<?php $PageSecurity = 4;
global  $db;
include('includes/session.inc');
include('includes/function_lib.php');
//include('includes/stockcalculation.php');

$function_filter[]=$_REQUEST['site'];
//$function_filter[]=$_REQUEST['store'];
$function_filter[]=date('Y-m-d');


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)'];



}	


function invoice($function_filter,$item)
{
global $db;
    $site=$function_filter[0];
    $store=$function_filter[1];
    $from=$function_filter[2];

    if($site!='')
    $filter[]="invoice.branch_name = '".$site."'";
    if($store!='')
    $filter[]="invoice_details.store = '".$store."'";
    if($from!='--' && $to!='--')
    $filter[]="invoice.invoiceDate <= '".$from."' ";
    if($item!='')
    $filter[]="invoice_details.ItemCode = '".$item."'";

    $filter=implode(' and ',$filter);

        $sql1="Select SUM(invoice_details.Quantity)  from 
      invoice_details INNER JOIN invoice 
            ON invoice.invoice_id=invoice_details.invoiceid 
            where $filter";
    $result1=DB_query($sql1,$db);
    $myrow1=DB_fetch_array($result1);
    return $myrow1['SUM(invoice_details.Quantity)'];



}

function disptch($function_filter,$item)
{
global $db;
    $site=$function_filter[0];
    $store=$function_filter[1];
    $from=$function_filter[2];

    if($site!='')
    $filter[]="dispatch.site = '".$site."'";
    if($store!='')
    $filter[]="dispatch.store = '".$store."'";
    if($from!='--' && $to!='--')
    $filter[]="dispatch.dispatchDate <= '".$from."' ";
    if($item!='')
    $filter[]="dispatch_details.item = '".$item."'";

    $filter=implode(' and ',$filter);




         $sql1="Select SUM(dispatch_details.dispatchQty)  from 
      dispatch_details INNER JOIN dispatch 
            ON dispatch.id=dispatch_details.parentId 
            where $filter";
    $result1=DB_query($sql1,$db);
    $myrow1=DB_fetch_array($result1);
    return $myrow1['SUM(dispatch_details.dispatchQty)'];

}
$data='<strong>Assembly Items</strong>  <input type="radio" name="itmval" value="ass" onclick="javascript:div_display(this.value)"><strong> Sub Assembly Items</strong><input type="radio" name="itmval" value="subass" onclick="javascript:div_display(this.value)" >';
$data.='<div id="ass_div" style="display:none" >
    <fieldset style="text-align: left"><strong>Assembly Items</strong></fieldset>
    <table  border="1" cellpadding="3" cellspacing="0" width="100%" style="border:1px solid #333333;">
    <tr><td class="tabledata"><strong>Select</strong></td>
         <td class="tabledata"><strong>Item Code</strong></td>
		
         <td class="tabledata"><strong>Item Name</strong></td>
	     <td class="tabledata"><strong>UOM</strong></td>		 
         <td class="tabledata"><strong>Current Stock</strong></td>
         <td class="tabledata"><strong>Requisition Quantity</strong></td>
         <td class="tabledata"><strong>Material Required Date</strong></td>
         <td class="tabledata"><strong>Remarks</strong></td></tr>';
 $itmsql=DB_query("SELECT * FROM indent_so_main_hrditem_details i,item_master itm WHERE itm.id=i.mst_itemid AND i.parent_id='".$_REQUEST['indent']."' AND itm.type_of_material='Bill' AND itm.CompanyId='".$_SESSION['ACGCompanyId']."' ",$db); while($itmres=DB_fetch_assoc($itmsql)){
     
     $op=openingBalance($function_filter,$itmres['mst_itemid']);
              //echo "openingBalance"."<br>";
 		       $in_1=receivedMRN($function_filter,$itmres['mst_itemid']);
          //echo "receivedMRN"."<br>";
 		     $out_1=issueSlip($function_filter,$itmres['mst_itemid']);
           // echo "issueSlip"."<br>";
		   $out_2=issueSiteToSite($function_filter,$itmres['mst_itemid']);                  
          // echo "issuesitetosite"."<br>";
		   $in_2=receivedSiteToSite($function_filter,$itmres['mst_itemid']);
		// echo " receivesitetosite"."<br>";
		  $out_3=issueMatarialReturn($function_filter,$itmres['mst_itemid']);
		 
		   $out_4=Rgpchallan($function_filter,$itmres['mst_itemid']);
		   $out_5=Nrgp($function_filter,$itmres['mst_itemid']);
		   //$out_6=invoice($function_filter,$myrow['id']);
		   $out_7=disptch($function_filter,$itmres['mst_itemid']);
		   $in_3=receivedReturn($function_filter,$itmres['mst_itemid']);		 
		  //$in_4=return_rejection($function_filter,$myrow['id']);
		    $in_4=Rgpreturn($function_filter,$itmres['mst_itemid']);
		    $in_5=FinishedMR($function_filter,$itmres['mst_itemid']);
		    $invalue=($op+$in_1[0]+$in_2+$in_3+$in_4+$in_5);
		
		     $outvalue=($out_1+$out_2+$out_3+$out_4+$out_5+$out_7);
		  $tot=trim($invalue)-trim($outvalue);
		
		
		 $total_current=$tot;
   $unit=DB_fetch_assoc(DB_query("select * from unitsofmeasure where unitid='".$itmres['mst_unitid']."' ",$db));
    $data.='<tr>
        <td><input type="checkbox" name="select[ass][]" value="'.$itmres['mst_itemid'].'"></td>
        <td>'.$itmres['StockID'].'</td>
        <td>'.$itmres['item_name'].'</td>
        <td>'. $unit['unitname'].'</td>
        <td><input  type="text" name="stck[ass][]" value="'.$total_current.'"></td>  
        <td><input name="reqqty[ass][]" type="text"></td>
        <td><input name="reqdate[ass][]" type="text" class="datepicker"></td>
        <td><input name="reqremark[ass][]" type="text"></td>        
    </tr>';
    } 
