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

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


include("config.inc.php");

//Set Time Execution For the Report
set_time_limit(0);

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


$querylog="select FI_ShowCostPrice,FI_ItemSelling1,FI_ItemSelling2,FI_ItemSelling3,FI_ItemSelling4,FI_ItemSelling5,FI_ItemSelling6 from ".$HTTP_SESSION_VARS['dbLabel'].".login where user_account='".$HTTP_SESSION_VARS['useraccount']."' and Upper(Login_Schema) = Upper('".$HTTP_SESSION_VARS['dbName']."')";
$resultlog=mysql_query($querylog) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
  extract($rows);
}


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

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

$queryPurchasesetup="select PurchaseTTC from PurchaseSetup";
$resultPurchasesetup=mysql_query($queryPurchasesetup,$connection) or die(mysql_error());
while($rowsPurchasesetup=mysql_fetch_array($resultPurchasesetup)){
  extract($rowsPurchasesetup);
}

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

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

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

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

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


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

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

$ReportTitle = '';
if ($transtype == 'PI') 	  $ReportTitle = 'Purchase Report';
else if ($transtype == 'PR')  $ReportTitle = 'Return Purchase Report';
else if ($transtype == 'PIR') $ReportTitle = 'Purchase & Return Purchase Report';
else if ($transtype == 'SI')  $ReportTitle = 'Sales Report';
else if ($transtype == 'SR')  $ReportTitle = 'Return Sales Report';
else if ($transtype == 'SIR') $ReportTitle = 'Sales & Return Sales Report';
else if ($transtype == 'AI')  $ReportTitle = 'Adjustment IN Report';
else if ($transtype == 'AR')  $ReportTitle = 'Adjustment OUT Report';
else if ($transtype == 'PD')  $ReportTitle = 'Production IN Report';
else if ($transtype == 'PDT') $ReportTitle = 'Production OUT Report';
else if ($transtype == 'TRT') $ReportTitle = 'Transfer IN Report';
else if ($transtype == 'TR')  $ReportTitle = 'Transfer OUT Report';


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

$SelectedOptions = "Filter by: \n";
if ($fromdate != '')      $SelectedOptions .= "-> Date From: ". $fromdate . "  To: ". $todate ."\n";	
if ($displaycur != '')    $SelectedOptions .= "-> Currency: ". $displaycur ."\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 ($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 ($fromcategory != '')  $SelectedOptions .= "-> Category From: ". $fromcategory . "  To: ". $tocategory ."\n";
if ($fromproject != '')   $SelectedOptions .= "-> Project From: ". $fromproject . "  To: ". $toproject . "\n";
if ($fromcostcent != '')  $SelectedOptions .= "-> Costcent From: ". $fromcostcent . "  To: ". $tocostcent . "\n";
if ($fromwarehouse != '') $SelectedOptions .= "-> Warehouse From: ". $fromwarehouse . "  To: ". $towarehouse . "\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');
$toproject     = GetSQLValueString($toproject, 'text');
$fromcostcent  = GetSQLValueString($fromcostcent, 'text');
$tocostcent    = GetSQLValueString($tocostcent, 'text');
$fromgroup     = GetSQLValueString($fromgroup, 'text');
$togroup       = GetSQLValueString($togroup, 'text');
$fromsubgroup  = GetSQLValueString($fromsubgroup, 'text');
$tosubgroup    = GetSQLValueString($tosubgroup, 'text');
$fromcategory  = GetSQLValueString($fromcategory, 'text');
$tocategory    = GetSQLValueString($tocategory, 'text');
$fromwarehouse = GetSQLValueString($fromwarehouse, 'text');
$towarehouse   = GetSQLValueString($towarehouse, 'text');
$fromdate	   = GetSQLValueString($fromdate, 'date');
$todate        = GetSQLValueString($todate, 'date');   
$fromclient    = GetSQLValueString($fromclient, 'text');
$toclient      = GetSQLValueString($toclient, 'text');
$fromsupplier  = GetSQLValueString($fromsupplier, 'text');
$tosupplier    = GetSQLValueString($tosupplier, 'text');
$fromsalesman  = GetSQLValueString($fromsalesman, 'text');
$tosalesman    = GetSQLValueString($tosalesman, 'text');  

  
if (empty($transtype)) 	   $transtype = 'SI';	
if (empty($period)) 	   $period = 4;	
if (empty($prtcost)) 	   $prtcost = 0;
if (empty($prtamt)) 	   $prtamt = 0;
if (empty($prtqty))	 	   $prtqty = 1;
if(empty($promprint))      $promprint = 3; 

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

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

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

//Column Title Format  
$columnTitleFormat =& $workbook->addFormat(array('bold'=>1,
  'top'=>1,
  'bottom'=>1 ,
  'size'=>15,
  'color'=>0x0A,
  ));
  
 
  
// Regular Format
$regularleftFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ));
$regularrightFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ));  
  
$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
  ));    
  

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


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




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


$query = '';

$query = $query." Select ProjCostCode, ProjCostDescription, GroupCode, GroupName, Sum(TransQty) As TransQty, ".
				" Sum(ifNull(TransAmount,0)) As TransAmount, Sum(ifNull(MeanCostAmount,0)) As MeanCostAmount ".
				" From ( ";


