/*

R23 :โรต้า3-6 6 เดือน (อายุไม่เกิน 32 สัปดาห์)

*/

# person_vaccine

DELETE FROM person_vaccine WHERE export_vaccine_code ='R23';

SET @R23 = (select max(person_vaccine_id)+1 from person_vaccine) ;

INSERT IGNORE INTO person_vaccine ( person_vaccine_id , vaccine_name , vaccine_code , vaccine_group , export_vaccine_code , hos_guid , combine_vaccine , icode ) VALUES (@R23, 'R32:โรต้า3-2 6 เดือนอายุไม่เกิน 32 สัปดาห์', 'RV3-3', 'RV', 'R23', NULL, 'N', NULL);

-- SELECT * FROM person_vaccine wHERE export_vaccine_code = 'R23';

# vaccine_combination

SET @c23 = ( SELECT MAX(vaccine_combination_id)+1 FROM vaccine_combination) ;

DELETE FROM vaccine_combination WHERE vaccine_code= 'RV3-3';

INSERT IGNORE INTO vaccine_combination (vaccine_combination_id, vaccine_code, vaccine_combine_code, hos_guid) VALUES (@c23, 'RV3-3', 'RV3-3', NULL);

UPDATE SERIAL

SET serial_no = (SELECT MAX(vaccine_combination_id) FROM vaccine_combination)

WHERE serial.name = 'vaccine_combination_id';

-- select * FROM vaccine_combination WHERE vaccine_code= 'RV3-3';

# wbc_vaccine

DELETE FROM wbc_vaccine WHERE export_vaccine_code='R23';

SET @w23 = (

SELECT MAX(wbc_vaccine_id)+1

FROM wbc_vaccine) ;

INSERT IGNORE INTO wbc_vaccine (wbc_vaccine_id, wbc_vaccine_name, wbc_vaccine_code, age_min, age_max, export_vaccine_code, check_code, vaccine_in_use, hos_guid, icode, price, combine_vaccine) VALUES (@w23, 'R32:โรต้า3-2 6 เดือนอายุไม่เกิน 32 สัปดาห์', 'RV3-3', 24, 32, 'R23', 'R23', NULL, NULL, NULL, NULL, 'N');

-- select * FROM wbc_vaccine WHERE export_vaccine_code='R23';

# provis_vcctype

DELETE FROM provis_vcctype WHERE code='R23';

INSERT IGNORE INTO provis_vcctype (code, name, hos_guid) VALUES ('R23', 'RV3-3', NULL);

-- SELECT * FROM provis_vcctype v WHERE v.code ='R23';

# provis_aptype

delete FROM provis_aptype where code = 'R23';

INSERT IGNORE INTO provis_aptype (code,name,hos_guid,hos_guid_ext) VALUES ('R23','RV3-3',NULL,NULL);

-- SELECT * FROM provis_aptype as v where v.code = 'R23';

# baby_items

delete FROM baby_items where export_code = 'R23';

set @b23 = (SELECT max(baby_code)+1 FROM baby_items);

INSERT IGNORE INTO baby_items (baby_code, baby_group, service_code, baby_name, vaccine_code, display_order, export_code, hos_guid) VALUES (@b23, 4, NULL, 'R32:โรต้า3-2 6 เดือนอายุไม่เกิน 32 สัปดาห์', 'RV3-3', NULL, 'R23', NULL);

-- SELECT * FROM baby_items where export_code = 'R23';

# person_vaccine_group_lot

delete FROM person_vaccine_group_lot where vaccine_group = 'RV3-3';

SET @G23 = (select max(person_vaccine_group_lot_id)+1 from person_vaccine_group_lot) ;

INSERT IGNORE INTO person_vaccine_group_lot (person_vaccine_group_lot_id, vaccine_group, vaccine_lot, receive_date, expire_date, receive_qty, left_qty, vaccine_lot_active, hos_guid) VALUES (@G23, 'RV3-3', NULL, NULL, NULL, NULL, NULL, 'Y', NULL);

UPDATE SERIAL

SET serial_no = (

select person_vaccine_group_lot_id

from person_vaccine_group_lot

where vaccine_group = 'RV3-3')

WHERE serial.name = 'person_vaccine_group_lot_id';

SELECT * FROM person_vaccine_group_lot o where o.vaccine_group = 'RV3-3';

