1642 lines
58 KiB
JavaScript
1642 lines
58 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({ extended: true }));
|
|
router.use(bodyParser.json());
|
|
|
|
var routes = function () {
|
|
router.get('/checkFranchiseePortal', (req, res) => {
|
|
execSQLQuery(
|
|
`SELECT
|
|
franchiseeportal
|
|
FROM config;`,
|
|
res,
|
|
req.originalUrl
|
|
);
|
|
});
|
|
|
|
router.get('/changeFranchiseePortal/:estado', (req, res) => {
|
|
const estado = req.params.estado ? 1 : 0;
|
|
execSQLQuery(
|
|
`UPDATE config SET
|
|
franchiseeportal = ${estado}
|
|
WHERE idconfig = 1;`,
|
|
res,
|
|
req.originalUrl
|
|
);
|
|
});
|
|
|
|
router.get('/checkVersionApp', (req, res) => {
|
|
execSQLQuery(
|
|
`SELECT
|
|
versionapp
|
|
FROM config;`,
|
|
res,
|
|
req.originalUrl
|
|
);
|
|
});
|
|
|
|
router.get('/checkVersionRobot/:idrobot', (req, res) => {
|
|
let idrobot = req.params.idrobot;
|
|
let Caminho = Variables('src_media') + 'downloads/';
|
|
let fs = require('fs');
|
|
if (idrobot == 0) Caminho += 'ZoeRobot';
|
|
else if (idrobot == 1) Caminho += 'MiniRobot';
|
|
fs.readFile(Caminho + '/versao.txt', 'utf8', (err, data) => {
|
|
if (err) throw err;
|
|
console.log(
|
|
new Date().toUTCString() + ' - ' + req.originalUrl + ' success 200'
|
|
);
|
|
res.send(data);
|
|
});
|
|
//execSQLQuery('SELECT versionrobot, messagerobot FROM config;', res, req.originalUrl);
|
|
});
|
|
|
|
router.get('/getZoeRobots', (req, res) => {
|
|
mysqlConnection.query(
|
|
`SELECT * FROM zoerobots;`,
|
|
function (error, results, fields) {
|
|
console.log(
|
|
new Date().toUTCString() + ' - ' + req.originalUrl + ' success 200'
|
|
);
|
|
res.send(JSON.stringify(results));
|
|
}
|
|
);
|
|
});
|
|
|
|
router.get('/getZoeRobotsApks/:idzoerobots', (req, res) => {
|
|
const idzoerobots = req.params.idzoerobots;
|
|
mysqlConnection.query(
|
|
`SELECT * FROM zoerobotsapks where idzoerobots = ${idzoerobots};`,
|
|
function (error, results, fields) {
|
|
console.log(
|
|
new Date().toUTCString() + ' - ' + req.originalUrl + ' success 200'
|
|
);
|
|
res.send(`{"Aplicativos": ` + JSON.stringify(results) + `}`);
|
|
}
|
|
);
|
|
});
|
|
|
|
router.post('/insertZoeRobotsApks', (req, res) => {
|
|
const idzoerobots = req.body.idzoerobots;
|
|
const descricao = req.body.descricao;
|
|
const versao = req.body.versao;
|
|
const mensagem = req.body.mensagem;
|
|
const packagename = req.body.packagename;
|
|
const enderecoapk = req.body.enderecoapk;
|
|
const nomeinstalador = req.body.nomeinstalador;
|
|
const pasta = req.body.pasta;
|
|
execSQLQuery(
|
|
`insert into zoerobotsapks (idzoerobots, descricao, versao, mensagem, packagename, enderecoapk, nomeinstalador, pasta) values (?, ?, ?, ?, ?, ?, ?, ?);`,
|
|
res,
|
|
req.originalUrl,
|
|
[
|
|
idzoerobots,
|
|
descricao,
|
|
versao,
|
|
mensagem,
|
|
packagename,
|
|
enderecoapk,
|
|
nomeinstalador,
|
|
pasta,
|
|
]
|
|
);
|
|
});
|
|
|
|
router.delete('/deleteZoeRobotsApks/:idzoerobots', (req, res) => {
|
|
const idzoerobots = req.params.idzoerobots;
|
|
execSQLQuery(
|
|
`delete from zoerobotsapks WHERE idzoerobots = ${idzoerobots};`,
|
|
res,
|
|
req.originalUrl
|
|
);
|
|
});
|
|
|
|
router.post('/SetVersaoRobotFile/:idrobot', verifyJWT, (req, res) => {
|
|
let Caminho = Variables('src_media') + 'downloads/';
|
|
const fs = require('fs');
|
|
const Json = JSON.stringify(req.body);
|
|
const idrobot = parseInt(req.params.idrobot);
|
|
if (idrobot == 0) Caminho += 'ZoeRobot';
|
|
else if (idrobot == 1) Caminho += 'MiniRobot';
|
|
fs.writeFile(Caminho + '/versao.txt', Json, (err) => {
|
|
if (err) throw err;
|
|
console.log(
|
|
new Date().toUTCString() + ' - ' + req.originalUrl + ' success 200'
|
|
);
|
|
res.send(true);
|
|
});
|
|
});
|
|
|
|
router.get('/checkRobotCourses', (req, res) => {
|
|
execSQLQuery(
|
|
`SELECT
|
|
robotcourses
|
|
FROM config;`,
|
|
res,
|
|
req.originalUrl
|
|
);
|
|
});
|
|
|
|
router.post('/checkexistnotificationclose/:tk', verifyJWT, (req, res) => {
|
|
let idteacher = req.body.idteacher;
|
|
let db = req.body.db;
|
|
execSQLQuery2(
|
|
`
|
|
SELECT
|
|
IDNotificacoes,
|
|
IDChamadaAPP
|
|
FROM
|
|
${db}NotificacoesErroEnvioAPP
|
|
WHERE
|
|
IDInstrutor = ${idteacher}
|
|
AND
|
|
EnviarRespostaAPP = 1
|
|
AND
|
|
RespostaRecebidaAPP = 0
|
|
AND
|
|
IDChamadaAPP != NULL;`,
|
|
res,
|
|
req.originalUrl
|
|
);
|
|
});
|
|
|
|
router.post('/notificationsteacher/:tk', verifyJWT, (req, res) => {
|
|
let idteacher = req.body.idteacher;
|
|
let idcall = req.body.idcall;
|
|
let db = req.body.db;
|
|
execSQLQuery2(
|
|
`
|
|
SELECT
|
|
*
|
|
FROM
|
|
${db}NotificacoesErroEnvioAPP
|
|
WHERE
|
|
IDInstrutor = ${idteacher}
|
|
AND
|
|
IDChamadaAPP = ${idcall};`,
|
|
res,
|
|
req.originalUrl
|
|
);
|
|
});
|
|
|
|
router.post('/send/:tk', verifyJWT, (req, res) => {
|
|
let db = req.body.db;
|
|
let idteacher = req.body.idteacher;
|
|
let description = req.body.desc;
|
|
let date = req.body.date;
|
|
let students = req.body.students;
|
|
let content = req.body.content;
|
|
var StudentsIDs = students.split(',');
|
|
let Nomes = '';
|
|
|
|
let sqlQry = `
|
|
SELECT
|
|
*
|
|
FROM
|
|
${db}FuncionariosAcesso
|
|
WHERE
|
|
IDFuncionario = ${idteacher}
|
|
AND
|
|
DescricaoAcesso = '${description}'
|
|
AND
|
|
permitido = 1;`;
|
|
//Check user have permission to this
|
|
sqlServerConnection
|
|
.request()
|
|
.query(sqlQry, function (error, results, fields) {
|
|
if (!error && results.length != 0) {
|
|
//Check if date exist in TurmasCalendario
|
|
CheckDateExists(
|
|
db,
|
|
date,
|
|
idteacher,
|
|
StudentsIDs,
|
|
0,
|
|
Nomes,
|
|
res,
|
|
content,
|
|
null
|
|
);
|
|
} else {
|
|
res.send(
|
|
'Funcionário não tem acesso a "Inclusão de Controle de Chamadas"'
|
|
);
|
|
}
|
|
});
|
|
|
|
/*if(checkAccessEmployee(db, idteacher, description)) {
|
|
var ResultTurmasCalendario = SeacrhCall(db, date, idteacher, idstudent);
|
|
res.send(ResultTurmasCalendario);
|
|
}
|
|
else {
|
|
res.send('Funcionário não tem acesso a "Inclusão de Controle de Chamadas"');
|
|
}*/
|
|
});
|
|
|
|
router.post('/checkclass/:tk', verifyJWT, (req, res) => {
|
|
let date = req.body.date;
|
|
let idstudent = req.body.idstudent;
|
|
let db = req.body.db;
|
|
execSQLQuery2(
|
|
`
|
|
SELECT top(1) *
|
|
FROM ${db}TurmasCalendario
|
|
where IDTurma = ${idstudent}
|
|
and Ministrado = 0
|
|
order by Data asc;`,
|
|
res,
|
|
req.originalUrl
|
|
);
|
|
});
|
|
|
|
router.post('/homeworkend', verifyJWT, (req, res) => {
|
|
const homework = req.body.homework;
|
|
const idstudent = parseInt(req.body.idstudent || req.body.idusers);
|
|
const idunits = parseInt(req.body.idunits);
|
|
const idclasses = parseInt(req.body.idclasses);
|
|
|
|
const SQLQry = `UPDATE stepclass SET
|
|
homework = (SELECT CASE WHEN r.qtde = 0 THEN 0 ELSE ${homework} END AS home FROM
|
|
(SELECT COUNT(*) AS qtde FROM stepclass WHERE idusers = ${idstudent} AND idunits = ${idunits} AND idclasses = ${idclasses} AND STR_TO_DATE(datefinal, '%d/%m/%Y') > NOW()) r)
|
|
WHERE idusers = ${idstudent} AND idunits = ${idunits} AND idclasses = ${idclasses};`;
|
|
|
|
mysqlConnection.query(SQLQry, function (error, results, fields) {
|
|
if (!error && results.length != 0) {
|
|
res.send(200);
|
|
console.log(
|
|
new Date().toUTCString() + ' - ' + req.originalUrl + ' success 200'
|
|
);
|
|
} else {
|
|
res.send(500);
|
|
console.log(
|
|
new Date().toUTCString() +
|
|
' - ' +
|
|
req.originalUrl +
|
|
' fail 500 ' +
|
|
error
|
|
);
|
|
}
|
|
});
|
|
InsertUserStage(idstudent, idunits, idclasses, homework);
|
|
InsertNotes();
|
|
//InsertNotasDetalhes(idstudent, idunits, idclasses);
|
|
});
|
|
|
|
router.get('/InsertNotes', (req, res) => {
|
|
if (InsertNotes()) res.send({ error: null, response: false, status: 500 });
|
|
else res.send({ error: null, response: true, status: 200 });
|
|
});
|
|
|
|
router.get('/verifyCorrection/:idunits', verifyJWT, (req, res) => {
|
|
let idunits = req.params.idunits;
|
|
execSQLQuery(
|
|
`
|
|
SELECT
|
|
idusers,
|
|
idclasses,
|
|
homework,
|
|
datefinal
|
|
FROM
|
|
stepclass
|
|
WHERE
|
|
note IS NULL
|
|
AND
|
|
completedClass = 1
|
|
AND
|
|
idunits = ${idunits};`,
|
|
res,
|
|
req.originalUrl
|
|
);
|
|
});
|
|
|
|
router.post('/listQRcodesByAny/:tk', verifyJWT, (req, res) => {
|
|
var value = req.body.value;
|
|
var db = req.body.db;
|
|
execSQLQuery2(
|
|
`
|
|
SELECT
|
|
c.Nome,
|
|
mt.IDMatricula,
|
|
t.IdTurma
|
|
FROM
|
|
${db}Turmas t
|
|
INNER JOIN
|
|
${db}MatriculasTreinandos mt
|
|
ON
|
|
t.IdTurma = mt.CodigoTurma
|
|
INNER JOIN
|
|
${db}Clientes c
|
|
ON
|
|
c.IDCliente = mt.IDTreinando
|
|
INNER JOIN
|
|
${db}Matriculas m
|
|
ON
|
|
m.IDMatricula = mt.IDMatricula
|
|
WHERE
|
|
m.QRCode = '${value}';`,
|
|
res,
|
|
req.originalUrl
|
|
);
|
|
});
|
|
|
|
router.get('/linkStudent/:db/:idusers/:qrcode/:tk', (req, res) => {
|
|
let db = req.params.db;
|
|
let idusers = req.params.idusers;
|
|
let qrcode = req.params.qrcode;
|
|
let token = req.params.tk;
|
|
|
|
if (token == Variables('tk_fixed')) {
|
|
let sqlQry = `
|
|
SELECT
|
|
*
|
|
FROM
|
|
units
|
|
WHERE
|
|
reference_db = '${db}'
|
|
AND
|
|
enabled = 1;`;
|
|
|
|
mysqlConnection.query(sqlQry, function (error, results, fields) {
|
|
if (!error && results.length != 0) {
|
|
var idunits = results[0]['idunits'];
|
|
let sqlQry2 = `
|
|
UPDATE
|
|
qrcodes
|
|
SET
|
|
idusers = ${idusers},
|
|
idunits = ${idunits}
|
|
WHERE
|
|
_key = '${qrcode}'
|
|
AND
|
|
idusers is null;`;
|
|
|
|
execSQLQuery(sqlQry2, res, req.originalUrl);
|
|
} else {
|
|
res.send('not found');
|
|
}
|
|
});
|
|
} else {
|
|
res.status(500).send({ status: 'expired' });
|
|
}
|
|
});
|
|
|
|
router.get('/linkBarCode/:db/:idusers/:_key/:idcourses/:tk', (req, res) => {
|
|
let db = req.params.db;
|
|
let idusers = req.params.idusers;
|
|
let _key = req.params._key;
|
|
let idcourses = req.params.idcourses;
|
|
let token = req.params.tk;
|
|
if (token == Variables('tk_fixed')) {
|
|
let sqlQry = `
|
|
SELECT
|
|
*
|
|
FROM
|
|
units
|
|
WHERE
|
|
reference_db = '${db}'
|
|
AND
|
|
enabled = 1;`;
|
|
|
|
mysqlConnection.query(sqlQry, function (error, results, fields) {
|
|
if (!error && results.length != 0) {
|
|
var idunits = results[0]['idunits'];
|
|
let sqlQry2 = `
|
|
UPDATE
|
|
barcodes
|
|
SET
|
|
idusers = ${idusers},
|
|
idunits = ${idunits},
|
|
idcourses = ${idcourses}
|
|
WHERE
|
|
_key = '${_key}'
|
|
AND
|
|
idusers is null;`;
|
|
|
|
execSQLQuery(sqlQry2, res, req.originalUrl);
|
|
} else {
|
|
res.send('not found');
|
|
}
|
|
});
|
|
|
|
//execSQLQuery(sqlQry, res, req.originalUrl);
|
|
} else {
|
|
res.status(500).send({ status: 'expired' });
|
|
}
|
|
});
|
|
|
|
router.get('/getCuriosities/:tk', verifyJWT, (req, res) => {
|
|
execSQLQuery(`SELECT * FROM curiosities`, res, req.originalUrl);
|
|
});
|
|
|
|
router.get('/checkQR/:_key/:_token', (req, res) => {
|
|
let _key = req.params._key;
|
|
let token = req.params._token;
|
|
|
|
if (token != Variables('tk_fixed')) {
|
|
res.status(500).send({ status: 'expired' });
|
|
} else {
|
|
var sqlQry = `
|
|
SELECT
|
|
*
|
|
FROM
|
|
qrcodes
|
|
WHERE
|
|
_key = '${_key}';`;
|
|
|
|
mysqlConnection.query(sqlQry, function (error, results, fields) {
|
|
if (!error && results.length != 0) {
|
|
var ordem = results[0]['ordem'];
|
|
var idqrlotes = results[0]['idqrlotes'];
|
|
var src =
|
|
Variables('src_qrcodes') +
|
|
idqrlotes +
|
|
'/qr_' +
|
|
idqrlotes +
|
|
'_' +
|
|
ordem +
|
|
'.jpg';
|
|
var cmd = '';
|
|
if (results[0]['idusers']) cmd = 'N,';
|
|
else cmd = 'S,';
|
|
console.log("/checkQR/ success 200 - '" + cmd + src + "'");
|
|
res.send(cmd + src);
|
|
} else {
|
|
res.status(500).send('QRCode não encontrado');
|
|
}
|
|
});
|
|
}
|
|
});
|
|
|
|
router.get(
|
|
'/isLastClass/:idusers/:idunits/:idclasses',
|
|
verifyJWT,
|
|
(req, res) => {
|
|
let idusers = req.params.idusers;
|
|
let idunits = req.params.idunits;
|
|
let idclasses = req.params.idclasses;
|
|
|
|
execSQLQuery(
|
|
`update stepclass set
|
|
homework = 1
|
|
where (SELECT aval FROM classes WHERE idclasses = ${idclasses}) = 1
|
|
and idusers = ${idusers} and idunits = ${idunits} and idclasses = ${idclasses};`,
|
|
res,
|
|
req.originalUrl
|
|
);
|
|
setTimeout(() => {
|
|
InsertNotes();
|
|
setTimeout(() => {
|
|
InsertCourseNotes();
|
|
}, 200);
|
|
}, 500);
|
|
}
|
|
);
|
|
|
|
router.get(
|
|
'/checkHasStudentsToNotificate/:idteacher/:db',
|
|
verifyJWT,
|
|
(req, res) => {
|
|
console.log('/checkHasStudentsToNotificate success 200');
|
|
let idteacher = req.params.idteacher;
|
|
let db = req.params.db;
|
|
let sqlQuery = `
|
|
SELECT DISTINCT
|
|
tc.IdTurma, tc.CodigoCurso, tt.IDMatricula
|
|
FROM
|
|
${db}TurmasCalendario tc
|
|
INNER JOIN
|
|
${db}TreinandosTurmas tt
|
|
ON
|
|
tc.IdTurma = tt.CodigoTurma
|
|
AND
|
|
tc.CodigoCurso = tt.IDCurso
|
|
WHERE
|
|
tc.Ministrado = 1
|
|
AND
|
|
tc.CodigoInstrutor = ${idteacher};
|
|
`;
|
|
sqlServerConnection
|
|
.request()
|
|
.query(sqlQuery, function (error, results, fields) {
|
|
if (!error && results.length != 0) {
|
|
//res.send(results);
|
|
//console.log(results);
|
|
indexStudents = 0;
|
|
LoopStudentsOfTeacher(results, res, db, idteacher);
|
|
} else {
|
|
res.send(false);
|
|
}
|
|
});
|
|
}
|
|
);
|
|
|
|
router.post('/insertScoregame/:tk', (req, res) => {
|
|
let token = req.params.tk;
|
|
if (token != Variables('tk_fixed')) {
|
|
res.status(500).send({ status: 'expired' });
|
|
return;
|
|
}
|
|
let cidade = '';
|
|
let jogador = req.body.jogador;
|
|
let pontuacao = req.body.pontuacao;
|
|
let idgames = 1;
|
|
execSQLQuery(
|
|
`INSERT INTO scoregames (cidade, jogador, pontuacao, idgames) VALUES ('${cidade}', '${jogador}', ${pontuacao}, ${idgames});`,
|
|
res,
|
|
req.originalUrl
|
|
);
|
|
});
|
|
|
|
router.post('/updateScoregame/:tk', (req, res) => {
|
|
let token = req.params.tk;
|
|
if (token != Variables('tk_fixed')) {
|
|
res.status(500).send({ status: 'expired' });
|
|
return;
|
|
}
|
|
let cidade = '';
|
|
let jogador = req.body.jogador;
|
|
let pontuacao = req.body.pontuacao;
|
|
let idgames = 1;
|
|
execSQLQuery(
|
|
`UPDATE scoregames SET pontuacao = ${pontuacao} WHERE jogador = '${jogador}' AND idgames = ${idgames};`,
|
|
res,
|
|
req.originalUrl
|
|
);
|
|
});
|
|
|
|
router.get('/listScoregame/:tk/:idgames', (req, res) => {
|
|
let token = req.params.tk;
|
|
let idgames = req.params.idgames;
|
|
if (token != Variables('tk_fixed')) {
|
|
res.status(500).send({ status: 'expired' });
|
|
return;
|
|
}
|
|
execSQLQuery(
|
|
`SELECT jogador, pontuacao FROM scoregames WHERE idgames = ${idgames} ORDER BY pontuacao ASC;`,
|
|
res,
|
|
req.originalUrl
|
|
);
|
|
});
|
|
|
|
router.get('/InsertCourseNotes', (req, res) => {
|
|
if (InsertCourseNotes())
|
|
res.send({ error: null, response: false, status: 500 });
|
|
else res.send({ error: null, response: true, status: 200 });
|
|
});
|
|
|
|
router.post('/DesativarAluno', (req, res) => {
|
|
const idunits = parseInt(req.body.idunits);
|
|
const idusers = parseInt(req.body.idusers);
|
|
execSQLQuery(
|
|
`insert into alunosdesativados (idunits, idusers) values (${idunits}, ${idusers});`,
|
|
res,
|
|
req.originalUrl
|
|
);
|
|
});
|
|
|
|
router.get('/MudarAlunoDesativado/:idunits/:idusers', (req, res) => {
|
|
const idunits = parseInt(req.params.idunits);
|
|
const idusers = parseInt(req.params.idusers);
|
|
execSQLQuery(
|
|
`update alunosdesativados set
|
|
desativado = !desativado
|
|
where idunits = ${idunits} and idusers = ${idusers};`,
|
|
res,
|
|
req.originalUrl
|
|
);
|
|
});
|
|
|
|
router.get('/MudarAlunoDesativadoP/:idunits/:idusers', (req, res) => {
|
|
const idunits = parseInt(req.params.idunits);
|
|
const idusers = parseInt(req.params.idusers);
|
|
execSQLQuery(
|
|
`update usershomework set
|
|
disabled = !disabled
|
|
where idunits = ${idunits} and idusers = ${idusers};`,
|
|
res,
|
|
req.originalUrl
|
|
);
|
|
});
|
|
|
|
router.get('/InserirNotasSQLServer', (req, res) => {
|
|
console.log(
|
|
new Date().toUTCString() + ' - ' + req.originalUrl + ' - initing...'
|
|
);
|
|
let SqlQueryUnidades = `select
|
|
idunits,
|
|
unityname as 'Unidade',
|
|
concat(reference_db, '.dbo.') as 'db'
|
|
from units
|
|
where enabled = 1
|
|
order by unityname asc;`;
|
|
mysqlConnection.query(SqlQueryUnidades, function (error, Unidades, fields) {
|
|
PreencherNotasSQLServer(Unidades, 0, function (Unidades) {
|
|
console.log(
|
|
new Date().toUTCString() + ' - ' + req.originalUrl + ' - finished'
|
|
);
|
|
res.send(Unidades);
|
|
});
|
|
});
|
|
});
|
|
|
|
router.get('/InserirRespostasAlunos', (req, res) => {
|
|
console.log(
|
|
new Date().toUTCString() + ' - ' + req.originalUrl + ' - initing...'
|
|
);
|
|
let SqlQueryUnidades = `select
|
|
idunits,
|
|
unityname as 'Unidade',
|
|
concat(reference_db, '.dbo.') as 'db'
|
|
from units
|
|
where enabled = 1
|
|
order by unityname asc;`;
|
|
mysqlConnection.query(SqlQueryUnidades, function (error, Unidades, fields) {
|
|
let SqlQueryRespostas = `select distinct
|
|
cc.idclasses,
|
|
ea.idexerciseanswers
|
|
from classescontent cc
|
|
inner join exercisequestions eq on eq.idexercises = cc.idexercises
|
|
inner join exerciseanswers ea on ea.idexercisequestions = eq.idexercisequestions
|
|
where ea.iscorrect = 1;`;
|
|
mysqlConnection.query(
|
|
SqlQueryRespostas,
|
|
function (error, Respostas, fields) {
|
|
PreencherAulas(Unidades, Respostas, 0, function (Unidades) {
|
|
console.log(
|
|
new Date().toUTCString() + ' - ' + req.originalUrl + ' - finished'
|
|
);
|
|
res.send(Unidades);
|
|
});
|
|
}
|
|
);
|
|
});
|
|
});
|
|
|
|
function PreencherAulas(Unidades, Respostas, Index, callback) {
|
|
if (Index < Unidades.length) {
|
|
console.log(
|
|
'Analisando unidade ' +
|
|
(Index + 1) +
|
|
' de ' +
|
|
Unidades.length +
|
|
' (' +
|
|
Unidades[Index]['Unidade'] +
|
|
')'
|
|
);
|
|
Unidades[Index]['AulaAluno'] = [];
|
|
let SqlQueryMySQL = `select * from (
|
|
select
|
|
sc.idusers,
|
|
sc.idunits,
|
|
sc.idclasses,
|
|
sc.note,
|
|
ExA.Questoes,
|
|
count(ug.idusergivenanswers) as 'Respostas'
|
|
from stepclass sc
|
|
inner join (select distinct
|
|
cl.idclasses,
|
|
cl.name as 'Aula',
|
|
count(cc.idexercises) as 'Questoes'
|
|
from classes cl
|
|
inner join courses co on co.idcourses = cl.idcourses
|
|
inner join classescontent cc on cc.idclasses = cl.idclasses
|
|
left join exercisequestions eq on eq.idexercises = cc.idexercises
|
|
where co.idtypescourse in (1,2,3)
|
|
group by cl.idclasses, cc.idclasses
|
|
order by cl.idclasses asc) ExA on sc.idclasses = ExA.idclasses and ExA.Questoes > 0
|
|
left join usergivenanswers ug on ug.idunits = sc.idunits and ug.idusers = sc.idusers and ug.idclasses = sc.idclasses
|
|
where sc.idunits = ${Unidades[Index]['idunits']} and sc.completedClass = 1 and (now() >= date_add(convert(sc.dateinicial, date), interval 7 day) or sc.homework is not null)
|
|
group by sc.idusers, sc.idunits, sc.idclasses) R
|
|
where R.Respostas = 0;`;
|
|
mysqlConnection.query(SqlQueryMySQL, function (error, AulaAluno, fields) {
|
|
Unidades[Index]['AulaAluno'] = AulaAluno;
|
|
if (AulaAluno && AulaAluno.length > 0) {
|
|
Unidades[Index][
|
|
'SQLRes'
|
|
] = `insert into usergivenanswers (idusergivenanswers, idusers, idexerciseanswers, date_answered, fixed, idclasses, idunits) values `;
|
|
Unidades[Index]['SQLNot'] = `update stepclass set note = null where `;
|
|
} else {
|
|
Unidades[Index]['SQLRes'] = 'select 1;';
|
|
Unidades[Index]['SQLNot'] = 'select 1 ;';
|
|
}
|
|
for (let i = 0; i < Unidades[Index]['AulaAluno'].length; i++) {
|
|
Unidades[Index][
|
|
'SQLNot'
|
|
] += `(idusers = ${Unidades[Index]['AulaAluno'][i]['idusers']} and idunits = ${Unidades[Index]['AulaAluno'][i]['idunits']} and idclasses = ${Unidades[Index]['AulaAluno'][i]['idclasses']}) or `;
|
|
for (let o = 0; o < Respostas.length; o++) {
|
|
if (
|
|
Respostas[o].idclasses ==
|
|
Unidades[Index]['AulaAluno'][i]['idclasses']
|
|
) {
|
|
Unidades[Index][
|
|
'SQLRes'
|
|
] += `((select ug1.idusergivenanswers + 1 from usergivenanswers ug1 order by ug1.idusergivenanswers desc limit 1), ${Unidades[Index]['AulaAluno'][i]['idusers']}, ${Respostas[o]['idexerciseanswers']}, '25/01/2021', 1, ${Unidades[Index]['AulaAluno'][i]['idclasses']}, ${Unidades[Index]['AulaAluno'][i]['idunits']}),`;
|
|
}
|
|
}
|
|
}
|
|
Unidades[Index]['SQLRes'] =
|
|
Unidades[Index]['SQLRes'].substring(
|
|
0,
|
|
Unidades[Index]['SQLRes'].length - 1
|
|
) + ';';
|
|
Unidades[Index]['SQLNot'] =
|
|
Unidades[Index]['SQLNot'].substring(
|
|
0,
|
|
Unidades[Index]['SQLNot'].length - 4
|
|
) + ';';
|
|
|
|
mysqlConnection.query(
|
|
Unidades[Index]['SQLRes'],
|
|
function (error, insRespostas, fields) {
|
|
mysqlConnection.query(
|
|
Unidades[Index]['SQLNot'],
|
|
function (error, updNotas, fields) {
|
|
Index++;
|
|
PreencherAulas(Unidades, Respostas, Index, callback);
|
|
}
|
|
);
|
|
}
|
|
);
|
|
});
|
|
} else {
|
|
callback(Unidades);
|
|
}
|
|
}
|
|
|
|
router.get('/RemoverRespostasAlunos', (req, res) => {
|
|
console.log(
|
|
new Date().toUTCString() + ' - ' + req.originalUrl + ' - initing...'
|
|
);
|
|
let SqlQueryUnidades = `select
|
|
idunits,
|
|
unityname as 'Unidade',
|
|
concat(reference_db, '.dbo.') as 'db'
|
|
from units
|
|
where enabled = 1
|
|
order by unityname asc;`;
|
|
mysqlConnection.query(SqlQueryUnidades, function (error, Unidades, fields) {
|
|
RemoverAulas(Unidades, 0, function (Unidades) {
|
|
console.log(
|
|
new Date().toUTCString() + ' - ' + req.originalUrl + ' - finished'
|
|
);
|
|
res.send(Unidades);
|
|
});
|
|
});
|
|
});
|
|
|
|
function RemoverAulas(Unidades, Index, callback) {
|
|
if (Index < Unidades.length) {
|
|
console.log(
|
|
'Analisando unidade ' +
|
|
(Index + 1) +
|
|
' de ' +
|
|
Unidades.length +
|
|
' (' +
|
|
Unidades[Index]['Unidade'] +
|
|
')'
|
|
);
|
|
Unidades[Index]['AulaAluno'] = [];
|
|
let SqlQueryMySQL = `select * from (
|
|
select
|
|
sc.idusers,
|
|
sc.idunits,
|
|
sc.idclasses,
|
|
sc.note,
|
|
ExA.Questoes,
|
|
count(ug.idusergivenanswers) as 'Respostas'
|
|
from stepclass sc
|
|
inner join (select distinct
|
|
cl.idclasses,
|
|
cl.name as 'Aula',
|
|
count(cc.idexercises) as 'Questoes'
|
|
from classes cl
|
|
inner join courses co on co.idcourses = cl.idcourses
|
|
inner join classescontent cc on cc.idclasses = cl.idclasses
|
|
left join exercisequestions eq on eq.idexercises = cc.idexercises
|
|
where co.idtypescourse in (1,2,3)
|
|
group by cl.idclasses, cc.idclasses
|
|
order by cl.idclasses asc) ExA on sc.idclasses = ExA.idclasses and ExA.Questoes > 0
|
|
left join usergivenanswers ug on ug.idunits = sc.idunits and ug.idusers = sc.idusers and ug.idclasses = sc.idclasses
|
|
where sc.idunits = ${Unidades[Index]['idunits']} and sc.completedClass = 1 and (now() >= date_add(convert(sc.dateinicial, date), interval 7 day) or sc.homework is not null)
|
|
group by sc.idusers, sc.idunits, sc.idclasses) R
|
|
where R.Respostas != R.Questoes;`;
|
|
mysqlConnection.query(SqlQueryMySQL, function (error, AulaAluno, fields) {
|
|
Unidades[Index]['AulaAluno'] = AulaAluno;
|
|
if (AulaAluno && AulaAluno.length > 0) {
|
|
Unidades[Index]['SQLRem'] = `delete from usergivenanswers where `;
|
|
} else {
|
|
Unidades[Index]['SQLRem'] = 'select 1;';
|
|
}
|
|
LoopRemoverRespostas(Unidades[Index], 0, '', function (newUnidade) {
|
|
Unidades[Index] = newUnidade;
|
|
if (Unidades[Index]['IDsRemover'] == '') {
|
|
Unidades[Index]['IDsRemover'] = 'select 1;';
|
|
} else {
|
|
Unidades[Index]['SQLRem'] +=
|
|
'(' + Unidades[Index]['IDsRemover'] + ')';
|
|
}
|
|
mysqlConnection.query(
|
|
Unidades[Index]['SQLRem'],
|
|
function (error, delRespostas, fields) {
|
|
Index++;
|
|
RemoverAulas(Unidades, Index, callback);
|
|
}
|
|
);
|
|
});
|
|
});
|
|
} else {
|
|
callback(Unidades);
|
|
}
|
|
}
|
|
|
|
function LoopRemoverRespostas(Unidade, Index, IDsRemover, callback) {
|
|
if (Index < Unidade['AulaAluno'].length) {
|
|
let sqlQueryRespostas = `select * from usergivenanswers ug where ug.idunits = ${Unidade['AulaAluno'][Index]['idunits']} and ug.idclasses = ${Unidade['AulaAluno'][Index]['idclasses']} and ug.idusers = ${Unidade['AulaAluno'][Index]['idusers']};`;
|
|
mysqlConnection.query(
|
|
sqlQueryRespostas,
|
|
function (error, ResAluno, fields) {
|
|
for (let i = 0; i < ResAluno.length; i++) {
|
|
if (IDsRemover != '') IDsRemover += ' or ';
|
|
IDsRemover += `(idunits = ${ResAluno[i]['idunits']} and idusers = ${ResAluno[i]['idusers']} and idclasses = ${ResAluno[i]['idclasses']})`;
|
|
}
|
|
Index++;
|
|
LoopRemoverRespostas(Unidade, Index, IDsRemover, callback);
|
|
}
|
|
);
|
|
} else {
|
|
Unidade['IDsRemover'] = IDsRemover;
|
|
callback(Unidade);
|
|
}
|
|
}
|
|
|
|
router.get('/RelatorioMatriculasSenhas/:db', (req, res) => {
|
|
const db = req.params.db;
|
|
console.log(
|
|
new Date().toUTCString() + ' - ' + req.originalUrl + ' - initing...'
|
|
);
|
|
const SqlQuery = `select distinct
|
|
tt.IDMatricula,
|
|
cl.Nome,
|
|
cl.SenhaIntranet as 'Senha'
|
|
from ${db}Clientes cl
|
|
inner join ${db}TreinandosTurmas tt on tt.IDTreinando = cl.IDCliente
|
|
order by tt.IDMatricula asc;`;
|
|
sqlServerConnection
|
|
.request()
|
|
.query(SqlQuery, function (error, results, fields) {
|
|
let Relatorio = [];
|
|
for (let i = 0; i < results['recordset'].length; i++) {
|
|
Relatorio.push(results['recordset'][i]);
|
|
Relatorio[i]['Senha'] = encriptPassword(
|
|
Relatorio[i]['Senha'],
|
|
Relatorio[i]['Senha'].length
|
|
);
|
|
}
|
|
res.send(Relatorio);
|
|
});
|
|
});
|
|
|
|
return router;
|
|
};
|
|
|
|
let indexStudents = 0;
|
|
function LoopStudentsOfTeacher(Alunos, res, db, idteacher) {
|
|
if (indexStudents < Alunos.length) {
|
|
let sqlQuery = `
|
|
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 = ${
|
|
Alunos[indexStudents]['IDMatricula']
|
|
} AND sc.idunits = (SELECT idunits FROM units WHERE reference_db = '${
|
|
db.split('.')[0]
|
|
}') AND co.idcourses = ${Alunos[indexStudents]['CodigoCurso']};`;
|
|
mysqlConnection.query(sqlQuery, function (error, results, fields) {
|
|
if (!error && results.length != 0) {
|
|
let sqlQuery2 = `SELECT COUNT(*) AS Ministradas FROM ${db}TurmasCalendario WHERE CodigoCurso = ${Alunos[indexStudents]['CodigoCurso']} AND IdTurma = ${Alunos[indexStudents]['IdTurma']} AND Ministrado = 1;`;
|
|
|
|
sqlServerConnection
|
|
.request()
|
|
.query(sqlQuery2, function (error2, results2, fields) {
|
|
if (!error && results2.length > 0) {
|
|
if (results2[0]['Ministradas'] > results[0]['qtde_stepclass']) {
|
|
res.send(true);
|
|
} else {
|
|
indexStudents++;
|
|
LoopStudentsOfTeacher(Alunos, res, db, idteacher);
|
|
}
|
|
} else {
|
|
indexStudents++;
|
|
LoopStudentsOfTeacher(Alunos, res, db, idteacher);
|
|
}
|
|
});
|
|
} else {
|
|
indexStudents++;
|
|
LoopStudentsOfTeacher(Alunos, res, db, idteacher);
|
|
}
|
|
});
|
|
} else {
|
|
res.send(false);
|
|
}
|
|
}
|
|
|
|
function CheckDateExists(
|
|
db,
|
|
date,
|
|
idteacher,
|
|
StudentsIDs,
|
|
i,
|
|
Nomes,
|
|
res,
|
|
content,
|
|
finalIDchamada
|
|
) {
|
|
try {
|
|
if (i < StudentsIDs.length) {
|
|
if (parseInt(StudentsIDs[i]) > 190000) {
|
|
StudentsIDs[
|
|
i
|
|
] = `(SELECT DISTINCT top(1) CodigoTurma FROM ${db}TreinandosTurmas WHERE IDMatricula = ${StudentsIDs[i]})`;
|
|
}
|
|
let sqlQry1 = `SELECT top(1) *, ${StudentsIDs[i]} AS 'CodTurma'
|
|
FROM ${db}TurmasCalendario tc
|
|
INNER JOIN ${db}TreinandosTurmas tt
|
|
ON tt.CodigoTurma = tc.IdTurma
|
|
where tc.IDTurma = ${StudentsIDs[i]}
|
|
and Ministrado = 0
|
|
order by Data asc;`;
|
|
console.log(sqlQry1);
|
|
sqlServerConnection
|
|
.request()
|
|
.query(sqlQry1, function (error1, results1, fields) {
|
|
if (!error1 && results1.length != 0) {
|
|
//console.log(results1);
|
|
var ResultTurmasCalendario = results1.recordsets[0][0];
|
|
if (ResultTurmasCalendario) {
|
|
StudentsIDs[i] = ResultTurmasCalendario['CodTurma'];
|
|
//console.log(StudentsIDs[i]);
|
|
let sqlQry2 = `
|
|
SELECT
|
|
*
|
|
FROM
|
|
${db}TreinandosTurmas
|
|
WHERE
|
|
CodigoTurma = ${ResultTurmasCalendario['IdTurma']}
|
|
AND
|
|
IDCurso = ${ResultTurmasCalendario['CodigoCurso']}
|
|
AND
|
|
Desistente = 0
|
|
AND
|
|
Faltoso = 0;
|
|
`;
|
|
|
|
sqlServerConnection
|
|
.request()
|
|
.query(sqlQry2, function (error2, results2, fields) {
|
|
if (!error2 && results2.length != 0) {
|
|
//console.log(results2.recordset[0]);
|
|
var ResultTreinandosTurmas = results2.recordset[0];
|
|
if (
|
|
ResultTreinandosTurmas['CodigoTurma'] == StudentsIDs[i]
|
|
) {
|
|
let sqlQry3 = `SELECT TOP 1 IDChamadas FROM ${db}Chamadas ORDER BY IDChamadas DESC;`;
|
|
sqlServerConnection
|
|
.request()
|
|
.query(sqlQry3, function (error3, results3, fields) {
|
|
console.log(results3);
|
|
let idchamada = results3.recordsets[0].length
|
|
? results3.recordsets[0][0]['IDChamadas'] + 1
|
|
: 1;
|
|
let idturma = ResultTurmasCalendario['IdTurma'];
|
|
let idcurso = ResultTurmasCalendario['CodigoCurso'];
|
|
let data2 = formatDate(
|
|
ResultTurmasCalendario['Data'],
|
|
0
|
|
);
|
|
let hora = formatDate(
|
|
ResultTurmasCalendario['HoraInicial'],
|
|
1
|
|
);
|
|
let horainicio = formatDate(
|
|
ResultTurmasCalendario['HoraInicial'],
|
|
1
|
|
);
|
|
let horatermino = formatDate(
|
|
ResultTurmasCalendario['HoraFinal'],
|
|
1
|
|
);
|
|
let idinstrutor =
|
|
ResultTurmasCalendario['CodigoInstrutor'];
|
|
let dataenvioapp = formatDate(new Date(), 0);
|
|
let dataatualizacaoapp = formatDate(new Date(), 0);
|
|
let idfuncionarioapp =
|
|
ResultTurmasCalendario['CodigoInstrutor'];
|
|
let sqlQry4 = `INSERT INTO ${db}Chamadas
|
|
(IDChamadas, IDTurma, IDCurso, Data, Hora, HoraInicio, HoraTermino, IDInstrutor, Flex, APP, DataEnvioAPP, DataAtualizacaoAPP, IDFuncionarioAPP)
|
|
VALUES
|
|
(${idchamada},${idturma},${idcurso},'${date}','${hora}','${horainicio}','${horatermino}',${idinstrutor},1,1,'${dataenvioapp}','${dataatualizacaoapp}',${idfuncionarioapp});`;
|
|
|
|
sqlServerConnection
|
|
.request()
|
|
.query(
|
|
sqlQry4,
|
|
function (error6, results6, fields) {
|
|
if (!error6) {
|
|
let sqlQry6 = `DELETE FROM ${db}ChamadasConteudos WHERE IDChamadas = ${idchamada};`;
|
|
|
|
sqlServerConnection
|
|
.request()
|
|
.query(
|
|
sqlQry6,
|
|
function (error7, results7, fields) {
|
|
if (!error7) {
|
|
let sqlQry7 = `INSERT INTO ${db}ChamadasConteudos (IDChamadas, CodigoConteudo, DescricaoConteudo) VALUES (${idchamada}, 0, '${content}');`;
|
|
//console.log(sqlQry7);
|
|
sqlServerConnection
|
|
.request()
|
|
.query(
|
|
sqlQry7,
|
|
function (
|
|
error8,
|
|
results8,
|
|
fields
|
|
) {
|
|
if (!error8) {
|
|
let sqlQry8 = `UPDATE ${db}TurmasCalendario SET Ministrado = 1, Data = '${date}' WHERE IdTurmasCalendario = ${ResultTurmasCalendario['IdTurmasCalendario']};`;
|
|
//console.log(sqlQry8);
|
|
sqlServerConnection
|
|
.request()
|
|
.query(
|
|
sqlQry8,
|
|
function (
|
|
error9,
|
|
results9,
|
|
fields
|
|
) {
|
|
if (!error9) {
|
|
let sqlQry9 = `DELETE FROM ${db}ChamadasDetalhes WHERE IDChamadas = ${idchamada};`;
|
|
//console.log(sqlQry9);
|
|
sqlServerConnection
|
|
.request()
|
|
.query(
|
|
sqlQry9,
|
|
function (
|
|
error10,
|
|
results10,
|
|
fields
|
|
) {
|
|
if (!error10) {
|
|
let sqlQry10 = `INSERT INTO ${db}ChamadasDetalhes
|
|
(IDChamadas, IDTreinando, Presente, Avisada, Justificativa, UltimaCarta) VALUES
|
|
(${idchamada}, ${ResultTreinandosTurmas['IDTreinando']}, 1, 0, null, null);`;
|
|
//console.log(sqlQry10);
|
|
sqlServerConnection
|
|
.request()
|
|
.query(
|
|
sqlQry10,
|
|
function (
|
|
error11,
|
|
results11,
|
|
fields
|
|
) {
|
|
if (
|
|
!error11
|
|
) {
|
|
i++;
|
|
CheckDateExists(
|
|
db,
|
|
date,
|
|
idteacher,
|
|
StudentsIDs,
|
|
i,
|
|
Nomes,
|
|
res,
|
|
content,
|
|
idchamada
|
|
);
|
|
}
|
|
}
|
|
);
|
|
}
|
|
}
|
|
);
|
|
}
|
|
}
|
|
);
|
|
}
|
|
}
|
|
);
|
|
}
|
|
}
|
|
);
|
|
} else {
|
|
console.log('erro sqlQry4: ' + sqlQry4);
|
|
console.log(error6);
|
|
}
|
|
}
|
|
);
|
|
});
|
|
} else {
|
|
console.log('Não existe');
|
|
|
|
var sqlQry5 = `SELECT * FROM ${db}Clientes WHERE IDCliente = ${StudentsIDs[i]};`;
|
|
sqlServerConnection
|
|
.request()
|
|
.query(sqlQry5, function (error5, results5, fields) {
|
|
if (!error5 && results5.length != 0) {
|
|
if (Nomes == '') {
|
|
console.log(results5.recordsets);
|
|
Nomes = results5.recordset[0]['Nome'];
|
|
} else {
|
|
Nomes += ', ' + results5.recordset[0]['Nome'];
|
|
}
|
|
}
|
|
i++;
|
|
CheckDateExists(
|
|
db,
|
|
date,
|
|
idteacher,
|
|
StudentsIDs,
|
|
i,
|
|
Nomes,
|
|
res,
|
|
content,
|
|
null
|
|
);
|
|
});
|
|
}
|
|
} else {
|
|
console.log('erro sqlQry2');
|
|
i++;
|
|
CheckDateExists(
|
|
db,
|
|
date,
|
|
idteacher,
|
|
StudentsIDs,
|
|
i,
|
|
Nomes,
|
|
res,
|
|
content,
|
|
null
|
|
);
|
|
}
|
|
});
|
|
} else {
|
|
console.log('erro: turmas calendário retornou null');
|
|
i++;
|
|
CheckDateExists(
|
|
db,
|
|
date,
|
|
idteacher,
|
|
StudentsIDs,
|
|
i,
|
|
Nomes,
|
|
res,
|
|
content,
|
|
null
|
|
);
|
|
}
|
|
} else {
|
|
console.log('erro sqlQry1: ' + error1);
|
|
i++;
|
|
CheckDateExists(
|
|
db,
|
|
date,
|
|
idteacher,
|
|
StudentsIDs,
|
|
i,
|
|
Nomes,
|
|
res,
|
|
content,
|
|
null
|
|
);
|
|
}
|
|
});
|
|
} else {
|
|
//console.log("finalIDchamada: " + finalIDchamada);
|
|
res.send(
|
|
JSON.stringify({
|
|
idcallresponse: finalIDchamada,
|
|
})
|
|
);
|
|
}
|
|
} catch (error) {
|
|
console.error(error);
|
|
res.send(
|
|
JSON.stringify({
|
|
error: error,
|
|
})
|
|
);
|
|
}
|
|
}
|
|
|
|
function formatDate(date, ret) {
|
|
var novaData = date
|
|
.toISOString()
|
|
.replace(/T/, ' ')
|
|
.replace(/\..+/, '')
|
|
.split(' ');
|
|
return novaData[ret];
|
|
}
|
|
|
|
function InsertNotes() {
|
|
console.log(new Date().toUTCString() + ' - /InsertNotes');
|
|
const SQLQryNotasAulas = `update stepclass A
|
|
join
|
|
(select * from
|
|
(select distinct
|
|
*,
|
|
((AC2.TotalAcertos / AC2.ItensAula) * 10) as 'Nota'
|
|
from (
|
|
select
|
|
*,
|
|
(AC.Acertos + AC.Erros + AC.Atividades) as 'ItensAula',
|
|
(AC.Acertos + AC.NotaAtividades) as 'TotalAcertos'
|
|
from
|
|
(select
|
|
sc.idusers,
|
|
sc.idclasses,
|
|
sc.idunits,
|
|
sc.completedClass,
|
|
sc.datefinal,
|
|
coalesce((ea.iscorrect), 0) as 'Acertos',
|
|
coalesce(sum(case when ea.iscorrect = 1 then 0 else 1 end), 0) as 'Erros',
|
|
(SELECT COUNT(*) FROM userstages WHERE idusers = sc.idusers AND idclasses = sc.idclasses AND idunits = sc.idunits) AS 'Atividades',
|
|
(SELECT coalesce(SUM(note), 0) FROM userstages WHERE idusers = sc.idusers AND idclasses = sc.idclasses AND idunits = sc.idunits) AS 'NotaAtividades',
|
|
sc.note
|
|
from (select * from stepclass sc where sc.completedClass = 1 and sc.idusers > 190008 and sc.note is null and (CASE WHEN STR_TO_DATE(sc.datefinal, '%d/%m/%Y') < NOW() OR sc.homework = 1 THEN 1 ELSE 0 END) = 1) sc
|
|
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 userstages us on us.idclasses = sc.idclasses and us.idusers = sc.idusers and us.idunits = sc.idunits
|
|
group by sc.idusers, sc.idclasses, sc.idunits, sc.completedClass, sc.note, sc.datefinal, ea.iscorrect) AC) AC2) AC3) B
|
|
on A.idusers = B.idusers and A.idunits = B.idunits and A.idclasses = B.idclasses
|
|
set A.note = B.Nota;`;
|
|
const SQLNewInsertNotes = `update stepclass A
|
|
join (select
|
|
R.idusers,
|
|
R.idunits,
|
|
R.idclasses,
|
|
R.Questoes,
|
|
R.EAcertos as 'ExerciciosAcertos',
|
|
R.TAcertos as 'TarefasAcertos',
|
|
case
|
|
when (R.Questoes + R.TAcertos + R.TErros) = 0 then 10
|
|
when ((R.EAcertos + R.TAcertos) / (R.Questoes + R.TAcertos + R.TErros) * 10) > 10 then 10
|
|
else ((R.EAcertos + R.TAcertos) / (R.Questoes + R.TAcertos + R.TErros) * 10)
|
|
end as 'Nota'
|
|
from (select distinct
|
|
sc.idstepclass,
|
|
sc.idusers,
|
|
sc.idunits,
|
|
sc.idclasses,
|
|
ExA.Questoes,
|
|
(select count(*) from usergivenanswers ug inner join exerciseanswers ea on ea.idexerciseanswers = ug.idexerciseanswers and ea.iscorrect = 1 where ug.idclasses = sc.idclasses and ug.idunits = sc.idunits and ug.idusers = sc.idusers) as 'EAcertos',
|
|
(select count(*) from usergivenanswers ug inner join exerciseanswers ea on ea.idexerciseanswers = ug.idexerciseanswers and ea.iscorrect = 0 where ug.idclasses = sc.idclasses and ug.idunits = sc.idunits and ug.idusers = sc.idusers) as 'EErros',
|
|
(select count(*) from userstages us where us.idclasses = sc.idclasses and us.idunits = sc.idunits and us.idusers = sc.idusers and us.note = 1) as 'TAcertos',
|
|
(select count(*) from userstages us where us.idclasses = sc.idclasses and us.idunits = sc.idunits and us.idusers = sc.idusers and us.note = 0) as 'TErros'
|
|
from stepclass sc
|
|
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 ExA.idclasses = sc.idclasses
|
|
where sc.note is null and sc.completedClass = 1 and (now() >= date_add(convert(sc.dateinicial, date), interval 7 day) or sc.homework is not null)) R) B
|
|
on A.idusers = B.idusers and A.idunits = B.idunits and A.idclasses = B.idclasses
|
|
set A.note = B.Nota;`;
|
|
|
|
mysqlConnection.query(SQLNewInsertNotes, function (error, results, fields) {
|
|
console.log(new Date().toUTCString() + ' - Notas de aulas lançadas');
|
|
InsertCourseNotes();
|
|
return true;
|
|
});
|
|
}
|
|
|
|
function InsertNotasDetalhes(idusers, idunits, idclasses) {
|
|
console.log(new Date().toUTCString() + ' - /InsertNotasDetalhes');
|
|
let sqlQry0 = `
|
|
SELECT
|
|
*
|
|
FROM
|
|
(SELECT note AS Nota from stepclass where idusers = ${idusers} and idunits = ${idunits} and idclasses = ${idclasses}) AS note,
|
|
(SELECT reference_db AS Banco from units where idunits = ${idunits}) AS banco,
|
|
(SELECT idcourses AS IDCurso from classes where idclasses = ${idclasses}) AS curso,
|
|
(SELECT ordem + 1 AS Conceito from classes where idclasses = ${idclasses}) AS conceito;`;
|
|
|
|
mysqlConnection.query(sqlQry0, function (error, results, fields) {
|
|
if (!error && results.length != 0) {
|
|
let nota = results[0]['Nota'];
|
|
let db = results[0]['Banco'] + '.dbo.';
|
|
let idcourses = results[0]['IDCurso'];
|
|
let concept = results[0]['Conceito'];
|
|
|
|
let sqlServerQuery = `
|
|
INSERT INTO
|
|
${db}NotasDetalhes(IDTurma, IDCurso, IDMatricula, IDTreinando, IDConceito, Nota)
|
|
SELECT
|
|
CodigoTurma, IDCurso, IDMatricula, IDTreinando, ${concept} as IDConceito, ${nota} as Nota FROM ${db}TreinandosTurmas WHERE IDMatricula = ${idusers} AND IDCurso = ${idcourses};
|
|
|
|
INSERT INTO
|
|
${db}NotasDetalhesSequencial(IDTurma, IDCurso, IDMatricula, IDTreinando, IDConceito)
|
|
SELECT
|
|
CodigoTurma, IDCurso, IDMatricula, IDTreinando, ${concept} as IDConceito FROM ${db}TreinandosTurmas WHERE IDMatricula = ${idusers} AND IDCurso = ${idcourses};`;
|
|
//console.log(sqlServerQuery);
|
|
sqlServerConnection
|
|
.request()
|
|
.query(sqlServerQuery, function (error1, results1, fields) {
|
|
return true;
|
|
});
|
|
}
|
|
});
|
|
}
|
|
|
|
function InsertCourseNotes() {
|
|
console.log(new Date().toUTCString() + ' - /InsertCourseNotes');
|
|
const SQLQry = `select
|
|
Tab2.idcourses,
|
|
Tab2.idusers,
|
|
(sum(Tab2.note) / Tab2.AulasCurso) as 'note',
|
|
Tab2.idunits
|
|
from (
|
|
select
|
|
Tab1.*,
|
|
sc.idstepclass,
|
|
sc.note
|
|
from (
|
|
select
|
|
sc.idusers,
|
|
co.idcourses,
|
|
sc.idunits,
|
|
count(sc.idclasses) as 'Aulas',
|
|
(select count(*) from classes cl1 where cl1.idcourses = co.idcourses and cl1.disabled = 0) as 'AulasCurso'
|
|
from stepclass sc
|
|
inner join classes cl on cl.idclasses = sc.idclasses
|
|
inner join courses co on co.idcourses = cl.idcourses
|
|
where sc.note is not null
|
|
group by sc.idusers, co.idcourses, sc.idunits) Tab1
|
|
inner join stepclass sc on sc.idunits = Tab1.idunits and sc.idusers = Tab1.idusers
|
|
inner join classes cl on cl.idclasses = sc.idclasses and cl.idcourses = Tab1.idcourses
|
|
where Tab1.Aulas = Tab1.AulasCurso
|
|
order by idunits, idcourses, idusers) Tab2
|
|
group by Tab2.idcourses, Tab2.idusers, Tab2.idunits;`;
|
|
|
|
mysqlConnection.query(SQLQry, function (error, results, fields) {
|
|
LoopCursoNotas(0, results, function (Sucesso) {
|
|
console.log(new Date().toUTCString() + ' - Notas de cursos lançadas');
|
|
return Sucesso;
|
|
});
|
|
});
|
|
}
|
|
|
|
function LoopCursoNotas(Index, Registros, callback) {
|
|
if (!Registros) {
|
|
callback(false);
|
|
return;
|
|
}
|
|
if (Index < Registros.length) {
|
|
let SQLInsert = `INSERT INTO coursenotes (idcourses, idusers, note, idunits) VALUES (${Registros[Index]['idcourses']}, ${Registros[Index]['idusers']}, ${Registros[Index]['note']}, ${Registros[Index]['idunits']});`;
|
|
//console.log(SQLInsert);
|
|
mysqlConnection.query(SQLInsert, function (error, results, fields) {
|
|
if (!error) {
|
|
console.log(
|
|
'Notas do curso lançadas para o aluno ' +
|
|
Registros[Index]['idusers'] +
|
|
' da unidade ' +
|
|
Registros[Index]['idcourses']
|
|
);
|
|
}
|
|
Index++;
|
|
LoopCursoNotas(Index, Registros, callback);
|
|
});
|
|
} else {
|
|
callback(true);
|
|
}
|
|
}
|
|
|
|
let IndexNotaDetalhe = 0;
|
|
function LoopNotasDetalhes(idusers, idunits, idcourses, conceito) {
|
|
if (IndexNotaDetalhe <= conceito) {
|
|
let sqlQry = `SELECT idclasses FROM classes WHERE ordem = ${IndexNotaDetalhe} AND idcourses = ${idcourses};`;
|
|
mysqlConnection.query(sqlQry, function (error, results, fields) {
|
|
let idclasses;
|
|
try {
|
|
idclasses = results[0]['idclasses'];
|
|
} catch (e) {
|
|
return true;
|
|
}
|
|
|
|
InsertNotasDetalhes(idusers, idunits, idclasses);
|
|
setTimeout(() => {
|
|
IndexNotaDetalhe++;
|
|
LoopNotasDetalhes(idusers, idunits, idcourses, IndexNotaDetalhe);
|
|
}, 200);
|
|
});
|
|
} else {
|
|
return true;
|
|
}
|
|
}
|
|
|
|
function InsertTraineeProgramNotes(idusers, idunits, idclasses) {
|
|
console.log('/InsertTraineeProgramNotes');
|
|
const SQLQry = `
|
|
INSERT INTO
|
|
traineeprogramnotes (idtraineeprogram, idusers, note, idunits)
|
|
SELECT
|
|
(SELECT DISTINCT
|
|
idtraineeprogram
|
|
FROM
|
|
traineeprogramcourses
|
|
WHERE
|
|
idcourses = (SELECT idcourses FROM classes WHERE idclasses = ${idclasses})) idtraineeprogram,
|
|
${idusers} AS idusers,
|
|
(SELECT
|
|
SUM(note) / COUNT(*) AS Nota
|
|
FROM
|
|
coursenotes
|
|
WHERE
|
|
idusers = ${idusers} AND idunits = ${idunits}) AS note,
|
|
${idunits} AS idunits
|
|
FROM
|
|
(SELECT 1) r
|
|
WHERE
|
|
(SELECT COUNT(*) as qtde FROM coursenotes WHERE idusers = ${idusers} AND idunits = ${idusers}) =
|
|
(SELECT COUNT(*) as qtde FROM traineeprogramcourses WHERE
|
|
idtraineeprogram = (SELECT DISTINCT idtraineeprogram FROM traineeprogramcourses WHERE idcourses = (SELECT idcourses FROM classes WHERE idclasses = ${idclasses})));`;
|
|
|
|
mysqlConnection.query(SQLQry, function (error, results, fields) {
|
|
if (!error && results.length != 0) {
|
|
//console.log(results['affectedRows']);
|
|
/*if (results['affectedRows'] == 1) {
|
|
|
|
let sqlQry0 = `
|
|
SELECT * FROM
|
|
(SELECT SUM(note) / COUNT(*) AS Nota FROM coursenotes WHERE idusers = ${idusers} AND idunits = ${idunits}) AS note,
|
|
(SELECT reference_db AS Banco from units where idunits = ${idunits}) AS banco,
|
|
(SELECT idcourses AS IDCurso from classes where idclasses = ${idclasses}) AS curso`;
|
|
|
|
mysqlConnection.query(sqlQry0, function (error, results, fields) {
|
|
if (!error && results.length != 0) {
|
|
console.log(results[0]);
|
|
let nota = results[0]['Nota'];
|
|
let db = results[0]['Banco'] + ".dbo.";
|
|
let idcourses = results[0]['IDCurso'];
|
|
let sqlQry1 = `
|
|
UPDATE
|
|
${db}TreinandosTurmas tt
|
|
SET
|
|
Nota = ${nota}
|
|
WHERE
|
|
IDMatricula = ${idusers}
|
|
AND
|
|
IDCurso = ${idcourses}`;
|
|
//console.log(sqlQry1);
|
|
sqlServerConnection.request().query(sqlQry1, function (error1, results1, fields) {
|
|
console.log("/Inserted TreinandosTurmas");
|
|
return true;
|
|
})
|
|
}
|
|
else {
|
|
return false;
|
|
}
|
|
});
|
|
}
|
|
else {
|
|
return true;
|
|
}*/
|
|
return true;
|
|
} else {
|
|
return true;
|
|
}
|
|
});
|
|
}
|
|
|
|
function InsertUserStage(idusers, idunits, idclasses, note) {
|
|
mysqlConnection.query(
|
|
'select * from userstages order by iduserstages desc;',
|
|
function (error, results, fields) {
|
|
let IDUserStages = '';
|
|
if (!results || results == '' || results.length == 0) IDUserStages = '1';
|
|
else IDUserStages = parseInt(results[0]['iduserstages']) + 1;
|
|
console.log(IDUserStages);
|
|
const SQLInsert = `INSERT INTO userstages(iduserstages, idstages, idusers, idunits, idclasses, note) values
|
|
(${IDUserStages}, 2, ${idusers}, ${idunits}, ${idclasses}, ${note});`;
|
|
mysqlConnection.query(SQLInsert, function (error, results, fields) {
|
|
if (!error)
|
|
console.log(
|
|
'/HomeWork success - Matrícula: ' +
|
|
idusers +
|
|
' ; Unidade: ' +
|
|
idunits
|
|
);
|
|
else
|
|
console.log(
|
|
'/HomeWork error - Matrícula: ' + idusers + ' ; Unidade: ' + idunits
|
|
);
|
|
});
|
|
}
|
|
);
|
|
}
|
|
|
|
function PreencherNotasSQLServer(Unidades, Index, callback) {
|
|
if (Index < Unidades.length) {
|
|
console.log(
|
|
'Analisando unidade ' +
|
|
(Index + 1) +
|
|
' de ' +
|
|
Unidades.length +
|
|
' (' +
|
|
Unidades[Index]['Unidade'] +
|
|
')'
|
|
);
|
|
let SqlQueryMySQL = `select * from (
|
|
select
|
|
cl.idcourses as 'IDCurso',
|
|
sc.idusers as 'IDMatricula',
|
|
(cl.ordem + 1) + ((co.idtypescourse - 1) * 16) as 'IDConceito',
|
|
sc.note as 'Nota'
|
|
from stepclass sc
|
|
inner join classes cl on cl.idclasses = sc.idclasses
|
|
inner join courses co on co.idcourses = cl.idcourses
|
|
inner join units un on un.idunits = sc.idunits
|
|
where co.idtypescourse in (1,2,3) and sc.idunits = ${Unidades[Index]['idunits']}) R
|
|
order by R.IDConceito asc;`;
|
|
mysqlConnection.query(SqlQueryMySQL, function (error, NotasMySQL, fields) {
|
|
let SqlQuerySQLServer = `select * from ${Unidades[Index]['db']}NotasDetalhes;`;
|
|
sqlServerConnection
|
|
.request()
|
|
.query(SqlQuerySQLServer, function (error, results, fields) {
|
|
let NotasSQLServer = [];
|
|
if (results['recordset']) {
|
|
NotasSQLServer = results['recordset'];
|
|
}
|
|
Unidades[Index].NotasLancar = [];
|
|
for (let i = 0; i < NotasMySQL.length; i++) {
|
|
let Existe = false;
|
|
for (let o = 0; o < NotasSQLServer.length; o++) {
|
|
if (
|
|
NotasMySQL[i]['IDCurso'] == NotasSQLServer[o]['IDCurso'] &&
|
|
NotasMySQL[i]['IDMatricula'] ==
|
|
NotasSQLServer[o]['IDMatricula'] &&
|
|
NotasMySQL[i]['IDConceito'] == NotasSQLServer[o]['IDConceito']
|
|
) {
|
|
Existe = true;
|
|
break;
|
|
}
|
|
}
|
|
if (!Existe) {
|
|
Unidades[Index].NotasLancar.push(NotasMySQL[i]);
|
|
}
|
|
}
|
|
console.log(
|
|
'Registros para inserir: ' + Unidades[Index]['NotasLancar'].length
|
|
);
|
|
InserirNotasSqlServer(Unidades, 0, Index, callback);
|
|
});
|
|
});
|
|
} else {
|
|
callback(Unidades);
|
|
}
|
|
}
|
|
|
|
function InserirNotasSqlServer(Unidades, Index1, Index2, callback) {
|
|
if (Index1 < Unidades[Index2]['NotasLancar'].length) {
|
|
let SqlQuerySQLInsert = `insert into ${Unidades[Index2]['db']}NotasDetalhes (IDTurma, IDCurso, IDMatricula, IDTreinando, IDConceito, Nota) select top 1 tt.CodigoTurma, ${Unidades[Index2]['NotasLancar'][Index1]['IDCurso']}, ${Unidades[Index2]['NotasLancar'][Index1]['IDMatricula']}, tt.IDTreinando, ${Unidades[Index2]['NotasLancar'][Index1]['IDConceito']}, ${Unidades[Index2]['NotasLancar'][Index1]['Nota']} from ${Unidades[Index2]['db']}TreinandosTurmas tt where tt.IDMatricula = ${Unidades[Index2]['NotasLancar'][Index1]['IDMatricula']}`;
|
|
sqlServerConnection
|
|
.request()
|
|
.query(SqlQuerySQLInsert, function (error, results, fields) {
|
|
if (!error) {
|
|
let SqlQuerySQLInsert2 = `insert into ${Unidades[Index2]['db']}NotasDetalhesSequencial (IDTurma, IDCurso, IDMatricula, IDTreinando, IDConceito) select top 1 tt.CodigoTurma, ${Unidades[Index2]['NotasLancar'][Index1]['IDCurso']}, ${Unidades[Index2]['NotasLancar'][Index1]['IDMatricula']}, tt.IDTreinando, ${Unidades[Index2]['NotasLancar'][Index1]['IDConceito']} from ${Unidades[Index2]['db']}TreinandosTurmas tt where tt.IDMatricula = ${Unidades[Index2]['NotasLancar'][Index1]['IDMatricula']}`;
|
|
sqlServerConnection
|
|
.request()
|
|
.query(SqlQuerySQLInsert2, function (error, results, fields) {
|
|
Index1++;
|
|
InserirNotasSqlServer(Unidades, Index1, Index2, callback);
|
|
});
|
|
} else {
|
|
Index1++;
|
|
InserirNotasSqlServer(Unidades, Index1, Index2, callback);
|
|
}
|
|
});
|
|
} else {
|
|
Index2++;
|
|
PreencherNotasSQLServer(Unidades, Index2, callback);
|
|
}
|
|
}
|
|
|
|
module.exports = routes;
|