<?php $PageSecurity = 11;
include('includes/session.inc');
include('includes/SQL_CommonFunctions.inc');
include('includes/function_lib.php');

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];
							$from=$function_filter[2];
							if($site!='')
							$filter[]="addmrn.site = '".$site."'";
							if($store!='')
							$filter[]="addmrn.StoreCode = '".$store."'";
							if($item!='')
							$filter[]="addmrnqty.mst_itemid = '".$item."'";
							if($from!='')
							$filter[]="addmrn.MRNDate <= '".$from."'";
							
							$filter=implode(' and ',$filter);
							   $sql1="Select SUM(addmrnqty.reciveqty),sum(addmrnqty.item_total_rate) as total
							  from addmrn left join addmrnqty 
							       on(addmrn.id=addmrnqty.issue_id) where Cancel_status!='Y' and $filter";
							$result1=DB_query($sql1,$db);
							$myrow1=DB_fetch_array($result1);
							  $return_array[]=$myrow1['SUM(addmrnqty.reciveqty)'];
							  if($myrow1['total']!='' && $myrow1['SUM(addmrnqty.reciveqty)']!=0 )
							  $rate1=$myrow1['total']/$myrow1['SUM(addmrnqty.reciveqty)'];
							  else
							  $rate1=0;
							   $rate=round($rate1,2);
								$return_array[]=$rate;
								$return_array[]=$myrow1['total'];
							  return $return_array;
						}
						

							
			               function issueSlip($function_filter,$item)
							{
								global $db;
								$site=$function_filter[0];
								$store=$function_filter[1];
								$from=$function_filter[2];
								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);
								
								return $myrow1['SUM(issueqty.issueqty)'];
							}




				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 <='".$from."' ";
					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 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 <='".$from."' ";
					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 <='".$from."' ";
					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.MRNDate <='".$from."' ";
						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 Rgpchallan($function_filter,$item)
					  {
					  
					      global $db;
							$site=$function_filter[0];
							$store=$function_filter[1];
							$from=$function_filter[2];
							$to=$function_filter[2];
							
							if($site!='')
							$filter[]="rgp_tab.branch = '".$site."'";
							if($store!='')
							$filter[]="rgp_tab.store = '".$store."'";
							if($from!='--' && $to!='--')
							$filter[]="rgp_tab.date <= '".$from."'";
							if($item!='')
							$filter[]="rgp_tab_details.mst_itemid = '".$item."'";
							
							$filter=implode(' and ',$filter);
							
							  $sql1="Select SUM(rgp_tab_details.qty) 
							  from rgp_tab left join rgp_tab_details 
							  on(rgp_tab.id=rgp_tab_details.parent_id) where $filter";
							$result1=DB_query($sql1,$db);
							$myrow1=DB_fetch_array($result1);
							return $myrow1['SUM(rgp_tab_details.qty)'];
					  
					  
					  		
					  }	
					   function Rgpreturn($function_filter,$item)
					    {
					  
					        global $db;
							$site=$function_filter[0];
							$store=$function_filter[1];
							$from=$function_filter[2];
							$to=$function_filter[2];
							
							if($site!='')
							$filter[]="rgp_return.branch = '".$site."'";
							if($store!='')
							$filter[]="rgp_return.store = '".$store."'";
							if($from!='--' && $to!='--')
							$filter[]="rgp_return.return_date <= '".$from."' ";
							if($item!='')
							$filter[]="rgp_return_details.item_id = '".$item."'";
							
							$filter=implode(' and ',$filter);
							
							  $sql1="Select SUM(rgp_return_details.qty) 
							  from rgp_return left join rgp_return_details 
							  on(rgp_return.id=rgp_return_details.parent_id) where $filter";
							$result1=DB_query($sql1,$db);
							$myrow1=DB_fetch_array($result1);
							return $myrow1['SUM(rgp_return_details.qty)'];
					  
					  
					  		
					  }	
					  
					  function Nrgp($function_filter,$item)
					    {					  
					        global $db;
							$site=$function_filter[0];
							$store=$function_filter[1];
							$from=$function_filter[2];
							$to=$function_filter[2];
							
							if($site!='')
							$filter[]="nrgp_challan.branch = '".$site."'";
							if($store!='')
							$filter[]="nrgp_challan.store = '".$store."'";
							if($from!='--' && $to!='--')
							$filter[]="nrgp_challan.date <= '".$from."' ";
							if($item!='')
							$filter[]="nrgp_challan_details.item_code = '".$item."'";
							
							$filter=implode(' and ',$filter);
							
							  $sql1="Select SUM(nrgp_challan_details.qty) 
							  from nrgp_challan left join nrgp_challan_details 
							  on(nrgp_challan.id=nrgp_challan_details.parant_id) where $filter";
							$result1=DB_query($sql1,$db);
							$myrow1=DB_fetch_array($result1);
							return $myrow1['SUM(nrgp_challan_details.qty)'];
					  
					  
					  		
					  }	
					  
					  function FinishedMR($function_filter,$item)
					    {					  
					        global $db;
							$site=$function_filter[0];
							$store=$function_filter[1];
							$from=$function_filter[2];
														
							if($site!='')
							$filter[]="finished_recieved.site = '".$site."'";
							if($store!='')
							$filter[]="finished_recieved.store = '".$store."'";
							if($from!='--' && $to!='--')
							$filter[]="finished_recieved.recieved_date <= '".$from."' ";
							if($item!='')
							$filter[]="finished_recieved_details.mst_itemid = '".$item."'";
							
							$filter=implode(' and ',$filter);
							
							   $sql1="Select SUM(finished_recieved_details.reciveqty) 
							  from finished_recieved left join finished_recieved_details 
							  on(finished_recieved.id=finished_recieved_details.finished_recieved_id) where $filter";
							$result1=DB_query($sql1,$db);
							$myrow1=DB_fetch_array($result1);
							return $myrow1['SUM(finished_recieved_details.reciveqty)'];
					  
					  
					  		
					  }	
					  

