<?php
/**
 * Expected Monthly Income Report
 *
 * @category Report
 * @package  ClientExec
 * @author   Juan Bolivar <juan@clientexec.com>
 * @license  ClientExec License
 * @version  1.0
 * @link     http://www.clientexec.com
 */

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

/**
 * Expected_Monthly_Income Report Class
 *
 * @category Report
 * @package  ClientExec
 * @author   Juan Bolivar <juan@clientexec.com>
 * @license  ClientExec License
 * @version  1.0
 * @link     http://www.clientexec.com
 */
class Expected_Monthly_Income extends Report
{
    protected $featureSet = 'billing';

    var $m_hasgraph = true;

    /**
     * Report Process Method
     *
     * @return null - direct output
     */
    function process()
    {
        // Set the report information
        $this->setName('Expected Monthly Income', true);
        $this->setVersion("1.0");
        $this->SetDescription($this->user->lang('Displays the total expected income per month.'));

        @set_time_limit(0);

        $currency = new Currency($this->user);

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

        //DETERMINE IF THIS IS A GRAPH CALL
        if (!get_magic_quotes_gpc()) {
            $graphdata = addslashes(@$_GET['graphdata']);
        } else {
            $graphdata = @$_GET['graphdata'];
        }

        //Get recurring fees
        $reportSQL = "SELECT r.`customerid`, "
            ."r.`amount`, "
            ."r.`nextbilldate`, "
            ."r.`appliestoid`, "
            ."r.`billingtypeid`, "
            ."r.`taxable`, "
            ."r.`paymentterm` "
            ."FROM `recurringfee` r "
            ."INNER JOIN `users` u "
            ."ON r.`customerid` = u.`id` "
            ."LEFT JOIN `domains` d "
            ."ON r.`appliestoid` = d.`id` "
            ."WHERE r.`nextbilldate` <> 'NULL' "
            ."AND DATE_FORMAT(r.`nextbilldate`, '%Y%m') <= DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL ".($amountOfMonths - 1)." MONTH), '%Y%m') "
            ."AND u.`status` = 1 "
            ."AND (r.`appliestoid` = 0 "
            ."OR (r.`appliestoid` <> 0 "
            ."AND d.`status` IN (".PACKAGE_STATUS_PENDING.", ".PACKAGE_STATUS_ACTIVE.") "
            ."AND (r.`billingtypeid` <> ".BILLINGTYPE_PACKAGE." "
            ."OR (r.`billingtypeid` = ".BILLINGTYPE_PACKAGE." "
            ."AND r.`recurring` = 1)))) "
            ."AND r.`paymentterm` != 0 "
            ."ORDER BY r.`nextbilldate` ";
        $result = $this->db->query($reportSQL);

        $masterGroup = array();

        while(list($customerid, $amount, $nextbilldate, $appliestoid, $billingtype, $taxable, $paymentterm) = $result->fetch()) {
            // Check for taxes
            $tax = 0;
            $customertax          = 0;
            $customertax2         = 0;
            $customertax2compound = 0;
            $user = new User($customerid);

            if ($user->IsTaxable()==1){
                //determine country and state and see if there is a tax in the rules to match
                $customertax  = $user->GetTaxRate();
                $customertax2 = $user->GetTaxRate(2);
                if($user->isTax2Compound()){
                    $customertax2compound = 1;
                }
            }

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

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

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

                $tax = $currency->format($this->settings->get('Default Currency'), $tax1 + $tax2);
            }

            $masterGroup[] = array($nextbilldate, $amount, $tax, $paymentterm);
        }

        $newMonthTotals  = array();

        //Get amounts already invoiced
        $reportSQL2 = "SELECT `billdate`, "
            ."IFNULL(`amount`, 0), "
            ."IFNULL(`subtotal`, 0) "
            ."FROM `invoice` "
            ."WHERE DATE_FORMAT(`billdate`, '%Y%m') >= DATE_FORMAT(CURDATE(), '%Y%m') "
            ."AND DATE_FORMAT(`billdate`, '%Y%m') <= DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL ".($amountOfMonths - 1)." MONTH), '%Y%m') "
            ."AND `status` IN (".INVOICE_STATUS_UNPAID.", ".INVOICE_STATUS_PAID.", ".INVOICE_STATUS_PENDING.", ".INVOICE_STATUS_PARTIALLY_PAID.") ";
        $result2 = $this->db->query($reportSQL2);

