require('../connections/mysql-server');
require('../connections/sql-server');
require('../helpers/CheckToken');
const express = require('express');
const router = express.Router();
const bodyParser = require('body-parser');

router.use(bodyParser.urlencoded({ extended: true }));
router.use(bodyParser.json());

var routes = function () {

  function CheckFiltros(Filtro, reference_db, callback) {
    let Condicao1 = "";
    let Condicao2 = "";
    if (Filtro == 0) {
      callback(Condicao1, Condicao2);
    }
    else if (Filtro == 1) {
      Condicao1 = `where R.Cancelada = 'Sim'`;
      callback(Condicao1, Condicao2);
    }
    else if (Filtro == 2) {
      Condicao1 = `where R.Cancelada = 'Não'`;
      callback(Condicao1, Condicao2);
    }
    else if (Filtro == 3) {
      Condicao2 = `where T.Cursos = T.Concluidos`;
      callback(Condicao1, Condicao2);
    }
    else if (Filtro == 4 || Filtro == 5) {
      let MySQLQuery = `select distinct group_concat(sc.idusers) as 'Matriculas' from stepclass sc inner join units un on un.idunits = sc.idunits where un.reference_db = '${reference_db.replace('.dbo.', '')}';`;
      mysqlConnection.query(MySQLQuery, function (error, Matriculas, fields) {
        Condicao2 = `where T.Matricula ${(Filtro == 5) ? 'not' : ''} in (${Matriculas[0]['Matriculas']})`
        callback(Condicao1, Condicao2);
      });
    }
  }

  function LoopSQLMatriculas(Unidades, Index, SQLServerQuery, DataInicial, DataFinal, Filtro, callback) {
    if (Index < Unidades.length) {
      CheckFiltros(Filtro, Unidades[Index], function(Condicao1, Condicao2) {
        if (Index > 0) {
          SQLServerQuery += `union all
          `;
        }
        SQLServerQuery += `select * from (
          select
            R.Matricula, R.Aluno, R.Data, R.Unidade, R.ValorTotal, R.ValorPrimeira, R.Mensalidade, R.QTDParcela, R.Parcelas, R.ValorParcela, R.TotalMaterial, R.TotalSMaterial, R.TotalCMaterial, R.Cancelada,
            count(R.IDCurso) as 'Cursos',
            sum(case when R.Concluiu = 1 then 1 else 0 end) as 'Concluidos',
            (select top 1 cu.NomeVersao from ${Unidades[Index]}TreinandosTurmas tt inner join ${Unidades[Index]}Cursos cu on cu.Codigo = tt.IDCurso and cu.IDTipoCurso in (3,5) where tt.IDMatricula = R.Matricula) as 'Curso'
          from (
            select distinct
              tt.IDMatricula as 'Matricula',
              cl.Nome as 'Aluno',
              convert(varchar, mt.DataMatricula, 103) as 'Data',
              '${Unidades[Index].replace('.dbo.', '').replace('SGP', '')}' as 'Unidade',
              ROUND(mt.ValorTotal, 2) as 'ValorTotal',
              ROUND(pm.ValorMatricula, 2) as 'ValorPrimeira',
              ROUND(pm.ValorParcela, 2) as 'Mensalidade',
              pm.QTDParcela,
              A.Parcelas,
              ROUND(A.ValorParcela, 2) as 'ValorParcela',
              ROUND(A.Parcelas * A.ValorParcela, 2) as 'TotalMaterial',
              ROUND(pm.QTDParcela * pm.ValorParcela, 2) as 'TotalSMaterial',
              ROUND((pm.QTDParcela * pm.ValorParcela) + (A.Parcelas * A.ValorParcela), 2) as 'TotalCMaterial',
              case when pr.TipoRequerimento = 'C' then 'Sim' else 'Não' end as 'Cancelada',
              tt.Concluiu,
              tt.IDCurso
            from ${Unidades[Index]}TreinandosTurmas tt
            inner join ${Unidades[Index]}Matriculas mt on mt.IDMatricula = tt.IDMatricula
            inner join ${Unidades[Index]}Clientes cl on cl.IDCliente = tt.IDTreinando
            inner join ${Unidades[Index]}PreMatricula pm on pm.IDPreMatricula = mt.IDPreMatricula
            left join (select R.*, R.Parcelas * R.ValorParcela as 'TotalMaterial' from (select IDPreMatricula, count(*) as 'Parcelas', ValorParcela from ${Unidades[Index]}PreMatriculaFinanceiroMaterial group by IDPreMatricula, ValorParcela) R) A on A.IDPreMatricula = pm.IDPreMatricula
            left join ${Unidades[Index]}PreRequerimento pr on pr.IDTreinando = tt.IDTreinando
            where ${(!DataInicial || !DataFinal) ? '1=1' : "mt.DataMatricula between '" + DataInicial + "' and '" + DataFinal + "'"}) R
          ${Condicao1}
          group by R.Matricula, R.Aluno, R.Data, R.Unidade, R.ValorTotal, R.ValorPrimeira, R.Mensalidade, R.QTDParcela, R.Parcelas, R.ValorParcela, R.TotalMaterial, R.TotalSMaterial, R.TotalCMaterial, R.Cancelada) T
        ${Condicao2}
        `;
        Index++;
        LoopSQLMatriculas(Unidades, Index, SQLServerQuery, DataInicial, DataFinal, Filtro, callback);
      });
    }
    else {
      callback(SQLServerQuery);
    }
  }

  router.post('/getMatriculas', verifyJWT, (req, res) => {
    const Unidades = req.body.Unidades;
    const DataInicial = req.body.DataInicial;
    const DataFinal = req.body.DataFinal;
    const Filtro = parseInt(req.body.Filtro);
    LoopSQLMatriculas(Unidades, 0, "", DataInicial, DataFinal, Filtro, function(SQLServerQuery) {
      SQLServerQuery += `order by T.Matricula asc;`;
      //console.log(SQLServerQuery);
      execSQLQuery2(SQLServerQuery, res, req.originalUrl);
    });
  });

  router.post('/getTickets', verifyJWT, (req, res) => {
    const Unidades = req.body.Unidades;
    const DataInicial = req.body.DataInicial;
    const DataFinal = req.body.DataFinal;

    let SQLServerQuery = ``;
    for (let i = 0; i < Unidades.length; i++) {
      if (i > 0) {
        SQLServerQuery += `union all
        `;
      }
      SQLServerQuery += `select A.Media, B.Moda, C.*, D.MediaMatricula from (
        select
          round(sum(pp.Valor) / count(pp.Valor), 2) as 'Media'
        from ${Unidades[i]}PreMatriculaParcelas pp
        inner join ${Unidades[i]}PreMatricula pm on pm.IDPreMatricula = pp.IDPreMatricula
        inner join ${Unidades[i]}Matriculas mt on mt.IDPreMatricula = pm.IDPreMatricula
        where pp.Material = 0 and ${(!DataInicial || !DataFinal) ? '1=1' : "mt.DataMatricula between '" + DataInicial + "' and '" + DataFinal + "'"}) A inner join (
        select top 1
          round(pp.Valor, 2) as 'Moda'
        from ${Unidades[i]}PreMatriculaParcelas pp
        inner join ${Unidades[i]}PreMatricula pm on pm.IDPreMatricula = pp.IDPreMatricula
        inner join ${Unidades[i]}Matriculas mt on mt.IDPreMatricula = pm.IDPreMatricula
        where pp.Material = 0 and ${(!DataInicial || !DataFinal) ? '1=1' : "mt.DataMatricula between '" + DataInicial + "' and '" + DataFinal + "'"}
        group by pp.Valor
        order by count(pp.Valor) desc) B on 1 = 1
        inner join (
        select
          round(pp.Valor, 2) as 'Valor',
          '${Unidades[i].replace('.dbo.', '').replace('SGP', '')}' as 'Unidade'
        from ${Unidades[i]}PreMatriculaParcelas pp
        inner join ${Unidades[i]}PreMatricula pm on pm.IDPreMatricula = pp.IDPreMatricula
        inner join ${Unidades[i]}Matriculas mt on mt.IDPreMatricula = pm.IDPreMatricula
        where pp.Material = 0 and ${(!DataInicial || !DataFinal) ? '1=1' : "mt.DataMatricula between '" + DataInicial + "' and '" + DataFinal + "'"}
      ) C on 1 = 1
      inner join (
      select
        round(sum(pm.ValorMatricula) / count(pm.ValorMatricula), 2) as 'MediaMatricula'
      from ${Unidades[i]}PreMatricula pm
      inner join ${Unidades[i]}Matriculas mt on mt.IDPreMatricula = pm.IDPreMatricula
      where ${(!DataInicial || !DataFinal) ? '1=1' : "mt.DataMatricula between '" + DataInicial + "' and '" + DataFinal + "'"}
      ) D on 1 = 1
    `;
    }
    SQLServerQuery += `order by C.Unidade asc, C.Valor asc;`;

    execSQLQuery2(SQLServerQuery, res, req.originalUrl);
  });

  router.post('/getCancelamentos', verifyJWT, (req, res) => {
    const Unidades = req.body.Unidades;
    const DataInicial = req.body.DataInicial;
    const DataFinal = req.body.DataFinal;

    let SQLServerQuery = ``;
    for (let i = 0; i < Unidades.length; i++) {
      if (i > 0) {
        SQLServerQuery += `union all
        `;
      }
      SQLServerQuery += `select distinct
        cl.Nome as 'Aluno',
        pr.IDMatricula as 'Matricula',
        convert(varchar, pr.Data, 103) as 'Data',
        mv.DescricaoMotivo as 'Motivo',
        pr.DescricaoMotivo,
        '${Unidades[i].replace('.dbo.', '').replace('SGP', '')}' as 'Unidade'
      from ${Unidades[i]}PreRequerimento pr
      inner join ${Unidades[i]}TreinandosTurmas tt on tt.IDTreinando = pr.IDTreinando
      inner join ${Unidades[i]}Clientes cl on cl.IDCliente = pr.IDTreinando
      inner join ${Unidades[i]}Motivos mv on mv.IDMotivo = pr.IDMotivo
      where pr.TipoRequerimento = 'C' and ${(!DataInicial || !DataFinal) ? '1=1' : "pr.Data between '" + DataInicial + "' and '" + DataFinal + "'"}
    `;
    }
    SQLServerQuery += `order by Unidade asc;`;
    //console.log(SQLServerQuery);
    execSQLQuery2(SQLServerQuery, res, req.originalUrl);
  });

  router.post('/getVisitas', verifyJWT, (req, res) => {
    const Unidades = req.body.Unidades;
    const DataInicial = req.body.DataInicial;
    const DataFinal = req.body.DataFinal;

    let SQLServerQuery = ``;
    for (let i = 0; i < Unidades.length; i++) {
      if (i > 0) {
        SQLServerQuery += `union all
        `;
      }
      SQLServerQuery += `select distinct
        ra.Descricao as 'Resultado',
        mt.IDMatricula,
        fa.Descricao as 'FormaAtendimento',
        ve.Descricao as 'Divulgacao',
        pv.Descricao as 'Curso',
        fu.Nome as 'Funcionario',
        ca.Nome as 'Cliente',
        convert(varchar, ca.Data, 103) + ' ' + convert(varchar, ca.Hora, 8) as 'Data',
        '${Unidades[i].replace('.dbo.', '').replace('SGP', '')}' as 'Unidade'
      from ${Unidades[i]}ControleAtendimento ca
      inner join ${Unidades[i]}FormasAtendimento fa on fa.IDFormaAtendimento = ca.IDFormaAtendimento
      inner join ${Unidades[i]}Veiculos ve on ve.IDVeiculo = ca.IDVeiculo
      left join ${Unidades[i]}ProdutosVendas pv on pv.IDProduto = ca.IDProduto
      inner join ${Unidades[i]}Funcionarios fu on fu.IDFuncionario = ca.IDFuncionario
      left join ${Unidades[i]}Atendimentos at on at.IdAtendimento = ca.IDAtendimento
	    left join ${Unidades[i]}ResultadosAtendimento ra on ra.IDResultadoAtendimento = at.IDResultadoAtendimento
      left join ${Unidades[i]}Matriculas mt on (mt.IDAtendimento + 1) = at.IdAtendimento
      where ${(!DataInicial || !DataFinal) ? '1=1' : "ca.Data between '" + DataInicial + "' and '" + DataFinal + "'"}
    `;
    }
    SQLServerQuery += `order by Unidade asc;`;
    //console.log(SQLServerQuery);
    execSQLQuery2(SQLServerQuery, res, req.originalUrl);
  });

  router.post('/getAlunosIrregulares', verifyJWT, (req, res) => {
    const Unidades = req.body.Unidades;
    let SQLServerQuery = ``;
    for (let i = 0; i < Unidades.length; i++) {
      if (i > 0) {
        SQLServerQuery += `union all
        `;
      }
      SQLServerQuery += `
      select distinct
        mt.IDMatricula as 'Matricula',
        c.Nome,
        case when tt.Faltoso = 0 then 'Não' else 'Sim' end as 'Faltoso',
        case when tt.Desistente = 0 then 'Não' else 'Sim' end as 'Desistente',
        case when tt.Concluiu = 0 then 'Não' else 'Sim' end as 'Concluiu',
        '${Unidades[i].replace('.dbo.', '').replace('SGP', '')}' as 'Unidade'
      from ${Unidades[i]}MatriculasTreinandos mt
      inner join ${Unidades[i]}Clientes c ON mt.IDTreinando = c.IDCliente
      inner join ${Unidades[i]}TreinandosTurmas tt on tt.IDTreinando = mt.IDTreinando
      where tt.Faltoso = 1 or tt.Concluiu = 1 or tt.Desistente = 1
    `;
    }
    SQLServerQuery += `order by Unidade asc;`;
    //console.log(SQLServerQuery);
    execSQLQuery2(SQLServerQuery, res, req.originalUrl);
  });

  router.get('/getAlunosDesativados', verifyJWT, (req, res) => {
    execSQLQuery(`select
      ad.idusers as 'Matricula',
      ad.idunits,
      un.unityname as 'Unidade',
      ad.desativado,
      case when ad.desativado = 1 then 'Sim' else 'Não' end as 'DesativadoT'
    from alunosdesativados ad
    inner join units un on un.idunits = ad.idunits;`, res, req.originalUrl);
  });

  router.get('/getAlunosDesativadosP', verifyJWT, (req, res) => {
    execSQLQuery(`select
      uh.idusers as 'Matricula',
      uh.idunits,
      un.unityname as 'Unidade',
      uh.disabled as 'desativado',
      case when uh.disabled = 1 then 'Sim' else 'Não' end as 'DesativadoT'
    from usershomework uh
    inner join units un on un.idunits = uh.idunits;`, res, req.originalUrl);
  });

  router.post('/getAlunosCursos', verifyJWT, (req, res) => {
    const Unidades = req.body.Unidades;
    const DataInicial = req.body.DataInicial;
    const DataFinal = req.body.DataFinal;

    let SQLServerQuery = ``;
    for (let i = 0; i < Unidades.length; i++) {
      if (i > 0) {
        SQLServerQuery += `union all
        `;
      }
      SQLServerQuery += `select
      S.Curso,
      S.Alunos,
      S.Unidade,
      round((convert(decimal,S.Alunos, 2) / S.Total) * 100, 2) as 'Porcentagem'
    from (
      select distinct
        co.Codigo as 'IDCurso',
        co.NomeVersao as 'Curso',
        count(tt.IDCurso) as 'Alunos',
        '${Unidades[i].replace('.dbo.', '').replace('SGP', '')}' as 'Unidade',
        (select sum(T.Alunos) as 'Total' from (
        select distinct
          co.Codigo as 'IDCurso',
          count(tt.IDCurso) as 'Alunos'
        from ${Unidades[i]}TreinandosTurmas tt
        inner join ${Unidades[i]}Cursos co on co.Codigo = tt.IDCurso and co.IDTipoCurso in (3,5)
        inner join ${Unidades[i]}Matriculas ma on ma.IDMatricula = tt.IDMatricula and ma.DataMatricula >= '${DataInicial}' and ma.DataMatricula <= '${DataFinal}'
        group by co.NomeVersao, co.Codigo) T) as 'Total'
      from ${Unidades[i]}TreinandosTurmas tt
      inner join ${Unidades[i]}Cursos co on co.Codigo = tt.IDCurso and co.IDTipoCurso in (3,5)
      inner join ${Unidades[i]}Matriculas ma on ma.IDMatricula = tt.IDMatricula and ma.DataMatricula >= '${DataInicial}' and ma.DataMatricula <= '${DataFinal}'
      group by co.NomeVersao, co.Codigo) S
    `;
    }
    SQLServerQuery += `order by Unidade asc;`;
    //console.log(SQLServerQuery);
    execSQLQuery2(SQLServerQuery, res, req.originalUrl);
  });


  router.post('/getAlunosAtivos', (req, res) => {
    let Unidades = req.body.Unidades;
    const CruzaMySQL = req.body.cruzamysql;
    const Cancelada = req.body.cancelada ? 1 : 0;
    const Suspensa = req.body.suspensa ? 1 : 0;
    const Desistente = req.body.desistente ? 1 : 0;
    const Faltoso = req.body.faltoso ? 1 : 0;
    const Concluiu = req.body.concluiu ? 1 : 0;
    const DataInicial = req.body.DataInicial;
    const DataFinal = req.body.DataFinal;

    let NovaUnidades = [];
    for (let i = 0; i < Unidades.length; i++) {
      NovaUnidades.push({"db": Unidades[i], "TabelaDatas": [], "TabelaSQL": "(select * from ("});
    }

    GetUltimaAulaMatriculas(0, NovaUnidades, function(newUnidades) {
      let SQLServerQuery = ``;
      for (let i = 0; i < Unidades.length; i++) {
        if (i > 0) {
          SQLServerQuery += `union all
          `;
        }
        if (!CruzaMySQL) {
          SQLServerQuery += `select ROW_NUMBER() OVER(ORDER BY GG.Unidade ASC, GG.Matricula ASC) AS 'N', * from (
            select distinct
              T.Unidade,
              T.IDMatricula as 'Matricula',
              T.Nome,
              T.Responsavel,
              T.Endereco,
              T.Fone,
              T.UltimaData
            from (
              select
                '${NovaUnidades[i]['db'].replace('.dbo.', '').replace('SGP', '')}' as 'Unidade',
                *,
                case when R.CH_Consumida >= R.CargaHorariaTotal then 1 else 0 end as 'Concluiu'
              from (
                select
                  tt.IDMatricula,
                  tt.IDTreinando,
                  tt.IDCurso,
                  tu.IdTurma,
                  (select re.Nome from ${NovaUnidades[i]['db']}Clientes re where re.IDCliente = mt.IDClienteResponsavel) as 'Responsavel',
                  cl.Endereco,
                  case
                    when cl.Celular != '' then cl.Celular
                    when cl.FoneResidencial != '' then cl.FoneResidencial
                    when cl.FoneRecado != '' then cl.FoneRecado
                    when cl.FoneComercial != '' then cl.FoneComercial
                  end as 'Fone',
                  cl.Nome,
                  tu.Codigo,
                  tu.DataInicio,
                  (select top(1) tc.Data from ${NovaUnidades[i]['db']}TurmasCalendario tc where tc.IdTurma = tt.CodigoTurma and tc.CodigoCurso = tt.IDCurso order by tc.IdTurmasCalendario asc) as 'PrimeiraAula',
                  tu.DataTermino,
                  (select top(1) tc.Data from ${NovaUnidades[i]['db']}TurmasCalendario tc where tc.IdTurma = tt.CodigoTurma and tc.CodigoCurso = tt.IDCurso order by tc.IdTurmasCalendario desc) as 'UltimaAula',
                  cu.CH_Individual,
                  (select (count(*) * 2) from ${NovaUnidades[i]['db']}Chamadas ch where ch.IDTurma = tt.CodigoTurma and ch. IDCurso = tt.IDCurso) as 'CH_Consumida',
                  (select top(1) ch.Data from ${NovaUnidades[i]['db']}Chamadas ch where ch.IDTurma = tt.CodigoTurma and ch. IDCurso = tt.IDCurso order by ch.IDChamadas desc) as 'UltimaChamada',
                  H_${i}.UltimaData,
                  case when pr.IDPreRequerimento is not null then 1 else 0 end as 'Trancada',
                  tt.Desistente,
                  tt.Faltoso,
                  tu.Cancelada,
                  tu.Suspensa,
                  tu.CargaHorariaTotal,
                  mt.DataMatricula
                from ${NovaUnidades[i]['db']}TreinandosTurmas tt
                inner join ${NovaUnidades[i]['db']}Turmas tu on tu.IdTurma = tt.CodigoTurma
                inner join ${NovaUnidades[i]['db']}Cursos cu on cu.Codigo = tt.IDCurso
                inner join ${NovaUnidades[i]['db']}Clientes cl on cl.IDCliente = tt.IDTreinando
                inner join ${NovaUnidades[i]['db']}Matriculas mt on mt.IDMatricula = tt.IDMatricula
                left join ${NovaUnidades[i]['db']}PreRequerimento pr on pr.IDMatricula = tt.IDMatricula and pr.IDTurma = tt. CodigoTurma and pr.TipoRequerimento = 'T'
                left join ${NovaUnidades[i]['TabelaSQL']} on H_${i}.Matricula = tt.IDMatricula) R
              ) T
            where (T.Cancelada = ${Cancelada} and T.Suspensa = ${Suspensa} and T.Desistente = ${Desistente} and T.Faltoso = ${Faltoso} and T.Concluiu = ${Concluiu} and T.DataMatricula < '${DataFinal}') or
            T.UltimaData between '${DataInicial}' and '${DataFinal}'
            ) GG
          `;
        }
        else {
            SQLServerQuery += `select ROW_NUMBER() OVER(ORDER BY GG.Unidade ASC, GG.Matricula ASC) AS 'N', * from (
              select distinct
              T.Unidade,
              T.IDMatricula as 'Matricula',
              T.Nome,
              T.Responsavel,
              T.Endereco,
              T.Fone
            from (
              select
                '${NovaUnidades[i]['db'].replace('.dbo.', '').replace('SGP', '')}' as 'Unidade',
                *,
                case when R.CH_Sistema >= R.CargaHorariaTotal then 1 else 0 end as 'Concluiu'
              from (
                select
                  tt.IDMatricula,
                  tt.IDTreinando,
                  tt.IDCurso,
                  tu.IdTurma,
                  (select re.Nome from ${NovaUnidades[i]['db']}Clientes re where re.IDCliente = mt.IDClienteResponsavel) as 'Responsavel',
                  cl.Endereco,
                  case
                    when cl.Celular != '' then cl.Celular
                    when cl.FoneResidencial != '' then cl.FoneResidencial
                    when cl.FoneRecado != '' then cl.FoneRecado
                    when cl.FoneComercial != '' then cl.FoneComercial
                  end as 'Fone',
                  cl.Nome,
                  tu.Codigo,
                  tu.DataInicio,
                  (select top(1) tc.Data from ${NovaUnidades[i]['db']}TurmasCalendario tc where tc.IdTurma = tt.CodigoTurma and tc.CodigoCurso = tt.IDCurso order by tc.IdTurmasCalendario asc) as 'PrimeiraAula',
                  tu.DataTermino,
                  (select top(1) tc.Data from ${NovaUnidades[i]['db']}TurmasCalendario tc where tc.IdTurma = tt.CodigoTurma and tc.CodigoCurso = tt.IDCurso order by tc.IdTurmasCalendario desc) as 'UltimaAula',
                  cu.CH_Individual,
                  (select (count(*) * 2) from ${NovaUnidades[i]['db']}Chamadas ch where ch.IDTurma = tt.CodigoTurma and ch. IDCurso = tt.IDCurso) as 'CH_Consumida',
                  (select top(1) ch.Data from ${NovaUnidades[i]['db']}Chamadas ch where ch.IDTurma = tt.CodigoTurma and ch. IDCurso = tt.IDCurso order by ch.IDChamadas desc) as 'UltimaChamada',
                  H_${i}.UltimaData,
                  H_${i}.PrimeiraData,
                  H_${i}.CH_Sistema,
                  case when pr.IDPreRequerimento is not null then 1 else 0 end as 'Trancada',
                  tt.Desistente,
                  tt.Faltoso,
                  tu.Cancelada,
                  tu.Suspensa,
                  tu.CargaHorariaTotal,
                  mt.DataMatricula
                from ${NovaUnidades[i]['db']}TreinandosTurmas tt
                left join ${NovaUnidades[i]['db']}Turmas tu on tu.IdTurma = tt.CodigoTurma
                inner join ${NovaUnidades[i]['db']}Cursos cu on cu.Codigo = tt.IDCurso
                left join ${NovaUnidades[i]['db']}ProgramaTreinamentoCursos pc on pc.CodigoCurso = cu.Codigo
                left join ${NovaUnidades[i]['db']}ProgramaTreinamento pt on pt.Codigo = pc.Codigo and pt.Desativado = 0
                inner join ${NovaUnidades[i]['db']}Clientes cl on cl.IDCliente = tt.IDTreinando
                left join ${NovaUnidades[i]['db']}Matriculas mt on mt.IDMatricula = tt.IDMatricula
                left join ${NovaUnidades[i]['db']}PreRequerimento pr on pr.IDMatricula = tt.IDMatricula and pr.IDTurma = tt. CodigoTurma and pr.TipoRequerimento = 'T'
                left join ${NovaUnidades[i]['TabelaSQL']} on H_${i}.Matricula = tt.IDMatricula) R
              ) T
            where (T.Cancelada = ${Cancelada} and T.Suspensa = ${Suspensa} and T.Desistente = ${Desistente} and T.Faltoso = ${Faltoso} and T.Concluiu = ${Concluiu} and T.DataMatricula < '${DataFinal}')
            ${(Concluiu == 1) ? `` : `or ((T.PrimeiraData <= '${DataInicial}' and T.UltimaData >= '${DataFinal}') or
			      (T.PrimeiraData between '${DataInicial}' and '${DataFinal}') or
			      (T.UltimaData between '${DataInicial}' and '${DataFinal}') or
            (T.PrimeiraData between '${DataInicial}' and '${DataFinal}' and T.UltimaData >= '${DataFinal}') or
						(T.UltimaData between '${DataInicial}' and '${DataFinal}' and T.PrimeiraData <= '${DataInicial}')
            )`}) GG
          `;
        }
      }
      SQLServerQuery += `order by GG.Unidade asc, GG.Matricula asc;`;

      //console.log(SQLServerQuery);
      execSQLQuery2(SQLServerQuery, res, req.originalUrl);
    });
  });

  function GetUltimaAulaMatriculas(Index, Unidades, callback) {
    if (Index < Unidades.length) {
      let SQLQuery = `select
        sc.idusers,
        max(date_format(sc.insert_date, '%Y-%m-%d')) as 'UltimaAula'
      from stepclass sc
      inner join units un on un.idunits = sc.idunits
      where un.reference_db = '${Unidades[Index]['db'].replace('.dbo.', '')}'
      group by sc.idusers;`;
      let newSQLQuery = `select
        sc.idusers,
        tc.idtraineeprogram,
        (count(sc.insert_date) * 2) as 'CH_Consumida',
        min(date_format(sc.insert_date, '%Y-%m-%d')) as 'PrimeiraAula',
        max(date_format(sc.insert_date, '%Y-%m-%d')) as 'UltimaAula'
      from stepclass sc
      inner join classes cl on cl.idclasses = sc.idclasses
      inner join traineeprogramcourses tc on tc.idcourses = cl.idcourses
      inner join traineeprogram tp on tp.idtraineeprogram = tc.idtraineeprogram and tp.disabled = 0
      inner join units un on un.idunits = sc.idunits
      where un.reference_db = '${Unidades[Index]['db'].replace('.dbo.', '')}'
      group by sc.idusers, tc.idtraineeprogram;`;
      mysqlConnection.query(newSQLQuery, function (error, Datas, fields) {
        Unidades[Index]['TabelaDatas'] = Datas;
        for (let i = 0; i < Datas.length; i++) {
          if (i > 0)
            Unidades[Index]['TabelaSQL'] += `union all `;
          Unidades[Index]['TabelaSQL'] += `select '${Datas[i]['idusers']}' as 'Matricula', '${Datas[i]['PrimeiraAula']}' as 'PrimeiraData', '${Datas[i]['UltimaAula']}' as 'UltimaData', '${Datas[i]['idtraineeprogram']}' as 'ProgramaTreinamento', '${Datas[i]['CH_Consumida']}' as 'CH_Sistema' `;
        }
        if (Datas.length == 0) {
          Unidades[Index]['TabelaSQL'] += `select '' as 'Matricula', '' as 'PrimeiraData', '' as 'UltimaData', '' as 'ProgramaTreinamento', '' as 'CH_Sistema'`;
        }
        Unidades[Index]['TabelaSQL'] += `) G_${Index}) H_${Index}`;
        Index++;
        GetUltimaAulaMatriculas(Index, Unidades, callback);
      });
    }
    else {
      callback(Unidades);
    }
  }


  router.post('/getContasReceber', verifyJWT, (req, res) => {
    const Unidades = req.body.Unidades;
    const DataInicial = req.body.DataInicial;
    const DataFinal = req.body.DataFinal;

    LoopContasReceber(0, Unidades, DataInicial, DataFinal, [], function(Resultados) {
      res.send({ "status": 200, "error": null, "response": Resultados });
    });
    //SQLServerQuery += `order by Unidade asc;`;
    //console.log(SQLServerQuery);
    //execSQLQuery2(SQLServerQuery, res, req.originalUrl);
  });

  function LoopContasReceber(Index, Unidades, DataInicial, DataFinal, Resultados, callback) {
    if (Index < Unidades.length) {
      sqlServerConnection.request().query(`exec ${Unidades[Index]}proc_InicializaRelAtrasadas '${DataInicial}','${DataFinal}',0,1,0,0,'G',0,'';`, function (error, results, fields) {
        for (let i = 0; i < results['recordsets'][0].length; i++) {
          results['recordsets'][0][i]['Unidade'] = Unidades[Index].split('.dbo.')[0].replace('SGP', '');

          Resultados.push(results['recordsets'][0][i]);
        }
        Index++;
        LoopContasReceber(Index, Unidades, DataInicial, DataFinal, Resultados, callback);
      });
    }
    else {
      callback(Resultados);
    }
  }

  router.post('/getParcelasAtrasadas', verifyJWT, (req, res) => {
    const Unidade = req.body.Unidade;
    const IDMatricula = req.body.IDMatricula;
    const IDCliente = req.body.IDCliente;

    let SQLServerQuery = `exec ${Unidade}proc_PesquisaParcelasAtrasadas ${IDMatricula},${IDCliente},3, .02, .0003;`;
    //SQLServerQuery += `order by Unidade asc;`;
    //console.log(SQLServerQuery);
    execSQLQuery2(SQLServerQuery, res, req.originalUrl);
  });

  router.post('/getHistoricoFinanceiro', verifyJWT, (req, res) => {
    const Unidade = req.body.Unidade;
    const IDMatricula = req.body.IDMatricula;

    let SQLServerQuery = `exec ${Unidade}proc_ContasReceberHistorico  .02, .0003,3,${IDMatricula};`;
    //SQLServerQuery += `order by Unidade asc;`;
    //console.log(SQLServerQuery);
    execSQLQuery2(SQLServerQuery, res, req.originalUrl);
  });

  router.post('/InserirCobranca', verifyJWT, (req, res) => {
    const Unidade = req.body.Unidade;
    const IDMatricula = req.body.IDMatricula;
    const IDFuncionario = req.body.IDFuncionario;
    const DataStr = new Date().toISOString().replace(/T/, ' ').replace(/\..+/, '').split(" ")[0];
    const Data = DataStr.split('-')[0] + "-" + DataStr.split('-')[1] + "-" + DataStr.split('-')[2];
    const Hora = new Date().toISOString().replace(/T/, ' ').replace(/\..+/, '').split(" ")[1];
    const Protestado = req.body.Protestado ? 1 : 0;
    const Observacao = req.body.Observacao;
    const IDTipoRequisicao = req.body.IDTipoRequisicao;
    const IDDepartamento = req.body.IDDepartamento;

    let SQLServerQuery = `exec ${Unidade}proc_InserirCobranca ${IDMatricula},${IDFuncionario},'${Data}','${Hora}',${Protestado},'${Observacao}',${IDTipoRequisicao},${IDDepartamento};`;
    //console.log(SQLServerQuery);
    execSQLQuery2(SQLServerQuery, res, req.originalUrl);
  });

  router.get('/getListaFuncionarios/:db', verifyJWT, (req, res) => {
    const db = req.params.db;
    execSQLQuery2(`select IDFuncionario, Nome from ${db}Funcionarios;`, res, req.originalUrl);
  });

  router.get('/getHistoricoCobrancas/:db/:IDMatricula', verifyJWT, (req, res) => {
    const db = req.params.db;
    const IDMatricula = req.params.IDMatricula;
    execSQLQuery2(`exec ${db}proc_HistoricoGeral ${IDMatricula};`, res, req.originalUrl);
  });

  router.get('/verificaAGendamento/:db/:IDMatricula', verifyJWT, (req, res) => {
    const db = req.params.db;
    const IDMatricula = req.params.IDMatricula;
    execSQLQuery2(`exec ${db}proc_VerificaAgendamentoCobranca ${IDMatricula};`, res, req.originalUrl);
  });

  router.post('/InserirAgendamento', verifyJWT, (req, res) => {
    const Unidade = req.body.Unidade;
    const IDMatricula = req.body.IDMatricula;
    const DataAgendamento = req.body.DataAgendamento;

    let SQLServerQuery = `exec ${Unidade}proc_InserirAgendamentoCobranca ${IDMatricula},'${DataAgendamento}',1;`;
    //console.log(SQLServerQuery);
    execSQLQuery2(SQLServerQuery, res, req.originalUrl);
  });

  router.patch('/AtualizarDadosCliente', verifyJWT, (req, res) => {
    const Unidade = req.body.Unidade;
    const IDCliente = req.body.Cliente;
    const CEP = req.body.CEP;
    const Endereco = req.body.Endereco;
    const Bairro = req.body.Bairro;
    const Cidade = req.body.Cidade;
    const UF = req.body.UF;
    const Celular = req.body.Celular;
    const FoneComercial = req.body.FoneComercial;
    const FoneResidencial = req.body.FoneResidencial;
    const FoneRecado = req.body.FoneRecado;

    let SQLServerQuery = `update SGP${Unidade}.dbo.Clientes set
      CEP = '${CEP}',
      Endereco = '${Endereco}',
      Bairro = '${Bairro}',
      Cidade = '${Cidade}',
      UF = '${UF}',
      Celular = '${Celular}',
      FoneComercial = '${FoneComercial}',
      FoneResidencial = '${FoneResidencial}',
      FoneRecado = '${FoneRecado}'
    where IDCliente = ${IDCliente};`;
    //console.log(SQLServerQuery);
    execSQLQuery2(SQLServerQuery, res, req.originalUrl);
  });

  router.post('/getAgendamentosCobranca', verifyJWT, (req, res) => {
    const Unidades = req.body.Unidades;
    const DataInicial = req.body.DataInicial;
    const DataFinal = req.body.DataFinal;

    let SQLServerQuery = ``;
    for (let i = 0; i < Unidades.length; i++) {
      if (i > 0) {
        SQLServerQuery += `union all
        `;
      }
      SQLServerQuery += `select
        '${Unidades[i].replace('.dbo.', '').replace('SGP', '')}' as 'Unidade',
        cb.IDCobranca,
        ac.IDAgendamentoCobranca,
        cb.IDMatricula,
        cl.Nome as 'Cliente',
        cl.Celular,
        convert(varchar, cb.Data, 103) + ' ' + convert(varchar, cb.Hora, 24) as 'Data',
        convert(varchar, ac.DataAgendamento, 103) as 'DataAgendamento',
        cb.Observacao
      from ${Unidades[i]}Cobranca cb
      inner join ${Unidades[i]}AgendamentoCobranca ac on ac.IDCobranca = cb.IDCobranca
      inner join ${Unidades[i]}Matriculas mt on mt.IDMatricula = cb.IDMatricula
      inner join ${Unidades[i]}Clientes cl on cl.IDCliente = mt.IDClienteResponsavel
      where ${(!DataInicial || !DataFinal) ? '1=1' : "ac.DataAgendamento between '" + DataInicial + "' and '" + DataFinal + "'"}
    `;
    }
    SQLServerQuery += `order by Unidade asc;`;
    //console.log(SQLServerQuery);
    execSQLQuery2(SQLServerQuery, res, req.originalUrl);
  });



  return router;
};

module.exports = routes;