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

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


include("config.inc.php");

define("_DEBUG",       "DEBUG MODE ON");
define("_PRODUCTION",  "DEBUG MODE OFF");
///////////////////////////////////////////   
$_mode = _DEBUG;   // Set mode to production after debug is finished
 
   


 

 
 
if ($_mode == _DEBUG) 
{

///////////////////////////////////////////////////////////
error_reporting (E_ALL ^ E_NOTICE ^ E_WARNING);
///////////////////////////////////////////////////////////
ini_set('display_errors', TRUE); // Error display
ini_set('log_errors', TRUE); // Error logging
ini_set('error_log', '../notice-logs.log'); // Logging file   // ../  to create file in main root cz ususaly root is contract
ini_set('log_errors_max_len', 10240); // Logging file size
///////////////////////////////////////////////////////////
}


	

//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(ClientSalesRMaskQty,0) As ClientSalesRMaskQty, 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_ListofSalesJV.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('List of Sales JV');
$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'  ));  
$regularrightFormatNum =& $workbook->addFormat(array('size'=>10,
  'align'=>'right'  )); 
  
$regularleftFormat1 =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ));
$regularrightFormat1 =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ));    
$regularrightFormat1Num =& $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
  )); 
$boldrightFormatNum =& $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();


$regularrightFormatNum->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$regularrightFormat1Num->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$boldrightFormatNum->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');


$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 ($fromclient != '')    $SelectedOptions .= "-> Client From: ". $fromclient . "  To: ". $toclient ."\n";
if ($fromsalesman != '')  $SelectedOptions .= "-> Salesman From: ". $fromsalesman . "  To: ". $tosalesman ."\n";
if ($fromproject != '')   $SelectedOptions .= "-> Project From: ". $fromproject . "  To: ". $toproject . "\n";
if ($fromcostcent != '')  $SelectedOptions .= "-> Costcent From: ". $fromcostcent . "  To: ". $tocostcent . "\n";

if ( $prProfitMargin !=0)
if ($filtermargin != '')  $SelectedOptions .= "-> Profit Margin: ". $filtermargin . "\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');   
$fromclient    = GetSQLValueString($fromclient, 'text');
$toclient      = GetSQLValueString($toclient, 'text');
$fromsalesman  = GetSQLValueString($fromsalesman, 'text');
$tosalesman    = GetSQLValueString($tosalesman, 'text');  

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


$MasterByLabel = '';
if ($groupby == 2 )       $MasterByLabel  = 'Client';
else if ($groupby == 3 )  $MasterByLabel  = 'Salesman';
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 Sales JV", $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;
	
	/*
	void Worksheet::setColumn ( integer $firstcol , integer $lastcol , float $width , mixed $format=0 , integer $hidden=0 ) */
$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Date", $boldleftFormat);
$ColumnNum += 1;	
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Ref.", $boldleftFormat);
$ColumnNum += 1;	
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"JV Ref.", $boldleftFormat);
$ColumnNum += 1;	
$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"Ledger No.", $boldleftFormat);
$ColumnNum += 1;	
$worksheet->setColumn ($ColumnNum, $ColumnNum, 50);
$worksheet->write($RowNum,$ColumnNum,"Name", $boldleftFormat);
$ColumnNum += 1;	
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Cur.", $boldcenterFormat);
$ColumnNum += 1;	
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Gross Total.", $boldcenterFormat);
$ColumnNum += 1;	
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Discount.", $boldcenterFormat);
$ColumnNum += 1;

// new columns here 

$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"VAT", $boldleftFormat);
$ColumnNum += 1;	

$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Net Total", $boldleftFormat);
$ColumnNum += 1;	

$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Total Cost", $boldleftFormat);
$ColumnNum += 1;	

			if ($prProfitMargin ==1 )
			{
				
	  $worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
      $worksheet->write($RowNum,$ColumnNum,"Profit Amount", $boldleftFormat);
      $ColumnNum += 1;
	  
	  
	  if($filtermargin != "Cost")	
{
	  $worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
      $worksheet->write($RowNum,$ColumnNum,"Profit Margin Selling", $boldleftFormat);
      $ColumnNum += 1;
}
else
{
	  $worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
      $worksheet->write($RowNum,$ColumnNum,"Profit Margin Cost", $boldleftFormat);
      $ColumnNum += 1;
}


				
			}
	
								if($showpaymentmode==1)
	{
		
	  $worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
      $worksheet->write($RowNum,$ColumnNum,"Cash Amount ($Base1)", $boldleftFormat);
      $ColumnNum += 1;
	  
	  $worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
      $worksheet->write($RowNum,$ColumnNum,"Bank Amount ($Base1)", $boldleftFormat);
      $ColumnNum += 1;
	  
	  $worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
      $worksheet->write($RowNum,$ColumnNum,"Card Amount ($Base1)", $boldleftFormat);
      $ColumnNum += 1;
	  
	  $worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
      $worksheet->write($RowNum,$ColumnNum,"Cash Amount ($Base2)", $boldleftFormat);
      $ColumnNum += 1;
	  
	  $worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
      $worksheet->write($RowNum,$ColumnNum,"Bank Amount ($Base2)", $boldleftFormat);
      $ColumnNum += 1;
	  
	  $worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
      $worksheet->write($RowNum,$ColumnNum,"Card Amount ($Base2)", $boldleftFormat);
      $ColumnNum += 1;
		
		
	}

