schoolInfrastructure.js

/*
Copyright (C) 2016 Centro de Computacao Cientifica e Software Livre
Departamento de Informatica - Universidade Federal do Parana - C3SL/UFPR

This file is part of simcaq-node.

simcaq-node is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

simcaq-node is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with simcaq-node.  If not, see <https://www.gnu.org/licenses/>.
*/

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.result = [];
    for(let i = 1; i <= 4; ++i) {
        req.result.push({
            id: i,
            name: id2str.admDependency(i)
        });
    };
    next();
}, response('adm_dependency'));

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

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: 'localizacao_id',
    resultField: 'location_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'localizacao_id'
    }
}).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, queryNeeded, zeroPercentage=false) {
    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 partialMatch = null;
        let needMatch = 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) {
                partialMatch = partial;
                break;
            }
        }
        if(queryPartial.length == 0) {
            partialMatch = JSON.parse(JSON.stringify(result));
            partialMatch.total = 0;
        }

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

        if(queryNeeded.length == 0) {
            needMatch = JSON.parse(JSON.stringify(result));
            needMatch.total = 0;
        }

        newObj.percentage = ( ( partialMatch ? partialMatch.total : 0) / result.total) * 100;
		if(zeroPercentage) newObj.percentage = 0;
		newObj.partial = ( partialMatch ? partialMatch.total : 0);
		newObj.total = result.total;
		newObj.need_adaptation = needMatch ? needMatch.total : 0;
		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')
    .where('escola.ensino_regular = 1 OR escola.ensino_eja = 1 OR escola.educacao_profissional = 1')
    .where('escola.local_func_predio_escolar = 1')
    .where('escola.dependencia_adm_id <= 3')
    .order('escola.ano_censo');
    req.queryIndex.allSchools = req.querySet.push(allSchools) - 1;

    let allUrbanSchools = allSchools.clone();
    allUrbanSchools.where('escola.localizacao_id = 1');
    req.queryIndex.allUrbanSchools = req.querySet.push(allUrbanSchools) - 1;

    let allCountrySchools = allSchools.clone();
    allCountrySchools.where('escola.localizacao_id = 2');
    req.queryIndex.allCountrySchools = req.querySet.push(allCountrySchools) - 1;

    let allSchoolsNotSchoolBuilding = req.sql.clone();
    allSchoolsNotSchoolBuilding.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')
    .where('escola.ensino_regular = 1 OR escola.ensino_eja = 1 OR escola.educacao_profissional = 1')
    .where('escola.local_func_predio_escolar = 0')
    .where('escola.dependencia_adm_id <= 3')
    .order('escola.ano_censo');
    req.queryIndex.allSchoolsNotSchoolBuilding = req.querySet.push(allSchoolsNotSchoolBuilding) - 1;

    // Bibliotecas
    // Se (situacao_de_funcionamento=1) & (dependencia_adm_id<=3) & (CEBES027P1=1) & 
    // (ensino_regular=1 | ensino_eja=1 |    educacao_profissional=1) & 
    // (reg_fund_ai_t1=1 | reg_fund_af_t1=1 | reg_medio_medio_t1=1 | ensino_eja_fund= 1 |  
    // ensino_eja_medio= 1 | ensino_eja_prof= 1 | esp_eja_fund=1 | esp_eja_medio=1 | ensino_esp_exclusiva_eja_prof=1) & (localizacao_id=1) então conta id
    let allLibraries = allUrbanSchools.clone();
    allLibraries.where('reg_fund_ai_t1=1 OR reg_fund_af_t1=1 OR reg_medio_medio_t1=1 OR ensino_eja_fund= 1 OR ensino_eja_medio= 1 OR ensino_eja_prof= 1 OR esp_eja_fund=1 OR esp_eja_medio=1 OR ensino_esp_exclusiva_eja_prof=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;

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

    // Bibliotecas/Sala de leitura
    // Se (situacao_de_funcionamento=1) & (dependencia_adm_id<=3) & (CEBES027P1=1) & (ensino_regular=1 | ensino_eja=1 | educacao_profissional=1) & 
    // (reg_fund_ai_t1=1 | reg_fund_af_t1=1 | reg_medio_medio_t1=1 | ensino_eja_fund= 1 |  ensino_eja_medio= 1 | 
    // ensino_eja_prof= 1 | esp_eja_fund=1 | esp_eja_medio=1 | ensino_esp_exclusiva_eja_prof=1) & (localizacao_id=2) então conta id
    let allLibrariesReadingRoom = allCountrySchools.clone();
    allLibrariesReadingRoom.where('reg_fund_ai_t1=1 OR reg_fund_af_t1=1 OR reg_medio_medio_t1=1 OR ensino_eja_fund=1 OR ensino_eja_medio=1 OR ensino_eja_prof=1 OR esp_eja_fund=1 OR esp_eja_medio=1 OR ensino_esp_exclusiva_eja_prof=1');
    req.queryIndex.allLibrariesReadingRoom = req.querySet.push(allLibrariesReadingRoom) - 1;

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

    let needLibrariesReadingRoom = allLibrariesReadingRoom.clone();
    needLibrariesReadingRoom.where('escola.biblioteca_sala_leitura = false');
    req.queryIndex.needLibrariesReadingRoom = req.querySet.push(needLibrariesReadingRoom) - 1;

    // Laboratório de informática
    // Se (situacao_de_funcionamento=1) & (dependencia_adm_id<=3) & (CEBES027P1=1) & 
    // (ensino_regular=1 | ensino_eja=1 |    educacao_profissional=1) & 
    // ( reg_fund_ai_t1=1 | reg_fund_af_t1=1 | reg_medio_medio_t1=1 | ensino_eja_fund= 1 |     ensino_eja_medio= 1 | ensino_eja_prof= 1 | esp_eja_fund=1 | 
    // esp_eja_medio=1 | ensino_esp_exclusiva_eja_prof=1) então conta id
    let allInfLab = allSchools.clone();
    allInfLab.where('reg_fund_ai_t1=1 OR reg_fund_af_t1=1 OR reg_medio_medio_t1=1 OR ensino_eja_fund=1 OR ensino_eja_medio=1 OR ensino_eja_prof=1 OR esp_eja_fund=1 OR esp_eja_medio=1 OR ensino_esp_exclusiva_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;

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

    // Laboratório de ciências
    let allScienceLab = allSchools.clone();
    allScienceLab.where('reg_fund_af_t1=1 OR reg_medio_medio_t1=1 OR ensino_eja_fund=1 OR ensino_eja_medio=1 OR ensino_eja_prof=1 OR esp_eja_fund=1 OR esp_eja_medio=1 OR ensino_esp_exclusiva_eja_prof=1');
    req.queryIndex.allScienceLab = req.querySet.push(allScienceLab) - 1;

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

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

    // Parque infantil
    // Se (situacao_de_funcionamento=1) and (ensino_regular=1 OR ensino_eja=1 OR educacao_profissional=1) and 
    // (local_func_predio_escolar=1) and (dependencia_adm_id<=3) and (reg_infantil_creche_t1=1 or reg_infantil_preescola_t1=1 or reg_fund_ai_t1=1) então conta id
    let allKidsPark = allSchools.clone();
    allKidsPark.where('reg_infantil_creche_t1=1 OR reg_infantil_preescola_t1=1 OR reg_fund_ai_t1=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;

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

    // Berçário
    let allCribs = allSchools.clone();
    allCribs.where('escola.reg_infantil_creche_t1 = 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;

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

    // Quadra Coberta
    let allSportsCourt = allSchools.clone();
    allSportsCourt.where('reg_fund_ai_t1=1  or reg_fund_af_t1=1 or  reg_medio_medio_t1=1  or  ensino_eja_fund= 1 or ensino_eja_medio= 1 or ensino_eja_prof= 1 or esp_eja_fund=1 or esp_eja_medio=1 or ensino_esp_exclusiva_eja_prof=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;

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

    // Cobertura de quadra esportiva
    // Se (situacao_de_funcionamento=1) and (ensino_regular=1 OR ensino_eja=1 OR educacao_profissional=1) and (local_func_predio_escolar=1) and 
    // (dependencia_adm_id<=3) and (reg_fund_ai_t1=1  or reg_fund_af_t1=1 or  reg_medio_medio_t1=1  or  ensino_eja_fund= 1 or ensino_eja_medio= 1 or 
    // ensino_eja_prof= 1 or esp_eja_fund=1 or esp_eja_medio=1 or ensino_esp_exclusiva_eja_prof=1) and (quadra_esportes_descoberta=1) então conta id
    let allSportsCourtCoverage = allSportsCourt.clone();
    allSportsCourtCoverage.where('escola.quadra_esportes_descoberta = 1');
    req.queryIndex.allSportsCourtCoverage = req.querySet.push(allSportsCourtCoverage) -1;

    req.queryIndex.haveSportsCourtCoverage = req.queryIndex.allSportsCourtCoverage; // It must be []

    req.queryIndex.needSportsCourtCoverage = req.queryIndex.allSportsCourtCoverage;

    // Pátio
    req.queryIndex.allCourtyard = req.queryIndex.allSchools;

    let haveCourtyard = allSchools.clone();
    haveCourtyard.where('escola.patio = 2 OR escola.patio = 1');
    req.queryIndex.haveCourtyard = req.querySet.push(haveCourtyard) - 1;

    let needCourtyard = allSchools.clone();
    needCourtyard.where('escola.patio = 0');
    req.queryIndex.needCourtyard = req.querySet.push(needCourtyard) - 1;

    // Cobertura do Pátio
    let allCourtyardCoverage = allSchools.clone();
    allCourtyardCoverage.where('escola.patio = 1');
    req.queryIndex.allCourtyardCoverage = req.querySet.push(allCourtyardCoverage) - 1;

    req.queryIndex.haveCourtyardCoverage = req.queryIndex.allCourtyardCoverage; // It must be []

    req.queryIndex.needCourtyardCoverage = req.queryIndex.allCourtyardCoverage;

    // Sala de direção
    req.queryIndex.allDirectorRoom = req.queryIndex.allUrbanSchools;

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

    let needDirectorRoom = allUrbanSchools.clone();
    needDirectorRoom.where('escola.sala_diretoria = 0');
    req.queryIndex.needDirectorRoom = req.querySet.push(needDirectorRoom) - 1;

    // Secretaria
    req.queryIndex.allSecretary = req.queryIndex.allSchools;

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

    let needSecretary = allSchools.clone();
    needSecretary.where('escola.secretaria = 0');
    req.queryIndex.needSecretary = req.querySet.push(needSecretary) - 1;

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

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

    let needTeacherRoom = allSchools.clone();
    needTeacherRoom.where('escola.sala_professor = 0');
    req.queryIndex.needTeacherRoom = req.querySet.push(needTeacherRoom) - 1;

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

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

    let needKitchen = allSchools.clone();
    needKitchen.where('escola.cozinha = 0');
    req.queryIndex.needKitchen = req.querySet.push(needKitchen) - 1;

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

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

    let needStoreroom = allSchools.clone();
    needStoreroom.where('escola.despensa = 0');
    req.queryIndex.needStoreroom = req.querySet.push(needStoreroom) - 1;

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

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

    let needWarehouse = allSchools.clone();
    needWarehouse.where('escola.almoxarifado = 0');
    req.queryIndex.needWarehouse = req.querySet.push(needWarehouse) - 1;

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

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

    let needInternet = allCountrySchools.clone();
    needInternet.where('escola.internet = 0');
    req.queryIndex.needInternet = req.querySet.push(needInternet) - 1;

    // Internet banda larga
    // Se (situacao_de_funcionamento=1) and (ensino_regular=1 OR ensino_eja=1 OR educacao_profissional=1) and (local_func_predio_escolar=1) and 
    // (dependencia_adm_id<=3) and (localizacao_id=2) então conta id
    req.queryIndex.allBroadbandInternet = req.queryIndex.allUrbanSchools;

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

    let needBroadbandInternet = allUrbanSchools.clone();
    needBroadbandInternet.where('escola.internet_banda_larga = 0');
    req.queryIndex.needBroadbandInternet = req.querySet.push(needBroadbandInternet) - 1;

    // Sanitário dentro do prédio
    req.queryIndex.allInsideBathroom = req.queryIndex.allSchools;

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

    let needInsideBathroom = allSchools.clone();
    needInsideBathroom.where('escola.sanitario_dentro_predio = 0');
    req.queryIndex.needInsideBathroom = req.querySet.push(needInsideBathroom) - 1;

    // Banheiro adequado para educação infantil dentro do prédio
    // Se (situacao_de_funcionamento=1) and (ensino_regular=1 OR ensino_eja=1 OR educacao_profissional=1) and (local_func_predio_escolar=1) and
    // (dependencia_adm_id<=3) and (reg_infantil_creche_t1=1 or reg_infantil_preescola_t1=1 or  reg_fund_ai_t1=1) então conta id
    let allInsideKidsBathroom = allSchools.clone();
    allInsideKidsBathroom.where('reg_infantil_creche_t1=1 OR reg_infantil_preescola_t1=1 OR reg_fund_ai_t1=1');
    req.queryIndex.allInsideKidsBathroom = req.querySet.push(allInsideKidsBathroom) - 1;

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

    let needInsideKidsBathroom = allInsideKidsBathroom.clone();
    needInsideKidsBathroom.where('escola.sanitario_ei = 0');
    req.queryIndex.needInsideKidsBathroom = req.querySet.push(needInsideKidsBathroom) - 1;

    // Fornecimento de energia
    req.queryIndex.allEletricPower = req.queryIndex.allSchools;

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

    let needEletricPower = allSchools.clone();
    needEletricPower.where('escola.fornecimento_energia = 0');
    req.queryIndex.needEletricPower = req.querySet.push(needEletricPower) - 1;

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

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

    let needWaterSupply = allSchools.clone();
    needWaterSupply.where('escola.fornecimento_agua = 0');
    req.queryIndex.needWaterSupply = req.querySet.push(needWaterSupply) - 1;

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

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

    let needFilteredWater = allSchools.clone();
    needFilteredWater.where('escola.agua_filtrada = 0');
    req.queryIndex.needFilteredWater = req.querySet.push(needFilteredWater) - 1;

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

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

    let needSewage = allSchools.clone();
    needSewage.where('escola.esgoto_sanitario = 0');
    req.queryIndex.needSewage = req.querySet.push(needSewage) - 1;

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

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

    let needAdaptedBuilding = allSchools.clone();
    needAdaptedBuilding.where('escola.dependencias_pne = 0');
    req.queryIndex.needAdaptedBuilding = req.querySet.push(needAdaptedBuilding) - 1;

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

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

    let needSpecialBathroom = allSchools.clone();
    needSpecialBathroom.where('escola.sanitario_pne = 0');
    req.queryIndex.needSpecialBathroom = req.querySet.push(needSpecialBathroom) - 1;


    next();
}, multiQuery, (req, res, next) => {

    let schools_in_school_buildings = req.result[req.queryIndex.allSchools];
    let urban_schools_in_school_buildings = req.result[req.queryIndex.allUrbanSchools];
    let country_schools_in_school_buildings = req.result[req.queryIndex.allCountrySchools];
    let schools_not_in_school_buildings = req.result[req.queryIndex.allSchoolsNotSchoolBuilding];

    // Faz o matching entre os resultados
    let libraries = matchQueries(req.result[req.queryIndex.allLibraries], req.result[req.queryIndex.haveLibraries], req.result[req.queryIndex.needLibraries]);
    let libraries_reading_room = matchQueries(req.result[req.queryIndex.allLibrariesReadingRoom], req.result[req.queryIndex.haveLibrariesReadingRoom], req.result[req.queryIndex.needLibrariesReadingRoom]);
    let computer_lab = matchQueries(req.result[req.queryIndex.allInfLab], req.result[req.queryIndex.haveInfLab], req.result[req.queryIndex.needInfLab]);
    let science_lab = matchQueries(req.result[req.queryIndex.allScienceLab], req.result[req.queryIndex.haveScienceLab], req.result[req.queryIndex.needScienceLab]);
    let kids_park = matchQueries(req.result[req.queryIndex.allKidsPark], req.result[req.queryIndex.haveKidsPark], req.result[req.queryIndex.needKidsPark]);
    let nursery = matchQueries(req.result[req.queryIndex.allCribs], req.result[req.queryIndex.haveCribs], req.result[req.queryIndex.needCribs]);
    let sports_court = matchQueries(req.result[req.queryIndex.allSportsCourt], req.result[req.queryIndex.haveSportsCourt], req.result[req.queryIndex.needSportsCourt]);
    let sports_court_coverage = matchQueries(req.result[req.queryIndex.allSportsCourtCoverage], [], req.result[req.queryIndex.needSportsCourtCoverage], true); // have = []
    let courtyard = matchQueries(req.result[req.queryIndex.allCourtyard], req.result[req.queryIndex.haveCourtyard], req.result[req.queryIndex.needCourtyard]);
    let courtyard_coverage = matchQueries(req.result[req.queryIndex.allCourtyardCoverage], [], req.result[req.queryIndex.needCourtyardCoverage], true); // have = []
    let director_room = matchQueries(req.result[req.queryIndex.allDirectorRoom], req.result[req.queryIndex.haveDirectorRoom], req.result[req.queryIndex.needDirectorRoom]);
    let secretary = matchQueries(req.result[req.queryIndex.allSecretary], req.result[req.queryIndex.haveSecretary], req.result[req.queryIndex.needSecretary]);
    let teacher_room = matchQueries(req.result[req.queryIndex.allTeacherRoom], req.result[req.queryIndex.haveTeacherRoom], req.result[req.queryIndex.needTeacherRoom]);
    let kitchen = matchQueries(req.result[req.queryIndex.allKitchen], req.result[req.queryIndex.haveKitchen], req.result[req.queryIndex.needKitchen]);
    let storeroom = matchQueries(req.result[req.queryIndex.allStoreroom], req.result[req.queryIndex.haveStoreroom], req.result[req.queryIndex.needStoreroom]);
    let warehouse = matchQueries(req.result[req.queryIndex.allWarehouse], req.result[req.queryIndex.haveWarehouse], req.result[req.queryIndex.needWarehouse]);
    let internet = matchQueries(req.result[req.queryIndex.allInternet], req.result[req.queryIndex.haveInternet], req.result[req.queryIndex.needInternet]);
    let broadband_internet = matchQueries(req.result[req.queryIndex.allBroadbandInternet], req.result[req.queryIndex.haveBroadbandInternet], req.result[req.queryIndex.needBroadbandInternet]);
    let inside_bathroom = matchQueries(req.result[req.queryIndex.allInsideBathroom], req.result[req.queryIndex.haveInsideBathroom], req.result[req.queryIndex.needInsideBathroom]);
    let inside_kids_bathroom = matchQueries(req.result[req.queryIndex.allInsideKidsBathroom], req.result[req.queryIndex.haveInsideKidsBathroom], req.result[req.queryIndex.needInsideKidsBathroom]);
    let eletrical_power = matchQueries(req.result[req.queryIndex.allEletricPower], req.result[req.queryIndex.haveEletricPower], req.result[req.queryIndex.needEletricPower]);
    let water_supply = matchQueries(req.result[req.queryIndex.allWaterSupply], req.result[req.queryIndex.haveWaterSupply], req.result[req.queryIndex.needWaterSupply]);
    let filtered_water = matchQueries(req.result[req.queryIndex.allFilteredWater], req.result[req.queryIndex.haveFilteredWater], req.result[req.queryIndex.needFilteredWater]);
    let sewage = matchQueries(req.result[req.queryIndex.allSewage], req.result[req.queryIndex.haveSewage], req.result[req.queryIndex.needSewage]);
    let adapted_building = matchQueries(req.result[req.queryIndex.allAdaptedBuilding], req.result[req.queryIndex.haveAdaptedBuilding], req.result[req.queryIndex.needAdaptedBuilding]);
    let adapted_bathroom = matchQueries(req.result[req.queryIndex.allSpecialBathroom], req.result[req.queryIndex.haveSpecialBathroom], req.result[req.queryIndex.needSpecialBathroom]);

    req.result = [{
        schools_in_school_buildings,
        urban_schools_in_school_buildings,
        country_schools_in_school_buildings,
        schools_not_in_school_buildings,
        libraries,
        libraries_reading_room,
        computer_lab,
        science_lab,
        kids_park,
        nursery,
        sports_court,
        sports_court_coverage,
        courtyard,
        courtyard_coverage,
        director_room,
        secretary,
        teacher_room,
        kitchen,
        storeroom,
        warehouse,
        internet,
        broadband_internet,
        inside_bathroom,
        inside_kids_bathroom,
        eletric_energy: eletrical_power,
        water_supply,
        filtered_water,
        sewage_treatment: sewage,
        adapted_building,
        special_bathroom: adapted_bathroom
    }];

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

module.exports = infrastructureApp;