<?php

namespace Predial\Http\Controllers;

use Predial\Area;
use Predial\Avaluo;
use Predial\Predio;
use Predial\ParametrizacionAnio;
use Predial\Http\Controllers\ExtractoController;
use Predial\Http\Controllers\ListadoPagosController;
use Fpdf;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Collection as Collection;

class ListadoPrediosControntroller extends Controller{

    public function vistaDeuda(){
        return view('listados.adeudo');
    }

    public static function listadoAdeudo(){
        try {
            ini_set('max_execution_time', 0);
            $axr  = array();
            $data = DB::table("gp_predio1 as pre")
                        ->select("pre.codigo_catastral as cod", "pre.nombre as nom", "pre.direccion as dir", "pre.matricula_inmobiliaria as mat",
                            "pro.nombres as nompro", "pro.numero as doc", DB::raw("MAX(ava.valor) as avaluo"), "pre.id_unico as id"
                        )
                        ->leftJoin("gp_tercero_predio as tpr", "tpr.predio", "=", "pre.id_unico")
                        ->leftJoin("gr_propietarios as pro", "tpr.tercero", "=", "pro.id_unico")
                        ->leftJoin("gr_avaluo as ava", "ava.predio", "=", "pre.id_unico")
                        ->where('pre.estado', 2)
                        ->where("tpr.orden", "001")
                        ->where("tpr.propietario", 0)
                        ->groupBy("pre.id_unico")
                        ->orderBy("ava.valor", "DESC")
                        ->distinct()
                        ->get();
            foreach ($data as $row) {
                $anno = ExtractoController::obtnerultimoannopago($row->id);
                if($anno != date("Y") || empty($anno)){
                    $xxx = ListadoPrediosControntroller::buscarAvaluoPredio($row->id);
                    if(empty($anno)){
                        $anno = "No hay último año pago";
                    }
                    $axr[] = array(
                        "cod" => $row->cod, "mat" => $row->mat, "nom" => $row->nom, "dir" => $row->dir, "nompro" => $row->nompro, "num_doc" => $row->doc,
                        "ava" => $row->avaluo, "xadeudo" => $xxx, "anno" => $anno);
                }
            }
            $adeudo = Collection::make($axr);
            $adeudo = $adeudo->sortByDesc('xadeudo');
            $compania = ListadoPagosController::obtenerCompania();
            $html  = "";
            $html .= "<!DOCTYPE html>";
            $html .= "\n<html>";
            $html .= "\n<head>";
            $html .= "\n\t<meta charset=\"utf-8\">";
            $html .= "\n\t<meta http-equiv=\"X-UA-Compatible\" content=\"IE=edge92\">";
            $html .= "\n\t<title>Listado de Predios Adeuda</title>";
            $html .= "\n</head>";
            $html .= "\n<body>";
            $html .= "\n\t<table style='width: 100%; border-collapse: collapse; font-family: \"Arial\", sans-serif;'>";
            $html .= "\n\t\t<thead>";
            $html .= "\n\t\t\t<tr>";
            $html .= "\n\t\t\t\t<th style=\"text-align: center\" colspan='8'>$compania->razonsocial</th>";
            $html .= "\n\t\t\t</tr>";
            $html .= "\n\t\t\t<tr>";
            $html .= "\n\t\t\t\t<th style=\"text-align: center\" colspan='8'>$compania->tipodoc : $compania->numeroi</th>";
            $html .= "\n\t\t\t</tr>";
            $html .= "\n\t\t\t<tr style=\"font-size: 10px\">";
            $html .= "\n\t\t\t\t<th style=\"border: 1px solid #000\">Codigo Catastral</th>";
            $html .= "\n\t\t\t\t<th style=\"border: 1px solid #000\">Matricula</th>";
            $html .= "\n\t\t\t\t<th style=\"border: 1px solid #000\">Nombre de Predio</th>";
            $html .= "\n\t\t\t\t<th style=\"border: 1px solid #000\">Dirección</th>";
            $html .= "\n\t\t\t\t<th style=\"border: 1px solid #000\">Propietario</th>";
            $html .= "\n\t\t\t\t<th style=\"border: 1px solid #000\">CC / NIT</th>";
            $html .= "\n\t\t\t\t<th style=\"border: 1px solid #000\">Avaluo</th>";
            $html .= "\n\t\t\t\t<th style=\"border: 1px solid #000\">Ultimo Año Pago</th>";
            $html .= "\n\t\t\t\t</tr>";
            $html .= "\n\t\t</thead>";
            $html .= "\n\t\t<tbody>";
            foreach ($adeudo as $row) {
                list($cod, $mat, $nom, $dir, $nompro, $doc, $ava, $anno) = array($row['cod'], $row['mat'], $row['nom'], $row['dir'], $row['nompro'], $row['num_doc'], $row['ava'], $row['anno']);
                $html .= "\n\t\t\t<tr style=\"font-size: 10px\">";
                $html .= "\n\t\t\t\t<td style=\"border: 1px solid #000\">'$cod'</td>";
                $html .= "\n\t\t\t\t<td style=\"border: 1px solid #000\">$mat</td>";
                $html .= "\n\t\t\t\t<td style=\"border: 1px solid #000\">$nom</td>";
                $html .= "\n\t\t\t\t<td style=\"border: 1px solid #000\">$dir</td>";
                $html .= "\n\t\t\t\t<td style=\"border: 1px solid #000\">$nompro</td>";
                $html .= "\n\t\t\t\t<td style=\"border: 1px solid #000; text-align: right;\">'$doc'</td>";
                $html .= "\n\t\t\t\t<td style=\"border: 1px solid #000; text-align: right;\">".number_format($ava, 2, ',', '.')."</td>";
                $html .= "\n\t\t\t\t<td style=\"border: 1px solid #000; text-align: right;\">$anno</td>";
                $html .= "\n\t\t\t</tr>";
            }
            $html .= "\n\t\t<tbody>";
            $html .= "\n\t</table>";
            $html .= "\n</body>";
            $html .= "\n</html>";
            header('Content-type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename="ListadoAdeudoPredios.xls"');
            echo $html;
        } catch (Exception $e) {
            return $e->getMessage();
        }
    }

    public static function buscarAvaluoPredio($predio){
        try {
            $anno = ExtractoController::obtnerultimoannopago($predio);
            if(empty($anno)){
                $data = Avaluo::where('predio', $predio)->get();
            }else{
                $data = Avaluo::where("predio", "=", $predio)->where("anno", ">", $anno)->get();
            }
            $xxx = 0;
            foreach ($data as $row) {
                $x = ListadoPrediosControntroller::buscarAvaluosPagos($row->id_unico, $predio);
                $e = ListadoPrediosControntroller::buscarExoneraciones($predio, $row->anno);
                if(empty($x))
                    if(empty($e))
                        $xxx += $row->valor;

            }
            return $xxx;
        } catch (Exception $e) {
            return $e->getMessage();
        }
    }

    public static function buscarAvaluosPagos($avaluo, $predio){
        try {
            $data = DB::table('gr_detalle_factura_predial as dtf')
                        ->select('dtp.id_unico')
                        ->leftJoin('gr_detalle_pago_predial as dtp', 'dtp.detallefactura', '=', 'dtf.id_unico')
                        ->where('dtf.avaluo', $avaluo)
                        ->first();
            return $data;
        } catch (Exception $e) {
            return $e->getMessage();
        }
    }

    public static function buscarExoneraciones($predio, $anno){
        try {
            $data = DB::table('gr_excensiones')
                        ->where('predio', '=', $predio)
                        ->where('annoinicial', '<=', $anno)
                        ->where('annofinal', '>=', $anno)
                        ->first();
            return $data;
        } catch (Exception $e) {
            return $e->getMessage();
        }
    }

