/*
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';