DROP PROCEDURE IF EXISTS sp_payments_details;
CREATE PROCEDURE `sp_payments_details`( module_id int (11), source_id INT (11) )
BEGIN


set @hims_module = module_id;
-- Pharmacy OPD Payments --
SELECT
	pp.patient_service_charge_id, pp.created_at as created_at, payment_date, charge_source, payment_mode_id, payment_type, pp.note, pp.id as pp_id,
	case when payment_type in ('P', 'A') then pp.paid_amount else 0 end as amount_debit,
	case when payment_type in ('J', 'R', 'T') then pp.paid_amount else 0 end  as amount_credit
FROM
	patient_payments pp
	INNER JOIN pharmacy_billing_header pbh ON pp.patient_service_charge_id = pbh.id 
	AND pp.charge_source = 'Pharmacy' AND pbh.opd_ipd_no = source_id AND pbh.opd_ipd_ot_flag = 'O' AND @hims_module = 3
	and payment_mode_id <> 'F'  and payment_type <> 'T'
	AND @hims_module = 3 

UNION 

-- Pharmacy IPD Payments --
SELECT
	pp.patient_service_charge_id, pp.created_at as created_at, payment_date, charge_source, payment_mode_id, payment_type, pp.note, pp.id as pp_id,
	case when payment_type in ('P', 'A') then pp.paid_amount else 0 end as amount_debit,
	case when payment_type in ('J', 'R', 'T') then pp.paid_amount else 0 end  as amount_credit
FROM
	patient_payments pp
	INNER JOIN pharmacy_billing_header pbh ON pp.patient_service_charge_id = pbh.id 
	AND pp.charge_source = 'Pharmacy' AND pbh.opd_ipd_no = source_id AND pbh.opd_ipd_ot_flag = 'I' AND @hims_module = 6
	and payment_mode_id <> 'F' and payment_type <> 'T'
	AND @hims_module = 6

	
UNION
	
-- service and Pharmacy IPD Payments --
SELECT
	pp.patient_service_charge_id, pp.created_at as created_at, payment_date, charge_source, payment_mode_id, payment_type, pp.note,pp.id as pp_id,
	case when payment_type in ('P', 'A') then pp.paid_amount else 0 end as amount_debit,
	case when payment_type in ('J', 'R', 'T') then pp.paid_amount else 0 end  as amount_credit
FROM
	patient_payments pp
	WHERE pp.charge_source In ('Service', 'Pharmacy')  
	AND pp.admission_id = ( case when @hims_module =6 then  source_id else -1 end)
	AND pp.patient_service_charge_id = 0
	and payment_mode_id <> 'F' 

UNION


-- service Payments  for TPA adjustment--
SELECT
	pp.patient_service_charge_id, pp.created_at as created_at, payment_date, charge_source, payment_mode_id, payment_type, pp.note,pp.id as pp_id,
	case when payment_type = 'P' then pp.paid_amount else 0 end as amount_debit,
	0.00  as amount_credit
FROM
	patient_payments pp
	WHERE pp.charge_source = 'Service'
	AND pp.admission_id = source_id
	AND pp.patient_service_charge_id <> 0
	and payment_mode_id = 'F' 
	AND	note like '%TPA%'
UNION



-- Service Payments --
SELECT
	pp.patient_service_charge_id, pp.created_at as created_at, payment_date, charge_source, payment_mode_id, payment_type, pp.note, pp.id as pp_id,
	case when payment_type in ('P', 'A') then pp.paid_amount else 0 end as amount_debit,
	case when payment_type in ('J', 'R', 'T') then pp.paid_amount else 0 end  as amount_credit
FROM
	patient_payments pp
	INNER JOIN patient_service_charges psc ON pp.patient_service_charge_id = psc.id 
	AND pp.charge_source = 'Service' AND psc.service_source_id = source_id
	AND psc.billing_module_id = module_id
	and payment_mode_id <> 'F'

UNION

