enrollment.js

const express = require('express');

const enrollmentApp = express.Router();

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

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

const squel = require('squel');

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

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

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

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

let rqf = new ReqQueryFields();

// Complete range of the enrollments dataset.
// Returns a tuple of start and ending years of the complete enrollments dataset.
enrollmentApp.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'));

enrollmentApp.get('/location', (req, res, next) => {
    req.sql = squel.select()
    .field('id')
    .field('descricao', 'name')
    .from('localizacao');
    next();
}, query, response('location'));

// Returns all school years available
enrollmentApp.get('/school_year', (req, res, next) => {
    req.sql.from('serie_ano')
    .field('id')
    .field('nome', 'name');
    next();
}, query, response('school_year'));

// Returns all school years available
enrollmentApp.get('/education_level', (req, res, next) => {
    req.sql.from('etapa_ensino')
    .field('id')
    .field('desc_etapa', 'name');
    next();
}, query, response('education_level'));

// Returns all school years available
enrollmentApp.get('/education_level_mod', (req, res, next) => {
    req.sql.from('etapas_mod_ensino_segmento')
    .field('id')
    .field('nome', 'name');
    next();
}, query, response('education_level_mod'));

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

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

// Return genders
enrollmentApp.get('/gender', (req, res, next) => {
    req.result = [
        {id: 1, name: 'Masculino'},
        {id: 2, name: 'Feminino'}
    ];
    next();
}, response('gender'));

// Return ethnic group
enrollmentApp.get('/ethnic_group', (req, res, next) => {
    req.sql.from('cor_raca')
    .field('id')
    .field('nome', 'name');
    next();
}, query, response('ethnic_group'));

enrollmentApp.get('/period', (req, res, next) => {
    req.sql.from('turma_turno')
    .field('id')
    .field('nome', 'name');
    next();
}, query, response('period'));

// Returns integral-time avaible
classApp.get('/integral_time', (req, res, next) => {
    req.result = [
        {id: null, name: 'Não Disponível'},
        {id: 0, name: 'Não'},
        {id: 1, name: 'Sim'}
    ];
    next();
}, response('integral_time'));


rqf.addField({
    name: 'filter',
    field: false,
    where: true
}).addField({
    name: 'dims',
    field: true,
    where: false
}).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: 'school_year',
    table: 'matricula',
    tableField: 'serie_ano_id',
    resultField: 'school_year_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'serie_ano_id'
    }
}).addValue({
    name: 'education_level',
    table: 'matricula',
    tableField: 'etapa_ensino_id',
    resultField: 'education_level_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'etapa_ensino_id'
    }
}).addValue({
    name: 'education_level_mod',
    table: 'matricula',
    tableField: 'etapas_mod_ensino_segmento_id',
    resultField: 'education_level_mod_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'etapas_mod_ensino_segmento_id'
    }
}).addValue({
    name: 'region',
    table: 'regiao',
    tableField: 'nome',
    resultField: 'region_name',
    where: {
        relation: '=',
        type: 'integer',
        field: 'id'
    },
    join: {
        primary: 'id',
        foreign: 'regiao_id',
        foreignTable: 'matricula'
    }
}).addValue({
    name: 'state',
    table: 'estado',
    tableField: 'nome',
    resultField: 'state_name',
    where: {
        relation: '=',
        type: 'integer',
        field: 'id'
    },
    join: {
        primary: 'id',
        foreign: 'estado_id',
        foreignTable: 'matricula'
    }
}).addValue({
    name: 'city',
    table: 'municipio',
    tableField: 'nome',
    resultField: 'city_name',
    where: {
        relation: '=',
        type: 'integer',
        field: 'id'
    },
    join: {
        primary: 'id',
        foreign: 'municipio_id',
        foreignTable: 'matricula'
    }
}).addValue({
    name: 'school',
    table: 'escola',
    tableField: 'nome_escola',
    resultField: 'school_name',
    where: {
        relation: '=',
        type: 'integer',
        field: 'id'
    },
    join: {
        primary: ['id', 'ano_censo'],
        foreign: ['escola_id', 'ano_censo'],
        foreignTable: 'matricula'
    }
}).addValue({
    name: 'location',
    table: 'matricula',
    tableField: 'localizacao_id',
    resultField: 'location_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'localizacao_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'
    }
}).addValue({
    name: 'gender',
    table: 'matricula',
    tableField: 'sexo',
    resultField: 'gender_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'sexo'
    }
}).addValue({
    name: 'ethnic_group',
    table: 'matricula',
    tableField: 'cor_raca_id',
    resultField: 'ethnic_group_id',
    where: {
        relation: '=',
        type: 'integer'
    }
}).addValue({
    name: 'period',
    table: 'matricula',
    tableField: 'turma_turno_id',
    resultField: 'period_id',
    where: {
        relation: '=',
        type: 'integer',
        field: 'turma_turno_id'
    }
}).addValue({
  name:'integral_time',
  table: 'turma',
  tableField: 'tempo_integral',
  resultField: 'integral_time_id',
  where: {
      relation: '=',
      type: 'boolean',
      field: 'tempo_integral'
  }
});

