enrollment.js

const express = require('express');

const enrollmentApp = express.Router();

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

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

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 config = require(`${libs}/config`);

const passport = require('passport');

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

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

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

enrollmentApp.use(cache('15 day'));

let rqf = new ReqQueryFields();

// Complete range of the enrollments dataset.
// Returns a tuple of start and ending years of the complete enrollments dataset.
enrollmentApp.get('/year_range', (req, res, next) => {
    req.sql.from('matricula')
    .field('MIN(matricula.ano_censo)', 'start_year')
    .field('MAX(matricula.ano_censo)', 'end_year');
    next();
}, query, response('range'));

enrollmentApp.get('/years', (req, res, next) => {
    req.sql.from('matricula')
    .field('DISTINCT matricula.ano_censo', 'year');
    next();
}, query, response('years'));

enrollmentApp.get('/source', (req, res, next) => {
    req.sql.from('fonte')
    .field('fonte', 'source')
    .where('tabela = \'matricula\'');
    next();
}, query, response('source'));

enrollmentApp.get('/location', (req, res, next) => {
    req.result = [];
    for(let i = 1; i <= 2; ++i) {
        req.result.push({
            id: i,
            name: id2str.location(i)
        });
    };
    next();
}, response('location'));

enrollmentApp.get('/rural_location', (req, res, next) => {
    req.result = [
        {id: 1, name: "Urbana"},
        {id: 2, name: "Rural"},
        {id: 3, name: "Rural - Área de assentamento"},
        {id: 4, name: "Rural - Terra indígena"},
        {id: 5, name: "Rural - Área remanescente de quilombos"},
        {id: 6, name: "Rural - Unidade de uso sustentável"}
    ];
    next();
}, response('rural_location'));

// Returns all school years available
enrollmentApp.get('/school_year', (req, res, next) => {
    req.result = [];
    for(let i = 11; i <= 71; ++i) {
        let obj = {
            id: i,
            name: id2str.schoolYear(i)
        };

        if(obj.name !== id2str.schoolYear(99)) {
            req.result.push(obj);
        }
    }
    req.result.push({
        id: 99,
        name: id2str.schoolYear(99)
    });
    next();
}, response('school_year'));

// Returns all school years available
enrollmentApp.get('/education_level', (req, res, next) => {
    req.result = [];
    for(let i = 1; i <= 74; ++i) {
        let obj = {
            id: i,
            name: id2str.educationLevel(i)
        };

        if(obj.name !== id2str.educationLevel(99)) {
            req.result.push(obj);
        }
    }
    req.result.push({
        id: 99,
        name: id2str.educationLevel(99)
    });
    next();
}, response('education_level'));

// Returns all school years available
enrollmentApp.get('/education_level_mod', (req, res, next) => {
    req.result = [];
    for(let i = 1; i <= 10; ++i) {
        req.result.push({
            id: i,
            name: id2str.educationLevelMod(i)
        });
    }
    req.result.push({
        id: 99,
        name: id2str.educationLevelMod(99)
    });
    next();
}, response('education_level_mod'));

enrollmentApp.get('/education_level_short', (req, res, next) => {
    req.result = [
        {id: null, name: 'Não classificada'},
        {id: 1, name: 'Creche'},
        {id: 2, name: 'Pré-Escola'},
        {id: 3, name: 'Ensino Fundamental - anos iniciais'},
        {id: 4, name: 'Ensino Fundamental - anos finais'},
        {id: 5, name: 'Ensino Médio'},
        {id: 6, name: 'EJA'},
        {id: 7, name: 'EE exclusiva'}
    ];
    next();
}, response('education_level_short'));

// Returns all adm dependencies
enrollmentApp.get('/adm_dependency', (req, res, next) => {
    req.result = [];
    for(let i = 1; i <= 4; ++i) {
        req.result.push({
            id: i,
            name: id2str.admDependency(i)
        });
    };
    next();
}, response('adm_dependency'));

enrollmentApp.get('/adm_dependency_detailed', (req, res, next) => {
    req.result = [];
    for(let i = 1; i <= 6; ++i) {
        req.result.push({
            id: i,
            name: id2str.admDependencyPriv(i)
        });
    };
    next();
}, response('adm_dependency_detailed'));

