transport.js

const express = require('express');

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

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();

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

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

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

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

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

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

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

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

transportApp.get('/transportation_manager', (req, res, next) => {
    req.result = [
        {id: null, name: 'Não classificada'},
        {id: 1, name: 'Estadual'},
        {id: 2, name: 'Municipal'},
    ];
    next();
}, response('transportation_manager'));

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

rqf.addField({
    name: 'filter',
    field: false,
    where: true
}).addField({
    name: 'dims',
    field: true,
    where: false
}).addValue({
    name: 'city',
    table: 'municipio',
    tableField: 'nome',
    resultField: 'city_name',
    where: {
        relation: '=',
        type: 'integer',
        field: 'municipio_id',
        table: 'matricula'
    },
    join: {
        primary: 'id',
        foreign: 'municipio_id',
        foreignTable: 'matricula'
    }
}).addValue({
    name: 'state',
    table: 'estado',
    tableField: 'nome',
    resultField: 'state_name',
    where: {
        relation: '=',
        type: 'integer',
        field: 'estado_id',
        table: 'matricula'
    },
    join: {
        primary: 'id',
        foreign: 'estado_id',
        foreignTable: 'matricula'
    }
}).addValue({
    name: 'rural_location',
    table: 'matricula',
    tableField: 'localidade_area_rural',
    resultField: 'rural_location_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'localidade_area_rural'
    }
}).addValue({
    name: 'location',
    table: 'matricula',
    tableField: 'localizacao_id',
    resultField: 'location_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'localizacao_id'
    }
}).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: 'transportation_manager',
    table: 'matricula',
    tableField: 'responsavel_transp',
    resultField: 'transportation_manager_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'responsavel_transp'
    }
}).addValue({
    name: 'education_level_basic',
    table: 'matricula',
    tableField: 'etapas_mod_ensino_segmento_id',
    resultField: 'education_level_basic_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'etapas_mod_ensino_segmento_id'
    }
}).addValue({
    name: 'service_type',
    table: 'matricula',
    tableField: 'tipo',
    resultField: 'service_type_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'tipo'
    }
}).addValue({
    name: 'service_type',
    table: 'matricula',
    tableField: 'tipo',
    resultField: 'service_type_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'tipo'
    }
}).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'
    }
});

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;
}

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

    let allEnrollment = req.sql.clone()
    allEnrollment.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');
    req.queryIndex.allEnrollment = req.querySet.push(allEnrollment) - 1;

    let allEnrollmentTransport = req.sql.clone()
    allEnrollmentTransport.field('COUNT(*)', 'total')
    .field("'Brasil'", 'name')
    .field('matricula.ano_censo', 'year')
    .field('matricula.transporte_escolar_publico', 'use_transport_public_id')
    .from('matricula')
    .group('matricula.ano_censo')
    .group('matricula.transporte_escolar_publico')
    .order('matricula.ano_censo')
    .where('matricula.tipo <= 3');
    req.queryIndex.allEnrollmentTransport = req.querySet.push(allEnrollmentTransport) - 1;


    let allTransports = req.sql.clone()
    allTransports.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 AND matricula.transporte_escolar_publico = 1');
    req.queryIndex.allTransports = req.querySet.push(allTransports) - 1;

    // Vans e Kombi
    let goVansAndKombi = allTransports.clone();
    goVansAndKombi.field('matricula.transporte_vans_kombi','use_transport_id')
    goVansAndKombi.where('matricula.transporte_vans_kombi = 1 OR matricula.transporte_vans_kombi = 0');
    goVansAndKombi.group('matricula.transporte_vans_kombi');
    req.queryIndex.goVansAndKombi = req.querySet.push(goVansAndKombi) - 1;

    // Micro
    let goMicroBus = allTransports.clone();
    goMicroBus.field('matricula.transporte_micro_onibus', 'use_transport_id')
    goMicroBus.where('matricula.transporte_micro_onibus = 1 OR matricula.transporte_micro_onibus = 0');
    goMicroBus.group('matricula.transporte_micro_onibus');
    req.queryIndex.goMicroBus = req.querySet.push(goMicroBus) - 1;


    // Ônibus
    let goBus = allTransports.clone();
    goBus.field("matricula.transporte_onibus", 'use_transport_id')
    goBus.where('matricula.transporte_onibus = 1 OR matricula.transporte_onibus = 0');
    goBus.group('matricula.transporte_onibus')
    req.queryIndex.goBus = req.querySet.push(goBus) - 1;

    // Bicicleta
    let goBikes = allTransports.clone();
    goBikes.field('matricula.transporte_bicicleta', 'use_transport_id')
    goBikes.where('matricula.transporte_bicicleta = 1 OR matricula.transporte_bicicleta = 0');
    goBikes.group('matricula.transporte_bicicleta')
    req.queryIndex.goBikes = req.querySet.push(goBikes) - 1;

    // Tração Animal
    let goAnimalTraction = allTransports.clone();
    goAnimalTraction.field('matricula.transporte_animal', 'use_transport_id')
    goAnimalTraction.where('matricula.transporte_animal = 1 OR matricula.transporte_animal = 0');
    goAnimalTraction.group('matricula.transporte_animal')
    req.queryIndex.goAnimalTraction = req.querySet.push(goAnimalTraction) - 1;

    // Outro Veículo
    let goOtherVehicle = allTransports.clone();
    goOtherVehicle.field('matricula.transporte_outro', 'use_transport_id')
    goOtherVehicle.where('matricula.transporte_outro = 1 OR matricula.transporte_outro = 0');
    goOtherVehicle.group('matricula.transporte_outro')
    req.queryIndex.goOtherVehicle = req.querySet.push(goOtherVehicle) - 1;

    // Aquaviário/ Embarcação (capacidade até 5 alunos)
    let goWaterway_5_Students = allTransports.clone();
    goWaterway_5_Students.field('matricula.transporte_embar_0_5','use_transport_id')
    goWaterway_5_Students.where('matricula.transporte_embar_0_5 = 1 OR matricula.transporte_embar_0_5 = 0');
    goWaterway_5_Students.group('matricula.transporte_embar_0_5')
    req.queryIndex.goWaterway_5_Students = req.querySet.push(goWaterway_5_Students) - 1;

    // Aquaviário/ Embarcação (capacidade de 5 até 15 alunos)
    let goWaterway_15_Students = allTransports.clone();
    goWaterway_15_Students.field('matricula.transporte_embar_5_15', 'use_transport_id')
    goWaterway_15_Students.where('matricula.transporte_embar_5_15 = 1 OR matricula.transporte_embar_5_15 = 0');
    goWaterway_15_Students.group('matricula.transporte_embar_5_15')
    req.queryIndex.goWaterway_15_Students = req.querySet.push(goWaterway_15_Students) - 1;

    // Aquaviário/ Embarcação (capacidade de 15 até 35 alunos)r
    let goWaterway_35_Students = allTransports.clone();
    goWaterway_35_Students.field('matricula.transporte_embar_15_35', 'use_transport_id')
    goWaterway_35_Students.where('matricula.transporte_embar_15_35 = 1 OR matricula.transporte_embar_15_35 = 0');
    goWaterway_35_Students.group('matricula.transporte_embar_15_35')
    req.queryIndex.goWaterway_35_Students = req.querySet.push(goWaterway_35_Students) - 1;

    // Aquaviário/ Embarcação (capacidade mais 35 alunos)
    let goWaterwayMoreThan_35 = allTransports.clone();
    goWaterwayMoreThan_35.field('matricula.transporte_embar_35', 'use_transport_id')
    goWaterwayMoreThan_35.where('matricula.transporte_embar_35 = 1 OR matricula.transporte_embar_35 = 0');
    goWaterwayMoreThan_35.group('matricula.transporte_embar_35')
    req.queryIndex.goWaterwayMoreThan_35 = req.querySet.push(goWaterwayMoreThan_35) - 1;

    // Trêm / Metrô
    let goSubwayAndTrain = allTransports.clone();
    goSubwayAndTrain.field('matricula.transporte_trem_metro', 'use_transport_id')
    goSubwayAndTrain.where('matricula.transporte_trem_metro = 1 OR matricula.transporte_trem_metro = 0');
    goSubwayAndTrain.group('matricula.transporte_trem_metro')
    req.queryIndex.goSubwayAndTrain = req.querySet.push(goSubwayAndTrain) - 1;

    next();
}, multiQuery, (req, res, next) => {
    let transport_match = [];
    let transport_match_0 = JSON.parse(JSON.stringify(req.result[req.queryIndex.allTransports]));
    let transport_match_1 = JSON.parse(JSON.stringify(req.result[req.queryIndex.allTransports]));

    let all_enrollment_match = [];
    let all_enrollment_match_0 = JSON.parse(JSON.stringify(req.result[req.queryIndex.allEnrollment]));
    let all_enrollment_match_1 = JSON.parse(JSON.stringify(req.result[req.queryIndex.allEnrollment]));

    //modifica adicionando use_transport_id=false, com os mesmos valores
    //do transport_id=true, usado para dar o match e fazer a divisão.
    for (let i = 0; i < transport_match_0.length; i++) {
        transport_match_0[i].use_transport_id = true;
        transport_match.push(transport_match_0[i])
        transport_match_1[i].use_transport_id = false;
        transport_match.push(transport_match_1[i])
    }

    //modifica adicionando use_transport_id=false, com os mesmos valores
    //do transport_id=true, usado para dar o match e fazer a divisão.
    for (let i = 0; i < all_enrollment_match_0.length; i++) {
        all_enrollment_match_0[i].use_transport_public_id = true;
        all_enrollment_match.push(all_enrollment_match_0[i])
        all_enrollment_match_1[i].use_transport_public_id = false;
        all_enrollment_match.push(all_enrollment_match_1[i])
    }

    let public_transport = matchQueries(all_enrollment_match, req.result[req.queryIndex.allEnrollmentTransport]);
    let van_and_kombi = matchQueries(transport_match, req.result[req.queryIndex.goVansAndKombi]);
    let micro_bus = matchQueries(transport_match, req.result[req.queryIndex.goMicroBus]);
    let bus = matchQueries(transport_match, req.result[req.queryIndex.goBus]);
    let bike = matchQueries(transport_match, req.result[req.queryIndex.goBikes]);
    let animal_traction = matchQueries(transport_match, req.result[req.queryIndex.goAnimalTraction]);
    let other_vehicle = matchQueries(transport_match, req.result[req.queryIndex.goOtherVehicle]);
    let waterway_5_Students = matchQueries(transport_match, req.result[req.queryIndex.goWaterway_5_Students]);
    let waterway_15_Students = matchQueries(transport_match, req.result[req.queryIndex.goWaterway_15_Students]);
    let waterway_35_Students = matchQueries(transport_match, req.result[req.queryIndex.goWaterway_35_Students]);
    let waterway_More_Than_35 = matchQueries(transport_match, req.result[req.queryIndex.goWaterwayMoreThan_35]);
    let subway_and_train = matchQueries(transport_match, req.result[req.queryIndex.goSubwayAndTrain]);

    req.result = [{
        public_transport,
        van_and_kombi,
        micro_bus,
        bus,
        bike,
        animal_traction,
        other_vehicle,
        waterway_5_Students,
        waterway_15_Students,
        waterway_35_Students,
        waterway_More_Than_35,
        subway_and_train
    }];
    next();
}, id2str.multitransform(false), response('transports'));

module.exports = transportApp;