
-- 2007-03-19  merdan Update Contacts Status
-- former
UPDATE contacts SET contact_status=7 WHERE 
custom_var16 is not NULL AND  LOCATE('退学', custom_var16)
AND smsgroup='student'

-- former
UPDATE contacts SET contact_status=7 WHERE 
custom_var16 is not NULL AND  LOCATE('退会', custom_var16)
AND smsgroup='student'

-- application
UPDATE contacts SET contact_status=5 WHERE 
custom_var16 is not NULL AND  LOCATE('問合せ', custom_var16)
AND smsgroup='student'

-- on leave
UPDATE contacts SET contact_status=9 WHERE 
custom_var16 is not NULL AND  LOCATE('休学', custom_var16)
AND smsgroup='student'

-- trial
UPDATE contacts SET contact_status=2 WHERE 
custom_var16 is not NULL AND  LOCATE('体験', custom_var16)
AND smsgroup='student'

-- 2007-10-22  metehan  get all teacher users with dates and schools
SELECT users.user_name , users.date_entered , users.date_modified , users.status , users.contact_id,  resource.name
FROM users, contacts, resource
WHERE users.smsgroup LIKE 'teacher' AND users.contact_id = contacts.id AND contacts.school_id = resource.id
ORDER BY users.date_modified DESC ;

-- 2008-01-25  metehan  problem in juchu screen total keiyaku and yoteigaku does not match for 2008-01
-- JUCHU KEIYAKU  TOTAL
	SELECT contacts.last_name, contacts.first_name, basic_fee, sell_date, discount_price , sms_fee.id as fee_id, contacts.id as contact_id, salesno FROM sms_fee JOIN contacts ON sms_fee.contact_id = contacts.id AND contacts.deleted=0
	LEFT JOIN smsevent ON sms_fee.event_id = smsevent.id AND smsevent.deleted=0
	LEFT JOIN event_contacts ON event_contacts.event_id = smsevent.id AND event_contacts.contact_id = sms_fee.contact_id AND event_contacts.deleted=0
	LEFT JOIN sms_recurrence ON sms_fee.event_id = sms_recurrence.id AND contacts.smsgroup = 'student'  
	LEFT JOIN accounts_contacts ON contacts.id = accounts_contacts.contact_id
	LEFT JOIN accounts ON accounts_contacts.account_id = accounts.id 
	WHERE 1=1 AND sell_date>='2007-05-31 15:00:00' AND sell_date<='2007-06-30 15:00:00' AND 
		contacts.smsgroup = 'student'  AND sms_fee.school_id = '58362bd0-5d1e-c3f9-efd1-4441c972601c'  AND sms_fee.applied_contact=1  AND 
		(sms_fee.event_id IS NULL OR (sms_fee.event_id IS NOT NULL AND event_contacts.contact_id IS NOT NULL ) ) AND sms_fee.deleted=0 
		ORDER BY sell_date asc
		ORDER BY sms_fee.salesno
				
				
-- JUCHU YOTEIGAKU TOTAL

	SELECT distinct fee_id,  in_amount, sms_transaction.salesno FROM `sms_transaction`  	
	JOIN contacts ON sms_transaction.contact_id = contacts.id 
	LEFT JOIN sms_recurrence ON sms_transaction.event_id = sms_recurrence.id AND contacts.smsgroup = 'student'  
	LEFT JOIN smsevent ON sms_transaction.event_id = smsevent.id AND smsevent.deleted=0  
	LEFT JOIN sms_fee ON sms_transaction.fee_id = sms_fee.id AND sms_fee.deleted=0   
	LEFT JOIN  accounts_contacts ON contacts.id = accounts_contacts.contact_id 
	LEFT JOIN accounts ON accounts_contacts.account_id = accounts.id 
	WHERE 1=1 AND sms_transaction.sell_date>='2007-12-31 15:00:00' AND sms_transaction.sell_date<='2008-01-31 15:00:00' AND 
	contacts.smsgroup = 'student'  AND 
	due_date >= '2007-12-31 15:00:00' AND due_date <= '2008-01-31 15:00:00'  AND 
	sms_fee.school_id = '58362bd0-5d1e-c3f9-efd1-4441c972601c'  AND sms_transaction.deleted=0  
	ORDER BY sms_transaction.salesno