DROP  PROCEDURE if EXISTS shims_payment;
CREATE PROCEDURE `shims_payment`( pat_id int (11), adm_id INT (11),  mod_id INT (11), module_type VARCHAR(1), with_pharmacy INT(11))
BEGIN

SELECT 
	id,
	source, 
	payment_mode, 
	date, 
	payment_type, 
	paid_amount, 
	refund_amount, 
	patient_service_charge_id,
	note
FROM

(SELECT 
	pp.id,
	pp.charge_source as source,
	pp.payment_mode_id as payment_mode, 
	pp.created_at as date, 
	pp.payment_type, 
	IF(pp.payment_type!='R', pp.paid_amount, 0) as paid_amount,
	IF(pp.payment_type='R', pp.paid_amount, 0) as refund_amount, 
	psch.patient_service_charge_id,
	psch.patient_id,
	IF(module_type = 'M', psch.parent_id, psc.service_source_id ) AS admission_id,
	IF(module_type = 'M', psch.parent_module_id, psch.billing_module_id ) AS module_id,
	pp.note

FROM patient_service_charge_header as psch
INNER JOIN patient_service_charges as psc ON psc.id=psch.patient_service_charge_id
INNER JOIN patient_payments as pp ON psc.id=pp.patient_service_charge_id AND pp.charge_source='service'
WHERE psch.billing_module_id != 11 

UNION

SELECT 
		pp.id, 
		pp.charge_source as source,
		pp.payment_mode_id as payment_mode, 
		pp.created_at as date, 
		pp.payment_type, 
		IF(pp.payment_type!='R', pp.paid_amount, 0) as paid_amount,
		IF(pp.payment_type='R', pp.paid_amount, 0) as refund_amount, 
		psch.patient_service_charge_id,
		psch.patient_id,
		IF(module_type = 'M', psch.parent_id, psch.patient_service_charge_id ) AS admission_id,
		IF(module_type = 'M', psch.parent_module_id, psch.billing_module_id ) AS module_id,
		pp.note
FROM patient_service_charge_header as psch
INNER JOIN patient_service_charges as psc ON psc.id=psch.patient_service_charge_id
INNER JOIN patient_payments as pp ON psc.id=pp.patient_service_charge_id AND pp.charge_source='pharmacy'
WHERE psch.billing_module_id = 11
) as payment
WHERE ( CASE WHEN pat_id != 0 THEN pat_id = patient_id ELSE 1 = 1 END ) 
		AND ( CASE WHEN adm_id != 0 THEN adm_id= admission_id ELSE 1 = 1 END ) 
		AND ( CASE WHEN mod_id != 0 THEN mod_id = module_id ELSE 1 = 1 END ) 
		AND ( CASE WHEN with_pharmacy = 0 THEN source!= 'Pharmacy' ELSE 1 = 1 END ) 
		AND ( CASE WHEN with_pharmacy = 2 THEN source= 'Pharmacy' ELSE 1 = 1 END ) 
		AND payment_type NOT IN ('T', 'A')
GROUP BY id;

END