drop procedure sp_analyte_grp_migration_indv;

CREATE PROCEDURE sp_analyte_grp_migration_indv (a_testid INT, a_analyteid VARCHAR(50))
BEGIN
	DECLARE done INT DEFAULT FALSE;
	DECLARE v_testid INT;
	DECLARE v_child_testid INT;
	DECLARE v_grpchild_testid	INT;
	DECLARE v_analyteid VARCHAR(50);
	DECLARE v_analytecode VARCHAR(50);
	DECLARE v_child_analyteid VARCHAR(50);
	DECLARE v_child_grp_name VARCHAR(500);
	DECLARE a INT Default 0 ;
	DECLARE sub_str VARCHAR(1000);

	DECLARE cur_lab_child_analyte CURSOR FOR 
		SELECT	analyteid, analytecode, GROUP_CONCAT(distinct trim(sub_analyte_code)) analyte_name_grp
		FROM 	pathology_master
		WHERE	analyteid = a_analyteid	
		AND 	sub_analyte_code != '---'
		GROUP BY analyteid, analytecode;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  

	OPEN cur_lab_child_analyte;  
		
	read_inner_loop: LOOP

		FETCH cur_lab_child_analyte INTO	v_analyteid, v_analytecode, v_child_grp_name;
		IF done THEN
			LEAVE read_inner_loop;
		END IF;

		If IFNULL(v_child_grp_name, '') <> '' THEN
			SET a = 0;
			SET sub_str = '';
			simple_loop: LOOP

				SET sub_str=SUBSTRING_INDEX(v_child_grp_name,',',1);
				Select Max(id) Into v_child_testid from lab_analyte_master where analytecode = sub_str;

				IF v_child_testid <> '' AND v_child_testid IS NOT NULL THEN
					insert into lab_analyte_category_map
					(parent_lab_analyte_id, child_lab_analyte_id)
					VALUE(a_testid, v_child_testid);
				END IF;

				If POSITION(',' in v_child_grp_name) = 0 THEN	
					LEAVE simple_loop;
				End If;
				
				SET a = a + 1;
				SET v_child_grp_name = REPLACE(v_child_grp_name, CONCAT(sub_str, ','), '');

		   END LOOP simple_loop; 

		END IF;

	END LOOP read_inner_loop;

	CLOSE cur_lab_child_analyte; 
END;