$data.='</table>
</div> <br>';

$data1='<div id="subass_div" style="display:none" >
    <fieldset style="text-align: left"><strong>Sub Assembly Items</strong></fieldset>
<table  border="1" cellpadding="3" cellspacing="0" width="100%" style="border:1px solid #333333;">
    <tr>
        <td class="tabledata"><strong>Select</strong></td>
         <td class="tabledata"><strong>Item Code</strong></td>
         <td class="tabledata"><strong>Item Name</strong></td>
	     <td class="tabledata"><strong>UOM</strong></td>		 
         <td class="tabledata"><strong>Current Stock</strong></td>
         <td class="tabledata"><strong>Requisition Quantity</strong></td>
         <td class="tabledata"><strong>Material Required Date</strong></td>
         <td class="tabledata"><strong>Remarks</strong></td>
    </tr>';
$itmsql1=DB_query("SELECT * FROM indent_so_main_hrditem_details i,item_master itm WHERE itm.id=i.mst_itemid AND i.parent_id='".$_REQUEST['indent']."' AND itm.type_of_material!='Bill' AND itm.CompanyId='".$_SESSION['ACGCompanyId']."' ",$db); while($itmres1=DB_fetch_assoc($itmsql1)){ 
    $op=openingBalance($function_filter,$itmres1['mst_itemid']);
              //echo "openingBalance"."<br>";
 		       $in_1=receivedMRN($function_filter,$itmres1['mst_itemid']);
          //echo "receivedMRN"."<br>";
 		     $out_1=issueSlip($function_filter,$itmres1['mst_itemid']);
           // echo "issueSlip"."<br>";
		   $out_2=issueSiteToSite($function_filter,$itmres1['mst_itemid']);		
          // echo "issuesitetosite"."<br>";
		   $in_2=receivedSiteToSite($function_filter,$itmres1['mst_itemid']);
		// echo " receivesitetosite"."<br>";
		  $out_3=issueMatarialReturn($function_filter,$itmres1['mst_itemid']);
		 
		   $out_4=Rgpchallan($function_filter,$itmres1['mst_itemid']);
		   $out_5=Nrgp($function_filter,$itmres1['mst_itemid']);
		   //$out_6=invoice($function_filter,$myrow['id']);
		   $out_7=disptch($function_filter,$itmres1['mst_itemid']);
		   $in_3=receivedReturn($function_filter,$itmres1['mst_itemid']);		 
		  //$in_4=return_rejection($function_filter,$myrow['id']);
		    $in_4=Rgpreturn($function_filter,$itmres1['mst_itemid']);
		    $in_5=FinishedMR($function_filter,$itmres1['mst_itemid']);
		    $invalue=($op+$in_1[0]+$in_2+$in_3+$in_4+$in_5);
		
		     $outvalue=($out_1+$out_2+$out_3+$out_4+$out_5+$out_7);
		  $tot=trim($invalue)-trim($outvalue);
		
		
		 $total_current=$tot;
   $unit=DB_fetch_assoc(DB_query("select * from unitsofmeasure where unitid='".$itmres1['mst_unitid']."' ",$db));
    $data1.='<tr>
        
        <td><input type="checkbox" name="select[subass][]" value="'.$itmres['mst_itemid'].'"></td>
        <td>'.$itmres1['StockID'].'</td>
        <td>'.$itmres1['item_name'].'</td>
        <td>'. $unit['unitname'].'</td>
        <td><input name="stck[subass][]" type="text" value="'.$total_current.'"></td>
        <td><input name="reqqty[subass][]" type="text"></td>
        <td><input name="reqdate[subass][]" type="text" class="datepicker"></td>
        <td><input name="reqremark[subass][]" type="text"></td>        
    </tr>';
    } 
$data1.='</table>
</div>';
$ind=DB_fetch_assoc(DB_query("select * from indent_so_main where id='".$_REQUEST['indent']."' ",$db));
$client=DB_fetch_assoc(DB_query("select c_name from clientmaster where id='".$ind['client']."' ",$db));
$user=DB_fetch_assoc(DB_query("SELECT * FROM empmaster WHERE     id='".$ind['handled_by']."'",$db));
echo $data.$data1."**".$client['c_name']."**".$ind['client']."**".$user['emp_name']."**".$ind['handled_by'];
    