
CREATE TABLE IF NOT EXISTS `sms_event_instance_sub` (
  `instance_id` varchar(36) NOT NULL,
  `ins_order` int(11) default NULL,
  `date_modified` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`instance_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `manual_contact` (
  `id` varchar(36) NOT NULL DEFAULT '',
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `date_entered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified_user_id` varchar(36) DEFAULT NULL,
  `assigned_user_id` varchar(36) DEFAULT NULL,
  `created_by` varchar(36) DEFAULT NULL,
  `salutation` varchar(5) DEFAULT NULL,
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `first_name_furigana` varchar(100) DEFAULT NULL,
  `last_name_furigana` varchar(100) DEFAULT NULL,
  `lead_source` varchar(100) DEFAULT NULL,
  `lead_source_description` varchar(64) DEFAULT NULL,
  `title` varchar(50) DEFAULT NULL,
  `department` varchar(100) DEFAULT NULL,
  `reports_to_id` varchar(36) DEFAULT NULL,
  `commission_company_id` varchar(36) DEFAULT NULL,
  `birthdate` date DEFAULT NULL,
  `do_not_call` varchar(3) DEFAULT '0',
  `phone_home` varchar(25) DEFAULT NULL,
  `phone_mobile` varchar(25) DEFAULT NULL,
  `phone_work` varchar(25) DEFAULT NULL,
  `phone_other` varchar(25) DEFAULT NULL,
  `phone_fax` varchar(25) DEFAULT NULL,
  `email1` varchar(100) DEFAULT NULL,
  `email2` varchar(100) DEFAULT NULL,
  `assistant` varchar(75) DEFAULT NULL,
  `assistant_phone` varchar(25) DEFAULT NULL,
  `email_opt_out` varchar(3) DEFAULT '0',
  `primary_address_street` varchar(150) DEFAULT NULL,
  `primary_address_city` varchar(100) DEFAULT NULL,
  `primary_address_state` varchar(100) DEFAULT NULL,
  `primary_address_postalcode` varchar(20) DEFAULT NULL,
  `primary_address_country` varchar(100) DEFAULT NULL,
  `primary_address_kana` varchar(250) DEFAULT NULL,
  `alt_address_street` varchar(150) DEFAULT NULL,
  `alt_address_city` varchar(100) DEFAULT NULL,
  `alt_address_state` varchar(100) DEFAULT NULL,
  `alt_address_postalcode` varchar(20) DEFAULT NULL,
  `alt_address_country` varchar(100) DEFAULT NULL,
  `alt_address_kana` varchar(250) DEFAULT NULL,
  `description` text,
  `portal_name` varchar(255) DEFAULT NULL,
  `portal_active` tinyint(1) NOT NULL DEFAULT '0',
  `portal_app` varchar(255) DEFAULT NULL,
  `invalid_email` tinyint(1) DEFAULT '0',
  `smsgroup` varchar(15) NOT NULL DEFAULT '',
  `contact_status` varchar(3) NOT NULL DEFAULT '0',
  `picture_filename` varchar(255) DEFAULT NULL,
  `picture_file_mime_type` varchar(100) DEFAULT NULL,
  `school_id` varchar(36) NOT NULL DEFAULT 'sssssss-1111',
  `vacation_total` float DEFAULT NULL,
  `vacation_used` float DEFAULT NULL,
  `is_available_strict` tinyint(1) NOT NULL DEFAULT '0',
  `major` varchar(32) DEFAULT NULL,
  `send_document_home` varchar(3) NOT NULL DEFAULT 'on',
  `send_document_work` varchar(3) NOT NULL DEFAULT 'on',
  `company_name` varchar(100) DEFAULT NULL,
  `idnumber` varchar(32) DEFAULT NULL,
  `nationality` varchar(16) DEFAULT NULL,
  `gender` varchar(8) DEFAULT NULL,
  `work_school` varchar(64) DEFAULT NULL,
  `last_school_name` varchar(64) DEFAULT NULL,
  `last_school_dept` varchar(64) DEFAULT NULL,
  `study_aim` varchar(64) DEFAULT NULL,
  `transport_fee_type` varchar(16) DEFAULT NULL,
  `transportfee` int(11) DEFAULT NULL,
  `incometax_rate` float NOT NULL DEFAULT '0.1',
  `leave_date` date DEFAULT NULL,
  `enroll_date` date DEFAULT NULL,
  `grade` varchar(10) DEFAULT NULL,
  `grade_entrance_date` date DEFAULT NULL,
  `application_date` date DEFAULT NULL,
  `extra_date` date DEFAULT NULL,
  `id_card_field1` text,
  `id_card_field2` varchar(50) DEFAULT NULL,
  `id_card_field3` varchar(50) DEFAULT NULL,
  `id_card_field4` text,
  `renmei_label` text,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `custom_var1` text,
  `custom_var2` text,
  `custom_var3` text,
  `custom_var4` text,
  `custom_var5` text,
  `custom_var6` text,
  `custom_var7` text,
  `custom_var8` text,
  `custom_var9` text,
  `custom_var10` text,
  `custom_var11` text,
  `custom_var12` text,
  `custom_var13` text,
  `custom_var14` text,
  `custom_var15` text,
  `custom_var16` text,
  `custom_var17` text,
  `custom_var18` text,
  `custom_var19` text,
  `custom_var20` text,
  `raw_import_data` text,
  `import_serial_no` int(11) NOT NULL AUTO_INCREMENT,
  `import_source` varchar(50) DEFAULT NULL,
  `user_password` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`import_serial_no`),
  UNIQUE KEY `id` (`id`),
  KEY `last_name` (`last_name`,`status`),
  KEY `last_name_furigana` (`last_name_furigana`),
  KEY `first_name` (`first_name`),
  KEY `first_name_furigana` (`first_name_furigana`),
  KEY `email` (`email1`),
  KEY `tel1` (`phone_home`),
  KEY `tel2` (`phone_other`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--- ANPI KAKUNIN

CREATE TABLE IF NOT EXISTS `poll` (
  `id` varchar(36) NOT NULL DEFAULT '',
  `name` varchar(255) NOT NULL,
  `poll_total_votes` int(11) NOT NULL DEFAULT '0',
  `poll_status` enum('enabled','disabled') NOT NULL DEFAULT 'disabled',
  `poll_vote_type` enum('single','multi') NOT NULL DEFAULT 'single',
  `poll_category` varchar(20) DEFAULT NULL,
  `poll_view` enum('public','member') NOT NULL DEFAULT 'member',
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  `description` text,
  `date_entered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified_user_id` varchar(36) NOT NULL,
  `created_by` varchar(36) NOT NULL,
  `school_id` varchar(36) NOT NULL,
  `target_language` varchar(6) DEFAULT NULL,
  `target_voter_type` varchar(50) DEFAULT NULL,
  `target_voter` varchar(255) DEFAULT NULL,
  `deleted` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `poll`
--

INSERT INTO `poll` (`id`, `name`, `poll_total_votes`, `poll_status`, `poll_vote_type`, `poll_category`, `poll_view`, `start_date`, `end_date`, `description`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `school_id`, `target_language`, `target_voter_type`, `target_voter`, `deleted`) VALUES
('962c365b-c004-d450-7509-4e917b129551', '安否確認', 0, 'enabled', 'single', 'anpikakunin', 'member', NULL, NULL, NULL, '2011-10-09 10:46:50', '2011-10-09 10:47:34', '1', '1', 'sssssss-1111', NULL, 'Student', NULL, 0);


CREATE TABLE IF NOT EXISTS `poll_question` (
  `id` varchar(36) NOT NULL DEFAULT '',
  `poll_id` varchar(36) NOT NULL,
  `name` text NOT NULL,
  `ans_options` text,
  `results` text,
  `total_votes` int(11) NOT NULL DEFAULT '0',
  `question_type` enum('single','multi','text') NOT NULL DEFAULT 'single',
  `question_order` int(11) DEFAULT NULL,
  `deleted` tinyint(4) NOT NULL DEFAULT '0',
  `date_entered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified_user_id` varchar(36) NOT NULL,
  `created_by` varchar(36) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `poll_id` (`poll_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `poll_question`
--

INSERT INTO `poll_question` (`id`, `poll_id`, `name`, `ans_options`, `results`, `total_votes`, `question_type`, `question_order`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`) VALUES
('d28e7b71-ad4c-09e9-6e82-4e917c8254ce', '962c365b-c004-d450-7509-4e917b129551', 'あなたの安否を教えてください', '無事<delim>軽傷<delim>重傷', '0;0;0', 0, 'single', 0, 0, '2011-10-09 10:48:38', '2011-10-09 10:48:38', '1', '1'),
('4fc0fcff-854e-7fb0-aef2-4e917c905917', '962c365b-c004-d450-7509-4e917b129551', '現在の所在地を教えてください', '社内<delim>自宅<delim>外出中', '0;0;0', 0, 'single', 0, 0, '2011-10-09 10:50:16', '2011-10-09 10:50:16', '1', '1'),
('450993fe-46b9-5873-4a2c-4e917ca560c4', '962c365b-c004-d450-7509-4e917b129551', '家族の安否を教えてください', '全員無事<delim>負傷者等あり<delim>不明', '0;0;0', 0, 'single', 0, 0, '2011-10-09 10:51:16', '2011-10-09 10:51:16', '1', '1'),
('29421454-a6ca-6d19-98b1-4e917cdc89b4', '962c365b-c004-d450-7509-4e917b129551', '出社可否を教えてください', '可<delim>不可<delim>微妙', '0;0;0', 0, 'single', 0, 0, '2011-10-09 10:51:53', '2011-10-09 10:51:53', '1', '1'),
('3f9560eb-6d41-8e84-60e3-4e917d52e941', '962c365b-c004-d450-7509-4e917b129551', 'その他会社への報告・連絡・相談があれば記述してください', NULL, NULL, 0, 'text', 0, 0, '2011-10-09 10:53:01', '2011-10-09 10:53:01', '1', '1');


CREATE TABLE IF NOT EXISTS `poll_vote` (
  `id` varchar(36) NOT NULL,
  `poll_id` varchar(36) NOT NULL,
  `submit_id` varchar(36) NOT NULL,
  `contact_id` varchar(36) DEFAULT NULL,
  `answers` text NOT NULL,
  `vote_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `ip_address` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `poll_id` (`poll_id`),
  KEY `vote_date` (`vote_date`),
  KEY `submit_id` (`submit_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- ikkatsu email settings


CREATE TABLE IF NOT EXISTS `emailman` (
  `date_entered` datetime default NULL,
  `date_modified` datetime default NULL,
  `user_id` varchar(36) default NULL,
  `id` int(11) NOT NULL auto_increment,
  `campaign_id` varchar(36) default NULL,
  `marketing_id` varchar(36) default NULL,
  `list_id` varchar(36) default NULL,
  `send_date_time` datetime default NULL,
  `modified_user_id` varchar(36) default NULL,
  `in_queue` tinyint(1) default '0',
  `in_queue_date` datetime default NULL,
  `send_attempts` int(11) default '0',
  `deleted` tinyint(1) default '0',
  `related_id` varchar(36) default NULL,
  `related_type` varchar(100) default NULL,
  `email_type` varchar(50) NOT NULL default 'email1',
  `send_options` int(11) NOT NULL default '0',
  `status` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `idx_eman_list` (`list_id`,`user_id`,`deleted`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
INSERT INTO `emailman` (`date_entered`, `date_modified`, `user_id`, `id`, `campaign_id`, `marketing_id`, `list_id`, `send_date_time`, `modified_user_id`, `in_queue`, `in_queue_date`, `send_attempts`, `deleted`, `related_id`, `related_type`, `email_type`, `send_options`, `status`) VALUES
('2011-10-19 05:59:18', NULL, '1', 3, NULL, '8cc9f30f-2828-eca6-7714-4e9e67245069', '4074eaa9-e603-d7a3-f0e5-4e9e6408138d', '2011-10-19 05:59:18', NULL, 0, NULL, 0, 0, '8dbe05f6-96dd-c3a9-a582-4e58a30165b1', 'Contacts', 'email1', 0, 0),
('2011-10-19 05:59:18', NULL, '1', 4, NULL, '8cc9f30f-2828-eca6-7714-4e9e67245069', '4074eaa9-e603-d7a3-f0e5-4e9e6408138d', '2011-10-19 05:59:18', NULL, 0, NULL, 0, 0, '1497c89f-6951-0667-2f22-4e587ee1ba12', 'Contacts', 'email1', 0, 0);



INSERT INTO `inbound_email` (`id`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `name`, `status`, `server_url`, `email_user`, `email_password`, `port`, `service`, `mailbox`, `delete_seen`, `mailbox_type`, `template_id`, `stored_options`, `group_id`) VALUES
('d1f90bbb-d603-2f-da44-441fc60da065', 1, '2006-03-21 09:26:51', '2007-05-23 04:14:26', '1', '1', 'TechnoSMS', 'Active', 'mail.anzenhosting.jp', 'technosms@technopian.com', 'yeVdrSuObvqOah4nFSEwMA==', 110, '::::::pop3::::notls::', 'INBOX', 0, NULL, NULL, 'YTo0OntzOjk6ImZyb21fbmFtZSI7czowOiIiO3M6OToiZnJvbV9hZGRyIjtzOjA6IiI7czoxMDoib25seV9zaW5jZSI7YjowO3M6MTM6ImZpbHRlcl9kb21haW4iO3M6MDoiIjt9', '3c801f9b-cf28-fe53-cd5d-4653bfe5220a');


INSERT INTO `inbound_email` (`id`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `name`, `status`, `server_url`, `email_user`, `email_password`, `port`, `service`, `mailbox`, `delete_seen`, `mailbox_type`, `template_id`, `stored_options`, `group_id`) VALUES
('d1f90bbb-d603-cb2f-da44-441fc60da065', 0, '2006-03-21 09:26:51', '2011-10-19 05:55:15', '1', '1', 'Caplan server', 'Active', 'mail.caplan.jp', 'technosms', 'o6g1AQh/gqP9Whtk/ziQWA==', 25, '::::::pop3::::notls::', 'INBOX', 0, NULL, NULL, 'YTo0OntzOjk6ImZyb21fbmFtZSI7czowOiIiO3M6OToiZnJvbV9hZGRyIjtzOjA6IiI7czoxMDoib25seV9zaW5jZSI7YjowO3M6MTM6ImZpbHRlcl9kb21haW4iO3M6MDoiIjt9', '3c801f9b-cf28-fe53-cd5d-4653bfe5220a');

INSERT INTO `inbound_email` (`id`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `name`, `status`, `server_url`, `email_user`, `email_password`, `port`, `service`, `mailbox`, `delete_seen`, `mailbox_type`, `template_id`, `stored_options`, `group_id`) VALUES
('d2f90bbb-d603-cb2f-da44-441fc60da065', 0, '2006-03-21 09:26:51', '2007-05-23 04:14:26', '1', '1', 'TechnoSMS2', 'Active', 'mail.anzenhosting.jp', 'technosms@technopian.com', 'yeVdrSuObvqOah4nFSEwMA==', 110, '::::::pop3::::notls::', 'INBOX', 0, 'bounce', '', 'YTo0OntzOjk6ImZyb21fbmFtZSI7czowOiIiO3M6OToiZnJvbV9hZGRyIjtzOjA6IiI7czoxMDoib25seV9zaW5jZSI7YjowO3M6MTM6ImZpbHRlcl9kb21haW4iO3M6MDoiIjt9', '3c801f9b-cf28-fe53-cd5d-4653bfe5220a');

-- clearing the DB

DELETE FROM `contacts` WHERE `id` NOT IN ('3dce531d-e31a-b0c7-68ed-4e570466285e', 'be59a6ae-2ceb-667f-5ba0-45b03573f1ac');
DELETE FROM users WHERE contact_id NOT IN ('3dce531d-e31a-b0c7-68ed-4e570466285e', 'be59a6ae-2ceb-667f-5ba0-45b03573f1ac');
DELETE FROM users_schools WHERE user_id  NOT IN ('93c64d5b-e654-6511-88f9-4e5704efed84', '1');
DELETE FROM acl_roles_users WHERE user_id  NOT IN ('93c64d5b-e654-6511-88f9-4e5704efed84', '1');

-- check number of counts for classes : this all should be 16

SELECT count(sms_event_instance.id) as class_count, smsevent.name as course_name FROM sms_event_instance 
INNER JOIN smsevent ON sms_event_instance.parent_id=smsevent.id AND smsevent.category='course'
GROUP BY sms_event_instance.parent_id ;

-- update contact data 
SELECT id, department, smsgroup, 
CONCAT(SUBSTRING(department, 1, LOCATE('?出向', department)-1 ),'（株）',SUBSTRING(department, LOCATE('?出向', department)+1)) as new_department
FROM `contacts` WHERE department LIKE '%?出向%'

UPDATE contacts SET department = CONCAT(SUBSTRING(department, 1, LOCATE('?出向', department)-1 ),'（株）',SUBSTRING(department, LOCATE('?出向', department)+1)) WHERE department LIKE '%?出向%'

-- crontab
0,5,10,15,20,25,30,35,40,45,50,55 * * * * cd /var/www/html; /usr/bin/php /var/www/html/emailmandelivery.php;


0,5,10,15,20,25,30,35,40,45,50,55 * * * * cd /var/www/html; /usr/bin/php /var/www/html/emailmandelivery.php;
08 17 * * * cd /var/www/html; /usr/bin/php /var/www/html/course_reminder_email_cron.php;


cd /var/www/html/test; /usr/bin/php /var/www/html/test/emailmandelivery.php;

-- rescheduling to an already cancelled class problem

SELECT from_datetime, parent_id, id as instance_id, count(from_datetime) as timecount
FROM sms_event_instance WHERE event_type='course' 
GROUP BY parent_id, from_datetime HAVING count(from_datetime) >1 
ORDER BY from_datetime


SELECT from_datetime, instance_id, event_id, target_id, count(from_datetime) as timecount
FROM smsevent_instances WHERE event_type='course' AND category='teacher' 
AND (smsevent_instances.status & 1024) = 0
GROUP BY target_id, from_datetime HAVING count(from_datetime) >1 
ORDER BY from_datetime

-- deleted class instances

-- course_id			605c2aa6-382d-b930-56a0-4eaa37a0d940
-- student_id 			1e125299-bb3b-07cb-5a58-4ea12b0d3720
--		instance_id	6284a4cc-f615-6d68-a349-4eaa3873f097
--		instance_id	e4b0adc0-ccc5-3410-e366-4eaa38ed7f03

SELECT *
FROM `smsevent_instances`
WHERE `target_id` LIKE '7f3f76a7-c6df-70f6-8cae-4ea13cf513d3'
AND `from_datetime` > '2011-11-22 00:00:00'
AND `to_datetime` < '2011-11-22 20:54:52'

-- available time categorizing according to the target classroom

SELECT smsevent.school_id, resource.school_id, from_datetime, to_datetime
FROM smsevent_instances
INNER JOIN smsevent ON smsevent_instances.event_id=smsevent.id AND smsevent.deleted=0
INNER JOIN resource ON smsevent_instances.target_id=resource.id AND resource.deleted=0
WHERE event_type='available' AND smsevent_instances.category='classroom'


SELECT smsevent.school_id, resource.school_id, from_datetime, to_datetime, smsevent.name, smsevent.category, resource.name
FROM smsevent_instances, smsevent, resource
WHERE smsevent_instances.event_id=smsevent.id AND smsevent.deleted=0
	AND smsevent_instances.target_id=resource.id AND resource.deleted=0
	AND event_type='available' AND smsevent_instances.category='classroom'

UPDATE smsevent_instances, smsevent, resource
SET smsevent.school_id=resource.school_id
WHERE smsevent_instances.event_id=smsevent.id AND smsevent.deleted=0
	AND smsevent_instances.target_id=resource.id AND resource.deleted=0
	AND event_type='available' AND smsevent_instances.category='classroom'


SELECT smsevent.school_id course_school, resource.school_id resource_school, 
	smsevent.name, smsevent.category, resource.name,
	in_st.instance_id as instance_id,
	SUBSTRING(DATE_ADD(in_st.from_datetime, INTERVAL 540 MINUTE), 1, 16) AS from_datetime,
	SUBSTRING(DATE_ADD(in_st.to_datetime, INTERVAL 540 MINUTE), 1, 16) AS to_datetime,
	contacts.last_name, contacts.first_name, contacts.idnumber, in_st.status
FROM smsevent_instances in_st, smsevent, resource, smsevent_instances in_re, contacts
WHERE in_re.event_id=smsevent.id AND smsevent.deleted=0
	AND in_re.target_id=resource.id AND resource.deleted=0
	AND in_re.event_type='course' AND in_re.category='classroom'
	AND in_st.instance_id=in_re.instance_id AND in_st.target_id=contacts.id AND in_st.category='student'
	AND smsevent.school_id!=resource.school_id
	
-- courses with furikaesaki non-existing due to non-existing sms_recurrence data
SELECT smsevent.id, smsevent.name, sms_recurrence.id FROM `smsevent` 
LEFT JOIN sms_recurrence ON smsevent.id=sms_recurrence.id 
WHERE smsevent.deleted=0 AND sms_recurrence.id=NULL

-- investigate English private course of which count is not 18
SELECT count(sms_event_instance.id) as class_count, smsevent.name as course_name, smsevent.id as course_id FROM sms_event_instance 
INNER JOIN smsevent ON sms_event_instance.parent_id=smsevent.id AND smsevent.category='course' AND subtype='2'
WHERE (sms_event_instance.status & 1088 ) = 0
GROUP BY sms_event_instance.parent_id HAVING count(sms_event_instance.id) !=18

-- new course fields for email sending

SELECT id, name, subtype, custom_var39 FROM smsevent WHERE subtype=1; 
SELECT id, name, subtype, custom_var39 FROM smsevent WHERE subtype=2; 

UPDATE smsevent SET custom_var39='第四期中国語レッスン' WHERE subtype=1; 
UPDATE smsevent SET custom_var39='2011年下期新英語レッスン' WHERE subtype=2; 

07 17 * * * cd /var/www/html; /usr/bin/php /var/www/html/course_reminder_email_cron.php;
0,5,10,15,20,25,30,35,40,45,50,55 * * * * cd /var/www/html; /usr/bin/php /var/www/html/emailmandelivery.php;

-- 2012-3-25  custom var for course
ALTER TABLE `smsevent` ADD `custom_var41` TEXT NULL AFTER `custom_var40`;

-- 2012-03-26 course account link
ALTER TABLE `smsevent` ADD `course_account_id` VARCHAR( 36 ) NULL DEFAULT NULL ;

-- 2012-04-24 course koukai settei change

SELECT name, school_id, custom_var36 FROM smsevent
WHERE school_id IN ('b4a02e2b-7b90-b4f8-e325-4e5703fe2e61', '3094ae2c-d5cf-56ae-a54b-4e5704b2c4a4', 'c9a7ba3e-d866-1e08-abf1-4e57049757a6', 'd2f5eef1-7ce9-9b22-fb29-4ea0bb6d46bc')
AND custom_var36='非公開' AND deleted=0;

UPDATE smsevent SET custom_var36=NULL 
WHERE school_id IN ('b4a02e2b-7b90-b4f8-e325-4e5703fe2e61', '3094ae2c-d5cf-56ae-a54b-4e5704b2c4a4', 'c9a7ba3e-d866-1e08-abf1-4e57049757a6', 'd2f5eef1-7ce9-9b22-fb29-4ea0bb6d46bc')
AND custom_var36='非公開' AND deleted=0;



-- 2012-08-27 honban upload

ALTER TABLE `exam` DROP `exam_takable_by` ;
ALTER TABLE `exam` ADD `kamoku_name` VARCHAR( 50 ) NULL DEFAULT NULL AFTER `resource_id` ;
ALTER TABLE `exam` ADD `nendo` VARCHAR( 16 ) NULL DEFAULT NULL AFTER `max_point` ;
ALTER TABLE `batch_manager` CHANGE `category` `category` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ;

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 ;
ALTER TABLE `smsevent` ADD `custom_var78` TEXT NULL DEFAULT NULL ;

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

-- 2012-10-22 course koukai settei change

SELECT name, school_id, custom_var36, custom_var39, date_entered FROM smsevent
WHERE school_id IN ('b4a02e2b-7b90-b4f8-e325-4e5703fe2e61', '3094ae2c-d5cf-56ae-a54b-4e5704b2c4a4', 'c9a7ba3e-d866-1e08-abf1-4e57049757a6', 'd2f5eef1-7ce9-9b22-fb29-4ea0bb6d46bc')
AND custom_var36='非公開' AND deleted=0;

UPDATE smsevent SET custom_var36=NULL 
WHERE school_id IN ('b4a02e2b-7b90-b4f8-e325-4e5703fe2e61', '3094ae2c-d5cf-56ae-a54b-4e5704b2c4a4', 'c9a7ba3e-d866-1e08-abf1-4e57049757a6', 'd2f5eef1-7ce9-9b22-fb29-4ea0bb6d46bc')
AND custom_var36='非公開' AND deleted=0;


-- 2015-05-11  course koukai settei change

SELECT name, school_id, custom_var36, custom_var39, date_entered FROM smsevent
WHERE school_id IN ('b4a02e2b-7b90-b4f8-e325-4e5703fe2e61', 'c9a7ba3e-d866-1e08-abf1-4e57049757a6')
AND custom_var36='非公開' AND deleted=0;

UPDATE smsevent SET custom_var36=NULL 
WHERE school_id IN ('b4a02e2b-7b90-b4f8-e325-4e5703fe2e61', 'c9a7ba3e-d866-1e08-abf1-4e57049757a6')
AND custom_var36='非公開' AND deleted=0;

