<?php
include("Class_Party.php");
include("Class_Branch.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 adjustingQty($function_filter,$item)
                {
                    global $db;
                    $add=0;
                    $minus=0;
                   $site=$function_filter[0];
                    $store=$function_filter[1];
                    $from=$function_filter[2];								
                    if($site!='')
                    $filter[]="p.site = '".$site."'";
                    if($store!='')
                    $filter[]="p.store = '".$store."'";
                    if($from!='--' && $to!='--')
                    $filter[]="p.date1 <= '".$from."'";
                    if($item!='')
                    $filter[]="pd.mstitemid = '".$item."'";

                    $filter=implode(' and ',$filter);      
                    
                    
                     $query="select pd.adjustment_qty ,pd.adjustment_type,p.site,p.store,pd.mstitemid,p.date1  from physical_stock p, physical_stock_details pd where  p.id=pd.physical_stock_id and $filter and p.cancel_status=0";
                    $phy_stck_opening=DB_query($query,$db);
                       while($phy_stck=DB_fetch_assoc($phy_stck_opening))
                       {
                           if($phy_stck['adjustment_type']=="plus")
                           {
                              // echo "jkl";
                               $add+=$phy_stck['adjustment_qty'];
                           }
                           else if($phy_stck['adjustment_type']=="minus")
                           {
                              // echo "bhjvhj";
                                 $minus+=$phy_stck['adjustment_qty'];
                           }
                                   
                       }
                        $diff=$add-$minus."<br>";
                                return $diff;
                               
                         }
				
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)'];
}
?>

<link rel="stylesheet" href="css/new_mxerp.css">
<div class="body_contain">
<div class="tab_div">
<h1>Lead Register</h1>
<div id="slidingDiv">
<form action="" method="post">
<table width="100%" border="0" cellpadding="3" cellspacing="0">
    <tr class="row1">
      <td class="tabledata1" style="width:146px;">Branch</td>
      <td><?php  	 //echo $ClassBranch->SiteRecord('mst_branchid',$mst_branchid,'leads');

		$sql_state=DB_query("SELECT loccode,locationname FROM  locations where CompanyId='".$_SESSION['ACGCompanyId']."' and loccode in('".$_SESSION['AssignedBranch']."') ",$db);?> 
		<select name="mst_branchid" id="mst_branchid" style="width:150px;" onchange="showsitename160(this.value)">
			
			<?php while($SiteName=DB_fetch_array($sql_state)) {?>]
			<option value="<?php echo $SiteName['loccode']; ?>"><?php echo $SiteName['locationname']; ?></option>
			<?php }?>
		</select>
      </td>
      
      <td class="tabledata1">Store<span style="color:#FF0000">*</span></td>
        <td>
			<div id="storedata7">
            	<select name="store1" id="store1">
				<option value='0'>All</option>
                <?php 
	 			$store_sql = "SELECT id, StoreName,storecode FROM storetype where Store_Site = '".$_REQUEST['mst_branchid']."'";
	 			$store_result = DB_query($store_sql,$db);
	 			while($store_row = DB_fetch_array($store_result))
				{
					$p='';
					if($store_row['id'] == $_REQUEST['store1'])
						$p="selected";
	 			?>
                <option value="<?php echo $store_row['id'] ?>"<?php echo $p; ?>><?php echo $store_row['storecode']?></option>
                <?php } ?>
                </select>
          	</div>
		</td>
      <td class="tabledata1" style="width:146px;">Items</td>
      <td><select name="items" id="items" style="width:150px;" >
          <option value="0">Select</option>
	<?php
		  $itemSQL="Select * from item_master where CompanyId='".$_SESSION['ACGCompanyId']."' order by item_name";
		$itemSQLExec=DB_query($itemSQL,$db);
		while($itemRow=DB_fetch_array($itemSQLExec)){?>
		<option value="<?php echo $itemRow['id']?>" <?php echo ($_REQUEST['items']==$itemRow['id']?'selected':'');?>><?php echo $itemRow['item_name']?></option>
		<?php  } ?>
        </select></td>
    </tr>
    <tr class="row1">
      <td class="tabledata1">From Date</td>
      <td><input name="frmDate" id="frmDate" type="text"  class="datepicker" style="width:150px;" value="<?php echo $_REQUEST['frmDate'];?>" /></td>
      <td class="tabledata1">To Date</td>
      <td><input name="ToDate" id="ToDate" type="text"  class="datepicker" style="width:150px;"  value="<?php echo $_REQUEST['ToDate'];?>" /></td>
      <td colspan="2"></td>
    </tr>
