require('../connections/mysql-server');
require('../connections/sql-server');
require('../helpers/CheckToken');
require('../helpers/config');
const express = require('express');
const router = express.Router();
const bodyParser = require('body-parser');

router.use(bodyParser.urlencoded({ extended: true }));
router.use(bodyParser.json());

var routes = function () {
  router.get('/checkFranchiseePortal', (req, res) => {
    execSQLQuery(
      `SELECT
      franchiseeportal
    FROM config;`,
      res,
      req.originalUrl
    );
  });

  router.get('/changeFranchiseePortal/:estado', (req, res) => {
    const estado = req.params.estado ? 1 : 0;
    execSQLQuery(
      `UPDATE config SET
      franchiseeportal = ${estado}
    WHERE idconfig = 1;`,
      res,
      req.originalUrl
    );
  });

  router.get('/checkVersionApp', (req, res) => {
    execSQLQuery(
      `SELECT
      versionapp
    FROM config;`,
      res,
      req.originalUrl
    );
  });

  router.get('/checkVersionRobot/:idrobot', (req, res) => {
    let idrobot = req.params.idrobot;
    let Caminho = Variables('src_media') + 'downloads/';
    let fs = require('fs');
    if (idrobot == 0) Caminho += 'ZoeRobot';
    else if (idrobot == 1) Caminho += 'MiniRobot';
    fs.readFile(Caminho + '/versao.txt', 'utf8', (err, data) => {
      if (err) throw err;
      console.log(
        new Date().toUTCString() + ' - ' + req.originalUrl + ' success 200'
      );
      res.send(data);
    });
    //execSQLQuery('SELECT versionrobot, messagerobot FROM config;', res, req.originalUrl);
  });

  router.get('/getZoeRobots', (req, res) => {
    mysqlConnection.query(
      `SELECT * FROM zoerobots;`,
      function (error, results, fields) {
        console.log(
          new Date().toUTCString() + ' - ' + req.originalUrl + ' success 200'
        );
        res.send(JSON.stringify(results));
      }
    );
  });

  router.get('/getZoeRobotsApks/:idzoerobots', (req, res) => {
    const idzoerobots = req.params.idzoerobots;
    mysqlConnection.query(
      `SELECT * FROM zoerobotsapks where idzoerobots = ${idzoerobots};`,
      function (error, results, fields) {
        console.log(
          new Date().toUTCString() + ' - ' + req.originalUrl + ' success 200'
        );
        res.send(`{"Aplicativos": ` + JSON.stringify(results) + `}`);
      }
    );
  });

  router.post('/insertZoeRobotsApks', (req, res) => {
    const idzoerobots = req.body.idzoerobots;
    const descricao = req.body.descricao;
    const versao = req.body.versao;
    const mensagem = req.body.mensagem;
    const packagename = req.body.packagename;
    const enderecoapk = req.body.enderecoapk;
    const nomeinstalador = req.body.nomeinstalador;
    const pasta = req.body.pasta;
    execSQLQuery(
      `insert into zoerobotsapks (idzoerobots, descricao, versao, mensagem, packagename, enderecoapk, nomeinstalador, pasta) values (?, ?, ?, ?, ?, ?, ?, ?);`,
      res,
      req.originalUrl,
      [
        idzoerobots,
        descricao,
        versao,
        mensagem,
        packagename,
        enderecoapk,
        nomeinstalador,
        pasta,
      ]
    );
  });

  router.delete('/deleteZoeRobotsApks/:idzoerobots', (req, res) => {
    const idzoerobots = req.params.idzoerobots;
    execSQLQuery(
      `delete from zoerobotsapks WHERE idzoerobots = ${idzoerobots};`,
      res,
      req.originalUrl
    );
  });

  router.post('/SetVersaoRobotFile/:idrobot', verifyJWT, (req, res) => {
    let Caminho = Variables('src_media') + 'downloads/';
    const fs = require('fs');
    const Json = JSON.stringify(req.body);
    const idrobot = parseInt(req.params.idrobot);
    if (idrobot == 0) Caminho += 'ZoeRobot';
    else if (idrobot == 1) Caminho += 'MiniRobot';
    fs.writeFile(Caminho + '/versao.txt', Json, (err) => {
      if (err) throw err;
      console.log(
        new Date().toUTCString() + ' - ' + req.originalUrl + ' success 200'
      );
      res.send(true);
    });
  });

  router.get('/checkRobotCourses', (req, res) => {
    execSQLQuery(
      `SELECT
      robotcourses
    FROM config;`,
      res,
      req.originalUrl
    );
  });

  router.post('/checkexistnotificationclose/:tk', verifyJWT, (req, res) => {
    let idteacher = req.body.idteacher;
    let db = req.body.db;
    execSQLQuery2(
      `
      SELECT
        IDNotificacoes,
        IDChamadaAPP
      FROM
        ${db}NotificacoesErroEnvioAPP
      WHERE
        IDInstrutor = ${idteacher}
      AND
        EnviarRespostaAPP = 1
      AND
        RespostaRecebidaAPP = 0
      AND
        IDChamadaAPP != NULL;`,
      res,
      req.originalUrl
    );
  });

  router.post('/notificationsteacher/:tk', verifyJWT, (req, res) => {
    let idteacher = req.body.idteacher;
    let idcall = req.body.idcall;
    let db = req.body.db;
    execSQLQuery2(
      `
    SELECT
      *
    FROM
      ${db}NotificacoesErroEnvioAPP
    WHERE
      IDInstrutor = ${idteacher}
    AND
      IDChamadaAPP = ${idcall};`,
      res,
      req.originalUrl
    );
  });

  router.post('/send/:tk', verifyJWT, (req, res) => {
    let db = req.body.db;
    let idteacher = req.body.idteacher;
    let description = req.body.desc;
    let date = req.body.date;
    let students = req.body.students;
    let content = req.body.content;
    var StudentsIDs = students.split(',');
    let Nomes = '';

    let sqlQry = `
      SELECT
        *
      FROM
        ${db}FuncionariosAcesso
      WHERE
        IDFuncionario = ${idteacher}
      AND
        DescricaoAcesso = '${description}'
      AND
        permitido = 1;`;
    //Check user have permission to this
    sqlServerConnection
      .request()
      .query(sqlQry, function (error, results, fields) {
        if (!error && results.length != 0) {
          //Check if date exist in TurmasCalendario
          CheckDateExists(
            db,
            date,
            idteacher,
            StudentsIDs,
            0,
            Nomes,
            res,
            content,
            null
          );
        } else {
          res.send(
            'Funcionário não tem acesso a "Inclusão de Controle de Chamadas"'
          );
        }
      });

    /*if(checkAccessEmployee(db, idteacher, description)) {
      var ResultTurmasCalendario = SeacrhCall(db, date, idteacher, idstudent);
      res.send(ResultTurmasCalendario);
    }
    else {
      res.send('Funcionário não tem acesso a "Inclusão de Controle de Chamadas"');
    }*/
  });

  router.post('/checkclass/:tk', verifyJWT, (req, res) => {
    let date = req.body.date;
    let idstudent = req.body.idstudent;
    let db = req.body.db;
    execSQLQuery2(
      `
      SELECT top(1) *
      FROM ${db}TurmasCalendario
      where IDTurma = ${idstudent}
      and Ministrado = 0
      order by Data asc;`,
      res,
      req.originalUrl
    );
  });

  router.post('/homeworkend', verifyJWT, (req, res) => {
    const homework = req.body.homework;
    const idstudent = parseInt(req.body.idstudent || req.body.idusers);
    const idunits = parseInt(req.body.idunits);
    const idclasses = parseInt(req.body.idclasses);

    const SQLQry = `UPDATE stepclass SET
      homework = (SELECT CASE WHEN r.qtde = 0 THEN 0 ELSE ${homework} END AS home FROM
      (SELECT COUNT(*) AS qtde FROM stepclass WHERE idusers = ${idstudent} AND idunits = ${idunits} AND idclasses = ${idclasses} AND STR_TO_DATE(datefinal, '%d/%m/%Y') > NOW()) r)
      WHERE idusers = ${idstudent} AND idunits = ${idunits} AND idclasses = ${idclasses};`;

    mysqlConnection.query(SQLQry, function (error, results, fields) {
      if (!error && results.length != 0) {
        res.send(200);
        console.log(
          new Date().toUTCString() + ' - ' + req.originalUrl + ' success 200'
        );
      } else {
        res.send(500);
        console.log(
          new Date().toUTCString() +
            ' - ' +
            req.originalUrl +
            ' fail 500 ' +
            error
        );
      }
    });
    InsertUserStage(idstudent, idunits, idclasses, homework);
    InsertNotes();
    //InsertNotasDetalhes(idstudent, idunits, idclasses);
  });

  router.get('/InsertNotes', (req, res) => {
    if (InsertNotes()) res.send({ error: null, response: false, status: 500 });
    else res.send({ error: null, response: true, status: 200 });
  });

  router.get('/verifyCorrection/:idunits', verifyJWT, (req, res) => {
    let idunits = req.params.idunits;
    execSQLQuery(
      `
    SELECT
	    idusers,
      idclasses,
      homework,
      datefinal
    FROM
	    stepclass
    WHERE
	    note IS NULL
    AND
	    completedClass = 1
    AND
	    idunits = ${idunits};`,
      res,
      req.originalUrl
    );
  });

  router.post('/listQRcodesByAny/:tk', verifyJWT, (req, res) => {
    var value = req.body.value;
    var db = req.body.db;
    execSQLQuery2(
      `
    SELECT
      c.Nome,
      mt.IDMatricula,
      t.IdTurma
    FROM
      ${db}Turmas t
    INNER JOIN
      ${db}MatriculasTreinandos mt
    ON
      t.IdTurma = mt.CodigoTurma
    INNER JOIN
      ${db}Clientes c
    ON
      c.IDCliente = mt.IDTreinando
    INNER JOIN
      ${db}Matriculas m
    ON
      m.IDMatricula = mt.IDMatricula
    WHERE
      m.QRCode = '${value}';`,
      res,
      req.originalUrl
    );
  });

  router.get('/linkStudent/:db/:idusers/:qrcode/:tk', (req, res) => {
    let db = req.params.db;
    let idusers = req.params.idusers;
    let qrcode = req.params.qrcode;
    let token = req.params.tk;

    if (token == Variables('tk_fixed')) {
      let sqlQry = `
      SELECT
        *
      FROM
        units
      WHERE
        reference_db = '${db}'
      AND
        enabled = 1;`;

      mysqlConnection.query(sqlQry, function (error, results, fields) {
        if (!error && results.length != 0) {
          var idunits = results[0]['idunits'];
          let sqlQry2 = `
          UPDATE
            qrcodes
          SET
            idusers = ${idusers},
            idunits = ${idunits}
          WHERE
            _key = '${qrcode}'
          AND
            idusers is null;`;

          execSQLQuery(sqlQry2, res, req.originalUrl);
        } else {
          res.send('not found');
        }
      });
    } else {
      res.status(500).send({ status: 'expired' });
    }
  });

  router.get('/linkBarCode/:db/:idusers/:_key/:idcourses/:tk', (req, res) => {
    let db = req.params.db;
    let idusers = req.params.idusers;
    let _key = req.params._key;
    let idcourses = req.params.idcourses;
    let token = req.params.tk;
    if (token == Variables('tk_fixed')) {
      let sqlQry = `
      SELECT
        *
      FROM
        units
      WHERE
        reference_db = '${db}'
      AND
        enabled = 1;`;

      mysqlConnection.query(sqlQry, function (error, results, fields) {
        if (!error && results.length != 0) {
          var idunits = results[0]['idunits'];
          let sqlQry2 = `
          UPDATE
            barcodes
          SET
            idusers = ${idusers},
            idunits = ${idunits},
            idcourses = ${idcourses}
          WHERE
            _key = '${_key}'
          AND
            idusers is null;`;

          execSQLQuery(sqlQry2, res, req.originalUrl);
        } else {
          res.send('not found');
        }
      });

      //execSQLQuery(sqlQry, res, req.originalUrl);
    } else {
      res.status(500).send({ status: 'expired' });
    }
  });

  router.get('/getCuriosities/:tk', verifyJWT, (req, res) => {
    execSQLQuery(`SELECT * FROM curiosities`, res, req.originalUrl);
  });

  router.get('/checkQR/:_key/:_token', (req, res) => {
    let _key = req.params._key;
    let token = req.params._token;

    if (token != Variables('tk_fixed')) {
      res.status(500).send({ status: 'expired' });
    } else {
      var sqlQry = `
      SELECT
        *
      FROM
        qrcodes
      WHERE
        _key = '${_key}';`;

      mysqlConnection.query(sqlQry, function (error, results, fields) {
        if (!error && results.length != 0) {
          var ordem = results[0]['ordem'];
          var idqrlotes = results[0]['idqrlotes'];
          var src =
            Variables('src_qrcodes') +
            idqrlotes +
            '/qr_' +
            idqrlotes +
            '_' +
            ordem +
            '.jpg';
          var cmd = '';
          if (results[0]['idusers']) cmd = 'N,';
          else cmd = 'S,';
          console.log("/checkQR/ success 200 - '" + cmd + src + "'");
          res.send(cmd + src);
        } else {
          res.status(500).send('QRCode não encontrado');
        }
      });
    }
  });

  router.get(
    '/isLastClass/:idusers/:idunits/:idclasses',
    verifyJWT,
    (req, res) => {
      let idusers = req.params.idusers;
      let idunits = req.params.idunits;
      let idclasses = req.params.idclasses;

      execSQLQuery(
        `update stepclass set
      homework = 1
    where (SELECT aval FROM classes WHERE idclasses = ${idclasses}) = 1
    and idusers = ${idusers} and idunits = ${idunits} and idclasses = ${idclasses};`,
        res,
        req.originalUrl
      );
      setTimeout(() => {
        InsertNotes();
        setTimeout(() => {
          InsertCourseNotes();
        }, 200);
      }, 500);
    }
  );

  router.get(
    '/checkHasStudentsToNotificate/:idteacher/:db',
    verifyJWT,
    (req, res) => {
      console.log('/checkHasStudentsToNotificate success 200');
      let idteacher = req.params.idteacher;
      let db = req.params.db;
      let sqlQuery = `
SELECT DISTINCT
  tc.IdTurma, tc.CodigoCurso, tt.IDMatricula
FROM
  ${db}TurmasCalendario tc
INNER JOIN
  ${db}TreinandosTurmas tt
ON
	tc.IdTurma = tt.CodigoTurma
AND
	tc.CodigoCurso = tt.IDCurso
  WHERE
    tc.Ministrado = 1
  AND
    tc.CodigoInstrutor = ${idteacher};
    `;
      sqlServerConnection
        .request()
        .query(sqlQuery, function (error, results, fields) {
          if (!error && results.length != 0) {
            //res.send(results);
            //console.log(results);
            indexStudents = 0;
            LoopStudentsOfTeacher(results, res, db, idteacher);
          } else {
            res.send(false);
          }
        });
    }
  );

  router.post('/insertScoregame/:tk', (req, res) => {
    let token = req.params.tk;
    if (token != Variables('tk_fixed')) {
      res.status(500).send({ status: 'expired' });
      return;
    }
    let cidade = '';
    let jogador = req.body.jogador;
    let pontuacao = req.body.pontuacao;
    let idgames = 1;
    execSQLQuery(
      `INSERT INTO scoregames (cidade, jogador, pontuacao, idgames) VALUES ('${cidade}', '${jogador}', ${pontuacao}, ${idgames});`,
      res,
      req.originalUrl
    );
  });

  router.post('/updateScoregame/:tk', (req, res) => {
    let token = req.params.tk;
    if (token != Variables('tk_fixed')) {
      res.status(500).send({ status: 'expired' });
      return;
    }
    let cidade = '';
    let jogador = req.body.jogador;
    let pontuacao = req.body.pontuacao;
    let idgames = 1;
    execSQLQuery(
      `UPDATE scoregames SET pontuacao = ${pontuacao} WHERE jogador = '${jogador}' AND idgames = ${idgames};`,
      res,
      req.originalUrl
    );
  });

  router.get('/listScoregame/:tk/:idgames', (req, res) => {
    let token = req.params.tk;
    let idgames = req.params.idgames;
    if (token != Variables('tk_fixed')) {
      res.status(500).send({ status: 'expired' });
      return;
    }
    execSQLQuery(
      `SELECT jogador, pontuacao FROM scoregames WHERE idgames = ${idgames} ORDER BY pontuacao ASC;`,
      res,
      req.originalUrl
    );
  });

  router.get('/InsertCourseNotes', (req, res) => {
    if (InsertCourseNotes())
      res.send({ error: null, response: false, status: 500 });
    else res.send({ error: null, response: true, status: 200 });
  });

  router.post('/DesativarAluno', (req, res) => {
    const idunits = parseInt(req.body.idunits);
    const idusers = parseInt(req.body.idusers);
    execSQLQuery(
      `insert into alunosdesativados (idunits, idusers) values (${idunits}, ${idusers});`,
      res,
      req.originalUrl
    );
  });

  router.get('/MudarAlunoDesativado/:idunits/:idusers', (req, res) => {
    const idunits = parseInt(req.params.idunits);
    const idusers = parseInt(req.params.idusers);
    execSQLQuery(
      `update alunosdesativados set
      desativado = !desativado
    where idunits = ${idunits} and idusers = ${idusers};`,
      res,
      req.originalUrl
    );
  });

  router.get('/MudarAlunoDesativadoP/:idunits/:idusers', (req, res) => {
    const idunits = parseInt(req.params.idunits);
    const idusers = parseInt(req.params.idusers);
    execSQLQuery(
      `update usershomework set
      disabled = !disabled
    where idunits = ${idunits} and idusers = ${idusers};`,
      res,
      req.originalUrl
    );
  });

  router.get('/InserirNotasSQLServer', (req, res) => {
    console.log(
      new Date().toUTCString() + ' - ' + req.originalUrl + ' - initing...'
    );
    let SqlQueryUnidades = `select
      idunits,
      unityname as 'Unidade',
      concat(reference_db, '.dbo.') as 'db'
    from units
    where enabled = 1
    order by unityname asc;`;
    mysqlConnection.query(SqlQueryUnidades, function (error, Unidades, fields) {
      PreencherNotasSQLServer(Unidades, 0, function (Unidades) {
        console.log(
          new Date().toUTCString() + ' - ' + req.originalUrl + ' - finished'
        );
        res.send(Unidades);
      });
    });
  });

  router.get('/InserirRespostasAlunos', (req, res) => {
    console.log(
      new Date().toUTCString() + ' - ' + req.originalUrl + ' - initing...'
    );
    let SqlQueryUnidades = `select
      idunits,
      unityname as 'Unidade',
      concat(reference_db, '.dbo.') as 'db'
    from units
    where enabled = 1
    order by unityname asc;`;
    mysqlConnection.query(SqlQueryUnidades, function (error, Unidades, fields) {
      let SqlQueryRespostas = `select distinct
        cc.idclasses,
        ea.idexerciseanswers
      from classescontent cc
      inner join exercisequestions eq on eq.idexercises = cc.idexercises
      inner join exerciseanswers ea on ea.idexercisequestions = eq.idexercisequestions
      where ea.iscorrect = 1;`;
      mysqlConnection.query(
        SqlQueryRespostas,
        function (error, Respostas, fields) {
          PreencherAulas(Unidades, Respostas, 0, function (Unidades) {
            console.log(
              new Date().toUTCString() + ' - ' + req.originalUrl + ' - finished'
            );
            res.send(Unidades);
          });
        }
      );
    });
  });

  function PreencherAulas(Unidades, Respostas, Index, callback) {
    if (Index < Unidades.length) {
      console.log(
        'Analisando unidade ' +
          (Index + 1) +
          ' de ' +
          Unidades.length +
          ' (' +
          Unidades[Index]['Unidade'] +
          ')'
      );
      Unidades[Index]['AulaAluno'] = [];
      let SqlQueryMySQL = `select * from (
        select
            sc.idusers,
            sc.idunits,
            sc.idclasses,
            sc.note,
            ExA.Questoes,
            count(ug.idusergivenanswers) as 'Respostas'
          from stepclass sc
          inner join (select distinct
              cl.idclasses,
              cl.name as 'Aula',
              count(cc.idexercises) as 'Questoes'
            from classes cl
            inner join courses co on co.idcourses = cl.idcourses
            inner join classescontent cc on cc.idclasses = cl.idclasses
            left join exercisequestions eq on eq.idexercises = cc.idexercises
            where co.idtypescourse in (1,2,3)
            group by cl.idclasses, cc.idclasses
            order by cl.idclasses asc) ExA on sc.idclasses = ExA.idclasses and ExA.Questoes > 0
          left join usergivenanswers ug on ug.idunits = sc.idunits and ug.idusers = sc.idusers and ug.idclasses = sc.idclasses
          where sc.idunits = ${Unidades[Index]['idunits']} and sc.completedClass = 1 and (now() >= date_add(convert(sc.dateinicial, date), interval 7 day) or sc.homework is not null)
          group by sc.idusers, sc.idunits, sc.idclasses) R
        where R.Respostas = 0;`;
      mysqlConnection.query(SqlQueryMySQL, function (error, AulaAluno, fields) {
        Unidades[Index]['AulaAluno'] = AulaAluno;
        if (AulaAluno && AulaAluno.length > 0) {
          Unidades[Index][
            'SQLRes'
          ] = `insert into usergivenanswers (idusergivenanswers, idusers, idexerciseanswers, date_answered, fixed, idclasses, idunits) values `;
          Unidades[Index]['SQLNot'] = `update stepclass set note = null where `;
        } else {
          Unidades[Index]['SQLRes'] = 'select 1;';
          Unidades[Index]['SQLNot'] = 'select 1   ;';
        }
        for (let i = 0; i < Unidades[Index]['AulaAluno'].length; i++) {
          Unidades[Index][
            'SQLNot'
          ] += `(idusers = ${Unidades[Index]['AulaAluno'][i]['idusers']} and idunits = ${Unidades[Index]['AulaAluno'][i]['idunits']} and idclasses = ${Unidades[Index]['AulaAluno'][i]['idclasses']}) or `;
          for (let o = 0; o < Respostas.length; o++) {
            if (
              Respostas[o].idclasses ==
              Unidades[Index]['AulaAluno'][i]['idclasses']
            ) {
              Unidades[Index][
                'SQLRes'
              ] += `((select ug1.idusergivenanswers + 1 from usergivenanswers ug1 order by ug1.idusergivenanswers desc limit 1), ${Unidades[Index]['AulaAluno'][i]['idusers']}, ${Respostas[o]['idexerciseanswers']}, '25/01/2021', 1, ${Unidades[Index]['AulaAluno'][i]['idclasses']}, ${Unidades[Index]['AulaAluno'][i]['idunits']}),`;
            }
          }
        }
        Unidades[Index]['SQLRes'] =
          Unidades[Index]['SQLRes'].substring(
            0,
            Unidades[Index]['SQLRes'].length - 1
          ) + ';';
        Unidades[Index]['SQLNot'] =
          Unidades[Index]['SQLNot'].substring(
            0,
            Unidades[Index]['SQLNot'].length - 4
          ) + ';';

        mysqlConnection.query(
          Unidades[Index]['SQLRes'],
          function (error, insRespostas, fields) {
            mysqlConnection.query(
              Unidades[Index]['SQLNot'],
              function (error, updNotas, fields) {
                Index++;
                PreencherAulas(Unidades, Respostas, Index, callback);
              }
            );
          }
        );
      });
    } else {
      callback(Unidades);
    }
  }

  router.get('/RemoverRespostasAlunos', (req, res) => {
    console.log(
      new Date().toUTCString() + ' - ' + req.originalUrl + ' - initing...'
    );
    let SqlQueryUnidades = `select
      idunits,
      unityname as 'Unidade',
      concat(reference_db, '.dbo.') as 'db'
    from units
    where enabled = 1
    order by unityname asc;`;
    mysqlConnection.query(SqlQueryUnidades, function (error, Unidades, fields) {
      RemoverAulas(Unidades, 0, function (Unidades) {
        console.log(
          new Date().toUTCString() + ' - ' + req.originalUrl + ' - finished'
        );
        res.send(Unidades);
      });
    });
  });

  function RemoverAulas(Unidades, Index, callback) {
    if (Index < Unidades.length) {
      console.log(
        'Analisando unidade ' +
          (Index + 1) +
          ' de ' +
          Unidades.length +
          ' (' +
          Unidades[Index]['Unidade'] +
          ')'
      );
      Unidades[Index]['AulaAluno'] = [];
      let SqlQueryMySQL = `select * from (
        select
          sc.idusers,
          sc.idunits,
          sc.idclasses,
          sc.note,
          ExA.Questoes,
          count(ug.idusergivenanswers) as 'Respostas'
        from stepclass sc
        inner join (select distinct
            cl.idclasses,
            cl.name as 'Aula',
            count(cc.idexercises) as 'Questoes'
          from classes cl
          inner join courses co on co.idcourses = cl.idcourses
          inner join classescontent cc on cc.idclasses = cl.idclasses
          left join exercisequestions eq on eq.idexercises = cc.idexercises
          where co.idtypescourse in (1,2,3)
          group by cl.idclasses, cc.idclasses
          order by cl.idclasses asc) ExA on sc.idclasses = ExA.idclasses and ExA.Questoes > 0
        left join usergivenanswers ug on ug.idunits = sc.idunits and ug.idusers = sc.idusers and ug.idclasses = sc.idclasses
        where sc.idunits = ${Unidades[Index]['idunits']} and sc.completedClass = 1 and (now() >= date_add(convert(sc.dateinicial, date), interval 7 day) or sc.homework is not null)
        group by sc.idusers, sc.idunits, sc.idclasses) R
      where R.Respostas != R.Questoes;`;
      mysqlConnection.query(SqlQueryMySQL, function (error, AulaAluno, fields) {
        Unidades[Index]['AulaAluno'] = AulaAluno;
        if (AulaAluno && AulaAluno.length > 0) {
          Unidades[Index]['SQLRem'] = `delete from usergivenanswers where `;
        } else {
          Unidades[Index]['SQLRem'] = 'select 1;';
        }
        LoopRemoverRespostas(Unidades[Index], 0, '', function (newUnidade) {
          Unidades[Index] = newUnidade;
          if (Unidades[Index]['IDsRemover'] == '') {
            Unidades[Index]['IDsRemover'] = 'select 1;';
          } else {
            Unidades[Index]['SQLRem'] +=
              '(' + Unidades[Index]['IDsRemover'] + ')';
          }
          mysqlConnection.query(
            Unidades[Index]['SQLRem'],
            function (error, delRespostas, fields) {
              Index++;
              RemoverAulas(Unidades, Index, callback);
            }
          );
        });
      });
    } else {
      callback(Unidades);
    }
  }

  function LoopRemoverRespostas(Unidade, Index, IDsRemover, callback) {
    if (Index < Unidade['AulaAluno'].length) {
      let sqlQueryRespostas = `select * from usergivenanswers ug where ug.idunits = ${Unidade['AulaAluno'][Index]['idunits']} and ug.idclasses = ${Unidade['AulaAluno'][Index]['idclasses']} and ug.idusers = ${Unidade['AulaAluno'][Index]['idusers']};`;
      mysqlConnection.query(
        sqlQueryRespostas,
        function (error, ResAluno, fields) {
          for (let i = 0; i < ResAluno.length; i++) {
            if (IDsRemover != '') IDsRemover += ' or ';
            IDsRemover += `(idunits = ${ResAluno[i]['idunits']} and idusers = ${ResAluno[i]['idusers']} and idclasses = ${ResAluno[i]['idclasses']})`;
          }
          Index++;
          LoopRemoverRespostas(Unidade, Index, IDsRemover, callback);
        }
      );
    } else {
      Unidade['IDsRemover'] = IDsRemover;
      callback(Unidade);
    }
  }

  router.get('/RelatorioMatriculasSenhas/:db', (req, res) => {
    const db = req.params.db;
    console.log(
      new Date().toUTCString() + ' - ' + req.originalUrl + ' - initing...'
    );
    const SqlQuery = `select distinct
      tt.IDMatricula,
      cl.Nome,
      cl.SenhaIntranet as 'Senha'
    from ${db}Clientes cl
    inner join ${db}TreinandosTurmas tt on tt.IDTreinando = cl.IDCliente
    order by tt.IDMatricula asc;`;
    sqlServerConnection
      .request()
      .query(SqlQuery, function (error, results, fields) {
        let Relatorio = [];
        for (let i = 0; i < results['recordset'].length; i++) {
          Relatorio.push(results['recordset'][i]);
          Relatorio[i]['Senha'] = encriptPassword(
            Relatorio[i]['Senha'],
            Relatorio[i]['Senha'].length
          );
        }
        res.send(Relatorio);
      });
  });

  return router;
};

