UPDATE kanebo SET work_student= 'house_wife' WHERE work_student= "専業主婦";
UPDATE kanebo SET work_student= 'busunessman' WHERE work_student= "会社員";
UPDATE kanebo SET work_student= 'other' WHERE work_student= "その他";;
UPDATE kanebo SET work_student= 'student' WHERE work_student= "短大・大学・専門学校生";
UPDATE kanebo SET work_student= 'free' WHERE work_student= "自由業";
UPDATE kanebo SET work_student= 'independent' WHERE work_student= "自営業・フリーランス";
UPDATE kanebo SET work_student= 'part-time' WHERE work_student= "パートタイマー";
UPDATE kanebo SET work_student= 'high_school' WHERE work_student= "高校生";
UPDATE kanebo SET work_student= 'none' WHERE work_student= "無職";
UPDATE kanebo SET work_student= '' WHERE work_student= "　";
UPDATE kanebo SET work_student= 'contract_employee' WHERE work_student= "契約・派遣社員";
UPDATE kanebo SET work_student= 'businessman' WHERE work_student= "会社員（営業・販売・サービス職）";
UPDATE kanebo SET work_student= 'student' WHERE work_student= "学生（大学・短大・高専・専門・大学院）";
UPDATE kanebo SET work_student= 'professional' WHERE work_student= "専門職（医者・弁護士・会計士・看護婦他）";
UPDATE kanebo SET work_student= 'businessman_account' WHERE work_student= "会社員（企画・人事・経理ほか事務系職種）";
UPDATE kanebo SET work_student= 'part-time' WHERE work_student= "パート・アルバイト";
UPDATE kanebo SET work_student= 'businessman_other' WHERE work_student= "会社員（その他）";
UPDATE kanebo SET work_student= 'businessman_creative' WHERE work_student= "会社員（クリエイティブ職）";
UPDATE kanebo SET work_student= 'independent' WHERE work_student= "自営・フリーランス";
UPDATE kanebo SET work_student= 'businessman_development' WHERE work_student= "会社員（技術・開発・研究職）";
UPDATE kanebo SET work_student= 'civil_servant' WHERE work_student= "公務員・教員・団体職員";
UPDATE kanebo SET work_student= NULL WHERE work_student= "";


UPDATE kanebo SET lead_source= 'other' WHERE lead_source = "その他";
UPDATE kanebo SET lead_source= 'shokai' WHERE lead_source = "知人の紹介";
UPDATE kanebo SET lead_source= 'kanebohp' WHERE lead_source = "カネボウ化粧品サイト";
UPDATE kanebo SET lead_source= 'newspaper' WHERE lead_source = "新聞";
UPDATE kanebo SET lead_source= 'yahoo' WHERE lead_source = "Yahoo！検索";
UPDATE kanebo SET lead_source= 'aromokyokai' WHERE lead_source = "本アロマ環境協会サイト";
UPDATE kanebo SET lead_source= NULL WHERE lead_source = '';
UPDATE kanebo SET lead_source= 'magazine' WHERE lead_source = "雑誌";
UPDATE kanebo SET lead_source= 'yahoo' WHERE lead_source = "Yahoo!検索";
UPDATE kanebo SET lead_source= 'google' WHERE lead_source = "Google検索";
UPDATE kanebo SET lead_source= 'shikaku_to_shigoto' WHERE lead_source = "資格と仕事net";
UPDATE kanebo SET lead_source= 'keikotomanabu_mobile' WHERE lead_source = "ケイコとマナブ(携帯)";
UPDATE kanebo SET lead_source= 'keikotomanabu_net' WHERE lead_source = "ケイコとマナブ(NET)";
UPDATE kanebo SET lead_source= 'keikotomanabu' WHERE lead_source = "ケイコとマナブ(本誌)";

UPDATE kanebo SET lead_source_description = NULL WHERE lead_source_description='';
UPDATE kanebo SET course = NULL WHERE course='';
UPDATE kanebo SET age = NULL WHERE age='';
UPDATE kanebo SET application_state = NULL WHERE application_state='';
UPDATE kanebo SET description = NULL WHERE description='';

UPDATE kanebo SET gender='male' WHERE gender ='男';
UPDATE kanebo SET gender='female' WHERE gender ='女';
UPDATE kanebo SET gender=NULL WHERE gender ='';


UPDATE kanebo SET last_name = SUBSTRING(name, 1, LOCATE(' ', name)-1 ) WHERE LOCATE(' ', name) !=0 ;
UPDATE kanebo SET last_name = SUBSTRING(name, 1, LOCATE('　', name)-1 ) WHERE LOCATE('　', name) !=0 ;

UPDATE kanebo SET first_name = SUBSTRING(name, LOCATE(' ', name)+1 ) WHERE LOCATE(' ', name) !=0 ;
UPDATE kanebo SET first_name = SUBSTRING(name, LOCATE('　', name)+1 ) WHERE LOCATE('　', name) !=0 ;

