classCount.js

/*
Copyright (C) 2016 Centro de Computacao Cientifica e Software Livre
Departamento de Informatica - Universidade Federal do Parana - C3SL/UFPR

This file is part of simcaq-node.

simcaq-node is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

simcaq-node is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with simcaq-node.  If not, see <https://www.gnu.org/licenses/>.
*/

const express = require('express');

const classCountApp = 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', 'id'],
  resultField: ['city_name', 'city_id'],
  where: {
    relation: '=',
    type: 'integer',
    field: 'municipio_id',
    table: 'turma'
  },
  join: {
    primary: 'id',
    foreign: 'municipio_id',
    foreignTable: 'turma'
  }
}, 'dims').addValueToField({
  name: 'city',
  table: 'municipio',
  tableField: 'nome',
  resultField: 'city_name',
  where: {
    relation: '=',
    type: 'integer',
    field: 'municipio_id',
    table: 'turma'
  },
  join: {
    primary: 'id',
    foreign: 'municipio_id',
    foreignTable: 'turma'
  }
}, 'filter')
.addValue({
  name: 'state',
  table: 'estado',
  tableField: 'nome',
  resultField: 'state_name',
  where: {
    relation: '=',
    type: 'integer',
    field: 'estado_id',
    table: 'turma'
  },
  join: {
    primary: 'id',
    foreign: 'estado_id',
    foreignTable: 'turma'
  }
}).addValue({
  name: 'region',
  table: 'regiao',
  tableField: 'nome',
  resultField: 'region_name',
  where: {
    relation: '=',
    type: 'integer',
    field: 'id'
  },
  join: {
    primary: 'id',
    foreign: 'regiao_id',
    foreignTable: 'turma'
  }
}).addValue({
  name: 'min_year',
  table: 'turma',
  tableField: 'ano_censo',
  resultField: 'year',
  where: {
    relation: '>=',
    type: 'integer',
    field: 'ano_censo'
  }
}).addValue({
  name: 'max_year',
  table: 'turma',
  tableField: 'ano_censo',
  resultField: 'year',
  where: {
    relation: '<=',
    type: 'integer',
    field: 'ano_censo'
  }
}).addValue({
  name:'adm_dependency',
  table: 'turma',
  tableField: 'dependencia_adm_id',
  resultField: 'adm_dependency_id',
  where: {
    relation: '=',
    type: 'integer',
    field: 'dependencia_adm_id'
  }
}).addValue({
  name: 'location',
  table: 'turma',
  tableField: 'localizacao_id',
  resultField: 'location_id',
  where: {
    relation: '=',
    type: 'integer',
    field: 'localizacao_id'
  }
}).addValue({
  name: 'rural_location',
  table: 'turma',
  tableField: 'localidade_area_rural',
  resultField: 'rural_location_id',
  where: {
    relation: '=',
    type: 'integer',
    field: 'localidade_area_rural'
  }
}).addValue({
  name:'education_level_mod',
  table: 'turma',
  tableField: 'etapas_mod_ensino_segmento_id',
  resultField: 'education_level_mod_id',
  where: {
    relation: '=',
    type: 'integer',
    field: 'etapas_mod_ensino_segmento_id'
  }
}).addValue({
  name:'education_level_short',
  table: 'turma',
  tableField: 'etapa_resumida',
  resultField: 'education_level_short_id',
  where: {
    relation: '=',
    type: 'integer',
    field: 'etapa_resumida'
  }
}).addValue({
  name: 'adm_dependency_detailed',
  table: 'turma',
  tableField: 'dependencia_adm_priv',
  resultField: 'adm_dependency_detailed_id',
  where: {
    relation: '=',
    type: 'integer',
    field: 'dependencia_adm_priv'
  }
}).addValueToField({
  name: 'school',
  table: 'escola',
  tableField: ['nome_escola', 'id'],
  resultField: ['school_name', 'school_id'],
  where: {
    relation: '=',
    type: 'integer',
    field: 'id'
  },
  join: {
    primary: ['id', 'ano_censo'],
    foreign: ['escola_id', 'ano_censo'],
    foreignTable: 'turma'
  }
}, 'dims').addValueToField({
  name: 'school',
  table: 'escola',
  tableField: 'nome_escola',
  resultField: 'school_name',
  where: {
    relation: '=',
    type: 'integer',
    field: 'id'
  },
  join: {
    primary: ['id', 'ano_censo'],
    foreign: ['escola_id', 'ano_censo'],
    foreignTable: 'turma'
  }
}, 'filter');

