SistemaMantenimiento/sistema-mantenimiento-back/app/Http/Controllers/TableController.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 TableController extends Controller
{
    private $responseController;
    private $encController;
    public function __construct(){
        $this->responseController = new ResponseController();
        $this->encController = new EncryptionController();

    }

    public function getTables($line){
        try {
            $data = DB::table('S002V01TTABL')
                ->where('TABL_NULI', '=', $line)
                ->get([
                    'TABL_IDTA AS CODIGO_TABLA',
                    'TABL_NOMB AS NOMBRE_TABLA',
                    'TABL_FERE AS FECHA_REGISTRA',
                    'TABL_FEMO AS FECHA_MODIFICA',
                    'TABL_USRE AS USUARIO_REGISTRA',
                    'TABL_USMO AS USUARIO_MODIFICA',
                    'TABL_ESTA AS ESTADO'
                ]);
        }catch (\Exception $e){
            return $this->responseController->makeResponse(true, "ERR_TABLE_GET000: Ocurrió un error al consultar los datos.", [], 500);
        }
        return $this->responseController->makeResponse(false, "ÉXITO", $data);
    }

    public function getColumnsByTables(Request $request){
        $validator = Validator::make($request->all(), [
            'tables' => 'required|array',
        ]);

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

        try {
            $form = $request->all();
            $resp = array();
            foreach ($form['tables'] as $table) {
                $data = array();
                $data['table'] = $table;
                $data['columns'] = [];

                $column_response = $this->getColumns(strtolower( $table));
                if ( $column_response['error']) return $this->responseController->makeResponse(false, $column_response['msg']);
                $columns = $column_response['response'];

                foreach ($columns as $column) {
                    $nameColumn = explode('_', $column->COLUMN_NAME)[1];
                    if ($nameColumn !== 'INEX'){
                        $data['columns'][] = $column->COLUMN_NAME;
                    }
                }
                $resp[] = $data;
            }
        } catch (\Throwable $th) {
            return $this->responseController->makeResponse(true, "ERR_TABLE_GET000: Ocurrió un error al consultar los datos.", [], 500);
        }

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

    public function getColumnsExtra(Request $request) {
        $validator = Validator::make($request->all(), [
            'numberForm' => 'required|integer',
            'tables' => 'required|array',
        ]);

        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();

        $data = array();
        $data['table'] = 'NA';
        $data['type'] = 'JSON';

        $getColumnsExtra = DB::table('S002V01TFODI')->where('FODI_NUFO', '=', $form['numberForm'])->first('FODI_DAFO');
        $arrFields = json_decode($getColumnsExtra->FODI_DAFO);
        foreach ($arrFields->fields  as $keyFields => $fields) {
            foreach ($fields->form as $keyForm => $form) {
                if ($form->data_table->table == 'NA') {
                    $data['columns'][] = $form->data_table->column;
                }
            }
        }
        return $this->responseController->makeResponse(false, "ÉXITO", $data);
    }

    public function createTable(Request $request){

        $validator = Validator::make($request->all(), [
            'table_name' => 'required|string',
            'user' => 'required|string'
        ]);
        if ($validator->fails()) {
            return $this->responseController->makeResponse(
                true,
                "ERR_TABLE_REG000: Se encontraron uno o más errores.",
                $this->responseController->makeErrors($validator->errors()->messages()),
                401
            );
        }
        DB::beginTransaction();
        $request_form = $request->all();
        $table_name = $this->encController->decrypt($request_form['table_name']);
        try {
            $user = $this->encController->decrypt($request_form['user']);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_TABLE_REG001: No se pudo obtener el usuario.", [], 500);
        }
        $reg = [
            "TABL_NULI" => 1,
            "TABL_NOMB" => $table_name,
            "TABL_USRE" => $user,
            "TABL_FERE" => Carbon::now()->timezone('America/Mazatlan')->toDateTimeString(),
            "TABL_FEAR" => DB::raw('CURRENT_TIMESTAMP')
        ];
        try {
            $validateInsert = DB::table('S002V01TTABL')->insert($reg);
            if (!$validateInsert) {
                return $this->responseController->makeResponse(true, "ERR_TABLE_REG002: No se pudo realizar la inserción en la base.", [], 500);
            }
        }catch (\PDOException $e){
            if($e->getCode() == 23000){
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_TABLE_REG003: La tabla ya ha sido registrada anteriormente.", [], 500);
            }else{
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_TABLE_REG004: Ocurrió un error al consultar los datos.", [], 500);
            }
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_TABLE_REG005: Ocurrió un error al consultar los datos.", [], 500);
        }
        DB::commit();
        return $this->responseController->makeResponse(false, "ÉXITO: Registro correcto");
    }

    public function updateTable(Request $request){
        $validator = Validator::make($request->all(), [
            'table_code' => 'required|string',
            'table_name' => 'required|string',
            'user' => 'required|string'
        ]);
        if ($validator->fails()) {
            return $this->responseController->makeResponse(
                true,
                "ERR_TABLE_UPD000: Se encontraron uno o más errores.",
                $this->responseController->makeErrors($validator->errors()->messages()),
                401
            );
        }
        DB::beginTransaction();
        $request_form = $request->all();
        $table_name = $this->encController->decrypt($request_form['table_name']);
        try {
            $user = $this->encController->decrypt($request_form['user']);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_TABLE_UPD001: No se pudo obtener el usuario.", [], 500);
        }

        try {
            $data_table = DB::table('S002V01TTABL')->where('TABL_IDTA', '=', $request_form['table_code'])->first();
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_TABLE_UPD002: Ocurrió un error al obtener los datos de la base.", [], 500);
        }
        if (empty($data_table)) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_TABLE_UPD003: El nombre de la tabla no existe.", [], 500);
        }

        $upd = [
            "TABL_NOMB" => $table_name,
            "TABL_USMO" => $user,
            "TABL_FEMO" => Carbon::now()->timezone('America/Mazatlan')->toDateTimeString(),
            "TABL_FEAR" => DB::raw('CURRENT_TIMESTAMP')
        ];
        try{
            $validateUpdate = DB::table('S002V01TTABL')->where('TABL_IDTA', '=', $request_form['table_code'])->update($upd);
            if (!$validateUpdate) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_TABLE_UPD004: No se pudo realizar la inserción en la base.", [], 500);
            }
        }catch (\PDOException $e){
            if($e->getCode() == 23000){
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_TABLE_UPD005: La tabla ya ha sido registrada anteriormente.", [], 500);
            }else{
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_TABLE_UPD006: Ocurrió un error al consultar los datos.", [], 500);
            }
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_TABLE_UPD007: Ocurrió un error al consultar los datos.", [], 500);
        }

        DB::commit();
        return $this->responseController->makeResponse(false, "ÉXITO: Modificación correcta");
    }

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

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

        try{
            $valid_tb = DB::table('S002V01TTABL')
                ->where('TABL_NOMB', $form['table_name'])
                ->first();
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_TABLE_UPD001: Ocurrió un error al hacer la consulta en la base.", [], 500);
        }
        if (empty($valid_tb)) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_TABLE_UPD002: El nombre de la tabla ".$form['table_name']." no existe.", [], 500);
        }

        try {
            $user = $this->encController->decrypt($form['user']);
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_TABLE_UPD003: No se pudo obtener el usuario.", [], 500);
        }
        $upd = [
            "TABL_ESTA" => "",
            "TABL_USMO" => $user,
            "TABL_FEMO" => Carbon::now()->timezone('America/Mazatlan')->toDateTimeString(),
            "TABL_FEAR" => DB::raw('CURRENT_TIMESTAMP')
        ];
        switch ($form['state']){
            case "delete":
                $upd["TABL_ESTA"] = "Eliminado";
                break;
            case "active":
                $upd["TABL_ESTA"] = "Activo";
                break;
            default:
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_TABLE_UPD004: No ha elegido una opción correcta para cambiar el estado", [], 500);
        }

        try{
            $validateUpdate = DB::table('S002V01TTABL')->where('TABL_NOMB', $form['table_name'])->update($upd);
            if (!$validateUpdate) {
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_TABLE_UPD005: No se pudo realizar la modificación en la base.", [], 500);
            }

        }catch (\PDOException $e){
            if($e->getCode() == 23000){
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_TABLE_UPD005: La tabla ya ha sido registrada anteriormente.", [], 500);
            }else{
                DB::rollBack();
                return $this->responseController->makeResponse(true, "ERR_TABLE_UPD006: Ocurrió un error al consultar los datos.", [], 500);
            }
        } catch (\Throwable $th) {
            DB::rollBack();
            return $this->responseController->makeResponse(true, "ERR_TABLE_UPD007: Ocurrió un error al consultar los datos.", [], 500);
        }

        DB::commit();
        return $this->responseController->makeResponse(false, "ÉXITO: Modificación correcta");
    }

    /* --------------------------------------- FUNCIONES ---------------------------------------------- */
    private function getColumns($table_name){

        $tables_name = $this->getTablesName();

        if ( $tables_name['error']) {
            return $tables_name;
        }
        $tables = $tables_name['response'];

        $valid_table = false;
        foreach ($tables as $table) {
            if ($table->TABLE_NAME == $table_name){
                $valid_table = true;
                break;
            }
        }
        if (!$valid_table) {
            return ["error" => true, "msg" => "ERR_DYNTABLE_REG000: No se encontró la tabla en la base de datos."];
        }

        $databaseName = DB::connection()->getDatabaseName();
        $qry = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '".$databaseName."' AND TABLE_NAME = '".$table_name."'";

        try{
            $data_columns = DB::select($qry);
        }catch (\Exception $e){
            return ["error" => true, "msg" => "ERR_DYNTABLE_REG000: No se pudo realizar la consulta a la base."];
        }

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

    private function getTablesName(){
        $databaseName = DB::connection()->getDatabaseName();
        try{
            $resp = DB::select('SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="'.$databaseName.'"');
        }catch (\Exception $e){
            return ["error" => true, "msg" => "ERR_DYNTABLE_REG000: Ocurrió un error al obtener el nombre de las tablas."];
        }
        if ( count((array)$resp) == 0) {
            return ["error" => true, "msg" => "ERR_DYNTABLE_REG000: No se pudo obtener el nombre de las tablas."];
        }
        return ["error" => false, "msg" => "ÉXITO", "response" => $resp];
    }

}