UPDATE kanebo SET furigana_last_name = SUBSTRING(furigana, 1, LOCATE(' ', furigana)-1 ) WHERE LOCATE(' ', furigana) !=0 ;
UPDATE kanebo SET furigana_last_name = SUBSTRING(furigana, 1, LOCATE('　', furigana)-1 ) WHERE LOCATE('　', furigana) !=0 ;

UPDATE kanebo SET furigana_first_name = SUBSTRING(furigana, LOCATE(' ', furigana)+1 ) WHERE LOCATE(' ', furigana) !=0 ;
UPDATE kanebo SET furigana_first_name = SUBSTRING(furigana, LOCATE('　', furigana)+1 ) WHERE LOCATE('　', furigana) !=0 ;



UPDATE kanebo SET description = CONCAT_WS('\r\n', description, CONCAT_WS(' ', '興味がある項目： ', description_hobby)  ) 
WHERE description_hobby IS NOT NULL AND description_hobby != '';

UPDATE kanebo SET description = CONCAT_WS('\r\n', description, CONCAT_WS(' ', '問合せ時年齢： ', age)  ) 
WHERE age IS NOT NULL AND age != '';


INSERT INTO `contacts` (
`id` ,`date_entered`,`date_modified`,
`last_name` ,`first_name` , `gender`,
`last_name_furigana`, `first_name_furigana`, `application_date`,
`contact_status`, `smsgroup`, `primary_address_postalcode`,
`primary_address_state`, `primary_address_city`, `primary_address_street`,
`phone_home`, `phone_mobile`, `birthdate`, `email1`,
`work_school`, `lead_source`, `lead_source_description`,
`custom_var14`, `custom_var13`, `description`
)
SELECT id, '2009-02-26 10:00:00', '2009-02-26 10:00:00',
last_name, first_name, gender,
furigana_last_name, furigana_first_name, application_date,
'5', 'student', postalcode,
state, city, street,
phone, mobile, birthdate, email,
work_student, lead_source, lead_source_description,
application_state, course, description
FROM kanebo;

-- 2009-03-03
UPDATE contacts SET custom_var13 = 'estethic' WHERE custom_var13='エステティック';
UPDATE contacts SET custom_var13 = 'nail' WHERE custom_var13='ネイル';
UPDATE contacts SET custom_var13 = 'aroma' WHERE custom_var13='アロマテラピー';

UPDATE contacts SET custom_var14 = 'sent_document' WHERE custom_var14='資料配送済み';


-- 2009-03-16
UPDATE sms_fee SET due_to="14";


