SistemaMantenimiento/sistema-mantenimiento-back/app/Http/Controllers/EmployeeController.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 PhpOffice\PhpSpreadsheet\IOFactory;
use Illuminate\Database\Query\Builder;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use stdClass;
use Throwable;

class EmployeeController extends Controller{
    private $responseController;
    private $encryptionController;
    private $documentManagementController;
    private $functionsController;

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

    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_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();

        $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|string|max:75',
            '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']
                ];
            }
        }

        $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' => $form['SPECIALITY'],
            '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,
        ]);

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

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

            $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 . ")";

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

        $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 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|string|max:75',
            '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;

        $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' => $form['SPECIALITY'],
            '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,
        ]);

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