-- 2016-03-24
-- "受講講座一覧"
SELECT koza.koza_name, contacts.idnumber, contacts.last_name, contacts.first_name, koza.*, koza_contacts.*
FROM koza_contacts, koza, contacts
WHERE koza_contacts.koza_id=koza.id AND koza_contacts.contact_id=contacts.id AND koza_contacts.deleted=0
	AND koza_contacts.contact_id='0198813'
	
-- "受講クラス一覧"
SELECT smsevent.name, contacts.idnumber, contacts.last_name, contacts.first_name, smsevent.*, event_contacts.*
FROM event_contacts, smsevent, contacts
WHERE event_contacts.event_id=smsevent.id AND event_contacts.contact_id=contacts.id AND event_contacts.deleted=0
	AND smsevent.category='course' AND event_contacts.contact_id='0198813'

-- "受講クラススケジュール: statusの条件はキャンセル済みのクラスを外す為です"
SELECT  smsevent.name, contacts.idnumber, contacts.last_name, contacts.first_name, smsevent_instances.from_datetime,
	smsevent_instances.*
FROM smsevent_instances 
LEFT JOIN smsevent ON smsevent_instances.event_id=smsevent.id AND smsevent.deleted=0
LEFT JOIN contacts ON smsevent_instances.target_id=contacts.id 
WHERE smsevent.category = 'course' AND (smsevent_instances.status & 1021136) = 0
	AND smsevent_instances.target_id='0198813'
	
-- "講座スケジュール情報"
SELECT  smsevent.name, sms_event_instance.from_datetime, sms_event_instance.*
FROM sms_event_instance 
LEFT JOIN smsevent ON sms_event_instance.parent_id=smsevent.id AND smsevent.deleted=0
WHERE smsevent.category = 'course' AND (sms_event_instance.status & 1021136) = 0
	AND sms_event_instance.parent_id='4cf4b368-576a-206c-4c5d-5361b658a4dc'

-- "通常商品"
SELECT buppin_name, buppin_category, master_name as buppin_category_name, buppin_code, shikenshu
FROM buppin 
LEFT JOIN master_kanri ON buppin.buppin_category=master_kanri.id
WHERE buppin.deleted=0

-- "カリキュラムに紐づくもの"
SELECT DISTINCT buppin_name, buppin_code, curriculum_name, curriculum_id
FROM curriculum
LEFT JOIN curriculum_hassocode ON curriculum_hassocode.curriculum_id=curriculum.id AND  curriculum_hassocode.deleted=0
LEFT JOIN hassocode ON curriculum_hassocode.hassocode_id=hassocode.id AND hassocode.deleted=0
LEFT JOIN  hassocode_buppin ON hassocode_buppin.hassocode_id=hassocode.id AND hassocode_buppin.deleted=0
LEFT JOIN buppin ON hassocode_buppin.buppin_id=buppin.id AND buppin.deleted=0
WHERE curriculum.id='8966512a-3f51-8f70-838b-558a6a305e65'

-- "講座に紐づくもの"
SELECT DISTINCT buppin_name, buppin_code, koza.koza_name, smsevent.name AS class_name, curriculum_name, curriculum.id
FROM koza
LEFT JOIN event_koza ON event_koza.koza_id=koza.id AND event_koza.deleted=0
LEFT JOIN smsevent ON event_koza.event_id=smsevent.id AND smsevent.deleted=0
LEFT JOIN curriculum ON smsevent.curriculum_id=curriculum.id AND curriculum.deleted=0
LEFT JOIN curriculum_hassocode ON curriculum_hassocode.curriculum_id=curriculum.id AND  curriculum_hassocode.deleted=0
LEFT JOIN hassocode ON curriculum_hassocode.hassocode_id=hassocode.id AND hassocode.deleted=0
LEFT JOIN  hassocode_buppin ON hassocode_buppin.hassocode_id=hassocode.id AND hassocode_buppin.deleted=0
LEFT JOIN buppin ON hassocode_buppin.buppin_id=buppin.id AND buppin.deleted=0
WHERE koza.id='2cda118c-97aa-4950-ae73-559baf4c6da9'

-- "受講生の発送物"
 SELECT hassocode_buppin.hassocode_id, hassocode_buppin.buppin_id, hassocode.hassocode, hassobi.hassobi, smsevent.name AS class_name, buppin.buppin_name,
 haisogyosha_name, tsuiseki_number, hasso_status, hasso_type, chushutsu_status, hasso_contacts.date_modified
FROM event_contacts
INNER JOIN smsevent ON event_contacts.event_id=smsevent.id AND smsevent.deleted=0 
INNER JOIN hassobi ON smsevent.id=hassobi.event_id AND hassobi.deleted=0 
INNER JOIN hassocode_hassobi ON hassocode_hassobi.hassobi_id=hassobi.id AND hassocode_hassobi.deleted=0 
INNER JOIN hassocode ON hassocode_hassobi.hassocode_id=hassocode.id AND hassocode.deleted=0 
INNER JOIN curriculum ON smsevent.curriculum_id=curriculum.id AND curriculum.deleted=0 
INNER JOIN curriculum_hassocode ON curriculum.id=curriculum_hassocode.curriculum_id AND hassocode.id=curriculum_hassocode.hassocode_id AND curriculum_hassocode.deleted=0
INNER JOIN hassocode_buppin ON hassocode_buppin.hassocode_id=hassocode.id AND hassocode_buppin.deleted=0 
INNER JOIN buppin ON hassocode_buppin.buppin_id=buppin.id AND buppin.deleted=0
LEFT JOIN (
 SELECT hcc.hassocode_id, hcc.contact_id, hc.id as hasso_contact_id, hcc.event_id, hc.buppin_id, hc.haisogyosha_name, hc.shukka_kohu_date as shukka_kohu_date, 
  hcc.hasso_type, hc.tsuiseki_number, hc.hasso_status, hcc.chushutsu_status, hc.date_modified 
 FROM hassocode_contacts as hcc 
 INNER JOIN hasso_contacts as hc ON hcc.id=hc.hassocode_contacts_id AND hc.deleted=0 AND hc.date_modified!='2014-01-03 00:00:00'
 WHERE hcc.contact_id='68c8f96a-875d-331f-e00e-58441e1cd807' AND hcc.deleted=0 
) as hasso_contacts ON hassocode.id=hasso_contacts.hassocode_id AND event_contacts.contact_id=hasso_contacts.contact_id AND hassobi.event_id=hasso_contacts.event_id AND hassocode_buppin.buppin_id=hasso_contacts.buppin_id  
WHERE event_contacts.contact_id='68c8f96a-875d-331f-e00e-58441e1cd807' AND event_contacts.deleted=0