/*

R22 :โรต้า 2-2 4 เดือน

*/

# person_vaccine

DELETE FROM person_vaccine WHERE export_vaccine_code ='R22';

SET @R22 = (select max(person_vaccine_id)+1 from person_vaccine) ;

INSERT IGNORE INTO person_vaccine ( person_vaccine_id , vaccine_name , vaccine_code , vaccine_group , export_vaccine_code , hos_guid , combine_vaccine , icode ) VALUES (@R22, 'R22 :โรต้า2-2***4 เดือน', 'RV3-2', 'RV', 'R22', NULL, 'N', NULL);

-- SELECT * FROM person_vaccine wHERE export_vaccine_code = 'R22';

# vaccine_combination

SET @c22 = ( SELECT MAX(vaccine_combination_id)+1 FROM vaccine_combination) ;

DELETE FROM vaccine_combination WHERE vaccine_code= 'RV3-2';

INSERT IGNORE INTO vaccine_combination (vaccine_combination_id, vaccine_code, vaccine_combine_code, hos_guid) VALUES (@c22, 'RV3-2', 'RV3-2', NULL);

UPDATE SERIAL

SET serial_no = (SELECT MAX(vaccine_combination_id) FROM vaccine_combination)

WHERE serial.name = 'vaccine_combination_id';

-- select * FROM vaccine_combination WHERE vaccine_code= 'RV3-2';

# wbc_vaccine

DELETE FROM wbc_vaccine WHERE export_vaccine_code='R22';

SET @w22 = (

SELECT MAX(wbc_vaccine_id)+1

FROM wbc_vaccine) ;

INSERT IGNORE INTO wbc_vaccine (wbc_vaccine_id, wbc_vaccine_name, wbc_vaccine_code, age_min, age_max, export_vaccine_code, check_code, vaccine_in_use, hos_guid, icode, price, combine_vaccine) VALUES (@w22, 'R22 :โรต้า2-2***4 เดือน', 'RV3-2', 16, 32, 'R22', 'R22', NULL, NULL, NULL, NULL, 'N');

-- select * FROM wbc_vaccine WHERE export_vaccine_code='R22';

# provis_vcctype

DELETE FROM provis_vcctype WHERE code='R22';

INSERT IGNORE INTO provis_vcctype (code, name, hos_guid) VALUES ('R22', 'RV3-2', NULL);

-- SELECT * FROM provis_vcctype v WHERE v.code ='R22';

# provis_aptype

delete FROM provis_aptype where code = 'R22';

INSERT IGNORE INTO provis_aptype (code,name,hos_guid,hos_guid_ext) VALUES ('R22','RV3-2',NULL,NULL);

-- SELECT * FROM provis_aptype as v where v.code = 'R22';

# baby_items

delete FROM baby_items where export_code = 'R22';

set @b22 = (SELECT max(baby_code)+1 FROM baby_items);

INSERT IGNORE INTO baby_items (baby_code, baby_group, service_code, baby_name, vaccine_code, display_order, export_code, hos_guid) VALUES (@b22, 4, NULL, 'R22 :โรต้า2-2***4 เดือน', 'RV3-2', NULL, 'R22', NULL);

-- SELECT * FROM baby_items where export_code = 'R22';

# person_vaccine_group_lot

delete FROM person_vaccine_group_lot where vaccine_group = 'RV3-2';

SET @G22 = (select max(person_vaccine_group_lot_id)+1 from person_vaccine_group_lot) ;

INSERT IGNORE INTO person_vaccine_group_lot (person_vaccine_group_lot_id, vaccine_group, vaccine_lot, receive_date, expire_date, receive_qty, left_qty, vaccine_lot_active, hos_guid) VALUES (@G22, 'RV3-2', NULL, NULL, NULL, NULL, NULL, 'Y', NULL);

UPDATE SERIAL

SET serial_no = (

select person_vaccine_group_lot_id

from person_vaccine_group_lot

where vaccine_group = 'RV3-2')

WHERE serial.name = 'person_vaccine_group_lot_id';

SELECT * FROM person_vaccine_group_lot o where o.vaccine_group = 'RV3-2';

/*

R12:โรต้า2-2 2 เดือนอายุไม่เกิน 15 สัปดาห์

*/

# person_vaccine

