<?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, ".
			" ifnull(Setup_Tax1Active,0) as Setup_Tax1Active, ifnull(Setup_Tax2Active,0) as Setup_Tax2Active,  ".
			" ifnull(Setup_Tax3Active,0) as Setup_Tax3Active, ifnull(Setup_Tax4Active,0) as Setup_Tax4Active,  ".
			" ifnull(Setup_Tax5Active,0) as Setup_Tax5Active, Setup_Tax1Name, Setup_Tax2Name, Setup_Tax3Name, ".
			" Setup_Tax4Name, Setup_Tax5Name ".
			" 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, ifNull(FI_ShowCostPrice,0) As FI_ShowCostPrice ".
          " from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' and Upper(Login_Schema) = Upper('".$_SESSION['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, ifNull(Module_AdditionalTaxes,0) As Module_AdditionalTaxes 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 ".$_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);		

$querywh=" select distinct warehouse.warehouse_code, warehouse_name ".
         " from warehouse where warehouse_code in ( select warehouse_code from ".$_SESSION['dbLabel'].".loginstk ".
		 "                                          where user_account='".$_SESSION['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 ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['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]);


//Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();
 
//Sending HTTP headers
$workbook->send('Exp_YearlySales.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('Yearly Sales');
$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,
  ));
  
//Elie format

$regularrightFormatNum1 =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ));

$regularleftFormatNum1 =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ));  


$grouplevel1FormatNum1 =& $workbook->addFormat(array('size'=>10,
  'align'=>'left',
  'bold'=>1,
  'color'=>0x0C
  )); 
  

$grouplevel2FormatNum1 =& $workbook->addFormat(array('size'=>10,
  'align'=>'left',
  'bold'=>0,
  'color'=>0x0A
  )); 
  
  
$grouplevel3FormatNum1 =& $workbook->addFormat(array('size'=>9,
  'align'=>'left',
  'italic'=>1,
  'color'=>0x11
  ));   
$grouplevel1totalFormatNum1 =& $workbook->addFormat(array('size'=>10,
  'align'=>'right',
  'bold'=>1,
  'color'=>0x0C
  ));  
$grouplevel2totalFormatNum1 =& $workbook->addFormat(array('size'=>10,
  'align'=>'right',
  'bold'=>0,
  'color'=>0x0A
  ));  
$grouplevel3totalFormatNum1 =& $workbook->addFormat(array('size'=>9,
  'align'=>'right',
  'italic'=>1,
  'color'=>0x11
  ));   
  
$boldleftFormatNum1 =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ,
  'valign'=>'top'  ,
  'bold'=>1
  ));  
$boldrightFormatNum1 =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ,
  'bold'=>1
  ));   
//new format
$regularleftFormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$regularrightFormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$grouplevel1FormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$grouplevel2FormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');  
$grouplevel3FormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$grouplevel1totalFormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$grouplevel2totalFormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$grouplevel3totalFormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$boldleftFormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$boldrightFormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');

//elie Format 
 
 
  
// Regular Format
$regularleftFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ));
$regularrightFormat =& $workbook->addFormat(array('size'=>9,
  '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
  )); 
$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();
   	
$worksheet->setColumn (0, 0, 20); 
$worksheet->setColumn (1, 1, 40); 
$worksheet->setColumn (2, 200, 12); 
//$worksheet->setRow (3, 100);

//Display Report Header
$worksheet->write(1,0,$CompanyName, $TitleFormat);
$worksheet->write(1,2,"Yearly Sales Report", $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."'";
}   

//If Option Daily, and Days differnce between Start and End dates > 30
//Then Set the EndDate equal to StartDate plus 30 days.
$Days = dateDiff(explodeword($fromdate), explodeword($todate))+1;
if ($Days == 30) $todate = addDays(explodeword($fromdate), 29, 'd/m/Y');
if ($Days == 31) $todate = addDays(explodeword($fromdate), 30, 'd/m/Y');

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

$SelectedOptions = "Filter by: \n";
if ($frommonth != '')     $SelectedOptions .= "-> Period From: ". getMonthDisp($frommonth) ." ". $fromyear . "  To: ". getMonthDisp($tomonth) ." ". $toyear ."\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 . "\n";
if ($subcostcent != '')	  $SelectedOptions .= "-> Costcent Include: ". $subcostcent . "\n";
if ($frombustype != '')   $SelectedOptions .= "-> Client Business Type From: ". $frombustype . "  To: ". $tobustype ."\n";

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'); 
$frombustype   = GetSQLValueString($frombustype, 'text');
$tobustype     = GetSQLValueString($tobustype, 'text');
   
if(empty($promprint))    $promprint = 3;
if(empty($showweight))   $showweight = 0;
if(empty($showsales))    $showsales = 0;
if(empty($showretsales)) $showretsales = 0;

if(empty($activeclt))  $activeclt = 1;
if(empty($activeitem)) $activeitem = 1;
	
$groupby1name = '';
$groupby2name = '';   
$groupby3name = '';
$QryGroupby1Code = '';
$QryGroupby2Code = '';
$QryGroupby3Code = '';
$QryGroupby1Name = '';
$QryGroupby2Name = '';
$QryGroupby3Name = '';

if ($groupby3 != 1 ) {
	$pairs = explode(",", $groupby3);
	
	$vCount = count($pairs);
	if ($vCount > 1) {
		$QryGroupby3Code = 'Concat( ';
		$QryGroupby3Name = 'Concat( ';
	}	
	foreach ($pairs as $pair) {
		
		if ($groupby3name != '') {
			$groupby3name .= " & ";
			$QryGroupby3Code .= ", '*', ";
			$QryGroupby3Name .= ", '*', ";
		}	
			
		
		if ($pair == 2 ) {
			$groupby3name    .= 'Supplier';
			$QryGroupby3Code .= 'ifNull(SupplierCode,"None")';
			$QryGroupby3Name .= 'ifNull(SupplierName,"None")';
		}	
		else if ($pair == 3 ) {	
			$groupby3name    .= 'Salesman';
			$QryGroupby3Code .= 'ifNull(SalesmanCode,"None")';
			$QryGroupby3Name .= 'ifNull(SalesmanName,"None")';
		}
		else if ($pair == 20 ) {	
			$groupby3name    .= 'Transaction Project(Parent)';
			$QryGroupby3Code .= 'ifNull(ParentProjectCode,"None")';
			$QryGroupby3Name .= 'ifNull(ParentProjectName,"None")';
		}
		else if ($pair == 4 ) {	
			$groupby3name    .= 'Transaction Project(Detail)';
			$QryGroupby3Code .= 'ifNull(ProjectCode,"None")';
			$QryGroupby3Name .= 'ifNull(ProjectName,"None")';
		}
		else if ($pair == 21 ) {	
			$groupby3name 	 .= 'Archieve Project(Parent)';
			$QryGroupby3Code .= 'ifNull(ParentMasterProject,"None")';
			$QryGroupby3Name .= 'ifNull(ParentMasterProject,"None")';
		}
		else if ($pair == 5 ) {	
			$groupby3name 	 .= 'Archieve Project(Detail)';
			$QryGroupby3Code .= 'ifNull(MasterProject,"None")';
			$QryGroupby3Name .= 'ifNull(MasterProject,"None")';
		}
		else if ($pair == 22 ) {	
			$groupby3name    .= 'Transaction Costcenter(Parent)';	
			$QryGroupby3Code .= 'ifNull(ParentCostcenterCode,"None")';
			$QryGroupby3Name .= 'ifNull(ParentCostcenterName,"None")';
		}
		else if ($pair == 6 ) {	
			$groupby3name    .= 'Transaction Costcenter(Detail)';	
			$QryGroupby3Code .= 'ifNull(CostcenterCode,"None")';
			$QryGroupby3Name .= 'ifNull(CostcenterName,"None")';
		}
		else if ($pair == 23 ) {
			$groupby3name 	 .= 'Archieve Costcenter(Parent)';	
			$QryGroupby3Code .= 'ifNull(ParentMasterCostcent,"None")';
			$QryGroupby3Name .= 'ifNull(ParentMasterCostcent,"None")';
		}
		else if ($pair == 7 ) {
			$groupby3name 	 .= 'Archieve Costcenter(Detail)';	
			$QryGroupby3Code .= 'ifNull(MasterCostcent,"None")';
			$QryGroupby3Name .= 'ifNull(MasterCostcent,"None")';
		}
		else if ($pair == 24 ) { 	
			$groupby3name	 .= 'Warehouse(Parent)';	
			$QryGroupby3Code .= 'ifNull(ParentWarehouseCode,"None")';
			$QryGroupby3Name .= 'ifNull(ParentWarehouseCode,"None")';
		}
		else if ($pair == 8 ) { 	
			$groupby3name	 .= 'Warehouse(Detail)';	
			$QryGroupby3Code .= 'ifNull(WarehouseCode,"None")';
			$QryGroupby3Name .= 'ifNull(WarehouseCode,"None")';
		}
		else if ($pair == 9 ) {	
			$groupby3name    .= 'Business Type';
			$QryGroupby3Code .= 'ifNull(BusinessType,"None")';
			$QryGroupby3Name .= 'ifNull(BusinessType,"None")';
		}
		else if ($pair == 25 )	{
			$groupby3name    .= 'Group(Parent)';
			$QryGroupby3Code .= 'ifNull(ParentGroupCode,"None")';
			$QryGroupby3Name .= 'ifNull(ParentGroupName,"None")';
		}
		else if ($pair == 10 )	{
			$groupby3name    .= 'Group(Detail)';
			$QryGroupby3Code .= 'ifNull(GroupCode,"None")';
			$QryGroupby3Name .= 'ifNull(GroupName,"None")';
		}
		else if ($pair == 26 )	{
			$groupby3name 	 .= 'Subgroup(Parent)';
			$QryGroupby3Code .= 'ifNull(ParentSubGroupCode,"None")';
			$QryGroupby3Name .= 'ifNull(ParentSubGroupName,"None")';
		}
		else if ($pair == 11 )	{
			$groupby3name 	 .= 'Subgroup(Detail)';
			$QryGroupby3Code .= 'ifNull(SubGroupCode,"None")';
			$QryGroupby3Name .= 'ifNull(SubGroupName,"None")';
		}
		else if ($pair == 12 ) {	
			$groupby3name 	.= 'Category';
			$QryGroupby3Code .= 'ifNull(CategoryCode,"None")';
			$QryGroupby3Name .= 'ifNull(CategoryName,"None")';
		}
		else if ($pair == 13 )	{
			$groupby3name 	.= 'Profit Center';
			$QryGroupby3Code .= 'ifNull(ProfitCenter,"None")';
			$QryGroupby3Name .= 'ifNull(ProfitCenter,"None")';
		}
		else if ($pair == 27 )	{
			$groupby3name    .= 'Area(Parent)';				
			$QryGroupby3Code .= 'ifNull(ParentAreaCode,"None")';
			$QryGroupby3Name .= 'ifNull(ParentAreaName,"None")';
		}
		else if ($pair == 14 )	{
			$groupby3name 	 .= 'Area(Detail)';				
			$QryGroupby3Code .= 'ifNull(AreaCode,"None")';
			$QryGroupby3Name .= 'ifNull(AreaName,"None")';
		}
		else if ($pair == 30 )	{
			$groupby3name 	 .= 'Client Group';				
			$QryGroupby3Code .= 'ifNull(ClientGroupCode,"None")';
			$QryGroupby3Name .= 'ifNull(ClientGroupName,"None")';
		}
		
	}
	
	if ($vCount > 1) {
		$QryGroupby3Code .= ' ) ';
		$QryGroupby3Name .= ' ) ';
	}
	
	$QryGroupby3Code .= ' As Groupby3Code, ';
	$QryGroupby3Name .= ' As Groupby3Name ';

} else 
{
	$QryGroupby3Code .= " -1 As Groupby3Code, ";
	$QryGroupby3Name .= " 'None' As Groupby3Name ";
}
//echo $QryGroupby3Code."\n";
//echo $QryGroupby3Name;
//exit;

if ($groupby1 != '' ) {
		
	if ($groupby1 == 2 ) {
		$groupby1name    .= 'Supplier';
		$QryGroupby1Code .= 'ifNull(SupplierCode,"None")';
		$QryGroupby1Name .= 'ifNull(SupplierName,"None")';
	}	
	else if ($groupby1 == 3 ) {	
		$groupby1name    .= 'Salesman';
		$QryGroupby1Code .= 'ifNull(SalesmanCode,"None")';
		$QryGroupby1Name .= 'ifNull(SalesmanName,"None")';
	}
	else if ($groupby1 == 20 ) {	
		$groupby1name    .= 'Transaction Project(Parent)';
		$QryGroupby1Code .= 'ifNull(ParentProjectCode,"None")';
		$QryGroupby1Name .= 'ifNull(ParentProjectName,"None")';
	}
	else if ($groupby1 == 4 ) {	
		$groupby1name    .= 'Transaction Project(Detail)';
		$QryGroupby1Code .= 'ifNull(ProjectCode,"None")';
		$QryGroupby1Name .= 'ifNull(ProjectName,"None")';
	}
	else if ($groupby1 == 21 ) {	
		$groupby1name 	 .= 'Archieve Project(Parent)';
		$QryGroupby1Code .= 'ifNull(ParentMasterProject,"None")';
		$QryGroupby1Name .= 'ifNull(ParentMasterProject,"None")';
	}
	else if ($groupby1 == 5 ) {	
		$groupby1name 	 .= 'Archieve Project(Detail)';
		$QryGroupby1Code .= 'ifNull(MasterProject,"None")';
		$QryGroupby1Name .= 'ifNull(MasterProject,"None")';
	}
	else if ($groupby1 == 22 ) {	
		$groupby1name    .= 'Transaction Costcenter(Parent)';	
		$QryGroupby1Code .= 'ifNull(ParentCostcenterCode,"None")';
		$QryGroupby1Name .= 'ifNull(ParentCostcenterName,"None")';
	}
	else if ($groupby1 == 6 ) {	
		$groupby1name    .= 'Transaction Costcenter(Detail)';	
		$QryGroupby1Code .= 'ifNull(CostcenterCode,"None")';
		$QryGroupby1Name .= 'ifNull(CostcenterName,"None")';
	}
	else if ($groupby1 == 23 ) {
		$groupby1name 	 .= 'Archieve Costcenter(Parent)';	
		$QryGroupby1Code .= 'ifNull(ParentMasterCostcent,"None")';
		$QryGroupby1Name .= 'ifNull(ParentMasterCostcent,"None")';
	}
	else if ($groupby1 == 7 ) {
		$groupby1name 	 .= 'Archieve Costcenter(Detail)';	
		$QryGroupby1Code .= 'ifNull(MasterCostcent,"None")';
		$QryGroupby1Name .= 'ifNull(MasterCostcent,"None")';
	}
	else if ($groupby1 == 24 ) { 	
		$groupby1name	 .= 'Warehouse(Parent)';	
		$QryGroupby1Code .= 'ifNull(ParentWarehouseCode,"None")';
		$QryGroupby1Name .= 'ifNull(ParentWarehouseCode,"None")';
	}
	else if ($groupby1 == 8 ) { 	
		$groupby1name	 .= 'Warehouse(Detail)';	
		$QryGroupby1Code .= 'ifNull(WarehouseCode,"None")';
		$QryGroupby1Name .= 'ifNull(WarehouseCode,"None")';
	}
	else if ($groupby1 == 9 ) {	
		$groupby1name    .= 'Business Type';
		$QryGroupby1Code .= 'ifNull(BusinessType,"None")';
		$QryGroupby1Name .= 'ifNull(BusinessType,"None")';
	}
	else if ($groupby1 == 25 )	{
		$groupby1name    .= 'Group(Parent)';
		$QryGroupby1Code .= 'ifNull(ParentGroupCode,"None")';
		$QryGroupby1Name .= 'ifNull(ParentGroupName,"None")';
	}
	else if ($groupby1 == 10 )	{
		$groupby1name    .= 'Group(Detail)';
		$QryGroupby1Code .= 'ifNull(GroupCode,"None")';
		$QryGroupby1Name .= 'ifNull(GroupName,"None")';
	}
	else if ($groupby1 == 26 )	{
		$groupby1name 	 .= 'Subgroup(Parent)';
		$QryGroupby1Code .= 'ifNull(ParentSubGroupCode,"None")';
		$QryGroupby1Name .= 'ifNull(ParentSubGroupName,"None")';
	}
	else if ($groupby1 == 11 )	{
		$groupby1name 	 .= 'Subgroup(Detail)';
		$QryGroupby1Code .= 'ifNull(SubGroupCode,"None")';
		$QryGroupby1Name .= 'ifNull(SubGroupName,"None")';
	}
	else if ($groupby1 == 12 ) {	
		$groupby1name 	 .= 'Category';
		$QryGroupby1Code .= 'ifNull(CategoryCode,"None")';
		$QryGroupby1Name .= 'ifNull(CategoryName,"None")';
	}
	else if ($groupby1 == 13 )	{
		$groupby1name 	 .= 'Profit Center';
		$QryGroupby1Code .= 'ifNull(ProfitCenter,"None")';
		$QryGroupby1Name .= 'ifNull(ProfitCenter,"None")';
	}
	else if ($groupby1 == 27 )	{
		$groupby1name 	 .= 'Area(Parent)';				
		$QryGroupby1Code .= 'ifNull(ParentAreaCode,"None")';
		$QryGroupby1Name .= 'ifNull(ParentAreaName,"None")';
	}
	else if ($groupby1 == 14 )	{
		$groupby1name 	 .= 'Area(Detail)';				
		$QryGroupby1Code .= 'ifNull(AreaCode,"None")';
		$QryGroupby1Name .= 'ifNull(AreaName,"None")';
	}
	else if ($groupby1 == 30 )	{
		$groupby1name 	 .= 'Client Group';				
		$QryGroupby1Code .= 'ifNull(ClientGroupCode,"None")';
		$QryGroupby1Name .= 'ifNull(ClientGroupName,"None")';
	}
	else {
		$groupby1name    .= '';				
		$QryGroupby1Code .= "-1";
		$QryGroupby1Name .= "'None'";
	}
		
	
	$QryGroupby1Code .= ' As Groupby1Code, ';
	$QryGroupby1Name .= ' As Groupby1Name, ';

} else 
{
	$QryGroupby1Code .= " -1 As Groupby1Code, ";
	$QryGroupby1Name .= " 'None' As Groupby1Name, ";
}

