-- ※各テーブルを更新前にバックアップを作成する

-- 対象カリキュラム:curriculum
コード：1000033　→　1000034
ID：1000033　→　6388635d-64c4-42bf-fe3d-568f0b5715ba

-- カリキュラム-科目番号:curriculum_instance
NKCで作成済
科目番号５の削除、６→５、７→６に変更

-- カリキュラム-科目番号-資格：curriculum_instance_shikaku
NKCで作成済

-- カリキュラム-講師:curriculum_contacts : 
講師一覧の担当可能カリキュラム検索で新旧カリキュラムの人数を確認

１．重複する可能性のあるcurriculum_contactsを抽出し、削除する:1
SELECT * FROM curriculum_contacts WHERE curriculum_id = '6388635d-64c4-42bf-fe3d-568f0b5715ba' AND contact_id IS NOT NULL AND deleted =0;

２．対象のcurriculum_contactsを抽出し、CSVでエクスポートする:219 
SELECT * FROM curriculum_contacts WHERE curriculum_id = '1000033' AND contact_id IS NOT NULL AND deleted =0;

３．データを新カリキュラムように変更
　　curriculum_contactsのIDを任意に変更（例：cu_con_0001〜cu_con_0218）
　　date_modifiedを任意に変更（例：2016-01-28 00:00:00）
　　curriculum_idを新ID(6388635d-64c4-42bf-fe3d-568f0b5715ba)に変更

４．変更したデータをCSVアップロードする

-- クラス:smsevent 113 
１．nen、kiが2016、4以降のクラスを抽出し、curriculum_idを新IDに変更

SELECT * FROM smsevent
WHERE nen >=2016 AND ki >=4 AND curriculum_id = '1000033' AND deleted =0;

UPDATE smsevent SET curriculum_id = '6388635d-64c4-42bf-fe3d-568f0b5715ba' 
WHERE nen >=2016 AND ki >=4 AND curriculum_id = '1000033' AND deleted =0;

-- クラス-科目番号:sms_event_instance・sms_event_instance_sub
smseventのIDをキーにしているため、smsevent.idの変更不要

１．ins_orderが５と７で切り分ける　→--５のクラスはNKC側で７に戻すため、別作業は不要
SELECT sms_event_instance.parent_id,sms_event_instance.event_type,sms_event_instance.from_datetime,
	sms_event_instance.to_datetime,sms_event_instance.deleted,sms_event_instance.status,MAX(ins_order)
FROM smsevent
	INNER JOIN sms_event_instance ON sms_event_instance.parent_id = smsevent.id
	INNER JOIN sms_event_instance_sub ON sms_event_instance_sub.instance_id = sms_event_instance.id
WHERE nen >=2016 AND ki >=4 AND curriculum_id = '6388635d-64c4-42bf-fe3d-568f0b5715ba' AND smsevent.deleted =0
GROUP BY smsevent.id
HAVING MAX(ins_order)=7;

-- ・ins_orderが１〜５のクラス → 作業は不要
-- sms_event_instance・sms_event_instance_subからins_order=5を削除（delete=1）

・ins_orderが１〜７のクラス

-- 対象スケジュール:科目番号７ 113
SELECT * FROM smsevent
	INNER JOIN sms_event_instance ON sms_event_instance.parent_id = smsevent.id
	INNER JOIN sms_event_instance_sub ON sms_event_instance_sub.instance_id = sms_event_instance.id AND sms_event_instance_sub.ins_order = 7
WHERE smsevent.curriculum_id = '6388635d-64c4-42bf-fe3d-568f0b5715ba';

