SELECT smsevent.parent_id, course_category.id as catid FROM smsevent, course_category WHERE smsevent.parent_id=course_category.common_code

UPDATE smsevent, course_category SET smsevent.deleted=1 WHERE smsevent.parent_id=course_category.common_code


SELECT smsevent.parent_id, course_category.id as catid FROM smsevent, course_category WHERE smsevent.parent_id=course_category.common_code

SELECT id, name, parent_id FROM `smsevent` WHERE deleted=0

UPDATE smsevent, course_category SET smsevent.parent_id=course_category.id WHERE smsevent.parent_id=course_category.common_code AND course_category.deleted=0


SELECT name, self_id, id, node_id, parent_node_id FROM course_category, category_tree WHERE course_category.id=category_tree.self_id

SELECT category_tree.node_id, smsevent.name, smsevent.id FROM smsevent, category_tree, category_tree2
WHERE category_tree.self_id=smsevent.parent_id AND category_tree2.self_id=smsevent.id

UPDATE smsevent, category_tree, category_tree2 SET category_tree2.parent_node_id=category_tree.node_id
WHERE category_tree.self_id=smsevent.parent_id AND category_tree2.self_id=smsevent.id 

SELECT smsevent.id, smsevent3.new_id FROM smsevent, smsevent3 WHERE smsevent.id=smsevent3.id

UPDATE smsevent, smsevent3 SET smsevent.id=smsevent3.new_id WHERE smsevent.id=smsevent3.id


SELECT category_tree.self_id, smsevent3.new_id FROM category_tree, smsevent3 WHERE category_tree.self_id=smsevent3.id
UPDATE category_tree, smsevent3 SET category_tree.self_id=smsevent3.new_id WHERE category_tree.self_id=smsevent3.id


UPDATE category_tree2, smsevent3 SET category_tree2.self_id=smsevent3.new_id WHERE category_tree2.self_id=smsevent3.id


UPDATE smsevent, course_category SET smsevent.parent_id=course_category.id WHERE smsevent.parent_id=course_category.common_code AND course_category.deleted=0


SELECT smsevent.name, course_category.name, category_tree2.self_id, category_tree2.parent_node_id, course_category.id
FROM course_category, category_tree2, smsevent 
WHERE smsevent.id=category_tree2.self_id AND course_category.deleted=0 AND course_category.common_code=category_tree2.parent_node_id
 
UPDATE course_category, category_tree2, smsevent SET smsevent.parent_id = course_category.id 
WHERE smsevent.id=category_tree2.self_id AND course_category.deleted=0 AND course_category.common_code=category_tree2.parent_node_id
 

SELECT category_tree2.parent_node_id, category_tree.parent_node_id, category_tree.self_id FROM category_tree, category_tree2 
WHERE category_tree.self_id=category_tree2.self_id

UPDATE category_tree, category_tree2 SET category_tree.parent_node_id=category_tree2.parent_node_id
WHERE category_tree.self_id=category_tree2.self_id


SELECT category_tree2.parent_node_id, course_category.common_code, category_tree.node_id
FROM course_category, category_tree, category_tree2 
WHERE course_category.common_code=category_tree2.parent_node_id AND course_category.deleted=0 AND category_tree.type='Category'
AND category_tree.self_id=course_category.id

UPDATE course_category, category_tree, category_tree2 SET category_tree2.parent_node_id=category_tree.node_id
WHERE course_category.common_code=category_tree2.parent_node_id AND course_category.deleted=0 AND category_tree.type='Category'
AND category_tree.self_id=course_category.id
 
SELECT sms_fee.id, sms_fee2.unit_type, sms_fee.unit_type FROM sms_fee, sms_fee2 WHERE sms_fee.id=sms_fee2.id
UPDATE sms_fee2 SET unit_type=1 WHERE `unit_type` LIKE '2%'
UPDATE sms_fee, sms_fee2 SET sms_fee.unit_type=sms_fee2.unit_type WHERE sms_fee.id=sms_fee2.id
 

SELECT course_cat.self_id, course_cat.node_id, course_cat.parent_node_id, cat_cat.node_id 
FROM smsevent, category_tree course_cat, category_tree cat_cat
WHERE smsevent.id=course_cat.self_id AND smsevent.parent_id=cat_cat.self_id
AND course_cat.parent_node_id!=cat_cat.node_id

UPDATE smsevent, category_tree course_cat, category_tree cat_cat
SET course_cat.parent_node_id=cat_cat.node_id 
WHERE smsevent.id=course_cat.self_id AND smsevent.parent_id=cat_cat.self_id
AND course_cat.parent_node_id!=cat_cat.node_id


SELECT course_cat.self_id, course_cat.node_id, course_cat.parent_node_id, cat_cat.node_id, course_category.id as cat_id, course_category.common_code
FROM smsevent, category_tree course_cat, category_tree cat_cat, course_category
WHERE smsevent.id=course_cat.self_id AND smsevent.parent_id=cat_cat.self_id AND `custom_var36` LIKE 'MEP進学' AND course_category.id=cat_cat.self_id

SELECT id, name, node_id, parent_node_id
FROM category_tree, course_category
WHERE node_id IN ('622', '623', '624', '626', '630', '631', '632', '131') AND self_id=id

--  DELETE FROM sms_fee WHERE `contact_id` LIKE 'c7c57c29-c29e-314d-f05f-4d0de35465e9' ;

SELECT sell_date, start_date, name, basic_fee, type_id, extra_kubun2, extra_kubun1 FROM `sms_fee` WHERE `contact_id` LIKE 'c7c57c29-c29e-314d-f05f-4d0de35465e9' 
ORDER BY `sms_fee`.`extra_kubun2` ASC, start_date ASC

SELECT sell_date, start_date, name, basic_fee, type_id, extra_kubun2, extra_kubun3 FROM `sms_fee` WHERE `event_id` LIKE 'course043'
ORDER BY `sms_fee`.`extra_kubun2` ASC, start_date ASC LIMIT 0, 50

SELECT sms_fee.id, sms_fee.name, sms_fee.extra_kubun2, extra_kubun3 FROM sms_fee, smsevent WHERE sms_fee.event_id = smsevent.id
AND smsevent.custom_var36='英語' AND sms_fee.extra_kubun3=3

