<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<?php

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


print <<< HERE
<HTML>
<HEAD>
<TITLE> Item Aging Report </TITLE>
<META NAME="Generator" CONTENT="EditPlus">
<META NAME="Author" CONTENT="">
<META NAME="Keywords" CONTENT="">
<META NAME="Description" CONTENT="">

<style type="text/css">
table.params td { font-size: 12px; 
                  font-family:Times-New-Roman;
                 }

table.master th { font-size: 14px; 
                  font-weight: bold;
                  font-family:Times-New-Roman;
                }				 
table.master td { font-size: 13px; 
                  font-family:Times-New-Roman;
                }
						
table.master td.grouplevel1 { font-size: 14px; 
							  font-weight: bold;
                              font-family:Times-New-Roman;
                            }				 									  

h4 { border-top-style: solid; 
	 border-top-color: black; 
	 border-top-width: 2px;
   }
h5 { border-top-style: solid; 
	 border-top-color: black; 
	 border-top-width: 1px;
   }				 
				 

</style>




</HEAD>


HERE;



include("config.inc.php");

//Set Time Execution For the Report
ini_set('max_execution_time', $config['ReportTimeExecution']);

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


$querylog="SELECT print as printaccess from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' 	and Upper(Login_Schema) = Upper('".$HTTP_SESSION_VARS['dbName']."')	";
$resultlog=mysql_query($querylog,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}


if ($printaccess==0 or $printaccess=='')
echo'<BODY oncontextmenu="return false;">';
else
echo'<BODY>';


$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 ".$HTTP_SESSION_VARS['dbLabel'].".login where user_account='".$HTTP_SESSION_VARS['useraccount']."' and Upper(Login_Schema) = Upper('".$HTTP_SESSION_VARS['dbName']."') ";
$resultlog=mysql_query($querylog) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}

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

$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 ".$HTTP_SESSION_VARS['dbLabel'].".loginstk on project.project_code like concat(ifnull(loginstk.project_code,project.project_code),'%') ".
			  " where user_account='".$HTTP_SESSION_VARS['useraccount']."' ".
			  " and (loginstk.project_code is not null and loginstk.project_code != '') ".
			  " order by project.project_code";
$projectresult = mysql_query($queryproject,$connection) or die(mysql_error());
$projectexist  = mysql_num_rows($projectresult);

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

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


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

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

print <<< HERE
<table width="100%" border="0" align="center">
<tr>
  <td width='30%'><b>$CompanyName</b></td>
  <td width='50%' align='center'><font size=4><b>Item Aging</b></font></td>
  <td width='10%'><font size=1><b> Printed Date: </b> <br><b>Time: </b>
  <td width='10%'><font size=1>
HERE;

echo $printdate[2]."/".$printdate[1]."/".$printdate[0];
//echo DATE("d"."/"."m"."/"."Y");
echo "<br>".$dt[1]." ".$dt[2];


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


$SelectedOptions = "Filter by: <br>";
if ($fromdate != '')      $SelectedOptions .= "-> Date From: ". $fromdate . "  To: ". $todate ."<br>";	
if ($displaycur != '')    $SelectedOptions .= "-> Currency: ". $displaycur ."<br>";	
if ($fromitem != '')      $SelectedOptions .= "-> Item From: ". $fromitem . "  To: ". $toitem ."<br>";
if ($fromgroup != '')     $SelectedOptions .= "-> Group From: ". $fromgroup . "  To: ". $togroup ."<br>";
if ($fromsubgroup != '')  $SelectedOptions .= "-> SubGroup From: ". $fromsubgroup . "  To: ". $tosubgroup ."<br>";
if ($fromcategory != '')  $SelectedOptions .= "-> SubGroup From: ". $fromcategory . "  To: ". $tocategory ."<br>";
if ($fromproject != '')   $SelectedOptions .= "-> Project From: ". $fromproject . "  To: ". $toproject . "<br>";
if ($fromcostcent != '')  $SelectedOptions .= "-> Costcent From: ". $fromcostcent . "  To: ". $tocostcent . "<br>";
if ($fromwarehouse != '') $SelectedOptions .= "-> Warehouse From: ". $fromwarehouse . "<br>";
if ($fromtranswh != '')   $SelectedOptions .= "-> Transfer Warehouse From: ". $fromtranswh . "  To: ". $totranswh ."<br>";

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


$fromitem      = GetSQLValueString($fromitem, 'text');
$toitem        = GetSQLValueString($toitem, 'text');
$fromproject   = GetSQLValueString($fromproject, 'text');
$toproject     = GetSQLValueString($toproject, 'text');
$fromcostcent  = GetSQLValueString($fromcostcent, 'text');
$tocostcent    = GetSQLValueString($tocostcent, 'text');
$fromgroup     = GetSQLValueString($fromgroup, 'text');
$togroup       = GetSQLValueString($togroup, 'text');
$fromsubgroup  = GetSQLValueString($fromsubgroup, 'text');
$tosubgroup    = GetSQLValueString($tosubgroup, 'text');
$fromwarehouse = GetSQLValueString($fromwarehouse, 'text');
$fromcategory  = GetSQLValueString($fromcategory, 'text');
$tocategory    = GetSQLValueString($tocategory, 'text');   
$fromtranswh   = GetSQLValueString($fromtranswh, 'text');
$totranswh     = GetSQLValueString($totranswh, 'text');   
$fromdate	   = GetSQLValueString($fromdate, 'date');
$todate        = GetSQLValueString($todate, 'date');   
$agingdate     = GetSQLValueString($agingdate, 'date');   
   

	
if (empty($prtcostin))    $prtcostin = 0;
if (empty($prtcostout))   $prtcostout = 0;
if (empty($prtsellprice)) $prtsellprice = 0;

				
print <<<HERE
</font></td>
</tr>
<tr height='5'><td colspan=4><h5></td></tr>
</table>


