-- get the classes and terms for the class
SELECT cl.class_id,
    cl.name,
    st.first_name,
    st.last_name,
    cl.size as 'class_size',
    cd.size as 'date_size',
    cdt.start,
    cdt.end,
    cdt.day,
    ba.name AS 'bookable_area',
    ce.name AS 'centre_name',
    co.name AS 'course_name',
    cd.[repeat],
    co.assessment_type,
    cd.[from],
    cd.[to]
FROM classes cl
    JOIN staff st ON (st.staff_id = cl.teacher)
    JOIN class_dates cd ON (cd.class_id = cl.class_id)
    JOIN class_date_times cdt ON (cdt.date_id = cd.date_id)
    JOIN centre_has_bookable_areas chba ON (chba.centre_bookable_area_id = cl.centre_bookable_area_id)
    JOIN bookable_areas ba ON (ba.area_id = chba.area_id)
    JOIN centre_has_courses chc ON (chc.centre_course_id = cl.centre_course_id)
    JOIN centres ce On (ce.centre_id = chc.centre_id)
    JOIN courses co ON (co.course_id = chc.course_id)
WHERE (
    cd.[to] >= GETDATE()
    OR cd.[to] IS NULL
)
AND chc.centre_id in (19);

-- get the member template
SELECT ce.name AS [centre_name],
    co.name AS [course_name],
    chm.class_id,
    chm.member_id,
    m.first_name,
    m.last_name,
    m.date_of_birth,
    m.postcode,
    chm.level_id,
    mpa.start_date,
    mpa.end_date,
    m.card_id,
    m.crm_member_id,
    chm.sessions as remaining_sessions,
    mpa.sessions as booked_sessions
FROM class_has_members chm
    JOIN member_payment_allocations mpa ON (mpa.class_member_id = chm.class_member_id)
    JOIN members m ON (m.member_id = chm.member_id)
    JOIN classes cl ON (cl.class_id = chm.class_id)
    JOIN centre_has_courses chc ON (chc.centre_course_id = cl.centre_course_id)
    JOIN courses co ON (co.course_id = chc.course_id)
    JOIN centres ce ON (ce.centre_id = chc.centre_id)
WHERE (
    mpa.end_date IS NULL
    OR mpa.end_date > GETDATE()
)
AND mpa.void_date IS NULL
AND chc.centre_id in (19);

-- member competencies
SELECT cc.member_id,
    cc.competency_id,
    cc.grade_grade_id,
    cc.edit_date
FROM member_has_course_competencies cc
        JOIN class_has_members chm ON (chm.member_id = cc.member_id)
WHERE chm.active NOT IN (0,6);


-- cancelled sessions (if you have time)e
SELECT [start],[end],cs.class_id
FROM class_sessions
WHERE state NOT IN (0,1)
AND [start] >= GETDATE();