enrollment.js

const express = require('express');

const enrollmentApp = express();

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

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

const dbQuery = require('../db/query_exec');

const response = require('../middlewares/response');

/**
 * 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) => {
    const yearSql = 'SELECT MIN(t.ano_censo) AS start_year, MAX(t.ano_censo)'
        + 'AS end_year FROM turmas AS t';

    dbQuery(yearSql).then((result) => {
        const record = result['0'];
        log.debug(record);
        req.result = { start_year: record.start_year, end_year: record.end_year };
        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');
    });
});

enrollmentApp.get('/education_level', (req, res, next) => {
    const edLevelSql = 'SELECT pk_etapa_ensino_id AS id, desc_etapa AS '
        + 'education_level FROM etapa_ensino';

    dbQuery(edLevelSql).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');
    });
});

enrollmentApp.get('/data', (req, res, next) => {
    const schoolClassSql = 'SELECT * FROM turmas';
    dbQuery(schoolClassSql).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');
    });
});

enrollmentApp.use('/enrollments', (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.census_year !== 'undefined') {
        req.census_year = parseInt(params.census_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('/enrollments', (req, res, next) => {
    const params = req.query;
    if (typeof params.aggregate !== 'undefined' && params.aggregate === 'region') {
        log.debug('Using enrollments query for regions');
        req.sqlQuery = 'SELECT r.nome AS name, COALESCE(SUM(t.num_matriculas), 0) AS total '
            + 'FROM regioes AS r '
            + 'INNER JOIN estados AS e ON r.pk_regiao_id = e.fk_regiao_id '
            + 'INNER JOIN municipios AS m ON e.pk_estado_id = m.fk_estado_id '
            + 'LEFT OUTER JOIN turmas AS t ON ( '
            + 'm.pk_municipio_id = t.fk_municipio_id ';
        req.sqlQueryParams = [];

        if (typeof req.census_year !== 'undefined') {
            req.sqlQuery += ' AND ';
            req.sqlQuery += 't.ano_censo = ?';
            req.sqlQueryParams.push(req.census_year);
        }

        if (typeof req.adm_dependency_id !== 'undefined') {
            req.sqlQuery += ' AND ';
            req.sqlQuery += 't.fk_dependencia_adm_id = ?';
            req.sqlQueryParams.push(req.adm_dependency_id);
        }

        if (typeof req.location_id !== 'undefined') {
            req.sqlQuery += ' AND ';
            req.sqlQuery += 't.id_localizacao = ?';
            req.sqlQueryParams.push(req.location_id);
        }

        if (typeof req.education_level_id !== 'undefined') {
            req.sqlQuery += ' AND ';
            req.sqlQuery += 't.fk_etapa_ensino_id = ?';
            req.sqlQueryParams.push(req.education_level_id);
        }

        req.sqlQuery += ')';
        if (typeof req.id !== 'undefined') {
            req.sqlQuery += ' WHERE ';
            req.sqlQuery += 'r.pk_regiao_id = ?';
            req.sqlQueryParams.push(req.id);
        }
        req.sqlQuery += ' GROUP BY r.nome';
    }
    next();
});

enrollmentApp.use('/enrollments', (req, res, next) => {
    const params = req.query;
    if (typeof params.aggregate !== 'undefined' && params.aggregate === 'state') {
        log.debug('Using enrollments query for states');
        req.sqlQuery = 'SELECT e.nome AS name, COALESCE(SUM(t.num_matriculas), 0) as total '
            + 'FROM estados AS e '
            + 'INNER JOIN municipios AS m ON m.fk_estado_id = e.pk_estado_id '
            + 'LEFT OUTER JOIN turmas AS t ON ('
            + 'm.pk_municipio_id = t.fk_municipio_id ';
        req.sqlQueryParams = [];

        if (typeof req.census_year !== 'undefined') {
            req.sqlQuery += ' AND ';
            req.sqlQuery += 't.ano_censo = ?';
            req.sqlQueryParams.push(req.census_year);
        }

        if (typeof req.adm_dependency_id !== 'undefined') {
            req.sqlQuery += ' AND ';
            req.sqlQuery += 't.fk_dependencia_adm_id = ?';
            req.sqlQueryParams.push(req.adm_dependency_id);
        }

        if (typeof req.location_id !== 'undefined') {
            req.sqlQuery += ' AND ';
            req.sqlQuery += 't.id_localizacao = ?';
            req.sqlQueryParams.push(req.location_id);
        }

        if (typeof req.education_level_id !== 'undefined') {
            req.sqlQuery += ' AND ';
            req.sqlQuery += 't.fk_etapa_ensino_id = ?';
            req.sqlQueryParams.push(req.education_level_id);
        }

        req.sqlQuery += ')';

        if (typeof req.id !== 'undefined') {
            req.sqlQuery += ' WHERE ';
            req.sqlQuery += 'e.pk_estado_id = ?';
            req.sqlQueryParams.push(req.id);
        }

        req.sqlQuery += ' GROUP BY e.nome';
    }
    next();
});

enrollmentApp.use('/enrollments', (req, res, next) => {
    const params = req.query;
    if (typeof params.aggregate !== 'undefined' && params.aggregate === 'city') {
        log.debug('Using enrollments query for cities');
        req.sqlQuery = 'SELECT m.nome AS name, COALESCE(SUM(t.num_matriculas), 0) as total '
            + 'FROM municipios AS m '
            + 'LEFT OUTER JOIN turmas AS t ON ( '
            + 'm.pk_municipio_id = t.fk_municipio_id';
        req.sqlQueryParams = [];

        if (typeof req.census_year !== 'undefined') {
            req.sqlQuery += ' AND ';
            req.sqlQuery += 't.ano_censo = ?';
            req.sqlQueryParams.push(req.census_year);
        }

        if (typeof req.adm_dependency_id !== 'undefined') {
            req.sqlQuery += ' AND ';
            req.sqlQuery += 't.fk_dependencia_adm_id = ?';
            req.sqlQueryParams.push(req.adm_dependency_id);
        }

        if (typeof req.location_id !== 'undefined') {
            req.sqlQuery += ' AND ';
            req.sqlQuery += 't.id_localizacao = ?';
            req.sqlQueryParams.push(req.location_id);
        }

        if (typeof req.education_level_id !== 'undefined') {
            req.sqlQuery += ' AND ';
            req.sqlQuery += 't.fk_etapa_ensino_id = ?';
            req.sqlQueryParams.push(req.education_level_id);
        }

        req.sqlQuery += ')';

        if (typeof req.id !== 'undefined') {
            req.sqlQuery += ' WHERE ';
            req.sqlQuery += 'm.pk_municipio_id = ?';
            req.sqlQueryParams.push(req.id);
        }

        req.sqlQuery += 'GROUP BY m.nome';
    }
    next();
});

enrollmentApp.use('/enrollments', (req, res, next) => {
    const params = req.query;
    if (typeof params.aggregate === 'undefined') {
        log.debug('Using enrollments query for the whole country');
        req.sqlQuery = 'SELECT \'Brasil\' AS name, COALESCE(SUM(t.num_matriculas),0) AS total '
            + 'FROM turmas AS t';
        req.sqlQueryParams = [];

        if (req.paramCnt > 0) {
            req.sqlQuery += ' WHERE ';
        }

        if (typeof req.census_year !== 'undefined') {
            req.sqlQuery += 't.ano_censo = ?';
            req.sqlQueryParams.push(req.census_year);
        }

        if (typeof req.adm_dependency_id !== 'undefined') {
            if (req.sqlQueryParams.length > 0) {
                req.sqlQuery += ' AND ';
            }
            req.sqlQuery += 't.fk_dependencia_adm_id = ?';
            req.sqlQueryParams.push(req.adm_dependency_id);
        }

        if (typeof req.location_id !== 'undefined') {
            if (req.sqlQueryParams.length > 0) {
                req.sqlQuery += ' AND ';
            }
            req.sqlQuery += 't.id_localizacao = ?';
            req.sqlQueryParams.push(req.location_id);
        }

        if (typeof req.education_level_id !== 'undefined') {
            if (req.sqlQueryParams.length > 0) {
                req.sqlQuery += ' AND ';
            }
            req.sqlQuery += 't.fk_etapa_ensino_id = ?';
            req.sqlQueryParams.push(req.education_level_id);
        }
    }
    next();
});

enrollmentApp.get('/enrollments', (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;