SELECT cou.self_id course_self, cou.node_id as course_node,
	cat.self_id category_self, cat.node_id as category_node,
	cou.parent_code course_code, course_category.common_code
FROM category_tree cat, course_category, category_tree2 cou
WHERE course_category.common_code = cou.parent_code 
	AND course_category.id=cat.self_id
	AND cou.type='Course' AND cat.type='Category' ;


UDPATE  category_tree2 cou, course_category, category_tree2 cou
SET cou.parent_node_id=cat.node_id
WHERE course_category.common_code = cou.parent_code 
	AND course_category.id=cat.self_id
	AND cou.type='Course' AND cat.type='Category' ;
	

SELECT smsevent.parent_id, course_category.common_code, course_category.id
FROM smsevent, course_category
WHERE smsevent.parent_id=course_category.common_code ;

UPDATE smsevent, course_category
SET smsevent.parent_id=course_category.id
WHERE smsevent.parent_id=course_category.common_code ;


UPDATE contacts SET idnumber=CONCAT('0190', SUBSTRING(idnumber,4,4)) WHERE SUBSTRING(idnumber,1,3)='019' AND smsgroup='staff'
SELECT CONCAT('0190', SUBSTRING(idnumber,4,4)) as newid, idnumber, smsgroup, last_name, first_name, deleted FROM `contacts` WHERE SUBSTRING(idnumber,1,3)='019' AND smsgroup='staff'

SELECT smsevent.id id_honban, smsevent2.id id_tmp, smsevent.custom_var33 start_honban, smsevent2.custom_var33 start_tmp 
FROM smsevent, smsevent2 WHERE smsevent.id=smsevent2.id 

UPDATE smsevent, smsevent2 SET smsevent.custom_var33=smsevent2.custom_var33 WHERE smsevent.id=smsevent2.id 


SELECT smsevent.id as id, smsevent5.id as newid, smsevent.description, smsevent5.description FROM smsevent, smsevent5 WHERE smsevent.id=smsevent5.id

UPDATE smsevent, smsevent5 SET smsevent.description=smsevent5.description WHERE smsevent.id=smsevent5.id

-- course data extraction 
SELECT `smsevent`.`id` , `smsevent`.`deleted` , `smsevent`.`date_entered` , `smsevent`.`created_by` , `smsevent`.`date_modified` , `smsevent`.`modified_user_id` , `smsevent`.`name` , `smsevent`.`common_code` , `smsevent`.`parent_id` AS category_id, `course_category`.`name` AS category_name, `course_category`.`common_code` AS category_code, `smsevent`.`subtype` , `smsevent`.`shortname` , `smsevent`.`custom_var35` , `smsevent`.`custom_var76` , `smsevent`.`custom_var77` , `smsevent`.`custom_var38` , `smsevent`.`custom_var33` , `smsevent`.`custom_var34` , `smsevent`.`custom_var31` , `smsevent`.`custom_var72` , `smsevent`.`custom_var73` , `smsevent`.`custom_var71` , `smsevent`.`custom_var36` , `smsevent`.`description`
FROM `smsevent` , `course_category`
WHERE `smsevent`.`category` = 'course' AND `smsevent`.`parent_id` = `course_category`.`id` AND `smsevent`.`deleted` = 0 
	AND `smsevent`.`id` = '00016'
	
	
SELECT 
	`smsevent`.`id` as course_id , `course_category`.`common_code` AS category_code, `smsevent`.`name` , 
	CASE `smsevent`.`school_id`
		WHEN 'sssssss-1111' THEN '個人'
		WHEN 'c03b798b-9a80-88e2-a0a7-4906c1ede0cb' THEN '法人'
		WHEN 'af72a884-ab2d-3a4a-ed7d-4c9b21fb44ce' THEN '物販ライセンス'
		ELSE 'none'
	END AS school_name,
	CASE `smsevent`.`subtype`
		WHEN '0a' THEN '見学会'
		WHEN '0b' THEN '説明会'
		WHEN '1' THEN '初めてのマクロ'
		WHEN '2' THEN '入門'
		WHEN '4' THEN '1Day'
		WHEN '5' THEN 'Basic1'
		WHEN '6' THEN 'Basic2'
		WHEN '7' THEN 'Advanced'
		WHEN '7a' THEN '通信B'
		WHEN '7b' THEN '通信Bスクーリング'
		WHEN '7c' THEN '通信Ad'
		WHEN '7d' THEN '通信Ad中間スクーリング'
		WHEN '7e' THEN '通信Ad修了スクーリング'
		WHEN '8' THEN '編入BW'
		WHEN '9' THEN 'レベル1'
		WHEN '10' THEN 'レベル2'
		WHEN '11' THEN 'レベル3'
		WHEN '12' THEN 'スイスタ'
		WHEN '13' THEN 'スイバリ'
		WHEN '14' THEN 'FC'
		WHEN '15' THEN 'FC スイーツ'
		WHEN '16' THEN 'カンファレンス'
		WHEN '17' THEN '講演会'
		WHEN '19' THEN '物販・ライセンス'
		WHEN '18' THEN 'その他'
		ELSE 'none'
	END AS subtype,
	`smsevent`.`shortname` , `smsevent`.`custom_var35` , `smsevent`.`custom_var76` , `smsevent`.`custom_var77` , 
	`smsevent`.`custom_var38` , `smsevent`.`custom_var33` , `smsevent`.`custom_var34` , `smsevent`.`custom_var31` , 
	`smsevent`.`custom_var72` , `smsevent`.`custom_var73` , `smsevent`.`custom_var71` , 
	`smsevent`.`custom_var36` , `smsevent`.`description`, 
	`smsevent`.`common_code` as course_code, `smsevent`.`date_entered` , `smsevent`.`date_modified` , `smsevent`.`parent_id` AS category_id, `course_category`.`name` AS category_name
