DROP  PROCEDURE if EXISTS shims_bill;
CREATE PROCEDURE `shims_bill`( pat_id INT (11), admission_id INT (11), mod_id VARCHAR (10), status_flag VARCHAR (1), group_by VARCHAR (1), module_type VARCHAR (1), with_pharmacy INT(11))
BEGIN

SET SESSION sql_mode = "";

SELECT
	date,
	pscd_id,
	flag,
	si_name,
	IF(status_flag!='D', SUM( gross_amt ), 0) AS gross_amt,
	SUM( cgst_amt + sgst_amt ) AS gst_amt,
	SUM( cgst_amt ) AS cgst_amount,
	SUM( sgst_amt ) AS sgst_amount,
	IF(status_flag!='D', SUM( dis_amt ), 0) AS dis_amt,
	IF(status_flag!='D', SUM( ROUND(net_amt) ), 0) AS net_amt,
	SUM( tpa_rate ) as tpa_rate,
	return_amount,
	return_discount,
	created_date,
	UPPER( perm_name ) AS billing_module_name,
	admission_no,
	CONCAT( is_loose_sold ) AS is_loose_sold,
	parent_id,
	module_id,
	SUM(loose_qty) as loose_qty,
	strip_qty,
	return_loose_quantity,
	billing_module_id,
	bill_no