if ($groupby2 != '' ) {
		
	if ($groupby2 == 2 ) {
		$groupby2name    .= 'Supplier';
		$QryGroupby2Code .= 'ifNull(SupplierCode,"None")';
		$QryGroupby2Name .= 'ifNull(SupplierName,"None")';
	}	
	else if ($groupby2 == 3 ) {	
		$groupby2name    .= 'Salesman';
		$QryGroupby2Code .= 'ifNull(SalesmanCode,"None")';
		$QryGroupby2Name .= 'ifNull(SalesmanName,"None")';
	}
	else if ($groupby2 == 20 ) {	
		$groupby2name    .= 'Transaction Project(Parent)';
		$QryGroupby2Code .= 'ifNull(ParentProjectCode,"None")';
		$QryGroupby2Name .= 'ifNull(ParentProjectName,"None")';
	}
	else if ($groupby2 == 4 ) {	
		$groupby2name    .= 'Transaction Project(Detail)';
		$QryGroupby2Code .= 'ifNull(ProjectCode,"None")';
		$QryGroupby2Name .= 'ifNull(ProjectName,"None")';
	}
	else if ($groupby2 == 21 ) {	
		$groupby2name 	 .= 'Archieve Project(Parent)';
		$QryGroupby2Code .= 'ifNull(ParentMasterProject,"None")';
		$QryGroupby2Name .= 'ifNull(ParentMasterProject,"None")';
	}
	else if ($groupby2 == 5 ) {	
		$groupby2name 	 .= 'Archieve Project(Detail)';
		$QryGroupby2Code .= 'ifNull(MasterProject,"None")';
		$QryGroupby2Name .= 'ifNull(MasterProject,"None")';
	}
	else if ($groupby2 == 22 ) {	
		$groupby2name    .= 'Transaction Costcenter(Parent)';	
		$QryGroupby2Code .= 'ifNull(ParentCostcenterCode,"None")';
		$QryGroupby2Name .= 'ifNull(ParentCostcenterName,"None")';
	}
	else if ($groupby2 == 6 ) {	
		$groupby2name    .= 'Transaction Costcenter(Detail)';	
		$QryGroupby2Code .= 'ifNull(CostcenterCode,"None")';
		$QryGroupby2Name .= 'ifNull(CostcenterName,"None")';
	}
	else if ($groupby2 == 23 ) {
		$groupby2name 	 .= 'Archieve Costcenter(Parent)';	
		$QryGroupby2Code .= 'ifNull(ParentMasterCostcent,"None")';
		$QryGroupby2Name .= 'ifNull(ParentMasterCostcent,"None")';
	}
	else if ($groupby2 == 7 ) {
		$groupby2name 	 .= 'Archieve Costcenter(Detail)';	
		$QryGroupby2Code .= 'ifNull(MasterCostcent,"None")';
		$QryGroupby2Name .= 'ifNull(MasterCostcent,"None")';
	}
	else if ($groupby2 == 24 ) { 	
		$groupby2name	 .= 'Warehouse(Parent)';	
		$QryGroupby2Code .= 'ifNull(ParentWarehouseCode,"None")';
		$QryGroupby2Name .= 'ifNull(ParentWarehouseCode,"None")';
	}
	else if ($groupby2 == 8 ) { 	
		$groupby2name	 .= 'Warehouse(Detail)';	
		$QryGroupby2Code .= 'ifNull(WarehouseCode,"None")';
		$QryGroupby2Name .= 'ifNull(WarehouseCode,"None")';
	}
	else if ($groupby2 == 9 ) {	
		$groupby2name    .= 'Business Type';
		$QryGroupby2Code .= 'ifNull(BusinessType,"None")';
		$QryGroupby2Name .= 'ifNull(BusinessType,"None")';
	}
	else if ($groupby2 == 25 )	{
		$groupby2name    .= 'Group(Parent)';
		$QryGroupby2Code .= 'ifNull(ParentGroupCode,"None")';
		$QryGroupby2Name .= 'ifNull(ParentGroupName,"None")';
	}
	else if ($groupby2 == 10 )	{
		$groupby2name    .= 'Group(Detail)';
		$QryGroupby2Code .= 'ifNull(GroupCode,"None")';
		$QryGroupby2Name .= 'ifNull(GroupName,"None")';
	}
	else if ($groupby2 == 26 )	{
		$groupby2name 	 .= 'Subgroup(Parent)';
		$QryGroupby2Code .= 'ifNull(ParentSubGroupCode,"None")';
		$QryGroupby2Name .= 'ifNull(ParentSubGroupName,"None")';
	}
	else if ($groupby2 == 11 )	{
		$groupby2name 	 .= 'Subgroup(Detail)';
		$QryGroupby2Code .= 'ifNull(SubGroupCode,"None")';
		$QryGroupby2Name .= 'ifNull(SubGroupName,"None")';
	}
	else if ($groupby2 == 12 ) {	
		$groupby2name 	 .= 'Category';
		$QryGroupby2Code .= 'ifNull(CategoryCode,"None")';
		$QryGroupby2Name .= 'ifNull(CategoryName,"None")';
	}
	else if ($groupby2 == 13 )	{
		$groupby2name 	.= 'Profit Center';
		$QryGroupby2Code .= 'ifNull(ProfitCenter,"None")';
		$QryGroupby2Name .= 'ifNull(ProfitCenter,"None")';
	}
	else if ($groupby2 == 27 )	{
		$groupby2name 	 .= 'Area(Parent)';				
		$QryGroupby2Code .= 'ifNull(ParentAreaCode,"None")';
		$QryGroupby2Name .= 'ifNull(ParentAreaName,"None")';
	}
	else if ($groupby2 == 14 )	{
		$groupby2name 	 .= 'Area(Detail)';				
		$QryGroupby2Code .= 'ifNull(AreaCode,"None")';
		$QryGroupby2Name .= 'ifNull(AreaName,"None")';
	}
	else if ($groupby2 == 30 )	{
		$groupby2name 	 .= 'Client Group';				
		$QryGroupby2Code .= 'ifNull(ClientGroupCode,"None")';
		$QryGroupby2Name .= 'ifNull(ClientGroupName,"None")';
	}
	else {
		$groupby2name    .= '';				
		$QryGroupby2Code .= "-1";
		$QryGroupby2Name .= "'None'";
	}
		
	
	$QryGroupby2Code .= ' As Groupby2Code, ';
	$QryGroupby2Name .= ' As Groupby2Name, ';

} else 
{
	$QryGroupby2Code .= " -1 As Groupby2Code, ";
	$QryGroupby2Name .= " 'None' As Groupby2Name, ";
}

$SelectedGroupBy = "Group by:  ";
if ($groupby1 != '' ) $SelectedGroupBy = $SelectedGroupBy . $groupby1name.", ";
if ($groupby2 != '' ) $SelectedGroupBy = $SelectedGroupBy . $groupby2name.", ";
if ($groupby3 != 1 )  $SelectedGroupBy = $SelectedGroupBy . $groupby3name.", ";
$SelectedOptions = substr($SelectedGroupBy, 0, strlen($SelectedGroupBy)-2) . "\n" . $SelectedOptions;


$ArrayActiveTaxes   = Array();
$ArrayActiveTaxes['ModuleTaxes'] = $Module_AdditionalTaxes;
$ArrayActiveTaxes['Tax1'] = $Setup_Tax1Active;
$ArrayActiveTaxes['Tax2'] = $Setup_Tax2Active;
$ArrayActiveTaxes['Tax3'] = $Setup_Tax3Active;
$ArrayActiveTaxes['Tax4'] = $Setup_Tax4Active;
$ArrayActiveTaxes['Tax5'] = $Setup_Tax5Active;


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

$RowNum    = 10;
$ColumnNum = 0;

$vCountColumns = 0;

$ColumnNum = 3;



$RowNum   += 1;
$ColumnNum = 0;

if ($prtdt == 0 && $prtsubdt == 0 && ($groupby1 != '' || $groupby2 != '' || $groupby3 != 1)) {
	$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
	$worksheet->write($RowNum,$ColumnNum,"Grouping Description", $boldleftFormat);
	$ColumnNum += 1;
}
else if (($masterby == 1 && $prtsubdt == 0) || ($masterby == 2 && $prtsubdt == 1)) {
	$worksheet->write($RowNum,$ColumnNum,"Client Code", $boldleftFormat);
	$ColumnNum += 1;	
	$worksheet->write($RowNum,$ColumnNum,"Name", $boldleftFormat);
} else {
	$worksheet->write($RowNum,$ColumnNum,"Item Code", $boldleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,"Description", $boldleftFormat);
	$ColumnNum += 1;	
	$worksheet->write($RowNum,$ColumnNum,"CRTNS", $boldleftFormat);	
}		

$ColumnNum += 1;

if ($period == 2 ) {
	for ( $y = $fromyear; $y <= $toyear; $y += 1) {

		if ($y == $fromyear && $y == $toyear) { $vMFrom = $frommonth; $vMTo = $tomonth; }
		else if ($y == $fromyear) { $vMFrom = $frommonth; $vMTo = 12; }
		else if ($y == $toyear) { $vMFrom = 1; $vMTo = $tomonth; }
		else { $vMFrom = 1; $vMTo = 12; }
		
		for ( $m = $vMFrom; $m <= $vMTo; $m += 1) {
			$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + $vCountColumns);
			$worksheet->write($RowNum,$ColumnNum,getMonth($m)." ".$y, $boldcenterFormat);
			$ColumnNum ++;
		}
	}
} 
else if ($period == 3 ) {
	$dayscount = dateDiff($fromdate, $todate);
	for ( $d = 0; $d <= $dayscount; $d += 1) {
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + $vCountColumns);
		$worksheet->write($RowNum,$ColumnNum,addDays($fromdate, $d), $boldcenterFormat);
		$ColumnNum ++;
	}
}

$worksheet->write($RowNum,$ColumnNum,"Total Sales/CRTNS", $boldleftFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"Total Sales/MT", $boldleftFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"Sales Values $Base1", $boldleftFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"Sales Values $Base2", $boldleftFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"Sales Price/Average", $boldleftFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"Stock Balance/CTS", $boldleftFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"Cost Price", $boldleftFormat);
$ColumnNum += 1;	 
$worksheet->write($RowNum,$ColumnNum,"Stock Values $Base1", $boldleftFormat);
$ColumnNum += 1;	 
$worksheet->write($RowNum,$ColumnNum,"Stock Values $Base2", $boldleftFormat);
$ColumnNum += 1;	 
$worksheet->write($RowNum,$ColumnNum,"Stock Balance/MT", $boldleftFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"Projection Sales Base on the Current Sales CTS/MT/Value", $boldleftFormat);
$ColumnNum += 1;	 
 
$MainCompanyName=explode('_',$_SESSION['dbName']);  
$QueryDir = " Select Concat(CompanyName, '_', DirYear) As DirSchema,StartReportDate,EndReportDate  ".
			" From ".$_SESSION['dbLabel'].".Directories ".
			" Where Upper(CompanyName) = Upper('".$MainCompanyName[0]."') ";
			
if ($period == 1) //Option Yearly
	$QueryDir= $QueryDir." And ( (ifNull(". $fromyear.", Year(StartFinancialDate)) >= Year(StartFinancialDate)  and ifNull(". $toyear.", Year(EndFinancialDate)) <= Year(EndFinancialDate)) or ".
						 " 	     (ifNull(". $fromyear.", Year(StartFinancialDate)) <= Year(StartFinancialDate)  and ifNull(". $toyear.", Year(EndFinancialDate)) >= Year(EndFinancialDate)) ) ";
else if ($period == 2) //Option Monthly						 
	$QueryDir= $QueryDir." And ( (ifNull(concat('".$fromyear."','".str_pad($frommonth,2,'0',STR_PAD_LEFT)."'),Date_Format(StartFinancialDate,'%Y%m')) >= Date_Format(StartFinancialDate,'%Y%m') and ifNull(concat('".$toyear."','".str_pad($tomonth,2,'0',STR_PAD_LEFT)."'),Date_Format(EndFinancialDate,'%Y%m')) <= Date_Format(EndFinancialDate,'%Y%m')) or   ".
						"       (Date_Format(StartFinancialDate,'%Y%m') >= ifNull(concat('".$fromyear."','".str_pad($frommonth,2,'0',STR_PAD_LEFT)."'),Date_Format(StartFinancialDate,'%Y%m')) and Date_Format(StartFinancialDate,'%Y%m') <= ifNull(concat('".$toyear."','".str_pad($tomonth,2,'0',STR_PAD_LEFT)."'),Date_Format(EndFinancialDate,'%Y%m')) ) or ".
						"       (Date_Format(EndFinancialDate,'%Y%m') >= ifNull(concat('".$fromyear."','".str_pad($frommonth,2,'0',STR_PAD_LEFT)."'),Date_Format(StartFinancialDate,'%Y%m')) and Date_Format(EndFinancialDate,'%Y%m') <= ifNull(concat('".$toyear."','".str_pad($tomonth,2,'0',STR_PAD_LEFT)."'),Date_Format(EndFinancialDate,'%Y%m')) ) ".
						"      )  ";