let indexStudents = 0;
function LoopStudentsOfTeacher(Alunos, res, db, idteacher) {
  if (indexStudents < Alunos.length) {
    let sqlQuery = `
    SELECT
      COUNT(sc.idusers) as qtde_stepclass
    FROM
      stepclass sc
        INNER JOIN
      classes c ON sc.idclasses = c.idclasses
        INNER JOIN
      courses co ON co.idcourses = c.idcourses
    WHERE
      sc.idusers = ${
        Alunos[indexStudents]['IDMatricula']
      } AND sc.idunits = (SELECT idunits FROM units WHERE reference_db = '${
      db.split('.')[0]
    }') AND co.idcourses = ${Alunos[indexStudents]['CodigoCurso']};`;
    mysqlConnection.query(sqlQuery, function (error, results, fields) {
      if (!error && results.length != 0) {
        let sqlQuery2 = `SELECT COUNT(*) AS Ministradas FROM ${db}TurmasCalendario WHERE CodigoCurso = ${Alunos[indexStudents]['CodigoCurso']} AND IdTurma = ${Alunos[indexStudents]['IdTurma']} AND Ministrado = 1;`;

        sqlServerConnection
          .request()
          .query(sqlQuery2, function (error2, results2, fields) {
            if (!error && results2.length > 0) {
              if (results2[0]['Ministradas'] > results[0]['qtde_stepclass']) {
                res.send(true);
              } else {
                indexStudents++;
                LoopStudentsOfTeacher(Alunos, res, db, idteacher);
              }
            } else {
              indexStudents++;
              LoopStudentsOfTeacher(Alunos, res, db, idteacher);
            }
          });
      } else {
        indexStudents++;
        LoopStudentsOfTeacher(Alunos, res, db, idteacher);
      }
    });
  } else {
    res.send(false);
  }
}

