<?php session_name('CAKEPHP'); session_start();
$PageSecurity = 4;
$PageName = 'Add Items';

include('includes/DefinePOClass.php');
include('includes/session.inc');
include('includes/SQL_CommonFunctions.inc');
include('includes/DefineSuppTransClass.php');
//include('includes/header.inc');
include('includes/function_lib.php');

$function_filter[]=$_REQUEST['site'];;
//$function_filter[]=$_REQUEST['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; 
$function_filter[]=$date1;
//$function_filter[]=$_REQUEST['store'];


                  function openingBalance($function_filter,$item)
							{
								global $db;
								$site=$function_filter[0];
								$from=$function_filter[1];
															
								if($site!='')
								$filter[]="site = '".$site."'";
								if($from!='--' && $to!='--')
								$filter[]="reveive_date <= '".$from."'";
								if($item!='')
								$filter[]="mstid = '".$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'];
							}
                       ////////////Opening Balance End/////////////////////////
					   
					   

						////1. MRN/////
                    function receivedMRN($function_filter,$item)
						{
							global $db;
							$site=$function_filter[0];
							$from=$function_filter[1];
							$to=$function_filter[2];
							if($site!='')
							$filter[]="addmrn.site = '".$site."'";
							if($item!='')
							$filter[]="addmrnqty.mst_itemid = '".$item."'";
							if($from!='--' && $to!='--')
							$filter[]="addmrn.MRNDate  <='".$from."'";
							$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];
								$from=$function_filter[1];
								$to=$function_filter[2];
								if($site!='')
								$filter[]="addissue.site = '".$site."'";
								if($item!='')
								$filter[]="issueqty.mst_itemid = '".$item."'";
								if($from!='--' && $to!='--')
	                       $filter[]="addissue.issuedate <='".$from."' ";
								
								$filter=implode(' and ',$filter);
							
							 	$sql1="Select SUM(issueqty.issueqty) from addissue left join 
								 issueqty on(addissue.id=issueqty.issue_id) where  cancel_issue=0 and $filter";
								$result1=DB_query($sql1,$db);
								$myrow1=DB_fetch_array($result1);
								
								return $myrow1['SUM(issueqty.issueqty)'];
							}




/// added new function 

////2. Material return/////
function receivedReturn($function_filter,$item)
{
	global $db;
	$site=$function_filter[0];
	$from=$function_filter[1];
	$to=$function_filter[2];
	
	if($site!='')
	$filter[]="material_return.site = '".$site."'";
	if($from!='--' && $to!='--')
	$filter[]="material_return.returndate <='".$from."' ";
	if($item!='')
	$filter[]="material_return_details.mst_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];
	$from=$function_filter[1];
	$to=$function_filter[2];
	
	if($site!='')
	$filter[]="transfer_receipt.sitecode = '".$site."'";
	if($from!='--' && $to!='--')
	$filter[]="transfer_receipt.MRNDate <='".$from."'";
	if($item!='')
	$filter[]="transfer_receipt_details.mst_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)'];
}
//////////Total received end//////////////////////////

/* Calculate total issued*/
////1. Issue Slip/////

////2. Site to site transfer issue/////
function issueSiteToSite($function_filter,$item)
{
	global $db;
 
	$site=$function_filter[0];	
	$from=$function_filter[1];
	$to=$function_filter[2];
	
	if($site!='')
	$filter[]="transfer_issue.sitecode = '".$site."'";	
	if($from!='--' && $to!='--')
	$filter[]="transfer_issue.issuedate <='".$from."'";
	if($item!='')
	$filter[]="transfer_issue_details.mst_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];
	$from=$function_filter[1];
	$to=$function_filter[2];
	
	if($site!='')
	$filter[]="inv_material_return.sitecode = '".$site."'";
	if($from!='--' && $to!='--')
	$filter[]="inv_material_return.MRNDate <='".$from."'";
	if($item!='')
	$filter[]="inv_material_return_details.mst_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)'];
}
	






if($_REQUEST['show']!='Show')
{
unset($_SESSION['ACGRequisition-ItemList']);
unset($_SESSION['ACGRequisition-ItemList_1']);
}
?>
<script src="js/function-ajax.js"></script>

<link href="css/professional/default.css" rel="stylesheet" type="text/css" />
 <h2 align="center">Search ITEMS</h2>
<form action="" method="post" >
<table align="center" border="0">
<tr> <td><strong>Item main category </strong></td>
<td>
<?php  
 $item_query="select stockmaincategoryname, stockmaincategoryid from stockmaincategory where stockmaincategoryid != 'FG' ";
 $res=DB_query($item_query ,$db);
  //echo DB_num_rows($res);
?>
<select name="CategoryID"  onchange="ShowDiv(this.value,'hid-subcatitem','disp_subcatitem.php')" >
	<option value="0">Select</option>
