<?php
// don't show if not logged in
if (!isset($_SESSION['userId'])) die('Log in first');

//** Monthly Income Report        **//
$report_version = "1.0";
$report = new Report();
$report->SetDescription(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)
************************************************
*/



//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.paymentterm <> 0 and d.customerid=u.id and u.status=1
       GROUP BY d.paymentterm,d.plan";
$result = $GLOBALS['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";
	        $append = " (quarterly)";
	}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";
	        $append = " (semi-annualy)";
	}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";
	        $append = " (annually)";
	}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";
            $append = '';
	}

	$result2=$GLOBALS['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'];
		$tPackageName = $row2['planname'].$append;
		$tPackageType = $row2['name'];
		$tPackageCount = $row['counted'];

		if ($tMonthlyIncome > 0){
				//$tExpectedIncome = sprintf("$%01.2f", $tMonthlyIncome);
				$expectedincomeTotal += $tMonthlyIncome;
				$sumpertotalpackagesTotal += $tPackageCount;
				if (isset($aGroup[$tPackageType])){
					$tArray = $aGroup[$tPackageType];
					$aGroup[$tPackageType] = array($tPackageType,$tArray[1]+$tPackageCount,$tArray[2]+$tMonthlyIncome);
				}else{
					$aGroup[$tPackageType] = array($tPackageType,$tPackageCount,$tMonthlyIncome);
				}
		}
	}
}


  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] = ShowCurrencySymbol($GLOBALS['gSettingsArray']['Default Currency'],false).sprintf("%01.2f", $aGroup[$tGroup[0]][2]);
      }

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

  $expectedincomeTotal = ShowCurrencySymbol($GLOBALS['gSettingsArray']['Default Currency'],false).sprintf("%01.2f",$expectedincomeTotal);
  $aGroup[] = array("--------","<b>" . $sumpertotalpackagesTotal . "</b>","<b>" . $expectedincomeTotal . "</b>");
  $report->Add($aGroup,lang('Totals'),array("","",""));

  //Display Report
  echo "<h1>".lang('Monthly Income by Package Type')." v$report_version</h1>";
  $report->Show();

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

  //function get the expected yearly income per billing cycle
  function GetExpectedMonthlyIncome($totalpackages,$tempSum){
           $packagesum = $totalpackages * $tempsum;
           return $packagesum;
  }

  //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;
           }
  }

?>
