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

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

if($exp ==1){
	$filename = "Exp_RepProcurement_" . date('Ymd') . ".xls";
	header("Content-Disposition: attachment; filename=\"$filename\"");
	header("Content-Type: application/vnd.ms-excel");
} 
print <<< HERE
<HTML>
<HEAD>
<TITLE> Procurement Report </TITLE>
<META NAME="Generator" CONTENT="EditPlus">
<META NAME="Author" CONTENT="">
<META NAME="Keywords" CONTENT="">
<META NAME="Description" CONTENT="">

</HEAD>

HERE;



include("config.inc.php");
include 'function.inc.php';

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

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

$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>';

$querylog="select fi_showcostprice,FI_ItemSelling1,FI_ItemSelling2,FI_ItemSelling3,FI_ItemSelling4,FI_ItemSelling5 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);
}
 

$querysetup="select grid_color1,grid_color2, DecimalBase, DecimalBase1, DecimalBase2, report_color1, report_color2, ifNull(InventoryRMaskqty,0) As InventoryRMaskqty, Base1, Base2, Company_Name As CompanyName from setup";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultsetup)){
  extract($rows);
}

$querylog=" select ifNull(FI_RepRestrictProject,0) As FI_RepRestrictProject, ifNull(FI_RepRestrictCostcenter,0) As FI_RepRestrictCostcenter, ".
		  " ifNull(FI_RepRestrictWarehouse,0) As FI_RepRestrictWarehouse,ifnull(PrequestApproved_Level,50) As PrequestApproved_Level ".
		  " ,ifnull(PorderApproved_Level,50) As PurorderApproved_Level ".
          " from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' and Upper(Login_Schema) = Upper('".$_SESSION['dbName']."') ";
$resultlog=mysql_query($querylog) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}

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

$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]);
$todayDate = $printdate[2]."/".$printdate[1]."/".$printdate[0];

if($prostatus =='1' ) $statusStr = 'MSR Pending Approval';
if($prostatus =='2' ) $statusStr = 'LPO Pending Approval';

if($exp==1) {
	$trHeight = '20';
	
}
else { 
	$trHeight = '10';
}

$masterStyleTh= "style='font-size: 12px;font-weight: bold;font-family:arial,sans-serif;border-top:2px solid #000000;
					border-bottom:1px solid #000000;text-align:center;'";
$masterStyleTd= "style='font-size: 12px;font-weight: normal;font-family:arial,sans-serif;'";		
$masterStyleTl= "style='font-size: 9px;font-weight: normal;font-family:arial,sans-serif;'";	
$masterStyleTf= "style='font-size: 9px;font-weight: bold;font-family:arial,sans-serif;'";			

print <<< HERE
<table width="680px" border="0" align="left" >
HERE;


print <<<HERE

<tr><td>
<table class="report-master"  border="0" align="left" style='table-layout:fixed;clear:left;'>
<col width='150'><col width='40'><col width='80'><col width='80'><col width='40'><col width='80'><col width='80'><col width='80'>
	<col width='40'><col width='80'><col width='40'><col width='80'><col width='80'>
<tr height='$trHeight'><td valign='top' align='right' $masterStyleTd colspan='13'><b>Date: $todayDate</b></td></tr>
<tr>
  <td  valign='top' align='center' width=100% colspan='13' $masterStyleTd><h2>Procurement</h2></td>
</tr>
<tr ><td colspan='13' $masterStyleTd><hr></td></tr>
</table></td></tr>

HERE;



			
echo "<tr><td><table  border='0' width='900' class='report-master' style='clear:left;' align='left' cellpadding='0' cellspacing='0'>";
echo"<col width='150'><col width='40'><col width='80'><col width='80'><col width='40'><col width='80'><col width='80'><col width='80'>
	<col width='40'><col width='80'><col width='40'><col width='80'><col width='80'>";
echo "<tr height='$trHeight'><td colspan='3' $masterStyleTd><b>From Date:</b> $fromdate</td><td colspan='4' $masterStyleTd><b>To Date:</b> $todate</td>
		  <td colspan='4' $masterStyleTd><b>From Project:</b> $fromproject</td><td colspan='3' $masterStyleTd><b>To Project:</b> $toproject</td></tr>";			
