<?php

namespace Predial\Http\Controllers;

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

class ListadoPagosController extends Controller{

    public static function index(){
        return view('informesPredial.listadoPago');
    }

    public function generate(Request $request)
    {
        $compania = $this->obtenerCompania();
        $fecha_i  = explode("/", $request->txtFechaI);
        $fecha_f  = explode("/", $request->txtFechaF);
        $fechaI   = "$fecha_i[2]-$fecha_i[1]-$fecha_i[0]";
        $fechaF   = "$fecha_f[2]-$fecha_f[1]-$fecha_f[0]";
        $bancoI   = $request->sltBancoI;
        $bancoF   = $request->sltBancoF;
        $resumen  = $request->chakRe;

        $html = view('pdf.listadoPagos', compact('compania', 'fechaI', 'fechaF', 'bancoI', 'bancoF'))->render();

        return Response::make($html, 200, [
            'Content-Type' => 'application/vnd.ms-excel',
            'Content-Disposition' => 'attachment; filename="ListadoPagos.xls"',
        ]);
    }

    public static function generateR(Request $request){
        $compania = ListadoPagosController::obtenerCompania();
        $fecha_i  = explode("/", $request->txtFechaI);
        $fecha_f  = explode("/", $request->txtFechaF);
        $fechaI   = "$fecha_i[2]-$fecha_i[1]-$fecha_i[0]";
        $fechaF   = "$fecha_f[2]-$fecha_f[1]-$fecha_f[0]";
        $bancoI   = $request->sltBancoI;
        $bancoF   = $request->sltBancoF;
        $resumen   = $request->chakRe;
         try {
             header('Content-type: application/vnd.ms-excel');
             header('Content-Disposition: attachment;filename="ListadoPagos.xls"');
            $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-collapse: collapse; font-family: 'Arial', sans-serif;'>";
            $html .="<thead>";
            $html .="<tr>";
            $html .="<th style='text-align: center' colspan='14'>$compania->razonsocial</th>";
            $html .="</tr>";
            $html .="<tr>";
            $html .="<th style='text-align: center' colspan='14'>$compania->tipodoc.' : '.$compania->numeroi</th>";
            $html .="</tr>";
            $html .="<tr style='font-size: 12px;'>";
            $html .="<th style='border: 1px solid #000'>COD. CATASTRAL</th>";
            $html .="<th style='border: 1px solid #000'>VIGENCIA</th>";
            $html .="<th style='border: 1px solid #000'>PROPIETARIO</th>";
            $html .="<th style='border: 1px solid #000'>FACTURA</th>";
            $html .="<th style='border: 1px solid #000'>FECHA PAGO</th>";
            $html .="<th style='border: 1px solid #000'>IMPUESTO</th>";
            $html .="<th style='border: 1px solid #000'>INTERES</th>";
            $html .="<th style='border: 1px solid #000'>CAS</th>";
            $html .="<th style='border: 1px solid #000'>INT. CAS</th>";
            $html .="<th style='border: 1px solid #000'>DESCUENTO</th>";
            $html .="<th style='border: 1px solid #000'>PAPELERIA</th>";
            $html .="<th style='border: 1px solid #000'>SOB. BOMB.</th>";
            $html .="<th style='border: 1px solid #000'>D. COBRAR</th>";
            $html .="<th style='border: 1px solid #000'>TOTAL</th>";
            $html .="</tr>";
            $html .="</thead>";
            $html .="<tbody>";
            list($imp, $sob ,$cor, $int, $des, $pap, $sbm, $inc, $deb, $xxx) = array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
            if(empty($bancoI)){
                $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])
                        ->groupBy('pag.banco')
                        ->get();
            }else{
                $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', [$bancoI, $bancoF])
                        ->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 = 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',
                    'av.anno as vig','predio.id_unico as predio'
                )
                ->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')
                ->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();
                // ->where('pago.tipopago', '!=', 6)
                if(count($dataPredio) > 0){
                        $html .="<tr style='font-size: 12px;'>";
                        $html .="<td colspan='1' style='text-align:left; font-weight: 800'>BANCO:</td>";
                        $html .="<td colspan='3' style='font-weight: 800'> $value->nombrebanco</td>";
                        $html .="<td colspan='1' style='text-align:left; font-weight: 800'>N° CUENTA:</td>";
                        $html .="<td colspan='6' style='font-weight: 800'> $value->nmcta</td>";
                        $html .="</tr>";

                    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;
                        }
                        $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();
                                    if ($dataProp->count() > 0) {
                                        $propietario = $dataProp[0]->propietario;
                                    } else {
                                        $propietario = "-";
                                    }  

