<?php

session_start();



/////////////////////////////////////////////////////// 
//BLOCK USED TO REDIRECT TO COMPANY SPECIFIC REPORTS//
/////////////////////////////////////////////////////
 $curfolder = substr(getcwd(), strrpos(getcwd(), '\\') + 1);
 $curfolder = strtolower($curfolder);
 if ($curfolder == "contract")
 {
$pagename = basename($_SERVER['REQUEST_URI']);
$pagenamewithoutparams =  basename($_SERVER['PHP_SELF']);
$CompYear = explode('_',$_SESSION["dbName"]);
$companyname = strtoupper($CompYear[0]);
$clientrelatedpage = getcwd()  . "\\" .$companyname . "\\".$$pagenamewithoutparams  ;

 
if (file_exists($clientrelatedpage.$pagenamewithoutparams )) {
header("location:$companyname/$pagename");
} 

if (!file_exists($clientrelatedpage)) {
    mkdir($clientrelatedpage, 0777, true);
}
	
}
////////////////////////////////////////////////////////////////////////////////////////// 
///adjust include,css, javascript library etc... in cloned report ex ////////////////////
// $success = include "config.inc.php"; if (!$success) {include "../config.inc.php";}///
//js exemplage <script src = '../scriptname.js' >  </script>     //////////////////////
//////////////////////////////////////////////////////////////////////////////////////




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


//$options 1=GRN   2=GRN RM
if ($options == 2) {
	if ($status == 1) {
		$ReportName  = 'List of GRN Raw Materials Pending';
		$ReportTitle = 'List of GRN Raw Materials Pending';
	}
	else if ($status == 2) {
		$ReportName  = 'List of GRN Raw Materials Purchased';
		$ReportTitle = 'List of GRN Raw Materials Purchased';
	}
	else {
		$ReportName  = 'List of GRN Raw Materials';
		$ReportTitle = 'List of GRN Raw Materials';
	}	
} 
else {
	if ($status == 1) {
		$ReportName  = 'List of GRN Pending';
		$ReportTitle = 'List of GRN Pending';
	}
	else if ($status == 2) {
		$ReportName  = 'List of GRN Purchased';
		$ReportTitle = 'List of GRN Purchased';
	}
	else {
		$ReportName  = 'List of GRN';
		$ReportTitle = 'List of GRN';
	}
}

print <<< HERE
<HTML>
<HEAD>
<TITLE> $ReportName </TITLE>

<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: 12px; 
                  font-family:Times-New-Roman;
				  padding-left:3px;
                 }

						
table.master tr.group { font-size: 12px; 
						font-weight: bold;
						font-family:Times-New-Roman;
					  }				 
			 

</style>
</HEAD>
HERE;
$success = include "config.inc.php"; if (!$success) {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, Base1, Base2, Company_Name As CompanyName, ifNull(PurchaseRMaskQty,0) As PurchaseRMaskQty 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);
}

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


$SelectedOptions = "Filter by: <br>";
if ($fromdate != '')      $SelectedOptions .= "-> Date From: ". $fromdate . "  To: ". $todate ."<br>";	
if ($fromref != '')       $SelectedOptions .= "-> Ref. From: ". $fromref . "  To: ". $toref ."<br>";
if ($fromitem != '')      $SelectedOptions .= "-> Item From: ". $fromitem . "  To: ". $toitem ."<br>";
if ($fromsupplier != '')  $SelectedOptions .= "-> Supplier From: ". $fromsupplier . "  To: ". $tosupplier ."<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 (!empty($fromdate))
   $fromdate = explodeword($fromdate);
if (!empty($todate))
   $todate = explodeword($todate);     

$fromitem      = GetSQLValueString($fromitem, 'text');
$toitem        = GetSQLValueString($toitem, 'text');
$fromref       = GetSQLValueString($fromref, 'text');
$toref         = GetSQLValueString($toref, 'text');
$fromsupplier  = GetSQLValueString($fromsupplier, 'text');
$tosupplier    = GetSQLValueString($tosupplier, 'text');
$fromgroup     = GetSQLValueString($fromgroup, 'text');
$togroup       = GetSQLValueString($togroup, 'text');
$fromsubgroup  = GetSQLValueString($fromsubgroup, 'text');
$tosubgroup    = GetSQLValueString($tosubgroup, 'text');
$fromcategory  = GetSQLValueString($fromcategory, 'text');
$tocategory    = GetSQLValueString($tocategory, 'text');
$fromproject   = GetSQLValueString($fromproject, 'text');
$toproject     = GetSQLValueString($toproject, 'text');
$fromcostcent  = GetSQLValueString($fromcostcent, 'text');
$tocostcent    = GetSQLValueString($tocostcent, 'text');
$fromwarehouse = GetSQLValueString($fromwarehouse, 'text');
$fromdate  	   = GetSQLValueString($fromdate, 'date');
$todate        = GetSQLValueString($todate, 'date');   

   

