<?php

namespace Predial\Http\Controllers;

use Illuminate\Http\Request;
use Predial\DetalleFacturaPredial;
use Illuminate\Support\Facades\DB;
use Fpdf;
class PrescritoController extends Controller{

    public static function index(){
        return view("prescripcion.informe");
    }

    public static function generateInfPdf(Request $request){
        $ff = explode("/", $request->txtFecha);
        $fecha = "$ff[2]-$ff[1]-$ff[0]";
        $compania = ListadoPagosController::obtenerCompania();
        return view("prescripcion.pdf", compact("fecha", "compania"));
    }

    public static function obtenerPrimerAnnoFactura($factura){
        $data = DB::table('gr_detalle_factura_predial as gdf')
                    ->leftJoin('gr_avaluo as gra', 'gdf.avaluo', '=', 'gra.id_unico')
                    ->where('gdf.factura', '=', $factura)
                    ->min('gra.anno');
        return $data;
    }

    public static function obtenerUltimoAnnoFactura($factura){
        $data = DB::table('gr_detalle_factura_predial as gdf')
            ->leftJoin('gr_avaluo as gra', 'gdf.avaluo', '=', 'gra.id_unico')
            ->where('gdf.factura', '=', $factura)
            ->max('gra.anno');
        return $data;
    }

    public static function obtenerCodigoResolucion($predio){
        $data = DB::table('gr_consecutivo_cert')->where('predio', '=', $predio)->where('tipo', '=', 3)->max('consecutivo');
        return $data;
    }

    public static function generateInfExl(Request $request){
        $ff = explode("/", $request->txtFecha);
        $fecha = "$ff[2]-$ff[1]-$ff[0]";
        $compania = ListadoPagosController::obtenerCompania();
        $data = DB::table('gr_pago_predial as gpp')
            ->leftJoin('gr_detalle_pago_predial as gdp', 'gdp.pago', '=', 'gpp.id_unico')
            ->leftJoin('gr_detalle_factura_predial as gdf', 'gdp.detallefactura', '=', 'gdf.id_unico')
            ->leftJoin('gr_factura_predial as gfp', 'gdf.factura', '=', 'gfp.id_unico')
            ->leftJoin('gp_predio1 as gpr', 'gfp.predio', '=', 'gpr.id_unico')
            ->leftJoin('gp_tercero_predio as gtp', 'gtp.predio', '=', 'gpr.id_unico')
            ->leftJoin('gr_propietarios as gps', 'gtp.tercero', '=', 'gps.id_unico')
            ->leftJoin('gr_avaluo as gra', 'gdf.avaluo', '=', 'gra.id_unico')
            ->select(
                'gpr.codigo_catastral as codCas', 'gps.nombres as nompr', 'gpr.direccion as dirpr',
                DB::raw('DATE_FORMAT(gpp.fechapago, "%d/%m/%Y") as fecha'), 'gfp.id_unico as factura',
                'gpr.id_unico as predio'
            )
            ->where('gpp.tipopago', '=', 6)
            ->where('gtp.orden', '=', '001')
            ->where('gpp.fechapago', '<', $fecha)
            ->groupBy('gpp.id_unico')
            ->orderBy('gpr.codigo_catastral', 'ASC')
            ->orderBy('gpp.fechapago')
            ->get();
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="ListadoPrescripciones.xls"');
        $html = "<!doctype html>";
        $html .= "<html lang=\"en\">";
        $html .= "<head>";
        $html .= "<meta charset=\"UTF-8\">";
        $html .= "<meta name=\"viewport\" content=\"width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0\">";
        $html .= "<meta http-equiv=\"X-UA-Compatible\" content=\"ie=edge\">";
        $html .= "<title>Informe de Prescripciones</title>";
        $html .= "</head>";
        $html .= "<body>";
        $html .= "<table style='width: 100%;'>";
        $html .= "<thead>";
        $html .= "<tr>";
        $html .= "<th style='border: solid #000 1px;' colspan='7'>$compania->razonsocial<br/>NIT $compania->numeroi<br/>".utf8_decode("INFORME DE PRESCRIPCIONES")."</th>";
        $html .= "</tr>";
        $html .= "<th style='border: solid #000 1px;'>CODIGO<br/>CATRASTRAL</th>";
        $html .= "<th style='border: solid #000 1px;'>PROPIETARIO</th>";
        $html .= "<th style='border: solid #000 1px;'>DIRECCION<br/>PREDIO</th>";
        $html .= "<th style='border: solid #000 1px;'>NRO<br/>RESOLUCION</th>";
        $html .= "<th style='border: solid #000 1px;'>FECHA</th>";
        $html .= "<th style='border: solid #000 1px;'>VALOR</th>";
        $html .= "<th style='border: solid #000 1px;'>AÑOS</th>";
        $html .= "</thead>";
        $html .= "<tbody>";
        foreach ($data as $row){
            $valor   = DetalleFacturaPredial::obtnerValorTotal($row->factura);
            $minAnno = PrescritoController::obtenerPrimerAnnoFactura($row->factura);
            $maxAnno = PrescritoController::obtenerUltimoAnnoFactura($row->factura);;
            $xconsec = PrescritoController::obtenerCodigoResolucion($row->predio);
            $html .= "<tr>";
            $html .= "<td style='border: solid #000 1px; text-align: right;'>'$row->codCas'</td>";
            $html .= "<td style='border: solid #000 1px; text-align: left;'>$row->nompr</td>";
            $html .= "<td style='border: solid #000 1px; text-align: left;'>$row->dirpr</td>";
            $html .= "<td style='border: solid #000 1px; text-align: right;'>$xconsec</td>";
            $html .= "<td style='border: solid #000 1px; text-align: right;'>$row->fecha</td>";
            $html .= "<td style='border: solid #000 1px; text-align: right;'>".number_format($valor->valor, 2, ',', '.')."</td>";
            $html .= "<td style='border: solid #000 1px; text-align: right;'>$minAnno - $maxAnno</td>";
            $html .= "</tr>";
        }
        $html .= "</tbody>";
        $html .= "</table>";
        $html .= "</body>";
        $html .= "</html>";
        echo $html;
    }
}
