-- 20070227 metehan  => delete old teachers and their schedules

UPDATE `contacts` SET `contacts`.`smsgroup` = 'xxx' WHERE `smsgroup` LIKE 'teacher' AND date_entered < '2007-02-27 00:00:00' ;
DELETE smsevent_instances.* FROM smsevent_instances JOIN contacts2 ON smsevent_instances.target_id = contacts2.id WHERE contacts2.smsgroup = 'xxx';



-- 20070227 metehan  => delete old students and their schedules
UPDATE `contacts` SET `contacts`.`smsgroup` = 'old_student' WHERE `smsgroup` LIKE 'student' AND date_entered < '2007-02-27 00:00:00' ;
DELETE smsevent_instances.* FROM smsevent_instances JOIN contacts2 ON smsevent_instances.target_id = contacts2.id WHERE contacts2.smsgroup = 'old_student';


-- 2007-02-28 -- merdan -- ACCCOUNTS UPDATES
-- custom_var 51 to satisfy pulldown menu
-- SELECT DISTINCT (custom_var51), count( custom_var51 ) FROM accounts GROUP BY custom_var51;

UPDATE accounts SET custom_var51='12month' WHERE custom_var51='12ヶ月';
UPDATE accounts SET custom_var51='1month' WHERE custom_var51='1ヶ月'   ; 
UPDATE accounts SET custom_var51='final' WHERE custom_var51='Final'   ;
UPDATE accounts SET custom_var51='mid-term' WHERE custom_var51='Mid-term'   ;
UPDATE accounts SET custom_var51='' WHERE custom_var51='なし'   ;
UPDATE accounts SET custom_var51='everyleveltest' WHERE custom_var51='レベルテスト毎'   ;
UPDATE accounts SET custom_var51='2month' WHERE custom_var51='２ヶ月'   ;
UPDATE accounts SET custom_var51='3month' WHERE custom_var51='３ヶ月'   ;
UPDATE accounts SET custom_var51='6month' WHERE custom_var51='６ヶ月'   ;

-- custom_var 53 to satisfy pulldown menu
-- SELECT DISTINCT (custom_var53), count( custom_var53 ) FROM accounts GROUP BY custom_var53;

UPDATE accounts SET custom_var53='final' WHERE custom_var53='Final'   ;
UPDATE accounts SET custom_var53='everyleveltest' WHERE custom_var53='レベルテスト毎'   ;
UPDATE accounts SET custom_var53='6month' WHERE custom_var53='６ヶ月'   ;


-- custom_var 52 to satisfy pulldown menu
-- SELECT DISTINCT (custom_var52), count( custom_var52 ) FROM accounts GROUP BY custom_var52;

UPDATE accounts SET custom_var52='pdf' WHERE custom_var52='PDF'   ;
UPDATE accounts SET custom_var52='wordsave' WHERE custom_var52='Word(保護つき)'   ;
UPDATE accounts SET custom_var52='wordwithoutsave' WHERE custom_var52='Word(保護なし)'   ;
UPDATE accounts SET custom_var52='mail' WHERE custom_var52='郵送'   ;

-- custom_var 54 to satisfy pulldown menu
-- SELECT DISTINCT (custom_var54), count( custom_var54 ) FROM accounts GROUP BY custom_var54;

UPDATE accounts SET custom_var54='wordsave' WHERE custom_var54='Word(保護つき)'   ;
UPDATE accounts SET custom_var54='mail' WHERE custom_var54='郵送'   ;

-- custom_var 55 to satisfy pulldown menu
-- SELECT DISTINCT (custom_var55), count( custom_var55 ) FROM accounts GROUP BY custom_var55;

UPDATE accounts SET custom_var55='wordsave' WHERE custom_var55='Word(保護つき)'   ;



-- 2007-02-28 -- merdan -- CONTACTS UPDATES
-- insert custom_var1 into assistant field
-- SELECT DISTINCT (custom_var1), count( custom_var1 ) FROM contacts GROUP BY custom_var1;
UPDATE contacts  SET contacts.assistant = contacts.custom_var1 where contacts.custom_var1 != '';

