<?php include "configuration.inc.php";?>
<?php

if ( !isset($_REQUEST['term']) )
    exit;
$rs = mysql_query('select item_id,item_name,unit_id,unit_name,sell_price,pur_price as unit_price,sum(pur_qty) as pur_qty,hsn_code from view_purchase_det where item_name like "%'. $_REQUEST['term'] .'%" group by item_id,item_name,unit_id,unit_name,sell_price order by item_name');

$data = array();

if ( $rs && mysql_num_rows($rs) )
{

$data[] = array(
            'label' =>'<table border="0" cellpadding="2" cellspacing="1" style="width:100%">
			<tr style="color:#fff;"><td style="background:#336666" nowrap>Item Name</td><td style="background:#336666">Unit</td><td style="background:#336666">Price</td><td style="background:#336666" nowrap>Avail Qty</td></tr></table> ' ,'value' => ''
           
        ); 
			
    while( $row = mysql_fetch_array($rs, MYSQL_ASSOC) )
    {
	$index=$row['item_id'];
	$item_id=$row["item_id"];
					$unit_id=$row["unit_id"];
					$sell_price=$row["sell_price"];
					$pur_qty=$row["pur_qty"];
					
					$sqlret="select sum(ret_qty) as purret_qty from view_purchasereturn_det where item_id='$item_id' and unit_id='$unit_id' and sell_price='$sell_price'";
					$exeret=mysql_query($sqlret);
					$rowsret=mysql_fetch_assoc($exeret);
					$purret_qty=$rowsret["purret_qty"];
					
					$sqlsel="select sum(qty) as sale_qty from view_sales_mstrdet where item_id='$item_id' and unit_id='$unit_id' and unit_price='$sell_price' and cancel_status=0";
					$exesel=mysql_query($sqlsel);
					$rowssel=mysql_fetch_assoc($exesel);
					$sale_qty=$rowssel["sale_qty"];
					
					$sqlselret="select sum(ret_qty) as saleret_qty from view_saleret_masdet where item_id='$item_id' and unit_id='$unit_id' and sell_price='$sell_price'";
					$exeselret=mysql_query($sqlselret);
					$rowsselret=mysql_fetch_assoc($exeselret);
					$saleret_qty=$rowsselret["saleret_qty"];
					
					$sqldem="select sum(dem_qty) as dem_qty from tbl_itemdamage_details	 where item_id='$item_id' and unit_id='$unit_id' and sell_price='$sell_price'";
					$exedem=mysql_query($sqldem);
					$rowsdem=mysql_fetch_assoc($exedem);
					$dem_qty=$rowsdem["dem_qty"];
					
					
						$stock=($pur_qty+$saleret_qty)-($purret_qty+$sale_qty+$dem_qty);
						
						if($stock>0)
						{	
	
	
    $data[] = array(
            'label' =>'<table border="0" cellpadding="2" cellspacing="1"  style="width:100%"><tr><td>'. $row['item_name'] .'</td><td>'. $row['unit_name'] .'</td><td>'. $row['sell_price'] .'</td><td>'. $stock .'</td></tr></table> ' ,
            'id' => $index,
			'value' => $row['item_name'],
			'unit_id' => $row['unit_id'],
			'unit_name'=>$row['unit_name'],
			'sell_price'=>$row['sell_price'],	
			'tot_qty'=>$stock,
			'unit_price'=>$row['unit_price'],
			'hsn_code'=>$row['hsn_code'],			
        );
    }
	}
	
}

echo json_encode($data);
flush();

?>