<?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 Fpdf;
use Predial\ParametrizacionAnio;

class RecaudosInfController extends Controller{
    public function index(){
        $compania = session()->get('compania');
        $param    = session()->get('anno');
        $bancoI   = RecaudosInfController::getBank($compania, $param, "ASC");
        $bancoF   = RecaudosInfController::getBank($compania, $param, "DESC");
        return view('informesPredial.generarRecaudo', compact('bancoI', 'bancoF'));
    }
    public static function listadoFacturaSinR(){
        return view('informesPredial.listadoFacturasSinRecaudo');
    }

    public static function generarFacturaSinRecaudo(Request $request){
        $compania = ListadoPagosController::obtenerCompania();
        $fecha_i  = explode("/", $request->txtFechaI);
        $fecha_f  = explode("/", $request->txtFechaF);
        $checkCon=$request->check_resumen;
        $fechaI   = "$fecha_i[2]-$fecha_i[1]-$fecha_i[0]";
        $fechaF   = "$fecha_f[2]-$fecha_f[1]-$fecha_f[0]";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="ListadoFacturaSinRecaudo.xls"');
        return view('informesPredial.listadoFacturaSinRecaudo', compact('compania', 'fechaI', 'fechaF','checkCon'));
    }


    public static function getBank($compania, $param, $orden){
        $data   = DB::table('gf_cuenta_bancaria as cta')
                    ->select(
                            'cta.id_unico',
                            'cta.numerocuenta as cod',
                            'cta.descripcion'
                        )
                    ->leftJoin('gf_cuenta_bancaria_tercero as cter', 'cter.cuentabancaria', '=', 'cta.id_unico')
                    ->where('cter.tercero', '=', $compania)
                    ->where('cta.parametrizacionanno', '=', $param)
                    ->orderBy('cta.id_unico', $orden)
                    ->get();
        return $data;
    }

    public function view_inf(Request $request){
    	$anno   = session()->get('anno');
        $vige   = ParametrizacionAnio::find($anno);
        $fIN    = $request->txtFechaI;
        $fFF    = $request->txtFechaF;
        $bancoI = $request->sltBancoI;
        $bancoF = $request->sltBancoF;
        $ffI    = explode("/","$request->txtFechaI");
        $fechaI = "$ffI[2]-$ffI[1]-$ffI[0]";
        $ffF    = explode("/","$request->txtFechaF");
        $fechaF = "$ffF[2]-$ffF[1]-$ffF[0]";
    	$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();

        $nomCon = DB::table('gr_concepto_predial as cp')
                        ->select('c.nombre as cnom')
                        ->leftJoin('gr_concepto as c', 'cp.id_concepto', '=', 'c.id_unico')
                        ->where('cp.anno', '=', $vige->anno)
                        ->orderBy('c.id_unico','ASC')
                        ->get();

        $view  = \View::make('informesPredial.infRecaudos', compact(['compania','dataPredio','dataConcepto','fIN','fFF','nomCon', 'fechaI', 'fechaF', 'bancoI', 'bancoF']))->render();
        $pdf   = \App::make('dompdf.wrapper');
        $pdf->setPaper("legal","landscape");
        $pdf->loadHTML($view);
        return $pdf->stream("Recaudo_Diario.pdf");
    }