０．科目番号５，６，７で instance_idのテーブルを作成する
CREATE TABLE sms_event_instance_ins5_20160128
	SELECT smsevent.id AS smsevent_id, sms_event_instance_sub.ins_order, sms_event_instance.id AS instance_id, sms_event_instance.from_datetime, sms_event_instance.to_datetime FROM smsevent
		INNER JOIN sms_event_instance ON sms_event_instance.parent_id = smsevent.id
		INNER JOIN sms_event_instance_sub ON sms_event_instance_sub.instance_id = sms_event_instance.id AND sms_event_instance_sub.ins_order = 5
	WHERE smsevent.curriculum_id = '6388635d-64c4-42bf-fe3d-568f0b5715ba';

CREATE TABLE sms_event_instance_ins6_20160128
	SELECT smsevent.id AS smsevent_id, sms_event_instance_sub.ins_order, sms_event_instance.id AS instance_id FROM smsevent
		INNER JOIN sms_event_instance ON sms_event_instance.parent_id = smsevent.id
		INNER JOIN sms_event_instance_sub ON sms_event_instance_sub.instance_id = sms_event_instance.id AND sms_event_instance_sub.ins_order = 6
	WHERE smsevent.curriculum_id = '6388635d-64c4-42bf-fe3d-568f0b5715ba';

CREATE TABLE sms_event_instance_ins7_20160128
	SELECT smsevent.id AS smsevent_id, sms_event_instance_sub.ins_order, sms_event_instance.id AS instance_id FROM smsevent
		INNER JOIN sms_event_instance ON sms_event_instance.parent_id = smsevent.id
		INNER JOIN sms_event_instance_sub ON sms_event_instance_sub.instance_id = sms_event_instance.id AND sms_event_instance_sub.ins_order = 7
	WHERE smsevent.curriculum_id = '6388635d-64c4-42bf-fe3d-568f0b5715ba';

ALTER TABLE sms_event_instance_ins5_20160128 ADD INDEX ( smsevent_id ) ;
ALTER TABLE sms_event_instance_ins5_20160128 ADD INDEX ( instance_id ) ;

ALTER TABLE sms_event_instance_ins6_20160128 ADD INDEX ( smsevent_id ) ;
ALTER TABLE sms_event_instance_ins6_20160128 ADD INDEX ( instance_id ) ;

ALTER TABLE sms_event_instance_ins7_20160128 ADD INDEX ( smsevent_id ) ;
ALTER TABLE sms_event_instance_ins7_20160128 ADD INDEX ( instance_id ) ;

-- ５，６のsms_event_instanceを合わせる
SELECT sms_event_instance_ins5_20160128.smsevent_id, sms_event_instance_ins5_20160128.instance_id AS instance_id5, sms_event_instance_ins5_20160128.from_datetime, sms_event_instance_ins5_20160128.to_datetime,
		 sms_event_instance_ins6_20160128.instance_id  AS instance_id6
FROM sms_event_instance_ins5_20160128
	INNER JOIN sms_event_instance_ins6_20160128 ON sms_event_instance_ins5_20160128.smsevent_id = sms_event_instance_ins6_20160128.smsevent_id

CREATE TABLE sms_event_instance_ins56_20160128 
	SELECT sms_event_instance_ins5_20160128.smsevent_id, sms_event_instance_ins5_20160128.instance_id AS instance_id5, sms_event_instance_ins5_20160128.from_datetime, sms_event_instance_ins5_20160128.to_datetime,
			 sms_event_instance_ins6_20160128.instance_id  AS instance_id6
	FROM sms_event_instance_ins5_20160128
		INNER JOIN sms_event_instance_ins6_20160128 ON sms_event_instance_ins5_20160128.smsevent_id = sms_event_instance_ins6_20160128.smsevent_id;

ALTER TABLE sms_event_instance_ins56_20160128 ADD INDEX ( smsevent_id ) ;
ALTER TABLE sms_event_instance_ins56_20160128 ADD INDEX ( instance_id5 ) ;
ALTER TABLE sms_event_instance_ins56_20160128 ADD INDEX ( instance_id6 ) ;


１．sms_event_instanceからins_order=7を削除（delete=1）:113 