print <<< HERE
<table width="100%" border="0" align="left" >
<tr><td>
<table width="100%" border="0" align="center">
<tr>
  <td width='30%'><b>$CompanyName</b></td>
  <td width='50%' align='center'><font size=4><b>$ReportTitle</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 "<br>".$dt[1]." ".$dt[2];
  
print <<<HERE
</font></td>
</tr>
<tr>
   <td colspan='4' width='100%'> <hr width='100%' color='black' size='1'> </td>
</tr>
</table>


<table class="params" width="100%" border="0" align="center" >
<tr>
  <td width="100%" valign='top'><b>$SelectedOptions</b></td>
</tr>
<tr>
  <td colspan='15'><hr width='100%' color='black' size='1'></td>
</tr>
</table>
HERE;

echo "<table  border='0' width='100%' align='center' class='master' style='table-layout:fixed;' cellpadding='1' cellspacing='0'>
	  <col width=70>	
	  <col width=70>
	  <col width=170>
	  <col width=100>
	  <col width=250>
	  <col width=70>
	  <col width=90>";
	  
if ($options == 2) {	  
	echo"<col width=90>
		 <col width=230>";
} else
	echo"<col width=300>";
	  
echo"<tr>
		<th align='left'>GRN Ref.</th>
		<th align='center'>Date</th>
		<th align='center'>Supplier</th>
		<th align='left'>Item Code</th>
		<th align='left'>Description</th>
		<th align='center'>Qty</th>
		<th align='center'>Invoice No.</th>";

if ($options == 2)
	echo"<th align='center'>Pack Ref.</th>";

echo"	<th align='center'>Remark</th>";
echo"</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 = '';

$query = $query." Select GrnReference, GrnDate, ItemCode, ItemDescription, ItemQty, InvoiceNo, PackRef, GrnRemark, OrderDate, SupplierName ".
				" From ( ";
					
$query = $query." Select GrnPurchases.Purchase_Reference As GrnReference, Date_Format(GrnPurchases.Purchase_Date,'%d/%m/%Y') As GrnDate, ".
				" GrnPurchasesDt.Item_Code As ItemCode, Items.Item_Description As ItemDescription, ifNull(GrnPurchasesDt.PurchaseDt_Quantity,0) As ItemQty, ".
				" GrnPurchases.Purchase_PackingListRef As PackRef, GrnPurchases.Purchase_InvoiceNo As InvoiceNo, ".
				" GrnPurchases.Purchase_Remark As GrnRemark, Date_Format(GrnPurchases.Purchase_Date,'%Y%m%d') As OrderDate, Suppliers.Ledger_Name As SupplierName  ".
				" From (  ";
				
if ($options == 2 && $showdetail == 0) {
	$query = $query." Select GrnPurchasesDt.Purchase_ID As Purchase_ID, GrnPurchasesDt.Item_Code As Item_Code, ".
					" GrnPurchasesDt.Warehouse_Code As Warehouse_Code, ifNull(GrnPurchasesDt.PurchaseDt_Quantity,0) As PurchaseDt_Quantity, 1 As ItemsCount ".
					" From GrnPurchasesDt ".
					" Where GrnPurchasesDt.Item_CodeFinished Is Null ".
					" And GrnPurchasesDt.Item_CodeSemiFinished Is Null ".
					" Union ".
					" Select GrnPurchasesDt.Purchase_ID As Purchase_ID, GrnPurchasesDt.Item_CodeFinished As Item_Code, ".
					" GrnPurchasesDt.Warehouse_Code As Warehouse_Code, ifNull(Item_qtyPacked,0) As PurchaseDt_Quantity, Count(*) As ItemsCount ".
					" From GrnPurchasesDt ".
					" Where GrnPurchasesDt.Item_CodeFinished Is Not Null ".
					" Group By GrnPurchasesDt.Item_CodeFinished, GrnPurchasesDt.Warehouse_Code ".
					" Union ".
					" Select GrnPurchasesDt.Purchase_ID As Purchase_ID, GrnPurchasesDt.Item_CodeSemiFinished As Item_Code, ".
					" GrnPurchasesDt.Warehouse_Code As Warehouse_Code, ifNull(Item_qtyPacked,0) As PurchaseDt_Quantity, Count(*) As ItemsCount ".
					" From GrnPurchasesDt ".
					" Where GrnPurchasesDt.Item_CodeSemiFinished Is Not Null ".
					" Group By GrnPurchasesDt.Item_CodeSemiFinished, GrnPurchasesDt.Warehouse_Code ";
					
}
else {				
	$query = $query." Select GrnPurchasesDt.Purchase_ID As Purchase_ID, GrnPurchasesDt.Item_Code As Item_Code, ".
					" GrnPurchasesDt.Warehouse_Code, ifNull(GrnPurchasesDt.PurchaseDt_Quantity,0) As PurchaseDt_Quantity ".
					" From GrnPurchasesDt ";
				
}				