enrollmentApp.get('/', rqf.parse(), rqf.build(), (req, res, next) => {
    log.debug(req.sql.toParam());
    req.sql.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');
    next();
}, query, id2str.transform(true), response('enrollment'));

let simRqf = new ReqQueryFields();

simRqf.addField({
    name: 'filter',
    field: false,
    where: true
}).addValue({
    name: 'simulation_time',
    parseOnly: true
}).addValue({
    name: 'city',
    table: 'municipio',
    tableField: 'nome',
    resultField: 'city_name',
    where: {
        relation: '=',
        type: 'integer',
        field: 'id'
    },
    join: {
        primary: 'id',
        foreign: 'municipio_id',
        foreignTable: 'uc408'
    }
});

enrollmentApp.get('/offer_projection', simRqf.parse(), simRqf.build(), (req, res, next) => {
    const max_year = squel.select()
    .field('MAX(matricula.ano_censo)')
    .from('matricula')
    .toString();

    req.sql.field('uc408.ano_censo', 'offerYear')
    .field('uc408.turma_turno_id', 'period_id')
    .field('etapas_mod_ensino_segmento.nome', 'education_level_name')
    .field('etapas_mod_ensino_segmento.id', 'education_level_id')
    .field('uc408.serie_ano_id', 'school_year_id')
    .field('SUM(uc408.matriculas)', 'currentOffer')
    .from('uc408')
    .join('etapas_mod_ensino_segmento', null, 'uc408.etapas_mod_ensino_segmento_id=etapas_mod_ensino_segmento.id')
    .where('uc408.turma_turno_id <= 2')
    .group('uc408.ano_censo')
    .group('uc408.turma_turno_id')
    .group('etapas_mod_ensino_segmento.nome')
    .group('etapas_mod_ensino_segmento.id')
    .group('uc408.serie_ano_id');

    next();
}, query, id2str.transform(false), (req, res, next) => {
    let years = parseInt(req.filter.simulation_time, 10);
    if(isNaN(years)) years = 0;
    let offerYear = req.result[0].offerYear;
    let temp = {daytime: {}, nightly: {}};
    let result = {
        daytime: [],
        nightly: [],
        offerYear,
        years: Array.apply(null, {length: years}).map(Number.call, Number).map((i)=>i+offerYear+1)
    };
    req.result.forEach((i) => {
        let period = (i.period_id === 1) ? 'daytime' : 'nightly';
        if(typeof temp[period][i.education_level_name] === 'undefined') {
            temp[period][i.education_level_name] = {
                id: i.education_level_id,
                name: i.education_level_name,
                currentOffer: 0,
                grades: []
            };
        }
        temp[period][i.education_level_name].currentOffer += parseInt(i.currentOffer, 10);
        temp[period][i.education_level_name].grades.push({
            id: i.school_year_id,
            name: i.school_year_name,
            currentOffer: parseInt(i.currentOffer, 10)
        });
    });
    Object.keys(temp.daytime).forEach((k) => {
        result.daytime.push(temp.daytime[k]);
    });
    Object.keys(temp.nightly).forEach((k) => {
        result.nightly.push(temp.nightly[k]);
    });
    req.result = result;
    next();
}, response('offer_projection'));

module.exports = enrollmentApp;