spatial.js

const express = require('express');

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

const squel = require('squel');

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

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

const sqlQuery = require(`${libs}/db/query_exec`);

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

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

const spatialApp = express();

let rqf = new ReqQueryFields();

function schoolYearIdToStr(schoolYearId) {
    let schoolYearStr;
    switch(schoolYearId) {
        case 11:
            schoolYearStr = 'Creche';
            break;
        case 21:
            schoolYearStr = 'Pré-escola';
            break;
        case 31:
            schoolYearStr = '1 Ano';
            break;
        case 32:
            schoolYearStr = '2 Ano - 1 Serie';
            break;
        case 33:
            schoolYearStr = '3 Ano - 2 Serie';
            break;
        case 34:
            schoolYearStr = '4 Ano - 3 Serie';
            break;
        case 35:
            schoolYearStr = '5 Ano - 4 Serie';
            break;
        case 41:
            schoolYearStr = '6 Ano - 5 Serie';
            break;
        case 42:
            schoolYearStr = '7 Ano - 6 Serie';
            break;
        case 43:
            schoolYearStr = '8 Ano - 7 Serie';
            break;
        case 44:
            schoolYearStr = '9 Ano - 8 Serie';
            break;
        case 51:
            schoolYearStr = '1 Ano'; // equivalent to 'EM 1 Série'
            break;
        case 52:
            schoolYearStr = '2 Ano'; // equivalent to 'EM 2 Série'
            break;
        case 53:
            schoolYearStr = '3 Ano'; // equivalent to 'EM 3 Série'
            break;
        case 54:
            schoolYearStr = '4 Ano'; // equivalent to 'EM 4 Série'
            break;
        case 61:
            schoolYearStr = 'EJA AI';
            break;
        case 62:
            schoolYearStr = 'EJA AF';
            break;
        case 63:
            schoolYearStr = 'EJA EM';
            break;
        case 64:
            schoolYearStr = 'EJA semi-presencial';
            break;
        case 71:
            schoolYearStr = 'EP';
            break;
        case 81:
            schoolYearStr = 'Atividades complementares e AEE';
            break;
        default:
            schoolYearStr = 'Não classificado';
    }
    return schoolYearStr;
}

function processResultSet(querySet, querySetLabels = ["result"], singleResult = false) {
    const resultMap = new Map();
    let resultIdx = 0;
    // loop relies on the fact that Promise.all maintains the order of the original iterable
    for(let result of querySet) {
        const resultLbl = querySetLabels[resultIdx];
        resultMap[resultLbl] = [];
        if (singleResult) {
            resultMap[resultLbl] = result[0];
        } else {
            for(let row of result) {
                log.debug(row);
                resultMap[resultLbl].push(row);
            }
        }
        resultIdx++;
    }
    log.debug(resultMap);
    return resultMap;
}

function dbExecAll(querySet = []) {
    // Issue all queries concurrently to the database, for every query object in the iterable
    // NOTE: Array.map() returns a copy of the original array with each object 'mapped'.
    return querySet.map((qry) => { return sqlQuery(qry.toString()); });
}

rqf.addField({
    name: 'filter',
    field: true,
    where: true
}).addValue({
    name: 'region',
    table: 'regiao',
    tableField: 'nome',
    resultField: 'region_name',
    where: {
        relation: '=',
        type: 'integer',
        field: 'id'
    },
    join: {
        primary: 'id',
        foreign: 'regiao_id'
    }
}).addValue({
    name: 'state',
    table: 'estado',
    tableField: 'nome',
    resultField: 'state_name',
    where: {
        relation: '=',
        type: 'integer',
        field: 'id'
    },
    join: {
        primary: 'id',
        foreign: 'estado_id'
    }
}).addValue({
    name: 'city',
    table: 'municipio',
    tableField: 'nome',
    resultField: 'city_name',
    where: {
        relation: '=',
        type: 'integer',
        field: 'id'
    },
    join: {
        primary: 'id',
        foreign: 'municipio_id'
    }
}).addValue({
    name: 'school',
    table: 'escola',
    tableField: 'nome_escola',
    resultField: 'school_name',
    where: {
        relation: '=',
        type: 'integer',
        field: 'id'
    }
});

