<?php
require_once('Spreadsheet/Excel/Writer.php');

session_start();
if($HTTP_SESSION_VARS['dbName']==""){
header("location:../index.php");
}


include("config.inc.php");

//Set Time Execution For the Report
ini_set('max_execution_time', $config['ReportTimeExecution']);

$connection = mysql_connect($config['dbServer'],$config['dbUser'],$config['dbPass']) or die("Could not connect to DB");
mysql_select_db($HTTP_SESSION_VARS['dbName'],$connection) or die("Could not find DB");


$querysetup="select grid_color1,grid_color2, DecimalBase, DecimalBase1, DecimalBase2, ifNull(PurchaseRMaskQty,0) As PurchaseRMaskQty, Base1, Base2, Company_Name As CompanyName from setup";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultsetup)){
  extract($rows);
}


$querylog=" select ifNull(FI_RepRestrictProject,0) As FI_RepRestrictProject, ifNull(FI_RepRestrictCostcenter,0) As FI_RepRestrictCostcenter, ".
		  " ifNull(FI_RepRestrictWarehouse,0) As FI_RepRestrictWarehouse ".
          " from ".$HTTP_SESSION_VARS['dbLabel'].".login where user_account='".$HTTP_SESSION_VARS['useraccount']."' and Upper(Login_Schema) = Upper('".$HTTP_SESSION_VARS['dbName']."') ";
$resultlog=mysql_query($querylog) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}


$queryproject=" select distinct project.project_code, project_name ".
              " from project left join ".$HTTP_SESSION_VARS['dbLabel'].".loginstk on project.project_code like concat(ifnull(loginstk.project_code,project.project_code),'%') ".
			  " where user_account='".$HTTP_SESSION_VARS['useraccount']."' ".
			  " and (loginstk.project_code is not null and loginstk.project_code != '') ".
			  " order by project.project_code";
$projectresult = mysql_query($queryproject,$connection) or die(mysql_error());
$projectexist  = mysql_num_rows($projectresult);

$querycostcent=" select distinct costcent.costcent_code, costcent_name ".
               " from costcent left join ".$HTTP_SESSION_VARS['dbLabel'].".loginstk on costcent.costcent_code like concat(ifnull(loginstk.costcent_code,costcent.costcent_code),'%') ".
			   " where user_account='".$HTTP_SESSION_VARS['useraccount']."' ".
			   " and (loginstk.costcent_code is not null and loginstk.costcent_code != '') ".
			   " order by costcent.costcent_code";
$costcentresult = mysql_query($querycostcent,$connection) or die(mysql_error());
$costcentexist  = mysql_num_rows($costcentresult);		


$now=current_server_time($connection);
$dt=split(" ",$now[0]);
$printdate=split("-",$dt[0]);


if ($curr == ''){
   $curr='Null';  
   $displaycur = '';
}   
else {
   $displaycur = $curr;
   $curr="'".$curr."'";
}  

//Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();

//Sending HTTP headers
$workbook->send('Exp_ListofPurchasesJVAndInvoice.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('List of Purchases');
$worksheet->setLandscape();
 
//set all columns same width
$columnWidth = 10;

//Title Format 
$sheetTitleFormat =& $workbook->addFormat(array('bold'=>1,
  'size'=>10));

//Column Title Format  
$columnTitleFormat =& $workbook->addFormat(array('bold'=>1,
  'top'=>1,
  'bottom'=>1 ,
  'size'=>15,
  'color'=>0x0A,
  ));
  
 
  
// Regular Format
$regularleftFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'left'  ));
$regularrightFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'right'  ));  
  
$regularleftFormat1 =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ));
$regularrightFormat1 =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ));    
  
$TitleFormat =& $workbook->addFormat(array('size'=>11,
  'align'=>'left',
  'bold'=>1
  ));

$boldleftFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'left'  ,
  'valign'=>'top'  ,
  'bold'=>1
  ));  
$boldrightFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'right'  ,
  'bold'=>1
  )); 
$boldcenterFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'center'  ,
  'valign'=>'top'  ,
  'bold'=>1
  ));    
  

$seloptionFormat =& $workbook->addFormat(array('size'=>9,
  'align'=> 'left'  ,
  'valign'=> 'top'  ,
  'bold'=> 1
  ));
$seloptionFormat->setTextWrap();