// Return genders
enrollmentApp.get('/gender', (req, res, next) => {
    req.result = [
        {id: 1, name: 'Masculino'},
        {id: 2, name: 'Feminino'}
    ];
    next();
}, response('gender'));

// Return ethnic group
enrollmentApp.get('/ethnic_group', (req, res, next) => {
    req.result = [];
    for(let i = 1; i <=5; ++i) {
        req.result.push({
            id: i,
            name: id2str.ethnicGroup(i)
        });
    }
    next();
}, response('ethnic_group'));

enrollmentApp.get('/period', (req, res, next) => {
    req.result = [];
    for(let i = 1; i <= 3; ++i) {
        req.result.push({
            id: i,
            name: id2str.period(i)
        });
    }
    req.result.push({
        id: 99,
        name: id2str.period(99)
    });
    next();
}, response('period'));

// Returns integral-time avaible
enrollmentApp.get('/integral_time', (req, res, next) => {
    req.result = [
        {id: null, name: 'Não Disponível'},
        {id: 0, name: 'Não'},
        {id: 1, name: 'Sim'}
    ];
    next();
}, response('integral_time'));

rqf.addField({
    name: 'filter',
    field: false,
    where: true
}).addField({
    name: 'dims',
    field: true,
    where: false
}).addValue({
    name: 'adm_dependency',
    table: 'matricula',
    tableField: 'dependencia_adm_id',
    resultField: 'adm_dependency_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'dependencia_adm_id'
    }
}).addValue({
    name: 'adm_dependency_detailed',
    table: 'matricula',
    tableField: 'dependencia_adm_priv',
    resultField: 'adm_dependency_detailed_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'dependencia_adm_priv'
    }
}).addValue({
    name: 'school_year',
    table: 'matricula',
    tableField: 'serie_ano_id',
    resultField: 'school_year_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'serie_ano_id'
    }
}).addValue({
    name: 'education_level',
    table: 'matricula',
    tableField: 'etapa_ensino_id',
    resultField: 'education_level_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'etapa_ensino_id'
    }
}).addValue({
    name: 'education_level_mod',
    table: 'matricula',
    tableField: 'etapas_mod_ensino_segmento_id',
    resultField: 'education_level_mod_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'etapas_mod_ensino_segmento_id'
    }
}).addValue({
    name: 'education_level_short',
    table: 'matricula',
    tableField: 'etapa_resumida',
    resultField: 'education_level_short_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'etapa_resumida'
    }
}).addValue({
    name: 'region',
    table: 'regiao',
    tableField: 'nome',
    resultField: 'region_name',
    where: {
        relation: '=',
        type: 'integer',
        field: 'id'
    },
    join: {
        primary: 'id',
        foreign: 'regiao_id',
        foreignTable: 'matricula'
    }
}).addValue({
    name: 'state',
    table: 'estado',
    tableField: 'nome',
    resultField: 'state_name',
    where: {
        relation: '=',
        type: 'integer',
        field: 'id'
    },
    join: {
        primary: 'id',
        foreign: 'estado_id',
        foreignTable: 'matricula'
    }
}).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: 'matricula'
    }
}, 'dims').addValueToField({
    name: 'city',
    table: 'municipio',
    tableField: 'nome',
    resultField: 'city_name',
    where: {
        relation: '=',
        type: 'integer',
        field: 'id'
    },
    join: {
        primary: 'id',
        foreign: 'municipio_id',
        foreignTable: 'matricula'
    }
}, 'filter').addValueToField({
    name: 'school',
    table: 'escola',
    tableField: ['nome_escola', 'id'],
    resultField: ['school_name', 'school_id'],
    where: {
        relation: '=',
        type: 'integer',
        field: 'id'
    },
    join: {
        primary: ['id', 'ano_censo'],
        foreign: ['escola_id', 'ano_censo'],
        foreignTable: 'matricula'
    }
}, 'dims').addValueToField({
    name: 'school',
    table: 'escola',
    tableField: 'nome_escola',
    resultField: 'school_name',
    where: {
        relation: '=',
        type: 'integer',
        field: 'id'
    },
    join: {
        primary: ['id', 'ano_censo'],
        foreign: ['escola_id', 'ano_censo'],
        foreignTable: 'matricula'
    }
}, 'filter').addValue({
    name: 'location',
    table: 'matricula',
    tableField: 'localizacao_id',
    resultField: 'location_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'localizacao_id'
    }
}).addValue({
    name: 'rural_location',
    table: 'matricula',
    tableField: 'localidade_area_rural',
    resultField: 'rural_location_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'localidade_area_rural'
    }
}).addValue({
    name: 'min_year',
    table: 'matricula',
    tableField: 'ano_censo',
    resultField: 'year',
    where: {
        relation: '>=',
        type: 'integer',
        field: 'ano_censo'
    }
}).addValue({
    name: 'max_year',
    table: 'matricula',
    tableField: 'ano_censo',
    resultField: 'year',
    where: {
        relation: '<=',
        type: 'integer',
        field: 'ano_censo'
    }
}).addValue({
    name: 'gender',
    table: 'matricula',
    tableField: 'sexo',
    resultField: 'gender_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'sexo'
    }
}).addValue({
    name: 'ethnic_group',
    table: 'matricula',
    tableField: 'cor_raca_id',
    resultField: 'ethnic_group_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'cor_raca_id'
    }
}).addValue({
    name: 'period',
    table: 'matricula',
    tableField: 'turma_turno_id',
    resultField: 'period_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'turma_turno_id'
    }
}).addValue({
  name:'integral_time',
  table: 'matricula',
  tableField: 'tempo_integral',
  resultField: 'integral_time_id',
  where: {
      relation: '=',
      type: 'boolean',
      field: 'tempo_integral'
  }
});