if ($prtdetail == 1) {
	$RowNum += 1;
	$ColumnNum = 1;
	
	$worksheet->write($RowNum,$ColumnNum,"Ledger No.", $boldleftFormat);
	$ColumnNum += 1;	
	$worksheet->write($RowNum,$ColumnNum,"Name", $boldleftFormat);
	$ColumnNum += 1;	
	$worksheet->write($RowNum,$ColumnNum,"Description", $boldleftFormat);
	$ColumnNum += 1;	
	$worksheet->write($RowNum,$ColumnNum,"Qty", $boldrightFormat);
	$ColumnNum += 1;	
	$worksheet->write($RowNum,$ColumnNum,"Price", $boldcenterFormat);
	$ColumnNum += 1;	
	$worksheet->write($RowNum,$ColumnNum,"Discount", $boldcenterFormat);
	$ColumnNum += 1;	
	
	$worksheet->write($RowNum,$ColumnNum,"Total", $boldcenterFormat);
	$ColumnNum += 1;	
	
	$worksheet->write($RowNum,$ColumnNum,"Cost", $boldcenterFormat);
	$ColumnNum += 1;	
	
	$worksheet->write($RowNum,$ColumnNum,"Total Cost", $boldcenterFormat);
	$ColumnNum += 1;	
	
	 global $prProfitMargin ,$filtermargin;
	 

if ($prProfitMargin ==1 )
	{
		 

			if ($filtermargin != "Cost")
			{
			//	
			 $worksheet->write($RowNum,$ColumnNum,"Profit Amount", $boldcenterFormat);
	         $ColumnNum += 1;	
	
	         $worksheet->write($RowNum,$ColumnNum,"Profit Margin Selling Price", $boldcenterFormat);
	         $ColumnNum += 1;	
			 
			}
			else
			{
				//
			 $worksheet->write($RowNum,$ColumnNum,"Profit Amount", $boldcenterFormat);
	         $ColumnNum += 1;	
	
	         $worksheet->write($RowNum,$ColumnNum,"Profit Margin Cost Price", $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 if($groupby=2, SalesJV.Client_Code, if($groupby=3, ifNull(SalesJV.Salesman_Code, 'None'), if($groupby=4, SalesJV.Project_Code, if($groupby=5, SalesJV.Costcenter_Code, '')))) As MasterCode,   ".
" if($groupby=2, Clients.Ledger_Name, if($groupby=3, ifNull(Salesmen.Ledger_Name, 'None'), if($groupby=4, Project.Project_Name, if($groupby=5, Costcent.Costcent_Name, '')))) As MasterDescription,  ".
" SalesJV.SalesJV_Id As SalesJVId, Date_Format(SalesJV.SalesJV_Date,'%d/%m/%Y') As SalesJVDate, SalesJV.SalesJV_Reference As SalesJVReference, ".
" if(upper($curr)='".$Base1."', '".$Base1."', if(upper($curr)='".$Base2."', '".$Base2."', SalesJV.Currency_Code)) As CurrencyCode, ".
" Journal.Journal_RefGovernment As JournalRef, SalesJV.Client_Code As ClientCode, Clients.Ledger_Name As ClientName, ".				
" if(upper($curr)='".$Base1."', ifNull(SalesJV.SalesJV_TotalBase1,0), if(upper($curr)='".$Base2."', ifNull(SalesJV.SalesJV_TotalBase2,0), ifNull(SalesJV.SalesJV_Total,0))) As SalesJVGrossTotal  , ".
" if(upper($curr)='".$Base1."', ifNull(SalesJV.salesjv_discountamount1,0), if(upper($curr)='".$Base2."', ifNull(SalesJV.salesjv_discountamount2,0), ifNull(SalesJV.salesjv_discountamount,0))) As SalesJVDiscount, ".
" if(upper($curr)='".$Base1."', ifNull(SalesJV.SalesJV_VatBase1,0), if(upper($curr)='".$Base2."', ifNull(SalesJV.SalesJV_VatBase2,0), ifNull(SalesJV.SalesJV_VAT,0))) As SalesJVVAT, "
."if(upper($curr)='$Base1', (ifNull(SalesJV.SalesJV_TotalBase1, 0) - ifNull(SalesJV.SalesJV_DiscountAmount1, 0) + ifNull(SalesJV.SalesJV_VatBase1, 0)), if(upper($curr)='$Base2', (ifNull(SalesJV.SalesJV_TotalBase2, 0) - ifNull(SalesJV.SalesJV_DiscountAmount2, 0) + ifNull(SalesJV.SalesJV_VatBase2, 0)), (ifNull(SalesJV.SalesJV_Total, 0) - ifNull(SalesJV.SalesJV_DiscountAmount, 0) + ifNull(SalesJV.SalesJV_VAT, 0)))) AS SalesNetTotal,"	
."    Sum(if(upper($curr)='$Base1', Salesjvdt.costbase1  *salesjvdt_quantity, if(upper($curr)='$Base2', Salesjvdt.costbase2  *salesjvdt_quantity, Salesjvdt.cost  *salesjvdt_quantity)) )AS TotalCost,"	  

." if(upper($curr)='$Base1',(SalesJv.SalesJv_Totalbase1 - SalesJV.SalesJV_DiscountAmount1) - (Sum(Salesjvdt.costbase1  *salesjvdt_quantity)), if(upper($curr)='$Base2', (SalesJv.SalesJv_Totalbase2 - SalesJV.SalesJV_DiscountAmount2) - (Sum(Salesjvdt.costbase2  *salesjvdt_quantity)), (SalesJv.SalesJv_Total - SalesJV.SalesJV_DiscountAmount) - (Sum(Salesjvdt.cost  *salesjvdt_quantity)))) AS ProfitAmount,"  

."if(upper($curr)='$Base1', ( 100 *   (  (SalesJv.SalesJV_Totalbase1 - SalesJV.SalesJV_DiscountAmount1) - Sum(Salesjvdt.costbase1  *salesjvdt_quantity))) / (SalesJv.SalesJV_Totalbase1 - SalesJV.SalesJV_DiscountAmount1), if(upper($curr)='$Base2', ( 100 *   (  (SalesJv.SalesJV_Totalbase2 - SalesJV.SalesJV_DiscountAmount2) - Sum(Salesjvdt.costbase2  *salesjvdt_quantity))) / (SalesJv.SalesJV_Totalbase2 - SalesJV.SalesJV_DiscountAmount2), ( 100 *   (  (SalesJv.SalesJV_Total - SalesJV.SalesJV_DiscountAmount) - Sum(Salesjvdt.cost  *salesjvdt_quantity))) / (SalesJv.SalesJV_Total - SalesJV.SalesJV_DiscountAmount))) AS ProfitMarginSellingPrice,"

."   if(upper($curr)='$Base1',  ( 100 *   (  (SalesJv.SalesJV_Totalbase1 - SalesJV.SalesJV_DiscountAmount1) - Sum(Salesjvdt.costbase1  *salesjvdt_quantity))) / (Sum(Salesjvdt.costbase1  *salesjvdt_quantity)) , if(upper($curr)='$Base2',  ( 100 *   (  (SalesJv.SalesJV_Totalbase2 - SalesJV.SalesJV_DiscountAmount2) - Sum(Salesjvdt.costbase2  *salesjvdt_quantity))) / (Sum(Salesjvdt.costbase2  *salesjvdt_quantity)) ,  ( 100 *   (  (SalesJv.SalesJV_Total - SalesJV.SalesJV_DiscountAmount) - Sum(Salesjvdt.cost  *salesjvdt_quantity))) / (Sum(Salesjvdt.cost  *salesjvdt_quantity)) )) AS ProfitMarginCost,"

				/* cash bank card for base1 and base2  */
				."  ifNull(SalesJV.salesjv_cashammount1,0)       as 'Cash Ammount ($Base1)',  ".
				"  ifNull(SalesJV.salesjv_bankammount1,0)       as 'Bank Ammount ($Base1)',  ".
				"  ifNull(SalesJV.SalesJv_CreditCardAmmount1,0) as 'Card Ammount ($Base1)',  ".
				"  ifNull(SalesJV.salesjv_cashammount2,0)       as 'Cash Ammount ($Base2)',   ".
				"  ifNull(SalesJV.salesjv_bankammount2,0)       as 'Bank Ammount ($Base2)',  ".
				"  ifNull(SalesJV.SalesJv_CreditCardAmmount2,0) as 'Card Ammount ($Base2)' ".

				/*"SalesJv_CashAmmount1 as CashAmmount1 ,  SalesJv_BankAmmount1 as BankAmmount1 , SalesJv_CreditCardAmmount1 as CrediCardAmmount1 ".*/
				" From SalesJV  ".
				" Left Join Journal on Journal.Journal_Id = SalesJV.Journal_Id ".
				" Left Join Clients on Clients.Ledger_Number = SalesJV.Client_Code ".
				" Left Join Salesmen on Salesmen.Ledger_Number = SalesJV.Salesman_Code ".
				" Left Join Costcent on Costcent.Costcent_Code = SalesJV.Costcenter_Code ".
				" Left Join Project on Project.Project_Code = SalesJV.Project_Code "
				."LEFT JOIN Salesjvdt on Salesjvdt.salesjv_id = salesjv.salesjv_id";

if ($projectexist >	0 && $FI_RepRestrictProject == 1)
	$query= $query.$QueryRestrictPrj." on SalesJV.project_code like concat(Q4.project_code,'%') ";

if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
	$query= $query.$QueryRestrictCst." on SalesJV.Costcenter_Code like concat(Q5.costcent_code,'%') ";		 

$query= $query.	" Where (SalesJV.SalesJV_Reference Is Not Null And SalesJV.SalesJV_Reference != '')  ".
				" And ( ($option = 2 and  ifNull(SalesJV_Reference,'-1') between ifNull(".$fromref.", ifNull(SalesJV_Reference,'-1')) and ifNull(".$toref.", ifNull(SalesJV_Reference,'-1'))) or ".
			    "       ($option = 1 and date_format(SalesJV.SalesJV_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(SalesJV.SalesJV_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(SalesJV.SalesJV_Date,'%Y-%m-%d')) )) ".
				" And ifNull(SalesJV.Client_Code,'-1') between ifNull(".$fromclient.",ifNull(SalesJV.Client_Code,'-1')) and ifNull(". $toclient.",ifNull(SalesJV.Client_Code,'-1')) ".
				" And ifNull(SalesJV.Salesman_Code,'-1') between ifNull(".$fromsalesman.",ifNull(SalesJV.Salesman_Code,'-1')) and ifNull(". $tosalesman.",ifNull(SalesJV.Salesman_Code,'-1')) ".
				" And ifNull(SalesJV.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(SalesJV.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(SalesJV.Project_Code,'-1')) ".
				" And ifNull(SalesJV.Costcenter_Code,'-1') between ifNull(".$fromcostcent.",ifNull(SalesJV.Costcenter_Code,'-1')) and ifNull(". $tocostcent.",ifNull(SalesJV.Costcenter_Code,'-1')) ";

$query= $query." GROUP BY salesjv.salesjv_id  Order By MasterCode, if($option=1, salesjv.SalesJV_Date, SalesJV_Reference ), if($option=2, SalesJV_Reference, Null) ";

// echo $query;


	
//echo $query; exit;	

$RowNum    = 13;
$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  = $boldrightFormatNum;
		$TotalsLabel = 'Total by '.$MasterByLabel;
		
		$vRowNum = DisplayTotals($worksheet, $curr, $Base1, $Base2, $ArrayCurrency, $ArrayTotalByMaster, $DecimalBase, $TotalsLabel, $StyleClass, $RowNum, $ColumnNum);		

		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum = $vRowNum;
		$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);
	}
	
	if ($prtdetail == 1) {
		$vHeaderClassLeft  = $boldleftFormat;
		$vHeaderClassRight = $boldrightFormatNum;
	} else {
		$vHeaderClassLeft  = $regularleftFormat;
		$vHeaderClassRight = $regularrightFormatNum;
	}
	
	$worksheet->write($RowNum, $ColumnNum, $rows[3], $vHeaderClassLeft);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[4], $vHeaderClassLeft);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[6], $vHeaderClassLeft);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[7], $vHeaderClassLeft);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[8], $vHeaderClassLeft);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[5], $vHeaderClassLeft);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $rows[9], $vHeaderClassRight);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $rows[10], $vHeaderClassRight);
	$ColumnNum += 1;
 
	
	
	//
     global $prProfitMargin , $filtermargin , $showpaymentmode;
	 
	//
	
	
	$worksheet->writeNumber($RowNum, $ColumnNum, $rows[11], $vHeaderClassRight);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $rows[12], $vHeaderClassRight);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $rows[13], $vHeaderClassRight);
	$ColumnNum += 1;
	
					if ($prProfitMargin ==1 )
			{
				
		  $worksheet->writeNumber($RowNum, $ColumnNum,  format_currency($rows[14], $DecimalBase) , $vHeaderClassRight);
	      $ColumnNum += 1;
	
	
				
						if ($filtermargin != "Cost")
			{
		  $worksheet->writeNumber($RowNum, $ColumnNum,  format_currency($rows[15], $DecimalBase) , $vHeaderClassRight);
	      $ColumnNum += 1;				
			}
			
			else
			{
		  $worksheet->writeNumber($RowNum, $ColumnNum,  format_currency($rows[16], $DecimalBase) , $vHeaderClassRight);
	      $ColumnNum += 1;				
			}
				
			}
			
				if($showpaymentmode==1)
	{
		 $worksheet->writeNumber($RowNum, $ColumnNum,  format_currency($rows[17], $DecimalBase) , $vHeaderClassRight);
	      $ColumnNum += 1;	
		   $worksheet->writeNumber($RowNum, $ColumnNum,  format_currency($rows[18], $DecimalBase) , $vHeaderClassRight);
	      $ColumnNum += 1;	
		   $worksheet->writeNumber($RowNum, $ColumnNum,  format_currency($rows[19], $DecimalBase) , $vHeaderClassRight);
	      $ColumnNum += 1;	
		   $worksheet->writeNumber($RowNum, $ColumnNum,  format_currency($rows[20], $DecimalBase) , $vHeaderClassRight);
	      $ColumnNum += 1;	
		   $worksheet->writeNumber($RowNum, $ColumnNum,  format_currency($rows[21], $DecimalBase) , $vHeaderClassRight);
	      $ColumnNum += 1;	
		   $worksheet->writeNumber($RowNum, $ColumnNum,  format_currency($rows[22], $DecimalBase) , $vHeaderClassRight);
	      $ColumnNum += 1;	
		
	}
			
			
	
	/* $worksheet->writeNumber($RowNum, $ColumnNum, $rows[14], $vHeaderClassRight);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $rows[15], $vHeaderClassRight);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $rows[16], $vHeaderClassRight);
	$ColumnNum += 1; */
	
	
	
	
	
	/* $worksheet->writeNumber($RowNum, $ColumnNum, $rows[17], $vHeaderClassRight);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $rows[18], $vHeaderClassRight);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $rows[19], $vHeaderClassRight);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $rows[20], $vHeaderClassRight);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $rows[21], $vHeaderClassRight);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $rows[22], $vHeaderClassRight);
	$ColumnNum += 1; */
	
	

	if ($prtdetail == 1) {
	$queryDetail= " SELECT SalesJVDt.Ledger_Number,
       Ledger.Ledger_Name,
       SalesJVDt_Description,
       SalesJVDt_Quantity,
         if(upper($curr)='$Base1', SalesJVDt_Pricebase1, if(upper($curr)='$Base2', SalesJVDt_Pricebase2, SalesJVDt_Price)) AS price ,
		 
		 
		   if(upper($curr)='$Base1', SalesJVDT_DiscountAmount1, if(upper($curr)='$Base2', SalesJVDT_DiscountAmount1, SalesJVDT_DiscountAmount)) AS Discount ,
		 
		   if(upper($curr)='$Base1',  (ifNull(SalesJVDt_Quantity, 0) * ifNull(SalesJVDt_Pricebase1, 0) * (1 - ifNull(SalesJVDt_DiscountPer, 0)/100)) , if(upper($curr)='$Base2',  (ifNull(SalesJVDt_Quantity, 0) * ifNull(SalesJVDt_Pricebase2, 0) * (1 - ifNull(SalesJVDt_DiscountPer, 0)/100)) , (ifNull(SalesJVDt_Quantity, 0) * ifNull(SalesJVDt_Price, 0) * (1 - ifNull(SalesJVDt_DiscountPer, 0)/100)) )) AS SalesJvDt_Total ,

         if(upper($curr)='$Base1', costbase1, if(upper($curr)='$Base2', costbase2, cost)) AS cost ,

          if(upper($curr)='$Base1', costbase1*SalesJVDt_Quantity, if(upper($curr)='$Base2', costbase2*SalesJVDt_Quantity, cost*SalesJVDt_Quantity)) AS ToTalcost ,
  

 if(upper($curr)='$Base1',  ( (salesjvdt_pricebase1*salesjvdt_quantity- SalesJVdt_DiscountAmount1) - (costbase1  *salesjvdt_quantity)) , if(upper($curr)='$Base2',  ( (salesjvdt_pricebase2*salesjvdt_quantity- SalesJVdt_DiscountAmount2) - (costbase2  *salesjvdt_quantity)) , ( (salesjvdt_price*salesjvdt_quantity- SalesJVdt_DiscountAmount) - (cost  *salesjvdt_quantity)) )) AS ProfitAmount ,

     if(upper($curr)='$Base1', ( 100 *   (  (salesjvdt_quantity*salesjvdt_pricebase1 - SalesJVdt_DiscountAmount1) -  (costbase1  *salesjvdt_quantity))) / (salesjvdt_pricebase1- SalesJVdt_DiscountAmount1 ), if(upper($curr)='$Base2', ( 100 *   (  (salesjvdt_quantity*salesjvdt_pricebase2 - SalesJVdt_DiscountAmount2) -  (costbase2  *salesjvdt_quantity))) / (salesjvdt_pricebase2- SalesJVdt_DiscountAmount2 ), ( 100 *   (  (salesjvdt_quantity*salesjvdt_price - SalesJVdt_DiscountAmount) -  (cost  *salesjvdt_quantity))) / (salesjvdt_price- SalesJVdt_DiscountAmount ))) AS profitsellingmargin , 

    if(upper($curr)='$Base1', ( 100 *   (  (salesjvdt_quantity*salesjvdt_pricebase1 - SalesJVdt_DiscountAmount1) -  (costbase1  *salesjvdt_quantity))) / ( (costbase1  *salesjvdt_quantity) ), if(upper($curr)='$Base2', ( 100 *   (  (salesjvdt_quantity*salesjvdt_pricebase2 - SalesJVdt_DiscountAmount2) -  (costbase2  *salesjvdt_quantity))) / ( (costbase2  *salesjvdt_quantity) ), ( 100 *   (  (salesjvdt_quantity*salesjvdt_price - SalesJVdt_DiscountAmount) -  (cost  *salesjvdt_quantity))) / ( (cost  *salesjvdt_quantity) ))) AS ProfitMarginCost

    ".

					  " From SalesJVDt Inner Join Ledger on Ledger.Ledger_Number = SalesJVDt.Ledger_Number ".
					  " where SalesJVDt.SalesJV_Id = '".$rows[2]."'";
					  
					 // echo $queryDetail; exit;

		//echo $queryDetail;            
		$resultDetail=mysql_query($queryDetail,$connection) or die(mysql_error());
		while($rowsDetail=mysql_fetch_row($resultDetail)){
			//Increment the Rows Counter By 1 and Reset the Columns Counter
			$RowNum += 1;
			$ColumnNum = 1;

			$worksheet->write($RowNum, $ColumnNum, $rowsDetail[0], $regularleftFormat1);
			$ColumnNum += 1;
			$worksheet->write($RowNum, $ColumnNum, $rowsDetail[1], $regularleftFormat1);
			$ColumnNum += 1;
			$worksheet->write($RowNum, $ColumnNum, $rowsDetail[2], $regularleftFormat1);
			$ColumnNum += 1;
			$worksheet->write($RowNum, $ColumnNum, number_format($rowsDetail[3], $ClientSalesRMaskQty), $regularrightFormat1);
			$ColumnNum += 1;

			$worksheet->write($RowNum, $ColumnNum, number_format($rowsDetail[4], $ClientSalesRMaskQty), $regularrightFormat1);
			$ColumnNum += 1;
			$worksheet->write($RowNum, $ColumnNum, number_format($rowsDetail[5], $ClientSalesRMaskQty), $regularrightFormat1);
			$ColumnNum += 1;
			$worksheet->write($RowNum, $ColumnNum, number_format($rowsDetail[6], $ClientSalesRMaskQty), $regularrightFormat1);
			$ColumnNum += 1;
			$worksheet->write($RowNum, $ColumnNum, number_format($rowsDetail[7], $ClientSalesRMaskQty), $regularrightFormat1);
			$ColumnNum += 1;
			$worksheet->write($RowNum, $ColumnNum, number_format($rowsDetail[8], $ClientSalesRMaskQty), $regularrightFormat1);
			$ColumnNum += 1;			
			
			
			   global $prProfitMargin ,$filtermargin;
			if ($prProfitMargin ==1 )
	{

			if ($filtermargin != "Cost")
			{
			//	
					$worksheet->write($RowNum, $ColumnNum, number_format($rowsDetail[9], $ClientSalesRMaskQty), $regularrightFormat1);
			        $ColumnNum += 1;		
	                $worksheet->write($RowNum, $ColumnNum, number_format($rowsDetail[10], $ClientSalesRMaskQty), $regularrightFormat1);
			        $ColumnNum += 1;					
		 
			}
			else
			{
				//
				$worksheet->write($RowNum, $ColumnNum, number_format($rowsDetail[9], $ClientSalesRMaskQty), $regularrightFormat1);
			    $ColumnNum += 1;		
				$worksheet->write($RowNum, $ColumnNum, number_format($rowsDetail[11], $ClientSalesRMaskQty), $regularrightFormat1);
			    $ColumnNum += 1;		
			}

	}
	
			
			
			
			/* if (trim($curr, "'") == $Base1) {
				$worksheet->writeNumber($RowNum, $ColumnNum, $rowsDetail[5], $regularrightFormat1Num);
				$ColumnNum += 1;
				$worksheet->writeNumber($RowNum, $ColumnNum, $rowsDetail[9], $regularrightFormat1Num);
				$ColumnNum += 1;
			}	
			else if (trim($curr, "'") == $Base2) {
				$worksheet->writeNumber($RowNum, $ColumnNum, $rowsDetail[6], $regularrightFormat1Num);
				$ColumnNum += 1;
				$worksheet->writeNumber($RowNum, $ColumnNum, $rowsDetail[10], $regularrightFormat1Num);
				$ColumnNum += 1;
			}
			else {
				$worksheet->writeNumber($RowNum, $ColumnNum, $rowsDetail[4], $regularrightFormat1Num);
				$ColumnNum += 1;
				$worksheet->writeNumber($RowNum, $ColumnNum, $rowsDetail[8], $regularrightFormat1Num);
				$ColumnNum += 1;
			} */

		}
		
		$RowNum += 1;
	
	}
	
