
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;


-- 2012-12-20


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;


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;




--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
CREATE INDEX `common_code` ON smsevent (`common_code`);

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

//--mroonga設定用
//smsevent
//alter table smsevent ENGINE = mroonga COMMENT = 'engine "innodb"';
//CREATE FULLTEXT INDEX `idx_name` on smsevent (`name`) COMMENT 'parser "TokenMecab"';
//CREATE FULLTEXT INDEX `idx_name_furigana` on smsevent (`name_furigana`) COMMENT 'parser "TokenMecab"';
--mroonga廃止の切戻し用
//smsevent
alter table smsevent ENGINE = InnoDB;
drop index idx_name ON smsevent;
CREATE INDEX `idx_name` on smsevent (`name`);
drop index idx_name_furigana ON smsevent;
CREATE INDEX `idx_name_furigana` on smsevent (`name_furigana`);


--2014-05-09
--備忘。GENELから転記
--2014-04-03
--cron起動のバッチの多重起動制御
ALTER TABLE `batch_manager` ADD `last_run_start` DATETIME NULL ;

--2014-05-09
--account_idの項目追加。h_idnumberとcontacts.account_idの断面が異なると、帳票出力時に不整合なデータになる。
ALTER TABLE sharei_hibetsu ADD account_id varchar(36) DEFAULT NULL ;
ALTER TABLE sharei_kouza ADD account_id varchar(36) DEFAULT NULL ;
ALTER TABLE sharei_koushi ADD account_id varchar(36) DEFAULT NULL ;

--2014/05/28 kato くずは追加
--事業所一覧から新規事業所を登録する
--xxxを登録時のidに置き換え、下記updateを実施する
update resource set id='d32c6192-e6be-2370-c9ff-5385aae9d6e2', school_id='23', name='くずは', area_id='大阪' where id='d43b0b6a-c00e-c55c-8942-538ebe1d9fa2';
update resource_sub1 set id_sub1='d32c6192-e6be-2370-c9ff-5385aae9d6e2', last_student_id_c=0, last_invoice_id_c=0, last_teacher_id_c=90000 where id_sub1='d43b0b6a-c00e-c55c-8942-538ebe1d9fa2';

