drop procedure sp_analyte_migration_indv;

CREATE PROCEDURE sp_analyte_migration_indv (a_testid INT, a_analyteid VARCHAR(50))
BEGIN
	DECLARE done INT DEFAULT FALSE;
	DECLARE inner_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_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	TAB1.analyteid analyteid, TAB3.analyte_name_grp sub_analyte_name
			FROM 		pathology_master TAB1 
				JOIN pathology_analytemaster TAB2  ON TAB1.id = TAB2.analyteComponentid
				LEFT JOIN
					(
						Select 	PATH.analytecode, 
										PATH.sub_analyte_code,  
										GROUP_CONCAT(distinct trim(PATH.sub_analyte_code)) analyte_name_grp
						FROM 		pathology_master PATH 
						Where 	PATH.analytecode in (
														SELECT B.analyte_header_name
														FROM pathology_master A 
														JOIN pathology_analytemaster B 
														ON A.id = B.analyteComponentid
														WHERE B.analyteid = a_analyteid 
													)
							AND	PATH.sub_analyte_code !='---' 
							AND	sub_analyte_is_active = 'Yes'
							AND	sub_analyte_is_deleted = 'No'
							AND	sub_analyte_name != '...'
						group by PATH.analytecode
					) TAB3 ON TAB2.analyte_header_name = TAB3.analytecode
				WHERE TAB2.analyteid = a_analyteid;
	
	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_child_analyteid, v_child_grp_name;
		IF done THEN
			LEAVE read_inner_loop;
		END IF;

		Select Max(id) Into v_grpchild_testid from lab_analyte_master where analyteid = v_child_analyteid;

		insert into lab_analyte_category_map
		(parent_lab_analyte_id, child_lab_analyte_id)
		VALUE(a_testid, v_grpchild_testid);

		/*select a_testid, v_grpchild_testid;*/

		If IFNULL(v_child_grp_name, '') <> '' THEN
			/*Select a_testid, v_grpchild_testid, v_child_analyteid, v_child_grp_name;*/
			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;
				Select v_grpchild_testid, v_child_testid, sub_str;

				insert into lab_analyte_category_map
				(parent_lab_analyte_id, child_lab_analyte_id)
				VALUE(v_grpchild_testid, v_child_testid);


				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;