function CheckDateExists(
  db,
  date,
  idteacher,
  StudentsIDs,
  i,
  Nomes,
  res,
  content,
  finalIDchamada
) {
  try {
    if (i < StudentsIDs.length) {
      if (parseInt(StudentsIDs[i]) > 190000) {
        StudentsIDs[
          i
        ] = `(SELECT DISTINCT top(1) CodigoTurma FROM ${db}TreinandosTurmas WHERE IDMatricula = ${StudentsIDs[i]})`;
      }
      let sqlQry1 = `SELECT top(1) *, ${StudentsIDs[i]} AS 'CodTurma'
      FROM ${db}TurmasCalendario tc
      INNER JOIN ${db}TreinandosTurmas tt
      ON tt.CodigoTurma = tc.IdTurma
      where tc.IDTurma = ${StudentsIDs[i]}
      and Ministrado = 0
      order by Data asc;`;
      console.log(sqlQry1);
      sqlServerConnection
        .request()
        .query(sqlQry1, function (error1, results1, fields) {
          if (!error1 && results1.length != 0) {
            //console.log(results1);
            var ResultTurmasCalendario = results1.recordsets[0][0];
            if (ResultTurmasCalendario) {
              StudentsIDs[i] = ResultTurmasCalendario['CodTurma'];
              //console.log(StudentsIDs[i]);
              let sqlQry2 = `
            SELECT
              *
            FROM
              ${db}TreinandosTurmas
            WHERE
              CodigoTurma = ${ResultTurmasCalendario['IdTurma']}
            AND
              IDCurso = ${ResultTurmasCalendario['CodigoCurso']}
            AND
              Desistente = 0
            AND
              Faltoso = 0;
            `;

              sqlServerConnection
                .request()
                .query(sqlQry2, function (error2, results2, fields) {
                  if (!error2 && results2.length != 0) {
                    //console.log(results2.recordset[0]);
                    var ResultTreinandosTurmas = results2.recordset[0];
                    if (
                      ResultTreinandosTurmas['CodigoTurma'] == StudentsIDs[i]
                    ) {
                      let sqlQry3 = `SELECT TOP 1 IDChamadas FROM ${db}Chamadas ORDER BY IDChamadas DESC;`;
                      sqlServerConnection
                        .request()
                        .query(sqlQry3, function (error3, results3, fields) {
                          console.log(results3);
                          let idchamada = results3.recordsets[0].length
                            ? results3.recordsets[0][0]['IDChamadas'] + 1
                            : 1;
                          let idturma = ResultTurmasCalendario['IdTurma'];
                          let idcurso = ResultTurmasCalendario['CodigoCurso'];
                          let data2 = formatDate(
                            ResultTurmasCalendario['Data'],
                            0
                          );
                          let hora = formatDate(
                            ResultTurmasCalendario['HoraInicial'],
                            1
                          );
                          let horainicio = formatDate(
                            ResultTurmasCalendario['HoraInicial'],
                            1
                          );
                          let horatermino = formatDate(
                            ResultTurmasCalendario['HoraFinal'],
                            1
                          );
                          let idinstrutor =
                            ResultTurmasCalendario['CodigoInstrutor'];
                          let dataenvioapp = formatDate(new Date(), 0);
                          let dataatualizacaoapp = formatDate(new Date(), 0);
                          let idfuncionarioapp =
                            ResultTurmasCalendario['CodigoInstrutor'];
                          let sqlQry4 = `INSERT INTO ${db}Chamadas
                      (IDChamadas, IDTurma, IDCurso, Data, Hora, HoraInicio, HoraTermino, IDInstrutor, Flex, APP, DataEnvioAPP, DataAtualizacaoAPP, IDFuncionarioAPP)
                      VALUES
                      (${idchamada},${idturma},${idcurso},'${date}','${hora}','${horainicio}','${horatermino}',${idinstrutor},1,1,'${dataenvioapp}','${dataatualizacaoapp}',${idfuncionarioapp});`;

                          sqlServerConnection
                            .request()
                            .query(
                              sqlQry4,
                              function (error6, results6, fields) {
                                if (!error6) {
                                  let sqlQry6 = `DELETE FROM ${db}ChamadasConteudos WHERE IDChamadas = ${idchamada};`;

                                  sqlServerConnection
                                    .request()
                                    .query(
                                      sqlQry6,
                                      function (error7, results7, fields) {
                                        if (!error7) {
                                          let sqlQry7 = `INSERT INTO ${db}ChamadasConteudos (IDChamadas, CodigoConteudo, DescricaoConteudo) VALUES (${idchamada}, 0, '${content}');`;
                                          //console.log(sqlQry7);
                                          sqlServerConnection
                                            .request()
                                            .query(
                                              sqlQry7,
                                              function (
                                                error8,
                                                results8,
                                                fields
                                              ) {
                                                if (!error8) {
                                                  let sqlQry8 = `UPDATE ${db}TurmasCalendario SET Ministrado = 1, Data = '${date}' WHERE IdTurmasCalendario = ${ResultTurmasCalendario['IdTurmasCalendario']};`;
                                                  //console.log(sqlQry8);
                                                  sqlServerConnection
                                                    .request()
                                                    .query(
                                                      sqlQry8,
                                                      function (
                                                        error9,
                                                        results9,
                                                        fields
                                                      ) {
                                                        if (!error9) {
                                                          let sqlQry9 = `DELETE FROM ${db}ChamadasDetalhes WHERE IDChamadas = ${idchamada};`;
                                                          //console.log(sqlQry9);
                                                          sqlServerConnection
                                                            .request()
                                                            .query(
                                                              sqlQry9,
                                                              function (
                                                                error10,
                                                                results10,
                                                                fields
                                                              ) {
                                                                if (!error10) {
                                                                  let sqlQry10 = `INSERT INTO ${db}ChamadasDetalhes
                                        (IDChamadas, IDTreinando, Presente, Avisada, Justificativa, UltimaCarta) VALUES
                                        (${idchamada}, ${ResultTreinandosTurmas['IDTreinando']}, 1, 0, null, null);`;
                                                                  //console.log(sqlQry10);
                                                                  sqlServerConnection
                                                                    .request()
                                                                    .query(
                                                                      sqlQry10,
                                                                      function (
                                                                        error11,
                                                                        results11,
                                                                        fields
                                                                      ) {
                                                                        if (
                                                                          !error11
                                                                        ) {
                                                                          i++;
                                                                          CheckDateExists(
                                                                            db,
                                                                            date,
                                                                            idteacher,
                                                                            StudentsIDs,
                                                                            i,
                                                                            Nomes,
                                                                            res,
                                                                            content,
                                                                            idchamada
                                                                          );
                                                                        }
                                                                      }
                                                                    );
                                                                }
                                                              }
                                                            );
                                                        }
                                                      }
                                                    );
                                                }
                                              }
                                            );
                                        }
                                      }
                                    );
                                } else {
                                  console.log('erro sqlQry4: ' + sqlQry4);
                                  console.log(error6);
                                }
                              }
                            );
                        });
                    } else {
                      console.log('Não existe');

                      var sqlQry5 = `SELECT * FROM ${db}Clientes WHERE IDCliente = ${StudentsIDs[i]};`;
                      sqlServerConnection
                        .request()
                        .query(sqlQry5, function (error5, results5, fields) {
                          if (!error5 && results5.length != 0) {
                            if (Nomes == '') {
                              console.log(results5.recordsets);
                              Nomes = results5.recordset[0]['Nome'];
                            } else {
                              Nomes += ', ' + results5.recordset[0]['Nome'];
                            }
                          }
                          i++;
                          CheckDateExists(
                            db,
                            date,
                            idteacher,
                            StudentsIDs,
                            i,
                            Nomes,
                            res,
                            content,
                            null
                          );
                        });
                    }
                  } else {
                    console.log('erro sqlQry2');
                    i++;
                    CheckDateExists(
                      db,
                      date,
                      idteacher,
                      StudentsIDs,
                      i,
                      Nomes,
                      res,
                      content,
                      null
                    );
                  }
                });
            } else {
              console.log('erro: turmas calendário retornou null');
              i++;
              CheckDateExists(
                db,
                date,
                idteacher,
                StudentsIDs,
                i,
                Nomes,
                res,
                content,
                null
              );
            }
          } else {
            console.log('erro sqlQry1: ' + error1);
            i++;
            CheckDateExists(
              db,
              date,
              idteacher,
              StudentsIDs,
              i,
              Nomes,
              res,
              content,
              null
            );
          }
        });
    } else {
      //console.log("finalIDchamada: " + finalIDchamada);
      res.send(
        JSON.stringify({
          idcallresponse: finalIDchamada,
        })
      );
    }
  } catch (error) {
    console.error(error);
    res.send(
      JSON.stringify({
        error: error,
      })
    );
  }
}

