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

session_start();
if($_SESSION['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($_SESSION['dbName'],$connection) or die("Could not find DB");

	
$querysetup="select DecimalBase, DecimalBase1, DecimalBase2, 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);
}

//User Access
$querylog=" Select ifNull(FI_ShowCostPrice,0) As FI_ShowCostPrice, ifNull(FI_RepRestrictProject,0) As FI_RepRestrictProject, ifNull(FI_RepRestrictCostcenter,0) As FI_RepRestrictCostcenter ".
          " From ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' and Upper(Login_Schema) = Upper('".$_SESSION['dbName']."')  ";
$resultlog=mysql_query($querylog,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}


$queryproject=" select distinct project.project_code, project_name ".
              " from project left join ".$_SESSION['dbLabel'].".loginstk on project.project_code like concat(ifnull(loginstk.project_code,project.project_code),'%') ".
			  " where user_account='".$_SESSION['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 ".$_SESSION['dbLabel'].".loginstk on costcent.costcent_code like concat(ifnull(loginstk.costcent_code,costcent.costcent_code),'%') ".
			   " where user_account='".$_SESSION['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($_SESSION['module']=="0")
  $ReportTitle = 'List of Sales';
else {
  if ($transaction == 'SI' || $transaction == 'SR' )
    $ReportTitle = 'List of Sales';
  else
    $ReportTitle = 'List of Deliveries';  
}  


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

//Sending HTTP headers
$workbook->send('Exp_ListofSales.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet($ReportTitle);
$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'=>9,
  'align'=>'left'  ));
$regularrightFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ));  
$regularrightFormatNum =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  )); 
$regularcenterFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'center'  ));    
  
$regularleftFormat1 =& $workbook->addFormat(array('size'=>8,
  'align'=>'left'  ));
$regularrightFormat1 =& $workbook->addFormat(array('size'=>8,
  'align'=>'right'  ));    
$regularrightFormat1Num =& $workbook->addFormat(array('size'=>8,
  'align'=>'right'  ));    
  
$TitleFormat =& $workbook->addFormat(array('size'=>11,
  'align'=>'left',
  'bold'=>1
  ));

$boldleftFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ,
  'valign'=>'top'  ,
  'bold'=>1
  ));  
$boldrightFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ,
  'bold'=>1
  )); 
$boldrightFormatNum =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ,
  'bold'=>1
  )); 
$boldcenterFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'center'  ,
  'valign'=>'top'  ,
  'bold'=>1
  ));    
  
$grouplevel1Format =& $workbook->addFormat(array('size'=>10,
  'align'=>'left',
  'bold'=>1,
  'color'=>0x0C
  )); 
$grouplevel2Format =& $workbook->addFormat(array('size'=>10,
  'align'=>'left',
  'bold'=>0,
  'color'=>0x0A
  )); 
$grouplevel3Format =& $workbook->addFormat(array('size'=>9,
  'align'=>'left',
  'italic'=>1,
  'color'=>0x11
  ));   
$grouplevel1totalFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'right',
  'bold'=>1,
  'color'=>0x0C
  ));  
$grouplevel2totalFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'right',
  'bold'=>0,
  'color'=>0x0A
  ));  
$grouplevel3totalFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right',
  'italic'=>1,
  'color'=>0x11
  ));   

$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);_(* "-"??_);_(@_)');
$grouplevel1totalFormat->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');

if ($cur == 'currency1'){
   $cur='null';  
   $displaycur = '';
}   
else {
   $displaycur = $cur;
   $cur="'".$cur."'";
}

$SelectedOptions = "Filter by: \n";
if ($fromdate != '')      $SelectedOptions .= "-> Date From: ". $fromdate . "  To: ". $todate ."\n";	
if ($displaycur != '')    $SelectedOptions .= "-> Currency: ". $displaycur ."\n";	
if ($fromref != '')       $SelectedOptions .= "-> Reference From: ". $fromref . "  To: ". $toref ."\n";
if ($fromclient != '')    $SelectedOptions .= "-> Client From: ". $fromclient . "  To: ". $toclient ."\n";
if ($fromproject != '')   $SelectedOptions .= "-> Project From: ". $fromproject . "\n";

if (empty($fromclient))   
   $fromclient = 'null';
else
   $fromclient = "'".$fromclient."'";   

if (empty($fromsalesman))   
   $fromsalesman = 'null';
else
   $fromsalesman = "'".$fromsalesman."'";


if (empty($tosalesman))   
   $tosalesman = 'null';
else
   $tosalesman = "'".$tosalesman."'";

if (empty($fromcostcent))   
   $fromcostcent = 'null';
else
   $fromcostcent = "'".$fromcostcent."'";

 if (empty($fromgroup))   
   $fromgroup = 'null';
else
   $fromgroup = "'".$fromgroup."'";  
if (empty($togroup))   
   $togroup = 'null';