        //Initialize months income
        for($monthsAhead = 0; $monthsAhead < $amountOfMonths; $monthsAhead++){
            $newMonthTotals[date("Y M", mktime(0, 0, 0, date("m") + $monthsAhead, date("d"), date("Y")))] = array(
                'amount' => 0,
                'taxes'  => 0,
                'total'  => 0
            );
        }

        //Add invoices already generated
        while(list($billdate, $amount, $subtotal) = $result2->fetch()) {
            $monthAndYear = date("Y M", mktime(0, 0, 0, date("m", strtotime($billdate)), date("d", strtotime($billdate)), date("Y", strtotime($billdate))));
            if(isset($newMonthTotals[$monthAndYear])){
                $newMonthTotals[$monthAndYear]['amount'] += $subtotal;
                $newMonthTotals[$monthAndYear]['taxes']  += $amount - $subtotal;
                $newMonthTotals[$monthAndYear]['total']  += $amount;
            }
        }

        $maxMonthAndYear = date("Ym", mktime(0, 0, 0, date("m", mktime()) + $amountOfMonths - 1, date("d", mktime()), date("Y", mktime())));

        //Add recurring charges
        while(list($info) = each($masterGroup)) {
            $nextbilldate    = $masterGroup[$info][0];
            $amount          = $masterGroup[$info][1];
            $taxes           = $masterGroup[$info][2];
            $paymentterm     = $masterGroup[$info][3];

            $monthsAhead = 0;
            $currentMonthAndYear = 0;
            do{
                $monthAndYear = date("Y M", mktime(0, 0, 0, date("m", strtotime($nextbilldate)) + $monthsAhead, date("d", strtotime($nextbilldate)), date("Y", strtotime($nextbilldate))));
                if(isset($newMonthTotals[$monthAndYear])){
                    $newMonthTotals[$monthAndYear]['amount'] += $amount;
                    $newMonthTotals[$monthAndYear]['taxes']  += $taxes;
                    $newMonthTotals[$monthAndYear]['total']  += $amount + $taxes;
                }
                $monthsAhead += $paymentterm;
                $currentMonthAndYear = date("Ym", mktime(0, 0, 0, date("m", strtotime($nextbilldate)) + $monthsAhead, date("d", strtotime($nextbilldate)), date("Y", strtotime($nextbilldate))));
            }while($currentMonthAndYear <= $maxMonthAndYear);
        }

        if($graphdata) {
            $data = array();
            $labels = array();
            foreach($newMonthTotals AS $monthAndYear => $monthData){
                $labels[] = $monthAndYear;
                $data[0][] = $monthData['amount'];
                $data[1][] = $monthData['taxes'];
                $data[2][] = $monthData['total'];
            }

            echo $this->GraphData($data,$labels);
            exit;
        }

        // Output the headers before anything else
        $this->showReportHeader();

        $this->SetDescription($this->user->lang('Displays expected monthly income'));

        $MonthsToDisplay =
             '<form action="index.php?fuse=reports&view=ViewReport&report=Expected+Monthly+Income&type=Income" method="POST">'
            .'    <div style="text-align:center">'
            .'        Months to display: '
            .'        <input type="text" name="amountOfMonths" size="2" value="'.$amountOfMonths.'"> '
            .'        <input type="submit" value="Search" />'
            .'    </div>'
            .'</form>'
            .'</br>';
        echo $MonthsToDisplay;

        //create a lazy loading chart so that our showgraph will call ajax
        //call to retrieve chart and populate it after load.  This should
        //increase performance.
        $url = "index.php?noheaderfooter=1&graphdata=1&view=".$_REQUEST['view']."&fuse=".$_REQUEST['fuse']."&report=".urlencode($_REQUEST['report'])."&type=".urlencode($_REQUEST['type'])."&amountOfMonths=".urlencode($amountOfMonths);
        $this->ShowGraph(0,$url);