-- insert occupation into major field
-- SELECT DISTINCT (special02), count( special02 ) FROM contacts GROUP BY special02;
UPDATE contacts  SET contacts.major = contacts.special02 where contacts.special02 != '';

-- insert special03 into assistant_phone field
-- SELECT DISTINCT (special03), count( special03 ) FROM contacts GROUP BY special03;
UPDATE contacts  SET contacts.assistant_phone = contacts.special03 where contacts.special03 != '';



-- SELECT DISTINCT (special04), count( special04 ) FROM contacts GROUP BY special04;
-- SELECT description, special04 FROM `contacts` WHERE special04 !=''
UPDATE contacts SET contacts.description = CONCAT_WS("\r\n", contacts.description, CONCAT_WS(":", "名刺受取日",contacts.special04))
WHERE contacts.special04 != "";



-- SELECT DISTINCT (custom_var19), count( custom_var19 ) FROM contacts where smsgroup='teacher' GROUP BY custom_var19 ;
-- SELECT DISTINCT (custom_var18), count( custom_var18 ) FROM contacts where smsgroup='teacher' GROUP BY custom_var18 ;

UPDATE contacts  SET custom_var19 = '1' where custom_var19='英語' and smsgroup='teacher';
UPDATE contacts  SET custom_var19 = '2' where custom_var19='スペイン語' and smsgroup='teacher';

UPDATE contacts  SET custom_var18 = '1' where custom_var18='英語' and smsgroup='teacher';
UPDATE contacts  SET custom_var18 = '2' where custom_var18='スペイン語' and smsgroup='teacher';
UPDATE contacts  SET custom_var18 = '4' where custom_var18='ドイツ語' and smsgroup='teacher';
UPDATE contacts  SET custom_var18 = '8' where custom_var18='イタリア語' and smsgroup='teacher';
UPDATE contacts  SET custom_var18 = '16' where custom_var18='フランス語' and smsgroup='teacher';
UPDATE contacts  SET custom_var18 = '32' where custom_var18='中国語' and smsgroup='teacher';
UPDATE contacts  SET custom_var18 = '64' where custom_var18='広東語' and smsgroup='teacher';

UPDATE contacts SET custom_var19 = (custom_var19 + custom_var18) where custom_var18 !='' and 
custom_var19 != '' and smsgroup='teacher';



-- 20070301 MERDAN  delete old contacts
UPDATE `contacts` SET `contacts`.`deleted` = 1 WHERE `smsgroup` LIKE 'old_student' ;


-- 2007-03-01 Merdan update course names
UPDATE contacts, event_contacts, smsevent
SET smsevent.name = CONCAT_WS("_",CONCAT_WS( " ", contacts.salutation, smsevent.name), "Private")
where 
contacts.id = event_contacts.contact_id
AND 
event_contacts.event_id = smsevent.id
AND 
contacts.smsgroup = 'student' AND 
contacts.deleted=0 AND 
event_contacts.deleted=0 AND 
smsevent.deleted = 0 AND smsevent.subtype=2


