<?php

namespace Predial\Http\Controllers;

use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Predial\ComprobanteCnt;
use Predial\ComprobantePptal;
use Predial\DetalleComprobanteCnt;
use Predial\DetalleComprobantePptal;
use Predial\FacturaPredial;
use Predial\DetalleFacturaPredial;
use Predial\DetallePagoPredial;
use PDF;

class ComprobanteIngreso extends Controller{

    public static function index($detalle){
        $detalle  = decrypt($detalle);
        $compania = ComprobanteIngreso::getCompany(session()->get('compania'));
        $idCnt    = ComprobanteIngreso::getComprobante($detalle);
        $dataCnt  = ComprobanteIngreso::getDataCnt($idCnt);
        $iddptal  = ComprobanteIngreso::getIdPptal($detalle);
        $idPtal   = ComprobanteIngreso::getDataDP($iddptal);
        $dataPtal = ComprobanteIngreso::getDataPtal($idPtal);
        $xhtml    = "";
        $xhtml   .= ComprobanteIngreso::setHtml();
        $xhtml   .= ComprobanteIngreso::setHead($compania, $dataCnt);
        $xhtml   .= ComprobanteIngreso::setSubHead($dataCnt);
        $xhtml   .= ComprobanteIngreso::setTable($idCnt);
        $xhtml   .= ComprobanteIngreso::setEnd();
        $pdf      = \App::make('dompdf.wrapper');
        $pdf->loadHTML($xhtml);
        return $pdf->stream("infIngreso.pdf");
    }

    public static function getCompany($compania){
        try {
            $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', '=', $compania)
                        ->first();
            return $data;
        } catch (Exception $e) {
            return $e->getMessage();
        }
    }

    public static function setHtml(){
        $html  = "";
        $html .= "<!DOCTYPE html>";
        $html .= "<html lang=".app()->getLocale().">";
        $html .= "\n<head>";
        $html .= "\n\t<meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\" />";
        $html .= "\n\t<meta http-equiv=\"X-UA-Compatible\" content=\"IE=edge\">";
        $html .= "\n\t<meta name=\"viewport\" content=\"width=device-width, user-scalable=no\">";
        $html .= "\n\t<meta name=\"description\" content=\"Sistema Integrado\">";
        $html .= "\n\t<meta name=\"description\" content=\"Sistema Contable\">";
        $html .= "\n\t<link media=\"all\" rel=\"stylesheet\" type=\"text/css\" href=\"css/bootstrap.css\">";
        $html .= "\n\t<title>Comprobante de Ingreso</title>";
        $html .= "\n</head>";
        $html .= "\n<body style='font-family:\"Courier\"'>";
        return $html;
    }

    public static function setEnd(){
        $html  = "";
        $html .= "\n<body>";
        $html .= "\n</html>";
        return $html;
    }

    public static function setHead($compania, $cnt){
        $html = "";
        $html .= "\n\t<table style='width: 100%;' >";
        $html .= "\n\t\t<tr>";
        $html .= "\n\t\t\t<td style='width: 80px'><img src='$compania->logo' style='width: 80px;'/></td>";
        $html .= "\n\t\t\t<td style='text-align:center; margin-right: -80px; font-weight:800; font-size: 14px'>".mb_strtoupper($compania->razonsocial);
        $html .= "<br/>";
        $html .= $compania->tipodoc.":".$compania->numeroi;
        $html .= "<br/>";
        $html .= mb_strtoupper($cnt->tipo)." Nro: ".$cnt->numero;
        $html .= "\n\t\t\t</td>";
        $html .= "\n\t\t</tr>";
        $html .= "\n\t</table>";
        return $html;
    }

    public static function getComprobante($detalle){
        try {
            $data = DetalleComprobanteCnt::find($detalle);
            return $data->comprobante;
        } catch (Exception $e) {
            return $e->getMessage();
        }
    }

