DROP FUNCTION IF EXISTS get_gst_in_out;
CREATE FUNCTION get_gst_in_out(item_id INT, patient_id INT, in_out_flag VARCHAR(50), date VARCHAR(50), is_pahrma VARCHAR(50)) 

RETURNS DECIMAL(16,2) 
DETERMINISTIC
BEGIN

	DECLARE	gst_purchase DECIMAL ( 16, 4 );
	DECLARE	gst_pharmacy DECIMAL ( 16, 4 );
	
	IF in_out_flag = 'I' THEN
	
		IF is_pahrma = 'P' OR is_pahrma='A' THEN
		
			SELECT 
				SUM(IFNULL(purchase_order_return_items.cgst,0)+IFNULL(purchase_order_return_items.sgst,0)) INTO gst_purchase
				
			FROM 
				purchase_order_return_items
				
			INNER JOIN purchase_order_returns
			ON purchase_order_returns.id= purchase_order_return_items.purchase_order_return_id
			AND purchase_order_returns.`status`='A'
			
			INNER JOIN purchase_order_receipts 
			ON purchase_order_receipts.id= purchase_order_returns.po_receipt_id
			AND purchase_order_receipts.`status`='A'
			
			WHERE 
				purchase_order_return_items.`status`='Y'
				AND ( purchase_order_return_items.item_id = item_id OR item_id = '0' )
				AND ( purchase_order_receipts.supplier_id = patient_id OR patient_id = '0' )
				AND purchase_order_returns.return_date>= date
				AND purchase_order_returns.return_date<= date
				
			GROUP BY	purchase_order_returns.return_date;
			
		END IF;
		
		IF is_pahrma = 'PH' OR is_pahrma='A' THEN
		
			SELECT 
				SUM(IFNULL(pharmacy_billing_item.cgst_amt, 0)+IFNULL(pharmacy_billing_item.sgst_amt,0)) INTO gst_pharmacy
				
			FROM 
				pharmacy_billing_item
				
			INNER JOIN pharmacy_billing_header 
			ON pharmacy_billing_header.id= pharmacy_billing_item.pharmacy_billing_header_id
			AND (pharmacy_billing_header.`status`='B' OR pharmacy_billing_header.`status`='P')

			WHERE 
				pharmacy_billing_item.`status`='A'
				AND ( pharmacy_billing_item.item_id = item_id OR item_id = '0' )
				AND ( pharmacy_billing_header.patient_id = patient_id OR patient_id = '0' )
				AND pharmacy_billing_header.bill_date>= date
				AND pharmacy_billing_header.bill_date<= date
				
			GROUP BY	pharmacy_billing_header.bill_date;
			
		END IF;
			
	ELSEIF in_out_flag = 'O' THEN
		
		IF is_pahrma = 'P' OR is_pahrma='A' THEN
	
			SELECT 
				SUM(IFNULL(purchase_order_receipt_items.cgst,0)+IFNULL(purchase_order_receipt_items.sgst,0)) INTO gst_purchase
			
			FROM 
				purchase_order_receipt_items
				
			INNER JOIN purchase_order_receipts 
			ON purchase_order_receipts.id= purchase_order_receipt_items.po_receipt_id
			AND purchase_order_receipts.`status`='A'
			
			WHERE 
				purchase_order_receipt_items.`status`='Y'
				AND ( purchase_order_receipt_items.item_id = item_id OR item_id = '0' )
				AND ( purchase_order_receipts.supplier_id = supplier_id OR supplier_id = '0' )
				AND purchase_order_receipts.purchase_date>= date
				AND purchase_order_receipts.purchase_date<= date
				
			GROUP BY	purchase_order_receipts.purchase_date;
			
		END IF;
		
		IF is_pahrma = 'PH' OR is_pahrma='A' THEN
		
			SELECT 
				SUM(IFNULL(pharmacy_return_items.cgst_amt,0)+IFNULL(pharmacy_return_items.sgst_amt,0)) INTO gst_pharmacy
				
			FROM 
				pharmacy_return_items
				
				INNER JOIN pharmacy_return_header
				ON pharmacy_return_header.id= pharmacy_return_items.pharmacy_return_header_id
				AND pharmacy_return_header.`status`='A'

				WHERE 
					pharmacy_return_items.`status`='Y'
					AND ( pharmacy_return_items.item_id = item_id OR item_id = '0' )
					AND ( pharmacy_return_header.patient_id = patient_id OR patient_id = '0' )
					AND pharmacy_return_header.return_date>= date
					AND pharmacy_return_header.return_date<= date
					
				GROUP BY	pharmacy_return_header.return_date;
				
			END IF;
			
	END IF;
	
	RETURN IFNULL(gst_pharmacy,0)+IFNULL(gst_purchase,0);
	
END;