<?php

namespace Softsat\Locacao\Controller\Admin;

use Hwapx\Admin\Controller\Admin\Admin;
use Hwapx\Core\Library\Template;
use HwapX\Core\Library\DB\SQL\Mapper;

class Dashboard extends Admin {

    public function index($f3)
    {

        $f3->set('page.title', 'Dashboard');
        $f3->set('page.description', 'Inicio');

        $template = Template::instance();

        $template->place('dashboard', $template->rendered('softsat/locacao/dashboard/chart.html', array(), 0));

        echo Template::instance()->render('softsat/locacao/dashboard.html', null, $data);
    }

    public function chart($f3) {
        $de  = convertDatetime($f3->get('GET.de'), 'd/m/Y', 'Y-m-d');
        $ate = convertDatetime($f3->get('GET.ate'), 'd/m/Y', 'Y-m-d');

        if(!$de || !$ate)
            return;

        echo json_encode(
            array(
                'error' => false,
                'params' => $f3->get('GET'),
                'data' => array(
                    'status'   => $this->dadosGraficoStatus($de, $ate),
                    'locacoes' => $this->dadosGraficoLocacoes($de, $ate),
                    'locacoes_veiculos' => $this->dadosGraficoVeiculos($de, $ate),
                    'faturamento' => $this->dadosGraficoFaturamento($de, $ate),
                    'faturamento_veiculos' => $this->dadosGraficoFaturamentoVeiculos($de, $ate),
                )
            )
        );
    }

    private function dadosGraficoLocacoes($de, $ate) {
        $db = $this->f3->get('db');

        $rows = $db->exec(
            "SELECT COUNT(1) AS count, DATE(IF(status = 'F', entrada, saida)) AS `date`, status
                FROM softsat_locacao_contrato
                WHERE DATE(IF(status = 'F', entrada, saida)) BETWEEN ? AND ? 
                GROUP BY IF(status = 'F', entrada, saida), status 
                ORDER BY entrada ASC, saida ASC", 
            array($de, $ate)
        );

        $locacoes = array();

        foreach ($rows as $row) {
            $locacoes[$row['date']]['date']   = $row['date'];

            $locacoes[$row['date']][$row['status']] = $row['count'];
        }

        $days = (strtotime($ate) - strtotime($de)) / 60 / 60 / 24;

        for($i = 0; $i <= $days; $i++) {
            $data = date('Y-m-d', strtotime("$de +$i day"));
            if(!isset($locacoes[$data]))
                $locacoes[$data] = array();

            $locacoes[$data] = array_merge(array('date' => $data, 'R' => 0, 'A' => 0, 'F' => 0, 'C' => 0), $locacoes[$data]);
        }

        return array_values($locacoes);
    }

    private function dadosGraficoVeiculos($de, $ate) {
        $db = $this->f3->get('db');

        $rows = $db->exec(
            "SELECT veiculo_id, CONCAT_WS(' ', veiculo_marca, veiculo_modelo, '-', veiculo_placa) AS veiculo, 
                COUNT(1) AS count, status FROM softsat_locacao_contrato_relatorio
                WHERE DATE(saida) BETWEEN ? AND ? 
                GROUP BY veiculo_id, status
                ORDER BY veiculo_marca, veiculo_modelo, veiculo_placa ASC",
            array($de, $ate)
        );

        $results = array();

        foreach($rows as $row) {
            $veiculo_id = $row['veiculo_id'];

            if(!isset($results[$veiculo_id]))
                $results[$veiculo_id] = array(
                    'y' => $row['veiculo'],
                    'R' => 0,
                    'A' => 0,
                    'F' => 0,
                    'C' => 0,
                );

            $results[$veiculo_id][$row['status']] += $row['count'];
        }

        return array_values($results);
    }

    private function dadosGraficoFaturamento($de, $ate) {
        $db = $this->f3->get('db');

        $rows = $db->exec(
            "SELECT SUM(total) AS receita, 0 AS despesa, DATE(entrada) AS `data`
                FROM softsat_locacao_contrato 
                WHERE status = 'F' AND entrada IS NOT NULL
                AND DATE(entrada) BETWEEN :de AND :ate 
                GROUP BY DATE(entrada) 
                UNION ALL
                SELECT 0 AS receita, SUM(valor) AS despesa, DATE(data) AS `data`
                FROM softsat_locacao_despesa
                WHERE DATE(data) BETWEEN :de AND :ate 
                GROUP BY DATE(data)",
            array(':de' => $de, ':ate' => $ate)
        );

        $faturamento = array();

        foreach($rows as &$row) {
            $item = &$faturamento[$row['data']];

            if(!$item)
                $item = array(
                    'receita' => 0,
                    'despesa' => 0,
                    'data'    => $row['data']
                );

            $item['receita'] += (float)$row['receita'];
            $item['despesa'] += (float)$row['despesa'];
        }

        $days = (strtotime($ate) - strtotime($de)) / 60 / 60 / 24;

        for($i = 0; $i <= $days; $i++) {
            $data = date('Y-m-d', strtotime("$de +$i day"));
            $item = &$faturamento[$data];
            if(!$item)
                $item = array('data' => $data, 'receita' => 0, 'despesa' => 0);
        }

        return array_values($faturamento);
    }

    private function dadosGraficoStatus($de, $ate) {
        $db = $this->f3->get('db');

        $rows_balanco = $db->exec(
            "SELECT status, COUNT(1) as count FROM softsat_locacao_contrato 
                WHERE entrada BETWEEN :de AND :ate OR saida BETWEEN :de AND :ate
                GROUP BY status",
            array(':de' => $de, ':ate' => $ate)
        );

        $balanco = array('R' => 0, 'A' => 0, 'F' => 0, 'C' => 0);

        foreach($rows_balanco as $row_balanco) {
            $balanco[$row_balanco['status']] = (int)$row_balanco['count'];
        }

        return $balanco;
    }

    public function dadosGraficoFaturamentoVeiculos($de, $ate) {
        $db = $this->f3->get('db');

        $rows = $db->exec(
            "SELECT 
                veiculo_id, 
                CONCAT_WS(' ', veiculo_marca, veiculo_modelo, '-', veiculo_placa) AS veiculo, 
                0 AS receita, 
                SUM(valor) AS despesa 
                FROM softsat_locacao_despesa_veiculo
                WHERE data BETWEEN :de AND :ate
                GROUP BY veiculo_id, veiculo_nome
                UNION ALL
                SELECT 
                veiculo_id, 
                CONCAT_WS(' ', veiculo_marca, veiculo_modelo, '-', veiculo_placa) AS veiculo, 
                SUM(total) AS receita, 
                0 AS despesa 
                FROM softsat_locacao_contrato_relatorio
                WHERE status = 'F' AND entrada BETWEEN :de AND :ate
                GROUP BY veiculo_id, veiculo_nome",
            array(':de' => $de, ':ate' => $ate)
        );

        $faturamento = array();

        foreach($rows as &$row) {
            $item = &$faturamento[$row['veiculo_id']];

            if(!$item)
                $item = array(
                    'receita' => 0,
                    'despesa' => 0,
                    'veiculo' => $row['veiculo']
                );

            $item['receita'] += (float)$row['receita'];
            $item['despesa'] += (float)$row['despesa'];
        }

        return array_values($faturamento);
    }
}