// Report
	$ArrayTotalByReport['grosstotal'][$rows[5]]        = $ArrayTotalByReport['grosstotal'][$rows[5]] + $rows[9];
	$ArrayTotalByReport['discount'][$rows[5]]        = $ArrayTotalByReport['discount'][$rows[5]] + $rows[10];

	$ArrayTotalByReport['Vat'][$rows[5]]        = $ArrayTotalByReport['Vat'][$rows[5]] + $rows[11];
	$ArrayTotalByReport['NetTotal'][$rows[5]]     = $ArrayTotalByReport['NetTotal'][$rows[5]] + $rows[12];

	$ArrayTotalByReport['totalcost'][$rows[5]]     = $ArrayTotalByReport['totalcost'][$rows[5]] + $rows[13];
	$ArrayTotalByReport['profitammount'][$rows[5]]     = $ArrayTotalByReport['profitammount'][$rows[5]] + $rows[14];

global $prProfitMargin;

global $filtermargin;

	if ($prProfitMargin ==1 )
	{

			if ($filtermargin != "Cost")
			{
			//	
				$ArrayTotalByReport['profitmargin'][$rows[5]]     = $ArrayTotalByReport['profitmargin'][$rows[5]] + $rows[15];
			}
			else
			{
				//
					$ArrayTotalByReport['profitmargin'][$rows[5]]     = $ArrayTotalByReport['profitmargin'][$rows[5]] + $rows[16];
			}

	}

	$ArrayTotalByReport['cashbase1'][$rows[5]]     = $ArrayTotalByReport['cashbase1'][$rows[5]] + $rows[17];
	$ArrayTotalByReport['bankbase1'][$rows[5]]     = $ArrayTotalByReport['bankbase1'][$rows[5]] + $rows[18];
	$ArrayTotalByReport['creditbase1'][$rows[5]]     = $ArrayTotalByReport['creditbase1'][$rows[5]] + $rows[19];
	$ArrayTotalByReport['cashbase2'][$rows[5]]     = $ArrayTotalByReport['cashbase2'][$rows[5]] + $rows[20];
	$ArrayTotalByReport['bankbase2'][$rows[5]]     = $ArrayTotalByReport['bankbase2'][$rows[5]] + $rows[21];
	$ArrayTotalByReport['creditbase2'][$rows[5]]     = $ArrayTotalByReport['creditbase2'][$rows[5]] + $rows[22];

	 // Master 
    $ArrayTotalByMaster['grosstotal'][$rows[5]]        = $ArrayTotalByMaster['grosstotal'][$rows[5]] + $rows[9];
    $ArrayTotalByMaster['discount'][$rows[5]]        = $ArrayTotalByMaster['discount'][$rows[5]] + $rows[10];
	$ArrayTotalByMaster['Vat'][$rows[5]]        = $ArrayTotalByMaster['Vat'][$rows[5]] + $rows[11];
	$ArrayTotalByMaster['NetTotal'][$rows[5]]     = $ArrayTotalByMaster['NetTotal'][$rows[5]] + $rows[12];

		$ArrayTotalByMaster['totalcost'][$rows[5]]     = $ArrayTotalByMaster['totalcost'][$rows[5]] + $rows[13];
	$ArrayTotalByMaster['profitammount'][$rows[5]]     = $ArrayTotalByMaster['profitammount'][$rows[5]] + $rows[14];

	if ($prProfitMargin ==1 )
	{

			if ($filtermargin != "Cost")
			{
			//	
				$ArrayTotalByMaster['profitmargin'][$rows[5]]     = $ArrayTotalByMaster['profitmargin'][$rows[5]] + $rows[15];
			}
			else
			{
				//
					$ArrayTotalByMaster['profitmargin'][$rows[5]]     = $ArrayTotalByMaster['profitmargin'][$rows[5]] + $rows[16];
			}

	}

	$ArrayTotalByMaster['cashbase1'][$rows[5]]       = $ArrayTotalByMaster['cashbase1'][$rows[5]] + $rows[17];
	$ArrayTotalByMaster['bankbase1'][$rows[5]]       = $ArrayTotalByMaster['bankbase1'][$rows[5]] + $rows[18];
	$ArrayTotalByMaster['creditbase1'][$rows[5]]     = $ArrayTotalByMaster['creditbase1'][$rows[5]] + $rows[19];
	$ArrayTotalByMaster['cashbase2'][$rows[5]]       = $ArrayTotalByMaster['cashbase2'][$rows[5]] + $rows[20];
	$ArrayTotalByMaster['bankbase2'][$rows[5]]       = $ArrayTotalByMaster['bankbase2'][$rows[5]] + $rows[21];
	$ArrayTotalByMaster['creditbase2'][$rows[5]]     = $ArrayTotalByMaster['creditbase2'][$rows[5]] + $rows[22];
	
	
	$RefFound = 0;
	$count = count ($ArrayCurrency); 
	for ($j=0; $j<$count; $j++){
		if ($ArrayCurrency[$j] == $rows[5])
			$RefFound = 1;
	}
				
	if ($RefFound == 0) 
		$ArrayCurrency[$count] = $rows[5];
		
	
	//Increment the Rows Counter By 1 and Reset the Columns Counter
	$RowNum += 1;
	$ColumnNum = 0;
	
}


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

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

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

	$RowNum = $vRowNum;
	$ColumnNum = 0;		



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

	$PCurr = trim($PCurr,"'");
	$PColumnNum = 5;
	
	global $prProfitMargin,$filtermargin,$showpaymentmode;
	
	 
	
	if ($PCurr == $PBase1) {
		
		
		$PTotalsLabel = $PTotalsLabel."  ".$PBase1;
		
		$worksheet->write($PRowNum, 5, $PTotalsLabel, $PStyleClass);
		$PColumnNum += 1;
		$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['grosstotal'][$PBase1], $PStyleClass);
		$PColumnNum += 1;
		$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['discount'][$PBase1], $PStyleClass);
		$PColumnNum += 1;
		$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['Vat'][$PBase1], $PStyleClass);
		$PColumnNum += 1;
		$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['NetTotal'][$PBase1], $PStyleClass);
		$PColumnNum += 1;
		$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['totalcost'][$PBase1], $PStyleClass);
		$PColumnNum += 1;	

 if ($prProfitMargin ==1)

	 {

        $worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['profitammount'][$PBase1], $PStyleClass);
		$PColumnNum += 1;	
		$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['profitmargin'][$PBase1], $PStyleClass);
		$PColumnNum += 1;	

	 }

