<?php

session_start();
header("Content-type: text/html; charset=".$_SESSION['encodingmode']);
if($_SESSION['dbName']==""){
	header("location:../index.php");
}

print <<< HERE
<HTML>
<HEAD>
<TITLE> Detailed Pending Porder Report </TITLE>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1256" />
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<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.main tr.group { font-size: 14px; 
					  font-weight: bold;
                      font-family:Times-New-Roman;
                    }	

table.main td.grouplevel1 { font-size: 14px; 
						    font-weight: bold;
                            font-family:Times-New-Roman;
						    color: #0000FF;
							background-color: #F0F0F0;
                           }
table.main td.grouplevel2 { font-size: 14px; 
                            font-family:Times-New-Roman;
						    color: #FF0000;
							background-color: #F0F0F0;
                           }	
table.main td.grouplevel3 { font-size: 13px; 
							font-style: italic;
                            font-family:Times-New-Roman;
						    color: #336600;
							background-color: #F0F0F0;
                           }					

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

table.master tr.totals { font-size: 12px; 
                         font-weight: bold;
                         font-family:Times-New-Roman;
                       }
table.master tr.summary { font-size: 12px; 
                          font-weight: bold;
                          font-family:Times-New-Roman;
						  background-color: #F0F0F0;
                        }					   

table.master tr.grouplevel1total { font-size: 14px; 
								   font-weight: bold;
								   font-family:Times-New-Roman;
								   color: #0000FF;
								   background-color: #F0F0F0;
								 }				 
table.master tr.grouplevel2total { font-size: 14px; 
							       font-family:Times-New-Roman;
							       color: #FF0000;
							       background-color: #F0F0F0;
						         }										 
table.master tr.grouplevel3total { font-size: 13px; 
							       font-style: italic;
							       font-family:Times-New-Roman;
							       color: #336600;
							       background-color: #F0F0F0;
						          }						   
				
table.group td { font-size: 12px; 
                 font-weight: bold;
				 font-style: italic;
                 font-family:Times-New-Roman;
               }				 
			   
			   
table.detail th { font-size: 12px; 
                  font-weight: bold;
                  font-family:Times-New-Roman;
				  border-bottom:1px solid black;
				}  
			   
table.detail td { font-size: 12px; 
                  font-family:Times-New-Roman;
                 }
				 
table.detail td.totals { font-size: 12px; 
                         font-weight: bold;
                         font-family:Times-New-Roman;
                       }				 				 
		 
				 

</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($_SESSION['dbName'],$connection) or die("Could not find DB");


if(	$_SESSION['encodingmode'] == 'utf8' || $_SESSION['encodingmode'] == 'utf-8'){
	mysql_query("SET NAMES 'utf8'"); 
	mysql_query('SET CHARACTER SET utf8'); 
	mysql_set_charset('utf8', $connection);
}
$querylog="SELECT print as printaccess from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' 	and Upper(Login_Schema) = Upper('".$_SESSION['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, report_color1, report_color2, Company_Name As CompanyName, ifNull(PorderReportMaskQty,0) As PorderReportMaskQty from setup";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultsetup)){
  extract($rows);
} 

