<?php
include_once('../../hdr_general.php');
include_once('../../lib/lib_general.php');
include_once('../../dbincludes.php');
include_once('../../libincludes.php');
include_once('../../classincludes.php');
// start include db
include_once("../../db/db_config.php");

$con = mysql_connect($db_config_pms['server'],$db_config_pms['username'],$db_config_pms['password']);

if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db($db_config_pms['db'], $con);
// end include db
?>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Sub-con Report</title>
<link href="css_setting.css" rel="stylesheet" type="text/css" />
</head>



<style type="text/css">
<!--
.style1 {
	color: #0066CC;
	font-weight: bold;
}
.style2 {font-size: 24px}
.style10 {font-size: 14px; color: #FFFFFF; font-weight: bold; }
.style11 {
	color: #666666;
	font-weight: bold;
	font-size: 12px;
}
-->
</style>

<!--<form method="post" name="frmMain">-->
<?php
		
		$varSupplier = $_REQUEST[varsupplier];
		$varSubCon = $_REQUEST[varsubconkey];
		$varUserID = $_REQUEST[userid];	
						
		//convert date
		$varTarikhMula = $_REQUEST['tarikhMula'];
		$varTarikhAkhir = $_REQUEST['tarikhAkhir'];
		
		$unixTarikhMula=converttounix($varTarikhMula);
		$unixTarikhAkhir=converttounix($varTarikhAkhir);	
		//end convert date	
		
		//set function for time default zone
		date_default_timezone_set('Asia/Kuala_Lumpur');
		$tarikh=date("d/m/Y");	
		
		//get project primary key
		$selProKey = mysql_query("select * from project_details where projectname = '$pubprojectname'");		
		while($row = mysql_fetch_array($selProKey))
		{			
			$varProKey = $row['projectpkey'];				
		}
				
		//get user
		$selUser = mysql_query("select * from staff_profiles where staff_userpkey ='$varUserID'");						
		while($row = mysql_fetch_array($selUser))
		{	
			$varUserName =  $row['staff_name'];		
			
		}
			
			// start include cms db			
			$con2 = mysql_connect($db_config_cms['server'],$db_config_cms['username'],$db_config_cms['password']);			
			if (!$con2)
			  {die('Could not connect: ' . mysql_error());}			
			mysql_select_db($db_config_cms['db'], $con);
			// end include cms  db
			
			 $selSubCon = mysql_query("select * from settings_subcontractors where subconmasterpkey ='$varSubCon'");		
			while($row = mysql_fetch_array($selSubCon))
			{	
				$varSubConName =  $row['subconname'];				
			}
		
?>

<style type="text/css" media="print,screen" >
	th {
	    font-family:Arial;
	    color:black;
	    background-color:lightgrey;
	}
	thead {
	    display:table-header-group;
	}
	tbody {
	    display:table-row-group;
	}
	
	div#footer{
  position:absolute;
  bottom:0;
  left:0;
  width:100%;
  height:footer-<length>;
 }
</style>

<table width="870" border="0">
  <tr>
    <td width="128" height="23">&nbsp;</td>
    <td width="10">&nbsp;</td>
    <td colspan="3"><div align="center"><img src="../../gfx/zikayheader.png" width="225" height="82" /></div></td>
  </tr>
  <tr>
    <td height="23">&nbsp;</td>
    <td>&nbsp;</td>
    <td colspan="3"><div align="left" class="style1 style2" style="border-bottom:#000000 medium">
      <div align="center">SUB-CON MATERIAL REPORT</div>
    </div></td>
  </tr>
  <tr>
    <td height="23">&nbsp;</td>
    <td>&nbsp;</td>
    <td colspan="3">&nbsp;</td>
  </tr>
  <tr>
    <td height="23">PROJECT TITLE</td>
    <td>:</td>
    <td colspan="3"><?php echo  $pubprojectname; ?></td>
  </tr>
  <tr>
    <td height="23">SUBCON NAME </td>
    <td>:</td>
    <td colspan="3"><?php echo $varSubConName;?></td>
  </tr>
  
  <tr>
    <td height="23">DATE</td>
    <td>:</td>
    <td width="100"><?php echo $_REQUEST[tarikhMula]?></td>
    <td width="41">To</td>
    <td width="569"><?php echo $_REQUEST[tarikhAkhir]?></td>
  </tr>
</table>

