/* SQL commads to update the schoolmng database Append a new change at the end of the file with the date and your name. */ /* 20061126 metehan */ UPDATE sms_fee SET UNIT_NO = 60 WHERE type_id = '4'; /* 20061206 yahya renraku-chou */ CREATE TABLE `contacts_sms_memo` ( `id` varchar( 36 ) NOT NULL default '', `contact_id` varchar( 36 ) default NULL , `sms_memo_id` varchar( 36 ) default NULL , `date_modified` datetime default NULL , `deleted` tinyint( 1 ) NOT NULL default '0', PRIMARY KEY ( `id` ) ) ENGINE = MYISAM DEFAULT CHARSET = utf8; ALTER TABLE `contacts_sms_memo` ADD `status` TINYINT DEFAULT '0' NOT NULL ; /* 20061206 yahya */ INSERT INTO `sms_recurrence_templates` (`id`, `deleted`, `date_entered`, `created_by`, `name`, `value`, `status`, `school_id`) VALUES ('81a694d8-b1c2-a05b-fef1-457511911059', 0, '2006-12-05 06:27:51', '1', 'timeslot', '{"recurrences_arr":[{"from_time_hour":"9","from_time_minute":"0","to_time_hour":"10","to_time_minute":"30"},{"from_time_hour":"10","from_time_minute":"40","to_time_hour":"12","to_time_minute":"10"},{"from_time_hour":"13","from_time_minute":"0","to_time_hour":"14","to_time_minute":"30"},{"from_time_hour":"14","from_time_minute":"40","to_time_hour":"16","to_time_minute":"10"},{"from_time_hour":"16","from_time_minute":"20","to_time_hour":"17","to_time_minute":"50"}]}', 1, NULL); /* 20061211 yahya */ ALTER TABLE `sms_memo` ADD `status` TINYINT DEFAULT '0' NOT NULL , ADD `school_id` VARCHAR( 36 ) ; /* 20061210 merdan */ -- Additional fields to be used in Id Card Printing ALTER TABLE `contacts` ADD `id_card_field1` VARCHAR( 50 ) AFTER `application_date` , ADD `id_card_field2` VARCHAR( 50 ) AFTER `id_card_field1` , ADD `id_card_field3` VARCHAR( 50 ) AFTER `id_card_field2` ; ALTER TABLE `contacts` ADD `id_card_field4` VARCHAR( 50 ) AFTER `id_card_field3` ; --20061216 yahya ALTER TABLE `event_contacts` CHANGE `start_date` `start_date` DATETIME DEFAULT '0000-00-00 00:00:00' ; ALTER TABLE `event_contacts` CHANGE `end_date` `end_date` DATETIME DEFAULT '0000-00-00 00:00:00' ; ALTER TABLE `event_resource` CHANGE `start_date` `start_date` DATETIME DEFAULT '0000-00-00 00:00:00' ; ALTER TABLE `event_resource` CHANGE `end_date` `end_date` DATETIME DEFAULT '0000-00-00 00:00:00' ; /* 2006.12.17 metehan */ UPDATE contacts SET grade_entrance_date = NULL WHERE grade_entrance_date = '0000-00-00'; /* 2006.12.19 */ ALTER TABLE `sms_fee` CHANGE `due_to` `due_to` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '0' ; /* RUN THE FOLLOWING in order to correct access rights for users index.php?module=ACL&action=install_actions */ /* 2006.12.21 metehan */ UPDATE contacts SET send_document_home = 'on' WHERE true; UPDATE contacts SET send_document_work = 'on' WHERE true; /* 2006.12.22 metehan */ /* RUN THE FOLLOWING in order to correct access rights for users index.php?module=ACL&action=install_actions EXTRACT SMSGroups from the system tabs Admin -> Edit Tabs -> Move SMSGroups from 'show tabs' to 'hide tabs' */ ALTER TABLE `accounts` ADD `school_id` VARCHAR( 36 ) AFTER `id` ; DELETE acl_actions.* FROM acl_actions WHERE category LIKE 'Accountant' ; DELETE acl_actions.* FROM acl_actions WHERE category LIKE 'Manager' ; DELETE acl_actions.* FROM acl_actions WHERE category LIKE 'SMSGroups' ; /* 2006.12.23 metehan */ /* UPDATE QUERIES FOR THE EXISTING RECORDS */ UPDATE accounts, users, contacts, resource SET accounts.school_id = resource.id WHERE accounts.assigned_user_id = users.id AND users.contact_id = contacts.id AND contacts.school_id = resource.id; UPDATE accounts SET school_id = 'sssssss-1111' WHERE assigned_user_id='1'; ALTER TABLE `campaigns` ADD `school_id` VARCHAR( 36 ) AFTER `id` ; UPDATE campaigns, users, contacts, resource SET campaigns.school_id = resource.id WHERE campaigns.assigned_user_id = users.id AND users.contact_id = contacts.id AND contacts.school_id = resource.id; UPDATE campaigns SET school_id = 'sssssss-1111' WHERE assigned_user_id='1'; ALTER TABLE `prospect_lists` ADD `school_id` VARCHAR( 36 ) AFTER `id` ; UPDATE prospect_lists, users, contacts, resource SET prospect_lists.school_id = resource.id WHERE prospect_lists.assigned_user_id = users.id AND users.contact_id = contacts.id AND contacts.school_id = resource.id; UPDATE prospect_lists SET school_id = 'sssssss-1111' WHERE assigned_user_id='1'; /* yahya -- first drop all the indices except PRIMARY then execute the following query to create new indices --- SMS_EVENT_INSTANCES table ALTER TABLE `sms_event_instance` ADD INDEX `parent_id` ( `parent_id` , `from_datetime`, `to_datetime`) ; ALTER TABLE `sms_event_instance` ADD INDEX `from_to_datetime` ( `from_datetime` , `to_datetime` , `status` ) ; ALTER TABLE `sms_event_instance` ADD INDEX `from_id` ( `from_id` ); ALTER TABLE `sms_event_instance` ADD INDEX `to_id` ( `to_id` ) ; */ -- 20061227 (SMSEVENT_INSTANCE table) ALTER TABLE `smsevent_instances` ADD INDEX `target_id` ( `target_id` , `from_datetime` , `to_datetime` ); -- Index changes for SMS_FEE table ALTER TABLE `sms_fee` DROP INDEX `event_id` , ADD INDEX `event_id` ( `event_id` , `contact_id` ); -- 2007-01-04 -- -- Table structure for table `postcard` -- CREATE TABLE `postcard` ( `id` varchar(36) NOT NULL default '', `date_entered` datetime NOT NULL default '0000-00-00 00:00:00', `created_by` varchar(36) default NULL, `date_modified` datetime NOT NULL default '0000-00-00 00:00:00', `modified_user_id` varchar(36) NOT NULL default '0', `deleted` tinyint(1) NOT NULL default '0', `title` varchar(255) NOT NULL default '', `description` text, `picture_filename` varchar(255) default NULL, `address_placement` varchar(30) default NULL, `print_horizontally` varchar(3) default 'off', `school_id` varchar(36) NOT NULL default 'sssssss-1111', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- 2007-01-08 -- yahya ALTER TABLE `sms_event_instance` DROP INDEX `parent_id` ; ALTER TABLE `sms_event_instance` DROP INDEX `from_datetime` ; ALTER TABLE `sms_event_instance` DROP INDEX `from_id` ; ALTER TABLE `sms_event_instance` DROP INDEX `to_id` ; ALTER TABLE `sms_event_instance` ADD INDEX `parent_id` ( `parent_id` , `from_datetime`, `to_datetime`) ; ALTER TABLE `sms_event_instance` ADD INDEX `from_to_datetime` ( `from_datetime` , `to_datetime` , `status` ) ; ALTER TABLE `sms_event_instance` ADD INDEX `from_id` ( `from_id` ); ALTER TABLE `sms_event_instance` ADD INDEX `to_id` ( `to_id` ) ; ALTER TABLE `sms_event_instance` DROP INDEX `parent_id` , ADD UNIQUE `parent_id` ( `parent_id` , `from_datetime` , `to_datetime` ) ; -- 2007-01-09 -- merdan -- ALTER TABLE `postcard` ADD `print_address_horizontally` VARCHAR( 3 ) DEFAULT 'off' NOT NULL AFTER `print_horizontally` ; ALTER TABLE `postcard` ADD `sender_address` VARCHAR( 3 ) DEFAULT 'off' NOT NULL AFTER `print_address_horizontally` ; ALTER TABLE `postcard` ADD `yubin_hagaki` VARCHAR( 3 ) DEFAULT 'off' NOT NULL AFTER `sender_address` ; ALTER TABLE `postcard` ADD `school_postcode` VARCHAR( 10 ) ; ALTER TABLE `postcard` ADD `school_address` TEXT; -- 2007-01-10 --- merdan ALTER TABLE `contacts` CHANGE `grade` `grade` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL; -- 2007-01-13 --- metehan ALTER TABLE `resource` CHANGE `capacity` `capacity` SMALLINT( 4 ) DEFAULT NULL ; -- 2007-01-14 --- metehan ALTER TABLE `contacts` ADD `primary_address_kana` VARCHAR( 250 ) AFTER `primary_address_country` ; ALTER TABLE `contacts` ADD `alt_address_kana` VARCHAR( 250 ) AFTER `alt_address_country` ; -- 2007-01-17 -- merdan ALTER TABLE `accounts` ADD `billing_address_kana` VARCHAR( 250 ) AFTER `billing_address_country` ; -- 2007-01-17 ALTER TABLE `accounts` ADD `custom_var41` TEXT, ADD `custom_var42` TEXT, ADD `custom_var43` TEXT, ADD `custom_var44` TEXT, ADD `custom_var45` TEXT, ADD `custom_var46` TEXT, ADD `custom_var47` TEXT, ADD `custom_var48` TEXT, ADD `custom_var49` TEXT, ADD `custom_var50` TEXT, ADD `custom_var51` TEXT, ADD `custom_var52` TEXT, ADD `custom_var53` TEXT, ADD `custom_var54` TEXT, ADD `custom_var55` TEXT, ADD `custom_var56` TEXT, ADD `custom_var57` TEXT, ADD `custom_var58` TEXT, ADD `custom_var59` TEXT, ADD `custom_var60` TEXT; -- 2007-01-17 -- merdan (update for school custom field) -- NOTE: please do not apply this if you can add last_teacher_id from admin page ALTER TABLE `resourceschool_cstm` ADD `last_teacher_id_c` VARCHAR( 10 ); INSERT INTO `fields_meta_data` values ('Schoollast_teacher_id_c', 'last_teacher_id_c', 'last_teacher_id_c', 'School', 'varchar', 10, 'optional', NULL, '2007-01-17 02:20:31', 0, 0, 'moduleList', '0', '0'); -- 2007-01-18 --- metehan UPDATE `resourceschool_cstm` SET `last_teacher_id_c` = '10' ; UPDATE `contacts` SET `incometax_rate` = 0.1 WHERE `smsgroup` LIKE 'teacher' ; INSERT INTO `sms_transactiontype` (`id`, `name`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `keiri_category`, `apply_new_student`, `show_in_edit_fee`, `keiri_code`, `description`) VALUES ('15', 'tuition student', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'student', 0, 1, 403, 'Tuition fees not related to a course'); INSERT INTO `sms_transactiontype` (`id`, `name`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `keiri_category`, `apply_new_student`, `show_in_edit_fee`, `keiri_code`, `description`) VALUES ('16', 'tuition add student', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'student', 0, 1, 403, 'Tuition fees additional not related to a course'); INSERT INTO `contacts` (`id`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `assigned_user_id`, `created_by`, `salutation`, `first_name`, `last_name`, `first_name_furigana`, `last_name_furigana`, `lead_source`, `lead_source_description`, `title`, `department`, `reports_to_id`, `commission_company_id`, `birthdate`, `do_not_call`, `phone_home`, `phone_mobile`, `phone_work`, `phone_other`, `phone_fax`, `email1`, `email2`, `assistant`, `assistant_phone`, `email_opt_out`, `primary_address_street`, `primary_address_city`, `primary_address_state`, `primary_address_postalcode`, `primary_address_country`, `primary_address_kana`, `alt_address_street`, `alt_address_city`, `alt_address_state`, `alt_address_postalcode`, `alt_address_country`, `alt_address_kana`, `description`, `portal_name`, `portal_active`, `portal_app`, `invalid_email`, `smsgroup`, `contact_status`, `picture_filename`, `picture_file_mime_type`, `school_id`, `vacation_total`, `vacation_used`, `is_available_strict`, `major`, `send_document_home`, `send_document_work`, `company_name`, `idnumber`, `nationality`, `gender`, `work_school`, `last_school_name`, `last_school_dept`, `study_aim`, `transport_fee_type`, `transportfee`, `incometax_rate`, `leave_date`, `enroll_date`, `grade`, `grade_entrance_date`, `application_date`, `id_card_field1`, `id_card_field2`, `id_card_field3`, `id_card_field4`, `custom_var1`, `custom_var2`, `custom_var3`, `custom_var4`, `custom_var5`, `custom_var6`, `custom_var7`, `custom_var8`, `custom_var9`, `custom_var10`, `custom_var11`, `custom_var12`, `custom_var13`, `custom_var14`, `custom_var15`, `custom_var16`, `custom_var17`, `custom_var18`, `custom_var19`, `custom_var20`) VALUES ('be59a6ae-2ceb-667f-5ba0-45b03573f1ac', 1, '2007-01-19 03:05:26', '2007-01-19 03:05:33', '1', '1', '1', NULL, NULL, 'Admin', NULL, 'Dummy', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'off', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'off', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, 0, 'contact', '0', NULL, NULL, 'sssssss-1111', NULL, NULL, 0, NULL, 'off', 'off', NULL, NULL, 'JP', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0.1, NULL, NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '0', '0'); UPDATE `users` SET `contact_id` = 'be59a6ae-2ceb-667f-5ba0-45b03573f1ac' WHERE CONVERT( `id` USING utf8 ) = '1' LIMIT 1 ; -- 2007-01-23 --- metehan DELETE FROM `sms_transactiontype` WHERE CONVERT( `id` USING utf8 ) = '14' LIMIT 1 ; DELETE FROM `sms_transactiontype` WHERE CONVERT( `id` USING utf8 ) = '4' LIMIT 1 ; DELETE FROM `sms_transactiontype` WHERE CONVERT( `id` USING utf8 ) = '3' LIMIT 1 ; DELETE FROM `sms_transactiontype` WHERE CONVERT( `id` USING utf8 ) = '12' LIMIT 1 ; DELETE FROM `sms_transactiontype` WHERE CONVERT( `id` USING utf8 ) = '13' LIMIT 1 ; OPTIMIZE TABLE `sms_transactiontype` ; INSERT INTO `sms_transactiontype` (`id`, `name`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `keiri_category`, `apply_new_student`, `show_in_edit_fee`, `keiri_code`, `description`) VALUES ('4', 'parttime salary', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'teacher', 0, 1, 653, 'Part-time salary for teachers'); INSERT INTO `sms_transactiontype` (`id`, `name`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `keiri_category`, `apply_new_student`, `show_in_edit_fee`, `keiri_code`, `description`) VALUES ('14', 'salary', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'teacher', 0, 1, 652, 'Salary for teachers'); INSERT INTO `sms_transactiontype` (`id`, `name`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `keiri_category`, `apply_new_student`, `show_in_edit_fee`, `keiri_code`, `description`) VALUES ('3', 'transportation', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'teacher', 0, 1, 665, 'Transporation fee for teachers'); INSERT INTO `sms_transactiontype` (`id`, `name`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `keiri_category`, `apply_new_student`, `show_in_edit_fee`, `keiri_code`, `description`) VALUES ('13', 'adjustment', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'teacher', 0, 0, 665, 'Salary djusment value for teachers'); INSERT INTO `sms_transactiontype` (`id`, `name`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `keiri_category`, `apply_new_student`, `show_in_edit_fee`, `keiri_code`, `description`) VALUES ('12', 'transfer_out', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'teacher', 0, 1, 665, 'Money transfer for teachers'); -- 2007-01-25 --- metehan ALTER TABLE `sms_memo` ADD `assigned_user_id` VARCHAR( 36 ) AFTER `modified_user_id` ; UPDATE sms_memo SET `assigned_user_id` = `created_by` ; ALTER TABLE `sms_memo` ADD `memodate` DATE AFTER `created_by` ; UPDATE sms_memo SET memodate = SUBSTRING(date_entered, 1, 10); -- 2007-02-01 --merdan ALTER TABLE `resourceschool_cstm` ADD `rules_teacher_c` TEXT; ALTER TABLE `resourceschool_cstm` ADD `rules_staff_c` TEXT ; ALTER TABLE `resourceschool_cstm` ADD `bank_account_c` TEXT ; ALTER TABLE `resourceschool_cstm` ADD `tantosha_c` VARCHAR(50); INSERT INTO `fields_meta_data` VALUES ('Schoolrules_teacher_c', 'rules_teacher_c', 'rules_teacher_c', 'School', 'text', 255, 'optional', NULL, '2007-02-01 01:04:56', 0, 0, 'moduleList', '0', '0'); INSERT INTO `fields_meta_data` VALUES ('Schoolrules_staff_c', 'rules_staff_c', 'rules_staff_c', 'School', 'text', 255, 'optional', NULL, '2007-02-01 01:05:14', 0, 0, 'moduleList', '0', '0'); INSERT INTO `fields_meta_data` VALUES ('Schoolbank_account_c', 'bank_account_c', 'bank_account_c', 'School', 'text', 255, 'optional', NULL, '2007-02-01 01:05:37', 0, 0, 'moduleList', '0', '0'); INSERT INTO `fields_meta_data` VALUES ('Schooltantosha_c', 'tantosha_c', 'tantosha_c', 'School', 'varchar', 50, 'optional', NULL, '2007-02-01 07:33:33', 0, 0, 'moduleList', '0', '0'); -- 2007-02-01 --merdan ALTER TABLE `contacts` CHANGE `contact_status` `contact_status` VARCHAR( 3 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0'; ALTER TABLE `event_contacts` CHANGE `event_contact_status` `event_contact_status` VARCHAR( 3 ) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '0' ; -- 2007-02-05 -- yahya change the length of the user_name field so that we can use mail addr as user-name ALTER TABLE `users` CHANGE `user_name` `user_name` VARCHAR( 60 ) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL; -- 2007-02-07 -- merdan limit school branches to 99 UPDATE `resource` SET `school_id` = '01' WHERE `school_id`= '001' ; UPDATE `resource` SET `school_id` = '02' WHERE `school_id`= '002' ; UPDATE `resource` SET `school_id` = '03' WHERE `school_id`= '003' ; UPDATE `resource` SET `school_id` = '04' WHERE `school_id`= '004' ; UPDATE `resource` SET `school_id` = '05' WHERE `school_id`= '005' ; -- 2007-02-08 --merdan update contacts set idnumber = CONCAT(SUBSTRING(idnumber,2,2), SUBSTRING(idnumber, 6)) where LENGTH(idnumber)=10; -- 2007-02-12 -- yahya add a new field to the resource table ALTER TABLE `resource` ADD `resource_attributes` TEXT; -- 2007-02-16 --merdan ALTER TABLE `sms_fee` ADD `account_id` VARCHAR( 36 ) AFTER `contact_id` ; ALTER TABLE `sms_transaction` ADD `account_id` VARCHAR( 36 ) AFTER `contact_id` ; ALTER TABLE `sms_salesgroup` ADD `account_id` VARCHAR( 36 ) AFTER `contact_id` ; -- 2007-02-19 -- merdan ALTER TABLE `sms_salesgroup` ADD `bank_account` TEXT; -- 2007-02-21 -- merdan INSERT INTO `sms_transactiontype` ( `id`, `name`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `keiri_category`, `apply_new_student`, `show_in_edit_fee`, `keiri_code`, `description`) VALUES ('20', 'tuition_account', 0, '2007-02-08 00:00:00', '2007-02-08 00:00:00', NULL, NULL, 'account', 1, 1, 701, 'Tuition fee for accounts'); INSERT INTO `sms_transactiontype` ( `id`, `name`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `keiri_category`, `apply_new_student`, `show_in_edit_fee`, `keiri_code`, `description`) VALUES ('21', 'tuition_add_account', 0, '2007-02-08 00:00:00', '2007-02-08 00:00:00', NULL, NULL, 'account', 0, 1, 701, 'Additional tuition fee for accounts'); INSERT INTO `sms_transactiontype` ( `id`, `name`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `keiri_category`, `apply_new_student`, `show_in_edit_fee`, `keiri_code`, `description`) VALUES ('22', 'text_account', 0, '2007-02-08 00:00:00', '2007-02-08 00:00:00', NULL, NULL, 'account', 1, 1, 702, 'Textbook fee for accounts'); INSERT INTO `sms_transactiontype` ( `id`, `name`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `keiri_category`, `apply_new_student`, `show_in_edit_fee`, `keiri_code`, `description`) VALUES ('23', 'entrance_account', 0, '2007-02-08 00:00:00', '2007-02-08 00:00:00', NULL, NULL, 'account', 1, 1, 702, 'Entrance fee for accounts'); -- 2007-02-28 ALTER TABLE `sms_recurrence` CHANGE `recurrence_start` `actual_event_end` DATETIME DEFAULT '0000-00-00 00:00:00' ; -- 2007-03-04 INSERT INTO `email_templates` (`id`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `published`, `name`, `description`, `subject`, `body`, `body_html`, `deleted`) VALUES ('bbbbbbbb-600d-36dd-f6f0-bbbbbbbbbbbb', '2007-03-04 18:57:26', '2007-03-04 18:57:26', '1', '1', 'off', 'Birthday message DO NOT DELETE!', NULL, 'Happy birthday', '$contact_name 様\r\nお誕生日おめでとうございます。素敵な一日をお過ごし下さい。\r\nＧＥＳスタッフ一同', '$contact_name 様
\r\nお誕生日おめでとうございます。素敵な一日をお過ごし下さい。
\r\nTechnoSMSスタッフ一同', 0); INSERT INTO `prospect_lists` VALUES ('eac502b8-c80e-f647-dd2a-45e9a8f63efd', 'sssssss-1111', 'Birthday Email List', 'default', '2007-03-03 16:53:20', '2007-03-03 16:53:20', '1', '1', '1', 1, NULL); --2007-03-04 ALTER TABLE `session` ADD `user_name` VARCHAR( 60 ) AFTER `sess_id` ; -- 2007-03-20 -- merdan Seiseki Kanri module DROP TABLE IF EXISTS exam; CREATE TABLE IF NOT EXISTS exam ( id varchar(36) NOT NULL, date_entered datetime NOT NULL, date_modified datetime NOT NULL, `name` varchar(50) NOT NULL, exam_date datetime default NULL, instance_id varchar(36) default NULL, event_id varchar(36) default NULL, resource_id varchar(36) default NULL, school_id varchar(36) NOT NULL, deleted tinyint(1) NOT NULL default '0', description text ) ENGINE=MyISAM DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS exam_grades; CREATE TABLE IF NOT EXISTS exam_grades ( id varchar(36) NOT NULL default '', contact_id varchar(36) default NULL, exam_id varchar(36) NOT NULL, score varchar(30) default NULL, description text, PRIMARY KEY (id), KEY idx_accnt_id_del (id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- 2007-03-21 ALTER TABLE `sms_memo` ADD `category_main` VARCHAR( 16 ) ; ALTER TABLE `sms_memo` ADD `category_sub` VARCHAR( 16 ) ; -- 2007-03-28 ALTER TABLE `session` ADD INDEX ( `user_name` ); --2007-04-06 extend memo field to text in id card printing ALTER TABLE `contacts` CHANGE `id_card_field1` `id_card_field1` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL ; ALTER TABLE `contacts` CHANGE `id_card_field4` `id_card_field4` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL; --2007-04-26 adding an 'other' type fee to the fee list INSERT INTO `sms_transactiontype` (`id`, `name`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `keiri_category`, `apply_new_student`, `show_in_edit_fee`, `keiri_code`, `description`) VALUES ('17', 'student other', 0, '2007-04-28 00:00:00', '2007-04-28 00:00:00', NULL, NULL, 'student', 1, 1, 402, 'Other student fees'); --2007-04-30 adding an 'other' type fee to the fee list for accounts INSERT INTO `sms_transactiontype` ( `id`, `name`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `keiri_category`, `apply_new_student`, `show_in_edit_fee`, `keiri_code`, `description`) VALUES ('24', 'account other', 0, '2007-04-30 00:00:00', '2007-04-30 00:00:00', NULL, NULL, 'account', 1, 1, 702, 'Other fees for accounts'); -- 2007-05-02 merdan ALTER TABLE `sms_fee` ADD `jikyu_rate` VARCHAR( 20 ) ; ALTER TABLE `sms_salesgroup` ADD `hakkoubi` DATE NOT NULL ; ALTER TABLE `sms_salesgroup` ADD `seikyusaki` TEXT; -- 2007-05-09 merdan ALTER TABLE `contacts` ADD `renmei_label` TEXT AFTER `id_card_field4` ; -- 2007-05-23 -- merdan ALTER TABLE `emails` CHANGE `description_html` `description_html` MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL; -- 2007-07-03 -- merdan ALTER TABLE `exam` ADD `use_free_date` VARCHAR( 3 ) DEFAULT '0' NOT NULL ; -- 2007-07-14 -- merdan ALTER TABLE `smsevent` ADD `event_credit` VARCHAR( 10 ) AFTER `subtype` ; ALTER TABLE `event_contacts` ADD `event_credit` VARCHAR( 10 ) ; ALTER TABLE `sms_fee` ADD `event_credit` VARCHAR( 10 ) ; --2007-07-18 merdan just PHOENIX SCHOOL INSERT INTO `sms_transactiontype` ( `id` , `name` , `deleted` , `date_entered` , `date_modified` , `modified_user_id` , `created_by` , `keiri_category` , `apply_new_student` , `show_in_edit_fee` , `keiri_code` , `description` ) VALUES ( '25', 'tuition bonus', '0', '2007-07-15 00:00:00', '2007-07-15 00:00:00', NULL , NULL , 'student', '0', '1', '401', 'Bonus tuition fee for students' ); -- 2007-08-01 --- metehan ALTER TABLE `email_templates` ADD `from_addr` VARCHAR ( 255 ) ; -- 2007-08-17 --- metehan ALTER TABLE `contacts` CHANGE `nationality` `nationality` VARCHAR ( 16 ) CHARACTER SET utf8 COLLATE utf8_general_ci ; -- 2008-08-30 ALTER TABLE `sms_event_instance` CHANGE `status` `status` INT DEFAULT '0' ; ALTER TABLE `smsevent_instances` CHANGE `status` `status` INT DEFAULT '0'; -- 2007-09-25 --- metehan INSERT INTO `sms_transactiontype` (`id`, `name`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `keiri_category`, `apply_new_student`, `show_in_edit_fee`, `keiri_code`, `description`) VALUES ('27', 'tuition student kaisu', 0, '2007-09-15 00:00:00', '2007-09-15 00:00:00', NULL, NULL, 'student', 0, 1, 407, 'Tuition fees with even distribution'); -- 2007-11-03 --- metehan INSERT INTO `sms_transactiontype` (`id`, `name`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `keiri_category`, `apply_new_student`, `show_in_edit_fee`, `keiri_code`, `description`) VALUES ('28', 'tuition gessha kaisu', 0, '2007-11-03 12:00:00', '2007-11-03 12:00:00', NULL, NULL, 'student', 0, 1, 407, 'Gessha fees with even distribution'); ALTER TABLE `sms_fee` ADD `school_id` VARCHAR ( 36 ) AFTER `id` ; UPDATE sms_fee, contacts SET sms_fee.school_id = contacts.school_id WHERE sms_fee.contact_id = contacts.id ; UPDATE sms_fee, smsevent SET sms_fee.school_id = smsevent.school_id WHERE sms_fee.event_id = smsevent.id ; UPDATE sms_fee, contacts, users SET sms_fee.school_id = contacts.school_id WHERE sms_fee.created_by = users.id AND users.contact_id = contacts.id AND sms_fee.contact_id IS NULL AND sms_fee.event_id IS NULL ; -- 2007-10-19 --- metehan ALTER TABLE `sms_salesgroup` ADD INDEX ( `salesno` ) ; -- 2007-12-16 --- metehan ALTER TABLE `sms_salesgroup` ADD `tantosha` VARCHAR( 128 ) , ADD `item` VARCHAR( 128 ) ; -- 2008-01-14 --- metehan ALTER TABLE `contacts` ADD `extra_date` DATE AFTER `application_date` ; -- 2008-02-03 --- metehan ALTER TABLE `sms_salesgroup` CHANGE `unit_type` `unit_type` VARCHAR( 36 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ; ALTER TABLE `product` ADD `product_type` VARCHAR( 16 ) ; ALTER TABLE `product` ADD `ticket_count` INT DEFAULT '1' NOT NULL ; ALTER TABLE `product` ADD `ticket_valid_months` INT; -- 2008-02-06 --- metehan -- INSERT INTO `sms_transactiontype` VALUES ('35', 'ticket', 0, '2008-02-06 00:00:00', '2008-02-06 00:00:00', NULL, NULL, 'student', 1, 1, 406, 'Ticket type fees'); ALTER TABLE `sms_fee` DROP `occurences` , DROP `occurences_hour` ; ALTER TABLE `sms_fee` ADD `ticket_count` INT DEFAULT '0' NOT NULL AFTER `product_count` ; ALTER TABLE `sms_fee` ADD `ticket_valid` INT DEFAULT '0' AFTER `ticket_count` ; -- 2008-02-13 --- metehan ALTER TABLE `course_category` ADD `common_code` VARCHAR( 36 ) ; ALTER TABLE `resource` ADD `common_code` VARCHAR( 36 ) ; -- 2008-02-16 --- metehan -- INSERT INTO `sms_transactiontype` VALUES ('18', 'student extra', 0, '2008-02-16 00:00:00', '2008-02-16 00:00:00', NULL, NULL, 'student', 1, 1, 402, 'Extra student fee'); -- 2008-02-22 --- metehan ALTER TABLE `sms_salesgroup` ADD `school_code` VARCHAR ( 36 ) ; -- 2008-02-26 --- metehan ALTER TABLE `smsevent` ADD `common_code` VARCHAR( 36 ) ; -- 2008-04-28 -- -- Table structure for table `bankactions_history` -- CREATE TABLE `bankactions_history` ( `id` varchar(36) NOT NULL, `date_entered` datetime NOT NULL, `date_modified` datetime NOT NULL, `user_id` varchar(36) NOT NULL, `input_type` varchar(10) NOT NULL, `total_count` varchar(10) default '0', `total_in_amount` varchar(20) default '0', PRIMARY KEY `id` (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -- Table structure for table `bankinfo_history` -- CREATE TABLE `bankinfo_history` ( `id` varchar(36) NOT NULL, `date_entered` datetime NOT NULL, `date_modified` datetime NOT NULL, `bankactions_id` varchar(36) NOT NULL, `input_type` varchar(10) default NULL, `bank_code` varchar(4) default NULL, `bank_name` varchar(15) default NULL, `branch_code` varchar(3) default NULL, `branch_name` varchar(15) default NULL, `account` varchar(7) default NULL, `name` varchar(30) default NULL, `in_amount` varchar(10) default NULL, `new_code` varchar(1) default NULL, `salesno` varchar(10) default NULL, `result_code` varchar(1) default NULL, `error_code` varchar(20) default '0', PRIMARY KEY `id` (`id`), KEY `bankactions_id` (`bankactions_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -- Table structure for table `keiriposactions_history` -- CREATE TABLE `keiriposactions_history` ( `id` varchar(36) NOT NULL, `school_id` varchar(36) NOT NULL, `date_entered` datetime NOT NULL, `date_modified` datetime NOT NULL, `user_id` varchar(36) NOT NULL, `total_count` varchar(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -- Table structure for table `keiripos_history` -- CREATE TABLE `keiripos_history` ( `id` varchar(36) NOT NULL, `date_entered` datetime NOT NULL, `date_modified` datetime NOT NULL, `keiriposactions_id` varchar(36) NOT NULL, `sell_date` date default NULL, `shop_id` varchar(4) default NULL, `sell_hour` varchar(2) default NULL, `sell_minute` varchar(2) default NULL, `tantou_code` varchar(20) default NULL, `contact_idnumber` varchar(10) default NULL, `pos_type1` varchar(30) default NULL, `pos_type2` varchar(30) default NULL, `bumon` varchar(3) default NULL, `sub_genre` varchar(10) default NULL, `product_code` varchar(12) default NULL, `product_name` varchar(20) default '0', `jan_code` varchar(13) default NULL, `basic_fee` varchar(10) default NULL, `product_count` varchar(4) default NULL, `basic_fee_total` varchar(10) default NULL, `product_stock` varchar(5) default NULL, `idou_average` varchar(8) default NULL, `product_original_price` varchar(10) default NULL, `tax_class` varchar(1) default NULL, `contact_age` varchar(4) default NULL, `contact_gender` varchar(3) default NULL, `process_status` varchar(30) default NULL, PRIMARY KEY (`id`), KEY `bankactions_id` (`keiriposactions_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- 2008-05-12 ALTER TABLE `bankactions_history` ADD `total_count_no_error` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0', ADD `total_in_amount_no_error` VARCHAR( 20 ) NULL DEFAULT '0', ADD `total_count_error` VARCHAR( 10 ) NULL DEFAULT '0', ADD `total_in_amount_error` VARCHAR( 20 ) NULL DEFAULT '0'; -- 2008-05-16 ALTER TABLE `bankinfo_history` ADD `customer_code` VARCHAR( 20 ) NULL AFTER `salesno` ; -- 2008-06-09 ALTER TABLE `bankinfo_history` ADD `contact_id` VARCHAR( 36 ) NOT NULL AFTER `bankactions_id` ; -- 2008-06-12 ALTER TABLE `sms_fee` ADD `fee_subcategory` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ; -- 2008-06-24 ALTER TABLE `sms_fee` CHANGE `discount_price` `discount_price` DOUBLE NULL DEFAULT NULL ; -- 2008-06-29 -- INSERT INTO `sms_transactiontype` VALUES ('29', 'tuition kaisu course', 0, '2008-06-29 12:00:00', '2008-06-29 12:00:00', NULL, NULL, 'student', 1, 1, 407, 'Course tuition fees with even distribution'); -- 2008-07-24 CREATE TABLE `email_templates_category` ( `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, `name` varchar(255) default NULL, `description` text, `deleted` tinyint(1) NOT NULL default '0', `template_category_status` varchar(20) default NULL, PRIMARY KEY (`id`), KEY `idx_email_template_name` (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; ALTER TABLE `email_templates` ADD `category_id` VARCHAR( 36 ) NULL ; ALTER TABLE `email_marketing` ADD `template_category_id` VARCHAR( 36 ) NULL AFTER `template_id` ; -- 2008-07-24 INSERT INTO `fields_meta_data` (`id` ,`name` ,`label` ,`custom_module` ,`data_type` ,`max_size` ,`required_option` ,`default_value` ,`date_modified` ,`deleted` ,`audited` ,`ext1` ,`ext2` ,`ext3` ) VALUES ('Schoolshimebi_c', 'shimebi_c', 'shimebi_c', 'School', 'varchar', '20', 'optional', NULL , '2008-07-24 07:33:33', '0', '0', 'moduleList', '0', '0'); ALTER TABLE `resourceschool_cstm` ADD `shimebi_c` DATE NOT NULL DEFAULT '2001-01-01'; -- 2008-07-26 ALTER TABLE `smsevent_instances` ADD `date_modified` DATETIME NOT NULL default '0000-00-00 00:00:00' AFTER `id` ; ALTER TABLE `sms_recurrence` ADD `actual_event_start` DATETIME NULL DEFAULT '0000-00-00 00:00:00' AFTER `created_by` ; -- 2008-07-29 CREATE TABLE `smsevent_instances_backup` ( `id` varchar( 36 ) NOT NULL default '', `date_modified` datetime default NULL , `event_id` varchar( 36 ) NOT NULL default '', `event_type` varchar( 15 ) default NULL , `instance_id` varchar( 36 ) NOT NULL default '', `target_id` varchar( 36 ) NOT NULL default '', `category` varchar( 25 ) NOT NULL default '', `from_datetime` datetime NOT NULL default '0000-00-00 00:00:00', `to_datetime` datetime NOT NULL default '0000-00-00 00:00:00', `status` int( 11 ) default '0', `from_instance_id` varchar( 36 ) default NULL , `to_instance_id` varchar( 36 ) default NULL , `attendance_status` tinyint( 1 ) default NULL , `instance_contact_status` tinyint( 1 ) default NULL , `name` text, `description` text, PRIMARY KEY ( `event_id` , `target_id` , `from_datetime` , `to_datetime` ) , UNIQUE KEY `id_index` ( `id` ) , KEY `event_id` ( `event_id` , `event_type` ) , KEY `instance_id` ( `instance_id` , `status` ) , KEY `target_from` ( `target_id` , `from_datetime` ) , KEY `target_to` ( `target_id` , `to_datetime` ) , KEY `target_id` ( `target_id` , `from_datetime` , `to_datetime` ) ) ENGINE = MyISAM DEFAULT CHARSET = utf8; CREATE TABLE `sms_event_instance_backup` ( `id` varchar( 36 ) NOT NULL default '', `parent_id` varchar( 36 ) NOT NULL default '', `event_type` varchar( 15 ) default NULL , `from_datetime` datetime NOT NULL default '0000-00-00 00:00:00', `to_datetime` datetime NOT NULL default '0000-00-00 00:00:00', `name` text, `description` text, `description2` text, `description3` text, `date_modified` datetime NOT NULL default '0000-00-00 00:00:00', `deleted` tinyint( 1 ) NOT NULL default '0', `status` int( 11 ) default '0', `from_id` varchar( 36 ) default NULL , `to_id` varchar( 36 ) default NULL , `reason` text, PRIMARY KEY ( `id` ) , KEY `from_to_datetime` ( `from_datetime` , `to_datetime` , `status` ) , KEY `from_id` ( `from_id` ) , KEY `to_id` ( `to_id` ) , KEY `parent_id` ( `parent_id` , `from_datetime` , `to_datetime` ) ) ENGINE = MyISAM DEFAULT CHARSET = utf8; ALTER TABLE `smsevent_instances_backup` ADD `deleted_date_modified` DATETIME NULL ; ALTER TABLE `sms_event_instance_backup` ADD `deleted_date_modified` DATETIME NULL ; ALTER TABLE `smsevent_instances_backup` ADD INDEX `deleted_date_modified` (`deleted_date_modified`) ; ALTER TABLE `sms_event_instance_backup` ADD INDEX `deleted_date_modified` (`deleted_date_modified`); ALTER TABLE `sms_event_instance_backup` DROP PRIMARY KEY ; ALTER TABLE `smsevent_instances_backup` DROP PRIMARY KEY ; -- 2008-09-08 (To remove unused Modules from list, use it wisely) DELETE FROM `acl_actions` WHERE category='Bugs' ; DELETE FROM `acl_actions` WHERE category='Calls' ; DELETE FROM `acl_actions` WHERE category='Cases' ; -- DELETE FROM `acl_actions` WHERE category='EditCustomVars' ; -- DELETE FROM `acl_actions` WHERE category='EditLabelCustom' ; DELETE FROM `acl_actions` WHERE category='Leads' ; DELETE FROM `acl_actions` WHERE category='Opportunities' ; -- 2008-10-03 -- HAVE TO DELETE KEY INDEXES FOR SCHEDULE BACKUP ALTER TABLE `smsevent_instances_backup` DROP INDEX `id_index`; -- 2008-10-09 ALTER TABLE `sms_transaction` ADD `unreal` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ; -- 2008-10-14 ALTER TABLE `sms_recurrence` CHANGE `number_of_occurrences` `number_of_occurrences` INT( 4 ) NULL DEFAULT NULL ; -- 2008-10-29 ALTER TABLE `sms_transaction` DROP `unreal` ; -- 2008-11-13 ALTER TABLE `sms_transaction` CHANGE `payment_type` `payment_type` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL; -- 2008-11-27 DROP TABLE `sms_groupsaleno`; -- 2008-12-10 ALTER TABLE `event_contacts` ADD `description` TEXT NULL ; -- 2009-01-08 ALTER TABLE `sms_salesgroup` ADD `receipt_hakkoubi` DATE NULL AFTER `hakkoubi` ; -- 2009-04-08 ALTER TABLE `bankinfo_history` ADD INDEX ( `salesno` ) ; ALTER TABLE `sms_transaction` ADD INDEX ( `salesno` ); -- 2009-06-18 ALTER TABLE `sms_fee` ADD `source_gessha_id` VARCHAR( 36 ) NULL , ADD `next_gessha_applied` VARCHAR( 1 ) NOT NULL DEFAULT '0'; -- 2009-07-10 ALTER TABLE `resourceschool_cstm` ADD `bank_account_receipt_c` TEXT NULL AFTER `bank_account_c` ; INSERT INTO `fields_meta_data` VALUES ('Schoolbank_account_receipt_c', 'bank_account_receipt_c', 'bank_account_receipt_c', 'School', 'text', 255, 'optional', NULL, '2009-07-10 01:05:37', 0, 0, 'moduleList', '0', '0'); -- 2009-07-21 => empty seiseki and exam tables for new schools! -- -- Table structure for table `exam` -- DROP TABLE IF EXISTS `exam`; CREATE TABLE `exam` ( `id` varchar(36) NOT NULL, `date_entered` datetime NOT NULL, `date_modified` datetime NOT NULL, `name` varchar(50) NOT NULL, `exam_date` datetime default NULL, `instance_id` varchar(36) default NULL, `event_id` varchar(36) default NULL, `resource_id` varchar(36) default NULL, `school_id` varchar(36) NOT NULL, `deleted` tinyint(1) NOT NULL default '0', `description` text, `use_free_date` varchar(3) NOT NULL default '0', `max_point` int(11) NOT NULL default '100', `exam_takable_by` varchar(32) NOT NULL default 'all', `exam_type` varchar(32) NOT NULL default 'juku_small', `exam_subtype1` varchar(32) default NULL, `exam_subtype2` varchar(32) default NULL, `exam_subtype3` varchar(32) default NULL, `exam_subtype4` varchar(32) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Table structure for table `exam_grades` -- DROP TABLE IF EXISTS `exam_grades`; CREATE TABLE `exam_grades` ( `id` varchar(36) NOT NULL default '', `contact_id` varchar(36) NOT NULL default '', `exam_id` varchar(36) NOT NULL, `subject_id` varchar(36) NOT NULL default '', `score` varchar(30) default NULL, `description` text, PRIMARY KEY (`contact_id`,`exam_id`,`subject_id`), KEY `idx_accnt_id_del` (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Table structure for table `exam_subject` -- DROP TABLE IF EXISTS `exam_subject`; CREATE TABLE `exam_subject` ( `id` varchar(36) NOT NULL default '', `exam_id` varchar(36) default NULL, `subject_id` varchar(36) default NULL, `date_modified` datetime default NULL, `deleted` tinyint(1) NOT NULL default '0', `max_point` int(11) NOT NULL default '100', `weight` double NOT NULL default '1', PRIMARY KEY (`id`), KEY `contact_id` (`exam_id`), KEY `person_id` (`subject_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Table structure for table `gakko_contacts` -- DROP TABLE IF EXISTS `gakko_contacts`; CREATE TABLE `gakko_contacts` ( `id` varchar(36) NOT NULL default '', `contact_id` varchar(36) default NULL, `gakko_id` varchar(36) default NULL, `date_modified` datetime default NULL, `deleted` tinyint(1) NOT NULL default '0', `type` varchar(32) default NULL, `index` tinyint(2) NOT NULL default '1', PRIMARY KEY (`id`), KEY `contact_id` (`contact_id`), KEY `gakko_id` (`gakko_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Table structure for table `gakkomaster` -- DROP TABLE IF EXISTS `gakkomaster`; CREATE TABLE `gakkomaster` ( `id` varchar(36) NOT NULL, `name` varchar(128) NOT NULL, `category` varchar(32) NOT NULL, `subcategory` varchar(32) default NULL, `state` varchar(32) NOT NULL, `city` varchar(64) default NULL, `global_id` varchar(10) default NULL, `description` text, `deleted` tinyint(1) NOT NULL default '0', `date_entered` datetime NOT NULL default '0000-00-00 00:00:00', `date_modified` datetime default '0000-00-00 00:00:00', `modified_user_id` varchar(36) default NULL, `created_by` varchar(36) default NULL, PRIMARY KEY (`id`), KEY `category` (`category`,`subcategory`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; /* START seiseki -> SQL commads to update the schoolmng database during seiseki kanri implementation */ -- 2009-06-08 ALTER TABLE `gakkomaster` ADD `description` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL AFTER `global_id` ; ALTER TABLE `exam_grades` ADD `subject_id` VARCHAR( 36 ) DEFAULT NULL AFTER `exam_id` ; UPDATE exam_grades, exam SET exam_grades.subject_id=exam.resource_id WHERE exam_grades.exam_id = exam.id AND exam.resource_id NOT LIKE '0' ; ALTER TABLE `exam` ADD `exam_subtype1` VARCHAR( 32 ) , ADD `exam_subtype2` VARCHAR( 32 ) , ADD `exam_subtype3` VARCHAR( 32 ) ; ALTER TABLE `exam_grades` DROP PRIMARY KEY, ADD PRIMARY KEY ( `contact_id` , `exam_id` , `subject_id` ) ; -- 2009-06-26 ALTER TABLE `exam` ADD `exam_subtype4` VARCHAR ( 32 ) ; /* END seiseki */ -- 2009-08-10 ALTER TABLE `exam_grades` CHANGE `subject_id` `subject_id` VARCHAR( 36 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ; -- 2010-01-15 ALTER TABLE `tasks` ADD `task_reminder` INT( 10 ) NOT NULL DEFAULT '0', ADD `task_reminder_sent` INT( 10 ) NOT NULL DEFAULT '0'; -- 2010-01-18 CREATE TABLE IF NOT EXISTS `nyutai` ( `id` varchar(36) NOT NULL, `contact_id` varchar(36) default NULL, `school_id` varchar(36) default NULL, `idnumber` varchar(20) default NULL, `date_entered` datetime NOT NULL, `date_modified` datetime NOT NULL, `nyutai_type` varchar(20) default NULL, `entry_type` varchar(20) default NULL, `result_type` varchar(20) default NULL, `appear_datetime` datetime NOT NULL, `deleted` varchar(1) NOT NULL default '0', `description` text, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- 2010-02-08 ALTER TABLE `smsevent` ADD `schedule_reminder` INT( 10 ) NOT NULL DEFAULT '0' AFTER `event_credit` ; -- 2010-04-26 CREATE TABLE IF NOT EXISTS `combiniactions_history` ( `id` varchar(36) NOT NULL, `date_entered` datetime NOT NULL, `date_modified` datetime NOT NULL, `user_id` varchar(36) NOT NULL, `input_type` varchar(10) NOT NULL, `total_count` varchar(10) DEFAULT '0', `total_in_amount` varchar(20) DEFAULT '0', `total_count_no_error` varchar(10) DEFAULT '0', `total_in_amount_no_error` varchar(20) DEFAULT '0', `total_count_error` varchar(10) DEFAULT '0', `total_in_amount_error` varchar(20) DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 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, `data_type` varchar(2) DEFAULT NULL, `t_date` varchar(12) DEFAULT NULL, `aplus_use0` varchar(2) DEFAULT NULL, `salesno` varchar(11) DEFAULT NULL, `aplus_use1` varchar(9) DEFAULT NULL, `branch_id` varchar(4) DEFAULT NULL, `aplus_use2` varchar(2) DEFAULT NULL, `due_date` varchar(5) DEFAULT NULL, `in_amount` varchar(6) DEFAULT NULL, `aplus_use3` varchar(1) DEFAULT NULL, `store_code` varchar(8) DEFAULT NULL, `aplus_use4` varchar(24) DEFAULT NULL, `store_name` varchar(4) DEFAULT NULL, `extra_payment` varchar(6) DEFAULT NULL, `flag` varchar(1) DEFAULT NULL, `barcode` varchar(44) DEFAULT NULL, `payment_date` varchar(8) DEFAULT NULL, PRIMARY KEY (`id`), KEY `bankactions_id` (`combiniactions_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; ALTER TABLE `combini_history` ADD `contact_id` VARCHAR( 36 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL AFTER `combiniactions_id` ; ALTER TABLE `combini_history` ADD `error_code` VARCHAR( 20 ) NULL DEFAULT NULL ; -- 2010-05-07 ALTER TABLE `emailman` ADD `email_type` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'email1'; -- 2010-05-18 ALTER TABLE `bankactions_history` ADD `school_id` VARCHAR( 36 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'sssssss-1111'; -- 2010-07-15 applied on all SMS DBs on e-school, demo and niki servers -- ALTER TABLE `sms_fee` ADD INDEX ( `type_id` ) ; -- 2010-07-16 yahya CREATE TABLE IF NOT EXISTS `acl_session` ( `user_id` varchar(36) NOT NULL, `acl_text` mediumtext NOT NULL, `date_modified` datetime NOT NULL, PRIMARY KEY (`user_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- 2010-08-06 email table change CREATE TABLE `emailsemail_cstm` SELECT * FROM `emails`; ALTER TABLE `emailsemail_cstm` CHANGE `id` `id_c` VARCHAR( 36 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL; ALTER TABLE `emailsemail_cstm` DROP `date_entered` , DROP `date_modified` , DROP `assigned_user_id` , DROP `modified_user_id` , DROP `created_by` , DROP `name` , DROP `date_start` , DROP `time_start` , DROP `parent_type` , DROP `parent_id` , DROP `from_addr` , DROP `from_name` , DROP `type` , DROP `status` , DROP `message_id` , DROP `reply_to_name` , DROP `reply_to_addr` , DROP `intent` , DROP `mailbox_id` , DROP `deleted` ; ALTER TABLE `emailsemail_cstm` ADD PRIMARY KEY ( `id_c` ) ; INSERT INTO `fields_meta_data` (`id`, `name`, `label`, `custom_module`, `data_type`, `max_size`, `required_option`, `default_value`, `date_modified`, `deleted`, `audited`, `ext1`, `ext2`, `ext3`) VALUES ('Emailsid_c', 'id_c', 'id_c', 'Emails', 'text', 255, 'optional', NULL, '0000-00-00 00:00:00', 0, 0, 'moduleList', '0', '0'), ('Emailsdescription', 'description', 'description', 'Emails', 'text', 255, 'optional', NULL, '0000-00-00 00:00:00', 0, 0, 'moduleList', '0', '0'), ('Emailsdescription_html', 'description_html', 'description_html', 'Emails', 'text', 255, 'optional', NULL, '0000-00-00 00:00:00', 0, 0, 'moduleList', '0', '0'), ('Emailsto_addrs', 'to_addrs', 'to_addrs', 'Emails', 'text', 255, 'optional', NULL, '0000-00-00 00:00:00', 0, 0, 'moduleList', '0', '0'), ('Emailscc_addrs', 'cc_addrs', 'cc_addrs', 'Emails', 'text', 255, 'optional', NULL, '0000-00-00 00:00:00', 0, 0, 'moduleList', '0', '0'), ('Emailsbcc_addrs', 'bcc_addrs', 'bcc_addrs', 'Emails', 'text', 255, 'optional', NULL, '0000-00-00 00:00:00', 0, 0, 'moduleList', '0', '0'), ('Emailsto_addrs_ids', 'to_addrs_ids', 'to_addrs_ids', 'Emails', 'text', 255, 'optional', NULL, '0000-00-00 00:00:00', 0, 0, 'moduleList', '0', '0'), ('Emailsto_addrs_names', 'to_addrs_names', 'to_addrs_names', 'Emails', 'text', 255, 'optional', NULL, '0000-00-00 00:00:00', 0, 0, 'moduleList', '0', '0'), ('Emailsto_addrs_emails', 'to_addrs_emails', 'to_addrs_emails', 'Emails', 'text', 255, 'optional', NULL, '0000-00-00 00:00:00', 0, 0, 'moduleList', '0', '0'), ('Emailscc_addrs_ids', 'cc_addrs_ids', 'cc_addrs_ids', 'Emails', 'text', 255, 'optional', NULL, '0000-00-00 00:00:00', 0, 0, 'moduleList', '0', '0'), ('Emailscc_addrs_names', 'cc_addrs_names', 'cc_addrs_names', 'Emails', 'text', 255, 'optional', NULL, '0000-00-00 00:00:00', 0, 0, 'moduleList', '0', '0'), ('Emailscc_addrs_emails', 'cc_addrs_emails', 'cc_addrs_emails', 'Emails', 'text', 255, 'optional', NULL, '0000-00-00 00:00:00', 0, 0, 'moduleList', '0', '0'), ('Emailsbcc_addrs_ids', 'bcc_addrs_ids', 'bcc_addrs_ids', 'Emails', 'text', 255, 'optional', NULL, '0000-00-00 00:00:00', 0, 0, 'moduleList', '0', '0'), ('Emails', 'bcc_addrs_emails', 'bcc_addrs_emails', 'Emails', 'text', 255, 'optional', NULL, '0000-00-00 00:00:00', 0, 0, 'moduleList', '0', '0'); ALTER TABLE `emails` ADD UNIQUE `id_deleted` ( `id` , `deleted` ) ; ALTER TABLE `emails` ADD INDEX ( `assigned_user_id` ); -- apply this after code update ALTER TABLE `emails` DROP `description` , DROP `description_html` , DROP `to_addrs` , DROP `cc_addrs` , DROP `bcc_addrs` , DROP `to_addrs_ids` , DROP `to_addrs_names` , DROP `to_addrs_emails` , DROP `cc_addrs_ids` , DROP `cc_addrs_names` , DROP `cc_addrs_emails` , DROP `bcc_addrs_ids` , DROP `bcc_addrs_names` , DROP `bcc_addrs_emails` ; -- Parent module is changed into ParentSMS module, so we need to delete Parent from ACL table, session tables DELETE FROM `acl_actions` WHERE `category` LIKE 'Parent' ; TRUNCATE TABLE `acl_session`; TRUNCATE TABLE `session`; -- new server phmyadmin user information db3tcpmaster 94pwVh5CGD -- 2010-09-15 yahya CREATE TABLE IF NOT EXISTS `sms_memo_templates` ( `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, `created_by` varchar(36) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `memo_description` text, `deleted` tinyint(1) NOT NULL DEFAULT '0', `status` tinyint(4) NOT NULL DEFAULT '0', `school_id` varchar(36) DEFAULT NULL, `category_main` varchar(16) DEFAULT NULL, `category_sub` varchar(16) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_note_name` (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- attendance status settings ALTER TABLE `smsevent_instances` CHANGE `attendance_status` `attendance_status` SMALLINT NULL DEFAULT NULL ; -- 2010-10-09 Batch Processing CREATE TABLE IF NOT EXISTS `batch_items` ( `id` varchar(36) NOT NULL, `batch_id` varchar(36) NOT NULL, `item_key` varchar(255) NOT NULL, `item_info1` varchar(255) NOT NULL, `item_info2` varchar(255) NOT NULL, `status` tinyint(1) NOT NULL DEFAULT '0', `sort_order` int(11) NOT NULL, `deleted` tinyint(4) NOT NULL DEFAULT '0' ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; CREATE TABLE IF NOT EXISTS `batch_manager` ( `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', `created_by` varchar(36) DEFAULT NULL, `modified_user_id` varchar(36) DEFAULT NULL, `name` varchar(255) NOT NULL DEFAULT '', `category` varchar(20) NOT NULL, `date_time_start` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `date_time_end` datetime DEFAULT NULL, `interval_secs` int(100) NOT NULL, `last_run` datetime DEFAULT NULL, `percentage_completed` float NOT NULL DEFAULT '0', `status` tinyint(1) NOT NULL DEFAULT '0', `deleted` tinyint(4) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `idx_schedule` (`date_time_start`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- 2010-10-18 Bank / Combini ALTER TABLE `bankactions_history` ADD `data_date` DATE NULL; CREATE TABLE IF NOT EXISTS `bankexport_salesnos` ( `hikiotoshi_date` varchar(4) CHARACTER SET latin1 NOT NULL, `salesno` varchar(10) CHARACTER SET latin1 NOT NULL, `contact_id` varchar(36) CHARACTER SET latin1 NOT NULL, `date_modified` datetime NOT NULL, `status` tinyint(4) NOT NULL, KEY `hikiotoshi_date` (`hikiotoshi_date`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- DROP TABLE IF EXISTS `combini_history`; 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(11) DEFAULT NULL, `branch_id` varchar(4) 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, PRIMARY KEY (`id`), KEY `combiniactions_id` (`combiniactions_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- attendance DB fix queries NOTE THAT ORDER IS IMPORTANT -- SELECT * FROM smsevent_instances WHERE (category = 'student' OR category = 'teacher' ) AND attendance_status IS NULL AND from_datetime < '2011-02-01 15:00:00' -- 1 shusseki update UPDATE smsevent_instances SET attendance_status = 2 WHERE (category = 'student' OR category = 'teacher' ) AND attendance_status IS NOT NULL AND from_datetime < '2011-02-16 01:00:00' ; -- 2 kesseki update UPDATE smsevent_instances SET attendance_status = 1 WHERE (category = 'student' OR category = 'teacher' ) AND attendance_status IS NULL AND from_datetime < '2011-02-16 01:00:00' ; -- 2010-11-01 ALTER TABLE `combini_history` CHANGE `branch_id` `branch_id` VARCHAR( 36 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ; -- 2010-11-12 (index for transaction table) ALTER TABLE `sms_transaction` ADD INDEX ( `salesno` ); ALTER TABLE `sms_transaction` ADD INDEX ( `due_date` ); -- 2010-11-23 CREATE TABLE IF NOT EXISTS `exam_student` ( `contact_id` varchar(36) NOT NULL DEFAULT '', `exam_id` varchar(36) NOT NULL, `memo` varchar(64) DEFAULT NULL, PRIMARY KEY (`contact_id`,`exam_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- 2010-12-15 ALTER TABLE `batch_manager` ADD `query` TEXT NOT NULL , ADD `filename` VARCHAR( 255 ) NOT NULL DEFAULT ''; ALTER TABLE `batch_manager` ADD `freeparam1` VARCHAR( 255 ) NOT NULL , ADD `freeparam2` VARCHAR( 255 ) NOT NULL , ADD `freeparam3` VARCHAR( 255 ) NOT NULL ; ALTER TABLE `batch_items` ADD `item_info3` VARCHAR( 255 ) NOT NULL DEFAULT '' AFTER `item_info2` , ADD `item_info4` VARCHAR( 255 ) NOT NULL DEFAULT '' AFTER `item_info3`; ALTER TABLE `batch_items` CHANGE `item_info1` `item_info1` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '', CHANGE `item_info2` `item_info2` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT ''; -- 2011-01-13 ALTER TABLE `emailman` ADD `send_options` INT NOT NULL DEFAULT '0', ADD `status` INT NOT NULL DEFAULT '0'; -- 2011-01-20 CREATE TABLE IF NOT EXISTS `config_export_fields` ( `module` varchar(32) NOT NULL, `suffix` varchar(32) NOT NULL, `user_id` varchar(36) NOT NULL, `field` varchar(64) NOT NULL, `deleted` tinyint(1) NOT NULL DEFAULT '0', `date_updated` date NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- 2011-02-02 ALTER TABLE `bankexport_salesnos` CHANGE `salesno` `salesno` VARCHAR( 20 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL ; ALTER TABLE `combini_history` CHANGE `salesno` `salesno` VARCHAR( 20 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL; ALTER TABLE `combini_history` ADD `student_id` VARCHAR( 20 ) NULL DEFAULT NULL; -- 2011-02-17 ALTER TABLE `combini_history` DROP `branch_id`; -- 2011-03-11 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; ALTER TABLE `productinout` CHANGE `price` `price` DECIMAL( 20, 2 ) NULL DEFAULT '0.00' ; -- new config file (config_lock) CREATE TABLE IF NOT EXISTS `config_lock` ( `category` varchar( 36 ) DEFAULT NULL , `name` varchar( 36 ) DEFAULT NULL , `value` text ) ENGINE = MYISAM DEFAULT CHARSET = utf8; -- insert a new row into config_lock table to store the last combini unique id INSERT INTO config_lock ( `category` , `name` , `value`) VALUES ( 'unique_id', 'combini_sakusei_id', '0' ); -- Felica Nyutaishitsu ALTER TABLE `contacts` ADD `card_id` VARCHAR( 36 ) NOT NULL AFTER `renmei_label` ; ALTER TABLE `contacts` ADD INDEX ( `card_id` ) ; ALTER TABLE `resource` ADD `activation_key` VARCHAR( 36 ) NOT NULL AFTER `common_code` ; -- 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; -- Attendance early/late times ALTER TABLE `smsevent_instances` ADD `early_late_time2` TINYINT NOT NULL DEFAULT '0', ADD `early_late_time3` TINYINT NOT NULL DEFAULT '0'; -- Attendance memo ALTER TABLE `smsevent_instances` ADD `attendance_memo` VARCHAR( 255 ) NOT NULL; -- kantan login ALTER TABLE `users` ADD `kantan_login_id` VARCHAR( 100 ) NULL ; -- session table update ALTER TABLE `session` ADD `access_from` CHAR( 1 ) NULL; -- POLL TABLES 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; 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; 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; -- Contacts ALTER TABLE `contacts` ADD INDEX ( `idnumber` ); ALTER TABLE `contacts` ADD INDEX ( `date_modified` ); ALTER TABLE `contacts` ADD INDEX ( `phone_home` ); ALTER TABLE `contacts` ADD INDEX ( `phone_mobile` ); ALTER TABLE `contacts` ADD INDEX ( `leave_date` ); -- Poll vote table_update ALTER TABLE `poll_vote` ADD `answers_excel` TEXT NOT NULL AFTER `answers` ; 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, 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 ; -- Manual contact import ALTER TABLE `manual_contact` ADD INDEX ( `idnumber` ); -- Emailman_update ALTER TABLE `emailman` ADD `replace_vars` TEXT NOT NULL DEFAULT ''; -- Schedule smsevent_instances ALTER TABLE `smsevent_instances` DROP PRIMARY KEY , ADD PRIMARY KEY ( `event_id` , `instance_id` , `target_id` ) ; ALTER TABLE `smsevent_instances` ADD INDEX ( `from_datetime` , `to_datetime` ) ; -- 2011-11-13 Manual contact fix ALTER TABLE `manual_contact` ADD `grade_entered` VARCHAR( 10 ) NULL AFTER `grade` ; ALTER TABLE `manual_contact` ADD `card_id` VARCHAR( 36 ) NOT NULL AFTER `renmei_label` ; -- 2011-11-20 General Contact info import (mapping) CREATE TABLE IF NOT EXISTS `config_import_contact_fields` ( `id` varchar(36) CHARACTER SET utf8 NOT NULL, `config_id` varchar(36) NOT NULL, `db_field_name` varchar(50) CHARACTER SET utf8 NOT NULL, `import_field_name` varchar(100) CHARACTER SET utf8 NOT NULL, `import_field_order` int(11) NOT NULL, `validation_type` varchar(20) CHARACTER SET utf8 NOT NULL, `required` tinyint(4) NOT NULL DEFAULT '0', PRIMARY KEY (`config_id`,`db_field_name`) ) ENGINE=MyISAM CHARSET=utf8; CREATE TABLE IF NOT EXISTS `config_import_info` ( `id` varchar(36) NOT NULL, `main_category` enum('contact','event','fee','schedule') CHARACTER SET utf8 NOT NULL, `sub_category` enum('Student','Teacher','Contact','Staff','Course','Fee') CHARACTER SET utf8 DEFAULT NULL, `import_sub_info` varchar(50) CHARACTER SET utf8 DEFAULT NULL, `name` varchar(255) CHARACTER SET utf8 NOT NULL, `user_type` enum('admin','user') CHARACTER SET utf8 NOT NULL DEFAULT 'user', `deleted` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM CHARSET=utf8; -- 2011-11-30 Poll description fields ALTER TABLE `poll` ADD `description1` TEXT NULL AFTER `description` , ADD `description2` TEXT NULL AFTER `description1` ; -- 2012-02-06 salesno DB field update ALTER TABLE `sms_fee` CHANGE `salesno` `salesno` VARCHAR( 11 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ; ALTER TABLE `sms_transaction` CHANGE `salesno` `salesno` VARCHAR( 11 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ; ALTER TABLE `sms_salesgroup` CHANGE `salesno` `salesno` VARCHAR( 11 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ; -- 2012-02-07 NyuTai (Attendance) table CREATE TABLE IF NOT EXISTS `sms_entrance` ( `id` varchar(36) NOT NULL, `resource_id` varchar(36) default NULL, `card_id` varchar(32) default NULL, `contact_id` varchar(36) default NULL, `device_id` varchar(255) character set utf8 default NULL, `card_type` varchar(20) default NULL, `date_entered` datetime NOT NULL, `date_modified` datetime NOT NULL, `card_input_date` datetime default NULL, `deleted` tinyint(4) NOT NULL, `modified_user_id` varchar(36) NOT NULL, `created_by` varchar(36) NOT NULL, `status` tinyint(4) NOT NULL default '1', KEY `resource_id` (`resource_id`), KEY `contact_id` (`contact_id`), KEY `card_input_date` (`card_input_date`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- 2012-03-14 unique_id combini transaction backup table (ex: childvillage) CREATE TABLE IF NOT EXISTS `trans_combini_hist` ( `transaction_id` varchar(36) NOT NULL, `contact_id` varchar(36) NOT NULL, `old_combini_sakusei_id` varchar(16) NOT NULL, `new_combini_sakusei_id` varchar(16) NOT NULL, `date_modified` datetime NOT NULL, PRIMARY KEY (`transaction_id`,`contact_id`,`old_combini_sakusei_id`,`new_combini_sakusei_id`), KEY `old_combini_sakusei_id` (`old_combini_sakusei_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- 2012-03-23 ALTER TABLE `event_contacts` ADD INDEX ( `start_date` ) ; -- 2012-04-09 ALTER TABLE `sms_entrance` ADD `status` TINYINT NOT NULL DEFAULT '1' ; -- 2012-04-24 ALTER TABLE `contacts` CHANGE `card_id` `card_id` VARCHAR( 36 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ; -- 2012-07-10 -- combini sakusei id ALTER TABLE `sms_transaction` ADD `combini_sakusei_id` INT NOT NULL DEFAULT '0'; ALTER TABLE `sms_transaction` CHANGE `combini_sakusei_id` `combini_sakusei_id` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0'; ALTER TABLE `sms_transaction` ADD INDEX ( `combini_sakusei_id` ); -- 2012-07-23 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` ; -- 2012-07-25 ALTER TABLE `batch_manager` CHANGE `category` `category` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ; -- 2012-08-30 ALTER TABLE `batch_manager` ADD `school_id` VARCHAR( 36 ) NULL DEFAULT NULL ; -- 2012-09-04 ALTER TABLE `bankinfo_history` ADD INDEX ( `contact_id` ) ; -- 2012-10-10 ALTER TABLE `users` CHANGE `user_preferences` `user_preferences` MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ; -- 2012-10-16 -- once tableli sec ve table ismini degistir -- emailsemail_cstm => emails_sub1 RENAME TABLE `emailsemail_cstm` TO `emails_sub1` ; ALTER TABLE `emails_sub1` CHANGE `id_c` `id_sub1` VARCHAR( 36 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ; -- once tableli sec ve table ismini degistir -- resourceschool_cstm => resource_sub1 RENAME TABLE `resourceschool_cstm` TO `resource_sub1` ; ALTER TABLE `resource_sub1` CHANGE `id_c` `id_sub1` VARCHAR( 36 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL; -- 2012-11-03 ALTER TABLE `resource` ADD `area_id` VARCHAR( 10 ) NULL DEFAULT NULL ; ALTER TABLE `sms_salesgroup` CHANGE `salesno` `salesno` VARCHAR( 36 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ; ALTER TABLE `sms_fee` CHANGE `salesno` `salesno` VARCHAR( 36 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ; -- 2012-12-03 ALTER TABLE `sms_transaction` ADD `fee_name` VARCHAR( 100 ) NULL DEFAULT NULL ; ALTER TABLE `sms_transaction` ADD `school_id` VARCHAR( 36 ) NULL DEFAULT NULL ; -- 2012-12-18 ALTER TABLE `sms_recurrence` ADD `actual_number_of_occurrences` INT( 4 ) NULL DEFAULT '0' AFTER `actual_event_end`; -- 2012-12-26 ALTER TABLE `contacts` ADD `nyutai_mail_notice` TINYINT NOT NULL DEFAULT '0' AFTER `card_id` ; -- 2012-12-30 ALTER TABLE `exam` ADD PRIMARY KEY ( `id` ); ALTER TABLE `exam` ADD INDEX ( `event_id` ); ALTER TABLE `exam` ADD INDEX ( `exam_date` ); ALTER TABLE `exam` ADD INDEX ( `name` ( 15 ) ); ALTER TABLE `exam` ADD INDEX ( `kamoku_name` ( 15 ) ); -- 2013-01-25 UPDATE sms_transaction, sms_fee SET sms_transaction.fee_name=sms_fee.name, sms_transaction.school_id=sms_fee.school_id WHERE sms_transaction.fee_id=sms_fee.id AND sms_fee.deleted=0 AND sms_transaction.deleted=0 -- 2013-01-30 - no need if already exists -- insert a new row into config_lock table to store the last combini unique id INSERT INTO config_lock ( `category` , `name` , `value`) VALUES ( 'unique_id', 'combini_sakusei_id', '0' ); -- 2013-02-04 is this one necessary for all schools? -- alter table contacts modify column `email2` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci ; -- 2013-02-19 nyukin_school_id and nyukin_tanmatsu_id -- SELECT * FROM sms_transaction WHERE `t_date` IS NOT NULL AND t_date != '0000-00-00 00:00:00' ; -- UPDATE sms_transaction SET nyukin_school_id='sssssss-1111', nyukin_tanmatsu_id='a09ae8da-32c9-5d91-1620-511fccf50c1f' WHERE `t_date` IS NOT NULL AND t_date != '0000-00-00 00:00:00' ; -- 2013-03-07 ALTER TABLE `resource_sub1` CHANGE `shimebi_c` `shimebi_c` DATE NULL DEFAULT '2001-01-01' ; ALTER TABLE `resource_sub1` CHANGE `last_student_id_c` `last_student_id_c` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0', CHANGE `last_invoice_id_c` `last_invoice_id_c` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0', CHANGE `last_teacher_id_c` `last_teacher_id_c` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '90000' ; -- 2013-04-01 CREATE TABLE IF NOT EXISTS `courselist` ( `id` char(36) NOT NULL, `deleted` tinyint(1) NOT NULL DEFAULT '0', `date_entered` datetime NOT NULL, `date_modified` datetime NOT NULL, `modified_user_id` char(36) NOT NULL, `created_by` char(36) NOT NULL, `school_id` char(36) DEFAULT NULL, `assigned_user_id` char(36) DEFAULT NULL, `name` varchar(50) DEFAULT NULL, `description` text, PRIMARY KEY (`id`), KEY `idx_clist_id_del` (`id`,`deleted`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `courselist_courses` ( `id` varchar(36) NOT NULL DEFAULT '', `courselist_id` varchar(36) DEFAULT NULL, `course_id` varchar(36) DEFAULT NULL, `date_modified` datetime DEFAULT NULL, `deleted` tinyint(1) DEFAULT '0', PRIMARY KEY (`id`), KEY `idx_clist_id` (`courselist_id`), KEY `idx_course_id` (`course_id`,`courselist_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 2013-04-15 CREATE TABLE IF NOT EXISTS `menu_priviledge` ( `id` int(11) NOT NULL AUTO_INCREMENT, `deleted` tinyint(1) NOT NULL DEFAULT '0', `date_entered` datetime NOT NULL, `date_modified` datetime NOT NULL, `modified_user_id` char(36) NOT NULL, `created_by` char(36) NOT NULL, `priviledge_id` char(36) DEFAULT NULL, `menu_item` varchar(40) DEFAULT NULL, `menu_name` varchar(40) DEFAULT NULL, `module_name` varchar(40) DEFAULT NULL, `priviledge` tinyint(1) DEFAULT '1', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; -- 2013-04-18 -- ACC仕様なので未反映。sampledb内にbank_infoはない。 -- ALTER TABLE `bank_info` ADD `deleted` TINYINT NOT NULL DEFAULT '0', ADD `date_modified` DATETIME NOT NULL , ADD `modified_user_id` VARCHAR( 36 ) NOT NULL ; UPDATE `bank_info` SET date_modified='2013-03-20 10:10:00', modified_user_id='1' WHERE 1 ; -- 2013-09-03 bank import related fix --　delete文は削除件数が多いが問題ないとのことです。例：heglは16万件くらい。 delete FROM `bankexport_salesnos` where status <> 0; ALTER TABLE `bankexport_salesnos` ADD PRIMARY KEY ( `hikiotoshi_date` , `salesno` , `contact_id` ) ;