spatialApp.get('/sociodemographic', rqf.parse(), rqf.build(), (req, res, next) => {
    const populationYearQry = squel.select()
        .field('MAX(ibge_populacao.ano_censo)')
        .from('ibge_populacao');

    const populationQry = req.sql.clone()
        .field('\'Brasil\'', 'name')
        .field('SUM(populacao)', 'population')
        .field('ibge_populacao.ano_censo', 'census_year')
        .from('ibge_populacao')
        .where(`ibge_populacao.ano_censo IN (${populationYearQry.toString()})`)
        .group('ibge_populacao.ano_censo');

    const pibYearQry = squel.select()
        .field('MAX(ibge_pib.ano_censo)')
        .from('ibge_pib');

    const pibQry = req.sql.clone()
        .field('\'Brasil\'', 'name')
        .field('AVG(ibge_pib.pib_per_capita)', 'gdp_per_capita')
        .field('ibge_pib.ano_censo', 'census_year')
        .from('ibge_pib')
        .where(`ibge_pib.ano_censo IN (${pibYearQry.toString()})`)
        .group('ibge_pib.ano_censo');

    const idhYearQry = squel.select()
        .field('MAX(adh_idh.ano_censo)')
        .from('adh_idh');

    const idhQry = req.sql.clone()
        .field('\'Brasil\'', 'name')
        .field('AVG(idhm)', 'idhm')
        .field('adh_idh.ano_censo', 'census_year')
        .from('adh_idh')
        .where(`adh_idh.ano_censo IN (${idhYearQry.toString()})`)
        .group('adh_idh.ano_censo');

    const analfabYearQry = squel.select()
        .field('MAX(adh_analfabetismo.ano_censo)')
        .from('adh_analfabetismo');

    const analfabQry = req.sql.clone()
        .field('\'Brasil\'', 'name')
        .field('AVG(t_analf15m)', 'analfabetism')
        .field('adh_analfabetismo.ano_censo', 'census_year')
        .from('adh_analfabetismo')
        .where(`adh_analfabetismo.ano_censo IN (${analfabYearQry.toString()})`)
        .group('adh_analfabetismo.ano_censo');

    const giniYearQry = squel.select()
        .field('MAX(adh_gini.ano_censo)')
        .from('adh_gini');

    const giniQry = req.sql.clone()
        .field('\'Brasil\'', 'name')
        .field('AVG(gini)', 'gini')
        .field('adh_gini.ano_censo', 'census_year')
        .from('adh_gini')
        .where(`adh_gini.ano_censo IN (${giniYearQry.toString()})`)
        .group('adh_gini.ano_censo');

    // map query objects to their respective response labels
    const queryLabels = [ "population", "gdp", "idh", "analfab", "gini" ];
    const querySet = [ populationQry, pibQry, idhQry, analfabQry, giniQry ];
    // wait until all queries finish or one of them fail
    Promise.all(dbExecAll(querySet)).then((queryResults) => {
        req.result = processResultSet(queryResults, queryLabels, true);
        next();
    }).catch((error) => {
        log.error(`[SQL query error] ${error}`);
        next(error);
    });
}, response('spatial'));