$SelectedOptions = "Filter by: \n";
if ($fromdate != '')      $SelectedOptions .= "-> Date From: ". $fromdate . "  To: ". $todate ."\n";	
if ($fromref != '')       $SelectedOptions .= "-> Reference From: ". $fromref . "  To: ". $toref ."\n";	
if ($displaycur != '')    $SelectedOptions .= "-> Currency: ". $displaycur ."\n";	
if ($fromsupplier != '')  $SelectedOptions .= "-> Supplier From: ". $fromsupplier . "  To: ". $tosupplier ."\n";
if ($fromproject != '')   $SelectedOptions .= "-> Project From: ". $fromproject . "  To: ". $toproject . "\n";
if ($fromcostcent != '')  $SelectedOptions .= "-> Costcent From: ". $fromcostcent . "  To: ". $tocostcent . "\n";


if (!empty($fromdate))   $fromdate = explodeword($fromdate);
if (!empty($todate))     $todate = explodeword($todate);  


$fromproject   = GetSQLValueString($fromproject, 'text');
$toproject     = GetSQLValueString($toproject, 'text');
$fromcostcent  = GetSQLValueString($fromcostcent, 'text');
$tocostcent    = GetSQLValueString($tocostcent, 'text');
$fromdate	   = GetSQLValueString($fromdate, 'date');
$todate        = GetSQLValueString($todate, 'date');
$fromref       = GetSQLValueString($fromref, 'text');
$toref         = GetSQLValueString($toref, 'text');   
$fromsupplier  = GetSQLValueString($fromsupplier, 'text');
$tosupplier    = GetSQLValueString($tosupplier, 'text');

  
if (empty($option)) 	 $option = 2;	
if (empty($prtdetail))   $prtdetail = 0;


$MasterByLabel = '';
if ($groupby == 2 )       $MasterByLabel  = 'Supplier';
else if ($groupby == 4 )  $MasterByLabel  = 'Project';
else if ($groupby == 5 )  $MasterByLabel  = 'CostCenter';

if ($MasterByLabel != '')
	$SelectedOptions = $SelectedOptions."Group by: ".$MasterByLabel;


//Display Report Header
$worksheet->write(1,0,$CompanyName, $TitleFormat);
$worksheet->write(1,4,"List of Purchases", $TitleFormat);
               
$worksheet->write(1,6,"Printed Date:", $boldleftFormat);
$worksheet->write(1,7,$printdate[2]."/".$printdate[1]."/".$printdate[0], $regularleftFormat);
$worksheet->write(2,6,"Time:", $boldleftFormat);		
$worksheet->write(2,7,$dt[1]." ".$dt[2], $regularleftFormat);
  
//Displaying Selected Options	
// Merge cells from row 3, col 0 to row 3, col 0
$worksheet->setMerge(3, 0, 8, 6);
$worksheet->write(3,0,$SelectedOptions, $seloptionFormat);  


$RowNum    = 10;
$ColumnNum = 0;
		

$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Date", $boldleftFormat);
$ColumnNum += 1;	
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"Ref.", $boldleftFormat);
$ColumnNum += 1;	
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"Type", $boldleftFormat);
$ColumnNum += 1;	
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Supplier Code", $boldleftFormat);
$ColumnNum += 1;	
$worksheet->setColumn ($ColumnNum, $ColumnNum, 50);
$worksheet->write($RowNum,$ColumnNum,"Name", $boldleftFormat);
$ColumnNum += 1;	
$worksheet->setColumn ($ColumnNum, $ColumnNum, 7);
$worksheet->write($RowNum,$ColumnNum,"Cur.", $boldcenterFormat);
$ColumnNum += 1;	
$worksheet->setColumn ($ColumnNum, $ColumnNum, 18);
$worksheet->write($RowNum,$ColumnNum,"Gross Total", $boldcenterFormat);
$ColumnNum += 1;		
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Discount", $boldcenterFormat);
$ColumnNum += 1;		
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"VAT", $boldcenterFormat);
$ColumnNum += 1;	
$worksheet->setColumn ($ColumnNum, $ColumnNum, 18);
$worksheet->write($RowNum,$ColumnNum,"Total", $boldcenterFormat);
$ColumnNum += 1;		

 
$QueryRestrictPrj = " Inner join ( select distinct project_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk ".
					" 			   where loginstk.user_account='".$HTTP_SESSION_VARS['useraccount']."' ".
					"              and (project_code is not null and project_code != '') ".
					"			 ) Q4  ";
					
