ALTER TABLE `event_contacts` ADD `ec_baitai` VARCHAR( 128 ) NULL;

ALTER TABLE `event_contacts` ADD `keiyaku_keitai` VARCHAR( 64 ) NULL;
ALTER TABLE `event_contacts` ADD `teigaku` VARCHAR( 16 ) NULL;
ALTER TABLE `event_contacts` ADD `kotsuhi` VARCHAR( 16 ) NULL;
ALTER TABLE `event_contacts` ADD `tanka` VARCHAR( 16 ) NULL;
ALTER TABLE `event_contacts` ADD `ritsu` VARCHAR( 16 ) NULL;


ALTER TABLE `smsevent` ADD `term` VARCHAR(8) NULL;
ALTER TABLE `smsevent` ADD `assigned_user_id` VARCHAR(36) NULL;
ALTER TABLE `smsevent` ADD `moushikomi_shimekiri` int(8) NULL;
ALTER TABLE `smsevent` ADD `tochuuketsuke` VARCHAR(8) NULL;
ALTER TABLE `smsevent` ADD `kengaku` VARCHAR(8) NULL;
ALTER TABLE `smsevent` ADD `trial` VARCHAR(8) NULL;
ALTER TABLE `smsevent` ADD `category1` VARCHAR(64) NULL;
ALTER TABLE `smsevent` ADD `category2` VARCHAR(64) NULL;
ALTER TABLE `smsevent` ADD `name2` VARCHAR(128) NULL;
ALTER TABLE `smsevent` ADD `name_furigana` VARCHAR(128) NULL;
ALTER TABLE `smsevent` ADD `web_name` VARCHAR(128) NULL;
ALTER TABLE `smsevent` ADD `web_show` tinyint(1) NULL;
ALTER TABLE `smsevent` ADD `web_uketsuke` tinyint(1) NULL;
ALTER TABLE `smsevent` ADD `uketsuke_start` DATE NULL;
ALTER TABLE `smsevent` ADD `uketsuke_end` DATE NULL;
ALTER TABLE `smsevent` ADD `web_start` DATE NULL;
ALTER TABLE `smsevent` ADD `web_end` DATE NULL;
ALTER TABLE `smsevent` ADD `web_picture` VARCHAR(128) NULL;
ALTER TABLE `smsevent` ADD `web_description` VARCHAR(128) NULL;
ALTER TABLE `smsevent` ADD `capacity_max` int(8) NULL;
ALTER TABLE `smsevent` ADD `capacity_min` int(8) NULL;
ALTER TABLE `smsevent` ADD `capacity_kanri` VARCHAR(8) NULL;
ALTER TABLE `smsevent` ADD `annai_text` VARCHAR(256) NULL;
ALTER TABLE `smsevent` ADD `annai_shokai` VARCHAR(256) NULL;
ALTER TABLE `smsevent` ADD `annai_salespoint` VARCHAR(256) NULL;
ALTER TABLE `smsevent` ADD `chirashi_pamp` tinyint(1) NULL;
ALTER TABLE `smsevent` ADD `chirashi_leaflet` tinyint(1) NULL;
ALTER TABLE `smsevent` ADD `chirashi_nittei` tinyint(1) NULL;
ALTER TABLE `smsevent` ADD `chirashi_category1` VARCHAR(64) NULL;
ALTER TABLE `smsevent` ADD `chirashi_category2` VARCHAR(64) NULL;
ALTER TABLE `smsevent` ADD `chirashi_category3` VARCHAR(64) NULL;

UPDATE `resource` SET `area_id` = '北海道' WHERE `resource`.`id` = '28e74451-24cc-c806-9642-5001d0f926d9' LIMIT 1 ;
UPDATE `contacts` SET `deleted` = '1' WHERE CONVERT( `contacts`.`id` USING utf8 ) = '4c381c20-9d3e-0530-5833-506901db1322' LIMIT 1 ;
UPDATE `resource` SET `deleted` = '1' WHERE `resource`.`id` = '4dac1600-cfec-aae9-e2ee-506901c68069' LIMIT 1 ;
UPDATE `resource` SET `area_id` = 'test' WHERE `resource`.`id` = '687ec71c-1c7d-6c97-fc2a-4c889fc5d652' LIMIT 1 ;

