956 lines
30 KiB
JavaScript
956 lines
30 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");
|
||
const formidable = require("formidable");
|
||
|
||
router.use(
|
||
bodyParser.urlencoded({
|
||
limit: "10mb",
|
||
extended: true,
|
||
})
|
||
);
|
||
router.use(
|
||
bodyParser.json({
|
||
limit: "10mb",
|
||
extended: true,
|
||
})
|
||
);
|
||
|
||
var routes = function () {
|
||
|
||
router.get("/getQtdeChamadas/:idusers/:idcourses/:db", verifyJWT,(req, res) => {
|
||
const idusers = parseInt(req.params.idusers);
|
||
const idcourses = req.params.idcourses;
|
||
const db = req.params.db;
|
||
let sqlQuery = `
|
||
SELECT
|
||
co.idcourses AS IDCurso,
|
||
CASE WHEN (SELECT COUNT(1) FROM zoeusers zu INNER JOIN zoeunits zun ON zun.id = zu.idunits WHERE CONCAT(zun.id, '.dbo.') = '${db}' AND zu.user = ${idusers} AND zu.idzoeusertype = 1) > 0 THEN 100 ELSE COUNT(cl.idclasses) END AS qtde_presencas
|
||
FROM courses co
|
||
LEFT JOIN classes cl ON cl.idcourses = co.idcourses AND cl.disabled = 0
|
||
WHERE co.idcourses IN ${idcourses}
|
||
GROUP BY co.idcourses
|
||
ORDER BY co.idcourses ASC;`;
|
||
//console.log(sqlQuery);
|
||
execSQLQuery(sqlQuery, res, req.originalUrl);
|
||
});
|
||
|
||
router.get("/getQtdeChamadasOld/:idusers/:idcourses/:db", verifyJWT,(req, res) => {
|
||
const idusers = parseInt(req.params.idusers);
|
||
const idcourses = req.params.idcourses;
|
||
const db = req.params.db;
|
||
let sqlQuery = `
|
||
SELECT
|
||
c.IDCurso,
|
||
COUNT(*) AS qtde_presencas
|
||
FROM ${db}Chamadas c
|
||
INNER JOIN ${db}TreinandosTurmas tt ON tt.CodigoTurma = c.IDTurma AND tt.IDCurso = c.IDCurso
|
||
INNER JOIN ${db}ChamadasDetalhes cd ON cd.IDChamadas = c.IDChamadas
|
||
INNER JOIN ${db}Cursos cu ON cu.Codigo = c.IDCurso
|
||
WHERE c.Flex = 1 AND c.IDCurso IN ${idcourses} AND tt.IDMatricula = ${idusers} AND cd.Presente = 1
|
||
GROUP BY c.IDCurso, cu.NomeVersao
|
||
ORDER BY cu.NomeVersao ASC;`;
|
||
//console.log(sqlQuery);
|
||
execSQLQuery2(sqlQuery, res, req.originalUrl);
|
||
});
|
||
|
||
|
||
|
||
|
||
router.get("/getClass/:idclasses", verifyJWT, (req, res) => {
|
||
const idclasses = parseInt(req.params.idclasses);
|
||
execSQLQuery(
|
||
`SELECT
|
||
*
|
||
FROM classes
|
||
WHERE idclasses = ${idclasses};`,
|
||
res,
|
||
req.originalUrl
|
||
);
|
||
});
|
||
/* router.get('/adjustXp', (req, res) => {
|
||
execSQLQuery(`SELECT idusers, completedClass, idunits FROM myrobot.stepclass where completedClass = 1;`, res, req.originalUrl);
|
||
}); */
|
||
|
||
router.get("/getClassByAny/:field/:value", verifyJWT, (req, res) => {
|
||
const field = req.params.field;
|
||
const value = req.params.value;
|
||
execSQLQuery(
|
||
`SELECT
|
||
*
|
||
FROM classes
|
||
WHERE ? = ?;`,
|
||
res,
|
||
req.originalUrl,
|
||
[field, value]
|
||
);
|
||
});
|
||
|
||
router.get("/getQtdeClass/:idusers/:idcourses/:db", verifyJWT, (req, res) => {
|
||
const matricula = parseInt(req.params.idusers);
|
||
const idcurso = req.params.idcourses;
|
||
const db = req.params.db;
|
||
let sqlQuery = `select count(*) as qtde_presencas
|
||
from ${db}Chamadas c
|
||
inner join ${db}TreinandosTurmas tt
|
||
on tt.CodigoTurma = c.IDTurma
|
||
and tt.IDCurso = c.IDCurso
|
||
inner join ${db}ChamadasDetalhes cd
|
||
on cd.IDChamadas = c.IDChamadas
|
||
and cd.IDTreinando = tt.IDTreinando
|
||
where c.Flex = 1
|
||
and c.IDCurso = ${idcurso}
|
||
and tt.IDMatricula = ${matricula}
|
||
and cd.Presente = 1;`;
|
||
execSQLQuery2(sqlQuery, res, req.originalUrl);
|
||
});
|
||
|
||
|
||
|
||
router.get("/getClassByCourse/:idcourses", verifyJWT, (req, res) => {
|
||
const idcourses = parseInt(req.params.idcourses);
|
||
execSQLQuery(
|
||
`SELECT
|
||
*
|
||
FROM classes
|
||
WHERE idcourses = ${idcourses} AND disabled = 0 ORDER BY ordem asc;`,
|
||
res,
|
||
req.originalUrl
|
||
);
|
||
});
|
||
|
||
router.get("/getClassbyCourse/count/:idcourses", verifyJWT, (req, res) => {
|
||
const idcourses = parseInt(req.params.idcourses);
|
||
execSQLQuery(
|
||
`SELECT count(*) as numeroDeAulas FROM classes WHERE idcourses = ${idcourses} AND disabled = 0 ORDER BY ordem asc;`,
|
||
res,
|
||
req.originalUrl
|
||
);
|
||
});
|
||
|
||
router.get(
|
||
"/getClassEnByCourse/:idcourses/:qtd/:homework",
|
||
verifyJWT,
|
||
(req, res) => {
|
||
const idcourses = parseInt(req.params.idcourses);
|
||
const qtd = parseInt(req.params.qtd);
|
||
const homework = parseInt(req.params.homework);
|
||
execSQLQuery(
|
||
`SELECT
|
||
idclasses,
|
||
idcourses,
|
||
name,
|
||
case when disabled = 0 and ordem < ${qtd} and (viewed < 2 or ${qtd} != 100) then 0 else 1 end as disabled,
|
||
ordem,
|
||
description,
|
||
viewed,
|
||
aval,
|
||
download,
|
||
demo,
|
||
homework,
|
||
sprites
|
||
FROM classes
|
||
WHERE idcourses = ${idcourses} AND disabled = 0 and homework = ${homework}
|
||
ORDER BY ordem asc;`,
|
||
res,
|
||
req.originalUrl
|
||
);
|
||
}
|
||
);
|
||
|
||
router.get(
|
||
"/getClassEnByCourseAll/:idcourses/:qtd/:homework",
|
||
verifyJWT,
|
||
(req, res) => {
|
||
const idcourses = parseInt(req.params.idcourses);
|
||
const qtd = parseInt(req.params.qtd);
|
||
const homework = parseInt(req.params.homework);
|
||
execSQLQuery(
|
||
`SELECT
|
||
idclasses,
|
||
idcourses,
|
||
name,
|
||
case when (disabled = 0 or disabled = 1) and (viewed < 2 or ${qtd} != 100) then 0 else 1 end as disabled,
|
||
ordem,
|
||
description,
|
||
viewed,
|
||
aval,
|
||
download,
|
||
demo,
|
||
homework,
|
||
sprites
|
||
FROM classes
|
||
WHERE idcourses = ${idcourses} AND homework = ${homework}
|
||
ORDER BY ordem asc;`,
|
||
res,
|
||
req.originalUrl
|
||
);
|
||
}
|
||
);
|
||
|
||
router.post("/insertStepClass", verifyJWT, (req, res) => {
|
||
const idusers = parseInt(req.body.idusers);
|
||
const idclasses = parseInt(req.body.idclasses);
|
||
const idunits = parseInt(req.body.idunits);
|
||
mysqlConnection.query(
|
||
"select * from stepclass order by idstepclass desc;",
|
||
function (error, results, fields) {
|
||
let IDStepClass = "";
|
||
if (!results || results == "" || results.length == 0) IDStepClass = "1";
|
||
else IDStepClass = parseInt(results[0]["idstepclass"]) + 1;
|
||
let SQLInsert = `INSERT INTO stepclass(idstepclass, idusers, idclasses, actualClass, actualExercise, actualAnswer, actualItem, answersdb, continueClass, completedClass, idunits) VALUES
|
||
(?, ?, ?, 0, 0, 0, 0, '', 0, 0, ?);`;
|
||
execSQLQuery(
|
||
SQLInsert,
|
||
res,
|
||
req.originalUrl,
|
||
[IDStepClass, idusers, idclasses, idunits],
|
||
function (Retorno) {
|
||
if (Retorno.status == 500) {
|
||
console.log(Retorno.error);
|
||
}
|
||
}
|
||
);
|
||
}
|
||
);
|
||
});
|
||
|
||
router.get(
|
||
"/lockClass/:idusers/:idunits/:idclasses",
|
||
verifyJWT,
|
||
(req, res) => {
|
||
const idusers = parseInt(req.params.idusers);
|
||
const idclasses = parseInt(req.params.idclasses);
|
||
const idunits = parseInt(req.params.idunits);
|
||
|
||
let sqlQuery = `UPDATE stepclass SET
|
||
lockedClass = 1
|
||
WHERE idunits = ${idunits} and idclasses = ${idclasses} and idusers = ${idusers};`;
|
||
execSQLQuery(sqlQuery, res, req.originalUrl);
|
||
}
|
||
);
|
||
|
||
router.patch("/updateStepClass", verifyJWT, (req, res) => {
|
||
const idusers = parseInt(req.body.idusers);
|
||
const idclasses = parseInt(req.body.idclasses);
|
||
const idunits = parseInt(req.body.idunits);
|
||
const actualClass = parseInt(req.body.actualClass);
|
||
const actualExercise = parseInt(req.body.actualExercise);
|
||
const actualAnswer = parseInt(req.body.actualAnswer);
|
||
const actualItem = parseInt(req.body.actualItem);
|
||
const answersdb = req.body.answersdb;
|
||
const continueClass = req.body.continueClass;
|
||
const completedClass = req.body.completedClass;
|
||
const notegame = req.body.notegame;
|
||
|
||
let sqlQuery = `UPDATE stepclass SET
|
||
actualClass = ${actualClass},
|
||
actualExercise = ${actualExercise},
|
||
actualAnswer = ${actualAnswer},
|
||
actualItem = ${actualItem},
|
||
answersdb = '${answersdb}',
|
||
continueClass = ${continueClass},
|
||
completedClass = ${completedClass},
|
||
notegame = ${notegame}
|
||
WHERE idunits = ${idunits} and idclasses = ${idclasses} and idusers = ${idusers};`;
|
||
execSQLQuery(sqlQuery, res, req.originalUrl);
|
||
});
|
||
|
||
router.patch("/addNote/:idunits", verifyJWT, (req, res) => {
|
||
const idusers = parseInt(req.body.idusers);
|
||
const idclasses = parseInt(req.body.idclasses);
|
||
const note = req.body.note;
|
||
const idunits = parseInt(req.params.idunits);
|
||
execSQLQuery(
|
||
`UPDATE stepclass SET
|
||
note = ${note},
|
||
notification = 1
|
||
WHERE idusers = ${idusers} AND idclasses = ${idclasses} AND idunits = ${idunits};`,
|
||
res,
|
||
req.originalUrl
|
||
);
|
||
});
|
||
|
||
router.patch("/setFinalDate", verifyJWT, (req, res) => {
|
||
const datefinal = req.body.datefinal;
|
||
const idusers = req.body.idusers;
|
||
const idclasses = req.body.idclasses;
|
||
const idunits = req.body.idunits;
|
||
execSQLQuery(
|
||
`UPDATE stepclass SET
|
||
datefinal = (select date_format(date_add(now(), interval 6 day), '%d/%m/%Y'))
|
||
WHERE idusers = ${idusers} AND idclasses = ${idclasses} AND idunits = ${idunits};`,
|
||
res,
|
||
req.originalUrl
|
||
);
|
||
});
|
||
|
||
router.get("/PlusView/:idclasses", verifyJWT, (req, res) => {
|
||
const idclasses = req.params.idclasses;
|
||
execSQLQuery(
|
||
`UPDATE classes SET
|
||
viewed = viewed + 1
|
||
WHERE idclasses = ${idclasses};`,
|
||
res,
|
||
req.originalUrl
|
||
);
|
||
});
|
||
|
||
router.post("/addCourseNote", verifyJWT, (req, res) => {
|
||
const idusers = parseInt(req.body.idusers);
|
||
const idcourses = parseInt(req.body.idcourses);
|
||
const note = req.body.note;
|
||
const idunits = parseInt(req.body.idunits);
|
||
execSQLQuery(
|
||
`INSERT INTO coursenotes (idusers, idcourses, note, idunits)
|
||
VALUES (${idusers}, ${idcourses}, ${note}, ${idunits});`,
|
||
res,
|
||
req.originalUrl
|
||
);
|
||
});
|
||
|
||
router.get("/getActualConcept/:idclasses", verifyJWT, (req, res) => {
|
||
const idclasses = parseInt(req.params.idclasses);
|
||
execSQLQuery(
|
||
`SELECT
|
||
(ordem + 1) as concept
|
||
FROM classes
|
||
WHERE idclasses = ${idclasses};`,
|
||
res,
|
||
req.originalUrl
|
||
);
|
||
});
|
||
|
||
router.patch("/removeNotification", verifyJWT, (req, res) => {
|
||
const idusers = parseInt(req.body.idusers);
|
||
const idunits = parseInt(req.body.idunits);
|
||
execSQLQuery(
|
||
`UPDATE stepclass SET
|
||
notification = 0
|
||
WHERE idusers = ${idusers} AND idunits = ${idunits}`,
|
||
res,
|
||
req.originalUrl
|
||
);
|
||
});
|
||
|
||
router.get(
|
||
"/getTreinandosTurmas/:matricula/:idcurso/:db",
|
||
verifyJWT,
|
||
(req, res) => {
|
||
const matricula = parseInt(req.params.matricula);
|
||
const idcurso = parseInt(req.params.idcurso);
|
||
const db = req.params.db;
|
||
execSQLQuery2(
|
||
`SELECT
|
||
*
|
||
FROM ${db}TreinandosTurmas
|
||
WHERE IDMatricula = ${matricula} AND IDCurso = ${idcurso};`,
|
||
res,
|
||
req.originalUrl
|
||
);
|
||
}
|
||
);
|
||
|
||
router.post("/insertNotasDetalhes/:banco", verifyJWT, (req, res) => {
|
||
const db = req.params.banco;
|
||
const IDTurma = parseInt(req.body.IDTurma);
|
||
const IDCurso = parseInt(req.body.IDCurso);
|
||
const IDMatricula = parseInt(req.body.IDMatricula);
|
||
const IDTreinando = parseInt(req.body.IDTreinando);
|
||
const IDConceito = parseInt(req.body.IDConceito);
|
||
const NumeroProva = parseInt(req.body.NumeroProva);
|
||
const Nota = req.body.Nota;
|
||
execSQLQuery2(
|
||
`INSERT INTO ${db}NotasDetalhes (IDTurma, IDCurso, IDMatricula, IDTreinando, IDConceito, Nota)
|
||
VALUES (${IDTurma}, ${IDCurso}, ${IDMatricula}, ${IDTreinando}, ${IDConceito}, ${Nota});
|
||
INSERT INTO ${db}NotasDetalhesSequencial (IDTurma, IDCurso, IDMatricula, IDTreinando, IDConceito)
|
||
VALUES (${IDTurma}, ${IDCurso}, ${IDMatricula}, ${IDTreinando}, ${IDConceito});`,
|
||
res,
|
||
req.originalUrl
|
||
);
|
||
});
|
||
|
||
router.get("/getAllStepClass/:englishbot", verifyJWT, (req, res) => {
|
||
const englishbot = parseInt(req.params.englishbot);
|
||
execSQLQuery(
|
||
`select
|
||
sc.*,
|
||
sc.idusers as 'Matricula',
|
||
un.unityname as 'Unidade',
|
||
cl.name as 'Aula',
|
||
co.name as 'Curso',
|
||
sc.completedClass as 'Concluida',
|
||
sc.actualExercise as 'Etapa'
|
||
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 ${englishbot == 1 ? "=" : "!="} 5;`,
|
||
res,
|
||
req.originalUrl
|
||
);
|
||
});
|
||
|
||
router.get(
|
||
"/getStepClass/:idusers/:idclasses/:idunits",
|
||
(req, res) => {
|
||
const idusers = parseInt(req.params.idusers);
|
||
const idclasses = parseInt(req.params.idclasses);
|
||
const idunits = parseInt(req.params.idunits);
|
||
const sql = `SELECT
|
||
sc.*,
|
||
(SELECT COUNT(*)
|
||
FROM (
|
||
SELECT
|
||
c.idclassescontent,
|
||
COALESCE(e.idexercises, m.idmedia, t.idtexts, s.idslides, c.montagem) AS content_id,
|
||
COALESCE(e.description, m.description, t.description, s.title, 'Hora da montagem') AS description,
|
||
COALESCE(e.enabled, 1, t.enabled, 1, 1) AS enabled,
|
||
CASE
|
||
WHEN e.idexercises IS NOT NULL THEN 'Exercício'
|
||
WHEN m.idmedia IS NOT NULL THEN 'Mídia'
|
||
WHEN t.idtexts IS NOT NULL THEN CASE WHEN t.game = 0 THEN 'Texto' ELSE 'Jogo' END
|
||
WHEN s.idslides IS NOT NULL THEN 'Slide'
|
||
WHEN c.montagem = 1 THEN 'Montagem'
|
||
END AS type,
|
||
CASE
|
||
WHEN e.idexercises IS NOT NULL THEN 'Editar'
|
||
WHEN m.idmedia IS NOT NULL THEN 'Prever'
|
||
WHEN t.idtexts IS NOT NULL THEN 'Editar'
|
||
WHEN s.idslides IS NOT NULL THEN 'Prever'
|
||
WHEN c.montagem = 1 THEN '⠀⠀⠀⠀⠀'
|
||
END AS acao
|
||
FROM classescontent c
|
||
LEFT JOIN exercises e ON c.idexercises = e.idexercises
|
||
LEFT JOIN media m ON c.idmedia = m.idmedia
|
||
LEFT JOIN texts t ON c.idtexts = t.idtexts
|
||
LEFT JOIN slides s ON c.idslides = s.idslides
|
||
WHERE c.idclasses = ${idclasses}
|
||
) AS content_table) AS qtdeItens
|
||
FROM stepclass sc
|
||
WHERE
|
||
sc.idusers = ${idusers}
|
||
AND sc.idclasses = ${idclasses}
|
||
AND sc.idunits = ${idunits};`;
|
||
mysqlConnection.query(sql, function (error, results, fields) {
|
||
console.log(req.originalUrl + " success 200");
|
||
res.send(
|
||
JSON.stringify({
|
||
response: results,
|
||
error: error,
|
||
status: results.length > 0 ? 200 : 500,
|
||
})
|
||
);
|
||
});
|
||
}
|
||
);
|
||
|
||
router.get("/getStepClassByUser/:idusers/:idunits", verifyJWT, (req, res) => {
|
||
const idusers = parseInt(req.params.idusers);
|
||
const idunits = parseInt(req.params.idunits);
|
||
execSQLQuery(
|
||
`SELECT
|
||
*
|
||
FROM stepclass
|
||
WHERE idusers = ${idusers} AND idunits = ${idunits};`,
|
||
res,
|
||
req.originalUrl
|
||
);
|
||
});
|
||
|
||
router.get("/list", verifyJWT, (req, res) => {
|
||
execSQLQuery(
|
||
`SELECT
|
||
classes.idclasses,
|
||
classes.name,
|
||
classes.disabled,
|
||
classes.ordem,
|
||
courses.name as coursename,
|
||
COUNT(classescontent.idclassescontent) as 'classContent'
|
||
FROM classes
|
||
JOIN courses ON classes.idcourses = courses.idcourses
|
||
LEFT JOIN classescontent ON classes.idclasses = classescontent.idclasses
|
||
GROUP BY classes.idclasses;`,
|
||
res,
|
||
req.originalUrl
|
||
);
|
||
});
|
||
|
||
router.get("/listEn", verifyJWT, (req, res) => {
|
||
execSQLQuery(
|
||
`SELECT
|
||
c.idclasses,
|
||
c.name,
|
||
c.disabled,
|
||
c.ordem,
|
||
c.download,
|
||
co.name AS coursename,
|
||
(select count(*) from courses co2 where co2.idcourses = c.idcourses and co2.idtypescourse = 5) as 'cursoingles',
|
||
COUNT(cc.idclassescontent) AS 'classContent'
|
||
FROM classes c
|
||
JOIN courses co ON c.idcourses = co.idcourses
|
||
LEFT JOIN classescontent cc ON c.idclasses = cc.idclasses
|
||
GROUP BY c.idcourses, c.idclasses
|
||
ORDER BY co.idcourses ASC, c.ordem ASC;`,
|
||
res,
|
||
req.originalUrl
|
||
);
|
||
});
|
||
|
||
router.get("/disable/:idclasses", verifyJWT, (req, res) => {
|
||
const idclasses = parseInt(req.params.idclasses);
|
||
execSQLQuery(
|
||
`UPDATE classes SET
|
||
disabled = not disabled
|
||
WHERE idclasses = ${idclasses};`,
|
||
res,
|
||
req.originalUrl
|
||
);
|
||
});
|
||
|
||
router.post("/insert", verifyJWT, (req, res) => {
|
||
const idcourses = parseInt(req.body.idcourses);
|
||
const name = req.body.name;
|
||
const disabled = req.body.disabled ? 1 : 0;
|
||
const description = req.body.description;
|
||
const aval = req.body.aval ? 1 : 0;
|
||
const download = req.body.download ? 1 : 0;
|
||
const demo = req.body.demo ? 1 : 0;
|
||
const homework = req.body.homework ? 1 : 0;
|
||
const sprites = parseInt(req.body.sprites);
|
||
execSQLQuery(
|
||
`INSERT INTO classes(idcourses, name, disabled, ordem, description, aval, download, viewed, demo, homework, sprites) SELECT
|
||
?, ?, ?, (select coalesce((select (ordem + 1) as ordem from classes where idcourses = ${idcourses} order by ordem desc limit 1), 0)), ?, ?, ?, 0, ?, ?, ?;`,
|
||
res,
|
||
req.originalUrl,
|
||
[
|
||
idcourses,
|
||
name,
|
||
disabled,
|
||
description,
|
||
aval,
|
||
download,
|
||
demo,
|
||
homework,
|
||
sprites,
|
||
]
|
||
);
|
||
});
|
||
|
||
router.patch("/update", verifyJWT, (req, res) => {
|
||
const idclasses = parseInt(req.body.idclasses);
|
||
const idcourses = parseInt(req.body.idcourses);
|
||
const name = req.body.name;
|
||
const disabled = req.body.disabled ? 1 : 0;
|
||
const description = req.body.description;
|
||
const viewed = parseInt(req.body.viewed);
|
||
const aval = req.body.aval ? 1 : 0;
|
||
const download = req.body.download ? 1 : 0;
|
||
const demo = req.body.demo ? 1 : 0;
|
||
const homework = req.body.homework ? 1 : 0;
|
||
const sprites = parseInt(req.body.sprites);
|
||
execSQLQuery(
|
||
`UPDATE classes SET
|
||
idcourses = ?,
|
||
name = ?,
|
||
disabled = ?,
|
||
description = ?,
|
||
viewed = ?,
|
||
aval = ?,
|
||
download = ?,
|
||
demo = ?,
|
||
homework = ?,
|
||
sprites = ?
|
||
WHERE idclasses = ?;`,
|
||
res,
|
||
req.originalUrl,
|
||
[
|
||
idcourses,
|
||
name,
|
||
disabled,
|
||
description,
|
||
viewed,
|
||
aval,
|
||
download,
|
||
demo,
|
||
homework,
|
||
sprites,
|
||
idclasses,
|
||
]
|
||
);
|
||
});
|
||
|
||
router.delete("/delete/:idclasses", verifyJWT, (req, res) => {
|
||
const idclasses = parseInt(req.params.idclasses);
|
||
execSQLQuery(
|
||
`DELETE FROM classes
|
||
WHERE idclasses = ${idclasses};`,
|
||
res,
|
||
req.originalUrl
|
||
);
|
||
});
|
||
|
||
router.post("/copy", verifyJWT, (req, res) => {
|
||
let form = new formidable.IncomingForm();
|
||
form.maxFileSize = 1024 * 1024 * 1024;
|
||
form.parse(req, function (err, fields, files) {
|
||
const fs = require("fs");
|
||
var idclasses = fields.idclasses;
|
||
var GifFile = "";
|
||
var BackFile = "";
|
||
|
||
try {
|
||
GifFile = files.gif.path;
|
||
} catch (e) {
|
||
console.log("Não copia Gif");
|
||
}
|
||
try {
|
||
BackFile = files.back.path;
|
||
} catch (e) {
|
||
console.log("Não copia Back");
|
||
}
|
||
|
||
if (GifFile == "" && BackFile != "") {
|
||
fs.rename(
|
||
BackFile,
|
||
Variables("src_media") + "images/back_" + idclasses + ".jpg",
|
||
function (err) {
|
||
console.log("/classes/copy success 200");
|
||
res.send(true);
|
||
}
|
||
);
|
||
} else if (GifFile != "" && BackFile == "") {
|
||
fs.rename(
|
||
GifFile,
|
||
Variables("src_media") + "images/gif_" + idclasses + ".gif",
|
||
function (err) {
|
||
console.log("/classes/copy success 200");
|
||
res.send(true);
|
||
}
|
||
);
|
||
} else if (GifFile != "" && BackFile != "") {
|
||
fs.rename(
|
||
GifFile,
|
||
Variables("src_media") + "images/gif_" + idclasses + ".gif",
|
||
function (err) {
|
||
fs.rename(
|
||
BackFile,
|
||
Variables("src_media") + "images/back_" + idclasses + ".jpg",
|
||
function (err) {
|
||
console.log("/classes/copy success 200");
|
||
res.send(true);
|
||
}
|
||
);
|
||
}
|
||
);
|
||
} else {
|
||
res.send(true);
|
||
}
|
||
});
|
||
});
|
||
|
||
router.post("/insertUserStage", verifyJWT, (req, res) => {
|
||
const iduserstages = req.body.iduserstages;
|
||
const idclassescontent = parseInt(req.body.idclassescontent);
|
||
const idusers = parseInt(req.body.idusers);
|
||
const idunits = parseInt(req.body.idunits);
|
||
const idclasses = parseInt(req.body.idclasses);
|
||
const note = req.body.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;
|
||
const SQLInsert = `INSERT INTO userstages(iduserstages, idstages, idusers, idunits, idclasses, note)
|
||
SELECT
|
||
${IDUserStages},
|
||
(SELECT DISTINCT idstages FROM stages WHERE convert(description using utf8) LIKE convert(Stage.description using utf8)) AS idstages,
|
||
${idusers} AS idusers,
|
||
${idunits} AS idunits,
|
||
${idclasses} AS idclasses,
|
||
${note} AS note
|
||
FROM (SELECT CASE
|
||
WHEN idgames is not null THEN 'Jogo'
|
||
WHEN idslides is not null THEN 'Montagem'
|
||
ELSE 'Lição de Casa'
|
||
END AS description
|
||
FROM classescontent
|
||
WHERE idclassescontent = ${idclassescontent}) Stage;`;
|
||
//console.log(SQLInsert);
|
||
execSQLQuery(SQLInsert, res, req.originalUrl);
|
||
}
|
||
);
|
||
});
|
||
|
||
router.post("/insertZoeUserGames", verifyJWT, (req, res) => {
|
||
const idunits = parseInt(req.body.idunits);
|
||
const idusers = parseInt(req.body.idusers);
|
||
const idclasses = parseInt(req.body.idclasses);
|
||
const idgames = parseInt(req.body.idgames);
|
||
const note = req.body.note;
|
||
execSQLQuery(
|
||
`insert into zoeusergames (idunits, idusers, idgames, idclasses, note) values (${idunits}, ${idusers}, ${idgames}, ${idclasses}, ${note});`,
|
||
res,
|
||
req.originalUrl
|
||
);
|
||
});
|
||
|
||
router.get("/CarregarImagem/:Nome", (req, res) => {
|
||
let Protocolo = "http://";
|
||
try {
|
||
Protocolo = req.headers.referer.split("/")[0] + "//";
|
||
} catch (e) {}
|
||
const fs = require("fs");
|
||
const Nome = req.params.Nome;
|
||
const DirLocal = `${Variables("src_media")}images/${Nome}`;
|
||
console.log(DirLocal);
|
||
const DirWeb = `${Protocolo}${
|
||
req.headers.host.split(":")[0]
|
||
}/images/${Nome}`;
|
||
console.log(DirWeb);
|
||
if (DirLocal.includes("C:/")) {
|
||
let diretoriodearquivolocal = DirWeb;
|
||
diretoriodearquivolocal = diretoriodearquivolocal.replace(
|
||
"localhost",
|
||
"localhost:4200"
|
||
);
|
||
diretoriodearquivolocal = diretoriodearquivolocal.replace(
|
||
"images",
|
||
"assets/imagesAula"
|
||
);
|
||
res.redirect(diretoriodearquivolocal);
|
||
} else {
|
||
if (fs.existsSync(DirLocal)) {
|
||
res.redirect(DirWeb);
|
||
} else {
|
||
const shell = require("shelljs");
|
||
shell.exec(
|
||
"cd " +
|
||
Variables("src_media") +
|
||
"images/;wget http://18.228.91.135/images/" +
|
||
Nome,
|
||
function (sucesso) {
|
||
//console.log(sucesso);
|
||
res.redirect(DirWeb);
|
||
}
|
||
);
|
||
}
|
||
}
|
||
});
|
||
|
||
router.patch("/AtualizarOrdem", verifyJWT, (req, res) => {
|
||
const idclasses = parseInt(req.body.idclasses);
|
||
const ordem = parseInt(req.body.ordem);
|
||
execSQLQuery(
|
||
`UPDATE classes SET
|
||
ordem = ${ordem}
|
||
WHERE idclasses = ${idclasses};`,
|
||
res,
|
||
req.originalUrl
|
||
);
|
||
});
|
||
|
||
router.get("/getTeacherPasswords/:db", verifyJWT, (req, res) => {
|
||
const db = req.params.db;
|
||
execSQLQuery2(
|
||
`SELECT
|
||
Senha
|
||
FROM ${db}Funcionarios
|
||
where Desativado = 0;`,
|
||
res,
|
||
req.originalUrl
|
||
);
|
||
});
|
||
|
||
router.get("/getFotoAluno/:idusers/:db", (req, res) => {
|
||
const idusers = parseInt(req.params.idusers);
|
||
const db = req.params.db;
|
||
let sqlQuery = `select distinct
|
||
tt.IDTreinando
|
||
from ${db}TreinandosTurmas tt
|
||
where tt.IDMatricula = ${idusers};`;
|
||
if (Variables("local") == "Local") {
|
||
console.log(req.originalUrl + " error 403");
|
||
res.send(
|
||
JSON.stringify({
|
||
response: "",
|
||
error: null,
|
||
status: 403,
|
||
})
|
||
);
|
||
return;
|
||
}
|
||
sqlServerConnection
|
||
.request()
|
||
.query(sqlQuery, function (error, results, fields) {
|
||
if (!error && results["recordsets"][0].length != 0) {
|
||
const fs = require("fs");
|
||
|
||
/* fs.readdir(Variables("src_fotos"), (err, files) => {
|
||
let FotoId = "";
|
||
files.forEach(Foto => {
|
||
if (Foto.split('.')[0] == results['recordsets'][0][0]['IDTreinando']) {
|
||
FotoId = Foto;
|
||
}
|
||
});
|
||
if (FotoId != "") {
|
||
fs.readFile(Variables("src_fotos") + FotoId, function(err, data) {
|
||
if (err) throw err
|
||
console.log(req.originalUrl + " success 200");
|
||
res.send(JSON.stringify({
|
||
"response": data,
|
||
"error": null,
|
||
"status": 200
|
||
}));
|
||
});
|
||
}
|
||
else {
|
||
console.log(req.originalUrl + " error 500");
|
||
res.send(JSON.stringify({
|
||
"response": "",
|
||
"error": null,
|
||
"status": 500
|
||
}));
|
||
}
|
||
}); */
|
||
} else {
|
||
console.log(req.originalUrl + " error 500");
|
||
res.send(
|
||
JSON.stringify({
|
||
response: "",
|
||
error: null,
|
||
status: 500,
|
||
})
|
||
);
|
||
}
|
||
});
|
||
});
|
||
|
||
router.post("/AlteraNotaAula", verifyJWT, (req, res) => {
|
||
const idclasses = parseInt(req.body.idclasses);
|
||
const idunits = parseInt(req.body.idunits);
|
||
const idusers = parseInt(req.body.idusers);
|
||
const Nota = req.body.Nota;
|
||
const SQLUpdate = `UPDATE stepclass SET note = ${Nota} WHERE idunits = ${idunits} AND idclasses = ${idclasses} AND idusers = ${idusers};`;
|
||
mysqlConnection.query(SQLUpdate, function (error, results, fields) {
|
||
const SQLDelete = `delete from coursenotes where idcourses = (select idcourses from classes where idclasses = ${idclasses}) and idunits = ${idunits} and idusers = ${idusers};`;
|
||
mysqlConnection.query(SQLDelete, function (error2, results2, fields2) {
|
||
InsertCourseNotes();
|
||
console.log(req.originalUrl + " success 200");
|
||
res.send(
|
||
JSON.stringify({
|
||
response: results2,
|
||
error: error2,
|
||
status: 200,
|
||
})
|
||
);
|
||
});
|
||
});
|
||
});
|
||
|
||
router.get(
|
||
"/AlteraStatusAulaAssistida/:idunits/:classes/:idusers/:status",
|
||
verifyJWT,
|
||
(req, res) => {
|
||
const idunits = parseInt(req.params.idunits);
|
||
const idclasses = parseInt(req.params.idclasses);
|
||
const idusers = parseInt(req.params.idusers);
|
||
const status = parseInt(req.params.status);
|
||
execSQLQuery(
|
||
`UPDATE stepclass SET
|
||
liberado = ${status}
|
||
WHERE idunits = ${idunits} and idclasses = ${idclasses} and idusers = ${idusers};`,
|
||
res,
|
||
req.originalUrl
|
||
);
|
||
}
|
||
);
|
||
|
||
router.get("/getTipoCursoPorAula/:idclasses", verifyJWT, (req, res) => {
|
||
const idclasses = parseInt(req.params.idclasses);
|
||
execSQLQuery(
|
||
`SELECT
|
||
tc.*
|
||
FROM typescourse tc
|
||
inner join courses co on co.idtypescourse = tc.idtypescourse
|
||
inner join classes cl on cl.idcourses = co.idcourses
|
||
where cl.idclasses = ${idclasses};`,
|
||
res,
|
||
req.originalUrl
|
||
);
|
||
});
|
||
|
||
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);
|
||
}
|
||
}
|
||
|
||
return router;
|
||
};
|
||
|
||
module.exports = routes;
|