$QueryRestrictCst = " Inner join ( select distinct costcent_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk ".
					"			   where loginstk.user_account='".$HTTP_SESSION_VARS['useraccount']."' ".
					"              and (costcent_code is not null and costcent_code != '') ".
					"			 ) Q5  ";
					


$query = '';
	
$query = $query." Select MasterCode, MasterDescription, PurchaseId, PurchaseDate, PurchaseDateOrder, PurchaseReference, SupplierCode, SupplierName, ".
				" PurchaseRemark, JournalType, CurrencyCode, if(JournalType='PR', (-1)*PurchaseGrossTotal, PurchaseGrossTotal) As PurchaseGrossTotal, ".
				" if(JournalType='PR', (-1)*PurchaseDiscount, PurchaseDiscount) As PurchaseDiscount, ".
				" if(JournalType='PR', (-1)*PurchaseVAT, PurchaseVAT) As PurchaseVAT, ".
				" if(JournalType='PR', (-1)*PurchaseTotal, PurchaseTotal) As PurchaseTotal ".
				" From ( ";
				
$query = $query." Select if($groupby=2, Purchases.Supplier_Code, if($groupby=4, Purchases.Project_Code, if($groupby=5, Purchases.Costcent_Code, ''))) As MasterCode,   ".
				" if($groupby=2, Suppliers.Ledger_Name, if($groupby=4, Project.Project_Name, if($groupby=5, Costcent.Costcent_Name, ''))) As MasterDescription,  ".
				" Purchases.Purchase_Id As PurchaseId, Date_Format(Purchases.Purchase_Date,'%d/%m/%Y') As PurchaseDate, Purchases.Purchase_Date As PurchaseDateOrder, Purchases.Purchase_Reference As PurchaseReference, ".
				" Purchases.Supplier_Code As SupplierCode, Suppliers.Ledger_Name As SupplierName, Purchases.Purchase_Remark As PurchaseRemark, if(Purchases.Journal_Type='PI', 'PI', 'PR') As JournalType, ".
				" if(upper($curr)='".$Base1."', '".$Base1."', if(upper($curr)='".$Base2."', '".$Base2."', Purchases.Currency_Code)) As CurrencyCode, ".
				" if(upper($curr)='".$Base1."', ifNull(Purchases.Purchase_TotalBase1,0), if(upper($curr)='".$Base2."', ifNull(Purchases.Purchase_TotalBase2,0), ifNull(Purchases.Purchase_Total,0))) As PurchaseGrossTotal, ".
				" if(upper($curr)='".$Base1."', ifNull(Purchases.Purchase_DiscountBase1,0), if(upper($curr)='".$Base2."', ifNull(Purchases.Purchase_DiscountBase2,0), ifNull(Purchases.Purchase_DiscountAmount,0))) As PurchaseDiscount, ".
				" if(upper($curr)='".$Base1."', ifNull(Purchases.Purchase_VATBase1,0), if(upper($curr)='".$Base2."', ifNull(Purchases.Purchase_VATBase2,0), ifNull(Purchases.Purchase_VAT,0))) As PurchaseVAT, ".
				" if(upper($curr)='".$Base1."', (ifNull(Purchases.Purchase_TotalBase1,0) - ifNull(Purchases.Purchase_DiscountBase1,0) + ifNull(Purchases.Purchase_VATBase1,0)), ".
				" if(upper($curr)='".$Base2."', (ifNull(Purchases.Purchase_TotalBase2,0) - ifNull(Purchases.Purchase_DiscountBase2,0) + ifNull(Purchases.Purchase_VATBase2,0)), ".
				" (ifNull(Purchases.Purchase_Total,0) - ifNull(Purchases.Purchase_DiscountAmount,0) + ifNull(Purchases.Purchase_VAT,0)))) As PurchaseTotal ".
				" From Purchases  ".
				" Left Join Suppliers on Suppliers.Ledger_Number = Purchases.Supplier_Code ".
				" Left Join Costcent on Costcent.Costcent_Code = Purchases.Costcent_Code ".
				" Left Join Project on Project.Project_Code = Purchases.Project_Code ";

if ($projectexist >	0 && $FI_RepRestrictProject == 1)
	$query= $query.$QueryRestrictPrj." on Purchases.project_code like concat(Q4.project_code,'%') ";
									 
