-- 1. 0000-00-00 00:00:00 timed instances

SELECT si.id AS instance_id, si.from_datetime, si.to_datetime, event_id, target_id, category, sis.from_datetime, sis.to_datetime, sis.id AS instances_id
FROM sms_event_instance AS si, smsevent_instances AS sis
WHERE si.parent_id=sis.event_id AND si.id=sis.instance_id
	AND (si.from_datetime!=sis.from_datetime OR si.to_datetime != sis.to_datetime)
	AND sis.from_datetime!='0000-00-00 00:00:00'
ORDER BY event_id, si.from_datetime

	
SELECT si.id AS instance_id, si.from_datetime, si.to_datetime, event_id, target_id, category, sis.from_datetime, sis.to_datetime, sis.id AS instances_id
FROM sms_event_instance AS si, smsevent_instances AS sis
WHERE si.parent_id=sis.event_id AND si.id=sis.instance_id
	AND (si.from_datetime!=sis.from_datetime OR si.to_datetime != sis.to_datetime)
	AND sis.from_datetime='0000-00-00 00:00:00'
	AND category='teacher'
ORDER BY event_id, si.from_datetime

UPDATE sms_event_instance, smsevent_instances
SET smsevent_instances.from_datetime=sms_event_instance.from_datetime
WHERE sms_event_instance.parent_id=smsevent_instances.event_id AND sms_event_instance.id=smsevent_instances.instance_id
	AND (sms_event_instance.from_datetime!=smsevent_instances.from_datetime OR sms_event_instance.to_datetime != smsevent_instances.to_datetime)
	AND smsevent_instances.from_datetime='0000-00-00 00:00:00' AND category='teacher'



SELECT si.id AS instance_id, si.from_datetime, si.to_datetime, event_id, target_id, category, sis.from_datetime, sis.to_datetime, sis.id AS instances_id
FROM sms_event_instance AS si, smsevent_instances AS sis
WHERE si.parent_id=sis.event_id AND si.id=sis.instance_id
	AND (si.from_datetime!=sis.from_datetime OR si.to_datetime != sis.to_datetime)
	AND sis.from_datetime='0000-00-00 00:00:00'
	AND category='classroom'
ORDER BY event_id, si.from_datetime

UPDATE sms_event_instance, smsevent_instances
SET smsevent_instances.from_datetime=sms_event_instance.from_datetime
WHERE sms_event_instance.parent_id=smsevent_instances.event_id AND sms_event_instance.id=smsevent_instances.instance_id
	AND (sms_event_instance.from_datetime!=smsevent_instances.from_datetime OR sms_event_instance.to_datetime != smsevent_instances.to_datetime)
	AND smsevent_instances.from_datetime='0000-00-00 00:00:00' AND category='classroom'


-- 2. event_contacts update	
	
SELECT * FROM `event_contacts` WHERE `start_date` IS NULL AND end_date IS NOT NULL

SELECT * FROM `event_contacts` WHERE `start_date`='0000-00-00 00:00:00' AND end_date IS NOT NULL


SELECT start_date, my_instances.min_from_datetime, contact_id, event_contacts.event_id, my_instances.target_id, my_instances.event_id
FROM event_contacts, (	
	SELECT MIN((from_datetime)) as min_from_datetime, smsevent_instances.event_id, target_id
	FROM smsevent_instances
	WHERE (status & 1021136) = 0  
	GROUP BY smsevent_instances.event_id, target_id
) AS my_instances
WHERE event_contacts.event_id=my_instances.event_id AND event_contacts.contact_id=my_instances.target_id 
	AND `start_date` IS NULL AND end_date IS NOT NULL
        		
UPDATE event_contacts, (	
	SELECT MIN((from_datetime)) as min_from_datetime, smsevent_instances.event_id, target_id
	FROM smsevent_instances
	WHERE (status & 1021136) = 0  
	GROUP BY smsevent_instances.event_id, target_id
) AS my_instances
SET start_date=my_instances.min_from_datetime
WHERE event_contacts.event_id=my_instances.event_id AND event_contacts.contact_id=my_instances.target_id 
	AND `start_date` IS NULL AND end_date IS NOT NULL
	
	
