<?php

namespace Predial;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Collection;

class DetalleFacturaPredial extends Model{

    public    $table      = "gr_detalle_factura_predial";
    protected $primaryKey = "id_unico";
    public    $timestamps = false;

    public static function obtnerDetalles($factura){
        try {
            $data = DB::table('gr_detalle_factura_predial as dtp')
                    ->select('con.nombre', DB::raw('SUM(dtp.valor) as valor'))
                    ->leftJoin('gr_concepto_predial as cpp', 'dtp.concepto', '=', 'cpp.id_unico')
                    ->leftJoin('gr_concepto as con', 'cpp.id_concepto', '=', 'con.id_unico')
                    ->groupBy('con.nombre')
                    ->orderBy('con.nombre', 'asc')
                    ->where('dtp.factura', '=', $factura)
                    ->get();
            return $data;
        } catch (Exception $e) {
            return $e->getMessage();
        }
    }

    public static function obtnerValorTotal($factura){
        try {
            $data = DB::table('gr_detalle_factura_predial as dtp')
                    ->select(DB::raw('SUM(dtp.valor) as valor'))
                    ->where('dtp.factura', '=', $factura)
                    ->first();
            return $data;
        } catch (Exception $e) {
            return $e->getMessage();
        }
    }

    public static function DescripcionDetalleAvaluo($factura){
        try {
            $data = DB::table('gr_detalle_factura_predial as dtp')
                        ->select(
                            'ava.anno',
                            'tar.valor as tarifa',
                            'ava.valor as avaluo',
                            DB::raw('sum(if(con.id_concepto = 1, dtp.valor, 0)) as impuesto'),
                            DB::raw('sum(if(con.id_concepto = 3, dtp.valor, 0)) as corporacion_regional'),
                            DB::raw('sum(if(con.id_concepto = 7, dtp.valor, 0)) as sobretasa'),
                            DB::raw('sum(if(con.id_concepto = 5, dtp.valor, 0)) as descuento'),
                            DB::raw('sum(if(con.id_concepto = 4, dtp.valor, 0)) as interes_mora'),
                            DB::raw('sum(if(con.id_concepto = 8, dtp.valor, 0)) as interes_corporacion'),
                            DB::raw('sum(if(con.id_concepto = 6, dtp.valor, 0)) as papeleria'),
                            DB::raw('sum(if(con.id_concepto = 9, dtp.valor, 0)) as cobrar_debido'),                            
                            DB::raw('sum(if(con.id_concepto = 4, dtp.dias, 0)) as dias_mora'),
                            DB::raw('sum(if(con.id_concepto = 8, dtp.dias, 0)) as dias_cor')
                            
                        )
                        ->leftJoin('gr_avaluo as ava', 'dtp.avaluo', '=', 'ava.id_unico')
                        ->leftJoin('gr_tarifa as tar', 'ava.tarifa', '=', 'tar.id_unico')
                        ->leftJoin('gr_concepto_predial as con', 'dtp.concepto', '=', 'con.id_unico')
                        ->where('dtp.factura', '=', $factura)
                        ->groupBy('ava.anno')
                        ->get();            
            return $data;
        } catch (Exception $e) {
            return $e->getMessage();
        }
    }

