<?php

namespace Predial\Http\Controllers;

use Illuminate\Http\Request;
use Predial\Predio;
use Predial\PropietarioPredio;
use Predial\FacturaPredial;
use Predial\PagoPredial;
use Illuminate\Support\Facades\DB;
use DateTime;
use PDF;
use Predial\ParametrizacionAnio;

class PazySalvoInfController extends Controller
{
    public function index(){
        return view('informesPredial.generarPazySalvo');
    }

    public function view_inf(Request $request){

        $id = $request->txtId;
        $NomS = ($request->txtNom);
    	$compania = DB::table('gf_tercero as ter')
                            ->select( DB::raw('UPPER(ter.razonsocial) as razonsocial'),
                                      DB::raw('UPPER(ti.nombre) as tipodoc'),
                                      DB::raw('CONCAT_WS(" - ",ter.numeroidentificacion,ter.digitoverficacion) as numeroi'),
                                      'dir.direccion', 'tel.valor as telefono','ter.ruta_logo as logo'
                              )
                            ->leftJoin('gf_tipo_identificacion as ti' ,'ter.tipoidentificacion', '=', 'ti.id_unico')
                            ->leftJoin('gf_direccion as dir', 'dir.tercero', '=', 'ter.id_unico')
                            ->leftJoin('gf_telefono as tel', 'tel.tercero', '=', 'ter.id_unico')
                            ->where('ter.id_unico', '=', session()->get('compania'))
                            ->first();

        $avaluos = DB::table('gr_avaluo as av')
                    ->select('av.id_unico as avaluo')
                    ->where('av.predio', '=', $id)
                    ->get();

        foreach ($avaluos as $value) {

            $totalpago = 0;
            $totalfac  = 0;
            $datapago = DB::table('gr_detalle_pago_predial as dtp')
                        ->select(

                            DB::raw('SUM(dtp.valor) as tp')

                        )
                        ->leftJoin('gr_detalle_factura_predial as dfp', 'dtp.detallefactura', '=', 'dfp.id_unico')
                        ->where('dfp.avaluo', '=', $value->avaluo)
                        ->get();


            foreach ($datapago as  $vpago) {
                $totalpago += $vpago->tp;
            }

            $datafac = DB::table('gr_detalle_factura_predial as dtf')
                        ->select(

                            DB::raw('SUM(dtf.valor) as tf')

                        )
                        ->where('dtf.avaluo', '=', $value->avaluo)
                        ->get();

            foreach ($datafac as  $vfac) {
                $totalfac += $vfac->tf;
            }

        }

        $total = (string)$totalfac - (string)$totalpago;

        if($total == 0 && $totalfac == 0){

            $anno = session()->get('anno');
            $vige = ParametrizacionAnio::find($anno);


            $predio = DB::table('gp_predio1 as pre')
                            ->select('pre.codigo_catastral as codc',
                                     'pre.direccion as dir',
                                      'p.nombres as prop'  ,

                                      'ba.nombre as nom',
                                      'pre.inicio_vigencia as iniv'
                                    )
                            ->leftjoin('gp_tercero_predio as tp', 'tp.predio', '=', 'pre.id_unico')
                            ->leftjoin('gr_propietarios as  p', 'tp.tercero', '=', 'p.id_unico')
                            ->leftjoin('gp_barrio as ba', 'pre.barrio', '=', 'ba.id_unico')
                            ->where('pre.id_unico', '=', $id)
                            ->first();



            if($vige->anno < $predio->iniv){

                    $Uava = DB::table('gr_avaluo as uav')
                                ->select(DB::raw('MAX(uav.id_unico) as id'),
                                            'uav.valor as valp',
                                            'uav.anno as anioa'
                                        )
                                ->where('uav.predio', '=', $id)
                                ->first();
                    $valA = DB::table('gr_area as a')
                                    ->select(DB::raw('MAX(a.id_unico) as id'),
                                            'a.valor as vala',
                                             'ua.nombre as noma'
                                            )
                                    ->leftjoin('gr_unidad_area as ua', 'a.unidadarea', '=', 'ua.id_unico')
                                    ->where('a.anio', '=', $Uava->anioa)
                                    ->where('a.predio', '=', $id)
                                    ->first();

                    $cooprop = DB::table('gp_predio1 as p')
                                    ->select('pr.nombres as prop'
                                            )
                                    ->leftjoin('gp_tercero_predio as tp', 'tp.predio', '=', 'p.id_unico')
                                    ->leftjoin('gr_propietarios as  pr', 'tp.tercero', '=', 'pr.id_unico')
                                    ->where('p.id_unico', '=', $id)
                                    ->where('tp.propietario', '!=', '0')
                                    ->first();

                    $hoy = date('d-m-Y');
                    $hoy = trim($hoy, '"');
                    $fecha_divi = explode("-", $hoy);
                    $anioA = $fecha_divi[2];
                    $mes = (int)$fecha_divi[1];
                    $dia = $fecha_divi[0];
                    $nombremes = ['no','Enero','Febrero','Marzo','Abril','Mayo','Junio','Julio','Agosto','Septiembre','Octubre','Noviembre','Diciembre'];

                    $mesA= $nombremes[$mes];
                    $paz = "Paz y Salvo Predial";
                    echo $firma = DB::table('gf_responsable_documento as rd')
                                ->select(DB::raw('IF(CONCAT_WS(" ",
                                                        t.nombreuno,
                                                        t.nombredos,
                                                        t.apellidouno,
                                                        t.apellidodos
                                                    ) = " ",
                                                    t.razonsocial,
                                                    CONCAT_WS(" ",
                                                        t.nombreuno,
                                                        t.nombredos,
                                                        t.apellidouno,
                                                        t.apellidodos
                                                    )
                                                ) as res'),
                                                'tr.nombre as nomt',
                                                'tre.nombre as ntro')
                                ->leftjoin('gf_tercero as t','rd.tercero', '=', 't.id_unico')
                                ->leftjoin('gf_tipo_documento as td','rd.tipodocumento', '=', 'td.id_unico')
                                ->leftjoin('gf_tipo_responsable as tr','rd.tiporesponsable', '=', 'tr.id_unico')
                                ->leftjoin('gg_tipo_relacion as  tre','rd.tipo_relacion', '=', 'tre.id_unico')
                                ->where('td.nombre', '=', $paz)
                                ->get();
                    $In = DB::table('gr_consecutivo_cert')
                            ->insert(['consecutivo'=> $cons, 'predio'=>$id,'nombre'=>$NomS,'tipo'=>2]);

                    $view  = \View::make('informesPredial.infResolucion', compact(['compania','predio','Uava','valA','cooprop','anioA','mesA','dia','firma','vige','cons']))->render();
                    $pdf   = \App::make('dompdf.wrapper');
                    //$pdf->setPaper("letter","landscape");
                    $pdf->loadHTML($view);
                    return $pdf->stream("Paz_y_Salvo.pdf");

            }else{
                    flash()->overlay('El Predio no se encuentra a Paz y Salvo', 'Información');
                    return back();
            }

        }elseif($total == 0 && $totalfac != 0){
            $predio = DB::table('gp_predio1 as pre')
                            ->select('pre.codigo_catastral as codc',
                                     'pre.direccion as dir',
                                      'p.nombres as prop'  ,

                                      'ba.nombre as nom',
                                      'pre.inicio_vigencia as iniv'
                                    )
                            ->leftjoin('gp_tercero_predio as tp', 'tp.predio', '=', 'pre.id_unico')
                            ->leftjoin('gr_propietarios as  p', 'tp.tercero', '=', 'p.id_unico')
                            ->leftjoin('gp_barrio as ba', 'pre.barrio', '=', 'ba.id_unico')
                            ->where('pre.id_unico', '=', $id)
                            ->first();

            $Uava = DB::table('gr_avaluo as uav')
                                ->select(DB::raw('MAX(uav.id_unico) as id'),
                                            'uav.valor as valp',
                                            'uav.anno as anioa'
                                        )
                                ->where('uav.predio', '=', $id)
                                ->first();
                    $valA = DB::table('gr_area as a')
                                    ->select(DB::raw('MAX(a.id_unico) as id'),
                                            'a.valor as vala',
                                             'ua.nombre as noma'
                                            )
                                    ->leftjoin('gr_unidad_area as ua', 'a.unidadarea', '=', 'ua.id_unico')
                                    ->where('a.anio', '=', $Uava->anioa)
                                    ->where('a.predio', '=', $id)
                                    ->first();

                    $cooprop = DB::table('gp_predio1 as p')
                                    ->select('pr.nombres as prop'
                                            )
                                    ->leftjoin('gp_tercero_predio as tp', 'tp.predio', '=', 'p.id_unico')
                                    ->leftjoin('gr_propietarios as  pr', 'tp.tercero', '=', 'pr.id_unico')
                                    ->where('p.id_unico', '=', $id)
                                    ->where('tp.propietario', '!=', '0')
                                    ->get();

                    $hoy = date('d-m-Y');
                    $hoy = trim($hoy, '"');
                    $fecha_divi = explode("-", $hoy);
                    $anioA = $fecha_divi[2];
                    $mes = (int)$fecha_divi[1];
                    $dia = $fecha_divi[0];
                    $nombremes = ['no','Enero','Febrero','Marzo','Abril','Mayo','Junio','Julio','Agosto','Septiembre','Octubre','Noviembre','Diciembre'];

                    $mesA= $nombremes[$mes];
                    $paz = "Paz y Salvo Predial";
                    echo $firma = DB::table('gf_responsable_documento as rd')
                                ->select(DB::raw('IF(CONCAT_WS(" ",
                                                        t.nombreuno,
                                                        t.nombredos,
                                                        t.apellidouno,
                                                        t.apellidodos
                                                    ) = " ",
                                                    t.razonsocial,
                                                    CONCAT_WS(" ",
                                                        t.nombreuno,
                                                        t.nombredos,
                                                        t.apellidouno,
                                                        t.apellidodos
                                                    )
                                                ) as res'),
                                                'tr.nombre as nomt',
                                                'tre.nombre as ntro')
                                ->leftjoin('gf_tercero as t','rd.tercero', '=', 't.id_unico')
                                ->leftjoin('gf_tipo_documento as td','rd.tipodocumento', '=', 'td.id_unico')
                                ->leftjoin('gf_tipo_responsable as tr','rd.tiporesponsable', '=', 'tr.id_unico')
                                ->leftjoin('gg_tipo_relacion as  tre','rd.tipo_relacion', '=', 'tre.id_unico')
                                ->where('td.nombre', '=', $paz)
                                ->get();