                            $html .="<tr>";
                            $html .="<td style='font-size: 13px; border: 1px solid #000; font-weight: 800'>$row->codigo_catastral</td>";
                            $html .="<td style='font-size: 13px; border: 1px solid #000; font-weight: 800'>$row->vig</td>";
                            $html .="<td style='font-size: 13px; border: 1px solid #000; font-weight: 800'>$propietario</td>";
                            $html .="<td style='text-align: right; font-size: 13px; border: 1px solid #000;font-weight: 800'>$row->numero</td>";
                            $html .="<td style='text-align: right; font-size: 13px; border: 1px solid #000;font-weight: 800'>$row->anno</td>";
                            $html .="<td style='text-align: right; font-size: 13px; border: 1px solid #000'>".number_format($row->impuesto,0,'.',',')."</td>";
                            $html .="<td style='text-align: right; font-size: 13px; border: 1px solid #000'>".number_format($row->interes_mora,0,'.',',')."</td>";
                            $html .="<td style='text-align: right; font-size: 13px; border: 1px solid #000'>".number_format($row->corporacion_regional,0,'.',',')."</td>";
                            $html .="<td style='text-align: right; font-size: 13px; border: 1px solid #000'>".number_format($row->interes_corporacion,0,'.',',')."</td>";

                            if($row->tipopago == 7){
                                $html .="<td style='text-align: right; font-size: 13px; border: 1px solid #000'>".number_format($descuento,0,'.',',')."</td>";
                            }else{

                                if ($row->descuento<0) {
                                    $desc=$row->descuento*-1;
                                }else{
                                    $desc=$row->descuento;
                                }
                            $html .="<td style='text-align: right; font-size: 13px; border: 1px solid #000'>".number_format( $desc,0,'.',',')."</td>";
                            }
                            $html .="<td style='text-align: right; font-size: 13px; border: 1px solid #000'>".number_format($row->papeleria,0,'.',',')."</td>";
                            $html .="<td style='text-align: right; font-size: 13px; border: 1px solid #000'>".number_format($row->Sobretasa_Bomberil,0,'.',',')."</td>";
                            $html .="<td style='text-align: right; font-size: 13px; border: 1px solid #000'>".number_format($row->Debido_Cobrar,0,'.',',')."</td>";
               
                                $x = $row->impuesto + $row->corporacion_regional + $row->interes_mora + $row->descuento  + $row->interes_corporacion + $row->papeleria + $row->Sobretasa_Bomberil + $row->Debido_Cobrar;
                   
                            $html .="<td style='text-align: right; font-size: 13px; border: 1px solid #000'>".number_format($x,0,'.',',')."</td>";
                            $html .="</tr>";
                        $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;

                       $param    = session()->get('anno');
                        if ($param==6) {
                            $año='2021';
                        }else if($param==10){
                            $año='2022';
                        }else if($param==13){
                          $año='2023';
                        }
                        if($row->vig == $año){                         
                            $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;
                        }
                    }
                        $html .="<tr>";
                        $html .="<td colspan='5' style='font-size: 11px; border: 1px solid #000'><b>TOTAL BANCO</b></td>";
                        $html .="<td style='text-align: right; font-size: 13px; border: 1px solid #000'><b>".number_format($ximp, 0, '.', ',')."</b></td>";
                        $html .="<td style='text-align: right; font-size: 13px; border: 1px solid #000'><b>". number_format($xint, 0, '.', ',')."</b></td>";
                        $html .="<td style='text-align: right; font-size: 13px; border: 1px solid #000'><b>". number_format($xcor, 0, '.', ',')."</b></td>";
                        $html .="<td style='text-align: right; font-size: 13px; border: 1px solid #000'><b>". number_format($xinc, 0, '.', ',')."</b></td>";
                        $html .="<td style='text-align: right; font-size: 13px; border: 1px solid #000'><b>". number_format($xdes, 0, '.', ',')."</b></td>";
                        $html .="<td style='text-align: right; font-size: 13px; border: 1px solid #000'><b>". number_format($xpap, 0, '.', ',')."</b></td>";
                        $html .="<td style='text-align: right; font-size: 13px; border: 1px solid #000'><b>". number_format($xsbm, 0, '.', ',')."</b></td>";
                        $html .="<td style='text-align: right; font-size: 13px; border: 1px solid #000'><b>". number_format($xdeb, 0, '.', ',')."</b></td>";
                        $html .="<td style='text-align: right; font-size: 13px; border: 1px solid #000'><b>". number_format($xxxx, 0, '.', ',')."</b></td>";
                        $html .="</tr>";
                }
            }
            echo $html;
        } catch (Exception $e) {
            return $e->getMessage();
        }
        

        
    }

    public static function obtenerCompania(){
        $data = 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();
        return $data;
    }
}