    public static function getDataCnt($id){
        try {
            $data = DB::table('gf_comprobante_cnt as cnt')
                        ->select(
                            'cnt.numero', 'tpc.nombre as tipo', DB::raw('DATE_FORMAT(cnt.fecha, "%d/%m/%Y") as fecha'),
                            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 tercero'
                            ),
                            'ter.numeroidentificacion as numdoc',
                            'cnt.descripcion as des'
                        )
                        ->leftJoin('gf_tipo_comprobante as tpc', 'cnt.tipocomprobante', '=', 'tpc.id_unico')
                        ->leftJoin('gf_tercero as ter', 'cnt.tercero', '=', 'ter.id_unico')
                        ->where('cnt.id_unico', '=', $id)
                        ->first();
            return $data;
        } catch (Exception $e) {
            return $e->getMessage();
        }
    }

    public static function getDataPtal($id){
        try {
            $data = ComprobantePptal::find($id);
            return $data;
        } catch (Exception $e) {
            return $e->getMessage();
        }
    }

    public static function getIdPptal($detalle){
        try {
            $data = DB::table('gf_detalle_comprobante')
                        ->select('detallecomprobantepptal as dtp')
                        ->where('id_unico', '=', $detalle)
                        ->whereNotNull('detallecomprobantepptal')
                        ->first();
            if(count($data) > 0){
                return $data->dtp;
            }else{
                return 0;
            }
        } catch (Exception $e) {
            return $e->getMessage();
        }
    }

    public static function getDataDP($id){
        try {
            $data = DetalleComprobantePptal::find($id);
            if(count($data) > 0){
                return $data->comprobantepptal;
            }else{
                return 0;
            }
        } catch (Exception $e) {

        }
    }

    public static function setSubHead($dataCnt){
        $fecha = ComprobanteIngreso::createDateCarbon($dataCnt->fecha);
        $mes   = ComprobanteIngreso::getNameMonth($fecha->format("m"));
        $html = "";
        $html .= "\n\t<table style='width: 100%; border-collapse: collapse; border: 1px solid #000'>";
        $html .= "\n\t\t<tr style='font-size: 12px'>";
        $html .= "\n\t\t\t<td style='font-weight:800; text-align:right; width: 100px'>FECHA:</td>";
        $html .= "\n\t\t\t<td style='text-align:left;'>".$fecha->format("d")." de  $mes de ".$fecha->format("Y")."</td>";
        $html .= "\n\t\t\t<td style='font-weight:800; text-align:right; width: 100px'>VENCE:</td>";
        $html .= "\n\t\t\t<td style='text-align:left; width: 100px'>$dataCnt->fecha</td>";
        $html .= "\n\t\t</tr>";
        $html .= "\n\t\t<tr style='font-size: 12px'>";
        $html .= "\n\t\t\t<td style='font-weight:800; text-align:right; width: 100px'>RECIBI DE:</td>";
        $html .= "\n\t\t\t<td style='text-align:left;'>$dataCnt->tercero</td>";
        $html .= "\n\t\t\t<td style='font-weight:800; text-align:right; width: 100px'>CC / NIT:</td>";
        $html .= "\n\t\t\t<td style='text-align:left; width: 100px'>$dataCnt->numdoc</td>";
        $html .= "\n\t\t</tr>";
        $html .= "\n\t\t<tr style='font-size: 12px;'>";
        $html .= "\n\t\t\t<td style='font-weight:800; text-align:right; width: 100px; border-top: 1px solid #000'>DESCRIPCIÓN:</td>";
        $html .= "\n\t\t\t<td style='text-align:left; border-top: 1px solid #000' colspan='3'>$dataCnt->des</td>";
        $html .= "\n\t\t</tr>";
        $html .= "\n\t</table>";
        return $html;
    }

    public static function getNameMonth($mes){
        $mes = (int) $mes;
        $nom = ['no','Enero','Febrero','Marzo','Abril','Mayo','Junio','Julio','Agosto','Septiembre','Octubre','Noviembre','Diciembre'];
        return $nom[$mes];
    }

    public static function createDateCarbon($fecha){
        $ff    = ComprobanteIngreso::separarFecha($fecha);
        $fecha = Carbon::createFromDate($ff[2], $ff[1], $ff[0]);
        return $fecha;
    }

    public static function separarFecha($fecha){
        return explode("/", $fecha);
    }

    public static function getDataDetails($idCnt){
        try {
            $data = DB::select("SELECT DISTINCT dtc.id_unico as dtcid, ct.id_unico as ctid, ct.nombre as ctnom, rb.id_unico rubro,
                                                rb.codi_presupuesto as rbcod, rb.nombre as rbnom, cnt.id_unico cuenta,
                                                cnt.codi_cuenta as cod_cta, cnt.nombre ctanom, cnt.naturaleza as ctanat,
                                                dtc.valor, pr.id_unico proyecto, pr.nombre as prnom, ctr.id_unico centroc,
                                                ctr.nombre as ctrnom, dtc.tercero, pptal.id_unico, ft.nombre ftnom, pptal.id_unico,
                                                dtc.conciliado,
                                                (if(concat_ws(' ', ter.nombreuno, ter.nombredos, ter.apellidouno, ter.apellidodos) = ' ',
                                                    ter.razonsocial = ' ',
                                                    concat_ws(' ', ter.nombreuno, ter.nombredos, ter.apellidouno, ter.apellidodos)
                                                )) as tercero
                                FROM            gf_detalle_comprobante dtc
                                LEFT JOIN       gf_detalle_comprobante_pptal pptal ON dtc.detallecomprobantepptal = pptal.id_unico
                                LEFT JOIN       gf_concepto_rubro cnr ON pptal.conceptoRubro = cnr.id_unico
                                LEFT JOIN       gf_concepto ct ON cnr.concepto = ct.id_unico
                                LEFT JOIN       gf_rubro_fuente rbf ON rbf.id_unico = pptal.rubrofuente
                                LEFT JOIN       gf_rubro_pptal rb ON rbf.rubro = rb.id_unico
                                LEFT JOIN       gf_fuente ft ON rbf.fuente = ft.id_unico
                                LEFT JOIN       gf_concepto_rubro_cuenta ctrb ON cnr.id_unico = ctrb.concepto_rubro
                                LEFT JOIN       gf_cuenta cnt ON dtc.cuenta = cnt.id_unico
                                LEFT JOIN       gf_proyecto pr ON dtc.proyecto = pr.id_unico
                                LEFT JOIN       gf_centro_costo ctr ON dtc.centrocosto = ctr.id_unico
                                LEFT JOIN       gf_tercero ter ON dtc.tercero = ter.id_unico
                                WHERE           dtc.comprobante = $idCnt");
            return $data;
        } catch (Exception $e) {
            return $e->getMessage();
        }
    }

    public static function setTable($id){
        $data = ComprobanteIngreso::getDataDetails($id);
        $html = "";
        $html .= "\n\t<table style='width: 100%; font-size: 11px; margin-top: 15px' border='1'>";
        $html .= "\n\t\t<thead>";
        $html .= "\n\t\t\t<tr>";
        $html .= "\n\t\t\t\t<th>CONCEPTO</th>";
        $html .= "\n\t\t\t\t<th>RUBRO FUENTE</th>";
        $html .= "\n\t\t\t\t<th>CUENTA</th>";
        $html .= "\n\t\t\t\t<th>DEBITO</th>";
        $html .= "\n\t\t\t\t<th>CREDITO</th>";
        $html .= "\n\t\t\t\t<th>CENTRO COSTO</th>";
        $html .= "\n\t\t\t\t<th>TERCERO</th>";
        $html .= "\n\t\t\t</tr>";
        $html .= "\n\t\t</thead>";
        $html .= "\n\t\t<tbody style='font-size: 9px;'>";
        $xdeb = 0; $xcred = 0;
        foreach ($data as $row) {
            $deb = 0; $cred = 0;
            $rubro  = $row->rbcod." ".$row->rbnom." ".$row->ftnom;
            $cuenta = $row->cod_cta." ".$row->ctanom;
            if($row->ctanat == 1){
                if($row->valor < 0){
                    $x      = $row->valor * -1;
                    $cred   = $x;
                    $xcred += $x;
                }else{
                    $deb   = $row->valor;
                    $xdeb += $row->valor;
                }
            }else{
                if($row->valor > 0){
                    $cred   = $row->valor;
                    $xcred += $row->valor;
                }else{
                    $x      = $row->valor * -1;
                    $deb    = $x;
                    $xdeb  += $x;
                }
            }
            $html .= "\n\t\t\t<tr>";
            $html .= "\n\t\t\t\t<td>$row->ctnom</td>";
            $html .= "\n\t\t\t\t<td>$rubro</td>";
            $html .= "\n\t\t\t\t<td>$cuenta</td>";
            $html .= "\n\t\t\t\t<td>".number_format($deb, 2, '.', ',')."</td>";
            $html .= "\n\t\t\t\t<td>".number_format($cred, 2, '.', ',')."</td>";
            $html .= "\n\t\t\t\t<td>$row->ctrnom</td>";
            $html .= "\n\t\t\t\t<td>".ucwords(mb_strtolower($row->tercero))."</td>";
            $html .= "\n\t\t\t</tr>";
        }
        $html .= "\n\t\t</tbody>";
        $html .= "\n\t\t<tfoot>";
        $html .= "\n\t\t\t<tr>";
        $html .= "\n\t\t\t\t<td colspan='3' style='font-weight:800; text-align:right'>Totales</td>";
        $html .= "\n\t\t\t\t<td>".number_format($xdeb, 2, '.', ',')."</td>";
        $html .= "\n\t\t\t\t<td>".number_format($xcred, 2, '.', ',')."</td>";
        $html .= "\n\t\t\t\t<td></td>";
        $html .= "\n\t\t\t\t<td></td>";
        $html .= "\n\t\t\t</tr>";
        $html .= "\n\t\t</tfoot>";
        $html .= "\n\t</table>";
        return $html;
    }

    public static function nivelarPagos(){
        $data = DB::select("SELECT SUM(valor) as DataV, ROUND(SUM(valor)) as formateado, ROUND(SUM(valor)) - SUM(valor) as diferencia, pago FROM gr_detalle_pago_predial GROUP BY pago");
        $xhtml  = "<table border='1'>";
        $xhtml .= "<thead>";
        $xhtml .= "<tr>";
        $xhtml .= "<th>Valor</th>";
        $xhtml .= "<th>Valor Formateado</th>";
        $xhtml .= "<th>Diferencia</th>";
        $xhtml .= "<th>Resultado</th>";
        $xhtml .= "<th>Pago</th>";
        $xhtml .= "<th>Detalle Pago</th>";
        $xhtml .= "<th>Valor Detalle Pago</th>";
        $xhtml .= "<th>Resultado</th>";
        $xhtml .= "</tr>";
        $xhtml .= "</thead>";
        $xhtml .= "<tbody>";
        foreach ($data as $row) {
            $x = $row->DataV + $row->diferencia;
            $d = ComprobanteIngreso::obtenerIdDetallePago($row->pago);
            $t = ComprobanteIngreso::obtnerValorDetallePago($d);
            $r = $t->valor + $row->diferencia;
            $xhtml .= "<tr>";
            $xhtml .= "<td>$row->DataV</td>";
            $xhtml .= "<td>$row->formateado</td>";
            $xhtml .= "<td>$row->diferencia</td>";
            if($row->DataV == $row->formateado){
                $xhtml .= "<td style='background:green'>$x</td>";
            }else{
                $a = ComprobanteIngreso::actualizarDetallePago($t->id_unico, $r);
                $xhtml .= "<td style='background:red'>$x</td>";
            }
            $xhtml .= "<td>$row->pago</td>";
            $xhtml .= "<td>$d</td>";
            $xhtml .= "<td>$t->valor</td>";
            $xhtml .= "<td>$r</td>";
            $xhtml .= "</tr>";
        }
        $xhtml .= "<tbody>";
        $xhtml .= "</table>";
        echo $xhtml;
    }

    public static function nivelarFactura(){
        $data = DB::select("SELECT SUM(valor) as DataV, ROUND(SUM(valor)) as formateado, ROUND(SUM(valor)) - SUM(valor) as diferencia, factura FROM gr_detalle_factura_predial GROUP BY factura");
        $xhtml  = "<table border='1'>";
        $xhtml .= "<thead>";
        $xhtml .= "<tr>";
        $xhtml .= "<th>Valor</th>";
        $xhtml .= "<th>Valor Formateado</th>";
        $xhtml .= "<th>Diferencia</th>";
        $xhtml .= "<th>Resultado</th>";
        $xhtml .= "<th>Factura</th>";
        $xhtml .= "<th>Detalle Factura</th>";
        $xhtml .= "<th>Valor Detalle Factura</th>";
        $xhtml .= "<th>Resultado</th>";
        $xhtml .= "</tr>";
        $xhtml .= "</thead>";
        $xhtml .= "<tbody>";
        foreach ($data as $row){
            $x = $row->DataV + $row->diferencia;
            $d = ComprobanteIngreso::obtenerIdDetalle($row->factura);
            $t = ComprobanteIngreso::obtnerValorDetalle($d);
            $r = $t->valor + $row->diferencia;
            $xhtml .= "<tr>";
            $xhtml .= "<td>$row->DataV</td>";
            $xhtml .= "<td>$row->formateado</td>";
            $xhtml .= "<td>$row->diferencia</td>";
            if($row->DataV == $row->formateado){
                $xhtml .= "<td style='background:green'>$x</td>";
            }else{
                $a = ComprobanteIngreso::actualizarDetalle($t->id_unico, $r);
                $xhtml .= "<td style='background:red'>$x</td>";
            }
            $xhtml .= "<td>$row->factura</td>";
            $xhtml .= "<td>$d</td>";
            $xhtml .= "<td>$t->valor</td>";
            $xhtml .= "<td>$r</td>";
            $xhtml .= "</tr>";
            if($row->DataV != $row->formateado){

            }
        }
        $xhtml .= "<tbody>";
        $xhtml .= "</table>";
        echo $xhtml;
    }

    public static function obtenerIdDetalle($factura){
        $data = DB::table('gr_detalle_factura_predial')->where('factura', '=', $factura)->min('id_unico');
        return $data;
    }

    public static function obtnerValorDetalle($id){
        $data = DetalleFacturaPredial::find($id);
        return $data;
    }

    public static function actualizarDetalle($id, $valor){
        $data = DB::table("gr_detalle_factura_predial")->where('id_unico', $id)->update(["valor" => $valor]);
        return $data;
    }

    public static function obtenerIdDetallePago($pago){
        $data = DB::table('gr_detalle_pago_predial')->where('pago', '=', $pago)->min('id_unico');
        return $data;
    }

    public static function obtnerValorDetallePago($id){
        $data = DetallePagoPredial::find($id);
        return $data;
    }

    public static function actualizarDetallePago($id, $valor){
        $data = DB::table("gr_detalle_pago_predial")->where('id_unico', $id)->update(["valor" => $valor]);
        return $data;
    }
}