else if ($period == 3) //Option Daily
	$QueryDir= $QueryDir." And ( (ifNull(Date_Format('". $fromdate."','%Y%m'),Date_Format(StartFinancialDate,'%Y%m')) >= Date_Format(StartFinancialDate,'%Y%m') and ifNull(Date_Format('". $todate."','%Y%m'),Date_Format(EndFinancialDate,'%Y%m')) <= Date_Format(EndFinancialDate,'%Y%m')) or   ".
						//"  		(ifNull(Date_Format('". $fromdate."','%Y%m'),Date_Format(StartFinancialDate,'%Y%m')) <= Date_Format(StartFinancialDate,'%Y%m') and ifNull(Date_Format('". $todate."','%Y%m'),Date_Format(EndFinancialDate,'%Y%m')) >= Date_Format(EndFinancialDate,'%Y%m')) or ".
						"       (Date_Format(StartFinancialDate,'%Y%m') >= ifNull(Date_Format('". $fromdate."','%Y%m'),Date_Format(StartFinancialDate,'%Y%m')) and Date_Format(StartFinancialDate,'%Y%m') <= ifNull(Date_Format('". $todate."','%Y%m'),Date_Format(EndFinancialDate,'%Y%m')) ) or ".
						"       (Date_Format(EndFinancialDate,'%Y%m') >= ifNull(Date_Format('". $fromdate."','%Y%m'),Date_Format(StartFinancialDate,'%Y%m')) and Date_Format(EndFinancialDate,'%Y%m') <= ifNull(Date_Format('". $todate."','%Y%m'),Date_Format(EndFinancialDate,'%Y%m')) ) ".
						"      )  ";
else if ($period == 4) //Option Monthly						 
	$QueryDir= $QueryDir." And ( (ifNull(concat('".$fromyear."','".str_pad($frommonth,2,'0',STR_PAD_LEFT)."'),Date_Format(StartFinancialDate,'%Y%m')) >= Date_Format(StartFinancialDate,'%Y%m') and ifNull(concat('".$toyear."','".str_pad($tomonth,2,'0',STR_PAD_LEFT)."'),Date_Format(EndFinancialDate,'%Y%m')) <= Date_Format(EndFinancialDate,'%Y%m')) or   ".
						"       (Date_Format(StartFinancialDate,'%Y%m') >= ifNull(concat('".$fromyear."','".str_pad($frommonth,2,'0',STR_PAD_LEFT)."'),Date_Format(StartFinancialDate,'%Y%m')) and Date_Format(StartFinancialDate,'%Y%m') <= ifNull(concat('".$toyear."','".str_pad($tomonth,2,'0',STR_PAD_LEFT)."'),Date_Format(EndFinancialDate,'%Y%m')) ) or ".
						"       (Date_Format(EndFinancialDate,'%Y%m') >= ifNull(concat('".$fromyear."','".str_pad($frommonth,2,'0',STR_PAD_LEFT)."'),Date_Format(StartFinancialDate,'%Y%m')) and Date_Format(EndFinancialDate,'%Y%m') <= ifNull(concat('".$toyear."','".str_pad($tomonth,2,'0',STR_PAD_LEFT)."'),Date_Format(EndFinancialDate,'%Y%m')) ) ".
						"      )  ";			
$QueryDir= $QueryDir." Order By DirYear ";
$ResultDir  = mysql_query($QueryDir,$connection) or die(mysql_error());
$RowsDirCount = mysql_num_rows($ResultDir);

//echo $QueryDir; exit;
$j = 0;				
$k = 0; // Count For First Existsing Db 


// Delivery Query
$querydelivery = '';

$querydelivery = $querydelivery." Select Groupby1Code, Groupby1Name, Groupby2Code, Groupby2Name, Groupby3Code, Groupby3Name, ".
								" MasterCode, MasterDescription, DetailCode, DetailDescription, Item_UnitSalesDesc, ItemWeight ";

if ($period == 2 ) {
	for ( $y = $fromyear; $y <= $toyear; $y += 1) {

		if ($y == $fromyear && $y == $toyear) { $vMFrom = $frommonth; $vMTo = $tomonth; }
		else if ($y == $fromyear) { $vMFrom = $frommonth; $vMTo = 12; }
		else if ($y == $toyear) { $vMFrom = 1; $vMTo = $tomonth; }
		else { $vMFrom = 1; $vMTo = 12; }
		
		for ( $m = $vMFrom; $m <= $vMTo; $m += 1) {
		
			$querydelivery = $querydelivery.", Qty".getMonth($m).$y.
											", (ItemWeight*Qty".getMonth($m).$y.") as TotalWeight".getMonth($m).$y.
											", if((Qty".getMonth($m).$y.")=0, 0, (Amt".getMonth($m).$y.") / (Qty".getMonth($m).$y.")) AS Price".getMonth($m).$y.
											", Amt".getMonth($m).$y.
											", if((Qty".getMonth($m).$y.")=0, 0, (TotalCost".getMonth($m).$y.") / (Qty".getMonth($m).$y.")) AS Cost".getMonth($m).$y.
											", TotalCost".getMonth($m).$y.
											", (Amt".getMonth($m).$y.") - (TotalCost".getMonth($m).$y.") As ProfitMargin".getMonth($m).$y.
											", if($prfmarby=2 and (TotalCost".getMonth($m).$y.")=0, 0, ((((Amt".getMonth($m).$y.") - (TotalCost".getMonth($m).$y.")) / if($prfmarby=1,(Amt".getMonth($m).$y."),(TotalCost".getMonth($m).$y."))) * 100)) As MarginPerc".getMonth($m).$y.
											", AmtB1".getMonth($m).$y.
											", AmtB2".getMonth($m).$y.
											", QtyExists".getMonth($m).$y.
											", MeanCostAmount".getMonth($m).$y.
											", MeanCostAmountB1".getMonth($m).$y.
											", MeanCostAmountB2".getMonth($m).$y;
		}

	}
}
else if ($period == 3 ) {
	$dayscount = dateDiff($fromdate, $todate);
	for ( $d = 0; $d <= $dayscount; $d += 1) {
		$querydelivery = $querydelivery.", Qty".addDays($fromdate, $d).
										", (ItemWeight*Qty".addDays($fromdate, $d).") as TotalWeight".addDays($fromdate, $d).
										", if((Qty".addDays($fromdate, $d).")=0, 0, (Amt".addDays($fromdate, $d).") / (Qty".addDays($fromdate, $d).")) AS Price".addDays($fromdate, $d).
										", Amt".addDays($fromdate, $d).
										", if((Qty".addDays($fromdate, $d).")=0, 0, (TotalCost".addDays($fromdate, $d).") / (Qty".addDays($fromdate, $d).")) AS Cost".addDays($fromdate, $d).
										", TotalCost".addDays($fromdate, $d).
										", (Amt".addDays($fromdate, $d).") - (TotalCost".addDays($fromdate, $d).") As ProfitMargin".addDays($fromdate, $d).
										", if($prfmarby=2 and (TotalCost".addDays($fromdate, $d).")=0, 0, ((((Amt".addDays($fromdate, $d).") - (TotalCost".addDays($fromdate, $d).")) / if($prfmarby=1,(Amt".addDays($fromdate, $d)."),(TotalCost".addDays($fromdate, $d)."))) * 100)) As MarginPerc".addDays($fromdate, $d).
										", AmtB1".addDays($fromdate, $d).
										", AmtB2".addDays($fromdate, $d).
										", QtyExists".addDays($fromdate, $d).
										", MeanCostAmount".addDays($fromdate, $d).
										", MeanCostAmountB1".addDays($fromdate, $d).
										", MeanCostAmountB2".addDays($fromdate, $d);
	}
}
$querydelivery = $querydelivery." From ( ";
$querydelivery = $querydelivery." Select Groupby1Code, Groupby1Name, Groupby2Code, Groupby2Name, Groupby3Code, Groupby3Name, ".
								" MasterCode, MasterDescription, DetailCode, DetailDescription, Item_UnitSalesDesc, ItemWeight ";

 if ($period == 2 ) {
	for ( $y = $fromyear; $y <= $toyear; $y += 1) {

		if ($y == $fromyear && $y == $toyear) { $vMFrom = $frommonth; $vMTo = $tomonth; }
		else if ($y == $fromyear) { $vMFrom = $frommonth; $vMTo = 12; }
		else if ($y == $toyear) { $vMFrom = 1; $vMTo = $tomonth; }
		else { $vMFrom = 1; $vMTo = 12; }
		
		for ( $m = $vMFrom; $m <= $vMTo; $m += 1) {
		
			$querydelivery = $querydelivery.", Sum(Qty".getMonth($m).$y.") AS Qty".getMonth($m).$y.
										    ", Sum(Amt".getMonth($m).$y.") AS Amt".getMonth($m).$y.
											", Sum(TotalCost".getMonth($m).$y.") As TotalCost".getMonth($m).$y.
											", Sum(AmtB1".getMonth($m).$y.") AS AmtB1".getMonth($m).$y.
											", Sum(AmtB2".getMonth($m).$y.") AS AmtB2".getMonth($m).$y.
											", (QtyExists".getMonth($m).$y.") AS QtyExists".getMonth($m).$y.
											", (MeanCostAmount".getMonth($m).$y.") As MeanCostAmount".getMonth($m).$y.
											", (MeanCostAmountB1".getMonth($m).$y.") As MeanCostAmountB1".getMonth($m).$y.
											", (MeanCostAmountB2".getMonth($m).$y.") As MeanCostAmountB2".getMonth($m).$y;
		}

	}
}	
else if ($period == 3 ) {
	$dayscount = dateDiff($fromdate, $todate);
	for ( $d = 0; $d <= $dayscount; $d += 1) {
		$querydelivery = $querydelivery.", Sum(Qty".addDays($fromdate, $d).") AS Qty".addDays($fromdate, $d).
										", Sum(Amt".addDays($fromdate, $d).") AS Amt".addDays($fromdate, $d).
										", Sum(TotalCost".addDays($fromdate, $d).") As TotalCost".addDays($fromdate, $d).
										", Sum(AmtB1".addDays($fromdate, $d).") AS AmtB1".addDays($fromdate, $d).
										", Sum(AmtB2".addDays($fromdate, $d).") AS AmtB2".addDays($fromdate, $d).
										", (QtyExists".addDays($fromdate, $d).") AS QtyExists".addDays($fromdate, $d).
										", (MeanCostAmount".addDays($fromdate, $d).") As MeanCostAmount".addDays($fromdate, $d).
										", (MeanCostAmountB1".addDays($fromdate, $d).") As MeanCostAmountB1".addDays($fromdate, $d).
										", (MeanCostAmountB2".addDays($fromdate, $d).") As MeanCostAmountB2".addDays($fromdate, $d);
	}
}	

$querydelivery = $querydelivery." From ( ";
$querydelivery = $querydelivery."   Select DeliveryDate, MasterCode, MasterDescription, DetailCode, DetailDescription, ".
								"   ClientCode, ClientName, ItemCode, ItemDescription, Item_UnitSalesDesc, ItemWeight, ";
$querydelivery = $querydelivery.$QryGroupby1Code . $QryGroupby1Name.
                                $QryGroupby2Code . $QryGroupby2Name.
								$QryGroupby3Code . $QryGroupby3Name;								
								
 if ($period == 2 ) {
	for ( $y = $fromyear; $y <= $toyear; $y += 1) {

		if ($y == $fromyear && $y == $toyear) { $vMFrom = $frommonth; $vMTo = $tomonth; }
		else if ($y == $fromyear) { $vMFrom = $frommonth; $vMTo = 12; }
		else if ($y == $toyear) { $vMFrom = 1; $vMTo = $tomonth; }
		else { $vMFrom = 1; $vMTo = 12; }
		
		for ( $m = $vMFrom; $m <= $vMTo; $m += 1) {
		
			$querydelivery = $querydelivery.", if(Month(DeliveryDate)='".$m."' And Year(DeliveryDate)='".$y."', QtySold, 0) AS Qty".getMonth($m).$y.
										    ", if(Month(DeliveryDate)='".$m."' And Year(DeliveryDate)='".$y."', (ifNull(SellingPrice,0)* QtySold), 0) AS Amt".getMonth($m).$y.
											", if(Month(DeliveryDate)='".$m."' And Year(DeliveryDate)='".$y."', (ifNull(MeanCostAmount,0)* QtySold), 0) As TotalCost".getMonth($m).$y.
											", if(Month(DeliveryDate)='".$m."' And Year(DeliveryDate)='".$y."', (ifNull(SellingPriceB1,0)* QtySold), 0) AS AmtB1".getMonth($m).$y.
											", if(Month(DeliveryDate)='".$m."' And Year(DeliveryDate)='".$y."', (ifNull(SellingPriceB2,0)* QtySold), 0) AS AmtB2".getMonth($m).$y.
											", if(Month(DeliveryDate)='".$m."' And Year(DeliveryDate)='".$y."', QtyExists, 0) AS QtyExists".getMonth($m).$y.
											", if(Month(DeliveryDate)='".$m."' And Year(DeliveryDate)='".$y."', ifNull(MeanCostAmount,0), 0) As MeanCostAmount".getMonth($m).$y.
											", if(Month(DeliveryDate)='".$m."' And Year(DeliveryDate)='".$y."', ifNull(MeanCostAmountB1,0), 0) As MeanCostAmountB1".getMonth($m).$y.
											", if(Month(DeliveryDate)='".$m."' And Year(DeliveryDate)='".$y."', ifNull(MeanCostAmountB2,0), 0) As MeanCostAmountB2".getMonth($m).$y;
		
		}

	}
}
else if ($period == 3 ) {
	$dayscount = dateDiff($fromdate, $todate);
	for ( $d = 0; $d <= $dayscount; $d += 1) {
		$querydelivery = $querydelivery.", if(Date_format(DeliveryDate,'%Y-%m-%d')='".addDays($fromdate, $d, 'Y-m-d')."' , QtySold, 0) AS Qty".addDays($fromdate, $d).
										", if(Date_format(DeliveryDate,'%Y-%m-%d')='".addDays($fromdate, $d, 'Y-m-d')."' , (ifNull(SellingPrice,0)* QtySold), 0) AS Amt".addDays($fromdate, $d).
										", if(Date_format(DeliveryDate,'%Y-%m-%d')='".addDays($fromdate, $d, 'Y-m-d')."' , (ifNull(MeanCostAmount,0)* QtySold), 0) As TotalCost".addDays($fromdate, $d).
										", if(Date_format(DeliveryDate,'%Y-%m-%d')='".addDays($fromdate, $d, 'Y-m-d')."' , (ifNull(SellingPriceB1,0)* QtySold), 0) AS AmtB1".addDays($fromdate, $d).
										", if(Date_format(DeliveryDate,'%Y-%m-%d')='".addDays($fromdate, $d, 'Y-m-d')."' , (ifNull(SellingPriceB2,0)* QtySold), 0) AS AmtB2".addDays($fromdate, $d). 
										", if(Date_format(DeliveryDate,'%Y-%m-%d')='".addDays($fromdate, $d, 'Y-m-d')."' , QtyExists, 0) AS QtyExists".addDays($fromdate, $d).
										", if(Date_format(DeliveryDate,'%Y-%m-%d')='".addDays($fromdate, $d, 'Y-m-d')."' , ifNull(MeanCostAmount,0), 0) As MeanCostAmount".addDays($fromdate, $d).
										", if(Date_format(DeliveryDate,'%Y-%m-%d')='".addDays($fromdate, $d, 'Y-m-d')."' , ifNull(MeanCostAmountB1,0), 0) As MeanCostAmountB1".addDays($fromdate, $d).
										", if(Date_format(DeliveryDate,'%Y-%m-%d')='".addDays($fromdate, $d, 'Y-m-d')."' , ifNull(MeanCostAmountB2,0), 0) As MeanCostAmountB2".addDays($fromdate, $d);
	}
}				
	
