<?php
header("Content-type: text/html; charset=windows-1256");

 function logHtml($content){
     ////////////Debuging purpose //////////////////////////////////////
	 
 
  echo '<script type="text/javascript">console.log("' . $content . '")</script>';
 
 
                           
}	

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


include("config.inc.php");
require_once('Spreadsheet/Excel/Writer.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, ifNull(StmtOfItemRMaskQty,0) As StmtOfItemRMaskQty, Base1, Base2, Company_Name As CompanyName from setup";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultsetup)){
  extract($rows);
} 

$querylog=" select ifNull(FI_RepRestrictProject,0) As FI_RepRestrictProject, ifNull(FI_RepRestrictCostcenter,0) As FI_RepRestrictCostcenter, ".
		  " ifNull(FI_RepRestrictWarehouse,0) As FI_RepRestrictWarehouse, 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_Grn,0) As Module_Grn, ifNull(Module_Delivery,0) As Module_Delivery , ifNull(Module_Project,0) As Module_Project, ifNull(Module_Costcenter,0) As Module_Costcenter,
			   ifNull(Module_expiredate,0) As Module_expiredate,ifnull(Module_BatchNo,0) as Module_BatchNo 
			   From Module ";
$resultmodule=mysql_query($querymodule,$connection) or die(mysql_error());
while($rowmodule=mysql_fetch_array($resultmodule)){
	extract($rowmodule);
}

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


$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();
$workbook->setVersion(8);

//Sending HTTP headers
//ddddddddddddddddd/////////////////////$workbook->send('Exp_StatementofItems.xls');
 $workbook->send('Exp_StatementofItems.xls');
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('Statement of Items');
$worksheet->setInputEncoding('windows-1256'); 
$worksheet->setLandscape();
 
//set all columns same width
$columnWidth = 10;

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

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

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


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


$SelectedOptions = "Filter by: \n";
if ($fromdate != '')      $SelectedOptions .= "-> Date From: ". $fromdate . "  To: ". $todate ."\n";	
if($Module_expiredate==1 && $edatechk == 1)
if ($fromedate != '')     $SelectedOptions .= "-> Expire Date From: ". $fromedate . "  To: ". $toedate ."<br>";	
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 ($fromcategory != '')  $SelectedOptions .= "-> SubGroup 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 . "\n";
if ($towarehouse != '') $SelectedOptions .= "-> Warehouse To: ". $towarehouse . "\n";
if ($fromtranswh != '')   $SelectedOptions .= "-> Transfer Warehouse From: ". $fromtranswh . "  To: ". $totranswh ."\n";
if ($Module_BatchNo == 1 && $frombatchno != '')
	$SelectedOptions .= "-> Batch No From: ". $frombatchno . "  To: ". $tobatchno ."\n";


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

$fromcostcent = "".$fromcostcent;
$tocostcent = "".$tocostcent;

$fromproject = "".$fromproject;
$toproject = "".$toproject;

if ($fromcostcent == '')   $fromcostcent = "NULL";
else  $fromcostcent = "'".$fromcostcent."'";    

if ($tocostcent == '')   $tocostcent = "NULL";
else  $tocostcent = "'".$tocostcent."'";    

if ($fromproject == '')   $fromproject = "NULL";
else  $fromproject = "'".$fromproject."'";       
if ($toproject == '')   $toproject = "NULL";
else  $toproject = "'".$toproject."'";       
   
if (empty($fromgroup))   $fromgroup = "NULL";
else  $fromgroup = "'".$fromgroup."'";    
if (empty($togroup))   $togroup = "NULL";
else  $togroup = "'".$togroup."'";   
   
if (empty($fromsubgroup))   $fromsubgroup = "NULL";
else   $fromsubgroup = "'".$fromsubgroup."'";    
if (empty($tosubgroup))   $tosubgroup = "NULL";
else   $tosubgroup = "'".$tosubgroup."'";      


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

if (empty($towarehouse))   $towarehouse = "NULL";
else   $towarehouse = "'".$towarehouse."'";     
   
if (empty($fromcategory))    $fromcategory = "NULL";
else   $fromcategory = "'".$fromcategory."'";     
if (empty($tocategory))    $tocategory = "NULL";
else   $tocategory = "'".$tocategory."'";        

if (empty($fromtranswh))    $fromtranswh = "NULL";
else   $fromtranswh = "'".$fromtranswh."'";     
if (empty($totranswh))    $totranswh = "NULL";
else   $totranswh = "'".$totranswh."'";        

if ($frombatchno == '' || $Module_BatchNo != 1)   $frombatchno = 'null';
else  $frombatchno = "'".$frombatchno."'";       
if ($tobatchno == '' || $Module_BatchNo != 1)   $tobatchno = 'null';
else  $tobatchno = "'".$tobatchno."'";  

   
if (!empty($fromedate))   $fromedate = explodeword($fromedate);
if (!empty($toedate))     $toedate = explodeword($toedate); 

if (empty($fromedate))    $fromedate = "NULL";
else   $fromedate = "'".$fromedate."'"; 
if (empty($toedate))    $toedate = "NULL";
else   $toedate = "'".$toedate."'";	

if (empty($todate))    $lastdate = insertMySQLDate(displayToday());
else   $lastdate = insertMySQLDate($todate);

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


if (empty($fromdate))    $fromdate = "NULL";
else   $fromdate = "'".$fromdate."'"; 
if (empty($todate))    $todate = "NULL";
else   $todate = "'".$todate."'";
   
	
	
if (empty($prtprice))     $prtprice = 0;
if (empty($prtcost))      $prtcost = 0;
if (empty($updmeancost))  $updmeancost = 0;
if (empty($searchserial)) $searchserial = 0;
if (empty($showserials))  $showserials = 0;
if ($Module_BatchNo != 1)
	$showbatch = 0;

//Display Report Header
 
$worksheet->write(1,0,$CompanyName, $TitleFormat);
$worksheet->write(1,3,"Statement of Items", $TitleFormat);
               
$worksheet->write(1,8,"Printed Date:", $boldleftFormat);
$worksheet->write(1,9,$printdate[2]."/".$printdate[1]."/".$printdate[0], $regularleftFormat);
$worksheet->write(2,8,"Time:", $boldleftFormat);		
$worksheet->write(2,9,$dt[1]." ".$dt[2], $regularleftFormat);

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

$RowNum    = 10;
$ColumnNum = 4;

$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
$worksheet->write($RowNum,$ColumnNum,"Transfer", $boldcenterFormat);
$ColumnNum += 2;				
$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
$worksheet->write($RowNum,$ColumnNum,"Movement", $boldcenterFormat);
$ColumnNum += 2;				

			
$RowNum   += 1;
$ColumnNum = 0;

$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Date", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"Ref.", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 35);
$worksheet->write($RowNum,$ColumnNum,"Description", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Warehouse", $boldcenterFormat);
$ColumnNum += 1;
if($Module_BatchNo == 1 && $showbatch == 1){
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Batch No", $boldcenterFormat);
	$ColumnNum += 1;
}	
		
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"From", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"To", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"In", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"Out", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"Qty.", $boldcenterFormat);
$ColumnNum += 1;

		 
if ($prtcost == 1) {	 
	$worksheet->write($RowNum,$ColumnNum,"Cur", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Price", $boldcenterFormat);
	$ColumnNum += 1;
} 
 
if($vShowProject == 1 && $Module_Project == 1){
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Project", $boldcenterFormat);
	$ColumnNum += 1;		
}

	if($vShowCostcenter == 1 && $Module_Costcenter == 1){
 	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Costcenter", $boldcenterFormat);
	$ColumnNum += 1;		
 }
$QueryRestrictPrj = " 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 != '') ".
					"			 ) Q4  ";
					
$QueryRestrictCst = " 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 != '') ".
					"			 ) Q5  ";
					
$query = '';
$SelectedTransaction = 0;

$query = $query." Select upper(ItemCode) as ItemCode, ItemDescription, TransReference, TransDate, TransDescription, WarehouseCode, QtyTransferFrom, QtyTransferTo, ".
				" QtyOut, QtyIn, ifNull(QtyPrev,0), CurrencyCode, TransPrice, TransOrder, OrderDate, TransType, TransDtId, ProjectCode, CostCenterCode, RemainVal, TransVal, batchno ".
				" From ( ";

if (($TransPI == 1 || $TransPR == 1) &&  $Module_Grn == 0) {
	
	$query = $query." Select ItemCode, ItemDescription, TransReference, TransDate, TransDescription, WarehouseCode, QtyTransferFrom, QtyTransferTo, ".
					" QtyOut, QtyIn, QtyPrev, CurrencyCode, TransPrice, TransOrder, OrderDate, TransType, TransDtId, ProjectCode, CostCenterCode, meanPrice*QtyExists as RemainVal, ".
					" abs((ifnull(QtyTransferFrom,0)-ifnull(QtyTransferTo,0))+(ifnull(QtyIn,0)-ifnull(QtyOut,0)))*meanPrice as TransVal, batchno ".
					" From ( ";
					
	if ($searchserial == 1) $query = $query." Select PurchasesSerialNo.SerialNo_Code As ItemCode, ";
	else $query = $query." Select PurchasesDt.Item_Code As ItemCode, ";
	
	$query = $query." Items.Item_Description As ItemDescription, Purchases.Purchase_Reference As TransReference, Date_Format(Purchases.Purchase_Date,'%d/%m/%Y') As TransDate, ".
					" Purchases.Purchase_Date As OrderDate, Concat(if(Purchases.Journal_Type='PI','PURCHASE :','RETURN PURCHASE :'), Suppliers.Ledger_Name, ' ', if(Purchases.Purchase_Remark is null,'',Purchases.Purchase_Remark)) As TransDescription, ".
					" Purchases.Journal_Type As TransType, PurchasesDt.PurchaseDt_ID As TransDtId, PurchasesDt.Warehouse_Code As WarehouseCode,PurchasesDt.Project_Code As ProjectCode,PurchasesDt.CostCent_Code As CostCenterCode, ".
					" Null As QtyTransferFrom, Null As QtyTransferTo, ".
					" if(Purchases.Journal_Type='PI', ifNull(PurchasesDt.PurchaseDt_Quantity,0), (-1)*ifNull(PurchasesDt.PurchaseDt_Quantity,0)) As TransQty, ".
					" if(Purchases.Journal_Type='PR', ifNull(Abs(PurchasesDt.PurchaseDt_Quantity),0), Null) As QtyOut, ".
					" if(Purchases.Journal_Type='PI', ifNull(PurchasesDt.PurchaseDt_Quantity,0), Null) As QtyIn, ";
					
	if ($searchserial == 1) $query = $query." 0 As QtyPrev, ";
	else $query = $query." QQtyPrev.QuantityExists As QtyPrev, ";
	
	$query = $query." if(Purchases.Journal_Type='PI', 1, 2) As TransOrder, ".
					" if(upper($curr)='".$Base1."', '".$Base1."',if(upper($curr)='".$Base2."', '".$Base2."', Purchases.Currency_Code)) As CurrencyCode, ";
					
					if ($updmeancost == 1)
						$query= $query." ifNull(MeanCostCur(PurchasesDt.Item_Code, '".$lastdate."', 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) TransPrice ";
					else
						$query= $query." if($PurchaseTTC=1, if(upper($curr)='".$Base1."',PurchasesDt.PurchaseDt_PriceTTCBase1,if(upper($curr)='".$Base2."',PurchasesDt.PurchaseDt_PriceTTCBase2, PurchasesDt.PurchaseDt_PriceTTC)),  ".
									   " if(upper($curr)='".$Base1."',PurchasesDt.PurchaseDt_PriceBase1,if(upper($curr)='".$Base2."',PurchasesDt.PurchaseDt_PriceBase2, PurchasesDt.PurchaseDt_Price))) As TransPrice  ";

	$query= $query. " , ifNull(MeanCostCur(PurchasesDt.Item_Code, '".$lastdate."', 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) as meanPrice
					  , ifnull(QtyExistsWSorder(PurchasesDt.Item_Code, PurchasesDt.Warehouse_Code, '".$lastdate."', 0, 'PI', '', ''),0) QtyExists, PurchaseDt_BatchNo as batchno ";		   
	$query= $query. " From PurchasesDt  ".
					" Inner Join Purchases on PurchasesDt.Purchase_ID = Purchases.Purchase_ID ".
					" Inner Join Items on PurchasesDt.Item_Code = Items.Item_Code ".
					" Inner Join Suppliers on Purchases.Supplier_Code = Suppliers.Ledger_Number ";

	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,'%') ";		 
		
	if ($searchserial == 1)
		$query= $query." Inner Join PurchasesSerialNo on PurchasesSerialNo.PurchaseDt_ID = PurchasesDt.PurchaseDt_ID And PurchasesSerialNo.Journal_Type = Purchases.Journal_Type ";
	
	if ($searchserial == 0) {
		$query= $query. " Left Join (  ".
						"               Select Stk.Item_Code, Sum((ifNull(quantityproducein,0) + ifNull(quantityentered,0) - ifNull(quantityproduceout,0) - ifNull(quantitydelivered,0))) As QuantityExists  ".
						"               From StkTransaction Stk ".
						"               Where Date_Format(Stk.Transaction_Date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(Stk.Transaction_Date,'%Y-%m-%d')) ".
						"  				And Stk.item_code between ifNull(".$fromitem.",Stk.item_code) and ifNull(". $toitem.",Stk.item_code) ".
						"               And ifNull(Stk.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Stk.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(Stk.Project_Code,'-1')) ".
						"               And ifNull(Stk.Costcent_Code,'-1') between ifNull(".$fromcostcent.",ifNull(Stk.Costcent_Code,'-1')) and ifNull(". $tocostcent.",ifNull(Stk.Costcent_Code,'-1')) ".
						"				And ifNull(Stk.warehouse_code,'-1') between ifNull(". $fromwarehouse .",ifNull(Stk.warehouse_code,'-1')) ".
  						"               And ifNull(". $towarehouse .",ifNull(Stk.warehouse_code,'-1'))  ".
						"               And (($Module_Grn=1 and Stk.Transaction_Type <> 'PI') or ($Module_Grn=0 and 1=1)) ".
						"               Group By Stk.Item_Code ".
						"             ) As QQtyPrev on PurchasesDt.Item_Code = QQtyPrev.Item_Code   ";
	}
	
	$query= $query.	" Where (Purchases.Purchase_Reference Is Not Null And Purchases.Purchase_Reference != '')  ".
					" 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'))  ";
	if($Module_expiredate==1 && $edatechk == 1)	
	$query= $query." And Date_format(PurchaseDt_ExpireDate,'%Y-%m-%d') between ifNull(". $fromedate.",date_format(PurchaseDt_ExpireDate,'%Y-%m-%d')) and ifNull(". $toedate.",date_format(PurchaseDt_ExpireDate,'%Y-%m-%d'))  ";
							
	if ($searchserial == 0)	
		$query= $query." And PurchasesDt.Item_Code between ifNull(".$fromitem.",PurchasesDt.Item_Code) and ifNull(". $toitem.",PurchasesDt.Item_Code) ";
	else
		$query= $query." And PurchasesSerialNo.SerialNo_Code between ifNull(".$fromitem.",PurchasesSerialNo.SerialNo_Code) and ifNull(". $toitem.",PurchasesSerialNo.SerialNo_Code) ";	
					
	$query .= " and ifnull(PurchaseDt_BatchNo,'') between ifnull(".$frombatchno.",ifnull(PurchaseDt_BatchNo,''))
				and ifnull(".$tobatchno.",ifnull(PurchaseDt_BatchNo,'')) ";
				
	$query= $query. " 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.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'))  ";										
					//" And ifNull(PurchasesDt.warehouse_code,'-1') Like Concat(ifNull(". $fromwarehouse .",ifNull(PurchasesDt.warehouse_code,'-1')),'%')  ";
				
				//if ($Module_Grn == 0) {
					if ($TransPI==1 && $TransPR==1) $query= $query." 	And Purchases.Journal_Type in ('PI','PR') ";
					else if ($TransPI==1 && $TransPR==0) $query= $query."	And Purchases.Journal_Type in ('PI') ";
					else if ($TransPI==0 && $TransPR==1) $query= $query."	And Purchases.Journal_Type in ('PR') ";
				//} else {
					//if ($TransPR==1) $query= $query."	And Purchases.Journal_Type in ('PR') ";
				//}	
	  
				if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
					$query= $query." 	And PurchasesDt.warehouse_code In (select distinct warehouse_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
				if ($groupexist > 0)
					$query= $query." 	And Items.group_code In (select distinct group_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  

	$query= $query." 	Order by ItemCode, OrderDate, TransOrder, TransReference  ";				
	$query= $query." ) Q1 ";
	
	$SelectedTransaction = 1;
	
}

