DROP TABLE IF EXISTS `sms_event_instance_sub`; 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 DROP TABLE IF EXISTS `poll`; 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); DROP TABLE IF EXISTS `poll_question`; 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', 'あなたの安否を教えてください', '無事軽傷重傷', '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', '現在の所在地を教えてください', '社内自宅外出中', '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', '家族の安否を教えてください', '全員無事負傷者等あり不明', '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', '出社可否を教えてください', '可不可微妙', '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'); DROP TABLE IF EXISTS `poll_vote`; 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 DROP TABLE IF EXISTS `emailman`; 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;