DROP PROCEDURE IF EXISTS sp_display_analytes;

CREATE  PROCEDURE sp_display_analytes(IN a_analyte_id INT(11))
BEGIN

	DECLARE v_analyte_category VARCHAR(1);

	DROP TEMPORARY TABLE IF EXISTS tmp_lab_analyte_master;

	

	CREATE TEMPORARY TABLE tmp_lab_analyte_master  (
		parent_analyte_id	int DEFAULT 0,
		id int NOT NULL ,
		sequence_no int DEFAULT 0,
		analyteid varchar(50) NOT NULL,
		analytecode varchar(50) NOT NULL,
		parent_analyte_name varchar(255) NOT NULL,
		analyte_name varchar(255) NOT NULL,
		analyte_display_text varchar(255) NULL DEFAULT '',
		department_id int NOT NULL,
		sub_department_id int NOT NULL,
		department_name varchar(100) NULL, 
		sub_department_name varchar(100) NULL, 
		analyte_category varchar(25) NOT NULL,
		method_id int NOT NULL,
		sample_id int NOT NULL,
		device	varchar(200) NULL DEFAULT '',
		analyte_uom varchar(50) NOT NULL,
		result_type varchar(50) NOT NULL,
		container_id int NOT NULL,
		analyte_sequence_no decimal(10, 2) NULL DEFAULT 0,
		standard_charge decimal(10, 2) NULL DEFAULT 0,
		is_active enum('Y','N')  NOT NULL DEFAULT 'Y',
		is_printable enum('Y','N')  NOT NULL DEFAULT 'Y',
		is_reportable enum('Y','N')  NOT NULL DEFAULT 'Y',
		is_orderable enum('Y','N')  NOT NULL DEFAULT 'Y',
		is_radiology enum('Y','N')  NOT NULL DEFAULT 'N',
		is_out_source enum('Y','N')  NOT NULL DEFAULT 'N',
		is_auto_authorize enum('Y','N')  NOT NULL DEFAULT 'Y',
		is_qty_chargable enum('Y','N')  NOT NULL DEFAULT 'Y',
		is_amount_editable enum('Y','N')  NOT NULL DEFAULT 'Y',
		is_print_seperate_page enum('Y','N')  NOT NULL DEFAULT 'N',
		is_discount enum('Y','N')  NOT NULL DEFAULT 'Y',
		is_history_required enum('Y','N')  NOT NULL DEFAULT 'N',
		is_mandetory enum('Y','N')  NOT NULL DEFAULT 'N',
		is_interface enum('Y','N')  NOT NULL DEFAULT 'Y',
		is_consent enum('Y','N')  NOT NULL DEFAULT 'Y',
		is_range_reference_required enum('Y','N')  NOT NULL DEFAULT 'Y',
		is_flag_required enum('Y','N')  NOT NULL DEFAULT 'Y',
		is_unit_required enum('Y','N')  NOT NULL DEFAULT 'Y',
		ip_type enum('A','P') NOT NULL DEFAULT 'P',
		standard_ip_rate decimal(10, 2) NULL DEFAULT 0.00,
		inner_notes varchar(1000) NULL,
		consent_form varchar(1000) NULL,
		remarks varchar(1000) NULL,
		service_item_id INT DEFAULT 0,
		charge_id INT DEFAULT 0,
		`comment` VARCHAR(1000) NULL
	) ;

	SELECT	analyte_category
	INTO		v_analyte_category
	FROM		lab_analyte_master
	WHERE		id = a_analyte_id;

	Truncate Table tmp_lab_grp_analytes;

	IF v_analyte_category IN ('P', 'G') THEN
		DROP TEMPORARY TABLE IF EXISTS tmp_lab_grp_analytes;

		Truncate Table tmp_lab_grp_analytes;

		CREATE TEMPORARY TABLE  IF NOT EXISTS tmp_lab_grp_analytes  (
			parent_analyte_id	int,
			child_analyte_id	int);

		Insert INTO	tmp_lab_grp_analytes
		(parent_analyte_id, child_analyte_id)
		Select 	b.parent_lab_analyte_id, b.child_lab_analyte_id 
		From 		lab_analyte_master a JOIN lab_analyte_category_map b ON	a.id = b.child_lab_analyte_id 
		WHERE		b.parent_lab_analyte_id = a_analyte_id
		AND			a.is_active = 'Y'
		AND  		b.is_active = 'Y'
		AND			a.analyte_category = 'G'
		UNION ALL
		Select 0, a_analyte_id;
	END IF;

	INSERT INTO tmp_lab_analyte_master
			(parent_analyte_id, sequence_no, id, analyteid, analytecode, parent_analyte_name, analyte_name, analyte_display_text,
			department_id, sub_department_id, department_name, sub_department_name, analyte_category, 
			method_id, sample_id, analyte_uom, result_type, device,
			container_id, analyte_sequence_no, standard_charge,
			is_active, is_printable, is_reportable, is_orderable, is_radiology, is_out_source,
			is_auto_authorize, is_qty_chargable, is_amount_editable, is_print_seperate_page, is_discount,
			is_history_required, is_mandetory, is_consent, is_range_reference_required, is_flag_required,
			is_unit_required, ip_type, standard_ip_rate, inner_notes, consent_form, remarks, service_item_id, charge_id)
	Select	0, 0, lab.id, analyteid, analytecode, analyte_name, analyte_name, analyte_display_text,
			department_id, sub_department_id, department_name, sub_department_name,analyte_category,
			method_id, sample_id, analyte_uom, result_type, device,
			container_id, analyte_sequence_no, standard_charge,
			lab.is_active, is_printable, is_reportable, is_orderable, is_radiology, is_out_source,
			is_auto_authorize, is_qty_chargable, is_amount_editable, is_print_seperate_page, is_discount,
			is_history_required, is_mandetory, is_consent, is_range_reference_required, is_flag_required,
			is_unit_required, ip_type, standard_ip_rate, inner_notes, consent_form, remarks, service_item_id, charge_id

	FROM	lab_analyte_master lab 

	JOIN  lab_department ld ON ld.id = lab.department_id
	LEFT JOIN lab_sub_department lsd ON lsd.id = lab.sub_department_id
	WHERE		lab.id = a_analyte_id

	UNION 

	Select	dtls.parent_lab_analyte_id, dtls.sequence_no, hdr.id, analyteid, analytecode, (SELECT analyte_name FROM lab_analyte_master WHERE id = dtls.parent_lab_analyte_id) as parent_analyte_name, analyte_name, analyte_display_text,
			department_id, sub_department_id, department_name, sub_department_name, analyte_category,
			method_id, sample_id, analyte_uom, result_type, device,
			container_id, analyte_sequence_no, standard_charge,
			hdr.is_active, is_printable, is_reportable, is_orderable, is_radiology, is_out_source,
			is_auto_authorize, is_qty_chargable, is_amount_editable, is_print_seperate_page, is_discount,
			is_history_required, is_mandetory, is_consent, is_range_reference_required, is_flag_required,
			is_unit_required, ip_type, standard_ip_rate, inner_notes, consent_form, remarks, service_item_id, charge_id

	FROM	lab_analyte_master hdr 

	JOIN  lab_department ld1 ON ld1.id = hdr.department_id
	JOIN lab_analyte_category_map dtls ON	hdr.id = dtls.child_lab_analyte_id 
	LEFT JOIN lab_sub_department lsd1 ON lsd1.id = hdr.sub_department_id
	WHERE	dtls.parent_lab_analyte_id IN (SELECT child_analyte_id	FROM tmp_lab_grp_analytes);

	Select lab.*, method_name, samplename
	from tmp_lab_analyte_master lab 
	LEFT JOIN lab_method_master lmm ON lmm.id = lab.method_id
	LEFT JOIN lab_sample_master lsm ON lsm.id = lab.sample_id
	WHERE lab.is_reportable = 'Y'
	ORDER BY lab.is_print_seperate_page ASC, lab.analyte_sequence_no ASC;

END