if (($TransPI == 1 || $TransPR == 1) &&  $Module_Grn == 1) {

	if ($SelectedTransaction == 1 ) 
		$query = $query." Union ";
	
	$query = $query." Select ItemCode, ItemDescription, TransReference, TransDate, TransDescription, WarehouseCode, QtyTransferFrom, QtyTransferTo, ".
					" QtyOut, QtyIn, QtyPrev, CurrencyCode, TransPrice, TransOrder, OrderDate, TransType, TransDtId, ProjectCode, CostCenterCode , meanPrice*QtyExists as RemainVal, ".
					" abs((ifnull(QtyTransferFrom,0)-ifnull(QtyTransferTo,0))+(ifnull(QtyIn,0)-ifnull(QtyOut,0)))*meanPrice as TransVal, batchno ".
					" From ( ";
					
	if ($searchserial == 1) $query = $query." Select PurchasesSerialNo.SerialNo_Code As ItemCode, ";
	else $query = $query." Select GrnPurchasesDt.Item_Code As ItemCode, ";
	
	$query = $query." Items.Item_Description As ItemDescription, GrnPurchases.Purchase_Reference As TransReference, Date_Format(GrnPurchases.Purchase_Date,'%d/%m/%Y') As TransDate, ".
					" GrnPurchases.Purchase_Date As OrderDate, Concat(if(GrnPurchases.Journal_Type='PI','GRN :','RETURN GRN :'), Suppliers.Ledger_Name, ' ', if(GrnPurchases.Purchase_Remark is null,'',GrnPurchases.Purchase_Remark)) As TransDescription, ".
					" GrnPurchases.Journal_Type As TransType, GrnPurchasesDt.PurchaseDt_ID As TransDtId, GrnPurchasesDt.Warehouse_Code As WarehouseCode,GrnPurchasesDt.Project_Code As ProjectCode,GrnPurchasesDt.CostCent_Code As CostCenterCode,  ".
					" Null As QtyTransferFrom, Null As QtyTransferTo, ".
					" if(GrnPurchases.Journal_Type='PI', ifNull(GrnPurchasesDt.PurchaseDt_Quantity,0), (-1)*ifNull(GrnPurchasesDt.PurchaseDt_Quantity,0)) As TransQty, ".
					" if(GrnPurchases.Journal_Type='PR', ifNull(Abs(GrnPurchasesDt.PurchaseDt_Quantity),0), Null) As QtyOut, ".
					" if(GrnPurchases.Journal_Type='PI', ifNull(GrnPurchasesDt.PurchaseDt_Quantity,0), Null) As QtyIn, ";
					
	if ($searchserial == 1) $query = $query." 0 As QtyPrev, ";
	else $query = $query." QQtyPrev.QuantityExists As QtyPrev, ";
	
	$query = $query." if(GrnPurchases.Journal_Type='PI', 1, 2) As TransOrder, ".
					" if(upper($curr)='".$Base1."', '".$Base1."',if(upper($curr)='".$Base2."', '".$Base2."', GrnPurchases.Currency_Code)) As CurrencyCode, ";
					
					if ($updmeancost == 1)
						$query= $query." ifNull(MeanCostCur(GrnPurchasesDt.Item_Code, '".$lastdate."', if(upper($curr)='".$Base1."', 1,if(upper($curr)='".$Base2."',2, 1)), GrnPurchases.Currency_Code, if(upper($curr)='".$Base1."', '',if(upper($curr)='".$Base2."','',items.currency_code ))),0) TransPrice ";
					else
						$query= $query." if($PurchaseTTC=1, if(upper($curr)='".$Base1."',GrnPurchasesDt.PurchaseDt_PriceTTCBase1,if(upper($curr)='".$Base2."',GrnPurchasesDt.PurchaseDt_PriceTTCBase2, GrnPurchasesDt.PurchaseDt_PriceTTC)),  ".
									   " if(upper($curr)='".$Base1."',GrnPurchasesDt.PurchaseDt_PriceBase1,if(upper($curr)='".$Base2."',GrnPurchasesDt.PurchaseDt_PriceBase2, GrnPurchasesDt.PurchaseDt_Price))) As TransPrice  ";

	$query= $query. " , ifNull(MeanCostCur(GrnPurchasesDt.Item_Code, '".$lastdate."', if(upper($curr)='".$Base1."', 1,if(upper($curr)='".$Base2."',2, 1)), GrnPurchases.Currency_Code, if(upper($curr)='".$Base1."', '',if(upper($curr)='".$Base2."','',items.currency_code ))),0) as meanPrice
					  , ifnull(QtyExistsWSorder(GrnPurchasesDt.Item_Code, GrnPurchasesDt.Warehouse_Code, '".$lastdate."', 0, 'PI', '', ''),0) QtyExists,PurchaseDt_BatchNo as batchno  ";			   
	$query= $query. " From GrnPurchasesDt  ".
					" Inner Join GrnPurchases on GrnPurchasesDt.Purchase_ID = GrnPurchases.Purchase_ID ".
					" Inner Join Items on GrnPurchasesDt.Item_Code = Items.Item_Code ".
					" Inner Join Suppliers on GrnPurchases.Supplier_Code = Suppliers.Ledger_Number ";

	if ($projectexist >	0 && $FI_RepRestrictProject == 1)
		$query= $query.$QueryRestrictPrj." on GrnPurchases.project_code like concat(Q4.project_code,'%') ";
								 
	if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
		$query= $query.$QueryRestrictCst." on GrnPurchases.Costcent_Code like concat(Q5.costcent_code,'%') ";		 
		
	if ($searchserial == 1)
		$query= $query." Inner Join PurchasesSerialNo on PurchasesSerialNo.PurchaseDt_ID = GrnPurchasesDt.PurchaseDt_ID And PurchasesSerialNo.Journal_Type = GrnPurchases.Journal_Type ";

	if ($searchserial == 0) {	
		$query= $query. " Left Join (  ".
						"               Select Stk.Item_Code, Sum((ifNull(quantityproducein,0) + ifNull(quantityentered,0) - ifNull(quantityproduceout,0) - ifNull(quantitydelivered,0))) As QuantityExists  ".
						"               From StkTransaction Stk ".
						"               Where Date_Format(Stk.Transaction_Date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(Stk.Transaction_Date,'%Y-%m-%d')) ".
						"  				And Stk.item_code between ifNull(".$fromitem.",Stk.item_code) and ifNull(". $toitem.",Stk.item_code) ".
						"               And ifNull(Stk.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Stk.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(Stk.Project_Code,'-1')) ".
						"               And ifNull(Stk.Costcent_Code,'-1') between ifNull(".$fromcostcent.",ifNull(Stk.Costcent_Code,'-1')) and ifNull(". $tocostcent.",ifNull(Stk.Costcent_Code,'-1')) ".
						"				And ifNull(Stk.warehouse_code,'-1') between ifNull(". $fromwarehouse .",ifNull(Stk.warehouse_code,'-1'))  ".
						"				And ifNull(". $towarehouse .",ifNull(Stk.warehouse_code,'-1'))  ".
						"               And (($Module_Grn=1 and Stk.Transaction_Type <> 'PI') or ($Module_Grn=0 and 1=1)) ".
						"               Group By Stk.Item_Code ".
						"             ) As QQtyPrev on GrnPurchasesDt.Item_Code = QQtyPrev.Item_Code   ";
	}
	
	$query= $query.	" Where (GrnPurchases.Purchase_Reference Is Not Null And GrnPurchases.Purchase_Reference != '')  ".
					" And Date_format(GrnPurchases.Purchase_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(GrnPurchases.Purchase_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(GrnPurchases.Purchase_Date,'%Y-%m-%d'))  ";
	if($Module_expiredate==1 && $edatechk == 1)	
	$query= $query." And Date_format(PurchaseDt_ExpireDate,'%Y-%m-%d') between ifNull(". $fromedate.",date_format(PurchaseDt_ExpireDate,'%Y-%m-%d')) and ifNull(". $toedate.",date_format(PurchaseDt_ExpireDate,'%Y-%m-%d'))  ";
					
	if ($searchserial == 0)	
		$query= $query." And GrnPurchasesDt.Item_Code between ifNull(".$fromitem.",GrnPurchasesDt.Item_Code) and ifNull(". $toitem.",GrnPurchasesDt.Item_Code) ";
	else
		$query= $query." And PurchasesSerialNo.SerialNo_Code between ifNull(".$fromitem.",PurchasesSerialNo.SerialNo_Code) and ifNull(". $toitem.",PurchasesSerialNo.SerialNo_Code) ";	
	
	$query .= " and ifnull(PurchaseDt_BatchNo,'') between ifnull(".$frombatchno.",ifnull(PurchaseDt_BatchNo,''))
				and ifnull(".$tobatchno.",ifnull(PurchaseDt_BatchNo,'')) ";
					
	$query= $query.	" 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(GrnPurchases.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(GrnPurchases.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(GrnPurchases.Project_Code,'-1')) ".
					" And ifNull(GrnPurchases.Costcent_Code,'-1') between ifNull(".$fromcostcent.",ifNull(GrnPurchases.Costcent_Code,'-1')) and ifNull(". $tocostcent.",ifNull(GrnPurchases.Costcent_Code,'-1')) ".
					" And ifNull(GrnPurchasesDt.warehouse_code,'-1') between ifNull(". $fromwarehouse .",ifNull(GrnPurchasesDt.warehouse_code,'-1')) and ifNull(". $towarehouse .",ifNull(GrnPurchasesDt.warehouse_code,'-1'))  ";
					//" And ifNull(GrnPurchasesDt.warehouse_code,'-1') Like Concat(ifNull(". $fromwarehouse .",ifNull(GrnPurchasesDt.warehouse_code,'-1')),'%')  ";
				
				
				if ($TransPI==1 && $TransPR==1) $query= $query." 	And GrnPurchases.Journal_Type in ('PI','PR') ";
				else if ($TransPI==1 && $TransPR==0) $query= $query."	And GrnPurchases.Journal_Type in ('PI') ";
				else if ($TransPI==0 && $TransPR==1) $query= $query."	And GrnPurchases.Journal_Type in ('PR') ";
	  
				if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
					$query= $query." 	And GrnPurchasesDt.warehouse_code In (select distinct warehouse_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
				if ($groupexist > 0)
					$query= $query." 	And Items.group_code In (select distinct group_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  

	$query= $query." 	Order by ItemCode, OrderDate, TransOrder, TransReference  ";				
	$query= $query." ) Q1 ";
	
	$SelectedTransaction = 1;
	
}