function formatDate(date, ret) {
  var novaData = date
    .toISOString()
    .replace(/T/, ' ')
    .replace(/\..+/, '')
    .split(' ');
  return novaData[ret];
}

function InsertNotes() {
  console.log(new Date().toUTCString() + ' - /InsertNotes');
  const SQLQryNotasAulas = `update stepclass A
  join
  (select * from
  (select distinct
    *,
      ((AC2.TotalAcertos / AC2.ItensAula) * 10) as 'Nota'
  from (
  select
    *,
      (AC.Acertos + AC.Erros + AC.Atividades) as 'ItensAula',
      (AC.Acertos + AC.NotaAtividades) as 'TotalAcertos'
  from
    (select
      sc.idusers,
      sc.idclasses,
      sc.idunits,
      sc.completedClass,
      sc.datefinal,
      coalesce((ea.iscorrect), 0) as 'Acertos',
      coalesce(sum(case when ea.iscorrect = 1 then 0 else 1 end), 0) as 'Erros',
          (SELECT COUNT(*) FROM userstages WHERE idusers = sc.idusers AND idclasses = sc.idclasses AND idunits = sc.idunits) AS 'Atividades',
      (SELECT coalesce(SUM(note), 0) FROM userstages WHERE idusers = sc.idusers AND idclasses = sc.idclasses AND idunits = sc.idunits) AS 'NotaAtividades',
      sc.note
    from (select * from stepclass sc where sc.completedClass = 1 and sc.idusers > 190008 and sc.note is null and (CASE WHEN STR_TO_DATE(sc.datefinal, '%d/%m/%Y') < NOW() OR sc.homework = 1 THEN 1 ELSE 0 END) = 1) sc
    left join usergivenanswers ug on ug.idclasses = sc.idclasses and ug.idusers = sc.idusers and ug.idunits = sc.idunits
    left join exerciseanswers ea on ea.idexerciseanswers = ug.idexerciseanswers
    left join userstages us on us.idclasses = sc.idclasses and us.idusers = sc.idusers and us.idunits = sc.idunits
    group by sc.idusers, sc.idclasses, sc.idunits, sc.completedClass, sc.note, sc.datefinal, ea.iscorrect) AC) AC2) AC3) B
  on A.idusers = B.idusers and A.idunits = B.idunits and A.idclasses = B.idclasses
  set A.note = B.Nota;`;
  const SQLNewInsertNotes = `update stepclass A
    join (select
	    R.idusers,
      R.idunits,
      R.idclasses,
      R.Questoes,
      R.EAcertos as 'ExerciciosAcertos',
      R.TAcertos as 'TarefasAcertos',
      case
		    when (R.Questoes + R.TAcertos + R.TErros) = 0 then 10
		    when ((R.EAcertos + R.TAcertos) / (R.Questoes + R.TAcertos + R.TErros) * 10) > 10 then 10
        else ((R.EAcertos + R.TAcertos) / (R.Questoes + R.TAcertos + R.TErros) * 10)
	    end as 'Nota'
    from (select distinct
	      sc.idstepclass,
        sc.idusers,
        sc.idunits,
        sc.idclasses,
        ExA.Questoes,
        (select count(*) from usergivenanswers ug inner join exerciseanswers ea on ea.idexerciseanswers = ug.idexerciseanswers and ea.iscorrect = 1 where ug.idclasses = sc.idclasses and ug.idunits = sc.idunits and ug.idusers = sc.idusers) as 'EAcertos',
        (select count(*) from usergivenanswers ug inner join exerciseanswers ea on ea.idexerciseanswers = ug.idexerciseanswers and ea.iscorrect = 0 where ug.idclasses = sc.idclasses and ug.idunits = sc.idunits and ug.idusers = sc.idusers) as 'EErros',
        (select count(*) from userstages us where us.idclasses = sc.idclasses and us.idunits = sc.idunits and us.idusers = sc.idusers and us.note = 1) as 'TAcertos',
        (select count(*) from userstages us where us.idclasses = sc.idclasses and us.idunits = sc.idunits and us.idusers = sc.idusers and us.note = 0) as 'TErros'
      from stepclass sc
      inner join (select distinct
		      cl.idclasses,
		      cl.name as 'Aula',
		      count(cc.idexercises) as 'Questoes'
	      from classes cl
	      inner join classescontent cc on cc.idclasses = cl.idclasses
	      left join exercisequestions eq on eq.idexercises = cc.idexercises
	      group by cl.idclasses, cc.idclasses
	      order by cl.idclasses asc) ExA on ExA.idclasses = sc.idclasses
      where sc.note is null and sc.completedClass = 1 and (now() >= date_add(convert(sc.dateinicial, date), interval 7 day) or sc.homework is not null)) R) B
    on A.idusers = B.idusers and A.idunits = B.idunits and A.idclasses = B.idclasses
  set A.note = B.Nota;`;

  mysqlConnection.query(SQLNewInsertNotes, function (error, results, fields) {
    console.log(new Date().toUTCString() + ' - Notas de aulas lançadas');
    InsertCourseNotes();
    return true;
  });
}

