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

router.use(bodyParser.urlencoded({
  limit: '10mb',
  extended: true
}));
router.use(bodyParser.json({
  limit: '10mb',
  extended: true
}));

var routes = function () {

  router.get('/getTurmas/:idfuncuionario/:db', verifyJWT, (req, res) => {
    var idfuncuionario = parseInt(req.params.idfuncuionario);
    var db = req.params.db;
    execSQLQuery2(`SELECT DISTINCT
      IdTurma
    FROM ${db}TurmasCalendario
    /*WHERE CodigoInstrutor = ${idfuncuionario}*/;`, res, req.originalUrl);
  })

  router.get('/getMatriculas/:idturma/:db', verifyJWT, (req, res) => {
    var idturma = parseInt(req.params.idturma);
    var db = req.params.db;
    execSQLQuery2(`
    SELECT DISTINCT
      IDMatricula
    FROM
      ${db}Matriculas
    WHERE
      CodigoTurma = ${idturma}`,
      res,
      req.originalUrl
    );
  })

  router.get('/getNomeAluno/:idmatricula/:db', verifyJWT, (req, res) => {
    var idmatricula = parseInt(req.params.idmatricula);
    var db = req.params.db;
    execSQLQuery2(`
    SELECT
      ${db}Clientes.Nome
    FROM
      ${db}MatriculasTreinandos
    INNER JOIN
      ${db}Clientes
    ON
      ${db}MatriculasTreinandos.IDTreinando = ${db}Clientes.IDCliente
    WHERE
      ${db}MatriculasTreinandos.IDMatricula = '${idmatricula}'`,
      res,
      req.originalUrl
    );
  })

  router.get('/getCallsOfClassesNotMaked/:idmatricula/:qtdeaulas/:idcourses/:idunits/:nome', verifyJWT, (req, res) => {
    var idmatricula = parseInt(req.params.idmatricula);
    var qtdeaulas = parseInt(req.params.qtdeaulas);
    var idcourses = parseInt(req.params.idcourses);
    var idunits = parseInt(req.params.idunits);
    var nome = req.params.nome;
    var sqlQry = `SELECT
    t.idclasses,
    (select name from classes where idclasses = t.idclasses) as Aula,
    ${idcourses} AS CodigoCurso,
    (select name from courses where idcourses = ${idcourses}) as NomeVersao,
    ${idmatricula} AS IDMatricula,
    '${nome}' AS Nome
  FROM (
    SELECT DISTINCT
      CASE WHEN c.idclasses = r.idclasses THEN 1 ELSE 0 END AS Completed,
      c.*
    FROM
      classes c
    LEFT JOIN
      (SELECT DISTINCT
        c.idclasses
      FROM
        stepclass sc
      INNER JOIN
        classes c
      ON
        sc.idclasses = c.idclasses
      WHERE
        sc.idusers = ${idmatricula}
      AND
        c.idcourses = ${idcourses}
      AND
        sc.idunits = ${idunits}) r
    ON
      r.idclasses = c.idclasses
    WHERE
      ordem + 1 <= ${qtdeaulas}
    AND
      idcourses = ${idcourses}
    ORDER BY
      c.ordem) t
  WHERE
    t.Completed = 0
  ORDER BY
    t.ordem;`;
    execSQLQuery(sqlQry, res, req.originalUrl);
  })

  router.get('/getStudentsByTeacher/:db/:idinstrutor', verifyJWT, (req, res) => {
    var db = req.params.db;
    var idinstrutor = parseInt(req.params.idinstrutor);
    execSQLQuery2(`SELECT
      Matriculas2.IDMatricula,
	    Turmas2.CodigoCurso,
      (SELECT COUNT(*) AS Ministradas FROM ${db}TurmasCalendario WHERE CodigoCurso = Turmas2.CodigoCurso AND IdTurma = (SELECT TOP(1) CodigoTurma FROM ${db}TreinandosTurmas WHERE IDMatricula = Matriculas2.IDMatricula) AND Ministrado = 1) AS Ministradas,
      (SELECT TOP(1) c.Nome FROM ${db}Clientes c INNER JOIN ${db}TreinandosTurmas tt ON c.IDCliente = tt.IDTreinando WHERE tt.IDMatricula = Matriculas2.IDMatricula) AS Nome
    FROM (SELECT DISTINCT
        IdTurma,
        CodigoCurso
      FROM ${db}TurmasCalendario tc
      /*WHERE CodigoInstrutor = ${idinstrutor}*/) Turmas2
    INNER JOIN (SELECT DISTINCT
      IDMatricula,
      CodigoTurma
    FROM ${db}Matriculas) Matriculas2 ON Turmas2.IdTurma = Matriculas2.CodigoTurma
    WHERE Turmas2.CodigoCurso > 0;`, res, req.originalUrl);
  })

  router.get('/getQtdeStepclass/:idunits/:idusers/:idcourses', verifyJWT, (req, res) => {
    var idunits = parseInt(req.params.idunits);
    var idusers = parseInt(req.params.idusers);
    var idcourses = parseInt(req.params.idcourses);
    execSQLQuery(`
  SELECT
    COUNT(sc.idusers) as qtde_stepclass
  FROM
    stepclass sc
        INNER JOIN
    classes c ON sc.idclasses = c.idclasses
        INNER JOIN
    courses co ON co.idcourses = c.idcourses
  WHERE
    sc.idusers = ${idusers} AND sc.idunits = ${idunits}
        AND co.idcourses = ${idcourses};`,
      res,
      req.originalUrl
    );
  })

  router.get('/getCalls/:db/:idinstrutor', verifyJWT, (req, res) => {
    const db = req.params.db;
    var idinstrutor = parseInt(req.params.idinstrutor);
    execSQLQuery2(`SELECT DISTINCT
      co.NomeVersao,
      tt.IDMatricula,
      c.Nome,
      CONVERT(varchar, tc.Data, 103) AS Data,
      CASE
        WHEN tc.Ministrado = 0 THEN 'Não' else 'Sim'
      END AS Ministrado
    FROM ${db}TurmasCalendario tc
    INNER JOIN ${db}TreinandosTurmas tt ON tt.CodigoTurma = tc.IdTurma
    INNER JOIN ${db}Clientes c ON c.IDCliente = tt.IDTreinando
    INNER JOIN ${db}Cursos co ON co.Codigo = tc.CodigoCurso
    WHERE /*tc.CodigoInstrutor = ${idinstrutor} and*/ tt.IDMatricula > 190008 and tc.Ministrado = 0;`, res, req.originalUrl);
  });

  router.get('/getClassByOrdem/:idcourses/:ordem', verifyJWT, (req, res) => {
    let idcourses = req.params.idcourses;
    let ordem = req.params.ordem;
    execSQLQuery(`
    SELECT
      idclasses
    FROM
      classes
    WHERE
      idcourses = ${idcourses}
    AND
      ordem = ${ordem};`,
      res,
      req.originalUrl
    );
  })

  router.get('/getExerciseContent/:idclasses', verifyJWT, (req, res) => {
    let idclasses = req.params.idclasses;
    execSQLQuery(`
SELECT
    e.idexercises,
    e.description AS Enunciado,
    eq.idexercisequestions,
    eq.content AS Questao,
    ea.idexerciseanswers,
    ea.content AS Resposta,
    ea.iscorrect,
    ea.hasmedia,
    (select
t.description
    from
texts t
inner join
classescontent cc
on
cc.idgames = t.idtexts
where
cc.idclasses = ${idclasses}) as Jogo
FROM
    classescontent cc
        INNER JOIN
    exercisequestions eq ON cc.idexercises = eq.idexercises
        INNER JOIN
    exercises e ON e.idexercises = eq.idexercises
        INNER JOIN
    exerciseanswers ea ON ea.idexercisequestions = eq.idexercisequestions
WHERE
    cc.idclasses = ${idclasses}
ORDER BY
cc.ordem`, res, req.originalUrl);
  })

  router.post('/getAulasRealizadas', verifyJWT, (req, res) => {
    let idusers = req.body.idusers;
    let idunits = req.body.idunits;
    let ip_externo = req.body.ip_externo;
    let porta_mysql = req.body.porta;

    let SQLQry = `SELECT DISTINCT
      (SELECT idcourses FROM classes WHERE idclasses = sc.idclasses) AS IDCurso,
      (SELECT description FROM courses WHERE idcourses = IDCurso) AS Curso,
      idclasses AS IDAula,
      (SELECT description FROM classes WHERE idclasses = sc.idclasses) AS Aula,
      CASE WHEN datefinal IS NOT NULL THEN datefinal ELSE '--/--/----' END AS Data,
      CASE WHEN completedClass = 0 THEN 'Não concluída' ELSE 'Concluída' END AS Status,
	    CASE WHEN homework = 1 THEN 'Realizada' WHEN homework = 0 THEN 'Falhou' WHEN homework IS NULL THEN 'Não realizada'  END AS LicaoCasa,
      CASE WHEN notegame = 1 THEN 'Sucesso' WHEN notegame IS NULL THEN 'Não concluído' WHEN notegame = 0 THEN 'Falhou' END  AS Jogo,
      (SELECT s.title FROM slides s WHERE idslides = (SELECT cc.idslides FROM classescontent cc INNER JOIN slides s1 ON cc. idslides = s1.idslides WHERE cc.idclasses = IDAula AND cc.idslides IS NOT NULL AND s1.title LIKE 'Montagem%')) AS  Projeto,
      CASE WHEN note IS NOT NULL THEN note ELSE '--' END as Nota,
      (SELECT ordem + 1 FROM classes WHERE idclasses = sc.idclasses) AS Ordem
    FROM
      stepclass sc
    WHERE
      idunits = ${idunits} AND idusers = ${idusers}
    ORDER BY IDCurso, Ordem;`;
    mysqlConnection.query(SQLQry, function (error, results, fields) {
      let Status = 0;
      if (!error && results.length != 0) {
        Status = 200;
        console.log("/getAulasRealizadas success 200");
      }
      else {
        Status = 500;
        console.log("/getAulasRealizadas fail 500 " + error);
      }
      res.send(JSON.stringify({
        "status": Status,
        "error": [],
        "error2": error,
        "response": [],
        "response2": results
      }))
    })

  })

  router.post('/getExerciciosAluno', verifyJWT, (req, res) => {
    let idusers = req.body.idusers;
    let idclasses = req.body.idclasses;
    let idunits = req.body.idunits;
    let ip_externo = req.body.ip_externo;
    let porta_mysql = req.body.porta;

    let SQLQry = `SELECT
      (SELECT e.description FROM exercises e WHERE eq.idexercises = e.idexercises) AS Enunciado,
      eq.content AS Questao,
      ea.content AS Resposta,
      CASE WHEN ea.iscorrect = 1 THEN 'Correta' ELSE 'Errada' END AS Correcao,
      (SELECT e.idexercisetype FROM exercises e WHERE eq.idexercises = e.idexercises) AS Tipo
    FROM
      exercisequestions eq
    INNER JOIN
      exerciseanswers ea
    ON
      eq.idexercisequestions = ea.idexercisequestions
    INNER JOIN
      usergivenanswers ua
    ON
      ua.idexerciseanswers = ea.idexerciseanswers
    WHERE
      ua.idusers = ${idusers} AND ua.idclasses = ${idclasses} and ua.idunits = ${idunits};`;

    mysqlConnection.query(SQLQry, function (error, results, fields) {
      let Status = 0;
      if (!error && results.length != 0) {
        Status = 200;
        console.log("/getAulasRealizadas success 200");
      }
      else {
        Status = 500;
        console.log("/getAulasRealizadas fail 500 " + error);
      }

      res.send(JSON.stringify({
        "status": Status,
        "error2": error,
        "response2": results
      }))

    })

  })

  router.post('/getTurmasDetalhes', verifyJWT, (req, res) => {
    const idfuncuionario = parseInt(req.body.IDFuncionario);
    const Unidades = req.body.Unidades;
    let SQLQuery = "";
    for (let i = 0; i < Unidades.length; i++) {
      if (i > 0) {
        SQLQuery += `union all
        `;
      }
      /*SQLQuery += `select distinct
        ${Unidades[i]['idunits']} as 'IDUnits',
        '${Unidades[i]['unityname']}' as 'Unidade',
        tt.IDMatricula as 'Matricula',
        cl.Nome as 'Aluno',
        tu.IdTurma,
        cu.Codigo as 'IDCurso',
        cu.NomeVersao as 'Curso',
        (select top 1 convert(varchar, tc2.HoraInicial, 8) from ${Unidades[i]['reference_db']}.dbo.TurmasCalendario tc2 where tc2.IdTurma = tu.IdTurma) as 'HoraInicial',
        (select top 1 convert(varchar, tc2.HoraFinal, 8) from ${Unidades[i]['reference_db']}.dbo.TurmasCalendario tc2 where tc2.IdTurma = tu.IdTurma) as 'HoraFinal',
        --convert(varchar, tu.HoraInicial, 8) as 'HoraInicial',
        --convert(varchar, tu.HoraFinal, 8) as 'HoraFinal',
        convert(VARCHAR, tu.DataInicio, 103) as 'DataInicio',
        convert(VARCHAR, tu.DataTermino, 103) as 'DataTermino',
        (select count(*) from ${Unidades[i]['reference_db']}.dbo.TurmasCalendario tc1 where tc1.CodigoCurso = cu.Codigo and tc1.IdTurma = tu.IdTurma and tc1.Ministrado = 1) as 'Chamadas'
      from ${Unidades[i]['reference_db']}.dbo.Turmas tu
      inner join ${Unidades[i]['reference_db']}.dbo.TurmasCalendario tc on tc.IdTurma = tu.IdTurma
      inner join ${Unidades[i]['reference_db']}.dbo.Cursos cu on cu.Codigo = tc.CodigoCurso
      inner join ${Unidades[i]['reference_db']}.dbo.TreinandosTurmas tt on tt.CodigoTurma = tu.IdTurma and tt.IDCurso = cu.Codigo
      inner join ${Unidades[i]['reference_db']}.dbo.Clientes cl on cl.IDCliente = tt.IDTreinando
      `;*/
      SQLQuery += `select distinct
        ${Unidades[i]['idunits']} as 'IDUnits',
        '${Unidades[i]['unityname']}' as 'Unidade',
        mt.IDMatricula as 'Matricula',
      	cl.Nome as 'Aluno',
      	tu.IdTurma,
      	tt.IDCurso,
      	cu.NomeVersao as 'Curso',
      	(select top 1 convert(varchar, tc2.HoraInicial, 8) from ${Unidades[i]['reference_db']}.dbo.TurmasCalendario tc2 where tc2.IdTurma = tu.IdTurma) as 'HoraInicial',
        (select top 1 convert(varchar, tc2.HoraFinal, 8) from ${Unidades[i]['reference_db']}.dbo.TurmasCalendario tc2 where tc2.IdTurma = tu.IdTurma) as 'HoraFinal',
      	convert(varchar, tu.DataInicio, 103) as 'DataInicio',
      	convert(varchar, tu.DataTermino, 103) as 'DataTermino',
        (select count(*) from ${Unidades[i]['reference_db']}.dbo.TurmasCalendario tc1 where tc1.CodigoCurso = tt.IDCurso and tc1.IdTurma = tu.IdTurma and tc1.Ministrado = 1) as 'Chamadas'
      from ${Unidades[i]['reference_db']}.dbo.Matriculas mt
      inner join ${Unidades[i]['reference_db']}.dbo.Turmas tu on tu.IdTurma = mt.CodigoTurma
      inner join ${Unidades[i]['reference_db']}.dbo.TreinandosTurmas	tt on tt.IDMatricula = mt.IDMatricula
      inner join ${Unidades[i]['reference_db']}.dbo.Clientes cl on cl.IDCliente = tt.IDTreinando
      inner join ${Unidades[i]['reference_db']}.dbo.Cursos cu on cu.Codigo = tt.IDCurso
      `;
    }
    SQLQuery += `/*where tc.CodigoInstrutor = ${idfuncuionario}*/
      group by mt.IDMatricula, cl.Nome, tu.IdTurma, tt.IDCurso, cu.NomeVersao, HoraInicial, HoraFinal, tu.DataInicio,  tu.DataTermino
      order by mt.IDMatricula asc;`;

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

  router.get('/getProgressoAula/:idusers/:idunits/:idcourses', verifyJWT, (req, res) => {
    const idusers = parseInt(req.params.idusers);
    const idunits = parseInt(req.params.idunits);
    const idcourses = parseInt(req.params.idcourses);
    execSQLQuery(`select distinct
      *,
      (Tab1.NM / Tab1.VezesAssistida) as 'NotaMedia',
      (Tab1.NMGeral / Tab1.VezesAssistidaGeral) as 'NotaMediaGeral'
    from (select
      cl.idclasses,
      sc.idstepclass,
      sc.idusers,
      sc.idunits,
      cl.name as 'Aula',
      case when sc.idstepclass then 'Sim' else 'Não' end as 'Iniciada',
      case when sc.completedClass = 1 then 'Sim' else 'Não' end as 'Finalizada',
      date_format(sc.insert_date, '%d/%m/%Y %h:%i') as 'DataInicio',
      date_format(sc.update_date, '%d/%m/%Y %h:%i') as 'DataFinalizada',
      sc.datefinal as 'DataFechamento',
      case when sc.liberado = 1 then 'Sim' else 'Não' end as 'liberado',
      case
        when sc.homework = 1 or ((select count(*) from userstages us where us.idclasses = sc.idclasses and us.idunits = sc.idunits and us.idusers = sc.idusers and us.idstages = 2) > 0) then 'Concluída'
        when sc.homework is null and sc.completedClass = 1 then 'Não Realizada'
        else null
	    end as 'TarefaCasa',
      (select date_format(us.insert_date, '%d/%m/%Y %h:%i') from userstages us where us.idclasses = sc.idclasses and us.idunits = sc.idunits and us.idusers = sc.idusers and us.idstages = 2 limit 1) as 'DataTarefa',
      (select round(us.note, 2) from userstages us where us.idclasses = sc.idclasses and us.idunits = sc.idunits and us.idusers = sc.idusers and us.idstages = 3 limit 1) as 'notaProjeto',
      round(sc.note, 2) as 'Nota',
      case
		    when (sc.notegame = 1 and sc.completedClass = 1) then (sum(ea.iscorrect) + 1)
        when ((sc.notegame = 0 or sc.notegame is null) and sc.completedClass = 1) then coalesce(sum(ea.iscorrect), 0)
		    else null
	    end as 'Acertos',
      case
		    when (sc.notegame = 0 and sc.completedClass = 1) then (sum(case when ea.iscorrect = 1 then 0 else 1 end) + 1)
        when ((sc.notegame = 1 or sc.notegame is null) and sc.completedClass = 1) then coalesce(sum(case when ea.iscorrect = 1  then 0 else 1 end), 0)
        else null
      end as 'Erros',
      ExA.Questoes,
      (select count(*) from stepclass sc1 where sc1.idclasses = cl.idclasses and sc1.idunits = ${idunits}) as 'VezesAssistida',
      (select count(*) from stepclass sc1 where sc1.idclasses = cl.idclasses) as 'VezesAssistidaGeral',
      (select count(*) from classes cl1 where cl1.idcourses = cl.idcourses and cl1.disabled = 0) as 'AulasCurso',
      (select sum(sc1.note) from stepclass sc1 where sc1.idclasses = cl.idclasses and sc1.idunits = ${idunits}) as 'NM',
      (select sum(sc1.note) from stepclass sc1 where sc1.idclasses = cl.idclasses) as 'NMGeral',
      cn.note as 'NotaCurso'
    from classes cl
    left join stepclass sc on cl.idclasses = sc.idclasses and sc.idusers = ${idusers} and sc.idunits = ${idunits}
    left join usergivenanswers ug on ug.idclasses = sc.idclasses and ug.idusers = sc.idusers and ug.idunits = sc.idunits
    left join exerciseanswers ea on ea.idexerciseanswers = ug.idexerciseanswers
    left join coursenotes cn on cn.idcourses = ${idcourses} and cn.idunits = ${idunits} and cn.idusers = ${idusers}
    inner join (select distinct
      cl.idclasses,
      cl.name as 'Aula',
      count(cc.idexercises) as 'Questoes'
    from classes cl
    inner join classescontent cc on cc.idclasses = cl.idclasses
    left join exercisequestions eq on eq.idexercises = cc.idexercises
    group by cl.idclasses, cc.idclasses
    order by cl.idclasses asc) ExA on cl.idclasses = ExA.idclasses
    where cl.idcourses = ${idcourses}
    group by cl.idclasses, sc.idusers, sc.idunits, cl.name, sc.completedClass, sc.insert_date, sc.update_date, sc. note, sc.notegame, cl.ordem, cn.note
    order by cl.ordem asc) Tab1;`, res, req.originalUrl);
  });

  router.post('/getAulasDoDiaSQLServer', 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
      * ,
      case when R.Ministrado = 'Sim' then (select count(*) from ${Unidades[i]}Chamadas ch where ch.IDCurso = R.IDCurso and ch.IdTurma = R.IdTurma) else (select (count(*) + 1) from ${Unidades[i]}Chamadas ch where ch.IDCurso = R.IDCurso and ch.IdTurma = R.IdTurma) end as 'Chamadas'
    from (select
      fu.Nome as 'Professor',
      tc.IdTurma,
      tt.IDMatricula,
      cl.Nome as 'Aluno',
      tt.IDCurso,
      cu.NomeVersao as 'Curso',
      CONVERT(varchar(10), tc.Data, 103) as 'Data',
      CONVERT(varchar(10), tc.HoraInicial, 108) as 'HoraInicial',
      CONVERT(varchar(10), tc.HoraFinal, 108) as 'HoraFinal',
      case when tc.MInistrado = 1 then 'Sim' else 'Não' end as 'Ministrado',
      '${Unidades[i].replace('.dbo.', '')}' as 'Unidade',
      0 as 'Concluida'
    from ${Unidades[i]}TurmasCalendario tc
    inner join ${Unidades[i]}TreinandosTurmas tt on tt.CodigoTurma = tc.IdTurma and tt.IDCurso = tc.CodigoCurso
    inner join ${Unidades[i]}Clientes cl on cl.IDCliente = tt.IDTreinando
    inner join ${Unidades[i]}Cursos cu on cu.Codigo = tt.IDCurso
    inner join ${Unidades[i]}Funcionarios fu on fu.IDFuncionario = tc.CodigoInstrutor
    where tc.Data >= '${DataInicial}' and tc.Data <= '${DataFinal}' and tt.IDMatricula > 190008) R
    `;
    }
    SQLServerQuery += `order by R.Data asc, R.HoraInicial asc;`;

    sqlServerConnection.request().query(SQLServerQuery, function (error, results, fields) {
      if (!error && results && results['recordset']) {
        let Calendario = results['recordset'];
        mysqlConnection.query(`select * from classes;`, function (error, Aulas, fields) {
          for (let i = 0; i < Calendario.length; i++) {
            for (let o = 0; o < Aulas.length; o++) {
              if (Calendario[i]['IDCurso'] == Aulas[o]['idcourses'] && Calendario[i]['Chamadas'] == (Aulas[o]['ordem'] + 1)) {
                Calendario[i]['Aula'] = Calendario[i]['Chamadas'] + " - " + Aulas[o]['description'];
                break;
              }
            }
          }
          res.send(JSON.stringify({
            "status": 200,
            "error": error,
            "response": Calendario,
          }));
          console.log(new Date().toUTCString() + " - " + req.originalUrl + " success 200");
        });
      }
      else {
        res.send(JSON.stringify({
          "status": 500,
          "error": error,
          "response": [],
        }));
        console.log(req.originalUrl + " error 500");
      }
    });
  });

  router.post('/getAulasDoDiaMySQL', verifyJWT, (req, res) => {
    const Unidades = req.body.Unidades;
    const DataInicial = req.body.DataInicial;
    const DataFinal = req.body.DataFinal;
    let SQLQuery = `select distinct * from (`;
    for (let i = 0; i < Unidades.length; i++) {
      if (i > 0) {
        SQLQuery += `union all
        `;
      }
      SQLQuery += `SELECT distinct
      date_format(sc.insert_date, '%d/%m/%Y') as 'Data',
        '' as 'Professor',
        sc.idusers as 'IDMatricula',
        '' as 'Aluno',
        cl.idcourses as 'IDCurso',
        co.name as 'Curso',
        concat(cl.ordem + 1, ' - ', cl.description) as 'Aula',
        date_format(sc.insert_date, '%H:%i:%s') as 'HoraInicial',
        date_format(sc.update_date, '%H:%i:%s') as 'HoraFinal',
        un.reference_db as 'Unidade',
        sc.completedClass as 'Concluida',
        false as 'Assistida',
        2 as 'Horas'
    FROM stepclass sc
    inner join classes cl on cl.idclasses = sc.idclasses
    inner join units un on un.idunits = sc.idunits and un.reference_db = '${Unidades[i].replace('.dbo.', '')}'
    inner join courses co on co.idcourses = cl.idcourses
    where date_format(sc.insert_date, '%Y-%m-%d') >= '${DataInicial}' and date_format(sc.insert_date, '%Y-%m-%d') <= '${DataFinal}'
    `;
    }
    SQLQuery += `order by Data asc, HoraInicial asc) R;`;

    mysqlConnection.query(SQLQuery, function (error, Aulas, fields) {
      res.send(JSON.stringify({
        "status": 200,
        "error": error,
        "response": Aulas,
      }));
      console.log(new Date().toUTCString() + " - " + req.originalUrl + " success 200");
    });
  });

  router.post('/getCursosAluno', verifyJWT, (req, res) => {
    const idcourses = req.body.idcourses;
    const idusers = parseInt(req.body.idusers);
    const idunits = parseInt(req.body.idunits);
    const SQLQuery1 = `select distinct
      co.idcourses,
      co.name as 'Curso',
      co.durationtraineeindividual as 'CargaHoraria',
      co.aditional as 'Adicional',
      co.extracourse as 'CursoExtra',
      (select un.unityname from units un where un.idunits = ${idunits}) as 'Unidade',
      (select count(*) from classes cl where cl.idcourses = co.idcourses) as 'Aulas',
      (select count(*) from classes cl inner join stepclass sc on sc.idclasses = cl.idclasses where cl.idcourses = co.idcourses and sc.idusers = ${idusers} and sc.idunits = ${idunits}) as 'AulasAssistidas'
    from courses co
    left join classes cl on cl.idcourses = co.idcourses
    left join stepclass sc on sc.idclasses = cl.idclasses
    where co.idcourses in ${idcourses}
    group by co.idcourses, co.name, co.durationtraineeindividual, co.aditional, co.extracourse;`;
    mysqlConnection.query(SQLQuery1, function (error, Cursos, fields) {
      let ExitCursos = [];
      for (let i = 0; i < Cursos.length; i++) {
        if (Cursos[i]['Curso'].includes('Módulo')) {
          let Incluso = false;
          for (let o = 0; o < ExitCursos.length; o++) {
            if (Cursos[i]['Curso'].includes(ExitCursos[o]['Curso'])) {
              ExitCursos[o]['QtdModulos']++;
              Incluso = true;
              break;
            }
          }
          if (!Incluso)
            ExitCursos.push( { 'Curso': Cursos[i]['Curso'].split(' - Módulo')[0], 'QtdModulos': 1, 'Modulos': [] } );
          ExitCursos[ExitCursos.length - 1]['Modulos'].push( Cursos[i] );
        }
        else {
          ExitCursos.push( { 'Curso': Cursos[i]['Curso'].split(' - Módulo')[0], 'QtdModulos': 1, 'Modulos': [] } );
          ExitCursos[ExitCursos.length - 1]['Modulos'].push( Cursos[i] );
        }
      }
      res.send(JSON.stringify({
        "status": 200,
        "error": error,
        "response": ExitCursos
      }));
      console.log(new Date().toUTCString() + " - " + req.originalUrl + " success 200");
    });
  });

  return router;
};

module.exports = routes;