<?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 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);
} 

$querysalessetup="select ifNull(Sale_MarginPer,0) As Sale_MarginPer from salessetup";
$resultsalessetup=mysql_query($querysalessetup,$connection) or die(mysql_error());
while($rowssalessetup=mysql_fetch_array($resultsalessetup)){
  extract($rowssalessetup);
}

$querylog=" select ifNull(FI_RepRestrictProject,0) As FI_RepRestrictProject, ifNull(FI_RepRestrictCostcenter,0) As FI_RepRestrictCostcenter, ".
		  " ifNull(FI_RepRestrictWarehouse,0) As FI_RepRestrictWarehouse, ifNull(FI_ShowCostPrice,0) As FI_ShowCostPrice ".
          " 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);
}

//Read From Module Table
$querymodule=" Select ifNull(Module_Delivery,0) As Module_Delivery From Module ";
$resultmodule=mysql_query($querymodule,$connection) or die(mysql_error());
while($rowmodule=mysql_fetch_array($resultmodule)){
	extract($rowmodule);
}

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

$querywh=" select distinct warehouse.warehouse_code, warehouse_name ".
         " from warehouse where warehouse_code in ( select warehouse_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk ".
		 "                                          where user_account='".$HTTP_SESSION_VARS['useraccount']."' ".
		 "                                          and (loginstk.warehouse_code is not null and loginstk.warehouse_code != '') ) ".
		 " order by warehouse.warehouse_code";
$resultwh = mysql_query($querywh,$connection) or die(mysql_error());
$whexist  = mysql_num_rows($resultwh);


$querygroup=" select distinct group_code,group_name ".
			" from grouping where group_code in (select group_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."') ".
			" order by group_code";
$resultgroup = mysql_query($querygroup,$connection) or die(mysql_error());
$groupexist  = mysql_num_rows($resultgroup);

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

$ReportName = 'Sales Report Statistics';

//Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();
 
//Sending HTTP headers
$workbook->send('Exp_SalesReportStatistics.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('Sales Report Statistics');
$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'  ));  
  
$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'=>10,
  'align'=>'right'  ,
  'bold'=>1
  )); 
$boldcenterFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'center'  ,
  'valign'=>'top'  ,
  'bold'=>1
  ));      
  
$groupleftFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'left',
  'bold'=>1,
  'color'=>0x0C
  )); 
$groupcenterFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'center',
  'bold'=>1,
  'color'=>0x0C
  ));   
$grouprightFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right',
  'bold'=>1,
  'color'=>0x0C
  ));  
  
$totalFormat1 =& $workbook->addFormat(array('size'=>11,
  'align'=>'right',
  'bold'=>1,
  'color'=>0x0C
  ));    
$totalFormat2 =& $workbook->addFormat(array('size'=>11,
  'align'=>'right',
  'bold'=>1,
  'color'=>0x0A
  ));    
$totalFormat3 =& $workbook->addFormat(array('size'=>11,
  'align'=>'right',
  'bold'=>1,
  'color'=>0x11
  ));      
  
$subtotalFormat1 =& $workbook->addFormat(array('size'=>10,
  'align'=>'right',
  'italic'=>1,
  'bold'=>1,
  'color'=>0x0C
  ));    
$subtotalFormat2 =& $workbook->addFormat(array('size'=>10,
  'align'=>'right',
  'italic'=>1,
  'bold'=>1,
  'color'=>0x0A
  ));    
$subtotalFormat3 =& $workbook->addFormat(array('size'=>10,
  'align'=>'right',
  'italic'=>1,
  'bold'=>1,
  'color'=>0x11
  ));        

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

//Display Report Header
$worksheet->write(1,0,$CompanyName, $TitleFormat);
$worksheet->write(1,2,$ReportName,  $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);
				


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


$subproj 	 = ereg_replace('-','%',$subproj);
$subcostcent = ereg_replace('-','%',$subcostcent);