<?php	
		while($item_row1=DB_fetch_array($res))
		{?>
		 <option value="<?php echo $item_row1['stockmaincategoryid'];?>" <?php echo $_REQUEST['CategoryID']==$item_row1['stockmaincategoryid']?'selected':'' ;?>>
		 <?php echo $item_row1['stockmaincategoryname'];?></option>
          <?php 	} ?>
        </select>
		</td>
</tr>
<tr><td><strong>Item sub category</strong></td>
<td>
<div id='hid-subcatitem'>
<select name="SubCategoryID">
<option value="0">Select</option>
<?php $item_query1="select categoryid, categorydescription from stockcategory ";
		$res1=DB_query($item_query1 ,$db);
		while($item_row2=DB_fetch_array($res1))
		{?>
<option value="<?php echo $item_row2['categoryid'];?>" <?php echo $_REQUEST['SubCategoryID']==$item_row2['categoryid']?'selected':'' ;?>><?php echo $item_row2['categorydescription'];?></option>
<?php } ?></select>
</div></td>
</tr>
<tr><td><strong>Item code</strong></td>
  <td><input name="StockID" type="text" id="StockID" size="25" /></td>
  </tr>
<tr><td colspan="2"><input type="submit" value="Show" name="show" /></td></tr>
</table>

<?php 
if($_REQUEST['Select']!='')
{
	
	 
	$item_array=array();
	$item_array=$_REQUEST['row_id'];
 		$_SESSION['ACGRequisition-ItemList']=$item_array;
 	 if(isset($_SESSION['ACGRequisition-ItemList']))
		$_SESSION['ACGRequisition-ItemList']=array_unique($_SESSION['ACGRequisition-ItemList']);
	
	

	if(!empty($_SESSION['ACGRequisition-ItemList']))
	{
	$itemlist=implode("#",$_SESSION['ACGRequisition-ItemList']);
	$_SESSION['EditSessionItems']=$itemlist;
	 
	}
	 
	  
if(count($_SESSION['ACGRequisition-ItemList'])==0)
{
	unset($_SESSION['ACGRequisition-ItemList']);
	unset($_SESSION['EditSessionItems']);
 
}
}

 ?>

<script type="text/javascript">
function ExitWindow()
{
 
opener.window.location="index.php?route=Edit_DirectpurchBillBook&action=Edit&editId=<?php echo $_REQUEST['editId']; ?>&modid=<?php echo $_REQUEST['modid'];?>&frmid=<?php echo $_REQUEST['frmid'];?>&BillBook=Yes";self.close();
}
</script>


 

  <?php
 //---------------------------Search Starts here-------------------------------
if(isset($_REQUEST['show']))
{
  $_SESSION['ACGRequisition-ItemList']=array();


		$sql_search='';
		$CategoryID=$_REQUEST['CategoryID'];
		$SubCategoryID=$_REQUEST['SubCategoryID'];
		$StockID=$_REQUEST['StockID'];
		$flexo_offset=$_REQUEST['flexo_offset'];
		
		$sql_search = "select * from item_master where 1=1 ";
		
		
		if(!empty($CategoryID))
		{
			$sql_search .=" and  CategoryID = '".$CategoryID."'";
		} 
		if($SubCategoryID!='0')
		{
			$sql_search .=" and SubCategoryID = '".$SubCategoryID."'";
		}
		if(!empty($StockID))
		{
			$sql_search .=" and StockID LIKE '%".$StockID."%'";
		}
		if(!empty($flexo_offset))
		{
			$sql_search .=" and flexo_offset = '".$flexo_offset."'";
		}
		
		 //echo $sql_search ;
			$search_result = DB_query($sql_search, $db);
			//$search_array=DB_fetch_array($search_result);
			//print_r($search_array);
			//echo $sql_search;
	
			
}?>

 <input type="hidden" value="<?php print_r($_SESSION['ACGRequisition-ItemList']);?>" id="arr" />
<table width="100%" border="0">
	<tr><td><input style="margin:0px 0px 10px 0px;" type="submit" name="Select" id="Select" value="Add To List"></td>
		<td><div align="right"><input style="margin:0px 0px 10px 0px;" type="button" name="Exit" id="Exit" value="Exit" onclick="ExitWindow()" ></div>
		</td>
	</tr></table>
<input type="hidden" name="date" value="<?php echo $_SESSION['date'];?>" />
<input type="hidden" name="site" value="<?php echo $_SESSION['site'];?>" />
<input type="hidden" name="requisition_no" value="<?php echo $_SESSION['requisition_no'];?>" />
<input type="hidden" name="remarks" value="<?php echo $_SESSION['remarks'];?>" />

  <table width="100%" border="0" cellspacing="3" cellpadding="2" align="center">
        <tr>
          <th width="13%"><strong>SELECT</strong></th>
          <th width="32%"><strong>ITEM CODE</strong></th>
          <th width="34%"><strong>ITEM NAME</strong></th>
          <th width="21%"><strong>UNIT</strong></th>
		   <th width="21%"><strong>Stock</strong></th>
        </tr>
