SAMPracticantes/sistema-mantenimiento-back/app/Http/Controllers/SubcontractController.php

<?php
/*
Nombre del programador:         Cordourier Rojas Mathew
Ultima fecha de modificación:   [ 03 / Marzo / 2023 ]
Descripción:                    Controlador del submodulo Subcontrataciones. Perteneciente al Módulo 13 - Gestion del Personal de Mantenimiento
*/

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 PhpOffice\PhpSpreadsheet\Spreadsheet;
use stdClass;
use Throwable;

class SubcontractController extends Controller
{
    private $response_controller;
    private $encrypt_controller;
    private $documents_controller;
    public function __construct()
    {
        $this->response_controller = new ResponseController();
        $this->encrypt_controller = new EncryptionController();
        $this->documents_controller = new DocumentsController();
    }

    // Metodo de consulta de datos especificos por subcontratistas
    public function getConsultOfSubcontratists($line_number)
    {
        try {

            $subcontratists = DB::table('S002V01TPESU')
                ->select(
                    'PESU_IDPS as ID_SUBCONTRATIST',
                    DB::raw('CONCAT(PESU_RASO, " " , COALESCE(PESU_REFI, "")) AS NAME'),
                    '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_number)
                ->get();

            // Verifica que el objeto no este vacio
            if (!isset($subcontratists[0]) && empty($subcontratists[0])) {
                return $this->response_controller
                ->makeResponse(TRUE, 'ERR_SUBCONTRATISTA_REG001: No se encontraron datos', $subcontratists, 500);
            }

            $users_register_name = DB::table('S002V01TUSUA')
                ->select('USUA_IDUS AS USERID', DB::raw('TRIM(CONCAT(USUA_NOMB, " " , USUA_APPA, " ", COALESCE(USUA_APMA,""))) AS NAME'))
                ->where('USUA_NULI', '=', $line_number)
                ->get();

            // Verifica si el objeto esta vacio
            if (!isset($users_register_name[0]) && empty($users_register_name[0])) {
                return $this->response_controller
                    ->makeResponse(TRUE, 'ERR_USUARIO_REG002: No se encontraron datos', $users_register_name, 500);
            }

            $interventions = DB::table('S002V01TPESU')
                ->select('S002V01TPERS.PERS_IDPS as SUBCONTRATIST_ID', 'S002V01TINTE_P.INTE_IDIN as INTERVENTION_ID')
                ->where('S002V01TPESU.PESU_NULI', '=', $line_number)
                ->where('S002V01TPERS.PERS_NULI', '=', $line_number)
                ->where('S002V01TPEEM.PEEM_NULI', '=', $line_number)
                ->where('S002V01TEQMA.EQMA_NULI', '=', $line_number)
                ->where('S002V01TEMIN.EMIN_NULI', '=', $line_number)
                ->where('S002V01TINTE_P.INTE_NULI', '=', $line_number)
                ->groupBy('S002V01TPESU.PESU_IDPS', 'S002V01TINTE_P.INTE_IDIN')
                ->join('S002V01TPERS', 'S002V01TPESU.PESU_IDPS', '=', 'S002V01TPERS.PERS_IDPS')
                ->join('S002V01TPEEM', 'S002V01TPERS.PERS_IDPE', '=', 'S002V01TPEEM.PEEM_IDPE')
                ->join('S002V01TEQMA', 'S002V01TPEEM.PEEM_IDEM', '=', 'S002V01TEQMA.EQMA_IDEQ')
                ->join('S002V01TEMIN', 'S002V01TEQMA.EQMA_IDEQ', '=', 'S002V01TEMIN.EMIN_IDEM')
                ->join('S002V01TINTE_P', 'S002V01TEMIN.EMIN_IDIN', '=', 'S002V01TINTE_P.INTE_IDIN')
                ->get();

            // Verifica si el objeto esta vacio
            /* if (!isset($interventions[0]) && empty($interventions[0])) {
                return $this->response_controller
                    ->makeResponse(TRUE, 'ERR_EQUIPO_INTERVENCIÓN_REG003: No se encontraron datos con intervenciones', $interventions, 500);
            } */

            // Se iteran los subcontratistas encontrados
            foreach ($subcontratists as $subcontratist) {
                $subcontratist->REGISTER_DATE = Carbon::create($subcontratist->REGISTER_DATE)->format("d-m-Y h:i:s A");
                $subcontratist->UPDATE_DATE != null ? $subcontratist->UPDATE_DATE = Carbon::create($subcontratist->UPDATE_DATE)->format("d-m-Y h:i:s A") : null;

                $subcontratist->NAME = trim($subcontratist->NAME);

                // Se adjunta el nombre completo del usuario que lo registro
                foreach ($users_register_name as $user_register) {
                    if ($subcontratist->REGISTERED_BY_USER == $user_register->USERID) {
                        $subcontratist->REGISTERED_BY_USER = $user_register->NAME;
                    }

                    if ($subcontratist->UPDATED_BY_USER == $user_register->USERID) {
                        $subcontratist->UPDATED_BY_USER = $user_register->NAME;
                    }

                }

                // Se adjunta el numero de intervenciones en las que participo el subcontratista
                $interventions_count = 0;
                foreach ($interventions as $intervention) {
                    if ($subcontratist->ID_SUBCONTRATIST == $intervention->SUBCONTRATIST_ID) {
                        $interventions_count++;
                    }
                }
                $subcontratist->INTERVENTIONS_COUNT = $interventions_count;

            }

            return $this->response_controller->makeResponse(FALSE, 'Consulta exitosa', $subcontratists);

        } catch (Throwable $e) {
            return $this->response_controller
                ->makeResponse(TRUE, 'ERR_SUBCONTRATISTA_REG004: Error inesperado', strtoupper($e->getMessage()), 500);
        }
    }

    // Metodo para la eliminación lógica de un subcontratista
    public function updateToInactiveStatus(Request $request, $id_subcontratist)
    {
        try {

            $request['SAVED_BY_USER'] = $this->encrypt_controller->decrypt($request->SAVED_BY_USER);
            $validator = Validator::make($request->all(), [
                "SAVED_BY_USER" => ['required', 'digits:10']
            ]);

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

            $user_register = DB::table('S002V01TUSUA')
                ->select('USUA_IDUS as ID_USER')
                ->where('USUA_IDUS', '=', $request->SAVED_BY_USER)
                ->where('USUA_NULI', '=', $request->LINE_NUMBER)
                ->first();

            // Verifica si el objeto está vacio
            if (!isset($user_register) && empty($user_register)) {
                return $this->response_controller
                    ->makeResponse(TRUE, "ERR_USUARIO_REG002: Tu usuario no es válido para eliminar subcontratistas", [], 500);
            }

            // Busca si el subcontratista existe
            $search_subcontratist = DB::table("S002V01TPESU")
                ->select("PESU_IDPS")
                ->where("PESU_IDPS", "=", $id_subcontratist)
                ->where('PESU_NULI', '=', $request->LINE_NUMBER)
                ->first();

            // Verifica si el objeto está vacio
            if (!isset($search_subcontratist) && empty($search_subcontratist)) {
                return $this->response_controller
                    ->makeResponse(TRUE, "ERR_SUBCONTRATISTA_REG003: No se encontró al subcontratista", $search_subcontratist, 500);
            }

            // Obtiene las intervenciones en las que está ocupado el subcontratista, si hay alguna
            $search_subcontratist = DB::table("S002V01TPERS")
                ->select("S002V01TINTE_P.INTE_NOMB")
                ->where("S002V01TPERS.PERS_IDPS", "=", $id_subcontratist)
                ->where("S002V01TINTE_P.INTE_ESTA", "=", "Activo")
                ->where("S002V01TEMIN.EMIN_ESTA", "=", "Activo")
                ->where('S002V01TPERS.PERS_NULI', '=', $request->LINE_NUMBER)
                ->where("S002V01TPEEM.PEEM_NULI", "=", $request->LINE_NUMBER)
                ->where("S002V01TEQMA.EQMA_NULI", "=", $request->LINE_NUMBER)
                ->where("S002V01TEMIN.EMIN_NULI", "=", $request->LINE_NUMBER)
                ->where("S002V01TINTE_P.INTE_NULI", "=", $request->LINE_NUMBER)
                ->groupBy('S002V01TINTE_P.INTE_NOMB')
                ->join("S002V01TPEEM", "S002V01TPERS.PERS_IDPE", "=", "S002V01TPEEM.PEEM_IDPE")
                ->join("S002V01TEQMA", "S002V01TPEEM.PEEM_IDEM", "=", "S002V01TEQMA.EQMA_IDEQ")
                ->join("S002V01TEMIN", "S002V01TEQMA.EQMA_IDEQ", "=", "S002V01TEMIN.EMIN_IDEM")
                ->join("S002V01TINTE_P", "S002V01TEMIN.EMIN_IDIN", "=", "S002V01TINTE_P.INTE_IDIN")
                ->get();

            // Verifica si el objeto contiene algo
            if (isset($search_subcontratist[0]) && !empty($search_subcontratist[0])) {
                return $this->response_controller
                    ->makeResponse(TRUE, "ERR_SUBCONTRATISTA_REG004: Subcontratista ocupado con intervenciones", $search_subcontratist, 500);
            } else {

                $UPDATE_DATE = Carbon::now()->timezone('America/Mexico_City')->toDateTimeString();

                $delete_subcontratist = DB::table('S002V01TPESU')
                    ->where('PESU_IDPS', $id_subcontratist)
                    ->where('PESU_NULI', '=', $request->LINE_NUMBER)
                    ->update([
                        "PESU_ESTA" => "Eliminado",
                        "PESU_USMO" => trim($request->SAVED_BY_USER),
                        "PESU_FEMO" => $UPDATE_DATE,
                        "PESU_FEAR" => DB::raw('CURRENT_TIMESTAMP')
                    ]);

                // Verifica si el subcontratista se inhabilitó correctamente
                if ($delete_subcontratist) {
                    $response = $this->response_controller->makeResponse(FALSE, "Eliminación exitosa");
                } else {
                    $response = $this->response_controller
                        ->makeResponse(TRUE, 'ERR_SUBCONTRATISTA_REG005: Algo salió mal, error eliminando al subcontratista', [], 500);
                }
            }

            return $response;

        } catch (Throwable $th) {
            return $this->response_controller
                ->makeResponse(TRUE, 'ERR_SUBCONTRATISTA_REG006: Error inesperado', strtoupper($th->getMessage()), 500);
        }
    }

    // Metodo para la activación lógica de un subcontratista
    public function updateToActiveStatus(Request $request, $id_subcontratist)
    {
        try {

            $request['SAVED_BY_USER'] = $this->encrypt_controller->decrypt($request->SAVED_BY_USER);
            $validator = Validator::make($request->all(), [
                "SAVED_BY_USER" => ['required', 'digits:10']
            ]);

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

            $user_register = DB::table('S002V01TUSUA')
                ->select('USUA_IDUS as ID_USER')
                ->where('USUA_IDUS', '=', $request->SAVED_BY_USER)
                ->where('USUA_NULI', '=', $request->LINE_NUMBER)
                ->first();

            // Verifica si el objeto está vacio
            if (!isset($user_register) && empty($user_register)) {
                return $this->response_controller
                    ->makeResponse(TRUE, "ERR_USUARIO_REG002: Tu usuario no es válido para activar subcontratistas", [], 500);
            }

            // Busca si el subcontratista existe
            $search_subcontratist = DB::table("S002V01TPESU")
                ->select("PESU_IDPS")
                ->where("PESU_IDPS", "=", $id_subcontratist)
                ->where('PESU_NULI', '=', $request->LINE_NUMBER)
                ->first();

            // Verifica si el objeto está vacio
            if (!isset($search_subcontratist) && empty($search_subcontratist)) {
                return $this->response_controller
                    ->makeResponse(TRUE, "ERR_SUBCONTRATISTA_REG003: No se encontró al subcontratista", $search_subcontratist, 500);
            }

                $UPDATE_DATE = Carbon::now()->timezone('America/Mexico_City')->toDateTimeString();

                $delete_subcontratist = DB::table('S002V01TPESU')
                    ->where('PESU_IDPS', $id_subcontratist)
                    ->where('PESU_NULI', '=', $request->LINE_NUMBER)
                    ->update([
                        "PESU_ESTA" => "Activo",
                        "PESU_USMO" => trim($request->SAVED_BY_USER),
                        "PESU_FEMO" => $UPDATE_DATE,
                        "PESU_FEAR" => DB::raw('CURRENT_TIMESTAMP')
                    ]);

                // Verifica si el subcontratista se inhabilitó correctamente
                if ($delete_subcontratist) {
                    $response = $this->response_controller->makeResponse(FALSE, "Activación exitosa");
                } else {
                    $response = $this->response_controller
                        ->makeResponse(TRUE, 'ERR_SUBCONTRATISTA_REG005: Algo salió mal, error activando al subcontratista', [], 500);
                }

            return $response;

        } catch (Throwable $th) {
            return $this->response_controller
                ->makeResponse(TRUE, 'ERR_SUBCONTRATISTA_REG006: Error inesperado', strtoupper($th->getMessage()), 500);
        }
    }

    // Metodo para guardar un nuevo subcontratista
    public function storeSubcontratist(Request $request)
    {

        $REGISTER_DATE = Carbon::now()->timezone('America/Mexico_City')->toDateTimeString();
        $request['SAVED_BY_USER'] = $this->encrypt_controller->decrypt($request->SAVED_BY_USER);
        $request['TELEPHONE1'] = $this->encrypt_controller->decrypt($request->TELEPHONE1);
        $request['TELEPHONE2'] = $this->encrypt_controller->decrypt($request->TELEPHONE2);
        $request['EMAIL'] = $this->encrypt_controller->decrypt($request->EMAIL);
        $request['RFC'] = $this->encrypt_controller->decrypt($request->RFC);
        $request['TAX'] = $this->encrypt_controller->decrypt($request->TAX);

        try {

            $validator = Validator::make($request->all(), [
                "RFC" => ['max:13'],
                "SOCIAL_REASON" => ['required', 'max:150'],
                "TAX_REFERENCE" => ['max:15'],
                "CONTRACT_TYPE" => ['required'],
                "EMAIL" => ['required', 'email', 'max:150'],
                "FOREIGNER" => ['required'],
                "TAX" => ['max:13'],
                "STREET" => ['required', 'max:50'],
                "EXTERIOR_NUMBER" => ['required','string'],
                "SUBURB" => ['required', 'max:50'],
                "CITY" => ['required', 'max:50'],
                "POSTAL_CODE" => ['required', 'digits:5'],
                "FEDERAL_ENTITY" => ['required', 'max:50'],
                "COUNTRY" => ['required'],
                "TELEPHONE1" => ['required', 'digits_between:7,11'],
                "LADA1" => ['required', 'max:5'],
                "SAVED_BY_USER" => ['required', 'digits:10'],
                "INTERIOR_NUMBER" => ['string'],
                "TELEPHONE2" => ['digits_between:7,11'],
                "LADA2" => ['max:5'],
                "LINE_NUMBER" => ['required', 'digits:1']
            ]);

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

            // Busca si hay duplicados con los subcontratistas ya guardados
            $subcontratists = DB::table('S002V01TPESU')
                ->select("PESU_RASO as SOCIAL_REASON", "PESU_XRFC as RFC", "PESU_CORR as EMAIL")
                ->where('PESU_RASO', '=', $request->SOCIAL_REASON)
                ->orWhere('PESU_XRFC', '=', $request->RFC)
                ->orWhere('PESU_CORR', '=', $request->EMAIL)
                ->get();

            // Valida que los campos no esten repetidos con los que ya se encuentran en la base de datos
            if (isset($subcontratists[0]) && !empty($subcontratists[0])) {
                $duplicated_data = [];
                foreach ($subcontratists as $subcontratist) {
                    if ($subcontratist->SOCIAL_REASON == $request->SOCIAL_REASON) {
                        $duplicated_data[] = "Razon Social: " . $subcontratist->SOCIAL_REASON;
                    }

                    if ($subcontratist->RFC == $request->RFC) {
                        $duplicated_data[] = "RFC: " . $subcontratist->RFC;
                    }

                    if ($subcontratist->EMAIL == $request->EMAIL) {
                        $duplicated_data[] = "Email: " . $subcontratist->EMAIL;
                    }
                }

                if (isset($duplicated_data[0]) && !empty($duplicated_data[0])) {
                    return $this->response_controller->makeResponse(TRUE, 'ERR_SUBCONTRATISTA_REG002: Datos duplicados', $duplicated_data, 500);
                }
            }

            $user_register = DB::table('S002V01TUSUA')
                ->select('USUA_IDUS as ID_USER')
                ->where('USUA_IDUS', '=', $request->SAVED_BY_USER)
                ->where('USUA_NULI', '=', $request->LINE_NUMBER)
                ->first();

            // Verifica si el objeto esta vacio
            if (!isset($user_register) && empty($user_register)) {
                return $this->response_controller
                    ->makeResponse(TRUE, "ERR_USUARIO_REG003: Tu usuario no es válido para registrar subcontratistas", [], 500);
            }

            $insert_subcontratist = DB::table('S002V01TPESU')->insert([
                "PESU_NULI" => $request->LINE_NUMBER,
                "PESU_RASO" => $request->SOCIAL_REASON != null ? trim($request->SOCIAL_REASON) : null,
                "PESU_REFI" => $request->TAX_REFERENCE != null ? trim($request->TAX_REFERENCE) : null,
                "PESU_XRFC" => $request->RFC != null ? trim($request->RFC) : null,
                "PESU_TIPO" => $request->CONTRACT_TYPE != null ? trim($request->CONTRACT_TYPE) : null,
                "PESU_CORR" => $request->EMAIL != null ? trim($request->EMAIL) : null,
                "PESU_EXTR" => $request->FOREIGNER != null ? trim($request->FOREIGNER) : null,
                "PESU_TAID" => $request->TAX != null ? trim($request->TAX) : null,
                "PESU_CALL" => $request->STREET != null ? trim($request->STREET) : null,
                "PESU_NOEX" => $request->EXTERIOR_NUMBER != null ? trim($request->EXTERIOR_NUMBER) : null,
                "PESU_COLO" => $request->SUBURB != null ? trim($request->SUBURB) : null,
                "PESU_CIUD" => $request->CITY != null ? trim($request->CITY) : null,
                "PESU_COPO" => $request->POSTAL_CODE != null ? trim($request->POSTAL_CODE) : null,
                "PESU_ENFE" => $request->FEDERAL_ENTITY != null ? trim($request->FEDERAL_ENTITY) : null,
                "PESU_IDPA" => $request->COUNTRY != null ? trim($request->COUNTRY) : null,
                "PESU_TEL1" => $request->TELEPHONE1 != null ? trim($request->TELEPHONE1) : null,
                "PESU_LAT1" => $request->LADA1 != null ? trim($request->LADA1) : null,
                "PESU_TEL2" => $request->TELEPHONE2 != null ? trim($request->TELEPHONE2) : null,
                "PESU_LAT2" => $request->LADA2 != null ? trim($request->LADA2) : null,
                "PESU_USRE" => $request->SAVED_BY_USER != null ? trim($request->SAVED_BY_USER) : null,
                "PESU_NOIN" => $request->INTERIOR_NUMBER,
                "PESU_ESTA" => "Activo",
                "PESU_FERE" => $REGISTER_DATE,
                "PESU_FEAR" => DB::raw('CURRENT_TIMESTAMP')
            ]);

            // Verifica que se haya insertado correctamente al subcontratista
            if ($insert_subcontratist) {
                $response = $this->response_controller->makeResponse(FALSE, 'Guardado exitoso');
            } else {
                $response = $this->response_controller->makeResponse(TRUE, 'ERR_SUBCONTRATISTA_REG004: Algo salió mal, error registrando al subcontratista', [], 500);
            }

            return $response;


        } catch (Throwable $th) {
            return $this->response_controller
                ->makeResponse(TRUE, 'ERR_SUBCONTRATISTA_REG005: Error inesperado', strtoupper($th->getMessage()), 500);
        }

    }

    // Metodo para obtener datos de un subcontratista
    public function getSubcontratistById($id_subcontratist, $line_number)
    {
        try {

            $subcontratists_info = DB::table('S002V01TPESU')
                ->select(
                    'PESU_IDPS as ID_SUBCONTRATIST',
                    'S002V01TPESU.PESU_RASO as SOCIAL_REASON',
                    'S002V01TPESU.PESU_REFI as TAX_REFERENCE',
                    'S002V01TPESU.PESU_XRFC as RFC',
                    'S002V01TPESU.PESU_TIPO as CONTRACT_TYPE',
                    'S002V01TPESU.PESU_CORR as EMAIL',
                    'S002V01TPESU.PESU_EXTR as FOREIGNER',
                    'S002V01TPESU.PESU_TAID as TAX',
                    'S002V01TPESU.PESU_CALL as STREET',
                    'S002V01TPESU.PESU_NOEX as EXTERIOR_NUMBER',
                    'S002V01TPESU.PESU_NOIN as INTERIOR_NUMBER',
                    'S002V01TPESU.PESU_COLO as SUBURB',
                    'S002V01TPESU.PESU_CIUD as CITY',
                    'S002V01TPESU.PESU_COPO as POSTAL_CODE',
                    'S002V01TPESU.PESU_ENFE as FEDERAL_ENTITY',
                    'S002V01TPESU.PESU_IDPA as COUNTRY',
                    'S002V01TPESU.PESU_TEL1 as TELEPHONE1',
                    'S002V01TPESU.PESU_LAT1 as LADA1',
                    'S002V01TPESU.PESU_TEL2 as TELEPHONE2',
                    'S002V01TPESU.PESU_LAT2 as LADA2',
                    'S002V01TPESU.PESU_ESTA as STATUS'
                )
                ->where('S002V01TPESU.PESU_IDPS', '=', $id_subcontratist)
                ->where('S002V01TPESU.PESU_NULI', '=', $line_number)
                ->join('S002V01TPAIS', 'S002V01TPESU.PESU_IDPA', '=', 'S002V01TPAIS.PAIS_IDPA')
                ->first();

            // Verifica si el objeto esta vacio
            if (!isset($subcontratists_info) && empty($subcontratists_info)) {
                return $this->response_controller
                    ->makeResponse(TRUE, 'ERR_SUBCONTRATISTA_REG001: No se encontró al subcontratista', $subcontratists_info, 500);
            }

            $subcontratists_info->RFC != null ? $subcontratists_info->RFC = $this->encrypt_controller->encrypt($subcontratists_info->RFC) : null;
            $subcontratists_info->TAX != null ? $subcontratists_info->TAX = $this->encrypt_controller->encrypt($subcontratists_info->TAX) : null;
            $subcontratists_info->TELEPHONE1 = $this->encrypt_controller->encrypt($subcontratists_info->TELEPHONE1);
            $subcontratists_info->TELEPHONE2 = $this->encrypt_controller->encrypt($subcontratists_info->TELEPHONE2);
            $subcontratists_info->EMAIL = $this->encrypt_controller->encrypt($subcontratists_info->EMAIL);

            return $this->response_controller->makeResponse(FALSE, 'Consulta exitosa', $subcontratists_info);

        } catch (Throwable $e) {
            return $this->response_controller
                ->makeResponse(TRUE, 'ERR_SUBCONTRATISTA_REG002: Error inesperado', strtoupper($e->getMessage()), 500);
        }
    }

    // Metodo para actualizar un subcontratista
    public function updateSubcontratist(Request $request, $id_subcontratist)
    {
        $UPDATE_DATE = Carbon::now()->timezone('America/Mexico_City')->toDateTimeString();
        $request['SAVED_BY_USER'] = $this->encrypt_controller->decrypt($request->SAVED_BY_USER);
        $request['TELEPHONE1'] = $this->encrypt_controller->decrypt($request->TELEPHONE1);
        $request['TELEPHONE2'] = $this->encrypt_controller->decrypt($request->TELEPHONE2);
        $request['EMAIL'] = $this->encrypt_controller->decrypt($request->EMAIL);
        $request['RFC'] != null ? $request['RFC'] = $this->encrypt_controller->decrypt($request->RFC) : null;
        $request['TAX'] != null ? $request['TAX'] = $this->encrypt_controller->decrypt($request->TAX) : null;

        try {
            $validator = Validator::make($request->all(), [
                "RFC" => ['max:13'],
                "SOCIAL_REASON" => ['required', 'max:150'],
                "TAX_REFERENCE" => ['max:15'],
                "CONTRACT_TYPE" => ['required'],
                "EMAIL" => ['required', 'email', 'max:150'],
                "FOREIGNER" => ['required'],
                "TAX" => ['max:13'],
                "STREET" => ['required', 'max:50'],
                "EXTERIOR_NUMBER" => ['required', 'string'],
                "SUBURB" => ['required', 'max:50'],
                "CITY" => ['required', 'max:50'],
                "POSTAL_CODE" => ['required', 'digits:5'],
                "FEDERAL_ENTITY" => ['required', 'max:50'],
                "COUNTRY" => ['required'],
                "TELEPHONE1" => ['required', 'digits_between:7,11'],
                "LADA1" => ['required', 'max:5'],
                "SAVED_BY_USER" => ['required', 'digits:10'],
                "INTERIOR_NUMBER" => ['string'],
                "TELEPHONE2" => ['digits_between:7,11'],
                "LADA2" => ['max:5'],
                "LINE_NUMBER" => ['required', 'digits:1']
            ]);

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

            // Busca si el subcontratista existe
            $search_subcontratist = DB::table("S002V01TPESU")
                ->select("PESU_IDPS")
                ->where("PESU_IDPS", "=", $id_subcontratist)
                ->where('PESU_NULI', '=', $request->LINE_NUMBER)
                ->first();

            // Verifica si el objeto esta vacio
            if (!isset($search_subcontratist) && empty($search_subcontratist)) {
                return $this->response_controller
                    ->makeResponse(TRUE, "ERR_SUBCONTRATISTA_REG002: No se encontró al subcontratista", $search_subcontratist, 500);
            }

            // Busca los subcontratistas que tengan datos iguales a los ingresados
            $subcontratists = DB::table('S002V01TPESU')
                ->select("PESU_RASO as SOCIAL_REASON", "PESU_XRFC as RFC", "PESU_CORR as EMAIL")
                ->where('PESU_IDPS', '<>', $id_subcontratist)
                ->where('PESU_NULI', '=', $request->LINE_NUMBER)
                ->get();

            // Valida que los campos no sean repetidos con los que ya se encuentran en la base de datos
            foreach ($subcontratists as $subcontratist) {
                if (isset($subcontratists[0]) && !empty($subcontratists[0])) {
                    $duplicated_data = [];
                    foreach ($subcontratists as $subcontratist) {
                        if ($subcontratist->SOCIAL_REASON == $request->SOCIAL_REASON) {
                            $duplicated_data[] = "Razon Social: " . $subcontratist->SOCIAL_REASON;
                        }

                        if ($request->RFC != null && $subcontratist->RFC == $request->RFC) {
                            $duplicated_data[] = "RFC: " . $subcontratist->RFC;
                        }

                        if ($subcontratist->EMAIL == $request->EMAIL) {
                            $duplicated_data[] = "Email: " . $subcontratist->EMAIL;
                        }
                    }

                    if (isset($duplicated_data[0]) && !empty($duplicated_data[0])) {
                        return $this->response_controller->makeResponse(TRUE, 'ERR_SUBCONTRATISTA_REG003: Datos duplicados', $duplicated_data, 500);
                    }
                }
            }

            $user_register = DB::table('S002V01TUSUA')
                ->select('USUA_IDUS as ID_USER')
                ->where('USUA_IDUS', '=', $request->SAVED_BY_USER)
                ->where('USUA_NULI', '=', $request->LINE_NUMBER)
                ->first();

            // Verifica si el objeto esta vacio
            if (!isset($user_register) && empty($user_register)) {
                return $this->response_controller
                    ->makeResponse(TRUE, "ERR_SUBCONTRATISTA_REG004: Tu usuario no es válido para actualizar subcontratistas", [], 500);
            }

            $update_sql = DB::table('S002V01TPESU')
                ->where('PESU_IDPS', $id_subcontratist)
                ->where('PESU_NULI', '=', $request->LINE_NUMBER)
                ->update([
                    "PESU_NULI" => $request->LINE_NUMBER,
                    "PESU_RASO" => $request->SOCIAL_REASON != null ? trim($request->SOCIAL_REASON) : null,
                    "PESU_REFI" => $request->TAX_REFERENCE != null ? trim($request->TAX_REFERENCE) : null,
                    "PESU_XRFC" => $request->RFC != null ? trim($request->RFC) : null,
                    "PESU_TIPO" => $request->CONTRACT_TYPE != null ? trim($request->CONTRACT_TYPE) : null,
                    "PESU_CORR" => $request->EMAIL != null ? trim($request->EMAIL) : null,
                    "PESU_EXTR" => $request->FOREIGNER != null ? trim($request->FOREIGNER) : null,
                    "PESU_TAID" => $request->TAX != null ? trim($request->TAX) : null,
                    "PESU_CALL" => $request->STREET != null ? trim($request->STREET) : null,
                    "PESU_NOEX" => $request->EXTERIOR_NUMBER != null ? trim($request->EXTERIOR_NUMBER) : null,
                    "PESU_COLO" => $request->SUBURB != null ? trim($request->SUBURB) : null,
                    "PESU_CIUD" => $request->CITY != null ? trim($request->CITY) : null,
                    "PESU_COPO" => $request->POSTAL_CODE != null ? trim($request->POSTAL_CODE) : null,
                    "PESU_ENFE" => $request->FEDERAL_ENTITY != null ? trim($request->FEDERAL_ENTITY) : null,
                    "PESU_IDPA" => $request->COUNTRY != null ? trim($request->COUNTRY) : null,
                    "PESU_TEL1" => $request->TELEPHONE1 != null ? trim($request->TELEPHONE1) : null,
                    "PESU_LAT1" => $request->LADA1 != null ? trim($request->LADA1) : null,
                    "PESU_TEL2" => $request->TELEPHONE2 != null ? trim($request->TELEPHONE2) : null,
                    "PESU_LAT2" => $request->LADA2 != null ? trim($request->LADA2) : null,
                    "PESU_USMO" => $request->SAVED_BY_USER != null ? trim($request->SAVED_BY_USER) : null,
                    "PESU_NOIN" => $request->INTERIOR_NUMBER,
                    "PESU_ESTA" => "Activo",
                    "PESU_FEMO" => $UPDATE_DATE,
                    "PESU_FEAR" => DB::raw('CURRENT_TIMESTAMP')
                ]);

            // Verifica que la actualización se haya realizado exitosamente
            if ($update_sql > 0) {
                $response = $this->response_controller->makeResponse(FALSE, 'Actualización exitosa');
            } else {
                $response = $this->response_controller->makeResponse(TRUE, 'ERR_SUBCONTRATISTA_REG005: Algo salió mal, error al actualizar al subcontratista', [], 500);
            }

            return $response;


        } catch (Throwable $th) {
            return $this->response_controller
                ->makeResponse(TRUE, 'ERR_SUBCONTRATISTA_REG006: Error inesperado', strtoupper($th->getMessage()), 500);
        }

    }

    // Metodo para obtener datos de los subcontratistas
    public function getAllSubcontratists($line_number)
    {
        try {

            $subcontratists_info = DB::table('S002V01TPESU')
                ->select(
                    'S002V01TPESU.PESU_IDPS as ID_SUBCONTRATIST',
                    'S002V01TPESU.PESU_RASO as SOCIAL_REASON',
                    'S002V01TPESU.PESU_REFI as TAX_REFERENCE',
                    'S002V01TPESU.PESU_XRFC as RFC',
                    'S002V01TPESU.PESU_TIPO as CONTRACT_TYPE',
                    'S002V01TPESU.PESU_CORR as EMAIL',
                    'S002V01TPESU.PESU_EXTR as FOREIGNER',
                    'S002V01TPESU.PESU_TAID as TAX',
                    'S002V01TPESU.PESU_CALL as STREET',
                    'S002V01TPESU.PESU_NOEX as EXTERIOR_NUMBER',
                    'S002V01TPESU.PESU_NOIN as INTERIOR_NUMBER',
                    'S002V01TPESU.PESU_COLO as COLONIA',
                    'S002V01TPESU.PESU_CIUD as CITY',
                    'S002V01TPESU.PESU_COPO as POSTAL_CODE',
                    'S002V01TPESU.PESU_ENFE as FEDERAL_ENTITY',
                    'S002V01TPAIS.PAIS_NOMB as COUNTRY',
                    'S002V01TPESU.PESU_TEL1 as TELEPHONE1',
                    'S002V01TPESU.PESU_LAT1 as LADA1',
                    'S002V01TPESU.PESU_TEL2 as TELEPHONE2',
                    'S002V01TPESU.PESU_LAT2 as LADA2',
                    'S002V01TPESU.PESU_ESTA as STATUS'
                )
                ->where('S002V01TPESU.PESU_NULI', '=', $line_number)
                ->join('S002V01TPAIS', 'S002V01TPESU.PESU_IDPA', '=', 'S002V01TPAIS.PAIS_IDPA')
                ->get();

            // Verifica si el objeto esta vacio
            if (!isset($subcontratists_info[0]) && empty($subcontratists_info[0])) {
                return $this->response_controller
                    ->makeResponse(TRUE, 'ERR_SUBCONTRATISTA_REG001: No se encontraron datos', $subcontratists_info, 500);
            }

            foreach ($subcontratists_info as $subcontratist) {
                $subcontratist->RFC = $this->encrypt_controller->encrypt($subcontratist->RFC);
                $subcontratist->TELEPHONE1 = $this->encrypt_controller->encrypt($subcontratist->TELEPHONE1);
                $subcontratist->TELEPHONE2 = $this->encrypt_controller->encrypt($subcontratist->TELEPHONE2);
                $subcontratist->EMAIL = $this->encrypt_controller->encrypt($subcontratist->EMAIL);
            }

            return $this->response_controller->makeResponse(FALSE, 'Consulta exitosa', $subcontratists_info);

        } catch (Throwable $e) {
            return $this->response_controller
                ->makeResponse(TRUE, 'ERR_SUBCONTRATISTA_REG002: Error inesperado', strtoupper($e->getMessage()), 500);
        }
    }

    // Metodo para obtener datos de los subcontratistas
    public function getActiveSubcontratists($line_number)
    {
        try {

            $subcontratists_info = DB::table('S002V01TPESU')
                ->select(
                    DB::raw('CONCAT(PESU_RASO, " " , COALESCE(PESU_REFI, "")) AS NAME'),
                    'PESU_IDPS as ID_SUBCONTRATIST',
                )
                ->orderBy('NAME', 'asc')
                ->where('PESU_NULI', '=', $line_number)
                ->where('PESU_ESTA', '=', "Activo")
                ->get();

            // Verifica si el objeto esta vacio
            if (!isset($subcontratists_info[0]) && empty($subcontratists_info[0])) {
                return $this->response_controller
                    ->makeResponse(TRUE, 'ERR_SUBCONTRATISTA_REG001: No se encontraron datos', $subcontratists_info, 500);
            }

            foreach ($subcontratists_info as $subcontratist) {
                $subcontratist->NAME = trim($subcontratist->NAME);
            }

            return $this->response_controller->makeResponse(FALSE, 'Consulta exitosa', $subcontratists_info);

        } catch (Throwable $e) {
            return $this->response_controller
                ->makeResponse(TRUE, 'ERR_SUBCONTRATISTA_REG002: Error inesperado', strtoupper($e->getMessage()), 500);
        }
    }

    // Metodo para obtener los subcontratistas y el numero de contratos que tienen
    public function getContractsOfEverySubcontratist($line_number)
    {
        try {
            $subcontratists = DB::table('S002V01TPESU')
                ->select(
                    'S002V01TPESU.PESU_IDPS as ID_SUBCONTRATIST',
                    DB::raw('CONCAT(S002V01TPESU.PESU_RASO, " " , COALESCE(S002V01TPESU.PESU_REFI, "")) AS NAME'),
                    DB::raw('COUNT(S002V01TPECO.PECO_IDPE) AS CONTRACTS_COUNT')
                )
                ->where('S002V01TPESU.PESU_NULI', '=', $line_number)
                ->where('S002V01TPERS.PERS_NULI', '=', $line_number)
                ->where('S002V01TPECO.PECO_NULI', '=', $line_number)
                ->where('S002V01TCONT.CONT_NULI', '=', $line_number)
                ->groupBy('S002V01TPESU.PESU_IDPS', 'S002V01TPESU.PESU_RASO', 'S002V01TPECO.PECO_IDPE')
                ->join('S002V01TPERS', 'S002V01TPESU.PESU_IDPS', '=', 'S002V01TPERS.PERS_IDPS')
                ->join('S002V01TPECO', 'S002V01TPERS.PERS_IDPE', '=', 'S002V01TPECO.PECO_IDPE')
                ->join('S002V01TCONT', 'S002V01TPECO.PECO_IDCO', '=', 'S002V01TCONT.CONT_IDCO')
                ->get();

            // Verifica si el objeto esta vacio
            /* if (!isset($subcontratists[0]) && empty($subcontratists[0])) {
                return $this->response_controller
                    ->makeResponse(TRUE, 'ERR_SUBCONTRATISTA_REG001: No se encontraron datos', $subcontratists, 500);
            } */

            $contracts_by_subcontratist = [];

            // Ciclo para impedir que un subcontratista cuente el contrato de cada empleado, solo contará los contratos unicos
            for ($i = 0; $i < sizeof($subcontratists); $i++) {
                for ($o = $i + 1; $o < sizeof($subcontratists); $o++) {
                    if ($subcontratists[$i]->NAME == $subcontratists[$o]->NAME) {

                        // Verifica que registro tiene el numero mayor, que sera el conteo real de los contratos sin repeticiones
                        // El registro que sea el menor (repetido), cambiara el nombre para indicar que esta repetido
                        if ($subcontratists[$i]->CONTRACTS_COUNT > $subcontratists[$o]->CONTRACTS_COUNT) {
                            $subcontratists[$o]->NAME = "XXXX";
                        } else {
                            $subcontratists[$i]->NAME = "XXXX";
                        }
                    }
                }

                // Si el registro tiene un nombre unico, se coloca en el arreglo a enviar
                if ($subcontratists[$i]->NAME != "XXXX") {
                    $subcontratists[$i]->NAME = trim($subcontratists[$i]->NAME);
                    $contracts_by_subcontratist[] = $subcontratists[$i];
                }
            }

            return $this->response_controller->makeResponse(FALSE, "Consulta exitosa", $contracts_by_subcontratist);

        } catch (Throwable $th) {
            return $this->response_controller
                ->makeResponse(TRUE, 'ERR_SUBCONTRATISTA_REG002: Error inesperado', strtoupper($th->getMessage()), 500);
        }

    }

    // Metodo para obtener el historico de los contratos de un subcontratista (Comprueba si existe el subcontratista)
    public function getContractsBySubcontratist($id_subcontratist, $line_number)
    {

        try {

            // Busca si el subcontratista existe
            $search_subcontratist = DB::table("S002V01TPESU")
                ->select("PESU_NULI as LINE_NUMBER")
                ->where("PESU_IDPS", "=", $id_subcontratist)
                ->where('PESU_NULI', '=', $line_number)
                ->first();

            // Verifica si el objeto esta vacio
            if (!isset($search_subcontratist) && empty($search_subcontratist)) {
                return $this->response_controller
                    ->makeResponse(TRUE, "ERR_SUBCONTRATISTA_REG001: No se encontró al subcontratista", $search_subcontratist, 500);
            }

            // Obtiene los contratos pertenecientes al subcontratista deseado
            $contracts = DB::table('S002V01TPERS')
                ->groupBy('S002V01TPERS.PERS_IDPS', 'S002V01TCONT.CONT_IDCO', 'S002V01TCONT.CONT_FEIN', 'S002V01TCONT.CONT_FEFI', 'S002V01TPERS.PERS_NULI', 'S002V01TCONT.CONT_COST')
                ->orderBy("S002V01TCONT.CONT_FEIN", 'desc')
                ->where('S002V01TPERS.PERS_IDPS', '=', $id_subcontratist)
                ->where('S002V01TPERS.PERS_NULI', '=', $line_number)
                ->where('S002V01TPECO.PECO_NULI', '=', $line_number)
                ->where('S002V01TCONT.CONT_NULI', '=', $line_number)
                ->select(
                    'S002V01TCONT.CONT_FEIN as START_DATE',
                    'S002V01TCONT.CONT_FEFI as END_DATE',
                    'S002V01TCONT.CONT_IDCO as ID_CONTRACT',
                    'S002V01TCONT.CONT_COST as COST',
                    'S002V01TPERS.PERS_IDPS as ID_SUBCONTRATIST',
                )
                ->join('S002V01TPECO', 'S002V01TPERS.PERS_IDPE', '=', 'S002V01TPECO.PECO_IDPE')
                ->join('S002V01TCONT', 'S002V01TPECO.PECO_IDCO', '=', 'S002V01TCONT.CONT_IDCO')
                ->get();

            // Verifica si el objeto esta vacio
            if (!isset($contracts[0]) && empty($contracts[0])) {
                return $this->response_controller
                    ->makeResponse(TRUE, 'ERR_CONTRATO_REG002: El subcontratista no tiene contratos', $contracts, 500);
            }


            // Obtiene las intervenciones con el id del contrato del personal que realizo el trabajo
            $interventions = DB::table('S002V01TCONT')
                ->select(
                    'S002V01TCONT.CONT_IDCO as ID_CONTRACT',
                    DB::raw('CONCAT(S002V01TPESU.PESU_RASO, " " , COALESCE(S002V01TPESU.PESU_REFI, "")) AS SUBCONTRACT_NAME'),
                    DB::raw('TRIM(CONCAT(S002V01TUSUA.USUA_NOMB, " " , S002V01TUSUA.USUA_APPA, " ", COALESCE(S002V01TUSUA.USUA_APMA,""))) AS NAME'),
                    'S002V01TPERS.PERS_ESPE',
                    'S002V01TINTE_P.INTE_IDIN as INTERVENTION_ID',
                    'S002V01TINTE_P.INTE_NOMB as INTERVENTION_NAME',
                    'S002V01TINTE_P.INTE_ESRE as SPECIALTY_REQUIRED',
                    'S002V01TPERS.PERS_IDPE as ID_SUBCONTRACT',
                    'S002V01TINTE_P.INTE_FECS as INTERVENTION_DATE',
                )
                ->where('S002V01TPERS.PERS_IDPS', '=', $id_subcontratist)
                ->where('S002V01TPERS.PERS_NULI', '=', $line_number)
                ->where('S002V01TCONT.CONT_NULI', '=', $line_number)
                ->where('S002V01TPECO.PECO_NULI', '=', $line_number)
                ->where('S002V01TUSUA.USUA_NULI', '=', $line_number)
                ->where('S002V01TPESU.PESU_NULI', '=', $line_number)
                ->where('S002V01TPEEM.PEEM_NULI', '=', $line_number)
                ->where('S002V01TEQMA.EQMA_NULI', '=', $line_number)
                ->where('S002V01TEMIN.EMIN_NULI', '=', $line_number)
                ->where('S002V01TINTE_P.INTE_NULI', '=', $line_number)
                ->join('S002V01TPECO', 'S002V01TCONT.CONT_IDCO', '=', 'S002V01TPECO.PECO_IDCO')
                ->join('S002V01TPERS', 'S002V01TPECO.PECO_IDPE', '=', 'S002V01TPERS.PERS_IDPE')
                ->join('S002V01TUSUA', 'S002V01TPERS.PERS_IDUS', '=', 'S002V01TUSUA.USUA_IDUS')
                ->join('S002V01TPESU', 'S002V01TPERS.PERS_IDPS', '=', 'S002V01TPESU.PESU_IDPS')
                ->join('S002V01TPEEM', 'S002V01TPERS.PERS_IDPE', '=', 'S002V01TPEEM.PEEM_IDPE')
                ->join('S002V01TEQMA', 'S002V01TPEEM.PEEM_IDEM', '=', 'S002V01TEQMA.EQMA_IDEQ')
                ->join('S002V01TEMIN', 'S002V01TEQMA.EQMA_IDEQ', '=', 'S002V01TEMIN.EMIN_IDEM')
                ->join('S002V01TINTE_P', 'S002V01TEMIN.EMIN_IDIN', '=', 'S002V01TINTE_P.INTE_IDIN')
                ->get();

            // Verifica si el objeto esta vacio
            if (!isset($interventions[0]) && empty($interventions[0])) {
                return $this->response_controller
                    ->makeResponse(TRUE, 'ERR_INTERVENCIÓN_REG003: No se encontraron datos', $interventions, 500);
            }

            // Itera los contratos para colocarles las intervenciones y miembros correspondientes
            foreach ($contracts as $contract) {

                // Para indicar la hora en la que fue creado el contrato con el formato deseado
                $contract->START_DATE = Carbon::create($contract->START_DATE)->format("d-m-Y h:i:s A");
                $contract->END_DATE = Carbon::create($contract->END_DATE)->format("d-m-Y h:i:s A");
                $contract->COST = $this->encrypt_controller->encrypt($contract->COST);
                $interventions_counter = 0;
                for ($i = 0; $i < sizeof($interventions); $i++) {
                    $temporal_intervention = new stdClass();
                    $temporal_members = [];
                    for ($o = $i + 1; $o < sizeof($interventions); $o++) {

                        // Verifica que no se repitan las intervenciones ni los nombres de integrantes en la misma intervencion
                        if ($interventions[$i]->INTERVENTION_ID == $interventions[$o]->INTERVENTION_ID && $interventions[$i]->SUBCONTRACT_NAME == $interventions[$o]->SUBCONTRACT_NAME) {
                            if ($interventions[$i]->NAME <> $interventions[$o]->NAME) {
                                $temporal_members[] = $interventions[$o]->NAME;
                            }

                            // Para que no lo tome nuevamente, eliminamos el ID del contrato
                            $interventions[$o]->INTERVENTION_ID = "XXXX";
                        }

                    }

                    // Introduce sus datos de la intervencion correspondiente
                    if ($interventions[$i]->INTERVENTION_ID != "XXXX" && Carbon::create($interventions[$i]->INTERVENTION_DATE)->between($contract->START_DATE, $contract->END_DATE)) {
                        $temporal_members[] = $interventions[$i]->NAME;
                        $temporal_intervention->INTERVENTION_NAME = $interventions[$i]->INTERVENTION_NAME;
                        $temporal_intervention->INTERVENTION_ID = $interventions[$i]->INTERVENTION_ID;
                        $temporal_intervention->SPECIALTY_REQUIRED = $interventions[$i]->SPECIALTY_REQUIRED;
                        $temporal_intervention->SUBCONTRACT_NAME = trim($interventions[$i]->SUBCONTRACT_NAME);
                        $temporal_intervention->MEMBERS = $temporal_members;
                        $contract->INTERVENTIONS[] = $temporal_intervention;
                        $interventions_counter++;
                    }

                }
                $contract->INTERVENTIONS_COUNTER = $interventions_counter;
            }

            // Verifica si el objeto esta vacio
            if (!isset($contracts[0]) && empty($contracts[0])) {
                return $this->response_controller
                    ->makeResponse(TRUE, 'ERR_CONTRATO_REG004: Error al asignar contrato con intervención', $contracts, 500);
            }
            $response = new stdClass();
            $response->LINE_NUMBER = $search_subcontratist->LINE_NUMBER;
            $response->CONTRACTS = $contracts;
            return $this->response_controller->makeResponse(FALSE, 'Consulta exitosa', $response);

        } catch (Throwable $th) {
            return $this->response_controller
                ->makeResponse(TRUE, 'ERR_SUBCONTRATISTA_REG005: Error inesperado', strtoupper($th->getMessage()), 500);
        }
    }

    // Metodo para obtener los paises
    public function getCountries($line_number)
    {
        try {

            // Busca si el subcontratista existe
            $countries = DB::table("S002V01TPAIS")
                ->select(
                    "PAIS_IDPA as COUNTRY_ID",
                    "PAIS_NOMB as NAME",
                    "PAIS_LADA as LADA",
                    "PAIS_NOME AS NOMECLARUTA_ISO2",
                    "PAIS_NOM2 AS NOMECLARUTA_ISO3"
                    )
                ->where('PAIS_NULI', '=', $line_number)
                ->get();

            // Verifica si el objeto esta vacio
            if (!isset($countries[0]) && empty($countries[0])) {
                return $this->response_controller
                    ->makeResponse(TRUE, "ERR_SUBCONTRATISTA_REG001: No se encontró al subcontratista", $countries, 500);
            }

            return $this->response_controller->makeResponse(FALSE, 'Consulta exitosa', $countries);

        } catch (Throwable $th) {
            return $this->response_controller
                ->makeResponse(TRUE, 'ERR_SUBCONTRATISTA_REG005: Error inesperado', strtoupper($th->getMessage()), 500);
        }
    }

    // Metodo para la descarga en archivo excel del historial de contratos por subcontratista
    public function downoloadSubcontractInfoOnExcel(Request $request)
    {

        try {
            $request['SAVED_BY_USER'] = $this->encrypt_controller->decrypt($request->SAVED_BY_USER);
            $id_subcontratist = $request->CONTRACTS[0]['ID_SUBCONTRATIST'];

            $final_part_name_document = "Contracts_History_By_Subcontratist_" . $id_subcontratist . ".xlsx";

            // Crea el documento con los datos del request
            $document = $this->createDocument($request);

            // Busca la ultima versión del documento que se haya insertado
            $old_document_name = $this->documents_controller->getDocumentsWithSameName($final_part_name_document, 'excel');

            if ($old_document_name != null) {

                // Obtiene el nombre del documento que tiene el mismo contenido
                $old_document_name = $this->documents_controller->sameDocumentsExcel($document, $old_document_name);

                // Si no hay ningun cambio en el documento, se descarga la ultima versión
                if ($old_document_name != null) {
                    return $this->response_controller
                        ->makeResponse(False, 'Éxito', Storage::disk('excel')->url($old_document_name), 200);
                }
            }

            $writer = IOFactory::createWriter($document, 'Xlsx');
            $content = tmpfile();
            $writer->save($content);

            // Inserta la nueva versión del documento en la base de datos y luego en el storage
            $name_document = $this->documents_controller->createDocument("GPRS", "IN", $final_part_name_document, null, 'excel', $request->LINE_NUMBER, $request->SAVED_BY_USER);
            Storage::disk('excel')->put(
                $name_document,
                $content
            );
            return $this->response_controller
                ->makeResponse(False, 'Éxito', Storage::disk('excel')->url($name_document), 200);

        } catch (Throwable $th) {
            return $this->response_controller
                ->makeResponse(TRUE, 'ERR_EXCEL_REG001: Error inesperado', strtoupper($th), 500);
        }
    }

    // Metodo para la descarga en archivo pdf del historial de contratos por subcontratista
    public function downoloadSubcontractInfoOnPdf(Request $request)
    {

        try {
            $request['SAVED_BY_USER'] = $this->encrypt_controller->decrypt($request->SAVED_BY_USER);
            $id_subcontratist = $request->CONTRACTS[0]['ID_SUBCONTRATIST'];

            $final_part_name_document = "Contracts_History_By_Subcontratist_" . $id_subcontratist . ".pdf";

            //Crea el documento con los datos del request

            $document = $this->createDocument($request);

            $writer = IOFactory::createWriter($document, 'Mpdf');
            $content = tmpfile();

            // Busca la ultima versión del documento que se haya insertado
            $old_document_name = $this->documents_controller->getDocumentsWithSameName($final_part_name_document, 'pdf');

            $writer->save($content);
            if ($old_document_name != null) {

                // Obtiene el nombre del documento que tiene el mismo contenido
                $old_document_name = $this->documents_controller->sameDocumentsPdf($content, $old_document_name);

                // Si no hay ningun cambio en el documento, se descarga la ultima versión
                if ($old_document_name != null) {
                    return $this->response_controller
                        ->makeResponse(False, 'Éxito', Storage::disk('pdf')->url($old_document_name), 200);
                }
            }

            // Inserta la nueva version del documento en la base de datos y luego en el storage
            $name_document = $this->documents_controller->createDocument("GPRS", "IN", $final_part_name_document, null, 'pdf', $request->LINE_NUMBER, $request->SAVED_BY_USER);

            Storage::disk('pdf')->put(
                $name_document,
                $content
            );

            return $this->response_controller
                ->makeResponse(False, 'Éxito', Storage::disk('pdf')->url($name_document), 200);

        } catch (Throwable $th) {
            return $this->response_controller
                ->makeResponse(TRUE, 'ERR_PDF_REG001: Error inesperado', strtoupper($th), 500);
        }
    }

    // Metodo para la creación de documento
    public function createDocument(Request $request)
    {
        try {

            $contracts = $request->CONTRACTS;
            $document = new Spreadsheet();

            // Propiedades del documento
            $document->getProperties()
                ->setCreator("ITTEC")
                ->setTitle("Historial de Contratos por Subcontratista")
                ->setSubject("Historial Documento")
                ->setKeywords("Subcontratista Contratos Historial")
                ->setCategory("Historial archivo");

            // Hace la escritura dentro del archivo
            $start_row = 2; # Indica desde que fila inicia la tabla
            $start_col = "B"; # Indica desde que columna inicia la tabla

            $row = $start_row;
            foreach ($contracts as $contract) {
                // Lleva el control de las celdas de forma automatica
                $first_row = $row;
                $col = $start_col;
                $col++;
                $second_col = $col;
                $col++;
                $third_col = $col;
                $col++;
                $semi_final_col = $col;
                $col++;
                $final_col = $col;
                $col = $start_col;

                // Titulo del documento
                if ($first_row == $start_row) {
                    $document->getActiveSheet()->mergeCells($start_col . $row . ":" . $final_col . $row);
                    $document->getActiveSheet()->setCellValue($start_col . $row, 'FICHA DE SUBCONTRATACIÓN')->getStyle($start_col . $row)->getFill()
                        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                        ->getStartColor()->setRGB('FFCC88');
                    $document->getActiveSheet()->getStyle($start_col . $row)->getFont()->setBold(true);
                    $row++;
                }

                // Cuerpo del documento
                // Si el contrato tiene intervenciones...
                if ($contract['INTERVENTIONS_COUNTER'] > 0) {

                    // Une las 4 primeras celdas para el titulo y datos de las fechas
                    $document->getActiveSheet()->mergeCells($col . $row . ":" . $semi_final_col . $row);
                    $document->getActiveSheet()->mergeCells($col . $row + 1 . ":" . $semi_final_col . $row + 1);

                    $document->getActiveSheet()->setCellValue($col . $row, 'FECHA INICIO - FECHA TÉRMINO')->getStyle($col . $row)->getFill()
                        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                        ->getStartColor()->setRGB('FFCC88');
                    $col++;
                    $col++;
                    $col++;
                    $col++;

                    $document->getActiveSheet()->setCellValue($col . $row, 'ID CONTRATO')->getStyle($col . $row)->getFill()
                        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                        ->getStartColor()->setRGB('FFCC88');
                    $row++;

                    $col = $start_col;
                    $document->getActiveSheet()->setCellValue($col . $row, $contract['START_DATE'] . " - " . $contract['END_DATE']);

                    $col++;
                    $col++;
                    $col++;
                    $col++;
                    $document->getActiveSheet()->setCellValue($col . $row, "#" . $contract['ID_CONTRACT']);

                    $row++;
                    foreach ($contract['INTERVENTIONS'] as $intervention) {
                        $col = $start_col;
                        $document->getActiveSheet()->setCellValue($col . $row, 'ID INTERVENCIÓN')->getStyle($col . $row)->getFill()
                            ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                            ->getStartColor()->setRGB('38D9CE');
                        $col++;
                        $document->getActiveSheet()->setCellValue($col . $row, 'NOMBRE INTERVENCIÓN')->getStyle($col . $row)->getFill()
                            ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                            ->getStartColor()->setRGB('38D9CE');

                        $col++;
                        $document->getActiveSheet()->setCellValue($col . $row, 'ESPECIALIDAD REQUERIDA')->getStyle($col . $row)->getFill()
                            ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                            ->getStartColor()->setRGB('38D9CE');

                        $col++;
                        $document->getActiveSheet()->setCellValue($col . $row, 'NOMBRE SUBCONTRATISTA')->getStyle($col . $row)->getFill()
                            ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                            ->getStartColor()->setRGB('38D9CE');

                        $col++;
                        $document->getActiveSheet()->setCellValue($col . $row, 'MIEMBROS DE INTERVENCIÓN')->getStyle($col . $row)->getFill()
                            ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                            ->getStartColor()->setRGB('38D9CE');

                        $col = $start_col;
                        $row++;
                        $document->getActiveSheet()->setCellValue($col . $row, "#" . $intervention['INTERVENTION_ID']);
                        $col++;
                        $document->getActiveSheet()->setCellValue($col . $row, $intervention['INTERVENTION_NAME']);
                        $col++;
                        $document->getActiveSheet()->setCellValue($col . $row, $intervention['SPECIALTY_REQUIRED']);
                        $col++;
                        $document->getActiveSheet()->setCellValue($col . $row, $intervention['SUBCONTRACT_NAME']);
                        $col++;

                        $temporal_pointer_row = $row; # Indica la primera fila donde se introducieron miembros de la intervencion

                        // Ingresa fila por fila a los miembros de una intervencion
                        foreach ($intervention['MEMBERS'] as $member) {
                            $document->getActiveSheet()->setCellValue($col . $row, $member);
                            $row++;
                        }

                        // Une las celdas sobrantes de los campos de intervencion
                        $document->getActiveSheet()->mergeCells($start_col . $temporal_pointer_row . ":" . $start_col . $row - 1);
                        $document->getActiveSheet()->mergeCells($second_col . $temporal_pointer_row . ":" . $second_col . $row - 1);
                        $document->getActiveSheet()->mergeCells($third_col . $temporal_pointer_row . ":" . $third_col . $row - 1);
                        $document->getActiveSheet()->mergeCells($semi_final_col . $temporal_pointer_row . ":" . $semi_final_col . $row - 1);
                        $document->getActiveSheet()->getStyle($start_col . $first_row . ':' . $col . $row - 1)->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM);
                    }

                    // Si el contrato no tiene intervenciones...
                } else {
                    $document->getActiveSheet()->mergeCells($col . $row . ":" . $semi_final_col . $row);
                    $document->getActiveSheet()->mergeCells($col . $row + 1 . ":" . $semi_final_col . $row + 1);

                    $document->getActiveSheet()->setCellValue($col . $row, 'FECHA INICIO - FECHA FIN')->getStyle($col . $row)->getFill()
                        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                        ->getStartColor()->setRGB('FFCC88');
                    $col++;
                    $col++;
                    $col++;
                    $col++;

                    $document->getActiveSheet()->setCellValue($col . $row, 'ID CONTRATO')->getStyle($col . $row)->getFill()
                        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                        ->getStartColor()->setRGB('FFCC88');
                    $row++;

                    $col = $start_col;
                    $document->getActiveSheet()->setCellValue($col . $row, $contract['START_DATE'] . " - " . $contract['END_DATE']);

                    $col++;
                    $col++;
                    $col++;
                    $col++;
                    $document->getActiveSheet()->setCellValue($col . $row, "#" . $contract['ID_CONTRACT']);

                    $row++;

                    $document->getActiveSheet()->mergeCells($start_col . $row . ":" . $final_col . $row);
                    $document->getActiveSheet()->setCellValue($start_col . $row, 'CONTRATO SIN INTERVENCIONES...')->getStyle($start_col . $row)->getFill()
                        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                        ->getStartColor()->setRGB('38D9CE');
                    $document->getActiveSheet()->getStyle($start_col . $row)->getFont()->setBold(true);

                    $row++;
                    $document->getActiveSheet()->getStyle($start_col . $first_row . ':' . $col . $row - 1)->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM);
                }

                $row++;
                $document->getActiveSheet()->getStyle($start_col . $row - 1 . ':' . $final_col . $row - 1)->getFill()
                    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                    ->getStartColor()->setRGB('D9386C');
                $document->getActiveSheet()->getRowDimension($row - 1)->setRowHeight(25);
                $document->getActiveSheet()->getStyle($start_col . $first_row . ':' . $final_col . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
                $document->getActiveSheet()->getStyle($start_col . $first_row . ':' . $final_col . $row)->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
            }
            $document->getActiveSheet()->getColumnDimension($start_col)->setAutoSize(true);
            $start_col++;
            $document->getActiveSheet()->getColumnDimension($start_col)->setAutoSize(true);
            $start_col++;
            $document->getActiveSheet()->getColumnDimension($start_col)->setAutoSize(true);
            $start_col++;
            $document->getActiveSheet()->getColumnDimension($start_col)->setAutoSize(true);
            $start_col++;
            $document->getActiveSheet()->getColumnDimension($start_col)->setAutoSize(true);

            return $document;

        } catch (Throwable $th) {
            return $this->response_controller
                ->makeResponse(TRUE, 'ERR_CREACIÓN_DOCUMENTO_REG001: Error inesperado', strtoupper($th->getMessage()), 500);
        }
    }

}