Drop procedure sp_lab_booking_details;

CREATE Procedure sp_lab_booking_details(adt_fromdt date, adt_todt date)
BEGIN
	SELECT
		lb.id AS id,
		lb.cc_id AS cc_id,
		lb.status AS booking_status,
		/*	lb.is_master AS is_master,*/
		lb.bill_no AS bill_no,
		lb.case_id AS case_id,
		/*	lb.sub_case_id AS sub_case_id, */
		lb.patient_id AS patient_id,
		lb.const_doc AS const_doc,
		lb.booking_date AS booking_date,
		/*lb.collection_date AS collection_date,
		lb.collection_slot AS collection_slot,
		lb.collection_address AS collection_address,
		lb.patient_contact AS patient_contact,
		lb.patient_age AS patient_age,
		lb.patient_sex AS patient_sex,*/
		lb.total_charge AS total_charge,
		lb.payemnt_status AS payemnt_status,
		lb.case_status AS case_status,
		lb.home_collection_status AS home_collection_status,
		lb.doctor_verified_details AS doctor_verified_details,
		lb.created_at AS created_at,
	/*	lb.collected_by AS collected_by,
		lb.collected_datetime AS collected_datetime,*/
		lb.referral_type AS referral_type,
		lb.referral AS referral,
		lb.status AS status,
	/*	lb.is_radiology AS is_radiology,*/
		lb.remarks AS remarks,
		lb.business_type AS business_type,
		lb.total_paid AS total_paid,
		lb.due_charge AS due_charge,
		lb.disc_amount AS disc_amount,
		lb.discount AS discount,
		lb.discount_remarks AS discount_remarks,
		lb.discount_approval_by AS discount_approval_by,
		lb.refund_amt AS refund_amt,
		/*lb.pathology_bill AS pathology_bill,*/
		lb.cancell_amt AS cancell_amt,
		lb.outstand AS outstand,
		lb.print_count AS print_count,
		lb.is_cut_money_generated AS is_cut_money_generated,
		/*(
			SELECT
				count( 0 ) AS total_sample_received 
			FROM
				lab_booking_details 
			WHERE
				((
						CONVERT ( lab_booking_details.case_id USING utf8mb4 ) = lb.case_id 
						) 
				AND ( lab_booking_details.in_transit_status = '3' ))) AS total_sample_received,(
			SELECT
			IF
				(( intransit_record_master.is_from_master = 'Y' ), 'ML', 'CC' ) 
			FROM
				(
					intransit_record_master
					JOIN intransit_items ON ((
							intransit_record_master.id = intransit_items.intransit_id 
						))) 
			WHERE
				( CONVERT ( intransit_items.visit_id USING utf8mb4 ) = lb.case_id ) 
				LIMIT 1 
				) AS transfer_from,
		*/
		(
		SELECT
			group_concat(
				concat(
					'<a onclick="getReport(',
					lab_booking_details.id,
					',',
					CONVERT ( lab_analyte_master.is_radiology USING utf8mb4 ),
					',',
					lb.total_charge,
					',',
					lb.total_paid,
					',',
					lb.case_status,
					')">',
					CONVERT ( lab_analyte_master.analyte_name USING utf8mb4 ),
					'</a> - ₹ ',
					lab_booking_details.apply_charge,
				CONVERT (( CASE WHEN ( lab_analyte_master.is_out_source = 'Y' ) THEN concat( '<i onclick="getOutsourceAttachment(', lab_booking_details.id, ')" class="fa fa-paperclip" aria-hidden="true"></i>' ) ELSE '' END ) USING utf8mb4 )) SEPARATOR '<br/>' 
			) 
		FROM
			(
				lab_booking_details
				JOIN lab_analyte_master ON ((
				lab_analyte_master.id = lab_booking_details.lab_analyte_id 
			))) 
		WHERE
			((
					CONVERT ( lab_booking_details.case_id USING utf8mb4 ) = lb.case_id 
					) 
			)) AS test_names,
		pat.patient_type AS patient_type,
		pat.patient_name AS p_name,
		pat.age AS age,
		pat.gender AS gender,
		pat.patient_unique_id AS patient_unique_id,
		/*pat.mobileno AS mobileno,
		pat.email AS p_email, */
		pat.dob AS date_of_birth,
		(select contact_details from contacts where lower(data_source) = 'patient' and pat.id = contacts.data_source_id and contact_type = 'M') as mobileno,
		(select contact_details from contacts where lower(data_source) = 'patient' and pat.id = contacts.data_source_id and contact_type = 'E') as email
	FROM	lab_booking lb, patients pat
	WHERE	lb.patient_id = pat.id
	And 	lb.booking_date >= adt_fromdt OR adt_fromdt is null
	AND		lb.booking_date <= adt_todt OR adt_todt is null
	ORDER BY
		lb.status DESC,
		lb.created_at DESC;
END;