echo "<tr height='$trHeight'><td colspan='3' $masterStyleTd><b>Status:</b> $statusStr</td></tr>";
echo "</table></td></tr>";
echo "<tr height='15'><td></td></tr>";
if (!empty($fromdate))   $fromdate = explodeword($fromdate);
if (!empty($todate))     $todate = explodeword($todate); 
if (empty($existqty))     $existqty = 0;

$fromproject   = GetSQLValueString($fromproject, 'text');
$toproject     = GetSQLValueString($toproject, 'text');

$fromitem   = GetSQLValueString($fromitem, 'text');
$toitem     = GetSQLValueString($toitem, 'text');

$fromsupplier   = GetSQLValueString($fromsupplier, 'text');
$tosupplier     = GetSQLValueString($tosupplier, 'text');


$fromdate  = GetSQLValueString($fromdate, 'date');
$todate    = GetSQLValueString($todate, 'date');

echo "<tr ><td><table  border='1' width='1100' class='report-master' style='clear:left;' align='left' cellpadding='0' cellspacing='0'>";
echo"<col width='200'>
     <col width='40'>
	 <col width='60'>
	 <col width='70'>
	 <col width='40'>
	 <col width='60'>
	 <col width='60'>
	 <col width='140'>
	 <col width='70'>
	<col width='30'>
	<col width='70'>
	<col width='30'>
	<col width='70'>
	<col width='120'>";

echo "<tr><th $masterStyleTh>Site</th>
<th $masterStyleTh>MSR#</th>
<th $masterStyleTh>date</th>
<th $masterStyleTh>Status</th>
<th $masterStyleTh>PO#</th>
<th $masterStyleTh>Date</th>
<th $masterStyleTh>ETD</th>
<th $masterStyleTh>PO Total</th>
<th $masterStyleTh>Status</th>";
echo "<th $masterStyleTh>GRN#</th>
<th $masterStyleTh>Grn Date</th>
<th $masterStyleTh>Pur.#</th>
<th $masterStyleTh>Pur.Date</th>
<th $masterStyleTh>Supplier</th></tr>";



$query = "select distinct project1.project_code,project1.project_name, prequest.PRqst_id as PRqst_id,prequest.prqst_reference
as prqst_reference,date_format(prequest.prqst_date,'%d/%m/%Y') as prqst_date, if(ifnull(Prqst_Cancelled,0)=1, 'Cancelled',
if(ifnull(Prqst_Confirmed,0)=0, 'Working On',if(PRqstDt_Quantity=0,'Transferred', if(ifnull(PRqstDt_status,'x')='x',
'Pend.App', if(ifnull(PRqstDt_status,'x')='a' or ifnull(PRqstDt_status,'x')='u', 'Approved',
if(ifnull(PRqstDt_status,'x')='r', 'Rejected', '')))))) as PRqstDtStatus , porder.porder_reference as porder_reference,
date_format(porder.porder_date,'%d/%m/%Y') as porderdate, date_format(porder.Porder_RequestDate,'%d/%m/%Y') as Porder_DeliveryDate,
 if(ifnull(porderdt.relateddt_id,0)=0, '',
 if(ifnull(porderdt_grnrejected,0)=1,'GRN Rejected',
 if(ifnull(porderdt_approvedstatus,'x')='x', 'Pend.App',
 if(ifnull(porderdt_approvedstatus,'x')='a', 'Approved',
 if(ifnull(porderdt_approvedstatus,'x')='r', 'Rejected', ''))))) as PorderDtStatus,
 GrnPurchases.Purchase_Reference,if(GrnPurchasesdt.Purchase_id is not null,'Received','') as GrnPurchaseStatus,
 Purchases.Purchase_Reference,if(Purchases.Purchase_id is not null,'Done','') as PurchaseStatus,
 if(PaymentRqst_Confirmed=1,'confirmed','') as PaymentRqstStatus,left(Grnpurchases.purchase_date,10) as Grndate,
 left(Purchases.purchase_date,10) as PurDate,Porder_total-porder_discountamount+porder_vat as PorderTotal,
 porder.currency_code,ledger_name from prequest
 left join prequestdt on prequestdt.prqst_id=prequest.prqst_id
 left join project on project.project_code = prequest.project_code
 left join project project1 on project1.project_code = prequestdt.project_code
 left join porderdt on porderdt.RelatedDt_ID = prequestdt.PRqstDt_id
 left join porder on porder.porder_id = porderdt.porder_id
 left join suppliers on Suppliers.ledger_number=Porder.supplier_code
 left join GrnPurchasesdt on GrnPurchasesdt.relateddt_id = porderdt.PorderDt_id
 left join GrnPurchases on GrnPurchases.Purchase_id = GrnPurchasesdt.Purchase_id
 left join Purchasesdt on Purchasesdt.relateddt_id = GrnPurchasesdt.PurchaseDt_id
 left join Purchases on Purchases.Purchase_id = Purchasesdt.Purchase_id
 left join PaymentRqst on PaymentRqst.Porder_Reference = porder.Porder_reference
 where prequest.prqst_id = prequestdt.prqst_id  and date_format(prequest.prqst_date,'%Y-%m-%d')
			between ifnull($fromdate, date_format(prequest.prqst_date,'%Y-%m-%d'))
			and ifnull($todate, date_format(prequest.prqst_date,'%Y-%m-%d'))
			and ifNull(project1.project_code,'-1') between ifNull(".$fromproject.",ifNull(project1.project_code,'-1')) and ifNull(". $toproject.",ifNull(project1.project_code,'-1')) ";
			
