<?php

declare(strict_types=1);

namespace App\Repository;

use Firebase\JWT\JWT;

final class QueryRepository
{
    private \PDO $database;

    public function __construct(\PDO $database)
    {
        $this->database = $database;
    }

    public function getDb(): \PDO
    {
        return $this->database;
    }

    public function sql(int $QueryId, object $data)
    {
        if ($QueryId === 0) {
            $http_code = "";

            if (isset($data->http_code)) {
                $http_code = $data->http_code;
            }

            if ($http_code === "200") {
                $query = "SELECT NOW()";
                $statement = $this->getDb()->prepare($query);
                $statement->execute();

                return $statement->fetchAll();
            } elseif ($http_code === "500") {
                $query = "SELECT error";
                $statement = $this->getDb()->prepare($query);
                $statement->execute();

                return $statement->fetchAll();
            }
        } elseif ($QueryId === 1000) {
            // *maestro*
            $campo = "";

            if (isset($data->campo)) {
                $campo = $data->campo;
            }

            $query =
                'SELECT texto AS name FROM Maestro WHERE campo = :campo AND estado = "ACTIVO" ORDER BY texto';
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam("campo", $campo);
            $statement->execute();

            return $statement->fetchAll();
        } elseif ($QueryId === 10000) {
            // *campos de la BD*
            $campo = "";
            $tabla = "";

            if (isset($data->campo)) {
                $campo = $data->campo;
            }
            if (isset($data->tabla)) {
                $tabla = $data->tabla;
            }

            $query =
                "SELECT DISTINCT " .
                $campo .
                " AS name FROM " .
                $tabla .
                " ORDER BY name";
            $statement = $this->getDb()->prepare($query);
            $statement->execute();

            return $statement->fetchAll();
        } elseif ($QueryId === 1001) {
            // Crear Session
            $fk = "";
            $name = "";
            $email = "";
            $field1 = "";
            $field2 = "";
            $field3 = "";
            $field4 = "";
            $field5 = "";
            $token = "";

            if (isset($data->fk)) {
                $fk = $data->fk;
            }
            if (isset($data->name)) {
                $name = $data->name;
            }
            if (isset($data->email)) {
                $email = $data->email;
            }
            if (isset($data->field1)) {
                $field1 = $data->field1;
            }
            if (isset($data->field2)) {
                $field2 = $data->field2;
            }
            if (isset($data->field3)) {
                $field3 = $data->field3;
            }
            if (isset($data->field4)) {
                $field4 = $data->field4;
            }
            if (isset($data->field5)) {
                $field5 = $data->field5;
            }
            if (isset($data->token)) {
                $token = $data->token;
            }

            $query = 'INSERT INTO Session(fk, name, email, field1, field2, field3, field4, field5, token) 
            VALUES (:fk,:name,:email,:field1,:field2,:field3,:field4,:field5,:token)';
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam("fk", $fk);
            $statement->bindParam("name", $name);
            $statement->bindParam("email", $email);
            $statement->bindParam("field1", $field1);
            $statement->bindParam("field2", $field2);
            $statement->bindParam("field3", $field3);
            $statement->bindParam("field4", $field4);
            $statement->bindParam("field5", $field5);
            $statement->bindParam("token", $token);
            $statement->execute();

            $sessionadminId = (int) $this->getDb()->lastInsertId();

            $query = "SELECT * FROM Session WHERE id = :id";
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam("id", $sessionadminId);
            $statement->execute();
            $sessionadmin = $statement->fetchObject();

            if (empty($sessionadmin)) {
                throw new \Exception("Query not found.", 404);
            }

            return $sessionadmin;
        } elseif ($QueryId === 1002) {
            // Borrar Session
            $id = "";

            if (isset($data->id)) {
                $id = $data->id;
            }

            $query = "DELETE FROM Session WHERE id = :id";
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam("id", $id);
            $statement->execute();
        } elseif ($QueryId === 1003) {
            $perfil = "";
            $modulo = "";
            $accion = "";
            $field1 = "";
            $field2 = "";
            $field3 = "";
            $field4 = "";
            $oficina = "";

            if (isset($data->perfil)) {
                $perfil = $data->perfil;
            }
            if (isset($data->modulo)) {
                $modulo = $data->modulo;
            }
            if (isset($data->accion)) {
                $accion = $data->accion;
            }
            if (isset($data->field1)) {
                $field1 = $data->field1; //field1
            }
            if (isset($data->field2)) {
                $field2 = $data->field2; //field2
            }
            if (isset($data->field3)) {
                $field3 = $data->field3; //field3
            }
            if (isset($data->field4)) {
                $field4 = $data->field4; //field4
            }
            if (isset($data->field5)) {
                $oficina = $data->field5; //field5
            }

            $query =
                "SELECT * FROM Accion WHERE modulo = :modulo AND field5 = :oficina AND " .
                $accion .
                ' = \'SI\' AND perfil = :perfil';

            $statement = $this->getDb()->prepare($query);
            $statement->bindParam("perfil", $perfil);
            $statement->bindParam("modulo", $modulo);
            $statement->bindParam("oficina", $oficina);
            $statement->execute();
            $Accion = $statement->fetchObject();

            if (empty($Accion)) {
                throw new \Exception("Query not found.", 404);
            }

            return $Accion;
        } elseif ($QueryId === 1004) {
            $tabla = "";
            $id = "";
            $name = "";

            if (isset($data->tabla)) {
                $tabla = $data->tabla;
            }
            if (isset($data->id)) {
                $id = $data->id;
            }
            if (isset($data->name)) {
                $name = $data->name;
            }

            if ($tabla == "TblPais") {
                $query =
                    "SELECT " .
                    $id .
                    " AS id, " .
                    $name .
                    " AS name FROM " .
                    $tabla .
                    " WHERE Activo = 1";
            } elseif ($tabla == "TblDepartamento") {
                $query =
                    "SELECT IdPais, " .
                    $id .
                    " AS id, " .
                    $name .
                    " AS name FROM " .
                    $tabla .
                    "";
            } elseif ($tabla == "TblCiudad") {
                $query =
                    "SELECT IdDepartamento, " .
                    $id .
                    " AS id, " .
                    $name .
                    " AS name FROM " .
                    $tabla .
                    "";
            } elseif ($tabla == "TblRepresentanteACTIVO") {
                $query =
                    "SELECT IdPais, " .
                    $id .
                    " AS id, " .
                    $name .
                    " AS name FROM TblRepresentante WHERE field1 = 'ACTIVO' ";
            } elseif ($tabla == "TblSupervisorACTIVO") {
                $query =
                    "SELECT " .
                    $id .
                    " AS id, " .
                    $name .
                    " AS name FROM TblSupervisor WHERE field1 = 'ACTIVO' ";
            } else {
                $query =
                    "SELECT " .
                    $id .
                    " AS id, " .
                    $name .
                    " AS name FROM " .
                    $tabla .
                    "";
            }

            $statement = $this->getDb()->prepare($query);
            $statement->execute();
            $Datos = (array) $statement->fetchAll();

            if (empty($Datos)) {
                throw new \Exception("Query not found.", 404);
            }

            return $Datos;
        } elseif ($QueryId === 1005) {
            $email = "";

            if (isset($data->email)) {
                $email = $data->email;
            }

            $query = "SELECT id, email FROM User WHERE email = :email";

            $statement = $this->getDb()->prepare($query);
            $statement->bindParam("email", $email);
            $statement->execute();
            $Datos = (array) $statement->fetchAll();

            if (empty($Datos)) {
                throw new \Exception("Query not found.", 404);
            }

            return $Datos;
        } elseif ($QueryId === 1006) {
            $tabla = "";
            $inicio = "";
            $fin = "";

            if (isset($data->tabla)) {
                $tabla = $data->tabla;
            }
            if (isset($data->inicio)) {
                $inicio = $data->inicio;
            }
            if (isset($data->fin)) {
                $fin = $data->fin;
            }

            $query =
                "SELECT * FROM " .
                $tabla .
                " WHERE id > :inicio AND id <= :fin";
            $statement = $this->getDb()->prepare($query);
            $inicio = (int) $inicio;
            $fin = (int) $fin;
            $statement->bindValue(":inicio", $inicio, \PDO::PARAM_INT);
            $statement->bindValue(":fin", $fin, \PDO::PARAM_INT);
            $statement->execute();
            $Datos = (array) $statement->fetchAll();

            if (empty($Datos)) {
                throw new \Exception("Query not found.", 404);
            }

            return $Datos;
        } elseif ($QueryId === 1007) {
            $tabla = "";

            if (isset($data->tabla)) {
                $tabla = $data->tabla;
            }

            $query = "SELECT COUNT(id) as cantidad FROM " . $tabla . " ";
            $statement = $this->getDb()->prepare($query);
            $statement->execute();
            $Datos = (array) $statement->fetchAll();

            if (empty($Datos)) {
                throw new \Exception("Query not found.", 404);
            }

            return $Datos;
        } elseif ($QueryId === 1008) {
            $tabla = "";
            $campo1 = "1";
            $valor1 = "1";
            $campo2 = "1";
            $valor2 = "1";

            if (isset($data->tabla)) {
                $tabla = $data->tabla;
            }
            if (isset($data->campo1)) {
                $campo1 = $data->campo1;
            }
            if (isset($data->valor1)) {
                $valor1 = $data->valor1;
            }
            if (isset($data->campo2)) {
                $campo2 = $data->campo2;
            }
            if (isset($data->valor2)) {
                $valor2 = $data->valor2;
            }

            $query =
                "SELECT * FROM " .
                $tabla .
                " WHERE " .
                $campo1 .
                ' LIKE "%' .
                $valor1 .
                '%" AND ' .
                $campo2 .
                ' LIKE "%' .
                $valor2 .
                '%"';
            $statement = $this->getDb()->prepare($query);
            $statement->execute();
            $Datos = (array) $statement->fetchAll();

            if (empty($Datos)) {
                throw new \Exception("Query not found.", 404);
            }

            return $Datos;
        } elseif ($QueryId === 1009) {
            $query = 'SELECT TblUsuario.*, User.id AS IdUser FROM TblUsuario INNER JOIN User ON TblUsuario.id = User.field2 WHERE TblUsuario.field1 = "PENDIENTE"';
            $statement = $this->getDb()->prepare($query);
            $statement->execute();
            $Datos = (array) $statement->fetchAll();

            if (!$Datos) {
                throw new \Exception("Query not found.", 404);
            }

            return $Datos;
        } elseif ($QueryId === 1010) {
            $query = 'SELECT TblUsuario.*, User.id AS IdUser FROM TblUsuario INNER JOIN User ON TblUsuario.id = User.field2 WHERE TblUsuario.field1 = "RECUPERACION"';
            $statement = $this->getDb()->prepare($query);
            $statement->execute();
            $Datos = (array) $statement->fetchAll();

            if (!$Datos) {
                throw new \Exception("Query not found.", 404);
            }

            return $Datos;
        } elseif ($QueryId === 1011) {
            $valor1 = "";

            if (isset($data->valor1)) {
                $valor1 = $data->valor1;
            }

            $query = 'UPDATE TblUsuario SET field1 = "PENDIENTE ENVIADO" WHERE id =:valor1 ';
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam("valor1", $valor1);
            $statement->execute();
        } elseif ($QueryId === 1012) {
            $valor1 = "";
            $valor2 = "";
            $valor3 = "";

            if (isset($data->valor1)) {
                $valor1 = $data->valor1;
            }
            if (isset($data->valor2)) {
                $valor2 = $data->valor2;
            }
            if (isset($data->valor3)) {
                $valor3 = $data->valor3;
            }

            $query = 'UPDATE TblUsuario SET field1 = "ACTIVO" WHERE id =:valor1 ';
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam("valor1", $valor1);
            $statement->execute();

            $hash = password_hash($valor3, PASSWORD_BCRYPT);
            $password = $hash;

            $query = 'UPDATE User SET password =:password WHERE id =:valor2 ';
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam("valor2", $valor2);
            $statement->bindParam("password", $password);
            $statement->execute();
        } elseif ($QueryId === 1014) {
            $valor1 = "";
            $valor2 = "";
            $valor3 = "";
            $valor4 = "";

            if (isset($data->valor1)) {
                $valor1 = $data->valor1; // CorreoElectronico
            }
            if (isset($data->valor2)) {
                $valor2 = $data->valor2; // TokenDispositivo
            }
            if (isset($data->valor3)) {
                $valor3 = $data->valor3; // IdUsuario
            }
            if (isset($data->valor4)) {
                $valor4 = $data->valor4; // IdPais
            }


            $query = 'UPDATE TblUsuario SET field1 = "ACTIVO" WHERE CorreoElectronico =:valor1 AND TokenDispositivo =:valor2 ';
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam("valor1", $valor1);
            $statement->bindParam("valor2", $valor2);
            $success = $statement->execute();

            if ($success) {
                if ($statement->rowCount() > 0) {
                    // return ['respuesta' => 'Actualización exitosa'];
                    $usuario_id = $valor3; // Definir el idUsuario aquí
                    // $query = 'CALL InsertarCursosYModulosYTemas(:idUsuario)';
                    // $statement = $this->getDb()->prepare($query);
                    // $statement->bindParam(":idUsuario", $idUsuario);
                    // $success = $statement->execute();
                  
                  
                  
                    // Preparar la consulta para obtener los cursos generales
                    $queryCursos = 'SELECT id FROM TblCurso WHERE TipoCurso = "General" AND IdPais = :IdPais';
                    $statementCursos = $this->getDb()->prepare($queryCursos);
                    $statementCursos->bindParam(':IdPais', $valor4);
                    $statementCursos->execute();

                    // Iniciar el loop para los cursos
                    while ($curso = $statementCursos->fetchObject()) {
                        // Insertar en TblCursoUsuario
                        $queryInsertCursoUsuario = 'INSERT INTO TblCursoUsuario (IdCurso, FechaInicio, IdUsuario, Calificacion, IdValorTipo) VALUES (:curso_id, NOW(), :usuario_id, 0, 32)';
                        $statementInsertCursoUsuario = $this->getDb()->prepare($queryInsertCursoUsuario);
                        $statementInsertCursoUsuario->bindParam(':curso_id', $curso->id);
                        $statementInsertCursoUsuario->bindParam(':usuario_id', $usuario_id);
                        $statementInsertCursoUsuario->execute();

                        // Insertar en TableInsertarCursosYModulos
                        $queryInsertCursosYModulos = 'INSERT INTO TableInsertarCursosYModulosYTemas (IdCurso, IdUsuario) VALUES (:curso_id, :usuario_id)';
                        $statementInsertCursosYModulos = $this->getDb()->prepare($queryInsertCursosYModulos);
                        $statementInsertCursosYModulos->bindParam(':curso_id', $curso->id);
                        $statementInsertCursosYModulos->bindParam(':usuario_id', $usuario_id);
                        $statementInsertCursosYModulos->execute();

                        // Preparar la consulta para obtener los módulos de cada curso
                        $queryModulos = 'SELECT id FROM TblModulo WHERE IdCurso = :curso_id';
                        $statementModulos = $this->getDb()->prepare($queryModulos);
                        $statementModulos->bindParam(':curso_id', $curso->id);
                        $statementModulos->execute();

                        // Iniciar el loop para los módulos
                        while ($modulo = $statementModulos->fetchObject()) {
                            // Insertar en TblModuloXUsuario
                            $queryInsertModuloXUsuario = 'INSERT INTO TblModuloXUsuario (IdModulo, EstadoRta, Estado, IdValorTipo, IdUsuario, Calificacion) VALUES (:modulo_id, 0, 0, 18, :usuario_id, 0)';
                            $statementInsertModuloXUsuario = $this->getDb()->prepare($queryInsertModuloXUsuario);
                            $statementInsertModuloXUsuario->bindParam(':modulo_id', $modulo->id);
                            $statementInsertModuloXUsuario->bindParam(':usuario_id', $usuario_id);
                            $statementInsertModuloXUsuario->execute();

                            // Preparar la consulta para obtener los temas de cada módulo
                            $queryTemas = 'SELECT id FROM TblTema WHERE IdModulo = :modulo_id';
                            $statementTemas = $this->getDb()->prepare($queryTemas);
                            $statementTemas->bindParam(':modulo_id', $modulo->id);
                            $statementTemas->execute();

                            // Iniciar el loop para los temas
                            while ($tema = $statementTemas->fetchObject()) {
                                // Insertar en TblTemaXUsuario
                                $queryInsertTemaXUsuario = 'INSERT INTO TblTemaXUsuario (IdUsuario, IdTema, IdEstado, Calificacion, NumeroIntentosRealizados, PorcentajeAvance) VALUES (:usuario_id, :tema_id, 18, 0, 0, 0)';
                                $statementInsertTemaXUsuario = $this->getDb()->prepare($queryInsertTemaXUsuario);
                                $statementInsertTemaXUsuario->bindParam(':usuario_id', $usuario_id);
                                $statementInsertTemaXUsuario->bindParam(':tema_id', $tema->id);
                                $statementInsertTemaXUsuario->execute();
                            }
                        }
                    }
                  
                    return ['respuesta' => '200'];
                } else {
                    // return ['respuesta' => 'No se realizó ninguna actualización'];
                    return ['respuesta' => '201'];
                }
            } else {
                // return ['respuesta' => 'Error al ejecutar la consulta'];
                return ['respuesta' => '404'];
            }
        } elseif ($QueryId === 1015) {
            // $valor1 = "";
            $valor2 = "";

            // if (isset($data->valor1)) {
            // $valor1 = $data->valor1;
            // }
            if (isset($data->valor2)) {
                $valor2 = $data->valor2;
            }

            $query = 'UPDATE TblUsuario SET field1 = "RECUPERACION" WHERE CorreoElectronico =:valor2';
            // $query = 'UPDATE TblUsuario SET field1 = "RECUPERACION" WHERE CorreoElectronico =:valor2 AND field1 = "ACTIVO" ';
            $statement = $this->getDb()->prepare($query);
            // $statement->bindParam("valor1", $valor1);
            $statement->bindParam("valor2", $valor2);
            $success = $statement->execute();
        } elseif ($QueryId === 1016) {
            $valor1 = "";

            if (isset($data->valor1)) {
                $valor1 = $data->valor1;
            }

            $query = 'SELECT * FROM TblPais WHERE id =:id';
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam("id", $valor1);
            $statement->execute();
            $Datos = (array) $statement->fetchAll();

            if (!$Datos) {
                throw new \Exception("Query not found.", 404);
            }

            return $Datos;
        } elseif ($QueryId === 1017) {
            $valor1 = "";
            $valor2 = "";

            if (isset($data->valor1)) {
                $valor1 = $data->valor1;
            }
            if (isset($data->valor2)) {
                $valor2 = $data->valor2;
            }

            // $query = 'SELECT * FROM TblCurso WHERE IdPais =:IdPais AND TipoCurso =:TipoCurso';
            $query = 'SELECT TblCurso.*, COUNT(TblModulo.id) AS cantidad
                      FROM TblCurso
                      LEFT JOIN TblModulo ON TblCurso.id = TblModulo.IdCurso
                      WHERE TblCurso.IdPais = :IdPais AND TblCurso.TipoCurso = :TipoCurso
                      GROUP BY TblCurso.id';
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam("IdPais", $valor1);
            $statement->bindParam("TipoCurso", $valor2);
            $statement->execute();
            $Datos = (array) $statement->fetchAll();

            if (!$Datos) {
                return null;
                // throw new \Exception("Query not found.", 404);
            }

            return $Datos;
        } elseif ($QueryId === 1018) {
            $valor1 = "";

            if (isset($data->valor1)) {
                $valor1 = $data->valor1;
            }

            $query = 'SELECT TblCurso.*, TblCursoUsuario.id AS idCursoUsuario, TblCursoUsuario.fecha AS fechaCursoUsuario, TblCursoUsuario.IdCurso AS IdCursoCursoUsuario,
            TblCursoUsuario.FechaInicio AS FechaInicioCursoUsuario, TblCursoUsuario.FechaFin AS FechaFinCursoUsuario, TblCursoUsuario.IdUsuario AS IdUsuarioCursoUsuario,
            TblCursoUsuario.DiasAdicionales AS DiasAdicionalesCursoUsuario, TblCursoUsuario.Calificacion AS CalificacionCursoUsuario, TblCursoUsuario.IdValorTipo AS IdValorTipoCursoUsuario,
            TblCursoUsuario.CalificarExperiencia AS CalificarExperienciaCursoUsuario, TblCursoUsuario.field1 AS field1CursoUsuario, TblCursoUsuario.field2 AS field2CursoUsuario,
            TblCursoUsuario.field3 AS field3CursoUsuario, TblCursoUsuario.field4 AS field4CursoUsuario, TblCursoUsuario.field5 AS field5CursoUsuario 
            FROM TblCurso INNER JOIN TblCursoUsuario ON TblCurso.id = TblCursoUsuario.IdCurso WHERE IdUsuario =:IdUsuario AND TipoCurso <> "Borrador"';
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam("IdUsuario", $valor1);
            $statement->execute();
            $Datos = (array) $statement->fetchAll();

            if (!$Datos) {
                throw new \Exception("Query not found.", 404);
            }

            return $Datos;
        } elseif ($QueryId === 1019) {

            $query = 'SELECT TblSegmento.*, TblSegmentoCurso.IdCurso AS IdCurso FROM TblSegmentoCurso INNER JOIN TblSegmento ON TblSegmentoCurso.IdSegmento = TblSegmento.id';
            $statement = $this->getDb()->prepare($query);
            $statement->execute();
            $Datos = (array) $statement->fetchAll();

            if (!$Datos) {
                throw new \Exception("Query not found.", 404);
            }

            return $Datos;
        } elseif ($QueryId === 1020) {
            $valor1 = "";

            if (isset($data->valor1)) {
                $valor1 = $data->valor1;
            }

            $query = 'SELECT * FROM TblCurso WHERE id =:IdCurso';
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam("IdCurso", $valor1);
            $statement->execute();
            $Datos = (array) $statement->fetchAll();

            if (!$Datos) {
                throw new \Exception("Query not found.", 404);
            }

            return $Datos;
        } elseif ($QueryId === 1021) {
            $valor1 = "";

            if (isset($data->valor1)) {
                $valor1 = $data->valor1;
            }

            $query = 'SELECT * FROM TblModulo WHERE IdCurso =:IdCurso ORDER BY Orden';
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam("IdCurso", $valor1);
            $statement->execute();
            $Datos = (array) $statement->fetchAll();

            if (!$Datos) {
                throw new \Exception("Query not found.", 404);
            }

            return $Datos;
        } elseif ($QueryId === 1022) {
            $valor1 = "";

            if (isset($data->valor1)) {
                $valor1 = $data->valor1;
            }

            $query = 'SELECT TblTema.* FROM TblTema INNER JOIN TblModulo ON TblTema.IdModulo = TblModulo.id WHERE IdCurso =:IdCurso';
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam("IdCurso", $valor1);
            $statement->execute();
            $Datos = (array) $statement->fetchAll();

            if (!$Datos) {
                throw new \Exception("Query not found.", 404);
            }

            return $Datos;
        } elseif ($QueryId === 1023) {
            $valor1 = "";

            if (isset($data->valor1)) {
                $valor1 = $data->valor1;
            }

            $query = 'SELECT TblContenidoTema.* FROM TblContenidoTema INNER JOIN TblTema ON TblContenidoTema.IdTema = TblTema.id 
            INNER JOIN TblModulo ON TblTema.IdModulo = TblModulo.id WHERE IdCurso =:IdCurso';
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam("IdCurso", $valor1);
            $statement->execute();
            $Datos = (array) $statement->fetchAll();

            if (!$Datos) {
                throw new \Exception("Query not found.", 404);
            }

            return $Datos;
        } elseif ($QueryId === 1024) {
            $valor1 = "";

            if (isset($data->valor1)) {
                $valor1 = $data->valor1;
            }

            $query = 'SELECT id, Identificacion, Nombres, Apellidos, CorreoElectronico, PuntoDeVentaNoPanel, Avatar, IdPais, FechaUltimoIngreso 
            FROM TblUsuario 
            WHERE IdRepresentante =:IdRepresentante';
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam("IdRepresentante", $valor1);
            $statement->execute();
            $Datos = (array) $statement->fetchAll();

            if (!$Datos) {
                return null;
                // throw new \Exception("Query not found.", 404);
            }

            return $Datos;
        } elseif ($QueryId === 1025) {
            $valor1 = "";

            if (isset($data->valor1)) {
                $valor1 = $data->valor1;
            }

            $query = 'SELECT TblRepresentante.*
            FROM TblRepresentante INNER JOIN TblSupervisor ON TblRepresentante.IdSupervisor = TblSupervisor.id
            WHERE TblSupervisor.id =:IdSupervisor';
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam("IdSupervisor", $valor1);
            $statement->execute();
            $Datos = (array) $statement->fetchAll();

            if (!$Datos) {
                return null;
                // throw new \Exception("Query not found.", 404);
            }

            return $Datos;
        } elseif ($QueryId === 1026) {
            $valor1 = "";
            $valor2 = "";

            if (isset($data->valor1)) {
                $valor1 = $data->valor1;
            }
            if (isset($data->valor2)) {
                $valor2 = $data->valor2;
            }

            $query = 'SELECT PorcentajeAvance
            FROM TblTemaXUsuario 
            WHERE IdTema =:IdTema AND IdUsuario =:IdUsuario ';
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam("IdTema", $valor1);
            $statement->bindParam("IdUsuario", $valor2);
            $statement->execute();
            $Datos = (array) $statement->fetchAll();

            if (!$Datos) {
                throw new \Exception("Query not found.", 404);
            }

            return $Datos;
        } elseif ($QueryId === 1027) {
            $valor1 = "";

            if (isset($data->valor1)) {
                $valor1 = $data->valor1;
            }

            $query = 'SELECT * FROM TblTema WHERE IdModulo =:IdModulo';
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam("IdModulo", $valor1);
            $statement->execute();
            $Datos = (array) $statement->fetchAll();

            if (!$Datos) {
                throw new \Exception("Query not found.", 404);
            }

            return $Datos;
        } elseif ($QueryId === 1028) {
            $query = 'SELECT * FROM PreguntasFrecuentes';
            $statement = $this->getDb()->prepare($query);
            $statement->execute();
            $Datos = (array) $statement->fetchAll();

            if (!$Datos) {
                return null;
                // throw new \Exception("Query not found.", 404);
            }

            return $Datos;
        } elseif ($QueryId === 1029) {
            $valor1 = "";

            if (isset($data->valor1)) {
                $valor1 = $data->valor1;
            }

            $query = 'SELECT TblNoticiaGenfar.*, TblTipoNoticiaGenfar.Color AS Color, TblTipoNoticiaGenfar.Icono AS Icono, 
            TblTipoNoticiaGenfar.TipoNoticia AS TipoNoticia FROM TblNoticiaGenfar INNER JOIN TblTipoNoticiaGenfar ON 
            TblNoticiaGenfar.IdTipoNoticiaGenfar = TblTipoNoticiaGenfar.id INNER JOIN TblNoticiaGenfarPais ON 
            TblNoticiaGenfar.id = TblNoticiaGenfarPais.IdNoticiaGenfar WHERE 
            TblNoticiaGenfarPais.IdPais = :IdPais AND FechaFin >= now() ORDER BY TblNoticiaGenfarPais.field1, TblNoticiaGenfar.Titulo';
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam("IdPais", $valor1);
            $statement->execute();
            $Datos = (array) $statement->fetchAll();

            if (!$Datos) {
                return null;
                // throw new \Exception("Query not found.", 404);
            }

            return $Datos;
        } elseif ($QueryId === 1030) {

            $query = 'SELECT COUNT(id) AS cantidad, IdCurso FROM TblModulo GROUP BY  IdCurso';
            $statement = $this->getDb()->prepare($query);
            $statement->execute();
            $Datos = (array) $statement->fetchAll();

            if (!$Datos) {
                return null;
                // throw new \Exception("Query not found.", 404);
            }

            return $Datos;
        } elseif ($QueryId === 1031) {
            $valor1 = "";

            if (isset($data->valor1)) {
                $valor1 = $data->valor1;
            }

            $query = 'SELECT TblContenidoTema.*, TblTema.Tema FROM TblContenidoTema INNER JOIN TblTema ON TblContenidoTema.IdTema = TblTema.id
            WHERE IdTema =:IdTema';
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam("IdTema", $valor1);
            $statement->execute();
            $Datos = (array) $statement->fetchAll();

            if (!$Datos) {
                return null;
                // throw new \Exception("Query not found.", 404);
            }

            return $Datos;
        } elseif ($QueryId === 1032) { // TblTemaXUsuario Actualizar el Avance *** IMPORTANTE ***
            $valor1 = "";
            $valor2 = "";

            if (isset($data->valor1)) {
                $valor1 = $data->valor1;
            }
            if (isset($data->valor2)) {
                $valor2 = $data->valor2;
            }

            $query = 'UPDATE TblTemaXUsuario 
            SET PorcentajeAvance = 100
            WHERE IdTema =:IdTema AND IdUsuario =:IdUsuario ';
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam("IdTema", $valor1);
            $statement->bindParam("IdUsuario", $valor2);
            $statement->execute();
        } elseif ($QueryId === 1033) { // Preguntas y Respuestas por IdTema
            $valor1 = "";

            if (isset($data->valor1)) {
                $valor1 = $data->valor1;
            }

            $query = 'SELECT Pregunta, IdTema, Celula, TblRespuesta.id AS IdRespuesta, 
            Respuesta, Ayuda, Imagen, RespuestaCorrecta, TblRespuesta.IdPregunta AS IdPregunta
            FROM TblPregunta INNER JOIN TblRespuesta 
            ON TblPregunta.id = TblRespuesta.IdPregunta LEFT JOIN TblAyuda 
            ON TblPregunta.id = TblAyuda.IdPregunta WHERE IdTema = :IdTema';
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam("IdTema", $valor1);
            $statement->execute();
            $Datos = (array) $statement->fetchAll();

            if (!$Datos) {
                return null;
                // throw new \Exception("Query not found.", 404);
            }

            return $Datos;
        } elseif ($QueryId === 1034) {
            $IdUsuario = "";
            $IdCurso = "";
            $IdModulo = "";
            $IdTema = "";
            $Apunte = "";

            if (isset($data->IdUsuario)) {
                $IdUsuario = $data->IdUsuario;
            }
            if (isset($data->IdCurso)) {
                $IdCurso = $data->IdCurso;
            }
            if (isset($data->IdModulo)) {
                $IdModulo = $data->IdModulo;
            }
            if (isset($data->IdTema)) {
                $IdTema = $data->IdTema;
            }
            if (isset($data->Apunte)) {
                $Apunte = $data->Apunte;
            }

            $query = 'INSERT INTO Apunte(IdUsuario, IdCurso, IdModulo, IdTema, Apunte) 
            VALUES (:IdUsuario, :IdCurso, :IdModulo, :IdTema, :Apunte)';
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam("IdUsuario", $IdUsuario);
            $statement->bindParam("IdCurso", $IdCurso);
            $statement->bindParam("IdModulo", $IdModulo);
            $statement->bindParam("IdTema", $IdTema);
            $statement->bindParam("Apunte", $Apunte);
            $statement->execute();
        } elseif ($QueryId === 1035) {
            $valor1 = "";

            if (isset($data->valor1)) {
                $valor1 = $data->valor1;
            }

            $query = 'SELECT * FROM Apunte WHERE IdUsuario =:IdUsuario';
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam("IdUsuario", $valor1);
            $statement->execute();
            $Datos = (array) $statement->fetchAll();

            if (!$Datos) {
                return null;
                // throw new \Exception("Query not found.", 404);
            }

            return $Datos;
        } elseif ($QueryId === 1036) {

            $query = 'SELECT * FROM WebInicio WHERE id = 1';
            $statement = $this->getDb()->prepare($query);
            $statement->execute();
            $Datos = (array) $statement->fetchAll();

            if (!$Datos) {
                return null;
                // throw new \Exception("Query not found.", 404);
            }

            return $Datos;
        } elseif ($QueryId === 1037) { // Registrar TblUsuario
            $query = 'INSERT INTO TblUsuario (Identificacion, Nombres, Apellidos, CorreoElectronico, Contrasena, Departamento, Ciudad, Farmacia, Avatar, Fecha2, Condiciones, 
            TokenDispositivo, FechaUltimoIngreso, IdCiudad, IdDepartamento, IdPuntoVenta, IdPais, CalificacionGenfar, IdTipoUsuario, IdRolPanel, PuntoDeVentaNoPanel, Rol, 
            ActivoComunidadHigen, IdRepresentante, NombreRepresentante, field1, field2, field3, field4, field5) VALUES (:Identificacion, :Nombres, :Apellidos, :CorreoElectronico, 
            :Contrasena, :Departamento, :Ciudad, :Farmacia, :Avatar, :Fecha2, :Condiciones, :TokenDispositivo, :FechaUltimoIngreso, :IdCiudad, :IdDepartamento, :IdPuntoVenta, :IdPais, :CalificacionGenfar, 
            :IdTipoUsuario, :IdRolPanel, :PuntoDeVentaNoPanel, :Rol, :ActivoComunidadHigen, :IdRepresentante, :NombreRepresentante, :field1, :field2, :field3, :field4, :field5)';
            $statement = $this->getDb()->prepare($query);
            // $statement->bindParam('id', $data->id);
            // $statement->bindParam('fecha', $data->fecha);
            $statement->bindParam('Identificacion', $data->Identificacion);
            $statement->bindParam('Nombres', $data->Nombres);
            $statement->bindParam('Apellidos', $data->Apellidos);
            $statement->bindParam('CorreoElectronico', $data->CorreoElectronico);
            $statement->bindParam('Contrasena', $data->Contrasena);
            $statement->bindParam('Departamento', $data->Departamento);
            $statement->bindParam('Ciudad', $data->Ciudad);
            $statement->bindParam('Farmacia', $data->Farmacia);
            $statement->bindParam('Avatar', $data->Avatar);
            $statement->bindParam('Fecha2', $data->Fecha2);
            $statement->bindParam('Condiciones', $data->Condiciones);
            $statement->bindParam('TokenDispositivo', $data->TokenDispositivo);
            $statement->bindParam('FechaUltimoIngreso', $data->FechaUltimoIngreso);
            $statement->bindParam('IdCiudad', $data->IdCiudad);
            $statement->bindParam('IdDepartamento', $data->IdDepartamento);
            $statement->bindParam('IdPuntoVenta', $data->IdPuntoVenta);
            $statement->bindParam('IdPais', $data->IdPais);
            $statement->bindParam('CalificacionGenfar', $data->CalificacionGenfar);
            $statement->bindParam('IdTipoUsuario', $data->IdTipoUsuario);
            $statement->bindParam('IdRolPanel', $data->IdRolPanel);
            $statement->bindParam('PuntoDeVentaNoPanel', $data->PuntoDeVentaNoPanel);
            $statement->bindParam('Rol', $data->Rol);
            $statement->bindParam('ActivoComunidadHigen', $data->ActivoComunidadHigen);
            $statement->bindParam('IdRepresentante', $data->IdRepresentante);
            $statement->bindParam('NombreRepresentante', $data->NombreRepresentante);
            $statement->bindParam('field1', $data->field1);
            $statement->bindParam('field2', $data->field2);
            $statement->bindParam('field3', $data->field3);
            $statement->bindParam('field4', $data->field4);
            $statement->bindParam('field5', $data->field5);

            $statement->execute();
            $id = (int) $this->getDb()->lastInsertId();
            $query = 'SELECT * FROM TblUsuario WHERE id = :id';
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam('id', $id);
            $statement->execute();
            $TblUsuario = $statement->fetchObject();
            if (!$TblUsuario) {
                throw new \Exception("Query not found.", 404);
            }

            return $TblUsuario;
        } elseif ($QueryId === 1038) { // Actualizar TblUsuario
            if (isset($data->Avatar)) {
                $data->Avatar = $data->Avatar;
            }

            $query = 'UPDATE TblUsuario SET Avatar = :Avatar WHERE id = :id';
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam('id', $data->id);
            $statement->bindParam('Avatar', $data->Avatar);

            $statement->execute();
            $id = (int) $data->id;
            $query = 'SELECT * FROM TblUsuario WHERE id = :id';
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam('id', $id);
            $statement->execute();
            $TblUsuario = $statement->fetchObject();
            if (!$TblUsuario) {
                throw new \Exception("Query not found.", 404);
            }

            return $TblUsuario;
        } elseif ($QueryId === 1039) { // Registrar User
            $query = 'INSERT INTO User (name, email, password, field1, field2, field3, field4, field5) 
            VALUES (:name, :email, :password, :field1, :field2, :field3, :field4, :field5)';
            $statement = $this->getDb()->prepare($query);

            $hash = password_hash($data->password, PASSWORD_BCRYPT);
            $password = $hash;

            // $statement->bindParam('id', $data->id);
            // $statement->bindParam('fecha', $data->fecha);
            $statement->bindParam('name', $data->name);
            $statement->bindParam('email', $data->email);
            $statement->bindParam('password', $password);
            $statement->bindParam('field1', $data->field1);
            $statement->bindParam('field2', $data->field2);
            $statement->bindParam('field3', $data->field3);
            $statement->bindParam('field4', $data->field4);
            $statement->bindParam('field5', $data->field5);

            $statement->execute();
            $id = (int) $this->getDb()->lastInsertId();
            $query = 'SELECT id, fecha, name, email, field1, field2, field3, field4, field5 FROM User WHERE id = :id';
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam('id', $id);
            $statement->execute();
            $User = $statement->fetchObject();
            if (!$User) {
                throw new \Exception("Query not found.", 404);
            }

            return $User;
        } elseif ($QueryId === 1040) { // Guardar Solicitud
            $query = 'INSERT INTO Solicitud(NombreCompleto, CorreoElectronico, NumeroCelular, Comentarios, Condiciones) VALUES (:NombreCompleto, :CorreoElectronico, :NumeroCelular, :Comentarios, :Condiciones)';
            $statement = $this->getDb()->prepare($query);

            // $statement->bindParam('id', $data->id);
            // $statement->bindParam('fecha', $data->fecha);
            $statement->bindParam('NombreCompleto', $data->NombreCompleto);
            $statement->bindParam('CorreoElectronico', $data->CorreoElectronico);
            $statement->bindParam('NumeroCelular', $data->NumeroCelular);
            $statement->bindParam('Comentarios', $data->Comentarios);
            $statement->bindParam('Condiciones', $data->Condiciones);
            $statement->execute();
        } elseif ($QueryId === 1041) { // Guardar Solicitud
            $query = 'INSERT INTO Testimonio(NombreCompleto, CorreoElectronico, Testimonio, Condiciones) VALUES (:NombreCompleto, :CorreoElectronico, :Testimonio, :Condiciones)';
            $statement = $this->getDb()->prepare($query);

            // $statement->bindParam('id', $data->id);
            // $statement->bindParam('fecha', $data->fecha);
            $statement->bindParam('NombreCompleto', $data->NombreCompleto);
            $statement->bindParam('CorreoElectronico', $data->CorreoElectronico);
            $statement->bindParam('Testimonio', $data->Testimonio);
            $statement->bindParam('Condiciones', $data->Condiciones);
            $statement->execute();
        } elseif ($QueryId === 1042) {
            $valor1 = "";
            $valor2 = "";

            if (isset($data->valor1)) {
                $valor1 = $data->valor1;
            }
            if (isset($data->valor2)) {
                $valor2 = $data->valor2;
            }

            $query = "";
            if ($valor1 === 0) {
                $query = "SELECT DISTINCT CorreoElectronico
              FROM Tbl_Usuario 
              WHERE CorreoElectronico REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'
              LIMIT 25";
            } else {
                $query = "SELECT DISTINCT CorreoElectronico
              FROM Tbl_Usuario 
              WHERE CorreoElectronico REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'
              LIMIT 25 OFFSET " . $valor1;
            }

            $statement = $this->getDb()->prepare($query);
            $statement->execute();
            $Datos = (array) $statement->fetchAll();

            if (!$Datos) {
                throw new \Exception("Query not found.", 404);
            }

            return $Datos;
        } elseif ($QueryId === 1043) {
            $valor1 = "";
            $valor2 = "";
            $valor3 = "";
            $valor4 = "";

            if (isset($data->valor1)) {
                $valor1 = $data->valor1;
            }
            if (isset($data->valor2)) {
                $valor2 = $data->valor2;
            }
            if (isset($data->valor3)) {
                $valor3 = $data->valor3;
            }
            if (isset($data->valor4)) {
                $valor4 = $data->valor4;
            }
          
          $query = 'CALL CalcularCalificacionNEW(:IdModulo, :IdTema, :IdUsuario, :IdCurso)';
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam("IdModulo", $valor1);
            $statement->bindParam("IdTema", $valor2);
            $statement->bindParam("IdUsuario", $valor3);
            $statement->bindParam("IdCurso", $valor4);
            $statement->execute();
        } elseif ($QueryId === 1044) {
            $valor1 = "";
            $valor2 = "";
            $valor3 = "";

            if (isset($data->valor1)) {
                $valor1 = $data->valor1;
            }
            if (isset($data->valor2)) {
                $valor2 = $data->valor2;
            }
            if (isset($data->valor3)) {
                $valor3 = $data->valor3;
            }
          
            $query = 'SELECT MAX(field1) AS maximo FROM TblPreguntasXUsuario WHERE IdModulo = :IdModulo AND IdTema = :IdTema AND IdUsuario = :IdUsuario';
            $statement = $this->getDb()->prepare($query);
            $statement->bindParam("IdModulo", $valor1);
            $statement->bindParam("IdTema", $valor2);
            $statement->bindParam("IdUsuario", $valor3);
            $statement->execute();
            $Datos = (array) $statement->fetchAll();

            if (!$Datos) {
                throw new \Exception("Query not found.", 404);
            }

            return $Datos;
        }
    }
}
