Technical Details

Owned by
Andrew Rose
Last updated: Nov 08, 2019
3 min read

The process is initiated by running the finance.spixeNZMOEFinance stored procedure.  This is due to having tuition fees exported as part of the process.  This proc inserts all students and past students (with their relevant financial information) to be included in the roll return into a temp table (##NZMOEStudents).  It then calls the dbo.spixeNZMOE stored proc.

dbo.spixeNZMOE gathers all demographic and school information for the data in the ##NZMOEStudents temp table and inserts into dbo.pvxeNZMOE - this is a view based on the xeNZMOE table.  It also creates a temp table called #NZMOE which is used to hold any issues raised during validation

Now for the data validation part... 

There is an INSTEAD OF trigger on the dbo.pvxeNZMOE view called "pvxeNZMoE_ITR" and this is where all the validation takes place.  All warnings and error conditions are inserted into the above-mentioned #NZMOE temp table.  If there are no ERRORS (warnings are OK) , the data passed into the trigger is inserted into the xeNZMOE table.  If there are any validation errors then the data is NOT inserted into the xeNZMOE table, and the contents of the #NZMOE temp table are presented in a report for the user.

From this point on all data is retrieved from dbo.xeNZMOE.  This is the snapshot of data at the required point in time and is used for all generated reports as well as the actual export file.  Therefore once this snapshot has been generated any further changes by the school to their data will not affect the roll return...  Unless they generate a new one that is.

Other Points

  • Roll return dates are stored in dbo.vNZMoE_CountDates.  This is simply a hard-coded union of all dates and needs to be amended each year
  • Roll returns are very date-centric.  To simplify testing any date comparisons should use dbo.fnGetNZMOEDate. This function checks for the existence of a override date in the Config table "ExternalSystem|NZMOE|RollReturn|NZMOEDate" and returns that if it exists (otherwise returns current date)
  • We do unit tests for ALL new rules.  Please note that you have to specifically amend the pvxeNZMoE_ITR to handle being unit tested by utilising the @RuleToTest variable
  • You can hold down Control+Shift and double click the buttons panel on the Roll Return screen to enable all the buttons


Loading code sets

The school code set has a plethora of maori characters which our current collation sequence does not support.  Therefore you are unable to use the DataImport tool.  The distribution format I used at time of writing is an Excel spreadsheet, therefore I used an Excel formula to substitute the offending characters...  Note that the data begins on row 6 - hence the referral to A6 & B6.

=CONCAT(" UNION ALL SELECT 'NZMOE', '", A6, "', ", "'",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C6, "'", "''"), "ā", "a"), "ē", "e"), "ī", "i"), "ō", "o"), "ū", "u"), "', ",IF(B6="*", "0", "1"))

The substitute also perform quote-escaping so schools with a single quote in their name are quoted correctly for resultant sql statement.

This formula can be pasted into a new column and it generates the resultant UNION ALL SELECT...  

Secondary Tertiary Programme

Note that they have used a strikeout font to indicate removed providers.  I do not know how to extract this from excel so I had to hard code the inactive values at the end of the formula

=CONCAT(" UNION ALL SELECT 'NZMOE', '", A5, "', ", "'",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5, "'", "''"), "’", "''"), "ā", "a"), "ē", "e"), "ī", "i"), "ō", "o"), "ū", "u"), "', ", A5*10, ", ", IF(OR(A5=11, A5=25, A5=98), "0", "1"))

Subjects

I pasted from the word document into an excel spreadsheet and used the following formula

=IF(B3="", "", CONCAT(" UNION ALL SELECT 'NZMOE', '", TRIM(B3), "', '", TRIM(A3), "'"))