                    $B = $vige->anno;
                    echo $consec = DB::table('gr_consecutivo_cert as cc')
                                ->select(DB::raw('MAX(cc.id_unico) as idcc'))
                                ->select('cc.consecutivo as co')
                                ->where('cc.tipo', '=', 2)
                                ->where('cc.consecutivo', 'like', "$B%")
                                ->get();

                    if(count($consec)<=0){

                        $cons = $vige->anno."0001";
                    }else{

                        foreach ($consec as $C) {
                            $E = substr($C->co,-4);
                        }

                        $num = (int) $E;
                        $num = $num + 1;

                        $m = substr($C->co,-8,4);
                        $cant = strlen($num);
                        if($cant < 4){
                            $c="";
                            for($i = $cant; $i <  4; $i++){
                                $c = $c."0";
                            }

                            $cons = $m.$c.$num;
                        }else{
                            $cons = $m.$num;
                        }



                    }
                    $In = DB::table('gr_consecutivo_cert')
                            ->insert(['consecutivo'=> $cons, 'predio'=>$id,'nombre'=>$NomS,'tipo'=>2]);

                    $view  = \View::make('informesPredial.infResolucion', compact(['compania','predio','Uava','valA','cooprop','anioA','mesA','dia','firma','vige','cons']))->render();
                    $pdf   = \App::make('dompdf.wrapper');
                    //$pdf->setPaper("letter","landscape");
                    $pdf->loadHTML($view);
                    return $pdf->stream("Paz_y_Salvo.pdf");


        }else{
            flash()->overlay('El Predio no se encuentra a Paz y Salvo', 'Información');
            return back();
        }

    }
}
