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

<?php
/*
    Desarrollador: Ing. Jean Jairo Benitez Meza
    Ultima Modificación: 11/04/2023
    Módulo: Formularios Dinámicos
*/
namespace App\Http\Controllers;

use App\Http\Controllers\Controller;
use App\Http\Controllers\ResponseController;
use App\Http\Controllers\EncryptionController;
use Illuminate\Http\Request;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Validator;

class GenericController extends Controller
{
    private $responseController;
    private $encryptionController;
    public function __construct(){
        $this->responseController = new ResponseController();
        $this->encryptionController = new EncryptionController();
    }

    public function generateForm($numberForm, $line){
        try {
            $objForm = DB::table('S002V01TFODI')
                ->where('FODI_NUFO', '=', $numberForm)
                ->where('FODI_ESTA', '=', 'Activo')
                ->where('FODI_NULI', '=', $line)
                ->first([
                    'FODI_TIRE AS TITULO_REGISTRO',
                    'FODI_TIMO AS TITULO_MODIFICACION',
                    'FODI_DAFO AS DATA_FORM'
                ]);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(true, "ERR_GENERIC_GEN000: No se pudo realizar la consulta a la base.", $th, 500);
        }
        if (empty($objForm)) {
            return $this->responseController->makeResponse(true, "ERR_GENERIC_GEN001: No se encontró el formulario", [], 500);
        }

        $dataFormDynamic = json_decode($objForm->DATA_FORM);

        try {
            for ($i = 0; $i < count($dataFormDynamic->fields); $i++){
                for ($j = 0; $j < count($dataFormDynamic->fields[$i]->form); $j++){
                    if (
                        $dataFormDynamic->fields[$i]->form[$j]->tag == 'select' &&
                        array_key_exists('table_rel', (array) $dataFormDynamic->fields[$i]->form[$j]) &&
                        $dataFormDynamic->fields[$i]->form[$j]->column_rel
                    ){
                        $nomTab = $dataFormDynamic->fields[$i]->form[$j]->table_rel->NOMBRE_TABLA;
                        $column_rel = $dataFormDynamic->fields[$i]->form[$j]->column_rel;
                        $data_select = $this->getDataSelect($nomTab,$column_rel);
                        if($data_select['error']) return ["error" => true, "msg" => $data_select['msg']];
                        $dataFormDynamic->fields[$i]->form[$j]->data_select = $data_select['response'];
                    }
                }
            }
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(true, "ERR_GENERIC_GEN002: No se pudo realizar la consulta a la base.", [], 500);
        }

        $objResponse = [
            'TITULO_REGISTRO' => $objForm->TITULO_REGISTRO,
            'TITULO_MODIFICACION' => $objForm->TITULO_MODIFICACION,
            'FIELDS' => $dataFormDynamic->fields
        ];
        return $this->responseController->makeResponse(false, "ÉXITO", $objResponse);
    }

