<?php

namespace Predial\Http\Controllers;

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

use Predial\TipoPredio;

class TipoPredioController extends Controller{

    public $path = "tipoPredio.";

    public function index(){
        if(!empty(session()->get('usuario'))){
            $data = TipoPredio::all();
            return view($this->path.'index')->with('data', $data);
        }else{
            return redirect("CerrarSesion");
        }
    }

    public function create(){
        if(!empty(session()->get('usuario'))){
            return view($this->path.'create');
        }else{
            return redirect("CerrarSesion");
        }
    }

    public function store(Request $request){
        try {
            $new = new TipoPredio();
            $new->timestamps = false;
            $new->nombre     = $request->txtNombre;
            $new->save();
            flash()->overlay('Información guardada correctamente', 'Información');
            return redirect('ListarTipoPredio');
        } catch (Exception $e) {
            return "Fatal Error".$e->getMessage();
        }
    }

    public function show(){

    }

    public function edit($id_unico){
        if(!empty(session()->get('usuario'))){
            $id   = decrypt($id_unico);
            $data = TipoPredio::find($id);
            return view($this->path.'edit')->with('data', $data);
        }else{
            return redirect("CerrarSesion");
        }
    }

    public function update(Request $request){
        try {
            $upd             = TipoPredio::findOrFail($request->id_unico);
            $upd->timestamps = false;
            $upd->nombre     = $request->txtNombre;
            $upd->save();
            flash()->overlay('Información modificada correctamente', 'Información');
            return redirect('ListarTipoPredio');
        } catch (Exception $e) {
            return "Fatal Error".$e->getMessage();
        }
    }

    public function destroy($id_unico){
        try {
            $id   = decrypt($id_unico);
            $del  = TipoPredio::findOrFail($id);
            $del->delete();
            flash()->overlay('Información eliminada correctamente', 'Información');
            return redirect('ListarTipoPredio');
        } catch (Exception $e) {
            return "Fatal Error".$e->getMessage();
        }
    }
    
    public function report(){
        if(!empty(session()->get('usuario'))){
            $data = TipoPredio::all();
            return view($this->path.'report')->with('data', $data);
        }else{
            return redirect("CerrarSesion");
        }
    }
    
    public function generate(Request $request){
        $tipoPredio = $request->tipoPredio;
        $predios = DB::table('gp_predio1 AS gpp')
                ->select('gpp.codigo_catastral', 'gpp.nombre', 'gpp.direccion', 'gpp.id_unico')
                ->leftJoin('gp_tipo_predio AS tipo', 'tipo.id_unico', '=', 'gpp.tipo_predio')
                ->where('gpp.estado', '!=', 3)
                ->where('gpp.tipo_predio', '=', $tipoPredio)
                ->groupBy('gpp.id_unico')
                ->orderBy('gpp.nombre', 'DESC')
                ->get();
        $name = 'INFORME_PREDIOS_POR_TIPO-'.date('Y-m-d').'.xls';
                
        header('Content-type: application/vnd.ms-excel; charset=utf-8');
        header('Content-Disposition: attachment;filename="'.$name.'"');
        
        $html = "";
        $html .= "\n\t<table width='100%'>";
        $html .= "\n\t<tr>";
        $html .= "\n\t\t<td style='border: solid 1px #000; text-align: center;'><strong>C&oacute;digo</strong></td>";
        $html .= "\n\t\t<td style='border: solid 1px #000; text-align: center;'><strong>Nombre</strong></td>";
        $html .= "\n\t\t<td style='border: solid 1px #000; text-align: center;'><strong>Direcci&oacute;n</strong></td>";
        $html .= "\n\t\t<td style='border: solid 1px #000; text-align: center;'><strong>Propietarios</strong></td>";
        $html .= "\n\t\t<td style='border: solid 1px #000; text-align: center;'><strong>Aval&uacute;o</strong></td>";
        $html .= "\n\t</tr>";
        $an = session()->get('anno');
        $anio = DB::table('gf_parametrizacion_anno as aa')
                ->select('aa.anno')
                ->where('aa.id_unico', '=', $an)
                ->first();
        setlocale(LC_MONETARY, 'es_CO');
        foreach ($predios as $row){
            $sum = DB::table('gr_avaluo as ava')
                ->select(DB::raw('sum(ava.valor) AS valor'))
                ->where('ava.predio', '=', $row->id_unico)
                ->where('ava.anno', '=', $anio->anno)
                ->first();
            $ter_predio = DB::table('gp_tercero_predio as tt')
                ->select('tt.tercero')
                ->where('tt.predio', '=', $row->id_unico)->get();
            $propietarios = '';
            foreach($ter_predio as $t){
                $pp = DB::table('gr_propietarios as pro')
                ->select('pro.nombres')
                ->where('pro.id_unico', '=', $t->tercero)
                ->first();
                if(count($ter_predio) > 1){
                    $propietarios .= $pp->nombres.', ';
                }else{
                    $propietarios .= $pp->nombres;
                }
            }
            $html .= "\n\t<tr>";
            $html .= "\n\t\t<td style='border: solid 1px #000; mso-number-format:\@'>".$row->codigo_catastral."</td>";
            $html .= "\n\t\t<td style='border: solid 1px #000;'>".$row->nombre."</td>";
            $html .= "\n\t\t<td style='border: solid 1px #000;'>".$row->direccion."</td>";
            $html .= "\n\t\t<td style='border: solid 1px #000;'>".$propietarios."</td>";
            $html .= "\n\t\t<td style='border: solid 1px #000;'>".str_replace("COP", "$", money_format('%i', $sum->valor))."</td>";
            $html .= "\n\t</tr>";
        }
        $html .= "\n\t</table>";
        
        echo $html;
    }
}
