<?php

namespace Predial\Http\Controllers;

use Illuminate\Http\Request;
use Carbon\Carbon;
use Predial\ConceptoPredial;
use Predial\FacturaPredial;
use Predial\Predio;
use Predial\Tarifa;
use Predial\CarteraObligada;
use Predial\Http\Controllers\LiquidadorInteresesController;
use Illuminate\Support\Facades\DB;
class CarteraController extends Controller{

    public function index(){
        if(!empty(session()->get('usuario_tercero')) && !empty(session()->get('compania'))){
            return view('cartera.index');
        }else{
            return redirect("CerrarSesion");
        }
    }

    public function obtenerUltimoAnnoPago($id){
        try {
            return DB::table('gr_ultimo_ano_pago')->where('predio', $id)->max('anno');
        } catch (Exception $e) {
            return $e->getMessage();
        }
    }

    public static function obtenerAvaluosDesde($predio, $anno){
        try {
            $data = DB::table('gr_avaluo as ava')->where('ava.predio', '=', $predio)->where('ava.anno', '>', $anno)->get();
            return $data;
        } catch (Exception $e) {
            return $e->getMessage();
        }
    }

    public static function obtenerTAvaluos($predio){
        try {
            $data = DB::table('gr_avaluo as ava')->where('ava.predio', '=', $predio)->get();
            return $data;
        } catch (Exception $e) {
            return $e->getMessage();
        }
    }

    public static function obtenerFechaVencimientoInteres($anno){
        try {
            $data = DB::table('gr_vencimiento')->where('anno', '=', $anno)->where('tipo', '=', 1)->max('fecha');
            return $data;
        } catch (Exception $e) {
            return $e->getMessage();
        }
    }

    public static function obtenerFechaVencimientoDescuento($anno, $mes){
        try {
            $data = DB::table('gr_vencimiento as grv')
                ->leftJoin('gf_mes as gms', 'grv.mes', '=', 'gms.id_unico')
                ->where('grv.anno', '=', $anno)
                ->where('grv.tipo', '=', 2)
                ->where('gms.numero', '=', $mes)
                ->first();
            return $data;
        } catch (Exception $e) {
            return $e->getMessage();
        }
    }

    public static function obtenerDebidoCobrar($predio){
        try {
            $xxx = 0;
            $data = DB::table('gr_debido_valor')->select('valor')->where('predio', '=', $predio)->first();
            if(!empty($data->valor)){
                $xxx = $data->valor;
            }else{
                $xxx = 0;
            }
            return $xxx;
        } catch (Exception $e) {
            return $e->getMessage();
        }
    }

   public static function obtenerExcensionesPredio($predio, $anno){
        return DB::table('gr_excensiones')->where('predio', '=', $predio)->where('annoinicial', '<=', $anno)->where('annofinal', '>=', $anno)->first();
   }

   public function generate(Request $request){
        if(!empty(session()->get('usuario_tercero')) && !empty(session()->get('compania'))){
            ini_set('max_execution_time', 0);
            $ff       = explode("/", $request->txtFecha);
            $fecha    = "$ff[2]-$ff[1]-$ff[0]";
            $x        = 0;
            $predios  = DB::select("SELECT id_unico FROM gp_predio1 WHERE estado = 2 LIMIT 250 OFFSET $request->txtOffset");
            foreach ($predios as $row){
                $ultimoA = CarteraController::obtenerUltimoAnnoPago($row->id_unico);

                if(!empty($ultimoA)){
                    if($ultimoA < date("Y")){
                        $avaluos = CarteraController::obtenerAvaluosDesde($row->id_unico, $ultimoA);
                        CarteraController::GuardarData($row->id_unico, $avaluos, $fecha);
                        $x++;
                    }
                }

                if(empty($ultimoA)){
                    $avaluos = CarteraController::obtenerTAvaluos($row->id_unico);
                    CarteraController::GuardarData($row->id_unico, $avaluos, $fecha);
                    $x++;
                }
            }

            flash()->overlay("Información guardada correctamente.", "Información");
            echo json_encode(["fecha" => $request->txtFecha, "offset" => $request->txtOffset, "_token" => $request->_token, "x" => count($predios), "y" => $x]);
        }else{
            return redirect("CerrarSesion");
        }
   }
   