--2014-05-30 shimoju
--コンビニデータ取り込み用の端末情報を追加するため、端末の種別を追加
ALTER TABLE `resource` ADD `tanmatsu_category` VARCHAR( 4 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL;
UPDATE resource SET tanmatsu_category = '01' WHERE resource.id IN ( 
"a09ae8da-32c9-5d91-1620-511fccf50c1f",
"86024d6c-2c9f-22e5-a780-511fcc643a10",
"bd90ff50-9738-8d04-1214-5120685754d3",
"50e3fb3f-4bfa-af3b-2362-5121a4dff4e3",
"d48a5121-d9b1-1516-0614-5121cfb59e91",
"82cbb0e0-c4b5-02d7-4a24-5121d2151fbf",
"47fcd0f9-91ed-22a6-b859-5121d2c72daa",
"71949f15-1600-6b5b-afb2-518b309f26a5",
"2fb00a59-1331-579f-024b-51f70dfa1ce0",
"cf061732-905d-5ee1-f704-51f70ddded8f",
"79534fff-703d-1fad-990f-51f70e31ba90",
"5b9e04cc-00bc-132f-faa6-51f70e5d3819",
"47080098-13bd-e516-5410-51f70e1ca19b",
"755390c6-bab4-61fe-d015-51f70eee2421",
"21e5566c-ee02-fd07-ba01-51f70ee914c8",
"f2128cd6-bc27-4b60-aaaf-51f70e465634",
"29e880a3-f77d-1d3d-96b9-51f70f3f8b8b",
"e32effe9-9ed0-4fff-004d-5216c4efd277"
);
UPDATE resource SET tanmatsu_category = '02' WHERE resource.id IN ( 
"241b288f-2394-f764-a59a-538689be83b8",
"b78db2b3-4e94-3053-47c7-53868a9ae205",
"28c996d5-7f0d-af0f-0380-53868ab33140",
"d88978a8-3137-4ccb-cb6b-53868a388bee",
"d78f0bfb-d755-50ce-29d5-53868adcb5c4",
"c704aaab-937f-4634-4950-53868b0a2d94",
"139cc11e-409a-5540-7964-53868bf547ca",
"5a300c77-e806-86aa-a8c0-53868b2ddf4b",
"679d56eb-de81-39f6-5a54-53868b0237b7",
"9187967f-13e3-0ab1-3874-53868bfec4ea",
"118fe9a6-913d-4a77-07c5-53868b45f634",
"c2930623-8568-20ba-c7d5-53868cadd7a4",
"a96c1a3b-e8d3-0921-be32-53868cf34dc6",
"5cbb1404-64b7-df34-e16c-53868cd6c003",
"a903d801-fdd9-0100-45fa-53868c7380bb"
);

--2014-06-03 kato ワークスペースの申込種別について
--L.138のテーブル定義には追加されている内容です。
ALTER TABLE yoyaku_basic ADD COLUMN web_order_flg tinyint(1) DEFAULT '0';

--2014/06/04 kato
ALTER TABLE `yoyaku_contacts` ADD COLUMN `old_valid_date` DATE NULL ;
ALTER TABLE `yoyaku_contacts` ADD COLUMN `old_contact_status` INT NULL ;
ALTER TABLE `yoyaku_contacts` ADD COLUMN `old_yutai_kubun` VARCHAR( 30 ) NULL;
ALTER TABLE `yoyaku_contacts` ADD COLUMN `old_account_id` VARCHAR( 36 ) NULL ;

-- 2014-06-09 kato 優待区分に立替金ＩＤを設定するため
 ALTER TABLE `master_kanri` ADD `field7` VARCHAR( 40 ) NULL DEFAULT NULL ;

--2014-07-28
--データ移行に対する項目追加
ALTER TABLE sharei_koushi ADD gensen_umu varchar(40) DEFAULT NULL ;

--2014-08-05
--WEB申込後のサンキューメールのテンプレート
DELETE FROM `email_templates` WHERE CONVERT(`email_templates`.`id` USING utf8) = '1ca1eb40-372a-27a2-bf44-5001d1ad9d91' LIMIT 1;
DELETE FROM `email_templates` WHERE CONVERT(`email_templates`.`id` USING utf8) = '28e74451-24cc-c806-9642-5001d0f926d9' LIMIT 1;
DELETE FROM `email_templates` WHERE CONVERT(`email_templates`.`id` USING utf8) = '49caa19d-9872-3095-6bd4-5001d0aa692d' LIMIT 1;
DELETE FROM `email_templates` WHERE CONVERT(`email_templates`.`id` USING utf8) = '4dac1600-cfec-aae9-e2ee-506901c68069' LIMIT 1;
DELETE FROM `email_templates` WHERE CONVERT(`email_templates`.`id` USING utf8) = '5bc40075-3911-d52a-a0f8-5001d15a06e5' LIMIT 1;
DELETE FROM `email_templates` WHERE CONVERT(`email_templates`.`id` USING utf8) = '5ea7a466-203e-45f4-582c-5001d12b64db' LIMIT 1;
DELETE FROM `email_templates` WHERE CONVERT(`email_templates`.`id` USING utf8) = '687ec71c-1c7d-6c97-fc2a-4c889fc5d652' LIMIT 1;
DELETE FROM `email_templates` WHERE CONVERT(`email_templates`.`id` USING utf8) = 'a312c57a-0e45-6f65-0402-5001d1e84f1d' LIMIT 1;
DELETE FROM `email_templates` WHERE CONVERT(`email_templates`.`id` USING utf8) = 'a32c39d6-45da-a750-6e4d-5001d0bd7a8d' LIMIT 1;
DELETE FROM `email_templates` WHERE CONVERT(`email_templates`.`id` USING utf8) = 'b0845182-f1bc-145f-0560-5001d0cbee2a' LIMIT 1;
DELETE FROM `email_templates` WHERE CONVERT(`email_templates`.`id` USING utf8) = 'bb0d4d73-13a4-b000-36a6-5001d0094cb6' LIMIT 1;
DELETE FROM `email_templates` WHERE CONVERT(`email_templates`.`id` USING utf8) = 'bf737929-0509-7bfe-586f-500248ae9b81' LIMIT 1;
DELETE FROM `email_templates` WHERE CONVERT(`email_templates`.`id` USING utf8) = 'c083c4e8-6f1d-24ea-fd47-5001d0b9910f' LIMIT 1;
DELETE FROM `email_templates` WHERE CONVERT(`email_templates`.`id` USING utf8) = 'c387f0ac-5700-6490-3264-5001d1127ee7' LIMIT 1;
DELETE FROM `email_templates` WHERE CONVERT(`email_templates`.`id` USING utf8) = 'e86510a2-302b-7c34-250d-5001d1fc1b41' LIMIT 1;
DELETE FROM `email_templates` WHERE CONVERT(`email_templates`.`id` USING utf8) = 'sssssss-1111' LIMIT 1;

INSERT INTO `email_templates` (`id`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `published`, `name`, `description`, `subject`, `body`, `body_html`, `deleted`, `from_addr`, `category_id`) VALUES
('sssssss-1111', '2014-07-28 16:28:50', '2014-07-28 16:28:50', '1', '1', 'off', 'WEB講座申込み完了のご案内', 'コメント', 'WEB講座申込み完了のご案内', '$contact_name様\r\n\r\nこのたびは、朝日カルチャーセンター　朝日ＪＴＢ・交流文化塾の講座をお申込みいただき、ありがとうございました。\r\nお申込みの内容は次のとおりです。ご確認くださいますようお願いいたします。料金はいずれも消費税が含まれています。\r\n\r\n$nyukaikin_info$koza_info\r\n\r\n今回、お支払いただいた料金は合計で　$total_fee円です。\r\n・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・\r\n本メールにて領収書・受講券といたします。プリントアウトしていただき、開講当日お持ちください。各講座の日程は、お手数\r\nですが、マイページにてご確認くださいますようお願いいたします。お申し込み内容にご不明の点などがございましたら、開催\r\n教室へお電話にてお問い合わせください。どうぞよろしくお願いします。', '$contact_name様<br />\r\n<br />\r\nこのたびは、朝日カルチャーセンター　朝日ＪＴＢ・交流文化塾の講座をお申込みいただき、ありがとうございました。<br />\r\nお申込みの内容は次のとおりです。ご確認くださいますようお願いいたします。料金はいずれも消費税が含まれています。<br />\r\n<br />\r\n$nyukaikin_info$koza_info<br />\r\n<br />\r\n今回、お支払いただいた料金は合計で　$total_fee円です。<br />\r\n・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・<br />\r\n本メールにて領収書・受講券といたします。プリントアウトしていただき、開講当日お持ちください。各講座の日程は、お手数<br />\r\nですが、マイページにてご確認くださいますようお願いいたします。お申し込み内容にご不明の点などがございましたら、開催<br />\r\n教室へお電話にてお問い合わせください。どうぞよろしくお願いします。', 0, NULL, NULL);

insert into `email_templates` select '49caa19d-9872-3095-6bd4-5001d0aa692d', `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `published`, `name`, `description`, `subject`, `body`, `body_html`, `deleted`, `from_addr`, `category_id` from `email_templates` where `id`='sssssss-1111';
insert into `email_templates` select 'a32c39d6-45da-a750-6e4d-5001d0bd7a8d', `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `published`, `name`, `description`, `subject`, `body`, `body_html`, `deleted`, `from_addr`, `category_id` from `email_templates` where `id`='sssssss-1111';
insert into `email_templates` select 'b0845182-f1bc-145f-0560-5001d0cbee2a', `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `published`, `name`, `description`, `subject`, `body`, `body_html`, `deleted`, `from_addr`, `category_id` from `email_templates` where `id`='sssssss-1111';
insert into `email_templates` select 'bf737929-0509-7bfe-586f-500248ae9b81', `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `published`, `name`, `description`, `subject`, `body`, `body_html`, `deleted`, `from_addr`, `category_id` from `email_templates` where `id`='sssssss-1111';
insert into `email_templates` select '28e74451-24cc-c806-9642-5001d0f926d9', `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `published`, `name`, `description`, `subject`, `body`, `body_html`, `deleted`, `from_addr`, `category_id` from `email_templates` where `id`='sssssss-1111';
insert into `email_templates` select 'c083c4e8-6f1d-24ea-fd47-5001d0b9910f', `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `published`, `name`, `description`, `subject`, `body`, `body_html`, `deleted`, `from_addr`, `category_id` from `email_templates` where `id`='sssssss-1111';
insert into `email_templates` select '5ea7a466-203e-45f4-582c-5001d12b64db', `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `published`, `name`, `description`, `subject`, `body`, `body_html`, `deleted`, `from_addr`, `category_id` from `email_templates` where `id`='sssssss-1111';
insert into `email_templates` select '5bc40075-3911-d52a-a0f8-5001d15a06e5', `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `published`, `name`, `description`, `subject`, `body`, `body_html`, `deleted`, `from_addr`, `category_id` from `email_templates` where `id`='sssssss-1111';
insert into `email_templates` select 'c387f0ac-5700-6490-3264-5001d1127ee7', `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `published`, `name`, `description`, `subject`, `body`, `body_html`, `deleted`, `from_addr`, `category_id` from `email_templates` where `id`='sssssss-1111';
insert into `email_templates` select 'bb0d4d73-13a4-b000-36a6-5001d0094cb6', `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `published`, `name`, `description`, `subject`, `body`, `body_html`, `deleted`, `from_addr`, `category_id` from `email_templates` where `id`='sssssss-1111';
insert into `email_templates` select '1ca1eb40-372a-27a2-bf44-5001d1ad9d91', `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `published`, `name`, `description`, `subject`, `body`, `body_html`, `deleted`, `from_addr`, `category_id` from `email_templates` where `id`='sssssss-1111';
insert into `email_templates` select 'a312c57a-0e45-6f65-0402-5001d1e84f1d', `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `published`, `name`, `description`, `subject`, `body`, `body_html`, `deleted`, `from_addr`, `category_id` from `email_templates` where `id`='sssssss-1111';
insert into `email_templates` select 'e86510a2-302b-7c34-250d-5001d1fc1b41', `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `published`, `name`, `description`, `subject`, `body`, `body_html`, `deleted`, `from_addr`, `category_id` from `email_templates` where `id`='sssssss-1111';
insert into `email_templates` select '687ec71c-1c7d-6c97-fc2a-4c889fc5d652', `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `published`, `name`, `description`, `subject`, `body`, `body_html`, `deleted`, `from_addr`, `category_id` from `email_templates` where `id`='sssssss-1111';
insert into `email_templates` select '4dac1600-cfec-aae9-e2ee-506901c68069', `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `published`, `name`, `description`, `subject`, `body`, `body_html`, `deleted`, `from_addr`, `category_id` from `email_templates` where `id`='sssssss-1111';


-- 2014-08-07 mon
-- This is to fix this bug 54022; this is to identify if the system needs to update
-- the contact_status/contacts via contact_status/yoyaku_contact even the student
-- has enough valid_date
ALTER TABLE yoyaku_contacts ADD COLUMN hikaiin_shori TINYINT(1) DEFAULT 0 NOT NULL ;

-- 2014-08-12 kato
-- WEB端末、WEBユーザ
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`, `tanmatsu_shimebi`, `shareilock_date`, `sharei_keisan_datetime`, `outside_class`, `phone_home`, `phone_fax`, `tanka`, `somenseki`, `tanmatsu_category`) VALUES
('183c7674-3250-f0c3-1e52-53e32aa78d57', 'a32c39d6-45da-a750-6e4d-5001d0bd7a8d', '2014-08-07 16:26:35', '2014-08-07 16:26:35', '1', '1', 'WEB（立川）', NULL, NULL, 'tanmatsu', NULL, 0, NULL, '', NULL, NULL, NULL, '2014-08-11', NULL, NULL, 0, NULL, NULL, NULL, NULL, '03'),
('299b2d95-d61c-911b-d7e7-53e32d30affb', 'c083c4e8-6f1d-24ea-fd47-5001d0b9910f', '2014-08-07 16:40:25', '2014-08-07 16:40:25', '1', '1', 'WEB（中之島）', NULL, NULL, 'tanmatsu', NULL, 0, NULL, '', NULL, NULL, NULL, '2014-08-11', NULL, NULL, 0, NULL, NULL, NULL, NULL, '03'),
('3ea26272-7b64-9b48-9d80-53e32dc404dc', 'c387f0ac-5700-6490-3264-5001d1127ee7', '2014-08-07 16:42:31', '2014-08-07 16:42:31', '1', '1', 'WEB（京都）', NULL, NULL, 'tanmatsu', NULL, 0, NULL, '', NULL, NULL, NULL, '2014-08-11', NULL, NULL, 0, NULL, NULL, NULL, NULL, '03'),
('40af3637-ee74-29d2-4bec-53e32a44a218', '49caa19d-9872-3095-6bd4-5001d0aa692d', '2014-08-07 16:26:07', '2014-08-07 16:26:07', '1', '1', 'WEB（横浜）', NULL, NULL, 'tanmatsu', NULL, 0, NULL, '', NULL, NULL, NULL, '2014-08-11', NULL, NULL, 0, NULL, NULL, NULL, NULL, '03'),
('4e91a397-a058-81a9-3271-53e32a6ad28a', 'b0845182-f1bc-145f-0560-5001d0cbee2a', '2014-08-07 16:26:48', '2014-08-07 16:26:48', '1', '1', 'WEB（湘南）', NULL, NULL, 'tanmatsu', NULL, 0, NULL, '', NULL, NULL, NULL, '2014-08-11', NULL, NULL, 0, NULL, NULL, NULL, NULL, '03'),
('66d9c2b6-bb27-3185-4137-53e32ed026cd', 'a312c57a-0e45-6f65-0402-5001d1e84f1d', '2014-08-07 16:46:00', '2014-08-07 16:46:00', '1', '1', 'WEB（福岡）', NULL, NULL, 'tanmatsu', NULL, 0, NULL, '', NULL, NULL, NULL, '2014-08-11', NULL, NULL, 0, NULL, NULL, NULL, NULL, '03'),
('6b565a8b-2dc4-5f6a-7d9c-53e32d87b608', '5ea7a466-203e-45f4-582c-5001d12b64db', '2014-08-07 16:41:02', '2014-08-07 16:41:02', '1', '1', 'WEB（川西）', NULL, NULL, 'tanmatsu', NULL, 0, NULL, '', NULL, NULL, NULL, '2014-08-11', NULL, NULL, 0, NULL, NULL, NULL, NULL, '03'),
('71685da5-9a0c-96c7-4a4e-53e32fbf362d', 'e86510a2-302b-7c34-250d-5001d1fc1b41', '2014-08-07 16:47:26', '2014-08-07 16:47:26', '1', '1', 'WEB（名古屋）', NULL, NULL, 'tanmatsu', NULL, 0, NULL, '', NULL, NULL, NULL, '2014-08-11', NULL, NULL, 0, NULL, NULL, NULL, NULL, '03'),
('91eb046b-56c5-b6d7-f564-53e32d9c09ab', '28e74451-24cc-c806-9642-5001d0f926d9', '2014-08-07 16:39:55', '2014-08-07 16:39:55', '1', '1', 'WEB（札幌）', NULL, NULL, 'tanmatsu', NULL, 0, NULL, '', NULL, NULL, NULL, '2014-08-11', NULL, NULL, 0, NULL, NULL, NULL, NULL, '03'),
('95f597a8-6f67-32bc-aee5-53e32e2166c7', '1ca1eb40-372a-27a2-bf44-5001d1ad9d91', '2014-08-07 16:45:25', '2014-08-07 16:45:25', '1', '1', 'WEB（北九州）', NULL, NULL, 'tanmatsu', NULL, 0, NULL, '', NULL, NULL, NULL, '2014-08-11', NULL, NULL, 0, NULL, NULL, NULL, NULL, '03'),
('a74a1d1a-3b9b-89c7-6f51-53e3298d6cf8', 'sssssss-1111', '2014-08-07 16:25:49', '2014-08-07 16:25:49', '1', '1', 'WEB（新宿）', NULL, NULL, 'tanmatsu', NULL, 0, NULL, '', NULL, NULL, NULL, '2014-08-11', NULL, NULL, 0, NULL, NULL, NULL, NULL, '03'),
('b0f8c518-13f2-95f3-e39f-53e32ee1e04d', 'd32c6192-e6be-2370-c9ff-5385aae9d6e2', '2014-08-07 16:44:25', '2014-08-07 16:44:25', '1', '1', 'WEB（くずは）', NULL, NULL, 'tanmatsu', NULL, 0, NULL, '', NULL, NULL, NULL, '2014-08-11', NULL, NULL, 0, NULL, NULL, NULL, NULL, '03'),
('e61042b1-1726-4881-b3ef-53e32d631c5f', '5bc40075-3911-d52a-a0f8-5001d15a06e5', '2014-08-07 16:41:40', '2014-08-07 16:41:40', '1', '1', 'WEB（芦屋）', NULL, NULL, 'tanmatsu', NULL, 0, NULL, '', NULL, NULL, NULL, '2014-08-11', NULL, NULL, 0, NULL, NULL, NULL, NULL, '03');

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`, `invalid_email`, `smsgroup`, `contact_status`, `picture_filename`, `picture_file_mime_type`, `school_id`, `major`, `send_document_home`, `send_document_work`, `company_name`, `idnumber`, `nationality`, `gender`, `work_school`, `last_school_name`, `last_school_dept`, `study_aim`, `leave_date`, `enroll_date`, `grade`, `grade_entered`, `grade_entrance_date`, `application_date`, `card_id`, `nyutai_mail_notice`, `phone_mobile_cleaned`, `phone_home_cleaned`, `phone_work_cleaned`, `phone_other_cleaned`, `is_available_strict`, `bank_payment_type`, `bank_code`, `bank_branch_code`, `bank_name`, `bank_branch_name`, `bank_account_type`, `bank_account_no`, `bank_account_name`, `yutai_kubun`, `renewal_date`, `valid_date`, `old_pass`, `renraku_saki`, `shokaisha_bango`, `renraku_jiko`, `other_komoku`, `mail_magazine`, `anketo_shokugyo`, `anketo_baitai`, `anketo_janru`, `anketo_other`, `vacation_total`, `vacation_used`, `transportfee`, `transport_fee_type`, `incometax_rate`, `internal_yoyaku_id`, `lead_source_kubun`, `account_id`, `portal_app`, `old_valid_date`, `old_contact_status`, `old_yutai_kubun`, `old_account_id`, `nyukai_tetsuzukichu`, `tanmatsu_id`, `vt_last_salesno`) VALUES
('199b1fac-4ea2-9335-7e34-53e337fcb79c', 0, '2014-08-07 17:22:00', '2014-08-07 17:22:27', '1', NULL, '1', NULL, 'スタッフ', 'WEB（中之島）', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '※削除／編集をしないでください。\r\nWEB決済のシステム処理時にこのスタッフを使用します。', 0, 'staff', '20', NULL, NULL, 'c083c4e8-6f1d-24ea-fd47-5001d0b9910f', NULL, 0, 0, NULL, '2000000999', 'JP', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, 0, 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.1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '299b2d95-d61c-911b-d7e7-53e32d30affb', NULL),
('39a01a9e-4a2b-97a1-96df-53e335b65c30', 0, '2014-08-07 17:13:38', '2014-08-07 17:16:22', '1', NULL, '1', NULL, 'スタッフ', 'WEB（新宿）', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '※削除／編集をしないでください。\r\nWEB決済のシステム処理時にこのスタッフを使用します。', 0, 'staff', '20', NULL, NULL, 'sssssss-1111', NULL, 0, 0, NULL, '0100000999', 'JP', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, 0, 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.1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'a74a1d1a-3b9b-89c7-6f51-53e3298d6cf8', NULL),
('3ef11f5e-af2e-7232-6a9c-53e338956d69', 0, '2014-08-07 17:29:26', '2014-08-07 17:37:00', '1', NULL, '1', NULL, 'スタッフ', 'WEB（京都）', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '※削除／編集をしないでください。\r\nWEB決済のシステム処理時にこのスタッフを使用します。', 0, 'staff', '20', NULL, NULL, 'c387f0ac-5700-6490-3264-5001d1127ee7', NULL, 0, 0, NULL, '2800000999', 'JP', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, 0, 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.1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '3ea26272-7b64-9b48-9d80-53e32dc404dc', NULL),
('5e8dbbf6-618f-34d1-7b61-53e339d9db1c', 0, '2014-08-07 17:31:19', '2014-08-07 17:33:55', '1', NULL, '1', NULL, 'スタッフ', 'WEB（名古屋）', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '※削除／編集をしないでください。\r\nWEB決済のシステム処理時にこのスタッフを使用します。', 0, 'staff', '20', NULL, NULL, 'e86510a2-302b-7c34-250d-5001d1fc1b41', NULL, 0, 0, NULL, '4500000999', 'JP', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, 0, 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.1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '71685da5-9a0c-96c7-4a4e-53e32fbf362d', NULL),
('632708e4-a3bb-52b6-fb4e-53e3386b7775', 0, '2014-08-07 17:28:23', '2014-08-07 17:37:29', '1', NULL, '1', NULL, 'スタッフ', 'WEB（芦屋）', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '※削除／編集をしないでください。\r\nWEB決済のシステム処理時にこのスタッフを使用します。', 0, 'staff', '20', NULL, NULL, '5bc40075-3911-d52a-a0f8-5001d15a06e5', NULL, 0, 0, NULL, '2400000999', 'JP', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, 0, 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.1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'e61042b1-1726-4881-b3ef-53e32d631c5f', NULL),
('65ae0734-18c3-7a4d-caa0-53e3370b7492', 0, '2014-08-07 17:21:22', '2014-08-07 17:22:40', '1', NULL, '1', NULL, 'スタッフ', 'WEB（札幌）', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '※削除／編集をしないでください。\r\nWEB決済のシステム処理時にこのスタッフを使用します。', 0, 'staff', '20', NULL, NULL, '28e74451-24cc-c806-9642-5001d0f926d9', NULL, 0, 0, NULL, '1700000999', 'JP', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, 0, 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.1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '91eb046b-56c5-b6d7-f564-53e32d9c09ab', NULL),
('687e9e1d-4dbf-da43-b77c-53e3363da5f1', 0, '2014-08-07 17:20:14', '2014-08-07 17:20:39', '1', NULL, '1', NULL, 'スタッフ', 'WEB（湘南）', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '※削除／編集をしないでください。\r\nWEB決済のシステム処理時にこのスタッフを使用します。', 0, 'staff', '20', NULL, NULL, 'b0845182-f1bc-145f-0560-5001d0cbee2a', NULL, 0, 0, NULL, '0600000999', 'JP', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, 0, 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.1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '4e91a397-a058-81a9-3271-53e32a6ad28a', NULL),
('9eb0fe95-e836-fa9c-1489-53e336ae90ef', 0, '2014-08-07 17:18:08', '2014-08-07 17:18:30', '1', NULL, '1', NULL, 'スタッフ', 'WEB（立川）', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '※削除／編集をしないでください。\r\nWEB決済のシステム処理時にこのスタッフを使用します。', 0, 'staff', '20', NULL, NULL, 'a32c39d6-45da-a750-6e4d-5001d0bd7a8d', NULL, 0, 0, NULL, '0500000999', 'JP', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, 0, 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.1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '183c7674-3250-f0c3-1e52-53e32aa78d57', NULL),
('a72815a7-b3f1-a23a-3570-53e339922ded', 0, '2014-08-07 17:30:07', '2014-08-07 17:36:47', '1', NULL, '1', NULL, 'スタッフ', 'WEB（北九州）', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '※削除／編集をしないでください。\r\nWEB決済のシステム処理時にこのスタッフを使用します。', 0, 'staff', '20', NULL, NULL, '1ca1eb40-372a-27a2-bf44-5001d1ad9d91', NULL, 0, 0, NULL, '3600000999', 'JP', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, 0, 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.1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '95f597a8-6f67-32bc-aee5-53e32e2166c7', NULL),
('b49ce0c7-499c-fb09-fbd7-53e338a9805d', 0, '2014-08-07 17:27:49', '2014-08-07 17:37:16', '1', NULL, '1', NULL, 'スタッフ', 'WEB（くずは）', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '※削除／編集をしないでください。\r\nWEB決済のシステム処理時にこのスタッフを使用します。', 0, 'staff', '20', NULL, NULL, 'd32c6192-e6be-2370-c9ff-5385aae9d6e2', NULL, 0, 0, NULL, '2300000999', 'JP', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, 0, 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.1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'b0f8c518-13f2-95f3-e39f-53e32ee1e04d', NULL),
('c5ac023e-c67b-88b1-845c-53e3368a1e60', 0, '2014-08-07 17:17:31', '2014-08-07 17:17:40', '1', NULL, '1', NULL, 'スタッフ', 'WEB（横浜）', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '※削除／編集をしないでください。\r\nWEB決済のシステム処理時にこのスタッフを使用します。', 0, 'staff', '20', NULL, NULL, '49caa19d-9872-3095-6bd4-5001d0aa692d', NULL, 0, 0, NULL, '0300000999', 'JP', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, 0, 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.1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '40af3637-ee74-29d2-4bec-53e32a44a218', NULL),
('dba42ef1-1ec6-65cf-7cee-53e337837a88', 0, '2014-08-07 17:23:24', '2014-08-07 17:23:39', '1', NULL, '1', NULL, 'スタッフ', 'WEB（川西）', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '※削除／編集をしないでください。\r\nWEB決済のシステム処理時にこのスタッフを使用します。', 0, 'staff', '20', NULL, NULL, '5ea7a466-203e-45f4-582c-5001d12b64db', NULL, 0, 0, NULL, '2200000999', 'JP', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, 0, 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.1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '6b565a8b-2dc4-5f6a-7d9c-53e32d87b608', NULL),
('f167b9fd-1854-59eb-e0e5-53e339e1f1fa', 0, '2014-08-07 17:30:41', '2014-08-07 17:35:27', '1', NULL, '1', NULL, 'スタッフ', 'WEB（福岡）', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '※削除／編集をしないでください。\r\nWEB決済のシステム処理時にこのスタッフを使用します。', 0, 'staff', '20', NULL, NULL, 'a312c57a-0e45-6f65-0402-5001d1e84f1d', NULL, 0, 0, NULL, '3700000999', 'JP', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, 0, 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.1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '66d9c2b6-bb27-3185-4137-53e32ed026cd', NULL);

INSERT INTO `contacts_sub1` (`id_sub1`) VALUES
('199b1fac-4ea2-9335-7e34-53e337fcb79c'),
('39a01a9e-4a2b-97a1-96df-53e335b65c30'),
('3ef11f5e-af2e-7232-6a9c-53e338956d69'),
('5e8dbbf6-618f-34d1-7b61-53e339d9db1c'),
('632708e4-a3bb-52b6-fb4e-53e3386b7775'),
('65ae0734-18c3-7a4d-caa0-53e3370b7492'),
('687e9e1d-4dbf-da43-b77c-53e3363da5f1'),
('9eb0fe95-e836-fa9c-1489-53e336ae90ef'),
('a72815a7-b3f1-a23a-3570-53e339922ded'),
('b49ce0c7-499c-fb09-fbd7-53e338a9805d'),
('c5ac023e-c67b-88b1-845c-53e3368a1e60'),
('dba42ef1-1ec6-65cf-7cee-53e337837a88'),
('f167b9fd-1854-59eb-e0e5-53e339e1f1fa');

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
('36eb4bf0-f290-dd5d-fcfd-53e88b18264a', 'web_yokohama', 'wer5EwGzcTa9M', '145fbe4440e273a51fdbf61018f98b77', 'off', '0', NULL, '2014-08-11 18:23:06', '2014-08-11 18:23:06', '1', '1', 'Active', 'YTo4OntzOjg6ImdyaWRsaW5lIjtzOjM6Im9mZiI7czoxMjoibWFpbG1lcmdlX29uIjtzOjM6Im9mZiI7czoxMjoiZGlzcGxheV90YWJzIjthOjEwOntpOjA7czo4OiJDYWxlbmRhciI7aToxO3M6NjoiQ291cnNlIjtpOjI7czo3OiJTdHVkZW50IjtpOjM7czo3OiJUZWFjaGVyIjtpOjQ7czo4OiJDb250YWN0cyI7aTo1O3M6ODoiUmVzb3VyY2UiO2k6NjtzOjY6IkVtYWlscyI7aTo3O3M6NToiS2VpcmkiO2k6ODtzOjQ6Ik1haW4iO2k6OTtzOjc6IlNNU0hlbHAiO31zOjk6ImhpZGVfdGFicyI7YTowOnt9czoxMToicmVtb3ZlX3RhYnMiO2E6MDp7fXM6MjoidXQiO3M6MToiMSI7czoxMzoibWFpbF9mcm9tbmFtZSI7czo5OiJUZWNobm9TTVMiO3M6MTY6Im1haWxfZnJvbWFkZHJlc3MiO3M6MjQ6InRlY2hub3Ntc0B0ZWNobm9waWFuLmNvbSI7fQ==', 0, 0, 0, 'c5ac023e-c67b-88b1-845c-53e3368a1e60', 'staff', NULL),
('49b9c097-fec5-4017-8a88-53e88c043ecd', 'web_kyoto', 'wer5EwGzcTa9M', '145fbe4440e273a51fdbf61018f98b77', 'off', '0', NULL, '2014-08-11 18:29:11', '2014-08-11 18:29:11', '1', '1', 'Active', 'YTo4OntzOjg6ImdyaWRsaW5lIjtzOjM6Im9mZiI7czoxMjoibWFpbG1lcmdlX29uIjtzOjM6Im9mZiI7czoxMjoiZGlzcGxheV90YWJzIjthOjEwOntpOjA7czo4OiJDYWxlbmRhciI7aToxO3M6NjoiQ291cnNlIjtpOjI7czo3OiJTdHVkZW50IjtpOjM7czo3OiJUZWFjaGVyIjtpOjQ7czo4OiJDb250YWN0cyI7aTo1O3M6ODoiUmVzb3VyY2UiO2k6NjtzOjY6IkVtYWlscyI7aTo3O3M6NToiS2VpcmkiO2k6ODtzOjQ6Ik1haW4iO2k6OTtzOjc6IlNNU0hlbHAiO31zOjk6ImhpZGVfdGFicyI7YTowOnt9czoxMToicmVtb3ZlX3RhYnMiO2E6MDp7fXM6MjoidXQiO3M6MToiMSI7czoxMzoibWFpbF9mcm9tbmFtZSI7czo5OiJUZWNobm9TTVMiO3M6MTY6Im1haWxfZnJvbWFkZHJlc3MiO3M6MjQ6InRlY2hub3Ntc0B0ZWNobm9waWFuLmNvbSI7fQ==', 0, 0, 0, '3ef11f5e-af2e-7232-6a9c-53e338956d69', 'staff', NULL),
('4c3a3c30-1d75-f987-8ae5-53e88cf65a55', 'web_kawanishi', 'wer5EwGzcTa9M', '145fbe4440e273a51fdbf61018f98b77', 'off', '0', NULL, '2014-08-11 18:27:07', '2014-08-11 18:27:07', '1', '1', 'Active', 'YTo4OntzOjg6ImdyaWRsaW5lIjtzOjM6Im9mZiI7czoxMjoibWFpbG1lcmdlX29uIjtzOjM6Im9mZiI7czoxMjoiZGlzcGxheV90YWJzIjthOjEwOntpOjA7czo4OiJDYWxlbmRhciI7aToxO3M6NjoiQ291cnNlIjtpOjI7czo3OiJTdHVkZW50IjtpOjM7czo3OiJUZWFjaGVyIjtpOjQ7czo4OiJDb250YWN0cyI7aTo1O3M6ODoiUmVzb3VyY2UiO2k6NjtzOjY6IkVtYWlscyI7aTo3O3M6NToiS2VpcmkiO2k6ODtzOjQ6Ik1haW4iO2k6OTtzOjc6IlNNU0hlbHAiO31zOjk6ImhpZGVfdGFicyI7YTowOnt9czoxMToicmVtb3ZlX3RhYnMiO2E6MDp7fXM6MjoidXQiO3M6MToiMSI7czoxMzoibWFpbF9mcm9tbmFtZSI7czo5OiJUZWNobm9TTVMiO3M6MTY6Im1haWxfZnJvbWFkZHJlc3MiO3M6MjQ6InRlY2hub3Ntc0B0ZWNobm9waWFuLmNvbSI7fQ==', 0, 0, 0, 'dba42ef1-1ec6-65cf-7cee-53e337837a88', 'staff', NULL),
('521c8837-4f1f-e0c1-46e3-53e88db6e279', 'web_kitakhushu', 'wer5EwGzcTa9M', '145fbe4440e273a51fdbf61018f98b77', 'off', '0', NULL, '2014-08-11 18:29:57', '2014-08-11 18:29:57', '1', '1', 'Active', 'YTo4OntzOjg6ImdyaWRsaW5lIjtzOjM6Im9mZiI7czoxMjoibWFpbG1lcmdlX29uIjtzOjM6Im9mZiI7czoxMjoiZGlzcGxheV90YWJzIjthOjEwOntpOjA7czo4OiJDYWxlbmRhciI7aToxO3M6NjoiQ291cnNlIjtpOjI7czo3OiJTdHVkZW50IjtpOjM7czo3OiJUZWFjaGVyIjtpOjQ7czo4OiJDb250YWN0cyI7aTo1O3M6ODoiUmVzb3VyY2UiO2k6NjtzOjY6IkVtYWlscyI7aTo3O3M6NToiS2VpcmkiO2k6ODtzOjQ6Ik1haW4iO2k6OTtzOjc6IlNNU0hlbHAiO31zOjk6ImhpZGVfdGFicyI7YTowOnt9czoxMToicmVtb3ZlX3RhYnMiO2E6MDp7fXM6MjoidXQiO3M6MToiMSI7czoxMzoibWFpbF9mcm9tbmFtZSI7czo5OiJUZWNobm9TTVMiO3M6MTY6Im1haWxfZnJvbWFkZHJlc3MiO3M6MjQ6InRlY2hub3Ntc0B0ZWNobm9waWFuLmNvbSI7fQ==', 0, 0, 0, 'a72815a7-b3f1-a23a-3570-53e339922ded', 'staff', NULL),
('53f166b3-e2a5-93d3-9681-53e88cd283cb', 'web_nakanoshima', 'wer5EwGzcTa9M', '145fbe4440e273a51fdbf61018f98b77', 'off', '0', NULL, '2014-08-11 18:26:21', '2014-08-11 18:26:21', '1', '1', 'Active', 'YTo4OntzOjg6ImdyaWRsaW5lIjtzOjM6Im9mZiI7czoxMjoibWFpbG1lcmdlX29uIjtzOjM6Im9mZiI7czoxMjoiZGlzcGxheV90YWJzIjthOjEwOntpOjA7czo4OiJDYWxlbmRhciI7aToxO3M6NjoiQ291cnNlIjtpOjI7czo3OiJTdHVkZW50IjtpOjM7czo3OiJUZWFjaGVyIjtpOjQ7czo4OiJDb250YWN0cyI7aTo1O3M6ODoiUmVzb3VyY2UiO2k6NjtzOjY6IkVtYWlscyI7aTo3O3M6NToiS2VpcmkiO2k6ODtzOjQ6Ik1haW4iO2k6OTtzOjc6IlNNU0hlbHAiO31zOjk6ImhpZGVfdGFicyI7YTowOnt9czoxMToicmVtb3ZlX3RhYnMiO2E6MDp7fXM6MjoidXQiO3M6MToiMSI7czoxMzoibWFpbF9mcm9tbmFtZSI7czo5OiJUZWNobm9TTVMiO3M6MTY6Im1haWxfZnJvbWFkZHJlc3MiO3M6MjQ6InRlY2hub3Ntc0B0ZWNobm9waWFuLmNvbSI7fQ==', 0, 0, 0, '199b1fac-4ea2-9335-7e34-53e337fcb79c', 'staff', NULL),
('6628059d-96ec-a50e-0ddb-53e88b53ffb7', 'web_tatikawa', 'wer5EwGzcTa9M', '145fbe4440e273a51fdbf61018f98b77', 'off', '0', NULL, '2014-08-11 18:24:04', '2014-08-11 18:24:04', '1', '1', 'Active', 'YTo4OntzOjg6ImdyaWRsaW5lIjtzOjM6Im9mZiI7czoxMjoibWFpbG1lcmdlX29uIjtzOjM6Im9mZiI7czoxMjoiZGlzcGxheV90YWJzIjthOjEwOntpOjA7czo4OiJDYWxlbmRhciI7aToxO3M6NjoiQ291cnNlIjtpOjI7czo3OiJTdHVkZW50IjtpOjM7czo3OiJUZWFjaGVyIjtpOjQ7czo4OiJDb250YWN0cyI7aTo1O3M6ODoiUmVzb3VyY2UiO2k6NjtzOjY6IkVtYWlscyI7aTo3O3M6NToiS2VpcmkiO2k6ODtzOjQ6Ik1haW4iO2k6OTtzOjc6IlNNU0hlbHAiO31zOjk6ImhpZGVfdGFicyI7YTowOnt9czoxMToicmVtb3ZlX3RhYnMiO2E6MDp7fXM6MjoidXQiO3M6MToiMSI7czoxMzoibWFpbF9mcm9tbmFtZSI7czo5OiJUZWNobm9TTVMiO3M6MTY6Im1haWxfZnJvbWFkZHJlc3MiO3M6MjQ6InRlY2hub3Ntc0B0ZWNobm9waWFuLmNvbSI7fQ==', 0, 0, 0, '9eb0fe95-e836-fa9c-1489-53e336ae90ef', 'staff', NULL),
('8dc75996-a295-678a-b2ef-53e88d7b28b6', 'web_fukuoka', 'wer5EwGzcTa9M', '145fbe4440e273a51fdbf61018f98b77', 'off', '0', NULL, '2014-08-11 18:30:42', '2014-08-11 18:30:42', '1', '1', 'Active', 'YTo4OntzOjg6ImdyaWRsaW5lIjtzOjM6Im9mZiI7czoxMjoibWFpbG1lcmdlX29uIjtzOjM6Im9mZiI7czoxMjoiZGlzcGxheV90YWJzIjthOjEwOntpOjA7czo4OiJDYWxlbmRhciI7aToxO3M6NjoiQ291cnNlIjtpOjI7czo3OiJTdHVkZW50IjtpOjM7czo3OiJUZWFjaGVyIjtpOjQ7czo4OiJDb250YWN0cyI7aTo1O3M6ODoiUmVzb3VyY2UiO2k6NjtzOjY6IkVtYWlscyI7aTo3O3M6NToiS2VpcmkiO2k6ODtzOjQ6Ik1haW4iO2k6OTtzOjc6IlNNU0hlbHAiO31zOjk6ImhpZGVfdGFicyI7YTowOnt9czoxMToicmVtb3ZlX3RhYnMiO2E6MDp7fXM6MjoidXQiO3M6MToiMSI7czoxMzoibWFpbF9mcm9tbmFtZSI7czo5OiJUZWNobm9TTVMiO3M6MTY6Im1haWxfZnJvbWFkZHJlc3MiO3M6MjQ6InRlY2hub3Ntc0B0ZWNobm9waWFuLmNvbSI7fQ==', 0, 0, 0, 'f167b9fd-1854-59eb-e0e5-53e339e1f1fa', 'staff', NULL),
('98a8ec8a-a149-6c87-7a1f-53e88cad23dc', 'web_ashiya', 'wer5EwGzcTa9M', '145fbe4440e273a51fdbf61018f98b77', 'off', '0', NULL, '2014-08-11 18:28:45', '2014-08-11 18:28:45', '1', '1', 'Active', 'YTo4OntzOjg6ImdyaWRsaW5lIjtzOjM6Im9mZiI7czoxMjoibWFpbG1lcmdlX29uIjtzOjM6Im9mZiI7czoxMjoiZGlzcGxheV90YWJzIjthOjEwOntpOjA7czo4OiJDYWxlbmRhciI7aToxO3M6NjoiQ291cnNlIjtpOjI7czo3OiJTdHVkZW50IjtpOjM7czo3OiJUZWFjaGVyIjtpOjQ7czo4OiJDb250YWN0cyI7aTo1O3M6ODoiUmVzb3VyY2UiO2k6NjtzOjY6IkVtYWlscyI7aTo3O3M6NToiS2VpcmkiO2k6ODtzOjQ6Ik1haW4iO2k6OTtzOjc6IlNNU0hlbHAiO31zOjk6ImhpZGVfdGFicyI7YTowOnt9czoxMToicmVtb3ZlX3RhYnMiO2E6MDp7fXM6MjoidXQiO3M6MToiMSI7czoxMzoibWFpbF9mcm9tbmFtZSI7czo5OiJUZWNobm9TTVMiO3M6MTY6Im1haWxfZnJvbWFkZHJlc3MiO3M6MjQ6InRlY2hub3Ntc0B0ZWNobm9waWFuLmNvbSI7fQ==', 0, 0, 0, '632708e4-a3bb-52b6-fb4e-53e3386b7775', 'staff', NULL),
('a66f1182-9e93-ae1b-b1fe-53e88b51f621', 'web_shinjuku', 'wer5EwGzcTa9M', '145fbe4440e273a51fdbf61018f98b77', 'off', '0', NULL, '2014-08-11 18:21:37', '2014-08-11 18:21:37', '1', '1', 'Active', 'YTo4OntzOjg6ImdyaWRsaW5lIjtzOjM6Im9mZiI7czoxMjoibWFpbG1lcmdlX29uIjtzOjM6Im9mZiI7czoxMjoiZGlzcGxheV90YWJzIjthOjEwOntpOjA7czo4OiJDYWxlbmRhciI7aToxO3M6NjoiQ291cnNlIjtpOjI7czo3OiJTdHVkZW50IjtpOjM7czo3OiJUZWFjaGVyIjtpOjQ7czo4OiJDb250YWN0cyI7aTo1O3M6ODoiUmVzb3VyY2UiO2k6NjtzOjY6IkVtYWlscyI7aTo3O3M6NToiS2VpcmkiO2k6ODtzOjQ6Ik1haW4iO2k6OTtzOjc6IlNNU0hlbHAiO31zOjk6ImhpZGVfdGFicyI7YTowOnt9czoxMToicmVtb3ZlX3RhYnMiO2E6MDp7fXM6MjoidXQiO3M6MToiMSI7czoxMzoibWFpbF9mcm9tbmFtZSI7czo5OiJUZWNobm9TTVMiO3M6MTY6Im1haWxfZnJvbWFkZHJlc3MiO3M6MjQ6InRlY2hub3Ntc0B0ZWNobm9waWFuLmNvbSI7fQ==', 0, 0, 0, '39a01a9e-4a2b-97a1-96df-53e335b65c30', 'staff', NULL),
('b228c651-a9a8-1920-5fe1-53e88c7d3c0f', 'web_kuzuha', 'wer5EwGzcTa9M', '145fbe4440e273a51fdbf61018f98b77', 'off', '0', NULL, '2014-08-11 18:27:43', '2014-08-11 18:27:43', '1', '1', 'Active', 'YTo4OntzOjg6ImdyaWRsaW5lIjtzOjM6Im9mZiI7czoxMjoibWFpbG1lcmdlX29uIjtzOjM6Im9mZiI7czoxMjoiZGlzcGxheV90YWJzIjthOjEwOntpOjA7czo4OiJDYWxlbmRhciI7aToxO3M6NjoiQ291cnNlIjtpOjI7czo3OiJTdHVkZW50IjtpOjM7czo3OiJUZWFjaGVyIjtpOjQ7czo4OiJDb250YWN0cyI7aTo1O3M6ODoiUmVzb3VyY2UiO2k6NjtzOjY6IkVtYWlscyI7aTo3O3M6NToiS2VpcmkiO2k6ODtzOjQ6Ik1haW4iO2k6OTtzOjc6IlNNU0hlbHAiO31zOjk6ImhpZGVfdGFicyI7YTowOnt9czoxMToicmVtb3ZlX3RhYnMiO2E6MDp7fXM6MjoidXQiO3M6MToiMSI7czoxMzoibWFpbF9mcm9tbmFtZSI7czo5OiJUZWNobm9TTVMiO3M6MTY6Im1haWxfZnJvbWFkZHJlc3MiO3M6MjQ6InRlY2hub3Ntc0B0ZWNobm9waWFuLmNvbSI7fQ==', 0, 0, 0, 'b49ce0c7-499c-fb09-fbd7-53e338a9805d', 'staff', NULL),
('b9ee82a9-f280-8a73-5886-53e88b559028', 'web_shonan', 'wer5EwGzcTa9M', '145fbe4440e273a51fdbf61018f98b77', 'off', '0', NULL, '2014-08-11 18:24:45', '2014-08-11 18:24:45', '1', '1', 'Active', 'YTo4OntzOjg6ImdyaWRsaW5lIjtzOjM6Im9mZiI7czoxMjoibWFpbG1lcmdlX29uIjtzOjM6Im9mZiI7czoxMjoiZGlzcGxheV90YWJzIjthOjEwOntpOjA7czo4OiJDYWxlbmRhciI7aToxO3M6NjoiQ291cnNlIjtpOjI7czo3OiJTdHVkZW50IjtpOjM7czo3OiJUZWFjaGVyIjtpOjQ7czo4OiJDb250YWN0cyI7aTo1O3M6ODoiUmVzb3VyY2UiO2k6NjtzOjY6IkVtYWlscyI7aTo3O3M6NToiS2VpcmkiO2k6ODtzOjQ6Ik1haW4iO2k6OTtzOjc6IlNNU0hlbHAiO31zOjk6ImhpZGVfdGFicyI7YTowOnt9czoxMToicmVtb3ZlX3RhYnMiO2E6MDp7fXM6MjoidXQiO3M6MToiMSI7czoxMzoibWFpbF9mcm9tbmFtZSI7czo5OiJUZWNobm9TTVMiO3M6MTY6Im1haWxfZnJvbWFkZHJlc3MiO3M6MjQ6InRlY2hub3Ntc0B0ZWNobm9waWFuLmNvbSI7fQ==', 0, 0, 0, '687e9e1d-4dbf-da43-b77c-53e3363da5f1', 'staff', NULL),
('bc323d37-bf83-642f-890b-53e88c8c3dfb', 'web_ssaporo', 'wer5EwGzcTa9M', '145fbe4440e273a51fdbf61018f98b77', 'off', '0', NULL, '2014-08-11 18:25:43', '2014-08-11 18:25:43', '1', '1', 'Active', 'YTo4OntzOjg6ImdyaWRsaW5lIjtzOjM6Im9mZiI7czoxMjoibWFpbG1lcmdlX29uIjtzOjM6Im9mZiI7czoxMjoiZGlzcGxheV90YWJzIjthOjEwOntpOjA7czo4OiJDYWxlbmRhciI7aToxO3M6NjoiQ291cnNlIjtpOjI7czo3OiJTdHVkZW50IjtpOjM7czo3OiJUZWFjaGVyIjtpOjQ7czo4OiJDb250YWN0cyI7aTo1O3M6ODoiUmVzb3VyY2UiO2k6NjtzOjY6IkVtYWlscyI7aTo3O3M6NToiS2VpcmkiO2k6ODtzOjQ6Ik1haW4iO2k6OTtzOjc6IlNNU0hlbHAiO31zOjk6ImhpZGVfdGFicyI7YTowOnt9czoxMToicmVtb3ZlX3RhYnMiO2E6MDp7fXM6MjoidXQiO3M6MToiMSI7czoxMzoibWFpbF9mcm9tbmFtZSI7czo5OiJUZWNobm9TTVMiO3M6MTY6Im1haWxfZnJvbWFkZHJlc3MiO3M6MjQ6InRlY2hub3Ntc0B0ZWNobm9waWFuLmNvbSI7fQ==', 0, 0, 0, '65ae0734-18c3-7a4d-caa0-53e3370b7492', 'staff', NULL),
('f174edcd-b92f-2a8e-c79e-53e88d9b0a57', 'web_nagoya', 'wer5EwGzcTa9M', '145fbe4440e273a51fdbf61018f98b77', 'off', '0', NULL, '2014-08-11 18:31:18', '2014-08-11 18:31:18', '1', '1', 'Active', 'YTo4OntzOjg6ImdyaWRsaW5lIjtzOjM6Im9mZiI7czoxMjoibWFpbG1lcmdlX29uIjtzOjM6Im9mZiI7czoxMjoiZGlzcGxheV90YWJzIjthOjEwOntpOjA7czo4OiJDYWxlbmRhciI7aToxO3M6NjoiQ291cnNlIjtpOjI7czo3OiJTdHVkZW50IjtpOjM7czo3OiJUZWFjaGVyIjtpOjQ7czo4OiJDb250YWN0cyI7aTo1O3M6ODoiUmVzb3VyY2UiO2k6NjtzOjY6IkVtYWlscyI7aTo3O3M6NToiS2VpcmkiO2k6ODtzOjQ6Ik1haW4iO2k6OTtzOjc6IlNNU0hlbHAiO31zOjk6ImhpZGVfdGFicyI7YTowOnt9czoxMToicmVtb3ZlX3RhYnMiO2E6MDp7fXM6MjoidXQiO3M6MToiMSI7czoxMzoibWFpbF9mcm9tbmFtZSI7czo5OiJUZWNobm9TTVMiO3M6MTY6Im1haWxfZnJvbWFkZHJlc3MiO3M6MjQ6InRlY2hub3Ntc0B0ZWNobm9waWFuLmNvbSI7fQ==', 0, 0, 0, '5e8dbbf6-618f-34d1-7b61-53e339d9db1c', 'staff', NULL);

-- 2014-08-12 kato
-- WEB用の講座申込日
ALTER TABLE `yoyaku_event_contacts` ADD COLUMN `order_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