else
   $togroup = "'".$togroup."'";  

if (empty($fromitem))   
   $fromitem = 'null';
else
   $fromitem = "'".$fromitem."'";  
if (empty($toitem))   
   $toitem = 'null';
else
   $toitem = "'".$toitem."'";  


if (empty($fromwarehouse))   
   $fromwarehouse = 'null';
else
   $fromwarehouse = "'".$fromwarehouse."'"; 
if (empty($towarehouse))   
   $towarehouse = 'null';
else
   $towarehouse = "'".$towarehouse."'"; 



if (empty($fromsubgroup))   
   $fromsubgroup = 'null';
else
   $fromsubgroup = "'".$fromsubgroup."'";  
if (empty($tosubgroup))   
   $tosubgroup = 'null';
else
   $tosubgroup = "'".$tosubgroup."'";  

if (empty($fromcategory))   
   $fromcategory = 'null';
else
   $fromcategory = "'".$fromcategory."'";  
if (empty($tocategory))   
   $tocategory = 'null';
else
   $tocategory = "'".$tocategory."'";        
if (empty($toclient))   
   $toclient = 'null';
else
   $toclient = "'".$toclient."'";      

if (empty($fromproject))   
   $fromproject = 'null';
else
   $fromproject = "'".$fromproject."%'";  

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


$fromref      = GetSQLValueString($fromref, 'text');
$toref        = GetSQLValueString($toref, 'text');
$fromproject  = GetSQLValueString($fromproject, 'text');
$fromclient   = GetSQLValueString($fromclient, 'text');
$toclient     = GetSQLValueString($toclient, 'text');
$fromdate	  = GetSQLValueString($fromdate, 'date');
$todate       = GetSQLValueString($todate, 'date');   


$SalesReport = 0;
$ShowTotals  = 0;
$ShowCost    = 0;

if($_SESSION['module']=="0"){
  $showboth = $salesboth;
  $SalesReport = 0;
  $ShowTotals  = 1;
  $ShowCost    = 0;
}
else {
  if ($transaction == 'SI' || $transaction == 'SR' ) {
    $showboth = $salesboth;
	$SalesReport = 1;
	$ShowTotals  = 1;
	$ShowCost    = 0;
  }		
  else	{
	$showboth = $deliveryboth;
	$SalesReport = 0;
	
	if ($FI_ShowCostPrice == 1) {	$ShowTotals = 1; $ShowCost = 1;  }
	else { $ShowTotals = 0; $ShowCost  = 0;  }
  }	
}


//Display Report Header
$worksheet->write(1,0,$CompanyName, $TitleFormat);
$worksheet->write(1,6,$ReportTitle, $TitleFormat);
               
$worksheet->write(1,8,"Printed Date:", $boldleftFormat);
$worksheet->write(1,9,$printdate[2]."/".$printdate[1]."/".$printdate[0], $regularleftFormat);
$worksheet->write(2,8,"Time:", $boldleftFormat);		
$worksheet->write(2,9,$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, 15);
$worksheet->write($RowNum,$ColumnNum,"Date", $boldleftFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"Ref.", $boldleftFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"Type", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"Sorder#", $boldcenterFormat);

$ColumnNum += 1;
	
if($SalesReport==1){
	$worksheet->write($RowNum,$ColumnNum,"Delivery#", $boldcenterFormat);
}

$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Client", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 40);
$worksheet->write($RowNum,$ColumnNum,"Description", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"Project", $boldcenterFormat);
$ColumnNum += 1;
if($showProjCostName == 1){
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Project Name", $boldcenterFormat);
	$ColumnNum += 1;
}
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"Costcenter", $boldcenterFormat);
$ColumnNum += 1;
if($showProjCostName == 1){
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Costcenter Name", $boldcenterFormat);
	$ColumnNum += 1;
}
$worksheet->write($RowNum,$ColumnNum,"Cur.", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"Taxable", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"Non Taxable", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"Gross Total", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"Disc.", $boldcenterFormat);
//if ($pricettc == 0){
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,"VAT", $boldcenterFormat);
//}
if($ShowTotals == 1) {
	$ColumnNum += 1;
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Net Total ".$displaycur, $boldcenterFormat);
}
   


if ($showdetail == 1) {
	
	$RowNum += 1;
	$ColumnNum = 0;
	
	$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 5);
	$worksheet->write($RowNum,$ColumnNum,"Item Code/Description", $boldleftFormat);
	$ColumnNum += 6;
	$worksheet->write($RowNum,$ColumnNum,"Warehouse", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,"Qty.", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,"Unit Price", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,"Disc. %", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,"Total", $boldcenterFormat);
	$ColumnNum += 1;
	
	if ($ShowCost == 1) {
		$worksheet->write($RowNum,$ColumnNum,"T.MeanCost", $boldcenterFormat);
		$ColumnNum += 1;
	}
	
}	

    