FROM
	(SELECT
		pscd.service_charge_date AS date,
		psc.service_source_id AS pscd_id,
		'service' AS flag,
		IF(psch.billing_module_id=8, lam.analyte_name ,si.service_item_name) AS si_name,
		psch.patient_id,
		(CASE WHEN module_type='M' THEN psch.parent_id 
		WHEN module_type='P' THEN psch.primary_id
		WHEN module_type='B' THEN psc.service_source_id END) AS parent_id,
		(CASE WHEN module_type='M' THEN psch.parent_module_id 
		WHEN module_type='P' THEN psch.primary_module_id
		WHEN module_type='B' THEN psch.billing_module_id END) AS module_id,
		SUM(pscd.applicable_rate) AS gross_amt,
		SUM(pscd.cgst_amount) AS cgst_amt,
		SUM(pscd.sgst_amount) AS sgst_amt,
		SUM(pscd.discount_amount) AS dis_amt,
		SUM(pscd.net_amount) AS net_amt,
		SUM(pscd.tpa_rate) AS tpa_rate,
		0 AS return_amount,
		0 AS return_discount,
		pscd.created_at AS created_date,
		per.perm_name,
		psc.admission_no AS admission_no,
		1 AS loose_qty,
		'N' AS is_loose_sold,
		1 AS strip_qty,
		0 as return_loose_quantity,
		psch.billing_module_id,
		psc.bill_no
	FROM
		patient_service_charge_header AS psch
		INNER JOIN patient_service_charges AS psc ON psc.id = psch.patient_service_charge_id
		INNER JOIN patient_service_charge_details AS pscd ON psc.id = pscd.patient_service_charge_id 
		AND (CASE
				
				WHEN status_flag = 'T' THEN
				pscd.STATUS IN ( 'C', 'B', 'A', 'R' ) 
				WHEN status_flag = 'B' THEN
				pscd.STATUS IN ( 'B', 'A' ) 
				WHEN status_flag = 'C' THEN
				pscd.STATUS IN ( 'C', 'R' ) 
				WHEN status_flag = 'D' THEN
				pscd.STATUS IN ( 'D') 
			END)
			INNER JOIN service_items AS si ON si.id = pscd.service_id
			INNER JOIN permissions AS per ON per.perm_id = psch.billing_module_id
			LEFT JOIN lab_analyte_master AS lam ON lam.charge_id=pscd.service_rate_id
		WHERE
			psch.billing_module_id != 11 
		GROUP BY pscd.id
		UNION
		SELECT
			pbh.bill_date AS date,
			pbh.id AS pscd_id,
			'pharmacy' AS flag,
			items.item_name AS si_name,
			psch.patient_id,
			(CASE WHEN module_type='M' THEN psch.parent_id 
			WHEN module_type='P' THEN psch.primary_id
			WHEN module_type='B' THEN pbh.id END) AS parent_id,
			(CASE WHEN module_type='M' THEN psch.parent_module_id
			WHEN module_type='P' THEN psch.primary_module_id
			WHEN module_type='B' THEN psch.billing_module_id END) AS module_id,
			(SUM(pbi.gross_sale_price)-(IFNULL(pri.gross_return_price, 0)+IFNULL(pri.discount_amount, 0))) AS gross_amt,
			(SUM(pbi.cgst_amt)-IFNULL(pri.cgst_amt, 0)) AS cgst_amt,
			(SUM(pbi.sgst_amt)-IFNULL(pri.sgst_amt, 0)) AS sgst_amt,
			(SUM(pbi.discount_value)-IFNULL(pri.discount_amount, 0)) AS dis_amt,
			(SUM(pbi.net_sale_price)-IFNULL(pri.net_return_price, 0)) AS net_amt,
			0 AS tpa_rate,
			(SUM(IFNULL(pri.gross_return_price, 0))) AS return_amount,
			(SUM(IFNULL(pri.discount_amount, 0))) AS return_discount,
			pbh.created_at AS created_date,
			'pharmacy' AS perm_name,
			pbh.bill_no AS admission_no,
			IF(pbi.is_loose_sold='Y', pbi.sale_quantity, pbi.sale_quantity*pbi.strip_qty) as loose_qty,
			pbi.is_loose_sold AS is_loose_sold,
			pbi.strip_qty as strip_qty,
			IFNULL(pri.return_loose_quantity, 0) as return_loose_quantity,
			psch.billing_module_id,
			pbh.bill_no
		FROM
			patient_service_charge_header AS psch
			INNER JOIN pharmacy_billing_header AS pbh ON pbh.id = psch.patient_service_charge_id
			INNER JOIN pharmacy_billing_item AS pbi ON pbi.pharmacy_billing_header_id = pbh.id AND pbi.status='A'
			INNER JOIN items ON items.id = pbi.item_id 
			LEFT JOIN  (SELECT 
			pharmacy_return_items.pharmacy_billing_item_id AS pharmacy_billing_item_id,
			SUM(pharmacy_return_items.gross_return_price) AS gross_return_price,
			SUM(pharmacy_return_items.discount_amount) AS discount_amount,
			SUM(pharmacy_return_items.cgst_amt) AS cgst_amt,
			SUM(pharmacy_return_items.sgst_amt) AS sgst_amt,
			SUM(pharmacy_return_items.net_return_price) AS net_return_price,
			SUM(pharmacy_return_items.return_loose_quantity) AS return_loose_quantity
			FROM pharmacy_return_header LEFT JOIN  pharmacy_return_items ON pharmacy_return_header.id = pharmacy_return_items.pharmacy_return_header_id WHERE pharmacy_return_header.status='A' AND pharmacy_return_items.status='Y' GROUP BY pharmacy_return_items.pharmacy_billing_item_id) as pri ON  pri.pharmacy_billing_item_id=pbi.id
		WHERE
			psch.billing_module_id = 11 
		GROUP BY pbi.id, pbi.discount_value
	) AS bill 
	WHERE
		( CASE WHEN pat_id != '' THEN pat_id = patient_id ELSE 1 = 1 END ) 
		AND ( CASE WHEN admission_id != 0 THEN parent_id = admission_id ELSE 1 = 1 END ) 
		AND ( CASE WHEN mod_id != 0 THEN mod_id = module_id ELSE 1 = 1 END ) 
		AND ( CASE WHEN with_pharmacy = 0 THEN perm_name!= 'pharmacy' ELSE 1 = 1 END ) 
		AND ( CASE WHEN with_pharmacy = 2 THEN perm_name= 'pharmacy' ELSE 1 = 1 END ) 
	GROUP BY
		(CASE
				WHEN group_by = 'P' THEN
				parent_id
				WHEN group_by = 'M' THEN
				billing_module_name 
				WHEN group_by = 'S' THEN
				si_name 
				WHEN group_by = 'D' THEN
				CONCAT( created_date, '-', si_name, '-', pscd_id ) 
				WHEN group_by = 'B' THEN
				bill_no
			END) 
	ORDER BY
	billing_module_name ASC,
	date DESC;

	END;