-- 2014-08-27 mon
-- new table teacher_chosho_message
 INSERT INTO teacher_chosho_message SET id = 'eae147ca-5c77-5e10-3fa9-53fd5ee3cea0'; 
 
-- 2014-09-04 shimoju
-- 謝礼系テーブル、前受け金、アカウント　Index作成

 ALTER TABLE `sharei_hibetsu` ADD INDEX ( `teacher_id` ); 
 ALTER TABLE `sharei_hibetsu` ADD INDEX ( `t_idnumber` ); 
 ALTER TABLE `sharei_hibetsu` ADD INDEX ( `h_idnumber` ); 
 ALTER TABLE `sharei_hibetsu` ADD INDEX ( `course_id` ); 
 ALTER TABLE `sharei_hibetsu` ADD INDEX ( `account_id` ); 

 ALTER TABLE `sharei_koushi` ADD INDEX ( `teacher_id` ); 
 ALTER TABLE `sharei_koushi` ADD INDEX ( `t_idnumber` ); 
 ALTER TABLE `sharei_koushi` ADD INDEX ( `h_idnumber` ); 
 ALTER TABLE `sharei_koushi` ADD INDEX ( `course_id` ); 
 ALTER TABLE `sharei_koushi` ADD INDEX ( `account_id` ); 

 ALTER TABLE `sharei_kouza` ADD INDEX ( `teacher_id` ); 
 ALTER TABLE `sharei_kouza` ADD INDEX ( `t_idnumber` ); 
 ALTER TABLE `sharei_kouza` ADD INDEX ( `h_idnumber` ); 
 ALTER TABLE `sharei_kouza` ADD INDEX ( `course_id` ); 
 ALTER TABLE `sharei_kouza` ADD INDEX ( `account_id` ); 

 ALTER TABLE `accounts` ADD INDEX ( `idnumber` ) ;
 
 ALTER TABLE `maeukekin` ADD INDEX ( `student_id` ) ;
 ALTER TABLE `maeukekin` ADD INDEX ( `course_id` ) ;
 ALTER TABLE `maeukekin` ADD INDEX ( `fee_id` ) ;
 ALTER TABLE maeukekin ADD INDEX ( school_id ); 
 ALTER TABLE maeukekin ADD INDEX ( keijobi ); 
 ALTER TABLE maeukekin ADD INDEX ( nyukinbi ); 
 
