Improve loading of exam data

[Ghislain Hachey]

We often end up with exams data file loaded without the TESTID resulting in this. This looks like should never have been accepted by the loading process in the first place as it essentially an unknown exam. I think we need to enforce a few things when loading with TESTID being one of them.

So we need to clean these with care as the loading process loads data in the Exams table and a bunch of other related tables.

Then re-enforce the upload process a little to avoid at least errors that can not be cleaned up by simply reloading the exam file.

‌

[Brian Lewis]

This is a discrepancy between what happens in the Load phase and the Process phase - Load will determine the examCode from the Item structure is TestID is not there - so testID is not mandatory. However the Process phase does not do the same: it always uses testid for the exam code. This is what need to change, and can happen in the SQL (mostly) .

Also - the Process phase will add the examCode into lkpExams if it is not currently defined - so probably not necessary to check this at Load time either.

Main thing is to make sure that the Upload and Process do the same things.

[Brian Lewis]

fix(exams): fix bug not correctly detecting examCode in Process phase of upload

Resolves #1348

→ <<cset 86867e753fed>>

[Ghislain Hachey]

@Brian Lewis Thank you for this fix. Can we get a suggested script to cleanup the badly loaded data?

[Brian Lewis]

See #1388 . May as well do this as write basically the same SQL code as a one-off.

‌