DELETE FROM person_vaccine WHERE export_vaccine_code ='R12';

SET @R12 = (select max(person_vaccine_id)+1 from person_vaccine) ;

INSERT IGNORE INTO person_vaccine ( person_vaccine_id , vaccine_name , vaccine_code , vaccine_group , export_vaccine_code , hos_guid , combine_vaccine , icode ) VALUES (@R12, 'R12: โรต้า 2-2 2 เดือน (อายุไม่เกิน 15 สัปดาห์)', 'RV2-2', 'RV', 'R12', NULL, 'N', NULL);

-- SELECT * FROM person_vaccine wHERE export_vaccine_code = 'R12';

# vaccine_combination

SET @c11 = ( SELECT MAX(vaccine_combination_id)+1 FROM vaccine_combination) ;

DELETE FROM vaccine_combination WHERE vaccine_code= 'RV2-2';

INSERT IGNORE INTO vaccine_combination (vaccine_combination_id, vaccine_code, vaccine_combine_code, hos_guid) VALUES (@c11, 'RV2-2', 'RV2-2', NULL);

UPDATE SERIAL

SET serial_no = (SELECT MAX(vaccine_combination_id) FROM vaccine_combination)

WHERE serial.name = 'vaccine_combination_id';

-- select * FROM vaccine_combination WHERE vaccine_code= 'RV2-2';

# wbc_vaccine

DELETE FROM wbc_vaccine WHERE export_vaccine_code='R12';

SET @w11 = (

SELECT MAX(wbc_vaccine_id)+1

FROM wbc_vaccine) ;

INSERT IGNORE INTO wbc_vaccine (wbc_vaccine_id, wbc_vaccine_name, wbc_vaccine_code, age_min, age_max, export_vaccine_code, check_code, vaccine_in_use, hos_guid, icode, price, combine_vaccine) VALUES (@w11, 'R12: โรต้า 2-2 2 เดือน (อายุไม่เกิน 15 สัปดาห์)', 'RV2-2', 16, 32, 'R12', 'R12', NULL, NULL, NULL, NULL, 'N');

-- select * FROM wbc_vaccine WHERE export_vaccine_code='R12';

# provis_vcctype

DELETE FROM provis_vcctype WHERE code='R12';

INSERT IGNORE INTO provis_vcctype (code, name, hos_guid) VALUES ('R12', 'RV2-2', NULL);

-- SELECT * FROM provis_vcctype v WHERE v.code ='R12';

# provis_aptype

delete FROM provis_aptype where code = 'R12';

INSERT IGNORE INTO provis_aptype (code,name,hos_guid,hos_guid_ext) VALUES ('R12','RV2-2',NULL,NULL);

-- SELECT * FROM provis_aptype as v where v.code = 'R12';

# baby_items

delete FROM baby_items where export_code = 'R12';

set @b11 = (SELECT max(baby_code)+1 FROM baby_items);

INSERT IGNORE INTO baby_items (baby_code, baby_group, service_code, baby_name, vaccine_code, display_order, export_code, hos_guid) VALUES (@b11, 4, NULL, 'โรต้า 2-2 2 เดือน (อายุไม่เกิน 15 สัปดาห์)', 'RV2-2', NULL, 'R12', NULL);

-- SELECT * FROM baby_items where export_code = 'R12';

# person_vaccine_group_lot

delete FROM person_vaccine_group_lot where vaccine_group = 'RV2-2';

SET @G11 = (select max(person_vaccine_group_lot_id)+1 from person_vaccine_group_lot) ;

INSERT IGNORE INTO person_vaccine_group_lot (person_vaccine_group_lot_id, vaccine_group, vaccine_lot, receive_date, expire_date, receive_qty, left_qty, vaccine_lot_active, hos_guid) VALUES (@G11, 'RV2-2', NULL, NULL, NULL, NULL, NULL, 'Y', NULL);

UPDATE SERIAL

SET serial_no = (

select person_vaccine_group_lot_id

from person_vaccine_group_lot

where vaccine_group = 'RV2-2')

WHERE serial.name = 'person_vaccine_group_lot_id';

SELECT * FROM person_vaccine_group_lot o where o.vaccine_group = 'RV2-2';

/*

R21:โรต้า3-1 2 เดือนอายุไม่เกิน 15 สัปดาห์

*/

