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

<?php

namespace App\Http\Controllers;

use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Facades\Validator;
use Illuminate\Database\Query\Builder;
use Illuminate\Http\File;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Style\Border;

use ElephantIO\Client;
use stdClass;
use Throwable;

class PersonnelManagementController extends Controller
{
    private $responseController;
    private $encryptionController;
    private $documentManagementController;
    private $functionsController;
    private $resourcesController;

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

    public function getConsultOfEmployees($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);
        }

        $employees = DB::table('S002V01TPERS')->select([
            'PERS_IDPE AS ID_EMPLOYEE',
            DB::raw('TRIM(CONCAT(USUA_NOMB, " " , USUA_APPA, " ", COALESCE(USUA_APMA,""))) as NAME'),
            'PERS_TICO AS CONTRACT_TYPE',
            'PERS_IDPS AS ID_SUBCONTRATISTA',
            'PERS_IDUS AS ID_USUARIO',
            'PERS_ESPE AS SPECIALITY',
            'PERS_EQTR AS TEAM_ID',
            'EQMA_NOMB AS TEAM_NAME',
            'PERS_FERE AS REGISTER_DATE',
            'PERS_USRE AS REGISTERED_BY_USER',
            'PERS_FEMO AS UPDATE_DATE',
            'PERS_USMO AS UPDATED_BY_USER',
            'PERS_ESTA AS STATUS'
        ])->join('S002V01TUSUA', 'PERS_IDUS', '=', 'USUA_IDUS')
        ->leftJoin('S002V01TEQMA', 'PERS_EQTR', '=', 'EQMA_IDEQ')
        ->where('USUA_NULI', '=', $line)->get()->all();

        foreach($employees as $key=>$employee){
            $employee->ID_EMPLOYEE = $this->encryptionController->encrypt($employee->ID_EMPLOYEE);
            if(!is_null($employee->TEAM_ID)){
                $employee->TEAM_ID = $this->encryptionController->encrypt($employee->TEAM_ID);
            }

            if(!is_null($employee->ID_SUBCONTRATISTA)){
                $employee->ID_SUBCONTRATISTA = $this->encryptionController->encrypt($employee->ID_SUBCONTRATISTA);
            }

            $employee->ID_USUARIO = $this->encryptionController->encrypt($employee->ID_USUARIO);
            $specialtiesArr = json_decode($employee->SPECIALITY, true);
            foreach($specialtiesArr as $key0=>$specialtyCode){
                $specialty = DB::table('S002V01TGEES')->where([
                    ['GEES_NULI', '=', $line],
                    ['GEES_COES', '=', $specialtyCode]
                ])->first();

                $specialtyName = $specialtyCode . " - " . $specialty->GEES_NOES;
                $specialtiesArr[$key0] = $this->encryptionController->encrypt($specialtyName);
            }

            $employee->SPECIALITY = json_encode($specialtiesArr);
            $employees[$key] = $employee;
        }

        $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,
            'S002V01M11GPRS',
            'S002V01F01GEPE',
            'S002V01P01COEM',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó los empleados registrados.",
            $idUser,
            $nowStr,
            'S002V01S02GEPE'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $employees);
    }
    
    public function getEmployeeById($idEmployee, $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);
        }

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

        $employee = DB::table('S002V01TPERS')->select([
            'PERS_IDPE AS ID_EMPLOYEE',
            DB::raw('TRIM(CONCAT(USUA_NOMB, " " , USUA_APPA, " ", COALESCE(USUA_APMA,""))) as EMPLOYEE_NAME'),
            'PERS_IDUS AS USER_ID',
            'EQMA_IDEQ AS WORKTEAM_ID',
            'EQMA_NOMB AS WORKTEAM_NAME',
            'PERS_TICO AS CONTRACT_TYPE',
            'PERS_IDPS AS SUBCONTRATIST_ID',
            'PESU_RASO AS SUBCONTRATIST_NAME',
            'PESU_REFI AS TAX_REGIME',
            'REFI_DRFI AS TAX_REGIME_DESCRIPTION',
            'PERS_ESPE AS SPECIALITY',
            'PERS_EXTR AS FOREIGNER',
            'PERS_XRFC AS RFC',
            'PERS_TAID AS TAX_ID',
            'PERS_IDPA AS COUNTRY',
            'PERS_ENFE AS STATE',
            'PERS_CIUD AS CITY',
            'PERS_LOCA AS TOWN',
            'PERS_COLO AS SETTING',
            'PERS_COPO AS ZIP_CODE',
            'PERS_CALL AS STREET',
            'PERS_NUEX AS EXTERIOR_NUMBER',
            'PERS_NUIN AS INTERIOR_NUMBER',
            'PERS_NOCE AS CONTACT_NAME',
            'PERS_LCEM AS CONTACT_LADA',
            'PERS_NUTC AS CONTACT_TELEPHONE',
            'PERS_DCEM AS CONTACT_ADDRESS',
            'PERS_DOAS AS DOCUMENTS',
            'PERS_ESTA AS STATUS',
            'PERS_USRE AS USRREG',
            'PERS_USMO AS USRMOD',
            'PERS_FERE AS FECREG',
            'PERS_FEMO AS FECMOD',
        ])->leftJoin('S002V01TEQMA', 'EQMA_IDEQ', '=', 'PERS_EQTR')
        ->leftJoin('S002V01TPESU', 'PESU_IDPS', '=', 'PERS_IDPS')
        ->leftJoin('S002V01TREFI', 'REFI_CRFI', '=', 'PESU_REFI')
        ->join('S002V01TUSUA', 'PERS_IDUS', '=', 'USUA_IDUS')->where([
            ['PERS_NULI', '=', $line],
            ['PERS_IDPE', '=', $idEmployee],
        ])->first();

        if(is_null($employee)){
            return $this->responseController->makeResponse(true, 'El empleado consultado no existe.', [], 404);
        }else if($employee->STATUS == 'Eliminado'){
            return $this->responseController->makeResponse(true, 'El empleado consultado está eliminado.', [], 404);
        }

        if(!is_null($employee->RFC)){
            $employee->RFC = $this->encryptionController->encrypt($employee->RFC);
        }

        if(!is_null($employee->TAX_ID)){
            $employee->TAX_ID = $this->encryptionController->encrypt($employee->TAX_ID);
        }

        $attached = [];
        $docsArr = json_decode($employee->DOCUMENTS, true);
        foreach($docsArr as $doc){
            $docArr = explode('=', $doc['CODE']);
            $codeArr = explode('-', $docArr[0]);

            $document = DB::table('S002V01TAFAL')->select([
                'AFAL_NOAR AS FILE_NAME',
                'AFAL_EXTE AS FILE_EXTENSION',
                'AFAL_TAMA AS FILE_SIZE',
                'AFAL_ESTA AS FILE_STATUS'
            ])->where([
                ['AFAL_NULI', '=', $line],
                ['AFAL_COMO', '=', $codeArr[1]],
                ['AFAL_CLDO', '=', $codeArr[2]],
                ['AFAL_FECR', '=', $codeArr[3]],
                ['AFAL_NUSE', '=', $codeArr[4]],
                ['AFAL_NUVE', '=', $docArr[1]],
            ])->first();

            if (!is_null($document)) {
                $document->FILE_ID = $this->encryptionController->encrypt($doc['CODE']);
                $document->FILE_TYPE = $doc['TYPE'];
                $attached[] = $document;
            }
        }

        $employee->DOCUMENTS = json_encode($attached);
        $country = DB::table('S002V01TPAIS')->where([
            ['PAIS_NULI', '=', $line],
            ['PAIS_IDPA', '=', $employee->COUNTRY]
        ])->first();

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

        $city = DB::table('S002V01TMUNI')->where([
            ['MUNI_NULI', '=', $line],
            ['MUNI_COMU', '=', $employee->CITY],
            ['MUNI_COES', '=', $employee->STATE],
        ])->first();

        $town = DB::table('S002V01TLOCA')->where([
            ['LOCA_NULI', '=', $line],
            ['LOCA_COLO', '=', $employee->TOWN],
            ['LOCA_COES', '=', $employee->STATE],
        ])->first();

        $setting = DB::table('S002V01TCOLO')->where([
            ['COLO_NULI', '=', $line],
            ['COLO_COCO', '=', $employee->SETTING],
            ['COLO_COPO', '=', $employee->ZIP_CODE],
        ])->first();

        $employee->COUNTRY = $country->PAIS_NOMB . " (" . $employee->COUNTRY . ")";
        $employee->STATE = is_null($state) ? $employee->STATE : $state->ESTA_NOES . " (" . $employee->STATE . ")";
        $employee->CITY = is_null($city) ? $employee->CITY : $city->MUNI_NOMU . " (" . $employee->CITY . ")";
        $employee->TOWN = is_null($town) ? $employee->TOWN : $town->LOCA_NOLO . " (" . $employee->TOWN . ")";
        $employee->SETTING = is_null($setting) ? $employee->SETTING : $setting->COLO_NOCO . " (" . $employee->SETTING . ")";

        $employee->ZIP_CODE = $this->encryptionController->encrypt($employee->ZIP_CODE);
        $employee->CONTACT_TELEPHONE = $this->encryptionController->encrypt($employee->CONTACT_TELEPHONE);
        $employee->USER_ID = $this->encryptionController->encrypt($employee->USER_ID);
        $employee->WORKTEAM_ID = !is_null($employee->WORKTEAM_ID) ? $this->encryptionController->encrypt($employee->WORKTEAM_ID) : null;

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

        $nameReg = $this->functionsController->joinName($usrReg->USUA_NOMB, $usrReg->USUA_APPA, $usrReg->USUA_APMA);
        $employee->USRREG = "$nameReg (" . $employee->USRREG . ")";

        if(!is_null($employee->USRMOD)){
            $usrMod = DB::table('S002V01TUSUA')->where([
                ['USUA_NULI', '=', $line],
                ['USUA_IDUS', '=', $employee->USRMOD]
            ])->first();

            $nameMod = $this->functionsController->joinName($usrMod->USUA_NOMB, $usrMod->USUA_APPA, $usrMod->USUA_APMA);
            $employee->USRMOD = "$nameMod (" . $employee->USRMOD . ")";
        }

        $specialtiesArr = json_decode($employee->SPECIALITY, true);
        foreach($specialtiesArr as $key=>$specialtyCode){
            $specialtyObj = DB::table('S002V01TGEES')->where([
                ['GEES_NULI', '=', $line],
                ['GEES_COES', '=', $specialtyCode]
            ])->first();

            $specialty = $specialtyCode . " - " . $specialtyObj->GEES_NOES;
            $specialtiesArr[$key] = $this->encryptionController->encrypt($specialty);
        }

        $employee->SPECIALITY = json_encode($specialtiesArr);
        $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,
            'S002V01M11GPRS',
            'S002V01F01GEPE',
            'S002V01P03DEEM',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó al empleado " . $employee->EMPLOYEE_NAME . " ($idEmployee).",
            $idUser,
            $nowStr,
            'S002V01S02GEPE'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $employee);
    }
    public function updateToInactiveStatusEmployee(Request $request) {
        DB::enableQueryLog();

        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'id_employee' => '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);
        }

        $idEmployee = $this->encryptionController->decrypt($form['id_employee']);
        if(!$idEmployee){
            return $this->responseController->makeResponse(true, 'El ID del empleado que desea actualizar no fue encriptado correctamente.', [], 400);
        }

        $employee = DB::table('S002V01TPERS')->where([
            ['PERS_NULI', '=', $form['linea']],
            ['PERS_IDPE', '=', $idEmployee]
        ])->first();
        
        if(is_null($employee)){
            return $this->responseController->makeResponse(true, 'El empleado que desea actualizar no existe.', [], 404);
        }
        
        if($idUser == $employee->PERS_IDUS){
            return $this->responseController->makeResponse(true, 'El usuario no puede eliminarse a sí mismo.', [], 400);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        DB::table('S002V01TPERS')->where([
            ['PERS_NULI', '=', $form['linea']],
            ['PERS_IDPE', '=', $idEmployee]
        ])->update([
            'PERS_ESTA' => 'Eliminado',
            'PERS_USMO' => $idUser,
            'PERS_FEMO' => $nowStr,
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M11GPRS',
            'S002V01F01GEPE',
            'S002V01P02REEM',
            'Eliminación',
            "El usuario $name (" . $usr->USUA_IDUS . ") eliminó al empleado $idEmployee.",
            $idUser,
            $nowStr,
            'S002V01S02GEPE'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO');
    }
    public function getContractsOfEveryEmployee($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);
        }

        $contractsByEmployees = DB::table('S002V01TPERS')->select([
            DB::raw("
                TRIM(CONCAT(
                    USUA_NOMB, 
                    ' ', 
                    USUA_APPA,
                    IF(ISNULL(USUA_APMA), '', CONCAT(' ', USUA_APMA)),
                    ' <(',
                    PERS_IDPE,
                    ')> (',
                    PERS_IDUS,
                    ')'
                )) AS EMPLOYEE
            "),
            DB::raw("COUNT(CONT_IDCO) AS CONTRACTS")
        ])->leftJoin('S002V01TCONT', 'CONT_IDEM', '=', 'PERS_IDPE')
        ->join('S002V01TUSUA', 'USUA_IDUS', '=', 'PERS_IDUS')
        ->groupBy('EMPLOYEE')->where([
            ['PERS_NULI', '=', $line],
            ['PERS_TICO', '=', 'Interno'],
        ])->get()->all();

        foreach($contractsByEmployees as $key=>$val){
            $employeeArr = explode('<', $val->EMPLOYEE);
            $employeeName = $employeeArr[0];
            $employeeArr = array_reverse($employeeArr);
            $employeeArr = explode('>', $employeeArr[0]);

            $employeeID = str_replace('(', '', $employeeArr[0]);
            $employeeID = str_replace(')', '', $employeeID);

            $val->ID_EMPLOYEE = $this->encryptionController->encrypt($employeeID);
            $val->EMPLOYEE = $employeeName . join('', $employeeArr);

            $contractsByEmployees[$key] = $val;
        }

        $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,
            'S002V01M11GPRS',
            'S002V01F02ACEM',
            'S002V01P01HCEM',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó los contratos por empleados.",
            $idUser,
            $nowStr,
            'S002V01S02GEPE'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $contractsByEmployees);
    }
    public function getDetailsOfContractsByEmployee($idEmployee, $idUser, $line) {
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue 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);
        }

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

        $sub = DB::table('S002V01TPERS')->where([
            ['PERS_NULI', '=', $line],
            ['PERS_IDPE', '=', $idEmployee],
            ['PERS_TICO', '=', 'Interno'],
        ])->first();
        
        if(is_null($sub)){
            return $this->responseController->makeResponse(true, 'El empleado solicitado no existe.', [], 404);
        }

        $contracts = DB::table('S002V01TCONT')->select([
            'CONT_NOCO AS CONTRACT_NAME',
            'CONT_FEIN AS START_DATE',
            'CONT_FEFI AS END_DATE',
            'CONT_COST AS COST',
        ])->where([
            ['CONT_NULI', '=', $line],
            ['CONT_IDEM', '=', $idEmployee],
            ['CONT_TIPO', '=', 'Interno'],
        ])->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,
            'S002V01M11GPRS',
            'S002V01F02ACEM',
            'S002V01P01HCEM',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó los contratos del empleado #$idEmployee.",
            $idUser,
            $nowStr,
            'S002V01S02GEPE'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $contracts);
    }
    public function getInterventionsByEmployee($idEmployee, $idUser, $line) {
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue 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);
        }

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

        $employee = DB::table('S002V01TPERS')->where([
            ['PERS_NULI', '=', $line],
            ['PERS_IDPE', '=', $idEmployee],
        ])->first();
        
        if(is_null($employee)){
            return $this->responseController->makeResponse(true, 'El empleado solicitado no existe.', [], 404);
        }

        $preventiveOrders = DB::select("
        SELECT 
            DISTINCT(S002V01TOTPR.OTPR_IDOT) AS IDORDEN, 
            S002V01TOTPR.OTPR_DEIN AS DESCRIPCION,
            'Preventivo' AS TIPOORDEN
        FROM 
            S002V01TOTPR, 
            JSON_TABLE(OTPR_OPPR, '$[*]' COLUMNS(
                OTPR_OPPR_ID INT PATH '$.ID', 
                OTPR_OPPR_TYPE VARCHAR(100) PATH '$.TYPE'
            )) OPPR 
        WHERE 
            (OTPR_OPPR_ID = :emp_id AND OTPR_OPPR_TYPE = 'EM')
            OR (OTPR_OPPR_ID = :sub_id AND OTPR_OPPR_TYPE = 'SU')
            OR (OTPR_OPPR_ID = :team_id AND OTPR_OPPR_TYPE = 'EQ')
        ", [
            'emp_id' => $employee->PERS_IDPE,
            'sub_id' => $employee->PERS_IDPS,
            'team_id' => $employee->PERS_EQTR
        ]);

        //PENDIENTE MANTENIMIENTO CORRECTIVO

        $ordersFN = [];
        $relatedOrders = [];
        foreach($preventiveOrders as $order){
            if(!in_array($order->IDORDEN, $relatedOrders)){
                $relatedOrders[] = $order->IDORDEN;

                $order->IDORDEN = $this->encryptionController->encrypt($order->IDORDEN);
                $ordersFN[] = $order;
            }
        }

        $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,
            'S002V01M11GPRS',
            'S002V01F01GEPE',
            'S002V01P03DEEM',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó las órdenes de trabajo relacionadas al empleado #$idEmployee.",
            $idUser,
            $nowStr,
            'S002V01S02GEPE'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $ordersFN);
    }
    public function getConsultOfEmployeesForLeader($idEmployee, $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);
        }

        try {
            $idEmployee = $this->encryptionController->decrypt($idEmployee);
        } catch (Throwable $th) {
            return $this->responseController->makeResponse(true, 'El ID del empleado no se encriptó 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);
        }

        if ( is_null($idEmployee) || $idEmployee === '') {
            $employees = DB::table('S002V01TPERS')
            ->select([
                'PERS_IDPE AS ID_EMPLOYEE',
                DB::raw('TRIM(CONCAT(USUA_NOMB, " " , USUA_APPA, " ", COALESCE(USUA_APMA,""))) as NAME'),
                'PERS_TICO AS CONTRACT_TYPE',
                'PERS_ESPE AS SPECIALITY',
                'PERS_EQTR AS TEAM_ID',
                'EQMA_NOMB AS TEAM_NAME',
                'PERS_FERE AS REGISTER_DATE',
                'PERS_USRE AS REGISTERED_BY_USER',
                'PERS_FEMO AS UPDATE_DATE',
                'PERS_USMO AS UPDATED_BY_USER',
            ])
            ->join('S002V01TUSUA', 'PERS_IDUS', '=', 'USUA_IDUS')
            ->leftJoin('S002V01TEQMA', 'PERS_EQTR', '=', 'EQMA_IDEQ')
            ->where('PERS_ESTA', '=', 'Activo')
            ->where('USUA_NULI', '=', $line)
            ->get()
            ->all();
        } else {
            $employees = DB::table('S002V01TPERS')
            ->select([
                'PERS_IDPE AS ID_EMPLOYEE',
                DB::raw('TRIM(CONCAT(USUA_NOMB, " " , USUA_APPA, " ", COALESCE(USUA_APMA,""))) as NAME'),
                'PERS_TICO AS CONTRACT_TYPE',
                'PERS_ESPE AS SPECIALITY',
                'PERS_EQTR AS TEAM_ID',
                'EQMA_NOMB AS TEAM_NAME',
                'PERS_FERE AS REGISTER_DATE',
                'PERS_USRE AS REGISTERED_BY_USER',
                'PERS_FEMO AS UPDATE_DATE',
                'PERS_USMO AS UPDATED_BY_USER',
            ])
            ->join('S002V01TUSUA', 'PERS_IDUS', '=', 'USUA_IDUS')
            ->leftJoin('S002V01TEQMA', 'PERS_EQTR', '=', 'EQMA_IDEQ')
            ->where('PERS_ESTA', '=', 'Activo')
            ->where('USUA_NULI', '=', $line)
            ->where('PERS_IDPE', '!=', $idEmployee)
            ->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,
            'S002V01M11GPRS',
            'S002V01F01GEPE',
            'S002V01P01COEM',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó los empleados registrados.",
            $idUser,
            $nowStr,
            'S002V01S02GEPE'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $employees);
    }
    public function storeEmployee(Request $request) {
        DB::enableQueryLog();

        $request['RFC'] = $this->encryptionController->decrypt($request->RFC) ? $this->encryptionController->decrypt($request->RFC) : 'ENC_ERR';
        $request['TAX'] = $this->encryptionController->decrypt($request->TAX) ? $this->encryptionController->decrypt($request->TAX) : 'ENC_ERR';
        $request['CONTACT_NAME'] = $this->encryptionController->decrypt($request->CONTACT_NAME) ? $this->encryptionController->decrypt($request->CONTACT_NAME) : 'ENC_ERR';
        $request['CONTACT_TELEPHONE'] = $this->encryptionController->decrypt($request->CONTACT_TELEPHONE) ? $this->encryptionController->decrypt($request->CONTACT_TELEPHONE) : 'ENC_ERR';
        $request['CONTACT_LADA'] = $this->encryptionController->decrypt($request->CONTACT_LADA) ? $this->encryptionController->decrypt($request->CONTACT_LADA) : 'ENC_ERR';
        $request['CONTACT_ADDRESS'] = $this->encryptionController->decrypt($request->CONTACT_ADDRESS) ? $this->encryptionController->decrypt($request->CONTACT_ADDRESS) : 'ENC_ERR';

        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'USER_ID' => 'required|string',
            'WORKTEAM_ID' => 'required|string',
            'CONTRACT_TYPE' => 'required|string|in:Subcontratista,Interno',
            'SUBCONTRATIST_ID' => 'required_if:CONTRACT_TYPE,=,Subcontratista|string',
            'SPECIALITY' => 'required|json',
            'FOREIGNER' => 'required|string|in:Si,No',
            'RFC' => 'required_if:FOREIGNER,=,No|string|max:13',
            'TAX' => 'required_if:FOREIGNER,=,Si|string|max:13',
            'COUNTRY' => 'required|string|max:75',
            'FEDERAL_ENTITY' => 'required|string|max:75',
            'CITY' => 'string|max:75',
            'TOWN' => 'string|max:75',
            'SUBURB' => 'required|string|max:75',
            'POSTAL_CODE' => 'required|string|max:5',
            'STREET' => 'required|string|max:150',
            'EXTERIOR_NUMBER' => 'required|integer',
            'INTERIOR_NUMBER' => 'integer',
            'CONTACT_NAME' => 'required|string|max:150',
            'CONTACT_LADA' => 'required|string|max:10',
            'CONTACT_TELEPHONE' => 'required|string|max:11',
            'CONTACT_ADDRESS' => 'required|string|max:250',
            'ATTACHED' => 'required|json',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                TRUE,
                'ERR_PERSONAL_REG001: Uno o más errores encontrados',
                $this->responseController->makeErrors($validator->errors()->messages()),
                400
            );
        }

        $form = $request->all();
        foreach($form as $k=>$v){
            if($k == 'INTERIOR_NUMBER' && $v == '0'){
                unset($form[$k]);
            }else if($v == '-'){
                unset($form[$k]);
            }
        }

        $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);
        }

        $idUserReg = $this->encryptionController->decrypt($form['USER_ID']);
        if(!$idUserReg){
            return $this->responseController->makeResponse(true, 'El ID del usuario seleccionado no fue encriptado correctamente.', [], 400);
        }else if($idUser == $idUserReg){
            return $this->responseController->makeResponse(true, 'El usuario no puede registrarse a sí mismo.', [], 400);
        }
        
        $usrReg = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $form['linea']],
            ['USUA_IDUS', '=', $idUserReg]
        ])->first();
        
        if(is_null($usrReg)){
            return $this->responseController->makeResponse(true, 'El usuario seleccionado no existe.', [], 404);
        }

        $idTeam = null;
        if(isset($form['WORKTEAM_ID'])){
            $idTeam = $this->encryptionController->decrypt($form['WORKTEAM_ID']);
            if(!$idTeam){
                return $this->responseController->makeResponse(true, 'El ID del equipo seleccionado encriptado correctamente.', [], 400);
            }

            $team = DB::table('S002V01TEQMA')->where([
                ['EQMA_IDEQ', '=', $idTeam],
                ['EQMA_NULI', '=', $form['linea']],
            ])->first();
        
            if(is_null($team)){
                return $this->responseController->makeResponse(true, 'El equipo seleccionado no existe.', [], 404);
            }else if($team->EQMA_ESTA == 'Eliminado'){
                return $this->responseController->makeResponse(true, 'El equipo seleccionado está eliminado.', [], 404);
            }
        }

        $idSubcontratist = null;
        if(isset($form['SUBCONTRATIST_ID'])){
            $idSubcontratist = $this->encryptionController->decrypt($form['SUBCONTRATIST_ID']);
            if(!$idSubcontratist){
                return $this->responseController->makeResponse(true, 'El ID del subcontratista seleccionado no fue encriptado correctamente.', [], 400);
            }

            $subcontratist = DB::table('S002V01TPESU')->where([
                ['PESU_IDPS', '=', $idSubcontratist],
                ['PESU_NULI', '=', $form['linea']],
            ])->first();
        
            if(is_null($subcontratist)){
                return $this->responseController->makeResponse(true, 'El subcontratista seleccionado no existe.', [], 404);
            }
        }

        $rfcx = null;
        if(isset($form['RFC']) && $form['FOREIGNER'] == 'No' && $form['RFC'] == 'ENC_ERR'){
            return $this->responseController->makeResponse(true, 'El RFC no fue encriptado correctamente.', [], 400);
        }else if(isset($form['RFC']) && $form['RFC'] != 'ENC_ERR'){
            $rfcx = $form['RFC'];
        }

        $taid = null;
        if(isset($form['TAX']) && $form['FOREIGNER'] == 'Si' && $form['TAX'] == 'ENC_ERR'){
            return $this->responseController->makeResponse(true, 'El TAX ID no fue encriptado correctamente.', [], 400);
        }else if(isset($form['TAX']) && $form['TAX'] != 'ENC_ERR'){
            $taid =  $form['TAX'];
        }
        
        $country = DB::table('S002V01TPAIS')->where([
            ['PAIS_NULI', '=', $form['linea']],
            ['PAIS_IDPA', '=', $form['COUNTRY']],
        ])->first();
        
        if(is_null($country)){
            return $this->responseController->makeResponse(true, 'El país seleccionado no existe.', [], 404);
        }

        $colo = null;
        $ciud = null;
        $loca = null;
        if($form['COUNTRY'] == 'MEX' || $form['COUNTRY'] == 'USA' || $form['COUNTRY'] == 'CAN'){
            $state = DB::table('S002V01TESTA')->where([
                ['ESTA_NULI', '=', $form['linea']],
                ['ESTA_COES', '=', $form['FEDERAL_ENTITY']],
                ['ESTA_COPA', '=', $form['COUNTRY']],
            ])->first();
        
            if(is_null($state)){
                return $this->responseController->makeResponse(true, 'El estado seleccionado no existe.', [], 404);
            }

            if($form['COUNTRY'] == 'MEX'){
                if(isset($form['CITY'])){
                    $city = DB::table('S002V01TMUNI')->where([
                        ['MUNI_NULI', '=', $form['linea']],
                        ['MUNI_COMU', '=', $form['CITY']],
                        ['MUNI_COES', '=', $form['FEDERAL_ENTITY']],
                    ])->first();
            
                    if(is_null($city)){
                        return $this->responseController->makeResponse(true, 'El municipio seleccionado no existe.', [], 404);
                    }else{
                        $ciud = $form['CITY'];
                    }
                }

                if(isset($form['TOWN'])){
                    $town = DB::table('S002V01TLOCA')->where([
                        ['LOCA_NULI', '=', $form['linea']],
                        ['LOCA_COLO', '=', $form['TOWN']],
                        ['LOCA_COES', '=', $form['FEDERAL_ENTITY']],
                    ])->first();
            
                    if(is_null($town)){
                        return $this->responseController->makeResponse(true, 'La localidad seleccionada no existe.', [], 404);
                    }else{
                        $loca = $form['TOWN'];
                    }
                }
                
                $setting = DB::table('S002V01TCOLO')->where([
                    ['COLO_NULI', '=', $form['linea']],
                    ['COLO_COCO', '=', $form['SUBURB']],
                    ['COLO_COPO', '=', $form['POSTAL_CODE']],
                ])->first();
            
                if(is_null($setting)){
                    return $this->responseController->makeResponse(true, 'La colonia seleccionada no existe.', [], 404);
                }else{
                    $colo = $form['SUBURB'];
                }
                
                $zipCode = DB::table('S002V01TCOPO')->where([
                    ['COPO_NULI', '=', $form['linea']],
                    ['COPO_COPO', '=', $form['POSTAL_CODE']],
                    ['COPO_COES', '=', $form['FEDERAL_ENTITY']],
                ])->first();
            
                if(is_null($zipCode)){
                    return $this->responseController->makeResponse(true, 'El código postal seleccionado no existe.', [], 404);
                }
            }else{
                $colo = $form['SUBURB'];
                $ciud = $form['CITY'];
                $loca = isset($form['TOWN']) ? $form['TOWN'] : null;
            }
        }else{
            $colo = $form['SUBURB'];
            $ciud = $form['CITY'];
            $loca = isset($form['TOWN']) ? $form['TOWN'] : null;
        }

        $subArr = DB::table('S002V01TPERS')->where('PERS_NULI', '=', $form['linea'])->where(function(Builder $query) use ($form, $idUserReg) {
            $query->where('PERS_IDUS', '=', $idUserReg)
            ->orWhere('PERS_XRFC', '=', $form['RFC']);
        })->where('PERS_ESTA', '=', 'Activo')->get()->all();

        if(count($subArr) > 0){
            return $this->responseController->makeResponse(true, 'La razón social, el RFC o el correo electrónico ya fueron registrados.', [], 401);
        }

        $ladasValues = [];
        $ladas = DB::table('S002V01TPAIS')->select([
            'PAIS_LADA AS LADA'
        ])->where('PAIS_NULI', '=', $form['linea'])->get()->all();

        foreach($ladas as $lada){
            if($lada->LADA != '' && $lada->LADA != '0'){
                $ladasValues[] = $lada->LADA;
            }
        }

        if($form['CONTACT_LADA'] == 'ENC_ERR'){
            return $this->responseController->makeResponse(true, "La lada del contacto de emergencia no fue encriptada correctamente.", [], 400);
        }else if(!in_array($form['CONTACT_LADA'], $ladasValues)){
            return $this->responseController->makeResponse(true, "La lada $form[CONTACT_LADA] no está relacionada a ningún país.", [], 400);
        }else if($form['CONTACT_TELEPHONE'] == 'ENC_ERR'){
            return $this->responseController->makeResponse(true, 'El número telefónico del contacto de emergencia no fue encriptado correctamente.', [], 400);
        }

        if($form['CONTACT_ADDRESS'] == 'ENC_ERR'){
            return $this->responseController->makeResponse(true, "La dirección del contacto de emergencia no fue encriptada correctamente.", [], 400);
        }

        if($form['CONTACT_NAME'] == 'ENC_ERR'){
            return $this->responseController->makeResponse(true, "El nombre del contacto de emergencia no fue encriptada correctamente.", [], 400);
        }

        $docsArr = json_decode($form['ATTACHED'], true);
        if(count($docsArr) == 0){
            return $this->responseController->makeResponse(true, "El arreglo de archivos adjuntos está vacío.", [], 400);
        }

        $authCont = 0;
        foreach($docsArr as $doc){
            if($doc['type'] == 'AU') $authCont++;
        }

        if($authCont < 1){
            return $this->responseController->makeResponse(true, "El arreglo de archivos adjuntos debe contener al menos un documento de autorización.", [], 400);
        }

        $finalDocsArr = [];
        foreach($docsArr as $doc){
            $idFileDec = $this->encryptionController->decrypt($doc['id']);
            $tempFile = DB::table('S002V01TARTE')->where([
                ['ARTE_NULI', '=', $form['linea']],
                ['ARTE_IDAR', '=', $idFileDec],
            ])->first();

            if(is_null($tempFile)){
                return $this->responseController->makeResponse(true, "El archivo $doc[name] no está registrado.", [], 404);
            }

            $classifications = ["AU" => "IN", "OF" => "CO", "CE" => "CE"];
            $cldo = $classifications[$doc['type']];
            $finalFile = $this->documentManagementController->moveFinalFile($form['linea'], "GPRS", $cldo, $tempFile, $idUser);

            if(!$finalFile[0]){
                return $this->responseController->makeResponse(true, $finalFile[1], [], 400);
            }else{
                $finalDocsArr[] = [
                    'CODE' => $finalFile[1],
                    'TYPE' => $doc['type']
                ];
            }
        }


        $specialtiesArr = json_decode($form['SPECIALITY'], true);
        foreach($specialtiesArr as $key=>$specialtyCode){
            $specialtyCode = $this->encryptionController->decrypt($specialtyCode);
            if(!$specialtyCode){
                return $this->responseController->makeResponse(true, "El ID del elemento en la posición $key del arreglo de especialidades no fue encriptado correctamente.", [], 400);
            }

            $specialtyObj = DB::table('S002V01TGEES')->where([
                ['GEES_NULI', '=', $form['linea']],
                ['GEES_COES', '=', $specialtyCode]
            ])->first();

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

            $specialtiesArr[$key] = $specialtyCode;
        }

        $specialtiesStr = json_encode($specialtiesArr);
        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $doas = json_encode($finalDocsArr);
        $nuin = isset($form['INTERIOR_NUMBER']) ? $form['INTERIOR_NUMBER'] : null;
        $employeeID = DB::table('S002V01TPERS')->insertGetId([
            'PERS_NULI' => $form['linea'],
            'PERS_IDUS' => $idUserReg,
            'PERS_EQTR' => $idTeam,
            'PERS_TICO' => $form['CONTRACT_TYPE'],
            'PERS_IDPS' => $idSubcontratist,
            'PERS_ESPE' => $specialtiesStr,
            'PERS_EXTR' => $form['FOREIGNER'],
            'PERS_XRFC' => $rfcx,
            'PERS_TAID' => $taid,
            'PERS_IDPA' => $form['COUNTRY'],
            'PERS_ENFE' => $form['FEDERAL_ENTITY'],
            'PERS_CIUD' => $ciud,
            'PERS_LOCA' => $loca,
            'PERS_COLO' => $colo,
            'PERS_COPO' => $form['POSTAL_CODE'],
            'PERS_CALL' => $form['STREET'],
            'PERS_NUEX' => $form['EXTERIOR_NUMBER'],
            'PERS_NUIN' => $nuin,
            'PERS_NOCE' => $form['CONTACT_NAME'],
            'PERS_LCEM' => $form['CONTACT_LADA'],
            'PERS_NUTC' => $form['CONTACT_TELEPHONE'],
            'PERS_DCEM' => $form['CONTACT_ADDRESS'],
            'PERS_DOAS' => $doas,
            'PERS_USRE' => $idUser,
            'PERS_FERE' => $nowStr,
        ]);


        if ( !is_null($form['EMPLOYEE_BOSS']) && $form['EMPLOYEE_BOSS'] !== '' && $form['EMPLOYEE_BOSS'] !== 'null') {
            try {
                $validateRegister = DB::table('S002V01TCREM')->insert([
                    'CREM_IDES' => $employeeID,
                    'CREM_IDEJ' => $form['EMPLOYEE_BOSS'],
                    'CREM_NULI' => $form['linea'],
                    'CREM_USRE' => $idUser,
                    'CREM_FERE' => $nowStr,
                    'CREM_FEAR' => DB::raw('CURRENT_TIMESTAMP'),
                ]);
            } catch (Throwable $th) {
                return $this->responseController->makeResponse(true, "Ocurrió un error al registrar el cronograma.", [], 400);
            }
            if (!$validateRegister) {
                return $this->responseController->makeResponse(true, "No se pudo registrar los empleados en el cronograma.", [], 400);
            }
        }

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        $nameEmp = $this->functionsController->joinName($usrReg->USUA_NOMB, $usrReg->USUA_APPA, $usrReg->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M11GPRS',
            'S002V01F01GEPE',
            'S002V01P02REEM',
            'Registro',
            "El usuario $name (" . $usr->USUA_IDUS . ") registró al empleado " . $nameEmp . "($employeeID).",
            $idUser,
            $nowStr,
            'S002V01S02GEPE'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO');
    }
    public function updateEmployee(Request $request) {
        DB::enableQueryLog();

        $request['RFC'] = $this->encryptionController->decrypt($request->RFC) ? $this->encryptionController->decrypt($request->RFC) : 'ENC_ERR';
        $request['TAX'] = $this->encryptionController->decrypt($request->TAX) ? $this->encryptionController->decrypt($request->TAX) : 'ENC_ERR';
        $request['CONTACT_NAME'] = $this->encryptionController->decrypt($request->CONTACT_NAME) ? $this->encryptionController->decrypt($request->CONTACT_NAME) : 'ENC_ERR';
        $request['CONTACT_TELEPHONE'] = $this->encryptionController->decrypt($request->CONTACT_TELEPHONE) ? $this->encryptionController->decrypt($request->CONTACT_TELEPHONE) : 'ENC_ERR';
        $request['CONTACT_LADA'] = $this->encryptionController->decrypt($request->CONTACT_LADA) ? $this->encryptionController->decrypt($request->CONTACT_LADA) : 'ENC_ERR';
        $request['CONTACT_ADDRESS'] = $this->encryptionController->decrypt($request->CONTACT_ADDRESS) ? $this->encryptionController->decrypt($request->CONTACT_ADDRESS) : 'ENC_ERR';

        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'id_employee' => 'required|string',
            'USER_ID' => 'required|string',
            'WORKTEAM_ID' => 'required|string',
            'CONTRACT_TYPE' => 'required|string|in:Subcontratista,Interno',
            'SUBCONTRATIST_ID' => 'required_if:CONTRACT_TYPE,=,Subcontratista|string',
            'SPECIALITY' => 'required|json',
            'FOREIGNER' => 'required|string|in:Si,No',
            'RFC' => 'required_if:FOREIGNER,=,No|string|max:13',
            'TAX' => 'required_if:FOREIGNER,=,Si|string|max:13',
            'COUNTRY' => 'required|string|max:75',
            'FEDERAL_ENTITY' => 'required|string|max:75',
            'CITY' => 'string|max:75',
            'TOWN' => 'string|max:75',
            'SUBURB' => 'required|string|max:75',
            'POSTAL_CODE' => 'required|string|max:5',
            'STREET' => 'required|string|max:150',
            'EXTERIOR_NUMBER' => 'required|integer',
            'INTERIOR_NUMBER' => 'integer',
            'CONTACT_NAME' => 'required|string|max:150',
            'CONTACT_LADA' => 'required|string|max:10',
            'CONTACT_TELEPHONE' => 'required|string|max:11',
            'CONTACT_ADDRESS' => 'required|string|max:250',
            'ATTACHED' => 'required|json',
        ]);

        if($validator->fails()){
            return $this->responseController->makeResponse(
                TRUE,
                'ERR_PERSONAL_REG001: Uno o más errores encontrados',
                $this->responseController->makeErrors($validator->errors()->messages()),
                400
            );
        }

        $form = $request->all();
        foreach($form as $k=>$v){
            if($k == 'INTERIOR_NUMBER' && $v == '0'){
                unset($form[$k]);
            }else if($v == '-'){
                unset($form[$k]);
            }
        }

        $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);
        }

        $idEmployee = $this->encryptionController->decrypt($form['id_employee']);
        if(!$idEmployee){
            return $this->responseController->makeResponse(true, 'El ID del empleado que desea actualizar no fue encriptado correctamente.', [], 400);
        }

        $employee = DB::table('S002V01TPERS')->where([
            ['PERS_NULI', '=', $form['linea']],
            ['PERS_IDPE', '=', $idEmployee]
        ])->first();
        
        if(is_null($employee)){
            return $this->responseController->makeResponse(true, 'El empleado que desea actualizar no existe.', [], 404);
        }

        $idUserReg = $this->encryptionController->decrypt($form['USER_ID']);
        if(!$idUserReg){
            return $this->responseController->makeResponse(true, 'El ID del usuario seleccionado no fue encriptado correctamente.', [], 400);
        }else if($idUser == $idUserReg){
            return $this->responseController->makeResponse(true, 'El usuario no puede registrarse a sí mismo.', [], 400);
        }
        
        $usrReg = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $form['linea']],
            ['USUA_IDUS', '=', $idUserReg]
        ])->first();
        
        if(is_null($usrReg)){
            return $this->responseController->makeResponse(true, 'El usuario seleccionado no existe.', [], 404);
        }

        $idTeam = null;
        if(isset($form['WORKTEAM_ID'])){
            $idTeam = $this->encryptionController->decrypt($form['WORKTEAM_ID']);
            if(!$idTeam){
                return $this->responseController->makeResponse(true, 'El ID del equipo seleccionado encriptado correctamente.', [], 400);
            }

            $team = DB::table('S002V01TEQMA')->where([
                ['EQMA_IDEQ', '=', $idTeam],
                ['EQMA_NULI', '=', $form['linea']],
            ])->first();
        
            if(is_null($team)){
                return $this->responseController->makeResponse(true, 'El equipo seleccionado no existe.', [], 404);
            }else if($team->EQMA_ESTA == 'Eliminado'){
                return $this->responseController->makeResponse(true, 'El equipo seleccionado está eliminado.', [], 404);
            }
        }

        $idSubcontratist = null;
        if(isset($form['SUBCONTRATIST_ID'])){
            $idSubcontratist = $this->encryptionController->decrypt($form['SUBCONTRATIST_ID']);
            if(!$idSubcontratist){
                return $this->responseController->makeResponse(true, 'El ID del subcontratista seleccionado no fue encriptado correctamente.', [], 400);
            }

            $subcontratist = DB::table('S002V01TPESU')->where([
                ['PESU_IDPS', '=', $idSubcontratist],
                ['PESU_NULI', '=', $form['linea']],
            ])->first();
        
            if(is_null($subcontratist)){
                return $this->responseController->makeResponse(true, 'El subcontratista seleccionado no existe.', [], 404);
            }
        }

        $rfcx = null;
        if(isset($form['RFC']) && $form['FOREIGNER'] == 'No' && $form['RFC'] == 'ENC_ERR'){
            return $this->responseController->makeResponse(true, 'El RFC no fue encriptado correctamente.', [], 400);
        }else if(isset($form['RFC']) && $form['RFC'] != 'ENC_ERR'){
            $rfcx = $form['RFC'];
        }

        $taid = null;
        if(isset($form['TAX']) && $form['FOREIGNER'] == 'Si' && $form['TAX'] == 'ENC_ERR'){
            return $this->responseController->makeResponse(true, 'El TAX ID no fue encriptado correctamente.', [], 400);
        }else if(isset($form['TAX']) && $form['TAX'] != 'ENC_ERR'){
            $taid =  $form['TAX'];
        }
        
        $country = DB::table('S002V01TPAIS')->where([
            ['PAIS_NULI', '=', $form['linea']],
            ['PAIS_IDPA', '=', $form['COUNTRY']],
        ])->first();
        
        if(is_null($country)){
            return $this->responseController->makeResponse(true, 'El país seleccionado no existe.', [], 404);
        }

        $colo = null;
        $ciud = null;
        $loca = null;
        if($form['COUNTRY'] == 'MEX' || $form['COUNTRY'] == 'USA' || $form['COUNTRY'] == 'CAN'){
            $state = DB::table('S002V01TESTA')->where([
                ['ESTA_NULI', '=', $form['linea']],
                ['ESTA_COES', '=', $form['FEDERAL_ENTITY']],
                ['ESTA_COPA', '=', $form['COUNTRY']],
            ])->first();
        
            if(is_null($state)){
                return $this->responseController->makeResponse(true, 'El estado seleccionado no existe.', [], 404);
            }

            if($form['COUNTRY'] == 'MEX'){
                if(isset($form['CITY'])){
                    $city = DB::table('S002V01TMUNI')->where([
                        ['MUNI_NULI', '=', $form['linea']],
                        ['MUNI_COMU', '=', $form['CITY']],
                        ['MUNI_COES', '=', $form['FEDERAL_ENTITY']],
                    ])->first();
            
                    if(is_null($city)){
                        return $this->responseController->makeResponse(true, 'El municipio seleccionado no existe.', [], 404);
                    }else{
                        $ciud = $form['CITY'];
                    }
                }

                if(isset($form['TOWN'])){
                    $town = DB::table('S002V01TLOCA')->where([
                        ['LOCA_NULI', '=', $form['linea']],
                        ['LOCA_COLO', '=', $form['TOWN']],
                        ['LOCA_COES', '=', $form['FEDERAL_ENTITY']],
                    ])->first();
            
                    if(is_null($town)){
                        return $this->responseController->makeResponse(true, 'La localidad seleccionada no existe.', [], 404);
                    }else{
                        $loca = $form['TOWN'];
                    }
                }
                
                $setting = DB::table('S002V01TCOLO')->where([
                    ['COLO_NULI', '=', $form['linea']],
                    ['COLO_COCO', '=', $form['SUBURB']],
                    ['COLO_COPO', '=', $form['POSTAL_CODE']],
                ])->first();
            
                if(is_null($setting)){
                    return $this->responseController->makeResponse(true, 'La colonia seleccionada no existe.', [], 404);
                }else{
                    $colo = $form['SUBURB'];
                }
                
                $zipCode = DB::table('S002V01TCOPO')->where([
                    ['COPO_NULI', '=', $form['linea']],
                    ['COPO_COPO', '=', $form['POSTAL_CODE']],
                    ['COPO_COES', '=', $form['FEDERAL_ENTITY']],
                ])->first();
            
                if(is_null($zipCode)){
                    return $this->responseController->makeResponse(true, 'El código postal seleccionado no existe.', [], 404);
                }
            }else{
                $colo = $form['SUBURB'];
                $ciud = $form['CITY'];
                $loca = isset($form['TOWN']) ? $form['TOWN'] : null;
            }
        }else{
            $colo = $form['SUBURB'];
            $ciud = $form['CITY'];
            $loca = isset($form['TOWN']) ? $form['TOWN'] : null;
        }

        $subArr = DB::table('S002V01TPERS')->where('PERS_NULI', '=', $form['linea'])->where(function(Builder $query) use ($form, $idUserReg) {
            $query->where('PERS_IDUS', '=', $idUserReg)
            ->orWhere('PERS_XRFC', '=', $form['RFC']);
        })->where([
            ['PERS_ESTA', '=', 'Activo'],
            ['PERS_IDPE', '!=', $idEmployee],
        ])->get()->all();

        if(count($subArr) > 0){
            return $this->responseController->makeResponse(true, 'La razón social, el RFC o el correo electrónico ya fueron registrados.', [], 401);
        }

        $ladasValues = [];
        $ladas = DB::table('S002V01TPAIS')->select([
            'PAIS_LADA AS LADA'
        ])->where('PAIS_NULI', '=', $form['linea'])->get()->all();

        foreach($ladas as $lada){
            if($lada->LADA != '' && $lada->LADA != '0'){
                $ladasValues[] = $lada->LADA;
            }
        }

        if($form['CONTACT_LADA'] == 'ENC_ERR'){
            return $this->responseController->makeResponse(true, "La lada del contacto de emergencia no fue encriptada correctamente.", [], 400);
        }else if(!in_array($form['CONTACT_LADA'], $ladasValues)){
            return $this->responseController->makeResponse(true, "La lada $form[CONTACT_LADA] no está relacionada a ningún país.", [], 400);
        }else if($form['CONTACT_TELEPHONE'] == 'ENC_ERR'){
            return $this->responseController->makeResponse(true, 'El número telefónico del contacto de emergencia no fue encriptado correctamente.', [], 400);
        }

        if($form['CONTACT_ADDRESS'] == 'ENC_ERR'){
            return $this->responseController->makeResponse(true, "La dirección del contacto de emergencia no fue encriptada correctamente.", [], 400);
        }

        if($form['CONTACT_NAME'] == 'ENC_ERR'){
            return $this->responseController->makeResponse(true, "El nombre del contacto de emergencia no fue encriptada correctamente.", [], 400);
        }

        $docsArr = json_decode($form['ATTACHED'], true);
        if(count($docsArr) == 0){
            return $this->responseController->makeResponse(true, "El arreglo de archivos adjuntos está vacío.", [], 400);
        }

        $authCont = 0;
        foreach($docsArr as $doc){
            if($doc['type'] == 'AU') $authCont++;
        }

        if($authCont < 1){
            return $this->responseController->makeResponse(true, "El arreglo de archivos adjuntos debe contener al menos un documento de autorización.", [], 400);
        }

        $finalDocsArr = [];
        foreach($docsArr as $doc){
            if(!array_key_exists('isFinalFile', $doc)){
                return $this->responseController->makeResponse(true, "La información del archivo $doc[name] no tiene un formato correcto.", [], 400);
            }

            if($doc['isFinalFile']){
                $finalDocsArr[] = [
                    'CODE' => $doc['name'],
                    'TYPE' => $doc['type']
                ];
            }else{
                $idFileDec = $this->encryptionController->decrypt($doc['id']);
                $tempFile = DB::table('S002V01TARTE')->where([
                    ['ARTE_NULI', '=', $form['linea']],
                    ['ARTE_IDAR', '=', $idFileDec],
                ])->first();
    
                if(is_null($tempFile)){
                    return $this->responseController->makeResponse(true, "El archivo $doc[name] no está registrado.", [], 404);
                }
    
                $classifications = ["AU" => "IN", "OF" => "CO", "CE" => "CE"];
                $cldo = $classifications[$doc['type']];
                $finalFile = $this->documentManagementController->moveFinalFile($form['linea'], "GPRS", $cldo, $tempFile, $idUser);
    
                if(!$finalFile[0]){
                    return $this->responseController->makeResponse(true, $finalFile[1], [], 400);
                }else{
                    $finalDocsArr[] = [
                        'CODE' => $finalFile[1],
                        'TYPE' => $doc['type']
                    ];
                }
            }
        }

        $doas = json_encode($finalDocsArr);
        $nuin = isset($form['INTERIOR_NUMBER']) ? $form['INTERIOR_NUMBER'] : null;


        $specialtiesArr = json_decode($form['SPECIALITY'], true);
        foreach($specialtiesArr as $key=>$specialtyCode){
            $specialtyCode = $this->encryptionController->decrypt($specialtyCode);
            if(!$specialtyCode){
                return $this->responseController->makeResponse(true, "El ID del elemento en la posición $key del arreglo de especialidades no fue encriptado correctamente.", [], 400);
            }

            $specialtyObj = DB::table('S002V01TGEES')->where([
                ['GEES_NULI', '=', $form['linea']],
                ['GEES_COES', '=', $specialtyCode]
            ])->first();

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

            $specialtiesArr[$key] = $specialtyCode;
        }

        $specialtiesStr = json_encode($specialtiesArr);
        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        DB::table('S002V01TPERS')->where([
            ['PERS_NULI', '=', $form['linea']],
            ['PERS_IDPE', '=', $idEmployee]
        ])->update([
            'PERS_EQTR' => $idTeam,
            'PERS_TICO' => $form['CONTRACT_TYPE'],
            'PERS_IDPS' => $idSubcontratist,
            'PERS_ESPE' => $specialtiesStr,
            'PERS_EXTR' => $form['FOREIGNER'],
            'PERS_XRFC' => $rfcx,
            'PERS_TAID' => $taid,
            'PERS_IDPA' => $form['COUNTRY'],
            'PERS_ENFE' => $form['FEDERAL_ENTITY'],
            'PERS_CIUD' => $ciud,
            'PERS_LOCA' => $loca,
            'PERS_COLO' => $colo,
            'PERS_COPO' => $form['POSTAL_CODE'],
            'PERS_CALL' => $form['STREET'],
            'PERS_NUEX' => $form['EXTERIOR_NUMBER'],
            'PERS_NUIN' => $nuin,
            'PERS_NOCE' => $form['CONTACT_NAME'],
            'PERS_LCEM' => $form['CONTACT_LADA'],
            'PERS_NUTC' => $form['CONTACT_TELEPHONE'],
            'PERS_DCEM' => $form['CONTACT_ADDRESS'],
            'PERS_DOAS' => $doas,
            'PERS_USMO' => $idUser,
            'PERS_FEMO' => $nowStr,
        ]);

        if ( !is_null($form['EMPLOYEE_BOSS']) && $form['EMPLOYEE_BOSS'] !== '' && $form['EMPLOYEE_BOSS'] !== 'null') {
            try {
                $arrCronograma = (array) DB::table('S002V01TCREM')
                    ->where('CREM_IDES', '=', $idEmployee)
                    ->where('CREM_IDEJ', '=', $form['EMPLOYEE_BOSS'])
                    ->where('CREM_NULI', '=', $form['linea'])
                    ->first([
                        'CREM_IDES AS ID_SUBORDINADO',
                        'CREM_IDEJ AS IS_JEFE',
                        'CREM_ESTA AS ESTADO',
                    ]);
                } catch (\Throwable $th) {
                    return $this->responseController->makeResponse(true, "Ocurrió un error al obtener el cronograma.", [], 400);
                }
            if ( count($arrCronograma) === 0 ) {
                try {
                    $validateRegister = DB::table('S002V01TCREM')->insert([
                        'CREM_IDES' => $idEmployee,
                        'CREM_IDEJ' => $form['EMPLOYEE_BOSS'],
                        'CREM_NULI' => $form['linea'],
                        'CREM_USRE' => $idUser,
                        'CREM_FERE' => $nowStr,
                        'CREM_FEAR' => DB::raw('CURRENT_TIMESTAMP'),
                    ]);
                } catch (Throwable $th) {
                    return $this->responseController->makeResponse(true, "Ocurrió un error al registrar el cronograma.", [], 400);
                }
                if (!$validateRegister) {
                    return $this->responseController->makeResponse(true, "No se pudo registrar los empleados en el cronograma.", [], 400);
                }
            } else {
                try {
                    $validateUpdate = DB::table('S002V01TCREM')
                        ->where('CREM_IDES', '=', $idEmployee)
                        ->where('CREM_IDEJ', '=', $form['EMPLOYEE_BOSS'])
                        ->where('CREM_NULI', '=', $form['linea'])
                        ->update([
                            'CREM_ESTA' => 'Activo',
                            'CREM_USMO' => $idUser,
                            'CREM_FEMO' => $nowStr,
                            'CREM_FEAR' => DB::raw('CURRENT_TIMESTAMP'),
                        ]);
                    } catch (\Throwable $th) {
                        return $this->responseController->makeResponse(true, "Ocurrió un error al modificar el cronograma.", [], 400);
                    }
                if (!$validateUpdate) {
                    return $this->responseController->makeResponse(true, "No se pudo modificar los empleados en el cronograma.", [], 400);
                }
            }
        }

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        $nameEmp = $this->functionsController->joinName($usrReg->USUA_NOMB, $usrReg->USUA_APPA, $usrReg->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M11GPRS',
            'S002V01F01GEPE',
            'S002V01P02REEM',
            'Actualización',
            "El usuario $name (" . $usr->USUA_IDUS . ") actualizó al empleado " . $nameEmp . "($idEmployee).",
            $idUser,
            $nowStr,
            'S002V01S02GEPE'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO');
    }
    public function getScheduleActivitiesByEmployee($id, $user, $line) {
        try {
            $idEmployee = $this->encryptionController->decrypt($id);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_GETACTIVITY000: Ocurrió un error al ibtener los datos.", $th->getMessage(), 404);
        }
        try {
            $getSheduleActivities = DB::table('S002V01TCOAC')
                ->where('COAC_NULI','=',$line)
                ->where('COAC_ESTA','=','Activo')
                ->where('COAC_IDPE','=',$idEmployee)
                ->get([
                    'COAC_IDAC AS ID_ACTIVIDAD',
                    'COAC_IDPE AS ID_EMPLEADO',
                    'COAC_ACTI AS ACTIVIDAD',
                    'COAC_DESC AS DESCRIPCION',
                    'COAC_TIPO AS TIPO_ACTIVIDAD',
                    'COAC_OTPR AS ID_ORDEN_PREVENTIVO',
                    'COAC_OTCO AS ID_ORDEN_CORRECTIVO',
                    'COAC_FEIN AS FECHA_INICIO',
                    'COAC_HOIN AS HORA_INICIO',
                    'COAC_FEFE AS FECHA_FINAL',
                    'COAC_HOFE AS HORA_FINAL',
                    'COAC_FEFR AS FECHA_FINAL_REAL',
                    'COAC_HOFR AS HORA_FINAL_REAL',
                    'COAC_PERI AS PERIODICIDAD',
                    'COAC_PEPE AS PERIODICIDAD_PERSONALIZADA',
                    'COAC_CLAS AS CLASIFICACION',
                    'COAC_COLO AS COLOR',
                    'COAC_ESTA AS ESTADO',
                    'COAC_FERE AS FECHA_REGISTRA',
                    'COAC_USRE AS USUARIO_REGISTRA',
                    'COAC_FEMO AS FECHA_MODIFICA',
                    'COAC_USMO AS USUARIO_MODIFICA',
                ]);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_GETACTIVITY001: Ocurrió un error al ibtener los datos.", $th->getMessage(), 404);
        }

        $arrSheduleActivities = json_decode(json_encode($getSheduleActivities), true);

        return $this->responseController->makeResponse(false, "ÉXITO: Consulta Exitosa", $arrSheduleActivities);
    }
    public function registerActivity(Request $request) {
        $valitador = Validator::make($request->all(), [
            'ID_EMPLEADO' => 'required|string',
            'ACTIVIDAD' => 'required|string',
            'CLASIFICACION' => 'required|string',
            'DESCRIPCION' => 'required|string',
            'TIPO_ACTIVIDAD' => 'required|string',
            'ID_ORDEN' => 'nullable|string',
            'PERIODICIDAD' => 'nullable|string',
            'FECHA_INICIO' => 'required|string',
            'HORA_INICIO' => 'required|string',
            'FECHA_FINAL' => 'nullable|string',
            'HORA_FINAL' => 'nullable|string',
            'PERIODICIDAD_PERSONALIZADA' => 'nullable|string',
            'COLOR' => 'required|string',
            'USUARIO' => 'required|string',
            'NUMERO_LINEA' => 'required|integer',
        ]);
        if ($valitador->fails()) {
            return $this->responseController->makeResponse(
                true,
                "ERR_EMPLOYEE_REGACTIVITY000: Se encontraron uno o más errores.",
                $this->responseController->makeErrors($valitador->errors()->messages()),
                401
            );
        }

        DB::beginTransaction();
        $requestData = $request->all();

        try {
            $user = $this->encryptionController->decrypt($requestData['USUARIO']);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_REGACTIVITY001: Ocurrió un error al desencriptar los datos.", $th->getMessage(), 401);
        }
        try {
            $validateUser = DB::table('S002V01TUSUA')
                ->where('USUA_NULI', '=', $requestData['NUMERO_LINEA'])
                ->where('USUA_IDUS', '=', $user)
                ->exists();
        } catch(\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_REGACTIVITY002: Ocurrió al verificar la existencia del usuario.", $th->getMessage(), 404);
        }
        if (!$validateUser) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_REGACTIVITY003: El usuario no existe.", [], 404);
        }
        
        $idPreventiveMaintenance = null;
        $idCorrectiveMaintenance = null;
        
        if ($requestData['TIPO_ACTIVIDAD'] === 'MP') {

            if (is_null($requestData['ID_ORDEN'])) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_REGACTIVITY004: El ID de la orden de mantenimiento no viene incluida en los datos del formulario.", [], 404);
            }
            try {
                $validateMaintenance = DB::table('S002V01TOTPR')
                    ->where('OTPR_IDOT', '=', $requestData['ID_ORDEN'])
                    ->where('OTPR_NULI', '=', $requestData['NUMERO_LINEA'])
                    ->exists();
            } catch (\Throwable $th) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_REGACTIVITY005: Ocurrió un error al validar la orden de mantenimiento.", $th->getMessage(), 404);
            }
            if (!$validateMaintenance) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_REGACTIVITY006: La orden de trabajo no existe en la lista de mantenimientos preventivos.", [], 404);
            }

            $idPreventiveMaintenance = $requestData['ID_ORDEN'];
        } else if ($requestData['TIPO_ACTIVIDAD'] === 'MC') {

            if (is_null($requestData['ID_ORDEN'])) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_REGACTIVITY007: El ID de la orden de mantenimiento no viene incluida en los datos del formulario.", [], 404);
            }

            try {
                $validateMaintenance = DB::table('S002V01TOTCO')
                    ->where('OTCO_IDOT ', '=', $requestData['ID_ORDEN'])
                    ->where('OTCO_NULI ', '=', $requestData['NUMERO_LINEA'])
                    ->exists();
            } catch (\Throwable $th) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_REGACTIVITY008: Ocurrió un error al validar la orden de mantenimiento.", $th->getMessage(), 404);
            }
            if (!$validateMaintenance) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_REGACTIVITY009: La orden de trabajo no existe en la lista de mantenimientos correctivo.", [], 404);
            }

            $idCorrectiveMaintenance = $requestData['ID_ORDEN'];
        } else if ($requestData['TIPO_ACTIVIDAD'] === 'NA' && is_null($requestData['ID_ORDEN']) && !is_null($requestData['PERIODICIDAD']) ) {

            if ($requestData['PERIODICIDAD'] === 'PE' && is_null($requestData['PERIODICIDAD_PERSONALIZADA'])) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_REGACTIVITY010: El tipo de periodicidad personalizada debe estar incluida si el tipo de periodicidad está selecciona como Personalizada.", [], 404);
            }

            if ($requestData['PERIODICIDAD'] === 'NR' && (is_null($requestData['FECHA_FINAL']) | is_null($requestData['HORA_FINAL'])) ) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_REGACTIVITY011: La fecha y la hora final esperada debe estar incluida en el formulario si la periodicidad está seleccionada como No se repite.", [], 404);
            }

        } else {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_REGACTIVITY012: El tipo de actividad no es correcto.", [], 404);
        }

        $now = $this->functionsController->now();
        $currentDate = $now->toDateTimeString();

        try {
            $validateInsert = DB::table('S002V01TCOAC')->insert([
                'COAC_NULI' => $requestData['NUMERO_LINEA'],
                'COAC_IDPE' => $requestData['ID_EMPLEADO'],
                'COAC_ACTI' => $requestData['ACTIVIDAD'],
                'COAC_DESC' => $requestData['DESCRIPCION'],
                'COAC_TIPO' => $requestData['TIPO_ACTIVIDAD'],
                'COAC_OTPR' => $idPreventiveMaintenance,
                'COAC_OTCO' => $idCorrectiveMaintenance,
                'COAC_FEIN' => $requestData['FECHA_INICIO'],
                'COAC_HOIN' => $requestData['HORA_INICIO'],
                'COAC_FEFE' => $requestData['FECHA_FINAL'],
                'COAC_HOFE' => $requestData['HORA_FINAL'],
                // 'COAC_FEFR' => $requestData[''],
                // 'COAC_HOFR' => $requestData[''],
                'COAC_PERI' => $requestData['PERIODICIDAD'],
                'COAC_PEPE' => $requestData['PERIODICIDAD_PERSONALIZADA'],
                'COAC_CLAS' => $requestData['CLASIFICACION'],
                'COAC_COLO' => $requestData['COLOR'],
                'COAC_FERE' => $currentDate,
                'COAC_USRE' => $user,
                'COAC_FEAR' => DB::raw('CURRENT_TIMESTAMP'),
            ]);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_REGACTIVITY013: Ocurrió un error al insertar los datos.", $th->getMessage(), 404);
        }
        if (!$validateInsert) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_REGACTIVITY014: No se pudo insertar los datos en la base de datos.", [], 404);
        }

        DB::commit();
        return $this->responseController->makeResponse(false, "ÉXITO: Registro Exitoso");

    }
    public function updateActivity(Request $request, $idActivity) {
        $valitador = Validator::make($request->all(), [
            // 'ID_ACTIVIDAD' => 'required|integer',
            'ID_EMPLEADO' => 'required|string',
            'ACTIVIDAD' => 'required|string',
            'CLASIFICACION' => 'required|string',
            'DESCRIPCION' => 'required|string',
            'TIPO_ACTIVIDAD' => 'required|string',
            'ID_ORDEN' => 'nullable|string',
            'PERIODICIDAD' => 'nullable|string',
            'FECHA_INICIO' => 'required|string',
            'HORA_INICIO' => 'required|string',
            'FECHA_FINAL' => 'nullable|string',
            'HORA_FINAL' => 'nullable|string',
            'PERIODICIDAD_PERSONALIZADA' => 'nullable|string',
            'COLOR' => 'required|string',
            'USUARIO' => 'required|string',
            'NUMERO_LINEA' => 'required|integer',
        ]);
        if ($valitador->fails()) {
            return $this->responseController->makeResponse(
                true,
                "ERR_EMPLOYEE_UPDACTIVITY000: Se encontraron uno o más errores.",
                $this->responseController->makeErrors($valitador->errors()->messages()),
                401
            );
        }

        DB::beginTransaction();
        $requestData = $request->all();

        try {
            $idActivity = $this->encryptionController->decrypt($idActivity);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_UPDACTIVITY001: Ocurrió un error al desencriptar los datos de la actividad.", $th->getMessage(), 401);
        }

        try {
            $user = $this->encryptionController->decrypt($requestData['USUARIO']);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_UPDACTIVITY002: Ocurrió un error al desencriptar los datos.", $th->getMessage(), 401);
        }
        try {
            $validateUser = DB::table('S002V01TUSUA')
                ->where('USUA_NULI', '=', $requestData['NUMERO_LINEA'])
                ->where('USUA_IDUS', '=', $user)
                ->exists();
        } catch(\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_UPDACTIVITY003: Ocurrió al verificar la existencia del usuario.", $th->getMessage(), 404);
        }
        if (!$validateUser) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_UPDACTIVITY004: El usuario no existe.", [], 404);
        }

        $idPreventiveMaintenance = null;
        $idCorrectiveMaintenance = null;

        if ($requestData['TIPO_ACTIVIDAD'] === 'MP') {

            if (is_null($requestData['ID_ORDEN'])) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_UPDACTIVITY005: El ID de la orden de mantenimiento no viene incluida en los datos del formulario.", [], 404);
            }
            try {
                $validateMaintenance = DB::table('S002V01TOTPR')
                    ->where('OTPR_IDOT', '=', $requestData['ID_ORDEN'])
                    ->where('OTPR_NULI', '=', $requestData['NUMERO_LINEA'])
                    ->exists();
            } catch (\Throwable $th) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_UPDACTIVITY006: Ocurrió un error al validar la orden de mantenimiento.", $th->getMessage(), 404);
            }
            if (!$validateMaintenance) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_UPDACTIVITY007: La orden de trabajo no existe en la lista de mantenimientos preventivos.", [], 404);
            }

            $idPreventiveMaintenance = $requestData['ID_ORDEN'];
        } else if ($requestData['TIPO_ACTIVIDAD'] === 'MC') {

            if (is_null($requestData['ID_ORDEN'])) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_UPDACTIVITY008: El ID de la orden de mantenimiento no viene incluida en los datos del formulario.", [], 404);
            }

            try {
                $validateMaintenance = DB::table('S002V01TOTCO')
                    ->where('OTCO_IDOT ', '=', $requestData['ID_ORDEN'])
                    ->where('OTCO_NULI ', '=', $requestData['NUMERO_LINEA'])
                    ->exists();
            } catch (\Throwable $th) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_UPDACTIVITY009: Ocurrió un error al validar la orden de mantenimiento.", $th->getMessage(), 404);
            }
            if (!$validateMaintenance) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_UPDACTIVITY010: La orden de trabajo no existe en la lista de mantenimientos correctivo.", [], 404);
            }

            $idCorrectiveMaintenance = $requestData['ID_ORDEN'];
        } else if ($requestData['TIPO_ACTIVIDAD'] === 'NA' && is_null($requestData['ID_ORDEN']) && !is_null($requestData['PERIODICIDAD']) ) {

            if ($requestData['PERIODICIDAD'] === 'PE' && is_null($requestData['PERIODICIDAD_PERSONALIZADA'])) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_UPDACTIVITY011: El tipo de periodicidad personalizada debe estar incluida si el tipo de periodicidad está selecciona como Personalizada.", [], 404);
            }

            if ($requestData['PERIODICIDAD'] === 'NR' && (is_null($requestData['FECHA_FINAL']) | is_null($requestData['HORA_FINAL'])) ) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_UPDACTIVITY012: La fecha y la hora final esperada debe estar incluida en el formulario si la periodicidad está seleccionada como No se repite.", [], 404);
            }

        } else {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_UPDACTIVITY013: El tipo de actividad no es correcto.", [], 404);
        }

        $now = $this->functionsController->now();
        $currentDate = $now->toDateTimeString();

        try {
            $validateInsert = DB::table('S002V01TCOAC')
                ->where('COAC_IDAC','=',$idActivity)
                ->where('COAC_NULI','=',$requestData['NUMERO_LINEA'])
                ->where('COAC_IDPE','=',$requestData['ID_EMPLEADO'])
                ->update([
                    'COAC_ACTI' => $requestData['ACTIVIDAD'],
                    'COAC_DESC' => $requestData['DESCRIPCION'],
                    'COAC_TIPO' => $requestData['TIPO_ACTIVIDAD'],
                    'COAC_OTPR' => $idPreventiveMaintenance,
                    'COAC_OTCO' => $idCorrectiveMaintenance,
                    'COAC_FEIN' => $requestData['FECHA_INICIO'],
                    'COAC_HOIN' => $requestData['HORA_INICIO'],
                    'COAC_FEFE' => $requestData['FECHA_FINAL'],
                    'COAC_HOFE' => $requestData['HORA_FINAL'],
                    // 'COAC_FEFR' => $requestData[''],
                    // 'COAC_HOFR' => $requestData[''],
                    'COAC_PERI' => $requestData['PERIODICIDAD'],
                    'COAC_PEPE' => $requestData['PERIODICIDAD_PERSONALIZADA'],
                    'COAC_CLAS' => $requestData['CLASIFICACION'],
                    'COAC_COLO' => $requestData['COLOR'],
                    'COAC_FEMO' => $currentDate,
                    'COAC_USMO' => $user,
                    'COAC_FEAR' => DB::raw('CURRENT_TIMESTAMP'),
                ]);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_UPDACTIVITY014: Ocurrió un error al insertar los datos.", $th->getMessage(), 404);
        }
        if (!$validateInsert) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_UPDACTIVITY015: No se pudo insertar los datos en la base de datos.", [], 404);
        }

        DB::commit();
        return $this->responseController->makeResponse(false, "ÉXITO: Modificación Exitosa");
    }
    public function deleteActivity(Request $request, $idActivity) {
        $valitador = Validator::make($request->all(), [
            'USUARIO' => 'required|string',
            'NUMERO_LINEA' => 'required|integer',
        ]);
        if ($valitador->fails()) {
            return $this->responseController->makeResponse(
                true,
                "ERR_EMPLOYEE_DELACTIVITY000: Se encontraron uno o más errores.",
                $this->responseController->makeErrors($valitador->errors()->messages()),
                401
            );
        }

        DB::beginTransaction();
        $requestData = $request->all();

        try {
            $idActivity = $this->encryptionController->decrypt($idActivity);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_DELACTIVITY001: Ocurrió un error al desencriptar los datos.", $th->getMessage(), 401);
        }

        try {
            $user = $this->encryptionController->decrypt($requestData['USUARIO']);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_DELACTIVITY002: Ocurrió un error al desencriptar los datos.", $th->getMessage(), 401);
        }

        $now = $this->functionsController->now();
        $currentDate = $now->toDateTimeString();

        try {
            $validateDelete = DB::table('S002V01TCOAC')
                ->where('COAC_NULI', '=', $requestData['NUMERO_LINEA'])
                ->where('COAC_IDAC', '=', $idActivity)
                ->update([
                    'COAC_ESTA' => 'Eliminado',
                    'COAC_FEMO' => $currentDate,
                    'COAC_USMO' => $user,
                    'COAC_FEAR' => DB::raw('CURRENT_TIMESTAMP'),
                ]);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_DELACTIVITY003: Ocurrió un error al eliminar la actividad.", $th->getMessage(), 401);
        }

        if (!$validateDelete) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_DELACTIVITY004: No se pudo eliminar la actividad.", [], 401);
        }

        DB::commit();
        return $this->responseController->makeResponse(false, "ÉXITO: Modificación Exitosa");
    }
    public function getScheduleActivitiesByActivity($id, $user, $line) {
        try {
            $idActivity = $this->encryptionController->decrypt($id);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_GETACTIVITY000: Ocurrió un error al ibtener los datos.", $th->getMessage(), 404);
        }
        try {
            $arrSheduleActivities = (array) DB::table('S002V01TCOAC')
                ->where('COAC_NULI','=',$line)
                ->where('COAC_ESTA','=','Activo')
                ->where('COAC_IDAC','=',$idActivity)
                ->first([
                    'COAC_IDPE AS ID_EMPLEADO',
                    'COAC_ACTI AS ACTIVIDAD',
                    'COAC_DESC AS DESCRIPCION',
                    'COAC_TIPO AS TIPO_ACTIVIDAD',
                    'COAC_OTPR AS ID_ORDEN_PREVENTIVO',
                    'COAC_OTCO AS ID_ORDEN_CORRECTIVO',
                    'COAC_FEIN AS FECHA_INICIO',
                    'COAC_HOIN AS HORA_INICIO',
                    'COAC_FEFE AS FECHA_FINAL',
                    'COAC_HOFE AS HORA_FINAL',
                    'COAC_FEFR AS FECHA_FINAL_REAL',
                    'COAC_HOFR AS HORA_FINAL_REAL',
                    'COAC_PERI AS PERIODICIDAD',
                    'COAC_PEPE AS PERIODICIDAD_PERSONALIZADA',
                    'COAC_CLAS AS CLASIFICACION',
                    'COAC_COLO AS COLOR',
                    'COAC_ESTA AS ESTADO',
                    'COAC_FERE AS FECHA_REGISTRA',
                    'COAC_USRE AS USUARIO_REGISTRA',
                    'COAC_FEMO AS FECHA_MODIFICA',
                    'COAC_USMO AS USUARIO_MODIFICA',
                ]);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_EMPLOYEE_GETACTIVITY001: Ocurrió un error al ibtener los datos.", $th->getMessage(), 404);
        }

        return $this->responseController->makeResponse(false, "ÉXITO: Consulta Exitosa", $arrSheduleActivities);
    }


    public function getConsultOfSubcontratists($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);
        }

        $subcontratists = DB::table('S002V01TPESU')->select([
            'PESU_IDPS as ID_SUBCONTRATIST',
            DB::raw('CONCAT(PESU_RASO, " (" , COALESCE(PESU_REFI, ""), ")") AS NAME'),
            'PESU_EXTR AS TIPO',
            'PESU_ESPE AS SPECIALTY',
            'PESU_FERE AS REGISTER_DATE',
            'PESU_FEMO AS UPDATE_DATE',
            'PESU_USRE AS REGISTERED_BY_USER',
            'PESU_USMO AS UPDATED_BY_USER',
            'PESU_ESTA AS STATUS'
        ])->where('PESU_NULI', '=', $line)->get()->all();

        foreach($subcontratists as $subcontratist){
            $regUsr = DB::table('S002V01TUSUA')->where([
                ['USUA_NULI', '=', $line],
                ['USUA_IDUS', '=', $subcontratist->REGISTERED_BY_USER],
            ])->first();

            if(!is_null($regUsr)){
                $subcontratist->REGISTERED_BY_USER = $this->functionsController->joinName($regUsr->USUA_NOMB, $regUsr->USUA_APPA, $regUsr->USUA_APMA) . " (" . $subcontratist->REGISTERED_BY_USER . ")";
            }else{
                $subcontratist->REGISTERED_BY_USER = "DESCONOCIDO (" . $subcontratist->REGISTERED_BY_USER . ")";
            }
            
            if(!is_null($subcontratist->UPDATED_BY_USER)){
                $modUsr = DB::table('S002V01TUSUA')->where([
                    ['USUA_NULI', '=', $line],
                    ['USUA_IDUS', '=', $subcontratist->UPDATED_BY_USER],
                ])->first();
    
                if(!is_null($modUsr)){
                    $subcontratist->UPDATED_BY_USER = $this->functionsController->joinName($modUsr->USUA_NOMB, $modUsr->USUA_APPA, $modUsr->USUA_APMA) . " (" . $subcontratist->UPDATED_BY_USER . ")";
                }else{
                    $subcontratist->UPDATED_BY_USER = "DESCONOCIDO (" . $subcontratist->UPDATED_BY_USER . ")";
                }
            }

            if($subcontratist->TIPO == 'No'){
                $subcontratist->TIPO = 'Nacional';
            }else{
                $subcontratist->TIPO = 'Extranjero';
            }

            $preventiveInterventions = DB::select("
            SELECT 
                DISTINCT(S002V01TOTPR.OTPR_IDOT), 
                OPPR.*
            FROM 
                S002V01TOTPR, 
                JSON_TABLE(OTPR_OPPR, '$[*]' COLUMNS(
                    OTPR_OPPR_ID INT PATH '$.ID', 
                    OTPR_OPPR_TYPE VARCHAR(100) PATH '$.TYPE'
                )) OPPR 
            WHERE 
                OPPR.OTPR_OPPR_TYPE = :emp_type
                AND OPPR.OTPR_OPPR_ID = :emp_id
            ", ['emp_type' => 'SU', 'emp_id' => $subcontratist->ID_SUBCONTRATIST]);
            //PENDIENTE IMPLEMENTAR INTERVENCIONES DE MANTENIMIENTO CORRECTIVO
            $subcontratist->INTERVENTIONS_COUNT = count($preventiveInterventions);
            $subcontratist->ID_SUBCONTRATIST = $this->encryptionController->encrypt($subcontratist->ID_SUBCONTRATIST);
        }

        $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,
            'S002V01M11GPRS',
            'S002V01F01GESU',
            'S002V01P01COSU',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó los subcontratistas registrados.",
            $idUser,
            $nowStr,
            'S002V01S01GESU'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $subcontratists);
    }
    public function storeSubcontratist(Request $request) {
        DB::enableQueryLog();

        $request['TELEPHONE1'] = $this->encryptionController->decrypt($request->TELEPHONE1) ? $this->encryptionController->decrypt($request->TELEPHONE1) : 'ENC_ERR';
        $request['TELEPHONE2'] = $this->encryptionController->decrypt($request->TELEPHONE2) ? $this->encryptionController->decrypt($request->TELEPHONE2) : 'ENC_ERR';
        $request['EMAIL'] = $this->encryptionController->decrypt($request->EMAIL) ? $this->encryptionController->decrypt($request->EMAIL) : 'ENC_ERR';
        if ( !is_null($request->RFC) ) {
            $request['RFC'] = $this->encryptionController->decrypt($request->RFC) ? $this->encryptionController->decrypt($request->RFC) : 'ENC_ERR';
        }
        if ( !is_null($request->TAX) ) {
            $request['TAX'] = $this->encryptionController->decrypt($request->TAX) ? $this->encryptionController->decrypt($request->TAX) : 'ENC_ERR';
        }

        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'SOCIAL_REASON' => 'required|string|max:150',
            'TAX_REFERENCE' => 'required|string|max:15',
            'CONTRACT_TYPE' => 'required|string|in:Persona moral,Persona física',
            'FOREIGNER' => 'required|string|in:Si,No',
            'RFC' => 'required_if:FOREIGNER,=,No',
            'TAX' => 'required_if:FOREIGNER,=,Si',
            'EMAIL' => 'required|string|max:150',
            'COUNTRY' => 'required|string|max:75',
            'FEDERAL_ENTITY' => 'required|string|max:75',
            'CITY' => 'string|max:75',
            'TOWN' => 'string|max:75',
            'SUBURB' => 'required|string|max:75',
            'POSTAL_CODE' => 'required|string|max:5',
            'STREET' => 'required|string|max:150',
            'EXTERIOR_NUMBER' => 'required|string',
            'INTERIOR_NUMBER' => 'string',
            'LADA1' => 'required|string|max:15',
            'TELEPHONE1' => 'required|string|min:7|max:11',
            'LADA2' => 'string|max:15',
            'TELEPHONE2' => 'string|min:7|max:11',
            'SPECIALTY' => 'required|json',
            'PROVIDER' => 'string|max:10'
        ]);

        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();
        foreach($form as $k=>$v){
            if($k == 'INTERIOR_NUMBER' && $v == '0'){
                unset($form[$k]);
            }else if($v == '-'){
                unset($form[$k]);
            }
        }

        $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);
        }

        $ladasValues = [];
        $ladas = DB::table('S002V01TPAIS')->select([
            'PAIS_LADA AS LADA'
        ])->where('PAIS_NULI', '=', $form['linea'])->get()->all();

        foreach($ladas as $lada){
            if($lada->LADA != '' && $lada->LADA != '0'){
                $ladasValues[] = $lada->LADA;
            }
        }

        if(!in_array($form['LADA1'], $ladasValues)){
            return $this->responseController->makeResponse(true, "La lada $form[LADA1] no está relacionada a ningún país.", [], 400);
        }else if($form['TELEPHONE1'] == 'ENC_ERR'){
            return $this->responseController->makeResponse(true, 'El número telefónico 1 no fue encriptado correctamente.', [], 400);
        }

        $lada2 = null;
        $phone2 = null;
        if(isset($form['LADA2'])){
            if(!in_array($form['LADA2'], $ladasValues)){
                return $this->responseController->makeResponse(true, "La lada $form[LADA2] no está relacionada a ningún país.", [], 400);
            }else if($form['TELEPHONE2'] == 'ENC_ERR'){
                return $this->responseController->makeResponse(true, 'El número telefónico 2 no fue encriptado correctamente.', [], 400);
            }

            $lada2 = $form['LADA2'];
            $phone2 = $form['TELEPHONE2'];
        }

        if($form['EMAIL'] == 'ENC_ERR'){
            return $this->responseController->makeResponse(true, 'El email no fue encriptado correctamente.', [], 400);
        }

        $rfcx = null;
        if(isset($form['RFC']) && $form['FOREIGNER'] == 'No' && $form['RFC'] == 'ENC_ERR'){
            return $this->responseController->makeResponse(true, 'El RFC no fue encriptado correctamente.', [], 400);
        }else if(isset($form['RFC']) && $form['RFC'] != 'ENC_ERR'){
            $rfcx = $form['RFC'];
        }

        $taid = null;
        if(isset($form['TAX']) && $form['FOREIGNER'] == 'Si' && $form['TAX'] == 'ENC_ERR'){
            return $this->responseController->makeResponse(true, 'El TAX ID no fue encriptado correctamente.', [], 400);
        }else if(isset($form['TAX']) && $form['TAX'] != 'ENC_ERR'){
            $taid =  $form['TAX'];
        }
        
        $country = DB::table('S002V01TPAIS')->where([
            ['PAIS_NULI', '=', $form['linea']],
            ['PAIS_IDPA', '=', $form['COUNTRY']],
        ])->first();
        
        if(is_null($country)){
            return $this->responseController->makeResponse(true, 'El país seleccionado no existe.', [], 404);
        }
        
        $taxRegime = DB::table('S002V01TREFI')->where([
            ['REFI_NULI', '=', $form['linea']],
            ['REFI_CRFI', '=', $form['TAX_REFERENCE']],
        ])->first();
        
        if(is_null($taxRegime)){
            return $this->responseController->makeResponse(true, 'El régimen fiscal seleccionado no existe.', [], 404);
        }

        $colo = null;
        $ciud = null;
        $loca = null;
        if($form['COUNTRY'] == 'MEX' || $form['COUNTRY'] == 'USA' || $form['COUNTRY'] == 'CAN'){
            $state = DB::table('S002V01TESTA')->where([
                ['ESTA_NULI', '=', $form['linea']],
                ['ESTA_COES', '=', $form['FEDERAL_ENTITY']],
                ['ESTA_COPA', '=', $form['COUNTRY']],
            ])->first();
        
            if(is_null($state)){
                return $this->responseController->makeResponse(true, 'El estado seleccionado no existe.', [], 404);
            }

            if($form['COUNTRY'] == 'MEX'){
                if(isset($form['CITY'])){
                    $city = DB::table('S002V01TMUNI')->where([
                        ['MUNI_NULI', '=', $form['linea']],
                        ['MUNI_COMU', '=', $form['CITY']],
                        ['MUNI_COES', '=', $form['FEDERAL_ENTITY']],
                    ])->first();
            
                    if(is_null($city)){
                        return $this->responseController->makeResponse(true, 'El municipio seleccionado no existe.', [], 404);
                    }else{
                        $ciud = $form['CITY'];
                    }
                }

                if(isset($form['TOWN'])){
                    $town = DB::table('S002V01TLOCA')->where([
                        ['LOCA_NULI', '=', $form['linea']],
                        ['LOCA_COLO', '=', $form['TOWN']],
                        ['LOCA_COES', '=', $form['FEDERAL_ENTITY']],
                    ])->first();
            
                    if(is_null($town)){
                        return $this->responseController->makeResponse(true, 'La localidad seleccionada no existe.', [], 404);
                    }else{
                        $loca = $form['TOWN'];
                    }
                }
                
                if ($form['FOREIGNER'] === 'No') {
                    $setting = DB::table('S002V01TCOLO')->where([
                        ['COLO_NULI', '=', $form['linea']],
                        ['COLO_COCO', '=', $form['SUBURB']],
                        ['COLO_COPO', '=', $form['POSTAL_CODE']],
                    ])->first();
                
                    if(is_null($setting)){
                        return $this->responseController->makeResponse(true, 'La colonia seleccionada no existe.', [], 404);
                    }else{
                        $colo = $form['SUBURB'];
                    }
                } else {
                    $colo = $form['SUBURB'];
                }
                
                
                $zipCode = DB::table('S002V01TCOPO')->where([
                    ['COPO_NULI', '=', $form['linea']],
                    ['COPO_COPO', '=', $form['POSTAL_CODE']],
                    ['COPO_COES', '=', $form['FEDERAL_ENTITY']],
                ])->first();
            
                if(is_null($zipCode)){
                    return $this->responseController->makeResponse(true, 'El código postal seleccionado no existe.', [], 404);
                }
            }else{
                $colo = $form['SUBURB'];
                $ciud = $form['CITY'];
                $loca = isset($form['TOWN']) ? $form['TOWN'] : null;
            }
        }else{
            $colo = $form['SUBURB'];
            $ciud = $form['CITY'];
            $loca = isset($form['TOWN']) ? $form['TOWN'] : null;
        }

        if (!is_null($form['RFC'])) {
            $subArr = DB::table('S002V01TPESU')->where('PESU_NULI', '=', $form['linea'])->where(function(Builder $query) use ($form) {
                $query->where('PESU_RASO', '=', $form['SOCIAL_REASON'])
                ->orWhere('PESU_XRFC', '=', $form['RFC'])
                ->orWhere('PESU_CORR', '=', $form['EMAIL']);
            })->where('PESU_ESTA', '=', 'Activo')->get()->all();

            if(count($subArr) > 0){
                return $this->responseController->makeResponse(true, 'La razón social, el RFC o el correo electrónico ya fueron registrados. 1', [], 401);
            }
        }
        if (!is_null($form['TAX'])) {
            $subArr = DB::table('S002V01TPESU')->where('PESU_NULI', '=', $form['linea'])->where(function(Builder $query) use ($form) {
                $query->where('PESU_RASO', '=', $form['SOCIAL_REASON'])
                ->orWhere('PESU_TAID', '=', $form['TAX'])
                ->orWhere('PESU_CORR', '=', $form['EMAIL']);
            })->where('PESU_ESTA', '=', 'Activo')->get()->all();
            if(count($subArr) > 0){
                return $this->responseController->makeResponse(true, 'La razón social, el Tax ID o el correo electrónico ya fueron registrados. 2', [], 401);
            }
        }

        $idProvider = null;
        if ( array_key_exists('PROVIDER', $form) ) {
            $idProvider = $form['PROVIDER'];

            try {
                $validateExist = DB::table('S002V01TPROV')
                    ->where('PROV_NUPR', '=', $idProvider)
                    ->where('PROV_NULI', '=', $form['linea'])
                    ->exists();
            } catch (\Throwable $th) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "Ocurrió un error al obtener el proveedor.", $th->getMessage(), 500);
            }

            if (!$validateExist) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "El número del proveedor no existe.", [], 500);
            }
        }

        $specialtiesArr = json_decode($form['SPECIALTY'], true);
        foreach($specialtiesArr as $key=>$specialtyCode){
            $specialtyCode = $this->encryptionController->decrypt($specialtyCode);
            if(!$specialtyCode){
                return $this->responseController->makeResponse(true, "El ID del elemento en la posición $key del arreglo de especialidades no fue encriptado correctamente.", [], 400);
            }

            $specialtyObj = DB::table('S002V01TGEES')->where([
                ['GEES_NULI', '=', $form['linea']],
                ['GEES_COES', '=', $specialtyCode]
            ])->first();

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

            $specialtiesArr[$key] = $specialtyCode;
        }

        $specialtiesStr = json_encode($specialtiesArr);
        $nuin = isset($form['INTERIOR_NUMBER']) ? $form['INTERIOR_NUMBER'] : null;
        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $subcontratistID = DB::table('S002V01TPESU')->insertGetId([
            'PESU_NULI' => $form['linea'],
            'PESU_NUPR' => $idProvider,
            'PESU_RASO' => $form['SOCIAL_REASON'],
            'PESU_REFI' => $form['TAX_REFERENCE'],
            'PESU_XRFC' => $rfcx === '' ? null : $rfcx,
            'PESU_TIPO' => $form['CONTRACT_TYPE'],
            'PESU_CORR' => $form['EMAIL'],
            'PESU_EXTR' => $form['FOREIGNER'],
            'PESU_TAID' => $taid === '' ? null : $taid,
            'PESU_CALL' => $form['STREET'],
            'PESU_NUEX' => $form['EXTERIOR_NUMBER'],
            'PESU_NUIN' => $nuin,
            'PESU_COLO' => $colo,
            'PESU_CIUD' => $ciud,
            'PESU_LOCA' => $loca,
            'PESU_COPO' => $form['POSTAL_CODE'],
            'PESU_ENFE' => $form['FEDERAL_ENTITY'],
            'PESU_IDPA' => $form['COUNTRY'],
            'PESU_TEL1' => $form['TELEPHONE1'],
            'PESU_LAT1' => $form['LADA1'],
            'PESU_TEL2' => $phone2,
            'PESU_LAT2' => $lada2,
            'PESU_ESPE' => $specialtiesStr,
            'PESU_USRE' => $idUser,
            'PESU_FERE' => $nowStr,
        ]);

        if (!is_null($idProvider)) {
            try {
                $validateUpdate = DB::table('S002V01TPROV')
                    ->where('PROV_NUPR', '=', $idProvider)
                    ->where('PROV_NULI', '=', $form['linea'])
                    ->update([
                        'PROV_IDPS' => $subcontratistID,
                        'PROV_USMO' => $idUser,
                        'PROV_FEMO' => $nowStr,
                        'PROV_FEAR' => DB::raw('CURRENT_TIMESTAMP')
                    ]);
            } catch (\Throwable $th) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "Ocurrió un error al modificar el proveedor.", $th->getMessage(), 500);
            }

            if (!$validateUpdate) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "No se pudo modificar el proveedor.", [], 500);
            }
        }

        $response = array( 'idSubcontract' => $subcontratistID );

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M11GPRS',
            'S002V01F01GESU',
            'S002V01P02RESU',
            'Registro',
            "El usuario $name (" . $usr->USUA_IDUS . ") registró al subcontratista $form[SOCIAL_REASON] ($subcontratistID).",
            $idUser,
            $nowStr,
            'S002V01S01GESU'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO', $response);
    }
    public function getSubcontratistById($idSub, $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);
        }
        
        $idSub = $this->encryptionController->decrypt($idSub);
        if(!$idSub){
            return $this->responseController->makeResponse(true, 'El ID del subcontratista consultado no está encriptado correctamente.', [], 400);
        }

        $subcontratists_info = DB::table('S002V01TPESU')->select([
            'PESU_IDPS as ID_SUBCONTRATIST',
            'PESU_RASO as SOCIAL_REASON',
            'PESU_REFI as TAX_REFERENCE',
            'PESU_XRFC as RFC',
            'PESU_TIPO as CONTRACT_TYPE',
            'PESU_CORR as EMAIL',
            'PESU_EXTR as FOREIGNER',
            'PESU_TAID as TAX',
            'PESU_CALL as STREET',
            'PESU_NUEX as EXTERIOR_NUMBER',
            'PESU_NUIN as INTERIOR_NUMBER',
            'PESU_COLO as SUBURB',
            'PESU_CIUD as CITY',
            'PESU_LOCA as TOWN',
            'PESU_COPO as POSTAL_CODE',
            'PESU_ENFE as FEDERAL_ENTITY',
            'PESU_IDPA as COUNTRY',
            'PESU_TEL1 as TELEPHONE1',
            'PESU_LAT1 as LADA1',
            'PESU_TEL2 as TELEPHONE2',
            'PESU_LAT2 as LADA2',
            'PESU_ESPE as SPECIALTY',
            'PESU_ESTA as STATUS'
        ])->where([
            ['PESU_NULI', '=', $line],
            ['PESU_IDPS', '=', $idSub],
        ])->first();

        $taxRegime = DB::table('S002V01TREFI')->where([
            ['REFI_NULI', '=', $line],
            ['REFI_CRFI', '=', $subcontratists_info->TAX_REFERENCE],
        ])->first();
        $subcontratists_info->TAX_REFERENCE = $taxRegime->REFI_DRFI . " (" . $subcontratists_info->TAX_REFERENCE . ")";

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

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

        $city = DB::table('S002V01TMUNI')->where([
            ['MUNI_NULI', '=', $line],
            ['MUNI_COMU', '=', $subcontratists_info->CITY],
            ['MUNI_COES', '=', $subcontratists_info->FEDERAL_ENTITY],
        ])->first();

        $town = DB::table('S002V01TLOCA')->where([
            ['LOCA_NULI', '=', $line],
            ['LOCA_COLO', '=', $subcontratists_info->TOWN],
            ['LOCA_COES', '=', $subcontratists_info->FEDERAL_ENTITY],
        ])->first();

        $setting = DB::table('S002V01TCOLO')->where([
            ['COLO_NULI', '=', $line],
            ['COLO_COCO', '=', $subcontratists_info->SUBURB],
            ['COLO_COPO', '=', $subcontratists_info->POSTAL_CODE],
        ])->first();

        if(!is_null($country)){
            $subcontratists_info->COUNTRY = trim($country->PAIS_NOMB) . " (" . $subcontratists_info->COUNTRY . ")";
        }

        if(!is_null($state)){
            $subcontratists_info->FEDERAL_ENTITY = trim($state->ESTA_NOES) . " (" . $subcontratists_info->FEDERAL_ENTITY . ")";
        }

        if(!is_null($city)){
            $subcontratists_info->CITY = trim($city->MUNI_NOMU) . " (" . $subcontratists_info->CITY . ")";
        }

        if(!is_null($town)){
            $subcontratists_info->TOWN = trim($town->LOCA_NOLO) . " (" . $subcontratists_info->TOWN . ")";
        }

        if(!is_null($setting)){
            $subcontratists_info->SUBURB = trim($setting->COLO_NOCO) . " (" . $subcontratists_info->SUBURB . ")";
        }

        $specialtiesArr = json_decode($subcontratists_info->SPECIALTY, true);
        foreach($specialtiesArr as $key=>$specialtyCode){
            $specialty = DB::table('S002V01TGEES')->where([
                ['GEES_NULI', '=', $line],
                ['GEES_COES', '=', $specialtyCode]
            ])->first();

            $specialty = $specialtyCode . " - " . $specialty->GEES_NOES;
            $specialtyCode = $this->encryptionController->encrypt($specialty);
            $specialtiesArr[$key] = $specialtyCode;
        }

        $subcontratists_info->SPECIALTY = json_encode($specialtiesArr);
        $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,
            'S002V01M11GPRS',
            'S002V01F01GESU',
            'S002V01P03DESU',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó al subcontratista " . $subcontratists_info->SOCIAL_REASON . " ($idSub).",
            $idUser,
            $nowStr,
            'S002V01S01GESU'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $subcontratists_info);
    }
    public function updateSubcontratist(Request $request) {
        DB::enableQueryLog();

        $request['TELEPHONE1'] = $this->encryptionController->decrypt($request->TELEPHONE1) ? $this->encryptionController->decrypt($request->TELEPHONE1) : 'ENC_ERR';
        $request['TELEPHONE2'] = $this->encryptionController->decrypt($request->TELEPHONE2) ? $this->encryptionController->decrypt($request->TELEPHONE2) : 'ENC_ERR';
        $request['EMAIL'] = $this->encryptionController->decrypt($request->EMAIL) ? $this->encryptionController->decrypt($request->EMAIL) : 'ENC_ERR';
        
        if (!is_null($request->RFC)) {
            $request['RFC'] = $this->encryptionController->decrypt($request->RFC) ? $this->encryptionController->decrypt($request->RFC) : 'ENC_ERR';
        } else {
            $request['RFC'] = '';
        }
        if (!is_null($request->TAX)) {
            $request['TAX'] = $this->encryptionController->decrypt($request->TAX) ? $this->encryptionController->decrypt($request->TAX) : 'ENC_ERR';
        } else {
            $request['TAX'] = '';
        }

        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'id_subcontratist' => 'required|string',
            'SOCIAL_REASON' => 'required|string|max:150',
            'TAX_REFERENCE' => 'required|string|max:15',
            'CONTRACT_TYPE' => 'required|string|in:Persona moral,Persona física',
            'FOREIGNER' => 'required|string|in:Si,No',
            'RFC' => 'required_if:FOREIGNER,=,No|string|max:13',
            'TAX' => 'required_if:FOREIGNER,=,Si|string|max:13',
            'EMAIL' => 'required|string|max:150',
            'COUNTRY' => 'required|string|max:75',
            'FEDERAL_ENTITY' => 'required|string|max:75',
            'CITY' => 'string|max:75',
            'TOWN' => 'string|max:75',
            'SUBURB' => 'required|string|max:75',
            'POSTAL_CODE' => 'required|string|max:5',
            'STREET' => 'required|string|max:150',
            'EXTERIOR_NUMBER' => 'required|string',
            // 'INTERIOR_NUMBER' => 'string',
            'LADA1' => 'required|string|max:15',
            'TELEPHONE1' => 'required|string|min:7|max:11',
            'LADA2' => 'string|max:15',
            'TELEPHONE2' => 'string|min:7|max:11',
            'SPECIALTY' => '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();
        foreach($form as $k=>$v){
            if($k == 'INTERIOR_NUMBER' && $v == '0'){
                unset($form[$k]);
            }else if($v == '-'){
                unset($form[$k]);
            }
        }

        $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);
        }

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

        $sub = DB::table('S002V01TPESU')->where([
            ['PESU_NULI', '=', $form['linea']],
            ['PESU_IDPS', '=', $idSub],
        ])->first();
        
        if(is_null($sub)){
            return $this->responseController->makeResponse(true, 'El subcontratista solicitado no existe.', [], 404);
        }

        $ladasValues = [];
        $ladas = DB::table('S002V01TPAIS')->select([
            'PAIS_LADA AS LADA'
        ])->where('PAIS_NULI', '=', $form['linea'])->get()->all();

        foreach($ladas as $lada){
            if($lada->LADA != '' && $lada->LADA != '0'){
                $ladasValues[] = $lada->LADA;
            }
        }

        if(!in_array($form['LADA1'], $ladasValues)){
            return $this->responseController->makeResponse(true, "La lada $form[LADA1] no está relacionada a ningún país.", [], 400);
        }else if($form['TELEPHONE1'] == 'ENC_ERR'){
            return $this->responseController->makeResponse(true, 'El número telefónico 1 no fue encriptado correctamente.', [], 400);
        }

        $lada2 = null;
        $phone2 = null;
        if(isset($form['LADA2'])){
            if(!in_array($form['LADA2'], $ladasValues)){
                return $this->responseController->makeResponse(true, "La lada $form[LADA2] no está relacionada a ningún país.", [], 400);
            }else if($form['TELEPHONE2'] == 'ENC_ERR'){
                return $this->responseController->makeResponse(true, 'El número telefónico 2 no fue encriptado correctamente.', [], 400);
            }

            $lada2 = $form['LADA2'];
            $phone2 = $form['TELEPHONE2'];
        }

        if($form['EMAIL'] == 'ENC_ERR'){
            return $this->responseController->makeResponse(true, 'El email no fue encriptado correctamente.', [], 400);
        }

        $rfcx = null;
        if(isset($form['RFC']) && $form['FOREIGNER'] == 'No' && $form['RFC'] == 'ENC_ERR'){
            return $this->responseController->makeResponse(true, 'El RFC no fue encriptado correctamente.', [], 400);
        }else if(isset($form['RFC']) && $form['RFC'] != 'ENC_ERR'){
            $rfcx = $form['RFC'];
        }

        $taid = null;
        if(isset($form['TAX']) && $form['FOREIGNER'] == 'Si' && $form['TAX'] == 'ENC_ERR'){
            return $this->responseController->makeResponse(true, 'El TAX ID no fue encriptado correctamente.', [], 400);
        }else if(isset($form['TAX']) && $form['TAX'] != 'ENC_ERR'){
            $taid =  $form['TAX'];
        }
        
        $country = DB::table('S002V01TPAIS')->where([
            ['PAIS_NULI', '=', $form['linea']],
            ['PAIS_IDPA', '=', $form['COUNTRY']],
        ])->first();
        
        if(is_null($country)){
            return $this->responseController->makeResponse(true, 'El país seleccionado no existe.', [], 404);
        }
        
        $taxRegime = DB::table('S002V01TREFI')->where([
            ['REFI_NULI', '=', $form['linea']],
            ['REFI_CRFI', '=', $form['TAX_REFERENCE']],
        ])->first();
        
        if(is_null($taxRegime)){
            return $this->responseController->makeResponse(true, 'El régimen fiscal seleccionado no existe.', [], 404);
        }

        $colo = null;
        $ciud = null;
        $loca = null;
        if($form['COUNTRY'] == 'MEX' || $form['COUNTRY'] == 'USA' || $form['COUNTRY'] == 'CAN'){
            $state = DB::table('S002V01TESTA')->where([
                ['ESTA_NULI', '=', $form['linea']],
                ['ESTA_COES', '=', $form['FEDERAL_ENTITY']],
                ['ESTA_COPA', '=', $form['COUNTRY']],
            ])->first();
        
            if(is_null($state)){
                return $this->responseController->makeResponse(true, 'El estado seleccionado no existe.', [], 404);
            }

            if($form['COUNTRY'] == 'MEX'){
                if(isset($form['CITY'])){
                    $city = DB::table('S002V01TMUNI')->where([
                        ['MUNI_NULI', '=', $form['linea']],
                        ['MUNI_COMU', '=', $form['CITY']],
                        ['MUNI_COES', '=', $form['FEDERAL_ENTITY']],
                    ])->first();
            
                    if(is_null($city)){
                        return $this->responseController->makeResponse(true, 'El municipio seleccionado no existe.', [], 404);
                    }else{
                        $ciud = $form['CITY'];
                    }
                }

                if(isset($form['TOWN'])){
                    $town = DB::table('S002V01TLOCA')->where([
                        ['LOCA_NULI', '=', $form['linea']],
                        ['LOCA_COLO', '=', $form['TOWN']],
                        ['LOCA_COES', '=', $form['FEDERAL_ENTITY']],
                    ])->first();
            
                    if(is_null($town)){
                        return $this->responseController->makeResponse(true, 'La localidad seleccionada no existe.', [], 404);
                    }else{
                        $loca = $form['TOWN'];
                    }
                }
                
                $setting = DB::table('S002V01TCOLO')->where([
                    ['COLO_NULI', '=', $form['linea']],
                    ['COLO_COCO', '=', $form['SUBURB']],
                    ['COLO_COPO', '=', $form['POSTAL_CODE']],
                ])->first();
            
                if(is_null($setting)){
                    return $this->responseController->makeResponse(true, 'La colonia seleccionada no existe.', [], 404);
                }else{
                    $colo = $form['SUBURB'];
                }
                
                $zipCode = DB::table('S002V01TCOPO')->where([
                    ['COPO_NULI', '=', $form['linea']],
                    ['COPO_COPO', '=', $form['POSTAL_CODE']],
                    ['COPO_COES', '=', $form['FEDERAL_ENTITY']],
                ])->first();
            
                if(is_null($zipCode)){
                    return $this->responseController->makeResponse(true, 'El código postal seleccionado no existe.', [], 404);
                }
            }else{
                $colo = $form['SUBURB'];
                $ciud = $form['CITY'];
                $loca = isset($form['TOWN']) ? $form['TOWN'] : null;
            }
        }else{
            $colo = $form['SUBURB'];
            $ciud = $form['CITY'];
            $loca = isset($form['TOWN']) ? $form['TOWN'] : null;
        }

        /* $subArr = DB::table('S002V01TPESU')->where('PESU_NULI', '=', $form['linea'])->where(function(Builder $query) use ($form) {
            $query->where('PESU_RASO', '=', $form['SOCIAL_REASON'])
            ->orWhere('PESU_XRFC', '=', $form['RFC'])
            ->orWhere('PESU_CORR', '=', $form['EMAIL']);
        })->where([
            ['PESU_IDPS', '!=', $idSub],
            ['PESU_ESTA', '=', 'Activo'],
        ])->get()->all();
        if(count($subArr) > 0){
            return $this->responseController->makeResponse(true, 'La razón social, el RFC o el correo electrónico ya fueron registrados.', [], 401);
        } */

        if (!is_null($form['RFC'])) {
            $subArr = DB::table('S002V01TPESU')->where('PESU_NULI', '=', $form['linea'])->where(function(Builder $query) use ($form) {
                $query->where('PESU_RASO', '=', $form['SOCIAL_REASON'])
                ->orWhere('PESU_XRFC', '=', $form['RFC'])
                ->orWhere('PESU_CORR', '=', $form['EMAIL']);
            })->where([
                ['PESU_IDPS', '!=', $idSub],
                ['PESU_ESTA', '=', 'Activo'],
            ])->get()->all();

            if(count($subArr) > 0){
                return $this->responseController->makeResponse(true, 'La razón social, el RFC o el correo electrónico ya fueron registrados.', [], 401);
            }
        }
        if (!is_null($form['TAX'])) {

            $subArr = DB::table('S002V01TPESU')->where('PESU_NULI', '=', $form['linea'])->where(function(Builder $query) use ($form) {
                $query->where('PESU_RASO', '=', $form['SOCIAL_REASON'])
                ->orWhere('PESU_XRFC', '=', $form['TAX'])
                ->orWhere('PESU_CORR', '=', $form['EMAIL']);
            })->where([
                ['PESU_IDPS', '!=', $idSub],
                ['PESU_ESTA', '=', 'Activo'],
            ])->get()->all();
            if(count($subArr) > 0){
                return $this->responseController->makeResponse(true, 'La razón social, el Tax ID o el correo electrónico ya fueron registrados.', [], 401);
            }
            /* $subArr = DB::table('S002V01TPESU')->where('PESU_NULI', '=', $form['linea'])->where(function(Builder $query) use ($form) {
                $query->where('PESU_RASO', '=', $form['SOCIAL_REASON'])
                ->orWhere('PESU_TAID', '=', $form['TAX'])
                ->orWhere('PESU_CORR', '=', $form['EMAIL']);
            })->where('PESU_ESTA', '=', 'Activo')->get()->all();
            if(count($subArr) > 0){
                return $this->responseController->makeResponse(true, 'La razón social, el Tax ID o el correo electrónico ya fueron registrados. 2', [], 401);
            } */
        }

        $specialtiesArr = json_decode($form['SPECIALTY'], true);
        foreach($specialtiesArr as $key=>$specialtyCode){
            $specialtyCode = $this->encryptionController->decrypt($specialtyCode);
            if(!$specialtyCode){
                return $this->responseController->makeResponse(true, "El ID del elemento en la posición $key del arreglo de especialidades no fue encriptado correctamente.", [], 400);
            }

            $specialtyObj = DB::table('S002V01TGEES')->where([
                ['GEES_NULI', '=', $form['linea']],
                ['GEES_COES', '=', $specialtyCode]
            ])->first();

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

            $specialtiesArr[$key] = $specialtyCode;
        }

        $specialtiesStr = json_encode($specialtiesArr);
        $nuin = isset($form['INTERIOR_NUMBER']) ? $form['INTERIOR_NUMBER'] : null;
        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        DB::table('S002V01TPESU')->where([
            ['PESU_NULI', '=', $form['linea']],
            ['PESU_IDPS', '=', $idSub],
        ])->update([
            'PESU_RASO' => $form['SOCIAL_REASON'],
            'PESU_REFI' => $form['TAX_REFERENCE'],
            'PESU_XRFC' => $rfcx,
            'PESU_TIPO' => $form['CONTRACT_TYPE'],
            'PESU_CORR' => $form['EMAIL'],
            'PESU_EXTR' => $form['FOREIGNER'],
            'PESU_TAID' => $taid,
            'PESU_CALL' => $form['STREET'],
            'PESU_NUEX' => $form['EXTERIOR_NUMBER'],
            'PESU_NUIN' => $nuin,
            'PESU_COLO' => $colo,
            'PESU_CIUD' => $ciud,
            'PESU_LOCA' => $loca,
            'PESU_COPO' => $form['POSTAL_CODE'],
            'PESU_ENFE' => $form['FEDERAL_ENTITY'],
            'PESU_IDPA' => $form['COUNTRY'],
            'PESU_TEL1' => $form['TELEPHONE1'],
            'PESU_LAT1' => $form['LADA1'],
            'PESU_TEL2' => $phone2,
            'PESU_LAT2' => $lada2,
            'PESU_ESPE' => $specialtiesStr,
            'PESU_USMO' => $idUser,
            'PESU_FEMO' => $nowStr,
        ]);

        $response  = array('idSubcontract' => $idSub);

        try {
            $getProvider = (array) DB::table('S002V01TPROV')
                ->where('PROV_IDPS', '=', $idSub)
                ->where('PROV_NULI', '=', $form['linea'])
                ->first();
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "Ocurrió un error al válidar si existe en proveedor.", $th->getMessage(), 500);
        }
        if ( !empty($getProvider) ) {
            try {
                $validateUpdateInformation = DB::table('S002V01TPROV')
                    ->where('PROV_NUPR', '=', $getProvider['PROV_NUPR'])
                    ->where('PROV_NULI', '=', $form['linea'])
                    ->update([
                        'PROV_NOCO' => $form['SOCIAL_REASON'],
                        'PROV_CORR' => $form['EMAIL'],
                        'PROV_LAD1' => $form['LADA1'],
                        'PROV_TEL1' => $form['TELEPHONE1'],
                        'PROV_LAD2' => $lada2,
                        'PROV_TEL2' => $phone2,
                        'PROV_TICO' => $form['CONTRACT_TYPE'],
                        'PROV_XRFC' => $rfcx,
                        'PROV_XTAX' => $taid,
                        'PROV_CRFI' => $form['TAX_REFERENCE'],
                        'PROV_TIPO' => $form['FOREIGNER'] === 'No' ? 'Nacional' : 'Extranjero',
                        'PROV_CALL' => $form['STREET'],
                        'PROV_NUEX' => $form['EXTERIOR_NUMBER'],
                        'PROV_NUIN' => $nuin,
                        'PROV_COPO' => $form['POSTAL_CODE'],
                        'PROV_COLO' => $colo,
                        'PROV_LOCA' => $loca,
                        'PROV_MUNI' => $ciud,
                        'PROV_ENTI' => $form['FEDERAL_ENTITY'],
                        'PROV_PAIS' => $form['COUNTRY'],
                        'PROV_USMO' => $idUser,
                        'PROV_FEMO' => $nowStr,
                        'PROV_FEAR' => DB::raw('CURRENT_TIMESTAMP')
                    ]);
            } catch (\Throwable $th) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "Ocurrió un error al modificar al proveedor", $th->getMessage(), 500);
            }
            if (!$validateUpdateInformation) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "No se pudo modificar el proveedor.", [], 500);
            }
            $response['provider'] = $getProvider;
        }

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M11GPRS',
            'S002V01F01GESU',
            'S002V01P02RESU',
            'Actualización',
            "El usuario $name (" . $usr->USUA_IDUS . ") actualizó al subcontratista $idSub.",
            $idUser,
            $nowStr,
            'S002V01S01GESU'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO', $response);
    }
    public  function updateToInactiveStatusSubcontract(Request $request) {
        DB::enableQueryLog();

        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'id_subcontratist' => '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);
        }

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

        $sub = DB::table('S002V01TPESU')->where([
            ['PESU_NULI', '=', $form['linea']],
            ['PESU_IDPS', '=', $idSub],
        ])->first();
        
        if(is_null($sub)){
            return $this->responseController->makeResponse(true, 'El subcontratista solicitado no existe.', [], 404);
        }

        $users = DB::table('S002V01TPERS')->where([
            ['PERS_NULI', '=', $form['linea']],
            ['PERS_ESTA', '=', 'Activo'],
        ])->join('S002V01TPESU', 'PERS_IDPS', '=', 'PESU_IDPS')->get()->all();

        if(count($users) > 0){
            return $this->responseController->makeResponse(true, 'El subcontratista que desea eliminar tiene usuarios relacionados.', [], 401);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        DB::table('S002V01TPESU')->where([
            ['PESU_NULI', '=', $form['linea']],
            ['PESU_IDPS', '=', $idSub],
        ])->update([
            'PESU_ESTA' => 'Eliminado',
            'PESU_USMO' => $idUser,
            'PESU_FEMO' => $nowStr
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M11GPRS',
            'S002V01F01GESU',
            'S002V01P02RESU',
            'Eliminación',
            "El usuario $name (" . $usr->USUA_IDUS . ") eliminó al subcontratista $idSub.",
            $idUser,
            $nowStr,
            'S002V01S01GESU'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO');
    }
    public function getContractsOfEverySubcontratist($idUser, $line) {
        $arrResponseCheckUser = $this->resourcesController->checkUserEnc($idUser, $line);
        if ($arrResponseCheckUser['error']) {
            return $this->responseController->makeResponse(true, $arrResponseCheckUser['msg'], [], 401);
        }

        try {
            $arrSubcontratists = DB::table('S002V01TPERS')
                ->where([
                    ['PERS_NULI', '=', $line],
                    ['PERS_TICO', '=', 'Subcontratista'],
                ])
                ->join('S002V01TUSUA', 'USUA_IDUS', '=', 'PERS_IDUS')
                ->join('S002V01TPESU', 'PESU_IDPS', '=', 'PERS_IDPS')
                ->get([
                    'PERS_IDPE AS ID_EMPLEADO',
                    'USUA_IDUS AS ID_USUARIO',
                    'USUA_NOMB AS NOMBRE',
                    'USUA_APPA AS APELLIDO_PATERNO',
                    'USUA_APMA AS APELLIDO_MATERNO',
                    'PESU_IDPS AS ID_SUBCONTRATISTA',
                    'PESU_RASO AS RAZON_SOCIAL',
                    'PESU_REFI AS REFERENCIA_FISCAL',
                ]);
            $arrSubcontratists = json_decode(json_encode($arrSubcontratists), true);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(true, 'Ocurrió un error al recuperar los subcontratistas', $th->getMessage(), 404);
        }
        
        $contractsBySubcontratists = array();
        foreach ($arrSubcontratists as $key => $subcontratist) {
            
            try {
                $countSubcontract = DB::table('S002V01TCONT')
                    ->where([
                        ['CONT_IDEM', '=', $subcontratist['ID_EMPLEADO']],
                        ['CONT_NULI', '=', $line],
                        ['CONT_ESTA', '=', 'Activo'],
                    ])
                    ->count();
            } catch (\Throwable $th) {
                return $this->responseController->makeResponse(true, 'Ocurrió un error al obtener los contratos', $th->getMessage(), 404);
            }

            $employee = trim("{$subcontratist['NOMBRE']} {$subcontratist['APELLIDO_PATERNO']} {$subcontratist['APELLIDO_MATERNO']}");

            $contractsBySubcontratists[] = [
                'ID_EMPLEADO' => $this->encryptionController->encrypt($subcontratist['ID_EMPLEADO']),
                'ID_USUARIO' => $this->encryptionController->encrypt($subcontratist['ID_USUARIO']),
                'ID_SUBCONTRATISTA' => $this->encryptionController->encrypt($subcontratist['ID_SUBCONTRATISTA']),
                'EMPLEADO' => "{$employee} ({$subcontratist['ID_EMPLEADO']}) <{$subcontratist['ID_USUARIO']}>",
                'RAZON_SOCIAL' => "{$subcontratist['RAZON_SOCIAL']} ({$subcontratist['ID_SUBCONTRATISTA']})",
                'NUMERO_CONTRATOS' => $countSubcontract === 1 ? "{$countSubcontract} contrato" : "{$countSubcontract} contratos",
            ];
        }

        return $this->responseController->makeResponse(false, 'EXITO', $contractsBySubcontratists);
    }
    public function getContractsOfEverySubcontratistOld($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);
        }

        $contractsBySubcontratists = DB::table('S002V01TPERS')->select([
            DB::raw("
                TRIM(CONCAT(
                    USUA_NOMB, 
                    ' ', 
                    USUA_APPA,
                    IF(ISNULL(USUA_APMA), '', CONCAT(' ', USUA_APMA)),
                    ' <(',
                    PERS_IDPE,
                    ')> (',
                    PERS_IDUS,
                    ')'
                )) AS EMPLOYEE
            "),
            DB::raw("COUNT(CONT_IDCO) AS CONTRACTS")
        ])->leftJoin('S002V01TCONT', 'CONT_IDEM', '=', 'PERS_IDPE')
        ->join('S002V01TUSUA', 'USUA_IDUS', '=', 'PERS_IDUS')
        ->groupBy('EMPLOYEE')->where([
            ['PERS_NULI', '=', $line],
            ['PERS_TICO', '=', 'Subcontratista'],
        ])->get()->all();

        foreach($contractsBySubcontratists as $key=>$val){
            $employeeArr = explode('<', $val->EMPLOYEE);
            $employeeName = $employeeArr[0];
            $employeeArr = array_reverse($employeeArr);
            $employeeArr = explode('>', $employeeArr[0]);

            $employeeID = str_replace('(', '', $employeeArr[0]);
            $employeeID = str_replace(')', '', $employeeID);

            $subcontratist = DB::table('S002V01TPESU')->where([
                ['PESU_NULI', '=', $line],
                ['PESU_IDPS', '=', $employeeID],
            ])->first();

            if (is_null($subcontratist)) {
                var_dump($employeeID);
                return $this->responseController->makeResponse(true, 'Ocurrió un error al obtener los datos del proveedor.', [], 404);
            }

            $val->ID_EMPLOYEE = $this->encryptionController->encrypt($employeeID);
            $val->EMPLOYEE = $employeeName . join('', $employeeArr);
            $val->ID_SUBCONTRATIST = $this->encryptionController->encrypt($employeeID);
            $val->SUBCONTRATIST = $subcontratist->PESU_RASO . " (" . $subcontratist->PESU_REFI . ")";

            $contractsBySubcontratists[$key] = $val;
        }

        $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,
            'S002V01M11GPRS',
            'S002V01F02ADCO',
            'S002V01P01HCSU',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó los contratos por subcontratista.",
            $idUser,
            $nowStr,
            'S002V01S01GESU'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $contractsBySubcontratists);
    }
    public function getContractsBySubcontratist($idSubcontratist, $idUser, $line) {
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue 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);
        }

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

        $sub = DB::table('S002V01TPERS')->where([
            ['PERS_NULI', '=', $line],
            ['PERS_IDPE', '=', $idSubcontratist],
            ['PERS_TICO', '=', 'Subcontratista'],
        ])->first();
        
        if(is_null($sub)){
            return $this->responseController->makeResponse(true, 'El subcontratista solicitado no existe.', [], 404);
        }

        $contracts = DB::table('S002V01TCONT')->select([
            'CONT_NOCO AS CONTRACT_NAME',
            'CONT_FEIN AS START_DATE',
            'CONT_FEFI AS END_DATE',
            'CONT_COST AS COST',
        ])->where([
            ['CONT_NULI', '=', $line],
            ['CONT_IDEM', '=', $idSubcontratist],
            ['CONT_TIPO', '=', 'Subcontratista'],
        ])->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,
            'S002V01M11GPRS',
            'S002V01F02ADCO',
            'S002V01P01HCSU',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó los contratos del subcontratista #$idSubcontratist.",
            $idUser,
            $nowStr,
            'S002V01S01GESU'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $contracts);
    }
    public function downloadSubcontractInfoOnExcel($idSubcontratist, $idUser, $line){
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue 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);
        }

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

        $sub = DB::table('S002V01TPERS')->where([
            ['PERS_NULI', '=', $line],
            ['PERS_IDPE', '=', $idSubcontratist],
            ['PERS_TICO', '=', 'Subcontratista'],
        ])->first();
        
        if(is_null($sub)){
            return $this->responseController->makeResponse(true, 'El subcontratista solicitado no existe.', [], 404);
        }

        $contracts = DB::table('S002V01TCONT')->select([
            'CONT_IDCO AS IDCONTRATO',
            'CONT_NOCO AS NOMBRECONTRATO',
            'CONT_IDOT AS IDORDEN',
            'CONT_TOTR AS TIPOORDEN',
            'PERS_ESPE AS ESPECIALIDAD',
            'CONT_FEIN AS FECHAINICIO',
            'CONT_FEFI AS FECHAFIN',
            'CONT_COST AS COSTO',
        ])->join('S002V01TPERS', 'PERS_IDPE', '=', 'CONT_IDEM')->where([
            ['CONT_NULI', '=', $line],
            ['CONT_IDEM', '=', $idSubcontratist],
            ['CONT_TIPO', '=', 'Subcontratista'],
        ])->get()->all();

        $document = $this->generateDocument($contracts, $line);

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        
        $dateTimeArr = explode(" ", $nowStr);
        $dateArr = explode("-", $dateTimeArr[0]);
        $year = substr($dateArr[0], 2);
        $como = 'GPRS';
        $cldo = 'IN';
        $fecr = $year . $dateArr[1] . $dateArr[2];

        $sec = DB::table('S002V01TAFAL')->where([
            ['AFAL_NULI', '=', $line],
            ['AFAL_COMO', '=', $como],
            ['AFAL_CLDO', '=', $cldo],
        ])->orderBy('AFAL_NUSE', 'desc')->first();

        $nuse = "";
        if(is_null($sec)){
            $nuse = '000001';
        }else{
            $secu = "" . intval($sec->AFAL_NUSE) + 1 . "";
            $nuse = "";

            for($i = strlen($secu); $i < 6; $i++){
                $nuse .= "0";
            }

            $nuse = $nuse . $secu;
        }

        $noar = "ficha_de_subcontratacion_del_subcontratista_$idSubcontratist";
        $exte = "xlsx";

        $ver = DB::table('S002V01TAFAL')->where([
            ['AFAL_NULI', '=', $line],
            ['AFAL_COMO', '=', $como],
            ['AFAL_CLDO', '=', $cldo],
            ['AFAL_NOAR', '=', $noar],
            ['AFAL_EXTE', '=', $exte],
        ])->orderBy('AFAL_NUVE', 'desc')->first();

        $nuve = "";
        if(is_null($ver)){
            $nuve = "01";
        }else{
            $vers = intval($ver->AFAL_NUVE) + 1;
            $nuve = $vers < 10 ? "0$vers" : "$vers";
        }

        $line = $line < 10 ? "0$line" : "$line";
        $filePath = $this->resourcesController->pathService.'\\public_files\\';
        $fileName = "$line-$como-$cldo-$fecr-$nuse=$nuve=$noar.$exte";
        
        $tempFile = $filePath . $fileName;
        if(file_exists($tempFile)){
            unlink($tempFile);
        }

        $writer = IOFactory::createWriter($document, 'Xlsx');
        $writer->save($tempFile);
        $ubic = Storage::putFile('files', new File($tempFile));
        $ubic = str_replace("/", "\\", $ubic);
        $ubic = $this->resourcesController->pathService."\storage\app\\" . $ubic;
        $tama = filesize($ubic);
        $usac = json_encode([$idUser]);
        unlink($tempFile);

        DB::table('S002V01TAFAL')->insert([
            'AFAL_NULI' => $line,
            'AFAL_COMO' => $como,
            'AFAL_CLDO' => $cldo,
            'AFAL_FECR' => $fecr,
            'AFAL_NUSE' => $nuse,
            'AFAL_NUVE' => $nuve,
            'AFAL_NOAR' => $noar,
            'AFAL_EXTE' => $exte,
            'AFAL_TAMA' => $tama,
            'AFAL_UBIC' => $ubic,
            'AFAL_USAC' => $usac,
            'AFAL_USRE' => $idUser,
            'AFAL_FERE' => $nowStr,
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M11GPRS',
            'S002V01F02ADCO',
            'S002V01P02FISU',
            'Registro',
            "El usuario $name (" . $usr->USUA_IDUS . ") generó la ficha de subcontratación del subcontratista #$idSubcontratist para Excel.",
            $idUser,
            $nowStr,
            'S002V01S01GESU'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO.', ['fileID' => $fileName]);
    }
    public function downloadSubcontractInfoOnPdf($idSubcontratist, $idUser, $line) {
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue 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);
        }

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

        $sub = DB::table('S002V01TPERS')->where([
            ['PERS_NULI', '=', $line],
            ['PERS_IDPE', '=', $idSubcontratist],
            ['PERS_TICO', '=', 'Subcontratista'],
        ])->first();
        
        if(is_null($sub)){
            return $this->responseController->makeResponse(true, 'El subcontratista solicitado no existe.', [], 404);
        }

        $contracts = DB::table('S002V01TCONT')->select([
            'CONT_IDCO AS IDCONTRATO',
            'CONT_NOCO AS NOMBRECONTRATO',
            'CONT_IDOT AS IDORDEN',
            'CONT_TOTR AS TIPOORDEN',
            'PERS_ESPE AS ESPECIALIDAD',
            'CONT_FEIN AS FECHAINICIO',
            'CONT_FEFI AS FECHAFIN',
            'CONT_COST AS COSTO',
        ])->join('S002V01TPERS', 'PERS_IDPE', '=', 'CONT_IDEM')->where([
            ['CONT_NULI', '=', $line],
            ['CONT_IDEM', '=', $idSubcontratist],
            ['CONT_TIPO', '=', 'Subcontratista'],
        ])->get()->all();

        $document = $this->generateDocument($contracts, $line);

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        
        $dateTimeArr = explode(" ", $nowStr);
        $dateArr = explode("-", $dateTimeArr[0]);
        $year = substr($dateArr[0], 2);
        $como = 'GPRS';
        $cldo = 'IN';
        $fecr = $year . $dateArr[1] . $dateArr[2];

        $sec = DB::table('S002V01TAFAL')->where([
            ['AFAL_NULI', '=', $line],
            ['AFAL_COMO', '=', $como],
            ['AFAL_CLDO', '=', $cldo],
        ])->orderBy('AFAL_NUSE', 'desc')->first();

        $nuse = "";
        if(is_null($sec)){
            $nuse = '000001';
        }else{
            $secu = "" . intval($sec->AFAL_NUSE) + 1 . "";
            $nuse = "";

            for($i = strlen($secu); $i < 6; $i++){
                $nuse .= "0";
            }

            $nuse = $nuse . $secu;
        }

        $noar = "ficha_de_subcontratacion_del_subcontratista_$idSubcontratist";
        $exte = "pdf";

        $ver = DB::table('S002V01TAFAL')->where([
            ['AFAL_NULI', '=', $line],
            ['AFAL_COMO', '=', $como],
            ['AFAL_CLDO', '=', $cldo],
            ['AFAL_NOAR', '=', $noar],
            ['AFAL_EXTE', '=', $exte],
        ])->orderBy('AFAL_NUVE', 'desc')->first();

        $nuve = "";
        if(is_null($ver)){
            $nuve = "01";
        }else{
            $vers = intval($ver->AFAL_NUVE) + 1;
            $nuve = $vers < 10 ? "0$vers" : "$vers";
        }

        $line = $line < 10 ? "0$line" : "$line";
        $filePath = $this->resourcesController->pathService.'\\public_files\\';
        $fileName = "$line-$como-$cldo-$fecr-$nuse=$nuve=$noar.$exte";
        
        $tempFile = $filePath . $fileName;
        if(file_exists($tempFile)){
            unlink($tempFile);
        }

        $writer = IOFactory::createWriter($document, 'Mpdf');
        $writer->save($tempFile);
        $ubic = Storage::putFile('files', new File($tempFile));
        $ubic = str_replace("/", "\\", $ubic);
        $ubic = $this->resourcesController->pathService."\storage\app\\" . $ubic;
        $tama = filesize($ubic);
        $usac = json_encode([$idUser]);
        unlink($tempFile);

        DB::table('S002V01TAFAL')->insert([
            'AFAL_NULI' => $line,
            'AFAL_COMO' => $como,
            'AFAL_CLDO' => $cldo,
            'AFAL_FECR' => $fecr,
            'AFAL_NUSE' => $nuse,
            'AFAL_NUVE' => $nuve,
            'AFAL_NOAR' => $noar,
            'AFAL_EXTE' => $exte,
            'AFAL_TAMA' => $tama,
            'AFAL_UBIC' => $ubic,
            'AFAL_USAC' => $usac,
            'AFAL_USRE' => $idUser,
            'AFAL_FERE' => $nowStr,
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M11GPRS',
            'S002V01F02ADCO',
            'S002V01P02FISU',
            'Registro',
            "El usuario $name (" . $usr->USUA_IDUS . ") generó la ficha de subcontratación del subcontratista #$idSubcontratist para Excel.",
            $idUser,
            $nowStr,
            'S002V01S01GESU'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO.', ['fileID' => $fileName]);
    }
    private function generateDocument($contracts, $line) : Spreadsheet {
        $spreadsheet = new Spreadsheet;
        $spreadsheet->getProperties()
            ->setCreator('STC')
            ->setTitle('Historial de contratos por subcontratista.')
            ->setSubject('Historial documento')
            ->setKeywords('Subcontratista Contratos Historial')
            ->setCategory('Historial archivo');
        
        $worksheet = $spreadsheet->getActiveSheet();
        $worksheet->setTitle('HISTORIAL DE CONTRATOS');

        $columns = ['# DE CONTRATO', 'NOMBRE DEL CONTRATO', 'ORDEN DE TRABAJO', 'TIPO DE ORDEN', 'ESPECIALIDAD', 'FECHA DE INICIO', 'FECHA DE TÉRMINO', 'COSTO'];
        $startRow = 2;
        $startCol = 2;
        $maxRow = $startRow + count($contracts) + 1;
        $maxCol = $startCol + 7;

        for($row = $startRow; $row <= $maxRow; $row++){
            $startColStr = Coordinate::stringFromColumnIndex($startCol);
            $maxColStr = Coordinate::stringFromColumnIndex($maxCol);

            if($row == 2){
                $worksheet->mergeCells($startColStr . $row . ':' . $maxColStr . $row);
                $worksheet->setCellValue($startColStr . $row, 'FICHA DE SUBCONTRATACIÓN')->getStyle($startColStr . $row)->getFill()
                    ->setFillType(Fill::FILL_SOLID)
                    ->getStartColor()->setRGB('B7BCC4');
                $worksheet->getStyle($startColStr . $row)->getFont()->setBold(true);
                $worksheet->getStyle($startColStr . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            }else if($row == 3){
                for($col = $startCol; $col <= $maxCol; $col++){
                    $colStr = Coordinate::stringFromColumnIndex($col);
                    $colInd = $col - 2;

                    $column = $columns[$colInd]; 
                    $worksheet->setCellValue($colStr . $row, $column);
                    $worksheet->getColumnDimension($colStr)->setAutoSize(true);
                    $worksheet->getStyle($colStr . $row)->getFont()->setBold(true);
                    $worksheet->getStyle($colStr . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
                }
            }else if($row > 3){
                $rowInd = $row - 4;
                $contract = (array) $contracts[$rowInd];
                $keys = array_keys($contract);

                for($col = $startCol; $col <= $maxCol; $col++){
                    $colInd = $col - 2;
                    $key = $keys[$colInd];
                    $value = $contract[$key];
                    $colStr = Coordinate::stringFromColumnIndex($col);

                    if($key == 'IDORDEN'){
                        $value = "Orden #$value";
                    }else if($key == 'FECHAINICIO' || $key == 'FECHAFIN'){
                        $value = $this->functionsController->formatDateTime($value);
                    }else if($key == 'COSTO'){
                        $worksheet->getStyle($colStr . $row)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_CURRENCY_USD);
                    }else if($key == 'ESPECIALIDAD'){
                        $specialtiesArr = json_decode($value, true);
                        $valueStr = "";

                        foreach($specialtiesArr as $val0){
                            $specialty = DB::table('S002V01TGEES')->where([
                                ['GEES_NULI', '=', $line],
                                ['GEES_COES', '=', $val0]
                            ])->first();

                            $valueStr .= $val0 . " - " . $specialty->GEES_NOES . ", ";
                        }

                        $value = substr($valueStr, 0, -2);
                    }

                    $worksheet->setCellValue($colStr . $row, $value);
                }
            }
        }

        if(count($contracts) == 0){
            $startColStr = Coordinate::stringFromColumnIndex($startCol);
            $maxColStr = Coordinate::stringFromColumnIndex($maxCol);
            $maxRow++;

            $worksheet->mergeCells($startColStr . $maxRow . ':' . $maxColStr . $maxRow);
            $worksheet->setCellValue($startColStr . $maxRow, 'Aún no se han asociado contratos al subcontratista.');
            $worksheet->getStyle($startColStr . $maxRow)->getFont()->setBold(true);
            $worksheet->getStyle($startColStr . $maxRow)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        }

        $worksheet->getStyle($startColStr . $startRow . ':' . $maxColStr . $maxRow)
            ->getBorders()
            ->getAllBorders()
            ->setBorderStyle(Border::BORDER_MEDIUM);

        return $spreadsheet;
    }


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

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

        if($validator->fails()){
            return $this->responseController->makeResponse(
                TRUE,
                'ERR_PERSONAL_REG001: Uno o más errores encontrados',
                $this->responseController->makeErrors($validator->errors()->messages()),
                400
            );
        }

        $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_NULI', '=', $form['linea']],
            ['USUA_IDUS', '=', $idUser],
        ])->first();

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

        $specialtiesArr = json_decode($form['SPECIALITY'], true);
        foreach($specialtiesArr as $key=>$specialty){
            $specialtyCode = $this->encryptionController->decrypt($specialty);
            if(!$specialtyCode){
                return $this->responseController->makeResponse(true, "El ID del elemento en la posición $key del arreglo de especialidades no fue encriptado correctamente.", [], 400);
            }

            $specialtyObj = DB::table('S002V01TGEES')->where([
                ['GEES_NULI', '=', $form['linea']],
                ['GEES_COES', '=', $specialtyCode]
            ])->first();

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

            $specialtiesArr[$key] = $specialtyCode;
        }

        $specialtiesStr = json_encode($specialtiesArr);
        $team = DB::table('S002V01TEQMA')->where([
            ['EQMA_NOMB', '=', $form['NAME']],
            ['EQMA_NULI', '=', $form['linea']],
            ['EQMA_ESTA', '=', 'Activo'],
        ])->get()->all();

        if(count($team) > 0){
            return $this->responseController->makeResponse(true, "El nombre $form[NAME] ya fue tomado", [], 401);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        DB::table('S002V01TEQMA')->insert([
            "EQMA_NULI" => $form['linea'],
            "EQMA_NOMB" => $form['NAME'],
            "EQMA_ESPE" => $specialtiesStr,
            "EQMA_USRE" => $idUser,
            "EQMA_FERE" => $nowStr,
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M11GPRS',
            'S002V01F01AETR',
            'S002V01P02REET',
            'Registro',
            "El usuario $name (" . $usr->USUA_IDUS . ") registró el equipo de trabajo $form[NAME].",
            $idUser,
            $nowStr,
            'S002V01S04GETR'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO.');
    }
    public function getConsultOfWorkteams($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);
        }

        $workteams = DB::table('S002V01TEQMA')->select([
            'EQMA_IDEQ as WORKTEAM_ID',
            'EQMA_NOMB as NAME',
            'EQMA_ESPE as SPECIALITY',
            'EQMA_ESTA as STATUS'
        ])->where('EQMA_NULI', '=', $line)->get()->all();

        foreach($workteams as $workteam){
            $workteam->WORKTEAM_ID = $this->encryptionController->encrypt($workteam->WORKTEAM_ID);
            $specialtiesArr = json_decode($workteam->SPECIALITY, true);
            foreach($specialtiesArr as $key=>$specialtyCode){
                $specialty = DB::table('S002V01TGEES')->where([
                    ['GEES_NULI', '=', $line],
                    ['GEES_COES', '=', $specialtyCode],
                ])->first();

                $specialtyName = $specialtyCode . " - " . $specialty->GEES_NOES;
                $specialtyCode = $this->encryptionController->encrypt($specialtyName);
                $specialtiesArr[$key] = $specialtyCode;
            }

            $workteam->SPECIALITY = json_encode($specialtiesArr);
        }

        $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,
            'S002V01M11GPRS',
            'S002V01F01AETR',
            'S002V01P01CETR',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó los equipos de trabajo registrados.",
            $idUser,
            $nowStr,
            'S002V01S04GETR'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO.', $workteams);
    }
    public function getWorkteamById($workteam, $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);
        }

        $workteam = $this->encryptionController->decrypt($workteam);
        if(!$workteam){
            return $this->responseController->makeResponse(true, 'El ID equipo de trabajo solicitado no está encriptado correctamente', [], 400);
        }

        $team = DB::table('S002V01TEQMA')->select([
            'EQMA_IDEQ as WORKTEAM_ID',
            'EQMA_NOMB as NAME',
            'EQMA_ESPE as SPECIALITY',
            'EQMA_ESTA as STATUS'
        ])->where([
            ['EQMA_IDEQ', '=', $workteam],
            ['EQMA_NULI', '=', $line],
        ])->first();

        if(is_null($team)){
            return $this->responseController->makeResponse(true, 'El equipo de trabajo consultado no está registrado', [], 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,
            'S002V01M11GPRS',
            'S002V01F01AETR',
            'S002V01P03DETR',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó los detalles del equipo de trabajo " . $team->NAME . ".",
            $idUser,
            $nowStr,
            'S002V01S04GETR'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO.', $team);
    }
    public function getMembersOfWorkteamById($workteam, $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);
        }

        $workteam = $this->encryptionController->decrypt($workteam);
        if(!$workteam){
            return $this->responseController->makeResponse(true, 'El ID equipo de trabajo solicitado no está encriptado correctamente', [], 400);
        }

        $team = DB::table('S002V01TEQMA')->where([
            ['EQMA_IDEQ', '=', $workteam],
            ['EQMA_NULI', '=', $line],
        ])->first();

        if(is_null($team)){
            return $this->responseController->makeResponse(true, 'El equipo de trabajo consultado no está registrado', [], 404);
        }

        $members = DB::table('S002V01TPERS')->select([
            DB::raw('TRIM(CONCAT(USUA_NOMB, " " , USUA_APPA, " ", COALESCE(USUA_APMA,""))) as NAME'),
            'PERS_IDPE AS IDEMPLEADO',
            'PERS_IDUS AS IDUSUARIO'
        ])->where([
            ['EQMA_IDEQ', '=', $workteam],
            ['EQMA_NULI', '=', $line],
            ['PERS_ESTA', '=', 'Activo'],
        ])->join(
            'S002V01TUSUA', 'PERS_IDUS', '=', 'USUA_IDUS'
        )->join(
            'S002V01TEQMA', 'PERS_EQTR', '=', 'EQMA_IDEQ'
        )->get()->all();

        foreach($members as $key=>$member){
            $member->IDEMPLEADO = $this->encryptionController->encrypt($member->IDEMPLEADO);
            $member->IDUSUARIO = $this->encryptionController->encrypt($member->IDUSUARIO);

            $members[$key] = $member;
        }

        $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,
            'S002V01M11GPRS',
            'S002V01F01AETR',
            'S002V01P03DETR',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó los integrantes del equipo de trabajo " . $team->EQMA_NOMB . ".",
            $idUser,
            $nowStr,
            'S002V01S04GETR'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO.', $members);
    }
    public function updateWorkteam(Request $request) {
        DB::enableQueryLog();

        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'WORKTEAM_ID' =>'required|string',
            'NAME' => 'required|string|max:75',
            'SPECIALITY' => 'required|json',
        ]);

        if($validator->fails()) {
            return $this->responseController->makeResponse(
                TRUE,
                'ERR_EQUIPO_TRABAJO_REG001: Uno o más errores encontrados',
                $this->responseController->makeErrors($validator->errors()->messages()),
                400
            );
        }

        $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_NULI', '=', $form['linea']],
            ['USUA_IDUS', '=', $idUser],
        ])->first();

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

        $workteam = $this->encryptionController->decrypt($form['WORKTEAM_ID']);
        if(!$workteam){
            return $this->responseController->makeResponse(true, 'El ID equipo de trabajo solicitado no está encriptado correctamente', [], 400);
        }

        $team = DB::table('S002V01TEQMA')->where([
            ['EQMA_IDEQ', '=', $workteam],
            ['EQMA_NULI', '=', $form['linea']],
        ])->first();

        if(is_null($team)){
            return $this->responseController->makeResponse(true, 'El equipo de trabajo consultado no está registrado', [], 404);
        }

        $teams = DB::table('S002V01TEQMA')->where([
            ['EQMA_NOMB', '=', $form['NAME']],
            ['EQMA_NULI', '=', $form['linea']],
            ['EQMA_ESTA', '=', 'Activo'],
        ])->get()->all();

        if(count($teams) > 0){
            return $this->responseController->makeResponse(true, "El nombre $form[NAME] ya fue tomado", [], 401);
        }

        $specialtiesArr = json_decode($form['SPECIALITY'], true);
        foreach($specialtiesArr as $key=>$specialty){
            $specialtyCode = $this->encryptionController->decrypt($specialty);
            if(!$specialtyCode){
                return $this->responseController->makeResponse(true, "El ID del elemento en la posición $key del arreglo de especialidades no fue encriptado correctamente.", [], 400);
            }

            $specialtyObj = DB::table('S002V01TGEES')->where([
                ['GEES_NULI', '=', $form['linea']],
                ['GEES_COES', '=', $specialtyCode]
            ])->first();

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

            $specialtiesArr[$key] = $specialtyCode;
        }
        
        $specialtiesStr = json_encode($specialtiesArr);
        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();

        DB::table('S002V01TEQMA')->where([
            ["EQMA_IDEQ", '=', $workteam],
            ['EQMA_NULI', '=', $form['linea']]
        ])->update([
            "EQMA_NOMB" => $form['NAME'],
            "EQMA_ESPE" => $specialtiesStr,
            "EQMA_USMO" => $idUser,
            "EQMA_FEMO" => $nowStr,
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M11GPRS',
            'S002V01F01AETR',
            'S002V01P02REET',
            'Actualización',
            "El usuario $name (" . $usr->USUA_IDUS . ") actualizó el equipo de trabajo #$workteam.",
            $idUser,
            $nowStr,
            'S002V01S04GETR'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO.');
    }
    public function updateToInactiveStatus(Request $request) {
        DB::enableQueryLog();

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

        if($validator->fails()) {
            return $this->responseController->makeResponse(
                TRUE,
                'ERR_EQUIPO_TRABAJO_REG001: Uno o más errores encontrados',
                $this->responseController->makeErrors($validator->errors()->messages()),
                400
            );
        }

        $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_NULI', '=', $form['linea']],
            ['USUA_IDUS', '=', $idUser],
        ])->first();

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

        $workteam = $this->encryptionController->decrypt($form['WORKTEAM_ID']);
        if(!$workteam){
            return $this->responseController->makeResponse(true, 'El ID equipo de trabajo solicitado no está encriptado correctamente', [], 400);
        }

        $team = DB::table('S002V01TEQMA')->where([
            ['EQMA_IDEQ', '=', $workteam],
            ['EQMA_NULI', '=', $form['linea']],
        ])->first();

        if(is_null($team)){
            return $this->responseController->makeResponse(true, 'El equipo de trabajo consultado no está registrado', [], 404);
        }

        $members = DB::table('S002V01TPERS')->select([
            DB::raw('TRIM(CONCAT(USUA_NOMB, " " , USUA_APPA, " ", COALESCE(USUA_APMA,""))) as NAME'),
            'PERS_IDPE AS IDEMPLEADO',
            'PERS_IDUS AS IDUSUARIO'
        ])->where([
            ['EQMA_IDEQ', '=', $workteam],
            ['EQMA_NULI', '=', $form['linea']],
            ['PERS_ESTA', '=', 'Activo'],
        ])->join(
            'S002V01TUSUA', 'PERS_IDUS', '=', 'USUA_IDUS'
        )->join(
            'S002V01TEQMA', 'PERS_EQTR', '=', 'EQMA_IDEQ'
        )->get()->all();

        if(count($members) > 0){
            return $this->responseController->makeResponse(true, 'El equipo de trabajo que desea eliminar tiene usuarios relacionados.', [], 401);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();

        DB::table('S002V01TEQMA')->where([
            ["EQMA_IDEQ", '=', $workteam],
            ['EQMA_NULI', '=', $form['linea']]
        ])->update([
            "EQMA_ESTA" => 'Eliminado',
            "EQMA_USMO" => $idUser,
            "EQMA_FEMO" => $nowStr,
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M11GPRS',
            'S002V01F01AETR',
            'S002V01P01CETR',
            'Eliminación',
            "El usuario $name (" . $usr->USUA_IDUS . ") eliminó el equipo de trabajo #$workteam.",
            $idUser,
            $nowStr,
            'S002V01S04GETR'
        );

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


    public function storeContract(Request $request) {
        DB::enableQueryLog();
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'CONTRACT_NAME' => 'required|string|max:100',
            'WORK_ORDER' => 'required|string',
            'CONTRACT_TYPE' => 'required|string|in:Subcontratista,Interno',
            'DENOMINATION' => 'required|string',
            'START_DATE' => 'required|date',
            'END_DATE' => 'required|date',
            'CONTRACT_COST' => 'required|string',
            'DOCUMENT' => 'required|json',
        ]);

        if ($validator->fails()) {
            return $this->responseController->makeResponse(
                TRUE,
                'ERR_CONTRATO_REG001: Uno o más errores encontrados',
                $this->responseController->makeErrors($validator->errors()->messages()),
                400
            );
        }

        $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);
        }

        $workOrder = $this->encryptionController->decrypt($form['WORK_ORDER']);
        if(!$workOrder){
            return $this->responseController->makeResponse(true, 'La información de la orden seleccionada no está encriptada correctamente.', [], 400);
        }

        $workOrderArr = explode('|', $workOrder);
        if(count($workOrderArr) != 3){
            return $this->responseController->makeResponse(true, 'El formato de la orden seleccionada es incorrecto.', [], 400);
        }

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

        $order = null;
        $idOrderStr = null;
        if($workOrderArr[1] == 'MP'){
            $idOrderArr = explode('|', $idOrder);
            $order = DB::table('S002V01TBEOT')->join('S002V01TOTPR', 'OTPR_IDOT', '=', 'BEOT_IDOT')->where([
                ['BEOT_NULI', '=', $form['linea']],
                ['BEOT_IDRE', '=', $idOrderArr[0]]
            ])->first();

            $idOrderStr = $idOrderArr[0];
        }else if($workOrderArr[1] == 'MC'){
            if($workOrderArr[2] == 'S'){
                $order = DB::table('S002V01TOTCO')->where([
                    ['OTCO_NULI', '=', $form['linea']],
                    ['OTCO_IDOT', '=', $idOrder],
                ])->first();
            }else if($workOrderArr[2] == 'N'){
                $order = DB::table('S002V01TFCPE')->where([
                    ['FCPE_NULI', '=', $form['linea']],
                    ['FCPE_IDRE', '=', $idOrder]
                ])->first();
            }

            $idOrderStr = $idOrder;
        }

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

        $idEmployee = $this->encryptionController->decrypt($form['DENOMINATION']);
        if(!$idEmployee){
            return $this->responseController->makeResponse(true, 'El ID del empleado seleccionado no está encriptado correctamente.', [], 400);
        }

        $employee = DB::table('S002V01TPERS')->where([
            ['PERS_NULI', '=', $form['linea']],
            ['PERS_IDPE', '=', $idEmployee]
        ])->first();
        
        if(is_null($employee)){
            return $this->responseController->makeResponse(true, 'El empleado seleccionado no existe.', [], 404);
        }

        $orderType = $workOrderArr[1] == 'MP' ? 'Preventivo' : 'Correctivo';
        $contract = DB::table('S002V01TCONT')->where([
            ['CONT_NULI', '=', $form['linea']],
            ['CONT_IDEM', '=', $idEmployee],
            ['CONT_IDOT', '=', $idOrder],
            ['CONT_TOTR', '=', $orderType],
        ])->first();

        if(!is_null($contract)){
            return $this->responseController->makeResponse(true, "El empleado #$idEmployee ya tiene un contrato relacionado a la orden de trabajo $orderType #$idOrder", [], 404);
        }
        
        $docsArr = json_decode($form['DOCUMENT'], true);
        $finalDocsArr = [];
        foreach($docsArr as $doc){
            $idFileDec = $this->encryptionController->decrypt($doc['id']);
            $tempFile = DB::table('S002V01TARTE')->where([
                ['ARTE_NULI', '=', $form['linea']],
                ['ARTE_IDAR', '=', $idFileDec],
            ])->first();
    
            if(is_null($tempFile)){
                return $this->responseController->makeResponse(true, "El archivo $doc[name] no está registrado.", [], 404);
            }

            $cldo = "CO";
            $finalFile = $this->documentManagementController->moveFinalFile($form['linea'], "GPRS", $cldo, $tempFile, $idUser);
    
            if(!$finalFile[0]){
                return $this->responseController->makeResponse(true, $finalFile[1], [], 400);
            }else{
                $finalDocsArr[] = $finalFile[1];
            }
        }

        $contractCostStr = $this->encryptionController->decrypt($form['CONTRACT_COST']);
        if(!$contractCostStr){
            return $this->responseController->makeResponse(true, 'El costo del contrato no fue encriptado correctamente.', [], 400);
        }

        $contractCostNum = floatval($contractCostStr);
        $lido = json_encode($finalDocsArr);
        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $idContract = DB::table('S002V01TCONT')->insertGetId([
            'CONT_NULI' => $form['linea'],
            'CONT_NOCO' => $form['CONTRACT_NAME'],
            'CONT_IDEM' => $idEmployee,
            'CONT_IDOT' => $idOrderStr,
            'CONT_TOTR' => $orderType,
            'CONT_TIPO' => $form['CONTRACT_TYPE'],
            'CONT_FEIN' => $form['START_DATE'],
            'CONT_FEFI' => $form['END_DATE'],
            'CONT_COST' => $contractCostNum,
            'CONT_LIDO' => $lido,
            'CONT_USRE' => $idUser,
            'CONT_FERE' => $nowStr,
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M11GPRS',
            'S002V01F02ADCN',
            'S002V01P02RECO',
            'Registro',
            "El usuario $name (" . $usr->USUA_IDUS . ") registró el contrato #$idContract.",
            $idUser,
            $nowStr,
            'S002V01S03GEIN'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO');
    }
    public function getConsultOfContracts($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);
        }

        $contracts = DB::table('S002V01TCONT')->select([
            'CONT_IDCO AS CONTRACT_ID',
            'CONT_NOCO AS CONTRACT_NAME',
            'CONT_IDEM AS DENOMINATION',
            'CONT_IDOT AS WORK_ORDER_ID',
            'CONT_TOTR AS WORK_ORDER_TYPE',
            'CONT_TIPO AS CONTRACT_TYPE',
            'CONT_FEIN AS START_DATE',
            'CONT_FEFI AS END_DATE',
            'CONT_COST AS CONTRACT_COST',
            'CONT_LIDO AS DOCUMENT',
            'CONT_USRE AS REGISTERED_BY',
            'CONT_FERE AS REGISTER_DATE',
            'CONT_USMO AS MODIFIED_BY',
            'CONT_FEMO AS MODIFICATION_DATE',
        ])->where('CONT_NULI', '=', $line)->get()->all();

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        foreach($contracts as $key=>$contract){
            $contract->CONTRACT_ID = $this->encryptionController->encrypt($contract->CONTRACT_ID);
            $contract->CONTRACT_COST = $this->encryptionController->encrypt($contract->CONTRACT_COST);
            $docsArr = json_decode($contract->DOCUMENT);

            $docsFN = [];
            foreach($docsArr as $keyDoc=>$doc){
                $docIDArr = explode('=', $doc);
                $codeArr = explode('-', $docIDArr[0]);

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

                if (!is_null($file)) {
                    $docsFN[] = [
                        'id' => $this->encryptionController->encrypt($doc),
                        'name' => $doc,
                        'size' => $file->AFAL_TAMA
                    ];
                }
            }

            $contract->DOCUMENT = json_encode($docsFN);
            $usrReg = DB::table('S002V01TUSUA')->where([
                ['USUA_NULI', '=', $line],
                ['USUA_IDUS', '=', $contract->REGISTERED_BY],
            ])->first();

            $contract->REGISTERED_BY = $this->functionsController->joinName(
                $usrReg->USUA_NOMB, $usrReg->USUA_APPA, $usrReg->USUA_APMA
            ) . " (" . $contract->REGISTERED_BY . ")";

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

            $employee = DB::table('S002V01TPERS')->join('S002V01TUSUA', 'USUA_IDUS', '=', 'PERS_IDUS')->where([
                ['PERS_NULI', '=', $line],
                ['PERS_IDPE', '=', $contract->DENOMINATION],
            ])->first();

            $contract->DENOMINATION = $this->functionsController->joinName(
                $employee->USUA_NOMB, $employee->USUA_APPA, $employee->USUA_APMA
            ) . ' (' . $employee->PERS_IDPE . ') (' . $employee->PERS_IDUS . ')';

            $contract->DURATION = $this->functionsController->getDatesDifference($contract->START_DATE, $contract->END_DATE);
            $contracts[$key] = $contract;

            $endDate = new Carbon($contract->END_DATE);
            $status = $now->gt($endDate) ? 'Expirado' : 'Activo';
            $contract->STATUS = $status;
        }

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M11GPRS',
            'S002V01F02ADCN',
            'S002V01P01COCO',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó los contratos registrados.",
            $idUser,
            $nowStr,
            'S002V01S03GEIN'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $contracts);
    }
    public function getConsultOfInterventions($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);
        }

        $correctiveInterventions = DB::table('S002V01TOTCO')->select([
            'OTCO_IDOT AS IDREG',
            'OTCO_IDOT AS IDORDER',
            DB::raw("CONCAT(
                OTCO_EQIN, ' - ',
                EQUI_TIPO, ' - ',
                EQUI_MODE, ' (',
                EQUI_IDEQ, ')'
            ) AS EQUIPMENT"),
            DB::raw("'C' AS ORDER_TYPE"),
            'OTCO_ESOR AS ACTION_TYPE',
            'OTCO_FIFA AS START_DATE',
            'OTCO_FTIN AS END_DATE'
        ])->join('S002V01TEQUI', 'EQUI_COEQ', '=', 'OTCO_EQIN');

        $maintenanceInterventions = DB::table('S002V01TBEOT')->select([
            DB::raw('CONCAT(BEOT_IDRE, "|", BEOT_FEPR) AS IDREG'),
            'OTPR_IDOT AS IDORDER',
            DB::raw("CONCAT(
                OTPR_EQIN, ' - ',
                EQUI_TIPO, ' - ',
                EQUI_MODE, ' (',
                EQUI_IDEQ, ')'
            ) AS EQUIPMENT"),
            DB::raw("'P' AS ORDER_TYPE"),
            'BEOT_TIAC AS ACTION_TYPE',
            'BEOT_FEEJ AS START_DATE',
            'BEOT_FEFI AS END_DATE'
        ])->join('S002V01TOTPR', 'OTPR_IDOT', '=', 'BEOT_IDOT')
        ->join('S002V01TEQUI', 'EQUI_COEQ', '=', 'OTPR_EQIN')
        ->union($correctiveInterventions)->orderBy('IDREG', 'desc')->get()->all();

        foreach($maintenanceInterventions as $key=>$val){
            $val->IDREG = $this->encryptionController->encrypt($val->IDREG);
            $val->IDORDER = $this->encryptionController->encrypt($val->IDORDER);
            $val->EQUIPMENT = $this->encryptionController->encrypt($val->EQUIPMENT);

            $preventiveInterventions[$key] = $val;
        }

        $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,
            'S002V01M11GPRS',
            'S002V01F01ADIN',
            '-',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó las intervenciones registradas.",
            $idUser,
            $nowStr,
            'S002V01S03GEIN'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $preventiveInterventions);
    }
    public function getAllRegisteredSpecialties($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);
        }

        $specialties = DB::table('S002V01TGEES')->select([
            'GEES_COES AS CODIGO_ESPECIALIDAD',
            'GEES_NOES AS NOMBRE_ESPECIALIDAD',
            'GEES_ESTA AS ESTADO',
            DB::raw("CONCAT(USRE.USUA_NOMB, ' ', USRE.USUA_APPA, IF(ISNULL(USRE.USUA_APMA), '', ' '), IF(ISNULL(USRE.USUA_APMA), '', USRE.USUA_APMA), ' (', GEES_USRE, ')') AS USRREG"),
            'GEES_FERE AS FECREG',
            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), ' (', GEES_USMO, ')')) AS USRMOD"),
            'GEES_FEMO AS FECMOD',
        ])->join(DB::raw('S002V01TUSUA USRE'), 'USRE.USUA_IDUS', '=', 'GEES_USRE')
        ->leftJoin(DB::raw('S002V01TUSUA USMO'), 'USMO.USUA_IDUS', '=', 'GEES_USMO')
        ->where('GEES_NULI', '=', $line)->get()->all();

        foreach($specialties as $key=>$specialty){
            $specialty->CODIGO_ESPECIALIDAD = $this->encryptionController->encrypt($specialty->CODIGO_ESPECIALIDAD);
            $specialties[$key] = $specialty;
        }

        $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,
            'S002V01M11GPRS',
            '-',
            '-',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") las especialidades registradas.",
            $idUser,
            $nowStr,
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $specialties);
    }
    public function registerSpecialty(Request $request) {
        DB::enableQueryLog();

        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'specialty_code' => 'required|string',
            'spacialty_name' => 'required|string|max:100',
        ]);

        if ($validator->fails()) {
            return $this->responseController->makeResponse(
                TRUE,
                'ERR_CONTRATO_REG001: Uno o más errores encontrados',
                $this->responseController->makeErrors($validator->errors()->messages()),
                400
            );
        }

        $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);
        }

        $specialtyCode = $this->encryptionController->decrypt($form['specialty_code']);
        if(!$specialtyCode){
            return $this->responseController->makeResponse(true, 'El código de la especialidad no fue encriptado correctamente.', [], 400);
        }

        $filtCode = DB::table('S002V01TGEES')->where([
            ['GEES_NULI', '=', $form['linea']],
            ['GEES_COES', '=', $specialtyCode],
            ['GEES_ESTA', '=', 'Activo']
        ])->first();

        if(!is_null($filtCode)){
            return $this->responseController->makeResponse(true, 'El código ingresado ya se encuentra registrado.', [], 400);
        }

        $filtName = DB::table('S002V01TGEES')->where([
            ['GEES_NULI', '=', $form['linea']],
            ['GEES_NOES', '=', $form['spacialty_name']],
            ['GEES_ESTA', '=', 'Activo']
        ])->first();

        if(!is_null($filtName)){
            return $this->responseController->makeResponse(true, 'El nombre ingresado ya se encuentra registrado.', [], 400);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        DB::table('S002V01TGEES')->insert([
            'GEES_NULI' => $form['linea'],
            'GEES_COES' => $specialtyCode,
            'GEES_NOES' => $form['spacialty_name'],
            'GEES_USRE' => $idUser,
            'GEES_FERE' => $nowStr
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M11GPRS',
            'S002V01F02ADCN',
            'S002V01P02RECO',
            'Registro',
            "El usuario $name (" . $usr->USUA_IDUS . ") registró la especialidad $form[spacialty_name]($specialtyCode).",
            $idUser,
            $nowStr,
            'S002V01S03GEIN'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO');
    }
    public function getSpecialtyByCode($code, $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);
        }

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

        $specialty = DB::table('S002V01TGEES')->select([
            'GEES_COES AS CODIGO_ESPECIALIDAD',
            'GEES_NOES AS NOMBRE_ESPECIALIDAD',
            'GEES_ESTA AS ESTADO',
            DB::raw("CONCAT(USRE.USUA_NOMB, ' ', USRE.USUA_APPA, IF(ISNULL(USRE.USUA_APMA), '', ' '), IF(ISNULL(USRE.USUA_APMA), '', USRE.USUA_APMA), ' (', GEES_USRE, ')') AS USRREG"),
            'GEES_FERE AS FECREG',
            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), ' (', GEES_USMO, ')')) AS USRMOD"),
            'GEES_FEMO AS FECMOD',
        ])->join(DB::raw('S002V01TUSUA USRE'), 'USRE.USUA_IDUS', '=', 'GEES_USRE')
        ->leftJoin(DB::raw('S002V01TUSUA USMO'), 'USMO.USUA_IDUS', '=', 'GEES_USMO')
        ->where([
            ['GEES_NULI', '=', $line],
            ['GEES_COES', '=', $code]
        ])->first();

        if(is_null($specialty)){
            return $this->responseController->makeResponse(true, 'La especialidad consultada no está registrada.', [], 404);
        }

        $specialty->CODIGO_ESPECIALIDAD = $this->encryptionController->encrypt($specialty->CODIGO_ESPECIALIDAD);
        $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,
            'S002V01M11GPRS',
            '-',
            '-',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") la especialidad $code.",
            $idUser,
            $nowStr,
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $specialty);
    }
    public function updateSpecialty(Request $request) {
        DB::enableQueryLog();

        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'specialty_code' => 'required|string',
            'spacialty_name' => 'required|string|max:75',
        ]);

        if ($validator->fails()) {
            return $this->responseController->makeResponse(
                TRUE,
                'ERR_CONTRATO_REG001: Uno o más errores encontrados',
                $this->responseController->makeErrors($validator->errors()->messages()),
                400
            );
        }

        $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);
        }

        $specialtyCode = $this->encryptionController->decrypt($form['specialty_code']);
        if(!$specialtyCode){
            return $this->responseController->makeResponse(true, 'El código de la especialidad no fue encriptado correctamente.', [], 400);
        }

        $specialty = DB::table('S002V01TGEES')->where([
            ['GEES_NULI', '=', $form['linea']],
            ['GEES_COES', '=', $specialtyCode],
        ])->first();

        if(is_null($specialty)){
            return $this->responseController->makeResponse(true, 'La especialidad que desea actualizar no existe.', [], 404);
        }else if($specialty->GEES_ESTA == 'Eliminado'){
            return $this->responseController->makeResponse(true, 'La especialidad que desea actualizar está eliminada.', [], 404);
        }

        $filtName = DB::table('S002V01TGEES')->where([
            ['GEES_NULI', '=', $form['linea']],
            ['GEES_NOES', '=', $form['spacialty_name']],
            ['GEES_ESTA', '=', 'Activo']
        ])->first();

        if(!is_null($filtName)){
            return $this->responseController->makeResponse(true, 'El nombre ingresado ya se encuentra registrado.', [], 400);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        DB::table('S002V01TGEES')->where([
            ['GEES_NULI', '=', $form['linea']],
            ['GEES_COES', '=', $specialtyCode],
        ])->update([
            'GEES_NOES' => $form['spacialty_name'],
            'GEES_USMO' => $idUser,
            'GEES_FEMO' => $nowStr
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M11GPRS',
            'S002V01F02ADCN',
            'S002V01P02RECO',
            'Actualización',
            "El usuario $name (" . $usr->USUA_IDUS . ") actualizó la especialidad $specialtyCode.",
            $idUser,
            $nowStr,
            'S002V01S03GEIN'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $form['linea']);
        return $this->responseController->makeResponse(false, 'EXITO');
    }
    public function deleteSpecialty(Request $request) {
        DB::enableQueryLog();

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

        if ($validator->fails()) {
            return $this->responseController->makeResponse(
                TRUE,
                'ERR_CONTRATO_REG001: Uno o más errores encontrados',
                $this->responseController->makeErrors($validator->errors()->messages()),
                400
            );
        }

        $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);
        }

        $specialtyCode = $this->encryptionController->decrypt($form['specialty_code']);
        if(!$specialtyCode){
            return $this->responseController->makeResponse(true, 'El código de la especialidad no fue encriptado correctamente.', [], 400);
        }

        $specialty = DB::table('S002V01TGEES')->where([
            ['GEES_NULI', '=', $form['linea']],
            ['GEES_COES', '=', $specialtyCode],
        ])->first();

        if(is_null($specialty)){
            return $this->responseController->makeResponse(true, 'La especialidad que desea actualizar no existe.', [], 404);
        }else if($specialty->GEES_ESTA == 'Eliminado'){
            return $this->responseController->makeResponse(true, 'La especialidad que desea actualizar está eliminada.', [], 404);
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        DB::table('S002V01TGEES')->where([
            ['GEES_NULI', '=', $form['linea']],
            ['GEES_COES', '=', $specialtyCode],
        ])->update([
            'GEES_ESTA' => 'Eliminado',
            'GEES_USMO' => $idUser,
            'GEES_FEMO' => $nowStr
        ]);

        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        $idac = $this->functionsController->registerActivity(
            $form['linea'],
            'S002V01M11GPRS',
            'S002V01F02ADCN',
            'S002V01P02RECO',
            'Eliminación',
            "El usuario $name (" . $usr->USUA_IDUS . ") eliminó la especialidad $specialtyCode.",
            $idUser,
            $nowStr,
            'S002V01S03GEIN'
        );

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