SELECT * FROM sms_event_instance
WHERE sms_event_instance.id IN(
	SELECT instance_id FROM sms_event_instance_ins7_20160128
);

UPDATE sms_event_instance SET deleted = '1' 
WHERE sms_event_instance.id IN(
	SELECT instance_id FROM sms_event_instance_ins7_20160128
);

２．ins_order=5のfrom_datetime・to_datetimeでins_order=6を更新

UPDATE
    sms_event_instance,
    sms_event_instance_ins56_20160128
SET 
    sms_event_instance.from_datetime = sms_event_instance_ins56_20160128.from_datetime,
    sms_event_instance.to_datetime = sms_event_instance_ins56_20160128.to_datetime
WHERE
    sms_event_instance.id = sms_event_instance_ins56_20160128.instance_id6 ;

３．curriculum_instanceの各科目番号の開始、終了時間でsms_event_instanceの時間のみを更新

-- 対象カリキュラムの日程
SELECT curriculum_instance.curriculum_id, curriculum_instance.ins_order, curriculum_instance.start_time, curriculum_instance.end_time
FROM curriculum_instance 
WHERE curriculum_id = '6388635d-64c4-42bf-fe3d-568f0b5715ba'
ORDER BY curriculum_instance.ins_order ASC;

-- 対象スケジュールとカリキュラム日程を統合：678 
SELECT sms_event_instance.id,sms_event_instance.parent_id,sms_event_instance.from_datetime,sms_event_instance.to_datetime,sms_event_instance_sub.ins_order,
		curriculum_instance.curriculum_id, curriculum_instance.start_time, curriculum_instance.end_time
FROM sms_event_instance
	INNER JOIN sms_event_instance_sub ON sms_event_instance_sub.instance_id = sms_event_instance.id
	INNER JOIN curriculum_instance ON curriculum_instance.ins_order = sms_event_instance_sub.ins_order AND curriculum_instance.curriculum_id = '6388635d-64c4-42bf-fe3d-568f0b5715ba'
WHERE sms_event_instance.parent_id IN(
	SELECT smsevent_id FROM sms_event_instance_ins5_20160128
	)
	AND sms_event_instance.deleted =0
ORDER BY sms_event_instance.parent_id,sms_event_instance_sub.ins_order;

CREATE TABLE sms_event_instance_ins_all_20160128 
	SELECT sms_event_instance.id,sms_event_instance.parent_id,sms_event_instance.from_datetime,sms_event_instance.to_datetime,sms_event_instance_sub.ins_order,
			curriculum_instance.curriculum_id, curriculum_instance.start_time, curriculum_instance.end_time
	FROM sms_event_instance
		INNER JOIN sms_event_instance_sub ON sms_event_instance_sub.instance_id = sms_event_instance.id
		INNER JOIN curriculum_instance ON curriculum_instance.ins_order = sms_event_instance_sub.ins_order AND curriculum_instance.curriculum_id = '6388635d-64c4-42bf-fe3d-568f0b5715ba'
		
	WHERE sms_event_instance.parent_id IN(
		SELECT smsevent_id FROM sms_event_instance_ins5_20160128
		)
		AND sms_event_instance.deleted =0
	ORDER BY sms_event_instance.parent_id,sms_event_instance_sub.ins_order;

ALTER TABLE sms_event_instance_ins_all_20160128 ADD INDEX ( id ) ;

CREATE TABLE sms_event_instance_ins_all_new_20160128 
	SELECT *,
	concat(substring(from_datetime, 1, 11), start_time) AS new_from_datetime,
	concat(substring(to_datetime, 1, 11), end_time) AS new_to_datetime
	FROM sms_event_instance_ins_all_20160128;

ALTER TABLE sms_event_instance_ins_all_new_20160128 ADD INDEX ( id ) ;

