-- 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'