<?php
/******************************************
*  Annual Income Report v 2.1
*  Date: 28 Mar 2004
* 
*    A report module for ClientExec 1.1 and higher
*    (tested on 2.3)
* 
*  Purpose: Shows you annual income for paid 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.
* 
* Rev History
* 12/17/2003 - v1.0 - Initial Release - Kevin Grubbs
* 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 - Duane Charter 
* 01/08/2006 - v2.1 - Default to not showing current year just graph
* 02/18/2006 - v2.2 - Added Tax to summary and graph
*******************************************/

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

$report_version = "2.1";

/********************************************************************************
* You shouldnt need to change anything below here, unless you want to customize
*********************************************************************************/ 

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

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

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

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

// 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( datepaid ) as theyear
                FROM  `invoice` 
                WHERE `invoice`.`paid` = 1
                GROUP  BY year( datepaid )
                ORDER BY `theyear` ASC";
                
    $result = $GLOBALS['db']->query($strSQL);
    
    $dataytemp = array();
    $dataxtemp = array();
    $taxtemp = array();
    
    while ( list($dTotal,$nYear) = $result->fetch() ){
        
        $strSQL = "SELECT sum( price ) as taxout
                FROM  `invoiceentry` ie, `invoice` i
                WHERE ie.invoiceid=i.id AND i.paid=1 AND year(i.datepaid)=$nYear";
                
        $result2 = $GLOBALS['db']->query($strSQL);
        $row = $result2->fetch();
        $dataytemp[] = $row['taxout'];
        $dataxtemp[] = $nYear;
        $taxtemp[] = $dTotal - $row['taxout'];
    }
    
    // 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);
        $tax = array_splice($taxtemp, -1 * $nShowYears);
    }
    else
    {
        $datax = $dataxtemp;
        $datay = $dataytemp;
        $tax = $taxtemp;
    }

    
    // 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('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);
    
    // Create the tax plot
    $tplot = new BarPlot($tax);
    $tplot->SetWidth(0.6);

    // Setup color for gradient fill style 
    $bplot->SetFillGradient("navy","#CCCCFF",GRAD_LEFT_REFLECTION);
    $tplot->SetFillGradient("azure4","azure1",GRAD_LEFT_REFLECTION);
    
    $bplot->SetLegend(lang("Income"));
    $tplot->SetLegend(lang("Taxes"));

    // Set color for the frame of each bar
    $bplot->SetColor("black");
    $tplot->SetColor("black");
    
    // Create the grouped bar plot
    $gbplot = new AccBarPlot(array($bplot,$tplot));
    
    $graph->Add($gbplot);

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

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

    if ( isset($_GET["month"]) && is_int((int)$_GET["month"]) ){        
        $nCurrentMonth = $_GET["month"];
    }else{
        if($nCurrentYear == date("Y",time())) $nCurrentMonth=1;
        else $nCurrentMonth = date("m",time());
    }

    echo "<h1>".lang('Annual Income Report').' '.lang('version')." 2.2</h1>";
    echo "<p>".lang('This report totals the paid invoices for the selected year.')."</p>";
    echo "<p><b>".lang('Select a year:')."</b><br>";
    echo generateYearLinks($nCurrentYear); 
    echo "<br><br>";
    echo "<b>".lang('Select a month:')."</b><br> ".generateMonthLinks($nCurrentMonth); 
    echo "</p>";
    echo "<p><img src=\"index.php?fuse=".cREPORT."&report=".urlencode('Income')."&type=income&viewimage=1\"></p>";
  
  $strYearFilter = "";
  if($nCurrentYear!="All") $strYearFilter .= "AND YEAR(datepaid) =".$nCurrentYear;
  if($nCurrentMonth!=0) $strYearFilter .= " AND MONTH(datepaid) =".$nCurrentMonth;
  
    //SQL to generate the the result set of the report
  $reportSQL = "SELECT `invoice`.id, `invoice`.customerid, amount, datepaid, currency, `invoice`.tax 
  FROM `users` 
  INNER JOIN `invoice` ON `users`.`id` = `invoice`.`customerid` 
  WHERE paid = 1 
  ".$strYearFilter." 
  ORDER BY datepaid";
    $result = $GLOBALS['db']->query($reportSQL);

    //initialize
    $incomeTotal = 0;
    $monthSubTotal = 0;
    $prevMonth = "-1";
    $recCount = 0;
    $TotalTax = 0;
    $totalInvoices = 0;
    $monthTax = 0;
    $monthInvoices = 0;
    while (list($nInvoiceId,$userID,$dAmount,$dtDatePaid,$strCurrency,$taxRate) = $result->fetch()){
        $thisMonth = date("n",strtotime($dtDatePaid));
         
         if($prevMonth!=$thisMonth)
         {
              if (isset($aGroup))
              {
                $aGroup[] = array("--------","-------","--------","--------");
                $aGroup[] = array("<b>".lang('Total Invoices')."</b>","<b>".lang('Total (Tax In)')."</b>","<b>".lang('Tax Charged')."</b>","<b>".lang('Total (Tax Out)')."</b>");
                $aGroup[] = array($monthInvoices, sprintf("$strBaseCurSymbol%01.2f", $monthSubTotal ),sprintf("$strBaseCurSymbol%01.2f", $monthTax),sprintf("$strBaseCurSymbol%01.2f", $monthSubTotal - $monthTax));
                //add previous group before getting next group
                $report->Add($aGroup,"$prevMonthName",array(lang('Invoice #'),lang('Customer'),lang('Amount'),lang('Date Paid')));
                unset($aGroup);
            }
            $aGroup = array();
            $monthSubTotal = 0;
            $monthTax = 0;
            $monthInvoices = 0;
            $prevMonth = $thisMonth;
            $prevMonthName = date("F Y",strtotime($dtDatePaid));
        }

        // Get list of use name
        $title = '';
        $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>";
		
        $taxRate = $taxRate/100;
        $strAmountFormatted = convertInvoiceAmount($dAmount,$strCurrency,$dConvAmount, $strBaseCurrency, $strBaseCurSymbol, $arCurrencyConv);
		$monthTax += ($dConvAmount * $taxRate) /(1+$taxRate);
		$TotalTax += ($dConvAmount * $taxRate)/(1+$taxRate);
		$incomeTotal += $dConvAmount;
		$monthSubTotal += $dConvAmount;
		$aGroup[] = array($printInvoiceLink,$CustomerLink,$strAmountFormatted,date($strDateFormat,strtotime($dtDatePaid)));
		$recCount++;
		$monthInvoices++;
		$totalInvoices++;
    }

    //add final group
    if (isset($aGroup)){
        $aGroup[] = array("--------","-------","--------","--------");
        $aGroup[] = array("<b>".lang('Total Invoices')."</b>","<b>".lang('Total (Tax In)')."</b>","<b>".lang('Tax Charged')."</b>","<b>".lang('Total (Tax Out)')."</b>");
        $aGroup[] = array($monthInvoices, sprintf("$strBaseCurSymbol%01.2f", $monthSubTotal ),sprintf("$strBaseCurSymbol%01.2f", $monthTax),sprintf("$strBaseCurSymbol%01.2f", $monthSubTotal - $monthTax));
        $report->Add($aGroup,"$prevMonthName",array(lang('Invoice #'),lang('Description'),lang('Amount'),lang('Date Paid')));
        unset($aGroup);
    }

    $aGroup[] = array($totalInvoices,sprintf("$strBaseCurSymbol%01.2f", $incomeTotal),sprintf("$strBaseCurSymbol%01.2f", $TotalTax),sprintf("$strBaseCurSymbol%01.2f", $incomeTotal - $TotalTax));
    if($nCurrentMonth==0) $report->Add($aGroup,lang('Total for').' '.$nCurrentYear,array(lang('Invoices'),lang('Income (Tax In)'),lang('Tax Charged'),lang('Income (Tax Out)')));

    //Display Report
    if($recCount>0){
        $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>";
    }else{
        echo "<p><b>".lang('* There are no invoices for the selected month')."</b></p>";        
    }
  
}

