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

/**
 * Overview 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/billing/models/Currency.php';
require_once 'modules/billing/models/BillingType.php';
require_once('modules/clients/models/DomainNameGateway.php');
require_once 'modules/billing/models/BillingCycle.php';

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

    protected $featureSet = 'accounts';

    function __construct($user = null, $customer = null)
    {
        $this->lang = lang('Overview');
        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 an Overview between 2 dates.'));

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

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

        $periods = array(
            'today'       => array(
                'name'       => $this->user->lang('Today'),
                'startdate'  => date("Y-m-d"),
                'enddate'    => date("Y-m-d"),
                'previously' => array(
                    'startdate' => date("Y-m-d", mktime(0, 0, 0, date('n'), date('j') - 1, date('Y'))),
                    'enddate'   => date("Y-m-d", mktime(0, 0, 0, date('n'), date('j') - 1, date('Y')))
                )
            ),
            'yesterday'       => array(
                'name'       => $this->user->lang('Yesterday'),
                'startdate'  => date("Y-m-d", mktime(0, 0, 0, date('n'), date('j') - 1, date('Y'))),
                'enddate'    => date("Y-m-d", mktime(0, 0, 0, date('n'), date('j') - 1, date('Y'))),
                'previously' => array(
                    'startdate' => date("Y-m-d", mktime(0, 0, 0, date('n'), date('j') - 2, date('Y'))),
                    'enddate'   => date("Y-m-d", mktime(0, 0, 0, date('n'), date('j') - 2, date('Y')))
                )
            ),
            'thisMonth'   => array(
                'name'      => $this->user->lang('This Month'),
                'startdate' => date("Y-m-d", mktime(0, 0, 0, date('n'), 1, date('Y'))),
                'enddate'   => date("Y-m-d"),
                'previously' => array(
                    'startdate' => date("Y-m-d", mktime(0, 0, 0, date('n') - 1, 1, date('Y'))),
                    'enddate'   => date("Y-m-d", mktime(0, 0, 0, date('n'), 0, date('Y')))
                )
            ),
            'lastMonth'   => array(
                'name'      => $this->user->lang('Last Month'),
                'startdate' => date("Y-m-d", mktime(0, 0, 0, date('n') - 1, 1, date('Y'))),
                'enddate'   => date("Y-m-d", mktime(0, 0, 0, date('n'), 0, date('Y'))),
                'previously' => array(
                    'startdate' => date("Y-m-d", mktime(0, 0, 0, date('n') - 2, 1, date('Y'))),
                    'enddate'   => date("Y-m-d", mktime(0, 0, 0, date('n') - 1, 0, date('Y')))
                )
            ),
            'thisQuarter' => array(
                'name'      => $this->user->lang('This Quarter'),
                'startdate' => date("Y-m-d", mktime(0, 0, 0, 3 * intval((date('n') - 1) / 3) + 1, 1, date('Y'))),
                'enddate'   => date("Y-m-d"),
                'previously' => array(
                    'startdate' => date("Y-m-d", mktime(0, 0, 0, (date('n') < 4)? 10 : 3 * intval((date('n') - 4) / 3) + 1, 1, (date('n') < 4)? date('Y') - 1 : date('Y'))),
                    'enddate'   => date("Y-m-d", mktime(0, 0, 0, 3 * intval((date('n') - 1) / 3) + 1, 0, date('Y')))
                )
            ),
            'thisYear'    => array(
                'name'      => $this->user->lang('This Year'),
                'startdate' => date("Y-m-d", mktime(0, 0, 0, 1, 1, date('Y'))),
                'enddate'   => date("Y-m-d"),
                'previously' => array(
                    'startdate' => date("Y-m-d", mktime(0, 0, 0, 1, 1, date('Y') - 1)),
                    'enddate'   => date("Y-m-d", mktime(0, 0, 0, 12, 31, date('Y') - 1))
                )
            ),
            'lastYear'    => array(
                'name'      => $this->user->lang('Last Year'),
                'startdate' => date("Y-m-d", mktime(0, 0, 0, 1, 1, date('Y') - 1)),
                'enddate'   => date("Y-m-d", mktime(0, 0, 0, 12, 31, date('Y') - 1)),
                'previously' => array(
                    'startdate' => date("Y-m-d", mktime(0, 0, 0, 1, 1, date('Y') - 2)),
                    'enddate'   => date("Y-m-d", mktime(0, 0, 0, 12, 31, date('Y') - 2))
                )
            )
        );

        $filter = '<form id="report" method="GET">'
            .'    <div style="text-align:center">'
            .'        '.$this->user->lang('Period').': '
            .'        <select name="period" class="normalSelect2 w-15" id="period" value="'.CE_Lib::viewEscape($period).'" > ';

        foreach ($periods as $periodKey => $periodData) {
            $filter .= '<option value="'.$periodKey.'" '.(($period == $periodKey)? 'selected' : '').'>'.$periodData['name'].'</option>';
        }

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

        $isSelectedCurrencyInTheList = false;

        //Get all currencies of all users
        $result = Capsule::table('users as u')
            ->join('currency as c', 'c.abrv', '=', 'u.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>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'
            .'        <input type=button name=search class="cm-btns orange" value=\''.$this->user->lang('Search').'\' onclick="ChangeTable(document.getElementById(\'period\').value, document.getElementById(\'currencycode\').value);">';


        $filter .= '    </div>'
            .'</form>'
            .'</br>'
            .'<script type="text/javascript">'
            .'    function ChangeTable(period, currencycode){'
            .'        location.href="index.php?fuse=reports&view=viewreport&controller=index&report=Overview&type=Accounts&period="+period+"&currencycode="+currencycode;'
            .'    }'
            .'</script>';
        echo $filter;

        $transactionsSubquery = Capsule::table('invoicetransaction as it')
            ->select(
                'it.transactiondate',
                'it.invoiceid',
                'it.amount',
                'it.action'
            )
            ->distinct()
            ->where('it.accepted', 1)
            ->whereNotNull('it.transactiondate')
            ->where(function ($query) {
                $query->whereIn('it.action', ['charge', 'refund'])
                    ->orWhere(function ($subQuery) {
                        $subQuery->where('it.action', 'none')
                            ->where('it.response', 'like', '%of a variable payment%');
                    });
            });

        $firstPaidSubquery = Capsule::table('invoiceentry as ie')
            ->join('invoice as i', 'ie.invoiceid', '=', 'i.id')
            ->selectRaw(
                'MIN(i.datepaid) as min_datepaid, '
                .'ie.appliestoid'
            )
            ->whereIn('ie.billingtypeid', [
                BILLINGTYPE_PACKAGE,
                BILLINGTYPE_PACKAGE_UPGRADE
            ])
            ->where('i.currency', $currencyCode)
            ->where('i.status', INVOICE_STATUS_PAID)
            ->groupBy('ie.appliestoid');

        $allPaidSubquery = Capsule::table('invoiceentry as ie')
            ->join('invoice as i', 'ie.invoiceid', '=', 'i.id')
            ->select(
                'i.datepaid',
                'ie.appliestoid'
            )
            ->whereIn('ie.billingtypeid', [
                BILLINGTYPE_PACKAGE,
                BILLINGTYPE_PACKAGE_UPGRADE
            ])
            ->where('i.currency', $currencyCode)
            ->where('i.status', INVOICE_STATUS_PAID);

        $cancellationRequestDateIdSubquery = Capsule::table('customField')
            ->select('id')
            ->where('name', 'Cancellation Request Date');

        $overviewData = array();
 
        //invoice transactions payments - invoice transactions refund
        $overviewData[$this->user->lang('Revenue')]['value'] = 0;
        $overviewData[$this->user->lang('Revenue')]['previously'] = 0;
        $overviewData[$this->user->lang('Revenue')]['currency'] = 1;
        $this->revenue($overviewData[$this->user->lang('Revenue')]['value'], $currencyCode, $periods[$period], $transactionsSubquery);
        $this->revenue($overviewData[$this->user->lang('Revenue')]['previously'], $currencyCode, $periods[$period]['previously'], $transactionsSubquery);

        //invoice transactions refund
        $overviewData[$this->user->lang('Refunds')]['value'] = 0;
        $overviewData[$this->user->lang('Refunds')]['previously'] = 0;
        $overviewData[$this->user->lang('Refunds')]['currency'] = 1;
        $this->refunds($overviewData[$this->user->lang('Refunds')]['value'], $currencyCode, $periods[$period], $transactionsSubquery);
        $this->refunds($overviewData[$this->user->lang('Refunds')]['previously'], $currencyCode, $periods[$period]['previously'], $transactionsSubquery);

        //count clients
        $overviewData[$this->user->lang('New Clients')]['value'] = 0;
        $overviewData[$this->user->lang('New Clients')]['previously'] = 0;
        $overviewData[$this->user->lang('New Clients')]['currency'] = 0;
        $this->newClients($overviewData[$this->user->lang('New Clients')]['value'], $periods[$period]);
        $this->newClients($overviewData[$this->user->lang('New Clients')]['previously'], $periods[$period]['previously']);

        //count first invoices for packages
        $overviewData[$this->user->lang('New Orders')]['value'] = 0;
        $overviewData[$this->user->lang('New Orders')]['previously'] = 0;
        $overviewData[$this->user->lang('New Orders')]['currency'] = 1;
        $this->newOrders($overviewData[$this->user->lang('New Orders')]['value'], $periods[$period], $firstPaidSubquery);
        $this->newOrders($overviewData[$this->user->lang('New Orders')]['previously'], $periods[$period]['previously'], $firstPaidSubquery);

        //count invoices for packages excluding the first one
        $overviewData[$this->user->lang('Renewals')]['value'] = 0;
        $overviewData[$this->user->lang('Renewals')]['previously'] = 0;
        $overviewData[$this->user->lang('Renewals')]['currency'] = 1;
        $this->renewals($overviewData[$this->user->lang('Renewals')]['value'], $periods[$period], $firstPaidSubquery, $allPaidSubquery);
        $this->renewals($overviewData[$this->user->lang('Renewals')]['previously'], $periods[$period]['previously'], $firstPaidSubquery, $allPaidSubquery);

        //count paid invoices
        $overviewData[$this->user->lang('Paid Invoices')]['value'] = 0;
        $overviewData[$this->user->lang('Paid Invoices')]['previously'] = 0;
        $overviewData[$this->user->lang('Paid Invoices')]['currency'] = 1;
        $this->paidInvoices($overviewData[$this->user->lang('Paid Invoices')]['value'], $currencyCode, $periods[$period]);
        $this->paidInvoices($overviewData[$this->user->lang('Paid Invoices')]['previously'], $currencyCode, $periods[$period]['previously']);

        //count open tickets
        $overviewData[$this->user->lang('Opened Tickets')]['value'] = 0;
        $overviewData[$this->user->lang('Opened Tickets')]['previously'] = 0;
        $overviewData[$this->user->lang('Opened Tickets')]['currency'] = 0;
        $this->openedTickets($overviewData[$this->user->lang('Opened Tickets')]['value'], $periods[$period]);
        $this->openedTickets($overviewData[$this->user->lang('Opened Tickets')]['previously'], $periods[$period]['previously']);

        //count closed tickets
        $overviewData[$this->user->lang('Closed Tickets')]['value'] = 0;
        $overviewData[$this->user->lang('Closed Tickets')]['previously'] = 0;
        $overviewData[$this->user->lang('Closed Tickets')]['currency'] = 0;
        $this->closedTickets($overviewData[$this->user->lang('Closed Tickets')]['value'], $periods[$period]);
        $this->closedTickets($overviewData[$this->user->lang('Closed Tickets')]['previously'], $periods[$period]['previously']);

        //count submitted cancellation requests
        $overviewData[$this->user->lang('Cancellation Requests')]['value'] = 0;
        $overviewData[$this->user->lang('Cancellation Requests')]['previously'] = 0;
        $overviewData[$this->user->lang('Cancellation Requests')]['currency'] = 0;
        $this->cancellationRequests($overviewData[$this->user->lang('Cancellation Requests')]['value'], $periods[$period], $cancellationRequestDateIdSubquery);
        $this->cancellationRequests($overviewData[$this->user->lang('Cancellation Requests')]['previously'], $periods[$period]['previously'], $cancellationRequestDateIdSubquery);

        //count Upgrades / Downgrades
        $overviewData[$this->user->lang('Upgrades / Downgrades')]['value'] = 0;
        $overviewData[$this->user->lang('Upgrades / Downgrades')]['previously'] = 0;
        $overviewData[$this->user->lang('Upgrades / Downgrades')]['currency'] = 0;
        $this->upgradesDowngrades($overviewData[$this->user->lang('Upgrades / Downgrades')]['value'], $periods[$period]);
        $this->upgradesDowngrades($overviewData[$this->user->lang('Upgrades / Downgrades')]['previously'], $periods[$period]['previously']);

        //count affiliate commissions
        $overviewData[$this->user->lang('Affiliate Commissions')]['value'] = 0;
        $overviewData[$this->user->lang('Affiliate Commissions')]['previously'] = 0;
        $overviewData[$this->user->lang('Affiliate Commissions')]['currency'] = 1;
        $this->affiliateCommissions($overviewData[$this->user->lang('Affiliate Commissions')]['value'], $currencyCode, $periods[$period]);
        $this->affiliateCommissions($overviewData[$this->user->lang('Affiliate Commissions')]['previously'], $currencyCode, $periods[$period]['previously']);

        $count = 0;
        $maxColumns = 4;


        // Header dates
        $data  = '<div class="text-center mb-2 fw-semibold">'
        .'<b>'.$this->user->lang("Current Period").':</b> '
        .$periods[$period]['startdate'].' 00:00:00'
        .' <b>-</b> '
        .$periods[$period]['enddate'].' 23:59:59'
        .'</div>';

        $data .= '<div class="text-center mb-4 fw-semibold">'
        .'<b>'.$this->user->lang("Previous Period").':</b> '
        .$periods[$period]['previously']['startdate'].' 00:00:00'
        .' <b>-</b> '
        .$periods[$period]['previously']['enddate'].' 23:59:59'
        .'</div>';

// Wrapper start
        $data .= '<div class="review-tile-wrapper">';

        $tileCount = 1;

        foreach ($overviewData as $overviewKey => $overviewValues) {
            // Value and previous period formatting
            $value = $overviewValues['value'];
            $prev  = $overviewValues['previously'];
            $diff  = 0;

            if (is_numeric($value) && is_numeric($prev) && $prev != 0) {
                $diff = (($value - $prev) / $prev) * 100;
            }

            $isUp = ($diff >= 0);
            $diffDisplay = number_format(abs($diff), 2) . '%';

            // Box
            $data .= '
        <div class="tile-box">
            <div class="holder">
                <i class="icon-acc-review-' . $tileCount . '"></i>
                <span>' . $overviewKey . (($overviewValues["currency"]) ? " ($currencyCode)" : "") . '</span>
                <span class="val">'.$value.'</span>
            </div>

            <p>
                <span class="'.($isUp ? 'up' : 'down').'">
                    '.$diffDisplay.'
                    <i class="'.($isUp ? 'icon-up-arrow' : 'icon-down-arrow').'"></i>
                </span>
                '.$this->user->lang("vs previous period").'
            </p>
        </div>
    ';

            $tileCount++;
        }

        $data .= '</div>'; // end wrapper

        echo $data;



        // $data = '<div align ="center"><b>'.$this->user->lang("Current Period").':</b>&nbsp;&nbsp;&nbsp;'.$periods[$period]['startdate'].' 00:00:00'.'&nbsp;&nbsp;&nbsp;<b>-</b>&nbsp;&nbsp;&nbsp;'.$periods[$period]['enddate'].' 23:59:59'.'</div>'
        //     .'<div align ="center"><b>'.$this->user->lang("Previous Period").':</b>&nbsp;&nbsp;&nbsp;'.$periods[$period]['previously']['startdate'].' 00:00:00'.'&nbsp;&nbsp;&nbsp;<b>-</b>&nbsp;&nbsp;&nbsp;'.$periods[$period]['previously']['enddate'].' 23:59:59'.'</div><br>'
        //     .'<table align ="center">';

        // foreach ($overviewData as $overviewKey => $overviewValues) {
        //     if ($count % $maxColumns === 0) {
        //         if ($count !== 0) {
        //             $data .= '</tr>'
        //                 .'<tr>';

        //             for ($count2 = 0; $count2 < ($maxColumns * 2 - 1); $count2++) {
        //                 $data .= '<td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>';
        //             }

        //             $data .= '</tr>';
        //         }

        //         $data .= '<tr>';
        //     } else {
        //         $data .= '<td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>';
        //     }

        //     $data .= '<td width="200px;" style="border: 1px solid black; padding: 15px;" align ="center">'
        //         .'<b>'.$overviewKey.(($overviewValues['currency'])? ' ('.$currencyCode.')' : '').'</b>'
        //         .'<br><br>'
        //         .'<span style="font-size: 25px;">'.$overviewValues['value'].'</span>'
        //         .'<br><br>'
        //         .'<i>'.$this->user->lang("Previous Period").': '.$overviewValues['previously'].'</i>'
        //         .'</td>';

        //     $count ++;
        // }

        // if ($count % $maxColumns !== 0) {
        //     for ($count3 = ($count % $maxColumns) *2 - 1; $count3 < ($maxColumns * 2 - 1); $count3++) {
        //         $data .= '<td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>';
        //     }
        // }

        // if ($count > 0) {
        //     $data .= '</tr>';
        // }

        // $data .= '</table>';

        // echo $data;
    }

    function revenue(&$overviewData, $currencyCode, $periods, $transactionsSubquery)
    {
        $result = Capsule::table(Capsule::raw("({$transactionsSubquery->toSql()}) as it2"))
            ->mergeBindings($transactionsSubquery) // Keep bindings from subquery
            ->join('invoice as i', 'i.id', '=', 'it2.invoiceid')
            ->selectRaw(
                'SUM(ABS(it2.amount)) AS amount, '
                .'it2.action'
            )
            ->where('i.currency', $currencyCode)
            ->where('it2.transactiondate', '>=', $periods['startdate'] . ' 00:00:00')
            ->where('it2.transactiondate', '<=', $periods['enddate'] . ' 23:59:59')
            ->groupBy('it2.action')
            ->orderBy('it2.action')
            ->get();

        foreach ($result as $row) {
            if ($row->action == 'refund') {
                $tAmount = - $row->amount;
            } else {
                $tAmount = $row->amount;
            }

            $overviewData += $tAmount;
        }

        $overviewData = $this->formatAmount($currencyCode, $overviewData);
    }

    function refunds(&$overviewData, $currencyCode, $periods, $transactionsSubquery)
    {
        $result = Capsule::table(Capsule::raw("({$transactionsSubquery->toSql()}) as it2"))
            ->mergeBindings($transactionsSubquery) // Keep bindings from subquery
            ->join('invoice as i', 'i.id', '=', 'it2.invoiceid')
            ->selectRaw(
                'SUM(ABS(it2.amount)) AS amount '
            )
            ->where('i.currency', $currencyCode)
            ->where('it2.action', 'refund')
            ->where('it2.transactiondate', '>=', $periods['startdate'] . ' 00:00:00')
            ->where('it2.transactiondate', '<=', $periods['enddate'] . ' 23:59:59')
            ->groupBy('it2.action')
            ->get();

        foreach ($result as $row) {
            $tAmount = - $row->amount;
            $overviewData += $tAmount;
        }

        $overviewData = $this->formatAmount($currencyCode, $overviewData);
    }

    function newClients(&$overviewData, $periods)
    {
        $status = INVOICE_STATUS_PAID;

        $result = Capsule::table('users as u')
            ->whereIn('u.groupid', function ($subQuery) {
                $subQuery->select('g.id')
                    ->from('groups as g')
                    ->where('g.isadmin', 0);
            })
            ->whereIn('u.id', function ($subQuery) use ($status) {
                $subQuery->selectRaw('DISTINCT i.customerid')
                    ->from('invoice as i')
                    ->where('i.status', $status);
            })
            ->where('u.dateActivated', '>=', $periods['startdate'] . ' 00:00:00')
            ->where('u.dateActivated', '<=', $periods['enddate'] . ' 23:59:59')
            ->count();

        $overviewData = $result;
    }

    function newOrders(&$overviewData, $periods, $firstPaidSubquery)
    {
        $result = Capsule::table(Capsule::raw("({$firstPaidSubquery->toSql()}) as ie2"))
            ->mergeBindings($firstPaidSubquery) // important to carry over bindings
            ->where('ie2.min_datepaid', '>=', $periods['startdate'] . ' 00:00:00')
            ->where('ie2.min_datepaid', '<=', $periods['enddate'] . ' 23:59:59')
            ->count();

        $overviewData = $result;
    }

    function renewals(&$overviewData, $periods, $firstPaidSubquery, $allPaidSubquery)
    {
        $result = Capsule::table(Capsule::raw("({$firstPaidSubquery->toSql()}) as first_paid"))
            ->mergeBindings($firstPaidSubquery)
            ->joinSub($allPaidSubquery, 'all_paid', function ($join) {
                $join->on('first_paid.appliestoid', '=', 'all_paid.appliestoid');
            })
            ->whereRaw('all_paid.datepaid != first_paid.min_datepaid')
            ->where('all_paid.datepaid', '>=', $periods['startdate'] . ' 00:00:00')
            ->where('all_paid.datepaid', '<=', $periods['enddate'] . ' 23:59:59')
            ->count();

        $overviewData = $result;
    }

    function paidInvoices(&$overviewData, $currencyCode, $periods)
    {
        $result = Capsule::table('invoice')
            ->where('datepaid', '>=', $periods['startdate'] . ' 00:00:00')
            ->where('datepaid', '<=', $periods['enddate'] . ' 23:59:59')
            ->where('status', INVOICE_STATUS_PAID)
            ->where('currency', $currencyCode)
            ->count();

        $overviewData = $result;
    }

    function openedTickets(&$overviewData, $periods)
    {
        $result = Capsule::table('troubleticket')
            ->where('datesubmitted', '>=', $periods['startdate'] . ' 00:00:00')
            ->where('datesubmitted', '<=', $periods['enddate'] . ' 23:59:59')
            ->count();

        $overviewData = $result;
    }

    function closedTickets(&$overviewData, $periods)
    {
        $closedStatuses = StatusAliasGateway::ticketClosedAliases($this->user);

        $result = Capsule::table('troubleticket')
            ->where('lastlog_datetime', '>=', $periods['startdate'] . ' 00:00:00')
            ->where('lastlog_datetime', '<=', $periods['enddate'] . ' 23:59:59')
            ->whereIn('status', $closedStatuses)
            ->count();

        $overviewData = $result;
    }

    function cancellationRequests(&$overviewData, $periods, $cancellationRequestDateIdSubquery)
    {
        $result = Capsule::table('object_customField')
            ->whereIn('customFieldId', $cancellationRequestDateIdSubquery)
            ->where('value', '>=', $periods['startdate'] . ' 00:00:00')
            ->where('value', '<=', $periods['enddate'] . ' 23:59:59')
            ->count();

        $overviewData = $result;
    }

    function upgradesDowngrades(&$overviewData, $periods)
    {
        $result = Capsule::table('events_log')
            ->where('action', PACKAGE_EVENTLOG_UPGRADE_PROCESSED)
            ->where('entity_type', 'Package')
            ->where('date', '>=', $periods['startdate'] . ' 00:00:00')
            ->where('date', '<=', $periods['enddate'] . ' 23:59:59')
            ->count();

        $overviewData = $result;
    }

    function affiliateCommissions(&$overviewData, $currencyCode, $periods)
    {
        $result = Capsule::table('affiliate_commission as ac')
            ->join('affiliates as a', 'a.id', '=', 'ac.affiliate_id')
            ->join('users as u', 'u.id', '=', 'a.user_id')
            ->where('u.currency', $currencyCode)
            ->where('ac.status', COMMISSION_STATUS_PAID)
            ->where('ac.clearing_date', '>=', $periods['startdate'] . ' 00:00:00')
            ->where('ac.clearing_date', '<=', $periods['enddate'] . ' 23:59:59')
            ->sum('ac.amount');

        $overviewData = $result;
        $overviewData = $this->formatAmount($currencyCode, $overviewData);
    }

    function formatAmount($currencyCode, $amount)
    {
        $currency = new Currency($this->user);
        $amountFormatted = $currency->format($currencyCode, $amount, true);

        if ($amount < 0) {
            $amountFormatted = "<font color='red'>" . $amountFormatted . "</font>";
        }

        return $amountFormatted;
    }
}
