Improve simple audit data

[Ghislain Hachey]

The columns pCreateUser, pCreateDateTime, pEditUser and pEditDateTime are used in various useful places throughout the database. However, several places they are not working consistently. Some minor issues encountered are:

• Much of it is empty (perhaps not working on census upload, etc.) It would be nice to back-fill the missing data with some default or at least make sure that it gets populated going forward.
• Sometimes the user included is the user running the web application (and not the logged in actual user). This is not ideal as regardless of who does the actual change it is the same web app user logged

Important tables to test and improve are:

• Schools
• TeacherIdentity, TeacherTraining
• Students_
• StudentEnrolments_
• EdExpenditure, EdExpenditureState, GovtExpenditure, GovtExpenditureState
• Exams, ExamIndicators, ExamBenchmarks, ExamStandards
• CensusWorkbookColumnMaps
• SchoolInspection (SchoolAccreditation, WASH)
• SchoolSurvey does not have the audit fields at all
• And more

Refer to the actual report at https://github.com/ghachey/pacific-emis-reports/issues/64 for details on the report and the various queries used in it.

[Ghislain Hachey]

Check out Students_ and StudentEnrolments_ as they audit seems to be working properly. Especially using the correct logged in user and not the web app user.

[Ghislain Hachey]

Note: for tables Exams, ExamIndicators, ExamBenchmarks, ExamStandards.

Once can populate the pCreateUSer and pCreateDateTime from the [exUser] and [exDate] fields of Exams table data upload. But to avoid having to do this modify the exams data upload script to populate the pCreateUSer and pCreateDateTime of remaining ExamIndicators, ExamBenchmarks, ExamStandards directly.

[Ghislain Hachey]

Note: school inspection loading does not input creation audit data.