-- 2007-03-02
UPDATE contacts SET nationality='IE' where nationality LIKE 'アイルランド';
UPDATE contacts SET nationality='US' where nationality LIKE 'アメリカ';
UPDATE contacts SET nationality='GB' where nationality LIKE 'イギリス';
UPDATE contacts SET nationality='HK' where nationality LIKE '香港';
UPDATE contacts SET nationality='IN' where nationality LIKE 'インド';
UPDATE contacts SET nationality='AT' where nationality LIKE 'オーストリア';
UPDATE contacts SET nationality='AU' where nationality LIKE 'オーストラリア';
UPDATE contacts SET nationality='NL' where nationality LIKE 'オランダ';
UPDATE contacts SET nationality='CA' where nationality LIKE 'カナダ';
UPDATE contacts SET nationality='KR' where nationality LIKE '韓国';
UPDATE contacts SET nationality='KE' where nationality LIKE 'ケニヤ';
UPDATE contacts SET nationality='SG' where nationality LIKE 'シンガポール';
UPDATE contacts SET nationality='CH' where nationality LIKE 'スイス';
UPDATE contacts SET nationality='LK' where nationality LIKE 'スリランカ';
UPDATE contacts SET nationality='TH' where nationality LIKE 'タイ';
UPDATE contacts SET nationality='TW' where nationality LIKE '台湾';
UPDATE contacts SET nationality='CN' where nationality LIKE '中国';
UPDATE contacts SET nationality='DE' where nationality LIKE 'ドイツ';
UPDATE contacts SET nationality='TO' where nationality LIKE 'トンガ';
UPDATE contacts SET nationality='JP' where nationality LIKE '日本';
UPDATE contacts SET nationality='NZ' where nationality LIKE 'ニュージーランド';
UPDATE contacts SET nationality='FJ' where nationality LIKE 'フィジー';
UPDATE contacts SET nationality='PH' where nationality LIKE 'フィリピン';
UPDATE contacts SET nationality='BR' where nationality LIKE 'ブラジル';
UPDATE contacts SET nationality='FR' where nationality LIKE 'フランス';
UPDATE contacts SET nationality='BE' where nationality LIKE 'ベルギー';
UPDATE contacts SET nationality='MY' where nationality LIKE 'マレーシア';
UPDATE contacts SET nationality='ZA' where nationality LIKE '南アフリカ';
UPDATE contacts SET nationality='RO' where nationality LIKE 'ルーマニア';
UPDATE contacts SET nationality='TR' where nationality LIKE 'トルコ';
UPDATE contacts SET nationality='SE' where nationality LIKE 'スウェーデン';
UPDATE contacts SET nationality='DK' where nationality LIKE 'デンマーク';
UPDATE contacts SET nationality='ES' where nationality LIKE 'スペイン';
UPDATE contacts SET nationality='FR' where nationality LIKE 'フランス語';
UPDATE contacts SET nationality='RU' where nationality LIKE 'ロシア';
UPDATE contacts SET nationality='PK' where nationality LIKE 'パキスタン';
UPDATE contacts SET nationality='FI' where nationality LIKE 'フィンランド';
UPDATE contacts SET nationality='PE' where nationality LIKE 'ペルー';
UPDATE contacts SET nationality='PL' where nationality LIKE 'ポーランド';
UPDATE contacts SET nationality='GB' where nationality LIKE 'スコットランド';
UPDATE contacts SET nationality='EC' where nationality LIKE 'エクアドル';

-- MACC custom 
UPDATE contacts SET nationality='HK' where nationality LIKE 'イギリス（香港）';
UPDATE contacts SET nationality='HK' where nationality LIKE '中国（香港）';
UPDATE contacts SET nationality='HK' where nationality LIKE '中国･香港';
UPDATE contacts SET nationality='AU' where nationality LIKE 'オーストラリア/イタリア/南アフ';
UPDATE contacts SET nationality='AU' where nationality LIKE 'オーストラリア/イギリス';
UPDATE contacts SET nationality='KG' where nationality LIKE 'キルギスタン';
UPDATE contacts SET nationality='CH' where nationality LIKE 'スイス人';
UPDATE contacts SET nationality='SE' where nationality LIKE 'スエーデン';
UPDATE contacts SET nationality='US' where nationality LIKE 'アメリカ/フランス';
UPDATE contacts SET nationality='FR' where nationality LIKE '英国系フランス';
UPDATE contacts SET nationality='DE' where nationality LIKE 'ドイツ/ポルトガル';
UPDATE contacts SET nationality='BE' where nationality LIKE 'ベルギー/ブラジル';
UPDATE contacts SET nationality='HK' where nationality LIKE '中国(香港）';






-- 2007-05-19 merdan 
update contacts set nationality=null where nationality='AF'


-- 2007-07-20
SELECT DISTINCT (
description
)
FROM sms_fee
WHERE LOCATE( '@', description ) > 0;

