New Student Mandatory Videos (FYE Compliance)

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.