   public static function GuardarData($predio, $avaluos, $fecha){
        try {
            ini_set('max_execution_time', 0);
            $Liq     = new LiquidadorInteresesController();
            $str     = "insert into gr_cartera_obligada (predio, avaluo, concepto, valor, fecha) values";
            $strx    = "";
            $valor = 0;
            foreach ($avaluos as $row){
                $xxx = CarteraController::obtenerExcensionesPredio($predio, $row->anno);
                if(empty($xxx)){
                    $fechaVM = CarteraController::obtenerFechaVencimientoInteres($row->anno);
                    $fechaVD = CarteraController::obtenerFechaVencimientoDescuento($row->anno, date("m"));
                    $tarifa  = Tarifa::where('id_unico', '=', $row->tarifa)->first();
                    $baseC   = $row->valor;
                    list($impuesto, $corporacion, $interes, $intCas, $descuento, $debido, $valor, $pap, $sob) = array(0, 0, 0, 0, 0, 0, 0, 0, 0);

                    $dataCto = ConceptoPredial::where('anno', '=', $row->anno)->get();


                    foreach ($dataCto as $item){
                        switch ($item->id_concepto){
                            case 1:
                                if(!empty($tarifa->valor)){
                                    $impuesto = ($baseC * $tarifa->valor) / 1000;
                                    $valor    = $impuesto;
                                }
                                break;
                            case 2:
                                $valor    = 0;
                                break;
                            case 3:
                                $base  = 0; $xpr = 0;
                                if(!empty($tarifa->tipobaseambiental)){
                                    switch ($tarifa->tipobaseambiental){
                                        case 6: 
                                            $base = $row->valor;
                                            $xpr  = 1000;
                                            break;
                                        case 7:
                                            $base = $impuesto;
                                            $xpr  = 100;
                                            break;
                                    }
                                    if(!empty($tarifa->porcentajeimpuestoambiental)){
                                        $corporacion = ($base * $tarifa->porcentajeimpuestoambiental) / $xpr;
                                        $valor       = $corporacion;
                                    }else{
                                        $valor       = 0;
                                    }
                                }
                                break;
                            case 4:

                                if(empty($fechaVM)){
                                    $xvn     = Carbon::createFromDate($row->anno, 01, 01);
                                    $xvn     = $xvn->lastOfMonth();
                                    $fechaVM = $xvn->format('Y-m-d');
                                }
                                $xvv     = Carbon::createFromDate(date('Y'), date('m'), 01);
                                $xvv     = $xvv->lastOfMonth();
                                $fechaVv = $xvv->format('Y-m-d');

                                #var_dump($impuesto.' - '.$fechaVM.' - '.$fecha.' - '.$fecha);
                                $interes = $Liq->liquidar_intereses($impuesto, $fechaVM, $fechaVv, $fecha);
                                $valor   = $interes;
                                break;
                            case 5:
                                if(!empty($fechaVD->fecha)){
                                    $ff    = explode("-", $fechaVD->fecha);
                                    $xintC = LiquidadorInteresesController::obtenerPorcentaje($ff[0], $ff[1], 2);
                                    if(!empty($xintC)){
                                        $descuento = ($impuesto * $xintC) / 100;
                                    }else{
                                        $descuento = 0;
                                    }
                                }else{
                                    $descuento = 0;
                                }
                                $descuento = $descuento * -1;
                                $valor     = $descuento;
                                break;
                            case 6:
                                $pap   = $item->formula;
                                $valor = $pap;
                                break;
                            case 7:
                                if(!empty($tarifa->porcentajesobretasa)){
                                    $sob = ($impuesto * $tarifa->porcentajesobretasa) / 100;
                                }else{
                                    $sob = 0;
                                }
                                $valor = $sob;
                                break;
                            case 8:
                                if(empty($fechaVM)){
                                    $xvn     = Carbon::createFromDate($row->anno, 01, 01);
                                    $xvn     = $xvn->lastOfMonth();
                                    $fechaVM = $xvn->format('Y-m-d');
                                }
                                $xvv     = Carbon::createFromDate(date('Y'), date('m'), 01);
                                $xvv     = $xvv->lastOfMonth();
                                $fechaVv = $xvv->format('Y-m-d');

                                $intCas = $Liq->liquidar_intereses($corporacion, $fechaVM, $fechaVv, $fecha);
                                $valor  = $intCas;
                                break;
                            case 9:
                                $debido = CarteraController::obtenerDebidoCobrar($predio);
                                $valor  = $debido;
                                break;
                        }
                        $valor   = ExtractoController::convertirPeso($valor);
                        if($valor !== 0){
                            $strx   .= "($predio, $row->id_unico, $item->id_concepto, $valor,'".$fecha."'), ";
                        }
                    }
                }
            }
            if(!empty($strx)){
                $strx  = substr($strx, 0, strlen($strx) - 2);
                $strx .= ";";
                $str  .= $strx;
                DB::insert($str);
            }
        }catch(Exception $e){
            return $e->getMessage();
        }
   }

   public function vistaInformeS(){
       if(!empty(session()->get('usuario_tercero')) && !empty(session()->get('compania'))){
           $carteras = DB::table('gr_cartera_obligada')
                            ->select(DB::raw("DATE_FORMAT(fecha, '%d/%m/%Y') AS fecha"), 'fecha AS normf')
                            ->groupBy('fecha')
                            ->get();
           return view("cartera.vista", compact('carteras',$carteras));
       }else{
           return redirect("CerrarSesion");
       }
   }

   public function validarConsultaInforme($periodo){
       $data = DB::table('gr_cartera_obligada as car')
           ->select('car.predio', DB::raw('SUM(car.valor) AS valor'))
           ->where('car.fecha', '=',$periodo)
           ->groupBy('car.predio')
           ->get();
       return $data;
   }

    public function obtenerCodigoEnte(){
        try {
            $xxx = "";
            $data = DB::table('gf_tercero')->where('id_unico', '=', session()->get('compania'))->first();
            if(count($data) > 0){
                $xxx = $data->codigo_dane;
            }
            return $xxx;
        } catch (Exception $e) {
            return $e->getMessage();
        }
    }

    public function obtenerMes($fecha){
        $xxx = explode("-", $fecha);
        return $xxx[1];
    }

    public function validarTipoP($tipo){
        $xxx = "";
        switch ($tipo){
            case 1:
            case 3:
            case 4:
            case 5:
                $xxx = 1;
                break;
            case 2:
            case 6:
                $xxx = 2;
                break;
        }
        return $xxx;
    }

    public function validarTipoDoc($tipo){
        $xxx = 0;
        switch ($tipo){
            case 1:
                $xxx = 1;
                break;
            case 2:
                $xxx = 2;
                break;
            case 3:
                $xxx = 9;
                break;
            case 4:
                $xxx = 4;
                break;
            case 5:
                $xxx = 3;
                break;
            case 6:
                $xxx = 1;
                break;
        }
        return $xxx;
    }

    public function validarEstructuraFecha($tipo, $fecha){
        $xxx = "";
        switch ($tipo){
            case 1:
                $ff  = explode("-", $fecha);
                $xxx = "$ff[0]-$ff[1]-01;$fecha";
                break;
            case 2:
                $xxx = $fecha;
                break;
            case 3:
                $xxx = $fecha;
                break;
        }
        return $xxx;
    }