<table class="params" width="100%" border="0" align="center" >
<tr>
  <td width="100%" valign='top'><b>$SelectedOptions</b></td>
</tr>
<tr height='5'><td><h5></td></tr>
</table>
HERE;

echo "<table  border='0' width='100%' align='center' class='master' style='table-layout:fixed; border-collapse:collapse;' cellpadding='0' cellspacing='0'>
	  <col width=150>
	  <col width=290>
	  <col width=90>
	  <col width=70>
	  <col width=400>";

echo "<tr><td colspan='5' align='left' valign='top'><table  border='0' width='100%' align='center' class='master' style='table-layout:fixed' cellpadding='0' cellspacing='0'>
      <col width=50>
      <col width=60>
      <col width=70>
	  <col width=60>";
	  
if ($prtcostin == 1) echo" <col width=50>  <col width=100>";

echo "<col width=50>
      <col width=60>
      <col width=70>
	  <col width=60>";
	  
if ($prtcostout == 1)   echo"<col width=50>  <col width=100>";
if ($prtsellprice == 1) echo"<col width=100>";
	
echo" <col width=50>
	  <col width=70>
	<tr>";


if ($prtcostin == 1) echo"<th colspan='6'>IN</th>";
else echo"<th colspan='4'>IN</th>";

if ($prtcostout == 1 && $prtsellprice == 1)
	echo"<th colspan='7'>OUT</th>";
else  if ($prtcostout == 0 && $prtsellprice == 0)
	echo"<th colspan='4'>OUT</th>";
else  if ($prtcostout == 1 && $prtsellprice == 0)
	echo"<th colspan='6'>OUT</th>";
else  if ($prtcostout == 0 && $prtsellprice == 1)
	echo"<th colspan='5'>OUT</th>";	

	
echo"	<th colspan='2'>&nbsp;</th>
	</tr>";
	
echo"<tr>";
echo"	<th >Type</th>
		<th >Ref.</th>
		<th >Date</th>
		<th >Quantity</th>";
			
if ($prtcostin == 1) echo"<th >Cur</th> <th >Cost Price</th>";
	
echo"	<th >Type</th>
		<th >Ref.</th>
		<th >Date</th>
		<th >Quantity</th>";
			
if ($prtcostout == 1)   echo"<th >Cur</th> <th >Cost Price</th>";
if ($prtsellprice == 1) echo"<th >Sell. Price</th>";	
		 
echo"	<th >Days</th>
		<th >Balance</th>";
			
echo"</tr>";
echo"</table></td></tr>";
echo" <tr height='5'><td colspan=5><h5></td></tr>";
	
 

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

$result = mysql_query("start transaction", $connection);
$resultvars = mysql_query("set @CumQty:=0, @VarGrouping:='x' ", $connection);

$query = $query." Select ItemCode, ItemDescription, In_TransType, In_TransReference, In_TransDate, In_TransQty, In_CurrencyCode, In_TransPrice, ".
				" Out_TransType, Out_TransReference, Out_TransDate, Out_TransQty, Out_CurrencyCode, Out_TransPrice, Out_SellPrice, TransOrder, ".
				" OrderDate, Days, QtyPrev, QtyExists,  ".
				" if((GroupingOptions != ItemCode), (@CumQty := ifNull(QtyPrev,0)), @CumQty := @CumQty), ".
				" Round((@CumQty := (@CumQty + ifNull(In_TransQty,0) - ifNull(Out_TransQty,0))),$StmtOfItemRMaskQty) As QtyBalance ".
				" From ( ";				
				
$query = $query." Select ItemCode, ItemDescription, In_TransType, In_TransReference, In_TransDate, In_TransQty, In_CurrencyCode, In_TransPrice, ".
				" Out_TransType, Out_TransReference, Out_TransDate, Out_TransQty, Out_CurrencyCode, Out_TransPrice, Out_SellPrice, TransOrder, ".
				" OrderDate, Days, QtyPrev, QtyExists,  ".
				" @VarGrouping As GroupingOptions, @VarGrouping := ItemCode  ".
				" From ( ";	
				
$query = $query." Select ItemCode, ItemDescription, In_TransType, In_TransReference, In_TransDate, Round(In_TransQty, $StmtOfItemRMaskQty) As In_TransQty, In_CurrencyCode, Round(In_TransPrice, $DecimalBase) As In_TransPrice, ".
				" Out_TransType, Out_TransReference, Out_TransDate, Round(Out_TransQty, $StmtOfItemRMaskQty) As Out_TransQty, Out_CurrencyCode, Round(Out_TransPrice, $DecimalBase) As Out_TransPrice, Round(Out_SellPrice, $DecimalBase) As Out_SellPrice, TransOrder, ".
				" OrderDate, Days, ifNull(QQtyPrev.PrevQuantity,0) As QtyPrev, ifNull(QQtyExists.QuantityExists,0) As QtyExists  ".
				" From ( ";				