$SelectedOptions = "Filter by: \n";
if ($fromdate != '')      $SelectedOptions .= "-> Date From: ". $fromdate . "  To: ". $todate ."\n";	
if ($displaycur != '')    $SelectedOptions .= "-> Currency: ". $displaycur ."\n";	
if ($fromitem != '')      $SelectedOptions .= "-> Item From: ". $fromitem . "  To: ". $toitem ."\n";
if ($fromgroup != '')     $SelectedOptions .= "-> Group From: ". $fromgroup . "  To: ". $togroup ."\n";
if ($fromsubgroup != '')  $SelectedOptions .= "-> SubGroup From: ". $fromsubgroup . "  To: ". $tosubgroup ."\n";
if ($fromproject != '')   $SelectedOptions .= "-> Project From: ". $fromproject . "\n";
if ($fromcostcent != '')  $SelectedOptions .= "-> Costcent From: ". $fromcostcent . "\n";
if ($fromclient != '')    $SelectedOptions .= "-> Client From: ". $fromclient . "  To: ". $toclient ."\n";
if ($fromsupplier != '')  $SelectedOptions .= "-> Supplier From: ". $fromsupplier . "  To: ". $tosupplier ."\n";
if ($fromsalesman != '')  $SelectedOptions .= "-> Salesman From: ". $fromsalesman . "  To: ". $tosalesman ."\n";
if ($fromwarehouse != '') $SelectedOptions .= "-> Warehouse From: ". $fromwarehouse . "\n";
if ($fromarea != '') 	  $SelectedOptions .= "-> Area From: ". $fromarea . "\n";
if ($subproj != '') 	  $SelectedOptions .= "-> Project Include: ". $subproj . "<br>";
if ($subcostcent != '')	  $SelectedOptions .= "-> Costcent Include: ". $subcostcent . "<br>";

if (!empty($fromdate)) $fromdate = explodeword($fromdate);
if (!empty($todate))   $todate = explodeword($todate);  
      
$fromitem      = GetSQLValueString($fromitem, 'text');
$toitem        = GetSQLValueString($toitem, 'text');
$fromproject   = GetSQLValueString($fromproject, 'text');
$fromcostcent  = GetSQLValueString($fromcostcent, 'text');
$fromgroup     = GetSQLValueString($fromgroup, 'text');
$togroup       = GetSQLValueString($togroup, 'text');
$fromsubgroup  = GetSQLValueString($fromsubgroup, 'text');
$tosubgroup    = GetSQLValueString($tosubgroup, 'text');
$fromwarehouse = GetSQLValueString($fromwarehouse, 'text');
$fromarea      = GetSQLValueString($fromarea, 'text');
$fromclient    = GetSQLValueString($fromclient, 'text');
$toclient      = GetSQLValueString($toclient, 'text');
$fromsupplier  = GetSQLValueString($fromsupplier, 'text');
$tosupplier    = GetSQLValueString($tosupplier, 'text');
$fromsalesman  = GetSQLValueString($fromsalesman, 'text');
$tosalesman    = GetSQLValueString($tosalesman, 'text');
$fromdate  	   = GetSQLValueString($fromdate, 'date');
$todate        = GetSQLValueString($todate, 'date');   
$subproj 	   = GetSQLValueString($subproj, 'text');
$subcostcent   = GetSQLValueString($subcostcent, 'text'); 
   
   
	

//Displaying Selected Options	
// Merge cells from row 3, col 0 to row 3, col 4
$worksheet->setMerge(3, 0, 8, 4);
$worksheet->write(3,0,$SelectedOptions, $seloptionFormat);  




// Delivery Query

$result = mysql_query("start transaction", $connection);
$resultvars = mysql_query("set @TotalQty := 0.00, @TotalAvgCM := 0.00, @ItemsCounter := 0 ", $connection);

$querydelivery = '';

$querydelivery = $querydelivery."   Select ItemCode, ItemDescription, Qty, SellingPrice, MeanCost, ContributionMargin, ".
								"   AverageCM, SumQty, SumAvgCM, MenuMix, TotalAvgCM, ItemsCounter, AvgMenuMix, ".
								"   Popularity, Profitability, ".
								"   if(Popularity='high' and Profitability='high', 'star', if(Popularity='high' and Profitability='low', 'plow horse',    ".
								"   if(Popularity='low' and Profitability='low', 'dog', 'puzzle'))) As Classification ";

