-- 2008-08-29
-- Instance ID:  edb4a64a-6291-73fe-2ea5-48747d6707dd  Event ID: 604148eb-b735-c0d4-ff50-4809d91f99d2
-- Instance ID:  a83056be-afce-1d51-7b19-48b4b5f1a3f1  Event ID: 1a47fcdd-0b78-bca1-a4ee-48b4b43991db


-- strange stage in instances table (time different for the same instance_id)
SELECT iii.event_id, iii.target_id, iii.category, iii.instance_id, iii.from_datetime, eee.from_datetime inst_time, iii.to_datetime
FROM smsevent_instances iii
LEFT JOIN sms_event_instance eee on eee.id=iii.instance_id
WHERE iii.from_datetime != eee.from_datetime
	AND iii.from_datetime > 2010-07-01
	AND iii.instance_id LIKE 'b849de2f-aaf6-345b-12c3-4c37f4d71524'
ORDER BY iii.from_datetime ;

SELECT iii.event_id, iii.target_id, iii.category, iii.instance_id, iii.from_datetime, eee.from_datetime inst_time, iii.to_datetime
FROM smsevent_instances iii
LEFT JOIN sms_event_instance eee on eee.id=iii.instance_id
WHERE iii.from_datetime != eee.from_datetime
	AND iii.from_datetime > 2010-07-01
	AND iii.instance_id LIKE '94870b13-f047-d6fe-f6f4-4c3aa0389799'
ORDER BY iii.from_datetime ;

SELECT iii.event_id, iii.target_id, iii.category, iii.instance_id, iii.from_datetime, eee.from_datetime inst_time, iii.to_datetime
FROM smsevent_instances iii
LEFT JOIN sms_event_instance eee on eee.id=iii.instance_id
WHERE iii.from_datetime != eee.from_datetime
	AND iii.from_datetime > '2010-07-01'
	AND iii.from_datetime < '2010-10-01 00:00:00'
ORDER BY iii.from_datetime ;


SELECT iii.event_id, iii.target_id, iii.category, iii.instance_id, iii.from_datetime category_time, eee.from_datetime inst_time,
	iii.date_modified category_mod, eee.date_modified inst_mod
FROM smsevent_instances iii
LEFT JOIN sms_event_instance eee on eee.id=iii.instance_id
WHERE iii.from_datetime != eee.from_datetime
	AND iii.from_datetime > '2010-07-01'
	AND iii.from_datetime < '2010-10-01 00:00:00'
ORDER BY iii.from_datetime ;



-- 2011-04-07 honban upload 

