enrollment.js

const express = require('express');

const enrollmentApp = express();

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

// **Temporary** solution to add where clauses that are common to all requests
function filter(req, q) {
    if (typeof req.min_year !== 'undefined') {
        q.where('ano_censo>=?', req.min_year);
    }

    if (typeof req.max_year !== 'undefined') {
        q.where('ano_censo<=?', req.max_year);
    }

    if (typeof req.adm_dependency_id !== 'undefined') {
        q.where('fk_dependencia_adm_id=?', req.adm_dependency_id);
    }

    if (typeof req.location_id !== 'undefined') {
        q.where('id_localizacao=?', req.location_id);
    }

    if (typeof req.education_level_id !== 'undefined') {
        q.where('fk_etapa_ensino_id=?', req.education_level_id);
    }
}

/**
 * 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 = squel.select()
        .from('turmas')
        .field('MIN(turmas.ano_censo)', 'start_year')
        .field('MAX(turmas.ano_censo)', 'end_year')
        .toParam();

    next();
}, query, response('range'));

/**
 * Returns all educational levels avaible
 *
 */
enrollmentApp.get('/education_level', (req, res, next) => {
    req.sql = squel.select()
        .from('etapa_ensino')
        .field('pk_etapa_ensino_id', 'id')
        .field('desc_etapa', 'name')
        .toParam();

    next();
}, query, response('education_level'));

/**
 * Returns all adm dependency
 *
 */
enrollmentApp.get('/adm_dependency', (req, res, next) => {
    req.sql = squel.select()
        .from('dependencia_adms')
        .field('pk_dependencia_adm_id', 'id')
        .field('nome', 'name')
        .toParam();

    next();
}, query, response('adm_dependency'));

enrollmentApp.get('/data', (req, res, next) => {
    req.sql = squel.select().from('turmas').toParam();
    next();
}, query, response('data'));

enrollmentApp.use('/', (req, res, next) => {
    const params = req.query;
    req.paramCnt = 0;

    if (typeof params.id !== 'undefined') {
        req.id = parseInt(params.id, 10);
        req.paramCnt += 1;
    }

    if (typeof params.location_id !== 'undefined') {
        req.location_id = parseInt(params.location_id, 10);
        req.paramCnt += 1;
    }

    if (typeof params.adm_dependency_id !== 'undefined') {
        req.adm_dependency_id = parseInt(params.adm_dependency_id, 10);
        req.paramCnt += 1;
    }

    if (typeof params.min_year !== 'undefined') {
        req.min_year = parseInt(params.min_year, 10);
        req.paramCnt += 1;
    }

    if (typeof params.max_year !== 'undefined') {
        req.max_year = parseInt(params.max_year, 10);
        req.paramCnt += 1;
    }

    if (typeof params.education_level_id !== 'undefined') {
        req.education_level_id = parseInt(params.education_level_id, 10);
        req.paramCnt += 1;
    }

    next();
});

enrollmentApp.use('/', (req, res, next) => {
    const params = req.query;
    if (typeof params.aggregate !== 'undefined' && params.aggregate === 'region') {
        log.debug('Using enrollments query for regions');
        const q = squel.select().from('mat_regioes')
            .field('name')
            .field('SUM(total)', 'total')
            .field('ano_censo', 'year');

        filter(req, q);

        if (typeof req.id !== 'undefined') {
            q.where('pk_regiao_id=?', req.id);
        }
        req.sql = q.group('name').group('ano_censo').order('ano_censo').toParam();
    }
    next();
});

enrollmentApp.use('/', (req, res, next) => {
    const params = req.query;
    if (typeof params.aggregate !== 'undefined' && params.aggregate === 'state') {
        log.debug('Using enrollments query for states');
        const q = squel.select().from('mat_estados')
            .field('name')
            .field('SUM(total)', 'total')
            .field('ano_censo', 'year');

        filter(req, q);

        if (typeof req.id !== 'undefined') {
            q.where('pk_estado_id=?', req.id);
        }
        req.sql = q.group('name').group('ano_censo').order('ano_censo').toParam();
    }
    next();
});

enrollmentApp.use('/', (req, res, next) => {
    const params = req.query;
    if (typeof params.aggregate !== 'undefined' && params.aggregate === 'city') {
        log.debug('Using enrollments query for cities');
        const q = squel.select().from('mat_municipios')
            .field('name')
            .field('SUM(total)', 'total')
            .field('ano_censo', 'year');

        filter(req, q);

        if (typeof req.id !== 'undefined') {
            q.where('pk_municipio_id=?', req.id);
        }
        req.sql = q.group('name').group('ano_censo').order('ano_censo').toParam();
    }
    next();
});

enrollmentApp.use('/', (req, res, next) => {
    const params = req.query;
    if (typeof params.aggregate !== 'undefined' && params.aggregate === 'school') {
        log.debug('Using enrollments query for schools');
        const q = squel.select().from('mat_escolas')
            .field('name')
            .field('SUM(total)', 'total')
            .field('ano_censo', 'year');

        filter(req, q);

        if (typeof req.id !== 'undefined') {
            q.where('pk_escola_id=?', req.id);
        }
        req.sql = q.group('name').group('ano_censo').order('ano_censo').toParam();
    }
    next();
});

enrollmentApp.use('/', (req, res, next) => {
    const params = req.query;
    if (typeof params.aggregate === 'undefined') {
        log.debug('Using enrollments query for the whole country');
        const q = squel.select().from('turmas').field("'Brasil'", 'name')
            .field('COALESCE(SUM(num_matriculas),0)', 'total')
            .field('ano_censo', 'year');

        filter(req, q);

        req.sql = q.group('ano_censo').order('ano_censo').toParam();
    }
    next();
});

enrollmentApp.get('/', (req, res, next) => {
    log.debug(`Request parameters: ${req}`);
    next();
}, query, response('enrollments'));

// enrollmentApp.get('/', (req, res, next) => {
//     log.debug(`Request parameters: ${req}`);
//     if (typeof req.sqlQuery === 'undefined') {
//         // Should only happen if there is a bug in the chaining of the
//         // '/enrollments' route, since when no +aggregate+ parameter is given,
//         // it defaults to use the query for the whole country.
//         log.error('BUG -- No SQL query was found to be executed!');
//         next('Internal error, request could not be satisfied at this moment. Please, '
//             + 'try again later');
//     } else {
//         log.debug('SQL query: ${ req.sqlQuery }?');
//         log.debug(req.sqlQuery);
//         dbQuery(req.sqlQuery).then((result) => {
//             req.result = result;
//             return response(req, res);
//         }, (error) => {
//             log.error(`[${req.originalUrl}] SQL query error: ${error}`);
//             next('Internal error, request could not be satisfied at this moment. Please, '
//                 + 'try again later');
//         });
//     }
// });

module.exports = enrollmentApp;