--  problematic user has three records in the user table

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`) VALUES 
('c3442bce-742d-83ce-f86a-49f679b93673', 'donot.regret_an.effort-wish28t@docomo.ne.jp', NULL, NULL, '0', '1', NULL, '2009-04-28 03:34:44', '2009-04-28 03:34:44', '26eb3d78-c90c-1982-bc9e-493769d53a54', '26eb3d78-c90c-1982-bc9e-493769d53a54', NULL, NULL, 0, 0, 0, NULL, NULL),
('88d4f9b4-a444-3c7c-71b8-49f679fc0dd7', 'donot.regret_an.effort-wish28t@docomo.ne.jp', NULL, NULL, '0', '1', NULL, '2009-04-28 03:36:04', '2009-04-28 03:36:04', '26eb3d78-c90c-1982-bc9e-493769d53a54', '26eb3d78-c90c-1982-bc9e-493769d53a54', NULL, NULL, 0, 0, 0, NULL, NULL),
('9f7c20c9-5640-a8fd-5a87-49f6793cfe54', 'donot.regret_an.effort-wish28t@docomo.ne.jp', 'doze6eltvhO5Y', 'dcf877b18896c5fa70fbbd4dc9c8adb0', '0', '1', NULL, '2009-04-28 03:36:07', '2010-07-09 09:14:12', '9f7c20c9-5640-a8fd-5a87-49f6793cfe54', '26eb3d78-c90c-1982-bc9e-493769d53a54', 'Active', 'YTo1OntzOjg6InRpbWV6b25lIjtzOjEwOiJBc2lhL1Rva3lvIjtzOjI6InV0IjtpOjE7czo0OToiQ2FsZW5kYXJfRVZFTlRfSU5TVEFOQ0VfQk9PS0lOR19DQUxFTkRBUl9PUkRFUl9CWSI7TjtzOjU4OiJFdmVudEluc3RhbmNlRGV0YWlsX0VWRU5UX0lOU1RBTkNFX0RFVEFJTF9CT09LSU5HX09SREVSX0JZIjtzOjM3OiJzbXNldmVudF9pbnN0YW5jZXMuZnJvbV9kYXRldGltZSBkZXNjIjtzOjk6Imxhc3RUaGVtZSI7czoxMDoiTmV3Qm9va2luZyI7fQ==', 0, 0, 0, 'aecb78f5-ebe1-afed-c4d4-49f1053fb124', 'student');

SELECT distinct target_id FROM `smsevent_instances`
WHERE `target_id` LIKE '4671e487-c712-85ec-c439-49d0886361d2' AND instance_contact_status =7

SELECT distinct target_id FROM smsevent_instances, contacts
WHERE contacts.id=smsevent_instances.target_id AND instance_contact_status =7 AND custom_var11='ticket'


SELECT COUNT(target_id), target_id, idnumber, last_name, first_name FROM smsevent_instances, contacts
WHERE contacts.id=smsevent_instances.target_id AND instance_contact_status =7 AND custom_var11='ticket'
GROUP BY target_id

SELECT last_name, first_name, idnumber FROM contacts WHERE id IN
('9685b2b2-ecf1-6c39-2f43-4a0b6ef5f9cd', 'dbb23c4e-f2f5-2d46-6366-49d6eb8654d7', 'd9196d65-8803-fad1-9286-49a60f2cbe2c', 
'57e573c2-c95d-6182-781d-49f02083d854', '47e7d6dc-23da-e909-47bb-49bf41fa126e', 'dfccb7a9-2c18-29f1-c304-4a038f2160f9', 
'945fb1c5-09c8-c300-12aa-49cb25e33040', 'b3f110db-69e7-4edb-696c-4a164062c728', '9bf13932-203e-aeb0-a533-49cb220b2014', 
'95c6b723-b9b8-8493-58b8-49bf2eaafbf4', '4671e487-c712-85ec-c439-49d0886361d2')

SELECT DISTINCT instance_contact_status FROM smsevent_instances WHERE category = 'student'
	-> result  0: student		7: old student		13: ninzu chousei

SELECT DISTINCT instance_contact_status FROM smsevent_instances WHERE target_id IN 
('9685b2b2-ecf1-6c39-2f43-4a0b6ef5f9cd', 'dbb23c4e-f2f5-2d46-6366-49d6eb8654d7', 'd9196d65-8803-fad1-9286-49a60f2cbe2c', 
'57e573c2-c95d-6182-781d-49f02083d854', '47e7d6dc-23da-e909-47bb-49bf41fa126e', 'dfccb7a9-2c18-29f1-c304-4a038f2160f9', 
'945fb1c5-09c8-c300-12aa-49cb25e33040', 'b3f110db-69e7-4edb-696c-4a164062c728', '9bf13932-203e-aeb0-a533-49cb220b2014', 
'95c6b723-b9b8-8493-58b8-49bf2eaafbf4', '4671e487-c712-85ec-c439-49d0886361d2')

UPDATEX smsevent_instances SET instance_contact_status=0 WHERE target_id IN 
('9685b2b2-ecf1-6c39-2f43-4a0b6ef5f9cd', 'dbb23c4e-f2f5-2d46-6366-49d6eb8654d7', 'd9196d65-8803-fad1-9286-49a60f2cbe2c', 
'57e573c2-c95d-6182-781d-49f02083d854', '47e7d6dc-23da-e909-47bb-49bf41fa126e', 'dfccb7a9-2c18-29f1-c304-4a038f2160f9', 
'945fb1c5-09c8-c300-12aa-49cb25e33040', 'b3f110db-69e7-4edb-696c-4a164062c728', '9bf13932-203e-aeb0-a533-49cb220b2014', 
'95c6b723-b9b8-8493-58b8-49bf2eaafbf4', '4671e487-c712-85ec-c439-49d0886361d2')  -> no all of them are updated

SELECT DISTINCT instance_contact_status FROM smsevent_instances WHERE category = 'student' AND target_id IN 
('9685b2b2-ecf1-6c39-2f43-4a0b6ef5f9cd', 'dbb23c4e-f2f5-2d46-6366-49d6eb8654d7', 'd9196d65-8803-fad1-9286-49a60f2cbe2c', 
'57e573c2-c95d-6182-781d-49f02083d854', '945fb1c5-09c8-c300-12aa-49cb25e33040', 'b3f110db-69e7-4edb-696c-4a164062c728', 
'95c6b723-b9b8-8493-58b8-49bf2eaafbf4', '4671e487-c712-85ec-c439-49d0886361d2')

--  this is the final update query 
UPDATE smsevent_instances SET instance_contact_status=0 WHERE target_id IN 
('9685b2b2-ecf1-6c39-2f43-4a0b6ef5f9cd', 'dbb23c4e-f2f5-2d46-6366-49d6eb8654d7', 'd9196d65-8803-fad1-9286-49a60f2cbe2c', 
'57e573c2-c95d-6182-781d-49f02083d854', '945fb1c5-09c8-c300-12aa-49cb25e33040', 'b3f110db-69e7-4edb-696c-4a164062c728', 
'95c6b723-b9b8-8493-58b8-49bf2eaafbf4', '4671e487-c712-85ec-c439-49d0886361d2')

-- ikkatsu email problem  this below should be set to 127.0.0.1 and 0 respectively
SELECT * FROM `config` WHERE name = 'smtpserver' OR name = 'smtpauth_req'