<?php
ob_start();
$PageSecurity = 4;
include('includes/DefinePOClass.php');
include('includes/session.inc');
include('includes/SQL_CommonFunctions.inc');


 //**************************   Calculating Stock Balance *******************************//
		

						
                        function openingBalance($function_filter,$item)
							     {							
								global $db;
								$site=$function_filter[0];
								$store=$function_filter[1];								
								$from=$function_filter[2];																
								if($site!='')
								$filter[]="site = '".$site."'";
								if($store!='')
								$filter[]="store = '".$store."'  ";								
								if($from!='--' && $to!='--')
								$filter[]="reveive_date <= '".$from."'";
								if($item!='')
								$filter[]="itemcode = '".$item."' ";								
								$filter=implode(' and ',$filter);
							 	$sql1="Select stock, rate from opening_stockitemwise where $filter";								
								$result1=DB_query($sql1,$db);
								$myrow1=DB_fetch_array($result1);
								return $myrow1['stock'];
							}

                        function receivedMRN($function_filter,$item)
						  {
							global $db;
							$site=$function_filter[0];
							$store=$function_filter[1];							
							if($site!='')
							$filter[]="addmrn.site = '".$site."'";
							if($store!='')
							$filter[]="addmrn.StoreCode = '".$store."'";
							if($item!='')
							$filter[]="addmrnqty.mst_itemid = '".$item."'";							
							$filter=implode(' and ',$filter);
							    $sql1="Select SUM(addmrnqty.reciveqty) from addmrn left join addmrnqty 
							       on(addmrn.id=addmrnqty.issue_id) where Cancel_Status='N'and  $filter";
							$result1=DB_query($sql1,$db);
							$myrow1=DB_fetch_array($result1);  
							return $myrow1['SUM(addmrnqty.reciveqty)'];
						}
						
				function receivedReturn($function_filter,$item)
			    {
				global $db;
				$site=$function_filter[0];
				$store=$function_filter[1];
				$from=$function_filter[2];
				$to=$function_filter[3];
				
				if($site!='')
				$filter[]="material_return.site = '".$site."'";
				if($store!='')
				$filter[]="material_return.store = '".$store."'";
				if($from!='--' && $to!='--')
				$filter[]="material_return.returndate between '".$from."' and '".$to."'";
				if($item!='')
				$filter[]="material_return_details.itemid = '".$item."'";
				
				$filter=implode(' and ',$filter);
				 $sql1="Select SUM(material_return_details.receiveqty) 
				 from material_return left join material_return_details
				  on (material_return.id=material_return_details.parentId) where $filter";
				  
				$result1=DB_query($sql1,$db);
				$myrow1=DB_fetch_array($result1);
				return $myrow1['SUM(material_return_details.receiveqty)'];
			}


					function issueSlip($function_filter,$item)
							   {
								global $db;
								$site=$function_filter[0];
								$store=$function_filter[1];
								
								if($site!='')
								$filter[]="addissue.site = '".$site."'";
								if($store!='')
								$filter[]="addissue.mst_storeid = '".$store."'";
								if($item!='')
								$filter[]="issueqty.mst_itemid = '".$item."'";
								
								$filter=implode(' and ',$filter);
							
								  $sql1="Select SUM(issueqty.issueqty) from addissue left join 
								 issueqty on(addissue.id=issueqty.issue_id) where $filter";
								$result1=DB_query($sql1,$db);
								$myrow1=DB_fetch_array($result1);
								
								//echo $myrow1['SUM(issueqty.issueqty)'];
								
								return $myrow1['SUM(issueqty.issueqty)'];
							}		



			function receivedSiteToSite($function_filter,$item)
			{
				global $db;
				$site=$function_filter[0];
				$store=$function_filter[1];
				$from=$function_filter[2];
				$to=$function_filter[3];
				
				if($site!='')
				$filter[]="transfer_receipt.Received_to = '".$site."'";
				if($store!='')
				$filter[]="transfer_receipt.Received_store = '".$store."'";
				if($from!='--' && $to!='--')
				$filter[]="transfer_receipt.MRNDate between '".$from."' and '".$to."'";
				if($item!='')
				$filter[]="transfer_receipt_details.itemid = '".$item."'";
				
				$filter=implode(' and ',$filter);
				
				   $sql1="Select SUM(transfer_receipt_details.reciveqty) 
				   from transfer_receipt left join transfer_receipt_details               
				    on(transfer_receipt.id=transfer_receipt_details.transfer_receipt_id) where $filter";
				$result1=DB_query($sql1,$db);
				$myrow1=DB_fetch_array($result1);
				return $myrow1['SUM(transfer_receipt_details.reciveqty)'];
			}

					function issueSiteToSite($function_filter,$item)
					{
						global $db;
						$site=$function_filter[0];
						$store=$function_filter[1];
						$from=$function_filter[2];
						$to=$function_filter[3];
						
						if($site!='')
						$filter[]="transfer_issue.sitecode = '".$site."'";
						if($store!='')
						$filter[]="transfer_issue.fromstore = '".$store."'";
						if($from!='--' && $to!='--')
						$filter[]="transfer_issue.issuedate between '".$from."' and '".$to."'";
						if($item!='')
						$filter[]="transfer_issue_details.itemid = '".$item."'";
						
						$filter=implode(' and ',$filter);
							$sql1="Select SUM(transfer_issue_details.issueqty)  from transfer_issue left join transfer_issue_details on(transfer_issue.id=transfer_issue_details.transfer_issue_id) where $filter";
						$result1=DB_query($sql1,$db);
						$myrow1=DB_fetch_array($result1);
						$return_array=$myrow1['SUM(transfer_issue_details.issueqty)'];
						//$avg_rate=$myrow1['SUM(transfer_issue_details.rate)'];
						//$return_array[]=$avg_rate;
						return $return_array;
						
					}

        function issueMatarialReturn($function_filter,$item)
        {
	global $db;
	$site=$function_filter[0];
	$store=$function_filter[1];
	$from=$function_filter[2];
	$to=$function_filter[3];
	
	if($site!='')
	$filter[]="inv_material_return.sitecode = '".$site."'";
	if($store!='')
	$filter[]="inv_material_return.storecode = '".$store."'";
	if($from!='--' && $to!='--')
	$filter[]="inv_material_return.Reqdate between '".$from."' and '".$to."'";
	if($item!='')
	$filter[]="inv_material_return_details.itemid = '".$item."'";
	
	$filter=implode(' and ',$filter);
	
	  $sql1="Select SUM(inv_material_return_details.ReturnQty)
	   from inv_material_return left join inv_material_return_details     
	   on(inv_material_return.id=inv_material_return_details.parentId) where $filter";
	$result1=DB_query($sql1,$db);
	$myrow1=DB_fetch_array($result1);
	return $myrow1['SUM(inv_material_return_details.ReturnQty)'];
   }				
						
