<?php

namespace App\Http\Controllers;

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

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

use stdClass;
use Throwable;

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

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

    public function getConsultOfSubcontratists($idUser, $line) {
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la solicitud no está encriptado correctamente', [], 400);
        }

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

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

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

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

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

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

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

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M11GPRS',
            'S002V01F01GESU',
            'S002V01P01COSU',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó los subcontratistas registrados.",
            $idUser,
            $nowStr,
            'S002V01S01GESU'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $subcontratists);
    }

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

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

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

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

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

        $idUser = $this->encryptionController->decrypt($form['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $form['linea']],
            ['USUA_IDUS', '=', $idUser]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    public function getSubcontratistById($idSub, $idUser, $line) {
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la consulta no está encriptado correctamente.', [], 400);
        }

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

        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la consulta no está registrado', [], 404);
        }
        
        $idSub = $this->encryptionController->decrypt($idSub);
        if(!$idSub){
            return $this->responseController->makeResponse(true, 'El ID del subcontratista consultado no está encriptado correctamente.', [], 400);
        }

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

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

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

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

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

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

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

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

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

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

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

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

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M11GPRS',
            'S002V01F01GESU',
            'S002V01P03DESU',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó al subcontratista " . $subcontratists_info->SOCIAL_REASON . " ($idSub).",
            $idUser,
            $nowStr,
            'S002V01S01GESU'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $subcontratists_info);
    }

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

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

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

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

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

        $idUser = $this->encryptionController->decrypt($form['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $form['linea']],
            ['USUA_IDUS', '=', $idUser]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

        $form = $request->all();
        $idUser = $this->encryptionController->decrypt($form['id_user']);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID de usuario no fue encriptado correctamente.', [], 400);
        }

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $form['linea']],
            ['USUA_IDUS', '=', $idUser]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

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

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

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

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

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

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

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

    public function getContractsOfEverySubcontratist($idUser, $line) {
        DB::enableQueryLog();

        $idUser = $this->encryptionController->decrypt($idUser);
        if(!$idUser){
            return $this->responseController->makeResponse(true, 'El ID del usuario que realizó la solicitud no está encriptado correctamente', [], 400);
        }

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

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

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

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

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

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

            $contractsBySubcontratists[$key] = $val;
        }

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M11GPRS',
            'S002V01F02ADCO',
            'S002V01P01HCSU',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó los contratos por subcontratista.",
            $idUser,
            $nowStr,
            'S002V01S01GESU'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $contractsBySubcontratists);
    }

    public function getContractsBySubcontratist($idSubcontratist, $idUser, $line) {
        DB::enableQueryLog();

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

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $line],
            ['USUA_IDUS', '=', $idUser]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

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

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

        $contracts = DB::table('S002V01TCONT')->select([
            'CONT_NOCO AS CONTRACT_NAME',
            'CONT_FEIN AS START_DATE',
            'CONT_FEFI AS END_DATE',
            'CONT_COST AS COST',
        ])->where([
            ['CONT_NULI', '=', $line],
            ['CONT_IDEM', '=', $idSubcontratist],
            ['CONT_TIPO', '=', 'Subcontratista'],
        ])->get()->all();

        $now = $this->functionsController->now();
        $nowStr = $now->toDateTimeString();
        $actions = DB::getQueryLog();
        $name = $this->functionsController->joinName($usr->USUA_NOMB, $usr->USUA_APPA, $usr->USUA_APMA);
        
        $idac = $this->functionsController->registerActivity(
            $line,
            'S002V01M11GPRS',
            'S002V01F02ADCO',
            'S002V01P01HCSU',
            'Consulta',
            "El usuario $name (" . $usr->USUA_IDUS . ") consultó los contratos del subcontratista #$idSubcontratist.",
            $idUser,
            $nowStr,
            'S002V01S01GESU'
        );

        $this->functionsController->registerLog($actions, $idUser, $nowStr, $idac, $line);
        return $this->responseController->makeResponse(false, 'EXITO', $contracts);
    }

    public function downloadSubcontractInfoOnExcel($idSubcontratist, $idUser, $line){
        DB::enableQueryLog();

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

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $line],
            ['USUA_IDUS', '=', $idUser]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

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

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

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

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

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

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

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

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

            $nuse = $nuse . $secu;
        }

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

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

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

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

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

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

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

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

    public function downloadSubcontractInfoOnPdf($idSubcontratist, $idUser, $line) {
        DB::enableQueryLog();

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

        $usr = DB::table('S002V01TUSUA')->where([
            ['USUA_NULI', '=', $line],
            ['USUA_IDUS', '=', $idUser]
        ])->first();
        
        if(is_null($usr)){
            return $this->responseController->makeResponse(true, 'El usuario que realizó la petición no existe.', [], 404);
        }

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

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

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

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

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

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

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

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

            $nuse = $nuse . $secu;
        }

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

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

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

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

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

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

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

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

    private function generateDocument($contracts) : Spreadsheet {
        $spreadsheet = new Spreadsheet;
        $spreadsheet->getProperties()
            ->setCreator('STC')
            ->setTitle('Historial de contratos por subcontratista.')
            ->setSubject('Historial documento')
            ->setKeywords('Subcontratista Contratos Historial')
            ->setCategory('Historial archivo');
        
        $worksheet = $spreadsheet->getActiveSheet();
        $worksheet->setTitle('HISTORIAL DE CONTRATOS');

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

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

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

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

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

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

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

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

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

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

        return $spreadsheet;
    }
}