spatialApp.get('/educational', rqf.parse(), rqf.build(), (req, res, next) => {
    const censusYearQry = squel.select()
        .field('MAX(escola.ano_censo)', 'ano_censo')
        .from('escola')
        .toString();

    const totalSchoolsQry = req.sql.clone()
        .field('\'Brasil\'', 'name')
        .field('\'Total\'', 'location_name')
        .field('COUNT(DISTINCT(escola.id))', 'total')
        .field('escola.ano_censo', 'census_year')
        .from('turma')
        .from('escola')
        .where('escola.ano_censo=turma.ano_censo AND escola.id=turma.escola_id')
        .where(`escola.ano_censo IN (${censusYearQry})`)
        .where('turma.tipo_turma_id = 0')
        .group('escola.ano_censo');

    const schoolsPerLocationQry = req.sql.clone()
        .field('\'Brasil\'', 'name')
        .field('COUNT(DISTINCT(escola.id))', 'total')
        .field('escola.ano_censo', 'census_year')
        .field('localizacao.descricao', 'location_name')
        .from('localizacao')
        .from('turma')
        .from('escola')
        .where('escola.cod_localizacao=localizacao.id')
        .where('escola.ano_censo=turma.ano_censo AND escola.id=turma.escola_id')
        .where(`escola.ano_censo IN (${censusYearQry})`)
        .where('turma.tipo_turma_id = 0')
        .group('escola.cod_localizacao')
        .group('escola.ano_censo')
        .group('localizacao.descricao')
        .order('localizacao.descricao');

    const schoolsPerAdmDependencyQry = req.sql.clone()
        .field('\'Brasil\'', 'name')
        .field('COUNT(DISTINCT(escola.id))', 'total')
        .field('escola.ano_censo', 'census_year')
        .field('dependencia_adm.nome', 'adm_dependency_name')
        .from('dependencia_adm')
        .from('escola')
        .where('escola.dependencia_adm_id=dependencia_adm.id')
        .where(`escola.ano_censo IN (${censusYearQry})`)
        .group('escola.ano_censo')
        .group('dependencia_adm.nome')
        .order('escola.ano_censo')
        .order('dependencia_adm.nome');

    const schoolClassYearQry = squel.select()
        .field('MAX(matricula.ano_censo)')
        .from('matricula')
        .toString();

    const enrollmentsQry = req.sql.clone()
        .field('\'Brasil\'', 'name')
        .field('COALESCE(COUNT(matricula.id), 0)', 'total')
        .field('matricula.ano_censo', 'census_year')
        .from('matricula')
        .where(`matricula.ano_censo IN (${schoolClassYearQry})`)
        .where('matricula.tipo<=3')
        .group('matricula.ano_censo');

    const enrollmentsPerAdmDepQry = req.sql.clone()
        .field('\'Brasil\'', 'name')
        .field('COALESCE(COUNT(matricula.id), 0)', 'total')
        .field('matricula.ano_censo', 'census_year')
        .field('dependencia_adm.nome', 'adm_dependency_name')
        .from('dependencia_adm')
        .from('matricula')
        .where('matricula.dependencia_adm_id=dependencia_adm.id')
        .where('matricula.tipo <= 3')
        .where(`matricula.ano_censo IN (${schoolClassYearQry})`)
        .group('matricula.ano_censo')
        .group('dependencia_adm.nome')
        .order('dependencia_adm.nome');

    const enrollmentsPerSchoolLevelQry = req.sql.clone()
        .field('\'Brasil\'', 'name')
        .field('COALESCE(COUNT(matricula.id), 0)', 'total')
        .field('matricula.ano_censo', 'census_year')
        .field('etapa_ensino.desc_etapa', 'school_level_name')
        .from('etapa_ensino')
        .from('matricula')
        .where('matricula.etapa_ensino_id=etapa_ensino.id')
        .where('matricula.tipo <= 3')
        .where(`matricula.ano_censo IN (${schoolClassYearQry})`)
        .group('matricula.ano_censo')
        .group('etapa_ensino.desc_etapa')
        .group('etapa_ensino.id')
        .order('etapa_ensino.id');

    const enrollmentsPerLocationQry = req.sql.clone()
        .field('\'Brasil\'', 'name')
        .field('COALESCE(COUNT(matricula.id), 0)', 'total')
        .field('matricula.ano_censo', 'census_year')
        .field('localizacao.descricao', 'location_name')
        .from('localizacao')
        .from('matricula')
        .where('matricula.localizacao_id=localizacao.id')
        .where('matricula.tipo <= 3')
        .where(`matricula.ano_censo IN (${schoolClassYearQry})`)
        .group('matricula.ano_censo')
        .group('localizacao.descricao')
        .order('ano_censo')
        .order('localizacao.descricao');

    const queryLabels = [ "school", "school_per_location", "school_per_adm_dependency", "enrollment", "enrollment_per_adm_dep",
        "enrollment_per_school_level", "enrollment_per_location" ];
    const querySet = [ totalSchoolsQry, schoolsPerLocationQry, schoolsPerAdmDependencyQry, enrollmentsQry,
        enrollmentsPerAdmDepQry, enrollmentsPerSchoolLevelQry, enrollmentsPerLocationQry];
    // wait until all queries finish or one of them fail
    Promise.all(dbExecAll(querySet)).then((queryResults) => {
        req.result = processResultSet(queryResults, queryLabels);
        next();
    }).catch((error) => {
        log.error(`[SQL query error] ${error}`);
        next(error);
    });
}, response('spatial'));

