[~[tlist_sql;
WITH tchrStudents AS(
	SELECT
		distinct students.dcid, students.id
	FROM sections
	JOIN cc ON cc.sectionid = sections.id
	JOIN students ON students.id = cc.studentid
	JOIN sectionteacher ON sectionteacher.sectionid = sections.id
	WHERE sectionteacher.teacherid = ~[x:userid]
	AND cc.termid LIKE '~(curyearid)__'
)

SELECT
    tchrStudents.id,
    emailaddress.emailaddress
FROM
    tchrStudents
    LEFT JOIN studentcontactassoc
        ON studentcontactassoc.studentdcid = tchrStudents.dcid
    INNER JOIN studentcontactdetail
        ON studentcontactdetail.studentcontactassocid = studentcontactassoc.studentcontactassocid
        AND (
            studentcontactdetail.iscustodial = 1
            or studentcontactdetail.liveswithflg = 1
            or studentcontactdetail.receivesmailflg = 1
        )
    LEFT JOIN personemailaddressassoc
        ON personemailaddressassoc.personid = studentcontactassoc.personid
        AND personemailaddressassoc.isprimaryemailaddress = 1
    LEFT JOIN emailaddress
        ON emailaddress.emailaddressid = personemailaddressassoc.emailaddressid
WHERE
    (
        studentcontactdetail.startdate <= current_date
        or studentcontactdetail.startdate is null
    )
    AND (
        studentcontactdetail.enddate >= current_date
        or studentcontactdetail.enddate is null
    )
    AND emailaddress.emailaddress is not null
    AND (
        1 = 0
        ~[if.pref.mba_tchrEm_contacts_custody=1]
            OR studentcontactdetail.iscustodial = 1
        [/if]
        ~[if.pref.mba_tchrEm_contacts_liveswith=1]
            OR studentcontactdetail.liveswithflg = 1
        [/if]
        ~[if.pref.mba_tchrEm_contacts_receivesmail=1]
            OR studentcontactdetail.receivesmailflg = 1
        [/if]
    )
]
{
    "studentid":"~(id)",
    "contactEmail":"~(email;JSON)"
},
[/tlist_sql]{}]