function InsertNotasDetalhes(idusers, idunits, idclasses) {
  console.log(new Date().toUTCString() + ' - /InsertNotasDetalhes');
  let sqlQry0 = `
  SELECT
	  *
  FROM
	  (SELECT note AS Nota from stepclass where idusers = ${idusers} and idunits = ${idunits} and idclasses = ${idclasses}) AS note,
	  (SELECT reference_db AS Banco from units where idunits = ${idunits}) AS banco,
    (SELECT idcourses AS IDCurso from classes where idclasses = ${idclasses}) AS curso,
    (SELECT ordem + 1 AS Conceito from classes where idclasses = ${idclasses}) AS conceito;`;

  mysqlConnection.query(sqlQry0, function (error, results, fields) {
    if (!error && results.length != 0) {
      let nota = results[0]['Nota'];
      let db = results[0]['Banco'] + '.dbo.';
      let idcourses = results[0]['IDCurso'];
      let concept = results[0]['Conceito'];

      let sqlServerQuery = `
      INSERT INTO
        ${db}NotasDetalhes(IDTurma, IDCurso, IDMatricula, IDTreinando, IDConceito, Nota)
      SELECT
        CodigoTurma, IDCurso, IDMatricula, IDTreinando, ${concept} as IDConceito, ${nota} as Nota FROM ${db}TreinandosTurmas WHERE IDMatricula = ${idusers} AND IDCurso = ${idcourses};

	    INSERT INTO
        ${db}NotasDetalhesSequencial(IDTurma, IDCurso, IDMatricula, IDTreinando, IDConceito)
      SELECT
        CodigoTurma, IDCurso, IDMatricula, IDTreinando, ${concept} as IDConceito FROM ${db}TreinandosTurmas WHERE IDMatricula = ${idusers} AND IDCurso = ${idcourses};`;
      //console.log(sqlServerQuery);
      sqlServerConnection
        .request()
        .query(sqlServerQuery, function (error1, results1, fields) {
          return true;
        });
    }
  });
}

