-
Vytor Calixto authoredVytor Calixto authored
school.js 11.94 KiB
const express = require('express');
const schoolApp = express.Router();
const libs = `${process.cwd()}/libs`;
const log = require(`${libs}/log`)(module);
const squel = require('squel');
const query = require(`${libs}/middlewares/query`).query;
const response = require(`${libs}/middlewares/response`);
const id2str = require(`${libs}/middlewares/id2str`);
const ReqQueryFields = require(`${libs}/middlewares/reqQueryFields`);
const request = require(`request`);
const config = require(`${libs}/config`);
const passport = require('passport');
const download = require(`${libs}/middlewares/downloadDatabase`);
const cache = require('apicache').options({ debug: config.debug, statusCodes: {include: [200]} }).middleware;
let rqf = new ReqQueryFields();
let rqfCount = new ReqQueryFields();
// Return location
schoolApp.get('/year_range', cache('15 day'), (req, res, next) => {
req.sql.from('escola')
.field('MIN(escola.ano_censo)', 'start_year')
.field('MAX(escola.ano_censo)', 'end_year')
.where('escola.ano_censo > 2014');
next();
}, query, response('range'));
schoolApp.get('/years', cache('15 day'), (req, res, next) => {
req.sql.from('escola').
field('DISTINCT escola.ano_censo', 'year')
.where('escola.ano_censo > 2014');
next();
}, query, response('years'));
schoolApp.get('/source', (req, res, next) => {
req.sql.from('fonte')
.field('fonte', 'source')
.where('tabela = \'escola\'');
next();
}, query, response('source'));
schoolApp.get('/location', cache('15 day'), (req, res, next) => {
req.result = [
{id: 1, name: 'Urbana'},
{id: 2, name: 'Rural'}
];
next();
}, response('location'));
schoolApp.get('/adm_dependency', (req, res, next) => {
req.sql.from('dependencia_adm')
.field('id')
.field('nome', 'name')
.where('id <= 4');
next();
}, query, response('adm_dependency'));
schoolApp.get('/adm_dependency_detailed', cache('15 day'), (req, res, next) => {
req.sql.from('dependencia_adm_priv')
.field('id', 'id')
.field('nome', 'name');
next();
}, query, response('adm_dependency_detailed'));
schoolApp.get('/government_agreement', cache('15 day'), (req, res, next) => {
req.result = [
{id: null, name: 'Não Declarado'},
{id: 0, name: 'Não'},
{id: 1, name: 'Sim'}
];
next();
}, response('government_agreement'));
schoolApp.get('/agreement', cache('15 day'), (req, res, next) => {
req.result = [
{id: 1, name: 'Municipal'},
{id: 2, name: 'Estadual'},
{id: 3, name: 'Estadual e Municipal'}
];
next();
}, response('agreement'));
schoolApp.get('/education_day_care_child', (req, res, next) => {
req.result = [
{id: null, name: 'Não Declarado'},
{id: 0, name: 'Não'},
{id: 1, name: 'Sim'}
];
next();
}, response('education_day_care_child'));
schoolApp.get('/education_preschool_child', cache('15 day'), (req, res, next) => {
req.result = [
{id: null, name: 'Não Declarado'},
{id: 0, name: 'Não'},
{id: 1, name: 'Sim'}
];
next();
}, response('education_preschool_child'));
schoolApp.get('/education_begin_elementary_school', cache('15 day'), (req, res, next) => {
req.result = [
{id: null, name: 'Não Declarado'},
{id: 0, name: 'Não'},
{id: 1, name: 'Sim'}
];
next();
}, response('education_begin_elementary_school'));
schoolApp.get('/education_end_elementary_school', cache('15 day'), (req, res, next) => {
req.result = [
{id: null, name: 'Não Declarado'},
{id: 0, name: 'Não'},
{id: 1, name: 'Sim'}
];
next();
}, response('education_end_elementary_school'));
schoolApp.get('/education_middle_school', cache('15 day'), (req, res, next) => {
req.result = [
{id: null, name: 'Não Declarado'},
{id: 0, name: 'Não'},
{id: 1, name: 'Sim'}
];
next();
}, response('education_middle_school'));
schoolApp.get('/education_professional', cache('15 day'), (req, res, next) => {
req.result = [
{id: null, name: 'Não Declarado'},
{id: 0, name: 'Não'},
{id: 1, name: 'Sim'}
];
next();
}, response('education_professional'));
schoolApp.get('/education_eja', cache('15 day'), (req, res, next) => {
req.result = [
{id: null, name: 'Não Declarado'},
{id: 0, name: 'Não'},
{id: 1, name: 'Sim'}
];
next();
}, response('education_eja'));
rqf.addField({
name: 'filter',
field: false,
where: true
}).addValue({
name: 'id',
table: 'escola',
tableField: 'id',
where: {
relation: '=',
type: 'integer',
field: 'id'
}
}).addValue({
name: 'city',
table: 'municipio',
tableField: 'nome',
resultField: 'city_name',
where: {
relation: '=',
type: 'integer',
field: 'municipio_id',
table: 'escola'
},
join: {
primary: 'id',
foreign: 'municipio_id',
foreignTable: 'escola'
}
}).addValue({
name: 'state',
table: 'estado',
tableField: 'nome',
resultField: 'state_name',
where: {
relation: '=',
type: 'integer',
field: 'estado_id',
table: 'escola'
},
join: {
primary: 'id',
foreign: 'estado_id',
foreignTable: 'escola'
}
}).addValue({
name: 'year',
table: 'escola',
tableField: 'ano_censo',
resultField: 'year',
where: {
relation: '=', type: 'integer',
field: 'ano_censo',
table: 'escola'
}
});
rqfCount.addField({
name: 'filter',
field: false,
where: true
}).addField({
name: 'dims',
field: true,
where: false
}).addValue({
name: 'id',
table: 'escola',
tableField: 'id',
where: {
relation: '=',
type: 'integer',
field: 'id'
}
}).addValueToField({
name: 'city',
table: 'municipio',
tableField: ['nome', 'id'],
resultField: ['city_name', 'city_id'],
where: {
relation: '=',
type: 'integer',
field: 'municipio_id',
table: 'escola'
},
join: {
primary: 'id',
foreign: 'municipio_id',
foreignTable: 'escola'
}
}, 'dims').addValueToField({
name: 'city',
table: 'municipio',
tableField: 'nome',
resultField: 'city_name',
where: {
relation: '=',
type: 'integer',
field: 'municipio_id',
table: 'escola'
},
join: {
primary: 'id',
foreign: 'municipio_id',
foreignTable: 'escola'
}
}, 'filter').addValue({
name: 'state',
table: 'estado',
tableField: 'nome',
resultField: 'state_name',
where: {
relation: '=',
type: 'integer',
field: 'estado_id',
table: 'escola'
},
join: {
primary: 'id',
foreign: 'estado_id',
foreignTable: 'escola' }
}).addValue({
name: 'region',
table: 'regiao',
tableField: 'nome',
resultField: 'region_name',
where: {
relation: '=',
type: 'integer',
field: 'id'
},
join: {
primary: 'id',
foreign: 'regiao_id',
foreignTable: 'escola'
}
}).addValue({
name: 'year',
table: 'escola',
tableField: 'ano_censo',
resultField: 'year',
where: {
relation: '=',
type: 'integer',
field: 'ano_censo',
table: 'escola'
}
}).addValue({
name: 'location',
table: 'escola',
tableField: 'cod_localizacao',
resultField: 'location_id',
where: {
relation: '=',
type: 'integer',
field: 'cod_localizacao'
}
}).addValue({
name: 'adm_dependency',
table: 'escola',
tableField: 'dependencia_adm_id',
resultField: 'adm_dependency_id',
where: {
relation: '=',
type: 'integer',
field: 'dependencia_adm_id'
}
}).addValue({
name: 'adm_dependency_detailed',
table: 'escola',
tableField: 'dependencia_adm_priv',
resultField: 'adm_dependency_detailed_id',
where: {
relation: '=',
type: 'integer',
field: 'dependencia_adm_priv'
}
}).addValue({
name: 'government_agreement',
table: 'escola',
tableField: 'conveniada_pp',
resultField: 'government_agreement_id',
where: {
relation: '=',
type: 'boolean',
field: 'conveniada_pp'
}
}).addValue({
name: 'agreement',
table: 'escola', tableField: 'tipo_convenio_pp',
resultField: 'agreement_id',
where: {
relation: '=',
type: 'integer',
field: 'tipo_convenio_pp'
}
}).addValue({
name: 'education_day_care_child',
table: 'escola',
tableField: 'reg_infantil_creche',
resultField: 'education_day_care_child_id',
where: {
relation: '=',
type: 'boolean',
field: 'reg_infantil_creche'
}
}).addValue({
name: 'education_preschool_child',
table: 'escola',
tableField: 'reg_infantil_preescola',
resultField: 'education_preschool_child_id',
where: {
relation: '=',
type: 'boolean',
field: 'reg_infantil_preescola'
}
}).addValue({
name: 'education_begin_elementary_school',
table: 'escola',
tableField: 'reg_fund_ai',
resultField: 'education_begin_elementary_school_id',
where: {
relation: '=',
type: 'boolean',
field: 'reg_fund_ai'
}
}).addValue({
name: 'education_end_elementary_school',
table: 'escola',
tableField: 'reg_fund_af',
resultField: 'education_end_elementary_school_id',
where: {
relation: '=',
type: 'boolean',
field: 'reg_fund_af'
}
}).addValue({
name: 'education_middle_school',
table: 'escola',
tableField: 'reg_medio_medio',
resultField: 'education_middle_school_id',
where: {
relation: '=',
type: 'boolean',
field: 'reg_medio_medio'
}
}).addValue({
name: 'education_professional',
table: 'escola',
tableField: 'educacao_profissional',
resultField: 'education_professional_id',
where: {
relation: '=',
type: 'boolean',
field: 'educacao_profissional'
}
}).addValue({
name: 'education_eja',
table: 'escola', tableField: 'ensino_eja',
resultField: 'education_eja_id',
where: {
relation: '=',
type: 'boolean',
field: 'ensino_eja'
}
}).addValue({
name: 'min_year',
table: 'escola',
tableField: 'ano_censo',
resultField: 'year',
where: {
relation: '>=',
type: 'integer',
field: 'ano_censo'
}
}).addValue({
name: 'max_year',
table: 'escola',
tableField: 'ano_censo',
resultField: 'year',
where: {
relation: '<=',
type: 'integer',
field: 'ano_censo'
}
});
schoolApp.get('/', rqf.parse(), rqf.build(), (req, res, next) => {
if(typeof req.filter === 'undefined' || Object.keys(req.filter).length === 0) {
res.status(400);
next({
status: 400,
message: 'Wrong/No filter specified'
});
}
req.sql.from('escola')
.field('escola.id')
.field('escola.ano_censo', 'year')
.field('escola.nome_escola', 'name')
.field('escola.estado_id', 'state_id')
.field('escola.municipio_id', 'city_id');
next();
}, query, response('school'));
schoolApp.get('/count', cache('15 day'), rqfCount.parse(), rqfCount.build(), (req, res, next) => {
req.sql.from('escola')
.field('COUNT(escola.id)', 'total')
.field("'Brasil'", 'name')
.field('escola.ano_censo', 'year')
.group('escola.ano_censo')
.order('escola.ano_censo')
.where('escola.situacao_de_funcionamento = 1 AND (escola.ensino_regular = 1 OR escola.ensino_eja=1 or escola.educacao_profissional=1)');
next();
}, query, id2str.transform(), response('school'));
schoolApp.get('/count/download', passport.authenticate('bearer', { session: false }), rqfCount.parse(), rqfCount.build(), download('escola', 'mapping_escola'));
module.exports = schoolApp;