//if ($pricettc == 0) {   
	$querydelivery= " select date_format(deliveries.delivery_date,'%d/%m/%Y') delivery_date, delivery_reference, deliveries.journal_type, ".
									" deliveries.client_code, clients.ledger_name , deliveries.project_code, deliveries.costcent_code, deliveries.currency_code, ".
									" ifnull(if(upper($cur)='".$Base1."',delivery_taxablebase1,if(upper($cur)='".$Base2."',delivery_taxablebase2,delivery_taxable)),0), ".
									" deliveries.delivery_id, if('".$date."' = 'date', date_format(deliveries.delivery_date,'%Y-%m-%d'), delivery_reference) ord, project_name,sorder_reference, null as sale_relatedid, deliveries.Delivery_Remark, ".
									" ifnull(if(upper($cur)='".$Base1."',delivery_nonetaxablebase1,if(upper($cur)='".$Base2."',delivery_nonetaxablebase2,delivery_nonetaxable)),0), ".
									" ifnull(if(upper($cur)='".$Base1."',delivery_vatbase1,if(upper($cur)='".$Base2."',delivery_vatbase2,delivery_vat)),0), ".
									" ifnull(if(upper($cur)='".$Base1."',delivery_discountbase1,if(upper($cur)='".$Base2."',delivery_discountbase2,delivery_discountamount)),0), ".
									" costcent_name ";
/*} else {
	$querydelivery= " select date_format(deliveries.delivery_date,'%d/%m/%Y') delivery_date, delivery_reference, deliveries.journal_type, ".
									" deliveries.client_code, clients.ledger_name , deliveries.project_code, deliveries.costcent_code, deliveries.currency_code, ".
									" convertcurfunc(deliveries.currency_code,if(upper($cur)='".$Base1."', '".$Base1."',if(upper($cur)='".$Base2."','".$Base2."',deliveries.currency_code)),deliveries.delivery_date,0,0,'','',ifnull(delivery_taxableTTC,0), ".
									" delivery_id,  if('".$date."' = 'date', date_format(deliveries.deliveries.delivery_date,'%Y-%m-%d'), delivery_reference) ord, project_name, sorder_reference, null as sale_relatedid, deliveries.Delivery_Remark, ".
									" ifnull(if(upper($cur)='".$Base1."',delivery_nonetaxablebase1,if(upper($cur)='".$Base2."',delivery_nonetaxablebase2,delivery_nonetaxable)),0), ".
									" convertcurfunc(deliveries.currency_code,if(upper($cur)='".$Base1."', '".$Base1."',if(upper($cur)='".$Base2."','".$Base2."',deliveries.currency_code)),deliveries.delivery_date,0,0,'','',ifnull(delivery_discountTTC,0))),".$DecimalBase.") ";
}*/

$querydelivery= $querydelivery." from deliveries ".
							   " left join clients on deliveries.client_code = clients.ledger_number ".
							   " left join project on deliveries.project_code = project.project_code ".
							   " left join costcent on costcent.Costcent_code = deliveries.Costcent_code".
							   " left join deliverydt on deliverydt.delivery_id = deliveries.delivery_id".
							   " left join items on deliverydt.item_code = items.item_code ";
if ($projectexist >	0 && $FI_RepRestrictProject == 1)
  $querydelivery= $querydelivery." left join ( select distinct project_code from ".$_SESSION['dbLabel'].".loginstk ".
								 "			 where loginstk.user_account='".$_SESSION['useraccount']."'  ".
								 "           and (project_code is not null and project_code != '') ".
								 "			) Q1 on deliveries.project_code like concat(Q1.project_code,'%') ";
if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1) 
		$querydelivery = $querydelivery." Inner join ( select distinct costcent_code from ".$_SESSION['dbLabel'].".loginstk ".
									  "			     where loginstk.user_account='".$_SESSION['useraccount']."' ".
									  "              and (costcent_code is not null and costcent_code != '') ".
								  	  "			 ) Q2 on deliveries.costcent_code like concat(Q2.Costcent_Code,'%')";	
									  