if ($transtype == 'PI' || $transtype == 'PR'|| $transtype == 'PIR') {
	$query = $query." Select ProjCostCode, ProjCostDescription, GroupCode, GroupName, TransQty, ".
					" (ifNull(TransPrice,0)*TransQty) As TransAmount, (ifNull(MeanCostAmount,0)*TransQty) As MeanCostAmount ".
					" From ( ";
	
	$query = $query." Select if($period=4, ifNull(Purchases.Project_Code, 'None'), ifNull(Purchases.Costcent_Code, 'None')) As ProjCostCode,   ".
					" if($period=4, Project.Project_Name, Costcent.Costcent_Name) As ProjCostDescription,  ".
					" Items.Group_Code As GroupCode, Grouping.Group_Name As GroupName,  ".
					" if(PurchasesDt.Journal_Type = 'PI', ifNull(PurchasesDt.PurchaseDt_Quantity,0), (-1)*ifNull(PurchasesDt.PurchaseDt_Quantity,0)) As TransQty,  ".
					" if($PurchaseTTC=1, if(ifNull(PurchasesDt.PurchaseDt_PriceTTC,0)=0, 0, ifNull( (((if(upper($curr)='".$Base1."', ifNull(PurchasesDt.PurchaseDt_PriceTTCBase1,0), if(upper($curr)='".$Base2."', ifNull(PurchasesDt.PurchaseDt_PriceTTCBase2,0), ifNull(PurchasesDt.PurchaseDt_PriceTTC,0))) - ".
					"                  (ifNull(if(upper($curr)='".$Base1."', ifNull(PurchasesDt.PurchaseDt_PriceTTCBase1,0), if(upper($curr)='".$Base2."', ifNull(PurchasesDt.PurchaseDt_PriceTTCBase2,0), ifNull(PurchasesDt.PurchaseDt_PriceTTC,0))),0) * ifNull(PurchasesDt.PurchaseDt_DiscountPer, 0)/100))*PurchasesDt.PurchaseDt_Quantity - ".
					"                  ifNull(if(upper($curr)='".$Base1."', ifNull(PurchasesDt.PurchaseDt_DiscountBase1,0), if(upper($curr)='".$Base2."', ifNull(PurchasesDt.PurchaseDt_DiscountBase2,0), ifNull(PurchasesDt.PurchaseDt_DiscountAmount,0))),0))/PurchasesDt.PurchaseDt_Quantity) ,0)),  ".
					" if(ifNull(PurchasesDt.PurchaseDt_Price,0)=0, 0, ifNull( (((if(upper($curr)='".$Base1."', ifNull(PurchasesDt.PurchaseDt_PriceBase1,0), if(upper($curr)='".$Base2."', ifNull(PurchasesDt.PurchaseDt_PriceBase2,0), ifNull(PurchasesDt.PurchaseDt_Price,0))) - ".
					"         (ifNull(if(upper($curr)='".$Base1."', ifNull(PurchasesDt.PurchaseDt_PriceBase1,0), if(upper($curr)='".$Base2."', ifNull(PurchasesDt.PurchaseDt_PriceBase2,0), ifNull(PurchasesDt.PurchaseDt_Price,0))),0) * ifNull(PurchasesDt.PurchaseDt_DiscountPer, 0)/100))*PurchasesDt.PurchaseDt_Quantity - ".
					"         ifNull(if(upper($curr)='".$Base1."', ifNull(PurchasesDt.PurchaseDt_DiscountBase1,0), if(upper($curr)='".$Base2."', ifNull(PurchasesDt.PurchaseDt_DiscountBase2,0), ifNull(PurchasesDt.PurchaseDt_DiscountAmount,0))),0))/PurchasesDt.PurchaseDt_Quantity) ,0)) ) As TransPrice,  ";

	if ($prtcost == 1)
		$query= $query." ifNull(MeanCostCur(PurchasesDt.Item_Code, concat(date_format(Purchases.Purchase_Date,'%Y-%m-%d'), ' 23:59:59'), if(upper($curr)='".$Base1."', 1, if(upper($curr)='".$Base2."',2, 1)), Purchases.Currency_Code, if(upper($curr)='".$Base1."', '',if(upper($curr)='".$Base2."','',items.currency_code ))),0) MeanCostAmount ";
	else
		$query= $query." 0 As MeanCostAmount ";
	
					
	$query= $query. " From PurchasesDt  ".
					" Inner Join Purchases on PurchasesDt.Purchase_ID = Purchases.Purchase_ID ".
					" Inner Join Items on PurchasesDt.Item_Code = Items.Item_Code ".
					" Left Join Grouping on Items.Group_Code = Grouping.Group_Code ".
					" Left Join Costcent on Costcent.Costcent_Code = Purchases.Costcent_Code ".
					" Left Join Project on Project.Project_Code = Purchases.Project_Code ";

	if ($projectexist >	0 && $FI_RepRestrictProject == 1)
		$query= $query.$QueryRestrictPrj." on Purchases.project_code like concat(Q4.project_code,'%') ";
								 
	if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
		$query= $query.$QueryRestrictCst." on Purchases.Costcent_Code like concat(Q5.costcent_code,'%') ";		 
					   
	
	$query= $query.	" Where 1 = 1  ".
					" And Date_format(Purchases.Purchase_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(Purchases.Purchase_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Purchases.Purchase_Date,'%Y-%m-%d'))  ".
					" And Items.Item_Code between ifNull(".$fromitem.",Items.Item_Code) and ifNull(". $toitem.",Items.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(Items.Category_Code,'-1') between ifNull(". $fromcategory .",ifNull(Items.Category_Code,'-1')) and ifNull(". $tocategory.",ifNull(Items.Category_Code,'-1'))  ".
					" And ifNull(Purchases.Supplier_Code,'-1') between ifNull(".$fromsupplier.", ifNull(Purchases.Supplier_Code,'-1')) and ifNull(". $tosupplier.", ifNull(Purchases.Supplier_Code,'-1')) ".
					" And ifNull(Purchases.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Purchases.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(Purchases.Project_Code,'-1')) ".
					" And ifNull(Purchases.Costcent_Code,'-1') between ifNull(".$fromcostcent.",ifNull(Purchases.Costcent_Code,'-1')) and ifNull(". $tocostcent.",ifNull(Purchases.Costcent_Code,'-1')) ".
					" And ifNull(PurchasesDt.warehouse_code,'-1') between ifNull(".$fromwarehouse.",ifNull(PurchasesDt.warehouse_code,'-1')) and ifNull(". $towarehouse.",ifNull(PurchasesDt.warehouse_code,'-1')) ";
				
				
	if ($transtype == 'PIR')     $query= $query." 	And ifNull(Purchases.Journal_Type,'PI') in ('PI','PR') ";
	else if ($transtype == 'PI') $query= $query."	And ifNull(Purchases.Journal_Type,'PI') in ('PI') ";
	else if ($transtype == 'PR') $query= $query."	And ifNull(Purchases.Journal_Type,'PR') in ('PR') ";
	  
	if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
		$query= $query." 	And PurchasesDt.warehouse_code In (select distinct warehouse_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  
	if ($groupexist > 0)
			$query= $query." 	And Items.group_code In (select distinct group_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  

	$query= $query." 	Order by ProjCostCode, GroupCode ";
	$query= $query." ) Q1 ";
	
	
}