function InsertCourseNotes() {
  console.log(new Date().toUTCString() + ' - /InsertCourseNotes');
  const SQLQry = `select
      Tab2.idcourses,
        Tab2.idusers,
      (sum(Tab2.note) / Tab2.AulasCurso) as 'note',
        Tab2.idunits
    from (
      select
        Tab1.*,
        sc.idstepclass,
        sc.note
      from (
        select
          sc.idusers,
          co.idcourses,
          sc.idunits,
          count(sc.idclasses) as 'Aulas',
          (select count(*) from classes cl1 where cl1.idcourses = co.idcourses and cl1.disabled = 0) as 'AulasCurso'
        from stepclass sc
        inner join classes cl on cl.idclasses = sc.idclasses
        inner join courses co on co.idcourses = cl.idcourses
        where sc.note is not null
        group by sc.idusers, co.idcourses, sc.idunits) Tab1
      inner join stepclass sc on sc.idunits = Tab1.idunits and sc.idusers = Tab1.idusers
      inner join classes cl on cl.idclasses = sc.idclasses and cl.idcourses = Tab1.idcourses
      where Tab1.Aulas = Tab1.AulasCurso
      order by idunits, idcourses, idusers) Tab2
    group by Tab2.idcourses, Tab2.idusers, Tab2.idunits;`;

  mysqlConnection.query(SQLQry, function (error, results, fields) {
    LoopCursoNotas(0, results, function (Sucesso) {
      console.log(new Date().toUTCString() + ' - Notas de cursos lançadas');
      return Sucesso;
    });
  });
}

