DROP PROCEDURE IF EXISTS sp_get_billing_details_by_patient;

CREATE  PROCEDURE sp_get_billing_details_by_patient(patientid INT)
BEGIN
SET SESSION sql_mode = "";

select balance_amount
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.advance_for IN ('I')
				and pab2.patient_id = patientid);
				
select balance_amount
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.advance_for IN ('P')
				and pab2.patient_id = patientid);
				
SELECT COALESCE
	( SUM( patient_service_charge_details.net_amount ), 0 )- 
	
	case when billing_module_id IN (3,6) then 	COALESCE ( patient_service_charges.discount_amount, 0 ) 
	else 0 End AS total_billing_amount,
	
	COALESCE (  SUM(patient_service_charges.paid_amount), 0 ) + 
	case when billing_module_id = 6 then IFNULL(@adv_balance,0) else 0 End AS total_paid_amount,
	COALESCE ( patient_service_charges.refund_amount, 0 ) AS total_refunded_amount,
	COALESCE ( SUM( patient_service_charge_details.net_amount ), 0 )- COALESCE ( patient_service_charges.paid_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 patient_service_charge_details ON patient_service_charge_details.patient_service_charge_id = patient_service_charges.id
	LEFT JOIN permissions ON permissions.perm_id = patient_service_charges.billing_module_id 
WHERE
	patients.id = patientid 
	AND patient_service_charge_details.STATUS IN ( 'B', 'A' ) 
GROUP BY
	patient_service_charges.billing_module_id 
HAVING
	COALESCE ( SUM( patient_service_charge_details.net_amount ), 0 )>0
UNION

SELECT
	COALESCE(SUM( total_net_amount ),0) - COALESCE(SUM( return_amount ),0) total_pharma_billing_amount,
	COALESCE(SUM( paid_amount ),0) +  IFNULL(@adv_balance_ph,0) AS total_paid_amount,

	COALESCE(refund_amount,0) AS total_refunded_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
HAVING
	COALESCE(SUM( total_net_amount ),0)>0;
	
END

