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

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

    next();
}, rqf.build(), query, id2str.transform(), (req, res, next) => {
    delete req.dims;
    delete req.filter;
    next();
}, rqf.parse(), rqf.build(), (req, res, next) => {
    console.log('dims', req.dims);
    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();
    while (i < req.classroom.length) {
        let classroom = req.classroom[i];
        // Cria hash única para cada espacialidade, dado um ano
        let hash = '' + classroom.year;
        if(req.dims.state) hash+= '' + classroom.state_id;
        if(req.dims.city) hash+= '' + classroom.state_id + classroom.city_id;
        // Estrutura do objeto do resultado final
        let obj = {
            year: classroom.year,
            name: classroom.name,
            locations: []
        };

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

        console.log('city', req.dims.city);
        if(req.dims.city) {
            obj.state_id = classroom.state_id;
            obj.state_name = classroom.state_name;
            obj.city_id = classroom.city_id;
            obj.city_name = classroom.city_name;
        }

        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();
        while(enrollmentMatch && j < req.enrollment.length) {
            let enrollment = req.enrollment[j];
            if(typeof enrollment === 'undefined') {
                enrollmentMatch = false;
                continue;
            }
            if(enrollment.city_id != classroom.city_id) { // Se as cidades não são iguais, já passamos do range
                enrollmentMatch = false;
                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;
            }

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

            // Temos uma matrícula com cidade, ano e localidades certos
            // "Consome" a matrícula (remove do vetor de matrículas)
            req.enrollment.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
                    }
                };
                
                // location.education_level.push(educationLevel);
                // Para manter a orde 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];
            }

            // 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
            educationLevel.enrollment.day_classes = Math.ceil((educationLevel.enrollment.total_enrollment_day / enrollmentEducationLevel.numberStudentClass) - educationLevel.enrollment.full_period_classes);

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

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

        }

        // 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;
    }
    // TODO: agregar por estado e brasil
    if(req.dims.state) {
        console.log('ESTADO!!!!');
    } else if(!req.dims.city) {
        console.log('BRASIL!!!');
    }

    req.result = result;

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

module.exports = classroomCountApp;