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&aacute; <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: "",
      })
    );
  }
}