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

/**
 * Revenue By Client Group 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';

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

    protected $featureSet = 'billing';

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

    /**
     * Report Process Method
     *
     * @return null - direct output
     */
    function process()
    {
        // Set the report information
        $this->SetDescription($this->user->lang('Displays total paid by Client Group.'));

        $yearpaid = 'allperyear';

        if (isset($_REQUEST['yearpaid'])) {
            $yearpaid = $_REQUEST['yearpaid'];
        }

        // 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).'" onChange="ChangeTable(\''.$yearpaid.'\', this.value);"> ';

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

        switch ($yearpaid) {
            case 'allperyear':
                $AllPerYearSelected = 'selected';
                $AllSelected = '';
                break;
            case 'all':
                $AllPerYearSelected = '';
                $AllSelected = 'selected';
                break;
            default:
                $AllPerYearSelected = '';
                $AllSelected = '';
                break;
        }

        $yearOptions = '<option value="allperyear" ' . $AllPerYearSelected . '>All Per Year</option>';
        $yearOptions .= '<option value="all" ' . $AllSelected . '>All</option>';

        $result = Capsule::table('invoice')
            ->selectRaw('YEAR(datepaid) AS year')
            ->distinct()
            ->where('status', '=', 1)
            ->orderBy('year', 'DESC')
            ->get();

        foreach ($result as $row) {
            $optionSelected = '';

            if ($row->year == $yearpaid) {
                $optionSelected = 'selected';
            }

            $yearOptions .= '<option value="' . $row->year . '" ' . $optionSelected . '>' . $row->year . '</option>';
        }

        $MonthsToDisplay =
             '<form id="report" method="GET">'
            .'    <div style="text-align:center">'
            .'        Select Year: '
            .'        <select id="yearpaid" class="normalSelect2 w-25" name="yearpaid" onChange="ChangeTable(this.value, \''.$currencyCode.'\');">'
            .$yearOptions
            .'        </select>'
            .$filter
            .'    </div>'
            .'</form>'
            .'</br>'
            .'<script type="text/javascript">'
            .'    function ChangeTable(yearpaid, currencycode){'
            .'        location.href="index.php?fuse=reports&view=viewreport&controller=index&report=Revenue+By+Client+Group&type=Revenue&yearpaid="+yearpaid+"&currencycode="+currencycode;'
            .'    }'
            .'</script>';
        echo $MonthsToDisplay;

        switch ($yearpaid) {
            case 'allperyear':
                $totalRevenue = 0;
                $oldGroupName = '';

                $result = Capsule::table('invoice')
                    ->leftJoin('user_groups', 'user_groups.user_id', '=', 'invoice.customerid')
                    ->leftJoin('groups', 'groups.id', '=', 'user_groups.group_id')
                    ->selectRaw(
                        'IFNULL(user_groups.group_id, 0) AS groupId, '
                        .'IFNULL(groups.name, "--none--") AS groupName, '
                        .'YEAR(invoice.datepaid) AS yearDatePaid, '
                        .'SUM(invoice.amount) AS groupSum'
                    )
                    ->where('invoice.status', '=', 1)
                    ->where('invoice.currency', '=', $currencyCode)
                    ->whereNotNull('invoice.datepaid')
                    ->where('invoice.datepaid', '!=', '0000-00-00')
                    ->groupBy('user_groups.group_id', 'groups.name', 'yearDatePaid')
                    ->orderByRaw('user_groups.group_id ASC, yearDatePaid DESC')
                    ->get();

                foreach ($result as $row) {
                    $totalRevenue += $row->groupSum;

                    if ($oldGroupName != $row->groupName) {
                        if (isset($aGroup)) {
                            //add previous group before getting next group
                            $this->reportData[] = array(
                                "group"     => $aGroup,
                                "groupname" => $oldGroupName.' ('.$currencyCode.')',
                                "label"     => array(
                                    $this->user->lang('Year'),
                                    $this->user->lang('Sum'),
                                ),
                                "colStyle"  => 'width:200px',
                                "groupId"   => "",
                                "isHidden"  => false
                            );

                            unset($aGroup);
                        }

                        $aGroup = array();
                        $oldGroupName = $row->groupName;
                    }

                    $aGroup[] = array(
                        $row->yearDatePaid,
                        $currency->format($currencyCode, $row->groupSum, true)
                    );
                }

                //add final group
                if (isset($aGroup)) {
                    //add previous group before getting next group
                    $this->reportData[] = array(
                        "group"     => $aGroup,
                        "groupname" => $oldGroupName.' ('.$currencyCode.')',
                        "label"     => array(
                            $this->user->lang('Year'),
                            $this->user->lang('Sum'),
                        ),
                        "colStyle"  => 'width:200px',
                        "groupId"   => "",
                        "isHidden"  => false
                    );

                    unset($aGroup);
                }

                $this->reportData[] = array(
                    "group"     => array(
                        array(
                            '',
                            $currency->format($currencyCode, $totalRevenue, true)
                        )
                    ),
                    "groupname" => $this->user->lang('Totals').' ('.$currencyCode.')',
                    "label"     => array(
                        '',
                        ''
                    ),
                    "colStyle"  => 'width:200px',
                    "groupId"   => "",
                    "isHidden"  => false
                );

                break;
            default:
                if ($yearpaid === 'all') {
                    $result = Capsule::table('invoice')
                        ->leftJoin('user_groups', 'user_groups.user_id', '=', 'invoice.customerid')
                        ->leftJoin('groups', 'groups.id', '=', 'user_groups.group_id')
                        ->selectRaw('IFNULL(user_groups.group_id, 0) AS groupId, IFNULL(groups.name, "--none--") AS groupName, SUM(invoice.amount) AS groupSum')
                        ->where('invoice.status', '=', 1)
                        ->where('invoice.currency', '=', $currencyCode)
                        ->groupBy('user_groups.group_id', 'groups.name')
                        ->orderBy('user_groups.group_id', 'ASC')
                        ->get();
                } else {
                    $result = Capsule::table('invoice')
                        ->leftJoin('user_groups', 'user_groups.user_id', '=', 'invoice.customerid')
                        ->leftJoin('groups', 'groups.id', '=', 'user_groups.group_id')
                        ->selectRaw('IFNULL(user_groups.group_id, 0) AS groupId, IFNULL(groups.name, "--none--") AS groupName, SUM(invoice.amount) AS groupSum')
                        ->where('invoice.status', '=', 1)
                        ->where('invoice.currency', '=', $currencyCode)
                        ->whereYear('invoice.datepaid', $yearpaid)
                        ->groupBy('user_groups.group_id', 'groups.name')
                        ->orderBy('user_groups.group_id', 'ASC')
                        ->get();
                }

                $totalRevenue = 0;

                foreach ($result as $row) {
                    $totalRevenue += $row->groupSum;

                    $this->reportData[] = array(
                        "group"     => array(
                            array(
                                $currency->format($currencyCode, $row->groupSum, true)
                            )
                        ),
                        "groupname" => $row->groupName.' ('.$currencyCode.')',
                        "label"     => array(
                            $this->user->lang('Sum'),
                        ),
                        "colStyle"  => 'width:200px',
                        "groupId"   => "",
                        "isHidden"  => false
                    );
                }

                $this->reportData[] = array(
                    "group"     => array(
                        array(
                            $currency->format($currencyCode, $totalRevenue, true)
                        )
                    ),
                    "groupname" => $this->user->lang('Totals').' ('.$currencyCode.')',
                    "label"     => array(
                        ''
                    ),
                    "colStyle"  => 'width:200px',
                    "groupId"   => "",
                    "isHidden"  => false
                );

                break;
        }
    }

    //*********************************************
    // Custom Function Definitions for this report
    //*********************************************
}