$Query=DB_query("SELECT * FROM rgp_tab where id='".$_REQUEST['RGPID']."'",$db);
$GetData=DB_fetch_array($Query);

$function_filter[]=$GetData['branch'];
$function_filter[]=$GetData['store'];

$data_date=explode('/',$_REQUEST['date']);
					     $date55=$data_date[2].'-'.$data_date[1].'-'.$data_date[0];
					      $date22= strtotime(date("Y-m-d", strtotime($date55)) . " +0 day");
                            $date1= date("Y-m-d",$date22);
$function_filter[]=$date1;


$data1="";
$data1.="<input name='vehchile_no' type='text' value='".$GetData['vehicle_no']."' style='width:100%' size='20' readonly='readonly'/>";

$data2="";
$data2.="<input name='remove_date' type='text' value='".ConvertSQLDate($GetData['remove_date'])."' style='width:47%' size='20' readonly='readonly'/> &nbsp;<input name='remove_time' type='text' value='".$GetData['remove_time']."' style='width:47%' size='20' readonly='readonly'/>";

$data3="";
$data3.="<input name='form_no' type='text' value='".$GetData['form_no']."' style='width:100%' size='20' readonly='readonly'/>";

$Query_details=DB_query("select * from rgp_tab_details where parent_id='".$GetData['id']."' and (status='N' || status='P')",$db);

$data4="";

