SistemaMantenimiento/sistema-mantenimiento-back/app/Http/Controllers/SystemAdministratorController.php

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Validator;
use Illuminate\Support\Facades\Hash;
use Illuminate\Support\Carbon;
use Illuminate\Database\Query\JoinClause;

use PhpOffice\PhpSpreadsheet\Reader\Exception as ExcelException;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use Jenssegers\Agent\Agent;

use Firebase\JWT\JWT;
use Firebase\JWT\Key;
use Exception;

class SystemAdministratorController extends Controller{
    private $responseController;
    private $encryptionController;
    private $functionsController;
    private $documentManagementController;
    private $resourcesController;
    private $publicKey = "zOgD0uF22+xg37nTmA+bg/6/E80BJYeHeByGpeTrNFw=";
    private $secretKey = "ydl27x22cNsNY0z6o3Fr6XZoUvsX0QMZx6MaiwN+KCnM6APS4Xbb7GDfudOYD5uD/r8TzQElh4d4HIal5Os0XA==";

    public function __construct(){
        $this->responseController = new ResponseController();
        $this->encryptionController = new EncryptionController();
        $this->functionsController = new FunctionsController();
        $this->resourcesController = new ResourcesController();
        $this->documentManagementController = new DocumentManagementController();
    }

    public function getLastConnections($idUser, $line){
        DB::enableQueryLog();
        $idUser = $this->encryptionController->decrypt($idUser);

        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la solicitud no está encriptado correctamente', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $line],
            ['USUA_IDUS', '=', $idUser]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

        $lastConnections = DB::table('S002V01TUSUA')->leftJoin('S002V01TBIAC', 'USUA_ULCO', '=', 'BIAC_IDCO')->select(
            'USUA_IDUS AS IDUSUARIO',
            'BIAC_FECO AS ULCON'
        )->get()->all();

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F01MAUS',
            'S002V01P02HICO',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó las últimas conexiones de los demás usuarios.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeresponse(false, "EXITO", $lastConnections);
    }

    public function setLastConnection(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id' => 'required|string',
            'linea' => 'required|integer',
            'latitude' => 'required|numeric|between:-90,90',
            'longitude' => 'required|numeric|between:-180,180',
            'accuracy' => 'required|numeric|between:0,1000000',
            'city' => 'string|max:100',
            'state' => 'string|max:100',
            'country' => 'required|string|max:50',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $form = $request->all();
        $idUser = $this->encryptionController->decrypt($form['id']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, "La ID del usuario no está encriptado correctamente.", [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $form['linea']],
            ['USUA_IDUS', '=', $idUser]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $ipv = $request->ip();
        $v4 = filter_var($ipv, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4) ? true : false;
        $state = isset($login['state']) ? $login['state'] : '-';

        //Se obtiene el dispositivo utilizado
        $agent = new Agent();
        $device = 'Desconocido';
        if($agent->isDesktop()) $device = 'PC';
        else if($agent->device()) $device = $agent->device();

        //Se obtiene el SO utilizado y la versión del mismo
        $platform = $agent->platform() ? $agent->platform() : 'Desconocido';
        $version = 'N/A';
        if($platform != 'Desconocido') $version = $agent->version($platform);

        //Se obtiene el navegador web utilizado
        $browser = $agent->browser() ? $agent->browser() : 'Desconocido';
        
        $idLastConn = DB::table('S002V01TBIAC')->insertGetId([
            'BIAC_NULI' => $form['linea'],
            'BIAC_IDUS' => $idUser,
            'BIAC_FECO' => $nowStr,
            'BIAC_IPV4' => $v4 ? $ipv : null,
            'BIAC_IPV6' => !$v4 ? $ipv : null,
            'BIAC_LATI' => $form['latitude'],
            'BIAC_LONG' => $form['longitude'],
            'BIAC_PREC' => $form['accuracy'],
            'BIAC_CIUD' => $form['city'],
            'BIAC_ESTA' => $state,
            'BIAC_PAIS' => $form['country'],
            'BIAC_DISP' => $device,
            'BIAC_SIOP' => $platform,
            'BIAC_VSOP' => $version,
            'BIAC_NAVE' => $browser,
        ]);

        DB::table('S002V01TUSUA')->where('USUA_IDUS', '=', $idUser)->update(['USUA_ULCO' => $idLastConn]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M01ADSI',
            'S002V01F01MAUS',
            'S002V01P01MOCO',
            'Registró',
            "El usuario $name (" . $usr->USUA_IDUS . ") registró su última conexión al sistema.",
            $idUser,
            $nowStr,
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, "EXITO: Actualización correcta.");
    }

    public function getCatalogues($idUser, $line){
        DB::enableQueryLog();
        
        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la consulta no está encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $line]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado.', [], 404);
        }

        $catalogues = DB::table('S002V01TCATA')->select(
            'CATA_IDCA AS IDCATALOGO',
            'CATA_TICA AS TIPOCATALOGO',
            'CATA_VERS AS VERSION',
            'CATA_ARRE AS ADJUNTOS',
            'CATA_USRE AS USUARIOREGISTRO',
            'CATA_FERE AS FECHAREGISTRO',
        )->where([
            ['CATA_NULI', '=', $line],
            ['CATA_ESTA', '=', 'Activo']
        ])->get()->all();

