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

/**
 * Transactions By Month 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';

/**
 * Transactions_By_Month 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 Transactions_By_Month extends Report
{
    private $lang;

    protected $featureSet = 'billing';

    function __construct($user = null, $customer = null)
    {
        $this->lang = lang('Transactions By Month');
        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 transactions by month.'));

        @set_time_limit(0);

        // 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>';

        $subGroup = array();
        $labels = array(
            $this->user->lang('Date'),
            $this->user->lang('User'),
            $this->user->lang('Invoice'),
            $this->user->lang('Action'),
            $this->user->lang('Transaction ID'),
            $this->user->lang('Description'),
            $this->user->lang('Gateway'),
            $this->user->lang('Amount')
        );

        $amountOfMonths = (int) ((isset($_REQUEST['amountOfMonths']))? $_REQUEST['amountOfMonths'] : 1);

        $currentMonthAndYear = '';
        $currentMonthAndYearTotal = '';

        //Used to ignore duplicated transactions about refunds
        $refundsReferences = array();

        // get accepted transactions
        $result = Capsule::table('invoicetransaction as it')
            ->join('invoice as i', 'i.id', '=', 'it.invoiceid')
            ->select(
                'it.transactiondate',
                'i.customerid',
                'it.invoiceid',
                'it.action',
                'it.transactionid',
                'it.response',
                'i.pluginused',
                'it.amount'
            )
            ->where('it.accepted', 1)
            ->where(function ($query) {
                $query->where('it.action', '!=', 'none')
                    ->orWhere('it.response', 'like', '%paid%');
            })
            ->whereRaw("DATE_FORMAT(it.transactiondate, '%Y%m') <= DATE_FORMAT(CURDATE(), '%Y%m')")
            ->whereRaw("DATE_FORMAT(it.transactiondate, '%Y%m') >= DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL - ? MONTH), '%Y%m')", [$amountOfMonths - 1])
            ->where('i.currency', $currencyCode)
            ->orderBy('it.transactiondate', 'DESC')
            ->orderBy('it.id', 'DESC')
            ->get();

        foreach ($result as $row) {
            $transdate = strtotime($row->transactiondate);

            if ($row->action === 'refund' && isset($refundsReferences[$row->customerid][$row->invoiceid][$row->transactionid][$row->pluginused][$row->amount])) {
                $previousDate = $refundsReferences[$row->customerid][$row->invoiceid][$row->transactionid][$row->pluginused][$row->amount]['transdate'];

                //If the refund transactiondate is on the same hour, ignore it, but update the description
                if (abs($previousDate - $transdate) <= (60*60)) {
                    $refundsReferences[$row->customerid][$row->invoiceid][$row->transactionid][$row->pluginused][$row->amount]['response'] = $row->response;
                    continue;
                }
            }

            if ($row->customerid != 0) {
                $user = new User($row->customerid);

                if ($user->IsOrganization()) {
                    $dCustomerName = $user->getOrganization();
                } else {
                    $dCustomerName = $user->getFirstName() . " " . $user->getLastName();
                }

                // build a link to the customer profile
                $CustomerLink = "<a href='index.php?fuse=clients&controller=userprofile&frmClientID=".$row->customerid."&view=profileinvoices' target=blank>".CE_Lib::viewEscape($dCustomerName)."</a>";

                // build the Print to PDF Link
                $printInvoiceLink = "<a href='index.php?fuse=billing&frmClientID=".$row->customerid."&controller=invoice&view=invoice&profile=1&invoiceid=".$row->invoiceid."' target=blank>#".$row->invoiceid."</a>";
            } else {
                $dCustomerName = $this->user->lang('DELETED');
                $CustomerLink = CE_Lib::viewEscape($dCustomerName);
                $printInvoiceLink = $row->invoiceid;
            }

            $monthAndYear = date("Y M", mktime(0, 0, 0, date("m", $transdate), 1, date("Y", $transdate)));

            if ($currentMonthAndYear !== $monthAndYear) {
                if ($currentMonthAndYearTotal !== '') {
                    if ($currentMonthAndYearTotal >= 0) {
                        $fontOpen = "<font color='Green'>";
                    } else {
                        $fontOpen = "<font color='Red'>";
                    }
                    $currentMonthAndYearTotal = '<b>'.$fontOpen.$currency->format($currencyCode, $currentMonthAndYearTotal, true, 'NONE', true)."</font>".'</b>';
                }

                if ($currentMonthAndYear !== '') {
                    $data = array(
                        '&nbsp;',
                        '',
                        '',
                        '',
                        '',
                        '',
                        '',
                        '',
                    );

                    $subGroup[] = $data;
                }

                $currentMonthAndYear = $monthAndYear;

                $data = array(
                    '<b>'.$monthAndYear.'</b>',
                    '',
                    '',
                    '',
                    '',
                    '<b>'.$this->user->lang('TOTAL').'</b>',
                    '',
                    0.00,
                );

                $currentMonthAndYearTotal = &$data[7];

                $subGroup[] = $data;
            }

            $formattedAmount = $currency->format($currencyCode, $row->amount, true, 'NONE', true);

            if (in_array($row->action, array('refund', 'credit'))) {
                $amountString = "<font color='Red'>- ".$formattedAmount."</font>";
                $fixedAmount = -$row->amount;
            } else {
                $amountString = "<font color='Green'>".$formattedAmount."</font>";
                $fixedAmount = $row->amount;
            }

            $currentMonthAndYearTotal += $fixedAmount;

            $data = array(
                date($this->settings->get('Date Format'), $transdate)." ".date("h:i:s A", $transdate),
                $CustomerLink,
                $printInvoiceLink,
                $row->action,
                $row->transactionid,
                $row->response,
                $row->pluginused,
                $amountString
            );

            //Used to ignore duplicated transactions about refunds
            if ($row->action === 'refund') {
                $refundsReferences[$row->customerid][$row->invoiceid][$row->transactionid][$row->pluginused][$row->amount] = array(
                    'transdate' => $transdate,
                    'response'  => &$data[5]
                );
            }

            $subGroup[] = $data;
        }

        //Need to format the last group
        if ($currentMonthAndYearTotal !== '') {
            if ($currentMonthAndYearTotal >= 0) {
                $fontOpen = "<font color='Green'>";
            } else {
                $fontOpen = "<font color='Red'>- ";
            }

            $currentMonthAndYearTotal = abs($currentMonthAndYearTotal);
            $currentMonthAndYearTotal = '<b>'.$fontOpen.$currency->format($currencyCode, $currentMonthAndYearTotal, true, 'NONE', true)."</font>".'</b>';
        }

        if (isset($_REQUEST['download']) && $_REQUEST['download'] == 1) {
            //csv file will exxclude any line having an empty invoice id
            $exclude = array('2' => '');
            $this->download($labels, $subGroup, 'Transactions_By_Month_'.$currencyCode.'.csv', $exclude);
        }

        $this->SetDescription($this->user->lang('Displays transactions by month'));

        $MonthsToDisplay =
            '<form id="report" method="GET">'
            .'    <table width=100%>'
            .'        <tr>'
            .'            <td 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, 0);">'
            .'            </td>'
            .'            <td width=250px align=right>'
            .'                <button class="cm-btns orange" type="button" data-loading-text="Loading..." onclick="ChangeTable(document.getElementById(\'amountOfMonths\').value, document.getElementById(\'currencycode\').value, 1);">'.$this->user->lang("Download .csv").'</button>'
            .'            </td>'
            .'        </tr>'
            .'    </table>'
            .'</form>'
            .'</br>'
            .'<script type="text/javascript">'
            .'    function ChangeTable(amountOfMonths, currencycode, download){'
            .'        location.href="index.php?fuse=reports&view=viewreport&controller=index&report=Transactions+By+Month&type=Revenue&amountOfMonths="+amountOfMonths+"&currencycode="+currencycode+"&download="+download;'
            .'    }'
            .'</script>';
        echo $MonthsToDisplay;

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