enrollmentApp.get('/', rqf.parse(), rqf.build(), (req, res, next) => {
    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();
}, query, addMissing(rqf), id2str.transform(false), response('enrollment'));

enrollmentApp.get('/download', passport.authenticate('bearer', { session: false }), rqf.parse(), rqf.build(), download('matricula', 'mapping_matricula'));

enrollmentApp.get('/diagnosis', rqf.parse(), (req, res, next) => {
    req.dims = {};
    req.dims.school_year = true;
    req.dims.location = true;
    req.dims.adm_dependency_detailed = 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) => {
    let enrollments = req.result;

    // Gera a relação etapa de ensino X ano escolar
    let educationSchoolYear = {};
    for(let i = 10; i < 80; ++i) {
        if(id2str.schoolYear(i) !== id2str.schoolYear(99)) {
            let educationLevelId = Math.floor(i/10);
            educationSchoolYear[i] = {
                id: educationLevelId,
                name: id2str.educationLevelShort(educationLevelId),
            };
        }
    }

    let result = [];
    let educationLevelSet = new Set();
    let schoolYearSet = new Set();
    let i = 0;
    while(i < enrollments.length) {
        let enrollment = enrollments[i];
        let educationLevelHash = '' + enrollment.year + educationSchoolYear[enrollment.school_year_id].id;
        let schoolYearHash = '' + enrollment.year + enrollment.school_year_id;

        let currentEducation = null;
        // Busca ou cria a etapa de ensino adequada
        if(educationLevelSet.has(educationLevelHash)) {
            let j = 0;
            let edu = result[j];
            while(j < result.length && (edu.year != enrollment.year || edu.education_level_school_year_id != educationSchoolYear[enrollment.school_year_id].id)) {
                ++j;
                edu = result[j];
            }
            if(j >= result.length) --j;
            edu = result[j];

            currentEducation = edu;
        } else {
            educationLevelSet.add(educationLevelHash);
            let obj = {
                year: enrollment.year,
                name: enrollment.name,
                education_level_school_year_id: educationSchoolYear[enrollment.school_year_id].id,
                education_level_school_year_name: educationSchoolYear[enrollment.school_year_id].name,
                total: 0,
                adm_dependencies: [
                    {
                        adm_dependency_detailed_id: enrollment.adm_dependency_detailed_id,
                        adm_dependency_detailed_name: enrollment.adm_dependency_detailed_name,
                        total: 0
                    }
                ],
                locations: [
                    {
                        location_id: enrollment.location_id,
                        location_name: enrollment.location_name,
                        total: 0
                    }
                ]
            };

            result.push(obj);
            currentEducation = obj;
        }

        let currentSchoolYear = null;
        // Busca ou cria a série adequada
        if(schoolYearSet.has(schoolYearHash)) {
            let j = 0;
            let edu = result[j];
            while(j < result.length && (edu.year != enrollment.year || edu.education_level_school_year_id != enrollment.school_year_id)) {
                ++j;
                edu = result[j];
            }
            if(j >= result.length) --j;
            edu = result[j];

            currentSchoolYear = edu;
        } else {
            schoolYearSet.add(schoolYearHash);
            let obj = {
                year: enrollment.year,
                name: enrollment.name,
                education_level_school_year_id: enrollment.school_year_id,
                education_level_school_year_name: enrollment.school_year_name,
                total: 0,
                adm_dependencies: [
                    {
                        adm_dependency_detailed_id: enrollment.adm_dependency_detailed_id,
                        adm_dependency_detailed_name: enrollment.adm_dependency_detailed_name,
                        total: 0
                    }
                ],
                locations: [
                    {
                        location_id: enrollment.location_id,
                        location_name: enrollment.location_name,
                        total: 0
                    }
                ]
            };

            result.push(obj);
            currentSchoolYear = obj;
        }

        // Adiciona ao total
        currentEducation.total += enrollment.total;
        currentSchoolYear.total += enrollment.total;

        // Adiciona ao total da dependência administrativa
        let admDependencyIndex = 0;
        let admDependency = currentEducation.adm_dependencies[admDependencyIndex];
        while (admDependencyIndex < currentEducation.adm_dependencies.length && enrollment.adm_dependency_detailed_id > admDependency.adm_dependency_detailed_id) {
            ++admDependencyIndex;
            admDependency = currentEducation.adm_dependencies[admDependencyIndex];
        }
        if(admDependencyIndex >= currentEducation.adm_dependencies.length || admDependency.adm_dependency_detailed_id != enrollment.adm_dependency_detailed_id) { // não encontrou
            let obj = {
                adm_dependency_detailed_id: enrollment.adm_dependency_detailed_id,
                adm_dependency_detailed_name: enrollment.adm_dependency_detailed_name,
                total: 0
            }
            currentEducation.adm_dependencies.splice(admDependencyIndex, 0, obj);
            admDependency = obj;
        }
        admDependency.total += enrollment.total;

        admDependencyIndex = 0;
        admDependency = currentSchoolYear.adm_dependencies[admDependencyIndex];
        while (admDependencyIndex < currentSchoolYear.adm_dependencies.length && enrollment.adm_dependency_detailed_id > admDependency.adm_dependency_detailed_id) {
            ++admDependencyIndex;
            admDependency = currentSchoolYear.adm_dependencies[admDependencyIndex];
        }
        if(admDependencyIndex >= currentSchoolYear.adm_dependencies.length || admDependency.adm_dependency_detailed_id != enrollment.adm_dependency_detailed_id) { // não encontrou
            let obj = {
                adm_dependency_detailed_id: enrollment.adm_dependency_detailed_id,
                adm_dependency_detailed_name: enrollment.adm_dependency_detailed_name,
                total: 0
            }
            currentSchoolYear.adm_dependencies.splice(admDependencyIndex, 0, obj);
            admDependency = obj;
        }
        admDependency.total += enrollment.total;

        // Adiciona ao total da localidade
        let locationIndex = 0;
        let location = currentEducation.locations[locationIndex];
        while (locationIndex < currentEducation.locations.length && enrollment.location_id > location.location_id) {
            ++locationIndex;
            location = currentEducation.locations[locationIndex];
        }
        if(locationIndex >= currentEducation.locations.length || location.location_id != enrollment.location_id) {
            let obj = {
                location_id: enrollment.location_id,
                location_name: enrollment.location_name,
                total: 0
            }
            currentEducation.locations.splice(locationIndex, 0, obj);
            location = obj;
        }
        location.total += enrollment.total;

        locationIndex = 0;
        location = currentSchoolYear.locations[locationIndex];
        while (locationIndex < currentSchoolYear.locations.length && enrollment.location_id > location.location_id) {
            ++locationIndex;
            location = currentSchoolYear.locations[locationIndex];
        }
        if(locationIndex >= currentSchoolYear.locations.length || location.location_id != enrollment.location_id) {
            let obj = {
                location_id: enrollment.location_id,
                location_name: enrollment.location_name,
                total: 0
            }
            currentSchoolYear.locations.splice(locationIndex, 0, obj);
            location = obj;
        }
        location.total += enrollment.total;

        ++i;
    }

    req.result = result;

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

enrollmentApp.get('/projection', rqf.parse(), (req, res, next) => {
    req.dims = {};
    req.dims.location = true;
    req.dims.school_year = true;
    req.dims.adm_dependency = true;
    req.dims.period = true;
    req.filter.adm_dependency = [1,2,3];

    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) => {
    let enrollments = req.result;

    // Gera a relação etapa de ensino X ano escolar
    let educationSchoolYear = {};
    for(let i = 10; i < 80; ++i) {
        if(id2str.schoolYear(i) !== id2str.schoolYear(99)) {
            let educationLevelId = Math.floor(i/10);
            educationSchoolYear[i] = {
                id: educationLevelId,
                name: id2str.educationLevelShort(educationLevelId),
            };
        }
    }

    let result = [];
    let educationLevelSet = new Set();
    let schoolYearSet = new Set();
    let i = 0;
    while(i < enrollments.length) {
        let enrollment = enrollments[i];
        let educationLevelHash = '' + enrollment.year + educationSchoolYear[enrollment.school_year_id].id;
        let schoolYearHash = '' + enrollment.year + enrollment.school_year_id;

        let currentEducation = null;
        // Busca ou cria a etada de ensino adequada
        if(educationLevelSet.has(educationLevelHash)) {
            let j = 0;
            let edu = result[j];
            while(j < result.length && (edu.year != enrollment.year || edu.education_level_school_year_id != educationSchoolYear[enrollment.school_year_id].id)) {
                ++j;
                edu = result[j];
            }
            if((j >= result.length)) --j;
            edu = result[j];

            currentEducation = edu;
        } else {
            educationLevelSet.add(educationLevelHash);
            let obj = {
                year: enrollment.year,
                name: enrollment.name,
                education_level_school_year_id: educationSchoolYear[enrollment.school_year_id].id,
                education_level_school_year_name: educationSchoolYear[enrollment.school_year_id].name,
                urban_day_total: 0,
                urban_night_total: 0,
                rural_day_total: 0,
                rural_night_total: 0
            };
            result.push(obj);
            currentEducation = obj;
        }

        let currentSchoolYear = null;
        // Busca ou cria a série adequada
        if(schoolYearSet.has(schoolYearHash)) {
            let j = 0;
            let edu = result[j];
            while(j < result.length && (edu.year != enrollment.year || edu.education_level_school_year_id != enrollment.school_year_id)){
                ++j;
                edu = result[j];
            }
            if(j >= result.length) --j;
            edu = result[j];

            currentSchoolYear = edu;
        } else {
            schoolYearSet.add(schoolYearHash);
            let obj = {
                year: enrollment.year,
                name: enrollment.name,
                education_level_school_year_id: enrollment.school_year_id,
                education_level_school_year_name: enrollment.school_year_name,
                urban_day_total: 0,
                urban_night_total: 0,
                rural_day_total: 0,
                rural_night_total: 0
            };

            result.push(obj);
            currentSchoolYear = obj;
        }

        if(enrollment.location_id == 1) {
            if(enrollment.period_id < 3) {
                currentEducation.urban_day_total += enrollment.total;
                currentSchoolYear.urban_day_total += enrollment.total;
            } else {
                currentEducation.urban_night_total += enrollment.total;
                currentSchoolYear.urban_night_total += enrollment.total;
            }
        } else {
            if(enrollment.period_id < 3) {
                currentEducation.rural_day_total += enrollment.total;
                currentSchoolYear.rural_day_total += enrollment.total;
            } else {
                currentEducation.rural_night_total += enrollment.total;
                currentSchoolYear.rural_night_total += enrollment.total;
            }
        }

        ++i;
    }

    req.result = result;

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

module.exports = enrollmentApp;