    public static function AcumuladoAnteriores($factura){
        try {
            $anno = array();
            $data = array();
            $dat_a = DB::table('gr_detalle_factura_predial as dtp')
                        ->select(
                            'ava.anno',
                            'tar.valor as tarifa',
                            'ava.valor as avaluo',
                            DB::raw('sum(if(con.id_concepto = 1, dtp.valor, 0)) as impuesto'),
                            DB::raw('sum(if(con.id_concepto = 3, dtp.valor, 0)) as corporacion_regional'),
                            DB::raw('sum(if(con.id_concepto = 7, dtp.valor, 0)) as sobretasa'),
                            DB::raw('sum(if(con.id_concepto = 5, dtp.valor, 0)) as descuento'),
                            DB::raw('sum(if(con.id_concepto = 4, dtp.valor, 0)) as interes_mora'),
                            DB::raw('sum(if(con.id_concepto = 8, dtp.valor, 0)) as interes_corporacion'),
                            DB::raw('sum(if(con.id_concepto = 6, dtp.valor, 0)) as papeleria'),
                            DB::raw('sum(if(con.id_concepto = 9, dtp.valor, 0)) as cobrar_debido'),                            
                            DB::raw('sum(if(con.id_concepto = 4, dtp.dias, 0)) as dias_mora'),
                            DB::raw('sum(if(con.id_concepto = 8, dtp.dias, 0)) as dias_cor')
                            
                        )
                        ->leftJoin('gr_avaluo as ava', 'dtp.avaluo', '=', 'ava.id_unico')
                        ->leftJoin('gr_tarifa as tar', 'ava.tarifa', '=', 'tar.id_unico')
                        ->leftJoin('gr_concepto_predial as con', 'dtp.concepto', '=', 'con.id_unico')
                        ->where('dtp.factura', '=', $factura)
                        ->groupBy('ava.anno')
                        ->orderBy('ava.anno', 'desc')
                        ->take(5)
                        ->get();
            foreach ($dat_a as $key => $value) {
                $data[] = array(
                    "anno" => $value->anno, "tarifa" => $value->tarifa, "avaluo" => $value->avaluo,
                    "impuesto" => $value->impuesto,  "corporacion_regional" => $value->corporacion_regional,
                    "sobretasa" => $value->sobretasa, "descuento" => $value->descuento, "interes_mora" => $value->interes_mora, 
                    "interes_corporacion" => $value->interes_corporacion, "cobrar_debido" => $value->cobrar_debido,
                    "dias_mora" => $value->dias_mora, "dias_cor" => $value->dias_cor, "papeleria" => $value->papeleria
                );

                $anno[] = $value->anno;
            }            

            $reg = end($anno);

            list(
                $tarifa, $avaluo, $impuesto, $sobretasa, $corporacion_regional, $descuento, $interes_mora, $interes_corporacion, $papeleria, $cobrar_debido,
                $dias_mora, $dias_cor
            ) = array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0); 
            $dat_b = DB::table('gr_detalle_factura_predial as dtp')
                        ->select(
                            'ava.anno',
                            'tar.valor as tarifa',
                            'ava.valor as avaluo',
                            DB::raw('sum(if(con.id_concepto = 1, dtp.valor, 0)) as impuesto'),
                            DB::raw('sum(if(con.id_concepto = 3, dtp.valor, 0)) as corporacion_regional'),
                            DB::raw('sum(if(con.id_concepto = 7, dtp.valor, 0)) as sobretasa'),
                            DB::raw('sum(if(con.id_concepto = 5, dtp.valor, 0)) as descuento'),
                            DB::raw('sum(if(con.id_concepto = 4, dtp.valor, 0)) as interes_mora'),
                            DB::raw('sum(if(con.id_concepto = 8, dtp.valor, 0)) as interes_corporacion'),
                            DB::raw('sum(if(con.id_concepto = 6, dtp.valor, 0)) as papeleria'),
                            DB::raw('sum(if(con.id_concepto = 9, dtp.valor, 0)) as cobrar_debido'),                            
                            DB::raw('sum(if(con.id_concepto = 4, dtp.dias, 0)) as dias_mora'),
                            DB::raw('sum(if(con.id_concepto = 8, dtp.dias, 0)) as dias_cor')
                            
                        )
                        ->leftJoin('gr_avaluo as ava', 'dtp.avaluo', '=', 'ava.id_unico')
                        ->leftJoin('gr_tarifa as tar', 'ava.tarifa', '=', 'tar.id_unico')
                        ->leftJoin('gr_concepto_predial as con', 'dtp.concepto', '=', 'con.id_unico')
                        ->where('dtp.factura', '=', $factura)
                        ->where('ava.anno', '<', $reg)
                        ->groupBy('ava.anno')
                        ->orderBy('ava.anno', 'desc')                        
                        ->get();
            foreach ($dat_b as $key => $value) {
                $tarifa               = $value->tarifa;
                $avaluo               += $value->avaluo;
                $impuesto             += $value->impuesto;
                $corporacion_regional += $value->corporacion_regional;
                $sobretasa            += $value->sobretasa;
                $descuento            += $value->descuento;
                $interes_mora         += $value->interes_mora;
                $interes_corporacion  += $value->interes_corporacion;
                $papeleria            += $value->papeleria;
                $cobrar_debido        += $value->cobrar_debido;
                $dias_mora            += $value->dias_mora;
                $dias_cor             += $value->dias_cor;

            } 

            $data[] = array(
                "anno" => "AÑOS", "tarifa" => "IMP", "avaluo" => "ANTERIORES", "impuesto" => $impuesto, "corporacion_regional" => $corporacion_regional,
                "sobretasa" => $sobretasa, "descuento" => $descuento, "interes_mora" => $interes_mora, "interes_corporacion" => $interes_corporacion,
                "papeleria" => $papeleria, "cobrar_debido" => $cobrar_debido, "dias_mora" => $dias_mora, "dias_cor" => $dias_cor
            );

            $data = Collection::make($data);
            return $data;
        } catch (Exception $e) {
            return $e->getMessage();
        }
    }
}