    public function vistaListado(){
        return view('informesPredial.vistaListadoConcepto');
    }

    public function vistaInformeCartera(){
         $carteras = DB::table('gr_cartera_obligada')
                ->select(DB::raw("DATE_FORMAT(fecha, '%d/%m/%Y') AS fecha"), 'fecha AS normf')
                ->groupBy('normf')
                ->orderBy('normf', 'DESC')
                ->get();
            return view('informesPredial.informeCartera', compact('carteras',$carteras));

    }

    public static function listadoImpuesto(Request $request){
        try {
            $compania = ListadoPagosController::obtenerCompania();
            $param    = session()->get('anno');
            $pdf      = new Fpdf();
            $pdf::AddPage("L", "Legal");
            $pdf::SetFont('Arial','B', 10);
            $pdf::SetY(10);
            if(!empty($compania->logo)){
                $pdf::Image($compania->logo, 15, 8, 20);
            }
            $pdf::SetX(35);
            $pdf::Cell(308, 5, $compania->razonsocial, 0, 0, "C");
            $pdf::Ln(5);
            $pdf::SetX(35);
            $pdf::Cell(308, 5,"NIT :".$compania->numeroi,0,0,'C');
            $pdf::Ln(5);
            $pdf::SetX(35);
            $pdf::Cell(308, 5, utf8_decode("RELACIÓN DE RECAUDOS DE CONCEPTO IMPUESTO PREDIAL E INTERES"),0,0,'C');
            $pdf::Ln(5);
            $pdf::SetX(35);
            $pdf::Cell(308, 5, utf8_decode("ENTRE $request->txtFechaI Y $request->txtFechaF"),0,0,'C');
            $pdf::Ln(5);
            $pdf::SetFont('Arial','B', 9);
            $pdf::Cell(30, 5, "COD. CATASTRAL", 'LTBR', 0, 'C');
            $pdf::Cell(100, 5, "PROPIETARIO", 'LTBR', 0, 'C');
            $pdf::Cell(40, 5, utf8_decode("Nº FACTURA"), 'LTBR', 0, 'C');
            $pdf::Cell(40, 5, "FECHA DE PAGO", 'LTBR', 0, 'C');
            $pdf::Cell(40, 5, "IMPUESTO", 'LTBR', 0, 'C');
            $pdf::Cell(40, 5, "INTERES", 'LTBR', 0, 'C');
            $pdf::Cell(40, 5, "TOTAL", 'LTBR', 0, 'C');
            $pdf::Ln(5);
            $ffi    = explode("/", $request->txtFechaI);
            $fff    = explode("/", $request->txtFechaF);
            $fechaI = "$ffi[2]-$ffi[1]-$ffi[0]";
            $fechaF = "$fff[2]-$fff[1]-$fff[0]";
            $data   = DB::table('gr_detalle_pago_predial as dtp')
                        ->select(
                            'pre.codigo_catastral as cod', 'pro.nombres as propietario', 'fat.numero as num',
                            DB::raw('DATE_FORMAT(fat.fechafactura, "%d/%m/%Y") as fecha'),
                            DB::raw('sum(if(con.id_concepto = 1, dtp.valor, 0)) as imp'),
                            DB::raw('sum(if(con.id_concepto = 4, dtp.valor, 0)) as ins'),
                            'pag.tipopago as tpgo'
                        )
                        ->leftJoin('gr_detalle_factura_predial as dtf', 'dtp.detallefactura', '=', 'dtf.id_unico')
                        ->leftJoin('gr_pago_predial as pag', 'dtp.pago', '=', 'pag.id_unico')
                        ->leftJoin('gr_factura_predial as fat', 'dtf.factura', '=', 'fat.id_unico')
                        ->leftJoin('gr_concepto_predial as con', 'dtf.concepto', '=', 'con.id_unico')
                        ->leftJoin('gp_predio1 as pre', 'fat.predio', '=',  'pre.id_unico')
                        ->leftJoin('gp_tercero_predio as tpr', 'tpr.predio', '=', 'pre.id_unico')
                        ->leftJoin('gr_propietarios as pro', 'tpr.tercero', '=', 'pro.id_unico')
                        ->whereBetween('pag.fechapago', [$fechaI, $fechaF])
                        ->where('pag.parametrizacionanno', '=', $param)
                        ->where('pag.tipopago', '!=', 6)
                        ->where('tpr.propietario', '=', 0)
                        ->where('tpr.orden', '=', '001' )
                        ->orderBy('pag.fechapago', 'ASC')
                        ->groupBy('pag.id_unico')
                        ->distinct()
                        ->get();
            $pdf::SetFont('Arial','', 8);
            $imp = 0;$int = 0;$xxx = 0;
            foreach ($data as $row){
                $xmp  = $row->imp;
                $xin  = $row->ins;
                $xtt  = $xmp + $xin;

                if($row->tpgo == 7){
                    $imp -= $xmp; $int -= $xin; $xxx -= $xtt;
                }else{
                    $imp += $xmp; $int += $xin; $xxx += $xtt;
                }

                $pdf::Cell(30, 5, $row->cod, 'LTBR', 0, 'C');
                $pdf::Cell(100, 5, $row->propietario, 'LTBR', 0, 'L');
                $pdf::Cell(40, 5, $row->num, 'LTBR', 0, 'R');
                $pdf::Cell(40, 5, $row->fecha, 'LTBR', 0, 'R');
                $pdf::Cell(40, 5, number_format($xmp, 0, '.', ','), 'LTBR', 0, 'R');
                $pdf::Cell(40, 5, number_format($xin, 0, '.', ','), 'LTBR', 0, 'R');
                $pdf::Cell(40, 5, number_format($xtt, 0, '.', ','), 'LTBR', 0, 'R');
                $pdf::Ln(5);
            }
            $pdf::SetFont('Arial','B', 8);
            $pdf::Cell(210, 5, "TOTALES", 'LTBR', 0, 'C');
            $pdf::Cell(40, 5, number_format($imp, 0, '.', ','), 'LTBR', 0, 'R');
            $pdf::Cell(40, 5, number_format($int, 0, '.', ','), 'LTBR', 0, 'R');
            $pdf::Cell(40, 5, number_format($xxx, 0, '.', ','), 'LTBR', 0, 'R');
            $pdf::Output();
            exit;
        } catch (Exception $e) {
            return $e->getMessage();
        }
    }

