-- 2016-09-14
-- how to create net table with net_haishin_kigen -> net_haishin_kigen will be inserted later
INSERT INTO contacts_nettohaishin (id, deleted, date_entered, date_modified, email1, email2, email_opt_out, invalid_email, contact_status, idnumber)
SELECT id, deleted, date_entered, date_modified, email1, email2, email_opt_out, invalid_email, contact_status, idnumber 
FROM contacts WHERE smsgroup='student' AND deleted=0 ;

-- find the max net_haishin_kigen using smevent (course) and event_contacts tables -> query takes around 5-10 seconds to execute
SELECT smsevent.id, event_contacts.contact_id, max(net_haishin_kigen) 
FROM smsevent
INNER JOIN event_contacts ON smsevent.id=event_contacts.event_id AND event_contacts.deleted=0 AND event_contact_status=1
WHERE smsevent.deleted =0 AND smsevent.net_haishin_kigen IS NOT NULL AND smsevent.net_haishin_kigen!='0000-00-00' AND smsevent.category='course' 
GROUP BY contact_id
    
-- update contacts_nettohaishin by metehan  ->  takes aroudn 8 seconds to execute
UPDATE
    contacts_nettohaishin t1,
    (		SELECT smsevent.id, event_contacts.contact_id, max(net_haishin_kigen) AS max_net_haishin_kigen
		FROM smsevent
		INNER JOIN event_contacts ON smsevent.id=event_contacts.event_id AND event_contacts.deleted=0 AND event_contact_status=1
		WHERE smsevent.deleted =0 AND smsevent.net_haishin_kigen IS NOT NULL AND smsevent.net_haishin_kigen!='0000-00-00' AND smsevent.category='course' 
		GROUP BY contact_id
    ) t2
SET 
    t1.net_haishin_kigen = t2.max_net_haishin_kigen
WHERE
    t1.id = t2.contact_id

-- (1) student which are 受講中（ネット配信期限内） and email address is not empty  -> as of 9/20 14,187 records
SELECT * FROM `contacts_nettohaishin` WHERE net_haishin_kigen > '2016-09-30' AND email1 IS NOT NULL AND email1!=''
UPDATE `contacts_nettohaishin` SET taisho=1 WHERE net_haishin_kigen > '2016-09-30' AND email1 IS NOT NULL AND email1!=''
    
-- (3) student which are 受講中（ネット配信期限内） and email address is empty  -> as of 9/20 344  records
SELECT * FROM `contacts_nettohaishin` WHERE net_haishin_kigen > '2016-09-30' AND (email1 IS NULL OR email1='')
UPDATE `contacts_nettohaishin` SET taisho=3 WHERE net_haishin_kigen > '2016-09-30' AND (email1 IS NULL OR email1='')

-- (2) student which are 新規生の人でかつ、更新日が2014年1月5日以降かつメアド登録があり、オプトイン（メール配信可）の方  -> as of 9/20 9,715 records
SELECT * FROM `contacts_nettohaishin` WHERE contact_status='14' AND date_modified >= '2014-01-05' AND email1 IS NOT NULL AND email1!='' AND email_opt_out=0
UPDATE `contacts_nettohaishin` SET taisho=2 WHERE contact_status='14' AND date_modified >= '2014-01-05' AND email1 IS NOT NULL AND email1!='' AND email_opt_out=0

-- for below idnumber list, invalid_email 一括メール送信不可 is 1 
	0133422 2006150 2024503 2018172 2010644 2016182 
	2034288 2009749 2006124 0223048 2011454 2011463

SELECT 
	contacts.last_name, contacts.first_name, contacts.last_name_furigana, contacts.first_name_furigana, 
	contacts.obog_members, contacts.primary_address_postalcode, contacts.primary_address_state, 
	contacts.primary_address_city, contacts.primary_address_street, contacts.email1, contacts.email2, 
	contacts.phone_home, contacts.gender, contacts.birthdate, contacts.grade, 	contacts.houka_graduate_school, 
	contacts.kishu_mishu, contacts.graduate_school, contacts.major, contacts.university, contacts.gakubu, 
	contacts.gakka, contacts.hougakukei_circle, contacts.circle_title, contacts.highschool, contacts.jikanshitei, 
	contacts.idnumber, contacts.contact_status, contacts.kiseisaki_address_postalcode, contacts.kiseisaki_address_state, 
	contacts.kiseisaki_address_city, contacts.kiseisaki_address_street, contacts.kiseisaki_date_entered, contacts.kiseisaki_date_end, 
	contacts.kyokudome_address_postalcode, contacts.kyokudome_address_state, contacts.kyokudome_address_city, 
	contacts.kyokudome_address_street, contacts.kyokudome_address_kana, contacts.alt_address_postalcode, 
	contacts.alt_address_street, contacts.alt_address_city, contacts.alt_address_state, contacts.kiseisaki_phone
FROM contacts 
INNER JOIN contacts_nettohaishin ON contacts.id = contacts_nettohaishin.id AND taisho=1 
WHERE contacts.deleted=0