school_infrastructure.js

const express = require('express');

const infrastructureApp = 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 multiQuery = require(`${libs}/middlewares/multiQuery`);

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

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

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

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

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

let rqf = new ReqQueryFields();

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

infrastructureApp.get('/year_range', (req, res, next) => {
    req.sql.from('escola')
    .field('MIN(escola.ano_censo)', 'start_year')
    .field('MAX(escola.ano_censo)', 'end_year');
    next();
}, query, response('range'));

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

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

infrastructureApp.get('/location', (req, res, next) => {
    req.result = [
        {id: 1, name: 'Urbana'},
        {id: 2, name: 'Rural'}
    ];
    next();
}, response('location'));

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

infrastructureApp.get('/adm_dependency', (req, res, next) => {
    req.sql.from('dependencia_adm')
    .field('id')
    .field('nome', 'name')
    .where('id <= 4');
    next();
}, query, response('adm_dependency'));

infrastructureApp.get('/adm_dependency_detailed', (req, res, next) => {
    req.sql.from('dependencia_adm_priv')
    .field('id', 'id')
    .field('nome', 'name');
    next();
}, query, response('adm_dependency_detailed'));

rqf.addField({
    name: 'filter',
    field: false,
    where: true
}).addField({
    name: 'dims',
    field: true,
    where: false
}).addValueToField({
    name: 'city',
    table: 'municipio',
    tableField: ['nome', 'id'],
    resultField: ['city_name', 'city_id'],
    where: {
        relation: '=',
        type: 'integer',
        field: 'municipio_id',
        table: 'escola'
    },
    join: {
        primary: 'id',
        foreign: 'municipio_id',
        foreignTable: 'escola'
    }
}, 'dims').addValueToField({
    name: 'city',
    table: 'municipio',
    tableField: 'id',
    resultField: 'city_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'municipio_id',
        table: 'escola'
    },
    join: {
        primary: 'id',
        foreign: 'municipio_id',
        foreignTable: 'escola'
    }
}, 'filter').addValueToField({
    name: 'state',
    table: 'estado',
    tableField: ['nome', 'id'],
    resultField: ['state_name', 'state_id'],
    where: {
        relation: '=',
        type: 'integer',
        field: 'estado_id',
        table: 'escola'
    },
    join: {
        primary: 'id',
        foreign: 'estado_id',
        foreignTable: 'escola'
    }
}, 'dims').addValueToField({
    name: 'state',
    table: 'estado',
    tableField: 'id',
    resultField: 'state_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'estado_id',
        table: 'escola'
    },
    join: {
        primary: 'id',
        foreign: 'estado_id',
        foreignTable: 'escola'
    }
}, 'filter').addValue({
    name: 'region',
    table: 'regiao',
    tableField: 'nome',
    resultField: 'region_name',
    where: {
        relation: '=',
        type: 'integer',
        field: 'id'
    },
    join: {
        primary: 'id',
        foreign: 'regiao_id',
        foreignTable: 'escola'
    }
}).addValue({
    name: 'location',
    table: 'escola',
    tableField: 'cod_localizacao',
    resultField: 'location_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'cod_localizacao'
    }
}).addValue({
    name: 'rural_location',
    table: 'escola',
    tableField: 'localidade_area_rural',
    resultField: 'rural_location_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'localidade_area_rural'
    }
}).addValue({
    name: 'adm_dependency',
    table: 'escola',
    tableField: 'dependencia_adm_id',
    resultField: 'adm_dependency_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'dependencia_adm_id'
    }
}).addValue({
    name: 'adm_dependency_detailed',
    table: 'escola',
    tableField: 'dependencia_adm_priv',
    resultField: 'adm_dependency_detailed_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'dependencia_adm_priv'
    }
}).addValue({
    name: 'min_year',
    table: 'escola',
    tableField: 'ano_censo',
    resultField: 'year',
    where: {
        relation: '>=',
        type: 'integer',
        field: 'ano_censo'
    }
}).addValue({
    name: 'max_year',
    table: 'escola',
    tableField: 'ano_censo',
    resultField: 'year',
    where: {
        relation: '<=',
        type: 'integer',
        field: 'ano_censo'
    }
});