UPDATE sms_fee SET description=NULL WHERE LOCATE( '@', description ) > 0;


-- 2007-10-20  --- metehan
ALTER TABLE `sms_fee` ADD `school_id` VARCHAR ( 36 ) AFTER `id` ; 

UPDATE sms_fee, contacts SET sms_fee.school_id = contacts.school_id WHERE sms_fee.contact_id = contacts.id ;
UPDATE sms_fee, smsevent SET sms_fee.school_id = smsevent.school_id WHERE sms_fee.event_id = smsevent.id ;
UPDATE sms_fee, accounts SET sms_fee.school_id = accounts.school_id  WHERE sms_fee.account_id = accounts.id ;

-- 2012-08-15  course data report problem

SELECT accounts_contacts.id, contact_id, accounts_contacts.account_id, contacts.idnumber, contacts.company_name, accounts.id, accounts.name FROM accounts_contacts
LEFT JOIN contacts ON accounts_contacts.contact_id=contacts.id
LEFT JOIN accounts ON accounts_contacts.account_id=accounts.id
WHERE accounts_contacts.deleted=0 AND accounts_contacts.contact_id IN 
("32b74ce2-0372-4b56-eb07-4dcb86885fac", "457806df-6676-ce22-5452-4e3f9b5ce090","53f2e8fd-82af-3003-7bbb-4f584642a97f","80640f79-a190-57de-4d03-46afe6abade5",
"acc54ced-f0b8-dc2e-2356-45e42831360b","ad2f523e-0a22-eb89-61cd-45e4288b876a","d027bb3b-1887-50fd-f345-45e428db2b74","e2550b4b-8121-64de-1a30-45e42857bdec")

SELECT  

	event_contacts.*, "SMSVENT", smsevent.id, smsevent.name, "COURSE_CATEGORY", course_category.*, "ACCOUNT_CONTACTS", "ACCOUNTS", accounts.id, accounts.name,
	
	"OTHER",

	CONCAT_WS(' ', contacts.salutation, contacts.last_name, contacts.first_name) AS contact_name,		contacts.id AS id, si1.status AS instance_status, smsevent.subtype, contacts.idnumber, 
	smsevent.description AS course_description, event_contacts.event_contact_status AS event_contact_status, 
	contacts.company_name AS contact_company_name,
	accounts.name AS company_name, accounts.id AS account_id,
	SUBSTRING(DATE_ADD(contacts.date_modified, INTERVAL 540 MINUTE), 1, 16) AS date_modified, 
	smsevent.id AS event_id, smsevent.name AS course_name, 
	smsevent.parent_id AS category_id, course_category.name AS category_name, 	sms_event_instance.name AS event_instance_title, 	
	SUBSTRING(DATE_ADD(si1.to_datetime, INTERVAL 540 MINUTE),1,10) AS course_period,
	DATE_FORMAT(DATE_ADD(si1.to_datetime, INTERVAL 540 MINUTE),'%w') AS day_of_week, 
	si1.to_instance_id,

	DATE_FORMAT(DATE_ADD( si1.from_datetime, INTERVAL 540 MINUTE ) , '%T') AS start_time,
	DATE_FORMAT(DATE_ADD( si1.to_datetime, INTERVAL 540 MINUTE ) , '%T') AS end_time,
				
	DATE_FORMAT(DATE_ADD( si2.from_datetime, INTERVAL 540 MINUTE ) , '%Y-%m-%d %T') AS target_start_time,
	DATE_FORMAT(DATE_ADD( si2.to_datetime, INTERVAL 540 MINUTE ) , '%T') AS target_end_time,
			
	DATE_FORMAT(DATE_ADD( si3.from_datetime, INTERVAL 540 MINUTE ) , '%Y-%m-%d %T') AS origin_start_time,
	DATE_FORMAT(DATE_ADD( si3.to_datetime, INTERVAL 540 MINUTE ) , '%T') AS origin_end_time,
	
	TIME_TO_SEC(TIMEDIFF(si1.to_datetime , si1.from_datetime))/3600 AS total_time,
		
	teacher.id AS teacher_id, CONCAT_WS(' ', teacher.last_name, teacher.first_name ) AS teacher_name,
	teacher_instances.status AS teacher_instance_status, teacher_instances.to_instance_id AS teacher_to_instance_id
			