else if (($transtype == 'SI' || $transtype == 'SR'|| $transtype == 'SIR') && $Module_Delivery == 1) {
	$query = $query." Select ProjCostCode, ProjCostDescription, GroupCode, GroupName, TransQty, ".
					" (ifNull(TransPrice,0)*TransQty) As TransAmount, (ifNull(MeanCostAmount,0)*TransQty) As MeanCostAmount ".
					" From ( ";
	
	$query = $query." Select if($period=4, ifNull(Sales.Project_Code, 'None'), ifNull(Sales.Costcenter_Code, 'None')) As ProjCostCode,   ".
					" if($period=4, Project.Project_Name, Costcent.Costcent_Name) As ProjCostDescription,  ".
					" Items.Group_Code As GroupCode, Grouping.Group_Name As GroupName,  ".
					" if(SalesDt.Journal_Type = 'SI', ifNull(SalesDt.SaleDt_Quantity,0), (-1)*ifNull(SalesDt.SaleDt_Quantity,0)) As TransQty,  ".
					" if($SalesTTC=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))/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.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))/SalesDt.SaleDt_Quantity) ,0)) ) As TransPrice,  ";

	if ($prtcost == 1)
		$query= $query." ifNull(MeanCostCur(SalesDt.Item_Code, concat(date_format(Sales.Sale_Date,'%Y-%m-%d'), ' 23:59:59'), if(upper($curr)='".$Base1."', 1, if(upper($curr)='".$Base2."',2, 1)), Sales.Currency_Code, if(upper($curr)='".$Base1."', '',if(upper($curr)='".$Base2."','',items.currency_code ))),0) MeanCostAmount ";
	else
		$query= $query." 0 As MeanCostAmount ";
	
					
	$query= $query. " From SalesDt  ".
					" Inner Join Sales on SalesDt.Sale_ID = Sales.Sale_ID ".
					" Inner Join Items on SalesDt.Item_Code = Items.Item_Code ".
					" Left Join Grouping on Items.Group_Code = Grouping.Group_Code ".
					" Left Join Costcent on Costcent.Costcent_Code = Sales.Costcenter_Code ".
					" Left Join Project on Project.Project_Code = Sales.Project_Code ";
					
	if ($projectexist >	0 && $FI_RepRestrictProject == 1)
		$query= $query.$QueryRestrictPrj." on Sales.project_code like concat(Q4.project_code,'%') ";
								 
	if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
		$query= $query.$QueryRestrictCst." on Sales.Costcenter_Code like concat(Q5.costcent_code,'%') ";		 
					   
	
	$query= $query.	" Where 1 = 1  ".
					" And Date_format(Sales.Sale_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(Sales.Sale_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Sales.Sale_Date,'%Y-%m-%d'))  ".
					" And Items.Item_Code between ifNull(".$fromitem.",Items.Item_Code) and ifNull(". $toitem.",Items.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(Items.Category_Code,'-1') between ifNull(". $fromcategory .",ifNull(Items.Category_Code,'-1')) and ifNull(". $tocategory.",ifNull(Items.Category_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.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(Sales.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Sales.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(Sales.Project_Code,'-1')) ".
					" And ifNull(Sales.Costcenter_Code,'-1') between ifNull(".$fromcostcent.",ifNull(Sales.Costcenter_Code,'-1')) and ifNull(". $tocostcent.",ifNull(Sales.Costcenter_Code,'-1')) ".
					" And ifNull(SalesDt.warehouse_code,'-1') between ifNull(".$fromwarehouse.",ifNull(SalesDt.warehouse_code,'-1')) and ifNull(". $towarehouse.",ifNull(SalesDt.warehouse_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))  ";
				
				
	if ($transtype == 'SIR')     $query= $query." 	And ifNull(Sales.Journal_Type,'SI') in ('SI','SR') ";
	else if ($transtype == 'SI') $query= $query."	And ifNull(Sales.Journal_Type,'SI') in ('SI') ";
	else if ($transtype == 'SR') $query= $query."	And ifNull(Sales.Journal_Type,'SR') in ('SR') ";
	  
	if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
		$query= $query." 	And SalesDt.warehouse_code In (select distinct warehouse_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  
	if ($groupexist > 0)
		$query= $query." 	And Items.group_code In (select distinct group_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  

	$query= $query." 	Order by ProjCostCode, GroupCode  ";
	$query= $query." ) Q1 ";


}