UPDATE sms_fee, smsevent SET extra_kubun3=2 
WHERE sms_fee.event_id = smsevent.id AND smsevent.custom_var36='英語' AND sms_fee.extra_kubun3=3

-- for kazoku ID updates 
SELECT id, assistant, last_name, first_name FROM `contacts` WHERE assistant IS NOT NULL AND assistant!='' ORDER BY assistant ;



SELECT id, name, contact_id, event_id, deleted, extra_kubun2
FROM `sms_fee`
WHERE (extra_kubun2 LIKE 'レベル5' OR extra_kubun2 LIKE 'レベル6' OR extra_kubun2 LIKE 'レベル7' OR extra_kubun2 LIKE 'レベル8' OR extra_kubun2 LIKE 'レベル5-維持費' OR extra_kubun2 LIKE 'レベル6-維持費' ) 
AND contact_id IS NOT NULL
ORDER BY `sms_fee`.`extra_kubun2` ASC

-- 25 fees
SELECT id, name, contact_id, event_id, deleted, extra_kubun2 FROM `sms_fee`
WHERE (extra_kubun2 LIKE 'レベル5' OR extra_kubun2 LIKE 'レベル6' OR extra_kubun2 LIKE 'レベル7' OR extra_kubun2 LIKE 'レベル8' ) AND contact_id IS NOT NULL
AND id LIKE 'fee%'
ORDER BY `sms_fee`.`extra_kubun2` ASC

UPDATE sms_fee SET extra_kubun2=CONCAT(extra_kubun2, '・通常') 
WHERE (extra_kubun2 LIKE 'レベル5' OR extra_kubun2 LIKE 'レベル6' OR extra_kubun2 LIKE 'レベル7' OR extra_kubun2 LIKE 'レベル8' ) AND contact_id IS NOT NULL
AND id LIKE 'fee%'

- 1 fee
SELECT id, name, contact_id, event_id, deleted, extra_kubun2 FROM `sms_fee` WHERE extra_kubun2 LIKE 'レベル5-維持費' AND contact_id IS NOT NULL 
UPDATE sms_fee SET extra_kubun2='レベル5・通常-維持費' WHERE extra_kubun2 LIKE 'レベル5-維持費' AND contact_id IS NOT NULL
- 1 fee
SELECT id, name, contact_id, event_id, deleted, extra_kubun2 FROM `sms_fee` WHERE extra_kubun2 LIKE 'レベル6-維持費' AND contact_id IS NOT NULL
UPDATE sms_fee SET extra_kubun2='レベル6・通常-維持費' WHERE extra_kubun2 LIKE 'レベル6-維持費' AND contact_id IS NOT NULL

UPDATE `sms_fee` SET `extra_kubun3` = '2' WHERE `sms_fee`.`id` = 'fee_00295' LIMIT 1 ; 
UPDATE `sms_fee` SET `extra_kubun3` = '2' WHERE `sms_fee`.`id` = 'fee_00630' LIMIT 1 ;
UPDATE `sms_fee` SET `extra_kubun3` = '2' WHERE `sms_fee`.`id` = 'fee_01099' LIMIT 1 ;
UPDATE `sms_fee` SET `extra_kubun3` = '2' WHERE `sms_fee`.`id` = 'fee_01483' LIMIT 1 ;

SELECT id, name, contact_id, event_id, extra_kubun2, extra_kubun3 FROM sms_fee WHERE id > 'fee_00387' AND id < 'fee_00604'

SELECT * FROM `sms_fee` WHERE `extra_kubun2` LIKE 'PZL-算数' AND contact_id IS NOT NULL
UPDATE sms_fee SET extra_kubun3='0' WHERE `extra_kubun2` LIKE 'PZL-算数' AND contact_id IS NOT NULL;

UPDATE `sms_fee` SET `extra_kubun3` = '2' WHERE `sms_fee`.`id` = 'fee_01099' LIMIT 1 ;
UPDATE `sms_fee` SET `extra_kubun3` = '2' WHERE `sms_fee`.`id` = 'fee_01483' LIMIT 1 ;
UPDATE `sms_fee` SET `extra_kubun3` = '2' WHERE `sms_fee`.`id` = 'fee_00630' LIMIT 1 ;
UPDATE `sms_fee` SET `extra_kubun3` = '2' WHERE `sms_fee`.`id` = 'fee_00295' LIMIT 1 ;