$querydelivery= $querydelivery.  				
				" where (($grpclient=1 and deliveries.client_code between ifNull(".$fromclient.",deliveries.client_code) and ifNull(". $toclient.",deliveries.client_code)) or ($grpclient=0 and 1 = 1)) ".
				" and delivery_reference between ifNull(". $fromref .",delivery_reference) and ifNull(". $toref.",delivery_reference) ".
				" and upper(ifNull(deliveries.project_code,-1)) like Concat(ifNull(upper(".$fromproject."), upper(ifNull(deliveries.project_code,-1))),'%') ".
				" and date_format(deliveries.delivery_date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(deliveries.delivery_date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(deliveries.delivery_date,'%Y-%m-%d'))  ".
				" and (($showboth = 1 and 1 = 1) or ($showboth =0 and deliveries.journal_type='".$transaction."')) 
				and deliveries.client_code between ifNull(".$fromclient.",deliveries.client_code) and ifNull(". $toclient.",deliveries.client_code)".
				 //and deliveries.Salesman_Code between ifNull(".$fromsalesman.",deliveries.salesman_code) and ifNull(". $tosalesman.",deliveries.salesman_code)
				" and deliveries.Costcent_code=ifnull(".$fromcostcent.",deliveries.Costcent_code)
				and deliverydt.item_Code between ifNull(".$fromitem.",deliverydt.item_code) and ifNull(". $toitem.",deliverydt.item_code)
				and items.group_Code between ifNull(".$fromgroup.",items.group_code) and ifNull(". $togroup.",items.group_code)
				and items.subgroup_Code between ifNull(".$fromsubgroup.",items.subgroup_code) and ifNull(". $tosubgroup.",items.subgroup_code)
				and items.category_Code between ifNull(".$fromcategory.",items.category_code) and ifNull(". $tocategory.",items.category_code)
				and deliverydt.warehouse_Code between ifNull(".$fromwarehouse.",deliverydt.warehouse_code) and ifNull(". $towarehouse.",deliverydt.warehouse_code)";	

$querydelivery= $querydelivery.  
				" order by if($grpclient=1, deliveries.Client_code, if($grpproject=1, deliveries.project_code, null) ),ord, delivery_reference ";
				
				
//if ($pricettc == 0) {   
	$querysales= " select date_format(Sale_Date,'%d/%m/%Y') Sale_Date, Sale_Reference, sales.journal_type, ".
							" client_code, clients.ledger_name , sales.project_code, sales.Costcenter_code, sales.currency_code, ".
							" ifnull(if(upper($cur)='".$Base1."',sale_taxablebase1,if(upper($cur)='".$Base2."',sale_taxablebase2,sale_taxable)),0), ".
							" sale_id, if('".$date."' = 'date', date_format(Sale_Date,'%Y-%m-%d'), Sale_Reference) ord, project_name, sorder_reference, sale_relatedid, sales.Sale_Remark,".
							" ifnull(if(upper($cur)='".$Base1."',sale_nonetaxablebase1,if(upper($cur)='".$Base2."',sale_nonetaxablebase2,sale_nonetaxable)),0),".
							" ifnull(if(upper($cur)='".$Base1."',sale_vatbase1,if(upper($cur)='".$Base2."',sale_vatbase2,sale_vat)),0),".
							" ifnull(if(upper($cur)='".$Base1."',sale_discountbase1,if(upper($cur)='".$Base2."',sale_discountbase2,sale_discountamount)),0)),".$DecimalBase."),".
							" costcent_name ";
/*} else {
	$querysales= " select date_format(Sale_Date,'%d/%m/%Y') Sale_Date, Sale_Reference, sales.journal_type, ".
							" client_code, clients.ledger_name , sales.project_code, sales.Costcenter_code, sales.currency_code, ".
							" convertcurfunc(sales.currency_code,if(upper($cur)='".$Base1."', '".$Base1."',if(upper($cur)='".$Base2."','".$Base2."',sales.currency_code)),Sale_Date,0,0,'','',ifnull(sale_taxableTTC,0)), ".
							" sale_id,  if('".$date."' = 'date', date_format(sales.Sale_Date,'%Y-%m-%d'), Sale_Reference) ord, project_name, sorder_reference, sale_relatedid, sales.Sale_Remark,".
							" ifnull(if(upper($cur)='".$Base1."',sale_nonetaxablebase1,if(upper($cur)='".$Base2."',sale_nonetaxablebase2,sale_nonetaxable)),0),".
							" convertcurfunc(sales.currency_code,if(upper($cur)='".$Base1."', '".$Base1."',if(upper($cur)='".$Base2."','".$Base2."',sales.currency_code)),Sale_Date,0,0,'','',ifnull(sale_discountTTC,0))),".$DecimalBase.") ";
}*/

$querysales= $querysales." from sales ".
				         " left join clients on sales.client_code = clients.ledger_number ".
						 " left join project on sales.project_code = project.project_code ".
						 " left join costcent on costcent.Costcent_code = sales.Costcent_code ";
				
if ($projectexist >	0 && $FI_RepRestrictProject == 1)
  $querysales= $querysales." left join ( select distinct project_code from ".$_SESSION['dbLabel'].".loginstk ".
						   "			 where loginstk.user_account='".$_SESSION['useraccount']."'  ".
						   "             and (project_code is not null and project_code != '') ".
						   "			) Q1 on sales.project_code like concat(Q1.project_code,'%') ";
if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1) 
		$querysales = $querysales." Inner join ( select distinct costcent_code from ".$_SESSION['dbLabel'].".loginstk ".
						  "			     where loginstk.user_account='".$_SESSION['useraccount']."' ".
						  "              and (costcent_code is not null and costcent_code != '') ".
						  "			 ) Q2 on sales.costcent_code like concat(Q2.Costcent_Code,'%')";		
