<?php 
require('../db/db.php'); 

if(isset($_GET['recocile_report'])&&$_GET['recocile_report']!='')
{
	$inventoryclosing_id=$_GET['recocile_report'];
	$_SESSION['inventoryclosingid']=$inventoryclosing_id;
}
else
{
	$inventoryclosing_id=$_SESSION['inventoryclosingid'];
}
	
if(isset($_POST['SUBMIT']) && $_POST['SUBMIT']=="Reconcile Quantities")
{

	$query= mysqli_query($con,"INSERT INTO product_reconcile (date, reconcile_by, inventoryclosing_id)VALUES('$date','$username', '$inventoryclosing_id')") or die(mysqli_errno()) or die(mysqli_errno());
	
	$reconcileid=mysqli_insert_id($con);
	
	for($i=0;$i<count($_POST['product_idrecocile']);$i++)
	{
		
		$product_id = $_POST['product_idrecocile'][$i];
		
		$qty = $_POST['recocile_qty'][$i];
		
		if ($qty!=0 && $qty!='')
		{
			$query= mysql_query("INSERT INTO product_reconcileitems (reconcile_id, date, product_id, reconcile_qty)VALUES('$reconcileid','$date', '$product_id', '$qty')") or die(mysql_error());
		
			$query= mysqli_query($con,"UPDATE products SET unit_qtyinstock='$qty' WHERE product_id='$product_id' ") or die(mysqli_errno());
		
		}
	
	
	}

}

?>
<!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"><!-- InstanceBegin template="/Templates/print.dwt.php" codeOutsideHTMLIsLocked="false" -->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

    <link rel="shortcut icon" href="../images/favicon.png">
    <title>Med2TEC Hospital Management Information System</title> 
    <link href="../css/print.css" rel="stylesheet">
    <link href="../datepicker/jquery-ui.css" rel="stylesheet" >  
    <script type="text/javascript" src="../datepicker/jquery-1.9.1.js"></script>
	<script type="text/javascript" src="../datepicker/jquery-ui.js"></script> 
</head>

<body> 
<div align="right"> 
 <SCRIPT LANGUAGE="JavaScript">  
if (window.print) {
document.write('<form class="no-print"><input type=button name=print value="Print" onClick="window.print()"></form>');
}
</script></div>

<section class="wrapper">
<div style="background:#FFF; padding:0px 0px 0; height:auto;">
  <table cellspacing="0" cellpadding="0" width="100%" border="0" style="font-size:11px;">
    <tbody>
      <tr>
        <td align="left" style="background:#fff;"><img alt="" src="../images/org_print_logos/<?php echo $_SESSION['security']['org_id']?>.png" style="width:100%;"></td>
      </tr>
    </tbody>
  </table>
  
</br>

</div>

<div class="row">
          <section class="panel">
            <header class="panel-heading">
              <h2><!-- InstanceBeginEditable name="title" -->SALE INVOICES<!-- InstanceEndEditable --></h2>
            </header>

  <br />
  
<!-- InstanceBeginEditable name="main" -->
  <div class="content">
   
   <table width="100%" border="0" align="left" cellpadding="0" cellspacing="0">
  
  <p align="right">
  <SCRIPT LANGUAGE="JavaScript"> 