-- 2014-09-16 kato
--ワークスペースのindex  
ALTER TABLE `yoyaku_basic` ADD INDEX ( `active_student_id` ) ;
ALTER TABLE `yoyaku_basic` ADD INDEX ( `daihyo_id` ) ;
ALTER TABLE `yoyaku_event_contacts` ADD PRIMARY KEY ( `id` ) ;
ALTER TABLE `yoyaku_event_contacts` ADD INDEX ( `event_id` ) ;
ALTER TABLE `yoyaku_event_contacts` ADD INDEX ( `contact_id` ) ;
ALTER TABLE `yoyaku_fees` ADD PRIMARY KEY ( `id` ) ;
ALTER TABLE `yoyaku_fees` ADD INDEX ( `school_id` ) ;
ALTER TABLE `yoyaku_fees` ADD INDEX ( `event_id` ) ;
ALTER TABLE `yoyaku_fees` ADD INDEX ( `contact_id` ) ;
ALTER TABLE `yoyaku_fees` ADD INDEX ( `internal_yoyaku_id` ) ;
ALTER TABLE `yoyaku_fees_sub1` ADD PRIMARY KEY ( `id_sub1` ) ;
ALTER TABLE `yoyaku_fees_sub1` ADD INDEX ( `yoyaku_school_id` ) ;
ALTER TABLE `yoyaku_transactions` ADD PRIMARY KEY ( `id` ) ;
ALTER TABLE `yoyaku_transactions` ADD INDEX ( `contact_id` ) ;
ALTER TABLE `yoyaku_transactions` ADD INDEX ( `fee_id` ) ;
ALTER TABLE `yoyaku_transactions` ADD INDEX ( `event_id` ) ;
ALTER TABLE `yoyaku_transactions` ADD INDEX ( `salesno` ) ;
ALTER TABLE `yoyaku_transactions` ADD INDEX ( `school_id` ) ;
ALTER TABLE `yoyaku_transactions` ADD INDEX ( `internal_yoyaku_id` ) ;