function LoopCursoNotas(Index, Registros, callback) {
  if (!Registros) {
    callback(false);
    return;
  }
  if (Index < Registros.length) {
    let SQLInsert = `INSERT INTO coursenotes (idcourses, idusers, note, idunits) VALUES (${Registros[Index]['idcourses']}, ${Registros[Index]['idusers']}, ${Registros[Index]['note']}, ${Registros[Index]['idunits']});`;
    //console.log(SQLInsert);
    mysqlConnection.query(SQLInsert, function (error, results, fields) {
      if (!error) {
        console.log(
          'Notas do curso lançadas para o aluno ' +
            Registros[Index]['idusers'] +
            ' da unidade ' +
            Registros[Index]['idcourses']
        );
      }
      Index++;
      LoopCursoNotas(Index, Registros, callback);
    });
  } else {
    callback(true);
  }
}

let IndexNotaDetalhe = 0;
function LoopNotasDetalhes(idusers, idunits, idcourses, conceito) {
  if (IndexNotaDetalhe <= conceito) {
    let sqlQry = `SELECT idclasses FROM classes WHERE ordem = ${IndexNotaDetalhe} AND idcourses = ${idcourses};`;
    mysqlConnection.query(sqlQry, function (error, results, fields) {
      let idclasses;
      try {
        idclasses = results[0]['idclasses'];
      } catch (e) {
        return true;
      }

      InsertNotasDetalhes(idusers, idunits, idclasses);
      setTimeout(() => {
        IndexNotaDetalhe++;
        LoopNotasDetalhes(idusers, idunits, idcourses, IndexNotaDetalhe);
      }, 200);
    });
  } else {
    return true;
  }
}