-- sms_event_instanceの時間のみをカリキュラム日程の時間で更新
UPDATE
    sms_event_instance,
    sms_event_instance_ins_all_new_20160128
SET 
    sms_event_instance.from_datetime = sms_event_instance_ins_all_new_20160128.new_from_datetime,
    sms_event_instance.to_datetime = sms_event_instance_ins_all_new_20160128.new_to_datetime
WHERE
    sms_event_instance.id = sms_event_instance_ins_all_new_20160128.id ;


-- 講師スケジュール:smsevent_instances 
smseventのIDをキーにしているため、smsevent.idの変更不要

-- ・ins_orderが１〜５のクラスの講師・教室  → 作業は不要
-- smsevent_instancesからsms_event_instance_subのins_order=5を削除

・ins_orderが１〜７のクラスの講師・教室
１．smsevent_instancesからsms_event_instance_subのins_order=7を削除

-- 計:203  teacher:110 classroom:93

SELECT * FROM smsevent_instances
WHERE smsevent_instances.instance_id IN(
	SELECT instance_id FROM sms_event_instance_ins7_20160128
	)
AND (category = 'teacher' OR category = 'classroom');

DELETE FROM smsevent_instances
WHERE smsevent_instances.instance_id IN(
	SELECT instance_id FROM sms_event_instance_ins7_20160128
	)
AND (category = 'teacher' OR category = 'classroom');

２．ins_order=5のtarget_idでins_order=6を更新
-- 科目番号5の講師：103
SELECT id,event_id,instance_id,target_id,status,instance_contact_status 
FROM smsevent_instances
WHERE smsevent_instances.instance_id IN(
	SELECT instance_id5 FROM sms_event_instance_ins56_20160128
	)
AND category = 'teacher' AND (smsevent_instances.status = 1 OR smsevent_instances.status = 2)
ORDER BY event_id,instance_contact_status,target_id;

-- 科目番号6の講師：103
SELECT id,event_id,instance_id,target_id,status,instance_contact_status 
FROM smsevent_instances
WHERE smsevent_instances.instance_id IN(
	SELECT instance_id6 FROM sms_event_instance_ins56_20160128
	)
AND category = 'teacher' AND (smsevent_instances.status = 1 OR smsevent_instances.status = 2)
ORDER BY event_id,instance_contact_status,target_id;

-- 科目番号6のキャンセル講師：6　→削除（重複回避のため）
SELECT id,event_id,instance_id,target_id,status,instance_contact_status 
FROM smsevent_instances
WHERE smsevent_instances.instance_id IN(
	SELECT instance_id6 FROM sms_event_instance_ins56_20160128
	)
AND category = 'teacher' AND smsevent_instances.status = 17
ORDER BY event_id,instance_contact_status,target_id;

-- 講師は５と６で数が同じのためtarget_idをコピーする
-- ５と６の講師をCSVダウンロードして、target_idごとに統合してアップする
-- （１スケジュールに複数人いるため、どちらに結びつけるかSQLのみでは難しい）
-- おなじエクセルに貼り付け、横のevent_id、instance_contact_statusと縦のinstance_idが同じか比較
-- 以下のテーブルにインサートする

CREATE TABLE smsevent_instances_ins56_20160128 (
  id_ins5 varchar(36) NOT NULL DEFAULT '',
  target_id_ins5 varchar(36) NOT NULL DEFAULT '',
  id_ins6 varchar(36) NOT NULL DEFAULT '',
  target_id_ins6 varchar(36) NOT NULL DEFAULT ''
) ;

 ALTER TABLE smsevent_instances_ins56_20160128 ADD INDEX ( id_ins5 ) ;
 ALTER TABLE smsevent_instances_ins56_20160128 ADD INDEX ( id_ins6 ) ;

-- Duplicate でエラーになるため、プライマリーの設定を一時抜く
ALTER TABLE smsevent_instances DROP PRIMARY KEY;

