Support for loading WASH in isolation [2020:H]

[Ghislain Hachey]

Perhaps the same fix as was done for staff.

[Ghislain Hachey]

Now working

[Ghislain Hachey]

@Brian Lewis WASH validation not working when I try to run in in isolation. Seems it raises an error before it can result the validation resultset.

Generally the validation for loading the RMI workbook is not working well. It throws an abstract error and not the detailed validation feedback expected.

I’m running each sheet as XML using the xllistutilities tool and the validation procs return the error results sets but they are not making there way to the Web UI.

[Ghislain Hachey]

The latest RMI workbook which does contains errors to fix can be found on the Google Share with you or downloaded from the MIEMIS production.

‌

[Ghislain Hachey]

Digging into this I see some other things I’m not sure I’m following. For example, when I use the xllistutilities tool to produce the XML for stored proc troubleshooting I get School_ID as shown below.

But when I look at all the censusLoad stored proc the XML unpacking code looks for School_No.

This seems to have further reaching consequences like in the code below.

‌

[Ghislain Hachey]

There seems to be no validation on Schools Sheets (maybe not needed?!) I had a school with incorrect spelling hence no School_ID, State or Govt_or_Non-govt yet the pSurveyOps.censusLoadSchools and its validate auxiliary ran just fine.

[Brian Lewis]

Digging into this I see some other things I’m not sure I’m following. For example, when I use the xllistutilities tool to produce the XML for stored proc troubleshooting I get School_ID as shown below. …ut when I look at all the censusLoad stored proc the XML unpacking code looks for School_No.

This is a consequence of #798, which allows vocab translatio of column names; ie School ID.

These are expected to be returned to their generic vocab bane ie ‘SchoolNo’ before passing to the load proc. This translation is done in CensusWorkbookcontroller - see https://bitbucket.org/softwords/pineapples/commits/88db4479d3b9#chg-Pineapples/Controllers_Api/CensusWorkbookController.cs

The down side is you do now get this problemif using xllistUtilities - probably the best solution is to temporarily set the column name School Id to SchoolNo before, save then run xllistutilities. Or, do a global search and replace in the xml in Notepad++

‌

[Brian Lewis]

Also, bear in mind that CensusWorkbookcontroller does another minor mod to the XML - it pushes the state and survey year as attributes of the root node:

‌

   /// <summary>
    /// add the district and survey year as attributes to the root node
    /// a convenient way to get these into the stored proc
    /// </summary>
    /// <param name="xd"></param>
    private XDocument prepXml(XDocument xd, string districtName, string schoolYear)
    {
        xd.Root.SetAttributeValue("state", districtName);
        xd.Root.SetAttributeValue("schoolYear", schoolYear);
        return xd;
    }

So again, you’ll need to do this before passing the xml to the load proc - this goes for all the load procedures.

This is how it gets that back in the stored proc (e.g. censusLoadSchools:

Select @DistrictName = v.value('@state', 'nvarchar(50)'), 
@SurveyYear = cast(substring(v.value('@schoolYear','nvarchar(50)'),@YearStartPos,4) as int)
From @xml.nodes('ListObject') as V(v)

‌

[Ghislain Hachey]

Seems it is working. Caveats documented in https://docs.pacific-emis.org/doku.php?id=emis_developer_manual#workbook_troubleshooting