function return_rejection($function_filter,$item)
{
	global $db;
	$site=$function_filter[0];
	$store=$function_filter[1];
	$from=$function_filter[2];
	$to=$function_filter[3];
	
	if($site!='')
	$filter[]="rejection_tbl.issue_branch = '".$site."'";
	if($store!='')
	$filter[]="rejection_tbl.issue_stroe = '".$store."'";
	if($from!='--' && $to!='--')
	$filter[]="rejection_tbl.date between '".$from."' and '".$to."'";
	if($item!='')
	$filter[]="rejection_tbl_details.itemid = '".$item."'";
	
	$filter=implode(' and ',$filter);
	
	  $sql1="Select SUM(rejection_tbl_details.returnqty) from rejection_tbl left join rejection_tbl_details on(rejection_tbl.id=rejection_tbl_details.parentId) where $filter";
	$result1=DB_query($sql1,$db);
	$myrow1=DB_fetch_array($result1);
	return $myrow1['SUM(rejection_tbl_details.returnqty)'];
}
	
		










  $sql="select MRNDate,OrderDate,ReceivedFrom,order_no from addmrn where id ='".$_REQUEST['q']."'";
$result=DB_query($sql,$db);
$mrn_row=DB_fetch_array($result);
  $poSql="SELECT purchase_ordNo,po_date FROM purchorders WHERE id='".$mrn_row['order_no']."'";
$poResult=DB_query($poSql,$db);
$poRow=DB_fetch_array($poResult);
//$_SESSION['itemcode']=$_REQUEST['item_code'];


?>

<table name="tablemrn" width="100%" border="1" cellspacing="0" cellpadding="3" style="border-collapse:collapse">
 <tr>
 <th style="width:150px;"><strong>MRN Date</strong></th>
 <td style="width:248px;"><input name="mrnDate" id="mrnDate" type="text" style="width: 100%"  value="<?php echo ConvertSQLDate($mrn_row['MRNDate']);?>"/></td>
 <th style="width:150px;"><strong>PO No</strong></th>
 <td><input name="poNo_code" id="poNo" type="text" readonly style="width: 100%"  value="<?php echo  $poRow['purchase_ordNo']; ?>"/><input type="hidden" name="poNo"  value="<?php echo $mrn_row['PO_WO_MTO'];?>" /></td>
</tr>
	<tr>
 <th><strong>PO Date</strong></th>
 <td><input name="poDate" id="poDate" type="text" style="width: 100%" readonly  value="<?php echo  ConvertSQLDate($poRow['po_date']); ?>"/>
 </td>

	<td colspan="2">&nbsp;</td>

 </tr>
 </table>
 <p align="left"><input type="submit"  name="item_popup" value="Add Items" id="item_popup"  onclick="open_items();" disabled="true"/></p>
 <!--<p align="right" ><a href="exportdata.php?modid=<?php echo $_REQUEST['modid'];?>&frmid=<?php echo $_REQUEST['frmid'];?>">Export</a></p>-->
 <div style="overflow:auto; width:810px; height:auto">