-- 以下更新文実行:78 
UPDATE
    smsevent_instances,
    smsevent_instances_ins56_20160128
SET 
    smsevent_instances.target_id = smsevent_instances_ins56_20160128.target_id_ins5
WHERE
    smsevent_instances.id = smsevent_instances_ins56_20160128.id_ins6 ;

-- 重複チェック　
SELECT count( * ) , event_id, instance_id, target_id
FROM smsevent_instances
GROUP BY event_id, instance_id, target_id
HAVING count( * ) >1

-- プライマリーを戻す
 ALTER TABLE smsevent_instances ADD PRIMARY KEY ( event_id , instance_id , target_id ) ;

-- 科目番号6のコンフリクト講師：18
SELECT smsevent_instances.id,event_id,instance_id,target_id,status,instance_contact_status,contacts.last_name,contacts.first_name
FROM smsevent_instances
inner join contacts on contacts.id = smsevent_instances.target_id
WHERE smsevent_instances.instance_id IN(
	SELECT instance_id6 FROM sms_event_instance_ins56_20160128
	)
AND category = 'teacher' AND smsevent_instances.status = 2
ORDER BY event_id,instance_contact_status,target_id;

-- 科目番号6のコンフリクト講師のコンフリクト解除
UPDATE smsevent_instances
SET status = '1'
WHERE smsevent_instances.instance_id IN(
	SELECT instance_id6 FROM sms_event_instance_ins56_20160128
	)
AND category = 'teacher' AND smsevent_instances.status = 2;

-- 教室は全て、クラスごとに同じのため、target_idの変更不要（６で割り当てされていない場合もあるが、OKとする）
-- 科目番号5の教室：112
SELECT * FROM smsevent_instances
WHERE smsevent_instances.instance_id IN(
	SELECT instance_id5 FROM sms_event_instance_ins56_20160128
	)
AND category = 'classroom';

-- 科目番号6の教室：93
SELECT * FROM smsevent_instances
WHERE smsevent_instances.instance_id IN(
	SELECT instance_id6 FROM sms_event_instance_ins56_20160128
	)
AND category = 'classroom';

３．sms_event_instanceのfrom_datetime・to_datetimeで、紐づく全ての科目番号のsmsevent_instancesを更新（講師・教室）

-- 対象スケジュール :1,274 更新：939
SELECT sms_event_instance.id,sms_event_instance.parent_id,sms_event_instance.from_datetime,sms_event_instance.to_datetime,sms_event_instance_sub.ins_order,
		smsevent_instances.id, smsevent_instances.instance_id, smsevent_instances.event_id,smsevent_instances.from_datetime,smsevent_instances.to_datetime
FROM sms_event_instance
	INNER JOIN sms_event_instance_sub ON sms_event_instance_sub.instance_id = sms_event_instance.id
	INNER JOIN smsevent_instances ON smsevent_instances.instance_id = sms_event_instance.id
WHERE sms_event_instance.parent_id IN(
	SELECT smsevent_id FROM sms_event_instance_ins5_20160128
	)
	AND sms_event_instance.deleted =0
ORDER BY sms_event_instance.parent_id,sms_event_instance_sub.ins_order;


UPDATE
    sms_event_instance,
    smsevent_instances
SET 
    smsevent_instances.from_datetime = sms_event_instance.from_datetime,
    smsevent_instances.to_datetime = sms_event_instance.to_datetime
WHERE
    sms_event_instance.id = smsevent_instances.instance_id ;

-- クラス-講師:event_contacts
smseventのIDをキーにしているため、smsevent.idの変更不要
７でスケジュールを削除しているため、
対象のクラスに紐づく講師のクラスの開始、終了日を再計算

-- smsevent_instancesから対象クラスの講師の開始、終了日を取得：174
SELECT
	target_id,event_id,
	MIN((from_datetime)) as from_datetime,
	MAX((to_datetime)) as to_datetime