else if (($transtype == 'SI' || $transtype == 'SR'|| $transtype == 'SIR') && $Module_Delivery == 0) {

	$query = $query." Select ProjCostCode, ProjCostDescription, GroupCode, GroupName, TransQty, ".
					" (ifNull(TransPrice,0)*TransQty) As TransAmount, (ifNull(MeanCostAmount,0)*TransQty) As MeanCostAmount ".
					" From ( ";
	
	$query = $query." Select if($period=4, ifNull(Deliveries.Project_Code, 'None'), ifNull(Deliveries.Costcent_Code, 'None')) As ProjCostCode,   ".
					" if($period=4, Project.Project_Name, Costcent.Costcent_Name) As ProjCostDescription,  ".
					" Items.Group_Code As GroupCode, Grouping.Group_Name As GroupName,  ".
					" if(DeliveryDt.Journal_Type = 'DI', ifNull(DeliveryDt.DeliveryDt_Quantity,0), (-1)*ifNull(DeliveryDt.DeliveryDt_Quantity,0)) As TransQty,  ".
					" if($SalesTTC=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))/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.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))/DeliveryDt.DeliveryDt_Quantity) ,0)) ) As TransPrice,  ";

	if ($prtcost == 1)
		$query= $query." ifNull(MeanCostCur(DeliveryDt.Item_Code, concat(date_format(Deliveries.Delivery_Date,'%Y-%m-%d'), ' 23:59:59'), if(upper($curr)='".$Base1."', 1, if(upper($curr)='".$Base2."',2, 1)), Deliveries.Currency_Code, if(upper($curr)='".$Base1."', '',if(upper($curr)='".$Base2."','',items.currency_code ))),0) MeanCostAmount ";
	else
		$query= $query." 0 As MeanCostAmount ";
	
					
	$query= $query. " From DeliveryDt  ".
					" Inner Join Deliveries on DeliveryDt.Delivery_ID = Deliveries.Delivery_ID ".
					" Inner Join Items on DeliveryDt.Item_Code = Items.Item_Code ".
					" Left Join Grouping on Items.Group_Code = Grouping.Group_Code ".
					" Left Join Costcent on Costcent.Costcent_Code = Deliveries.Costcent_Code ".
					" Left Join Project on Project.Project_Code = Deliveries.Project_Code ";

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

	$query= $query.	" Where 1 = 1  ".
					" And Date_format(Deliveries.Delivery_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(Deliveries.Delivery_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Deliveries.Delivery_Date,'%Y-%m-%d'))  ".
					" And Items.Item_Code between ifNull(".$fromitem.",Items.Item_Code) and ifNull(". $toitem.",Items.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(Items.Category_Code,'-1') between ifNull(". $fromcategory .",ifNull(Items.Category_Code,'-1')) and ifNull(". $tocategory.",ifNull(Items.Category_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.Client_Code,'-1') between ifNull(".$fromclient.",ifNull(Deliveries.Client_Code,'-1')) and ifNull(". $toclient.",ifNull(Deliveries.Client_Code,'-1')) ".
					" And ifNull(Deliveries.Salesman_Code,'-1') between ifNull(".$fromsalesman.",ifNull(Deliveries.Salesman_Code,'-1')) and ifNull(". $tosalesman.",ifNull(Deliveries.Salesman_Code,'-1')) ".
					" And ifNull(Deliveries.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Deliveries.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(Deliveries.Project_Code,'-1')) ".
					" And ifNull(Deliveries.Costcent_Code,'-1') between ifNull(".$fromcostcent.",ifNull(Deliveries.Costcent_Code,'-1')) and ifNull(". $tocostcent.",ifNull(Deliveries.Costcent_Code,'-1')) ".
					" And ifNull(DeliveryDt.warehouse_code,'-1') between ifNull(".$fromwarehouse.",ifNull(DeliveryDt.warehouse_code,'-1')) and ifNull(". $towarehouse.",ifNull(DeliveryDt.warehouse_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))  ";
				
				
	if ($transtype == 'SIR')     $query= $query." 	And ifNull(Deliveries.Journal_Type, 'DI') in ('DI','DR') ";
	else if ($transtype == 'SI') $query= $query."	And ifNull(Deliveries.Journal_Type, 'DI') in ('DI') ";
	else if ($transtype == 'SR') $query= $query."	And ifNull(Deliveries.Journal_Type, 'DR') in ('DR') ";
	  
	if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
		$query= $query." 	And DeliveryDt.warehouse_code In (select distinct warehouse_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  
	if ($groupexist > 0)
		$query= $query." 	And Items.group_code In (select distinct group_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  

	$query= $query." 	Order by ProjCostCode, GroupCode  ";				
	$query= $query." ) Q1 ";
	
}


else if ($transtype == 'TR' || $transtype == 'TRT') {

	$query = $query." Select ProjCostCode, ProjCostDescription, GroupCode, GroupName, TransQty, ".
					" (ifNull(TransPrice,0)*TransQty) As TransAmount, (ifNull(MeanCostAmount,0)*TransQty) As MeanCostAmount ".
					" From ( ";
	
	$query = $query." Select if($period=4, ifNull(if('".$transtype."'='TR', Transfer.Project_Code1, Transfer.Project_Code2), 'None'), ifNull(if('".$transtype."'='TR', Transfer.Costcenter_Code, ToCostcenter_Code), 'None')) As ProjCostCode,   ".
					" if($period=4, Project.Project_Name, Costcent.Costcent_Name) As ProjCostDescription,  ".
					" Items.Group_Code As GroupCode, Grouping.Group_Name As GroupName,  ".
					" ifNull(TransferDt.TransferDt_Quantity,0) As TransQty,  ".
					" if(upper($curr)='".$Base1."', TransferDt.TransferDt_PriceBase1, if(upper($curr)='".$Base2."',TransferDt.TransferDt_PriceBase2, TransferDt.TransferDt_Price)) As TransPrice, ";

	if ($prtcost == 1)
		$query= $query." ifNull(MeanCostCur(TransferDt.Item_Code, concat(date_format(Transfer.Transfer_Date,'%Y-%m-%d'), ' 23:59:59'), if(upper($curr)='".$Base1."', 1, if(upper($curr)='".$Base2."',2, 1)), TransferDt.Cur_Code, if(upper($curr)='".$Base1."', '',if(upper($curr)='".$Base2."','',items.currency_code ))),0) MeanCostAmount ";
	else
		$query= $query." 0 As MeanCostAmount ";
	
					
	$query= $query. " From TransferDt  ".
					" Inner Join Transfer on TransferDt.Transfer_ID = Transfer.Transfer_ID ".
					" Inner Join Items on TransferDt.Item_Code = Items.Item_Code ".
					" Left Join Grouping on Items.Group_Code = Grouping.Group_Code ";
					
	if ($transtype == 'TR')	{
		$query= $query." Left Join Costcent on Costcent.Costcent_Code = Transfer.Costcenter_Code ".
					   " Left Join Project on Project.Project_Code = Transfer.Project_Code1 ";
	} else {
		$query= $query." Left Join Costcent on Costcent.Costcent_Code = Transfer.ToCostcenter_Code ".
					   " Left Join Project on Project.Project_Code = Transfer.Project_Code2 ";
	}
					   

	if ($projectexist >	0 && $FI_RepRestrictProject == 1)
		$query= $query.$QueryRestrictPrj." on Transfer.project_code1 like concat(Q4.project_code,'%') ";
								 
	if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
		$query= $query.$QueryRestrictCst." on Transfer.Costcenter_Code like concat(Q5.costcent_code,'%') ";		 
					   
	
	$query= $query.	" Where 1 = 1  ".
					" And Date_format(Transfer.Transfer_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(Transfer.Transfer_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Transfer.Transfer_Date,'%Y-%m-%d'))  ".
					" And Items.Item_Code between ifNull(".$fromitem.",Items.Item_Code) and ifNull(". $toitem.",Items.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(Items.Category_Code,'-1') between ifNull(". $fromcategory .",ifNull(Items.Category_Code,'-1')) and ifNull(". $tocategory.",ifNull(Items.Category_Code,'-1'))  ".
					" And ifNull(Transfer.Project_Code1,'-1') between ifNull(".$fromproject.",ifNull(Transfer.Project_Code1,'-1')) and ifNull(". $toproject.",ifNull(Transfer.Project_Code1,'-1')) ".
					" And ifNull(Transfer.Costcenter_Code,'-1') between ifNull(".$fromcostcent.",ifNull(Transfer.Costcenter_Code,'-1')) and ifNull(". $tocostcent.",ifNull(Transfer.Costcenter_Code,'-1')) ".
					" And (( ifNull(TransferDt.TransferDt_WarehouseFrom,'-1') = ifNull(". $fromwarehouse .",ifNull(TransferDt.TransferDt_WarehouseFrom,'-1'))) or   ".
					" 	   ( ifNull(TransferDt.TransferDt_WarehouseTo,'-1') = ifNull(". $towarehouse .",ifNull(TransferDt.TransferDt_WarehouseTo,'-1'))) )   ";
					
				
				
	if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
		$query= $query." 	And TransferDt.TransferDt_WarehouseFrom In (select distinct warehouse_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  
	if ($groupexist > 0)
		$query= $query." 	And Items.group_code In (select distinct group_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  

	$query= $query." 	Order by ProjCostCode, GroupCode  ";
	$query= $query." ) Q1 ";
	
}