    public static function listadoDebido(Request $request){
        try {
            $param    = session()->get('anno');
            $compania = ListadoPagosController::obtenerCompania();
            $pdf      = new Fpdf();
            $pdf::AddPage("L", "Legal");
            $pdf::SetFont('Arial','B', 10);
            $pdf::SetY(10);
            if(!empty($compania->logo)){
                $pdf::Image($compania->logo, 15, 8, 20);
            }
            $pdf::SetX(35);
            $pdf::Cell(308, 5, $compania->razonsocial, 0, 0, "C");
            $pdf::Ln(5);
            $pdf::SetX(35);
            $pdf::Cell(308, 5,"NIT :".$compania->numeroi,0,0,'C');
            $pdf::Ln(5);
            $pdf::SetX(35);
            $pdf::Cell(308, 5, utf8_decode("RELACIÓN DE RECAUDOS DE CONCEPTO DEBIDO COBRAR"),0,0,'C');
            $pdf::Ln(5);
            $pdf::SetX(35);
            $pdf::Cell(308, 5, utf8_decode("ENTRE $request->txtFechaI Y $request->txtFechaF"),0,0,'C');
            $pdf::Ln(5);
            $pdf::SetFont('Arial','B', 9);
            $pdf::Cell(30, 5, "COD. CATASTRAL", 'LTBR', 0, 'C');
            $pdf::Cell(120, 5, "PROPIETARIO", 'LTBR', 0, 'C');
            $pdf::Cell(60, 5, utf8_decode("Nº FACTURA"), 'LTBR', 0, 'C');
            $pdf::Cell(60, 5, "FECHA DE PAGO", 'LTBR', 0, 'C');
            $pdf::Cell(60, 5, "DEBIDO COBRAR", 'LTBR', 0, 'C');
            $pdf::Ln(5);
            $ffi    = explode("/", $request->txtFechaI);
            $fff    = explode("/", $request->txtFechaF);
            $fechaI = "$ffi[2]-$ffi[1]-$ffi[0]";
            $fechaF = "$fff[2]-$fff[1]-$fff[0]";
            $data   = DB::table('gr_detalle_pago_predial as dtp')
                        ->select(
                            'pre.codigo_catastral as cod', 'pro.nombres as propietario', 'fat.numero as num',
                            DB::raw('DATE_FORMAT(fat.fechafactura, "%d/%m/%Y") as fecha'),
                            DB::raw('sum(if(con.id_concepto = 9, dtp.valor, 0)) as dbd'),
                            'pag.tipopago as tpgo'
                        )
                        ->leftJoin('gr_detalle_factura_predial as dtf', 'dtp.detallefactura', '=', 'dtf.id_unico')
                        ->leftJoin('gr_pago_predial as pag', 'dtp.pago', '=', 'pag.id_unico')
                        ->leftJoin('gr_factura_predial as fat', 'dtf.factura', '=', 'fat.id_unico')
                        ->leftJoin('gr_concepto_predial as con', 'dtf.concepto', '=', 'con.id_unico')
                        ->leftJoin('gp_predio1 as pre', 'fat.predio', '=',  'pre.id_unico')
                        ->leftJoin('gp_tercero_predio as tpr', 'tpr.predio', '=', 'pre.id_unico')
                        ->leftJoin('gr_propietarios as pro', 'tpr.tercero', '=', 'pro.id_unico')
                        ->where('pag.parametrizacionanno', '=', $param)
                        ->where('pag.tipopago', '!=', 6)
                        ->whereBetween('pag.fechapago', [$fechaI, $fechaF])
                        ->where('tpr.propietario', '=', 0)
                        ->where('tpr.orden', '=', '001')
                        ->orderBy('pag.fechapago', 'ASC')
                        ->groupBy('pag.id_unico')
                        ->distinct()
                        ->get();
            $pdf::SetFont('Arial','', 8);
            list($deb) = array(0);
            foreach ($data as $row){
                $xdb = $row->dbd;

                if($row->tpgo == 7){
                    $deb -= $xdb;
                }else{
                    $deb += $xdb;
                }

                $pdf::Cell(30, 5, $row->cod, 'LTBR', 0, 'C');
                $pdf::Cell(120, 5, $row->propietario, 'LTBR', 0, 'L');
                $pdf::Cell(60, 5, $row->num, 'LTBR', 0, 'R');
                $pdf::Cell(60, 5, $row->fecha, 'LTBR', 0, 'R');
                $pdf::Cell(60, 5, number_format($xdb, 0, '.', ','), 'LTBR', 0, 'R');
                $pdf::Ln(5);
            }
            $pdf::SetFont('Arial','B', 8);
            $pdf::Cell(270, 5, "TOTAL", 'LTBR', 0, 'C');
            $pdf::Cell(60, 5, number_format($deb, 0, '.', ','), 'LTBR', 0, 'R');
            $pdf::Output();
            exit;
        } catch (Exception $e) {
            return $e->getMessage();
        }
    }

    public static function listadoCorReg(Request $request){
        try {
            $param    = session()->get('anno');
            $compania = ListadoPagosController::obtenerCompania();
            $pdf      = new Fpdf();
            $pdf::AddPage("L", "Legal");
            $pdf::SetFont('Arial','B', 10);
            $pdf::SetY(10);
            if(!empty($compania->logo)){
                $pdf::Image($compania->logo, 15, 8, 20);
            }
            $pdf::SetX(35);
            $pdf::Cell(308, 5, $compania->razonsocial, 0, 0, "C");
            $pdf::Ln(5);
            $pdf::SetX(35);
            $pdf::Cell(308, 5,"NIT :".$compania->numeroi,0,0,'C');
            $pdf::Ln(5);
            $pdf::SetX(35);
            $pdf::Cell(308, 5, utf8_decode("RELACIÓN DE RECAUDOS DE CONCEPTO CAS"),0,0,'C');
            $pdf::Ln(5);
            $pdf::SetX(35);
            $pdf::Cell(308, 5, utf8_decode("ENTRE $request->txtFechaI Y $request->txtFechaF"),0,0,'C');
            $pdf::Ln(5);
            $pdf::SetFont('Arial','B', 9);
            $pdf::Cell(30, 5, "COD. CATASTRAL", 'LTBR', 0, 'C');
            $pdf::Cell(100, 5, "PROPIETARIO", 'LTBR', 0, 'C');
            $pdf::Cell(40, 5, utf8_decode("Nº FACTURA"), 'LTBR', 0, 'C');
            $pdf::Cell(40, 5, "FECHA DE PAGO", 'LTBR', 0, 'C');
            $pdf::Cell(40, 5, "CAS", 'LTBR', 0, 'C');
            $pdf::Cell(40, 5, "INTERES", 'LTBR', 0, 'C');
            $pdf::Cell(40, 5, "TOTAL", 'LTBR', 0, 'C');
            $pdf::Ln(5);
            $ffi    = explode("/", $request->txtFechaI);
            $fff    = explode("/", $request->txtFechaF);
            $fechaI = trim("$ffi[2]-$ffi[1]-$ffi[0]");
            $fechaF = trim("$fff[2]-$fff[1]-$fff[0]");
            $data   = DB::table('gr_detalle_pago_predial as dtp')
                        ->select(
                            'pre.codigo_catastral as cod', 'pro.nombres as propietario', 'fat.numero as num',
                            DB::raw('DATE_FORMAT(fat.fechafactura, "%d/%m/%Y") as fecha'),
                            DB::raw('sum(if(con.id_concepto = 3, dtp.valor, 0)) as cas'),
                            DB::raw('sum(if(con.id_concepto = 8, dtp.valor, 0)) as ins'),
                            'pag.tipopago as tpgo','dtp.id_unico as ids'
                        )
                        ->leftJoin('gr_detalle_factura_predial as dtf', 'dtp.detallefactura', '=', 'dtf.id_unico')
                        ->leftJoin('gr_pago_predial as pag', 'dtp.pago', '=', 'pag.id_unico')
                        ->leftJoin('gr_factura_predial as fat', 'dtf.factura', '=', 'fat.id_unico')
                        ->leftJoin('gr_concepto_predial as con', 'dtf.concepto', '=', 'con.id_unico')
                        ->leftJoin('gp_predio1 as pre', 'fat.predio', '=',  'pre.id_unico')
                        ->leftJoin('gp_tercero_predio as tpr', 'tpr.predio', '=', 'pre.id_unico')
                        ->leftJoin('gr_propietarios as pro', 'tpr.tercero', '=', 'pro.id_unico')
                        ->whereBetween('pag.fechapago', [$fechaI, $fechaF])
                        ->where('tpr.propietario', '=', 0)
                        ->where('tpr.orden', '=', '001')
                        ->where('pag.parametrizacionanno', '=', $param)
                        ->where('pag.tipopago', '!=', 6)
                        ->orderBy('pag.fechapago', 'ASC')
                        ->groupBy('pag.id_unico')
                        ->distinct()
                        ->get();
            $pdf::SetFont('Arial','', 8);
            $imp = 0;$int = 0;$xxx = 0;
            foreach ($data as $row){
                $xmp  = $row->cas;
                $xin  = $row->ins;
                $xtt  = $xmp + $xin;

                if($row->tpgo == 7){
                    $imp -= $xmp; $int -= $xin; $xxx -= $xtt;
                }else{
                    $imp += $xmp; $int += $xin; $xxx += $xtt;
                }

                $pdf::Cell(30, 5, $row->cod, 'LTBR', 0, 'C');
                $pdf::Cell(100, 5, $row->propietario, 'LTBR', 0, 'L');
                $pdf::Cell(40, 5, $row->num, 'LTBR', 0, 'R');
                $pdf::Cell(40, 5, $row->fecha, 'LTBR', 0, 'R');
                $pdf::Cell(40, 5, number_format($xmp, 0, '.', ','), 'LTBR', 0, 'R');
                $pdf::Cell(40, 5, number_format($xin, 0, '.', ','), 'LTBR', 0, 'R');
                $pdf::Cell(40, 5, number_format($xtt, 0, '.', ','), 'LTBR', 0, 'R');
                $pdf::Ln(5);
            }
            $pdf::SetFont('Arial','B', 8);
            $pdf::Cell(210, 5, "TOTALES", 'LTBR', 0, 'C');
            $pdf::Cell(40, 5, number_format($imp, 0, '.', ','), 'LTBR', 0, 'R');
            $pdf::Cell(40, 5, number_format($int, 0, '.', ','), 'LTBR', 0, 'R');
            $pdf::Cell(40, 5, number_format($xxx, 0, '.', ','), 'LTBR', 0, 'R');
            $pdf::Output();
            exit;
        } catch (Exception $e) {
            return $e->getMessage();
        }
    }