if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
	$query= $query.$QueryRestrictCst." on Purchases.Costcent_Code like concat(Q5.costcent_code,'%') ";		 
						   

$query= $query.	" Where (Purchases.Purchase_Reference Is Not Null And Purchases.Purchase_Reference != '')  ".
				" And ( ($option = 2 and  ifNull(Purchase_Reference,'-1') between ifNull(".$fromref.", ifNull(Purchase_Reference,'-1')) and ifNull(".$toref.", ifNull(Purchase_Reference,'-1'))) or ".
				"       ($option = 1 and date_format(Purchases.Purchase_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(Purchases.Purchase_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Purchases.Purchase_Date,'%Y-%m-%d')) )) ".
				" And ifNull(Purchases.Supplier_Code,'-1') between ifNull(".$fromsupplier.",ifNull(Purchases.Supplier_Code,'-1')) and ifNull(". $tosupplier.",ifNull(Purchases.Supplier_Code,'-1')) ".
				" And ifNull(Purchases.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Purchases.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(Purchases.Project_Code,'-1')) ".
				" And ifNull(Purchases.Costcent_Code,'-1') between ifNull(".$fromcostcent.",ifNull(Purchases.Costcent_Code,'-1')) and ifNull(". $tocostcent.",ifNull(Purchases.Costcent_Code,'-1')) ";				

$query = $query." Union ";					

$query = $query." Select if($groupby=2, PurchasesJV.Supplier_Code, if($groupby=4, PurchasesJV.Project_Code, if($groupby=5, PurchasesJV.Costcenter_Code, ''))) As MasterCode,   ".
				" if($groupby=2, Suppliers.Ledger_Name, if($groupby=4, Project.Project_Name, if($groupby=5, Costcent.Costcent_Name, ''))) As MasterDescription,  ".
				" PurchasesJV.PurchaseJV_Id As PurchaseId, Date_Format(PurchasesJV.PurchaseJV_Date,'%d/%m/%Y') As PurchaseDate, PurchasesJV.PurchaseJV_Date As PurchaseDateOrder, PurchasesJV.PurchaseJV_Reference As PurchaseReference, ".
				" PurchasesJV.Supplier_Code As SupplierCode, Suppliers.Ledger_Name As SupplierName, PurchasesJV.Purchase_Remark As PurchaseRemark, PurchasesJV.Journal_Type As JournalType, ".
				" if(upper($curr)='".$Base1."', '".$Base1."', if(upper($curr)='".$Base2."', '".$Base2."', PurchasesJV.Currency_Code)) As CurrencyCode, ".
				" if(upper($curr)='".$Base1."', ifNull(PurchasesJV.PurchaseJV_TotalBase1,0), if(upper($curr)='".$Base2."', ifNull(PurchasesJV.PurchaseJV_TotalBase2,0), ifNull(PurchasesJV.PurchaseJV_Total,0))) As PurchaseGrossTotal, ".
				" if(upper($curr)='".$Base1."', ifNull(PurchasesJV.PurchaseJV_DiscAmount1,0), if(upper($curr)='".$Base2."', ifNull(PurchasesJV.PurchaseJV_DiscAmount2,0), ifNull(PurchasesJV.PurchaseJV_DiscAmount,0))) As PurchaseDiscount, ".
				" if(upper($curr)='".$Base1."', ifNull(PurchasesJV.PurchaseJV_VatBase1,0), if(upper($curr)='".$Base2."', ifNull(PurchasesJV.PurchaseJV_VatBase2,0), ifNull(PurchasesJV.PurchaseJV_VAT,0))) As PurchaseVAT, ".
				" if(upper($curr)='".$Base1."', (ifNull(PurchasesJV.PurchaseJV_TotalBase1,0) - ifNull(PurchasesJV.PurchaseJV_DiscAmount1,0) + ifNull(PurchasesJV.PurchaseJV_VatBase1,0)), ".
				" if(upper($curr)='".$Base2."', (ifNull(PurchasesJV.PurchaseJV_TotalBase2,0) - ifNull(PurchasesJV.PurchaseJV_DiscAmount2,0) + ifNull(PurchasesJV.PurchaseJV_VatBase2,0)), ".
				" (ifNull(PurchasesJV.PurchaseJV_Total,0) - ifNull(PurchasesJV.PurchaseJV_DiscAmount,0) + ifNull(PurchasesJV.PurchaseJV_VAT,0)))) As PurchaseTotal ".
				" From PurchasesJV  ".
				" Left Join Suppliers on Suppliers.Ledger_Number = PurchasesJV.Supplier_Code ".
				" Left Join Costcent on Costcent.Costcent_Code = PurchasesJV.Costcenter_Code ".
				" Left Join Project on Project.Project_Code = PurchasesJV.Project_Code ";