CREATE TABLE IF NOT EXISTS `sms_memo_templates` (
  `id` varchar(36) NOT NULL DEFAULT '',
  `date_entered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified_user_id` varchar(36) DEFAULT NULL,
  `assigned_user_id` varchar(36) DEFAULT NULL,
  `created_by` varchar(36) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `memo_description` text,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `status` tinyint(4) NOT NULL DEFAULT '0',
  `school_id` varchar(36) DEFAULT NULL,
  `category_main` varchar(16) DEFAULT NULL,
  `category_sub` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_note_name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE `smsevent_instances` CHANGE `attendance_status` `attendance_status` SMALLINT NULL DEFAULT NULL ;


CREATE TABLE IF NOT EXISTS `batch_items` (
  `id` varchar(36) NOT NULL,
  `batch_id` varchar(36) NOT NULL,
  `item_key` varchar(255) NOT NULL,
  `item_info1` varchar(255) NOT NULL,
  `item_info2` varchar(255) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `sort_order` int(11) NOT NULL,
  `deleted` tinyint(4) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;


CREATE TABLE IF NOT EXISTS `batch_manager` (
  `id` varchar(36) NOT NULL DEFAULT '',
  `date_entered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `created_by` varchar(36) DEFAULT NULL,
  `modified_user_id` varchar(36) DEFAULT NULL,
  `name` varchar(255) NOT NULL DEFAULT '',
  `category` varchar(20) NOT NULL,
  `date_time_start` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_time_end` datetime DEFAULT NULL,
  `interval_secs` int(100) NOT NULL,
  `last_run` datetime DEFAULT NULL,
  `percentage_completed` float NOT NULL DEFAULT '0',
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `deleted` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_schedule` (`date_time_start`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE `bankactions_history` ADD `data_date` DATE NULL;

CREATE TABLE IF NOT EXISTS `bankexport_salesnos` (
  `hikiotoshi_date` varchar(4) CHARACTER SET latin1 NOT NULL,
  `salesno` varchar(10) CHARACTER SET latin1 NOT NULL,
  `contact_id` varchar(36) CHARACTER SET latin1 NOT NULL,
  `date_modified` datetime NOT NULL,
  `status` tinyint(4) NOT NULL,
  KEY `hikiotoshi_date` (`hikiotoshi_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- DROP TABLE IF EXISTS `combini_history`;
CREATE TABLE IF NOT EXISTS `combini_history` (
  `id` varchar(36) NOT NULL,
  `date_entered` datetime NOT NULL,
  `date_modified` datetime NOT NULL,
  `combiniactions_id` varchar(36) NOT NULL,
  `contact_id` varchar(36) DEFAULT NULL,
  `data_type` varchar(2) DEFAULT NULL,
  `t_date` datetime DEFAULT NULL,
  `salesno` varchar(11) DEFAULT NULL,
  `branch_id` varchar(4) DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `in_amount` varchar(6) DEFAULT NULL,
  `store_code` varchar(8) DEFAULT NULL,
  `store_name` varchar(60) DEFAULT NULL,
  `payment_date` date DEFAULT NULL,
  `error_code` varchar(20) DEFAULT NULL,
  `raw_import_data` text,
  PRIMARY KEY (`id`),
  KEY `combiniactions_id` (`combiniactions_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 1 shusseki update
UPDATE smsevent_instances SET attendance_status = 2 
WHERE (category = 'student' OR category = 'teacher' ) AND attendance_status IS NOT NULL AND from_datetime < '2011-04-07 15:00:00' ;
-- 2 kesseki update
UPDATE smsevent_instances SET attendance_status = 1 
WHERE (category = 'student' OR category = 'teacher' ) AND attendance_status IS NULL AND from_datetime < '2011-04-07 15:00:00' ;

-- 2010-11-01
ALTER TABLE `combini_history` CHANGE `branch_id` `branch_id` VARCHAR( 36 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ;

CREATE TABLE IF NOT EXISTS `exam_student` (
  `contact_id` varchar(36) NOT NULL DEFAULT '',
  `exam_id` varchar(36) NOT NULL,
  `memo` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`contact_id`,`exam_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 2010-12-15
ALTER TABLE `batch_manager` ADD `query` TEXT NOT NULL ,
ADD `filename` VARCHAR( 255 ) NOT NULL DEFAULT '';
ALTER TABLE `batch_manager` ADD `freeparam1` VARCHAR( 255 ) NOT NULL ,
ADD `freeparam2` VARCHAR( 255 ) NOT NULL ,
ADD `freeparam3` VARCHAR( 255 ) NOT NULL ;
ALTER TABLE `batch_items` ADD `item_info3` VARCHAR( 255 ) NOT NULL DEFAULT '' AFTER `item_info2` ,
ADD `item_info4` VARCHAR( 255 ) NOT NULL DEFAULT '' AFTER `item_info3`;
ALTER TABLE `batch_items` CHANGE `item_info1` `item_info1` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
CHANGE `item_info2` `item_info2` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '';


ALTER TABLE `emailman` ADD `send_options` INT NOT NULL DEFAULT '0',
ADD `status` INT NOT NULL DEFAULT '0';


CREATE TABLE IF NOT EXISTS `config_export_fields` (
  `module` varchar(32) NOT NULL,
  `suffix` varchar(32) NOT NULL,
  `user_id` varchar(36) NOT NULL,
  `field` varchar(64) NOT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `date_updated` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 2011-02-02
ALTER TABLE `bankexport_salesnos` CHANGE `salesno` `salesno` VARCHAR( 20 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL ;
ALTER TABLE `combini_history` CHANGE `salesno` `salesno` VARCHAR( 20 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
ALTER TABLE `combini_history` ADD `student_id` VARCHAR( 20 ) NULL DEFAULT NULL;

-- 2011-02-17
ALTER TABLE `combini_history` DROP `branch_id`;

-- 2011-03-11
ALTER TABLE `product` ADD `subcategory` VARCHAR( 64 ) NULL AFTER `productcategory_id` ;
CREATE TABLE IF NOT EXISTS `productinout` (
  `id` varchar(36) NOT NULL DEFAULT '',
  `date_entered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified_user_id` varchar(36) DEFAULT NULL,
  `created_by` varchar(36) DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  `product_id` varchar(36) DEFAULT NULL,
  `category` varchar(16) DEFAULT NULL,
  `price` decimal(20,2) NOT NULL DEFAULT '0.00',
  `date_buy_sell` date DEFAULT NULL,
  `quantity` int(5) NOT NULL DEFAULT '0',
  `school_id` varchar(36) DEFAULT NULL,
  `description` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;



-- 2011-04-7 honban upload gakunen updates

ALTER TABLE `contacts` ADD `grade_entered` VARCHAR( 10 ) AFTER `grade` ;

UPDATE contacts SET grade_entered=grade WHERE smsgroup='student' ; 

UPDATE contacts SET grade=grade_entered + (2011-SUBSTRING(grade_entrance_date, 1, 4)) WHERE smsgroup='student' AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken') AND SUBSTRING(grade_entrance_date, 6, 2) < 4 ;
UPDATE contacts SET grade=grade_entered + (2010-SUBSTRING(grade_entrance_date, 1, 4)) WHERE smsgroup='student' AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken') AND SUBSTRING(grade_entrance_date, 6, 2) >= 4 ;

UPDATE contacts SET grade=18 WHERE smsgroup='student' AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken') AND grade > 18 ;

UPDATE contacts SET grade=grade+1 WHERE smsgroup='student' AND grade IS NOT NULL AND grade!=''
	AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken', '18');

-- 2011-07-14 deleted course investigation
SELECT event_contacts.deleted, smsevent.id, smsevent.name, smsevent.deleted, smsevent.school_id 
FROM event_contacts, smsevent
WHERE contact_id='bd09c2ee-4f44-5627-f17d-46b2ecadd287'	AND event_id=smsevent.id
AND subtype='2'

SELECT smsevent.name, smsevent_instances.* FROM smsevent_instances, smsevent 
WHERE target_id='bd09c2ee-4f44-5627-f17d-46b2ecadd287' AND smsevent_instances.event_id=smsevent.id AND subtype='2' AND 
event_id NOT IN 
('1dd15195-088c-5b5d-31fb-47de69649ba7', '2abc250d-7750-1981-fbc3-47f31d4849a6', '3fa282b4-27fb-6c39-5d76-4d9059e54888', '8a9a047f-ccde-079a-87e9-49d354d3e449', '8c4d88f8-0f02-880b-0965-4c065568125b', '9731c80e-06e5-7440-7fce-47f31e4da3fe', 'a5069b9f-d51e-6654-6cb9-4d8332df9fd9', 'e6ea072e-0240-7dd4-4fe1-46b2f2e16879', 'f2f1ed98-04fa-bcac-e782-4d9058b342af')

SELECT smsevent.name, smsevent_instances_backup.* FROM smsevent_instances_backup, smsevent 
WHERE target_id='bd09c2ee-4f44-5627-f17d-46b2ecadd287' AND smsevent_instances_backup.event_id=smsevent.id AND subtype='2' AND 
event_id NOT IN 
('1dd15195-088c-5b5d-31fb-47de69649ba7', '2abc250d-7750-1981-fbc3-47f31d4849a6', '3fa282b4-27fb-6c39-5d76-4d9059e54888', '8a9a047f-ccde-079a-87e9-49d354d3e449', '8c4d88f8-0f02-880b-0965-4c065568125b', '9731c80e-06e5-7440-7fce-47f31e4da3fe', 'a5069b9f-d51e-6654-6cb9-4d8332df9fd9', 'e6ea072e-0240-7dd4-4fe1-46b2f2e16879', 'f2f1ed98-04fa-bcac-e782-4d9058b342af')