classCount.js

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'));

function mediaCalc(response) {
  let obj = [];
  response.forEach((result) => {
    let newObj = {};
    let keys = Object.keys(result);
    keys.forEach((key) => {
      if(key !== "total_classes" && key !== "total_enrollment")
        newObj[key] = result[key]
    })
    newObj.total = result.total_enrollment / result.total_classes;
    obj.push(newObj);
  });
  return(obj);
}

classCountApp.get('/', rqf.parse(), rqf.build(), (req, res, next) => {
  if(("education_level_mod" in req.dims) || ("education_level_mod" in req.filter)) {
    req.sql.field('COUNT(turma.id)', 'total_classes')
     .field('SUM(turma.num_matricula)', 'total_enrollment')
     .field("'Brasil'", 'name')
     .field('turma.ano_censo', 'year')
     .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'
    });
  }
}, query, addMissing(rqf), id2str.transform(), (req, res, next) => {
   const classCount = mediaCalc(req.result);
   req.result = classCount;
  next();
}, response('class_count'));

classCountApp.get('/count', rqf.parse(), rqf.build(), (req, res, next) => {
  req.sql.field('COUNT(turma.id)', 'total_classes')
   .field('SUM(turma.num_matricula)', 'total_enrollment')
   .field("'Brasil'", 'name')
   .field('turma.ano_censo', 'year')
   .from('turma')
   .group('turma.ano_censo')
   .order('turma.ano_censo')
   .where('turma.tipo_turma_id = 0 AND ((turma.etapa_resumida >= 1 AND turma.etapa_resumida <= 7) OR turma.etapa_resumida = 99)');
   next();
}, query, addMissing(rqf), id2str.transform(), (req, res, next) => {
  const classCount = mediaCalc(req.result);
  req.result = classCount;
  next();
}, response('class_count'));

module.exports = classCountApp;