    public function getDataInfo(Request $request, $numberForm){
        $request = $request->all();
        $idTableMain = $request['tables']['main_table'];
        $arr_id_secondary = $request['tables']['secondary_table'];

        if (empty($numberForm) || empty($numberForm)) {
            return $this->responseController->makeResponse(true, "ERR_DYNFORM_GET000: Los identificadores no pueden estar vacios.", [], 500);
        }

        try {
            $data_form = DB::table('S002V01TFODI')->where('FODI_NUFO', $numberForm)->first(['FODI_DAFO AS DATA_FORM', 'FODI_NOMB AS NOMBRE']);
            $data_json = json_decode($data_form->DATA_FORM);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(true, "ERR_DYNFORM_GET001: No se pudo realizar la consulta a la base.", [], 500);
        }

        // Obtenemos los datos del formulario
        $arr_fields = $data_json->fields;
        $name_form = $data_form->NOMBRE;

        // Obtener el nombre de la tabla principal
        $table_main = $data_json->table_main;
        try {
            $str_table_main = $this->encryptionController->decrypt($table_main);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(true, "ERR_DYNFORM_GET002: Ocurrió un error al obtener la tabla principal.", [], 500);
        }

        // Obtener el arreglo de las tablas relacionales
        $table_relationship = $data_json->table_relationship;
        try {
            $table_relationship = $this->encryptionController->decrypt($table_relationship);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(true, "ERR_DYNFORM_GET003: Ocurrió un error al obtener las tablas relacionales", [], 500);
        }

        // Se obtienen los datos del formulario por tablas
        $arr_form_main = array();
        $arr_form_mm = array();
        $arr_form_secondary = array();
        foreach ($arr_fields as $field) {
            foreach ($field->form as $form) {
                $date_table = (array) $form->data_table;
                if ($date_table['table'] == $str_table_main) {
                    $arr_form_main[] = $form;
                }else if(array_key_exists('columnMM', $date_table)){
                    $arr_form_mm[] = $form;
                }else{
                    $arr_form_secondary[] = $form;
                }
            }
        }

        // Se le asignan los valores al formulario principales
        $arr_resp = $this->getPrimaryKeyData($str_table_main);
        if ($arr_resp['error']) {
            return $this->responseController->makeResponse(true, "ERR_DYNFORM_GET004: No se pudo obtener la información de la llave principal.", [], 500);
        }else{
            $primary_key = $arr_resp['response']->COLUMN_NAME;
            try {
                $arr_data_main = (array) DB::table($str_table_main)->where($primary_key, '=', $idTableMain)->first();
            } catch (\Throwable $th) {
                return $this->responseController->makeResponse(true, "ERR_DYNFORM_GET005: No se pudo realizar la consulta a la base.", [], 500);
            }
             // Se asignan los valores obtenidos al formulario
            foreach ($arr_form_main as $data_form) {
                $column_form = $data_form->data_table->column;
                foreach ($arr_data_main as $key => $data_main) {
                    if ($key == $column_form) {
                        $data_form->value = $data_main;
                    }
                }

                // Se obtienen los datos del select
                if ($data_form->tag == 'select' && $data_form->table_rel != null && $data_form->column_rel != null) {
                    $nomTab = $data_form->table_rel->NOMBRE_TABLA;
                    $column_rel = $data_form->column_rel;

                    $data_select = $this->getDataSelect($nomTab,$column_rel);
                    if($data_select['error']){
                        return $this->responseController->makeResponse(true, "ERR_DYNFORM_GET006: No se pudo realizar la consulta a la base.", [], 500);
                    }
                    $data_form->data_select = $data_select['response'];
                }
            }
        }

        // Se le asignan los valores al formulario M:M
        foreach ($arr_form_mm as $form_mm) {
            $table = $form_mm->columnMM->table;
            $column = $form_mm->columnMM->column;

            $table_rel = $form_mm->table_rel->NOMBRE_TABLA;
            $column_rel = $form_mm->column_rel;

            $data_select = $this->getDataSelect($table_rel,$column_rel);
            if ($data_select['error']) {
                return $this->responseController->makeResponse(true, "ERR_DYNFORM_GET007: No se pudo realizar la consulta a la base.", [], 500);
            }else{
                $form_mm->data_select = [];
                foreach ($data_select['response'] as $value) {
                    $form_mm->data_select[] = $value;
                }
            }

            try {
                $arr_data_mm = DB::table($table)->where($column, '=', $idTableMain)->get();
            } catch (\Throwable $th) {
                return $this->responseController->makeResponse(true, "ERR_DYNFORM_GET008: No se pudo realizar la consulta a la base.", [], 500);
            }

            $form_mm->value = [];
            foreach ($arr_data_mm as $value) {
                $form_mm->value[] = $value->PRAR_IDPR;
            }

        }

        // Se le asignan los valores al formulario secundarios
        foreach ($arr_id_secondary as $data_form) {
            $arr_resp = $this->getPrimaryKeyData($data_form['table']);
            if ($arr_resp['error']) {
                return $this->responseController->makeResponse(true, "ERR_DYNFORM_GET009: No se pudo obtener la información de la llave principal.", [], 500);
            }else{
                $primary_key = $arr_resp['response']->COLUMN_NAME;
                try {
                    $arr_data_secondary = (array) DB::table($data_form['table'])->where($primary_key, '=', $data_form['id'])->first();
                } catch (\Throwable $th) {
                    return $this->responseController->makeResponse(true, "ERR_DYNFORM_GET010: No se pudo realizar la consulta a la base.", [], 500);
                }
            }

            // Se asignan los valores obtenidos al formulario
            foreach ($arr_form_secondary as $form_secondary) {
                if ($form_secondary->data_table->table == $data_form['table']) {
                    $column_form = $form_secondary->data_table->column;
                    foreach ($arr_data_secondary as $key => $data_main) {
                        if ($key == $column_form) {
                            $form_secondary->value = $data_main;
                        }
                    }
                    // Se obtienen los datos del select
                    if ($form_secondary->tag == 'select' && $form_secondary->table_rel != null && $form_secondary->column_rel != null) {
                        $nomTab = $form_secondary->table_rel->NOMBRE_TABLA;
                        $column_rel = $form_secondary->column_rel;

                        $data_select = $this->getDataSelect($nomTab,$column_rel);
                        if($data_select['error']){
                            return $this->responseController->makeResponse(true, "ERR_DYNFORM_GET011: No se pudo realizar la consulta a la base.", [], 500);
                        }
                        $form_secondary->data_select = $data_select['response'];
                    }
                }
            }
        }

        foreach ($arr_fields as $key => $fields) {
            foreach ($fields->form as $key => $form) {
                $data_table = $form->data_table;
                foreach ($arr_form_main as $key => $form_main) {
                    $data_table_main = $form_main->data_table;
                    if ($data_table->table == $data_table_main->table && $data_table->column == $data_table_main->column) {
                        $form->value = $form_main->value;
                    }
                }

                foreach ($arr_form_mm as $key => $form_mm) {
                    $data_table_main = $form_mm->data_table;
                    if ($data_table->table == $data_table_main->table && $data_table->column == $data_table_main->column) {
                        $form->value = $form_mm->value;
                    }
                }

                foreach ($arr_form_secondary as $key => $form_secondary) {
                    $data_table_main = $form_secondary->data_table;
                    if ($data_table->table == $data_table_main->table && $data_table->column == $data_table_main->column) {
                        $form->value = $form_secondary->value;
                    }
                }
            }
        }

        $resp = [ 'NOMBRE_FORM' => $name_form, 'FIELDS' => $arr_fields];
        return $this->responseController->makeResponse(false, "ÉXITO", $resp);
    }