FROM smsevent_instances 
WHERE 
	event_id IN(
	SELECT smsevent_id FROM sms_event_instance_ins5_20160128
	)
	
	AND (status = 1 OR status = 2)  
	AND category = 'teacher'
GROUP BY target_id,event_id

CREATE TABLE smsevent_instances_maxmin_20160128 
	SELECT
		target_id,event_id,
		MIN((from_datetime)) as from_datetime,
		MAX((to_datetime)) as to_datetime
	FROM smsevent_instances 
	WHERE 
		event_id IN(
		SELECT smsevent_id FROM sms_event_instance_ins5_20160128
		)
		
		AND (status = 1 OR status = 2)  
		AND category = 'teacher'
	GROUP BY target_id,event_id;

 ALTER TABLE smsevent_instances_maxmin_20160128 ADD INDEX ( target_id );
 ALTER TABLE smsevent_instances_maxmin_20160128 ADD INDEX ( event_id );

-- event_contactsと対象クラスのsmsevent_instancesの講師の開始、終了日を取得：243 
SELECT event_contacts.event_id,event_contacts.contact_id,event_contacts.start_date,event_contacts.end_date,
	smsevent_instances_maxmin_20160128.from_datetime,smsevent_instances_maxmin_20160128.to_datetime
FROM event_contacts
LEFT JOIN smsevent_instances_maxmin_20160128 ON smsevent_instances_maxmin_20160128.target_id = event_contacts.contact_id
	AND smsevent_instances_maxmin_20160128.event_id = event_contacts.event_id
WHERE event_contacts.event_id
IN (
	SELECT smsevent_id
	FROM sms_event_instance_ins5_20160128
	)
AND event_contacts.deleted=0
ORDER BY event_contacts.event_id,event_contacts.contact_id

CREATE TABLE event_contacts_new_20160128 
	SELECT event_contacts.event_id,event_contacts.contact_id,event_contacts.start_date,event_contacts.end_date,
		smsevent_instances_maxmin_20160128.from_datetime,smsevent_instances_maxmin_20160128.to_datetime
	FROM event_contacts
	LEFT JOIN smsevent_instances_maxmin_20160128 ON smsevent_instances_maxmin_20160128.target_id = event_contacts.contact_id
		AND smsevent_instances_maxmin_20160128.event_id = event_contacts.event_id
	WHERE event_contacts.event_id
	IN (
		SELECT smsevent_id
		FROM sms_event_instance_ins5_20160128
		)
	AND event_contacts.deleted=0
	ORDER BY event_contacts.event_id,event_contacts.contact_id;

 ALTER TABLE event_contacts_new_20160128 ADD INDEX ( contact_id );
 ALTER TABLE event_contacts_new_20160128 ADD INDEX ( event_id );

UPDATE
    event_contacts,
    event_contacts_new_20160128
SET 
    event_contacts.start_date = event_contacts_new_20160128.from_datetime,
    event_contacts.end_date = event_contacts_new_20160128.to_datetime
WHERE
    event_contacts.event_id = event_contacts_new_20160128.event_id AND
    event_contacts.contact_id = event_contacts_new_20160128.contact_id  ;

-- クラス-教室:event_resource
-- smsevent_instancesから対象クラスの教室の開始、終了日を取得：113 
SELECT
	target_id,event_id,
	MIN((from_datetime)) as from_datetime,
	MAX((to_datetime)) as to_datetime
FROM smsevent_instances 
WHERE 
	event_id IN(
	SELECT smsevent_id FROM sms_event_instance_ins5_20160128
	)
	
	AND (status = 1 OR status = 2)  
	AND category = 'classroom'
GROUP BY target_id,event_id;