else if ($transtype == 'AI' || $transtype == 'AR') {

	$query = $query." Select ProjCostCode, ProjCostDescription, GroupCode, GroupName, TransQty, ".
					" (ifNull(TransPrice,0)*TransQty) As TransAmount, (ifNull(MeanCostAmount,0)*TransQty) As MeanCostAmount ".
					" From ( ";
	
	$query = $query." Select if($period=4, ifNull(Adjustment.Project_Code, 'None'), ifNull(Adjustment.Costcent_Code, 'None')) As ProjCostCode,   ".
					" if($period=4, Project.Project_Name, Costcent.Costcent_Name) As ProjCostDescription,  ".
					" Items.Group_Code As GroupCode, Grouping.Group_Name As GroupName,  ".
					" if(AdjustmentDt.Journal_Type = 'AI', ifNull(AdjustmentDt.AdjustmentDt_Quantity,0), ifNull(AdjustmentDt.AdjustmentDt_Quantity,0)) As TransQty,  ".
					" if(upper($curr)='".$Base1."', AdjustmentDt.AdjustmentDt_PriceBase1, if(upper($curr)='".$Base2."',AdjustmentDt.AdjustmentDt_PriceBase2, AdjustmentDt.AdjustmentDt_Price)) As TransPrice, ";

	if ($prtcost == 1)
		$query= $query." ifNull(MeanCostCur(AdjustmentDt.Item_Code, concat(date_format(Adjustment.Adjustment_Date,'%Y-%m-%d'), ' 23:59:59'), if(upper($curr)='".$Base1."', 1, if(upper($curr)='".$Base2."',2, 1)), Adjustment.Currency_Code, if(upper($curr)='".$Base1."', '',if(upper($curr)='".$Base2."','',items.currency_code ))),0) MeanCostAmount ";
	else
		$query= $query." 0 As MeanCostAmount ";
	
					
	$query= $query. " From AdjustmentDt  ".
					" Inner Join Adjustment on AdjustmentDt.Adjustment_ID = Adjustment.Adjustment_ID ".
					" Inner Join Items on AdjustmentDt.Item_Code = Items.Item_Code ".
					" Left Join Grouping on Items.Group_Code = Grouping.Group_Code ".
					" Left Join Costcent on Costcent.Costcent_Code = Adjustment.Costcent_Code ".
					" Left Join Project on Project.Project_Code = Adjustment.Project_Code ";

	if ($projectexist >	0 && $FI_RepRestrictProject == 1)
		$query= $query.$QueryRestrictPrj." on Adjustment.project_code like concat(Q4.project_code,'%') ";
								 
	if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
		$query= $query.$QueryRestrictCst." on Adjustment.Costcent_Code like concat(Q5.costcent_code,'%') ";		 
					   
	
	$query= $query.	" Where 1 = 1  ".
					" And Date_format(Adjustment.Adjustment_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(Adjustment.Adjustment_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Adjustment.Adjustment_Date,'%Y-%m-%d'))  ".
					" And Items.Item_Code between ifNull(".$fromitem.",Items.Item_Code) and ifNull(". $toitem.",Items.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(Items.Category_Code,'-1') between ifNull(". $fromcategory .",ifNull(Items.Category_Code,'-1')) and ifNull(". $tocategory.",ifNull(Items.Category_Code,'-1'))  ".
					" And ifNull(Adjustment.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Adjustment.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(Adjustment.Project_Code,'-1')) ".
					" And ifNull(Adjustment.Costcent_Code,'-1') between ifNull(".$fromcostcent.",ifNull(Adjustment.Costcent_Code,'-1')) and ifNull(". $tocostcent.",ifNull(Adjustment.Costcent_Code,'-1')) ".
					" And ifNull(AdjustmentDt.warehouse_code,'-1') between ifNull(".$fromwarehouse.",ifNull(AdjustmentDt.warehouse_code,'-1')) and ifNull(". $towarehouse.",ifNull(AdjustmentDt.warehouse_code,'-1')) ";
				
				
	if ($transtype == 'AI')      $query= $query."	And ifNull(AdjustmentDt.AdjustmentDt_Quantity,0) >= 0 ";
	else if ($transtype == 'AR') $query= $query."	And ifNull(AdjustmentDt.AdjustmentDt_Quantity,0) < 0 ";
	  
	if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
		$query= $query." 	And AdjustmentDt.warehouse_code In (select distinct warehouse_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  
	if ($groupexist > 0)
		$query= $query." 	And Items.group_code In (select distinct group_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  

	$query= $query." 	Order by ProjCostCode, GroupCode  ";
	$query= $query." ) Q1 ";
	
}