        $cataloguesF = [];
        foreach($catalogues as $catalogue){
            $usrReg = DB::table('S002V01TUSUA')->where([
                ['USUA_NULI', '=', $line],
                ['USUA_IDUS', '=', $catalogue->USUARIOREGISTRO],
            ])->first();

            $idReg = $catalogue->USUARIOREGISTRO;
            $catalogue->USUARIOREGISTRO = $this->functionsController->joinName(
                $usrReg->USUA_NOMB, 
                $usrReg->USUA_APPA, 
                $usrReg->USUA_APMA
            ) . " ($idReg)";

            $cataloguesF[] = $catalogue;
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F10AGCA',
            'S002V01P01HICA',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó los catálogos registrados.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $cataloguesF);
    }

    public function getUserConnections($id, $idUser, $line){
        DB::enableQueryLog();
        
        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la solicitud no está encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $line],
            ['USUA_IDUS', '=', $idUser]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

        $id = $this->encryptionController->decrypt($id);
        if(!$id){
            return $this->responseController->makeResponse(true, "El ID del usuario que desea consultar no está encriptado correctamente.", [], 400);
        }

        $usrCons = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $line],
            ['USUA_IDUS', '=', $id]
        ])->first();
        
        if(is_null($usrCons)){
            return $this->responseController->makeResponse(true, 'El usuario consultado no existe.', [], 404);
        }

        $connections = DB::table('S002V01TBIAC')->select(
            'BIAC_IPV4 AS IP4',
            'BIAC_IPV6 AS IP6',
            'BIAC_FECO AS FECHA',
            'BIAC_LATI AS LATITUD',
            'BIAC_LONG AS LONGITUD',
            'BIAC_PREC AS PRECISION',
            'BIAC_CIUD AS CIUDAD',
            'BIAC_ESTA AS ESTADO',
            'BIAC_PAIS AS PAIS',
            'BIAC_DISP AS DISPOSITIVO',
            'BIAC_SIOP AS SITEMAOPERATIVO',
            'BIAC_VSOP AS VERSIONSO',
            'BIAC_NAVE AS NAVEGADOR',
        )->where([
            ['BIAC_IDUS', '=', $id],
            ['BIAC_NULI', '=', $line]
        ])->orderBy('BIAC_FECO', 'desc')->get()->all();

        $responseArr = [
            'NOMBRE' => $usrCons->USUA_NOMB,
            'APPAT' => $usrCons->USUA_APPA,
            'APMAT' => $usrCons->USUA_APMA,
            'ULTCON' => $usrCons->USUA_ULCO,
            'HISTORIAL' => $connections
        ];

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        $nameCons = $this->functionsController->joinName($usrCons->USUA_NOMB, $usrCons->USUA_APPA, $usrCons->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F01MAUS',
            'S002V01P02HICO',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó las conexiones del usuario $nameCons ($id).",
            $idUser,
            $nowStr,
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, "EXITO", $responseArr);
    }

    public function getLastActions($idUser, $line){
        DB::enableQueryLog();
        $idUser = $this->encryptionController->decrypt($idUser);
        
        if(!$idUser){
            return $this->responseController->makeResponse(true, "El ID el usuario que realizó la petición no está encriptado correctamente.", [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $line]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $lastActions = DB::table('S002V01TUSUA')->leftJoin('S002V01TACCI', 'USUA_ULAC', '=', 'ACCI_IDAC')->select(
            'USUA_IDUS AS IDUSUARIO',
            'USUA_NOMB AS NOMBRE',
            'USUA_APPA AS APEPAT',
            'USUA_APMA AS APEMAT',
            'ACCI_IDMO AS IDMODULO',
            'ACCI_IDSM AS IDSUBMODULO',
            'ACCI_IDFU AS IDFUNCION',
            'ACCI_IDPA AS IDPANTALLA',
            'ACCI_TIAC AS TIPOACCION',
            'ACCI_DESC AS DESCRIPCION',
            'ACCI_FEAC AS FECHAACCION',
        )->get()->all();

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F02SAUS',
            'S002V01P01MOAC',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó las últimas acciónes realizadas.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $lastActions);
    }

    public function getUserActions($id, $idUser, $line){
        DB::enableQueryLog();

        $id = $this->encryptionController->decrypt($id);
        if(!$id){
            return $this->responseController->makeResponse(true, 'El ID del usuario que desea consultar no está encriptado correctamente.', [], 400);
        }

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la consulta no está encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $line]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $userInfo = DB::table('S002V01TUSUA')->select(
            'USUA_IDUS AS IDUSUARIO',
            'USUA_NOMB AS NOMBRE',
            'USUA_APPA AS APEPAT',
            'USUA_APMA AS APEMAT'
        )->where([
            ['USUA_IDUS', '=', $id],
            ['USUA_NULI', '=', $line],
        ])->get()->first();

        if(is_null($userInfo)){
            return $this->responseController->makeResponse(true, 'El usuario solicitado no existe.', [], 404);
        }

        $actions = DB::table('S002V01TACCI')->select(
            'MODU_NOMO AS MODULO',
            'SUBM_NOMB AS SUBMODULO',
            'FUNC_NOMB AS FUNCION',
            'PANT_NOMB AS PANTALLA',
            'ACCI_TIAC AS TIPOACCION',
            'ACCI_DESC AS DESCRIPCION',
            'ACCI_FEAC AS FECHA',
        )->leftJoin('S002V01TMODU', 'ACCI_IDMO', '=', 'MODU_IDMO')
        ->leftJoin('S002V01TSUBM', 'ACCI_IDSM', '=', 'SUBM_IDSM')
        ->leftJoin('S002V01TPANT', 'ACCI_IDPA', '=', 'PANT_IDPA')
        ->leftJoin('S002V01TFUNC', 'ACCI_IDFU', '=', 'FUNC_IDFU')->where([
            ['ACCI_IDUS', '=', $id],
            ['ACCI_NULI', '=', $line],
        ])->orderBy('ACCI_FEAC', 'desc')->get()->all();

        $userInfo->HISTORIAL = $actions;

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        $nameCons = $this->functionsController->joinName($userInfo->NOMBRE, $userInfo->APEPAT, $userInfo->APEMAT);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F02SAUS',
            'S002V01P02HIAC',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó las acciones del usuario $nameCons (" . $userInfo->IDUSUARIO . ")",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $userInfo);
    }

    public function readCatalogue($id, $idUser, $line){
        DB::enableQueryLog();

        $id = $this->encryptionController->decrypt($id);
        if(!$id){
            return $this->responseController->makeResponse(true, "El ID del catálogo no fue encriptado correctamente");
        }

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, "El ID del usuario no fue encriptado correctamente");
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $line]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado.', [], 404);
        }

        $idArr = explode("-", $id);
        $idArr2 = explode("=", end($idArr));

        $catalogue = DB::table('S002V01TAFAL')->where([
            ['AFAL_NULI', '=', $idArr[0]],
            ['AFAL_COMO', '=', $idArr[1]],
            ['AFAL_CLDO', '=', $idArr[2]],
            ['AFAL_FECR', '=', $idArr[3]],
            ['AFAL_NUSE', '=', $idArr2[0]],
            ['AFAL_NUVE', '=', $idArr2[1]],
        ])->first();

        if(is_null($catalogue)){
            return $this->responseController->makeResponse(true, 'El catálogo solicitado no existe.', [], 404);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $response = [
            "IDCATALOGO" => $this->encryptionController->encrypt($id),
            "EXTENSION" => $catalogue->AFAL_EXTE,
            "FECHA" => $now->timestamp
        ];
        
        copy($catalogue->AFAL_UBIC, 'C:\\ITTEC\\SAM\\Dev\\SistemaMantenimiento\\sistema-mantenimiento-back\\public_files\\' . $id);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F10AGCA',
            'S002V01P01HICA',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó el contenido del catálogo $id.",
            $idUser,
            $nowStr,
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, "EXITO", $response);
    }

    public function getAdvices($idUser, $line){
        DB::enableQueryLog();
        
        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la consulta no está encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $line]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $advices = DB::table('S002V01TAVIS')->select(
            'AVIS_IDAV AS IDAVISO',
            'AVIS_TIAV AS TITULO',
            'AVIS_CADU AS CADUCIDAD',
            DB::raw("IF(
                AVIS_ESTA != 'Activo',
                AVIS_ESTA,
                IF(
                    NOW() > AVIS_CADU,
                    'Caducado',
                    AVIS_ESTA
                )
            ) AS ESTATUS"),
        )->orderBy('AVIS_IDAV', 'asc')->get()->all();

        foreach($advices as $key=>$advice){
            $advice->IDAVISO = $this->encryptionController->encrypt($advice->IDAVISO);
            $advices[$key] = $advice;
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F03CRAV',
            'S002V01P01HIAV',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó los avisos registrados.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $advices);
    }

    public function getAdvice($idAdvice, $idUser, $line){
        DB::enableQueryLog();
        
        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la consulta no está encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $line]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado.', [], 404);
        }
        
        $idAdvice = $this->encryptionController->decrypt($idAdvice);
        if(!$idAdvice){
            return $this->responseController->makeResponse(true, 'El ID del aviso que consultó no está encriptado correctamente.', [], 400);
        }

        $advice = DB::table('S002V01TAVIS')->select([
            'AVIS_IDAV AS IDAVISO',
            'AVIS_TIAV AS TITULO',
            'AVIS_COAV AS CONTENIDO',
            'AVIS_CADU AS CADUCIDAD',
            'AVIS_ALAV AS AUDIENCIA',
            'AVIS_ADJU AS ADJUNTOS',
            DB::raw("IF(
                AVIS_ESTA != 'Activo',
                AVIS_ESTA,
                IF(
                    NOW() > AVIS_CADU,
                    'Caducado',
                    AVIS_ESTA
                )
            ) AS ESTADO"),
            'AVIS_FERE AS FECREG',
            DB::raw("CONCAT(USRE.USUA_NOMB, ' ', USRE.USUA_APPA, IF(ISNULL(USRE.USUA_APMA), '', ' '), IF(ISNULL(USRE.USUA_APMA), '', USRE.USUA_APMA), ' (', AVIS_USRE, ')') AS USRREG"),
            'AVIS_FEMO AS FECMOD',
            DB::raw("IF(ISNULL(USMO.USUA_NOMB), null, CONCAT(USMO.USUA_NOMB, ' ', USMO.USUA_APPA, IF(ISNULL(USMO.USUA_APMA), '', ' '), IF(ISNULL(USMO.USUA_APMA), '', USMO.USUA_APMA), ' (', AVIS_USMO, ')')) AS USRMOD")
        ])->where([
            ['AVIS_NULI', '=', $line],
            ['AVIS_IDAV', '=', $idAdvice]
        ])->join(DB::raw('S002V01TUSUA USRE'), 'USRE.USUA_IDUS', '=', 'AVIS_USRE')
        ->leftJoin(DB::raw('S002V01TUSUA USMO'), 'USMO.USUA_IDUS', '=', 'AVIS_USMO')->first();

        if(is_null($advice)){
            return $this->responseController->makeResponse(true, 'El aviso consultado no existe.', [], 404);
        }else if($advice->ESTADO != 'Activo'){
            return $this->responseController->makeResponse(true, 'El aviso consultado no está disponible.', [], 401);
        }

        $caduStr = $advice->CADUCIDAD;
        $caduObj = new Carbon($caduStr);
        $now = $this->functionsController->now();
        
        if($now->gt($caduObj)){
            return $this->responseController->makeResponse(true, 'El aviso consultado está caducado.', [], 401);
        }

        $attachedArr = json_decode($advice->ADJUNTOS, true);
        $attachedF = [];
        foreach($attachedArr as $fileCode){
            $nameArr = explode('=', $fileCode);
            $idArr = explode('-', $nameArr[0]);

            $file = DB::table('S002V01TAFAL')->where([
                ['AFAL_NULI', '=', $idArr[0]],
                ['AFAL_COMO', '=', $idArr[1]],
                ['AFAL_CLDO', '=', $idArr[2]],
                ['AFAL_FECR', '=', $idArr[3]],
                ['AFAL_NUSE', '=', $idArr[4]],
                ['AFAL_NUVE', '=', $nameArr[1]],
            ])->first();

            $attachedF[] = [
                'id' => $this->encryptionController->encrypt($fileCode),
                'name' => $file->AFAL_NOAR . '.' . $file->AFAL_EXTE,
                'size' => $file->AFAL_TAMA
            ];
        }

        $audienceArr = json_decode($advice->AUDIENCIA, true);
        foreach($audienceArr as $key=>$item){
            $itemName = '';
            if($item['type'] == 'user'){
                $user = DB::table('S002V01TUSUA')->where([
                    ['USUA_NULI', '=', $line],
                    ['USUA_IDUS', '=', $item['id']]
                ])->first();

                $itemName = $this->functionsController->joinName($user->USUA_NOMB, $user->USUA_APPA, $user->USUA_APMA);
            }else{
                $profile = DB::table('S002V01TPERF')->where([
                    ['PERF_NULI', '=', $line],
                    ['PERF_IDPE', '=', $item['id']]
                ])->first();

                $itemName = $profile->PERF_NOPE;
            }

            $item['name'] = $itemName;
            $item['id'] = $this->encryptionController->encrypt($item['id']);
            $audienceArr[$key] = $item;
        }

        $advice->AUDIENCIA = json_encode($audienceArr);
        $advice->ADJUNTOS = json_encode($attachedF);
        $advice->IDAVISO = $this->encryptionController->encrypt($advice->IDAVISO);

        $actions = DB::getQueryLog();
        $nowStr = $now->toDateTimeString();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F03CRAV',
            'S002V01P01HIAV',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó el aviso " . $advice->TITULO . " ($idAdvice).",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $advice);
    }

    public function publishAdvice(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'titulo' => 'required|string|max:100',
            'caducidad' => 'required|date_format:Y-m-d H:i:s',
            'contenido' => 'required|string',
            'audiencia' => 'required|json',
            'archivos' => 'required|json',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $advice = $request->all();
        $idUser = $this->encryptionController->decrypt($advice['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, "El ID del usuario que realizó la petición no fue encriptado correctamente", [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $advice['linea']]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $audienceArr = json_decode($advice['audiencia'], true);
        $audienceArrFn = [];
        foreach($audienceArr as $key=>$item){
            $itemArr = explode('-', $item);
            if(count($itemArr) != 2){
                return $this->responseController->makeResponse(true, "El elemento en la posición $key del arreglo de audiencia tiene un formáto inválido.", [], 400);
            }

            $idElementDec = $this->encryptionController->decrypt($itemArr[1]);
            if(!$idElementDec){
                return $this->responseController->makeResponse(true, "El ID del elemento en la posición $key del arreglo de audiencia no fue encriptado correctamente.", [], 400);
            }

            $element = null;
            if($itemArr[0] == 'prof'){
                $element = DB::table('S002V01TPERF')->where([
                    ['PERF_NULI', '=', $advice['linea']],
                    ['PERF_IDPE', '=', $idElementDec]
                ])->first();
            }else{
                $element = DB::table('S002V01TUSUA')->where([
                    ['USUA_NULI', '=', $advice['linea']],
                    ['USUA_IDUS', '=', $idElementDec]
                ])->first();
            }

            if(is_null($element)){
                return $this->responseController->makeResponse(true, "El elemento en la posición $key no existe.", [], 404);
            }

            $audienceArrFn[] = [
                'type' => $itemArr[0],
                'id' => $idElementDec
            ];
        }

        $audienceStr = json_encode($audienceArrFn);
        $filesArr = json_decode($advice['archivos'], true);
        $attachedArr = [];
        foreach($filesArr as $key=>$file){
            $idFileDec = $this->encryptionController->decrypt($file['id']);
            if(!$idFileDec){
                return $this->responseController->makeResponse(true, "El ID del archivo en la posición $key del arreglo de adjuntos no fue encriptado correctamente.", [], 400);
            }

            $document = DB::table('S002V01TARTE')->where([
                ['ARTE_IDAR', '=', $idFileDec],
                ['ARTE_NULI', '=', $advice['linea']]
            ])->first();

            if(is_null($document)){
                return $this->responseController->makeResponse(true, "El archivo en la posición $key del arreglo de adjuntos no existe.", [], 404);
            }else if($document->ARTE_ESTA == 'Eliminado'){
                return $this->responseController->makeResponse(true, "El archivo en la posición $key del arreglo de adjuntos está eliminado.", [], 404);
            }

            $fileRes = $this->documentManagementController->moveFinalFile(
                $advice['linea'],
                'ADSI',
                'AV',
                $document,
                $idUser
            );

            if(!$fileRes[0]){
                return $this->responseController->makeResponse(true, "El archivo en la posición $key no pudo ser procesado: $fileRes[1].", [], 400);
            }

            $attachedArr[] = $fileRes[1];
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $attachedStr = json_encode($attachedArr);
        $idav = DB::table('S002V01TAVIS')->insertGetId([
            'AVIS_NULI' => $advice['linea'],
            'AVIS_TIAV' => $advice['titulo'],
            'AVIS_COAV' => $advice['contenido'],
            'AVIS_ALAV' => $audienceStr,
            'AVIS_ADJU' => $attachedStr,
            'AVIS_CADU' => $advice['caducidad'],
            'AVIS_FERE' => $nowStr,
            'AVIS_USRE' => $idUser
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);

        $idac = $this->functionsController->registerActivity(
            $advice['linea'],
            'S002V01M01ADSI',
            'S002V01F03CRAV',
            'S002V01P02CNAV',
            'Registro',
            "El usuario $name (" . $usr->USUA_IDUS . ") registró un nuevo aviso.",
            $idUser,
            $nowStr,
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $advice['linea']);
        return $this->responseController->makeresponse(false, "EXITO", ['idav' => $this->encryptionController->encrypt($idav)]);
    }

    public function updateAdvice(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'id_advice' =>'required|string',
            'linea' => 'required|integer',
            'titulo' => 'required|string|max:100',
            'caducidad' => 'required|date_format:Y-m-d H:i:s',
            'contenido' => 'required|string',
            'audiencia' => 'required|json',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }
        
        $form = $request->all();
        $idUser = $this->encryptionController->decrypt($form['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, "El ID del usuario que realizó la solicitud no fue encriptado correctamente.", [], 401);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $form['linea']]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $idAdvice = $this->encryptionController->decrypt($form['id_advice']);
        if(!$idAdvice){
            return $this->responseController->makeResponse(true, "El ID del aviso no fue encriptado correctamente.", [], 401);
        }

        $advice = DB::table('S002V01TAVIS')->where([
            ['AVIS_NULI', '=', $form['linea']],
            ['AVIS_IDAV', '=', $idAdvice]
        ])->first();

        if(is_null($advice)){
            return $this->responseController->makeResponse(true, 'El aviso seleccionado no está registrado', [], 404);
        }

        $audienceArr = json_decode($form['audiencia'], true);
        $audienceArrFn = [];
        foreach($audienceArr as $key=>$item){
            $itemArr = explode('-', $item);
            if(count($itemArr) != 2){
                return $this->responseController->makeResponse(true, "El elemento en la posición $key del arreglo de audiencia tiene un formáto inválido.", [], 400);
            }

            $idElementDec = $this->encryptionController->decrypt($itemArr[1]);
            if(!$idElementDec){
                return $this->responseController->makeResponse(true, "El ID del elemento en la posición $key del arreglo de audiencia no fue encriptado correctamente.", [], 400);
            }

            $element = null;
            if($itemArr[0] == 'prof'){
                $element = DB::table('S002V01TPERF')->where([
                    ['PERF_NULI', '=', $form['linea']],
                    ['PERF_IDPE', '=', $idElementDec]
                ])->first();
            }else{
                $element = DB::table('S002V01TUSUA')->where([
                    ['USUA_NULI', '=', $form['linea']],
                    ['USUA_IDUS', '=', $idElementDec]
                ])->first();
            }

            if(is_null($element)){
                return $this->responseController->makeResponse(true, "El elemento en la posición $key no existe.", [], 404);
            }

            $audienceArrFn[] = [
                'type' => $itemArr[0],
                'id' => $idElementDec
            ];
        }

        $audienceStr = json_encode($audienceArrFn);
        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        DB::table('S002V01TAVIS')->where([
            ['AVIS_NULI', '=', $form['linea']],
            ['AVIS_IDAV', '=', $idAdvice]
        ])->update([
            'AVIS_TIAV' => $form['titulo'],
            'AVIS_COAV' => $form['contenido'],
            'AVIS_ALAV' => $audienceStr,
            'AVIS_CADU' => $form['caducidad'],
            'AVIS_FEMO' => $nowStr,
            'AVIS_USMO' => $idUser,
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);

        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M01ADSI',
            'S002V01F03CRAV',
            'S002V01P03EDAV',
            'Actualización',
            "El usuario $name (" . $usr->USUA_IDUS . ") actualizó el aviso $idAdvice.",
            $idUser,
            $nowStr,
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeresponse(false, "EXITO");
    }

    public function deleteAdvice(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'id_advice' =>'required|string',
            'linea' => 'required|integer',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $advice = $request->all();
        $idAdvice = $this->encryptionController->decrypt($advice['id_advice']);
        if(!$idAdvice){
            return $this->responseController->makeResponse(true, "El ID del aviso no fue encriptado correctamente.", [], 401);
        }

        $idUser = $this->encryptionController->decrypt($advice['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, "El ID del usuario que realizó la solicitud no fue encriptado correctamente.", [], 401);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $advice['linea']]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        DB::table('S002V01TAVIS')->where([
            ['AVIS_NULI', '=', $advice['linea']],
            ['AVIS_IDAV', '=', $idAdvice]
        ])->update([
            'AVIS_ESTA' => 'Eliminado',
            'AVIS_FEMO' => $nowStr,
            'AVIS_USMO' => $idUser,
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);

        $idac = $this->functionsController->registerActivity(
            $advice['linea'],
            'S002V01M01ADSI',
            'S002V01F03CRAV',
            'S002V01P01HIAV',
            'Eliminación',
            "El usuario $name (" . $usr->USUA_IDUS . ") eliminó el aviso $idAdvice.",
            $idUser,
            $nowStr,
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $advice['linea']);
        return $this->responseController->makeresponse(false, "EXITO");
    }

    public function getTwoStepsAuthUsers($idUser, $line){
        DB::enableQueryLog();
        
        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la consulta no está encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $line]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $usersAuth = DB::table('S002V01TUSUA')->select([
            'USUA_IDUS',
            'USUA_NOMB',
            'USUA_APPA',
            'USUA_APMA',
            'USUA_FADO',
        ])->where('USUA_NULI', '=', $line)->get()->all();

        $arrAuth = [];
        foreach($usersAuth as $user){
            $name = $this->functionsController->joinName($user->USUA_NOMB, $user->USUA_APPA, $user->USUA_APMA);
            $authEnabled = $user->USUA_FADO == 'Si';

            $arrAuth[] = [
                'IDUSUARIO' => $this->encryptionController->encrypt($user->USUA_IDUS),
                'NOMBREUSUARIO' => $name,
                'AUTHENABLED' => $authEnabled
            ];
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F04POSE',
            'S002V01P03ADPA',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó las autenticaciones en dos pasos de los usuarios.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $arrAuth);
    }

    public function setTwoStepsAuth(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'factor_doble' => 'required|in:Si,No',
            'usuario' => 'required|string',
            'linea' => 'required|integer',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $form = $request->all();
        $idUser = $this->encryptionController->decrypt($form['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, "El ID del usuario que realizó la solicitud no fue encriptado correctamente.", [], 401);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $form['linea']]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $idChange = $this->encryptionController->decrypt($form['usuario']);
        if(!$idChange){
            return $this->responseController->makeResponse(true, "El ID del usuario que desea modificar no fue encriptado correctamente.", [], 401);
        }

        $usrMod = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idChange],
            ['USUA_NULI', '=', $form['linea']]
        ])->first();

        if(is_null($usrMod)){
            return $this->responseController->makeResponse(true, 'El usuario que desea actualizar no está registrado', [], 404);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idChange],
            ['USUA_NULI', '=', $form['linea']]
        ])->update([
            'USUA_FADO' => $form['factor_doble'],
            'USUA_USMO' => $idUser,
            'USUA_FEMO' => $nowStr
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        $nameMod = $this->functionsController->joinName($usrMod->USUA_NOMB, $usrMod->USUA_APPA, $usrMod->USUA_APMA);

        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M01ADSI',
            'S002V01F04POSE',
            'S002V01P03ADPA',
            'Actualización',
            "El usuario $name (" . $usr->USUA_IDUS . ") cambió el factor de dos pasos del usuario $nameMod ($idChange).",
            $idUser,
            $nowStr,
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeresponse(false, "EXITO");
    }

    public function getPasswordFormat($idUser, $line){
        DB::enableQueryLog();
        
        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la consulta no está encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $line]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $politicsInfoRoute  = str_replace("app\Http\Controllers", "", __DIR__);
        $politicsInfoRoute .= "storage\\app\\files\\security-politics.json";
        $politicsStr = file_get_contents($politicsInfoRoute);
        $politicsArr = json_decode($politicsStr, true);

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $passwordFormat = $politicsArr['password_format'];
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F04POSE',
            'S002V01P01PACO',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó el formato de las contraseñas.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', ['password_format' => $passwordFormat]);
    }

    public function savePasswordFormat(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'min_length' => 'required|integer|between:8,30',
            'upper_enabled' => 'required|boolean',
            'min_upper' => 'required|integer|between:1,10',
            'number_enabled' => 'required|boolean',
            'min_number' => 'required|integer|between:1,10',
            'chars_enabled' => 'required|boolean',
            'min_chars' => 'required|integer|between:1,10',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $info = $request->all();
        $idUser = $this->encryptionController->decrypt($info['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $info['linea']],
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

        $filePoliticsExists = file_exists('C:\ITTEC\SAM\Dev\SistemaMantenimiento\sistema-mantenimiento-back\storage\app\files\security-politics.json');
        if(!$filePoliticsExists){
            return $this->responseController->makeResponse(true, 'El archivo de politicas de seguridad no fue encontrado.', [], 500);
        }

        $politicsStr = file_get_contents('C:\ITTEC\SAM\Dev\SistemaMantenimiento\sistema-mantenimiento-back\storage\app\files\security-politics.json');
        $politicsArr = json_decode($politicsStr, true);
        $passwordFormat = $politicsArr['password_format'];
        $formatBKP = $politicsStr;
        
        $passwordFormat['min_length'] = intval($info['min_length']);
        $passwordFormat['upper_enabled'] = intval($info['upper_enabled']) == 1;
        $passwordFormat['min_upper'] = intval($info['min_upper']);
        $passwordFormat['number_enabled'] = intval($info['number_enabled']) == 1;
        $passwordFormat['min_number'] = intval($info['min_number']);
        $passwordFormat['chars_enabled'] = intval($info['chars_enabled']) == 1;
        $passwordFormat['min_chars'] = intval($info['min_chars']);

        $politicsArr['password_format'] = $passwordFormat;
        $finalStr = json_encode($politicsArr); 
        file_put_contents('C:\ITTEC\SAM\Dev\SistemaMantenimiento\sistema-mantenimiento-back\storage\app\files\security-politics.json', $finalStr);

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        DB::table('S002V01TAPSE')->insert([
            'APSE_NULI' => $info['linea'],
            'APSE_USMO' => $idUser,
            'APSE_FEMO' => $nowStr,
            'APSE_VAAN' => $formatBKP,
            'APSE_VAAC' => $finalStr
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $info['linea'],
            'S002V01M01ADSI',
            'S002V01F04POSE',
            'S002V01P01PACO',
            'Actualización',
            "El usuario $name (" . $usr->USUA_IDUS . ") actualizó el formato de las contraseñas.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $info['linea']);
        return $this->responseController->makeResponse(false, 'EXITO.');
    }

    public function getSessionsDuration($idUser, $line){
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la consulta no está encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $line]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $politicsInfoRoute  = str_replace("app\Http\Controllers", "", __DIR__);
        $politicsInfoRoute .= "storage\\app\\files\\security-politics.json";
        $politicsStr = file_get_contents($politicsInfoRoute);
        $politicsArr = json_decode($politicsStr, true);

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $sessionsDuration = $politicsArr['sessions_duration'];
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F04POSE',
            'S002V01P02DUSE',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó la configuración de duración de sesiones.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', ['sessions_duration' => $sessionsDuration]);
    }

    public function saveSessionsDuration(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'days' => 'required|integer|max:30',
            'hours' => 'required|integer|max:23',
            'minutes' => 'required|integer|max:59',
            'seconds' => 'required|integer|max:59',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $info = $request->all();
        $idUser = $this->encryptionController->decrypt($info['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $info['linea']],
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

        $filePoliticsExists = file_exists('C:\ITTEC\SAM\Dev\SistemaMantenimiento\sistema-mantenimiento-back\storage\app\files\security-politics.json');
        if(!$filePoliticsExists){
            return $this->responseController->makeResponse(true, 'El archivo de politicas de seguridad no fue encontrado.', [], 500);
        }

        $politicsStr = file_get_contents('C:\ITTEC\SAM\Dev\SistemaMantenimiento\sistema-mantenimiento-back\storage\app\files\security-politics.json');
        $politicsArr = json_decode($politicsStr, true);
        $sessionsDuration = $politicsArr['sessions_duration'];
        $formatBKP = $politicsStr;

        $sessionsDuration['days'] = intval($info['days']);
        $sessionsDuration['hours'] = intval($info['hours']);
        $sessionsDuration['minutes'] = intval($info['minutes']);
        $sessionsDuration['seconds'] = intval($info['seconds']);

        $politicsArr['sessions_duration'] = $sessionsDuration;
        $finalStr = json_encode($politicsArr); 
        file_put_contents('C:\ITTEC\SAM\Dev\SistemaMantenimiento\sistema-mantenimiento-back\storage\app\files\security-politics.json', $finalStr);

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        DB::table('S002V01TAPSE')->insert([
            'APSE_NULI' => $info['linea'],
            'APSE_USMO' => $idUser,
            'APSE_FEMO' => $nowStr,
            'APSE_VAAN' => $formatBKP,
            'APSE_VAAC' => $finalStr
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $info['linea'],
            'S002V01M01ADSI',
            'S002V01F04POSE',
            'S002V01P02DUSE',
            'Actualización',
            "El usuario $name (" . $usr->USUA_IDUS . ") actualizó la duración de las sesiones.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $info['linea']);
        return $this->responseController->makeResponse(false, 'EXITO.');
    }

    public function getActiveSessionsNumber($idUser, $line){
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la consulta no está encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $line]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $politicsInfoRoute  = str_replace("app\Http\Controllers", "", __DIR__);
        $politicsInfoRoute .= "storage\\app\\files\\security-politics.json";
        $politicsStr = file_get_contents($politicsInfoRoute);
        $politicsArr = json_decode($politicsStr, true);

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $activeSessionsNumber = $politicsArr['active_sessions_number'];
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F04POSE',
            'S002V01P04NSAC',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó la configuración del número de sesiones activas.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', ['active_sessions_number' => $activeSessionsNumber]);
    }

    public function saveActiveSessionsNumber(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'sessions' => 'required|integer|between:1,5',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $info = $request->all();
        $idUser = $this->encryptionController->decrypt($info['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $info['linea']],
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

        $filePoliticsExists = file_exists('C:\ITTEC\SAM\Dev\SistemaMantenimiento\sistema-mantenimiento-back\storage\app\files\security-politics.json');
        if(!$filePoliticsExists){
            return $this->responseController->makeResponse(true, 'El archivo de politicas de seguridad no fue encontrado.', [], 500);
        }

        $politicsStr = file_get_contents('C:\ITTEC\SAM\Dev\SistemaMantenimiento\sistema-mantenimiento-back\storage\app\files\security-politics.json');
        $politicsArr = json_decode($politicsStr, true);
        $formatBKP = $politicsStr;
        
        $politicsArr['active_sessions_number'] = intval($info['sessions']);
        $finalStr = json_encode($politicsArr); 
        file_put_contents('C:\ITTEC\SAM\Dev\SistemaMantenimiento\sistema-mantenimiento-back\storage\app\files\security-politics.json', $finalStr);

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        DB::table('S002V01TAPSE')->insert([
            'APSE_NULI' => $info['linea'],
            'APSE_USMO' => $idUser,
            'APSE_FEMO' => $nowStr,
            'APSE_VAAN' => $formatBKP,
            'APSE_VAAC' => $finalStr
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $info['linea'],
            'S002V01M01ADSI',
            'S002V01F04POSE',
            'S002V01P04NSAC',
            'Actualización',
            "El usuario $name (" . $usr->USUA_IDUS . ") actualizó el número de sesiones activas.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $info['linea']);
        return $this->responseController->makeResponse(false, 'EXITO.');
    }

    public function activateMaintenanceMode(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'motivos' => 'required|string',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $info = $request->all();
        $idUser = $this->encryptionController->decrypt($info['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $info['linea']],
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

        
        $maintenanceModeExists = file_exists('C:\ITTEC\SAM\Dev\SistemaMantenimiento\sistema-mantenimiento-back\storage\app\files\maintenance_mode.json');
        if(!$maintenanceModeExists){
            return $this->responseController->makeResponse(true, 'El archivo de mantenimiento no fue encontrado.', [], 500);
        }
        
        $maintenanceStr = file_get_contents('C:\ITTEC\SAM\Dev\SistemaMantenimiento\sistema-mantenimiento-back\storage\app\files\maintenance_mode.json');
        $maintenanceArr = json_decode($maintenanceStr, true);

        if($maintenanceArr['activated']){
            return $this->responseController->makeResponse(true, 'El modo mantenimiento ya se encuentra activado', [], 401);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $idMant = DB::table('S002V01THMSA')->insertGetId([
            'HMSA_NULI' => $info['linea'],
            'HMSA_USAC' => $idUser,
            'HMSA_FEAC' => $nowStr,
            'HMSA_MOAC' => $info['motivos']
        ]);

        $maintenanceArr['activated'] = true;
        $maintenanceArr['last_activation_id'] = $idMant;
        $finalStr = json_encode($maintenanceArr);
        file_put_contents('C:\ITTEC\SAM\Dev\SistemaMantenimiento\sistema-mantenimiento-back\storage\app\files\maintenance_mode.json', $finalStr);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $info['linea'],
            'S002V01M01ADSI',
            'S002V01F05MOMA',
            'S002V01P01AMMA',
            'Registro',
            "El usuario $name (" . $usr->USUA_IDUS . ") activó el modo mantenimiento",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $info['linea']);
        return $this->responseController->makeResponse(false, 'EXITO.');
    }

    public function deactivateMaintenanceMode(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'justificacion' => 'required|string',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $info = $request->all();
        $idUser = $this->encryptionController->decrypt($info['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $info['linea']],
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

        $maintenanceModeExists = file_exists('C:\SistemaMantenimiento\sistema-mantenimiento-back\storage\app\files\maintenance_mode.json');
        if(!$maintenanceModeExists){
            return $this->responseController->makeResponse(true, 'El archivo de mantenimiento no fue encontrado.', [], 500);
        }

        $maintenanceStr = file_get_contents('C:\SistemaMantenimiento\sistema-mantenimiento-back\storage\app\files\maintenance_mode.json');
        $maintenanceArr = json_decode($maintenanceStr, true);

        if(!$maintenanceArr['activated']){
            return $this->responseController->makeResponse(true, 'El modo mantenimiento ya se encuentra desactivado', [], 401);
        }

        $maintenanceDB = DB::table('S002V01THMSA')->where([
            ['HMSA_IDAC', '=', $maintenanceArr['last_activation_id']],
            ['HMSA_NULI', '=', $info['linea']],
        ])->first();

        if(!is_null($maintenanceDB->HMSA_USDE)){
            $maintenanceArr['activated'] = false;
            $finalStr = json_encode($maintenanceArr);
            file_put_contents('C:\SistemaMantenimiento\sistema-mantenimiento-back\storage\app\files\maintenance_mode.json', $finalStr);

            return $this->responseController->makeResponse(true, 'El modo mantenimiento ya fue desactivado en la base de datos.', [], 500);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        DB::table('S002V01THMSA')->where([
            ['HMSA_IDAC', '=', $maintenanceArr['last_activation_id']],
            ['HMSA_NULI', '=', $info['linea']],
        ])->update([
            'HMSA_USDE' => $idUser,
            'HMSA_FEDE' => $nowStr,
            'HMSA_JUDE' => $info['justificacion']
        ]);

        
        $maintenanceArr['activated'] = false;
        $finalStr = json_encode($maintenanceArr);
        file_put_contents('C:\ITTEC\SAM\Dev\SistemaMantenimiento\sistema-mantenimiento-back\storage\app\files\maintenance_mode.json', $finalStr);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $info['linea'],
            'S002V01M01ADSI',
            'S002V01F05MOMA',
            'S002V01P01AMMA',
            'Actualización',
            "El usuario $name (" . $usr->USUA_IDUS . ") desactivó el modo mantenimiento",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $info['linea']);
        return $this->responseController->makeResponse(false, 'EXITO.');
    }

    public function getMaintenanceHistory($idUser, $line){
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la consulta no está encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $line]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $history = DB::table('S002V01THMSA')->where('HMSA_NULI', '=', $line)->get()->all();
        $historyF = array();

        foreach($history as $val){
            $activationDate = new Carbon($val->HMSA_FEAC, 'America/Mexico_city');
            $deactivationDate = new Carbon($val->HMSA_FEDE, 'America/Mexico_city');

            $diffDays = $deactivationDate->diffInDays($activationDate);
            $activationDate->addDays($diffDays);

            $diffHours = $deactivationDate->diffInHours($activationDate);
            $activationDate->addHours($diffHours);
            
            $diffMinutes = $deactivationDate->diffInMinutes($activationDate);
            $activationDate->addMinutes($diffMinutes);
            
            $diffSeconds = $deactivationDate->diffInSeconds($activationDate);

            $duration = "";
            if($diffDays > 0) $duration .= "$diffDays días, ";
            if($diffHours > 0) $duration .= "$diffHours horas, ";
            if($diffMinutes > 0) $duration .= "$diffMinutes minutos, ";
            if($diffSeconds > 0) $duration .= "$diffSeconds segundos, ";

            $duration = substr($duration, 0, -2);
            $usac = DB::table('S002V01TUSUA')->select([
                'USUA_NOMB', 'USUA_APPA', 'USUA_APMA'
            ])->where([
                ['USUA_NULI', '=', $line],
                ['USUA_IDUS', '=', $val->HMSA_USAC]
            ])->first();
            $nomUSAC = $this->functionsController->joinName($usac->USUA_NOMB, $usac->USUA_APPA, $usac->USUA_APMA);

            $usde = DB::table('S002V01TUSUA')->select([
                'USUA_NOMB', 'USUA_APPA', 'USUA_APMA'
            ])->where([
                ['USUA_NULI', '=', $line],
                ['USUA_IDUS', '=', $val->HMSA_USDE]
            ])->first();
            $nomUSDE = $this->functionsController->joinName($usde->USUA_NOMB, $usde->USUA_APPA, $usde->USUA_APMA);

            $historyF[] = [
                "IDACTIVACION" => $val->HMSA_IDAC,
                "LINEA" => $val->HMSA_NULI,
                "USUARIOACTIVO" => $nomUSAC . " (" . $val->HMSA_USAC . ")",
                "FECHAACTIVACION" => $val->HMSA_FEAC,
                "MOTIVOS" => $val->HMSA_MOAC,
                "USUARIODESACTIVO" => $nomUSDE . " (" . $val->HMSA_USDE . ")",
                "FECHADESACTIVACION" => $val->HMSA_FEDE,
                "JUSTIFICACION" => $val->HMSA_JUDE,
                "DURACION" => $duration
            ];
        }

        $now = $this->functionsController->now();
        $actions = DB::getQueryLog();
        $nowStr = $now->toDateTimeString();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F05MOMA',
            'S002V01P02HIMA',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó el historial de mantenimientos.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $historyF);
    }

    public function getMaintenanceStatus($idUser, $line){
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la consulta no está encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $line]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $maintenanceModeExists = file_exists('C:\ITTEC\SAM\Dev\SistemaMantenimiento\sistema-mantenimiento-back\storage\app\files\maintenance_mode.json');
        if(!$maintenanceModeExists){
            return $this->responseController->makeResponse(true, 'El archivo de mantenimiento no fue encontrado.', [], 500);
        }

        $maintenanceStr = file_get_contents('C:\ITTEC\SAM\Dev\SistemaMantenimiento\sistema-mantenimiento-back\storage\app\files\maintenance_mode.json');
        $maintenanceArr = json_decode($maintenanceStr, true);

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F05MOMA',
            'S002V01P02HIMA',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó el estado actual del modo mantenimiento.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', ['activated' => $maintenanceArr['activated']]);
    }

    public function getEmailServers($idUser, $line){
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la consulta no está encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $line]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $emailServers = DB::table('S002V01TSCEL')->where('SCEL_NULI', '=', $line)->orderBy('SCEL_PRIO')->get()->all();
        $serversArr = [];
        foreach($emailServers as $server){
            $usal = DB::table('S002V01TUSUA')->where([
                ['USUA_IDUS', '=', $server->SCEL_USAL],
                ['USUA_NULI', '=', $line],
            ])->first();
            $usalName = $this->functionsController->joinName($usal->USUA_NOMB, $usal->USUA_APPA, $usal->USUA_APMA);
            
            $usmo = DB::table('S002V01TUSUA')->where([
                ['USUA_IDUS', '=', $server->SCEL_USMO],
                ['USUA_NULI', '=', $line],
            ])->first();
            $usmoName = is_null($usmo) ? '-' : $this->functionsController->joinName($usmo->USUA_NOMB, $usmo->USUA_APPA, $usmo->USUA_APMA);

            $serversArr[] = [
                'USUARIO' => $server->SCEL_USUA,
                'HOST' => $server->SCEL_HOST,
                'PRIORIDAD' => $server->SCEL_PRIO,
                'USRREG' => "$usalName (" . $server->SCEL_USAL . ")",
                'FECREG' => $server->SCEL_FEAL,
                'USRMOD' => is_null($usmo) ? $usmoName : "$usalName (" . $server->SCEL_USMO . ")",
                'FECMOD' => is_null($server->SCEL_FEMO) ? '-' : $server->SCEL_FEMO,
                'ESTADO' => $server->SCEL_ESCO,
            ];
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F07CCEL',
            'S002V01P01CNSC',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó los servidores de correo electrónico registrados.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $serversArr);
    }

    public function registerEmailServer(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'user' => 'required|string|max:50',
            'host' => 'required|string|max:50',
            'priority' => 'required|integer|between:1,100',
            'smtpsec' => 'string|max:50',
            'port' => 'required|string|max:10',
            'password' => 'required|string|max:2000',
            'timeout' => 'required|string|max:3',
            'smtpaut' => 'required|string|max:2',
            'portals' => 'required|json',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $form = $request->all();
        $idUser = $this->encryptionController->decrypt($form['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $form['linea']],
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $smtpsec = isset($form['smtpsec']) ? $form['smtpsec'] : null;

        DB::table('S002V01TSCEL')->insert([
            'SCEL_NULI' => $form['linea'],
            'SCEL_USUA' => $form['user'],
            'SCEL_HOST' => $form['host'],
            'SCEL_PRIO' => $form['priority'],
            'SCEL_SESM' => $smtpsec,
            'SCEL_PUER' => $form['port'],
            'SCEL_CONT' => $form['password'],
            'SCEL_TIES' => $form['timeout'],
            'SCEL_AUSM' => $form['smtpaut'],
            'SCEL_ADPO' => $form['portals'],
            'SCEL_USAL' => $idUser,
            'SCEL_FEAL' => $nowStr,
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M01ADSI',
            'S002V01F07CCEL',
            'S002V01P01CNSC',
            'Registro',
            "El usuario $name (" . $usr->USUA_IDUS . ") registró el servidor $form[host]",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO.');
    }

    public function getEmailServer($host, $user, $idUser, $line){
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la consulta no está encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $line]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $host = $this->encryptionController->decrypt($host);
        if(!$host){
            return $this->responseController->makeResponse(true, 'El HOST del servidor no está encriptado correctamente.', [], 400);
        }

        $user = $this->encryptionController->decrypt($user);
        if(!$user){
            return $this->responseController->makeResponse(true, 'El USUARIO del servidor no está encriptado correctamente.', [], 400);
        }

        $server = DB::table('S002V01TSCEL')->select([
            'SCEL_USUA AS USUARIO',
            'SCEL_HOST AS HOST',
            'SCEL_PRIO AS PRIORIDAD',
            'SCEL_SESM AS SEGSMTP',
            'SCEL_PUER AS PUERTO',
            'SCEL_CONT AS CONTRASENA',
            'SCEL_TIES AS TIEMPOESPERA',
            'SCEL_AUSM AS AUTSMTP',
            'SCEL_ADPO AS PORTALES'
        ])->where([
            ['SCEL_NULI', '=', $line],
            ['SCEL_USUA', '=', $user],
            ['SCEL_HOST', '=', $host],
        ])->first();

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F07CCEL',
            'S002V01P01CNSC',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó el servidor $host.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $server);
    }

    public function updateEmailServer(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'user' => 'required|string|max:50',
            'host' => 'required|string|max:50',
            'priority' => 'required|integer|between:1,100',
            'smtpsec' => 'string|max:50',
            'port' => 'required|string|max:10',
            'password' => 'required|string|max:2000',
            'timeout' => 'required|string|max:3',
            'smtpaut' => 'required|string|max:2',
            'portals' => 'required|json',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $form = $request->all();
        $idUser = $this->encryptionController->decrypt($form['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $form['linea']]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

        $server = DB::table('S002V01TSCEL')->where([
            ['SCEL_NULI', '=', $form['linea']],
            ['SCEL_USUA', '=', $form['user']],
            ['SCEL_HOST', '=', $form['host']],
        ])->first();
        
        if(is_null($server)){
            return $this->responseController->makeResponse(true, 'El servidor que desea modificar no existe.', [], 404);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $smtpsec = isset($form['smtpsec']) ? $form['smtpsec'] : null;
        DB::table('S002V01TSCEL')->where([
            ['SCEL_NULI', '=', $form['linea']],
            ['SCEL_USUA', '=', $form['user']],
            ['SCEL_HOST', '=', $form['host']],
        ])->update([
            'SCEL_PRIO' => $form['priority'],
            'SCEL_SESM' => $smtpsec,
            'SCEL_PUER' => $form['port'],
            'SCEL_CONT' => $form['password'],
            'SCEL_TIES' => $form['timeout'],
            'SCEL_AUSM' => $form['smtpaut'],
            'SCEL_ADPO' => $form['portals'],
            'SCEL_USMO' => $idUser,
            'SCEL_FEMO' => $nowStr
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M01ADSI',
            'S002V01F07CCEL',
            'S002V01P01CNSC',
            'Actualización',
            "El usuario $name (" . $usr->USUA_IDUS . ") actualizó el servidor $form[host]",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO.');
    }

    public function deleteEmailServer(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'user' => 'required|string|max:50',
            'host' => 'required|string|max:50'
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $form = $request->all();
        $idUser = $this->encryptionController->decrypt($form['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $form['linea']]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

        $server = DB::table('S002V01TSCEL')->where([
            ['SCEL_NULI', '=', $form['linea']],
            ['SCEL_USUA', '=', $form['user']],
            ['SCEL_HOST', '=', $form['host']],
        ])->first();
        
        if(is_null($server)){
            return $this->responseController->makeResponse(true, 'El servidor que desea eliminar no existe.', [], 404);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        DB::table('S002V01TSCEL')->where([
            ['SCEL_NULI', '=', $form['linea']],
            ['SCEL_USUA', '=', $form['user']],
            ['SCEL_HOST', '=', $form['host']],
        ])->update([
            'SCEL_ESCO' => 'Eliminado',
            'SCEL_USMO' => $idUser,
            'SCEL_FEMO' => $nowStr
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M01ADSI',
            'S002V01F07CCEL',
            'S002V01P01CNSC',
            'Eliminación',
            "El usuario $name (" . $usr->USUA_IDUS . ") eliminó el servidor $form[host]",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO.');
    }

    public function deactivateEmailServer(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'user' => 'required|string|max:50',
            'host' => 'required|string|max:50',
            'state' => 'required|string|in:Activo,Inactivo',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $form = $request->all();
        $idUser = $this->encryptionController->decrypt($form['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $form['linea']]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

        $server = DB::table('S002V01TSCEL')->where([
            ['SCEL_NULI', '=', $form['linea']],
            ['SCEL_USUA', '=', $form['user']],
            ['SCEL_HOST', '=', $form['host']],
        ])->first();
        
        if(is_null($server)){
            return $this->responseController->makeResponse(true, 'El servidor que desea modificar no existe.', [], 404);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        DB::table('S002V01TSCEL')->where([
            ['SCEL_NULI', '=', $form['linea']],
            ['SCEL_USUA', '=', $form['user']],
            ['SCEL_HOST', '=', $form['host']],
        ])->update([
            'SCEL_ESCO' => $form['state'],
            'SCEL_USMO' => $idUser,
            'SCEL_FEMO' => $nowStr
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M01ADSI',
            'S002V01F07CCEL',
            'S002V01P01CNSC',
            'Actualización',
            "El usuario $name (" . $usr->USUA_IDUS . ") cambió el estado del servidor $form[host]",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO.');
    }

    public function registerEmailTemplate(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'module' => 'required|string|max:25',
            'submodule' => 'string|max:25',
            'function' => 'required|string|max:25',
            'screen' => 'required|string|max:25',
            'subject' => 'required|string|max:50',
            'body' => 'required|string',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $form = $request->all();
        $idUser = $this->encryptionController->decrypt($form['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $form['linea']]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

        $module = DB::table('S002V01TMODU')->where([
            ['MODU_IDMO', '=', $form['module']],
            ['MODU_NULI', '=', $form['linea']]
        ])->first();
        
        if(is_null($module)){
            return $this->responseController->makeResponse(true, 'El módulo seleccionado no existe.', [], 404);
        }

        $hasSubmodule = isset($form['submodule']);
        if($hasSubmodule){
            $submodule = DB::table('S002V01TSUBM')->where([
                ['SUBM_IDSM', '=', $form['submodule']],
                ['SUBM_IDMO', '=', $form['module']],
                ['SUBM_NULI', '=', $form['linea']],
            ])->first();
        
            if(is_null($submodule)){
                return $this->responseController->makeResponse(true, 'El submódulo seleccionado no existe.', [], 404);
            }
        }

        $submodule = $hasSubmodule ? $form['submodule'] : null;
        $function = DB::table('S002V01TFUNC')->where([
            ['FUNC_NULI', '=', $form['linea']],
            ['FUNC_IDFU', '=', $form['function']],
            ['FUNC_IDMO', '=', $form['module']],
            ['FUNC_IDSM', '=', $submodule],
        ])->first();
        
        if(is_null($function)){
            return $this->responseController->makeResponse(true, 'La función seleccionada no existe.', [], 404);
        }

        $screen = DB::table('S002V01TPANT')->where([
            ['PANT_NULI', '=', $form['linea']],
            ['PANT_IDPA', '=', $form['screen']],
            ['PANT_IDFU', '=', $form['function']],
        ])->first();
        
        if(is_null($screen)){
            return $this->responseController->makeResponse(true, 'La pantalla seleccionada no existe.', [], 404);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $idPlan = DB::table('S002V01TPCEL')->insertGetId([
            'PCEL_NULI' => $form['linea'],
            'PCEL_IDMO' => $form['module'],
            'PCEL_IDSM' => $submodule,
            'PCEL_IDFU' => $form['function'],
            'PCEL_IDPA' => $form['screen'],
            'PCEL_ASCO' => $form['subject'],
            'PCEL_CUCO' => $form['body'],
            'PCEL_USRE' => $idUser,
            'PCEL_FERE' => $nowStr,
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M01ADSI',
            'S002V01F08CPCE',
            'S002V01P02NPEP',
            'Registro',
            "El usuario $name (" . $usr->USUA_IDUS . ") registró la plantilla $form[subject] ($idPlan)",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO.');
    }

    public function getEmailTemplates($idUser, $line){
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la consulta no está encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $line]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $emailTemplates = DB::table('S002V01TPCEL')->select([
            'PCEL_IDPL AS IDPLANTILLA',
            'PCEL_NULI AS LINEA',
            'MODU_NOMO AS NOMBREMODULO',
            'SUBM_NOMB AS NOMBRESUBMODULO',
            'FUNC_NOMB AS NOMBREFUNCION',
            'PANT_NOMB AS NOMBREPANTALLA',
            'PCEL_ASCO AS ASUNTO',
            'PCEL_CUCO AS CUERPO',
            'PCEL_ESTA AS ESTADO',
            'PCEL_USRE AS USUARIOREGISTRA',
            'PCEL_FERE AS FECHAREGISTRO',
            'PCEL_USMO AS USUARIOMODIFICA',
            'PCEL_FEMO AS FECHAMODIFICACION'
        ])->join('S002V01TMODU', 'MODU_IDMO', '=', 'PCEL_IDMO')
        ->leftJoin('S002V01TSUBM', 'SUBM_IDSM', '=', 'PCEL_IDSM')
        ->join('S002V01TFUNC', 'FUNC_IDFU', '=', 'PCEL_IDFU')
        ->join('S002V01TPANT', 'PANT_IDPA', '=', 'PCEL_IDPA')->where([
            ['PCEL_NULI', '=', $line]
        ])->orderBy('PCEL_IDPL', 'asc')->get()->all();

        $emailTemplatesF = [];
        foreach($emailTemplates as $template){
            $usrTemplate = DB::table('S002V01TUSUA')->where([
                ['USUA_NULI', '=', $line],
                ['USUA_IDUS', '=', $template->USUARIOREGISTRA],
            ])->first();

            $idus = $template->USUARIOREGISTRA;
            $template->USUARIOREGISTRA = $this->functionsController->joinName(
                $usrTemplate->USUA_NOMB, 
                $usrTemplate->USUA_APPA, 
                $usrTemplate->USUA_APMA
            ) . " ($idus)";

            if(!is_null($template->USUARIOMODIFICA)){
                $usrTemplate = DB::table('S002V01TUSUA')->where([
                    ['USUA_NULI', '=', $line],
                    ['USUA_IDUS', '=', $template->USUARIOMODIFICA],
                ])->first();
    
                $idus = $template->USUARIOMODIFICA;
                $template->USUARIOMODIFICA = $this->functionsController->joinName(
                    $usrTemplate->USUA_NOMB, 
                    $usrTemplate->USUA_APPA, 
                    $usrTemplate->USUA_APMA
                ) . " ($idus)";
            }
            
            $emailTemplatesF[] = $template;
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F09MACS',
            'S002V01P01HIPL',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó las plantillas de correo electrónico registradas.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $emailTemplatesF);
    }

    public function getEmailTemplate($id, $idUser, $line){
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la consulta no está encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $line]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado.', [], 404);
        }

        $id = $this->encryptionController->decrypt($id);
        if(!$id){
            return $this->responseController->makeResponse(true, 'El ID de la plantilla consultada no está encriptado correctamente.', [], 400);
        }

        $emailTemplate = DB::table('S002V01TPCEL')->select([
            'PCEL_IDPL AS IDPLANTILLA',
            'PCEL_IDMO AS MODULO',
            'PCEL_IDSM AS SUBMODULO',
            'PCEL_IDFU AS FUNCION',
            'PCEL_IDPA AS PANTALLA',
            'PCEL_ASCO AS ASUNTO',
            'PCEL_CUCO AS CUERPO'
        ])->where([
            ['PCEL_IDPL', '=', $id],
            ['PCEL_NULI', '=', $line],
        ])->first();

        if(is_null($emailTemplate)){
            return $this->responseController->makeResponse(true, 'La plantilla solicitada no existe.', [], 404);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F09MACS',
            'S002V01P01HIPL',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó la plantilla " . $emailTemplate->ASUNTO . " ($id).",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $emailTemplate);
    }

    public function updateEmailTemplate(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'id_template' => 'required|string',
            'module' => 'required|string|max:25',
            'submodule' => 'string|max:25',
            'function' => 'required|string|max:25',
            'screen' => 'required|string|max:25',
            'subject' => 'required|string|max:50',
            'body' => 'required|string',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $form = $request->all();
        $idUser = $this->encryptionController->decrypt($form['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $form['linea']]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }
        
        $idTemplate = $this->encryptionController->decrypt($form['id_template']);
        if(!$idTemplate){
            return $this->responseController->makeResponse(true, 'El ID de la plantilla no fue encriptado correctamente.', [], 400);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $submodule = isset($form['submodule']) ? $form['submodule'] : null;
        DB::table('S002V01TPCEL')->where([
            ['PCEL_NULI', '=', $form['linea']],
            ['PCEL_IDPL', '=', $idTemplate]
        ])->update([
            'PCEL_IDMO' => $form['module'],
            'PCEL_IDSM' => $submodule,
            'PCEL_IDFU' => $form['function'],
            'PCEL_IDPA' => $form['screen'],
            'PCEL_ASCO' => $form['subject'],
            'PCEL_CUCO' => $form['body'],
            'PCEL_USMO' => $idUser,
            'PCEL_FEMO' => $nowStr,
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M01ADSI',
            'S002V01F08CPCE',
            'S002V01P02NPEP',
            'Actualización',
            "El usuario $name (" . $usr->USUA_IDUS . ") actualizó la plantilla $form[subject] ($idTemplate)",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO.');
    }

    public function deleteEmailTemplate(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'id_template' => 'required|string',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $form = $request->all();
        $idUser = $this->encryptionController->decrypt($form['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $form['linea']]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }
        
        $idTemplate = $this->encryptionController->decrypt($form['id_template']);
        if(!$idTemplate){
            return $this->responseController->makeResponse(true, 'El ID de la plantilla no fue encriptado correctamente.', [], 400);
        }
        
        $template = DB::table('S002V01TPCEL')->where([
            ['PCEL_IDPL', '=', $idTemplate],
            ['PCEL_NULI', '=', $form['linea']]
        ])->first();
        
        if(is_null($template)){
            return $this->responseController->makeResponse(true, 'La plantilla solicitada no existe.', [], 404);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        DB::table('S002V01TPCEL')->where([
            ['PCEL_IDPL', '=', $idTemplate],
            ['PCEL_NULI', '=', $form['linea']]
        ])->update([
            'PCEL_ESTA' => 'Eliminado',
            'PCEL_USMO' => $idUser,
            'PCEL_FEMO' => $nowStr,
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M01ADSI',
            'S002V01F08CPCE',
            'S002V01P01HIPL',
            'Eliminación',
            "El usuario $name (" . $usr->USUA_IDUS . ") eliminó la plantilla ($idTemplate)",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO.');
    }

    public function saveCatalogue(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'tipo' => 'required|string|max:35',
            'adjuntos' => 'required|json',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $form = $request->all();
        $idUser = $this->encryptionController->decrypt($form['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $form['linea']]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }
        
        $allowedFiles = ["Colonias", "Códigos postales", "Estados", "Localidades", "Municipios", "Países", "Régimen fiscal", "Monedas", "General"];
        if(!in_array($form['tipo'], $allowedFiles)){
            return $this->responseController->makeResponse(true, "El tipo de catálogo no está soportado.", [], 400);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $attachedFiles = json_decode($form['adjuntos'], true);
        $attachedArr = [];
        foreach($attachedFiles as $file){
            $idFile = $file['id'];
            $idFile = $this->encryptionController->decrypt($idFile);
            if(!$idFile){
                return $this->responseController->makeResponse(true, "El ID del archivo $file[name] no fue encriptado correctamente", [], 400);
            }

            $fileObj = DB::table('S002V01TARTE')->where([
                ['ARTE_IDAR', '=', $idFile],
                ['ARTE_NULI', '=', $form['linea']]
            ])->first();

            if(is_null($fileObj)){
                return $this->responseController->makeResponse(true, "El ID del archivo $file[name] no existe", [], 404);
            }else if($fileObj->ARTE_ESTA == 'Eliminado'){
                return $this->responseController->makeResponse(true, "El archivo $file[name] está eliminado", [], 404);
            }


            $fileRes = $this->documentManagementController->moveFinalFile(
                $form['linea'],
                'ADSI',
                'CA',
                $fileObj,
                $idUser
            );
            if(!$fileRes[0]){
                return $this->responseController->makeResponse(true, "El archivo no pudo guardarse: $fileRes[1]", [], 500);
            }
            
            $attachedArr[] = $this->encryptionController->encrypt($fileRes[1]);

            DB::table('S002V01TARTE')->where([
                ['ARTE_IDAR', '=', $idFile],
                ['ARTE_NULI', '=', $form['linea']],
            ])->update([
                'ARTE_ESTA' => 'Eliminado',
                'ARTE_USMO' => $idUser,
                'ARTE_FEMO' => $nowStr
            ]);
        }

        $lastVersionObj = DB::table('S002V01TCATA')->where([
            ['CATA_NULI', '=', $form['linea']],
            ['CATA_TICA', '=', $form['tipo']]
        ])->orderBy('CATA_IDCA', 'desc')->first();

        $lastVersion = 0;
        if(is_null($lastVersionObj)){
            $lastVersion = 1;
        }else{
            DB::table('S002V01TCATA')->where([
                ['CATA_NULI', '=', $form['linea']],
                ['CATA_IDCA', '=', $lastVersionObj->CATA_IDCA],
            ])->update([
                'CATA_ESTA' => 'Obsoleto',
            ]);

            $lastVersion = intval($lastVersionObj->CATA_VERS) + 1;
        }

        $attachedStr = json_encode($attachedArr);
        DB::table('S002V01TCATA')->insert([
            'CATA_NULI' => $form['linea'],
            'CATA_TICA' => $form['tipo'],
            'CATA_VERS' => $lastVersion,
            'CATA_ARRE' => $attachedStr,
            'CATA_USRE' => $idUser,
            'CATA_FERE' => $nowStr,
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);

        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M01ADSI',
            'S002V01F10AGCA',
            'S002V01P02NUCA',
            'Registro',
            "El usuario $name (" . $usr->USUA_IDUS . ") registró un nuevo catálogo.",
            $idUser,
            $nowStr,
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeresponse(false, "EXITO");
    }

    public function getCatalogueHistory($type, $idUser, $line){
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la consulta no está encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $line]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado.', [], 404);
        }

        $types = ['Colonias', 'Códigos postales', 'Estados', 'Localidades', 'Municipios', 'Países', 'Monedas', 'Régimen fiscal'];
        if(!in_array($type, $types)){
            return $this->responseController->makeResponse(true, 'El tipo de catálogo no está soportado.', [], 404);
        }

        $catalogueHistory = DB::table('S002V01TCATA')->select([
            'CATA_IDCA AS IDCATALOGO',
            'CATA_VERS AS VERSION',
            'CATA_ARRE AS ARCHIVO',
            'CATA_ESTA AS ESTADO',
            'CATA_USRE AS USUARIOREGISTRA',
            'CATA_FERE AS FECHAREGISTRO',
        ])->where([
            ['CATA_NULI', '=', $line],
            ['CATA_TICA', '=', $type],
        ])->get()->all();

        $cont = 0;
        foreach($catalogueHistory as $reg){
            $usrReg = DB::table('S002V01TUSUA')->where([
                ['USUA_NULI', '=', $line],
                ['USUA_IDUS', '=', $reg->USUARIOREGISTRA]
            ])->first();

            $catalogueHistory[$cont]->USUARIOREGISTRA = $this->functionsController->joinName(
                $usrReg->USUA_NOMB,
                $usrReg->USUA_APPA,
                $usrReg->USUA_APMA,
            ) . " (" . $reg->USUARIOREGISTRA . ")";

            $cont++;
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F10AGCA',
            'S002V01P01HICA',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó el historial de catálogos $type.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $catalogueHistory);
    }

    public function getMaterialIcons($idUser, $line){
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la consulta no está encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $line]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado.', [], 404);
        }

        $iconsStr = file_get_contents("C:\\ITTEC\\SAM\\Dev\\SistemaMantenimiento\\sistema-mantenimiento-back\\storage\\app\\files\\icons.json");
        $iconsArr = json_decode($iconsStr, true);
        $icons = $iconsArr['icons'];

        $iconsF = [];
        foreach($icons as $icon){
            if(!array_key_exists($icon['name'], $iconsF)){
                $iconsF[$icon['name']] = [
                    'name' => $icon['name'],
                    'categorie' => $icon['categories'],
                ];
            }
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            '-',
            '-',
            '-',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó los íconos de Material.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', array_values($iconsF));
    }

    public function getOrderPriorities($idUser, $line){
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la consulta no está encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $line],
            ['USUA_IDUS', '=', $idUser],
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $systemParamsExists = file_exists('C:\ITTEC\SAM\Dev\SistemaMantenimiento\sistema-mantenimiento-back\storage\app\files\system-params.json');
        if(!$systemParamsExists){
            return $this->responseController->makeResponse(true, 'El archivo de parámetros del sistema no fue encontrado.', [], 500);
        }

        $paramsStr = file_get_contents('C:\ITTEC\SAM\Dev\SistemaMantenimiento\sistema-mantenimiento-back\storage\app\files\system-params.json');
        $paramsArr = json_decode($paramsStr, true);

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F11PASI',
            'S002V01P08EPOT',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó la configuración de las prioridades de las órdenes de trabajo.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', ['order_priorities' => $paramsArr['order_priorities']]);
    }

    public function updateOrderPriorities(Request $request){
        DB::enableQueryLog();
        
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'priorities' => 'required|json',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $form = $request->all();
        $idUser = $this->encryptionController->decrypt($form['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $form['linea']]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

        $systemParamsExists = file_exists('C:\ITTEC\SAM\Dev\SistemaMantenimiento\sistema-mantenimiento-back\storage\app\files\system-params.json');
        if(!$systemParamsExists){
            return $this->responseController->makeResponse(true, 'El archivo de parámetros del sistema no fue encontrado.', [], 500);
        }

        $paramsStr = file_get_contents('C:\ITTEC\SAM\Dev\SistemaMantenimiento\sistema-mantenimiento-back\storage\app\files\system-params.json');
        $paramsArr = json_decode($paramsStr, true);

        $newPriorities = json_decode($form['priorities'], true);
        if(empty($newPriorities)){
            return $this->responseController->makeResponse(true, 'El arreglo de prioridades enviado está vacío', [], 400);
        }

        $paramsArr['order_priorities'] = $newPriorities;
        $paramsStr = json_encode($paramsArr);
        file_put_contents('C:\ITTEC\SAM\Dev\SistemaMantenimiento\sistema-mantenimiento-back\storage\app\files\system-params.json', $paramsStr);

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M01ADSI',
            'S002V01F11PASI',
            'S002V01P08EPOT',
            'Actualización',
            "El usuario $name (" . $usr->USUA_IDUS . ") actualizó la configuración de las prioridades de las órdenes de trabajo.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO');
    }

    public function getLoginImages($idUser, $line){
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la consulta no está encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $line]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $systemParamsExists = file_exists('C:\ITTEC\SAM\Dev\SistemaMantenimiento\sistema-mantenimiento-back\storage\app\files\system-params.json');
        if(!$systemParamsExists){
            return $this->responseController->makeResponse(true, 'El archivo de parámetros del sistema no fue encontrado.', [], 500);
        }

        $paramsStr = file_get_contents('C:\ITTEC\SAM\Dev\SistemaMantenimiento\sistema-mantenimiento-back\storage\app\files\system-params.json');
        $paramsArr = json_decode($paramsStr, true);

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F11PASI',
            'S002V01P04EILO',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó las imágenes de la pantalla del inicio de sesión.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', ['login_params' => $paramsArr['login_params']]);
    }

    public function saveLoginImage(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'tipo' => 'required|string|in:image,background',
            'archivo' => 'required|string',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $form = $request->all();
        $idUser = $this->encryptionController->decrypt($form['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $form['linea']]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

        $systemParamsExists = file_exists('C:\ITTEC\SAM\Dev\SistemaMantenimiento\sistema-mantenimiento-back\storage\app\files\system-params.json');
        if(!$systemParamsExists){
            return $this->responseController->makeResponse(true, 'El archivo de parámetros del sistema no fue encontrado.', [], 500);
        }

        $paramsStr = file_get_contents('C:\ITTEC\SAM\Dev\SistemaMantenimiento\sistema-mantenimiento-back\storage\app\files\system-params.json');
        $paramsArr = json_decode($paramsStr, true);
        
        $actualImage = $paramsArr["login_params"]["login_$form[tipo]"];
        $replaceImage = $this->encryptionController->decrypt($form['archivo']);
        if(!$replaceImage){
            return $this->responseController->makeResponse(true, 'El ID de la imágen enviada no fue encriptado correctamente.', [], 500);
        }

        $imageObj = DB::table('S002V01TARTE')->where([
            ['ARTE_IDAR', '=', $replaceImage],
            ['ARTE_NULI', '=', $form['linea']]
        ])->first();

        $ubiImgTmp = $imageObj->ARTE_UBTE;
        if(!file_exists($ubiImgTmp)){
            return $this->responseController->makeResponse(true, 'El archivo de la imagen enviada no existe.', [], 500);
        }

        $ubiImgAct = "C:\\ITTEC\\SAM\\Dev\\SistemaMantenimiento\\sistema-mantenimiento-back\\public\\assets\\$actualImage";
        if(!file_exists($ubiImgAct)){
            return $this->responseController->makeResponse(true, 'La imagen actual no existe.', [], 500);
        }

        $now = $this->functionsController->now();
        $timestamp = $now->timestamp;

        rename($ubiImgAct, "C:\\ITTEC\\SAM\\Dev\\SistemaMantenimiento\\sistema-mantenimiento-back\\public\\assets\\UPDATED_$timestamp.png");
        copy($ubiImgTmp, $ubiImgAct);

        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M01ADSI',
            'S002V01F11PASI',
            'S002V01P04EILO',
            'Actualización',
            "El usuario $name (" . $usr->USUA_IDUS . ") cambió la apariencia del login.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO.');
    }

    public function updateModuleIcons(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'module_icons' => 'required|string',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $form = $request->all();
        $idUser = $this->encryptionController->decrypt($form['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $form['linea']]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

        $moduleIconsStr = $form['module_icons'];
        $moduleIconsDec = $this->encryptionController->decrypt($moduleIconsStr);
        if(!$moduleIconsDec){
            return $this->responseController->makeResponse(true, 'El arreglo de módulos no fue encriptado correctamente.', [], 400);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $moduleIconsArr = json_decode($moduleIconsDec, true);
        foreach($moduleIconsArr as $modIcon){
            DB::table('S002V01TMODU')->where([
                ['MODU_NULI', '=', $form['linea']],
                ['MODU_IDMO', '=', $modIcon['IDMODULO']]
            ])->update([
                'MODU_ICON' => $modIcon['ICONOMODULO'],
                'MODU_USMO' => $idUser,
                'MODU_FEMO' => $nowStr,
            ]);
        }

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M01ADSI',
            'S002V01F11PASI',
            'S002V01P05EIMO',
            'Actualización',
            "El usuario $name (" . $usr->USUA_IDUS . ") cambió los íconos de los módulos.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO.');
    }

    public function updateSubmoduleIcons(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'submodule_icons' => 'required|string',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $form = $request->all();
        $idUser = $this->encryptionController->decrypt($form['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $form['linea']]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

        $submoduleIconsDec = $this->encryptionController->decrypt($form['submodule_icons']);
        if(!$submoduleIconsDec){
            return $this->responseController->makeResponse(true, 'El arreglo de submódulos no fue encriptado correctamente.', [], 400);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $submodulesArr = json_decode($submoduleIconsDec, true);
        foreach($submodulesArr as $module=>$submodules){
            foreach($submodules as $submodule){
                DB::table('S002V01TSUBM')->where([
                    ['SUBM_NULI', '=', $form['linea']],
                    ['SUBM_IDSM', '=', $submodule['IDSUBMODULO']],
                    ['SUBM_IDMO', '=', $module],
                ])->update([
                    'SUBM_ICON' => $submodule['ICONOSUBMODULO'],
                    'SUBM_USMO' => $idUser,
                    'SUBM_FEMO' => $nowStr,
                ]);
            }
        }

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M01ADSI',
            'S002V01F11PASI',
            'S002V01P06EISU',
            'Actualización',
            "El usuario $name (" . $usr->USUA_IDUS . ") cambió los íconos de los submódulos.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO.');
    }

    public function updateFunctionIcons(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'module_functions_icons' => 'required|string',
            'submodule_functions_icons' => 'required|string',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $form = $request->all();
        $idUser = $this->encryptionController->decrypt($form['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $form['linea']]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

        $moduleFunctionIconsDec = $this->encryptionController->decrypt($form['module_functions_icons']);
        if(!$moduleFunctionIconsDec){
            return $this->responseController->makeResponse(true, 'El arreglo de funciones por módulo no fue encriptado correctamente.', [], 400);
        }

        $submoduleFunctionIconsDec = $this->encryptionController->decrypt($form['submodule_functions_icons']);
        if(!$submoduleFunctionIconsDec){
            return $this->responseController->makeResponse(true, 'El arreglo de funciones por submódulo no fue encriptado correctamente.', [], 400);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $moduleFunctionIconsArr = json_decode($moduleFunctionIconsDec, true);
        foreach($moduleFunctionIconsArr as $module=>$functions){
            foreach($functions as $function){
                DB::table('S002V01TFUNC')->where([
                    ['FUNC_NULI', '=', $form['linea']],
                    ['FUNC_IDFU', '=', $function['IDFUNCION']],
                    ['FUNC_IDMO', '=', $module],
                    ['FUNC_IDSM', '=', null],
                ])->update([
                    'FUNC_ICON' => $function['ICONOFUNCION'],
                    'FUNC_USMO' => $idUser,
                    'FUNC_FEMO' => $nowStr,
                ]);
            }
        }

        $submoduleFunctionIconsArr = json_decode($submoduleFunctionIconsDec, true);
        foreach($submoduleFunctionIconsArr as $module=>$submodules){
            foreach($submodules as $submodule=>$functions){
                foreach($functions as $function){
                    DB::table('S002V01TFUNC')->where([
                        ['FUNC_NULI', '=', $form['linea']],
                        ['FUNC_IDFU', '=', $function['IDFUNCION']],
                        ['FUNC_IDMO', '=', $module],
                        ['FUNC_IDSM', '=', $submodule],
                    ])->update([
                        'FUNC_ICON' => $function['ICONOFUNCION'],
                        'FUNC_USMO' => $idUser,
                        'FUNC_FEMO' => $nowStr,
                    ]);
                }
            }
        }

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M01ADSI',
            'S002V01F11PASI',
            'S002V01P07EIFU',
            'Actualización',
            "El usuario $name (" . $usr->USUA_IDUS . ") cambió los íconos de las funciones.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO.');
    }

    public function getModuleFunctions($idMod, $idUser, $line){
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la solicitud no está encriptado correctamente', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $line]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $idMod = $this->encryptionController->decrypt($idMod);
        if(!$idMod){
            return $this->responseController->makeResponse(true, 'El ID del módulo no fue encriptado correctamente', [], 400);
        }

        $mod = DB::table('S002V01TMODU')->where([
            ['MODU_NULI', '=', $line],
            ['MODU_IDMO', '=', $idMod]
        ])->first();

        if(is_null($mod)){
            return $this->responseController->makeResponse(true, 'El módulo consultado no existe', [], 404);
        }

        $functions = DB::table('S002V01TFUNC')->where([
            ['FUNC_NULI', '=', $line],
            ['FUNC_IDMO', '=', $idMod],
            ['FUNC_IDSM', '=', null],
        ])->select([
            'FUNC_IDFU AS IDFUNCION',
            'FUNC_NOMB AS NOMBREFUNCION',
            'FUNC_ICON AS ICONOFUNCION'
        ])->get()->all();

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            '-',
            '-',
            '-',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó las funciones del módulo '" . $mod->MODU_NOMO . "'.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $functions);
    }

    public function registerSCADA(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'scada_name' => 'required|string|max:100',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $form = $request->all();
        $idUser = $this->encryptionController->decrypt($form['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $form['linea']]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        DB::table('S002V01TLISC')->insert([
            'LISC_NULI' => $form['linea'],
            'LISC_NOSC' => $form['scada_name'],
            'LISC_USRE' => $idUser,
            'LISC_FERE' => $nowStr,
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M01ADSI',
            'S002V01F06ASWA',
            'S002V01P02RESC',
            'Registro',
            "El usuario $name (" . $usr->USUA_IDUS . ") registró el SCADA $form[scada_name].",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO.');
    }

    public function getSCADAList($idUser, $line){
        DB::enableQueryLog();
        
        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la solicitud no está encriptado correctamente', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $line]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $list = DB::table('S002V01TLISC')->select([
            'LISC_IDSC AS IDSCADA',
            'LISC_NOSC AS NOMBRESCADA',
            'LISC_ESTA AS ESTATUS',
            'LISC_USRE AS USUARIOREGISTRO',
            'LISC_FERE AS FECHAREGISTRO',
            'LISC_USMO AS USUARIOMODIFICO',
            'LISC_FEMO AS FECHAMODIFICACION',
        ])->where([
            ['LISC_NULI', '=', $line],
            ['LISC_ESTA', '=', 'Activo'],
        ])->get()->all();

        foreach($list as $item){
            $usrReg = DB::table('S002V01TUSUA')->where([
                ['USUA_NULI', '=', $line],
                ['USUA_IDUS', '=', $item->USUARIOREGISTRO],
            ])->first();
            $nameReg = $this->functionsController->joinName($usrReg->USUA_NOMB, $usrReg->USUA_APPA, $usrReg->USUA_APMA) . " (" . $item->USUARIOREGISTRO . ")";
            $item->USUARIOREGISTRO = $nameReg;

            $usrMod = DB::table('S002V01TUSUA')->where([
                ['USUA_NULI', '=', $line],
                ['USUA_IDUS', '=', $item->USUARIOMODIFICO],
            ])->first();
            if(!is_null($usrMod)){
                $nameMod = $this->functionsController->joinName($usrReg->USUA_NOMB, $usrReg->USUA_APPA, $usrReg->USUA_APMA) . " (" . $item->USUARIOMODIFICO . ")";
                $item->USUARIOMODIFICO = $nameMod;
            }
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F06ASWA',
            'S002V01P01ADSC',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó los SCADA registrados en el sistema.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $list);
    }

    public function updateSCADA(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'id_scada' => 'required|string',
            'linea' => 'required|integer',
            'scada_name' => 'required|string|max:100',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $form = $request->all();
        $idUser = $this->encryptionController->decrypt($form['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $form['linea']],
            ['USUA_IDUS', '=', $idUser]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

        $idSCADA = $this->encryptionController->decrypt($form['id_scada']);
        if(!$idSCADA){
            return $this->responseController->makeResponse(true, 'El ID del SCADA no fue encriptado correctamente.', [], 400);
        }

        $scada = DB::table('S002V01TLISC')->where([
            ['LISC_NULI', '=', $form['linea']],
            ['LISC_IDSC', '=', $idSCADA],
        ])->first();
        
        if(is_null($scada)){
            return $this->responseController->makeResponse(true, 'El SCADA solicitado no existe.', [], 404);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        DB::table('S002V01TLISC')->where([
            ['LISC_NULI', '=', $form['linea']],
            ['LISC_IDSC', '=', $idSCADA],
        ])->update([
            'LISC_NOSC' => $form['scada_name'],
            'LISC_USMO' => $idUser,
            'LISC_FEMO' => $nowStr,
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M01ADSI',
            'S002V01F06ASWA',
            'S002V01P02RESC',
            'Actualización',
            "El usuario $name (" . $usr->USUA_IDUS . ") actualizó el SCADA $form[scada_name] ($idSCADA).",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO.');
    }

    public function deleteSCADA(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'id_scada' => 'required|string',
            'linea' => 'required|integer',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $form = $request->all();
        $idUser = $this->encryptionController->decrypt($form['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $form['linea']]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

        $idSCADA = $this->encryptionController->decrypt($form['id_scada']);
        if(!$idSCADA){
            return $this->responseController->makeResponse(true, 'El ID del SCADA no fue encriptado correctamente.', [], 400);
        }

        $scada = DB::table('S002V01TLISC')->where([
            ['LISC_NULI', '=', $form['linea']],
            ['LISC_IDSC', '=', $idSCADA],
        ])->first();
        
        if(is_null($scada)){
            return $this->responseController->makeResponse(true, 'El SCADA solicitado no existe.', [], 404);
        }

        $tokensSCADA = DB::table('S002V01TTASW')->where([
            ['TASW_NULI', '=', $form['linea']],
            ['TASW_IDSC', '=', $idSCADA],
        ])->get()->all();

        if(!empty($tokensSCADA)){
            return $this->responseController->makeResponse(true, 'No se pudo eliminar el SCADA porque tiene tokens asignados.', [], 400);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        DB::table('S002V01TLISC')->where([
            ['LISC_NULI', '=', $form['linea']],
            ['LISC_IDSC', '=', $idSCADA],
        ])->update([
            'LISC_ESTA' => 'Eliminado',
            'LISC_USMO' => $idUser,
            'LISC_FEMO' => $nowStr,
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M01ADSI',
            'S002V01F06ASWA',
            'S002V01P01ADSC',
            'Eliminación',
            "El usuario $name (" . $usr->USUA_IDUS . ") eliminó el SCADA " . $scada->LISC_NOSC . " ($idSCADA).",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO.');
    }

    public function generateSCADAtoken(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'id_scada' => 'required|string',
            'linea' => 'required|integer',
            'descripcion' => 'required|string',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $form = $request->all();
        $idUser = $this->encryptionController->decrypt($form['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la solicitud no está encriptado correctamente', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $form['linea']]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }
        
        $id = $this->encryptionController->decrypt($form['id_scada']);
        if(!$id){
            return $this->responseController->makeResponse(true, 'El ID del SCADA no está encriptado correctamente', [], 400);
        }

        $scada = DB::table('S002V01TLISC')->where([
            ['LISC_NULI', '=', $form['linea']],
            ['LISC_IDSC', '=', $id],
        ])->first();

        if(is_null($scada)){
            return $this->responseController->makeResponse(true, 'El SCADA consultado no existe', [], 404);
        }

        $now = $this->functionsController->now();
        $iat = $now->timestamp;
        $cad = $now->addYear()->timestamp;

        $payload = [
            "iss" => $scada->LISC_IDSC,
            "aud" => "dominio.syp.mx",
            "iat" => $iat,
            "cad" => $cad
        ];

        $token = JWT::encode($payload, $this->secretKey, 'EdDSA');
        $nowStr = $now->subYear()->toDateTimeString();

        DB::table('S002V01TTASW')->insert([
            'TASW_NULI' => $form['linea'],
            'TASW_IDSC' => $scada->LISC_IDSC,
            'TASW_TOKE' => $token,
            'TASW_DETO' => $form['descripcion'],
            'TASW_TIMO' => "PRUEBA",
            'TASW_USRE' => $idUser,
            'TASW_FERE' => $nowStr,
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M01ADSI',
            'S002V01F06ASWA',
            'S002V01P04GETO',
            'Registro',
            "El usuario $name (" . $usr->USUA_IDUS . ") registró y asignó el token \"$token\" al SCADA \"" . $scada->LISC_NOSC . " ($id)\".",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO.', ['token' => $token]);
    }

    public function getGeneratedTokens($idUser, $line){
        DB::enableQueryLog();
        
        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la solicitud no está encriptado correctamente', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $line]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $tokens = DB::table('S002V01TTASW')->join('S002V01TLISC', 'LISC_IDSC', '=', 'TASW_IDSC')->select([
            'LISC_NOSC AS NOMBRESCADA',
            'TASW_DETO AS DESCRIPCION',
            'TASW_ESTA AS ESTATUS',
            'TASW_USRE AS USUARIOREGISTRO',
            'TASW_FERE AS FECHAREGISTRO',
            'TASW_USMO AS USUARIOMODIFICACION',
            'TASW_FEMO AS FECHAMODIFICACION',
            'TASW_IDSC AS IDSCADA',
            'TASW_TOKE AS TOKEN'
        ])->where([
            ['TASW_NULI', '=', $line],
            ['LISC_ESTA', '=', 'Activo'],
        ])->get()->all();

        foreach($tokens as $token){
            $usre = DB::table('S002V01TUSUA')->where([
                ['USUA_NULI', '=', $line],
                ['USUA_IDUS', '=', $token->USUARIOREGISTRO]
            ])->first();
            $usreName = $this->functionsController->joinName($usre->USUA_NOMB, $usre->USUA_APPA, $usre->USUA_APMA) . " (" . $token->USUARIOREGISTRO . ")";
            $token->USUARIOREGISTRO = $usreName;

            
            $usmo = DB::table('S002V01TUSUA')->where([
                ['USUA_NULI', '=', $line],
                ['USUA_IDUS', '=', $token->USUARIOMODIFICACION]
            ])->first();
            if(!is_null($usmo)){
                $usmoName = $this->functionsController->joinName($usmo->USUA_NOMB, $usmo->USUA_APPA, $usmo->USUA_APMA) . " (" . $token->USUARIOMODIFICACION . ")";
                $token->USUARIOMODIFICACION = $usmoName;
            }

            $keyStr = $token->IDSCADA . "|" . $token->TOKEN;
            $keyEnc = $this->encryptionController->encrypt($keyStr);

            unset($token->IDSCADA);
            unset($token->TOKEN);
            
            $token->KEY = $keyEnc;
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F06ASWA',
            'S002V01P03ADTO',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó los tokens generados.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $tokens);
    }

    public function revokeToken(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'token_key' => 'required|string',
            'linea' => 'required|integer',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $form = $request->all();
        $idUser = $this->encryptionController->decrypt($form['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $form['linea']]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

        $tokenKey = $this->encryptionController->decrypt($form['token_key']);
        if(!$tokenKey){
            return $this->responseController->makeResponse(true, 'La llave del token solicitado no fue encriptado correctamente.', [], 400);
        }

        $keyArr = explode("|", $tokenKey);
        $token = DB::table('S002V01TTASW')->where([
            ['TASW_NULI', '=', $form['linea']],
            ['TASW_IDSC', '=', $keyArr[0]],
            ['TASW_TOKE', '=', $keyArr[1]]
        ])->first();

        if(is_null($token)){
            return $this->responseController->makeResponse(true, 'El token solicitado no existe.', [], 404);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        DB::table('S002V01TTASW')->where([
            ['TASW_NULI', '=', $form['linea']],
            ['TASW_IDSC', '=', $keyArr[0]],
            ['TASW_TOKE', '=', $keyArr[1]]
        ])->update([
            'TASW_ESTA' => 'Revocado',
            'TASW_USMO' => $idUser,
            'TASW_FEMO' => $nowStr,
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M01ADSI',
            'S002V01F06ASWA',
            'S002V01P03ADTO',
            'Actualización',
            "El usuario $name (" . $usr->USUA_IDUS . ") revocó el token \"$keyArr[1]\".",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO.');
    }

    public function registerWebService(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'url' => 'required|string|max:250',
            'description' => 'required|string|max:100',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }
        
        $form = $request->all();
        $idUser = $this->encryptionController->decrypt($form['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $form['linea']],
            ['USUA_IDUS', '=', $idUser]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $idWebService = DB::table('S002V01TLSWE')->insertGetId([
            'LSWE_NULI' => $form['linea'],
            'LSWE_URLX' => $form['url'],
            'LSWE_DESC' => $form['description'],
            'LSWE_USRE' => $idUser,
            'LSWE_FERE' => $nowStr
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M01ADSI',
            'S002V01F06ASWA',
            'S002V01P07RESW',
            'Registro',
            "El usuario $name (" . $usr->USUA_IDUS . ") registró el servicio web $form[url] ($idWebService).",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO.');
    }

    public function getWebServices($idUser, $line){
        DB::enableQueryLog();
        
        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la solicitud no está encriptado correctamente', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $line]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $webServices = DB::table('S002V01TLSWE')->where([
            ['LSWE_NULI', '=', $line]
        ])->select([
            'LSWE_IDSW AS IDSERVICIO',
            'LSWE_URLX AS URL',
            'LSWE_DESC AS DESCRIPCION',
            'LSWE_ESTA AS ESTADO',
            'LSWE_USRE AS USUREG',
            'LSWE_FERE AS FECREG',
            'LSWE_USMO AS USUMOD',
            'LSWE_FEMO AS FECMOD'
        ])->get()->all();

        foreach($webServices as $webService){
            $usre = DB::table('S002V01TUSUA')->where([
                ['USUA_NULI', '=', $line],
                ['USUA_IDUS', '=', $webService->USUREG],
            ])->first();

            $usreName = $this->functionsController->joinName($usre->USUA_NOMB, $usre->USUA_APPA, $usre->USUA_APMA) . " (" . $webService->USUREG . ")";
            $webService->USUREG = $usreName;

            $usmo = DB::table('S002V01TUSUA')->where([
                ['USUA_NULI', '=', $line],
                ['USUA_IDUS', '=', $webService->USUMOD],
            ])->first();

            if(!is_null($usmo)){
                $usmoName = $this->functionsController->joinName($usmo->USUA_NOMB, $usmo->USUA_APPA, $usmo->USUA_APMA) . " (" . $webService->USUMOD . ")";
                $webService->USUMOD = $usmoName;
            }

            $vinculos = DB::table('S002V01TTASW')->where([
                ['TASW_NULI', '=', $line],
                ['TASW_IDSW', '=', $webService->IDSERVICIO],
            ])->get()->all();

            $webService->VINCULOS = count($vinculos);
        }

        $now = $this->functionsController->now();
        $actions = DB::getQueryLog();
        $nowStr = $now->toDateTimeString();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F06ASWA',
            'S002V01P06LSWE',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó los servicios web almacenados.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $webServices);
    }

    public function getRoutedTokens($idUser, $line){
        DB::enableQueryLog();
        
        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la solicitud no está encriptado correctamente', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $line]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $list = DB::table('S002V01TTASW')
        ->join('S002V01TLISC', 'LISC_IDSC', '=', 'TASW_IDSC')
        ->leftJoin('S002V01TLSWE', 'LSWE_IDSW', '=', 'TASW_IDSW')
        ->where([
            ['TASW_NULI', '=', $line],
            ['TASW_ESTA', '!=', 'Revocado'],
        ])->get()->all();

        $arrList = [];
        foreach($list as $val){
            $key = $this->encryptionController->encrypt($val->TASW_IDSC . "|" . $val->TASW_TOKE);
            $arrList[] = [
                'SCADA' => $val->LISC_NOSC . " (" . $val->LISC_IDSC . ")",
                'DESCRIPCIONSC' => $val->TASW_DETO,
                'SERVICIOWEB' => is_null($val->LSWE_IDSW) ? 'No asignado' : $val->LSWE_URLX . " (" . $val->LSWE_IDSW . ")",
                'DESCRIPCIONSW' => $val->LSWE_DESC,
                'ESTADO' => $val->TASW_ESTA,
                "KEY" => $key
            ];
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F06ASWA',
            'S002V01P05ESSW',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó el enrutamiento de los SCADA a los servicios web.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $arrList);
    }

    public function changeTokenAccess(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'estado' => 'required|string|in:Activo,Inactivo',
            'key' => 'required|string',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $form = $request->all();
        $idUser = $this->encryptionController->decrypt($form['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $form['linea']],
            ['USUA_IDUS', '=', $idUser]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

        $keyStr = $this->encryptionController->decrypt($form['key']);
        if(!$keyStr){
            return $this->responseController->makeResponse(true, 'La llave del token no fue encriptada correctamente.', [], 400);
        }

        $keyArr = explode('|', $keyStr);
        if(count($keyArr) != 2){
            return $this->responseController->makeResponse(true, 'La llave del token no tiene un formato correcto.', [], 400);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        DB::table('S002V01TTASW')->where([
            ['TASW_NULI', '=', $form['linea']],
            ['TASW_IDSC', '=', $keyArr[0]],
            ['TASW_TOKE', '=', $keyArr[1]],
        ])->update([
            'TASW_ESTA' => $form['estado'],
            'TASW_TIMO' => 'Cambio de estado del token',
            'TASW_USMO' => $idUser,
            'TASW_FEMO' => $nowStr,
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M01ADSI',
            'S002V01F06ASWA',
            'S002V01P05ESSW',
            'Actualización',
            "El usuario $name (" . $usr->USUA_IDUS . ") actualizó el estado de un token.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO.');
    }

    public function changeWebService(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'key' => 'required|string',
            'id_web_service' => 'required|string',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $form = $request->all();
        $idUser = $this->encryptionController->decrypt($form['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $form['linea']],
            ['USUA_IDUS', '=', $idUser]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }
        
        $keyToken = $this->encryptionController->decrypt($form['key']);
        if(!$keyToken){
            return $this->responseController->makeResponse(true, 'La llave del token no está encriptada correctamente.', [], 400);
        }

        $keyArr = explode('|', $keyToken);
        $token = DB::table('S002V01TTASW')->where([
            ['TASW_NULI', '=', $form['linea']],
            ['TASW_IDSC', '=', $keyArr[0]],
            ['TASW_TOKE', '=', $keyArr[1]],
        ])->first();

        if(is_null($token)){
            return $this->responseController->makeResponse(true, 'El token solicitado no existe.', [], 404);
        }

        $idWS = $this->encryptionController->decrypt($form['id_web_service']);
        if(!$idWS){
            return $this->responseController->makeResponse(true, 'El ID del servicio web no fue encriptado correctamente.', [], 400);
        }

        $webService = DB::table('S002V01TLSWE')->where([
            ['LSWE_NULI', '=', $form['linea']],
            ['LSWE_IDSW', '=', $idWS]
        ])->first();
        
        if(is_null($webService)){
            return $this->responseController->makeResponse(true, 'El servicio web solicitado no existe', [], 404);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        DB::table('S002V01TTASW')->where([
            ['TASW_NULI', '=', $form['linea']],
            ['TASW_IDSC', '=', $keyArr[0]],
            ['TASW_TOKE', '=', $keyArr[1]],
        ])->update([
            'TASW_IDSW' => $idWS,
            'TASW_TIMO' => 'Cambio de servicio web',
            'TASW_USMO' => $idUser,
            'TASW_FEMO' => $nowStr,
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M01ADSI',
            'S002V01F06ASWA',
            'S002V01P05ESSW',
            'Actualización',
            "El usuario $name (" . $usr->USUA_IDUS . ") actualizó el servicio web de un SCADA.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO.');
    }

    public function updateWebService(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'id_web_service' => 'required|string',
            'url' => 'required|string|max:250',
            'description' => 'required|string|max:100',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $form = $request->all();
        $idUser = $this->encryptionController->decrypt($form['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $form['linea']],
            ['USUA_IDUS', '=', $idUser]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

        $idWS = $this->encryptionController->decrypt($form['id_web_service']);
        if(!$idWS){
            return $this->responseController->makeResponse(true, 'El ID del servicio web no fue encriptado correctamente.', [], 400);
        }

        $now = $this->functionsController->now();
        $nowStr = $now ->toDateTimeString();
        DB::table('S002V01TLSWE')->where([
            ['LSWE_IDSW', '=', $idWS],
            ['LSWE_NULI', '=', $form['linea']]
        ])->update([
            'LSWE_URLX' => $form['url'],
            'LSWE_DESC' => $form['description'],
            'LSWE_USMO' => $idUser,
            'LSWE_FEMO' => $nowStr,
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M01ADSI',
            'S002V01F06ASWA',
            'S002V01P07RESW',
            'Actualización',
            "El usuario $name (" . $usr->USUA_IDUS . ") actualizó el servicio web $form[url] ($idWS).",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO.');
    }

    public function changeWSstatus(Request $request){
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'id_web_service' => 'required|string',
            'status' => 'required|string|in:Activo,Inactivo',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $form = $request->all();
        $idUser = $this->encryptionController->decrypt($form['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $form['linea']],
            ['USUA_IDUS', '=', $idUser]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

        $idWS = $this->encryptionController->decrypt($form['id_web_service']);
        if(!$idWS){
            return $this->responseController->makeResponse(true, 'El ID del servicio web no fue encriptado correctamente.', [], 400);
        }

        $webService = DB::table('S002V01TLSWE')->where([
            ['LSWE_NULI', '=', $form['linea']],
            ['LSWE_IDSW', '=', $idWS]
        ])->first();
        
        if(is_null($webService)){
            return $this->responseController->makeResponse(true, 'El servicio web solicitado no existe.', [], 404);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        DB::table('S002V01TLSWE')->where([
            ['LSWE_NULI', '=', $form['linea']],
            ['LSWE_IDSW', '=', $idWS],
        ])->update([
            'LSWE_ESTA' => $form['status'],
            'LSWE_USMO' => $idUser,
            'LSWE_FEMO' => $nowStr,
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M01ADSI',
            'S002V01F06ASWA',
            'S002V01P07RESW',
            'Actualización',
            "El usuario $name (" . $usr->USUA_IDUS . ") actualizó el estado servicio web de un SCADA.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO.');
    }

    public function getBackupsList($idUser, $line){
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la solicitud no está encriptado correctamente', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $line]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $backups = DB::table('S002V01TRESP')->select([
            'RESP_IDRE AS IDRESPALDO',
            'RESP_FERE AS FECHARESPALDO',
            'RESP_ESTA AS ESTADO',
            'RESP_LORE AS LOG',
        ])->where('RESP_NULI', '=', $line)->get()->all();

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F12GERE',
            'S002V01P01HRES',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó la lista de respaldos.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $backups);
    }

    public function getSubmoduleFunctions($idMod, $idSub, $idUser, $line){
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la solicitud no está encriptado correctamente', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_IDUS', '=', $idUser],
            ['USUA_NULI', '=', $line]
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $idMod = $this->encryptionController->decrypt($idMod);
        if(!$idMod){
            return $this->responseController->makeResponse(true, 'El ID del módulo no está encriptado correctamente', [], 400);
        }

        $mod = DB::table('S002V01TMODU')->where([
            ['MODU_NULI', '=', $line],
            ['MODU_IDMO', '=', $idMod]
        ])->first();

        if(is_null($mod)){
            return $this->responseController->makeResponse(true, 'El módulo enviado no existe', [], 404);
        }

        $idSub = $this->encryptionController->decrypt($idSub);
        if(!$idSub){
            return $this->responseController->makeResponse(true, 'El ID del submódulo no está encriptado correctamente', [], 400);
        }

        $sub = DB::table('S002V01TSUBM')->where([
            ['SUBM_NULI', '=', $line],
            ['SUBM_IDSM', '=', $idSub],
            ['SUBM_IDMO', '=', $idMod],
        ])->first();

        if(is_null($sub)){
            return $this->responseController->makeResponse(true, 'El submódulo enviado no existe', [], 404);
        }

        $functions = DB::table('S002V01TFUNC')->select([
            'FUNC_IDFU AS IDFUNCION',
            'FUNC_NOMB AS NOMBREFUNCION',
            'FUNC_ICON AS ICONOFUNCION',
        ])->where([
            ['FUNC_NULI', '=', $line],
            ['FUNC_IDMO', '=', $idMod],
            ['FUNC_IDSM', '=', $idSub],
        ])->get()->all();

        foreach($functions as $function){
            $screens = DB::table('S002V01TPANT')->where([
                ['PANT_NULI', '=', $line],
                ['PANT_IDFU', '=', $function->IDFUNCION],
            ])->get()->all();

            $function->HASSCREENS = count($screens) > 0;
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            '-',
            '-',
            '-',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó las funciones del submódulo " . $sub->SUBM_NOMB . " ($idSub).",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $functions);
    }

    public function getTaxInformation($line){
        try {
            $getTaxInformation = DB::table('S002V01TINFI')
                ->where('INFI_NULI','=',$line)
                ->where('INFI_ESTA','=','Activo')
                ->first([
                    'INFI_IDIN',
                    'INFI_NOFI',
                    'INFI_XRFC',
                    'INFI_COEL',
                    'INFI_CORF',
                    'INFI_LAD1',
                    'INFI_TEL1',
                    'INFI_LAD2',
                    'INFI_TEL2',
                    'INFI_COPO',
                    'INFI_TIVI',
                    'INFI_VIAL',
                    'INFI_ENCA',
                    'INFI_YCAL',
                    'INFI_NUEX',
                    'INFI_NUIN',
                    'INFI_COLO',
                    'INFI_LOCA',
                    'INFI_MUNI',
                    'INFI_ENFE',
                    'INFI_PAIS',
                    'INFI_USRE',
                    'INFI_FERE',
                    'INFI_USMO',
                    'INFI_FEMO',
                ]);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_SYSTEM_ADMINISTRATOR_GET_TAX_INFORMATION000: Ocurrió un error al obtener la información fiscal.", [], 401);
        }
        return $this->responseController->makeResponse(false, 'EXITO', $getTaxInformation);
    }

    public function getCountriesList($idUser, $line) {
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la solicitud no está encriptado correctamente', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $line],
            ['USUA_IDUS', '=', $idUser],
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $catalogue = DB::table('S002V01TCATA')->where([
            ['CATA_TICA', '=', 'Países'],
            ['CATA_ESTA', '=', 'Activo'],
            ['CATA_NULI', '=', $line],
        ])->orderBy('CATA_VERS', 'desc')->first();

        if(is_null($catalogue)){
            return $this->responseController->makeResponse(true, 'Aún no se ha registrado ningún catálogo de paises', [], 404);
        }

        $filesArr = json_decode($catalogue->CATA_ARRE, true);
        $fileDec = $this->encryptionController->decrypt($filesArr[0]);
        if(!$fileDec){
            return $this->responseController->makeResponse(true, 'El ID catálogo vigente de países no fue encriptado correctamente.', [], 400);
        }

        $fileArr = explode("=", $fileDec);
        $idArr = explode("-", $fileArr[0]);
        $file = DB::table('S002V01TAFAL')->where([
            ['AFAL_NULI', '=', $line],
            ['AFAL_COMO', '=', $idArr[1]],
            ['AFAL_CLDO', '=', $idArr[2]],
            ['AFAL_FECR', '=', $idArr[3]],
            ['AFAL_NUSE', '=', $idArr[4]],
            ['AFAL_NUVE', '=', $fileArr[1]],
        ])->first();

        if(is_null($file)){
            return $this->responseController->makeResponse(true, 'El archivo asociado al catálogo vigente de países no está registrado.', [], 404);
        }else if($file->AFAL_ESTA == 'Eliminado'){
            return $this->responseController->makeResponse(true, 'El archivo asociado al catálogo vigente de países está eliminado.', [], 404);
        }else if(!file_exists($file->AFAL_UBIC)){
            return $this->responseController->makeResponse(true, 'El archivo asociado al catálogo vigente de países no existe en el almacenamiento final.', [], 404);
        }

        $spreadsheet = IOFactory::load($file->AFAL_UBIC);
        $sheetNames = $spreadsheet->getSheetNames();
        if(!in_array('c_Pais', $sheetNames)){
            return $this->responseController->makeResponse(true, 'El archivo asociado no contiene la hoja c_Pais.', [], 400);
        }

        $worksheet = $spreadsheet->getActiveSheet();
        $fileValidatorCell = $worksheet->getCell('A5')->getValue();

        if($fileValidatorCell != 'c_Pais'){
            return $this->responseController->makeResponse(true, 'El archivo asociado no tiene un formato válido.', [], 400);
        }

        $countries = [];
        $maxRow = $worksheet->getHighestRow();
        for($row = 6; $row <= $maxRow; $row++){
            $countryKey = $worksheet->getCell("A$row")->getValue();
            $countryName = $worksheet->getCell("B$row")->getValue();

            $countryInfo = DB::table('S002V01TPAIS')->where([
                ['PAIS_NULI', '=', $line],
                ['PAIS_IDPA', '=', $countryKey],
            ])->first();

            $countries[] = [
                'COUNTRY_ID' => $countryKey,
                'NAME' => $countryName,
                'LADA' => is_null($countryInfo) ? null : $countryInfo->PAIS_LADA,
                'NOMECLARUTA_ISO2' => is_null($countryInfo) ? null : $countryInfo->PAIS_NOME
            ];
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F10AGCA',
            'S002V01P01HICA',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó el contenido del catálogo de Países.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $countries);
    }

    public function saveCatalogueConfig(Request $request) {
        DB::enableQueryLog();

        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'catalogue_type' => 'required|string',
            'config' => 'required|json',
        ]);


        if($validator->fails()){
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors(
                    $validator->errors()->messages()
                ),
                401
            );
        }

        $form = $request->all();
        $idUser = $this->encryptionController->decrypt($form['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $form['linea']],
            ['USUA_IDUS', '=', $idUser]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }
        
        $allowedFiles = ["Colonias", "Códigos postales", "Estados", "Localidades", "Municipios", "Países", "Monedas", "Régimen fiscal"];
        if(!in_array($form['catalogue_type'], $allowedFiles)){
            return $this->responseController->makeResponse(true, "El tipo de catálogo no está soportado.", [], 400);
        }

        $catalogue = DB::table('S002V01TCATA')->where([
            ['CATA_TICA', '=', $form['catalogue_type']],
            ['CATA_ESTA', '=', 'Activo'],
            ['CATA_NULI', '=', $form['linea']],
        ])->orderBy('CATA_VERS', 'desc')->first();

        if(is_null($catalogue)){
            return $this->responseController->makeResponse(true, "Aún no se ha registrado ningún catálogo de $form[catalogue_type]", [], 404);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        switch($form['catalogue_type']){
            case "Países":
                $configArr = json_decode($form['config'], true);
                foreach($configArr as $val){
                    $country = DB::table('S002V01TPAIS')->where([
                        ['PAIS_NULI', '=', $form['linea']],
                        ['PAIS_IDPA', '=', $val['COUNTRY_ID']],
                    ])->first();

                    if(is_null($val['COUNTRY_ID'])) continue;

                    if(is_null($country)){
                        DB::table('S002V01TPAIS')->insert([
                            'PAIS_NULI' => $form['linea'],
                            'PAIS_IDPA' => $val['COUNTRY_ID'],
                            'PAIS_NOMB' => $val['NAME'],
                            'PAIS_NOME' => $val['NOMECLARUTA_ISO2'],
                            'PAIS_LADA' => $val['LADA'],
                            'PAIS_USRE' => $idUser,
                            'PAIS_FERE' => $nowStr,
                        ]);
                    }else{
                        DB::table('S002V01TPAIS')->where([
                            ['PAIS_NULI', '=', $form['linea']],
                            ['PAIS_IDPA', '=', $val['COUNTRY_ID']],
                        ])->update([
                            'PAIS_NOME' => $val['NOMECLARUTA_ISO2'],
                            'PAIS_LADA' => $val['LADA'],
                            'PAIS_USMO' => $idUser,
                            'PAIS_FEMO' => $nowStr,
                        ]);
                    }
                }
            break;
            case "Códigos postales":
                $configArr = json_decode($form['config'], true);
                foreach($configArr as $val){
                    $zipCode = DB::table('S002V01TCOPO')->where([
                        ['COPO_NULI', '=', $form['linea']],
                        ['COPO_COPO', '=', $val['CODIGO_POSTAL']],
                        ['COPO_COES', '=', $val['ESTADO']],
                    ])->first();

                    if(is_null($val['CODIGO_POSTAL'])) continue;

                    if(is_null($zipCode)){
                        DB::table('S002V01TCOPO')->insert([
                            'COPO_NULI' => $form['linea'],
                            'COPO_COPO' => $val['CODIGO_POSTAL'],
                            'COPO_COES' => $val['ESTADO'],
                            'COPO_COMU' => $val['MUNICIPIO'],
                            'COPO_COLO' => $val['LOCALIDAD'],
                            'COPO_USRE' => $idUser,
                            'COPO_FERE' => $nowStr,
                        ]);
                    }else{
                        DB::table('S002V01TCOPO')->where([
                            ['COPO_NULI', '=', $form['linea']],
                            ['COPO_COPO', '=', $val['CODIGO_POSTAL']],
                            ['COPO_COES', '=', $val['ESTADO']],
                        ])->update([
                            'COPO_COMU' => $val['MUNICIPIO'],
                            'COPO_COLO' => $val['LOCALIDAD'],
                            'COPO_USMO' => $idUser,
                            'COPO_FEMO' => $nowStr,
                        ]);
                    }
                }
            break;
            case "Estados":
                $configArr = json_decode($form['config'], true);
                foreach($configArr as $val){
                    $state = DB::table('S002V01TESTA')->where([
                        ['ESTA_NULI', '=', $form['linea']],
                        ['ESTA_COES', '=', $val['STATE_KEY']],
                        ['ESTA_COPA', '=', $val['COUNTRY_KEY']],
                    ])->first();

                    if(is_null($val['STATE_KEY'])) continue;

                    if(is_null($state)){
                        DB::table('S002V01TESTA')->insert([
                            'ESTA_NULI' => $form['linea'],
                            'ESTA_COES' => $val['STATE_KEY'],
                            'ESTA_COPA' => $val['COUNTRY_KEY'],
                            'ESTA_NOES' => $val['STATE_NAME'],
                            'ESTA_USRE' => $idUser,
                            'ESTA_FERE' => $nowStr,
                        ]);
                    }else{
                        DB::table('S002V01TESTA')->where([
                            ['ESTA_NULI', '=', $form['linea']],
                            ['ESTA_COES', '=', $val['STATE_KEY']],
                            ['ESTA_COPA', '=', $val['COUNTRY_KEY']],
                        ])->update([
                            'ESTA_NOES' => $val['STATE_NAME'],
                            'ESTA_USMO' => $idUser,
                            'ESTA_FEMO' => $nowStr,
                        ]);
                    }
                }
            break;
            case "Municipios": 
                $configArr = json_decode($form['config'], true);
                foreach($configArr as $val){
                    $city = DB::table('S002V01TMUNI')->where([
                        ['MUNI_NULI', '=', $form['linea']],
                        ['MUNI_COMU', '=', $val['CITY_KEY']],
                        ['MUNI_COES', '=', $val['STATE_KEY']],
                    ])->first();

                    if(is_null($val['CITY_KEY'])) continue;

                    if(is_null($city)){
                        DB::table('S002V01TMUNI')->insert([
                            'MUNI_NULI' => $form['linea'],
                            'MUNI_COMU' => $val['CITY_KEY'],
                            'MUNI_COES' => $val['STATE_KEY'],
                            'MUNI_NOMU' => $val['CITY_NAME'],
                            'MUNI_USRE' => $idUser,
                            'MUNI_FERE' => $nowStr,
                        ]);
                    }else{
                        DB::table('S002V01TMUNI')->where([
                            ['MUNI_NULI', '=', $form['linea']],
                            ['MUNI_COMU', '=', $val['CITY_KEY']],
                            ['MUNI_COES', '=', $val['STATE_KEY']],
                        ])->update([
                            'MUNI_NOMU' => $val['CITY_NAME'],
                            'MUNI_USMO' => $idUser,
                            'MUNI_FEMO' => $nowStr,
                        ]);
                    }
                }
            break;
            case "Localidades":
                $configArr = json_decode($form['config'], true);
                foreach($configArr as $val){
                    $town = DB::table('S002V01TLOCA')->where([
                        ['LOCA_NULI', '=', $form['linea']],
                        ['LOCA_COLO', '=', $val['TOWN_KEY']],
                        ['LOCA_COES', '=', $val['STATE_KEY']],
                    ])->first();

                    if(is_null($val['TOWN_KEY'])) continue;

                    if(is_null($town)){
                        DB::table('S002V01TLOCA')->insert([
                            'LOCA_NULI' => $form['linea'],
                            'LOCA_COLO' => $val['TOWN_KEY'],
                            'LOCA_COES' => $val['STATE_KEY'],
                            'LOCA_NOLO' => $val['TOWN_NAME'],
                            'LOCA_USRE' => $idUser,
                            'LOCA_FERE' => $nowStr,
                        ]);
                    }else{
                        DB::table('S002V01TLOCA')->where([
                            ['LOCA_NULI', '=', $form['linea']],
                            ['LOCA_COLO', '=', $val['TOWN_KEY']],
                            ['LOCA_COES', '=', $val['CITY_NAME']],
                        ])->update([
                            'LOCA_NOLO' => $val['CITY_NAME'],
                            'LOCA_USMO' => $idUser,
                            'LOCA_FEMO' => $nowStr,
                        ]);
                    }
                }
            break;
            case "Colonias":
                $configArr = json_decode($form['config'], true);
                foreach($configArr as $val){
                    $town = DB::table('S002V01TCOLO')->where([
                        ['COLO_NULI', '=', $form['linea']],
                        ['COLO_COCO', '=', $val['SETTING_KEY']],
                        ['COLO_COPO', '=', $val['CODIGO_POSTAL']],
                    ])->first();

                    if(is_null($val['SETTING_KEY'])) continue;

                    if(is_null($town)){
                        DB::table('S002V01TCOLO')->insert([
                            'COLO_NULI' => $form['linea'],
                            'COLO_COCO' => $val['SETTING_KEY'],
                            'COLO_COPO' => $val['CODIGO_POSTAL'],
                            'COLO_NOCO' => $val['SETTING_NAME'],
                            'COLO_USRE' => $idUser,
                            'COLO_FERE' => $nowStr,
                        ]);
                    }else{
                        DB::table('S002V01TCOLO')->where([
                            ['COLO_NULI', '=', $form['linea']],
                            ['COLO_COCO', '=', $val['SETTING_KEY']],
                            ['COLO_COPO', '=', $val['CODIGO_POSTAL']],
                        ])->update([
                            'COLO_NOCO' => $val['SETTING_NAME'],
                            'COLO_USMO' => $idUser,
                            'COLO_FEMO' => $nowStr,
                        ]);
                    }
                }
            break;
            case "Monedas":
                $configArr = json_decode($form['config'], true);
                foreach($configArr as $val){
                    $camo = DB::table('S002V01TCAMO')->where([
                        ['CAMO_NULI', '=', $form['linea']],
                        ['CAMO_COMO', '=', $val['COIN']],
                        ['CAMO_DESC', '=', $val['DESCRIPTION']],
                    ])->first();

                    if(is_null($val['COIN'])) continue;

                    if(is_null($camo)){
                        DB::table('S002V01TCAMO')->insert([
                            'CAMO_COMO' => $val['COIN'],
                            'CAMO_NULI' => $form['linea'],
                            'CAMO_DESC' => $val['DESCRIPTION'],
                            'CAMO_USRE' => $idUser,
                            'CAMO_FERE' => $nowStr,
                            'CAMO_FEAR' => DB::raw('CURRENT_TIMESTAMP')
                        ]);
                    }else{
                        DB::table('S002V01TCAMO')->where([
                            ['CAMO_NULI', '=', $form['linea']],
                            ['CAMO_COMO', '=', $val['COIN']],
                        ])->update([
                            'CAMO_DESC' => $val['DESCRIPTION'],
                            'CAMO_USMO' => $idUser,
                            'CAMO_FEMO' => $nowStr,
                            'CAMO_FEAR' => DB::raw('CURRENT_TIMESTAMP')
                        ]);
                    }
                }
            break;
            case "Régimen fiscal":
                $configArr = json_decode($form['config'], true);
                foreach($configArr as $val){
                    $taxRegime = DB::table('S002V01TREFI')->where([
                        ['REFI_NULI', '=', $form['linea']],
                        ['REFI_CRFI', '=', $val['TAX_REGIME_KEY']],
                    ])->first();

                    if(is_null($val['TAX_REGIME_KEY'])) continue;

                    if(is_null($taxRegime)){
                        DB::table('S002V01TREFI')->insert([
                            'REFI_NULI' => $form['linea'],
                            'REFI_CRFI' => $val['TAX_REGIME_KEY'],
                            'REFI_DRFI' => $val['TAX_REGIME_DESC'],
                            'REFI_APFI' => $val['APPLY_PHISIC'],
                            'REFI_APMO' => $val['APPLY_MORAL'],
                            'REFI_FERE' => $nowStr,
                            'REFI_USRE' => $idUser,
                        ]);
                    }else{
                        DB::table('S002V01TREFI')->where([
                            ['REFI_NULI', '=', $form['linea']],
                            ['REFI_CRFI', '=', $val['TAX_REGIME_KEY']],
                        ])->update([
                            'REFI_DRFI' => $val['TAX_REGIME_DESC'],
                            'REFI_APFI' => $val['APPLY_PHISIC'],
                            'REFI_APMO' => $val['APPLY_MORAL'],
                            'REFI_USMO' => $idUser,
                            'REFI_FEMO' => $nowStr,
                        ]);
                    }
                }
            break;
        }

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);

        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M01ADSI',
            'S002V01F10AGCA',
            'S002V01P02NUCA',
            'Actualización',
            "El usuario $name (" . $usr->USUA_IDUS . ") actualizó el catálogo $form[catalogue_type].",
            $idUser,
            $nowStr,
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeresponse(false, "EXITO");
    }

    public function getZipCodes($idUser, $line){
        ini_set("display_errors", 1);
        ini_set("memory_limit", "-1");
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la solicitud no está encriptado correctamente', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $line],
            ['USUA_IDUS', '=', $idUser],
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $catalogue = DB::table('S002V01TCATA')->where([
            ['CATA_TICA', '=', 'Códigos postales'],
            ['CATA_ESTA', '=', 'Activo'],
            ['CATA_NULI', '=', $line],
        ])->orderBy('CATA_VERS', 'desc')->first();

        if(is_null($catalogue)){
            return $this->responseController->makeResponse(true, 'Aún no se ha registrado ningún catálogo de códigos postales', [], 404);
        }

        $filesArr = json_decode($catalogue->CATA_ARRE, true);
        $fileDec = $this->encryptionController->decrypt($filesArr[0]);
        if(!$fileDec){
            return $this->responseController->makeResponse(true, 'El ID catálogo vigente de códigos postales no fue encriptado correctamente.', [], 400);
        }

        $fileArr = explode("=", $fileDec);
        $idArr = explode("-", $fileArr[0]);
        $file = DB::table('S002V01TAFAL')->where([
            ['AFAL_NULI', '=', $line],
            ['AFAL_COMO', '=', $idArr[1]],
            ['AFAL_CLDO', '=', $idArr[2]],
            ['AFAL_FECR', '=', $idArr[3]],
            ['AFAL_NUSE', '=', $idArr[4]],
            ['AFAL_NUVE', '=', $fileArr[1]],
        ])->first();

        if(is_null($file)){
            return $this->responseController->makeResponse(true, 'El archivo asociado al catálogo vigente de códigos postales no está registrado.', [], 404);
        }else if($file->AFAL_ESTA == 'Eliminado'){
            return $this->responseController->makeResponse(true, 'El archivo asociado al catálogo vigente de códigos postales está eliminado.', [], 404);
        }else if(!file_exists($file->AFAL_UBIC)){
            return $this->responseController->makeResponse(true, 'El archivo asociado al catálogo vigente de códigos postales no existe en el almacenamiento final.', [], 404);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $spreadsheet = IOFactory::load($file->AFAL_UBIC);
        $sheetNames = $spreadsheet->getSheetNames();
        $zipCodes = [];
        foreach($sheetNames as $name){
            if(!str_contains($name, 'c_CodigoPostal')){
                return $this->responseController->makeResponse(true, "La hoja $name no pertenece al catálogo de códigos postales.", [], 400);
            }

            $worksheet = $spreadsheet->getSheetByName($name);
            $fileValidatorCell = $worksheet->getCell('A6')->getValue();

            if($fileValidatorCell != 'c_CodigoPostal'){
                return $this->responseController->makeResponse(true, 'El archivo asociado no tiene un formato válido.', [], 400);
            }

            $maxRow = $worksheet->getHighestRow();
            for($row = 8; $row <= $maxRow; $row++){
                $zipCode = $worksheet->getCell("A$row")->getValue();
                if($zipCode == null) continue;

                $state = $worksheet->getCell("B$row")->getValue();
                $city = $worksheet->getCell("C$row")->getValue();
                $town = $worksheet->getCell("D$row")->getValue();

                if(is_null($state)) continue;
                
                $endDateNum = $worksheet->getCell("G$row")->getValue();
                if(is_null($endDateNum)){
                    $zipCodes[] = [
                        'CODIGO_POSTAL' => $zipCode,
                        'ESTADO' => $state,
                        'MUNICIPIO' => $city,
                        'LOCALIDAD' => $town,
                    ];
                }else{
                    $endDateStr = Date::excelToTimestamp($endDateNum);
                    $endDateObj = Carbon::createFromTimestamp($endDateStr);
                    if($endDateObj->gte($now)){
                        $zipCodes[] = [
                            'CODIGO_POSTAL' => $zipCode,
                            'ESTADO' => $state,
                            'MUNICIPIO' => $city,
                            'LOCALIDAD' => $town,
                        ];
                    }
                }
            }
        }

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F10AGCA',
            'S002V01P01HICA',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó el contenido del catálogo de Códigos postales.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $zipCodes);
    }

    public function getZipCodesTotal($idUser, $line) {
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la solicitud no está encriptado correctamente', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $line],
            ['USUA_IDUS', '=', $idUser],
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $totalZipCodes = DB::table('S002V01TCOPO')->select(DB::raw('count(*) as total_zip_codes'))->where(
            'COPO_NULI', '=', $line
        )->first();

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F10AGCA',
            'S002V01P01HICA',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó el total de Códigos postales.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', ["TOTAL_ZIP_CODES" => $totalZipCodes->total_zip_codes]);
    }

    public function getZipCodesPage($limit, $offset, $idUser, $line) {
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la solicitud no está encriptado correctamente', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $line],
            ['USUA_IDUS', '=', $idUser],
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $limit = intval($limit);
        $offset = intval($offset);

        $zipCodesPaginated = DB::table('S002V01TCOPO')->select([
            'COPO_COPO AS CODIGO_POSTAL',
            'COPO_COES AS ESTADO',
            'COPO_COMU AS MUNICIPIO',
            'COPO_COLO AS LOCALIDAD',
        ])->where([
            ['COPO_NULI', '=', $line],
            ['COPO_ESTA', '=', 'Activo']
        ])->offset($offset)->limit($limit)->orderBy('COPO_COES', 'ASC')->orderBy('COPO_COPO', 'ASC')->get()->all();

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F10AGCA',
            'S002V01P01HICA',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó $limit registros desde el $offset del catálogo de Códigos postales.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $zipCodesPaginated);
    }

    public function getAddressByZipCode($zipCode, $idUser, $line) {
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la solicitud no está encriptado correctamente', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $line],
            ['USUA_IDUS', '=', $idUser],
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $zipCode = $this->encryptionController->decrypt($zipCode);
        if(!$zipCode){
            return $this->responseController->makeResponse(true, 'El código postal enviado no está encriptado correctamente', [], 400);
        }

        $code = DB::table('S002V01TCOPO')->where([
            ['COPO_NULI', '=', $line],
            ['COPO_COPO', '=', $zipCode]
        ])->first();

        if(is_null($code)){
            return $this->responseController->makeResponse(true, "El código postal $zipCode no está registrado.", [], 404);
        }else if($code->COPO_ESTA == 'Eliminado'){
            return $this->responseController->makeResponse(true, "El código postal $zipCode está eliminado.", [], 404);
        }

        $address = DB::table('S002V01TCOPO')->select([
            'COPO_COPO AS CODIGO_POSTAL',
            'COPO_COES AS CODIGO_ESTADO',
            'ESTA_NOES AS NOMBRE_ESTADO',
            'COPO_COMU AS CODIGO_MUNICIPIO',
            'MUNI_NOMU AS NOMBRE_MUNICIPIO',
            'COPO_COLO AS CODIGO_LOCALIDAD',
            'LOCA_NOLO AS NOMBRE_LOCALIDAD'
        ])->where([
            ['COPO_NULI', '=', $line],
            ['COPO_COPO', '=', $zipCode]
        ])->join('S002V01TESTA', 'COPO_COES', '=', 'ESTA_COES')
        ->leftJoin('S002V01TMUNI', function(JoinClause $join) {
            $join->on('COPO_COMU', '=', 'MUNI_COMU')
            ->on('COPO_COES', '=', 'MUNI_COES');
        })->leftJoin('S002V01TLOCA', function(JoinClause $join) {
            $join->on('COPO_COLO', '=', 'LOCA_COLO')
            ->on('COPO_COES', '=', 'LOCA_COES');
        })->first();

        $settings = DB::table('S002V01TCOLO')->select([
            'COLO_COCO AS CODIGO_COLONIA',
            'COLO_NOCO AS NOMBRE_COLONIA'
        ])->where([
            ['COLO_NULI', '=', $line],
            ['COLO_COPO', '=', $zipCode],
        ])->get()->all();

        $address->COLONIAS = $settings;

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);

        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F10AGCA',
            'S002V01P02NUCA',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó la dirección del código postal $zipCode.",
            $idUser,
            $nowStr,
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeresponse(false, "EXITO", $address);
    }

    public function getStatesList($idUser, $line) {
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la solicitud no está encriptado correctamente', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $line],
            ['USUA_IDUS', '=', $idUser],
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $catalogue = DB::table('S002V01TCATA')->where([
            ['CATA_TICA', '=', 'Estados'],
            ['CATA_ESTA', '=', 'Activo'],
            ['CATA_NULI', '=', $line],
        ])->orderBy('CATA_VERS', 'desc')->first();

        if(is_null($catalogue)){
            return $this->responseController->makeResponse(true, 'Aún no se ha registrado ningún catálogo de Estados', [], 404);
        }

        $filesArr = json_decode($catalogue->CATA_ARRE, true);
        $fileDec = $this->encryptionController->decrypt($filesArr[0]);
        if(!$fileDec){
            return $this->responseController->makeResponse(true, 'El ID catálogo vigente de países no fue encriptado correctamente.', [], 400);
        }

        $fileArr = explode("=", $fileDec);
        $idArr = explode("-", $fileArr[0]);
        $file = DB::table('S002V01TAFAL')->where([
            ['AFAL_NULI', '=', $line],
            ['AFAL_COMO', '=', $idArr[1]],
            ['AFAL_CLDO', '=', $idArr[2]],
            ['AFAL_FECR', '=', $idArr[3]],
            ['AFAL_NUSE', '=', $idArr[4]],
            ['AFAL_NUVE', '=', $fileArr[1]],
        ])->first();

        if(is_null($file)){
            return $this->responseController->makeResponse(true, 'El archivo asociado al catálogo vigente de países no está registrado.', [], 404);
        }else if($file->AFAL_ESTA == 'Eliminado'){
            return $this->responseController->makeResponse(true, 'El archivo asociado al catálogo vigente de países está eliminado.', [], 404);
        }else if(!file_exists($file->AFAL_UBIC)){
            return $this->responseController->makeResponse(true, 'El archivo asociado al catálogo vigente de países no existe en el almacenamiento final.', [], 404);
        }

        $spreadsheet = IOFactory::load($file->AFAL_UBIC);
        $sheetNames = $spreadsheet->getSheetNames();
        if(!in_array('c_Estado', $sheetNames)){
            return $this->responseController->makeResponse(true, 'El archivo asociado no contiene la hoja c_Estado.', [], 400);
        }

        $worksheet = $spreadsheet->getActiveSheet();
        $fileValidatorCell = $worksheet->getCell('A5')->getValue();

        if($fileValidatorCell != 'c_Estado'){
            return $this->responseController->makeResponse(true, 'El archivo asociado no tiene un formato válido.', [], 400);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $states = [];
        $maxRow = $worksheet->getHighestRow();
        for($row = 6; $row <= $maxRow; $row++){
            $stateKey = $worksheet->getCell("A$row")->getValue();
            $countryKey = $worksheet->getCell("B$row")->getValue();
            $stateName = $worksheet->getCell("C$row")->getValue();

            $endDateNum = $worksheet->getCell("E$row")->getValue();
            if(is_null($endDateNum)){
                $states[] = [
                    'STATE_KEY' => $stateKey,
                    'COUNTRY_KEY' => $countryKey,
                    'STATE_NAME' => $stateName,
                ];
            }else{
                $endDateStr = Date::excelToTimestamp($endDateNum);
                $endDateObj = Carbon::createFromTimestamp($endDateStr);
                if($endDateObj->gte($now)){
                    $states[] = [
                        'STATE_KEY' => $stateKey,
                        'COUNTRY_KEY' => $countryKey,
                        'STATE_NAME' => $stateName,
                    ];
                }
            }
        }

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F10AGCA',
            'S002V01P01HICA',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó el contenido del catálogo de Estados.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $states);
    }

    public function getCitiesList($idUser, $line) {
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la solicitud no está encriptado correctamente', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $line],
            ['USUA_IDUS', '=', $idUser],
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $catalogue = DB::table('S002V01TCATA')->where([
            ['CATA_TICA', '=', 'Municipios'],
            ['CATA_ESTA', '=', 'Activo'],
            ['CATA_NULI', '=', $line],
        ])->orderBy('CATA_VERS', 'desc')->first();

        if(is_null($catalogue)){
            return $this->responseController->makeResponse(true, 'Aún no se ha registrado ningún catálogo de Estados', [], 404);
        }

        $filesArr = json_decode($catalogue->CATA_ARRE, true);
        $fileDec = $this->encryptionController->decrypt($filesArr[0]);
        if(!$fileDec){
            return $this->responseController->makeResponse(true, 'El ID catálogo vigente de países no fue encriptado correctamente.', [], 400);
        }

        $fileArr = explode("=", $fileDec);
        $idArr = explode("-", $fileArr[0]);
        $file = DB::table('S002V01TAFAL')->where([
            ['AFAL_NULI', '=', $line],
            ['AFAL_COMO', '=', $idArr[1]],
            ['AFAL_CLDO', '=', $idArr[2]],
            ['AFAL_FECR', '=', $idArr[3]],
            ['AFAL_NUSE', '=', $idArr[4]],
            ['AFAL_NUVE', '=', $fileArr[1]],
        ])->first();

        if(is_null($file)){
            return $this->responseController->makeResponse(true, 'El archivo asociado al catálogo vigente de países no está registrado.', [], 404);
        }else if($file->AFAL_ESTA == 'Eliminado'){
            return $this->responseController->makeResponse(true, 'El archivo asociado al catálogo vigente de países está eliminado.', [], 404);
        }else if(!file_exists($file->AFAL_UBIC)){
            return $this->responseController->makeResponse(true, 'El archivo asociado al catálogo vigente de países no existe en el almacenamiento final.', [], 404);
        }

        $spreadsheet = IOFactory::load($file->AFAL_UBIC);
        $sheetNames = $spreadsheet->getSheetNames();
        if(!in_array('c_Municipio', $sheetNames)){
            return $this->responseController->makeResponse(true, 'El archivo asociado no contiene la hoja c_Municipio.', [], 400);
        }

        $worksheet = $spreadsheet->getActiveSheet();
        $fileValidatorCell = $worksheet->getCell('A5')->getValue();

        if($fileValidatorCell != 'c_Municipio'){
            return $this->responseController->makeResponse(true, 'El archivo asociado no tiene un formato válido.', [], 400);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $cities = [];
        $maxRow = $worksheet->getHighestRow();
        for($row = 6; $row <= $maxRow; $row++){
            $cityKey = $worksheet->getCell("A$row")->getValue();
            $stateKey = $worksheet->getCell("B$row")->getValue();
            $cityName = $worksheet->getCell("C$row")->getValue();

            $endDateNum = $worksheet->getCell("E$row")->getValue();
            if(is_null($endDateNum)){
                $cities[] = [
                    'CITY_KEY' => $cityKey,
                    'STATE_KEY' => $stateKey,
                    'CITY_NAME' => $cityName,
                ];
            }else{
                $endDateStr = Date::excelToTimestamp($endDateNum);
                $endDateObj = Carbon::createFromTimestamp($endDateStr);
                if($endDateObj->gte($now)){
                    $cities[] = [
                        'CITY_KEY' => $cityKey,
                        'STATE_KEY' => $stateKey,
                        'CITY_NAME' => $cityName,
                    ];
                }
            }
        }

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F10AGCA',
            'S002V01P01HICA',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó el contenido del catálogo de Municipios.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $cities);
    }

    public function getTownsList($idUser, $line) {
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la solicitud no está encriptado correctamente', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $line],
            ['USUA_IDUS', '=', $idUser],
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $catalogue = DB::table('S002V01TCATA')->where([
            ['CATA_TICA', '=', 'Localidades'],
            ['CATA_ESTA', '=', 'Activo'],
            ['CATA_NULI', '=', $line],
        ])->orderBy('CATA_VERS', 'desc')->first();

        if(is_null($catalogue)){
            return $this->responseController->makeResponse(true, 'Aún no se ha registrado ningún catálogo de Estados', [], 404);
        }

        $filesArr = json_decode($catalogue->CATA_ARRE, true);
        $fileDec = $this->encryptionController->decrypt($filesArr[0]);
        if(!$fileDec){
            return $this->responseController->makeResponse(true, 'El ID catálogo vigente de países no fue encriptado correctamente.', [], 400);
        }

        $fileArr = explode("=", $fileDec);
        $idArr = explode("-", $fileArr[0]);
        $file = DB::table('S002V01TAFAL')->where([
            ['AFAL_NULI', '=', $line],
            ['AFAL_COMO', '=', $idArr[1]],
            ['AFAL_CLDO', '=', $idArr[2]],
            ['AFAL_FECR', '=', $idArr[3]],
            ['AFAL_NUSE', '=', $idArr[4]],
            ['AFAL_NUVE', '=', $fileArr[1]],
        ])->first();

        if(is_null($file)){
            return $this->responseController->makeResponse(true, 'El archivo asociado al catálogo vigente de países no está registrado.', [], 404);
        }else if($file->AFAL_ESTA == 'Eliminado'){
            return $this->responseController->makeResponse(true, 'El archivo asociado al catálogo vigente de países está eliminado.', [], 404);
        }else if(!file_exists($file->AFAL_UBIC)){
            return $this->responseController->makeResponse(true, 'El archivo asociado al catálogo vigente de países no existe en el almacenamiento final.', [], 404);
        }

        $spreadsheet = IOFactory::load($file->AFAL_UBIC);
        $sheetNames = $spreadsheet->getSheetNames();
        if(!in_array('c_Localidad', $sheetNames)){
            return $this->responseController->makeResponse(true, 'El archivo asociado no contiene la hoja c_Localidad.', [], 400);
        }

        $worksheet = $spreadsheet->getActiveSheet();
        $fileValidatorCell = $worksheet->getCell('A5')->getValue();

        if($fileValidatorCell != 'c_Localidad'){
            return $this->responseController->makeResponse(true, 'El archivo asociado no tiene un formato válido.', [], 400);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $towns = [];
        $maxRow = $worksheet->getHighestRow();
        for($row = 6; $row <= $maxRow; $row++){
            $townKey = $worksheet->getCell("A$row")->getValue();
            $stateKey = $worksheet->getCell("B$row")->getValue();
            $townName = $worksheet->getCell("C$row")->getValue();

            $endDateNum = $worksheet->getCell("E$row")->getValue();
            if(is_null($endDateNum)){
                $towns[] = [
                    'TOWN_KEY' => $townKey,
                    'STATE_KEY' => $stateKey,
                    'TOWN_NAME' => $townName,
                ];
            }else{
                $endDateStr = Date::excelToTimestamp($endDateNum);
                $endDateObj = Carbon::createFromTimestamp($endDateStr);
                if($endDateObj->gte($now)){
                    $towns[] = [
                        'TOWN_KEY' => $townKey,
                        'STATE_KEY' => $stateKey,
                        'TOWN_NAME' => $townName,
                    ];
                }
            }
        }
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F10AGCA',
            'S002V01P01HICA',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó el contenido del catálogo de Localidades.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $towns);
    }

    public function getCoinsList($idUser, $line) {

        $arrResponseCheckUser = $this->resourcesController->checkUserEnc($idUser, $line);
        if ($arrResponseCheckUser['error']) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, $arrResponseCheckUser['msg'], [], 401);
        }
        $user = $arrResponseCheckUser['response'];

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $line],
            ['USUA_IDUS', '=', $user],
        ])->first();

        try {
            $catalogue = DB::table('S002V01TCATA')->where([
                ['CATA_TICA', '=', 'Monedas'],
                ['CATA_ESTA', '=', 'Activo'],
                ['CATA_NULI', '=', $line],
            ])->orderBy('CATA_VERS', 'desc')->first();
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(true, 'Ocurrió un error al obtener los catálogos de monedas.', $th->getMessage(), 500);
        }
        
        if (is_null($catalogue)) {
            return $this->responseController->makeResponse(true, 'No se pudo obtener los catálogos de monedas.', [], 500);
        }

        
        $filesArr = json_decode($catalogue->CATA_ARRE, true);
        $fileDec = $this->encryptionController->decrypt($filesArr[0]);
        if(!$fileDec){
            return $this->responseController->makeResponse(true, 'El ID catálogo vigente de países no fue encriptado correctamente.', [], 400);
        }

        $fileArr = explode("=", $fileDec);
        $idArr = explode("-", $fileArr[0]);
        $file = DB::table('S002V01TAFAL')->where([
            ['AFAL_NULI', '=', $line],
            ['AFAL_COMO', '=', $idArr[1]],
            ['AFAL_CLDO', '=', $idArr[2]],
            ['AFAL_FECR', '=', $idArr[3]],
            ['AFAL_NUSE', '=', $idArr[4]],
            ['AFAL_NUVE', '=', $fileArr[1]],
        ])->first();

        if(is_null($file)){
            return $this->responseController->makeResponse(true, 'El archivo asociado al catálogo vigente de monedas no está registrado.', [], 404);
        }else if($file->AFAL_ESTA == 'Eliminado'){
            return $this->responseController->makeResponse(true, 'El archivo asociado al catálogo vigente de monedas está eliminado.', [], 404);
        }else if(!file_exists($file->AFAL_UBIC)){
            return $this->responseController->makeResponse(true, 'El archivo asociado al catálogo vigente de monedas no existe en el almacenamiento final.', [], 404);
        }

        $spreadsheet = IOFactory::load($file->AFAL_UBIC);
        $sheetNames = $spreadsheet->getSheetNames();
        if(!in_array('c_Moneda', $sheetNames)){
            return $this->responseController->makeResponse(true, 'El archivo asociado no contiene la hoja c_Moneda.', [], 400);
        }

        $worksheet = $spreadsheet->getActiveSheet();
        $fileValidatorCell = $worksheet->getCell('A5')->getValue();

        if($fileValidatorCell != 'c_Moneda'){
            return $this->responseController->makeResponse(true, 'El archivo asociado no tiene un formato válido.', [], 400);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $towns = [];
        $maxRow = $worksheet->getHighestRow();
        for($row = 6; $row <= $maxRow; $row++){
            $coinKey = $worksheet->getCell("A$row")->getValue();
            $descriptionKey = $worksheet->getCell("B$row")->getValue();

            $endDateNum = $worksheet->getCell("F$row")->getValue();
            if(is_null($endDateNum)){
                $towns[] = [
                    'COIN' => $coinKey,
                    'DESCRIPTION' => $descriptionKey,
                ];
            }else{
                $endDateStr = Date::excelToTimestamp($endDateNum);
                $endDateObj = Carbon::createFromTimestamp($endDateStr);
                if($endDateObj->gte($now)){
                    $towns[] = [
                        'COIN' => $coinKey,
                        'DESCRIPTION' => $descriptionKey,
                    ];
                }
            }
        }
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F10AGCA',
            'S002V01P01HICA',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó el contenido del catálogo de Monedas.",
            $user,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $user, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $towns);

    }

    public function getSettingsList($idUser, $line) {
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la solicitud no está encriptado correctamente', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $line],
            ['USUA_IDUS', '=', $idUser],
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $catalogue = DB::table('S002V01TCATA')->where([
            ['CATA_TICA', '=', 'Colonias'],
            ['CATA_ESTA', '=', 'Activo'],
            ['CATA_NULI', '=', $line],
        ])->orderBy('CATA_VERS', 'desc')->first();

        if(is_null($catalogue)){
            return $this->responseController->makeResponse(true, 'Aún no se ha registrado ningún catálogo de Estados', [], 404);
        }

        $filesArr = json_decode($catalogue->CATA_ARRE, true);
        $fileDec = $this->encryptionController->decrypt($filesArr[0]);
        if(!$fileDec){
            return $this->responseController->makeResponse(true, 'El ID catálogo vigente de países no fue encriptado correctamente.', [], 400);
        }

        $fileArr = explode("=", $fileDec);
        $idArr = explode("-", $fileArr[0]);
        $file = DB::table('S002V01TAFAL')->where([
            ['AFAL_NULI', '=', $line],
            ['AFAL_COMO', '=', $idArr[1]],
            ['AFAL_CLDO', '=', $idArr[2]],
            ['AFAL_FECR', '=', $idArr[3]],
            ['AFAL_NUSE', '=', $idArr[4]],
            ['AFAL_NUVE', '=', $fileArr[1]],
        ])->first();

        if(is_null($file)){
            return $this->responseController->makeResponse(true, 'El archivo asociado al catálogo vigente de países no está registrado.', [], 404);
        }else if($file->AFAL_ESTA == 'Eliminado'){
            return $this->responseController->makeResponse(true, 'El archivo asociado al catálogo vigente de países está eliminado.', [], 404);
        }else if(!file_exists($file->AFAL_UBIC)){
            return $this->responseController->makeResponse(true, 'El archivo asociado al catálogo vigente de países no existe en el almacenamiento final.', [], 404);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $spreadsheet = IOFactory::load($file->AFAL_UBIC);
        $sheetNames = $spreadsheet->getSheetNames();
        $settings = [];
        foreach($sheetNames as $name){
            if(!str_contains($name, 'c_Colonia')){
                return $this->responseController->makeResponse(true, "La hoja $name no pertenece al catálogo de colonias.", [], 400);
            }

            $worksheet = $spreadsheet->getSheetByName($name);
            $fileValidatorCell = $worksheet->getCell('A5')->getValue();

            if($fileValidatorCell != 'c_Colonia'){
                return $this->responseController->makeResponse(true, 'El archivo asociado no tiene un formato válido.', [], 400);
            }

            $maxRow = $worksheet->getHighestRow();
            for($row = 6; $row <= $maxRow; $row++){
                $settingKey = $worksheet->getCell("A$row")->getValue();
                if($settingKey == null) continue;

                $zipCode = $worksheet->getCell("B$row")->getValue();
                $settingName = $worksheet->getCell("C$row")->getValue();

                if(is_null($zipCode)) continue;

                $settings[] = [
                    'SETTING_KEY' => $settingKey,
                    'CODIGO_POSTAL' => $zipCode,
                    'SETTING_NAME' => $settingName,
                ];
            }
        }

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F10AGCA',
            'S002V01P01HICA',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó el contenido del catálogo de Colonias.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $settings);
    }

    public function getTaxRegimeList($idUser, $line) {
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la solicitud no está encriptado correctamente', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $line],
            ['USUA_IDUS', '=', $idUser],
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $catalogue = DB::table('S002V01TCATA')->where([
            ['CATA_TICA', '=', 'Régimen fiscal'],
            ['CATA_ESTA', '=', 'Activo'],
            ['CATA_NULI', '=', $line],
        ])->orderBy('CATA_VERS', 'desc')->first();

        if(is_null($catalogue)){
            return $this->responseController->makeResponse(true, 'Aún no se ha registrado ningún catálogo de Estados', [], 404);
        }

        $filesArr = json_decode($catalogue->CATA_ARRE, true);
        $fileDec = $this->encryptionController->decrypt($filesArr[0]);
        if(!$fileDec){
            return $this->responseController->makeResponse(true, 'El ID catálogo vigente de países no fue encriptado correctamente.', [], 400);
        }

        $fileArr = explode("=", $fileDec);
        $idArr = explode("-", $fileArr[0]);
        $file = DB::table('S002V01TAFAL')->where([
            ['AFAL_NULI', '=', $line],
            ['AFAL_COMO', '=', $idArr[1]],
            ['AFAL_CLDO', '=', $idArr[2]],
            ['AFAL_FECR', '=', $idArr[3]],
            ['AFAL_NUSE', '=', $idArr[4]],
            ['AFAL_NUVE', '=', $fileArr[1]],
        ])->first();

        if(is_null($file)){
            return $this->responseController->makeResponse(true, 'El archivo asociado al catálogo vigente de países no está registrado.', [], 404);
        }else if($file->AFAL_ESTA == 'Eliminado'){
            return $this->responseController->makeResponse(true, 'El archivo asociado al catálogo vigente de países está eliminado.', [], 404);
        }else if(!file_exists($file->AFAL_UBIC)){
            return $this->responseController->makeResponse(true, 'El archivo asociado al catálogo vigente de países no existe en el almacenamiento final.', [], 404);
        }

        $spreadsheet = IOFactory::load($file->AFAL_UBIC);
        $sheetNames = $spreadsheet->getSheetNames();
        if(!in_array('c_RegimenFiscal', $sheetNames)){
            return $this->responseController->makeResponse(true, 'El archivo asociado no contiene la hoja c_RegimenFiscal.', [], 400);
        }

        $worksheet = $spreadsheet->getActiveSheet();
        $fileValidatorCell = $worksheet->getCell('A6')->getValue();

        if($fileValidatorCell != 'c_RegimenFiscal'){
            return $this->responseController->makeResponse(true, 'El archivo asociado no tiene un formato válido.', [], 400);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $taxRegime = [];
        $maxRow = $worksheet->getHighestRow();
        for($row = 7; $row <= $maxRow; $row++){
            $taxRegimeKey = $worksheet->getCell("A$row")->getValue();
            $taxRegimeDesc = $worksheet->getCell("B$row")->getValue();
            $applyPhisic = $worksheet->getCell("C$row")->getValue();
            $applyMoral = $worksheet->getCell("D$row")->getValue();

            $endDateNum = $worksheet->getCell("F$row")->getValue();
            if(is_null($endDateNum)){
                $taxRegime[] = [
                    'TAX_REGIME_KEY' => $taxRegimeKey,
                    'TAX_REGIME_DESC' => $taxRegimeDesc,
                    'APPLY_PHISIC' => $applyPhisic,
                    'APPLY_MORAL' => $applyMoral,
                ];
            }else{
                $endDateStr = Date::excelToTimestamp($endDateNum);
                $endDateObj = Carbon::createFromTimestamp($endDateStr);
                if($endDateObj->gte($now)){
                    $taxRegime[] = [
                        'TAX_REGIME_KEY' => $taxRegimeKey,
                        'TAX_REGIME_DESC' => $taxRegimeDesc,
                        'APPLY_PHISIC' => $applyPhisic,
                        'APPLY_MORAL' => $applyMoral,
                    ];
                }
            }
        }
        
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F10AGCA',
            'S002V01P01HICA',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó el contenido del catálogo de Régimen fiscal.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $taxRegime);
    }

    public function getStatesByCountryCode($countryCode, $idUser, $line) {
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la solicitud no está encriptado correctamente', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $line],
            ['USUA_IDUS', '=', $idUser],
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $countryCode = $this->encryptionController->decrypt($countryCode);
        if(!$countryCode){
            return $this->responseController->makeResponse(true, 'El código del país seleccionado no está encriptado correctamente', [], 400);
        }

        $country = DB::table('S002V01TPAIS')->where([
            ['PAIS_NULI', '=', $line],
            ['PAIS_IDPA', '=', $countryCode]
        ])->first();

        if(is_null($country)){
            return $this->responseController->makeResponse(true, 'El país seleccionado no está registrado', [], 404);
        }

        $relatedStates = DB::table('S002V01TESTA')->select([
            'ESTA_COES AS CODIGOESTADO',
            'ESTA_NOES AS NOMBREESTADO'
        ])->where([
            ['ESTA_COPA', '=', $countryCode],
            ['ESTA_NULI', '=', $line],
        ])->orderBy('ESTA_COES', 'asc')->get()->all();

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F10AGCA',
            'S002V01P01HICA',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó los estados relacionados al país " . $country->PAIS_NOMB . " ($countryCode).",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $relatedStates);
    }

    public function getCitiesByStateCode($stateCode, $idUser, $line) {
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la solicitud no está encriptado correctamente', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $line],
            ['USUA_IDUS', '=', $idUser],
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $stateCode = $this->encryptionController->decrypt($stateCode);
        if(!$stateCode){
            return $this->responseController->makeResponse(true, 'El código del estado seleccionado no está encriptado correctamente', [], 400);
        }

        $state = DB::table('S002V01TESTA')->where([
            ['ESTA_NULI', '=', $line],
            ['ESTA_COES', '=', $stateCode]
        ])->first();

        if(is_null($state)){
            return $this->responseController->makeResponse(true, 'El estado seleccionado no está registrado', [], 404);
        }

        $relatedCities = DB::table('S002V01TMUNI')->select([
            'MUNI_COMU AS CODIGOMUNICIPIO',
            'MUNI_NOMU AS NOMBREMUNICIPIO'
        ])->where([
            ['MUNI_COES', '=', $stateCode],
            ['MUNI_NULI', '=', $line],
        ])->orderBy('MUNI_COMU', 'asc')->get()->all();

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F10AGCA',
            'S002V01P01HICA',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó los municipios relacionados al estado " . $state->ESTA_NOES . " ($stateCode).",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $relatedCities);
    }

    public function getTowns($stateCode, $cityCode, $idUser, $line) {
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la solicitud no está encriptado correctamente', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $line],
            ['USUA_IDUS', '=', $idUser],
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $stateCode = $this->encryptionController->decrypt($stateCode);
        if(!$stateCode){
            return $this->responseController->makeResponse(true, 'El código del estado seleccionado no está encriptado correctamente', [], 400);
        }

        $state = DB::table('S002V01TESTA')->where([
            ['ESTA_NULI', '=', $line],
            ['ESTA_COES', '=', $stateCode]
        ])->first();

        if(is_null($state)){
            return $this->responseController->makeResponse(true, 'El estado seleccionado no está registrado', [], 404);
        }

        $cityCode = $this->encryptionController->decrypt($cityCode);
        if(!$cityCode){
            return $this->responseController->makeResponse(true, 'El código del municipio seleccionado no está encriptado correctamente', [], 400);
        }

        $city = DB::table('S002V01TMUNI')->where([
            ['MUNI_NULI', '=', $line],
            ['MUNI_COMU', '=', $cityCode],
            ['MUNI_COES', '=', $stateCode],
        ])->first();

        if(is_null($city)){
            return $this->responseController->makeResponse(true, 'El municipio seleccionado no está registrado', [], 404);
        }

        $relatedZipCodes = DB::table('S002V01TCOPO')->select([
            DB::raw('DISTINCT(COPO_COLO) AS CODIGOLOCALIDAD'),
            'LOCA_NOLO AS NOMBRELOCALIDAD'
        ])->where([
            ['COPO_NULI', '=', $line],
            ['COPO_COES', '=', $stateCode],
            ['COPO_COMU', '=', $cityCode],
        ])->leftJoin('S002V01TLOCA', function(JoinClause $join) {
            $join->on('LOCA_COLO', '=', 'COPO_COLO')
            ->on('LOCA_COES', '=', 'COPO_COES');
        })->get()->all();

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F10AGCA',
            'S002V01P01HICA',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó las localidades relacionadas al estado " . $state->ESTA_NOES . " ($stateCode).",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $relatedZipCodes);
    }

    public function getCitySettings($stateCode, $cityCode, $townCode, $idUser, $line) {
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la solicitud no está encriptado correctamente', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $line],
            ['USUA_IDUS', '=', $idUser],
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $stateCode = $this->encryptionController->decrypt($stateCode);
        if(!$stateCode){
            return $this->responseController->makeResponse(true, 'El código del estado seleccionado no está encriptado correctamente', [], 400);
        }

        $state = DB::table('S002V01TESTA')->where([
            ['ESTA_NULI', '=', $line],
            ['ESTA_COES', '=', $stateCode]
        ])->first();

        if(is_null($state)){
            return $this->responseController->makeResponse(true, 'El estado seleccionado no está registrado', [], 404);
        }

        $cityCode = $this->encryptionController->decrypt($cityCode);
        if(!$cityCode){
            return $this->responseController->makeResponse(true, 'El código del municipio seleccionado no está encriptado correctamente', [], 400);
        }

        $city = DB::table('S002V01TMUNI')->where([
            ['MUNI_NULI', '=', $line],
            ['MUNI_COMU', '=', $cityCode],
            ['MUNI_COES', '=', $stateCode],
        ])->first();

        if(is_null($city)){
            return $this->responseController->makeResponse(true, 'El municipio seleccionado no está registrado', [], 404);
        }

        $townCode = $this->encryptionController->decrypt($townCode);
        if(!$townCode){
            return $this->responseController->makeResponse(true, 'El código de la localidad seleccionada no está encriptado correctamente', [], 400);
        }

        $townCode = $townCode == '-' ? null : $townCode;
        if(!is_null($townCode)){
            $town = DB::table('S002V01TLOCA')->where([
                ['LOCA_NULI', '=', $line],
                ['LOCA_COLO', '=', $townCode],
                ['LOCA_COES', '=', $stateCode]
            ])->first();

            if(is_null($town)){
                return $this->responseController->makeResponse(true, 'La localidad seleccionada no está registrada', [], 404);
            }
        }

        $settings = DB::table('S002V01TCOPO')->select([
            'COLO_COCO AS CODIGOCOLONIA',
            'COPO_COPO AS CODIGOPOSTAL',
            'COLO_NOCO AS NOMBRECOLONIA',
        ])->where([
            ['COPO_NULI', '=', $line],
            ['COPO_COES', '=', $stateCode],
            ['COPO_COMU', '=', $cityCode],
            ['COPO_COLO', '=', $townCode],
        ])->join('S002V01TCOLO', 'COLO_COPO', '=', 'COPO_COPO')->get()->all();

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F10AGCA',
            'S002V01P01HICA',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó las colonias relacionadas al estado " . $state->ESTA_NOES . " ($stateCode).",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $settings);
    }

    public function getGeneralCatalogueInfo($type, $headerRow, $idUser, $line) {
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la solicitud no está encriptado correctamente', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $line],
            ['USUA_IDUS', '=', $idUser],
        ])->first();

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }

        $catalogue = DB::table('S002V01TCATA')->where([
            ['CATA_TICA', '=', 'General'],
            ['CATA_ESTA', '=', 'Activo'],
            ['CATA_NULI', '=', $line],
        ])->orderBy('CATA_VERS', 'desc')->first();

        if(is_null($catalogue)){
            return $this->responseController->makeResponse(true, 'Aún no se ha registrado ningún catálogo de General', [], 404);
        }

        $filesArr = json_decode($catalogue->CATA_ARRE, true);
        $fileDec = $this->encryptionController->decrypt($filesArr[0]);
        if(!$fileDec){
            return $this->responseController->makeResponse(true, 'El ID catálogo vigente de países no fue encriptado correctamente.', [], 400);
        }

        $fileArr = explode("=", $fileDec);
        $idArr = explode("-", $fileArr[0]);
        $file = DB::table('S002V01TAFAL')->where([
            ['AFAL_NULI', '=', $line],
            ['AFAL_COMO', '=', $idArr[1]],
            ['AFAL_CLDO', '=', $idArr[2]],
            ['AFAL_FECR', '=', $idArr[3]],
            ['AFAL_NUSE', '=', $idArr[4]],
            ['AFAL_NUVE', '=', $fileArr[1]],
        ])->first();

        if(is_null($file)){
            return $this->responseController->makeResponse(true, 'El archivo asociado al catálogo general vigente no está registrado.', [], 404);
        }else if($file->AFAL_ESTA == 'Eliminado'){
            return $this->responseController->makeResponse(true, 'El archivo asociado al catálogo general vigente está eliminado.', [], 404);
        }else if(!file_exists($file->AFAL_UBIC)){
            return $this->responseController->makeResponse(true, 'El archivo asociado al catálogo general vigente no existe en el almacenamiento final.', [], 404);
        }

        ini_set("memory_limit", "-1");
        set_time_limit(0);

        $spreadsheet = IOFactory::load($file->AFAL_UBIC);
        $sheetNames = $spreadsheet->getSheetNames();
        $relatedSheets = [];

        foreach($sheetNames as $sheet){
            if(str_contains($sheet, $type)){
                $relatedSheets[] = $sheet;
            }
        }

        if(count($relatedSheets) == 0){
            return $this->responseController->makeResponse(true, "No se encontraron hojas relacionadas al tipo $type.", [], 404);
        }

        $responseArr = [];
        foreach($relatedSheets as $sheet){
            $worksheet = $spreadsheet->getSheetByName($sheet);
            $maxCol = $worksheet->getHighestColumn();
            $maxColInd = Coordinate::columnIndexFromString($maxCol);
            $headers = [];
            $startRow = intval($headerRow);

            if($startRow == 0){
                return $this->responseController->makeResponse(true, 'La fila del encabezado no tiene un formato válido.', [], 400);
            }

            for($col = 1; $col <= $maxColInd; $col++){
                $colStr = Coordinate::stringFromColumnIndex($col);
                $header = $worksheet->getCell($colStr . $startRow)->getValue();

                if(!is_null($header)){
                    $headers[$colStr] = $header;
                }else{
                    $headers[$colStr] = $colStr;
                }
            }

            $startRow++;
            $maxRow = $worksheet->getHighestRow();
            for($row = $startRow; $row <= $maxRow; $row++){
                $arrAux = [];
                for($col = 1; $col <= count($headers); $col++){
                    $colStr = Coordinate::stringFromColumnIndex($col);
                    $val = $worksheet->getCell($colStr . $row)->getCalculatedValue();
                    $header = $headers[$colStr];

                    $isDate = Date::isDateTime($worksheet->getCell($colStr . $row));
                    if($isDate){
                        $val = Date::excelToTimestamp($val);
                        $val = Carbon::createFromTimestamp($val);
                        $val = $val->toDateTimeString();
                    }

                    $header = str_replace(' ', '_', $header);
                    $arrAux[$header] = $val;
                }
                $responseArr[] = $arrAux;
            }
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M01ADSI',
            'S002V01F10AGCA',
            'S002V01P01HICA',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó el contenido del catálogo General.",
            $idUser,
            $nowStr
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $responseArr);
    }

    public function saveTaxInformation(Request $request) {
        $validator = Validator::make($request->all(), [
            'NOMBRE_FISCAL' => 'required|string',
            'RFC' => 'required|string',
            'CORREO_ELECTRONICO' => 'required|string',
            'REGIMEN_FISCAL' => 'required|string',
            'LADA1' => 'required|string',
            'TELEFONO_FIJO' => 'required|integer',
            'LADA2' => 'required|string',
            'TELEFONO_MOVIL' => 'required|integer',
            'CODIGO_POSTAL' => 'required|integer',
            'TIPOVIALIDAD' => 'required|string',
            'NOMBRE_VIALIDAD' => 'required|string',
            'ENTRE_CALLE' => 'required|string',
            'Y_CALLE' => 'required|string',
            'NUMERO_EXTERIOR' => 'required|string',
            // 'NUMERO_INTERIOR' => 'required|string',
            'COLONIA' => 'required|string',
            'LOCALIDAD' => 'required|string',
            'MUNICIPIO' => 'required|string',
            'ENTIDAD_FEDERATIVA' => 'required|string',
            'PAIS' => 'required|string',
            'USER' => 'required|string',
            'LINE_NUMBER' => 'required|integer',
        ]);
        if ($validator->fails()) {
            return $this->responseController->makeResponse(
                true,
                "ERR_SYSTEM_ADMINISTRATOR_TAX_INFORMATION000: Se encontraron uno o más errores.",
                $this->responseController->makeErrors($validator->errors()->messages()),
                401
            );
        }

        DB::beginTransaction();
        $responseData = $request->all();

        try {
            $user = $this->encryptionController->decrypt($responseData['USER']);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_SYSTEM_ADMINISTRATOR_TAX_INFORMATION001: Ocurrió un error al desencriptar los datos.", [], 401);
        }

        $now = $this->functionsController->now();
        $currentDate = $now->toDateTimeString();

        try {
            $existsActives = DB::table('S002V01TINFI')
                ->where('INFI_ESTA','=','Activo')
                ->where('INFI_NULI','=',$responseData['LINE_NUMBER'])
                ->exists();
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_SYSTEM_ADMINISTRATOR_TAX_INFORMATION002: Ocurrió un error al validar los registros anteriores.", [], 401);
        }
        if ($existsActives) {
            try {
                $validateUpdate = DB::table('S002V01TINFI')
                    ->where('INFI_ESTA','=','Activo')
                    ->where('INFI_NULI','=',$responseData['LINE_NUMBER'])
                    ->update([
                        'INFI_ESTA' => 'Inactivo',
                        'INFI_USMO' => $user,
                        'INFI_FEMO' => $currentDate,
                        'INFI_FEAR' => DB::raw('CURRENT_TIMESTAMP'),
                    ]);
            } catch (\Throwable $th) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_SYSTEM_ADMINISTRATOR_TAX_INFORMATION002: Ocurrió un error al modificar los registros anteriores.", $th->getMessage(), 401);
            }
            if (!$validateUpdate) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_SYSTEM_ADMINISTRATOR_TAX_INFORMATION003: No se pudo modificar los registros anteriores.", [], 401);
            }
        }

        try {
            $validateInsert = DB::table('S002V01TINFI')->insert([
                'INFI_NOFI' => $responseData['NOMBRE_FISCAL'],
                'INFI_XRFC' => $responseData['RFC'],
                'INFI_COEL' => $responseData['CORREO_ELECTRONICO'],
                'INFI_CORF' => $responseData['REGIMEN_FISCAL'],
                'INFI_LAD1' => $responseData['LADA1'],
                'INFI_TEL1' => $responseData['TELEFONO_FIJO'],
                'INFI_LAD2' => $responseData['LADA2'],
                'INFI_TEL2' => $responseData['TELEFONO_MOVIL'],
                'INFI_COPO' => $responseData['CODIGO_POSTAL'],
                'INFI_TIVI' => $responseData['TIPOVIALIDAD'],
                'INFI_VIAL' => $responseData['NOMBRE_VIALIDAD'],
                'INFI_ENCA' => $responseData['ENTRE_CALLE'],
                'INFI_YCAL' => $responseData['Y_CALLE'],
                'INFI_NUEX' => $responseData['NUMERO_EXTERIOR'],
                'INFI_NUIN' => $responseData['NUMERO_INTERIOR'],
                'INFI_COLO' => $responseData['COLONIA'],
                'INFI_LOCA' => $responseData['LOCALIDAD'],
                'INFI_MUNI' => $responseData['MUNICIPIO'],
                'INFI_ENFE' => $responseData['ENTIDAD_FEDERATIVA'],
                'INFI_PAIS' => $responseData['PAIS'],
                'INFI_NULI' => $responseData['LINE_NUMBER'],
                'INFI_USRE' => $user,
                'INFI_FERE' => $currentDate,
                'INFI_FEAR' => DB::raw('CURRENT_TIMESTAMP'),
            ]);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_SYSTEM_ADMINISTRATOR_TAX_INFORMATION004: Ocurrió un error al insertar los datos en la base de datos.", $th->getMessage(), 401);
        }
        if (!$validateInsert) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_SYSTEM_ADMINISTRATOR_TAX_INFORMATION005: No se pudo insertar los datos en la base de datos.", [], 401);
        }

        DB::commit();
        return $this->responseController->makeResponse(false, "ÉXITO: Registro Exitoso");
    }

    public function validateCatalog(Request $request) {
        $validator = Validator::make($request->all(), [
            'CODIGO_POSTAL' => 'required|boolean',
            'COLONIAS' => 'required|boolean',
            'LOCALIDADES' => 'required|boolean',
            'MUNICIPIOS' => 'required|boolean',
            'ESTADOS' => 'required|boolean',
            'PAISES' => 'required|boolean',
            'REGIMEN_FISCAL' => 'required|boolean',
            'USUARIO' => 'required|string',
            'NUMERO_LINEA' => 'required|integer',
        ]);

        if ($validator->fails()) {
            return $this->responseController->makeResponse(
                true,
                "Se encontraron uno o más errores.",
                $this->responseController->makeErrors($validator->errors()->messages()),
                401
            );
        }

        $requestData = $request->all();

        $response['CODIGO_POSTAL'] = null;
        $response['COLONIAS'] = null;
        $response['LOCALIDADES'] = null;
        $response['MUNICIPIOS'] = null;
        $response['ESTADOS'] = null;
        $response['PAISES'] = null;
        $response['REGIMEN_FISCAL'] = null;
        
        if ($requestData['CODIGO_POSTAL'] === true) {

            $get = DB::table('S002V01TCOPO')->get();
            $get = json_decode(json_encode($get), true);
            $response['CODIGO_POSTAL'] = empty($get);

        }
        if ($requestData['COLONIAS'] === true) {

            $get = DB::table('S002V01TCOLO')->get();
            $get = json_decode(json_encode($get), true);
            $response['COLONIAS'] = empty($get);

        }
        if ($requestData['LOCALIDADES'] === true) {

            $get = DB::table('S002V01TCOLO')->get();
            $get = json_decode(json_encode($get), true);
            $response['LOCALIDADES'] = empty($get);

        }
        if ($requestData['MUNICIPIOS'] === true) {

            $get = DB::table('S002V01TMUNI')->get();
            $get = json_decode(json_encode($get), true);
            $response['MUNICIPIOS'] = empty($get);

        }
        if ($requestData['ESTADOS'] === true) {

            $get = DB::table('S002V01TESTA')->get();
            $get = json_decode(json_encode($get), true);
            $response['ESTADOS'] = empty($get);

        }
        if ($requestData['PAISES'] === true) {

            $get = DB::table('S002V01TPAIS')->get();
            $get = json_decode(json_encode($get), true);
            $response['PAISES'] = empty($get);

        }
        if ($requestData['REGIMEN_FISCAL'] === true) {

            $get = DB::table('S002V01TREFI')->get();
            $get = json_decode(json_encode($get), true);
            $response['REGIMEN_FISCAL'] = empty($get);

        }

        return $this->responseController->makeResponse(false, "ÉXITO", $response);
    }

    public function getCatalogCoins ($user, $line) {

        $arrResponseCheckUser = $this->resourcesController->checkUserEnc($user, $line);
        if ($arrResponseCheckUser['error']) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, $arrResponseCheckUser['msg'], [], 401);
        }
        $user = $arrResponseCheckUser['response'];

        try {
            $arrCoins = DB::table('S002V01TCAMO')
                ->where('CAMO_NULI', '=', $line)
                ->where('CAMO_ESTA', '=', 'Activo')
                ->get([
                    'CAMO_COMO AS COIN',
                    'CAMO_DESC AS DESCRIPTION',
                ]);
            $arrCoins = json_decode(json_encode($arrCoins), true);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(true, 'Ocurrió un error al obtener los datos del catálogo de monedas.', $th->getMessage(), 500);
        }

        return $this->responseController->makeResponse(false, "ÉXITO: Consulta Exitosa", $arrCoins);
    }
}