    public static function buscarValorDebidoCobrar($predio, $anno, $concepto){
        try {
            $data = DB::table('gr_detalle_pago_predial as gdp')
                        ->leftJoin('gr_detalle_factura_predial as gdf', 'gdp.detallefactura', '=', 'gdf.id_unico')
                        ->leftJoin('gr_avaluo as gra', 'gdf.avaluo', '=', 'gra.id_unico')
                        ->leftJoin('gr_concepto_predial as gcp', 'gdf.concepto', '=', 'gcp.id_unico')
                        ->leftJoin('gr_factura_predial as gft', 'gdf.factura', '=', 'gft.id_unico')
                        ->where('gft.predio', $predio)
                        ->where('gra.anno', $anno)
                        ->where('gcp.id_concepto', $concepto)
                        ->first();
            return $data;
        } catch (Exception $e) {
            return $e->getMessage();
        }
    }

    public static function vistaConceptosAdeudo(){
        return view('listados.conceptos');
    }

    public static function informeConceptosPdf(){
        ini_set('max_execution_time', 0);
        $compania = ListadoPagosController::obtenerCompania();
        $xData = DB::table('gr_cartera_obligada as grc')
                    ->select(
                        'gpr.codigo_catastral as cod',
                        'gra.anno as debe',
                        'gps.nombres as prop',
                        DB::raw('(sum(if(grc.concepto = 1, grc.valor, 0))) as impuesto'),
                        DB::raw('(sum(if(grc.concepto = 4, grc.valor, 0))) as mora'),
                        DB::raw('(sum(if(grc.concepto = 3, grc.valor, 0))) as corp'),
                        DB::raw('(sum(if(grc.concepto = 8, grc.valor, 0))) as intc'),
                        DB::raw('(sum(if(grc.concepto = 7, grc.valor, 0))) as sobr'),
                        DB::raw('(sum(if(grc.concepto = 5, grc.valor, 0))) as dest'),
                        DB::raw('(sum(if(grc.concepto = 6, grc.valor, 0))) as pape'),
                        DB::raw('(sum(if(grc.concepto = 9, grc.valor, 0))) as cobr')
                    )
                    ->leftJoin('gp_predio1 as gpr', 'grc.predio', '=', 'gpr.id_unico')
                    ->leftJoin('gr_avaluo  as gra', 'grc.avaluo', '=', 'gra.id_unico')
                    ->leftJoin('gp_tercero_predio as gtp', 'gtp.predio', '=', 'gpr.id_unico')
                    ->leftJoin('gr_propietarios as gps', 'gtp.tercero', '=', 'gps.id_unico')
                    ->where('gpr.estado', '=', 2)
                    ->where('grc.fecha', date("Y-m-d"))
                    ->where('gtp.propietario', '=', 0)
                    ->where('gtp.orden', '=', '001')
                    ->whereNotNull('gpr.codigo_catastral')
                    ->get();
        if($xData->count() == 1){
            $data  = DB::table('gp_predio1 as gpr')->where('gpr.estado', 2)->select('gpr.id_unico as id')->get();
            foreach ($data as $row) {
                $xxxA = ExtractoController::obtnerultimoannopago($row->id);
                $xdb  = ListadoPrediosControntroller::buscarValorDebidoCobrar($row->id, date("Y"), 9);
                if(empty($xdb)){
                    if(!empty($xxxA)){
                        $avaluos = CarteraController::obtenerAvaluosDesde($row->id, $xxxA);
                        CarteraController::GuardarData($row->id, $avaluos, date("Y-m-d"));
                    }

                    if(empty($xxxA)){
                        $avaluos = CarteraController::obtenerTAvaluos($row->id);
                        CarteraController::GuardarData($row->id, $avaluos, date("Y-m-d"));
                    }
                }
            }
            $xData = DB::table('gr_cartera_obligada as grc')
                    ->select(
                        'gpr.codigo_catastral as cod',
                        'gra.anno as debe',
                        'gps.nombres as prop',
                        DB::raw('(sum(if(grc.concepto = 1, grc.valor, 0))) as imp'),
                        DB::raw('(sum(if(grc.concepto = 4, grc.valor, 0))) as mora'),
                        DB::raw('(sum(if(grc.concepto = 3, grc.valor, 0))) as corp'),
                        DB::raw('(sum(if(grc.concepto = 8, grc.valor, 0))) as intc'),
                        DB::raw('(sum(if(grc.concepto = 5, grc.valor, 0))) as dest'),
                        DB::raw('(sum(if(grc.concepto = 6, grc.valor, 0))) as pape'),
                        DB::raw('(sum(if(grc.concepto = 7, grc.valor, 0))) as sobr'),
                        DB::raw('(sum(if(grc.concepto = 9, grc.valor, 0))) as cobr')
                    )
                    ->leftJoin('gp_predio1 as gpr', 'grc.predio', '=', 'gpr.id_unico')
                    ->leftJoin('gr_avaluo  as gra', 'grc.avaluo', '=', 'gra.id_unico')
                    ->leftJoin('gp_tercero_predio as gtp', 'gtp.predio', '=', 'gpr.id_unico')
                    ->leftJoin('gr_propietarios as gps', 'gtp.tercero', '=', 'gps.id_unico')
                    ->where('gpr.estado', '=', 2)
                    ->where('grc.fecha', date("Y-m-d"))
                    ->where('gtp.propietario', '=', 0)
                    ->where('gtp.orden', '=', '001')
                    ->whereNotNull('gpr.codigo_catastral')
                    ->get();
        }
        $pdf      = new Fpdf();
        $pdf::AddPage("L", "Legal");
        $pdf::SetFont('Arial','B', 10);
        $pdf::SetY(10);
        if(!empty($compania->logo)){
            $pdf::Image($compania->logo, 15, 8, 15);
        }
        $pdf::SetX(35);
        $pdf::Cell(308, 5, $compania->razonsocial, 0, 0, "C");
        $pdf::Ln(5);
        $pdf::SetX(35);
        $pdf::Cell(308, 5,"NIT :".$compania->numeroi,0,0,'C');
        $pdf::Ln(5);
        $pdf::SetX(35);
        $pdf::Cell(308, 5, utf8_decode("LISTADO MOROSOS DEBIDO COBRAR"),0,0,'C');
        $pdf::Ln(5);
        $pdf::SetFont('Arial','B', 9);
        $pdf::Cell(40, 5, "CODIGO", 'LTR', 0, 'C');
        $pdf::Cell(90, 5, "NOMBRE", 'LTR', 0, 'C');
        $pdf::Cell(20, 5, "VIGENCIA", 'LTR', 0, 'C');
        $pdf::Cell(20, 5, "IMPUESTO", 'LTR', 0, 'C');
        $pdf::Cell(20, 5, "INTERES", 'LTR', 0, 'C');
        $pdf::Cell(20, 5, "", 'LTR', 0, 'C');
        $pdf::Cell(20, 5, "INTERES", 'LTR', 0, 'C');
        $pdf::Cell(20, 5, "", 'LTR', 0, 'C');
        $pdf::Cell(20, 5, "", 'LTR', 0, 'C');
        $pdf::Cell(20, 5, "SOBRETASA", 'LTR', 0, 'C');
        $pdf::Cell(20, 5, "DEBIDO", 'LTR', 0, 'C');
        $pdf::Cell(20, 5, "", 'LTR', 0, 'C');
        $pdf::Ln(5);
        $pdf::Cell(40, 5, "CATASTRAL", 'LBR', 0, 'C');
        $pdf::Cell(90, 5, "PROPIETARIO", 'LBR', 0, 'C');
        $pdf::Cell(20, 5, "ADEUDADA", 'LBR', 0, 'C');
        $pdf::Cell(20, 5, "PREDIAL", 'LBR', 0, 'C');
        $pdf::Cell(20, 5, "IMPUESTO", 'LBR', 0, 'C');
        $pdf::Cell(20, 5, "CAS", 'LBR', 0, 'C');
        $pdf::Cell(20, 5, "CAS", 'LBR', 0, 'C');
        $pdf::Cell(20, 5, "DESCUENTO", 'LBR', 0, 'C');
        $pdf::Cell(20, 5, "PAPELERIA", 'LBR', 0, 'C');
        $pdf::Cell(20, 5, "BOMBERIL", 'LBR', 0, 'C');
        $pdf::Cell(20, 5, "COBRAR", 'LBR', 0, 'C');
        $pdf::Cell(20, 5, "TOTAL", 'LBR', 0, 'C');
        $pdf::Ln(5);
        $pdf::SetFont('Arial','', 9);
        foreach ($xData as $row) {
            $xxx = $row->imp + $row->mora + $row->corp + $row->intc + $row->dest + $row->pape + $row->sobr + $row->cobr;
            $pdf::Cell(40, 5, $row->cod, 0, 0, 'L');
            $pdf::Cell(90, 5, $row->prop, 0, 0, 'L');
            $pdf::Cell(20, 5, $row->debe, 0, 0, 'R');
            $pdf::Cell(20, 5, number_format($row->imp, 2, ',', '.'), 0, 0, 'R');
            $pdf::Cell(20, 5, number_format($row->mora, 2, ',', '.'), 0, 0, 'R');
            $pdf::Cell(20, 5, number_format($row->corp, 2, ',', '.'), 0, 0, 'R');
            $pdf::Cell(20, 5, number_format($row->intc, 2, ',', '.'), 0, 0, 'R');
            $pdf::Cell(20, 5, number_format($row->dest, 2, ',', '.'), 0, 0, 'R');
            $pdf::Cell(20, 5, number_format($row->pape, 2, ',', '.'), 0, 0, 'R');
            $pdf::Cell(20, 5, number_format($row->sobr, 2, ',', '.'), 0, 0, 'R');
            $pdf::Cell(20, 5, number_format($row->cobr, 2, ',', '.'), 0, 0, 'R');
            $pdf::Cell(20, 5, number_format($xxx, 2, ',', '.'), 0, 0, 'R');
            $pdf::Ln(5);
        }
        $pdf::Output();
        exit;
    }