classCountApp.get('/year_range', (req, res, next) => {
  req.sql.from('escola')
  .field('MIN(escola.ano_censo)', 'start_year')
  .field('MAX(escola.ano_censo)', 'end_year');
  next();
}, query, response('range'));

classCountApp.get('/years', (req, res, next) => {
  req.sql.from('escola')
  .field('DISTINCT escola.ano_censo', 'year');
  next();
}, query, response('years'));

classCountApp.get('/adm_dependency', (req, res, next) => {
  req.result = [];
  for(let i = 1; i <= 4; ++i) {
    req.result.push({
      id: i,
      name: id2str.admDependency(i)
    });
  };
  next();
}, response('adm_dependency'));

classCountApp.get('/adm_dependency_detailed', (req, res, next) => {
  req.result = [];
  for(let i = 1; i <= 6; ++i) {
    req.result.push({
      id: i,
      name: id2str.admDependencyPriv(i)
    });
  };
  next();
}, response('adm_dependency_detailed'));

classCountApp.get('/location', (req, res, next) => {
  req.result = [
    {id: 1, name: 'Urbana'},
    {id: 2, name: 'Rural'}
  ];
  next();
}, response('location'));

classCountApp.get('/rural_location', (req, res, next) => {
  req.result = [
    {id: 1, name: "Urbana"},
    {id: 2, name: "Rural"},
    {id: 3, name: "Rural - Área de assentamento"},
    {id: 4, name: "Rural - Terra indígena"},
    {id: 5, name: "Rural - Área remanescente de quilombos"},
    {id: 6, name: "Rural - Unidade de uso sustentável"}
  ];
  next();
}, response('rural_location'));

classCountApp.get('/education_level_mod', (req, res, next) => {
  req.result = [];
  for(let i = 1; i <= 10; ++i) {
    req.result.push({
      id: i,
      name: id2str.educationLevelMod(i)
    });
  }
  req.result.push({
    id: 99,
    name: id2str.educationLevelMod(99)
  });
  next();
}, response('education_level_mod'));

classCountApp.get('/education_level_short', (req, res, next) => {
  req.result = [];
  for(let i = 1; i <= 7; ++i) {
    req.result.push({
      id: i,
      name: id2str.educationLevelShort(i)
    });
  }
  req.result.push({
    id: 99,
    name: id2str.educationLevelShort(99)
  });
  next();
}, response('education_level_short'));

classCountApp.get('/source', (req, res, next) => {
  req.sql.from('fonte')
  .field('fonte', 'source')
  .where('tabela = \'turma\'');
  next();
}, query, response('source'));

// Se a dimensão obrigatória do LDE (etapa de ensino) possui alguma etapa sem nenhum valor, então é criado um objeto
// com média 0.0 e é inserido no resultado. Usada para não quebrar a sequência de etapas na tabela do LDE.
function addNullFields(result) {
  const firstYear = result[0].year;
  var obj = result.filter(res => res.year == firstYear);
  var prevFirstDimId = obj[0];
  obj.forEach((d) => {
    if((d["education_level_mod_id"] > prevFirstDimId["education_level_mod_id"]) && (prevFirstDimId["education_level_mod_id"] != 10) &&
       (d["education_level_mod_id"] != prevFirstDimId["education_level_mod_id"] + 1)) {
      let newObj = {};
      Object.keys(prevFirstDimId).forEach((key) => {
        newObj[key] = prevFirstDimId[key];
      });
      newObj.education_level_mod_id = d["education_level_mod_id"] - 1;
      newObj.education_level_mod_name = id2str.educationLevelMod(d["education_level_mod_id"] - 1);
      newObj.average = 0.0;
      result.splice(result.indexOf(prevFirstDimId) + 1, 0, newObj);
    }
    prevFirstDimId = d;
  });
}

