DROP PROCEDURE IF EXISTS `sp_get_billing_details_by_patient`;

CREATE PROCEDURE `sp_get_billing_details_by_patient`(patientid INT) BEGIN
SELECT
	COALESCE(SUM( net_amount ),0) AS total_billing_amount,
	COALESCE(SUM( paid_amount ),0) AS total_paid_amount,
	COALESCE(SUM( due_amount ),0) AS total_due_amount,
	permissions.perm_name AS module_name, 
	permissions.card_color AS card_color, 
	permissions.perm_icon AS icon
FROM
	patient_service_charges
	LEFT JOIN patients ON patient_service_charges.patient_id = patients.id
	LEFT JOIN permissions ON permissions.perm_id = patient_service_charges.billing_module_id 
WHERE
	patient_id = patientid 
GROUP BY
	patient_service_charges.billing_module_id

UNION

SELECT
	COALESCE(SUM( total_net_amount ),0) total_billing_amount,
	COALESCE(SUM( paid_amount ),0) total_paid_amount,
	COALESCE(SUM( due_amount ),0) total_due_amount,
	'PHARMACY',
	(SELECT permissions.card_color FROM permissions WHERE permissions.perm_id = '11') as card_color,
	(SELECT permissions.perm_icon FROM permissions WHERE permissions.perm_id = '11') as icon
FROM
	pharmacy_billing_header
	JOIN patients ON pharmacy_billing_header.patient_id = patients.id 
WHERE
	patient_id = patientid;
	
END;

CALL sp_get_billing_details_by_patient('54');