<?php
$i=0;
  	if($i%2==0)
	{$bg="#CCCCCC";}
	else
	$bg="#C8BECB";

if(isset($_REQUEST['show']))
{

	if(DB_num_rows($search_result)>0)
	{
	 	while($myrow = DB_fetch_array($search_result))
	 	{
	 		$c='';
			 
			if( in_array($myrow['id'],$_SESSION['ACGRequisition-ItemList']) && count($_SESSION['ACGRequisition-ItemList'])>0) 
			{
					
			  	$c="checked";
 				$f=0;
			}
			
			
		       $op=openingBalance($function_filter,$myrow['id']);
			
		      $in_1=receivedMRN($function_filter,$myrow['id']);
			
		     $out_1=issueSlip($function_filter,$myrow['id']);
			 
             $out_2=issueSiteToSite($function_filter,$myrow['id']);	
			 $in_2=receivedSiteToSite($function_filter,$myrow['id']);
		     $out_3=issueMatarialReturn($function_filter,$myrow['id']);
		     $in_3=receivedReturn($function_filter,$myrow['id']);		 
		    // $in_4=return_rejection($function_filter,$myrow['id']);
		     $invalue=($op+$in_1+$in_2+$in_3);
		     $outvalue=($out_1+$out_2+$out_3);
		     $tot=trim($invalue)-trim($outvalue);		 
		     $total_current=$tot;	
		 
		 
   		//$total_current=number_format(($op+$in_1+$in_2+$in_3)-($out_1+$out_2+$out_3),2);
		
			
			
			
			
	 	?>
        <?php 	
  $parameter=$myrow['id'];
  ?>
        <tr bgcolor="<?php echo $bg;?>">
		 
		  
          <td> <input type="checkbox" name="row_id[<?php echo $i;?>]" id="row_id_<?php echo $i;?>" value="<?php echo $parameter;?>"<?php echo $c;?> /></td>
          <td><strong><?php echo $myrow['StockID'];?></strong></td>
          <td><?php echo $myrow['item_name'];?></td>
          <td><?php $unit=$s->getField('unitsofmeasure','unitid',$myrow['unit'],'unitname'); echo $unit;?></td>
		  <td><?php echo $total_current; ?></td>
        </tr>
        <?php $i++;
	 } 
  }
	else
	{?>
	 <tr>
      <td colspan="9"><h3 align="center">No Items Found</h3></td>
    </tr>
        <?php 
	}
?>
     
 <?php } //----------------end of Search---------------- 
else 
{
   	$sql=DB_query("Select * from item_master where CategoryID!='FG'",$db); 	
	if(DB_num_rows($sql)>0)
	{
		while($myrow=DB_fetch_array($sql))
	  	{
		$c='';
		  if(isset($_SESSION['ACGRequisition-ItemList']))
		  {
			if( in_array($myrow['id'],$_SESSION['ACGRequisition-ItemList'])) 
			{
					
			  	$c="checked";
 			 
			} 	
		 }	
		 
		     $op=openingBalance($function_filter,$myrow['id']);
		    $in_1=receivedMRN($function_filter,$myrow['id']);
		     $out_1=issueSlip($function_filter,$myrow['id']);
             $out_2=issueSiteToSite($function_filter,$myrow['id']);	
			 $in_2=receivedSiteToSite($function_filter,$myrow['id']);
		     $out_3=issueMatarialReturn($function_filter,$myrow['id']);
		     $in_3=receivedReturn($function_filter,$myrow['id']);		 
		   //  $in_4=return_rejection($function_filter,$myrow['id']);
		     $invalue=($op+$in_1+$in_2+$in_3);
		     $outvalue=($out_1+$out_2+$out_3);
		     $tot=trim($invalue)-trim($outvalue);		 
		     $total_current=$tot;	
		
	 	 $parameter=$myrow['id'];
  ?>
 <tr bgcolor="<?php echo $bg;?>">
      <td width="13%"><input type="checkbox" name="row_id[<?php echo $i;?>]2" id="row_id_<?php echo $i;?>" value="<?php echo $parameter;?>"<?php echo $c;?> /></td>
      <td width="32%"><strong><?php echo $myrow['StockID'];?></strong></td>
      <td width="34%"><?php echo $myrow['item_name'];?></td>
  
       <td width="21%"><?php $unit=$s->getField('unitsofmeasure','unitid',$myrow['unit'],'unitname'); echo $unit;?></td>
        <td><?php echo $total_current; ?></td>
    </tr>
<?php $i++;
		} 
}?>
<input type="hidden" name="row_sount" id="row_count" value="<?php echo $i;?>">
</table>
 
<?php } 
 ?>
  </form>

