user_id IN ('39ff07ee-e072-49e0-4ecb-4f153a3621ff', '256a52ab-1d80-7db6-c8a1-4f153fdbaad0', '9d36df46-401f-40c1-f4bc-4f164527910d', 'b9768e82-745f-258c-1d18-4f1646c587eb')

contact_id IN ('df06f56d-647a-f14c-8585-4f153a875b3f', '59970536-fc47-55d9-5439-4f153ecda3bf', 'd09dfc6d-cf02-2540-f8b7-4f164558a105', '19ba6b55-b715-bc10-861a-4f1645367f37')

UPDATE contacts SET `deleted` = '1' WHERE id IN ('1900017', '1900021', '1900023', '1900026', '1900028', '1900031', '1900032', '1900033', '1900034', '1900035', '1900041', 
	'1900043', '1900044', '1900047', '1900048', '1900049', '1900053', '1900067', '1900069', '1900072', '1900073', '1900078', '1900079', '1900092', '1900097', '1900108', '1900111', '1900113', '1900116', '1900117', '1900120', 
	'1900127', '1900128', '1900129', '1900130', '1900131', '1900141', '1900143', '1900146', '1900147', '1900153', '1900155', '1900158', 
	'1900161', '1900162', '1900163', '1900164', '1900165', '1900166', '1900167', '1900168', '1900169')
	

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_entered`, `grade_entrance_date`, `application_date`, `extra_date`, `id_card_field1`, `id_card_field2`, `id_card_field3`, `id_card_field4`, `renmei_label`, `card_id`, `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
('dfae9e5b-da79-185e-5318-4f843186378c', 0, '2012-04-10 13:09:42', '2012-04-10 13:09:50', '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, 'sssssss-1111', NULL, NULL, 0, NULL, 'on', 'on', NULL, '1900216', ' ', 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, NULL, NULL, '0', '0');


SELECT id, idnumber, last_name FROM `contacts` WHERE idnumber IN  ('1900098', '1900142', '1900201', '1900202', '1900203', '1900204', '1900207', '1900208', '1900209', '1900210', '1900212', '1900213', '1900216')


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`, `kantan_login_id`) VALUES
('1900216', '1900216', NULL, NULL, 'off', '0', NULL, '2012-04-10 13:14:22', '2012-04-10 13:14:22', '1', '1', 'Inactive', '', 0, 0, 0, 'dfae9e5b-da79-185e-5318-4f843186378c', 'staff', NULL),
('1900201', '1900201', NULL, NULL, 'off', '0', NULL, '2012-04-10 13:14:00', '2012-04-10 13:14:00', '1', '1', 'Inactive', '', 0, 0, 0, '7b4f234c-7d49-d8bd-92a0-4f84013a5529', 'staff', NULL),
('1900203', '1900203', NULL, NULL, 'off', '0', NULL, '2012-04-10 13:14:00', '2012-04-10 13:14:00', '1', '1', 'Inactive', '', 0, 0, 0, '7cc04242-b69a-fe3b-f81b-4f8401d5c5c0', 'staff', NULL),
('1900204', '1900204', NULL, NULL, 'off', '0', NULL, '2012-04-10 13:14:00', '2012-04-10 13:14:00', '1', '1', 'Inactive', '', 0, 0, 0, '7e2af362-524b-943d-6d61-4f8401c97f3c', 'staff', NULL),
('1900202', '1900202', NULL, NULL, 'off', '0', NULL, '2012-04-10 13:14:00', '2012-04-10 13:14:00', '1', '1', 'Inactive', '', 0, 0, 0, '805772cf-c77c-134e-7c35-4f83f8f2ec4d', 'staff', NULL),
('1900207', '1900207', NULL, NULL, 'off', '0', NULL, '2012-04-10 13:14:00', '2012-04-10 13:14:00', '1', '1', 'Inactive', '', 0, 0, 0, '82a2ddb1-5c6d-4873-3bf9-4f8401c90c27', 'staff', NULL),
('1900208', '1900208', NULL, NULL, 'off', '0', NULL, '2012-04-10 13:14:00', '2012-04-10 13:14:00', '1', '1', 'Inactive', '', 0, 0, 0, '841a10b3-d512-481c-4805-4f840187e2a3', 'staff', NULL),
('1900209', '1900209', NULL, NULL, 'off', '0', NULL, '2012-04-10 13:14:00', '2012-04-10 13:14:00', '1', '1', 'Inactive', '', 0, 0, 0, '859aa258-1160-5f8a-47bd-4f84013541a4', 'staff', NULL),
('1900210', '1900210', NULL, NULL, 'off', '0', NULL, '2012-04-10 13:14:00', '2012-04-10 13:14:00', '1', '1', 'Inactive', '', 0, 0, 0, '87142077-bb09-72ee-4d1f-4f8401974f2c', 'staff', NULL),
('1900212', '1900212', NULL, NULL, 'off', '0', NULL, '2012-04-10 13:14:00', '2012-04-10 13:14:00', '1', '1', 'Inactive', '', 0, 0, 0, '88b2f6e7-bbe7-15db-b3b7-4f840127fa1e', 'staff', NULL),
('1900213', '1900213', NULL, NULL, 'off', '0', NULL, '2012-04-10 13:14:00', '2012-04-10 13:14:00', '1', '1', 'Inactive', '', 0, 0, 0, '8a421e51-dc31-f0a7-b4cc-4f8401a64a1e', 'staff', NULL),
('1900098', '1900098', NULL, NULL, 'off', '0', NULL, '2012-04-10 13:14:00', '2012-04-10 13:14:00', '1', '1', 'Inactive', '', 0, 0, 0, 'b0c24403-0123-1c97-c23f-4f84013575b6', 'staff', NULL),
('1900142', '1900142', NULL, NULL, 'off', '0', NULL, '2012-04-10 13:14:00', '2012-04-10 13:14:00', '1', '1', 'Inactive', '', 0, 0, 0, 'c129df95-ccc4-fdc5-cdfd-4f840116bf60', 'staff', NULL);

SELECT id, created_by, custom_var5, smsgroup FROM `contacts` WHERE custom_var5 IS NOT NULL
SELECT id, created_by, custom_var1, smsgroup FROM `contacts` WHERE custom_var1 IS NOT NULL AND smsgroup='teacher'

UPDATE contacts SET created_by=custom_var5 WHERE custom_var5 IS NOT NULL;
UPDATE contacts SET created_by=custom_var1 WHERE custom_var1 IS NOT NULL AND smsgroup='teacher';


INSERT INTO `resource` (`id`, `school_id`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `name`, `shortname`, `description`, `category`, `location`, `deleted`, `capacity`, `resource_attributes`, `common_code`, `activation_key`) VALUES
('5b31e50f-7794-e0f7-26d0-4f846ea4099e', 'sssssss-1111', '2012-04-10 17:32:22', '2012-04-10 17:32:22', '1', '1', 'A', NULL, NULL, 'classroom', NULL, 0, NULL, 'YTowOnt9', NULL, ''),
('14a5e02d-1f2f-5b00-6e1c-4f846e0483a4', 'sssssss-1111', '2012-04-10 17:32:36', '2012-04-10 17:32:36', '1', '1', 'B', NULL, NULL, 'classroom', NULL, 0, NULL, 'YTowOnt9', NULL, ''),
('b738009e-334f-102b-122f-4f846e5e2861', 'sssssss-1111', '2012-04-10 17:32:44', '2012-04-10 17:32:44', '1', '1', '101', NULL, NULL, 'classroom', NULL, 0, NULL, 'YTowOnt9', NULL, ''),
('c9088a6a-129e-8d37-0469-4f846ecece4c', 'sssssss-1111', '2012-04-10 17:32:52', '2012-04-10 17:32:52', '1', '1', '102', NULL, NULL, 'classroom', NULL, 0, NULL, 'YTowOnt9', NULL, '');


SELECT * FROM `contacts` WHERE `idnumber` LIKE '114%' 

UPDATE contacts SET created_by=custom_var5 WHERE custom_var5 IS NOT NULL AND idnumber LIKE '114%' ;

SELECT * FROM `smsevent`
WHERE parent_id IN ('1260050b-6cf5-555a-c476-4f86d072a4ad', '7ffc8695-6940-0ee9-8b8b-4f86d07abf64', '4fe03ce9-bc5f-487a-30fb-4f86d0405e8d', '5fe9a901-0f0b-939b-a0df-4f86d0a38fec', 'c26c06c8-b665-d54d-b330-4f86d0664a34', '375764e3-3025-10a5-d45a-4f86d18bbb34', '8795f7e4-c6ca-f7a2-b3f2-4f86d1e68e1b', '1fb87a24-9e20-cbee-7e76-4f86d195312a', '9289cb2b-3df3-3488-2a0a-4f86d3a30e7b')


UPDATE category_tree SET parent_node_id='9030' WHERE self_id
IN (
'1260050b-6cf5-555a-c476-4f86d072a4ad', '7ffc8695-6940-0ee9-8b8b-4f86d07abf64', '4fe03ce9-bc5f-487a-30fb-4f86d0405e8d', '5fe9a901-0f0b-939b-a0df-4f86d0a38fec', 'c26c06c8-b665-d54d-b330-4f86d0664a34', '375764e3-3025-10a5-d45a-4f86d18bbb34', '8795f7e4-c6ca-f7a2-b3f2-4f86d1e68e1b', '1fb87a24-9e20-cbee-7e76-4f86d195312a', '9289cb2b-3df3-3488-2a0a-4f86d3a30e7b'
);

UPDATE course_category SET school_id='8b28ff27-a39f-9c17-9813-4f877a6e0aa7', parent_id='557db709-92d7-f234-3dff-4f8c16a25b92' WHERE id    	
IN (
'1260050b-6cf5-555a-c476-4f86d072a4ad', '7ffc8695-6940-0ee9-8b8b-4f86d07abf64', '4fe03ce9-bc5f-487a-30fb-4f86d0405e8d', '5fe9a901-0f0b-939b-a0df-4f86d0a38fec', 'c26c06c8-b665-d54d-b330-4f86d0664a34', '375764e3-3025-10a5-d45a-4f86d18bbb34', '8795f7e4-c6ca-f7a2-b3f2-4f86d1e68e1b', '1fb87a24-9e20-cbee-7e76-4f86d195312a', '9289cb2b-3df3-3488-2a0a-4f86d3a30e7b'
);

UPDATE smsevent SET school_id='8b28ff27-a39f-9c17-9813-4f877a6e0aa7' 
WHERE parent_id
IN (
'1260050b-6cf5-555a-c476-4f86d072a4ad', '7ffc8695-6940-0ee9-8b8b-4f86d07abf64', '4fe03ce9-bc5f-487a-30fb-4f86d0405e8d', '5fe9a901-0f0b-939b-a0df-4f86d0a38fec', 'c26c06c8-b665-d54d-b330-4f86d0664a34', '375764e3-3025-10a5-d45a-4f86d18bbb34', '8795f7e4-c6ca-f7a2-b3f2-4f86d1e68e1b', '1fb87a24-9e20-cbee-7e76-4f86d195312a', '9289cb2b-3df3-3488-2a0a-4f86d3a30e7b'
);


SELECT sms_event_instance.parent_id, from_datetime FROM sms_event_instance
INNER JOIN smsevent ON sms_event_instance.parent_id=smsevent.id AND smsevent.deleted=0 AND smsevent.school_id='sssssss-1111' AND date_entered <= '2012-04-10 10:43:14'
WHERE sms_event_instance.deleted=0 AND smsevent.school_id='sssssss-1111' 
GROUP BY sms_event_instance.parent_id, from_datetime HAVING COUNT(sms_event_instance.parent_id)>1

SELECT smsevent_instances.event_id, from_datetime FROM smsevent_instances
INNER JOIN smsevent ON smsevent_instances.event_id=smsevent.id AND smsevent.deleted=0 AND smsevent.school_id='sssssss-1111' AND date_entered <= '2012-04-10 10:43:14'
WHERE smsevent.school_id='sssssss-1111' AND smsevent_instances.category='student'
GROUP BY smsevent_instances.event_id, smsevent_instances.target_id, from_datetime HAVING COUNT(smsevent_instances.event_id)>1


-- data discrepency in schedules

SELECT smsevent.name, smsevent.common_code, smsevent.id, smsevent_instances.instance_id, sms_event_instance.from_datetime as clas_fromtime, smsevent_instances.from_datetime as person_fromtime
FROM sms_event_instance
INNER JOIN smsevent_instances ON smsevent_instances.instance_id=sms_event_instance.id AND smsevent_instances.event_id=sms_event_instance.parent_id
INNER JOIN smsevent ON smsevent_instances.event_id=smsevent.id AND sms_event_instance.parent_id=smsevent.id AND smsevent.deleted=0
WHERE sms_event_instance.from_datetime!=smsevent_instances.from_datetime AND sms_event_instance.deleted=0
AND smsevent_instances.instance_id=''


SELECT DISTINCT smsevent.common_code
FROM sms_event_instance
INNER JOIN smsevent_instances ON smsevent_instances.instance_id=sms_event_instance.id AND smsevent_instances.event_id=sms_event_instance.parent_id
INNER JOIN smsevent ON smsevent_instances.event_id=smsevent.id AND sms_event_instance.parent_id=smsevent.id AND smsevent.deleted=0
WHERE sms_event_instance.from_datetime!=smsevent_instances.from_datetime AND sms_event_instance.deleted=0 

SELECT DISTINCT smsevent_instances.instance_id
FROM sms_event_instance
INNER JOIN smsevent_instances ON smsevent_instances.instance_id=sms_event_instance.id AND smsevent_instances.event_id=sms_event_instance.parent_id
INNER JOIN smsevent ON smsevent_instances.event_id=smsevent.id AND sms_event_instance.parent_id=smsevent.id AND smsevent.deleted=0
WHERE sms_event_instance.from_datetime!=smsevent_instances.from_datetime AND sms_event_instance.deleted=0


SELECT smsevent.name, smsevent.common_code, smsevent.id, smsevent_instances.instance_id, sms_event_instance.from_datetime as class_fromtime, smsevent_instances.from_datetime as person_fromtime,
	sms_event_instance.to_datetime as class_totime, smsevent_instances.to_datetime as person_totime
FROM sms_event_instance
INNER JOIN smsevent_instances ON smsevent_instances.instance_id=sms_event_instance.id AND smsevent_instances.event_id=sms_event_instance.parent_id
INNER JOIN smsevent ON smsevent_instances.event_id=smsevent.id AND sms_event_instance.parent_id=smsevent.id AND smsevent.deleted=0
WHERE sms_event_instance.from_datetime!=smsevent_instances.from_datetime AND sms_event_instance.deleted=0
AND smsevent_instances.instance_id='793bd429-6277-ac82-831b-4f86d7035e77'


UPDATE smsevent_instances SET from_datetime='2011-03-24 04:00:00', to_datetime='2011-03-24 06:00:00'
WHERE instance_id='793bd429-6277-ac82-831b-4f86d7035e77'


SELECT smsevent.name, smsevent.common_code, smsevent.id, smsevent_instances.instance_id, sms_event_instance.from_datetime as class_fromtime, smsevent_instances.from_datetime as person_fromtime,
	sms_event_instance.to_datetime as class_totime, smsevent_instances.to_datetime as person_totime
FROM sms_event_instance, smsevent_instances, smsevent
WHERE smsevent_instances.instance_id=sms_event_instance.id AND smsevent_instances.event_id=sms_event_instance.parent_id
	AND smsevent_instances.event_id=smsevent.id AND sms_event_instance.parent_id=smsevent.id AND smsevent.deleted=0
	AND sms_event_instance.from_datetime!=smsevent_instances.from_datetime AND sms_event_instance.deleted=0
	AND smsevent_instances.instance_id='7ea7d17c-19c5-b9a0-7550-4f86d8bfb820' 

UPDATE sms_event_instance, smsevent_instances, smsevent
SET smsevent_instances.from_datetime=sms_event_instance.from_datetime, smsevent_instances.to_datetime=sms_event_instance.to_datetime
WHERE smsevent_instances.instance_id=sms_event_instance.id AND smsevent_instances.event_id=sms_event_instance.parent_id
	AND smsevent_instances.event_id=smsevent.id AND sms_event_instance.parent_id=smsevent.id AND smsevent.deleted=0
	AND sms_event_instance.from_datetime!=smsevent_instances.from_datetime AND sms_event_instance.deleted=0
	AND smsevent_instances.instance_id='793bd429-6277-ac82-831b-4f86d7035e77'