FROM contacts 
LEFT JOIN event_contacts ON event_contacts.contact_id=contacts.id AND event_contacts.deleted=0
LEFT JOIN smsevent ON smsevent.id = event_contacts.event_id AND smsevent.category='course' AND smsevent.deleted=0
LEFT JOIN course_category ON smsevent.parent_id = course_category.id AND course_category.deleted=0 	
LEFT JOIN accounts ON accounts.id=contacts.account_id 

LEFT JOIN smsevent_instances AS si1 ON si1.target_id = contacts.id AND smsevent.deleted=0 AND si1.category='student' AND si1.event_id = smsevent.id 
LEFT JOIN sms_event_instance ON si1.instance_id = sms_event_instance.id  	
LEFT JOIN sms_event_instance  AS si2 ON si1.to_instance_id = si2.id 
LEFT JOIN sms_event_instance  AS si3 ON si1.from_instance_id = si3.id 
LEFT JOIN smsevent_instances AS teacher_instances ON  teacher_instances.instance_id = si1.instance_id AND teacher_instances.category='teacher' 
	AND teacher_instances.event_id = smsevent.id AND smsevent.deleted=0 AND si1.to_datetime=teacher_instances.to_datetime 
LEFT JOIN event_contacts AS teacher_status ON teacher_status.event_id=teacher_instances.event_id AND teacher_status.deleted=0
	AND (teacher_status.event_contact_status=3 OR teacher_status.event_contact_status=4) AND teacher_instances.target_id = teacher_status.contact_id
LEFT JOIN contacts AS teacher ON teacher.id=teacher_status.contact_id AND teacher.smsgroup='teacher' AND teacher.deleted=0	
LEFT JOIN category_tree ON course_category.id = category_tree.self_id   

WHERE  contacts.deleted=0 AND contacts.smsgroup='student'  AND category_tree.node_id IN ( '4' )   AND smsevent.id='938a9b85-8f50-a396-9670-4eeaf25c16eb'
	AND DATE_FORMAT(DATE_ADD(si1.from_datetime, INTERVAL 540 MINUTE ), '%Y-%m-%d %h:%s' )  >= '2012-08-02 00:00:00'  
	AND DATE_FORMAT(DATE_ADD(si1.to_datetime, INTERVAL 540 MINUTE ), '%Y-%m-%d %h:%s' ) <= '2012-08-02 23:59:00'    
	
ORDER BY course_period, course_category.name DESC, smsevent.name DESC, event_contacts.event_contact_status ASC, start_time DESC, end_time DESC 


SELECT  
	si1.id, si1.instance_id, teacher_instances.id,
	CONCAT_WS(' ', contacts.salutation, contacts.last_name, contacts.first_name) AS contact_name,		contacts.id AS id, si1.status AS instance_status, smsevent.subtype, contacts.idnumber, 
	smsevent.description AS course_description, event_contacts.event_contact_status AS event_contact_status, 
	contacts.company_name,
	SUBSTRING(DATE_ADD(contacts.date_modified, INTERVAL 540 MINUTE), 1, 16) AS date_modified, 
	smsevent.id AS event_id, smsevent.name AS course_name, 
	smsevent.parent_id AS category_id, course_category.name AS category_name, 	sms_event_instance.name AS event_instance_title, 	
	SUBSTRING(DATE_ADD(si1.to_datetime, INTERVAL 540 MINUTE),1,10) AS course_period,
	DATE_FORMAT(DATE_ADD(si1.to_datetime, INTERVAL 540 MINUTE),'%w') AS day_of_week, 
	si1.to_instance_id,
	
	DATE_FORMAT(DATE_ADD( si1.from_datetime, INTERVAL 540 MINUTE ) , '%T') AS start_time,
	DATE_FORMAT(DATE_ADD( si1.to_datetime, INTERVAL 540 MINUTE ) , '%T') AS end_time,
				
	DATE_FORMAT(DATE_ADD( si2.from_datetime, INTERVAL 540 MINUTE ) , '%Y-%m-%d %T') AS target_start_time,
	DATE_FORMAT(DATE_ADD( si2.to_datetime, INTERVAL 540 MINUTE ) , '%T') AS target_end_time,
			
	DATE_FORMAT(DATE_ADD( si3.from_datetime, INTERVAL 540 MINUTE ) , '%Y-%m-%d %T') AS origin_start_time,
	DATE_FORMAT(DATE_ADD( si3.to_datetime, INTERVAL 540 MINUTE ) , '%T') AS origin_end_time,
	
	TIME_TO_SEC(TIMEDIFF(si1.to_datetime , si1.from_datetime))/3600 AS total_time,
			
	teacher.id AS teacher_id, CONCAT_WS(' ', teacher.last_name, teacher.first_name ) AS teacher_name,
	teacher_instances.status AS teacher_instance_status, teacher_instances.to_instance_id AS teacher_to_instance_id
			