-- Service OPD Lab Payments --
SELECT
	pp.patient_service_charge_id, pp.created_at as created_at, payment_date, 'lab' charge_source, payment_mode_id, payment_type, pp.note, pp.id as pp_id,
	case when payment_type in ('P', 'A') then pp.paid_amount else 0 end as amount_debit,
	case when payment_type in ('J', 'R', 'T') then pp.paid_amount else 0 end  as amount_credit
FROM
	patient_payments pp
	INNER JOIN patient_service_charges psc ON psc.id = pp.patient_service_charge_id AND psc.billing_module_id = 8
	INNER JOIN lab_booking lbk ON psc.service_source_id = lbk.id AND lbk.op_ip_flag = 'O' AND lbk.op_ip_id = source_id
	and payment_mode_id <> 'F'
	AND @hims_module = 3
WHERE pp.charge_source = 'Service'

UNION

-- Service IPD Lab Payments --
SELECT
	pp.patient_service_charge_id, pp.created_at as created_at, payment_date, 'lab' charge_source, payment_mode_id, payment_type, pp.note, pp.id as pp_id,
	case when payment_type in ('P', 'A') then pp.paid_amount else 0 end as amount_debit,
	case when payment_type in ('J', 'R', 'T') then pp.paid_amount else 0 end  as amount_credit
FROM
	patient_payments pp
	INNER JOIN patient_service_charges psc ON psc.id = pp.patient_service_charge_id AND psc.billing_module_id = 8
	INNER JOIN lab_booking lbk ON psc.service_source_id = lbk.id AND lbk.op_ip_flag = 'I' AND lbk.op_ip_id = source_id
	and payment_mode_id <> 'F' 
	AND @hims_module = 6
WHERE pp.charge_source = 'Service'

UNION

-- Advance Payments --
SELECT
	pp.patient_service_charge_id, pp.created_at as created_at, payment_date, charge_source, payment_mode_id, payment_type, pp.note, pp.id as pp_id,
	pp.paid_amount as amount_debit,
	0 as amount_credit
FROM
	patient_payments pp
	INNER JOIN ipd_admission ia ON pp.admission_id = ia.id 
	AND ((payment_type = 'A') or (payment_type = 'J' and payment_mode_id ='F'))
	where ia.id = ( case when @hims_module =6 then  source_id else -1 end)
	

UNION

-- Service IPD OT Payments --
SELECT
	pp.patient_service_charge_id, pp.created_at as created_at, payment_date, 'ot service' charge_source, payment_mode_id, payment_type, pp.note, pp.id as pp_id,
	case when payment_type in ('P', 'A') then pp.paid_amount else 0 end as amount_debit,
	case when payment_type in ('J', 'R', 'T') then pp.paid_amount else 0 end  as amount_credit
FROM
	patient_payments pp
	INNER JOIN patient_service_charges psc ON psc.id = pp.patient_service_charge_id AND psc.billing_module_id = 12
	INNER JOIN ot_bookings ob ON psc.service_source_id = ob.id AND ob.ref_opd_ipd_flag = 'I' AND ob.ref_opd_ipd_id = source_id

	and payment_mode_id <> 'F' 
	AND @hims_module = 6
WHERE pp.charge_source = 'Service'

UNION

-- Service OT Pharmacy Payments --
SELECT
	pp.patient_service_charge_id, pp.created_at as created_at, payment_date, charge_source, payment_mode_id, payment_type, pp.note, pp.id as pp_id,
	case when payment_type in ('P', 'A') then pp.paid_amount else 0 end as amount_debit,
	case when payment_type in ('J', 'R', 'T') then pp.paid_amount else 0 end  as amount_credit
FROM
	patient_payments pp
	INNER JOIN pharmacy_billing_header pbh ON pp.patient_service_charge_id = pbh.id AND pp.charge_source = 'Pharmacy' AND pbh.opd_ipd_ot_flag = 'T'
	INNER JOIN ot_bookings ob ON pbh.opd_ipd_no = ob.id AND ob.ref_opd_ipd_flag = 'I' AND ob.ref_opd_ipd_id = source_id

WHERE payment_mode_id <> 'F' and payment_type <> 'T'
	AND @hims_module = 6

ORDER BY payment_date, charge_source, amount_debit desc;

END