if ($projectexist >	0 && $FI_RepRestrictProject == 1)
	$query= $query.$QueryRestrictPrj." on PurchasesJV.project_code like concat(Q4.project_code,'%') ";
								 
if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
	$query= $query.$QueryRestrictCst." on PurchasesJV.Costcenter_Code like concat(Q5.costcent_code,'%') ";		 
					   

$query= $query.	" Where (PurchasesJV.PurchaseJV_Reference Is Not Null And PurchasesJV.PurchaseJV_Reference != '')  ".
				" And ( ($option = 2 and  ifNull(PurchaseJV_Reference,'-1') between ifNull(".$fromref.", ifNull(PurchaseJV_Reference,'-1')) and ifNull(".$toref.", ifNull(PurchaseJV_Reference,'-1'))) or ".
			    "       ($option = 1 and date_format(PurchasesJV.PurchaseJV_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(PurchasesJV.PurchaseJV_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(PurchasesJV.PurchaseJV_Date,'%Y-%m-%d')) )) ".
				" And ifNull(PurchasesJV.Supplier_Code,'-1') between ifNull(".$fromsupplier.",ifNull(PurchasesJV.Supplier_Code,'-1')) and ifNull(". $tosupplier.",ifNull(PurchasesJV.Supplier_Code,'-1')) ".
				" And ifNull(PurchasesJV.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(PurchasesJV.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(PurchasesJV.Project_Code,'-1')) ".
				" And ifNull(PurchasesJV.Costcenter_Code,'-1') between ifNull(".$fromcostcent.",ifNull(PurchasesJV.Costcenter_Code,'-1')) and ifNull(". $tocostcent.",ifNull(PurchasesJV.Costcenter_Code,'-1')) ";
			
$query = $query." ) Q1 ";			
$query= $query." Order By MasterCode, if($option=1, PurchaseDateOrder, PurchaseReference ), if($option=1, PurchaseReference, Null) ";			


	
//echo $query; exit;	

$RowNum    = 12;
$ColumnNum = 0;

$ArrayTotalByMaster   = Array();
$ArrayTotalByReport   = Array();
$ArrayCurrency	      = Array();

$MasterByCode = '';
$MasterByName = '';

$result=mysql_query($query,$connection) or die(mysql_error());
while($rows=mysql_fetch_row($result)){

	if (($groupby != 1) && ($MasterByCode != $rows[0] && $MasterByCode != '')) {
		    
		// Display Totals By Group	
		$StyleClass  = $boldrightFormat;
		$TotalsLabel = 'Total by '.$MasterByLabel;

		DisplayTotals($worksheet, $curr, $Base1, $Base2, $ArrayCurrency, $ArrayTotalByMaster, $DecimalBase, $TotalsLabel, $StyleClass, $RowNum, $ColumnNum);

		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 2;
		$ColumnNum = 0;
		
	}
	
	if (($groupby != 1) && ($MasterByCode == '' || $MasterByCode!= $rows[0])) {
		$worksheet->write($RowNum, $ColumnNum, $rows[0]." - ".$rows[1], $boldleftFormat);
		$ColumnNum += 1;
			
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 1;
		$ColumnNum = 0;
		
	    $MasterByCode = $rows[0];
		$MasterByName = $rows[1];
		
		unset($ArrayTotalByMaster);
	}


	
	$worksheet->write($RowNum, $ColumnNum, $rows[3], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[5], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[9], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[6], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[7], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[10], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, format_currency($rows[11], $DecimalBase), $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, format_currency($rows[12], $DecimalBase), $regularrightFormat);
	$ColumnNum += 1;	
	$worksheet->write($RowNum, $ColumnNum, format_currency($rows[13], $DecimalBase), $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, format_currency($rows[14], $DecimalBase), $regularrightFormat);
	$ColumnNum += 1;	

	
	//Show Remark
	if ($rows[8] != '') {
		$RowNum += 1;
		$ColumnNum = 1;	
		
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 8);
		$worksheet->write($RowNum, $ColumnNum, $rows[8], $regularleftFormat);
		$ColumnNum += 1;
		
	}
	
	

	
	$ArrayTotalByReport['GrossTotal']  = $ArrayTotalByReport['GrossTotal'] + $rows[11];
	$ArrayTotalByReport['Discount']    = $ArrayTotalByReport['Discount']   + $rows[12];
	$ArrayTotalByReport['Vat']         = $ArrayTotalByReport['Vat']        + $rows[13];
	$ArrayTotalByReport['Total']       = $ArrayTotalByReport['Total']      + $rows[14];
	
	$ArrayTotalByMaster['GrossTotal']  = $ArrayTotalByMaster['GrossTotal'] + $rows[11];
	$ArrayTotalByMaster['Discount']    = $ArrayTotalByMaster['Discount']   + $rows[12];
	$ArrayTotalByMaster['Vat']         = $ArrayTotalByMaster['Vat']        + $rows[13];
	$ArrayTotalByMaster['Total']       = $ArrayTotalByMaster['Total']      + $rows[14];
				
				
	//Increment the Rows Counter By 1 and Reset the Columns Counter
	$RowNum += 1;
	$ColumnNum = 0;			

}


