Skip to content
Snippets Groups Projects
Select Git revision
  • teachers-ies
  • homologa protected
  • development protected
  • instruction_level_fix2
  • enrollment_rate
  • master default protected
  • hotfix-enrollment-aggregate
  • instruction_level_fixes
  • docentes-ies-ente-federativo
  • receita-potencial
  • #974-receita-potencial
  • db-conn
  • years-of-study-mean
  • new-indicators
  • issue_935
  • instruction_number
  • issue_958
  • issue_953
  • issues_indicadores_agregados
  • courseAggregate
  • v1.16.0
  • v1.15.1
  • v1.14.2
  • v1.14.1
  • v1.14.0
  • v1.14
  • v1.9.0
  • v1.8.3
  • v1.8.2
  • v1.8.1
  • v1.8.0
  • v1.7.0
  • v1.6.1
  • v1.6.0
  • v1.5.0
  • v1.4.2
  • v1.4.1
  • v1.4.0
  • v1.3.3
  • v1.3.2
40 results

classroomCount.js

Blame
  • classroomCount.js 19.17 KiB
    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 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
                        }
                    };
                    
                    // 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];
                }
    
                // 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) educationLevel.enrollment.total_classrooms_needed += (educationLevel.enrollment.night_classes - educationLevel.enrollment.day_classes);
    
                educationLevel.enrollment.total_classrooms_needed = Math.ceil(educationLevel.enrollment.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;