-- 2009-05-13
SELECT id, CONCAT_WS('', alt_address_state, alt_address_city, alt_address_street), total, postcode FROM contacts, yubin
WHERE CONCAT_WS('', alt_address_state, alt_address_city, alt_address_street) LIKE CONCAT_WS('',total,"%");

UPDATE contacts,yubin SET alt_address_postalcode = postcode 
WHERE CONCAT_WS('', alt_address_state, alt_address_city, alt_address_street) LIKE CONCAT_WS('',total,"%");

SELECT id, alt_address_postalcode, CONCAT_WS('', alt_address_state, alt_address_city, alt_address_street) FROM contacts WHERE 1;

UPDATE contacts SET idnumber = id;

UPDATE contacts, cupdate SET contacts.id = cupdate.id WHERE contacts.idnumber = cupdate.idnumber;

-- 2009-11-30
INSERT INTO `contacts` 
(`id`, `date_entered`,`date_modified`,`smsgroup`,`last_name`,`primary_address_state`,`primary_address_city`,`primary_address_street`, `phone_work`, 
`lead_source`, `custom_var13`, `custom_var19`, `contact_status` )
SELECT id,'2009-11-30 16:00:15', '2009-11-30 16:00:15', 'student', name,state,city,address,phone_work,lead_source,custom_var13, custom_var19, status 
FROM shanaisms;

UPDATE contacts,yubin.yubin SET primary_address_postalcode = postcode 
WHERE CONCAT_WS('', primary_address_state, primary_address_city, primary_address_street) LIKE CONCAT_WS('',total,"%")
AND date_entered='2009-11-30 16:00:15';

-- 20111217

DELETE FROM contacts WHERE deleted=1 AND id!='be59a6ae-2ceb-667f-5ba0-45b03573f1ac' ;

SELECT id, last_name, company_name, contact_status, date_entered,custom_var14, custom_var19 FROM `contacts` 
WHERE  smsgroup='student' AND contact_status='5' AND (custom_var19 & 1) = 1

UPDATE contacts SET contact_status='22'
WHERE  smsgroup='student' AND contact_status='5' AND (custom_var19 & 1) = 1

SELECT id, last_name, first_name, company_name, contact_status, date_entered, custom_var14, custom_var19, email1
FROM `contacts` WHERE smsgroup = 'student' AND contact_status = '7' AND company_name='横国大鈴木研究室'

UPDATE contacts SET contact_status='22', custom_var19='8'
WHERE  smsgroup = 'student' AND contact_status = '0' AND date_entered='2010-03-30 10:00:33'

DELETE FROM `contacts` WHERE smsgroup = 'student' AND contact_status = '7' AND email1 LIKE '%keio.ac.jp'

-- get old toiawase information 
-- assigned to takahashi
SELECT last_name, first_name, company_name, contact_status, date_modified, email1 
FROM `contacts1219`
WHERE deleted=0 AND smsgroup='student' AND `assigned_user_id` LIKE '7811fbfe-e6ad-9ed4-bad2-4b4132bf9299'

-- toiawase from HP
SELECT last_name, first_name, company_name, contact_status, date_modified, email1
FROM `contacts` WHERE `contact_status` LIKE '5' AND lead_source='hp' AND custom_var14 ='inquiry_received'

SELECT * FROM `contacts` WHERE `contact_status` LIKE '5' AND lead_source='hp' AND custom_var14 ='inquiry_received' AND deleted=0




-- ------------------------
-- Start of implementing AN 
-- ------------------------
-- combini sakusei id
ALTER TABLE `sms_transaction` ADD `combini_sakusei_id` INT NOT NULL DEFAULT '0';
ALTER TABLE `sms_transaction` CHANGE `combini_sakusei_id` `combini_sakusei_id` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0';
ALTER TABLE `sms_transaction` ADD INDEX ( `combini_sakusei_id` );

-- 2012-07-23
-- ALTER TABLE `exam` DROP `exam_takable_by` ;
-- ALTER TABLE `exam` ADD `kamoku_name` VARCHAR( 50 ) NULL DEFAULT NULL AFTER `resource_id` ;
-- ALTER TABLE `exam` ADD `nendo` VARCHAR( 16 ) NULL DEFAULT NULL AFTER `max_point` ;

-- 2012-07-25
ALTER TABLE `batch_manager` CHANGE `category` `category` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ;


