<?php
/**
 * Monthly Income By Type Report
 *
 * @category Report
 * @package  ClientExec
 * @author   Jason Yates <jason@clientexec.com>
 * @license  ClientExec License
 * @version  1.2
 * @link     http://www.clientexec.com
 *
 *************************************************
 *   1.0 Initial Report Released.  - Alberto Vasquez
 *   1.2 Updated report to include a title & PEAR commenting
 ************************************************
 */

require_once 'modules/billing/models/Currency.php';
require_once 'modules/billing/models/BillingType.php';

/**
 * Monthly_Income_By_Type Report Class
 *
 * @category Report
 * @package  ClientExec
 * @author   Jason Yates <jason@clientexec.com>
 * @license  ClientExec License
 * @version  1.2
 * @link     http://www.clientexec.com
 */
class Monthly_Income_By_Type extends Report
{

    var $m_hasgraph = true;
    var $m_graphType = "Pie2D.swf";
    var $friendlyName = 'Income By Type (Monthly)';
    var $colorArray = array('#DFC329','#6699FF','#CC9933','#CC0033','#000099','#990033','#006600','#FFFF99','#FF6699');

    /**
     * Report Process Method
     *
     * @return null - direct output
     */
    function process()
    {
        // Set the report information
        $this->setName('Monthly Income By Type');
        $this->setVersion("1.2");
        $this->SetDescription($this->user->lang('Displays total recurring transactions broken down by package types with the sum and expected monthly income from each.'));

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

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

        //SQL to generate the the result set of the report
        $sql = "SELECT COUNT(*) AS counted, "
            ."d.Plan, "
            ."rf.paymentterm, "
            ."d.use_custom_price, "
            ."d.custom_price, "
            ."ocf.value AS domain_name "
            ."FROM domains d "
            ."LEFT JOIN `recurringfee` rf "
            ."ON rf.appliestoid = d.id "
            ."AND rf.billingtypeid = -1 "
            ."LEFT JOIN object_customField ocf "
            ."ON ocf.objectid = d.id "
            ."AND ocf.customFieldId = (SELECT cf.id "
            ."FROM customField cf "
            ."WHERE groupId = 2 "
            ."AND subGroupId  = 3 "
            ."AND name  = 'Domain Name'), "
            ."users u "
            ."WHERE rf.paymentterm <> 0 "
            ."AND IFNULL(rf.recurring, 0) <> 0 "
            ."AND d.customerid = u.id "
            ."AND u.status = 1 "
            ."AND d.status = 1 "
            ."GROUP BY rf.paymentterm, d.Plan, d.use_custom_price, d.custom_price ";
        $result = $this->db->query($sql);
        $expectedincomeTotal = 0;
        $sumpertotalpackagesTotal = 0;
        
        // Just in case we have domains
        $dng = new DomainNameGateway($this->user);
        while($row = $result->fetch()) {
            $inSQL="SELECT t.name, "
                ."p.planname, "
                ."p.pricing AS price, "
                ."t.type "
                ."FROM package p, "
                ."promotion t "
                ."WHERE p.id = ? "
                ."AND t.id = p.planid ";
            $result2=$this->db->query($inSQL, $row['Plan']);
            while($row2 = $result2->fetch()) {
                //Now we do the math, and add the class array and variables
                $pricing = unserialize($row2['price']);
                
                if($row2['type'] == 3){  // Domain Type
                    $aDomainName = $dng->splitDomain($row['domain_name']);
                    $tld = $aDomainName[1];
                    
                    $packagePrice = 0;
                    
                    if($row['paymentterm'] != 0 && ($row['paymentterm'] % 12) == 0 && isset($pricing['pricedata'][$tld][($row['paymentterm']/12)]['price'])){
                        $packagePrice = $pricing['pricedata'][$tld][($row['paymentterm']/12)]['price'] / $row['paymentterm'];
                    }
                }else{
                    $packagePrice = 0;
                    
                    if($row['paymentterm'] != 0 && isset($pricing['price'.$row['paymentterm']])){
                        $packagePrice = $pricing['price'.$row['paymentterm']]/$row['paymentterm'];
                    }
                }
                
                //this is for overrided prices
                if($row['paymentterm'] != 0 && $row["use_custom_price"]) {
                    $packagePrice = $row["custom_price"]/$row['paymentterm'];
                }
                $tMonthlyIncome = $packagePrice * $row['counted'];
                $tPackageType = $row2['name'];
                $tPackageCount = $row['counted'];

                $expectedincomeTotal += $tMonthlyIncome;
                $sumpertotalpackagesTotal += $tPackageCount;

                if ($tMonthlyIncome > 0) {
                    if (isset($aGroup[$tPackageType])) {
                        $tArray = $aGroup[$tPackageType];
                        $aGroup[$tPackageType] = array($tPackageType,$tArray[1]+$tPackageCount,$tArray[2]+$tMonthlyIncome);
                        //$aGraphGroup[$tPackageType] = array($tPackageType,$tArray[1]+$tPackageCount,$tArray[2]+$tMonthlyIncome);
                    }else {
                        $aGroup[$tPackageType] = array($tPackageType,$tPackageCount,$tMonthlyIncome);
                        //$aGraphGroup[$tPackageType] = array($tPackageType,$tPackageCount,$tMonthlyIncome);
                    }
                }

                // NOTE: remember the addon cycle can be different than the package's
                $sql = "SELECT COUNT(*) AS counted, "
                        ."SUM(rf.amount) AS total, "
                        ."rf.paymentterm "
                        ."FROM recurringfee rf "
                        ."LEFT JOIN (domains d "
                        ."LEFT JOIN `recurringfee` rrff "
                        ."ON rrff.appliestoid = d.id "
                        ."AND rrff.billingtypeid = -1) "
                        ."ON rf.appliestoid = d.id "
                        ."WHERE rf.billingtypeid = ? "
                        ."AND d.Plan = ? "
                        ."AND rrff.paymentterm = ? "
                        ."GROUP BY rf.paymentterm ";

                $result3 = $this->db->query($sql, BILLINGTYPE_PACKAGE_ADDON, $row['Plan'], $row['paymentterm']);
                $groupName = "$tPackageType add-ons";
                while ($row3 = $result3->fetch()) {
                    $tAddonCount = $row3['counted'];

                    //get expected monthly based on term
                    if($row3['paymentterm']== 3) {
                        $row3['total'] = $row3['total'] / 3;
                    }elseif($row3['paymentterm']== 6) {
                        $row3['total'] = $row3['total'] / 6;
                    }elseif($row3['paymentterm']== 12) {
                        $row3['total'] = $row3['total'] / 12;
                    }elseif($row3['paymentterm']== 24) {
                        $row3['total'] = $row3['total'] / 24;
                    }elseif($row3['paymentterm']== 1) {
                    } else {
                        continue;
                    }
                    $tExpectedIncome = $currency->format($this->settings->get('Default Currency'), $row3['total'], true, false);
                    $expectedincomeTotal += $row3['total'];

                    if($row3['total'] > 0) {
                        if (isset($aGroup[$groupName])) {
                            $tArray = $aGroup[$groupName];
                            $aGroup[$groupName] = array($groupName, $tArray[1] + $tAddonCount, $tArray[2] + $row3['total']);
                            //$aGraphGroup[$groupName] = array($groupName, $tArray[1] + $tAddonCount, $tArray[2] + $row3['total']);
                        } else {
                            $aGroup[$groupName] = array($groupName, $tAddonCount, $row3['total']);
                            //$aGraphGroup[$groupName] = array($groupName, $tAddonCount, $row3['total']);
                        }
                    }

                }
            }
        }

        if($graphdata && isset($aGroup)) {
            //this supports lazy loading and dynamic loading of graphs
            echo $this->GraphData($aGroup,$sumpertotalpackagesTotal,$expectedincomeTotal);
            exit;
        }

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

        //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']);
        $this->ShowGraph(0,$url);
        if (isset($aGroup)) {

            //Loop through group array and change price format now that all the sums have been made
            foreach ($aGroup as $tGroup) {
                $aGroup[$tGroup[0]][2] = $currency->format($this->settings->get('Default Currency'), $aGroup[$tGroup[0]][2], true, false);
            }

            $this->Add($aGroup,$this->GetExtendedName(@$oldtotalpackages),array($this->user->lang('Package Type'),$this->user->lang('Total Packages'),$this->user->lang('Expected Monthly Income')));
            unset($aGroup);
        }

        $expectedincomeTotal = $currency->format($this->settings->get('Default Currency'), $expectedincomeTotal, true, false);
        $aGroup[] = array("--------","<b>" . $sumpertotalpackagesTotal . "</b>","<b>" . $expectedincomeTotal . "</b>");
        $this->Add($aGroup,$this->user->lang('Totals'),array("","",""));

        //Display Report
        $this->Show();
    }

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