SELECT event_contacts.start_date, event_contacts20141208.start_date
FROM event_contacts, event_contacts20141208
WHERE event_contacts.id=event_contacts20141208.id AND event_contacts20141208.start_date='0000-00-00 00:00:00'  AND event_contacts.start_date IS NOT NULL
	
	
SELECT start_date, my_instances.min_from_datetime, contact_id, event_contacts.event_id, my_instances.target_id, my_instances.event_id
FROM event_contacts, (	
	SELECT MIN((from_datetime)) as min_from_datetime, smsevent_instances.event_id, target_id
	FROM smsevent_instances
	WHERE (status & 1021136) = 0  
	GROUP BY smsevent_instances.event_id, target_id
) AS my_instances
WHERE event_contacts.event_id=my_instances.event_id AND event_contacts.contact_id=my_instances.target_id 
	AND `start_date`='0000-00-00 00:00:00' AND end_date IS NOT NULL

UPDATE event_contacts, (	
	SELECT MIN((from_datetime)) as min_from_datetime, smsevent_instances.event_id, target_id
	FROM smsevent_instances
	WHERE (status & 1021136) = 0  
	GROUP BY smsevent_instances.event_id, target_id
) AS my_instances
SET start_date=my_instances.min_from_datetime
WHERE event_contacts.event_id=my_instances.event_id AND event_contacts.contact_id=my_instances.target_id 
	AND `start_date`='0000-00-00 00:00:00' AND end_date IS NOT NULL
	


-- 3. event_resource update	
	
	
SELECT start_date, my_instances.min_from_datetime, resource_id, event_resource.event_id, my_instances.target_id, my_instances.event_id
FROM event_resource, (	
	SELECT MIN((from_datetime)) as min_from_datetime, smsevent_instances.event_id, target_id
	FROM smsevent_instances
	WHERE (status & 1021136) = 0  
	GROUP BY smsevent_instances.event_id, target_id
) AS my_instances
WHERE event_resource.event_id=my_instances.event_id AND event_resource.resource_id=my_instances.target_id 
	AND `start_date`='0000-00-00 00:00:00' AND end_date IS NOT NULL

UPDATE event_resource, (	
	SELECT MIN((from_datetime)) as min_from_datetime, smsevent_instances.event_id, target_id
	FROM smsevent_instances
	WHERE (status & 1021136) = 0  
	GROUP BY smsevent_instances.event_id, target_id
) AS my_instances
SET start_date=my_instances.min_from_datetime
WHERE event_resource.event_id=my_instances.event_id AND event_resource.resource_id=my_instances.target_id 
	AND `start_date`='0000-00-00 00:00:00' AND end_date IS NOT NULL
	
SELECT event_resource.start_date, event_resource20141208.start_date
FROM event_resource, event_resource20141208
WHERE event_resource.id=event_resource20141208.id AND event_resource20141208.start_date='0000-00-00 00:00:00'  AND event_resource.start_date!='0000-00-00 00:00:00' 


-- 4. 0000-00-00 00:00:00 timed instances to_datetime
	
SELECT si.id AS instance_id, si.from_datetime, si.to_datetime, event_id, target_id, category, sis.from_datetime, sis.to_datetime, sis.id AS instances_id
FROM sms_event_instance AS si, smsevent_instances AS sis
WHERE si.parent_id=sis.event_id AND si.id=sis.instance_id
	AND (si.from_datetime!=sis.from_datetime OR si.to_datetime != sis.to_datetime)
	AND sis.to_datetime='0000-00-00 00:00:00'
	AND category='teacher'
ORDER BY event_id, si.to_datetime

UPDATE sms_event_instance, smsevent_instances
SET smsevent_instances.to_datetime=sms_event_instance.to_datetime
WHERE sms_event_instance.parent_id=smsevent_instances.event_id AND sms_event_instance.id=smsevent_instances.instance_id
	AND (sms_event_instance.from_datetime!=smsevent_instances.from_datetime OR sms_event_instance.to_datetime != smsevent_instances.to_datetime)
	AND smsevent_instances.to_datetime='0000-00-00 00:00:00' AND category='teacher'