CREATE TABLE smsevent_instances_class_maxmin_20160128 
	SELECT
		target_id,event_id,
		MIN((from_datetime)) as from_datetime,
		MAX((to_datetime)) as to_datetime
	FROM smsevent_instances 
	WHERE 
		event_id IN(
		SELECT smsevent_id FROM sms_event_instance_ins5_20160128
		)
		
		AND (status = 1 OR status = 2)  
		AND category = 'classroom'
	GROUP BY target_id,event_id;

 ALTER TABLE smsevent_instances_class_maxmin_20160128 ADD INDEX ( target_id );
 ALTER TABLE smsevent_instances_class_maxmin_20160128 ADD INDEX ( event_id );

-- event_resourceと対象クラスのsmsevent_instancesの教室の開始、終了日を取得：113 
SELECT event_resource.event_id,event_resource.resource_id,event_resource.start_date,event_resource.end_date,
	smsevent_instances_class_maxmin_20160128.from_datetime,smsevent_instances_class_maxmin_20160128.to_datetime
FROM event_resource
LEFT JOIN smsevent_instances_class_maxmin_20160128 ON smsevent_instances_class_maxmin_20160128.target_id = event_resource.resource_id
	AND smsevent_instances_class_maxmin_20160128.event_id = event_resource.event_id
WHERE event_resource.event_id
IN (
	SELECT smsevent_id
	FROM sms_event_instance_ins5_20160128
	)
AND event_resource.deleted=0
ORDER BY event_resource.event_id,event_resource.resource_id;

CREATE TABLE event_resource_new_20160128 
	SELECT event_resource.event_id,event_resource.resource_id,event_resource.start_date,event_resource.end_date,
		smsevent_instances_class_maxmin_20160128.from_datetime,smsevent_instances_class_maxmin_20160128.to_datetime
	FROM event_resource
	LEFT JOIN smsevent_instances_class_maxmin_20160128 ON smsevent_instances_class_maxmin_20160128.target_id = event_resource.resource_id
		AND smsevent_instances_class_maxmin_20160128.event_id = event_resource.event_id
	WHERE event_resource.event_id
	IN (
		SELECT smsevent_id
		FROM sms_event_instance_ins5_20160128
		)
	AND event_resource.deleted=0
	ORDER BY event_resource.event_id,event_resource.resource_id;

 ALTER TABLE event_resource_new_20160128 ADD INDEX ( resource_id );
 ALTER TABLE event_resource_new_20160128 ADD INDEX ( event_id );

UPDATE
    event_resource,
    event_resource_new_20160128
SET 
    event_resource.start_date = event_resource_new_20160128.from_datetime,
    event_resource.end_date = event_resource_new_20160128.to_datetime
WHERE
    event_resource.event_id = event_resource_new_20160128.event_id AND
    event_resource.resource_id = event_resource_new_20160128.resource_id  ;

-- ///////////////////////////////////////////
-- 本番作業時にバックアップ　kyoikuc_20160128 を作成
-- 新カリキュラムに新規追加クラス　28+削除１が存在　→　データ修正作業のため一時smsevent削除

-- 新規追加クラス　28+削除１
SELECT *
FROM smsevent
WHERE curriculum_id = '6388635d-64c4-42bf-fe3d-568f0b5715ba'


-- 新規スケジュール　168	
SELECT *
FROM sms_event_instance
	INNER JOIN smsevent ON sms_event_instance.parent_id = smsevent.id
	AND smsevent.curriculum_id = '6388635d-64c4-42bf-fe3d-568f0b5715ba'


-- 新規スケジュール　教室　168	講師　0
SELECT *
FROM smsevent_instances
	INNER JOIN smsevent ON smsevent_instances.event_id = smsevent.id
	AND smsevent.curriculum_id = '6388635d-64c4-42bf-fe3d-568f0b5715ba'

-- バックアップから削除したデータを抽出　→　NKCに提出 28
SELECT id , deleted , class_code , name
FROM smsevent
WHERE curriculum_id = '6388635d-64c4-42bf-fe3d-568f0b5715ba'
	AND deleted=0;