    public function registerForm(Request $request) {
        $validator = Validator::make($request->all(), [
            'CODIGO_FORM' => 'required|string',
            'DATA_VALUE' => 'required|string',
            'USER' => 'required|string',
            'LINE' => 'required|string',
        ]);
        if ($validator->fails()) {
            return $this->responseController->makeResponse(
                true,
                "ERR_GENERIC_REG000: Se encontraron uno o más errores.",
                $this->responseController->makeErrors($validator->errors()->messages()),
                401
            );
        }

        $response = $request->all();
        // Se obtiene el número del formulario
        try {
            $numberForm = $this->encryptionController->decrypt($response['CODIGO_FORM']);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(true,"ERR_GENERIC_REG001: Ocurrió al obtener el número de formulario.",[],500);
        }
        // Se obtiene la información a un objeto
        $arrDataValue = json_decode($response['DATA_VALUE']);
        // Se obtiene el usuario
        try {
            $user = $this->encryptionController->decrypt($response['USER']);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(true,"ERR_GENERIC_REG002: Ocurrió un error al obtener el usuario.",[],500);
        }
        // Se obtiene el número de línea
        $line = $response['LINE'];
        try {
            // Se consulta la información del formulario
            $getForm = DB::table('S002V01TFODI')
                ->where('FODI_NUFO', '=', $numberForm)
                ->where('FODI_ESTA', '=', 'Activo')
                ->where('FODI_NULI', '=', $line)
                ->first([ 'FODI_DAFO' ]);

            // Si no encuentra ningún registro, entonces manda un mensaje de error
            if (empty($getForm)) {
                return $this->responseController->makeResponse(true,"ERR_GENERIC_REG003: No se encontró el formulario.",[],500);
            }
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(true,"ERR_GENERIC_REG004: Ocurrió un error en la consulta.",[],500);
        }
        // Se obtiene la información del formulario dinámico
        $arrFields = json_decode($getForm->FODI_DAFO);

        try {
            $mainTable = $this->encryptionController->decrypt($arrFields->table_main);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(true,"ERR_GENERIC_REG005: Ocurrió un error al obtener la tabla principal",[],500);
        }

        // Se obtienen las columnas condicionales
        $arrInformationWhere = $arrFields->info_users_dates;

        // Se iteran según las condicionales
        foreach ($arrInformationWhere as $keyInfo => $informationWhere) {
            // Se obtiene el nombre de la tabla
            $tableNameInformation = $informationWhere->table;
            // Se inicializa el arreglo para la inserción
            $arrInsert = array();

            $codeTable = strtoupper(substr($mainTable, 8, strlen($mainTable)));

            // Se itera la información del formulario
            foreach ($arrDataValue as $key => $dataValue) {
                $tableNameForm = $dataValue->data_table->table;
                // Se verifica que la tabla de las condicionales coincida con la información del formulario
                if ($tableNameForm == $tableNameInformation) {
                    // Se inserta la información de la columna con el valor perteneciente
                    if (array_key_exists('value', (array) $dataValue)) {
                        $arrInsert[$dataValue->column] = $dataValue->value;
                    }else{
                        $arrInsert[$dataValue->column] = false;
                    }
                }

                if ($tableNameForm == 'NA') {
                    $columnTable = $dataValue->data_table->column;
                    $arrInsert[$codeTable.'_INEX'][$columnTable] = $dataValue->value;
                }
            }

            // Se ingresa en la tabla el usuario
            $arrInsert[$informationWhere->user_reg] = $user;
            $arrInsert[$informationWhere->date_reg] = Carbon::now()->timezone('America/Mazatlan')->toDateTimeString();
            $arrInsert[$informationWhere->timestamp] = DB::raw('CURRENT_TIMESTAMP');
            if (array_key_exists($codeTable.'_INEX', $arrInsert)) {
                $arrInsert[$codeTable.'_INEX'] = json_encode($arrInsert[$codeTable.'_INEX']);
            }
            // Se verifica que el arreglo contenga datos
            if (!empty($arrInsert)) {
                try {
                    $response = DB::table($tableNameInformation)->insert($arrInsert);
                    if (!$response) {
                        return $this->responseController->makeResponse(true,"ERR_GENERIC_REG006: El registro no fue exitoso.",[],500);
                    }
                } catch (\Exception $thx) {
                    $message = $thx->getMessage();
                    if (count(explode('Duplicate entry', $message)) == 2) {
                        return $this->responseController->makeResponse(true,"ERR_GENERIC_REG007: El identificador se encuentra duplicado.",[],500);
                    } else {
                        return $this->responseController->makeResponse(true,"ERR_GENERIC_REG008: Ocurrió un error al momento de insertar los registros. $thx",[],500);
                    }
                }
            }
        }
        return $this->responseController->makeResponse(false, "ÉXITO");

    }

