SELECT * FROM `contacts` WHERE `date_entered` = '2012-03-27 11:40:08' ORDER BY idnumber

DELETE FROM `contacts` WHERE `date_entered` = '2012-03-27 11:40:08' AND smsgroup='teacher' ;


SELECT * FROM `accounts` WHERE `date_modified` = '2012-03-27 11:21:23'


CREATE TABLE IF NOT EXISTS `abcd` (
  `account_id` varchar(36) DEFAULT NULL,
  `account_name` varchar(255) DEFAULT NULL,
  `account_eng` varchar(255) DEFAULT NULL,
  `gender1` varchar(32) DEFAULT NULL,
  `last_name1` varchar(128) DEFAULT NULL,
  `gender2` varchar(32) DEFAULT NULL,
  `last_name2` varchar(128) DEFAULT NULL,
  `gender3` varchar(32) DEFAULT NULL,
  `last_name3` varchar(128) DEFAULT NULL,
  `gender4` varchar(32) DEFAULT NULL,
  `last_name4` varchar(128) DEFAULT NULL,
  `gender5` varchar(32) DEFAULT NULL,
  `last_name5` varchar(128) DEFAULT NULL,
  `gender6` varchar(32) DEFAULT NULL,
  `last_name6` varchar(128) DEFAULT NULL,
  `gender7` varchar(32) DEFAULT NULL,
  `last_name7` varchar(128) DEFAULT NULL,
  `gender8` varchar(32) DEFAULT NULL,
  `last_name8` varchar(128) DEFAULT NULL,
  `gender9` varchar(32) DEFAULT NULL,
  `last_name9` varchar(128) DEFAULT NULL,
  `gender10` varchar(32) DEFAULT NULL,
  `last_name10` varchar(128) DEFAULT NULL,
  `gender11` varchar(32) DEFAULT NULL,
  `last_name11` varchar(128) DEFAULT NULL,
  `gender12` varchar(32) DEFAULT NULL,
  `last_name12` varchar(128) DEFAULT NULL,
  `gender13` varchar(32) DEFAULT NULL,
  `last_name13` varchar(128) DEFAULT NULL,
  `gender14` varchar(32) DEFAULT NULL,
  `last_name14` varchar(128) DEFAULT NULL,
  `gender15` varchar(32) DEFAULT NULL,
  `last_name15` varchar(128) DEFAULT NULL,
  `gender16` varchar(32) DEFAULT NULL,
  `last_name16` varchar(128) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `abcdefg` (
  `contact_id` varchar(36) DEFAULT NULL,
  `account_id` varchar(36) DEFAULT NULL,
  `gender` varchar(32) DEFAULT NULL,
  `last_name` varchar(128) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

SELECT DISTINCT(CONCAT(account_id, gender1, last_name1)) as contact_id, account_id, gender1 as gender, last_name1 as last_name FROM abcd WHERE last_name1 !='' ;
SELECT DISTINCT(CONCAT(account_id, gender2, last_name2)) as contact_id, account_id, gender2 as gender, last_name2 as last_name FROM abcd WHERE last_name2 !='' ;
SELECT DISTINCT(CONCAT(account_id, gender3, last_name3)) as contact_id, account_id, gender3 as gender, last_name3 as last_name FROM abcd WHERE last_name3 !='' ;
SELECT DISTINCT(CONCAT(account_id, gender4, last_name4)) as contact_id, account_id, gender4 as gender, last_name4 as last_name FROM abcd WHERE last_name4 !='' ;
SELECT DISTINCT(CONCAT(account_id, gender5, last_name5)) as contact_id, account_id, gender5 as gender, last_name5 as last_name FROM abcd WHERE last_name5 !='' ;
SELECT DISTINCT(CONCAT(account_id, gender6, last_name6)) as contact_id, account_id, gender6 as gender, last_name6 as last_name FROM abcd WHERE last_name6 !='' ;
SELECT DISTINCT(CONCAT(account_id, gender7, last_name7)) as contact_id, account_id, gender7 as gender, last_name7 as last_name FROM abcd WHERE last_name7 !='' ;
SELECT DISTINCT(CONCAT(account_id, gender8, last_name8)) as contact_id, account_id, gender8 as gender, last_name8 as last_name FROM abcd WHERE last_name8 !='' ;
SELECT DISTINCT(CONCAT(account_id, gender9, last_name9)) as contact_id, account_id, gender9 as gender, last_name9 as last_name FROM abcd WHERE last_name9 !='' ;
SELECT DISTINCT(CONCAT(account_id, gender10, last_name10)) as contact_id, account_id, gender10 as gender, last_name10 as last_name FROM abcd WHERE last_name10 !='' ;
SELECT DISTINCT(CONCAT(account_id, gender11, last_name11)) as contact_id, account_id, gender11 as gender, last_name11 as last_name FROM abcd WHERE last_name11 !='' ;
SELECT DISTINCT(CONCAT(account_id, gender12, last_name12)) as contact_id, account_id, gender12 as gender, last_name12 as last_name FROM abcd WHERE last_name12 !='' ;
SELECT DISTINCT(CONCAT(account_id, gender13, last_name13)) as contact_id, account_id, gender13 as gender, last_name13 as last_name FROM abcd WHERE last_name13 !='' ;
SELECT DISTINCT(CONCAT(account_id, gender14, last_name14)) as contact_id, account_id, gender14 as gender, last_name14 as last_name FROM abcd WHERE last_name14 !='' ;
SELECT DISTINCT(CONCAT(account_id, gender15, last_name15)) as contact_id, account_id, gender15 as gender, last_name15 as last_name FROM abcd WHERE last_name15 !='' ;
SELECT DISTINCT(CONCAT(account_id, gender16, last_name16)) as contact_id, account_id, gender16 as gender, last_name16 as last_name FROM abcd WHERE last_name16 !='' ;

SELECT DISTINCT contact_id, account_id, gender, last_name FROM `abcdefg` ;


SELECT * FROM `contacts` WHERE `date_entered` = '2012-03-27 11:28:43' AND smsgroup='student' ;

DELETE FROM `contacts` WHERE `date_entered` = '2012-03-27 11:28:43' AND smsgroup='student' ;


SELECT accounts.name, contacts.id, contacts.last_name
FROM accounts , accounts_contacts, contacts
WHERE accounts.id=accounts_contacts.account_id AND accounts_contacts.deleted=0 
	AND contacts.id=accounts_contacts.contact_id AND contacts.deleted=0
	AND accounts.deleted=0 AND contacts.`date_entered` = '2012-03-27 11:28:43';
	

UPDATE accounts , accounts_contacts, contacts
SET contacts.company_name=accounts.name
WHERE accounts.id=accounts_contacts.account_id AND accounts_contacts.deleted=0 
	AND contacts.id=accounts_contacts.contact_id AND contacts.deleted=0
	AND accounts.deleted=0 AND contacts.`date_entered` = '2012-03-27 11:28:43';
	
	
-- 2012-08-18 import


CREATE TABLE IF NOT EXISTS `a_account_goryu` (
  `var51` varchar(36) NOT NULL DEFAULT '',
  `var52` varchar(100) DEFAULT NULL,
  `var48` varchar(100) DEFAULT NULL,
  `email2` varchar(100) DEFAULT NULL,
  `a_type` tinyint(1) NOT NULL DEFAULT '0',
  `tutor_id` varchar(36) DEFAULT NULL,
  `tutor_name` varchar(100) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

SELECT account_goryu.email2, account_goryu.var48m, accounts.email2, accounts.custom_var48
FROM accounts, account_goryu WHERE accounts.id=account_goryu.tutor_id

UPDATE accounts, account_goryu
SET accounts.email2=account_goryu.email2, accounts.custom_var48=account_goryu.var48
WHERE accounts.id=account_goryu.tutor_id

ALTER TABLE `smsevent` CHANGE `name` `name` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ;


CREATE TABLE IF NOT EXISTS `a_course_goryu` (
  `course_account_id` varchar(36) DEFAULT NULL,
  `tutor_id` varchar(36) NOT NULL DEFAULT '',
  `name` varchar(100) NOT NULL,
  `start_date` varchar(36) DEFAULT NULL,
  `end_date` varchar(36) DEFAULT NULL,
  `sdb_account_id` varchar(36) DEFAULT NULL,
  `sdb_name` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `a_course_tm` (
  `course_account_id` varchar(36) DEFAULT NULL,
  `tutor_id` varchar(36) NOT NULL DEFAULT '',
  `name` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `a_student_tm` (
  `course_account_id` varchar(36) DEFAULT NULL,
  `tutor_id` varchar(36) NOT NULL DEFAULT '',
  `s_id` varchar(36) NOT NULL DEFAULT '',
  `sname` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

SELECT a_course_tm.course_account_id, a_student_tm.sname
FROM a_course_tm, a_student_tm
WHERE a_course_tm.tutor_id=a_student_tm.tutor_id

UPDATE a_course_tm, a_student_tm
SET a_student_tm.course_account_id=a_course_tm.course_account_id
WHERE a_course_tm.tutor_id=a_student_tm.tutor_id

SELECT sname, course_account_id FROM `a_student_tm`WHERE 1
GROUP BY sname, course_account_id HAVING count(*) >1

SELECT a_course_goryu.start_date, a_course_goryu.end_date
FROM a_ec_tm, a_course_goryu
WHERE a_ec_tm.tutor_id=a_course_goryu.tutor_id

UPDATE a_ec_tm, a_course_goryu
SET a_ec_tm.start_date=a_course_goryu.start_date, a_ec_tm.end_date=a_course_goryu.end_date
WHERE a_ec_tm.tutor_id=a_course_goryu.tutor_id

UPDATE a_ec_tm
SET start_date=DATE_ADD(start_date, INTERVAL 600 MINUTE), end_date=DATE_ADD(end_date, INTERVAL 600 MINUTE)
WHERE 1


StudentID	Gender	FirstName	LastName	Company	Band	Position	Destination

CREATE TABLE IF NOT EXISTS `a_student_sdb` (
  `id` varchar(36) DEFAULT NULL,
  `gender` varchar(8) DEFAULT NULL,
  `last_name` varchar(100) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `company_id` varchar(36) DEFAULT NULL,
  `company_name` varchar(100) NOT NULL,
  `custom_var9` varchar(100) NOT NULL,
  `position` varchar(100) NOT NULL,
  `destination` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

SELECT a_account_goryu.var51, a_account_goryu.tutor_id
FROM a_account_goryu, a_student_sdb
WHERE a_student_sdb.company_name=a_account_goryu.var52

UPDATE a_account_goryu, a_student_sdb
SET a_student_sdb.company_id=a_account_goryu.tutor_id
WHERE a_student_sdb.company_name=a_account_goryu.var52

SELECT a_account_goryu.tutor_name, a_student_sdb.company_name
FROM a_account_goryu, a_student_sdb
WHERE a_student_sdb.company_id=a_account_goryu.tutor_id


UPDATE a_account_goryu, a_student_sdb
SET a_student_sdb.company_name=a_account_goryu.tutor_name
WHERE a_student_sdb.company_id=a_account_goryu.tutor_id


SELECT a_account_goryu.tutor_name, a_student_tm.company_name
FROM a_account_goryu, a_student_tm
WHERE a_student_tm.course_account_id=a_account_goryu.tutor_id


UPDATE a_account_goryu, a_student_tm
SET a_student_tm.company_name=a_account_goryu.tutor_name
WHERE a_student_tm.course_account_id=a_account_goryu.tutor_id


SELECT a_account_goryu.tutor_name, a_student_tm.company_name, course_account_id 
FROM a_student_tm
LEFT JOIN a_account_goryu ON a_student_tm.course_account_id=a_account_goryu.tutor_id
WHERE a_account_goryu.tutor_name IS NULL

('h7821', 'h7853', 'h7857', 'h7883', 'h7895')

ALTER TABLE `smsevent` ADD `custom_var71` TEXT NULL DEFAULT NULL ;
ALTER TABLE `smsevent` ADD `custom_var72` TEXT NULL DEFAULT NULL ;
ALTER TABLE `smsevent` ADD `custom_var73` TEXT NULL DEFAULT NULL ;
ALTER TABLE `smsevent` ADD `custom_var74` TEXT NULL DEFAULT NULL ;
ALTER TABLE `smsevent` ADD `custom_var75` TEXT NULL DEFAULT NULL ;
ALTER TABLE `smsevent` ADD `custom_var76` TEXT NULL DEFAULT NULL ;
ALTER TABLE `smsevent` ADD `custom_var77` TEXT NULL DEFAULT NULL ;


SELECT parent_id, node_id, category_tree.parent_node_id
FROM smsevent, category_tree
WHERE smsevent.parent_id = category_tree.node_id


UPDATE smsevent, category_tree
SET parent_id=category_tree.parent_node_id
WHERE smsevent.parent_id = category_tree.node_id

SELECT smsevent.course_account_id, accounts.id, accounts.name
FROM smsevent, accounts WHERE smsevent.course_account_id=accounts.name

UPDATE smsevent, accounts 
SET smsevent.course_account_id=accounts.id
WHERE smsevent.course_account_id=accounts.name


SELECT smsevent.course_account_id, a_account_goryu.tutor_id, a_account_goryu.tutor_name, var52
FROM smsevent, a_account_goryu WHERE smsevent.course_account_id=SUBSTRING(a_account_goryu.var52, 1, 36)

UPDATE smsevent, a_account_goryu 
SET smsevent.course_account_id=a_account_goryu.tutor_id
WHERE smsevent.course_account_id=SUBSTRING(a_account_goryu.var52, 1, 36)


SELECT a_course_goryu.start_date, a_course_goryu.end_date, a_ec_tm.start_date, a_ec_tm.end_date
FROM a_ec_tm, a_course_goryu
WHERE a_ec_tm.tutor_id=a_course_goryu.tutor_id

SELECT a_course_goryu.start_date, a_course_goryu.end_date, CONCAT(a_course_goryu.start_date, " ", a_course_goryu.end_date) as new_desc
FROM a_ec_tm, a_course_goryu
WHERE a_ec_tm.tutor_id=a_course_goryu.tutor_id

UPDATE a_ec_tm, a_course_goryu
SET a_ec_tm.description=CONCAT(a_course_goryu.start_date, " ", a_course_goryu.end_date)
WHERE a_ec_tm.tutor_id=a_course_goryu.tutor_id

SELECT smsevent.custom_var77, smsevent.id
FROM smsevent, event_contacts
WHERE event_contacts.event_id=smsevent.custom_var77

UPDATE smsevent, event_contacts
SET event_contacts.event_id=smsevent.id
WHERE event_contacts.event_id=smsevent.custom_var77

SELECT smsevent.syllabus_id as new_desc
FROM smsevent, event_contacts
WHERE smsevent.id=event_contacts.event_id

UPDATE smsevent, event_contacts
SET event_contacts.description=smsevent.syllabus_id
WHERE smsevent.id=event_contacts.event_id


SELECT smsevent.parent_id, course_category.id
FROM smsevent, course_category
WHERE smsevent.parent_id = course_category.name


UPDATE smsevent, course_category
SET smsevent.parent_id = course_category.id
WHERE smsevent.parent_id = course_category.name

SELECT id, name, custom_var46, date_entered, SUBSTRING(date_entered, 1, 10) FROM `accounts` WHERE 1

UPDATE accounts SET custom_var46=SUBSTRING(date_entered, 1, 10) WHERE 1


CREATE TABLE IF NOT EXISTS `a_course_update` (
  `tutor_id` varchar(36) NOT NULL DEFAULT '',
  `custom_var38` varchar(100) DEFAULT NULL,
  `custom_var33` varchar(100) DEFAULT NULL,
  `custom_var41` varchar(100) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;


SELECT a_course_update.tutor_id, a_course_update.custom_var38, a_course_update.custom_var33, a_course_update.custom_var41 
FROM a_course_goryu, a_course_update WHERE a_course_goryu.tutor_id= a_course_update.tutor_id


UPDATE a_course_goryu, a_course_update
SET a_course_goryu.custom_var38=a_course_update.custom_var38,
	a_course_goryu.custom_var33=a_course_update.custom_var33,
	a_course_goryu.custom_var41=a_course_update.custom_var41
WHERE a_course_goryu.tutor_id= a_course_update.tutor_id


SELECT smsevent.name as original, a_course_goryu.name, a_course_goryu.custom_var38, a_course_goryu.custom_var33, a_course_goryu.custom_var41
FROM smsevent, a_course_goryu
WHERE sdb_courseid=smsevent.custom_var77

UPDATE smsevent, a_course_goryu
SET smsevent.name=a_course_goryu.name, 
	smsevent.custom_var38=a_course_goryu.custom_var38, 
	smsevent.custom_var33=a_course_goryu.custom_var33, 
	smsevent.custom_var41=a_course_goryu.custom_var41
WHERE sdb_courseid=smsevent.custom_var77

	
SELECT * FROM `a_course_goryu` WHERE sdb_courseid='1112BusinessEnglishClass'

SELECT smsevent.id, custom_var77, smsevent.name, syllabus_id, course_account_id, accounts.name
FROM smsevent, accounts WHERE smsevent.course_account_id=accounts.id AND custom_var77='1001Group1' 


SELECT event_contacts.id, event_contacts.event_id, contacts.id, contacts.last_name, contacts.company_name
FROM event_contacts, smsevent, contacts
WHERE smsevent.id=event_contacts.event_id AND contacts.id=event_contacts.contact_id AND smsevent.custom_var77='1001Group1' 



ClassA0709				X
1207Kobayashi
	ec_sdb_4247 	csdb_1091 	6473 	Takeshi 	Topre Corporation
	ec_sdb_4259 	csdb_0543 	9923 	Kenji 	NSK Ltd.
1206ClassA				
	csdb_0015  	1206ClassA 	Gumma R&D Class A 120612 	2012-06-12 	h1379 	Advantest Corporation
	csdb_0848 	1206ClassA 	Gumma R&D Class A 120612 	2012-06-14 	h6594 	Nippon Steel Corporation
1206BusinessEnglishClass
	csdb_1048  	1206BusinessEnglishClass 	Business English Class 120613 	2012-06-13 	h7826 	SUMITOMO METAL MINING ENGINEERING CO.,LTD.
	csdb_1093 	1206BusinessEnglishClass 	Business English Class 120613 	2012-06-25 	h7894 	TOYAMA Co., Ltd.
1205ClassF
	csdb_0014  	1205ClassF 	Marunouchi HQ Class F 120612 	2012-06-12 	h1379 	Advantest Corporation
	csdb_0751 	1205ClassF 	Marunouchi HQ Class F 120612 	2012-05-09 	h6670 	Nihon Synopsys Co,Ltd.
1205ClassE
	csdb_0013  	1205ClassE 	Saitama R&D Class E  120611 	2012-06-12 	h1379 	Advantest Corporation
	csdb_0750 	1205ClassE 	Saitama R&D Class E  120611 	2012-05-08 	h6670 	Nihon Synopsys Co,Ltd.
1201BusinessEnglish
	csdb_0161  	1201BusinessEnglish 	Business English 120126 	2012-01-26 	h7874 	FUJI OFFICE&LIFE SERVICE CO.,lTD.
	csdb_0832 	1201BusinessEnglish 	Business English 120126 	2012-01-10 	h6594 	Nippon Steel Corporation
	csdb_1067 	1201BusinessEnglish 	Business English 120126 	2012-01-11 	h7873 	Tokyo Metropolitan Small and Medium Enterprise Sup...
1112BusinessEnglishClass
	csdb_1039  	1112BusinessEnglishClass 	Mr. Yoichi Onosato 20 hour Pre-departure for Alask... 	2011-12-06 	h7803 	SUMITOMO METAL MINING CO., LTD
	csdb_1047 	1112BusinessEnglishClass 	Mr. Yoichi Onosato 20 hour Pre-departure course fo... 	2011-12-06 	h7826 	SUMITOMO METAL MINING ENGINEERING CO.,LTD.
1111ClassC
	csdb_0012  	1111ClassC 	Class C 24 Hour Group Lesson 	2011-11-10 	h1379 	Advantest Corporation
	csdb_0831 	1111ClassC 	Class C November 2011 40 Hour Group Course 	2011-11-24 	h6594 	Nippon Steel Corporation
	csdb_0011  	1111ClassB 	Class B 24 Hour Group Lesson 	2011-11-02 	h1379 	Advantest Corporation
	csdb_0830 	1111ClassB 	Class B November 2011 40 Hour Group Lesson 	2011-11-30 	h6594 	Nippon Steel Corporation
1111ClassA
	csdb_0010  	1111ClassA 	Class A 24 Hour Group Lesson 	2011-11-01 	h1379 	Advantest Corporation
	csdb_0829 	1111ClassA 	Class A November 2011 Group Lesson 	2011-11-22 	h6594 	Nippon Steel Corporation
1106ClassA
	csdb_0006  	1106ClassA 	Class A Group Class 	2011-06-01 	h1379 	Advantest Corporation
	csdb_0218 	1106ClassA 	Class A 	2011-06-01 	h7748 	FUJIFILM Graphic Systems Co., Ltd.
	csdb_0290 	1106ClassA 	Class A Group Class 	2011-06-22 	h7859 	JDA Software Japan Co.,Ltd.
1105ClassC
	csdb_0002  	1105ClassC 	Class C Group Class 	2011-05-30 	h1379 	Advantest Corporation
	csdb_0241 	1105ClassC 	Class C (Beginner) Business English (bilingual tra... 	2011-05-09 	h7798 	FUJIFILM Graphic Systems Techno service Co.,Ltd.
1105ClassB
	csdb_0001  	1105ClassB 	Class B Group Class 	2011-05-30 	h1379 	Advantest Corporation
	csdb_0240 	1105ClassB 	Class B (Intermediate) Business English (w/bilingu... 	2011-05-15 	h7798 	FUJIFILM Graphic Systems Techno service Co.,Ltd.
1105ClassA				X
1105 Presentation
	csdb_0771  	1105 Presentation 	1105 Presentation 	2011-06-09 	h7712 	NIKON CORPORATION
	csdb_1010 	1105 Presentation 	1105 Presentation 	2011-05-13 	h7239 	Sanwa Shutter Corporation
1011ClassB
	csdb_0301  	1011ClassB 	Class B Group Lesson 	2010-11-01 	h7778 	Johnson&Johnson K.K.
	csdb_0801 	1011ClassB 	Class B Group Class 	2010-11-16 	h6594 	Nippon Steel Corporation
1011ClassA
	csdb_0300  	1011ClassA 	Class A Small Group Class 	2010-11-04 	h7778 	Johnson&Johnson K.K.
	csdb_0800 	1011ClassA 	Class A Group Class 	2010-11-16 	h6594 	Nippon Steel Corporation
1011 Presentation
	csdb_0085  	1011 Presentation 	1011 Presentation 	2010-11-08 	h1858 	CalsonicKansei Corp
	csdb_0770 	1011 Presentation 	1011 Presentation 	2010-11-10 	h7712 	NIKON CORPORATION
	csdb_0955 	1011 Presentation 	1011 Presentation 	2010-11-25 	h7843 	Nissan shatai Co., Ltd.
1009 Negotiation
	csdb_0769  	1009 Negotiation 	1009 Negotiation 	2010-09-06 	h7712 	NIKON CORPORATION
	csdb_0972 	1009 Negotiation 	1009 Negotiation 	2010-09-09 	h690 	Ricoh Human Creates Inc.
1007BusinessEnglishClass
	csdb_0964  	1007BusinessEnglishClass 	Business English Class Group Lesson 	2010-07-13 	h7807 	Rhythm Watch Co.,Ltd.
	csdb_1045 	1007BusinessEnglishClass 	Business English Group Class 	2010-07-06 	h7826 	SUMITOMO METAL MINING ENGINEERING CO.,LTD.
1002Wednesday
	csdb_0337  	1002Wednesday 	Wednesday Feb 2010 	2010-02-10 	h7734 	MAHLE Filter Systems Japan Corporation
	csdb_0351 	1002Wednesday 	Akasaka AT&T Wednesday Class 	2010-02-03 	h2211 	Manulife Life Insurance Company
1001Group1
	csdb_0039  	1001Group1 	New Associate Intensive Program Group 1 	2010-01-14 	h6244 	Tokyo Aoyama Aoki Koma Law Office
	csdb_0880 	1001Group1 	Group 1 Pre-departure 	2010-01-27 	h1470 	NISSAN MOTOR CO., LTD.


SELECT a_course_update.tutor_id, a_course_update.custom_var38, a_course_update.custom_var33, a_course_update.custom_var41, smsevent.custom_var77
FROM smsevent, a_course_update WHERE smsevent.id= a_course_update.tutor_id

UPDATE smsevent, a_course_update
SET smsevent.custom_var38=a_course_update.custom_var38,
	smsevent.custom_var33=a_course_update.custom_var33,
	smsevent.custom_var41=a_course_update.custom_var41
WHERE smsevent.id= a_course_update.tutor_id


UPDATE smsevent SET custom_var38='J-Presence Academy' WHERE custom_var38='JAL Academy'


SELECT accounts.name, accounts.custom_var41 FROM accounts, smsevent WHERE smsevent.course_account_id=accounts.id

UPDATE accounts, smsevent
SET  smsevent.custom_var31=accounts.custom_var41, smsevent.custom_var32=accounts.name
WHERE smsevent.course_account_id=accounts.id

SELECT * FROM `contacts` WHERE `smsgroup` LIKE 'teacher' AND `school_id` LIKE '2d4bc5bb-57be-c6cb-723e-502ddd73996b'

DELETE * FROM `contacts` WHERE `smsgroup` LIKE 'teacher' AND `school_id` LIKE '2d4bc5bb-57be-c6cb-723e-502ddd73996b'


SELECT * FROM `event_contacts` WHERE contact_id<'2000' AND contact_id > '1000'

UPDATE `event_contacts` SET deleted=1 WHERE contact_id<'2000' AND contact_id > '1000'

SELECT * FROM `event_contacts` WHERE contact_id<'3000' AND contact_id >= '2000'



SELECT id, parent_id, deleted FROM `sms_memo` WHERE parent_id<'3000' 

DELETE FROM `sms_memo` WHERE parent_id<'3000' 

UPDATE `sms_memo` SET deleted=1 WHERE description = ''


SELECT * FROM `contacts` WHERE `id` LIKE 'sdb%' AND smsgroup='student'

DELETE * FROM `contacts` WHERE `id` LIKE 'sdb%' AND smsgroup='student'


CREATE TABLE IF NOT EXISTS `a_schedule` (
  `account_id` varchar(36) DEFAULT NULL,
  `course_id` varchar(100) DEFAULT NULL,
  `tutor_id` varchar(100) DEFAULT NULL,
  `course_name` varchar(256) NOT NULL,
  `course_date` varchar(36) DEFAULT NULL,
  `course_start` varchar(36) DEFAULT NULL,
  `course_end` varchar(36) DEFAULT NULL,
  `cancel_info` varchar(32) DEFAULT NULL,
  `teacher_id` varchar(36) DEFAULT NULL,
  `teacher_id_tm` varchar(36) DEFAULT NULL,
  `teacher_name` varchar(100) NOT NULL,
  `account_name` varchar(256) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

事前キャンセル				64
天災キャンセル				131072
依頼キャンセル				262144
当日キャンセル				524288


SELECT smsevent.name, smsevent.id, smsevent.course_account_id, a_schedule.account_id, a_schedule.tutor_id
FROM smsevent, a_schedule 
WHERE custom_var77 IS NULL AND smsevent.id=a_schedule.tutor_id

UPDATE  smsevent, a_schedule SET a_schedule.course_id=smsevent.id
WHERE custom_var77 IS NULL AND smsevent.id=a_schedule.tutor_id


SELECT a_course_goryu.tutor_id, a_course_goryu.course_account_id, smsevent.id, smsevent.custom_var77
FROM a_course_goryu, smsevent
WHERE a_course_goryu.event_id IS NULL AND smsevent.id=a_course_goryu.tutor_id

UPDATE a_course_goryu, smsevent SET a_course_goryu.event_id=smsevent.id
WHERE a_course_goryu.event_id IS NULL AND smsevent.id=a_course_goryu.tutor_id

SELECT a_course_goryu.tutor_id, a_course_goryu.sdb_courseid, smsevent.id, a_course_goryu.course_account_id, smsevent.course_account_id, smsevent.id, smsevent.custom_var77
FROM a_course_goryu, smsevent
WHERE a_course_goryu.event_id IS NULL AND smsevent.custom_var77=a_course_goryu.sdb_courseid
 AND a_course_goryu.course_account_id=smsevent.course_account_id
 

UPDATE a_course_goryu, smsevent SET a_course_goryu.event_id=smsevent.id
WHERE a_course_goryu.event_id IS NULL AND smsevent.custom_var77=a_course_goryu.sdb_courseid
 AND a_course_goryu.course_account_id=smsevent.course_account_id


SELECT a_course_goryu.tutor_id, a_course_goryu.course_account_id, smsevent.id, smsevent.custom_var77
FROM a_course_goryu, smsevent
WHERE a_course_goryu.event_id IS NULL AND smsevent.id=a_course_goryu.tutor_id 
	AND smsevent.course_account_id=a_course_goryu.course_account_id
	

SELECT a_course_goryu.event_id, a_course_goryu.tutor_id, a_course_goryu.sdb_courseid, a_course_goryu.course_account_id,
	a_schedule.account_id, a_schedule.tutor_id
FROM a_course_goryu, a_schedule 
WHERE a_course_goryu.tutor_id=a_schedule.tutor_id AND a_course_goryu.course_account_id=a_schedule.account_id
	AND a_schedule.course_id IS NULL

	
UPDATE FROM `a_schedule` SET course_id='csdb_1043' WHERE `tutor_id` LIKE 'N11012301nn'

DELETE FROM `a_schedule` WHERE `tutor_id` LIKE '20111101R'

SELECT max(course_date) as max_date, tutor_id
FROM a_schedule WHERE course_id IS NULL
ORDER BY max_date
GROUP BY tutor_id


2013-03-12 	A12062001basic
2013-03-28 	A12062001inter
2013-05-18 	K11010403

SELECT max( course_date ) AS max_date, tutor_id
FROM a_schedule
WHERE course_id IS NULL 
GROUP BY tutor_id, account_id
HAVING max_date < '2012-06-01'
ORDER BY max_date


SELECT max( course_date ) AS max_date, tutor_id
FROM a_schedule
WHERE course_id IS NULL 
GROUP BY tutor_id
HAVING max_date < '2012-06-01'
ORDER BY max_date

SELECT max( course_date ) AS max_date, tutor_id
FROM a_schedule
WHERE course_id IS NULL 
GROUP BY tutor_id
HAVING max_date < '2012-06-01'
ORDER BY max_date


SELECT max( course_date ) AS max_date, tutor_id, account_id
FROM a_schedule
WHERE course_id IS NULL 
GROUP BY tutor_id, account_id
HAVING max_date < '2012-06-01'
ORDER BY max_date


SELECT max( course_date ) AS max_date, tutor_id, account_id 
FROM a_schedule
WHERE course_id IS NULL 
GROUP BY tutor_id, account_id
ORDER BY tutor_id



SELECT smsevent.name, smsevent.id, smsevent.course_account_id, a_schedule.account_id, a_schedule.tutor_id
FROM smsevent, a_schedule 
WHERE custom_var77 IS NULL AND smsevent.id=a_schedule.tutor_id AND smsevent.course_account_id=a_schedule.account_id

UPDATE  smsevent, a_schedule SET a_schedule.course_id=smsevent.id
WHERE custom_var77 IS NULL AND smsevent.id=a_schedule.tutor_id AND smsevent.course_account_id=a_schedule.account_id


SELECT a_course_goryu.event_id, a_course_goryu.tutor_id, a_course_goryu.sdb_courseid, a_course_goryu.course_account_id,
	a_schedule.account_id, a_schedule.tutor_id
FROM a_course_goryu, a_schedule 
WHERE a_course_goryu.tutor_id=a_schedule.tutor_id AND a_course_goryu.course_account_id=a_schedule.account_id
	AND a_schedule.course_id IS NULL

UPDATE a_course_goryu, a_schedule 
SET a_schedule.course_id=a_course_goryu.event_id
WHERE a_course_goryu.tutor_id=a_schedule.tutor_id AND a_course_goryu.course_account_id=a_schedule.account_id
	AND a_schedule.course_id IS NULL

SELECT DISTINCT course_id FROM `a_schedule` WHERE course_id IS NOT NULL   -> 323

SELECT DISTINCT event_id FROM a_course_goryu WHERE 1  -> 323


SELECT max( course_date ) AS max_date, tutor_id, account_name, course_name
FROM a_schedule
WHERE course_id IS NULL 
GROUP BY tutor_id, account_id
HAVING max_date >= '2012-06-01'
ORDER BY max_date

max_date 	tutor_id
2012-06-07 	N11010601 	SUMITOMO METAL MINING CO., LTD 	バイリンガル（秘書向け）Class1 120126
2012-07-06 	N11072503n 	Samsung Japan Corporation 	Mr. Kaneko 111126
2012-07-23 	K10100501 	Expedia Holdings K.K. 	Ms. Sato 101006
2012-08-02 	MN101109001 	  	Mr. Sekiguchi 101118
		2012-07-27 	Sub 	Sub Trainer 	サブ
		2012-07-28 	OH 	Office Hour 	オフィースアワー

('N11010601', 'N11072503n', 'K10100501', 'MN101109001')

SELECT DISTINCT course_id, account_id, course_name, account_name FROM `a_schedule`
WHERE course_id IS NOT NULL ORDER BY account_id, course_id

SELECT DISTINCT event_id, course_account_id, name FROM `a_course_goryu`
WHERE event_id IS NOT NULL ORDER BY course_account_id, event_id


SELECT 	from_time, to_time, course_date, course_start, course_end, 
	DATE_ADD(CONCAT(course_date, " ", course_start), INTERVAL -540 MINUTE) AS from_t, 
	DATE_ADD(CONCAT(course_date, " ", course_end), INTERVAL -540 MINUTE) AS to_t
FROM a_schedule WHERE 1

UPDATE a_schedule
SET from_time = DATE_ADD(CONCAT(course_date, " ", course_start), INTERVAL -540 MINUTE), 
	to_time = DATE_ADD(CONCAT(course_date, " ", course_end), INTERVAL -540 MINUTE)
WHERE 1



INSERT INTO `sms_recurrence` (`id`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `actual_event_start`, `actual_event_end`, `number_of_occurrences`, `occurrence_interval`, `holiday_skip_type`, `is_check_holidays`, `schedule_type`, `event_start`, `event_end`, `recurrence_info`) VALUES
('38ed26c0-5bb9-30e7-7cf5-4eb0e91bd8a9', 0, '2011-11-02 06:57:22', '2012-02-13 09:14:12', 'c34b062c-ee28-53cb-de64-4ea3e1979df1', 'c34b062c-ee28-53cb-de64-4ea3e1979df1', '2011-11-17 08:30:00', '2012-02-16 10:30:00', 0, 1, 'skip', 1, 'weekly', '2011-11-16 15:00:00', '2012-02-15 15:00:00', '{"recurrences_arr":[{"day":"4","from_time_hour":"17","from_time_minute":"30","to_time_hour":"19","to_time_minute":"30","all_day":""}]}');
INSERT INTO `sms_recurrence` (`id`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `actual_event_start`, `actual_event_end`, `number_of_occurrences`, `occurrence_interval`, `holiday_skip_type`, `is_check_holidays`, `schedule_type`, `event_start`, `event_end`, `recurrence_info`) VALUES
('ca7127cb-2041-2466-3b39-4ea4cb307002', 0, '2011-10-24 02:21:25', '2011-10-24 02:21:44', '1', '1', '2011-12-22 15:00:00', '2012-01-04 15:00:00', 0, 1, 'skip', 1, 'single', '2011-12-22 15:00:00', '2012-01-04 15:00:00', '{"recurrences_arr":[]}');


INSERT INTO `event_contacts` (`id`, `contact_id`, `event_id`, `date_modified`, `deleted`, `event_contact_status`, `start_date`, `end_date`, `event_credit`, `description`) VALUES
('d3875894-06fd-1f6a-d626-4eb38794dd31', '19901e8c-d6a8-45bf-f46f-4eb349018eb4', '38ed26c0-5bb9-30e7-7cf5-4eb0e91bd8a9', '2012-01-30 08:14:09', 0, '0', '2011-11-24 08:30:00', '2012-02-16 10:30:00', NULL, NULL),
('d4ac7d62-6548-a3a0-f83b-4eb387f1066d', '5080f95d-5637-e89a-b583-4eb349da6c41', '38ed26c0-5bb9-30e7-7cf5-4eb0e91bd8a9', '2012-01-30 08:14:09', 0, '0', '2011-11-17 08:30:00', '2012-02-16 10:30:00', NULL, NULL),
('d790020c-c098-4d1a-7553-4eb0e9de313d', '349b6e88-a13b-0099-0a14-4eafaffefccc', '38ed26c0-5bb9-30e7-7cf5-4eb0e91bd8a9', '2012-01-30 08:14:09', 0, '4', '2011-11-17 08:30:00', '2012-02-16 10:30:00', NULL, NULL);



INSERT INTO `sms_event_instance` (`id`, `parent_id`, `event_type`, `from_datetime`, `to_datetime`, `name`, `description`, `description2`, `description3`, `date_modified`, `deleted`, `status`, `from_id`, `to_id`, `reason`) VALUES
('ec490742-defe-21c1-a9c4-4eb0e983bfe7', '38ed26c0-5bb9-30e7-7cf5-4eb0e91bd8a9', 'course', '2011-11-17 08:30:00', '2011-11-17 10:30:00', NULL, NULL, NULL, NULL, '2012-02-13 09:14:12', 0, 1, 'a68af24d-ff39-26ee-d835-4eb0e801f66a', '', ''),
('ec581a4d-289f-0f03-e0bc-4eb0e94aeeeb', '38ed26c0-5bb9-30e7-7cf5-4eb0e91bd8a9', 'course', '2011-11-24 08:30:00', '2011-11-24 10:30:00', NULL, NULL, NULL, NULL, '2012-02-13 09:14:12', 0, 1, 'a6993980-d744-1485-c1bb-4eb0e8ea0cf4', '', '');


INSERT INTO `smsevent_instances` (`id`, `date_modified`, `event_id`, `event_type`, `instance_id`, `target_id`, `category`, `from_datetime`, `to_datetime`, `status`, `from_instance_id`, `to_instance_id`, `attendance_status`, `instance_contact_status`, `name`, `description`, `early_late_time2`, `early_late_time3`, `attendance_memo`) VALUES
('ecdf2135-f7a5-1f8a-8cf0-4eb0e902d15d', '2011-11-02 06:57:22', '38ed26c0-5bb9-30e7-7cf5-4eb0e91bd8a9', 'course', 'ec490742-defe-21c1-a9c4-4eb0e983bfe7', '349b6e88-a13b-0099-0a14-4eafaffefccc', 'teacher', '2011-11-17 08:30:00', '2011-11-17 10:30:00', 1, '', '', 2, 4, NULL, '', 0, 0, ''),
('c60e1d0f-cfda-41bb-b7b5-4eb3877031db', '2011-11-04 06:35:14', '38ed26c0-5bb9-30e7-7cf5-4eb0e91bd8a9', 'course', 'ec490742-defe-21c1-a9c4-4eb0e983bfe7', '5080f95d-5637-e89a-b583-4eb349da6c41', 'student', '2011-11-17 08:30:00', '2011-11-17 10:30:00', 1, '', '', 2, 0, NULL, '', 0, 0, ''),
('c602da02-2081-b02a-b184-4eb387440379', '2011-11-16 04:35:17', '38ed26c0-5bb9-30e7-7cf5-4eb0e91bd8a9', 'course', 'ec490742-defe-21c1-a9c4-4eb0e983bfe7', '19901e8c-d6a8-45bf-f46f-4eb349018eb4', 'student', '2011-11-17 08:30:00', '2011-11-17 10:30:00', 129, '', '92145f64-4708-9dfc-a35e-4eb0ebeca010', NULL, 0, NULL, '', 0, 0, ''),
('8486b7a7-9ab9-1d93-6829-4ebb734b173a', '2011-11-17 09:10:02', '38ed26c0-5bb9-30e7-7cf5-4eb0e91bd8a9', 'course', 'ec490742-defe-21c1-a9c4-4eb0e983bfe7', '12ef6c3c-974f-acb9-1f91-4eb34960dfb4', 'student', '2011-11-17 08:30:00', '2011-11-17 10:30:00', 161, 'c1c358e6-2e66-f47d-f266-4eb0e95e8452', '92145f64-4708-9dfc-a35e-4eb0ebeca010', NULL, 1, NULL, '', 0, 0, '');


DELETE FROM `event_contacts` WHERE `event_contact_status` LIKE '3';
TRUNCATE TABLE `sms_recurrence` ;
TRUNCATE TABLE `sms_event_instance` ;
TRUNCATE TABLE `smsevent_instances` ;


SELECT id, name, course_account_id, custom_var77 FROM `smsevent` WHERE `custom_var77` LIKE '1206ClassA' 


CREATE TABLE IF NOT EXISTS `a_course_text` (
  `company_name` varchar(256) NOT NULL,
  `account_id` varchar(36) DEFAULT NULL,
  `course_id` varchar(100) DEFAULT NULL,
  `tutor_id` varchar(100) DEFAULT NULL,
  `course_name` varchar(256) NOT NULL,
  `tid1` varchar(100) DEFAULT NULL,
  `tname1` varchar(256) NOT NULL,
  `tid2` varchar(100) DEFAULT NULL,
  `tname2` varchar(256) NOT NULL,
  `tid3` varchar(100) DEFAULT NULL,
  `tname3` varchar(256) NOT NULL,
  `tid4` varchar(100) DEFAULT NULL,
  `tname4` varchar(256) NOT NULL,
  `tid5` varchar(100) DEFAULT NULL,
  `tname5` varchar(256) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


SELECT a_account_goryu.var51, a_account_goryu.tutor_id
FROM a_account_goryu, a_course_text
WHERE a_course_text.company_name=a_account_goryu.var52

UPDATE  a_account_goryu, a_course_text
SET a_course_text.account_id=a_account_goryu.tutor_id
WHERE a_course_text.company_name=a_account_goryu.var52


SELECT a_course_goryu.event_id, a_course_goryu.name, a_course_text.course_name
FROM a_course_goryu, a_course_text
WHERE a_course_goryu.course_account_id=a_course_text.account_id AND a_course_goryu.sdb_courseid=a_course_text.csdb_id
AND a_course_goryu.sdb_courseid!=''

UPDATE  a_course_goryu, a_course_text
SET a_course_text.course_id=a_course_goryu.event_id
WHERE a_course_goryu.course_account_id=a_course_text.account_id AND a_course_goryu.sdb_courseid=a_course_text.csdb_id
AND a_course_goryu.sdb_courseid!=''



SELECT smsevent.id, smsevent.name, a_course_text.course_name, smsevent.course_account_id
FROM smsevent, a_course_text
WHERE smsevent.course_account_id=a_course_text.account_id AND smsevent.name=a_course_text.course_name
	AND a_course_text.course_id IS NULL  AND a_course_text.csdb_id=smsevent.custom_var77
ORDER BY smsevent.id ASC

UPDATE smsevent, a_course_text
SET a_course_text.course_id=smsevent.id
WHERE smsevent.course_account_id=a_course_text.account_id AND smsevent.name=a_course_text.course_name
	AND a_course_text.course_id IS NULL  AND a_course_text.csdb_id=smsevent.custom_var77


SELECT account_id, course_id, course_name, tid1, tname1
FROM a_course_text WHERE 1

SELECT id, course_id, name FROM product, a_course_text
WHERE product.name=a_course_text.tname1
ORDER BY product.id, a_course_text.course_id

UPDATE product, a_course_text SET tid1=product.id WHERE product.name=a_course_text.tname1

UPDATE product, a_course_text SET tid2=product.id WHERE product.name=a_course_text.tname2 ;
UPDATE product, a_course_text SET tid3=product.id WHERE product.name=a_course_text.tname3 ;
UPDATE product, a_course_text SET tid4=product.id WHERE product.name=a_course_text.tname4 ;
UPDATE product, a_course_text SET tid5=product.id WHERE product.name=a_course_text.tname5 ;

SELECT account_id, course_id, course_name, tid1, tname1 FROM a_course_text WHERE tname1!=''; 
SELECT account_id, course_id, course_name, tid2, tname2 FROM a_course_text WHERE tname2!=''; 
SELECT account_id, course_id, course_name, tid3, tname3 FROM a_course_text WHERE tname3!=''; 
SELECT account_id, course_id, course_name, tid4, tname4 FROM a_course_text WHERE tname4!=''; 
SELECT account_id, course_id, course_name, tid5, tname5 FROM a_course_text WHERE tname5!=''; 



CREATE TABLE IF NOT EXISTS `a_course_td` (
  `account_id` varchar(36) DEFAULT NULL,
  `course_id` varchar(100) DEFAULT NULL,
  `course_name` varchar(256) NOT NULL,
  `tid` varchar(100) DEFAULT NULL,
  `tname` varchar(256) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

SELECT * FROM a_course_td WHERE tid='' ORDER BY tname

UPDATE a_course_td SET tid='35330182' WHERE tname='IBSP - Pre-Intermediate' AND tid=''

SELECT id, name FROM `product` WHERE `name` LIKE '%IBSP%' ORDER BY `product`.`name` ASC



CREATE TABLE IF NOT EXISTS `product_course` (
  `id` varchar(36) NOT NULL DEFAULT '',
  `product_id` varchar(36) DEFAULT NULL,
  `event_id` varchar(36) DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `product_id_event_id` (`product_id`,`event_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `product_student` (
  `id` varchar(36) NOT NULL DEFAULT '',
  `product_id` varchar(36) DEFAULT NULL,
  `contact_id` varchar(36) DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `product_id_contact_id` (`product_id`,`contact_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


SELECT product_student.*
FROM product_student
LEFT JOIN contacts ON product_student.contact_id=contacts.id 
WHERE contacts.id  IS NULL AND product_student.deleted=0

SELECT id, deleted FROM product_student WHERE id < 'st_0251'
UPDATE product_student SET deleted=1 WHERE id < 'st_0251'


SELECT id, name FROM product, a_student_td
WHERE product.name=a_student_td.tname

UPDATE product, a_student_td SET tid=id WHERE product.name=a_student_td.tname

SELECT id, name FROM `product` WHERE `name` LIKE '%Meetings%'

UPDATE a_student_td SET tid='35330630' WHERE tname='Teleconferencing Intermediate'



SELECT syllabus_id, event_contacts.description, start_date
FROM smsevent, event_contacts
WHERE smsevent.id=event_contacts.event_id
AND smsevent.custom_var77 IS NOT NULL
AND event_contacts.start_date IS NULL
AND smsevent.id='csdb_0492'


UPDATE smsevent, event_contacts
SET event_contacts.description=syllabus_id
WHERE smsevent.id=event_contacts.event_id
AND smsevent.custom_var77 IS NOT NULL
AND event_contacts.start_date IS NULL
AND smsevent.id='csdb_0492'

SELECT * FROM `smsevent_instances` WHERE `status` !=0
UPDATE smsevent_instances SET status=1025 WHERE `status` !=0

UPDATE sms_event_instance SET reason='##事前キャンセル##' WHERE status='64' ;
UPDATE sms_event_instance SET reason='##天災キャンセル##' WHERE status='131072' ;
UPDATE sms_event_instance SET reason='##依頼キャンセル##' WHERE status='262144' ;
UPDATE sms_event_instance SET reason='##当日キャンセル##' WHERE status='524288' ;


UPDATE sms_event_instance SET status=1025 WHERE `status` !=0

事前キャンセル				64
天災キャンセル				131072
依頼キャンセル				262144
当日キャンセル				524288


SELECT * FROM event_contacts WHERE start_date IS NOT NULL AND end_date IS NOT NULL AND description IS NOT NULL AND date_modified='2012-08-17 10:10:00' 

UPDATE event_contacts SET description=NULL WHERE start_date IS NOT NULL AND end_date IS NOT NULL AND description IS NOT NULL AND date_modified='2012-08-17 10:10:00' 


SELECT last_name, first_name, first_name_furigana, idnumber FROM contacts
WHERE idnumber < '1000299' AND school_id='2d4bc5bb-57be-c6cb-723e-502ddd73996b' 


SELECT last_name, first_name, first_name_furigana, idnumber FROM contacts
WHERE idnumber < '1005000' AND idnumber > '1000299' AND school_id='2d4bc5bb-57be-c6cb-723e-502ddd73996b' 

UPDATE contacts SET first_name_furigana=first_name WHERE idnumber < '1005000' AND idnumber > '1000299' AND school_id='2d4bc5bb-57be-c6cb-723e-502ddd73996b' ;
UPDATE contacts SET first_name=last_name WHERE idnumber < '1005000' AND idnumber > '1000299' AND school_id='2d4bc5bb-57be-c6cb-723e-502ddd73996b' ;
UPDATE contacts SET last_name=first_name_furigana WHERE idnumber < '1005000' AND idnumber > '1000299' AND school_id='2d4bc5bb-57be-c6cb-723e-502ddd73996b' ;
UPDATE contacts SET first_name_furigana=NULL WHERE idnumber < '1005000' AND idnumber > '1000299' AND school_id='2d4bc5bb-57be-c6cb-723e-502ddd73996b' ;


SELECT contact_status, last_name, first_name FROM contacts
WHERE smsgroup='teacher' AND contact_status='3' AND school_id='2d4bc5bb-57be-c6cb-723e-502ddd73996b' ;

UPDATE contacts SET contact_status='4' WHERE smsgroup='teacher' AND contact_status='3' AND school_id='2d4bc5bb-57be-c6cb-723e-502ddd73996b' ;


-- 2012-09-09 existing teacher updates

SELECT * FROM `contacts` WHERE smsgroup='teacher' and date_entered='2012-08-17 12:00:00'

	Edit 	Delete 	J4027 			66af668c-28ef-d661-d2cb-4ea144832f40 	Troy 	Charles
	Edit 	Delete 	P111025R 	75945ebb-90eb-4628-b933-4ea1440c0c55 	Mohammadi 	Raha
	Edit 	Delete 	P4378 			7058b053-bd8f-8907-f4fa-4ea14467463d 	Lane 	Kenneth
	Edit 	Delete 	J4222 			1501b7aa-0fd3-7880-5bc5-4ea1432e12ec 	Kondo 	Oto
	Edit 	Delete 	J4085 			3e3300d8-0638-7bbe-ae01-4ea143a437c3 	Harmel 	Ken
	Edit 	Delete 	J4297 			552d6ef4-2db8-39fb-75be-4ea143dbfc5f 	Concannon 	Tony
	Edit 	Delete 	P1101V 		14808f46-bf11-6b18-ab94-4ea143ffe09d 	Christiansen 	Victor
	Edit 	Delete 	J3768 			a8adba00-880b-470a-17bd-4ea143e50442 	Carroll 	John

SELECT id, custom_var10, last_name, first_name FROM `contacts` WHERE smsgroup='teacher' and date_entered='2012-08-17 12:00:00'

SELECT event_contacts.*, contacts.custom_var10 FROM event_contacts, smsevent, contacts
WHERE event_contacts.event_id=smsevent.id AND event_contacts.contact_id=contacts.id
AND smsevent.school_id = '2d4bc5bb-57be-c6cb-723e-502ddd73996b' 
AND event_contacts.contact_id IN ('66af668c-28ef-d661-d2cb-4ea144832f40', 'a8adba00-880b-470a-17bd-4ea143e50442', '3e3300d8-0638-7bbe-ae01-4ea143a437c3', '1501b7aa-0fd3-7880-5bc5-4ea1432e12ec', '552d6ef4-2db8-39fb-75be-4ea143dbfc5f', '7058b053-bd8f-8907-f4fa-4ea14467463d', '75945ebb-90eb-4628-b933-4ea1440c0c55', '14808f46-bf11-6b18-ab94-4ea143ffe09d')


UPDATE event_contacts, smsevent, contacts SET event_contacts.contact_id='J4027'
WHERE event_contacts.event_id=smsevent.id AND event_contacts.contact_id=contacts.id
AND smsevent.school_id = '2d4bc5bb-57be-c6cb-723e-502ddd73996b' AND event_contacts.contact_id ='66af668c-28ef-d661-d2cb-4ea144832f40'; 
 
UPDATE event_contacts, smsevent, contacts SET event_contacts.contact_id='P111025R'
WHERE event_contacts.event_id=smsevent.id AND event_contacts.contact_id=contacts.id
AND smsevent.school_id = '2d4bc5bb-57be-c6cb-723e-502ddd73996b' AND event_contacts.contact_id ='75945ebb-90eb-4628-b933-4ea1440c0c55'; 
 
UPDATE event_contacts, smsevent, contacts SET event_contacts.contact_id='P4378'
WHERE event_contacts.event_id=smsevent.id AND event_contacts.contact_id=contacts.id
AND smsevent.school_id = '2d4bc5bb-57be-c6cb-723e-502ddd73996b' AND event_contacts.contact_id ='7058b053-bd8f-8907-f4fa-4ea14467463d'; 
 
UPDATE event_contacts, smsevent, contacts SET event_contacts.contact_id='J4222'
WHERE event_contacts.event_id=smsevent.id AND event_contacts.contact_id=contacts.id
AND smsevent.school_id = '2d4bc5bb-57be-c6cb-723e-502ddd73996b' AND event_contacts.contact_id ='1501b7aa-0fd3-7880-5bc5-4ea1432e12ec'; 
 
UPDATE event_contacts, smsevent, contacts SET event_contacts.contact_id='J4085'
WHERE event_contacts.event_id=smsevent.id AND event_contacts.contact_id=contacts.id
AND smsevent.school_id = '2d4bc5bb-57be-c6cb-723e-502ddd73996b' AND event_contacts.contact_id ='3e3300d8-0638-7bbe-ae01-4ea143a437c3'; 
 
UPDATE event_contacts, smsevent, contacts SET event_contacts.contact_id='J4297'
WHERE event_contacts.event_id=smsevent.id AND event_contacts.contact_id=contacts.id
AND smsevent.school_id = '2d4bc5bb-57be-c6cb-723e-502ddd73996b' AND event_contacts.contact_id ='552d6ef4-2db8-39fb-75be-4ea143dbfc5f'; 
 
UPDATE event_contacts, smsevent, contacts SET event_contacts.contact_id='P1101V'
WHERE event_contacts.event_id=smsevent.id AND event_contacts.contact_id=contacts.id
AND smsevent.school_id = '2d4bc5bb-57be-c6cb-723e-502ddd73996b' AND event_contacts.contact_id ='14808f46-bf11-6b18-ab94-4ea143ffe09d'; 
 
UPDATE event_contacts, smsevent, contacts SET event_contacts.contact_id='J3768'
WHERE event_contacts.event_id=smsevent.id AND event_contacts.contact_id=contacts.id
AND smsevent.school_id = '2d4bc5bb-57be-c6cb-723e-502ddd73996b' AND event_contacts.contact_id ='a8adba00-880b-470a-17bd-4ea143e50442'; 
 

SELECT smsevent_instances.*, contacts.custom_var10 FROM smsevent_instances, smsevent, contacts
WHERE smsevent_instances.event_id=smsevent.id AND smsevent_instances.target_id=contacts.id
AND smsevent.school_id = '2d4bc5bb-57be-c6cb-723e-502ddd73996b' 
AND smsevent_instances.target_id IN ('66af668c-28ef-d661-d2cb-4ea144832f40', 'a8adba00-880b-470a-17bd-4ea143e50442', '3e3300d8-0638-7bbe-ae01-4ea143a437c3', '1501b7aa-0fd3-7880-5bc5-4ea1432e12ec', '552d6ef4-2db8-39fb-75be-4ea143dbfc5f', '7058b053-bd8f-8907-f4fa-4ea14467463d', '75945ebb-90eb-4628-b933-4ea1440c0c55', '14808f46-bf11-6b18-ab94-4ea143ffe09d')


UPDATE smsevent_instances, smsevent, contacts SET smsevent_instances.target_id='J4027'
WHERE smsevent_instances.event_id=smsevent.id AND smsevent_instances.target_id=contacts.id
AND smsevent.school_id = '2d4bc5bb-57be-c6cb-723e-502ddd73996b' AND smsevent_instances.target_id ='66af668c-28ef-d661-d2cb-4ea144832f40'; 
 
UPDATE smsevent_instances, smsevent, contacts SET smsevent_instances.target_id='P111025R'
WHERE smsevent_instances.event_id=smsevent.id AND smsevent_instances.target_id=contacts.id
AND smsevent.school_id = '2d4bc5bb-57be-c6cb-723e-502ddd73996b' AND smsevent_instances.target_id ='75945ebb-90eb-4628-b933-4ea1440c0c55'; 
 
UPDATE smsevent_instances, smsevent, contacts SET smsevent_instances.target_id='P4378'
WHERE smsevent_instances.event_id=smsevent.id AND smsevent_instances.target_id=contacts.id
AND smsevent.school_id = '2d4bc5bb-57be-c6cb-723e-502ddd73996b' AND smsevent_instances.target_id ='7058b053-bd8f-8907-f4fa-4ea14467463d'; 
 
UPDATE smsevent_instances, smsevent, contacts SET smsevent_instances.target_id='J4222'
WHERE smsevent_instances.event_id=smsevent.id AND smsevent_instances.target_id=contacts.id
AND smsevent.school_id = '2d4bc5bb-57be-c6cb-723e-502ddd73996b' AND smsevent_instances.target_id ='1501b7aa-0fd3-7880-5bc5-4ea1432e12ec'; 
 
UPDATE smsevent_instances, smsevent, contacts SET smsevent_instances.target_id='J4085'
WHERE smsevent_instances.event_id=smsevent.id AND smsevent_instances.target_id=contacts.id
AND smsevent.school_id = '2d4bc5bb-57be-c6cb-723e-502ddd73996b' AND smsevent_instances.target_id ='3e3300d8-0638-7bbe-ae01-4ea143a437c3'; 
 
UPDATE smsevent_instances, smsevent, contacts SET smsevent_instances.target_id='J4297'
WHERE smsevent_instances.event_id=smsevent.id AND smsevent_instances.target_id=contacts.id
AND smsevent.school_id = '2d4bc5bb-57be-c6cb-723e-502ddd73996b' AND smsevent_instances.target_id ='552d6ef4-2db8-39fb-75be-4ea143dbfc5f'; 
 
UPDATE smsevent_instances, smsevent, contacts SET smsevent_instances.target_id='P1101V'
WHERE smsevent_instances.event_id=smsevent.id AND smsevent_instances.target_id=contacts.id
AND smsevent.school_id = '2d4bc5bb-57be-c6cb-723e-502ddd73996b' AND smsevent_instances.target_id ='14808f46-bf11-6b18-ab94-4ea143ffe09d'; 
 
UPDATE smsevent_instances, smsevent, contacts SET smsevent_instances.target_id='J3768'
WHERE smsevent_instances.event_id=smsevent.id AND smsevent_instances.target_id=contacts.id
AND smsevent.school_id = '2d4bc5bb-57be-c6cb-723e-502ddd73996b' AND smsevent_instances.target_id ='a8adba00-880b-470a-17bd-4ea143e50442';