<?php

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

    function process()
    {
        /******************************************
        *  Annual Unpaid Income Report
        *  Date: 28 Mar 2004
        * 
        *    A report module for ClientExec 1.1 and higher
        *    (tested on 2.3)
        * 
        *  Purpose: Shows you annual income for unpaid invoices, also subtotals for each month.
        * 
        *  Remarks: Takes into account different currencies. This report will convert all invoice
        *  amounts into your base currency that you set below. But note that you will have to 
        *  enter all the conversion factors on your own. This script is setup for USD by default,
        *  and is able to convert EUR, CAD, and GBP.
        * 
        *  TODO: Graph totals are not in base currency, only detailed views are.
        * 
        *  Written by Kevin Grubbs
        *  Ichthus Media <><
        *  dev@ichthusmedia.com
        * 
        *  Modified by Duane Charter to add customer name and links to customer profile and invoices.
        *  Modified by Mike Mallinson to show only unpaid invoice details.
        * 
        * Rev History
        * 12/17/2003 - v1.0 - Initial Release
        * 12/18/2003 - v1.1 - Added currency conversion abilities
        * 03/28/2004 - v1.2 - Added graphs
        * 03/10/2005 - v2.0 - Added user names and links
        * 03/12/2005 - Unpaid Mod - Shows unpaid invoices only
        *******************************************/
        
        /********************************************************************************
        *           CONFIGURATION SETTINGS
        *********************************************************************************/ 
        
        // date format of the paid date
        // uses the PHP date() format syntax
        $strDateFormat = "j M Y";
        
        // Base currency.. set this to the currency you want to see your report in
        $strBaseCurrency = "USD";
        
        // Base Currency Symbol... set this to the symbol you want to see in the report
        $strBaseCurSymbol = "$";
        
        // number of years to show in the graph (last x years)
        $nShowYears = 4;
        
        require_once 'classes/Currency.php';
        $currency = new Currency($this->user);
        
        // Currency Conversion factors... uncomment and set these to the multiplying factor
        // to convert from the given currency back to your base currency
        //
        // Best way to do this...
        //  - go to http://www.xe.com/ucc/
        //  - put 1 in the far left box ("this amount")
        //  - put your base currency in the far right box ("into this type of currency")
        //  - put each currency below in the middle box ("of this type of currency")
        //  - THEN put the result(s) below
        //
        // you dont have to do all the currencies, just the ones that your clients use
        //$arCurrencyConv["USD"] = 1.0;     // United States Dollars
        $arCurrencyConv["GBP"] = 1.76857;   // Pound Sterling (GBP)
        $arCurrencyConv["CAD"] = 0.751026;  // Canadian Dollar
        $arCurrencyConv["EUR"] = 1.24151;   // Euro
        //$arCurrencyConv["INR"] = 1.0;  // Indian Rupees
        //$arCurrencyConv["NZD"] = 1.0;  // New Zealand Dollars
        //$arCurrencyConv["SAR"] = 1.0;  // Saudi riyal
        //$arCurrencyConv["TRL"] = 1.0;  // Turkish lira
        //$arCurrencyConv["SEK"] = 1.0;  // Swedish krona
        //$arCurrencyConv["SGD"] = 1.0;  // Singapore Dollar
        //$arCurrencyConv["JPY"] = 1.0;  // Yen
        //$arCurrencyConv["IDR"] = 1.0;  // Indonesia Rupiah (Rp)
        //$arCurrencyConv["AUD"] = 1.0;  // Australia Dollar
        //$arCurrencyConv["MYR"] = 1.0;  // Malaysian Ringgit (MYR)
        //$arCurrencyConv["PHP"] = 1.0;  // Philippine Peso (PHP)
        //$arCurrencyConv["CHF"] = 1.0;  // Swiss Franc (SwF)
        //$arCurrencyConv["SlT"] = 1.0;  // Slovenia Tolar (SIT)
        
        
        
        /********************************************************************************
        * You shouldnt need to change anything below here, unless you want to customize
        *********************************************************************************/ 
        
        $report_version = "1.2";
        
            
        $this->SetDescription("");
        
        if ( isset($_GET["year"]) && is_int((int)$_GET["year"]) )
            $nCurrentYear = $_GET["year"];
        else
            $nCurrentYear = date("Y",time());
    
        echo "<h1>".$this->user->lang('Unpaid Invoices Report')."</h1>";
        echo "<p>",$this->user->lang('This report totals the unpaid invoices for the selected year.')."</p>";
        echo "<p>".$this->user->lang('Select a year').": <br><blockquote>";
        echo $this->generateYearLinks($nCurrentYear);
        echo "</blockquote></p>";
       
      
        //SQL to generate the the result set of the report
        $reportSQL = "SELECT `invoice`.id, `invoice`.customerid, amount, billdate, currency "
                  ."FROM `users` "
                  ."INNER JOIN `invoice` ON `users`.`id` = `invoice`.`customerid` "
                  ."WHERE `invoice`.status = 0 "
                  ."AND YEAR(billdate) = ? "
                  ."ORDER BY billdate";
        $result = $this->db->query($reportSQL, $nCurrentYear);
    
        //initialize
        $incomeTotal = 0;
        $monthSubTotal = 0;
        $prevMonth = "-1";
      while (list($nInvoiceId,$userID,$dAmount,$dtDateSent,$strCurrency) = $result->fetch()){
            $thisMonth = date("n",strtotime($dtDateSent));
             
             if($prevMonth!=$thisMonth)
             {
                  if (isset($aGroup))
                  {
                    $aGroup[] = array("--------","-------","--------","--------");
                    $aGroup[] = array("",$this->user->lang('Subtotal'),$currency->format($strBaseCurrency, $monthSubTotal, true, false),"");
                    //add previous group before getting next group
                      $this->Add($aGroup,"$prevMonthName",array($this->user->lang('Invoice #'),$this->user->lang('Customer'),$this->user->lang('Amount'),$this->user->lang('Due Date')));
                    unset($aGroup);
                }
                $aGroup = array();
                $monthSubTotal = 0;
                $prevMonth = $thisMonth;
                $prevMonthName = date("F Y",strtotime($dtDateSent));
            }
    
            $tUser =& new User($userID);
             if ($tUser->IsOrganization() == true) {
                $lastName = $tUser->getOrganization();
                if (strlen($lastName) > 20) {
                    $dlastName = substr($lastName, 0,20) . "...";
                    $title = "title=\"$lastName\"";
                } else {
                    $dlastName = $lastName;
                    $title = "title=\"$lastName\"";
                }
            } else {
                $lastName = $tUser->getLastName();
                $dlastName = $lastName;
                $title = "";
            }
    
    
            // build a link to the customer profile 
            $CustomerLink = "<a href=\"index.php?fuse=clients&amp;frmClientID=".$userID."&amp;view=ShowCustomerData\" $title>" .$dlastName."</a>"; 

        
            // build the Print to PDF Link
            $printInvoiceLink = "<a href=index.php?fuse=clients&amp;frmClientID=".$userID."&amp;view=ViewInvoices&amp;billdetailid=".$nInvoiceId." target=blank>".$nInvoiceId."</a>";
                        
            $strAmountFormatted = $this->convertInvoiceAmount($dAmount,$strCurrency,$dConvAmount, $strBaseCurrency, $strBaseCurSymbol, $arCurrencyConv);
            $incomeTotal += $dConvAmount;
            $monthSubTotal += $dConvAmount;
            $aGroup[] = array($printInvoiceLink,$CustomerLink,$strAmountFormatted,date($strDateFormat,strtotime($dtDateSent)));
        }
    
        //add final group
        if (isset($aGroup)){
            $aGroup[] = array("--------","-------","--------","--------");
            $aGroup[] = array("",$this->user->lang('Subtotal'), $currency->format($strBaseCurrency, $monthSubTotal, true, false),"");
            $this->Add($aGroup,"$prevMonthName",array($this->user->lang('Invoice #'),$this->user->lang('Customer'),$this->user->lang('Amount'),$this->user->lang('Due Date')));
            unset($aGroup);
        }
    
        $aGroup[] = array("","","<b>".$currency->format($strBaseCurrency, $incomeTotal, true, false)."</b>","");
        $this->Add($aGroup,$this->user->lang('Total for')." $nCurrentYear",array("","","",""));
    
        //Display Report
        $this->Show();
      
        echo "<p>".$this->user->lang('* Items in')." <b><font color=\"red\">".$this->user->lang('RED')."</font></b> ".$this->user->lang("are invoice amounts not in your base currency that don't have conversion factors configured for this report. Please configure them as your total will be incorrect!")."</p>";
    }
    
    function generateYearLinks($nCurrentYear)
    {
        // first lets figure out what years are available to us
        $listSQL = "SELECT DISTINCT YEAR(billdate) FROM `invoice` WHERE status = 0 ORDER BY billdate";
        $listResult = $this->db->query($listSQL);
        
        $basePage = $_SERVER["REQUEST_URI"];
        
        // this sucks... but we have to do it this way
        // check to see if there is already a year in the url, and remove it
        // otherwise we end up just adding the year to the url every time and get year=2002&year=2003&year=2001
        // if you can think of a better way to do this, please let me know
        $nPos = strpos($basePage, "&year=");
        if ( $nPos === false )
        {
            $basePage .= "&year";
        }
        else
        {
            $basePage = substr($basePage, 0, $nPos) . "&year";
        }
        
        echo "-&nbsp;";
        while (list($tYear) = $listResult->fetch()) {
            $selected = ( $tYear == $nCurrentYear ) ? " selected" : "";
            echo "<a href=\"$basePage=$tYear\">$tYear</a>&nbsp;-&nbsp;";
        } 
    }
    
    function convertInvoiceAmount($dAmount, $strCurrency, &$dConvAmount, $strBaseCurrency, $strBaseCurSymbol, $arCurrencyConv)
    {   
        $bConvFound = true;
        
        // is this our base currency?
        if ( $strBaseCurrency == $strCurrency )
        {
            // yes, no conversion required
            $dConvAmount = $dAmount;
        }
        // else this isn't our base currency
        else
        {
            // do we have a conversion factor?
            if ( !isset( $arCurrencyConv[$strCurrency] ) )
            {
                // no conversion factor set, so return the original value
                $dConvAmount = $dAmount;
                $bConvFound = false;
            }
            else
            {
                // conversion factor found, convert
                $dConvAmount = $dAmount * $arCurrencyConv[$strCurrency];
            }
        }
        require_once 'classes/Currency.php';
        $currency = new Currency($this->user);
        $strFormatted = $currency->format($strBaseCurrency, $dConvAmount);
        
        if ( $bConvFound )
        {
            return $strBaseCurSymbol.$strFormatted;
        }
        else
        {
            return "<b><font color=\"red\">$strFormatted ($strCurrency)</font></b>";
        }
    }
}
?>