    public function updateForm(Request $request) {
        $validator = Validator::make($request->all(), [
            'CODIGO_FORM' => 'required|string',
            'DATA_VALUE' => 'required|string',
            'USER' => 'required|string',
            'LINE' => 'required|string',
        ]);
        if ($validator->fails()) {
            return $this->responseController->makeResponse(
                true,
                "ERR_GENERIC_UPD000: Se encontraron uno o más errores.",
                $this->responseController->makeErrors($validator->errors()->messages()),
                401
            );
        }

        $response = $request->all();
        // Se obtiene el número del formulario
        try {
            $numberForm = $this->encryptionController->decrypt($response['CODIGO_FORM']);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(true,"ERR_GENERIC_UPD001: Ocurrió un error al obtener el número del formulario.",[],500);
        }
        // Se obtiene la información a un objeto
        $arrDataValue = json_decode($response['DATA_VALUE']);
        // Se obtiene el usuario
        try {
            $user = $this->encryptionController->decrypt($response['USER']);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(true,"ERR_GENERIC_UPD002: Ocurrió un error al obtener el usuario.",[],500);
        }
        // Se obtiene el número de línea
        $line = $response['LINE'];

        try {
            // Se consulta la información del formulario
            $getForm = DB::table('S002V01TFODI')
                ->where('FODI_NUFO', '=', $numberForm)
                ->where('FODI_ESTA', '=', 'Activo')
                ->where('FODI_NULI', '=', $line)
                ->first([ 'FODI_DAFO' ]);

            // Si no encuentra ningún registro, entonces manda un mensaje de error
            if (empty($getForm)) {
                return $this->responseController->makeResponse(true,"ERR_GENERIC_UPD003: No se encontró el formulario.",[],500);
            }
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(true,"ERR_GENERIC_UPD004: Ocurrió un error en la consulta.",[],500);
        }


        // Se obtiene la información del formulario dinámico
        $arrFields = json_decode($getForm->FODI_DAFO);
        // Se obtienen las columnas condicionales
        $arrInformationWhere = $arrFields->info_users_dates;

        try {
            $mainTable = $this->encryptionController->decrypt($arrFields->table_main);
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(true,"ERR_GENERIC_UPD005: Ocurrió un error en la consulta.",[],500);
        }

        foreach ($arrInformationWhere as $keyInfo => $informationWhere) {
            // Se obtiene el nombre de la tabla
            $tableNameInformation = $informationWhere->table;
            // Se inicializa el arreglo para la inserción
            $arrUpdate = array();
            // Se inicializa el arreglo para la condicional
            $arrWhere = array();

            $codeTable = strtoupper(substr($mainTable, 8, strlen($mainTable)));

            // Se itera la información del formulario
            foreach ($arrDataValue as $key => $dataValue) {
                $tableNameForm = $dataValue->data_table->table;
                // Se verifica que la tabla de las condicionales coincida con la información del formulario
                if ($tableNameForm == $tableNameInformation) {
                    // Se inserta la información de la columna con el valor perteneciente
                    $arrUpdate[$dataValue->column] = $dataValue->value;
                }
                if ($tableNameForm == 'NA') {
                    $columnTable = $dataValue->data_table->column;
                    $arrUpdate[$codeTable.'_INEX'][$columnTable] = $dataValue->value;
                }
            }
            // Se ingresa en la tabla el usuario
            $arrUpdate[$informationWhere->user_mod] = $user;
            $arrUpdate[$informationWhere->date_mod] = Carbon::now()->timezone('America/Mazatlan')->toDateTimeString();
            $arrUpdate[$informationWhere->timestamp] = DB::raw('CURRENT_TIMESTAMP');
            if (array_key_exists($codeTable.'_INEX', $arrUpdate)) {
                $arrUpdate[$codeTable.'_INEX'] = json_encode($arrUpdate[$codeTable.'_INEX']);
            }

            foreach ($informationWhere->where as $keyInfoWhere => $infoWhere) {
                foreach ($arrDataValue as $key => $dataValue) {
                    if ($infoWhere == $dataValue->column) {
                        $arrWhere[$infoWhere] = $dataValue->value;
                    }
                }
            }
            // Se verifica que el arreglo contenga datos
            if (!empty($arrUpdate)) {
                try {
                    $responseUpdate = DB::table($tableNameInformation)->where($arrWhere)->update($arrUpdate);
                    if (!$responseUpdate) {
                        return $this->responseController->makeResponse(true,"ERR_GENERIC_UPD005: No se encontró el registros selecciondo.",[],500);
                    }
                } catch (\Exception $th) {
                    $message = $th->getMessage();
                    if (count(explode('Duplicate entry', $message)) == 2) {
                        return $this->responseController->makeResponse(true,"ERR_GENERIC_UPD006: El identificador se encuentra duplicado.",[],500);
                    } else {
                        return $this->responseController->makeResponse(true,"ERR_GENERIC_UPD007: Ocurrió un error al momento de insertar los registros. ",[],500);
                    }
                }
            }
        }
        return $this->responseController->makeResponse(false, "ÉXITO: Modificación Exitosa");

    }