ALTER TABLE `contacts` ADD INDEX ( `tanmatsu_id` ) ;
ALTER TABLE `users` ADD INDEX ( `contact_id` ) ;
ALTER TABLE `resource` ADD INDEX ( `tanmatsu_category` ) ;

-- 2014-10-10 kato
-- 予約取消一覧で表示する受付番号
ALTER TABLE `yoyaku_event_contacts` ADD COLUMN `torikeshi_uketsukebango` varchar(12) DEFAULT NULL;

-- 2014-10-17 kato
-- indexの整理
DROP INDEX description ON contacts;
DROP INDEX idx_name ON smsevent;
DROP INDEX idx_name_furigana ON smsevent;
CREATE INDEX actual_event_start ON sms_recurrence (actual_event_start);
CREATE INDEX actual_event_end ON sms_recurrence (actual_event_end);
CREATE INDEX master_group ON master_kanri (master_group);
CREATE INDEX school_id ON master_kanri (school_id);
CREATE INDEX master_category1 ON master_kanri (master_category1);
CREATE INDEX master_category2 ON master_kanri (master_category2);
CREATE INDEX tearcher_fromdate ON sharei_koushi (teacher_id,from_datetime);

-- 2014-12-19 Mon 
-- For bug 55564 
INSERT INTO `teacher_meisai_message` (`id`)
VALUES
('accd97c5-9707-d199-1f5e-5229871c7b59');


