classroomCount.js

/*
Copyright (C) 2016 Centro de Computacao Cientifica e Software Livre
Departamento de Informatica - Universidade Federal do Parana - C3SL/UFPR

This file is part of simcaq-node.

simcaq-node is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

simcaq-node is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with simcaq-node.  If not, see <https://www.gnu.org/licenses/>.
*/


const express = require('express');

const classroomCountApp = express.Router();

const libs = `${process.cwd()}/libs`;

const squel = require('squel');

const query = require(`${libs}/middlewares/query`).query;

const response = require(`${libs}/middlewares/response`);

const ReqQueryFields = require(`${libs}/middlewares/reqQueryFields`);

const id2str = require(`${libs}/middlewares/id2str`);

const addMissing = require(`${libs}/middlewares/addMissing`);

const config = require(`${libs}/config`); 

const cache = require('apicache').options({ debug: config.debug, statusCodes: {include: [200]} }).middleware;

let rqf = new ReqQueryFields();

rqf.addField({
    name: 'filter',
    field: false,
    where: true
}).addField({
    name: 'dims',
    field: true,
    where: false
}).addValueToField({
    name: 'city',
    table: 'municipio',
    tableField: 'nome',
    resultField: 'city_name',
    where: {
        relation: '=',
        type: 'integer',
        field: 'municipio_id',
        table: '@'
    },
    join: {
        primary: 'id',
        foreign: 'municipio_id',
        foreignTable: '@'
    }
}, 'filter').addValueToField({
    name: 'city',
    table: 'municipio',
    tableField: ['nome', 'id'],
    resultField: ['city_name', 'city_id'],
    where: {
        relation: '=',
        type: 'integer',
        field: 'id'
    },
    join: {
        primary: 'id',
        foreign: 'municipio_id',
        foreignTable: '@'
    }
}, 'dims').addValueToField({
    name: 'state',
    table: 'estado',
    tableField: 'nome',
    resultField: 'state_name',
    where: {
        relation: '=',
        type: 'integer',
        field: 'estado_id',
        table: '@'
    },
    join: {
        primary: 'id',
        foreign: 'estado_id',
        foreignTable: '@'
    }
}, 'filter').addValueToField({
    name: 'state',
    table: 'estado',
    tableField: ['nome', 'id'],
    resultField: ['state_name', 'state_id'],
    where: {
        relation: '=',
        type: 'integer',
        field: 'id',
    },
    join: {
        primary: 'id',
        foreign: 'estado_id',
        foreignTable: '@'
    }
}, 'dims').addValue({
    name: 'region',
    table: 'regiao',
    tableField: 'nome',
    resultField: 'region_name',
    where: {
        relation: '=',
        type: 'integer',
        field: 'id'
    },
    join: {
        primary: 'id',
        foreign: 'regiao_id',
        foreignTable: '@'
    }
}).addValue({
    name: 'min_year',
    table: '@',
    tableField: 'ano_censo',
    resultField: 'year',
    where: {
        relation: '>=',
        type: 'integer',
        field: 'ano_censo'
    }
}).addValue({
    name: 'max_year',
    table: '@',
    tableField: 'ano_censo',
    resultField: 'year',
    where: {
        relation: '<=',
        type: 'integer',
        field: 'ano_censo'
    }
}).addValue({
    name: 'school_year',
    table: '@',
    tableField: 'serie_ano_id',
    resultField: 'school_year_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'serie_ano_id'
    }
}).addValue({
    name: 'location',
    table: '@',
    tableField: 'localizacao_id',
    resultField: 'location_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'localizacao_id'
    }
}).addValue({
    name: 'period',
    table: '@',
    tableField: 'turma_turno_id',
    resultField: 'period_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'turma_turno_id'
    }
}).addValue({
    name: 'school_building',
    table: 'escola',
    tableField: 'local_func_predio_escolar',
    resultField: 'school_building',
    where: {
        relation: '=',
        type: 'boolean',
        field: 'local_func_predio_escolar'
    }
}).addValue({
    name: 'night_time',
    table: 'matricula',
    tableField: 'periodo_noturno',
    resultField: 'night_time',
    where: {
        relation: '=',
        type: 'boolean',
        field: 'periodo_noturno'
    }
});