spatialApp.get('/educational/school_level', rqf.parse(), rqf.build(), (req, res, next) => {
    const enrollmentsPerSchoolLevelYearQry = squel.select()
        .field('MAX(matricula.ano_censo)', 'census_year')
        .from('matricula');

    const enrollmentsPerSchoolLevelQry = req.sql.clone()
        .field('COALESCE(COUNT(matricula.id), 0)', 'total')
        .field('matricula.ano_censo', 'census_year')
        .field('matricula.serie_ano_id', 'school_year')
        .field('etapa_ensino.desc_etapa', 'school_level')
        .from('etapa_ensino')
        .from('matricula')
        .where(`matricula.ano_censo IN (${enrollmentsPerSchoolLevelYearQry.toString()})`)
        .where('matricula.etapa_ensino_id = etapa_ensino.id')
        .where('matricula.tipo <= 3')
        .group('etapa_ensino.desc_etapa')
        .group('etapa_ensino.id')
        .group('matricula.serie_ano_id')
        .group('matricula.ano_censo')
        .order('etapa_ensino.id')
        .order('matricula.serie_ano_id')
        .order('matricula.ano_censo');

    const queryLabels = [ 'enrollment_per_school_level', 'enrollment_census_year' ];
    const querySet = [ enrollmentsPerSchoolLevelQry, enrollmentsPerSchoolLevelYearQry ];
    // wait until all queries finish or one of them fail
    Promise.all(dbExecAll(querySet, enrollmentsPerSchoolLevelYearQry)).then((queryResults) => {
        const result = queryResults[0];
        const censusYear = queryResults[1][0]['census_year'];

        let school_levels = {};
        for(let i = 0; i < result.length; ++i) {
            const school_year  = schoolYearIdToStr(result[i].school_year);
            const school_level = result[i].school_level;
            const census_year = result[i].census_year;
            if (typeof school_levels[school_level] === 'undefined') {
                school_levels[school_level] = {};
            }
            school_levels[school_level][school_year] = parseInt(result[i].total, 10);
        }

        let response = [];
        for(let level in school_levels) {
            if (school_levels.hasOwnProperty(level)) {
                let sclevel = {};
                sclevel["degree"] = level;
                sclevel["census_year"] = parseInt(censusYear, 10);
                sclevel["table"] = [];
                for(let school_year in school_levels[level]) {
                    if (school_levels[level].hasOwnProperty(school_year)) {
                        let enrollment = { 'title' : school_year,
                                           'value' : school_levels[level][school_year] };
                        sclevel["table"].push(enrollment);
                    }
                }
                response.push(sclevel);
            }
        }
        req.result = response;
        next();
    }).catch((error) => {
        log.error(`[SQL query error] ${error}`);
        next(error);
    });
}, response('spatial'));

module.exports = spatialApp;