$querydelivery = $querydelivery.
				" From ( ";
				
// Reading Data For Many Years Or Different Directories
while($RowsDir=mysql_fetch_row($ResultDir)){
	
	$j = $j + 1;    
	$DirSchema = $RowsDir[0];
	$StartReportDate=$RowsDir[1];
	$EndReportDate=$RowsDir[2];

			   
	if ($RowsDirCount > 1) {
		$QuerySchemaExists = "Show Schemas where `Database` = '".$DirSchema."'  ";   
		$ResultSchemaExists=mysql_query($QuerySchemaExists,$connection) or die(mysql_error());
		$SchemaExists = mysql_num_rows($ResultSchemaExists);
				
	}
	
	if (($RowsDirCount > 1 && $SchemaExists > 0) || ($RowsDirCount == 1) ) {
		
		if($Module_Delivery == 0 ) {	
			$querydelivery = $querydelivery.
					" Select DeliveryDt.DeliveryDt_Id as DeliveryDtId, Deliveries.Delivery_Date As DeliveryDate, if($prtdt=1, if($masterby=1, Deliveries.Client_Code, DeliveryDt.Item_Code),'') As MasterCode, ".
					" if($prtdt=1, if($masterby=1, Clients.Ledger_Name, Items.Item_Description), '') As MasterDescription , ".
					" if($prtsubdt=1, if($masterby=1, DeliveryDt.Item_Code, Deliveries.Client_Code), '') As DetailCode, ".
					" if($prtsubdt=1, if($masterby=1, Items.Item_Description, Clients.Ledger_Name), '') As DetailDescription , ".
					" Deliveries.Client_Code As ClientCode, Clients.Ledger_Name As ClientName, ".
					" DeliveryDt.Item_Code As ItemCode, Items.Item_Description As ItemDescription, ".
					" Items.Item_Weight as ItemWeight, Items.Item_UnitSalesDesc,  ".
					" Deliveries.Salesman_Code As SalesmanCode,Salesmen.Ledger_Name As SalesmanName, ".
					" Items.Ledger_Number As SupplierCode,Suppliers.Ledger_Name As SupplierName, ".
					" ifNull(Costcent.Parent_Code, Costcent.Costcent_Code) As ParentCostcenterCode,Costcent.Costcent_Name As ParentCostcenterName, ".
					" Deliveries.Costcent_Code As CostcenterCode,Costcent.Costcent_Name As CostcenterName, ".
					" ifNull(Project.Parent_Code, Project.Project_Code) As ParentProjectCode, Project.Project_Name As ParentProjectName, ".
					" Deliveries.Project_Code As ProjectCode,Project.Project_Name As ProjectName, ".
					" ifNull(Grouping.Parent_Code, Items.Group_Code) As ParentGroupCode, Grouping.Group_Name As ParentGroupName, ".
					" Items.Group_Code As GroupCode,Grouping.Group_Name As GroupName, ".
					" ifNull(Subgroup.Parent_Code, Items.SubGroup_Code) As ParentSubGroupCode, Subgroup.Subgroup_Name As ParentSubGroupName, ".
					" Items.SubGroup_Code As SubGroupCode,Subgroup.Subgroup_Name As SubGroupName, ".
					" ifNull(Area.Parent_Code, Clients.Area_Code) As ParentAreaCode, Area.Area_Name As ParentAreaName, ".
					" Clients.Area_Code As AreaCode ,Area.Area_Name As AreaName, ".
					" Items.Category_Code As CategoryCode, Category.Category_Name As CategoryName, ".
					" Clients.ProfitCenter_Code As ProfitCenter, Clients.Client_BusinessType As BusinessType, Clients.ClientGrp_Code As ClientGroupCode, LedgerGrp.LedgerGrp_Name As ClientGroupName, ".
					" Clients.Project_Code As MasterProject, Clients.CostCenter_Code As MasterCostcent, ".
					" ifNull(Project1.Parent_Code, Clients.Project_Code) As ParentMasterProject, ifNull(Costcent1.Parent_Code, Clients.CostCenter_Code) As ParentMasterCostcent, ".
					" DeliveryDt.Warehouse_Code As WarehouseCode, ifNull(Warehouse1.Warehouse_Code, DeliveryDt.Warehouse_Code) As ParentWarehouseCode,  ".
					" if(DeliveryDt.Journal_Type = 'DI',DeliveryDt.DeliveryDt_Quantity, (-1)*DeliveryDt.DeliveryDt_Quantity) As QtySold,  ".
					" Deliveries.Currency_Code As Currency, ".
					" ifNull(DeliveryDt.DeliveryDt_discountper, 0) As DeliveryDt_discountper,  ".
					//" ifnull(QtyExists(DeliveryDt.Item_Code, DeliveryDt.Warehouse_Code, Deliveries.Delivery_Date, 0, 'DI', '', ''),0) As QtyExists,  ".
					" ifnull(QtyExists(DeliveryDt.Item_Code, '', concat('". $todate."',' 23:59:59'),0, 'DI', '', ''),0) As QtyExists,  ".
					" if($pricettc=1, if(ifNull(DeliveryDt.DeliveryDt_PriceTTC,0)=0, 0, ifNull( (((if(upper($curr)='".$Base1."', ifNull(DeliveryDt.DeliveryDt_PriceTTCBase1,0), if(upper($curr)='".$Base2."', ifNull(DeliveryDt.DeliveryDt_PriceTTCBase2,0), ifNull(DeliveryDt.DeliveryDt_PriceTTC,0))) - ".
					"                  (ifNull(if(upper($curr)='".$Base1."', ifNull(DeliveryDt.DeliveryDt_PriceTTCBase1,0), if(upper($curr)='".$Base2."', ifNull(DeliveryDt.DeliveryDt_PriceTTCBase2,0), ifNull(DeliveryDt.DeliveryDt_PriceTTC,0))),0) * ifNull(DeliveryDt.DeliveryDt_discountper, 0)/100))*DeliveryDt.DeliveryDt_Quantity - ".
					"                  ifNull(if(upper($curr)='".$Base1."', ifNull(DeliveryDt.DeliveryDt_DiscountBase1,0), if(upper($curr)='".$Base2."', ifNull(DeliveryDt.DeliveryDt_DiscountBase2,0), ifNull(DeliveryDt.DeliveryDt_DiscountAmount,0))),0) - ".
					"        		   ifNull(if(upper($curr)='".$Base1."',DeliveryDt.DeliveryDt_TransportAmountBase1,if(upper($curr)='".$Base2."',DeliveryDt.DeliveryDt_TransportAmountBase2,DeliveryDt.DeliveryDt_TransportAmount)),0) - ".
					"				   ifNull(if(upper($curr)='".$Base1."',DeliveryDt.DeliveryDt_BonusBase1,if(upper($curr)='".$Base2."',DeliveryDt.DeliveryDt_BonusBase2,DeliveryDt.DeliveryDt_BonusAmount)),0))/DeliveryDt.DeliveryDt_Quantity) ,0)),  ".
					" if(ifNull(DeliveryDt.DeliveryDt_Price,0)=0, 0, ifNull( (((if(upper($curr)='".$Base1."', ifNull(DeliveryDt.DeliveryDt_PriceBase1,0), if(upper($curr)='".$Base2."', ifNull(DeliveryDt.DeliveryDt_PriceBase2,0), ifNull(DeliveryDt.DeliveryDt_Price,0))) - ".
					//"         (ifNull(if(upper($curr)='".$Base1."', ifNull(DeliveryDt.DeliveryDt_PriceBase1,0), if(upper($curr)='".$Base2."', ifNull(DeliveryDt.DeliveryDt_PriceBase2,0), ifNull(DeliveryDt.DeliveryDt_Price,0))),0) * ifNull(DeliveryDt.Item_Vat, 0)/100) - ".
					"         (ifNull(if(upper($curr)='".$Base1."', ifNull(DeliveryDt.DeliveryDt_PriceBase1,0), if(upper($curr)='".$Base2."', ifNull(DeliveryDt.DeliveryDt_PriceBase2,0), ifNull(DeliveryDt.DeliveryDt_Price,0))),0) * ifNull(DeliveryDt.DeliveryDt_discountper, 0)/100))*DeliveryDt.DeliveryDt_Quantity - ".
					"         ifNull(if(upper($curr)='".$Base1."', ifNull(DeliveryDt.DeliveryDt_DiscountBase1,0), if(upper($curr)='".$Base2."', ifNull(DeliveryDt.DeliveryDt_DiscountBase2,0), ifNull(DeliveryDt.DeliveryDt_DiscountAmount,0))),0) - ".
					"         ifNull(if(upper($curr)='".$Base1."',DeliveryDt.DeliveryDt_TransportAmountBase1,if(upper($curr)='".$Base2."',DeliveryDt.DeliveryDt_TransportAmountBase2,DeliveryDt.DeliveryDt_TransportAmount)),0) - ".
					"		  ifNull(if(upper($curr)='".$Base1."',DeliveryDt.DeliveryDt_BonusBase1,if(upper($curr)='".$Base2."',DeliveryDt.DeliveryDt_BonusBase2,DeliveryDt.DeliveryDt_BonusAmount)),0))/DeliveryDt.DeliveryDt_Quantity) ,0)) ) As SellingPrice,  ".
					//base1
					" if($pricettc=1, if(ifNull(DeliveryDt.DeliveryDt_PriceTTC,0)=0, 0, ifNull( (((ifNull(DeliveryDt.DeliveryDt_PriceTTCBase1,0) - ".
					"                  (ifNull(DeliveryDt.DeliveryDt_PriceTTCBase1,0) * ifNull(DeliveryDt.DeliveryDt_discountper, 0)/100))*DeliveryDt.DeliveryDt_Quantity - ".
					"                  ifNull(DeliveryDt.DeliveryDt_DiscountBase1,0) - ".
					"        		   ifNull(DeliveryDt.DeliveryDt_TransportAmountBase1,0) - ".
					"				   ifNull(DeliveryDt.DeliveryDt_BonusBase1,0))/DeliveryDt.DeliveryDt_Quantity) ,0)),  ".
					" if(ifNull(DeliveryDt.DeliveryDt_Price,0)=0, 0, ifNull( (((ifNull(DeliveryDt.DeliveryDt_PriceBase1,0) - ".
					"         (ifNull(DeliveryDt.DeliveryDt_PriceBase1,0) * ifNull(DeliveryDt.DeliveryDt_discountper, 0)/100))*DeliveryDt.DeliveryDt_Quantity - ".
					"          ifNull(DeliveryDt.DeliveryDt_DiscountBase1,0) - ".
					"         ifNull(DeliveryDt.DeliveryDt_TransportAmountBase1,0) - ".
					"		  ifNull(DeliveryDt.DeliveryDt_BonusBase1,0))/DeliveryDt.DeliveryDt_Quantity) ,0)) ) As SellingPriceB1,  ".
					//base2
					" if($pricettc=1, if(ifNull(DeliveryDt.DeliveryDt_PriceTTC,0)=0, 0, ifNull( (((ifNull(DeliveryDt.DeliveryDt_PriceTTCBase2,0) - ".
					"                  (ifNull(DeliveryDt.DeliveryDt_PriceTTCBase2,0) * ifNull(DeliveryDt.DeliveryDt_discountper, 0)/100))*DeliveryDt.DeliveryDt_Quantity - ".
					"                  ifNull(DeliveryDt.DeliveryDt_DiscountBase2,0) - ".
					"        		   ifNull(DeliveryDt.DeliveryDt_TransportAmountBase2,0) - ".
					"				   ifNull(DeliveryDt.DeliveryDt_BonusBase2,0))/DeliveryDt.DeliveryDt_Quantity) ,0)),  ".
					" if(ifNull(DeliveryDt.DeliveryDt_Price,0)=0, 0, ifNull( (((ifNull(DeliveryDt.DeliveryDt_PriceBase2,0) - ".
					"         (ifNull(DeliveryDt.DeliveryDt_PriceBase2,0) * ifNull(DeliveryDt.DeliveryDt_discountper, 0)/100))*DeliveryDt.DeliveryDt_Quantity - ".
					"          ifNull(DeliveryDt.DeliveryDt_DiscountBase2,0) - ".
					"         ifNull(DeliveryDt.DeliveryDt_TransportAmountBase2,0) - ".
					"		  ifNull(DeliveryDt.DeliveryDt_BonusBase2,0))/DeliveryDt.DeliveryDt_Quantity) ,0)) ) As SellingPriceB2,  ".
					" if(ifNull(Delivery_VAT,0) > 0, DeliveryDt.Item_Vat, 0) As ItemVat, ifNull(DeliveryDt.Item_tax1,0) as ItemTax1, ifNull(DeliveryDt.Item_tax2,0) as ItemTax2, ifNull(DeliveryDt.Item_tax3,0) as ItemTax3, ifNull(DeliveryDt.Item_tax4,0) as ItemTax4, ifNull(DeliveryDt.Item_tax5,0) as ItemTax5, ";

			$querydelivery= $querydelivery." ifNull(MeanCostCur(DeliveryDt.Item_Code, concat('". $todate."',' 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) MeanCostAmount, ".
							" ifNull(MeanCostCur(DeliveryDt.Item_Code, concat('". $todate."',' 23:59:59'), 1, Deliveries.Currency_Code, ''),0) MeanCostAmountB1, ".
							" ifNull(MeanCostCur(DeliveryDt.Item_Code, concat('". $todate."',' 23:59:59'), 2, Deliveries.Currency_Code, ''),0) MeanCostAmountB2 ";
			

			$querydelivery= $querydelivery. " From $DirSchema.DeliveryDt As DeliveryDt  ";
			$querydelivery= $querydelivery. " Inner Join $DirSchema.Deliveries As Deliveries on DeliveryDt.Delivery_ID = Deliveries.Delivery_ID ".
											" Inner Join $DirSchema.Items on DeliveryDt.Item_Code = Items.Item_Code ".
											" Inner Join Clients on Clients.Ledger_Number = Deliveries.Client_Code ".
											" Left Join Suppliers on Items.Ledger_Number = Suppliers.Ledger_Number ".
											" Left Join Grouping on Items.Group_Code = Grouping.Group_Code ".
											" Left Join SubGroup on Items.SubGroup_Code = SubGroup.SubGroup_Code ".
											" Left Join Category on Items.Category_Code = Category.Category_Code ".
											" Left Join Salesmen on Deliveries.Salesman_Code = Salesmen.Ledger_Number ".
											" Left Join Area on Area.Area_Code = Clients.Area_Code ".
											" Left Join LedgerGrp on LedgerGrp.LedgerGrp_Code = Clients.ClientGrp_Code ".
											" Left Join Costcent on Costcent.Costcent_Code = Deliveries.Costcent_Code ".
											" Left Join Project on Project.Project_Code = Deliveries.Project_Code ".
											" Left Join Costcent As Costcent1 on Costcent1.Costcent_Code = Clients.CostCenter_Code ".
											" Left Join Project As Project1 on Project1.Project_Code = Clients.Project_Code ".
											" Left Join Warehouse As Warehouse1 on Warehouse1.Warehouse_Code = DeliveryDt.Warehouse_Code ";
									  
			if ($projectexist >	0 && $FI_RepRestrictProject == 1)
			  $querydelivery= $querydelivery." Inner 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 (Deliveries.Delivery_Reference Is Not Null And Deliveries.Delivery_Reference != '')  ".
											" 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 Deliveries.Client_Code between ifNull(".$fromclient.",Deliveries.Client_Code) and ifNull(". $toclient.",Deliveries.Client_Code) ".
											" 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 ifNull(Clients.Client_BusinessType,'-1') between ifNull(".$frombustype.",ifNull(Clients.Client_BusinessType,'-1')) and ifNull(". $tobustype.",ifNull(Clients.Client_BusinessType,'-1')) ".
											" And ifNull(Clients.area_code,'-1') = ifNull(". $fromarea .",ifNull(Clients.area_code,'-1'))  ".
											" And (($promprint=1 and ifNull(DeliveryDt.DeliveryDt_Promotion,0) = 1) Or ($promprint=2 and ifNull(DeliveryDt.DeliveryDt_Promotion,0) = 0) Or ($promprint=3 and 1 = 1))  ".
											" And (($activeclt=1 and ifNull(Clients.Client_deactivate,0) = 0) or ($activeclt=2 and ifNull(Clients.Client_deactivate,0) = 1) or ($activeclt=3 and 1 = 1)) ".
											" And (($activeitem=1 and ifNull(Items.Item_Active,0) = 1) or ($activeitem=2 and ifNull(Items.Item_Active,0) = 0) or ($activeitem=3 and 1 = 1)) ";
								
			if ($showsales == 1 && $showretsales == 1)
				$querydelivery= $querydelivery." And DeliveryDt.Journal_Type in ('DI','DR') ";
			else if ($showsales == 1)
				$querydelivery= $querydelivery." And DeliveryDt.Journal_Type = 'DI' ";
			else if ($showretsales == 1)
				$querydelivery= $querydelivery." And DeliveryDt.Journal_Type = 'DR' ";
											

			if ($period == 1) //Option Yearly
				$querydelivery= $querydelivery." And Year(Deliveries.Delivery_Date) between ifNull(year('".$StartReportDate."'), Year(Deliveries.Delivery_Date)) and ifNull(year('".$EndReportDate."'), Year(Deliveries.Delivery_Date))  ";	
			else if ($period == 2) //Option Monthly
				$querydelivery= $querydelivery." And concat(Year(Deliveries.Delivery_Date),LPAD(Month(Deliveries.Delivery_Date), 2, '0')) between concat(ifNull(year('".$StartReportDate."'), Year(Deliveries.Delivery_Date)),ifNull('".str_pad($frommonth,2,'0',STR_PAD_LEFT)."', LPAD(Month(Deliveries.Delivery_Date), 2, '0'))) and concat(ifNull(year('".$EndReportDate."'), Year(Deliveries.Delivery_Date)),ifNull('".str_pad($tomonth,2,'0',STR_PAD_LEFT)."', LPAD(Month(Deliveries.Delivery_Date), 2, '0')))  ";
											   //" And Month(Deliveries.Delivery_Date) between ifNull(".$frommonth.", Month(Deliveries.Delivery_Date)) and ifNull(".$tomonth.", Month(Deliveries.Delivery_Date))  ";	
			else if ($period == 3) //Option Daily
				$querydelivery= $querydelivery." 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'))  ";
			else if ($period == 4) //Option Monthly Comparative
				$querydelivery= $querydelivery." And concat(Year(Deliveries.Delivery_Date),LPAD(Month(Deliveries.Delivery_Date), 2, '0')) between concat(ifNull(year('".$StartReportDate."'), Year(Deliveries.Delivery_Date)),ifNull('".str_pad($frommonth,2,'0',STR_PAD_LEFT)."', LPAD(Month(Deliveries.Delivery_Date), 2, '0'))) and concat(ifNull(year('".$EndReportDate."'), Year(Deliveries.Delivery_Date)),ifNull('".str_pad($tomonth,2,'0',STR_PAD_LEFT)."', LPAD(Month(Deliveries.Delivery_Date), 2, '0')))  ";								 
			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 ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
			if ($groupexist > 0)
				$querydelivery= $querydelivery." And Items.group_code In (select distinct group_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
			
		} else {
		
			$querydelivery = $querydelivery.
					" Select SalesDt.SaleDt_Id as DeliveryDtId, Sales.Sale_Date As DeliveryDate, if($prtdt=1, if($masterby=1, Sales.Client_Code, SalesDt.Item_Code),'') As MasterCode, ".
					" if($prtdt=1, if($masterby=1, Clients.Ledger_Name, Items.Item_Description), '') As MasterDescription , ".
					" if($prtsubdt=1, if($masterby=1, SalesDt.Item_Code, Sales.Client_Code), '') As DetailCode, ".
					" if($prtsubdt=1, if($masterby=1, Items.Item_Description, Clients.Ledger_Name), '') As DetailDescription , ".
					" Sales.Client_Code As ClientCode, Clients.Ledger_Name As ClientName, ".
					" SalesDt.Item_Code As ItemCode, Items.Item_Description As ItemDescription, ".
					" Items.Item_Weight as ItemWeight, Items.Item_UnitSalesDesc,  ".
					" Sales.Salesman_Code As SalesmanCode,Salesmen.Ledger_Name As SalesmanName, ".
					" Items.Ledger_Number As SupplierCode,Suppliers.Ledger_Name As SupplierName, ".
					" ifNull(Costcent.Parent_Code, Costcent.Costcent_Code) As ParentCostcenterCode,Costcent.Costcent_Name As ParentCostcenterName, ".
					" Sales.Costcenter_Code As CostcenterCode,Costcent.Costcent_Name As CostcenterName, ".
					" ifNull(Project.Parent_Code, Project.Project_Code) As ParentProjectCode, Project.Project_Name As ParentProjectName, ".
					" Sales.Project_Code As ProjectCode,Project.Project_Name As ProjectName, ".
					" ifNull(Grouping.Parent_Code, Items.Group_Code) As ParentGroupCode, Grouping.Group_Name As ParentGroupName, ".
					" Items.Group_Code As GroupCode,Grouping.Group_Name As GroupName, ".
					" ifNull(Subgroup.Parent_Code, Items.SubGroup_Code) As ParentSubGroupCode, Subgroup.Subgroup_Name As ParentSubGroupName, ".
					" Items.SubGroup_Code As SubGroupCode,Subgroup.Subgroup_Name As SubGroupName, ".
					" ifNull(Area.Parent_Code, Clients.Area_Code) As ParentAreaCode, Area.Area_Name As ParentAreaName, ".
					" Clients.Area_Code As AreaCode ,Area.Area_Name As AreaName, ".
					" Items.Category_Code As CategoryCode, Category.Category_Name As CategoryName, ".
					" Clients.ProfitCenter_Code As ProfitCenter, Clients.Client_BusinessType As BusinessType, Clients.ClientGrp_Code As ClientGroupCode, LedgerGrp.LedgerGrp_Name As ClientGroupName, ".
					" Clients.Project_Code As MasterProject, Clients.CostCenter_Code As MasterCostcent, ".
					" ifNull(Project1.Parent_Code, Clients.Project_Code) As ParentMasterProject, ifNull(Costcent1.Parent_Code, Clients.CostCenter_Code) As ParentMasterCostcent, ".
					" SalesDt.Warehouse_Code As WarehouseCode, ifNull(Warehouse1.Warehouse_Code, SalesDt.Warehouse_Code) As ParentWarehouseCode,  ".
					" if(SalesDt.Journal_Type = 'SI',ifNull(SalesDt.SaleDt_Quantity,0), (-1)*ifNull(SalesDt.SaleDt_Quantity,0)) As QtySold,  ".
					" Sales.Currency_Code As Currency, ".
					" ifNull(SalesDt.SaleDt_discountper, 0) As DeliveryDt_discountper,  ".
					//" ifnull(QtyExists(SalesDt.Item_Code, SalesDt.Warehouse_Code, Sales.Sale_Date, 0, 'SI', '', ''),0) As QtyExists,  ".
					" ifnull(QtyExists(SalesDt.Item_Code, '', concat('". $todate."',' 23:59:59'), 0, 'SI', '', ''),0) As QtyExists,  ".
					" if($pricettc=1, if(ifNull(SalesDt.SaleDt_PriceTTC,0)=0, 0, ifNull( (((if(upper($curr)='".$Base1."', ifNull(SalesDt.SaleDt_PriceTTCBase1,0), if(upper($curr)='".$Base2."', ifNull(SalesDt.SaleDt_PriceTTCBase2,0), ifNull(SalesDt.SaleDt_PriceTTC,0))) - ".
					"                  (ifNull(if(upper($curr)='".$Base1."', ifNull(SalesDt.SaleDt_PriceTTCBase1,0), if(upper($curr)='".$Base2."', ifNull(SalesDt.SaleDt_PriceTTCBase2,0), ifNull(SalesDt.SaleDt_PriceTTC,0))),0) * ifNull(SalesDt.SaleDt_discountper, 0)/100))*SalesDt.SaleDt_Quantity - ".
					"                  ifNull(if(upper($curr)='".$Base1."', ifNull(SalesDt.SaleDt_DiscountBase1,0), if(upper($curr)='".$Base2."', ifNull(SalesDt.SaleDt_DiscountBase2,0), ifNull(SalesDt.SaleDt_DiscountAmount,0))),0) - ".
					"        		   ifNull(if(upper($curr)='".$Base1."',SalesDt.SaleDt_TransportBase1,if(upper($curr)='".$Base2."',SalesDt.SaleDt_TransportBase2,SalesDt.SaleDt_TransportAmount)),0) - ".
					"		 		   ifNull(if(upper($curr)='".$Base1."',SalesDt.SaleDt_BonusBase1,if(upper($curr)='".$Base2."',SalesDt.SaleDt_BonusBase2,SalesDt.SaleDt_BonusAmount)),0))/SalesDt.SaleDt_Quantity) ,0)),  ".
					" if(ifNull(SalesDt.SaleDt_Price,0)=0, 0, ifNull( (((if(upper($curr)='".$Base1."', ifNull(SalesDt.SaleDt_PriceBase1,0), if(upper($curr)='".$Base2."', ifNull(SalesDt.SaleDt_PriceBase2,0), ifNull(SalesDt.SaleDt_Price,0))) - ".
					//"         (ifNull(if(upper($curr)='".$Base1."', ifNull(SalesDt.SaleDt_PriceBase1,0), if(upper($curr)='".$Base2."', ifNull(SalesDt.SaleDt_PriceBase2,0), ifNull(SalesDt.SaleDt_Price,0))),0) * ifNull(SalesDt.Item_Vat, 0)/100) - ".
					"         (ifNull(if(upper($curr)='".$Base1."', ifNull(SalesDt.SaleDt_PriceBase1,0), if(upper($curr)='".$Base2."', ifNull(SalesDt.SaleDt_PriceBase2,0), ifNull(SalesDt.SaleDt_Price,0))),0) * ifNull(SalesDt.SaleDt_discountper, 0)/100))*SalesDt.SaleDt_Quantity - ".
					"         ifNull(if(upper($curr)='".$Base1."', ifNull(SalesDt.SaleDt_DiscountBase1,0), if(upper($curr)='".$Base2."', ifNull(SalesDt.SaleDt_DiscountBase2,0), ifNull(SalesDt.SaleDt_DiscountAmount,0))),0) - ".
					"         ifNull(if(upper($curr)='".$Base1."',SalesDt.SaleDt_TransportBase1,if(upper($curr)='".$Base2."',SalesDt.SaleDt_TransportBase2,SalesDt.SaleDt_TransportAmount)),0) - ".
					"		  ifNull(if(upper($curr)='".$Base1."',SalesDt.SaleDt_BonusBase1,if(upper($curr)='".$Base2."',SalesDt.SaleDt_BonusBase2,SalesDt.SaleDt_BonusAmount)),0))/SalesDt.SaleDt_Quantity) ,0)) ) As SellingPrice,  ".
					//base1
					" if($pricettc=1, if(ifNull(SalesDt.SaleDt_PriceTTC,0)=0, 0, ifNull( (((if(upper($curr)='".$Base1."', ifNull(SalesDt.SaleDt_PriceTTCBase1,0), if(upper($curr)='".$Base2."', ifNull(SalesDt.SaleDt_PriceTTCBase2,0), ifNull(SalesDt.SaleDt_PriceTTC,0))) - ".
					"                  (ifNull(SalesDt.SaleDt_PriceTTCBase1,0) * ifNull(SalesDt.SaleDt_discountper, 0)/100))*SalesDt.SaleDt_Quantity - ".
					"                  ifNull(SalesDt.SaleDt_DiscountBase1,0) - ".
					"        		   ifNull(SalesDt.SaleDt_TransportBase1,0) - ".
					"		 		   ifNull(SalesDt.SaleDt_BonusBase1,0))/SalesDt.SaleDt_Quantity) ,0)),  ".
					" if(ifNull(SalesDt.SaleDt_Price,0)=0, 0, ifNull( (((ifNull(SalesDt.SaleDt_PriceBase1,0) - ".
					"         (ifNull(SalesDt.SaleDt_PriceBase1,0) * ifNull(SalesDt.SaleDt_discountper, 0)/100))*SalesDt.SaleDt_Quantity - ".
					"         ifNull(SalesDt.SaleDt_DiscountBase1,0) - ".
					"         ifNull(SalesDt.SaleDt_TransportBase1,0) - ".
					"		  ifNull(SalesDt.SaleDt_BonusBase1,0))/SalesDt.SaleDt_Quantity) ,0)) ) As SellingPriceB1,  ".
					//base2
					" if($pricettc=1, if(ifNull(SalesDt.SaleDt_PriceTTC,0)=0, 0, ifNull( ((( ifNull(SalesDt.SaleDt_PriceTTCBase2,0) - ".
					"                  (ifNull(SalesDt.SaleDt_PriceTTCBase2,0) * ifNull(SalesDt.SaleDt_discountper, 0)/100))*SalesDt.SaleDt_Quantity - ".
					"                  ifNull(SalesDt.SaleDt_DiscountBase2,0) - ".
					"        		   ifNull(SalesDt.SaleDt_TransportBase2,0) - ".
					"		 		   ifNull(SalesDt.SaleDt_BonusBase2,0))/SalesDt.SaleDt_Quantity) ,0)),  ".
					" if(ifNull(SalesDt.SaleDt_Price,0)=0, 0, ifNull( (((ifNull(SalesDt.SaleDt_PriceBase2,0) - ".
					"         (ifNull(SalesDt.SaleDt_PriceBase2,0) * ifNull(SalesDt.SaleDt_discountper, 0)/100))*SalesDt.SaleDt_Quantity - ".
					"         ifNull(SalesDt.SaleDt_DiscountBase2,0) - ".
					"         ifNull(SalesDt.SaleDt_TransportBase2,0) - ".
					"		  ifNull(SalesDt.SaleDt_BonusBase2,0))/SalesDt.SaleDt_Quantity) ,0)) ) As SellingPriceB2,  ".
					" if(ifNull(sale_chkvat,0) = 1, SalesDt.Item_Vat, 0) As ItemVat, ifNull(SalesDt.Item_tax1,0) as ItemTax1, ifNull(SalesDt.Item_tax2,0) as ItemTax2, ifNull(SalesDt.Item_tax3,0) as ItemTax3, ifNull(SalesDt.Item_tax4,0) as ItemTax4, ifNull(SalesDt.Item_tax5,0) as ItemTax5, ";

			$querydelivery= $querydelivery." ifNull(MeanCostCur(SalesDt.Item_Code, concat('". $todate."',' 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) MeanCostAmount, ".
						" ifNull(MeanCostCur(SalesDt.Item_Code, concat('". $todate."',' 23:59:59'), 1, Sales.Currency_Code, ''),0) MeanCostAmountB1, ".
						" ifNull(MeanCostCur(SalesDt.Item_Code, concat('". $todate."',' 23:59:59'), 2, Sales.Currency_Code, ''),0) MeanCostAmountB2 ";
			
			$querydelivery= $querydelivery. " From $DirSchema.SalesDt As SalesDt  ";
			$querydelivery= $querydelivery. " Inner Join $DirSchema.Sales As Sales on SalesDt.Sale_ID = Sales.Sale_ID ".
											" Left Join $DirSchema.DeliveryDt On SalesDt.RelatedDt_ID = DeliveryDt.DeliveryDt_ID ".
											" Inner Join $DirSchema.Items on SalesDt.Item_Code = Items.Item_Code ".
											" Inner Join Clients on Clients.Ledger_Number = Sales.Client_Code ".
											" Left Join Suppliers on Items.Ledger_Number = Suppliers.Ledger_Number ".
											" Left Join Grouping on Items.Group_Code = Grouping.Group_Code ".
											" Left Join SubGroup on Items.SubGroup_Code = SubGroup.SubGroup_Code ".
											" Left Join Category on Items.Category_Code = Category.Category_Code ".
											" Left Join Salesmen on Sales.Salesman_Code = Salesmen.Ledger_Number ".
											" Left Join Area on Area.Area_Code = Clients.Area_Code ".
											" Left Join LedgerGrp on LedgerGrp.LedgerGrp_Code = Clients.ClientGrp_Code ".
											" Left Join Costcent on Costcent.Costcent_Code = Sales.Costcenter_Code ".
											" Left Join Project on Project.Project_Code = Sales.Project_Code ".
											" Left Join Costcent As Costcent1 on Costcent1.Costcent_Code = Clients.CostCenter_Code ".
											" Left Join Project As Project1 on Project1.Project_Code = Clients.Project_Code ".
											" Left Join Warehouse As Warehouse1 on Warehouse1.Warehouse_Code = SalesDt.Warehouse_Code ";
									  
			if ($projectexist >	0 && $FI_RepRestrictProject == 1)
			  $querydelivery= $querydelivery." Inner 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)
			  $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 Sales.Costcenter_Code like concat(Q2.costcent_code,'%')  ";							 
									   

			$querydelivery= $querydelivery. " Where (Sales.Sale_Reference Is Not Null And Sales.Sale_Reference != '')  ".
											" 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.Client_BusinessType,'-1') between ifNull(".$frombustype.",ifNull(Clients.Client_BusinessType,'-1')) and ifNull(". $tobustype.",ifNull(Clients.Client_BusinessType,'-1')) ".
											" And ifNull(Clients.area_code,'-1') = ifNull(". $fromarea .",ifNull(Clients.area_code,'-1'))  ".
											" And (($promprint=1 and ifNull(SalesDt.SaleDt_Promotion,0) = 1) Or ($promprint=2 and ifNull(SalesDt.SaleDt_Promotion,0) = 0) Or ($promprint=3 and 1 = 1))  ".
											" And (($activeclt=1 and ifNull(Clients.Client_deactivate,0) = 0) or ($activeclt=2 and ifNull(Clients.Client_deactivate,0) = 1) or ($activeclt=3 and 1 = 1)) ".
											" And (($activeitem=1 and ifNull(Items.Item_Active,0) = 1) or ($activeitem=2 and ifNull(Items.Item_Active,0) = 0) or ($activeitem=3 and 1 = 1)) ";

			if ($showsales == 1 && $showretsales == 1)
				$querydelivery= $querydelivery." And SalesDt.Journal_Type in ('SI','SR') ";
			else if ($showsales == 1)
				$querydelivery= $querydelivery." And SalesDt.Journal_Type = 'SI' ";
			else if ($showretsales == 1)
				$querydelivery= $querydelivery." And SalesDt.Journal_Type = 'SR' ";
				
			if ($period == 1) //Option Yearly
				$querydelivery= $querydelivery." And Year(Sales.Sale_Date) between ifNull(year('".$StartReportDate."'), Year(Sales.Sale_Date)) and ifNull(year('".$EndReportDate."'), Year(Sales.Sale_Date))  ";	
			else if ($period == 2) //Option Monthly
				$querydelivery= $querydelivery." And concat(Year(Sales.Sale_Date),LPAD(Month(Sales.Sale_Date), 2, '0')) between concat(ifNull(year('".$StartReportDate."'), Year(Sales.Sale_Date)),ifNull('".str_pad($frommonth,2,'0',STR_PAD_LEFT)."', LPAD(Month(Sales.Sale_Date), 2, '0'))) and concat(ifNull(year('".$EndReportDate."'), Year(Sales.Sale_Date)),ifNull('".str_pad($tomonth,2,'0',STR_PAD_LEFT)."', LPAD(Month(Sales.Sale_Date), 2, '0')))  ";
											   //" And Month(Sales.Sale_Date) between ifNull(".$frommonth.", Month(Sales.Sale_Date)) and ifNull(".$tomonth.", Month(Sales.Sale_Date))  ";	
			else if ($period == 3) //Option Daily
				$querydelivery= $querydelivery." 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'))  ";
			else if ($period == 4) //Option Monthly Comparative
				$querydelivery= $querydelivery." And concat(Year(Sales.Sale_Date),LPAD(Month(Sales.Sale_Date), 2, '0')) between concat(ifNull(year('".$StartReportDate."'), Year(Sales.Sale_Date)),ifNull('".str_pad($frommonth,2,'0',STR_PAD_LEFT)."', LPAD(Month(Sales.Sale_Date), 2, '0'))) and concat(ifNull(year('".$EndReportDate."'), Year(Sales.Sale_Date)),ifNull('".str_pad($tomonth,2,'0',STR_PAD_LEFT)."', LPAD(Month(Sales.Sale_Date), 2, '0')))  ";
											 
			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 ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
			if ($groupexist > 0)
				$querydelivery= $querydelivery." And Items.group_code In (select distinct group_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
				
		}
		
		if ($RowsDirCount > 1 && $j < $RowsDirCount)	
			$querydelivery = $querydelivery." Union ";
			
	}
}	