    public static function informeConceptosExl(){
        ini_set('max_execution_time', 0);
        $compania = ListadoPagosController::obtenerCompania();
        $xData = DB::table('gr_cartera_obligada as grc')
            ->select(
                'gpr.codigo_catastral as cod',
                'gra.anno as debe',
                'gps.nombres as prop',
                DB::raw('(sum(if(grc.concepto = 1, grc.valor, 0))) as impuesto'),
                DB::raw('(sum(if(grc.concepto = 4, grc.valor, 0))) as mora'),
                DB::raw('(sum(if(grc.concepto = 3, grc.valor, 0))) as corp'),
                DB::raw('(sum(if(grc.concepto = 8, grc.valor, 0))) as intc'),
                DB::raw('(sum(if(grc.concepto = 7, grc.valor, 0))) as sobr'),
                DB::raw('(sum(if(grc.concepto = 5, grc.valor, 0))) as dest'),
                DB::raw('(sum(if(grc.concepto = 6, grc.valor, 0))) as pape'),
                DB::raw('(sum(if(grc.concepto = 9, grc.valor, 0))) as cobr')
            )
            ->leftJoin('gp_predio1 as gpr', 'grc.predio', '=', 'gpr.id_unico')
            ->leftJoin('gr_avaluo  as gra', 'grc.avaluo', '=', 'gra.id_unico')
            ->leftJoin('gp_tercero_predio as gtp', 'gtp.predio', '=', 'gpr.id_unico')
            ->leftJoin('gr_propietarios as gps', 'gtp.tercero', '=', 'gps.id_unico')
            ->where('gpr.estado', '=', 2)
            ->where('grc.fecha', date("Y-m-d"))
            ->where('gtp.propietario', '=', 0)
            ->where('gtp.orden', '=', '001')
            ->whereNotNull('gpr.codigo_catastral')
            ->get();
        if($xData->count() == 1){
            $data  = DB::table('gp_predio1 as gpr')->where('gpr.estado', 2)->select('gpr.id_unico as id')->get();
            foreach ($data as $row) {
                $xxxA = ExtractoController::obtnerultimoannopago($row->id);
                $xdb  = ListadoPrediosControntroller::buscarValorDebidoCobrar($row->id, date("Y"), 9);
                if(empty($xdb)){
                    if(!empty($xxxA)){
                        $avaluos = CarteraController::obtenerAvaluosDesde($row->id, $xxxA);
                        CarteraController::GuardarData($row->id, $avaluos, date("Y-m-d"));
                    }

                    if(empty($xxxA)){
                        $avaluos = CarteraController::obtenerTAvaluos($row->id);
                        CarteraController::GuardarData($row->id, $avaluos, date("Y-m-d"));
                    }
                }
            }
            $xData = DB::table('gr_cartera_obligada as grc')
                ->select(
                    'gpr.codigo_catastral as cod',
                    'gra.anno as debe',
                    'gps.nombres as prop',
                    DB::raw('(sum(if(grc.concepto = 1, grc.valor, 0))) as imp'),
                    DB::raw('(sum(if(grc.concepto = 4, grc.valor, 0))) as mora'),
                    DB::raw('(sum(if(grc.concepto = 3, grc.valor, 0))) as corp'),
                    DB::raw('(sum(if(grc.concepto = 8, grc.valor, 0))) as intc'),
                    DB::raw('(sum(if(grc.concepto = 5, grc.valor, 0))) as dest'),
                    DB::raw('(sum(if(grc.concepto = 6, grc.valor, 0))) as pape'),
                    DB::raw('(sum(if(grc.concepto = 7, grc.valor, 0))) as sobr'),
                    DB::raw('(sum(if(grc.concepto = 9, grc.valor, 0))) as cobr')
                )
                ->leftJoin('gp_predio1 as gpr', 'grc.predio', '=', 'gpr.id_unico')
                ->leftJoin('gr_avaluo  as gra', 'grc.avaluo', '=', 'gra.id_unico')
                ->leftJoin('gp_tercero_predio as gtp', 'gtp.predio', '=', 'gpr.id_unico')
                ->leftJoin('gr_propietarios as gps', 'gtp.tercero', '=', 'gps.id_unico')
                ->where('gpr.estado', '=', 2)
                ->where('grc.fecha', date("Y-m-d"))
                ->where('gtp.propietario', '=', 0)
                ->where('gtp.orden', '=', '001')
                ->whereNotNull('gpr.codigo_catastral')
                ->get();
            header("Content-type: application/vnd.ms-excel");
            header("Content-Disposition: attachment; filename=ListadosDeudoresDebidoCobrar.xls");
            $html = "";
            $html .= "<html>";
            $html .= "<body>";
            $html .= "<table style='width: 100%;' border='1'>";
            $html .= "<thead>";
            $html .= "<caption> $compania->razonsocial <br/> NIT :$compania->numeroi <br/> ".utf8_decode("LISTADO MOROSOS DEBIDO COBRAR")." </caption>";
            $html .= "<tr>";
            $html .= "<th>CODIGO CATRASTRAL</th>";
            $html .= "<th>PROPIETARIO</th>";
            $html .= "<th>VIGENCIA ADEUDADA</th>";
            $html .= "<th>IMPUESTO PREDIAL</th>";
            $html .= "<th>INTERES PREDIAL</th>";
            $html .= "<th>CAS</th>";
            $html .= "<th>INTERES CAS</th>";
            $html .= "<th>DESCUENTO</th>";
            $html .= "<th>PAPELERIA</th>";
            $html .= "<th>SOBRETASA BOMBERIL</th>";
            $html .= "<th>DEBIDO COBRAR</th>";
            $html .= "<th>TOTAL</th>";
            $html .= "</tr>";
            $html .= "</thead>";
            $html .= "<tbody>";
            foreach ($xData as $row) {
                $xxx = $row->imp + $row->mora + $row->corp + $row->intc + $row->dest + $row->pape + $row->sobr + $row->cobr;
                $html .= "<tr>";
                $html .= "<td>$row->cod</td>";
                $html .= "<td>$row->prop</td>";
                $html .= "<td>$row->debe</td>";
                $html .= "<td>".number_format($row->imp, 2, ',', '.')."</td>";
                $html .= "<td>".number_format($row->mora, 2, ',', '.')."</td>";
                $html .= "<td>".number_format($row->corp, 2, ',', '.')."</td>";
                $html .= "<td>".number_format($row->intc, 2, ',', '.')."</td>";
                $html .= "<td>".number_format($row->dest, 2, ',', '.')."</td>";
                $html .= "<td>".number_format($row->pape, 2, ',', '.')."</td>";
                $html .= "<td>".number_format($row->sobr, 2, ',', '.')."</td>";
                $html .= "<td>".number_format($row->cobr, 2, ',', '.')."</td>";
                $html .= "<td>".number_format($xxx, 2, ',', '.')."</td>";
                $html .= "</tr>";
            }
            $html .= "</tbody>";
            $html .= "</table>";
            $html .= "</body>";
            $html .= "</html>";
            echo $html;
        }
    }

