-- 2014-01-19 idnumber change
SELECT idnumber, CONCAT(last_name, '　',  first_name) as name, CONCAT(last_name_furigana, '　',  first_name_furigana) as name_furigana, phone_home, phone_mobile, primary_address_city, date_entered, id
FROM `contacts`  WHERE `deleted` =0 AND `date_entered` != '2014-01-03 00:00:00' AND `smsgroup` = 'student'

SELECT idnumber, last_name, first_name, last_name_furigana, first_name_furigana, phone_home, phone_mobile, primary_address_city, date_entered, id
FROM `contacts`  WHERE `deleted` =0 AND `smsgroup` = 'student' AND SUBSTRING(idnumber, 1, 4)='2004'

UPDATE contacts SET idnumber='0231476' WHERE id='a2a03b4e-4bf3-1fdd-ee27-52d72eb242dc' ; 
UPDATE contacts SET idnumber='0231475' WHERE id='bb77d2ca-a6da-1db1-4388-52d7306d10c3' ; 
UPDATE contacts SET idnumber='0231474' WHERE id='b651300f-f3f2-e39e-aaf7-52d7363c7591' ; 
UPDATE contacts SET idnumber='0231472' WHERE id='ea052220-6b01-1edd-d6c6-52d73814735e' ; 
UPDATE contacts SET idnumber='0231466' WHERE id='6ae2893a-08a2-4696-947b-52d741fa4d78' ; 
UPDATE contacts SET idnumber='0231455' WHERE id='565c62de-c52c-1666-68dd-52d7462ff362' ; 
UPDATE contacts SET idnumber='0231365' WHERE id='8ba7aacb-e872-36b5-8cc7-52d74891998a' ; 
UPDATE contacts SET idnumber='0231370' WHERE id='72e89e90-cdbb-64e7-14f1-52d74aabea21' ; 
UPDATE contacts SET idnumber='0231372' WHERE id='a888dd12-73d8-a99e-205b-52d74cde7136' ; 
UPDATE contacts SET idnumber='0231464' WHERE id='937a57e4-dee3-9e53-1be5-52d7558a6d78' ; 
UPDATE contacts SET idnumber='0231467' WHERE id='12c8f79a-339c-1ee8-c50f-52d766aee63c' ; 
UPDATE contacts SET idnumber='0231450' WHERE id='c8514942-bb5a-9640-9120-52d769af149f' ; 
UPDATE contacts SET idnumber='0231461' WHERE id='16ee920b-0bc9-01b2-450d-52d775263c64' ; 
UPDATE contacts SET idnumber='0231367' WHERE id='d54a4bec-adcd-9c1f-a6e7-52d785810625' ; 
UPDATE contacts SET idnumber='0231449' WHERE id='87bcf91a-cecc-896e-ac5b-52d78725f59c' ; 
UPDATE contacts SET idnumber='0231362' WHERE id='cf03b0c4-f89f-878b-1060-52d78e387a35' ; 
UPDATE contacts SET idnumber='0231368' WHERE id='e6f48129-1424-ed72-a203-52d79deb38a4' ; 
UPDATE contacts SET idnumber='0231458' WHERE id='8c9b6022-135e-fed3-5a3b-52d7a489283b' ; 
UPDATE contacts SET idnumber='0231364' WHERE id='795807db-64fd-d7a3-ad3f-52d7a7636163' ; 
UPDATE contacts SET idnumber='0231456' WHERE id='92439e0e-7a5e-c564-aa59-52d7b4fc6b7d' ; 

-- 2014-01-21 class change-add

SELECT event_koza.id, event_id, smsevent.name, smsevent.class_code, smsevent.shikenshu, smsevent.curriculum_id, curriculum_code, curriculum_name, smsevent.no_current_student, 
	koza_id, 
	koza_name, koza.common_code, jukoukeitai_code, koza.shikenshu
FROM event_koza 
INNER JOIN smsevent ON smsevent.id=event_koza.event_id AND smsevent.deleted=0
INNER JOIN curriculum ON curriculum.id=curriculum_id 
INNER JOIN koza ON event_koza.koza_id=koza.id AND koza.deleted=0
WHERE event_koza.deleted=0 AND smsevent.id IN ('CL0361310', 'CL0361311', 'CL0361312', 'CL0361313', 'CL0361314', 'CL0361352', 'CL0361353', 'CL0361354', 'CL0361355', 'CL0361356', 'CL0380144', 'CL0380339', 'CL0380340', 'CL0381615', 'CL0381616', 
'CL0381617', 'CL0381618', 'CL0381619', 'CL0381620', 'CL0381621', 'CL0400124', 'CL0420341', 'CL0420342') 
ORDER BY smsevent.class_code, koza.common_code, koza_id



-- old one
SELECT event_contacts.id, event_id, smsevent.name, smsevent.class_code, smsevent.shikenshu, smsevent.curriculum_id, curriculum_code, curriculum_name, contact_id as student_id, idnumber, last_name, first_name
FROM event_contacts
INNER JOIN smsevent ON smsevent.id=event_contacts.event_id AND smsevent.deleted=0
INNER JOIN curriculum ON curriculum.id=curriculum_id 
INNER JOIN contacts ON event_contacts.contact_id=contacts.id AND contacts.deleted=0
WHERE event_contacts.deleted=0 AND smsevent.id IN ('CL0361310', 'CL0361311', 'CL0361312', 'CL0361313', 'CL0361314', 'CL0361352', 'CL0361353', 'CL0361354', 'CL0361355', 'CL0361356', 'CL0380144', 'CL0380339', 'CL0380340', 'CL0381615', 'CL0381616', 
'CL0381617', 'CL0381618', 'CL0381619', 'CL0381620', 'CL0381621', 'CL0400124', 'CL0420341', 'CL0420342') 
ORDER BY smsevent.class_code, idnumber

-- new one
SELECT event_contacts.id, event_contacts.event_id, smsevent.name, smsevent.class_code, smsevent.shikenshu, smsevent.curriculum_id, curriculum_code, curriculum_name, event_contacts.contact_id as student_id, idnumber, last_name, first_name, 
	koza_contacts.koza_id, koza.koza_name, koza.common_code, jukoukeitai_code, koza.shikenshu
FROM event_contacts
INNER JOIN smsevent ON smsevent.id=event_contacts.event_id AND smsevent.deleted=0
INNER JOIN curriculum ON curriculum.id=curriculum_id 
INNER JOIN contacts ON event_contacts.contact_id=contacts.id AND contacts.deleted=0
INNER JOIN koza_contacts ON koza_contacts.contact_id=event_contacts.contact_id AND contacts.id=koza_contacts.contact_id AND koza_contacts.deleted=0
INNER JOIN event_koza ON event_koza.koza_id=koza_contacts.koza_id  AND event_koza.event_id=smsevent.id AND event_koza.deleted=0
INNER JOIN koza ON koza.id=koza_contacts.koza_id AND koza.id=event_koza.koza_id AND koza.deleted=0
WHERE event_contacts.deleted=0 AND smsevent.id IN ('CL0361310', 'CL0361311', 'CL0361312', 'CL0361313', 'CL0361314', 'CL0361352', 'CL0361353', 'CL0361354', 'CL0361355', 'CL0361356', 'CL0380144', 'CL0380339', 'CL0380340', 'CL0381615', 'CL0381616', 
'CL0381617', 'CL0381618', 'CL0381619', 'CL0381620', 'CL0381621', 'CL0400124', 'CL0420341', 'CL0420342') 
ORDER BY smsevent.class_code, idnumber

SELECT event_id, smsevent.name, smsevent.class_code, smsevent.shikenshu, smsevent.curriculum_id, curriculum_code, curriculum_name, hassobi
FROM smsevent 
INNER JOIN curriculum ON curriculum.id=curriculum_id 
INNER JOIN hassobi ON hassobi.event_id=smsevent.id 
WHERE smsevent.deleted=0 AND smsevent.id IN ('CL0361310', 'CL0361311', 'CL0361312', 'CL0361313', 'CL0361314', 'CL0361352', 'CL0361353', 'CL0361354', 'CL0361355', 'CL0361356', 'CL0380144', 'CL0380339', 'CL0380340', 'CL0381615', 'CL0381616', 
'CL0381617', 'CL0381618', 'CL0381619', 'CL0381620', 'CL0381621', 'CL0400124', 'CL0420341', 'CL0420342') 
ORDER BY smsevent.class_code, hassobi

SELECT event_id, smsevent.name, smsevent.class_code, smsevent.shikenshu, smsevent.curriculum_id, curriculum_code, curriculum_name, 
	hassobi, hassocode, hassocode.id, hassocode_buppin.hassocode_id, buppin_id, buppin.buppin_name, hasso_suryo
FROM smsevent 
INNER JOIN curriculum ON curriculum.id=curriculum_id 
INNER JOIN hassobi ON hassobi.event_id=smsevent.id 
INNER JOIN hassocode_hassobi ON hassocode_hassobi.hassobi_id=hassobi.id 
INNER JOIN hassocode ON hassocode_hassobi.hassocode_id=hassocode.id 
INNER JOIN hassocode_buppin ON hassocode_buppin.hassocode_id=hassocode.id 
INNER JOIN buppin ON hassocode_buppin.buppin_id=buppin.id
WHERE smsevent.deleted=0 AND smsevent.id IN ('CL0361310', 'CL0361311', 'CL0361312', 'CL0361313', 'CL0361314', 'CL0361352', 'CL0361353', 'CL0361354', 'CL0361355', 'CL0361356', 'CL0380144', 'CL0380339', 'CL0380340', 'CL0381615', 'CL0381616', 
'CL0381617', 'CL0381618', 'CL0381619', 'CL0381620', 'CL0381621', 'CL0400124', 'CL0420341', 'CL0420342') 
ORDER BY smsevent.class_code, hassocode

SELECT event_id, smsevent.name, smsevent.class_code, smsevent.shikenshu, smsevent.curriculum_id, curriculum_code, curriculum_name, 
	hassobi, hassocode, hassocode.id
FROM smsevent 
INNER JOIN curriculum ON curriculum.id=curriculum_id 
INNER JOIN hassobi ON hassobi.event_id=smsevent.id 
INNER JOIN hassocode_hassobi ON hassocode_hassobi.hassobi_id=hassobi.id 
INNER JOIN hassocode ON hassocode_hassobi.hassocode_id=hassocode.id 
WHERE smsevent.deleted=0 AND smsevent.id IN ('CL0361310', 'CL0361311', 'CL0361312', 'CL0361313', 'CL0361314', 'CL0361352', 'CL0361353', 'CL0361354', 'CL0361355', 'CL0361356', 'CL0380144', 'CL0380339', 'CL0380340', 'CL0381615', 'CL0381616', 
'CL0381617', 'CL0381618', 'CL0381619', 'CL0381620', 'CL0381621', 'CL0400124', 'CL0420341', 'CL0420342') 
ORDER BY smsevent.class_code, hassocode

-- 2014-01-24 class data update
SELECT id FROM curriculum WHERE curriculum_code IN (
'CU16B110060104', 'CU16B110060105', 'CU16B110060108', 'CU16B110060109', 'CU16B110060104', 'CU16B110060105', 'CU16B110060108', 'CU16B110060109', 
'CU13C190011011', 'CU13C190011012', 'CU13C190011013', 'CU13C190011020', 'CU13C190011011', 'CU13C190011012', 'CU13C190011013', 'CU13C190011020', 
'CU13C190011111', 'CU13C190011112', 'CU13C190011113', 'CU13C190011120', 'CU13C190011211', 'CU13C190011212', 'CU13C190011213', 'CU13C190011220', 
'CU13C190011311', 'CU13C190011312', 'CU13C190011313', 'CU13C190011320', 'CU13C190011411', 'CU13C190011412', 'CU13C190011413', 'CU13C190011420', 'CU14C090020001', 'CU14C090020002')

-- id	curriculum_code	curriculum_name	kaikonen	kozabunrui	common_code_a	common_code_b	shikenshu