SELECT si.id AS instance_id, si.from_datetime, si.to_datetime, event_id, target_id, category, sis.from_datetime, sis.to_datetime, sis.id AS instances_id
FROM sms_event_instance AS si, smsevent_instances AS sis
WHERE si.parent_id=sis.event_id AND si.id=sis.instance_id
	AND (si.from_datetime!=sis.from_datetime OR si.to_datetime != sis.to_datetime)
	AND sis.to_datetime='0000-00-00 00:00:00'
	AND category='classroom'
ORDER BY event_id, si.to_datetime

UPDATE sms_event_instance, smsevent_instances
SET smsevent_instances.to_datetime=sms_event_instance.to_datetime
WHERE sms_event_instance.parent_id=smsevent_instances.event_id AND sms_event_instance.id=smsevent_instances.instance_id
	AND (sms_event_instance.from_datetime!=smsevent_instances.from_datetime OR sms_event_instance.to_datetime != smsevent_instances.to_datetime)
	AND smsevent_instances.to_datetime='0000-00-00 00:00:00' AND category='classroom'


-- 5. event_resource update	end_date

SELECT start_date, end_date, my_instances.max_to_datetime, resource_id, event_resource.event_id, my_instances.target_id, my_instances.event_id
FROM event_resource, (	
	SELECT MAX((to_datetime)) as max_to_datetime, smsevent_instances.event_id, target_id
	FROM smsevent_instances
	WHERE (status & 1021136) = 0  
	GROUP BY smsevent_instances.event_id, target_id
) AS my_instances
WHERE event_resource.event_id=my_instances.event_id AND event_resource.resource_id=my_instances.target_id 
	AND `end_date`='0000-00-00 00:00:00' AND start_date IS NOT NULL

UPDATE event_resource, (	
	SELECT MAX((to_datetime)) as max_from_datetime, smsevent_instances.event_id, target_id
	FROM smsevent_instances
	WHERE (status & 1021136) = 0  
	GROUP BY smsevent_instances.event_id, target_id
) AS my_instances
SET end_date=my_instances.max_from_datetime
WHERE event_resource.event_id=my_instances.event_id AND event_resource.resource_id=my_instances.target_id 
	AND `end_date`='0000-00-00 00:00:00' AND start_date IS NOT NULL



-- 6. event_contacts update	
	
SELECT * FROM `event_contacts` WHERE `end_date` IS NULL AND start_date IS NOT NULL  -->   X

SELECT * FROM `event_contacts` WHERE `end_date`='0000-00-00 00:00:00' AND start_date IS NOT NULL

	
SELECT event_contacts.start_date, event_contacts20141208.start_date
FROM event_contacts, event_contacts20141208
WHERE event_contacts.id=event_contacts20141208.id AND event_contacts20141208.start_date='0000-00-00 00:00:00'  AND event_contacts.start_date IS NOT NULL
	
	
SELECT event_contacts.id, start_date, end_date, my_instances.max_to_datetime, contact_id, event_contacts.event_id, my_instances.target_id, my_instances.event_id
FROM event_contacts, (	
	SELECT MAX((to_datetime)) as max_to_datetime, smsevent_instances.event_id, target_id
	FROM smsevent_instances
	WHERE (status & 1021136) = 0  
	GROUP BY smsevent_instances.event_id, target_id
) AS my_instances
WHERE event_contacts.event_id=my_instances.event_id AND event_contacts.contact_id=my_instances.target_id 
	AND `end_date`='0000-00-00 00:00:00' AND start_date IS NOT NULL

UPDATE event_contacts, (	
	SELECT MAX((to_datetime)) as max_to_datetime, smsevent_instances.event_id, target_id
	FROM smsevent_instances
	WHERE (status & 1021136) = 0  
	GROUP BY smsevent_instances.event_id, target_id
) AS my_instances
SET end_date=my_instances.max_to_datetime
WHERE event_contacts.event_id=my_instances.event_id AND event_contacts.contact_id=my_instances.target_id 
	AND `end_date`='0000-00-00 00:00:00' AND start_date IS NOT NULL