function matchQueries(queryTotal, queryPartial) {
    let match = [];
    queryTotal.forEach((result) => {
        let newObj = {};
        let keys = Object.keys(result);
        keys.forEach((key) => {
            newObj[key] = result[key];
        });
        let index = keys.indexOf('total');
        if(index > -1) keys.splice(index, 1);
        let objMatch = null;

        for(let i = 0; i < queryPartial.length; ++i) {
            let partial = queryPartial[i];
            let foundMatch = true;
            for(let j = 0; j < keys.length; ++j) {
                let key = keys[j];
                if(partial[key] !== result[key]) {
                    foundMatch = false;
                    break;
                }
            }
            if(foundMatch) {
                objMatch = partial;
                break;
            }
        }

        if(objMatch) {
            newObj.percentage = (objMatch.total / result.total) * 100;
            newObj.partial = objMatch.total;
            newObj.total = result.total
            match.push(newObj);
        }
    });

    return match;
}

infrastructureApp.get('/', rqf.parse(), rqf.build(), (req, res, next) => {
    req.querySet = [];
    req.queryIndex = {};

    // Local de funcionamento
    let allSchools = req.sql.clone();
    allSchools.from('escola').field('COUNT(escola.id)', 'total')
    .field("'Brasil'", 'name')
    .field('escola.ano_censo', 'year')
    .group('escola.ano_censo')
    .where('escola.situacao_de_funcionamento = 1')
    .order('escola.ano_censo');
    req.queryIndex.allSchools = req.querySet.push(allSchools) - 1;

    let schoolPlace = allSchools.clone();
    schoolPlace.where('escola.func_predio_escolar = 1 AND escola.func_salas_empresa = 0 AND escola.func_templo_igreja = 0 AND escola.func_casa_professor = 0 AND escola.func_galpao = 0 AND escola.biblioteca = 1');
    req.queryIndex.schoolPlace = req.querySet.push(schoolPlace) - 1;

    // Bibliotecas
    let allLibraries = allSchools.clone();
    allLibraries.where('escola.func_predio_escolar = 1 AND escola.cod_localizacao = 1');
    req.queryIndex.allLibraries = req.querySet.push(allLibraries) - 1;

    let haveLibraries = allLibraries.clone();
    haveLibraries.where('escola.biblioteca = 1');
    req.queryIndex.haveLibraries = req.querySet.push(haveLibraries) - 1;

    // Bibliotecas/Sala de leitura
    let allLibrariesReadingRoom = allSchools.clone();
    allLibrariesReadingRoom.where('escola.func_predio_escolar = 1 AND escola.cod_localizacao = 2');
    req.queryIndex.allLibrariesReadingRoom = req.querySet.push(allLibrariesReadingRoom) - 1;

    let haveLibrariesReadingRoom = allLibrariesReadingRoom.clone();
    haveLibrariesReadingRoom.where('escola.sala_leitura = 1');
    req.queryIndex.haveLibrariesReadingRoom = req.querySet.push(haveLibrariesReadingRoom) - 1;

    // Laboratório de informática
    let allInfLab = allSchools.clone();
    allInfLab.where('escola.func_predio_escolar = 1')
    .where('escola.reg_fund_ai = 1 OR escola.reg_fund_af = 1 OR escola.reg_medio_medio = 1 OR escola.reg_medio_integrado = 1 OR escola.reg_medio_normal = 1 OR escola.ensino_eja_fund = 1 OR escola.ensino_eja_medio = 1 OR escola.ensino_eja_prof = 1');
    req.queryIndex.allInfLab = req.querySet.push(allInfLab) - 1;

    let haveInfLab = allInfLab.clone();
    haveInfLab.where('escola.lab_informatica = 1');
    req.queryIndex.haveInfLab = req.querySet.push(haveInfLab) - 1;

    // Laboratório de ciências
    let allScienceLab = allInfLab.clone();
    req.queryIndex.allScienceLab = req.querySet.push(allScienceLab) - 1;

    let haveScienceLab = allScienceLab.clone();
    haveScienceLab.where('escola.lab_ciencias = 1');
    req.queryIndex.haveScienceLab = req.querySet.push(haveScienceLab) - 1;

    // Parque infantil
    let allKidsPark = allSchools.clone();
    allKidsPark.where('escola.func_predio_escolar = 1')
    .where('escola.reg_infantil_creche = 1 OR escola.reg_infantil_preescola = 1 OR escola.reg_fund_ai = 1 OR escola.esp_infantil_creche = 1 OR escola.esp_exclusiva_creche = 1 OR escola.reg_esp_exclusiva_fund_ai = 1');
    req.queryIndex.allKidsPark = req.querySet.push(allKidsPark) - 1;

    let haveKidsPark = allKidsPark.clone();
    haveKidsPark.where('escola.parque_infantil = 1');
    req.queryIndex.haveKidsPark = req.querySet.push(haveKidsPark) - 1;

    // Berçário
    let allCribs = allSchools.clone();
    allCribs.where('escola.func_predio_escolar = 1')
    .where('escola.reg_infantil_creche = 1 OR escola.esp_infantil_creche = 1');
    req.queryIndex.allCribs = req.querySet.push(allCribs) - 1;

    let haveCribs = allCribs.clone();
    haveCribs.where('escola.bercario = 1');
    req.queryIndex.haveCribs = req.querySet.push(haveCribs) - 1;

    // Quadra
    let allSportsCourt = allScienceLab.clone();
    allSportsCourt.where('escola.cod_localizacao = 1');
    req.queryIndex.allSportsCourt = req.querySet.push(allSportsCourt) - 1;

    let haveSportsCourt = allSportsCourt.clone();
    haveSportsCourt.where('escola.quadra_esportes = 1');
    req.queryIndex.haveSportsCourt = req.querySet.push(haveSportsCourt) - 1;

    // Quadra coberta
    req.queryIndex.allCoveredSportsCourt = req.queryIndex.allSportsCourt;

    let haveCoveredSportsCourt = allSportsCourt.clone();
    haveCoveredSportsCourt.where('escola.quadra_esportes_coberta = 1');
    req.queryIndex.haveCoveredSportsCourt = req.querySet.push(haveCoveredSportsCourt) - 1;

    // Quadra Descoberta
    let allUncoveredSportsCourt = allSportsCourt.clone();
    allUncoveredSportsCourt.where('escola.quadra_esportes_coberta = 0');
    req.queryIndex.allUncoveredSportsCourt = req.querySet.push(allUncoveredSportsCourt) - 1;

    let haveUncoveredSportsCourt = allUncoveredSportsCourt.clone();
    haveUncoveredSportsCourt.where('escola.quadra_esportes_descoberta = 1');
    req.queryIndex.haveUncoveredSportsCourt = req.querySet.push(haveUncoveredSportsCourt) - 1;

    // Sala de direção
    let allDirectorRoom = allSchools.clone();
    allDirectorRoom.where('escola.func_predio_escolar = 1 AND escola.cod_localizacao = 1');
    req.queryIndex.allDirectorRoom = req.querySet.push(allDirectorRoom) - 1;

    let haveDirectorRoom = allDirectorRoom.clone();
    haveDirectorRoom.where('escola.sala_diretoria = 1');
    req.queryIndex.haveDirectorRoom = req.querySet.push(haveDirectorRoom) - 1;

    // Secretaria
    let allSecretary = allSchools.clone();
    allSecretary.where('escola.func_predio_escolar = 1');
    req.queryIndex.allSecretary = req.querySet.push(allSecretary) - 1;

    let haveSecretary = allSecretary.clone();
    haveSecretary.where('escola.secretaria = 1');
    req.queryIndex.haveSecretary = req.querySet.push(haveSecretary) - 1;

    // Sala de professores
    req.queryIndex.allTeacherRoom = req.queryIndex.allSecretary;

    let haveTeacherRoom = allSecretary.clone();
    haveTeacherRoom.where('escola.sala_professor = 1');
    req.queryIndex.haveTeacherRoom = req.querySet.push(haveTeacherRoom) - 1;

    // Cozinha
    req.queryIndex.allKitchen = req.queryIndex.allSecretary;

    let haveKitchen = allSecretary.clone();
    haveKitchen.where('escola.cozinha = 1');
    req.queryIndex.haveKitchen = req.querySet.push(haveKitchen) - 1;

    // Despensa
    req.queryIndex.allStoreroom = req.queryIndex.allSecretary;

    let haveStoreroom = allSecretary.clone();
    haveStoreroom.where('escola.despensa = 1');
    req.queryIndex.haveStoreroom = req.querySet.push(haveStoreroom) - 1;

    // Almoxarifado
    req.queryIndex.allWarehouse = req.queryIndex.allSecretary;

    let haveWarehouse = allSecretary.clone();
    haveWarehouse.where('escola.almoxarifado = 1');
    req.queryIndex.haveWarehouse = req.querySet.push(haveWarehouse) - 1;

    // Internet
    req.queryIndex.allInternet = req.queryIndex.allLibrariesReadingRoom;

    let haveInternet = allLibrariesReadingRoom.clone();
    haveInternet.where('escola.internet = 1');
    req.queryIndex.haveInternet = req.querySet.push(haveInternet) - 1;

    // Internet banda larga
    req.queryIndex.allBroadbandInternet = req.queryIndex.allLibraries;

    let haveBroadbandInternet = allLibraries.clone();
    haveBroadbandInternet.where('escola.internet_banda_larga = 1');
    req.queryIndex.haveBroadbandInternet = req.querySet.push(haveBroadbandInternet) - 1;

    // Banheiro dentro do prédio
    req.queryIndex.allInsideBathroom = req.queryIndex.allSecretary;

    let haveInsideBathroom = allSecretary.clone();
    haveInsideBathroom.where('escola.sanitario_dentro_predio = 1');
    req.queryIndex.haveInsideBathroom = req.querySet.push(haveInsideBathroom) - 1;

    // Banheiro adequado para educação infantil dentro do prédio
    req.queryIndex.allInsideKidsBathroom = req.queryIndex.allKidsPark;

    let haveInsideKidsBathroom = allKidsPark.clone();
    haveInsideKidsBathroom.where('escola.sanitario_ei = 1');
    req.queryIndex.haveInsideKidsBathroom = req.querySet.push(haveInsideKidsBathroom) - 1;

    // Fornecimento de energia
    req.queryIndex.allEletricEnergy = req.queryIndex.allSecretary;

    let haveEletricEnergy = allSecretary.clone();
    haveEletricEnergy.where('escola.fornecimento_energia = 1');
    req.queryIndex.haveEletricEnergy = req.querySet.push(haveEletricEnergy) - 1;

    // Abastecimento de água
    req.queryIndex.allWaterSupply = req.queryIndex.allSecretary;

    let haveWaterSupply = allSecretary.clone();
    haveWaterSupply.where('escola.fornecimento_agua = 1');
    req.queryIndex.haveWaterSupply = req.querySet.push(haveWaterSupply) - 1;

    // Água filtrada
    req.queryIndex.allFilteredWater = req.queryIndex.allSecretary;

    let haveFilteredWater = allSecretary.clone();
    haveFilteredWater.where('escola.agua_filtrada = 1');
    req.queryIndex.haveFilteredWater = req.querySet.push(haveFilteredWater) - 1;

    // Coleta de esgoto
    req.queryIndex.allSewage = req.queryIndex.allSecretary;

    let haveSewage = allSecretary.clone();
    haveSewage.where('escola.esgoto_sanitario = 1');
    req.queryIndex.haveSewage = req.querySet.push(haveSewage) - 1;

    // Sala de recursos multifuncionais para Atendimento Educacional Especializado
    req.queryIndex.allMultifunctionRoom = req.queryIndex.allSecretary;

    let haveMultifunctionRoom = allSecretary.clone();
    haveMultifunctionRoom.where('escola.sala_atendimento_especial = 1');
    req.queryIndex.haveMultifunctionRoom = req.querySet.push(haveMultifunctionRoom) - 1;

    // Banheiros adaptados para pessoas com deficiências
    req.queryIndex.allSpecialBathroom = req.queryIndex.allSecretary;

    let haveSpecialBathroom = allSecretary.clone();
    haveSpecialBathroom.where('escola.sanitario_pne = 1');
    req.queryIndex.haveSpecialBathroom = req.querySet.push(haveSpecialBathroom) - 1;

    // Dependências adaptada para pessoas com deficiências
    req.queryIndex.allAdaptedBuilding = req.queryIndex.allSecretary;

    let haveAdaptedBuilding = allSecretary.clone();
    haveAdaptedBuilding.where('escola.dependencias_pne = 1');
    req.queryIndex.haveAdaptedBuilding = req.querySet.push(haveAdaptedBuilding) - 1;

    next();
}, multiQuery, (req, res, next) => {
    // Faz o matching entre os resultados
    let school_place = matchQueries(req.result[req.queryIndex.allSchools], req.result[req.queryIndex.schoolPlace]);
    let libraries = matchQueries(req.result[req.queryIndex.allLibraries], req.result[req.queryIndex.haveLibraries]);
    let libraries_reading_room = matchQueries(req.result[req.queryIndex.allLibrariesReadingRoom], req.result[req.queryIndex.haveLibrariesReadingRoom]);
    let computer_lab = matchQueries(req.result[req.queryIndex.allInfLab], req.result[req.queryIndex.haveInfLab]);
    let science_lab = matchQueries(req.result[req.queryIndex.allScienceLab], req.result[req.queryIndex.haveScienceLab]);
    let kids_park = matchQueries(req.result[req.queryIndex.allKidsPark], req.result[req.queryIndex.haveKidsPark]);
    let nursery = matchQueries(req.result[req.queryIndex.allCribs], req.result[req.queryIndex.haveCribs]);
    let sports_court = matchQueries(req.result[req.queryIndex.allSportsCourt], req.result[req.queryIndex.haveSportsCourt]);
    let covered_sports_court = matchQueries(req.result[req.queryIndex.allCoveredSportsCourt], req.result[req.queryIndex.haveCoveredSportsCourt]);
    let uncovered_sports_court = matchQueries(req.result[req.queryIndex.allUncoveredSportsCourt], req.result[req.queryIndex.haveUncoveredSportsCourt]);
    let director_room = matchQueries(req.result[req.queryIndex.allDirectorRoom], req.result[req.queryIndex.haveDirectorRoom]);
    let secretary = matchQueries(req.result[req.queryIndex.allSecretary], req.result[req.queryIndex.haveSecretary]);
    let teacher_room = matchQueries(req.result[req.queryIndex.allTeacherRoom], req.result[req.queryIndex.haveTeacherRoom]);
    let kitchen = matchQueries(req.result[req.queryIndex.allKitchen], req.result[req.queryIndex.haveKitchen]);
    let storeroom = matchQueries(req.result[req.queryIndex.allStoreroom], req.result[req.queryIndex.haveStoreroom]);
    let warehouse = matchQueries(req.result[req.queryIndex.allWarehouse], req.result[req.queryIndex.haveWarehouse]);
    let internet = matchQueries(req.result[req.queryIndex.allInternet], req.result[req.queryIndex.haveInternet]);
    let broadband_internet = matchQueries(req.result[req.queryIndex.allBroadbandInternet], req.result[req.queryIndex.haveBroadbandInternet]);
    let inside_bathroom = matchQueries(req.result[req.queryIndex.allInsideBathroom], req.result[req.queryIndex.haveInsideBathroom]);
    let inside_kids_bathroom = matchQueries(req.result[req.queryIndex.allInsideKidsBathroom], req.result[req.queryIndex.haveInsideKidsBathroom]);
    let eletric_energy = matchQueries(req.result[req.queryIndex.allEletricEnergy], req.result[req.queryIndex.haveEletricEnergy]);
    let water_supply = matchQueries(req.result[req.queryIndex.allWaterSupply], req.result[req.queryIndex.haveWaterSupply]);
    let filtered_water = matchQueries(req.result[req.queryIndex.allFilteredWater], req.result[req.queryIndex.haveFilteredWater]);
    let sewage_treatment = matchQueries(req.result[req.queryIndex.allSewage], req.result[req.queryIndex.haveSewage]);
    let special_multifunction_room = matchQueries(req.result[req.queryIndex.allMultifunctionRoom], req.result[req.queryIndex.haveMultifunctionRoom]);
    let special_bathroom = matchQueries(req.result[req.queryIndex.allSpecialBathroom], req.result[req.queryIndex.haveSpecialBathroom]);
    let adapted_building = matchQueries(req.result[req.queryIndex.allAdaptedBuilding], req.result[req.queryIndex.haveAdaptedBuilding]);

    req.result = [{
        school_place,
        libraries,
        libraries_reading_room,
        computer_lab,
        science_lab,
        kids_park,
        nursery,
        sports_court,
        covered_sports_court,
        uncovered_sports_court,
        director_room,
        secretary,
        teacher_room,
        kitchen,
        storeroom,
        warehouse,
        internet,
        broadband_internet,
        inside_bathroom,
        inside_kids_bathroom,
        eletric_energy,
        water_supply,
        filtered_water,
        sewage_treatment,
        special_multifunction_room,
        special_bathroom,
        adapted_building
    }];

    next();
}, id2str.multitransform(false), response('infrastructure'));

module.exports = infrastructureApp;