-- 2012-08-30
ALTER TABLE `batch_manager` ADD `school_id` VARCHAR( 36 ) NULL DEFAULT NULL ;

-- 2012-09-04
ALTER TABLE `bankinfo_history` ADD INDEX ( `contact_id` ) ;

-- 2012-10-10
ALTER TABLE `users` CHANGE `user_preferences` `user_preferences` MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ;

-- 2012-10-16
-- once tableli sec ve table ismini degistir
-- emailsemail_cstm => emails_sub1
RENAME TABLE `emailsemail_cstm` TO `emails_sub1` ;
ALTER TABLE `emails_sub1` CHANGE `id_c` `id_sub1` VARCHAR( 36 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ;

-- once tableli sec ve table ismini degistir
-- resourceschool_cstm => resource_sub1
RENAME TABLE `resourceschool_cstm` TO `resource_sub1` ;
ALTER TABLE `resource_sub1` CHANGE `id_c` `id_sub1` VARCHAR( 36 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;



-- 2012-11-03
ALTER TABLE `resource` ADD `area_id` VARCHAR( 10 ) NULL DEFAULT NULL ;

ALTER TABLE `sms_salesgroup` CHANGE `salesno` `salesno` VARCHAR( 36 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ;
ALTER TABLE `sms_fee` CHANGE `salesno` `salesno` VARCHAR( 36 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ;

-- 2012-12-03
ALTER TABLE `sms_transaction` ADD `fee_name` VARCHAR( 100 ) NULL DEFAULT NULL ;
ALTER TABLE `sms_transaction` ADD `school_id` VARCHAR( 36 ) NULL DEFAULT NULL ;

-- 2012-12-18 
ALTER TABLE `sms_recurrence` ADD `actual_number_of_occurrences` INT( 4 ) NULL DEFAULT '0' AFTER `actual_event_end`;

-- 2012-12-26
ALTER TABLE `contacts` ADD `nyutai_mail_notice` TINYINT NOT NULL DEFAULT '0' AFTER `card_id` ;

-- 2012-12-30
-- ALTER TABLE `exam` ADD PRIMARY KEY ( `id` );
-- ALTER TABLE `exam` ADD INDEX ( `event_id` );
-- ALTER TABLE `exam` ADD INDEX ( `exam_date` );
-- ALTER TABLE `exam` ADD INDEX ( `name` ( 15 ) );
-- ALTER TABLE `exam` ADD INDEX ( `kamoku_name` ( 15 ) );

-- 2013-01-25
UPDATE sms_transaction, sms_fee SET sms_transaction.fee_name=sms_fee.name, sms_transaction.school_id=sms_fee.school_id
WHERE sms_transaction.fee_id=sms_fee.id AND sms_fee.deleted=0 AND sms_transaction.deleted=0 ;

-- 2013-01-30   - no need if already exists
-- insert a new row into config_lock table to store the last combini unique id
INSERT INTO config_lock ( `category` , `name` , `value`) VALUES ( 'unique_id', 'combini_sakusei_id', '0' );

-- 2013-02-04 is this one necessary for all schools?
-- alter table contacts modify column `email2` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci ;

-- 2013-02-19 nyukin_school_id and nyukin_tanmatsu_id
-- SELECT * FROM sms_transaction WHERE `t_date` IS NOT NULL AND t_date != '0000-00-00 00:00:00' ;
-- UPDATE sms_transaction SET nyukin_school_id='sssssss-1111', nyukin_tanmatsu_id='a09ae8da-32c9-5d91-1620-511fccf50c1f' WHERE `t_date` IS NOT NULL AND t_date != '0000-00-00 00:00:00' ;

-- 2013-03-07 
 ALTER TABLE `resource_sub1` CHANGE `shimebi_c` `shimebi_c` DATE NULL DEFAULT '2001-01-01' ;
 
ALTER TABLE `resource_sub1` CHANGE `last_student_id_c` `last_student_id_c` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0',
CHANGE `last_invoice_id_c` `last_invoice_id_c` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0',
CHANGE `last_teacher_id_c` `last_teacher_id_c` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '90000'  ;

-- 2013-04-01
CREATE TABLE IF NOT EXISTS `courselist` (
  `id` char(36) NOT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `date_entered` datetime NOT NULL,
  `date_modified` datetime NOT NULL,
  `modified_user_id` char(36) NOT NULL,
  `created_by` char(36) NOT NULL,
  `school_id` char(36) DEFAULT NULL,
  `assigned_user_id` char(36) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `description` text,
  PRIMARY KEY (`id`),
  KEY `idx_clist_id_del` (`id`,`deleted`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `courselist_courses` (
  `id` varchar(36) NOT NULL DEFAULT '',
  `courselist_id` varchar(36) DEFAULT NULL,
  `course_id` varchar(36) DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_clist_id` (`courselist_id`),
  KEY `idx_course_id` (`course_id`,`courselist_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 2013-04-15
CREATE TABLE IF NOT EXISTS `menu_priviledge` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `date_entered` datetime NOT NULL,
  `date_modified` datetime NOT NULL,
  `modified_user_id` char(36) NOT NULL,
  `created_by` char(36) NOT NULL,
  `priviledge_id` char(36) DEFAULT NULL,
  `menu_item` varchar(40) DEFAULT NULL,
  `menu_name` varchar(40) DEFAULT NULL,
  `module_name` varchar(40) DEFAULT NULL,
  `priviledge` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- 2013-04-18
-- ACC仕様なので未反映。sampledb内にbank_infoはない。
-- ALTER TABLE `bank_info` ADD `deleted` TINYINT NOT NULL DEFAULT '0', ADD `date_modified` DATETIME NOT NULL , ADD `modified_user_id` VARCHAR( 36 ) NOT NULL ;  UPDATE `bank_info` SET date_modified='2013-03-20 10:10:00', modified_user_id='1' WHERE 1 ; 


-- 2013-09-03  bank import related fix
--　delete文は削除件数が多いが問題ないとのことです。例：heglは16万件くらい。
delete FROM `bankexport_salesnos` where status <> 0;
ALTER TABLE `bankexport_salesnos` ADD PRIMARY KEY ( `hikiotoshi_date` , `salesno` , `contact_id` ) ;

-- 2013-11-26
ALTER TABLE `acl_session` CHANGE `acl_text` `acl_text` MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL ;
ALTER TABLE `batch_items` CHANGE `item_info4` `item_info4` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ;
ALTER TABLE `batch_manager` CHANGE `query` `query` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL ;
ALTER TABLE `batch_manager` CHANGE `freeparam1` `freeparam1` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ;
ALTER TABLE `batch_manager` CHANGE `freeparam2` `freeparam2` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ;
ALTER TABLE `batch_manager` CHANGE `freeparam3` `freeparam3` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ;
ALTER TABLE `config_import_contact_fields` CHANGE `validation_type` `validation_type` VARCHAR( 20 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ;
ALTER TABLE `emailman` CHANGE `replace_vars` `replace_vars` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL ;
ALTER TABLE `keiriposactions_history` CHANGE `total_count` `total_count` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ;
ALTER TABLE `poll_vote` CHANGE `answers` `answers` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL ;
ALTER TABLE `poll_vote` CHANGE `answers_excel` `answers_excel` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL ;
ALTER TABLE `resource` CHANGE `activation_key` `activation_key` VARCHAR( 36 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ;
ALTER TABLE `sms_entrance` CHANGE `deleted` `deleted` TINYINT( 4 ) NOT NULL DEFAULT '0' ;
ALTER TABLE `smsevent_instances` CHANGE `attendance_memo` `attendance_memo` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ;
ALTER TABLE `sms_recurrence_templates` CHANGE `value` `value` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL ;
ALTER TABLE `sms_salesgroup` CHANGE `due_to` `due_to` VARCHAR( 2 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0';

-- 2014-04-03
-- cron起動のバッチの多重起動制御
ALTER TABLE `batch_manager` ADD `last_run_start` DATETIME NULL ; 

-- ------------------------
-- END of implementing AN 
-- ------------------------

 -- jewel 69113
ALTER TABLE `sms_fee` CHANGE COLUMN `basic_fee` `basic_fee` DOUBLE NULL DEFAULT '0' ;

-- 79891 (mon)
alter table contacts modify column  do_not_call varchar(3);
alter table contacts modify column  send_document_home varchar(3);
alter table contacts modify column  send_document_work varchar(3);
alter table contacts modify column  email_opt_out varchar(3);
alter table contacts modify column  invalid_email varchar(3);