SET @tpa_id = 2;  /* tpa  id */
SET @dt_fil_typ = 'E'; /* E = Entry, I = initial approve dt, C = claim sent date, A = approval date, P = pay date */
SET @d1 = '2022-02-07 00:00:00'; /* date range 1 */
SET @d2 = '2022-02-17 00:00:00'; /* date range 2 */


SELECT
	CONCAT( p.patient_name, ' (', IFNULL( p_cons.contact_details, '--' ), ')' ) as P_name,
	admt.admission_no as admit_no,
	IFNULL(CONCAT(tpa_org.organisation_name,' (',tpa_pol.policy_name,')'),'--') as tpa_dtls,
	IFNULL(tpa_stat.status,'N/A') as tpa_stat,
	IFNULL(tpa_stat.tpa_entry_date,'--') as entry_dt,
	IFNULL(tpa_stat.tpa_ini_app_approved_at,'--') as init_apprv_dt,
	IFNULL(tpa_stat.tpa_initial_approve_amount,'--') as init_apprv_amt,
	IFNULL(tpa_stat.tpa_claim_amount,'--') as claim_amt,
	IFNULL(tpa_stat.tpa_claim_approved_at,'--') as claim_dt,
	IFNULL(tpa_stat.tpa_sanction_approved_at,'--') as finl_apprv_dt,
	IFNULL(tpa_stat.tpa_sanction_amount,'--') as finl_apprv_amt,
	IFNULL(tpa_stat.tpa_pay_approved_at,'--') as tpa_pay_dt,
	IFNULL(tpa_stat.paid_amt,'--') as tpa_paid, 
	IFNULL((tpa_stat.tpa_sanction_amount - tpa_stat.paid_amt),'--') as loss
FROM
	tpa_patient_claim_workflow as tpa_stat
	INNER JOIN ipd_admission admt ON tpa_stat.ipd_admission_id = admt.id
	INNER JOIN patients p ON admt.patient_id = p.id
	LEFT JOIN contacts p_cons ON p_cons.id = (
	SELECT
		p_cons1.id 
	FROM
		contacts p_cons1 
	WHERE
		p_cons1.data_source_id = p.id 
		AND p_cons1.data_source = 'Patient' 
		AND p_cons1.contact_type = 'M' 
		AND p_cons1.is_active = 'Y' 
	ORDER BY
		p_cons1.id DESC 
	LIMIT 1 
	)
	INNER JOIN tpa_policies tpa_pol ON admt.policy_id = tpa_pol.id
	INNER JOIN tpa_organisations tpa_org ON tpa_pol.tpa_id = tpa_org.id
	WHERE (
		CASE
			WHEN @tpa_id != '' THEN (tpa_org.id = @tpa_id)
		END)
		AND (
			CASE
				WHEN (@dt_fil_typ = 'E' AND (@d1 != '' AND @d2 != '')) THEN (tpa_stat.tpa_entry_date BETWEEN @d1 AND @d2)
				WHEN (@dt_fil_typ = 'I' AND (@d1 != '' AND @d2 != '')) THEN (tpa_stat.tpa_ini_app_approved_at BETWEEN @d1 AND @d2)
				WHEN (@dt_fil_typ = 'C' AND (@d1 != '' AND @d2 != '')) THEN (tpa_stat.tpa_claim_approved_at BETWEEN @d1 AND @d2)
				WHEN (@dt_fil_typ = 'A' AND (@d1 != '' AND @d2 != '')) THEN (tpa_stat.tpa_sanction_approved_at BETWEEN @d1 AND @d2)
				WHEN (@dt_fil_typ = 'P' AND (@d1 != '' AND @d2 != '')) THEN (tpa_stat.tpa_pay_approved_at BETWEEN @d1 AND @d2)
			END)