        $subGroup = array();
        foreach($newMonthTotals AS $monthAndYear => $monthData){
            $subGroup[] = array(
                $monthAndYear,
                $currency->format($this->settings->get('Default Currency'), $monthData['amount'], true, false),
                $currency->format($this->settings->get('Default Currency'), $monthData['taxes'], true, false),
                $currency->format($this->settings->get('Default Currency'), $monthData['total'], true, false)
            );
        }

        $this->Add(
            $subGroup,
            "",
            array(
                $this->user->lang('Month and Year'),
                $this->user->lang('Expected Subtotal'),
                $this->user->lang('Expected Tax'),
                $this->user->lang('Expected Total Income')
            )
        );

        $this->Show();
    }

    function GraphData($data,$labels)
    {
        // These headers must be set or IE will show an error.
        // IE + SSL = BAD
        // http://www.fusioncharts.com/KB/article.aspx?id=10091
        header('Cache-Control: cache, must-revalidate');
        header('Pragma: public');

        $strXML = "";

        $title = "";
        $subcaption = "";

        if($this->viewingFromDashboard()) {
            //do not want to see title in dashboard since we already show from dropdown
            //universal link to the report that can be used in any report
            $url = "'index.php?fuse=".$_REQUEST['fuse']."&report=".urlencode($_REQUEST['report'])."&type=".urlencode($_REQUEST['type'])."&view=".$_REQUEST['view']."'";
            $clickurl="clickURL=".$url;
        }else {
            $clickurl="";
        }

        $currencySymbol = '$';
        if(strtoupper($this->settings->get('Default Currency')) == 'EUR'){
            $currencySymbol = '€';
        }elseif (strtoupper($this->settings->get('Default Currency')) == 'GBP') {
            $currencySymbol = '£';
        }elseif (strtoupper($this->settings->get('Default Currency')) == 'JPY') {
            $currencySymbol = '¥';
        }else{
            $currency = new Currency($this->user);
            $currencySymbol = $currency->ShowCurrencySymbol($this, $this->settings->get('Default Currency'),false);
        }
        $strXML  = "<chart ".$clickurl." caption='".$title."' subcaption='".$subcaption."' numberPrefix='".$currencySymbol."' ".$this->linechartDefaults.">";

        $strXML  .= "<categories>\n";
        foreach($labels as $label) {
            $strXML  .= "<category label='".$label."'/>\n";
        }
        $strXML  .= "</categories>\n\n";

        $Subtotal = "<dataset seriesName='Subtotal' color='#DFC329'>\n";
        $Tax      = "<dataset seriesName='Tax'      color='#FF6699'>\n";
        $Total    = "<dataset seriesName='Total'    color='#6699FF'>\n";
        for($x=0; $x< count($data[0]); $x++) {
            $Subtotal .= "<set value='".$data[0][$x]."' />\n";
            $Tax .= "<set value='".$data[1][$x]."' />\n";
            $Total .= "<set value='".$data[2][$x]."' />\n";
        }
        $Subtotal .= "</dataset>\n";
        $Tax .= "</dataset>\n";
        $Total .= "</dataset>\n";

        $strXML .= $Subtotal.$Tax.$Total;
        $strXML .= "<styles>";
        $strXML .= "<definition>";
        $strXML .= "    <style name='myToolTipFont' borderColor='dddddd' leftMargin='20' type='font' font='Verdana' size='10' color='333333'/>";
        $strXML .= "    <style name='myLegendFont' type='font' font='Verdana' size='10' color='333333' bold='0' />";
        $strXML .= "</definition>";
        $strXML .= "<application>";
        $strXML .= "    <apply toObject='ToolTip' styles='myToolTipFont' />";
        $strXML .= "    <apply toObject='Legend' styles='myLegendFont' />";
        $strXML .= "</application>";
        $strXML .= "</styles>";
        $strXML .= "</chart>";

        return $strXML;
    }
}
?>