DROP FUNCTION IF EXISTS get_item_in_out_stock;
CREATE FUNCTION get_item_in_out_stock(a_item_id INT,a_batch_no VARCHAR ( 50 ),a_flag VARCHAR ( 50 ), in_out_flag VARCHAR ( 50 ), from_date VARCHAR ( 50 ), to_date VARCHAR ( 50 )) 

RETURNS INT 
DETERMINISTIC
BEGIN
	DECLARE 		v_item_qty_int DECIMAL ( 16, 4 );
	DECLARE	v_item_qty_out DECIMAL ( 16, 4 );
	
	IF a_flag = 'S' AND in_out_flag = 'I' THEN
		SELECT
			IFNULL( sum( item_qty ), 0 ) INTO v_item_qty_int 
		FROM
			good_receipt_details,
			items 
		WHERE
			good_receipt_details.item_id = items.id 
			AND item_id = a_item_id 
			AND ( batch_number = a_batch_no OR a_batch_no = 'X' ) 
			AND inout_status IN ( 'R', 'T' ) 
			AND reserve_for_loose_sale = 'N' 
			AND stock_close_date IS NULL
			AND good_receipt_date>=from_date
			AND good_receipt_date<=to_date;
			RETURN v_item_qty_int;
			
	ELSEIF a_flag = 'S' AND in_out_flag = 'O' THEN
		SELECT
			IFNULL( sum( item_qty ), 0 ) INTO v_item_qty_out 
		FROM
			good_receipt_details 
		WHERE
			item_id = a_item_id 
			AND ( batch_number = a_batch_no OR a_batch_no = 'X' ) 
			AND inout_status IN ( 'E', 'S', 'ST' ) 
			AND reserve_for_loose_sale = 'N' 
			AND stock_close_date IS NULL
			AND good_receipt_date>=from_date
			AND good_receipt_date<=to_date;
			RETURN v_item_qty_out;
		
	ELSEIF a_flag = 'LS' AND in_out_flag = 'I' THEN
		SELECT
			IFNULL( sum( loose_item_qty ), 0 ) INTO v_item_qty_int 
		FROM
			good_receipt_details,
			items 
		WHERE
			good_receipt_details.item_id = items.id 
			AND item_id = a_item_id 
			AND ( batch_number = a_batch_no OR a_batch_no = 'X' ) 
			AND inout_status IN ( 'SR', 'T' ) 
			AND reserve_for_loose_sale = 'Y' 
			AND stock_close_date IS NULL
			AND good_receipt_date>=from_date
			AND good_receipt_date<=to_date;
			RETURN v_item_qty_int;
			
	ELSEIF a_flag = 'LS' AND in_out_flag = 'O' THEN
		SELECT
			IFNULL( sum( loose_item_qty ), 0 ) INTO v_item_qty_out 
		FROM
			good_receipt_details 
		WHERE
			item_id = a_item_id 
			AND ( batch_number = a_batch_no OR a_batch_no = 'X' ) 
			AND reserve_for_loose_sale = 'Y' 
			AND inout_status IN ( 'E', 'S', 'SE' ) 
			AND stock_close_date IS NULL
			AND good_receipt_date>=from_date
			AND good_receipt_date<=to_date;
			RETURN v_item_qty_out;
	END IF;
	
END;