# person_vaccine

DELETE FROM person_vaccine WHERE export_vaccine_code ='R21';

SET @R21 = (select max(person_vaccine_id)+1 from person_vaccine) ;

INSERT IGNORE INTO person_vaccine ( person_vaccine_id , vaccine_name , vaccine_code , vaccine_group , export_vaccine_code , hos_guid , combine_vaccine , icode ) VALUES (@R21, 'R21: โรต้า 3-1 2 เดือน (อายุไม่เกิน 15 สัปดาห์)', 'RV3-1', 'RV', 'R21', NULL, 'N', NULL);

-- SELECT * FROM person_vaccine wHERE export_vaccine_code = 'R21';

# vaccine_combination

SET @c21 = ( SELECT MAX(vaccine_combination_id)+1 FROM vaccine_combination) ;

DELETE FROM vaccine_combination WHERE vaccine_code= 'RV3-1';

INSERT IGNORE INTO vaccine_combination (vaccine_combination_id, vaccine_code, vaccine_combine_code, hos_guid) VALUES (@c21, 'RV3-1', 'RV3-1', NULL);

UPDATE SERIAL

SET serial_no = (SELECT MAX(vaccine_combination_id) FROM vaccine_combination)

WHERE serial.name = 'vaccine_combination_id';

-- select * FROM vaccine_combination WHERE vaccine_code= 'RV3-1';

# wbc_vaccine

DELETE FROM wbc_vaccine WHERE export_vaccine_code='R21';

SET @w21 = (

SELECT MAX(wbc_vaccine_id)+1

FROM wbc_vaccine) ;

INSERT IGNORE INTO wbc_vaccine (wbc_vaccine_id, wbc_vaccine_name, wbc_vaccine_code, age_min, age_max, export_vaccine_code, check_code, vaccine_in_use, hos_guid, icode, price, combine_vaccine) VALUES (@w21, 'R21: โรต้า 3-1 2 เดือน (อายุไม่เกิน 15 สัปดาห์)', 'RV3-1', 8, 15, 'R21', 'R21', NULL, NULL, NULL, NULL, 'N');

-- select * FROM wbc_vaccine WHERE export_vaccine_code='R21';

# provis_vcctype

DELETE FROM provis_vcctype WHERE code='R21';

INSERT IGNORE INTO provis_vcctype (code, name, hos_guid) VALUES ('R21', 'RV3-1', NULL);

-- SELECT * FROM provis_vcctype v WHERE v.code ='R21';

# provis_aptype

delete FROM provis_aptype where code = 'R21';

INSERT IGNORE INTO provis_aptype (code,name,hos_guid,hos_guid_ext) VALUES ('R21','RV3-1',NULL,NULL);

-- SELECT * FROM provis_aptype as v where v.code = 'R21';

# baby_items

delete FROM baby_items where export_code = 'R21';

set @b21 = (SELECT max(baby_code)+1 FROM baby_items);

INSERT IGNORE INTO baby_items (baby_code, baby_group, service_code, baby_name, vaccine_code, display_order, export_code, hos_guid) VALUES (@b21, 4, NULL, 'โรต้า 3-1 2 เดือน (อายุไม่เกิน 15 สัปดาห์)', 'RV3-1', NULL, 'R21', NULL);

-- SELECT * FROM baby_items where export_code = 'R21';

# person_vaccine_group_lot

delete FROM person_vaccine_group_lot where vaccine_group = 'RV3-1';

SET @G21 = (select max(person_vaccine_group_lot_id)+1 from person_vaccine_group_lot) ;

INSERT IGNORE INTO person_vaccine_group_lot (person_vaccine_group_lot_id, vaccine_group, vaccine_lot, receive_date, expire_date, receive_qty, left_qty, vaccine_lot_active, hos_guid) VALUES (@G21, 'RV3-1', NULL, NULL, NULL, NULL, NULL, 'Y', NULL);

UPDATE SERIAL

SET serial_no = (

select person_vaccine_group_lot_id

from person_vaccine_group_lot

where vaccine_group = 'RV3-1')

WHERE serial.name = 'person_vaccine_group_lot_id';

SELECT * FROM person_vaccine_group_lot o where o.vaccine_group = 'RV3-1';

