SELECT name, SUBSTRING(name, LOCATE('Class ', name)+6) as new_name 
FROM smsevent WHERE school_id LIKE 'sss%' AND name LIKE '%Class %'

UPDATE smsevent SET name=SUBSTRING(name, LOCATE('Class ', name)+6) 
WHERE school_id LIKE 'sss%' AND name LIKE '%Class %' AND school_id='sssssss-1111'

SELECT * FROM `sms_fee` WHERE `event_id` IS NULL AND `contact_id` IS NOT NULL ;

UPDATE sms_fee SET event_id = '2c600dbd-a2ce-147f-2c69-4ef992da1876' WHERE `event_id` IS NULL AND `contact_id` IS NOT NULL ;


SELECT sms_fee.id, sms_fee.contact_id, sms_fee.event_id, event_contacts.id, event_contacts.event_id FROM sms_fee
INNER JOIN event_contacts ON sms_fee.contact_id=event_contacts.contact_id AND event_contacts.deleted=0 
WHERE sms_fee.event_id != event_contacts.event_id AND sms_fee.deleted=0 AND sms_fee.fee_subcategory='tuition'
GROUP BY sms_fee.contact_id HAVING count(sms_fee.contact_id) > 1

-- students having more than one course => all are checked and seems to be OK
SELECT contact_id, idnumber, smsevent.name as course FROM event_contacts, contacts, smsevent
WHERE event_contact_status =0 AND contact_id=contacts.id AND smsevent.id=event_id
GROUP BY contact_id HAVING count( contact_id ) >1
ORDER BY `contacts`.`idnumber` ASC

SELECT idnumber as student_id, last_name, first_name, smsevent.name as course, common_code as course_id, contact_id, event_id
FROM event_contacts
INNER JOIN contacts ON contact_id=contacts.id  AND contacts.school_id='sssssss-1111' AND idnumber IN 
	('211302', '211448', '211449', '211450', '211451', '211453', '211454', '211455', '211460', '211461', '211462', '211464', '211468', '211469', '211474', '211478', '211479', '211482', '211483', '211485', '211486', '211488', '211489', '211491', '211493', '211494', '211495', '211496', '211497', '211498', '211501', '211507', '211510', '211511', '211513', '211517', '211519', '211520', '211521', '211523', '211524', '211525', '211526', '211531', '211532', '211535', '211536', '211538', '211539')
LEFT JOIN smsevent ON event_contacts.event_id=smsevent.id AND smsevent.school_id='sssssss-1111'
WHERE event_contact_status =0
ORDER BY `contacts`.`idnumber` ASC



-- preparation of honban DB -> deleting existing FICS student, course, event_contacts,  data  //	 fee, transaction, salesgroup

SELECT * FROM `contacts` WHERE smsgroup = 'student' AND school_id = 'sssssss-1111' ;
-- contact_id IN ('7d3e9de5-8a75-323e-ff0d-4efa6a5fd631', '98beae10-fc42-1bd9-5310-4efa6a9892dd', '9a7ae5fd-bf8e-02b3-6454-4efa62810ff7', 'a33c5c10-1bad-7fd5-00da-4edcc4079268')


SELECT event_contacts.contact_id, event_contacts.event_id, event_contact_status, contacts.id, contacts.school_id
FROM event_contacts 
INNER JOIN contacts ON event_contacts.contact_id = contacts.id AND contacts.school_id='sssssss-1111' AND contacts.deleted=0
WHERE event_contacts.deleted=0 		AND event_contact_status=0 

SELECT * FROM `event_contacts` WHERE `event_id` LIKE 'fics_course_%' ;


DELETE FROM `contacts` WHERE smsgroup = 'student' AND school_id = 'sssssss-1111' ;

DELETE FROM `event_contacts` WHERE `event_id` LIKE 'fics_course_%' ;

DELETE FROM `category_tree` WHERE `self_id` LIKE 'fics_course_%';
DELETE FROM `smsevent` WHERE `id` LIKE 'fics_course_%';

UPDATE smsevent SET name=SUBSTRING(name, LOCATE('Class ', name)+6) 
WHERE school_id LIKE 'sss%' AND name LIKE '%Class %'

SELECT max( idnumber ) FROM `contacts` WHERE school_id = 'sssssss-1111' AND smsgroup = 'student'  ;
SELECT idnumber, CONCAT('0', idnumber) as new_idnumber FROM `contacts` WHERE school_id = 'sssssss-1111' AND smsgroup = 'student' ;

UPDATE contacts SET idnumber = CONCAT('0', idnumber) WHERE school_id = 'sssssss-1111' AND smsgroup = 'student' ;