if ($showpaymentmode == 1)
{
	
 /*        $worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['cashbase1'][$PBase1], $PStyleClass);
		$PColumnNum += 1;	
        $worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['bankbase1'][$PBase1], $PStyleClass);
		$PColumnNum += 1;	
        $worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['creditbase1'][$PBase1], $PStyleClass);
		$PColumnNum += 1;	
         $worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['cashbase2'][$PBase1], $PStyleClass);
		$PColumnNum += 1;	
        $worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['bankbase2'][$PBase1], $PStyleClass);
		$PColumnNum += 1;	
        $worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['creditbase2'][$PBase1], $PStyleClass);
		$PColumnNum += 1;	 */		
  

	}






		
	}
	else if ($PCurr == $PBase2) {
		
		
		
		$PTotalsLabel = $PTotalsLabel."  ".$PBase2;
		
		$worksheet->write($PRowNum, 5, $PTotalsLabel, $PStyleClass);
		$PColumnNum += 1;
		$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['grosstotal'][$PBase2], $PStyleClass);
		$PColumnNum += 1;
		$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['discount'][$PBase2], $PStyleClass);
		$PColumnNum += 1;
		$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['Vat'][$PBase2], $PStyleClass);
		$PColumnNum += 1;
		$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['NetTotal'][$PBase2], $PStyleClass);
		$PColumnNum += 1;
		$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['totalcost'][$PBase2], $PStyleClass);
		$PColumnNum += 1;	

 if ($prProfitMargin ==1)

	 {

        $worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['profitammount'][$PBase2], $PStyleClass);
		$PColumnNum += 1;	
		$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['profitmargin'][$PBase2], $PStyleClass);
		$PColumnNum += 1;	

	 }