$querydelivery= $querydelivery."    ) Q1 ";
$querydelivery= $querydelivery."    Order by Groupby1Code, Groupby2Code, Groupby3Code, MasterCode, DetailCode  ";				
$querydelivery= $querydelivery." ) Q2 ".
							   " Group By Groupby1Code, Groupby2Code, Groupby3Code, MasterCode, DetailCode ".
							   " Order by Groupby1Code, Groupby2Code, Groupby3Code, MasterCode, DetailCode  ";
$querydelivery= $querydelivery." ) Q3 ";							   
							   
$querydelivery= $querydelivery." Order by Groupby1Code, Groupby2Code, Groupby3Code, MasterCode, DetailCode  ";				
	
//echo $querydelivery; exit;



$TotalQtyByRow        = 0;
$TotalAmountByRow     = 0;
$ArrayTotalByPeriod   = Array();
$ArrayTotalByMaster   = Array();
$ArrayTotalByGroup1   = Array();
$ArrayTotalByGroup2   = Array();
$ArrayTotalByGroup3   = Array();

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

$MainGroup1 = '';
$MainGroup2 = '';
$MainGroup3 = '';
$groupby1desc = '';
$groupby2desc = '';
$groupby3desc = '';

$RowNum    = 13;
$ColumnNum = 0;

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

    
    // Master By Options Names and Totals
    if (($prtdt == 1 && $prtsubdt == 1) && (($MasterByCode != $rows[6] && $MasterByCode != '') || ($MainGroup3 != $rows[4] && $MainGroup3 != '') || ($MainGroup2 != $rows[2] && $MainGroup2 != '') || ($MainGroup1 != $rows[0] && $MainGroup1 != ''))) {
		    
		// Display Totals By Master	 //Elie Add
		$StyleClass  = $boldrightFormat;
		if ($masterby == 1) $TotalsLabel = 'Total by Client';
		else $TotalsLabel = 'Total by Item';
		DisplayTotals($worksheet, $period, $fromyear, $toyear, $frommonth, $tomonth, $fromdate, $todate, $ArrayTotalByMaster, $DecimalBase, $TotalsLabel, $StyleClass, $ArrayActiveTaxes, $RowNum, $ColumnNum);	
		
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 2;
		$ColumnNum = 0;
			
	}
	
	
	//Group By Options Level 3 Totals
	if ( ($groupby3 != 1 && $prtdt == 1) && (($MainGroup3 != $rows[4] && $MainGroup3 != '') || ($MainGroup2 != $rows[2] && $MainGroup2 != '') || ($MainGroup1 != $rows[0] && $MainGroup1 != ''))  ){
	  
		// Display Totals By Master	
		$StyleClass  = $grouplevel3totalFormatNum1;
		$TotalsLabel = 'Total Group '.$groupby3name.' : '. str_replace('*',' --> ',$groupby3code);
		DisplayTotals($worksheet, $period, $fromyear, $toyear, $frommonth, $tomonth, $fromdate, $todate, $ArrayTotalByGroup3, $DecimalBase, $TotalsLabel, $StyleClass, $ArrayActiveTaxes, $RowNum, $ColumnNum);	

		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 1;
		$ColumnNum = 0;
		
	}
		
	//Group By Options Level 2 Totals
	if ( ($groupby2 != '' && ($prtdt == 1 || $groupby3 != 1)) && (($MainGroup2 != $rows[2] && $MainGroup2 != '') || ($MainGroup1 != $rows[0] && $MainGroup1 != ''))  ){
	  
		// Display Totals By Master	
		$StyleClass  = $grouplevel2totalFormatNum1;
		$TotalsLabel = 'Total Group '.$groupby2name.' : '.$groupby2code;
		DisplayTotals($worksheet, $period, $fromyear, $toyear, $frommonth, $tomonth, $fromdate, $todate, $ArrayTotalByGroup2, $DecimalBase, $TotalsLabel, $StyleClass, $ArrayActiveTaxes, $RowNum, $ColumnNum);	

		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 1;
		$ColumnNum = 0;
			   
	}
		
	//Group By Options Level 1 Totals
	if ( ($groupby1 != '' && ($prtdt == 1 || $groupby2 != '' || $groupby3 != 1)) && ($MainGroup1 != $rows[0] && $MainGroup1 != '')  ){
	  
		// Display Totals By Master	
		$StyleClass  = $grouplevel1totalFormatNum1;
		$TotalsLabel = 'Total Group '.$groupby1name.' : '.$groupby1code;
		DisplayTotals($worksheet, $period, $fromyear, $toyear, $frommonth, $tomonth, $fromdate, $todate, $ArrayTotalByGroup1, $DecimalBase, $TotalsLabel, $StyleClass, $ArrayActiveTaxes, $RowNum, $ColumnNum);	

		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 1;
		$ColumnNum = 0;
	
	}
		
	if ( ($groupby1 != ''  && $MainGroup1 != '' && $MainGroup1 != $rows[0]) || 
		 ($groupby2 != ''  && $MainGroup2 != '' && $MainGroup2 != $rows[2]) ||
		 ($groupby3 != 1   && $MainGroup3 != '' && $MainGroup3 != $rows[4])  )
	{
		$RowNum += 1;
	}
		
	//Group By Options Level 1 Names 
	if ( ($groupby1 != '' && ($prtdt == 1 || $groupby2 != '' || $groupby3 != 1)) && ($MainGroup1 == '' || $MainGroup1 != $rows[0])  ){
			
		$groupby1code = '';
		if ($rows[0] == -1)
		   $groupby1code = '';
		else
            $groupby1code = $rows[0];			
		
		$worksheet->write($RowNum, $ColumnNum, "Group by  ".$groupby1name, $grouplevel1Format);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, str_replace('*',' --> ',$groupby1code)." - ".str_replace('*',' --> ',$rows[1]), $grouplevel1Format);		
				
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 1;
		$ColumnNum = 0;		
						
		$MainGroup1   = $rows[0];
		$groupby1desc = $rows[1];
		
		$MainGroup2   = '';
		$groupby2desc = '';
		$MainGroup3   = '';
		$groupby3desc = '';	
		$MasterByCode = '';
		$MasterByName = '';
		
		unset($ArrayTotalByMaster);
		unset($ArrayTotalByGroup1);		
		unset($ArrayTotalByGroup2);		
		unset($ArrayTotalByGroup3);
		
	}
		
	//Group By Options Level 2 Names 
	if ( ($groupby2 != '' && ($prtdt == 1 || $groupby3 != 1)) && ($MainGroup2 == '' || $MainGroup2 != $rows[2])  ){
			
		$groupby2code = '';
		if ($rows[2] == -1)
		   $groupby2code = '';
		else
              $groupby2code = $rows[2];			
		
		$worksheet->write($RowNum, $ColumnNum, "Group by  ".$groupby2name, $grouplevel2Format);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, str_replace('*',' --> ',$groupby2code)." - ".str_replace('*',' --> ',$rows[3]), $grouplevel2Format);		
				
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 1;
		$ColumnNum = 0;		
						
		$MainGroup2   = $rows[2];
		$groupby2desc = $rows[3];
		
		$MainGroup3   = '';
		$groupby3desc = '';	
		$MasterByCode = '';
		$MasterByName = '';
		
		unset($ArrayTotalByMaster);
		unset($ArrayTotalByGroup2);		
		unset($ArrayTotalByGroup3);

	}
		
	//Group By Options Level 3 Names 
	if ( ($groupby3 != 1 && $prtdt == 1) && ($MainGroup3 == '' || $MainGroup3 != $rows[4])  ){
			
		$groupby3code = '';
		if ($rows[4] == -1)
		   $groupby3code = '';
		else
            $groupby3code = $rows[4];			
		
		$worksheet->write($RowNum, $ColumnNum, "Group by  ".$groupby3name, $grouplevel3Format);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, str_replace('*',' --> ',$groupby3code)." - ".str_replace('*',' --> ',$rows[5]), $grouplevel3Format);		
				
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 2;
		$ColumnNum = 0;
						
		$MainGroup3   = $rows[4];
		$groupby3desc = $rows[5];
			
		$MasterByCode = '';
		$MasterByName = '';
		
		unset($ArrayTotalByMaster);	
		unset($ArrayTotalByGroup3);
	}
	
	if ($MasterByCode == '' || $MasterByCode!= $rows[6]) {
		if($prtsubdt == 1 && $prtdt == 1) {
			$worksheet->write($RowNum, $ColumnNum, $rows[6], $boldleftFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum, $ColumnNum, $rows[7], $boldleftFormat);
			
			//Increment the Rows Counter By 1 and Reset the Columns Counter
			$RowNum += 1;
			$ColumnNum = 0;
		}
    
	    $MasterByCode = $rows[6];
		$MasterByName = $rows[7];
		
		unset($ArrayTotalByMaster);
	}
	
	
	

	if ($prtdt == 1 && $prtsubdt == 0 && ($masterby == 1 || $masterby == 2)) {
		$worksheet->write($RowNum, $ColumnNum, $rows[6], $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $rows[7], $regularleftFormat);
	} 
	else if ($prtdt == 0 && $prtsubdt == 0 && $groupby1 != '' && $groupby2 == '' && $groupby3 == 1) {
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
		$worksheet->write($RowNum, $ColumnNum, str_replace('*',' --> ',$rows[0])."  -  ".str_replace('*',' --> ',$rows[1]), $regularleftFormat);
		$ColumnNum += 1;
	}
	else if ($prtdt == 0 && $prtsubdt == 0 && $groupby2 != '' && $groupby3 == 1) {
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
		$worksheet->write($RowNum, $ColumnNum, str_replace('*',' --> ',$rows[2])."  -  ".str_replace('*',' --> ',$rows[3]), $regularleftFormat);
		$ColumnNum += 1;
	}
	else if ($prtdt == 0 && $prtsubdt == 0 && $groupby3 != 1) {
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
		$worksheet->write($RowNum, $ColumnNum, str_replace('*',' --> ',$rows[4])."  -  ".str_replace('*',' --> ',$rows[5]), $regularleftFormat);
		$ColumnNum += 1;
	}
	else {
		$worksheet->write($RowNum, $ColumnNum, $rows[8], $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $rows[9], $regularleftFormat);
	}	
	
	
	
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $rows[11], $regularrightFormat);
	
	 if ($period == 2 ) {
		$i = 12;
		$TotalQtyByRow       = 0;
		$TotalAmountByRow    = 0;
		$TotalAmountByRowB1  = 0;
		$TotalAmountByRowB2  = 0;
		$TotalStockBalance   = 0;
		$TotalCostPrice      = 0;
		$TotalCostPriceB1    = 0;
		$TotalCostPriceB2    = 0;
		$totalWeight		 = 0;
		$totalSalesMt    	= 0;
		$salesPriceAvg = 0;
		$StockValueB1 = 0;
		$StockValueB2 = 0;
		$stockBalanceMt = 0;
		$projection = 0;

		for ( $y = $fromyear; $y <= $toyear; $y += 1) {
			
			if ($y == $fromyear && $y == $toyear) { $vMFrom = $frommonth; $vMTo = $tomonth; }
			else if ($y == $fromyear) { $vMFrom = $frommonth; $vMTo = 12; }
			else if ($y == $toyear) { $vMFrom = 1; $vMTo = $tomonth; }
			else { $vMFrom = 1; $vMTo = 12; }
			
			for ( $m = $vMFrom; $m <= $vMTo; $m += 1) {
			
					$ColumnNum += 1;
					$worksheet->write($RowNum, $ColumnNum, $rows[$i], $regularrightFormat);
			
					
				
				$TotalQtyByRow     	  = $TotalQtyByRow + $rows[$i];
				$TotalAmountByRow  	  = $TotalAmountByRow + $rows[$i + 3];
				$TotalAmountByRowB1   = $TotalAmountByRowB1 + $rows[$i + 8];
				$TotalAmountByRowB2   = $TotalAmountByRowB2 + $rows[$i + 9];
				$TotalStockBalance    = $TotalStockBalance + $rows[$i + 10];
				$TotalCostPrice       = $TotalCostPrice + $rows[$i + 11];
				$TotalCostPriceB1     = $TotalCostPriceB1 + $rows[$i + 12];
				$TotalCostPriceB2     = $TotalCostPriceB2 + $rows[$i + 13];
								
				$ArrayTotalByPeriod['Qty'][$y.$m]         = $ArrayTotalByPeriod['Qty'][$y.$m] + $rows[$i];
				$ArrayTotalByPeriod['Price'][$y.$m]       = 0;
				$ArrayTotalByPeriod['Amount'][$y.$m]      = $ArrayTotalByPeriod['Amount'][$y.$m] + $rows[$i + 3];
				$ArrayTotalByPeriod['Cost'][$y.$m]        = $ArrayTotalByPeriod['Cost'][$y.$m] + $rows[$i + 4];
				$ArrayTotalByPeriod['TotalCost'][$y.$m]   = $ArrayTotalByPeriod['TotalCost'][$y.$m] + $rows[$i + 5];
							
				$ArrayTotalByMaster['Qty'][$y.$m]         = $ArrayTotalByMaster['Qty'][$y.$m] + $rows[$i];
				$ArrayTotalByMaster['Price'][$y.$m]       = 0;
				$ArrayTotalByMaster['Amount'][$y.$m]      = $ArrayTotalByMaster['Amount'][$y.$m] + $rows[$i + 3];
				$ArrayTotalByMaster['Cost'][$y.$m]        = $ArrayTotalByMaster['Cost'][$y.$m] + $rows[$i + 4];
				$ArrayTotalByMaster['TotalCost'][$y.$m]   = $ArrayTotalByMaster['TotalCost'][$y.$m] + $rows[$i + 5];
							
				$ArrayTotalByGroup1['Qty'][$y.$m]         = $ArrayTotalByGroup1['Qty'][$y.$m] + $rows[$i];
				$ArrayTotalByGroup1['Price'][$y.$m]       = 0;
				$ArrayTotalByGroup1['Amount'][$y.$m]      = $ArrayTotalByGroup1['Amount'][$y.$m] + $rows[$i + 3];
				$ArrayTotalByGroup1['Cost'][$y.$m]        = $ArrayTotalByGroup1['Cost'][$y.$m] + $rows[$i + 4];
				$ArrayTotalByGroup1['TotalCost'][$y.$m]   = $ArrayTotalByGroup1['TotalCost'][$y.$m] + $rows[$i + 5];
							
				$ArrayTotalByGroup2['Qty'][$y.$m]         = $ArrayTotalByGroup2['Qty'][$y.$m] + $rows[$i];
				$ArrayTotalByGroup2['Price'][$y.$m]       = 0;
				$ArrayTotalByGroup2['Amount'][$y.$m]      = $ArrayTotalByGroup2['Amount'][$y.$m] + $rows[$i + 3];
				$ArrayTotalByGroup2['Cost'][$y.$m]        = $ArrayTotalByGroup2['Cost'][$y.$m] + $rows[$i + 4];
				$ArrayTotalByGroup2['TotalCost'][$y.$m]   = $ArrayTotalByGroup2['TotalCost'][$y.$m] + $rows[$i + 5];
							
				$ArrayTotalByGroup3['Qty'][$y.$m]         = $ArrayTotalByGroup3['Qty'][$y.$m] + $rows[$i];
				$ArrayTotalByGroup3['Price'][$y.$m]       = 0;
				$ArrayTotalByGroup3['Amount'][$y.$m]      = $ArrayTotalByGroup3['Amount'][$y.$m] + $rows[$i + 3];
				$ArrayTotalByGroup3['Cost'][$y.$m]        = $ArrayTotalByGroup3['Cost'][$y.$m] + $rows[$i + 4];
				$ArrayTotalByGroup3['TotalCost'][$y.$m]   = $ArrayTotalByGroup3['TotalCost'][$y.$m] + $rows[$i + 5];
				
				$i = $i + 14;
			}

		}
		
		
		
	}
	else if ($period == 3 ) {
		$i = 12;
		$TotalQtyByRow       = 0;
		$TotalAmountByRow    = 0;
		$TotalAmountByRowB1  = 0;
		$TotalAmountByRowB2  = 0;
		$TotalStockBalance   = 0;
		$TotalCostPrice      = 0;
		$TotalCostPriceB1    = 0;
		$TotalCostPriceB2    = 0;
		$totalWeight		 = 0;
		$totalSalesMt    	= 0;
		$salesPriceAvg = 0;
		$StockValueB1 = 0;
		$StockValueB2 = 0;
		$stockBalanceMt = 0;
		$projection = 0;
		$dayscount = dateDiff($fromdate, $todate);
		
		for ( $d = 0; $d <= $dayscount; $d += 1) {	
		
			$ColumnNum += 1;
			$worksheet->write($RowNum, $ColumnNum, $rows[$i], $regularrightFormat);
				
			$TotalQtyByRow     	  = $TotalQtyByRow + $rows[$i];
			$TotalAmountByRow  	  = $TotalAmountByRow + $rows[$i + 3];
			$TotalAmountByRowB1   = $TotalAmountByRowB1 + $rows[$i + 8];
			$TotalAmountByRowB2   = $TotalAmountByRowB2 + $rows[$i + 9];
			$TotalStockBalance    = $TotalStockBalance + $rows[$i + 10];
			$TotalCostPrice       = $TotalCostPrice + $rows[$i + 11];
			$TotalCostPriceB1     = $TotalCostPriceB1 + $rows[$i + 12];
			$TotalCostPriceB2     = $TotalCostPriceB2 + $rows[$i + 13];
			
			$ArrayTotalByPeriod['Qty'][$d]         = $ArrayTotalByPeriod['Qty'][$d] + $rows[$i];
			$ArrayTotalByPeriod['Price'][$d]       = 0;
			$ArrayTotalByPeriod['Amount'][$d]      = $ArrayTotalByPeriod['Amount'][$d] + $rows[$i + 3];
			
						
			$ArrayTotalByMaster['Qty'][$d]         = $ArrayTotalByMaster['Qty'][$d] + $rows[$i];
			$ArrayTotalByMaster['Price'][$d]       = 0;
			$ArrayTotalByMaster['Amount'][$d]      = $ArrayTotalByMaster['Amount'][$d] + $rows[$i + 3];
			
			$ArrayTotalByGroup1['Qty'][$d]         = $ArrayTotalByGroup1['Qty'][$d] + $rows[$i];
			$ArrayTotalByGroup1['Price'][$d]       = 0;
			$ArrayTotalByGroup1['Amount'][$d]      = $ArrayTotalByGroup1['Amount'][$d] + $rows[$i + 3];
						
			$ArrayTotalByGroup2['Qty'][$d]         = $ArrayTotalByGroup2['Qty'][$d] + $rows[$i];
			$ArrayTotalByGroup2['Price'][$d]       = 0;
			$ArrayTotalByGroup2['Amount'][$d]      = $ArrayTotalByGroup2['Amount'][$d] + $rows[$i + 3];
									
			$ArrayTotalByGroup3['Qty'][$d]         = $ArrayTotalByGroup3['Qty'][$d] + $rows[$i];
			$ArrayTotalByGroup3['Price'][$d]       = 0;
			$ArrayTotalByGroup3['Amount'][$d]      = $ArrayTotalByGroup3['Amount'][$d] + $rows[$i + 3];
				
			$ArrayTotalByMaster['totalWeight1'][$d] = $ArrayTotalByMaster['totalWeight1'][$d] +($rows[11]*$rows[$i]);	
			$ArrayTotalByPeriod['totalWeight1'][$d]	= $ArrayTotalByPeriod['totalWeight1'][$d] + ($rows[11]*$rows[$i]);	
			$i = $i + 14;
		}
		
	}
	$ArrayTotalByPeriod['TotalQty']['Qty']     		   = $ArrayTotalByPeriod['TotalQty']['Qty'] + $TotalQtyByRow;
	$ArrayTotalByPeriod['TotalAmount']['Amount']	   = $ArrayTotalByPeriod['TotalAmount']['Amount'] + $TotalAmountByRow;
	
	$ArrayTotalByMaster['TotalQty']['Qty']     		   = $ArrayTotalByMaster['TotalQty']['Qty'] + $TotalQtyByRow;
	$ArrayTotalByMaster['TotalAmount']['Amount']	   = $ArrayTotalByMaster['TotalAmount']['Amount'] + $TotalAmountByRow;
	
	$ArrayTotalByGroup1['TotalQty']['Qty'] 		       = $ArrayTotalByGroup1['TotalQty']['Qty'] + $TotalQtyByRow;
	$ArrayTotalByGroup1['TotalAmount']['Amount']	   = $ArrayTotalByGroup1['TotalAmount']['Amount'] + $TotalAmountByRow;
	
	$ArrayTotalByGroup2['TotalQty']['Qty']   		   = $ArrayTotalByGroup2['TotalQty']['Qty'] + $TotalQtyByRow;
	$ArrayTotalByGroup2['TotalAmount']['Amount']	   = $ArrayTotalByGroup2['TotalAmount']['Amount'] + $TotalAmountByRow;
	
	$ArrayTotalByGroup3['TotalQty']['Qty']       	   = $ArrayTotalByGroup3['TotalQty']['Qty'] + $TotalQtyByRow;
	$ArrayTotalByGroup3['TotalAmount']['Amount']	   = $ArrayTotalByGroup3['TotalAmount']['Amount'] + $TotalAmountByRow;
	
	$totalWeight = $totalWeight + $rows[11]; 
	$ArrayTotalByMaster['totalWeight'] 	= $ArrayTotalByMaster['totalWeight'] + $totalWeight;
	$ArrayTotalByPeriod['totalWeight']	= $ArrayTotalByPeriod['totalWeight'] + $totalWeight;
	$ArrayTotalByGroup1['totalWeight']	= $ArrayTotalByGroup1['totalWeight'] + $totalWeight;
	$ArrayTotalByGroup2['totalWeight']	= $ArrayTotalByGroup2['totalWeight'] + $totalWeight;
	$ArrayTotalByGroup3['totalWeight']	= $ArrayTotalByGroup3['totalWeight'] + $totalWeight;
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $TotalQtyByRow, $regularrightFormat);
	
	$totalSalesMt 							= $TotalQtyByRow * $rows[11];
	$ArrayTotalByMaster['TotalSalesMt'] 	= $ArrayTotalByMaster['TotalSalesMt'] + $totalSalesMt;
	$ArrayTotalByPeriod['TotalSalesMt']		= $ArrayTotalByPeriod['TotalSalesMt'] + $totalSalesMt;
	$ArrayTotalByGroup1['TotalSalesMt']		= $ArrayTotalByGroup1['TotalSalesMt'] + $totalSalesMt;
	$ArrayTotalByGroup2['TotalSalesMt']		= $ArrayTotalByGroup2['TotalSalesMt'] + $totalSalesMt;
	$ArrayTotalByGroup3['TotalSalesMt']		= $ArrayTotalByGroup3['TotalSalesMt'] + $totalSalesMt;
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $totalSalesMt, $regularrightFormat);
	
	$ArrayTotalByMaster['TotalAmountByRowB1'] 		= $ArrayTotalByMaster['TotalAmountByRowB1'] + $TotalAmountByRowB1;
	$ArrayTotalByPeriod['TotalAmountByRowB1']		= $ArrayTotalByPeriod['TotalAmountByRowB1'] + $TotalAmountByRowB1;
	$ArrayTotalByGroup1['TotalAmountByRowB1']		= $ArrayTotalByGroup1['TotalAmountByRowB1'] + $TotalAmountByRowB1;
	$ArrayTotalByGroup2['TotalAmountByRowB1']		= $ArrayTotalByGroup2['TotalAmountByRowB1'] + $TotalAmountByRowB1;
	$ArrayTotalByGroup3['TotalAmountByRowB1']		= $ArrayTotalByGroup3['TotalAmountByRowB1'] + $TotalAmountByRowB1;
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $TotalAmountByRowB1, $regularrightFormat);
	
	$ArrayTotalByMaster['TotalAmountByRowB2'] 		= $ArrayTotalByMaster['TotalAmountByRowB2'] + $TotalAmountByRowB2;
	$ArrayTotalByPeriod['TotalAmountByRowB2']		= $ArrayTotalByPeriod['TotalAmountByRowB2'] + $TotalAmountByRowB2;
	$ArrayTotalByGroup1['TotalAmountByRowB2']		= $ArrayTotalByGroup1['TotalAmountByRowB2'] + $TotalAmountByRowB2;
	$ArrayTotalByGroup2['TotalAmountByRowB2']		= $ArrayTotalByGroup2['TotalAmountByRowB2'] + $TotalAmountByRowB2;
	$ArrayTotalByGroup3['TotalAmountByRowB2']		= $ArrayTotalByGroup3['TotalAmountByRowB2'] + $TotalAmountByRowB2;
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $TotalAmountByRowB2, $regularrightFormat);
	
	$salesPriceAvg = $TotalAmountByRow/$TotalQtyByRow;
	$ArrayTotalByMaster['salesPriceAvg'] 		= $ArrayTotalByMaster['salesPriceAvg'] + $salesPriceAvg;
	$ArrayTotalByPeriod['salesPriceAvg']		= $ArrayTotalByPeriod['salesPriceAvg'] + $salesPriceAvg;
	$ArrayTotalByGroup1['salesPriceAvg']		= $ArrayTotalByGroup1['salesPriceAvg'] + $salesPriceAvg;
	$ArrayTotalByGroup2['salesPriceAvg']		= $ArrayTotalByGroup2['salesPriceAvg'] + $salesPriceAvg;
	$ArrayTotalByGroup3['salesPriceAvg']		= $ArrayTotalByGroup3['salesPriceAvg'] + $salesPriceAvg;
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $salesPriceAvg, $regularrightFormat);
	
	$ArrayTotalByMaster['TotalStockBalance'] 		= $ArrayTotalByMaster['TotalStockBalance'] + $TotalStockBalance;
	$ArrayTotalByPeriod['TotalStockBalance']		= $ArrayTotalByPeriod['TotalStockBalance'] + $TotalStockBalance;
	$ArrayTotalByGroup1['TotalStockBalance']		= $ArrayTotalByGroup1['TotalStockBalance'] + $TotalStockBalance;
	$ArrayTotalByGroup2['TotalStockBalance']		= $ArrayTotalByGroup2['TotalStockBalance'] + $TotalStockBalance;
	$ArrayTotalByGroup3['TotalStockBalance']		= $ArrayTotalByGroup3['TotalStockBalance'] + $TotalStockBalance;
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $TotalStockBalance, $regularrightFormat);
	
	$ArrayTotalByMaster['TotalCostPrice'] 		= $ArrayTotalByMaster['TotalCostPrice'] + $TotalCostPrice;
	$ArrayTotalByPeriod['TotalCostPrice']		= $ArrayTotalByPeriod['TotalCostPrice'] + $TotalCostPrice;
	$ArrayTotalByGroup1['TotalCostPrice']		= $ArrayTotalByGroup1['TotalCostPrice'] + $TotalCostPrice;
	$ArrayTotalByGroup2['TotalCostPrice']		= $ArrayTotalByGroup2['TotalCostPrice'] + $TotalCostPrice;
	$ArrayTotalByGroup3['TotalCostPrice']		= $ArrayTotalByGroup3['TotalCostPrice'] + $TotalCostPrice;	
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $TotalCostPrice, $regularrightFormat);
	
	$StockValueB1 = $TotalCostPriceB1*$TotalStockBalance;		
	$ArrayTotalByMaster['StockValueB1'] 	= $ArrayTotalByMaster['StockValueB1'] + $StockValueB1;
	$ArrayTotalByPeriod['StockValueB1']		= $ArrayTotalByPeriod['StockValueB1'] + $StockValueB1;
	$ArrayTotalByGroup1['StockValueB1']		= $ArrayTotalByGroup1['StockValueB1'] + $StockValueB1;
	$ArrayTotalByGroup2['StockValueB1']		= $ArrayTotalByGroup2['StockValueB1'] + $StockValueB1;
	$ArrayTotalByGroup3['StockValueB1']		= $ArrayTotalByGroup3['StockValueB1'] + $StockValueB1;	
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $StockValueB1, $regularrightFormat);
	
	$StockValueB2 = $TotalCostPriceB2*$TotalStockBalance;		
	$ArrayTotalByMaster['StockValueB2'] 	= $ArrayTotalByMaster['StockValueB2'] + $StockValueB2;
	$ArrayTotalByPeriod['StockValueB2']		= $ArrayTotalByPeriod['StockValueB2'] + $StockValueB2;
	$ArrayTotalByGroup1['StockValueB2']		= $ArrayTotalByGroup1['StockValueB2'] + $StockValueB2;
	$ArrayTotalByGroup2['StockValueB2']		= $ArrayTotalByGroup2['StockValueB2'] + $StockValueB2;
	$ArrayTotalByGroup3['StockValueB2']		= $ArrayTotalByGroup3['StockValueB2'] + $StockValueB2;
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $StockValueB2, $regularrightFormat);
	
	$stockBalanceMt = $TotalStockBalance * $rows[11];
	$ArrayTotalByMaster['stockBalanceMt'] 		= $ArrayTotalByMaster['stockBalanceMt'] + $stockBalanceMt;
	$ArrayTotalByPeriod['stockBalanceMt']		= $ArrayTotalByPeriod['stockBalanceMt'] + $stockBalanceMt;
	$ArrayTotalByGroup1['stockBalanceMt']		= $ArrayTotalByGroup1['stockBalanceMt'] + $stockBalanceMt;
	$ArrayTotalByGroup2['stockBalanceMt']		= $ArrayTotalByGroup2['stockBalanceMt'] + $stockBalanceMt;
	$ArrayTotalByGroup3['stockBalanceMt']		= $ArrayTotalByGroup3['stockBalanceMt'] + $stockBalanceMt;
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $stockBalanceMt, $regularrightFormat);
	
	if($StockValueB1 == 0) $projection = 0;
	else $projection = $TotalAmountByRowB1 / $StockValueB1; 
	$ArrayTotalByMaster['projection'] 		= $ArrayTotalByMaster['projection'] + $projection;
	$ArrayTotalByPeriod['projection']		= $ArrayTotalByPeriod['projection'] + $projection;
	$ArrayTotalByGroup1['projection']		= $ArrayTotalByGroup1['projection'] + $projection;
	$ArrayTotalByGroup2['projection']		= $ArrayTotalByGroup2['projection'] + $projection;
	$ArrayTotalByGroup3['projection']		= $ArrayTotalByGroup3['projection'] + $projection;
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $projection, $regularrightFormat);
	$RowNum += 1;
	$ColumnNum = 0;
}

