Cleanup Procedures

This page is for boards' technical support. Other users may ignore.

This procedure is related to the Ontario tech note sent out on August 16, 2017.

Description: There was an issue in the Edit Completed Lessons/Work Units page introduced in CAN-ON 17.5.0.3.0.  Once marks are submitted without the Date Marked field specified, lesson marks fail to display on the page. This issue has been addressed in the July 28 release (CAN-ON 17.7.0.3.0).

This issue may have also caused duplicate records to be created in the database. Users can see those duplicate records on the page after applying the update. Boards, who were impacted, will need to follow the steps below to clean up and remove those duplicate records.  

Solution: Client must follow steps below to check if they are impacted and to remove duplicate working units if applicable.

1.  Run SELECT query to check whether the school board is impacted.

SELECT sch.name || '(' || sch.school_number ||')' AS school,
       stu.lastfirst || '(' || stu.state_studentnumber ||')' AS student,
       sc.ccdcid, cc.course_number,
       sc.lesson_number,
       count(*)
  FROM S_ON_CC_WKUNOLM_C sc INNER JOIN cc ON
        sc.ccdcid = cc.dcid
       INNER JOIN students stu ON
        cc.studentid = stu.id
       INNER JOIN schools sch ON
        sch.school_number = cc.schoolid
 WHERE sc.lesson_number IS NOT NULL
 GROUP BY sch.name || '(' || sch.school_number ||')',
          stu.lastfirst || '(' || stu.state_studentnumber ||')',
          sc.ccdcid, cc.course_number, sc.lesson_number
HAVING COUNT(*)>1;

If no record returns, then your school board isn’t impacted. and you do not need to proceed, otherwise, continue with the following steps:

1. Backup table S_ON_CC_WKUNOLM_C before clean up procedure.

2. Run DELETE scripts to delete bad data (Clean up Process – Step 1). This step will remove records with Blank Marks.

   SQL

   DELETE FROM S_ON_CC_WKUNOLM_C WHERE mark IS NULL and date_marked IS NULL;
   DELETE FROM S_ON_CC_WKUNOLM_C WHERE LESSON_NUMBER IS NULL;

3. Repeat step 1 to verify duplicate records, and keep this result for step 7 to revise and fix data.
4. If records are found, run the following script (Clean up process – Step 2). This step will:
   1. Keep the latest entered record with a mark.
   2. Remove other duplicated records.
   SQL

   BEGIN
    
     DELETE FROM S_ON_CC_WKUNOLM_C WHERE mark IS NULL and date_marked IS NULL;
     DELETE FROM S_ON_CC_WKUNOLM_C WHERE LESSON_NUMBER IS NULL;
   
     DELETE FROM S_ON_CC_WKUNOLM_C ccMark
      WHERE COALESCE(ccMark.WHENMODIFIED, ccMark.WHENCREATED)+ccMark.id != (
              SELECT MAX (COALESCE(ccMark2.WHENMODIFIED, ccMark2.WHENCREATED)+ccMark2.id)              
                FROM S_ON_CC_WKUNOLM_C ccMark2
               WHERE ccMark.ccDCID = ccMark2.ccDCID
                 AND ccMark.Lesson_number = ccMark2.Lesson_number)                 
       AND (SELECT count(ccmark1.lesson_number)  
              FROM S_ON_CC_WKUNOLM_C ccMark1
             WHERE ccMark.ccDCID = ccMark1.ccDCID
               AND ccMark.Lesson_number = ccMark1.Lesson_number) > 1;
     COMMIT; 
   END;
   

5. Run step 1 to confirm CLEANUP Procedure was successful. No records should be returned.
6. Review affected students’ marks and fix manually if necessary.