-- 上記まで　honban, test, test2, training, koza, import で実施済み　2014-12-19
-- All SQL avobe have done update in DB of honban, test, test2, training, koza, import 2014-12-19

-- 2015-01-02 contacts email_opt_out setting
-- 本番のみで実行、他の環境では必要ない
SELECT id, email_opt_out FROM contacts 
WHERE CHAR_LENGTH( id ) < 30 AND smsgroup='student'

SELECT email_opt_out, count(id) FROM contacts 
WHERE CHAR_LENGTH( id ) < 30 AND smsgroup='student'
GROUP BY email_opt_out

UPDATE contacts SET email_opt_out=2 WHERE CHAR_LENGTH( id ) < 30 AND smsgroup='student' AND email_opt_out='0';
UPDATE contacts SET email_opt_out=0 WHERE CHAR_LENGTH( id ) < 30 AND smsgroup='student' AND email_opt_out='1';
UPDATE contacts SET email_opt_out=1 WHERE CHAR_LENGTH( id ) < 30 AND smsgroup='student' AND email_opt_out='2';

-- 2015-01-04 
SELECT contact_id, event_id, nenki, common_code, smsevent.name as koza_name, ct_dankai1_start, ct_dankai1_end
FROM smsevent, smsevent_sub1, event_contacts, event_contacts_sub1
WHERE event_contacts.id=event_contacts_sub1.id_sub1
	AND smsevent.id=smsevent_sub1.id_sub1 AND smsevent.id=event_contacts.event_id
	AND event_contacts.deleted=0 AND smsevent.deleted=0
	AND ct_keiyaku_keitai='3' AND ct_dankai1_start =0 AND ct_dankai1_end IS NOT NULL
	
-- 2015-02-12 Yahya
-- honban, testで実施済み、他の環境では必要ない　2015-2-18
-- SQL below have done update in DB of honban, test No need to run on the others 2015-2-18
 ALTER TABLE `maeukekin` ADD INDEX ( `bumon_code` ) ;
  ALTER TABLE `maeukekin` ADD INDEX ( `nenki` ) ;
  
-- 2015-04-30

INSERT INTO `sms_salesgroup` (`id`, `name`, `event_id`, `contact_id`, `account_id`, `type_id`, `occurences`, `occurences_hour`, `deleted`, `date_entered`, `date_modified`, `sell_date`, `modified_user_id`, `created_by`, `basic_fee`, `unit_type`, `unit_no`, `period`, `period_no`, `due_to`, `description`, `applied_contact`, `discount_percentage`, `discount_price`, `salesno`, `bank_account`, `hakkoubi`, `receipt_hakkoubi`, `seikyusaki`, `tantosha`, `item`, `school_code`, `internal_yoyaku_id`, `daihyo_id`, `uketsuke_bango`, `inshizei_umu`) VALUES
('32d0-f68f-72ea-552f4f65d660', NULL, NULL, 'd20450cf-d82f-39c9-76ae-552f4fc3fc12', NULL, NULL, 1, 1, 0, '2015-04-24 14:58:08', '2015-04-24 14:58:08', '0000-00-00 00:00:00', 's_925', 's_925', 0, NULL, 0, 0, 0, '0', NULL, 0, 0, NULL, '0100047383', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '', 0);
INSERT INTO `sms_fee` (`id`, `school_id`, `name`, `event_id`, `contact_id`, `account_id`, `type_id`, `deleted`, `date_entered`, `date_modified`, `sell_date`, `modified_user_id`, `created_by`, `basic_fee`, `unit_type`, `unit_no`, `period`, `period_no`, `due_to`, `description`, `applied_contact`, `discount_percentage`, `discount_price`, `salesno`, `start_date`, `end_date`, `product_id`, `product_count`, `ticket_count`, `ticket_valid`, `jikyu_rate`, `event_credit`, `fee_subcategory`, `source_gessha_id`, `next_gessha_applied`, `internal_yoyaku_id`, `nyukin_zumi`, `fee_payment_type`, `total_paid`, `moto_payment_type`) VALUES
('e25a-834e-be4e-552f4fd0c17f', 'sssssss-1111', '受講料', '759709ff-ea5c-187d-7dde-54d06abbb9d8', 'd20450cf-d82f-39c9-76ae-552f4fc3fc12', NULL, '29', 0, '2015-04-24 14:58:08', '2015-04-24 14:58:08', '2015-04-24 00:00:00', 's_919', 's_919', -4645, '0', 1, 1, '0', '180', NULL, 1, 0, NULL, '0100047383', '2015-04-25', '2015-04-25', NULL, NULL, NULL, NULL, 0, 0, '受講料', NULL, NULL, 'f95e-99a5-ae76-55348441debe', 0, NULL, 0, 'combini');
INSERT INTO `sms_fee_sub1` (`id_sub1`, `reigaishori_id`, `tanka`, `suryo`, `discount_id`, `yoyaku_school_id`, `zeinuki_kakaku`, `fee_shokubun`, `henkin_riyu`) VALUES
('e25a-834e-be4e-552f4fd0c17f', NULL, 4645, NULL, NULL, 'sssssss-1111', -4300, 1, '4');
INSERT INTO `sms_transaction` (`id`, `fee_id`, `type_id`, `document_id`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `sell_date`, `due_date`, `t_date`, `in_amount`, `out_amount`, `actual_in_amount`, `actual_out_amount`, `commission`, `actual`, `contact_id`, `account_id`, `event_id`, `payment_type`, `bank_account_id`, `description`, `salesno`, `combini_sakusei_id`, `fee_name`, `school_id`, `internal_yoyaku_id`, `nyukin_tanmatsu_id`, `nyukin_school_id`, `moto_payment_type`, `furikomibi`, `kakuhobi`) VALUES
('6ebd-629c-6087-552f4ffa0af0', 'e25a-834e-be4e-552f4fd0c17f', '29', NULL, 0, '2015-04-24 14:58:08', '2015-04-24 14:58:08', 's_919', 's_919', '2015-04-24 00:00:00', '2015-05-29 00:00:00', NULL, -4645, 0, 0, 0, 0, 0, 'd20450cf-d82f-39c9-76ae-552f4fc3fc12', NULL, '759709ff-ea5c-187d-7dde-54d06abbb9d8', NULL, NULL, '受講料', '0100047383', NULL, '受講料', 'sssssss-1111', NULL, NULL, NULL, NULL, NULL, NULL);

