João Victor Risso
--- a/src/libs/routes/location.js

+ 72

− 0
+++ b/src/libs/routes/location.js

+ 72

− 0
 @@ -1124,4 +1124,76 @@ locationApp.get('/educational/school_level/region/:id', (req, res, next) => {
 @@ -1124,4 +1124,76 @@ locationApp.get('/educational/school_level/region/:id', (req, res, next) => {
    });
 }, response('location'));
+locationApp.get('/educational/school_level/state/:id', (req, res, next) => {
+    const stateId = parseInt(req.params.id, 10);
+    const enrollmentsPerSchoolLevelYearQry = squel.select()
+        .field('MAX(turma.ano_censo)', 'census_year')
+        .from('turma');
+    const enrollmentsPerSchoolLevelQry = squel.select()
+        .field('COALESCE(SUM(turma.num_matriculas), 0)', 'total')
+        .field('turma.ano_censo', 'census_year')
+        .field('turma.serie_ano', 'school_year')
+        .field('etapa_ensino.desc_etapa', 'school_level')
+        .from('turma')
+        .from('etapa_ensino')
+        .from('estado')
+        .where(`turma.fk_estado_id = ${stateId}`)
+        .where('turma.fk_estado_id = estado.pk_estado_id')
+        .where(`turma.ano_censo IN (${enrollmentsPerSchoolLevelYearQry.toString()})`)
+        .where('turma.fk_etapa_ensino_id = etapa_ensino.pk_etapa_ensino_id')
+        .where('turma.fk_tipo_turma_id <= 3')
+        .group('estado.nome')
+        .group('etapa_ensino.desc_etapa')
+        .group('turma.serie_ano')
+        .group('turma.ano_censo')
+        .order('estado.nome')
+        .order('etapa_ensino.desc_etapa')
+        .order('turma.serie_ano')
+        .order('turma.ano_censo');
+    const queryLabels = [ 'enrollment_per_school_level', 'enrollment_census_year' ];
+    const querySet = [ enrollmentsPerSchoolLevelQry, enrollmentsPerSchoolLevelYearQry ];
+    // wait until all queries finish or one of them fail
+    Promise.all(dbExecAll(querySet, enrollmentsPerSchoolLevelYearQry)).then((queryResults) => {
+        const result = queryResults[0];
+        const censusYear = queryResults[1][0]['census_year'];
+        let school_levels = {};
+        for(let i = 0; i < result.length; ++i) {
+            const school_year  = schoolYearIdToStr(result[i].school_year);
+            const school_level = result[i].school_level;
+            const census_year = result[i].census_year;
+            if (typeof school_levels[school_level] === 'undefined') {
+                school_levels[school_level] = {};
+            }
+            school_levels[school_level][school_year] = parseInt(result[i].total, 10);
+        }
+        let response = [];
+        for(let level in school_levels) {
+            if (school_levels.hasOwnProperty(level)) {
+                let sclevel = {};
+                sclevel["degree"] = level;
+                sclevel["census_year"] = parseInt(censusYear, 10);
+                sclevel["table"] = [];
+                for(let school_year in school_levels[level]) {
+                    if (school_levels[level].hasOwnProperty(school_year)) {
+                        let enrollment = { 'title' : school_year,
+                                           'value' : school_levels[level][school_year] };
+                        sclevel["table"].push(enrollment);
+                    }
+                }
+                response.push(sclevel);
+            }
+        }
+        req.result = response;
+        next();
+    }).catch((error) => {
+        log.error(`[SQL query error] ${error}`);
+        next(error);
+    });
+}, response('location'));
 module.exports = locationApp;