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

/**
 * Revenue by Billing Cycle 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_Billing_Cycle 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_Billing_Cycle extends Report
{
    private $lang;

    protected $featureSet = 'billing';

    function __construct($user = null, $customer = null)
    {
        $this->lang = lang('Revenue by Billing Cycle');
        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 total recurring transactions broken down by billing cycles with the sum and expected yearly revenue from each.'));

        // 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 = '<form id="report" method="GET">'
            .'    <div style="text-align:center">'
            .'        '.$this->user->lang('Currency').': '
            .'        <select class="normalSelect2 w-20" name="currencycode" id="currencycode" value="'.CE_Lib::viewEscape($currencyCode).'" onChange="ChangeTable(this.value);"> ';

        $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>';
        $filter .= '    </div>'
            .'</form>'
            .'</br>'
            .'<script type="text/javascript">'
            .'    function ChangeTable(currencycode){'
            .'        location.href="index.php?fuse=reports&view=viewreport&controller=index&report=Revenue+By+Billing+Cycle&type=Revenue&currencycode="+currencycode;'
            .'    }'
            .'</script>';
        echo $filter;

        // Array to store all the totals
        //Billing Cycles
        $revenueByBillingCycle = array();

        include_once 'modules/billing/models/BillingCycleGateway.php';
        $gateway = new BillingCycleGateway();
        $iterator = $gateway->getBillingCycles(array(), array('order_value', 'ASC'));

        while ($cycle = $iterator->fetch()) {
            $revenueByBillingCycle[$cycle->id] = false;
        }
        //Billing Cycles

        $userStatuses = StatusAliasGateway::userActiveAliases($this->user);
        $packageStatuses = StatusAliasGateway::getInstance($this->user)->getPackageStatusIdsFor(array(PACKAGE_STATUS_PENDING, PACKAGE_STATUS_ACTIVE));
        $billingtypePackage = BILLINGTYPE_PACKAGE;

        // Get the totals for recurring charges that are not package prices
        $result = Capsule::table('recurringfee as r')
            ->selectRaw(
                'COUNT(r.id) as count, '
                .'SUM(r.amount * r.quantity) as total, '
                .'r.paymentterm '
            )
            ->leftJoin('domains as d', 'r.appliestoid', '=', 'd.id')
            ->join('users as u', 'r.customerid', '=', 'u.id')
            ->whereIn('u.status', $userStatuses)
            ->where(function ($query) use ($packageStatuses, $billingtypePackage) {
                $query->where('r.appliestoid', '=', 0)
                    ->orWhere(function ($sub) use ($packageStatuses, $billingtypePackage) {
                        $sub->where('r.appliestoid', '!=', 0)
                            ->whereIn('d.status', $packageStatuses)
                            ->where('r.billingtypeid', '!=', $billingtypePackage);
                    });
            })
            ->where('r.recurring', 1)
            ->where('r.paymentterm', '!=', 0)
            ->where('u.currency', $currencyCode)
            ->groupBy('r.paymentterm')
            ->orderBy('r.paymentterm')
            ->get();

        // Fill array with the totals for recurring charges that are not package prices
        foreach ($result as $row) {
            if (isset($revenueByBillingCycle[$row->paymentterm]["NumberOfRecurringItems"])) {
                $revenueByBillingCycle[$row->paymentterm]["NumberOfRecurringItems"] += $row->count;
            } else {
                $revenueByBillingCycle[$row->paymentterm]["NumberOfRecurringItems"] = $row->count;
            }

            if (isset($revenueByBillingCycle[$row->paymentterm]["SumPerBillingCycle"])) {
                $revenueByBillingCycle[$row->paymentterm]["SumPerBillingCycle"] += $row->total;
            } else {
                $revenueByBillingCycle[$row->paymentterm]["SumPerBillingCycle"] = $row->total;
            }
        }

        // Just in case we have domains
        $dng = new DomainNameGateway($this->user);

        include_once 'modules/billing/models/Prices.php';
        $prices = new Prices();

        // Get the totals for recurring charges that are package prices
        $result = Capsule::table('recurringfee as r')
            ->select([
                'r.paymentterm',
                'd.use_custom_price',
                'd.custom_price',
                'ocf.value as domain_name',
                'p.id',
                'p.pricing',
                'g.type'
            ])
            ->join('domains as d', 'r.appliestoid', '=', 'd.id')
            ->join('users as u', 'r.customerid', '=', 'u.id')
            ->leftJoin('object_customField as ocf', function ($join) {
                $join->on('ocf.objectid', '=', 'd.id')
                    ->where('ocf.customFieldId', '=', function ($query) {
                        $query->select('cf.id')
                            ->from('customField as cf')
                            ->where('cf.groupId', '=', 2)
                            ->where('cf.subGroupId', '=', 3)
                            ->where('cf.name', '=', 'Domain Name')
                            ->limit(1);
                    });
            })
            ->join('package as p', 'p.id', '=', 'd.Plan')
            ->join('promotion as g', 'p.planid', '=', 'g.id')
            ->whereIn('u.status', $userStatuses)
            ->where('r.appliestoid', '!=', 0)
            ->whereIn('d.status', $packageStatuses)
            ->where('r.billingtypeid', '=', $billingtypePackage)
            ->where('r.recurring', '=', 1)
            ->where('r.paymentterm', '!=', 0)
            ->where('u.currency', '=', $currencyCode)
            ->orderBy('r.paymentterm')
            ->get();

        // Fill array with the totals for recurring charges that are package prices
        foreach ($result as $row) {
            $tPricing = $prices->getPricing(PRODUCT_PRICE, $row->id, $currencyCode, $row->pricing);

            if ($row->use_custom_price) {
                 $packagePrice = $row->custom_price;
            } else {
                $pricing = @unserialize($tPricing);

                if (is_array($pricing)) {
                    if ($row->type == 3) {  // Domain Type
                        $aDomainName = $dng->splitDomain($row->domain_name);
                        $tld = $aDomainName[1];
                        $packagePrice = 0;
                        $keys = array_keys($pricing['pricedata']);

                        if (isset($pricing['pricedata'][$keys[0]][$row->paymentterm]['price'])) {
                            $packagePrice = $pricing['pricedata'][$keys[0]][$row->paymentterm]['price'];
                        }
                    } else {
                        $packagePrice = 0;

                        if (isset($pricing['price'.$row->paymentterm])) {
                            $packagePrice = $pricing['price'.$row->paymentterm];
                        }
                    }
                }
            }

            if (isset($revenueByBillingCycle[$row->paymentterm]["NumberOfRecurringItems"])) {
                $revenueByBillingCycle[$row->paymentterm]["NumberOfRecurringItems"] += 1;
            } else {
                $revenueByBillingCycle[$row->paymentterm]["NumberOfRecurringItems"] = 1;
            }

            if (isset($revenueByBillingCycle[$row->paymentterm]["SumPerBillingCycle"])) {
                $revenueByBillingCycle[$row->paymentterm]["SumPerBillingCycle"] += $packagePrice;
            } else {
                $revenueByBillingCycle[$row->paymentterm]["SumPerBillingCycle"] = $packagePrice;
            }
        }

        //initialize
        $oldBillingCycle = -1;
        $expectedRevenueTotal = 0;
        $itemTotal = 0;

        foreach ($revenueByBillingCycle as $tBillingCycle => $revenueByBillingCycleData) {
            if ($revenueByBillingCycleData !== false) {
                $tNumberOfRecurringItems = $revenueByBillingCycleData["NumberOfRecurringItems"];
                $tSumPerBillingCycle = $revenueByBillingCycleData["SumPerBillingCycle"];

                if ($oldBillingCycle != $tBillingCycle) {
                    if (isset($aGroup)) {
                        //add previous group before getting next group
                        $this->reportData[] = array(
                            "group"     => $aGroup,
                            "groupname" => $this->GetExtendedName($oldBillingCycle).' ('.$currencyCode.')',
                            "label"     => array($this->user->lang('Items'), $this->user->lang('Sum'), $this->user->lang('Expected Yearly Revenue')),
                            'colStyle'  => 'width:200px',
                            "groupId"   => "",
                            "isHidden"  => false
                        );

                        unset($aGroup);
                    }

                    $aGroup = array();
                    $oldBillingCycle = $tBillingCycle;
                }

                //truncate
                $tExpectedRevenue = $currency->format($currencyCode, $this->GetExpectedYearlyRevenue($tBillingCycle, $tSumPerBillingCycle), true);
                $expectedRevenueTotal += $this->GetExpectedYearlyRevenue($tBillingCycle, $tSumPerBillingCycle);
                $tSumPerBillingCycle = $currency->format($currencyCode, $tSumPerBillingCycle, true);
                $aGroup[] = array($tNumberOfRecurringItems,$tSumPerBillingCycle,$tExpectedRevenue);
                $itemTotal += $tNumberOfRecurringItems;
            }
        }

        //add final group
        if (isset($aGroup)) {
            $this->reportData[] = array(
                "group"     => $aGroup,
                "groupname" => $this->GetExtendedName($oldBillingCycle).' ('.$currencyCode.')',
                "label"     => array($this->user->lang('Items'), $this->user->lang('Sum'), $this->user->lang('Expected Yearly Revenue')),
                'colStyle'  => 'width:200px',
                "groupId"   => "",
                "isHidden"  => false
            );

            unset($aGroup);
        }

        $expectedRevenueTotal = $currency->format($currencyCode, $expectedRevenueTotal, true);
        $aGroup[] = array(
            $itemTotal,
            '',
            $expectedRevenueTotal
        );

        $this->reportData[] = array(
            "group"     => $aGroup,
            "groupname" => $this->user->lang('Totals').' ('.$currencyCode.')',
            "label"     => array($this->user->lang('Items'), '', $this->user->lang('Expected Yearly Revenue')),
            'colStyle'  => 'width:200px',
            "groupId"   => "",
            "isHidden"  => false
        );
    }

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

    /**
     * function get the expected yearly revenue per billing cycle
     *
     * @return var - estimated revenue
     */
    function GetExpectedYearlyRevenue($billingCycle, $tempSum)
    {
        $billingCycle = new BillingCycle($billingCycle);

        if ($billingCycle > 0 && $billingCycle->amount_of_units > 0) {
            switch ($billingCycle->time_unit) {
                case 'd':
                    return ($tempSum/$billingCycle->amount_of_units)*365;
                    break;
                case 'w':
                    return (($tempSum/$billingCycle->amount_of_units)/7)*365;
                    break;
                case 'm':
                    return ($tempSum/$billingCycle->amount_of_units)*12;
                    break;
                case 'y':
                    return $tempSum/$billingCycle->amount_of_units;
                    break;
            }
        } else {
            return 0;
        }
    }

    /**
     * function to translate the digit billing cycle to the preferred header ( e.g. 1 returns Monthly )
     *
     * @return var - header item
     */
    function GetExtendedName($billingCycle)
    {
        $billingCycle = new BillingCycle($billingCycle);
        return $this->user->lang('Revenue Every').' '.$this->user->lang($billingCycle->name);
    }
}
