<?php

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


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


/*
    The following installdir block of code must be here because
    when this page is called in the <img> tag, it is not included
    in the index.php file like it normally would be, so we have
    to make sure that the installdir gets set... specifically
    because we need the $connection variable which is defined in
    varDefinitions.php, which requires config.php, so we have to
    include both of them here and they won't work if we don't
    add the installdir to the include_path.  Hope that
    made sense :)
    
    If you don't include this code the image will not show up
    because PHP will spit out an error saying that the MySQL
    query is bad, and the image needs to be the only/first thing
    that is sent to the browser.
*/
// don't show if not logged in
if (!isset($_SESSION['userId'])) die('Log in first');

ini_set('include_path', dirname(__FILE__) . '/../..');

include_once("config.php");
include_once("common/varDefinitions.php");

// PHP must have write access to this directory
/*
    EDIT - it turns out on my server at least, that PHP
    does not need write access to the directory, but it does
    have to be defined or you'll get an error... go figure :-)
*/
//$cache_dir = "c:\\temp\\";    // example for Windows
$cache_dir = "/tmp/";      // example for Linux

if (!get_magic_quotes_gpc()) {
    $viewimage = addslashes(@$_GET['viewimage']);
} else {
    $viewimage = @$_GET['viewimage'];
}

if ($viewimage) {
    require_once 'classes/jpgraph/src/jpgraph.php';
    require_once 'classes/jpgraph/src/jpgraph_bar.php';
    
    // get all of the year totals
    $strSQL = "SELECT sum( amount ) as total, year( billdate ) as theyear
                FROM  `invoice` 
                WHERE `paid` = 0
                AND `billdate` is not null
                GROUP  BY year( billdate )
                ORDER BY `theyear` ASC";
                
    $result = $GLOBALS['db']->query($strSQL);
    
    $dataytemp = array();
    $dataxtemp = array();
    
    while ( list($dTotal,$nYear) = $result->fetch() ){
        $dataxtemp[] = $nYear;
        $dataytemp[] = $dTotal;
    }
    
    // now we have ALL the years in order... we want only the last x ($nShowYears)
    if (count($dataxtemp) > $nShowYears)
    {
        // gotta chop some off the front
        $datax = array_splice($dataxtemp, -1 * $nShowYears);
        $datay = array_splice($dataytemp, -1 * $nShowYears);
    }
    else
    {
        $datax = $dataxtemp;
        $datay = $dataytemp;
    }

    
    // Setup the graph. 
    $graph = new Graph(400,200,"auto"); 
    $graph->img->SetMargin(60,20,30,50);
    $graph->SetScale("textlin");
    $graph->SetMarginColor("#eeeeee");
    $graph->SetShadow();

    // Set up the title for the graph
    $graph->title->Set(lang('Unpaid Income for the last').' '.count($datax).' '.lang('years'));

    // Show 0 label on Y-axis (default is not to show)
    $graph->yscale->ticks->SupressZeroLabel(false);

    // Setup X-axis labels
    $graph->xaxis->SetTickLabels($datax);

    // Create the bar pot
    $bplot = new BarPlot($datay);
    $bplot->SetWidth(0.6);

    // Setup color for gradient fill style 
    $bplot->SetFillGradient("navy","#CCCCFF",GRAD_LEFT_REFLECTION);

    // Set color for the frame of each bar
    $bplot->SetColor("black");
    $graph->Add($bplot);

    // Finally send the graph to the browser
    $graph->Stroke();

}
else {  
    
    //GLOBALs available to you
    //$connection - connection string to database
    $report = new Report();
    $report->SetDescription("");
    
    if ( isset($_GET["year"]) && is_int((int)$_GET["year"]) )
        $nCurrentYear = $_GET["year"];
    else
        $nCurrentYear = date("Y",time());

    echo "<h1>".lang('Unpaid Invoices Report')."</h1>";
    echo "<p>",lang('This report totals the unpaid invoices for the selected year.')."</p>";
    echo "<p>".lang('Select a year').": <br><blockquote>";
    echo generateYearLinks($nCurrentYear);
    echo "</blockquote></p>";
    echo "<p><img src='index.php?fuse=".cREPORT."&report=".urlencode('Unpaid Invoices')."&type=income&viewimage=1'></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 paid = 0 
  AND YEAR(billdate) = ? 
  ORDER BY billdate";
    $result = $GLOBALS['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("",lang('Subtotal'),sprintf("$strBaseCurSymbol%01.2f", $monthSubTotal ),"");
                //add previous group before getting next group
                  $report->Add($aGroup,"$prevMonthName",array(lang('Invoice #'),lang('Customer'),lang('Amount'),lang('Due Date')));
                unset($aGroup);
            }
            $aGroup = array();
            $monthSubTotal = 0;
            $prevMonth = $thisMonth;
            $prevMonthName = date("F Y",strtotime($dtDateSent));
        }

        $tUser =& new User($userID);
        $tUser->PopulateCustomFields();
         if ($tUser->IsOrganization() == true) {
            $lastName = $tUser->getOrganization();
            if (strlen($lastName) > 20) {
                $dlastName = substr($lastName, 0,20) . "...";
                $title = "title=\"$lastName\"";
            } else {
                $dlastName = $lastName;
            }
        } else {
            $lastName = $tUser->getLastName();
            $dlastName = $lastName;
            $title = "";
        }


                // build a link to the customer profile 
                $CustomerLink = "<a href=\"index.php?fuse=3&frmClientID=".$userID."&action=0\" $title>" .$dlastName."</a>"; 

            
            // build the Print to PDF Link
            $printInvoiceLink = "<a href=index.php?fuse=4&frmClientID=".$userID."&action=0&billdetailid=".$nInvoiceId." target=blank>".$nInvoiceId."</a>";
                
            $strAmountFormatted = 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("",lang('Subtotal'),sprintf("$strBaseCurSymbol%01.2f", $monthSubTotal ),"");
        $report->Add($aGroup,"$prevMonthName",array(lang('Invoice #'),lang('Description'),lang('Amount'),lang('Due Date')));
        unset($aGroup);
    }

    $aGroup[] = array("","","<b>".sprintf("$strBaseCurSymbol%01.2f", $incomeTotal)."</b>","");
    $report->Add($aGroup,lang('Total for')." $nCurrentYear",array("","","",""));

    //Display Report
    $report->Show();
  
    echo "<p>".lang('* Items in')." <b><font color=\"red\">".lang('RED')."</font></b> ".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 paid = 0 ORDER BY billdate";
    $listResult = $GLOBALS['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];
        }
    }
    
    $strFormatted = sprintf("%01.2f", $dConvAmount );
    
    if ( $bConvFound )
    {
        return $strBaseCurSymbol.$strFormatted;
    }
    else
    {
        return "<b><font color=\"red\">$strFormatted ($strCurrency)</font></b>";
    }
    
    
}

?>