    public function procesarInforme(Request $request){
        if(!empty(session()->get('usuario_tercero')) && !empty(session()->get('compania'))){
            $need = stripos($request->txtValor, ",");
            if(!empty($need)){
                $valor = str_replace(",", "", $request->txtValor);
            }else{
                $valor = $request->txtValor;
            }
            $query = CarteraController::validarConsultaInforme($request->sltPeriodo);
            $ente  = CarteraController::obtenerCodigoEnte();
            $mesI = CarteraController::obtenerMes($request->sltPeriodo);
            $mes  = "1$mesI$mesI";

            $separador = "";
            if(!empty($request->txtSeparador)){
                if($request->txtSeparador === "TAB" OR $request->txtSeparador === "tab"){
                    $separador = "\t";
                }else{
                    $separador = $request->txtSeparador;
                }
            }

            $ruta = "docs/archivosplanos/";
            switch ($request->sltTipoA){
                case 1:
                    $name = 'CGN2009_BDME_REPORTE_SEMESTRAL-'.date('Y-m-d').'.xls';
                    $html = "";
                    $html .= "\n\t<table width='100%'>";
                    $html .= "\n\t<tr>";
                    $html .= "\n\t\t<td style='border: solid 1px #000;'>S</td>";
                    $html .= "\n\t\t<td style='border: solid 1px #000;'>$ente</td>";
                    $html .= "\n\t\t<td style='border: solid 1px #000;'>$mes</td>";
                    $html .= "\n\t\t<td style='border: solid 1px #000;'>".date("Y")."</td>";
                    $html .= "\n\t\t<td style='border: solid 1px #000;'>CGN2009_BDME_REPORTE_SEMESTRAL</td>";
                    $html .= "\n\t\t<td colspan='4' style='border: solid 1px #000;'></td>";
                    $html .= "\n\t</tr>";
                    foreach ($query as $row){
                        if($row->valor > $valor){
                            $data  = DB::table('gr_cartera_obligada as car')
                                 ->select('gct.nombre as con', 'gpr.tipoidentificacion as tpi', 'gpr.nombres as nom', 'gpr.numero as doc',
                                     'gpd.codigo_catastral as cod',
                                     DB::raw('SUM(car.valor) AS valor')
                                 )
                                 ->leftJoin('gr_concepto as gct', 'car.concepto', '=', 'gct.id_unico')
                                 ->leftJoin('gp_tercero_predio as gtr', 'gtr.predio', '=', 'car.predio')
                                 ->leftJoin('gr_propietarios as gpr', 'gtr.tercero', '=', 'gpr.id_unico')
                                 ->leftJoin('gp_predio1 as gpd', 'car.predio', '=', 'gpd.id_unico')
                                 ->where('gtr.propietario', '=', '0')
                                 ->where('car.predio', '=', $row->predio)
                                 ->where('car.fecha', '=' ,$request->sltPeriodo)
                                 ->groupBy('car.predio')
                                 ->get();
                            foreach ($data as $item){
                                $tipo  = CarteraController::validarTipoP($item->tpi);
                                $tipoD = CarteraController::validarTipoDoc($item->tpi);
                                $xv    = substr($item->valor, 0, strlen($item->valor) - 3);
                                $html .= "\n\t<tr>";
                                $html .= "\n\t\t<td style='border: solid 1px #000;'>D</td>";
                                $html .= "\n\t\t<td style='border: solid 1px #000;'>1</td>";
                                $html .= "\n\t\t<td style='border: solid 1px #000;'>$tipo</td>";
                                $html .= "\n\t\t<td style='border: solid 1px #000;'>$item->cod</td>";
                                $html .= "\n\t\t<td style='border: solid 1px #000;'>'$item->doc'</td>";
                                $html .= "\n\t\t<td style='border: solid 1px #000;'>$tipoD</td>";
                                $html .= "\n\t\t<td style='border: solid 1px #000;'>$item->nom</td>";
                                $html .= "\n\t\t<td style='border: solid 1px #000;'>$xv</td>";
                                $html .= "\n\t\t<td style='border: solid 1px #000;'>1</td>";
                                $html .= "\n\t</tr>";
                            }
                        }
                    }
                    $html .= "\n\t</table>";

                    if(!$handle = fopen($ruta.$name, "w")){
                        echo json_encode(["mensaje" => "No se puede abrir el archivo"]);
                    }

                    if(fwrite($handle, $html) === FALSE){
                        echo json_encode(["mensaje" => "No se puede escribir archivo"]);
                        exit;
                    }

                    if(file_exists($ruta.$name)){
                        echo json_encode(["mensaje" => "OK", "ruta" => $ruta.$name]);
                    }
                    break;
                case 2:
                    $name = 'CGN2009_BDME_REPORTE_SEMESTRAL'.date('Y-m-d').'.csv';
                    $xxx = "S$separador$ente$separador$mes$separador".date('Y')."$separador"."CGN2009_BDME_REPORTE_SEMESTRAL\n";
                    foreach ($query as $row){
                        if($row->valor > $valor){
                            $data  = DB::table('gr_cartera_obligada as car')
                                ->select('gct.nombre as con', 'gpr.tipoidentificacion as tpi', 'gpr.nombres as nom', 'gpr.numero as doc',
                                    'gpd.codigo_catastral as cod',
                                    DB::raw('SUM(car.valor) AS valor')
                                )
                                ->leftJoin('gr_concepto as gct', 'car.concepto', '=', 'gct.id_unico')
                                ->leftJoin('gp_tercero_predio as gtr', 'gtr.predio', '=', 'car.predio')
                                ->leftJoin('gr_propietarios as gpr', 'gtr.tercero', '=', 'gpr.id_unico')
                                ->leftJoin('gp_predio1 as gpd', 'car.predio', '=', 'gpd.id_unico')
                                ->where('gtr.propietario', '=', '0')
                                ->where('car.predio', '=', $row->predio)
                                ->where('car.fecha', $request->sltPeriodo)
                                ->groupBy('car.predio')
                                ->get();
                            foreach ($data as $item){
                                $tipo  = CarteraController::validarTipoP($item->tpi);
                                $tipoD = CarteraController::validarTipoDoc($item->tpi);
                                $xv    = substr($item->valor, 0, strlen($item->valor) - 3);
                                $xxx .= "D$separador"."1$separador$tipo$separador$item->cod$separador$item->doc$separador$tipoD$separador$item->nom$separador$xv$separador"."1\n";
                            }
                        }
                    }

                    if(!$handle = fopen($ruta.$name, "w")){
                        echo json_encode(["mensaje" => "No se puede abrir el archivo"]);
                    }

                    if(fwrite($handle, $xxx) === FALSE){
                        echo json_encode(["mensaje" => "No se puede escribir archivo"]);
                        exit;
                    }

                    if(file_exists($ruta.$name)){
                        echo json_encode(["mensaje" => "OK", "ruta" => $ruta.$name]);
                    }
                    break;
                case 3:
                    $name = 'CGN2009_BDME_REPORTE_SEMESTRAL-'.date('Y-m-d').'.txt';
                    $xxx = "S$separador$ente$separador$mes$separador".date("Y")."$separador"."CGN2009_BDME_REPORTE_SEMESTRAL\n";
                    foreach ($query as $row){
                        if($row->valor > $valor){
                            $data  = DB::table('gr_cartera_obligada as car')
                                ->select('gct.nombre as con', 'gpr.tipoidentificacion as tpi', 'gpr.nombres as nom', 'gpr.numero as doc',
                                    'gpd.codigo_catastral as cod',
                                    DB::raw('SUM(car.valor) AS valor')
                                )
                                ->leftJoin('gr_concepto as gct', 'car.concepto', '=', 'gct.id_unico')
                                ->leftJoin('gp_tercero_predio as gtr', 'gtr.predio', '=', 'car.predio')
                                ->leftJoin('gr_propietarios as gpr', 'gtr.tercero', '=', 'gpr.id_unico')
                                ->leftJoin('gp_predio1 as gpd', 'car.predio', '=', 'gpd.id_unico')
                                ->where('gtr.propietario', '=', '0')
                                ->where('car.predio', '=', $row->predio)
                                ->where('car.fecha', $request->sltPeriodo)
                                ->groupBy('car.predio')
                                ->get();
                            foreach ($data as $item){
                                $tipo  = CarteraController::validarTipoP($item->tpi);
                                $tipoD = CarteraController::validarTipoDoc($item->tpi);
                                $xv    = substr($item->valor, 0, strlen($item->valor) - 3);
                                $xxx .= "D$separador"."1$separador$tipo$separador$item->cod$separador$item->doc$separador$tipoD$separador$item->nom$separador$xv$separador"."1\n";
                            }
                        }
                    }
                    if(!$handle = fopen($ruta.$name, "w")){
                        echo json_encode(["mensaje" => "No se puede abrir el archivo"]);
                    }

                    if(fwrite($handle, $xxx) === FALSE){
                        echo json_encode(["mensaje" => "No se puede escribir archivo"]);
                        exit;
                    }

                    if(file_exists($ruta.$name)){
                        echo json_encode(["mensaje" => "OK", "ruta" => $ruta.$name]);
                    }
                    break;
            }
        }else{
            return redirect("CerrarSesion");
        }
    }

