Newer
Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
const express = require('express');
const classroomCountApp = express.Router();
const libs = `${process.cwd()}/libs`;
const squel = require('squel');
const query = require(`${libs}/middlewares/query`).query;
const response = require(`${libs}/middlewares/response`);
const ReqQueryFields = require(`${libs}/middlewares/reqQueryFields`);
const id2str = require(`${libs}/middlewares/id2str`);
const addMissing = require(`${libs}/middlewares/addMissing`);
const config = require(`${libs}/config`);
const cache = require('apicache').options({ debug: config.debug, statusCodes: {include: [200]} }).middleware;
let rqf = new ReqQueryFields();
rqf.addField({
name: 'filter',
field: false,
where: true
}).addField({
name: 'dims',
field: true,
where: false
}).addValueToField({
name: 'city',
table: 'municipio',
tableField: 'nome',
resultField: 'city_name',
where: {
relation: '=',
type: 'integer',
field: 'municipio_id',
},
join: {
primary: 'id',
foreign: 'municipio_id',
}
}, 'filter').addValueToField({
name: 'city',
table: 'municipio',
tableField: ['nome', 'id'],
resultField: ['city_name', 'city_id'],
where: {
relation: '=',
type: 'integer',
field: 'id'
},
join: {
primary: 'id',
foreign: 'municipio_id',
name: 'state',
table: 'estado',
tableField: 'nome',
resultField: 'state_name',
where: {
relation: '=',
type: 'integer',
field: 'estado_id',
},
join: {
primary: 'id',
foreign: 'estado_id',
}, 'filter').addValueToField({
name: 'state',
table: 'estado',
tableField: ['nome', 'id'],
resultField: ['state_name', 'state_id'],
where: {
relation: '=',
type: 'integer',
field: 'id',
},
join: {
primary: 'id',
foreign: 'estado_id',
foreignTable: '@'
}
}, 'dims').addValue({
name: 'region',
table: 'regiao',
tableField: 'nome',
resultField: 'region_name',
where: {
relation: '=',
type: 'integer',
field: 'id'
},
join: {
primary: 'id',
foreign: 'regiao_id',
}
}).addValue({
name: 'min_year',
tableField: 'ano_censo',
resultField: 'year',
where: {
relation: '>=',
type: 'integer',
field: 'ano_censo'
}
}).addValue({
name: 'max_year',
tableField: 'ano_censo',
resultField: 'year',
where: {
relation: '<=',
type: 'integer',
field: 'ano_censo'
}
}).addValue({
name: 'school_year',
tableField: 'serie_ano_id',
resultField: 'school_year_id',
where: {
relation: '=',
type: 'integer',
field: 'serie_ano_id'
}
}).addValue({
name: 'location',
tableField: 'localizacao_id',
resultField: 'location_id',
where: {
relation: '=',
type: 'integer',
field: 'localizacao_id'
}
}).addValue({
tableField: 'turma_turno_id',
resultField: 'period_id',
where: {
relation: '=',
type: 'integer',
}
});
classroomCountApp.post('/', rqf.parse(), (req, res, next) => {
let classSize = JSON.parse(req.body.class_size) || null;
let integralTime = JSON.parse(req.body.integral_time) || null;
console.log(classSize, integralTime);
if(classSize == null || integralTime == null) {
res.statusCode = 400;
return res.json({err: {message: "There was an error processing class_size or integral_time. Check your JSON sintax and be sure you're sending both paramenters."}});
}
req.classSize = classSize;
req.integralTime = integralTime;
req.dims.state = true;
req.dims.city = true;
req.dims.period = true;
req.dims.location = true;
req.sql.field('COUNT(*)', 'total')
.field("'Brasil'", 'name')
.field('matricula.ano_censo', 'year')
.from('matricula')
.group('matricula.ano_censo')
.order('matricula.ano_censo')
.where('matricula.tipo<=3')
.where('matricula.dependencia_adm_id < 4');
next();
}, rqf.build(), query, id2str.transform(), (req, res, next) => {
// Gera a relação etapa de ensino X ano escolar
req.educationSchoolYear = {};
for(let i = 10; i < 80; ++i) {
if(id2str.schoolYear(i) !== id2str.schoolYear(99)) {
let educationLevelId = Math.floor(i/10);
let classSize = req.classSize.find((el) => {return el.id === educationLevelId});
let integralTime = req.integralTime.find((el) => {return el.id === educationLevelId});
let numberStudentClass = (typeof classSize !== 'undefined') ? classSize.numberStudentClass : null;
let offerGoal = (typeof integralTime !== 'undefined') ? integralTime.offerGoal : null;
req.educationSchoolYear[i] = {
id: educationLevelId,
name: id2str.educationLevelShort(educationLevelId),
numberStudentClass,
offerGoal
};
}
}
req.resetSql();
next();
}, rqf.parse(), (req, res, next) => {
req.dims.state = true;
req.dims.city = true;
req.dims.location = true;
req.sql.field('SUM(escola.num_salas)', 'total')
.field("'Brasil'", 'name')
.field('escola.ano_censo', 'year')
.from('escola')
.group('escola.ano_censo')
.order('escola.ano_censo')
.where('escola.situacao_de_funcionamento = 1 AND escola.local_func_predio_escolar = 1')
.where('escola.dependencia_adm_id < 4');
next();
}, rqf.build(), query, id2str.transform(), (req, res, next) => {
delete req.dims;
delete req.filter;
next();
}, rqf.parse(), rqf.build(), (req, res, next) => {
// req.result = [{classroom: req.classroom, enrollment: req.enrollment}]; return next();
// Cria estrutura de resposta requisitada:
let i = 0;
let j = 0;
let result = [];
let hashSet = new Set();
let enrollments = [...req.enrollment];
while (i < req.classroom.length) {
let classroom = req.classroom[i];
// Cria hash única para cada espacialidade, dado um ano
let hash = '' + classroom.year + classroom.state_id + classroom.city_id;
year: classroom.year,
name: classroom.name,
state_id: classroom.state_id,
state_name: classroom.state_name,
city_id: classroom.city_id,
city_name: classroom.city_name,
let currentClassroomObj = null;
if( !hashSet.has(hash) ) {
hashSet.add(hash);
result.push(obj);
currentClassroomObj = obj;
} else { // Se a hash já existe, já temos a cidade nos resultados. Como está ordenado, é o último valor nos resultados
currentClassroomObj = result[result.length - 1];
// Inserimos a localidade no array de locations da sala
let location = {
location_id: classroom.location_id,
location_name: classroom.location_name,
total_classroom: parseInt(classroom.total, 10),
total_classroom_be_built: 0,
education_level: []
};
currentClassroomObj.locations.push(location);
// Partimos para as etapas de ensino/anos escolares
let enrollmentMatch = true;
j = 0;
let educationLevelSet = new Set();
let enrollment = enrollments[j];
while(enrollmentMatch && j < enrollments.length) {
enrollment = enrollments[j];
continue;
}
if(enrollment.city_id != classroom.city_id) { // Se as cidades não são iguais, já passamos do range
if(enrollment.year != classroom.year || enrollment.location_id != classroom.location_id) { // Se ano ou localização são diferentes, passa para o próximo
++j;
continue;
}
// Remove se o período é nulo (não dá pra usar no cálculo)
if(enrollment.period_id == null) {
enrollments.splice(j, 1);
// Temos uma matrícula com cidade, ano e localidades certos
// "Consome" a matrícula (remove do vetor de matrículas)
enrollments.splice(j, 1);
// Cria a etapa de ensino adequada
let enrollmentEducationLevel = req.educationSchoolYear[enrollment.school_year_id];
// Se não há um número de alunos por turna para a etapa de ensino, ignoramos a entrada
if(enrollmentEducationLevel.numberStudentClass == null) continue;
let educationLevel = null;
if(!educationLevelSet.has(enrollmentEducationLevel.id)) {
educationLevelSet.add(enrollmentEducationLevel.id);
educationLevel = {
education_level_short_id: enrollmentEducationLevel.id,
education_level_short_name: enrollmentEducationLevel.name,
enrollment: {
total_enrollment_day: 0,
total_enrollment_night: 0,
full_period_classes: 0,
day_classes: 0,
night_classes: 0,
total_classrooms_needed: 0
}
};
if (location.education_level.length == 0) {
location.education_level.push(educationLevel);
} else {
let k = location.education_level.length - 1;
let el = location.education_level[k];
while (k >= 0) {
if(educationLevel.education_level_short_id < el.education_level_short_id) {
--k;
if(k>=0) el = location.education_level[k];
} else break;
}
k++;
location.education_level.splice(k, 0, educationLevel);
} else {
let k = 0;
let el = location.education_level[k];
while(k < location.education_level.length) {
if(el.education_level_short_id != enrollmentEducationLevel.id) {
++k;
if(k<location.education_level.length) el = location.education_level[k];
} else break;
}
if(k >= location.education_level.length) --k;
educationLevel = location.education_level[k];
}
// Soma os totais de matrícula da etapa de ensino
educationLevel.enrollment.total_enrollment_day += (enrollment.period_id < 3 && enrollment.period_id != null) ? enrollment.total : 0;
educationLevel.enrollment.total_enrollment_night += (enrollment.period_id == 3) ? enrollment.total : 0;
// Calcula o número de turmas parcial
// Turmas de período integral
educationLevel.enrollment.full_period_classes = Math.ceil((educationLevel.enrollment.total_enrollment_day * (enrollmentEducationLevel.offerGoal/100)) / enrollmentEducationLevel.numberStudentClass);
// Turmas diurnas
educationLevel.enrollment.day_classes = Math.ceil((educationLevel.enrollment.total_enrollment_day / enrollmentEducationLevel.numberStudentClass) - educationLevel.enrollment.full_period_classes);
// Turmas noturnas
educationLevel.enrollment.night_classes = Math.ceil((educationLevel.enrollment.total_enrollment_night / enrollmentEducationLevel.numberStudentClass));
// Total de salas
educationLevel.enrollment.total_classrooms_needed = (educationLevel.enrollment.full_period_classes + educationLevel.enrollment.day_classes);
if(educationLevel.enrollment.night_classes > educationLevel.enrollment.day_classes) educationLevel.enrollment.total_classrooms_needed += (educationLevel.enrollment.night_classes - educationLevel.enrollment.day_classes);
enrollment = enrollments[j];
}
// Calculamos o total classroom be built para o município usando reduce
location.total_classroom_be_built = location.education_level.reduce((total, atual) => {
return total + atual.enrollment.total_classrooms_needed;
}, 0) - location.total_classroom;
if(location.total_classroom_be_built < 0) location.total_classroom_be_built = 0;
++i;
}
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
let reduction = null;
if(req.dims.state || !req.dims.city) { // Se um dos dois acontecer, sabemos que devemos agregar
let i = 0;
reduction = [];
let reductionSet = new Set();
while (i < result.length) {
let city = result[i];
let obj = {
year: city.year,
name: city.name
}
if(req.dims.state) {
obj.state_id = city.state_id;
obj.state_name = city.state_name;
}
obj.locations = [];
let hash = '' + city.year;
if(req.dims.state) hash += '' + city.state_id;
let currentObj = null;
if(!reductionSet.has(hash)) {
reductionSet.add(hash);
reduction.push(obj);
currentObj = obj;
} else { // Está ordenado, podemos pegar o último
currentObj = reduction[reduction.length - 1];
}
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
// Fazer "merge" do array locations da cidade com o da agregação
if(currentObj.locations.length == 0) {
currentObj.locations = [...city.locations];
} else {
let j = 0;
let k = 0;
let cityLocation = null;
let currentLocation = null;
while((typeof cityLocation !== 'undefined') && (typeof currentLocation !== 'undefined')) {
cityLocation = city.locations[j];
currentLocation = currentObj.locations[k];
if(cityLocation.location_id < currentLocation.location_id) {
++j;
cityLocation = city.locations[j];
continue;
} else if(cityLocation.location_id > currentLocation.location_id) {
++k;
currentLocation = currentObj.locations[k];
continue;
}
// Fazer merge do array education_level
if(currentLocation.education_level.length == 0) {
currentLocation.education_level = [...cityLocation.education_level];
} else {
let l = 0;
while(l < cityLocation.education_level.length) {
let cityEducation = cityLocation.education_level[l];
let m = 0;
let currentEducation = currentLocation.education_level[m];
while(m < currentLocation.education_level.length && cityEducation.education_level_short_id > currentEducation.education_level_short_id) {
++m;
currentEducation = currentLocation.education_level[m];
}
if(m >= currentLocation.education_level.length) --m;
currentEducation = currentLocation.education_level[m];
if(currentEducation.education_level_short_id == cityEducation.education_level_short_id) {
currentEducation.enrollment.total_enrollment_day += cityEducation.enrollment.total_enrollment_day;
currentEducation.enrollment.total_enrollment_night += cityEducation.enrollment.total_enrollment_night;
currentEducation.enrollment.full_period_classes += cityEducation.enrollment.full_period_classes;
currentEducation.enrollment.day_classes += cityEducation.enrollment.day_classes;
currentEducation.enrollment.night_classes += cityEducation.enrollment.night_classes;
currentEducation.enrollment.total_classrooms_needed += cityEducation.enrollment.total_classrooms_needed;
} else {
if(currentEducation.education_level_short_id < cityEducation.education_level_short_id) {
currentLocation.education_level.splice(++m, 0, cityEducation);
} else {
currentLocation.education_level.splice(m, 0, cityEducation);
}
}
++l;
}
}
currentLocation.total_classroom += cityLocation.total_classroom;
currentLocation.total_classroom_be_built += cityLocation.total_classroom_be_built;
++j;
cityLocation = city.locations[j];
}
}
req.result = /*{result, enrollments} //*/ reduction || result;
next();
}, response('classroom_count'));
module.exports = classroomCountApp;