1263 lines
51 KiB
JavaScript
1263 lines
51 KiB
JavaScript
require("../connections/mysql-server");
|
|
require("../helpers/CheckToken");
|
|
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("/listLogin", (req, res) => {
|
|
execSQLQuery(`
|
|
SELECT
|
|
CAST(zu.id AS CHAR) AS reference_db,
|
|
zu.description AS unityname,
|
|
COALESCE(zu.idunits, zu.id + 200) AS idunits,
|
|
'' AS ip_externo,
|
|
'' AS ip_local,
|
|
0 AS porta_mysql
|
|
FROM zoeunits zu
|
|
WHERE zu.enabled = 1
|
|
ORDER BY description ASC;`,
|
|
res,
|
|
req.originalUrl
|
|
);
|
|
});
|
|
|
|
router.get("/listLoginOld", (req, res) => {
|
|
execSQLQuery(`
|
|
SELECT
|
|
*
|
|
FROM units
|
|
WHERE enabled = 1
|
|
ORDER BY description ASC;`,
|
|
res,
|
|
req.originalUrl
|
|
);
|
|
});
|
|
|
|
|
|
router.get("/getEnabledUnits", (req, res) => {
|
|
let sqlQry = `select
|
|
un.idunits as 'ID',
|
|
un.unityname as 'Unidade',
|
|
un.address as 'Endereco',
|
|
un.neighborhood as 'Bairro',
|
|
un.cep as 'CEP',
|
|
st.description as 'Estado',
|
|
ct.description as 'Cidade'
|
|
from units un
|
|
inner join cities ct on ct.idcities = un.idcities
|
|
inner join states st on st.idstates = un.idstates
|
|
where un.enabled = 1
|
|
order by un.unityname asc;`;
|
|
execSQLQuery(sqlQry, res, req.originalUrl);
|
|
});
|
|
|
|
router.get("/listUnits", (req, res) => {
|
|
execSQLQuery(
|
|
`SELECT * FROM units WHERE enabled = 1;`,
|
|
res,
|
|
req.originalUrl
|
|
);
|
|
});
|
|
|
|
router.get("/getConfig/:Campo", (req, res) => {
|
|
let Campo = req.params.Campo;
|
|
execSQLQuery(`SELECT ${Campo} FROM config;`, res, req.originalUrl);
|
|
});
|
|
|
|
router.get("/getAll", verifyJWT, (req, res) => {
|
|
execSQLQuery(
|
|
`SELECT
|
|
*
|
|
FROM units
|
|
ORDER BY unityname ASC;`,
|
|
res,
|
|
req.originalUrl
|
|
);
|
|
});
|
|
|
|
router.get("/list", verifyJWT, (req, res) => {
|
|
execSQLQuery(
|
|
`SELECT
|
|
idunits,
|
|
unityname,
|
|
phone,
|
|
manager,
|
|
email,
|
|
enabled,
|
|
reference_db
|
|
FROM units
|
|
ORDER BY unityname ASC;`,
|
|
res,
|
|
req.originalUrl
|
|
);
|
|
});
|
|
|
|
router.get("/listDis", (req, res) => {
|
|
execSQLQuery(
|
|
`SELECT distinct
|
|
idunits,
|
|
unityname,
|
|
reference_db,
|
|
celphone
|
|
FROM units
|
|
WHERE enabled = 1 and reference_db not like '%Learning%';`,
|
|
res,
|
|
req.originalUrl
|
|
);
|
|
});
|
|
|
|
router.get("/listLoginFilter", (req, res) => {
|
|
const domain = req.hostname.split(".").pop();
|
|
|
|
let language;
|
|
|
|
switch (domain) {
|
|
case "br":
|
|
language = 0;
|
|
break;
|
|
case "py":
|
|
language = 1;
|
|
break;
|
|
default:
|
|
language = 0;
|
|
break;
|
|
}
|
|
|
|
execSQLQuery(
|
|
`SELECT
|
|
*
|
|
FROM units
|
|
WHERE enabled = 1 and idcountry = ${language}
|
|
ORDER BY description ASC;`,
|
|
res,
|
|
req.originalUrl
|
|
);
|
|
});
|
|
|
|
router.get("/getUnit/:idunits", verifyJWT, (req, res) => {
|
|
const idunits = parseInt(req.params.idunits);
|
|
execSQLQuery(
|
|
`SELECT
|
|
*
|
|
FROM units
|
|
WHERE idunits = ${idunits};`,
|
|
res,
|
|
req.originalUrl
|
|
);
|
|
});
|
|
|
|
router.get("/getUnitByAny/:field/:value", verifyJWT, (req, res) => {
|
|
const field = req.params.field;
|
|
const value = req.params.value;
|
|
execSQLQuery(
|
|
`SELECT
|
|
*
|
|
FROM units
|
|
WHERE ? = ? AND enabled = 1;`,
|
|
res,
|
|
req.originalUrl,
|
|
[field, value]
|
|
);
|
|
});
|
|
|
|
router.get("/disable/:idunits", verifyJWT, (req, res) => {
|
|
const idunits = parseInt(req.params.idunits);
|
|
execSQLQuery(
|
|
`UPDATE units SET
|
|
enabled = not enabled
|
|
WHERE idunits = ${idunits};`,
|
|
res,
|
|
req.originalUrl
|
|
);
|
|
});
|
|
|
|
router.post("/insert", verifyJWT, (req, res) => {
|
|
const description = req.body.description;
|
|
const unityname = req.body.unityname;
|
|
const address = req.body.address;
|
|
const neighborhood = req.body.neighborhood;
|
|
const idcities = parseInt(req.body.idcities);
|
|
const idstates = parseInt(req.body.idstates);
|
|
const cep = req.body.cep;
|
|
const phone = req.body.phone;
|
|
const celphone = req.body.celphone;
|
|
const cnpj = req.body.cnpj;
|
|
const stateregistration = req.body.stateregistration;
|
|
const municipalregistration = req.body.municipalregistration;
|
|
const manager = req.body.manager;
|
|
const email = req.body.email;
|
|
const enabled = req.body.enabled ? 1 : 0;
|
|
const reference_db = req.body.reference_db;
|
|
const ip_local = req.body.ip_local;
|
|
const ip_externo = req.body.ip_externo;
|
|
const porta_mysql = req.body.porta_mysql;
|
|
const codello = req.body.codello;
|
|
const idcountry = req.body.idcountry;
|
|
execSQLQuery(
|
|
`INSERT INTO units (description, unityname, address, neighborhood, idcities, idstates, cep, phone, celphone, cnpj, stateregistration, municipalregistration, manager, email, enabled, reference_db, ip_local, ip_externo, porta_mysql, codello, idcountry)
|
|
VALUES (?, ?, ?, ?, ? , ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);`,
|
|
res,
|
|
req.originalUrl,
|
|
[
|
|
description,
|
|
unityname,
|
|
address,
|
|
neighborhood,
|
|
idcities,
|
|
idstates,
|
|
cep,
|
|
phone,
|
|
celphone,
|
|
cnpj,
|
|
stateregistration,
|
|
municipalregistration,
|
|
manager,
|
|
email,
|
|
enabled,
|
|
reference_db,
|
|
ip_local,
|
|
ip_externo,
|
|
porta_mysql,
|
|
codello,
|
|
idcountry,
|
|
]
|
|
);
|
|
});
|
|
|
|
router.patch("/update", verifyJWT, (req, res) => {
|
|
const idunits = parseInt(req.body.idunits);
|
|
const description = req.body.description;
|
|
const unityname = req.body.unityname;
|
|
const address = req.body.address;
|
|
const neighborhood = req.body.neighborhood;
|
|
const idcities = parseInt(req.body.idcities);
|
|
const idstates = parseInt(req.body.idstates);
|
|
const cep = req.body.cep;
|
|
const phone = req.body.phone;
|
|
const celphone = req.body.celphone;
|
|
const cnpj = req.body.cnpj;
|
|
const stateregistration = req.body.stateregistration;
|
|
const municipalregistration = req.body.municipalregistration;
|
|
const manager = req.body.manager;
|
|
const email = req.body.email;
|
|
const enabled = req.body.enabled ? 1 : 0;
|
|
const reference_db = req.body.reference_db;
|
|
const ip_local = req.body.ip_local;
|
|
const ip_externo = req.body.ip_externo;
|
|
const porta_mysql = req.body.porta_mysql;
|
|
const codello = req.body.codello;
|
|
execSQLQuery(
|
|
`UPDATE units SET
|
|
description = ?,
|
|
unityname = ?,
|
|
address = ?,
|
|
neighborhood = ?,
|
|
idcities = ?,
|
|
idstates = ?,
|
|
cep = ?,
|
|
phone = ?,
|
|
celphone = ?,
|
|
cnpj = ?,
|
|
stateregistration = ?,
|
|
municipalregistration = ?,
|
|
manager = ?,
|
|
email = ?,
|
|
enabled = ?,
|
|
reference_db = ?,
|
|
ip_local = ?,
|
|
ip_externo = ?,
|
|
porta_mysql = ?,
|
|
codello = ?
|
|
WHERE idunits = ?;`,
|
|
res,
|
|
req.originalUrl,
|
|
[
|
|
description,
|
|
unityname,
|
|
address,
|
|
neighborhood,
|
|
idcities,
|
|
idstates,
|
|
cep,
|
|
phone,
|
|
celphone,
|
|
cnpj,
|
|
stateregistration,
|
|
municipalregistration,
|
|
manager,
|
|
email,
|
|
enabled,
|
|
reference_db,
|
|
ip_local,
|
|
ip_externo,
|
|
porta_mysql,
|
|
codello,
|
|
idunits,
|
|
]
|
|
);
|
|
});
|
|
|
|
router.get("/alarmUnits/:tk", (req, res) => {
|
|
alarmGetUnits(req, res, 1);
|
|
});
|
|
|
|
router.get("/alarmUnitsClassToday/:tk", (req, res) => {
|
|
alarmGetUnits(req, res, 2);
|
|
});
|
|
|
|
router.get("/getOnlineUsers/:tempo", verifyJWT, (req, res) => {
|
|
const Tempo = parseInt(req.params.tempo);
|
|
execSQLQuery(
|
|
`select
|
|
un.unityname as 'Unidade',
|
|
co.name as 'Curso',
|
|
cl.name as 'Aula',
|
|
cl.description as 'Pauta',
|
|
sc.idusers as 'Matricula',
|
|
sc.actualExercise,
|
|
sc.actualItem,
|
|
sc.actualAnswer,
|
|
case when sc.completedClass = 1 then 'Sim' else 'Não' end as 'Concluida',
|
|
date_format(sc.insert_date, '%d/%m/%Y %h:%i') as 'IniciadoEm',
|
|
date_format(sc.update_date, '%d/%m/%Y %h:%i') as 'VistoUltimo',
|
|
(select count(*) from stepclass sc1 where sc1.idunits = sc.idunits and sc1.insert_date > date_format(now(), '%y-%m-%d')) as 'AulasHoje',
|
|
(select count(*) from stepclass sc1 where sc1.insert_date > date_format(now(), '%y-%m-%d')) as 'TotalAulas'
|
|
from stepclass sc
|
|
inner join units un on un.idunits = sc.idunits
|
|
inner join classes cl on cl.idclasses = sc.idclasses
|
|
inner join courses co on co.idcourses = cl.idcourses
|
|
where sc.update_date > date_sub(now(), interval ${Tempo} minute)
|
|
order by sc.update_date desc;`,
|
|
res,
|
|
req.originalUrl
|
|
);
|
|
});
|
|
|
|
router.get("/getAlunosPorUnidade", verifyJWT, (req, res) => {
|
|
mysqlConnection.query(
|
|
`select * from units where enabled = 1 order by unityname asc;`,
|
|
function (error, Unidades, fields) {
|
|
if (!Unidades) Unidades = [];
|
|
let sqlQry = ``;
|
|
for (let i = 0; i < Unidades.length; i++) {
|
|
/*sqlQry += `select
|
|
${Unidades[i]['idunits']} as 'idunits',
|
|
'${Unidades[i]['unityname']}' as 'Unidade',
|
|
count(*) as 'TotalAlunos',
|
|
0 as 'AlunosOnline',
|
|
0 as 'AulasHoje'
|
|
from (select distinct
|
|
ma.IDMatricula,
|
|
cl.Nome,
|
|
tu.Nome as 'Turma',
|
|
case when pr.TipoRequerimento = 'C' then 'Sim' else 'Não' end as 'Cancelada'
|
|
from ${Unidades[i]['reference_db']}.dbo.Matriculas ma
|
|
inner join ${Unidades[i]['reference_db']}.dbo.MatriculasTreinandos mt on mt.IDMatricula = ma.IDMatricula
|
|
inner join ${Unidades[i]['reference_db']}.dbo.Turmas tu on tu.IdTurma = mt.CodigoTurma
|
|
inner join ${Unidades[i]['reference_db']}.dbo.Clientes cl on cl.IDCliente = mt.IDTreinando
|
|
inner join ${Unidades[i]['reference_db']}.dbo.TreinandosTurmas tt on tt.IDMatricula = mt.IDMatricula
|
|
inner join ${Unidades[i]['reference_db']}.dbo.Cursos cu on cu.Codigo = tt.IDCurso
|
|
inner join ${Unidades[i]['reference_db']}.dbo.Chamadas ch on ch.IDTurma = tu.IdTurma
|
|
left join ${Unidades[i]['reference_db']}.dbo.PreRequerimento pr on pr.IDTreinando = tt.IDTreinando
|
|
where tt.Faltoso = 0 and tt.Concluiu = 0 and tt.Desistente = 0 and tt.IDMatricula > 190008 and ma.QRCode != '' and tu.Cancelada = 0 and tu.Suspensa = 0) T_${i}
|
|
where T_${i}.Cancelada = 'Não'
|
|
UNION ALL `;*/
|
|
sqlQry += `select
|
|
${Unidades[i]["idunits"]} as 'idunits',
|
|
'${Unidades[i]["unityname"]}' as 'Unidade',
|
|
count(*) as 'TotalAlunos',
|
|
(select COUNT(*) as 'CalendarioEmDia'
|
|
from ${Unidades[i]["reference_db"]}.dbo.TreinandosTurmas TT
|
|
where TT.Desistente = 0 and TT.Faltoso = 0 and TT.CodigoTurma in (
|
|
select TC.IdTurma
|
|
from ${Unidades[i]["reference_db"]}.dbo.TurmasCalendario TC
|
|
where GETDATE() between (
|
|
select MIN(TC2.Data)
|
|
from ${Unidades[i]["reference_db"]}.dbo.TurmasCalendario TC2
|
|
where TC2.IdTurma = TC.IdTurma and TC2.CodigoCurso = TT.IDCurso)
|
|
and
|
|
(select MAX(TC3.Data)
|
|
from ${Unidades[i]["reference_db"]}.dbo.TurmasCalendario TC3
|
|
where TC3.IdTurma = TC.IdTurma and TC3.CodigoCurso = TT.IDCurso))) as 'CalendarioEmDia',
|
|
0 as 'AlunosOnline',
|
|
0 as 'AulasHoje'
|
|
from (
|
|
select
|
|
C.Nome
|
|
from ${Unidades[i]["reference_db"]}.dbo.Clientes C
|
|
inner join ${Unidades[i]["reference_db"]}.dbo.Unidades U on U.IDUnidade = C.IDUnidade
|
|
left join ${Unidades[i]["reference_db"]}.dbo.Profissoes P on P.IDProfissao = C.IDProfissao
|
|
left join ${Unidades[i]["reference_db"]}.dbo.Funcionarios F on F.IDFuncionario = C.FuncionarioCadastro
|
|
where (
|
|
((
|
|
--Cursos Trancados e Cancelados
|
|
select COUNT(*)
|
|
from ${Unidades[i]["reference_db"]}.dbo.TreinandosTurmas TT
|
|
where TT.IDTreinando = C.IDCliente and
|
|
(TT.CodigoTurma = 0 or TT.Desistente = 1)) <
|
|
(select COUNT(*)
|
|
from ${Unidades[i]["reference_db"]}.dbo.TreinandosTurmas TT2
|
|
where TT2.IDTreinando = C.IDCliente))) or
|
|
((select COUNT(*)
|
|
from ${Unidades[i]["reference_db"]}.dbo.TreinandosTurmas TT
|
|
where TT.IDTreinando = C.IDCliente and TT.Desistente = 0 and TT.Faltoso = 0 and
|
|
TT.CodigoTurma in (
|
|
select TC.IdTurma
|
|
from ${Unidades[i]["reference_db"]}.dbo.TurmasCalendario TC
|
|
where (
|
|
select MIN(TC2.Data)
|
|
from ${Unidades[i]["reference_db"]}.dbo.TurmasCalendario TC2
|
|
where TC2.IdTurma = TC.IdTurma and TC2.CodigoCurso = TT.IDCurso) > GETDATE())) > 0)
|
|
) resultado
|
|
union all
|
|
`;
|
|
}
|
|
sqlQry = sqlQry.substring(0, sqlQry.length - 30) + ";";
|
|
//console.log(sqlQry);
|
|
sqlServerConnection
|
|
.request()
|
|
.query(sqlQry, function (error1, results1, fileds1) {
|
|
res.send(
|
|
JSON.stringify({
|
|
status: 200,
|
|
error: error1,
|
|
response: results1.recordset,
|
|
})
|
|
);
|
|
console.log(
|
|
new Date().toUTCString() + " - " + req.originalUrl + "/ error 500"
|
|
);
|
|
});
|
|
}
|
|
);
|
|
});
|
|
|
|
router.get("/getAcompanhamentoAulas", verifyJWT, (req, res) => {
|
|
execSQLQuery(
|
|
`select distinct
|
|
co.name as 'Curso',
|
|
(select count(*) from classes cl where cl.idcourses = co.idcourses) as 'AulasCadastradas',
|
|
(select count(*) from classes cl inner join stepclass sc on sc.idclasses = cl.idclasses where cl.idcourses = co.idcourses) as 'VezesAssistida',
|
|
(select cl.name from classes cl inner join stepclass sc on sc.idclasses = cl.idclasses where cl.idcourses = co.idcourses and cl.download = 0 order by cl.ordem desc limit 1) as 'UltimaAssistida',
|
|
(select cl.description from classes cl inner join stepclass sc on sc.idclasses = cl.idclasses where cl.idcourses = co.idcourses and cl.download = 0 order by cl.ordem desc limit 1) as 'Tema',
|
|
(select un.unityname from classes cl inner join stepclass sc on sc.idclasses = cl.idclasses inner join units un on un.idunits = sc.idunits where cl.idcourses = co.idcourses and cl.download = 0 order by cl.ordem desc limit 1) as 'Unidade',
|
|
(select date_format(sc.insert_date, '%d/%m/%Y %H:%i') from classes cl inner join stepclass sc on sc.idclasses = cl.idclasses where cl.idcourses = co.idcourses and cl.download = 0 order by cl.ordem desc limit 1) as 'Data'
|
|
from courses co
|
|
inner join classes cl on cl.idcourses = co.idcourses
|
|
order by co.name asc;`,
|
|
res,
|
|
req.originalUrl
|
|
);
|
|
});
|
|
|
|
router.get("/unitscursoferias", (req, res) => {
|
|
mysqlConnection.query(
|
|
`select idunits, unityname from units where idunits in (21, 22, 23, 24, 31, 32, 35, 36, 38);`,
|
|
function (error, results, fields) {
|
|
res.send(
|
|
JSON.stringify({
|
|
status: 200,
|
|
error: error,
|
|
results: results,
|
|
})
|
|
);
|
|
console.log(req.originalUrl);
|
|
}
|
|
);
|
|
});
|
|
|
|
router.post("/captacaoferias", (req, res) => {
|
|
//console.log(req.body);
|
|
let idunits = req.body.unidade;
|
|
const nome = req.body.nome;
|
|
const telefone = req.body.telefone;
|
|
let minecraft = req.body.minecraft || "off";
|
|
let caneta3d = req.body.caneta3d || "off";
|
|
let cursos = "";
|
|
if (minecraft == "on" && caneta3d == "off") cursos = "16";
|
|
if (minecraft == "off" && caneta3d == "on") cursos = "17";
|
|
if (minecraft == "on" && caneta3d == "on") cursos = "16,17";
|
|
try {
|
|
idunits = idunits[0];
|
|
} catch (e) {
|
|
idunits = 14;
|
|
}
|
|
mysqlConnection.query(
|
|
`select * from units where idunits = ${idunits};`,
|
|
function (error, Unidade, fields) {
|
|
//console.log(Unidade);
|
|
Unidade = Unidade[0];
|
|
// Unidade['email'],
|
|
|
|
//#region EMAIL CAPTACAO
|
|
let TextoEmail = `
|
|
<div style="background-color:#f7f7f7">
|
|
<div style="margin:0px auto;max-width:600px">
|
|
<table align="center" border="0" cellpadding="0" cellspacing="0" role="presentation" style="width:100%">
|
|
<tbody>
|
|
<tr>
|
|
<td style="direction:ltr;font-size:0px;padding:10px;padding-left:40px;padding-right:40px;text-align:center"></td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
<div style="border:1px solid #d9d9d9;background:white;background-color:white;margin:0px auto;max-width:600px">
|
|
<table align="center" border="0" cellpadding="0" cellspacing="0" role="presentation" style="background:white;background-color:white;width:100%">
|
|
<tbody>
|
|
<tr>
|
|
<td style="direction:ltr;font-size:0px;padding:0px 0;padding-bottom:30px;text-align:center">
|
|
<div style="margin:0px auto;max-width:600px">
|
|
<table align="center" border="0" cellpadding="0" cellspacing="0" role="presentation" style="width:100%">
|
|
<tbody>
|
|
<tr>
|
|
<td style="direction:ltr;font-size:0px;padding:0;padding-left:0;padding-right:0;text-align:center">
|
|
<div class="m_-4945555513056369728mj-column-per-100" style="font-size:0px;text-align:left;direction:ltr;display:inline-block;vertical-align:top;width:100%">
|
|
<table border="0" cellpadding="0" cellspacing="0" role="presentation" style="vertical-align:top" width="100%">
|
|
<tbody>
|
|
<tr>
|
|
<td align="center" style="font-size:0px;padding:0 0;word-break:break-word">
|
|
<table border="0" cellpadding="0" cellspacing="0" role="presentation" style="border-collapse:collapse;border-spacing:0px">
|
|
<tbody>
|
|
<tr>
|
|
<td style="width:600px">
|
|
<img height="auto" src="https://myrobotlab.com.br/assets/images/CursoDeFerias/banner.png" style="border:0;display:block;outline:none;text-decoration:none;height:auto;width:100%;font-size:13px" width="600" class="CToWUd a6T" tabindex="0">
|
|
<div class="a6S" dir="ltr" style="opacity: 0.01; left: 677.5px; top: 180px;">
|
|
<div id=":173" class="T-I J-J5-Ji aQv T-I-ax7 L3 a5q" role="button" tabindex="0" aria-label="Fazer o download do anexo " data-tooltip-class="a1V" data-tooltip="Fazer o download">
|
|
<div class="aSK J-J5-Ji aYr"></div>
|
|
</div>
|
|
</div>
|
|
</td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
</td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
</td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
<div style="margin:0px auto;max-width:600px">
|
|
<table align="center" border="0" cellpadding="0" cellspacing="0" role="presentation" style="width:100%">
|
|
<tbody>
|
|
<tr>
|
|
<td style="direction:ltr;font-size:0px;padding:40px 40px 0 40px;padding-left:40px;padding-right:40px;text-align:center">
|
|
<div class="m_-4945555513056369728mj-column-per-100" style="font-size:0px;text-align:left;direction:ltr;display:inline-block;vertical-align:top;width:100%">
|
|
<table border="0" cellpadding="0" cellspacing="0" role="presentation" style="vertical-align:top" width="100%">
|
|
<tbody>
|
|
<tr>
|
|
<td align="center" style="font-size:0px;padding:0px;word-break:break-word">
|
|
<div style="font-family:sans-serif;font-size:22px;line-height:150%;text-align:center;color:#f46f36"><strong>Olá <span id="nomeGerente">${
|
|
Unidade.manager
|
|
}</span>!</strong>
|
|
</div>
|
|
</td>
|
|
</tr>
|
|
|
|
<tr>
|
|
<td align="center" style="font-size:0px;padding:0px;padding-bottom:20px;word-break:break-word">
|
|
<div style="font-family:sans-serif;font-size:16px;line-height:150%;text-align:center;color:#383838">
|
|
<strong>Sua unidade recebeu uma inscrição para o curso de férias.</strong>
|
|
<br>
|
|
<br>
|
|
<strong>Os seguintes cursos foram escolhidos:</strong>
|
|
</div>
|
|
</td>
|
|
</tr>
|
|
|
|
<tr>
|
|
<td align="center" style="font-size:0px;padding:0px;word-break:break-word">${
|
|
minecraft == "on"
|
|
? '<div id="minecraft" style="font-family:sans-serif;font-size:22px;line-height:150%;text-align:center;color:#f46f36;"><img src="https://myrobotlab.com.br/assets/images/CursoDeFerias/minecraft.png" alt="" style="vertical-align:middle;padding-right:5px;margin-top: -5px;" class="CToWUd"><strong><span style="margin-top: 10px;">Minecraft</span></strong></div>'
|
|
: ""
|
|
} ${
|
|
caneta3d == "on"
|
|
? '<div id="caneta3d" style="font-family:sans-serif;font-size:22px;line-height:150%;text-align:center;color:#f46f36;"><img src="https://myrobotlab.com.br/assets/images/CursoDeFerias/caneta.png" alt="" style="vertical-align:middle;padding-right:5px;margin-top: -5px;" class="CToWUd"><strong><span style="margin-top: 10px;">Caneta 3D</span></strong></div>'
|
|
: ""
|
|
}
|
|
</td>
|
|
</tr>
|
|
|
|
<tr>
|
|
<td align="center" style="font-size:0px;padding:0px;padding-bottom:20px;word-break:break-word">
|
|
<div style="font-family:sans-serif;font-size:16px;line-height:150%;text-align:center;color:#383838">
|
|
<br>
|
|
<strong>Aqui está os dados para contato com o aluno:</strong>
|
|
<br>
|
|
<br>
|
|
<div style="text-align: left;padding-left: 150px;"><strong><img src="https://myrobotlab.com.br/assets/images/CursoDeFerias/id.png" alt="" style="vertical-align:middle;padding-right:5px;margin-top: -5px;" class="CToWUd">Nome: <span id="nomeAluno">${nome}</span></strong></div>
|
|
<div style="text-align: left;padding-left: 150px;"><strong><img src="https://myrobotlab.com.br/assets/images/CursoDeFerias/phone.png" alt="" style="vertical-align:middle;padding-right:5px;margin-top: -5px;" class="CToWUd">Telefone: <span id="telefoneAluno">${telefone}</span></strong></div>
|
|
</div>
|
|
</td>
|
|
</tr>
|
|
|
|
<tr>
|
|
|
|
<td align="center" style="font-size:0px;padding:0px;word-break:break-word">
|
|
<hr style="width: 80%;">
|
|
<img style="width: 60%; margin-top: 10px;" src="https://myrobotlab.com.br/assets/images/CursoDeFerias/logo.png" alt="">
|
|
</td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
</td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
</td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
<div style="margin:0px auto;max-width:600px">
|
|
<table align="center" border="0" cellpadding="0" cellspacing="0" role="presentation" style="width:100%">
|
|
<tbody>
|
|
<tr>
|
|
<td style="direction:ltr;font-size:0px;padding:10px;padding-left:40px;padding-right:40px;text-align:center"></td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
</div>
|
|
`;
|
|
//#endregion
|
|
|
|
EnviarEmail(
|
|
Unidade["email"],
|
|
"Inscrição para curso de férias",
|
|
TextoEmail,
|
|
function (Sucesso) {
|
|
let Rota = "http://myrobot.com.br/cursodeferias?";
|
|
console.log(req.originalUrl);
|
|
if (Sucesso) {
|
|
Rota += "1";
|
|
mysqlConnection.query(
|
|
`insert into leadscursosferias (idunits, nome, telefone, cursos) values (?, ?, ?, ?);`,
|
|
[idunits, nome, telefone, cursos],
|
|
function (error, Unidade, fields) {
|
|
res.redirect(Rota);
|
|
}
|
|
);
|
|
} else {
|
|
Rota += "0";
|
|
res.redirect(Rota);
|
|
}
|
|
}
|
|
);
|
|
}
|
|
);
|
|
});
|
|
|
|
router.post("/captacaosite", (req, res) => {
|
|
const idunits = req.body.idunits;
|
|
const nome = req.body.nome;
|
|
const telefone = req.body.telefone;
|
|
const email = req.body.email;
|
|
mysqlConnection.query(
|
|
`select * from units where idunits = ${idunits};`,
|
|
function (error, Unidade, fields) {
|
|
Unidade = Unidade[0];
|
|
EnviarEmail(
|
|
Unidade["email"],
|
|
"Inscrição de interesse através do site",
|
|
"AQUI VAI IR O EMAIL PARA O FRANQUEADO COM AS INFORMAÇÕES DO LEAD CAPTURADO",
|
|
function (Sucesso) {
|
|
let Rota = "https://myrobot.com.br?";
|
|
console.log(req.originalUrl);
|
|
if (Sucesso) {
|
|
Rota += "1";
|
|
mysqlConnection.query(
|
|
`insert into leadssite (nome, telefone, email, idunits) values (?, ?, ?, ?);`,
|
|
[nome, telefone, email, idunits],
|
|
function (error, Lead, fields) {
|
|
res.redirect(Rota);
|
|
}
|
|
);
|
|
} else {
|
|
Rota += "0";
|
|
res.redirect(Rota);
|
|
}
|
|
}
|
|
);
|
|
}
|
|
);
|
|
});
|
|
|
|
function EnviarEmail(email, Assunto, Texto, callback) {
|
|
const request = require("request");
|
|
request.post(
|
|
"http://18.228.91.135:3337/api/email/send",
|
|
{
|
|
json: {
|
|
from: "MyRobot School <staff@myrobot.com.br>",
|
|
to: email,
|
|
subject: Assunto,
|
|
text: Texto,
|
|
},
|
|
},
|
|
function (error, response, body) {
|
|
if (!error && response.statusCode == 200) {
|
|
console.log("Sucesso ao enviar e-mail!");
|
|
callback(true);
|
|
} else {
|
|
console.log("Falha ao enviar e-mail!");
|
|
callback(false);
|
|
}
|
|
}
|
|
);
|
|
}
|
|
|
|
return router;
|
|
};
|
|
|
|
module.exports = routes;
|
|
|
|
function alarmGetUnits(req, res, type) {
|
|
var sqlQry = `select idunits, unityname,
|
|
reference_db, ip_externo, porta_mysql
|
|
from units
|
|
cross join config
|
|
where enabled = 1
|
|
and units.idunits > 14
|
|
and tk_api = '${req.params.tk}'`;
|
|
|
|
mysqlConnection.query(sqlQry, function (error, results, fields) {
|
|
if (!error && results.length != 0) {
|
|
if (type == 1) {
|
|
generateUnitsAlarmDate(results, res);
|
|
} else if (type == 2) {
|
|
alarmClassesToday(results, res);
|
|
}
|
|
} else {
|
|
console.log("Erro:");
|
|
console.log("Token Inválido");
|
|
res.status(500).send({ error: "Token Inválido" });
|
|
}
|
|
});
|
|
}
|
|
|
|
function alarmClassesToday(resultsUnits, res) {
|
|
var d = new Date(),
|
|
month = "" + (d.getMonth() + 1),
|
|
day = "" + d.getDate(),
|
|
year = d.getFullYear();
|
|
|
|
if (month.length < 2) month = "0" + month;
|
|
if (day.length < 2) day = "0" + day;
|
|
|
|
var dateSQL = [year, month, day].join("-");
|
|
|
|
var sqlQry = "";
|
|
|
|
for (var i = 0; i < resultsUnits.length; i++) {
|
|
var database = resultsUnits[i]["reference_db"] + ".dbo.";
|
|
|
|
var sqlQry =
|
|
sqlQry +
|
|
`SELECT DISTINCT Y.*,
|
|
CASE
|
|
WHEN DiaDaSemana = 1 THEN 'DOM'
|
|
WHEN DiaDaSemana = 2 THEN 'SEG'
|
|
WHEN DiaDaSemana = 3 THEN 'TER'
|
|
WHEN DiaDaSemana = 4 THEN 'QUA'
|
|
WHEN DiaDaSemana = 5 THEN 'QUI'
|
|
WHEN DiaDaSemana = 6 THEN 'SEX'
|
|
WHEN DiaDaSemana = 7 THEN 'SAB'
|
|
ELSE
|
|
''
|
|
END AS DiaSemana,
|
|
Y.NUMEROVAGAS - Y.VAGASOCUPADAS AS VagasDisponiveis,
|
|
${resultsUnits[i]["idunits"]} as MySQLUnitID,
|
|
'${resultsUnits[i]["unityname"]}' as MySQLUnitName
|
|
FROM (
|
|
SELECT DISTINCT Z.* FROM (
|
|
SELECT DISTINCT convert(varchar(10), TC.Data, 120) as Data,
|
|
convert(varchar(05), TC.HoraInicial, 108) as HoraInicial,
|
|
convert(varchar(05), TC.HoraFinal, 108) as HoraFinal,
|
|
T.Codigo AS CodigoTurma, T.NOME as DescricaoTurma, TC.CodigoCurso,
|
|
C.NomeVersao AS DescricaoCurso, TC.CodigoInstrutor, F.Nome as NomeInstrutor,
|
|
TC.CodigoSala, S.Descricao AS DescricaoSala, T.NumeroVagas, T.TipoTurma,
|
|
DATEPART(weekday, TC.Data) as DiaDaSemana,
|
|
coalesce((
|
|
SELECT COUNT(*)
|
|
FROM ${database}TreinandosTurmas TT
|
|
WHERE TT.CODIGOTURMA = T.IDTURMA
|
|
AND TT.IdCurso = TC.CodigoCurso
|
|
AND TT.Faltoso = 0
|
|
AND TT.IDMatricula > 190008
|
|
),0) + coalesce((
|
|
SELECT COUNT(*)
|
|
FROM ${database}ReservaVagas RV
|
|
WHERE RV.IDTurma = T.IDTurma
|
|
AND RV.IDCurso = TC.CodigoCurso
|
|
AND RV.Concluida = 0
|
|
AND RV.ReservaFlex = 0
|
|
),0) as VagasOcupadas,
|
|
TC.DATA AS DATAC, T.BloqueadaVenda, 0 AS IdPlantaoReposicao, T.IDTurma,
|
|
coalesce(
|
|
(
|
|
SELECT COUNT(*)
|
|
FROM ${database}ReservaVagas RV
|
|
WHERE RV.IDTurma = T.IDTurma
|
|
AND RV.IDCurso = TC.CodigoCurso
|
|
AND RV.Concluida = 0
|
|
AND RV.ReservaFlex = 0
|
|
),0) as QTDReservas
|
|
from ${database}Turmas T
|
|
INNER JOIN ${database}TurmasCalendario TC
|
|
on TC.IdTurma = T.IdTurma
|
|
INNER JOIN ${database}SALAS S
|
|
on S.Codigo = TC.CodigoSala
|
|
INNER JOIN ${database}Funcionarios F
|
|
ON F.IDFuncionario = TC.CodigoInstrutor
|
|
INNER JOIN ${database}Cursos C
|
|
ON C.Codigo = TC.CodigoCurso
|
|
WHERE T.TipoTurma <> 'F'
|
|
AND TC.DATA BETWEEN '${dateSQL}' AND '${dateSQL}'
|
|
AND CONVERT(CHAR(5), TC.HoraInicial, 108)
|
|
BETWEEN '00:00' AND '23:59'
|
|
AND CONVERT(CHAR(5), TC.HoraFinal, 108) BETWEEN '00:00' AND '23:59'
|
|
AND T.TipoTurma IN ('R','T','E','P','L','C','O')
|
|
|
|
UNION
|
|
|
|
SELECT Data, HoraInicial, HoraFinal, CodigoTurma, DescricaoTurma, CodigoCurso, DescricaoCurso,
|
|
CodigoInstrutor, NomeInstrutor, CodigoSala, DescricaoSala, NumeroVagas, TipoTurma, DiaDaSemana,
|
|
((
|
|
select count(*)
|
|
from (
|
|
select distinct TCV.IDTurma
|
|
from ${database}TurmasCalendario TCV
|
|
inner join ${database}Turmas TV
|
|
on TV.IdTurma = TCV.IdTurma
|
|
inner join ${database}TreinandosTurmas TTV
|
|
on TTV.CodigoTurma = TCV.IdTurma
|
|
and TTV.IDCurso = TCV.CodigoCurso
|
|
where TCV.Data = X.Data
|
|
and TCV.CodigoInstrutor = X.CodigoInstrutor
|
|
and (
|
|
TCV.HoraInicial between X.HoraInicial and dateadd(minute,-1,X.HoraFinal)
|
|
or TCV.HoraFinal between dateadd(minute,1,X.HoraInicial) and X.HoraFinal
|
|
)
|
|
AND TV.TipoTurma = 'F'
|
|
AND TTV.IDMatricula > 190008
|
|
) as L
|
|
) + (coalesce(
|
|
(
|
|
SELECT COUNT(*)
|
|
FROM ${database}ReservaVagas RV
|
|
WHERE (
|
|
RV.HoraInicioFlex between convert(time(0),X.HoraInicial) and convert(time(0),dateadd(minute,-1,X.HoraFinal))
|
|
OR RV.HoraTerminoFlex between convert(time(0),dateadd(minute,1,X.HoraInicial)) and convert(time(0),X.HoraFinal)
|
|
)
|
|
AND RV.IDProfessorFlex = X.CodigoInstrutor
|
|
AND RV.Concluida = 0
|
|
AND RV.ReservaFlex = 1
|
|
),0)
|
|
)
|
|
) as VagasOcupadas, DataC, BloqueadaVenda,
|
|
IdPlantaoReposicao, IDTurma, QTDReservas
|
|
FROM (
|
|
SELECT convert(varchar(10), TC.Data, 120) as Data,
|
|
convert(varchar(05), TC.HoraInicial, 108) as HoraInicial,
|
|
convert(varchar(05), TC.HoraFinal, 108) as HoraFinal,
|
|
'FLEX' as CodigoTurma, 'FLEX' as DescricaoTurma,
|
|
'' as CodigoCurso, 'FLEX' as DescricaoCurso, TC.CodigoInstrutor,
|
|
F.Nome as NomeInstrutor, TC.CodigoSala, S.Descricao AS DescricaoSala,
|
|
S.NumMicros as NumeroVagas, T.TipoTurma, DATEPART(weekday, TC.Data) as DiaDaSemana,
|
|
(
|
|
coalesce((
|
|
SELECT COUNT(*)
|
|
FROM ${database}TreinandosTurmas TT
|
|
WHERE TT.CODIGOTURMA = T.IDTURMA
|
|
AND TT.IdCurso = TC.CodigoCurso and TT.Faltoso = 0
|
|
AND TT.IDMatricula > 190008
|
|
),0) +
|
|
coalesce(
|
|
(
|
|
SELECT COUNT(*)
|
|
FROM ${database}ReservaVagas RV
|
|
WHERE (
|
|
RV.HoraInicioFlex between convert(time(0),TC.HoraInicial) and convert(time(0),dateadd(minute,-1,TC.HoraFinal))
|
|
OR RV.HoraTerminoFlex between convert(time(0),dateadd(minute,1,TC.HoraInicial)) and convert(time(0),TC.HoraFinal)
|
|
)
|
|
AND RV.IDProfessorFlex = TC.CodigoInstrutor
|
|
AND RV.Concluida = 0
|
|
AND RV.ReservaFlex = 1
|
|
)
|
|
,0)
|
|
) as VagasOcupadas, TC.DATA AS DATAC, T.BloqueadaVenda, 0 AS IdPlantaoReposicao, 0 AS IDTurma,
|
|
coalesce(
|
|
(
|
|
SELECT COUNT(*)
|
|
FROM ${database}ReservaVagas RV
|
|
WHERE (
|
|
RV.HoraInicioFlex between convert(time(0),TC.HoraInicial) and convert(time(0),dateadd(minute,-1,TC.HoraFinal))
|
|
OR RV.HoraTerminoFlex between convert(time(0),dateadd(minute,1,TC.HoraInicial)) and convert(time(0),TC.HoraFinal)
|
|
)
|
|
AND RV.IDProfessorFlex = TC.CodigoInstrutor
|
|
AND RV.Concluida = 0
|
|
AND RV.ReservaFlex = 1
|
|
)
|
|
,0) as QTDReservas
|
|
from ${database}Turmas T
|
|
INNER JOIN ${database}TurmasCalendario TC
|
|
on TC.IdTurma = T.IdTurma
|
|
INNER JOIN ${database}SALAS S
|
|
on S.Codigo = TC.CodigoSala
|
|
INNER JOIN ${database}Funcionarios F
|
|
ON F.IDFuncionario = TC.CodigoInstrutor
|
|
INNER JOIN ${database}Cursos C
|
|
ON C.Codigo = TC.CodigoCurso
|
|
WHERE TC.DATA BETWEEN '${dateSQL}' AND '${dateSQL}'
|
|
AND CONVERT(CHAR(5), TC.HoraInicial, 108) BETWEEN '00:00' AND '23:59'
|
|
AND CONVERT(CHAR(5), TC.HoraFinal, 108) BETWEEN '00:00' AND '23:59' AND T.TipoTurma = 'F'
|
|
) AS X
|
|
GROUP BY Data, HoraInicial, HoraFinal, CodigoTurma, DescricaoTurma, CodigoCurso, DescricaoCurso,
|
|
CodigoInstrutor, NomeInstrutor, CodigoSala, DescricaoSala, TipoTurma, DiaDaSemana, DataC, NumeroVagas,
|
|
BloqueadaVenda, IdPlantaoReposicao, IdTurma, QTDReservas
|
|
|
|
UNION
|
|
|
|
SELECT DISTINCT convert(varchar(10), P.Data, 120) as Data, convert(varchar(05), P.HoraInicial, 108) as HoraInicial,
|
|
convert(varchar(05), P.HoraFinal, 108) as HoraFinal, '' as CodigoTurma, coalesce(P.Descricao,'') as DescricaoTurma,
|
|
P.IdTipoCursoExtra as CodigoCurso, AE.Descricao as DescricaoCurso, P.IdInstrutor as CodigoInstrutor,
|
|
F.Nome as NomeInstrutor, P.IdSala as CodigoSala, S.Descricao AS DescricaoSala, P.Vagas as NumeroVagas,
|
|
'-' as TipoTurma, DATEPART(weekday, P.Data) as DiaDaSemana,
|
|
(
|
|
select COUNT(*)
|
|
from ${database}PlantaoReposicaoDetalhe PRD
|
|
where PRD.IdPlantaoReposicao = P.IDPlantaoReposicao
|
|
) as VagasOcupadas,
|
|
P.DATA AS DATAC, 0 as BloqueadaVenda, P.IdPlantaoReposicao,
|
|
0 as IDTurma, 0 as QTDReservas
|
|
FROM ${database}PlantaoReposicao P
|
|
left JOIN ${database}PlantaoReposicaoDetalhe PR
|
|
on PR.IdPlantaoReposicao = P.IdPlantaoReposicao
|
|
INNER JOIN ${database}TiposAulasExtras AE
|
|
on AE.IDTipoAulaExtra = P.IdTipoCursoExtra
|
|
INNER JOIN ${database}SALAS S
|
|
on S.Codigo = P.IDSala
|
|
INNER JOIN ${database}Funcionarios F
|
|
ON F.IDFuncionario = P.IDInstrutor
|
|
WHERE P.DATA BETWEEN '${dateSQL}' AND '${dateSQL}'
|
|
AND CONVERT(CHAR(5), P.HoraInicial, 108) BETWEEN '00:00' AND '23:59'
|
|
AND CONVERT(CHAR(5), P.HoraFinal, 108) BETWEEN '00:00' AND '23:59'
|
|
) AS Z WHERE 1 = 1
|
|
) AS Y`;
|
|
|
|
if (i < resultsUnits.length - 1) {
|
|
sqlQry = sqlQry + " union all ";
|
|
} else {
|
|
sqlQry = sqlQry + " ORDER BY Data";
|
|
}
|
|
}
|
|
|
|
sqlServerConnection
|
|
.request()
|
|
.query(sqlQry, function (error, results, fields) {
|
|
if (!error && results.length != 0) {
|
|
res.send(
|
|
JSON.stringify({
|
|
status: 200,
|
|
error: null,
|
|
response: results.recordsets[0],
|
|
})
|
|
);
|
|
} else {
|
|
console.log("Erro:");
|
|
console.log(error);
|
|
}
|
|
});
|
|
}
|
|
|
|
function generateUnitsAlarmDate(resultsUnits, res) {
|
|
var sqlQry = "";
|
|
|
|
for (var i = 0; i < resultsUnits.length; i++) {
|
|
var database = resultsUnits[i]["reference_db"] + ".dbo.";
|
|
|
|
sqlQry =
|
|
sqlQry +
|
|
`select R.*
|
|
from (
|
|
select
|
|
(
|
|
select count(*)
|
|
from ${database}Chamadas CH
|
|
inner join ${database}ChamadasDetalhes CD
|
|
on CD.IDChamadas = CH.IDChamadas
|
|
where CH.IDTurma = TT.CodigoTurma
|
|
and CH.IDCurso = TT.IDCurso
|
|
and CD.Presente = 1
|
|
) as QTDChamadas,
|
|
TT.IDMatricula,
|
|
A.Nome as Aluno,
|
|
CUR.NomeVersao as Curso,
|
|
CUR.Codigo as IDCurso,
|
|
convert(varchar(10),(
|
|
select MIN(TC.Data) as Data
|
|
from ${database}TurmasCalendario TC
|
|
where TC.Ministrado = 0
|
|
and TC.IdTurma = TT.CodigoTurma
|
|
and TC.Data >= convert(date,getdate(),121)
|
|
),103) as DataProximaAula,
|
|
TD.Ordem,
|
|
${resultsUnits[i]["idunits"]} as MySQLUnitID,
|
|
'${resultsUnits[i]["unityname"]}' as MySQLUnitName,
|
|
'${resultsUnits[i]["ip_externo"]}' as MySQLIPExterno,
|
|
'${resultsUnits[i]["porta_mysql"]}' as MySQLPort
|
|
from ${database}TreinandosTurmas TT
|
|
inner join ${database}Cursos CUR
|
|
on CUR.Codigo = TT.IDCurso
|
|
inner join ${database}Clientes A
|
|
on A.IDCliente = TT.IDTreinando
|
|
inner join ${database}TurmasDetalhe TD
|
|
on TD.IDTurma = TT.CodigoTurma
|
|
and TD.CodigoCurso = TT.IDCurso
|
|
where TT.IDMatricula > 190008
|
|
and TT.Desistente = 0
|
|
and CUR.CH_ProgTrein > (
|
|
select sum(datediff(HOUR,CH.HoraInicio,CH.HoraTermino))
|
|
from ${database}Chamadas CH
|
|
inner join ${database}ChamadasDetalhes CD
|
|
on CD.IDChamadas = CH.IDChamadas
|
|
where CH.IDTurma = TT.CodigoTurma
|
|
and CH.IDCurso = TT.IDCurso
|
|
)
|
|
) R
|
|
inner join (
|
|
select TT.IDMatricula,
|
|
min(TD.Ordem) as Ordem
|
|
from ${database}TreinandosTurmas TT
|
|
inner join ${database}Cursos CUR
|
|
on CUR.Codigo = TT.IDCurso
|
|
inner join ${database}Clientes A
|
|
on A.IDCliente = TT.IDTreinando
|
|
inner join ${database}TurmasDetalhe TD
|
|
on TD.IDTurma = TT.CodigoTurma
|
|
and TD.CodigoCurso = TT.IDCurso
|
|
where TT.IDMatricula > 190008
|
|
and TT.Desistente = 0
|
|
and CUR.CH_ProgTrein > (
|
|
select sum(datediff(HOUR,CH.HoraInicio,CH.HoraTermino))
|
|
from ${database}Chamadas CH
|
|
inner join ${database}ChamadasDetalhes CD
|
|
on CD.IDChamadas = CH.IDChamadas
|
|
where CH.IDTurma = TT.CodigoTurma
|
|
and CH.IDCurso = TT.IDCurso
|
|
)
|
|
group by IDMatricula
|
|
) S
|
|
on R.IDMatricula = S.IDMatricula
|
|
and R.Ordem = S.Ordem`;
|
|
|
|
if (i < resultsUnits.length - 1) {
|
|
sqlQry = sqlQry + " union all ";
|
|
} else {
|
|
sqlQry = sqlQry + " order by MySQLUnitID asc, QTDChamadas desc";
|
|
}
|
|
}
|
|
|
|
sqlServerConnection
|
|
.request()
|
|
.query(sqlQry, function (error, results, fields) {
|
|
if (!error && results.length != 0) {
|
|
ApplyMysqlData(results, res);
|
|
} else {
|
|
//res.send('Funcionário não tem acesso a "Inclusão de Controle de Chamadas"');
|
|
console.log("Erro:");
|
|
console.log(error);
|
|
}
|
|
});
|
|
}
|
|
|
|
function ApplyMysqlData(
|
|
results,
|
|
res,
|
|
returnMysql = null,
|
|
lastPos = 0,
|
|
lastUnit = 0,
|
|
arrCrashedUnits = []
|
|
) {
|
|
try {
|
|
//console.log("MysqlUnit: " + results.recordsets[0][lastPos]['MySQLUnitID'])
|
|
|
|
if (
|
|
arrCrashedUnits.indexOf(results.recordsets[0][lastPos]["MySQLUnitID"]) ==
|
|
-1
|
|
) {
|
|
//Se a conexão com a unidade não falhou (anteriormente)
|
|
if (results.recordsets[0][lastPos]["MySQLUnitID"] !== lastUnit) {
|
|
//Se a unidade não é mesma que a anterior faz a pesquisa no MySQL
|
|
lastUnit = results.recordsets[0][lastPos]["MySQLUnitID"];
|
|
|
|
var sqlQry2 = `select tpc.idcourses, count(c.idclasses) as QTDAulasCadastradas
|
|
from traineeprogramcourses tpc
|
|
inner join classes c
|
|
on c.idcourses = tpc.idcourses
|
|
where c.disabled = 0
|
|
group by tpc.idcourses`;
|
|
|
|
//console.log(sqlQry2);
|
|
|
|
var mysqlReference = require("mysql");
|
|
|
|
var DadosConnection = {
|
|
host: results.recordsets[0][lastPos]["MySQLIPExterno"],
|
|
user: "myrobot",
|
|
password: "myrobot2019",
|
|
database: Variables("database_mysql"),
|
|
port: results.recordsets[0][lastPos]["MySQLPort"],
|
|
};
|
|
|
|
mysqlLocal2 = mysqlReference.createConnection(DadosConnection);
|
|
|
|
mysqlLocal2.query(sqlQry2, function (error2, results2, fields2) {
|
|
if (!error2 && results2.length != 0) {
|
|
//Caso a conexão tenha sido correta e encontrado resultados
|
|
for (var i = 0; i < results2.length; i++) {
|
|
if (
|
|
results2[i]["idcourses"] ==
|
|
results.recordsets[0][lastPos]["IDCurso"]
|
|
) {
|
|
results.recordsets[0][lastPos]["QTDAulasCadastradas"] =
|
|
results2[i]["QTDAulasCadastradas"];
|
|
}
|
|
}
|
|
|
|
if (lastPos < results.recordsets[0].length - 1) {
|
|
ApplyMysqlData(
|
|
results,
|
|
res,
|
|
results2,
|
|
lastPos + 1,
|
|
lastUnit,
|
|
arrCrashedUnits
|
|
);
|
|
} else {
|
|
console.log("Consulta Alunos Controle");
|
|
|
|
res.send(
|
|
JSON.stringify({
|
|
status: 200,
|
|
error: null,
|
|
response: results.recordsets[0],
|
|
})
|
|
);
|
|
}
|
|
} else {
|
|
//Caso a conexão tenha dados erro ou não encontrou resultados
|
|
console.log(
|
|
"Erro ao Acessar Banco de Dados: " +
|
|
results.recordsets[0][lastPos]["MySQLUnitName"]
|
|
);
|
|
//console.log(DadosConnection);
|
|
//console.log(error2);
|
|
|
|
//Adiciona a unidades crashadas
|
|
arrCrashedUnits.push(lastUnit);
|
|
|
|
results.recordsets[0][lastPos]["QTDAulasCadastradas"] = null;
|
|
|
|
if (lastPos < results.recordsets[0].length - 1) {
|
|
ApplyMysqlData(
|
|
results,
|
|
res,
|
|
results2,
|
|
lastPos + 1,
|
|
lastUnit,
|
|
arrCrashedUnits
|
|
);
|
|
} else {
|
|
console.log("Consulta Alunos Controle");
|
|
|
|
res.send(
|
|
JSON.stringify({
|
|
status: 200,
|
|
error: null,
|
|
response: results.recordsets[0],
|
|
})
|
|
);
|
|
}
|
|
}
|
|
});
|
|
} else {
|
|
//Se o resultado já foi encontrado antes
|
|
for (var i = 0; i < returnMysql.length; i++) {
|
|
if (
|
|
returnMysql[i]["idcourses"] ==
|
|
results.recordsets[0][lastPos]["IDCurso"]
|
|
) {
|
|
results.recordsets[0][lastPos]["QTDAulasCadastradas"] =
|
|
returnMysql[i]["QTDAulasCadastradas"];
|
|
}
|
|
}
|
|
|
|
if (lastPos < results.recordsets[0].length - 1) {
|
|
ApplyMysqlData(
|
|
results,
|
|
res,
|
|
returnMysql,
|
|
lastPos + 1,
|
|
lastUnit,
|
|
arrCrashedUnits
|
|
);
|
|
} else {
|
|
console.log("Consulta Alunos Controle");
|
|
|
|
res.send(
|
|
JSON.stringify({
|
|
status: 200,
|
|
error: null,
|
|
response: results.recordsets[0],
|
|
})
|
|
);
|
|
}
|
|
}
|
|
} else {
|
|
results.recordsets[0][lastPos]["QTDAulasCadastradas"] = null;
|
|
|
|
//Se a conexão com o mysql falhou a unidade está dentro de crashadas
|
|
if (lastPos < results.recordsets[0].length - 1) {
|
|
ApplyMysqlData(
|
|
results,
|
|
res,
|
|
null,
|
|
lastPos + 1,
|
|
lastUnit,
|
|
arrCrashedUnits
|
|
);
|
|
} else {
|
|
res.send(
|
|
JSON.stringify({
|
|
status: 200,
|
|
error: null,
|
|
response: results.recordsets[0],
|
|
})
|
|
);
|
|
}
|
|
}
|
|
} catch (err) {
|
|
console.log("Erro de Consulta no MySQL");
|
|
console.log(err);
|
|
res.send(
|
|
JSON.stringify({
|
|
status: 500,
|
|
error: "Erro Geral",
|
|
response: "",
|
|
})
|
|
);
|
|
}
|
|
}
|