UPDATE `resource` SET `school_id` = '99' WHERE `resource`.`id` = '687ec71c-1c7d-6c97-fc2a-4c889fc5d652' LIMIT 1; 
UPDATE `resource` SET `school_id` = '03' WHERE `resource`.`id` = '49caa19d-9872-3095-6bd4-5001d0aa692d' LIMIT 1; 
UPDATE `resource` SET `school_id` = '05' WHERE `resource`.`id` = 'a32c39d6-45da-a750-6e4d-5001d0bd7a8d' LIMIT 1; 
UPDATE `resource` SET `school_id` = '06' WHERE `resource`.`id` = 'b0845182-f1bc-145f-0560-5001d0cbee2a' LIMIT 1; 
UPDATE `resource` SET `school_id` = '17' WHERE `resource`.`id` = '28e74451-24cc-c806-9642-5001d0f926d9' LIMIT 1; 
UPDATE `resource` SET `school_id` = '20' WHERE `resource`.`id` = 'c083c4e8-6f1d-24ea-fd47-5001d0b9910f' LIMIT 1; 
UPDATE `resource` SET `school_id` = '29' WHERE `resource`.`id` = 'bb0d4d73-13a4-b000-36a6-5001d0094cb6' LIMIT 1; 
UPDATE `resource` SET `school_id` = '24' WHERE `resource`.`id` = '5bc40075-3911-d52a-a0f8-5001d15a06e5' LIMIT 1;
UPDATE `resource` SET `school_id` = '22' WHERE `resource`.`id` = '5ea7a466-203e-45f4-582c-5001d12b64db' LIMIT 1; 
UPDATE `resource` SET `school_id` = '28' WHERE `resource`.`id` = 'c387f0ac-5700-6490-3264-5001d1127ee7' LIMIT 1; 
UPDATE `resource` SET `school_id` = '37' WHERE `resource`.`id` = 'a312c57a-0e45-6f65-0402-5001d1e84f1d' LIMIT 1; 
UPDATE `resource` SET `school_id` = '36' WHERE `resource`.`id` = '1ca1eb40-372a-27a2-bf44-5001d1ad9d91' LIMIT 1; 
UPDATE `resource` SET `school_id` = '45' WHERE `resource`.`id` = 'e86510a2-302b-7c34-250d-5001d1fc1b41' LIMIT 1; 
UPDATE `resource` SET `school_id` = '98' WHERE `resource`.`id` = 'bf737929-0509-7bfe-586f-500248ae9b81' LIMIT 1; 


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`, `activation_key`, `area_id`) VALUES
('sssssss-1111', '01', '2006-01-20 19:07:48', '2012-07-15 13:34:44', '1', '1', '新宿', NULL, NULL, 'school', NULL, 0, NULL, NULL, NULL, '', '首都圏'),
('687ec71c-1c7d-6c97-fc2a-4c889fc5d652', '99', '2010-09-09 17:50:04', '2010-09-09 17:50:04', '1', '1', 'test campus', NULL, NULL, 'school', NULL, 0, NULL, NULL, NULL, '', 'test'),
('49caa19d-9872-3095-6bd4-5001d0aa692d', '03', '2012-07-15 05:03:38', '2012-07-15 05:03:38', '1', '1', '横浜', NULL, NULL, 'school', NULL, 0, NULL, NULL, NULL, '', '首都圏'),
('a32c39d6-45da-a750-6e4d-5001d0bd7a8d', '05', '2012-07-15 05:03:49', '2012-10-26 14:47:33', 'a437cc11-564c-e5c4-3ff0-4c88c6961e3b', '1', '立川', NULL, NULL, 'school', NULL, 0, NULL, NULL, NULL, '', '首都圏'),
('b0845182-f1bc-145f-0560-5001d0cbee2a', '06', '2012-07-15 05:04:02', '2012-10-26 14:47:50', 'a437cc11-564c-e5c4-3ff0-4c88c6961e3b', '1', '湘南', NULL, NULL, 'school', NULL, 0, NULL, NULL, NULL, '', '首都圏'),
('28e74451-24cc-c806-9642-5001d0f926d9', '17', '2012-07-15 05:04:19', '2012-10-26 14:48:28', 'a437cc11-564c-e5c4-3ff0-4c88c6961e3b', '1', '札幌', NULL, NULL, 'school', NULL, 0, NULL, NULL, NULL, '', '北海道'),
('c083c4e8-6f1d-24ea-fd47-5001d0b9910f', '20', '2012-07-15 05:04:50', '2012-10-26 14:50:24', 'a437cc11-564c-e5c4-3ff0-4c88c6961e3b', '1', '中之島', NULL, NULL, 'school', NULL, 0, NULL, NULL, NULL, '', '大阪'),
('bb0d4d73-13a4-b000-36a6-5001d0094cb6', '29', '2012-07-15 05:05:09', '2012-10-26 14:50:33', 'a437cc11-564c-e5c4-3ff0-4c88c6961e3b', '1', '梅田', NULL, NULL, 'school', NULL, 0, NULL, NULL, NULL, '', '大阪'),
('5bc40075-3911-d52a-a0f8-5001d15a06e5', '24', '2012-07-15 05:05:20', '2012-10-26 14:53:32', 'a437cc11-564c-e5c4-3ff0-4c88c6961e3b', '1', '芦屋', NULL, NULL, 'school', NULL, 0, NULL, NULL, NULL, '', '大阪'),
('5ea7a466-203e-45f4-582c-5001d12b64db', '22', '2012-07-15 05:05:32', '2012-10-26 14:52:12', 'a437cc11-564c-e5c4-3ff0-4c88c6961e3b', '1', '川西', NULL, NULL, 'school', NULL, 0, NULL, NULL, NULL, '', '大阪'),
('c387f0ac-5700-6490-3264-5001d1127ee7', '28', '2012-07-15 05:05:47', '2012-10-26 14:53:27', 'a437cc11-564c-e5c4-3ff0-4c88c6961e3b', '1', '京都', NULL, NULL, 'school', NULL, 0, NULL, NULL, NULL, '', '大阪'),
('a312c57a-0e45-6f65-0402-5001d1e84f1d', '37', '2012-07-15 05:05:57', '2012-10-26 14:54:19', 'a437cc11-564c-e5c4-3ff0-4c88c6961e3b', '1', '福岡', NULL, NULL, 'school', NULL, 0, NULL, NULL, NULL, '', '九州'),
('1ca1eb40-372a-27a2-bf44-5001d1ad9d91', '36', '2012-07-15 05:06:08', '2012-07-15 05:06:08', '1', '1', '北九州', NULL, NULL, 'school', NULL, 0, NULL, NULL, NULL, '', '九州'),
('e86510a2-302b-7c34-250d-5001d1fc1b41', '45', '2012-07-15 05:06:16', '2012-10-26 14:54:04', 'a437cc11-564c-e5c4-3ff0-4c88c6961e3b', '1', '名古屋', NULL, NULL, 'school', NULL, 0, NULL, NULL, NULL, '', '名古屋'),
('bf737929-0509-7bfe-586f-500248ae9b81', '98', '2012-07-15 13:34:22', '2012-10-10 10:55:44', 'a437cc11-564c-e5c4-3ff0-4c88c6961e3b', '1', '本社', NULL, NULL, 'school', NULL, 0, NULL, NULL, NULL, '', '首都圏'),
('4dac1600-cfec-aae9-e2ee-506901c68069', '15', '2012-10-01 11:37:20', '2012-10-01 11:37:20', '1', '1', 'マスタ管理', NULL, NULL, 'school', NULL, 1, NULL, NULL, NULL, '', '首都圏');


-- 2012-12-06 
ALTER TABLE `event_contacts` CHANGE `ec_yoyaku_school_name` `ec_yoyaku_school_id` VARCHAR( 36 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ; 


-- 2012-12-07
alter table event_contacts add column ec_payment_type varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci ;
alter table event_contacts add column ec_student_status varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci ;
alter table event_contacts add column internal_yoyaku_id char(36) CHARACTER SET utf8 COLLATE utf8_general_ci ;

-- 2012-12-08
-- ALTER TABLE `master_kanri` ADD `field6` VARCHAR( 40 ) NULL DEFAULT NULL ;

-- 2012-12-10
ALTER TABLE `contacts` CHANGE `assigned_user_id` `assigned_user_id` CHAR( 36 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ;

-- 2012-12-11
ALTER TABLE `master_kanri` CHANGE `show_edit` `inactive` TINYINT( 1 ) NULL DEFAULT '1';
UPDATE `master_kanri` SET inactive=0 WHERE 1;

-- 2012-12-18
ALTER TABLE `smsevent_sub3` ENGINE = InnoDB ROW_FORMAT = FIXED ;


-- 2012-12-20


DROP TABLE IF EXISTS `yoyaku_contacts`;
CREATE TABLE IF NOT EXISTS `yoyaku_contacts` (
  `id` varchar(36) NOT NULL DEFAULT '',
  `internal_yoyaku_id` varchar(36) DEFAULT NULL,
  `contact_id` varchar(36) DEFAULT NULL,
  `status` tinyint(4) NOT NULL DEFAULT '0',
  `date_entered` datetime NOT NULL,
  `date_modified` datetime NOT NULL,
  `deleted` tinyint(4) NOT NULL,
  `modified_user_id` varchar(36) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `internal_yoyaku_id` (`internal_yoyaku_id`),
  KEY `contact_id` (`contact_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `yoyaku_basic`;
