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

<?php
/*
    Desarrollador: Ing. Jean Jairo Benitez Meza
    Ultima Modificación: 05/09/2023
    Módulo: Gestión de Adquisiciones
*/
namespace App\Http\Controllers;

use App\Http\Controllers\Controller;
use App\Http\Controllers\ResponseController;
use App\Http\Controllers\EncryptionController;
use App\Http\Controllers\FunctionsController;
use App\Http\Controllers\DocumentManagementController;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Validator;
use Illuminate\Support\Facades\Storage;
use Illuminate\Http\File;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Dompdf\Dompdf;


class ProviderController extends Controller{

    private $responseController;
    private $encController;
    private $resourcesController;
    private $functionsController;
    private $documentManagementController;

    

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

    public function getAllProvider($user, $line){
        try {
            $arrProvider = DB::table('S002V01TPROV')
                ->where('PROV_NULI', '=', $line)
                ->join('S002V01TREFI', 'REFI_CRFI', '=', 'PROV_CRFI')
                ->get([
                    'PROV_NUPR AS NUMERO_PROVEEDOR',
                    'PROV_NOCO AS RAZON_SOCIAL',
                    'PROV_NOMB AS NOMBRE',
                    'PROV_APPA AS PRIMER_APELLIDO',
                    'PROV_APMA AS SEGUNDO_APELLIDO',
                    'PROV_CORR AS CORREO',
                    'PROV_LAD1 AS LADA1',
                    'PROV_TEL1 AS TELEFONO1',
                    'PROV_LAD2 AS LADA2',
                    'PROV_TEL2 AS TELEFONO2',
                    'PROV_XRFC AS RFC',
                    'PROV_XTAX AS TAXID',
                    'PROV_CRFI AS REGIMEN_FISCAL',
                    DB::raw('CONCAT(REFI_DRFI, " (", REFI_CRFI, ")") AS REGIMEN_FISCAL'),
                    'PROV_TIPO AS TIPO',
                    'PROV_TICO AS TIPO_CONTRIBUYENTE',
                    'PROV_SIWE AS SITIOWEB',
                    'PROV_MEPA AS METODO_PAGO',
                    'PROV_ESTA AS ESTADO',
                    'PROV_USRE AS USUARIO_REGISTRA',
                    'PROV_FERE AS FECHA_REGISTRA',
                    'PROV_USMO AS USUARIO_MODIFICA',
                    'PROV_FEMO AS FECHA_MODIFICA',
                    'PROV_CALL AS CALLE',
                    'PROV_NUEX AS EXTERIOR',
                    'PROV_NUIN AS INTERIOR',
                    'PROV_COPO AS CODIGO_POSTAL',
                    'PROV_COLO AS COLONIA',
                    'PROV_LOCA AS LOCALIDAD',
                    'PROV_MUNI AS MUNICIPIO',
                    'PROV_ENTI AS ENTIDAD',
                    'PROV_PAIS AS PAIS',
                ]);


            $arrResponse = array();
            foreach ($arrProvider as $provider) {
                $provider = (array) $provider;
                $provider['SEGUNDO_APELLIDO'] = $provider['SEGUNDO_APELLIDO'] == null ? '' : $provider['SEGUNDO_APELLIDO'];

                $arrReferences = DB::table('S002V01TPRRE')
                    ->where('PRRE_NULI', '=', $line)
                    ->where('PRRE_ESTA', '=', 'Activo')
                    ->where('PRRE_NUPR', '=', $provider['NUMERO_PROVEEDOR'])
                    ->get([
                        'PRRE_IDRE AS NUMERO_REFERENCIA',
                        'PRRE_EMPR AS RAZON_SOCIAL',
                        'PRRE_CORR AS CORREO',
                        'PRRE_LADA AS LADA',
                        'PRRE_TELE AS TELEFONO',
                        'PRRE_XRFC AS RFC',
                        'PRRE_DIRE AS DIRECCION',
                        'PRRE_ESTA AS ESTADO',
                        'PRRE_USRE AS USUARIO_REGISTRA',
                        'PRRE_FERE AS FECHA_REGISTRA',
                        'PRRE_USMO AS USUARIO_MODIFICA',
                        'PRRE_FEMO AS FECHA_MODIFICA',
                    ]);

                $arrContacts = DB::table('S002V01TPRCO')
                    ->where('PRCO_NULI', '=', $line)
                    ->where('PRCO_ESTA', '=', 'Activo')
                    ->where('PRCO_NUPR', '=', $provider['NUMERO_PROVEEDOR'])
                    ->get([
                        'PRCO_IDCP AS NUMERO_CONTACTO',
                        'PRCO_NOMB AS NOMBRE_CONTACTO',
                        'PRCO_TIPO AS TIPO_CONTACTO',
                        'PRCO_CALL AS CALLE',
                        'PRCO_NUEX AS NUMERO_EXTERIOR',
                        'PRCO_NUIN AS NUMERO_INTERIOR',
                        'PRCO_COPO AS CODIGO_POSTAL',
                        'PRCO_COLO AS COLONIA',
                        'PRCO_LOCA AS LOCALIDAD',
                        'PRCO_MUNI AS MUNICIPIO',
                        'PRCO_ENTI AS ENTIDAD',
                        'PRCO_PAIS AS PAIS',
                        'PRCO_LAD1 AS LADA1',
                        'PRCO_TEL1 AS TELEFONO1',
                        'PRCO_LAD2 AS LADA2',
                        'PRCO_TEL2 AS TELEFONO2',
                        'PRCO_COR1 AS CORREO1',
                        'PRCO_COR2 AS CORREO2',
                        'PRCO_OBSE AS OBSERVACIONES',
                        'PRCO_ESTA AS ESTADO',
                        'PRCO_USRE AS USUARIO_REGISTRA',
                        'PRCO_FERE AS FECHA_REGISTRA',
                        'PRCO_USMO AS USUARIO_MODIFICA',
                        'PRCO_FEMO AS FECHA_MODIFICA',
                    ]);

                $arrConditions = DB::table('S002V01TPRCN')
                    ->where('PRCN_NULI', '=', $line)
                    ->where('PRCN_ESTA', '=', 'Activo')
                    ->where('PRCN_NUPR', '=', $provider['NUMERO_PROVEEDOR'])
                    ->get([
                        'PRCN_IDCM AS NUMERO_CONDICION',
                        'PRCN_TIPO AS TIPO_CONDICION',
                        'PRCN_INFO AS INFORMACION',
                        'PRCN_ESTA AS ESTADO',
                        'PRCN_USRE AS USUARIO_REGISTRA',
                        'PRCN_FERE AS FECHA_REGISTRA',
                        'PRCN_USMO AS USUARIO_MODIFICA',
                        'PRCN_FEMO AS FECHA_MODIFICA',
                    ]);

                $arrCatalogs = DB::table('S002V01TPRCE')
                    ->where('PRCE_NULI', '=', $line)
                    ->where('PRCE_ESTA', '=', 'Activo')
                    ->where('PRCE_NUPR', '=', $provider['NUMERO_PROVEEDOR'])
                    ->get([
                        'PRCE_IDCE AS NUMERO_CATALAGO',
                        'PRCE_CATA AS CATALAGO',
                        'PRCE_DESC AS DESCRIPCION',
                        'PRCE_XURL AS URL_CATALAGO',
                        'PRCE_ESTA AS ESTADO',
                        'PRCE_USRE AS USUARIO_REGISTRA',
                        'PRCE_FERE AS FECHA_REGISTRA',
                        'PRCE_USMO AS USUARIO_MODIFICA',
                        'PRCE_FEMO AS FECHA_MODIFICA',
                    ]);


                $arrPayment = json_decode($provider['METODO_PAGO']);

                $arrPaymentFormat = array();
                foreach ($arrPayment as $key => $payment) {
                    $arrPaymentFormat[] = [
                        'C_FORMAPAGO' => $key,
                        'DESCRIPCION' => $payment,
                    ];
                }

                $provider['REFERENCIAS'] = $arrReferences;
                $provider['CONTACTOS'] = $arrContacts;
                $provider['CONDICIONES'] = $arrConditions;
                $provider['CATALAGOS'] = $arrCatalogs;
                $provider['METODOS_PAGO'] = $arrPaymentFormat;

                $arrResponse[] = $provider;
            }


        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(true, "ERR_PROVIDER_GET000: No se pudo realizar la consulta a la base.", $th->getMessage(), 500);
        }

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

    public function getProvidersActives($user, $line) {
        try {
            $arrProvider = DB::table('S002V01TPROV')
                ->where('PROV_ESTA', '=', 'Activo')
                ->where('PROV_NULI', '=', $line)
                ->join('S002V01TREFI', 'REFI_CRFI', '=', 'PROV_CRFI')
                ->get([
                    'PROV_NUPR',
                    'PROV_NOCO',
                    'PROV_NOMB',
                    'PROV_APPA',
                    'PROV_APMA',
                    'PROV_CORR',
                    'PROV_LAD1',
                    'PROV_TEL1',
                    'PROV_LAD2',
                    'PROV_TEL2',
                    'PROV_XRFC',
                    'PROV_XTAX',
                    'REFI_CRFI',
                    'REFI_DRFI',
                    'PROV_TIPO',
                    'PROV_TICO',
                    'PROV_SIWE',
                    'PROV_MEPA',
                    'PROV_CALL',
                    'PROV_NUEX',
                    'PROV_NUIN',
                    'PROV_COPO',
                    'PROV_COLO',
                    'PROV_MUNI',
                    'PROV_LOCA',
                    'PROV_ENTI',
                    'PROV_PAIS',
                    'PROV_INEX',
                    'PROV_USRE',
                    'PROV_FERE',
                    'PROV_USMO',
                    'PROV_FEMO',
                    'PROV_FEAR',
                ]);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(true, "ERR_PROVIDER_GETACTIVE000: No se pudo realizar la consulta a la base.", $th->getMessage(), 500);
        }
        return $this->responseController->makeResponse(false, "ÉXITO: Consulta Exitosa", $arrProvider);
    }

    public function getProvidersByNumber($provider, $user, $line) {
        try {
            $arrProvider = DB::table('S002V01TPROV')
                ->where('PROV_NUPR', '=', $provider)
                ->where('PROV_NULI', '=', $line)
                ->join('S002V01TREFI', 'REFI_CRFI', '=', 'PROV_CRFI')
                ->first([
                    'PROV_NUPR',
                    'PROV_NOCO',
                    'PROV_NOMB',
                    'PROV_APPA',
                    'PROV_APMA',
                    'PROV_CORR',
                    'PROV_LAD1',
                    'PROV_TEL1',
                    'PROV_LAD2',
                    'PROV_TEL2',
                    'PROV_XRFC',
                    'PROV_XTAX',
                    'REFI_CRFI',
                    'REFI_DRFI',
                    'PROV_TIPO',
                    'PROV_TICO',
                    'PROV_SIWE',
                    'PROV_MEPA',
                    'PROV_CALL',
                    'PROV_NUEX',
                    'PROV_NUIN',
                    'PROV_COPO',
                    'PROV_COLO',
                    'PROV_MUNI',
                    'PROV_LOCA',
                    'PROV_ENTI',
                    'PROV_PAIS',
                    'PROV_INEX',
                    'PROV_USRE',
                    'PROV_FERE',
                    'PROV_USMO',
                    'PROV_FEMO',
                    'PROV_FEAR',
                ]);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(true, "ERR_PROVIDER_GETBYNUMBER000: No se pudo realizar la consulta a la base.", $th->getMessage(), 500);
        }
        return $this->responseController->makeResponse(false, "ÉXITO: Consulta Exitosa", $arrProvider);

    }

    public function updateProvider(Request $request) {
        $validator = Validator::make($request->all(), [
            'line' => 'required|string',
            'user' => 'required|string',
            'obj_response' => 'required',
        ]);

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

        DB::beginTransaction();
        $requestData = $request->all();
        $line = $requestData['line'];

        try {
            $user = $this->encController->decrypt($request['user']);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD001: No se pudo obtener el usuario.", $th->getMessage(), 500);
        }

        $arrInformationPersonal = $requestData['obj_response']['INFORMACION'];
        $arrReferences = $requestData['obj_response']['REFERENCIAS'];
        $arrContacts = $requestData['obj_response']['CONTACTOS'];
        $arrConditions = $requestData['obj_response']['CONDICIONES'];
        $arrPaymentMedia = $requestData['obj_response']['METODOS_PAGO'];
        $arrCatalogs = $requestData['obj_response']['CATALAGOS'];

        $numeroProveedor = $arrInformationPersonal['NUMERO_PROVEEDOR'];

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

        $arrPaymentTemp = array();
        foreach ($arrPaymentMedia as $payment) {
            $arrPaymentTemp[$payment['C_FORMAPAGO']] = $payment['DESCRIPCION'];
        }
        try {
            $strPayment = json_encode($arrPaymentTemp);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD002: Ocurrió un error al obtener los datos del método de pago.", $th->getMessage(), 500);
        }
        
        try {
            $validateUpdateInformation = DB::table('S002V01TPROV')
                ->where('PROV_NUPR', '=', $numeroProveedor)
                ->where('PROV_NULI', '=', $line)
                ->update([
                    'PROV_NOCO' => $arrInformationPersonal['RAZON_SOCIAL'],
                    'PROV_NOMB' => $arrInformationPersonal['NOMBRE'],
                    'PROV_APPA' => $arrInformationPersonal['PRIMER_APELLIDO'],
                    'PROV_APMA' => $arrInformationPersonal['SEGUNDO_APELLIDO'] != '' ? $arrInformationPersonal['SEGUNDO_APELLIDO'] : null,
                    'PROV_CORR' => $arrInformationPersonal['CORREO'],
                    'PROV_LAD1' => $arrInformationPersonal['LADA1'],
                    'PROV_TEL1' => $arrInformationPersonal['TELEFONO1'],
                    'PROV_LAD2' => $arrInformationPersonal['LADA2'] != '' ? $arrInformationPersonal['LADA2'] : null,
                    'PROV_TEL2' => $arrInformationPersonal['TELEFONO2'] != '' ? $arrInformationPersonal['TELEFONO2'] : null,
                    'PROV_TICO' => $arrInformationPersonal['TIPO_CONTRIBUYENTE'],
                    'PROV_XRFC' => $arrInformationPersonal['RFC'],
                    'PROV_XTAX' => $arrInformationPersonal['TAXID'] != '' ? $arrInformationPersonal['TAXID'] : null,
                    'PROV_CRFI' => $arrInformationPersonal['REGIMEN_FISCAL'],
                    'PROV_TIPO' => $arrInformationPersonal['TIPO'],
                    'PROV_SIWE' => $arrInformationPersonal['SITIOWEB'],
                    'PROV_MEPA' => $strPayment,
                    'PROV_CALL' => $arrInformationPersonal['CALLE'],
                    'PROV_NUEX' => $arrInformationPersonal['EXTERIOR'],
                    'PROV_NUIN' => $arrInformationPersonal['INTERIOR'],
                    'PROV_COPO' => $arrInformationPersonal['CODIGO_POSTAL'],
                    'PROV_COLO' => $arrInformationPersonal['COLONIA'],
                    'PROV_LOCA' => $arrInformationPersonal['LOCALIDAD'] === 'Sin localidad' ? null : $arrInformationPersonal['LOCALIDAD'],
                    'PROV_MUNI' => $arrInformationPersonal['MUNICIPIO'],
                    'PROV_ENTI' => $arrInformationPersonal['ENTIDAD'],
                    'PROV_PAIS' => $arrInformationPersonal['PAIS'],
                    'PROV_USMO' => $user,
                    'PROV_FEMO' => $currentDate,
                    'PROV_FEAR' => DB::raw('CURRENT_TIMESTAMP')
                ]);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD003: Ocurrió un error al obtener el contenido.", $th->getMessage(), 500);
        }
        if (!$validateUpdateInformation) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD004: Ocurrió un error al obtener el contenido.", [], 500);
        }

        foreach ($arrReferences as $references) {
            if ($references['ESTADO'] == 'Activo' || $references['ESTADO'] == 'Modificado') {
                try {
                    $validateUpdateReference = DB::table('S002V01TPRRE')
                        ->where('PRRE_IDRE', '=', $references['NUMERO_REFERENCIA'])
                        ->where('PRRE_NUPR', '=', $numeroProveedor)
                        ->where('PRRE_NULI', '=', $line)
                        ->update([
                            'PRRE_EMPR' => $references['RAZON_SOCIAL'],
                            'PRRE_CORR' => $references['CORREO'],
                            'PRRE_LADA' => $references['LADA'],
                            'PRRE_TELE' => $references['TELEFONO'],
                            'PRRE_XRFC' => $references['RFC'],
                            'PRRE_DIRE' => $references['DIRECCION'],
                            'PRRE_USMO' => $user,
                            'PRRE_FEMO' => $currentDate,
                            'PRRE_FEAR' => DB::raw('CURRENT_TIMESTAMP')
                        ]);
                } catch (\Throwable $th) {
                    DB::rollBack();
                    return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD005: Ocurrió un error al obtener el contenido.", $th->getMessage(), 500);
                }
                if (!$validateUpdateReference) {
                    DB::rollBack();
                    return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD006: Ocurrió un error al obtener el contenido. ", [], 500);
                }
            } else if ($references['ESTADO'] == 'Nuevo') {
                try {
                    $validateInsertReference = DB::table('S002V01TPRRE')->insert([
                        'PRRE_NUPR' => $numeroProveedor,
                        'PRRE_EMPR' => $references['RAZON_SOCIAL'],
                        'PRRE_CORR' => $references['CORREO'],
                        'PRRE_LADA' => $references['LADA'],
                        'PRRE_TELE' => $references['TELEFONO'],
                        'PRRE_XRFC' => $references['RFC'],
                        'PRRE_DIRE' => $references['DIRECCION'],
                        'PRRE_NULI' => $line,
                        'PRRE_USRE' => $user,
                        'PRRE_FERE' => $currentDate,
                        'PRRE_FEAR' => DB::raw('CURRENT_TIMESTAMP')
                    ]); 
                } catch (\Throwable $th) {
                    DB::rollBack();
                    return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD007: Ocurrió un error al obtener el contenido.", $th->getMessage(), 500);
                }
                if (!$validateInsertReference) {
                    DB::rollBack();
                    return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD008: Ocurrió un error al obtener el contenido.", [], 500);
                }
            } else if ($references['ESTADO'] == 'Eliminado'){
                try {
                    $validateUpdateReference = DB::table('S002V01TPRRE')
                        ->where('PRRE_IDRE', '=', $references['NUMERO_REFERENCIA'])
                        ->where('PRRE_NUPR', '=', $numeroProveedor)
                        ->where('PRRE_NULI', '=', $line)
                        ->update([
                            'PRRE_ESTA' => 'Eliminado',
                            'PRRE_USMO' => $user,
                            'PRRE_FEMO' => $currentDate,
                            'PRRE_FEAR' => DB::raw('CURRENT_TIMESTAMP')
                        ]);
                } catch (\Throwable $th) {
                    DB::rollBack();
                    return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD009: Ocurrió un error al obtener el contenido.", $th->getMessage(), 500);
                }
                if (!$validateUpdateReference) {
                    DB::rollBack();
                    return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD010: Ocurrió un error al obtener el contenido.", [], 500);
                }
            }
        }

        foreach ($arrContacts as $contacts) {
            if ($contacts['ESTADO'] == 'Activo' || $contacts['ESTADO'] == 'Modificado') {
                try {
                    $validateUpdateContact = DB::table('S002V01TPRCO')
                        ->where('PRCO_IDCP', '=', $contacts['NUMERO_CONTACTO'])
                        ->where('PRCO_NUPR', '=', $numeroProveedor)
                        ->where('PRCO_NULI', '=', $line)
                        ->update([
                            'PRCO_NOMB' => $contacts['NOMBRE_CONTACTO'],
                            'PRCO_TIPO' => $contacts['TIPO_CONTACTO'],
                            'PRCO_CALL' => $contacts['CALLE'],
                            'PRCO_NUEX' => $contacts['NUMERO_EXTERIOR'],
                            'PRCO_NUIN' => $contacts['NUMERO_INTERIOR'] != '' ? $contacts['NUMERO_INTERIOR'] : null,
                            'PRCO_COPO' => $contacts['CODIGO_POSTAL'],
                            'PRCO_COLO' => $contacts['COLONIA'],
                            'PRCO_LOCA' => $contacts['LOCALIDAD'],
                            'PRCO_MUNI' => $contacts['MUNICIPIO'],
                            'PRCO_ENTI' => $contacts['ENTIDAD'],
                            'PRCO_PAIS' => $contacts['PAIS'],
                            'PRCO_TEL1' => $contacts['TELEFONO1'],
                            'PRCO_TEL2' => $contacts['TELEFONO2'] != '' ? $contacts['TELEFONO2'] : null,
                            'PRCO_COR1' => $contacts['CORREO1'],
                            'PRCO_COR2' => $contacts['CORREO2'] != '' ? $contacts['CORREO2'] : null,
                            'PRCO_OBSE' => $contacts['OBSERVACIONES'],
                            'PRCO_USMO' => $user,
                            'PRCO_FEMO' => $currentDate,
                            'PRCO_FEAR' => DB::raw('CURRENT_TIMESTAMP')
                        ]);
                } catch (\Throwable $th) {
                    DB::rollBack();
                    return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD011: Ocurrió un error al obtener el contenido.", $th->getMessage(), 500);
                }
                if (!$validateUpdateContact) {
                    DB::rollBack();
                    return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD012: Ocurrió un error al obtener el contenido.", [], 500);
                }
            } else if ($contacts['ESTADO'] == 'Nuevo') {
                try {
                    $validateInsertContact = DB::table('S002V01TPRCO')->insert([
                        'PRCO_NUPR' => $numeroProveedor,
                        'PRCO_NOMB' => $contacts['NOMBRE_CONTACTO'],
                        'PRCO_TIPO' => $contacts['TIPO_CONTACTO'],
                        'PRCO_CALL' => $contacts['CALLE'],
                        'PRCO_NUEX' => $contacts['NUMERO_EXTERIOR'],
                        'PRCO_NUIN' => $contacts['NUMERO_INTERIOR'] != '' ? $contacts['NUMERO_INTERIOR'] : null,
                        'PRCO_COPO' => $contacts['CODIGO_POSTAL'],
                        'PRCO_COLO' => $contacts['COLONIA'],
                        'PRCO_LOCA' => $contacts['LOCALIDAD'],
                        'PRCO_MUNI' => $contacts['MUNICIPIO'],
                        'PRCO_ENTI' => $contacts['ENTIDAD'],
                        'PRCO_PAIS' => $contacts['PAIS'],
                        'PRCO_LAD1' => $contacts['LADA1'],
                        'PRCO_TEL1' => $contacts['TELEFONO1'],
                        'PRCO_LAD2' => $contacts['LADA2'] != '' ? $contacts['LADA2'] : null,
                        'PRCO_TEL2' => $contacts['TELEFONO2'] != '' ? $contacts['TELEFONO2'] : null,
                        'PRCO_COR1' => $contacts['CORREO1'],
                        'PRCO_COR2' => $contacts['CORREO2'] != '' ? $contacts['CORREO2'] : null,
                        'PRCO_OBSE' => $contacts['OBSERVACIONES'],
                        'PRCO_NULI' => $line,
                        'PRCO_USRE' => $user,
                        'PRCO_FERE' => $currentDate,
                        'PRCO_FEAR' => DB::raw('CURRENT_TIMESTAMP')
                    ]);
                } catch (\Throwable $th) {
                    DB::rollBack();
                    return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD013: Ocurrió un error al obtener el contenido.", $th->getMessage(), 500);
                }
                if (!$validateInsertContact) {
                    DB::rollBack();
                    return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD014: Ocurrió un error al obtener el contenido.", [], 500);
                }
            } else if ($contacts['ESTADO'] == 'Eliminado'){
                try {
                    $validateUpdateContact = DB::table('S002V01TPRCO')
                        ->where('PRCO_IDCP', '=', $contacts['NUMERO_CONTACTO'])
                        ->where('PRCO_NUPR', '=', $numeroProveedor)
                        ->where('PRCO_NULI', '=', $line)
                        ->update([
                            'PRCO_ESTA' => 'Eliminado',
                            'PRCO_USMO' => $user,
                            'PRCO_FEMO' => $currentDate,
                            'PRCO_FEAR' => DB::raw('CURRENT_TIMESTAMP')
                        ]);
                } catch (\Throwable $th) {
                    DB::rollBack();
                    return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD015: Ocurrió un error al obtener el contenido.", $th->getMessage(), 500);
                }
                if (!$validateUpdateContact) {
                    DB::rollBack();
                    return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD016: Ocurrió un error al obtener el contenido.", [], 500);
                }
            }
        }

        foreach ($arrConditions as $conditions) {
            if ($conditions['ESTADO'] == 'Activo' || $conditions['ESTADO'] == 'Modificado') {
                try {
                    $validateUpdateCondition = DB::table('S002V01TPRCN')
                        ->where('PRCN_IDCM', '=', $conditions['NUMERO_CONDICION'])
                        ->where('PRCN_NUPR', '=', $numeroProveedor)
                        ->where('PRCN_NULI', '=', $line)
                        ->update([
                            'PRCN_TIPO' => $conditions['TIPO_CONDICION'],
                            'PRCN_INFO' => $conditions['INFORMACION'],
                            'PRCN_USMO' => $user,
                            'PRCN_FEMO' => $currentDate,
                            'PRCN_FEAR' => DB::raw('CURRENT_TIMESTAMP')
                        ]);
                } catch (\Throwable $th) {
                    DB::rollBack();
                    return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD017: Ocurrió un error al obtener el contenido.", $th->getMessage(), 500);
                }
                if (!$validateUpdateCondition) {
                    DB::rollBack();
                    return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD018: Ocurrió un error al obtener el contenido.", [], 500);
                }
            } else if ($conditions['ESTADO'] == 'Nuevo'){
                try {
                    $validateInsertCondition = DB::table('S002V01TPRCN')->insert([
                        'PRCN_NUPR' => $numeroProveedor,
                        'PRCN_TIPO' => $conditions['TIPO_CONDICION'],
                        'PRCN_INFO' => $conditions['INFORMACION'],
                        'PRCN_NULI' => $line,
                        'PRCN_USRE' => $user,
                        'PRCN_FERE' => $currentDate,
                        'PRCN_FEAR' => DB::raw('CURRENT_TIMESTAMP')
                    ]);
                } catch (\Throwable $th) {
                    DB::rollBack();
                    return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD019: Ocurrió un error al obtener el contenido.", $th->getMessage(), 500);
                }
                if (!$validateInsertCondition) {
                    DB::rollBack();
                    return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD020: Ocurrió un error al obtener el contenido.", [], 500);
                }
            } else if ($conditions['ESTADO'] == 'Eliminado'){
                try {
                    $validateRegister = DB::table('S002V01TPRCN')
                        ->where('PRCN_IDCM', '=', $conditions['NUMERO_CONDICION'])
                        ->where('PRCN_NUPR', '=', $numeroProveedor)
                        ->where('PRCN_NULI', '=', $line)
                        ->exists();
                } catch (\Throwable $th) {
                    DB::rollBack();
                    return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD021: Ocurrió un error al validar el registro.", $th->getMessage(), 500);
                }

                if ($validateRegister) {
                    try {
                        $validateUpdateConditions = DB::table('S002V01TPRCN')
                            ->where('PRCN_IDCM', '=', $conditions['NUMERO_CONDICION'])
                            ->where('PRCN_NUPR', '=', $numeroProveedor)
                            ->where('PRCN_NULI', '=', $line)
                            ->update([
                                'PRCN_ESTA' => 'Eliminado',
                                'PRCN_USMO' => $user,
                                'PRCN_FEMO' => $currentDate,
                                'PRCN_FEAR' => DB::raw('CURRENT_TIMESTAMP')
                            ]);
                        
                    } catch (\Throwable $th) {
                        DB::rollBack();
                        return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD022: Ocurrió un error al obtener el contenido.", $th->getMessage(), 500);
                    }
                    if (!$validateUpdateConditions) {
                        DB::rollBack();
                        return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD023: Ocurrió un error al obtener el contenido.", [], 500);
                    }
                }
            }
        }

        foreach ($arrCatalogs as $catalogs) {
            if ($catalogs['ESTADO'] == 'Activo' || $catalogs['ESTADO'] == 'Modificado') {
                try {
                    $validateUpdateCatalog = DB::table('S002V01TPRCE')
                        ->where('PRCE_IDCE', '=', $catalogs['NUMERO_CATALAGO'])
                        ->where('PRCE_NUPR', '=', $numeroProveedor)
                        ->where('PRCE_NULI', '=', $line)
                        ->update([
                            'PRCE_CATA' => $catalogs['CATALAGO'],
                            'PRCE_DESC' => $catalogs['DESCRIPCION'],
                            'PRCE_XURL' => $catalogs['URL_CATALAGO'],
                            'PRCE_USMO' => $user,
                            'PRCE_FEMO' => $currentDate,
                            'PRCE_FEAR' => DB::raw('CURRENT_TIMESTAMP')
                        ]);
                } catch (\Throwable $th) {
                    DB::rollBack();
                    return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD024: Ocurrió un error al obtener el contenido.", $th->getMessage(), 500);
                }
                if (!$validateUpdateCatalog) {
                    DB::rollBack();
                    return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD025: Ocurrió un error al obtener el contenido.", [], 500);
                }
            } else if ($catalogs['ESTADO'] == 'Nuevo'){
                try {
                    $validateInsertCatalog = DB::table('S002V01TPRCE')->insert([
                        'PRCE_NUPR' => $numeroProveedor,
                        'PRCE_CATA' => $catalogs['CATALAGO'],
                        'PRCE_DESC' => $catalogs['DESCRIPCION'],
                        'PRCE_XURL' => $catalogs['URL_CATALAGO'],
                        'PRCE_NULI' => $line,
                        'PRCE_USRE' => $user,
                        'PRCE_FERE' => $currentDate,
                        'PRCE_FEAR' => DB::raw('CURRENT_TIMESTAMP')
                    ]);
                } catch (\Throwable $th) {
                    DB::rollBack();
                    return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD026: Ocurrió un error al obtener el contenido.", $th->getMessage(), 500);
                }
                if (!$validateInsertCatalog) {
                    DB::rollBack();
                    return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD027: Ocurrió un error al obtener el contenido.", [], 500);
                }
            } else if ($catalogs['ESTADO'] == 'Eliminado'){
                try {
                    $validateExists = DB::table('S002V01TPRCE')
                        ->where('PRCE_IDCE', '=', $catalogs['NUMERO_CATALAGO'])
                        ->where('PRCE_NUPR', '=', $numeroProveedor)
                        ->where('PRCE_NULI', '=', $line)
                        ->exists();
                } catch (\Throwable $th) {
                    DB::rollBack();
                    return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD028: Ocurrió un error al obtener el contenido.", $th->getMessage(), 500);
                }

                if ($validateExists) {
                    try {
                        $validateDelete = DB::table('S002V01TPRCE')
                            ->where('PRCE_IDCE', '=', $catalogs['NUMERO_CATALAGO'])
                            ->where('PRCE_NUPR', '=', $numeroProveedor)
                            ->where('PRCE_NULI', '=', $line)
                            ->update([
                                'PRCE_ESTA' => 'Eliminado',
                                'PRCE_USMO' => $user,
                                'PRCE_FEMO' => $currentDate,
                                'PRCE_FEAR' => DB::raw('CURRENT_TIMESTAMP')
                            ]);
                        
                    } catch (\Throwable $th) {
                        DB::rollBack();
                        return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD029: Ocurrió un error al obtener el contenido.", $th->getMessage(), 500);
                    }
                    if (!$validateDelete) {
                        DB::rollBack();
                        return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD030: Ocurrió un error al obtener el contenido.", [], 500);
                    }
                }
            }
        }

        $response = [ 'idProvider' => $numeroProveedor ];

        try {
            $getSubcontract = (array) DB::table('S002V01TPESU')
                ->where('PESU_NUPR', '=', $numeroProveedor)
                ->where('PESU_NULI', '=', $line)
                ->first();
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD031: Ocurrió un error al válidar si existe en subcontratista.", $th->getMessage(), 500);
        }
        
        if ( !empty($getSubcontract)) {

            try {
                $updateSubcontract = DB::table('S002V01TPESU')
                    ->where('PESU_NUPR', '=', $numeroProveedor)
                    ->where('PESU_NULI', '=', $line)
                    ->update([
                        'PESU_RASO' => $arrInformationPersonal['RAZON_SOCIAL'],
                        'PESU_REFI' => $arrInformationPersonal['REGIMEN_FISCAL'],
                        'PESU_XRFC' => $arrInformationPersonal['RFC'] != '' ? $arrInformationPersonal['RFC'] : null,
                        'PESU_TIPO' => $arrInformationPersonal['TIPO_CONTRIBUYENTE'],
                        'PESU_CORR' => $arrInformationPersonal['CORREO'],
                        'PESU_EXTR' => $arrInformationPersonal['TIPO'] === 'Nacional' ? 'No' : 'Si',
                        'PESU_TAID' => $arrInformationPersonal['TAXID'] != '' ? $arrInformationPersonal['TAXID'] : null,
                        'PESU_CALL' => $arrInformationPersonal['CALLE'],
                        'PESU_NUEX' => $arrInformationPersonal['EXTERIOR'],
                        'PESU_NUIN' => $arrInformationPersonal['INTERIOR'],
                        'PESU_COLO' => $arrInformationPersonal['COLONIA'],
                        'PESU_CIUD' => $arrInformationPersonal['MUNICIPIO'],
                        'PESU_LOCA' => $arrInformationPersonal['LOCALIDAD'] === 'Sin localidad' ? null : $arrInformationPersonal['LOCALIDAD'],
                        'PESU_COPO' => $arrInformationPersonal['CODIGO_POSTAL'],
                        'PESU_ENFE' => $arrInformationPersonal['ENTIDAD'],
                        'PESU_IDPA' => $arrInformationPersonal['PAIS'],
                        'PESU_TEL1' => $arrInformationPersonal['TELEFONO1'],
                        'PESU_LAT1' => $arrInformationPersonal['LADA1'],
                        'PESU_TEL2' => $arrInformationPersonal['TELEFONO2'] != '' ? $arrInformationPersonal['TELEFONO2'] : null,
                        'PESU_LAT2' => $arrInformationPersonal['LADA2'] != '' ? $arrInformationPersonal['LADA2'] : null,
                        'PESU_USMO' => $user,
                        'PESU_FEMO' => $currentDate,
                    ]
                );
            } catch (\Throwable $th) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD032: Ocurrió un error al modificar el subcontratista.", $th->getMessage(), 500);
            }
            if ( !$updateSubcontract ) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_PROVIDER_MOD033: No se pudo modificar el subcontratista.", [], 500);
            }