-- 2015-05-01
SELECT * FROM `maeukekin` WHERE `fee_id` LIKE '41c87426-4cbf-2ff2-1edf-552c86ca2d0b'  -- deleted 4/25 record
SELECT * FROM `maeukekin` WHERE `fee_id` LIKE '98568acf-5c71-03ca-ae5c-5506580f9aaf'  -- deleted 4/6 record
SELECT * FROM `maeukekin` WHERE `fee_id` LIKE 'c60ec607-a5fb-e437-8c10-550bc8e1c8b2' -- deleted 4/6 record
SELECT * FROM `maeukekin` WHERE `fee_id` LIKE '9c1fee6d-c50b-faac-1c82-550b96e8e663'  -- deleted 4/6 record
UPDATE `maeukekin` SET `kingaku` = '159' WHERE CONVERT( `maeukekin`.`student_id` USING utf8 ) = '1386943' AND CONVERT( `maeukekin`.`course_id` USING utf8 ) = 'c9c31f75-7b47-5255-cafa-54d1d6943a8e' AND CONVERT( `maeukekin`.`school_id` USING utf8 ) = 'b0845182-f1bc-145f-0560-5001d0cbee2a' AND CONVERT( `maeukekin`.`bumon_code` USING utf8 ) = '05' AND `maeukekin`.`nenki` =201504 AND CONVERT( `maeukekin`.`common_code` USING utf8 ) = '532001200' AND `maeukekin`.`nyukinbi` = '2015-03-18' AND `maeukekin`.`keijobi` = '2015-04-15' AND CONVERT( `maeukekin`.`fee_subcategory` USING utf8 ) = 't' AND `maeukekin`.`kingaku` =157 AND CONVERT( `maeukekin`.`fee_id` USING utf8 ) = '400cd822-e2e5-73c0-3dac-550944afbb4c' AND `maeukekin`.`kaisu` =1 AND `maeukekin`.`date_modified` = '2015-05-01 01:17:07' LIMIT 1 ;
UPDATE `maeukekin` SET `kingaku` = '159' WHERE CONVERT( `maeukekin`.`student_id` USING utf8 ) = '1480492' AND CONVERT( `maeukekin`.`course_id` USING utf8 ) = 'c9c31f75-7b47-5255-cafa-54d1d6943a8e' AND CONVERT( `maeukekin`.`school_id` USING utf8 ) = 'b0845182-f1bc-145f-0560-5001d0cbee2a' AND CONVERT( `maeukekin`.`bumon_code` USING utf8 ) = '05' AND `maeukekin`.`nenki` =201504 AND CONVERT( `maeukekin`.`common_code` USING utf8 ) = '532001200' AND `maeukekin`.`nyukinbi` = '2015-03-23' AND `maeukekin`.`keijobi` = '2015-04-15' AND CONVERT( `maeukekin`.`fee_subcategory` USING utf8 ) = 't' AND `maeukekin`.`kingaku` =157 AND CONVERT( `maeukekin`.`fee_id` USING utf8 ) = '201f98b7-6b80-44fb-e586-55100125789f' AND `maeukekin`.`kaisu` =1 AND `maeukekin`.`date_modified` = '2015-05-01 01:17:07' LIMIT 1 ;
UPDATE `maeukekin` SET `kingaku` = '159' WHERE CONVERT( `maeukekin`.`student_id` USING utf8 ) = '177760' AND CONVERT( `maeukekin`.`course_id` USING utf8 ) = 'c9c31f75-7b47-5255-cafa-54d1d6943a8e' AND CONVERT( `maeukekin`.`school_id` USING utf8 ) = 'b0845182-f1bc-145f-0560-5001d0cbee2a' AND CONVERT( `maeukekin`.`bumon_code` USING utf8 ) = '05' AND `maeukekin`.`nenki` =201504 AND CONVERT( `maeukekin`.`common_code` USING utf8 ) = '532001200' AND `maeukekin`.`nyukinbi` = '2015-03-18' AND `maeukekin`.`keijobi` = '2015-04-15' AND CONVERT( `maeukekin`.`fee_subcategory` USING utf8 ) = 't' AND `maeukekin`.`kingaku` =157 AND CONVERT( `maeukekin`.`fee_id` USING utf8 ) = 'a79976d5-4414-5308-77a4-5509459d75b1' AND `maeukekin`.`kaisu` =1 AND `maeukekin`.`date_modified` = '2015-05-01 01:17:07' LIMIT 1 ;

SELECT * FROM `maeukekin` WHERE `fee_id` LIKE '1c4bed21-a5c5-8572-08c6-54f6ccf97169'  -- hanbun deleted
SELECT * FROM `maeukekin` WHERE `fee_id` LIKE 'df6478e2-f11d-cc4c-60d1-54f6d4cc2cff'  -- hanbun deleted
SELECT * FROM `maeukekin` WHERE `fee_id` LIKE 'f3b306eb-bdd2-3aff-8c5c-54fe9351a7d5' -- deleted last one
SELECT * FROM `maeukekin` WHERE `fee_id` LIKE '53ebd871-dabd-ddc6-f8dc-5521f4309679'  -- 5/6 & 6/17 deleted
SELECT * FROM `maeukekin` WHERE `fee_id` LIKE '203807a5-33c7-7020-ffae-5521f4342158'  -- 5/6 & 6/17 deleted


INSERT INTO `maeukekin` (`student_id`, `course_id`, `school_id`, `bumon_code`, `nenki`, `common_code`, `nyukinbi`, `keijobi`, `fee_subcategory`, `kingaku`, `fee_id`, `kaisu`, `date_modified`) VALUES
('1001850', '543395e2-b7f7-747d-4785-54cc7b6b72e9', 'sssssss-1111', '05', 201504, '507200600', '2015-04-30', '2015-05-30', 'j', -3564, '8cc38211-8a82-ab1e-f017-5541b4414f37', -1, '2015-05-01 01:41:07');

-- 2015-05-22

INSERT INTO `sms_salesgroup` (`id`, `name`, `event_id`, `contact_id`, `account_id`, `type_id`, `occurences`, `occurences_hour`, `deleted`, `date_entered`, `date_modified`, `sell_date`, `modified_user_id`, `created_by`, `basic_fee`, `unit_type`, `unit_no`, `period`, `period_no`, `due_to`, `description`, `applied_contact`, `discount_percentage`, `discount_price`, `salesno`, `bank_account`, `hakkoubi`, `receipt_hakkoubi`, `seikyusaki`, `tantosha`, `item`, `school_code`, `internal_yoyaku_id`, `daihyo_id`, `uketsuke_bango`, `inshizei_umu`) VALUES
('4d3d-3835-4813-5541c0154f64', NULL, NULL, '1511201', NULL, NULL, 1, 1, 0, '2015-05-22 13:39:39', '2015-05-22 13:39:39', '0000-00-00 00:00:00', 's_924', 's_924', 0, NULL, 0, 0, 0, '0', NULL, 0, 0, NULL, '0100051455', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '05b0-447c-ddc3-5552d5d15f11', '1511201', NULL, 0);

INSERT INTO `sms_fee` (`id`, `school_id`, `name`, `event_id`, `contact_id`, `account_id`, `type_id`, `deleted`, `date_entered`, `date_modified`, `sell_date`, `modified_user_id`, `created_by`, `basic_fee`, `unit_type`, `unit_no`, `period`, `period_no`, `due_to`, `description`, `applied_contact`, `discount_percentage`, `discount_price`, `salesno`, `start_date`, `end_date`, `product_id`, `product_count`, `ticket_count`, `ticket_valid`, `jikyu_rate`, `event_credit`, `fee_subcategory`, `source_gessha_id`, `next_gessha_applied`, `internal_yoyaku_id`, `nyukin_zumi`, `fee_payment_type`, `total_paid`, `moto_payment_type`) VALUES
('984a-c4d9-30f9-5541c0b0beed', 'sssssss-1111', '受講料', '34d2efcd-ef76-8f3f-a3b2-54cc717991e3', '1511201', NULL, '29', 0, '2015-05-22 13:39:39', '2015-05-22 13:39:39', '2015-05-22 00:00:00', 's_919', 's_919', -5292, '0', 1, 1, '0', '180', NULL, 1, 0, NULL, '0100051455', '2015-05-15', '2015-05-15', NULL, NULL, NULL, NULL, 0, 0, '受講料', NULL, NULL, '05b0-447c-ddc3-5552d5d15f11', 0, NULL, 0, 'combini');

INSERT INTO `sms_fee_sub1` (`id_sub1`, `reigaishori_id`, `tanka`, `suryo`, `discount_id`, `yoyaku_school_id`, `zeinuki_kakaku`, `fee_shokubun`, `henkin_riyu`) VALUES
('984a-c4d9-30f9-5541c0b0beed', NULL, 5292, NULL, NULL, 'sssssss-1111', -4900, 1, 4);

INSERT INTO `sms_transaction` (`id`, `fee_id`, `type_id`, `document_id`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `sell_date`, `due_date`, `t_date`, `in_amount`, `out_amount`, `actual_in_amount`, `actual_out_amount`, `commission`, `actual`, `contact_id`, `account_id`, `event_id`, `payment_type`, `bank_account_id`, `description`, `salesno`, `combini_sakusei_id`, `fee_name`, `school_id`, `internal_yoyaku_id`, `nyukin_tanmatsu_id`, `nyukin_school_id`, `moto_payment_type`, `furikomibi`, `kakuhobi`) VALUES
('82d7-561f-1c53-5541c08e37ed', '984a-c4d9-30f9-5541c0b0beed', '29', NULL, 0, '2015-05-22 13:39:39', '2015-05-22 13:39:39', 's_919', 's_919', '2015-05-22 00:00:00', '2015-11-22 00:00:00', NULL, -5292, 0, 0, 0, 0, 0, '1511201', NULL, '34d2efcd-ef76-8f3f-a3b2-54cc717991e3', NULL, NULL, '受講料', '0100051455', NULL, '受講料', 'sssssss-1111', '05b0-447c-ddc3-5552d5d15f11', NULL, NULL, NULL, NULL, NULL);

-- 2015-05-30 maeukekin chousei

INSERT INTO `maeukekin` (`student_id`, `course_id`, `school_id`, `bumon_code`, `nenki`, `common_code`, `nyukinbi`, `keijobi`, `fee_subcategory`, `kingaku`, `fee_id`, `kaisu`, `date_modified`) VALUES
('826a317c-fbb7-840b-69ea-54f92578dfc3', '6452e262-ae99-ea1d-8506-54c0ae7b0b61', '49caa19d-9872-3095-6bd4-5001d0aa692d', '03', 201504, '357200100', '2015-04-20', '2015-05-30', 'j', 7128, 'chousei_ba364d16-02ca-709c-52c5-5534', 2, '2015-06-01 10:00:00');
INSERT INTO `maeukekin` (`student_id`, `course_id`, `school_id`, `bumon_code`, `nenki`, `common_code`, `nyukinbi`, `keijobi`, `fee_subcategory`, `kingaku`, `fee_id`, `kaisu`, `date_modified`) VALUES
('875430', 'd230f5a1-cb45-6f96-35fe-54c6f73a5d75', '49caa19d-9872-3095-6bd4-5001d0aa692d', '03', 201504, '360200400', '2015-04-11', '2015-05-30', 'j', 3240, 'chousei_c7e36cf0-8630-f10d-c809-5528', 1, '2015-06-01 10:00:00');

-- 2015-06-01 furikomi data shusei
UPDATE `sharei_kouza` SET furikomibi='2015-05-15' WHERE  `date_modified` =  '2015-04-30 23:59:59' AND  `furikomibi` =  '2015-06-15'

-- 2015-06-24 schedule update
INSERT INTO `smsevent_instances` (`id`, `date_modified`, `event_id`, `event_type`, `instance_id`, `target_id`, `category`, `from_datetime`, `to_datetime`, `status`, `from_instance_id`, `to_instance_id`, `attendance_status`, `instance_contact_status`, `name`, `description`, `early_late_time2`, `early_late_time3`, `attendance_memo`) VALUES
('55f3033c-4a42-62ae-54e2a9af2f0d', '2015-03-18 11:33:46', '1b8d0a5c-662a-1b4d-8ba8-54cf1642b015', 'course', 'af59e52f-2702-d243-dfa4-5563df80de52', 'eec94cef-eed9-c277-1e11-54e2a36c5bb3', 'student', '2015-07-03 10:30:00', '2015-07-03 14:30:00', 17, '', '', NULL, 2, NULL, '', 0, 0, NULL);