CREATE TABLE IF NOT EXISTS `yoyaku_basic` (
  `id` varchar(36) NOT NULL,
  `internal_yoyaku_id` varchar(36) NOT NULL DEFAULT '',
  `active_student_id` varchar(36) DEFAULT NULL,
  `active_event_contacts_id` varchar(36) DEFAULT NULL,
  `active_daihyo_id` varchar(36) DEFAULT NULL,
  `uketsuke_bango` varchar(12) DEFAULT NULL,
  `status` tinyint(4) NOT NULL DEFAULT '0',
  `date_entered` datetime NOT NULL,
  `date_modified` datetime NOT NULL,
  `deleted` tinyint(4) NOT NULL,
  `modified_user_id` varchar(36) NOT NULL,
  `web_order_flg` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `internal_yoyaku_id` (`internal_yoyaku_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 2012-01-08 
UPDATE `master_kanri` SET `deleted` = '1' WHERE `master_kanri`.`id` =554 LIMIT 1 ;

-- 2012-01-09
ALTER TABLE `yoyaku_basic` ADD `uketsuke_bango` VARCHAR( 12 ) NULL AFTER `active_daihyo_id` ;
ALTER TABLE `yoyaku_basic` CHANGE `active_daihyo_id` `daihyo_id` VARCHAR( 36 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ;

-- 2013-02-07
CREATE TABLE IF NOT EXISTS `accounts_audit` (
  `id` varchar(36) NOT NULL default '',
  `parent_id` varchar(36) NOT NULL default '',
  `date_created` datetime default NULL,
  `created_by` varchar(36) default NULL,
  `field_name` varchar(100) default NULL,
  `data_type` varchar(100) default NULL,
  `before_value_string` varchar(255) default NULL,
  `after_value_string` varchar(255) default NULL,
  `before_value_text` text,
  `after_value_text` text
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 2013-02-16  mypage related
-- INSERT INTO `users` (`id`, `user_name`, `user_password`, `user_hash`, `is_admin`, `receive_notifications`, `description`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `status`, `user_preferences`, `deleted`, `portal_only`, `is_group`, `contact_id`, `smsgroup`, `kantan_login_id`) VALUES ('c4565fc9-30f9-719c-cb31-5537354e807d', '0100002', 'deU4p6Xc50VMk', '1', 'off', '0', NULL, '2013-02-14 10:48:07', '2013-02-14 19:07:31', '1', '1', 'Active', '', 0, 0, 0, 'd4e703c5-0ff5-7d76-7c14-5003ab88522f', 'student', NULL);

-- 2013-02-17 tanmatsu related
-- 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`, `activation_key`, `area_id`) VALUES ('86024d6c-2c9f-22e5-a780-511fcc643a10', 'sssssss-1111', '2013-02-17 03:14:07', '2013-02-17 03:14:07', '1', '1', '新宿3階2', NULL, NULL, 'tanmatsu', NULL, 0, NULL, 'YTowOnt9', NULL, '', NULL), ('a09ae8da-32c9-5d91-1620-511fccf50c1f', 'sssssss-1111', '2013-02-17 03:13:46', '2013-02-17 03:14:20', '1', '1', '新宿3階1', NULL, NULL, 'tanmatsu', NULL, 0, NULL, 'YTowOnt9', NULL, '', NULL), ('bd90ff50-9738-8d04-1214-5120685754d3', '49caa19d-9872-3095-6bd4-5001d0aa692d', '2013-02-17 14:18:44', '2013-02-17 14:18:44', '1', '1', '横浜1', NULL, NULL, 'tanmatsu', NULL, 0, NULL, 'YTowOnt9', NULL, '', NULL);

-- 2013-02-21 updating nyukin_tanmatsu_id and nyukin_school_id
-- UPDATE sms_transaction SET nyukin_school_id='sssssss-1111', nyukin_tanmatsu_id='a09ae8da-32c9-5d91-1620-511fccf50c1f' WHERE nyukin_tanmatsu_id IS NULL AND (t_date IS NULL OR t_date='0000-00-00 00:00:00') ;


-- 2013-04-01 kigyo master data
INSERT INTO `master_kanri` (`id`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `inactive`, `display_order`, `master_name`, `master_group`, `school_id`, `master_category1`, `master_category2`, `field1`, `field2`, `field3`, `field4`, `field5`, `field6`) VALUES
(2900, 0, '2013-01-04 23:15:50', '2013-04-01 02:49:11', '1', '1', 0, NULL, '企業マスタ', 'kigyo', NULL, NULL, NULL, '5', NULL, NULL, '30000', NULL, NULL);


-- 2013-04-22
ALTER TABLE `yoyaku_transactions` ADD `combini_sakusei_id` INT NOT NULL DEFAULT '0';
ALTER TABLE `yoyaku_transactions` CHANGE `combini_sakusei_id` `combini_sakusei_id` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0';
ALTER TABLE `yoyaku_transactions` ADD INDEX ( `combini_sakusei_id` );

-- 20130-04-16
 SELECT * FROM `acl_roles` WHERE id IN ('538c8945-3474-cac4-494e-516c949cb961', '3d8b57cf-fc86-e2f9-f9c1-516c97e5617f', '9ba344d4-24aa-fdde-3fba-516c978c4b63')
 SELECT * FROM `acl_roles_actions` WHERE role_id IN ('538c8945-3474-cac4-494e-516c949cb961', '3d8b57cf-fc86-e2f9-f9c1-516c97e5617f', '9ba344d4-24aa-fdde-3fba-516c978c4b63')
 
INSERT INTO `menu_priviledge` (`id`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `priviledge_id`, `menu_item`, `menu_name`, `module_name`, `priviledge`) VALUES
(1, 0, '2013-04-15 09:00:00', '2013-04-15 09:00:00', '1', '1', '538c8945-3474-cac4-494e-516c949cb961', 'LBL_KOUZA_MENU_C', NULL, 'Course', 0),
(2, 0, '2013-04-15 09:00:00', '2013-04-15 09:00:00', '1', '1', '538c8945-3474-cac4-494e-516c949cb961', 'LNK_RENRAKUHYOU', NULL, 'Course', 0),
(3, 0, '2013-04-15 09:00:00', '2013-04-15 09:00:00', '1', '1', '538c8945-3474-cac4-494e-516c949cb961', 'LNK_KAISAIBI_CHUSHI', NULL, 'Course', 0),
(4, 0, '2013-04-15 09:00:00', '2013-04-15 09:00:00', '1', '1', '538c8945-3474-cac4-494e-516c949cb961', 'LNK_ISHOKUSHO', NULL, 'Course', 0),
(5, 0, '2013-04-15 09:00:00', '2013-04-15 09:00:00', '1', '1', '538c8945-3474-cac4-494e-516c949cb961', 'LNK_SHUSHI_MIKOMI', NULL, 'Course', 0),
(6, 0, '2013-04-15 09:00:00', '2013-04-15 09:00:00', '1', '1', '538c8945-3474-cac4-494e-516c949cb961', 'LNK_SHUSSEKIBO', NULL, 'Course', 0),
(7, 0, '2013-04-15 09:00:00', '2013-04-15 09:00:00', '1', '1', '538c8945-3474-cac4-494e-516c949cb961', 'LNK_MYPAGE_VIEW', NULL, 'Course', 0),
(8, 0, '2013-04-15 09:00:00', '2013-04-15 09:00:00', '1', '1', '538c8945-3474-cac4-494e-516c949cb961', 'LNK_COURSE_EXPORT_SETTING', NULL, 'Course', 0),
(9, 0, '2013-04-15 09:00:00', '2013-04-15 09:00:00', '1', '1', '538c8945-3474-cac4-494e-516c949cb961', 'LBL_COURSELIST_MENU', NULL, 'Course', 0),
(10, 0, '2013-04-15 09:00:00', '2013-04-15 09:00:00', '1', '1', '538c8945-3474-cac4-494e-516c949cb961', 'LNK_COURSELIST_LIST', NULL, 'Course', 0),
(11, 0, '2013-04-15 09:00:00', '2013-04-15 09:00:00', '1', '1', '538c8945-3474-cac4-494e-516c949cb961', 'LBL_KOUZA_MENU_C', NULL, 'Course', 0),
(12, 0, '2013-04-15 09:00:00', '2013-04-15 09:00:00', '1', '1', '538c8945-3474-cac4-494e-516c949cb961', 'LNK_COURSELIST_ADD', NULL, 'Course', 0),

(13, 0, '2013-04-15 09:00:00', '2013-04-15 09:00:00', '1', '1', '3d8b57cf-fc86-e2f9-f9c1-516c97e5617f', 'LBL_UKETSUKE_MENU_S', NULL, 'Course', 0),
(14, 0, '2013-04-15 09:00:00', '2013-04-15 09:00:00', '1', '1', '3d8b57cf-fc86-e2f9-f9c1-516c97e5617f', 'LBL_UKETSUKE_MENU_C', NULL, 'Course', 0),
(15, 0, '2013-04-15 09:00:00', '2013-04-15 09:00:00', '1', '1', '3d8b57cf-fc86-e2f9-f9c1-516c97e5617f', 'LNK_GOUKEI_DATA', NULL, 'Course', 0),
(16, 0, '2013-04-15 09:00:00', '2013-04-15 09:00:00', '1', '1', '3d8b57cf-fc86-e2f9-f9c1-516c97e5617f', 'LNK_GOUKEI_DATA_LIST', NULL, 'Course', 0),
(17, 0, '2013-04-15 09:00:00', '2013-04-15 09:00:00', '1', '1', '3d8b57cf-fc86-e2f9-f9c1-516c97e5617f', 'LNK_JUKO_TOUROKU', NULL, 'Student', 0),
(18, 0, '2013-04-15 09:00:00', '2013-04-15 09:00:00', '1', '1', '3d8b57cf-fc86-e2f9-f9c1-516c97e5617f', 'LNK_HENKIN_SHORI', NULL, 'Student', 0),
(19, 0, '2013-04-15 09:00:00', '2013-04-15 09:00:00', '1', '1', '3d8b57cf-fc86-e2f9-f9c1-516c97e5617f', 'LNK_NYUKAIKIN_HENKO', NULL, 'Student', 0),
(20, 0, '2013-04-15 09:00:00', '2013-04-15 09:00:00', '1', '1', '3d8b57cf-fc86-e2f9-f9c1-516c97e5617f', 'LNK_JUKO_CHUSHI', NULL, 'Student', 0),
(21, 0, '2013-04-15 09:00:00', '2013-04-15 09:00:00', '1', '1', '3d8b57cf-fc86-e2f9-f9c1-516c97e5617f', 'LNK_REIGAI_SHORI', NULL, 'Student', 0),
(22, 0, '2013-04-15 09:00:00', '2013-04-15 09:00:00', '1', '1', '3d8b57cf-fc86-e2f9-f9c1-516c97e5617f', 'LNK_REIGAI_SHORI_LIST', NULL, 'Student', 0);

-- 20130-04-30
ALTER TABLE `smsevent_sub3` CHANGE `shinki_toruku_no` `shinki_toroku_no` INT( 11 ) NULL DEFAULT '0',
CHANGE `keizoku_toruku_no` `keizoku_toroku_no` INT( 11 ) NULL DEFAULT '0',
CHANGE `dansei_toruku_no` `dansei_toroku_no` INT( 11 ) NULL DEFAULT '0',
CHANGE `josei_toruku_no` `josei_toroku_no` INT( 11 ) NULL DEFAULT '0',
CHANGE `hatsu_toruku_no` `hatsu_toroku_no` INT( 11 ) NULL DEFAULT '0',
CHANGE `sai_toruku_no` `sai_toroku_no` INT( 11 ) NULL DEFAULT '0'

SELECT contacts.description, contacts_sub1.description, smsgroup, deleted, last_name, contact_status FROM contacts, contacts_sub1 WHERE contacts.id=contacts_sub1.id_sub1 AND 
	contacts.description IS NOT NULL AND contacts_sub1.description IS NOT NULL
	
	
SELECT contacts.description, contacts_sub1.description, smsgroup, deleted, last_name, contact_status FROM contacts, contacts_sub1 WHERE contacts.id=contacts_sub1.id_sub1 AND 
	 contacts_sub1.description IS NOT NULL
	 
UPDATE contacts, contacts_sub1 SET contacts.description=contacts_sub1.description  WHERE contacts.id=contacts_sub1.id_sub1 AND contacts_sub1.description IS NOT NULL

-- 20130-05-08
ALTER TABLE `resource` ADD `tanmatsu_shimebi` DATE NULL ;


-- 20130-05-14  

CREATE TABLE IF NOT EXISTS `maeukekin` (
  `student_id` char(36) DEFAULT NULL,
  `course_id` char(36) DEFAULT NULL,
  `school_id` char(36) DEFAULT NULL,
  `bumon_code` varchar(2) DEFAULT NULL,
  `nyukinbi` date DEFAULT NULL,
  `keijobi` date DEFAULT NULL,
  `fee_subcategory` varchar(1) DEFAULT NULL,
  `kingaku` int(11) DEFAULT NULL,
  `fee_id` char(36) DEFAULT NULL,
  `kaisu` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `sharei_hibetsu` (
  `teacher_id` char(36) DEFAULT NULL,
  `t_idnumber` varchar(10) DEFAULT NULL,
  `teacher_name` varchar(40) DEFAULT NULL,
  `t_bank_payment_type` tinyint(4) DEFAULT NULL,
  `zei_kubun` varchar(10) DEFAULT NULL,
  `h_idnumber` varchar(10) DEFAULT NULL,
  `course_id` char(36) DEFAULT NULL,
  `common_code` char(36) DEFAULT NULL,
  `bumon_code` varchar(2) DEFAULT NULL,
  `school_id` char(36) DEFAULT NULL,
  `course_name` varchar(50) DEFAULT NULL,
  `course_code` varchar(2) DEFAULT NULL,
  `nenki` int(11) DEFAULT NULL,
  `assigned_user_id` char(36) DEFAULT NULL,
  `from_datetime` datetime DEFAULT NULL,
  `ct_keiyaku_keitai` tinyint(4) DEFAULT NULL,
  `sharei_komoku` int(11) DEFAULT NULL,
  `ct_ritsu` int(11) DEFAULT NULL,
  `ct_tanka` int(11) DEFAULT NULL,
  `ninzu` int(11) DEFAULT NULL,
  `zeigaku` int(11) DEFAULT NULL,
  `houshugaku` int(11) DEFAULT NULL,
  `sharei_biko` char(36) DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  `kakutei_flg` tinyint(1) DEFAULT '1',
  `tsuikakbn` tinyint(1) DEFAULT '0',
  `modified_user_id` char(36) DEFAULT NULL,
  `sharei_kubun` tinyint(1) DEFAULT NULL,
  `kaisu` int(11) DEFAULT NULL,
  `furikomibi` date DEFAULT NULL,
  `t_bank_code` varchar(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `sharei_hibetsu` ADD `kaisu` INT NULL DEFAULT NULL ,
ADD `furikomibi` DATE NULL DEFAULT NULL ,
ADD `t_bank_code` VARCHAR( 4 ) NULL DEFAULT NULL;



CREATE TABLE IF NOT EXISTS `sharei_kouza` (
  `teacher_id` char(36) DEFAULT NULL,
  `t_idnumber` varchar(10) DEFAULT NULL,
  `teacher_name` varchar(40) DEFAULT NULL,
  `t_bank_payment_type` tinyint(4) DEFAULT NULL,
  `zei_kubun` varchar(10) DEFAULT NULL,
  `h_idnumber` varchar(10) DEFAULT NULL,
  `course_id` char(36) DEFAULT NULL,
  `common_code` char(36) DEFAULT NULL,
  `bumon_code` varchar(2) DEFAULT NULL,
  `school_id` char(36) DEFAULT NULL,
  `course_name` varchar(50) DEFAULT NULL,
  `course_code` varchar(2) DEFAULT NULL,
  `nenki` int(11) DEFAULT NULL,
  `assigned_user_id` char(36) DEFAULT NULL,
  `from_datetime` datetime DEFAULT NULL,
  `ct_keiyaku_keitai` tinyint(4) DEFAULT NULL,
  `sharei_komoku` int(11) DEFAULT NULL,
  `ct_ritsu` int(11) DEFAULT NULL,
  `ct_tanka` int(11) DEFAULT NULL,
  `ninzu` int(11) DEFAULT NULL,
  `zeigaku` int(11) DEFAULT NULL,
  `houshugaku` int(11) DEFAULT NULL,
  `sharei_biko` char(36) DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  `kakutei_flg` tinyint(1) DEFAULT '1',
  `tsuikakbn` tinyint(1) DEFAULT '0',
  `modified_user_id` char(36) DEFAULT NULL,
  `sharei_kubun` tinyint(1) DEFAULT NULL,
  `kaisu` int(11) DEFAULT NULL,
  `furikomibi` date DEFAULT NULL,
  `t_bank_code` varchar(4) DEFAULT NULL
  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `sharei_koushi` (
   `teacher_id` char(36) DEFAULT NULL,
  `t_idnumber` varchar(10) DEFAULT NULL,
  `teacher_name` varchar(40) DEFAULT NULL,
  `t_bank_payment_type` tinyint(4) DEFAULT NULL,
  `zei_kubun` varchar(10) DEFAULT NULL,
  `h_idnumber` varchar(10) DEFAULT NULL,
  `course_id` char(36) DEFAULT NULL,
  `common_code` char(36) DEFAULT NULL,
  `bumon_code` varchar(2) DEFAULT NULL,
  `school_id` char(36) DEFAULT NULL,
  `course_name` varchar(50) DEFAULT NULL,
  `course_code` varchar(2) DEFAULT NULL,
  `nenki` int(11) DEFAULT NULL,
  `assigned_user_id` char(36) DEFAULT NULL,
  `from_datetime` datetime DEFAULT NULL,
  `ct_keiyaku_keitai` tinyint(4) DEFAULT NULL,
  `sharei_komoku` int(11) DEFAULT NULL,
  `ct_ritsu` int(11) DEFAULT NULL,
  `ct_tanka` int(11) DEFAULT NULL,
  `ninzu` int(11) DEFAULT NULL,
  `zeigaku` int(11) DEFAULT NULL,
  `houshugaku` int(11) DEFAULT NULL,
  `sharei_biko` char(36) DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  `kakutei_flg` tinyint(1) DEFAULT '1',
  `tsuikakbn` tinyint(1) DEFAULT '0',
  `modified_user_id` char(36) DEFAULT NULL,
  `sharei_kubun` tinyint(1) DEFAULT NULL,
  `kaisu` int(11) DEFAULT NULL,
  `furikomibi` date DEFAULT NULL,
  `t_bank_code` varchar(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE IF NOT EXISTS `master_relation` (
  `master_id` int(11) NOT NULL,
  `related_id` int(11) NOT NULL,
  `date_modified` datetime NOT NULL,
  `modified_user_id` char(36) NOT NULL,
  `relation_type` varchar(20) DEFAULT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


-- 20130-05-22
ALTER TABLE `resource` ADD `shareilock_date` DATE NULL ;
ALTER TABLE `resource` ADD `sharei_keisan_datetime` DATETIME NULL ;

INSERT INTO `master_kanri` (`id`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `inactive`, `display_order`, `master_name`, `master_group`, `school_id`, `master_category1`, `master_category2`, `field1`, `field2`, `field3`, `field4`, `field5`, `field6`) VALUES
(2950, 0, '2013-05-20 10:00:00', '2013-05-20 10:00:00', '1', '1', 0, NULL, '講師謝礼ロック解除制限', 'sharei_lock', NULL, NULL, NULL, '2013-04-10', NULL, NULL, NULL, NULL, NULL);


-- 20130-05-26
ALTER TABLE `maeukekin` ADD `nenki` INT NOT NULL AFTER `bumon_code` ;
ALTER TABLE `maeukekin` ADD `common_code` INT NOT NULL AFTER `nenki` ;
ALTER TABLE `maeukekin` CHANGE `common_code` `common_code` VARCHAR( 36 ) NULL ;

-- 2013-06-04
ALTER TABLE `yoyaku_contacts` ADD `valid_date` DATE NULL ,
ADD `contact_status` INT NULL ;
ALTER TABLE `yoyaku_contacts` ADD `yutai_kubun` VARCHAR( 30 ) NULL;
ALTER TABLE `yoyaku_contacts` ADD `account_id` VARCHAR( 36 ) NULL ;

-- 2013-06-05
INSERT INTO `acc_test`.`config` (`category`, `name`, `value`) VALUES ('teacher_idnumber', 'kojin', '0');
INSERT INTO `acc_test`.`config` (`category`, `name`, `value`) VALUES ('teacher_idnumber', 'model', '0');
INSERT INTO `acc_test`.`config` (`category`, `name`, `value`) VALUES ('teacher_idnumber', 'hojin', '0');
INSERT INTO `acc_test`.`config` (`category`, `name`, `value`) VALUES ('accounts_idnumber', 'account', '0');

--2013-07-16
--マイページログイン画面のパスワードリマインダーの自動返信メール
--$contact_nameが予約語のため、$send_contact_nameに変更
UPDATE `email_templates` SET `body` = '$send_contact_name　 様

新しいパスワードを発行しましたので、お送り致します。


●ユーザ名： $user_name
●新パスワード(半角英数）： $user_password






', `body_html` = '<font face="MS UI Gothic">$send_contact_name　 様<br />
<br />
新しいパスワードを発行しましたので、お送り致します。<br />
<br />
<br />
●ユーザ名： $user_name<br />
●新パスワード(半角英数）： $user_password<br />
<br />
<br />
<br />
<br />
<br />
<br />
</font>' WHERE `email_templates`.`id` = 'dea70547-7e10-5c20-0dc6-5120e5082e5a' ;


--2013-07-19 Null制約解除
--EventInstanceDetailのsave_ANでnullによるエラーが発生するため
ALTER TABLE `smsevent_instances` MODIFY COLUMN `attendance_memo` VARCHAR( 255 );

--2013-08-07 teacher_hoshu_tsuikaに作成者の項目を追加
https://acc.e-school.jp/test/index.php?module=Administration&action=MG_TblRepair_AN&module_to_manage=HoshuTsuika&manage_type=repairtbl&table_to_manage=teacher_hoshu_tsuika&echo_only=true

--2013-09-05 bank_info 銀行情報の仕様変更によるマスタ入替が必要
--既存のBankinfoのリネーム
ALTER TABLE bank_info RENAME TO bank_info_old;
--手順にしたがって下記をインポートする
--\\fuji\public\kaihatsu\銀行DB\新bankinfo作成

--2013-09-09 未使用項目削除（移行データ検証でnull制約エラーが発生したため）
--実施済　acc2.e-school.jp/import/、acc.e-school.jp/test/
ALTER TABLE `smsevent_sub1` DROP `sakusei_jokyo_test`;

--2013-09-12 Resourceに項目を追加 ※test実施済
https://acc.e-school.jp/test/index.php?module=Administration&action=MG_TblRepair_AN&module_to_manage=Resource&manage_type=repairtbl&table_to_manage=resource&echo_only=true

--2013-09-12 Muhammad Naeem 新規Moduleを作成された為、新規テーブル作成・データ入力

CREATE TABLE IF NOT EXISTS `teacher_meisai_message` (
  
`id` char(36) NOT NULL,
  
`date_entered` datetime NOT NULL,
 
`date_modified` datetime NOT NULL,
  
`annai_bun1` varchar(52) DEFAULT NULL,
  
`annai_bun2` varchar(64) DEFAULT NULL,
  
`annai_bun3` varchar(64) DEFAULT NULL,
  
`annai_bun4` varchar(64) DEFAULT NULL,
  
`annai_bun5` varchar(86) DEFAULT NULL,
  
`annai_bun6` varchar(52) DEFAULT NULL,
  
`annai_bun7` varchar(52) DEFAULT NULL,
  
`annai_bun8` varchar(52) DEFAULT NULL
) 
ENGINE=InnoDB DEFAULT CHARSET=utf8;



INSERT INTO `teacher_meisai_message` (`id`)
VALUES
('accd97c5-9707-d199-1f5e-5229871c7b59');

--2013-09-12
--講師銀行振込データで数分かかるため。sharei_koushiのfurikomibiを更新。
CREATE INDEX tearcher_fromdate ON sharei_koushi (teacher_id, from_datetime);

--2013-09-17
--acc_sample, acc_importに実施済
--test環境へのCREATE FULLTEXT INDEXは見送すこと。mroongaがないので。
//contacts
drop index idx_cont_last_first ON contacts;
drop index idx_contacts_del_last ON contacts;
drop index idx_cont_del_reports ON contacts;
drop index smsgroup ON contacts;
drop index phone_home ON contacts;
drop index phone_mobile ON contacts;
drop index phone_other ON contacts;
CREATE INDEX `last_name_furigana` ON contacts (`last_name_furigana`);
CREATE INDEX `phone_home_cleaned` ON contacts (`phone_home_cleaned`);
CREATE INDEX `phone_mobile_cleaned` ON contacts (`phone_mobile_cleaned`);
CREATE INDEX `phone_work_cleaned` ON contacts (`phone_work_cleaned`);
CREATE INDEX `phone_other_cleaned` ON contacts (`phone_other_cleaned`);

//smsevent
alter table smsevent ENGINE = mroonga COMMENT = 'engine "innodb"'
CREATE INDEX `common_code` ON smsevent (`common_code`);
CREATE FULLTEXT INDEX `idx_name` on smsevent (`name`) COMMENT 'parser "TokenMecab"';
CREATE FULLTEXT INDEX `idx_name_furigana` on smsevent (`name_furigana`) COMMENT 'parser "TokenMecab"';

//smsevent_sub1
CREATE INDEX `nenki` ON smsevent_sub1 (`nenki`);