$querysales= $querysales.  				
				" where (($grpclient=1 and sales.client_code between ifNull(".$fromclient.",sales.client_code) and ifNull(". $toclient.",sales.client_code)) or ($grpclient=0 and 1 = 1)) ".
				" and Sale_Reference between ifNull(". $fromref .",Sale_Reference) and ifNull(". $toref.",Sale_Reference) ".
				" and upper(ifNull(sales.project_code,-1)) like ifNull(upper(".$fromproject."), upper(ifNull(sales.project_code,-1))) ".
				" and date_format(Sale_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(Sale_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Sale_Date,'%Y-%m-%d'))  ".
				" and (($showboth = 1 and 1 = 1) or ($showboth =0 and sales.journal_type='".$transaction."')) ";

$querysales= $querysales.  
				" order by if($grpclient=1, Client_code, if($grpproject=1, sales.project_code, null) ), ord, Sale_Reference ";

$SumTaxable     = 0;
$SumNonTaxable = 0;
$SumGrossTot    = 0;
$SumDisc        = 0;
$SumVat         = 0;
$SumNetTot      = 0;
$SumGroupNetTot = 0;

$masterproject  = '';
$masterclient = '';

$RowNum    = 13;
$ColumnNum = 0;
	
//echo $querydelivery;
//exit;
if($SalesReport==1)
  $resultdelivery=mysql_query($querysales,$connection) or die(mysql_error());
else
  $resultdelivery=mysql_query($querydelivery,$connection) or die(mysql_error());
  