    public function listadoDebidoCobrar(){
        header("Content-type: application/vnd.ms-excel");
        header("Content-Disposition: attachment; filename=ListadosDeudoresDebidoCobrar.xls");
        $compania = ListadoPagosController::obtenerCompania();
        $html = "";
        $html .= "<html>";
        $html .= "<body>";
        $html .= "<table style='width: 100%;' border='1'>";
        $html .= "<thead>";
        $html .= "<caption> $compania->razonsocial <br/> NIT :$compania->numeroi <br/> ".utf8_decode("LISTADO MOROSOS DEBIDO COBRAR")." </caption>";
        $html .= "<tr>";
        $html .= "<th>CODIGO CATRASTRAL</th>";
        $html .= "<th>PROPIETARIO</th>";
        $html .= "<th>VIGENCIA ADEUDADA</th>";
        $html .= "<th>DEBIDO COBRAR</th>";
        $html .= "</tr>";
        $html .= "</thead>";
        $html .= "<tbody>";
        $xData = DB::table('gp_predio1 as gpr')
            ->select(
                'gpr.id_unico',
                'gpr.codigo_catastral as cod',
                'gps.nombres as prop'
            )
            ->leftJoin('gp_tercero_predio as gtp', 'gtp.predio', '=', 'gpr.id_unico')
            ->leftJoin('gr_propietarios as gps', 'gtp.tercero', '=', 'gps.id_unico')
            ->where('gtp.propietario', '=', 0)
            ->where('gtp.orden', '=', '001')
            ->where('gpr.estado', '=', 2)
            ->get();
        foreach ($xData as $row) {
            $xxx = ListadoPrediosControntroller::buscarValorDebidoCobrar($row->id_unico, date("Y"), 9);
            if(empty($xxx)){
                $xvalor = DB::table('gr_debido_valor')->select('valor')->where('predio', $row->id_unico)->first();
                $xxxA = ExtractoController::obtnerultimoannopago($row->id_unico);
                if(empty($xxxA)){
                    $xxxA = date("Y");
                }
                if(!empty($xvalor)){
                    $html .= "<tr>";
                    $html .= "<td>'$row->cod'</td>";
                    $html .= "<td>$row->prop</td>";
                    $html .= "<td>$xxxA</td>";
                    $html .= "<td>".number_format($xvalor->valor, 2, ',', '.')."</td>";
                    $html .= "</tr>";
                }
            }
        }
        $html .= "</tbody>";
        $html .= "</table>";
        $html .= "</body>";
        $html .= "</html>";
        echo $html;
    }