function generateMonthLinks($nCurrentMonth)
{
    
    $basePage = $_SERVER["REQUEST_URI"];

    $nPos = strpos($basePage, "&month=");
    
    if ( $nPos === false )
    {
        $basePage .= "&year=".@$_GET['year']."&month";
    }
    else
    {
        $basePage = substr($basePage, 0, $nPos) . "&year=".$_GET['year']."&month";
    }
    
    if($nCurrentMonth==1){ $strMonths = "<b><a href=\"$basePage=1\">".lang("January")."</a></b> - "; }
    else{ $strMonths = "<a href=\"$basePage=1\">".lang("January")."</a> - "; }
    
    if($nCurrentMonth==2){ $strMonths .= "<b><a href=\"$basePage=2\">".lang("February")."</a></b> - "; }
    else{ $strMonths .= "<a href=\"$basePage=2\">".lang("February")."</a> - "; }
    
    if($nCurrentMonth==3){ $strMonths .= "<b><a href=\"$basePage=3\">".lang("March")."</a></b> - "; }
    else{ $strMonths .= "<a href=\"$basePage=3\">".lang("March")."</a> - "; }

    if($nCurrentMonth==4){ $strMonths .= "<b><a href=\"$basePage=4\">".lang("April")."</a></b> - "; }
    else{ $strMonths .= "<a href=\"$basePage=4\">".lang("April")."</a> - "; }
    
    if($nCurrentMonth==5){ $strMonths .= "<b><a href=\"$basePage=5\">".lang("May")."</a></b> - "; }
    else{ $strMonths .= "<a href=\"$basePage=5\">".lang("May")."</a> - "; }

    if($nCurrentMonth==6){ $strMonths .= "<b><a href=\"$basePage=6\">".lang("June")."</a></b> - "; }
    else{ $strMonths .= "<a href=\"$basePage=6\">".lang("June")."</a> - "; }

    if($nCurrentMonth==7){ $strMonths .= "<b><a href=\"$basePage=7\">".lang("July")."</a></b> - "; }
    else{ $strMonths .= "<a href=\"$basePage=7\">".lang("July")."</a> - "; }

    if($nCurrentMonth==8){ $strMonths .= "<b><a href=\"$basePage=8\">".lang("August")."</a></b> - "; }
    else{ $strMonths .= "<a href=\"$basePage=8\">".lang("August")."</a> - "; }

    if($nCurrentMonth==9){ $strMonths .= "<b><a href=\"$basePage=9\">".lang("September")."</a></b> - "; }
    else{ $strMonths .= "<a href=\"$basePage=9\">".lang("September")."</a> - "; }

    if($nCurrentMonth==10){ $strMonths .= "<b><a href=\"$basePage=10\">".lang("October")."</a></b> - "; }
    else{ $strMonths .= "<a href=\"$basePage=10\">".lang("October")."</a> - "; }

    if($nCurrentMonth==11){ $strMonths .= "<b><a href=\"$basePage=11\">".lang("November")."</a></b> - "; }
    else{ $strMonths .= "<a href=\"$basePage=11\">".lang("November")."</a> - "; }

    if($nCurrentMonth==12){ $strMonths .= "<b><a href=\"$basePage=12\">".lang("December")."</a></b> - "; }
    else{ $strMonths .= "<a href=\"$basePage=12\">".lang("December")."</a> - "; }

    if($nCurrentMonth==0){ $strMonths .= "<b><a href=\"$basePage=0\">".lang("ALL")."</a></b> "; }
    else{ $strMonths .= "<a href=\"$basePage=0\" style='text-transform:capitalize;'>".strtolower(lang("ALL"))."</a> "; }
    
    return $strMonths;
    
}

function generateYearLinks($nCurrentYear)
{
    // first lets figure out what years are available to us
    $listSQL = "SELECT DISTINCT YEAR(datepaid) FROM `invoice` WHERE paid = 1 ORDER BY datepaid";
    $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";
    }
        
    while (list($tYear) = $listResult->fetch()) {
        if ( $tYear == $nCurrentYear ){     
            echo "<b><a href=\"$basePage=$tYear\">$tYear</a></b>&nbsp;-&nbsp;";         
        }else{
            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>";
    }
    
    
}

?>
