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

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

var routes = function () {

    router.post('/authUser', (req, res) => {
        //const password = encriptPassword(req.body.password);
        const type = parseInt(req.body.type);
        const password = req.body.password;
        const user = req.body.login;
        const db = req.body.db;
        this.id = type + user + password;
        const query = `SELECT  
            zu.user AS IDMatricula, 
            zu.userName AS Nome,
            zu.idzoeusertype AS idusertype
        FROM zoeusers zu 
        WHERE zu.enabled = 1 AND CONCAT(zu.idunits, '.dbo.') = '${db}' AND zu.user = '${user}' AND zu.password = '${password}';`;
        //console.log(query);
        execMySQLQueryLogin(query, res,  req.originalUrl);
    });

    router.post('/authUserOld', (req, res) => {
        const type = parseInt(req.body.type);
        const password = encriptPassword(req.body.password);
        const email = req.body.email;
        const user = req.body.login;
        this.id = type + user + password;
        const db = req.body.db;
        const ModoEmergencia = req.body.ME || false;

    /*    if(db != "SGPInicial.dbo." && user == "190000"){
            //Vai entra não rapai
        }else  {*/
            switch (type) {
                case 1: //funcionário
                    execSQLQueryLogin(`SELECT *,
                    1 as 'idusertype' FROM ${db}Funcionarios WHERE IDFuncionario = '${user}' AND Senha = '${password}'`, res, req.originalUrl);
                    break;
                case "1": //funcionário app
                    execSQLQueryLogin(`SELECT IDFuncionario, Nome,
                    1 as 'idusertype' FROM ${db}Funcionarios WHERE IDFuncionario = '${user}' AND Senha = '${password}'`, res, req.originalUrl);
                    break;
                case 2: //adm
                    execMySQLQueryLogin(`SELECT
                        *
                    FROM users
                    WHERE user = '${user}' AND password = '${password}' AND enabled = 1 AND idusertype in (2, 3, 6, 7, 8, 9, 10, 11, 12, 13);`, res, req.originalUrl);
                    break;
                case 3: //First Access
                    execSQLQuery2(`SELECT
                        mt.IDMatricula,
                        c.e_mail,
                        c.Nome,
                        c.IDCliente,
                        c.SenhaIntranet
                    FROM ${db}MatriculasTreinandos mt
                    INNER JOIN ${db}Clientes c ON mt.IDTreinando = c.IDCliente
                    WHERE mt.IDMatricula = '${user}' AND c.e_mail = '${email}';`, res, req.originalUrl);
                    break;
                case 4: //aluno
                    if (ModoEmergencia) {
                        execMySQLQueryLogin(`SELECT
                            ${user} AS IDMatricula,
                            '123' AS SenhaIntranet,
                            'Aluno' AS Nome,
                            4 as 'idusertype';`, res, req.originalUrl);
                    }
                    else {
                        //console.log("Matricula: " + user + " - Senha: '" + password + "'");
                        // '      ' espaços em branco das senhas
                        if (user == "190000")
                        {
                            if (db == "SGPInicial.dbo." && password == encriptPassword("z0et3acherM02ster") )
                            {
                                execSQLQueryLogin(`SELECT
                                    '190000' as 'IDMatricula',
                                    'maker2019' as 'SenhaIntranet',
                                    'Sistema' as 'Nome',
                                    4 as 'idusertype';`, res, req.originalUrl);
                            }
                            else if(db != "SGPInicial.dbo." && password == encriptPassword("zoeteacher"))
                            {
                                execSQLQueryLogin(`SELECT
                                '190000' as 'IDMatricula',
                                'maker2019' as 'SenhaIntranet',
                                'Sistema' as 'Nome',
                                4 as 'idusertype';`, res, req.originalUrl);
                            }
                        }
                        else if (user =="190024" && password == encriptPassword("123"))
                        {
                            execSQLQueryLogin(`SELECT
                            '190024' as 'IDMatricula',
                            'maker2019' as 'SenhaIntranet',
                            'Sistema' as 'Nome',
                            4 as 'idusertype';`, res, req.originalUrl);
                        }
                        else if(user=="190025" && password == encriptPassword("123"))
                        {
                            execSQLQueryLogin(`SELECT
                            '190025' as 'IDMatricula',
                            'maker2019' as 'SenhaIntranet',
                            'Sistema' as 'Nome',
                            4 as 'idusertype';`, res, req.originalUrl);
                        }
                        else if(user=="190026" && password == encriptPassword("123"))
                        {
                            execSQLQueryLogin(`SELECT
                            '190026' as 'IDMatricula',
                            'maker2019' as 'SenhaIntranet',
                            'Sistema' as 'Nome',
                            4 as 'idusertype';`, res, req.originalUrl);
                        }
                        else if(user=="240001" && password == encriptPassword("123"))
                        {
                            execSQLQueryLogin(`SELECT
                            '240001' as 'IDMatricula',
                            '123' as 'SenhaIntranet',
                            'ZoeSistema' as 'Nome',
                            4 as 'idusertype';`, res, req.originalUrl);
                        }
                        else if (user == "190001" && password == encriptPassword("123")) {
                            execSQLQueryLogin(`SELECT
                                '190001' as 'IDMatricula',
                                '123' as 'SenhaIntranet',
                                'Aluno Demonstração' as 'Nome',
                                4 as 'idusertype';`, res, req.originalUrl);
                        }
                        else if (user == "190002" && password == encriptPassword("123")) {
                            mysqlConnection.query(`select * from usershomework uh inner join units un on un.idunits = uh.idunits where uh.idusers = ${user} and un.reference_db = '${db.split('.dbo.')[0]}';`, function(error, AlunoExiste, fields) {
                                if (AlunoExiste.length > 0 && AlunoExiste[0]['disabled'] == 1) {
                                    res.send(JSON.stringify({
                                        "status": 500,
                                        "error": "Usuário desativado",
                                        "response": [],
                                        "token": null
                                    }));
                                }
                                else if (AlunoExiste.length > 0 && AlunoExiste[0]['disabled'] == 0) {
                                    mysqlConnection.query(`update usershomework set disabled = 1 where idusers = ${user} and idunits = (select idunits from units where reference_db = '${db.split('.dbo.')[0]}');`, function(error, results, fields) {
                                        execSQLQueryLogin(`SELECT
                                        '190002' as 'IDMatricula',
                                        '123' as 'SenhaIntranet',
                                        'Professor' as 'Nome',
                                        4 as 'idusertype';`, res, req.originalUrl);
                                    });
                                }
                                else {
                                    mysqlConnection.query(`insert into usershomework (idusers, idunits, disabled) values (${user}, (select idunits from units where reference_db = '${db.split('.dbo.')[0]}'), 1);`, function(error, results, fields) {
                                        execSQLQueryLogin(`SELECT
                                        '190002' as 'IDMatricula',
                                        '123' as 'SenhaIntranet',
                                        'Professor' as 'Nome',
                                        4 as 'idusertype';`, res, req.originalUrl);
                                    });
                                }
                            });
                        }
                        else {
                            mysqlConnection.query(`SELECT desativado FROM alunosdesativados where idunits = (select idunits from units where reference_db = '${db.split('.dbo.')[0]}') and idusers = ${user};`, function(error, AlunoDesativado, fields) {
                                if (AlunoDesativado && AlunoDesativado.length > 0 && AlunoDesativado[0]['desativado'] == 1) {
                                    res.send(JSON.stringify({
                                        "status": 500,
                                        "error": "Usuário desativado",
                                        "response": [],
                                        "token": null
                                    }));
                                    console.log(new Date().toUTCString() + " - " + req.originalUrl + "/ error 500");
                                }
                                else {
                                    execSQLQueryLogin(`SELECT distinct
                                        mt.IDMatricula,
                                        c.SenhaIntranet,
                                        c.Nome,
                                        4 as 'idusertype'
                                    FROM ${db}MatriculasTreinandos mt
                                    INNER JOIN ${db}Clientes c ON mt.IDTreinando = c.IDCliente
                                    inner join ${db}TreinandosTurmas tt on tt.IDMatricula = mt.IDMatricula and tt.CodigoTurma > 0
                                    WHERE mt.IDMatricula = '${user}' AND c.SenhaIntranet = '${password}' and tt.Faltoso = 0 and tt.Concluiu = 0 and tt.Desistente = 0;`, res, req.originalUrl);
                                }
                            });
                        }
                    }
                    break;
                case 5: //franchisee
                    execMySQLQueryLogin(`SELECT
                        us.idusers,
                        us.name,
                        us.idunits,
                        (SELECT u.reference_db FROM units u WHERE u.idunits = us.idunits) AS 'db',
                        us.idusertype
                    FROM users us
                    WHERE us.user = '${user}' AND us.password = '${password}' AND us.enabled = 1 AND us.idusertype in (5, 14, 15, 16);`, res, req.originalUrl);
                    break;
                default:
                    console.log("default");
                    break;
            }
        //} 
    });

    router.post('/loginzoerobot', (req, res) => {
        const robot_id = req.body.robot_id;
        const idusers = parseInt(req.body.idusers);
        const idunits = parseInt(req.body.idunits);
        const idusertype = parseInt(req.body.idusertype);
        const password = req.body.password;
        const db = req.body.db;
        const micro = parseInt(req.body.micro) || 0;
        execSQLQuery(`CALL loginzoerobot('${robot_id}', ${idusers}, ${idunits}, '${db}', '${password}', ${idusertype}, ${micro})`, res, req.originalUrl); 
    })

    router.post('/getloginzoerobot', (req, res) => {
        const robot_id = req.body.robot_id;
        mysqlConnection.query(`CALL getloginzoerobot('${robot_id}')`, function(error, response, fields) {
            res.send(response[0]);
        })
    })

    router.post('/updatezoerobotmicro', (req, res) => {
        const robot_id = req.body.robot_id;
        const micro = parseInt(req.body.micro);
        execSQLQuery(`CALL updatezoerobotmicro('${robot_id}', ${micro})`, res, req.originalUrl); 
    })



    router.get('/closeCon', (req, res) => {
        console.log("Fechando conexões");
        mysqlConnection.end();
        res.send(true);
    })

    router.get('/authTeacher/:db/:login/:senha', (req, res) => {
        let Login = req.params.login;
        let Senha = encriptPassword(req.params.senha);
        let Banco = req.params.db;
        let sqlQuery = `SELECT IDFuncionario, Nome FROM ${Banco}.dbo.Funcionarios WHERE Nome = '${Login}' AND Senha = '${Senha}';`;
        sqlServerConnection.request().query(sqlQuery, function (error, results, fields) {
            const Caminho = `http:${req.headers.host.split(':')[0]}/assets/`;
            try {
                if (results.recordsets[0].length > 0)
                    res.redirect(Caminho + 'login_succefull.php');
                else
                    res.redirect(Caminho + 'restart_login.php');
            }
            catch (e) {
                res.redirect(Caminho + 'restart_login.php');
            }
        })
    })

    router.patch('/cadPass', (req, res) => {
        const password = encriptPassword(req.body.password);
        const user = req.body.login;
        const db = req.body.db;
        execSQLQuery2(`
            UPDATE
                ${db}Clientes
            SET
                SenhaIntranet = '${password}'
            WHERE
                IDCliente = ${user}`,
            res,
            req.originalUrl
        );
    })

    router.post('/ChangeUserIcon', (req, res) => {
        console.log(req.body);
        const idusers = parseInt(req.body.idusers);
        const usericon = req.body.usericon;
       execSQLQuery(`
            UPDATE
                myrobot.usersLevel
            SET
                usericon = '${usericon}'
            WHERE
                idusers = ${idusers}`,
            res,
            req.originalUrl
        );
    })

    router.patch('/ResetPass', (req, res) => {
        const id = req.body.id;
        const db = req.body.db;
        execSQLQuery2(`
            UPDATE
                ${db}Clientes
            SET
                SenhaIntranet = null
            WHERE
                IDCliente = ${id}`,
            res,
            req.originalUrl
        );
    })

    router.post('/insert', verifyJWT, (req, res) => {
        const idusertype = req.body.idusertype;
        const idcities = req.body.idcities;
        const idstates = req.body.idstates;
        const name = req.body.name;
        const user = req.body.user;
        const password = encriptPassword(req.body.password);
        const email = req.body.email;
        const telephone = req.body.telephone;
        const address = req.body.address;
        const district = req.body.district;
        const enabled = req.body.enabled ? 1 : 0;
        const idunits = req.body.idunits;

        execSQLQuery(`INSERT INTO users (idusertype, idcities, idstates, name, user, password, email, telephone, address, district, enabled, idunits)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);`, res, req.originalUrl, [idusertype, idcities, idstates, name, user, password, email, telephone, address, district, enabled, idunits]);
    });
    
    router.post('/givexp', (req, res) => {
        
        const xpAdd = parseInt(req.body.xpAdd);
        const userId = parseInt(req.body.userId);
        const unitid = parseInt(req.body.unitid);

        execSQLQuery(`UPDATE usersLevel SET usersxp = usersxp + ${xpAdd} where idusers = ${userId} AND idunit = ${unitid};`, res, req.originalUrl);
    });

    router.post('/createusersprofile', (req, res) => {
        
        const userId = parseInt(req.body.userId);
        const unitId = parseInt(req.body.unitId);

        execSQLQuery(`INSERT INTO usersLevel (idusers, idunit,usersxp) VALUES (${userId}, ${unitId}, 0);`, res, req.originalUrl);
    });

    router.patch('/update', verifyJWT, (req, res) => {
        const idusers = req.body.idusers;
        const idusertype = req.body.idusertype;
        const idcities = req.body.idcities;
        const idstates = req.body.idstates;
        const name = req.body.name;
        const user = req.body.user;
        const password = encriptPassword(req.body.password);
        const email = req.body.email;
        const telephone = req.body.telephone;
        const address = req.body.address;
        const district = req.body.district;
        const enabled = req.body.enabled ? 1 : 0;
        const idunits = req.body.idunits;

        execSQLQuery(`UPDATE users SET
            idusertype = ${idusertype},
            idcities = ${idcities},
            idstates = ${idstates},
            name = '${name}',
            user = '${user}',
            password = '${password}',
            email = '${email}',
            telephone = '${telephone}',
            address = '${address}',
            district = '${district}',
            enabled = ${enabled},
            idunits = ${idunits}
        WHERE idusers = ${idusers};`, res, req.originalUrl, [idusertype, idcities, idstates, name, user, password, email, telephone, address, district, enabled, idunits, idusers]);
    });

    router.get('/disable/:idusers', verifyJWT, (req, res) => {
        const idusers = parseInt(req.params.idusers);
        execSQLQuery(`UPDATE users SET
            enabled = not enabled
        WHERE idusers = ${idusers};`, res, req.originalUrl);
    });

    router.get('/getUser/:idusers', verifyJWT, (req, res) => {
        const idusers = parseInt(req.params.idusers);
        execSQLQuery(`SELECT
            *
        FROM users
        WHERE idusers = ${idusers};`, res, req.originalUrl);
    });


    router.get('/getUserType', verifyJWT, (req, res) => {
        execSQLQuery(`SELECT
            *
        FROM usertype;`, res, req.originalUrl);
    });

    router.post('/inserirleveluser', verifyJWT, (req, res) => {
        const idusers = req.body.idusers;
        const idunit = req.body.idunits;
        execSQLQuery(`INSERT INTO usersLevel (idusers, idunit) VALUES (?, ?);`, res, req.originalUrl, [idusers, idunit]);
    });

    router.post('/insertUserType', verifyJWT, (req, res) => {
        const idusertype = req.body.idusertype;
        const description = req.body.description;
        const enabled = req.body.enabled ? 1 : 0;
        execSQLQuery(`INSERT INTO usertype (description, enabled) VALUES (?, ?);`, res, req.originalUrl, [description, enabled]);
    });

    router.patch('/updateUserType', verifyJWT, (req, res) => {
        const idusertype = req.body.idusertype;
        const description = req.body.description;
        const enabled = req.body.enabled ? 1 : 0;
        execSQLQuery(`update usertype set description = ?, enabled = ? where idusertype = ?;`, res, req.originalUrl, [description, enabled, idusertype]);
    });


    router.get('/getidusertype/:idusertype', (req, res) => {
        const idusertype = parseInt(req.params.idusertype);
        execSQLQuery(`SELECT
            *
        FROM usertype
        WHERE idusertype = ${idusertype};`, res, req.originalUrl);
    });
    //AVATAR
    router.get('/getavatar', verifyJWT, (req, res) => {
        execSQLQuery(`SELECT * FROM myrobot.avatarusers;`, res, req.originalUrl);
    });
    //LEVEL
    router.get('/getleveluser/:idusers/:idunit', (req, res) =>{
        const  idusers = parseInt(req.params.idusers);
        const idunit = parseInt(req.params.idunit);
        let sqlQuery = `SELECT *
        FROM usersLevel
        WHERE idusers = ${idusers} AND idunit = ${idunit};`;

        /* console.log(sqlQuery); */
        execSQLQuery(sqlQuery, res, req.originalUrl);
    });

    router.get('/getEmployee/:banco', (req, res) => {
        const db = req.params.banco;
        execSQLQuery2(`SELECT distinct
            fu.IDFuncionario,
            fu.Nome,
            fa.DescricaoAcesso
        FROM ${db}Funcionarios fu
        INNER JOIN ${db}FuncionariosAcesso fa ON fa.IDFuncionario = fu.IDFuncionario
        WHERE fa.Permitido = 1 AND fa.DescricaoAcesso = 'EAD Acesso Online Professor';`, res, req.originalUrl);
    });

    router.get('/getEmployeeId/:id/:banco', (req, res) => {
        id = parseInt(req.params.id);
        const db = req.params.banco;
        execSQLQuery2(`
        SELECT distinct
            ${db}Funcionarios.IDFuncionario,
            ${db}Funcionarios.Nome,
            ${db}FuncionariosAcesso.DescricaoAcesso
        FROM
            ${db}Funcionarios
        INNER JOIN
            ${db}FuncionariosAcesso
        ON
            ${db}FuncionariosAcesso.IDFuncionario = ${db}Funcionarios.IDFuncionario
        WHERE
            ${db}FuncionariosAcesso.Permitido = 1
        AND
            ${db}Funcionarios.IDFuncionario = ${id}
        AND
            ${db}FuncionariosAcesso.DescricaoAcesso = 'EAD Acesso Online Professor'`,
            res,
            req.originalUrl
        );
    });

    router.get('/getUser', verifyJWT, (req, res) => {
        execSQLQuery(`SELECT
            us.*,
            ut.description
        FROM users us
        inner join usertype ut on ut.idusertype = us.idusertype;`, res, req.originalUrl);
    });

    router.get('/getStudentName/:id/:banco', verifyJWT, (req, res) => {
        id = parseInt(req.params.id);
        const db = req.params.banco;
        execSQLQuery2(`
        select top(1)
            C.Nome
        from
            ${db}TreinandosTurmas TT
        inner join
            ${db}Clientes C
        on
            C.IDCliente = TT.IDTreinando
        where
            TT.IDMatricula = ${id};`,
            res,
            req.originalUrl
        );
    })

    router.get('/SolucaoProblemas', (req, res) => {
        AtualizaConexaoMySQL();
    });


    router.get('/RelatorioAlunosAulasGeral/:idunits', (req, res) => {
        const idunits = parseInt(req.params.idunits);
        let condicao = `un.idunits = ${idunits}`;
        if (idunits == 0)
            condicao = "true";
        const sqlQuery = `select
            *,
            al.Aulas * 2 as 'HorasAssistidas'
        from (
            select
                un.idunits,
                un.unityname as 'Unidade',
                sc.idusers as 'Matricula',
                count(*) as 'Aulas',
                (select date_format(sc1.insert_date, '%d/%m/%Y %H:%i') from stepclass sc1 where sc1.idunits = sc.idunits and sc1.idusers = sc.idusers order by sc1.insert_date desc limit 1) as 'UltimaAula'
            from stepclass sc
            inner join units un on un.idunits = sc.idunits
            where ${condicao}
            group by un.idunits, sc.idusers, un.unityname
            order by un.unityname, sc.idusers asc) al`;
        mysqlConnection.query(sqlQuery, function(error, ListagemAulas, fields) {
            mysqlConnection.query(`select * from units un where ${condicao};`, function(error, ListaUnidades, fields) {
                LoopAlunosSQLServerGeral(ListagemAulas, ListaUnidades, 0, function(NovaListaAulas) {
                    let TabelaHtml = `<html>
                        <head>
                            <meta charset="UTF-8">
                            <title>Relatório de Alunos por Unidade</title>
                        </head>
                        <body>
                            <table>
                                <tr>
                                    <th>Unidade</th>
                                    <th>Matricula</th>
                                    <th>Aulas</th>
                                    <th>Ultima Aula</th>
                                    <th>Horas Assistidas</th>
                                    <th>Aluno</th>
                                    <th>Cancelada</th>
                                    <th>Suspensa</th>
                                </tr>`;
                    for (let i = 0; i < NovaListaAulas.length; i++) {
                        TabelaHtml += `${(i % 2 === 0) ? '<tr>' : '<tr style="background: #eee">'}
                            <td>${NovaListaAulas[i]['Unidade']}</td>
                            <td>${NovaListaAulas[i]['Matricula']}</td>
                            <td>${NovaListaAulas[i]['Aulas']}</td>
                            <td>${NovaListaAulas[i]['UltimaAula']}</td>
                            <td>${NovaListaAulas[i]['HorasAssistidas']}</td>
                            <td>${NovaListaAulas[i]['Aluno']}</td>
                            <td>${NovaListaAulas[i]['Cancelada']}</td>
                            <td>${NovaListaAulas[i]['Suspensa']}</td>
                        </tr>`;
                    }
                    TabelaHtml += `</table></body></html>`;
                    res.send(JSON.stringify({"status": 200, "Dados": NovaListaAulas, "TabelaHtml": TabelaHtml}));
                })
            })
        })
    })

    function LoopAlunosSQLServerGeral(ListagemAulas, ListaUnidades, Index, callback) {
        if (Index < ListaUnidades.length) {
            const sqlQuery = `SELECT
                mt.IDMatricula,
                c.Nome,
                tu.Cancelada,
	            tu.Suspensa
            FROM ${ListaUnidades[Index]['reference_db']}.dbo.MatriculasTreinandos mt
            INNER JOIN ${ListaUnidades[Index]['reference_db']}.dbo.Clientes c ON mt.IDTreinando = c.IDCliente
            INNER JOIN ${ListaUnidades[Index]['reference_db']}.dbo.Turmas tu ON tu.IdTurma = mt.CodigoTurma;`;
            sqlServerConnection.request().query(sqlQuery, function (error, AlunosUnidade, fields) {
                AlunosUnidade = AlunosUnidade['recordset'];
                if (AlunosUnidade && AlunosUnidade.length > 0) {
                    for (let i = 0; i < AlunosUnidade.length; i++) {
                        AlunosUnidade[i]['Encontrado'] = 0;
                        for (let o = 0; o < ListagemAulas.length; o++) {
                            if (ListagemAulas[o]['idunits'] == ListaUnidades[Index]['idunits']) {
                                if (ListagemAulas[o]['Matricula'] == AlunosUnidade[i]['IDMatricula']) {
                                    ListagemAulas[o]['Aluno'] = AlunosUnidade[i]['Nome'];
                                    ListagemAulas[o]['Cancelada'] = AlunosUnidade[i]['Cancelada'];
                                    ListagemAulas[o]['Suspensa'] = AlunosUnidade[i]['Suspensa'];
                                    AlunosUnidade[i]['Encontrado'] = 1;
                                }
                            }
                        }
                        if (AlunosUnidade[i]['Encontrado'] == 0) {
                            ListagemAulas.push({
                                "idunits": ListaUnidades[Index]['idunits'],
                                "Unidade": ListaUnidades[Index]['unityname'],
                                "Matricula": AlunosUnidade[i]['IDMatricula'],
                                "Aulas": 0,
                                "HorasAssistidas": 0,
                                "Aluno": AlunosUnidade[i]['Nome'],
                                "Cancelada": AlunosUnidade[i]['Cancelada'],
                                "Suspensa": AlunosUnidade[i]['Suspensa'],
                            });
                        }
                    }
                }
                Index++;
                LoopAlunosSQLServer(ListagemAulas, ListaUnidades, Index, callback);
            })
        }
        else {
            callback(ListagemAulas);
        }
    }

    router.get('/RelatorioAlunosAulas/:idunits', (req, res) => {
        const idunits = parseInt(req.params.idunits);
        let condicao = `un.idunits = ${idunits}`;
        if (idunits == 0)
            condicao = "true";
        const sqlQuery = `select
            *,
            al.Aulas * 2 as 'HorasAssistidas'
        from (
            select
                un.idunits,
                un.unityname as 'Unidade',
                sc.idusers as 'Matricula',
                cl.idcourses,
                /*(select co2.name from courses co2 where co2.idcourses = cl.idcourses) as 'Curso',*/
                count(*) as 'Aulas',
                (select count(*) from stepclass sc1 where sc1.idunits = un.idunits and sc1.idusers = sc.idusers and sc1.idclasses in (select cl2.idclasses from classes cl2 where cl2.idcourses = cl.idcourses) and sc1.completedClass = 0) as 'Pendentes',
                (select date_format(sc1.insert_date, '%d/%m/%Y %H:%i') from stepclass sc1 where sc1.idunits = sc.idunits and sc1.idusers = sc.idusers order by sc1.insert_date desc limit 1) as 'UltimaAula'
            from stepclass sc
            inner join classes cl on cl.idclasses = sc.idclasses and cl.idcourses in (select co.idcourses from courses co where co.aditional = 0 and co.extracourse = 0)
            inner join units un on un.idunits = sc.idunits
            where ${condicao}
            group by un.idunits, sc.idusers, un.unityname, cl.idcourses
            order by un.unityname, sc.idusers asc) al`;
        mysqlConnection.query(sqlQuery, function(error, ListagemAulas, fields) {
            mysqlConnection.query(`select * from units un where ${condicao};`, function(error, ListaUnidades, fields) { // un.enabled = 1 and
                LoopAlunosSQLServer(ListagemAulas, ListaUnidades, 0, function(NovaListaAulas) {
                    let TabelaHtml = `<html>
                        <head>
                            <meta charset="UTF-8">
                            <title>Relatório de Alunos por Unidade</title>
                        </head>
                        <body>
                            <table>
                                <tr>
                                    <th>Unidade</th>
                                    <th>Matricula</th>
                                    <th>Curso</th>
                                    <th>Aulas</th>
                                    <th>Pendentes</th>
                                    <th>Ultima Aula</th>
                                    <th>Horas Assistidas</th>
                                    <th>Aluno</th>
                                    <th>Cancelada</th>
                                    <th>Suspensa</th>
                                </tr>`;
                    for (let i = 0; i < NovaListaAulas.length; i++) {
                        TabelaHtml += `${(i % 2 === 0) ? '<tr>' : '<tr style="background: #eee">'}
                            <td>${NovaListaAulas[i]['Unidade']}</td>
                            <td>${NovaListaAulas[i]['Matricula']}</td>
                            <td>${NovaListaAulas[i]['Curso']}</td>
                            <td>${NovaListaAulas[i]['Aulas']}</td>
                            <td>${NovaListaAulas[i]['Pendentes']}</td>
                            <td>${NovaListaAulas[i]['UltimaAula']}</td>
                            <td>${NovaListaAulas[i]['HorasAssistidas']}</td>
                            <td>${NovaListaAulas[i]['Aluno']}</td>
                            <td>${NovaListaAulas[i]['Cancelada']}</td>
                            <td>${NovaListaAulas[i]['Suspensa']}</td>
                        </tr>`;
                    }
                    TabelaHtml += `</table></body></html>`;
                    res.send(JSON.stringify({"status": 200, "Dados": NovaListaAulas, "TabelaHtml": TabelaHtml}));
                })
            })
        })
    })

    function LoopAlunosSQLServer(ListagemAulas, ListaUnidades, Index, callback) {
        if (Index < ListaUnidades.length) {
            const sqlQuery = `SELECT distinct
                mt.IDMatricula,
                c.Nome,
                tu.IdTurma,
                tu.Cancelada,
                tu.Suspensa,
                tc.CodigoCurso,
                cu.NomeVersao as 'Curso',
                (select count(*) from ${ListaUnidades[Index]['reference_db']}.dbo.Chamadas ch where ch.IDTurma = tu.IdTurma and ch.IDCurso = cu.Codigo) as 'Chamadas',
                (select count(*) from ${ListaUnidades[Index]['reference_db']}.dbo.TurmasCalendario tc1 where tc1.IDTurma = tu.IdTurma and tc1.CodigoCurso = cu.Codigo and tc1.Ministrado = 1) as 'Ministradas'
            FROM ${ListaUnidades[Index]['reference_db']}.dbo.MatriculasTreinandos mt
            INNER JOIN ${ListaUnidades[Index]['reference_db']}.dbo.Clientes c ON mt.IDTreinando = c.IDCliente
            LEFT JOIN ${ListaUnidades[Index]['reference_db']}.dbo.Turmas tu ON tu.IdTurma = mt.CodigoTurma
            left join ${ListaUnidades[Index]['reference_db']}.dbo.TurmasCalendario tc on tc.IdTurma = tu.IdTurma
            left join ${ListaUnidades[Index]['reference_db']}.dbo.Cursos cu on cu.Codigo = tc.CodigoCurso
            order by mt.IDMatricula asc;`;
            sqlServerConnection.request().query(sqlQuery, function (error, AlunosUnidade, fields) {
                AlunosUnidade = AlunosUnidade['recordset'];
                if (AlunosUnidade && AlunosUnidade.length > 0) {
                    //console.log(AlunosUnidade);
                    for (let i = 0; i < AlunosUnidade.length; i++) {
                        AlunosUnidade[i]['Encontrado'] = 0;
                        for (let o = 0; o < ListagemAulas.length; o++) {
                            if (ListagemAulas[o]['idunits'] == ListaUnidades[Index]['idunits'] && ListagemAulas[o]['Matricula'] == AlunosUnidade[i]['IDMatricula']) {
                                ListagemAulas[o]['Aluno'] = AlunosUnidade[i]['Nome'];
                                ListagemAulas[o]['Cancelada'] = (AlunosUnidade[i]['Cancelada'] != null) ? AlunosUnidade[i]['Cancelada'] : true;
                                ListagemAulas[o]['Suspensa'] = (AlunosUnidade[i]['Suspensa'] != null) ? AlunosUnidade[i]['Suspensa'] : true;
                                if (ListagemAulas[o]['idcourses'] == AlunosUnidade[i]['CodigoCurso']) {
                                    ListagemAulas[o]['Curso'] = AlunosUnidade[i]['Curso'];
                                    ListagemAulas[o]['Chamadas'] = AlunosUnidade[i]['Chamadas'];
                                    ListagemAulas[o]['Ministradas'] = AlunosUnidade[i]['Ministradas'];
                                    AlunosUnidade[i]['Encontrado'] = 1;
                                }
                            }
                        }
                        if (AlunosUnidade[i]['Encontrado'] == 0) {
                            ListagemAulas.push({
                                "idunits": ListaUnidades[Index]['idunits'],
                                "Unidade": ListaUnidades[Index]['unityname'],
                                "Matricula": AlunosUnidade[i]['IDMatricula'],
                                "Curso": AlunosUnidade[i]['Curso'],
                                "Aulas": 0,
                                "Pendentes": 0,
                                "HorasAssistidas": 0,
                                "Aluno": AlunosUnidade[i]['Nome'],
                                "Cancelada": AlunosUnidade[i]['Cancelada'],
                                "Suspensa": AlunosUnidade[i]['Suspensa'],
                                "Chamadas": 0,
                                "Ministradas": 0,
                            });
                        }
                    }
                }
                Index++;
                LoopAlunosSQLServer(ListagemAulas, ListaUnidades, Index, callback);
            })
        }
        else {
            callback(ListagemAulas);
        }
    }

    router.post('/insertLog', (req, res) => {
        const desc = req.body.desc;
        console.log(desc);
        execSQLQuery(`insert into logs_general (description)
        values (?);`, res, req.originalUrl, [desc]);
    });

    router.post('/transferirMatriculas', verifyJWT, (req, res) => {
        const Matricula = parseInt(req.body.Matricula);
        const idunitsD = parseInt(req.body.idunitsD);
        const dbD = req.body.dbD;
        const idunitsP = parseInt(req.body.idunitsP);
        const dbP = req.body.dbP;

        // JUNTAR DADOS DO ALUNO A SER TRANSFERIDO
        const SQLDadosAluno = `select distinct
            tt.CodigoTurma,
            mt.IDClienteResponsavel,
            tt.IDTreinando,
            pm.IDPreMatricula,
            tt.IDMatricula
        from ${dbD}TreinandosTurmas tt
        inner join ${dbD}Matriculas mt on mt.IDMatricula = tt.IDMatricula
        inner join ${dbD}PreMatricula pm on pm.IDPreMatricula = mt.IDPreMatricula
        where tt.IDMatricula = ${Matricula};`;
        //console.log(SQLDadosAluno);
        sqlServerConnection.request().query(SQLDadosAluno, function (error, DadosAluno, fields) {
            DadosAluno = DadosAluno['recordset'][0];
            // CRIAR E ABASTECER AS TABELAS A SEREM MIGRADAS
            AdicionarTabelas(dbD, dbP, DadosAluno, function(success) {
                // MONTAR E EXECUTAR SQL DE INSERT
                MontaSQLInserts(dbP, DadosAluno, 0, 0, function(Sucesso) {
                    // CASO DE ALGUM ERRO NO PROCESSO, PARA A EXECUCAO
                    if (!Sucesso) {
                        res.send(DadosAluno);
                        return;
                    }
                    // REMOVER DADOS DUPLICADOS PARA MATRICULAS TREINANDOS
                    const SQLRemoverDuplicados = `SELECT
                        IDMatricula,
                        IDTreinando,
                        CodigoTurma,
                        COUNT(*) AS CNT
                    FROM ${dbP}MatriculasTreinandos
                    GROUP BY IDMatricula, IDTreinando, CodigoTurma
                    HAVING COUNT(*) > 1;`;
                    sqlServerConnection.request().query(SQLRemoverDuplicados, function (error, results, fields) {
                        console.log("Consulta de duplicados concluida com SUCESSO");
                        const Duplicados = results['recordset'];
                        let SQLDeleteDup = `delete from ${dbP}MatriculasTreinandos where `;
                        let SQLInsertDup = `insert into ${dbP}MatriculasTreinandos (IDMatricula, IDTreinando, CodigoTurma) values `;
                        for (let i = 0; i < Duplicados.length; i++) {
                            if (i > 0) {
                                SQLInsertDup += `, `;
                                SQLDeleteDup += ` or `;
                            }
                            SQLInsertDup += `(${Duplicados[i]['IDMatricula']}, ${Duplicados[i]['IDTreinando']}, ${Duplicados[i]['CodigoTurma']})`;
                            SQLDeleteDup += `(IDMatricula = ${Duplicados[i]['IDMatricula']} and IDTreinando = ${Duplicados[i]['IDTreinando']} and CodigoTurma = ${Duplicados[i]['CodigoTurma']})`;
                        }
                        // REMOVER DUPLICADOS
                        sqlServerConnection.request().query(SQLDeleteDup, function (error, results2, fields) {
                            if (error) console.log("ERRO ao remover duplicados");
                            else console.log("Remover duplicados concluido com SUCESSO");
                            // INSERIR REGISTROS CORRETOS
                            sqlServerConnection.request().query(SQLInsertDup, function (error, results2, fields) {
                                if (error) console.log("ERRO ao incluir TreinandosTurmas");
                                else console.log("Incluir TreinandosTurmas concluido com SUCESSO");
                                // ATUALIZAR UNIDADE E MATRICULA NA TABELA STEPCLASS
                                const SQLStepClass = `update stepclass set idunits = ${idunitsP}, idusers = ${DadosAluno.Tabelas[21].Dados[0]['IDMatricula']} where idunits = ${idunitsD} and idusers = ${Matricula};`;
                                mysqlConnection.query(SQLStepClass, function (errorSC, resultsSC, fieldsSC) {
                                    if (errorSC) console.log("ERRO ao atualizar STEPCLASS");
                                    else console.log("STEPCLASS atualizado com SUCESSO");
                                    // ATUALIZAR UNIDADE E MATRICULA NA TABELA USERGIVENASNWERS
                                    const SQLUserGivenAnswers = `update usergivenanswers set idunits = ${idunitsP}, idusers = ${DadosAluno.Tabelas[21].Dados[0]['IDMatricula']} where idunits = ${idunitsD} and idusers = ${Matricula};`;
                                    mysqlConnection.query(SQLUserGivenAnswers, function (errorUG, resultsUG, fieldsUG) {
                                        if (errorUG) console.log("ERRO ao atualizar USERGIVENANSWERS");
                                        else console.log("USERGIVENANSWERS atualizado com SUCESSO");
                                        // ATUALIZAR UNIDADE E MATRICULA NA TABELA USERSTAGES
                                        const SQLUserStages = `update userstages set idunits = ${idunitsP}, idusers = ${DadosAluno.Tabelas[21].Dados[0]['IDMatricula']} where idunits = ${idunitsD} and idusers = ${Matricula};`;
                                        mysqlConnection.query(SQLUserStages, function (errorUS, resultsUS, fieldsUS) {
                                            if (errorUS) console.log("ERRO ao atualizar USERSTAGES");
                                            else console.log("USERSTAGES atualizado com SUCESSO");
                                            // ATUALIZAR UNIDADE E MATRICULA NA TABELA QRCODES
                                            const SQLQRCode = `update qrcodes set idunits = ${idunitsP}, idusers = ${DadosAluno.Tabelas[21].Dados[0]['IDMatricula']} where idunits = ${idunitsD} and idusers = ${Matricula};`;
                                            mysqlConnection.query(SQLQRCode, function (errorQR, resultsQR, fieldsQR) {
                                                if (errorQR) console.log("ERRO ao atualizar QRCODE");
                                                else console.log("QRCODE atualizado com SUCESSO");
                                                // ATUALIZAR UNIDADE E MATRICULA NA TABELA BARCODES
                                                const SQLBarCode = `update barcodes set idunits = ${idunitsP}, idusers = ${DadosAluno.Tabelas[21].Dados[0]['IDMatricula']} where idunits = ${idunitsD} and idusers = ${Matricula};`;
                                                mysqlConnection.query(SQLBarCode, function (errorBC, resultsBC, fieldsBC) {
                                                    if (errorBC) console.log("ERRO ao atualizar BARCODE");
                                                    else console.log("BARCODE atualizado com SUCESSO");
                                                    // TRANCAR ALUNO NA UNIDADE ANTERIOR
                                                    const SQLPreRequerimento = `insert into ${dbD}PreRequerimento (IDTreinando, IDMatricula, Data, IDFuncionario, IDTurma, TipoRequerimento, TipoRequerimentoAnt, Status, IDMotivo, DescricaoMotivo) values
                                                    (${DadosAluno.IDTreinando}, ${DadosAluno.IDMatricula}, '2021-02-19', 1250, 0, 'T', 'T', 'C', 6, 'Aluno transferido para outra unidade');`;
                                                    sqlServerConnection.request().query(SQLPreRequerimento, function (error, resPR, fields) {
                                                        if (error) console.log("ERRO ao criar PreRequerimento");
                                                        else console.log("PreRequerimento criado com sucesso");
                                                        const SQLGetIDs = `select
                                                        coalesce((select top(1) IdTransferencia from ${dbD}Transferencias order by IdTransferencia desc), 0) + 1 as 'IDTransferencia',
                                                        coalesce((select top(1) IDPreRequerimento from ${dbD}PreRequerimento order by IDPreRequerimento desc), 1) as 'IDPreRequerimento';`;
                                                        sqlServerConnection.request().query(SQLGetIDs, function (error, resIDs, fields) {
                                                            if (error) console.log("ERRO ao reunir IDs para Transferencia");
                                                            else console.log("Reunido IDs para Transferencia com SUCESSO");
                                                            const IDs = resIDs['recordset'][0];
                                                            const SQLTransferencia = `insert into ${dbD}Transferencias (IdTransferencia, IDTreinando, IDMatricula, DataTransferencia, Motivo, CobrarTaxa, ValorTaxa, VencimentoTaxa, FuncionarioResponsavel, ObsAviso, TipoTransferencia, ParcelasTaxa, IDMotivo, IDPreRequerimento) values
                                                            (${IDs.IDTransferencia}, ${DadosAluno.IDTreinando}, ${DadosAluno.IDMatricula}, '2021-02-19', 'Aluno transferido para outra unidade', 0, 0, '2021-02-19', 1250, '', 'T', 1, 6, ${IDs.IDPreRequerimento});`;
                                                            sqlServerConnection.request().query(SQLTransferencia, function (error, resTransf, fields) {
                                                                if (error) console.log("ERRO ao criar Transferencia");
                                                                else console.log("Transferencia criada com sucesso");
                                                                let SQLTransfDet = `insert into ${dbD}TransferenciasDetalhe (IDTransferencia, IDCurso, CodigoTurma, IDTreinando) values `;
                                                                for (let i = 0; i < DadosAluno.Tabelas[22].Dados.length; i++) {
                                                                    if (i > 0)
                                                                        SQLTransfDet += `, `;
                                                                    SQLTransfDet += `(${IDs.IDTransferencia}, ${DadosAluno.Tabelas[22].Dados[i].IDCurso}, ${DadosAluno.CodigoTurma}, ${DadosAluno.IDTreinando})`;
                                                                }
                                                                sqlServerConnection.request().query(SQLTransfDet, function (error, resTransfDet, fields) {
                                                                    if (error) console.log("ERRO ao criar TransferenciaDetalhes");
                                                                    else console.log("TransferenciaDetalhes criado com sucesso");
                                                                    const SQLRemoverTurma = `update ${dbD}TreinandosTurmas set CodigoTurma = 0 where IDMatricula = ${DadosAluno.IDMatricula} and IDTreinando = ${DadosAluno.IDTreinando};`;
                                                                    sqlServerConnection.request().query(SQLRemoverTurma, function (error, resRemTurma, fields) {
                                                                        if (error) console.log("ERRO ao setar CodigoTurma = 0 em TreinandosTurmas");
                                                                        else console.log("CodigoTurma = 0 realizado com sucesso");
                                                                        res.send(DadosAluno);
                                                                    });
                                                                });
                                                            });
                                                        });
                                                    });
                                                });
                                            });
                                        });
                                    });
                                });
                            });
                        });
                    });
                });
            });

        });

    });

    function AdicionarTabelas(dbD, dbP, DadosAluno, callback) {
        let Tabelas = [];
        let Campos0 = [{"Forcar": true, "CampoP": "IDFuncionario", "Valor": 1250}];
        PreencheDadosTabela(dbD, dbP, 0, "Turmas", "IdTurma", `IdTurma = ${DadosAluno['CodigoTurma']}`, Tabelas, Campos0, true, function(Success) {
            let Campos1 = [{"Forcar": true, "CampoP": "IDAtendente", "Valor": 1250}];
            PreencheDadosTabela(dbD, dbP, 0, "Atendimentos", "IdAtendimento", `select at.* from ${dbD}Atendimentos at inner join ${dbD}Matriculas mt on mt.IDAtendimento = at.IdAtendimento where mt.IDMatricula = ${DadosAluno['IDMatricula']};`, Tabelas, Campos1, true, function(Success) {
                let Campos2 = [{"IdxT": 1, "IdxD": 0, "CampoD": "IdAtendimento", "CampoP": "IdAtendimento", "Seq": true}, {"Forcar": true, "CampoP": "IDFuncionarioCadastro", "Valor": 1250}];
                PreencheDadosTabela(dbD, dbP, 0, "AtendimentosCabecalho", "", `select ac.* from ${dbD}AtendimentosCabecalho ac inner join ${dbD}Matriculas mt on mt.IDAtendimento = ac.IdAtendimento where mt.IDMatricula = ${DadosAluno['IDMatricula']};`, Tabelas, Campos2, true, function(Success) {
                    let Campos3 = [{"IdxT": 1, "IdxD": 0, "CampoD": "IdAtendimento", "CampoP": "IDAtendimento", "Seq": true}, {"Forcar": true, "CampoP": "IDAtendente", "Valor": 1250}];
                    PreencheDadosTabela(dbD, dbP, 0, "AtendimentosComissoes", "", `select ac.* from ${dbD}AtendimentosComissoes ac inner join ${dbD}Matriculas mt on mt.IDAtendimento = ac.IdAtendimento where mt.IDMatricula = ${DadosAluno['IDMatricula']};`, Tabelas, Campos3, true, function(Success) {
                        let Campos4 = [{"IdxT": 0, "IdxD": 0, "CampoD": "IdTurma", "CampoP": "IdTurma", "Seq": false}, {"Forcar": true, "CampoP": "IDFuncionarioInstrutor", "Valor": 1250}];
                        PreencheDadosTabela(dbD, dbP, 0, "AtendimentosFuncionarios", "", `IdTurma = ${DadosAluno['CodigoTurma']}`, Tabelas, Campos4, false, function(Success) {
                            let Campos5 = [{"IdxT": 0, "IdxD": 0, "CampoD": "IdTurma", "CampoP": "IDTurma", "Seq": false}];
                            PreencheDadosTabela(dbD, dbP, 0, "Notas", "", `IDTurma = ${DadosAluno['CodigoTurma']}`, Tabelas, Campos5, true, function(Success) {
                                let Campos6 = [{"IdxT": 0, "IdxD": 0, "CampoD": "IdTurma", "CampoP": "IdTurma", "Seq": false}, {"Forcar": true, "CampoP": "IDFuncionario", "Valor": 1250}];
                                PreencheDadosTabela(dbD, dbP, 0, "TransacoesTurmas", "IDTransacao", `IdTurma = ${DadosAluno['CodigoTurma']}`, Tabelas, Campos6, true, function(Success) {
                                    let Campos7 = [{"IdxT": 0, "IdxD": 0, "CampoD": "IdTurma", "CampoP": "IdTurma", "Seq": false}, {"Forcar": true, "CampoP": "CodigoInstrutor", "Valor": 1250}];
                                    PreencheDadosTabela(dbD, dbP, 0, "TransacoesTurmasCalendario", "", `IdTurma = ${DadosAluno['CodigoTurma']}`, Tabelas, Campos7, true, function(Success) {
                                        let Campos8 = [{"IdxT": 0, "IdxD": 0, "CampoD": "IdTurma", "CampoP": "IDTurma", "Seq": false}];
                                        PreencheDadosTabela(dbD, dbP, 0, "TransacoesTurmasDetalhe", "", `IdTurma = ${DadosAluno['CodigoTurma']}`, Tabelas, Campos8, true, function(Success) {
                                            let Campos9 = [{"IdxT": 0, "IdxD": 0, "CampoD": "IdTurma", "CampoP": "IdTurma", "Seq": false}, {"Forcar": true, "CampoP": "IdInstrutor", "Valor": 1250}];
                                            PreencheDadosTabela(dbD, dbP, 0, "TurmasAgendaCalendario", "IdTurmasAgendaCalendario", `IdTurma = ${DadosAluno['CodigoTurma']}`, Tabelas, Campos9, true, function(Success) {
                                                let Campos10 = [{"IdxT": 0, "IdxD": 0, "CampoD": "IdTurma", "CampoP": "IDTurma", "Seq": false}];
                                                PreencheDadosTabela(dbD, dbP, 0, "TurmasDetalhe", "", `IdTurma = ${DadosAluno['CodigoTurma']}`, Tabelas, Campos10, true, function(Success) {
                                                    let Campos11 = [{"IdxT": 0, "IdxD": 0, "CampoD": "IdTurma", "CampoP": "IdTurma", "Seq": false}, {"Forcar": true, "CampoP": "CodigoInstrutor", "Valor": 1250}];
                                                    PreencheDadosTabela(dbD, dbP, 0, "TurmasCalendario", "IdTurmasCalendario", `IdTurma = ${DadosAluno['CodigoTurma']}`, Tabelas, Campos11, true, function(Success) {
                                                        let Campos12 = [{"Forcar": true, "CampoP": "FuncionarioCadastro", "Valor": 1250}];
                                                        PreencheDadosTabela(dbD, dbP, 0, "Clientes", "IDCliente", `IDCliente in (${DadosAluno['IDClienteResponsavel']}, ${DadosAluno['IDTreinando']});`, Tabelas, Campos12, true, function(Success) {
                                                            let Campos13 = [{"IdxT": 12, "IdxD": 0, "CampoD": "IDCliente", "CampoP": "IDResponsavel", "Seq": false}, {"Forcar": true, "CampoP": "IDFuncionario", "Valor": 1250}];
                                                            PreencheDadosTabela(dbD, dbP, 0, "PreMatricula", "IDPreMatricula", `IDPreMatricula = ${DadosAluno['IDPreMatricula']}`, Tabelas, Campos13, true, function(Success) {
                                                                let Campos14 = [{"IdxT": 13, "IdxD": 0, "CampoD": "IDPreMatricula", "CampoP": "IDPreMatricula", "Seq": false}, {"IdxT": 12, "IdxD": (Tabelas[12].Dados.length - 1), "CampoD": "IDCliente", "CampoP": "IDTreinando", "Seq": false}];
                                                                PreencheDadosTabela(dbD, dbP, 0, "PreMatriculaTreinandos", "", `IDPreMatricula = ${DadosAluno['IDPreMatricula']}`, Tabelas, Campos14, true, function(Success) {
                                                                    let Campos15 = [{"IdxT": 13, "IdxD": 0, "CampoD": "IDPreMatricula", "CampoP": "IDPreMatricula", "Seq": false}];
                                                                    PreencheDadosTabela(dbD, dbP, 0, "PreMatriculaParcelas", "", `IDPreMatricula = ${DadosAluno['IDPreMatricula']}`, Tabelas, Campos15, true, function(Success) {
                                                                        let Campos16 = [{"IdxT": 13, "IdxD": 0, "CampoD": "IDPreMatricula", "CampoP": "IDPreMatricula", "Seq": false}];
                                                                        PreencheDadosTabela(dbD, dbP, 0, "PreMatriculaFinanceiroMaterial", "", `IDPreMatricula = ${DadosAluno['IDPreMatricula']}`, Tabelas, Campos16, true, function(Success) {
                                                                            let Campos17 = [{"IdxT": 13, "IdxD": 0, "CampoD": "IDPreMatricula", "CampoP": "IDPreMatricula", "Seq": false}, {"IdxT": 0, "IdxD": 0, "CampoD": "IdTurma", "CampoP": "IDTurma", "Seq": false}];
                                                                            PreencheDadosTabela(dbD, dbP, 0, "PreMatriculaCursos", "", `IDPreMatricula = ${DadosAluno['IDPreMatricula']}`, Tabelas, Campos17, true, function(Success) {
                                                                                let Campos18 = [{"IdxT": 0, "IdxD": 0, "CampoD": "IdTurma", "CampoP": "IDTurma", "Seq": false}, {"Forcar": true, "CampoP": "IDInstrutor", "Valor": 1250}, {"Forcar": true, "CampoP": "IDFuncionarioAPP", "Valor": 1250}];
                                                                                PreencheDadosTabela(dbD, dbP, 0, "Chamadas", "IDChamadas", `IDTurma = ${DadosAluno['CodigoTurma']}`, Tabelas, Campos18, true, function(Success) {
                                                                                    let Campos19 = [{"IdxT": 18, "IdxD": 0, "CampoD": "IDChamadas", "CampoP": "IDChamadas", "Seq": true}];
                                                                                    PreencheDadosTabela(dbD, dbP, 0, "ChamadasConteudos", "", `select cd.* from ${dbD}Chamadas ch inner join ${dbD}ChamadasConteudos cd on cd.IDChamadas = ch.IDChamadas where ch.IDTurma = ${DadosAluno['CodigoTurma']}`, Tabelas, Campos19, true, function(Success) {
                                                                                        let Campos20 = [{"IdxT": 18, "IdxD": 0, "CampoD": "IDChamadas", "CampoP": "IDChamadas", "Seq": true}, {"IdxT": 12, "IdxD": (Tabelas[12].Dados.length - 1), "CampoD": "IDCliente", "CampoP": "IDTreinando", "Seq": false}];
                                                                                        PreencheDadosTabela(dbD, dbP, 0, "ChamadasDetalhes", "", `select cd.* from ${dbD}Chamadas ch inner join ${dbD}ChamadasDetalhes cd on cd.IDChamadas = ch.IDChamadas where ch.IDTurma = ${DadosAluno['CodigoTurma']}`, Tabelas, Campos20, true, function(Success) {
                                                                                            let Campos21 = [{"IdxT": 0, "IdxD": 0, "CampoD": "IdTurma", "CampoP": "CodigoTurma", "Seq": false}, {"IdxT": 12, "IdxD": 0, "CampoD": "IDCliente", "CampoP": "IDClienteResponsavel", "Seq": false}, {"IdxT": 1, "IdxD": 0, "CampoD": "IdAtendimento", "CampoP": "IDAtendimento", "Seq": false}, {"IdxT": 13, "IdxD": 0, "CampoD": "IDPreMatricula", "CampoP": "IDPreMatricula", "Seq": false}, {"Forcar": true, "CampoP": "FuncionarioVenda", "Valor": 1250}, {"Forcar": true, "CampoP": "FuncionarioCadastro", "Valor": 1250}];
                                                                                            let Ano = (new Date().getFullYear() - 2000) * 10000;
                                                                                            PreencheDadosTabela(dbD, dbP, Ano, "Matriculas", "IDMatricula", `IDMatricula = ${DadosAluno['IDMatricula']}`, Tabelas, Campos21, true, function(Success) {
                                                                                                let Campos22 = [{"IdxT": 21, "IdxD": 0, "CampoD": "IDMatricula", "CampoP": "IDMatricula", "Seq": false}, {"IdxT": 12, "IdxD": (Tabelas[12].Dados.length - 1), "CampoD": "IDCliente", "CampoP": "IDTreinando", "Seq": false}, {"IdxT": 0, "IdxD": 0, "CampoD": "IdTurma", "CampoP": "CodigoTurma", "Seq": false}];
                                                                                                PreencheDadosTabela(dbD, dbP, 0, "TreinandosTurmas", "", `IDMatricula = ${DadosAluno['IDMatricula']}`, Tabelas, Campos22, true, function(Success) {
                                                                                                    let Campos23 = [{"IdxT": 21, "IdxD": 0, "CampoD": "IDMatricula", "CampoP": "IDMatricula", "Seq": false}, {"IdxT": 12, "IdxD": (Tabelas[12].Dados.length - 1), "CampoD": "IDCliente", "CampoP": "IDTreinando", "Seq": false}, {"IdxT": 0, "IdxD": 0, "CampoD": "IdTurma", "CampoP": "IDTurma", "Seq": false}];
                                                                                                    PreencheDadosTabela(dbD, dbP, 0, "TreinandosTurmasMateriais", "", `IDMatricula = ${DadosAluno['IDMatricula']}`, Tabelas, Campos23, true, function(Success) {
                                                                                                        let Campos24 = [{"Ignorar": true, "CampoP": "NumeroProva"}, {"IdxT": 21, "IdxD": 0, "CampoD": "IDMatricula", "CampoP": "IDMatricula", "Seq": false}, {"IdxT": 12, "IdxD": (Tabelas[12].Dados.length - 1), "CampoD": "IDCliente", "CampoP": "IDTreinando", "Seq": false}, {"IdxT": 0, "IdxD": 0, "CampoD": "IdTurma", "CampoP": "IDTurma", "Seq": false}];
                                                                                                        PreencheDadosTabela(dbD, dbP, 0, "NotasDetalhesSequencial", "NumeroProva", `IDMatricula = ${DadosAluno['IDMatricula']}`, Tabelas, Campos24, true, function(Success) {
                                                                                                            let Campos25 = [{"IdxT": 21, "IdxD": 0, "CampoD": "IDMatricula", "CampoP": "IDMatricula", "Seq": false}, {"IdxT": 12, "IdxD": (Tabelas[12].Dados.length - 1), "CampoD": "IDCliente", "CampoP": "IDTreinando", "Seq": false}, {"IdxT": 0, "IdxD": 0, "CampoD": "IdTurma", "CampoP": "IDTurma", "Seq": false}];
                                                                                                            PreencheDadosTabela(dbD, dbP, 0, "NotasDetalhes", "", `IDMatricula = ${DadosAluno['IDMatricula']}`, Tabelas, Campos25, true, function(Success) {
                                                                                                                let Campos26 = [{"IdxT": 21, "IdxD": 0, "CampoD": "IDMatricula", "CampoP": "IDMatricula", "Seq": false}, {"IdxT": 12, "IdxD": (Tabelas[12].Dados.length - 1), "CampoD": "IDCliente", "CampoP": "IDTreinando", "Seq": false}, {"IdxT": 0, "IdxD": 0, "CampoD": "IdTurma", "CampoP": "CodigoTurma", "Seq": false}];
                                                                                                                PreencheDadosTabela(dbD, dbP, 0, "MatriculasTreinandos", "", `IDMatricula = ${DadosAluno['IDMatricula']}`, Tabelas, Campos26, true, function(Success) {
                                                                                                                    let Campos27 = [{"IdxT": 21, "IdxD": 0, "CampoD": "IDMatricula", "CampoP": "IDMatricula", "Seq": false}];
                                                                                                                    PreencheDadosTabela(dbD, dbP, 0, "MatriculasProdutos", "", `IDMatricula = ${DadosAluno['IDMatricula']}`, Tabelas, Campos27, true, function(Success) {
                                                                                                                        let Campos28 = [{"IdxT": 21, "IdxD": 0, "CampoD": "IDMatricula", "CampoP": "IDMatricula", "Seq": false}, {"IdxT": 0, "IdxD": 0, "CampoD": "IdTurma", "CampoP": "CodigoTurma", "Seq": false}];
                                                                                                                        PreencheDadosTabela(dbD, dbP, 0, "MatriculasCursos", "", `IDMatricula = ${DadosAluno['IDMatricula']}`, Tabelas, Campos28, true, function(Success) {
                                                                                                                            let Campos29 = [{"IdxT": 21, "IdxD": 0, "CampoD": "IDMatricula", "CampoP": "IDMatricula", "Seq": false}, {"IdxT": 12, "IdxD": (Tabelas[12].Dados.length - 1), "CampoD": "IDCliente", "CampoP": "IDTreinando", "Seq": false}, {"IdxT": 0, "IdxD": 0, "CampoD": "IdTurma", "CampoP": "CodigoTurma", "Seq": false}];
                                                                                                                            PreencheDadosTabela(dbD, dbP, 0, "HistoricoTreinandosTurmas", "", `IDMatricula = ${DadosAluno['IDMatricula']}`, Tabelas, Campos29, true, function(Success) {
                                                                                                                                let Campos30 = [{"IdxT": 21, "IdxD": 0, "CampoD": "IDMatricula", "CampoP": "IDMatricula", "Seq": false}, {"IdxT": 12, "IdxD": 0, "CampoD": "IDCliente", "CampoP": "IDClienteResponsavel", "Seq": false}, {"IdxT": 0, "IdxD": 0, "CampoD": "IdTurma", "CampoP": "CodigoTurma", "Seq": false}, {"Forcar": true, "CampoP": "FuncionarioCadastro", "Valor": 1250}];
                                                                                                                                PreencheDadosTabela(dbD, dbP, 0, "HistoricoMatriculas", "", `IDMatricula = ${DadosAluno['IDMatricula']}`, Tabelas, Campos30, true, function(Success) {
                                                                                                                                    let Campos31 = [{"IdxT": 21, "IdxD": 0, "CampoD": "IDMatricula", "CampoP": "IDMatricula", "Seq": false}, {"IdxT": 12, "IdxD": (Tabelas[12].Dados.length - 1), "CampoD": "IDCliente", "CampoP": "IDTreinando", "Seq": false}, {"IdxT": 0, "IdxD": 0, "CampoD": "IdTurma", "CampoP": "IDTurma", "Seq": false}, {"Forcar": true, "CampoP": "IDFuncionario", "Valor": 1250}];
                                                                                                                                    PreencheDadosTabela(dbD, dbP, 0, "HistoricoFaltas", "IDHistFalta", `IDMatricula = ${DadosAluno['IDMatricula']}`, Tabelas, Campos31, true, function(Success) {
                                                                                                                                        let Campos32 = [{"IdxT": 21, "IdxD": 0, "CampoD": "IDMatricula", "CampoP": "IDMatricula", "Seq": false}, {"IdxT": 12, "IdxD": (Tabelas[12].Dados.length - 1), "CampoD": "IDCliente", "CampoP": "IDTreinando", "Seq": false}, {"IdxT": 0, "IdxD": 0, "CampoD": "IdTurma", "CampoP": "IDTurma", "Seq": false}, {"Forcar": true, "CampoP": "IDFuncionario", "Valor": 1250}];
                                                                                                                                        PreencheDadosTabela(dbD, dbP, 0, "HistoricoEvadidos", "", `IDMatricula = ${DadosAluno['IDMatricula']}`, Tabelas, Campos32, true, function(Success) {
                                                                                                                                            let Campos33 = [{"IdxT": 21, "IdxD": 0, "CampoD": "IDMatricula", "CampoP": "IDMatricula", "Seq": false}, {"IdxT": 12, "IdxD": (Tabelas[12].Dados.length - 1), "CampoD": "IDCliente", "CampoP": "IDTreinando", "Seq": false}, {"IdxT": 0, "IdxD": 0, "CampoD": "IdTurma", "CampoP": "IDTurma", "Seq": false}];
                                                                                                                                            PreencheDadosTabela(dbD, dbP, 0, "Evadidos", "", `IDMatricula = ${DadosAluno['IDMatricula']}`, Tabelas, Campos33, true, function(Success) {
                                                                                                                                                let Campos34 = [{"IdxT": 21, "IdxD": 0, "CampoD": "IDMatricula", "CampoP": "IDMatricula", "Seq": false}, {"IdxT": 12, "IdxD": 0, "CampoD": "IDCliente", "CampoP": "Cliente", "Seq": false}];
                                                                                                                                                PreencheDadosTabela(dbD, dbP, 0, "ContasReceber", "IDContasReceber", `IDMatricula = ${DadosAluno['IDMatricula']}`, Tabelas, Campos34, true, function(Success) {
                                                                                                                                                    DadosAluno['Tabelas'] = Tabelas;
                                                                                                                                                    callback(true);
                                                                                                                                                });
                                                                                                                                            });
                                                                                                                                        });
                                                                                                                                    });
                                                                                                                                });
                                                                                                                            });
                                                                                                                        });
                                                                                                                    });
                                                                                                                });
                                                                                                            });
                                                                                                        });
                                                                                                    });
                                                                                                });
                                                                                            });
                                                                                        });
                                                                                    });
                                                                                });
                                                                            });
                                                                        });
                                                                    });
                                                                });
                                                            });
                                                        });
                                                    });
                                                });
                                            });
                                        });
                                    });
                                });
                            });
                        });
                    });
                });
            });

        });
    }

    function PreencheDadosTabela(dbD, dbP, AcrescimoID, NomeTabela, IDTabela, Consulta, Tabelas, CamposReplace, ErroCritico, callback) {
        let Query = ``;
        if (Consulta[0] == "s")
            Query = Consulta;
        else
            Query = `select * from ${dbD}${NomeTabela} where ${Consulta};`
        sqlServerConnection.request().query(Query, function (error, Dados, fields) {
            const SQLID = (IDTabela != "") ? `select top(1) (${IDTabela} + 1) as 'ID' from ${dbP}${NomeTabela} order by ${IDTabela} desc;` : "select 0 as 'ID';";
            sqlServerConnection.request().query(SQLID, function (error, ID, fields) {
                ID = (ID['recordset'].length > 0) ? ID['recordset'][0]['ID'] : (1 + AcrescimoID);
                if (Dados) {
                    Dados = Dados['recordset'];

                    for (let i = 0; i < Dados.length; i++) {
                        if (IDTabela != "")
                            Dados[i][IDTabela] = (ID + i);
                        for (let o = 0; o < CamposReplace.length; o++) {
                            if (CamposReplace[o].Forcar) {
                                Dados[i][CamposReplace[o].CampoP] = CamposReplace[o].Valor;
                            }
                            else if (!CamposReplace[o].Ignorar) {
                                Dados[i][CamposReplace[o].CampoP] = Tabelas[CamposReplace[o].IdxT].Dados[CamposReplace[o].IdxD][CamposReplace[o].CampoD] + ((Dados.length > 1 && CamposReplace[o].Seq) ? i : 0);
                            }
                        }
                    }

                    Tabelas.push({"Tabela": NomeTabela, "IDTabela": IDTabela, "Dados": Dados, "ProxID": ID, "SQL": "", "CamposReplace": CamposReplace, "ErroCritico": ErroCritico});
                    console.log(`Tabela ${NomeTabela} consultada com sucesso`);
                }
                else {
                    Tabelas.push({"Tabela": NomeTabela, "IDTabela": IDTabela, "Dados": [], "ProxID": 0, "SQL": "", "CamposReplace": CamposReplace, "ErroCritico": ErroCritico});
                    console.log(`Erro ao consultar tabela ${NomeTabela}`);
                    console.log(Consulta);
                    //console.log(error);
                }
                callback(true);
            });
        });
    }

    function MontaSQLInserts(dbP, DadosAluno, IndexT, IndexD, callback) {
        let ModoInsert = true;
        if (IndexT < DadosAluno.Tabelas.length) {
            if (IndexD < DadosAluno.Tabelas[IndexT].Dados.length) {
                DadosAluno.Tabelas[IndexT]['SQL'] += "insert into " + dbP + DadosAluno.Tabelas[IndexT].Tabela + " (";
                for (let key in DadosAluno.Tabelas[IndexT].Dados[IndexD]) {
                    if (DadosAluno.Tabelas[IndexT]['SQL'][DadosAluno.Tabelas[IndexT]['SQL'].length - 1] != "(")
                        DadosAluno.Tabelas[IndexT]['SQL'] += ", ";
                    let InsereCampo = true;
                    for (let j = 0; j < DadosAluno.Tabelas[IndexT].CamposReplace.length; j++) {
                        if (DadosAluno.Tabelas[IndexT].CamposReplace[j].Ignorar && DadosAluno.Tabelas[IndexT].CamposReplace[j].CampoP == key) {
                            InsereCampo = false;
                            break;
                        }
                    }
                    if (InsereCampo)
                        DadosAluno.Tabelas[IndexT]['SQL'] += key;
                }
                DadosAluno.Tabelas[IndexT]['SQL'] += ") values (";
                for (let key in DadosAluno.Tabelas[IndexT].Dados[IndexD]) {
                    let InsereCampo = true;
                    for (let j = 0; j < DadosAluno.Tabelas[IndexT].CamposReplace.length; j++) {
                        if (DadosAluno.Tabelas[IndexT].CamposReplace[j].Ignorar && DadosAluno.Tabelas[IndexT].CamposReplace[j].CampoP == key) {
                            InsereCampo = false;
                            break;
                        }
                    }
                    if (InsereCampo) {
                        // SE NAO FOR O PRIMEIRO VALOR INSERIDO
                        if (DadosAluno.Tabelas[IndexT]['SQL'][DadosAluno.Tabelas[IndexT]['SQL'].length - 1] != "(")
                            DadosAluno.Tabelas[IndexT]['SQL'] += ", ";
                        // SE FOR DATA
                        if (DadosAluno.Tabelas[IndexT].Dados[IndexD][key] && (DadosAluno.Tabelas[IndexT].Dados[IndexD][key].toString().includes(" (Horário ") || DadosAluno.Tabelas[IndexT].Dados[IndexD][key].toString().includes(" Time)"))) {
                            DadosAluno.Tabelas[IndexT].Dados[IndexD][key] = new Date(DadosAluno.Tabelas[IndexT].Dados[IndexD][key].toString()).toISOString().replace('T', ' ').split('.')[0];
                        }
                        if (DadosAluno.Tabelas[IndexT].Dados[IndexD][key] == null)
                            DadosAluno.Tabelas[IndexT]['SQL'] += "null";
                        else
                            DadosAluno.Tabelas[IndexT]['SQL'] += "'" + DadosAluno.Tabelas[IndexT].Dados[IndexD][key] + "'";
                    }
                }
                DadosAluno.Tabelas[IndexT]['SQL'] += "); ";
                IndexD++;
                MontaSQLInserts(dbP, DadosAluno, IndexT, IndexD, callback);
            }
            else {
                if (ModoInsert) {
                    sqlServerConnection.request().query(DadosAluno.Tabelas[IndexT]['SQL'], function (error, Dados, fields) {
                        if (error && DadosAluno.Tabelas[IndexT].ErroCritico) {
                            console.log("INSERT SQL EXECUTADO com ERRO na tabela " + DadosAluno.Tabelas[IndexT].Tabela);
                            console.log(DadosAluno.Tabelas[IndexT]['SQL']);
                            console.log(error);
                            callback(false);
                            return;
                        }
                        else {
                            console.log("INSERT SQL EXECUTADO com SUCESSO na tabela " + DadosAluno.Tabelas[IndexT].Tabela);
                        }
                        IndexT++;
                        IndexD = 0;
                        setTimeout(() => {
                            MontaSQLInserts(dbP, DadosAluno, IndexT, IndexD, callback);
                        }, 300);
                    });
                }
                else {
                    IndexT++;
                    IndexD = 0;
                    MontaSQLInserts(dbP, DadosAluno, IndexT, IndexD, callback);
                }
            }
        }
        else {
            callback(ModoInsert);
        }
    }

    router.get('/getOnlineUsers/:tempo/:idunits', (req, res) => {
        const Tempo = req.params.tempo;
        const idunits = req.params.idunits;
        const SQLOnline = `select
            concat(un.reference_db, '.dbo.') as 'Banco',
            un.unityname as 'Unidade',
            co.name as 'Curso',
            cl.name as 'Aula',
            cl.description as 'Pauta',
            sc.idusers as 'Matricula',
            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 ${(idunits == 0) ? 'true' : 'sc.idunits = ' + idunits} and sc.update_date > date_sub(now(), interval ${Tempo} minute)
        order by sc.update_date desc;`;
        mysqlConnection.query(SQLOnline, function (error, Alunos, fields) {
            let SQLNomes = ``;
            for (let i = 0; i < Alunos.length; i++) {
                if (SQLNomes != ``)
                    SQLNomes += ` union all `;
                SQLNomes += `select distinct
                    '${Alunos[i].Unidade}' as Unidade,
                    tt.IDMatricula as 'Matricula',
                    cl.Nome,
                    '${Alunos[i].Curso}' as Curso,
                    '${Alunos[i].Aula}' as Aula,
                    '${Alunos[i].Concluida}' as Concluida,
                    '${Alunos[i].IniciadoEm}' as IniciadoEm,
                    '${Alunos[i].VistoUltimo}' as VistoUltimo
                from ${Alunos[i].Banco}TreinandosTurmas tt
                inner join ${Alunos[i].Banco}Clientes cl on cl.IDCliente = tt.IDTreinando
                where tt.IDMatricula = ${Alunos[i].Matricula}`
            }
            execSQLQuery2(SQLNomes, res, req.originalUrl);
        });
    });


    


    return router;
}

module.exports = routes;