<br>
<table width="1079" border cellspacing="0.5">
<thead>
	<tr>
		<th bgcolor="#0000FF"><span class="style16">No</span></th>
		<th bgcolor="#0000FF"><span class="style16">SUPPLIER</span></th>
		<th bgcolor="#0000FF"><span class="style16">PO NO</span></th>
		<th bgcolor="#0000FF"><span class="style16">PO DATE</span></th>
		<th bgcolor="#0000FF"><span class="style16">PO AMOUNT(RM)</span></th>
		<th bgcolor="#0000FF"><span class="style16">INVOICE NO </span></th>
		<th bgcolor="#0000FF"><span class="style16">INVOICE DATE</span></th>
		<th bgcolor="#0000FF"><span class="style16">AMOUNT(RM)</span></th>
	</tr>
  </thead>
 <tbody>
 
 <?php 
 			// start include pms db			
			$con2 = mysql_connect($db_config_pms['server'],$db_config_pms['username'],$db_config_pms['password']);			
			if (!$con2)
			  {die('Could not connect: ' . mysql_error());}			
			mysql_select_db($db_config_pms['db'], $con);
			// end include pms  db
			
			
			if ($varSubCon =='' and $_REQUEST[tarikhMula]!='')
			{
				$selCountPO = "select PO.*,PO.po_no as pono, PO.po_supplierpkey as supplierKey, MRO.mro_subcons as subConKey from po_details PO, mro_details MRO where PO.po_projectpkey = '$varProKey' and PO.po_mropkey = MRO.mro_pkey and MRO.mro_subcons <> '' and PO.po_date >= '$unixTarikhMula'  and PO.po_date <= '$unixTarikhAkhir' and PO.po_cancel <> 1 order by PO.po_date";				
 			}
			elseif ($varSubCon =='' and $_REQUEST[tarikhMula] =='')
			{
				$selCountPO = "select PO.*,PO.po_no as pono, PO.po_supplierpkey as supplierKey, MRO.mro_subcons as subConKey from po_details PO, mro_details MRO where PO.po_projectpkey = '$varProKey' and PO.po_mropkey = MRO.mro_pkey and MRO.mro_subcons <> '' and PO.po_cancel <> 1 order by PO.po_date";
				//echo $selCountPO;exit;				
 			}
			elseif ($_REQUEST[varsubconkey] !='' and $_REQUEST[tarikhMula]!='')
			{
				$selCountPO = "select PO.*,PO.po_no as pono, PO.po_supplierpkey as supplierKey, MRO.mro_subcons as subConKey from po_details PO, mro_details MRO where PO.po_projectpkey = '$varProKey' and PO.po_mropkey = MRO.mro_pkey and MRO.mro_subcons = '$varSubCon' and PO.po_date >= '$unixTarikhMula'  and PO.po_date <= '$unixTarikhAkhir' and PO.po_cancel <> 1 order by PO.po_date";				
 			}
			elseif ($_REQUEST[varsubconkey] !='' and $_REQUEST[tarikhMula]=='')
			{
			 	$selCountPO = "select PO.*,PO.po_no as pono, PO.po_supplierpkey as supplierKey, MRO.mro_subcons as subConKey from po_details PO, mro_details MRO where PO.po_projectpkey = '$varProKey' and PO.po_mropkey = MRO.mro_pkey and MRO.mro_subcons = '$varSubCon' and PO.po_cancel <> 1 order by PO.po_date";
			}
			 //print $selCountPO;exit;
			 $selTotalPO = mysql_query($selCountPO);
			 
			 $i=0;	
		while($row = mysql_fetch_array($selTotalPO))
		{			
			$i = $i +1;
			//include db pms
			include_once("../../db/db_config.php");
			$con = mysql_connect($db_config_pms['server'],$db_config_pms['username'],$db_config_pms['password']);			
			if (!$con)
			  {
			  die('Could not connect: ' . mysql_error());
			  }			
			mysql_select_db($db_config_pms['db'], $con);						
			//end include db pms
			
			$varPOKey = $row['po_pkey'];
			$varSubConKey = $row['subConKey'];		
			$varSupplierKey = $row['supplierKey'];	
			$varPONO = $row['pono'];
			$varPODate = unconverttounixslash($row['po_date']);	
			
			//echo $varPOKey;
			$varTotalPO = 0;
			//get po amount
			 $selTotalPO1 = mysql_query("select * from po_items where item_popkey = '$varPOKey'");		
			while($row = mysql_fetch_array($selTotalPO1))
			{	
				$varSubTotalPO = $row['item_quantity'] * $row['item_rate'];				
				$varTotalPO = $varTotalPO + $varSubTotalPO;
				
				//echo $varTotalPO;
//				echo "<br>";
			}	
			
			
			$varGrandTotalPO = $varGrandTotalPO + $varTotalPO;
			
			//====== get subconname
			// start include db			
			$con2 = mysql_connect($db_config_cms['server'],$db_config_cms['username'],$db_config_cms['password']);			
			if (!$con2)
			  {die('Could not connect: ' . mysql_error());}			
			mysql_select_db($db_config_cms['db'], $con);
			// end include db
			
			 $selSubCon = mysql_query("select * from settings_suppliers where supplier_pkey ='$varSupplierKey'");		
			while($row = mysql_fetch_array($selSubCon))
			{	
				$varSupplierName =  $row['supplier_name'];				
			}							
			
			//====== end get subconname
			
			
			//===== check PO len ======
			if (strlen($varPONO) ==1 )
			{
				$varPONO = 'PO000'.$varPONO;
			}
			else if (strlen($varPONO) ==2 )
			{
				$varPONO = 'PO00'.$varPONO;
			}
			else if (strlen($varPONO) ==3 )
			{
				$varPONO = 'PO0'.$varPONO;
			}
			else if (strlen($varPONO) ==4 )
			{
				$varPONO = 'PO'.$varPONO;
			}			
			//======end check PO len ===
			
			//change amount format 
			//$varTotalPO = number_format($varTotalPO,2,'.','');
			$varTotalPO = number_format($varTotalPO,2);
						
			
			//get invoice data		
			
			//include db pms
			include_once("../../db/db_config.php");
			$con = mysql_connect($db_config_pms['server'],$db_config_pms['username'],$db_config_pms['password']);			
			if (!$con)
			  {
			  die('Could not connect: ' . mysql_error());
			  }			
			mysql_select_db($db_config_pms['db'], $con);						
			//end include db pms
			
			//clear kan variable
			$j = 0;
			$varInvoiceNo =  0;
			$varInvoiceDate = 0;				
			$varSubTotalInvoice = 0;
                        					

			$selInvoice = mysql_query("select * from invoice_items where item_popkey = '$varPOKey' order by item_invoiceno");		
			while($row = mysql_fetch_array($selInvoice))
			{	
				
				$j = $j+1;
				$varInvoiceNo =  $row['item_invoiceno'];
				$varInvoiceDate = unconverttounixslash($row['item_invoicedate']);				
				$varSubTotalInvoice = $row['item_quantity'] * $row['item_rate'];
                                				
				$varTotalInvoice = $varTotalInvoice + $varSubTotalInvoice;
				$varTotalInvoice = number_format($varTotalInvoice,2);				
				$varSubTotalInvoice = number_format($varSubTotalInvoice,2);
                                				
				$varSubTotalInvoice1 = $row['item_quantity'] * $row['item_rate'];
				$varGrandTotalInvoice = $varGrandTotalInvoice + $varSubTotalInvoice1;
                $varSubTotalInvAll =  $varSubTotalInvAll + $varSubTotalInvoice1;
				
				//view utk 1st row
				if ($j == 1){				
					echo "<tr>";
						echo "<td height='30'><div align='left' class='style11'>$i</td>";
						echo "<td><div align='left' class='style11'>$varSupplierName</td>";
						echo "<td><div align='right' class='style11'>$varPONO</td>";
						echo "<td><div align='right' class='style11'>$varPODate</td>";
						echo "<td><div align='right' class='style11'>$varTotalPO </td>";
						
						if ($varInvoiceNo !=''){echo "<td><div align='right' class='style11'>$varInvoiceNo</td>";}
						else{echo "<td>&nbsp;</td>";}
						
						if ($varInvoiceDate !=''){echo "<td><div align='right' class='style11'>$varInvoiceDate</td>";}
						else{echo "<td>&nbsp;</td>";}
						
						if ($varTotalInvoice !=''){echo "<td><div align='right' class='style11'>$varSubTotalInvoice</td>";}
						else{echo "<td>&nbsp;</td>";}						
					
					echo "</tr>";
					//view utk 1st row			
					//$varGrandTotalInvoice = $varGrandTotalInvoice + $varSubTotalInvoice;
					//echo $varGrandTotalInvoice;
				}
				
				if ($j > 1)
				{			
				echo "<tr>";
					echo "<td height='30'><div align='left' class='style11'>&nbsp;</td>";
					echo "<td><div align='left' class='style11'>&nbsp;</td>";
					echo "<td><div align='left' class='style11'>&nbsp;</td>";
					echo "<td><div align='left' class='style11'>&nbsp;</td>";
					echo "<td><div align='left' class='style11'>&nbsp;</td>";
					
					if ($varInvoiceNo !=''){echo "<td><div align='right' class='style11'>$varInvoiceNo</td>";}
					else{echo "<td>&nbsp;</td>";}
					
					if ($varInvoiceDate !=''){echo "<td><div align='right' class='style11'>$varInvoiceDate</td>";}
					else{echo "<td>&nbsp;</td>";}
					
					if ($varTotalInvoice !=''){echo "<td><div align='right' class='style11'>$varSubTotalInvoice</td>";}
					else{echo "<td>&nbsp;</td>";}				
				echo "</tr>";
				//$varGrandTotalInvoice = $varGrandTotalInvoice + $varSubTotalInvoice;
				//echo $varGrandTotalInvoice;
				}
  
				
			}		
			
			
	//klu tak ada data invoice atau satu shj data invoice
	if ($j == 0)
	{

		echo "<tr>";
			echo "<td height='30'><div align='left' class='style11'>$i</td>";
			echo "<td><div align='left' class='style11'>$varSupplierName</td>";
			echo "<td><div align='right' class='style11'>$varPONO</td>";
			echo "<td><div align='right' class='style11'>$varPODate</td>";
			echo "<td><div align='right' class='style11'>$varTotalPO </td>";
			
			if ($varInvoiceNo !=''){echo "<td><div align='right' class='style11'>$varInvoiceNo</td>";}
			else{echo "<td>&nbsp;</td>";}
			
			if ($varInvoiceDate !=''){echo "<td><div align='right' class='style11'>$varInvoiceDate</td>";}
			else{echo "<td>&nbsp;</td>";}
			
			if ($varTotalInvoice !=''){echo "<td><div align='right' class='style11'>$varSubTotalInvoice</td>";}
			else{echo "<td>&nbsp;</td>";}
			
		echo "</tr>"; 
	
	}
	
//sub total
$varSubTotalInvAll = number_format($varSubTotalInvAll,2);				

	echo "<tr>";
		echo "<td height='30'>&nbsp;</td>";
		echo "<td>&nbsp;</td>";
		echo "<td>&nbsp;</td>";
		echo "<td><div align='right' class='style11'><strong>Total PO</strong></td>";	
		echo "<td><div align='right' class='style11'>$varTotalPO</td>";
		echo "<td>&nbsp;</td>";
		echo "<td><div align='right' class='style11'><strong>Total Invoice</strong></td>";		
		echo "<td><div align='right' class='style11'>$varSubTotalInvAll</td>";
	echo "</tr>";	

//blank line
	echo "<tr>";
		echo "<td>&nbsp;</td>";
		echo "<td>&nbsp;</td>";
		echo "<td>&nbsp;</td>";
		echo "<td>&nbsp;</td>";
		echo "<td>&nbsp;</td>";
		echo "<td>&nbsp;</td>";
		echo "<td>&nbsp;</td>";
		echo "<td>&nbsp;</td>";
	echo "</tr>";	
$varSubTotalInvAll = 0;	
  }
  
  $varGrandTotalInvoice = number_format($varGrandTotalInvoice,2);
  $varGrandTotalPO = number_format($varGrandTotalPO,2);
 //Total
	echo "<tr>";				
		echo "<td height='30'><div align='left' class='style7'>&nbsp;</td>";
		echo "<td><div align='left' class='style7'>&nbsp;</td>";
		echo "<td><div align='left' class='style7'>&nbsp;</td>";
		echo "<td><div align='right' class='style7'><strong>Grant Total PO</strong></td>";
		echo "<td><div align='right' class='style7'><strong>$varGrandTotalPO</strong> </td>";						
		echo "<td><div align='left' class='style7'>&nbsp;</td>";
		echo "<td><div align='right' class='style7'><strong>Grant Total Invoice</strong></td>";			
		
		if ($varTotalInvoice !=''){echo "<td><div align='right' class='style7'><strong>$varGrandTotalInvoice</strong></td>";}
		else{echo "<td>&nbsp;</td>";}					
	echo "</tr>";  
	
  ?>
<tfoot>
	<td colspan="8" class="footer" >Print Date :   <?php echo $tarikh;?>           - Printed By : <?php echo $varUserName;?></td>
	</tfoot>
</tr>
</table>
 </tbody>	

 </html>	
<!--<div id="footer"> ini footer </div>-->
<!--</form>-->