--2007-08-22 Metehan

SELECT `id` , `date_entered` , `date_modified` , `modified_user_id` , `last_name` , `first_name` , `smsgroup` , `contact_status` , `idnumber` FROM `contacts` 
WHERE `school_id` LIKE '319cf9e5-6b54-dea0-0ffb-466799a2109e' AND `idnumber` LIKE '0100%'  AND smsgroup='student' ;

UPDATE contacts SET deleted=1 
WHERE `school_id` LIKE '319cf9e5-6b54-dea0-0ffb-466799a2109e' AND `idnumber` LIKE '0100%'  AND smsgroup='student' ;


delete from contacts where school_id='483794c4-adee-a607-f685-4441c943a1e2' OR
school_id='58362bd0-5d1e-c3f9-efd1-4441c972601c' OR
school_id='6229f7de-823a-64b1-1705-461c351b34b6' OR
school_id='bf36da0b-976e-73ec-81f9-464990a1d2a8' OR 
school_id='e05a0766-f518-04b9-9a0a-464990bf3b37' OR
school_id='cf35620c-3386-b92a-bd98-4655259540fb' OR 
school_id='51e79ba2-70b1-15b4-881f-4655253302d1' ;


delete from resource where school_id='483794c4-adee-a607-f685-4441c943a1e2' OR
school_id='58362bd0-5d1e-c3f9-efd1-4441c972601c' OR
school_id='6229f7de-823a-64b1-1705-461c351b34b6' OR
school_id='bf36da0b-976e-73ec-81f9-464990a1d2a8' OR 
school_id='e05a0766-f518-04b9-9a0a-464990bf3b37' OR
school_id='cf35620c-3386-b92a-bd98-4655259540fb' OR 
school_id='51e79ba2-70b1-15b4-881f-4655253302d1' ;

delete from resourceschool_cstm where id_c='483794c4-adee-a607-f685-4441c943a1e2' OR
id_c='58362bd0-5d1e-c3f9-efd1-4441c972601c' OR
id_c='6229f7de-823a-64b1-1705-461c351b34b6' OR
id_c='bf36da0b-976e-73ec-81f9-464990a1d2a8' OR 
id_c='e05a0766-f518-04b9-9a0a-464990bf3b37' OR
id_c='cf35620c-3386-b92a-bd98-4655259540fb' OR 
id_c='51e79ba2-70b1-15b4-881f-4655253302d1' ;

delete from smsevent where school_id='483794c4-adee-a607-f685-4441c943a1e2' OR
school_id='58362bd0-5d1e-c3f9-efd1-4441c972601c' OR
school_id='6229f7de-823a-64b1-1705-461c351b34b6' OR
school_id='bf36da0b-976e-73ec-81f9-464990a1d2a8' OR 
school_id='e05a0766-f518-04b9-9a0a-464990bf3b37' OR
school_id='cf35620c-3386-b92a-bd98-4655259540fb' OR 
school_id='51e79ba2-70b1-15b4-881f-4655253302d1' ;


DELETE FROM `resource` WHERE CONVERT( `resource`.`id` USING utf8 ) = '483794c4-adee-a607-f685-4441c943a1e2' LIMIT 1 ;
DELETE FROM `resource` WHERE CONVERT( `resource`.`id` USING utf8 ) = '58362bd0-5d1e-c3f9-efd1-4441c972601c' LIMIT 1 ;
DELETE FROM `resource` WHERE CONVERT( `resource`.`id` USING utf8 ) = '6229f7de-823a-64b1-1705-461c351b34b6' LIMIT 1 ;
DELETE FROM `resource` WHERE CONVERT( `resource`.`id` USING utf8 ) = 'bf36da0b-976e-73ec-81f9-464990a1d2a8' LIMIT 1 ;
DELETE FROM `resource` WHERE CONVERT( `resource`.`id` USING utf8 ) = 'e05a0766-f518-04b9-9a0a-464990bf3b37' LIMIT 1 ;
DELETE FROM `resource` WHERE CONVERT( `resource`.`id` USING utf8 ) = 'cf35620c-3386-b92a-bd98-4655259540fb' LIMIT 1 ;
DELETE FROM `resource` WHERE CONVERT( `resource`.`id` USING utf8 ) = '51e79ba2-70b1-15b4-881f-4655253302d1' LIMIT 1 ;


update contacts set deleted=1 WHERE date_entered<"2007-06-13" and smsgroup='student';
update contacts set deleted=1 WHERE date_entered<"2007-06-13" and smsgroup='teacher';
update contacts set deleted=1 WHERE date_entered<"2007-06-13" and smsgroup='parent';
update contacts set deleted=1 WHERE date_entered<"2007-06-13" and smsgroup='contact';

/*
update contacts set idnumber = replace(idnumber, "010", "080") where school_id = 'sssssss-1111' and length(idnumber)=7
update contacts set idnumber = replace(idnumber, "090", "010") where school_id = '319cf9e5-6b54-dea0-0ffb-466799a2109e' and length(idnumber)=7
*/