while($rowdelivery=mysql_fetch_row($resultdelivery)){

    //if ($pricettc == 0) {
		$querydeliverydt= " select deliverydt.item_code, items.item_description, deliverydt.warehouse_code, deliverydt_quantity, ".
						  " ifNull(if(upper($cur)='".$Base1."',deliverydt_pricebase1,if(upper($cur)='".$Base2."',deliverydt_pricebase2,deliverydt_price)),0), ".
						  " ifNull(deliverydt_discountper,0), ".
						  " ((ifNull(deliverydt_quantity,0) * ifNull(if(upper($cur)='".$Base1."',deliverydt_pricebase1,if(upper($cur)='".$Base2."',deliverydt_pricebase2,deliverydt_price)),0)) - ".
						  " ((ifNull(deliverydt_quantity,0) * ifNull(if(upper($cur)='".$Base1."',deliverydt_pricebase1,if(upper($cur)='".$Base2."',deliverydt_pricebase2,deliverydt_price)),0) * ifNull(deliverydt_discountper,0)) / 100) ) total, ".
						  " if(upper($cur)='".$Base1."', DeliveryDt_CostBase1, if(upper($cur)='".$Base2."', DeliveryDt_CostBase2, DeliveryDt_Cost))*ifNull(deliverydt_quantity,0) As MeanCostAmount ";
						  
	/*} else 	{
		$querydeliverydt= " select deliverydt.item_code, items.item_description, deliverydt.warehouse_code, deliverydt_quantity, ".
						  " convertcurfunc(deliverydt.currency_code,if(upper($cur)='".$Base1."', '".$Base1."',if(upper($cur)='".$Base2."','".$Base2."',deliverydt.currency_code)),delivery_date,0,0,'','',ifnull(deliverydt_priceTTC,0)), ".
						  " ifNull(deliverydt_discountper,0), ".
						  " convertcurfunc(deliverydt.currency_code,if(upper($cur)='".$Base1."', '".$Base1."',if(upper($cur)='".$Base2."','".$Base2."',deliverydt.currency_code)),delivery_date,0,0,'','', ".
						  " ((ifNull(deliverydt_quantity,0) * ifNull(deliverydt_priceTTC,0)) - ".
						  " ((ifNull(deliverydt_quantity,0) * ifNull(deliverydt_priceTTC,0) * ifNull(deliverydt_discountper,0)) / 100) ) ) total, ".
						  " if(upper($cur)='".$Base1."', DeliveryDt_CostBase1, if(upper($cur)='".$Base2."', DeliveryDt_CostBase2, DeliveryDt_Cost))*ifNull(deliverydt_quantity,0) As MeanCostAmount ";
						   
    }*/
	$querydeliverydt = $querydeliverydt." from deliverydt ".
										" left join items on deliverydt.item_code = items.item_code ".
										" where deliverydt.delivery_id = ". $rowdelivery[9]."
										and deliverydt.item_Code between ifNull(".$fromitem.",deliverydt.item_code) and ifNull(". $toitem.",deliverydt.item_code)
										and items.group_Code between ifNull(".$fromgroup.",items.group_code) and ifNull(". $togroup.",items.group_code)
										and items.subgroup_Code between ifNull(".$fromsubgroup.",items.subgroup_code) and ifNull(". $tosubgroup.",items.subgroup_code)
										and items.category_Code between ifNull(".$fromcategory.",items.category_code) and ifNull(". $tocategory.",items.category_code)
										and deliverydt.warehouse_Code between ifNull(".$fromwarehouse.",deliverydt.warehouse_code) and ifNull(". $towarehouse.",deliverydt.warehouse_code)";								
										
										
    //if ($pricettc == 0) {
		$querysalesdt= " select salesdt.item_code, items.item_description, salesdt.warehouse_code, saledt_quantity, ".
					   " ifNull(if(upper($cur)='".$Base1."',saledt_pricebase1,if(upper($cur)='".$Base2."',saledt_pricebase2,saledt_price)),0), ".
					   " ifNull(saledt_discountper,0), ".
					   " ((ifNull(saledt_quantity,0) * ifNull(if(upper($cur)='".$Base1."',saledt_pricebase1,if(upper($cur)='".$Base2."',saledt_pricebase2,saledt_price)),0)) - ".
					   " ((ifNull(saledt_quantity,0) * ifNull(if(upper($cur)='".$Base1."',saledt_pricebase1,if(upper($cur)='".$Base2."',saledt_pricebase2,saledt_price)),0) * ifNull(saledt_discountper,0)) / 100) ) total, ".
					   " 0 As MeanCostAmount ";
						  
	/*} else 	{
		$querysalesdt= " select salesdt.item_code, items.item_description, salesdt.warehouse_code, saledt_quantity, ".
					   " convertcurfunc(salesdt.currency_code,if(upper($cur)='".$Base1."', '".$Base1."',if(upper($cur)='".$Base2."','".$Base2."',salesdt.currency_code)),sale_date,0,0,'','',ifnull(saledt_priceTTC,0)), ".
					   " ifNull(saledt_discountper,0), ".
					   " convertcurfunc(salesdt.currency_code,if(upper($cur)='".$Base1."', '".$Base1."',if(upper($cur)='".$Base2."','".$Base2."',salesdt.currency_code)),sale_date,0,0,'','', ".
					   " ((ifNull(saledt_quantity,0) * ifNull(saledt_priceTTC,0)) - ".
					   " ((ifNull(saledt_quantity,0) * ifNull(saledt_priceTTC,0) * ifNull(saledt_discountper,0)) / 100) ) ) total, ".
					   " 0 As MeanCostAmount ";
						   
    }*/
	$querysalesdt = $querysalesdt." from salesdt ".
					        	  " left join items on salesdt.item_code = items.item_code ".
							      " where salesdt.sale_id = ". $rowdelivery[9];													  
	
	//echo $querydeliverydt;
	
	if($_SESSION['module']=="1" && ($transaction == 'DI' || $transaction == 'DR' )){
		$sub_query= " select Sum(if(upper($cur)='".$Base1."', DeliveryDt_CostBase1, if(upper($cur)='".$Base2."', DeliveryDt_CostBase2, DeliveryDt_Cost))*ifNull(deliverydt_quantity,0)) As TotalCost ".
					" from deliverydt ".
					" where deliverydt.delivery_id = ". $rowdelivery[9];
								
		$sub_result = mysql_query($sub_query,$connection) or die(mysql_error());
		$sub_row = mysql_fetch_row($sub_result);
		
	}										
	
	if (($grpproject == 1  && $grpclient == 0) && ($masterproject == '' || $masterproject!= $rowdelivery[5])) {
	  
		if (($cur != 'null') && ($masterproject != '' && $masterproject!= $rowdelivery[5]) && $ShowTotals == 1){
		
			//$RowNum += 1;
			$ColumnNum = 8;
		
			$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
			$worksheet->write($RowNum, $ColumnNum, "Total by group $displaycur", $grouplevel1Format);
			$ColumnNum += 2;
			$worksheet->writeNumber($RowNum, $ColumnNum, $SumGroupNetTot, $grouplevel1totalFormat);
			$ColumnNum += 1;
			
			$SumGroupNetTot = 0;
			
			//Increment the Rows Counter By 1 and Reset the Columns Counter
			$RowNum += 1;
			$ColumnNum = 0;
			
		}	
		
		$worksheet->write($RowNum, $ColumnNum, "Group by Project", $grouplevel1Format);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $rowdelivery[5]." - ".$rowdelivery[11], $grouplevel1Format);
		$ColumnNum += 1;
			
		$masterproject= $rowdelivery[5];
	  
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 2;
		$ColumnNum = 0;
	
	}
	
	if (($grpclient == 1) && ($masterclient == '' || $masterclient!= $rowdelivery[3])) {
	  
		if (($cur != 'null') && ($masterclient != '' && $masterclient!= $rowdelivery[3]) && $ShowTotals == 1){
		
			//$RowNum += 1;
			$ColumnNum = 8;
		
			$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
			$worksheet->write($RowNum, $ColumnNum, "Total by group $displaycur", $grouplevel1Format);
			$ColumnNum += 2;
			$worksheet->writeNumber($RowNum, $ColumnNum, $SumGroupNetTot, $grouplevel1totalFormat);
			$ColumnNum += 1;
			
			$SumGroupNetTot = 0;
			
			//Increment the Rows Counter By 1 and Reset the Columns Counter
			$RowNum += 1;
			$ColumnNum = 0;
			
		}	
		
		$worksheet->write($RowNum, $ColumnNum, "Group by Client", $grouplevel1Format);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $rowdelivery[3]." - ".$rowdelivery[4], $grouplevel1Format);
		$ColumnNum += 1;
			
		$masterclient= $rowdelivery[3];
	  
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 2;
		$ColumnNum = 0;
		
	}
	
	if($_SESSION['module']=="1" && ($transaction == 'DI' || $transaction == 'DR' )){
		$NetTotal = $sub_row[0];
	}	
	else { 	
		$NetTotal = $rowdelivery[8] + $rowdelivery[15] - $rowdelivery[17];
		//if ($pricettc == 0)
			$NetTotal += $rowdelivery[16];
	}	
		
	$worksheet->write($RowNum, $ColumnNum, $rowdelivery[0], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rowdelivery[1], $regularleftFormat);
	
	$ColumnNum += 1;
	
	if($_SESSION['module']=="0"){
		if ($rowdelivery[2] == 'DI')
			$worksheet->write($RowNum, $ColumnNum, "SI", $regularcenterFormat);
		else
			$worksheet->write($RowNum, $ColumnNum, "SR", $regularcenterFormat);
	}
    else
		$worksheet->write($RowNum, $ColumnNum, $rowdelivery[2], $regularcenterFormat);
      
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rowdelivery[12], $regularcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rowdelivery[13], $regularcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rowdelivery[3], $regularcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rowdelivery[4], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->writestring($RowNum, $ColumnNum, $rowdelivery[5], $regularcenterFormat);
	$ColumnNum += 1;
	if($showProjCostName == 1){
		$worksheet->writestring($RowNum, $ColumnNum, $rowdelivery[11], $regularcenterFormat);
		$ColumnNum += 1;
	}
		
	$worksheet->write($RowNum, $ColumnNum, $rowdelivery[6], $regularcenterFormat);
	$ColumnNum += 1;
	if($showProjCostName == 1){
		$worksheet->writestring($RowNum, $ColumnNum, $rowdelivery[18], $regularcenterFormat);
		$ColumnNum += 1;
	}
		$worksheet->write($RowNum, $ColumnNum, $rowdelivery[7], $regularcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, number_format($rowdelivery[8],$DecimalBase), $regularrightFormatNum);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, number_format($rowdelivery[15],$DecimalBase), $regularrightFormatNum);
	$GrossTot = $rowdelivery[15] + $rowdelivery[8];
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, number_format($GrossTot,$DecimalBase), $regularrightFormatNum);
	//if ($pricettc == 0){
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, number_format($rowdelivery[17],$DecimalBase), $regularrightFormatNum);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, number_format($rowdelivery[16],$DecimalBase), $regularrightFormatNum);
	/*}else{
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, number_format($rowdelivery[16],$DecimalBase), $regularrightFormatNum);
	}*/
	if($ShowTotals == 1) {
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, number_format($NetTotal,$DecimalBase), $regularrightFormatNum);
	}	
	
	if ($rowdelivery[14] != "") {
		$RowNum += 1;
		$ColumnNum = 1;
		
		$worksheet->write($RowNum, $ColumnNum, $rowdelivery[14], $regularleftFormat1);
		$ColumnNum += 1;
			
	}
	
	if ($rowdelivery[2] == 'SI' || $rowdelivery[2] == 'DI' || $showboth == 0) {
		$SumNetTot       = $SumNetTot  + $NetTotal;
		$SumGroupNetTot  = $SumGroupNetTot  + $NetTotal;
	} else {
		$SumNetTot       = $SumNetTot  - $NetTotal;
		$SumGroupNetTot  = $SumGroupNetTot  - $NetTotal;
	}

	
	if ($showdetail == 1) {
	    if($SalesReport==1)
		   $resultdeliverydt=mysql_query($querysalesdt,$connection) or die(mysql_error());
		else
           $resultdeliverydt=mysql_query($querydeliverydt,$connection) or die(mysql_error());
		   
		while($rowdeliverydt=mysql_fetch_row($resultdeliverydt)){
		
			$RowNum += 1;
			$ColumnNum = 0;
			
			//$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
			$worksheet->write($RowNum, $ColumnNum, $rowdeliverydt[0], $regularleftFormat1);
			$ColumnNum += 1;
			//$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
			$worksheet->write($RowNum, $ColumnNum, $rowdeliverydt[1], $regularleftFormat1);
			$ColumnNum += 5;
			$worksheet->write($RowNum, $ColumnNum, $rowdeliverydt[2], $regularleftFormat1);
			$ColumnNum += 1;
			$worksheet->writeNumber($RowNum, $ColumnNum, $rowdeliverydt[3], $regularrightFormat1Num);
			$ColumnNum += 1;
			$worksheet->writeNumber($RowNum, $ColumnNum, $rowdeliverydt[4], $regularrightFormat1Num);
			$ColumnNum += 1;
			$worksheet->writeNumber($RowNum, $ColumnNum, $rowdeliverydt[5], $regularrightFormat1Num);
			$ColumnNum += 1;
			if($showProjCostName == 1){
				$worksheet->writestring($RowNum, $ColumnNum, $rowdelivery[11], $regularcenterFormat);
				$ColumnNum += 1;
			}
			$worksheet->writeNumber($RowNum, $ColumnNum, $rowdeliverydt[6], $regularrightFormat1Num);
			$ColumnNum += 1;
			if($showProjCostName == 1){
				$worksheet->writestring($RowNum, $ColumnNum, $rowdelivery[18], $regularcenterFormat);
				$ColumnNum += 1;
			}
			if ($ShowCost == 1) {
				$worksheet->writeNumber($RowNum, $ColumnNum, $rowdeliverydt[7], $regularrightFormat1Num);
				$ColumnNum += 1;
			}
			
		}
	}	
		
	//Increment the Rows Counter By 1 and Reset the Columns Counter
	$RowNum += 1;
	$ColumnNum = 0;
	$SumTaxable += $rowdelivery[8];
	$SumNonTaxable += $rowdelivery[15];
	$SumDisc += $rowdelivery[17];
	//if ($pricettc == 0)
		$SumVat += $rowdelivery[16];
	$SumGrossTot += $GrossTot;
}

