DROP  PROCEDURE if EXISTS sp_module_wise_bill_data;
CREATE PROCEDURE `sp_module_wise_bill_data`( IN bill_fromdt DATE, IN bill_todt DATE )
BEGIN
	
	SELECT
		SUM(balance_amount) as bam  INTO @adv_balance 
	FROM
		patient_advance_balance pab1
		INNER JOIN ipd_admission AS ia ON ia.id = pab1.admission_id 
		AND pab1.id = (
		SELECT
			Max( id ) 
		FROM
			patient_advance_balance pab2 
		WHERE
			pab2.admission_id = ia.id 
			AND pab2.patient_id=	ia.patient_id
			AND pab2.advance_for ='I'
			AND DATE(pab2.balance_date)	BETWEEN bill_fromdt 
			AND bill_todt
		) 
		GROUP BY pab1.advance_for;
		
	SELECT
		SUM(balance_amount) as bam  INTO @adv_balance_ph 
	FROM
		patient_advance_balance pab1
		INNER JOIN ipd_admission AS ia ON ia.id = pab1.admission_id 
		AND pab1.id = (
		SELECT
			Max( id ) 
		FROM
			patient_advance_balance pab2 
		WHERE
			pab2.admission_id = ia.id 
			AND pab2.patient_id=	ia.patient_id
			AND pab2.advance_for ='P'
			AND DATE(pab2.balance_date)	BETWEEN bill_fromdt 
			AND bill_todt
		) GROUP BY pab1.advance_for;
		
	SELECT COALESCE
		( SUM( patient_service_charge_details.applicable_rate ), 0 ) +
		case when billing_module_id = 6 then IFNULL(@adv_balance,0) else 0 End AS total_billed,
		UCASE( permissions.perm_name ) AS perm_name,
		permissions.perm_icon AS icon,
		permissions.card_color,
		permissions.perm_id	
	FROM
		permissions
		LEFT JOIN patient_service_charges ON permissions.perm_id = patient_service_charges.billing_module_id 
		LEFT JOIN patient_service_charge_details ON patient_service_charges.id = patient_service_charge_details.patient_service_charge_id AND patient_service_charge_details.status IN ( 'A', 'B' ) AND patient_service_charges.bill_date BETWEEN bill_fromdt 
		AND bill_todt 
	WHERE
		permissions.is_billable = '1' 
		AND permissions.perm_id != '11' 
	GROUP BY
		patient_service_charges.billing_module_id UNION
	SELECT COALESCE
		( SUM( pharmacy_billing_item.net_sale_price ), 0 ) + IFNULL(@adv_balance_ph,0) AS total_billed,
		'PHARMACY' AS perm_name,
		( SELECT permissions.perm_icon AS icon FROM permissions WHERE permissions.perm_id = '11' ),
		( SELECT permissions.card_color FROM permissions WHERE permissions.perm_id = '11' ),
		'11' AS perm_id 
	FROM
		pharmacy_billing_item
		JOIN pharmacy_billing_header ON pharmacy_billing_header.id = pharmacy_billing_item.pharmacy_billing_header_id 
	WHERE
		pharmacy_billing_header.bill_date BETWEEN bill_fromdt 
		AND bill_todt;

END