SELECT 	COUNT(smsevent_instances.event_id) AS number_of_occurrences, 
					CONCAT(contacts.last_name, ' ', IFNULL(contacts.first_name, '') ) AS contact_name,
					contacts.id AS contact_id, smsevent.id AS event_id,accounts.name AS account_name, accounts.id AS account_id,
					smsevent.name AS course_name, contacts.smsgroup, sms_fee.id AS fee_id, IFNULL(sms_fee.discount_price, sms_fee.basic_fee) AS sell_amount,
					sms_fee.name AS description, smsevent_instances.from_datetime AS t_date,
					event_contacts.start_date, event_contacts.end_date, sms_fee.start_date AS due_date, sms_fee.end_date AS t_date, sms_fee.sell_date 	,sms_fee.start_date as due_date, sms_fee.end_date as t_date 	FROM smsevent_instances 
					LEFT JOIN smsevent ON smsevent_instances.event_id = smsevent.id AND smsevent.deleted=0 
					LEFT JOIN event_contacts ON event_contacts.event_id = smsevent.id AND event_contacts.deleted=0
					CROSS JOIN contacts ON contacts.deleted=0 AND event_contacts.contact_id=contacts.id 
					CROSS JOIN sms_fee ON sms_fee.contact_id = contacts.id AND sms_fee.event_id = smsevent_instances.event_id AND sms_fee.deleted=0 
					LEFT JOIN sms_transactiontype ON sms_transactiontype.id = sms_fee.type_id
					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_fee.school_id = 'sssssss-1111' 	AND smsevent_instances.from_datetime <= event_contacts.end_date
					AND smsevent_instances.from_datetime >= event_contacts.start_date  	AND (smsevent_instances.instance_contact_status & 128) = 0   AND smsevent_instances.from_datetime < '2011-04-30 15:00:00 ' AND smsevent_instances.from_datetime >= '2011-03-31 15:00:00'  AND sms_transactiontype.keiri_code=401 AND sms_fee.applied_contact=1  AND contacts.smsgroup = 'student' AND smsevent.id = smsevent_instances.event_id AND contacts.id = smsevent_instances.target_id  	AND smsevent_instances.from_datetime <= CONCAT(sms_fee.end_date,' 23:59:59') 
						AND smsevent_instances.from_datetime >= CONCAT(sms_fee.start_date,' 00:00:00')   GROUP BY smsevent_instances.event_id, contacts.id, sms_fee.id  ORDER BY sms_fee.sell_date desc 
						
						
	SELECT 	COUNT(smsevent_instances.event_id) AS number_of_occurrences, 
						CONCAT(contacts.last_name, ' ', IFNULL(contacts.first_name, '') ) AS contact_name,
						contacts.id AS contact_id, 'student_based' AS event_id, accounts.name AS account_name, accounts.id AS account_id, 
						'' AS course_name, contacts.smsgroup, sms_fee.id AS fee_id, IFNULL(sms_fee.discount_price, sms_fee.basic_fee) AS sell_amount,
						sms_fee.name AS description, smsevent_instances.from_datetime AS t_date, sms_fee.start_date, sms_fee.end_date,sms_fee.sell_date
						FROM smsevent_instances 
						LEFT JOIN smsevent ON smsevent_instances.event_id = smsevent.id AND smsevent.deleted=0 
						LEFT JOIN event_contacts ON event_contacts.event_id = smsevent.id AND event_contacts.deleted=0
						CROSS JOIN contacts ON contacts.deleted=0 AND event_contacts.contact_id=contacts.id 
						CROSS JOIN sms_fee ON sms_fee.contact_id = contacts.id AND sms_fee.event_id IS NULL AND sms_fee.deleted=0 
						LEFT JOIN sms_transactiontype ON sms_transactiontype.id = sms_fee.type_id 
						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_fee.school_id = 'sssssss-1111' 	AND smsevent_instances.from_datetime <= event_contacts.end_date
						AND smsevent_instances.from_datetime >= event_contacts.start_date  	AND (smsevent_instances.instance_contact_status & 128) = 0   	AND smsevent_instances.from_datetime < '2011-04-30 15:00:00 ' AND smsevent_instances.from_datetime >= '2011-03-31 15:00:00'  	AND sms_transactiontype.keiri_code=403 AND sms_fee.applied_contact=1  	AND contacts.smsgroup = 'student' AND smsevent.id = smsevent_instances.event_id AND contacts.id = smsevent_instances.target_id  	AND (
							smsevent_instances.from_datetime <= CONCAT(sms_fee.end_date,' 23:59:59') 
							AND smsevent_instances.from_datetime >= CONCAT(sms_fee.start_date,' 00:00:00') 
						)  GROUP BY contacts.id, sms_fee.id  ORDER BY sms_fee.sell_date desc 
						
						
	SELECT contacts.id AS contact_id, smsevent.id AS course_id, 
					CONCAT(contacts.last_name, ' ', IFNULL(contacts.first_name, '') ) AS contact_name,
					accounts.name AS account_name, accounts.id AS account_id, smsevent.name AS course_name, contacts.smsgroup, 
					sms_fee.id AS fee_id, sms_fee.name AS description, IFNULL(sms_fee.discount_price, sms_fee.basic_fee) AS sell_amount, 
						 	sms_fee.start_date as start_date, 1 AS end_date,  	sms_fee.sell_date, '' AS t_date
					FROM sms_fee 
					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.deleted=0							
					LEFT JOIN sms_transactiontype ON sms_transactiontype.id = sms_fee.type_id 
					CROSS JOIN contacts ON event_contacts.contact_id=contacts.id AND sms_fee.contact_id = contacts.id AND contacts.deleted=0 
					LEFT JOIN accounts_contacts ON accounts_contacts.contact_id = contacts.id
					LEFT JOIN accounts ON accounts_contacts.account_id = accounts.id
					LEFT JOIN smsevent_instances ON sms_fee.event_id = smsevent_instances.event_id AND contacts.id = smsevent_instances.target_id    	WHERE sms_transactiontype.keiri_code!=401 AND sms_transactiontype.keiri_code!=403 
					AND sms_transactiontype.keiri_code!=407 AND sms_transactiontype.keiri_code!=406 AND sms_transactiontype.keiri_code!=302
					AND sms_transactiontype.keiri_code!=405 AND sms_fee.applied_contact=1  	AND contacts.smsgroup = 'student' AND sms_fee.deleted=0  AND sms_fee.school_id = 'sssssss-1111'  AND sms_fee.sell_date < '2011-04-30 15:00:00 ' AND sms_fee.sell_date >= '2011-03-31 15:00:00' 
					
					
	SELECT sms_transaction.payment_type, sms_fee.name AS description, sms_transaction.t_date, sms_transaction.id, 
					contacts.id AS contact_id, CONCAT(contacts.last_name, ' ', IFNULL(contacts.first_name, '') ) AS contact_name,
					accounts.name AS account_name, accounts.id AS account_id, contacts.smsgroup, 
					sms_transaction.sell_date AS start_date, 1 AS end_date, 
					sms_transaction.fee_id AS fee_id, sms_transaction.in_amount AS sell_amount, sms_fee.sell_date
					FROM sms_transaction, sms_fee, contacts				
					LEFT JOIN accounts_contacts ON contacts.id = accounts_contacts.contact_id
					LEFT JOIN accounts ON accounts_contacts.account_id = accounts.id
					WHERE sms_transaction.contact_id = contacts.id
						AND sms_transaction.event_id IS NULL AND (sms_fee.type_id NOT IN ('15', '16', '27', '28', '29', '35', '19', '5', '17' )) AND sms_transaction.deleted=0
						AND sms_transaction.fee_id = sms_fee.id AND sms_fee.deleted=0  AND contacts.smsgroup = 'student'  AND sms_fee.school_id = 'sssssss-1111'  AND sms_transaction.deleted=0 AND contacts.deleted=0  AND sms_transaction.sell_date < '2011-04-30 15:00:00 ' AND sms_transaction.sell_date >= '2011-03-31 15:00:00'