    public static function listadoDebidoExcel(Request $request){
        try {
            header("Content-type: application/vnd.ms-excel");
            header("Content-Disposition: attachment; filename=ListadosDebidoCobrar.xls");
            $compania = ListadoPagosController::obtenerCompania();
            $param    = session()->get('anno');
            $html = "";
            $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>RELACIÓN DE RECAUDOS DE CONCEPTO DEBIDO COBRAR</title>";
            $html .= "</head>";
            $html .= "<body>";
            $html .= "<table style='width: 100%;' border='1'>";
            $html .= "<thead>";
            $html .= "<caption>$compania->razonsocial<br/>"."NIT :".$compania->numeroi."<br/>RELACIÓN DE RECAUDOS DE CONCEPTO DEBIDO COBRAR<br/>ENTRE $request->txtFechaI Y $request->txtFechaF</caption>";
            $html .= "<tr>";
            $html .= "<th>CODIGO CATASTRAL</th>";
            $html .= "<th>PROPIETARIO</th>";
            $html .= "<th>Nº FACTURA</th>";
            $html .= "<th>FECHA DE PAGO</th>";
            $html .= "<th>DEBIDO COBRAR</th>";
            $html .= "</tr>";
            $html .= "</thead>";
            $html .= "<tbody>";
            $ffi    = explode("/", $request->txtFechaI);
            $fff    = explode("/", $request->txtFechaF);
            $fechaI = "$ffi[2]-$ffi[1]-$ffi[0]";
            $fechaF = "$fff[2]-$fff[1]-$fff[0]";
            $data   = DB::table('gr_detalle_pago_predial as dtp')
                        ->select(
                            'pre.codigo_catastral as cod', 'pro.nombres as propietario', 'fat.numero as num',
                            DB::raw('DATE_FORMAT(fat.fechafactura, "%d/%m/%Y") as fecha'),
                            DB::raw('sum(if(con.id_concepto = 9, dtp.valor, 0)) as dbd'),
                            'pag.tipopago as tpgo'
                        )
                        ->leftJoin('gr_detalle_factura_predial as dtf', 'dtp.detallefactura', '=', 'dtf.id_unico')
                        ->leftJoin('gr_pago_predial as pag', 'dtp.pago', '=', 'pag.id_unico')
                        ->leftJoin('gr_factura_predial as fat', 'dtf.factura', '=', 'fat.id_unico')
                        ->leftJoin('gr_concepto_predial as con', 'dtf.concepto', '=', 'con.id_unico')
                        ->leftJoin('gp_predio1 as pre', 'fat.predio', '=',  'pre.id_unico')
                        ->leftJoin('gp_tercero_predio as tpr', 'tpr.predio', '=', 'pre.id_unico')
                        ->leftJoin('gr_propietarios as pro', 'tpr.tercero', '=', 'pro.id_unico')
                        ->where('pag.parametrizacionanno', '=', $param)
                        ->where('pag.tipopago', '!=', 6)
                        ->whereBetween('pag.fechapago', [$fechaI, $fechaF])
                        ->where('tpr.propietario', '=', 0)
                        ->where('tpr.orden', '=', '001')
                        ->orderBy('pag.fechapago', 'ASC')
                        ->groupBy('pag.id_unico')
                        ->distinct()
                        ->get();
            list($deb) = array(0);
            foreach ($data as $row){
                $xdb = $row->dbd;

                if($row->tpgo == 7){
                    $deb -= $xdb;
                }else{
                    $deb += $xdb;
                }

                $html .= "<tr>";
                $html .= "<td style='text-align: center;'>'$row->cod'</td>";
                $html .= "<td style='text-align: left;'>$row->propietario</td>";
                $html .= "<td style='text-align: right;'>$row->num</td>";
                $html .= "<td style='text-align: right;'>$row->fecha</td>";
                $html .= "<td style='text-align: right;'>".number_format($xdb, 0, '.', ',')."</td>";
                $html .= "</tr>";
            }
            $html .= "</tbody>";
            $html .= "<tfoot>";
            $html .= "<tr>";
            $html .= "<th colspan='4' style='text-align: center;'>TOTALES</th>";
            $html .= "<th style='text-align: right;'>".number_format($deb, 0, '.', ',')."</th>";
            $html .= "</tr>";
            $html .= "</tfoot>";
            $html .= "</table>";
            $html .= "</body>";
            $html .= "</html>";
            echo $html;
        } catch (Exception $e) {
            return $e->getMessage();
        }
    }
        public static function informeConceptosExcel(Request $request){
        ini_set('max_execution_time', 0);
        $param    = session()->get('anno');
        $compania = ListadoPagosController::obtenerCompania();
        $ffi    = explode("/", $request->txtFechaI);
        $fff    = explode("/", $request->txtFechaF);
        $fechaI = trim("$ffi[2]-$ffi[1]-$ffi[0]");
        $fechaF = trim("$fff[2]-$fff[1]-$fff[0]");
            $data   = DB::table('gr_detalle_pago_predial as dtp')
                        ->select(
                            'pre.codigo_catastral as cod', 
                            // 'pro.nombres as propietario', 
                            'fat.numero as num',
                            DB::raw('DATE_FORMAT(pag.fechapago, "%d/%m/%Y") as fecha'),
                            DB::raw('sum(if(con.id_concepto = 3, dtp.valor, 0)) as cas'),
                            DB::raw('sum(if(con.id_concepto = 8, dtp.valor, 0)) as ins'),
                            'pag.tipopago as tpgo','dtp.id_unico as ids','pre.id_unico as predio'
                        )
                        ->leftJoin('gr_detalle_factura_predial as dtf', 'dtp.detallefactura', '=', 'dtf.id_unico')
                        ->leftJoin('gr_pago_predial as pag', 'dtp.pago', '=', 'pag.id_unico')
                        ->leftJoin('gr_factura_predial as fat', 'dtf.factura', '=', 'fat.id_unico')
                        ->leftJoin('gr_concepto_predial as con', 'dtf.concepto', '=', 'con.id_unico')
                        ->leftJoin('gp_predio1 as pre', 'fat.predio', '=',  'pre.id_unico')
                        // ->leftJoin('gp_tercero_predio as tpr', 'tpr.predio', '=', 'pre.id_unico')
                        // ->leftJoin('gr_propietarios as pro', 'tpr.tercero', '=', 'pro.id_unico')
                        ->whereBetween('pag.fechapago', [$fechaI, $fechaF])
                        // ->where('tpr.propietario', '=', 0)
                        // ->where('tpr.orden', '=', '001')
                        ->where('pag.parametrizacionanno', '=', $param)
                        ->where('pag.tipopago', '!=', 6)
                        ->groupBy('pag.id_unico')
                        ->orderBy('pag.fechapago', 'ASC')
                        ->distinct()
                        ->get();
            header("Content-type: application/vnd.ms-excel");
            header("Content-Disposition: attachment; filename=RelacionConceptosCAS.xls");
            $html = "";
            $html .= "<html>";
            $html .= "<body>";
            $html .= "<table style='width: 100%;' border='1'>";
            $html .= "<thead>";
            $html .= "<caption> $compania->razonsocial<br/> NIT :$compania->numeroi <br/> ".utf8_decode("RELACIÓN DE RECAUDOS DE CONCEPTO  CAS")."<br/> ENTRE $request->txtFechaI Y $request->txtFechaF </caption>";
            $html .= "<tr>";
            $html .= "<th>CODIGO CATRASTRAL</th>";
            $html .= "<th>PROPIETARIO</th>";
            $html .= "<th>N FACTURA</th>";
            $html .= "<th>FECHA DE PAGO </th>";
            $html .= "<th>CAS</th>";
            $html .= "<th>INTERES</th>";
            $html .= "<th>TOTAL</th>";
            $html .= "</tr>";
            $html .= "</thead>";
            $html .= "<tbody>";
            $imp = 0;$int = 0;$xxx = 0;
            foreach ($data as $row) {
                $dataProp = DB::table('gp_tercero_predio as tp')
                ->select('tr.nombres as propietario')
                ->leftJoin('gr_propietarios as tr', 'tp.tercero', '=', 'tr.id_unico')
                ->where('tp.propietario', '=', 0)
                ->where('tp.orden', '=', '001')
                ->where('tp.predio', '=', $row->predio)
                ->groupBy('tp.tercero')
                ->get();
        
                // Verificar si hay resultados antes de acceder al índice 0
                if ($dataProp->count() > 0) {
                    $propietario = $dataProp[0]->propietario;
                } else {
                    $propietario = "-";
                }
                $xmp  = $row->cas;
                $xin  = $row->ins;
                $xtt  = $xmp + $xin;

              
                    $imp += $xmp; $int += $xin; $xxx += $xtt;
                
                $html .= "<tr>";
                $html .= "<td>"."'".$row->cod."'"."</td>";
                $html .= "<td>$propietario</td>";
                $html .= "<td>$row->num</td>";
                $html .= "<td>".$row->fecha."</td>";
                $html .= "<td>". number_format($xmp, 0, '.', ',')."</td>";
                $html .= "<td>".number_format($xin, 0, '.', ',')."</td>";
                $html .= "<td>".number_format($xtt, 0, '.', ',')."</td>";
                $html .= "</tr>";
            }
            $html .= "<tr>";
            $html .= "<td colspan='4'>Total</td>";
            $html .= "<td>". number_format($imp, 0, '.', ',')."</td>";
            $html .= "<td>".number_format($int, 0, '.', ',')."</td>";
            $html .= "<td>".number_format($xxx, 0, '.', ',')."</td>";
            $html .= "</tr>";
            $html .= "</tbody>";
            $html .= "</table>";
            $html .= "</body>";
            $html .= "</html>";
            echo $html;
        
    }

