-- 2014-12-20

-- 1. initialization: set values to zero (necessary for courses without corresonding students, e.g. no yoyaku in even_contacts)
UPDATE smsevent_sub3, smsevent_sub1 SET shinki_toroku_no=0, keizoku_toroku_no=0, shinki_yoyaku_no=0, keizoku_yoyaku_no=0, sai_toroku_no=0, sai_yoyaku_no=0 WHERE id_sub1=id_sub3 AND nenki IN ('201501', '201504')

-- 2. set each ninzu by getting values from event_contacts 
UPDATE  smsevent_sub1, smsevent_sub3, (
	SELECT count(contact_id) AS mycount, event_id FROM event_contacts, smsevent_sub1 	
	WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contact_status=1 AND ec_shinki_keizoku IN ('1', '3') AND event_contacts.deleted=0 AND smsevent_sub1.nenki IN ('201501', '201504') GROUP BY event_id
) AS my_ec
SET shinki_yoyaku_no=mycount WHERE nenki IN ('201501', '201504') AND id_sub1=id_sub3 AND my_ec.event_id=id_sub1 ;

UPDATE  smsevent_sub1, smsevent_sub3, (
	SELECT count(contact_id) AS mycount, event_id FROM event_contacts, smsevent_sub1 	
	WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contact_status=1 AND ec_shinki_keizoku=2 AND event_contacts.deleted=0 AND smsevent_sub1.nenki IN ('201501', '201504') GROUP BY event_id
) AS my_ec
SET keizoku_yoyaku_no=mycount WHERE nenki IN ('201501', '201504') AND id_sub1=id_sub3 AND my_ec.event_id=id_sub1 ;

UPDATE  smsevent_sub1, smsevent_sub3, (
	SELECT count(contact_id) AS mycount, event_id FROM event_contacts, smsevent_sub1 	
	WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contact_status=1 AND ec_shinki_keizoku=3 AND event_contacts.deleted=0 AND smsevent_sub1.nenki IN ('201501', '201504') GROUP BY event_id
) AS my_ec
SET sai_yoyaku_no=mycount WHERE nenki IN ('201501', '201504') AND id_sub1=id_sub3 AND my_ec.event_id=id_sub1 ;


UPDATE  smsevent_sub1, smsevent_sub3, (
	SELECT count(contact_id) AS mycount, event_id FROM event_contacts, smsevent_sub1 	
	WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contact_status=2 AND ec_shinki_keizoku IN ('1', '3') AND event_contacts.deleted=0 AND smsevent_sub1.nenki IN ('201501', '201504') GROUP BY event_id
) AS my_ec
SET shinki_toroku_no=mycount WHERE nenki IN ('201501', '201504') AND id_sub1=id_sub3 AND my_ec.event_id=id_sub1 ;

UPDATE  smsevent_sub1, smsevent_sub3, (
	SELECT count(contact_id) AS mycount, event_id FROM event_contacts, smsevent_sub1 	
	WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contact_status=2 AND ec_shinki_keizoku=2 AND event_contacts.deleted=0 AND smsevent_sub1.nenki IN ('201501', '201504') GROUP BY event_id
) AS my_ec
SET keizoku_toroku_no=mycount WHERE nenki IN ('201501', '201504') AND id_sub1=id_sub3 AND my_ec.event_id=id_sub1 ;

UPDATE  smsevent_sub1, smsevent_sub3, (
	SELECT count(contact_id) AS mycount, event_id FROM event_contacts, smsevent_sub1 	
	WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contact_status=2 AND ec_shinki_keizoku=3 AND event_contacts.deleted=0 AND smsevent_sub1.nenki IN ('201501', '201504') GROUP BY event_id
) AS my_ec
SET sai_toroku_no=mycount WHERE nenki IN ('201501', '201504') AND id_sub1=id_sub3 AND my_ec.event_id=id_sub1 ;

-- 3. confirmation 
-- total toroku numbers using shinki/keizoku
SELECT mycount, (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, (
	SELECT count(contact_id) AS mycount, event_id FROM event_contacts, smsevent_sub1 	WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contacts.deleted=0 AND smsevent_sub1.nenki IN ('201501', '201504')
		AND event_contact_status IN ('1', '2') GROUP BY event_id
) AS my_ec
WHERE smsevent_sub1.nenki IN ('201501', '201504') AND smsevent_sub1.id_sub1=smsevent_sub3.id_sub3 AND my_ec.event_id=id_sub1 AND mycount!=(shinki_yoyaku_no + keizoku_yoyaku_no + shinki_toroku_no + keizoku_toroku_no)

-- total toroku numbers using dansei/josei if the tein_type of the course is dansei/josei
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.nenki IN ('201501', '201504') AND 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)