
<?php
$PageSecurity = 11;
include('includes/DefinePOClass.php');
include('includes/SQL_CommonFunctions.inc');
$title = _('Stock Trial'); 

	$function_filter[]='';
	$function_filter[]='';
	$function_filter[]=date('Y-m-d'); 
	$function_filter[]=date('Y-m-d'); 
   
   //*********************   stock function ********************************//
   
      ////////////Opening 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'];
							}
                      
					   
					   

						////1. MRN/////
                     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."'";
							
							$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)'];
						}
						

							////1. Issue Slip/////
                         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)'];
							}






                                     ////2. Material return/////
						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)'];
						}
						
						
						       ////////////site to site receive//////
						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)'];
						}
						
						
						////2. Site to site transfer issue/////
						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;
							
						}
						////3. Material return to vendor///
						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 <= '".$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 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)'];
					  
					  
					  
					  }								

   
   
   //*************************************************************************//


?>
<link rel="stylesheet" href="css/colorbox.css" />
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
<script src="js/jquery.colorbox.js"></script>
<style type="text/css">
.Container{width:100%; height:auto;}
.Container .imageBox{width:22%; margin-right:1%; min-height:280px; border:#999999 1px solid; float:left; margin-bottom:20px;}
.img{margin-bottom:10px;}
</style>
<script>
$(document).ready(function(){
$(".inline").colorbox({inline:true, width:"50%"});
});
</script>
<div  class="Container">
<?php      
$mainid=$s->getField('stockmaincategory','stockmaincategoryid','FG','id');

$SQL=DB_query("select * from item_master where CompanyId='".$_SESSION['ACGCompanyId']."' and CategoryID='".$mainid."' ",$db);
while($res=DB_fetch_assoc($SQL))
{
 
  $op=openingBalance($function_filter,$res['id']);
//echo "openingBalance"."<br>";
 $in_1=receivedMRN($function_filter,$res['id']);
//echo "receivedMRN"."<br>";
 $out_1=issueSlip($function_filter,$res['id']);
//echo "issueSlip"."<br>";
 $out_2=issueSiteToSite($function_filter,$res['id']);		
//echo "issuesitetosite"."<br>";
 $in_2=receivedSiteToSite($function_filter,$res['id']);
//echo " receivesitetosite"."<br>";
 $out_3=issueMatarialReturn($function_filter,$res['id']);
//echo "issuematerialreturn"."<br>";
 $in_3=receivedReturn($function_filter,$res['id']);	
//echo "receivedReturn"."<br>";
 $out_4=Rgpchallan($function_filter,$res['id']);
//echo "Rgpchallan"."<br>";
 $out_5=Nrgp($function_filter,$res['id']);
//echo "Nrgp"."<br>";
//echo $out_6=invoice($function_filter,$res['id']);
//echo "invoice"."<br>";
 $out_7=disptch($function_filter,$res['id']);
//echo "disptch"."<br>";
 $in_4=Rgpreturn($function_filter,$res['id']);
//echo "Rgpreturn"."<br>";	 
 $in_5=FinishedMR($function_filter,$res['id']);
//echo "FinishedMR"."<br>";	
$invalue=($op+$in_1+$in_2+$in_3+$in_4+$in_5);

$outvalue=($out_1+$out_2+$out_3+$out_4+$out_5+$out_7);
$tot=$invalue-$outvalue;
 
 
 
 
 
 
 
 
 ?>
 <?php //echo $res['back_image'] ?>
    <div class="imageBox"><div style="padding:10px;"><a class='inline' href="#inline_content"><img src="images/login/<?php echo $res['front_image'] ?>" height="180" width="100%" class="img" border="0"></a>
	<table width="100%" border="0" align="center" cellpadding="2" cellspacing="0">
            <tr>
                <td width="30%"><strong>Code</strong></td>
                <td><?php echo $res['StockID'] ?></td>
            </tr>
            <tr>
                <td><strong>Name</strong></td>
                <td style="white-space:normal;"><?php echo $res['item_name'] ?></td>
            </tr>
             <tr>
                <td nowrap><strong>Net Rate</strong></td>
                <td><?php echo $res['net_rate'] ?></td>
            </tr>
            <tr>
                <td><strong>Stock</strong></td>
                <td><?php echo abs($tot); ?></td>
            </tr>
      </table></div>
  </div>
    
        <?php } ?>
		<div style="clear:both;"></div>
</div>
<div style="clear:both;"></div>
<div style='display:none;'>
			<div id='inline_content' style='padding:10px; background:#fff;'>
			<img src="images/login/<?php echo $res['front_image'] ?>" width="600" height="250">
			<p><strong>This content comes from a hidden element on this page. </strong></p>
			
			
			</div>
		</div>

<!-- This contains the hidden content for inline calls -->
		