//Read From PurchaseSetup Table
$queryPurchaseSetup="Select PurchaseTTC From PurchaseSetup";
$resultPurchaseSetup=mysql_query($queryPurchaseSetup,$connection) or die(mysql_error());
while($rowPurchaseSetup=mysql_fetch_array($resultPurchaseSetup)){
	extract($rowPurchaseSetup);
}


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


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

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>Detailed Pending Porder</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 == 'currency1'){
   $curr='null';  
   $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 ($fromproject != '')   $SelectedOptions .= "-> Project From: ". $fromproject . "<br>";
if ($fromcostcent != '')  $SelectedOptions .= "-> Costcent From: ". $fromcostcent . "<br>";
if ($fromsupplier != '')  $SelectedOptions .= "-> Supplier From: ". $fromsupplier . "  To: ". $tosupplier ."<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');
$fromproject   = GetSQLValueString($fromproject, 'text');
$fromcostcent  = GetSQLValueString($fromcostcent, 'text');
$fromgroup     = GetSQLValueString($fromgroup, 'text');
$togroup       = GetSQLValueString($togroup, 'text');
$fromsubgroup  = GetSQLValueString($fromsubgroup, 'text');
$tosubgroup    = GetSQLValueString($tosubgroup, 'text');
$fromwarehouse = GetSQLValueString($fromwarehouse, 'text');
$fromsupplier  = GetSQLValueString($fromsupplier, 'text');
$tosupplier    = GetSQLValueString($tosupplier, 'text');
$fromdate  	   = GetSQLValueString($fromdate, 'date');
$todate        = GetSQLValueString($todate, 'date');  

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

if ($groupby3 != 1 ) {
	$pairs = explode(",", $groupby3);
	
	$vCount = count($pairs);
	if ($vCount > 1) {
		$QryGroupby3Code = 'Concat( ';
		$QryGroupby3Name = 'Concat( ';
	}	
	foreach ($pairs as $pair) {
		
		if ($groupby3name != '') {
			$groupby3name .= " & ";
			$QryGroupby3Code .= ", '*', ";
			$QryGroupby3Name .= ", '*', ";
		}	
			
		if ($pair == 2 ) {	
			$groupby3name    .= 'Supplier';
			$QryGroupby3Code .= 'ifNull(SupplierCode,"None")';
			$QryGroupby3Name .= 'ifNull(SupplierName,"None")';
		}
		else if ($pair == 20 ) {	
			$groupby3name    .= 'Transaction Project(Parent)';
			$QryGroupby3Code .= 'ifNull(ParentProjectCode,"None")';
			$QryGroupby3Name .= 'ifNull(ParentProjectName,"None")';
		}
		else if ($pair == 4 ) {	
			$groupby3name    .= 'Transaction Project(Detail)';
			$QryGroupby3Code .= 'ifNull(ProjectCode,"None")';
			$QryGroupby3Name .= 'ifNull(ProjectName,"None")';
		}
		else if ($pair == 21 ) {	
			$groupby3name 	 .= 'Archieve Project(Parent)';
			$QryGroupby3Code .= 'ifNull(ParentMasterProject,"None")';
			$QryGroupby3Name .= 'ifNull(ParentMasterProject,"None")';
		}
		else if ($pair == 5 ) {	
			$groupby3name 	 .= 'Archieve Project(Detail)';
			$QryGroupby3Code .= 'ifNull(MasterProject,"None")';
			$QryGroupby3Name .= 'ifNull(MasterProject,"None")';
		}
		else if ($pair == 22 ) {	
			$groupby3name    .= 'Transaction Costcenter(Parent)';	
			$QryGroupby3Code .= 'ifNull(ParentCostcenterCode,"None")';
			$QryGroupby3Name .= 'ifNull(ParentCostcenterName,"None")';
		}
		else if ($pair == 6 ) {	
			$groupby3name    .= 'Transaction Costcenter(Detail)';	
			$QryGroupby3Code .= 'ifNull(CostcenterCode,"None")';
			$QryGroupby3Name .= 'ifNull(CostcenterName,"None")';
		}
		else if ($pair == 23 ) {
			$groupby3name 	 .= 'Archieve Costcenter(Parent)';	
			$QryGroupby3Code .= 'ifNull(ParentMasterCostcent,"None")';
			$QryGroupby3Name .= 'ifNull(ParentMasterCostcent,"None")';
		}
		else if ($pair == 7 ) {
			$groupby3name 	 .= 'Archieve Costcenter(Detail)';	
			$QryGroupby3Code .= 'ifNull(MasterCostcent,"None")';
			$QryGroupby3Name .= 'ifNull(MasterCostcent,"None")';
		}
		else if ($pair == 24 ) { 	
			$groupby3name	 .= 'Warehouse(Parent)';	
			$QryGroupby3Code .= 'ifNull(ParentWarehouseCode,"None")';
			$QryGroupby3Name .= 'ifNull(ParentWarehouseCode,"None")';
		}
		else if ($pair == 8 ) { 	
			$groupby3name	 .= 'Warehouse(Detail)';	
			$QryGroupby3Code .= 'ifNull(WarehouseCode,"None")';
			$QryGroupby3Name .= 'ifNull(WarehouseCode,"None")';
		}
		else if ($pair == 25 )	{
			$groupby3name    .= 'Group(Parent)';
			$QryGroupby3Code .= 'ifNull(ParentGroupCode,"None")';
			$QryGroupby3Name .= 'ifNull(ParentGroupName,"None")';
		}
		else if ($pair == 10 )	{
			$groupby3name    .= 'Group(Detail)';
			$QryGroupby3Code .= 'ifNull(GroupCode,"None")';
			$QryGroupby3Name .= 'ifNull(GroupName,"None")';
		}
		else if ($pair == 26 )	{
			$groupby3name 	 .= 'Subgroup(Parent)';
			$QryGroupby3Code .= 'ifNull(ParentSubGroupCode,"None")';
			$QryGroupby3Name .= 'ifNull(ParentSubGroupName,"None")';
		}
		else if ($pair == 11 )	{
			$groupby3name 	 .= 'Subgroup(Detail)';
			$QryGroupby3Code .= 'ifNull(SubGroupCode,"None")';
			$QryGroupby3Name .= 'ifNull(SubGroupName,"None")';
		}
		else if ($pair == 12 ) {	
			$groupby3name 	 .= 'Category';
			$QryGroupby3Code .= 'ifNull(CategoryCode,"None")';
			$QryGroupby3Name .= 'ifNull(CategoryName,"None")';
		}
		
	}
	
	if ($vCount > 1) {
		$QryGroupby3Code .= ' ) ';
		$QryGroupby3Name .= ' ) ';
	}
	
	$QryGroupby3Code .= ' As Groupby3Code, ';
	$QryGroupby3Name .= ' As Groupby3Name ';

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

if ($groupby1 != '' ) {
		
	if ($groupby1 == 2 ) {	
		$groupby1name    .= 'Supplier';
		$QryGroupby1Code .= 'ifNull(SupplierCode,"None")';
		$QryGroupby1Name .= 'ifNull(SupplierName,"None")';
	}
	else if ($groupby1 == 20 ) {	
		$groupby1name    .= 'Transaction Project(Parent)';
		$QryGroupby1Code .= 'ifNull(ParentProjectCode,"None")';
		$QryGroupby1Name .= 'ifNull(ParentProjectName,"None")';
	}
	else if ($groupby1 == 4 ) {	
		$groupby1name    .= 'Transaction Project(Detail)';
		$QryGroupby1Code .= 'ifNull(ProjectCode,"None")';
		$QryGroupby1Name .= 'ifNull(ProjectName,"None")';
	}
	else if ($groupby1 == 21 ) {	
		$groupby1name 	 .= 'Archieve Project(Parent)';
		$QryGroupby1Code .= 'ifNull(ParentMasterProject,"None")';
		$QryGroupby1Name .= 'ifNull(ParentMasterProject,"None")';
	}
	else if ($groupby1 == 5 ) {	
		$groupby1name 	 .= 'Archieve Project(Detail)';
		$QryGroupby1Code .= 'ifNull(MasterProject,"None")';
		$QryGroupby1Name .= 'ifNull(MasterProject,"None")';
	}
	else if ($groupby1 == 22 ) {	
		$groupby1name    .= 'Transaction Costcenter(Parent)';	
		$QryGroupby1Code .= 'ifNull(ParentCostcenterCode,"None")';
		$QryGroupby1Name .= 'ifNull(ParentCostcenterName,"None")';
	}
	else if ($groupby1 == 6 ) {	
		$groupby1name    .= 'Transaction Costcenter(Detail)';	
		$QryGroupby1Code .= 'ifNull(CostcenterCode,"None")';
		$QryGroupby1Name .= 'ifNull(CostcenterName,"None")';
	}
	else if ($groupby1 == 23 ) {
		$groupby1name 	 .= 'Archieve Costcenter(Parent)';	
		$QryGroupby1Code .= 'ifNull(ParentMasterCostcent,"None")';
		$QryGroupby1Name .= 'ifNull(ParentMasterCostcent,"None")';
	}
	else if ($groupby1 == 7 ) {
		$groupby1name 	 .= 'Archieve Costcenter(Detail)';	
		$QryGroupby1Code .= 'ifNull(MasterCostcent,"None")';
		$QryGroupby1Name .= 'ifNull(MasterCostcent,"None")';
	}
	else if ($groupby1 == 24 ) { 	
		$groupby1name	 .= 'Warehouse(Parent)';	
		$QryGroupby1Code .= 'ifNull(ParentWarehouseCode,"None")';
		$QryGroupby1Name .= 'ifNull(ParentWarehouseCode,"None")';
	}
	else if ($groupby1 == 8 ) { 	
		$groupby1name	 .= 'Warehouse(Detail)';	
		$QryGroupby1Code .= 'ifNull(WarehouseCode,"None")';
		$QryGroupby1Name .= 'ifNull(WarehouseCode,"None")';
	}
	else if ($groupby1 == 25 )	{
		$groupby1name    .= 'Group(Parent)';
		$QryGroupby1Code .= 'ifNull(ParentGroupCode,"None")';
		$QryGroupby1Name .= 'ifNull(ParentGroupName,"None")';
	}
	else if ($groupby1 == 10 )	{
		$groupby1name    .= 'Group(Detail)';
		$QryGroupby1Code .= 'ifNull(GroupCode,"None")';
		$QryGroupby1Name .= 'ifNull(GroupName,"None")';
	}
	else if ($groupby1 == 26 )	{
		$groupby1name 	 .= 'Subgroup(Parent)';
		$QryGroupby1Code .= 'ifNull(ParentSubGroupCode,"None")';
		$QryGroupby1Name .= 'ifNull(ParentSubGroupName,"None")';
	}
	else if ($groupby1 == 11 )	{
		$groupby1name 	 .= 'Subgroup(Detail)';
		$QryGroupby1Code .= 'ifNull(SubGroupCode,"None")';
		$QryGroupby1Name .= 'ifNull(SubGroupName,"None")';
	}
	else if ($groupby1 == 12 ) {	
		$groupby1name 	 .= 'Category';
		$QryGroupby1Code .= 'ifNull(CategoryCode,"None")';
		$QryGroupby1Name .= 'ifNull(CategoryName,"None")';
	}
	else {
		$groupby1name    .= '';				
		$QryGroupby1Code .= "-1";
		$QryGroupby1Name .= "'None'";
	}
		
	
	$QryGroupby1Code .= ' As Groupby1Code, ';
	$QryGroupby1Name .= ' As Groupby1Name, ';

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

if ($groupby2 != '' ) {
		
	if ($groupby2 == 2 ) {	
		$groupby2name    .= 'Supplier';
		$QryGroupby2Code .= 'ifNull(SupplierCode,"None")';
		$QryGroupby2Name .= 'ifNull(SupplierName,"None")';
	}
	else if ($groupby2 == 20 ) {	
		$groupby2name    .= 'Transaction Project(Parent)';
		$QryGroupby2Code .= 'ifNull(ParentProjectCode,"None")';
		$QryGroupby2Name .= 'ifNull(ParentProjectName,"None")';
	}
	else if ($groupby2 == 4 ) {	
		$groupby2name    .= 'Transaction Project(Detail)';
		$QryGroupby2Code .= 'ifNull(ProjectCode,"None")';
		$QryGroupby2Name .= 'ifNull(ProjectName,"None")';
	}
	else if ($groupby2 == 21 ) {	
		$groupby2name 	 .= 'Archieve Project(Parent)';
		$QryGroupby2Code .= 'ifNull(ParentMasterProject,"None")';
		$QryGroupby2Name .= 'ifNull(ParentMasterProject,"None")';
	}
	else if ($groupby2 == 5 ) {	
		$groupby2name 	 .= 'Archieve Project(Detail)';
		$QryGroupby2Code .= 'ifNull(MasterProject,"None")';
		$QryGroupby2Name .= 'ifNull(MasterProject,"None")';
	}
	else if ($groupby2 == 22 ) {	
		$groupby2name    .= 'Transaction Costcenter(Parent)';	
		$QryGroupby2Code .= 'ifNull(ParentCostcenterCode,"None")';
		$QryGroupby2Name .= 'ifNull(ParentCostcenterName,"None")';
	}
	else if ($groupby2 == 6 ) {	
		$groupby2name    .= 'Transaction Costcenter(Detail)';	
		$QryGroupby2Code .= 'ifNull(CostcenterCode,"None")';
		$QryGroupby2Name .= 'ifNull(CostcenterName,"None")';
	}
	else if ($groupby2 == 23 ) {
		$groupby2name 	 .= 'Archieve Costcenter(Parent)';	
		$QryGroupby2Code .= 'ifNull(ParentMasterCostcent,"None")';
		$QryGroupby2Name .= 'ifNull(ParentMasterCostcent,"None")';
	}
	else if ($groupby2 == 7 ) {
		$groupby2name 	 .= 'Archieve Costcenter(Detail)';	
		$QryGroupby2Code .= 'ifNull(MasterCostcent,"None")';
		$QryGroupby2Name .= 'ifNull(MasterCostcent,"None")';
	}
	else if ($groupby2 == 24 ) { 	
		$groupby2name	 .= 'Warehouse(Parent)';	
		$QryGroupby2Code .= 'ifNull(ParentWarehouseCode,"None")';
		$QryGroupby2Name .= 'ifNull(ParentWarehouseCode,"None")';
	}
	else if ($groupby2 == 8 ) { 	
		$groupby2name	 .= 'Warehouse(Detail)';	
		$QryGroupby2Code .= 'ifNull(WarehouseCode,"None")';
		$QryGroupby2Name .= 'ifNull(WarehouseCode,"None")';
	}
	else if ($groupby2 == 25 )	{
		$groupby2name    .= 'Group(Parent)';
		$QryGroupby2Code .= 'ifNull(ParentGroupCode,"None")';
		$QryGroupby2Name .= 'ifNull(ParentGroupName,"None")';
	}
	else if ($groupby2 == 10 )	{
		$groupby2name    .= 'Group(Detail)';
		$QryGroupby2Code .= 'ifNull(GroupCode,"None")';
		$QryGroupby2Name .= 'ifNull(GroupName,"None")';
	}
	else if ($groupby2 == 26 )	{
		$groupby2name 	 .= 'Subgroup(Parent)';
		$QryGroupby2Code .= 'ifNull(ParentSubGroupCode,"None")';
		$QryGroupby2Name .= 'ifNull(ParentSubGroupName,"None")';
	}
	else if ($groupby2 == 11 )	{
		$groupby2name 	 .= 'Subgroup(Detail)';
		$QryGroupby2Code .= 'ifNull(SubGroupCode,"None")';
		$QryGroupby2Name .= 'ifNull(SubGroupName,"None")';
	}
	else if ($groupby2 == 12 ) {	
		$groupby2name 	 .= 'Category';
		$QryGroupby2Code .= 'ifNull(CategoryCode,"None")';
		$QryGroupby2Name .= 'ifNull(CategoryName,"None")';
	}
	else {
		$groupby2name    .= '';				
		$QryGroupby2Code .= "-1";
		$QryGroupby2Name .= "'None'";
	}
		
	
	$QryGroupby2Code .= ' As Groupby2Code, ';
	$QryGroupby2Name .= ' As Groupby2Name, ';

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


$SelectedGroupBy = "Group by:  ";
if ($groupby1 != '' ) $SelectedGroupBy = $SelectedGroupBy . $groupby1name.", ";
if ($groupby2 != '' ) $SelectedGroupBy = $SelectedGroupBy . $groupby2name.", ";
if ($groupby3 != 1 )  $SelectedGroupBy = $SelectedGroupBy . $groupby3name.", ";
$SelectedOptions = substr($SelectedGroupBy, 0, strlen($SelectedGroupBy)-2) . "<br>" . $SelectedOptions;
				
				
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 width='100%'> <hr width='100%' color='black' size='1'> </td>
</tr>
</table>
HERE;

echo "<table  border='0' width='100%' class='main' align='center' cellpadding='0' cellspacing='0'>";
 
$result = mysql_query("start transaction", $connection);
$resultvars = mysql_query("set @PorderRef = '', @AllPurchaseRef = '', @PorderDtID := '', @QtyRemain := 0.00 ", $connection);


$query = '';
$query = $query." Select Groupby1Code, Groupby1Name, Groupby2Code, Groupby2Name, Groupby3Code, Groupby3Name, ".
				" PorderReference, PorderDate, MasterCode, PurchaseReferences, PurchaseReference, ItemCode, ItemDescription, ".
				" QuantityOrdered, QuantityPurchased, QuantityRemain ".
				" From ( ";
				
$query = $query." Select PorderReference, PorderDate, MasterCode, PurchaseReferences, PurchaseReference, ".
				" ItemCode, ItemDescription, QuantityOrdered, QuantityPurchased, QuantityRemain, ";
				
$query = $query.$QryGroupby1Code . $QryGroupby1Name.
                $QryGroupby2Code . $QryGroupby2Name.
				$QryGroupby3Code . $QryGroupby3Name;
	
$query = $query." From ( ".
				" Select Porder.Porder_Reference As PorderReference, Date_Format(Porder.Porder_Date,'%d/%m/%Y') As PorderDate, ".
				" Concat(Porder.Porder_ID, '*', PorderDt.PorderDt_ID) As MasterCode,  ".
				" Porder.Supplier_Code As SupplierCode, Suppliers.Ledger_Name As SupplierName, ".
				" PorderDt.Item_Code As ItemCode, Items.Item_Description As ItemDescription, ".
				" ifNull(Costcent.Parent_Code, Costcent.Costcent_Code) As ParentCostcenterCode,Costcent.Costcent_Name As ParentCostcenterName, ".
				" Porder.Costcent_Code As CostcenterCode,Costcent.Costcent_Name As CostcenterName, ".
				" ifNull(Project.Parent_Code, Project.Project_Code) As ParentProjectCode, Project.Project_Name As ParentProjectName, ".
				" Porder.Project_Code As ProjectCode,Project.Project_Name As ProjectName, ".
				" ifNull(Grouping.Parent_Code, Items.Group_Code) As ParentGroupCode, Grouping.Group_Name As ParentGroupName, ".
				" Items.Group_Code As GroupCode,Grouping.Group_Name As GroupName, ".
				" ifNull(Subgroup.Parent_Code, Items.SubGroup_Code) As ParentSubGroupCode, Subgroup.Subgroup_Name As ParentSubGroupName, ".
				" Items.SubGroup_Code As SubGroupCode,Subgroup.Subgroup_Name As SubGroupName, ".
				" Items.Category_Code As CategoryCode, Category.Category_Name As CategoryName, ".
				" Suppliers.Project_Code As MasterProject, Suppliers.CostCenter_Code As MasterCostcent, ".
				" ifNull(Project1.Parent_Code, Suppliers.Project_Code) As ParentMasterProject, ifNull(Costcent1.Parent_Code, Suppliers.CostCenter_Code) As ParentMasterCostcent, ".
				" PorderDt.Warehouse_Code As WarehouseCode, ifNull(Warehouse1.Warehouse_Code, PorderDt.Warehouse_Code) As ParentWarehouseCode,  ".
				" QPend.Purchase_Reference As PurchaseReference, ifNull(PorderDt.PorderDt_Quantity,0) As QuantityOrdered, ifNull(QPend.QuantityUsed,0) As QuantityPurchased, ".
				//" (ifNull(PorderDt.PorderDt_Quantity,0) - ifNull(QPend.QuantityUsed,0)) As QuantityRemain, ".
				" if(@PorderRef = '' or @PorderRef != Porder.Porder_Reference, @AllPurchaseRef := ifNull(QPend.Purchase_Reference,-1), if(FIND_IN_SET(ifNull(QPend.Purchase_Reference,-1),@AllPurchaseRef) = 0, @AllPurchaseRef := concat(@AllPurchaseRef,',',ifNull(QPend.Purchase_Reference,-1)), @AllPurchaseRef)) As PurchaseReferences, ".
				" if(@PorderDtID = '' OR @PorderDtID != PorderDt.PorderDt_ID, @QtyRemain := (ifNull(PorderDt.PorderDt_Quantity,0) - ifNull(QPend.QuantityUsed,0)), @QtyRemain := @QtyRemain - ifNull(QPend.QuantityUsed,0)) AS QuantityRemain, ".
                " @PorderRef := Porder.Porder_Reference, @PorderDtID := PorderDt.PorderDt_ID ";
			   
$query = $query." From PorderDt  ".
				" Inner Join Porder on PorderDt.Porder_ID = Porder.Porder_ID ".
				" Inner Join Items on PorderDt.Item_Code = Items.Item_Code ".
				" Inner Join Suppliers on Suppliers.Ledger_Number = Porder.Supplier_Code ".
				" Left Join Grouping on Items.Group_Code = Grouping.Group_Code ".
				" Left Join SubGroup on Items.SubGroup_Code = SubGroup.SubGroup_Code ".
				" Left Join Category on Items.Category_Code = Category.Category_Code ".
				" Left Join Costcent on Costcent.Costcent_Code = Porder.Costcent_Code ".
				" Left Join Project on Project.Project_Code = Porder.Project_Code ".
				" Left Join Costcent As Costcent1 on Costcent1.Costcent_Code = Suppliers.CostCenter_Code ".
				" Left Join Project As Project1 on Project1.Project_Code = Suppliers.Project_Code ".
				" Left Join Warehouse As Warehouse1 on Warehouse1.Warehouse_Code = PorderDt.Warehouse_Code ";
						  
if ($projectexist >	0 && $FI_RepRestrictProject == 1)
  $query= $query." Inner join ( select distinct project_code from ".$_SESSION['dbLabel'].".loginstk ".
				 " 			   	where loginstk.user_account='".$_SESSION['useraccount']."' ".
				 "             	and (project_code is not null and project_code != '') ".
				 "			 ) Q1 on Porder.project_code like concat(Q1.project_code,'%')  ";
							 
if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
  $query= $query." Inner join ( select distinct costcent_code from ".$_SESSION['dbLabel'].".loginstk ".
				 "			  	where loginstk.user_account='".$_SESSION['useraccount']."' ".
				 "              and (costcent_code is not null and costcent_code != '') ".
				 "			 ) Q2 on Porder.Costcent_Code like concat(Q2.costcent_code,'%')  ";							 
						   

$query = $query." Inner Join ( Select Porder_ID, PorderDt.PorderDt_ID, PorderDt.Item_Code, Purchases.Purchase_Reference, ".
			    "              Sum(ifNull(PurchasesDt.PurchaseDt_Quantity,0)) As QuantityUsed, ".
				"              ifNull(Porderdt.PorderDt_Quantity,0) As QuantityOrdered ".
				"              From PorderDt ".
				"              Left Join PurchasesDt on (PurchasesDt.RelatedDt_ID = PorderDt.PorderDt_Id And PurchasesDt.Journal_Type = 'PI') ".
				"              Left Join Purchases on (Purchases.Purchase_ID = PurchasesDt.Purchase_ID And Purchases.Journal_Type = 'PI') ".
				"	           Where ifNull(Porderdt_OldData,0) = 0  ".
				"              And PorderDt.Supplier_Code between ifNull(".$fromsupplier.",PorderDt.Supplier_Code) and ifNull(". $tosupplier.",PorderDt.Supplier_Code) ".
				" 			   And PorderDt.Item_Code between ifNull(".$fromitem.",PorderDt.Item_Code) and ifNull(". $toitem.",PorderDt.Item_Code) ".
				"              Group by Porder_ID, PorderDt.PorderDt_ID, PorderDt.Item_Code, Purchases.Purchase_Reference ".
				"			   Having QuantityOrdered > QuantityUsed ".
				"	        ) As QPend on QPend.Porder_ID = Porder.Porder_ID And QPend.PorderDt_ID = PorderDt.PorderDt_ID And QPend.Item_Code = PorderDt.Item_Code ";



$query = $query." Where (Porder.Porder_Reference Is Not Null And Porder.Porder_Reference != '')  ".
				" And Date_format(Porder.Porder_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(Porder.Porder_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Porder.Porder_Date,'%Y-%m-%d'))  ".
				" And PorderDt.item_code between ifNull(".$fromitem.",PorderDt.item_code) and ifNull(". $toitem.",PorderDt.item_code) ".
				" And Items.group_code between ifNull(". $fromgroup .",Items.group_code) and ifNull(". $togroup.",Items.group_code)  ".
				" And ifNull(Items.SubGroup_Code,'-1') between ifNull(". $fromsubgroup .",ifNull(Items.SubGroup_Code,'-1')) and ifNull(". $tosubgroup.",ifNull(Items.SubGroup_Code,'-1'))  ".
				" And Porder.Supplier_Code between ifNull(".$fromsupplier.",Porder.Supplier_Code) and ifNull(". $tosupplier.",Porder.Supplier_Code) ".
				" And ifNull(Porder.project_code,'-1')   Like Concat(ifNull(".$fromproject.",ifNull(Porder.project_code,'-1')),'%')  ".
				" And ifNull(Porder.Costcent_Code,'-1')  Like Concat(ifNull(".$fromcostcent.",ifNull(Porder.Costcent_Code,'-1')),'%')  ".
				" And ifNull(PorderDt.warehouse_code,'-1') Like Concat(ifNull(". $fromwarehouse .",ifNull(PorderDt.warehouse_code,'-1')),'%')  ";
						  
if ($confirm == 1)
	$query= $query." And ifNull(Porder.Porder_Confirmed,0) = 1 ";
else if ($confirm == 2)
	$query= $query." And ifNull(Porder.Porder_Confirmed,0) != 1 ";
	
if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
	$query= $query." And PorderDt.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 PorderReference, ItemCode, PurchaseReference ";
$query= $query." ) Q1 ";

$query= $query." Order by PorderReference, ItemCode, PurchaseReference Desc ";
$query = $query." ) Q2 ";

$query = $query." Order by Groupby1Code, Groupby2Code, Groupby3Code, PorderReference, ItemCode, PurchaseReference Desc  ";				
	
//echo $query; exit;

$ArrayTotalByReport   = Array();
$ArrayTotalByMaster   = Array();
$ArrayTotalByGroup1   = Array();
$ArrayTotalByGroup2   = Array();
$ArrayTotalByGroup3   = Array();
$ArrayData   = Array();

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

$HeaderByCode = '';

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


$result = mysql_query($query,$connection) or die(mysql_error());
while($rows=mysql_fetch_row($result)){
	$col +=1;
	
	if($col%2==0){
		$color = "style='background-color:".$report_color1."'";
	}else{
		$color = "style='background-color:".$report_color2."'";
	}
	
	if ($HeaderByCode != '' && $HeaderByCode != $rows[8]) {
		$col =0;
		DisplayItems($ArrayData, $PorderReportMaskQty, $color);
	
	}
	
	//Group By Options Level 1 Names 
	if ( ($groupby1 != '' && ($prtdt == 1 || $groupby2 != '' || $groupby3 != 1)) && ($MainGroup1 == '' || $MainGroup1 != $rows[0])  ){
			
		$groupby1code = '';
		if ($rows[0] == -1)
		    $groupby1code = '';
		else
            $groupby1code = $rows[0];	

		if ($MainGroup1 != '')
			echo "<tr><td>&nbsp;</td></tr>";	
			   
		echo "<tr height='20'>";
		echo "   <td align='left' valign='top' class='grouplevel1' >Group by  ". $groupby1name." :&nbsp;&nbsp;". 
		         str_replace('*',' --> ',$groupby1code) . " - ". 
                 str_replace('*',' --> ',$rows[1]) ."</td>";
		echo "</tr>"; 
				
		
		$MainGroup1   = $rows[0];
		$groupby1desc = $rows[1];
		
		$MainGroup2   = '';
		$groupby2desc = '';
		$MainGroup3   = '';
		$groupby3desc = '';	
		$MasterByCode = '';
		$HeaderByCode = '';
	}
		
	//Group By Options Level 2 Names 
	if ( ($groupby2 != '' && ($prtdt == 1 || $groupby3 != 1)) && ($MainGroup2 == '' || $MainGroup2 != $rows[2])  ){
			
		$groupby2code = '';
		if ($rows[2] == -1)
		    $groupby2code = '';
		else
            $groupby2code = $rows[2];			
			   
		echo "<tr height='20'>";
		echo "   <td align='left' valign='top' class='grouplevel2'>Group by  ". $groupby2name." :&nbsp;&nbsp;". 
		         str_replace('*',' --> ',$groupby2code) ." - ".
				 str_replace('*',' --> ',$rows[3]) ."</td>";
		echo "</tr>"; 
				
						
		$MainGroup2   = $rows[2];
		$groupby2desc = $rows[3];
		
		
		$MainGroup3   = '';
		$groupby3desc = '';	
		$MasterByCode = '';
		$HeaderByCode = '';
	}
		
	//Group By Options Level 3 Names 
	if ( ($groupby3 != 1 && $prtdt == 1) && ($MainGroup3 == '' || $MainGroup3 != $rows[4])  ){
	
		$groupby3code = '';
		if ($rows[4] == -1)
		    $groupby3code = '';
		else
            $groupby3code = $rows[4];			
			   
		echo "<tr>";
		echo "   <td align='left' valign='top' class='grouplevel3'>Group by  ". $groupby3name." :&nbsp;&nbsp;". 
			         str_replace('*',' --> ',$groupby3code) ."&nbsp;&nbsp; - &nbsp;&nbsp;".
			         str_replace('*',' --> ',$rows[5]) ."</td>";
		echo "</tr>"; 
				
		echo "<tr>";
		echo "   <td align='left' valign='top'>&nbsp;</td>";
		echo "</tr>"; 
						
		$MainGroup3   = $rows[4];
		$groupby3desc = $rows[5];
		
		$MasterByCode = '';
		$HeaderByCode = '';
	}
	
	
	if ($MasterByCode == '' || $MasterByCode != $rows[6]) { 
	
		if ($MasterByCode != '')
			echo "<tr><td>&nbsp;</td></tr>";
			
		echo "<tr class='group'><td> Purchase Order: ".$rows[6]."</td></tr>";
		
		
		$MasterByCode = $rows[6];
		$HeaderByCode = '';
		
		unset($ArrayData);
	
		//Create Table Structure From Detail Items
		CreateTableDetail($rows[9]);
		
	}
	
	
	
	if ($HeaderByCode == '' || $HeaderByCode != $rows[8]) { 
		
		$HeaderByCode = $rows[8];
		
		unset($ArrayData);
		
		$ArrayData['PurchaseReferences'] = $rows[9];
		$ArrayData['ItemCode'] = $rows[11];
		$ArrayData['ItemDesc'] = $rows[12];
		$ArrayData['PorderQty'] = $rows[13];
		$ArrayData['PurchaseQty'][$rows[10]] = $rows[14];
		$ArrayData['RemainQty'] = $rows[15];
	}
	else {
		$ArrayData['PurchaseQty'][$rows[10]] = $rows[14];
	}
}



if ($HeaderByCode != '' && $HeaderByCode != $rows[8]) {

	if($col%2==0){
		$color1 = "style='background-color:".$report_color1."'";
	}else{
		$color1 = "style='background-color:".$report_color2."'";
	}
	
	DisplayItems($ArrayData, $PorderReportMaskQty, $color1);
}

echo "</table>";

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

function CreateTableDetail($PPurchaseReferences) {
	echo "<tr><td>";
	echo "<table  border='0' align='left' class='detail' style='table-layout:fixed' cellpadding='0' cellspacing='0'>";
	echo " <col width=20>
		   <col width=120>
		   <col width=300>
		   <col width=100>
		   <col width=100>";
	
	$PurchaseReferences = explode(',', $PPurchaseReferences);
	foreach ($PurchaseReferences as $PurchaseReference) {
		echo "<col width=100>";
	}
		
	echo "<tr>";
	echo "   <th align='left' valign='top'>&nbsp;</th>";
	echo "   <th align='left' valign='top'>Item Code</th>";
	echo "   <th align='left' valign='top'>Item Description</th>";
	echo "   <th align='center' valign='top'>Qty Ordered</th>";
		
		
	$PurchaseReferences = explode(',', $PPurchaseReferences);
	foreach ($PurchaseReferences as $PurchaseReference) {
		echo "   <th align='center' valign='top'>Purchase # ".str_replace('-1', '', $PurchaseReference)."</th>";
	}
		

	echo "   <th align='center' valign='top'>Qty Remaining</th>";
	echo "</tr>"; 	
	echo "</table>";	
	echo "</td></tr>";

}

function DisplayItems($PArrayData, $PPorderReportMaskQty, $color) {
	
	echo "<tr ><td>";
	echo "<table  border='0' align='left' class='detail' style='table-layout:fixed' cellpadding='0' cellspacing='0'>";
	echo " <col width=20>
		   <col width=120>
		   <col width=300>
		   <col width=100>
		   <col width=100>";
				   
	$PurchaseReferences = $PArrayData['PurchaseReferences'];
	$PurchaseReferences = explode(',', $PurchaseReferences);
	
	foreach ($PurchaseReferences as $PurchaseReference) {
		echo "<col width=100>";
	}
			
	echo "<tr $color>";
	echo "   <td align='left' valign='top'>&nbsp;</td>";
	echo "   <td align='left' valign='top' style='border-right:1px solid black;'> ". $PArrayData['ItemCode'] ."</td>";
	echo "   <td align='left' valign='top' style='border-right:1px solid black; padding-left:2px;'>". $PArrayData['ItemDesc'] ."</td>";
	echo "   <td align='right' valign='top' style='border-right:1px solid black; padding-right:2px;'>". number_format($PArrayData['PorderQty'], $PPorderReportMaskQty) ."</td>";
			
	$PurchaseReferences = $PArrayData['PurchaseReferences'];
	$PurchaseReferences = explode(',', $PurchaseReferences);
	foreach ($PurchaseReferences as $PurchaseReference) {
		echo "   <td align='right' valign='top' style='border-right:1px solid black; padding-right:2px;'>". number_format($PArrayData['PurchaseQty'][$PurchaseReference], $PPorderReportMaskQty) ."</td>";
	}
		
	echo "   <td align='right' valign='top' style='border-right:1px solid black; padding-right:2px;'>". number_format($PArrayData['RemainQty'], $PPorderReportMaskQty) ."</td>";
	echo "</tr>"; 	
	echo "</table>";	
	echo "</td></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 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;

?>