1. students with no schedule athough their status is 入金 
- make nenki selectabe by user (default nenki from course listview)
- make contacts.idnumber and smsevent.commoncode links to the corresponding records  (query took 122 seconds)
- add smsevent.school_id to the list (use get_school_name_static($this->data_row['school_id']) )

SELECT contact_id, last_name, first_name, idnumber, event_id, common_code, smsevent.name, start_date, end_date 
FROM event_contacts, smsevent, smsevent_sub1, contacts
WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND smsevent.id=smsevent_sub1.id_sub1 
	AND contacts.id=event_contacts.contact_id AND event_contact_status=2 AND event_contacts.deleted=0 
	AND smsevent_sub1.nenki ='201501' AND start_date IS NULL 
ORDER BY event_id
	
-> unfortunately above query takes too much time (159 sec on honban data for 17 results, 
so apply the below query (7 sec) and then retrive the detailed information for course and student

SELECT contact_id, event_id, start_date, end_date FROM event_contacts, smsevent_sub1 
WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contact_status=2 
	AND event_contacts.deleted=0 AND smsevent_sub1.nenki = '201501' AND start_date IS NULL 
ORDER BY event_id


2. course statistics checks, comare dansei/josei toroku/yoyaku numbers with event_contacts numbers
- get the nenki from the user, default is like Course LW
- an optional course popup would be useful
- course related information (name, nenki, common_code) can be get via smsevent_sub1.id_sub1
- add smsevent.school_id to the list (use get_school_name_static($this->data_row['school_id']) )

SELECT event_contacts_count, teiin, nenki, (shinki_yoyaku_no + keizoku_yoyaku_no + shinki_toroku_no + keizoku_toroku_no) AS shinki_keizoku_c, 
	(dansei_toroku_no + josei_toroku_no + dansei_yoyaku_no+ josei_yoyaku_no) as danjo_c, 
	id_sub1 AS course_id, shinki_toroku_no, keizoku_toroku_no, shinki_yoyaku_no, keizoku_yoyaku_no, dansei_toroku_no, 
	josei_toroku_no, dansei_yoyaku_no, josei_yoyaku_no, sai_toroku_no, sai_yoyaku_no, tein_type, teiin_sub1, teiin_sub2 
FROM smsevent_sub1, smsevent_sub3, (
	SELECT count(contact_id) AS event_contacts_count, event_id FROM event_contacts, smsevent_sub1
	WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contacts.deleted=0 AND event_contact_status IN ('1', '2') 
		AND smsevent_sub1.nenki='201501'
	GROUP BY event_id
) AS my_ec
WHERE smsevent_sub1.nenki='201501' AND smsevent_sub1.id_sub1=smsevent_sub3.id_sub3 AND my_ec.event_id=id_sub1 
	AND event_contacts_count!=(shinki_yoyaku_no + keizoku_yoyaku_no + shinki_toroku_no + keizoku_toroku_no)

- below one is optional -> when teiin_type is dansei/josei, this is checking whether dansei/josei totals are maching shinki/keizoku totals
SELECT  (dansei_toroku_no + josei_toroku_no + dansei_yoyaku_no+  josei_yoyaku_no) AS total_danjo, 
	(shinki_yoyaku_no + keizoku_yoyaku_no + shinki_toroku_no + keizoku_toroku_no) AS total_toroku, id_sub1 AS course_id, 
	shinki_toroku_no, keizoku_toroku_no, shinki_yoyaku_no, keizoku_yoyaku_no, dansei_toroku_no, josei_toroku_no, 
	dansei_yoyaku_no, josei_yoyaku_no, sai_toroku_no, sai_yoyaku_no, teiin, tein_type, teiin_sub1, teiin_sub2 
FROM smsevent_sub1, smsevent_sub3 WHERE smsevent_sub1.id_sub1=smsevent_sub3.id_sub3 AND tein_type=2
	AND (dansei_toroku_no + josei_toroku_no + dansei_yoyaku_no+  josei_yoyaku_no)!=(shinki_yoyaku_no + keizoku_yoyaku_no + shinki_toroku_no + keizoku_toroku_no)
	AND smsevent_sub1.nenki='201501' 
	
	
3. For a given nenki, which courses have wrong actual_number_of_occurrences
- course related information (name, nenki, common_code) can be get via sms_recurrence.id
- add smsevent.school_id to the list (use get_school_name_static($this->data_row['school_id']) )
- nenki is input by the user (default is like in the LW of course)
- 918592 is from CourseInstanceCancelledRescheduledOrigin
- query takes 2 sec on honban data for 2 records)

SELECT actual_number_of_occurrences, my_instance.instance_count, sms_recurrence.id
FROM  sms_recurrence, smsevent_sub1,
 ( 	SELECT count(sms_event_instance.id)  AS instance_count, sms_event_instance.parent_id FROM sms_event_instance, smsevent_sub1 AS sr2
 	WHERE sms_event_instance.parent_id=sr2.id_sub1 AND sr2.nenki='201501' AND  (sms_event_instance.status & 918592) = 0
 	GROUP BY sms_event_instance.parent_id
 ) AS my_instance
WHERE smsevent_sub1.id_sub1=sms_recurrence.id AND my_instance.parent_id=smsevent_sub1.id_sub1 
	AND my_instance.instance_count!=actual_number_of_occurrences
	
	
4. combini torikomi error
- user inputs a combini ID and related information is shown (usually, that transaction is deleted and no matching could be done for combini torikomi)
- add smsevent.school_id to the list (use get_school_name_static($this->data_row['school_id']) )

SELECT contact_id, idnumber, event_id, common_code, sell_date, in_amount, fee_name, salesno, combini_sakusei_id
FROM yoyaku_transactions_deleted, contacts, smsevent
WHERE yoyaku_transactions_deleted.contact_id=contacts.id AND yoyaku_transactions_deleted.event_id=smsevent.id 
	AND combini_sakusei_id = '1002000000000177'
	
	
5. sms_fee total_paid inconsistency which is used in Fee ListView
- add smsevent.name, smsevent.common_code, smsevent.school_id
- add smsevent.school_id to the list (use get_school_name_static($this->data_row['school_id']) )

SELECT sms_fee.id, sms_fee.contact_id, sms_fee.event_id, sms_fee.fee_subcategory, 
	sms_fee.basic_fee, in_amount, total_paid, actual_in_amount, payment_type, t_date
FROM sms_fee, sms_transaction
WHERE sms_fee.id=sms_transaction.fee_id AND sms_fee.deleted=0 AND sms_transaction.deleted=0 
	AND actual_in_amount!=0 AND actual_in_amount!=total_paid