classroomCountApp.post('/', rqf.parse(), (req, res, next) => {
    let classSize = JSON.parse(req.body.class_size) || null;
    let integralTime = JSON.parse(req.body.integral_time) || null;
    let enrollmentProjection = (req.body.projections !== undefined) ? JSON.parse(req.body.projections) : null;

    if(classSize == null || integralTime == null) {
        res.statusCode = 400;
        return res.json({err: {message: "There was an error processing class_size or integral_time. Check your JSON sintax and be sure you're sending both paramenters."}});
    }
    req.classSize = classSize;
    req.integralTime = integralTime;
    req.projections = (enrollmentProjection !== null) ? enrollmentProjection : false;

    req.dims.state = true;
    req.dims.city = true;
    req.dims.school_year = true;
    req.dims.location = true;

    req.sql.field('dia_total', 'total_day')
    .field('noite_total', 'total_night')
    .field("'Brasil'", 'name')
    .field('matricula_por_localizacao.ano_censo', 'year')
    .from('matricula_por_localizacao')
    .where('matricula_por_localizacao.serie_ano_id < 15')
    .group('matricula_por_localizacao.ano_censo')
    .group('matricula_por_localizacao.dia_total')
    .group('matricula_por_localizacao.noite_total')
    .order('matricula_por_localizacao.ano_censo')

    next();
}, rqf.build(), query, id2str.transform(), (req, res, next) => {
    req.enrollment = req.result;

    // Gera a relação etapa de ensino X ano escolar
    req.educationSchoolYear = {};
    for(let i = 1; i < 15; ++i) {
        if(id2str.educationLevelSchoolYear(i) !== id2str.educationLevelSchoolYear(99)) {
            let educationLevelId = (i > 10) ? Math.floor(i/10) : i;

            let classSize = req.classSize.find((el) => {return el.id === educationLevelId || el.id === i});
            let integralTime = req.integralTime.find((el) => {return el.id === educationLevelId});

            let numberStudentClass = (typeof classSize !== 'undefined') ? classSize.numberStudentClass : null;
            let integralTimeOfferGoal = (typeof integralTime !== 'undefined') ? integralTime.offerGoal : null;

            req.educationSchoolYear[i] = {
                id: educationLevelId,
                name: id2str.educationLevelShort(educationLevelId),
                numberStudentClass,
                integralTimeOfferGoal
            };
        }
    }
    delete req.dims;
    delete req.filter;
    req.resetSql();
    next();
}, rqf.parse(), (req, res, next) => {

    req.dims.state = true;
    req.dims.city = true;
    req.dims.location = true;
    req.dims.school_building = true;

    req.sql.field('SUM(escola.num_salas)', 'total')
    .field("'Brasil'", 'name')
    .field('escola.ano_censo', 'year')
    .from('escola')
    .group('escola.ano_censo')
    .order('escola.ano_censo')
    .where('escola.situacao_de_funcionamento = 1')
    .where('escola.dependencia_adm_id < 4');

    next();
}, rqf.build(), query, id2str.transform(), (req, res, next) =>  {
    req.classroom = req.result;
    delete req.dims;
    delete req.filter;
    req.resetSql();
    next();
}, rqf.parse(), (req, res, next) => {
    if ("state" in req.filter) {
        req.dims.state = true;
        req.dims.city = true;
        req.dims.school_year = true;
        req.dims.location = true;
        req.sql.field('indice_distribuicao_matriculas.indice', 'total')
               .field("'Brasil'", 'name')
               .field('indice_distribuicao_matriculas.ano_censo', 'year')
               .from('indice_distribuicao_matriculas')
               .where('indice_distribuicao_matriculas.nivel_simulacao = 2')
               .where('indice_distribuicao_matriculas.serie_ano_id < 15')
               .group('indice_distribuicao_matriculas.ano_censo')
               .group('indice_distribuicao_matriculas.indice')
               .order('indice_distribuicao_matriculas.ano_censo')
    }
    else {
        req.dims.state = true;
        req.dims.city = true;
        req.dims.school_year = true;
        req.dims.location = true;
        req.sql.field('indice_distribuicao_matriculas.indice', 'idm')
               .field("'Brasil'", 'name')
               .field('indice_distribuicao_matriculas.ano_censo', 'year')
               .from('indice_distribuicao_matriculas')
               .where('indice_distribuicao_matriculas.nivel_simulacao = 1')
               .where('indice_distribuicao_matriculas.serie_ano_id < 15')
               .group('indice_distribuicao_matriculas.ano_censo')
               .group('indice_distribuicao_matriculas.indice')
               .order('indice_distribuicao_matriculas.ano_censo')
    }

    next();
}, rqf.build(), query, id2str.transform(), (req, res, next) =>  {
    delete req.dims;
    delete req.filter;
    next();
},rqf.parse(), rqf.build(), (req, res, next) => {
    // req.classroom = req.result;
    req.idm = req.result; 
    
    // req.result = [{classroom: req.classroom, enrollment: req.enrollment}]; return next();

    let enrollmentProjectionNight = [];
    let enrollmentProjectionDay = [];
    if (req.projections) {
        for (let i = 0; i < req.projections.length; i++) {
            if (req.projections[i].periods === 3) {
                enrollmentProjectionNight.push(req.projections[i]);
            } else {
                enrollmentProjectionDay.push(req.projections[i]);
            }
        }
    }
    let qntYears = 1;
    if (req.projections) {
        qntYears = enrollmentProjectionDay[0].stagesEnrollments[0].seriesEnrollments[0].enrollments.length;
    }
    let result = [];

    // Cria estrutura de resposta requisitada:
    for(let yearCount = 0; yearCount < qntYears; yearCount++) {
        let i = 0;
        let j = 0;
        let hashSet = new Set();
        let enrollments = [...req.enrollment];
        let ei = 0;
        let eiCityStart = 0;
        let verifySchoolYear;                                   // Matriz para verificar se o idm já foi inserido no school year.
        while (i < req.classroom.length) {
            let classroom = req.classroom[i];
            // Cria hash única para cada espacialidade, dado um ano
            let hash = '' + yearCount + classroom.state_id + classroom.city_id;
            // Estrutura do objeto do resultado final
            let obj = {
                year: classroom.year + yearCount,
                name: classroom.name,
                state_id: classroom.state_id,
                state_name: classroom.state_name,
                city_id: classroom.city_id,
                city_name: classroom.city_name,
                locations: []
            };

            // Inserimos a localidade no array de locations da sala
            let location = {
                location_id: classroom.location_id,
                location_name: classroom.location_name,
                total_classroom: (classroom.school_building == 1) ? parseInt(classroom.total, 10) : 0,      // Conta apenas salas de prédios próprios
                total_classroom_be_built: 0,
                education_level: []
            };
            
            let currentClassroomObj = null;
            if( !hashSet.has(hash) ) {
                if (result[result.length - 1] !== undefined) {      // Após mudar de cidade, passamos pela cidade anterior e juntamos o valor to_be_built de localizações com o mesmo id
                    let last_locations = result[result.length - 1].locations
                    for (let i = 0; i < last_locations.length - 1; i++) {
                        if (last_locations[i].location_id === last_locations[i+1].location_id) {
                            last_locations[i].total_classroom_be_built += last_locations[i+1].total_classroom_be_built;
                            last_locations[i].total_classroom += last_locations[i+1].total_classroom;
                            last_locations.splice(i+1, 1);
                        }
                    }
                    for (let i = 0; i < last_locations.length; i++) {   // Passamos agora para não deixar to_be_built < 0
                        last_locations[i].total_classroom_be_built = (last_locations[i].total_classroom_be_built < 0) ? 0 : last_locations[i].total_classroom_be_built;
                    }
                }
                hashSet.add(hash);
                result.push(obj);
                currentClassroomObj = obj;
                eiCityStart = ei;
                verifySchoolYear = Array.from(Array(2), () => new Array(15));
            } else { // Se a hash já existe, já temos a cidade nos resultados. Como está ordenado, é o último valor nos resultados
                currentClassroomObj = result[result.length - 1];
                ei = eiCityStart;
            }
            currentClassroomObj.locations.push(location);

            // Partimos para as etapas de ensino/anos escolares
            let enrollmentMatch = true;
            j = 0;
            let educationLevelSet = new Set();
            let schoolYearSet = new Set();
            let enrollment;
            while(enrollmentMatch && j < enrollments.length) {
                enrollment = enrollments[j];

                if(typeof enrollment === 'undefined') {
                    ++j;
                    continue;
                }

                if(classroom.city_id !== enrollment.city_id) { // Se as cidades não são iguais, já passamos do range
                    enrollmentMatch = false;
                    while (req.idm[ei].city_id !== enrollment.city_id) {
                        ei++;
                    }
                    continue;
                } 

                if(enrollment.year != classroom.year || enrollment.location_id != classroom.location_id) { // Se ano ou localização são diferentes, passa para o próximo
                    ++j;
                    continue;
                }


                // Temos uma matrícula com cidade, ano e localidades certos
                // "Consome" a matrícula (remove do vetor de matrículas)
                enrollments.splice(j, 1);

                // Cria a etapa de ensino adequada
                let enrollmentEducationLevel = req.educationSchoolYear[enrollment.school_year_id];
                // Se não há um número de alunos por turna para a etapa de ensino, ignoramos a entrada

                if(enrollmentEducationLevel.numberStudentClass == null) continue;
                
                let educationLevel = null;
                if(!educationLevelSet.has(enrollmentEducationLevel.id)) {
                    educationLevelSet.add(enrollmentEducationLevel.id);

                    educationLevel = {
                        education_level_short_id: enrollmentEducationLevel.id,
                        education_level_short_name: enrollmentEducationLevel.name,
                        enrollment: {
                            total_enrollment_day: 0,
                            total_enrollment_night: 0,
                            full_period_classes: 0,
                            day_classes: 0,
                            night_classes: 0,
                            total_classrooms_needed: 0
                        }
                    };

                    if(enrollmentEducationLevel.id == 1) {
                        educationLevel.classes_school_year = [];
                    }

                    // Para manter a ordem da etapa de ensino
                    if (location.education_level.length == 0) {
                        location.education_level.push(educationLevel);
                    } else {
                        let k = location.education_level.length - 1;
                        let el = location.education_level[k];
                        while (k >= 0) {
                            if(educationLevel.education_level_short_id < el.education_level_short_id) {
                                --k;
                                if(k>=0) el = location.education_level[k];
                            } else break;
                        }
                        k++;
                        location.education_level.splice(k, 0, educationLevel);
                    }
                } else {
                    let k = 0;
                    let el = location.education_level[k];
                    while(k < location.education_level.length) {
                        if(el.education_level_short_id != enrollmentEducationLevel.id) {
                            ++k;
                            if(k<location.education_level.length) el = location.education_level[k];
                        } else break;
                    }
                    if(k >= location.education_level.length) --k;
                    educationLevel = location.education_level[k];
                }

                // Adiciona as séries da creche
                let currentSchoolYear = null;
                if(enrollmentEducationLevel.id == 1){
                    let schoolYearHash = '' + enrollment.year + enrollment.city_id + enrollment.location_id + enrollment.school_year_id;
                    if(schoolYearSet.has(schoolYearHash)) { // Busca a série escolar
                        let k = 0;
                        let el = educationLevel.classes_school_year[k];
                        while(k < educationLevel.classes_school_year.length) {
                            if(el.school_year_id != enrollment.school_year_id) {
                                ++k;
                                if(k < educationLevel.classes_school_year.length) el = educationLevel.classes_school_year[k];
                            } else break;
                        }
                        if(k >= educationLevel.classes_school_year.length) --k;
                        currentSchoolYear = educationLevel.classes_school_year[k];
                    } else { // Adiciona uma nova série escolar
                        let school_year = {
                            school_year_id: enrollment.school_year_id,
                            school_year_name: enrollment.school_year_name,
                            total_enrollment_day: 0,
                            total_enrollment_night: 0,
                            full_period_classes: 0,
                            day_classes: 0,
                            night_classes: 0,
                            total_classrooms_needed: 0
                        }
                        schoolYearSet.add(schoolYearHash);
                        // Busca a posição para inserir
                        let k = 0;
                        let el = educationLevel.classes_school_year[k];
                        while((typeof el !== 'undefined') && school_year.school_year_id > el.school_year_id) {
                            el = educationLevel.classes_school_year[++k];
                        }
                        // educationLevel.classes_school_year.push(school_year);
                        educationLevel.classes_school_year.splice(k, 0, school_year);
                        currentSchoolYear = school_year;
                    }
                }

                // Faz match da tabela de idm com a de enrollment
                if (educationLevel.education_level_short_id !== 1) {
                    while (req.idm[ei].school_year_id < educationLevel.education_level_short_id) ei++;
                    while (req.idm[ei].school_year_id > educationLevel.education_level_short_id) ei--;

                    if (req.idm[ei].location_id < location.location_id) {
                        ei++;
                    }
                    else if(req.idm[ei].location_id > location.location_id) {
                        ei--;
                    }
                }
                let enrollmentIdm = req.idm[ei];


                let currentIntegralOfferGoal = enrollmentEducationLevel.integralTimeOfferGoal;
                let currentNumberStudentClass = (enrollment.location_id == 1) ? enrollmentEducationLevel.numberStudentClass.urban : enrollmentEducationLevel.numberStudentClass.country;
                
                // Soma os totais de matrícula da etapa de ensino
                educationLevel.enrollment.total_enrollment_day += enrollment.total_day;
                educationLevel.enrollment.total_enrollment_night += (educationLevel.education_level_short_id > 2) ? enrollment.total_night : 0;      //Não contamos matrículas noturnos de pré-escola e creche

                let currentEnrollmentOfferDay;
                let currentEnrollmentOfferNight;
                if (req.projections) {
                    currentEnrollmentOfferDay = enrollmentProjectionDay[enrollment.location_id - 1].stagesEnrollments[educationLevel.education_level_short_id - 1];
                    currentEnrollmentOfferNight = enrollmentProjectionNight[enrollment.location_id - 1].stagesEnrollments[educationLevel.education_level_short_id - 1];
                }
                if (req.projections && (!verifySchoolYear[location.location_id-1][educationLevel.education_level_short_id]) && (currentEnrollmentOfferDay.enrollments !== undefined)) {     // Garantimos que será somado apenas uma vez por localização.
                    // Projeção de matricula multiplicada pelo indice de distribuição de matriculas da localização.
                    verifySchoolYear[location.location_id-1][educationLevel.education_level_short_id] = 1;

                    educationLevel.enrollment.total_enrollment_day += Math.ceil((currentEnrollmentOfferDay.enrollments[yearCount].quantity - currentEnrollmentOfferDay.currentOffer)*enrollmentIdm.idm);
                    if (educationLevel.enrollment.total_enrollment_day < 0) educationLevel.enrollment.total_enrollment_day = 0;
                    educationLevel.enrollment.total_enrollment_night += (educationLevel.education_level_short_id > 2) ? Math.ceil((currentEnrollmentOfferNight.enrollments[yearCount].quantity - currentEnrollmentOfferNight.currentOffer)*enrollmentIdm.idm) : 0;
                    if (educationLevel.enrollment.total_enrollment_night < 0) educationLevel.enrollment.total_enrollment_night = 0;
                }

                // Calcula o número de turmas parcial
                // Turmas de período integral
                educationLevel.enrollment.full_period_classes = Math.ceil((educationLevel.enrollment.total_enrollment_day * (currentIntegralOfferGoal/100)) / currentNumberStudentClass);

                // Turmas diurnas (matrículas diurnas - matrículas integrais)
                educationLevel.enrollment.day_classes = Math.ceil((educationLevel.enrollment.total_enrollment_day * (1 - currentIntegralOfferGoal/100)) / currentNumberStudentClass);

                // Turmas noturnas
                educationLevel.enrollment.night_classes = Math.ceil((educationLevel.enrollment.total_enrollment_night / currentNumberStudentClass)) || 0;

                // Total de salas
                educationLevel.enrollment.total_classrooms_needed = (educationLevel.enrollment.full_period_classes + educationLevel.enrollment.day_classes/2);

                if(educationLevel.enrollment.night_classes > (educationLevel.enrollment.day_classes/2)) educationLevel.enrollment.total_classrooms_needed += (educationLevel.enrollment.night_classes - (educationLevel.enrollment.day_classes/2));

                educationLevel.enrollment.total_classrooms_needed = Math.ceil(educationLevel.enrollment.total_classrooms_needed);

                // Faz os mesmos cálculos para a série escolar
                if(currentSchoolYear) {
                    // Faz match da enrollment com o idm, no caso de não usar o mod
                    while (req.idm[ei].school_year_id < enrollment.school_year_id) ei++;
                    while (req.idm[ei].school_year_id > enrollment.school_year_id) ei--;

                    if (req.idm[ei].location_id < location.location_id) {
                        ei++;
                    }
                    else if(req.idm[ei].location_id > location.location_id) {
                        ei--;
                    }
                    enrollmentIdm = req.idm[ei];


                    // Totais de matrícula
                    currentSchoolYear.total_enrollment_day += enrollment.total_day;

                    if (req.projections && !verifySchoolYear[location.location_id-1][currentSchoolYear.school_year_id]) {     // Garantimos que será somado apenas uma vez por localização.
                        verifySchoolYear[location.location_id-1][currentSchoolYear.school_year_id] = 1;

                        let currentEnrollmentSchoolYear = currentEnrollmentOfferDay.seriesEnrollments[Math.trunc(currentSchoolYear.school_year_id/10) - 1];
                        currentSchoolYear.total_enrollment_day += (currentEnrollmentSchoolYear !== undefined) ? Math.ceil((currentEnrollmentSchoolYear.enrollments[yearCount].quantity - currentEnrollmentSchoolYear.currentOffer)*enrollmentIdm.idm) : 0;
                        if (currentSchoolYear.total_enrollment_day < 0) currentSchoolYear.total_enrollment_day = 0;
                    }
                    
                    // Número de turmas parcial
                    currentSchoolYear.full_period_classes = Math.ceil((currentSchoolYear.total_enrollment_day * (currentIntegralOfferGoal/100)) / currentNumberStudentClass);

                    currentSchoolYear.day_classes = Math.ceil((currentSchoolYear.total_enrollment_day * (1 - currentIntegralOfferGoal/100)) / currentNumberStudentClass);


                    // Total de salas
                    currentSchoolYear.total_classrooms_needed = (currentSchoolYear.full_period_classes + currentSchoolYear.day_classes/2);

                    currentSchoolYear.total_classrooms_needed = Math.ceil(currentSchoolYear.total_classrooms_needed);

                    function reducer(key) { return (sum, elem) => sum + elem[key]}
                    educationLevel.enrollment.total_enrollment_day = educationLevel.classes_school_year.reduce(reducer('total_enrollment_day'), 0);
                    educationLevel.enrollment.full_period_classes = educationLevel.classes_school_year.reduce(reducer('full_period_classes'), 0);
                    educationLevel.enrollment.day_classes = educationLevel.classes_school_year.reduce(reducer('day_classes'), 0);
                    educationLevel.enrollment.total_classrooms_needed = educationLevel.classes_school_year.reduce(reducer('total_classrooms_needed'), 0);
                }
                enrollment = enrollments[j];
            }

            // Calculamos o total classroom be built para o município usando reduce
            location.total_classroom_be_built = location.education_level.reduce((total, atual) => {
                return total + atual.enrollment.total_classrooms_needed;
            }, 0) - location.total_classroom;
            ++i;
        }

        // Tratamento do último resultado, para remover double location, tirar negativo do to_be_built.
        if (result[result.length - 1] !== undefined) {      // Após mudar de cidade, passamos pela cidade anterior e juntamos o valor to_be_built de localizações com o mesmo id
            let last_locations = result[result.length - 1].locations
            for (let i = 0; i < last_locations.length - 1; i++) {
                if (last_locations[i].location_id === last_locations[i+1].location_id) {
                    last_locations[i].total_classroom_be_built += last_locations[i+1].total_classroom_be_built;
                    last_locations[i].total_classroom += last_locations[i+1].total_classroom;
                    last_locations.splice(i+1, 1);
                }
            }
            for (let i = 0; i < last_locations.length; i++) {   // Passamos agora para não deixar to_be_built < 0
                last_locations[i].total_classroom_be_built = (last_locations[i].total_classroom_be_built < 0) ? 0 : last_locations[i].total_classroom_be_built;
            }
        }
    }

    // Agregar por estado e brasil
    let reduction = null;
    if(req.dims.state || !req.dims.city) { // Se um dos dois acontecer, sabemos que devemos agregar
        let i = 0;
        reduction = [];
        let reductionSet = new Set();
        while (i < result.length) {
            let city = result[i];
            let obj = {
                year: city.year,
                name: city.name
            }

            if(req.dims.state) {
                obj.state_id = city.state_id;
                obj.state_name = city.state_name;
            }

            obj.locations = [];

            let hash = '' + city.year;
            if(req.dims.state) hash += '' + city.state_id;

            let currentObj = null;
            if(!reductionSet.has(hash)) {
                reductionSet.add(hash);
                reduction.push(obj);
                currentObj = obj;
            } else { // Está ordenado, podemos pegar o último
                currentObj = reduction[reduction.length - 1];
            }

            // Fazer "merge" do array locations da cidade com o da agregação
            if(currentObj.locations.length == 0) {
                // Pode ser que a cidade atual tenha menos localidades que o total (só urbana ou só rural)
                currentObj.locations = [...city.locations];
                if (currentObj.locations.length === 1) {        // Se a cidade só tinha uma, adcionamos a outra manualmente.
                    currentObj.locations[1] = {
                        location_id: (currentObj.locations[0].location_id === 1) ? 2 : 1,   // Oposto da adcionada
                        location_name: (currentObj.locations[0].location_id === 1) ? 'Rural' : 'Urbana',    // Oposto da adcionada
                        total_classroom: 0,
                        total_classroom_be_built: 0,
                        education_level: []
                    }
                }
            } else {
                let j = 0;
                let k = 0;
                let cityLocation = null;
                let currentLocation = null;
                while((typeof cityLocation !== 'undefined') && (typeof currentLocation !== 'undefined')) {
                    cityLocation = city.locations[j];
                    currentLocation = currentObj.locations[k];
                    if(cityLocation.location_id < currentLocation.location_id) {
                        ++j;
                        cityLocation = city.locations[j];
                        continue;
                    } else if(cityLocation.location_id > currentLocation.location_id) {
                        ++k;
                        currentLocation = currentObj.locations[k];
                        continue;
                    }

                    // Faz "merge" do array education_level
                    // Se a localidade atual não tem o vetor
                    if(currentLocation.education_level.length == 0) {
                        currentLocation.education_level = [...cityLocation.education_level];
                    } else { // Caso já tenha, atualiza os valores
                        let l = 0;
                        while(l < cityLocation.education_level.length) {
                            let cityEducation = cityLocation.education_level[l];
                            let m = 0;
                            let currentEducation = currentLocation.education_level[m];
                            while(m < currentLocation.education_level.length && cityEducation.education_level_short_id > currentEducation.education_level_short_id) {
                                ++m;
                                currentEducation = currentLocation.education_level[m];
                            }
                            if(m >= currentLocation.education_level.length) --m;
                            currentEducation = currentLocation.education_level[m];

                            if(currentEducation.education_level_short_id == cityEducation.education_level_short_id) {
                                currentEducation.enrollment.total_enrollment_day += cityEducation.enrollment.total_enrollment_day;
                                currentEducation.enrollment.total_enrollment_night += cityEducation.enrollment.total_enrollment_night;
                                currentEducation.enrollment.full_period_classes += cityEducation.enrollment.full_period_classes;
                                currentEducation.enrollment.day_classes += cityEducation.enrollment.day_classes;
                                currentEducation.enrollment.night_classes += cityEducation.enrollment.night_classes;
                                currentEducation.enrollment.total_classrooms_needed += cityEducation.enrollment.total_classrooms_needed;
                                // Insere as séries escolares, se existirem
                                if((typeof cityEducation.classes_school_year !== 'undefined') && (typeof currentEducation.classes_school_year !== 'undefined')) {
                                    let n = 0;
                                    let o = 0;
                                    let currentClass = currentEducation.classes_school_year[n];
                                    let cityClass = cityEducation.classes_school_year[o];
                                    while((typeof cityClass !== 'undefined') && (typeof currentClass !== 'undefined')) {
                                        currentClass = currentEducation.classes_school_year[n];
                                        cityClass = cityEducation.classes_school_year[o];

                                        // Se a série escolar é menor que a atual, ela não está no vetor, pois o vetor está ordenado e tem range limitado
                                        if(cityClass.school_year_id < currentClass.school_year_id) {
                                            currentEducation.classes_school_year.splice(n, 0, cityClass);
                                            currentClass = currentEducation.classes_school_year[n];
                                            cityClass = cityEducation.classes_school_year[++o];
                                            continue;
                                        } else if(cityClass.school_year_id > currentClass.school_year_id) {
                                            currentClass = currentEducation.classes_school_year[++n];
                                            // Se o ano escolar da cidade é  maior que do objeto atual E o vetor de ano escolar do objeto atual
                                            // acaba, então este ano escolar falta no objeto atual, pois os anos escolares estão ordenados
                                            if((typeof currentClass == 'undefined') && (typeof cityClass !== 'undefined')) {
                                                currentEducation.classes_school_year[n] = cityClass;
                                                currentClass = currentEducation.classes_school_year[n];
                                            }
                                            continue;
                                        }

                                        currentClass.total_enrollment_day += cityClass.total_enrollment_day;
                                        currentClass.total_enrollment_night += cityClass.total_enrollment_night;
                                        currentClass.full_period_classes += cityClass.full_period_classes;
                                        currentClass.day_classes += cityClass.day_classes;
                                        currentClass.night_classes += cityClass.night_classes;
                                        currentClass.total_classrooms_needed += cityClass.total_classrooms_needed;
    
                                        cityClass = cityEducation.classes_school_year[++o];
                                    }

                                }
                            } else {
                                if(currentEducation.education_level_short_id < cityEducation.education_level_short_id) {
                                    currentLocation.education_level.splice(++m, 0, cityEducation);
                                } else {
                                    currentLocation.education_level.splice(m, 0, cityEducation);
                                }
                            }
                            ++l;
                        }
                    }

                    currentLocation.total_classroom += cityLocation.total_classroom;
                    currentLocation.total_classroom_be_built += cityLocation.total_classroom_be_built;

                    ++j;
                    cityLocation = city.locations[j];
                }
            }
            ++i;
        }
    }

    req.result = reduction || result;

    next();
}, response('classroom_count'));

module.exports = classroomCountApp;