drop procedure sp_analyte_migration ;

drop procedure sp_analyte_migration_indv;

CALL sp_analyte_migration;
call sp_analyte_migration_indv(260, 'PGRP3449')
commit;

truncate table lab_analyte_category_map;
Select * from lab_analyte_category_map;
Select * from lab_analyte_master where id = 260;
SELECT id, analyteid FROM lab_analyte_master 	where analyte_category = 'P' 

SELECT SUBSTRING_INDEX('www.mytestpage.info.abcd','/',1);

SELECT POSITION('/' in 'www.mytestpage.info.abcd');

SELECT REPLACE("www", "www.", "");

SELECT id, analyteid FROM lab_analyte_master
	where analyte_category = 'P' and analyteid = 'PGRP3449';
	
	
		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 = 'PGRP3449' 
													)
							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 = 'PGRP3449';
				
Truncate table lab_analyte_master;
Insert Into lab_analyte_master
(analyteid, analytecode, analyte_name, analyte_display_text, department_id, sub_department_id, analyte_category,
method_id, sample_id, analyte_uom, result_type, container_id, analyte_sequence_no, standard_charge,
is_interface, is_range_reference_required, is_flag_required, is_unit_required,
is_active, 
is_printable, 
is_reportable, 
is_orderable,
ip_type, standard_ip_rate, inner_notes, consent_form, remarks
)
Select analyteid, analytecode, analyte_name, analyte_display_text, 0, 0, '',
0, 0,  analyte_uom, result_type, 0, analyte_sequence_no, standard_charge,
is_interface, is_range_reference_required, is_flag_required, is_unit_required,
'Y',
Case when Upper(printable) = 'YES' Then 'Y' ELSE 'N' END, 
Case when Upper(is_reportable) = 'YES' Then 'Y' ELSE 'N' END, 
Case when Upper(orderable) = 'YES' Then 'Y' ELSE 'N' END, 
'P', 0, inner_notes, consent_form, remarks
 from pathology_master
 #where is_active = 'Yes'
 group by analyteid, analyte_name;
 
 Select * from lab_analyte_master;
 
 Update lab_analyte_master
 SET		analyte_category = 'P'
 Where analyteid in 
(Select distinct analyteid from pathology_analytemaster );

Update lab_analyte_master
 SET		analyte_category = 'G'
 Where analyteid in 
(Select distinct analyteid from pathology_analytemaster );

Update lab_analyte_master
 SET		analyte_category = 'G'
 Where analyte_category <> 'P' 
 and analyteid in (
Select analyteid from pathology_master
where sub_analyte_code != '---' and sub_analyte_code is not null
group by analyteid, analytecode, analyte_name)

Update lab_analyte_master


select analyte_category, count(*)
from lab_analyte_master
group by analyte_category

Update lab_analyte_master
SET		analyte_category = 'I'
Where analyte_category Not in ( 'P', 'G')
 