$query = $query." and ifNull(prequestdt.item_code,'-1') between ifNull(".$fromitem.",ifNull(prequestdt.item_code,'-1')) and ifNull(". $toitem.",ifNull(prequestdt.item_code,'-1')) ";			

$query = $query." and ifNull(porderdt.supplier_code,'-1') between ifNull(".$fromsupplier.",ifNull(porderdt.supplier_code,'-1')) and ifNull(". $tosupplier.",ifNull(porderdt.supplier_code,'-1')) ";			

	if($prostatus == '1') 	
$query = $query." and ifnull(Prqst_Confirmed,0) = 1 and ((ifnull(PRqstDt_status,'x') = 'x' and 1 = 1) or (ifnull(PRqstDt_status,'x') = 'a' and PRqstDt_approvedlevel > $PrequestApproved_Level)) ";
else if($prostatus == '2' && $Module_PorderConfirm != 1) 	
$query = $query." and ((ifnull(porderdt_approvedstatus,'x') = 'x' and 1 = 1) or (ifnull(porderdt_approvedstatus,'x') = 'a' and porderdt_approvedlevel > $PurorderApproved_Level)) ";
else if($prostatus == '2' && $Module_PorderConfirm == 1) 	
$query = $query." and ifnull(Porder_Confirmed,0) = 1 and ((ifnull(porderdt_approvedstatus,'x') = 'x' and 1 = 1) or (ifnull(porderdt_approvedstatus,'x') = 'a' and porderdt_approvedlevel > $PurorderApproved_Level))  ";

$query = $query." order by prequest.PRqst_reference,prequestdt.PRqstDt_id;";