// Display Totals By Group	
if ($groupby != 1 && $MasterByCode != '') {
		    
	$StyleClass  = $boldrightFormat;
	$TotalsLabel = 'Total by '.$MasterByLabel;
	
	DisplayTotals($worksheet, $curr, $Base1, $Base2, $ArrayCurrency, $ArrayTotalByMaster, $DecimalBase, $TotalsLabel, $StyleClass, $RowNum, $ColumnNum);		

	//Increment the Rows Counter By 1 and Reset the Columns Counter
	$RowNum += 2;
	$ColumnNum = 0;		
			
}

// Display Totals By Report			
$StyleClass  = $boldrightFormat;
$TotalsLabel = 'Totals';
DisplayTotals($worksheet, $curr, $Base1, $Base2, $ArrayCurrency, $ArrayTotalByReport, $DecimalBase, $TotalsLabel, $StyleClass, $RowNum, $ColumnNum);		


	

function DisplayTotals($worksheet, $PCurr, $PBase1, $PBase2, $PArrayCurrency, $PArrayTotal, $PDecimalBase, $PTotalsLabel, $PStyleClass, $PRowNum, $PColumnNum) {

	$PCurr = trim($PCurr,"'");
	$PColumnNum = 4;
	
	
	if ($PCurr == $PBase1) {
		$PTotalsLabel = $PTotalsLabel."  ".$PBase1;
	}
	else if ($PCurr == $PBase2) {
		$PTotalsLabel = $PTotalsLabel."  ".$PBase2;
	}
	
	if ($PCurr == $PBase1 || $PCurr == $PBase2) {
		$worksheet->write($PRowNum, $PColumnNum, $PTotalsLabel, $PStyleClass);
		$PColumnNum += 2;
		$worksheet->write($PRowNum, $PColumnNum, format_currency($PArrayTotal['GrossTotal'], $PDecimalBase), $PStyleClass);
		$PColumnNum += 1;
		$worksheet->write($PRowNum, $PColumnNum, format_currency($PArrayTotal['Discount'], $PDecimalBase), $PStyleClass);
		$PColumnNum += 1;
		$worksheet->write($PRowNum, $PColumnNum, format_currency($PArrayTotal['Vat'], $PDecimalBase), $PStyleClass);
		$PColumnNum += 1;
		$worksheet->write($PRowNum, $PColumnNum, format_currency($PArrayTotal['Total'], $PDecimalBase), $PStyleClass);
		$PColumnNum += 1;
	}	

}


function format_currency($amount, $DecimalBase) { 

    if($amount < 0) 
        return "(".number_format(abs($amount),$DecimalBase).")"; 
 
    else return number_format($amount, $DecimalBase); 
} 


function current_server_time($connection){

  $string="Select date_format(NOW(),'%Y-%m-%d %h:%i %p') ";
  $result=mysql_query($string,$connection) or die(mysql_error());
  $now=mysql_fetch_row($result);

  return $now;
}

function explodeword($date)
{

$trim = explode('/',$date);
$trim = "$trim[2]"."-"."$trim[1]"."-"."$trim[0]";
return $trim;
}

function GetSQLValueString($theValue, $theType) 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "Null";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "Null";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "Null";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "Null";
      break;
  }
  return $theValue;
}



$workbook->close();

?>