 public static function informeCartera(Request $request){
        ini_set('max_execution_time', 0);
        $param    = session()->get('anno');
        $fechaCartera = $request->sltCartera;
        $compania = ListadoPagosController::obtenerCompania();
                $fechaCa = DB::table('gr_cartera_obligada as dtf')
                       ->select('dtf.fecha')
                       ->orderBy('dtf.id_unico', 'DESC')
                       ->distinct()
                       ->first();

               $data = DB::table('gr_cartera_obligada as gco')
                        ->leftJoin('gp_predio1 as gpr', 'gco.predio', '=', 'gpr.id_unico')
                        ->leftJoin('gr_avaluo as gav', 'gco.avaluo', '=', 'gav.id_unico')
                        ->leftJoin('gr_concepto as gct', 'gco.concepto', '=', 'gct.id_unico')
                         ->leftJoin('gp_tercero_predio as tpr', 'tpr.predio', '=', 'gpr.id_unico')
                        ->leftJoin('gr_propietarios as pro', 'tpr.tercero', '=', 'pro.id_unico')
                        ->leftJoin('gp_tipo_predio as tp', 'tp.id_unico', '=', 'gpr.tipo_predio')
                        ->leftJoin('gf_tipo_identificacion as ti', 'ti.id_unico', '=', 'pro.tipoidentificacion')
                        ->where('gco.fecha', $fechaCartera)
                        ->where('tpr.propietario','=','0')
                        ->select(
                            'gpr.codigo_catastral as cod',
                            DB::raw('(sum(if(gco.concepto = 1, gco.valor, 0))+
                            sum(if(gco.concepto = 3, gco.valor, 0)) +
                            sum(if(gco.concepto = 7, gco.valor, 0)) +
                            sum(if(gco.concepto = 6, gco.valor, 0)) +
                            sum(if(gco.concepto = 9, gco.valor, 0))) as total_imp'),
                            DB::raw('(sum(if(gco.concepto = 4, gco.valor, 0))+
                                     sum(if(gco.concepto = 8, gco.valor, 0))) as int_p'),
                            DB::raw('MIN(gav.anno) as menor'),
                            DB::raw('MAX(gav.anno)as mayor'),
                            'gco.predio','pro.nombres as propietario', DB::raw('CONCAT_WS(" - ",tp.codigo,tp.nombre) as tipop'),
                            'gpr.direccion as direc','ti.sigla as tipoi','pro.numero as numeroi'
                        )
                        ->groupBy('gco.predio')
                        ->orderBy('gpr.codigo_catastral', 'ASC')
                        //->orderBy('gav.anno', 'ASC')
                        ->distinct()
                        ->get();
            header("Content-type: application/vnd.ms-excel");
            header("Content-Disposition: attachment; filename=InformeCarteraConsolidado.xls");
            $html = "";
            $html .= "<html>";
            $html .= "<body>";
            $html .= "<table style='width: 100%;' border='1'>";
            $html .= "<thead>";
            $fechaAc=date('d-m-Y');
            $html .= "<caption><b> $compania->razonsocial<br/> NIT :$compania->numeroi <br/> ".utf8_decode("INFORME CARTERA CONSOLIDADO")."<br/> FECHA CARTERA: $fechaCartera</b></caption>";
            $html .= "<tr>";
            $html .= "<th style='border: solid 1px #000;'>CODIGO CATRASTRAL</th>";
            $html .= "<th style='border: solid 1px #000;'>TIPO PREDIO</th>";
            $html .= "<th style='border: solid 1px #000;'>".utf8_decode('DIRECCIÓN')."</th>";
            $html .= "<th style='border: solid 1px #000;'>NOMBRE PROPIETARIO</th>";
            $html .= "<th style='border: solid 1px #000;'>TIPO DOCUMENTO IDENTIDAD</th>";
            $html .= "<th style='border: solid 1px #000;'>NUMERO DOCUMENTO IDENTIDAD</th>";
            $html .= "<th style='border: solid 1px #000;'>".utf8_decode('AÑO DEUDA INICIAL')."</th>";
            $html .= "<th style='border: solid 1px #000;'>".utf8_decode('AÑO DEUDA FINAL')."</th>";
            $html .= "<th style='border: solid 1px #000;'>TOTAL CAPITAL IMPUESTO Y SOBRETASA</th>";
            $html .= "<th style='border: solid 1px #000;'>TOTAL INTERESES IMPUESTO Y SOBRE TASA</th>";
            $html .= "<th style='border: solid 1px #000;'>TOTAL IMPUESTO</th>";
            $html .= "</tr>";
            $html .= "</thead>";
            $html .= "<tbody>";
            $imp = 0;$int = 0;$xxx = 0; $cor=0; $icp=0;$sol=0;$des=0;$deb=0;$pap=0;
            $tc=0;
            $tci=0;
            $tcf=0;

            foreach ($data as $rowF) {
                 //bUSCAMOS AÑO DEUDA INICIAL

                    $totalP=$rowF->total_imp+$rowF->int_p;
                    $html .= "<tr>";
                    $html .= "<td style='border: solid 1px #000;'><b>"."'".$rowF->cod."'"."</b></td>";
                    $html .= "<td style='border: solid 1px #000;'><b>".$rowF->tipop."</b></td>";
                    $html .= "<td style='border: solid 1px #000;'><b>".$rowF->direc."</b></td>";
                    $html .= "<td style='border: solid 1px #000;'><b>$rowF->propietario</td>";
                    $html .= "<td style='border: solid 1px #000;'><b>$rowF->tipoi</b></td>";
                    $html .= "<td style='border: solid 1px #000;'><b>$rowF->numeroi</b></td>";
                    $html .= "<td style='border: solid 1px #000;'><b>".$rowF->menor."</b></td>";
                    $html .= "<td style='border: solid 1px #000;'><b>".$rowF->mayor."</b></td>";
                    $html .= "<td style='text-align: right;border: solid 1px #000;'>".number_format($rowF->total_imp, 2, ',', '.')."</td>";
                    $html .= "<td  style='text-align: right;border: solid 1px #000;'>".number_format($rowF->int_p, 2, ',', '.')."</td>";
                    $html .= "<td  style='text-align: right;border: solid 1px #000;'>".  number_format($totalP, 2, ',', '.')."</td>";
                    $html .= "</tr>";
                    $tc+=$rowF->total_imp;
                    $tci+=$rowF->int_p;
                    $tcf+=$totalP;

                
            }

            $html .= "<tr>";
            $html .= "<td colspan='8' style='border: solid 1px #000;text-align: right;'><b>TOTALES</b></td>";
            $html .= "<td style='text-align: right;border: solid 1px #000;'>". number_format($tc, 0, '.', ',')."</td>";
            $html .= "<td style='text-align: right;border: solid 1px #000;'>".number_format($tci, 0, '.', ',')."</td>";
            $html .= "<td style='text-align: right;border: solid 1px #000;'>".number_format($tcf, 0, '.', ',')."</td>";
            $html .= "</tr>";
            $html .= "</tbody>";
            $html .= "</table>";
            $html .= "</body>";
            $html .= "</html>";
            echo $html;
        
    }
}