Skip to content
Snippets Groups Projects

Implement UC201 - Select Location

Merged Implement UC201 - Select Location
71-simcaq-implementar-backend-uc201-selecionar-localidade into development
Merged
João Victor Rissorequested to merge
71-simcaq-implementar-backend-uc201-selecionar-localidade into development
Viewing commit 2298f5de
Prev Next
Show latest version
2 files
+ 49
− 44
Compare changes
  • Side-by-side
  • Inline

Files

src/libs/routes/enrollment.js
+ 47
− 42
@@ -16,14 +16,13 @@ const parseParams = require(`${libs}/middlewares/parseParams`);
// **Temporary** solution to add where clauses that are common to all requests
// 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');
.from('turma')
.field('MIN(turma.ano_censo)', 'start_year')
.field('MAX(turma.ano_censo)', 'end_year');
next();
}, query, response('range'));
@@ -41,7 +40,7 @@ enrollmentApp.get('/education_level', (req, res, next) => {
// Returns all adm dependencies
enrollmentApp.get('/adm_dependency', (req, res, next) => {
req.sql = squel.select()
.from('dependencia_adms')
.from('dependencia_adm')
.field('pk_dependencia_adm_id', 'id')
.field('nome', 'name');
@@ -71,30 +70,34 @@ enrollmentApp.use('/', parseParams('filter', [
log.debug(req.filter);
log.debug(req.dims);
//applyJoins(req.sql, req.filter, req.dims);
// Do the joins
if(typeof req.filter.adm_dependency !== 'undefined'
|| typeof req.dims.adm_dependency !== 'undefined') {
req.sql.join('dependencia_adms', null, 'fk_dependencia_adm_id=dependencia_adms.pk_dependencia_adm_id');
req.sql.join('dependencia_adm', null,
'fk_dependencia_adm_id = dependencia_adm.pk_dependencia_adm_id');
}
if(typeof req.filter.education_level !== 'undefined'
|| typeof req.dims.education_level !== 'undefined') {
req.sql.join('etapa_ensino', null, 'fk_etapa_ensino_id=etapa_ensino.pk_etapa_ensino_id');
req.sql.join('etapa_ensino', null,
'fk_etapa_ensino_id = etapa_ensino.pk_etapa_ensino_id');
}
if(typeof req.filter.region !== 'undefined'
|| typeof req.dims.region !== 'undefined') {
req.sql.join('municipios', null, 'fk_municipio_id=municipios.pk_municipio_id')
.join('estados', null, 'municipios.fk_estado_id=estados.pk_estado_id')
.join('regioes', null, 'estados.fk_regiao_id=regioes.pk_regiao_id');
req.sql.join('municipio', null, 'fk_municipio_id = municipio.pk_cod_ibge')
.join('estado', null, 'municipio.fk_estado_id = estado.pk_estado_id')
.join('regiao', null, 'estado.fk_regiao_id = regiao.pk_regiao_id');
}
if((typeof req.filter.state !== 'undefined'
|| typeof req.dims.state !== 'undefined')
&& (typeof req.filter.region === 'undefined'
&& typeof req.dims.region === 'undefined')) {
req.sql.join('municipios', null, 'fk_municipio_id=municipios.pk_municipio_id')
.join('estados', null, 'municipios.fk_estado_id=estados.pk_estado_id');
req.sql.join('municipio', null, 'fk_municipio_id = municipio.pk_cod_ibge')
.join('estado', null, 'municipio.fk_estado_id = estado.pk_estado_id');
}
if((typeof req.filter.city !== 'undefined'
@@ -103,11 +106,11 @@ enrollmentApp.use('/', parseParams('filter', [
&& typeof req.dims.state === 'undefined')
&& (typeof req.filter.region === 'undefined'
&& typeof req.dims.region === 'undefined')) {
req.sql.join('municipios', null, 'fk_municipio_id=municipios.pk_municipio_id');
req.sql.join('municipio', null, 'fk_municipio_id = municipio.pk_cod_ibge');
}
if(typeof req.dims.school !== 'undefined') {
req.sql.join('escolas', null, 'fk_escola_id=escolas.pk_escola_id');
req.sql.join('escola', null, 'turma.cod_entidade = escola.cod_entidade');
}
// Dimensions (add fields)
@@ -119,39 +122,41 @@ enrollmentApp.use('/', parseParams('filter', [
}
if(typeof req.dims.region !== 'undefined') {
req.sql.field('regioes.nome', 'region_name')
.group('regioes.nome')
.order('regioes.nome');
req.sql.field('regiao.nome', 'region_name')
.group('regiao.nome')
.order('regiao.nome');
}
if(typeof req.dims.state !== 'undefined') {
req.sql.field('estados.nome', 'state_name')
.group('estados.nome')
.order('estados.nome');
req.sql.field('estado.nome', 'state_name')
.group('estado.nome')
.order('estado.nome');
}
if(typeof req.dims.city !== 'undefined') {
req.sql.field('municipios.nome', 'city_name')
.group('municipios.nome')
.order('municipios.nome');
req.sql.field('municipio.nome', 'city_name')
.group('municipio.nome')
.order('municipio.nome');
}
/*
if(typeof req.dims.school !== 'undefined') {
req.sql.field('escolas.nome_entidade', 'school_name')
.group('escolas.nome_entidade')
.order('escolas.nome_entidade');
}
*/
if(typeof req.dims.adm_dependency !== 'undefined') {
req.sql.field('dependencia_adms.nome', 'adm_dependency_name')
.group('dependencia_adms.nome')
.order('dependencia_adms.nome');
req.sql.field('dependencia_adm.nome', 'adm_dependency_name')
.group('dependencia_adm.nome')
.order('dependencia_adm.nome');
}
if(typeof req.dims.location !== 'undefined') {
req.sql.field('turmas.id_localizacao', 'location_name')
.group('turmas.id_localizacao')
.order('turmas.id_localizacao');
req.sql.field('turma.id_localizacao', 'location_name')
.group('turma.id_localizacao')
.order('turma.id_localizacao');
}
if(typeof req.dims.region === 'undefined'
@@ -164,39 +169,39 @@ enrollmentApp.use('/', parseParams('filter', [
// Filter (add where)
if (typeof req.filter.min_year !== 'undefined') {
req.sql.where('turmas.ano_censo>=?', parseInt(req.filter.min_year, 10));
req.sql.where('turma.ano_censo >= ?', parseInt(req.filter.min_year, 10));
}
if (typeof req.filter.max_year !== 'undefined') {
req.sql.where('turmas.ano_censo<=?', parseInt(req.filter.max_year, 10));
req.sql.where('turma.ano_censo <= ?', parseInt(req.filter.max_year, 10));
}
if (typeof req.filter.adm_dependency !== 'undefined') {
req.sql.where('pk_dependencia_adm_id=?', parseInt(req.filter.adm_dependency, 10));
req.sql.where('pk_dependencia_adm_id = ?', parseInt(req.filter.adm_dependency, 10));
}
if (typeof req.filter.location !== 'undefined') {
req.sql.where('turmas.id_localizacao=?', parseInt(req.filter.location, 10));
req.sql.where('turma.id_localizacao = ?', parseInt(req.filter.location, 10));
}
if (typeof req.filter.education_level !== 'undefined') {
req.sql.where('pk_etapa_ensino_id=?', parseInt(req.filter.education_level, 10));
req.sql.where('pk_etapa_ensino_id = ?', parseInt(req.filter.education_level, 10));
}
if (typeof req.filter.region !== 'undefined') {
req.sql.where('pk_regiao_id=?', parseInt(req.filter.region, 10));
req.sql.where('pk_regiao_id = ?', parseInt(req.filter.region, 10));
}
if (typeof req.filter.state !== 'undefined') {
req.sql.where('pk_estado_id=?', parseInt(req.filter.state, 10));
req.sql.where('pk_estado_id = ?', parseInt(req.filter.state, 10));
}
if (typeof req.filter.city !== 'undefined') {
req.sql.where('turmas.fk_municipio_id=?', parseInt(req.filter.city, 10));
req.sql.where('turma.fk_municipio_id = ?', parseInt(req.filter.city, 10));
}
if (typeof req.filter.school !== 'undefined') {
req.sql.where('turmas.fk_escola_id=?', parseInt(req.filter.school, 10));
req.sql.where('turma.fk_escola_id = ?', parseInt(req.filter.school, 10));
}
log.debug(req.sql.toParam());
next();
@@ -204,10 +209,10 @@ enrollmentApp.use('/', parseParams('filter', [
enrollmentApp.get('/', (req, res, next) => {
req.sql.field('COALESCE(SUM(num_matriculas), 0)', 'total')
.field('turmas.ano_censo', 'year')
.from('turmas')
.group('turmas.ano_censo')
.order('turmas.ano_censo');
.field('turma.ano_censo', 'year')
.from('turma')
.group('turma.ano_censo')
.order('turma.ano_censo');
next();
}, query, (req, res, next) => {
// 'Sanitize' result