classroomCount.js

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

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;

    console.log(classSize, integralTime);

    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});
            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.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 AND escola.local_func_predio_escolar = 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: []
        };

        let currentClassroomObj = null;
        if( !hashSet.has(hash) ) {
            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];
        }

        // Inserimos a localidade no array de locations da sala
        let location = {
            location_id: classroom.location_id,
            location_name: classroom.location_name,
            total_classroom: parseInt(classroom.total, 10),
            total_classroom_be_built: 0,
            education_level: []
        };
        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];

            // Remove se o período é nulo (não dá pra usar no cálculo)
            if(enrollment.period_id == null) {
                enrollments.splice(j, 1);
                continue;
            }

            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);
                    educationLevel.classes_school_year.push(school_year);
                    currentSchoolYear = school_year;
                }
            }

            // Soma os totais de matrícula da etapa de ensino
            educationLevel.enrollment.total_enrollment_day += (enrollment.period_id < 3 && enrollment.period_id != null) ? enrollment.total : 0;
            educationLevel.enrollment.total_enrollment_night += (enrollment.period_id == 3) ? enrollment.total : 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 * (enrollmentEducationLevel.offerGoal/100)) / enrollmentEducationLevel.numberStudentClass);

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

            // Turmas noturnas
            educationLevel.enrollment.night_classes = Math.ceil((educationLevel.enrollment.total_enrollment_night / enrollmentEducationLevel.numberStudentClass));

            // 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.period_id != null) ? enrollment.total : 0;
                currentSchoolYear.total_enrollment_night += (enrollment.period_id == 3) ? enrollment.total : 0;
                
                // Número de turmas parcial
                currentSchoolYear.full_period_classes = Math.ceil((currentSchoolYear.total_enrollment_day * (enrollmentEducationLevel.offerGoal/100)) / enrollmentEducationLevel.numberStudentClass);

                currentSchoolYear.day_classes = Math.ceil((currentSchoolYear.total_enrollment_day * (1 - enrollmentEducationLevel.offerGoal/100)) / enrollmentEducationLevel.numberStudentClass);

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

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

            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;

        if(location.total_classroom_be_built < 0) location.total_classroom_be_built = 0;

        ++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;
                    }

                    // Fazer "merge" do array education_level
                    if(currentLocation.education_level.length == 0) {
                        currentLocation.education_level = [...cityLocation.education_level];
                    } else {
                        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;
                            } 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;