<?php

namespace Predial\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

use Predial\ConceptoPredial;
use Predial\Excensiones;
use Predial\Predio;
use Predial\TipoExcension;

class ExcensionesController extends Controller{

    public $path = "excensiones.";

    public function index($id_unico){
        if(!empty(session()->get('usuario'))){
            $id     = decrypt($id_unico);
            $predio = Predio::find($id);
            $data   = DB::table('gr_excensiones as exc')
                        ->select(
                            'exc.resolucion',
                            DB::raw('DATE_FORMAT(exc.fecha, "%d/%m/%Y") as fecha'),
                            'exc.annoinicial',
                            'exc.annofinal',
                            'exc.porcentajeex',
                            DB::raw('sum(if(dte.concepto = 1, dte.porcentajeex, 0)) as impuesto'),
                            DB::raw('sum(if(dte.concepto = 4, dte.porcentajeex, 0)) as interes'),
                            DB::raw('sum(if(dte.concepto = 3, dte.porcentajeex, 0)) as Cas'),
                            DB::raw('sum(if(dte.concepto = 8, dte.porcentajeex, 0)) as intCas'),
                            DB::raw('sum(if(dte.concepto = 5, dte.porcentajeex, 0)) as descuento'),
                            DB::raw('sum(if(dte.concepto = 6, dte.porcentajeex, 0)) as papeleria'),
                            DB::raw('sum(if(dte.concepto = 7, dte.porcentajeex, 0)) as SobBom')
                        )
                        ->leftJoin('gr_detalle_excensiones as dte', 'dte.excension', '=', 'exc.id_unico')
                        ->leftJoin('gr_concepto as con', 'dte.concepto', '=', 'con.id_unico')
                        ->where('exc.predio', '=', $id)
                        ->groupBy('exc.id_unico')
                        ->get();
            return view($this->path.'index', compact('predio', 'data'));
        }else{
            return redirect("CerrarSesion");
        }
    }

    public function store(Request $request){
        try {
            $id_p  = decrypt($request->txtPredio);
            $ff    = explode("/", $request->txtFecha);
            $fecha = "$ff[2]-$ff[1]-$ff[0]";
            $new   = new Excensiones();
            $new->predio       = $id_p;
            $new->annoinicial  = $request->txtAnnoI;
            $new->annofinal    = $request->txtAnnoF;
            $new->resolucion   = $request->txtResolucion;
            $new->fecha        = $fecha;
            $new->porcentajeex = $request->txtPorcentaje;
            $new->save();
            $conceptos = $request->chkConcepto;
            $xda = ExcensionesController::obtnerRegistroExcension($new);
            if(!empty($xda)){
                for ($i = 0; $i < count($conceptos); $i++) {
                    DB::table('gr_detalle_excensiones')->insert(
                        ['concepto' => $conceptos[$i], 'excension' => $xda->id_unico, 'porcentajeex' => $new->porcentajeex]
                    );
                }
            }
            flash()->overlay('Información guardada correctamente', 'Información');
            return back();
        } catch (Exception $e) {
            return "Fatal Error = ".$e->getMessage();
        }
    }

    public static function ExcencionCabezaFactura($predio, $anno, $concepto, $valor){
        $data = DB::table('gr_excensiones as exc')
                    ->leftJoin('gr_detalle_excensiones as  dte', 'dte.excension', '=', 'exc.id_unico')
                    ->where('exc.predio', '=', $predio)
                    ->where('exc.annoinicial', '<=', $anno)
                    ->where('exc.annofinal', '>=', $anno)
                    ->where('dte.concepto', '=', $concepto)
                    ->first();
        if(empty($data)){
            $xxx = $valor;
        }else{
            $xxx = ($valor * $data->porcentajeex) / 100;
            $xxx = $valor - $xxx;
        }
        return $xxx;
    }

    public static function obtnerRegistroExcension($new){
        try{
            $data = DB::table('gr_excensiones')
                        ->select('id_unico')
                        ->where('resolucion', '=', $new->resolucion)
                        ->where('annoinicial', '=', $new->annoinicial)
                        ->where('annofinal', '=', $new->annofinal)
                        ->where('fecha', '=', $new->fecha)
                        ->where('porcentajeex', '=', $new->porcentajeex)
                        ->where('predio', '=', $new->predio)
                        ->first();
            return $data;
        }catch (Exception $e){
           return $e->getMessage();
        }
    }
}