$querydelivery = $querydelivery." From ( ";

$querydelivery = $querydelivery."   Select ItemCode, ItemDescription, Qty, SellingPrice, MeanCost, ContributionMargin, ".
								"   AverageCM, @TotalQty As SumQty, @TotalAvgCM As SumAvgCM, ((Qty/@TotalQty)*100) As MenuMix, ".
								"   (@TotalAvgCM/@TotalQty) As TotalAvgCM, @ItemsCounter As ItemsCounter,  (100/@ItemsCounter) As AvgMenuMix, ".
								"   if (((Qty/@TotalQty)*100) > ($Sale_MarginPer/100)*(100/@ItemsCounter), 'high', 'low') As Popularity, ".
								"   if (ContributionMargin > (@TotalAvgCM/@TotalQty), 'high', 'low') As Profitability ";

$querydelivery = $querydelivery." From ( ";
$querydelivery = $querydelivery."   Select ItemCode, ItemDescription, ifNull(QtySold, 0) AS Qty, ifNull(SellingPrice,0) AS SellingPrice, ".
								"   ifNull(MeanCostAmount,0) As MeanCost, (ifNull(SellingPrice,0) - ifNull(MeanCostAmount,0)) As ContributionMargin, ".
								"   (ifNull(QtySold, 0) * (ifNull(SellingPrice,0) - ifNull(MeanCostAmount,0))) As AverageCM, ".
								"   (@TotalQty := @TotalQty + ifNull(QtySold, 0)), ".
								"   (@TotalAvgCM := @TotalAvgCM + (ifNull(QtySold, 0) * (ifNull(SellingPrice,0) - ifNull(MeanCostAmount,0)))), ".
								"   (@ItemsCounter := @ItemsCounter + 1) ";
	
$querydelivery = $querydelivery.
				" From ( ";
				