INSERT INTO `resource` (`id`, `school_id`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `name`, `shortname`, `description`, `category`, `location`, `deleted`, `capacity`, `resource_attributes`, `common_code`) VALUES 
 ('sub_001', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'Pre-MEPⅠ-国', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_002', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'Pre-MEPⅠ-理', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_003', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'Pre-MEPⅠ-算', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_004', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'Pre-MEPⅡ-算', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_005', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'Pre-MEPⅡ-国', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_006', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'Pre-MEPⅡ-理', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_007', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEPアネックス-理', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_008', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEPアネックス-算基礎', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_009', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEPアネックス-算難問', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_010', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEPアネックス-国', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_011', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEPベーシック-人', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_012', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEPベーシック-国', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_013', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEPベーシック-理', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_014', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEPベーシック-算基礎', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_015', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEPベーシック-算難問', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_016', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEPベーシック-社', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_017', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEPアドバンス-人', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_018', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEPアドバンスー社', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_019', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEPアドバンス-国', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_020', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEPアドバンス-理', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_021', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEPアドバンス-算基礎', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_022', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEPアドバンス-算難問', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_023', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEPエクセレンス-理', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_024', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEPエクセレンス-社', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_025', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEPエクセレンス-国', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_026', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEPエクセレンス-算難問', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_027', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEPエクセレンス-算', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_028', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEPエクセレンス-人', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_029', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'Pre-MEPⅠ父母会:4月-国', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_030', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'Pre-MEPⅠ父母会:4月-算', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_031', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'Pre-MEPⅡ父母会:4月-国', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_032', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'Pre-MEPⅡ父母会:4月-算', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_033', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'Pre-MEPⅠ父母会:6月-国', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_034', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'Pre-MEPⅠ父母会:6月-算', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_035', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'Pre-MEPⅡ父母会:6月-国', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_036', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'Pre-MEPⅡ父母会:6月-算', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_037', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'Pre-MEPⅠ父母会:9月-国', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_038', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'Pre-MEPⅠ父母会:9月-算', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_039', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'Pre-MEPⅡ父母会:9-国', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_040', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'Pre-MEPⅡ父母会:9-算', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_041', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'Pre-MEPⅠ父母会:11-国', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_042', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'Pre-MEPⅠ父母会:11月-算', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_043', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'Pre-MEPⅡ父母会:11月-国', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_044', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'Pre-MEPⅡ父母会:11月-算', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_045', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'Pre-MEPⅠ父母会:1月-国', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_046', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'Pre-MEPⅠ父母会:1月-算', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_047', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'Pre-MEPⅡ父母会:1-国', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_048', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'Pre-MEPⅡ父母会:1月-算', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_049', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEP父母セミナー:2月-1', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_050', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEP父母セミナー:2月-2', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_051', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEP父母セミナー:3月-1', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_052', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEP父母セミナー:3月-2', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_053', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEP父母セミナー:4月-1', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_054', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEP父母セミナー:4月-2', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_055', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEP父母セミナー:5月-1', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_056', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEP父母セミナー:5月-2', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_057', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEP父母セミナー:6月-1', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_058', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEP父母セミナー:6月-2', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_059', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEP父母セミナー:7月-1', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_060', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEP父母セミナー:7月-2', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_061', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEP父母セミナー:8月-1', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_062', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEP父母セミナー:8月-2', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_063', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEP父母セミナー:9月-1', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_064', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEP父母セミナー:9月-2', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_065', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEP父母セミナー:10月-1', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_066', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEP父母セミナー:10月-2', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_067', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEP父母セミナー:11月-1', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_068', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEP父母セミナー:11月-2', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_069', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEP父母セミナー:12月-1', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_070', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEP父母セミナー:12月-2', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_071', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEP父母セミナー:1月-1', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_072', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'MEP父母セミナー:1月-2', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_073', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'ベーシック小4基幹:水-国', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_074', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'ベーシック小4基幹:水-作', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_075', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'ベーシック小4基幹:水-算', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_076', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'ベーシック小4基幹:土-国', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_077', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'ベーシック小4基幹:土-作', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_078', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'ベーシック小4基幹:土-算', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_079', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'アドバンス小5基幹:土-国', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_080', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'アドバンス小5基幹:土-記', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_081', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'アドバンス小5基幹:土-理', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_082', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'アドバンス小5基幹:土-算', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_083', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'アドバンス小5基幹:土-社', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_084', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'アドバンス小5基幹:月-国', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_085', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'アドバンス小5基幹:月-算', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_086', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'アドバンス小5基幹:月-社', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_087', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'アドバンス小5基幹:月-理', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_088', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'エクセレンス小6基幹:土-算', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_089', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'エクセレンス小6基幹:土-理', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_090', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'エクセレンス小6基幹:土-社', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_091', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'エクセレンス小6基幹:土-国', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_092', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'エクセレンス小6基幹:月-算', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_093', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'エクセレンス小6基幹:月-国', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_094', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'エクセレンス小6基幹:月-理', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_095', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', 'エクセレンス小6基幹:月-社', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_096', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', '中高一貫:水-理', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL),
 ('sub_097', 'sssssss-1111', '2011-03-28 08:00:00', '2011-03-28 08:00:00', '1', '1', '中高一貫:水-文', NULL, NULL, 'subject', NULL, 0, NULL, 'YTowOnt9', NULL);

 
