<?php

namespace App\Http\Controllers\Web;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Crypt;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Str;
use App\Models\ExpedienteRequisito;
use App\Models\Tramite;

class SeguimientoController extends Controller
{
    public function index(){
        return view("web.seguimiento.index");
    }
    public function validar(Request $request){
        // dd($request);
        $expediente = DB::table("expediente as e")
                ->where("solicitud_codigo",$request->codigo)
                ->where("password",$request->password)
                ->first();
        if($expediente){
            $response["status"] = true;
            $response["d"] = Crypt::encryptString($expediente->id);
        }else{
            $response["status"] = false;
            $response["message"] = "El codigo de solicitud o contraseña son incorrectos.";
        }
        return $response;
    }
    public function expediente($id){
        $ide = Crypt::decryptString($id);
        // dd($ide);
        $response["expediente"] = DB::table("expediente as e")
                ->select(
                    "e.id as RowId",
                    "e.solicitud_codigo as SolicitudCodigo",
                    "e.codigo as CodigoExpediente",
                    "e.numero_documento as NumeroDocumento",
                    DB::raw("DATE_FORMAT(e.created_at,'%d/%m/%Y %h:%i %p') as FechaReg"),
                    "e.asunto as Asunto",
                    "e.folio as Folio",
                    // "e.comentario as Comentario",
                    "e.observacion as Observacion",
                    DB::raw("CASE 
                            WHEN e.estado = 0 THEN 'Pendiente'
                            WHEN e.estado = 1 THEN 'En tramite'
                            WHEN e.estado = 2 THEN 'Rechazado'
                            WHEN e.estado = 3 THEN 'Finalizado'
                            ELSE 'Pendiente'
                            END AS Estado"),
                    "e.estado",
                    "e.tipo_entidad as TipoEntidad",

                    "td.nombre as TipoDocumento",
                    DB::raw("CONCAT(en.documento,' | ',en.paterno,' ',en.materno,' ',en.nombres) as Responsable"),
                    "en.telefono as Telefono",
                    "en.email as Email",
                    "en.direccion as Direccion",
                    "en.id_ubigeo as Ubigeo",
                    "en.extranjero as Extranjero",
                    "en.pais as Pais",

                    "em.ruc as Ruc",
                    "em.razon_social as RazonSocial",
                    "em.direccion as DireccionEmpresa",

                    DB::raw("CASE 
                        WHEN p.tipo = 1 THEN 'Tupa'
                        WHEN p.tipo = 2 THEN 'No Tupa'
                        WHEN p.tipo = 3 THEN 'Otro'
                        ELSE 'Otro'
                        END AS TipoExpediente"),
                    "p.nombre as Proceso",
                    // DB::raw("CONCAT(em.ruc,' | ',em.empresa) as Empresa"),
                    // "em.direccion as DireccionEmpresa",

                    DB::raw('"" as Botones')
                )
                ->join("entidad as en","en.id","e.id_entidad")
                ->leftJoin("empresa as em","em.id","e.id_empresa")
                ->join("proceso as p","p.id","e.id_proceso")
                // ->join("servicio as s","s.id","p.id_servicio")
                // ->join("clase as c","c.id","p.id_clase")
                ->join("tipo_documento as td","td.id","en.id_tipo_documento")
                // ->leftJoin("expediente_tipo as et","et.id","e.id_expediente_tipo")
                ->where("e.id",$ide)
                ->first();
        
        if($response["expediente"]->Extranjero==0){           
            $response['distrito'] = DB::table('ubigeo')
                                ->select('nombre','provincia','departamento')
                                ->where('id',$response["expediente"]->Ubigeo)
                                ->first();

            $response['provincia'] = DB::table('ubigeo')
                            ->select('nombre')
                            ->where('provincia',$response['distrito']->provincia)
                            ->where('departamento',$response['distrito']->departamento)
                            ->where('distrito','0')
                            ->first();

            $response['departamento'] = DB::table('ubigeo')
                            ->select('nombre')
                            ->where('departamento',$response['distrito']->departamento)
                            ->where('provincia','0')
                            ->where('distrito','0')
                            ->first();
        } 

        $response["requisitos"] = DB::table("expediente_requisito as er")
                        ->select("r.nombre","er.*")
                        ->leftJoin("requisito as r","r.id","er.id_requisito")
                        ->where("er.id_expediente",$ide)
                        ->orderby("er.tipo","asc")
                        ->orderby("er.observado","asc")
                        // ->where("ea.tipo",0)
                        ->get();

        $observado = 0;
        $tramite = DB::table("tramite")
                    ->where("id_expediente",$ide)
                    ->where("estado",6)
                    ->first();
        if($tramite){
            $observado = 1;
        }
        $estado = 0;
        foreach ($response["requisitos"] as $key => $value) {
            if($value->tipo==1||$value->tipo==2){
                if($value->estado==0){
                    $estado=1;
                }
            }
        }

        if($observado==1&&$estado==0){
            $tramite = Tramite::find($tramite->id);
            $tramite->estado = 0;
            $tramite->save();
        }
        $response["estado"] = $estado;
        $response["observado"] = $observado;
        return view("web.seguimiento.expediente",$response);
    }
    public function store_archivo(Request $request,$id){
        // dd($request);
        DB::beginTransaction();
        try {
            $req = DB::table("expediente_requisito as er")
                            ->select("r.id","er.tipo","r.nombre","er.id_expediente")
                            ->join("requisito as r","r.id","er.id_requisito")
                            ->where("er.id",$id)
                            ->first();
            $entidad = DB::table("expediente as e")
                        ->select("en.documento")
                        ->join("entidad as en","en.id","e.id_entidad")
                        ->where("e.id",$req->id_expediente)
                        ->first();
            // dd($entidad);

            $nombreArchivo = Str::slug($req->nombre,'-').'-'.time().'.pdf';
            $ruta = '/'.date('Y').'/'.date("m").'/'.date("d").'/'.$entidad->documento;
            $path = $ruta."/".$nombreArchivo;
            $archivo = $request->archivo;

            Storage::disk('expediente')->putFileAs($ruta, $archivo, $nombreArchivo);

            $requisito = ExpedienteRequisito::find($id);
            $requisito->enlace = $path;
            // $requisito->id_requisito = $req->id;
            // $requisito->id_expediente = $expediente->id;
            $requisito->estado = 1;
            $requisito->save();

            DB::commit();
            $message = 'Archivo subido.';
            $status = true;
        } catch (\Exception $e) {
            DB::rollback();
            $message = 'Error al Subir. Intente otra vez.';
            $status = false;
        }
        $response["status"] = $status;
        $response["message"] = $message;
        return $response;
    }
    public function seguimiento($id){
        $response["tramite"] = DB::table("tramite as t")
                ->select(
                    "o.nombre as Oficina",
                    DB::raw("IFNULL(od.nombre, '') as Destino"),
                    DB::raw("CASE 
                            WHEN t.estado = 0 THEN 'Pendiente'
                            WHEN t.estado = 1 THEN 'Recibido'
                            WHEN t.estado = 2 THEN 'Derivado'
                            WHEN t.estado = 3 THEN 'Rechazado'
                            WHEN t.estado = 4 THEN 'Finalizado'
                            WHEN t.estado = 5 THEN 'Archivado'
                            WHEN t.estado = 6 THEN 'Observado'
                            ELSE ''
                            END AS Estado"),
                    "t.estado",
                    DB::raw("IFNULL(t.observacion,'') as Observacion")
                )
                ->join("oficina as o","o.id","t.id_oficina")
                ->leftJoin("oficina as od","od.id","t.id_oficina_destino")
                ->where("t.id_expediente",$id)
                ->where("t.copia",0)
                ->orderBy("t.id","asc")
                ->get();
        return $response;
    }
}
