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

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


include("config.inc.php");

//Set Time Execution For the Report
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");


$querysetup="select DecimalBase, DecimalBase1, DecimalBase2, Base1, Base2, 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 ".$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]);

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

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

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

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

//Column Title Format  
$columnTitleFormat =& $workbook->addFormat(array('bold'=>1,
  'top'=>1,
  'bottom'=>1 ,
  'size'=>15,
  'color'=>0x0A,
  ));
  
 
  
// Regular Format
$regularleftFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ));
$regularleftFormat1 =& $workbook->addFormat(array('size'=>10,
  'align'=>'left'  ));  
$regularrightFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ));  
$regularrightFormat1 =& $workbook->addFormat(array('size'=>10,
  'align'=>'right'  ));    
  
$TitleFormat =& $workbook->addFormat(array('size'=>11,
  'align'=>'left',
  'bold'=>1
  ));

$boldleftFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ,
  'valign'=>'top'  ,
  'bold'=>1
  ));  
$boldrightFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ,
  'bold'=>1
  )); 
$boldcenterFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'center'  ,
  'valign'=>'top'  ,
  'bold'=>1
  ));    
  
$grouplevel1Format =& $workbook->addFormat(array('size'=>10,
  'align'=>'left',
  'bold'=>1,
  'color'=>0x0C
  )); 
$grouplevel2Format =& $workbook->addFormat(array('size'=>10,
  'align'=>'left',
  'bold'=>0,
  'color'=>0x0A
  )); 
$grouplevel3Format =& $workbook->addFormat(array('size'=>9,
  'align'=>'left',
  'italic'=>1,
  'color'=>0x11
  ));   
$grouplevel1totalFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'right',
  'bold'=>1,
  'color'=>0x0C
  ));  
$grouplevel2totalFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'right',
  'bold'=>0,
  'color'=>0x0A
  ));  
$grouplevel3totalFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right',
  'italic'=>1,
  'color'=>0x11
  ));      

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

$SelectedOptions = "Filter by: \n";
if ($fromdate != '')      $SelectedOptions .= "-> Date From: ". $fromdate . "  To: ". $todate ."\n";	
if ($displaycur != '')    $SelectedOptions .= "-> Currency: ". $displaycur ."\n";	
if ($fromitem != '')      $SelectedOptions .= "-> Item From: ". $fromitem . "  To: ". $toitem ."\n";
if ($fromgroup != '')     $SelectedOptions .= "-> Group From: ". $fromgroup . "  To: ". $togroup ."\n";
if ($fromsubgroup != '')  $SelectedOptions .= "-> SubGroup From: ". $fromsubgroup . "  To: ". $tosubgroup ."\n";
if ($fromproject != '')   $SelectedOptions .= "-> Project From: ". $fromproject . "\n";
if ($fromcostcent != '')  $SelectedOptions .= "-> Costcent From: ". $fromcostcent . "\n";
if ($fromsupplier != '')  $SelectedOptions .= "-> Supplier From: ". $fromsupplier . "  To: ". $tosupplier ."\n";
if ($fromwarehouse != '') $SelectedOptions .= "-> Warehouse From: ". $fromwarehouse . "\n";

  
if (!empty($fromdate)) $fromdate = explodeword($fromdate);
if (!empty($todate))   $todate = explodeword($todate);  
   
$fromitem      = GetSQLValueString($fromitem, 'text');
$toitem        = GetSQLValueString($toitem, 'text');
$fromproject   = GetSQLValueString($fromproject, 'text');
$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;
				
				
//Display Report Header
$worksheet->write(1,0,$CompanyName, $TitleFormat);
$worksheet->write(1,2,"Detailed Pending Porder", $TitleFormat);
               
$worksheet->write(1,5,"Printed Date:", $boldleftFormat);
$worksheet->write(1,6,$printdate[2]."/".$printdate[1]."/".$printdate[0], $regularleftFormat);
$worksheet->write(2,5,"Time:", $boldleftFormat);		
$worksheet->write(2,6,$dt[1]." ".$dt[2], $regularleftFormat);
  
//Displaying Selected Options	
// Merge cells from row 3, col 0 to row 3, col 0
$worksheet->setMerge(3, 0, 8, 6);
$worksheet->write(3,0,$SelectedOptions, $seloptionFormat);  