if (($TransPI == 1 || $TransPR == 1) && $Module_Grn == 0) {
	
	$query = $query." Select TransID, ItemCode, ItemDescription, In_TransType, In_TransReference, In_TransDate, In_TransQty, In_CurrencyCode, In_TransPrice, ".
					" Out_TransType, Out_TransReference, Out_TransDate, Out_TransQty, Out_CurrencyCode, Out_TransPrice, Out_SellPrice, TransOrder, OrderDate, Days ".
					" From ( ";
					
	
	$query = $query." Select PurchasesDt.PurchaseDt_Id As TransID, PurchasesDt.Item_Code As ItemCode, Items.Item_Description As ItemDescription, Purchases.Journal_Type As In_TransType, Purchases.Purchase_Reference As In_TransReference, ".
					" Date_Format(Purchases.Purchase_Date,'%d/%m/%Y') As In_TransDate, Date_Format(Purchases.Purchase_Date,'%Y%m%d') As OrderDate, PurchasesDt.Warehouse_Code As WarehouseCode, ".
					" if(Purchases.Journal_Type='PI', ifNull(PurchasesDt.PurchaseDt_Quantity,0), (-1)*ifNull(PurchasesDt.PurchaseDt_Quantity,0)) As In_TransQty, ".
					" if(Purchases.Journal_Type='PI', 1, 2) As TransOrder, Purchases.Currency_Code As In_CurrencyCode, ".
					" 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 In_TransPrice,  ".
					" Null As Out_TransType, Null As Out_TransReference, Null As Out_TransDate, Null As Out_TransQty,  Null As Out_CurrencyCode, Null As Out_TransPrice, Null As Out_SellPrice, ".
					" DATEDIFF(date_format(".$agingdate.",'%Y-%m-%d'), date_format(Purchases.Purchase_Date,'%Y-%m-%d')) Days ".
					" From PurchasesDt  ".
					" Inner Join Purchases on PurchasesDt.Purchase_ID = Purchases.Purchase_ID ".
					" Inner Join Items on PurchasesDt.Item_Code = Items.Item_Code ";

	if ($projectexist >	0 && $FI_RepRestrictProject == 1)
		$query= $query.$QueryRestrictPrj." on Purchases.project_code like concat(Q4.project_code,'%') ";
								 
	if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
		$query= $query.$QueryRestrictCst." on Purchases.Costcent_Code like concat(Q5.costcent_code,'%') ";		 
	
	$query= $query.	" Where (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'))  ".
					" And PurchasesDt.Item_Code between ifNull(".$fromitem.",PurchasesDt.Item_Code) and ifNull(". $toitem.",PurchasesDt.Item_Code) ".
					" And ifNull(Items.Group_Code,'-1') between ifNull(". $fromgroup .",ifNull(Items.Group_Code,'-1')) and ifNull(". $togroup.",ifNull(Items.Group_Code,'-1'))  ".
					" And ifNull(Items.SubGroup_Code,'-1') between ifNull(". $fromsubgroup .",ifNull(Items.SubGroup_Code,'-1')) and ifNull(". $tosubgroup.",ifNull(Items.SubGroup_Code,'-1'))  ".
					" And ifNull(Items.Category_Code,'-1') between ifNull(". $fromcategory .",ifNull(Items.Category_Code,'-1')) and ifNull(". $tocategory.",ifNull(Items.Category_Code,'-1'))  ".
					" And ifNull(Purchases.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') Like Concat(ifNull(". $fromwarehouse .",ifNull(PurchasesDt.warehouse_code,'-1')),'%')  ";
				
				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') ";
				
				if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
					$query= $query." 	And PurchasesDt.warehouse_code In (select distinct warehouse_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  
				if ($groupexist > 0)
					$query= $query." 	And Items.group_code In (select distinct group_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  

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

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

	if ($SelectedTransaction == 1 ) 
		$query = $query." Union ";
	
	$query = $query." Select TransID, ItemCode, ItemDescription, In_TransType, In_TransReference, In_TransDate, In_TransQty, In_CurrencyCode, In_TransPrice, ".
					" Out_TransType, Out_TransReference, Out_TransDate, Out_TransQty, Out_CurrencyCode, Out_TransPrice, Out_SellPrice, TransOrder, OrderDate, Days ".
					" From ( ";
					
	$query = $query." Select GrnPurchasesDt.PurchaseDt_Id As TransID, GrnPurchasesDt.Item_Code As ItemCode, Items.Item_Description As ItemDescription, GrnPurchases.Journal_Type As In_TransType, GrnPurchases.Purchase_Reference As In_TransReference, ".
					" Date_Format(GrnPurchases.Purchase_Date,'%d/%m/%Y') As In_TransDate, Date_Format(GrnPurchases.Purchase_Date,'%Y%m%d') As OrderDate, GrnPurchasesDt.Warehouse_Code As WarehouseCode, ".
					" if(GrnPurchases.Journal_Type='PI', ifNull(if(PurchasesDt.PurchaseDt_ID Is Not Null, PurchasesDt.PurchaseDt_Quantity, GrnPurchasesDt.PurchaseDt_Quantity),0), (-1)*ifNull(if(PurchasesDt.PurchaseDt_ID Is Not Null, PurchasesDt.PurchaseDt_Quantity, GrnPurchasesDt.PurchaseDt_Quantity),0)) As In_TransQty, ".
					" if(GrnPurchases.Journal_Type='PI', 1, 2) As TransOrder, PurchasesDt.Currency_Code As In_CurrencyCode, ".
					" 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 In_TransPrice,  ".
					" Null As Out_TransType, Null As Out_TransReference, Null As Out_TransDate, Null As Out_TransQty, Null As Out_CurrencyCode, Null As Out_TransPrice, Null As Out_SellPrice, ".
					" DATEDIFF(date_format(".$agingdate.",'%Y-%m-%d'), date_format(GrnPurchases.Purchase_Date,'%Y-%m-%d')) Days ".
				    " From GrnPurchasesDt  ".
					" Inner Join GrnPurchases on GrnPurchasesDt.Purchase_ID = GrnPurchases.Purchase_ID ".
					" Left Join PurchasesDt on PurchasesDt.RelatedDt_ID = GrnPurchasesDt.PurchaseDt_ID ".
					" Inner Join Items on GrnPurchasesDt.Item_Code = Items.Item_Code ";

	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,'%') ";		 
		

	$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'))  ".
					" And GrnPurchasesDt.Item_Code between ifNull(".$fromitem.",GrnPurchasesDt.Item_Code) and ifNull(". $toitem.",GrnPurchasesDt.Item_Code) ".
					" And ifNull(Items.Group_Code,'-1') between ifNull(". $fromgroup .",ifNull(Items.Group_Code,'-1')) and ifNull(". $togroup.",ifNull(Items.Group_Code,'-1'))  ".
					" And ifNull(Items.SubGroup_Code,'-1') between ifNull(". $fromsubgroup .",ifNull(Items.SubGroup_Code,'-1')) and ifNull(". $tosubgroup.",ifNull(Items.SubGroup_Code,'-1'))  ".
					" And ifNull(Items.Category_Code,'-1') between ifNull(". $fromcategory .",ifNull(Items.Category_Code,'-1')) and ifNull(". $tocategory.",ifNull(Items.Category_Code,'-1'))  ".
					" And ifNull(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') 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 ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  
				if ($groupexist > 0)
					$query= $query." 	And Items.group_code In (select distinct group_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  

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

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

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

	$query = $query." Select TransID, ItemCode, ItemDescription, In_TransType, In_TransReference, In_TransDate, In_TransQty, In_CurrencyCode, In_TransPrice, ".
					" Out_TransType, Out_TransReference, Out_TransDate, Out_TransQty, Out_CurrencyCode, Out_TransPrice, Out_SellPrice, TransOrder, OrderDate, Days ".
					" From ( ";	
					
	
	$query = $query." Select DeliveryDt.DeliveryDt_Id As TransID, DeliveryDt.Item_Code As ItemCode, Items.Item_Description As ItemDescription, Deliveries.Journal_Type As Out_TransType, Deliveries.Delivery_Reference As Out_TransReference, ".
					" Date_Format(Deliveries.Delivery_Date,'%d/%m/%Y') As Out_TransDate, Date_Format(Deliveries.Delivery_Date,'%Y%m%d') As OrderDate,  DeliveryDt.Warehouse_Code As WarehouseCode, ".
					" if(Deliveries.Journal_Type='DI', ifNull(DeliveryDt.DeliveryDt_Quantity,0), (-1)*ifNull(DeliveryDt.DeliveryDt_Quantity,0)) As Out_TransQty, ".
					" if(Deliveries.Journal_Type='DI', 5, 6) As TransOrder, ";
					
	if ($Module_Delivery == 0) {
		$query = $query." Deliveries.Currency_Code As Out_CurrencyCode, ".
						" 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))) As Out_SellPrice, ";
	} else {
		$query = $query." SalesDt.Currency_Code As Out_CurrencyCode, ".
						" if($SalesTTC=1, if(upper($curr)='".$Base1."',SalesDt.SaleDt_PriceTTCBase1,if(upper($curr)='".$Base2."',SalesDt.SaleDt_PriceTTCBase2, SalesDt.SaleDt_PriceTTC)),  ".
						" if(upper($curr)='".$Base1."',SalesDt.SaleDt_PriceBase1,if(upper($curr)='".$Base2."',SalesDt.SaleDt_PriceBase2, SalesDt.SaleDt_Price))) As Out_SellPrice, ";
	}	
					
					
	$query = $query." if(upper($curr)='".$Base1."',DeliveryDt.DeliveryDt_CostBase1,if(upper($curr)='".$Base2."',DeliveryDt.DeliveryDt_CostBase2, DeliveryDt.DeliveryDt_CostBase1)) As Out_TransPrice, ".
					" Null As In_TransType, Null As In_TransReference, Null As In_TransDate, Null As In_TransQty, Null As In_CurrencyCode, Null As In_TransPrice, ".
					" DATEDIFF(date_format(".$agingdate.",'%Y-%m-%d'), date_format(Deliveries.Delivery_Date,'%Y-%m-%d')) Days ".
					" From DeliveryDt  ".
					" Inner Join Deliveries on DeliveryDt.Delivery_ID = Deliveries.Delivery_ID ".
					" Inner Join Items on DeliveryDt.Item_Code = Items.Item_Code ";
					
	if ($Module_Delivery == 1) 
		$query = $query." Left Join SalesDt on SalesDt.RelatedDt_ID = DeliveryDt.DeliveryDt_ID ";

	if ($projectexist >	0 && $FI_RepRestrictProject == 1)
		$query= $query.$QueryRestrictPrj." on Deliveries.project_code like concat(Q4.project_code,'%') ";
								 
	if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
		$query= $query.$QueryRestrictCst." on Deliveries.Costcent_Code like concat(Q5.costcent_code,'%') ";		 
		
	
	$query= $query.	" Where (Deliveries.Delivery_Reference Is Not Null And Deliveries.Delivery_Reference != '')  ".
					" And Date_format(Deliveries.Delivery_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(Deliveries.Delivery_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Deliveries.Delivery_Date,'%Y-%m-%d'))  ".
					" And DeliveryDt.Item_Code between ifNull(".$fromitem.",DeliveryDt.Item_Code) and ifNull(". $toitem.",DeliveryDt.Item_Code) ".
					" And ifNull(Items.Group_Code,'-1') between ifNull(". $fromgroup .",ifNull(Items.Group_Code,'-1')) and ifNull(". $togroup.",ifNull(Items.Group_Code,'-1'))  ".
					" And ifNull(Items.SubGroup_Code,'-1') between ifNull(". $fromsubgroup .",ifNull(Items.SubGroup_Code,'-1')) and ifNull(". $tosubgroup.",ifNull(Items.SubGroup_Code,'-1'))  ".
					" And ifNull(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') 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 ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  
				if ($groupexist > 0)
					$query= $query." 	And Items.group_code In (select distinct group_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  

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