    public static function pdf(Request $request){
        $compania = ListadoPagosController::obtenerCompania();
        $param    = session()->get('anno');
        $pdf      = new Fpdf();
        $pdf::AddPage("L", "Legal");
        $pdf::SetFont('Arial','B', 10);
        $pdf::SetY(10);
        if ($param==6) {
            $ano='2021';
        }else if($param==10){
            $ano='2022';
        }else if($param==13){
            $ano='2023';
        }else if($param==14){
            $ano='2024';
        }else if($param==15){
            $ano='2025';
        }else if($param==16){
            $ano='2026';
        }
        
        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("RELACI�N DE RECAUDOS DE IMPUESTO PREDIAL"),0,0,'C');
        $pdf::Ln(6);
        $pdf::SetFont('Arial','B', 8);
        $pdf::Cell(30, 6, "COD. CATASTRAL", 'LTBR', 0, 'C');
        $pdf::Cell(15, 6, "VIGENCIA", 'LTBR', 0, 'C');
        $pdf::Cell(65, 6, "PROPIETARIO", 'LTBR', 0, 'C');
        $pdf::Cell(20, 6, "FACTURA", 'LTBR', 0, 'C');
        $pdf::Cell(20, 6, "FECHA P", 'LTBR', 0, 'C');
        $pdf::Cell(20, 6, "IMPUESTO", 'LTBR', 0, 'C');
        $pdf::Cell(20, 6, "INTERES", 'LTBR', 0, 'C');
        $pdf::Cell(20, 6, "CAS", 'LTBR', 0, 'C');
        $pdf::Cell(20, 6, "INT. CAS", 'LTBR', 0, 'C');
        $pdf::Cell(20, 6, "DESCUENTO", 'LTBR', 0, 'C');
        $pdf::Cell(20, 6, "PAPELERIA", 'LTBR', 0, 'C');
        $pdf::Cell(20, 6, "SOB. BOMB.", 'LTBR', 0, 'C');
        $pdf::Cell(20, 6, "D. COBRAR", 'LTBR', 0, 'C');
        $pdf::Cell(20, 6, "TOTAL", 'LTBR', 0, 'C');
        $pdf::Ln(6);
        $ffi    = explode("/", $request->txtFechaI);
        $fechaI = "$ffi[2]-$ffi[1]-$ffi[0]";
        $fff    = explode("/", $request->txtFechaF);
        $fechaF = "$fff[2]-$fff[1]-$fff[0]";
        $dataBancos = DB::table('gr_detalle_pago_predial as dtp')
                        ->select(
                            DB::raw('(if(
                                concat_ws(" ", ter.nombreuno, ter.nombredos, ter.apellidouno, ter.apellidodos) = " ",
                                ter.razonsocial,
                                concat_ws(" ", ter.nombreuno, ter.nombredos, ter.apellidouno, ter.apellidodos)
                            )) as nombrebanco'),
                            'cta.numerocuenta as nmcta',
                            'pag.banco as banco'
                        )
                        ->leftJoin('gr_pago_predial as pag', 'dtp.pago', '=', 'pag.id_unico')
                        ->leftJoin('gf_cuenta_bancaria as cta', 'pag.banco', '=', 'cta.id_unico')
                        ->leftJoin('gf_tercero as ter', 'cta.banco', '=', 'ter.id_unico')
                        ->whereBetween('pag.fechapago', [$fechaI, $fechaF])
                        ->whereBetween('cta.id_unico', [$request->sltBancoI, $request->sltBancoF])
                        ->groupBy('pag.banco')
                        ->get();
        $imp = $sob = $cor =  $int = $des = $pap = $sbm = $inc = $deb = $xxx = $xant = $xact = $impA = $sobA = $corA = $intA = $desA = $papA = 0;
        $sbmA = $incA = $debA = $impH = $sobH = $corH = $intH = $desH = $papH = $sbmH = $incH = $debH = $antA = 0;
        foreach ($dataBancos as $value) {
            list($ximp, $xsob ,$xcor, $xint, $xdes, $xpap, $xsbm, $xinc, $xdeb, $xxxx) = array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
            $dataPredio = DB::table('gr_detalle_pago_predial as dtp')
                            ->select(
                                'predio.codigo_catastral',
                                'tr.nombres as propietario',
                                'fp.numero',
                                DB::raw("DATE_FORMAT(pago.fechapago, '%d/%m/%Y') as anno"),
                                DB::raw('sum(if(cp.id_concepto = 1, dtp.valor, 0)) as impuesto'),
                                DB::raw('sum(if(cp.id_concepto = 3, dtp.valor, 0)) as corporacion_regional'),
                                DB::raw('sum(if(cp.id_concepto = 2, dtp.valor, 0)) as sobretasa'),
                                DB::raw('sum(if(cp.id_concepto = 5, dtp.valor, 0)) as descuento'),
                                DB::raw('sum(if(cp.id_concepto = 4, dtp.valor, 0)) as interes_mora'),
                                DB::raw('sum(if(cp.id_concepto = 8, dtp.valor, 0)) as interes_corporacion'),
                                DB::raw('sum(if(cp.id_concepto = 7, dtp.valor, 0)) as Sobretasa_Bomberil'),
                                DB::raw('sum(if(cp.id_concepto = 9, dtp.valor, 0)) as Debido_Cobrar'),
                                DB::raw('sum(if(cp.id_concepto = 6, dtp.valor, 0)) as papeleria'),
                                'pago.tipopago',
                                'pago.parametrizacionanno as param',
                                'av.anno as vig'
                            )
                            ->leftJoin('gr_pago_predial as pago', 'dtp.pago', '=', 'pago.id_unico')
                            ->leftJoin('gr_detalle_factura_predial as dtf', 'dtp.detallefactura', '=', 'dtf.id_unico')
                            ->leftJoin('gr_factura_predial as fp', 'dtf.factura', '=', 'fp.id_unico')
                            ->leftJoin('gp_predio1 as predio', 'fp.predio', '=', 'predio.id_unico')
                            ->leftJoin('gp_tercero_predio as tp', 'tp.predio', '=', 'predio.id_unico')
                            ->leftJoin('gr_propietarios as tr', 'tp.tercero', '=', 'tr.id_unico')
                            ->leftJoin('gr_avaluo as av', 'dtf.avaluo', '=', 'av.id_unico')
                            ->leftJoin('gr_concepto_predial as cp', 'dtf.concepto', '=', 'cp.id_unico')
                            ->whereBetween('pago.fechapago', [$fechaI, $fechaF])
                            ->where('pago.banco', '=', $value->banco)
                            ->where('tp.propietario', '=', 0)
                            ->where('tp.orden', '=', '001')
                            // ->where('pago.tipopago', '!=', 6)
                            ->orderBy('pago.fechapago', 'ASC')
                            ->orderBy('predio.id_unico', 'ASC')
                            ->orderBy('av.anno', 'ASC')
                            ->orderBy('tp.tercero', 'ASC')
                            ->orderBy('pago.id_unico', 'ASC')
                            ->groupBy('dtf.avaluo')
                            ->groupBy('pago.id_unico')
                            ->get();
            if(count($dataPredio) > 0){
                if($value->nmcta != '99999990'){
                    $pdf::SetFont('Arial','B', 8);
                    $pdf::Cell(30, 5, "BANCO:", 0, 0, 'R');
                    $pdf::Cell(100, 5, $value->nombrebanco, 0, 0, 'L');
                    $pdf::Cell(20, 5, utf8_decode("N� CUENTA:"), 0, 0, 'R');
                    $pdf::Cell(100, 5, $value->nmcta, 0, 0, 'L');
                    $pdf::Ln(5);
                    foreach ($dataPredio as $row){
                        $x = 0;
                        if($row->tipopago == 7){
                            $descuento = $row->descuento>0?$row->descuento * -1:$row->descuento;
                        }else{
                            $descuento = $row->descuento<0?$row->descuento * -1:$row->descuento;
                        }
                        $pdf::SetFont('Arial','B', 8);
                        $pdf::Cell(30, 5, $row->codigo_catastral, 'LTBR', 0, 'C');
                        $pdf::Cell(15, 5, $row->vig, 'LTBR', 0, 'R');
                        $pdf::Cell(65, 5, $row->propietario, 'LTBR', 0, 'L');
                        $pdf::Cell(20, 5, $row->numero, 'LTBR', 0, 'R');
                        $pdf::Cell(20, 5, $row->anno, 'LTBR', 0, 'C');
                        $pdf::SetFont('Arial','', 8);
                        $pdf::Cell(20, 5, number_format($row->impuesto,0,'.',','), 'LTBR', 0, 'R');
                        $pdf::Cell(20, 5, number_format($row->interes_mora,0,'.',','), 'LTBR', 0, 'R');
                        $pdf::Cell(20, 5, number_format($row->corporacion_regional,0,'.',','), 'LTBR', 0, 'R');
                        $pdf::Cell(20, 5, number_format($row->interes_corporacion,0,'.',','), 'LTBR', 0, 'R');
                        if($row->tipopago == 7){
                            $pdf::Cell(20, 5, number_format($descuento,0,'.',','), 'LTBR', 0, 'R');
                        }else{
                            $pdf::Cell(20, 5, number_format($row->descuento<0?$row->descuento * -1:$row->descuento, 0, '.', ','), 'LTBR', 0, 'R');
                        }
                        $pdf::Cell(20, 5, number_format($row->papeleria,0,'.',','), 'LTBR', 0, 'R');
                        $pdf::Cell(20, 5, number_format($row->Sobretasa_Bomberil,0,'.',','), 'LTBR', 0, 'R');
                        $pdf::Cell(20, 5, number_format($row->Debido_Cobrar,0,'.',','), 'LTBR', 0, 'R');
                        $x = $row->impuesto + $row->sobretasa + $row->corporacion_regional + $row->interes_mora + $row->descuento + $row->papeleria + $row->Sobretasa_Bomberil + $row->interes_corporacion + $row->Debido_Cobrar;
                        $pdf::Cell(20, 5, number_format($x,0,'.',','), 'LTBR', 0, 'R');
                        $pdf::Ln(5);
                        $ximp += $row->impuesto;
                        $xsob += $row->sobretasa;
                        $xcor += $row->corporacion_regional;
                        $xint += $row->interes_mora;
                        if($row->tipopago == 7){
                            $xdes += $descuento;
                            $des  += $descuento;
                        }else{
                            $xdes += $row->descuento<0?$row->descuento * -1:$row->descuento;
                            $des  += $row->descuento<0?$row->descuento * -1:$row->descuento;
                        }
                        $xpap += $row->papeleria;
                        $xsbm += $row->Sobretasa_Bomberil;
                        $xinc += $row->interes_corporacion;
                        $xdeb += $row->Debido_Cobrar;
                        $xxxx += $x;
                        $imp += $row->impuesto;
                        $sob += $row->sobretasa;
                        $cor += $row->corporacion_regional;
                        $int += $row->interes_mora;
                        $pap += $row->papeleria;
                        $sbm += $row->Sobretasa_Bomberil;
                        $inc += $row->interes_corporacion;
                        $deb += $row->Debido_Cobrar;
                        $xxx += $x;

                        if($row->vig == $ano){
                            $xact += $x;
                            $impH += $row->impuesto;
                            $sobH += $row->sobretasa;
                            $corH += $row->corporacion_regional;
                            $intH += $row->interes_mora;
                            if($row->tipopago == 7){
                                $desH += $descuento;
                            }else{
                                $desH += $row->descuento<0?$row->descuento * -1:$row->descuento;
                            }
                            $papH += $row->papeleria;
                            $sbmH += $row->Sobretasa_Bomberil;
                            $incH += $row->interes_corporacion;
                            $debH += $row->Debido_Cobrar;
                        }else{
                            $xant += $x;
                            $impA += $row->impuesto;
                            $sobA += $row->sobretasa;
                            $corA += $row->corporacion_regional;
                            $intA += $row->interes_mora;
                            if($row->tipopago == 7){
                                $desA += $descuento;
                            }else{
                                $desA += $row->descuento<0?$row->descuento * -1:$row->descuento;
                            }
                            $papA += $row->papeleria;
                            $sbmA += $row->Sobretasa_Bomberil;
                            $incA += $row->interes_corporacion;
                            $debA += $row->Debido_Cobrar;
                        }
                    }
                    $pdf::SetFont('Arial','B', 8);
                    $pdf::Cell(150, 5, "TOTAL BANCO", 'LTBR', 0, 'C');
                    $pdf::SetFont('Arial','', 8);
                    $pdf::Cell(20, 5, number_format($ximp, 0, '.', ','), 'LTBR', 0, 'R');
                    $pdf::Cell(20, 5, number_format($xint, 0, '.', ','), 'LTBR', 0, 'R');
                    $pdf::Cell(20, 5, number_format($xcor, 0, '.', ','), 'LTBR', 0, 'R');
                    $pdf::Cell(20, 5, number_format($xinc, 0, '.', ','), 'LTBR', 0, 'R');
                    $pdf::Cell(20, 5, number_format($xdes, 0, '.', ','), 'LTBR', 0, 'R');
                    $pdf::Cell(20, 5, number_format($xpap, 0, '.', ','), 'LTBR', 0, 'R');
                    $pdf::Cell(20, 5, number_format($xsbm, 0, '.', ','), 'LTBR', 0, 'R');
                    $pdf::Cell(20, 5, number_format($xdeb, 0, '.', ','), 'LTBR', 0, 'R');
                    $pdf::Cell(20, 5, number_format($xxxx, 0, '.', ','), 'LTBR', 0, 'R');
                    $pdf::Ln(5);
                }
            }
        }
        $pdf::SetFont('Arial','B', 8);
        $pdf::Cell(150, 6, "TOTAL", 'LTBR', 0, 'C');
        $pdf::SetFont('Arial','', 8);
        $pdf::Cell(20, 6, number_format($imp, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 6, number_format($int, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 6, number_format($cor, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 6, number_format($inc, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 6, number_format($des, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 6, number_format($pap, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 6, number_format($sbm, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 6, number_format($deb, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 6, number_format($xxx, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Ln(10);
        $pdf::SetFont('Arial','B', 8);
        $pdf::Cell(330, 5, utf8_decode("RESUMEN DE VIGENCIAS"), 'LBTR', 0, 'C');
        $pdf::Ln(5);
        $pdf::Cell(165, 5, "VIGENCIAS ANTERIORES", 'LBTR', 0, 'C');
        $pdf::Cell(165, 5, "VIGENCIA ACTUAL", 'LBTR', 0, 'C');
        $pdf::Ln(5);
        $pdf::Cell(25, 5, "IMPUESTO", 'LTBR', 0, 'C');
        $pdf::Cell(20, 5, "INTERES", 'LTBR', 0, 'C');
        $pdf::Cell(20, 5, "CAS", 'LTBR', 0, 'C');
        $pdf::Cell(20, 5, "INT. CAS", 'LTBR', 0, 'C');
        $pdf::Cell(20, 5, "DESCUENTO", 'LTBR', 0, 'C');
        $pdf::Cell(20, 5, "PAPELERIA", 'LTBR', 0, 'C');
        $pdf::Cell(20, 5, "SOB. BOMB", 'LTBR', 0, 'C');
        $pdf::Cell(20, 5, "DEB.COBRAR", 'LTBR', 0, 'C');
        $pdf::Cell(25, 5, "IMPUESTO", 'LTBR', 0, 'C');
        $pdf::Cell(20, 5, "INTERES", 'LTBR', 0, 'C');
        $pdf::Cell(20, 5, "CAS", 'LTBR', 0, 'C');
        $pdf::Cell(20, 5, "INT. CAS", 'LTBR', 0, 'C');
        $pdf::Cell(20, 5, "DESCUENTO", 'LTBR', 0, 'C');
        $pdf::Cell(20, 5, "PAPELERIA", 'LTBR', 0, 'C');
        $pdf::Cell(20, 5, "SOB. BOMB", 'LTBR', 0, 'C');
        $pdf::Cell(20, 5, "DEB.COBRAR", 'LTBR', 0, 'C');
        $pdf::Ln(5);
        $param    = session()->get('anno');
        $pdf::SetFont('Arial','', 8);
        $pdf::Cell(25, 5, number_format($impA, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 5, number_format($intA, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 5, number_format($corA, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 5, number_format($incA, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 5, number_format($desA, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 5, number_format($papA, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 5, number_format($sbmA, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 5, number_format($debA, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(25, 5, number_format($impH, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 5, number_format($intH, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 5, number_format($corH, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 5, number_format($incH, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 5, number_format($desH, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 5, number_format($papH, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 5, number_format($sbmH, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 5, number_format($debH, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Ln(5);
        $pdf::SetFont('Arial','B', 8);
        $pdf::Cell(65, 5, "TOTAL VIGENCIAS ANTERIORES", 'LBTR', 0, 'R');
        $pdf::SetFont('Arial','', 8);
        $pdf::Cell(100, 5, number_format($xant, 0, '.', ','), 'LBTR', 0, 'R');
        $pdf::SetFont('Arial','B', 8);
        $pdf::Cell(65, 5, "TOTAL VIGENCIA ACTUAL", 'LBTR', 0, 'R');
        $pdf::SetFont('Arial','', 8);
        $pdf::Cell(100, 5, number_format($xact, 0, '.', ','), 'LBTR', 0, 'R');
        $pdf::Ln(5);
        $pdf::SetFont('Arial','B', 8);
        $pdf::Cell(230, 5, "TOTAL VIGENCIAS", 'LBTR', 0, 'R');
        $pdf::SetFont('Arial','', 8);
        $pdf::Cell(100, 5, number_format($xant + $xact, 0, '.', ','), 'LBTR', 0, 'R');
        $pdf::Output();
        exit;
    }
    public static function pdfR(Request $request){
        $compania = ListadoPagosController::obtenerCompania();
        $param    = session()->get('anno');
        $pdf      = new Fpdf();
        $pdf::AddPage("L", "Legal");
        $pdf::SetFont('Arial','B', 10);
        $pdf::SetY(10);
        if ($param==6) {
            $anio='2021';
        }else if($param==10){
            $anio='2022';
        }else if($param==13){
            $anio='2023';
        }else if($param==14){
            $anio='2024';
        }else if($param==15){
            $anio='2025';
        }else if($param==16){
            $anio='2026';
        }
        
        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("RELACI�N DE RECAUDOS DE IMPUESTO PREDIAL"),0,0,'C');
        $pdf::Ln(6);
      
        $ffi    = explode("/", $request->txtFechaI);
        $fechaI = "$ffi[2]-$ffi[1]-$ffi[0]";
        $fff    = explode("/", $request->txtFechaF);
        $fechaF = "$fff[2]-$fff[1]-$fff[0]";
        $dataBancos = DB::table('gr_detalle_pago_predial as dtp')
                        ->select(
                            DB::raw('(if(
                                concat_ws(" ", ter.nombreuno, ter.nombredos, ter.apellidouno, ter.apellidodos) = " ",
                                ter.razonsocial,
                                concat_ws(" ", ter.nombreuno, ter.nombredos, ter.apellidouno, ter.apellidodos)
                            )) as nombrebanco'),
                            'cta.numerocuenta as nmcta',
                            'pag.banco as banco'
                        )
                        ->leftJoin('gr_pago_predial as pag', 'dtp.pago', '=', 'pag.id_unico')
                        ->leftJoin('gf_cuenta_bancaria as cta', 'pag.banco', '=', 'cta.id_unico')
                        ->leftJoin('gf_tercero as ter', 'cta.banco', '=', 'ter.id_unico')
                        ->whereBetween('pag.fechapago', [$fechaI, $fechaF])
                        ->whereBetween('cta.id_unico', [$request->sltBancoI, $request->sltBancoF])
                        ->groupBy('pag.banco')
                        ->get();
        $imp = $sob = $cor =  $int = $des = $pap = $sbm = $inc = $deb = $xxx = $xant = $xact = $impA = $sobA = $corA = $intA = $desA = $papA = 0;
        $sbmA = $incA = $debA = $impH = $sobH = $corH = $intH = $desH = $papH = $sbmH = $incH = $debH = $antA = 0;
        foreach ($dataBancos as $value) {
            list($ximp, $xsob ,$xcor, $xint, $xdes, $xpap, $xsbm, $xinc, $xdeb, $xxxx) = array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
            $dataPredio = DB::table('gr_detalle_pago_predial as dtp')
                            ->select(
                                'predio.codigo_catastral',
                                'tr.nombres as propietario',
                                'fp.numero',
                                DB::raw("DATE_FORMAT(pago.fechapago, '%d/%m/%Y') as anno"),
                                DB::raw('sum(if(cp.id_concepto = 1, dtp.valor, 0)) as impuesto'),
                                DB::raw('sum(if(cp.id_concepto = 3, dtp.valor, 0)) as corporacion_regional'),
                                DB::raw('sum(if(cp.id_concepto = 2, dtp.valor, 0)) as sobretasa'),
                                DB::raw('sum(if(cp.id_concepto = 5, dtp.valor, 0)) as descuento'),
                                DB::raw('sum(if(cp.id_concepto = 4, dtp.valor, 0)) as interes_mora'),
                                DB::raw('sum(if(cp.id_concepto = 8, dtp.valor, 0)) as interes_corporacion'),
                                DB::raw('sum(if(cp.id_concepto = 7, dtp.valor, 0)) as Sobretasa_Bomberil'),
                                DB::raw('sum(if(cp.id_concepto = 9, dtp.valor, 0)) as Debido_Cobrar'),
                                DB::raw('sum(if(cp.id_concepto = 6, dtp.valor, 0)) as papeleria'),
                                'pago.tipopago',
                                'pago.parametrizacionanno as param',
                                'av.anno as vig'
                            )
                            ->leftJoin('gr_pago_predial as pago', 'dtp.pago', '=', 'pago.id_unico')
                            ->leftJoin('gr_detalle_factura_predial as dtf', 'dtp.detallefactura', '=', 'dtf.id_unico')
                            ->leftJoin('gr_factura_predial as fp', 'dtf.factura', '=', 'fp.id_unico')
                            ->leftJoin('gp_predio1 as predio', 'fp.predio', '=', 'predio.id_unico')
                            ->leftJoin('gp_tercero_predio as tp', 'tp.predio', '=', 'predio.id_unico')
                            ->leftJoin('gr_propietarios as tr', 'tp.tercero', '=', 'tr.id_unico')
                            ->leftJoin('gr_avaluo as av', 'dtf.avaluo', '=', 'av.id_unico')
                            ->leftJoin('gr_concepto_predial as cp', 'dtf.concepto', '=', 'cp.id_unico')
                            ->whereBetween('pago.fechapago', [$fechaI, $fechaF])
                            ->where('pago.banco', '=', $value->banco)
                            ->where('tp.propietario', '=', 0)
                            ->where('tp.orden', '=', '001')
                            // ->where('pago.tipopago', '!=', 6)
                            ->orderBy('pago.fechapago', 'ASC')
                            ->orderBy('predio.id_unico', 'ASC')
                            ->orderBy('av.anno', 'ASC')
                            ->orderBy('tp.tercero', 'ASC')
                            ->orderBy('pago.id_unico', 'ASC')
                            ->groupBy('dtf.avaluo')
                            ->groupBy('pago.id_unico')
                            ->get();
            if(count($dataPredio) > 0){
                if($value->nmcta != '99999990'){
                
                    foreach ($dataPredio as $row){
                        $x = 0;
                        if($row->tipopago == 7){
                            $descuento = $row->descuento>0?$row->descuento * -1:$row->descuento;
                        }else{
                            $descuento = $row->descuento<0?$row->descuento * -1:$row->descuento;
                        }
                        

                      
                        $x = $row->impuesto + $row->sobretasa + $row->corporacion_regional + $row->interes_mora + $row->descuento + $row->papeleria + $row->Sobretasa_Bomberil + $row->interes_corporacion + $row->Debido_Cobrar;
                      
                        $ximp += $row->impuesto;
                        $xsob += $row->sobretasa;
                        $xcor += $row->corporacion_regional;
                        $xint += $row->interes_mora;
                        if($row->tipopago == 7){
                            $xdes += $descuento;
                            $des  += $descuento;
                        }else{
                            $xdes += $row->descuento<0?$row->descuento * -1:$row->descuento;
                            $des  += $row->descuento<0?$row->descuento * -1:$row->descuento;
                        }
                        $xpap += $row->papeleria;
                        $xsbm += $row->Sobretasa_Bomberil;
                        $xinc += $row->interes_corporacion;
                        $xdeb += $row->Debido_Cobrar;
                        $xxxx += $x;
                        $imp += $row->impuesto;
                        $sob += $row->sobretasa;
                        $cor += $row->corporacion_regional;
                        $int += $row->interes_mora;
                        $pap += $row->papeleria;
                        $sbm += $row->Sobretasa_Bomberil;
                        $inc += $row->interes_corporacion;
                        $deb += $row->Debido_Cobrar;
                        $xxx += $x;

                        if($row->vig == $anio){
                            $xact += $x;
                            $impH += $row->impuesto;
                            $sobH += $row->sobretasa;
                            $corH += $row->corporacion_regional;
                            $intH += $row->interes_mora;
                            if($row->tipopago == 7){
                                $desH += $descuento;
                            }else{
                                $desH += $row->descuento<0?$row->descuento * -1:$row->descuento;
                            }
                            $papH += $row->papeleria;
                            $sbmH += $row->Sobretasa_Bomberil;
                            $incH += $row->interes_corporacion;
                            $debH += $row->Debido_Cobrar;
                        }else{
                            $xant += $x;
                            $impA += $row->impuesto;
                            $sobA += $row->sobretasa;
                            $corA += $row->corporacion_regional;
                            $intA += $row->interes_mora;
                            if($row->tipopago == 7){
                                $desA += $descuento;
                            }else{
                                $desA += $row->descuento<0?$row->descuento * -1:$row->descuento;
                            }
                            $papA += $row->papeleria;
                            $sbmA += $row->Sobretasa_Bomberil;
                            $incA += $row->interes_corporacion;
                            $debA += $row->Debido_Cobrar;
                        }
                    }
                    
                }
            }
        }
        
        $pdf::Ln(10);
        $pdf::SetFont('Arial','B', 8);
        $pdf::Cell(330, 5, utf8_decode("RESUMEN DE VIGENCIAS"), 'LBTR', 0, 'C');
        $pdf::Ln(5);
        $pdf::Cell(165, 5, "VIGENCIAS ANTERIORES", 'LBTR', 0, 'C');
        $pdf::Cell(165, 5, "VIGENCIA ACTUAL", 'LBTR', 0, 'C');
        $pdf::Ln(5);
        $pdf::Cell(25, 5, "IMPUESTO", 'LTBR', 0, 'C');
        $pdf::Cell(20, 5, "INTERES", 'LTBR', 0, 'C');
        $pdf::Cell(20, 5, "CAS", 'LTBR', 0, 'C');
        $pdf::Cell(20, 5, "INT. CAS", 'LTBR', 0, 'C');
        $pdf::Cell(20, 5, "DESCUENTO", 'LTBR', 0, 'C');
        $pdf::Cell(20, 5, "PAPELERIA", 'LTBR', 0, 'C');
        $pdf::Cell(20, 5, "SOB. BOMB", 'LTBR', 0, 'C');
        $pdf::Cell(20, 5, "DEB.COBRAR", 'LTBR', 0, 'C');
        $pdf::Cell(25, 5, "IMPUESTO", 'LTBR', 0, 'C');
        $pdf::Cell(20, 5, "INTERES", 'LTBR', 0, 'C');
        $pdf::Cell(20, 5, "CAS", 'LTBR', 0, 'C');
        $pdf::Cell(20, 5, "INT. CAS", 'LTBR', 0, 'C');
        $pdf::Cell(20, 5, "DESCUENTO", 'LTBR', 0, 'C');
        $pdf::Cell(20, 5, "PAPELERIA", 'LTBR', 0, 'C');
        $pdf::Cell(20, 5, "SOB. BOMB", 'LTBR', 0, 'C');
        $pdf::Cell(20, 5, "DEB.COBRAR", 'LTBR', 0, 'C');
        $pdf::Ln(5);
        $param    = session()->get('anno');
        $pdf::SetFont('Arial','', 8);
        $pdf::Cell(25, 5, number_format($impA, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 5, number_format($intA, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 5, number_format($corA, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 5, number_format($incA, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 5, number_format($desA, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 5, number_format($papA, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 5, number_format($sbmA, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 5, number_format($debA, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(25, 5, number_format($impH, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 5, number_format($intH, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 5, number_format($corH, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 5, number_format($incH, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 5, number_format($desH, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 5, number_format($papH, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 5, number_format($sbmH, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Cell(20, 5, number_format($debH, 0, '.', ','), 'LTBR', 0, 'R');
        $pdf::Ln(5);
        $pdf::SetFont('Arial','B', 8);
        $pdf::Cell(65, 5, "TOTAL VIGENCIAS ANTERIORES", 'LBTR', 0, 'R');
        $pdf::SetFont('Arial','', 8);
        $pdf::Cell(100, 5, number_format($xant, 0, '.', ','), 'LBTR', 0, 'R');
        $pdf::SetFont('Arial','B', 8);
        $pdf::Cell(65, 5, "TOTAL VIGENCIA ACTUAL", 'LBTR', 0, 'R');
        $pdf::SetFont('Arial','', 8);
        $pdf::Cell(100, 5, number_format($xact, 0, '.', ','), 'LBTR', 0, 'R');
        $pdf::Ln(5);
        $pdf::SetFont('Arial','B', 8);
        $pdf::Cell(230, 5, "TOTAL VIGENCIAS", 'LBTR', 0, 'R');
        $pdf::SetFont('Arial','', 8);
        $pdf::Cell(100, 5, number_format($xant + $xact, 0, '.', ','), 'LBTR', 0, 'R');
        $pdf::Output();
        exit;
    }
}