            $response['subcontract'] = $getSubcontract;

        }
        
        
        DB::commit();
        return $this->responseController->makeResponse(false, "EXITO: Modificación Exitosa", $response);
    }

    public function registerProvider(Request $request) {
        $validator = Validator::make($request->all(), [
            'line' => 'required|string',
            'user' => 'required|string',
            'obj_response' => 'required',
        ]);

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

        DB::beginTransaction();
        $requestData = $request->all();
        $line = $requestData['line'];

        try {
            $user = $this->encController->decrypt($requestData['user']);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_PROVIDER_REG001: Ocurrió un error al obtener el usuario.", $th->getMessage(), 500);
        }

        $arrInformationPersonal = (array) $requestData['obj_response']['INFORMACION'];
        $arrReferences = (array) $requestData['obj_response']['REFERENCIAS'];
        $arrContacts = (array) $requestData['obj_response']['CONTACTOS'];
        $arrConditions = (array) $requestData['obj_response']['CONDICIONES'];
        $arrPaymentMedia = (array) $requestData['obj_response']['METODOS_PAGO'];
        $arrCatalogs = (array) $requestData['obj_response']['CATALAGOS'];

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

        $arrPaymentTemp = array();
        foreach ($arrPaymentMedia as $payment) {
            $arrPaymentTemp[$payment['MEPA_IDME']] = $payment['MEPA_NOMB'];
        }

        try {
            $strPayment = json_encode($arrPaymentTemp);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_PROVIDER_REG002: Ocurrió un error al obtener los métodos de pago del proveedor.", $th->getMessage(), 500);
        }

        $idSubcontract = null;
        if (!is_null($arrInformationPersonal['ID_SUBCONTRACT'])) {
            $idSubcontract = $arrInformationPersonal['ID_SUBCONTRACT'];

            try {
                $validateSubcontract = DB::table('S002V01TPESU')
                    ->where('PESU_IDPS', '=', $idSubcontract)
                    ->where('PESU_NULI', '=', $line)
                    ->exists();
            } catch (\Throwable $th) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_PROVIDER_REG003: Ocurrió un error al validar que el subcontratista exista.", $th->getMessage(), 500);
            }

            if (!$validateSubcontract) {
                return $this->responseController->makeResponse(true, "ERR_PROVIDER_REG004: El subcontratista no existe.", [], 500);
            }
        }
        
        try {
            $idProdiver = DB::table('S002V01TPROV')->insertGetId([
                'PROV_NOCO' => $arrInformationPersonal['RAZON_SOCIAL'],
                'PROV_IDPS' => $idSubcontract,
                'PROV_NOMB' => $arrInformationPersonal['NOMBRE'],
                'PROV_APPA' => $arrInformationPersonal['PRIMER_APELLIDO'],
                'PROV_APMA' => $arrInformationPersonal['SEGUNDO_APELLIDO'] != '' ? $arrInformationPersonal['SEGUNDO_APELLIDO'] : null,
                'PROV_CORR' => $arrInformationPersonal['CORREO'],
                'PROV_LAD1' => $arrInformationPersonal['LADA1'],
                'PROV_TEL1' => $arrInformationPersonal['TELEFONO1'],
                'PROV_LAD2' => $arrInformationPersonal['LADA2'],
                'PROV_TEL2' => $arrInformationPersonal['TELEFONO2'] != '' ? $arrInformationPersonal['TELEFONO2'] : null,
                'PROV_TICO' => $arrInformationPersonal['TIPO_CONTRIBUYENTE'],
                'PROV_XRFC' => $arrInformationPersonal['RFC'],
                'PROV_XTAX' => $arrInformationPersonal['TAXID'] != '' ? $arrInformationPersonal['TAXID'] : null,
                'PROV_CRFI' => $arrInformationPersonal['REGIMEN_FISCAL'],
                'PROV_TIPO' => $arrInformationPersonal['TIPO'],
                'PROV_SIWE' => $arrInformationPersonal['SITIOWEB'],
                'PROV_CALL' => $arrInformationPersonal['CALLE'],
                'PROV_NUEX' => $arrInformationPersonal['EXTERIOR'],
                'PROV_NUIN' => $arrInformationPersonal['INTERIOR'] != '' ? $arrInformationPersonal['INTERIOR'] : null,
                'PROV_COPO' => $arrInformationPersonal['CODIGO_POSTAL'],
                'PROV_COLO' => $arrInformationPersonal['COLONIA'],
                'PROV_LOCA' => $arrInformationPersonal['LOCALIDAD'] === 'Sin localidad' ? null : $arrInformationPersonal['LOCALIDAD'],
                'PROV_MUNI' => $arrInformationPersonal['MUNICIPIO'],
                'PROV_ENTI' => $arrInformationPersonal['ENTIDAD'],
                'PROV_PAIS' => $arrInformationPersonal['PAIS'],
                'PROV_MEPA' => $strPayment,
                'PROV_NULI' => $line,
                'PROV_USRE' => $user,
                'PROV_FERE' => $currentDate,
                'PROV_FEAR' => DB::raw('CURRENT_TIMESTAMP')
            ]);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_PROVIDER_REG005: Ocurrió un error al obtener el contenido.", $th->getMessage(), 500);
        }

        if ( !is_null($idSubcontract )) {
            try {
                $validateUpdateSubcontract = DB::table('S002V01TPESU')
                    ->where('PESU_IDPS', '=', $idSubcontract)
                    ->where('PESU_NULI', '=', $line)
                    ->update([
                        'PESU_NUPR' => $idProdiver,
                        'PESU_USMO' => $user,
                        'PESU_FEMO' => $currentDate,
                        'PESU_FEAR' => DB::raw('CURRENT_TIMESTAMP')
                    ]);
            } catch (\Throwable $th) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_PROVIDER_REG006: Ocurrió un error enlazar el proveedor con el subcontratista.", $th->getMessage(), 500);
            }

            if (!$validateUpdateSubcontract) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_PROVIDER_REG007: No se pudo enlazar el proveedor con el subcontratista.", [], 500);
            }
        }
        foreach ($arrReferences as $references) {
            if ($references['ESTADO'] == 'Activo' ||  $references['ESTADO'] == 'Nuevo') {
                $insertReferences = [
                    'PRRE_NUPR' => $idProdiver,
                    'PRRE_EMPR' => $references['RAZON_SOCIAL'],
                    'PRRE_CORR' => $references['CORREO'],
                    'PRRE_LADA' => $references['LADA'],
                    'PRRE_TELE' => $references['TELEFONO'],
                    'PRRE_XRFC' => $references['RFC'],
                    'PRRE_DIRE' => $references['DIRECCION'],
                    'PRRE_NULI' => $line,
                    'PRRE_USRE' => $user,
                    'PRRE_FERE' => $currentDate,
                    'PRRE_FEAR' => DB::raw('CURRENT_TIMESTAMP')
                ];
                try {
                    $resp = DB::table('S002V01TPRRE')->insert($insertReferences);
                    if (!$resp) {
                        DB::rollBack();
                        return $this->responseController->makeResponse(true, "ERR_PROVIDER_REG008: Ocurrió un error al obtener el contenido.", [], 500);
                    }
                } catch (\Throwable $th) {
                    DB::rollBack();
                    return $this->responseController->makeResponse(true, "ERR_PROVIDER_REG009: Ocurrió un error al obtener el contenido.", $th->getMessage(), 500);
                }
            }
        }

        foreach ($arrContacts as $contacts) {
            if ($contacts['ESTADO'] == 'Activo' || $contacts['ESTADO'] == 'Nuevo') {
                $insertContacts = [
                    'PRCO_NUPR' => $idProdiver,
                    'PRCO_NOMB' => $contacts['NOMBRE_CONTACTO'],
                    'PRCO_TIPO' => $contacts['TIPO_CONTACTO'],
                    'PRCO_CALL' => $contacts['CALLE'],
                    'PRCO_NUEX' => $contacts['NUMERO_EXTERIOR'],
                    'PRCO_NUIN' => $contacts['NUMERO_INTERIOR'] != '' ? $contacts['NUMERO_INTERIOR'] : null,
                    'PRCO_COPO' => $contacts['CODIGO_POSTAL'],
                    'PRCO_LOCA' => $contacts['LOCALIDAD'],
                    'PRCO_COLO' => $contacts['COLONIA'],
                    'PRCO_MUNI' => $contacts['MUNICIPIO'],
                    'PRCO_ENTI' => $contacts['ENTIDAD'],
                    'PRCO_PAIS' => $contacts['PAIS'],
                    'PRCO_LAD1' => $contacts['LADA1'],
                    'PRCO_TEL1' => $contacts['TELEFONO1'],
                    'PRCO_LAD2' => $contacts['LADA2'] != '' ? $contacts['LADA2'] : null,
                    'PRCO_TEL2' => $contacts['TELEFONO2'] != '' ? $contacts['TELEFONO2'] : null,
                    'PRCO_COR1' => $contacts['CORREO1'],
                    'PRCO_COR2' => $contacts['CORREO2'] != '' ? $contacts['CORREO2'] : null,
                    'PRCO_OBSE' => $contacts['OBSERVACIONES'],
                    'PRCO_NULI' => $line,
                    'PRCO_USRE' => $user,
                    'PRCO_FERE' => $currentDate,
                    'PRCO_FEAR' => DB::raw('CURRENT_TIMESTAMP')
                ];
                try {
                    $resp = DB::table('S002V01TPRCO')->insert($insertContacts);
                    
                    if (!$resp) {
                        DB::rollBack();
                        return $this->responseController->makeResponse(true, "ERR_PROVIDER_REG010: Ocurrió un error al obtener el contenido.", [], 500);
                    }
                } catch (\Throwable $th) {
                    DB::rollBack();
                    return $this->responseController->makeResponse(true, "ERR_PROVIDER_REG011: Ocurrió un error al obtener el contenido.", $th->getMessage(), 500);
                }
            }
        }

        foreach ($arrConditions as $conditions) {
            if ($conditions['ESTADO'] == 'Activo') {
                $insertConditions = [
                    'PRCN_NUPR' => $idProdiver,
                    'PRCN_TIPO' => $conditions['TIPO_CONDICION'],
                    'PRCN_INFO' => $conditions['INFORMACION'],
                    'PRCN_NULI' => $line,
                    'PRCN_USRE' => $user,
                    'PRCN_FERE' => $currentDate,
                    'PRCN_FEAR' => DB::raw('CURRENT_TIMESTAMP')
                ];
                try {
                    $resp = DB::table('S002V01TPRCN')->insert($insertConditions);
                    if (!$resp) {
                        DB::rollBack();
                        return $this->responseController->makeResponse(true, "ERR_PROVIDER_REG012: Ocurrió un error al obtener el contenido.", [], 500);
                    }
                } catch (\Throwable $th) {
                    DB::rollBack();
                    return $this->responseController->makeResponse(true, "ERR_PROVIDER_REG013: Ocurrió un error al obtener el contenido.", $th->getMessage(), 500);
                }
            }
        }

        foreach ($arrCatalogs as $catalogs) {
            if ($catalogs['ESTADO'] == 'Activo' || $catalogs['ESTADO'] == 'Nuevo') {
                $insertCatalogs = [
                    'PRCE_NUPR' => $idProdiver,
                    'PRCE_CATA' => $catalogs['CATALAGO'],
                    'PRCE_DESC' => $catalogs['DESCRIPCION'],
                    'PRCE_XURL' => $catalogs['URL_CATALAGO'],
                    'PRCE_NULI' => $line,
                    'PRCE_USRE' => $user,
                    'PRCE_FERE' => $currentDate,
                    'PRCE_FEAR' => DB::raw('CURRENT_TIMESTAMP')
                ];
                try {
                    $resp = DB::table('S002V01TPRCE')->insert($insertCatalogs);
                    if (!$resp) {
                        DB::rollBack();
                        return $this->responseController->makeResponse(true, "ERR_PROVIDER_REG014: Ocurrió un error al obtener el contenido.", [], 500);
                    }
                } catch (\Throwable $th) {
                    DB::rollBack();
                    return $this->responseController->makeResponse(true, "ERR_PROVIDER_REG015: Ocurrió un error al obtener el contenido.", $th->getMessage(), 500);
                }
            }
        }

        $response = [ 'idProvider' => $idProdiver ];
        DB::commit();
        return $this->responseController->makeResponse(false, "EXITO: Registro Exitoso", $response);
    }

    public function deleteProvider(Request $request) {
        $validator = Validator::make($request->all(), [
            'line' => 'required|string',
            'user' => 'required|string',
            'numero_proveedor' => 'required|string'
        ]);

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

        DB::beginTransaction();

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

        $line = $requestData['line'];

        try {
            $numeroProveedor = $this->encController->decrypt($requestData['numero_proveedor']);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_PROVIDER_DEL001: Ocurrió un error al obtener el número del proveedor.", $th->getMessage(), 500);
        }
        try {
            $user = $this->encController->decrypt($requestData['user']);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_PROVIDER_DEL002: Ocurrió un error al obtener el contenido.", $th->getMessage(), 500);
        }

        $update = [
            'PROV_ESTA' => 'Eliminado',
            'PROV_USMO' => $user,
            'PROV_FEMO' => $currentDate,
            'PROV_FEAR' => DB::raw('CURRENT_TIMESTAMP')
        ];
        try {
            $resp = DB::table('S002V01TPROV')->where('PROV_NUPR', '=', $numeroProveedor)->where('PROV_NULI', '=', $line)->update($update);
            if (!$resp) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_PROVIDER_DEL003: Ocurrió un error al obtener el contenido. ", [], 500);
            }
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_PROVIDER_DEL004: Ocurrió un error al obtener el contenido.", $th->getMessage(), 500);
        }

        try {
            $resp = DB::table('S002V01TPRRE')
                ->where('PRRE_NUPR', '=', $numeroProveedor)
                ->where('PRRE_NULI', '=', $line)
                ->update([
                    'PRRE_ESTA' => 'Eliminado',
                    'PRRE_USMO' => $user,
                    'PRRE_FEMO' => $currentDate,
                    'PRRE_FEAR' => DB::raw('CURRENT_TIMESTAMP')
                ]);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_PROVIDER_DEL006: Ocurrió un error al obtener el contenido.", $th->getMessage(), 500);
        }

        try {
            $resp = DB::table('S002V01TPRCO')
                ->where('PRCO_NUPR', '=', $numeroProveedor)
                ->where('PRCO_NULI', '=', $line)
                ->update([
                    'PRCO_ESTA' => 'Eliminado',
                    'PRCO_USMO' => $user,
                    'PRCO_FEMO' => $currentDate,
                    'PRCO_FEAR' => DB::raw('CURRENT_TIMESTAMP')
                ]);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_PROVIDER_DEL008: Ocurrió un error al obtener el contenido.", $th->getMessage(), 500);
        }

        try {
            $resp = DB::table('S002V01TPRCN')
                ->where('PRCN_NUPR', '=', $numeroProveedor)
                ->where('PRCN_NULI', '=', $line)
                ->update([
                    'PRCN_ESTA' => 'Eliminado',
                    'PRCN_USMO' => $user,
                    'PRCN_FEMO' => $currentDate,
                    'PRCN_FEAR' => DB::raw('CURRENT_TIMESTAMP')
                ]);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_PROVIDER_DEL010: Ocurrió un error al obtener el contenido.", $th->getMessage(), 500);
        }

        try {
            $resp = DB::table('S002V01TPRCE')
                ->where('PRCE_NUPR', '=', $numeroProveedor)
                ->where('PRCE_NULI', '=', $line)
                ->update([
                    'PRCE_ESTA' => 'Eliminado',
                    'PRCE_USMO' => $user,
                    'PRCE_FEMO' => $currentDate,
                    'PRCE_FEAR' => DB::raw('CURRENT_TIMESTAMP')
                ]);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_PROVIDER_DEL012: Ocurrió un error al obtener el contenido.", $th->getMessage(), 500);
        }

        DB::commit();
        return $this->responseController->makeResponse(false, "EXITO: Eliminación correcta",);

    }

    public function getProviderByArtitle(Request $request) {
        $validator = Validator::make($request->all(), [
            'NUMERO_ARTICULO' => 'required|string',
            'NUMERO_LINEA' => 'required|string',
        ]);

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

        $response = $request->all();
        $numeroArticulo = $this->encController->decrypt($response['NUMERO_ARTICULO']);
        try {
            $arrDescription = DB::table('S002V01TDEAR')
                ->where('DEAR_ESTA', '=', 'Activo')
                ->where('DEAR_IDAR', '=', $numeroArticulo)
                ->join('S002V01TPROV', 'DEAR_NUPR', '=', 'PROV_NUPR')
                ->get([
                    'DEAR_IDDE AS NUMERO_DESCRIPTION',
                    'PROV_NUPR AS NUMERO_PROVEEDOR',
                    'PROV_NOCO AS RAZON_SOCIAL',
                    'PROV_NOMB AS NOMBRE',
                    'PROV_APPA AS PRIMER_APELLIDO',
                    'PROV_APMA AS SEGUNDO_APELLIDO',
                    'PROV_CORR AS CORREO',
                    'PROV_LAD1 AS LADA1',
                    'PROV_TEL1 AS TELEFONO1',
                    'PROV_LAD2 AS LADA2',
                    'PROV_TEL2 AS TELEFONO2',
                    'PROV_TICO AS TIPO_CONTRIBUYENTE',
                    'PROV_XRFC AS RFC',
                    'PROV_XTAX AS TAXID',
                    'PROV_CRFI AS REGIMEN_FISCAL',
                    'PROV_TIPO AS TIPO',
                    'PROV_SIWE AS SITIOWEB',
                    'PROV_MEPA AS METODO_PAGO',
                    'PROV_ESTA AS ESTADO',
                    'PROV_USRE AS USUARIO_REGISTRA',
                    'PROV_FERE AS FECHA_REGISTRA',
                    'PROV_USMO AS USUARIO_MODIFICA',
                    'PROV_FEMO AS FECHA_MODIFICA',
                ]);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(true, "ERR_PROVIDER_GETBYARTITLE001: No se pudo realizar la consulta a la base.", $th->getMessage(), 500);
        }

        return $this->responseController->makeResponse(false, "ÉXITO", $arrDescription);

    }

    public function getArtitlesByProvider($encProvider, $user, $line) {
        DB::beginTransaction();

        // Se obtiene el número del proveedor
        try {
            $provider = $this->encController->decrypt($encProvider);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(
                true, 
                "ERR_ARTITLES_PROVIDER_GET000: Ocurrió un error al obtener el número del proveedor.", 
                $th->getMessage(), 
                500
            );
        }
        
        // Verifica su existe el proveedor
        try {
            $validateExist = DB::table('S002V01TPROV')
                ->where('PROV_NUPR', '=', $provider)
                ->where('PROV_NULI', '=', $line)
                ->exists();
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(
                true, 
                "ERR_ARTITLES_PROVIDER_GET001: Ocurrió un error al identificar el proveedor.", 
                $th->getMessage(), 
                500
            );
        }

        // Valida si existe el proveedor
        if (!$validateExist) {
            DB::rollBack();
            return $this->responseController->makeResponse(
                true, 
                "ERR_ARTITLES_PROVIDER_GET002: El proveedor no existe", 
                [], 
                500
            );
        }

        // Obtiene todos las descripciones de los artículos del proveedor
        try {
            $getDescriptionArtitles = DB::table('S002V01TDEAR')
                ->where('DEAR_NUPR', '=', $provider)
                ->where('DEAR_NULI', '=', $line)
                ->where('DEAR_ESTA', '=', 'Activo')
                ->get([
                    'DEAR_IDDE', # Identificador de la descripción
                    'DEAR_IMAG', # Imagenes de los artículos
                    'DEAR_DESC', # Descripción del artículo
                    'DEAR_CARA', # Características del artículo
                    'DEAR_COWE', # Compra web
                    'DEAR_IDUN', # Identificador del tipo de unidad
                    'DEAR_IDAR', # Identificador del artículo principal
                    'DEAR_USRE', # Usuario de registro
                    'DEAR_FERE', # Fecha de registro
                    'DEAR_USMO', # Usuario de modificación
                    'DEAR_FEMO', # Fecha de modificación
                ]);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_ARTITLES_PROVIDER_GET003: El proveedor no existe", $th->getMessage(), 500);
        }

        // El resultado pasa a ser un arreglo
        $arrDescriptionArtitles = json_decode(json_encode($getDescriptionArtitles), true);

        $arrDescription = array();
        foreach ($arrDescriptionArtitles as $keyDescription => $descriptionArtitle) {
            // Obtiene las imagenes y las pasa a un arreglo
            $arrImages = json_decode($descriptionArtitle['DEAR_IMAG']);
            $arrUrlImage = array();
            foreach ($arrImages as $keyImages => $images) {
                $responseDocument = $this->documentManagementController->privateGetPublicDocumentURL($images, $user, $line);
                if ($responseDocument['error']) {
                    return $this->responseController->makeResponse(true, "ERR_ARTITLES_PROVIDER_GET004: Ocurrió un error al obtener la URL de la imágen.", [], 500);
                }
                $arrUrlImage[] = $responseDocument['response']['public_uri'];
            }
            $descriptionArtitle['DEAR_IMAG'] = $arrUrlImage;

            // Obtiene la información principal del artículo
            try {
                $objArtitle = DB::table('S002V01TARTI')
                    ->where('ARTI_IDAR', '=', $descriptionArtitle['DEAR_IDAR'])
                    ->where('ARTI_NULI', '=', $line)
                    ->where('ARTI_ESTA', '=', 'Activo')
                    ->join('S002V01TFAMI', 'ARTI_COFA', '=', 'FAMI_COFA')
                    ->join('S002V01TSUBF', 'ARTI_COSU', '=', 'SUBF_COSU')
                    ->first([
                        'ARTI_IDAR', # Identificador del artículo
                        'ARTI_COFA', # Identificador de la familia del artículo
                        'FAMI_NOFA', # Nombre de la familia del artículo
                        'ARTI_COSU', # Identificador de la subfamilia del artículo
                        'SUBF_NOSU', # Nombre de la subfamilia del artículo
                        'ARTI_CODI', # Código de barras del artículo
                        'ARTI_NOMB', # Nombre principal del artículo
                        // 'ARTI_USRE', # Usuario de registro
                        // 'ARTI_FERE', # Fecha de registro
                        // 'ARTI_USMO', # Usuario de modificación
                        // 'ARTI_FEMO', # Fecha de modificación
                    ]);
            } catch (\Throwable $th) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_ARTITLES_PROVIDER_GET005: Ocurrió un error al obtener la información del artículo.", $th->getMessage(), 500);
            }
            // La información es guardada en el arreglo de respuesta
            foreach ($objArtitle as $keyArtitle => $artitle) {
                $descriptionArtitle[$keyArtitle] = $artitle;
            }

            // Se obtiene la unidad del artículo
            try {
                $objUnit = DB::table('S002V01TUNID')
                    ->where('UNID_IDUN', '=', $descriptionArtitle['DEAR_IDUN'])
                    ->where('UNID_NULI', '=', $line)
                    ->where('UNID_ESTA', '=', 'Activo')
                    ->first([
                        'UNID_IDUN', # Identificador de la unidad
                        'UNID_NOMB', # Nombre de la unidad
                        'UNID_ACRO', # Acrónimo de la unidad
                        'UNID_USRE', # Usuario de registro
                        'UNID_FERE', # Fecha de registro
                        'UNID_USMO', # Usuario de modificación
                        'UNID_FEMO', # Fecha de modificación
                    ]);
            } catch (\Throwable $th) {
                DB::rollBack();
                return $this->responseController->makeResponse(
                    true, 
                    "ERR_ARTITLES_PROVIDER_GET006: Ocurrió un error al obtener el tipo de unidad.", 
                    $th->getMessage(), 
                    500
                );
            }

            // La información es guardada en el arreglo de respuesta
            $descriptionArtitle['DEAR_IDUN'] = $objUnit;
            
            // Se obtienen los detalles de la descripción
            try {
                $getDetailsDescription = DB::table('S002V01TINAR')
                    ->where('INAR_IDDE', '=', $descriptionArtitle['DEAR_IDDE'])
                    ->where('INAR_NULI', '=', $line)
                    ->where('INAR_ESTA', '=', 'Activo')
                    ->get([
                        'INAR_IDIN', # Identificador de los detalles
                        'INAR_CODI', # Código del artículo
                        'INAR_MODE', # Módelo del artículo
                        'INAR_COMO', # Tipo de moneda
                        'INAR_PREC', # Precio unitario del artículo
                        'INAR_MOMI', # Mónto mínimo
                        'INAR_CARA', # Características
                        'INAR_USRE', # Usuario de registro
                        'INAR_FERE', # Fecha de registro
                        'INAR_USMO', # Usuario de modificación
                        'INAR_FEMO', # Fecha de modificación
                    ]);
            } catch (\Throwable $th) {
                DB::rollBack();
                return $this->responseController->makeResponse(
                    true, 
                    "ERR_ARTITLES_PROVIDER_GET007: Ocurrió un error al obtener los detalles de la descripción del artículo.", 
                    $th->getMessage(), 
                    500
                );
            }
            
            // La información es guardada en el arreglo de respuesta
            $descriptionArtitle['DETAILS'] = $getDetailsDescription;

            // Se almacen el arreglo de respuesta en el arreglo principal
            $arrDescription[] = $descriptionArtitle;
        }

        DB::commit(); # Para guardar los cambios en la base de datos
        return $this->responseController->makeResponse(false, "ÉXITO: Consulta exitosa", $arrDescription);
    }
    public function generateSheetProviderXLS($encProvider, $typeUser, $user, $line) {
        
        try {
            $idProvider = $this->encController->decrypt($encProvider);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET000: Ocurrió un error al obtener el número del proveedor.", 
                $th->getMessage(), 
                500
            );
        }

        try {
            $user = $this->encController->decrypt($user);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET001: Ocurrió un error al obtener el usuario.", 
                $th->getMessage(), 
                500
            );
        }

        if ($typeUser === 'P') {
            try {
                $arrProvider = (array) DB::table('S002V01TPROV')
                ->where('PROV_NUPR', '=', $idProvider)
                ->where('PROV_NULI', '=', $line)
                ->where('REFI_NULI', '=', $line)
                ->where('REFI_ESTA', '=', 'Activo')
                ->join('S002V01TREFI', 'REFI_CRFI', '=', 'PROV_CRFI')
                ->first([
                    'PROV_NUPR',
                    'PROV_IDPS',
                    'PROV_NOCO',
                    'PROV_NOMB',
                    'PROV_APPA',
                    'PROV_APMA',
                    'PROV_CORR',
                    'PROV_LAD1',
                    'PROV_TEL1',
                    'PROV_LAD2',
                    'PROV_TEL2',
                    'PROV_XRFC',
                    'PROV_XTAX',
                    'PROV_TIPO',
                    'PROV_TICO',
                    'PROV_SIWE',
                    'PROV_MEPA',
                    'PROV_CALL',
                    'PROV_NUEX',
                    'PROV_NUIN',
                    'PROV_COPO',
                    'PROV_COLO',
                    'PROV_MUNI',
                    'PROV_LOCA',
                    'PROV_ENTI',
                    'PROV_PAIS',
                    'PROV_ESTA',
                    'PROV_INEX',
                    'PROV_USRE',
                    'PROV_FERE',
                    'PROV_USMO',
                    'PROV_FEMO',
                    'PROV_FEAR',
                    'REFI_CRFI',
                    'REFI_DRFI',
                ]);
            } catch (\Throwable $th) {
                return $this->responseController->makeResponse(
                    true, 
                    "ERR_PROVIDER_SHEET002: Ocurrió un error al obtener la información del proveedor.", 
                    $th->getMessage(), 
                    500
                );
            }
        } else if ($typeUser === 'S') {
            try {
                $arrProvider = (array) DB::table('S002V01TPROV')
                ->where('PROV_IDPS', '=', $idProvider)
                ->where('PROV_NULI', '=', $line)
                ->where('REFI_NULI', '=', $line)
                ->where('REFI_ESTA', '=', 'Activo')
                ->join('S002V01TREFI', 'REFI_CRFI', '=', 'PROV_CRFI')
                ->first([
                    'PROV_NUPR',
                    'PROV_IDPS',
                    'PROV_NOCO',
                    'PROV_NOMB',
                    'PROV_APPA',
                    'PROV_APMA',
                    'PROV_CORR',
                    'PROV_LAD1',
                    'PROV_TEL1',
                    'PROV_LAD2',
                    'PROV_TEL2',
                    'PROV_XRFC',
                    'PROV_XTAX',
                    'PROV_TIPO',
                    'PROV_TICO',
                    'PROV_SIWE',
                    'PROV_MEPA',
                    'PROV_CALL',
                    'PROV_NUEX',
                    'PROV_NUIN',
                    'PROV_COPO',
                    'PROV_COLO',
                    'PROV_MUNI',
                    'PROV_LOCA',
                    'PROV_ENTI',
                    'PROV_PAIS',
                    'PROV_ESTA',
                    'PROV_INEX',
                    'PROV_USRE',
                    'PROV_FERE',
                    'PROV_USMO',
                    'PROV_FEMO',
                    'PROV_FEAR',
                    'REFI_CRFI',
                    'REFI_DRFI',
                ]);
            } catch (\Throwable $th) {
                return $this->responseController->makeResponse(
                    true, 
                    "ERR_PROVIDER_SHEET002: Ocurrió un error al obtener la información del proveedor.", 
                    $th->getMessage(), 
                    500
                );
            }
        }
        

        if ( empty($arrProvider) ) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET003: El proveedor seleccionado no existe.", 
                [], 
                500
            );
        }

        if ($arrProvider['PROV_ESTA'] !== 'Activo') {
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET004: El proveedor seleccionado no se encuentra activo.", 
                [], 
                500
            );
        }

        try {
            $arrReference = DB::table('S002V01TPRRE')
                ->where('PRRE_NUPR', '=', $idProvider)
                ->where('PRRE_NULI', '=', $line)
                ->where('PRRE_ESTA', '=', 'Activo')
                ->get([
                    'PRRE_IDRE',
                    'PRRE_EMPR',
                    'PRRE_CORR',
                    'PRRE_LADA',
                    'PRRE_TELE',
                    'PRRE_XRFC',
                    'PRRE_DIRE',
                ]);

            $arrReference = json_decode( json_encode($arrReference), true );
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET005: Ocurrió un error al obtener las referencias del proveedor.", 
                $th->getMessage(), 
                500
            );
        }

        try {
            $arrContact = DB::table('S002V01TPRCO')
                ->where('PRCO_NUPR', '=', $idProvider)
                ->where('PRCO_NULI', '=', $line)
                ->where('PRCO_ESTA', '=', 'Activo')
                ->get([
                    'PRCO_IDCP',
                    'PRCO_NOMB',
                    'PRCO_TIPO',
                    'PRCO_CALL',
                    'PRCO_NUEX',
                    'PRCO_NUIN',
                    'PRCO_COPO',
                    'PRCO_COLO',
                    'PRCO_LOCA',
                    'PRCO_MUNI',
                    'PRCO_ENTI',
                    'PRCO_PAIS',
                    'PRCO_LAD1',
                    'PRCO_TEL1',
                    'PRCO_LAD2',
                    'PRCO_TEL2',
                    'PRCO_COR1',
                    'PRCO_COR2',
                    'PRCO_OBSE',
                ]);

            $arrContact = json_decode( json_encode($arrContact), true );
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET006: Ocurrió un error al obtener los contactos del proveedor.", 
                $th->getMessage(), 
                500
            );
        }

        try {
            $arrCondition = DB::table('S002V01TPRCN')
                ->where('PRCN_NUPR', '=', $idProvider)
                ->where('PRCN_NULI', '=', $line)
                ->where('PRCN_ESTA', '=', 'Activo')
                ->get([
                    'PRCN_IDCM',
                    'PRCN_TIPO',
                    'PRCN_INFO',
                ]);
            $arrCondition = json_decode( json_encode($arrCondition), true );
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET007: Ocurrió un error al obtener las condiciones del proveedor.", 
                $th->getMessage(), 
                500
            );
        }
        
        try {
            $arrCatalog = DB::table('S002V01TPRCE')
                ->where('PRCE_NUPR', '=', $idProvider)
                ->where('PRCE_NULI', '=', $line)
                ->where('PRCE_ESTA', '=', 'Activo')
                ->get([
                    'PRCE_IDCE',
                    'PRCE_CATA',
                    'PRCE_DESC',
                    'PRCE_XURL',
                ]);
            $arrCatalog = json_decode( json_encode($arrCatalog), true );
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET008: Ocurrió un error al obtener los catálogos del proveedor.", 
                $th->getMessage(), 
                500
            );
        }

        try {
            $arrPayment = json_decode($arrProvider['PROV_MEPA']);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET009: El proveedor seleccionado no se encuentra activo.", 
                $th->getMessage(), 
                500
            );
        }


        $spreadsheet = new Spreadsheet();
        $activeWorksheet = $spreadsheet->getActiveSheet()->setTitle($arrProvider['PROV_NUPR']);


        $headerA1 = 'Número de Proveedor';
        $contentA1 = $arrProvider['PROV_NUPR'];
        $activeWorksheet->setCellValue("A1", $headerA1);
        $activeWorksheet->setCellValue("B1", $contentA1);
        $activeWorksheet->getStyle( "A1" )->getFont()->setBold(true);
        

        $headerB1 = 'Número de Subcontratista';
        $contentB1 = '';
        if ( $arrProvider['PROV_IDPS'] === null || $arrProvider['PROV_IDPS'] === '' ) {
            $contentB1 = 'Sin Subcontratista';
        } else {
            $contentB1 = $arrProvider['PROV_IDPS'];
        }
        $activeWorksheet->setCellValue("A2", $headerB1);
        $activeWorksheet->setCellValue("B2", $contentB1);
        $activeWorksheet->getStyle( "A2" )->getFont()->setBold(true);

        $headerC1 = "Nombre Comercial";
        $contentC1 = $arrProvider['PROV_NOCO'];
        $activeWorksheet->setCellValue("A3", $headerC1);
        $activeWorksheet->setCellValue("B3", $contentC1);
        $activeWorksheet->getStyle( "A3" )->getFont()->setBold(true);


        $headerD1 = 'Responsable';
        $contentD1 = $arrProvider['PROV_NOMB'].' '.$arrProvider['PROV_APPA'].' '.$arrProvider['PROV_APMA'] === null ? '' : $arrProvider['PROV_APMA'];
        $activeWorksheet->setCellValue("A4", $headerD1);
        $activeWorksheet->setCellValue("B4", $contentD1);
        $activeWorksheet->getStyle( "A4" )->getFont()->setBold(true);


        $headerE1 = 'Correo electrónico';
        $contentE1 = $arrProvider['PROV_CORR'];
        $activeWorksheet->setCellValue("A5", $headerE1);
        $activeWorksheet->setCellValue("B5", $contentE1);
        $activeWorksheet->getStyle( "A5" )->getFont()->setBold(true);


        $headerF1 = '';
        $contentF1 = '';
        if ($arrProvider['PROV_XRFC'] !== null && $arrProvider['PROV_XRFC'] !== '' && ($arrProvider['PROV_XTAX'] === '' || $arrProvider['PROV_XTAX'] === null)) {
            $headerF1 = 'R.F.C';
            $contentF1 = $arrProvider['PROV_XRFC'];
        } else if ($arrProvider['PROV_XTAX'] !== null && $arrProvider['PROV_XTAX'] !== '' && ($arrProvider['PROV_XRFC'] === '' || $arrProvider['PROV_XRFC'] === null)) {
            $headerF1 = 'TAX ID';
            $contentF1 = $arrProvider['PROV_XTAX'];
        } else {
            $headerF1 = 'R.F.C / TAX ID';
            $contentF1 = 'Sin contenido';
        }
        $activeWorksheet->setCellValue("A6", $headerF1);
        $activeWorksheet->setCellValue("B6", $contentF1);
        $activeWorksheet->getStyle( "A6" )->getFont()->setBold(true);


        $headerF1 = 'Régimen fiscal';
        $contentF1 = $arrProvider['REFI_DRFI'] . ' (' . $arrProvider['REFI_CRFI'] . ')';
        $activeWorksheet->setCellValue("A7", "Régimen Fiscal");
        $activeWorksheet->setCellValue("B7", $contentF1);
        $activeWorksheet->getStyle( "A7" )->getFont()->setBold(true);


        $headerH1 = 'Tipo de ciudadania';
        $contentH1 = $arrProvider['PROV_TIPO'];
        $activeWorksheet->setCellValue("A8", $headerH1);
        $activeWorksheet->setCellValue("B8", $contentH1);
        $activeWorksheet->getStyle( "A9" )->getFont()->setBold(true);


        $headerI1 = 'Tipo de contribuyente';
        $contentI1 = $arrProvider['PROV_TICO'];
        $activeWorksheet->setCellValue("A9", $headerI1);
        $activeWorksheet->setCellValue("B9", $contentI1);
        $activeWorksheet->getStyle( "A9" )->getFont()->setBold(true);

        $headerJ1 = 'Teléfono principal';
        $contentJ1 = '('.$arrProvider['PROV_LAD1'] .') '. $arrProvider['PROV_TEL1'];
        $activeWorksheet->setCellValue("A10", $headerJ1);
        $activeWorksheet->setCellValue("B10", $contentJ1);
        $activeWorksheet->getStyle( "A10" )->getFont()->setBold(true);

        $headerK1 = 'Teléfono secundario';
        $contentK1 = '';
        if ($arrProvider['PROV_LAD2'] !== null && $arrProvider['PROV_LAD2'] !== '' && $arrProvider['PROV_TEL2'] !== null && $arrProvider['PROV_TEL2'] !== '') {
            $contentK1 = '('.$arrProvider['PROV_LAD2'] .') '. $arrProvider['PROV_TEL2'];
        } else {
            $contentK1 = 'No aplica';
        }
        $activeWorksheet->setCellValue("A11", $headerK1);
        $activeWorksheet->setCellValue("B11", $contentK1);
        $activeWorksheet->getStyle( "A11" )->getFont()->setBold(true);


        $headerL1 = 'Enlace de sitio web';
        $contentL1 = '';
        if ($arrProvider['PROV_SIWE'] !== null && $arrProvider['PROV_SIWE'] !== '') {
            $contentL1 = $arrProvider['PROV_SIWE'];
        } else {
            $contentL1 = 'No aplica';
        }
        $activeWorksheet->setCellValue("A12", $headerL1);
        $activeWorksheet->setCellValue("B12", $contentL1);
        $activeWorksheet->getStyle( "A12" )->getFont()->setBold(true);


        $headerM1 = 'Calle ';
        $contentM1 = $arrProvider['PROV_CALL'];
        $activeWorksheet->setCellValue("A13", $headerM1);
        $activeWorksheet->setCellValue("B13", $contentM1);
        $activeWorksheet->getStyle( "A13" )->getFont()->setBold(true);


        $headerN1 = 'Número exterior';
        $contentN1 = $arrProvider['PROV_NUEX'];
        $activeWorksheet->setCellValue("A14", $headerN1);
        $activeWorksheet->setCellValue("B14", $contentN1);
        $activeWorksheet->getStyle( "A14" )->getFont()->setBold(true);


        $headerO1 = 'Número interior';
        $contentO1 = '';
        if ($arrProvider['PROV_NUIN'] !== null && $arrProvider['PROV_NUIN'] !== '') {
            $contentO1 = $arrProvider['PROV_NUIN'];
        } else {
            $contentO1 = 'No aplica';
        }
        $activeWorksheet->setCellValue("A15", $headerO1);
        $activeWorksheet->setCellValue("B15", $contentO1);
        $activeWorksheet->getStyle( "A15" )->getFont()->setBold(true);


        $headerP1 = 'Código postal';
        $contentP1 = $arrProvider['PROV_COPO'];
        $activeWorksheet->setCellValue("A16", $headerP1);
        $activeWorksheet->setCellValue("B16", $contentP1);
        $activeWorksheet->getStyle( "A16" )->getFont()->setBold(true);


        $headerQ1 = 'Colonia';
        if ($arrProvider['PROV_PAIS'] === 'MEX') {
            try {
                $arrColony = (array) DB::table('S002V01TCOLO')
                    ->where('COLO_NULI', '=', $line)
                    ->where('COLO_COCO', '=', $arrProvider['PROV_COLO'])
                    ->where('COLO_COPO', '=', $arrProvider['PROV_COPO'])
                    ->where('COLO_ESTA', '=', 'Activo')
                    ->first([ 'COLO_COCO', 'COLO_NOCO' ]);
            } catch (\Throwable $th) {
                return $this->responseController->makeResponse(
                    true, 
                    "ERR_PROVIDER_SHEET010: Ocurrió un error al obtener la información de la colonia.", 
                    $th->getMessage(), 
                    500
                );
            }
            if ( empty($arrColony) ) {
                return $this->responseController->makeResponse(
                    true, 
                    "ERR_PROVIDER_SHEET011: No se encontró la colonia asociada al proveedor.", 
                    [], 
                    500
                );
            }
            $contentQ1 = $arrColony['COLO_NOCO'] . ' (' . $arrColony['COLO_COCO'] . ')';
        } else {
            $contentQ1 = $arrProvider['PROV_COLO'];
        }
        $activeWorksheet->setCellValue("A17", $headerQ1);
        $activeWorksheet->setCellValue("B17", $contentQ1);
        $activeWorksheet->getStyle( "A17" )->getFont()->setBold(true);


        $headerR1 = 'Localidad';
        $contentR1 = '';
        if ($arrProvider['PROV_PAIS'] === 'MEX') {
            if ( !is_null($arrProvider['PROV_LOCA']) &&  !empty($arrProvider['PROV_LOCA']) ) {
                try {
                    $arrLocate = (array) DB::table('S002V01TLOCA')
                        ->where('LOCA_NULI', '=', $line)
                        ->where('LOCA_COLO', '=', $arrProvider['PROV_LOCA'])
                        ->where('LOCA_COES', '=', $arrProvider['PROV_ENTI'])
                        ->where('LOCA_ESTA', '=', 'Activo')
                        ->first([ 'LOCA_COLO', 'LOCA_NOLO' ]);
                } catch (\Throwable $th) {
                    return $this->responseController->makeResponse(
                        true, 
                        "ERR_PROVIDER_SHEET012: Ocurrió un error al obtener la información de la localidad.", 
                        $th->getMessage(), 
                        500
                    );
                }

                if ( empty($arrLocate) ) {
                    return $this->responseController->makeResponse(
                        true, 
                        "ERR_PROVIDER_SHEET013: No se encontró la localidad asociada al proveedor.", 
                        [], 
                        500
                    );
                }

                $contentR1 = $arrLocate['LOCA_NOLO'] . ' (' . $arrLocate['LOCA_NOLO'] . ')';
            } else {
                $contentR1 = 'Sin localidad';
            }
        } else {
            if ( !is_null($arrProvider['PROV_LOCA']) &&  !empty($arrProvider['PROV_LOCA']) ) {
                $contentR1 = $arrProvider['PROV_LOCA'];
            } else {
                $contentR1 = 'Sin localidad';
            }
        }
        $activeWorksheet->setCellValue("A18", $headerR1);
        $activeWorksheet->setCellValue("B18", $contentR1);
        $activeWorksheet->getColumnDimension("R")->setAutoSize(true);
        $activeWorksheet->getStyle( "A18" )->getFont()->setBold(true);


        $headerS1 = 'Municipio';
        $contentS1 = '';
        if ($arrProvider['PROV_PAIS'] === 'MEX') {
            try {
                $arrMunicipality = (array) DB::table('S002V01TMUNI')
                    ->where('MUNI_NULI', '=', $line)
                    ->where('MUNI_COMU', '=', $arrProvider['PROV_MUNI'])
                    ->where('MUNI_COES', '=', $arrProvider['PROV_ENTI'])
                    ->where('MUNI_ESTA', '=', 'Activo')
                    ->first([ 'MUNI_COMU', 'MUNI_NOMU' ]);
            } catch (\Throwable $th) {
                return $this->responseController->makeResponse(
                    true, 
                    "ERR_PROVIDER_SHEET014: Ocurrió un error al obtener la información del municipio.", 
                    $th->getMessage(), 
                    500
                );
            }
            if ( empty($arrMunicipality) ) {
                return $this->responseController->makeResponse(
                    true, 
                    "ERR_PROVIDER_SHEET015: No se encontró el municipio asociada al proveedor.", 
                    [], 
                    500
                );
            }
            $contentS1 = $arrMunicipality['MUNI_NOMU'] . ' (' . $arrMunicipality['MUNI_COMU'] . ')';
        } else {
            $contentS1 = $arrProvider['PROV_MUNI'];
        }
        $activeWorksheet->setCellValue("A19", $headerS1);
        $activeWorksheet->setCellValue("B19", $contentS1);
        $activeWorksheet->getStyle( "A19" )->getFont()->setBold(true);


        $headerT1 = 'Entidad Federativa';
        $contentT1 = '';
        if ($arrProvider['PROV_PAIS'] === 'MEX') {
            try {
                $arrEntity = (array) DB::table('S002V01TESTA')
                    ->where('ESTA_NULI', '=', $line)
                    ->where('ESTA_COES', '=', $arrProvider['PROV_ENTI'])
                    ->where('ESTA_COPA', '=', $arrProvider['PROV_PAIS'])
                    ->where('ESTA_ESTA', '=', 'Activo')
                    ->first([ 'ESTA_COES', 'ESTA_NOES' ]);
            } catch (\Throwable $th) {
                return $this->responseController->makeResponse(
                    true, 
                    "ERR_PROVIDER_SHEET016: Ocurrió un error al obtener la información de la entidad federativa.", 
                    $th->getMessage(), 
                    500
                );
            }
            if ( empty($arrEntity) ) {
                return $this->responseController->makeResponse(
                    true, 
                    "ERR_PROVIDER_SHEET017: No se encontró la entidad federativa asociada al proveedor.", 
                    [], 
                    500
                );
            }
            $contentT1 = $arrEntity['ESTA_NOES'] . ' (' . $arrEntity['ESTA_COES'] . ')';
        } else {
            $contentT1 = $arrProvider['PROV_ENTI'];
        }
        $activeWorksheet->setCellValue("A20", $headerT1);
        $activeWorksheet->setCellValue("B20", $contentT1);
        $activeWorksheet->getStyle( "A20" )->getFont()->setBold(true);

               
        $headerU1 = 'País';
        try {
            $arrCountry = (array) DB::table('S002V01TPAIS')
                ->where('PAIS_NULI', '=', $line)
                ->where('PAIS_IDPA', '=', $arrProvider['PROV_PAIS'])
                ->first([ 'PAIS_IDPA', 'PAIS_NOMB' ]);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET018: Ocurrió un error al obtener la información del país.", 
                $th->getMessage(), 
                500
            );
        }
        if ( empty($arrCountry) ) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET019: No se encontró el país asociado al proveedor.", 
                [], 
                500
            );
        } 
        $contentU1 = $arrCountry['PAIS_NOMB'] . ' (' . $arrCountry['PAIS_IDPA'] . ')';
        $activeWorksheet->setCellValue("A21", $headerU1);
        $activeWorksheet->setCellValue("B21", $contentU1);
        $activeWorksheet->getStyle( "A21" )->getFont()->setBold(true);

        $activeWorksheet->getColumnDimension("A")->setAutoSize(true);
        $activeWorksheet->getColumnDimension("B")->setAutoSize(true);


        $arrAlphabet = $this->resourcesController->arrAlphabet;


        $spreadsheet->createSheet();
        $spreadsheet->setActiveSheetIndex(1);
        $activeWorksheetContact = $spreadsheet->getActiveSheet()->setTitle('Contactos');
        $arrHeaderContact = array(
            'ID',                               // A
            'Contacto',                         // B
            'Tipo de Contacto',                 // C
            'Correo Electrónico Principal',     // D
            'Correo Electrónico Secundario',    // E
            'Teléfono Principal',               // F
            'Teléfono Secundario',              // G
            'Calle',                            // H
            'Número Exterior',                  // I
            'Numero Interior',                  // J
            'Código Postal',                    // K
            'Colonia',                          // L
            'Localidad',                        // M
            'Municipio',                        // N
            'Entidad Federativa',               // O
            'País',                             // P
            'Observaciones'                     // Q
        );
        foreach ($arrContact as $key => $contact) {
            $key = $key + 2;

            $activeWorksheetContact->setCellValue("A".$key, $contact['PRCO_IDCP']);
            $activeWorksheetContact->setCellValue("B".$key, $contact['PRCO_NOMB']);
            $activeWorksheetContact->setCellValue("C".$key, $contact['PRCO_TIPO']);
            $activeWorksheetContact->setCellValue("D".$key, $contact['PRCO_COR1']);
            if ( !is_null( $contact['PRCO_COR2'] ) ) {
                $activeWorksheetContact->setCellValue("E".$key, $contact['PRCO_COR2']);
            } else {
                $activeWorksheetContact->setCellValue("E".$key, 'No aplica' );
            }
            $activeWorksheetContact->setCellValue("F".$key, $contact['PRCO_LAD1'] . ' ' . $contact['PRCO_TEL1'] );
            if ( !is_null( $contact['PRCO_LAD2'] ) && !is_null( $contact['PRCO_TEL2'] ) ) {
                $activeWorksheetContact->setCellValue("G".$key, $contact['PRCO_LAD2'] . ' ' . $contact['PRCO_TEL2'] );
            } else {
                $activeWorksheetContact->setCellValue("G".$key, 'No aplica' );
            }
            $activeWorksheetContact->setCellValue("H".$key, $contact['PRCO_CALL'] );
            $activeWorksheetContact->setCellValue("I".$key, $contact['PRCO_NUEX'] );
            if ( !is_null( $contact['PRCO_NUIN'] ) ) {
                $activeWorksheetContact->setCellValue("J".$key, $contact['PRCO_NUIN']);
            } else {
                $activeWorksheetContact->setCellValue("J".$key, 'No aplica' );
            }
            $activeWorksheetContact->setCellValue("K".$key, $contact['PRCO_COPO'] );
            $activeWorksheetContact->setCellValue("L".$key, $contact['PRCO_COLO'] );
            if ( !is_null($contact['PRCO_LOCA']) && $contact['PRCO_LOCA'] !== 'Sin localidad' ) {
                $activeWorksheetContact->setCellValue("M".$key, $contact['PRCO_LOCA'] );
            } else {
                $activeWorksheetContact->setCellValue("M".$key, 'No aplica' );
            }
            $activeWorksheetContact->setCellValue("N".$key, $contact['PRCO_MUNI'] );
            $activeWorksheetContact->setCellValue("O".$key, $contact['PRCO_ENTI'] );
            $activeWorksheetContact->setCellValue("P".$key, $contact['PRCO_PAIS'] );
            $activeWorksheetContact->setCellValue("Q".$key, $contact['PRCO_OBSE'] );
        }
        foreach ($arrHeaderContact as $keyHeaderContact => $headerContact) {
            $activeWorksheetContact->setCellValue( $arrAlphabet[$keyHeaderContact]."1", $headerContact);
            $activeWorksheetContact->getColumnDimension( $arrAlphabet[$keyHeaderContact] )->setAutoSize(true);
            $activeWorksheetContact->getStyle( $arrAlphabet[$keyHeaderContact]."1" )->getFont()->setBold(true);
        }

        $spreadsheet->createSheet();
        $spreadsheet->setActiveSheetIndex(2);
        $activeWorksheetReference = $spreadsheet->getActiveSheet()->setTitle('Referencias');
        $arrHeaderReference = array(
            'ID',           // A
            'Empresa',      // B
            'R.F.C.',       // C
            'Correo',       // D
            'Teléfono',     // E
            'Dirección',    // F
        );
        foreach ($arrReference as $key => $reference) {
            $key = $key + 2;

            $activeWorksheetReference->setCellValue("A".$key, $reference['PRRE_IDRE']);
            $activeWorksheetReference->setCellValue("B".$key, $reference['PRRE_EMPR']);
            $activeWorksheetReference->setCellValue("C".$key, $reference['PRRE_XRFC']);
            $activeWorksheetReference->setCellValue("D".$key, $reference['PRRE_CORR']);
            $activeWorksheetReference->setCellValue("E".$key, $reference['PRRE_LADA'] .' '. $reference['PRRE_TELE']);
            $activeWorksheetReference->setCellValue("F".$key, $reference['PRRE_DIRE']);

        }
        foreach ($arrHeaderReference as $keyHeaderReference => $headerReference) {
            $activeWorksheetReference->setCellValue( $arrAlphabet[$keyHeaderReference]."1", $headerReference);
            $activeWorksheetReference->getColumnDimension( $arrAlphabet[$keyHeaderReference] )->setAutoSize(true);
            $activeWorksheetReference->getStyle( $arrAlphabet[$keyHeaderReference]."1" )->getFont()->setBold(true);
        }


        $spreadsheet->createSheet();
        $spreadsheet->setActiveSheetIndex(3);
        $activeWorksheetCatalog = $spreadsheet->getActiveSheet()->setTitle('Catálogos');
        $arrHeaderCatalog = array(
            'ID',           // A
            'Catálogo',     // B
            'Descripción',  // C
            'URL',          // D
        );
        foreach ($arrCatalog as $key => $catalog) {
            $key = $key + 2;
            $activeWorksheetCatalog->setCellValue("A".$key, $catalog['PRCE_IDCE']);
            $activeWorksheetCatalog->setCellValue("B".$key, $catalog['PRCE_CATA']);
            $activeWorksheetCatalog->setCellValue("C".$key, $catalog['PRCE_DESC']);
            $activeWorksheetCatalog->setCellValue("D".$key, $catalog['PRCE_XURL']);

        }
        foreach ($arrHeaderCatalog as $keyHeaderCatalog => $headerCatalog) {
            $activeWorksheetCatalog->setCellValue( $arrAlphabet[$keyHeaderCatalog]."1", $headerCatalog);
            $activeWorksheetCatalog->getColumnDimension( $arrAlphabet[$keyHeaderCatalog] )->setAutoSize(true);
            $activeWorksheetCatalog->getStyle( $arrAlphabet[$keyHeaderCatalog]."1" )->getFont()->setBold(true);
        }


        $spreadsheet->createSheet();
        $spreadsheet->setActiveSheetIndex(4);
        $activeWorksheetPayment = $spreadsheet->getActiveSheet()->setTitle('Métodos de Pago');
        $arrHeaderPayment = array(
            'ID',               // A
            'Método de Pago',   // B
        );
        $cont = 2;
        foreach ($arrPayment as $key => $payment) {
            $activeWorksheetPayment->setCellValue("A".$cont, $key);
            $activeWorksheetPayment->setCellValue("B".$cont, $payment);
            $cont++;
        }
        foreach ($arrHeaderPayment as $keyHeaderPayment => $headerPayment) {
            $activeWorksheetPayment->setCellValue( $arrAlphabet[$keyHeaderPayment]."1", $headerPayment);
            $activeWorksheetPayment->getColumnDimension( $arrAlphabet[$keyHeaderPayment] )->setAutoSize(true);
            $activeWorksheetPayment->getStyle( $arrAlphabet[$keyHeaderPayment]."1" )->getFont()->setBold(true);
        }

        $spreadsheet->createSheet();
        $spreadsheet->setActiveSheetIndex(5);
        $activeWorksheetCondition = $spreadsheet->getActiveSheet()->setTitle('Condiciones');
        $arrHeaderCondition = array(
            'ID',                   // A
            'Tipo de Condición',    // B
            'Información',          // C
        );
        foreach ($arrCondition as $key => $condition) {
            $key = $key + 2;
            $activeWorksheetCondition->setCellValue("A".$key, $condition['PRCN_IDCM']);
            $activeWorksheetCondition->setCellValue("B".$key, $condition['PRCN_TIPO']);
            $activeWorksheetCondition->setCellValue("C".$key, $condition['PRCN_INFO']);

        }
        foreach ($arrHeaderCondition as $keyHeaderCondition => $headerCondition) {
            $activeWorksheetCondition->setCellValue( $arrAlphabet[$keyHeaderCondition]."1", $headerCondition);
            $activeWorksheetCondition->getColumnDimension( $arrAlphabet[$keyHeaderCondition] )->setAutoSize(true);
            $activeWorksheetCondition->getStyle( $arrAlphabet[$keyHeaderCondition]."1" )->getFont()->setBold(true);
        }


        $nuli = $this->resourcesController->formatSecuence($line, 2);
        $como = 'GEAD';         // Código del módulo
        $cldo = 'IN';           // Código de la clasificación
        $fecr = date('ymd');    // Fecha en la se carga el archivo

        try {
            $arrSecuence = (array) DB::table('S002V01TAFAL')
                ->where('AFAL_COMO', '=', $como)
                ->where('AFAL_CLDO', '=', $cldo)
                ->where('AFAL_NULI', '=', $line)
                ->orderBy('AFAL_NUSE', 'desc')
                ->first([ 'AFAL_NUSE' ]);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET020: Ocurrió un error al obtener la información de la secuencia.", 
                $th->getMessage(), 
                500
            );
        }
        $nuse = 1;              // Secuencia del documento
        if ( !empty( $arrSecuence ) && !is_null( $arrSecuence ) ) {
            $nuse = intval( $arrSecuence['AFAL_NUSE'] ) + 1; 
        }
        $nuse = $this->resourcesController->formatSecuence($nuse, 6);
        $nuve = $this->resourcesController->formatSecuence('1', 2);
        $noar = 'ficha_de_proveedor_' . $arrProvider['PROV_NUPR'];
        $exte = 'xlsx';

        if ($_SERVER['SERVER_NAME'] === '192.168.100.105') {
            $filePath = 'C:/ITTEC/SAM/Dev/SistemaMantenimiento/sistema-mantenimiento-back/public/public_files/';    // API JEAN
        } else {
            $filePath = 'C:\inetpub\wwwroot\sam\public_files\\';     // API QA
        }

        $fileName = $nuli.'-'.$como.'-'.$cldo.'-'.$fecr.'-'.$nuse.'='.$nuve.'='.$noar.'.'.$exte;

        $tempFile = $filePath.$fileName;

        if ( file_exists( $tempFile ) ) {
            if ( !unlink( $tempFile ) ) {
                return $this->responseController->makeResponse(
                    true, 
                    "ERR_PROVIDER_SHEET021: Ocurrió un error al eliminar el siguiente archivo: " . $tempFile, 
                    [], 
                    500
                );
            }
        }
        
        try {
            $writer = new Xlsx($spreadsheet);
            ob_start();
            $writer->save('php://output');
            $base64 = base64_encode(ob_get_clean());
            $validate = \File::put( $tempFile, base64_decode($base64));
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET022: Ocurrió un error al guardar el documento.", 
                $th->getMessage(), 
                500
            );
        }


        $ubic = Storage::putFile('files', new File($tempFile));
        $ubic = str_replace("/", "\\", $ubic);
        if ($_SERVER['SERVER_NAME'] === '192.168.100.105') {
            $ubic = "C:\ITTEC\SAM\Dev\SistemaMantenimiento\sistema-mantenimiento-back\storage\app\\" . $ubic;
        } else {
            $ubic = "C:\inetpub\wwwroot\sam\storage\app\\" . $ubic;
        }
        $tama = filesize($ubic);
        $usac = json_encode([$user]);

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

        try {
            $validateInsert = DB::table('S002V01TAFAL')->insert([
                'AFAL_NULI' => $line,
                'AFAL_COMO' => $como,
                'AFAL_CLDO' => $cldo,
                'AFAL_FECR' => $fecr,
                'AFAL_NUSE' => $nuse,
                'AFAL_NUVE' => $nuve,
                'AFAL_NOAR' => $noar,
                'AFAL_EXTE' => $exte,
                'AFAL_TAMA' => $tama,
                'AFAL_UBIC' => $ubic,
                'AFAL_USAC' => $usac,
                'AFAL_USRE' => $user,
                'AFAL_FERE' => $currentDate,
            ]);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET023: Ocurrió un error guardar los datos a la tabla final de archivos.", 
                $th->getMessage(), 
                500
            );
        }

        if ( !$validateInsert ) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET024: No se pudo guardar la ficha del proveedor en la base de datos.", 
                [], 
                500
            );
        }
        
        if ($_SERVER['SERVER_NAME'] === '192.168.100.105') {
            $urlPublic = 'http://192.168.100.105:8000/public_files/' . $fileName;
        } else {
            $urlPublic = $this->functionsController->getApiURI().'sam/public_files/' . $fileName;
        }

        return $this->responseController->makeResponse(false, "EXITO: Modificación Exitosa", [ 'url' => $urlPublic ]);

    }

    public function generateSheetProviderPDF($encProvider, $typeUser, $user, $line) {

        try {
            $idProvider = $this->encController->decrypt($encProvider);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET000: Ocurrió un error al obtener el número del proveedor.", 
                $th->getMessage(), 
                500
            );
        }

        try {
            $user = $this->encController->decrypt($user);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET001: Ocurrió un error al obtener el usuario.", 
                $th->getMessage(), 
                500
            );
        }

        $arrProvider = [];
        if ($typeUser === 'P') {
            try {
                $arrProvider = (array) DB::table('S002V01TPROV')
                ->where('PROV_NUPR', '=', $idProvider)
                ->where('PROV_NULI', '=', $line)
                ->where('REFI_NULI', '=', $line)
                ->where('REFI_ESTA', '=', 'Activo')
                ->join('S002V01TREFI', 'REFI_CRFI', '=', 'PROV_CRFI')
                ->first([
                    'PROV_NUPR',
                    'PROV_IDPS',
                    'PROV_NOCO',
                    // 'PROV_CRFI',
                    'PROV_NOMB',
                    'PROV_APPA',
                    'PROV_APMA',
                    'PROV_CORR',
                    'PROV_LAD1',
                    'PROV_TEL1',
                    'PROV_LAD2',
                    'PROV_TEL2',
                    'PROV_XRFC',
                    'PROV_XTAX',
                    'PROV_TIPO',
                    'PROV_TICO',
                    'PROV_SIWE',
                    'PROV_MEPA',
                    'PROV_CALL',
                    'PROV_NUEX',
                    'PROV_NUIN',
                    'PROV_COPO',
                    'PROV_COLO',
                    'PROV_MUNI',
                    'PROV_LOCA',
                    'PROV_ENTI',
                    'PROV_PAIS',
                    'PROV_ESTA',
                    'PROV_INEX',
                    'PROV_USRE',
                    'PROV_FERE',
                    'PROV_USMO',
                    'PROV_FEMO',
                    'PROV_FEAR',
                    'REFI_CRFI',
                    'REFI_DRFI',
                ]);
            } catch (\Throwable $th) {
                return $this->responseController->makeResponse(
                    true, 
                    "ERR_PROVIDER_SHEET002: Ocurrió un error al obtener la información del proveedor.", 
                    $th->getMessage(), 
                    500
                );
            }
        } else if ($typeUser === 'S') {
            try {
                $arrProvider = (array) DB::table('S002V01TPROV')
                ->where('PROV_IDPS', '=', $idProvider)
                ->where('PROV_NULI', '=', $line)
                ->where('REFI_NULI', '=', $line)
                ->where('REFI_ESTA', '=', 'Activo')
                ->join('S002V01TREFI', 'REFI_CRFI', '=', 'PROV_CRFI')
                ->first([
                    'PROV_NUPR',
                    'PROV_IDPS',
                    'PROV_NOCO',
                    // 'PROV_CRFI',
                    'PROV_NOMB',
                    'PROV_APPA',
                    'PROV_APMA',
                    'PROV_CORR',
                    'PROV_LAD1',
                    'PROV_TEL1',
                    'PROV_LAD2',
                    'PROV_TEL2',
                    'PROV_XRFC',
                    'PROV_XTAX',
                    'PROV_TIPO',
                    'PROV_TICO',
                    'PROV_SIWE',
                    'PROV_MEPA',
                    'PROV_CALL',
                    'PROV_NUEX',
                    'PROV_NUIN',
                    'PROV_COPO',
                    'PROV_COLO',
                    'PROV_MUNI',
                    'PROV_LOCA',
                    'PROV_ENTI',
                    'PROV_PAIS',
                    'PROV_ESTA',
                    'PROV_INEX',
                    'PROV_USRE',
                    'PROV_FERE',
                    'PROV_USMO',
                    'PROV_FEMO',
                    'PROV_FEAR',
                    'REFI_CRFI',
                    'REFI_DRFI',
                ]);
            } catch (\Throwable $th) {
                return $this->responseController->makeResponse(
                    true, 
                    "ERR_PROVIDER_SHEET002: Ocurrió un error al obtener la información del proveedor.", 
                    $th->getMessage(), 
                    500
                );
            }
        }
        if ( empty($arrProvider) ) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET003: El proveedor seleccionado no existe.", 
                [], 
                500
            );
        }

        if ($arrProvider['PROV_ESTA'] !== 'Activo') {
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET004: El proveedor seleccionado no se encuentra activo.", 
                [], 
                500
            );
        }

        try {
            $arrReference = DB::table('S002V01TPRRE')
                ->where('PRRE_NUPR', '=', $idProvider)
                ->where('PRRE_NULI', '=', $line)
                ->where('PRRE_ESTA', '=', 'Activo')
                ->get([
                    'PRRE_IDRE',
                    'PRRE_EMPR',
                    'PRRE_CORR',
                    'PRRE_LADA',
                    'PRRE_TELE',
                    'PRRE_XRFC',
                    'PRRE_DIRE',
                ]);
            $arrReference = json_decode( json_encode($arrReference), true );
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET005: Ocurrió un error al obtener las referencias del proveedor.", 
                $th->getMessage(), 
                500
            );
        }

        try {
            $arrContact = DB::table('S002V01TPRCO')
                ->where('PRCO_NUPR', '=', $idProvider)
                ->where('PRCO_NULI', '=', $line)
                ->where('PRCO_ESTA', '=', 'Activo')
                ->get([
                    'PRCO_IDCP',
                    'PRCO_NOMB',
                    'PRCO_TIPO',
                    'PRCO_CALL',
                    'PRCO_NUEX',
                    'PRCO_NUIN',
                    'PRCO_COPO',
                    'PRCO_COLO',
                    'PRCO_LOCA',
                    'PRCO_MUNI',
                    'PRCO_ENTI',
                    'PRCO_PAIS',
                    'PRCO_LAD1',
                    'PRCO_TEL1',
                    'PRCO_LAD2',
                    'PRCO_TEL2',
                    'PRCO_COR1',
                    'PRCO_COR2',
                    'PRCO_OBSE',
                ]);
            $arrContact = json_decode( json_encode($arrContact), true );
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET006: Ocurrió un error al obtener los contactos del proveedor.", 
                $th->getMessage(), 
                500
            );
        }
        
        try {
            $arrCondition = DB::table('S002V01TPRCN')
                ->where('PRCN_NUPR', '=', $idProvider)
                ->where('PRCN_NULI', '=', $line)
                ->where('PRCN_ESTA', '=', 'Activo')
                ->get([
                    'PRCN_IDCM',
                    'PRCN_TIPO',
                    'PRCN_INFO',
                ]);
            $arrCondition = json_decode( json_encode($arrCondition), true );
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET007: Ocurrió un error al obtener las condiciones del proveedor.", 
                $th->getMessage(), 
                500
            );
        }
        
        try {
            $arrCatalog = DB::table('S002V01TPRCE')
                ->where('PRCE_NUPR', '=', $idProvider)
                ->where('PRCE_NULI', '=', $line)
                ->where('PRCE_ESTA', '=', 'Activo')
                ->get([
                    'PRCE_IDCE',
                    'PRCE_CATA',
                    'PRCE_DESC',
                    'PRCE_XURL',
                ]);
            $arrCatalog = json_decode( json_encode($arrCatalog), true );
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET008: Ocurrió un error al obtener los catálogos del proveedor.", 
                $th->getMessage(), 
                500
            );
        }

        try {
            $arrPayment = json_decode($arrProvider['PROV_MEPA']);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET009: El proveedor seleccionado no se encuentra activo.", 
                $th->getMessage(), 
                500
            );
        }

        $arrProvider['CONDITION'] = $arrCondition;
        $arrProvider['CATALOG'] = $arrCatalog;
        $arrProvider['PROV_MEPA'] = $arrPayment;

        $html = '<!DOCTYPE html>
        <html lang="en">
        <head>
            <meta charset="UTF-8">
            <meta name="viewport" content="width=device-width, initial-scale=1.0">
            <title>Document</title>
            <style>
                body {
                    font-family: Arial, Helvetica, sans-serif;
                    font-size: 8pt;
                }
                .section-title { 
                    margin: 20px 0;
                    font-size: 12pt;
                }

                table {
                    width: 100%;
                }
                thead tr th {
                    text-align: start;
                    padding: 0 16px;
                    height: 52px;
                    border-bottom-width: 1px;
                    border-bottom-style: solid;
                    border-bottom-color: #CCC;
                }

                tbody tr td {
                    padding: 0 16px;
                    height: 52px;
                    border-bottom-width: 1px;
                    border-bottom-style: solid;
                    border-bottom-color: #CCC;
                }

                p {
                    text-align: justify;
                }

            </style>
        </head>
        <body>
            <div class="item">
                <b>Número Proveedor:</b>
                <span>'.$arrProvider['PROV_NUPR'].'</span>
            </div>
            <div class="item">
                <b>Nombre Comercial: </b>
                <span>'.$arrProvider['PROV_NOCO'].'</span>
            </div>
            <div class="item">
                <b>Responsable: </b>
                <span>'.$arrProvider['PROV_NOMB'].' '.$arrProvider['PROV_APPA'].' '.$arrProvider['PROV_APMA'].'</span>
            </div>
            <div class="item">
                <b>Correo electrónico: </b>
                <span>'.$arrProvider['PROV_CORR'].'</span>
            </div>';
        
        if ($arrProvider['PROV_XRFC'] !== null && $arrProvider['PROV_XRFC'] !== '' && ($arrProvider['PROV_XTAX'] === '' || $arrProvider['PROV_XTAX'] === null)) {
            $html.='<div class="item">
                        <b>R.F.C: </b>
                        <span>'.$arrProvider['PROV_XRFC'].'</span>
                    </div>';
        } else if ($arrProvider['PROV_XTAX'] !== null && $arrProvider['PROV_XTAX'] !== '' && ($arrProvider['PROV_XRFC'] === '' || $arrProvider['PROV_XRFC'] === null)) {
            $html.='<div class="item">
                        <b>TAX ID: </b>
                        <span>'.$arrProvider['PROV_XTAX'].'</span>
                    </div>';
        } else {
            $html.='<div class="item">
                        <b>R.F.C / TAX ID: </b>
                        <span> Sin contenido </span>
                    </div>';
        }

        $html.='<div class="item">
                    <b>Régimen fiscal: </b>
                    <span>'.$arrProvider['REFI_DRFI'] . ' (' . $arrProvider['REFI_CRFI'] . ')'.'</span>
                </div>
                <div class="item">
                    <b>Tipo de ciudadania: </b>
                    <span>'.$arrProvider['PROV_TIPO'].'</span>
                </div>
                <div class="item">
                    <b>Tipo de contribuyente: </b>
                    <span>'.$arrProvider['PROV_TICO'].'</span>
                </div>
                <div class="item">
                    <b>Teléfono principal: </b>
                    <span>'.'('.$arrProvider['PROV_LAD1'] .') '. $arrProvider['PROV_TEL1'].'</span>
                </div>';
        
        if ($arrProvider['PROV_LAD2'] !== null && $arrProvider['PROV_LAD2'] !== '' && $arrProvider['PROV_TEL2'] !== null && $arrProvider['PROV_TEL2'] !== '') {
            $html.='<div class="item">
                        <b>Teléfono secundario: </b>
                        <span>'.'('.$arrProvider['PROV_LAD2'] .') '. $arrProvider['PROV_TEL2'].'</span>
                    </div>';
        } else {
            $html.='<div class="item">
                        <b>Teléfono secundario: </b>
                        <span>No aplica</span>
                    </div>';
        }


        if ($arrProvider['PROV_SIWE'] !== null && $arrProvider['PROV_SIWE'] !== '') {
            $html.='<div class="item">
                        <b>Enlace de sitio web: </b>
                        <span>'.$arrProvider['PROV_SIWE'].'</span>
                    </div>';
        } else {
            $html.='<div class="item">
                        <b>Enlace de sitio web: </b>
                        <span>No aplica</span>
                    </div>';
        }

        $address = $arrProvider['PROV_CALL'].' '.$arrProvider['PROV_NUEX'] . ' ';
        if ($arrProvider['PROV_NUIN'] !== null && $arrProvider['PROV_NUIN'] !== '') {
            $address .= $arrProvider['PROV_NUIN'] . ' ';
        }
        $address .= $arrProvider['PROV_COPO'] . ', ';

        if ($arrProvider['PROV_PAIS'] === 'MEX') {
            try {
                $arrColony = (array) DB::table('S002V01TCOLO')
                    ->where('COLO_NULI', '=', $line)
                    ->where('COLO_COCO', '=', $arrProvider['PROV_COLO'])
                    ->where('COLO_COPO', '=', $arrProvider['PROV_COPO'])
                    ->where('COLO_ESTA', '=', 'Activo')
                    ->first([ 'COLO_COCO', 'COLO_NOCO' ]);
            } catch (\Throwable $th) {
                return $this->responseController->makeResponse(
                    true, 
                    "ERR_PROVIDER_SHEET010: Ocurrió un error al obtener la información de la colonia.", 
                    $th->getMessage(), 
                    500
                );
            }
            if ( empty($arrColony) ) {
                return $this->responseController->makeResponse(
                    true, 
                    "ERR_PROVIDER_SHEET011: No se encontró la colonia asociada al proveedor.", 
                    [], 
                    500
                );
            }
            $address .= $arrColony['COLO_NOCO'] . ', ';
        } else {
            $address .= $arrProvider['PROV_COLO'] . ', ';
        }
        

        if ($arrProvider['PROV_PAIS'] === 'MEX') {
            if ( !is_null($arrProvider['PROV_LOCA']) &&  !empty($arrProvider['PROV_LOCA']) ) {
                try {
                    $arrLocate = (array) DB::table('S002V01TLOCA')
                        ->where('LOCA_NULI', '=', $line)
                        ->where('LOCA_COLO', '=', $arrProvider['PROV_LOCA'])
                        ->where('LOCA_COES', '=', $arrProvider['PROV_ENTI'])
                        ->where('LOCA_ESTA', '=', 'Activo')
                        ->first([ 'LOCA_COLO', 'LOCA_NOLO' ]);
                } catch (\Throwable $th) {
                    return $this->responseController->makeResponse(
                        true, 
                        "ERR_PROVIDER_SHEET012: Ocurrió un error al obtener la información de la localidad.", 
                        $th->getMessage(), 
                        500
                    );
                }
    
                if ( empty($arrLocate) ) {
                    return $this->responseController->makeResponse(
                        true, 
                        "ERR_PROVIDER_SHEET013: No se encontró la localidad asociada al proveedor.", 
                        [], 
                        500
                    );
                }
    
                $address .= $arrLocate['LOCA_NOLO'] . ', ';
            } 
        } else {
            if ( !is_null($arrProvider['PROV_LOCA']) &&  !empty($arrProvider['PROV_LOCA']) ) {
                $address .= $arrProvider['PROV_LOCA'] . ', ';
            }
        }
        

        if ($arrProvider['PROV_PAIS'] === 'MEX') {
            try {
                $arrMunicipality = (array) DB::table('S002V01TMUNI')
                    ->where('MUNI_NULI', '=', $line)
                    ->where('MUNI_COMU', '=', $arrProvider['PROV_MUNI'])
                    ->where('MUNI_COES', '=', $arrProvider['PROV_ENTI'])
                    ->where('MUNI_ESTA', '=', 'Activo')
                    ->first([ 'MUNI_COMU', 'MUNI_NOMU' ]);
            } catch (\Throwable $th) {
                return $this->responseController->makeResponse(
                    true, 
                    "ERR_PROVIDER_SHEET014: Ocurrió un error al obtener la información del municipio.", 
                    $th->getMessage(), 
                    500
                );
            }
            if ( empty($arrMunicipality) ) {
                return $this->responseController->makeResponse(
                    true, 
                    "ERR_PROVIDER_SHEET015: No se encontró el municipio asociada al proveedor.", 
                    [], 
                    500
                );
            }
            $address .= $arrMunicipality['MUNI_NOMU'] . ', ';
        } else {
            $address .= $arrProvider['PROV_MUNI'] . ', ';
        }
        

        if ($arrProvider['PROV_PAIS'] === 'MEX') {
            try {
                $arrEntity = (array) DB::table('S002V01TESTA')
                    ->where('ESTA_NULI', '=', $line)
                    ->where('ESTA_COES', '=', $arrProvider['PROV_ENTI'])
                    ->where('ESTA_COPA', '=', $arrProvider['PROV_PAIS'])
                    ->where('ESTA_ESTA', '=', 'Activo')
                    ->first([ 'ESTA_COES', 'ESTA_NOES' ]);
            } catch (\Throwable $th) {
                return $this->responseController->makeResponse(
                    true, 
                    "ERR_PROVIDER_SHEET016: Ocurrió un error al obtener la información de la entidad federativa.", 
                    $th->getMessage(), 
                    500
                );
            }
            if ( empty($arrEntity) ) {
                return $this->responseController->makeResponse(
                    true, 
                    "ERR_PROVIDER_SHEET017: No se encontró la entidad federativa asociada al proveedor.", 
                    [], 
                    500
                );
            }
            $address .= $arrEntity['ESTA_NOES'] . ', ';
        } else {
            $address .= $arrProvider['PROV_ENTI'] . ', ';
        }     

        try {
            $arrCountry = (array) DB::table('S002V01TPAIS')
                ->where('PAIS_NULI', '=', $line)
                ->where('PAIS_IDPA', '=', $arrProvider['PROV_PAIS'])
                ->first([ 'PAIS_IDPA', 'PAIS_NOMB' ]);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET018: Ocurrió un error al obtener la información del país.", 
                $th->getMessage(), 
                500
            );
        }
        if ( empty($arrCountry) ) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET019: No se encontró el país asociado al proveedor.", 
                [], 
                500
            );
        } 
        $address .= $arrCountry['PAIS_NOMB'] . '.';


        $html.='<div class="item">
            <b>Dirección: </b>
            <span>'.$address.'</span>
        </div>';


        if ( !empty($arrContact) ) {
            $html.='<div class="section-title">
                        <b>Contactos</b>
                    </div>';
            $html.='<table cellspacing="0" cellpadding="0">
                        <thead>
                            <tr>
                                <th>ID</th>
                                <th>Contacto</th>
                                <th>Tipo de Contacto</th>
                                <th>Correo Electrónico</th>
                                <th>Teléfono</th>
                                <th>Direcciones</th>
                                <th>Observaciones</th>
                            </tr>
                        </thead>
                        <tbody>';
            foreach ($arrContact as $keyContact => $contact) {
                $html.=    '<tr>
                                <td>'.$contact['PRCO_IDCP'].'</td>
                                <td>'.$contact['PRCO_NOMB'].'</td>
                                <td>'.$contact['PRCO_TIPO'].'</td>
                                <td>'.$contact['PRCO_COR1'].'</td>
                                <td>'.$contact['PRCO_LAD1'].' '.$contact['PRCO_TEL1'].'</td>
                                <td>'.$contact['PRCO_CALL'].'</td>
                                <td>'.$contact['PRCO_OBSE'].'</td>
                            </tr>';
            }
            $html.=    '</tbody>
                    </table>';
        }

        if ( !empty($arrReference) ) {
            $html.='<div class="section-title">
                        <b>Referencias</b>
                    </div>';
            $html.='<table cellspacing="0" cellpadding="0">
                        <thead>
                            <tr>
                                <th>ID</th>
                                <th>Empresa</th>
                                <th>R.F.C.</th>
                                <th>Correo Electrónico</th>
                                <th>Teléfono</th>
                                <th>Dirección</th>
                            </tr>
                        </thead>
                        <tbody>';
            foreach ($arrReference as $keyReference => $reference) {
                $html.=    '<tr>
                                <td>'.$reference['PRRE_IDRE'].'</td>
                                <td>'.$reference['PRRE_EMPR'].'</td>
                                <td>'.$reference['PRRE_XRFC'].'</td>
                                <td>'.$reference['PRRE_CORR'].'</td>
                                <td>'.$reference['PRRE_LADA'].' '.$reference['PRRE_TELE'].'</td>
                                <td>'.$reference['PRRE_DIRE'].'</td>
                            </tr>';
            }
            $html.=    '</tbody>
                    </table>';
        }
        
        if ( !empty($arrPayment) ) {
            $html.='<div class="section-title">
                        <b>Métodos de Pago</b>
                    </div>';

            $html.='<ol style="margin: 0;">';
            foreach ($arrPayment as $keyPayment => $payment) {
                $html.='<li>'.$payment.'</li>';
            }
            $html.='</ol>';
        }

        if ( !empty($arrCatalog) ) {
            $html.='<div class="section-title">
                        <b>Catálogos</b>
                    </div>';

            $html.='<table cellspacing="0" cellpadding="0">
                        <thead>
                            <tr>
                                <th>ID</th>
                                <th>Catálogo</th>
                                <th>Descripción</th>
                                <th>URL</th>
                            </tr>
                        </thead>
                        <tbody>';
            foreach ($arrCatalog as $keyCatalog => $catalog) {
                $html.=    '<tr>
                                <td>'.$catalog['PRCE_IDCE'].'</td>
                                <td>'.$catalog['PRCE_CATA'].'</td>
                                <td>'.$catalog['PRCE_DESC'].'</td>
                                <td>'.$catalog['PRCE_XURL'].'</td>
                            </tr>';
            }
            $html.=    '</tbody>
                    </table>';
        }

        if ( !empty($arrCondition) ) {
            foreach ($arrCondition as $keyCondition => $condition) {
                $html.='<div class="section-title">
                            <b>'.$condition['PRCN_TIPO'].'</b>
                        </div>';
                $html.='<p>'.$condition['PRCN_INFO'].'</p>';
            }
        }

        $html.='</body></html>';

        $nuli = $this->resourcesController->formatSecuence($line, 2);
        $como = 'GEAD';         // Código del módulo
        $cldo = 'IN';           // Código de la clasificación
        $fecr = date('ymd');    // Fecha en la se carga el archivo

        try {
            $arrSecuence = (array) DB::table('S002V01TAFAL')
                ->where('AFAL_COMO', '=', $como)
                ->where('AFAL_CLDO', '=', $cldo)
                ->where('AFAL_NULI', '=', $line)
                ->orderBy('AFAL_NUSE', 'desc')
                ->first([ 'AFAL_NUSE' ]);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET020: Ocurrió un error al obtener la información de la secuencia.", 
                $th->getMessage(), 
                500
            );
        }
        $nuse = 1;              // Secuencia del documento
        if ( !empty( $arrSecuence ) && !is_null( $arrSecuence ) ) {
            $nuse = intval( $arrSecuence['AFAL_NUSE'] ) + 1; 
        }
        $nuse = $this->resourcesController->formatSecuence($nuse, 6);
        $nuve = $this->resourcesController->formatSecuence('1', 2);
        $noar = 'ficha_de_proveedor_' . $arrProvider['PROV_NUPR'];

        $nuli = $this->resourcesController->formatSecuence($line, 2);
        $como = 'GEAD';         // Código del módulo
        $cldo = 'IN';           // Código de la clasificación
        $fecr = date('ymd');    // Fecha en la se carga el archivo
        try {
            $arrSecuence = (array) DB::table('S002V01TAFAL')
                ->where('AFAL_COMO', '=', $como)
                ->where('AFAL_CLDO', '=', $cldo)
                ->where('AFAL_NULI', '=', $line)
                ->orderBy('AFAL_NUSE', 'desc')
                ->first([ 'AFAL_NUSE' ]);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET020: Ocurrió un error al obtener la información de la secuencia.", 
                $th->getMessage(), 
                500
            );
        }
        $nuse = 1;              // Secuencia del documento
        if ( !empty( $arrSecuence ) && !is_null( $arrSecuence ) ) {
            $nuse = intval( $arrSecuence['AFAL_NUSE'] ) + 1; 
        }
        $nuse = $this->resourcesController->formatSecuence($nuse, 6);
        $nuve = $this->resourcesController->formatSecuence('1', 2);
        $noar = 'ficha_de_proveedor_' . $arrProvider['PROV_NUPR'];
        $exte = 'pdf';        
    
        if ($_SERVER['SERVER_NAME'] === '192.168.100.105') {
            $filePath = 'C:/ITTEC/SAM/Dev/SistemaMantenimiento/sistema-mantenimiento-back/public/public_files/';    // API JEAN
        } else {
            $filePath = 'C:\inetpub\wwwroot\sam\public_files\\';     // API QA
        }

        $fileName = $nuli.'-'.$como.'-'.$cldo.'-'.$fecr.'-'.$nuse.'='.$nuve.'='.$noar.'.'.$exte;
        
        $tempFile = $filePath . $fileName;

        $dompdf = new Dompdf();
        $dompdf ->loadHtml($html);
        $dompdf->setPaper('A4', 'portrait');
        $dompdf->render();


        $output = $dompdf->output();
        file_put_contents($tempFile, $output);
        $ubic = Storage::putFile('files', new File($tempFile));

        $ubic = str_replace("/", "\\", $ubic);
        if ($_SERVER['SERVER_NAME'] === '192.168.100.105') {
            $ubic = "C:\ITTEC\SAM\Dev\SistemaMantenimiento\sistema-mantenimiento-back\storage\app\\" . $ubic;
        } else {
            $ubic = "C:\inetpub\wwwroot\sam\storage\app\\" . $ubic;
        }
        $tama = filesize($ubic);
        $usac = json_encode([$user]);

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

        try {
            $validateInsert = DB::table('S002V01TAFAL')->insert([
                'AFAL_NULI' => $line,
                'AFAL_COMO' => $como,
                'AFAL_CLDO' => $cldo,
                'AFAL_FECR' => $fecr,
                'AFAL_NUSE' => $nuse,
                'AFAL_NUVE' => $nuve,
                'AFAL_NOAR' => $noar,
                'AFAL_EXTE' => $exte,
                'AFAL_TAMA' => $tama,
                'AFAL_UBIC' => $ubic,
                'AFAL_USAC' => $usac,
                'AFAL_USRE' => $user,
                'AFAL_FERE' => $currentDate,
            ]);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET023: Ocurrió un error guardar los datos a la tabla final de archivos.", 
                $th->getMessage(), 
                500
            );
        }

        if ( !$validateInsert ) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_PROVIDER_SHEET024: No se pudo guardar la ficha del proveedor en la base de datos.", 
                [], 
                500
            );
        }

        if ($_SERVER['SERVER_NAME'] === '192.168.100.105') {
            $urlPublic = 'http://192.168.100.105:8000/public_files/' . $fileName;
        } else {
            $urlPublic = $this->functionsController->getApiURI().'sam/public_files/' . $fileName;
        }

        return $this->responseController->makeResponse(false, "EXITO: Modificación Exitosa", [ 'url' => $urlPublic ]);
    }
}