    /**
     * function to translate the digit billing cycle to the preferred header ( e.g. 1 returns Monthly )
     *
     * @return var - header item
     */
    function GetExtendedName($billingCycle)
    {
        switch ($billingCycle) {
            case 1:
                return $this->user->lang('Monthly');
                break;
            case 3:
                return $this->user->lang('Quarterly');
                break;
            case 6:
                return $this->user->lang('Semiannually');
                break;
            case 12:
                return $this->user->lang('Annual');
                break;
        }
    }

    /**
     * Function to output the xml for the graph data
     *
     * @return XML - graph data
     */
    function GraphData($aGroups,$sum,$expectedIncome) {

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

        $showborder="showborder='0'";
        $bgColor="bgColor='FFFFFF'";
        $title = "";
        $subcaption = "";

        if($this->viewingFromDashboard()) {
            //do not want to see title in dashboard since we already show from dropdown
            $legend="showLegend='0'";
            $labelstep="labelStep='3'";
            $clickurl="clickURL='index.php?fuse=reports&report=Monthly+Income+By+Type&type=Income&view=ViewReport'";
        }else {
            $clickurl="";
            $legend="";
            $labelstep="";
        }

        $strXML  = "<chart ".$clickurl." ".$bgColor." ".$showborder." ".$labelstep." ".$legend." useRoundEdges='1' caption='".$title."' subcaption='".$subcaption."' xAxisName='' yAxisName='' yAxisMinValue='' numberPrefix='' showValues='0' alternateHGridColor='FCB541' alternateHGridAlpha='20' divLineColor='FCB541' divLineAlpha='50' canvasBorderColor='666666' baseFontColor='666666' lineColor='FCB541'>\n";

        //get largest so that we can slice it when not viewing from dashboard
        $largest=0;
        $largestIndex=0;
        $gIndex=0;
        foreach($aGroups as $group) {
            if($group[2] > $largest) {
                $largest=$group[2];
                $largestIndex = $gIndex;
            }
            $gIndex++;
        }

        $index=0;
        foreach($aGroups as $group) {
            $issliced = "";
            if ( ($index==$largestIndex) && (!$this->viewingFromDashboard()) ) {
                $issliced = "isSliced='1'";
            }
            $strXML .= "<set value='".$this->ReturnAverage($group[2],$expectedIncome)."' label='".htmlentities($group[0],ENT_QUOTES)."' color='".$this->colorArray[$index]."' ".$issliced." />\n";
            $index++;
        }

        $strXML .= "</chart>\n";
        $this->logger->log(4,print_r("in graph data",true) );
        return $strXML;


    }

    /**
     * Function to return the average, used for making the pie chart
     *
     * @return num - average value
     */
    function ReturnAverage($count,$totalCount) {
        $avg = ($count/$totalCount);
        $avg =  $avg * 100;
        return ceil($avg);
    }

}
?>