if ($TransSI == 1 || $TransSR == 1 ) {

	if ($SelectedTransaction == 1 ) 
		$query = $query." Union ";

	$query = $query." Select ItemCode, ItemDescription, TransReference, TransDate, TransDescription, WarehouseCode, QtyTransferFrom, QtyTransferTo, ".
					" QtyOut, QtyIn, QtyPrev, CurrencyCode, TransPrice, TransOrder, OrderDate, TransType, TransDtId , ProjectCode, CostCenterCode  ,  meanPrice*QtyExists as RemainVal,  ".
					" abs((ifnull(QtyTransferFrom,0)-ifnull(QtyTransferTo,0))+(ifnull(QtyIn,0)-ifnull(QtyOut,0)))*meanPrice as TransVal, batchno ".
					" From ( ";
					
	if ($searchserial == 1) $query = $query." Select PurchasesSerialNo.SerialNo_Code As ItemCode, ";
	else $query = $query." Select DeliveryDt.Item_Code As ItemCode, ";
	
	$query = $query." Items.Item_Description As ItemDescription, Deliveries.Delivery_Reference As TransReference, ".
					" Date_Format(Deliveries.Delivery_Date,'%d/%m/%Y') As TransDate, Deliveries.Delivery_Date As OrderDate,  ".
					" Concat(if(Deliveries.Journal_Type='DI',if($Module_Delivery=1,'DELIVERY :','SALES :'),if($Module_Delivery=1,'RETURN DELIVERY :','RETURN SALES :')), Clients.Ledger_Name, ' ', if(Deliveries.Delivery_Remark is null,'',Deliveries.Delivery_Remark)) As TransDescription, ".
					" Deliveries.Journal_Type As TransType, DeliveryDt.DeliveryDt_ID As TransDtId, DeliveryDt.Warehouse_Code As WarehouseCode,DeliveryDt.Project_Code As ProjectCode,DeliveryDt.CostCent_Code As CostCenterCode,   ".
					" Null As QtyTransferFrom, Null As QtyTransferTo, ".
					" if(Deliveries.Journal_Type='DI', ifNull(DeliveryDt.DeliveryDt_Quantity,0), (-1)*ifNull(DeliveryDt.DeliveryDt_Quantity,0)) As TransQty, ".
					" if(Deliveries.Journal_Type='DI', ifNull(DeliveryDt.DeliveryDt_Quantity,0), Null) As QtyOut, ".
					" if(Deliveries.Journal_Type='DR', ifNull(Abs(DeliveryDt.DeliveryDt_Quantity),0), Null) As QtyIn, ";
					
	if ($searchserial == 1) $query = $query." 0 As QtyPrev, ";
	else $query = $query." QQtyPrev.QuantityExists As QtyPrev, ";
	
	$query = $query." if(Deliveries.Journal_Type='DI', 5, 6) As TransOrder, ".
					" if(upper($curr)='".$Base1."', '".$Base1."',if(upper($curr)='".$Base2."', '".$Base2."', Deliveries.Currency_Code)) As CurrencyCode, ";
					
					if ($updmeancost == 1)
						$query= $query." ifNull(MeanCostCur(DeliveryDt.Item_Code, '".$lastdate."', 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) TransPrice ";
					else
						$query= $query." if($Module_Delivery=0,if($SalesTTC=1, if(upper($curr)='".$Base1."',DeliveryDt.DeliveryDt_PriceTTCBase1,if(upper($curr)='".$Base2."',DeliveryDt.DeliveryDt_PriceTTCBase2, DeliveryDt.DeliveryDt_PriceTTC)),  ".
									   " if(upper($curr)='".$Base1."',DeliveryDt.DeliveryDt_PriceBase1,if(upper($curr)='".$Base2."',DeliveryDt.DeliveryDt_PriceBase2, DeliveryDt.DeliveryDt_Price))), ".
									   " if(upper($curr)='".$Base1."',DeliveryDt.SaleDt_PriceBase1,if(upper($curr)='".$Base2."',DeliveryDt.SaleDt_PriceBase2, DeliveryDt.SaleDt_Price))) As TransPrice  ";
					
	$query= $query. " , ifNull(MeanCostCur(DeliveryDt.Item_Code, '".$lastdate."', if(upper($curr)='".$Base1."', 1,if(upper($curr)='".$Base2."',2, 1)), Deliveries.Currency_Code, if(upper($curr)='".$Base1."', '',if(upper($curr)='".$Base2."','',items.currency_code ))),0) as meanPrice
					  , ifnull(QtyExistsWSorder(DeliveryDt.Item_Code, DeliveryDt.Warehouse_Code, '".$lastdate."', 0, 'DI', '', ''),0) QtyExists, DeliveryDt_BatchNo as batchno ";			   
	$query= $query. " From DeliveryDt  ".
					" Inner Join Deliveries on DeliveryDt.Delivery_ID = Deliveries.Delivery_ID ".
					" Inner Join Items on DeliveryDt.Item_Code = Items.Item_Code ".
					" Inner Join Clients on Deliveries.Client_Code = Clients.Ledger_Number ";

	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,'%') ";		 
		
	if ($searchserial == 1)
		$query= $query." Inner Join PurchasesSerialNo on PurchasesSerialNo.PurchaseDt_ID = DeliveryDt.DeliveryDt_ID And PurchasesSerialNo.Journal_Type = Deliveries.Journal_Type ";
					   
	if ($searchserial == 0) {				   
		$query= $query. " Left Join (  ".
						"               Select Stk.Item_Code, Sum((ifNull(quantityproducein,0) + ifNull(quantityentered,0) - ifNull(quantityproduceout,0) - ifNull(quantitydelivered,0))) As QuantityExists  ".
						"               From StkTransaction Stk ".
						"               Where Date_Format(Stk.Transaction_Date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(Stk.Transaction_Date,'%Y-%m-%d')) ".
						"  				And Stk.item_code between ifNull(".$fromitem.",Stk.item_code) and ifNull(". $toitem.",Stk.item_code) ".
						"               And ifNull(Stk.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Stk.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(Stk.Project_Code,'-1')) ".
						"               And ifNull(Stk.Costcent_Code,'-1') between ifNull(".$fromcostcent.",ifNull(Stk.Costcent_Code,'-1')) and ifNull(". $tocostcent.",ifNull(Stk.Costcent_Code,'-1')) ".
						//"				And ifNull(Stk.warehouse_code,'-1') Like Concat( ifNull(". $fromwarehouse .",ifNull(Stk.warehouse_code,'-1')),'%')  ".
						"				And ifNull(Stk.warehouse_code,'-1') between ifNull(". $fromwarehouse .",ifNull(Stk.warehouse_code,'-1')) and ifNull(". $towarehouse .",ifNull(Stk.warehouse_code,'-1'))  ".
						"               And (($Module_Grn=1 and Stk.Transaction_Type <> 'PI') or ($Module_Grn=0 and 1=1)) ".
						"               Group By Stk.Item_Code ".
						"             ) As QQtyPrev on DeliveryDt.Item_Code = QQtyPrev.Item_Code   ";
	}				
	$query= $query.	" Where (Deliveries.Delivery_Reference Is Not Null And Deliveries.Delivery_Reference != '')  ".
					" And Date_format(Deliveries.Delivery_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(Deliveries.Delivery_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Deliveries.Delivery_Date,'%Y-%m-%d'))  ";
	if($Module_expiredate==1 && $edatechk == 1)	
	$query= $query." And Date_format(DeliveryDt_ExpireDate,'%Y-%m-%d') between ifNull(". $fromedate.",date_format(DeliveryDt_ExpireDate,'%Y-%m-%d')) and ifNull(". $toedate.",date_format(DeliveryDt_ExpireDate,'%Y-%m-%d'))  ";
					
	if ($searchserial == 0)	
		$query= $query." And DeliveryDt.Item_Code between ifNull(".$fromitem.",DeliveryDt.Item_Code) and ifNull(". $toitem.",DeliveryDt.Item_Code) ";
	else
		$query= $query." And PurchasesSerialNo.SerialNo_Code between ifNull(".$fromitem.",PurchasesSerialNo.SerialNo_Code) and ifNull(". $toitem.",PurchasesSerialNo.SerialNo_Code) ";
	
	$query .= " and ifnull(DeliveryDt_BatchNo,'') between ifnull(".$frombatchno.",ifnull(DeliveryDt_BatchNo,''))
				and ifnull(".$tobatchno.",ifnull(DeliveryDt_BatchNo,'')) ";	
				
	$query= $query.	" 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(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') = ifNull(". $fromwarehouse .",ifNull(DeliveryDt.warehouse_code,'-1'))  ";
					//" And ifNull(DeliveryDt.warehouse_code,'-1') Like Concat(ifNull(". $fromwarehouse .",ifNull(DeliveryDt.warehouse_code,'-1')),'%')  ";
				
				
				if ($TransSI==1 && $TransSR==1) $query= $query." 	And Deliveries.Journal_Type in ('DI','DR') ";
				else if ($TransSI==1 && $TransSR==0) $query= $query."	And Deliveries.Journal_Type in ('DI') ";
				else if ($TransSI==0 && $TransSR==1) $query= $query."	And Deliveries.Journal_Type in ('DR') ";
	  
				if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
					$query= $query." 	And DeliveryDt.warehouse_code In (select distinct warehouse_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
				if ($groupexist > 0)
					$query= $query." 	And Items.group_code In (select distinct group_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  

	$query= $query." 	Order by ItemCode, OrderDate, TransOrder, TransReference  ";				
	$query= $query." ) Q1 ";
	
	$SelectedTransaction = 1;
	
}


