DROP PROCEDURE sp_lab_booking_details;

CREATE PROCEDURE sp_lab_booking_details(IN adt_fromdt DATE, IN adt_todt DATE, IN adt_ctype VARCHAR(10), IN a_booking_status VARCHAR(100), IN a_home_collection_status VARCHAR(100), IN a_test_collection_status VARCHAR(100), IN aray_cType VARCHAR(100))
BEGIN
	
	SET SESSION sql_mode='';
	
	SELECT
		lb.id AS id,
		lb.cc_id AS cc_id,
		lb.case_status AS booking_status,
		(SELECT COUNT(lab_booking_details.id) FROM lab_booking_details WHERE lab_booking_details.status = '0' AND lab_booking_details.lab_booking = lb.id) AS item_bc_not_generated,
		(SELECT COUNT(lab_booking_details.id) FROM lab_booking_details WHERE lab_booking_details.status = '0.2' AND lab_booking_details.lab_booking = lb.id) AS item_not_collected,
		lb.bill_no AS bill_no,
		lb.case_id AS case_id,
		lb.patient_id AS patient_id,
		lb.referred_doc_id AS referred_doc_id,
		lb.booking_date AS booking_date,
		chg.gross_amount AS gross_charge,
		chg.net_amount AS net_charge,
		lb.payemnt_status AS payemnt_status,
		lb.home_collection_status AS home_collection_status,
		lb.home_collection_date,
		lb.home_collection_time,
		lb.location,
		lb.home_collection_phlebotomist,
		CONCAT_WS(' ',stfs.first_name,stfs.last_name) as phlebotomist_name,
		lb.created_at AS created_at,
		lb.client_referral AS client_referral,
		lb.discount_referred_by AS discount_referred_by,
		lb.is_stats AS is_stats,
		lb.remarks AS remarks,
		chg.paid_amount AS total_paid,
		chg.due_amount AS due_charge,
		chg.discount_amount AS disc_rate,
		lb.discount_type AS discount_type,
		lb.discount_remarks AS discount_remarks,
		lb.discount_approval_by AS discount_approval_by,
		chg.refund_amount AS refund_amt,
		lb.cancel_charge AS cancel_charge,
		lb.print_count AS print_count,
		lb.is_ip_generated AS is_ip_generated,
		lb.collection_type,
		lb.pre_booking_is_converted,
		(
		SELECT
			group_concat(
				concat(
					'<a style="cursor: pointer;" data-toggle="tooltip" data-bs-original-title="Click Here For Details" aria-label="Click Here For Detail" onclick="getReport(',
					lab_booking_details.id,
					',',
					CONCAT ("'", lab_analyte_master.is_radiology ,"'"),
					',',
					CONCAT("'",lb.case_id,"',"),
					CONCAT("'",lb.case_status,"',"),
					CONCAT("'",lb.id,"'"),
					')">',
					lab_analyte_master.analyte_name,
					'</a> - ₹ ',
					lab_booking_details.apply_charge,
								( 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 )
								) SEPARATOR '<br/>' 
								
			) 
		FROM
			(
				lab_booking_details
				JOIN lab_analyte_master ON ((
				lab_analyte_master.id = lab_booking_details.lab_analyte_id 
			))) 
		WHERE
			((
					lab_booking_details.case_id = lb.case_id
					) 
			)) AS test_names,
		pat.patient_type AS patient_type,
		CONCAT(pat.patient_name) AS p_name,
		pat.age AS age,
		pat.gender AS gender,
		pat.patient_unique_id AS patient_unique_id,
		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' LIMIT 1) as mobileno,
		(select contact_details from contacts where lower(data_source) = 'patient' and pat.id = contacts.data_source_id and contact_type = 'E' LIMIT 1) as email
	FROM	lab_booking lb
    JOIN patients pat ON lb.patient_id = pat.id
		JOIN patient_service_charges chg ON lb.id = chg.service_source_id AND chg.billing_module_id = '8'
		LEFT JOIN users stfs ON lb.home_collection_phlebotomist = stfs.id
		LEFT JOIN lab_booking_details lbd ON lb.id = lbd.lab_booking
		JOIN (SELECT (CASE WHEN adt_ctype = short_code THEN short_code WHEN adt_ctype = "all" THEN short_code ELSE "" END) sCode FROM lookup_values WHERE lookup_id = '44') cTyps ON lb.collection_type = cTyps.sCode
	WHERE	( (CASE WHEN adt_ctype='HMC' THEN lb.home_collection_date ELSE lb.booking_date END) >= adt_fromdt OR adt_fromdt is null)
	AND		( (CASE WHEN adt_ctype='HMC' THEN lb.home_collection_date ELSE lb.booking_date END) <= adt_todt OR adt_todt is null)
	
	AND (lbd.status IN ( SELECT *
		 FROM
		   JSON_TABLE(
				 a_test_collection_status,
			 "$[*]"
			 COLUMNS(tab_val VARCHAR(10) PATH "$")
		   ) data) OR a_test_collection_status = '')
			 
	AND (lb.case_status IN ( SELECT *
		 FROM
		   JSON_TABLE(
				 a_booking_status,
			 "$[*]"
			 COLUMNS(tab_val VARCHAR(10) PATH "$")
		   ) data) OR a_booking_status = '')
			 
			 
			 
	AND	(lb.home_collection_status IN ( SELECT *
		 FROM
		   JSON_TABLE(
				 a_home_collection_status,
			 "$[*]"
			 COLUMNS(tab_val VARCHAR(10) PATH "$")
		   ) data) OR a_home_collection_status  = '')
	
	AND	(lb.collection_type IN ( SELECT *
		 FROM
		   JSON_TABLE(
				 aray_cType,
			 "$[*]"
			 COLUMNS(tab_val VARCHAR(10) PATH "$")
		   ) data) OR aray_cType  = '')
	GROUP BY lb.case_id
	ORDER BY
		lb.is_stats DESC,
		lb.created_at DESC;

END
