api.js

const express = require('express');

const xml = require('js2xmlparser');

const enrollmentApp = express();

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

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

const conn = require(`${libs}/db/monet`);

function response(req, res) {
    if (req.query.format === 'csv') {
        res.csv(req.result.data);
    } else if (req.query.format === 'xml') {
        res.send(xml('result', JSON.stringify({ state: req.result.data })));
    } else {
        res.json({ result: req.result.data });
    }
}

enrollmentApp.get('/', (req, res) => {
    res.json({ msg: 'SimCAQ API is running' });
});

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

    conn.query(yearSql, true).then(
        (dbResult) => {
            log.debug(dbResult);
            req.result = dbResult;
            response(req, res);
        },
        (dbError) => {
            log.error(`SQL query execution error: ${dbError.message}`);
            // FIXME: change response to HTTP 501 status
            res.json({ error: 'An internal error has occurred' }).end();
        }
    );
});

enrollmentApp.get('/education_level', (req, res) => {
    const edLevelSql = 'SELECT pk_etapa_ensino_id AS id, desc_etapa AS '
        + 'education_level FROM etapa_ensino';
    conn.query(edLevelSql, true).then(
        (dbResult) => {
            log.debug(dbResult);
            req.result = dbResult;
            response(req, res);
        },
        (dbError) => {
            log.error(`SQL query error: ${dbError.message}`);
            // FIXME: change response to HTTP 501 status
            res.json({ error: 'An internal error has occurred' }).end();
        }
    );
});

enrollmentApp.get('/data', (req, res) => {
    log.debug(req.query);
    log.debug(req.query.met);
    log.debug(req.query.dim);
    const schoolClassSql = 'SELECT * FROM turmas';
    conn.query(schoolClassSql, true).then(
        (dbResult) => {
            log.debug(dbResult);
            req.result = dbResult;
            response(req, res);
        },
        (dbError) => {
            log.error(`SQL query error: ${dbError.message}`);
            // FIXME: change response to HTTP 501 status
            res.json({ error: 'An internal error has occurred' }).end();
        }
    );
});

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!');
        res.send('Request could not be satisfied due to an internal error');
    } else {
        log.debug('SQL query: ${ req.sqlQuery }?');
        log.debug(req.sqlQuery);

        conn.prepare(req.sqlQuery, true).then((dbQuery) => {
            dbQuery.exec(req.sqlQueryParams).then(
                (dbResult) => {
                    log.debug(dbResult);
                    req.result = dbResult;
                    response(req, res);
                },
                (dbError) => {
                    log.error(`SQL query execution error: ${dbError.message}`);
                    // FIXME: change response to HTTP 501 status
                    res.json({ error: 'An internal error has occurred' }).end();
                }
            );
        });
    }
});

module.exports = enrollmentApp;