    /* ------------------------------------------------------ FUNCIONES ----------------------------------------------------------------------*/

    private function getConditions($conditions, $query, $flag = 0):string {
        foreach ($conditions as $condition){
            $queryAux = "";
            $cond1 = $condition->cond1;
            $cond2 = $condition->cond2;

            $queryAux .= "$cond1->table.$cond1->column $condition->ope ";

            if(!$condition->isCond2Column && !$condition->isCond2Request ){
                $queryAux .= $cond2." ";
            }else if($condition->isCond2Column && !$condition->isCond2Request){
                $queryAux .= "$cond2->table.$cond2->column ";
            }else if($condition->isCond2Request && !$condition->isCond2Column){
                $queryAux .= "";
            }else{
                return "";
            }
            if($condition->conditionAnidada){
                $flag ++;
                $query .= "$condition->operadorLogic ($queryAux";
                return $this->getConditions($condition->conditionAnidada, $query, $flag);
            }else{
                $query .= $condition->operadorLogic." ".$queryAux;
                if ($flag > 0){
                    $query .= str_repeat(")", $flag);
                    $flag = 0;
                }
            }
        }
        return $query;
    }

    private function getTablesName(){
        $databaseName = DB::connection()->getDatabaseName();

        try {
            $resp = DB::select('SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="'.$databaseName.'"');
        } catch (\Throwable $th) {
            return ["error" => true, "msg" => "ERR_GENERIC_REG000: No se pudo realizar la consulta a la base."];
        }

        if ( count($resp) == 0) {
            return ["error" => true, "msg" => "ERR_GENERIC_REG001: Ocurrió un error con obtener los nombres de las tablas."];
        }
        return ["error" => false, "msg" => "ÉXITO", "response" => $resp];
    }

