drop procedure if EXISTS sp_ipd_details;
Create procedure sp_ipd_details(a_from_date date, 
								a_to_date date)
BEGIN
	SELECT	pt.id as patient_id, 
			pt.patient_type as patient_type,
			pt.patient_unique_id as patient_unique_id,
			pt.admission_date as admission_date,
			pt.patient_name as patient_name,
			pt.dob as dob,
			pt.gender as gender,
			pt.marital_status as marital_status,
			pt.blood_group as blood_group,
			fn_patient_dtls('PADDR', pt.id) as present_addr,
			fn_patient_dtls('MOB', pt.id) as present_mobile,
			fn_patient_dtls('EMAIL', pt.id) as present_email,
			fn_patient_dtls('KIN', pt.id) as present_kin,
			ia.admission_date as admission_date,
			ia.admission_no as admission_no,
			doc.doctor_name as ipd_doctor,
			CASE 
				WHEN is_deceased='Y' THEN 'Deceased'
				WHEN is_discharged='Y' THEN 'Discharge'
				ELSE 'Not Discharge'
			END as present_status,
			ia.discharged_date as discharged_deceased_date,
			ia.policy_id as policy_id,
			ia.beneficiary_id as beneficiary_id,
			ia.card_no as policy_card_no,
			fn_ipd_details('BED', ia.id) as bed_details, 
			fn_ipd_details('CONSULTANT', ia.id) as other_consultant, 
			cd.case_no as case_no,
			cd.case_register_date as case_register_date,
			cd.clearence_status as clearence_status,
			cd.case_status as case_status,
			cd.is_settled as is_case_settled,
			vd.visit_no as visit_no,
			vd.visit_open_dt as visit_open_dt,
			vd.visit_close_dt as visit_close_dt,
			vd.visit_status as visit_status,
			vd.is_cancelled as is_visit_cancelled,
			fn_symptom_vital_hist('S', 'I', ia.id) as symptom_dtls,
			fn_symptom_vital_hist('V', 'I', ia.id) as vital_dtls,
			fn_prescription_dtls(vd.id) as prescription_dtls,
			vd.visit_no as visit_no,
			vd.visit_open_dt as visit_open_dt,
			vd.visit_close_dt as visit_close_dt,
			fr.room_name as room_name,
			b.bed_name as bed_name,
			doc.doctor_name as doctor_name,
			fn_service_charge_dtls(psc.id) as service_charges,
			fn_prescription_dtls(vd.id) as prescription_dtls
	FROM 	patients pt
			INNER JOIN	ipd_admission ia ON pt.id = ia.patient_id
			INNER JOIN doctors doc ON doc.id = ia.doctor_id 
			INNER JOIN case_details cd ON cd.opd_ipd_ot_id = ia.id AND cd.opd_ipd_ot_flag = 'I'
			INNER JOIN	visit_details vd ON cd.id = vd.case_id 
			INNER JOIN	bed b ON b.id = vd.bed_id
			INNER JOIN	floorwise_room fr ON fr.id = b.room_id
			LEFT JOIN	patient_service_charges psc ON psc.service_source_id = ia.id AND psc.billing_module_id = 6
	WHERE	(ia.admission_date >= a_from_date OR a_from_date is null)
	And		(ia.admission_date	<=	a_to_date OR a_to_date is null)

	ORDER BY ia.admission_date, patient_unique_id ;
END;