/*

R11:โรต้า 2 เดือน (อายุไม่เกิน 15 สัปดาห์)8to15week

*/

# person_vaccine

DELETE FROM person_vaccine WHERE export_vaccine_code ='R11';

SET @R11 = (select max(person_vaccine_id)+1 from person_vaccine) ;

INSERT IGNORE INTO person_vaccine ( person_vaccine_id , vaccine_name , vaccine_code , vaccine_group , export_vaccine_code , hos_guid , combine_vaccine , icode ) VALUES (@R11, 'R11: โรต้า 2-1 2 เดือน (อายุไม่เกิน 15 สัปดาห์)', 'RV2-1', 'RV', 'R11', NULL, 'N', NULL);

-- SELECT * FROM person_vaccine wHERE export_vaccine_code = 'R11';

# vaccine_combination

SET @c11 = ( SELECT MAX(vaccine_combination_id)+1 FROM vaccine_combination) ;

DELETE FROM vaccine_combination WHERE vaccine_code= 'RV2-1';

INSERT IGNORE INTO vaccine_combination (vaccine_combination_id, vaccine_code, vaccine_combine_code, hos_guid) VALUES (@c11, 'RV2-1', 'RV2-1', NULL);

UPDATE SERIAL

SET serial_no = (SELECT MAX(vaccine_combination_id) FROM vaccine_combination)

WHERE serial.name = 'vaccine_combination_id';

-- select * FROM vaccine_combination WHERE vaccine_code= 'RV2-1';

# wbc_vaccine

DELETE FROM wbc_vaccine WHERE export_vaccine_code='R11';

SET @w11 = (

SELECT MAX(wbc_vaccine_id)+1

FROM wbc_vaccine) ;

INSERT IGNORE INTO wbc_vaccine (wbc_vaccine_id, wbc_vaccine_name, wbc_vaccine_code, age_min, age_max, export_vaccine_code, check_code, vaccine_in_use, hos_guid, icode, price, combine_vaccine) VALUES (@w11, 'R11: โรต้า 2-1 2 เดือน (อายุไม่เกิน 15 สัปดาห์)', 'RV2-1', 8, 15, 'R11', 'R11', NULL, NULL, NULL, NULL, 'N');

-- select * FROM wbc_vaccine WHERE export_vaccine_code='R11';

# provis_vcctype

DELETE FROM provis_vcctype WHERE code='R11';

INSERT IGNORE INTO provis_vcctype (code, name, hos_guid) VALUES ('R11', 'RV2-1', NULL);

-- SELECT * FROM provis_vcctype v WHERE v.code ='R11';

# provis_aptype

delete FROM provis_aptype where code = 'R11';

INSERT IGNORE INTO provis_aptype (code,name,hos_guid,hos_guid_ext) VALUES ('R11','RV2-1',NULL,NULL);

-- SELECT * FROM provis_aptype as v where v.code = 'R11';

# baby_items

delete FROM baby_items where export_code = 'R11';

set @b11 = (SELECT max(baby_code)+1 FROM baby_items);

INSERT IGNORE INTO baby_items (baby_code, baby_group, service_code, baby_name, vaccine_code, display_order, export_code, hos_guid) VALUES (@b11, 4, NULL, 'โรต้า 2-1 2 เดือน (อายุไม่เกิน 15 สัปดาห์)', 'RV2-1', NULL, 'R11', NULL);

-- SELECT * FROM baby_items where export_code = 'R11';

# person_vaccine_group_lot

delete FROM person_vaccine_group_lot where vaccine_group = 'RV2-1';

SET @G11 = (select max(person_vaccine_group_lot_id)+1 from person_vaccine_group_lot) ;

INSERT IGNORE INTO person_vaccine_group_lot (person_vaccine_group_lot_id, vaccine_group, vaccine_lot, receive_date, expire_date, receive_qty, left_qty, vaccine_lot_active, hos_guid) VALUES (@G11, 'RV2-1', NULL, NULL, NULL, NULL, NULL, 'Y', NULL);

UPDATE SERIAL

SET serial_no = (

select person_vaccine_group_lot_id

from person_vaccine_group_lot

where vaccine_group = 'RV2-1')

WHERE serial.name = 'person_vaccine_group_lot_id';

SELECT * FROM person_vaccine_group_lot o where o.vaccine_group = 'RV2-1';