// Display Totals By Master	
if ($MasterByCode != '' && $prtdt == 1 && $prtsubdt == 1) {
	$RowNum += 1;
	$StyleClass  = $boldrightFormatNum1;
	if ($masterby == 1) $TotalsLabel = 'Total by Client';
	else $TotalsLabel = 'Total by Item';
	DisplayTotals($worksheet, $period, $fromyear, $toyear, $frommonth, $tomonth, $fromdate, $todate, $ArrayTotalByMaster, $DecimalBase, $TotalsLabel, $StyleClass, $ArrayActiveTaxes, $RowNum, $ColumnNum);	
		
	//Increment the Rows Counter By 1 and Reset the Columns Counter
	$RowNum += 2;
	$ColumnNum = 0;
			
}

//Display Totals By Group Level 3 
if ($groupby3 != 1 && $prtdt == 1){

	$StyleClass  = $grouplevel3totalFormatNum1;
	$TotalsLabel = 'Total Group '.$groupby3name.' : '. str_replace('*',' --> ',$groupby3code);
	DisplayTotals($worksheet, $period, $fromyear, $toyear, $frommonth, $tomonth, $fromdate, $todate, $ArrayTotalByGroup3, $DecimalBase, $TotalsLabel, $StyleClass, $ArrayActiveTaxes, $RowNum, $ColumnNum);	

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

//Display Totals By Group Level 2 
if ($groupby2 != '' && ($prtdt == 1 || $groupby3 != 1)){

	$StyleClass  = $grouplevel2totalFormatNum1;
	$TotalsLabel = 'Total Group '.$groupby2name.' : '. str_replace('*',' --> ',$groupby2code);
	DisplayTotals($worksheet, $period, $fromyear, $toyear, $frommonth, $tomonth, $fromdate, $todate, $ArrayTotalByGroup2, $DecimalBase, $TotalsLabel, $StyleClass, $ArrayActiveTaxes, $RowNum, $ColumnNum);	

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

//Display Totals By Group Level 1 
if ($groupby1 != '' && ($prtdt == 1 || $groupby2 != '' || $groupby3 != 1)){

	$StyleClass  = $grouplevel1totalFormatNum1;
	$TotalsLabel = 'Total Group '.$groupby1name.' : '. str_replace('*',' --> ',$groupby1code);
	DisplayTotals($worksheet, $period, $fromyear, $toyear, $frommonth, $tomonth, $fromdate, $todate, $ArrayTotalByGroup1, $DecimalBase, $TotalsLabel, $StyleClass, $ArrayActiveTaxes, $RowNum, $ColumnNum);	

	//Increment the Rows Counter By 1 and Reset the Columns Counter
	$RowNum += 1;
	$ColumnNum = 0;
															
}
		
// Display Totals By Report			
$StyleClass  = $boldrightFormatNum1;
$TotalsLabel = 'Totals';
DisplayTotals($worksheet, $period, $fromyear, $toyear, $frommonth, $tomonth, $fromdate, $todate, $ArrayTotalByPeriod, $DecimalBase, $TotalsLabel, $StyleClass, $ArrayActiveTaxes, $RowNum, $ColumnNum);	
		
//Increment the Rows Counter By 1 and Reset the Columns Counter
$RowNum += 2;
$ColumnNum = 0;



function DisplayTotals($worksheet, $PPeriod, $PFromYear, $PToYear, $PFromMonth, $PToMonth, $PFromDate, $PToDate, $PArrayTotal, $PDecimalBase, $PTotalsLabel, $PStyleClass, $PArrayActiveTaxes, $PRowNum, $PColumnNum) {

	
	$worksheet->write($PRowNum, $PColumnNum, $PTotalsLabel, $PStyleClass);
	$PColumnNum += 1;
	$worksheet->write($PRowNum, $PColumnNum, "Total", $PStyleClass);
	$PColumnNum += 1;
		
	 if ($PPeriod == 2 ) {
		for ( $y = $PFromYear; $y <= $PToYear; $y += 1) {
				
			if ($y == $PFromYear && $y == $PToYear) { $vMFrom = $PFromMonth; $vMTo = $PToMonth; }
			else if ($y == $PFromYear) { $vMFrom = $PFromMonth; $vMTo = 12; }
			else if ($y == $PToYear) { $vMFrom = 1; $vMTo = $PToMonth; }
			else { $vMFrom = 1; $vMTo = 12; }
				
			for ( $m = $vMFrom; $m <= $vMTo; $m += 1) {
				$PColumnNum += 1;
					$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['Qty'][$y.$m], $PStyleClass);
			}

		}
		
		
	}
	else if ($PPeriod == 3 ) {
		$dayscount = dateDiff($PFromDate, $PToDate);
		
		for ( $d = 0; $d <= $dayscount; $d += 1) {
		
				$PColumnNum += 1;
				$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['Qty'][$d], $PStyleClass);
		}
		
	}	
	
	$PColumnNum += 1;
	$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['TotalQty']['Qty'], $PStyleClass);
	$PColumnNum += 1;
	$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['TotalSalesMt'], $PStyleClass);
	$PColumnNum += 1;
	$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['TotalAmountByRowB1'], $PStyleClass);
	$PColumnNum += 1;
	$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['TotalAmountByRowB2'], $PStyleClass);
	$PColumnNum += 1;
	$worksheet->write($PRowNum, $PColumnNum, '-', $PStyleClass);
	$PColumnNum += 1;
	$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['TotalStockBalance'], $PStyleClass);
	$PColumnNum += 1;
	$worksheet->write($PRowNum, $PColumnNum, '-', $PStyleClass);
	$PColumnNum += 1;
	$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['StockValueB1'], $PStyleClass);
	$PColumnNum += 1;
	$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['StockValueB2'], $PStyleClass);
	$PColumnNum += 1;
	$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['stockBalanceMt'], $PStyleClass);
	$PColumnNum += 1;
	$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['projection'], $PStyleClass);
	
	$PColumnNum = 0;
	$PRowNum ++;
	$worksheet->write($PRowNum, $PColumnNum, $PTotalsLabel, $PStyleClass);
	$PColumnNum += 1;
	$worksheet->write($PRowNum, $PColumnNum, "Total MT", $PStyleClass);
	$PColumnNum += 1;
	 if ($PPeriod == 2 ) {
		for ( $y = $PFromYear; $y <= $PToYear; $y += 1) {
				
			if ($y == $PFromYear && $y == $PToYear) { $vMFrom = $PFromMonth; $vMTo = $PToMonth; }
			else if ($y == $PFromYear) { $vMFrom = $PFromMonth; $vMTo = 12; }
			else if ($y == $PToYear) { $vMFrom = 1; $vMTo = $PToMonth; }
			else { $vMFrom = 1; $vMTo = 12; }
				
			for ( $m = $vMFrom; $m <= $vMTo; $m += 1) {
				$PColumnNum += 1;
					$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['Qty'][$y.$m] * $PArrayTotal['totalWeight'], $PStyleClass);
			}

		}
		
		
	}
	else if ($PPeriod == 3 ) {
		$dayscount = dateDiff($PFromDate, $PToDate);
		
		for ( $d = 0; $d <= $dayscount; $d += 1) {
		
				$PColumnNum += 1;
				$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['totalWeight1'][$d], $PStyleClass);
		}
		
	}	
	$PColumnNum += 1;
	$worksheet->writeNumber($PRowNum, $PColumnNum, '', $PStyleClass);
	$PColumnNum += 1;
	$worksheet->writeNumber($PRowNum, $PColumnNum, '', $PStyleClass);
	$PColumnNum += 1;
	$worksheet->writeNumber($PRowNum, $PColumnNum, '', $PStyleClass);
	$PColumnNum += 1;
	$worksheet->writeNumber($PRowNum, $PColumnNum,'', $PStyleClass);
	$PColumnNum += 1;
	$worksheet->write($PRowNum, $PColumnNum,'-', $PStyleClass);
	$PColumnNum += 1;
	$worksheet->writeNumber($PRowNum, $PColumnNum, '', $PStyleClass);
	$PColumnNum += 1;
	$worksheet->write($PRowNum, $PColumnNum, '-', $PStyleClass);
	$PColumnNum += 1;
	$worksheet->writeNumber($PRowNum, $PColumnNum, '', $PStyleClass);
	$PColumnNum += 1;
	$worksheet->writeNumber($PRowNum, $PColumnNum, '', $PStyleClass);
	$PColumnNum += 1;
	$worksheet->writeNumber($PRowNum, $PColumnNum, '', $PStyleClass);
	$PColumnNum += 1;
	$worksheet->writeNumber($PRowNum, $PColumnNum, '', $PStyleClass);
	
	$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 getMonth($month){
     return date('M', mktime(0,0,0,$month,1));
}
function getMonthDisp($month){
     return date('F', mktime(0,0,0,$month,1));
}
function dateDiff($start, $end) {
  $start_ts = strtotime($start);
  $end_ts = strtotime($end);
  $diff = $end_ts - $start_ts;
  return round($diff / 86400);
}
function addDays($startdate, $numdays, $format='dMY') {
	return date($format, strtotime($startdate) + $numdays*60*60*24 + 10000);
}

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

?>