if ($showpaymentmode == 1)
{/* 
         $worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['cashbase1'][$PBase2], $PStyleClass);
		$PColumnNum += 1;	
        $worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['bankbase1'][$PBase2], $PStyleClass);
		$PColumnNum += 1;	
        $worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['creditbase1'][$PBase2], $PStyleClass);
		$PColumnNum += 1;	 
        $worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['cashbase2'][$PBase2], $PStyleClass);
		$PColumnNum += 1;	
        $worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['bankbase2'][$PBase2], $PStyleClass);
		$PColumnNum += 1;	
        $worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['creditbase2'][$PBase2], $PStyleClass);
		$PColumnNum += 1;			 */
 

	}

		
		
		
		
	}
	else {
		$count = count ($PArrayCurrency); 
		$PTotalsLabel1 = $PTotalsLabel;
		for ($i=0; $i<$count; $i++){
		
			$Currency = $PArrayCurrency[$i];
			
			if ($PArrayTotal['NetTotal'][$Currency] != 0) {
				
		
		
		$PTotalsLabel = $PTotalsLabel1."  ".$Currency;
		
		$worksheet->write($PRowNum, 5, $PTotalsLabel, $PStyleClass);
		$PColumnNum += 1;
		$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['grosstotal'][$Currency], $PStyleClass);
		$PColumnNum += 1;
		$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['discount'][$Currency], $PStyleClass);
		$PColumnNum += 1;
		$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['Vat'][$Currency], $PStyleClass);
		$PColumnNum += 1;
		$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['NetTotal'][$Currency], $PStyleClass);
		$PColumnNum += 1;
		$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['totalcost'][$Currency], $PStyleClass);
		$PColumnNum += 1;	

 if ($prProfitMargin ==1)

	 {

        $worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['profitammount'][$Currency], $PStyleClass);
		$PColumnNum += 1;	
		$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['profitmargin'][$Currency], $PStyleClass);
		$PColumnNum += 1;	

	 }

if ($showpaymentmode == 1)
{/* 
         $worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['cashbase1'][$Currency], $PStyleClass);
		$PColumnNum += 1;	
        $worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['bankbase1'][$Currency], $PStyleClass);
		$PColumnNum += 1;	
        $worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['creditbase1'][$Currency], $PStyleClass);
		$PColumnNum += 1;	
        $worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['cashbase2'][$Currency], $PStyleClass);
		$PColumnNum += 1;	
        $worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['bankbase2'][$Currency], $PStyleClass);
		$PColumnNum += 1;	
        $worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['creditbase2'][$Currency], $PStyleClass);
		$PColumnNum += 1;			 
  */

	}

		
					
				$PColumnNum = 4;
				$PRowNum += 1;
				
				
			}	
		}
	
	}
	
	$PRowNum += 2;
	
	return $PRowNum;
	
}


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();

?>