597 lines
24 KiB
JavaScript
597 lines
24 KiB
JavaScript
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; |