FROM `smsevent` , `course_category`
WHERE `smsevent`.`category` = 'course' AND `smsevent`.`parent_id` = `course_category`.`id` AND `smsevent`.`deleted` = 0 
	AND `smsevent`.`school_id` != '766b7ba7-b923-df94-9aa3-4d6c9922653b'
	
	AND `smsevent`.`id` = '00016' 

SELECT 
	`smsevent`.`id` AS 'コースＩＤ' , 
	`course_category`.`common_code` AS '共通コード', 
	CASE `smsevent`.`school_id`
		WHEN 'sssssss-1111' THEN '個人'
		WHEN 'c03b798b-9a80-88e2-a0a7-4906c1ede0cb' THEN '法人'
		WHEN 'af72a884-ab2d-3a4a-ed7d-4c9b21fb44ce' THEN '物販ライセンス'
		ELSE 'none'
	END AS '校舎',
	`smsevent`.`name` AS '新コース名（略称）', 
	CASE `smsevent`.`subtype`
		WHEN '0a' THEN '見学会'
		WHEN '0b' THEN '説明会'
		WHEN '1' THEN '初めてのマクロ'
		WHEN '2' THEN '入門'
		WHEN '4' THEN '1Day'
		WHEN '5' THEN 'Basic1'
		WHEN '6' THEN 'Basic2'
		WHEN '7' THEN 'Advanced'
		WHEN '7a' THEN '通信B'
		WHEN '7b' THEN '通信Bスクーリング'
		WHEN '7c' THEN '通信Ad'
		WHEN '7d' THEN '通信Ad中間スクーリング'
		WHEN '7e' THEN '通信Ad修了スクーリング'
		WHEN '8' THEN '編入BW'
		WHEN '9' THEN 'レベル1'
		WHEN '10' THEN 'レベル2'
		WHEN '11' THEN 'レベル3'
		WHEN '12' THEN 'スイスタ'
		WHEN '13' THEN 'スイバリ'
		WHEN '14' THEN 'FC'
		WHEN '15' THEN 'FC スイーツ'
		WHEN '16' THEN 'カンファレンス'
		WHEN '17' THEN '講演会'
		WHEN '19' THEN '物販・ライセンス'
		WHEN '18' THEN 'その他'
		ELSE 'none'
	END AS 'コース分類',
	`smsevent`.`shortname` AS '定員', `smsevent`.`custom_var35` AS '実施日(回数)', `smsevent`.`custom_var76` AS '曜日', 
	`smsevent`.`custom_var77` AS '時間', 	`smsevent`.`custom_var38` AS '実施場所', 
	`smsevent`.`custom_var33` AS '開始日', `smsevent`.`custom_var34` AS '終了日', `smsevent`.
	`custom_var31` AS '新コース名（正式名称)', `smsevent`.`custom_var72` AS '修了プログラム名(KIJ修了証)', 
	`smsevent`.`custom_var73` AS '修了コース名(KIJ修了証)', `smsevent`.`custom_var71` AS 'スクール名(ＫＩＪ修了証)', 
	`smsevent`.`custom_var36` AS '分類4(経理明細)', `smsevent`.`description` AS 'コメント', 
	`smsevent`.`common_code` as course_code, `smsevent`.`date_entered` , `smsevent`.`date_modified` , `smsevent`.`parent_id` AS category_id, `course_category`.`name` AS category_name
FROM `smsevent` , `course_category`
WHERE `smsevent`.`category` = 'course' AND `smsevent`.`parent_id` = `course_category`.`id` AND `smsevent`.`deleted` = 0 
	AND `smsevent`.`school_id` != '766b7ba7-b923-df94-9aa3-4d6c9922653b'
	
	
-- 2011-04-22 new data import

DELETE FROM `contacts` WHERE smsgroup='student' AND school_id != '766b7ba7-b923-df94-9aa3-4d6c9922653b' ;
TRUNCATE `event_contacts`;
TRUNCATE `event_resource`;
TRUNCATE `emailsemail_cstm`;
TRUNCATE `prospect_lists_prospects`;
TRUNCATE `sms_shikaku`;

SELECT DISTINCT tsushin_nohin_jikan FROM `event_contacts`

UPDATE event_contacts SET tsushin_nohin_jikan='gozen' WHERE tsushin_nohin_jikan='午前中' ;
UPDATE event_contacts SET tsushin_nohin_jikan='20_21' WHERE tsushin_nohin_jikan='20-21時' ;
UPDATE event_contacts SET tsushin_nohin_jikan='18_20' WHERE tsushin_nohin_jikan='18-20時' ;
UPDATE event_contacts SET tsushin_nohin_jikan='14_16' WHERE tsushin_nohin_jikan='14-16時' ;
UPDATE event_contacts SET tsushin_nohin_jikan='16_18' WHERE tsushin_nohin_jikan='16-18時' ;
UPDATE event_contacts SET tsushin_nohin_jikan='12_14' WHERE tsushin_nohin_jikan='12-14時' ;

SELECT DISTINCT`juko_date` FROM `sms_shikaku` WHERE 1

UPDATE sms_shikaku SET juko_date=NULL WHERE juko_date='0000-00-00';