-- 2015-06-24 fee addition
INSERT INTO `sms_fee` (`id`, `school_id`, `name`, `event_id`, `contact_id`, `account_id`, `type_id`, `deleted`, `date_entered`, `date_modified`, `sell_date`, `modified_user_id`, `created_by`, `basic_fee`, `unit_type`, `unit_no`, `period`, `period_no`, `due_to`, `description`, `applied_contact`, `discount_percentage`, `discount_price`, `salesno`, `start_date`, `end_date`, `product_id`, `product_count`, `ticket_count`, `ticket_valid`, `jikyu_rate`, `event_credit`, `fee_subcategory`, `source_gessha_id`, `next_gessha_applied`, `internal_yoyaku_id`, `nyukin_zumi`, `fee_payment_type`, `total_paid`, `moto_payment_type`) VALUES
('c9e04519-cc61-e4de-5589ff23095f', 'sssssss-1111', '入会金・更新(個人)', NULL, '1416379', NULL, '29', 0, '2015-06-24 09:51:39', '2015-06-24 09:52:25', '2015-06-24 00:00:00', 's_916', 's_916', -0, '0', 1, 1, '0', '180', NULL, 1, 0, NULL, '0100062352', '2015-06-24', NULL, NULL, NULL, NULL, NULL, 0, 0, '入会金・更新', NULL, NULL, '71f18ab4-f35e-b0e6-e0b6-5589ff7be17a', 0, 'credit', 0, NULL);
INSERT INTO `sms_fee_sub1` (`id_sub1`, `reigaishori_id`, `tanka`, `suryo`, `discount_id`, `yoyaku_school_id`, `zeinuki_kakaku`, `fee_shokubun`, `henkin_riyu`) VALUES
('c9e04519-cc61-e4de-5589ff23095f', NULL, NULL, NULL, '5001', 'sssssss-1111', -0, NULL, NULL);
INSERT INTO `sms_transaction` (`id`, `fee_id`, `type_id`, `document_id`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `sell_date`, `due_date`, `t_date`, `in_amount`, `out_amount`, `actual_in_amount`, `actual_out_amount`, `commission`, `actual`, `contact_id`, `account_id`, `event_id`, `payment_type`, `bank_account_id`, `description`, `salesno`, `combini_sakusei_id`, `fee_name`, `school_id`, `internal_yoyaku_id`, `nyukin_tanmatsu_id`, `nyukin_school_id`, `moto_payment_type`, `furikomibi`, `kakuhobi`) VALUES
('70a342d4-27bb-e6ed-5589ff79c28a', 'c9e04519-cc61-e4de-5589ff23095f', '29', NULL, 0, '2015-06-24 09:52:25', '2015-06-24 09:52:25', 's_916', 's_916', '2015-06-24 00:00:00', '2015-12-21 00:00:00', '2015-06-24 09:52:25', 0, 0, 0, 0, 0, 0, '1416379', NULL, NULL, 'credit', NULL, '入会金・更新(個人)', '0100062352', NULL, '入会金・更新(個人)', 'sssssss-1111', '71f18ab4-f35e-b0e6-e0b6-5589ff7be17a', 'd417f2e1-bdd0-de26-48c7-5454bf222381', 'sssssss-1111', NULL, NULL, NULL);


-- 2015-06-26 schedule addition
INSERT INTO `smsevent_instances` (`id`, `date_modified`, `event_id`, `event_type`, `instance_id`, `target_id`, `category`, `from_datetime`, `to_datetime`, `status`, `from_instance_id`, `to_instance_id`, `attendance_status`, `instance_contact_status`, `name`, `description`, `early_late_time2`, `early_late_time3`, `attendance_memo`) VALUES
('35fe02f1-34bd-dd72-5562b9b15b09', '2015-05-25 14:56:39', '36d7ee93-a726-411e-04fc-553ee04bc0cb', 'course', '86deba3d-7e61-b02c-3ad0-553ee0b9c355', '1197868', 'student', '2015-07-13 13:00:00', '2015-07-13 14:30:00', 1, '', '', NULL, 2, NULL, '', 0, 0, NULL),
('36a20c91-b01e-6822-5562b9f439b9', '2015-05-25 14:56:39', '36d7ee93-a726-411e-04fc-553ee04bc0cb', 'course', '86f0a037-25a9-470f-a96a-553ee0aecd97', '1197868', 'student', '2015-08-24 13:00:00', '2015-08-24 14:30:00', 1, '', '', NULL, 2, NULL, '', 0, 0, NULL),
('372009ee-3046-3dd6-5562b99aba19', '2015-05-25 14:56:39', '36d7ee93-a726-411e-04fc-553ee04bc0cb', 'course', '870212b9-e1f6-703a-eea1-553ee064b753', '1197868', 'student', '2015-09-14 13:00:00', '2015-09-14 14:30:00', 1, '', '', NULL, 2, NULL, '', 0, 0, NULL);

-- 2015-07-28 mailmagazine setting for trainig site
SELECT id, last_name, first_name, email1 FROM `contacts` WHERE deleted=0 AND smsgroup='student' AND email1!='technopian001@yahoo.co.jp'

SELECT id, last_name, first_name, email1 FROM `contacts` WHERE deleted=0 AND smsgroup='student' AND mail_magazine='1' 

SELECT id, last_name, first_name, email1 FROM `contacts` WHERE deleted=0 AND smsgroup='student' AND email1 IS NOT NULL 
SELECT id, last_name, first_name, email1 FROM `contacts` WHERE deleted=0 AND smsgroup='teacher' AND email1 IS NOT NULL 

-- UPDATE contacts SET email1=NULL WHERE smsgroup='student' 
-- UPDATE contacts SET email1=NULL WHERE smsgroup='teacher' 

SELECT id, last_name, first_name, email1, idnumber, school_id FROM `contacts` WHERE deleted=0 AND smsgroup='student' AND mail_magazine='1' 

SELECT id, idnumber, last_name, first_name, email1, mail_magazine, email_opt_out, invalid_email FROM `contacts` WHERE deleted=0 AND smsgroup='student' AND id<'1388633' AND school_id='b0845182-f1bc-145f-0560-5001d0cbee2a'

-- UPDATE contacts SET mail_magazine=1 WHERE deleted=0 AND smsgroup='student' AND id<'1388633' AND school_id='b0845182-f1bc-145f-0560-5001d0cbee2a'  -- 3,000 rows affected

-- UPDATE contacts SET email1='testdm01@asahiculture.com' WHERE deleted=0 AND smsgroup='student' AND mail_magazine=1

-- UPDATE contacts SET last_name='テストサイト用', first_name=id WHERE deleted=0 AND smsgroup='student' AND mail_magazine=1

-- UPDATE contacts SET email_opt_out=0, invalid_email=0 WHERE deleted=0 AND smsgroup='student' AND mail_magazine=1

-- 2015-08-28 DB update

INSERT INTO `smsevent_instances` (`id`, `date_modified`, `event_id`, `event_type`, `instance_id`, `target_id`, `category`, `from_datetime`, `to_datetime`, `status`, `from_instance_id`, `to_instance_id`, `attendance_status`, `instance_contact_status`, `name`, `description`, `early_late_time2`, `early_late_time3`, `attendance_memo`) VALUES
('36cd0b6a-7500-ea61-0001-55d7dde31389', '2015-08-22 11:23:55', '891804e5-0f44-5c7f-9301-552f35a27a4d', 'course', 'e96560cc-b389-eb33-254d-55d7dd228e70', '1428468', 'student', '2015-10-10 10:00:00', '2015-10-10 11:30:00', 8706, 'bb4b289b-945b-c9d9-035b-552f358ad6e3', '', NULL, 2, NULL, '', 0, 0, NULL), 
('36cd0b6a-7500-ea61-0002-55d7dde31389', '2015-08-22 11:23:55', '891804e5-0f44-5c7f-9301-552f35a27a4d', 'course', 'e96560cc-b389-eb33-254d-55d7dd228e70', '804281', 'student', '2015-10-10 10:00:00', '2015-10-10 11:30:00', 8706, 'bb4b289b-945b-c9d9-035b-552f358ad6e3', '', NULL, 2, NULL, '', 0, 0, NULL), 
('36cd0b6a-7500-ea61-0003-55d7dde31389', '2015-08-22 11:23:55', '891804e5-0f44-5c7f-9301-552f35a27a4d', 'course', 'e96560cc-b389-eb33-254d-55d7dd228e70', '771141', 'student', '2015-10-10 10:00:00', '2015-10-10 11:30:00', 8706, 'bb4b289b-945b-c9d9-035b-552f358ad6e3', '', NULL, 2, NULL, '', 0, 0, NULL), 
('36cd0b6a-7500-ea61-0004-55d7dde31389', '2015-08-22 11:23:55', '891804e5-0f44-5c7f-9301-552f35a27a4d', 'course', 'e96560cc-b389-eb33-254d-55d7dd228e70', '887178', 'student', '2015-10-10 10:00:00', '2015-10-10 11:30:00', 8706, 'bb4b289b-945b-c9d9-035b-552f358ad6e3', '', NULL, 2, NULL, '', 0, 0, NULL);

INSERT INTO `smsevent_instances` (`id`, `date_modified`, `event_id`, `event_type`, `instance_id`, `target_id`, `category`, `from_datetime`, `to_datetime`, `status`, `from_instance_id`, `to_instance_id`, `attendance_status`, `instance_contact_status`, `name`, `description`, `early_late_time2`, `early_late_time3`, `attendance_memo`) VALUES
('47efde0f-db3d-627c-18eb-55a0681e0eb9', '2015-07-11 09:49:53', '891804e5-0f44-5c7f-9301-552f35a27a4d', 'course', 'bb2b0cae-1e97-af49-b048-552f35ca4fcf', '1428468', 'student', '2015-07-11 10:00:00', '2015-07-11 11:30:00', 1, '', '', NULL, 2, NULL, '', 0, 0, NULL);


INSERT INTO `maeukekin` (`student_id`, `course_id`, `school_id`, `bumon_code`, `nenki`, `common_code`, `nyukinbi`, `keijobi`, `fee_subcategory`, `kingaku`, `fee_id`, `kaisu`, `date_modified`) VALUES
('1505737', '1ad54aa1-b3db-4ab6-39c4-557aa6cf0432', 'b0845182-f1bc-145f-0560-5001d0cbee2a', '05', 201510, '532005701', '2015-10-27', '2015-11-24', 't', -550, 'chousei_399a639c-b623-eb57-3854-562eeda6b395', -1, '2015-11-30 17:00:00'),
('1505737', '1ad54aa1-b3db-4ab6-39c4-557aa6cf0432', 'b0845182-f1bc-145f-0560-5001d0cbee2a', '05', 201510, '532005701', '2015-10-27', '2015-12-08', 't', -550, 'chousei_399a639c-b623-eb57-3854-562eeda6b395', -1, '2015-11-30 17:00:00');


-- 2016-12-01 DB update

INSERT INTO `smsevent_instances` (`id`, `date_modified`, `event_id`, `event_type`, `instance_id`, `target_id`, `category`, `from_datetime`, `to_datetime`, `status`, `from_instance_id`, `to_instance_id`, `attendance_status`, `instance_contact_status`, `name`, `description`, `early_late_time2`, `early_late_time3`, `attendance_memo`) VALUES
('8a7901eb-b1ae-e1e0-89a7-57f5de4814', '2016-10-06 14:17:31', '344834b1-50c2-0fa1-4b15-577dc7f76478', 'course', 'e6f7e4ca-493a-bb66-0718-577dc796ecaf', '1160631', 'student', '2016-10-18 10:30:00', '2016-10-18 12:30:00', 1, '', '', NULL, 2, NULL, '', 0, 0, NULL),
('8b820d52-148a-8943-9293-57f5de45cf', '2016-10-06 14:17:31', '344834b1-50c2-0fa1-4b15-577dc7f76478', 'course', 'e757a494-bf8b-59b4-e24c-577dc700b67c', '1160631', 'student', '2016-12-20 10:30:00', '2016-12-20 12:30:00', 1, '', '', NULL, 2, NULL, '', 0, 0, NULL);


-- 2017-08-11 Bug 66606 (rio)
INSERT INTO config (`category`, `name`, `value`) VALUES ('course_cancel','cancel_period','90');

