<?php

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

/**
* @package Reports
*/
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');

    function process()
    {
        //** Monthly Income Report        **//
        $currency = new Currency($this->user);
        $report_version = "1.1";
        $this->SetDescription($this->user->lang('Displays total recurring transactions broken down by package types with the sum and expected monthly income from each.'));
        
   /*
        ************************************************
        *   1.0 Initial Report Released.  - Alberto Vasquez
        *   (based off of monthly report created by Bart Wegrzyn)
        ************************************************
        */
        
        
        //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, d.paymentterm, d.promotionid "
             ."FROM domains d, users u "
             ."WHERE d.recurring <> 0 AND d.customerid=u.id AND u.status=1 AND d.status=1 "
             ."GROUP BY d.paymentterm, d.plan";
             
        $result = $this->db->query($sql);
        
        $expectedincomeTotal = 0;
        $sumpertotalpackagesTotal = 0;
        while($row = $result->fetch()){       
        
        	if($row['paymentterm']== 3){ //quarterly
        	        $inSQL="SELECT t.name, p.planname, p.price3 /3 AS price FROM package p, promotion t WHERE p.id = ? AND t.id=p.planid";
        	}elseif($row['paymentterm']== 6){ //semi-annualy
        	        $inSQL="SELECT t.name, p.planname, p.price6 /6 AS price FROM package p, promotion t WHERE p.id = ? AND t.id=p.planid";
        	}elseif($row['paymentterm']== 12){ //annualy
        	        $inSQL="SELECT t.name, p.planname, p.price12 /12 AS price FROM package p, promotion t WHERE p.id = ? AND t.id=p.planid";
        	}else{ //must be monthly
        			$inSQL="SELECT t.name, p.planname, p.price 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
        		$tMonthlyIncome = $row2['price'] * $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(amount) AS total, rf.paymentterm "
                      ."FROM recurringfee rf LEFT JOIN domains d ON rf.appliestoid=d.id "
                      ."WHERE rf.billingtypeid=? AND d.Plan=? AND d.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($row['paymentterm']== 6){
                            $row3['total'] = $row3['total'] / 6;
                    }elseif($row['paymentterm']== 12){
                            $row3['total'] = $row3['total'] / 12;
                    }elseif($row['paymentterm']== 24){
                            $row3['total'] = $row3['total'] / 24;
                    }elseif($row['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){
            //this supports lazy loading and dynamic loading of graphs
            echo $this->GraphData($aGroup,$sumpertotalpackagesTotal,$expectedincomeTotal);
            exit;
        }
        

        //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
          echo "<h1>".$this->user->lang('Monthly Income by Package Type')." v$report_version</h1>";
          $this->Show();
    }
    
    //*********************************************
    // Custom Function Definitions for this report
    //*********************************************

    //function to translate the digit billing cycle to the preferred header ( e.g. 1 returns Monthly )
    function GetExtendedName($totalpackages){
           switch ($totalpackages){
                   case 1:
                        return "Monthly";
                        break;
                   case 3:
                        return "Quarterly";
                        break;
                   case 6:
                        return "Semiannually";
                        break;
                   case 12:
                        return "Annual";
                        break;
           }
    }
    
    function GraphData($aGroups,$sum,$expectedIncome){
        
        
        $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='".$group[0]."' color='".$this->colorArray[$index]."' ".$issliced." />\n";
  			$index++;
  		}
    	
    	$strXML .= "</chart>\n";
        $this->logger->log(4,print_r("in graph data",true) );
        return $strXML;        
        
        
    }         
    
	function ReturnAverage($count,$totalCount){
		$avg = ($count/$totalCount);
		$avg =  $avg * 100;
		return ceil($avg);
	}    
    
}
?>
