DROP PROCEDURE IF EXISTS `sp_ipd_bill`;
CREATE PROCEDURE `sp_ipd_bill`( module_id INT (11), ipd_id INT (11), rate_flag VARCHAR (10), status_flag VARCHAR (1), group_by VARCHAR (1) )
BEGIN

SET SESSION sql_mode = ""; -- CR ID-6-220131

CASE module_id 
	WHEN 7 THEN	/*7-Only Registration*/
		SELECT
			'Registration' module, 
			pscd.service_charge_date, 
			sr.service_item_name, 
			count(pscd.service_id)  as qty,
			SUM(pscd.applicable_rate) as gross,
			psc.discount_amount as disc, 			
			psc.due_amount as due,
			SUM(pscd.cgst_amount + pscd.sgst_amount) as gst,
			SUM(pscd.net_amount) as net_total,
			is_tpa_charge,
			is_approved_by_tpa,
			SUM(tpa_rate) as final_tpa_charge, 
			psc.refund_amount -- CR ID-6-220131
		FROM
			patients ia
						INNER JOIN (Select max(prl.id) id, prl.patient_id patient_id from patient_registration_log prl group by prl.id)  pr on pr.patient_id =   ia.id
INNER JOIN patient_service_charges psc ON pr.id = psc.service_source_id AND psc.billing_module_id = 7
			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', 'R')
				when status_flag='B'  THEN pscd.status IN	('B', 'A')
				when status_flag='C'  THEN pscd.status	IN	('C', 'R') END
		where pr.id = ipd_id
		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
		ORDER BY	service_charge_date, module;

	WHEN 6 THEN	/*6-IPD*/
		SELECT
			'IPD' module, 
			pscd.service_charge_date, 
			sr.service_item_name, 
			count(pscd.service_id)  as qty,
			SUM(pscd.applicable_rate) as gross,
			psc.discount_amount as disc, 			
			psc.due_amount as due,
			SUM(pscd.cgst_amount + pscd.sgst_amount) as gst,
			SUM(pscd.net_amount) as net_total,
			is_tpa_charge,
			is_approved_by_tpa,
			SUM(tpa_rate) as final_tpa_charge, 
			psc.refund_amount -- CR ID-6-220131
		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', 'R')
				when status_flag='B'  THEN pscd.status IN	('B', 'A')
				when status_flag='C'  THEN pscd.status	IN	('C', 'R') END
		where ia.id = ipd_id
		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 module, bill_date, item_name, 
				round(sum(sale_quantity - return_quantity) / strip_qty, 2) as qty,
				sum(gross_sale_price - gross_return_price - return_disc) as gross,
				sum(discount_value - return_disc) as disc,
				round(sum(due)/sum(count_row)) as due,
				sum(sell_gst - return_gst) as gst,
				sum(net_sale_price - net_return_price) as net_total,
				'N', 'N', 0.00, 
				refund_amount -- CR ID-6-220131
				FROM 
				(
					SELECT
								'Pharmacy' module, pbh.bill_date, it.item_name,pbi.strip_qty,COUNT(*) as `count_row`, 
								
								case when pbi.is_loose_sold = 'Y' then pbi.sale_quantity
								else pbi.sale_quantity*pbi.strip_qty
								end as sale_quantity, 
								
								pbh.due_amount as due,
								sum(IFNULL(pri.return_quantity,0)) as return_quantity,
								gross_sale_price, 
								SUM(IFNULL(pri.return_gross_price,0)) as gross_return_price,
								pbi.discount_value,
								sum(IFNULL(pri.return_discount_amount,0)) as return_disc, 
								pbi.cgst_amt + 	pbi.sgst_amt as sell_gst,
								sum(IFNULL(pri.return_cgst+pri.return_sgst,0)) as return_gst,
								pbi.net_sale_price,
								sum(IFNULL(pri.return_net_price,0)) as net_return_price,
								pbh.refund_amount -- CR ID-6-220131
							FROM
								pharmacy_billing_header pbh
								INNER JOIN pharmacy_billing_item pbi ON pbh.id = pbi.pharmacy_billing_header_id
								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 = ipd_id
								/*
								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')))
								*/
								left join 	vw_pharmacy_return_details	pri on (pri.pharmacy_billing_header_id = pbi.pharmacy_billing_header_id AND pri.pharmacy_billing_item_id = pbi.id)

							WHERE pbh.opd_ipd_no = ipd_id
							AND pbh.opd_ipd_ot_flag = 'I' 
							group by pbh.id, pbi.id, pbi.item_id, pbh.due_amount
				) bill_dtl
		group by 
		CASE 
				when group_by='M'  THEN module
				when group_by='S'  THEN item_name
				when group_by='D'  THEN CONCAT(bill_date,'-',item_name) END
		
		having qty != 0
		
		UNION
		/*OT Service bill for IPD*/
		SELECT
			'OT' module,
			pscd.service_charge_date, 
			sr.service_item_name, 
			count(pscd.service_id)  as qty,
			SUM(pscd.applicable_rate) as gross,
			psc.discount_amount as disc, 			
			psc.due_amount as due,
			SUM(pscd.cgst_amount + pscd.sgst_amount) as gst,
			SUM(pscd.net_amount) as net_total,
			is_tpa_charge,
			is_approved_by_tpa,
			SUM(tpa_rate) as final_tpa_charge, 
			psc.refund_amount -- CR ID-6-220131
		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 psc.service_source_id = ob.id AND ob.ref_opd_ipd_flag = 'I'
			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', 'R' ) 
				WHEN status_flag = 'B' THEN
				pscd.STATUS IN ( 'B', 'A' ) 
				WHEN status_flag = 'C' THEN
				pscd.STATUS IN ( 'C', 'R' ) 
		END 
		WHERE ia.id = ipd_id
		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
		
		/*calculate OT Pharmacy*/
		Select module, bill_date, item_name, 
				round(sum(sale_quantity - return_quantity) / strip_qty, 2) as qty,
				sum(gross_sale_price - gross_return_price - return_disc) as gross,
				sum(discount_value - return_disc) as disc,
				sum(due) as due, 
				sum(sell_gst - return_gst) as gst,
				sum(net_sale_price - net_return_price) as net_total,
				'N', 'N', 0.00, 
				refund_amount -- CR ID-6-220131
				FROM 
				(
					SELECT
								'OT Pharmacy' module, pbh.bill_date, it.item_name,pbi.strip_qty,
								
								case when pbi.is_loose_sold = 'Y' then pbi.sale_quantity
								else pbi.sale_quantity*pbi.strip_qty
								end as sale_quantity, 
								
								pbh.due_amount as due,
								sum(IFNULL(pri.return_quantity,0)) as return_quantity,
								gross_sale_price, 
								SUM(IFNULL(pri.return_gross_price,0)) as gross_return_price,
								pbi.discount_value,
								sum(IFNULL(pri.return_discount_amount,0)) as return_disc, 
								pbi.cgst_amt + 	pbi.sgst_amt as sell_gst,
								sum(IFNULL(pri.return_cgst+pri.return_sgst,0)) as return_gst,
								pbi.net_sale_price,
								sum(IFNULL(pri.return_net_price,0)) as net_return_price,
								pbh.refund_amount -- CR ID-6-220131
							FROM
								pharmacy_billing_header pbh
								INNER JOIN pharmacy_billing_item pbi ON pbh.id = pbi.pharmacy_billing_header_id
								INNER JOIN items it ON pbi.item_id = it.id
								INNER JOIN ot_bookings ob ON ob.id = pbh.opd_ipd_no AND ob.ref_opd_ipd_flag = 'I'
								INNER JOIN ipd_admission ia ON ia.id  = ob.ref_opd_ipd_id AND ia.id = ipd_id AND ob.ref_opd_ipd_flag = 'I'
								
								/*
								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')))
								*/
								left join 	vw_pharmacy_return_details	pri on (pri.pharmacy_billing_header_id = pbi.pharmacy_billing_header_id AND pri.pharmacy_billing_item_id = pbi.id)
							
							WHERE ia.id = ipd_id
							AND pbh.opd_ipd_ot_flag = 'T' 
							group by pbh.id, pbi.id, pbi.item_id, pbh.due_amount
				) bill_dtl
		group by 
		CASE 
				when group_by='M'  THEN module
				when group_by='S'  THEN item_name
				when group_by='D'  THEN CONCAT(bill_date,'-',item_name) END
		
		having qty != 0

		UNION 

		SELECT
			'Lab' module, 
			pscd.service_charge_date, 
			lam.analyte_name service_item_name, 
			count(pscd.service_id)  as qty,
			/*
			SUM(pscd.applicable_rate) as gross,
			*/
			SUM(pscd.standard_rate) as gross,
			SUM(pscd.discount_amount) as disc, 
			psc.due_amount as due,
			SUM(pscd.cgst_amount + pscd.sgst_amount) as gst,
			SUM(pscd.net_amount) as net_total,
			is_tpa_charge,
			is_approved_by_tpa,
			SUM(tpa_rate) as final_tpa_charge, 
			psc.refund_amount -- CR ID-6-220131
		FROM
			lab_booking lb
			INNER JOIN lab_booking_details lbd ON lb.id = lbd.lab_booking
			INNER JOIN lab_analyte_master lam ON lam.id = lbd.lab_analyte_id
			INNER JOIN patient_service_charges psc ON lb.id = psc.service_source_id AND psc.billing_module_id = 8 
			
			INNER JOIN	patient_service_charge_details pscd ON	pscd.patient_service_charge_id = psc.id AND pscd.service_id = lam.service_item_id and pscd.service_rate_id = lam.charge_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', 'R')
				when status_flag='B'  THEN pscd.status IN	('B', 'A')
				when status_flag='C'  THEN pscd.status	IN	('C', 'R') END
		where lb.op_ip_flag = 'I' AND lb.op_ip_id = ipd_id
		group by CASE 
				when group_by='M'  THEN module
				when group_by='S'  THEN lam.analyte_name
				when group_by='D'  THEN CONCAT(pscd.service_charge_date,'-',lam.analyte_name) END
		ORDER BY	service_charge_date, module;
	
	WHEN 3 THEN	/*3-OPD*/
		SELECT
			'OPD' module, 
			pscd.service_charge_date, 
			sr.service_item_name, 
			count(pscd.service_id)  as qty,
			SUM(pscd.applicable_rate) as gross,
			psc.discount_amount as disc, 
			psc.due_amount as due,
			SUM(pscd.cgst_amount + pscd.sgst_amount) as gst,
			SUM(pscd.net_amount) as net_total,
			is_tpa_charge,
			is_approved_by_tpa,
			SUM(tpa_rate) as final_tpa_charge, 
			psc.refund_amount -- CR ID-6-220131
		FROM
			opd_doctor_appointments oda
			INNER JOIN patient_service_charges psc ON oda.id = psc.service_source_id AND psc.billing_module_id = 3 
			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', 'R')
				when status_flag='B'  THEN pscd.status IN	('B', 'A')
				when status_flag='C'  THEN pscd.status	IN	('C', 'R') END
		where oda.id = ipd_id
		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 module, bill_date, item_name, 
				round(sum(sale_quantity - return_quantity) / strip_qty, 2) as qty,
				sum(gross_sale_price - gross_return_price  - return_disc) as gross,
				sum(discount_value - return_disc) as disc,
				round(sum(due)/sum(count_row)) as due,
				sum(sell_gst - return_gst) as gst,
				sum(net_sale_price - net_return_price) as net_total,
				'N', 'N', 0.00, 
				refund_amount -- CR ID-6-220131
				FROM 
				(
					SELECT
								'Pharmacy' module, pbh.bill_date, it.item_name,pbi.strip_qty, COUNT(*) as `count_row`, 
								
								case when pbi.is_loose_sold = 'Y' then pbi.sale_quantity
								else pbi.sale_quantity*pbi.strip_qty
								end as sale_quantity,
							
								pbh.due_amount as due,
								sum(IFNULL(pri.return_quantity,0)) as return_quantity,
								gross_sale_price, 
								SUM(IFNULL(pri.return_gross_price,0)) as gross_return_price,
								pbi.discount_value,
								sum(IFNULL(pri.return_discount_amount,0)) as return_disc, 
								pbi.cgst_amt + 	pbi.sgst_amt as sell_gst,
								sum(IFNULL(pri.return_cgst+pri.return_sgst,0)) as return_gst,
								pbi.net_sale_price,
								sum(IFNULL(pri.return_net_price,0)) as net_return_price,
								pbh.refund_amount -- CR ID-6-220131
							FROM
								pharmacy_billing_header pbh
								INNER JOIN pharmacy_billing_item pbi ON pbh.id = pbi.pharmacy_billing_header_id
								INNER JOIN items it ON pbi.item_id = it.id
								INNER JOIN opd_doctor_appointments oda ON pbh.opd_ipd_no = oda.id AND oda.id = ipd_id
								
								/*
								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')))
								*/

								left join 	vw_pharmacy_return_details	pri on (pri.pharmacy_billing_header_id = pbi.pharmacy_billing_header_id AND pri.pharmacy_billing_item_id = pbi.id)
								
							WHERE pbh.opd_ipd_no = ipd_id
							AND pbh.opd_ipd_ot_flag = 'O' 
							group by pbh.id, pbi.id, pbi.item_id, pbh.due_amount
				) bill_dtl
		group by 
		CASE 
				when group_by='M'  THEN module
				when group_by='S'  THEN item_name
				when group_by='D'  THEN CONCAT(bill_date,'-',item_name) END
		
		having qty != 0
		UNION

		SELECT
			'Lab' module, 
			pscd.service_charge_date, 
			lam.analyte_name service_item_name, 
			count(pscd.service_id)  as qty,
			/*
			SUM(pscd.applicable_rate) as gross,
			*/
			SUM(pscd.standard_rate) as gross,
			SUM(pscd.discount_amount) as disc, 
			psc.due_amount as due,
			SUM(pscd.cgst_amount + pscd.sgst_amount) as gst,
			SUM(pscd.net_amount) as net_total,
			is_tpa_charge,
			is_approved_by_tpa,
			SUM(tpa_rate) as final_tpa_charge, 
			psc.refund_amount -- CR ID-6-220131
		FROM
			lab_booking lb
			INNER JOIN	lab_booking_details lbd ON lb.id = lbd.lab_booking
			INNER JOIN	lab_analyte_master lam ON lam.id = lbd.lab_analyte_id
			INNER JOIN	patient_service_charges psc ON lb.id = psc.service_source_id AND psc.billing_module_id = 8 

			INNER JOIN	patient_service_charge_details pscd ON	pscd.patient_service_charge_id = psc.id AND pscd.service_id = lam.service_item_id and pscd.service_rate_id = lam.charge_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', 'R')
				when status_flag='B'  THEN pscd.status IN	('B', 'A')
				when status_flag='C'  THEN pscd.status	IN	('C', 'R') END
		where lb.op_ip_flag = 'O' AND lb.op_ip_id = ipd_id
		group by CASE 
				when group_by='M'  THEN module
				when group_by='S'  THEN lam.analyte_name
				when group_by='D'  THEN CONCAT(pscd.service_charge_date,'-',lam.analyte_name) END
		ORDER BY	service_charge_date, module;
		
	WHEN 8 THEN	/*8-LAB*/
		SELECT
			'Lab' module, 
			pscd.service_charge_date, 
			lam.analyte_name service_item_name, 
			count(pscd.service_id)  as qty,
			/*
			SUM(pscd.applicable_rate) as gross,
			*/
			SUM(pscd.standard_rate) as gross,
			SUM(pscd.discount_amount) as disc, 
			psc.due_amount as due,
			SUM(pscd.cgst_amount + pscd.sgst_amount) as gst,
			SUM(pscd.net_amount) as net_total,
			is_tpa_charge,
			is_approved_by_tpa,
			SUM(tpa_rate) as final_tpa_charge, 
			psc.refund_amount -- CR ID-6-220131
		FROM
			lab_booking lb
			INNER JOIN	lab_booking_details lbd ON lb.id = lbd.lab_booking
			INNER JOIN	lab_analyte_master lam ON lam.id = lbd.lab_analyte_id
			INNER JOIN	patient_service_charges psc ON lb.id = psc.service_source_id AND psc.billing_module_id = 8 

			INNER JOIN	patient_service_charge_details pscd ON	pscd.patient_service_charge_id = psc.id AND pscd.service_id = lam.service_item_id and pscd.service_rate_id = lam.charge_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', 'R')
				when status_flag='B'  THEN pscd.status IN	('B', 'A')
				when status_flag='C'  THEN pscd.status	IN	('C', 'R') END
		where lb.op_ip_flag = 'N'
		AND	lb.id = ipd_id
		group by CASE 
				when group_by='M'  THEN module
				when group_by='S'  THEN lam.analyte_name
				when group_by='D'  THEN CONCAT(pscd.service_charge_date,'-',lam.analyte_name) END
		ORDER BY	service_charge_date, module;
END CASE;
END