ALTER TABLE `sms_fee` ADD `extra_kubun1` VARCHAR( 256 ) NOT NULL ; ALTER TABLE `sms_fee` ADD `extra_kubun2` VARCHAR( 256 ) NOT NULL ; ALTER TABLE `sms_fee` ADD `extra_kubun3` VARCHAR( 256 ) NOT NULL ; ALTER TABLE `sms_fee` CHANGE `extra_kubun1` `extra_kubun1` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL , CHANGE `extra_kubun2` `extra_kubun2` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL , CHANGE `extra_kubun3` `extra_kubun3` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ; ALTER TABLE `event_contacts` ADD `application_date` DATE NOT NULL , ADD `entrance_date` DATE NOT NULL ; ALTER TABLE `event_contacts` ADD `course_kubun` VARCHAR( 255 ) NOT NULL , ADD `contents` VARCHAR( 255 ) NOT NULL ; ALTER TABLE `sms_fee` CHANGE `salesno` `salesno` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL; ALTER TABLE `sms_transaction` CHANGE `salesno` `salesno` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL; ALTER TABLE `sms_salesgroup` CHANGE `salesno` `salesno` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL; ALTER TABLE `sms_fee` ADD `hinmei` VARCHAR( 64 ) NULL ; ALTER TABLE `product` ADD `subcategory` VARCHAR( 64 ) NULL AFTER `productcategory_id` ; CREATE TABLE IF NOT EXISTS `productinout` ( `id` varchar(36) NOT NULL DEFAULT '', `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, `created_by` varchar(36) DEFAULT NULL, `deleted` tinyint(1) NOT NULL DEFAULT '0', `name` varchar(255) DEFAULT NULL, `product_id` varchar(36) DEFAULT NULL, `category` varchar(16) DEFAULT NULL, `price` decimal(20,2) NOT NULL DEFAULT '0.00', `date_buy_sell` date DEFAULT NULL, `quantity` int(5) NOT NULL DEFAULT '0', `school_id` varchar(36) DEFAULT NULL, `description` text, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; SELECT id, cost_price, discount_price, list_price FROM `product` WHERE discount_price IS NOT NULL ; UPDATE product SET list_price=discount_price WHERE discount_price IS NOT NULL ; SELECT * FROM `smsevent` WHERE `category` LIKE 'course' AND `subtype` =2 ; SELECT * FROM `smsevent` WHERE `category` LIKE 'course' AND `subtype` =3 ; UPDATE smsevent SET subtype=4 WHERE `category` LIKE 'course' AND `subtype` =2 ; UPDATE smsevent SET subtype=5 WHERE `category` LIKE 'course' AND `subtype` =3 ; SELECT * FROM `sms_fee` WHERE `product_id` IS NOT NULL AND `product_count` =0 AND contact_id IS NULL ; UPDATE `sms_fee` SET product_count=1 WHERE `product_id` IS NOT NULL AND `product_count` =0 AND contact_id IS NULL ; UPDATE sms_fee SET deleted=1 WHERE id IN ('coursefee02723','coursefee02724','coursefee02726','coursefee02727','coursefee02728','coursefee02729', 'coursefee02730','coursefee02731','coursefee02732','coursefee02733','coursefee02734','coursefee02735', 'coursefee02736','coursefee02747','coursefee02748','coursefee02749','coursefee02750','coursefee02751', 'coursefee02752','coursefee02753','coursefee02754','coursefee02756','coursefee02757','coursefee02758', 'coursefee02759','coursefee02760','coursefee02761','coursefee02762','coursefee02763','coursefee02714', 'coursefee02715','coursefee02716','coursefee02717','coursefee02718','coursefee02719','coursefee02720', 'coursefee02721','coursefee02722') SELECT sms_fee.id, sms_fee.name, sms_fee_update.name FROM sms_fee, sms_fee_update WHERE sms_fee.id=sms_fee_update.id AND sms_fee_update.modified_name=1 ; UPDATE sms_fee, sms_fee_update SET sms_fee.name=sms_fee_update.name WHERE sms_fee.id=sms_fee_update.id AND sms_fee_update.modified_name=1 ; SELECT sms_fee.id, sms_fee.event_id, sms_fee_update.event_id FROM sms_fee, sms_fee_update WHERE sms_fee.id=sms_fee_update.id AND sms_fee_update.modified_event_id=1 ; UPDATE sms_fee, sms_fee_update SET sms_fee.event_id=sms_fee_update.event_id WHERE sms_fee.id=sms_fee_update.id AND sms_fee_update.modified_event_id=1 ; SELECT sms_fee.id, sms_fee.basic_fee, sms_fee_update.basic_fee FROM sms_fee, sms_fee_update WHERE sms_fee.id=sms_fee_update.id AND sms_fee_update.modified_basic_fee=1 ; UPDATE sms_fee, sms_fee_update SET sms_fee.basic_fee=sms_fee_update.basic_fee WHERE sms_fee.id=sms_fee_update.id AND sms_fee_update.modified_basic_fee=1 ; SELECT sms_fee.id, sms_fee.extra_kubun2, sms_fee_update.extra_kubun2 FROM sms_fee, sms_fee_update WHERE sms_fee.id=sms_fee_update.id AND sms_fee_update.modified_extra_kubun2=1 ; UPDATE sms_fee, sms_fee_update SET sms_fee.extra_kubun2=sms_fee_update.extra_kubun2 WHERE sms_fee.id=sms_fee_update.id AND sms_fee_update.modified_extra_kubun2=1 ; SELECT sms_fee.id, sms_fee.extra_kubun3, sms_fee_update.extra_kubun3 FROM sms_fee, sms_fee_update WHERE sms_fee.id=sms_fee_update.id AND sms_fee_update.modified_extra_kubun3=1 ; UPDATE sms_fee, sms_fee_update SET sms_fee.extra_kubun3=sms_fee_update.extra_kubun3 WHERE sms_fee.id=sms_fee_update.id AND sms_fee_update.modified_extra_kubun3=1 ; SELECT sms_fee.id, sms_fee.name, sms_fee.extra_kubun3, sms_fee_update.extra_kubun3 FROM sms_fee, sms_fee_update WHERE sms_fee.id=sms_fee_update.id AND sms_fee_update.modified_extra_kubun3=1 ; SELECT product.id, product.discount_price, product.list_price, product2.discount_price as discount_price2, product2.list_price list_price2 FROM product, product2 WHERE product.id=product2.id AND product.id='9801' UPDATE product, product2 SET product.discount_price = product2.list_price, product.list_price = product2.list_price WHERE product.id=product2.id SELECT smsevent3.id, smsevent3.new_id FROM smsevent3, smsevent WHERE smsevent.id=smsevent3.new_id; UPDATE product SET subcategory=NULL WHERE `subcategory` LIKE '-' ; INSERT INTO `resource` (`id`, `school_id`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `name`, `shortname`, `description`, `category`, `location`, `deleted`, `capacity`, `resource_attributes`, `common_code`) VALUES ('1001', 'sssssss-1111', '2011-03-20 08:00:00', '2011-03-20 08:00:00', '1', '1', '601教室', '601', '立川本部校', 'classroom', NULL, 0, 20, 'YTowOnt9', NULL), ('1002', 'sssssss-1111', '2011-03-20 08:00:00', '2011-03-20 08:00:00', '1', '1', '602教室', '602', '立川本部校', 'classroom', NULL, 0, 20, 'YTowOnt9', NULL), ('1003', 'sssssss-1111', '2011-03-20 08:00:00', '2011-03-20 08:00:00', '1', '1', '603教室', '603', '立川本部校', 'classroom', NULL, 0, 20, 'YTowOnt9', NULL), ('1004', 'sssssss-1111', '2011-03-20 08:00:00', '2011-03-20 08:00:00', '1', '1', '701教室', '701', '立川本部校', 'classroom', NULL, 0, 30, 'YTowOnt9', NULL), ('1005', 'sssssss-1111', '2011-03-20 08:00:00', '2011-03-20 08:00:00', '1', '1', '702教室', '702', '立川本部校', 'classroom', NULL, 0, 30, 'YTowOnt9', NULL), ('1006', 'sssssss-1111', '2011-03-20 08:00:00', '2011-03-20 08:00:00', '1', '1', '703教室', '703', '立川本部校', 'classroom', NULL, 0, 10, 'YTowOnt9', NULL), ('1007', 'sssssss-1111', '2011-03-20 08:00:00', '2011-03-20 08:00:00', '1', '1', '第2校', '703', '立川第2校', 'classroom', NULL, 0, 120, 'YTowOnt9', NULL), ('1008', 'sssssss-1111', '2011-03-20 08:00:00', '2011-03-20 08:00:00', '1', '1', '301教室', '301', '立川MEP校', 'classroom', NULL, 0, 40, 'YTowOnt9', NULL), ('1009', 'sssssss-1111', '2011-03-20 08:00:00', '2011-03-20 08:00:00', '1', '1', '302教室', '302', '立川MEP校', 'classroom', NULL, 0, 40, 'YTowOnt9', NULL), ('1010', 'sssssss-1111', '2011-03-20 08:00:00', '2011-03-20 08:00:00', '1', '1', '303教室', '303', '立川MEP校', 'classroom', NULL, 0, 40, 'YTowOnt9', NULL); DELETE FROM `contacts` WHERE `date_entered` = '2011-03-20 10:00' SELECT event_contacts.event_id, smsevent3.id, smsevent3.new_id FROM event_contacts, smsevent3 WHERE event_contacts.event_id=smsevent3.id; SELECT event_contacts.event_id, smsevent3.id, smsevent3.new_id FROM event_contacts LEFT JOIN smsevent3 ON event_contacts.event_id=smsevent3.id WHERE smsevent3.id IS NULL SELECT distinct event_contacts.event_id FROM event_contacts LEFT JOIN smsevent3 ON event_contacts.event_id = smsevent3.id WHERE smsevent3.id IS NULL SELECT event_contacts.event_id, smsevent3.id, smsevent3.new_id FROM event_contacts, smsevent3 WHERE event_contacts.event_id = smsevent3.id UPDATE event_contacts, smsevent3 SET event_contacts.event_id=new_id WHERE event_contacts.event_id = smsevent3.id UPDATE event_contacts2, smsevent3 SET event_contacts2.event_id=new_id WHERE event_contacts2.event_id = smsevent3.id UPDATE productcategory SET NAME="PAD(初級ベーシック)" WHERE id='9900'; UPDATE productcategory SET NAME="PAD(初級アドバンス)" WHERE id='9910'; UPDATE productcategory SET NAME="PAD(中級講座)" WHERE id='9920'; UPDATE productcategory SET NAME="PAD(高速基本)" WHERE id='9930'; SELECT id, idnumber FROM contacts WHERE idnumber IN ('0810140058', '0810100000') ; UPDATE contacts SET idnumber=id WHERE idnumber IN ('0810140058', '0810100000') ; SELECT users.id,user_name, last_name, first_name, idnumber FROM users, contacts WHERE users.contact_id=contacts.id AND contacts.smsgroup='teacher' AND contacts.deleted=0 ORDER BY contacts.idnumber ASC SELECT DISTINCT assigned_user_id FROM `contacts` WHERE smsgroup='student' AND deleted=0 AND assigned_user_id NOT IN ('a437cc11-564c-e5c4-3ff0-4c88c6961e3b', 'c4565fc9-30f9-719c-cb31-4437354e807d') Edit Delete 0810190001 Edit Delete 0810190005 Edit Delete 0810190006 Edit Delete 0810190007 Edit Delete 0810190008 Edit Delete 0810190009 Edit Delete 0810190010 Edit Delete 0810190011 Edit Delete 0810190013 Edit Delete 0810190014 Edit Delete 0810190015 Edit Delete 0810190016 Edit Delete 0810190017 SELECT DISTINCT reports_to_id FROM `contacts` WHERE smsgroup='student' AND deleted=0 AND reports_to_id>'0' Edit Delete 0810190002 Edit Delete 0810190005 Edit Delete 0810190009 Edit Delete 0810190013 Edit Delete 0810190014 Edit Delete 0810190026 SELECT 'XXXX', users.id,idnumber,'YYYY' FROM users, contacts WHERE users.contact_id=contacts.id AND contacts.smsgroup='teacher' AND contacts.deleted=0 ORDER BY contacts.idnumber ASC UPDATE contacts SET assigned_user_id='82a02561-4084-ab73-4109-4d3791710603' WHERE assigned_user_id ='0810190000'; UPDATE contacts SET assigned_user_id='236b0a68-5463-3188-bc02-4d8aa56b3bb7' WHERE assigned_user_id ='0810190001'; UPDATE contacts SET assigned_user_id='4ef5f4c9-02be-337a-d2ea-4d8aa709d9e5' WHERE assigned_user_id ='0810190002'; UPDATE contacts SET assigned_user_id='445d7b5e-a48d-55a2-6c79-4d8aa7227ae7' WHERE assigned_user_id ='0810190003'; UPDATE contacts SET assigned_user_id='6414547c-173d-152b-64e6-4d8aa5775197' WHERE assigned_user_id ='0810190004'; UPDATE contacts SET assigned_user_id='3d8fde0b-0a0e-e8ed-0ac5-4d8aa6cee9bb' WHERE assigned_user_id ='0810190005'; UPDATE contacts SET assigned_user_id='25a50ba3-f8ed-d211-4da3-4d8aa71f97e2' WHERE assigned_user_id ='0810190006'; UPDATE contacts SET assigned_user_id='d7182dac-105d-0a20-2786-4d8aa6819efb' WHERE assigned_user_id ='0810190007'; UPDATE contacts SET assigned_user_id='e1913fd2-c03d-6aaa-6f04-4d8aa239b8f3' WHERE assigned_user_id ='0810190008'; UPDATE contacts SET assigned_user_id='b2ab8f92-0f9d-6d91-ca6e-4d8aa5708132' WHERE assigned_user_id ='0810190009'; UPDATE contacts SET assigned_user_id='cc3df45b-3f39-d600-53a3-4d8aa6e8effd' WHERE assigned_user_id ='0810190010'; UPDATE contacts SET assigned_user_id='fcff00aa-2ea3-2c41-d8f2-4d8aa64a49e3' WHERE assigned_user_id ='0810190011'; UPDATE contacts SET assigned_user_id='4aee34d7-0109-9ae1-59ba-4d8aa6524c4e' WHERE assigned_user_id ='0810190012'; UPDATE contacts SET assigned_user_id='33f69107-f01f-7bd0-9361-4d8aa7cfb487' WHERE assigned_user_id ='0810190013'; UPDATE contacts SET assigned_user_id='b7718260-01a6-d6e4-d5bb-4d8aa5e25cb3' WHERE assigned_user_id ='0810190014'; UPDATE contacts SET assigned_user_id='50ce182d-9ee9-0ff2-6c29-4d8aa601b2d3' WHERE assigned_user_id ='0810190015'; UPDATE contacts SET assigned_user_id='aef74633-c9a0-ae20-a5b7-4d8aa610792f' WHERE assigned_user_id ='0810190016'; UPDATE contacts SET assigned_user_id='57e3b68c-f07e-14bf-3f08-4d8aa5e3feb4' WHERE assigned_user_id ='0810190017'; UPDATE contacts SET assigned_user_id='5d3f9953-7efd-c13b-ebb4-4d8aa4edc778' WHERE assigned_user_id ='0810190018'; UPDATE contacts SET assigned_user_id='cb532f97-ec72-b94f-3259-4d8aa79b39a2' WHERE assigned_user_id ='0810190019'; UPDATE contacts SET assigned_user_id='65f635ee-88c1-b5f2-ffb5-4d8aa5322a0f' WHERE assigned_user_id ='0810190020'; UPDATE contacts SET assigned_user_id='e1cb1b2d-3007-d80b-6f0a-4d8aa58b0f6a' WHERE assigned_user_id ='0810190021'; UPDATE contacts SET assigned_user_id='4ea2870d-8c69-d965-6693-4d8aa626922e' WHERE assigned_user_id ='0810190022'; UPDATE contacts SET assigned_user_id='9c45bd7a-26e0-cf72-1201-4d8aa4aa2493' WHERE assigned_user_id ='0810190023'; UPDATE contacts SET assigned_user_id='efa59bf7-033d-94c9-c0cb-4d8aa188febf' WHERE assigned_user_id ='0810190024'; UPDATE contacts SET assigned_user_id='c708178d-d09d-a7df-e0aa-4d8aa612dd8f' WHERE assigned_user_id ='0810190025'; UPDATE contacts SET assigned_user_id='46385af7-5c22-41ef-7cac-4d8aa5bbd1f4' WHERE assigned_user_id ='0810190026'; UPDATE contacts SET assigned_user_id='2e0ed05f-31a6-71de-d0ed-4d8aa68f4d74' WHERE assigned_user_id ='0810190027'; UPDATE contacts SET assigned_user_id='cff0e98c-adfe-e72e-cdc8-4d3791dac350' WHERE assigned_user_id ='0810290000'; UPDATE contacts SET assigned_user_id='14bb1ece-4e37-30b8-befd-4d3791fb9d9e' WHERE assigned_user_id ='0810390000'; UPDATE contacts SET reports_to_id='4ef5f4c9-02be-337a-d2ea-4d8aa709d9e5' WHERE reports_to_id ='0810190002'; UPDATE contacts SET reports_to_id='3d8fde0b-0a0e-e8ed-0ac5-4d8aa6cee9bb' WHERE reports_to_id ='0810190005'; UPDATE contacts SET reports_to_id='b2ab8f92-0f9d-6d91-ca6e-4d8aa5708132' WHERE reports_to_id ='0810190009'; UPDATE contacts SET reports_to_id='33f69107-f01f-7bd0-9361-4d8aa7cfb487' WHERE reports_to_id ='0810190013'; UPDATE contacts SET reports_to_id='b7718260-01a6-d6e4-d5bb-4d8aa5e25cb3' WHERE reports_to_id ='0810190014'; UPDATE contacts SET reports_to_id='46385af7-5c22-41ef-7cac-4d8aa5bbd1f4' WHERE reports_to_id ='0810190026'; UPDATE sms_f SET extra_kubun1='236b0a68-5463-3188-bc02-4d8aa56b3bb7' WHERE extra_kubun1 ='0810190001'; UPDATE sms_f SET extra_kubun1='3d8fde0b-0a0e-e8ed-0ac5-4d8aa6cee9bb' WHERE extra_kubun1 ='0810190005'; UPDATE sms_f SET extra_kubun1='25a50ba3-f8ed-d211-4da3-4d8aa71f97e2' WHERE extra_kubun1 ='0810190006'; UPDATE sms_f SET extra_kubun1='d7182dac-105d-0a20-2786-4d8aa6819efb' WHERE extra_kubun1 ='0810190007'; UPDATE sms_f SET extra_kubun1='e1913fd2-c03d-6aaa-6f04-4d8aa239b8f3' WHERE extra_kubun1 ='0810190008'; UPDATE sms_f SET extra_kubun1='b2ab8f92-0f9d-6d91-ca6e-4d8aa5708132' WHERE extra_kubun1 ='0810190009'; UPDATE sms_f SET extra_kubun1='cc3df45b-3f39-d600-53a3-4d8aa6e8effd' WHERE extra_kubun1 ='0810190010'; UPDATE sms_f SET extra_kubun1='fcff00aa-2ea3-2c41-d8f2-4d8aa64a49e3' WHERE extra_kubun1 ='0810190011'; UPDATE sms_f SET extra_kubun1='4aee34d7-0109-9ae1-59ba-4d8aa6524c4e' WHERE extra_kubun1 ='0810190012'; UPDATE sms_f SET extra_kubun1='33f69107-f01f-7bd0-9361-4d8aa7cfb487' WHERE extra_kubun1 ='0810190013'; UPDATE sms_f SET extra_kubun1='b7718260-01a6-d6e4-d5bb-4d8aa5e25cb3' WHERE extra_kubun1 ='0810190014'; UPDATE sms_f SET extra_kubun1='50ce182d-9ee9-0ff2-6c29-4d8aa601b2d3' WHERE extra_kubun1 ='0810190015'; UPDATE sms_f SET extra_kubun1='aef74633-c9a0-ae20-a5b7-4d8aa610792f' WHERE extra_kubun1 ='0810190016'; UPDATE sms_f SET extra_kubun1='57e3b68c-f07e-14bf-3f08-4d8aa5e3feb4' WHERE extra_kubun1 ='0810190017'; SELECT sms_f.event_id, smsevent3.id, smsevent3.new_id FROM sms_f, smsevent3 WHERE sms_f.event_id = smsevent3.id UPDATE sms_f, smsevent3 SET sms_f.event_id=new_id WHERE sms_f.event_id = smsevent3.id SELECT sms_t.event_id, smsevent3.id, smsevent3.new_id FROM sms_t, smsevent3 WHERE sms_t.event_id = smsevent3.id UPDATE sms_t, smsevent3 SET sms_t.event_id=new_id WHERE sms_t.event_id = smsevent3.id UPDATE sms_s SET id=CONCAT('0', salesno-0810100000+081010000100) WHERE id='0810100014' UPDATE sms_s SET id=CONCAT('0', salesno-0810100000+081010000100) WHERE 1; UPDATE sms_s SET salesno=CONCAT('0', salesno-0810100000+081010000100) WHERE 1; UPDATE sms_s SET name=salesno WHERE 1; UPDATE sms_f SET salesno=CONCAT('0', salesno-0810100000+081010000100) WHERE 1; UPDATE sms_t SET salesno=CONCAT('0', salesno-0810100000+081010000100) WHERE 1; SELECT contacts.idnumber, custom_var7, custom_var8, custom_var9, custom_var10, bank_code, branch_code, katakana FROM contacts, bank_info WHERE custom_var7=bank_code AND branch_code='000' UPDATE contacts, bank_info SET custom_var8=katakana WHERE custom_var7=bank_code AND branch_code='000' SELECT contacts.idnumber, custom_var7, custom_var8, custom_var9, custom_var10, bank_code, branch_code, katakana FROM contacts, bank_info WHERE custom_var7=bank_code AND branch_code=custom_var9 UPDATE contacts, bank_info SET custom_var10=katakana WHERE custom_var7=bank_code AND branch_code=custom_var9 SELECT smsevent.custom_var36, smsevent.id as event_id, smsevent.custom_var37, fee_subcategory, in_amount, actual_in_amount, actual_out_amount, payment_type FROM sms_transaction LEFT JOIN sms_fee ON sms_transaction.fee_id=sms_fee.id AND sms_fee.deleted=0 LEFT JOIN smsevent ON sms_fee.event_id=smsevent.id AND smsevent.deleted=0 WHERE sms_transaction.deleted=0 AND sms_fee.school_id='sssssss-1111' AND sms_fee.start_date>=2012-09-01 AND sms_transaction.t_date<'2012-09-01' GROUP BY payment_type, fee_subcategory -- 201104-18 student status update SELECT DISTINCT custom_var17 FROM contacts WHERE smsgroup='student' ; UPDATE contacts SET custom_var17='退室・CD会員' WHERE custom_var17='CD会員' ; UPDATE contacts SET custom_var17='受講・休室' WHERE custom_var17='休室' ; UPDATE contacts SET custom_var17='一般・保護者' WHERE custom_var17='保護者' ; UPDATE contacts SET custom_var17='一般・外部生' WHERE custom_var17='外部生' ; UPDATE contacts SET custom_var17='受講・月1' WHERE custom_var17='月1' ; UPDATE contacts SET custom_var17='受講・月2' WHERE custom_var17='月2' ; UPDATE contacts SET custom_var17='退室・退室' WHERE custom_var17='退室' ; UPDATE contacts SET custom_var17='受講・通常' WHERE custom_var17='通常' ; UPDATE contacts SET custom_var17='一般・退室' WHERE custom_var17='退室・退室' AND contact_status='102' -- 2011-04-16 yahya (add index) ALTER TABLE `event_contacts` ADD INDEX ( `start_date` ); ALTER TABLE `event_contacts` ADD INDEX ( `end_date` ); -- 2011-04-22 gessha sakusei for April SELECT DISTINCT leave_date FROM contacts UPDATE contacts SET leave_date=NULL WHERE leave_date='0000-00-00' ; UPDATE contacts SET enroll_date=NULL WHERE enroll_date='0000-00-00' ; SELECT event_contacts.*, contacts.*, sms_fee.* FROM event_contacts, contacts, sms_fee WHERE sms_fee.contact_id=contacts.id AND sms_fee.event_id=event_contacts.event_id AND contacts.id=event_contacts.contact_id AND contacts.idnumber IN ('0810120295', '0810106175') AND sms_fee.id IN ('fee_02551', 'fee_02054') SELECT contacts.*, sms_fee.* FROM contacts, sms_fee WHERE sms_fee.contact_id=contacts.id AND contacts.idnumber IN ('0810120295', '0810106175') AND sms_fee.id IN ('fee_02551', 'fee_02054') -- gakunen update 2011-04-27 SELECT id, last_name, grade, grade_entrance_date, grade_entered, date_entered, date_modified FROM contacts WHERE smsgroup = 'student' AND deleted =0 AND grade IS NOT NULL AND grade!='' AND date_entered < '2011-04-01 00:00:00' AND grade!=grade_entered SELECT id, last_name, grade, grade_entrance_date, grade_entered, date_entered, date_modified FROM contacts WHERE smsgroup = 'student' AND deleted =0 AND grade IS NOT NULL AND grade!='' AND date_entered < '2011-04-01 00:00:00' AND grade=grade_entered AND grade !='general' UPDATE contacts SET grade=grade+1 WHERE smsgroup = 'student' AND deleted =0 AND grade IS NOT NULL AND grade!='' AND date_entered < '2011-04-01 00:00:00' AND grade=grade_entered AND grade !='general' SELECT id, last_name, grade, grade_entrance_date, grade_entered, date_entered, date_modified FROM contacts WHERE smsgroup = 'student' AND deleted =0 AND grade IS NOT NULL AND grade!='' AND date_entered < '2011-04-01 00:00:00' AND grade=grade_entered AND grade !='general' UPDATE contacts SET grade='general' WHERE grade =19 ; -- 2011-04-28 shinki toiawase kanri excel xml related fields ALTER TABLE `event_contacts` ADD `setsumeikai_date1` DATE NULL; ALTER TABLE `event_contacts` ADD `setsumeikai_tantosha1_1` VARCHAR( 36 ) NULL , ADD `setsumeikai_tantosha1_2` VARCHAR( 36 ) NULL; ALTER TABLE `event_contacts` ADD `setsumeikai_comment1` VARCHAR( 255 ) NULL ; ALTER TABLE `event_contacts` ADD `kengaku_date1` DATE NULL; ALTER TABLE `event_contacts` ADD `kengaku_tantosha1_1` VARCHAR( 36 ) NULL , ADD `kengaku_tantosha1_2` VARCHAR( 36 ) NULL; ALTER TABLE `event_contacts` ADD `kengaku_comment1` VARCHAR( 255 ) NULL ; ALTER TABLE `event_contacts` ADD `kengaku_date2` DATE NULL; ALTER TABLE `event_contacts` ADD `kengaku_tantosha2_1` VARCHAR( 36 ) NULL , ADD `kengaku_tantosha2_2` VARCHAR( 36 ) NULL; ALTER TABLE `event_contacts` ADD `kengaku_comment2` VARCHAR( 255 ) NULL ; ALTER TABLE `event_contacts` ADD `kengaku_date3` DATE NULL; ALTER TABLE `event_contacts` ADD `kengaku_tantosha3_1` VARCHAR( 36 ) NULL , ADD `kengaku_tantosha3_2` VARCHAR( 36 ) NULL; ALTER TABLE `event_contacts` ADD `kengaku_comment3` VARCHAR( 255 ) NULL ; ALTER TABLE `event_contacts` CHANGE `setsumeikai_date1` `setsumeikai_date1` VARCHAR( 64 ) NULL DEFAULT NULL , CHANGE `kengaku_date1` `kengaku_date1` VARCHAR( 64 ) NULL DEFAULT NULL , CHANGE `kengaku_date2` `kengaku_date2` VARCHAR( 64 ) NULL DEFAULT NULL , CHANGE `kengaku_date3` `kengaku_date3` VARCHAR( 64 ) NULL DEFAULT NULL ; -- SMSEventContactMemo related updates DROP TABLE IF EXISTS `sms_ec_memo`; CREATE TABLE IF NOT EXISTS `sms_ec_memo` ( `id` varchar(36) NOT NULL DEFAULT '', `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, `assigned_user_id2` varchar(36) DEFAULT NULL, `created_by` varchar(36) DEFAULT NULL, `deleted` tinyint(1) NOT NULL DEFAULT '0', `category` varchar(16) DEFAULT NULL, `status` varchar(16) DEFAULT NULL, `memodate` date DEFAULT NULL, `memodate2` date DEFAULT NULL, `value` int(8) DEFAULT NULL, `contact_id` varchar(36) DEFAULT NULL, `smsgroup` varchar(25) DEFAULT NULL, `event_id` varchar(36) DEFAULT NULL, `event_type` varchar(25) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `description` text, PRIMARY KEY (`id`), KEY `event_id` (`event_id`), KEY `contact_id` (`contact_id`,`event_id`), KEY `memodate` (`memodate`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; ALTER TABLE `sms_ec_memo` CHANGE `memodate` `memodate2` DATE NULL DEFAULT NULL ; ALTER TABLE `sms_ec_memo` ADD `memodate3` DATE NULL DEFAULT NULL AFTER `memodate2` ; --- add a new field to event_contacts table ALTER TABLE `event_contacts` ADD `leave_date` DATE NOT NULL AFTER `entrance_date`; -- to fix textbook fees not related course uriage problem UPDATE sms_transactiontype SET `keiri_code` = '407' WHERE `sms_transactiontype`.`id` = '5' LIMIT 1 ; UPDATE sms_transactiontype SET `keiri_code` = '407' WHERE `sms_transactiontype`.`id` = '17' LIMIT 1 ; -- objective table DROP TABLE IF EXISTS `sms_objective`; CREATE TABLE IF NOT EXISTS `sms_objective` ( `id` varchar(36) NOT NULL DEFAULT '', `date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `modified_user_id` varchar(36) DEFAULT NULL, `group` varchar(32) DEFAULT NULL, `category` varchar(64) DEFAULT NULL, `subcategory` varchar(64) DEFAULT NULL, `objectivedate` date DEFAULT NULL, `mokuhyo` int(8) DEFAULT NULL, `jisseki` int(8) DEFAULT NULL, `target_id` varchar(36) DEFAULT NULL, `school_id` varchar(36) DEFAULT NULL, PRIMARY KEY (`id`), KEY `target_id` (`target_id`), KEY `objectivedate` (`objectivedate`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; ALTER TABLE `sms_objective` CHANGE `mokuhyo` `mokuhyo` DECIMAL( 20, 2 ) NULL DEFAULT NULL ; ALTER TABLE `sms_objective` CHANGE `jisseki` `jisseki` DECIMAL( 20, 2 ) NULL DEFAULT NULL ; -- DROP TABLE IF EXISTS `combini_history` (if first time, drop the old table) CREATE TABLE IF NOT EXISTS `combini_history` ( `id` varchar(36) NOT NULL, `date_entered` datetime NOT NULL, `date_modified` datetime NOT NULL, `combiniactions_id` varchar(36) NOT NULL, `contact_id` varchar(36) default NULL, `data_type` varchar(2) default NULL, `t_date` datetime default NULL, `salesno` varchar(20) default NULL, `due_date` date default NULL, `in_amount` varchar(6) default NULL, `store_code` varchar(8) default NULL, `store_name` varchar(60) default NULL, `payment_date` date default NULL, `error_code` varchar(20) default NULL, `raw_import_data` text, `student_id` varchar(20) default NULL, PRIMARY KEY (`id`), KEY `combiniactions_id` (`combiniactions_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- eigoban course and fees ido javascript: get_category('614', '01本科', '1b2f032b-e1d8-81ee-46bd-4e689467a126'); javascript: get_course('7fc64b04-d9b5-8390-02f6-4e6894c0caf1'); javascript: get_course('77632c36-e2f1-ab64-021f-4e68940a04e1'); javascript: get_course('b1a1eef1-a6d9-c62a-7049-4e6894718002'); javascript: get_course('ec603b46-568e-f7ec-7842-4e6894e31df7'); IN ('7fc64b04-d9b5-8390-02f6-4e6894c0caf1', '77632c36-e2f1-ab64-021f-4e68940a04e1', 'b1a1eef1-a6d9-c62a-7049-4e6894718002', 'ec603b46-568e-f7ec-7842-4e6894e31df7') javascript: get_category('616', '01胎教', '499b1259-9532-ba30-3b90-4e6895a1f96d'); javascript: get_course('3b55eee4-3367-7ad8-a796-4e689548f8ad'); IN ('3b55eee4-3367-7ad8-a796-4e689548f8ad') javascript: get_category('617', '02親と子の共育大学', '64e42d36-8b51-6c96-5556-4e6895b57c63'); javascript: get_course('5ad85867-16fa-7867-ea36-4e689636d5c5'); IN('5ad85867-16fa-7867-ea36-4e689636d5c5') javascript: get_category('621', '03入室説明会', '5bafe2ca-544f-b6d8-ef32-4e6896883f41'); javascript: get_course('3c6bc540-420e-0420-0545-4e68966d5436'); IN('3c6bc540-420e-0420-0545-4e68966d5436') javascript: get_category('622', '01本科', 'c2d5a49e-d019-e8ec-a958-4e689715b6ea'); javascript: get_category('625', '01胎教', '51aa535d-eaec-5c3d-dcf7-4e6897bba160'); javascript: get_category('626', '02親と子の共育大学', '473e7cd5-795d-1c7e-b4af-4e6897ffd4bd'); javascript: get_category('624', '03入室説明会', 'e9a3481c-3928-db44-1105-4e6897bcad2e'); UPDATE smsevent SET school_id='ba1a2012-eb6e-ee2a-ae6f-4e65ad43749d' WHERE id IN ('7fc64b04-d9b5-8390-02f6-4e6894c0caf1', '77632c36-e2f1-ab64-021f-4e68940a04e1', 'b1a1eef1-a6d9-c62a-7049-4e6894718002', 'ec603b46-568e-f7ec-7842-4e6894e31df7', '3b55eee4-3367-7ad8-a796-4e689548f8ad', '5ad85867-16fa-7867-ea36-4e689636d5c5', '3c6bc540-420e-0420-0545-4e68966d5436') UPDATE sms_fee SET school_id='ba1a2012-eb6e-ee2a-ae6f-4e65ad43749d' WHERE event_id IN ('7fc64b04-d9b5-8390-02f6-4e6894c0caf1', '77632c36-e2f1-ab64-021f-4e68940a04e1', 'b1a1eef1-a6d9-c62a-7049-4e6894718002', 'ec603b46-568e-f7ec-7842-4e6894e31df7', '3b55eee4-3367-7ad8-a796-4e689548f8ad', '5ad85867-16fa-7867-ea36-4e689636d5c5', '3c6bc540-420e-0420-0545-4e68966d5436') -- 2011-10-18 ryokin tekiyo problem SELECT * FROM `sms_fee` WHERE `event_id` LIKE '114a8fad-8430-14c3-a589-4e97d78e33e1' AND contact_id IS NOT NULL AND deleted=0 SELECT * FROM `sms_fee` WHERE `event_id` LIKE '114a8fad-8430-14c3-a589-4e97d78e33e1' AND contact_id IS NOT NULL AND deleted=0 AND contact_id!='create_student_schedule_as_well' SELECT * FROM `sms_transaction` WHERE `event_id` LIKE '114a8fad-8430-14c3-a589-4e97d78e33e1' AND deleted=0 UPDATE `sms_fee` SET deleted=1 WHERE `event_id` LIKE '114a8fad-8430-14c3-a589-4e97d78e33e1' AND contact_id IS NOT NULL AND deleted=0 AND contact_id!='create_student_schedule_as_well' UPDATE `sms_transaction` SET deleted=1 WHERE `event_id` LIKE '114a8fad-8430-14c3-a589-4e97d78e33e1' AND deleted=0 UPDATE `sms_fee` SET deleted=1 WHERE `event_id` LIKE '114a8fad-8430-14c3-a589-4e97d78e33e1' AND contact_id IS NOT NULL AND deleted=0 AND contact_id='create_student_schedule_as_well' -- grade problem with age0 SELECT id, grade, grade_entered, grade_entrance_date FROM `contacts` WHERE grade_entered = '00' -- 2012-04-23 fee hinmei update UPDATE sms_fee SET hinmei=CONCAT( '維持費', basic_fee ) WHERE `name` LIKE '%プレミアム%維持%' AND `deleted` =0 AND hinmei != CONCAT( '維持費', basic_fee ) -- 2012-08-21 ALTER TABLE `contacts` ADD `shiryo_hassobi` DATE NULL DEFAULT NULL AFTER `birthdate` , ADD `kengakubi` DATE NULL DEFAULT NULL AFTER `shiryo_hassobi` , ADD `nyushitsu_tetsuzukibi` DATE NULL DEFAULT NULL AFTER `kengakubi`; -- 2013-01-31 bank branch addition INSERT INTO `bank_info` (`bank_code`, `branch_code`, `katakana`, `name`, `type`, `branch_type`) VALUES ('0001', '004', 'ﾏﾙﾉｳﾁﾁﾕｳｵｳ ', '丸の内中央', '2', '1'); --2013-10-04 update finish 受講生ステータス変更バッチに伴うパッチ ----contactsのステータス変更 --update contacts set contact_status=7, custom_var17='退室・退室' --where leave_date is not null and smsgroup='student' and contact_status!=7 and custom_var17!='退室・退室' ----event_contactsのステータス変更 --update event_contacts set event_contact_status=7 --where leave_date is not null and leave_date!='0000-00-00' and event_contact_status!=7 --and contact_id in (select id from contacts where smsgroup='student') -- 2014-04-01 gakunen updates SELECT id, last_name, date_modified, grade, grade_entrance_date, grade_entered, date_modified, date_entered, contact_status FROM contacts WHERE smsgroup='student' AND grade IN ("00", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18") UPDATE contacts SET grade="general" WHERE smsgroup='student' AND grade ="18" UPDATE contacts SET grade=grade+1 WHERE smsgroup='student' AND grade IN ("00", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17")