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

//** Monthly Income Report        **//
$report_version = "1.3";
$report = new Report();
$report->SetDescription(lang('Displays total recurring transactions broken down by packages with the sum and expected monthly income from each.'));

/*
************************************************
*   1.0 Initial Report Released.  - Bart Wegrzyn          *
*   1.1 Fixed to account for yearly, semi-yearly,  quarterly, and monthly pricing.  - Shane Sammons          *
*   1.2 Only show those packages that have recurring prices not all packages.  - Alberto Vasquez          *
*   1.3 Only show those packages for active customers.  - Alberto Vasquez          *
************************************************
*/

//SQL to generate the the result set of the report
$sql ="SELECT COUNT(*) as counted, d.Plan, d.paymentterm
       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 planname,price3 /3 as price FROM package WHERE id = ?";
            $append = " (quarterly)";
    }elseif($row['paymentterm']== 6){ //semi-annualy
            $inSQL="SELECT planname,price6 /6 as price FROM package WHERE id = ?";
            $append = " (semi-annualy)";
    }elseif($row['paymentterm']== 12){ //annualy
            $inSQL="SELECT planname,price12 /12 as price FROM package WHERE id = ?";
            $append = " (annually)";
    }else{ //must be monthly
            $inSQL="SELECT planname,price FROM package WHERE id = ?";
            $append = '';
    }
    $result2=$GLOBALS['db']->query($inSQL, $row['Plan']);
        while($row2 = $result2->fetch()){
        //Now we do the math, and add the class array and variables
        //echo "Price: ".
        $tMonthlyIncome = $row2['price'] * $row['counted'];
        //echo "Plan Name: ".
        $tPackageName = $row2['planname'].$append;
        //echo "Quantity: ".
        $tPackageCount = $row['counted'];

        //."<br>";
                 if ($tMonthlyIncome > 0){
                        $tExpectedIncome = ShowCurrencySymbol($GLOBALS['gSettingsArray']['Default Currency'],false).sprintf("%01.2f", $tMonthlyIncome);
                        $expectedincomeTotal += $tMonthlyIncome;
                        $sumpertotalpackagesTotal += $tPackageCount;
                        $aGroup[] = array($tPackageName,$tPackageCount,$tExpectedIncome);
                 }
    }
}

  if (isset($aGroup)){
      $report->Add($aGroup,GetExtendedName(@$oldtotalpackages),array(lang('Package Name'),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')." 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;
           }
  }

?>