FROM contacts 
LEFT JOIN event_contacts ON event_contacts.contact_id=contacts.id AND event_contacts.deleted=0
LEFT JOIN smsevent ON smsevent.id = event_contacts.event_id AND smsevent.category='course' AND smsevent.deleted=0
LEFT JOIN course_category ON smsevent.parent_id = course_category.id AND course_category.deleted=0 	
LEFT JOIN smsevent_instances AS si1 ON si1.target_id = contacts.id AND smsevent.deleted=0 AND si1.category='student'  	
LEFT JOIN sms_event_instance ON si1.instance_id = sms_event_instance.id  	
LEFT JOIN sms_event_instance  AS si2 ON si1.to_instance_id = si2.id 
LEFT JOIN sms_event_instance  AS si3 ON si1.from_instance_id = si3.id 
LEFT JOIN smsevent_instances AS teacher_instances ON  teacher_instances.instance_id = si1.instance_id AND teacher_instances.category='teacher' 
	AND teacher_instances.event_id = smsevent.id AND smsevent.deleted=0 AND si1.to_datetime=teacher_instances.to_datetime 
LEFT JOIN event_contacts AS teacher_status ON teacher_status.event_id=teacher_instances.event_id AND teacher_status.deleted=0
	AND (teacher_status.event_contact_status=3 OR teacher_status.event_contact_status=4) AND teacher_instances.target_id = teacher_status.contact_id
LEFT JOIN contacts AS teacher ON teacher.id=teacher_status.contact_id AND teacher.smsgroup='teacher' AND teacher.deleted=0	
LEFT JOIN category_tree ON course_category.id = category_tree.self_id   
	
WHERE  contacts.deleted=0 AND contacts.smsgroup='student'  AND category_tree.node_id IN ( '6721' )   
	AND DATE_FORMAT(DATE_ADD(si1.from_datetime, INTERVAL 540 MINUTE ), '%Y-%m-%d %h:%s' )  >= '2012-06-01 00:00:00'  
	AND DATE_FORMAT(DATE_ADD(si1.to_datetime, INTERVAL 540 MINUTE ), '%Y-%m-%d %h:%s' ) <= '2012-06-01 23:59:00'    
	AND smsevent.id='d249dfb1-878b-7725-b15d-4ec0a863e79e'
	
ORDER BY course_period, course_category.name DESC, smsevent.name DESC, event_contacts.event_contact_status ASC, start_time DESC, end_time DESC 