update contacts set deleted=1 WHERE length(idnumber)=7;

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`, `portal_name`, `portal_active`, `portal_app`, `invalid_email`, `smsgroup`, `contact_status`, `picture_filename`, `picture_file_mime_type`, `school_id`, `vacation_total`, `vacation_used`, `is_available_strict`, `major`, `send_document_home`, `send_document_work`, `company_name`, `idnumber`, `nationality`, `gender`, `work_school`, `last_school_name`, `last_school_dept`, `study_aim`, `transport_fee_type`, `transportfee`, `incometax_rate`, `leave_date`, `enroll_date`, `grade`, `grade_entrance_date`, `application_date`, `id_card_field1`, `id_card_field2`, `id_card_field3`, `id_card_field4`, `renmei_label`, `custom_var1`, `custom_var2`, `custom_var3`, `custom_var4`, `custom_var5`, `custom_var6`, `custom_var7`, `custom_var8`, `custom_var9`, `custom_var10`, `custom_var11`, `custom_var12`, `custom_var13`, `custom_var14`, `custom_var15`, `custom_var16`, `custom_var17`, `custom_var18`, `custom_var19`, `custom_var20`) VALUES 
('a2167878-5253-1820-2c4d-46ccd33e2d1c', 0, '2007-08-23 00:23:00', '2007-08-23 00:23:22', '1', NULL, '1', NULL, NULL, 'スタッフ', NULL, 'Staff', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'off', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'off', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, 0, 'staff', '20', NULL, NULL, '319cf9e5-6b54-dea0-0ffb-466799a2109e', NULL, NULL, 0, NULL, 'on', 'on', NULL, '0900038', 'JP', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0.1, 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, NULL, NULL, NULL, '0', '0'),
('4c57951a-cb29-ec62-4eea-46cbc8768c87', 0, '2007-08-22 05:23:18', '2007-08-22 05:23:26', '1', NULL, '1', NULL, '麻佐代', '大場', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'off', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'off', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, 0, 'staff', '20', NULL, NULL, '319cf9e5-6b54-dea0-0ffb-466799a2109e', NULL, NULL, 0, NULL, 'on', 'on', NULL, '0900036', 'JP', 'female', NULL, NULL, NULL, NULL, NULL, NULL, 0.1, 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, NULL, NULL, NULL, '0', '0'),
('3f96659e-efaf-82c1-67cc-46cbbade2fce', 0, '2007-08-22 04:24:56', '2007-08-22 04:25:04', '1', NULL, '1', NULL, '由紀子', '渡部', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'off', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'off', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, 0, 'staff', '20', NULL, NULL, '319cf9e5-6b54-dea0-0ffb-466799a2109e', NULL, NULL, 0, NULL, 'on', 'on', NULL, '0900035', 'JP', 'female', NULL, NULL, NULL, NULL, NULL, NULL, 0.1, 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, NULL, NULL, NULL, '0', '0'),
('bf063dde-7251-c188-e634-46cbb9c49d5b', 0, '2007-08-22 04:20:52', '2007-08-22 04:22:41', '1', NULL, '1', NULL, '佳奈', '角谷', 'Kana', 'Sumitani ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'off', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'off', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, 0, 'staff', '20', NULL, NULL, '319cf9e5-6b54-dea0-0ffb-466799a2109e', NULL, NULL, 0, NULL, 'on', 'on', NULL, '0900034', 'JP', 'female', NULL, NULL, NULL, NULL, NULL, NULL, 0.1, 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, NULL, NULL, NULL, '0', '0'),
('8a62d4ea-eaa5-5be6-b251-46cb825baa62', 0, '2007-08-22 00:27:01', '2007-08-22 04:21:05', '1', NULL, '1', NULL, '愛子', '桜井', 'Chikako', 'Sakurai ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'off', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'off', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, 0, 'staff', '20', NULL, NULL, '319cf9e5-6b54-dea0-0ffb-466799a2109e', NULL, NULL, 0, NULL, 'on', 'on', NULL, '0100046', 'JP', 'female', NULL, NULL, NULL, NULL, NULL, NULL, 0.1, 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, NULL, NULL, NULL, '0', '0');


-- 2010-05-26
UPDATE `sms_memo` SET category_main='attention'  WHERE `parent_module` LIKE 'Student';

-- 20120-02-02
-- study_aim
SELECT * FROM `contacts` WHERE `study_aim` IS NOT NULL AND smsgroup = 'student'
UPDATE contacts SET study_aim=NULL WHERE study_aim IS NOT NULL AND smsgroup = 'student'

-- custom_var11
SELECT * FROM `contacts` WHERE custom_var11='other_teacher' AND smsgroup = 'student'
SELECT * FROM `contacts` WHERE custom_var11='graduate_reasearch'  AND smsgroup = 'student'
SELECT * FROM `contacts` WHERE custom_var11='marketing'  AND smsgroup = 'student'


UPDATE `contacts` SET custom_var11='english_teacher' WHERE custom_var11='other_teacher' AND smsgroup = 'student' ;
UPDATE `contacts` SET custom_var11='student' WHERE custom_var11='graduate_reasearch' AND smsgroup = 'student'; 
UPDATE `contacts` SET custom_var11='staff' WHERE custom_var11='marketing' AND smsgroup = 'student'; 

-- 2013-06-14
DELETE FROM emailman WHERE deleted=1 ;
UPDATE emailman SET in_queue=0, send_attempts=0, status=0 WHERE 1 ;