SET @rate_flag='APPL';
SET @status_flag='T';
SET @group_by='M';

SELECT
	'IPD' module, 
	pscd.service_charge_date, 
	sr.service_item_name, 
	count(pscd.service_id)  as qty,
	0 as gross, 
	0 as disc, 
	0 gst,
	SUM(CASE when @rate_flag='TPA' THEN pscd.tpa_rate ELSE pscd.applicable_rate END) net_total
FROM
	ipd_admission ia
	INNER JOIN patient_service_charges psc ON ia.id = psc.service_source_id AND psc.billing_module_id = 6 
	INNER JOIN	patient_service_charge_details pscd ON	pscd.patient_service_charge_id = psc.id 
	INNER JOIN	service_items sr ON sr.id = pscd.service_id
	AND CASE 
		when @status_flag='T'  THEN pscd.status IN	('C', 'B', 'A')
		when @status_flag='B'  THEN pscd.status IN	('B', 'A')
		when @status_flag='C'  THEN pscd.status	IN	('C') END
where ia.id = 2
group by CASE 
		when @group_by='M'  THEN module
		when @group_by='S'  THEN sr.service_item_name 
		when @group_by='D'  THEN CONCAT(pscd.service_charge_date,'-',sr.service_item_name) END

UNION

SELECT
	'Pharmacy' module, pbh.bill_date, it.item_name, 
	SUM(pbi.sale_quantity) - sum(IFNULL(pri.return_quantity,0)) as qty,
	SUM(gross_sale_price)-SUM(IFNULL(pri.gross_return_price,0)) as gross,
	SUM(pbi.discount_value) - sum(IFNULL(pri.discount_amount,0)) as disc, 
	SUM(pbi.cgst_amt + 	pbi.sgst_amt) - sum(IFNULL(pri.cgst_amt+pri.sgst_amt,0)) as gst,
	SUM(pbi.net_sale_price) - sum(IFNULL(pri.net_return_price,0)) as net_total
	
FROM
	pharmacy_billing_header pbh
	INNER JOIN pharmacy_billing_item pbi ON pbh.id = pbi.pharmacy_billing_header_id 
	AND pbh.opd_ipd_ot_flag = 'I'
	INNER JOIN items it ON pbi.item_id = it.id
	INNER JOIN ipd_admission ia ON pbh.opd_ipd_no = ia.id 
	AND ia.id = 2
	LEFT JOIN pharmacy_return_items pri ON (
		pri.pharmacy_billing_header_id = pbi.pharmacy_billing_header_id 
		AND pri.pharmacy_billing_item_id = pbi.id 
	AND pri.STATUS IN ( 'A', 'Y' ))
group by CASE 
		when @group_by='M'  THEN module
		when @group_by='S'  THEN it.item_name
		when @group_by='D'  THEN CONCAT(pbh.bill_date,'-',it.item_name) END

UNION

SELECT
	'OT' module,
	pscd.service_charge_date, 
	sr.service_item_name, 
	count(pscd.service_id)  as qty,
	0 as gross, 
	0 as disc, 
	0 gst,
	SUM(CASE when @rate_flag='TPA' THEN pscd.tpa_rate ELSE pscd.applicable_rate END) net_total
FROM
	ot_bookings AS ob
	INNER JOIN ipd_admission AS ia ON ia.id = ob.ref_opd_ipd_id
	INNER JOIN patient_service_charges AS psc ON ia.id = ob.ref_opd_ipd_id 
	AND psc.billing_module_id = 12
	INNER JOIN patient_service_charge_details AS pscd ON pscd.patient_service_charge_id = psc.id
	INNER JOIN	service_items sr ON sr.id = pscd.service_id
	AND
CASE
		WHEN @status_flag = 'T' THEN
		pscd.STATUS IN ( 'C', 'B', 'A' ) 
		WHEN @status_flag = 'B' THEN
		pscd.STATUS IN ( 'B', 'A' ) 
		WHEN @status_flag = 'C' THEN
		pscd.STATUS IN ( 'C' ) 
END 
WHERE ia.id = 2
group by CASE 
		when @group_by='M'  THEN module
		when @group_by='S'  THEN sr.service_item_name 
		when @group_by='D'  THEN CONCAT(pscd.service_charge_date,'-',sr.service_item_name) END