<?php
include('db_con.php');
$debit = 0;
$credit = 0;
$total_stock = 0;
$item_no = 0;
 if(isset($_POST['item_no']))
	{
		$item_no = $_POST['item_no'];
		
		$query = "
		SELECT * FROM items WHERE item_id = :item_no
		";
		$statement = $connect->prepare($query);
		$statement->execute(
			array(
				':item_no'	=>	$item_no
			)
		);
		$result = $statement->fetchAll();
		foreach($result as $row)
		{
		
			$output['retail_price'] = $row['retail_price'];
			$output['list_price'] = $row['list_price'];		
      $output['item_cost'] = $row['item_cost'];
      $output['dimension'] = $row['dimension'];
      $output['location'] = $row['location'];    
			
			
		}
      
		 	$sql = "SELECT IFNULL(sum(qty),0) as purchase FROM purchase_detail WHERE item_no ='".$item_no."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that out
           $purchase = $statement->fetchColumn();
           if($purchase > 0)
           {
           	$debit = $debit + $purchase;
           }
           $sql = "SELECT IFNULL(sum(qty),0) as sale FROM sale_detail WHERE item_no ='".$item_no."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that out
           $sale = $statement->fetchColumn();
           if($sale > 0)
           {
           	$credit = $credit + $sale;
           }
           $sql = "SELECT IFNULL(sum(qty),0) as pr FROM sr_detail WHERE item_no ='".$item_no."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); 
           $sr = $statement->fetchColumn();
           if($sr > 0)
           {
           	$debit = $debit + $sr;
           }
            $sql = "SELECT IFNULL(sum(qty),0) as pr FROM pr_detail WHERE item_no ='".$item_no."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); 
           $pr = $statement->fetchColumn();
           if($pr > 0)
           {
           	$credit = $credit + $pr;
           }
           $sql = "SELECT IFNULL(sum(qty_out),0)  FROM adjusted_stock WHERE item_no ='".$item_no."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); 
           $pr = $statement->fetchColumn();
           if($pr > 0)
           {
            $credit = $credit + $pr;
           }
             $sql = "SELECT IFNULL(sum(qty_in),0) FROM adjusted_stock WHERE item_no ='".$item_no."' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); 
           $sr = $statement->fetchColumn();
           if($sr > 0)
           {
            $debit = $debit + $sr;
           }
         
            
         $stock = $debit - $credit;
            
            $output['stock'] = $stock;
		echo json_encode($output);
	}
		
	
	
?>