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

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',
    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: '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 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 public_transport = req.result[req.queryIndex.allTransports];
    let van_and_kombi = req.result[req.queryIndex.goVansAndKombi];
    let micro_bus = req.result[req.queryIndex.goMicroBus];
    let Bus = req.result[req.queryIndex.goBus];
    let bike = req.result[req.queryIndex.goBikes];
    let animal_traction = req.result[req.queryIndex.goAnimalTraction];
    let other_vehicle = req.result[req.queryIndex.goOtherVehicle];
    let waterway_5_Students = req.result[req.queryIndex.goWaterway_5_Students];
    let waterway_15_Students = req.result[req.queryIndex.goWaterway_15_Students];
    let waterway_35_Students = req.result[req.queryIndex.goWaterway_35_Students];
    let waterway_More_Than_35 = req.result[req.queryIndex.goWaterwayMoreThan_35];
    let subway_and_train = 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;