CREATE TABLE IF NOT EXISTS `curriculum1` (
  `id` char(36) NOT NULL,
  `curriculum_code` varchar(50) DEFAULT NULL,
  `curriculum_name` varchar(100) DEFAULT NULL,
  `kaikonen` varchar(5) DEFAULT NULL,
  `kozabunrui` varchar(36) DEFAULT NULL,
  `common_code_a` varchar(36) DEFAULT NULL,
  `common_code_b` varchar(36) DEFAULT NULL,
  `shikenshu` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `curriculum_code` (`curriculum_code`),
  KEY `common_code_a` (`common_code_a`),
  KEY `common_code_b` (`common_code_b`),
  KEY `curriculum_name` (`curriculum_name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8  ;

SELECT curriculum.id, curriculum.curriculum_code FROM curriculum, curriculum1 WHERE curriculum.id=curriculum1.id ;

UPDATE curriculum, curriculum1 SET 
	curriculum.curriculum_code=curriculum1.curriculum_code, 
	curriculum.curriculum_name=curriculum1.curriculum_name, 
	curriculum.kaikonen=curriculum1.kaikonen, 
	curriculum.kozabunrui=curriculum1.kozabunrui, 
	curriculum.common_code_a=curriculum1.common_code_a, 
	curriculum.common_code_b=curriculum1.common_code_b, 
	curriculum.shikenshu=curriculum1.shikenshu
WHERE curriculum.id=curriculum1.id ; 


-- id	class_code	name	kamoku	net_haishin_umu	net_haishin_kigen	web_de_doriru_taisho	kaikonen	kozabunrui	common_code_a	common_code_b

CREATE TABLE IF NOT EXISTS `smsevent1` (
  `id` char(36) NOT NULL,
  `class_code` varchar(100) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `kamoku` varchar(100) DEFAULT NULL,
  `net_haishin_umu` varchar(5) DEFAULT NULL,
  `net_haishin_kigen` date DEFAULT NULL,
  `web_de_doriru_taisho` varchar(5) DEFAULT NULL,
  `kaikonen` varchar(5) DEFAULT NULL,
  `kozabunrui` varchar(36) DEFAULT NULL,
  `common_code_a` varchar(36) DEFAULT NULL,
  `common_code_b` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `class_code` (`class_code`),
  KEY `kamoku` (`kamoku`),
  KEY `common_code_a` (`common_code_a`),
  KEY `common_code_b` (`common_code_b`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8  ;


-- id	class_code	name	class_teacher	teiin	kamoku	net_haishin_umu	net_haishin_kigen	web_de_doriru_taisho	kaikonen	kozabunrui	common_code_a	common_code_b

CREATE TABLE IF NOT EXISTS `smsevent2` (
  `id` char(36) NOT NULL,
  `class_code` varchar(100) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `class_teacher` varchar(36) DEFAULT NULL,
  `teiin` int(11) DEFAULT NULL,
  `kamoku` varchar(100) DEFAULT NULL,
  `net_haishin_umu` varchar(5) DEFAULT NULL,
  `net_haishin_kigen` date DEFAULT NULL,
  `web_de_doriru_taisho` varchar(5) DEFAULT NULL,
  `kaikonen` varchar(5) DEFAULT NULL,
  `kozabunrui` varchar(36) DEFAULT NULL,
  `common_code_a` varchar(36) DEFAULT NULL,
  `common_code_b` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `class_code` (`class_code`),
  KEY `kamoku` (`kamoku`),
  KEY `common_code_a` (`common_code_a`),
  KEY `common_code_b` (`common_code_b`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8  ;

-- id	class_code	name	class_teacher	net_haishin_umu	net_haishin_kigen	kozabunrui	common_code_a	common_code_b

CREATE TABLE IF NOT EXISTS `smsevent3` (
  `id` char(36) NOT NULL,
  `class_code` varchar(100) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `class_teacher` varchar(36) DEFAULT NULL,
  `net_haishin_umu` varchar(5) DEFAULT NULL,
  `net_haishin_kigen` date DEFAULT NULL,
  `kozabunrui` varchar(36) DEFAULT NULL,
  `common_code_a` varchar(36) DEFAULT NULL,
  `common_code_b` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `class_code` (`class_code`),
  KEY `common_code_a` (`common_code_a`),
  KEY `common_code_b` (`common_code_b`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8  ;

-- id	class_code	name	class_teacher	teiin	kamoku	net_haishin_umu	net_haishin_kigen	web_de_doriru_taisho	kaikonen	kozabunrui	common_code_a	common_code_b

CREATE TABLE IF NOT EXISTS `smsevent4` (
  `id` char(36) NOT NULL,
  `class_code` varchar(100) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `class_teacher` varchar(36) DEFAULT NULL,
  `teiin` int(11) DEFAULT NULL,
  `kamoku` varchar(100) DEFAULT NULL,
  `net_haishin_umu` varchar(5) DEFAULT NULL,
  `net_haishin_kigen` date DEFAULT NULL,
  `web_de_doriru_taisho` varchar(5) DEFAULT NULL,
  `kaikonen` varchar(5) DEFAULT NULL,
  `kozabunrui` varchar(36) DEFAULT NULL,
  `common_code_a` varchar(36) DEFAULT NULL,
  `common_code_b` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `class_code` (`class_code`),
  KEY `kamoku` (`kamoku`),
  KEY `common_code_a` (`common_code_a`),
  KEY `common_code_b` (`common_code_b`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8  ;

SELECT smsevent.id, smsevent.class_code FROM smsevent, smsevent1 WHERE smsevent.id=smsevent1.id 
SELECT smsevent.id, smsevent.class_code FROM smsevent, smsevent2 WHERE smsevent.id=smsevent2.id 
SELECT smsevent.id, smsevent.class_code FROM smsevent, smsevent3 WHERE smsevent.id=smsevent3.id 
SELECT smsevent.id, smsevent.class_code FROM smsevent, smsevent4 WHERE smsevent.id=smsevent4.id 

UPDATE smsevent, smsevent1 SET 
	smsevent.class_code=smsevent1.class_code, 
	smsevent.name=smsevent1.name, 
	smsevent.kamoku=smsevent1.kamoku, 
	smsevent.net_haishin_umu=smsevent1.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent1.net_haishin_kigen, 
	smsevent.web_de_doriru_taisho=smsevent1.web_de_doriru_taisho, 
	smsevent.kaikonen=smsevent1.kaikonen, 
	smsevent.kozabunrui=smsevent1.kozabunrui, 
	smsevent.common_code_a=smsevent1.common_code_a, 
	smsevent.common_code_b=smsevent1.common_code_b										
WHERE smsevent.id=smsevent1.id ; 

UPDATE smsevent, smsevent2 SET 
	smsevent.class_code=smsevent2.class_code, 
	smsevent.name=smsevent2.name, 
	smsevent.class_teacher=smsevent2.class_teacher, 
	smsevent.teiin=smsevent2.teiin, 
	smsevent.kamoku=smsevent2.kamoku, 
	smsevent.net_haishin_umu=smsevent2.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent2.net_haishin_kigen, 
	smsevent.web_de_doriru_taisho=smsevent2.web_de_doriru_taisho, 
	smsevent.kaikonen=smsevent2.kaikonen, 
	smsevent.kozabunrui=smsevent2.kozabunrui, 
	smsevent.common_code_a=smsevent2.common_code_a, 
	smsevent.common_code_b=smsevent2.common_code_b										
WHERE smsevent.id=smsevent2.id ; 

UPDATE smsevent, smsevent3 SET 
	smsevent.class_code=smsevent3.class_code, 
	smsevent.name=smsevent3.name, 
	smsevent.class_teacher=smsevent3.class_teacher, 
	smsevent.net_haishin_umu=smsevent3.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent3.net_haishin_kigen, 
	smsevent.kozabunrui=smsevent3.kozabunrui, 
	smsevent.common_code_a=smsevent3.common_code_a, 
	smsevent.common_code_b=smsevent3.common_code_b										
WHERE smsevent.id=smsevent3.id ; 

UPDATE smsevent, smsevent4 SET 
	smsevent.class_code=smsevent4.class_code, 
	smsevent.name=smsevent4.name, 
	smsevent.class_teacher=smsevent4.class_teacher, 
	smsevent.teiin=smsevent4.teiin, 
	smsevent.kamoku=smsevent4.kamoku, 
	smsevent.net_haishin_umu=smsevent4.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent4.net_haishin_kigen, 
	smsevent.web_de_doriru_taisho=smsevent4.web_de_doriru_taisho, 
	smsevent.kaikonen=smsevent4.kaikonen, 
	smsevent.kozabunrui=smsevent4.kozabunrui, 
	smsevent.common_code_a=smsevent4.common_code_a, 
	smsevent.common_code_b=smsevent4.common_code_b										
WHERE smsevent.id=smsevent4.id ; 

-- 2014-01-26 class data update

-- id	class_code	name	class_teacher	kamoku	net_haishin_umu	net_haishin_kigen	web_de_doriru_taisho

CREATE TABLE IF NOT EXISTS `smsevent5` (
  `id` char(36) NOT NULL,
  `class_code` varchar(100) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `class_teacher` varchar(36) DEFAULT NULL,
  `kamoku` varchar(100) DEFAULT NULL,
  `net_haishin_umu` varchar(5) DEFAULT NULL,
  `net_haishin_kigen` date DEFAULT NULL,
  `web_de_doriru_taisho` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `class_code` (`class_code`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8  ;


-- id	class_code	name	teiin	kamoku	net_haishin_umu	net_haishin_kigen	web_de_doriru_taisho	kaikonen	kozabunrui	common_code_a	common_code_b

CREATE TABLE IF NOT EXISTS `smsevent6` (
  `id` char(36) NOT NULL,
  `class_code` varchar(100) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `teiin` int(11) DEFAULT NULL,
  `kamoku` varchar(100) DEFAULT NULL,
  `net_haishin_umu` varchar(5) DEFAULT NULL,
  `net_haishin_kigen` date DEFAULT NULL,
  `web_de_doriru_taisho` varchar(5) DEFAULT NULL,
  `kaikonen` varchar(5) DEFAULT NULL,
  `kozabunrui` varchar(36) DEFAULT NULL,
  `common_code_a` varchar(36) DEFAULT NULL,
  `common_code_b` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `class_code` (`class_code`),
  KEY `kamoku` (`kamoku`),
  KEY `common_code_a` (`common_code_a`),
  KEY `common_code_b` (`common_code_b`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8  ;

SELECT smsevent.id, smsevent.class_code FROM smsevent, smsevent5 WHERE smsevent.id=smsevent5.id 
SELECT smsevent.id, smsevent.class_code FROM smsevent, smsevent6 WHERE smsevent.id=smsevent6.id 

UPDATE smsevent, smsevent5 SET 
	smsevent.class_code=smsevent5.class_code, 
	smsevent.name=smsevent5.name, 
	smsevent.class_teacher=smsevent5.class_teacher, 
	smsevent.kamoku=smsevent5.kamoku, 
	smsevent.net_haishin_umu=smsevent5.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent5.net_haishin_kigen, 
	smsevent.web_de_doriru_taisho=smsevent5.web_de_doriru_taisho										
WHERE smsevent.id=smsevent5.id ; 

UPDATE smsevent, smsevent6 SET 
	smsevent.class_code=smsevent6.class_code, 
	smsevent.name=smsevent6.name, 
	smsevent.teiin=smsevent6.teiin, 
	smsevent.kamoku=smsevent6.kamoku, 
	smsevent.net_haishin_umu=smsevent6.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent6.net_haishin_kigen, 
	smsevent.web_de_doriru_taisho=smsevent6.web_de_doriru_taisho, 
	smsevent.kaikonen=smsevent6.kaikonen, 
	smsevent.kozabunrui=smsevent6.kozabunrui, 
	smsevent.common_code_a=smsevent6.common_code_a, 
	smsevent.common_code_b=smsevent6.common_code_b										
WHERE smsevent.id=smsevent6.id ; 

-- 2014-01-29
-- 1xN

SELECT event_koza.id, event_id, smsevent.name, smsevent.class_code, smsevent.shikenshu, smsevent.curriculum_id, curriculum_code, curriculum_name, smsevent.no_current_student, koza_id, koza_name, koza.common_code, jukoukeitai_code, koza.shikenshu
FROM event_koza INNER JOIN smsevent ON smsevent.id=event_koza.event_id AND smsevent.deleted=0
INNER JOIN curriculum ON curriculum.id=curriculum_id INNER JOIN koza ON event_koza.koza_id=koza.id AND koza.deleted=0
WHERE event_koza.deleted=0 AND smsevent.id IN 
('CL2191308','CL2191309','CL2191310','CL2191311','CL2191312','CL2191313','CL2191399','CL2191449','CL2191450','CL2191451','CL2191452','CL2191513','CL2191514','CL2191515','CL2191602','CL2191602',
'CL2191603','CL2191603','CL2191604','CL2191604','CL2191605','CL2191605','CL2191606','CL2191606','CL2191607','CL2191607','CL2191608','CL2191609','CL2191610','CL2191611','CL2191612','CL2191618',
'CL2191619','CL2191620','CL2191621','CL2191622','CL2191653','CL2191654','CL2191655','CL2191656','CL2191657','CL2191658','CL2191704','CL2191705','CL2191706','CL2191707','CL2191708','CL2191709',
'CL2191806','CL2191807','CL2191808','CL2191809','CL2191810','CL2191811','CL2191857','CL2191858','CL2191859','CL2191860','CL2191861','CL2191862','CL2191908','CL2191909','CL2191910','CL2191911',
'CL2191912','CL2191913','CL2191959','CL2191960','CL2191961','CL2191962','CL2192009','CL2192010','CL2192011','CL2192012','CL2192065','CL2192066','CL2192067','CL2192068','CL2192121','CL2192122',
'CL2192123','CL2192124','CL2192177','CL2192178','CL2192179','CL2192180','CL2192233','CL2192234','CL2192235','CL2192236','CL2192289','CL2192290','CL2192291','CL2192292','CL2192444','CL2192445',
'CL2192446','CL2192448','CL2192449','CL2192450','CL2192452','CL2192453','CL2192454','CL2192456','CL2192457','CL2192458','CL2192460','CL2192461','CL2192462','CL2192464','CL2192465','CL2192466',
'CL2192468','CL2192469','CL2192470','CL3111458','CL3111459','CL3111460','CL3111461','CL3111462','CL3111463','CL3120082','CL3120082','CL3120082','CL3120083','CL3120083','CL3120083','CL3120084',
'CL3120084','CL3120084','CL3120085','CL3120085','CL3120085','CL3120086','CL3120086','CL3120086','CL3120087','CL3120087','CL3120087','CL3120090','CL3120152','CL3120152','CL3120153','CL3120153',
'CL3120154','CL3120154','CL3120154','CL3120155','CL3120155','CL3120156','CL3120156','CL3120157','CL3120157','CL3120191','CL3120192','CL3120193','CL3120194','CL3120195','CL3120196','CL3120199',
'CL3120210','CL3120211','CL3120212','CL3120213','CL3120214','CL3120215','CL3120229','CL3120229','CL3120230','CL3120230','CL3120231','CL3120231','CL3120232','CL3120232','CL3120233','CL3120233',
'CL3120234','CL3120234','CL3120258','CL3120259','CL3120260','CL3120261','CL3120262','CL3120263','CL3120306','CL3120308','CL3120327','CL3120329','CL3120331','CL3120445','CL3120473','CL3120474',
'CL3120507','CL3120508','CL3120510','CL3120511','CL0361310','CL0361311','CL0361312','CL0361313','CL0361314','CL0361352','CL0361353','CL0361354','CL0361355','CL0361356','CL0380144','CL0380144',
'CL0380144','CL0380339','CL0381615','CL0381616','CL0381617','CL0381618','CL0381619','CL0381620','CL0381621','CL0400123','CL0400123','CL0400123','CL0400124','CL0420341','CL0420341','CL0420341',
'CL0420342','CL1240646','CL1250101','CL1250103','CL1250118','CL1250120','CL1250135','CL1250137','CL1250152','CL1250154')
ORDER BY smsevent.class_code, koza.common_code, koza_id

SELECT event_contacts.id, event_contacts.event_id, smsevent.name, smsevent.class_code, smsevent.shikenshu, smsevent.curriculum_id, curriculum_code, curriculum_name, event_contacts.contact_id as student_id, idnumber, last_name, first_name, 
	koza_contacts.koza_id, koza.koza_name, koza.common_code, jukoukeitai_code, koza.shikenshu
FROM event_contacts INNER JOIN smsevent ON smsevent.id=event_contacts.event_id AND smsevent.deleted=0
INNER JOIN curriculum ON curriculum.id=curriculum_id INNER JOIN contacts ON event_contacts.contact_id=contacts.id AND contacts.deleted=0
INNER JOIN koza_contacts ON koza_contacts.contact_id=event_contacts.contact_id AND contacts.id=koza_contacts.contact_id AND koza_contacts.deleted=0
INNER JOIN event_koza ON event_koza.koza_id=koza_contacts.koza_id  AND event_koza.event_id=smsevent.id AND event_koza.deleted=0 INNER JOIN koza ON koza.id=koza_contacts.koza_id AND koza.id=event_koza.koza_id AND koza.deleted=0
WHERE event_contacts.deleted=0 AND smsevent.id IN 
('CL2191308','CL2191309','CL2191310','CL2191311','CL2191312','CL2191313','CL2191399','CL2191449','CL2191450','CL2191451','CL2191452','CL2191513','CL2191514','CL2191515','CL2191602','CL2191602',
'CL2191603','CL2191603','CL2191604','CL2191604','CL2191605','CL2191605','CL2191606','CL2191606','CL2191607','CL2191607','CL2191608','CL2191609','CL2191610','CL2191611','CL2191612','CL2191618',
'CL2191619','CL2191620','CL2191621','CL2191622','CL2191653','CL2191654','CL2191655','CL2191656','CL2191657','CL2191658','CL2191704','CL2191705','CL2191706','CL2191707','CL2191708','CL2191709',
'CL2191806','CL2191807','CL2191808','CL2191809','CL2191810','CL2191811','CL2191857','CL2191858','CL2191859','CL2191860','CL2191861','CL2191862','CL2191908','CL2191909','CL2191910','CL2191911',
'CL2191912','CL2191913','CL2191959','CL2191960','CL2191961','CL2191962','CL2192009','CL2192010','CL2192011','CL2192012','CL2192065','CL2192066','CL2192067','CL2192068','CL2192121','CL2192122',
'CL2192123','CL2192124','CL2192177','CL2192178','CL2192179','CL2192180','CL2192233','CL2192234','CL2192235','CL2192236','CL2192289','CL2192290','CL2192291','CL2192292','CL2192444','CL2192445',
'CL2192446','CL2192448','CL2192449','CL2192450','CL2192452','CL2192453','CL2192454','CL2192456','CL2192457','CL2192458','CL2192460','CL2192461','CL2192462','CL2192464','CL2192465','CL2192466',
'CL2192468','CL2192469','CL2192470','CL3111458','CL3111459','CL3111460','CL3111461','CL3111462','CL3111463','CL3120082','CL3120082','CL3120082','CL3120083','CL3120083','CL3120083','CL3120084',
'CL3120084','CL3120084','CL3120085','CL3120085','CL3120085','CL3120086','CL3120086','CL3120086','CL3120087','CL3120087','CL3120087','CL3120090','CL3120152','CL3120152','CL3120153','CL3120153',
'CL3120154','CL3120154','CL3120154','CL3120155','CL3120155','CL3120156','CL3120156','CL3120157','CL3120157','CL3120191','CL3120192','CL3120193','CL3120194','CL3120195','CL3120196','CL3120199',
'CL3120210','CL3120211','CL3120212','CL3120213','CL3120214','CL3120215','CL3120229','CL3120229','CL3120230','CL3120230','CL3120231','CL3120231','CL3120232','CL3120232','CL3120233','CL3120233',
'CL3120234','CL3120234','CL3120258','CL3120259','CL3120260','CL3120261','CL3120262','CL3120263','CL3120306','CL3120308','CL3120327','CL3120329','CL3120331','CL3120445','CL3120473','CL3120474',
'CL3120507','CL3120508','CL3120510','CL3120511','CL0361310','CL0361311','CL0361312','CL0361313','CL0361314','CL0361352','CL0361353','CL0361354','CL0361355','CL0361356','CL0380144','CL0380144',
'CL0380144','CL0380339','CL0381615','CL0381616','CL0381617','CL0381618','CL0381619','CL0381620','CL0381621','CL0400123','CL0400123','CL0400123','CL0400124','CL0420341','CL0420341','CL0420341',
'CL0420342','CL1240646','CL1250101','CL1250103','CL1250118','CL1250120','CL1250135','CL1250137','CL1250152','CL1250154')
ORDER BY smsevent.class_code, idnumber

SELECT event_id, smsevent.name, smsevent.class_code, smsevent.shikenshu, smsevent.curriculum_id, curriculum_code, curriculum_name, hassobi
FROM smsevent INNER JOIN curriculum ON curriculum.id=curriculum_id INNER JOIN hassobi ON hassobi.event_id=smsevent.id 
WHERE smsevent.deleted=0 AND smsevent.id IN 
('CL2191308','CL2191309','CL2191310','CL2191311','CL2191312','CL2191313','CL2191399','CL2191449','CL2191450','CL2191451','CL2191452','CL2191513','CL2191514','CL2191515','CL2191602','CL2191602',
'CL2191603','CL2191603','CL2191604','CL2191604','CL2191605','CL2191605','CL2191606','CL2191606','CL2191607','CL2191607','CL2191608','CL2191609','CL2191610','CL2191611','CL2191612','CL2191618',
'CL2191619','CL2191620','CL2191621','CL2191622','CL2191653','CL2191654','CL2191655','CL2191656','CL2191657','CL2191658','CL2191704','CL2191705','CL2191706','CL2191707','CL2191708','CL2191709',
'CL2191806','CL2191807','CL2191808','CL2191809','CL2191810','CL2191811','CL2191857','CL2191858','CL2191859','CL2191860','CL2191861','CL2191862','CL2191908','CL2191909','CL2191910','CL2191911',
'CL2191912','CL2191913','CL2191959','CL2191960','CL2191961','CL2191962','CL2192009','CL2192010','CL2192011','CL2192012','CL2192065','CL2192066','CL2192067','CL2192068','CL2192121','CL2192122',
'CL2192123','CL2192124','CL2192177','CL2192178','CL2192179','CL2192180','CL2192233','CL2192234','CL2192235','CL2192236','CL2192289','CL2192290','CL2192291','CL2192292','CL2192444','CL2192445',
'CL2192446','CL2192448','CL2192449','CL2192450','CL2192452','CL2192453','CL2192454','CL2192456','CL2192457','CL2192458','CL2192460','CL2192461','CL2192462','CL2192464','CL2192465','CL2192466',
'CL2192468','CL2192469','CL2192470','CL3111458','CL3111459','CL3111460','CL3111461','CL3111462','CL3111463','CL3120082','CL3120082','CL3120082','CL3120083','CL3120083','CL3120083','CL3120084',
'CL3120084','CL3120084','CL3120085','CL3120085','CL3120085','CL3120086','CL3120086','CL3120086','CL3120087','CL3120087','CL3120087','CL3120090','CL3120152','CL3120152','CL3120153','CL3120153',
'CL3120154','CL3120154','CL3120154','CL3120155','CL3120155','CL3120156','CL3120156','CL3120157','CL3120157','CL3120191','CL3120192','CL3120193','CL3120194','CL3120195','CL3120196','CL3120199',
'CL3120210','CL3120211','CL3120212','CL3120213','CL3120214','CL3120215','CL3120229','CL3120229','CL3120230','CL3120230','CL3120231','CL3120231','CL3120232','CL3120232','CL3120233','CL3120233',
'CL3120234','CL3120234','CL3120258','CL3120259','CL3120260','CL3120261','CL3120262','CL3120263','CL3120306','CL3120308','CL3120327','CL3120329','CL3120331','CL3120445','CL3120473','CL3120474',
'CL3120507','CL3120508','CL3120510','CL3120511','CL0361310','CL0361311','CL0361312','CL0361313','CL0361314','CL0361352','CL0361353','CL0361354','CL0361355','CL0361356','CL0380144','CL0380144',
'CL0380144','CL0380339','CL0381615','CL0381616','CL0381617','CL0381618','CL0381619','CL0381620','CL0381621','CL0400123','CL0400123','CL0400123','CL0400124','CL0420341','CL0420341','CL0420341',
'CL0420342','CL1240646','CL1250101','CL1250103','CL1250118','CL1250120','CL1250135','CL1250137','CL1250152','CL1250154')
ORDER BY smsevent.class_code, hassobi

SELECT event_id, smsevent.name, smsevent.class_code, smsevent.shikenshu, smsevent.curriculum_id, curriculum_code, curriculum_name, hassobi, hassocode, hassocode.id
FROM smsevent INNER JOIN curriculum ON curriculum.id=curriculum_id INNER JOIN hassobi ON hassobi.event_id=smsevent.id 
INNER JOIN hassocode_hassobi ON hassocode_hassobi.hassobi_id=hassobi.id INNER JOIN hassocode ON hassocode_hassobi.hassocode_id=hassocode.id 
WHERE smsevent.deleted=0 AND smsevent.id IN 
('CL2191308','CL2191309','CL2191310','CL2191311','CL2191312','CL2191313','CL2191399','CL2191449','CL2191450','CL2191451','CL2191452','CL2191513','CL2191514','CL2191515','CL2191602','CL2191602',
'CL2191603','CL2191603','CL2191604','CL2191604','CL2191605','CL2191605','CL2191606','CL2191606','CL2191607','CL2191607','CL2191608','CL2191609','CL2191610','CL2191611','CL2191612','CL2191618',
'CL2191619','CL2191620','CL2191621','CL2191622','CL2191653','CL2191654','CL2191655','CL2191656','CL2191657','CL2191658','CL2191704','CL2191705','CL2191706','CL2191707','CL2191708','CL2191709',
'CL2191806','CL2191807','CL2191808','CL2191809','CL2191810','CL2191811','CL2191857','CL2191858','CL2191859','CL2191860','CL2191861','CL2191862','CL2191908','CL2191909','CL2191910','CL2191911',
'CL2191912','CL2191913','CL2191959','CL2191960','CL2191961','CL2191962','CL2192009','CL2192010','CL2192011','CL2192012','CL2192065','CL2192066','CL2192067','CL2192068','CL2192121','CL2192122',
'CL2192123','CL2192124','CL2192177','CL2192178','CL2192179','CL2192180','CL2192233','CL2192234','CL2192235','CL2192236','CL2192289','CL2192290','CL2192291','CL2192292','CL2192444','CL2192445',
'CL2192446','CL2192448','CL2192449','CL2192450','CL2192452','CL2192453','CL2192454','CL2192456','CL2192457','CL2192458','CL2192460','CL2192461','CL2192462','CL2192464','CL2192465','CL2192466',
'CL2192468','CL2192469','CL2192470','CL3111458','CL3111459','CL3111460','CL3111461','CL3111462','CL3111463','CL3120082','CL3120082','CL3120082','CL3120083','CL3120083','CL3120083','CL3120084',
'CL3120084','CL3120084','CL3120085','CL3120085','CL3120085','CL3120086','CL3120086','CL3120086','CL3120087','CL3120087','CL3120087','CL3120090','CL3120152','CL3120152','CL3120153','CL3120153',
'CL3120154','CL3120154','CL3120154','CL3120155','CL3120155','CL3120156','CL3120156','CL3120157','CL3120157','CL3120191','CL3120192','CL3120193','CL3120194','CL3120195','CL3120196','CL3120199',
'CL3120210','CL3120211','CL3120212','CL3120213','CL3120214','CL3120215','CL3120229','CL3120229','CL3120230','CL3120230','CL3120231','CL3120231','CL3120232','CL3120232','CL3120233','CL3120233',
'CL3120234','CL3120234','CL3120258','CL3120259','CL3120260','CL3120261','CL3120262','CL3120263','CL3120306','CL3120308','CL3120327','CL3120329','CL3120331','CL3120445','CL3120473','CL3120474',
'CL3120507','CL3120508','CL3120510','CL3120511','CL0361310','CL0361311','CL0361312','CL0361313','CL0361314','CL0361352','CL0361353','CL0361354','CL0361355','CL0361356','CL0380144','CL0380144',
'CL0380144','CL0380339','CL0381615','CL0381616','CL0381617','CL0381618','CL0381619','CL0381620','CL0381621','CL0400123','CL0400123','CL0400123','CL0400124','CL0420341','CL0420341','CL0420341',
'CL0420342','CL1240646','CL1250101','CL1250103','CL1250118','CL1250120','CL1250135','CL1250137','CL1250152','CL1250154')
ORDER BY smsevent.class_code, hassocode

SELECT event_id, smsevent.name, smsevent.class_code, smsevent.shikenshu, smsevent.curriculum_id, curriculum_code, curriculum_name, hassobi, hassocode, hassocode.id, hassocode_buppin.hassocode_id, buppin_id, buppin.buppin_name, hasso_suryo
FROM smsevent INNER JOIN curriculum ON curriculum.id=curriculum_id INNER JOIN hassobi ON hassobi.event_id=smsevent.id INNER JOIN hassocode_hassobi ON hassocode_hassobi.hassobi_id=hassobi.id 
INNER JOIN hassocode ON hassocode_hassobi.hassocode_id=hassocode.id INNER JOIN hassocode_buppin ON hassocode_buppin.hassocode_id=hassocode.id INNER JOIN buppin ON hassocode_buppin.buppin_id=buppin.id
WHERE smsevent.deleted=0 AND smsevent.id IN 
('CL2191308','CL2191309','CL2191310','CL2191311','CL2191312','CL2191313','CL2191399','CL2191449','CL2191450','CL2191451','CL2191452','CL2191513','CL2191514','CL2191515','CL2191602','CL2191602',
'CL2191603','CL2191603','CL2191604','CL2191604','CL2191605','CL2191605','CL2191606','CL2191606','CL2191607','CL2191607','CL2191608','CL2191609','CL2191610','CL2191611','CL2191612','CL2191618',
'CL2191619','CL2191620','CL2191621','CL2191622','CL2191653','CL2191654','CL2191655','CL2191656','CL2191657','CL2191658','CL2191704','CL2191705','CL2191706','CL2191707','CL2191708','CL2191709',
'CL2191806','CL2191807','CL2191808','CL2191809','CL2191810','CL2191811','CL2191857','CL2191858','CL2191859','CL2191860','CL2191861','CL2191862','CL2191908','CL2191909','CL2191910','CL2191911',
'CL2191912','CL2191913','CL2191959','CL2191960','CL2191961','CL2191962','CL2192009','CL2192010','CL2192011','CL2192012','CL2192065','CL2192066','CL2192067','CL2192068','CL2192121','CL2192122',
'CL2192123','CL2192124','CL2192177','CL2192178','CL2192179','CL2192180','CL2192233','CL2192234','CL2192235','CL2192236','CL2192289','CL2192290','CL2192291','CL2192292','CL2192444','CL2192445',
'CL2192446','CL2192448','CL2192449','CL2192450','CL2192452','CL2192453','CL2192454','CL2192456','CL2192457','CL2192458','CL2192460','CL2192461','CL2192462','CL2192464','CL2192465','CL2192466',
'CL2192468','CL2192469','CL2192470','CL3111458','CL3111459','CL3111460','CL3111461','CL3111462','CL3111463','CL3120082','CL3120082','CL3120082','CL3120083','CL3120083','CL3120083','CL3120084',
'CL3120084','CL3120084','CL3120085','CL3120085','CL3120085','CL3120086','CL3120086','CL3120086','CL3120087','CL3120087','CL3120087','CL3120090','CL3120152','CL3120152','CL3120153','CL3120153',
'CL3120154','CL3120154','CL3120154','CL3120155','CL3120155','CL3120156','CL3120156','CL3120157','CL3120157','CL3120191','CL3120192','CL3120193','CL3120194','CL3120195','CL3120196','CL3120199',
'CL3120210','CL3120211','CL3120212','CL3120213','CL3120214','CL3120215','CL3120229','CL3120229','CL3120230','CL3120230','CL3120231','CL3120231','CL3120232','CL3120232','CL3120233','CL3120233',
'CL3120234','CL3120234','CL3120258','CL3120259','CL3120260','CL3120261','CL3120262','CL3120263','CL3120306','CL3120308','CL3120327','CL3120329','CL3120331','CL3120445','CL3120473','CL3120474',
'CL3120507','CL3120508','CL3120510','CL3120511','CL0361310','CL0361311','CL0361312','CL0361313','CL0361314','CL0361352','CL0361353','CL0361354','CL0361355','CL0361356','CL0380144','CL0380144',
'CL0380144','CL0380339','CL0381615','CL0381616','CL0381617','CL0381618','CL0381619','CL0381620','CL0381621','CL0400123','CL0400123','CL0400123','CL0400124','CL0420341','CL0420341','CL0420341',
'CL0420342','CL1240646','CL1250101','CL1250103','CL1250118','CL1250120','CL1250135','CL1250137','CL1250152','CL1250154')
ORDER BY smsevent.class_code, hassocode

-- nX1

SELECT event_koza.id, event_id, smsevent.name, smsevent.class_code, smsevent.shikenshu, smsevent.curriculum_id, curriculum_code, curriculum_name, smsevent.no_current_student, koza_id, koza_name, koza.common_code, jukoukeitai_code, koza.shikenshu
FROM event_koza INNER JOIN smsevent ON smsevent.id=event_koza.event_id AND smsevent.deleted=0
INNER JOIN curriculum ON curriculum.id=curriculum_id INNER JOIN koza ON event_koza.koza_id=koza.id AND koza.deleted=0
WHERE event_koza.deleted=0 AND smsevent.id IN 
('CL2192182','CL2192460','CL2192183','CL2192461','CL2192184','CL2192462','CL2201798','CL2201799','CL2201698','CL2201699','CL2201635','CL2201645','CL2201633','CL2201643','CL2201634','CL2201644',
'CL2200561','CL2201491','CL2210364','CL2211021','CL2210292','CL2211123','CL2210268','CL2210987','CL2210025','CL2210951','CL2210316','CL2211157','CL2210244','CL2211055','CL2210340','CL2211191',
'CL2210001','CL2210724','CL2210388','CL2211089','CL2210365','CL2211022','CL2210293','CL2211124','CL2210269','CL2210988','CL2210026','CL2210952','CL2210317','CL2211158','CL2210245','CL2211056',
'CL2210341','CL2211192','CL2210002','CL2210725','CL2210389','CL2211090','CL2210380','CL2211037','CL2210308','CL2211139','CL2210284','CL2211003','CL2210041','CL2210967','CL2210332','CL2211173',
'CL2210260','CL2211071','CL2210356','CL2211207','CL2210017','CL2210740','CL2210404','CL2211105','CL2210381','CL2211038','CL2210309','CL2211140','CL2210285','CL2211004','CL2210042','CL2210968',
'CL2210333','CL2211174','CL2210261','CL2211072','CL2210357','CL2211208','CL2210018','CL2210741','CL2210405','CL2211106','CL2210382','CL2211039','CL2210310','CL2211141','CL2210286','CL2211005',
'CL2210043','CL2210969','CL2210334','CL2211175','CL2210262','CL2211073','CL2210358','CL2211209','CL2210019','CL2210742','CL2210406','CL2211107','CL2210383','CL2211040','CL2210311','CL2211142',
'CL2210287','CL2211006','CL2210044','CL2210970','CL2210335','CL2211176','CL2210263','CL2211074','CL2210359','CL2211210','CL2210020','CL2210743','CL2210407','CL2211108','CL2210386','CL2211042',
'CL2210314','CL2211144','CL2210290','CL2211008','CL2210047','CL2210973','CL2210338','CL2211178','CL2210266','CL2211076','CL2210362','CL2211212','CL2210023','CL2210746','CL2210410','CL2211110',
'CL2210048','CL2210974','CL2210024','CL2210747','CL2210046','CL2210972','CL2210022','CL2210745','CL2210384','CL2211041','CL2210312','CL2211143','CL2210288','CL2211007','CL2210045','CL2210971',
'CL2210336','CL2211177','CL2210264','CL2211075','CL2210360','CL2211211','CL2210021','CL2210744','CL2210408','CL2211109','CL2210366','CL2211023','CL2210294','CL2211125','CL2210270','CL2210989',
'CL2210027','CL2210953','CL2210318','CL2211159','CL2210246','CL2211057','CL2210342','CL2211193','CL2210003','CL2210726','CL2210390','CL2211091','CL2210367','CL2211024','CL2210295','CL2211126',
'CL2210271','CL2210990','CL2210028','CL2210954','CL2210319','CL2211160','CL2210247','CL2211058','CL2210343','CL2211194','CL2210004','CL2210727','CL2210391','CL2211092','CL2210368','CL2211025',
'CL2210296','CL2211127','CL2210272','CL2210991','CL2210029','CL2210955','CL2210320','CL2211161','CL2210248','CL2211059','CL2210344','CL2211195','CL2210005','CL2210728','CL2210392','CL2211093',
'CL2210369','CL2211026','CL2210297','CL2211128','CL2210273','CL2210992','CL2210030','CL2210956','CL2210321','CL2211162','CL2210249','CL2211060','CL2210345','CL2211196','CL2210006','CL2210729',
'CL2210393','CL2211094','CL2210370','CL2211027','CL2210298','CL2211129','CL2210274','CL2210993','CL2210031','CL2210957','CL2210322','CL2211163','CL2210250','CL2211061','CL2210346','CL2211197',
'CL2210007','CL2210730','CL2210394','CL2211095','CL2210371','CL2211028','CL2210299','CL2211130','CL2210275','CL2210994','CL2210032','CL2210958','CL2210323','CL2211164','CL2210251','CL2211062',
'CL2210347','CL2211198','CL2210008','CL2210731','CL2210395','CL2211096','CL2210372','CL2211029','CL2210300','CL2211131','CL2210276','CL2210995','CL2210033','CL2210959','CL2210324','CL2211165',
'CL2210252','CL2211063','CL2210348','CL2211199','CL2210009','CL2210732','CL2210396','CL2211097','CL2210373','CL2211030','CL2210301','CL2211132','CL2210277','CL2210996','CL2210034','CL2210960',
'CL2210325','CL2211166','CL2210253','CL2211064','CL2210349','CL2211200','CL2210010','CL2210733','CL2210397','CL2211098','CL2210374','CL2211031','CL2210302','CL2211133','CL2210278','CL2210997',
'CL2210035','CL2210961','CL2210326','CL2211167','CL2210254','CL2211065','CL2210350','CL2211201','CL2210011','CL2210734','CL2210398','CL2211099','CL2210375','CL2211032','CL2210303','CL2211134',
'CL2210279','CL2210998','CL2210036','CL2210962','CL2210327','CL2211168','CL2210255','CL2211066','CL2210351','CL2211202','CL2210012','CL2210735','CL2210399','CL2211100','CL2210376','CL2211033',
'CL2210304','CL2211135','CL2210280','CL2210999','CL2210037','CL2210963','CL2210328','CL2211169','CL2210256','CL2211067','CL2210352','CL2211203','CL2210013','CL2210736','CL2210400','CL2211101',
'CL2210377','CL2211034','CL2210305','CL2211136','CL2210281','CL2211000','CL2210038','CL2210964','CL2210329','CL2211170','CL2210257','CL2211068','CL2210353','CL2211204','CL2210014','CL2210737',
'CL2210401','CL2211102','CL2210378','CL2211035','CL2210306','CL2211137','CL2210282','CL2211001','CL2210039','CL2210965','CL2210330','CL2211171','CL2210258','CL2211069','CL2210354','CL2211205',
'CL2210015','CL2210738','CL2210402','CL2211103','CL2210379','CL2211036','CL2210307','CL2211138','CL2210283','CL2211002','CL2210040','CL2210966','CL2210331','CL2211172','CL2210259','CL2211070',
'CL2210355','CL2211206','CL2210016','CL2210739','CL2210403','CL2211104','CL2210094','CL2211222','CL2210057','CL2210782','CL2210095','CL2211224','CL2210058','CL2210783','CL2210096','CL2211225',
'CL2210059','CL2210784','CL2210101','CL2211230','CL2210053','CL2210789','CL2210097','CL2211226','CL2210049','CL2210785','CL2210099','CL2211228','CL2210051','CL2210787','CL2210098','CL2211227',
'CL2210050','CL2210786','CL2210100','CL2211229','CL2210052','CL2210788','CL2210102','CL2211231','CL2210054','CL2210790','CL2210103','CL2211232','CL2210060','CL2210791','CL2210104','CL2211233',
'CL2210061','CL2210792','CL2210105','CL2211234','CL2210062','CL2210793','CL2210106','CL2211235','CL2210063','CL2210794','CL2210107','CL2211236','CL2210064','CL2210795','CL2210108','CL2211237',
'CL2210065','CL2210796','CL2210109','CL2211238','CL2210066','CL2210797','CL2210110','CL2211239','CL2210067','CL2210798','CL2210111','CL2211240','CL2210068','CL2210799','CL2210112','CL2211241',
'CL2210069','CL2210800','CL2210115','CL2211244','CL2210072','CL2210803','CL2210116','CL2211245','CL2210073','CL2210804','CL2210114','CL2211243','CL2210071','CL2210802','CL2210429','CL2210439',
'CL2210113','CL2211242','CL2210070','CL2210801','CL2210134','CL2211262','CL2210091','CL2210821','CL2210135','CL2211263','CL2210055','CL2210822','CL2210720','CL2210781','CL2210136','CL2211264',
'CL2210092','CL2210823','CL2210125','CL2211253','CL2210082','CL2210812','CL2210126','CL2211254','CL2210083','CL2210813','CL2210127','CL2211255','CL2210084','CL2210814','CL2210128','CL2211256',
'CL2210085','CL2210815','CL2210129','CL2211257','CL2210086','CL2210816','CL2210133','CL2211261','CL2210090','CL2210820','CL2210130','CL2211258','CL2210087','CL2210817','CL2210132','CL2211260',
'CL2210089','CL2210819','CL2210131','CL2211259','CL2210088','CL2210818','CL2211249','CL2220188','CL2210808','CL2220067','CL2211250','CL2220189','CL2210809','CL2220068','CL2211251','CL2220190',
'CL2210810','CL2220069','CL2211252','CL2220191','CL2210811','CL2220070','CL3120029','CL3120218','CL3120026','CL3120199','CL3120025','CL3120325','CL3120030','CL3120237','CL3120027','CL3120287',
'CL3120023','CL3120160','CL3120022','CL3120090','CL3120306','CL3120031','CL3120266','CL0349986','CL0349987','CL0349835','CL0349836','CL0349879','CL0349880','CL0349960','CL0349961','CL0349990',
'CL0349991','CL0349988','CL0349989','CL0349901','CL0349907','CL0349838','CL0349844','CL0349882','CL0349888','CL0349962','CL0349963','CL0349922','CL0349924','CL0349908','CL0349915','CL0349909',
'CL0349916','CL0349910','CL0349917','CL0349914','CL0349921','CL0349911','CL0349918','CL0349913','CL0349920','CL0349912','CL0349919','CL0349998','CL0349999','CL0361528','CL0361529','CL0367581',
'CL0367582','CL0361536','CL0361538','CL0361539','CL0381664','CL0381681','CL0381665','CL0381682','CL0381885','CL0381886','CL0381835','CL0381836','CL0381694','CL0381695','CL0381981','CL0381982',
'CL0381931','CL0381932','CL0381413','CL0381414','CL0381735','CL0381736','CL0381323','CL0381324','CL0381785','CL0381786','CL0381855','CL0382328','CL0381852','CL0382325','CL0381854','CL0382327',
'CL0381853','CL0382326','CL0381571','CL0381572','CL0381578','CL0382081','CL0382082','CL0382088','CL0381531','CL0381532','CL0381538','CL0381576','CL0381582','CL0381583','CL0382086','CL0382092',
'CL0382093','CL0381536','CL0381542','CL0381543','CL0367591','CL0367592','CL0367593','CL0367594','CL0367595','CL0367596','CL0367597','CL0367236','CL0367237','CL0367238','CL0367239','CL0367240',
'CL0367241','CL0367242','CL0380927','CL0367598','CL0367599','CL0367600','CL0367601','CL0367602','CL0367603','CL0367604','CL0367605','CL0367606','CL0367607','CL0367608','CL0367609','CL0367610',
'CL0367611','CL0367612','CL0367613','CL0367614','CL0367615','CL0367616','CL0367617','CL0367618','CL0367619','CL0367620','CL0367621','CL0367622','CL0367623','CL0367624','CL0367625','CL0367626',
'CL0367627','CL0367628','CL0367629','CL0367630','CL0367631','CL0367632','CL0367633','CL0367634','CL0367635','CL0367636','CL0367637','CL0367638','CL0367639','CL0367640','CL0367641','CL0367642',
'CL0367643','CL0367644','CL0367645','CL0367646','CL0381615','CL0381616','CL0381617','CL0381618','CL0381619','CL0381620','CL0381621','CL0367230','CL0367231','CL0367232','CL0367233','CL0367234',
'CL0367235','CL0380926','CL0400269','CL1230621','CL1230633','CL1250033','CL1250052','CL1250069','CL1250086','CL1250034','CL1250053','CL1250070','CL1250087','CL1250035','CL1250054','CL1250071',
'CL1250088','CL1250037','CL1250056','CL1250073','CL1250090','CL1250036','CL1250055','CL1250072','CL1250089','CL1250192','CL1250193','CL1250194','CL1250195','CL1250196','CL1250197','CL1250190',
'CL1250191','CL1250200','CL1250201','CL1250188','CL1250189','CL1250198','CL1250199','CL1250186','CL1250187','CL1250202','CL1250203')
ORDER BY smsevent.class_code, koza.common_code, koza_id

SELECT event_contacts.id, event_contacts.event_id, smsevent.name, smsevent.class_code, smsevent.shikenshu, smsevent.curriculum_id, curriculum_code, curriculum_name, event_contacts.contact_id as student_id, idnumber, last_name, first_name, 
	koza_contacts.koza_id, koza.koza_name, koza.common_code, jukoukeitai_code, koza.shikenshu
FROM event_contacts INNER JOIN smsevent ON smsevent.id=event_contacts.event_id AND smsevent.deleted=0
INNER JOIN curriculum ON curriculum.id=curriculum_id INNER JOIN contacts ON event_contacts.contact_id=contacts.id AND contacts.deleted=0
INNER JOIN koza_contacts ON koza_contacts.contact_id=event_contacts.contact_id AND contacts.id=koza_contacts.contact_id AND koza_contacts.deleted=0
INNER JOIN event_koza ON event_koza.koza_id=koza_contacts.koza_id  AND event_koza.event_id=smsevent.id AND event_koza.deleted=0 INNER JOIN koza ON koza.id=koza_contacts.koza_id AND koza.id=event_koza.koza_id AND koza.deleted=0
WHERE event_contacts.deleted=0 AND smsevent.id IN 
('CL2192182','CL2192460','CL2192183','CL2192461','CL2192184','CL2192462','CL2201798','CL2201799','CL2201698','CL2201699','CL2201635','CL2201645','CL2201633','CL2201643','CL2201634','CL2201644',
'CL2200561','CL2201491','CL2210364','CL2211021','CL2210292','CL2211123','CL2210268','CL2210987','CL2210025','CL2210951','CL2210316','CL2211157','CL2210244','CL2211055','CL2210340','CL2211191',
'CL2210001','CL2210724','CL2210388','CL2211089','CL2210365','CL2211022','CL2210293','CL2211124','CL2210269','CL2210988','CL2210026','CL2210952','CL2210317','CL2211158','CL2210245','CL2211056',
'CL2210341','CL2211192','CL2210002','CL2210725','CL2210389','CL2211090','CL2210380','CL2211037','CL2210308','CL2211139','CL2210284','CL2211003','CL2210041','CL2210967','CL2210332','CL2211173',
'CL2210260','CL2211071','CL2210356','CL2211207','CL2210017','CL2210740','CL2210404','CL2211105','CL2210381','CL2211038','CL2210309','CL2211140','CL2210285','CL2211004','CL2210042','CL2210968',
'CL2210333','CL2211174','CL2210261','CL2211072','CL2210357','CL2211208','CL2210018','CL2210741','CL2210405','CL2211106','CL2210382','CL2211039','CL2210310','CL2211141','CL2210286','CL2211005',
'CL2210043','CL2210969','CL2210334','CL2211175','CL2210262','CL2211073','CL2210358','CL2211209','CL2210019','CL2210742','CL2210406','CL2211107','CL2210383','CL2211040','CL2210311','CL2211142',
'CL2210287','CL2211006','CL2210044','CL2210970','CL2210335','CL2211176','CL2210263','CL2211074','CL2210359','CL2211210','CL2210020','CL2210743','CL2210407','CL2211108','CL2210386','CL2211042',
'CL2210314','CL2211144','CL2210290','CL2211008','CL2210047','CL2210973','CL2210338','CL2211178','CL2210266','CL2211076','CL2210362','CL2211212','CL2210023','CL2210746','CL2210410','CL2211110',
'CL2210048','CL2210974','CL2210024','CL2210747','CL2210046','CL2210972','CL2210022','CL2210745','CL2210384','CL2211041','CL2210312','CL2211143','CL2210288','CL2211007','CL2210045','CL2210971',
'CL2210336','CL2211177','CL2210264','CL2211075','CL2210360','CL2211211','CL2210021','CL2210744','CL2210408','CL2211109','CL2210366','CL2211023','CL2210294','CL2211125','CL2210270','CL2210989',
'CL2210027','CL2210953','CL2210318','CL2211159','CL2210246','CL2211057','CL2210342','CL2211193','CL2210003','CL2210726','CL2210390','CL2211091','CL2210367','CL2211024','CL2210295','CL2211126',
'CL2210271','CL2210990','CL2210028','CL2210954','CL2210319','CL2211160','CL2210247','CL2211058','CL2210343','CL2211194','CL2210004','CL2210727','CL2210391','CL2211092','CL2210368','CL2211025',
'CL2210296','CL2211127','CL2210272','CL2210991','CL2210029','CL2210955','CL2210320','CL2211161','CL2210248','CL2211059','CL2210344','CL2211195','CL2210005','CL2210728','CL2210392','CL2211093',
'CL2210369','CL2211026','CL2210297','CL2211128','CL2210273','CL2210992','CL2210030','CL2210956','CL2210321','CL2211162','CL2210249','CL2211060','CL2210345','CL2211196','CL2210006','CL2210729',
'CL2210393','CL2211094','CL2210370','CL2211027','CL2210298','CL2211129','CL2210274','CL2210993','CL2210031','CL2210957','CL2210322','CL2211163','CL2210250','CL2211061','CL2210346','CL2211197',
'CL2210007','CL2210730','CL2210394','CL2211095','CL2210371','CL2211028','CL2210299','CL2211130','CL2210275','CL2210994','CL2210032','CL2210958','CL2210323','CL2211164','CL2210251','CL2211062',
'CL2210347','CL2211198','CL2210008','CL2210731','CL2210395','CL2211096','CL2210372','CL2211029','CL2210300','CL2211131','CL2210276','CL2210995','CL2210033','CL2210959','CL2210324','CL2211165',
'CL2210252','CL2211063','CL2210348','CL2211199','CL2210009','CL2210732','CL2210396','CL2211097','CL2210373','CL2211030','CL2210301','CL2211132','CL2210277','CL2210996','CL2210034','CL2210960',
'CL2210325','CL2211166','CL2210253','CL2211064','CL2210349','CL2211200','CL2210010','CL2210733','CL2210397','CL2211098','CL2210374','CL2211031','CL2210302','CL2211133','CL2210278','CL2210997',
'CL2210035','CL2210961','CL2210326','CL2211167','CL2210254','CL2211065','CL2210350','CL2211201','CL2210011','CL2210734','CL2210398','CL2211099','CL2210375','CL2211032','CL2210303','CL2211134',
'CL2210279','CL2210998','CL2210036','CL2210962','CL2210327','CL2211168','CL2210255','CL2211066','CL2210351','CL2211202','CL2210012','CL2210735','CL2210399','CL2211100','CL2210376','CL2211033',
'CL2210304','CL2211135','CL2210280','CL2210999','CL2210037','CL2210963','CL2210328','CL2211169','CL2210256','CL2211067','CL2210352','CL2211203','CL2210013','CL2210736','CL2210400','CL2211101',
'CL2210377','CL2211034','CL2210305','CL2211136','CL2210281','CL2211000','CL2210038','CL2210964','CL2210329','CL2211170','CL2210257','CL2211068','CL2210353','CL2211204','CL2210014','CL2210737',
'CL2210401','CL2211102','CL2210378','CL2211035','CL2210306','CL2211137','CL2210282','CL2211001','CL2210039','CL2210965','CL2210330','CL2211171','CL2210258','CL2211069','CL2210354','CL2211205',
'CL2210015','CL2210738','CL2210402','CL2211103','CL2210379','CL2211036','CL2210307','CL2211138','CL2210283','CL2211002','CL2210040','CL2210966','CL2210331','CL2211172','CL2210259','CL2211070',
'CL2210355','CL2211206','CL2210016','CL2210739','CL2210403','CL2211104','CL2210094','CL2211222','CL2210057','CL2210782','CL2210095','CL2211224','CL2210058','CL2210783','CL2210096','CL2211225',
'CL2210059','CL2210784','CL2210101','CL2211230','CL2210053','CL2210789','CL2210097','CL2211226','CL2210049','CL2210785','CL2210099','CL2211228','CL2210051','CL2210787','CL2210098','CL2211227',
'CL2210050','CL2210786','CL2210100','CL2211229','CL2210052','CL2210788','CL2210102','CL2211231','CL2210054','CL2210790','CL2210103','CL2211232','CL2210060','CL2210791','CL2210104','CL2211233',
'CL2210061','CL2210792','CL2210105','CL2211234','CL2210062','CL2210793','CL2210106','CL2211235','CL2210063','CL2210794','CL2210107','CL2211236','CL2210064','CL2210795','CL2210108','CL2211237',
'CL2210065','CL2210796','CL2210109','CL2211238','CL2210066','CL2210797','CL2210110','CL2211239','CL2210067','CL2210798','CL2210111','CL2211240','CL2210068','CL2210799','CL2210112','CL2211241',
'CL2210069','CL2210800','CL2210115','CL2211244','CL2210072','CL2210803','CL2210116','CL2211245','CL2210073','CL2210804','CL2210114','CL2211243','CL2210071','CL2210802','CL2210429','CL2210439',
'CL2210113','CL2211242','CL2210070','CL2210801','CL2210134','CL2211262','CL2210091','CL2210821','CL2210135','CL2211263','CL2210055','CL2210822','CL2210720','CL2210781','CL2210136','CL2211264',
'CL2210092','CL2210823','CL2210125','CL2211253','CL2210082','CL2210812','CL2210126','CL2211254','CL2210083','CL2210813','CL2210127','CL2211255','CL2210084','CL2210814','CL2210128','CL2211256',
'CL2210085','CL2210815','CL2210129','CL2211257','CL2210086','CL2210816','CL2210133','CL2211261','CL2210090','CL2210820','CL2210130','CL2211258','CL2210087','CL2210817','CL2210132','CL2211260',
'CL2210089','CL2210819','CL2210131','CL2211259','CL2210088','CL2210818','CL2211249','CL2220188','CL2210808','CL2220067','CL2211250','CL2220189','CL2210809','CL2220068','CL2211251','CL2220190',
'CL2210810','CL2220069','CL2211252','CL2220191','CL2210811','CL2220070','CL3120029','CL3120218','CL3120026','CL3120199','CL3120025','CL3120325','CL3120030','CL3120237','CL3120027','CL3120287',
'CL3120023','CL3120160','CL3120022','CL3120090','CL3120306','CL3120031','CL3120266','CL0349986','CL0349987','CL0349835','CL0349836','CL0349879','CL0349880','CL0349960','CL0349961','CL0349990',
'CL0349991','CL0349988','CL0349989','CL0349901','CL0349907','CL0349838','CL0349844','CL0349882','CL0349888','CL0349962','CL0349963','CL0349922','CL0349924','CL0349908','CL0349915','CL0349909',
'CL0349916','CL0349910','CL0349917','CL0349914','CL0349921','CL0349911','CL0349918','CL0349913','CL0349920','CL0349912','CL0349919','CL0349998','CL0349999','CL0361528','CL0361529','CL0367581',
'CL0367582','CL0361536','CL0361538','CL0361539','CL0381664','CL0381681','CL0381665','CL0381682','CL0381885','CL0381886','CL0381835','CL0381836','CL0381694','CL0381695','CL0381981','CL0381982',
'CL0381931','CL0381932','CL0381413','CL0381414','CL0381735','CL0381736','CL0381323','CL0381324','CL0381785','CL0381786','CL0381855','CL0382328','CL0381852','CL0382325','CL0381854','CL0382327',
'CL0381853','CL0382326','CL0381571','CL0381572','CL0381578','CL0382081','CL0382082','CL0382088','CL0381531','CL0381532','CL0381538','CL0381576','CL0381582','CL0381583','CL0382086','CL0382092',
'CL0382093','CL0381536','CL0381542','CL0381543','CL0367591','CL0367592','CL0367593','CL0367594','CL0367595','CL0367596','CL0367597','CL0367236','CL0367237','CL0367238','CL0367239','CL0367240',
'CL0367241','CL0367242','CL0380927','CL0367598','CL0367599','CL0367600','CL0367601','CL0367602','CL0367603','CL0367604','CL0367605','CL0367606','CL0367607','CL0367608','CL0367609','CL0367610',
'CL0367611','CL0367612','CL0367613','CL0367614','CL0367615','CL0367616','CL0367617','CL0367618','CL0367619','CL0367620','CL0367621','CL0367622','CL0367623','CL0367624','CL0367625','CL0367626',
'CL0367627','CL0367628','CL0367629','CL0367630','CL0367631','CL0367632','CL0367633','CL0367634','CL0367635','CL0367636','CL0367637','CL0367638','CL0367639','CL0367640','CL0367641','CL0367642',
'CL0367643','CL0367644','CL0367645','CL0367646','CL0381615','CL0381616','CL0381617','CL0381618','CL0381619','CL0381620','CL0381621','CL0367230','CL0367231','CL0367232','CL0367233','CL0367234',
'CL0367235','CL0380926','CL0400269','CL1230621','CL1230633','CL1250033','CL1250052','CL1250069','CL1250086','CL1250034','CL1250053','CL1250070','CL1250087','CL1250035','CL1250054','CL1250071',
'CL1250088','CL1250037','CL1250056','CL1250073','CL1250090','CL1250036','CL1250055','CL1250072','CL1250089','CL1250192','CL1250193','CL1250194','CL1250195','CL1250196','CL1250197','CL1250190',
'CL1250191','CL1250200','CL1250201','CL1250188','CL1250189','CL1250198','CL1250199','CL1250186','CL1250187','CL1250202','CL1250203')
ORDER BY smsevent.class_code, idnumber

SELECT event_id, smsevent.name, smsevent.class_code, smsevent.shikenshu, smsevent.curriculum_id, curriculum_code, curriculum_name, hassobi
FROM smsevent INNER JOIN curriculum ON curriculum.id=curriculum_id INNER JOIN hassobi ON hassobi.event_id=smsevent.id 
WHERE smsevent.deleted=0 AND smsevent.id IN 
('CL2192182','CL2192460','CL2192183','CL2192461','CL2192184','CL2192462','CL2201798','CL2201799','CL2201698','CL2201699','CL2201635','CL2201645','CL2201633','CL2201643','CL2201634','CL2201644',
'CL2200561','CL2201491','CL2210364','CL2211021','CL2210292','CL2211123','CL2210268','CL2210987','CL2210025','CL2210951','CL2210316','CL2211157','CL2210244','CL2211055','CL2210340','CL2211191',
'CL2210001','CL2210724','CL2210388','CL2211089','CL2210365','CL2211022','CL2210293','CL2211124','CL2210269','CL2210988','CL2210026','CL2210952','CL2210317','CL2211158','CL2210245','CL2211056',
'CL2210341','CL2211192','CL2210002','CL2210725','CL2210389','CL2211090','CL2210380','CL2211037','CL2210308','CL2211139','CL2210284','CL2211003','CL2210041','CL2210967','CL2210332','CL2211173',
'CL2210260','CL2211071','CL2210356','CL2211207','CL2210017','CL2210740','CL2210404','CL2211105','CL2210381','CL2211038','CL2210309','CL2211140','CL2210285','CL2211004','CL2210042','CL2210968',
'CL2210333','CL2211174','CL2210261','CL2211072','CL2210357','CL2211208','CL2210018','CL2210741','CL2210405','CL2211106','CL2210382','CL2211039','CL2210310','CL2211141','CL2210286','CL2211005',
'CL2210043','CL2210969','CL2210334','CL2211175','CL2210262','CL2211073','CL2210358','CL2211209','CL2210019','CL2210742','CL2210406','CL2211107','CL2210383','CL2211040','CL2210311','CL2211142',
'CL2210287','CL2211006','CL2210044','CL2210970','CL2210335','CL2211176','CL2210263','CL2211074','CL2210359','CL2211210','CL2210020','CL2210743','CL2210407','CL2211108','CL2210386','CL2211042',
'CL2210314','CL2211144','CL2210290','CL2211008','CL2210047','CL2210973','CL2210338','CL2211178','CL2210266','CL2211076','CL2210362','CL2211212','CL2210023','CL2210746','CL2210410','CL2211110',
'CL2210048','CL2210974','CL2210024','CL2210747','CL2210046','CL2210972','CL2210022','CL2210745','CL2210384','CL2211041','CL2210312','CL2211143','CL2210288','CL2211007','CL2210045','CL2210971',
'CL2210336','CL2211177','CL2210264','CL2211075','CL2210360','CL2211211','CL2210021','CL2210744','CL2210408','CL2211109','CL2210366','CL2211023','CL2210294','CL2211125','CL2210270','CL2210989',
'CL2210027','CL2210953','CL2210318','CL2211159','CL2210246','CL2211057','CL2210342','CL2211193','CL2210003','CL2210726','CL2210390','CL2211091','CL2210367','CL2211024','CL2210295','CL2211126',
'CL2210271','CL2210990','CL2210028','CL2210954','CL2210319','CL2211160','CL2210247','CL2211058','CL2210343','CL2211194','CL2210004','CL2210727','CL2210391','CL2211092','CL2210368','CL2211025',
'CL2210296','CL2211127','CL2210272','CL2210991','CL2210029','CL2210955','CL2210320','CL2211161','CL2210248','CL2211059','CL2210344','CL2211195','CL2210005','CL2210728','CL2210392','CL2211093',
'CL2210369','CL2211026','CL2210297','CL2211128','CL2210273','CL2210992','CL2210030','CL2210956','CL2210321','CL2211162','CL2210249','CL2211060','CL2210345','CL2211196','CL2210006','CL2210729',
'CL2210393','CL2211094','CL2210370','CL2211027','CL2210298','CL2211129','CL2210274','CL2210993','CL2210031','CL2210957','CL2210322','CL2211163','CL2210250','CL2211061','CL2210346','CL2211197',
'CL2210007','CL2210730','CL2210394','CL2211095','CL2210371','CL2211028','CL2210299','CL2211130','CL2210275','CL2210994','CL2210032','CL2210958','CL2210323','CL2211164','CL2210251','CL2211062',
'CL2210347','CL2211198','CL2210008','CL2210731','CL2210395','CL2211096','CL2210372','CL2211029','CL2210300','CL2211131','CL2210276','CL2210995','CL2210033','CL2210959','CL2210324','CL2211165',
'CL2210252','CL2211063','CL2210348','CL2211199','CL2210009','CL2210732','CL2210396','CL2211097','CL2210373','CL2211030','CL2210301','CL2211132','CL2210277','CL2210996','CL2210034','CL2210960',
'CL2210325','CL2211166','CL2210253','CL2211064','CL2210349','CL2211200','CL2210010','CL2210733','CL2210397','CL2211098','CL2210374','CL2211031','CL2210302','CL2211133','CL2210278','CL2210997',
'CL2210035','CL2210961','CL2210326','CL2211167','CL2210254','CL2211065','CL2210350','CL2211201','CL2210011','CL2210734','CL2210398','CL2211099','CL2210375','CL2211032','CL2210303','CL2211134',
'CL2210279','CL2210998','CL2210036','CL2210962','CL2210327','CL2211168','CL2210255','CL2211066','CL2210351','CL2211202','CL2210012','CL2210735','CL2210399','CL2211100','CL2210376','CL2211033',
'CL2210304','CL2211135','CL2210280','CL2210999','CL2210037','CL2210963','CL2210328','CL2211169','CL2210256','CL2211067','CL2210352','CL2211203','CL2210013','CL2210736','CL2210400','CL2211101',
'CL2210377','CL2211034','CL2210305','CL2211136','CL2210281','CL2211000','CL2210038','CL2210964','CL2210329','CL2211170','CL2210257','CL2211068','CL2210353','CL2211204','CL2210014','CL2210737',
'CL2210401','CL2211102','CL2210378','CL2211035','CL2210306','CL2211137','CL2210282','CL2211001','CL2210039','CL2210965','CL2210330','CL2211171','CL2210258','CL2211069','CL2210354','CL2211205',
'CL2210015','CL2210738','CL2210402','CL2211103','CL2210379','CL2211036','CL2210307','CL2211138','CL2210283','CL2211002','CL2210040','CL2210966','CL2210331','CL2211172','CL2210259','CL2211070',
'CL2210355','CL2211206','CL2210016','CL2210739','CL2210403','CL2211104','CL2210094','CL2211222','CL2210057','CL2210782','CL2210095','CL2211224','CL2210058','CL2210783','CL2210096','CL2211225',
'CL2210059','CL2210784','CL2210101','CL2211230','CL2210053','CL2210789','CL2210097','CL2211226','CL2210049','CL2210785','CL2210099','CL2211228','CL2210051','CL2210787','CL2210098','CL2211227',
'CL2210050','CL2210786','CL2210100','CL2211229','CL2210052','CL2210788','CL2210102','CL2211231','CL2210054','CL2210790','CL2210103','CL2211232','CL2210060','CL2210791','CL2210104','CL2211233',
'CL2210061','CL2210792','CL2210105','CL2211234','CL2210062','CL2210793','CL2210106','CL2211235','CL2210063','CL2210794','CL2210107','CL2211236','CL2210064','CL2210795','CL2210108','CL2211237',
'CL2210065','CL2210796','CL2210109','CL2211238','CL2210066','CL2210797','CL2210110','CL2211239','CL2210067','CL2210798','CL2210111','CL2211240','CL2210068','CL2210799','CL2210112','CL2211241',
'CL2210069','CL2210800','CL2210115','CL2211244','CL2210072','CL2210803','CL2210116','CL2211245','CL2210073','CL2210804','CL2210114','CL2211243','CL2210071','CL2210802','CL2210429','CL2210439',
'CL2210113','CL2211242','CL2210070','CL2210801','CL2210134','CL2211262','CL2210091','CL2210821','CL2210135','CL2211263','CL2210055','CL2210822','CL2210720','CL2210781','CL2210136','CL2211264',
'CL2210092','CL2210823','CL2210125','CL2211253','CL2210082','CL2210812','CL2210126','CL2211254','CL2210083','CL2210813','CL2210127','CL2211255','CL2210084','CL2210814','CL2210128','CL2211256',
'CL2210085','CL2210815','CL2210129','CL2211257','CL2210086','CL2210816','CL2210133','CL2211261','CL2210090','CL2210820','CL2210130','CL2211258','CL2210087','CL2210817','CL2210132','CL2211260',
'CL2210089','CL2210819','CL2210131','CL2211259','CL2210088','CL2210818','CL2211249','CL2220188','CL2210808','CL2220067','CL2211250','CL2220189','CL2210809','CL2220068','CL2211251','CL2220190',
'CL2210810','CL2220069','CL2211252','CL2220191','CL2210811','CL2220070','CL3120029','CL3120218','CL3120026','CL3120199','CL3120025','CL3120325','CL3120030','CL3120237','CL3120027','CL3120287',
'CL3120023','CL3120160','CL3120022','CL3120090','CL3120306','CL3120031','CL3120266','CL0349986','CL0349987','CL0349835','CL0349836','CL0349879','CL0349880','CL0349960','CL0349961','CL0349990',
'CL0349991','CL0349988','CL0349989','CL0349901','CL0349907','CL0349838','CL0349844','CL0349882','CL0349888','CL0349962','CL0349963','CL0349922','CL0349924','CL0349908','CL0349915','CL0349909',
'CL0349916','CL0349910','CL0349917','CL0349914','CL0349921','CL0349911','CL0349918','CL0349913','CL0349920','CL0349912','CL0349919','CL0349998','CL0349999','CL0361528','CL0361529','CL0367581',
'CL0367582','CL0361536','CL0361538','CL0361539','CL0381664','CL0381681','CL0381665','CL0381682','CL0381885','CL0381886','CL0381835','CL0381836','CL0381694','CL0381695','CL0381981','CL0381982',
'CL0381931','CL0381932','CL0381413','CL0381414','CL0381735','CL0381736','CL0381323','CL0381324','CL0381785','CL0381786','CL0381855','CL0382328','CL0381852','CL0382325','CL0381854','CL0382327',
'CL0381853','CL0382326','CL0381571','CL0381572','CL0381578','CL0382081','CL0382082','CL0382088','CL0381531','CL0381532','CL0381538','CL0381576','CL0381582','CL0381583','CL0382086','CL0382092',
'CL0382093','CL0381536','CL0381542','CL0381543','CL0367591','CL0367592','CL0367593','CL0367594','CL0367595','CL0367596','CL0367597','CL0367236','CL0367237','CL0367238','CL0367239','CL0367240',
'CL0367241','CL0367242','CL0380927','CL0367598','CL0367599','CL0367600','CL0367601','CL0367602','CL0367603','CL0367604','CL0367605','CL0367606','CL0367607','CL0367608','CL0367609','CL0367610',
'CL0367611','CL0367612','CL0367613','CL0367614','CL0367615','CL0367616','CL0367617','CL0367618','CL0367619','CL0367620','CL0367621','CL0367622','CL0367623','CL0367624','CL0367625','CL0367626',
'CL0367627','CL0367628','CL0367629','CL0367630','CL0367631','CL0367632','CL0367633','CL0367634','CL0367635','CL0367636','CL0367637','CL0367638','CL0367639','CL0367640','CL0367641','CL0367642',
'CL0367643','CL0367644','CL0367645','CL0367646','CL0381615','CL0381616','CL0381617','CL0381618','CL0381619','CL0381620','CL0381621','CL0367230','CL0367231','CL0367232','CL0367233','CL0367234',
'CL0367235','CL0380926','CL0400269','CL1230621','CL1230633','CL1250033','CL1250052','CL1250069','CL1250086','CL1250034','CL1250053','CL1250070','CL1250087','CL1250035','CL1250054','CL1250071',
'CL1250088','CL1250037','CL1250056','CL1250073','CL1250090','CL1250036','CL1250055','CL1250072','CL1250089','CL1250192','CL1250193','CL1250194','CL1250195','CL1250196','CL1250197','CL1250190',
'CL1250191','CL1250200','CL1250201','CL1250188','CL1250189','CL1250198','CL1250199','CL1250186','CL1250187','CL1250202','CL1250203')
ORDER BY smsevent.class_code, hassobi

SELECT event_id, smsevent.name, smsevent.class_code, smsevent.shikenshu, smsevent.curriculum_id, curriculum_code, curriculum_name, hassobi, hassocode, hassocode.id
FROM smsevent INNER JOIN curriculum ON curriculum.id=curriculum_id INNER JOIN hassobi ON hassobi.event_id=smsevent.id 
INNER JOIN hassocode_hassobi ON hassocode_hassobi.hassobi_id=hassobi.id INNER JOIN hassocode ON hassocode_hassobi.hassocode_id=hassocode.id 
WHERE smsevent.deleted=0 AND smsevent.id IN 
('CL2192182','CL2192460','CL2192183','CL2192461','CL2192184','CL2192462','CL2201798','CL2201799','CL2201698','CL2201699','CL2201635','CL2201645','CL2201633','CL2201643','CL2201634','CL2201644',
'CL2200561','CL2201491','CL2210364','CL2211021','CL2210292','CL2211123','CL2210268','CL2210987','CL2210025','CL2210951','CL2210316','CL2211157','CL2210244','CL2211055','CL2210340','CL2211191',
'CL2210001','CL2210724','CL2210388','CL2211089','CL2210365','CL2211022','CL2210293','CL2211124','CL2210269','CL2210988','CL2210026','CL2210952','CL2210317','CL2211158','CL2210245','CL2211056',
'CL2210341','CL2211192','CL2210002','CL2210725','CL2210389','CL2211090','CL2210380','CL2211037','CL2210308','CL2211139','CL2210284','CL2211003','CL2210041','CL2210967','CL2210332','CL2211173',
'CL2210260','CL2211071','CL2210356','CL2211207','CL2210017','CL2210740','CL2210404','CL2211105','CL2210381','CL2211038','CL2210309','CL2211140','CL2210285','CL2211004','CL2210042','CL2210968',
'CL2210333','CL2211174','CL2210261','CL2211072','CL2210357','CL2211208','CL2210018','CL2210741','CL2210405','CL2211106','CL2210382','CL2211039','CL2210310','CL2211141','CL2210286','CL2211005',
'CL2210043','CL2210969','CL2210334','CL2211175','CL2210262','CL2211073','CL2210358','CL2211209','CL2210019','CL2210742','CL2210406','CL2211107','CL2210383','CL2211040','CL2210311','CL2211142',
'CL2210287','CL2211006','CL2210044','CL2210970','CL2210335','CL2211176','CL2210263','CL2211074','CL2210359','CL2211210','CL2210020','CL2210743','CL2210407','CL2211108','CL2210386','CL2211042',
'CL2210314','CL2211144','CL2210290','CL2211008','CL2210047','CL2210973','CL2210338','CL2211178','CL2210266','CL2211076','CL2210362','CL2211212','CL2210023','CL2210746','CL2210410','CL2211110',
'CL2210048','CL2210974','CL2210024','CL2210747','CL2210046','CL2210972','CL2210022','CL2210745','CL2210384','CL2211041','CL2210312','CL2211143','CL2210288','CL2211007','CL2210045','CL2210971',
'CL2210336','CL2211177','CL2210264','CL2211075','CL2210360','CL2211211','CL2210021','CL2210744','CL2210408','CL2211109','CL2210366','CL2211023','CL2210294','CL2211125','CL2210270','CL2210989',
'CL2210027','CL2210953','CL2210318','CL2211159','CL2210246','CL2211057','CL2210342','CL2211193','CL2210003','CL2210726','CL2210390','CL2211091','CL2210367','CL2211024','CL2210295','CL2211126',
'CL2210271','CL2210990','CL2210028','CL2210954','CL2210319','CL2211160','CL2210247','CL2211058','CL2210343','CL2211194','CL2210004','CL2210727','CL2210391','CL2211092','CL2210368','CL2211025',
'CL2210296','CL2211127','CL2210272','CL2210991','CL2210029','CL2210955','CL2210320','CL2211161','CL2210248','CL2211059','CL2210344','CL2211195','CL2210005','CL2210728','CL2210392','CL2211093',
'CL2210369','CL2211026','CL2210297','CL2211128','CL2210273','CL2210992','CL2210030','CL2210956','CL2210321','CL2211162','CL2210249','CL2211060','CL2210345','CL2211196','CL2210006','CL2210729',
'CL2210393','CL2211094','CL2210370','CL2211027','CL2210298','CL2211129','CL2210274','CL2210993','CL2210031','CL2210957','CL2210322','CL2211163','CL2210250','CL2211061','CL2210346','CL2211197',
'CL2210007','CL2210730','CL2210394','CL2211095','CL2210371','CL2211028','CL2210299','CL2211130','CL2210275','CL2210994','CL2210032','CL2210958','CL2210323','CL2211164','CL2210251','CL2211062',
'CL2210347','CL2211198','CL2210008','CL2210731','CL2210395','CL2211096','CL2210372','CL2211029','CL2210300','CL2211131','CL2210276','CL2210995','CL2210033','CL2210959','CL2210324','CL2211165',
'CL2210252','CL2211063','CL2210348','CL2211199','CL2210009','CL2210732','CL2210396','CL2211097','CL2210373','CL2211030','CL2210301','CL2211132','CL2210277','CL2210996','CL2210034','CL2210960',
'CL2210325','CL2211166','CL2210253','CL2211064','CL2210349','CL2211200','CL2210010','CL2210733','CL2210397','CL2211098','CL2210374','CL2211031','CL2210302','CL2211133','CL2210278','CL2210997',
'CL2210035','CL2210961','CL2210326','CL2211167','CL2210254','CL2211065','CL2210350','CL2211201','CL2210011','CL2210734','CL2210398','CL2211099','CL2210375','CL2211032','CL2210303','CL2211134',
'CL2210279','CL2210998','CL2210036','CL2210962','CL2210327','CL2211168','CL2210255','CL2211066','CL2210351','CL2211202','CL2210012','CL2210735','CL2210399','CL2211100','CL2210376','CL2211033',
'CL2210304','CL2211135','CL2210280','CL2210999','CL2210037','CL2210963','CL2210328','CL2211169','CL2210256','CL2211067','CL2210352','CL2211203','CL2210013','CL2210736','CL2210400','CL2211101',
'CL2210377','CL2211034','CL2210305','CL2211136','CL2210281','CL2211000','CL2210038','CL2210964','CL2210329','CL2211170','CL2210257','CL2211068','CL2210353','CL2211204','CL2210014','CL2210737',
'CL2210401','CL2211102','CL2210378','CL2211035','CL2210306','CL2211137','CL2210282','CL2211001','CL2210039','CL2210965','CL2210330','CL2211171','CL2210258','CL2211069','CL2210354','CL2211205',
'CL2210015','CL2210738','CL2210402','CL2211103','CL2210379','CL2211036','CL2210307','CL2211138','CL2210283','CL2211002','CL2210040','CL2210966','CL2210331','CL2211172','CL2210259','CL2211070',
'CL2210355','CL2211206','CL2210016','CL2210739','CL2210403','CL2211104','CL2210094','CL2211222','CL2210057','CL2210782','CL2210095','CL2211224','CL2210058','CL2210783','CL2210096','CL2211225',
'CL2210059','CL2210784','CL2210101','CL2211230','CL2210053','CL2210789','CL2210097','CL2211226','CL2210049','CL2210785','CL2210099','CL2211228','CL2210051','CL2210787','CL2210098','CL2211227',
'CL2210050','CL2210786','CL2210100','CL2211229','CL2210052','CL2210788','CL2210102','CL2211231','CL2210054','CL2210790','CL2210103','CL2211232','CL2210060','CL2210791','CL2210104','CL2211233',
'CL2210061','CL2210792','CL2210105','CL2211234','CL2210062','CL2210793','CL2210106','CL2211235','CL2210063','CL2210794','CL2210107','CL2211236','CL2210064','CL2210795','CL2210108','CL2211237',
'CL2210065','CL2210796','CL2210109','CL2211238','CL2210066','CL2210797','CL2210110','CL2211239','CL2210067','CL2210798','CL2210111','CL2211240','CL2210068','CL2210799','CL2210112','CL2211241',
'CL2210069','CL2210800','CL2210115','CL2211244','CL2210072','CL2210803','CL2210116','CL2211245','CL2210073','CL2210804','CL2210114','CL2211243','CL2210071','CL2210802','CL2210429','CL2210439',
'CL2210113','CL2211242','CL2210070','CL2210801','CL2210134','CL2211262','CL2210091','CL2210821','CL2210135','CL2211263','CL2210055','CL2210822','CL2210720','CL2210781','CL2210136','CL2211264',
'CL2210092','CL2210823','CL2210125','CL2211253','CL2210082','CL2210812','CL2210126','CL2211254','CL2210083','CL2210813','CL2210127','CL2211255','CL2210084','CL2210814','CL2210128','CL2211256',
'CL2210085','CL2210815','CL2210129','CL2211257','CL2210086','CL2210816','CL2210133','CL2211261','CL2210090','CL2210820','CL2210130','CL2211258','CL2210087','CL2210817','CL2210132','CL2211260',
'CL2210089','CL2210819','CL2210131','CL2211259','CL2210088','CL2210818','CL2211249','CL2220188','CL2210808','CL2220067','CL2211250','CL2220189','CL2210809','CL2220068','CL2211251','CL2220190',
'CL2210810','CL2220069','CL2211252','CL2220191','CL2210811','CL2220070','CL3120029','CL3120218','CL3120026','CL3120199','CL3120025','CL3120325','CL3120030','CL3120237','CL3120027','CL3120287',
'CL3120023','CL3120160','CL3120022','CL3120090','CL3120306','CL3120031','CL3120266','CL0349986','CL0349987','CL0349835','CL0349836','CL0349879','CL0349880','CL0349960','CL0349961','CL0349990',
'CL0349991','CL0349988','CL0349989','CL0349901','CL0349907','CL0349838','CL0349844','CL0349882','CL0349888','CL0349962','CL0349963','CL0349922','CL0349924','CL0349908','CL0349915','CL0349909',
'CL0349916','CL0349910','CL0349917','CL0349914','CL0349921','CL0349911','CL0349918','CL0349913','CL0349920','CL0349912','CL0349919','CL0349998','CL0349999','CL0361528','CL0361529','CL0367581',
'CL0367582','CL0361536','CL0361538','CL0361539','CL0381664','CL0381681','CL0381665','CL0381682','CL0381885','CL0381886','CL0381835','CL0381836','CL0381694','CL0381695','CL0381981','CL0381982',
'CL0381931','CL0381932','CL0381413','CL0381414','CL0381735','CL0381736','CL0381323','CL0381324','CL0381785','CL0381786','CL0381855','CL0382328','CL0381852','CL0382325','CL0381854','CL0382327',
'CL0381853','CL0382326','CL0381571','CL0381572','CL0381578','CL0382081','CL0382082','CL0382088','CL0381531','CL0381532','CL0381538','CL0381576','CL0381582','CL0381583','CL0382086','CL0382092',
'CL0382093','CL0381536','CL0381542','CL0381543','CL0367591','CL0367592','CL0367593','CL0367594','CL0367595','CL0367596','CL0367597','CL0367236','CL0367237','CL0367238','CL0367239','CL0367240',
'CL0367241','CL0367242','CL0380927','CL0367598','CL0367599','CL0367600','CL0367601','CL0367602','CL0367603','CL0367604','CL0367605','CL0367606','CL0367607','CL0367608','CL0367609','CL0367610',
'CL0367611','CL0367612','CL0367613','CL0367614','CL0367615','CL0367616','CL0367617','CL0367618','CL0367619','CL0367620','CL0367621','CL0367622','CL0367623','CL0367624','CL0367625','CL0367626',
'CL0367627','CL0367628','CL0367629','CL0367630','CL0367631','CL0367632','CL0367633','CL0367634','CL0367635','CL0367636','CL0367637','CL0367638','CL0367639','CL0367640','CL0367641','CL0367642',
'CL0367643','CL0367644','CL0367645','CL0367646','CL0381615','CL0381616','CL0381617','CL0381618','CL0381619','CL0381620','CL0381621','CL0367230','CL0367231','CL0367232','CL0367233','CL0367234',
'CL0367235','CL0380926','CL0400269','CL1230621','CL1230633','CL1250033','CL1250052','CL1250069','CL1250086','CL1250034','CL1250053','CL1250070','CL1250087','CL1250035','CL1250054','CL1250071',
'CL1250088','CL1250037','CL1250056','CL1250073','CL1250090','CL1250036','CL1250055','CL1250072','CL1250089','CL1250192','CL1250193','CL1250194','CL1250195','CL1250196','CL1250197','CL1250190',
'CL1250191','CL1250200','CL1250201','CL1250188','CL1250189','CL1250198','CL1250199','CL1250186','CL1250187','CL1250202','CL1250203')
ORDER BY smsevent.class_code, hassocode

SELECT event_id, smsevent.name, smsevent.class_code, smsevent.shikenshu, smsevent.curriculum_id, curriculum_code, curriculum_name, hassobi, hassocode, hassocode.id, hassocode_buppin.hassocode_id, buppin_id, buppin.buppin_name, hasso_suryo
FROM smsevent INNER JOIN curriculum ON curriculum.id=curriculum_id INNER JOIN hassobi ON hassobi.event_id=smsevent.id INNER JOIN hassocode_hassobi ON hassocode_hassobi.hassobi_id=hassobi.id 
INNER JOIN hassocode ON hassocode_hassobi.hassocode_id=hassocode.id INNER JOIN hassocode_buppin ON hassocode_buppin.hassocode_id=hassocode.id INNER JOIN buppin ON hassocode_buppin.buppin_id=buppin.id
WHERE smsevent.deleted=0 AND smsevent.id IN 
('CL2192182','CL2192460','CL2192183','CL2192461','CL2192184','CL2192462','CL2201798','CL2201799','CL2201698','CL2201699','CL2201635','CL2201645','CL2201633','CL2201643','CL2201634','CL2201644',
'CL2200561','CL2201491','CL2210364','CL2211021','CL2210292','CL2211123','CL2210268','CL2210987','CL2210025','CL2210951','CL2210316','CL2211157','CL2210244','CL2211055','CL2210340','CL2211191',
'CL2210001','CL2210724','CL2210388','CL2211089','CL2210365','CL2211022','CL2210293','CL2211124','CL2210269','CL2210988','CL2210026','CL2210952','CL2210317','CL2211158','CL2210245','CL2211056',
'CL2210341','CL2211192','CL2210002','CL2210725','CL2210389','CL2211090','CL2210380','CL2211037','CL2210308','CL2211139','CL2210284','CL2211003','CL2210041','CL2210967','CL2210332','CL2211173',
'CL2210260','CL2211071','CL2210356','CL2211207','CL2210017','CL2210740','CL2210404','CL2211105','CL2210381','CL2211038','CL2210309','CL2211140','CL2210285','CL2211004','CL2210042','CL2210968',
'CL2210333','CL2211174','CL2210261','CL2211072','CL2210357','CL2211208','CL2210018','CL2210741','CL2210405','CL2211106','CL2210382','CL2211039','CL2210310','CL2211141','CL2210286','CL2211005',
'CL2210043','CL2210969','CL2210334','CL2211175','CL2210262','CL2211073','CL2210358','CL2211209','CL2210019','CL2210742','CL2210406','CL2211107','CL2210383','CL2211040','CL2210311','CL2211142',
'CL2210287','CL2211006','CL2210044','CL2210970','CL2210335','CL2211176','CL2210263','CL2211074','CL2210359','CL2211210','CL2210020','CL2210743','CL2210407','CL2211108','CL2210386','CL2211042',
'CL2210314','CL2211144','CL2210290','CL2211008','CL2210047','CL2210973','CL2210338','CL2211178','CL2210266','CL2211076','CL2210362','CL2211212','CL2210023','CL2210746','CL2210410','CL2211110',
'CL2210048','CL2210974','CL2210024','CL2210747','CL2210046','CL2210972','CL2210022','CL2210745','CL2210384','CL2211041','CL2210312','CL2211143','CL2210288','CL2211007','CL2210045','CL2210971',
'CL2210336','CL2211177','CL2210264','CL2211075','CL2210360','CL2211211','CL2210021','CL2210744','CL2210408','CL2211109','CL2210366','CL2211023','CL2210294','CL2211125','CL2210270','CL2210989',
'CL2210027','CL2210953','CL2210318','CL2211159','CL2210246','CL2211057','CL2210342','CL2211193','CL2210003','CL2210726','CL2210390','CL2211091','CL2210367','CL2211024','CL2210295','CL2211126',
'CL2210271','CL2210990','CL2210028','CL2210954','CL2210319','CL2211160','CL2210247','CL2211058','CL2210343','CL2211194','CL2210004','CL2210727','CL2210391','CL2211092','CL2210368','CL2211025',
'CL2210296','CL2211127','CL2210272','CL2210991','CL2210029','CL2210955','CL2210320','CL2211161','CL2210248','CL2211059','CL2210344','CL2211195','CL2210005','CL2210728','CL2210392','CL2211093',
'CL2210369','CL2211026','CL2210297','CL2211128','CL2210273','CL2210992','CL2210030','CL2210956','CL2210321','CL2211162','CL2210249','CL2211060','CL2210345','CL2211196','CL2210006','CL2210729',
'CL2210393','CL2211094','CL2210370','CL2211027','CL2210298','CL2211129','CL2210274','CL2210993','CL2210031','CL2210957','CL2210322','CL2211163','CL2210250','CL2211061','CL2210346','CL2211197',
'CL2210007','CL2210730','CL2210394','CL2211095','CL2210371','CL2211028','CL2210299','CL2211130','CL2210275','CL2210994','CL2210032','CL2210958','CL2210323','CL2211164','CL2210251','CL2211062',
'CL2210347','CL2211198','CL2210008','CL2210731','CL2210395','CL2211096','CL2210372','CL2211029','CL2210300','CL2211131','CL2210276','CL2210995','CL2210033','CL2210959','CL2210324','CL2211165',
'CL2210252','CL2211063','CL2210348','CL2211199','CL2210009','CL2210732','CL2210396','CL2211097','CL2210373','CL2211030','CL2210301','CL2211132','CL2210277','CL2210996','CL2210034','CL2210960',
'CL2210325','CL2211166','CL2210253','CL2211064','CL2210349','CL2211200','CL2210010','CL2210733','CL2210397','CL2211098','CL2210374','CL2211031','CL2210302','CL2211133','CL2210278','CL2210997',
'CL2210035','CL2210961','CL2210326','CL2211167','CL2210254','CL2211065','CL2210350','CL2211201','CL2210011','CL2210734','CL2210398','CL2211099','CL2210375','CL2211032','CL2210303','CL2211134',
'CL2210279','CL2210998','CL2210036','CL2210962','CL2210327','CL2211168','CL2210255','CL2211066','CL2210351','CL2211202','CL2210012','CL2210735','CL2210399','CL2211100','CL2210376','CL2211033',
'CL2210304','CL2211135','CL2210280','CL2210999','CL2210037','CL2210963','CL2210328','CL2211169','CL2210256','CL2211067','CL2210352','CL2211203','CL2210013','CL2210736','CL2210400','CL2211101',
'CL2210377','CL2211034','CL2210305','CL2211136','CL2210281','CL2211000','CL2210038','CL2210964','CL2210329','CL2211170','CL2210257','CL2211068','CL2210353','CL2211204','CL2210014','CL2210737',
'CL2210401','CL2211102','CL2210378','CL2211035','CL2210306','CL2211137','CL2210282','CL2211001','CL2210039','CL2210965','CL2210330','CL2211171','CL2210258','CL2211069','CL2210354','CL2211205',
'CL2210015','CL2210738','CL2210402','CL2211103','CL2210379','CL2211036','CL2210307','CL2211138','CL2210283','CL2211002','CL2210040','CL2210966','CL2210331','CL2211172','CL2210259','CL2211070',
'CL2210355','CL2211206','CL2210016','CL2210739','CL2210403','CL2211104','CL2210094','CL2211222','CL2210057','CL2210782','CL2210095','CL2211224','CL2210058','CL2210783','CL2210096','CL2211225',
'CL2210059','CL2210784','CL2210101','CL2211230','CL2210053','CL2210789','CL2210097','CL2211226','CL2210049','CL2210785','CL2210099','CL2211228','CL2210051','CL2210787','CL2210098','CL2211227',
'CL2210050','CL2210786','CL2210100','CL2211229','CL2210052','CL2210788','CL2210102','CL2211231','CL2210054','CL2210790','CL2210103','CL2211232','CL2210060','CL2210791','CL2210104','CL2211233',
'CL2210061','CL2210792','CL2210105','CL2211234','CL2210062','CL2210793','CL2210106','CL2211235','CL2210063','CL2210794','CL2210107','CL2211236','CL2210064','CL2210795','CL2210108','CL2211237',
'CL2210065','CL2210796','CL2210109','CL2211238','CL2210066','CL2210797','CL2210110','CL2211239','CL2210067','CL2210798','CL2210111','CL2211240','CL2210068','CL2210799','CL2210112','CL2211241',
'CL2210069','CL2210800','CL2210115','CL2211244','CL2210072','CL2210803','CL2210116','CL2211245','CL2210073','CL2210804','CL2210114','CL2211243','CL2210071','CL2210802','CL2210429','CL2210439',
'CL2210113','CL2211242','CL2210070','CL2210801','CL2210134','CL2211262','CL2210091','CL2210821','CL2210135','CL2211263','CL2210055','CL2210822','CL2210720','CL2210781','CL2210136','CL2211264',
'CL2210092','CL2210823','CL2210125','CL2211253','CL2210082','CL2210812','CL2210126','CL2211254','CL2210083','CL2210813','CL2210127','CL2211255','CL2210084','CL2210814','CL2210128','CL2211256',
'CL2210085','CL2210815','CL2210129','CL2211257','CL2210086','CL2210816','CL2210133','CL2211261','CL2210090','CL2210820','CL2210130','CL2211258','CL2210087','CL2210817','CL2210132','CL2211260',
'CL2210089','CL2210819','CL2210131','CL2211259','CL2210088','CL2210818','CL2211249','CL2220188','CL2210808','CL2220067','CL2211250','CL2220189','CL2210809','CL2220068','CL2211251','CL2220190',
'CL2210810','CL2220069','CL2211252','CL2220191','CL2210811','CL2220070','CL3120029','CL3120218','CL3120026','CL3120199','CL3120025','CL3120325','CL3120030','CL3120237','CL3120027','CL3120287',
'CL3120023','CL3120160','CL3120022','CL3120090','CL3120306','CL3120031','CL3120266','CL0349986','CL0349987','CL0349835','CL0349836','CL0349879','CL0349880','CL0349960','CL0349961','CL0349990',
'CL0349991','CL0349988','CL0349989','CL0349901','CL0349907','CL0349838','CL0349844','CL0349882','CL0349888','CL0349962','CL0349963','CL0349922','CL0349924','CL0349908','CL0349915','CL0349909',
'CL0349916','CL0349910','CL0349917','CL0349914','CL0349921','CL0349911','CL0349918','CL0349913','CL0349920','CL0349912','CL0349919','CL0349998','CL0349999','CL0361528','CL0361529','CL0367581',
'CL0367582','CL0361536','CL0361538','CL0361539','CL0381664','CL0381681','CL0381665','CL0381682','CL0381885','CL0381886','CL0381835','CL0381836','CL0381694','CL0381695','CL0381981','CL0381982',
'CL0381931','CL0381932','CL0381413','CL0381414','CL0381735','CL0381736','CL0381323','CL0381324','CL0381785','CL0381786','CL0381855','CL0382328','CL0381852','CL0382325','CL0381854','CL0382327',
'CL0381853','CL0382326','CL0381571','CL0381572','CL0381578','CL0382081','CL0382082','CL0382088','CL0381531','CL0381532','CL0381538','CL0381576','CL0381582','CL0381583','CL0382086','CL0382092',
'CL0382093','CL0381536','CL0381542','CL0381543','CL0367591','CL0367592','CL0367593','CL0367594','CL0367595','CL0367596','CL0367597','CL0367236','CL0367237','CL0367238','CL0367239','CL0367240',
'CL0367241','CL0367242','CL0380927','CL0367598','CL0367599','CL0367600','CL0367601','CL0367602','CL0367603','CL0367604','CL0367605','CL0367606','CL0367607','CL0367608','CL0367609','CL0367610',
'CL0367611','CL0367612','CL0367613','CL0367614','CL0367615','CL0367616','CL0367617','CL0367618','CL0367619','CL0367620','CL0367621','CL0367622','CL0367623','CL0367624','CL0367625','CL0367626',
'CL0367627','CL0367628','CL0367629','CL0367630','CL0367631','CL0367632','CL0367633','CL0367634','CL0367635','CL0367636','CL0367637','CL0367638','CL0367639','CL0367640','CL0367641','CL0367642',
'CL0367643','CL0367644','CL0367645','CL0367646','CL0381615','CL0381616','CL0381617','CL0381618','CL0381619','CL0381620','CL0381621','CL0367230','CL0367231','CL0367232','CL0367233','CL0367234',
'CL0367235','CL0380926','CL0400269','CL1230621','CL1230633','CL1250033','CL1250052','CL1250069','CL1250086','CL1250034','CL1250053','CL1250070','CL1250087','CL1250035','CL1250054','CL1250071',
'CL1250088','CL1250037','CL1250056','CL1250073','CL1250090','CL1250036','CL1250055','CL1250072','CL1250089','CL1250192','CL1250193','CL1250194','CL1250195','CL1250196','CL1250197','CL1250190',
'CL1250191','CL1250200','CL1250201','CL1250188','CL1250189','CL1250198','CL1250199','CL1250186','CL1250187','CL1250202','CL1250203')
ORDER BY smsevent.class_code, hassocode

-- 2014-01-31 koza, curriculum, clas change

-- id	curriculum_code	curriculum_name	kaikonen	kozabunrui	common_code_a	common_code_b	shikenshu
CREATE TABLE IF NOT EXISTS `curriculum3` (
  `id` char(36) NOT NULL,
  `curriculum_code` varchar(50) DEFAULT NULL,
  `curriculum_name` varchar(100) DEFAULT NULL,
  `kaikonen` varchar(5) DEFAULT NULL,
  `kozabunrui` varchar(36) DEFAULT NULL,
  `common_code_a` varchar(36) DEFAULT NULL,
  `common_code_b` varchar(36) DEFAULT NULL,
  `shikenshu` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `curriculum_code` (`curriculum_code`),
  KEY `common_code_a` (`common_code_a`),
  KEY `common_code_b` (`common_code_b`),
  KEY `curriculum_name` (`curriculum_name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8  ;

SELECT curriculum.id, curriculum.curriculum_code FROM curriculum, curriculum3 WHERE curriculum.id=curriculum3.id ;

UPDATE curriculum, curriculum3 SET 
	curriculum.curriculum_code=curriculum3.curriculum_code, 
	curriculum.curriculum_name=curriculum3.curriculum_name, 
	curriculum.kaikonen=curriculum3.kaikonen, 
	curriculum.kozabunrui=curriculum3.kozabunrui, 
	curriculum.common_code_a=curriculum3.common_code_a, 
	curriculum.common_code_b=curriculum3.common_code_b, 
	curriculum.shikenshu=curriculum3.shikenshu
WHERE curriculum.id=curriculum3.id ; 

-- id	class_code	name	teiin	kamoku	net_haishin_umu	net_haishin_kigen	web_de_doriru_taisho	kaikonen	kozabunrui	common_code_a	common_code_b
CREATE TABLE IF NOT EXISTS `smsevent7` (
  `id` char(36) NOT NULL,
  `class_code` varchar(100) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `teiin` int(11) DEFAULT NULL,
  `kamoku` varchar(100) DEFAULT NULL,
  `net_haishin_umu` varchar(5) DEFAULT NULL,
  `net_haishin_kigen` date DEFAULT NULL,
  `web_de_doriru_taisho` varchar(5) DEFAULT NULL,
  `kaikonen` varchar(5) DEFAULT NULL,
  `kozabunrui` varchar(36) DEFAULT NULL,
  `common_code_a` varchar(36) DEFAULT NULL,
  `common_code_b` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `class_code` (`class_code`),
  KEY `kamoku` (`kamoku`),
  KEY `common_code_a` (`common_code_a`),
  KEY `common_code_b` (`common_code_b`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8  ;

SELECT smsevent.id, smsevent.class_code FROM smsevent, smsevent7 WHERE smsevent.id=smsevent7.id 

UPDATE smsevent, smsevent7 SET 
	smsevent.class_code=smsevent7.class_code, 
	smsevent.name=smsevent7.name, 
	smsevent.teiin=smsevent7.teiin, 
	smsevent.kamoku=smsevent7.kamoku, 
	smsevent.net_haishin_umu=smsevent7.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent7.net_haishin_kigen, 
	smsevent.web_de_doriru_taisho=smsevent7.web_de_doriru_taisho, 
	smsevent.kaikonen=smsevent7.kaikonen, 
	smsevent.kozabunrui=smsevent7.kozabunrui, 
	smsevent.common_code_a=smsevent7.common_code_a, 
	smsevent.common_code_b=smsevent7.common_code_b										
WHERE smsevent.id=smsevent7.id ; 

-- id	common_code	jukoukeitai_code	koza_name	jukoukeitai_name	shikenshu	jukoukeitai	common_code_a	common_code_b	kozabunrui	ki	teacher_name	taishou_status	kaikonen	mokuhyonen	koza_start_date	koza_end_date	shikenbi	if_code	hanbaikahi	kouza_kingaku	shohin_kobestu_number	csv_export_date

CREATE TABLE IF NOT EXISTS `koza1` (
  `id` char(36) NOT NULL,
  `common_code` varchar(100) DEFAULT NULL,
  `jukoukeitai_code` varchar(100) DEFAULT NULL,
  `koza_name` varchar(100) DEFAULT NULL,
  `jukoukeitai_name` varchar(100) DEFAULT NULL, 
  `shikenshu` varchar(5) DEFAULT NULL,
  `jukoukeitai` varchar(36) DEFAULT NULL,
  `common_code_a` varchar(36) DEFAULT NULL,
  `common_code_b` varchar(36) DEFAULT NULL,
  `kozabunrui` varchar(36) DEFAULT NULL,
  `ki` varchar(36) DEFAULT NULL,
  `teacher_name` varchar(36) DEFAULT NULL,
  `taishou_status` varchar(5) DEFAULT NULL,
  `kaikonen` varchar(5) DEFAULT NULL,
  `mokuhyonen` varchar(5) DEFAULT NULL,
  `koza_start_date` date DEFAULT NULL,
  `koza_end_date` date DEFAULT NULL,
  `shikenbi` date DEFAULT NULL,
  `if_code` varchar(36) DEFAULT NULL,
  `hanbaikahi` varchar(36) DEFAULT NULL,
  `kouza_kingaku` double DEFAULT NULL,
  `shohin_kobestu_number` int(11) DEFAULT NULL,
  `csv_export_date` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `common_code` (`common_code`),
  KEY `common_code_a` (`common_code_a`),
  KEY `common_code_b` (`common_code_b`),
  KEY `koza_start_date` (`koza_start_date`),
  KEY `koza_end_date` (`koza_end_date`),
  KEY `shikenbi` (`shikenbi`),
  KEY `csv_export_date` (`csv_export_date`),
  KEY `koza_name` (`koza_name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

SELECT koza.id, koza.common_code FROM koza, koza1 WHERE koza.id=koza1.id 

UPDATE koza, koza1 SET 
	koza.common_code=koza1.common_code, 
	koza.jukoukeitai_code=koza1.jukoukeitai_code, 
	koza.koza_name=koza1.koza_name, 
	koza.jukoukeitai_name=koza1.jukoukeitai_name, 
	koza.shikenshu=koza1.shikenshu, 
	koza.jukoukeitai=koza1.jukoukeitai, 
	koza.common_code_a=koza1.common_code_a, 
	koza.common_code_b=koza1.common_code_b, 
	koza.kozabunrui=koza1.kozabunrui, 		
	koza.ki=koza1.ki, 
	koza.teacher_name=koza1.teacher_name, 
	koza.taishou_status=koza1.taishou_status, 
	koza.kaikonen=koza1.kaikonen, 		
	koza.mokuhyonen=koza1.mokuhyonen, 				
	koza.koza_start_date=koza1.koza_start_date, 				
	koza.koza_end_date=koza1.koza_end_date, 				
	koza.shikenbi=koza1.shikenbi, 				
	koza.if_code=koza1.if_code, 				
	koza.hanbaikahi=koza1.hanbaikahi, 				
	koza.kouza_kingaku=koza1.kouza_kingaku, 				
	koza.shohin_kobestu_number=koza1.shohin_kobestu_number, 				
	koza.csv_export_date=koza1.csv_export_date 						
WHERE koza.id=koza1.id ; 

-- tables to be backuped
-- koza, curriculum, smsevent, event_koza, event_contacts

-- 2014-02-01 

-- 1xN

SELECT event_koza.id, event_id, smsevent.name, smsevent.class_code, smsevent.shikenshu, smsevent.curriculum_id, curriculum_code, curriculum_name, smsevent.no_current_student, koza_id, koza_name, koza.common_code, jukoukeitai_code, koza.shikenshu
FROM event_koza INNER JOIN smsevent ON smsevent.id=event_koza.event_id AND smsevent.deleted=0
INNER JOIN curriculum ON curriculum.id=curriculum_id INNER JOIN koza ON event_koza.koza_id=koza.id AND koza.deleted=0
WHERE event_koza.deleted=0 AND smsevent.id IN 
('CL2191710','CL2191711','CL2191712','CL2191713','CL2191714','CL2191308','CL2191309','CL2191310','CL2191311','CL2191312','CL2191313','CL2191399','CL2191449','CL2191450','CL2191451','CL2191452',
'CL2191513','CL2191514','CL2191515','CL2191602','CL2191602','CL2191603','CL2191603','CL2191604','CL2191604','CL2191605','CL2191605','CL2191606','CL2191606','CL2191607','CL2191607','CL2191608',
'CL2191609','CL2191610','CL2191611','CL2191612','CL2191618','CL2191619','CL2191620','CL2191621','CL2191622','CL2191653','CL2191654','CL2191655','CL2191656','CL2191657','CL2191658','CL2191704',
'CL2191705','CL2191706','CL2191707','CL2191708','CL2191709','CL2191806','CL2191807','CL2191808','CL2191809','CL2191810','CL2191811','CL2191857','CL2191858','CL2191859','CL2191860','CL2191861',
'CL2191862','CL2191908','CL2191909','CL2191910','CL2191911','CL2191912','CL2191913','CL2191914','CL2191915','CL2191959','CL2191960','CL2191961','CL2191962','CL2192009','CL2192010','CL2192011',
'CL2192012','CL2192065','CL2192066','CL2192067','CL2192068','CL2192121','CL2192122','CL2192123','CL2192124','CL2192177','CL2192178','CL2192179','CL2192180','CL2192233','CL2192234','CL2192235',
'CL2192236','CL2192289','CL2192290','CL2192291','CL2192292','CL2192444','CL2192445','CL2192446','CL2192448','CL2192449','CL2192450','CL2192452','CL2192453','CL2192454','CL2192456','CL2192457',
'CL2192458','CL2192460','CL2192461','CL2192462','CL2192464','CL2192465','CL2192466','CL2192468','CL2192469','CL2192470','CL2202997','CL2202998','CL2202999','CL3111458','CL3111459','CL3111460',
'CL3111461','CL3111462','CL3111463','CL3120082','CL3120082','CL3120082','CL3120083','CL3120083','CL3120083','CL3120084','CL3120084','CL3120084','CL3120085','CL3120085','CL3120085','CL3120086',
'CL3120086','CL3120086','CL3120087','CL3120087','CL3120087','CL3120090','CL3120152','CL3120152','CL3120153','CL3120153','CL3120154','CL3120154','CL3120154','CL3120155','CL3120155','CL3120156',
'CL3120156','CL3120157','CL3120157','CL3120191','CL3120192','CL3120193','CL3120194','CL3120195','CL3120196','CL3120199','CL3120210','CL3120211','CL3120212','CL3120213','CL3120214','CL3120215',
'CL3120229','CL3120229','CL3120230','CL3120230','CL3120231','CL3120231','CL3120232','CL3120232','CL3120233','CL3120233','CL3120234','CL3120234','CL3120258','CL3120259','CL3120260','CL3120261',
'CL3120262','CL3120263','CL3120306','CL3120308','CL3120327','CL3120329','CL3120331','CL3120445','CL3120473','CL3120474','CL3120507','CL3120508','CL3120510','CL3120511','CL0361310','CL0361311',
'CL0361312','CL0361313','CL0361314','CL0361352','CL0361353','CL0361354','CL0361355','CL0361356','CL0380144','CL0380144','CL0380144','CL0380339','CL0381615','CL0381616','CL0381617','CL0381618',
'CL0381619','CL0381620','CL0381621','CL0400123','CL0400123','CL0400123','CL0400124','CL0420341','CL0420341','CL0420341','CL0420342','CL1240646','CL1250101','CL1250103','CL1250118','CL1250120',
'CL1250135','CL1250137','CL1250152','CL1250154')
ORDER BY smsevent.class_code, koza.common_code, koza_id

SELECT event_contacts.id, event_contacts.event_id, smsevent.name, smsevent.class_code, smsevent.shikenshu, smsevent.curriculum_id, curriculum_code, curriculum_name, event_contacts.contact_id as student_id, idnumber, last_name, first_name, 
	koza_contacts.koza_id, koza.koza_name, koza.common_code, jukoukeitai_code, koza.shikenshu
FROM event_contacts INNER JOIN smsevent ON smsevent.id=event_contacts.event_id AND smsevent.deleted=0
INNER JOIN curriculum ON curriculum.id=curriculum_id INNER JOIN contacts ON event_contacts.contact_id=contacts.id AND contacts.deleted=0
INNER JOIN koza_contacts ON koza_contacts.contact_id=event_contacts.contact_id AND contacts.id=koza_contacts.contact_id AND koza_contacts.deleted=0
INNER JOIN event_koza ON event_koza.koza_id=koza_contacts.koza_id  AND event_koza.event_id=smsevent.id AND event_koza.deleted=0 INNER JOIN koza ON koza.id=koza_contacts.koza_id AND koza.id=event_koza.koza_id AND koza.deleted=0
WHERE event_contacts.deleted=0 AND smsevent.id IN 
('CL2191710','CL2191711','CL2191712','CL2191713','CL2191714','CL2191308','CL2191309','CL2191310','CL2191311','CL2191312','CL2191313','CL2191399','CL2191449','CL2191450','CL2191451','CL2191452',
'CL2191513','CL2191514','CL2191515','CL2191602','CL2191602','CL2191603','CL2191603','CL2191604','CL2191604','CL2191605','CL2191605','CL2191606','CL2191606','CL2191607','CL2191607','CL2191608',
'CL2191609','CL2191610','CL2191611','CL2191612','CL2191618','CL2191619','CL2191620','CL2191621','CL2191622','CL2191653','CL2191654','CL2191655','CL2191656','CL2191657','CL2191658','CL2191704',
'CL2191705','CL2191706','CL2191707','CL2191708','CL2191709','CL2191806','CL2191807','CL2191808','CL2191809','CL2191810','CL2191811','CL2191857','CL2191858','CL2191859','CL2191860','CL2191861',
'CL2191862','CL2191908','CL2191909','CL2191910','CL2191911','CL2191912','CL2191913','CL2191914','CL2191915','CL2191959','CL2191960','CL2191961','CL2191962','CL2192009','CL2192010','CL2192011',
'CL2192012','CL2192065','CL2192066','CL2192067','CL2192068','CL2192121','CL2192122','CL2192123','CL2192124','CL2192177','CL2192178','CL2192179','CL2192180','CL2192233','CL2192234','CL2192235',
'CL2192236','CL2192289','CL2192290','CL2192291','CL2192292','CL2192444','CL2192445','CL2192446','CL2192448','CL2192449','CL2192450','CL2192452','CL2192453','CL2192454','CL2192456','CL2192457',
'CL2192458','CL2192460','CL2192461','CL2192462','CL2192464','CL2192465','CL2192466','CL2192468','CL2192469','CL2192470','CL2202997','CL2202998','CL2202999','CL3111458','CL3111459','CL3111460',
'CL3111461','CL3111462','CL3111463','CL3120082','CL3120082','CL3120082','CL3120083','CL3120083','CL3120083','CL3120084','CL3120084','CL3120084','CL3120085','CL3120085','CL3120085','CL3120086',
'CL3120086','CL3120086','CL3120087','CL3120087','CL3120087','CL3120090','CL3120152','CL3120152','CL3120153','CL3120153','CL3120154','CL3120154','CL3120154','CL3120155','CL3120155','CL3120156',
'CL3120156','CL3120157','CL3120157','CL3120191','CL3120192','CL3120193','CL3120194','CL3120195','CL3120196','CL3120199','CL3120210','CL3120211','CL3120212','CL3120213','CL3120214','CL3120215',
'CL3120229','CL3120229','CL3120230','CL3120230','CL3120231','CL3120231','CL3120232','CL3120232','CL3120233','CL3120233','CL3120234','CL3120234','CL3120258','CL3120259','CL3120260','CL3120261',
'CL3120262','CL3120263','CL3120306','CL3120308','CL3120327','CL3120329','CL3120331','CL3120445','CL3120473','CL3120474','CL3120507','CL3120508','CL3120510','CL3120511','CL0361310','CL0361311',
'CL0361312','CL0361313','CL0361314','CL0361352','CL0361353','CL0361354','CL0361355','CL0361356','CL0380144','CL0380144','CL0380144','CL0380339','CL0381615','CL0381616','CL0381617','CL0381618',
'CL0381619','CL0381620','CL0381621','CL0400123','CL0400123','CL0400123','CL0400124','CL0420341','CL0420341','CL0420341','CL0420342','CL1240646','CL1250101','CL1250103','CL1250118','CL1250120',
'CL1250135','CL1250137','CL1250152','CL1250154')
ORDER BY smsevent.class_code, idnumber

SELECT event_id, smsevent.name, smsevent.class_code, smsevent.shikenshu, smsevent.curriculum_id, curriculum_code, curriculum_name, hassobi
FROM smsevent INNER JOIN curriculum ON curriculum.id=curriculum_id INNER JOIN hassobi ON hassobi.event_id=smsevent.id 
WHERE smsevent.deleted=0 AND smsevent.id IN 
('CL2191710','CL2191711','CL2191712','CL2191713','CL2191714','CL2191308','CL2191309','CL2191310','CL2191311','CL2191312','CL2191313','CL2191399','CL2191449','CL2191450','CL2191451','CL2191452',
'CL2191513','CL2191514','CL2191515','CL2191602','CL2191602','CL2191603','CL2191603','CL2191604','CL2191604','CL2191605','CL2191605','CL2191606','CL2191606','CL2191607','CL2191607','CL2191608',
'CL2191609','CL2191610','CL2191611','CL2191612','CL2191618','CL2191619','CL2191620','CL2191621','CL2191622','CL2191653','CL2191654','CL2191655','CL2191656','CL2191657','CL2191658','CL2191704',
'CL2191705','CL2191706','CL2191707','CL2191708','CL2191709','CL2191806','CL2191807','CL2191808','CL2191809','CL2191810','CL2191811','CL2191857','CL2191858','CL2191859','CL2191860','CL2191861',
'CL2191862','CL2191908','CL2191909','CL2191910','CL2191911','CL2191912','CL2191913','CL2191914','CL2191915','CL2191959','CL2191960','CL2191961','CL2191962','CL2192009','CL2192010','CL2192011',
'CL2192012','CL2192065','CL2192066','CL2192067','CL2192068','CL2192121','CL2192122','CL2192123','CL2192124','CL2192177','CL2192178','CL2192179','CL2192180','CL2192233','CL2192234','CL2192235',
'CL2192236','CL2192289','CL2192290','CL2192291','CL2192292','CL2192444','CL2192445','CL2192446','CL2192448','CL2192449','CL2192450','CL2192452','CL2192453','CL2192454','CL2192456','CL2192457',
'CL2192458','CL2192460','CL2192461','CL2192462','CL2192464','CL2192465','CL2192466','CL2192468','CL2192469','CL2192470','CL2202997','CL2202998','CL2202999','CL3111458','CL3111459','CL3111460',
'CL3111461','CL3111462','CL3111463','CL3120082','CL3120082','CL3120082','CL3120083','CL3120083','CL3120083','CL3120084','CL3120084','CL3120084','CL3120085','CL3120085','CL3120085','CL3120086',
'CL3120086','CL3120086','CL3120087','CL3120087','CL3120087','CL3120090','CL3120152','CL3120152','CL3120153','CL3120153','CL3120154','CL3120154','CL3120154','CL3120155','CL3120155','CL3120156',
'CL3120156','CL3120157','CL3120157','CL3120191','CL3120192','CL3120193','CL3120194','CL3120195','CL3120196','CL3120199','CL3120210','CL3120211','CL3120212','CL3120213','CL3120214','CL3120215',
'CL3120229','CL3120229','CL3120230','CL3120230','CL3120231','CL3120231','CL3120232','CL3120232','CL3120233','CL3120233','CL3120234','CL3120234','CL3120258','CL3120259','CL3120260','CL3120261',
'CL3120262','CL3120263','CL3120306','CL3120308','CL3120327','CL3120329','CL3120331','CL3120445','CL3120473','CL3120474','CL3120507','CL3120508','CL3120510','CL3120511','CL0361310','CL0361311',
'CL0361312','CL0361313','CL0361314','CL0361352','CL0361353','CL0361354','CL0361355','CL0361356','CL0380144','CL0380144','CL0380144','CL0380339','CL0381615','CL0381616','CL0381617','CL0381618',
'CL0381619','CL0381620','CL0381621','CL0400123','CL0400123','CL0400123','CL0400124','CL0420341','CL0420341','CL0420341','CL0420342','CL1240646','CL1250101','CL1250103','CL1250118','CL1250120',
'CL1250135','CL1250137','CL1250152','CL1250154')
ORDER BY smsevent.class_code, hassobi

SELECT event_id, smsevent.name, smsevent.class_code, smsevent.shikenshu, smsevent.curriculum_id, curriculum_code, curriculum_name, hassobi, hassocode, hassocode.id
FROM smsevent INNER JOIN curriculum ON curriculum.id=curriculum_id INNER JOIN hassobi ON hassobi.event_id=smsevent.id 
INNER JOIN hassocode_hassobi ON hassocode_hassobi.hassobi_id=hassobi.id INNER JOIN hassocode ON hassocode_hassobi.hassocode_id=hassocode.id 
WHERE smsevent.deleted=0 AND smsevent.id IN 
('CL2191710','CL2191711','CL2191712','CL2191713','CL2191714','CL2191308','CL2191309','CL2191310','CL2191311','CL2191312','CL2191313','CL2191399','CL2191449','CL2191450','CL2191451','CL2191452',
'CL2191513','CL2191514','CL2191515','CL2191602','CL2191602','CL2191603','CL2191603','CL2191604','CL2191604','CL2191605','CL2191605','CL2191606','CL2191606','CL2191607','CL2191607','CL2191608',
'CL2191609','CL2191610','CL2191611','CL2191612','CL2191618','CL2191619','CL2191620','CL2191621','CL2191622','CL2191653','CL2191654','CL2191655','CL2191656','CL2191657','CL2191658','CL2191704',
'CL2191705','CL2191706','CL2191707','CL2191708','CL2191709','CL2191806','CL2191807','CL2191808','CL2191809','CL2191810','CL2191811','CL2191857','CL2191858','CL2191859','CL2191860','CL2191861',
'CL2191862','CL2191908','CL2191909','CL2191910','CL2191911','CL2191912','CL2191913','CL2191914','CL2191915','CL2191959','CL2191960','CL2191961','CL2191962','CL2192009','CL2192010','CL2192011',
'CL2192012','CL2192065','CL2192066','CL2192067','CL2192068','CL2192121','CL2192122','CL2192123','CL2192124','CL2192177','CL2192178','CL2192179','CL2192180','CL2192233','CL2192234','CL2192235',
'CL2192236','CL2192289','CL2192290','CL2192291','CL2192292','CL2192444','CL2192445','CL2192446','CL2192448','CL2192449','CL2192450','CL2192452','CL2192453','CL2192454','CL2192456','CL2192457',
'CL2192458','CL2192460','CL2192461','CL2192462','CL2192464','CL2192465','CL2192466','CL2192468','CL2192469','CL2192470','CL2202997','CL2202998','CL2202999','CL3111458','CL3111459','CL3111460',
'CL3111461','CL3111462','CL3111463','CL3120082','CL3120082','CL3120082','CL3120083','CL3120083','CL3120083','CL3120084','CL3120084','CL3120084','CL3120085','CL3120085','CL3120085','CL3120086',
'CL3120086','CL3120086','CL3120087','CL3120087','CL3120087','CL3120090','CL3120152','CL3120152','CL3120153','CL3120153','CL3120154','CL3120154','CL3120154','CL3120155','CL3120155','CL3120156',
'CL3120156','CL3120157','CL3120157','CL3120191','CL3120192','CL3120193','CL3120194','CL3120195','CL3120196','CL3120199','CL3120210','CL3120211','CL3120212','CL3120213','CL3120214','CL3120215',
'CL3120229','CL3120229','CL3120230','CL3120230','CL3120231','CL3120231','CL3120232','CL3120232','CL3120233','CL3120233','CL3120234','CL3120234','CL3120258','CL3120259','CL3120260','CL3120261',
'CL3120262','CL3120263','CL3120306','CL3120308','CL3120327','CL3120329','CL3120331','CL3120445','CL3120473','CL3120474','CL3120507','CL3120508','CL3120510','CL3120511','CL0361310','CL0361311',
'CL0361312','CL0361313','CL0361314','CL0361352','CL0361353','CL0361354','CL0361355','CL0361356','CL0380144','CL0380144','CL0380144','CL0380339','CL0381615','CL0381616','CL0381617','CL0381618',
'CL0381619','CL0381620','CL0381621','CL0400123','CL0400123','CL0400123','CL0400124','CL0420341','CL0420341','CL0420341','CL0420342','CL1240646','CL1250101','CL1250103','CL1250118','CL1250120',
'CL1250135','CL1250137','CL1250152','CL1250154')
ORDER BY smsevent.class_code, hassocode

SELECT event_id, smsevent.name, smsevent.class_code, smsevent.shikenshu, smsevent.curriculum_id, curriculum_code, curriculum_name, hassobi, hassocode, hassocode.id, hassocode_buppin.hassocode_id, buppin_id, buppin.buppin_name, hasso_suryo
FROM smsevent INNER JOIN curriculum ON curriculum.id=curriculum_id INNER JOIN hassobi ON hassobi.event_id=smsevent.id INNER JOIN hassocode_hassobi ON hassocode_hassobi.hassobi_id=hassobi.id 
INNER JOIN hassocode ON hassocode_hassobi.hassocode_id=hassocode.id INNER JOIN hassocode_buppin ON hassocode_buppin.hassocode_id=hassocode.id INNER JOIN buppin ON hassocode_buppin.buppin_id=buppin.id
WHERE smsevent.deleted=0 AND smsevent.id IN 
('CL2191710','CL2191711','CL2191712','CL2191713','CL2191714','CL2191308','CL2191309','CL2191310','CL2191311','CL2191312','CL2191313','CL2191399','CL2191449','CL2191450','CL2191451','CL2191452',
'CL2191513','CL2191514','CL2191515','CL2191602','CL2191602','CL2191603','CL2191603','CL2191604','CL2191604','CL2191605','CL2191605','CL2191606','CL2191606','CL2191607','CL2191607','CL2191608',
'CL2191609','CL2191610','CL2191611','CL2191612','CL2191618','CL2191619','CL2191620','CL2191621','CL2191622','CL2191653','CL2191654','CL2191655','CL2191656','CL2191657','CL2191658','CL2191704',
'CL2191705','CL2191706','CL2191707','CL2191708','CL2191709','CL2191806','CL2191807','CL2191808','CL2191809','CL2191810','CL2191811','CL2191857','CL2191858','CL2191859','CL2191860','CL2191861',
'CL2191862','CL2191908','CL2191909','CL2191910','CL2191911','CL2191912','CL2191913','CL2191914','CL2191915','CL2191959','CL2191960','CL2191961','CL2191962','CL2192009','CL2192010','CL2192011',
'CL2192012','CL2192065','CL2192066','CL2192067','CL2192068','CL2192121','CL2192122','CL2192123','CL2192124','CL2192177','CL2192178','CL2192179','CL2192180','CL2192233','CL2192234','CL2192235',
'CL2192236','CL2192289','CL2192290','CL2192291','CL2192292','CL2192444','CL2192445','CL2192446','CL2192448','CL2192449','CL2192450','CL2192452','CL2192453','CL2192454','CL2192456','CL2192457',
'CL2192458','CL2192460','CL2192461','CL2192462','CL2192464','CL2192465','CL2192466','CL2192468','CL2192469','CL2192470','CL2202997','CL2202998','CL2202999','CL3111458','CL3111459','CL3111460',
'CL3111461','CL3111462','CL3111463','CL3120082','CL3120082','CL3120082','CL3120083','CL3120083','CL3120083','CL3120084','CL3120084','CL3120084','CL3120085','CL3120085','CL3120085','CL3120086',
'CL3120086','CL3120086','CL3120087','CL3120087','CL3120087','CL3120090','CL3120152','CL3120152','CL3120153','CL3120153','CL3120154','CL3120154','CL3120154','CL3120155','CL3120155','CL3120156',
'CL3120156','CL3120157','CL3120157','CL3120191','CL3120192','CL3120193','CL3120194','CL3120195','CL3120196','CL3120199','CL3120210','CL3120211','CL3120212','CL3120213','CL3120214','CL3120215',
'CL3120229','CL3120229','CL3120230','CL3120230','CL3120231','CL3120231','CL3120232','CL3120232','CL3120233','CL3120233','CL3120234','CL3120234','CL3120258','CL3120259','CL3120260','CL3120261',
'CL3120262','CL3120263','CL3120306','CL3120308','CL3120327','CL3120329','CL3120331','CL3120445','CL3120473','CL3120474','CL3120507','CL3120508','CL3120510','CL3120511','CL0361310','CL0361311',
'CL0361312','CL0361313','CL0361314','CL0361352','CL0361353','CL0361354','CL0361355','CL0361356','CL0380144','CL0380144','CL0380144','CL0380339','CL0381615','CL0381616','CL0381617','CL0381618',
'CL0381619','CL0381620','CL0381621','CL0400123','CL0400123','CL0400123','CL0400124','CL0420341','CL0420341','CL0420341','CL0420342','CL1240646','CL1250101','CL1250103','CL1250118','CL1250120',
'CL1250135','CL1250137','CL1250152','CL1250154')
ORDER BY smsevent.class_code, hassocode

-- nX1

SELECT event_koza.id, event_id, smsevent.name, smsevent.class_code, smsevent.shikenshu, smsevent.curriculum_id, curriculum_code, curriculum_name, smsevent.no_current_student, koza_id, koza_name, koza.common_code, jukoukeitai_code, koza.shikenshu
FROM event_koza INNER JOIN smsevent ON smsevent.id=event_koza.event_id AND smsevent.deleted=0
INNER JOIN curriculum ON curriculum.id=curriculum_id INNER JOIN koza ON event_koza.koza_id=koza.id AND koza.deleted=0
WHERE event_koza.deleted=0 AND smsevent.id IN 
('CL0349835','CL0349836','CL0349838','CL0349844','CL0349879','CL0349880','CL0349882','CL0349888','CL0349901','CL0349907','CL0349908','CL0349909','CL0349910','CL0349911','CL0349912','CL0349913',
'CL0349914','CL0349915','CL0349916','CL0349917','CL0349918','CL0349919','CL0349920','CL0349921','CL0349922','CL0349924','CL0349960','CL0349961','CL0349962','CL0349963','CL0349986','CL0349987',
'CL0349988','CL0349989','CL0349990','CL0349991','CL0349998','CL0349999','CL0361528','CL0361529','CL0361536','CL0361538','CL0361539','CL0367230','CL0367231','CL0367232','CL0367233','CL0367234',
'CL0367235','CL0367236','CL0367237','CL0367238','CL0367239','CL0367240','CL0367241','CL0367242','CL0367581','CL0367582','CL0367591','CL0367592','CL0367593','CL0367594','CL0367595','CL0367596',
'CL0367597','CL0367598','CL0367599','CL0367600','CL0367601','CL0367602','CL0367603','CL0367604','CL0367605','CL0367606','CL0367607','CL0367608','CL0367609','CL0367610','CL0367611','CL0367612',
'CL0367613','CL0367614','CL0367615','CL0367616','CL0367617','CL0367618','CL0367619','CL0367620','CL0367621','CL0367622','CL0367623','CL0367624','CL0367625','CL0367626','CL0367627','CL0367628',
'CL0367629','CL0367630','CL0367631','CL0367632','CL0367633','CL0367634','CL0367635','CL0367636','CL0367637','CL0367638','CL0367639','CL0367640','CL0367641','CL0367642','CL0367643','CL0367644',
'CL0367645','CL0367646','CL0380142','CL0380146','CL0380148','CL0380168','CL0380170','CL0380172','CL0380184','CL0380218','CL0380219','CL0380220','CL0380221','CL0380223','CL0380224','CL0380225',
'CL0380341','CL0380343','CL0380345','CL0380347','CL0380351','CL0380353','CL0380357','CL0380367','CL0380368','CL0380369','CL0380370','CL0380372','CL0380373','CL0380374','CL0380391','CL0380392',
'CL0380393','CL0380669','CL0380670','CL0380671','CL0380672','CL0380674','CL0380675','CL0380676','CL0380773','CL0380774','CL0380775','CL0380776','CL0380777','CL0380778','CL0380779','CL0380781',
'CL0380782','CL0380783','CL0380784','CL0380926','CL0380927','CL0381323','CL0381324','CL0381413','CL0381414','CL0381531','CL0381532','CL0381536','CL0381538','CL0381542','CL0381543','CL0381571',
'CL0381572','CL0381576','CL0381578','CL0381582','CL0381583','CL0381615','CL0381616','CL0381617','CL0381618','CL0381619','CL0381620','CL0381621','CL0381664','CL0381665','CL0381681','CL0381682',
'CL0381694','CL0381695','CL0381735','CL0381736','CL0381785','CL0381786','CL0381835','CL0381836','CL0381852','CL0381853','CL0381854','CL0381855','CL0381885','CL0381886','CL0381931','CL0381932',
'CL0381981','CL0381982','CL0382081','CL0382082','CL0382086','CL0382088','CL0382092','CL0382093','CL0382325','CL0382326','CL0382327','CL0382328','CL0400069','CL0400070','CL0400071','CL0400072',
'CL0400073','CL0400074','CL0400075','CL0400076','CL0400136','CL0400137','CL0400138','CL0400139','CL0400140','CL0400141','CL0400142','CL0400143','CL0400269','CL1250033','CL1250034','CL1250035',
'CL1250036','CL1250037','CL1250052','CL1250053','CL1250054','CL1250055','CL1250056','CL1250069','CL1250070','CL1250071','CL1250072','CL1250073','CL1250086','CL1250087','CL1250088','CL1250089',
'CL1250090','CL1250186','CL1250187','CL1250188','CL1250189','CL1250190','CL1250191','CL1250192','CL1250193','CL1250194','CL1250195','CL1250196','CL1250197','CL1250198','CL1250199','CL1250200',
'CL1250201','CL1250202','CL1250203','CL2192182','CL2192183','CL2192184','CL2192460','CL2192461','CL2192462','CL2200561','CL2200602','CL2201490','CL2201491','CL2201633','CL2201634','CL2201635',
'CL2201643','CL2201644','CL2201645','CL2201698','CL2201699','CL2201798','CL2201799','CL2210001','CL2210002','CL2210003','CL2210004','CL2210005','CL2210006','CL2210007','CL2210008','CL2210009',
'CL2210010','CL2210011','CL2210012','CL2210013','CL2210014','CL2210015','CL2210016','CL2210017','CL2210018','CL2210019','CL2210020','CL2210021','CL2210022','CL2210023','CL2210024','CL2210025',
'CL2210026','CL2210027','CL2210028','CL2210029','CL2210030','CL2210031','CL2210032','CL2210033','CL2210034','CL2210035','CL2210036','CL2210037','CL2210038','CL2210039','CL2210040','CL2210041',
'CL2210042','CL2210043','CL2210044','CL2210045','CL2210046','CL2210047','CL2210048','CL2210049','CL2210050','CL2210051','CL2210052','CL2210053','CL2210054','CL2210055','CL2210057','CL2210058',
'CL2210059','CL2210060','CL2210061','CL2210062','CL2210063','CL2210064','CL2210065','CL2210066','CL2210067','CL2210068','CL2210069','CL2210070','CL2210071','CL2210072','CL2210073','CL2210082',
'CL2210083','CL2210084','CL2210085','CL2210086','CL2210087','CL2210088','CL2210089','CL2210090','CL2210091','CL2210092','CL2210094','CL2210095','CL2210096','CL2210097','CL2210098','CL2210099',
'CL2210100','CL2210101','CL2210102','CL2210103','CL2210104','CL2210105','CL2210106','CL2210107','CL2210108','CL2210109','CL2210110','CL2210111','CL2210112','CL2210113','CL2210114','CL2210115',
'CL2210116','CL2210125','CL2210126','CL2210127','CL2210128','CL2210129','CL2210130','CL2210131','CL2210132','CL2210133','CL2210134','CL2210135','CL2210136','CL2210244','CL2210245','CL2210246',
'CL2210247','CL2210248','CL2210249','CL2210250','CL2210251','CL2210252','CL2210253','CL2210254','CL2210255','CL2210256','CL2210257','CL2210258','CL2210259','CL2210260','CL2210261','CL2210262',
'CL2210263','CL2210264','CL2210266','CL2210268','CL2210269','CL2210270','CL2210271','CL2210272','CL2210273','CL2210274','CL2210275','CL2210276','CL2210277','CL2210278','CL2210279','CL2210280',
'CL2210281','CL2210282','CL2210283','CL2210284','CL2210285','CL2210286','CL2210287','CL2210288','CL2210290','CL2210292','CL2210293','CL2210294','CL2210295','CL2210296','CL2210297','CL2210298',
'CL2210299','CL2210300','CL2210301','CL2210302','CL2210303','CL2210304','CL2210305','CL2210306','CL2210307','CL2210308','CL2210309','CL2210310','CL2210311','CL2210312','CL2210314','CL2210316',
'CL2210317','CL2210318','CL2210319','CL2210320','CL2210321','CL2210322','CL2210323','CL2210324','CL2210325','CL2210326','CL2210327','CL2210328','CL2210329','CL2210330','CL2210331','CL2210332',
'CL2210333','CL2210334','CL2210335','CL2210336','CL2210338','CL2210340','CL2210341','CL2210342','CL2210343','CL2210344','CL2210345','CL2210346','CL2210347','CL2210348','CL2210349','CL2210350',
'CL2210351','CL2210352','CL2210353','CL2210354','CL2210355','CL2210356','CL2210357','CL2210358','CL2210359','CL2210360','CL2210362','CL2210364','CL2210365','CL2210366','CL2210367','CL2210368',
'CL2210369','CL2210370','CL2210371','CL2210372','CL2210373','CL2210374','CL2210375','CL2210376','CL2210377','CL2210378','CL2210379','CL2210380','CL2210381','CL2210382','CL2210383','CL2210384',
'CL2210386','CL2210388','CL2210389','CL2210390','CL2210391','CL2210392','CL2210393','CL2210394','CL2210395','CL2210396','CL2210397','CL2210398','CL2210399','CL2210400','CL2210401','CL2210402',
'CL2210403','CL2210404','CL2210405','CL2210406','CL2210407','CL2210408','CL2210410','CL2210429','CL2210439','CL2210720','CL2210724','CL2210725','CL2210726','CL2210727','CL2210728','CL2210729',
'CL2210730','CL2210731','CL2210732','CL2210733','CL2210734','CL2210735','CL2210736','CL2210737','CL2210738','CL2210739','CL2210740','CL2210741','CL2210742','CL2210743','CL2210744','CL2210745',
'CL2210746','CL2210747','CL2210781','CL2210782','CL2210783','CL2210784','CL2210785','CL2210786','CL2210787','CL2210788','CL2210789','CL2210790','CL2210791','CL2210792','CL2210793','CL2210794',
'CL2210795','CL2210796','CL2210797','CL2210798','CL2210799','CL2210800','CL2210801','CL2210802','CL2210803','CL2210804','CL2210808','CL2210809','CL2210810','CL2210811','CL2210812','CL2210813',
'CL2210814','CL2210815','CL2210816','CL2210817','CL2210818','CL2210819','CL2210820','CL2210821','CL2210822','CL2210823','CL2210951','CL2210952','CL2210953','CL2210954','CL2210955','CL2210956',
'CL2210957','CL2210958','CL2210959','CL2210960','CL2210961','CL2210962','CL2210963','CL2210964','CL2210965','CL2210966','CL2210967','CL2210968','CL2210969','CL2210970','CL2210971','CL2210972',
'CL2210973','CL2210974','CL2210987','CL2210988','CL2210989','CL2210990','CL2210991','CL2210992','CL2210993','CL2210994','CL2210995','CL2210996','CL2210997','CL2210998','CL2210999','CL2211000',
'CL2211001','CL2211002','CL2211003','CL2211004','CL2211005','CL2211006','CL2211007','CL2211008','CL2211021','CL2211022','CL2211023','CL2211024','CL2211025','CL2211026','CL2211027','CL2211028',
'CL2211029','CL2211030','CL2211031','CL2211032','CL2211033','CL2211034','CL2211035','CL2211036','CL2211037','CL2211038','CL2211039','CL2211040','CL2211041','CL2211042','CL2211055','CL2211056',
'CL2211057','CL2211058','CL2211059','CL2211060','CL2211061','CL2211062','CL2211063','CL2211064','CL2211065','CL2211066','CL2211067','CL2211068','CL2211069','CL2211070','CL2211071','CL2211072',
'CL2211073','CL2211074','CL2211075','CL2211076','CL2211089','CL2211090','CL2211091','CL2211092','CL2211093','CL2211094','CL2211095','CL2211096','CL2211097','CL2211098','CL2211099','CL2211100',
'CL2211101','CL2211102','CL2211103','CL2211104','CL2211105','CL2211106','CL2211107','CL2211108','CL2211109','CL2211110','CL2211123','CL2211124','CL2211125','CL2211126','CL2211127','CL2211128',
'CL2211129','CL2211130','CL2211131','CL2211132','CL2211133','CL2211134','CL2211135','CL2211136','CL2211137','CL2211138','CL2211139','CL2211140','CL2211141','CL2211142','CL2211143','CL2211144',
'CL2211157','CL2211158','CL2211159','CL2211160','CL2211161','CL2211162','CL2211163','CL2211164','CL2211165','CL2211166','CL2211167','CL2211168','CL2211169','CL2211170','CL2211171','CL2211172',
'CL2211173','CL2211174','CL2211175','CL2211176','CL2211177','CL2211178','CL2211191','CL2211192','CL2211193','CL2211194','CL2211195','CL2211196','CL2211197','CL2211198','CL2211199','CL2211200',
'CL2211201','CL2211202','CL2211203','CL2211204','CL2211205','CL2211206','CL2211207','CL2211208','CL2211209','CL2211210','CL2211211','CL2211212','CL2211222','CL2211224','CL2211225','CL2211226',
'CL2211227','CL2211228','CL2211229','CL2211230','CL2211231','CL2211232','CL2211233','CL2211234','CL2211235','CL2211236','CL2211237','CL2211238','CL2211239','CL2211240','CL2211241','CL2211242',
'CL2211243','CL2211244','CL2211245','CL2211249','CL2211250','CL2211251','CL2211252','CL2211253','CL2211254','CL2211255','CL2211256','CL2211257','CL2211258','CL2211259','CL2211260','CL2211261',
'CL2211262','CL2211263','CL2211264','CL2220067','CL2220068','CL2220069','CL2220070','CL2220188','CL2220189','CL2220190','CL2220191','CL3120022','CL3120023','CL3120025','CL3120026','CL3120027',
'CL3120029','CL3120030','CL3120031','CL3120090','CL3120160','CL3120199','CL3120218','CL3120237','CL3120266','CL3120287','CL3120306','CL3120325')
ORDER BY smsevent.class_code, koza.common_code, koza_id

SELECT event_contacts.id, event_contacts.event_id, smsevent.name, smsevent.class_code, smsevent.shikenshu, smsevent.curriculum_id, curriculum_code, curriculum_name, event_contacts.contact_id as student_id, idnumber, last_name, first_name, 
	koza_contacts.koza_id, koza.koza_name, koza.common_code, jukoukeitai_code, koza.shikenshu
FROM event_contacts INNER JOIN smsevent ON smsevent.id=event_contacts.event_id AND smsevent.deleted=0
INNER JOIN curriculum ON curriculum.id=curriculum_id INNER JOIN contacts ON event_contacts.contact_id=contacts.id AND contacts.deleted=0
INNER JOIN koza_contacts ON koza_contacts.contact_id=event_contacts.contact_id AND contacts.id=koza_contacts.contact_id AND koza_contacts.deleted=0
INNER JOIN event_koza ON event_koza.koza_id=koza_contacts.koza_id  AND event_koza.event_id=smsevent.id AND event_koza.deleted=0 INNER JOIN koza ON koza.id=koza_contacts.koza_id AND koza.id=event_koza.koza_id AND koza.deleted=0
WHERE event_contacts.deleted=0 AND smsevent.id IN 
('CL0349835','CL0349836','CL0349838','CL0349844','CL0349879','CL0349880','CL0349882','CL0349888','CL0349901','CL0349907','CL0349908','CL0349909','CL0349910','CL0349911','CL0349912','CL0349913',
'CL0349914','CL0349915','CL0349916','CL0349917','CL0349918','CL0349919','CL0349920','CL0349921','CL0349922','CL0349924','CL0349960','CL0349961','CL0349962','CL0349963','CL0349986','CL0349987',
'CL0349988','CL0349989','CL0349990','CL0349991','CL0349998','CL0349999','CL0361528','CL0361529','CL0361536','CL0361538','CL0361539','CL0367230','CL0367231','CL0367232','CL0367233','CL0367234',
'CL0367235','CL0367236','CL0367237','CL0367238','CL0367239','CL0367240','CL0367241','CL0367242','CL0367581','CL0367582','CL0367591','CL0367592','CL0367593','CL0367594','CL0367595','CL0367596',
'CL0367597','CL0367598','CL0367599','CL0367600','CL0367601','CL0367602','CL0367603','CL0367604','CL0367605','CL0367606','CL0367607','CL0367608','CL0367609','CL0367610','CL0367611','CL0367612',
'CL0367613','CL0367614','CL0367615','CL0367616','CL0367617','CL0367618','CL0367619','CL0367620','CL0367621','CL0367622','CL0367623','CL0367624','CL0367625','CL0367626','CL0367627','CL0367628',
'CL0367629','CL0367630','CL0367631','CL0367632','CL0367633','CL0367634','CL0367635','CL0367636','CL0367637','CL0367638','CL0367639','CL0367640','CL0367641','CL0367642','CL0367643','CL0367644',
'CL0367645','CL0367646','CL0380142','CL0380146','CL0380148','CL0380168','CL0380170','CL0380172','CL0380184','CL0380218','CL0380219','CL0380220','CL0380221','CL0380223','CL0380224','CL0380225',
'CL0380341','CL0380343','CL0380345','CL0380347','CL0380351','CL0380353','CL0380357','CL0380367','CL0380368','CL0380369','CL0380370','CL0380372','CL0380373','CL0380374','CL0380391','CL0380392',
'CL0380393','CL0380669','CL0380670','CL0380671','CL0380672','CL0380674','CL0380675','CL0380676','CL0380773','CL0380774','CL0380775','CL0380776','CL0380777','CL0380778','CL0380779','CL0380781',
'CL0380782','CL0380783','CL0380784','CL0380926','CL0380927','CL0381323','CL0381324','CL0381413','CL0381414','CL0381531','CL0381532','CL0381536','CL0381538','CL0381542','CL0381543','CL0381571',
'CL0381572','CL0381576','CL0381578','CL0381582','CL0381583','CL0381615','CL0381616','CL0381617','CL0381618','CL0381619','CL0381620','CL0381621','CL0381664','CL0381665','CL0381681','CL0381682',
'CL0381694','CL0381695','CL0381735','CL0381736','CL0381785','CL0381786','CL0381835','CL0381836','CL0381852','CL0381853','CL0381854','CL0381855','CL0381885','CL0381886','CL0381931','CL0381932',
'CL0381981','CL0381982','CL0382081','CL0382082','CL0382086','CL0382088','CL0382092','CL0382093','CL0382325','CL0382326','CL0382327','CL0382328','CL0400069','CL0400070','CL0400071','CL0400072',
'CL0400073','CL0400074','CL0400075','CL0400076','CL0400136','CL0400137','CL0400138','CL0400139','CL0400140','CL0400141','CL0400142','CL0400143','CL0400269','CL1250033','CL1250034','CL1250035',
'CL1250036','CL1250037','CL1250052','CL1250053','CL1250054','CL1250055','CL1250056','CL1250069','CL1250070','CL1250071','CL1250072','CL1250073','CL1250086','CL1250087','CL1250088','CL1250089',
'CL1250090','CL1250186','CL1250187','CL1250188','CL1250189','CL1250190','CL1250191','CL1250192','CL1250193','CL1250194','CL1250195','CL1250196','CL1250197','CL1250198','CL1250199','CL1250200',
'CL1250201','CL1250202','CL1250203','CL2192182','CL2192183','CL2192184','CL2192460','CL2192461','CL2192462','CL2200561','CL2200602','CL2201490','CL2201491','CL2201633','CL2201634','CL2201635',
'CL2201643','CL2201644','CL2201645','CL2201698','CL2201699','CL2201798','CL2201799','CL2210001','CL2210002','CL2210003','CL2210004','CL2210005','CL2210006','CL2210007','CL2210008','CL2210009',
'CL2210010','CL2210011','CL2210012','CL2210013','CL2210014','CL2210015','CL2210016','CL2210017','CL2210018','CL2210019','CL2210020','CL2210021','CL2210022','CL2210023','CL2210024','CL2210025',
'CL2210026','CL2210027','CL2210028','CL2210029','CL2210030','CL2210031','CL2210032','CL2210033','CL2210034','CL2210035','CL2210036','CL2210037','CL2210038','CL2210039','CL2210040','CL2210041',
'CL2210042','CL2210043','CL2210044','CL2210045','CL2210046','CL2210047','CL2210048','CL2210049','CL2210050','CL2210051','CL2210052','CL2210053','CL2210054','CL2210055','CL2210057','CL2210058',
'CL2210059','CL2210060','CL2210061','CL2210062','CL2210063','CL2210064','CL2210065','CL2210066','CL2210067','CL2210068','CL2210069','CL2210070','CL2210071','CL2210072','CL2210073','CL2210082',
'CL2210083','CL2210084','CL2210085','CL2210086','CL2210087','CL2210088','CL2210089','CL2210090','CL2210091','CL2210092','CL2210094','CL2210095','CL2210096','CL2210097','CL2210098','CL2210099',
'CL2210100','CL2210101','CL2210102','CL2210103','CL2210104','CL2210105','CL2210106','CL2210107','CL2210108','CL2210109','CL2210110','CL2210111','CL2210112','CL2210113','CL2210114','CL2210115',
'CL2210116','CL2210125','CL2210126','CL2210127','CL2210128','CL2210129','CL2210130','CL2210131','CL2210132','CL2210133','CL2210134','CL2210135','CL2210136','CL2210244','CL2210245','CL2210246',
'CL2210247','CL2210248','CL2210249','CL2210250','CL2210251','CL2210252','CL2210253','CL2210254','CL2210255','CL2210256','CL2210257','CL2210258','CL2210259','CL2210260','CL2210261','CL2210262',
'CL2210263','CL2210264','CL2210266','CL2210268','CL2210269','CL2210270','CL2210271','CL2210272','CL2210273','CL2210274','CL2210275','CL2210276','CL2210277','CL2210278','CL2210279','CL2210280',
'CL2210281','CL2210282','CL2210283','CL2210284','CL2210285','CL2210286','CL2210287','CL2210288','CL2210290','CL2210292','CL2210293','CL2210294','CL2210295','CL2210296','CL2210297','CL2210298',
'CL2210299','CL2210300','CL2210301','CL2210302','CL2210303','CL2210304','CL2210305','CL2210306','CL2210307','CL2210308','CL2210309','CL2210310','CL2210311','CL2210312','CL2210314','CL2210316',
'CL2210317','CL2210318','CL2210319','CL2210320','CL2210321','CL2210322','CL2210323','CL2210324','CL2210325','CL2210326','CL2210327','CL2210328','CL2210329','CL2210330','CL2210331','CL2210332',
'CL2210333','CL2210334','CL2210335','CL2210336','CL2210338','CL2210340','CL2210341','CL2210342','CL2210343','CL2210344','CL2210345','CL2210346','CL2210347','CL2210348','CL2210349','CL2210350',
'CL2210351','CL2210352','CL2210353','CL2210354','CL2210355','CL2210356','CL2210357','CL2210358','CL2210359','CL2210360','CL2210362','CL2210364','CL2210365','CL2210366','CL2210367','CL2210368',
'CL2210369','CL2210370','CL2210371','CL2210372','CL2210373','CL2210374','CL2210375','CL2210376','CL2210377','CL2210378','CL2210379','CL2210380','CL2210381','CL2210382','CL2210383','CL2210384',
'CL2210386','CL2210388','CL2210389','CL2210390','CL2210391','CL2210392','CL2210393','CL2210394','CL2210395','CL2210396','CL2210397','CL2210398','CL2210399','CL2210400','CL2210401','CL2210402',
'CL2210403','CL2210404','CL2210405','CL2210406','CL2210407','CL2210408','CL2210410','CL2210429','CL2210439','CL2210720','CL2210724','CL2210725','CL2210726','CL2210727','CL2210728','CL2210729',
'CL2210730','CL2210731','CL2210732','CL2210733','CL2210734','CL2210735','CL2210736','CL2210737','CL2210738','CL2210739','CL2210740','CL2210741','CL2210742','CL2210743','CL2210744','CL2210745',
'CL2210746','CL2210747','CL2210781','CL2210782','CL2210783','CL2210784','CL2210785','CL2210786','CL2210787','CL2210788','CL2210789','CL2210790','CL2210791','CL2210792','CL2210793','CL2210794',
'CL2210795','CL2210796','CL2210797','CL2210798','CL2210799','CL2210800','CL2210801','CL2210802','CL2210803','CL2210804','CL2210808','CL2210809','CL2210810','CL2210811','CL2210812','CL2210813',
'CL2210814','CL2210815','CL2210816','CL2210817','CL2210818','CL2210819','CL2210820','CL2210821','CL2210822','CL2210823','CL2210951','CL2210952','CL2210953','CL2210954','CL2210955','CL2210956',
'CL2210957','CL2210958','CL2210959','CL2210960','CL2210961','CL2210962','CL2210963','CL2210964','CL2210965','CL2210966','CL2210967','CL2210968','CL2210969','CL2210970','CL2210971','CL2210972',
'CL2210973','CL2210974','CL2210987','CL2210988','CL2210989','CL2210990','CL2210991','CL2210992','CL2210993','CL2210994','CL2210995','CL2210996','CL2210997','CL2210998','CL2210999','CL2211000',
'CL2211001','CL2211002','CL2211003','CL2211004','CL2211005','CL2211006','CL2211007','CL2211008','CL2211021','CL2211022','CL2211023','CL2211024','CL2211025','CL2211026','CL2211027','CL2211028',
'CL2211029','CL2211030','CL2211031','CL2211032','CL2211033','CL2211034','CL2211035','CL2211036','CL2211037','CL2211038','CL2211039','CL2211040','CL2211041','CL2211042','CL2211055','CL2211056',
'CL2211057','CL2211058','CL2211059','CL2211060','CL2211061','CL2211062','CL2211063','CL2211064','CL2211065','CL2211066','CL2211067','CL2211068','CL2211069','CL2211070','CL2211071','CL2211072',
'CL2211073','CL2211074','CL2211075','CL2211076','CL2211089','CL2211090','CL2211091','CL2211092','CL2211093','CL2211094','CL2211095','CL2211096','CL2211097','CL2211098','CL2211099','CL2211100',
'CL2211101','CL2211102','CL2211103','CL2211104','CL2211105','CL2211106','CL2211107','CL2211108','CL2211109','CL2211110','CL2211123','CL2211124','CL2211125','CL2211126','CL2211127','CL2211128',
'CL2211129','CL2211130','CL2211131','CL2211132','CL2211133','CL2211134','CL2211135','CL2211136','CL2211137','CL2211138','CL2211139','CL2211140','CL2211141','CL2211142','CL2211143','CL2211144',
'CL2211157','CL2211158','CL2211159','CL2211160','CL2211161','CL2211162','CL2211163','CL2211164','CL2211165','CL2211166','CL2211167','CL2211168','CL2211169','CL2211170','CL2211171','CL2211172',
'CL2211173','CL2211174','CL2211175','CL2211176','CL2211177','CL2211178','CL2211191','CL2211192','CL2211193','CL2211194','CL2211195','CL2211196','CL2211197','CL2211198','CL2211199','CL2211200',
'CL2211201','CL2211202','CL2211203','CL2211204','CL2211205','CL2211206','CL2211207','CL2211208','CL2211209','CL2211210','CL2211211','CL2211212','CL2211222','CL2211224','CL2211225','CL2211226',
'CL2211227','CL2211228','CL2211229','CL2211230','CL2211231','CL2211232','CL2211233','CL2211234','CL2211235','CL2211236','CL2211237','CL2211238','CL2211239','CL2211240','CL2211241','CL2211242',
'CL2211243','CL2211244','CL2211245','CL2211249','CL2211250','CL2211251','CL2211252','CL2211253','CL2211254','CL2211255','CL2211256','CL2211257','CL2211258','CL2211259','CL2211260','CL2211261',
'CL2211262','CL2211263','CL2211264','CL2220067','CL2220068','CL2220069','CL2220070','CL2220188','CL2220189','CL2220190','CL2220191','CL3120022','CL3120023','CL3120025','CL3120026','CL3120027',
'CL3120029','CL3120030','CL3120031','CL3120090','CL3120160','CL3120199','CL3120218','CL3120237','CL3120266','CL3120287','CL3120306','CL3120325')
ORDER BY smsevent.class_code, idnumber

SELECT event_id, smsevent.name, smsevent.class_code, smsevent.shikenshu, smsevent.curriculum_id, curriculum_code, curriculum_name, hassobi
FROM smsevent INNER JOIN curriculum ON curriculum.id=curriculum_id INNER JOIN hassobi ON hassobi.event_id=smsevent.id 
WHERE smsevent.deleted=0 AND smsevent.id IN 
('CL0349835','CL0349836','CL0349838','CL0349844','CL0349879','CL0349880','CL0349882','CL0349888','CL0349901','CL0349907','CL0349908','CL0349909','CL0349910','CL0349911','CL0349912','CL0349913',
'CL0349914','CL0349915','CL0349916','CL0349917','CL0349918','CL0349919','CL0349920','CL0349921','CL0349922','CL0349924','CL0349960','CL0349961','CL0349962','CL0349963','CL0349986','CL0349987',
'CL0349988','CL0349989','CL0349990','CL0349991','CL0349998','CL0349999','CL0361528','CL0361529','CL0361536','CL0361538','CL0361539','CL0367230','CL0367231','CL0367232','CL0367233','CL0367234',
'CL0367235','CL0367236','CL0367237','CL0367238','CL0367239','CL0367240','CL0367241','CL0367242','CL0367581','CL0367582','CL0367591','CL0367592','CL0367593','CL0367594','CL0367595','CL0367596',
'CL0367597','CL0367598','CL0367599','CL0367600','CL0367601','CL0367602','CL0367603','CL0367604','CL0367605','CL0367606','CL0367607','CL0367608','CL0367609','CL0367610','CL0367611','CL0367612',
'CL0367613','CL0367614','CL0367615','CL0367616','CL0367617','CL0367618','CL0367619','CL0367620','CL0367621','CL0367622','CL0367623','CL0367624','CL0367625','CL0367626','CL0367627','CL0367628',
'CL0367629','CL0367630','CL0367631','CL0367632','CL0367633','CL0367634','CL0367635','CL0367636','CL0367637','CL0367638','CL0367639','CL0367640','CL0367641','CL0367642','CL0367643','CL0367644',
'CL0367645','CL0367646','CL0380142','CL0380146','CL0380148','CL0380168','CL0380170','CL0380172','CL0380184','CL0380218','CL0380219','CL0380220','CL0380221','CL0380223','CL0380224','CL0380225',
'CL0380341','CL0380343','CL0380345','CL0380347','CL0380351','CL0380353','CL0380357','CL0380367','CL0380368','CL0380369','CL0380370','CL0380372','CL0380373','CL0380374','CL0380391','CL0380392',
'CL0380393','CL0380669','CL0380670','CL0380671','CL0380672','CL0380674','CL0380675','CL0380676','CL0380773','CL0380774','CL0380775','CL0380776','CL0380777','CL0380778','CL0380779','CL0380781',
'CL0380782','CL0380783','CL0380784','CL0380926','CL0380927','CL0381323','CL0381324','CL0381413','CL0381414','CL0381531','CL0381532','CL0381536','CL0381538','CL0381542','CL0381543','CL0381571',
'CL0381572','CL0381576','CL0381578','CL0381582','CL0381583','CL0381615','CL0381616','CL0381617','CL0381618','CL0381619','CL0381620','CL0381621','CL0381664','CL0381665','CL0381681','CL0381682',
'CL0381694','CL0381695','CL0381735','CL0381736','CL0381785','CL0381786','CL0381835','CL0381836','CL0381852','CL0381853','CL0381854','CL0381855','CL0381885','CL0381886','CL0381931','CL0381932',
'CL0381981','CL0381982','CL0382081','CL0382082','CL0382086','CL0382088','CL0382092','CL0382093','CL0382325','CL0382326','CL0382327','CL0382328','CL0400069','CL0400070','CL0400071','CL0400072',
'CL0400073','CL0400074','CL0400075','CL0400076','CL0400136','CL0400137','CL0400138','CL0400139','CL0400140','CL0400141','CL0400142','CL0400143','CL0400269','CL1250033','CL1250034','CL1250035',
'CL1250036','CL1250037','CL1250052','CL1250053','CL1250054','CL1250055','CL1250056','CL1250069','CL1250070','CL1250071','CL1250072','CL1250073','CL1250086','CL1250087','CL1250088','CL1250089',
'CL1250090','CL1250186','CL1250187','CL1250188','CL1250189','CL1250190','CL1250191','CL1250192','CL1250193','CL1250194','CL1250195','CL1250196','CL1250197','CL1250198','CL1250199','CL1250200',
'CL1250201','CL1250202','CL1250203','CL2192182','CL2192183','CL2192184','CL2192460','CL2192461','CL2192462','CL2200561','CL2200602','CL2201490','CL2201491','CL2201633','CL2201634','CL2201635',
'CL2201643','CL2201644','CL2201645','CL2201698','CL2201699','CL2201798','CL2201799','CL2210001','CL2210002','CL2210003','CL2210004','CL2210005','CL2210006','CL2210007','CL2210008','CL2210009',
'CL2210010','CL2210011','CL2210012','CL2210013','CL2210014','CL2210015','CL2210016','CL2210017','CL2210018','CL2210019','CL2210020','CL2210021','CL2210022','CL2210023','CL2210024','CL2210025',
'CL2210026','CL2210027','CL2210028','CL2210029','CL2210030','CL2210031','CL2210032','CL2210033','CL2210034','CL2210035','CL2210036','CL2210037','CL2210038','CL2210039','CL2210040','CL2210041',
'CL2210042','CL2210043','CL2210044','CL2210045','CL2210046','CL2210047','CL2210048','CL2210049','CL2210050','CL2210051','CL2210052','CL2210053','CL2210054','CL2210055','CL2210057','CL2210058',
'CL2210059','CL2210060','CL2210061','CL2210062','CL2210063','CL2210064','CL2210065','CL2210066','CL2210067','CL2210068','CL2210069','CL2210070','CL2210071','CL2210072','CL2210073','CL2210082',
'CL2210083','CL2210084','CL2210085','CL2210086','CL2210087','CL2210088','CL2210089','CL2210090','CL2210091','CL2210092','CL2210094','CL2210095','CL2210096','CL2210097','CL2210098','CL2210099',
'CL2210100','CL2210101','CL2210102','CL2210103','CL2210104','CL2210105','CL2210106','CL2210107','CL2210108','CL2210109','CL2210110','CL2210111','CL2210112','CL2210113','CL2210114','CL2210115',
'CL2210116','CL2210125','CL2210126','CL2210127','CL2210128','CL2210129','CL2210130','CL2210131','CL2210132','CL2210133','CL2210134','CL2210135','CL2210136','CL2210244','CL2210245','CL2210246',
'CL2210247','CL2210248','CL2210249','CL2210250','CL2210251','CL2210252','CL2210253','CL2210254','CL2210255','CL2210256','CL2210257','CL2210258','CL2210259','CL2210260','CL2210261','CL2210262',
'CL2210263','CL2210264','CL2210266','CL2210268','CL2210269','CL2210270','CL2210271','CL2210272','CL2210273','CL2210274','CL2210275','CL2210276','CL2210277','CL2210278','CL2210279','CL2210280',
'CL2210281','CL2210282','CL2210283','CL2210284','CL2210285','CL2210286','CL2210287','CL2210288','CL2210290','CL2210292','CL2210293','CL2210294','CL2210295','CL2210296','CL2210297','CL2210298',
'CL2210299','CL2210300','CL2210301','CL2210302','CL2210303','CL2210304','CL2210305','CL2210306','CL2210307','CL2210308','CL2210309','CL2210310','CL2210311','CL2210312','CL2210314','CL2210316',
'CL2210317','CL2210318','CL2210319','CL2210320','CL2210321','CL2210322','CL2210323','CL2210324','CL2210325','CL2210326','CL2210327','CL2210328','CL2210329','CL2210330','CL2210331','CL2210332',
'CL2210333','CL2210334','CL2210335','CL2210336','CL2210338','CL2210340','CL2210341','CL2210342','CL2210343','CL2210344','CL2210345','CL2210346','CL2210347','CL2210348','CL2210349','CL2210350',
'CL2210351','CL2210352','CL2210353','CL2210354','CL2210355','CL2210356','CL2210357','CL2210358','CL2210359','CL2210360','CL2210362','CL2210364','CL2210365','CL2210366','CL2210367','CL2210368',
'CL2210369','CL2210370','CL2210371','CL2210372','CL2210373','CL2210374','CL2210375','CL2210376','CL2210377','CL2210378','CL2210379','CL2210380','CL2210381','CL2210382','CL2210383','CL2210384',
'CL2210386','CL2210388','CL2210389','CL2210390','CL2210391','CL2210392','CL2210393','CL2210394','CL2210395','CL2210396','CL2210397','CL2210398','CL2210399','CL2210400','CL2210401','CL2210402',
'CL2210403','CL2210404','CL2210405','CL2210406','CL2210407','CL2210408','CL2210410','CL2210429','CL2210439','CL2210720','CL2210724','CL2210725','CL2210726','CL2210727','CL2210728','CL2210729',
'CL2210730','CL2210731','CL2210732','CL2210733','CL2210734','CL2210735','CL2210736','CL2210737','CL2210738','CL2210739','CL2210740','CL2210741','CL2210742','CL2210743','CL2210744','CL2210745',
'CL2210746','CL2210747','CL2210781','CL2210782','CL2210783','CL2210784','CL2210785','CL2210786','CL2210787','CL2210788','CL2210789','CL2210790','CL2210791','CL2210792','CL2210793','CL2210794',
'CL2210795','CL2210796','CL2210797','CL2210798','CL2210799','CL2210800','CL2210801','CL2210802','CL2210803','CL2210804','CL2210808','CL2210809','CL2210810','CL2210811','CL2210812','CL2210813',
'CL2210814','CL2210815','CL2210816','CL2210817','CL2210818','CL2210819','CL2210820','CL2210821','CL2210822','CL2210823','CL2210951','CL2210952','CL2210953','CL2210954','CL2210955','CL2210956',
'CL2210957','CL2210958','CL2210959','CL2210960','CL2210961','CL2210962','CL2210963','CL2210964','CL2210965','CL2210966','CL2210967','CL2210968','CL2210969','CL2210970','CL2210971','CL2210972',
'CL2210973','CL2210974','CL2210987','CL2210988','CL2210989','CL2210990','CL2210991','CL2210992','CL2210993','CL2210994','CL2210995','CL2210996','CL2210997','CL2210998','CL2210999','CL2211000',
'CL2211001','CL2211002','CL2211003','CL2211004','CL2211005','CL2211006','CL2211007','CL2211008','CL2211021','CL2211022','CL2211023','CL2211024','CL2211025','CL2211026','CL2211027','CL2211028',
'CL2211029','CL2211030','CL2211031','CL2211032','CL2211033','CL2211034','CL2211035','CL2211036','CL2211037','CL2211038','CL2211039','CL2211040','CL2211041','CL2211042','CL2211055','CL2211056',
'CL2211057','CL2211058','CL2211059','CL2211060','CL2211061','CL2211062','CL2211063','CL2211064','CL2211065','CL2211066','CL2211067','CL2211068','CL2211069','CL2211070','CL2211071','CL2211072',
'CL2211073','CL2211074','CL2211075','CL2211076','CL2211089','CL2211090','CL2211091','CL2211092','CL2211093','CL2211094','CL2211095','CL2211096','CL2211097','CL2211098','CL2211099','CL2211100',
'CL2211101','CL2211102','CL2211103','CL2211104','CL2211105','CL2211106','CL2211107','CL2211108','CL2211109','CL2211110','CL2211123','CL2211124','CL2211125','CL2211126','CL2211127','CL2211128',
'CL2211129','CL2211130','CL2211131','CL2211132','CL2211133','CL2211134','CL2211135','CL2211136','CL2211137','CL2211138','CL2211139','CL2211140','CL2211141','CL2211142','CL2211143','CL2211144',
'CL2211157','CL2211158','CL2211159','CL2211160','CL2211161','CL2211162','CL2211163','CL2211164','CL2211165','CL2211166','CL2211167','CL2211168','CL2211169','CL2211170','CL2211171','CL2211172',
'CL2211173','CL2211174','CL2211175','CL2211176','CL2211177','CL2211178','CL2211191','CL2211192','CL2211193','CL2211194','CL2211195','CL2211196','CL2211197','CL2211198','CL2211199','CL2211200',
'CL2211201','CL2211202','CL2211203','CL2211204','CL2211205','CL2211206','CL2211207','CL2211208','CL2211209','CL2211210','CL2211211','CL2211212','CL2211222','CL2211224','CL2211225','CL2211226',
'CL2211227','CL2211228','CL2211229','CL2211230','CL2211231','CL2211232','CL2211233','CL2211234','CL2211235','CL2211236','CL2211237','CL2211238','CL2211239','CL2211240','CL2211241','CL2211242',
'CL2211243','CL2211244','CL2211245','CL2211249','CL2211250','CL2211251','CL2211252','CL2211253','CL2211254','CL2211255','CL2211256','CL2211257','CL2211258','CL2211259','CL2211260','CL2211261',
'CL2211262','CL2211263','CL2211264','CL2220067','CL2220068','CL2220069','CL2220070','CL2220188','CL2220189','CL2220190','CL2220191','CL3120022','CL3120023','CL3120025','CL3120026','CL3120027',
'CL3120029','CL3120030','CL3120031','CL3120090','CL3120160','CL3120199','CL3120218','CL3120237','CL3120266','CL3120287','CL3120306','CL3120325')
ORDER BY smsevent.class_code, hassobi

SELECT event_id, smsevent.name, smsevent.class_code, smsevent.shikenshu, smsevent.curriculum_id, curriculum_code, curriculum_name, hassobi, hassocode, hassocode.id
FROM smsevent INNER JOIN curriculum ON curriculum.id=curriculum_id INNER JOIN hassobi ON hassobi.event_id=smsevent.id 
INNER JOIN hassocode_hassobi ON hassocode_hassobi.hassobi_id=hassobi.id INNER JOIN hassocode ON hassocode_hassobi.hassocode_id=hassocode.id 
WHERE smsevent.deleted=0 AND smsevent.id IN 
('CL0349835','CL0349836','CL0349838','CL0349844','CL0349879','CL0349880','CL0349882','CL0349888','CL0349901','CL0349907','CL0349908','CL0349909','CL0349910','CL0349911','CL0349912','CL0349913',
'CL0349914','CL0349915','CL0349916','CL0349917','CL0349918','CL0349919','CL0349920','CL0349921','CL0349922','CL0349924','CL0349960','CL0349961','CL0349962','CL0349963','CL0349986','CL0349987',
'CL0349988','CL0349989','CL0349990','CL0349991','CL0349998','CL0349999','CL0361528','CL0361529','CL0361536','CL0361538','CL0361539','CL0367230','CL0367231','CL0367232','CL0367233','CL0367234',
'CL0367235','CL0367236','CL0367237','CL0367238','CL0367239','CL0367240','CL0367241','CL0367242','CL0367581','CL0367582','CL0367591','CL0367592','CL0367593','CL0367594','CL0367595','CL0367596',
'CL0367597','CL0367598','CL0367599','CL0367600','CL0367601','CL0367602','CL0367603','CL0367604','CL0367605','CL0367606','CL0367607','CL0367608','CL0367609','CL0367610','CL0367611','CL0367612',
'CL0367613','CL0367614','CL0367615','CL0367616','CL0367617','CL0367618','CL0367619','CL0367620','CL0367621','CL0367622','CL0367623','CL0367624','CL0367625','CL0367626','CL0367627','CL0367628',
'CL0367629','CL0367630','CL0367631','CL0367632','CL0367633','CL0367634','CL0367635','CL0367636','CL0367637','CL0367638','CL0367639','CL0367640','CL0367641','CL0367642','CL0367643','CL0367644',
'CL0367645','CL0367646','CL0380142','CL0380146','CL0380148','CL0380168','CL0380170','CL0380172','CL0380184','CL0380218','CL0380219','CL0380220','CL0380221','CL0380223','CL0380224','CL0380225',
'CL0380341','CL0380343','CL0380345','CL0380347','CL0380351','CL0380353','CL0380357','CL0380367','CL0380368','CL0380369','CL0380370','CL0380372','CL0380373','CL0380374','CL0380391','CL0380392',
'CL0380393','CL0380669','CL0380670','CL0380671','CL0380672','CL0380674','CL0380675','CL0380676','CL0380773','CL0380774','CL0380775','CL0380776','CL0380777','CL0380778','CL0380779','CL0380781',
'CL0380782','CL0380783','CL0380784','CL0380926','CL0380927','CL0381323','CL0381324','CL0381413','CL0381414','CL0381531','CL0381532','CL0381536','CL0381538','CL0381542','CL0381543','CL0381571',
'CL0381572','CL0381576','CL0381578','CL0381582','CL0381583','CL0381615','CL0381616','CL0381617','CL0381618','CL0381619','CL0381620','CL0381621','CL0381664','CL0381665','CL0381681','CL0381682',
'CL0381694','CL0381695','CL0381735','CL0381736','CL0381785','CL0381786','CL0381835','CL0381836','CL0381852','CL0381853','CL0381854','CL0381855','CL0381885','CL0381886','CL0381931','CL0381932',
'CL0381981','CL0381982','CL0382081','CL0382082','CL0382086','CL0382088','CL0382092','CL0382093','CL0382325','CL0382326','CL0382327','CL0382328','CL0400069','CL0400070','CL0400071','CL0400072',
'CL0400073','CL0400074','CL0400075','CL0400076','CL0400136','CL0400137','CL0400138','CL0400139','CL0400140','CL0400141','CL0400142','CL0400143','CL0400269','CL1250033','CL1250034','CL1250035',
'CL1250036','CL1250037','CL1250052','CL1250053','CL1250054','CL1250055','CL1250056','CL1250069','CL1250070','CL1250071','CL1250072','CL1250073','CL1250086','CL1250087','CL1250088','CL1250089',
'CL1250090','CL1250186','CL1250187','CL1250188','CL1250189','CL1250190','CL1250191','CL1250192','CL1250193','CL1250194','CL1250195','CL1250196','CL1250197','CL1250198','CL1250199','CL1250200',
'CL1250201','CL1250202','CL1250203','CL2192182','CL2192183','CL2192184','CL2192460','CL2192461','CL2192462','CL2200561','CL2200602','CL2201490','CL2201491','CL2201633','CL2201634','CL2201635',
'CL2201643','CL2201644','CL2201645','CL2201698','CL2201699','CL2201798','CL2201799','CL2210001','CL2210002','CL2210003','CL2210004','CL2210005','CL2210006','CL2210007','CL2210008','CL2210009',
'CL2210010','CL2210011','CL2210012','CL2210013','CL2210014','CL2210015','CL2210016','CL2210017','CL2210018','CL2210019','CL2210020','CL2210021','CL2210022','CL2210023','CL2210024','CL2210025',
'CL2210026','CL2210027','CL2210028','CL2210029','CL2210030','CL2210031','CL2210032','CL2210033','CL2210034','CL2210035','CL2210036','CL2210037','CL2210038','CL2210039','CL2210040','CL2210041',
'CL2210042','CL2210043','CL2210044','CL2210045','CL2210046','CL2210047','CL2210048','CL2210049','CL2210050','CL2210051','CL2210052','CL2210053','CL2210054','CL2210055','CL2210057','CL2210058',
'CL2210059','CL2210060','CL2210061','CL2210062','CL2210063','CL2210064','CL2210065','CL2210066','CL2210067','CL2210068','CL2210069','CL2210070','CL2210071','CL2210072','CL2210073','CL2210082',
'CL2210083','CL2210084','CL2210085','CL2210086','CL2210087','CL2210088','CL2210089','CL2210090','CL2210091','CL2210092','CL2210094','CL2210095','CL2210096','CL2210097','CL2210098','CL2210099',
'CL2210100','CL2210101','CL2210102','CL2210103','CL2210104','CL2210105','CL2210106','CL2210107','CL2210108','CL2210109','CL2210110','CL2210111','CL2210112','CL2210113','CL2210114','CL2210115',
'CL2210116','CL2210125','CL2210126','CL2210127','CL2210128','CL2210129','CL2210130','CL2210131','CL2210132','CL2210133','CL2210134','CL2210135','CL2210136','CL2210244','CL2210245','CL2210246',
'CL2210247','CL2210248','CL2210249','CL2210250','CL2210251','CL2210252','CL2210253','CL2210254','CL2210255','CL2210256','CL2210257','CL2210258','CL2210259','CL2210260','CL2210261','CL2210262',
'CL2210263','CL2210264','CL2210266','CL2210268','CL2210269','CL2210270','CL2210271','CL2210272','CL2210273','CL2210274','CL2210275','CL2210276','CL2210277','CL2210278','CL2210279','CL2210280',
'CL2210281','CL2210282','CL2210283','CL2210284','CL2210285','CL2210286','CL2210287','CL2210288','CL2210290','CL2210292','CL2210293','CL2210294','CL2210295','CL2210296','CL2210297','CL2210298',
'CL2210299','CL2210300','CL2210301','CL2210302','CL2210303','CL2210304','CL2210305','CL2210306','CL2210307','CL2210308','CL2210309','CL2210310','CL2210311','CL2210312','CL2210314','CL2210316',
'CL2210317','CL2210318','CL2210319','CL2210320','CL2210321','CL2210322','CL2210323','CL2210324','CL2210325','CL2210326','CL2210327','CL2210328','CL2210329','CL2210330','CL2210331','CL2210332',
'CL2210333','CL2210334','CL2210335','CL2210336','CL2210338','CL2210340','CL2210341','CL2210342','CL2210343','CL2210344','CL2210345','CL2210346','CL2210347','CL2210348','CL2210349','CL2210350',
'CL2210351','CL2210352','CL2210353','CL2210354','CL2210355','CL2210356','CL2210357','CL2210358','CL2210359','CL2210360','CL2210362','CL2210364','CL2210365','CL2210366','CL2210367','CL2210368',
'CL2210369','CL2210370','CL2210371','CL2210372','CL2210373','CL2210374','CL2210375','CL2210376','CL2210377','CL2210378','CL2210379','CL2210380','CL2210381','CL2210382','CL2210383','CL2210384',
'CL2210386','CL2210388','CL2210389','CL2210390','CL2210391','CL2210392','CL2210393','CL2210394','CL2210395','CL2210396','CL2210397','CL2210398','CL2210399','CL2210400','CL2210401','CL2210402',
'CL2210403','CL2210404','CL2210405','CL2210406','CL2210407','CL2210408','CL2210410','CL2210429','CL2210439','CL2210720','CL2210724','CL2210725','CL2210726','CL2210727','CL2210728','CL2210729',
'CL2210730','CL2210731','CL2210732','CL2210733','CL2210734','CL2210735','CL2210736','CL2210737','CL2210738','CL2210739','CL2210740','CL2210741','CL2210742','CL2210743','CL2210744','CL2210745',
'CL2210746','CL2210747','CL2210781','CL2210782','CL2210783','CL2210784','CL2210785','CL2210786','CL2210787','CL2210788','CL2210789','CL2210790','CL2210791','CL2210792','CL2210793','CL2210794',
'CL2210795','CL2210796','CL2210797','CL2210798','CL2210799','CL2210800','CL2210801','CL2210802','CL2210803','CL2210804','CL2210808','CL2210809','CL2210810','CL2210811','CL2210812','CL2210813',
'CL2210814','CL2210815','CL2210816','CL2210817','CL2210818','CL2210819','CL2210820','CL2210821','CL2210822','CL2210823','CL2210951','CL2210952','CL2210953','CL2210954','CL2210955','CL2210956',
'CL2210957','CL2210958','CL2210959','CL2210960','CL2210961','CL2210962','CL2210963','CL2210964','CL2210965','CL2210966','CL2210967','CL2210968','CL2210969','CL2210970','CL2210971','CL2210972',
'CL2210973','CL2210974','CL2210987','CL2210988','CL2210989','CL2210990','CL2210991','CL2210992','CL2210993','CL2210994','CL2210995','CL2210996','CL2210997','CL2210998','CL2210999','CL2211000',
'CL2211001','CL2211002','CL2211003','CL2211004','CL2211005','CL2211006','CL2211007','CL2211008','CL2211021','CL2211022','CL2211023','CL2211024','CL2211025','CL2211026','CL2211027','CL2211028',
'CL2211029','CL2211030','CL2211031','CL2211032','CL2211033','CL2211034','CL2211035','CL2211036','CL2211037','CL2211038','CL2211039','CL2211040','CL2211041','CL2211042','CL2211055','CL2211056',
'CL2211057','CL2211058','CL2211059','CL2211060','CL2211061','CL2211062','CL2211063','CL2211064','CL2211065','CL2211066','CL2211067','CL2211068','CL2211069','CL2211070','CL2211071','CL2211072',
'CL2211073','CL2211074','CL2211075','CL2211076','CL2211089','CL2211090','CL2211091','CL2211092','CL2211093','CL2211094','CL2211095','CL2211096','CL2211097','CL2211098','CL2211099','CL2211100',
'CL2211101','CL2211102','CL2211103','CL2211104','CL2211105','CL2211106','CL2211107','CL2211108','CL2211109','CL2211110','CL2211123','CL2211124','CL2211125','CL2211126','CL2211127','CL2211128',
'CL2211129','CL2211130','CL2211131','CL2211132','CL2211133','CL2211134','CL2211135','CL2211136','CL2211137','CL2211138','CL2211139','CL2211140','CL2211141','CL2211142','CL2211143','CL2211144',
'CL2211157','CL2211158','CL2211159','CL2211160','CL2211161','CL2211162','CL2211163','CL2211164','CL2211165','CL2211166','CL2211167','CL2211168','CL2211169','CL2211170','CL2211171','CL2211172',
'CL2211173','CL2211174','CL2211175','CL2211176','CL2211177','CL2211178','CL2211191','CL2211192','CL2211193','CL2211194','CL2211195','CL2211196','CL2211197','CL2211198','CL2211199','CL2211200',
'CL2211201','CL2211202','CL2211203','CL2211204','CL2211205','CL2211206','CL2211207','CL2211208','CL2211209','CL2211210','CL2211211','CL2211212','CL2211222','CL2211224','CL2211225','CL2211226',
'CL2211227','CL2211228','CL2211229','CL2211230','CL2211231','CL2211232','CL2211233','CL2211234','CL2211235','CL2211236','CL2211237','CL2211238','CL2211239','CL2211240','CL2211241','CL2211242',
'CL2211243','CL2211244','CL2211245','CL2211249','CL2211250','CL2211251','CL2211252','CL2211253','CL2211254','CL2211255','CL2211256','CL2211257','CL2211258','CL2211259','CL2211260','CL2211261',
'CL2211262','CL2211263','CL2211264','CL2220067','CL2220068','CL2220069','CL2220070','CL2220188','CL2220189','CL2220190','CL2220191','CL3120022','CL3120023','CL3120025','CL3120026','CL3120027',
'CL3120029','CL3120030','CL3120031','CL3120090','CL3120160','CL3120199','CL3120218','CL3120237','CL3120266','CL3120287','CL3120306','CL3120325')
ORDER BY smsevent.class_code, hassocode

SELECT event_id, smsevent.name, smsevent.class_code, smsevent.shikenshu, smsevent.curriculum_id, curriculum_code, curriculum_name, hassobi, hassocode, hassocode.id, hassocode_buppin.hassocode_id, buppin_id, buppin.buppin_name, hasso_suryo
FROM smsevent INNER JOIN curriculum ON curriculum.id=curriculum_id INNER JOIN hassobi ON hassobi.event_id=smsevent.id INNER JOIN hassocode_hassobi ON hassocode_hassobi.hassobi_id=hassobi.id 
INNER JOIN hassocode ON hassocode_hassobi.hassocode_id=hassocode.id INNER JOIN hassocode_buppin ON hassocode_buppin.hassocode_id=hassocode.id INNER JOIN buppin ON hassocode_buppin.buppin_id=buppin.id
WHERE smsevent.deleted=0 AND smsevent.id IN 
('CL0349835','CL0349836','CL0349838','CL0349844','CL0349879','CL0349880','CL0349882','CL0349888','CL0349901','CL0349907','CL0349908','CL0349909','CL0349910','CL0349911','CL0349912','CL0349913',
'CL0349914','CL0349915','CL0349916','CL0349917','CL0349918','CL0349919','CL0349920','CL0349921','CL0349922','CL0349924','CL0349960','CL0349961','CL0349962','CL0349963','CL0349986','CL0349987',
'CL0349988','CL0349989','CL0349990','CL0349991','CL0349998','CL0349999','CL0361528','CL0361529','CL0361536','CL0361538','CL0361539','CL0367230','CL0367231','CL0367232','CL0367233','CL0367234',
'CL0367235','CL0367236','CL0367237','CL0367238','CL0367239','CL0367240','CL0367241','CL0367242','CL0367581','CL0367582','CL0367591','CL0367592','CL0367593','CL0367594','CL0367595','CL0367596',
'CL0367597','CL0367598','CL0367599','CL0367600','CL0367601','CL0367602','CL0367603','CL0367604','CL0367605','CL0367606','CL0367607','CL0367608','CL0367609','CL0367610','CL0367611','CL0367612',
'CL0367613','CL0367614','CL0367615','CL0367616','CL0367617','CL0367618','CL0367619','CL0367620','CL0367621','CL0367622','CL0367623','CL0367624','CL0367625','CL0367626','CL0367627','CL0367628',
'CL0367629','CL0367630','CL0367631','CL0367632','CL0367633','CL0367634','CL0367635','CL0367636','CL0367637','CL0367638','CL0367639','CL0367640','CL0367641','CL0367642','CL0367643','CL0367644',
'CL0367645','CL0367646','CL0380142','CL0380146','CL0380148','CL0380168','CL0380170','CL0380172','CL0380184','CL0380218','CL0380219','CL0380220','CL0380221','CL0380223','CL0380224','CL0380225',
'CL0380341','CL0380343','CL0380345','CL0380347','CL0380351','CL0380353','CL0380357','CL0380367','CL0380368','CL0380369','CL0380370','CL0380372','CL0380373','CL0380374','CL0380391','CL0380392',
'CL0380393','CL0380669','CL0380670','CL0380671','CL0380672','CL0380674','CL0380675','CL0380676','CL0380773','CL0380774','CL0380775','CL0380776','CL0380777','CL0380778','CL0380779','CL0380781',
'CL0380782','CL0380783','CL0380784','CL0380926','CL0380927','CL0381323','CL0381324','CL0381413','CL0381414','CL0381531','CL0381532','CL0381536','CL0381538','CL0381542','CL0381543','CL0381571',
'CL0381572','CL0381576','CL0381578','CL0381582','CL0381583','CL0381615','CL0381616','CL0381617','CL0381618','CL0381619','CL0381620','CL0381621','CL0381664','CL0381665','CL0381681','CL0381682',
'CL0381694','CL0381695','CL0381735','CL0381736','CL0381785','CL0381786','CL0381835','CL0381836','CL0381852','CL0381853','CL0381854','CL0381855','CL0381885','CL0381886','CL0381931','CL0381932',
'CL0381981','CL0381982','CL0382081','CL0382082','CL0382086','CL0382088','CL0382092','CL0382093','CL0382325','CL0382326','CL0382327','CL0382328','CL0400069','CL0400070','CL0400071','CL0400072',
'CL0400073','CL0400074','CL0400075','CL0400076','CL0400136','CL0400137','CL0400138','CL0400139','CL0400140','CL0400141','CL0400142','CL0400143','CL0400269','CL1250033','CL1250034','CL1250035',
'CL1250036','CL1250037','CL1250052','CL1250053','CL1250054','CL1250055','CL1250056','CL1250069','CL1250070','CL1250071','CL1250072','CL1250073','CL1250086','CL1250087','CL1250088','CL1250089',
'CL1250090','CL1250186','CL1250187','CL1250188','CL1250189','CL1250190','CL1250191','CL1250192','CL1250193','CL1250194','CL1250195','CL1250196','CL1250197','CL1250198','CL1250199','CL1250200',
'CL1250201','CL1250202','CL1250203','CL2192182','CL2192183','CL2192184','CL2192460','CL2192461','CL2192462','CL2200561','CL2200602','CL2201490','CL2201491','CL2201633','CL2201634','CL2201635',
'CL2201643','CL2201644','CL2201645','CL2201698','CL2201699','CL2201798','CL2201799','CL2210001','CL2210002','CL2210003','CL2210004','CL2210005','CL2210006','CL2210007','CL2210008','CL2210009',
'CL2210010','CL2210011','CL2210012','CL2210013','CL2210014','CL2210015','CL2210016','CL2210017','CL2210018','CL2210019','CL2210020','CL2210021','CL2210022','CL2210023','CL2210024','CL2210025',
'CL2210026','CL2210027','CL2210028','CL2210029','CL2210030','CL2210031','CL2210032','CL2210033','CL2210034','CL2210035','CL2210036','CL2210037','CL2210038','CL2210039','CL2210040','CL2210041',
'CL2210042','CL2210043','CL2210044','CL2210045','CL2210046','CL2210047','CL2210048','CL2210049','CL2210050','CL2210051','CL2210052','CL2210053','CL2210054','CL2210055','CL2210057','CL2210058',
'CL2210059','CL2210060','CL2210061','CL2210062','CL2210063','CL2210064','CL2210065','CL2210066','CL2210067','CL2210068','CL2210069','CL2210070','CL2210071','CL2210072','CL2210073','CL2210082',
'CL2210083','CL2210084','CL2210085','CL2210086','CL2210087','CL2210088','CL2210089','CL2210090','CL2210091','CL2210092','CL2210094','CL2210095','CL2210096','CL2210097','CL2210098','CL2210099',
'CL2210100','CL2210101','CL2210102','CL2210103','CL2210104','CL2210105','CL2210106','CL2210107','CL2210108','CL2210109','CL2210110','CL2210111','CL2210112','CL2210113','CL2210114','CL2210115',
'CL2210116','CL2210125','CL2210126','CL2210127','CL2210128','CL2210129','CL2210130','CL2210131','CL2210132','CL2210133','CL2210134','CL2210135','CL2210136','CL2210244','CL2210245','CL2210246',
'CL2210247','CL2210248','CL2210249','CL2210250','CL2210251','CL2210252','CL2210253','CL2210254','CL2210255','CL2210256','CL2210257','CL2210258','CL2210259','CL2210260','CL2210261','CL2210262',
'CL2210263','CL2210264','CL2210266','CL2210268','CL2210269','CL2210270','CL2210271','CL2210272','CL2210273','CL2210274','CL2210275','CL2210276','CL2210277','CL2210278','CL2210279','CL2210280',
'CL2210281','CL2210282','CL2210283','CL2210284','CL2210285','CL2210286','CL2210287','CL2210288','CL2210290','CL2210292','CL2210293','CL2210294','CL2210295','CL2210296','CL2210297','CL2210298',
'CL2210299','CL2210300','CL2210301','CL2210302','CL2210303','CL2210304','CL2210305','CL2210306','CL2210307','CL2210308','CL2210309','CL2210310','CL2210311','CL2210312','CL2210314','CL2210316',
'CL2210317','CL2210318','CL2210319','CL2210320','CL2210321','CL2210322','CL2210323','CL2210324','CL2210325','CL2210326','CL2210327','CL2210328','CL2210329','CL2210330','CL2210331','CL2210332',
'CL2210333','CL2210334','CL2210335','CL2210336','CL2210338','CL2210340','CL2210341','CL2210342','CL2210343','CL2210344','CL2210345','CL2210346','CL2210347','CL2210348','CL2210349','CL2210350',
'CL2210351','CL2210352','CL2210353','CL2210354','CL2210355','CL2210356','CL2210357','CL2210358','CL2210359','CL2210360','CL2210362','CL2210364','CL2210365','CL2210366','CL2210367','CL2210368',
'CL2210369','CL2210370','CL2210371','CL2210372','CL2210373','CL2210374','CL2210375','CL2210376','CL2210377','CL2210378','CL2210379','CL2210380','CL2210381','CL2210382','CL2210383','CL2210384',
'CL2210386','CL2210388','CL2210389','CL2210390','CL2210391','CL2210392','CL2210393','CL2210394','CL2210395','CL2210396','CL2210397','CL2210398','CL2210399','CL2210400','CL2210401','CL2210402',
'CL2210403','CL2210404','CL2210405','CL2210406','CL2210407','CL2210408','CL2210410','CL2210429','CL2210439','CL2210720','CL2210724','CL2210725','CL2210726','CL2210727','CL2210728','CL2210729',
'CL2210730','CL2210731','CL2210732','CL2210733','CL2210734','CL2210735','CL2210736','CL2210737','CL2210738','CL2210739','CL2210740','CL2210741','CL2210742','CL2210743','CL2210744','CL2210745',
'CL2210746','CL2210747','CL2210781','CL2210782','CL2210783','CL2210784','CL2210785','CL2210786','CL2210787','CL2210788','CL2210789','CL2210790','CL2210791','CL2210792','CL2210793','CL2210794',
'CL2210795','CL2210796','CL2210797','CL2210798','CL2210799','CL2210800','CL2210801','CL2210802','CL2210803','CL2210804','CL2210808','CL2210809','CL2210810','CL2210811','CL2210812','CL2210813',
'CL2210814','CL2210815','CL2210816','CL2210817','CL2210818','CL2210819','CL2210820','CL2210821','CL2210822','CL2210823','CL2210951','CL2210952','CL2210953','CL2210954','CL2210955','CL2210956',
'CL2210957','CL2210958','CL2210959','CL2210960','CL2210961','CL2210962','CL2210963','CL2210964','CL2210965','CL2210966','CL2210967','CL2210968','CL2210969','CL2210970','CL2210971','CL2210972',
'CL2210973','CL2210974','CL2210987','CL2210988','CL2210989','CL2210990','CL2210991','CL2210992','CL2210993','CL2210994','CL2210995','CL2210996','CL2210997','CL2210998','CL2210999','CL2211000',
'CL2211001','CL2211002','CL2211003','CL2211004','CL2211005','CL2211006','CL2211007','CL2211008','CL2211021','CL2211022','CL2211023','CL2211024','CL2211025','CL2211026','CL2211027','CL2211028',
'CL2211029','CL2211030','CL2211031','CL2211032','CL2211033','CL2211034','CL2211035','CL2211036','CL2211037','CL2211038','CL2211039','CL2211040','CL2211041','CL2211042','CL2211055','CL2211056',
'CL2211057','CL2211058','CL2211059','CL2211060','CL2211061','CL2211062','CL2211063','CL2211064','CL2211065','CL2211066','CL2211067','CL2211068','CL2211069','CL2211070','CL2211071','CL2211072',
'CL2211073','CL2211074','CL2211075','CL2211076','CL2211089','CL2211090','CL2211091','CL2211092','CL2211093','CL2211094','CL2211095','CL2211096','CL2211097','CL2211098','CL2211099','CL2211100',
'CL2211101','CL2211102','CL2211103','CL2211104','CL2211105','CL2211106','CL2211107','CL2211108','CL2211109','CL2211110','CL2211123','CL2211124','CL2211125','CL2211126','CL2211127','CL2211128',
'CL2211129','CL2211130','CL2211131','CL2211132','CL2211133','CL2211134','CL2211135','CL2211136','CL2211137','CL2211138','CL2211139','CL2211140','CL2211141','CL2211142','CL2211143','CL2211144',
'CL2211157','CL2211158','CL2211159','CL2211160','CL2211161','CL2211162','CL2211163','CL2211164','CL2211165','CL2211166','CL2211167','CL2211168','CL2211169','CL2211170','CL2211171','CL2211172',
'CL2211173','CL2211174','CL2211175','CL2211176','CL2211177','CL2211178','CL2211191','CL2211192','CL2211193','CL2211194','CL2211195','CL2211196','CL2211197','CL2211198','CL2211199','CL2211200',
'CL2211201','CL2211202','CL2211203','CL2211204','CL2211205','CL2211206','CL2211207','CL2211208','CL2211209','CL2211210','CL2211211','CL2211212','CL2211222','CL2211224','CL2211225','CL2211226',
'CL2211227','CL2211228','CL2211229','CL2211230','CL2211231','CL2211232','CL2211233','CL2211234','CL2211235','CL2211236','CL2211237','CL2211238','CL2211239','CL2211240','CL2211241','CL2211242',
'CL2211243','CL2211244','CL2211245','CL2211249','CL2211250','CL2211251','CL2211252','CL2211253','CL2211254','CL2211255','CL2211256','CL2211257','CL2211258','CL2211259','CL2211260','CL2211261',
'CL2211262','CL2211263','CL2211264','CL2220067','CL2220068','CL2220069','CL2220070','CL2220188','CL2220189','CL2220190','CL2220191','CL3120022','CL3120023','CL3120025','CL3120026','CL3120027',
'CL3120029','CL3120030','CL3120031','CL3120090','CL3120160','CL3120199','CL3120218','CL3120237','CL3120266','CL3120287','CL3120306','CL3120325')
ORDER BY smsevent.class_code, hassocode


-- 2014-02-12  

SELECT smsevent.id, smsevent.class_code FROM smsevent, smsevent1 WHERE smsevent.id=smsevent1.id 
SELECT smsevent.id, smsevent.class_code FROM smsevent, smsevent2 WHERE smsevent.id=smsevent2.id 
SELECT smsevent.id, smsevent.class_code FROM smsevent, smsevent3 WHERE smsevent.id=smsevent3.id 
SELECT smsevent.id, smsevent.class_code FROM smsevent, smsevent4 WHERE smsevent.id=smsevent4.id 
SELECT smsevent.id, smsevent.class_code FROM smsevent, smsevent5 WHERE smsevent.id=smsevent5.id 
SELECT smsevent.id, smsevent.class_code FROM smsevent, smsevent6 WHERE smsevent.id=smsevent6.id 
SELECT smsevent.id, smsevent.class_code FROM smsevent, smsevent7 WHERE smsevent.id=smsevent7.id 

UPDATE smsevent, smsevent1 SET 
	smsevent.class_code=smsevent1.class_code, 
	smsevent.name=smsevent1.name, 
	smsevent.kamoku=smsevent1.kamoku, 
	smsevent.net_haishin_umu=smsevent1.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent1.net_haishin_kigen, 
	smsevent.web_de_doriru_taisho=smsevent1.web_de_doriru_taisho, 
	smsevent.kaikonen=smsevent1.kaikonen, 
	smsevent.kozabunrui=smsevent1.kozabunrui, 
	smsevent.common_code_a=smsevent1.common_code_a, 
	smsevent.common_code_b=smsevent1.common_code_b										
WHERE smsevent.id=smsevent1.id ; 
UPDATE smsevent, smsevent2 SET 
	smsevent.class_code=smsevent2.class_code, 
	smsevent.name=smsevent2.name, 
	smsevent.class_teacher=smsevent2.class_teacher, 
	smsevent.teiin=smsevent2.teiin, 
	smsevent.kamoku=smsevent2.kamoku, 
	smsevent.net_haishin_umu=smsevent2.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent2.net_haishin_kigen, 
	smsevent.web_de_doriru_taisho=smsevent2.web_de_doriru_taisho, 
	smsevent.kaikonen=smsevent2.kaikonen, 
	smsevent.kozabunrui=smsevent2.kozabunrui, 
	smsevent.common_code_a=smsevent2.common_code_a, 
	smsevent.common_code_b=smsevent2.common_code_b										
WHERE smsevent.id=smsevent2.id ; 
UPDATE smsevent, smsevent3 SET 
	smsevent.class_code=smsevent3.class_code, 
	smsevent.name=smsevent3.name, 
	smsevent.class_teacher=smsevent3.class_teacher, 
	smsevent.net_haishin_umu=smsevent3.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent3.net_haishin_kigen, 
	smsevent.kozabunrui=smsevent3.kozabunrui, 
	smsevent.common_code_a=smsevent3.common_code_a, 
	smsevent.common_code_b=smsevent3.common_code_b										
WHERE smsevent.id=smsevent3.id ; 
UPDATE smsevent, smsevent4 SET 
	smsevent.class_code=smsevent4.class_code, 
	smsevent.name=smsevent4.name, 
	smsevent.class_teacher=smsevent4.class_teacher, 
	smsevent.teiin=smsevent4.teiin, 
	smsevent.kamoku=smsevent4.kamoku, 
	smsevent.net_haishin_umu=smsevent4.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent4.net_haishin_kigen, 
	smsevent.web_de_doriru_taisho=smsevent4.web_de_doriru_taisho, 
	smsevent.kaikonen=smsevent4.kaikonen, 
	smsevent.kozabunrui=smsevent4.kozabunrui, 
	smsevent.common_code_a=smsevent4.common_code_a, 
	smsevent.common_code_b=smsevent4.common_code_b										
WHERE smsevent.id=smsevent4.id ; 
UPDATE smsevent, smsevent5 SET 
	smsevent.class_code=smsevent5.class_code, 
	smsevent.name=smsevent5.name, 
	smsevent.class_teacher=smsevent5.class_teacher, 
	smsevent.kamoku=smsevent5.kamoku, 
	smsevent.net_haishin_umu=smsevent5.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent5.net_haishin_kigen, 
	smsevent.web_de_doriru_taisho=smsevent5.web_de_doriru_taisho										
WHERE smsevent.id=smsevent5.id ; 
UPDATE smsevent, smsevent6 SET 
	smsevent.class_code=smsevent6.class_code, 
	smsevent.name=smsevent6.name, 
	smsevent.teiin=smsevent6.teiin, 
	smsevent.kamoku=smsevent6.kamoku, 
	smsevent.net_haishin_umu=smsevent6.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent6.net_haishin_kigen, 
	smsevent.web_de_doriru_taisho=smsevent6.web_de_doriru_taisho, 
	smsevent.kaikonen=smsevent6.kaikonen, 
	smsevent.kozabunrui=smsevent6.kozabunrui, 
	smsevent.common_code_a=smsevent6.common_code_a, 
	smsevent.common_code_b=smsevent6.common_code_b										
WHERE smsevent.id=smsevent6.id ; 
UPDATE smsevent, smsevent7 SET 
	smsevent.class_code=smsevent7.class_code, 
	smsevent.name=smsevent7.name, 
	smsevent.teiin=smsevent7.teiin, 
	smsevent.kamoku=smsevent7.kamoku, 
	smsevent.net_haishin_umu=smsevent7.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent7.net_haishin_kigen, 
	smsevent.web_de_doriru_taisho=smsevent7.web_de_doriru_taisho, 
	smsevent.kaikonen=smsevent7.kaikonen, 
	smsevent.kozabunrui=smsevent7.kozabunrui, 
	smsevent.common_code_a=smsevent7.common_code_a, 
	smsevent.common_code_b=smsevent7.common_code_b										
WHERE smsevent.id=smsevent7.id ; 

-- after update on test site 

UPDATE smsevent, smsevent1 SET 
	smsevent.class_code=smsevent1.class_code, 
	smsevent.name=smsevent1.name, 
	smsevent.kamoku=smsevent1.kamoku, 
	smsevent.net_haishin_umu=smsevent1.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent1.net_haishin_kigen, 
	smsevent.web_de_doriru_taisho=smsevent1.web_de_doriru_taisho, 
	smsevent.kaikonen=smsevent1.kaikonen, 
	smsevent.kozabunrui=smsevent1.kozabunrui, 
	smsevent.common_code_a=smsevent1.common_code_a, 
	smsevent.common_code_b=smsevent1.common_code_b										
WHERE smsevent.id=smsevent1.id ;# Affected rows: 31
 
UPDATE smsevent, smsevent2 SET 
	smsevent.class_code=smsevent2.class_code, 
	smsevent.name=smsevent2.name, 
	smsevent.class_teacher=smsevent2.class_teacher, 
	smsevent.teiin=smsevent2.teiin, 
	smsevent.kamoku=smsevent2.kamoku, 
	smsevent.net_haishin_umu=smsevent2.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent2.net_haishin_kigen, 
	smsevent.web_de_doriru_taisho=smsevent2.web_de_doriru_taisho, 
	smsevent.kaikonen=smsevent2.kaikonen, 
	smsevent.kozabunrui=smsevent2.kozabunrui, 
	smsevent.common_code_a=smsevent2.common_code_a, 
	smsevent.common_code_b=smsevent2.common_code_b										
WHERE smsevent.id=smsevent2.id ;# Affected rows: 38
 
UPDATE smsevent, smsevent3 SET 
	smsevent.class_code=smsevent3.class_code, 
	smsevent.name=smsevent3.name, 
	smsevent.class_teacher=smsevent3.class_teacher, 
	smsevent.net_haishin_umu=smsevent3.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent3.net_haishin_kigen, 
	smsevent.kozabunrui=smsevent3.kozabunrui, 
	smsevent.common_code_a=smsevent3.common_code_a, 
	smsevent.common_code_b=smsevent3.common_code_b										
WHERE smsevent.id=smsevent3.id ;# Affected rows: 16
 
UPDATE smsevent, smsevent4 SET 
	smsevent.class_code=smsevent4.class_code, 
	smsevent.name=smsevent4.name, 
	smsevent.class_teacher=smsevent4.class_teacher, 
	smsevent.teiin=smsevent4.teiin, 
	smsevent.kamoku=smsevent4.kamoku, 
	smsevent.net_haishin_umu=smsevent4.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent4.net_haishin_kigen, 
	smsevent.web_de_doriru_taisho=smsevent4.web_de_doriru_taisho, 
	smsevent.kaikonen=smsevent4.kaikonen, 
	smsevent.kozabunrui=smsevent4.kozabunrui, 
	smsevent.common_code_a=smsevent4.common_code_a, 
	smsevent.common_code_b=smsevent4.common_code_b										
WHERE smsevent.id=smsevent4.id ;# Affected rows: 34
 
UPDATE smsevent, smsevent5 SET 
	smsevent.class_code=smsevent5.class_code, 
	smsevent.name=smsevent5.name, 
	smsevent.class_teacher=smsevent5.class_teacher, 
	smsevent.kamoku=smsevent5.kamoku, 
	smsevent.net_haishin_umu=smsevent5.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent5.net_haishin_kigen, 
	smsevent.web_de_doriru_taisho=smsevent5.web_de_doriru_taisho										
WHERE smsevent.id=smsevent5.id ;# Affected rows: 30
 
UPDATE smsevent, smsevent6 SET 
	smsevent.class_code=smsevent6.class_code, 
	smsevent.name=smsevent6.name, 
	smsevent.teiin=smsevent6.teiin, 
	smsevent.kamoku=smsevent6.kamoku, 
	smsevent.net_haishin_umu=smsevent6.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent6.net_haishin_kigen, 
	smsevent.web_de_doriru_taisho=smsevent6.web_de_doriru_taisho, 
	smsevent.kaikonen=smsevent6.kaikonen, 
	smsevent.kozabunrui=smsevent6.kozabunrui, 
	smsevent.common_code_a=smsevent6.common_code_a, 
	smsevent.common_code_b=smsevent6.common_code_b										
WHERE smsevent.id=smsevent6.id ;# Affected rows: 158
 
UPDATE smsevent, smsevent7 SET 
	smsevent.class_code=smsevent7.class_code, 
	smsevent.name=smsevent7.name, 
	smsevent.teiin=smsevent7.teiin, 
	smsevent.kamoku=smsevent7.kamoku, 
	smsevent.net_haishin_umu=smsevent7.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent7.net_haishin_kigen, 
	smsevent.web_de_doriru_taisho=smsevent7.web_de_doriru_taisho, 
	smsevent.kaikonen=smsevent7.kaikonen, 
	smsevent.kozabunrui=smsevent7.kozabunrui, 
	smsevent.common_code_a=smsevent7.common_code_a, 
	smsevent.common_code_b=smsevent7.common_code_b										
WHERE smsevent.id=smsevent7.id ;# Affected rows: 212

-- curriculum

CREATE TABLE IF NOT EXISTS `curriculum1` (
  `id` char(36) NOT NULL,
  `curriculum_code` varchar(50) DEFAULT NULL,
  `curriculum_name` varchar(100) DEFAULT NULL,
  `kaikonen` varchar(5) DEFAULT NULL,
  `kozabunrui` varchar(36) DEFAULT NULL,
  `common_code_a` varchar(36) DEFAULT NULL,
  `common_code_b` varchar(36) DEFAULT NULL,
  `shikenshu` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `curriculum_code` (`curriculum_code`),
  KEY `common_code_a` (`common_code_a`),
  KEY `common_code_b` (`common_code_b`),
  KEY `curriculum_name` (`curriculum_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SELECT curriculum.id, curriculum.curriculum_code FROM curriculum, curriculum1 WHERE curriculum.id=curriculum1.id ;

UPDATE curriculum, curriculum1 SET 
	curriculum.curriculum_code=curriculum1.curriculum_code, 
	curriculum.curriculum_name=curriculum1.curriculum_name, 
	curriculum.kaikonen=curriculum1.kaikonen, 
	curriculum.kozabunrui=curriculum1.kozabunrui, 
	curriculum.common_code_a=curriculum1.common_code_a, 
	curriculum.common_code_b=curriculum1.common_code_b, 
	curriculum.shikenshu=curriculum1.shikenshu
WHERE curriculum.id=curriculum1.id ; 


-- id	class_code	name	teiin	kamoku	net_haishin_umu	net_haishin_kigen	web_de_doriru_taisho	kaikonen	kozabunrui	common_code_a	common_code_b

CREATE TABLE IF NOT EXISTS `smsevent8` (
  `id` char(36) NOT NULL,
  `class_code` varchar(100) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `teiin` int(11) DEFAULT NULL,
  `kamoku` varchar(100) DEFAULT NULL,
  `net_haishin_umu` varchar(5) DEFAULT NULL,
  `net_haishin_kigen` date DEFAULT NULL,
  `web_de_doriru_taisho` varchar(5) DEFAULT NULL,
  `kaikonen` varchar(5) DEFAULT NULL,
  `kozabunrui` varchar(36) DEFAULT NULL,
  `common_code_a` varchar(36) DEFAULT NULL,
  `common_code_b` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `class_code` (`class_code`),
  KEY `kamoku` (`kamoku`),
  KEY `common_code_a` (`common_code_a`),
  KEY `common_code_b` (`common_code_b`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8  ;

SELECT smsevent.id, smsevent.class_code FROM smsevent, smsevent8 WHERE smsevent.id=smsevent8.id 

UPDATE smsevent, smsevent8 SET 
	smsevent.class_code=smsevent8.class_code, 
	smsevent.name=smsevent8.name, 
	smsevent.teiin=smsevent8.teiin, 
	smsevent.kamoku=smsevent8.kamoku, 
	smsevent.net_haishin_umu=smsevent8.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent8.net_haishin_kigen, 
	smsevent.web_de_doriru_taisho=smsevent8.web_de_doriru_taisho, 
	smsevent.kaikonen=smsevent8.kaikonen, 
	smsevent.kozabunrui=smsevent8.kozabunrui, 
	smsevent.common_code_a=smsevent8.common_code_a, 
	smsevent.common_code_b=smsevent8.common_code_b										
WHERE smsevent.id=smsevent8.id ; 

-- koza update both on test and honban

CREATE TABLE IF NOT EXISTS `koza1` (
  `id` char(36) NOT NULL,
  `common_code` varchar(100) DEFAULT NULL,
  `jukoukeitai_code` varchar(100) DEFAULT NULL,
  `koza_name` varchar(100) DEFAULT NULL,
  `jukoukeitai_name` varchar(100) DEFAULT NULL, 
  `shikenshu` varchar(5) DEFAULT NULL,
  `jukoukeitai` varchar(36) DEFAULT NULL,
  `common_code_a` varchar(36) DEFAULT NULL,
  `common_code_b` varchar(36) DEFAULT NULL,
  `kozabunrui` varchar(36) DEFAULT NULL,
  `ki` varchar(36) DEFAULT NULL,
  `teacher_name` varchar(36) DEFAULT NULL,
  `taishou_status` varchar(5) DEFAULT NULL,
  `kaikonen` varchar(5) DEFAULT NULL,
  `mokuhyonen` varchar(5) DEFAULT NULL,
  `koza_start_date` date DEFAULT NULL,
  `koza_end_date` date DEFAULT NULL,
  `shikenbi` date DEFAULT NULL,
  `if_code` varchar(36) DEFAULT NULL,
  `hanbaikahi` varchar(36) DEFAULT NULL,
  `kouza_kingaku` double DEFAULT NULL,
  `shohin_kobestu_number` int(11) DEFAULT NULL,
  `csv_export_date` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `common_code` (`common_code`),
  KEY `common_code_a` (`common_code_a`),
  KEY `common_code_b` (`common_code_b`),
  KEY `koza_start_date` (`koza_start_date`),
  KEY `koza_end_date` (`koza_end_date`),
  KEY `shikenbi` (`shikenbi`),
  KEY `csv_export_date` (`csv_export_date`),
  KEY `koza_name` (`koza_name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

SELECT koza.id, koza.common_code FROM koza, koza1 WHERE koza.id=koza1.id 

UPDATE koza, koza1 SET 
	koza.common_code=koza1.common_code, 
	koza.jukoukeitai_code=koza1.jukoukeitai_code, 
	koza.koza_name=koza1.koza_name, 
	koza.jukoukeitai_name=koza1.jukoukeitai_name, 
	koza.shikenshu=koza1.shikenshu, 
	koza.jukoukeitai=koza1.jukoukeitai, 
	koza.common_code_a=koza1.common_code_a, 
	koza.common_code_b=koza1.common_code_b, 
	koza.kozabunrui=koza1.kozabunrui, 		
	koza.ki=koza1.ki, 
	koza.teacher_name=koza1.teacher_name, 
	koza.taishou_status=koza1.taishou_status, 
	koza.kaikonen=koza1.kaikonen, 		
	koza.mokuhyonen=koza1.mokuhyonen, 				
	koza.koza_start_date=koza1.koza_start_date, 				
	koza.koza_end_date=koza1.koza_end_date, 				
	koza.shikenbi=koza1.shikenbi, 				
	koza.if_code=koza1.if_code, 				
	koza.hanbaikahi=koza1.hanbaikahi, 				
	koza.kouza_kingaku=koza1.kouza_kingaku, 				
	koza.shohin_kobestu_number=koza1.shohin_kobestu_number, 				
	koza.csv_export_date=koza1.csv_export_date 						
WHERE koza.id=koza1.id ; 


-- 2014-02-13 to be done on itojuku honban (tables are already copied)

UPDATE smsevent, smsevent1 SET 
	smsevent.class_code=smsevent1.class_code, 
	smsevent.name=smsevent1.name, 
	smsevent.kamoku=smsevent1.kamoku, 
	smsevent.net_haishin_umu=smsevent1.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent1.net_haishin_kigen, 
	smsevent.web_de_doriru_taisho=smsevent1.web_de_doriru_taisho, 
	smsevent.kaikonen=smsevent1.kaikonen, 
	smsevent.kozabunrui=smsevent1.kozabunrui, 
	smsevent.common_code_a=smsevent1.common_code_a, 
	smsevent.common_code_b=smsevent1.common_code_b										
WHERE smsevent.id=smsevent1.id ; 
UPDATE smsevent, smsevent2 SET 
	smsevent.class_code=smsevent2.class_code, 
	smsevent.name=smsevent2.name, 
	smsevent.class_teacher=smsevent2.class_teacher, 
	smsevent.teiin=smsevent2.teiin, 
	smsevent.kamoku=smsevent2.kamoku, 
	smsevent.net_haishin_umu=smsevent2.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent2.net_haishin_kigen, 
	smsevent.web_de_doriru_taisho=smsevent2.web_de_doriru_taisho, 
	smsevent.kaikonen=smsevent2.kaikonen, 
	smsevent.kozabunrui=smsevent2.kozabunrui, 
	smsevent.common_code_a=smsevent2.common_code_a, 
	smsevent.common_code_b=smsevent2.common_code_b										
WHERE smsevent.id=smsevent2.id ; 
UPDATE smsevent, smsevent3 SET 
	smsevent.class_code=smsevent3.class_code, 
	smsevent.name=smsevent3.name, 
	smsevent.class_teacher=smsevent3.class_teacher, 
	smsevent.net_haishin_umu=smsevent3.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent3.net_haishin_kigen, 
	smsevent.kozabunrui=smsevent3.kozabunrui, 
	smsevent.common_code_a=smsevent3.common_code_a, 
	smsevent.common_code_b=smsevent3.common_code_b										
WHERE smsevent.id=smsevent3.id ; 
UPDATE smsevent, smsevent4 SET 
	smsevent.class_code=smsevent4.class_code, 
	smsevent.name=smsevent4.name, 
	smsevent.class_teacher=smsevent4.class_teacher, 
	smsevent.teiin=smsevent4.teiin, 
	smsevent.kamoku=smsevent4.kamoku, 
	smsevent.net_haishin_umu=smsevent4.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent4.net_haishin_kigen, 
	smsevent.web_de_doriru_taisho=smsevent4.web_de_doriru_taisho, 
	smsevent.kaikonen=smsevent4.kaikonen, 
	smsevent.kozabunrui=smsevent4.kozabunrui, 
	smsevent.common_code_a=smsevent4.common_code_a, 
	smsevent.common_code_b=smsevent4.common_code_b										
WHERE smsevent.id=smsevent4.id ; 
UPDATE smsevent, smsevent5 SET 
	smsevent.class_code=smsevent5.class_code, 
	smsevent.name=smsevent5.name, 
	smsevent.class_teacher=smsevent5.class_teacher, 
	smsevent.kamoku=smsevent5.kamoku, 
	smsevent.net_haishin_umu=smsevent5.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent5.net_haishin_kigen, 
	smsevent.web_de_doriru_taisho=smsevent5.web_de_doriru_taisho										
WHERE smsevent.id=smsevent5.id ; 
UPDATE smsevent, smsevent6 SET 
	smsevent.class_code=smsevent6.class_code, 
	smsevent.name=smsevent6.name, 
	smsevent.teiin=smsevent6.teiin, 
	smsevent.kamoku=smsevent6.kamoku, 
	smsevent.net_haishin_umu=smsevent6.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent6.net_haishin_kigen, 
	smsevent.web_de_doriru_taisho=smsevent6.web_de_doriru_taisho, 
	smsevent.kaikonen=smsevent6.kaikonen, 
	smsevent.kozabunrui=smsevent6.kozabunrui, 
	smsevent.common_code_a=smsevent6.common_code_a, 
	smsevent.common_code_b=smsevent6.common_code_b										
WHERE smsevent.id=smsevent6.id ; 
UPDATE smsevent, smsevent7 SET 
	smsevent.class_code=smsevent7.class_code, 
	smsevent.name=smsevent7.name, 
	smsevent.teiin=smsevent7.teiin, 
	smsevent.kamoku=smsevent7.kamoku, 
	smsevent.net_haishin_umu=smsevent7.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent7.net_haishin_kigen, 
	smsevent.web_de_doriru_taisho=smsevent7.web_de_doriru_taisho, 
	smsevent.kaikonen=smsevent7.kaikonen, 
	smsevent.kozabunrui=smsevent7.kozabunrui, 
	smsevent.common_code_a=smsevent7.common_code_a, 
	smsevent.common_code_b=smsevent7.common_code_b										
WHERE smsevent.id=smsevent7.id ; 
UPDATE smsevent, smsevent8 SET 
	smsevent.class_code=smsevent8.class_code, 
	smsevent.name=smsevent8.name, 
	smsevent.teiin=smsevent8.teiin, 
	smsevent.kamoku=smsevent8.kamoku, 
	smsevent.net_haishin_umu=smsevent8.net_haishin_umu, 
	smsevent.net_haishin_kigen=smsevent8.net_haishin_kigen, 
	smsevent.web_de_doriru_taisho=smsevent8.web_de_doriru_taisho, 
	smsevent.kaikonen=smsevent8.kaikonen, 
	smsevent.kozabunrui=smsevent8.kozabunrui, 
	smsevent.common_code_a=smsevent8.common_code_a, 
	smsevent.common_code_b=smsevent8.common_code_b										
WHERE smsevent.id=smsevent8.id ; 

UPDATE curriculum, curriculum1 SET 
	curriculum.curriculum_code=curriculum1.curriculum_code, 
	curriculum.curriculum_name=curriculum1.curriculum_name, 
	curriculum.kaikonen=curriculum1.kaikonen, 
	curriculum.kozabunrui=curriculum1.kozabunrui, 
	curriculum.common_code_a=curriculum1.common_code_a, 
	curriculum.common_code_b=curriculum1.common_code_b, 
	curriculum.shikenshu=curriculum1.shikenshu
WHERE curriculum.id=curriculum1.id ; 



-- 2014-02-21  postal code hyphen removal
SELECT id, last_name, first_name, date_entered, date_modified, modified_user_id, primary_address_postalcode,  CONCAT(SUBSTRING(primary_address_postalcode, 1, 3), SUBSTRING(primary_address_postalcode, 5, 4))
FROM `contacts` WHERE SUBSTRING(primary_address_postalcode, 4, 1)='-'  -- 2,028 on honban

SELECT id, last_name, first_name, date_entered, date_modified, modified_user_id, alt_address_postalcode,  CONCAT(SUBSTRING(alt_address_postalcode, 1, 3), SUBSTRING(alt_address_postalcode, 5, 4))
FROM `contacts` WHERE SUBSTRING(alt_address_postalcode, 4, 1)='-'  -- 102 on honban

UPDATE contacts SET primary_address_postalcode= CONCAT(SUBSTRING(primary_address_postalcode, 1, 3), SUBSTRING(primary_address_postalcode, 5, 4)) WHERE SUBSTRING(primary_address_postalcode, 4, 1)='-' ;  -- 1,903 rows in test3
UPDATE contacts SET alt_address_postalcode= CONCAT(SUBSTRING(alt_address_postalcode, 1, 3), SUBSTRING(alt_address_postalcode, 5, 4)) WHERE SUBSTRING(alt_address_postalcode, 4, 1)='-' ;  -- 90 rows in test3

UPDATE contacts SET primary_address_postalcode= CONCAT(SUBSTRING(primary_address_postalcode, 1, 3), SUBSTRING(primary_address_postalcode, 5, 4)) WHERE SUBSTRING(primary_address_postalcode, 4, 1)='-' ;  
UPDATE contacts SET alt_address_postalcode= CONCAT(SUBSTRING(alt_address_postalcode, 1, 3), SUBSTRING(alt_address_postalcode, 5, 4)) WHERE SUBSTRING(alt_address_postalcode, 4, 1)='-' ;  

-- 2014-02-24  email address update
SELECT contacts_opt.id, contacts.email1, contacts_opt.email1, contacts.email2, contacts_opt.email2 FROM contacts_opt LEFT JOIN contacts ON contacts.id=contacts_opt.id WHERE contacts.id IS NULL

SELECT contacts.email1, contacts_opt.email1, contacts.email2, contacts_opt.email2 FROM contacts, contacts_opt WHERE contacts.id=contacts_opt.id

UPDATE contacts, contacts_opt SET contacts.email1=contacts_opt.email1, contacts.email2=contacts_opt.email2 WHERE contacts.id=contacts_opt.id; 

-- 2014-02-25  university update 

CREATE TABLE IF NOT EXISTS `contacts_un` (
  `id` char(36) NOT NULL,
  `university` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SELECT contacts.id, contacts.university, contacts_un.university FROM contacts, contacts_un WHERE contacts.id=contacts_un.id AND contacts.university!=contacts_un.university

SELECT contacts_un.id, contacts.university, contacts_un.university FROM contacts_un LEFT JOIN contacts ON contacts.id=contacts_un.id WHERE contacts.id IS NULL 

UPDATE contacts, contacts_un SET contacts.university=contacts_un.university WHERE contacts.id=contacts_un.id 

-- 2014-02-28 


CREATE TABLE IF NOT EXISTS `kyoikukunren_contacs_upd` (
  `id` char(36) NOT NULL,
  `warimashi_sentaku` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SELECT kyoikukunren_contacs.warimashi_sentaku, kyoikukunren_contacs_upd.warimashi_sentaku FROM kyoikukunren_contacs, kyoikukunren_contacs_upd WHERE kyoikukunren_contacs.id=kyoikukunren_contacs_upd.id

UPDATE kyoikukunren_contacs, kyoikukunren_contacs_upd SET kyoikukunren_contacs.warimashi_sentaku=kyoikukunren_contacs_upd.warimashi_sentaku WHERE kyoikukunren_contacs.id=kyoikukunren_contacs_upd.id