    private function getDataSelect($base_name, $column){
        $pk_data = $this->getPrimaryKeyData($base_name);

        if($pk_data['error']) return ["error" => true, "msg" => $pk_data['msg']];
        $pk = $pk_data['response']->COLUMN_NAME;

        try {
            $resp = DB::table($base_name)->get([ $pk.' AS valor', $column. ' AS opcion' ]);
        } catch (\Throwable $th) {
            return ["error" => true, "msg" => "ERR_GENERIC_REG000: No se pudo realizar la consulta a la base."];
        }
        return ["error" => false, "msg" => "ÉXITO", "response" => $resp];
    }

    private function getPrimaryKeyData($table_name_main){
        $databaseName = DB::connection()->getDatabaseName();
        try {
            $resp = DB::select(DB::raw("SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH AS LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '".$databaseName."' AND TABLE_NAME = '".$table_name_main."' AND COLUMN_KEY = 'PRI'"));
        } catch (\Throwable $th) {
            return ["error" => true, "msg" => "ERR_GENERIC_REG000: No se pudo realizar la consulta a la base."];
        }
        if (count($resp) == 0) {
            return ["error" => true, "msg" => "ERR_GENERIC_REG001: No se pudo realizar la consulta a la base."];
        }

        return ["error" => false, "msg" => "ÉXITO", "response" => $resp[0]];
    }

}