else if ($transtype == 'PD' || $transtype == 'PDT') {

	$query = $query." Select ProjCostCode, ProjCostDescription, GroupCode, GroupName, TransQty, ".
					" (ifNull(TransPrice,0)*TransQty) As TransAmount, (ifNull(MeanCostAmount,0)*TransQty) As MeanCostAmount ".
					" From ( ";
	
	if ($transtype == 'PDT') {
		$query = $query." Select if($period=4, ifNull(Production.Project_Code, 'None'), ifNull(Production.Costcenter_Code, 'None')) As ProjCostCode,   ".
						" if($period=4, Project.Project_Name, Costcent.Costcent_Name) As ProjCostDescription,  ".
						" Items.Group_Code As GroupCode, Grouping.Group_Name As GroupName,  ".
						" (-1)*ifNull(ProductionDt.ProductionDt_Quantity,0) As TransQty,  ".
						" if(upper($curr)='".$Base1."',ProductionDt.ProductionDt_CostBase1,if(upper($curr)='".$Base2."',ProductionDt.ProductionDt_CostBase2, ProductionDt.ProductionDt_Cost)) As TransPrice, ";

		if ($prtcost == 1)
			$query= $query." ifNull(MeanCostCur(ProductionDt.Item_Code, concat(date_format(Production.Production_Date,'%Y-%m-%d'), ' 23:59:59'), if(upper($curr)='".$Base1."', 1, if(upper($curr)='".$Base2."',2, 1)), Production.Currency_Code, if(upper($curr)='".$Base1."', '',if(upper($curr)='".$Base2."','',items.currency_code ))),0) MeanCostAmount ";
		else
			$query= $query." 0 As MeanCostAmount ";
		
						
		$query= $query. " From ProductionDt  ".
						" Inner Join Production on ProductionDt.Production_ID = Production.Production_ID ".
						" Inner Join Items on ProductionDt.Item_Code = Items.Item_Code ".
						" Left Join Grouping on Items.Group_Code = Grouping.Group_Code ".
						" Left Join Costcent on Costcent.Costcent_Code = Production.Costcenter_Code ".
						" Left Join Project on Project.Project_Code = Production.Project_Code ";
						
		if ($projectexist >	0 && $FI_RepRestrictProject == 1)
			$query= $query.$QueryRestrictPrj." on Production.project_code like concat(Q4.project_code,'%') ";
									 
		if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
			$query= $query.$QueryRestrictCst." on Production.Costcenter_Code like concat(Q5.costcent_code,'%') ";		 
						   
		
		$query= $query.	" Where (Production.Production_Reference Is Not Null And Production.Production_Reference != '')  ".
						" And Date_format(Production.Production_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(Production.Production_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Production.Production_Date,'%Y-%m-%d'))  ".
						" And Items.Item_Code between ifNull(".$fromitem.",Items.Item_Code) and ifNull(". $toitem.",Items.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(Items.Category_Code,'-1') between ifNull(". $fromcategory .",ifNull(Items.Category_Code,'-1')) and ifNull(". $tocategory.",ifNull(Items.Category_Code,'-1'))  ".
						" And ifNull(Production.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Production.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(Production.Project_Code,'-1')) ".
						" And ifNull(Production.Costcenter_Code,'-1') between ifNull(".$fromcostcent.",ifNull(Production.Costcenter_Code,'-1')) and ifNull(". $tocostcent.",ifNull(Production.Costcenter_Code,'-1')) ".
						" And ifNull(ProductionDt.warehouse_code,'-1') between ifNull(".$fromwarehouse.",ifNull(ProductionDt.warehouse_code,'-1')) and ifNull(". $towarehouse.",ifNull(ProductionDt.warehouse_code,'-1')) ";
					
					
		if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
			$query= $query." 	And ProductionDt.warehouse_code In (select distinct warehouse_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  
		if ($groupexist > 0)
			$query= $query." 	And Items.group_code In (select distinct group_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  
	}
	else if ($transtype == 'PD') {
		$query = $query." Select if($period=4, ifNull(Production.Project_Code, 'None'), ifNull(Production.Costcenter_Code, 'None')) As ProjCostCode,   ".
						" if($period=4, Project.Project_Name, Costcent.Costcent_Name) As ProjCostDescription,  ".
						" Items.Group_Code As GroupCode, Grouping.Group_Name As GroupName,  ".
						" ifNull(Production.Production_Quantity,0) As TransQty,  ".
						" if(upper($curr)='".$Base1."',Production.Production_TotalBase1,if(upper($curr)='".$Base2."',Production.Production_TotalBase2, Production.Production_Total))/ifNull(Production.Production_Quantity,1) As TransPrice, ";

		if ($prtcost == 1)
			$query= $query." ifNull(MeanCostCur(Production.Item_Code, concat(date_format(Production.Production_Date,'%Y-%m-%d'), ' 23:59:59'), if(upper($curr)='".$Base1."', 1, if(upper($curr)='".$Base2."',2, 1)), Production.Currency_Code, if(upper($curr)='".$Base1."', '',if(upper($curr)='".$Base2."','',items.currency_code ))),0) MeanCostAmount ";
		else
			$query= $query." 0 As MeanCostAmount ";
		
						
		$query= $query. " From Production  ".
						" Inner Join Items on Production.Item_Code = Items.Item_Code ".
						" Left Join Grouping on Items.Group_Code = Grouping.Group_Code ".
						" Left Join Costcent on Costcent.Costcent_Code = Production.Costcenter_Code ".
						" Left Join Project on Project.Project_Code = Production.Project_Code ";
						
		if ($projectexist >	0 && $FI_RepRestrictProject == 1)
			$query= $query.$QueryRestrictPrj." on Production.project_code like concat(Q4.project_code,'%') ";
									 
		if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
			$query= $query.$QueryRestrictCst." on Production.Costcenter_Code like concat(Q5.costcent_code,'%') ";		 
						   
		
		$query= $query.	" Where 1 = 1  ".
						" And Date_format(Production.Production_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(Production.Production_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Production.Production_Date,'%Y-%m-%d'))  ".
						" And Items.Item_Code between ifNull(".$fromitem.",Items.Item_Code) and ifNull(". $toitem.",Items.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(Items.Category_Code,'-1') between ifNull(". $fromcategory .",ifNull(Items.Category_Code,'-1')) and ifNull(". $tocategory.",ifNull(Items.Category_Code,'-1'))  ".
						" And ifNull(Production.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Production.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(Production.Project_Code,'-1')) ".
						" And ifNull(Production.Costcenter_Code,'-1') between ifNull(".$fromcostcent.",ifNull(Production.Costcenter_Code,'-1')) and ifNull(". $tocostcent.",ifNull(Production.Costcenter_Code,'-1')) ".
						" And ifNull(Production.warehouse_code,'-1') between ifNull(".$fromwarehouse.",ifNull(Production.warehouse_code,'-1')) and ifNull(". $towarehouse.",ifNull(Production.warehouse_code,'-1')) ";
					
					
		if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
			$query= $query." 	And Production.warehouse_code In (select distinct warehouse_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  
		if ($groupexist > 0)
			$query= $query." 	And Items.group_code In (select distinct group_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  
	}
	
	$query= $query." 	Order by ProjCostCode, GroupCode  ";
	$query= $query." ) Q1 ";
	
	
}
			