//echo $query;
//exit;
$result = mysql_query($query,$connection);
while($rows = mysql_fetch_row($result)){
	if($col%2==0){
			$color = "style='background-color:".$report_color1."'";
		}else{
			$color = "style='background-color:".$report_color2."'";
		}
		
echo "<tr height='20' $color>
<td $masterStyleTl>$rows[1]</td>
<td align='center' $masterStyleTd>$rows[3]</td>
<td align='center' $masterStyleTd>$rows[4]</td>
<td align='center' $masterStyleTd>$rows[5]</td>
<td align='center' $masterStyleTd>$rows[6]</td>
<td align='center' $masterStyleTd>$rows[7]</td>
<td align='center' $masterStyleTd>$rows[8]</td>
<td align='right' $masterStyleTd>".number_format($rows[17], $DecimalBase)." $rows[18] </td>
<td align='center' $masterStyleTd>$rows[9]</td>";
echo "<td align='center' $masterStyleTd>$rows[10]</td>
<td align='center' $masterStyleTd>$rows[15]</td>
<td align='center' $masterStyleTd>$rows[12]</td>
<td align='center' $masterStyleTd>$rows[16]</td>
<td align='center' $masterStyleTl>$rows[19]</td></tr>";


if ($rows[6]!='' ) 
$varporder=$rows[6];
else $varporder=0;

if ($rows[10]!='' ) 
$vargrn=$rows[10];
else $vargrn=0;

if ($rows[12]!='' ) 
$varpurchase=$rows[12];
else $varpurchase=0;





if ($existqty==1) {

$querydetail=" select prequestdt.item_code,items.item_description,prequestdt.item_description, 
if (PRqstDt_Quantity=0,PRqstDt_OQuantity,PRqstDt_Quantity) as Quantity,
if(ifnull(Prqst_Cancelled,0)=1, 'Cancelled', if(ifnull(Prqst_Confirmed,0)=0, 'Working On',
if(PRqstDt_Quantity=0,'Transferred', if(ifnull(PRqstDt_status,'x')='x', 'Pend.App',
if(ifnull(PRqstDt_status,'x')='a' or ifnull(PRqstDt_status,'x')='u', 'Approved',
if(ifnull(PRqstDt_status,'x')='r', 'Rejected', '')))))) as PRqstDtStatus,porder_reference as PORef,
grnpurchases.purchase_reference as GRNRef,purchases.purchase_reference as PurRef,
porderdt.porderdt_quantity,GrnPurchasesdt.purchasedt_quantity
from prequestdt inner join prequest on prequest.prqst_id=prequestdt.prqst_id
   left join porderdt    on porderdt.RelatedDt_ID = prequestdt.PRqstDt_id
   left join porder on porder.porder_id = porderdt.porder_id
   left join GrnPurchasesdt on GrnPurchasesdt.relateddt_id = porderdt.PorderDt_id
   left join GrnPurchases on GrnPurchases.Purchase_id = GrnPurchasesdt.Purchase_id
   left join Purchasesdt on Purchasesdt.relateddt_id =  GrnPurchasesdt.PurchaseDt_id
   left join Purchases on Purchases.Purchase_id = Purchasesdt.Purchase_id
left join items on items.item_code=prequestdt.item_code
where prequestdt.prqst_id=$rows[2] 
having  PRqstDtStatus='$rows[5]' ";

if($varporder != '0') 	
$querydetail = $querydetail." and PORef=$varporder ";
else $querydetail = $querydetail." and PORef is null ";

if($vargrn != '0') 	
$querydetail = $querydetail." and GRNRef=$vargrn ";
else $querydetail = $querydetail." and GRNRef is null ";

if($varpurchase != '0') 	
$querydetail = $querydetail." and PurRef=$varpurchase ";
else $querydetail = $querydetail." and PurRef is null ";

//echo $querydetail;exit;

$resultdetail = mysql_query($querydetail,$connection);
while($rowsdetail = mysql_fetch_row($resultdetail)){

if ($rowsdetail > 0) {			  
			echo "<tr><td colspan='14'>";	
			echo "<table border=1 width='1100' cellspacing=1 class='subdetail' style='table-layout:fixed;' >
				  <col width=150>
				  <col width=325>
				  <col width=325>
				  <col width=100>
				  <col width=100>
				  <col width=100>";
			
			echo "<tr>";
			echo "   <th align='left' $masterStyleTf>Item Code</th>";
			echo "	 <th align='left' $masterStyleTf>Description</th>";
			echo "	 <th align='left' $masterStyleTf>New Item</th>";
			echo "	 <th align='center' $masterStyleTf>MSR Qty</th>";
			echo "	 <th align='center' $masterStyleTf>PO Qty</th>";
			echo "	 <th align='center' $masterStyleTf>GRN Qty</th>";
			echo "</tr>";	
			
			$resultdetail=mysql_query($querydetail,$connection) or die(mysql_error());
			while($rowsdetail=mysql_fetch_array($resultdetail)){
				extract($rowsdetail);
				
			echo "<tr>";
			echo "   <th align='left' $masterStyleTl>$rowsdetail[0]</th>";
			echo "   <th align='left' $masterStyleTl>$rowsdetail[1]</th>";
			echo "	 <th align='left' $masterStyleTl>$rowsdetail[2]</th>";
			echo "	 <th align='center' $masterStyleTl>$rowsdetail[3]</th>";
			echo "	 <th align='center' $masterStyleTl>$rowsdetail[8]</th>";
			echo "	 <th align='center' $masterStyleTl>$rowsdetail[9]</th>";
			echo "</tr>";
				
			}	
			
			echo"</table></td></tr>";
		}

}





}



}



echo "</table></td></tr>";
echo "</table>";
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;
}


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

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

?>