$query = $query." ) As GrnPurchasesDt ".
				" Inner Join GrnPurchases on GrnPurchasesDt.Purchase_ID = GrnPurchases.Purchase_ID ".
				" Left Join Suppliers on GrnPurchases.Supplier_Code = Suppliers.Ledger_Number ".
				" 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,'%') ";		 
	
if ($status == 1 || $status == 2) {
	$query= $query.	" Inner Join ( Select Purchase_ID, Sum(QuantityGrn - QuantityPurchased) as QuantityPending ".
					"			   From (           ".
					"                     Select GrnPurchasesDt.Purchase_ID, GrnPurchasesDt.Item_Code, ifNull(GrnPurchasesDt.PurchaseDt_Quantity,0) As QuantityGrn, ".
					"                     Sum(ifNull(PurchasesDt.PurchaseDt_Quantity,0)) As QuantityPurchased ".
					"             		  From GrnPurchasesDt ".
					"       		      Left Outer Join PurchasesDt on PurchasesDt.RelatedDt_ID = GrnPurchasesDt.PurchaseDt_ID ".
					"	            	  Where ifNull(GrnPurchasesDt.PurchaseDt_OldData,0) = 0  ".
					"                     And ifNull(PurchasesDt.PurchaseDt_OldData,0) = 0 ".
					"              	      Group by GrnPurchasesDt.Purchase_ID, PurchasesDt.RelatedDt_ID ".
					"              	    ) as A Group By Purchase_ID ";
					
	if ($status == 1) 	   $query = $query." Having QuantityPending > 0 ";
	else if ($status == 2) $query = $query." Having QuantityPending = 0 ";
	
	$query = $query."	          ) As Q on Q.Purchase_ID = GrnPurchases.Purchase_ID ";
	
}	
		
$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 GrnPurchases.Purchase_Reference between ifNull(".$fromref.",GrnPurchases.Purchase_Reference) and ifNull(". $toref.",GrnPurchases.Purchase_Reference) ".
				" And GrnPurchases.Supplier_Code between ifNull(".$fromsupplier.",GrnPurchases.Supplier_Code) and ifNull(". $tosupplier.",GrnPurchases.Supplier_Code) ".
				" 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 ($options==1) $query= $query." And ifNull(GrnPurchases.Purchase_Type,'GRN') = 'GRN' ";				
else if ($options==2) $query= $query." And GrnPurchases.Purchase_Type = 'RM' ";
	  
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 GrnReference, OrderDate, ItemCode  ";				
$query= $query." ) Q1 ";

//echo $query; exit;
$result=mysql_query($query,$connection) or die(mysql_error());
while($rows=mysql_fetch_row($result)){
	
	echo "<tr>"; 
	echo "	<td align='left'   valign='top' >". $rows[0] ."</td>";
	echo "	<td align='left'   valign='top' >". $rows[1] ."</td>";
	echo "	<td align='left'   valign='top' >". $rows[9] ."</td>";
	echo "	<td align='left'   valign='top' >". $rows[2] ."</td>";
	echo "	<td align='left'   valign='top' >". $rows[3] ."</td>";
	echo "	<td align='right'  valign='top' style='padding-right:5px;'>". number_format($rows[4], $PurchaseRMaskQty) ."</td>";
	echo "	<td align='left'   valign='top' >". $rows[5] ."</td>";
	
	if ($options == 2)	
		echo "	<td align='left'   valign='top' >". $rows[6] ."</td>";
	
	echo "	<td align='left'   valign='top' >". $rows[7] ."</td>";
	echo "</tr>";
	
}


function current_server_time($connection){

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

  return $now;
}

function explodeword($date)
{

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

function format_currency($amount, $DecimalBase) { 

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

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;


?>