Get the term. For new students (new, new fall student and new transfer fall student) this will be their admit term. For all other students this will be the current term. This needs to change to accommodate new off-term transfers.
Check to see if the person is in the Learn course that corresponds to the term, and that the course is currently active. Registrations into the Learn course happen via a Banner job (document this job).
select cm.pk1, cm.course_name, cm.course_id, cm.start_date, cm.end_date, cm.available_ind, u.user_id from course_main cm inner join course_users cu on cu.crsmain_pk1 = cm.pk1 inner join users u on cu.users_pk1 = u.pk1 where u.user_id = ? and cm.course_id = ? and lower(cm.available_ind) = 'y' and cm.start_date < current_timestamp and cm.end_date > current_timestamp and cu.role = 'S'and cu.row_status = '0'
This basically looks for a record where the course id matches the title ix course for the term, the user id matches the student in question, and the current date is between the start and end dates for the course.
If we find a record, we show the checklist item.
No record means the student is not in the course, or the course is not active. Don't show the checklist item.
If the person is in the course, we check the grades for the quizzes:
select q1.*, case when q1.scale_type = 'T' and q1.percentage is not null then ( select gs.symbol from gradebook_symbol gs where gs.gradebook_translator_pk1 = q1.gradebook_translator_pk1 and gs.lower_bound <= q1.percentage and (gs.upper_bound > q1.percentage or (gs.upper_bound = 100 and q1.percentage = 100))) when q1.scale_type = 'S' then cast(round(q1.score, 2) as VARCHAR(32)) when q1.scale_type = 'X' then cast(q1.grade as VARCHAR(32)) when q1.scale_type = 'P' then cast(q1.percentage as VARCHAR(32)) when q1.scale_type = 'C' then case when q1.score is null then N'Incomplete' else N'Complete' end else N'unknown scale_type!' end as grade_display from ( select q0.*, case when coalesce(q0.possible, 0) = 0 then null else round(cast(coalesce(q0.score, 0)/q0.possible * 100 as numeric), 2) end as percentage from ( select c.pk1 || '.' || gg.pk1 as course_grade_pk1, g.gradebook_translator_pk1, g.title, g.possible, gt.scale_type, AVG(coalesce(case when gg.last_override_date is not null then gg.manual_score else null end, a.score, 0)) as score, MAX(coalesce(case when gg.last_override_date is not null then gg.manual_grade else null end, a.grade)) as grade, g.position, MAX(gg.last_override_date) as max_last_override_date, MAX(gg.last_attempt_date) as max_last_attempt_date, MAX(g.date_modified) as max_gradebook_modified from gradebook_main g inner join course_main c on c.pk1 = g.crsmain_pk1 inner join gradebook_grade gg on gg.gradebook_main_pk1 = g.pk1 inner join course_users d on gg.course_users_pk1 = d.pk1 inner join gradebook_translator gt on g.gradebook_translator_pk1 = gt.pk1 left join attempt a on gg.pk1 = a.gradebook_grade_pk1 where c.service_level = 'F' and g.visible_ind = 'Y' and d.available_ind = 'Y' and c.available_ind = 'Y' and d.row_status = 0 and g.deleted_ind = 'N' and d.users_pk1 = (select pk1 from users where student_id=?) and (date_trunc('day', c.start_date) <= CURRENT_TIMESTAMP or c.start_date IS NULL) and (date_trunc('day', c.end_date) >= CURRENT_TIMESTAMP or c.end_date IS NULL) and c.course_id=? and ((a.pk1 = case when g.aggregation_model = 1 then gg.last_graded_attempt_pk1 when g.aggregation_model = 2 then gg.highest_attempt_pk1 when g.aggregation_model = 3 then gg.lowest_attempt_pk1 when g.aggregation_model = 4 then gg.first_graded_attempt_pk1 else a.pk1 end) or (gg.last_override_date is not null)) group by c.pk1, gg.pk1, g.gradebook_translator_pk1, g.title, g.possible, gt.scale_type, g.position ) q0 ) q1 order by q1.position
If the student achieved 100% on all quizzes, the checklist item is complete. Otherwise, it is incomplete.