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.