<?php
use Illuminate\Database\Capsule\Manager as Capsule;

/**
 * Revenue By Gateway Report
 *
 * @category Report
 * @package  ClientExec
 * @author   Juan D. Bolivar <juan@clientexec.com>
 * @license  ClientExec License
 * @version  1.0
 * @link     http://www.clientexec.com
 *
 *************************************************
 *   1.0 Initial Report Released.  - Juan D. Bolivar
 ************************************************
 */

require_once 'modules/admin/models/Package.php';
require_once 'modules/clients/models/UserPackage.php';
require_once 'modules/billing/models/Currency.php';
require_once 'modules/clients/models/User.php';
require_once 'modules/billing/models/Invoice.php';

/**
 * Revenue_By_Gateway Report Class
 *
 * @category Report
 * @package  ClientExec
 * @author   Juan D. Bolivar <juan@clientexec.com>
 * @license  ClientExec License
 * @version  1.0
 * @link     http://www.clientexec.com
 */
class Revenue_By_Gateway extends Report
{
    private $lang;

    protected $featureSet = 'billing';

    function __construct($user = null, $customer = null)
    {
        $this->lang = lang('Revenue By Gateway');
        parent::__construct($user, $customer);
    }

    /**
     * Report Process Method
     *
     * @return null - direct output
     */
    function process()
    {
        include_once 'modules/admin/models/StatusAliasGateway.php' ;

        // Set the report information
        $this->SetDescription($this->user->lang('Displays the total revenue by gateway per month.'));

        @set_time_limit(0);

        // Load the currency information
        $amountOfMonths = ((isset($_REQUEST['amountOfMonths']))? $_REQUEST['amountOfMonths'] : 12);

        // Load the currency information
        $currency = new Currency($this->user);

        $currencyCode = ((isset($_REQUEST['currencycode']))? $_REQUEST['currencycode'] : $this->settings->get('Default Currency'));
        $currencyName = $currency->getName($currencyCode);

        $filter = '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'
            .'        '.$this->user->lang('Currency').': '
            .'        <select class="normalSelect2 w-25" name="currencycode" id="currencycode" value="'.CE_Lib::viewEscape($currencyCode).'" > ';

        $isSelectedCurrencyInTheList = false;

        //Get all currencies of all invoices
        $result = Capsule::table('invoice as i')
            ->join('currency as c', 'c.abrv', '=', 'i.currency')
            ->select('c.abrv', 'c.name')
            ->distinct()
            ->orderBy('c.name', 'ASC')
            ->get();

        foreach ($result as $row) {
            if (!$isSelectedCurrencyInTheList && $currencyName < $row->name) {
                $filter .= '<option value="'.$currencyCode.'" selected>'.$currencyName.'</option>';
                $isSelectedCurrencyInTheList = true;
            } elseif ($currencyCode == $row->abrv) {
                $isSelectedCurrencyInTheList = true;
            }

            $filter .= '<option value="'.$row->abrv.'" '.(($currencyCode == $row->abrv)? 'selected' : '').'>'.$row->name.'</option>';
        }

        if (!$isSelectedCurrencyInTheList) {
            $filter .= '<option value="'.$currencyCode.'" selected>'.$currencyName.'</option>';
            $isSelectedCurrencyInTheList = true;
        }

        $filter .= '</select>';

        $gatewaysUsed = array();

        //Get Gateways Used
        $result = Capsule::table('invoice as i')
            ->selectRaw('DISTINCT i.pluginused AS gatewayused')
            ->leftJoin('users as u', 'i.customerid', '=', 'u.id')
            ->whereRaw('DATE_FORMAT(i.datepaid, "%Y%m") <= DATE_FORMAT(CURDATE(), "%Y%m")')
            ->whereRaw('DATE_FORMAT(i.datepaid, "%Y%m") >= DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL - ? MONTH), "%Y%m")', [$amountOfMonths - 1])
            ->where('i.status', INVOICE_STATUS_PAID)
            ->where('i.currency', $currencyCode)
            ->orderBy('gatewayused', 'asc')
            ->get();

        foreach ($result as $row) {
            $gatewaysUsed[$row->gatewayused] = 0;
        }

        $gatewaysUsed[$this->user->lang('TOTAL')] = 0;

        $newMonthTotals  = array();

        //Initialize months revenue
        for ($monthsBackward = 0; $monthsBackward < $amountOfMonths; $monthsBackward++) {
            $newMonthTotals[date("Y M", mktime(0, 0, 0, date("m") - $monthsBackward, 1, date("Y")))] = $gatewaysUsed;
        }

        $newMonthTotals[$this->user->lang('TOTAL')] = $gatewaysUsed;

        //Get Paid Invoices
        $result = Capsule::table('invoice as i')
            ->selectRaw(
                'i.datepaid, '
                .'IFNULL(i.amount, 0) as amount, '
                .'i.pluginused as gatewayused '
            )
            ->leftJoin('users as u', 'i.customerid', '=', 'u.id')
            ->whereRaw('DATE_FORMAT(i.datepaid, "%Y%m") <= DATE_FORMAT(CURDATE(), "%Y%m")')
            ->whereRaw('DATE_FORMAT(i.datepaid, "%Y%m") >= DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL - ? MONTH), "%Y%m")', [$amountOfMonths - 1])
            ->where('i.status', INVOICE_STATUS_PAID)
            ->where('i.currency', $currencyCode)
            ->get();

        //Add invoices already generated
        foreach ($result as $row) {
            $monthAndYear = date("Y M", mktime(0, 0, 0, date("m", strtotime($row->datepaid)), 1, date("Y", strtotime($row->datepaid))));

            if (isset($newMonthTotals[$monthAndYear][$row->gatewayused])) {
                $newMonthTotals[$monthAndYear][$row->gatewayused] += $row->amount;
                $newMonthTotals[$monthAndYear][$this->user->lang('TOTAL')] += $row->amount;
                $newMonthTotals[$this->user->lang('TOTAL')][$row->gatewayused] += $row->amount;
                $newMonthTotals[$this->user->lang('TOTAL')][$this->user->lang('TOTAL')] += $row->amount;
            }
        }

        $this->SetDescription($this->user->lang('Displays revenue by gateway'));

        $MonthsToDisplay =
             '<form id="report" method="GET">'
            .'    <div style="text-align:center">'
            .'        Months to display: '
            .'        <input type="text" name="amountOfMonths" id="amountOfMonths" size="2" value="'.CE_Lib::viewEscape($amountOfMonths).'" onkeydown="if (event.keyCode == 13) { event.preventDefault(); }"> '
            .$filter
            .'        <input type=button name=search class="btn" value=\''.$this->user->lang('Display').'\' onclick="ChangeTable(document.getElementById(\'amountOfMonths\').value, document.getElementById(\'currencycode\').value);">'
            .'    </div>'
            .'</form>'
            .'</br>'
            .'<script type="text/javascript">'
            .'    function ChangeTable(amountOfMonths, currencycode){'
            .'        location.href="index.php?fuse=reports&view=viewreport&controller=index&report=Revenue+By+Gateway&type=Revenue&amountOfMonths="+amountOfMonths+"&currencycode="+currencycode;'
            .'    }'
            .'</script>';
        echo $MonthsToDisplay;

        $subGroup = array();

        foreach ($newMonthTotals as $monthAndYear => $monthData) {
            $prepend = '';
            $append = '';

            if ($monthAndYear == $this->user->lang('TOTAL')) {
                $prepend = '<b>';
                $append = '</b>';
            }

            $subGroupValues = array(
                $prepend.$monthAndYear.$append
            );

            foreach ($monthData as $gatewayName => $gatewayTotal) {
                $formattedGatewayTotal = $currency->format($currencyCode, $gatewayTotal, true, false);

                if ($gatewayTotal == 0) {
                    $formattedGatewayTotal = '<font color="grey">'.$formattedGatewayTotal.'</font>';
                }

                if ($gatewayName == $this->user->lang('TOTAL')) {
                    $prepend = '<b>';
                    $append = '</b>';
                }

                $subGroupValues[] = $prepend.$formattedGatewayTotal.$append;
            }

            $subGroup[] = $subGroupValues;
        }

        $labels = array(
            $this->user->lang('Month and Year')
        );

        foreach ($gatewaysUsed as $gatewayName => $gatewayZero) {
            switch ($gatewayName) {
                case $this->user->lang('TOTAL'):
                    $labels[] = $gatewayName;
                    break;
                case null:
                case 'none':
                    $labels[] = $this->user->lang('None');
                    break;
                default:
                    $labels[] = ($gatewayName != 'none')? $this->user->lang($this->settings->get('plugin_'.$gatewayName.'_Plugin Name')) : $this->user->lang('None');
                    break;
            }
        }

        $this->reportData[] = array(
            "group"     => $subGroup,
            "groupname" => $this->user->lang('Revenue By Gateway').' ('.$currencyCode.')',
            "label"     => $labels
        );
    }
}