// SimCAQ
classCountApp.get('/count', rqf.parse(), (req, res, next) => {
  req.sql.field("'Brasil'", 'name')
   .field('turma.ano_censo', 'year')
   .field('AVG(turma.num_matricula)', 'average')
   .field('MEDIAN(turma.num_matricula)', 'median')
   .field('STDDEV_POP(turma.num_matricula)', 'stddev')
   .field('QUANTILE(turma.num_matricula, 0.25)', 'first_qt')
   .field('QUANTILE(turma.num_matricula, 0.75)', 'third_qt')
   .from('turma')
   .group('turma.ano_censo')
   .order('turma.ano_censo')
   .where('turma.tipo_turma_id = 0 AND turma.dependencia_adm_id <= 3 AND ((turma.etapa_resumida >= 1 AND turma.etapa_resumida <= 7) OR turma.etapa_resumida = 99)');
   next();
}, rqf.build(), query, addMissing(rqf), id2str.transform(), response('class_count'));

// LDE
classCountApp.get('/', rqf.parse(), (req, res, next) => {
  // Faz a consulta do número de alunos pelas dimensões
  if(("education_level_mod" in req.dims) || ("education_level_mod" in req.filter) || ("education_level_short" in req.dims)) {
    req.sql.field("'Brasil'", 'name')
     .field('turma.ano_censo', 'year')
     .field('AVG(turma.num_matricula)', 'average')
     .field('MEDIAN(turma.num_matricula)', 'median')
     .field('STDDEV_POP(turma.num_matricula)', 'stddev')
     .field('QUANTILE(turma.num_matricula, 0.25)', 'first_qt')
     .field('QUANTILE(turma.num_matricula, 0.75)', 'third_qt')
     .from('turma')
     .group('turma.ano_censo')
     .order('turma.ano_censo')
     .where('turma.tipo_turma_id = 0 AND turma.etapas_mod_ensino_segmento_id >= 1 AND turma.etapas_mod_ensino_segmento_id <= 10');
     next();
  } else {
    res.status(400);
    next({
      status: 400,
      message: 'Wrong/No filter specified'
    });
  }
}, rqf.build(), query, id2str.transform(), (req, res, next) => {
    req.partial = [];

    // Caso tenha apenas uma dimensão, o indicador possuirá uma linha de total
    if((req.dims) && (req.dims.size == 1)) {
      req.partial = req.result;

      // A linha de total deverá conter o valor total do ano que está no banco de dados, então usa o mesmo filtro de anos da consulta anterior
      let yearFilter = {};
      if("min_year" in req.filter)
        yearFilter.min_year = req.filter.min_year;
      if("max_year" in req.filter)
        yearFilter.max_year = req.filter.max_year;

      // Faz a consulta sem dimensões, do total do(s) ano(s) escolhido(s)
      req.resetSql();
      req.dims = {};
      req.filter = yearFilter;
      req.sql.field("'Brasil'", 'name')
       .field('turma.ano_censo', 'year')
       .field('AVG(turma.num_matricula)', 'average')
       .field('MEDIAN(turma.num_matricula)', 'median')
       .field('STDDEV_POP(turma.num_matricula)', 'stddev')
       .field('QUANTILE(turma.num_matricula, 0.25)', 'first_qt')
       .field('QUANTILE(turma.num_matricula, 0.75)', 'third_qt')
       .from('turma')
       .group('turma.ano_censo')
       .order('turma.ano_censo')
       .where('turma.tipo_turma_id = 0 AND turma.etapas_mod_ensino_segmento_id >= 1 AND turma.etapas_mod_ensino_segmento_id <= 10');
     }
    next();
}, rqf.build(), query, addMissing(rqf), id2str.transform(), (req, res, next) => {
  // Se tem apenas uma dimensão
  if(req.partial.length > 0) {
    const yearClassCount = req.result;
    req.result = req.partial;

    // Como a linha de total deve aparecer em um caso específico, ela é adicionada junto com a dimensão obrigatória
    yearClassCount.forEach((result) => {
      let obj = {};
      obj = result;
      obj.education_level_mod_name = "Total";
      req.result.push(obj);
    })
  }
  // Caso tenha mais de uma dimensão, retorna a consulta só pelas dimensões, sem linha de total
  else
    addNullFields(req.result);

  next();
}, response('class_count'));

module.exports = classCountApp;