if($Module_Delivery == 0 ) {	
	$querydelivery = $querydelivery.
					" Select DeliveryDt.Item_Code As ItemCode, Items.Item_Description As ItemDescription, ".
					" Sum(if(DeliveryDt.Journal_Type = 'DI',DeliveryDt.DeliveryDt_Quantity, (-1)*DeliveryDt.DeliveryDt_Quantity)) As QtySold,  ".
					" Deliveries.Currency_Code As Currency, ".
					" if(upper($curr)='".$Base1."', ifNull(Items.Item_SellingPrice1Base1,0), if(upper($curr)='".$Base2."', ifNull(Items.Item_SellingPrice1Base2,0), ifNull(convertcurfunc(Items.Currency_Code, Deliveries.Currency_Code, Deliveries.Delivery_Date, 0, 0, '', '', ifNull(ifNull(Item_SellingPrice1,0),0)),0))) As SellingPrice, ".
					" ifNull(MeanCostCur(DeliveryDt.Item_Code, concat(date_format(Deliveries.Delivery_Date,'%Y-%m-%d'), ' 23:59:59'), if(upper($curr)='".$Base1."', 1, if(upper($curr)='".$Base2."',2, 1)), Deliveries.Currency_Code, if(upper($curr)='".$Base1."', '',if(upper($curr)='".$Base2."','',items.currency_code ))),0) As MeanCostAmount ".
					" From DeliveryDt As DeliveryDt  ".
					" Inner Join Deliveries As Deliveries on DeliveryDt.Delivery_ID = Deliveries.Delivery_ID ".
					" Inner Join Items on DeliveryDt.Item_Code = Items.Item_Code ";
											
									  
	if ($projectexist >	0 && $FI_RepRestrictProject == 1)
		$querydelivery = $querydelivery." 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 != '') ".
										"			 ) 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 ".$HTTP_SESSION_VARS['dbLabel'].".loginstk ".
										"			   where loginstk.user_account='".$HTTP_SESSION_VARS['useraccount']."' ".
										"              and (costcent_code is not null and costcent_code != '') ".
										"			 ) Q2 on Deliveries.Costcent_Code like concat(Q2.costcent_code,'%')  ";							 
									   

	$querydelivery= $querydelivery. " Where DeliveryDt.Journal_Type in ('DI','DR') ".
									" And (Deliveries.Delivery_Reference Is Not Null And Deliveries.Delivery_Reference != '')  ".
									" 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 DeliveryDt.item_code between ifNull(".$fromitem.",DeliveryDt.item_code) and ifNull(". $toitem.",DeliveryDt.item_code) ".
									" And ifNull(Items.group_code,'-1') between ifNull(". $fromgroup .", ifNull(Items.group_code,'-1')) and ifNull(". $togroup.", ifNull(Items.group_code,'-1'))  ".
									" And ifNull(Items.SubGroup_Code,'-1') between ifNull(". $fromsubgroup .",ifNull(Items.SubGroup_Code,'-1')) and ifNull(". $tosubgroup.",ifNull(Items.SubGroup_Code,'-1'))  ".
									" And ifNull(Deliveries.Client_Code,'-1') between ifNull(".$fromclient.",ifNull(Deliveries.Client_Code,'-1')) and ifNull(". $toclient.",ifNull(Deliveries.Client_Code,'-1')) ".
									" And ifNull(Deliveries.Salesman_Code,'-1') between ifNull(".$fromsalesman.",ifNull(Deliveries.Salesman_Code,'-1')) and ifNull(". $tosalesman.",ifNull(Deliveries.Salesman_Code,'-1')) ".
									" And ifNull(Items.Ledger_Number,'-1') between ifNull(".$fromsupplier.", ifNull(Items.Ledger_Number,'-1')) and ifNull(". $tosupplier.", ifNull(Items.Ledger_Number,'-1')) ".
									" And ifNull(Deliveries.project_code,'-1')   Like Concat(ifNull(".$fromproject.",ifNull(Deliveries.project_code,'-1')),'%')  ".
									" And ifNull(Deliveries.Costcent_Code,'-1')  Like Concat(ifNull(".$fromcostcent.",ifNull(Deliveries.Costcent_Code,'-1')),'%')  ".
									" And ifNull(DeliveryDt.warehouse_code,'-1') Like Concat(ifNull(". $fromwarehouse .",ifNull(DeliveryDt.warehouse_code,'-1')),'%')  ".
									" And (($promprint=1 and ifNull(DeliveryDt_Promotion,0) = 1) Or ($promprint=2 and ifNull(DeliveryDt_Promotion,0) = 0) Or ($promprint=3 and 1 = 1))  ";

			
	if ($subproj != 'Null')
		$querydelivery= $querydelivery." And ifNull(Deliveries.project_code,'-1')  Like ".$subproj; 
	if ($subcostcent != 'Null')
		$querydelivery= $querydelivery." And ifNull(Deliveries.Costcent_Code,'-1')  Like ".$subcostcent;  
				
	if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
		$querydelivery= $querydelivery." And DeliveryDt.warehouse_code In (select distinct warehouse_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  
	if ($groupexist > 0)
		$querydelivery= $querydelivery." And Items.group_code In (select distinct group_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  
	
	$querydelivery = $querydelivery." Group By ItemCode ";
	
} else {
		
	$querydelivery = $querydelivery.
					" Select SalesDt.Item_Code As ItemCode, Items.Item_Description As ItemDescription, ".
					" Sum(if(SalesDt.Journal_Type = 'SI',SalesDt.SaleDt_Quantity, (-1)*SalesDt.SaleDt_Quantity)) As QtySold,  ".
					" Sales.Currency_Code As Currency, ".
					" if(upper($curr)='".$Base1."', ifNull(Items.Item_SellingPrice1Base1,0), if(upper($curr)='".$Base2."', ifNull(Items.Item_SellingPrice1Base2,0), ifNull(convertcurfunc(Items.Currency_Code, Sales.Currency_Code, Sales.Sale_Date, 0, 0, '', '', ifNull(ifNull(Item_SellingPrice1,0),0)),0))) As SellingPrice, ".
					" ifNull(MeanCostCur(SalesDt.Item_Code, concat(date_format(Sales.Sale_Date,'%Y-%m-%d'), ' 23:59:59'), if(upper($curr)='".$Base1."', 1, if(upper($curr)='".$Base2."',2, 1)), Sales.Currency_Code, if(upper($curr)='".$Base1."', '',if(upper($curr)='".$Base2."','',items.currency_code ))),0) As MeanCostAmount ".
					" From SalesDt As SalesDt  ".
					" Inner Join Sales As Sales on SalesDt.Sale_ID = Sales.Sale_ID ".
					" Inner Join Items on SalesDt.Item_Code = Items.Item_Code ";
											
	if ($projectexist >	0 && $FI_RepRestrictProject == 1)
		$querydelivery = $querydelivery." 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 != '') ".
										"			 ) Q1 on Sales.project_code like concat(Q1.project_code,'%')  ";
										 
	if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
		$querydelivery = $querydelivery." 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 != '') ".
										"			 ) Q2 on Sales.Costcenter_Code like concat(Q2.costcent_code,'%')  ";							 
									   

	$querydelivery= $querydelivery. " Where SalesDt.Journal_Type in ('SI','SR') ".
									" And (Sales.Sale_Reference Is Not Null And Sales.Sale_Reference != '')  ".
									" And Date_format(Sales.Sale_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(Sales.Sale_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Sales.Sale_Date,'%Y-%m-%d'))  ".
									" And SalesDt.item_code between ifNull(".$fromitem.",SalesDt.item_code) and ifNull(". $toitem.",SalesDt.item_code) ".
									" And ifNull(Items.group_code,'-1') between ifNull(". $fromgroup .", ifNull(Items.group_code,'-1')) and ifNull(". $togroup.", ifNull(Items.group_code,'-1'))  ".
									" And ifNull(Items.SubGroup_Code,'-1') between ifNull(". $fromsubgroup .",ifNull(Items.SubGroup_Code,'-1')) and ifNull(". $tosubgroup.",ifNull(Items.SubGroup_Code,'-1'))  ".
									" And ifNull(Sales.Client_Code,'-1') between ifNull(".$fromclient.",ifNull(Sales.Client_Code,'-1')) and ifNull(". $toclient.",ifNull(Sales.Client_Code,'-1')) ".
									" And ifNull(Sales.Salesman_Code,'-1') between ifNull(".$fromsalesman.",ifNull(Sales.Salesman_Code,'-1')) and ifNull(". $tosalesman.",ifNull(Sales.Salesman_Code,'-1')) ".
									" And ifNull(Items.Ledger_Number,'-1') between ifNull(".$fromsupplier.", ifNull(Items.Ledger_Number,'-1')) and ifNull(". $tosupplier.", ifNull(Items.Ledger_Number,'-1')) ".
									" And ifNull(Sales.project_code,'-1')   Like Concat(ifNull(".$fromproject.",ifNull(Sales.project_code,'-1')),'%')  ".
									" And ifNull(Sales.Costcenter_Code,'-1')  Like Concat(ifNull(".$fromcostcent.",ifNull(Sales.Costcenter_Code,'-1')),'%')  ".
									" And ifNull(SalesDt.warehouse_code,'-1') Like Concat(ifNull(". $fromwarehouse .",ifNull(SalesDt.warehouse_code,'-1')),'%')  ".
									//" And ifNull(Clients.area_code,'-1') = ifNull(". $fromarea .",ifNull(Clients.area_code,'-1'))  ".
									" And (($promprint=1 and ifNull(SaleDt_Promotion,0) = 1) Or ($promprint=2 and ifNull(SaleDt_Promotion,0) = 0) Or ($promprint=3 and 1 = 1))  ";

	if ($subproj != 'Null')
		$querydelivery= $querydelivery." And ifNull(Sales.project_code,'-1')  Like ".$subproj;  
	if ($subcostcent != 'Null')
		$querydelivery= $querydelivery." And ifNull(Sales.Costcenter_code,'-1')  Like ".$subcostcent;  						  
				
	if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
		$querydelivery= $querydelivery." And SalesDt.warehouse_code In (select distinct warehouse_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  
	if ($groupexist > 0)
		$querydelivery= $querydelivery." And Items.group_code In (select distinct group_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  
			
	$querydelivery = $querydelivery." Group By ItemCode ";
	
}
	