<table width="100%" border="1" cellspacing="0" cellpadding="3" style="border-collapse:collapse">
  <tr>
    <th>Item Code</th>
    <th>Item Name</th>
    <th>UOM</th>
	<th>PO Qty</th>
	 <th>Qty As Per Bill</th>
    <th>Qty Accepted</th>
	 <th>Stock Bal</th>
    <th>Rate</th>
    <th>Total Value</th>
    <th>Return Qty</th>
	<th>Remarks</th>
	
  </tr>
 
 <?php
 $x=0;
 $sitedata=DB_query("select site,StoreCode,order_no from addmrn where id='".$_REQUEST['q']."'",$db);
 $sitedata1=DB_fetch_array($sitedata);
 
                                       $function_filter[]=$sitedata1['site'];
										$function_filter[]=$sitedata1['storecode'];
									    $function_filter[]= date('Y-m-d');
										$function_filter[]=date('Y-m-d'); 
 
 
 $site=$sitedata1['site'];
 $store=$sitedata1['StoreCode'];
$query="Select * from addmrnqty where issue_id='".$_REQUEST['q']."' and MRVS=0";
$result2=DB_query($query,$db);
while($myrow2 = DB_fetch_array($result2)) 
{
	

           $op=openingBalance($function_filter,$myrow2['mst_itemid']);
	   	 $in_1=receivedMRN($function_filter,$myrow2['mst_itemid']);
	 	 $out_1=issueSlip($function_filter,$myrow2['mst_itemid']);
		 $out_2=issueSiteToSite($function_filter,$myrow2['mst_itemid']);
		  $in_2=receivedSiteToSite($function_filter,$myrow2['mst_itemid']);
		  $out_3=issueMatarialReturn($function_filter,$myrow2['mst_itemid']); 
		  $in_3=receivedReturn($function_filter,$myrow2['mst_itemid']);
		    $in_4=return_rejection($function_filter,$myrow2['mst_itemid']);
		  $invalue=($op+$in_1+$in_2+$in_3+$in_4);
		  $outvalue=($out_1+$out_2+$out_3);
		  $tot=$invalue-$outvalue;
		 //$total_current=$op ;	
		 
		  $stockRow=$tot;	
?>
  <?php
$xtemquery="Select * from item_master where id  ='".$myrow2['mst_itemid']."'";
$xtemres=DB_query($xtemquery,$db);
$xtemrow=DB_fetch_array($xtemres);
?>
<tr bgcolor="#FFFFFF">
<td><?php echo $xtemrow['StockID'];?>
<input type="hidden" name="item_code[]" id="item_code_<?php echo $x;?>" value="<?php echo $myrow2['mst_itemid'];?>">	
<input type="hidden" name="itemid[]" id="itemid_<?php echo $x; ?>" value="<?php echo $myrow2['mst_itemid']; ?>" />
</td>

<td><?php echo $xtemrow['item_name']; ?></td>
<td><?php 
$unitsql="select unitname from unitsofmeasure where unitid='".$xtemrow['mst_unitid']."'";
$unitresult=DB_query($unitsql,$db);
$unitname=DB_fetch_array( $unitresult);
echo $unitname['unitname']; ?>
</td>

<td><?php echo $myrow2['POQty']; ?><input type="hidden" name="POQty[]" id="POQty_<?php echo $x;?>" value="<?php echo $myrow2['POQty'];?>">	
	</td>
	   
<td><?php echo $myrow2['qty_as_per_bill']; ?><input type="hidden" name="QtyAsPerBill[]" id="QtyAsPerBill_<?php echo $x;?>" value="<?php echo  $myrow2['qty_as_per_bill'];?>"></td>
<td><?php echo $myrow2['reciveqty']; ?><input type="hidden" name="QtyAccepted[]" id="QtyAccepted_<?php echo $x;?>" value="<?php echo $myrow2['reciveqty'];?>"></td>
<td> <?php echo $stockRow; ?>
<input name="stock[<?php echo $i;?>]" id="stock_<?php echo $x;?>"  value="<?php echo $stockRow; ?>" type="hidden" /></td>
<td><?php echo $myrow2['itemprice']; ?><input type="hidden" name="Rate[]" id="Rate_<?php echo $x;?>" value="<?php echo $myrow2['itemprice'];?>"></td>
<td><?php echo $myrow2['item_total_rate']; ?><input type="hidden" name="TotalValue[]" id="TotalValue_<?php echo $x;?>" value="<?php $myrow2['item_total_rate'];?>"></td>
 <td align="center"><input name="ReturnQty[]" type="text" id="ReturnQty_<?php echo $x;?>" size="20"  onchange="calamount(<?php echo $x;?>)" /></td>
<td align="center"><input name="Remarks[]" type="text" id="Remarks_<?php echo $x;?>" size="10" onfocus="checkdata(<?php echo $i; ?>)"/></td>

</tr>


 <?php $x++; }  ?>
 <input type="hidden" name="ctr" id="ctr" value="<?php echo $x;?>" />
 </table>
  </div>


 

 
 
 
 
 