if ($TransTR == 1 ) {

	if ($SelectedTransaction == 1 ) 
		$query = $query." Union ";

	$query = $query." Select ItemCode, ItemDescription, TransReference, TransDate, TransDescription, WarehouseCode, QtyTransferFrom, QtyTransferTo, ".
					" QtyOut, QtyIn, QtyPrev, CurrencyCode, TransPrice, TransOrder, OrderDate, TransType, TransDtId, ProjectCode, CostCenterCode , meanPrice*QtyExists as RemainVal, ".
					" abs((ifnull(QtyTransferFrom,0)-ifnull(QtyTransferTo,0))+(ifnull(QtyIn,0)-ifnull(QtyOut,0)))*meanPrice as TransVal, batchno ".
					" From ( ";
					
	if ($searchserial == 1) $query = $query." Select PurchasesSerialNo.SerialNo_Code As ItemCode, ";
	else $query = $query." Select TransferDt.Item_Code As ItemCode, ";
	
	$query = $query." Items.Item_Description As ItemDescription, Transfer.Transfer_Reference As TransReference, Date_Format(Transfer.Transfer_Date,'%d/%m/%Y') As TransDate, ".
					" Transfer.Transfer_Date As OrderDate, Concat('TRANSFER : ', if(Transfer.Transfer_Remark is null,'',Transfer.Transfer_Remark)) As TransDescription, ".
					" 'TR' As TransType, TransferDt.TransferDt_ID As TransDtId, Concat('F: ',TransferDt_WarehouseFrom,' T: ',TransferDt_WarehouseTo) As WarehouseCode, 
					 Concat('F: ',Transferdt.Project_Code1,' T: ',Transferdt.Project_Code2) As ProjectCode, Concat('F: ',Transferdt.COSTCENT_CODE,' T: ',Transferdt.ToCostcent_Code) As CostCenterCode, ".
					" if (($fromwarehouse Is Null) or (TransferDt_WarehouseFrom between $fromwarehouse and $towarehouse) ,ifNull(TransferDt.TransferDt_Quantity,0), 0) As QtyTransferFrom, ".
					" if (($fromwarehouse Is Null) or (TransferDt_WarehouseTo between $fromwarehouse and $towarehouse) , ifNull(TransferDt.TransferDt_Quantity,0), 0) As QtyTransferTo, ".
					
					" ifNull(TransferDt.TransferDt_Quantity,0) As TransQty, Null As QtyOut, Null As QtyIn, ";
					
	if ($searchserial == 1) $query = $query." 0 As QtyPrev, ";
	else $query = $query." QQtyPrev.QuantityExists As QtyPrev, ";
	
	$query = $query." 3 As TransOrder, ".
					" if(upper($curr)='".$Base1."', '".$Base1."',if(upper($curr)='".$Base2."', '".$Base2."', transfer.CurencyCode)) As CurrencyCode, 0 As TransPrice, 0 as meanPrice, 0 as QtyExists, TransferDt_BatchNo as batchno ".
					" From TransferDt  ".
					" Inner Join Transfer on TransferDt.Transfer_ID = Transfer.Transfer_ID ".
					" Inner Join Items on TransferDt.Item_Code = Items.Item_Code ";

	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,'%') ";		 
		
	if ($searchserial == 1)
		$query= $query." Inner Join PurchasesSerialNo on PurchasesSerialNo.PurchaseDt_ID = TransferDt.TransferDt_ID And PurchasesSerialNo.Journal_Type IN ('TR','TRT') ";
	
	if ($searchserial == 0) {
		$query= $query. " Left Join (  ".
						"               Select Stk.Item_Code, Sum((ifNull(quantityproducein,0) + ifNull(quantityentered,0) - ifNull(quantityproduceout,0) - ifNull(quantitydelivered,0))) As QuantityExists  ".
						"               From StkTransaction Stk ".
						"               Where Date_Format(Stk.Transaction_Date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(Stk.Transaction_Date,'%Y-%m-%d')) ".
						"  				And Stk.item_code between ifNull(".$fromitem.",Stk.item_code) and ifNull(". $toitem.",Stk.item_code) ".
						"               And ifNull(Stk.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Stk.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(Stk.Project_Code,'-1')) ".
						"               And ifNull(Stk.Costcent_Code,'-1') between ifNull(".$fromcostcent.",ifNull(Stk.Costcent_Code,'-1')) and ifNull(". $tocostcent.",ifNull(Stk.Costcent_Code,'-1')) ".
						//"				And ifNull(Stk.warehouse_code,'-1') Like Concat( ifNull(". $fromwarehouse .",ifNull(Stk.warehouse_code,'-1')),'%')  ".
						"				And ifNull(Stk.warehouse_code,'-1') between ifNull(". $fromwarehouse .",ifNull(Stk.warehouse_code,'-1')) and ifNull(". $towarehouse .",ifNull(Stk.warehouse_code,'-1'))  ".
						"               And (($Module_Grn=1 and Stk.Transaction_Type <> 'PI') or ($Module_Grn=0 and 1=1)) ".
						"               Group By Stk.Item_Code ".
						"             ) As QQtyPrev on TransferDt.Item_Code = QQtyPrev.Item_Code   ";
	}
	
	$query= $query.	" Where (Transfer.Transfer_Reference Is Not Null And Transfer.Transfer_Reference != '')  ".
					" 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'))  ";
	//if($Module_expiredate==1 && $edatechk == 1)	
	//$query= $query." And Date_format(TransferDt_ExpireDate,'%Y-%m-%d') between ifNull(". $fromedate.",date_format(TransferDt_ExpireDate,'%Y-%m-%d')) and ifNull(". $toedate.",date_format(TransferDt_ExpireDate,'%Y-%m-%d'))  ";
					
	if ($searchserial == 0)	
		$query= $query." And TransferDt.Item_Code between ifNull(".$fromitem.",TransferDt.Item_Code) and ifNull(". $toitem.",TransferDt.Item_Code) ";
	else
		$query= $query." And PurchasesSerialNo.SerialNo_Code between ifNull(".$fromitem.",PurchasesSerialNo.SerialNo_Code) and ifNull(". $toitem.",PurchasesSerialNo.SerialNo_Code) ";
	
	$query .= " and ifnull(TransferDt_BatchNo,'') between ifnull(".$frombatchno.",ifnull(TransferDt_BatchNo,''))
				and ifnull(".$tobatchno.",ifnull(TransferDt_BatchNo,'')) ";				
				
	$query= $query.	" 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(". $fromtranswh .",ifNull(TransferDt.TransferDt_WarehouseFrom,'-1'))  ".
					" And ifNull(TransferDt.TransferDt_WarehouseTo,'-1') = ifNull(". $totranswh .",ifNull(TransferDt.TransferDt_WarehouseTo,'-1'))  ".
					" And (( ifNull(TransferDt.TransferDt_WarehouseFrom,'-1') between ifNull(". $fromwarehouse .",ifNull(TransferDt.TransferDt_WarehouseFrom,'-1')) and ifNull(". $towarehouse .",ifNull(TransferDt.TransferDt_WarehouseFrom,'-1')) ) or   ".
					" 	   ( ifNull(TransferDt.TransferDt_WarehouseTo,'-1') between ifNull(". $fromwarehouse .",ifNull(TransferDt.TransferDt_WarehouseTo,'-1')) and 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 ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
				if ($groupexist > 0)
					$query= $query." 	And Items.group_code In (select distinct group_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  

	$query= $query." 	Order by ItemCode, OrderDate, TransOrder, TransReference  ";				
	$query= $query." ) Q1 ";
	
	$SelectedTransaction = 1;
	
}

if ($TransAD == 1 ) {

	if ($SelectedTransaction == 1 ) 
		$query = $query." Union ";

	$query = $query." Select ItemCode, ItemDescription, TransReference, TransDate, TransDescription, WarehouseCode, QtyTransferFrom, QtyTransferTo, ".
					" QtyOut, QtyIn, QtyPrev, CurrencyCode, TransPrice, TransOrder, OrderDate, TransType, TransDtId ,ProjectCode, CostCenterCode,  meanPrice*QtyExists as RemainVal, ".
					" abs((ifnull(QtyTransferFrom,0)-ifnull(QtyTransferTo,0))+(ifnull(QtyIn,0)-ifnull(QtyOut,0)))*meanPrice as TransVal, batchno ".
					" From ( ";
					
	if ($searchserial == 1) $query = $query." Select PurchasesSerialNo.SerialNo_Code As ItemCode, ";
	else $query = $query." Select AdjustmentDt.Item_Code As ItemCode, ";
	
	$query = $query." Items.Item_Description As ItemDescription, Adjustment.Adjustment_Reference As TransReference, Date_Format(Adjustment.Adjustment_Date,'%d/%m/%Y') As TransDate, ".
					" Adjustment.Adjustment_Date As OrderDate, Concat('ADJUSTMENT : ', if(Adjustment.Adjustment_Remak is null,'',Adjustment.Adjustment_Remak)) As TransDescription, ".
					" AdjustmentDt.Journal_Type As TransType, AdjustmentDt.AdjustmentDt_ID As TransDtId, AdjustmentDt.Warehouse_Code As WarehouseCode,AdjustmentDt.Project_Code As ProjectCode,AdjustmentDt.Costcent_Code As CostCenterCode, ".
					" Null As QtyTransferFrom, Null As QtyTransferTo, ".
					" if(AdjustmentDt.Journal_Type='AI', ifNull(AdjustmentDt.AdjustmentDt_Quantity,0), ifNull(AdjustmentDt.AdjustmentDt_Quantity,0)) As TransQty, ".
					" if(AdjustmentDt.Journal_Type='AR', Abs(ifNull(AdjustmentDt.AdjustmentDt_Quantity,0)), Null) As QtyOut, ".
					" if(AdjustmentDt.Journal_Type='AI', ifNull(AdjustmentDt.AdjustmentDt_Quantity,0), Null) As QtyIn, ";
					
	if ($searchserial == 1) $query = $query." 0 As QtyPrev, ";
	else $query = $query." QQtyPrev.QuantityExists As QtyPrev, ";
	
	$query = $query." 8 As TransOrder, ".
					" if(upper($curr)='".$Base1."', '".$Base1."',if(upper($curr)='".$Base2."', '".$Base2."', Adjustment.Currency_Code)) As CurrencyCode, ";
					
					if ($updmeancost == 1)
						$query= $query." ifNull(MeanCostCur(AdjustmentDt.Item_Code, '".$lastdate."', 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) TransPrice ";
					else
						$query= $query." if(upper($curr)='".$Base1."',AdjustmentDt.AdjustmentDt_PriceBase1,if(upper($curr)='".$Base2."',AdjustmentDt.AdjustmentDt_PriceBase2, AdjustmentDt.AdjustmentDt_Price)) As TransPrice ";
									   
	$query= $query. " , ifNull(MeanCostCur(AdjustmentDt.Item_Code, '".$lastdate."', 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)  as meanPrice
					  , ifnull(QtyExistsWSorder(AdjustmentDt.Item_Code, AdjustmentDt.Warehouse_Code, '".$lastdate."', 0, '', '', ''),0) QtyExists,AdjustmentDt_BatchNo as batchno ";			   
	$query= $query. " From AdjustmentDt  ".
					" Inner Join Adjustment on AdjustmentDt.Adjustment_ID = Adjustment.Adjustment_ID ".
					" Inner Join Items on AdjustmentDt.Item_Code = Items.Item_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,'%') ";		 
		
	if ($searchserial == 1)
		$query= $query." Inner Join PurchasesSerialNo on PurchasesSerialNo.PurchaseDt_ID = AdjustmentDt.AdjustmentDt_ID And PurchasesSerialNo.Journal_Type = AdjustmentDt.Journal_Type ";
		
	if ($searchserial == 0) {	
		$query= $query. " Left Join (  ".
						"               Select Stk.Item_Code, Sum((ifNull(quantityproducein,0) + ifNull(quantityentered,0) - ifNull(quantityproduceout,0) - ifNull(quantitydelivered,0))) As QuantityExists  ".
						"               From StkTransaction Stk ".
						"               Where Date_Format(Stk.Transaction_Date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(Stk.Transaction_Date,'%Y-%m-%d')) ".
						"  				And Stk.item_code between ifNull(".$fromitem.",Stk.item_code) and ifNull(". $toitem.",Stk.item_code) ".
						"               And ifNull(Stk.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Stk.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(Stk.Project_Code,'-1')) ".
						"               And ifNull(Stk.Costcent_Code,'-1') between ifNull(".$fromcostcent.",ifNull(Stk.Costcent_Code,'-1')) and ifNull(". $tocostcent.",ifNull(Stk.Costcent_Code,'-1')) ".
						//"				And ifNull(Stk.warehouse_code,'-1') Like Concat( ifNull(". $fromwarehouse .",ifNull(Stk.warehouse_code,'-1')),'%')  ".
						"				And ifNull(Stk.warehouse_code,'-1') between ifNull(". $fromwarehouse .",ifNull(Stk.warehouse_code,'-1')) and ifNull(". $towarehouse .",ifNull(Stk.warehouse_code,'-1'))  ".
						"               And (($Module_Grn=1 and Stk.Transaction_Type <> 'PI') or ($Module_Grn=0 and 1=1)) ".
						"               Group By Stk.Item_Code ".
						"             ) As QQtyPrev on AdjustmentDt.Item_Code = QQtyPrev.Item_Code   ";
	}
	
	$query= $query.	" Where (Adjustment.Adjustment_Reference Is Not Null And Adjustment.Adjustment_Reference != '')  ".
					" 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'))  ";
	if($Module_expiredate==1 && $edatechk == 1)	
	$query= $query." And Date_format(AdjustmentDt_ExpireDate,'%Y-%m-%d') between ifNull(". $fromedate.",date_format(AdjustmentDt_ExpireDate,'%Y-%m-%d')) and ifNull(". $toedate.",date_format(AdjustmentDt_ExpireDate,'%Y-%m-%d'))  ";
					
	if ($searchserial == 0)	
		$query= $query." And AdjustmentDt.Item_Code between ifNull(".$fromitem.",AdjustmentDt.Item_Code) and ifNull(". $toitem.",AdjustmentDt.Item_Code) ";
	else
		$query= $query." And PurchasesSerialNo.SerialNo_Code between ifNull(".$fromitem.",PurchasesSerialNo.SerialNo_Code) and ifNull(". $toitem.",PurchasesSerialNo.SerialNo_Code) ";	
	
	$query .= " and ifnull(AdjustmentDt_BatchNo,'') between ifnull(".$frombatchno.",ifnull(AdjustmentDt_BatchNo,''))
				and ifnull(".$tobatchno.",ifnull(AdjustmentDt_BatchNo,'')) ";
				
	$query= $query.	" 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'))  ";
					//" And ifNull(AdjustmentDt.warehouse_code,'-1') Like Concat(ifNull(". $fromwarehouse .",ifNull(AdjustmentDt.warehouse_code,'-1')),'%')  ";
				
				
	  
				if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
					$query= $query." 	And AdjustmentDt.warehouse_code In (select distinct warehouse_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
				if ($groupexist > 0)
					$query= $query." 	And Items.group_code In (select distinct group_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  

	$query= $query." 	Order by ItemCode, OrderDate, TransOrder, TransReference  ";				
	$query= $query." ) Q1 ";
	
	$SelectedTransaction = 1;
	
}