<tr class="row1">
	<td colspan="6"><p align="center"><input type="submit" name="Search" value="Search" class="search" /></p></td>
</tr>
  </table>
</form>
</div>
<?php  

 if($_REQUEST['Search']){
 


 
$function_filter[]=$_REQUEST['mst_branchid'];
             
if($_REQUEST['store1']!='0')
 $function_filter[]=$_REQUEST['store1'];
 
  if($_REQUEST['items']!='0'){
 $filter[]='id='.$_REQUEST['items'];
}

$filter[]='CompanyId='.$_SESSION['ACGCompanyId'];


	if($_REQUEST['frmDate'] && $_REQUEST['ToDate']!='')
        {
	 $function_filter[]=ConvertSQLDate($_REQUEST['frmDate']);
        }
 else {
       $function_filter[]=date('Y-m-d');     
        }
	if($filter!='')
	{
	$filter1=implode(" and ",$filter);
        }
       // print_r($function_filter);
 ?>
<table width="100%" border="1" cellpadding="3" cellspacing="0">
  <tr class="row1">
    <td class="tabledata" >Sr No.</td>
    <td class="tabledata" >Item Code</td>
    <td class="tabledata" >Item Name</td>
    <td class="tabledata" >Unit</td>
    <td class="tabledata" >Stock</td>
    <td class="tabledata" >Transit Qty.</td>
   </tr>
   
   <?php
   $i=0;
    $itemQry="select * from item_master where $filter1";
   $itemQryExec=DB_query($itemQry,$db);
   while($itemRow=DB_fetch_array($itemQryExec)){
   ?>
   <tr>
   <td><?php echo ($i+1).'.'; ?></td>
   <td><?php echo $itemRow['StockID']; ?></td>
   <td><?php echo $itemRow['item_name']; ?></td>
   <td><?php echo $s->getField('unitsofmeasure','unitid',$itemRow['mst_unitid'],'unitname'); ?></td>
   <td><?php 
   // Stock Functions
   $op=openingBalance($function_filter,$itemRow['id']);
              //echo "openingBalance"."<br>";
 		      $in_1=receivedMRN($function_filter,$itemRow['id']);
          //echo "receivedMRN"."<br>";
 		    $out_1=issueSlip($function_filter,$itemRow['id']);
           // echo "issueSlip"."<br>";
		   $out_2=issueSiteToSite($function_filter,$itemRow['id']);		
          // echo "issuesitetosite"."<br>";
		   $in_2=receivedSiteToSite($function_filter,$itemRow['id']);
		// echo " receivesitetosite"."<br>";
		  $out_3=issueMatarialReturn($function_filter,$itemRow['id']);
		 
		   $out_4=Rgpchallan($function_filter,$itemRow['id']);
		   $out_5=Nrgp($function_filter,$itemRow['id']);
		  $in_3=receivedReturn($function_filter,$itemRow['id']);		 
		  //$in_4=return_rejection($function_filter,$myrow['id']);
		   $in_4=Rgpreturn($function_filter,$itemRow['id']);
		     $in_5=FinishedMR($function_filter,$itemRow['id']);
		    $invalue=($op+$in_1[0]+$in_2+$in_3+$in_4+$in_5);
		
		     $outvalue=($out_1+$out_2+$out_3+$out_4+$out_5);
		  $tot=trim($invalue)-trim($outvalue);
		
		
		  echo  $total_current=$tot;                  
                  // production Qty.
                   $priDqRY="SELECT sum(prodqty) as proQty from job_card_item_details where mst_itemid='".$itemRow['id']."'";
                  $priDqRYExec=DB_query($priDqRY,$db);
                  $proRow=DB_fetch_array($priDqRYExec);
                  
                  // recieve Qty.
                   $priDqRY1="SELECT sum(reciveqty) as reciveqty from finished_recieved_details where mst_itemid='".$itemRow['id']."'";
                  $priDqRYExec1=DB_query($priDqRY1,$db);
                  $proRow1=DB_fetch_array($priDqRYExec1);
                 
   ?></td>
   <td><?php  echo $proRow['proQty']-$proRow1['reciveqty']; ?></td>
   </tr>
   <?php $i++; } ?>
   
</table>
<?php } ?>
<script>
    function showsitename160(str)
{

var xmlhttp;
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
	var data=xmlhttp.responseText.split("__");	
	document.getElementById("storedata7").innerHTML=data[0];
	  }
  }
  xmlhttp.open("GET","get_sitenamedata3.php?site="+str,true);
	xmlhttp.send();

//xmlhttp.open("GET","getSort.php?s="+str+"&cat="+cat,true);

	
}
    </script>