$query= $query." ) Q2 ";
$query= $query." Group by ProjCostCode, GroupCode  ";
$query= $query." Order by ProjCostCode, GroupCode  ";
	
//echo $query; exit;	

$ArrayProjCostCent    = Array();
$ArrayGroupCode       = Array();
$ArrayRecordData      = Array();
$ArrayTotalByGroup    = Array();
$ArrayTotalByProjCost = Array();



$result=mysql_query($query,$connection) or die(mysql_error());
while($rows=mysql_fetch_row($result)){
	
	//Check If Project/Costcenter Exists In Array
	$PrjCstFound = 0;
	$count = count ($ArrayProjCostCent); 
	for ($j=0; $j<$count; $j++){
		if ($ArrayProjCostCent[$j] == $rows[0])
			$PrjCstFound = 1;
	}
				
	if ($PrjCstFound == 0) 
		$ArrayProjCostCent[$count] = $rows[0];
		
	//Check If Group Code Exists In Array		
	$GroupFound = 0;
	$count = count ($ArrayGroupCode); 
	for ($j=0; $j<$count; $j++){
		if ($ArrayGroupCode[$j] == $rows[2])
			$GroupFound = 1;
	}
				
	if ($GroupFound == 0) 
		$ArrayGroupCode[$count] = $rows[2];	
			
			
		
	$ArrayRecordData[$rows[0]][$rows[2]]['Qty']  = $rows[4];
	$ArrayRecordData[$rows[0]][$rows[2]]['Amt']  = $rows[5];
	$ArrayRecordData[$rows[0]][$rows[2]]['Cost'] = $rows[6];
	
	$ArrayRecordData[$rows[0]]['Total']['Qty']  = $ArrayRecordData[$rows[0]]['Total']['Qty']  + $rows[4];
	$ArrayRecordData[$rows[0]]['Total']['Amt']  = $ArrayRecordData[$rows[0]]['Total']['Amt']  + $rows[5];
	$ArrayRecordData[$rows[0]]['Total']['Cost'] = $ArrayRecordData[$rows[0]]['Total']['Cost'] + $rows[6];
		

}

//sorting data in array
array_multisort($ArrayProjCostCent);
array_multisort($ArrayGroupCode);


$RowNum    = 10;
$ColumnNum = 0;


$vCountColumns = 0;
if ($prtqty == 1) $vCountColumns += 1;
if ($prtamt == 1) $vCountColumns += 1;
if ($prtcost == 1 && $FI_ShowCostPrice == 1) $vCountColumns += 1;

$worksheet->write($RowNum,$ColumnNum,"Group Code", $boldleftFormat);
$ColumnNum += 1;

$count = count ($ArrayGroupCode); 
for ($j=0; $j<$count; $j++){ 
	$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + $vCountColumns - 1);
	$worksheet->write($RowNum,$ColumnNum,$ArrayGroupCode[$j], $boldcenterFormat);
	$ColumnNum += $vCountColumns;
}

$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + $vCountColumns - 1);
$worksheet->write($RowNum,$ColumnNum,"Totals", $boldcenterFormat);
$ColumnNum += $vCountColumns;
	

$RowNum   += 1;
$ColumnNum = 0;



if ($period == 4) {
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
	$worksheet->write($RowNum,$ColumnNum,"Project Code", $boldleftFormat);
	$ColumnNum += 1;
}	
else {
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
	$worksheet->write($RowNum,$ColumnNum,"CostCenter Code", $boldleftFormat);
	$ColumnNum += 1;
}	

	
$count = count ($ArrayGroupCode); 
for ($j=0; $j<$count; $j++){ 
    if ($prtqty == 1) {
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
		$worksheet->write($RowNum,$ColumnNum,"Quantity", $boldcenterFormat);
		$ColumnNum += 1;
	}	
	if ($prtamt == 1) {
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
		$worksheet->write($RowNum,$ColumnNum,"Amount", $boldcenterFormat);
		$ColumnNum += 1;
	}	
	if ($prtcost == 1 && $FI_ShowCostPrice == 1) {
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
		$worksheet->write($RowNum,$ColumnNum,"MeanCost", $boldcenterFormat);
		$ColumnNum += 1;
	}	
}

if ($prtqty == 1) {
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Quantity", $boldcenterFormat);
	$ColumnNum += 1;
}	
if ($prtamt == 1) {
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Amount", $boldcenterFormat);
	$ColumnNum += 1;
}
if ($prtcost == 1 && $FI_ShowCostPrice == 1) {
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Cost Amount", $boldcenterFormat);
	$ColumnNum += 1;
}
	