function InsertTraineeProgramNotes(idusers, idunits, idclasses) {
  console.log('/InsertTraineeProgramNotes');
  const SQLQry = `
  INSERT INTO
    traineeprogramnotes (idtraineeprogram, idusers, note, idunits)
  SELECT
      (SELECT DISTINCT
              idtraineeprogram
          FROM
              traineeprogramcourses
          WHERE
              idcourses = (SELECT idcourses FROM classes WHERE idclasses = ${idclasses})) idtraineeprogram,
      ${idusers} AS idusers,
      (SELECT
              SUM(note) / COUNT(*) AS Nota
          FROM
              coursenotes
          WHERE
              idusers = ${idusers} AND idunits = ${idunits}) AS note,
      ${idunits} AS idunits
  FROM
   (SELECT 1) r
  WHERE
      (SELECT COUNT(*) as qtde FROM coursenotes WHERE idusers = ${idusers} AND idunits = ${idusers}) =
    (SELECT COUNT(*) as qtde FROM traineeprogramcourses WHERE
      idtraineeprogram = (SELECT DISTINCT idtraineeprogram FROM traineeprogramcourses WHERE idcourses = (SELECT idcourses FROM classes WHERE idclasses = ${idclasses})));`;

  mysqlConnection.query(SQLQry, function (error, results, fields) {
    if (!error && results.length != 0) {
      //console.log(results['affectedRows']);
      /*if (results['affectedRows'] == 1) {

          let sqlQry0 = `
          SELECT * FROM
          (SELECT SUM(note) / COUNT(*) AS Nota FROM coursenotes WHERE idusers = ${idusers} AND idunits = ${idunits}) AS note,
          (SELECT reference_db AS Banco from units where idunits = ${idunits}) AS banco,
          (SELECT idcourses AS IDCurso from classes where idclasses = ${idclasses}) AS curso`;

          mysqlConnection.query(sqlQry0, function (error, results, fields) {
            if (!error && results.length != 0) {
              console.log(results[0]);
              let nota = results[0]['Nota'];
              let db = results[0]['Banco'] + ".dbo.";
              let idcourses = results[0]['IDCurso'];
              let sqlQry1 = `
              UPDATE
                ${db}TreinandosTurmas tt
              SET
                Nota = ${nota}
              WHERE
                IDMatricula = ${idusers}
              AND
                IDCurso = ${idcourses}`;
                //console.log(sqlQry1);
              sqlServerConnection.request().query(sqlQry1, function (error1, results1, fields) {
                console.log("/Inserted TreinandosTurmas");
                return true;
              })
            }
            else {
              return false;
            }
          });
        }
        else {
          return true;
        }*/
      return true;
    } else {
      return true;
    }
  });
}

function InsertUserStage(idusers, idunits, idclasses, note) {
  mysqlConnection.query(
    'select * from userstages order by iduserstages desc;',
    function (error, results, fields) {
      let IDUserStages = '';
      if (!results || results == '' || results.length == 0) IDUserStages = '1';
      else IDUserStages = parseInt(results[0]['iduserstages']) + 1;
      console.log(IDUserStages);
      const SQLInsert = `INSERT INTO userstages(iduserstages, idstages, idusers, idunits, idclasses, note) values
    (${IDUserStages}, 2, ${idusers}, ${idunits}, ${idclasses}, ${note});`;
      mysqlConnection.query(SQLInsert, function (error, results, fields) {
        if (!error)
          console.log(
            '/HomeWork success - Matrícula: ' +
              idusers +
              ' ; Unidade: ' +
              idunits
          );
        else
          console.log(
            '/HomeWork error - Matrícula: ' + idusers + ' ; Unidade: ' + idunits
          );
      });
    }
  );
}

function PreencherNotasSQLServer(Unidades, Index, callback) {
  if (Index < Unidades.length) {
    console.log(
      'Analisando unidade ' +
        (Index + 1) +
        ' de ' +
        Unidades.length +
        ' (' +
        Unidades[Index]['Unidade'] +
        ')'
    );
    let SqlQueryMySQL = `select * from (
      select
        cl.idcourses as 'IDCurso',
        sc.idusers as 'IDMatricula',
        (cl.ordem + 1) + ((co.idtypescourse - 1) * 16) as 'IDConceito',
        sc.note as 'Nota'
      from stepclass sc
      inner join classes cl on cl.idclasses = sc.idclasses
      inner join courses co on co.idcourses = cl.idcourses
      inner join units un on un.idunits = sc.idunits
      where co.idtypescourse in (1,2,3) and sc.idunits = ${Unidades[Index]['idunits']}) R
    order by R.IDConceito asc;`;
    mysqlConnection.query(SqlQueryMySQL, function (error, NotasMySQL, fields) {
      let SqlQuerySQLServer = `select * from ${Unidades[Index]['db']}NotasDetalhes;`;
      sqlServerConnection
        .request()
        .query(SqlQuerySQLServer, function (error, results, fields) {
          let NotasSQLServer = [];
          if (results['recordset']) {
            NotasSQLServer = results['recordset'];
          }
          Unidades[Index].NotasLancar = [];
          for (let i = 0; i < NotasMySQL.length; i++) {
            let Existe = false;
            for (let o = 0; o < NotasSQLServer.length; o++) {
              if (
                NotasMySQL[i]['IDCurso'] == NotasSQLServer[o]['IDCurso'] &&
                NotasMySQL[i]['IDMatricula'] ==
                  NotasSQLServer[o]['IDMatricula'] &&
                NotasMySQL[i]['IDConceito'] == NotasSQLServer[o]['IDConceito']
              ) {
                Existe = true;
                break;
              }
            }
            if (!Existe) {
              Unidades[Index].NotasLancar.push(NotasMySQL[i]);
            }
          }
          console.log(
            'Registros para inserir: ' + Unidades[Index]['NotasLancar'].length
          );
          InserirNotasSqlServer(Unidades, 0, Index, callback);
        });
    });
  } else {
    callback(Unidades);
  }
}

function InserirNotasSqlServer(Unidades, Index1, Index2, callback) {
  if (Index1 < Unidades[Index2]['NotasLancar'].length) {
    let SqlQuerySQLInsert = `insert into ${Unidades[Index2]['db']}NotasDetalhes (IDTurma, IDCurso, IDMatricula, IDTreinando, IDConceito, Nota) select top 1 tt.CodigoTurma, ${Unidades[Index2]['NotasLancar'][Index1]['IDCurso']}, ${Unidades[Index2]['NotasLancar'][Index1]['IDMatricula']}, tt.IDTreinando, ${Unidades[Index2]['NotasLancar'][Index1]['IDConceito']}, ${Unidades[Index2]['NotasLancar'][Index1]['Nota']} from ${Unidades[Index2]['db']}TreinandosTurmas tt where tt.IDMatricula = ${Unidades[Index2]['NotasLancar'][Index1]['IDMatricula']}`;
    sqlServerConnection
      .request()
      .query(SqlQuerySQLInsert, function (error, results, fields) {
        if (!error) {
          let SqlQuerySQLInsert2 = `insert into ${Unidades[Index2]['db']}NotasDetalhesSequencial (IDTurma, IDCurso, IDMatricula, IDTreinando, IDConceito) select top 1 tt.CodigoTurma, ${Unidades[Index2]['NotasLancar'][Index1]['IDCurso']}, ${Unidades[Index2]['NotasLancar'][Index1]['IDMatricula']}, tt.IDTreinando, ${Unidades[Index2]['NotasLancar'][Index1]['IDConceito']} from ${Unidades[Index2]['db']}TreinandosTurmas tt where tt.IDMatricula = ${Unidades[Index2]['NotasLancar'][Index1]['IDMatricula']}`;
          sqlServerConnection
            .request()
            .query(SqlQuerySQLInsert2, function (error, results, fields) {
              Index1++;
              InserirNotasSqlServer(Unidades, Index1, Index2, callback);
            });
        } else {
          Index1++;
          InserirNotasSqlServer(Unidades, Index1, Index2, callback);
        }
      });
  } else {
    Index2++;
    PreencherNotasSQLServer(Unidades, Index2, callback);
  }
}

module.exports = routes;