$querydelivery = $querydelivery."    ) Q1 ";
$querydelivery = $querydelivery."   Order By ItemCode, ItemDescription ";
$querydelivery = $querydelivery." ) Q2 ";
$querydelivery = $querydelivery." ) Q3 ";

	
//echo $querydelivery; exit;


$i = 1;

$RowNum    = 11;
$ColumnNum = 0;
	
$result = mysql_query($querydelivery,$connection) or die(mysql_error());
while($rows=mysql_fetch_row($result)){

	if ($i == 1) {
								
		$worksheet->write($RowNum,$ColumnNum,"Total Quantity Sold", $boldleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum,$ColumnNum,number_format($rows[7], $DecimalBase), $boldrightFormat);
		$ColumnNum += 1;
		$RowNum   += 1;
		$ColumnNum = 0;
		
		$worksheet->write($RowNum,$ColumnNum,"Total Average CM", $boldleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum,$ColumnNum,number_format($rows[8], $DecimalBase), $boldrightFormat);
		$ColumnNum += 1;
		$RowNum   += 1;
		$ColumnNum = 0;
		
		$worksheet->write($RowNum,$ColumnNum,"Average CM", $boldleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum,$ColumnNum,number_format($rows[10], $DecimalBase), $boldrightFormat);
		$ColumnNum += 1;
		$RowNum   += 1;
		$ColumnNum = 0;
		
		$worksheet->write($RowNum,$ColumnNum,"Average Menu Mix", $boldleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum,$ColumnNum,number_format($rows[12], $DecimalBase), $boldrightFormat);
		$ColumnNum += 1;

		$RowNum   += 2;
		$ColumnNum = 0;
		
		//Display Report Header
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
		$worksheet->write($RowNum,$ColumnNum,"Item Code", $boldleftFormat);
		$ColumnNum += 1;
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 35);
		$worksheet->write($RowNum,$ColumnNum,"Item Description", $boldleftFormat);
		$ColumnNum += 1;
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
		$worksheet->write($RowNum,$ColumnNum,"Unit Cost ".$displaycur, $boldcenterFormat);
		$ColumnNum += 1;
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
		$worksheet->write($RowNum,$ColumnNum,"Unit Price ".$displaycur, $boldcenterFormat);
		$ColumnNum += 1;
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
		$worksheet->write($RowNum,$ColumnNum,"Quantity Sold", $boldcenterFormat);
		$ColumnNum += 1;
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 17);
		$worksheet->write($RowNum,$ColumnNum,"Contibution Margin", $boldcenterFormat);
		$ColumnNum += 1;
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
		$worksheet->write($RowNum,$ColumnNum,"Menu Mix (%)", $boldcenterFormat);
		$ColumnNum += 1;
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
		$worksheet->write($RowNum,$ColumnNum,"Average CM", $boldcenterFormat);
		$ColumnNum += 1;
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
		$worksheet->write($RowNum,$ColumnNum,"Popularity", $boldcenterFormat);
		$ColumnNum += 1;
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
		$worksheet->write($RowNum,$ColumnNum,"Profitability", $boldcenterFormat);
		$ColumnNum += 1;
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
		$worksheet->write($RowNum,$ColumnNum,"Classification", $boldcenterFormat);
		$ColumnNum += 1;
		
		
		$RowNum   += 1;
		$ColumnNum = 0;
	}	

	$worksheet->write($RowNum, $ColumnNum, $rows[0], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[1], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,number_format($rows[4], $DecimalBase), $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,number_format($rows[3], $DecimalBase), $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,number_format($rows[2], $DecimalBase), $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,number_format($rows[5], $DecimalBase), $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,$rows[9], $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,number_format($rows[6], $DecimalBase), $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[13], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[14], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[15], $regularleftFormat);
	$ColumnNum += 1;
	
	$i += 1;
	
	//Increment the Rows Counter By 1 and Reset the Columns Counter
	$RowNum   += 1;
	$ColumnNum = 0;
}   




$result = mysql_query("rollback", $connection);


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

?>