$data4.="<table width='100%' border='1' cellpadding='3' cellspacing='0'>";
$data4.="<tr>";
$data4.="<th><strong>SrNo</strong></th>";
$data4.="<th><strong>Part Code</strong></th>";
$data4.="<th><strong>Part Name</strong></th>";
$data4.="<th><strong>Unit</strong></th>";
$data4.="<th><strong>Batch No</strong></th>";
$data4.="<th><strong>Stock Qty</strong></th>";
$data4.="<th><strong>Issueqty</strong></th>";
$data4.="<th><strong>Qty</strong></th>";
$data4.="<th><strong>Job Process</strong></th>";
$data4.="<th><strong>Job Rate</strong></th>";
$data4.="<th><strong>Job Value</strong></th>";
$data4.="</tr>";
$data4.="<tbody id='dataTable'>";
$i=0;
while($GetdetalsData=DB_fetch_array($Query_details))
{
         $op=openingBalance($function_filter,$GetdetalsData['mst_itemid']);
            //  echo "openingBalance"."<br>";
 		      $in_1=receivedMRN($function_filter,$GetdetalsData['mst_itemid']);
          //echo "receivedMRN"."<br>";
 		    $out_1=issueSlip($function_filter,$GetdetalsData['mst_itemid']);
           // echo "issueSlip"."<br>";
		   $out_2=issueSiteToSite($function_filter,$GetdetalsData['mst_itemid']);		
          // echo "issuesitetosite"."<br>";
		   $in_2=receivedSiteToSite($function_filter,$GetdetalsData['mst_itemid']);
		// echo " receivesitetosite"."<br>";
		  $out_3=issueMatarialReturn($function_filter,$GetdetalsData['mst_itemid']);
		 
		   $in_3=receivedReturn($function_filter,$GetdetalsData['mst_itemid']);		
		   $in_4=Rgpreturn($function_filter,$GetdetalsData['mst_itemid']);
		   $out_4=Rgpchallan($function_filter,$GetdetalsData['mst_itemid']);
		    $in_5=FinishedMR($function_filter,$GetdetalsData['mst_itemid']);
		 
		 	 
		  //$in_4=return_rejection($function_filter,$myrow['id']);
		   $invalue=($op+$in_1[0]+$in_2+$in_3+$in_4+$in_5);
		   $outvalue=($out_1+$out_2+$out_3+$out_4); 
		  //$in_4=return_rejection($function_filter,$myrow['mst_itemid']);
		 // $invalue=($op+$in_1+$in_2+$in_3);
		  //$outvalue=($out_1+$out_2+$out_3);
		  $tot=$invalue-$outvalue;
		   $total_current=$tot;





if($GetdetalsData['pending_qty']=='')
{
	$ChkQty=$GetdetalsData['qty'];
}
else
{
	$ChkQty=$GetdetalsData['pending_qty'];
}
$data4.="<input type='hidden' name='rgptab_detailId[]' value='".$GetdetalsData['id']."' size='20'/>";
$item_code=$s->GetFieldValue('item_master',"id='".$GetdetalsData['mst_itemid']."'",'StockID');
$item_name=$s->GetFieldValue('item_master',"id='".$GetdetalsData['mst_itemid']."'",'item_name');
$unitid=$s->GetFieldValue('item_master',"id='".$GetdetalsData['mst_itemid']."'",'unit');
$unitname=$s->GetFieldValue('unitsofmeasure',"unitid='".$unitid."'",'unitname');
$data4.="<tr bgcolor='#FFFFFF'>";
$data4.="<td>&nbsp;</td>";
$data4.="<td>".$item_code."<input type='hidden' name='itemcode[]' value='".$GetdetalsData['mst_itemid']."' size='20'/></td>";
$data4.="<td>".$item_name."</td>";
$data4.="<td>".$unitname."</td>";
$data4.="<td><input type='text' name='batch_no[]' size='20'  value='".$GetdetalsData['batch_no']."' readonly /></td>";
$data4.="<td><input type='text' name='stock_qty[]' size='20' id='stock_qty_".$i."' readonly value='".$total_current."'/>
</td>";
$data4.="<td><input type='text' name='qty1[]' size='20' id='qty1_".$i."'  value='".$GetdetalsData['qty']."'/></td>";
$data4.="<td><input type='text' name='qty[]' size='20' id='qty_".$i."' onkeyup='checkQty(".$i.")'  value='".$ChkQty."'/>
<input type='hidden' name='pendingQty[]' size='20' id='pendingQty_".$i."'  value='".$ChkQty."'/>
<input type='hidden' name='chkqty[]' size='20' id='chkqty_".$i."'  value='".$ChkQty."'/>
</td>";
$data4.="<td>";


$data4.="<select name='job_process[]' id='jobprocess' >";
$data4.="<option value='0'>Select</option>";
$sql_process=DB_query("select * from job_work_master",$db);
while($row1=DB_fetch_array($sql_process)){
$sel='';
if($GetdetalsData['job_process']==$row1['id'])
$sel='selected';
$data4.="<option value='".$row1['id']."' ".$sel.">".$row1['jobwork_name']."</option>";
			} 
$data4.="</select>";



$data4.="</td>";
$data4.="<td><input type='text' name='rate[]' id='rate_".$i."' onkeyup='calculaterate(".$i.")' size='20'  value='".$GetdetalsData['job_rate']."'/></td>";
$data4.="<td><input type='text' size='25' name='rvalue[]' id='rvalue_".$i."'  value='".$GetdetalsData['jobvalue']."' class='datepicker'/></td>";
$data4.="</tr>";
$i++;
}
$data4.="<input type='hidden' name='count' id='count' value='".$i."' />";

$data4.="</tbody>";
$data4.="</table>";


$data5="";
$data5.="<textarea name='removal_remarks'  style='width:100%' id='remark'>".$GetData['remarks']."</textarea>";


$data6="";
$data6.="<input type='text' id='exp_time' name='exp_time'  style='width:100%'  value='".$GetData['expected_time']."' />";

$data7="";
$data7.="<input type='text' id='exp_value' name='exp_value' onfocus='GrabdTotal()'  style='width:100%'  value='".$GetData['appox_value']."' />";


echo $data=$data1."__".$data2."__".$data3."__".$data4."__".$data5."__".$data6."__".$data7;
?>