<?php
/*
    Desarrollador: Ing. Jean Jairo Benitez Meza
    Ultima Modificación: 11/04/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\ResourcesController;
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\Database\Query\JoinClause;
use Illuminate\Support\Facades\Storage;
use Illuminate\Http\File;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Dompdf\Dompdf;


class OrderController extends Controller {

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

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

    public function getOrders($user, $line) {

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

        try {
            $arrOrders = DB::table('S002V01TORCO')
                ->where('ORCO_NULI', '=', $line)
                ->where('DESP_NULI', '=', $line)
                ->where('PROV_NULI', '=', $line)
                ->where('LINE_NULI', '=', $line)
                ->join('S002V01TLINE', 'LINE_IDLI', '=', 'ORCO_IDLI')
                ->join('S002V01TPROV', 'PROV_NUPR', '=', 'LINE_NUPR')
                ->join('S002V01TDESP', 'DESP_IDDE', '=', 'ORCO_IDDE')
                ->get([
                    'ORCO_NUOR AS NUMERO_ORDEN', // Número de Orden
                    'LINE_IDLI AS NUMERO_SOLICITUD_COMPRA', // Identificador de línea de solicitud de compra
                    'PROV_NUPR AS NUMERO_PROVEEDOR', // Número de Proveedor
                    'PROV_NOCO AS NOMBRE_COMERCIAL', // Nombre Comercial del Proveedor
                    'PROV_TIPO AS TIPO_NACIONALIDAD', // Tipo de Nacionalidad
                    'DESP_IDDE AS ID_DESPACHO', // Número de Despacho
                    'DESP_NODE AS NOMBRE_DESPACHO', // Nombre del Despacho
                    'ORCO_ESTA AS ESTADO', // Estado
                    'ORCO_USRE AS USUARIO_REGISTRA', // Usuario Registra
                    'ORCO_FERE AS FECHA_REGISTRA', // Fecha Registra
                    'ORCO_USMO AS USUARIO_MODIFICA', // Usuario Modifica
                    'ORCO_FEMO AS FECHA_MODIFICA', // Fecha Modifica
                ]);
            $arrOrders = json_decode(json_encode($arrOrders), true);
        } catch ( \Throwable $th ) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_ORDER_GET000: Ocurrió un error al momento de obtener las órdenes de compra.", 
                $th->getMessage(), 
                500
            );
        }

        $responseCheckLatestUpdate = $this->resourcesController->checkLatestUpdate($arrOrders, $line);
        if ($responseCheckLatestUpdate['error']) {
            return $this->responseController->makeResponse(true, $responseCheckLatestUpdate['msg'], [], 500);
        }
        $arrOrders = $responseCheckLatestUpdate['response'];

        return $this->responseController->makeResponse(false, "ÉXITO: Consulta Exitosa", $arrOrders);
    }
    public function createOrder(Request $request){
        $validator = Validator::make($request->all(), [
            'NUMERO_SOLICITUD' => 'required|string',
            'DESPACHO' => 'required|string',
            'PROPOSITO' => 'required|string',
            'ID_MANT_PREV' => 'nullable|string',
            'ID_MANT_CORR' => 'nullable|string',
            'NUMERO_LINEA' => 'required|integer',
            'USUARIO' => 'required|string',
        ]);

        if ($validator->fails()) {
            return $this->responseController->makeResponse(
                true,
                "ERR_ORDER_REG000: Se encontraron uno o más errores.",
                $this->responseController->makeErrors($validator->errors()->messages()),
                401
            );
        }
        DB::beginTransaction();
        $requestData = $request->all();
        $arrToWorkflow = array();

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

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

        try {
            $idOrder = DB::table('S002V01TORCO')->insertGetId([
                'ORCO_NULI' => $requestData['NUMERO_LINEA'],
                'ORCO_IDLI' => $requestData['NUMERO_SOLICITUD'],
                'ORCO_IDDE' => $requestData['DESPACHO'],
                'ORCO_USRE' => $user,
                'ORCO_FERE' => $currentDate,
                'ORCO_FEAR' => DB::raw('CURRENT_TIMESTAMP'),
            ]);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_ORDER_REG002: Ocurrió un error al registrar la orden de compra.", $th->getMessage(), 500);
        }
        if (!$idOrder) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_ORDER_REG003: No se pudo registrar la orden de compra.", [], 401);
        }

        $idMantPrev = null;
        $idMantCorr = null;
        if ($requestData['PROPOSITO'] === 'MANT-PREV' && !is_null($requestData['ID_MANT_PREV'])) {
            $idMantPrev = $requestData['ID_MANT_PREV'];
        } else if ($requestData['PROPOSITO'] === 'MANT-CORR' && !is_null($requestData['ID_MANT_CORR'])) {
            $idMantCorr = $requestData['ID_MANT_CORR'];
        }
        
        try {
            $validateUpdate = DB::table('S002V01TLINE')
                ->where('LINE_IDLI', '=', $requestData['NUMERO_SOLICITUD'])
                ->where('LINE_NULI', '=', $requestData['NUMERO_LINEA'])
                ->update([
                    'LINE_ESTA' => 'En OC',
                    'LINE_OTPR' => $idMantPrev,
                    'LINE_OTCO' => $idMantCorr,
                    'LINE_USMO' => $user,
                    'LINE_FEMO' => $currentDate,
                    'LINE_FEAR' => DB::raw('CURRENT_TIMESTAMP'),
                ]);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_ORDER_REG004: Ocurrió un error al modificar la línea de solitud de compra.", $th->getMessage(), 500);
        }
        if (!$validateUpdate) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_ORDER_REG005: No se pudo modificar la línea de solicitud de compra.", [], 401);
        }


        try {
            $idHistoryOrder = DB::table('S002V01THIOR')->insertGetId([
                'HIOR_NULI' => $requestData['NUMERO_LINEA'],
                'HIOR_NUOR' => $idOrder,
                'HIOR_POEN' => 10,
                'HIOR_USRE' => $user,
                'HIOR_FERE' => $currentDate,
                'HIOR_FEAR' => DB::raw('CURRENT_TIMESTAMP'),
            ]);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_ORDER_REG006: Ocurrió un error al insertar el registro en el historial.", $th->getMessage(), 500);
        }
        if (!$idHistoryOrder) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_ORDER_REG007: No se pudo insertar el registro en el HISTORIAL.", [], 401);
        }

        DB::commit();
        return $this->responseController->makeResponse(false, "ÉXITO: Generación de Órdenes Exitosas");

    }
    public function getOrderByNumber($order, $provider, $user, $line){
        $arrResponseCheckUser = $this->resourcesController->checkUserEnc($user, $line);
        if ($arrResponseCheckUser['error']) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, 'ERR_WAREHOUSE_GETBY000:'.$arrResponseCheckUser['msg'], [], 401);
        }

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

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

        try {
            $arrOrders = (array) DB::table('S002V01TORCO')
                ->where('ORCO_NUOR', '=', $order)
                ->where('ORCO_NULI', '=', $line)
                ->where('DESP_NULI', '=', $line)
                ->where('LINE_NULI', '=', $line)
                ->where('PROV_NULI', '=', $line)
                ->where('LINE_ESTA', '!=', 'Cancelado')
                ->where('DESP_ESTA', '=', 'Activo')
                ->where('PROV_ESTA', '=', 'Activo')
                ->join('S002V01TLINE', 'ORCO_IDLI', '=', 'LINE_IDLI')
                ->join('S002V01TDESP', 'ORCO_IDDE', '=', 'DESP_IDDE')
                ->join('S002V01TPROV', 'LINE_NUPR', '=', 'PROV_NUPR')
                ->first([
                    'ORCO_NUOR AS NUMERO_ORDEN',
                    'ORCO_ESTA AS ESTADO_ORDEN',
                    'ORCO_USRE AS USUARIO_REGISTRA',
                    'ORCO_FERE AS FECHA_REGISTRA',
                    'ORCO_USMO AS USUARIO_MODIFICA',
                    'ORCO_FEMO AS FECHA_MODIFICA',
                    'LINE_IDLI AS NUMERO_SOLICITUD_COMPRA',
                    'LINE_OTPR AS ORDEN_TRABAJO_PREVENTIVO',
                    'LINE_OTCO AS ORDEN_TRABAJO_CORRECTIVO',
                    'DESP_IDDE AS ID_DESPACHO',
                    'DESP_NODE AS NOMBRE_DESPACHO',
                    'DESP_NOMB AS NOMBRE_RESPONSABLE',
                    'DESP_APPA AS APELLIDO_PATERNO_RESPONSABLE',
                    'DESP_APMA AS APELLIDO_MATERNO_RESPONSABLE',
                    'DESP_LADA AS LADA',
                    'DESP_TELE AS TELEFONO',
                    'DESP_CORR AS CORREO_ELECTRONICO',
                    'DESP_CALL AS CALLE',
                    'DESP_NUEX AS NUMERO_EXTERIOR',
                    'DESP_NUIN AS NUMERO_INTERIOR',
                    'DESP_COPO AS CODIGO_POSTAL',
                    'DESP_LOCA AS LOCALIDAD',
                    'DESP_COLO AS COLONIA',
                    'DESP_MUNI AS MUNICIPIO',
                    'DESP_ENTI AS ENTIDAD_FEDERATIVA',
                    'DESP_PAIS AS PAIS',
                    'DESP_COME AS COMENTARIOS',
                    'PROV_NUPR AS NUMERO_PROVEEDOR',
                    'PROV_NOCO AS NOMBRE_COMERCIAL',
                    'PROV_NOMB AS NOMBRE_PROVEEDOR',
                    'PROV_APPA AS APELLIDO_PATERNO_PROVEEDOR',
                    'PROV_APMA AS APELLIDO_MATERNO_PROVEEDOR',
                    'PROV_CORR AS CORREO_ELECTRONICO_PROVEEDOR',
                    'PROV_LAD1 AS LADA1_PROVEEDOR',
                    'PROV_TEL1 AS TELEFONO1_PROVEEDOR',
                    'PROV_LAD2 AS LADA2_PROVEEDOR',
                    'PROV_TEL2 AS TELEFONO2_PROVEEDOR',
                    'PROV_XRFC AS RFC',
                    'PROV_XTAX AS TAXID',
                ]);
        } catch ( \Throwable $th ) {
            return $this->responseController->makeResponse( 
                true, 
                "ERR_ORDER_GET002: Ocurrió un error al momento de obtener las órdenes de compra.", 
                $th->getMessage(), 
                500
            );
        }

        if ( empty($arrOrders) || is_null($arrOrders) ) {
            return $this->responseController->makeResponse( true, "ERR_ORDER_GET003: No se pudo obtener los datos de las órdenes de compra", [], 500);
        }
        
        try {
            $getArtitles = DB::table('S002V01TARSE')
                ->where('ARSE_IDLI', '=', $arrOrders['NUMERO_SOLICITUD_COMPRA'])
                ->where('ARSE_ESTA', '!=', 'Eliminado')
                ->where('ARSE_NULI', '=', $line)
                ->where('ARTI_ESTA', '=', 'Activo')
                ->where('ARTI_NULI', '=', $line)
                ->where('FAMI_ESTA', '=', 'Activo')
                ->where('FAMI_NULI', '=', $line)
                ->where('SUBF_ESTA', '=', 'Activo')
                ->where('SUBF_NULI', '=', $line)
                ->where('DEAR_ESTA', '=', 'Activo')
                ->where('DEAR_NULI', '=', $line)
                ->where('INAR_ESTA', '=', 'Activo')
                ->where('INAR_NULI', '=', $line)
                ->where('UNID_ESTA', '=', 'Activo')
                ->where('UNID_NULI', '=', $line)
                ->join('S002V01TARTI', 'ARSE_IDAR', '=', 'ARTI_IDAR')
                ->join('S002V01TINAR', 'ARSE_IDIN', '=', 'INAR_IDIN')
                ->join('S002V01TDEAR', 'DEAR_IDDE', '=', 'INAR_IDDE')
                ->join('S002V01TFAMI', 'ARTI_COFA', '=', 'FAMI_COFA')
                ->join('S002V01TSUBF', 'ARTI_COSU', '=', 'SUBF_COSU')
                ->join('S002V01TUNID', 'DEAR_IDUN', '=', 'UNID_IDUN')
                ->get([
                    'ARSE_IDAS AS ID_ARTICULO_SELECCION',
                    'FAMI_COFA AS CODIGO_FAMILIA',
                    'FAMI_NOFA AS NOMBRE_FAMILIA',
                    'SUBF_COSU AS CODIGO_SUBFAMILIA',
                    'SUBF_NOSU AS NOMBRE_SUBFAMILIA',
                    'ARTI_IDAR AS ID_ARTICULO',
                    'ARTI_CODI AS CODIGO',
                    'ARTI_NOMB AS NOMRE_ARTICULO',
                    'DEAR_IDDE AS ID_DESCRIPCION',
                    'DEAR_IMAG AS IMAGEN',
                    'DEAR_DESC AS DESCRIPCION',
                    'DEAR_CARA AS CARACTERISTICAS',
                    'DEAR_COWE AS COMPRA_WEB',
                    'INAR_IDIN AS ID_INFORMACION',
                    'INAR_CODI AS CODIGO_ARTICULO',
                    'INAR_MODE AS MODELO_ARTICULO',
                    'INAR_COMO AS CODIGO_MONEDA',
                    'INAR_PREC AS PRECIO',
                    'INAR_MOMI AS MONTO_MINIMO',
                    'INAR_CARA AS CARACTERISTIAS_INFORMACION',
                    'UNID_IDUN AS ID_UNIDAD',
                    'UNID_NOMB AS NOMBRE_UNIDAD',
                    'UNID_ACRO AS ACRONIMO_UNIDAD',
                ]);
            $arrArtitles = json_decode(json_encode($getArtitles), true);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse( 
                true, 
                "ERR_ORDER_GET004: Ocurrió un error al momento de obtener los artículos de las órdenes de compra.", 
                $th->getMessage(), 
                500
            );
        }
        if (empty($arrArtitles) || is_null($arrArtitles)) {
            return $this->responseController->makeResponse( true, "ERR_ORDER_GET005: No se pudo obtener los artículos de las órdenes de compra.", [], 500);
        }


        $index = 0;
        $arrArtitlesTemp = array();
        foreach ($arrArtitles as $keyArtitle => $artitles) {
            if ($keyArtitle === 0) {
                $arrArtitlesTemp[$index] = $artitles;
                $arrArtitlesTemp[$index]['CANTIDAD'] = 1;
            } else {
                if ( in_array($artitles['ID_INFORMACION'], array_column($arrArtitlesTemp, 'ID_INFORMACION')) ) {
                    $indexArtitle = array_search($artitles['ID_INFORMACION'], array_column($arrArtitlesTemp, 'ID_INFORMACION'));
                    $arrArtitlesTemp[$indexArtitle]['CANTIDAD'] = $arrArtitlesTemp[$indexArtitle]['CANTIDAD'] + 1;
                } else {
                    $index = $index + 1;
                    $arrArtitlesTemp[$index] = $artitles;
                    $arrArtitlesTemp[$index]['CANTIDAD'] = 1;
                }
            }
        }
        $arrArtitles = $arrArtitlesTemp;

        foreach ($arrArtitles as $keyArtitles => $artitles) {
            $arrImageArtitle = json_decode($artitles['IMAGEN']);
            $arrUrlImage = array();
            foreach ($arrImageArtitle as $key => $imagen) {
                $responseDocument = $this->documentManagementController->privateGetPublicDocumentURL($imagen, $user, $line);
                if ($responseDocument['error']) {
                    return $this->responseController->makeResponse(true, "ERR_ARTITLE_GET003: Ocurrió un error al obtener la URL de la imágen.", [], 500);
                }
                $arrUrlImage[] = $responseDocument['response']['public_uri'];
            }
            $arrArtitles[$keyArtitles]['IMAGEN'] = $arrUrlImage;
        }

        try {
            $getHistoryOrder = DB::table('S002V01THIOR')
                ->where('HIOR_NUOR', '=', $order)
                ->where('HIOR_NULI', '=', $line)
                ->get([
                    'HIOR_IDHO AS ID_HISTORIA_ORDEN',
                    'HIOR_ESOR AS ESTADO_ORDEN',
                    'HIOR_DESC AS DESCRIPCION',
                    'HIOR_EVID AS EVIDENCIA',
                    'HIOR_POEN AS PORCENTAJE_ENTREGA',
                    'HIOR_USRE AS USUARIO_REGISTRA',
                    'HIOR_FERE AS FECHA_REGISTRA',
                    'HIOR_USMO AS USUARIO_MODIFICA',
                    'HIOR_FEMO AS FECHA_MODIFICA',
                ]);
            $arrHistoryOrder = json_decode(json_encode($getHistoryOrder), true);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse( 
                true, 
                "ERR_ORDER_GET006: Ocurrió un error al momento de obtener el historial de la orden de compra.", 
                $th->getMessage(), 
                500
            );
        }
        if (empty($arrHistoryOrder) || is_null($arrHistoryOrder)) {
            return $this->responseController->makeResponse( true, "ERR_ORDER_GET007: No se pudo obtener el historial de la orden de compra.", [], 500);
        }

        $responseCheckLatestUpdate = $this->resourcesController->checkLatestUpdate($arrHistoryOrder, $line);
        if ($responseCheckLatestUpdate['error']) {
            return $this->responseController->makeResponse(true, $responseCheckLatestUpdate['msg'], [], 500);
        }
        $arrHistoryOrder = $responseCheckLatestUpdate['response'];


        foreach ($arrHistoryOrder as $keyHistoryOrder => $historyOrder) {
            if ( !is_null($historyOrder['EVIDENCIA']) ) {
                $arrEvidence = json_decode($historyOrder['EVIDENCIA']);
                $arrUrlImage = array();
                foreach ($arrEvidence as $key => $imagen) {
                    $responseDocument = $this->documentManagementController->privateGetPublicDocumentURL($imagen, $user, $line);
                    if ($responseDocument['error']) {
                        return $this->responseController->makeResponse(true, "ERR_ARTITLE_GET003: Ocurrió un error al obtener la URL de la imágen.", [], 500);
                    }
                    $arrUrlImage[] = $responseDocument['response']['public_uri'];
                }
                $historyOrder['EVIDENCIA'] = $arrUrlImage;
            }
            $arrHistoryOrder[$keyHistoryOrder] = $historyOrder;
        }

        $arrOrders['ARTI'] = $arrArtitles;
        $arrOrders['HIST'] = $arrHistoryOrder;

        return $this->responseController->makeResponse(false, "ÉXITO: Consulta Exitosa", $arrOrders);
    }
    public function getLastSateOrder($order, $user, $line) {

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

        try {
            $arrOrderState = (array) DB::table('S002V01THIOR')
                ->where('HIOR_NUOR', '=', $order)
                ->where('HIOR_NULI', '=', $line)
                ->where('HIOR_ESTA', '=', 'Activo')
                ->orderBy('HIOR_FEAR', 'DESC')
                ->first([
                    'HIOR_IDHO AS ID_HISTORIA_ORDEN',
                    'HIOR_ESOR AS ESTADO_ORDEN',
                    'HIOR_DESC AS DESCRIPCION',
                    'HIOR_EVID AS EVIDENCIA',
                    'HIOR_POEN AS PORCENTAJE_ENTREGA',
                    'HIOR_ESTA AS ESTADO_ORDEN',
                    'HIOR_USRE AS USUARIO_REGISTRA',
                    'HIOR_FERE AS FECHA_REGISTRA',
                    'HIOR_USMO AS USUARIO_MODIFICA',
                    'HIOR_FEMO AS FECHA_MODIFICA',
                ]);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_ORDER_GETHISTORY000: Ocurrió un error al obtener el historial de las órdenes de compra", 
                $th->getMessage(), 
                500
            );
        }

        $responseCheckLatestUpdate = $this->resourcesController->checkLatestUpdate([$arrOrderState], $line);
        if ($responseCheckLatestUpdate['error']) {
            return $this->responseController->makeResponse(true, $responseCheckLatestUpdate['msg'], [], 500);
        }
        $arrOrderState = $responseCheckLatestUpdate['response'];

        return $this->responseController->makeResponse(false, "ÉXITO: Consulta Exitosa", $arrOrderState);
    }
    public function updateStateHistory(Request $request) {
        $validator = Validator::make($request->all(), [
            'ESTADO' => 'required|string',
            'DESCRIPCION' => 'required|string',
            'EVIDENCIA' => 'required|array',
            'PORCENTAJE' => 'required|string',
            'NUMERO_ORDEN' => 'required|string',
            'USUARIO' => 'required|string',
            'NUMERO_LINEA' => 'required|integer',
        ]);

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

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

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

        $arrCodeEvidence = array();
        foreach ($requestData['EVIDENCIA'] as $evidence) {
            $idFile = $this->encController->decrypt($evidence);
            $tempFile = DB::table('S002V01TARTE')->where([
                ['ARTE_NULI', '=', $requestData['NUMERO_LINEA']],
                ['ARTE_IDAR', '=', $idFile],
            ])->first();

            if(is_null($tempFile)){
                return $this->responseController->makeResponse(true, 'ERR_ORDERSTATE_REG002: El archivo consultado no está registrado', [], 404);
            }else if($tempFile->ARTE_ESTA == 'Eliminado'){
                return $this->responseController->makeResponse(true, 'ERR_ORDERSTATE_REG003: El archivo consultado está eliminado', [], 404);
            }
            $fileResponse = $this->documentManagementController->moveFinalFile(
                intval($requestData['NUMERO_LINEA']),
                'GEAD',
                'FO',
                $tempFile,
                $user,
            );
            if(!$fileResponse[0]){
                return $this->responseController->makeResponse(true, 'ERR_ORDERSTATE_REG004: '.$fileResponse[1], [], 400);
            }
            $arrCodeEvidence[] = $this->encController->encrypt($fileResponse[1]);
        }
        $jsonEvidence = json_encode($arrCodeEvidence);

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

        $arrInsert = [
            'HIOR_NUOR' => $requestData['NUMERO_ORDEN'],
            'HIOR_ESOR' => $requestData['ESTADO'],
            'HIOR_DESC' => $requestData['DESCRIPCION'],
            'HIOR_EVID' => $jsonEvidence,
            'HIOR_POEN' => $requestData['PORCENTAJE'],
            'HIOR_NULI' => $requestData['NUMERO_LINEA'],
            'HIOR_USRE' => $user,
            'HIOR_FERE' => $currentDate,
            'HIOR_FEAR' => DB::raw('CURRENT_TIMESTAMP'),
        ];

        try {
            $validateInsert = DB::table('S002V01THIOR')->insert($arrInsert);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_ORDERSTATE_REG005: Ocurrió un error al momento de insertar los datos.", $th->getMessage(), 500);
        }

        if ( !$validateInsert ) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_ORDERSTATE_REG006: No se pudo insertar los datos a la tabla.", [], 500);
        }

        try {
            $validateUpdate = DB::table('S002V01TORCO')
                ->where('ORCO_NUOR', '=', $requestData['NUMERO_ORDEN'])
                ->where('ORCO_NULI', '=', $requestData['NUMERO_LINEA'])
                ->update([
                    'ORCO_ESTA' => $requestData['ESTADO'],
                    'ORCO_USMO' => $user,
                    'ORCO_FEMO' => $currentDate,
                ]);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_ORDERSTATE_REG007: Ocurrió un error al momento de modificar los datos.", $th->getMessage(), 500);
        }

        if ( !$validateUpdate ) {
            DB::rollBack();
            return $this->responseController->makeRespÑonse(true, "ERR_ORDERSTATE_REG008: No se pudo modificar los datos a la tabla.", [], 500);
        }

        DB::commit();
        return $this->responseController->makeResponse(false, "ÉXITO: Modificación de Estado Exitoso");
    }
    public function reorderArtitles(Request $request) {
        $validator = Validator::make($request->all(), [
            'ORDEN' => 'required|string',
            'DESCRIPCION' => 'required|string',
            'USUARIO' => 'required|string',
            'NUMERO_LINEA' => 'required|integer',
        ]);

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

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

        try {
            $user = $this->encController->decrypt($requestData['USUARIO']);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_REORDER_REG001: Ocurrió un error al obtener el usuario.", $th->getMessage(), 500);
        }
        try {
            $order = $this->encController->decrypt($requestData['ORDEN']);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_REORDER_REG002: Ocurrió un error al obtener el número de orden.", $th->getMessage(), 500);
        }

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

        try {
            $idRegisterLine = DB::table('S002V01TLINE')->insertGetId([
                // 'LINE_DESC' => $request['DESCRIPCION'],
                'LINE_NULI' => $request['NUMERO_LINEA'],
                'LINE_USRE' => $user,
                'LINE_FERE' => $currentDate,
                'LINE_FEAR' => DB::raw('CURRENT_TIMESTAMP')
            ]);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_REORDER_REG003: Ocurrió un error al hacer la inserción en la base.", $th->getMessage(), 500);
        }

        if ( !$idRegisterLine ) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_REORDER_REG004: Ocurrió un error al hacer la inserción en la base.", [], 500);
        }

        try {
            $arrArtitles = DB::table('S002V01TORCO')
                ->where('ORCO_NUOR', '=', $order)
                ->join('S002V01TARSE', function (JoinClause $join) {
                    $join->on('ARSE_IDLI', '=', 'ORCO_IDLI')
                    ->on('ARSE_NUPR', '=', 'ORCO_NUPR');
                })
                ->get([
                    'ARSE_IDAR', // Identificador del artículo
                    'ARSE_NUPR', // Número del proveedor
                    'ARSE_IDIN', // Identificador de la información del artículo
                    'ARSE_CANT', // Cantidad
                    // 'ARSE_PRTO', // Precio total
                ]);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_REORDER_REG005: No se pudo obtener la información de la orden.", $th->getMessage(), 500);
        }

        if ( $arrArtitles->isEmpty() ) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_REORDER_REG006: La orden no tiene artículos.", [], 500);
        }

        foreach ($arrArtitles as $keyArtitle => $artitle) {
            try {
                $validateInsert = DB::table('S002V01TARSE')->insert([
                    'ARSE_IDAS' => $keyArtitle + 1,
                    'ARSE_IDLI' => $idRegisterLine,
                    'ARSE_IDAR' => $artitle->ARSE_IDAR,
                    'ARSE_NUPR' => $artitle->ARSE_NUPR,
                    'ARSE_IDIN' => $artitle->ARSE_IDIN,
                    'ARSE_CANT' => $artitle->ARSE_CANT,
                    // 'ARSE_PRTO' => $artitle->ARSE_PRTO,
                    'ARSE_NULI' => $request['NUMERO_LINEA'],
                    'ARSE_USRE' => $user,
                    'ARSE_FERE' => $currentDate,
                    'ARSE_FEAR' => DB::raw('CURRENT_TIMESTAMP')
                ]);
            } catch (\Throwable $th) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_REORDER_REG007: Ocurrió un error al hacer la inserción en la base.", $th->getMessage(), 500);
            }
            if ( !$validateInsert ) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_REORDER_REG008: Ocurrió un error al hacer la inserción en la base.", [], 500);
            }
        }

        DB::commit();
        return $this->responseController->makeResponse(false, "ÉXITO: Artículos Reordenados Exitosamente");
    }

    public function generateSheetAcquisitionXLS($encOrderNumber, $user, $line) {
        
        try {
            $idOrder = $this->encController->decrypt($encOrderNumber);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(
                true, 
                "ERR_ORDER_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_ORDER_SHEET001: Ocurrió un error al obtener el usuario.", 
                $th->getMessage(), 
                500
            );
        }

        try {
            $arrOrder = (array) DB::table('S002V01TORCO')
                ->where('ORCO_NUOR', '=', $idOrder)
                ->where('ORCO_NULI', '=', $line)
                ->where('LINE_NULI', '=', $line)
                ->where('DESP_NULI', '=', $line)
                ->where('PROV_NULI', '=', $line)
                ->where('DESP_ESTA', '=', 'Activo')
                ->where('PROV_ESTA', '=', 'Activo')
                ->join('S002V01TLINE', 'LINE_IDLI', '=', 'ORCO_IDLI')
                ->join('S002V01TDESP', 'DESP_IDDE', '=', 'ORCO_IDDE')
                ->join('S002V01TPROV', 'PROV_NUPR', '=', 'LINE_NUPR')
                ->join('S002V01TREFI', 'REFI_CRFI', '=', 'PROV_CRFI')
                ->first([
                    'ORCO_NUOR',
                    'ORCO_ESTA',
                    'LINE_IDLI',
                    'LINE_OTPR',
                    'LINE_OTCO',
                    'LINE_ESTA',
                    'DESP_IDDE',
                    'DESP_NODE',
                    'DESP_NOMB',
                    'DESP_APPA',
                    'DESP_APMA',
                    'DESP_LADA',
                    'DESP_TELE',
                    'DESP_CORR',
                    'DESP_CALL',
                    'DESP_NUEX',
                    'DESP_NUIN',
                    'DESP_COPO',
                    'DESP_COLO',
                    'DESP_LOCA',
                    'DESP_MUNI',
                    'DESP_ENTI',
                    'DESP_PAIS',
                    'DESP_COME',
                    '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',
                    'REFI_CRFI',
                    'REFI_DRFI',
                ]);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_ORDER_SHEET002: Ocurrió un error al obtener la información de la orden de compra.", 
                $th->getMessage(), 
                500
            );
        }


        try {
            $arrArtitle = DB::table('S002V01TARSE')
                ->where('ARSE_NULI', '=', $line)
                ->where('INAR_NULI', '=', $line)
                ->where('CAMO_NULI', '=', $line)
                ->where('DEAR_NULI', '=', $line)
                ->where('UNID_NULI', '=', $line)
                ->where('ARTI_NULI', '=', $line)
                ->where('FAMI_NULI', '=', $line)
                ->where('SUBF_NULI', '=', $line)
                ->where('ARSE_ESTA', '=', 'Activo')
                ->where('INAR_ESTA', '=', 'Activo')
                ->where('CAMO_ESTA', '=', 'Activo')
                ->where('DEAR_ESTA', '=', 'Activo')
                ->where('UNID_ESTA', '=', 'Activo')
                ->where('ARTI_ESTA', '=', 'Activo')
                ->where('FAMI_ESTA', '=', 'Activo')
                ->where('SUBF_ESTA', '=', 'Activo')
                ->where('ARSE_IDLI', '=', $arrOrder['LINE_IDLI'])
                ->where('ARSE_NUPR', '=', $arrOrder['PROV_NUPR'])
                ->join('S002V01TINAR','INAR_IDIN','=','ARSE_IDIN')
                ->join('S002V01TCAMO','CAMO_COMO','=','INAR_COMO')
                ->join('S002V01TDEAR','DEAR_IDDE','=','INAR_IDDE')
                ->join('S002V01TUNID','UNID_IDUN','=','DEAR_IDUN')
                ->join('S002V01TARTI','ARTI_IDAR','=','DEAR_IDAR')
                ->join('S002V01TFAMI','FAMI_COFA','=','ARTI_COFA')
                ->join('S002V01TSUBF','SUBF_COSU','=','ARTI_COSU')
                ->get([
                    'ARSE_IDAS',
                    'ARSE_IDLI',
                    'INAR_IDIN',
                    'INAR_CODI',
                    'INAR_MODE',
                    'INAR_PREC',
                    'INAR_MOMI',
                    'INAR_CARA',
                    'CAMO_COMO',
                    'CAMO_DESC',
                    // 'CAMO_DECI',
                    // 'CAMO_POVA',
                    // 'CAMO_FEIN',
                    // 'CAMO_FEFI',
                    'DEAR_IDDE',
                    'DEAR_IMAG',
                    'DEAR_DESC',
                    'DEAR_CARA',
                    'DEAR_COWE',
                    'UNID_IDUN',
                    'UNID_NOMB',
                    'UNID_ACRO',
                    'ARTI_IDAR',
                    'ARTI_CODI',
                    'ARTI_NOMB',
                    'FAMI_COFA',
                    'FAMI_NOFA',
                    'SUBF_COSU',
                    'SUBF_NOSU',
                ]);
            $arrArtitle = json_decode( json_encode( $arrArtitle ), true );
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_ORDER_SHEET002: Ocurrió un error al obtener la información de la orden de compra.", 
                $th->getMessage(), 
                500
            );
        }

        $index = 0;
        $arrArtitlesTemp = array();
        foreach ($arrArtitle as $keyArtitle => $artitles) {
            if ($keyArtitle === 0) {
                $arrArtitlesTemp[$index] = $artitles;
                $arrArtitlesTemp[$index]['CANTIDAD'] = 1;
            } else {
                if (
                    in_array($artitles['INAR_IDIN'], array_column($arrArtitlesTemp, 'INAR_IDIN'))
                ) {
                    $indexArtitle = array_search($artitles['INAR_IDIN'], array_column($arrArtitlesTemp, 'INAR_IDIN'));
                    $arrArtitlesTemp[$indexArtitle]['CANTIDAD'] = $arrArtitlesTemp[$indexArtitle]['CANTIDAD'] + 1;
                } else {
                    $index = $index + 1;
                    $arrArtitlesTemp[$index] = $artitles;
                    $arrArtitlesTemp[$index]['CANTIDAD'] = 1;
                }
            }
        }
        $arrArtitle = $arrArtitlesTemp;
        
        $spreadsheet = new Spreadsheet();
        $activeWorksheet = $spreadsheet->getActiveSheet()->setTitle($arrOrder['ORCO_NUOR']);


        $headerA1 = 'Número de orden';
        $contentB1 = $arrOrder['ORCO_NUOR'];
        $activeWorksheet->setCellValue("A1", $headerA1);
        $activeWorksheet->setCellValue("B1", $contentB1);
        $activeWorksheet->getStyle( "A1" )->getFont()->setBold(true);


        $headerA2 = 'Estado de la orden';
        $contentB2 = $arrOrder['ORCO_ESTA'];
        $activeWorksheet->setCellValue("A2", $headerA2);
        $activeWorksheet->setCellValue("B2", $contentB2);
        $activeWorksheet->getStyle( "A2" )->getFont()->setBold(true);


        $headerA3 = 'Numero de línea de solicitud';
        $contentB3 = $arrOrder['LINE_IDLI'];
        $activeWorksheet->setCellValue("A3", $headerA3);
        $activeWorksheet->setCellValue("B3", $contentB3);
        $activeWorksheet->getStyle( "A3" )->getFont()->setBold(true);


        $headerA4 = '';
        if ( !is_null( $arrOrder['LINE_OTPR'] ) && is_null( $arrOrder['LINE_OTCO'] )  ) {
            $headerA4 = 'Orden de Trabajo Preventivo';
            $contentB4 = $arrOrder['LINE_OTPR'];
        } else if ( is_null( $arrOrder['LINE_OTPR'] ) && !is_null( $arrOrder['LINE_OTCO'] )  ) {
            $headerA4 = 'Orden de Trabajo Correctivo';
            $contentB4 = $arrOrder['LINE_OTCO'];
        } else if ( is_null( $arrOrder['LINE_OTPR'] ) && is_null( $arrOrder['LINE_OTCO'] )  ) {
            $headerA4 = 'Orden de Trabajo';
            $contentB4 = 'No aplica';
        } else {
            $headerA4 = 'Orden de Trabajo';
            $contentB4 = 'No aplica';
        }
        $activeWorksheet->setCellValue("A4", $headerA4 );
        $activeWorksheet->setCellValue("B4", $contentB4 );
        $activeWorksheet->getStyle( "A4" )->getFont()->setBold(true);


        $headerA5 = 'ID Despacho';
        $contentB5 = $arrOrder['DESP_IDDE'];
        $activeWorksheet->setCellValue("A5", $headerA5 );
        $activeWorksheet->setCellValue("B5", $contentB5 );
        $activeWorksheet->getStyle( "A5" )->getFont()->setBold(true);


        $headerA6 = 'Despacho';
        $contentB6 = $arrOrder['DESP_NODE'];
        $activeWorksheet->setCellValue("A6", $headerA6 );
        $activeWorksheet->setCellValue("B6", $contentB6 );
        $activeWorksheet->getStyle( "A6" )->getFont()->setBold(true);


        $headerA7 = 'Responsable del despacho';
        $contentB7 = $arrOrder['DESP_NOMB'] . ' ' . $arrOrder['DESP_APPA'] . ' ' . $arrOrder['DESP_APMA'];
        $activeWorksheet->setCellValue("A7", $headerA7 );
        $activeWorksheet->setCellValue("B7", $contentB7 );
        $activeWorksheet->getStyle( "A7" )->getFont()->setBold(true);


        $headerA8 = 'Teléfono del despacho';
        $contentB8 = $arrOrder['DESP_LADA'] . ' ' . $arrOrder['DESP_TELE'];
        $activeWorksheet->setCellValue("A8", $headerA8 );
        $activeWorksheet->setCellValue("B8", $contentB8 );
        $activeWorksheet->getStyle( "A8" )->getFont()->setBold(true);


        $headerA9 = 'Correo electrónico del despacho';
        $contentB9 = $arrOrder['DESP_CORR'];
        $activeWorksheet->setCellValue("A9", $headerA9 );
        $activeWorksheet->setCellValue("B9", $contentB9 );
        $activeWorksheet->getStyle( "A9" )->getFont()->setBold(true);


        $headerA10 = 'Calle del despacho';
        $contentB10 = $arrOrder['DESP_CALL'];
        $activeWorksheet->setCellValue("A10", $headerA10 );
        $activeWorksheet->setCellValue("B10", $contentB10 );
        $activeWorksheet->getStyle( "A10" )->getFont()->setBold(true);

        
        $headerA11 = 'Número exterior';
        $contentB11 = $arrOrder['DESP_NUEX'];
        $activeWorksheet->setCellValue("A11", $headerA11 );
        $activeWorksheet->setCellValue("B11", $contentB11 );
        $activeWorksheet->getStyle( "A11" )->getFont()->setBold(true);


        $headerA12 = 'Número interior';
        if ( !is_null($arrOrder['DESP_NUIN']) ) {
            $contentB12 = $arrOrder['DESP_NUIN'];
        } else {
            $contentB12 = 'No aplica';
        }
        $activeWorksheet->setCellValue("A12", $headerA12 );
        $activeWorksheet->setCellValue("B12", $contentB12 );
        $activeWorksheet->getStyle( "A12" )->getFont()->setBold(true);


        $headerA13 = 'Código postal';
        $contentB13 = $arrOrder['DESP_COPO'];
        $activeWorksheet->setCellValue("A13", $headerA13 );
        $activeWorksheet->setCellValue("B13", $contentB13 );
        $activeWorksheet->getStyle( "A13" )->getFont()->setBold(true);

        
        $headerA14 = 'Colonia';
        $contentB14 = $arrOrder['DESP_COLO'];
        $activeWorksheet->setCellValue("A14", $headerA14 );
        $activeWorksheet->setCellValue("B14", $contentB14 );
        $activeWorksheet->getStyle( "A14" )->getFont()->setBold(true);


        $headerA15 = 'Localidad';
        if ( !is_null($arrOrder['DESP_LOCA']) && !empty($arrOrder['DESP_LOCA']) ) {
            $contentB15 = $arrOrder['DESP_LOCA'];
        } else {
            $contentB15 = 'No aplica';
        }
        $activeWorksheet->setCellValue("A15", $headerA15 );
        $activeWorksheet->setCellValue("B15", $contentB15 );
        $activeWorksheet->getStyle( "A15" )->getFont()->setBold(true);


        $headerA16 = 'Municipio';
        $contentB16 = $arrOrder['DESP_MUNI'];
        $activeWorksheet->setCellValue("A16", $headerA16 );
        $activeWorksheet->setCellValue("B16", $contentB16 );
        $activeWorksheet->getStyle( "A16" )->getFont()->setBold(true);


        $headerA17 = 'Entidad Federativa';
        $contentB17 = $arrOrder['DESP_ENTI'];
        $activeWorksheet->setCellValue("A17", $headerA17 );
        $activeWorksheet->setCellValue("B17", $contentB17 );
        $activeWorksheet->getStyle( "A17" )->getFont()->setBold(true);


        $headerA18 = 'País';
        $contentB18 = $arrOrder['DESP_PAIS'];
        $activeWorksheet->setCellValue("A18", $headerA18 );
        $activeWorksheet->setCellValue("B18", $contentB18 );
        $activeWorksheet->getStyle( "A18" )->getFont()->setBold(true);


        $headerA19 = 'Número de proveedor';
        $contentB19 = $arrOrder['PROV_NUPR'];
        $activeWorksheet->setCellValue("A19", $headerA19 );
        $activeWorksheet->setCellValue("B19", $contentB19 );
        $activeWorksheet->getStyle( "A19" )->getFont()->setBold(true);


        $headerA20 = 'Número de subcontratista';
        $contentB20 = $arrOrder['PROV_IDPS'];
        $activeWorksheet->setCellValue("A20", $headerA20 );
        $activeWorksheet->setCellValue("B20", $contentB20 );
        $activeWorksheet->getStyle( "A20" )->getFont()->setBold(true);


        $headerA21 = 'Nombre comercial del proveedor';
        $contentB21 = $arrOrder['PROV_NOCO'];
        $activeWorksheet->setCellValue("A21", $headerA21 );
        $activeWorksheet->setCellValue("B21", $contentB21 );
        $activeWorksheet->getStyle( "A21" )->getFont()->setBold(true);


        $headerA22 = 'Referencia fiscal del proveedor';
        $contentB22 = $arrOrder['REFI_DRFI'] . ' (' . $arrOrder['REFI_CRFI'] . ')';
        $activeWorksheet->setCellValue("A22", $headerA22 );
        $activeWorksheet->setCellValue("B22", $contentB22 );
        $activeWorksheet->getStyle( "A22" )->getFont()->setBold(true);


        $headerA23 = 'Responsable del proveedor';
        $contentB23 = $arrOrder['PROV_NOMB'] . ' ' . $arrOrder['PROV_APPA'] . ' ' . $arrOrder['PROV_APMA'];
        $activeWorksheet->setCellValue("A23", $headerA23 );
        $activeWorksheet->setCellValue("B23", $contentB23 );
        $activeWorksheet->getStyle( "A23" )->getFont()->setBold(true);


        $headerA24 = 'Correo electrónico del proveedor';
        $contentB24 = $arrOrder['PROV_CORR'];
        $activeWorksheet->setCellValue("A24", $headerA24 );
        $activeWorksheet->setCellValue("B24", $contentB24 );
        $activeWorksheet->getStyle( "A24" )->getFont()->setBold(true);


        $headerA25 = 'Teléfono del proveedor';
        $contentB25 = $arrOrder['PROV_LAD1'] . ' ' . $arrOrder['PROV_TEL1'];
        $activeWorksheet->setCellValue("A25", $headerA25 );
        $activeWorksheet->setCellValue("B25", $contentB25 );
        $activeWorksheet->getStyle( "A25" )->getFont()->setBold(true);


        $headerA26 = '';
        $contentB26 = '';
        if ( !is_null( $arrOrder['PROV_XRFC'] ) && is_null( $arrOrder['PROV_XRFC'] )) {
            $headerA26 = 'R.F.C. del proveedor';
            $contentB26 = $arrOrder['PROV_XRFC'];
        } else if ( is_null( $arrOrder['PROV_XRFC'] ) && !is_null( $arrOrder['PROV_XRFC'] )) {
            $headerA26 = 'TAX ID. del proveedor';
            $contentB26 = $arrOrder['PROV_XTAX'];
        } else {
            $headerA26 = 'R.F.C. / TAX ID';
            $contentB26 = 'No aplica';
        }
        $activeWorksheet->setCellValue("A26", $headerA26 );
        $activeWorksheet->setCellValue("B26", $contentB26 );
        $activeWorksheet->getStyle( "A26" )->getFont()->setBold(true);

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


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


        $spreadsheet->createSheet();
        $spreadsheet->setActiveSheetIndex(1);
        $activeWorksheetArtitle = $spreadsheet->getActiveSheet()->setTitle('Artículos de la orden');
        $arrHeaderArtitle = array(
            /* A */ 'ID Artículo',
            /* B */ 'Código del Artíulo',
            /* C */ 'Artículo',
            /* D */ 'Familia',
            /* E */ 'Subfamilia',
            /* F */ 'Descripción',
            /* G */ 'Características',
            /* H */ 'Unidad',
            /* I */ 'Código',
            /* J */ 'Modelo',
            /* L */ 'Precio Unitario',
            /* M */ 'Cantidad',
            /* N */ 'Precio Total',
            /* O */ 'No. de selección',
        );
        foreach ($arrArtitle as $key => $artitle) {
            $key = $key + 2;

            $activeWorksheetArtitle->setCellValue("A".$key, $artitle['ARTI_IDAR']);
            $activeWorksheetArtitle->setCellValue("B".$key, $artitle['ARTI_CODI']);
            $activeWorksheetArtitle->setCellValue("C".$key, $artitle['ARTI_NOMB']);
            $activeWorksheetArtitle->setCellValue("D".$key, $artitle['FAMI_NOFA'] . ' (' . $artitle['FAMI_COFA'] . ')');
            $activeWorksheetArtitle->setCellValue("E".$key, $artitle['SUBF_NOSU'] . ' (' . $artitle['SUBF_COSU'] . ')');
            $activeWorksheetArtitle->setCellValue("F".$key, $artitle['DEAR_DESC']);
            $activeWorksheetArtitle->setCellValue("G".$key, $artitle['DEAR_CARA']);
            $activeWorksheetArtitle->setCellValue("H".$key, $artitle['UNID_NOMB'] . ' (' . $artitle['UNID_IDUN'] . ')');
            $activeWorksheetArtitle->setCellValue("I".$key, $artitle['INAR_CODI']);
            $activeWorksheetArtitle->setCellValue("J".$key, $artitle['INAR_MODE']);
            $activeWorksheetArtitle->setCellValue("K".$key, '$'. $artitle['INAR_PREC'] . ' ' . $artitle['CAMO_COMO'] );
            $activeWorksheetArtitle->setCellValue("L".$key, $artitle['CANTIDAD']);
            $activeWorksheetArtitle->setCellValue("M".$key, '$'.( $artitle['INAR_PREC'] * $artitle['CANTIDAD']) . ' ' . $artitle['CAMO_COMO'] );
            $activeWorksheetArtitle->setCellValue("N".$key, $artitle['ARSE_IDAS']);
        }
        foreach ($arrHeaderArtitle as $keyHeaderArtitle => $headerArtitle) {
            $activeWorksheetArtitle->setCellValue( $arrAlphabet[$keyHeaderArtitle]."1", $headerArtitle);
            $activeWorksheetArtitle->getColumnDimension( $arrAlphabet[$keyHeaderArtitle] )->setAutoSize(true);
            $activeWorksheetArtitle->getStyle( $arrAlphabet[$keyHeaderArtitle]."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_adquisición_' . $arrOrder['ORCO_NUOR'];
        $exte = 'xlsx';

        if ($_SERVER['SERVER_NAME'] === '192.168.100.105') {
            $filePath = $this->resourcesController->pathService.'\\public\\public_files\\';    // API JEAN
        } else {
            $filePath = $this->resourcesController->pathService.'\\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 generateSheetAcquisitionPDF($encOrderNumber, $user, $line) {
        try {
            $idOrder = $this->encController->decrypt($encOrderNumber);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(
                true, 
                "ERR_ORDER_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_ORDER_SHEET001: Ocurrió un error al obtener el usuario.", 
                $th->getMessage(), 
                500
            );
        }

        try {
            $arrOrder = (array) DB::table('S002V01TORCO')
                ->where('ORCO_NUOR', '=', $idOrder)
                ->where('ORCO_NULI', '=', $line)
                ->where('LINE_NULI', '=', $line)
                ->where('DESP_NULI', '=', $line)
                ->where('PROV_NULI', '=', $line)
                ->where('DESP_ESTA', '=', 'Activo')
                ->where('PROV_ESTA', '=', 'Activo')
                ->join('S002V01TLINE', 'LINE_IDLI', '=', 'ORCO_IDLI')
                ->join('S002V01TDESP', 'DESP_IDDE', '=', 'ORCO_IDDE')
                ->join('S002V01TPROV', 'PROV_NUPR', '=', 'LINE_NUPR')
                ->join('S002V01TREFI', 'REFI_CRFI', '=', 'PROV_CRFI')
                ->first([
                    'ORCO_NUOR',
                    'ORCO_ESTA',
                    'LINE_IDLI',
                    'LINE_OTPR',
                    'LINE_OTCO',
                    'LINE_ESTA',
                    'DESP_IDDE',
                    'DESP_NODE',
                    'DESP_NOMB',
                    'DESP_APPA',
                    'DESP_APMA',
                    'DESP_LADA',
                    'DESP_TELE',
                    'DESP_CORR',
                    'DESP_CALL',
                    'DESP_NUEX',
                    'DESP_NUIN',
                    'DESP_COPO',
                    'DESP_COLO',
                    'DESP_LOCA',
                    'DESP_MUNI',
                    'DESP_ENTI',
                    'DESP_PAIS',
                    'DESP_COME',
                    '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',
                    'REFI_CRFI',
                    'REFI_DRFI',
                ]);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_ORDER_SHEET002: Ocurrió un error al obtener la información de la orden de compra.", 
                $th->getMessage(), 
                500
            );
        }


        try {
            $arrArtitle = DB::table('S002V01TARSE')
                ->where('ARSE_NULI', '=', $line)
                ->where('INAR_NULI', '=', $line)
                ->where('CAMO_NULI', '=', $line)
                ->where('DEAR_NULI', '=', $line)
                ->where('UNID_NULI', '=', $line)
                ->where('ARTI_NULI', '=', $line)
                ->where('FAMI_NULI', '=', $line)
                ->where('SUBF_NULI', '=', $line)
                ->where('ARSE_ESTA', '=', 'Activo')
                ->where('INAR_ESTA', '=', 'Activo')
                ->where('CAMO_ESTA', '=', 'Activo')
                ->where('DEAR_ESTA', '=', 'Activo')
                ->where('UNID_ESTA', '=', 'Activo')
                ->where('ARTI_ESTA', '=', 'Activo')
                ->where('FAMI_ESTA', '=', 'Activo')
                ->where('SUBF_ESTA', '=', 'Activo')
                ->where('ARSE_IDLI', '=', $arrOrder['LINE_IDLI'])
                ->where('ARSE_NUPR', '=', $arrOrder['PROV_NUPR'])
                ->join('S002V01TINAR','INAR_IDIN','=','ARSE_IDIN')
                ->join('S002V01TCAMO','CAMO_COMO','=','INAR_COMO')
                ->join('S002V01TDEAR','DEAR_IDDE','=','INAR_IDDE')
                ->join('S002V01TUNID','UNID_IDUN','=','DEAR_IDUN')
                ->join('S002V01TARTI','ARTI_IDAR','=','DEAR_IDAR')
                ->join('S002V01TFAMI','FAMI_COFA','=','ARTI_COFA')
                ->join('S002V01TSUBF','SUBF_COSU','=','ARTI_COSU')
                ->get([
                    'ARSE_IDAS',
                    'ARSE_IDLI',
                    // 'ARSE_CANT',
                    // 'ARSE_PRTO',
                    'INAR_IDIN',
                    'INAR_CODI',
                    'INAR_MODE',
                    'INAR_PREC',
                    'INAR_MOMI',
                    'INAR_CARA',
                    'CAMO_COMO',
                    'CAMO_DESC',
                    // 'CAMO_DECI',
                    // 'CAMO_POVA',
                    // 'CAMO_FEIN',
                    // 'CAMO_FEFI',
                    'DEAR_IDDE',
                    'DEAR_IMAG',
                    'DEAR_DESC',
                    'DEAR_CARA',
                    'DEAR_COWE',
                    'UNID_IDUN',
                    'UNID_NOMB',
                    'UNID_ACRO',
                    'ARTI_IDAR',
                    'ARTI_CODI',
                    'ARTI_NOMB',
                    'FAMI_COFA',
                    'FAMI_NOFA',
                    'SUBF_COSU',
                    'SUBF_NOSU',
                ]);
            $arrArtitle = json_decode( json_encode( $arrArtitle ), true );
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_ORDER_SHEET002: Ocurrió un error al obtener la información de la orden de compra.", 
                $th->getMessage(), 
                500
            );
        }

        $index = 0;
        $arrArtitlesTemp = array();
        foreach ($arrArtitle as $keyArtitle => $artitles) {
            if ($keyArtitle === 0) {
                $arrArtitlesTemp[$index] = $artitles;
                $arrArtitlesTemp[$index]['CANTIDAD'] = 1;
            } else {
                if (
                    in_array($artitles['INAR_IDIN'], array_column($arrArtitlesTemp, 'INAR_IDIN'))
                ) {
                    $indexArtitle = array_search($artitles['INAR_IDIN'], array_column($arrArtitlesTemp, 'INAR_IDIN'));
                    $arrArtitlesTemp[$indexArtitle]['CANTIDAD'] = $arrArtitlesTemp[$indexArtitle]['CANTIDAD'] + 1;
                } else {
                    $index = $index + 1;
                    $arrArtitlesTemp[$index] = $artitles;
                    $arrArtitlesTemp[$index]['CANTIDAD'] = 1;
                }
            }
        }
        $arrArtitle = $arrArtitlesTemp;

        $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%;
                    font-size: 8pt;
                }

                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 de Orden de Compra: </b>
                <span>'.$arrOrder['ORCO_NUOR'].'</span>
            </div>
            <div class="item">
                <b>Estado de la orden: </b>
                <span>'.$arrOrder['ORCO_ESTA'].'</span>
            </div>
            <div class="item">
                <b>Numero de línea de solicitud: </b>
                <span>'.$arrOrder['LINE_IDLI'].'</span>
            </div>
        ';

        if ( !is_null( $arrOrder['LINE_OTPR'] ) && is_null( $arrOrder['LINE_OTCO'] )  ) {
            $html.='<div class="item">
                        <b>Orden de Trabajo Preventivo: </b>
                        <span>'.$arrOrder['LINE_OTPR'].'</span>
                    </div>';
        } else if ( is_null( $arrOrder['LINE_OTPR'] ) && !is_null( $arrOrder['LINE_OTCO'] )  ) {
            $html.='<div class="item">
                        <b>Orden de Trabajo Correctivo: </b>
                        <span>'.$arrOrder['LINE_OTCO'].'</span>
                    </div>';
        } else if ( is_null( $arrOrder['LINE_OTPR'] ) && is_null( $arrOrder['LINE_OTCO'] )  ) {
            $html.='<div class="item">
                        <b>Orden de Trabajo: </b>
                        <span>No aplica</span>
                    </div>';
        } else {
            $html.='<div class="item">
                        <b>Orden de Trabajo: </b>
                        <span>No aplica</span>
                    </div>';
        }

        $html.='<div class="item">
                    <b>ID Despacho: </b>
                    <span>'.$arrOrder['DESP_IDDE'].'</span>
                </div>
                <div class="item">
                    <b>Despacho: </b>
                    <span>'.$arrOrder['DESP_NODE'].'</span>
                </div>
                <div class="item">
                    <b>Responsable del despacho: </b>
                    <span>'.$arrOrder['DESP_NOMB'] . ' ' . $arrOrder['DESP_APPA'] . ' ' . $arrOrder['DESP_APMA'].'</span>
                </div>
                <div class="item">
                    <b>Teléfono del despacho: </b>
                    <span>'.$arrOrder['DESP_LADA'] . ' ' . $arrOrder['DESP_TELE'].'</span>
                </div>
                <div class="item">
                    <b>Correo electrónico del despacho: </b>
                    <span>'.$arrOrder['DESP_CORR'].'</span>
                </div>
                <div class="item">
                    <b>Calle del despacho: </b>
                    <span>'.$arrOrder['DESP_CALL'].'</span>
                </div>
                <div class="item">
                    <b>Número exterior: </b>
                    <span>'.$arrOrder['DESP_NUEX'].'</span>
                </div>
        ';
        if ( !is_null($arrOrder['DESP_NUIN']) ) {
            $html.='<div class="item">
                        <b>Número interior: </b>
                        <span>'.$arrOrder['DESP_NUIN'].'</span>
                    </div>';
        } else {
            $html.='<div class="item">
                        <b>Número interior: </b>
                        <span>No aplica</span>
                    </div>';
        }

        $html.='<div class="item">
                    <b>Código postal: </b>
                    <span>'.$arrOrder['DESP_COPO'].'</span>
                </div>
                <div class="item">
                    <b>Colonia: </b>
                    <span>'.$arrOrder['DESP_COLO'].'</span>
                </div>
        ';


        if ( !is_null($arrOrder['DESP_LOCA']) && !empty($arrOrder['DESP_LOCA']) ) {
            $html.='<div class="item">
                        <b>Localidad: </b>
                        <span>'.$arrOrder['DESP_LOCA'].'</span>
                    </div>';
        } else {
            $html.='<div class="item">
                        <b>Localidad: </b>
                        <span>No aplica</span>
                    </div>';
        }

        $html.='<div class="item">
                    <b>Municipio: </b>
                    <span>'.$arrOrder['DESP_MUNI'].'</span>
                </div>
                <div class="item">
                    <b>Entidad Federativa: </b>
                    <span>'.$arrOrder['DESP_ENTI'].'</span>
                </div>
                <div class="item">
                    <b>País: </b>
                    <span>'.$arrOrder['DESP_PAIS'].'</span>
                </div>
                <div class="item">
                    <b>Número de proveedor: </b>
                    <span>'.$arrOrder['PROV_NUPR'].'</span>
                </div>
                <div class="item">
                    <b>Número de subcontratista: </b>
                    <span>'.$arrOrder['PROV_IDPS'].'</span>
                </div>
                <div class="item">
                    <b>Nombre comercial del proveedor: </b>
                    <span>'.$arrOrder['PROV_NOCO'].'</span>
                </div>
                <div class="item">
                    <b>Referencia fiscal del proveedor: </b>
                    <span>'.$arrOrder['REFI_DRFI'] . ' (' . $arrOrder['REFI_CRFI'] . ')'.'</span>
                </div>
                <div class="item">
                    <b>Responsable del proveedor: </b>
                    <span>'.$arrOrder['PROV_NOMB'] . ' ' . $arrOrder['PROV_APPA'] . ' ' . $arrOrder['PROV_APMA'].'</span>
                </div>
                <div class="item">
                    <b>Correo electrónico del proveedor: </b>
                    <span>'.$arrOrder['PROV_CORR'].'</span>
                </div>
                <div class="item">
                    <b>Teléfono del proveedor: </b>
                    <span>'.$arrOrder['PROV_LAD1'] . ' ' . $arrOrder['PROV_TEL1'].'</span>
                </div>
        ';

        if ( !is_null( $arrOrder['PROV_XRFC'] ) && is_null( $arrOrder['PROV_XRFC'] )) {
            $html.='<div class="item">
                        <b>R.F.C. del proveedor: </b>
                        <span>'.$arrOrder['PROV_XRFC'].'</span>
                    </div>';
        } else if ( is_null( $arrOrder['PROV_XRFC'] ) && !is_null( $arrOrder['PROV_XRFC'] )) {
            $html.='<div class="item">
                        <b>TAX ID. del proveedor: </b>
                        <span>'.$arrOrder['PROV_XTAX'].'</span>
                    </div>';            
        } else {
            $html.='<div class="item">
                        <b>R.F.C. / TAX ID: </b>
                        <span>No aplica</span>
                    </div>';
        }


        if ( !empty($arrArtitle) ) {
            $html.='
                    <div style="page-break-after:always;"></div>

                    <div class="section-title">
                        <b>Artículos</b>
                    </div>';
            $html.='<table cellspacing="0" cellpadding="0">
                        <thead>
                            <tr>
                                <th>ID Artículo</th>
                                <th>Código del artíulo</th>
                                <th>Artículo</th>
                                <th>Familia</th>
                                <th>Subfamilia</th>
                                <!-- <th>Descripción</th> -->
                                <!-- <th>Características</th> -->
                                <th>Unidad</th>
                                <th>Código</th>
                                <th>Modelo</th>
                                <th>Precio Unitario</th>
                                <th>Cantidad</th>
                                <th>Precio Total</th>
                                <th>No. de selección</th>
                            </tr>
                        </thead>
                        <tbody>';
            foreach ($arrArtitle as $keyArtitle => $artitle) {
                $html.=    '<tr>
                                <td>'.$artitle['ARTI_IDAR'].'</td>
                                <td>'.$artitle['ARTI_CODI'].'</td>
                                <td>'.$artitle['ARTI_NOMB'].'</td>
                                <td>'.$artitle['FAMI_NOFA'] . ' (' . $artitle['FAMI_COFA'] . ')'.'</td>
                                <td>'.$artitle['SUBF_NOSU'] . ' (' . $artitle['SUBF_COSU'] . ')'.'</td>
                                <!-- <td>'.$artitle['DEAR_DESC'].'</td> -->
                                <!-- <td>'.$artitle['DEAR_CARA'].'</td> -->
                                <td>'.$artitle['UNID_NOMB'] . ' (' . $artitle['UNID_IDUN'] . ')'.'</td>
                                <td>'.$artitle['INAR_CODI'].'</td>
                                <td>'.$artitle['INAR_MODE'].'</td>
                                <td>'.'$'. $artitle['INAR_PREC'] . ' ' . $artitle['CAMO_COMO'] .'</td>
                                <td>'.$artitle['CANTIDAD'].'</td>
                                <td>'.'$'. ($artitle['INAR_PREC'] * $artitle['CANTIDAD']) . ' ' . $artitle['CAMO_COMO'] .'</td>
                                <td>'.$artitle['ARSE_IDAS'].'</td>
                            </tr>';
            }
            $html.=    '</tbody>
                    </table>';
        }


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

        // return $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_adquisición_' . $arrOrder['ORCO_NUOR'];

        $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_adquisición_' . $arrOrder['ORCO_NUOR'];
        $exte = 'pdf';        
        
        
        if ($_SERVER['SERVER_NAME'] === '192.168.100.105') {
            $filePath = $this->resourcesController->pathService.'\\public\\public_files\\';    // API JEAN
        } else {
            $filePath = $this->resourcesController->pathService.'\\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', 'landscape');
        $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 = $this->resourcesController->pathService."\\storage\\app\\" . $ubic;
        } else {
            $ubic = $this->resourcesController->pathService."\\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 generateSheetDivisaXLS($encOrderNumber, $user, $line) {
        try {
            $idOrder = $this->encController->decrypt($encOrderNumber);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(
                true, 
                "ERR_ORDER_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_ORDER_SHEET001: Ocurrió un error al obtener el usuario.", 
                $th->getMessage(), 
                500
            );
        }

        try {
            $arrOrder = (array) DB::table('S002V01TORCO')
                ->where('ORCO_NUOR', '=', $idOrder)
                ->where('ORCO_NULI', '=', $line)
                ->where('LINE_NULI', '=', $line)
                ->where('DESP_NULI', '=', $line)
                ->where('PROV_NULI', '=', $line)
                ->where('DESP_ESTA', '=', 'Activo')
                ->where('PROV_ESTA', '=', 'Activo')
                ->join('S002V01TLINE', 'LINE_IDLI', '=', 'ORCO_IDLI')
                ->join('S002V01TDESP', 'DESP_IDDE', '=', 'ORCO_IDDE')
                ->join('S002V01TPROV', 'PROV_NUPR', '=', 'LINE_NUPR')
                ->join('S002V01TREFI', 'REFI_CRFI', '=', 'PROV_CRFI')
                ->first([
                    'ORCO_NUOR',
                    'ORCO_ESTA',
                    'LINE_IDLI',
                    'LINE_OTPR',
                    'LINE_OTCO',
                    'LINE_ESTA',
                    'DESP_IDDE',
                    'DESP_NODE',
                    'DESP_NOMB',
                    'DESP_APPA',
                    'DESP_APMA',
                    'DESP_LADA',
                    'DESP_TELE',
                    'DESP_CORR',
                    'DESP_CALL',
                    'DESP_NUEX',
                    'DESP_NUIN',
                    'DESP_COPO',
                    'DESP_COLO',
                    'DESP_LOCA',
                    'DESP_MUNI',
                    'DESP_ENTI',
                    'DESP_PAIS',
                    'DESP_COME',
                    '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',
                    'REFI_CRFI',
                    'REFI_DRFI',
                ]);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_ORDER_SHEET002: Ocurrió un error al obtener la información de la orden de compra.", 
                $th->getMessage(), 
                500
            );
        }

        try {
            $arrArtitle = DB::table('S002V01TARSE')
                ->where('ARSE_NULI', '=', $line)
                ->where('INAR_NULI', '=', $line)
                ->where('CAMO_NULI', '=', $line)
                ->where('DEAR_NULI', '=', $line)
                ->where('UNID_NULI', '=', $line)
                ->where('ARTI_NULI', '=', $line)
                ->where('FAMI_NULI', '=', $line)
                ->where('SUBF_NULI', '=', $line)
                ->where('ARSE_ESTA', '=', 'Activo')
                ->where('INAR_ESTA', '=', 'Activo')
                ->where('CAMO_ESTA', '=', 'Activo')
                ->where('DEAR_ESTA', '=', 'Activo')
                ->where('UNID_ESTA', '=', 'Activo')
                ->where('ARTI_ESTA', '=', 'Activo')
                ->where('FAMI_ESTA', '=', 'Activo')
                ->where('SUBF_ESTA', '=', 'Activo')
                ->where('ARSE_IDLI', '=', $arrOrder['LINE_IDLI'])
                ->where('ARSE_NUPR', '=', $arrOrder['PROV_NUPR'])
                ->join('S002V01TINAR','INAR_IDIN','=','ARSE_IDIN')
                ->join('S002V01TCAMO','CAMO_COMO','=','INAR_COMO')
                ->join('S002V01TDEAR','DEAR_IDDE','=','INAR_IDDE')
                ->join('S002V01TUNID','UNID_IDUN','=','DEAR_IDUN')
                ->join('S002V01TARTI','ARTI_IDAR','=','DEAR_IDAR')
                ->join('S002V01TFAMI','FAMI_COFA','=','ARTI_COFA')
                ->join('S002V01TSUBF','SUBF_COSU','=','ARTI_COSU')
                ->get([
                    'ARSE_IDAS',
                    'ARSE_IDLI',
                    // 'ARSE_CANT',
                    // 'ARSE_PRTO',
                    'INAR_IDIN',
                    'INAR_CODI',
                    'INAR_MODE',
                    'INAR_PREC',
                    'INAR_MOMI',
                    'INAR_CARA',
                    'CAMO_COMO',
                    'CAMO_DESC',
                    // 'CAMO_DECI',
                    // 'CAMO_POVA',
                    // 'CAMO_FEIN',
                    // 'CAMO_FEFI',
                    'DEAR_IDDE',
                    'DEAR_IMAG',
                    'DEAR_DESC',
                    'DEAR_CARA',
                    'DEAR_COWE',
                    'UNID_IDUN',
                    'UNID_NOMB',
                    'UNID_ACRO',
                    'ARTI_IDAR',
                    'ARTI_CODI',
                    'ARTI_NOMB',
                    'FAMI_COFA',
                    'FAMI_NOFA',
                    'SUBF_COSU',
                    'SUBF_NOSU',
                ]);
            $arrArtitle = json_decode( json_encode( $arrArtitle ), true );
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_ORDER_SHEET002: Ocurrió un error al obtener la información de la orden de compra.", 
                $th->getMessage(), 
                500
            );
        }

        $index = 0;
        $arrArtitlesTemp = array();
        foreach ($arrArtitle as $keyArtitle => $artitles) {
            if ($keyArtitle === 0) {
                $arrArtitlesTemp[$index] = $artitles;
                $arrArtitlesTemp[$index]['CANTIDAD'] = 1;
            } else {
                if (
                    in_array($artitles['INAR_IDIN'], array_column($arrArtitlesTemp, 'INAR_IDIN'))
                ) {
                    $indexArtitle = array_search($artitles['INAR_IDIN'], array_column($arrArtitlesTemp, 'INAR_IDIN'));
                    $arrArtitlesTemp[$indexArtitle]['CANTIDAD'] = $arrArtitlesTemp[$indexArtitle]['CANTIDAD'] + 1;
                } else {
                    $index = $index + 1;
                    $arrArtitlesTemp[$index] = $artitles;
                    $arrArtitlesTemp[$index]['CANTIDAD'] = 1;
                }
            }
        }
        $arrArtitle = $arrArtitlesTemp;

        try {
            $arrInvoice = DB::table('S002V01TFALS')
                ->where('FALS_IDLI', '=', $arrOrder['LINE_IDLI'])
                ->where('FALS_NULI', '=', $line)
                ->where('DLSC_NULI', '=', $line)
                ->where('FALS_ESTA', '=', 'Activo')
                ->where('DLSC_ESTA', '=', 'Activo')
                ->where('DLSC_TIPO', '=', 'Cotización')
                ->orWhere('DLSC_TIPO', '=', 'Orden')
                ->join('S002V01TDLSC', 'DLSC_IDFA', '=', 'FALS_IDFA')
                ->get([
                    'FALS_IDFA',
                    'FALS_CODO',
                    'FALS_UUID',
                    'FALS_VERS',
                    'FALS_FEEM',
                    'FALS_RFCE',
                    'FALS_RFCR',
                    'FALS_FOLI',
                    'FALS_SERI',
                    'FALS_FOPA',
                    'FALS_MEPA',
                    'FALS_TICA',
                    'FALS_MONE',
                    'FALS_COPA',
                    'DLSC_IDDO',
                    'DLSC_CODO',
                    'DLSC_TIPO',
                    'DLSC_TIAR',
                    'DLSC_DESC',
                ]);
            $arrInvoice = json_decode( json_encode( $arrInvoice ), true );
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_ORDER_SHEET003: Ocurrió un error al obtener la información de las facturas.", 
                $th->getMessage(), 
                500
            );
        }
        if ( empty($arrInvoice) ) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_ORDER_SHEET004: La orden de compra no tiene ninguna factura registrada.", 
                [], 
                500
            );
        }

        $arrInvoiceOrder = array();
        $arrInvoiceCotizacion = array();
        foreach ($arrInvoice as $keyInvoice => $invoice) {
            if ( $invoice['DLSC_TIPO'] === 'Orden') {
                $arrInvoiceOrder[] = $invoice;
            }

            if ( $invoice['DLSC_TIPO'] === 'Cotización') {
                $arrInvoiceCotizacion[] = $invoice;
            }
        }
        unset($arrInvoice);
        if ( !empty($arrInvoiceOrder) ) {
            $idMax = 0;
            foreach ($arrInvoiceOrder as $keyInvoiceOrder => $invoiceOrder) {
                if ( $invoiceOrder['FALS_IDFA'] > $idMax ) {
                    $idMax = $invoiceOrder['FALS_IDFA'];
                    $arrInvoice = $invoiceOrder;
                }
            }
        } else {
            $idMax = 0;
            foreach ($arrInvoiceCotizacion as $keyInvoiceCotizacion => $invoiceCotizacion) {
                if ( $invoiceCotizacion['FALS_IDFA'] > $idMax ) {
                    $idMax = $invoiceCotizacion['FALS_IDFA'];
                    $arrInvoice = $invoiceCotizacion;
                }
            }
        }
        
        $spreadsheet = new Spreadsheet();
        $activeWorksheet = $spreadsheet->getActiveSheet()->setTitle($arrOrder['ORCO_NUOR']);


        $headerA1 = 'Número de orden';
        $contentB1 = $arrOrder['ORCO_NUOR'];
        $activeWorksheet->setCellValue("A1", $headerA1);
        $activeWorksheet->setCellValue("B1", $contentB1);
        $activeWorksheet->getStyle( "A1" )->getFont()->setBold(true);


        $headerA2 = 'Estado de la orden';
        $contentB2 = $arrOrder['ORCO_ESTA'];
        $activeWorksheet->setCellValue("A2", $headerA2);
        $activeWorksheet->setCellValue("B2", $contentB2);
        $activeWorksheet->getStyle( "A2" )->getFont()->setBold(true);


        $headerA3 = 'Numero de línea de solicitud';
        $contentB3 = $arrOrder['LINE_IDLI'];
        $activeWorksheet->setCellValue("A3", $headerA3);
        $activeWorksheet->setCellValue("B3", $contentB3);
        $activeWorksheet->getStyle( "A3" )->getFont()->setBold(true);


        $headerA4 = '';
        if ( !is_null( $arrOrder['LINE_OTPR'] ) && is_null( $arrOrder['LINE_OTCO'] )  ) {
            $headerA4 = 'Orden de Trabajo Preventivo';
            $contentB4 = $arrOrder['LINE_OTPR'];
        } else if ( is_null( $arrOrder['LINE_OTPR'] ) && !is_null( $arrOrder['LINE_OTCO'] )  ) {
            $headerA4 = 'Orden de Trabajo Correctivo';
            $contentB4 = $arrOrder['LINE_OTCO'];
        } else if ( is_null( $arrOrder['LINE_OTPR'] ) && is_null( $arrOrder['LINE_OTCO'] )  ) {
            $headerA4 = 'Orden de Trabajo';
            $contentB4 = 'No aplica';
        } else {
            $headerA4 = 'Orden de Trabajo';
            $contentB4 = 'No aplica';
        }
        $activeWorksheet->setCellValue("A4", $headerA4 );
        $activeWorksheet->setCellValue("B4", $contentB4 );
        $activeWorksheet->getStyle( "A4" )->getFont()->setBold(true);


        $headerA5 = 'ID Despacho';
        $contentB5 = $arrOrder['DESP_IDDE'];
        $activeWorksheet->setCellValue("A5", $headerA5 );
        $activeWorksheet->setCellValue("B5", $contentB5 );
        $activeWorksheet->getStyle( "A5" )->getFont()->setBold(true);


        $headerA6 = 'Despacho';
        $contentB6 = $arrOrder['DESP_NODE'];
        $activeWorksheet->setCellValue("A6", $headerA6 );
        $activeWorksheet->setCellValue("B6", $contentB6 );
        $activeWorksheet->getStyle( "A6" )->getFont()->setBold(true);


        $headerA7 = 'Responsable del despacho';
        $contentB7 = $arrOrder['DESP_NOMB'] . ' ' . $arrOrder['DESP_APPA'] . ' ' . $arrOrder['DESP_APMA'];
        $activeWorksheet->setCellValue("A7", $headerA7 );
        $activeWorksheet->setCellValue("B7", $contentB7 );
        $activeWorksheet->getStyle( "A7" )->getFont()->setBold(true);


        $headerA8 = 'Teléfono del despacho';
        $contentB8 = $arrOrder['DESP_LADA'] . ' ' . $arrOrder['DESP_TELE'];
        $activeWorksheet->setCellValue("A8", $headerA8 );
        $activeWorksheet->setCellValue("B8", $contentB8 );
        $activeWorksheet->getStyle( "A8" )->getFont()->setBold(true);


        $headerA9 = 'Correo electrónico del despacho';
        $contentB9 = $arrOrder['DESP_CORR'];
        $activeWorksheet->setCellValue("A9", $headerA9 );
        $activeWorksheet->setCellValue("B9", $contentB9 );
        $activeWorksheet->getStyle( "A9" )->getFont()->setBold(true);


        $headerA10 = 'Calle del despacho';
        $contentB10 = $arrOrder['DESP_CALL'];
        $activeWorksheet->setCellValue("A10", $headerA10 );
        $activeWorksheet->setCellValue("B10", $contentB10 );
        $activeWorksheet->getStyle( "A10" )->getFont()->setBold(true);

        
        $headerA11 = 'Número exterior';
        $contentB11 = $arrOrder['DESP_NUEX'];
        $activeWorksheet->setCellValue("A11", $headerA11 );
        $activeWorksheet->setCellValue("B11", $contentB11 );
        $activeWorksheet->getStyle( "A11" )->getFont()->setBold(true);


        $headerA12 = 'Número interior';
        if ( !is_null($arrOrder['DESP_NUIN']) ) {
            $contentB12 = $arrOrder['DESP_NUIN'];
        } else {
            $contentB12 = 'No aplica';
        }
        $activeWorksheet->setCellValue("A12", $headerA12 );
        $activeWorksheet->setCellValue("B12", $contentB12 );
        $activeWorksheet->getStyle( "A12" )->getFont()->setBold(true);


        $headerA13 = 'Código postal';
        $contentB13 = $arrOrder['DESP_COPO'];
        $activeWorksheet->setCellValue("A13", $headerA13 );
        $activeWorksheet->setCellValue("B13", $contentB13 );
        $activeWorksheet->getStyle( "A13" )->getFont()->setBold(true);

        
        $headerA14 = 'Colonia';
        $contentB14 = $arrOrder['DESP_COLO'];
        $activeWorksheet->setCellValue("A14", $headerA14 );
        $activeWorksheet->setCellValue("B14", $contentB14 );
        $activeWorksheet->getStyle( "A14" )->getFont()->setBold(true);


        $headerA15 = 'Localidad';
        if ( !is_null($arrOrder['DESP_LOCA']) && !empty($arrOrder['DESP_LOCA']) ) {
            $contentB15 = $arrOrder['DESP_LOCA'];
        } else {
            $contentB15 = 'No aplica';
        }
        $activeWorksheet->setCellValue("A15", $headerA15 );
        $activeWorksheet->setCellValue("B15", $contentB15 );
        $activeWorksheet->getStyle( "A15" )->getFont()->setBold(true);


        $headerA16 = 'Municipio';
        $contentB16 = $arrOrder['DESP_MUNI'];
        $activeWorksheet->setCellValue("A16", $headerA16 );
        $activeWorksheet->setCellValue("B16", $contentB16 );
        $activeWorksheet->getStyle( "A16" )->getFont()->setBold(true);


        $headerA17 = 'Entidad Federativa';
        $contentB17 = $arrOrder['DESP_ENTI'];
        $activeWorksheet->setCellValue("A17", $headerA17 );
        $activeWorksheet->setCellValue("B17", $contentB17 );
        $activeWorksheet->getStyle( "A17" )->getFont()->setBold(true);


        $headerA18 = 'País';
        $contentB18 = $arrOrder['DESP_PAIS'];
        $activeWorksheet->setCellValue("A18", $headerA18 );
        $activeWorksheet->setCellValue("B18", $contentB18 );
        $activeWorksheet->getStyle( "A18" )->getFont()->setBold(true);


        $headerA19 = 'Número de proveedor';
        $contentB19 = $arrOrder['PROV_NUPR'];
        $activeWorksheet->setCellValue("A19", $headerA19 );
        $activeWorksheet->setCellValue("B19", $contentB19 );
        $activeWorksheet->getStyle( "A19" )->getFont()->setBold(true);


        $headerA20 = 'Número de subcontratista';
        $contentB20 = $arrOrder['PROV_IDPS'];
        $activeWorksheet->setCellValue("A20", $headerA20 );
        $activeWorksheet->setCellValue("B20", $contentB20 );
        $activeWorksheet->getStyle( "A20" )->getFont()->setBold(true);


        $headerA21 = 'Nombre comercial del proveedor';
        $contentB21 = $arrOrder['PROV_NOCO'];
        $activeWorksheet->setCellValue("A21", $headerA21 );
        $activeWorksheet->setCellValue("B21", $contentB21 );
        $activeWorksheet->getStyle( "A21" )->getFont()->setBold(true);


        $headerA22 = 'Referencia fiscal del proveedor';
        $contentB22 = $arrOrder['REFI_DRFI'] . ' (' . $arrOrder['REFI_CRFI'] . ')';
        $activeWorksheet->setCellValue("A22", $headerA22 );
        $activeWorksheet->setCellValue("B22", $contentB22 );
        $activeWorksheet->getStyle( "A22" )->getFont()->setBold(true);


        $headerA23 = 'Responsable del proveedor';
        $contentB23 = $arrOrder['PROV_NOMB'] . ' ' . $arrOrder['PROV_APPA'] . ' ' . $arrOrder['PROV_APMA'];
        $activeWorksheet->setCellValue("A23", $headerA23 );
        $activeWorksheet->setCellValue("B23", $contentB23 );
        $activeWorksheet->getStyle( "A23" )->getFont()->setBold(true);


        $headerA24 = 'Correo electrónico del proveedor';
        $contentB24 = $arrOrder['PROV_CORR'];
        $activeWorksheet->setCellValue("A24", $headerA24 );
        $activeWorksheet->setCellValue("B24", $contentB24 );
        $activeWorksheet->getStyle( "A24" )->getFont()->setBold(true);


        $headerA25 = 'Teléfono del proveedor';
        $contentB25 = $arrOrder['PROV_LAD1'] . ' ' . $arrOrder['PROV_TEL1'];
        $activeWorksheet->setCellValue("A25", $headerA25 );
        $activeWorksheet->setCellValue("B25", $contentB25 );
        $activeWorksheet->getStyle( "A25" )->getFont()->setBold(true);


        $headerA26 = '';
        $contentB26 = '';
        if ( !is_null( $arrOrder['PROV_XRFC'] ) && is_null( $arrOrder['PROV_XRFC'] )) {
            $headerA26 = 'R.F.C. del proveedor';
            $contentB26 = $arrOrder['PROV_XRFC'];
        } else if ( is_null( $arrOrder['PROV_XRFC'] ) && !is_null( $arrOrder['PROV_XRFC'] )) {
            $headerA26 = 'TAX ID. del proveedor';
            $contentB26 = $arrOrder['PROV_XTAX'];
        } else {
            $headerA26 = 'R.F.C. / TAX ID';
            $contentB26 = 'No aplica';
        }
        $activeWorksheet->setCellValue("A26", $headerA26 );
        $activeWorksheet->setCellValue("B26", $contentB26 );
        $activeWorksheet->getStyle( "A26" )->getFont()->setBold(true);

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


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


        $spreadsheet->createSheet();
        $spreadsheet->setActiveSheetIndex(1);
        $activeWorksheetArtitle = $spreadsheet->getActiveSheet()->setTitle('Artículos de la orden');
        $arrHeaderArtitle = array(
            /* A */ 'ID Artículo',
            /* B */ 'Código del Artíulo',
            /* C */ 'Artículo',
            /* D */ 'Familia',
            /* E */ 'Subfamilia',
            /* F */ 'Unidad',
            /* G */ 'Código',
            /* H */ 'Modelo',
            /* I */ 'Precio Unitario',
            /* J */ 'Cantidad',
            /* K */ 'Precio Total',
            /* L */ 'Moneda Local',
            /* M */ 'Tipo de cambio',
            /* N */ 'Precio Local',
        );
        foreach ($arrArtitle as $key => $artitle) {
            $key = $key + 2;

            $activeWorksheetArtitle->setCellValue("A".$key, $artitle['ARTI_IDAR']);
            $activeWorksheetArtitle->setCellValue("B".$key, $artitle['ARTI_CODI']);
            $activeWorksheetArtitle->setCellValue("C".$key, $artitle['ARTI_NOMB']);
            $activeWorksheetArtitle->setCellValue("D".$key, $artitle['FAMI_NOFA'] . ' (' . $artitle['FAMI_COFA'] . ')');
            $activeWorksheetArtitle->setCellValue("E".$key, $artitle['SUBF_NOSU'] . ' (' . $artitle['SUBF_COSU'] . ')');
            $activeWorksheetArtitle->setCellValue("F".$key, $artitle['UNID_NOMB'] . ' (' . $artitle['UNID_IDUN'] . ')');
            $activeWorksheetArtitle->setCellValue("G".$key, $artitle['INAR_CODI']);
            $activeWorksheetArtitle->setCellValue("H".$key, $artitle['INAR_MODE']);
            $activeWorksheetArtitle->setCellValue("I".$key, '$'. $artitle['INAR_PREC'] . ' ' . $artitle['CAMO_COMO'] );
            $activeWorksheetArtitle->setCellValue("J".$key, $artitle['CANTIDAD']);
            $activeWorksheetArtitle->setCellValue("K".$key, '$'. ($artitle['INAR_PREC'] * $artitle['CANTIDAD']). ' ' . $artitle['CAMO_COMO'] );
            $activeWorksheetArtitle->setCellValue("L".$key, 'MXN');
            $activeWorksheetArtitle->setCellValue("M".$key, $arrInvoice['FALS_TICA']);
            $activeWorksheetArtitle->setCellValue("N".$key, '$'.($arrInvoice['FALS_TICA'] * $artitle['INAR_PREC']) . ' MXN'  );
        }
        foreach ($arrHeaderArtitle as $keyHeaderArtitle => $headerArtitle) {
            $activeWorksheetArtitle->setCellValue( $arrAlphabet[$keyHeaderArtitle]."1", $headerArtitle);
            $activeWorksheetArtitle->getColumnDimension( $arrAlphabet[$keyHeaderArtitle] )->setAutoSize(true);
            $activeWorksheetArtitle->getStyle( $arrAlphabet[$keyHeaderArtitle]."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_divisa_' . $arrOrder['ORCO_NUOR'];
        $exte = 'xlsx';

        if ($_SERVER['SERVER_NAME'] === '192.168.100.105') {
            $filePath = $this->resourcesController->pathService.'\\public\\public_files\\';    // API JEAN
        } else {
            $filePath = $this->resourcesController->pathService.'\\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 = $this->resourcesController->pathService."\\storage\\app\\" . $ubic;
        } else {
            $ubic = $this->resourcesController->pathService."\\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 generateSheetDivisaPDF($encOrderNumber, $user, $line) {
        try {
            $idOrder = $this->encController->decrypt($encOrderNumber);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(
                true, 
                "ERR_ORDER_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_ORDER_SHEET001: Ocurrió un error al obtener el usuario.", 
                $th->getMessage(), 
                500
            );
        }

        try {
            $arrOrder = (array) DB::table('S002V01TORCO')
                ->where('ORCO_NUOR', '=', $idOrder)
                ->where('ORCO_NULI', '=', $line)
                ->where('LINE_NULI', '=', $line)
                ->where('DESP_NULI', '=', $line)
                ->where('PROV_NULI', '=', $line)
                ->where('DESP_ESTA', '=', 'Activo')
                ->where('PROV_ESTA', '=', 'Activo')
                ->join('S002V01TLINE', 'LINE_IDLI', '=', 'ORCO_IDLI')
                ->join('S002V01TDESP', 'DESP_IDDE', '=', 'ORCO_IDDE')
                ->join('S002V01TPROV', 'PROV_NUPR', '=', 'LINE_NUPR')
                ->join('S002V01TREFI', 'REFI_CRFI', '=', 'PROV_CRFI')
                ->first([
                    'ORCO_NUOR',
                    'ORCO_ESTA',
                    'LINE_IDLI',
                    'LINE_OTPR',
                    'LINE_OTCO',
                    'LINE_ESTA',
                    'DESP_IDDE',
                    'DESP_NODE',
                    'DESP_NOMB',
                    'DESP_APPA',
                    'DESP_APMA',
                    'DESP_LADA',
                    'DESP_TELE',
                    'DESP_CORR',
                    'DESP_CALL',
                    'DESP_NUEX',
                    'DESP_NUIN',
                    'DESP_COPO',
                    'DESP_COLO',
                    'DESP_LOCA',
                    'DESP_MUNI',
                    'DESP_ENTI',
                    'DESP_PAIS',
                    'DESP_COME',
                    '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',
                    'REFI_CRFI',
                    'REFI_DRFI',
                ]);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_ORDER_SHEET002: Ocurrió un error al obtener la información de la orden de compra.", 
                $th->getMessage(), 
                500
            );
        }

        try {
            $arrArtitle = DB::table('S002V01TARSE')
                ->where('ARSE_NULI', '=', $line)
                ->where('INAR_NULI', '=', $line)
                ->where('CAMO_NULI', '=', $line)
                ->where('DEAR_NULI', '=', $line)
                ->where('UNID_NULI', '=', $line)
                ->where('ARTI_NULI', '=', $line)
                ->where('FAMI_NULI', '=', $line)
                ->where('SUBF_NULI', '=', $line)
                ->where('ARSE_ESTA', '=', 'Activo')
                ->where('INAR_ESTA', '=', 'Activo')
                ->where('CAMO_ESTA', '=', 'Activo')
                ->where('DEAR_ESTA', '=', 'Activo')
                ->where('UNID_ESTA', '=', 'Activo')
                ->where('ARTI_ESTA', '=', 'Activo')
                ->where('FAMI_ESTA', '=', 'Activo')
                ->where('SUBF_ESTA', '=', 'Activo')
                ->where('ARSE_IDLI', '=', $arrOrder['LINE_IDLI'])
                ->where('ARSE_NUPR', '=', $arrOrder['PROV_NUPR'])
                ->join('S002V01TINAR','INAR_IDIN','=','ARSE_IDIN')
                ->join('S002V01TCAMO','CAMO_COMO','=','INAR_COMO')
                ->join('S002V01TDEAR','DEAR_IDDE','=','INAR_IDDE')
                ->join('S002V01TUNID','UNID_IDUN','=','DEAR_IDUN')
                ->join('S002V01TARTI','ARTI_IDAR','=','DEAR_IDAR')
                ->join('S002V01TFAMI','FAMI_COFA','=','ARTI_COFA')
                ->join('S002V01TSUBF','SUBF_COSU','=','ARTI_COSU')
                ->get([
                    'ARSE_IDAS',
                    'ARSE_IDLI',
                    // 'ARSE_CANT',
                    // 'ARSE_PRTO',
                    'INAR_IDIN',
                    'INAR_CODI',
                    'INAR_MODE',
                    'INAR_PREC',
                    'INAR_MOMI',
                    'INAR_CARA',
                    'CAMO_COMO',
                    'CAMO_DESC',
                    // 'CAMO_DECI',
                    // 'CAMO_POVA',
                    // 'CAMO_FEIN',
                    // 'CAMO_FEFI',
                    'DEAR_IDDE',
                    'DEAR_IMAG',
                    'DEAR_DESC',
                    'DEAR_CARA',
                    'DEAR_COWE',
                    'UNID_IDUN',
                    'UNID_NOMB',
                    'UNID_ACRO',
                    'ARTI_IDAR',
                    'ARTI_CODI',
                    'ARTI_NOMB',
                    'FAMI_COFA',
                    'FAMI_NOFA',
                    'SUBF_COSU',
                    'SUBF_NOSU',
                ]);
            $arrArtitle = json_decode( json_encode( $arrArtitle ), true );
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_ORDER_SHEET002: Ocurrió un error al obtener la información de la orden de compra.", 
                $th->getMessage(), 
                500
            );
        }

        $index = 0;
        $arrArtitlesTemp = array();
        foreach ($arrArtitle as $keyArtitle => $artitles) {
            if ($keyArtitle === 0) {
                $arrArtitlesTemp[$index] = $artitles;
                $arrArtitlesTemp[$index]['CANTIDAD'] = 1;
            } else {
                if (
                    in_array($artitles['INAR_IDIN'], array_column($arrArtitlesTemp, 'INAR_IDIN'))
                ) {
                    $indexArtitle = array_search($artitles['INAR_IDIN'], array_column($arrArtitlesTemp, 'INAR_IDIN'));
                    $arrArtitlesTemp[$indexArtitle]['CANTIDAD'] = $arrArtitlesTemp[$indexArtitle]['CANTIDAD'] + 1;
                } else {
                    $index = $index + 1;
                    $arrArtitlesTemp[$index] = $artitles;
                    $arrArtitlesTemp[$index]['CANTIDAD'] = 1;
                }
            }
        }
        $arrArtitle = $arrArtitlesTemp;

        try {
            $arrInvoice = DB::table('S002V01TFALS')
                ->where('FALS_IDLI', '=', $arrOrder['LINE_IDLI'])
                ->where('FALS_NULI', '=', $line)
                ->where('DLSC_NULI', '=', $line)
                ->where('FALS_ESTA', '=', 'Activo')
                ->where('DLSC_ESTA', '=', 'Activo')
                ->where('DLSC_TIPO', '=', 'Cotización')
                ->orWhere('DLSC_TIPO', '=', 'Orden')
                ->join('S002V01TDLSC', 'DLSC_IDFA', '=', 'FALS_IDFA')
                ->get([
                    'FALS_IDFA',
                    'FALS_CODO',
                    'FALS_UUID',
                    'FALS_VERS',
                    'FALS_FEEM',
                    'FALS_RFCE',
                    'FALS_RFCR',
                    'FALS_FOLI',
                    'FALS_SERI',
                    'FALS_FOPA',
                    'FALS_MEPA',
                    'FALS_TICA',
                    'FALS_MONE',
                    'FALS_COPA',
                    'DLSC_IDDO',
                    'DLSC_CODO',
                    'DLSC_TIPO',
                    'DLSC_TIAR',
                    'DLSC_DESC',
                ]);
            $arrInvoice = json_decode( json_encode( $arrInvoice ), true );
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_ORDER_SHEET003: Ocurrió un error al obtener la información de las facturas.", 
                $th->getMessage(), 
                500
            );
        }
        if ( empty($arrInvoice) ) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_ORDER_SHEET004: La orden de compra no tiene ninguna factura registrada.", 
                [], 
                500
            );
        }

        $arrInvoiceOrder = array();
        $arrInvoiceCotizacion = array();
        foreach ($arrInvoice as $keyInvoice => $invoice) {
            if ( $invoice['DLSC_TIPO'] === 'Orden') {
                $arrInvoiceOrder[] = $invoice;
            }

            if ( $invoice['DLSC_TIPO'] === 'Cotización') {
                $arrInvoiceCotizacion[] = $invoice;
            }
        }
        unset($arrInvoice);
        if ( !empty($arrInvoiceOrder) ) {
            $idMax = 0;
            foreach ($arrInvoiceOrder as $keyInvoiceOrder => $invoiceOrder) {
                if ( $invoiceOrder['FALS_IDFA'] > $idMax ) {
                    $idMax = $invoiceOrder['FALS_IDFA'];
                    $arrInvoice = $invoiceOrder;
                }
            }
        } else {
            $idMax = 0;
            foreach ($arrInvoiceCotizacion as $keyInvoiceCotizacion => $invoiceCotizacion) {
                if ( $invoiceCotizacion['FALS_IDFA'] > $idMax ) {
                    $idMax = $invoiceCotizacion['FALS_IDFA'];
                    $arrInvoice = $invoiceCotizacion;
                }
            }
        }

        $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%;
                    font-size: 8pt;
                }

                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 de Orden de Compra: </b>
                <span>'.$arrOrder['ORCO_NUOR'].'</span>
            </div>
            <div class="item">
                <b>Estado de la orden: </b>
                <span>'.$arrOrder['ORCO_ESTA'].'</span>
            </div>
            <div class="item">
                <b>Numero de línea de solicitud: </b>
                <span>'.$arrOrder['LINE_IDLI'].'</span>
            </div>
        ';

        if ( !is_null( $arrOrder['LINE_OTPR'] ) && is_null( $arrOrder['LINE_OTCO'] )  ) {
            $html.='<div class="item">
                        <b>Orden de Trabajo Preventivo: </b>
                        <span>'.$arrOrder['LINE_OTPR'].'</span>
                    </div>';
        } else if ( is_null( $arrOrder['LINE_OTPR'] ) && !is_null( $arrOrder['LINE_OTCO'] )  ) {
            $html.='<div class="item">
                        <b>Orden de Trabajo Correctivo: </b>
                        <span>'.$arrOrder['LINE_OTCO'].'</span>
                    </div>';
        } else if ( is_null( $arrOrder['LINE_OTPR'] ) && is_null( $arrOrder['LINE_OTCO'] )  ) {
            $html.='<div class="item">
                        <b>Orden de Trabajo: </b>
                        <span>No aplica</span>
                    </div>';
        } else {
            $html.='<div class="item">
                        <b>Orden de Trabajo: </b>
                        <span>No aplica</span>
                    </div>';
        }

        $html.='<div class="item">
                    <b>ID Despacho: </b>
                    <span>'.$arrOrder['DESP_IDDE'].'</span>
                </div>
                <div class="item">
                    <b>Despacho: </b>
                    <span>'.$arrOrder['DESP_NODE'].'</span>
                </div>
                <div class="item">
                    <b>Responsable del despacho: </b>
                    <span>'.$arrOrder['DESP_NOMB'] . ' ' . $arrOrder['DESP_APPA'] . ' ' . $arrOrder['DESP_APMA'].'</span>
                </div>
                <div class="item">
                    <b>Teléfono del despacho: </b>
                    <span>'.$arrOrder['DESP_LADA'] . ' ' . $arrOrder['DESP_TELE'].'</span>
                </div>
                <div class="item">
                    <b>Correo electrónico del despacho: </b>
                    <span>'.$arrOrder['DESP_CORR'].'</span>
                </div>
                <div class="item">
                    <b>Calle del despacho: </b>
                    <span>'.$arrOrder['DESP_CALL'].'</span>
                </div>
                <div class="item">
                    <b>Número exterior: </b>
                    <span>'.$arrOrder['DESP_NUEX'].'</span>
                </div>
        ';
        if ( !is_null($arrOrder['DESP_NUIN']) ) {
            $html.='<div class="item">
                        <b>Número interior: </b>
                        <span>'.$arrOrder['DESP_NUIN'].'</span>
                    </div>';
        } else {
            $html.='<div class="item">
                        <b>Número interior: </b>
                        <span>No aplica</span>
                    </div>';
        }

        $html.='<div class="item">
                    <b>Código postal: </b>
                    <span>'.$arrOrder['DESP_COPO'].'</span>
                </div>
                <div class="item">
                    <b>Colonia: </b>
                    <span>'.$arrOrder['DESP_COLO'].'</span>
                </div>
        ';


        if ( !is_null($arrOrder['DESP_LOCA']) && !empty($arrOrder['DESP_LOCA']) ) {
            $html.='<div class="item">
                        <b>Localidad: </b>
                        <span>'.$arrOrder['DESP_LOCA'].'</span>
                    </div>';
        } else {
            $html.='<div class="item">
                        <b>Localidad: </b>
                        <span>No aplica</span>
                    </div>';
        }

        $html.='<div class="item">
                    <b>Municipio: </b>
                    <span>'.$arrOrder['DESP_MUNI'].'</span>
                </div>
                <div class="item">
                    <b>Entidad Federativa: </b>
                    <span>'.$arrOrder['DESP_ENTI'].'</span>
                </div>
                <div class="item">
                    <b>País: </b>
                    <span>'.$arrOrder['DESP_PAIS'].'</span>
                </div>
                <div class="item">
                    <b>Número de proveedor: </b>
                    <span>'.$arrOrder['PROV_NUPR'].'</span>
                </div>
                <div class="item">
                    <b>Número de subcontratista: </b>
                    <span>'.$arrOrder['PROV_IDPS'].'</span>
                </div>
                <div class="item">
                    <b>Nombre comercial del proveedor: </b>
                    <span>'.$arrOrder['PROV_NOCO'].'</span>
                </div>
                <div class="item">
                    <b>Referencia fiscal del proveedor: </b>
                    <span>'.$arrOrder['REFI_DRFI'] . ' (' . $arrOrder['REFI_CRFI'] . ')'.'</span>
                </div>
                <div class="item">
                    <b>Responsable del proveedor: </b>
                    <span>'.$arrOrder['PROV_NOMB'] . ' ' . $arrOrder['PROV_APPA'] . ' ' . $arrOrder['PROV_APMA'].'</span>
                </div>
                <div class="item">
                    <b>Correo electrónico del proveedor: </b>
                    <span>'.$arrOrder['PROV_CORR'].'</span>
                </div>
                <div class="item">
                    <b>Teléfono del proveedor: </b>
                    <span>'.$arrOrder['PROV_LAD1'] . ' ' . $arrOrder['PROV_TEL1'].'</span>
                </div>
        ';

        if ( !is_null( $arrOrder['PROV_XRFC'] ) && is_null( $arrOrder['PROV_XRFC'] )) {
            $html.='<div class="item">
                        <b>R.F.C. del proveedor: </b>
                        <span>'.$arrOrder['PROV_XRFC'].'</span>
                    </div>';
        } else if ( is_null( $arrOrder['PROV_XRFC'] ) && !is_null( $arrOrder['PROV_XRFC'] )) {
            $html.='<div class="item">
                        <b>TAX ID. del proveedor: </b>
                        <span>'.$arrOrder['PROV_XTAX'].'</span>
                    </div>';            
        } else {
            $html.='<div class="item">
                        <b>R.F.C. / TAX ID: </b>
                        <span>No aplica</span>
                    </div>';
        }


        if ( !empty($arrArtitle) ) {
            $html.='
                    <div style="page-break-after:always;"></div>

                    <div class="section-title">
                        <b>Artículos</b>
                    </div>';
            $html.='<table cellspacing="0" cellpadding="0">
                        <thead>
                            <tr>
                                <th>ID</th>
                                <th>Código del artíulo</th>
                                <th>Artículo</th>
                                <th>Familia</th>
                                <th>Subfamilia</th>
                                <th>Unidad</th>
                                <th>Código</th>
                                <th>Modelo</th>
                                <th>Precio Unitario</th>
                                <th>Cantidad</th>
                                <th>Precio Total</th>
                                <th>Moneda Local</th>
                                <th>Tipo de cambio</th>
                                <th>Precio Local</th>
                            </tr>
                        </thead>
                        <tbody>';
            foreach ($arrArtitle as $keyArtitle => $artitle) {
                $html.=    '<tr>
                                
                                <td>'.$artitle['ARTI_IDAR'] . '</td>
                                <td>'.$artitle['ARTI_CODI'] . '</td>
                                <td>'.$artitle['ARTI_NOMB'] . '</td>
                                <td>'.$artitle['FAMI_NOFA'] . ' (' . $artitle['FAMI_COFA'] . ')' . '</td>
                                <td>'.$artitle['SUBF_NOSU'] . ' (' . $artitle['SUBF_COSU'] . ')' . '</td>
                                <td>'.$artitle['UNID_NOMB'] . ' (' . $artitle['UNID_IDUN'] . ')' . '</td>
                                <td>'.$artitle['INAR_CODI'] . '</td>
                                <td>'.$artitle['INAR_MODE'] . '</td>
                                <td>'.'$'. $artitle['INAR_PREC'] . ' ' . $artitle['CAMO_COMO']  . '</td>
                                <td>'.$artitle['CANTIDAD'] . '</td>
                                <td>'.'$'. ($artitle['INAR_PREC'] * $artitle['CANTIDAD']) . ' ' . $artitle['CAMO_COMO']  . '</td>
                                <td>'.'MXN' . '</td>
                                <td>'.$arrInvoice['FALS_TICA'] . '</td>
                                <td>'.'$'.($arrInvoice['FALS_TICA'] * $artitle['INAR_PREC']) . ' MXN' . '</td>

                            </tr>';
            }
            $html.=    '</tbody>
                    </table>';
        }


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

        // return $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_divisa_' . $arrOrder['ORCO_NUOR'];

        $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_divisa_' . $arrOrder['ORCO_NUOR'];
        $exte = 'pdf';        
        
        
        if ($_SERVER['SERVER_NAME'] === '192.168.100.105') {
            $filePath = $this->resourcesController->pathService.'\\public\\public_files\\';    // API JEAN
        } else {
            $filePath = $this->resourcesController->pathService.'\\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', 'landscape');
        $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 = $this->resourcesController->pathService."\\storage\\app\\" . $ubic;
        } else {
            $ubic = $this->resourcesController->pathService."\\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 generateSheetInvoiceXLS($encOrderNumber, $user, $line) {
        try {
            $idOrder = $this->encController->decrypt($encOrderNumber);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(
                true, 
                "ERR_ORDER_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_ORDER_SHEET001: Ocurrió un error al obtener el usuario.", 
                $th->getMessage(), 
                500
            );
        }

        try {
            $arrOrder = (array) DB::table('S002V01TORCO')
                ->where('ORCO_NUOR', '=', $idOrder)
                ->where('ORCO_NULI', '=', $line)
                ->where('LINE_NULI', '=', $line)
                ->where('DESP_NULI', '=', $line)
                ->where('PROV_NULI', '=', $line)
                ->where('DESP_ESTA', '=', 'Activo')
                ->where('PROV_ESTA', '=', 'Activo')
                ->join('S002V01TLINE', 'LINE_IDLI', '=', 'ORCO_IDLI')
                ->join('S002V01TDESP', 'DESP_IDDE', '=', 'ORCO_IDDE')
                ->join('S002V01TPROV', 'PROV_NUPR', '=', 'LINE_NUPR')
                ->join('S002V01TREFI', 'REFI_CRFI', '=', 'PROV_CRFI')
                ->first([
                    'ORCO_NUOR',
                    'ORCO_ESTA',
                    'LINE_IDLI',
                    'LINE_OTPR',
                    'LINE_OTCO',
                    'LINE_ESTA',
                    'DESP_IDDE',
                    'DESP_NODE',
                    'DESP_NOMB',
                    'DESP_APPA',
                    'DESP_APMA',
                    'DESP_LADA',
                    'DESP_TELE',
                    'DESP_CORR',
                    'DESP_CALL',
                    'DESP_NUEX',
                    'DESP_NUIN',
                    'DESP_COPO',
                    'DESP_COLO',
                    'DESP_LOCA',
                    'DESP_MUNI',
                    'DESP_ENTI',
                    'DESP_PAIS',
                    'DESP_COME',
                    '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',
                    'REFI_CRFI',
                    'REFI_DRFI',
                ]);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_ORDER_SHEET002: Ocurrió un error al obtener la información de la orden de compra.", 
                $th->getMessage(), 
                500
            );
        }

        try {
            $arrArtitle = DB::table('S002V01TARSE')
                ->where('ARSE_NULI', '=', $line)
                ->where('INAR_NULI', '=', $line)
                ->where('CAMO_NULI', '=', $line)
                ->where('DEAR_NULI', '=', $line)
                ->where('UNID_NULI', '=', $line)
                ->where('ARTI_NULI', '=', $line)
                ->where('FAMI_NULI', '=', $line)
                ->where('SUBF_NULI', '=', $line)
                ->where('ARSE_ESTA', '=', 'Activo')
                ->where('INAR_ESTA', '=', 'Activo')
                ->where('CAMO_ESTA', '=', 'Activo')
                ->where('DEAR_ESTA', '=', 'Activo')
                ->where('UNID_ESTA', '=', 'Activo')
                ->where('ARTI_ESTA', '=', 'Activo')
                ->where('FAMI_ESTA', '=', 'Activo')
                ->where('SUBF_ESTA', '=', 'Activo')
                ->where('ARSE_IDLI', '=', $arrOrder['LINE_IDLI'])
                ->where('ARSE_NUPR', '=', $arrOrder['PROV_NUPR'])
                ->join('S002V01TINAR','INAR_IDIN','=','ARSE_IDIN')
                ->join('S002V01TCAMO','CAMO_COMO','=','INAR_COMO')
                ->join('S002V01TDEAR','DEAR_IDDE','=','INAR_IDDE')
                ->join('S002V01TUNID','UNID_IDUN','=','DEAR_IDUN')
                ->join('S002V01TARTI','ARTI_IDAR','=','DEAR_IDAR')
                ->join('S002V01TFAMI','FAMI_COFA','=','ARTI_COFA')
                ->join('S002V01TSUBF','SUBF_COSU','=','ARTI_COSU')
                ->get([
                    'ARSE_IDAS',
                    'ARSE_IDLI',
                    // 'ARSE_CANT',
                    // 'ARSE_PRTO',
                    'INAR_IDIN',
                    'INAR_CODI',
                    'INAR_MODE',
                    'INAR_PREC',
                    'INAR_MOMI',
                    'INAR_CARA',
                    'CAMO_COMO',
                    'CAMO_DESC',
                    // 'CAMO_DECI',
                    // 'CAMO_POVA',
                    // 'CAMO_FEIN',
                    // 'CAMO_FEFI',
                    'DEAR_IDDE',
                    'DEAR_IMAG',
                    'DEAR_DESC',
                    'DEAR_CARA',
                    'DEAR_COWE',
                    'UNID_IDUN',
                    'UNID_NOMB',
                    'UNID_ACRO',
                    'ARTI_IDAR',
                    'ARTI_CODI',
                    'ARTI_NOMB',
                    'FAMI_COFA',
                    'FAMI_NOFA',
                    'SUBF_COSU',
                    'SUBF_NOSU',
                ]);
            $arrArtitle = json_decode( json_encode( $arrArtitle ), true );
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_ORDER_SHEET002: Ocurrió un error al obtener la información de la orden de compra.", 
                $th->getMessage(), 
                500
            );
        }

        $index = 0;
        $arrArtitlesTemp = array();
        foreach ($arrArtitle as $keyArtitle => $artitles) {
            if ($keyArtitle === 0) {
                $arrArtitlesTemp[$index] = $artitles;
                $arrArtitlesTemp[$index]['CANTIDAD'] = 1;
            } else {
                if (
                    in_array($artitles['INAR_IDIN'], array_column($arrArtitlesTemp, 'INAR_IDIN'))
                ) {
                    $indexArtitle = array_search($artitles['INAR_IDIN'], array_column($arrArtitlesTemp, 'INAR_IDIN'));
                    $arrArtitlesTemp[$indexArtitle]['CANTIDAD'] = $arrArtitlesTemp[$indexArtitle]['CANTIDAD'] + 1;
                } else {
                    $index = $index + 1;
                    $arrArtitlesTemp[$index] = $artitles;
                    $arrArtitlesTemp[$index]['CANTIDAD'] = 1;
                }
            }
        }
        $arrArtitle = $arrArtitlesTemp;

        try {
            $arrInvoice = DB::table('S002V01TFALS')
                ->where('FALS_IDLI', '=', $arrOrder['LINE_IDLI'])
                ->where('FALS_NULI', '=', $line)
                ->where('DLSC_NULI', '=', $line)
                ->where('FALS_ESTA', '=', 'Activo')
                ->where('DLSC_ESTA', '=', 'Activo')
                ->where('DLSC_TIPO', '=', 'Cotización')
                ->orWhere('DLSC_TIPO', '=', 'Orden')
                ->join('S002V01TDLSC', 'DLSC_IDFA', '=', 'FALS_IDFA')
                ->get([
                    'FALS_IDFA',
                    'FALS_CODO',
                    'FALS_UUID',
                    'FALS_VERS',
                    'FALS_FEEM',
                    'FALS_RFCE',
                    'FALS_RFCR',
                    'FALS_FOLI',
                    'FALS_SERI',
                    'FALS_FOPA',
                    'FALS_MEPA',
                    'FALS_TICA',
                    'FALS_MONE',
                    'FALS_COPA',
                    'FALS_CAAR',
                    'FALS_PRTO',
                    'DLSC_IDDO',
                    'DLSC_CODO',
                    'DLSC_TIPO',
                    'DLSC_TIAR',
                    'DLSC_DESC',
                ]);
            $arrInvoice = json_decode( json_encode( $arrInvoice ), true );
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_ORDER_SHEET003: Ocurrió un error al obtener la información de las facturas.", 
                $th->getMessage(), 
                500
            );
        }
        if ( empty($arrInvoice) ) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_ORDER_SHEET004: La orden de compra no tiene ninguna factura registrada.", 
                [], 
                500
            );
        }

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

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

        $activeWorksheet->setCellValue("B2", 'Número de orden de compra:' );
        $activeWorksheet->setCellValue("C2", $arrOrder['ORCO_NUOR'] );
        $activeWorksheet->getStyle( "B2" )->getFont()->setBold(true);

        $arrHeaderInvoice = array(
            /* A */ 'ID Factura',
            /* B */ 'Tipo de Factura',
            /* C */ 'UUID',
            /* D */ 'Versión',
            /* E */ 'Fecha de Emisión',
            /* F */ 'R.F.C. Emisor',
            /* G */ 'R.F.C. Receptor',
            /* H */ 'Folio',
            /* I */ 'Serie',
            /* J */ 'Forma de Pago',
            /* K */ 'Método de Pago',
            /* L */ 'Tipo de Cambio',
            /* M */ 'Moneda',
            /* N */ 'Descripción',
            /* O */ 'Cantidad de artículos',
            /* P */ 'Precio Total',
        );
        foreach ($arrInvoice as $keyInvoice => $invoice) {
            $key = $keyInvoice + 5;

            $activeWorksheet->setCellValue("A".$key, $invoice['FALS_IDFA']);
            if ($invoice['DLSC_TIPO'] === 'Cotización') {
                $activeWorksheet->setCellValue("B".$key, 'Factura de Cotización');
            } else if ($invoice['DLSC_TIPO'] === 'Orden') {
                $activeWorksheet->setCellValue("B".$key, 'Factura de Orden de Compra');
            } else if ($invoice['DLSC_TIPO'] === 'Abono') {
                $activeWorksheet->setCellValue("B".$key, 'Factura de Abono');
            } else {
                $activeWorksheet->setCellValue("B".$key, 'No aplica');
            }
            $activeWorksheet->setCellValue("C".$key, $invoice['FALS_UUID']);
            $activeWorksheet->setCellValue("D".$key, $invoice['FALS_VERS']);
            $activeWorksheet->setCellValue("E".$key, $invoice['FALS_FEEM']);
            $activeWorksheet->setCellValue("F".$key, $invoice['FALS_RFCE']);
            $activeWorksheet->setCellValue("G".$key, $invoice['FALS_RFCR']);
            $activeWorksheet->setCellValue("H".$key, $invoice['FALS_FOLI']);
            $activeWorksheet->setCellValue("I".$key, $invoice['FALS_SERI']);
            $activeWorksheet->setCellValue("J".$key, $invoice['FALS_FOPA']);
            $activeWorksheet->setCellValue("K".$key, $invoice['FALS_MEPA']);
            $activeWorksheet->setCellValue("L".$key, $invoice['FALS_TICA']);
            $activeWorksheet->setCellValue("M".$key, $invoice['FALS_MONE']);
            $activeWorksheet->setCellValue("N".$key, $invoice['DLSC_DESC']);
            $activeWorksheet->setCellValue("O".$key, $invoice['FALS_CAAR']);
            $activeWorksheet->setCellValue("P".$key, $invoice['FALS_PRTO']);
        }

        foreach ($arrHeaderInvoice as $keyHeaderInvoice => $headerInvoice) {
            $activeWorksheet->setCellValue( $arrAlphabet[$keyHeaderInvoice]."4", $headerInvoice);
            $activeWorksheet->getColumnDimension( $arrAlphabet[$keyHeaderInvoice] )->setAutoSize(true);
            $activeWorksheet->getStyle( $arrAlphabet[$keyHeaderInvoice]."4" )->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_facturación_' . $arrOrder['ORCO_NUOR'];
        $exte = 'xlsx';

        if ($_SERVER['SERVER_NAME'] === '192.168.100.105') {
            $filePath = $this->resourcesController->pathService.'\\public\\public_files\\';    // API JEAN
        } else {
            $filePath = $this->resourcesController->pathService.'\\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 = $this->resourcesController->pathService."\\storage\\app\\" . $ubic;
        } else {
            $ubic = $this->resourcesController->pathService."\\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 generateSheetInvoicePDF($encOrderNumber, $user, $line) {
        try {
            $idOrder = $this->encController->decrypt($encOrderNumber);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(
                true, 
                "ERR_ORDER_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_ORDER_SHEET001: Ocurrió un error al obtener el usuario.", 
                $th->getMessage(), 
                500
            );
        }

        try {
            $arrOrder = (array) DB::table('S002V01TORCO')
                ->where('ORCO_NUOR', '=', $idOrder)
                ->where('ORCO_NULI', '=', $line)
                ->where('LINE_NULI', '=', $line)
                ->where('DESP_NULI', '=', $line)
                ->where('PROV_NULI', '=', $line)
                ->where('DESP_ESTA', '=', 'Activo')
                ->where('PROV_ESTA', '=', 'Activo')
                ->join('S002V01TLINE', 'LINE_IDLI', '=', 'ORCO_IDLI')
                ->join('S002V01TDESP', 'DESP_IDDE', '=', 'ORCO_IDDE')
                ->join('S002V01TPROV', 'PROV_NUPR', '=', 'LINE_NUPR')
                ->join('S002V01TREFI', 'REFI_CRFI', '=', 'PROV_CRFI')
                ->first([
                    'ORCO_NUOR',
                    'ORCO_ESTA',
                    'LINE_IDLI',
                    'LINE_OTPR',
                    'LINE_OTCO',
                    'LINE_ESTA',
                    'DESP_IDDE',
                    'DESP_NODE',
                    'DESP_NOMB',
                    'DESP_APPA',
                    'DESP_APMA',
                    'DESP_LADA',
                    'DESP_TELE',
                    'DESP_CORR',
                    'DESP_CALL',
                    'DESP_NUEX',
                    'DESP_NUIN',
                    'DESP_COPO',
                    'DESP_COLO',
                    'DESP_LOCA',
                    'DESP_MUNI',
                    'DESP_ENTI',
                    'DESP_PAIS',
                    'DESP_COME',
                    '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',
                    'REFI_CRFI',
                    'REFI_DRFI',
                ]);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_ORDER_SHEET002: Ocurrió un error al obtener la información de la orden de compra.", 
                $th->getMessage(), 
                500
            );
        }

        try {
            $arrArtitle = DB::table('S002V01TARSE')
                ->where('ARSE_NULI', '=', $line)
                ->where('INAR_NULI', '=', $line)
                ->where('CAMO_NULI', '=', $line)
                ->where('DEAR_NULI', '=', $line)
                ->where('UNID_NULI', '=', $line)
                ->where('ARTI_NULI', '=', $line)
                ->where('FAMI_NULI', '=', $line)
                ->where('SUBF_NULI', '=', $line)
                ->where('ARSE_ESTA', '=', 'Activo')
                ->where('INAR_ESTA', '=', 'Activo')
                ->where('CAMO_ESTA', '=', 'Activo')
                ->where('DEAR_ESTA', '=', 'Activo')
                ->where('UNID_ESTA', '=', 'Activo')
                ->where('ARTI_ESTA', '=', 'Activo')
                ->where('FAMI_ESTA', '=', 'Activo')
                ->where('SUBF_ESTA', '=', 'Activo')
                ->where('ARSE_IDLI', '=', $arrOrder['LINE_IDLI'])
                ->where('ARSE_NUPR', '=', $arrOrder['PROV_NUPR'])
                ->join('S002V01TINAR','INAR_IDIN','=','ARSE_IDIN')
                ->join('S002V01TCAMO','CAMO_COMO','=','INAR_COMO')
                ->join('S002V01TDEAR','DEAR_IDDE','=','INAR_IDDE')
                ->join('S002V01TUNID','UNID_IDUN','=','DEAR_IDUN')
                ->join('S002V01TARTI','ARTI_IDAR','=','DEAR_IDAR')
                ->join('S002V01TFAMI','FAMI_COFA','=','ARTI_COFA')
                ->join('S002V01TSUBF','SUBF_COSU','=','ARTI_COSU')
                ->get([
                    'ARSE_IDAS',
                    'ARSE_IDLI',
                    // 'ARSE_CANT',
                    // 'ARSE_PRTO',
                    'INAR_IDIN',
                    'INAR_CODI',
                    'INAR_MODE',
                    'INAR_PREC',
                    'INAR_MOMI',
                    'INAR_CARA',
                    'CAMO_COMO',
                    'CAMO_DESC',
                    // 'CAMO_DECI',
                    // 'CAMO_POVA',
                    // 'CAMO_FEIN',
                    // 'CAMO_FEFI',
                    'DEAR_IDDE',
                    'DEAR_IMAG',
                    'DEAR_DESC',
                    'DEAR_CARA',
                    'DEAR_COWE',
                    'UNID_IDUN',
                    'UNID_NOMB',
                    'UNID_ACRO',
                    'ARTI_IDAR',
                    'ARTI_CODI',
                    'ARTI_NOMB',
                    'FAMI_COFA',
                    'FAMI_NOFA',
                    'SUBF_COSU',
                    'SUBF_NOSU',
                ]);
            $arrArtitle = json_decode( json_encode( $arrArtitle ), true );
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_ORDER_SHEET002: Ocurrió un error al obtener la información de la orden de compra.", 
                $th->getMessage(), 
                500
            );
        }

        $index = 0;
        $arrArtitlesTemp = array();
        foreach ($arrArtitle as $keyArtitle => $artitles) {
            if ($keyArtitle === 0) {
                $arrArtitlesTemp[$index] = $artitles;
                $arrArtitlesTemp[$index]['CANTIDAD'] = 1;
            } else {
                if (
                    in_array($artitles['INAR_IDIN'], array_column($arrArtitlesTemp, 'INAR_IDIN'))
                ) {
                    $indexArtitle = array_search($artitles['INAR_IDIN'], array_column($arrArtitlesTemp, 'INAR_IDIN'));
                    $arrArtitlesTemp[$indexArtitle]['CANTIDAD'] = $arrArtitlesTemp[$indexArtitle]['CANTIDAD'] + 1;
                } else {
                    $index = $index + 1;
                    $arrArtitlesTemp[$index] = $artitles;
                    $arrArtitlesTemp[$index]['CANTIDAD'] = 1;
                }
            }
        }
        $arrArtitle = $arrArtitlesTemp;

        try {
            $arrInvoice = DB::table('S002V01TFALS')
                ->where('FALS_IDLI', '=', $arrOrder['LINE_IDLI'])
                ->where('FALS_NULI', '=', $line)
                ->where('DLSC_NULI', '=', $line)
                ->where('FALS_ESTA', '=', 'Activo')
                ->where('DLSC_ESTA', '=', 'Activo')
                ->where('DLSC_TIPO', '=', 'Cotización')
                ->orWhere('DLSC_TIPO', '=', 'Orden')
                ->join('S002V01TDLSC', 'DLSC_IDFA', '=', 'FALS_IDFA')
                ->get([
                    'FALS_IDFA',
                    'FALS_CODO',
                    'FALS_UUID',
                    'FALS_VERS',
                    'FALS_FEEM',
                    'FALS_RFCE',
                    'FALS_RFCR',
                    'FALS_FOLI',
                    'FALS_SERI',
                    'FALS_FOPA',
                    'FALS_MEPA',
                    'FALS_TICA',
                    'FALS_MONE',
                    'FALS_COPA',
                    'FALS_CAAR',
                    'FALS_PRTO',
                    'DLSC_IDDO',
                    'DLSC_CODO',
                    'DLSC_TIPO',
                    'DLSC_TIAR',
                    'DLSC_DESC',
                ]);
            $arrInvoice = json_decode( json_encode( $arrInvoice ), true );
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_ORDER_SHEET003: Ocurrió un error al obtener la información de las facturas.", 
                $th->getMessage(), 
                500
            );
        }
        if ( empty($arrInvoice) ) {
            return $this->responseController->makeResponse(
                true, 
                "ERR_ORDER_SHEET004: La orden de compra no tiene ninguna factura registrada.", 
                [], 
                500
            );
        }


        $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%;
                    font-size: 8pt;
                }

                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 de Orden de Compra: </b>
                <span>'.$arrOrder['ORCO_NUOR'].'</span>
            </div>';


        $html.='<table cellspacing="0" cellpadding="0">
                    <thead>
                        <tr>
                            <th>ID Factura</th>
                            <th>Tipo de Factura</th>
                            <th>UUID</th>
                            <!-- <th>Versión</th> -->
                            <th>Fecha de Emisión</th>
                            <th>R.F.C. Emisor</th>
                            <th>R.F.C. Receptor</th>
                            <!-- <th>Folio</th> -->
                            <!-- <th>Serie</th> -->
                            <!-- <th>Forma de Pago</th> -->
                            <!-- <th>Método de Pago</th> -->
                            <th>Tipo de Cambio</th>
                            <!-- <th>Moneda</th> -->
                            <th>Descripción</th>
                            <th>Cantidad de artículos</th>
                            <th>Precio Total</th>
                        </tr>
                    </thead>
                    <tbody>';
        foreach ($arrInvoice as $keyInvoice => $invoice) {
            $html.=     '<tr>';
            $html.=         '<td>'.$invoice['FALS_IDFA'] . '</td>';
            if ($invoice['DLSC_TIPO'] === 'Cotización') {
                $html.=     '<td>Factura de Cotización</td>';
            } else if ($invoice['DLSC_TIPO'] === 'Orden') {
                $html.=     '<td>Factura de Orden de Compra</td>';
            } else if ($invoice['DLSC_TIPO'] === 'Abono') {
                $html.=     '<td>Factura de Abono</td>';
            } else {
                $html.=     '<td>No aplica</td>';
            }
            $html.=         '<td>'.$invoice['FALS_UUID'].'</td>';
            // $html.=         '<td>'.$invoice['FALS_VERS'].'</td>';
            $html.=         '<td>'.$invoice['FALS_FEEM'].'</td>';
            $html.=         '<td>'.$invoice['FALS_RFCE'].'</td>';
            $html.=         '<td>'.$invoice['FALS_RFCR'].'</td>';
            // $html.=         '<td>'.$invoice['FALS_FOLI'].'</td>';
            // $html.=         '<td>'.$invoice['FALS_SERI'].'</td>';
            // $html.=         '<td>'.$invoice['FALS_FOPA'].'</td>';
            // $html.=         '<td>'.$invoice['FALS_MEPA'].'</td>';
            $html.=         '<td>'.$invoice['FALS_TICA'].'</td>';
            // $html.=         '<td>'.$invoice['FALS_MONE'].'</td>';
            $html.=         '<td>'.$invoice['DLSC_DESC'].'</td>';
            $html.=         '<td>'.$invoice['FALS_CAAR'].'</td>';
            $html.=         '<td>$'.$invoice['FALS_PRTO']. ' '. $invoice['FALS_MONE'] . '</td>';
            $html.=     '</tr>';
        }
        $html.=    '</tbody>
                </table>';
    
        $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_divisa_' . $arrOrder['ORCO_NUOR'];

        $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_facturación_' . $arrOrder['ORCO_NUOR'];
        $exte = 'pdf';        
        
        
        if ($_SERVER['SERVER_NAME'] === '192.168.100.105') {
            $filePath = $this->resourcesController->pathService.'\\public\\public_files\\';    // API JEAN
        } else {
            $filePath = $this->resourcesController->pathService.'\\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', 'landscape');
        $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 = $this->resourcesController->pathService."\\storage\\app\\" . $ubic;
        } else {
            $ubic = $this->resourcesController->pathService."\\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 ]);
    }
}