$RowNum    = 10;
$ColumnNum = 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 ".$HTTP_SESSION_VARS['dbLabel'].".loginstk ".
				 " 			   	where loginstk.user_account='".$HTTP_SESSION_VARS['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 ".$HTTP_SESSION_VARS['dbLabel'].".loginstk ".
				 "			  	where loginstk.user_account='".$HTTP_SESSION_VARS['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 ".$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 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)){
	
	if ($HeaderByCode != '' && $HeaderByCode != $rows[8]) {
		$StyleClass  = $regularleftFormat;
		$StyleClass1 = $regularrightFormat;
		DisplayItems($worksheet, $ArrayData, $PorderReportMaskQty, $StyleClass, $StyleClass1, $RowNum, $ColumnNum);
		
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 1;
		$ColumnNum = 0;
	}
	
	//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 != '') {
			$RowNum += 1;
			$ColumnNum = 0;	
		}
			   
		$worksheet->write($RowNum, $ColumnNum, "Group by  ".$groupby1name, $grouplevel1Format);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, str_replace('*',' --> ',$groupby1code)." - ".str_replace('*',' --> ',$rows[1]), $grouplevel1Format);		
				
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 1;
		$ColumnNum = 0;	
		
		
		$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];			
			   
		$worksheet->write($RowNum, $ColumnNum, "Group by  ".$groupby2name, $grouplevel2Format);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, str_replace('*',' --> ',$groupby2code)." - ".str_replace('*',' --> ',$rows[3]), $grouplevel2Format);		
				
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 1;
		$ColumnNum = 0;
		
								
		$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];			
			   
		$worksheet->write($RowNum, $ColumnNum, "Group by  ".$groupby3name, $grouplevel3Format);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, str_replace('*',' --> ',$groupby3code)." - ".str_replace('*',' --> ',$rows[5]), $grouplevel3Format);
				
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 2;
		$ColumnNum = 0;
						
		$MainGroup3   = $rows[4];
		$groupby3desc = $rows[5];
		
		$MasterByCode = '';
		$HeaderByCode = '';
	}
	
	
	if ($MasterByCode == '' || $MasterByCode != $rows[6]) { 
	
		if ($MasterByCode != '') {
			$RowNum += 1;
			$ColumnNum = 0;	
		}
		
		$worksheet->write($RowNum, $ColumnNum, "Purchase Order: ".$rows[6], $boldleftFormat);
		$ColumnNum += 1;
			
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 1;
		$ColumnNum = 0;
		
		$MasterByCode = $rows[6];
		$HeaderByCode = '';
		
		unset($ArrayData);
	
		//Create Table Structure From Detail Items
		$StyleClass = $boldcenterFormat;
		CreateTableDetail($worksheet, $rows[9], $StyleClass, $RowNum, $ColumnNum);
		
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 1;
		$ColumnNum = 0;
	}
	
	
	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]) {
	$StyleClass = $regularleftFormat;
	$StyleClass1 = $regularrightFormat;
	DisplayItems($worksheet, $ArrayData, $PorderReportMaskQty, $StyleClass, $StyleClass1, $RowNum, $ColumnNum);
	
	//Increment the Rows Counter By 1 and Reset the Columns Counter
	$RowNum += 1;
	$ColumnNum = 0;
}



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

function CreateTableDetail($worksheet, $PPurchaseReferences, $PStyleClass, $PRowNum, $PColumnNum) {

	$worksheet->setColumn ($PColumnNum, $PColumnNum, 20);
	$worksheet->write($PRowNum,$PColumnNum,"Item Code", $PStyleClass);
	$PColumnNum += 1;
	$worksheet->setColumn ($PColumnNum, $PColumnNum, 40);
	$worksheet->write($PRowNum,$PColumnNum,"Item Description", $PStyleClass);
	$PColumnNum += 1;
	$worksheet->setColumn ($PColumnNum, $PColumnNum, 15);
	$worksheet->write($PRowNum,$PColumnNum,"Qty Ordered", $PStyleClass);
	$PColumnNum += 1;
		
	$PurchaseReferences = explode(',', $PPurchaseReferences);
	foreach ($PurchaseReferences as $PurchaseReference) {
		$worksheet->setColumn ($PColumnNum, $PColumnNum, 15);
		$worksheet->write($PRowNum,$PColumnNum,"Purchase # ".str_replace('-1', '', $PurchaseReference), $PStyleClass);
		$PColumnNum += 1;
	}
		
	$worksheet->setColumn ($PColumnNum, $PColumnNum, 15);
	$worksheet->write($PRowNum,$PColumnNum,"Qty Remaining", $PStyleClass);
	$PColumnNum += 1;
	
}

function DisplayItems($worksheet, $PArrayData, $PPorderReportMaskQty, $PStyleClass, $PStyleClass1, $PRowNum, $PColumnNum) {
		
	$worksheet->write($PRowNum,$PColumnNum,$PArrayData['ItemCode'], $PStyleClass);
	$PColumnNum += 1;
	$worksheet->write($PRowNum,$PColumnNum,$PArrayData['ItemDesc'], $PStyleClass);
	$PColumnNum += 1;
	$worksheet->write($PRowNum,$PColumnNum,number_format($PArrayData['PorderQty'], $PPorderReportMaskQty), $PStyleClass1);
	$PColumnNum += 1;
	
	$PurchaseReferences = $PArrayData['PurchaseReferences'];
	$PurchaseReferences = explode(',', $PurchaseReferences);
	foreach ($PurchaseReferences as $PurchaseReference) {
		$worksheet->write($PRowNum,$PColumnNum,number_format($PArrayData['PurchaseQty'][$PurchaseReference], $PPorderReportMaskQty), $PStyleClass1);
		$PColumnNum += 1;
	}
		
	$worksheet->write($PRowNum,$PColumnNum,number_format($PArrayData['RemainQty'], $PPorderReportMaskQty), $PStyleClass1);
	$PColumnNum += 1;
}

function current_server_time($connection){

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

  return $now;
}

function explodeword($date)
{

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

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

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

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

$workbook->close();

?>