<?php
/******************************************
*  Annual Income Report v 2.3
*  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
* 01/30/2007 - v2.3 - Ported to new framework
* 07/01/2007 - v2.4 - Added new taxes calculations
* 03/01/2008 - v2.5 - Added tax column to invoice table
*******************************************/

/**
* @package Reports
*/
class Income extends Report
{
    var $m_hasgraph = true;

    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 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.4";
        ini_set('include_path', dirname(__FILE__) . '/../..');


        // 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'];
        }
        
        require_once 'classes/Currency.php';
        $currency = new Currency($this->user);

        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, SUM( subtotal ) AS subtotal, year( datepaid ) AS theyear "
                      ."FROM  `invoice` "
                      ."WHERE `invoice`.`status` = 1 "
                      ."GROUP  BY year( datepaid ) "
                      ."ORDER BY `theyear` ASC";
                        
            $result = $this->db->query($strSQL);
            
            $dataytemp = array();
            $dataxtemp = array();
            $taxtemp = array();
            
            while ( list($dTotal, $dSubTotal, $nYear) = $result->fetch() ){
        		if (!$nYear) {
        	        	continue;
        		}
                $dataytemp[] = $dSubTotal;
                $dataxtemp[] = $nYear;
                $taxtemp[] = $dTotal - $dSubTotal;
            }
            
            // 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(320, 150, "auto"); 
            $graph->SetMarginColor('white');
            $graph->SetScale("textlin");
            $graph->SetFrame(false);

            // Set up the title for the graph
            $txt =new Text($this->user->lang('Last').' '.count($datax).' '.$this->user->lang('years'));
            $txt->Pos(55,0);
            $txt->SetFont(FF_FONT1,FS_BOLD);
            $txt->SetColor( "black");
            $graph->AddText( $txt);

            // 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);
            
            if (!isset($_REQUEST['showlegend']) || $_REQUEST['showlegend'] == 1) {
                $bplot->SetLegend($this->user->lang("Income"));
                $tplot->SetLegend($this->user->lang("Taxes"));
                if (!$this->user->getShowDashboardGraphLegend()) {
                    $this->user->setShowDashboardGraphLegend(1);
                    $this->user->save();
                }
            } else {
                if ($this->user->getShowDashboardGraphLegend() == 1) {
                    $this->user->setShowDashboardGraphLegend(0);
                    $this->user->save();
                }
            }
            
            if (!isset($_REQUEST['showvalues']) || $_REQUEST['showvalues'] == 1) {
                $bplot->value->Show();
                $tplot->value->Show();    
                if (!$this->user->getShowDashboardGraphTotals()) {
                    $this->user->setShowDashboardGraphTotals(1);
                    $this->user->save();
                }
            } else {
                if ($this->user->getShowDashboardGraphTotals() == 1) {
                    $this->user->setShowDashboardGraphTotals(0);
                    $this->user->save();
                }
            }
            
            // 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);
            $graph->yaxis->scale->SetGrace(25);

            $graph->legend->SetShadow('gray@0.4',3);
            $graph->legend->SetPos(0.0,0.0,'right','top');

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

        }
        else {  
            $this->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>".$this->user->lang('Annual Income Report').' '.$this->user->lang('version')." 2.3</h1>";
            echo "<p>".$this->user->lang('This report totals the paid invoices for the selected year.')."</p>";
            echo "<p><b>".$this->user->lang('Select a year:')."</b><br>";
            echo $this->generateYearLinks($nCurrentYear); 
            echo "<br><br>";
            echo "<b>".$this->user->lang('Select a month:')."</b><br> ".$this->generateMonthLinks($nCurrentMonth); 
            echo "</p>";
            echo "<p><img src=\"index.php?fuse=reports&amp;report=".urlencode('Income')."&amp;type=income&amp;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, subtotal, datepaid, currency, `invoice`.tax "
                      ."FROM `users` "
                      ."INNER JOIN `invoice` ON `users`.`id` = `invoice`.`customerid` "
                      ."WHERE `invoice`.status = 1 "
                      .$strYearFilter 
                      ." ORDER BY datepaid";
            $result = $this->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,$dSubTotal,$dtDatePaid,$strCurrency,$taxRate) = $result->fetch()){
                $thisMonth = date("n",strtotime($dtDatePaid));
                 
                 if($prevMonth!=$thisMonth)
                 {
                      if (isset($aGroup))
                      {
                        $aGroup[] = array("--------","-------","--------","--------","--------");
                        $aGroup[] = array("<b>".$this->user->lang('Total Invoices')."</b>","","<b>".$this->user->lang('Total (Tax In)')."</b>","<b>".$this->user->lang('Tax Charged')."</b>","<b>".$this->user->lang('Total (Tax Out)')."</b>");
                        $aGroup[] = array($monthInvoices, '', $currency->format($strBaseCurrency, $monthSubTotal, true, false), $currency->format($strBaseCurrency, $monthTax, true, false),$currency->format($strBaseCurrency, $monthSubTotal - $monthTax, 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('Tax'),$this->user->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);
                 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=clients&amp;frmClientID=".$userID."&amp;view=ShowCustomerData\" $title>" .$dlastName."</a>";

                // build the Print to PDF Link
                $printInvoiceLink = "<a href=index.php?fuse=billing&amp;frmClientID=".$userID."&amp;view=ActiveInvoices&amp;billdetailid=".$nInvoiceId." target=blank>".$nInvoiceId."</a>";
                
                $taxRate = $taxRate/100;
                $strTaxFormatted = $this->convertInvoiceAmount($dAmount - $dSubTotal,$strCurrency,$convTax, $strBaseCurrency, $strBaseCurSymbol, $arCurrencyConv);
                $strAmountFormatted = $this->convertInvoiceAmount($dAmount,$strCurrency,$dConvAmount, $strBaseCurrency, $strBaseCurSymbol, $arCurrencyConv);
                $monthTax += $convTax;
                $TotalTax += $convTax;
                $incomeTotal += $dConvAmount;
                $monthSubTotal += $dConvAmount;
                $aGroup[] = array($printInvoiceLink,$CustomerLink,$strAmountFormatted,$strTaxFormatted,date($strDateFormat,strtotime($dtDatePaid)));
                $recCount++;
                $monthInvoices++;
                $totalInvoices++;
            }

            //add final group
            if (isset($aGroup)){
                $aGroup[] = array("--------","-------","--------","--------","--------");
                $aGroup[] = array("<b>".$this->user->lang('Total Invoices')."</b>","","<b>".$this->user->lang('Total (Tax In)')."</b>","<b>".$this->user->lang('Tax Charged')."</b>","<b>".$this->user->lang('Total (Tax Out)')."</b>");
                $aGroup[] = array($monthInvoices, '', $currency->format($strBaseCurrency, $monthSubTotal, true, false), $currency->format($strBaseCurrency, $monthTax, true, false), $currency->format($strBaseCurrency, $monthSubTotal - $monthTax, true, false));
                $this->Add($aGroup,"$prevMonthName",array($this->user->lang('Invoice #'),$this->user->lang('Description'),$this->user->lang('Amount'),$this->user->lang('Tax'), $this->user->lang('Date Paid')));
                unset($aGroup);
            }

            $aGroup[] = array($totalInvoices, '', $currency->format($strBaseCurrency, $incomeTotal, true, false), $currency->format($strBaseCurrency, $TotalTax, true, false), $currency->format($strBaseCurrency, $incomeTotal - $TotalTax, true, false));
            if($nCurrentMonth==0) $this->Add($aGroup,$this->user->lang('Total for').' '.$nCurrentYear,array($this->user->lang('Invoices'), '', $this->user->lang('Income (Tax In)'),$this->user->lang('Tax Charged'),$this->user->lang('Income (Tax Out)')));

            //Display Report
            if($recCount>0){
                $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>";
            }else{
                echo "<p><b>".$this->user->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\">".$this->user->lang("January")."</a></b> - "; }
        else{ $strMonths = "<a href=\"$basePage=1\">".$this->user->lang("January")."</a> - "; }
        
        if($nCurrentMonth==2){ $strMonths .= "<b><a href=\"$basePage=2\">".$this->user->lang("February")."</a></b> - "; }
        else{ $strMonths .= "<a href=\"$basePage=2\">".$this->user->lang("February")."</a> - "; }
        
        if($nCurrentMonth==3){ $strMonths .= "<b><a href=\"$basePage=3\">".$this->user->lang("March")."</a></b> - "; }
        else{ $strMonths .= "<a href=\"$basePage=3\">".$this->user->lang("March")."</a> - "; }

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

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

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

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

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

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

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

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

        if($nCurrentMonth==0){ $strMonths .= "<b><a href=\"$basePage=0\">".$this->user->lang("ALL")."</a></b> "; }
        else{ $strMonths .= "<a href=\"$basePage=0\" style='text-transform:capitalize;'>".strtolower($this->user->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 status = 1 ORDER BY datepaid";
        $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";
        }
            
        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];
            }
        }
        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>";
        }
    }
}

?>
