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. Maeukekin drill-down report
- make nenki selectabe by user (default nenki from course listview) -> no empty option (like in LWMaeukekinAnalysis1_AN)
- bumon_code (like in LWMaeukekinAnalysis1_AN)
- dates for maeukekin.nyukinbi (from-to) 
- make contacts.idnumber and smsevent.commoncode links to the corresponding records
- add smsevent.school_id to the list (use get_school_name_static($this->data_row['school_id']) )

SELECT sum(kingaku), course_id, name, maeukekin.school_id, bumon_code, nenki, maeukekin.common_code, nyukinbi, keijobi, fee_subcategory, kingaku, fee_id, kaisu
FROM maeukekin, smsevent
WHERE maeukekin.course_id=smsevent.id 
	AND nenki ='201501' 
	AND bumon_code='01'
	AND maeukekin.school_id='d32c6192-e6be-2370-c9ff-5385aae9d6e2' AND smsevent.school_id='d32c6192-e6be-2370-c9ff-5385aae9d6e2' 
	AND nyukinbi>='2014-10-01' AND nyukinbi<='2015-01-31'
GROUP BY course_id

- when a course is selected a drill-down menu shold appear -> 

SELECT sum(kingaku), student_id, course_id, name, maeukekin.school_id, bumon_code, nenki, maeukekin.common_code, nyukinbi, keijobi, fee_subcategory, kingaku, fee_id, kaisu
FROM maeukekin, smsevent
WHERE maeukekin.course_id=smsevent.id 
	AND nenki ='201501' 
	AND bumon_code='01'
	AND maeukekin.school_id='d32c6192-e6be-2370-c9ff-5385aae9d6e2' AND smsevent.school_id='d32c6192-e6be-2370-c9ff-5385aae9d6e2' 
	AND nyukinbi>='2014-10-01' AND nyukinbi<='2015-01-31'
	AND course_id='5f6f766b-243a-87cb-a04e-545dd47706c3'
GROUP BY student_id

4. Corresponding report for nyukinnippo regarding the above report
- make nenki selectabe by user (default nenki from course listview) -> no empty option (like in LWMaeukekinAnalysis1_AN)
- bumon_code (like in LWMaeukekinAnalysis1_AN)
- dates for t_date(regular) or sell_date (henkin) (from-to) 
- make contacts.idnumber and smsevent.commoncode links to the corresponding records
- add smsevent.school_id to the list (use get_school_name_static($this->data_row['school_id']) )

SELECT sum(in_amount), sms_transaction.event_id, sms_transaction.school_id, bumon_code, nenki, sms_transaction.date_modified, t_date
FROM sms_transaction, smsevent_sub1, sms_fee
WHERE sms_transaction.event_id=smsevent_sub1.id_sub1 AND sms_transaction.deleted=0 AND sms_fee.id=sms_transaction.fee_id AND sms_fee.deleted=0
	AND fee_subcategory IN ('受講料', '教材費', '設備費', '合計データ')
	AND nenki ='201501' 
	AND bumon_code='01'
	AND sms_transaction.school_id='d32c6192-e6be-2370-c9ff-5385aae9d6e2' 
	AND sms_transaction.date_modified>='2014-10-01 00:00:0' AND sms_transaction.date_modified<='2015-01-31 23:59:59'
GROUP BY sms_transaction.event_id

- when a course is selected a drill-down menu shold appear -> 

SELECT sum(in_amount), sms_transaction.contact_id, sms_transaction.event_id, sms_transaction.school_id, bumon_code, nenki, sms_transaction.date_modified, t_date
FROM sms_transaction, smsevent_sub1, sms_fee
WHERE sms_transaction.event_id=smsevent_sub1.id_sub1 AND sms_transaction.deleted=0 AND sms_fee.id=sms_transaction.fee_id AND sms_fee.deleted=0
	AND fee_subcategory IN ('受講料', '教材費', '設備費', '合計データ')
	AND nenki ='201501' 
	AND bumon_code='01'
	AND sms_transaction.school_id='d32c6192-e6be-2370-c9ff-5385aae9d6e2' 
	AND sms_transaction.date_modified>='2014-10-01 00:00:0' AND sms_transaction.date_modified<='2015-01-31 23:59:59'
	AND sms_transaction.event_id='5f6f766b-243a-87cb-a04e-545dd47706c3'
GROUP BY sms_transaction.contact_id
	
5. 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
	
	
6. 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'
	
	
7. 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
	

