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

/**
 * Server Revenue Forecast 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';

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

    protected $featureSet = 'billing';

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

    /**
     * Report Process Method
     *
     * @return null - direct output
     */
    function process()
    {
        //Billing Cycles
        $billingcycles = array();

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

        while ($cycle = $iterator->fetch()) {
            $billingcycles[$cycle->id] = array(
                'name'            => $this->user->lang($cycle->name),
                'time_unit'       => $cycle->time_unit,
                'amount_of_units' => $cycle->amount_of_units
            );
        }
        //Billing Cycles

        include_once 'modules/admin/models/StatusAliasGateway.php' ;

        // Set the report information
        $this->SetDescription($this->user->lang('Displays the total server revenue forecast.'));

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

        $getServerDetails = @$_GET['getServerDetails'];

        if ($getServerDetails) {
            $this->getServerDetails($currencyCode);

            exit;
        }

        // Load the server information
        $serverGateway = new ServerGateway();
        $serverList = $serverGateway->getServerIdsAndNames();
        $serversArray = array();

        foreach ($serverList['servers'] as $serverData) {
            $serversArray[$serverData['serverid']] = $serverData['servername'];
        }

        $billingTypeArray[-1] = 'Package';

        $result = Capsule::table('billingtype')
            ->select('id', 'name')
            ->get();

        foreach ($result as $row) {
            $billingTypeArray[$row->id] = $row->name;
        }

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

        $result = Capsule::table('recurringfee as r')
            ->select([
                'r.customerid',
                'r.amount',
                'r.quantity',
                'r.appliestoid',
                'r.billingtypeid',
                'r.taxable',
                'r.paymentterm',
                'ocf.value as server_id',
            ])
            ->join('users as u', 'r.customerid', '=', 'u.id')
            ->join('domains as d', 'r.appliestoid', '=', 'd.id')
            ->join('object_customField as ocf', 'ocf.objectid', '=', 'd.id')
            ->whereNotNull('r.nextbilldate')
            ->whereIn('u.status', $userStatuses)
            ->where('r.appliestoid', '<>', 0)
            ->whereIn('d.status', $packageStatuses)
            ->where(function ($query) {
                $query->where('r.billingtypeid', '<>', BILLINGTYPE_PACKAGE)
                    ->orWhere(function ($query) {
                        $query->where('r.billingtypeid', '=', BILLINGTYPE_PACKAGE)
                            ->whereRaw('IFNULL(r.recurring, 0) = 1');
                    });
            })
            ->where('r.paymentterm', '!=', 0)
            ->where('u.currency', $currencyCode)
            ->whereIn('ocf.customFieldId', function ($query) {
                $query->select('cf.id')
                    ->from('customField as cf')
                    ->where('cf.name', 'Server Id');
            })
            ->orderBy('ocf.value', 'ASC')
            ->orderBy('r.appliestoid', 'ASC')
            ->orderBy('r.paymentterm', 'ASC')
            ->orderBy('r.billingtypeid', 'DESC')
            ->get();

        foreach ($result as $row) {
            // Check for taxes
            $tax = 0;
            $customertax          = 0;
            $customertax2         = 0;
            $customertax2compound = 0;
            $user = new User($row->customerid);

            if ($user->IsTaxable() == 1) {
                //determine country and state and see if there is a tax in the rules to match
                $customertax  = $user->GetTaxRate();
                //-1 is returned when we don't have a taxrate
                $customertax = ($customertax == -1) ? 0 : (float) $customertax;

                $customertax2 = $user->GetTaxRate(2);
                //-1 is returned when we don't have a taxrate
                $customertax2 = ($customertax2 == -1) ? 0 : (float) $customertax2;

                if ($user->isTax2Compound()) {
                    $customertax2compound = 1;
                }
            }

            if ($row->appliestoid > 0 && $row->billingtypeid == -1) {
                $domain = new UserPackage($row->appliestoid);
                $paymentterm = $domain->getPaymentTerm();
                $taxable = $domain->isTaxable()? true : false;
                $amount = $domain->getPrice(false);
            } else {
                $paymentterm = $row->paymentterm;
                $taxable = $row->taxable;
                $amount = $row->amount;
            }

            // if $amount is not a numeric, skip this.
            if (!is_numeric($amount)) {
                continue;
            }

            if ($taxable) {
                $tax1 = round($amount * $row->quantity * $customertax / 100, 2);

                if ($customertax2compound) {
                    $tax2 = round(($amount * $row->quantity + $tax1) * $customertax2 / 100, 2);
                } else {
                    $tax2 = round($amount * $row->quantity * $customertax2 / 100, 2);
                }

                $tax = $currency->format($currencyCode, $tax1 + $tax2);
            }

            $masterGroup[] = array($serversArray[$row->server_id], $amount * $row->quantity, $tax, $paymentterm, $row->billingtypeid);
        }

        $newServerTotals  = array();

        //Initialize server revenue
        foreach ($serversArray as $serverName) {
            $newServerTotals[$serverName] = array(
                'data'   => array(),
                'amount' => 0,
                'taxes'  => 0,
                'total'  => 0
            );
        }

        //Add recurring charges
        foreach ($masterGroup as $masterGroupinfo) {
            $serverName      = $masterGroupinfo[0];
            $amount          = $masterGroupinfo[1];
            $taxes           = $masterGroupinfo[2];
            $paymentterm     = $masterGroupinfo[3];
            $billingtype     = $billingTypeArray[$masterGroupinfo[4]];

            if ($paymentterm == 0) {
                break;
            }

            if (isset($newServerTotals[$serverName])) {
                $newServerTotals[$serverName]['amount'] += $amount;
                $newServerTotals[$serverName]['taxes']  += $taxes;
                $newServerTotals[$serverName]['total']  += $amount + $taxes;
                $type = $billingcycles[$paymentterm]['name'].' - '.$billingtype;

                if (isset($newServerTotals[$serverName]['data'][$type])) {
                    $newServerTotals[$serverName]['data'][$type][2] += 1;
                    $newServerTotals[$serverName]['data'][$type][3] += $amount;
                    $newServerTotals[$serverName]['data'][$type][4] += $taxes;
                    $newServerTotals[$serverName]['data'][$type][5] += $amount + $taxes;
                } else {
                    $newServerTotals[$serverName]['data'][$type] = array(
                        $billingcycles[$paymentterm]['name'],
                        $billingtype,
                        1,
                        $amount,
                        $taxes,
                        $amount + $taxes
                    );
                }
            }
        }

        $this->SetDescription($this->user->lang('Displays server revenue forecast'));

        $form =
             '<form id="report" method="GET">'
            .'    <div style="text-align:center">'
            .$filter
            .'        <input type=button name=search class="cm-btns orange" value=\''.$this->user->lang('Display').'\' onclick="ChangeTable(document.getElementById(\'currencycode\').value);">'
            .'    </div>'
            .'</form>'
            .'</br>'
            .'<script type="text/javascript">'
            .'    function ChangeTable(currencycode){'
            .'        location.href="index.php?fuse=reports&view=viewreport&controller=index&report=Server+Revenue+Forecast&type=Revenue&currencycode="+currencycode;'
            .'    }'
            .'    function LoadDetailsForServer(server,data,currencycode){'
            .'        var url;';
        echo $form;

            $url = 'admin/index.php?noheaderfooter=1&getServerDetails=1&server="+server+"&data="+data+"&currencycode="+currencycode+"&view='.CE_Lib::viewEscape($_REQUEST['view']).'&fuse='.CE_Lib::viewEscape($_REQUEST['fuse']).'&report='.urlencode($_REQUEST['report']).'&type='.CE_Lib::viewEscape($_REQUEST['type']);
            $url = mb_substr(CE_Lib::getSoftwareURL(), -1, 1) == "//" ? CE_Lib::getSoftwareURL().$url : CE_Lib::getSoftwareURL()."/".$url;

        $form =
            '        url ="'.$url.'";'
            .'        window.open(url, "", "top=100, left=100, width=800, height=600, scrollbars=yes");'
            .'    }'
            .'</script>';
        echo $form;

        $subGroup = array();

        foreach ($newServerTotals as $serverName => $serverData) {
            $encodedData = '';

            foreach ($serverData['data'] as $data) {
                if ($encodedData != '') {
                    $encodedData .= ';';
                }

                $encodedData .= implode('_', $data);
            }

            $subGroup[] = array(
                ($encodedData == '')? $serverName : "<a href=\"javascript:LoadDetailsForServer('".$serverName."','".base64_encode($encodedData)."','".$currencyCode."');\">".$serverName."</a>",
                $currency->format($currencyCode, $serverData['amount'], true, false),
                $currency->format($currencyCode, $serverData['taxes'], true, false),
                $currency->format($currencyCode, $serverData['total'], true, false)
            );
        }

        $this->reportData[] = array(
            "group"     => $subGroup,
            "groupname" => "",
            "label"     => array(
                $this->user->lang('Server'),
                $this->user->lang('Projected Subtotal'),
                $this->user->lang('Projected Tax'),
                $this->user->lang('Projected Total Revenue')
            )
        );
    }

    function getServerDetails($currencyCode)
    {
        if (isset($_GET['data'])) {
            $data = base64_decode($_GET['data']);
            $decodedData = explode(';', $data);
            $dataArray = array();

            foreach ($decodedData as $data) {
                $dataArray[] = explode('_', $data);
            }
        }

        include_once 'modules/billing/models/Currency.php';
        $currency = new Currency($this->user);

        echo "<H1>" . CE_Lib::viewEscape($_GET["server"]) . "</h1></br>";

        echo "<style>
            table {
                width:100%;
            }
            table, th, td {
                border: 1px solid black;
                border-collapse: collapse;
            }
            th, td {
                padding: 5px;
                text-align: left;
            }
            table#t01 tr:nth-child(even) {
                background-color: #eee;
            }
            table#t01 tr:nth-child(odd) {
               background-color:#fff;
            }
            table#t01 th	{
                background-color: white;
                color: black;
            }
            </style>";

        echo "<table id='t01'>";
        echo "<tr><th>". $this->user->lang('Billing Cycle') . "</th><th>". $this->user->lang('Type') . "</th><th>" . $this->user->lang('Items') . "</th><th>" . $this->user->lang('Subtotal') . "</th><th>" . $this->user->lang('Tax') . "</th><th>" . $this->user->lang('Total') . "</th></tr>";


        foreach ($dataArray as $data) {
            echo "<tr><td>" . $data[0] . "</td><td>" . $data[1] . "</td><td>" . $data[2] . "</td><td>" . $currency->format($currencyCode, $data[3], true, "NONE", true) . "</td><td>" . $currency->format($currencyCode, $data[4], true, "NONE", true). "</td><td>" . $currency->format($currencyCode, $data[5], true, "NONE", true). "</td></tr>";
        }

        echo "</table>";
    }
}