if ($TransPD == 1 ) {

	if ($SelectedTransaction == 1 ) 
		$query = $query." Union ";

	$query = $query." Select ItemCode, ItemDescription, TransReference, TransDate, TransDescription, WarehouseCode, QtyTransferFrom, QtyTransferTo, ".
					" QtyOut, QtyIn, QtyPrev, CurrencyCode, TransPrice, TransOrder, OrderDate, TransType, TransDtId , ProjectCode, CostcenterCode , meanPrice*QtyExists as RemainVal, ".
					" abs((ifnull(QtyTransferFrom,0)-ifnull(QtyTransferTo,0))+(ifnull(QtyIn,0)-ifnull(QtyOut,0)))*meanPrice as TransVal, batchno ".
					" From ( ";
					
	if ($searchserial == 1) $query = $query." Select PurchasesSerialNo.SerialNo_Code As ItemCode, ";
	else $query = $query." Select ProductionDt.Item_Code As ItemCode, ";
	
	$query = $query." Items.Item_Description As ItemDescription, Production.Production_Reference As TransReference, Date_Format(Production.Production_Date,'%d/%m/%Y') As TransDate, ".
					" Production.Production_Date As OrderDate, Concat('PRODUCTION OUT : ', if(Production.Production_Remark is null,'',Production.Production_Remark)) As TransDescription, ".
					" 'PDT' As TransType, ProductionDt.ProductionDt_ID As TransDtId, ProductionDt.Warehouse_Code As WarehouseCode,ProductionDt.Project_Code As ProjectCode,ProductionDt.Costcenter_Code As CostcenterCode, ".
					" Null As QtyTransferFrom, Null As QtyTransferTo, ".
					" (-1)*ifNull(ProductionDt.ProductionDt_Quantity,0) As TransQty, ".
					" ifNull(ProductionDt.ProductionDt_Quantity,0) As QtyOut, Null As QtyIn, ";
					
	if ($searchserial == 1) $query = $query." 0 As QtyPrev, ";
	else $query = $query." QQtyPrev.QuantityExists As QtyPrev, ";
	
	$query = $query." 7 As TransOrder, ".
					" if(upper($curr)='".$Base1."', '".$Base1."',if(upper($curr)='".$Base2."', '".$Base2."', Production.Currency_Code)) As CurrencyCode, ";
					
					if ($updmeancost == 1)
						$query= $query." ifNull(MeanCostCur(ProductionDt.Item_Code, '".$lastdate."', 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) TransPrice ";
					else
						$query= $query." if(upper($curr)='".$Base1."',ProductionDt.ProductionDt_CostBase1,if(upper($curr)='".$Base2."',ProductionDt.ProductionDt_CostBase2, ProductionDt.ProductionDt_Cost)) As TransPrice ";
						
	$query= $query. " , ifNull(MeanCostCur(ProductionDt.Item_Code, '".$lastdate."', 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)  as meanPrice
				      , ifnull(QtyExistsWSorder(ProductionDt.Item_Code, ProductionDt.Warehouse_Code, '".$lastdate."', 0, 'DI', '', ''),0) QtyExists, ProductionDt_BatchNo as batchno ";			   
	$query= $query. " From ProductionDt  ".
					" Inner Join Production on ProductionDt.Production_ID = Production.Production_ID ".
					" Inner Join Items on ProductionDt.Item_Code = Items.Item_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,'%') ";		 
		
	if ($searchserial == 1)
		$query= $query." Inner Join PurchasesSerialNo on PurchasesSerialNo.PurchaseDt_ID = ProductionDt.ProductionDt_ID And PurchasesSerialNo.Journal_Type = 'PDT' ";
	
	if ($searchserial == 0) {	
		$query= $query. " Left Join (  ".
						"               Select Stk.Item_Code, Sum((ifNull(quantityproducein,0) + ifNull(quantityentered,0) - ifNull(quantityproduceout,0) - ifNull(quantitydelivered,0))) As QuantityExists  ".
						"               From StkTransaction Stk ".
						"               Where Date_Format(Stk.Transaction_Date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(Stk.Transaction_Date,'%Y-%m-%d')) ".
						"  				And Stk.item_code between ifNull(".$fromitem.",Stk.item_code) and ifNull(". $toitem.",Stk.item_code) ".
						"               And ifNull(Stk.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Stk.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(Stk.Project_Code,'-1')) ".
						"               And ifNull(Stk.Costcent_Code,'-1') between ifNull(".$fromcostcent.",ifNull(Stk.Costcent_Code,'-1')) and ifNull(". $tocostcent.",ifNull(Stk.Costcent_Code,'-1')) ".
						//"				And ifNull(Stk.warehouse_code,'-1') Like Concat( ifNull(". $fromwarehouse .",ifNull(Stk.warehouse_code,'-1')),'%')  ".
						"				And ifNull(Stk.warehouse_code,'-1') between ifNull(". $fromwarehouse .",ifNull(Stk.warehouse_code,'-1')) and ifNull(". $towarehouse .",ifNull(Stk.warehouse_code,'-1'))  ".
						"               And (($Module_Grn=1 and Stk.Transaction_Type <> 'PI') or ($Module_Grn=0 and 1=1)) ".
						"               Group By Stk.Item_Code ".
						"             ) As QQtyPrev on ProductionDt.Item_Code = QQtyPrev.Item_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'))  ";
	if($Module_expiredate==1 && $edatechk == 1)	
	$query= $query." And Date_format(ProductionDt_ExpireDate,'%Y-%m-%d') between ifNull(". $fromedate.",date_format(ProductionDt_ExpireDate,'%Y-%m-%d')) and ifNull(". $toedate.",date_format(ProductionDt_ExpireDate,'%Y-%m-%d'))  ";
					
	if ($searchserial == 0)	
		$query= $query." And ProductionDt.Item_Code between ifNull(".$fromitem.",ProductionDt.Item_Code) and ifNull(". $toitem.",ProductionDt.Item_Code) ";
	else
		$query= $query." And PurchasesSerialNo.SerialNo_Code between ifNull(".$fromitem.",PurchasesSerialNo.SerialNo_Code) and ifNull(". $toitem.",PurchasesSerialNo.SerialNo_Code) ";		
	
	$query .= " and ifnull(ProductionDt_BatchNo,'') between ifnull(".$frombatchno.",ifnull(ProductionDt_BatchNo,''))
				and ifnull(".$tobatchno.",ifnull(ProductionDt_BatchNo,'')) ";
					
	$query= $query.	" 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'))  ";
					//" And ifNull(ProductionDt.warehouse_code,'-1') Like Concat(ifNull(". $fromwarehouse .",ifNull(ProductionDt.warehouse_code,'-1')),'%')  ";
				
	  
				if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
					$query= $query." 	And ProductionDt.warehouse_code In (select distinct warehouse_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
				if ($groupexist > 0)
					$query= $query." 	And Items.group_code In (select distinct group_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
	
		
	$query= $query." Union ";
		
	if ($searchserial == 1) $query = $query." Select PurchasesSerialNo.SerialNo_Code As ItemCode, ";
	else $query = $query." Select Production.Item_Code As ItemCode, ";
	
	$query = $query." Items.Item_Description As ItemDescription, Production.Production_Reference As TransReference, Date_Format(Production.Production_Date,'%d/%m/%Y') As TransDate, ".
					" Production.Production_Date As OrderDate, Concat('PRODUCTION IN : ', if(Production.Production_Remark is null,'',Production.Production_Remark)) As TransDescription, ".
					" 'PD' As TransType, Production.Production_ID As TransDtId, Production.Warehouse_Code As WarehouseCode,Production.Project_Code As ProjectCode,Production.Costcenter_Code As CostcenterCode,".
					" Null As QtyTransferFrom, Null As QtyTransferTo, ".
					" ifNull(Production.Production_Quantity,0) As TransQty, ".
					" Null As QtyOut, ifNull(Production.Production_Quantity,0) As QtyIn, ";
					
	if ($searchserial == 1) $query = $query." 0 As QtyPrev, ";
	else $query = $query." QQtyPrev.QuantityExists As QtyPrev, ";
	
	$query = $query." 4 As TransOrder, ".
					" if(upper($curr)='".$Base1."', '".$Base1."',if(upper($curr)='".$Base2."', '".$Base2."', Production.Currency_Code)) As CurrencyCode, ";
					
					if ($updmeancost == 1)
						$query= $query." ifNull(MeanCostCur(Production.Item_Code, '".$lastdate."', 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) TransPrice ";
					else
						$query= $query." if(upper($curr)='".$Base1."',Production.Production_TotalBase1,if(upper($curr)='".$Base2."',Production.Production_TotalBase2, Production.Production_Total))/ifNull(Production.Production_Quantity,1) As TransPrice  ";
	$query= $query. " , ifNull(MeanCostCur(Production.Item_Code, '".$lastdate."', 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)  as meanPrice
				      , ifnull(QtyExistsWSorder(Production.Item_Code, Production.Warehouse_Code, '".$lastdate."', 0, 'DI', '', ''),0) QtyExists, Production_BatchNo as batchno ";
						
	$query= $query. " From Production  ".
					" Inner Join Items on Production.Item_Code = Items.Item_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,'%') ";		 
		
	if ($searchserial == 1)
		$query= $query." Inner Join PurchasesSerialNo on PurchasesSerialNo.PurchaseDt_ID = Production.Production_ID And PurchasesSerialNo.Journal_Type = 'PD' ";
		
	if ($searchserial == 0) {					   
		$query= $query. " Left Join (  ".
						"               Select Stk.Item_Code, Sum((ifNull(quantityproducein,0) + ifNull(quantityentered,0) - ifNull(quantityproduceout,0) - ifNull(quantitydelivered,0))) As QuantityExists  ".
						"               From StkTransaction Stk ".
						"               Where Date_Format(Stk.Transaction_Date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(Stk.Transaction_Date,'%Y-%m-%d')) ".
						"  				And Stk.item_code between ifNull(".$fromitem.",Stk.item_code) and ifNull(". $toitem.",Stk.item_code) ".
						"               And ifNull(Stk.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Stk.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(Stk.Project_Code,'-1')) ".
						"               And ifNull(Stk.Costcent_Code,'-1') between ifNull(".$fromcostcent.",ifNull(Stk.Costcent_Code,'-1')) and ifNull(". $tocostcent.",ifNull(Stk.Costcent_Code,'-1')) ".
						//"				And ifNull(Stk.warehouse_code,'-1') Like Concat( ifNull(". $fromwarehouse .",ifNull(Stk.warehouse_code,'-1')),'%')  ".
						"				And ifNull(Stk.warehouse_code,'-1') between ifNull(". $fromwarehouse .",ifNull(Stk.warehouse_code,'-1')) and ifNull(". $towarehouse .",ifNull(Stk.warehouse_code,'-1'))  ".
						"               And (($Module_Grn=1 and Stk.Transaction_Type <> 'PI') or ($Module_Grn=0 and 1=1)) ".
						"               Group By Stk.Item_Code ".
						"             ) As QQtyPrev on Production.Item_Code = QQtyPrev.Item_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'))  ";
	if($Module_expiredate==1 && $edatechk == 1)	
	$query= $query." And Date_format(Production_ExpireDate,'%Y-%m-%d') between ifNull(". $fromedate.",date_format(Production_ExpireDate,'%Y-%m-%d')) and ifNull(". $toedate.",date_format(Production_ExpireDate,'%Y-%m-%d'))  ";
					
	if ($searchserial == 0)	
		$query= $query." And Production.Item_Code between ifNull(".$fromitem.",Production.Item_Code) and ifNull(". $toitem.",Production.Item_Code) ";
	else
		$query= $query." And PurchasesSerialNo.SerialNo_Code between ifNull(".$fromitem.",PurchasesSerialNo.SerialNo_Code) and ifNull(". $toitem.",PurchasesSerialNo.SerialNo_Code) ";		
	
	$query .= " and ifnull(Production_BatchNo,'') between ifnull(".$frombatchno.",ifnull(Production_BatchNo,''))
				and ifnull(".$tobatchno.",ifnull(Production_BatchNo,'')) ";	
				
	$query= $query.	" 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')) ";
					//" And ifNull(Production.warehouse_code,'-1') Like Concat(ifNull(". $fromwarehouse .",ifNull(Production.warehouse_code,'-1')),'%')  ";
				
	  
				if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
					$query= $query." 	And Production.warehouse_code In (select distinct warehouse_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
				if ($groupexist > 0)
					$query= $query." 	And Items.group_code In (select distinct group_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
					
	$query= $query." 	Order by ItemCode, OrderDate, TransOrder, TransReference  ";				
	$query= $query." ) Q1 ";
	
	$SelectedTransaction = 1;
	
}

if ($TransPD == 1 ) {

	if ($SelectedTransaction == 1 ) 
		$query = $query." Union ";

	$query = $query." Select ItemCode, ItemDescription, TransReference, TransDate, TransDescription, WarehouseCode, QtyTransferFrom, QtyTransferTo, ".
					" QtyOut, QtyIn, QtyPrev, CurrencyCode, TransPrice, TransOrder, OrderDate, TransType, TransDtId, ProjectCode, CostcenterCode, meanPrice*QtyExists as RemainVal, ".
					" abs((ifnull(QtyTransferFrom,0)-ifnull(QtyTransferTo,0))+(ifnull(QtyIn,0)-ifnull(QtyOut,0)))*meanPrice as TransVal, batchno ".
					" From ( ";
					
	if ($searchserial == 1) $query = $query." Select PurchasesSerialNo.SerialNo_Code As ItemCode, ";
	else $query = $query." Select ProductionRM.Item_Code As ItemCode, ";
	
	$query = $query." Items.Item_Description As ItemDescription, ProductionHD.ProductionHD_Reference As TransReference, Date_Format(ProductionHD.ProductionHD_Date,'%d/%m/%Y') As TransDate, ".
					" ProductionHD.ProductionHD_Date As OrderDate, Concat('PRODUCTION OUT : ', if(ProductionHD.ProductionHD_Remark is null,'',ProductionHD.ProductionHD_Remark)) As TransDescription, ".
					" 'PDT' As TransType, ProductionRM.ProductionRM_ID As TransDtId, ProductionRM.Warehouse_Code As WarehouseCode,ProductionRM.Project_Code As ProjectCode,ProductionRM.Costcenter_Code As CostcenterCode,".
					" Null As QtyTransferFrom, Null As QtyTransferTo, ".
					" (-1)*ifNull(ProductionRM.ProductionRM_Quantity,0) As TransQty, ".
					" ifNull(ProductionRM.ProductionRM_Quantity,0) As QtyOut, Null As QtyIn, ";
					
	if ($searchserial == 1) $query = $query." 0 As QtyPrev, ";
	else $query = $query." QQtyPrev.QuantityExists As QtyPrev, ";
	
	$query = $query." 7 As TransOrder, ".
					" if(upper($curr)='".$Base1."', '".$Base1."',if(upper($curr)='".$Base2."', '".$Base2."', ProductionHD.Currency_Code)) As CurrencyCode, ";
					
					if ($updmeancost == 1)
						$query= $query." ifNull(MeanCostCur(ProductionRM.Item_Code, '".$lastdate."', if(upper($curr)='".$Base1."', 1,if(upper($curr)='".$Base2."',2, 1)), ProductionHD.Currency_Code, if(upper($curr)='".$Base1."', '',if(upper($curr)='".$Base2."','',items.currency_code ))),0) TransPrice ";
					else
						$query= $query." if(upper($curr)='".$Base1."',ProductionRM.ProductionRM_CostBase1,if(upper($curr)='".$Base2."',ProductionRM.ProductionRM_CostBase2, ProductionRM.ProductionRM_Cost)) As TransPrice ";
						
	$query= $query. " , ifNull(MeanCostCur(ProductionRM.Item_Code, '".$lastdate."', if(upper($curr)='".$Base1."', 1,if(upper($curr)='".$Base2."',2, 1)), ProductionHD.Currency_Code, if(upper($curr)='".$Base1."', '',if(upper($curr)='".$Base2."','',items.currency_code ))),0)  as meanPrice
				      , ifnull(QtyExistsWSorder(ProductionRM.Item_Code, ProductionRM.Warehouse_Code, '".$lastdate."', 0, 'DI', '', ''),0) QtyExists, ProductionRM_BatchNo as batchno  ";			   
	$query= $query. " From ProductionRM  ".
					" Inner Join ProductionHD on ProductionRM.ProductionHD_ID = ProductionHD.ProductionHD_ID ".
					" Inner Join Items on ProductionRM.Item_Code = Items.Item_Code ";

	/*if ($projectexist >	0 && $FI_RepRestrictProject == 1)
		$query= $query.$QueryRestrictPrj." on ProductionHD.project_code like concat(Q4.project_code,'%') ";
								 
	if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
		$query= $query.$QueryRestrictCst." on ProductionHD.Costcenter_Code like concat(Q5.costcent_code,'%') ";	*/	 
		
	if ($searchserial == 1)
		$query= $query." Inner Join PurchasesSerialNo on PurchasesSerialNo.PurchaseDt_ID = ProductionRM.ProductionRM_ID And PurchasesSerialNo.Journal_Type IN ('PDT','PDO') ";
	
	if ($searchserial == 0) {	
		$query= $query. " Left Join (  ".
						"               Select Stk.Item_Code, Sum((ifNull(quantityproducein,0) + ifNull(quantityentered,0) - ifNull(quantityproduceout,0) - ifNull(quantitydelivered,0))) As QuantityExists  ".
						"               From StkTransaction Stk ".
						"               Where Date_Format(Stk.Transaction_Date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(Stk.Transaction_Date,'%Y-%m-%d')) ".
						"  				And Stk.item_code between ifNull(".$fromitem.",Stk.item_code) and ifNull(". $toitem.",Stk.item_code) ".
						"               And ifNull(Stk.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Stk.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(Stk.Project_Code,'-1')) ".
						"               And ifNull(Stk.Costcent_Code,'-1') between ifNull(".$fromcostcent.",ifNull(Stk.Costcent_Code,'-1')) and ifNull(". $tocostcent.",ifNull(Stk.Costcent_Code,'-1')) ".
						//"				And ifNull(Stk.warehouse_code,'-1') Like Concat( ifNull(". $fromwarehouse .",ifNull(Stk.warehouse_code,'-1')),'%')  ".
						"				And ifNull(Stk.warehouse_code,'-1') between ifNull(". $fromwarehouse .",ifNull(Stk.warehouse_code,'-1')) and ifNull(". $towarehouse .",ifNull(stk.warehouse_code,'-1')) ".
						"               And (($Module_Grn=1 and Stk.Transaction_Type <> 'PI') or ($Module_Grn=0 and 1=1)) ".
						"               Group By Stk.Item_Code ".
						"             ) As QQtyPrev on ProductionRM.Item_Code = QQtyPrev.Item_Code   ";
	}
	
	$query= $query.	" Where (ProductionHD.ProductionHD_Reference Is Not Null And ProductionHD.ProductionHD_Reference != '')  ".
					" And Date_format(ProductionHD.ProductionHD_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(ProductionHD.ProductionHD_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(ProductionHD.ProductionHD_Date,'%Y-%m-%d'))  ";
	if($Module_expiredate==1 && $edatechk == 1)	
	$query= $query." And Date_format(ProductionRM_ExpireDate,'%Y-%m-%d') between ifNull(". $fromedate.",date_format(ProductionRM_ExpireDate,'%Y-%m-%d')) and ifNull(". $toedate.",date_format(ProductionRM_ExpireDate,'%Y-%m-%d'))  ";
					
	if ($searchserial == 0)	
		$query= $query." And ProductionRM.Item_Code between ifNull(".$fromitem.",ProductionRM.Item_Code) and ifNull(". $toitem.",ProductionRM.Item_Code) ";
	else
		$query= $query." And PurchasesSerialNo.SerialNo_Code between ifNull(".$fromitem.",PurchasesSerialNo.SerialNo_Code) and ifNull(". $toitem.",PurchasesSerialNo.SerialNo_Code) ";		
	
	$query .= " and ifnull(ProductionRM_BatchNo,'') between ifnull(".$frombatchno.",ifnull(ProductionRM_BatchNo,''))
				and ifnull(".$tobatchno.",ifnull(ProductionRM_BatchNo,'')) ";	
	$query= $query.	" 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(ProductionHD.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(ProductionHD.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(ProductionHD.Project_Code,'-1')) ".
					//" And ifNull(ProductionHD.Costcenter_Code,'-1') between ifNull(".$fromcostcent.",ifNull(ProductionHD.Costcenter_Code,'-1')) and ifNull(". $tocostcent.",ifNull(ProductionHD.Costcenter_Code,'-1')) ".
					//" And ifNull(ProductionRM.warehouse_code,'-1') Like Concat(ifNull(". $fromwarehouse .",ifNull(ProductionRM.warehouse_code,'-1')),'%')  ";
					" And ifNull(ProductionRM.warehouse_code,'-1') between ifNull(". $fromwarehouse .",ifNull(ProductionRM.warehouse_code,'-1')) and ifNull(". $towarehouse .",ifNull(ProductionRM.warehouse_code,'-1')) ";
				
	  
				if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
					$query= $query." 	And ProductionRM.warehouse_code In (select distinct warehouse_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
				if ($groupexist > 0)
					$query= $query." 	And Items.group_code In (select distinct group_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
	
		
	$query= $query." Union ";
		
	if ($searchserial == 1) $query = $query." Select PurchasesSerialNo.SerialNo_Code As ItemCode, ";
	else $query = $query." Select ProductionFG.Item_Code As ItemCode, ";
	
	$query = $query." Items.Item_Description As ItemDescription, ProductionHD.ProductionHD_Reference As TransReference, Date_Format(ProductionHD.ProductionHD_Date,'%d/%m/%Y') As TransDate, ".
					" ProductionHD.ProductionHD_Date As OrderDate, Concat('PRODUCTION IN : ', if(ProductionHD.ProductionHD_Remark is null,'',ProductionHD.ProductionHD_Remark)) As TransDescription, ".
					" 'PD' As TransType, ProductionFG.ProductionFG_ID As TransDtId, ProductionFG.Warehouse_Code As WarehouseCode,ProductionFG.Project_Code As ProjectCode,ProductionFG.Costcenter_Code As CostcenterCode,".
					" Null As QtyTransferFrom, Null As QtyTransferTo, ".
					" ifNull(ProductionFG.ProductionFG_Quantity,0) As TransQty, ".
					" Null As QtyOut, ifNull(ProductionFG.ProductionFG_Quantity,0) As QtyIn, ";
					
	if ($searchserial == 1) $query = $query." 0 As QtyPrev, ";
	else $query = $query." QQtyPrev.QuantityExists As QtyPrev, ";
	
	$query = $query." 4 As TransOrder, ".
					" if(upper($curr)='".$Base1."', '".$Base1."',if(upper($curr)='".$Base2."', '".$Base2."', ProductionHD.Currency_Code)) As CurrencyCode, ";
					
					if ($updmeancost == 1)
						$query= $query." ifNull(MeanCostCur(ProductionFG.Item_Code, '".$lastdate."', if(upper($curr)='".$Base1."', 1,if(upper($curr)='".$Base2."',2, 1)), ProductionHD.Currency_Code, if(upper($curr)='".$Base1."', '',if(upper($curr)='".$Base2."','',items.currency_code ))),0) TransPrice ";
					else
						$query= $query." if(upper($curr)='".$Base1."',ProductionFG.ProductionFG_CostBase1,if(upper($curr)='".$Base2."',ProductionFG.ProductionFG_CostBase2, ProductionFG.ProductionFG_Cost)) As TransPrice ";
						
	$query= $query. " , ifNull(MeanCostCur(ProductionFG.Item_Code, '".$lastdate."', if(upper($curr)='".$Base1."', 1,if(upper($curr)='".$Base2."',2, 1)), ProductionHD.Currency_Code, if(upper($curr)='".$Base1."', '',if(upper($curr)='".$Base2."','',items.currency_code ))),0) as meanPrice
				      , ifnull(QtyExistsWSorder(ProductionFG.Item_Code, ProductionFG.Warehouse_Code, '".$lastdate."', 0, 'DI', '', ''),0) QtyExists, ProductionFG_BatchNo as batchno ";			   
	$query= $query. " From ProductionFG  ".
					" Inner Join ProductionHD on ProductionFG.ProductionHD_ID = ProductionHD.ProductionHD_ID ".
					" Inner Join Items on ProductionFG.Item_Code = Items.Item_Code ";

	/*if ($projectexist >	0 && $FI_RepRestrictProject == 1)
		$query= $query.$QueryRestrictPrj." on ProductionHD.project_code like concat(Q4.project_code,'%') ";
								 
	if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
		$query= $query.$QueryRestrictCst." on ProductionHD.Costcenter_Code like concat(Q5.costcent_code,'%') ";	*/	 
		
	if ($searchserial == 1)
		$query= $query." Inner Join PurchasesSerialNo on PurchasesSerialNo.PurchaseDt_ID = ProductionFG.ProductionFG_ID And PurchasesSerialNo.Journal_Type = 'PD' ";

	if ($searchserial == 0) {					   
		$query= $query. " Left Join (  ".
						"               Select Stk.Item_Code, Sum((ifNull(quantityproducein,0) + ifNull(quantityentered,0) - ifNull(quantityproduceout,0) - ifNull(quantitydelivered,0))) As QuantityExists  ".
						"               From StkTransaction Stk ".
						"               Where Date_Format(Stk.Transaction_Date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(Stk.Transaction_Date,'%Y-%m-%d')) ".
						"  				And Stk.item_code between ifNull(".$fromitem.",Stk.item_code) and ifNull(". $toitem.",Stk.item_code) ".
						"               And ifNull(Stk.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Stk.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(Stk.Project_Code,'-1')) ".
						"               And ifNull(Stk.Costcent_Code,'-1') between ifNull(".$fromcostcent.",ifNull(Stk.Costcent_Code,'-1')) and ifNull(". $tocostcent.",ifNull(Stk.Costcent_Code,'-1')) ".
						//"				And ifNull(Stk.warehouse_code,'-1') Like Concat( ifNull(". $fromwarehouse .",ifNull(Stk.warehouse_code,'-1')),'%')  ".
						"				And ifNull(Stk.warehouse_code,'-1') between ifNull(". $fromwarehouse .",ifNull(Stk.warehouse_code,'-1')) and ifNull(". $towarehouse .",ifNull(Stk.warehouse_code,'-1'))  ".
						"               And (($Module_Grn=1 and Stk.Transaction_Type <> 'PI') or ($Module_Grn=0 and 1=1)) ".
						"               Group By Stk.Item_Code ".
						"             ) As QQtyPrev on ProductionFG.Item_Code = QQtyPrev.Item_Code   ";
	}
	
	$query= $query.	" Where (ProductionHD.ProductionHD_Reference Is Not Null And ProductionHD.ProductionHD_Reference != '')  ".
					" And Date_format(ProductionHD.ProductionHD_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(ProductionHD.ProductionHD_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(ProductionHD.ProductionHD_Date,'%Y-%m-%d'))  ";
	
	if($Module_expiredate==1 && $edatechk == 1)	
	$query= $query." And Date_format(ProductionFG_ExpireDate,'%Y-%m-%d') between ifNull(". $fromedate.",date_format(ProductionFG_ExpireDate,'%Y-%m-%d')) and ifNull(". $toedate.",date_format(ProductionFG_ExpireDate,'%Y-%m-%d'))  ";
		
	if ($searchserial == 0)	
		$query= $query." And ProductionFG.Item_Code between ifNull(".$fromitem.",ProductionFG.Item_Code) and ifNull(". $toitem.",ProductionFG.Item_Code) ";
	else
		$query= $query." And PurchasesSerialNo.SerialNo_Code between ifNull(".$fromitem.",PurchasesSerialNo.SerialNo_Code) and ifNull(". $toitem.",PurchasesSerialNo.SerialNo_Code) ";		
	
	$query .= " and ifnull(ProductionFG_BatchNo,'') between ifnull(".$frombatchno.",ifnull(ProductionFG_BatchNo,''))
				and ifnull(".$tobatchno.",ifnull(ProductionFG_BatchNo,'')) ";	
	$query= $query.	" 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(ProductionHD.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(ProductionHD.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(ProductionHD.Project_Code,'-1')) ".
					//" And ifNull(ProductionHD.Costcenter_Code,'-1') between ifNull(".$fromcostcent.",ifNull(ProductionHD.Costcenter_Code,'-1')) and ifNull(". $tocostcent.",ifNull(ProductionHD.Costcenter_Code,'-1')) ".
					//" And ifNull(ProductionFG.warehouse_code,'-1') Like Concat(ifNull(". $fromwarehouse .",ifNull(ProductionFG.warehouse_code,'-1')),'%')  ";
					" And ifNull(ProductionFG.warehouse_code,'-1') between ifNull(". $fromwarehouse .",ifNull(ProductionFG.warehouse_code,'-1')) and ifNull(". $towarehouse .",ifNull(ProductionFG.warehouse_code,'-1')) ";
				
	  
				if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
					$query= $query." 	And ProductionFG.warehouse_code In (select distinct warehouse_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
				if ($groupexist > 0)
					$query= $query." 	And Items.group_code In (select distinct group_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
					
	$query= $query." 	Order by ItemCode, OrderDate, TransOrder, TransReference  ";				
	$query= $query." ) Q1 ";
	
	$SelectedTransaction = 1;
	
}


if ($TransPRT == 1 && $edatechk != 1 && $searchserial != 1 ) {

	if ($SelectedTransaction == 1 ) 
		$query = $query." Union ";
	$query = $query." Select ItemCode, ItemDescription, TransReference, TransDate, TransDescription, WarehouseCode, QtyTransferFrom, QtyTransferTo, ".
					" QtyOut, QtyIn, QtyPrev, CurrencyCode, TransPrice, TransOrder, OrderDate, TransType, TransDtId, ProjectCode, CostcenterCode , meanPrice*QtyExists as RemainVal, ".
					" abs((ifnull(QtyTransferFrom,0)-ifnull(QtyTransferTo,0))+(ifnull(QtyIn,0)-ifnull(QtyOut,0)))*meanPrice as TransVal, batchno ".
					" From ( ";
	
	$query = $query." Select productionreqfg.Item_Code As ItemCode, ";
	
	$query = $query." Items.Item_Description As ItemDescription, ProductionReq_Reference As TransReference, Date_Format(productionrequest.ProductionReq_Date,'%d/%m/%Y') As TransDate, ".
					" productionrequest.ProductionReq_Date As OrderDate, Concat('Production Request : ', if(productionrequest.ProductionReq_Remark is null,'',productionrequest.ProductionReq_Remark)) As TransDescription, ".
					" 'PRqst' As TransType, productionreqfg.ProductionReqFG_Id As TransDtId, productionreqfg.Warehouse_Code As WarehouseCode,productionrequest.Project_Code As ProjectCode,productionrequest.CostCenter_Code As CostcenterCode, ".
					" null as AdjustmentDt_HeatNo, Null As QtyTransferFrom, Null As QtyTransferTo, ".
					" 0 As TransQty, ".
					" 0 As QtyOut, ".
					" ifnull(ProductionReqFG_Quantity,0) As QtyIn, ";
					
	$query = $query." QQtyPrev.QuantityExists As QtyPrev, ";
	
	$query = $query." 10 As TransOrder, ".
					" upper($curr) As CurrencyCode, NULL as TransPrice ";
				   
	$query= $query. " , null ,null As ExpireDate ";
	$query= $query. " , 0 as meanPrice
				      , 0 QtyExists, '' as batchno ";
	$query= $query. " From productionreqfg  ".
					" Inner Join productionrequest on productionrequest.ProductionReq_Id = productionreqfg.ProductionReq_Id ".
					" Inner Join Items on productionreqfg.Item_Code = Items.Item_Code ";

	if ($projectexist >	0 && $FI_RepRestrictProject == 1)
		$query= $query.$QueryRestrictPrj." on productionrequest.project_code like concat(Q4.project_code,'%') ";
								 
	if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
		$query= $query.$QueryRestrictCst." on productionrequest.CostCenter_Code like concat(Q5.costcent_code,'%') ";		 
	
	$query= $query. " Left Join (  ".
						"               Select Stk.Item_Code, Sum(ifNull(qt_productionrqst,0)) As QuantityExists  ".
						"               From StkTransaction Stk ".
						"               Where Date_Format(Stk.Transaction_Date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(Stk.Transaction_Date,'%Y-%m-%d')) ".
						"  				And Stk.item_code between ifNull(".$fromitem.",Stk.item_code) and ifNull(". $toitem.",Stk.item_code) ".
						"               And ifNull(Stk.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Stk.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(Stk.Project_Code,'-1')) ".
						"               And ifNull(Stk.Costcent_Code,'-1') between ifNull(".$fromcostcent.",ifNull(Stk.Costcent_Code,'-1')) and ifNull(". $tocostcent.",ifNull(Stk.Costcent_Code,'-1')) ".
						"				And ifNull(Stk.warehouse_code,'-1') between ifNull(". $fromwarehouse .",ifNull(Stk.warehouse_code,'-1')) and ifNull(". $towarehouse .",ifNull(Stk.warehouse_code,'-1'))  ".
						"               And (($Module_Grn=1 and Stk.Transaction_Type <> 'PI') or ($Module_Grn=0 and 1=1)) ".
						"               Group By Stk.Item_Code ".
						"             ) As QQtyPrev on productionreqfg.Item_Code = QQtyPrev.Item_Code   ";
	
	
	$query= $query.	" Where (ProductionReq_Reference Is Not Null And ProductionReq_Reference != '')  ";
					
	$query= $query." And Date_format(productionrequest.ProductionReq_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(productionrequest.ProductionReq_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(productionrequest.ProductionReq_Date,'%Y-%m-%d'))  ";	
	
	$query= $query." And productionreqfg.Item_Code between ifNull(".$fromitem.",productionreqfg.Item_Code) and ifNull(". $toitem.",productionreqfg.Item_Code) ";
	
	$query= $query.	" 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(productionrequest.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(productionrequest.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(productionrequest.Project_Code,'-1')) ".
					" And ifNull(productionrequest.CostCenter_Code,'-1') between ifNull(".$fromcostcent.",ifNull(productionrequest.CostCenter_Code,'-1')) and ifNull(". $tocostcent.",ifNull(productionrequest.CostCenter_Code,'-1')) ".
					" And ifNull(productionreqfg.warehouse_code,'-1') between  ifNull(". $fromwarehouse .",ifNull(productionreqfg.warehouse_code,'-1')) and  ifNull(". $towarehouse .",ifNull(productionreqfg.warehouse_code,'-1'))  ";
	if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
		$query= $query." 	And productionreqfg.warehouse_code In (select distinct warehouse_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
	if ($groupexist > 0)
		$query= $query." 	And Items.group_code In (select distinct group_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
	$query = $query." union Select productionreqrm.Item_Code As ItemCode, ";
	
	$query = $query." Items.Item_Description As ItemDescription, ProductionReq_Reference As TransReference, Date_Format(productionrequest.ProductionReq_Date,'%d/%m/%Y') As TransDate, ".
					" productionrequest.ProductionReq_Date As OrderDate, Concat('Production Request : ', if(productionrequest.ProductionReq_Remark is null,'',productionrequest.ProductionReq_Remark)) As TransDescription, ".
					" 'PRqst' As TransType, productionreqrm.ProductionReqFG_Id As TransDtId, productionreqrm.Warehouse_Code As WarehouseCode,productionrequest.Project_Code As ProjectCode,productionrequest.CostCenter_Code As CostcenterCode, ".
					" null as AdjustmentDt_HeatNo, Null As QtyTransferFrom, Null As QtyTransferTo, ".
					" 0 As TransQty, ".
					" 0 As QtyOut, ".
					" ifnull(ProductionReqRM_Quantity,0) As QtyIn, ";
					
	$query = $query." QQtyPrev.QuantityExists As QtyPrev, ";
	
	$query = $query." 10 As TransOrder, ".
					" upper($curr) As CurrencyCode, NULL as TransPrice ";
				   
	$query= $query. " , null ,null As ExpireDate ";
	$query= $query. " , 0 as meanPrice
				      , 0 as QtyExists, '' as batchno ";
	$query= $query. " From productionreqrm  ".
					" Inner Join productionrequest on productionrequest.ProductionReq_Id = productionreqrm.ProductionReq_Id ".
					" Inner Join Items on productionreqrm.Item_Code = Items.Item_Code ";

	if ($projectexist >	0 && $FI_RepRestrictProject == 1)
		$query= $query.$QueryRestrictPrj." on productionrequest.project_code like concat(Q4.project_code,'%') ";
								 
	if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
		$query= $query.$QueryRestrictCst." on productionrequest.CostCenter_Code like concat(Q5.costcent_code,'%') ";		 
	
	$query= $query. " Left Join (  ".
						"               Select Stk.Item_Code, Sum(ifNull(qt_productionrqst,0)) As QuantityExists  ".
						"               From StkTransaction Stk ".
						"               Where Date_Format(Stk.Transaction_Date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(Stk.Transaction_Date,'%Y-%m-%d')) ".
						"  				And Stk.item_code between ifNull(".$fromitem.",Stk.item_code) and ifNull(". $toitem.",Stk.item_code) ".
						"               And ifNull(Stk.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Stk.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(Stk.Project_Code,'-1')) ".
						"               And ifNull(Stk.Costcent_Code,'-1') between ifNull(".$fromcostcent.",ifNull(Stk.Costcent_Code,'-1')) and ifNull(". $tocostcent.",ifNull(Stk.Costcent_Code,'-1')) ".
						"				And ifNull(Stk.warehouse_code,'-1') between ifNull(". $fromwarehouse .",ifNull(Stk.warehouse_code,'-1')) and ifNull(". $towarehouse .",ifNull(Stk.warehouse_code,'-1'))  ".
						"               And (($Module_Grn=1 and Stk.Transaction_Type <> 'PI') or ($Module_Grn=0 and 1=1)) ".
						"               Group By Stk.Item_Code ".
						"             ) As QQtyPrev on productionreqrm.Item_Code = QQtyPrev.Item_Code   ";
	
	
	$query= $query.	" Where (ProductionReq_Reference Is Not Null And ProductionReq_Reference != '')  ";
					
	$query= $query." And Date_format(productionrequest.ProductionReq_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(productionrequest.ProductionReq_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(productionrequest.ProductionReq_Date,'%Y-%m-%d'))  ";	
	
	$query= $query." And productionreqrm.Item_Code between ifNull(".$fromitem.",productionreqrm.Item_Code) and ifNull(". $toitem.",productionreqrm.Item_Code) ";
	
	$query= $query.	" 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(productionrequest.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(productionrequest.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(productionrequest.Project_Code,'-1')) ".
					" And ifNull(productionrequest.CostCenter_Code,'-1') between ifNull(".$fromcostcent.",ifNull(productionrequest.CostCenter_Code,'-1')) and ifNull(". $tocostcent.",ifNull(productionrequest.CostCenter_Code,'-1')) ".
					" And ifNull(productionreqrm.warehouse_code,'-1') between  ifNull(". $fromwarehouse .",ifNull(productionreqrm.warehouse_code,'-1')) and  ifNull(". $towarehouse .",ifNull(productionreqrm.warehouse_code,'-1'))  ";
	if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
		$query= $query." 	And productionreqrm.warehouse_code In (select distinct warehouse_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
	if ($groupexist > 0)
		$query= $query." 	And Items.group_code In (select distinct group_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  

	$query= $query." 	Order by ItemCode, OrderDate, TransOrder, TransReference  ";				
	$query= $query." ) Q1 ";
	
	$SelectedTransaction = 1;
	
}


$query= $query." ) MainQuery ";
$query= $query." Order by ItemCode, OrderDate, TransOrder, TransReference  ";
	
//echo $query; exit;

$mastergroup  = '';
$GroupDesc   = '';
$SumQtyExists = 0;
$QtyIn	= 0;
$QtyOut = 0;
$QtyTrsFrom	= 0;
$QtyTrsTo = 0;

$SumQtyIn	= 0;
$SumQtyOut = 0;
$SumQtyTrsFrom	= 0;
$SumQtyTrsTo = 0;

$RowNum    = 13;
$ColumnNum = 0;

//LogHtml($query);
$result=mysql_query($query,$connection) or die(mysql_error());
while($rows=mysql_fetch_row($result)){
		
		
	if ($mastergroup == '' || $mastergroup != $rows[0])  {
		
		if ($searchserial == 1)	$GroupDesc = 'Serial:  ';
		else $GroupDesc = 'Item:  ';
			
		if ($mastergroup != '') {
			$RowNum += 1;
			$ColumnNum = 4;
			if ($Module_BatchNo == 1 && $showbatch == 1)	$ColumnNum = 5;	
			$worksheet->write($RowNum, $ColumnNum, number_format($SumQtyTrsFrom, $StmtOfItemRMaskQty), $grouplevel1totalFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum, $ColumnNum, number_format($SumQtyTrsTo, $StmtOfItemRMaskQty), $grouplevel1totalFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum, $ColumnNum, number_format($SumQtyIn, $StmtOfItemRMaskQty), $grouplevel1totalFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum, $ColumnNum, number_format($SumQtyOut, $StmtOfItemRMaskQty), $grouplevel1totalFormat);
			$ColumnNum += 1;
		
			$RowNum += 2;
			$ColumnNum = 0;
		}
		
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
		$worksheet->write($RowNum, $ColumnNum, "$GroupDesc ". $rows[0], $grouplevel1Format);
		$ColumnNum += 2;
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 4);
		$worksheet->write($RowNum, $ColumnNum, $rows[1], $grouplevel1Format);
		$ColumnNum += 5;
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 2);
		$worksheet->write($RowNum, $ColumnNum, "Previous:  ". number_format($rows[10], $StmtOfItemRMaskQty), $grouplevel1Format);
		
	
		$mastergroup   = $rows[0];
		$SumQtyExists  = $rows[10];
		
		$SumQtyIn	= 0;
		$SumQtyOut = 0;
		$SumQtyTrsFrom	= 0;
		$SumQtyTrsTo = 0;
		
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 2;
		$ColumnNum = 0;
		
	}
		
		
	$QtyIn		= $rows[9];
	$QtyOut 	= $rows[8];
	$QtyTrsFrom	= $rows[6];
	$QtyTrsTo 	= $rows[7];
	
	if (empty($QtyIn))  	 $QtyIn = 0;
	if (empty($QtyOut)) 	 $QtyOut = 0;
	if (empty($QtyTrsFrom))  $QtyTrsFrom = 0;
	if (empty($QtyTrsTo)) 	 $QtyTrsTo = 0;
	
	
	$SumQtyExists   = $SumQtyExists  +  $QtyIn  -  $QtyOut + $QtyTrsTo - $QtyTrsFrom;
	
	$SumQtyIn	+= $QtyIn;
	$SumQtyOut  += $QtyOut;
	$SumQtyTrsFrom	+= $QtyTrsFrom;
	$SumQtyTrsTo    += $QtyTrsTo;
	
	
	$worksheet->write($RowNum, $ColumnNum, $rows[3], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, trim($rows[2]), $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[4], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[5], $regularleftFormat);
	$ColumnNum += 1;
	
	if ($Module_BatchNo == 1 && $showbatch == 1){
		$worksheet->write($RowNum, $ColumnNum, $rows[21], $regularleftFormat);
		$ColumnNum += 1;
	}
	$worksheet->write($RowNum, $ColumnNum, number_format($rows[6], $StmtOfItemRMaskQty), $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, number_format($rows[7], $StmtOfItemRMaskQty), $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, number_format($rows[9], $StmtOfItemRMaskQty), $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, number_format($rows[8], $StmtOfItemRMaskQty), $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, number_format($SumQtyExists, $StmtOfItemRMaskQty), $regularrightFormat);
	$ColumnNum += 1;
	
	if ($prtcost == 1) {
		$worksheet->write($RowNum, $ColumnNum, $rows[11], $regularcenterFormat);
		$ColumnNum += 1;
		if($rows[15]=='TR'){
			$sub_query = "select transferdt_costbase1, transferdt_costbase2, transferdt_chargebase1, transferdt_chargebase2 from transferDt where transferDt_Id = '$rows[16]'";
			//echo $sub_query;exit;
			$result_sub_query = mysql_query($sub_query,$connection);
			$rows_sub_query = mysql_fetch_array($result_sub_query);
		
			if($rows[11]==$Base1)
				$totalTrans = $rows_sub_query[0] + $rows_sub_query[2];
			if($rows[11]==$Base2)
				$totalTrans = $rows_sub_query[1] + $rows_sub_query[3];
			//echo $rows_sub_query[1] + $rows_sub_query[3];exit;
			$worksheet->writeNumber($RowNum, $ColumnNum, $totalTrans, $regularrightFormatNum);
		}
		else
			$worksheet->writeNumber($RowNum, $ColumnNum, $rows[12], $regularrightFormatNum);
		$ColumnNum += 1;
	} 
	
	if ($showserials == 1 && $searchserial == 0) {
	
		$sub_query = " Select Distinct SerialNo_Code ".
					 " From Purchasesserialno ".
					 " Where Item_Code = '".$rows[0]."' ".
					 " And PurchaseDt_ID = '".$rows[16]."' ".
					 " And Journal_Type = '".$rows[15]."' ";
					 
		
		$sub_result=mysql_query($sub_query,$connection) or die(mysql_error());
		$subnum=mysql_num_rows($sub_result);
				
		if ($subnum != 0)
		{
			$RowNum += 1;
			$ColumnNum = 0;
					
			$worksheet->write($RowNum, $ColumnNum, "Serial No :", $regularrightFormat);
			$ColumnNum += 1;
					
			$SerialCode='';
			$i = 0;
			while($sub_row=mysql_fetch_array($sub_result)){
				$i += 1;
				$SerialCode = $sub_row[0];
						
				$worksheet->writestring($RowNum, $ColumnNum, $SerialCode, $regularleftFormat);
				$ColumnNum += 1;
				
				if($i%20==0) {
					$RowNum += 1;
					$ColumnNum = 1;
				}
			}
			
		}
		
	
	}
	
	
	if($vShowProject == 1 && $Module_Project == 1){
				$worksheet->writestring($RowNum, $ColumnNum, $rows[17], $regularleftFormat);
				$ColumnNum += 1;
				
	}
	if($vShowCostcenter == 1 && $Module_Costcenter == 1){
			$worksheet->writestring($RowNum, $ColumnNum, $rows[18], $regularleftFormat);
			$ColumnNum += 1;
		}
		
	//Increment the Rows Counter By 1 and Reset the Columns Counter
	$RowNum += 1;
	$ColumnNum = 0;
	

}

if ($mastergroup != '') {
	$RowNum += 1;
	$ColumnNum = 4;
	if ($Module_BatchNo == 1 && $showbatch == 1)	$ColumnNum = 5;	
	$worksheet->write($RowNum, $ColumnNum, number_format($SumQtyTrsFrom, $StmtOfItemRMaskQty), $grouplevel1totalFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, number_format($SumQtyTrsTo, $StmtOfItemRMaskQty), $grouplevel1totalFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, number_format($SumQtyIn, $StmtOfItemRMaskQty), $grouplevel1totalFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, number_format($SumQtyOut, $StmtOfItemRMaskQty), $grouplevel1totalFormat);
	$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;
}

//Return Date in YYYY-MM-DD HH:MM:SS format. Supplied Format: DD/MM/YYYY
function insertMySQLDate($data){
	return substr($data, 6, 4) ."-". substr($data, 3, 2) ."-".substr($data, 0, 2)." ".strftime("%H").":".strftime("%M").":".strftime("%S");;
}
//Return Today's Date in DD/MM/YYYY format
function displayToday(){
	return strftime("%d")."/".strftime("%m")."/".strftime("%Y");
}


$workbook->close();

?>