67 lines
2.3 KiB
JavaScript
67 lines
2.3 KiB
JavaScript
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; |