8. courses with no schedule athough goueki data is registered
- make nenki selectabe by user (default nenki from course listview)
- make 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 sms_fee.event_id, smsevent.name, smsevent.common_code, resource.name, sms_fee.basic_fee, SUBSTRING(sell_date, 1, 10) as sell_date, sms_fee.id as fee_id, actual_number_of_occurrences
FROM sms_fee
INNER JOIN resource ON sms_fee.school_id=resource.id
INNER JOIN smsevent ON sms_fee.event_id=smsevent.id AND smsevent.deleted=0
INNER JOIN sms_recurrence ON sms_fee.event_id=sms_recurrence.id AND (actual_number_of_occurrences IS NULL OR actual_number_of_occurrences=0)
WHERE sms_fee.fee_subcategory='合計データ' AND sms_fee.deleted=0 
ORDER BY actual_number_of_occurrences, resource.school_id, sell_date

9. although event_contact_status is 13=全取消 schedule is not deleted

SELECT contact_id, event_id, event_contact_status, start_date, end_date FROM event_contacts, smsevent_sub1 WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contact_status=13 
AND event_contacts.deleted=0 AND start_date IS NOT NULL AND smsevent_sub1.nenki IN ('201501', '201504') 


10. fees which have wrong tanka values (total of tanka is larger than basic fee)

SELECT sms_fee.id, basic_fee, sms_fee_sub1.tanka, sms_fee.school_id, sms_fee.fee_subcategory, resource.name, FLOOR(sms_fee_sub1.tanka), ROUND(basic_fee/FLOOR(sms_fee_sub1.tanka)), (FLOOR(sms_fee_sub1.tanka) * ROUND(basic_fee/FLOOR(sms_fee_sub1.tanka))) as tanka_total
FROM sms_fee, sms_fee_sub1, resource
WHERE sms_fee.id=sms_fee_sub1.id_sub1 AND sms_fee.deleted=0 AND resource.id=sms_fee.school_id 
	AND (FLOOR(sms_fee_sub1.tanka) * ROUND(basic_fee/FLOOR(sms_fee_sub1.tanka))) > basic_fee
	AND basic_fee > 0
	
id 	basic_fee 	tanka 	school_id 	fee_subcategory 	name 	FLOOR(sms_fee_sub1.tanka) 	ROUND(basic_fee/FLOOR(sms_fee_sub1.tanka)) 	tanka_total
500f7171-8c00-79b4-afe5-54ba113d66a6 	648 	130 	e86510a2-302b-7c34-250d-5001d1fc1b41 	教材費 	名古屋 	130 	5 	650
63a608a2-3241-b9d6-5f9e-54b5fcf71cb9 	3855 	643 	e86510a2-302b-7c34-250d-5001d1fc1b41 	受講料 	名古屋 	643 	6 	3858
72ee719a-1222-55f1-208b-54ae4b37b538 	3240 	295 	e86510a2-302b-7c34-250d-5001d1fc1b41 	教材費 	名古屋 	295 	11 	3245
ba5a538f-2d7e-fdcb-178f-54af463835c0 	185 	16.8182 	e86510a2-302b-7c34-250d-5001d1fc1b41 	教材費 	名古屋 	16 	12 	

-- 2/15 done on koza site
UPDATE `acc_20150206`.`sms_fee_sub1` SET `tanka` = '129' WHERE CONVERT( `sms_fee_sub1`.`id_sub1` USING utf8 ) = '500f7171-8c00-79b4-afe5-54ba113d66a6' LIMIT 1 ;
UPDATE `acc_20150206`.`sms_fee_sub1` SET `tanka` = '642' WHERE CONVERT( `sms_fee_sub1`.`id_sub1` USING utf8 ) = '63a608a2-3241-b9d6-5f9e-54b5fcf71cb9' LIMIT 1 ;
UPDATE `acc_20150206`.`sms_fee_sub1` SET `tanka` = '294' WHERE CONVERT( `sms_fee_sub1`.`id_sub1` USING utf8 ) = '72ee719a-1222-55f1-208b-54ae4b37b538' LIMIT 1 ;
UPDATE `acc_20150206`.`sms_fee_sub1` SET `tanka` = '15' WHERE CONVERT( `sms_fee_sub1`.`id_sub1` USING utf8 ) = 'ba5a538f-2d7e-fdcb-178f-54af463835c0' LIMIT 1 ;

SELECT sms_fee.id, basic_fee, tanka, FLOOR(tanka), ROUND(basic_fee/FLOOR(tanka)), (FLOOR(tanka) * ROUND(basic_fee/FLOOR(tanka))) as tanka_total
FROM sms_fee, sms_fee_sub1
WHERE sms_fee.id=sms_fee_sub1.id_sub1 AND deleted=0
	AND (FLOOR(tanka) * ROUND(basic_fee/FLOOR(tanka))) < basic_fee
	AND basic_fee < 0
	
--