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'
    }
});

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;

    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.dims.state = true;
    req.dims.city = true;
    req.dims.period = true;
    req.dims.school_year = true;
    req.dims.location = true;

    req.sql.field('COUNT(*)', 'total')
    .field("'Brasil'", 'name')
    .field('matricula.ano_censo', 'year')
    .from('matricula')
    .group('matricula.ano_censo')
    .order('matricula.ano_censo')
    .where('matricula.tipo<=3')
    .where('matricula.dependencia_adm_id < 4');

    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 = 10; i < 80; ++i) {
        if(id2str.schoolYear(i) !== id2str.schoolYear(99)) {
            let educationLevelId = Math.floor(i/10);

            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 offerGoal = (typeof integralTime !== 'undefined') ? integralTime.offerGoal : null;

            req.educationSchoolYear[i] = {
                id: educationLevelId,
                name: id2str.educationLevelShort(educationLevelId),
                numberStudentClass,
                offerGoal
            };
        }
    }

    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) => {
    delete req.dims;
    delete req.filter;
    next();
}, rqf.parse(), rqf.build(), (req, res, next) => {
    req.classroom = req.result;
    let classroom = [];
    
    // req.result = [{classroom: req.classroom, enrollment: req.enrollment}]; return next();

    // Cria estrutura de resposta requisitada:
    let i = 0;
    let j = 0;
    let result = [];
    let hashSet = new Set();
    let enrollments = [...req.enrollment];
    let leftovers = [];
    while (i < req.classroom.length) {
        let classroom = req.classroom[i];
        // Cria hash única para cada espacialidade, dado um ano
        let hash = '' + classroom.year + classroom.state_id + classroom.city_id;
        // Estrutura do objeto do resultado final
        let obj = {
            year: classroom.year,
            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 = 1; i < last_locations.length; 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-1].total_classroom_be_built = 0;
                    }
                }
                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;
        } 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];
        }
        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 = enrollments[j];

        while(enrollmentMatch && j < enrollments.length) {
            enrollment = enrollments[j];

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

            if(classroom.city_name < enrollment.city_name) { // Se as cidades não são iguais, já passamos do range
                enrollmentMatch = false;
                continue;
            } else if(classroom.city_name > enrollment.city_name) {
                let enrollmentHash = '' + enrollment.year + enrollment.state_id + enrollment.city_id;
                if(hashSet.has(enrollmentHash)) {
                    leftovers.push(enrollments.splice(j, 1)); // Para inspeção, adiciona os que "sobram" no array de sobras
                } else {
                    ++j;
                }
                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;
                }
            }

            let currentOfferGoal = enrollmentEducationLevel.offerGoal;
            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.period_id < 3) ? enrollment.total : 0;
            educationLevel.enrollment.total_enrollment_night += (enrollment.period_id == 3 && educationLevel.education_level_short_id > 2) ? enrollment.total : 0;      //Não contamos matrículas noturnos de pré-escola e creche

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

            // Turmas diurnas (matrículas diurnas - matrículas integrais)
            educationLevel.enrollment.day_classes = Math.ceil((educationLevel.enrollment.total_enrollment_day * (1 - currentOfferGoal/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) {
                // Totais de matrícula
                currentSchoolYear.total_enrollment_day += (enrollment.period_id < 3) ? enrollment.total : 0;
                currentSchoolYear.total_enrollment_night += (enrollment.period_id == 3 && currentSchoolYear.school_year_id > 30) ? enrollment.total : 0;        //Não contamos matrículas noturnos de pré-escola e creche
                
                // Número de turmas parcial
                currentSchoolYear.full_period_classes = Math.ceil((currentSchoolYear.total_enrollment_day * (currentOfferGoal/100)) / currentNumberStudentClass);

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

                currentSchoolYear.night_classes = Math.ceil((currentSchoolYear.total_enrollment_night / enrollmentEducationLevel.numberStudentClass)) || 0;

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

                if(currentSchoolYear.night_classes > (currentSchoolYear.day_classes/2)) currentSchoolYear.total_classrooms_needed += (currentSchoolYear.night_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.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.night_classes = educationLevel.classes_school_year.reduce(reducer('night_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;
    }

    // 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];
            } 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];
                        // Se a localidade da cidade é  maior que a localidade do objeto atual E o vetor de localidades do objeto atual
                        // acaba, então esta localidade falta no objeto atual, pois as localidades estão ordenadas
                        if(typeof currentLocation == 'undefined' && typeof cityLocation !== 'undefined') {
                            currentObj.locations[k] = cityLocation;
                            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;