SELECT  
	si1.id, si1.instance_id, teacher_instances.id as t_i_id, teacher_instances.event_id, 
	CONCAT_WS(' ', contacts.salutation, contacts.last_name, contacts.first_name) AS contact_name,		contacts.id AS id, si1.status AS instance_status, smsevent.subtype, contacts.idnumber, 
	smsevent.description AS course_description, event_contacts.event_contact_status AS event_contact_status, 
	contacts.company_name,
	SUBSTRING(DATE_ADD(contacts.date_modified, INTERVAL 540 MINUTE), 1, 16) AS date_modified, 
	smsevent.id AS event_id, smsevent.name AS course_name, 
	smsevent.parent_id AS category_id, course_category.name AS category_name, 	sms_event_instance.name AS event_instance_title, 	
	SUBSTRING(DATE_ADD(si1.to_datetime, INTERVAL 540 MINUTE),1,10) AS course_period,
	DATE_FORMAT(DATE_ADD(si1.to_datetime, INTERVAL 540 MINUTE),'%w') AS day_of_week, 
	si1.to_instance_id,
	
	DATE_FORMAT(DATE_ADD( si1.from_datetime, INTERVAL 540 MINUTE ) , '%T') AS start_time,
	DATE_FORMAT(DATE_ADD( si1.to_datetime, INTERVAL 540 MINUTE ) , '%T') AS end_time,
				
	DATE_FORMAT(DATE_ADD( si2.from_datetime, INTERVAL 540 MINUTE ) , '%Y-%m-%d %T') AS target_start_time,
	DATE_FORMAT(DATE_ADD( si2.to_datetime, INTERVAL 540 MINUTE ) , '%T') AS target_end_time,
			
	DATE_FORMAT(DATE_ADD( si3.from_datetime, INTERVAL 540 MINUTE ) , '%Y-%m-%d %T') AS origin_start_time,
	DATE_FORMAT(DATE_ADD( si3.to_datetime, INTERVAL 540 MINUTE ) , '%T') AS origin_end_time,
	
	TIME_TO_SEC(TIMEDIFF(si1.to_datetime , si1.from_datetime))/3600 AS total_time,
			
	teacher.id AS teacher_id, CONCAT_WS(' ', teacher.last_name, teacher.first_name ) AS teacher_name,
	teacher_instances.status AS teacher_instance_status, teacher_instances.to_instance_id AS teacher_to_instance_id
			
FROM contacts 
LEFT JOIN event_contacts ON event_contacts.contact_id=contacts.id AND event_contacts.deleted=0
LEFT JOIN smsevent ON smsevent.id = event_contacts.event_id AND smsevent.category='course' AND smsevent.deleted=0
LEFT JOIN course_category ON smsevent.parent_id = course_category.id AND course_category.deleted=0 	
LEFT JOIN smsevent_instances AS si1 ON si1.target_id = contacts.id AND smsevent.deleted=0 AND si1.category='student'  	
LEFT JOIN sms_event_instance ON si1.instance_id = sms_event_instance.id  	
LEFT JOIN sms_event_instance  AS si2 ON si1.to_instance_id = si2.id 
LEFT JOIN sms_event_instance  AS si3 ON si1.from_instance_id = si3.id 
LEFT JOIN smsevent_instances AS teacher_instances ON  teacher_instances.instance_id = si1.instance_id AND teacher_instances.event_id=smsevent.id AND teacher_instances.category='teacher' 
LEFT JOIN contacts AS teacher ON teacher.id=teacher_instances.target_id AND teacher.smsgroup='teacher' AND teacher.deleted=0	
LEFT JOIN category_tree ON course_category.id = category_tree.self_id   
	
WHERE  contacts.deleted=0 AND contacts.smsgroup='student'  AND category_tree.node_id IN ( '6721' )   
	AND DATE_FORMAT(DATE_ADD(si1.from_datetime, INTERVAL 540 MINUTE ), '%Y-%m-%d %h:%s' )  >= '2012-06-01 00:00:00'  
	AND DATE_FORMAT(DATE_ADD(si1.to_datetime, INTERVAL 540 MINUTE ), '%Y-%m-%d %h:%s' ) <= '2012-06-01 23:59:00'    
	AND smsevent.id='d249dfb1-878b-7725-b15d-4ec0a863e79e'
	
ORDER BY course_period, course_category.name DESC, smsevent.name DESC, event_contacts.event_contact_status ASC, start_time DESC, end_time DESC