require('../connections/mysql-server');

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 () {
    router.get('/getStudents/:codello/:startdate/:enddate/:tkapi', (req, res) => {
      
      let codello = req.params.codello;
      let tkapi = req.params.tkapi;
      let startdate = req.params.startdate;
      let enddate = req.params.enddate;

      let sqlQry = `select reference_db, studenthourvalue 
                    from units
                    cross join config
                    where codello = ${codello}
                    and tk_api = '${tkapi}'`;

      mysqlConnection.query(sqlQry, function (error, results, fields) {
        if (!error && results.length != 0) {
          
          var studenthourvalue = results[0]['studenthourvalue'];
          var reference_db = results[0]['reference_db'];

          let sqlQry = `select *,
                        (ceiling(convert(decimal(10,2),HoraAula)/8) * ${studenthourvalue}) as Valor 
                        from (
                          select TT.IDMatricula as Matricula,
			  C.Nome as Nome,
			  sum(datediff(HOUR,TC.HoraInicial,TC.HoraFinal)) as HoraAula
			  from ${reference_db}.dbo.TreinandosTurmas TT
			  inner join ${reference_db}.dbo.TurmasCalendario TC
			  on TC.IdTurma = TT.CodigoTurma
			  and TC.CodigoCurso = TT.IDCurso
			  inner join ${reference_db}.dbo.Clientes C
			  on C.IDCliente = TT.IDTreinando
			  where TC.Ministrado = 1
			  and TT.IDMatricula > 190008
			  and TC.Data between '${startdate}' and '${enddate}'
			  group by IDMatricula, Nome
                        ) Res
                        order by Matricula asc`;
          
          sqlServerConnection.request().query(sqlQry, function (error2, results2, fields) {
            if (!error2 && results2.length != 0) {
              //console.log(results2.recordset);
              res.status(200).send(results2.recordset);
            } else {
              res.status(500).send('Não foram encontrados resultados para a pesquisa');
            }
          });
        }
        else {
          res.status(500).send('Código do Cliente ou Token Inválido');
        }
      });
    })

    return router;
}

module.exports = routes;