<?php
/**
 * Unpaid Invoices Report
 *
 * @category Report
 * @package  ClientExec
 * @author   Kevin Grubbs <dev@ichthusmedia.com>
 * @license  ClientExec License
 * @version  2.3
 * @link     http://www.clientexec.com
 *
 *************************************************
 *   1.0 Initial Report Released.  - Kevin Grubbs
 *   1.1 Added currency conversion abilities
 *   1.2 Added graphs
 *   2.0 Added user names and links - Duane Charter
 *   2.1 Shows unpaid invoices only - Mike Mallinson
 *   2.2 Added partially payments - Juan Bolivar
 *   2.3 Added PEAR commenting, standard report title & Updated link to client profile
 ************************************************
 */

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

/**
 * Unpaid_Invoices Report Class
 *
 * @category Report
 * @package  ClientExec
 * @author   Kevin Grubbs <dev@ichthusmedia.com>
 * @license  ClientExec License
 * @version  2.3
 * @link     http://www.clientexec.com
 */
class Unpaid_Invoices extends Report
{

    /**
     * Report Process Method
     *
     * @return null - direct output
     */
    function process()
    {

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

        $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.51381;   // Pound Sterling (GBP)
        $arCurrencyConv["CAD"] = 0.856800;  // Canadian Dollar
        $arCurrencyConv["EUR"] = 1.33040;   // 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
        *********************************************************************************/

        // Set the report information
        $this->setName('Unpaid Invoices');
        $this->setVersion("2.3");
        $this->SetDescription($this->user->lang('This report totals the unpaid invoices for the selected year.'));

        // Load the currency information
        $currency = new Currency($this->user);

        // Output the headers before anything else
        $this->showReportHeader();

        if ( isset($_GET["year"]) && is_int((int)$_GET["year"]) )
            $nCurrentYear = $_GET["year"];
        else
            $nCurrentYear = date("Y",time());

        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, balance_due, billdate, currency "
                  ."FROM `users` "
                  ."INNER JOIN `invoice` ON `users`.`id` = `invoice`.`customerid` "
                  ."WHERE (`invoice`.status = 0 OR `invoice`.status = 5) "
                  ."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 = mb_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&view=Admin_Profile_Contact&frmClientID=".$userID."\" $title>" .$dlastName."</a>";


            // build the Print to PDF Link
            $printInvoiceLink = "<a href=index.php?fuse=billing&action=GeneratePDFInvoice&invoiceid=".$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(true);

        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 OR status = 5 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 = mb_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];
            }
        }

        $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>";
        }
    }
}
?>
