api.js

'use strict';

const express = require('express');
const xml = require('js2xmlparser');
const enrollmentsApp = express();

const libs = process.cwd() + '/libs/';

const log = require(libs + 'log')(module);
const config = require(libs + 'config');

const conn = require(libs + 'db/monet');

enrollmentsApp.get('/', function (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.
 */
enrollmentsApp.get('/year_range', function(req, res) {
    var 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(function(result) {
      if (req.query.format === 'csv') {
        res.csv(result.data);
      } else if (req.query.format === 'xml') {
        res.send(xml("result", JSON.stringify({year_range: result.data})))
      }
      else {
        res.json({
            result: result.data
        });
      }
    }, function(error) {
        log.error('SQL query error: ${ error.message }?');
        log.debug(error);
        res.send('Request could not be satisfied due to an internal error');
    });
});

enrollmentsApp.get('/education_level', function(req, res) {
    var edLevelId = "SELECT pk_etapa_ensino_id AS id, desc_etapa AS education_level FROM etapa_ensino";
    conn.query(edLevelId, true).then(function(result) {
      if (req.query.format === 'csv') {
        res.csv(result.data);
      } else if (req.query.format === 'xml') {
        res.send(xml("result", JSON.stringify({year_range: result.data})))
      }
      else {
        res.json({
            result: result.data
        });
      }
    }, function(error) {
        log.error('SQL query error: ${ error.message }?');
        log.debug(error);
        res.send('Request could not be satisfied due to an internal error');
    });
});

enrollmentsApp.get('/data', function(req, res) {
    log.debug(req.query)
    log.debug(req.query.met)
    log.debug(req.query.dim)
    conn.query('SELECT * FROM turmas LIMIT 5', true).then(function(result) {
      if (req.query.format === 'csv') {
        res.csv(result.data);
      } else if (req.query.format === 'xml') {
        res.send(xml("result", JSON.stringify({data: result.data})))
      }
      else {
        res.json({
            result: result.data
        });
      }
    }, function(error) {
        log.error('SQL query error: ${ error.message }?');
        log.debug(error);
        res.send('Request could not be satisfied due to an internal error');
    });
})

enrollmentsApp.use('/enrollments', function(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();
});

enrollmentsApp.use('/enrollments', function(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();
});

enrollmentsApp.use('/enrollments', function(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();
});

enrollmentsApp.use('/enrollments', function(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();
});

enrollmentsApp.use('/enrollments', function(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();
});

enrollmentsApp.get('/enrollments', function(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(function(dbQuery) {
            dbQuery.exec(req.sqlQueryParams).then(function(dbResult) {
                log.debug(dbResult);
                if (req.query.format === 'csv') {
                    res.csv(dbResult.data);
                } else if (req.query.format === 'xml') {
                    res.send(xml('result', JSON.stringify({enrollments: dbResult.data})));
                } else {
                    res.json({ result: dbResult.data });
                }
            });
        }, function(error) {
                log.error('SQL query error: ${ error.message }?');
                log.debug(error);
                res.send('Request could not be satisfied due to an internal error');
        });
    }
});

module.exports = enrollmentsApp