    public function vistaInformeCartera(){
        if(!empty(session()->get('usuario_tercero')) && !empty(session()->get('compania'))){
            $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("cartera.informe", compact('carteras',$carteras));
        }else{
            return redirect("CerrarSesion");
        }
    }

    public function generarInfCarteraObl(Request $request){
        header('Content-type: application/vnd.ms-excel; charset=utf-8');
        header('Content-Disposition: attachment;filename="InformeCarteraObligada.xls"');
        $compania = ListadoPagosController::obtenerCompania();
        $an = explode('-', $request->sltCartera);
        $anno_cartera = $an[0];
        if($request->check_resumen == true){
            $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>Informe Cartera Obligada</title>";
            $html .= "</head>";
            $html .= "<body>";
            $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')
                        ->where('gco.fecha', $request->sltCartera)
                        ->select(
                            'gpr.codigo_catastral as cod', 'gav.anno',
                            DB::raw('sum(if(gco.concepto = 1, gco.valor, 0)) as imp'),
                            DB::raw('sum(if(gco.concepto = 4, gco.valor, 0)) as "int"'),
                            DB::raw('sum(if(gco.concepto = 3, gco.valor, 0)) as cas'),
                            DB::raw('sum(if(gco.concepto = 8, gco.valor, 0)) as inc'),
                            DB::raw('sum(if(gco.concepto = 7, gco.valor, 0)) as sob'),
                            DB::raw('sum(if(gco.concepto = 6, gco.valor, 0)) as pap'),
                            DB::raw('sum(if(gco.concepto = 9, gco.valor, 0)) as deb')
                        )
                        ->groupBy('gco.avaluo')
                        ->groupBy('gco.predio')
                        ->orderBy('gpr.id_unico', 'ASC')
                        ->orderBy('gav.anno', 'ASC')
                        ->distinct()
                        ->get();

            $html .= "<table style='width: 100%;'>";
            $html .= "<thead>";
            $html .= "<tr>";
            $html .= "<th style='border: solid 1px #000;'></th>";
            $html .= "<th style='border: solid 1px #000;'>IMPUESTO PREDIAL</th>";
            $html .= "<th style='border: solid 1px #000;'>INTER&Eacute;S PREDIAL</th>";
            $html .= "<th style='border: solid 1px #000;'>SUMA DE VlrCAR</th>";
            $html .= "<th style='border: solid 1px #000;'>SUMA DE VlrINTERESCAR</th>";
            $html .= "<th style='border: solid 1px #000;'>OTROS</th>";
            $html .= "<th style='border: solid 1px #000;'>TOTAL</th>";
            $html .= "</tr>";
            $html .= "</thead>";
            $html .= "<tbody>";
            list($imp_predial, $int_predial, $sum_cas, $sum_int_cas, $otros, $total) = array(0, 0, 0, 0, 0, 0);
            list($imp_predial_c1, $imp_predial_c2, $imp_predial_c3, $imp_predial_c4) = array(0, 0, 0, 0);
            list($int_predial_c1, $int_predial_c2, $int_predial_c3, $int_predial_c4) = array(0, 0, 0, 0);
            list($sum_car_c1, $sum_car_c2, $sum_car_c3, $sum_car_c4) = array(0, 0, 0, 0);
            list($sum_int_car_c1, $sum_int_car_c2, $sum_int_car_c3, $sum_int_car_c4) = array(0, 0, 0, 0);
            list($otros_c1, $otros_c2, $otros_c3, $otros_c4) = array(0, 0, 0, 0);
            list($imp_predial_c1, $imp_predial_c2, $imp_predial_c3, $imp_predial_c4) = array(0, 0, 0, 0);
            $x = 5;
            foreach ($data as $rw){
                if($rw->anno == $anno_cartera){
                    $imp_predial_c4 += $rw->imp;
                    $int_predial_c4 += $rw->int;
                    $sum_car_c4 += $rw->cas;
                    $sum_int_car_c4 += $rw->inc;
                    $otros_c4 += ($rw->sob + $rw->pap + $rw->deb);
                }

                else if($rw->anno < ($anno_cartera - 5)){
                    $imp_predial_c1 += $rw->imp;
                    $int_predial_c1 += $rw->int;
                    $sum_car_c1 += $rw->cas;
                    $sum_int_car_c1 += $rw->inc;
                    $otros_c1 += ($rw->sob + $rw->pap + $rw->deb);
                }else if((($anno_cartera - 4) == $rw->anno) || ($rw->anno == ($anno_cartera - 5))){
                    $imp_predial_c2 += $rw->imp;
                    $int_predial_c2 += $rw->int;
                    $sum_car_c2 += $rw->cas;
                    $sum_int_car_c2 += $rw->inc;
                    $otros_c2 += ($rw->sob + $rw->pap + $rw->deb);
                }else if((($anno_cartera - 1) == $rw->anno) || ($rw->anno == ($anno_cartera - 3)) || ($rw->anno == ($anno_cartera - 2))){
                    $imp_predial_c3 += $rw->imp;
                    $int_predial_c3 += $rw->int;
                    $sum_car_c3 += $rw->cas;
                    $sum_int_car_c3 += $rw->inc;
                    $otros_c3 += ($rw->sob + $rw->pap + $rw->deb);
                }
            }
            $imp_predial = $imp_predial_c1 + $imp_predial_c2 + $imp_predial_c3 + $imp_predial_c4;
            $int_predial = $int_predial_c1 + $int_predial_c2 + $int_predial_c3 + $int_predial_c4;
            $sum_cas = $sum_car_c1 + $sum_car_c2 + $sum_car_c3 + $sum_car_c4;
            $sum_int_cas = $sum_int_car_c1 + $sum_int_car_c2 + $sum_int_car_c3 + $sum_int_car_c4;
            $otros = $otros_c1 + $otros_c2 + $otros_c3 + $otros_c4;

            $tipos_cartera = [
                'CARTERA DE MAS DE 5 A&Ntilde;OS' => [
                    'IMPUESTO_PREDIAL' => $imp_predial_c1,
                    'INTERES_PREDIAL' => $int_predial_c1,
                    'SUMA_CAR' => $sum_car_c1,
                    'SUMA_INT_CAR' => $sum_int_car_c1,
                    'OTROS' => $otros_c1,
                    'TOTAL' => $imp_predial_c1 + $int_predial_c1 + $sum_car_c1 + $sum_int_car_c1 + $otros_c1
                ],
                'CARTERA DE 4 A 5 A&Ntilde;OS' => [
                    'IMPUESTO_PREDIAL' => $imp_predial_c2,
                    'INTERES_PREDIAL' => $int_predial_c2,
                    'SUMA_CAR' => $sum_car_c2,
                    'SUMA_INT_CAR' => $sum_int_car_c2,
                    'OTROS' => $otros_c2,
                    'TOTAL' => $imp_predial_c2 + $int_predial_c2 + $sum_car_c2 + $sum_int_car_c2 + $otros_c2
                ],
                'CARTERA DE 1 A 3 A&Ntilde;OS' => [
                    'IMPUESTO_PREDIAL' => $imp_predial_c3,
                    'INTERES_PREDIAL' => $int_predial_c3,
                    'SUMA_CAR' => $sum_car_c3,
                    'SUMA_INT_CAR' => $sum_int_car_c3,
                    'OTROS' => $otros_c3,
                    'TOTAL' => $imp_predial_c3 + $int_predial_c3 + $sum_car_c3 + $sum_int_car_c3 + $otros_c3
                ],
                'VIGENCIA ACTUAL' => [
                    'IMPUESTO_PREDIAL' => $imp_predial_c4,
                    'INTERES_PREDIAL' => $int_predial_c4,
                    'SUMA_CAR' => $sum_car_c4,
                    'SUMA_INT_CAR' => $sum_int_car_c4,
                    'OTROS' => $otros_c4,
                    'TOTAL' => $imp_predial_c4 + $int_predial_c4 + $sum_car_c4 + $sum_int_car_c4 + $otros_c4
                ]
            ];

            foreach($tipos_cartera as $key => $value){
                $html .= "<tr>";
                $html .= "<td width='15%' style='border: solid 1px #000; text-align: left; font-weight: bold;'>".$key."</td>";
                $html .= "<td style='border: solid 1px #000; text-align: right;'>".number_format($value['IMPUESTO_PREDIAL'], 2, ',', '.')."</td>";
                $html .= "<td style='border: solid 1px #000; text-align: right;'>".number_format($value['INTERES_PREDIAL'], 2, ',', '.')."</td>";
                $html .= "<td style='border: solid 1px #000; text-align: right;'>".number_format($value['SUMA_CAR'], 2, ',', '.')."</td>";
                $html .= "<td style='border: solid 1px #000; text-align: right;'>".number_format($value['SUMA_INT_CAR'], 2, ',', '.')."</td>";
                $html .= "<td style='border: solid 1px #000; text-align: right;'>".number_format($value['OTROS'], 2, ',', '.')."</td>";
                $html .= "<td style='border: solid 1px #000; text-align: right;'>".number_format($value['TOTAL'], 2, ',', '.')."</td>";
                $html .= "</tr>";
                $total += $value['TOTAL'];
            }


            $html .= "</tbody>";
            $html .= "<tfoot>";
            $html .= "<tr>";
            $html .= "<th style='border: solid 1px #000; text-align: center;'>TOTAL CARTERA PREDIAL</th>";
            $html .= "<th style='border: solid 1px #000; text-align: right;'>".number_format($imp_predial, 2, ',', '.')."</th>";
            $html .= "<th style='border: solid 1px #000; text-align: right;'>".number_format($int_predial, 2, ',', '.')."</th>";
            $html .= "<th style='border: solid 1px #000; text-align: right;'>".number_format($sum_cas, 2, ',', '.')."</th>";
            $html .= "<th style='border: solid 1px #000; text-align: right;'>".number_format($sum_int_cas, 2, ',', '.')."</th>";
            $html .= "<th style='border: solid 1px #000; text-align: right;'>".number_format($otros, 2, ',', '.')."</th>";
            $html .= "<th style='border: solid 1px #000; text-align: right;'>".number_format($total, 2, ',', '.')."</th>";
            $html .= "</tr>";
            $html .= "</tfoot>";
            $html .= "</table>";
            $html .= "</body>";
            $html .= "</html>";
            echo $html;
        }else{
            $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>Informe Cartera Obligada</title>";
            $html .= "</head>";
            $html .= "<body>";
            $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')
                        ->where('gco.fecha', $request->sltCartera)
                        ->select(
                            'gpr.codigo_catastral as cod', 'gav.anno',
                            DB::raw('sum(if(gco.concepto = 1, gco.valor, 0)) as imp'),
                            DB::raw('sum(if(gco.concepto = 4, gco.valor, 0)) as "int"'),
                            DB::raw('sum(if(gco.concepto = 3, gco.valor, 0)) as cas'),
                            DB::raw('sum(if(gco.concepto = 8, gco.valor, 0)) as inc'),
                            DB::raw('sum(if(gco.concepto = 7, gco.valor, 0)) as sob'),
                            DB::raw('sum(if(gco.concepto = 6, gco.valor, 0)) as pap'),
                            DB::raw('sum(if(gco.concepto = 9, gco.valor, 0)) as deb')
                        )
                        ->groupBy('gco.avaluo')
                        ->groupBy('gco.predio')
                        ->orderBy('gpr.id_unico', 'ASC')
                        //->orderBy('gav.anno', 'ASC')
                        ->distinct()
                        ->get();
            $html .= "<table style='width: 100%;'>";
            $html .= "<thead>";
            $html .= "<tr>";
            $html .= "<th colspan='10' style='border: solid 1px #000;'>$compania->razonsocial<br/>$compania->tipodoc : $compania->numeroi<br/>DIRECCI&Oacute;N : $compania->direccion<br/>TELEFONO : $compania->telefono <br/> INFORME DE CARTERA OBLIGADA</th>";
            $html .= "</tr>";
            $html .= "<tr>";
            $html .= "<th style='border: solid 1px #000;'>CODIGO CATASTRAL</th>";
            $html .= "<th style='border: solid 1px #000;'>AVALUO</th>";
            $html .= "<th style='border: solid 1px #000;'>IMPUESTO <br/> PREDIAL</th>";
            $html .= "<th style='border: solid 1px #000;'>INTERES</th>";
            $html .= "<th style='border: solid 1px #000;'>CAS</th>";
            $html .= "<th style='border: solid 1px #000;'>INTERES <br/> CAS</th>";
            $html .= "<th style='border: solid 1px #000;'>SOBRETASA <br/> BOMBERIL</th>";
            $html .= "<th style='border: solid 1px #000;'>PAPELERIA</th>";
            $html .= "<th style='border: solid 1px #000;'>DEBIDO COBRAR</th>";
            $html .= "<th style='border: solid 1px #000;'>TOTAL</th>";
            $html .= "</tr>";
            $html .= "</thead>";
            $html .= "<tbody>";
            list($imp, $int, $cas, $inc, $sob, $pap, $deb, $xto) = array(0, 0, 0, 0, 0, 0, 0, 0);
            foreach ($data as $row){
                $xxx = $row->imp + $row->int + $row->cas + $row->sob + $row->pap + $row->deb+$row->inc;
                $imp += $row->imp; $int += $row->int; $int += $row->cas; $sob += $row->sob;
                $pap += $row->pap; $deb += $row->deb; $xto += $xxx; 
                $inc += $row->inc; 
                $cas += $row->cas;
                $html .= "<tr>";
                $html .= "<td style='border: solid 1px #000; text-align: right; font-weight: bold;'>'$row->cod'</td>";
                $html .= "<td style='border: solid 1px #000; text-align: right; font-weight: bold;'>$row->anno</td>";
                $html .= "<td style='border: solid 1px #000; text-align: right;'>".number_format($row->imp, 2, ',', '.')."</td>";
                $html .= "<td style='border: solid 1px #000; text-align: right;'>".number_format($row->int, 2, ',', '.')."</td>";
                $html .= "<td style='border: solid 1px #000; text-align: right;'>".number_format($row->cas, 2, ',', '.')."</td>";
                $html .= "<td style='border: solid 1px #000; text-align: right;'>".number_format($row->inc, 2, ',', '.')."</td>";
                $html .= "<td style='border: solid 1px #000; text-align: right;'>".number_format($row->sob, 2, ',', '.')."</td>";
                $html .= "<td style='border: solid 1px #000; text-align: right;'>".number_format($row->pap, 2, ',', '.')."</td>";
                $html .= "<td style='border: solid 1px #000; text-align: right;'>".number_format($row->deb, 2, ',', '.')."</td>";
                $html .= "<td style='border: solid 1px #000; text-align: right;'>".number_format($xxx, 2, ',', '.')."</td>";
                $html .= "</tr>";
            }
            $html .= "</tbody>";
            $html .= "<tfoot>";
            $html .= "<tr>";
            $html .= "<th style='border: solid 1px #000; text-align: center;' colspan='2'>TOTALES</th>";
            $html .= "<th style='border: solid 1px #000; text-align: right;'>".number_format($imp, 2, ',', '.')."</th>";
            $html .= "<th style='border: solid 1px #000; text-align: right;'>".number_format($int, 2, ',', '.')."</th>";
            $html .= "<th style='border: solid 1px #000; text-align: right;'>".number_format($cas, 2, ',', '.')."</th>";
            $html .= "<th style='border: solid 1px #000; text-align: right;'>".number_format($inc, 2, ',', '.')."</th>";
            $html .= "<th style='border: solid 1px #000; text-align: right;'>".number_format($sob, 2, ',', '.')."</th>";
            $html .= "<th style='border: solid 1px #000; text-align: right;'>".number_format($pap, 2, ',', '.')."</th>";
            $html .= "<th style='border: solid 1px #000; text-align: right;'>".number_format($deb, 2, ',', '.')."</th>";
            $html .= "<th style='border: solid 1px #000; text-align: right;'>".number_format($xto, 2, ',', '.')."</th>";
            $html .= "</tr>";
            $html .= "</tfoot>";
            $html .= "</table>";

            $html .= '<br>';

            $html .= "<table style='width: 100%;'>";
            $html .= "<thead>";
            $html .= "<tr>";
            $html .= "<th style='border: solid 1px #000;'></th>";
            $html .= "<th style='border: solid 1px #000;'>IMPUESTO PREDIAL</th>";
            $html .= "<th style='border: solid 1px #000;'>INTER&Eacute;S PREDIAL</th>";
            $html .= "<th style='border: solid 1px #000;'>SUMA DE VlrCAR</th>";
            $html .= "<th style='border: solid 1px #000;'>SUMA DE VlrINTERESCAR</th>";
            $html .= "<th style='border: solid 1px #000;'>OTROS</th>";
            $html .= "<th style='border: solid 1px #000;'>TOTAL</th>";
            $html .= "</tr>";
            $html .= "</thead>";
            $html .= "<tbody>";
            list($imp_predial, $int_predial, $sum_cas, $sum_int_cas, $otros, $total) = array(0, 0, 0, 0, 0, 0);
            list($imp_predial_c1, $imp_predial_c2, $imp_predial_c3, $imp_predial_c4) = array(0, 0, 0, 0);
            list($int_predial_c1, $int_predial_c2, $int_predial_c3, $int_predial_c4) = array(0, 0, 0, 0);
            list($sum_car_c1, $sum_car_c2, $sum_car_c3, $sum_car_c4) = array(0, 0, 0, 0);
            list($sum_int_car_c1, $sum_int_car_c2, $sum_int_car_c3, $sum_int_car_c4) = array(0, 0, 0, 0);
            list($otros_c1, $otros_c2, $otros_c3, $otros_c4) = array(0, 0, 0, 0);
            list($imp_predial_c1, $imp_predial_c2, $imp_predial_c3, $imp_predial_c4) = array(0, 0, 0, 0);
            $x = 5;
            foreach ($data as $rw){
                if($rw->anno == $anno_cartera){
                    $imp_predial_c4 += $rw->imp;
                    $int_predial_c4 += $rw->int;
                    $sum_car_c4 += $rw->cas;
                    $sum_int_car_c4 += $rw->inc;
                    $otros_c4 += ($rw->sob + $rw->pap + $rw->deb);
                }

                else if($rw->anno < ($anno_cartera - 5)){
                    $imp_predial_c1 += $rw->imp;
                    $int_predial_c1 += $rw->int;
                    $sum_car_c1 += $rw->cas;
                    $sum_int_car_c1 += $rw->inc;
                    $otros_c1 += ($rw->sob + $rw->pap + $rw->deb);
                }else if((($anno_cartera - 4) == $rw->anno) || ($rw->anno == ($anno_cartera - 5))){
                    $imp_predial_c2 += $rw->imp;
                    $int_predial_c2 += $rw->int;
                    $sum_car_c2 += $rw->cas;
                    $sum_int_car_c2 += $rw->inc;
                    $otros_c2 += ($rw->sob + $rw->pap + $rw->deb);
                }else if((($anno_cartera - 1) == $rw->anno) || ($rw->anno == ($anno_cartera - 3)) || ($rw->anno == ($anno_cartera - 2))){
                    $imp_predial_c3 += $rw->imp;
                    $int_predial_c3 += $rw->int;
                    $sum_car_c3 += $rw->cas;
                    $sum_int_car_c3 += $rw->inc;
                    $otros_c3 += ($rw->sob + $rw->pap + $rw->deb);
                }
            }
            $imp_predial = $imp_predial_c1 + $imp_predial_c2 + $imp_predial_c3 + $imp_predial_c4;
            $int_predial = $int_predial_c1 + $int_predial_c2 + $int_predial_c3 + $int_predial_c4;
            $sum_cas = $sum_car_c1 + $sum_car_c2 + $sum_car_c3 + $sum_car_c4;
            $sum_int_cas = $sum_int_car_c1 + $sum_int_car_c2 + $sum_int_car_c3 + $sum_int_car_c4;
            $otros = $otros_c1 + $otros_c2 + $otros_c3 + $otros_c4;

            $tipos_cartera = [
                'CARTERA DE MAS DE 5 A&Ntilde;OS' => [
                    'IMPUESTO_PREDIAL' => $imp_predial_c1,
                    'INTERES_PREDIAL' => $int_predial_c1,
                    'SUMA_CAR' => $sum_car_c1,
                    'SUMA_INT_CAR' => $sum_int_car_c1,
                    'OTROS' => $otros_c1,
                    'TOTAL' => $imp_predial_c1 + $int_predial_c1 + $sum_car_c1 + $sum_int_car_c1 + $otros_c1
                ],
                'CARTERA DE 4 A 5 A&Ntilde;OS' => [
                    'IMPUESTO_PREDIAL' => $imp_predial_c2,
                    'INTERES_PREDIAL' => $int_predial_c2,
                    'SUMA_CAR' => $sum_car_c2,
                    'SUMA_INT_CAR' => $sum_int_car_c2,
                    'OTROS' => $otros_c2,
                    'TOTAL' => $imp_predial_c2 + $int_predial_c2 + $sum_car_c2 + $sum_int_car_c2 + $otros_c2
                ],
                'CARTERA DE 1 A 3 A&Ntilde;OS' => [
                    'IMPUESTO_PREDIAL' => $imp_predial_c3,
                    'INTERES_PREDIAL' => $int_predial_c3,
                    'SUMA_CAR' => $sum_car_c3,
                    'SUMA_INT_CAR' => $sum_int_car_c3,
                    'OTROS' => $otros_c3,
                    'TOTAL' => $imp_predial_c3 + $int_predial_c3 + $sum_car_c3 + $sum_int_car_c3 + $otros_c3
                ],
                'VIGENCIA ACTUAL' => [
                    'IMPUESTO_PREDIAL' => $imp_predial_c4,
                    'INTERES_PREDIAL' => $int_predial_c4,
                    'SUMA_CAR' => $sum_car_c4,
                    'SUMA_INT_CAR' => $sum_int_car_c4,
                    'OTROS' => $otros_c4,
                    'TOTAL' => $imp_predial_c4 + $int_predial_c4 + $sum_car_c4 + $sum_int_car_c4 + $otros_c4
                ]
            ];

            foreach($tipos_cartera as $key => $value){
                $html .= "<tr>";
                $html .= "<td width='15%' style='border: solid 1px #000; text-align: left; font-weight: bold;'>".$key."</td>";
                $html .= "<td style='border: solid 1px #000; text-align: right;'>".number_format($value['IMPUESTO_PREDIAL'], 2, ',', '.')."</td>";
                $html .= "<td style='border: solid 1px #000; text-align: right;'>".number_format($value['INTERES_PREDIAL'], 2, ',', '.')."</td>";
                $html .= "<td style='border: solid 1px #000; text-align: right;'>".number_format($value['SUMA_CAR'], 2, ',', '.')."</td>";
                $html .= "<td style='border: solid 1px #000; text-align: right;'>".number_format($value['SUMA_INT_CAR'], 2, ',', '.')."</td>";
                $html .= "<td style='border: solid 1px #000; text-align: right;'>".number_format($value['OTROS'], 2, ',', '.')."</td>";
                $html .= "<td style='border: solid 1px #000; text-align: right;'>".number_format($value['TOTAL'], 2, ',', '.')."</td>";
                $html .= "</tr>";
                $total += $value['TOTAL'];
            }


            $html .= "</tbody>";
            $html .= "<tfoot>";
            $html .= "<tr>";
            $html .= "<th style='border: solid 1px #000; text-align: center;'>TOTAL CARTERA PREDIAL</th>";
            $html .= "<th style='border: solid 1px #000; text-align: right;'>".number_format($imp_predial, 2, ',', '.')."</th>";
            $html .= "<th style='border: solid 1px #000; text-align: right;'>".number_format($int_predial, 2, ',', '.')."</th>";
            $html .= "<th style='border: solid 1px #000; text-align: right;'>".number_format($sum_cas, 2, ',', '.')."</th>";
            $html .= "<th style='border: solid 1px #000; text-align: right;'>".number_format($sum_int_cas, 2, ',', '.')."</th>";
            $html .= "<th style='border: solid 1px #000; text-align: right;'>".number_format($otros, 2, ',', '.')."</th>";
            $html .= "<th style='border: solid 1px #000; text-align: right;'>".number_format($total, 2, ',', '.')."</th>";
            $html .= "</tr>";
            $html .= "</tfoot>";
            $html .= "</table>";
            $html .= "</body>";
            $html .= "</html>";
            echo $html;
        }
    }

}