SELECT name, CONCAT(SUBSTRING(name, 1, LOCATE(':', name)+2 ), ' 2011'), custom_var36 FROM smsevent WHERE school_id = 'sssssss-1111' AND custom_var36='2011-2012' ;

UPDATE smsevent SET name=CONCAT(SUBSTRING(name, 1, LOCATE(':', name)+2 ), ' 2011') WHERE school_id = 'sssssss-1111' AND custom_var36='2011-2012' ;

-- DB updates for honban site

DROP TABLE `poll_vote` ;

CREATE TABLE IF NOT EXISTS `poll_vote` (
  `id` varchar(36) NOT NULL,
  `poll_id` varchar(36) NOT NULL,
  `submit_id` varchar(36) NOT NULL,
  `contact_id` varchar(36) default NULL,
  `answers` text NOT NULL,
  `vote_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `ip_address` varchar(30) default NULL,
  PRIMARY KEY  (`id`),
  KEY `poll_id` (`poll_id`),
  KEY `vote_date` (`vote_date`),
  KEY `submit_id` (`submit_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;


-- Contacts
ALTER TABLE `contacts` ADD INDEX ( `idnumber` );
ALTER TABLE `contacts` ADD INDEX ( `date_modified` );
ALTER TABLE `contacts` ADD INDEX ( `phone_home` );
ALTER TABLE `contacts` ADD INDEX ( `phone_mobile` );
ALTER TABLE `contacts` ADD INDEX ( `leave_date` );

-- Poll vote table_update
ALTER TABLE `poll_vote` ADD `answers_excel` TEXT NOT NULL AFTER `answers` ;


CREATE TABLE IF NOT EXISTS `manual_contact` (
  `id` varchar(36) NOT NULL DEFAULT '',
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `date_entered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified_user_id` varchar(36) DEFAULT NULL,
  `assigned_user_id` varchar(36) DEFAULT NULL,
  `created_by` varchar(36) DEFAULT NULL,
  `salutation` varchar(5) DEFAULT NULL,
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `first_name_furigana` varchar(100) DEFAULT NULL,
  `last_name_furigana` varchar(100) DEFAULT NULL,
  `lead_source` varchar(100) DEFAULT NULL,
  `lead_source_description` varchar(64) DEFAULT NULL,
  `title` varchar(50) DEFAULT NULL,
  `department` varchar(100) DEFAULT NULL,
  `reports_to_id` varchar(36) DEFAULT NULL,
  `commission_company_id` varchar(36) DEFAULT NULL,
  `birthdate` date DEFAULT NULL,
  `do_not_call` varchar(3) DEFAULT '0',
  `phone_home` varchar(25) DEFAULT NULL,
  `phone_mobile` varchar(25) DEFAULT NULL,
  `phone_work` varchar(25) DEFAULT NULL,
  `phone_other` varchar(25) DEFAULT NULL,
  `phone_fax` varchar(25) DEFAULT NULL,
  `email1` varchar(100) DEFAULT NULL,
  `email2` varchar(100) DEFAULT NULL,
  `assistant` varchar(75) DEFAULT NULL,
  `assistant_phone` varchar(25) DEFAULT NULL,
  `email_opt_out` varchar(3) DEFAULT '0',
  `primary_address_street` varchar(150) DEFAULT NULL,
  `primary_address_city` varchar(100) DEFAULT NULL,
  `primary_address_state` varchar(100) DEFAULT NULL,
  `primary_address_postalcode` varchar(20) DEFAULT NULL,
  `primary_address_country` varchar(100) DEFAULT NULL,
  `primary_address_kana` varchar(250) DEFAULT NULL,
  `alt_address_street` varchar(150) DEFAULT NULL,
  `alt_address_city` varchar(100) DEFAULT NULL,
  `alt_address_state` varchar(100) DEFAULT NULL,
  `alt_address_postalcode` varchar(20) DEFAULT NULL,
  `alt_address_country` varchar(100) DEFAULT NULL,
  `alt_address_kana` varchar(250) DEFAULT NULL,
  `description` text,
  `portal_name` varchar(255) DEFAULT NULL,
  `portal_active` tinyint(1) NOT NULL DEFAULT '0',
  `portal_app` varchar(255) DEFAULT NULL,
  `invalid_email` tinyint(1) DEFAULT '0',
  `smsgroup` varchar(15) NOT NULL DEFAULT '',
  `contact_status` varchar(3) NOT NULL DEFAULT '0',
  `picture_filename` varchar(255) DEFAULT NULL,
  `picture_file_mime_type` varchar(100) DEFAULT NULL,
  `school_id` varchar(36) NOT NULL DEFAULT 'sssssss-1111',
  `vacation_total` float DEFAULT NULL,
  `vacation_used` float DEFAULT NULL,
  `is_available_strict` tinyint(1) NOT NULL DEFAULT '0',
  `major` varchar(32) DEFAULT NULL,
  `send_document_home` varchar(3) NOT NULL DEFAULT 'on',
  `send_document_work` varchar(3) NOT NULL DEFAULT 'on',
  `company_name` varchar(100) DEFAULT NULL,
  `idnumber` varchar(32) DEFAULT NULL,
  `nationality` varchar(16) DEFAULT NULL,
  `gender` varchar(8) DEFAULT NULL,
  `work_school` varchar(64) DEFAULT NULL,
  `last_school_name` varchar(64) DEFAULT NULL,
  `last_school_dept` varchar(64) DEFAULT NULL,
  `study_aim` varchar(64) DEFAULT NULL,
  `transport_fee_type` varchar(16) DEFAULT NULL,
  `transportfee` int(11) DEFAULT NULL,
  `incometax_rate` float NOT NULL DEFAULT '0.1',
  `leave_date` date DEFAULT NULL,
  `enroll_date` date DEFAULT NULL,
  `grade` varchar(10) DEFAULT NULL,
  `grade_entrance_date` date DEFAULT NULL,
  `application_date` date DEFAULT NULL,
  `extra_date` date DEFAULT NULL,
  `id_card_field1` text,
  `id_card_field2` varchar(50) DEFAULT NULL,
  `id_card_field3` varchar(50) DEFAULT NULL,
  `id_card_field4` text,
  `renmei_label` text,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `custom_var1` text,
  `custom_var2` text,
  `custom_var3` text,
  `custom_var4` text,
  `custom_var5` text,
  `custom_var6` text,
  `custom_var7` text,
  `custom_var8` text,
  `custom_var9` text,
  `custom_var10` text,
  `custom_var11` text,
  `custom_var12` text,
  `custom_var13` text,
  `custom_var14` text,
  `custom_var15` text,
  `custom_var16` text,
  `custom_var17` text,
  `custom_var18` text,
  `custom_var19` text,
  `custom_var20` text,
  `raw_import_data` text,
  `import_serial_no` int(11) NOT NULL AUTO_INCREMENT,
  `import_source` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`import_serial_no`),
  UNIQUE KEY `id` (`id`),
  KEY `last_name` (`last_name`,`status`),
  KEY `last_name_furigana` (`last_name_furigana`),
  KEY `first_name` (`first_name`),
  KEY `first_name_furigana` (`first_name_furigana`),
  KEY `email` (`email1`),
  KEY `tel1` (`phone_home`),
  KEY `tel2` (`phone_other`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- Manual contact import
ALTER TABLE `manual_contact` ADD INDEX ( `idnumber` );

-- Emailman_update
ALTER TABLE `emailman` ADD `replace_vars` TEXT NOT NULL DEFAULT '';

ALTER TABLE `smsevent_instances` DROP PRIMARY KEY ,
ADD PRIMARY KEY ( `event_id` , `instance_id` , `target_id` ) ;
ALTER TABLE `smsevent_instances` ADD INDEX ( `from_datetime` , `to_datetime` ) ;

ALTER TABLE `manual_contact` ADD `grade_entered` VARCHAR( 10 ) NULL AFTER `grade` ;
ALTER TABLE `manual_contact` ADD `card_id` VARCHAR( 36 ) NOT NULL AFTER `renmei_label` ;

-- 2011-11-20 General Contact info import (mapping)
CREATE TABLE IF NOT EXISTS `config_import_contact_fields` (
  `id` varchar(36) CHARACTER SET utf8 NOT NULL,
  `config_id` varchar(36) NOT NULL,
  `db_field_name` varchar(50) CHARACTER SET utf8 NOT NULL,
  `import_field_name` varchar(100) CHARACTER SET utf8 NOT NULL,
  `import_field_order` int(11) NOT NULL,
  `validation_type` varchar(20) CHARACTER SET utf8 NOT NULL,
  `required` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`config_id`,`db_field_name`)
) ENGINE=MyISAM CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `config_import_info` (
  `id` varchar(36) NOT NULL,
  `main_category` enum('contact','event','fee','schedule') CHARACTER SET utf8 NOT NULL,
  `sub_category` enum('Student','Teacher','Contact','Staff','Course','Fee') CHARACTER SET utf8 DEFAULT NULL,
  `import_sub_info` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 NOT NULL,
  `user_type` enum('admin','user') CHARACTER SET utf8 NOT NULL DEFAULT 'user',
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM CHARSET=utf8;

-- 2001-11-30 Poll description fields
ALTER TABLE `poll` ADD `description1` TEXT NULL AFTER `description` ,
ADD `description2` TEXT NULL AFTER `description1` ;