<?php

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

/**
* @package Reports
*/
class Monthly_Income extends Report
{

    function process()
    {
        //** Monthly Income Report        **//
        $currency = new Currency($this->user);
        $report_version = "1.4";
        $this->SetDescription($this->user->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          *
        *   1.4 Refactored to show income from addons as well - Alejandro Pedraza
        ************************************************
        */

        
        //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 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 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=$this->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'];
        
                $tExpectedIncome = $currency->format($this->settings->get('Default Currency'), $tMonthlyIncome, true, false);
                $expectedincomeTotal += $tMonthlyIncome;
                $sumpertotalpackagesTotal += $tPackageCount;
                $aGroup[] = array($tPackageName,$tPackageCount,$tExpectedIncome);

                // NOTE: remember the addon cycle can be different than the package's
                $sql = "SELECT 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']);
                while ($row3 = $result3->fetch()) {
                    if($row3['paymentterm']== 3){
                            $row3['total'] = $row3['total'] / 3;
                            $append = "quarterly";
                    }elseif($row['paymentterm']== 6){
                            $append = "semi-annualy";
                            $row3['total'] = $row3['total'] / 6;
                    }elseif($row['paymentterm']== 12){
                            $append = "annually";
                            $row3['total'] = $row3['total'] / 12;
                    }elseif($row['paymentterm']== 24){
                            $append = "bi-annually";
                            $row3['total'] = $row3['total'] / 24;
                    }elseif($row['paymentterm']== 1){
                            $append = 'monthly';
                    } else {
                        continue;
                    }
                    $tExpectedIncome = $currency->format($this->settings->get('Default Currency'), $row3['total'], true, false);
                    $expectedincomeTotal += $row3['total'];
                    $aGroup[] = array("$tPackageName $append add-ons", '', $tExpectedIncome);
                }
            }
        }
        
          if (isset($aGroup)){
              $this->Add($aGroup,$this->GetExtendedName(@$oldtotalpackages),array($this->user->lang('Package Name'),$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')." 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;
           }
    }
}
?>