$RowNum += 1;
$worksheet->write($RowNum, 10,"Taxable: ".number_format($SumTaxable,$DecimalBase), $regularrightFormat1Num);
$worksheet->write($RowNum, 11, "NonTaxable: ".number_format($SumNonTaxable,$DecimalBase), $regularrightFormat1Num);
$worksheet->write($RowNum, 12, "Gross Tot.: ".number_format($SumGrossTot,$DecimalBase), $regularrightFormat1Num);
//if ($pricettc == 0){
	$worksheet->write($RowNum, 13, "Disc.: ".number_format($SumDisc,$DecimalBase), $regularrightFormat1Num);
	$worksheet->write($RowNum, 14, "VAT: ".number_format($SumVat,$DecimalBase), $regularrightFormat1Num);
// }else
//	$worksheet->write($RowNum, 13, "Disc: ".number_format($SumDisc,$DecimalBase), $regularrightFormat1Num);


if (($grpproject == 1  && $grpclient == 0 ) && ($masterproject == '' || $masterproject!= $rowdelivery[5])) {
	  
    if (($cur != 'null') && ($masterproject != '' ) && $ShowTotals == 1){
	
		$RowNum += 1;
		$ColumnNum = 13;
		
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
		$worksheet->write($RowNum, $ColumnNum, "Total by group $displaycur", $grouplevel1Format);
		$ColumnNum += 2;
		$worksheet->writeNumber($RowNum, $ColumnNum, $SumGroupNetTot, $grouplevel1totalFormat);
		$ColumnNum += 1;
			
		$SumGroupNetTot = 0;
			
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 1;
		$ColumnNum = 0;
			
	}	
}

if (($grpclient == 1 ) && ($masterclient == '' || $masterclient!= $rowdelivery[3])) {
	  
	if (($cur != 'null') && ($masterclient != '' ) && $ShowTotals == 1){
	
		//$RowNum += 1;
		$ColumnNum = 13;
		
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
		$worksheet->write($RowNum, $ColumnNum, "Total by group $displaycur", $grouplevel1Format);
		$ColumnNum += 2;
		$worksheet->writeNumber($RowNum, $ColumnNum, number_format($SumGroupNetTot,$DecimalBase), $grouplevel1totalFormat);
		$ColumnNum += 1;
			
		$SumGroupNetTot = 0;
			
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 1;
		$ColumnNum = 0;
	}	
	  
}
	
if ($cur != 'null' && $ShowTotals == 1){
	$RowNum += 1;
	$ColumnNum = 13;
	
	$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
	$worksheet->write($RowNum, $ColumnNum, "Total ".$displaycur, $boldrightFormat);
	$ColumnNum += 2;
	$worksheet->write($RowNum, $ColumnNum, number_format($SumNetTot,$DecimalBase), $boldrightFormatNum);
	$ColumnNum += 1;
	
}



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


?>