U Got This (Title IX)

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.

OLD STUFF BELOW

Content is in a Learn course. There are several videos that students watch, then take a quiz. We check the status of these quizzes in the portal to determine whether the item is complete or not.

Users are added to the course through a UC4 process flow called DU_GEN_NEW_STU_LEARN, with a job called ICZSNSTU.

The job calls a stored procedure which defines the population to add to the course. The important thing to note about the below query is the use of the NEWSTUDENT role.

cursor assignment_sel is
    select distinct v_courseid || '|' || gobsrid_sourced_id || '|' || 'S|enabled' vOUTPUT
      from gorirol,
           gobsrid
     where gorirol_pidm = gobsrid_pidm
       and gorirol_role = 'NEWSTUDENT'
       and not exists (select 'x'
                         from bbSaaS.bbtUSERS u,
                              bbSaaS.bbtCOURSE_USERS cu,
                              bbSaaS.bbtCOURSE  cm
                        where u.batch_uid = gobsrid_sourced_id
                          and cu.crsmain_pk1 = cm.course_pk1
                          and cu.users_pk1 = u.user_pk1
                          and cm.course_id = v_courseid
                          and cu.row_status = 0
                          and not u.batch_uid like 'TEST%')
     order by 1;

It then adds users into a temp table:

  /* USER */
  insert into bbSaaS.bbtUSERS select * from BBSAAS.bbvUSERS;

  /*COURSE MAIN */
  insert into bbSaaS.bbtCOURSE select * from BBSAAS.bbvCOURSE;

  /* COURSE USERS */
  insert into bbSaaS.bbtCOURSE_USERS select * from BBSAAS.bbvCOURSE_USERS;

Finally a feed file is generated and scp'd to netxfer. A script is started on netxfer via a remote ssh command that processes the generated feed file. This actually updates Learn.