if ($TransTR == 1 || $TransTRT == 1) {

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

	$query = $query." Select TransID, ItemCode, ItemDescription, In_TransType, In_TransReference, In_TransDate, In_TransQty, In_CurrencyCode, In_TransPrice, ".
					" Out_TransType, Out_TransReference, Out_TransDate, Out_TransQty, Out_CurrencyCode, Out_TransPrice, Out_TransPrice As Out_SellPrice, TransOrder, OrderDate, Days ".
					" From ( ";	
					
	$query = $query." Select TransferDt.TransferDt_Id As TransID, TransferDt.Item_Code As ItemCode, Items.Item_Description As ItemDescription, if($TransTR = 1, 'TR', Null) As In_TransType, if($TransTR = 1, Transfer.Transfer_Reference, Null) As In_TransReference, ".
					" if($TransTR = 1, Date_Format(Transfer.Transfer_Date,'%d/%m/%Y'), Null) As In_TransDate, ".
					" Date_Format(Transfer.Transfer_Date,'%Y%m%d') As OrderDate, Concat('F: ',Transfer_WarehouseFrom,' T: ',Transfer_WarehouseTo) As WarehouseCode, ".
					" if($TransTR = 1, if ($fromwarehouse Is Null or Transfer_WarehouseTo = $fromwarehouse  , ifNull(TransferDt.TransferDt_Quantity,0), 0), Null) As In_TransQty, ".
					" 3 As TransOrder, Null As In_CurrencyCode, Null As In_TransPrice,  ".
					" if($TransTRT = 1, 'TR', Null) As Out_TransType, if($TransTRT = 1, Transfer.Transfer_Reference, Null) As Out_TransReference, if($TransTRT = 1, Date_Format(Transfer.Transfer_Date,'%d/%m/%Y'), Null) As Out_TransDate, ".
					" if($TransTRT = 1, if ($fromwarehouse Is Null or Transfer_WarehouseFrom = $fromwarehouse, ifNull(TransferDt.TransferDt_Quantity,0), 0), Null) As Out_TransQty, ".
					" Null As Out_CurrencyCode, Null As Out_TransPrice, Null As Out_SellPrice, DATEDIFF(date_format(".$agingdate.",'%Y-%m-%d'), date_format(Transfer.Transfer_Date,'%Y-%m-%d')) Days ".
					" 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,'%') ";		 
		
	
	$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'))  ".
					" And TransferDt.Item_Code between ifNull(".$fromitem.",TransferDt.Item_Code) and ifNull(". $toitem.",TransferDt.Item_Code) ".
					" And ifNull(Items.Group_Code,'-1') between ifNull(". $fromgroup .",ifNull(Items.Group_Code,'-1')) and ifNull(". $togroup.",ifNull(Items.Group_Code,'-1'))  ".
					" And ifNull(Items.SubGroup_Code,'-1') between ifNull(". $fromsubgroup .",ifNull(Items.SubGroup_Code,'-1')) and ifNull(". $tosubgroup.",ifNull(Items.SubGroup_Code,'-1'))  ".
					" And ifNull(Items.Category_Code,'-1') between ifNull(". $fromcategory .",ifNull(Items.Category_Code,'-1')) and ifNull(". $tocategory.",ifNull(Items.Category_Code,'-1'))  ".
					" And ifNull(Transfer.Project_Code1,'-1') between ifNull(".$fromproject.",ifNull(Transfer.Project_Code1,'-1')) and ifNull(". $toproject.",ifNull(Transfer.Project_Code1,'-1')) ".
					" And ifNull(Transfer.Costcenter_Code,'-1') between ifNull(".$fromcostcent.",ifNull(Transfer.Costcenter_Code,'-1')) and ifNull(". $tocostcent.",ifNull(Transfer.Costcenter_Code,'-1')) ".
					" And ifNull(Transfer.Transfer_WarehouseFrom,'-1') = ifNull(". $fromtranswh .",ifNull(Transfer.Transfer_WarehouseFrom,'-1'))  ".
					" And ifNull(Transfer.Transfer_WarehouseTo,'-1') = ifNull(". $totranswh .",ifNull(Transfer.Transfer_WarehouseTo,'-1'))  ".
					" And (( ifNull(Transfer.Transfer_WarehouseFrom,'-1') = ifNull(". $fromwarehouse .",ifNull(Transfer.Transfer_WarehouseFrom,'-1'))) or   ".
					" 	   ( ifNull(Transfer.Transfer_WarehouseTo,'-1') = ifNull(". $fromwarehouse .",ifNull(Transfer.Transfer_WarehouseTo,'-1'))) )   ";
				
				
				if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
					$query= $query." 	And Transfer.Transfer_WarehouseFrom In (select distinct warehouse_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  
				if ($groupexist > 0)
					$query= $query." 	And Items.group_code In (select distinct group_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  

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

if ($TransAI == 1 || $TransAR == 1) {

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

	$query = $query." Select TransID, ItemCode, ItemDescription, In_TransType, In_TransReference, In_TransDate, In_TransQty, In_CurrencyCode, In_TransPrice, ".
					" Out_TransType, Out_TransReference, Out_TransDate, Out_TransQty, Out_CurrencyCode, Out_TransPrice, Out_TransPrice As Out_SellPrice, TransOrder, OrderDate, Days ".
					" From ( ";		
					
					
	$query = $query." Select AdjustmentDt.AdjustmentDt_Id As TransID, AdjustmentDt.Item_Code As ItemCode, Items.Item_Description As ItemDescription, if(AdjustmentDt.Journal_Type='AI', AdjustmentDt.Journal_Type, Null) As In_TransType, ".
					" if(AdjustmentDt.Journal_Type='AI', Adjustment.Adjustment_Reference, Null) As In_TransReference, ".
					" if(AdjustmentDt.Journal_Type='AI', Date_Format(Adjustment.Adjustment_Date,'%d/%m/%Y'), null) As In_TransDate, ".
					" Date_Format(Adjustment.Adjustment_Date,'%Y%m%d') As OrderDate, AdjustmentDt.Warehouse_Code As WarehouseCode, ".
					" if(AdjustmentDt.Journal_Type='AI', ifNull(AdjustmentDt.AdjustmentDt_Quantity,0), Null) As In_TransQty, ".
					" if(AdjustmentDt.Journal_Type='AI', 8, 9) As TransOrder, if(AdjustmentDt.Journal_Type='AI', Adjustment.Currency_Code, Null) As In_CurrencyCode, ".
					" if(AdjustmentDt.Journal_Type='AI', if(upper($curr)='".$Base1."',AdjustmentDt.AdjustmentDt_PriceBase1,if(upper($curr)='".$Base2."',AdjustmentDt.AdjustmentDt_PriceBase2, AdjustmentDt.AdjustmentDt_Price)), Null) As In_TransPrice, ".
					" if(AdjustmentDt.Journal_Type='AR', AdjustmentDt.Journal_Type, Null) As Out_TransType, ".
					" if(AdjustmentDt.Journal_Type='AR', Adjustment.Adjustment_Reference, Null) As Out_TransReference, ".
					" if(AdjustmentDt.Journal_Type='AR', Date_Format(Adjustment.Adjustment_Date,'%d/%m/%Y'), null) As Out_TransDate, ".
					" if(AdjustmentDt.Journal_Type='AR', Abs(ifNull(AdjustmentDt.AdjustmentDt_Quantity,0)), Null) As Out_TransQty, ".
					" if(AdjustmentDt.Journal_Type='AR', Adjustment.Currency_Code, Null) As Out_CurrencyCode, ".
					" if(AdjustmentDt.Journal_Type='AR', if(upper($curr)='".$Base1."',AdjustmentDt.AdjustmentDt_PriceBase1,if(upper($curr)='".$Base2."',AdjustmentDt.AdjustmentDt_PriceBase2, AdjustmentDt.AdjustmentDt_Price)), Null) As Out_TransPrice, ".
					" Null As Out_SellPrice, DATEDIFF(date_format(".$agingdate.",'%Y-%m-%d'), date_format(Adjustment.Adjustment_Date,'%Y-%m-%d')) Days ".
					" 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,'%') ";		 
		
	
	$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'))  ".
					" And AdjustmentDt.Item_Code between ifNull(".$fromitem.",AdjustmentDt.Item_Code) and ifNull(". $toitem.",AdjustmentDt.Item_Code) ".
					" And ifNull(Items.Group_Code,'-1') between ifNull(". $fromgroup .",ifNull(Items.Group_Code,'-1')) and ifNull(". $togroup.",ifNull(Items.Group_Code,'-1'))  ".
					" And ifNull(Items.SubGroup_Code,'-1') between ifNull(". $fromsubgroup .",ifNull(Items.SubGroup_Code,'-1')) and ifNull(". $tosubgroup.",ifNull(Items.SubGroup_Code,'-1'))  ".
					" And ifNull(Items.Category_Code,'-1') between ifNull(". $fromcategory .",ifNull(Items.Category_Code,'-1')) and ifNull(". $tocategory.",ifNull(Items.Category_Code,'-1'))  ".
					" And ifNull(Adjustment.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Adjustment.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(Adjustment.Project_Code,'-1')) ".
					" And ifNull(Adjustment.Costcent_Code,'-1') between ifNull(".$fromcostcent.",ifNull(Adjustment.Costcent_Code,'-1')) and ifNull(". $tocostcent.",ifNull(Adjustment.Costcent_Code,'-1')) ".
					" And ifNull(AdjustmentDt.warehouse_code,'-1') Like Concat(ifNull(". $fromwarehouse .",ifNull(AdjustmentDt.warehouse_code,'-1')),'%')  ";
				
				if ($TransAI==1 && $TransAR==1) $query= $query." 	And AdjustmentDt.Journal_Type in ('AI','AR') ";
				else if ($TransAI==1 && $TransAR==0) $query= $query."	And AdjustmentDt.Journal_Type in ('AI') ";
				else if ($TransAI==0 && $TransAR==1) $query= $query."	And AdjustmentDt.Journal_Type in ('AR') ";
				
				if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
					$query= $query." 	And AdjustmentDt.warehouse_code In (select distinct warehouse_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  
				if ($groupexist > 0)
					$query= $query." 	And Items.group_code In (select distinct group_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  

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



if ($TransPD == 1 || $TransPDT == 1) {

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

	$query = $query." Select TransID, ItemCode, ItemDescription, In_TransType, In_TransReference, In_TransDate, In_TransQty, In_CurrencyCode, In_TransPrice, ".
					" Out_TransType, Out_TransReference, Out_TransDate, Out_TransQty, Out_CurrencyCode, Out_TransPrice, Out_TransPrice As Out_SellPrice, TransOrder, OrderDate, Days ".
					" From ( ";		
					
	if ($TransPDT == 1) {
	
		$query = $query." Select ProductionDt.ProductionDt_Id As TransID, ProductionDt.Item_Code As ItemCode, Items.Item_Description As ItemDescription, Null As In_TransType, Null As In_TransReference, ".
						" Null As In_TransDate, Date_Format(Production.Production_Date,'%Y%m%d') As OrderDate, ProductionDt.Warehouse_Code As WarehouseCode, ".
						" Null As In_TransQty, 7 As TransOrder, Null As In_CurrencyCode, Null As In_TransPrice, 'PD' As Out_TransType, Production.Production_Reference As Out_TransReference, ".
						" Date_Format(Production.Production_Date,'%d/%m/%Y') As Out_TransDate, ifNull(ProductionDt.ProductionDt_Quantity,0) As Out_TransQty, Production.Currency_Code As Out_CurrencyCode, ".
						" if(upper($curr)='".$Base1."',ProductionDt.ProductionDt_CostBase1,if(upper($curr)='".$Base2."',ProductionDt.ProductionDt_CostBase2, ProductionDt.ProductionDt_Cost)) As Out_TransPrice, ".
						" Null As Out_SellPrice, DATEDIFF(date_format(".$agingdate.",'%Y-%m-%d'), date_format(Production.Production_Date,'%Y-%m-%d')) Days ".
						" 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,'%') ";		 
			
		
		$query= $query.	" Where (Production.Production_Reference Is Not Null And Production.Production_Reference != '')  ".
						" And Date_format(Production.Production_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(Production.Production_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Production.Production_Date,'%Y-%m-%d'))  ".
						" And ProductionDt.Item_Code between ifNull(".$fromitem.",ProductionDt.Item_Code) and ifNull(". $toitem.",ProductionDt.Item_Code) ".
						" And ifNull(Items.Group_Code,'-1') between ifNull(". $fromgroup .",ifNull(Items.Group_Code,'-1')) and ifNull(". $togroup.",ifNull(Items.Group_Code,'-1'))  ".
						" And ifNull(Items.SubGroup_Code,'-1') between ifNull(". $fromsubgroup .",ifNull(Items.SubGroup_Code,'-1')) and ifNull(". $tosubgroup.",ifNull(Items.SubGroup_Code,'-1'))  ".
						" And ifNull(Items.Category_Code,'-1') between ifNull(". $fromcategory .",ifNull(Items.Category_Code,'-1')) and ifNull(". $tocategory.",ifNull(Items.Category_Code,'-1'))  ".
						" And ifNull(Production.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Production.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(Production.Project_Code,'-1')) ".
						" And ifNull(Production.Costcenter_Code,'-1') between ifNull(".$fromcostcent.",ifNull(Production.Costcenter_Code,'-1')) and ifNull(". $tocostcent.",ifNull(Production.Costcenter_Code,'-1')) ".
						" And ifNull(ProductionDt.warehouse_code,'-1') 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 ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  
					if ($groupexist > 0)
						$query= $query." 	And Items.group_code In (select distinct group_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  
	}	
		
	if ($TransPD == 1 && $TransPDT == 1) 	
		$query= $query." Union ";
	
	if ($TransPD == 1) {
		
		$query = $query." Select Production.Production_Id As TransID, Production.Item_Code As ItemCode, Items.Item_Description As ItemDescription, 'PD' As In_TransType, Production.Production_Reference As In_TransReference, ".
						" Date_Format(Production.Production_Date,'%d/%m/%Y') As In_TransDate, Date_Format(Production.Production_Date,'%Y%m%d') As OrderDate, Production.Warehouse_Code As WarehouseCode, ".
						" ifNull(Production.Production_Quantity,0) As In_TransQty, 4 As TransOrder, Production.Currency_Code As In_CurrencyCode, ".
						" if(upper($curr)='".$Base1."',Production.Production_TotalBase1,if(upper($curr)='".$Base2."',Production.Production_TotalBase2, Production.Production_Total))/ifNull(Production.Production_Quantity,1) As In_TransPrice, ".
						" Null As Out_TransType, Null As Out_TransReference, Null As Out_TransDate, Null As Out_TransQty,  Null As Out_CurrencyCode, Null As Out_TransPrice, Null As Out_SellPrice, ".
						" DATEDIFF(date_format(".$agingdate.",'%Y-%m-%d'), date_format(Production.Production_Date,'%Y-%m-%d')) Days ".
						" 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,'%') ";		 
			
		
		$query= $query.	" Where (Production.Production_Reference Is Not Null And Production.Production_Reference != '')  ".
						" And Date_format(Production.Production_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(Production.Production_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Production.Production_Date,'%Y-%m-%d'))  ".
						" And Production.Item_Code between ifNull(".$fromitem.",Production.Item_Code) and ifNull(". $toitem.",Production.Item_Code) ".
						" And ifNull(Items.Group_Code,'-1') between ifNull(". $fromgroup .",ifNull(Items.Group_Code,'-1')) and ifNull(". $togroup.",ifNull(Items.Group_Code,'-1'))  ".
						" And ifNull(Items.SubGroup_Code,'-1') between ifNull(". $fromsubgroup .",ifNull(Items.SubGroup_Code,'-1')) and ifNull(". $tosubgroup.",ifNull(Items.SubGroup_Code,'-1'))  ".
						" And ifNull(Items.Category_Code,'-1') between ifNull(". $fromcategory .",ifNull(Items.Category_Code,'-1')) and ifNull(". $tocategory.",ifNull(Items.Category_Code,'-1'))  ".
						" And ifNull(Production.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Production.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(Production.Project_Code,'-1')) ".
						" And ifNull(Production.Costcenter_Code,'-1') between ifNull(".$fromcostcent.",ifNull(Production.Costcenter_Code,'-1')) and ifNull(". $tocostcent.",ifNull(Production.Costcenter_Code,'-1')) ".
						" And ifNull(Production.warehouse_code,'-1') 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 ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  
					if ($groupexist > 0)
						$query= $query." 	And Items.group_code In (select distinct group_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  
	}
	
	$query= $query." 	Order by ItemCode, OrderDate, TransOrder, In_TransReference, Out_TransReference  ";				
	$query= $query." ) Q1 ";
	
	$SelectedTransaction = 1;
	
}

$query= $query." ) MainQuery ";
$query= $query. " Left Join (  ".
				"               Select Stk.Item_Code, Sum((ifNull(quantityproducein,0) + ifNull(quantityentered,0) - ifNull(quantityproduceout,0) - ifNull(quantitydelivered,0))) As PrevQuantity  ".
				"               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 (($Module_Grn=1 and Stk.Transaction_Type <> 'PI') or ($Module_Grn=0 and 1=1)) ".
				"               Group By Stk.Item_Code ".
				"             ) As QQtyPrev on MainQuery.ItemCode = QQtyPrev.Item_Code   ";
				
$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') between ifNull(". $fromdate.",date_format(Stk.Transaction_Date,'%Y-%m-%d')) and ifNull(". $todate.",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 (($Module_Grn=1 and Stk.Transaction_Type <> 'PI') or ($Module_Grn=0 and 1=1)) ".
				"               Group By Stk.Item_Code ".
				"             ) As QQtyExists on MainQuery.ItemCode = QQtyExists.Item_Code   ";				
					
$query= $query." Where (ifNull(QQtyPrev.PrevQuantity,0) + ifNull(QQtyExists.QuantityExists,0)) != 0 ";
$query= $query." Order by ItemCode, OrderDate, TransOrder, In_TransReference, Out_TransReference  ";

$query= $query." ) Q1 ";	
$query= $query." Order by ItemCode, OrderDate, TransOrder, In_TransReference, Out_TransReference  ";
$query= $query." ) Q2 ";	
$query= $query." Order by ItemCode, OrderDate, TransOrder, In_TransReference, Out_TransReference  ";
//echo $query; exit;


$MainRecord  = '';
$GroupDesc   = '';

$result=mysql_query($query,$connection) or die(mysql_error());
while($rows=mysql_fetch_row($result)){
		
	if ($MainRecord == '' || $MainRecord != $rows[0])  {
		
		if ($MainRecord != '') {
			echo "<tr>";
			echo "<td colspan='5'><hr width='100%' color='black' size='1'></td>";
			echo "</tr>"; 
		
		}
		
		
		echo "<tr height='25'>";
		echo "   <td align='left'  valign='top' class='grouplevel1' >Item: ". $rows[0]."</td>";
		echo "   <td align='left'  valign='top' class='grouplevel1' >". $rows[1]."</td>";
		echo "   <td align='right' valign='top' class='grouplevel1' >Previous Qty: </td>";
		echo "   <td align='right' valign='top' class='grouplevel1' >". number_format($rows[18], $StmtOfItemRMaskQty)."</td>";
		echo "   <td align='right' valign='top' class='grouplevel1' >&nbsp;</td>";
		echo "</tr>"; 
				
	
		$MainRecord   = $rows[0];
		
	}	
	
	echo "<tr>"; 
	echo "<td colspan='5' align='left' valign='top'><table  border='0' width='100%' align='center' class='master' style='table-layout:fixed' cellpadding='0' cellspacing='0'>
		  <col width=50>
		  <col width=60>
		  <col width=70>
		  <col width=60>";
		  
	if ($prtcostin == 1) echo" <col width=50>  <col width=100>";

	echo "<col width=50>
		  <col width=60>
		  <col width=70>
		  <col width=60>";
		  
	if ($prtcostout == 1)   echo"<col width=50>  <col width=100>";
	if ($prtsellprice == 1) echo"<col width=100>";
		
	echo" <col width=50>
		  <col width=70>";

	echo "<tr>"; 
	echo "	<td align='left'   valign='top' >". $rows[2] ."</td>";
	echo "	<td align='left'   valign='top' >". $rows[3] ."</td>";
	echo "	<td align='left'   valign='top' >". $rows[4] ."</td>";
	echo "	<td align='right'  valign='top' >". $rows[5] ."</td>";
	
	if ($prtcostin == 1) {
		echo "	<td align='center' valign='top' >". $rows[6] ."</td>";
		echo "	<td align='right'  valign='top' >". $rows[7] ."</td>";
	}
	
	echo "	<td align='center'   valign='top' >". $rows[8] ."</td>";
	echo "	<td align='left'   valign='top' >". $rows[9] ."</td>";
	echo "	<td align='left'   valign='top' >". $rows[10] ."</td>";
	echo "	<td align='right'  valign='top' >". $rows[11] ."</td>";
	
	if ($prtcostout == 1) {
		echo "	<td align='center' valign='top' >". $rows[12] ."</td>";
		echo "	<td align='right'  valign='top' >". $rows[13] ."</td>";
	}
	
	if ($prtsellprice == 1)
		echo "	<td align='right'  valign='top' >". $rows[14] ."</td>";
		
	echo "	<td align='right'  valign='top' >". $rows[17] ."</td>";
	echo "	<td align='right'  valign='top' >". $rows[21] ."</td>";
	
	echo "</tr>";
	echo "</table></td>";
	echo "</tr>";
	

}
	
	
echo "</table>";

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

function format_currency($amount, $DecimalBase) { 

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

function current_server_time($connection){

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

  return $now;
}

function explodeword($date)
{

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

//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");
}

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

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

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

if ($printaccess==1)
print <<< HERE

<script language=javascript>
if (typeof(window.print) != 'undefined')
{
    window.print();
}
</script>
</BODY>
</HTML>
HERE;

?>