if (window.print) {
document.write('<form class="no-print"><input type=button name=print value="Print" onClick="window.print()"></form>');
}
</script></p>
  <tr>
    <td align="right"><table width="100%" border="0" cellspacing="0" cellpadding="0" class="bottomBorder">
      <tr>
        <td valign="bottom"><h1>INVENTORY RECONCILIATION</h1></td>
        <td align="right" valign="top"> </td>
      </tr>
	  <tr> </tr>
      </table></td>
  </tr>
  <form action="" method="POST" onsubmit="return check(this);"/>
  <tr>
    <td><div align="right">
    <?php 
	$process_reconcile = mysql_query("SELECT inventoryclosing_id FROM   product_reconcile WHERE inventoryclosing_id='$inventoryclosing_id'") or die(mysql_error()); 
	$row_process_reconcile = mysql_fetch_assoc($process_reconcile);
	
	if (isset($row_process_reconcile['inventoryclosing_id']))
	{
	?>
    
    <?PHP 
	}
	else
	{
		?>
			<input name="SUBMIT" type="submit" value="Reconcile Quantities"  />
		<?php 
	}
	?>
    </div>
    </td>
  </tr>
  <tr>
    <td width="511"><table width="100%">
      
      
      <tr>
        	<td><table width="100%" class="bottomBorder">
            <tr>
                <th width="3%" align="center" valign="top"><h3><br />ID</h3></th>
                <th width="7%" align="center" valign="top"><h3>Product Name</h3></th>
                <th width="5%" align="center" valign="top"><h3>Product <br />Type</h3></th>
                <th width="10%" align="center" valign="top"><h3>Qty<br />Last Closing</h3></th>
                <th width="10%" align="center" valign="top"><h3>System Qty<br />in Stock</h3></th>
                <th width="10%" align="center" valign="top"><h3>Actual Qty<br />this Closing</h3></th>
                <!--<th width="10%" align="center" valign="top"><h3>Unit Price<br />&nbsp;</h3></th>
                <th width="10%" align="center" valign="top"><h3>Stock Price<br />&nbsp;</h3></th>-->
                <th width="10%" align="center" valign="top"><h3>Variation<br />&nbsp;</h3></th>
               
            </tr>
            <?php
			
			//////////////////////////////Getting the Months ///////////////////////////////////////////
			
			$raw_results1 = mysql_query("SELECT * FROM  `inventory_closing` WHERE inventoryclosing_id='$inventoryclosing_id'") or die(mysql_error()); 
	 		$row1 = mysql_fetch_assoc($raw_results1);
			
			$invclosingid=$row1['inventoryclosing_id'];
			$timestampOfInventoryClosing=$row1['timestamp'];
			
			$monthofInventoryClosing=date('m',strtotime($timestampOfInventoryClosing));
			$yearofInventoryClosing=date('Y',strtotime($timestampOfInventoryClosing));
			
			$raw_results = mysql_query("SELECT * FROM products WHERE active= 1") or die(mysql_error()); 
			while($row = mysql_fetch_array($raw_results))
			{ 
				$productid=$row['product_id'];
				
				$unit_qtyinstock=$row['unit_qtyinstock'];
				
				?>
				
				<?PHP 
				$queryForProductRecItems = mysql_query("SELECT SUM(qty_received) FROM product_recitems WHERE product_id = '$productid' &&  YEAR(time) = '$yearofInventoryClosing' && MONTH(time) = '$monthofInventoryClosing'") or die(mysql_error()); 
				
				while($rowOfRecItems = mysql_fetch_assoc($queryForProductRecItems))
				{
					$queryForProductSaleItems = mysql_query("SELECT SUM(qty) FROM product_saleitems WHERE product_id = '$productid' AND  YEAR(time) = '$yearofInventoryClosing' AND MONTH(time) = '$monthofInventoryClosing'") or die(mysql_error()); 
					
					while($rowOfSaleItems = mysql_fetch_assoc($queryForProductSaleItems))
					{
						$raw_result5 = mysql_query("SELECT * FROM product_closing where product_id='$productid' && inventoryclosing_id='$inventoryclosing_id' && product_closing.unit_phy_qtyinstock!=product_closing.unit_qtyinstock") or die(mysql_error()); 
						
						while ($row5 = mysql_fetch_array($raw_result5)) 
						{
							if(($row5['closingcount']-$row5['unit_qtyinstock'])!=0)
							{
								$phy_count=$row5['closingcount'];
								$product_idrecocile=$row5['product_id'];
								$timestampOfProductClosing=$row5['timestamp'];
								
								date('M',strtotime($timestampOfProductClosing));
								
								if($row['product_id']==$row5['product_id']) 
								{ 
									?>
									<tr> 
									
										<td align="center"><?php echo $row['product_id'];?><p></td>
										
										<td align="center"><?php echo $row['product_name'];?><p></td>
										
										<td><?php echo $row['medicine_type'];?><p></td>
										
										<td><?php 
										$lastcosingid= $inventoryclosing_id - 1;
										$lastclosingqty = mysql_query("SELECT * FROM  `product_closing` WHERE inventoryclosing_id='$lastcosingid' && product_id='$productid'") or die(mysql_error()); 
										while ($row_lastclosingqty = mysql_fetch_assoc($lastclosingqty))
										{
										echo $row_lastclosingqty['unit_phy_qtyinstock']; 
										}
										?></td>
										<input type="hidden" name="product_idrecocile[]" value="<?php echo $product_idrecocile; ?>" />
										
										<td><?php echo $row5['unit_qtyinstock'];?><p></td>
										
										<td>
											<?php echo $row5['closingcount']; ?>
											<p>
										</td>
										<td><?php  if ($row5['closingcount']> $row5['unit_qtyinstock']) { echo "+"; } echo $row5['closingcount']-$row5['unit_qtyinstock'];?><p></td>
										
									</tr>
									<?php 
								} 
							}
						}
					}
				} 
			} 
			?>
          	</table></td>
      </tr>
      <tr>
        <td></td>
      </tr>  
      </table> </td>
  </tr>
  </table></form>
  </div>
  <!-- InstanceEndEditable -->
</section>
</div>

<!--<p align="center" style="font-size:12px; font-family:Arial, Helvetica, sans-serif; margin-top:260px;"><strong><!--317 A-1, Nursery Stop, Township, Lahore. 
Ph: 042-35151666</strong><br />
For Appointment call at 042-35151666 (bring your Patient ID Card) </p>-->

   <div class="sidepop" style="float:right;">
    <div class="sidepopbutton stemp"><div style="color: rgb(0, 0, 0); text-align: right; font-size: 10px; margin-top: 4px; position: absolute; right: 180px;">© Software by Softusion Inc. 0322 6155 219</div>   
    </div>
</div>

<!--<div class="sidepop" style="float:right;">
    <div class="sidepopbutton stemp"><div style="color:#000; text-align:right; font-size:9px;">© Software designed by Softusion Inc. Contact us at 0322 6155 219</div>   
    </div>
</div>-->


          
</body>
<!-- InstanceEnd --></html>