$RowNum   += 1;
$ColumnNum = 0;

// Displaying Data
$CountProjCost = count($ArrayProjCostCent); 
for ($i=0; $i<$CountProjCost; $i++){
	$CountGroup = count($ArrayGroupCode);
	
	$worksheet->writestring($RowNum,$ColumnNum,$ArrayProjCostCent[$i], $boldleftFormat);
	$ColumnNum += 1;
				
	for ($j=0; $j < $CountGroup; $j++){
		if ($prtqty == 1) {
			$worksheet->write($RowNum,$ColumnNum,number_format($ArrayRecordData[$ArrayProjCostCent[$i]][$ArrayGroupCode[$j]]['Qty'], $InventoryRMaskqty), $regularrightFormat);
			$ColumnNum += 1;
		}	
		if ($prtamt == 1) {
			$worksheet->write($RowNum,$ColumnNum,format_currency($ArrayRecordData[$ArrayProjCostCent[$i]][$ArrayGroupCode[$j]]['Amt'], $DecimalBase), $regularrightFormat);
			$ColumnNum += 1;
		}
		if ($prtcost == 1 && $FI_ShowCostPrice == 1) {
			$worksheet->write($RowNum,$ColumnNum,format_currency($ArrayRecordData[$ArrayProjCostCent[$i]][$ArrayGroupCode[$j]]['Cost'], $DecimalBase), $regularrightFormat);
			$ColumnNum += 1;
		}
		
		$ArrayTotalByGroup[$ArrayGroupCode[$j]]['Qty']  = $ArrayTotalByGroup[$ArrayGroupCode[$j]]['Qty']  + $ArrayRecordData[$ArrayProjCostCent[$i]][$ArrayGroupCode[$j]]['Qty'];
		$ArrayTotalByGroup[$ArrayGroupCode[$j]]['Amt']  = $ArrayTotalByGroup[$ArrayGroupCode[$j]]['Amt']  + $ArrayRecordData[$ArrayProjCostCent[$i]][$ArrayGroupCode[$j]]['Amt'];
		$ArrayTotalByGroup[$ArrayGroupCode[$j]]['Cost'] = $ArrayTotalByGroup[$ArrayGroupCode[$j]]['Cost'] + $ArrayRecordData[$ArrayProjCostCent[$i]][$ArrayGroupCode[$j]]['Cost'];
	}

	
	//Display Total Groups By Project/CostCenter
	if ($prtqty == 1) {
		$worksheet->write($RowNum,$ColumnNum,number_format($ArrayRecordData[$ArrayProjCostCent[$i]]['Total']['Qty'], $InventoryRMaskqty), $regularrightFormat);
		$ColumnNum += 1;
	}	
	if ($prtamt == 1) {
		$worksheet->write($RowNum,$ColumnNum,format_currency($ArrayRecordData[$ArrayProjCostCent[$i]]['Total']['Amt'], $DecimalBase), $regularrightFormat);
		$ColumnNum += 1;
	}
	if ($prtcost == 1 && $FI_ShowCostPrice == 1) {
		$worksheet->write($RowNum,$ColumnNum,format_currency($ArrayRecordData[$ArrayProjCostCent[$i]]['Total']['Cost'], $DecimalBase), $regularrightFormat);
		$ColumnNum += 1;
	}	
	
	$ArrayTotalByGroup['Total']['Qty']  = $ArrayTotalByGroup['Total']['Qty']  + $ArrayRecordData[$ArrayProjCostCent[$i]]['Total']['Qty'];
	$ArrayTotalByGroup['Total']['Amt']  = $ArrayTotalByGroup['Total']['Amt']  + $ArrayRecordData[$ArrayProjCostCent[$i]]['Total']['Amt'];
	$ArrayTotalByGroup['Total']['Cost'] = $ArrayTotalByGroup['Total']['Cost'] + $ArrayRecordData[$ArrayProjCostCent[$i]]['Total']['Cost'];
	
	$RowNum   += 1;
	$ColumnNum = 0;
	
}



// Display Totals By Report	
	
$RowNum   += 1;
$ColumnNum = 0;	

$worksheet->write($RowNum,$ColumnNum,"Totals", $boldcenterFormat);
$ColumnNum += 1;
	
$CountGroup = count($ArrayGroupCode);
for ($j=0; $j < $CountGroup; $j++){
	if ($prtqty == 1) {
		$worksheet->write($RowNum,$ColumnNum,number_format($ArrayTotalByGroup[$ArrayGroupCode[$j]]['Qty'], $InventoryRMaskqty), $boldrightFormat);
		$ColumnNum += 1;
	}	
	if ($prtamt == 1) {
		$worksheet->write($RowNum,$ColumnNum,format_currency($ArrayTotalByGroup[$ArrayGroupCode[$j]]['Amt'], $DecimalBase), $boldrightFormat);
		$ColumnNum += 1;
	}
	if ($prtcost == 1 && $FI_ShowCostPrice == 1) {
		$worksheet->write($RowNum,$ColumnNum,format_currency($ArrayTotalByGroup[$ArrayGroupCode[$j]]['Cost'], $DecimalBase), $boldrightFormat);
		$ColumnNum += 1;
	}
		
}

	
//Display Total Groups By Project/CostCenter
if ($prtqty == 1) {
	$worksheet->write($RowNum,$ColumnNum,number_format($ArrayTotalByGroup['Total']['Qty'], $InventoryRMaskqty), $boldrightFormat);
	$ColumnNum += 1;
}	
if ($prtamt == 1) {
	$worksheet->write($RowNum,$ColumnNum,format_currency($ArrayTotalByGroup['Total']['Amt'], $DecimalBase), $boldrightFormat);
	$ColumnNum += 1;
}
if ($prtcost == 1 && $FI_ShowCostPrice == 1) {
	$worksheet->write($RowNum,$ColumnNum,format_currency($ArrayTotalByGroup['Total']['Cost'], $DecimalBase), $boldrightFormat);
	$ColumnNum += 1;
}	
	

function format_currency($amount, $DecimalBase) { 

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

function current_server_time($connection){

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

  return $now;
}

function explodeword($date)
{

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

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

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

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

$workbook->close();

?>