CREATE TABLE IF NOT EXISTS `smsevent_import` (
  `id` varchar(36) DEFAULT NULL,
  `course_id` varchar(36) DEFAULT NULL,
  `subject` varchar(128) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `time_slot` varchar(128) DEFAULT NULL,
  `teacher_id` varchar(36) DEFAULT NULL,
  `room_id` varchar(36) DEFAULT NULL,
  `school_id` varchar(36) DEFAULT NULL,
  `schedule_type` varchar(36) DEFAULT NULL,
  `week_day` varchar(36) DEFAULT NULL,
  `monthly1` date DEFAULT NULL,
  `monthly2` date DEFAULT NULL,
  `monthly3` date DEFAULT NULL,
  `monthly4` date DEFAULT NULL,
  `monthly5` date DEFAULT NULL,
  `monthly6` date DEFAULT NULL,
  `monthly7` date DEFAULT NULL,
  `monthly8` date DEFAULT NULL,
  `monthly9` date DEFAULT NULL,
  `monthly10` date DEFAULT NULL,
  `monthly11` date DEFAULT NULL,
  `monthly12` date DEFAULT NULL,
  `monthly13` date DEFAULT NULL,
  `monthly14` date DEFAULT NULL,
  `monthly15` date DEFAULT NULL,
  `monthly16` date DEFAULT NULL,
  `monthly17` date DEFAULT NULL,
  `monthly18` date DEFAULT NULL,
  `monthly19` date DEFAULT NULL,
  `monthly20` date DEFAULT NULL,
  `monthly21` date DEFAULT NULL,
  `monthly22` date DEFAULT NULL,
  `monthly23` date DEFAULT NULL,
  `monthly24` date DEFAULT NULL,
  KEY `id` (`id`),
  KEY `course_id` (`course_id`),
  KEY `teacher_id` (`teacher_id`),
  KEY `room_id` (`room_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


SELECT smsevent_import.id, subject, resource.id, resource.name  FROM smsevent_import, resource WHERE subject=resource.name
UPDATE smsevent_import, resource SET subject=resource.id WHERE subject=resource.name;
	
UPDATE smsevent_import SET teacher_id='82a02561-4084-ab73-4109-4d3791710603' WHERE teacher_id ='0810190000';
UPDATE smsevent_import SET teacher_id='236b0a68-5463-3188-bc02-4d8aa56b3bb7' WHERE teacher_id ='0810190001';
UPDATE smsevent_import SET teacher_id='4ef5f4c9-02be-337a-d2ea-4d8aa709d9e5' WHERE teacher_id ='0810190002';
UPDATE smsevent_import SET teacher_id='445d7b5e-a48d-55a2-6c79-4d8aa7227ae7' WHERE teacher_id ='0810190003';
UPDATE smsevent_import SET teacher_id='6414547c-173d-152b-64e6-4d8aa5775197' WHERE teacher_id ='0810190004';
UPDATE smsevent_import SET teacher_id='3d8fde0b-0a0e-e8ed-0ac5-4d8aa6cee9bb' WHERE teacher_id ='0810190005';
UPDATE smsevent_import SET teacher_id='25a50ba3-f8ed-d211-4da3-4d8aa71f97e2' WHERE teacher_id ='0810190006';
UPDATE smsevent_import SET teacher_id='d7182dac-105d-0a20-2786-4d8aa6819efb' WHERE teacher_id ='0810190007';
UPDATE smsevent_import SET teacher_id='e1913fd2-c03d-6aaa-6f04-4d8aa239b8f3' WHERE teacher_id ='0810190008';
UPDATE smsevent_import SET teacher_id='b2ab8f92-0f9d-6d91-ca6e-4d8aa5708132' WHERE teacher_id ='0810190009';
UPDATE smsevent_import SET teacher_id='cc3df45b-3f39-d600-53a3-4d8aa6e8effd' WHERE teacher_id ='0810190010';
UPDATE smsevent_import SET teacher_id='fcff00aa-2ea3-2c41-d8f2-4d8aa64a49e3' WHERE teacher_id ='0810190011';
UPDATE smsevent_import SET teacher_id='4aee34d7-0109-9ae1-59ba-4d8aa6524c4e' WHERE teacher_id ='0810190012';
UPDATE smsevent_import SET teacher_id='33f69107-f01f-7bd0-9361-4d8aa7cfb487' WHERE teacher_id ='0810190013';
UPDATE smsevent_import SET teacher_id='b7718260-01a6-d6e4-d5bb-4d8aa5e25cb3' WHERE teacher_id ='0810190014';
UPDATE smsevent_import SET teacher_id='50ce182d-9ee9-0ff2-6c29-4d8aa601b2d3' WHERE teacher_id ='0810190015';
UPDATE smsevent_import SET teacher_id='aef74633-c9a0-ae20-a5b7-4d8aa610792f' WHERE teacher_id ='0810190016';
UPDATE smsevent_import SET teacher_id='57e3b68c-f07e-14bf-3f08-4d8aa5e3feb4' WHERE teacher_id ='0810190017';
UPDATE smsevent_import SET teacher_id='5d3f9953-7efd-c13b-ebb4-4d8aa4edc778' WHERE teacher_id ='0810190018';
UPDATE smsevent_import SET teacher_id='cb532f97-ec72-b94f-3259-4d8aa79b39a2' WHERE teacher_id ='0810190019';
UPDATE smsevent_import SET teacher_id='65f635ee-88c1-b5f2-ffb5-4d8aa5322a0f' WHERE teacher_id ='0810190020';
UPDATE smsevent_import SET teacher_id='e1cb1b2d-3007-d80b-6f0a-4d8aa58b0f6a' WHERE teacher_id ='0810190021';
UPDATE smsevent_import SET teacher_id='4ea2870d-8c69-d965-6693-4d8aa626922e' WHERE teacher_id ='0810190022';
UPDATE smsevent_import SET teacher_id='9c45bd7a-26e0-cf72-1201-4d8aa4aa2493' WHERE teacher_id ='0810190023';
UPDATE smsevent_import SET teacher_id='efa59bf7-033d-94c9-c0cb-4d8aa188febf' WHERE teacher_id ='0810190024';
UPDATE smsevent_import SET teacher_id='c708178d-d09d-a7df-e0aa-4d8aa612dd8f' WHERE teacher_id ='0810190025';
UPDATE smsevent_import SET teacher_id='46385af7-5c22-41ef-7cac-4d8aa5bbd1f4' WHERE teacher_id ='0810190026';
UPDATE smsevent_import SET teacher_id='2e0ed05f-31a6-71de-d0ed-4d8aa68f4d74' WHERE teacher_id ='0810190027';

-- not user_id but contact_id should be set for teachers during schedule imprt
UPDATE smsevent_import SET teacher_id='ce097268-5b3e-8ce5-1d6d-4d41fe6e6fa4' WHERE teacher_id='25a50ba3-f8ed-d211-4da3-4d8aa71f97e2';
UPDATE smsevent_import SET teacher_id='63ec0781-bf6b-d6d2-cc0a-4d41ffb0d8b3' WHERE teacher_id='33f69107-f01f-7bd0-9361-4d8aa7cfb487';
UPDATE smsevent_import SET teacher_id='c5cc0a1d-b26a-43e9-4bfd-4d41fe6b2d03' WHERE teacher_id='445d7b5e-a48d-55a2-6c79-4d8aa7227ae7';
UPDATE smsevent_import SET teacher_id='27171047-d566-1b37-e1ad-4d48e4790cc3' WHERE teacher_id='4ea2870d-8c69-d965-6693-4d8aa626922e';
UPDATE smsevent_import SET teacher_id='2fabd3bb-f766-d50d-87a3-4d41fd05bcc4' WHERE teacher_id='4ef5f4c9-02be-337a-d2ea-4d8aa709d9e5';
UPDATE smsevent_import SET teacher_id='e172acc6-6922-3e35-d4c3-4d42002b19a0' WHERE teacher_id='57e3b68c-f07e-14bf-3f08-4d8aa5e3feb4';
UPDATE smsevent_import SET teacher_id='c0c4b822-8ba6-0ed1-09d0-4d42000ae1b4' WHERE teacher_id='5d3f9953-7efd-c13b-ebb4-4d8aa4edc778';
UPDATE smsevent_import SET teacher_id='39826633-3fda-a7ce-a3f9-4d41fea25668' WHERE teacher_id='6414547c-173d-152b-64e6-4d8aa5775197';
UPDATE smsevent_import SET teacher_id='6ff74d74-4023-79ac-37e8-4d4200fe8402' WHERE teacher_id='65f635ee-88c1-b5f2-ffb5-4d8aa5322a0f';
UPDATE smsevent_import SET teacher_id='78edb37f-3a2e-dea3-a891-4d490214fe6f' WHERE teacher_id='9c45bd7a-26e0-cf72-1201-4d8aa4aa2493';
UPDATE smsevent_import SET teacher_id='ed5c9ea8-3cf4-d793-a469-4d41fe4c6d16' WHERE teacher_id='b2ab8f92-0f9d-6d91-ca6e-4d8aa5708132';
UPDATE smsevent_import SET teacher_id='1b4fb5ba-0e95-b6db-3748-4d41ff4fa207' WHERE teacher_id='b7718260-01a6-d6e4-d5bb-4d8aa5e25cb3';
UPDATE smsevent_import SET teacher_id='71db15b1-7beb-12b8-4556-4d42009756f4' WHERE teacher_id='cb532f97-ec72-b94f-3259-4d8aa79b39a2';
UPDATE smsevent_import SET teacher_id='574d522b-842a-5366-5c69-4d41ff5171b8' WHERE teacher_id='cc3df45b-3f39-d600-53a3-4d8aa6e8effd';
UPDATE smsevent_import SET teacher_id='4fd98a87-866a-7fd0-69e4-4d41fe7ad0c3' WHERE teacher_id='d7182dac-105d-0a20-2786-4d8aa6819efb';
UPDATE smsevent_import SET teacher_id='d3e31a01-c199-2750-7bad-4d41fe33d7cb' WHERE teacher_id='e1913fd2-c03d-6aaa-6f04-4d8aa239b8f3';
UPDATE smsevent_import SET teacher_id='b8d046d3-41df-a75f-d7f5-4d4903115f40' WHERE teacher_id='efa59bf7-033d-94c9-c0cb-4d8aa188febf';
UPDATE smsevent_import SET teacher_id='45ba0a09-5b85-d55a-ca04-4d41ff642f2d' WHERE teacher_id='fcff00aa-2ea3-2c41-d8f2-4d8aa64a49e3';

SELECT smsevent_import.course_id, smsevent3.new_id FROM smsevent_import, smsevent3 WHERE smsevent_import.course_id = smsevent3.id

UPDATE smsevent_import, smsevent3 SET smsevent_import.course_id=new_id  WHERE smsevent_import.course_id = smsevent3.id

-- below courses have multiple line but should be automatically schedulable
SELECT * FROM smsevent_import WHERE course_id IN (
'course057', 'course059', 'course171', 'course172', 'course173', 'course174', 'course175', 'course176', 'course177', 'course178', 'course179', 'course180', 'course181', 'course182', 'course183', 'course184', 'course185', 'course186', 'course187', 'course188', 'course189', 'course190', 'course191', 'course192', 'course193', 'course194', 'course195', 'course196', 'course197', 'course319' )
ORDER BY `smsevent_import`.`course_id` ASC, time_slot ASC
UPDATE smsevent_import SET duplicate=1 WHERE  course_id IN (
'course057', 'course059', 'course171', 'course172', 'course173', 'course174', 'course175', 'course176', 'course177', 'course178', 'course179', 'course180', 'course181', 'course182', 'course183', 'course184', 'course185', 'course186', 'course187', 'course188', 'course189', 'course190', 'course191', 'course192', 'course193', 'course194', 'course195', 'course196', 'course197', 'course319' )

-- below courses are not automatically schedulable!!!
SELECT * FROM smsevent_import WHERE course_id IN ('course057', 'course059', 'course177', 'course178', 'course179', 'course180', 'course181')
ORDER BY `smsevent_import`.`course_id` ASC, time_slot ASC
UPDATE smsevent_import SET duplicate=2 WHERE  course_id IN ('course057', 'course059', 'course177', 'course178', 'course179', 'course180', 'course181')


UPDATE smsevent_import SET duplicate=1 WHERE  course_id IN ('course177', 'course178', 'course179', 'course180', 'course181')

SELECT * FROM `event_contacts` WHERE id LIKE 'ev_con_%' AND event_id NOT LIKE 'course%'
DELETE  FROM `event_contacts` WHERE id LIKE 'ev_con_%' AND event_id NOT LIKE 'course%'

SELECT event_contacts.id, event_contacts.event_id, event_contacts.start_date, event_contacts.description, week_day FROM event_contacts
LEFT JOIN smsevent_import ON event_contacts.event_id=smsevent_import.course_id AND smsevent_import.duplicate='0'
WHERE event_contacts.id LIKE 'ev_con_%'

UPDATE event_contacts, smsevent_import SET description=NULL WHERE event_contacts.event_id=smsevent_import.course_id AND smsevent_import.duplicate='0'
AND  event_contacts.id LIKE 'ev_con_%'
SELECT event_contacts.id, event_contacts.event_id, event_contacts.start_date, event_contacts.description, week_day FROM event_contacts, smsevent_import
WHERE event_contacts.event_id=smsevent_import.course_id AND smsevent_import.duplicate='0' AND event_contacts.id LIKE 'ev_con_%'

SELECT event_id, contact_id, description FROM `event_contacts` WHERE `event_id` IN ('course194','course195','course196')
SELECT event_id, contact_id, description FROM `event_contacts` WHERE `event_id` NOT IN ('course194','course195','course196')


UPDATE event_contacts SET description = NULL WHERE `event_id` NOT  IN ('course194','course195','course196')


TRUNCATE TABLE `event_resource` ;
TRUNCATE TABLE `event_contacts` ;
TRUNCATE TABLE `smsevent_instances` ;
TRUNCATE TABLE `sms_event_instance` ;
TRUNCATE TABLE `sms_recurrence` ;


SELECT event_contacts.start_date, event_contacts2.start_date, event_contacts.end_date, event_contacts2.end_date
FROM event_contacts, event_contacts2 WHERE event_contacts.id=event_contacts2.id 
AND event_contacts2.contact_id LIKE '0810%' AND event_contacts2.event_id LIKE 'course%'

UPDATE event_contacts, event_contacts2 
SET event_contacts.start_date=event_contacts2.start_date, event_contacts.end_date=event_contacts2.end_date
WHERE event_contacts.id=event_contacts2.id AND event_contacts2.contact_id LIKE '0810%' AND event_contacts2.event_id LIKE 'course%'


SELECT event_contacts.start_date, event_contactsm.start_date, event_contacts.end_date, event_contactsm.end_date
FROM event_contacts, event_contactsm WHERE event_contacts.id=event_contactsm.id 
AND event_contactsm.contact_id LIKE '0810%' AND event_contactsm.event_id LIKE 'course%'

UPDATE event_contacts, event_contactsm 
SET event_contacts.start_date=event_contactsm.start_date, event_contacts.end_date=event_contactsm.end_date
WHERE event_contacts.id=event_contactsm.id AND event_contactsm.contact_id LIKE '0810%' AND event_contactsm.event_id LIKE 'course%'

SELECT * FROM `sms_event_instance` WHERE `date_modified` > '2011-03-31 05:30:00'

SELECT * FROM `smsevent_instances` WHERE `date_modified` > '2011-03-31 05:30:00'

DELETE FROM `sms_event_instance` WHERE `date_modified` > '2011-03-31 05:30:00' ;
DELETE FROM `smsevent_instances` WHERE `date_modified` > '2011-03-31 05:30:00' ; 

UPDATE `hegl`.`event_contacts` SET `end_date` = '2010-11-30 00:00:00' WHERE `event_contacts`.`id` = 'ev_con_02546' LIMIT 1;



SELECT event_contacts2.start_date, event_contacts.start_date, event_contacts2.end_date, event_contacts.end_date,
event_contacts2.contact_id, event_contacts2.event_id
FROM event_contacts2, event_contacts
WHERE event_contacts2.contact_id=event_contacts.contact_id AND event_contacts2.event_id=event_contacts.event_id
AND ( event_contacts2.start_date!=event_contacts.start_date OR event_contacts2.end_date!=event_contacts.end_date)



UPDATE event_contacts2, event_contacts
SET event_contacts.start_date=event_contacts2.start_date, event_contacts.end_date=event_contacts2.end_date
WHERE event_contacts2.contact_id=event_contacts.contact_id AND event_contacts2.event_id=event_contacts.event_id
AND ( event_contacts2.start_date!=event_contacts.start_date OR event_contacts2.end_date!=event_contacts.end_date)


--  local hegl DB event_contacts
INSERT INTO `event_contacts` (`id`, `contact_id`, `event_id`, `date_modified`, `deleted`, `event_contact_status`, `start_date`, `end_date`, `event_credit`, `description`, `application_date`, `entrance_date`, `course_kubun`, `contents`) VALUES
('1de5bd80-13ef-960a-d21c-4d8c4f1372f2', 'efee0ea5-da41-3400-266a-4d8c4e4703bd', '25333273-1da3-cd94-408f-4d8c033f63a7', '2011-03-25 08:14:57', 0, '0', NULL, NULL, NULL, NULL, '0000-00-00', '0000-00-00', '', ''),
('1e105771-83be-b735-76a0-4d8c00a8a9e7', 'c7c57c29-c29e-314d-f05f-4d0de35465e9', 'a46d3f7c-39bc-7b3d-a4c8-4d8c000379f0', '2011-03-25 02:40:37', 0, '0', '0000-00-00 00:00:00', '0000-00-00 00:00:00', NULL, NULL, '0000-00-00', '0000-00-00', '', ''),
('27e7507d-86fa-58ec-c69c-4d91d1e455db', '7d1d8d32-d64e-13d8-362f-4d91d1f6fc24', '8f95390d-d124-c2e8-3889-4d91d1977617', '2011-03-29 12:35:05', 0, '4', NULL, NULL, NULL, NULL, '0000-00-00', '0000-00-00', '', ''),
('3b275b3f-ed44-87d3-b8fa-4d8c038cfd32', 'c7c57c29-c29e-314d-f05f-4d0de35465e9', '25333273-1da3-cd94-408f-4d8c033f63a7', '2011-03-25 02:51:54', 0, '0', '0000-00-00 00:00:00', '0000-00-00 00:00:00', NULL, NULL, '0000-00-00', '0000-00-00', '', ''),
('3bc57374-862a-0995-649d-4d91d11ed5d3', 'c7c57c29-c29e-314d-f05f-4d0de35465e9', '8f95390d-d124-c2e8-3889-4d91d1977617', '2011-03-29 12:33:34', 0, '0', NULL, NULL, NULL, NULL, '0000-00-00', '0000-00-00', '', ''),
('3c3ff00e-d727-e970-f109-4d91d11e1d3b', 'efee0ea5-da41-3400-266a-4d8c4e4703bd', '8f95390d-d124-c2e8-3889-4d91d1977617', '2011-03-29 12:33:34', 0, '0', NULL, NULL, NULL, NULL, '0000-00-00', '0000-00-00', '', ''),
('3c89449e-af9a-3137-71ca-4d0ec497eb8c', 'cc2aa006-ba90-df2d-4986-4d0ebfdb778e', 'eac0be89-a877-2773-4cc6-4d0ec3a51d7c', '2010-12-20 02:51:48', 0, '0', '0000-00-00 00:00:00', '0000-00-00 00:00:00', NULL, NULL, '0000-00-00', '0000-00-00', '', ''),
('59c34114-1fe7-726f-c2e3-4d8c02f3e582', 'c7c57c29-c29e-314d-f05f-4d0de35465e9', '54a29707-1a90-e71c-f154-4d8c026a566e', '2011-03-25 02:48:46', 0, '0', '0000-00-00 00:00:00', '0000-00-00 00:00:00', NULL, NULL, '0000-00-00', '0000-00-00', '', ''),
('627c5f70-3097-c6cb-8bf6-4d91d17a0077', '1d22de46-dfa9-99e0-5053-4d3791b389be', '8f95390d-d124-c2e8-3889-4d91d1977617', '2011-03-29 12:34:15', 0, '4', NULL, NULL, NULL, NULL, '0000-00-00', '0000-00-00', '', ''),
('bb9d2d24-5c3a-f491-259c-4d8c0112981f', 'c7c57c29-c29e-314d-f05f-4d0de35465e9', 'a64a1f60-94f5-3bd7-c739-4d8c019be209', '2011-03-25 02:48:15', 1, '0', '0000-00-00 00:00:00', '0000-00-00 00:00:00', NULL, NULL, '0000-00-00', '0000-00-00', '', '');

--  honban hegl DB event_contacts


--  test hegl DB event_contacts


-- fee updates 20110403

SELECT sms_fee.basic_fee as old_fee, sms_fee3.basic_fee FROM sms_fee3, sms_fee WHERE sms_fee3.id=sms_fee.id ;

UPDATE sms_fee3, sms_fee SET sms_fee.basic_fee=sms_fee3.basic_fee WHERE sms_fee3.id=sms_fee.id ;


CREATE TABLE IF NOT EXISTS `sms_fee4` (
  `id` varchar(36) NOT NULL DEFAULT '',
  `deleted` tinyint(4) NOT NULL DEFAULT '0',
  `modified_name` varchar(20) DEFAULT NULL,
  `name` varchar(100) NOT NULL DEFAULT '',
  `modified_fee` varchar(20) DEFAULT NULL,
  `basic_fee` int(11) DEFAULT '0',
  `modified_dueto` varchar(20) DEFAULT NULL,
  `due_to` varchar(10) DEFAULT '0',
  `modified_desc` varchar(20) DEFAULT NULL,
  `description` text,
  `modified_kubun3` varchar(20) DEFAULT NULL,
  `extra_kubun3` varchar(256) DEFAULT NULL,
  `modified_hinmei` varchar(20) DEFAULT NULL,
  `hinmei` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

SELECT * FROM sms_fee4 WHERE modified_name='modified' ;
SELECT * FROM sms_fee4 WHERE modified_fee='modified' ;
SELECT * FROM sms_fee4 WHERE modified_dueto='modified' ;
SELECT * FROM sms_fee4 WHERE modified_desc='modified' ;
SELECT * FROM sms_fee4 WHERE modified_kubun3='modified' ;
SELECT * FROM sms_fee4 WHERE modified_hinmei='modified' ;

UPDATE `sms_fee4` SET deleted=0 WHERE modified_name='modified' ;
UPDATE `sms_fee4` SET deleted=0 WHERE modified_fee='modified' ;
UPDATE `sms_fee4` SET deleted=0 WHERE modified_dueto='modified' ;
UPDATE `sms_fee4` SET deleted=0 WHERE modified_desc='modified' ;
UPDATE `sms_fee4` SET deleted=0 WHERE modified_kubun3='modified' ;
UPDATE `sms_fee4` SET deleted=0 WHERE modified_hinmei='modified' ;

SELECT sms_fee.name, sms_fee4.name FROM sms_fee, sms_fee4 
WHERE sms_fee.id=sms_fee4.id AND modified_name='modified'


UPDATE sms_fee, sms_fee4 SET sms_fee.name=sms_fee4.name WHERE sms_fee.id=sms_fee4.id AND modified_name='modified' ;
UPDATE sms_fee, sms_fee4 SET sms_fee.basic_fee=sms_fee4.basic_fee WHERE sms_fee.id=sms_fee4.id AND modified_fee='modified' ;
UPDATE sms_fee, sms_fee4 SET sms_fee.due_to=sms_fee4.due_to WHERE sms_fee.id=sms_fee4.id AND modified_dueto='modified' ;
UPDATE sms_fee, sms_fee4 SET sms_fee.description=sms_fee4.description WHERE sms_fee.id=sms_fee4.id AND modified_desc='modified' ;
UPDATE sms_fee, sms_fee4 SET sms_fee.extra_kubun3=sms_fee4.extra_kubun3 WHERE sms_fee.id=sms_fee4.id AND modified_kubun3='modified' ;
UPDATE sms_fee, sms_fee4 SET sms_fee.hinmei=sms_fee4.hinmei WHERE sms_fee.id=sms_fee4.id AND modified_hinmei='modified' ;



CREATE TABLE IF NOT EXISTS `sms_fee5` (
  `id` varchar(36) NOT NULL DEFAULT '',
  `modified` varchar(20) DEFAULT NULL,
  `deleted` varchar(20) DEFAULT NULL,
  `modified_name` varchar(20) DEFAULT NULL,
  `name` varchar(100) NOT NULL DEFAULT '',
  `modified_fee` varchar(20) DEFAULT NULL,
  `basic_fee` int(11) DEFAULT '0',
  `modified_hinmei` varchar(20) DEFAULT NULL,
  `hinmei` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


UPDATE `sms_fee5` SET deleted=0 WHERE modified_name='modified' ;
UPDATE `sms_fee5` SET deleted=0 WHERE modified_fee='modified' ;
UPDATE `sms_fee5` SET deleted=0 WHERE modified_hinmei='modified' ;

SELECT sms_fee.name, sms_fee5.name FROM sms_fee, sms_fee5 
WHERE sms_fee.id=sms_fee5.id AND modified_name='modified';
SELECT sms_fee5.name, sms_fee6.name, sms_fee5.hinmei, sms_fee6.hinmei FROM sms_fee5, sms_fee6 WHERE sms_fee5.id=sms_fee6.id; 
UPDATE sms_fee5, sms_fee6 SET sms_fee5.name=sms_fee6.name, sms_fee5.hinmei=sms_fee6.hinmei WHERE sms_fee5.id=sms_fee6.id ;

UPDATE sms_fee, sms_fee5 SET sms_fee.name=sms_fee5.name WHERE sms_fee.id=sms_fee5.id AND modified_name='modified' ;
UPDATE sms_fee, sms_fee5 SET sms_fee.basic_fee=sms_fee5.basic_fee WHERE sms_fee.id=sms_fee5.id AND modified_fee='modified' ;
UPDATE sms_fee, sms_fee5 SET sms_fee.hinmei=sms_fee5.hinmei WHERE sms_fee.id=sms_fee5.id AND modified_hinmei='modified' ;
UPDATE sms_fee, sms_fee5 SET sms_fee.deleted=1 WHERE sms_fee.id=sms_fee5.id AND sms_fee5.deleted='delete' ;


SELECT sms_transaction.in_amount, sms_transaction.actual_in_amount, sms_fee.basic_fee 
FROM sms_fee, sms_transaction
WHERE sms_transaction.fee_id=sms_fee.id AND sms_fee.basic_fee!=sms_transaction.in_amount AND SUBSTRING(sms_fee.id, 1, 4) = 'fee_'

UPDATE sms_fee, sms_transaction SET sms_transaction.actual_in_amount=sms_fee.basic_fee, sms_transaction.in_amount=sms_fee.basic_fee 
WHERE sms_transaction.fee_id=sms_fee.id AND sms_fee.basic_fee!=sms_transaction.in_amount AND SUBSTRING(sms_fee.id, 1, 4) = 'fee_' ;

SELECT sms_transaction.deleted, sms_transaction.in_amount, sms_fee.deleted 
FROM sms_fee, sms_transaction
WHERE sms_transaction.fee_id=sms_fee.id AND sms_fee.deleted!=sms_transaction.deleted AND SUBSTRING(sms_fee.id, 1, 4) = 'fee_'

UPDATE sms_fee, sms_transaction SET sms_transaction.deleted=1
WHERE sms_transaction.fee_id=sms_fee.id AND sms_fee.deleted=1 AND sms_transaction.deleted=0 AND SUBSTRING(sms_fee.id, 1, 4) = 'fee_' ;


UPDATE sms_fee, sms_transaction SET sms_transaction.actual_in_amount=sms_fee.basic_fee, sms_transaction.in_amount=sms_fee.basic_fee 
WHERE sms_transaction.fee_id=sms_fee.id AND sms_fee.basic_fee!=sms_transaction.in_amount AND SUBSTRING(sms_fee.id, 1, 4) = 'fee_' ;

SELECT sms_fee20110407.id, sms_fee20110407.extra_kubun3, sms_fee.extra_kubun3 FROM sms_fee20110407, sms_fee 
WHERE sms_fee20110407.id=sms_fee.id AND sms_fee20110407.extra_kubun3!=sms_fee.extra_kubun3 AND sms_fee.extra_kubun3=''

UPDATE sms_fee20110407, sms_fee SET sms_fee.extra_kubun3=sms_fee20110407.extra_kubun3
WHERE sms_fee20110407.id=sms_fee.id AND sms_fee20110407.extra_kubun3!=sms_fee.extra_kubun3 AND sms_fee.extra_kubun3=''


SELECT sms_fee20110408.id, sms_fee20110408.extra_kubun3, sms_fee.extra_kubun3 FROM sms_fee20110408, sms_fee 
WHERE sms_fee20110408.id=sms_fee.id AND sms_fee20110408.extra_kubun3!=sms_fee.extra_kubun3 AND sms_fee.extra_kubun3=''

UPDATE sms_fee20110408, sms_fee SET sms_fee.extra_kubun3=sms_fee20110408.extra_kubun3
WHERE sms_fee20110408.id=sms_fee.id AND sms_fee20110408.extra_kubun3!=sms_fee.extra_kubun3 AND sms_fee.extra_kubun3=''



CREATE TABLE IF NOT EXISTS `sms_fee7` (
  `course_id` varchar(36) DEFAULT NULL,
  `fee_id` varchar(36) DEFAULT NULL,
  `new_id` varchar(36) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


SELECT sms_fee7.new_id, sms_fee7.course_id as event_id, sms_fee.* FROM sms_fee, sms_fee7
WHERE sms_fee.id=sms_fee7.fee_id

INSERT INTO sms_fee8 (`new_id`, `course_id`, `id`, `school_id`, `name`, `event_id`, `contact_id`, `account_id`, `type_id`, `deleted`, `date_entered`, `date_modified`, `sell_date`, `modified_user_id`, `created_by`, `basic_fee`, `unit_type`, `unit_no`, `period`, `period_no`, `due_to`, `description`, `applied_contact`, `discount_percentage`, `discount_price`, `salesno`, `start_date`, `end_date`, `product_id`, `product_count`, `ticket_count`, `ticket_valid`, `jikyu_rate`, `event_credit`, `fee_subcategory`, `source_gessha_id`, `next_gessha_applied`, `extra_kubun1`, `extra_kubun2`, `extra_kubun3`, `hinmei`) 
SELECT sms_fee7.new_id, sms_fee7.course_id, sms_fee.* FROM sms_fee, sms_fee7
WHERE sms_fee.id=sms_fee7.fee_id

UPDATE sms_fee SET deleted=1 WHERE id IN 
('coursefee02326', 'coursefee02327', 'coursefee02328', 'coursefee02330', 'coursefee02331', 'coursefee02332', 'coursefee02334', 'coursefee02335', 
'coursefee02336', 'coursefee02338', 'coursefee02339', 'coursefee02340', 'coursefee02382', 'coursefee02383', 'coursefee02384', 'coursefee02385', 
'coursefee02386', 'coursefee02387', 'coursefee02388', 'coursefee02389', 'coursefee02390', 'coursefee02391', 'coursefee02392', 'coursefee02393', 
'coursefee02394', 'coursefee02395', 'coursefee02396', 'coursefee02397', 'coursefee02413') ;

UPDATE sms_fee SET extra_kubun3=1 WHERE `id` LIKE '%coursefee02413' ; 

SELECT sms_fee.extra_kubun2,sms_fee.extra_kubun3, id, contact_id, event_id FROM sms_fee
WHERE event_id IN ('course043', 'course044') AND `extra_kubun2` LIKE '%維持費' ; 

UPDATE sms_fee SET extra_kubun3=2 WHERE event_id IN ('course043', 'course044') AND `extra_kubun2` LIKE '%維持費' ; 


SELECT 'xxx', id, 'yyy' FROM users WHERE user_name LIKE 'kensyu%' 

SELECT * FROM users
SELECT * FROM users_schools 
SELECT * FROM `acl_roles_users`

WHERE user_id IN ('ee6e2ff9-85f8-39c9-2b05-4d9190ac44b8', '73412cb6-17c5-cafa-5a4a-4d919575c5cc', 
'b06826dc-d14c-9455-90b7-4d91913bb956', 'd21d148a-9b00-c31c-2aae-4d9192d60c68', '601aa8a0-baf3-8ccc-a3e5-4d9192ecc881', 
'55b0a55f-d49a-afa7-a640-4d9193c2e8d5', '74b9d2a0-d602-5b82-6465-4d91930ff969', '39d2f6e8-7c16-c05f-a0c0-4d91946e3f5a', 
'35f1e94d-37d9-6196-7565-4d919411b37b', 'cc44ce44-68f9-4a76-0ec9-4d9195d4b02f')


SELECT 'xxx', contact_id, 'yyy' FROM users WHERE user_name LIKE 'kensyu%' 

SELECT * FROM contacts 
WHERE id IN ('b95c3844-54e6-13fd-0c62-4d9190b4f911', 'fdb50823-c0d5-7d79-c563-4d9195cb1fa7', 'a117b253-6120-185e-d609-4d919177568c', 
'9b60d6b7-7799-beb2-3273-4d91928d8513', 'ec424e1e-0c8e-b867-9823-4d91923bdb21', '4bb7e7c7-161b-1801-43c8-4d91938090ef', 
'374b03f0-c3a0-4652-7413-4d9193f06807', 'bc7835fd-a861-dc4f-f210-4d9194fa6aaa', 'dd1fa979-2a17-c9d1-847e-4d91946d5b69', 
'9148b736-3b27-2b72-34ad-4d91958ac234')