Seems like incorrect dates in school accreditation

[Ghislain Hachey]

‌

[Ghislain Hachey]

@Brian Lewis As I worked through what I thought was going to be the simplest of thing I came across a number of issues here all related. The above is in fact correct dates (I was misguided by the missing stars in newer school accreditations). After banging my head in the wrong direction for the morning I found out what I think is simply missing Partitions definitions. In FedEMIS production and in my systems the following was missing.

Adding those did indeed bring up the missing stars in the tablet versions of the school accreditations. However, the standard levels were not inline when comparing with the Form B component. For example, check the one below.

Comparing with the Form B component generated page.

Further investigating I see that the standard tally for the schools.SchoolAccreditation data comes from the [pInspectionRead].[SCHOOL_ACCREDITATION]. However, the partition value for standards were all using SSACRIT (which partitions into for equal bins). Following the school accreditation manual (and I believe this is how the Form B component already does it) should they not be partitioning using SSA? See circled below. Doing that I endup with matching standards levels whether I look at Form B component generated page or the short version offered in individual Schools.

And it does not stop there. I also notice that SSA was partitioned like this. Meaning a score or 75 would be a Level 3.

But in the school accreditation manual a 75 is a level 2 as shown below.

I changed it to the below in my own system which I think is more correct, no?

But now I have matching standard levels I have a differing overall Level (for the one on an “edge”.)

Seems the inspection result is “hard coded” in the table data as shown below.

This comes as an automatically calculated value based on a scalar function in SQL server (which depends on the country). The one for FSM will need to change from floor to round up.

We will need an UPDATE statement to correct all the ones already in the system. I believe this should do it. The one below is for FSM (i.e. [dbo].[calcInspectionResult_SSA]). The same code would work for RMI but you need to change the function (i.e. [dbo].[calcInspectionResult_RMI]) However, this was not needed at this point this RMI has not loaded a cloudfile yet.

‌

BEGIN TRANSACTION;

SELECT COUNT(*)
      ,[Source]
      ,[InspectionYear]
      ,[InspectionType]
      ,[InspectionResult]
  FROM [pInspectionRead].[SCHOOL_ACCREDITATION]
  GROUP BY 
       [Source]
      ,[InspectionYear]
      ,[InspectionType]
      ,[InspectionResult]
  ORDER BY 
       [Source]
      ,[InspectionYear]
      ,[InspectionType]
      ,[InspectionResult]

UPDATE [dbo].[SchoolInspection] SET [inspResult] = [dbo].[calcInspectionResult_SSA](inspID) WHERE [inspXml].value('(/survey/version)[1]', 'int') != 0

SELECT COUNT(*)
      ,[Source]
      ,[InspectionYear]
      ,[InspectionType]
      ,[InspectionResult]
  FROM [pInspectionRead].[SCHOOL_ACCREDITATION]
  GROUP BY 
       [Source]
      ,[InspectionYear]
      ,[InspectionType]
      ,[InspectionResult]
  ORDER BY 
       [Source]
      ,[InspectionYear]
      ,[InspectionType]
      ,[InspectionResult]

ROLLBACK

‌

[Ghislain Hachey]

@Brian Lewis to resolve this issue when rolling up the SQL delta changes in the repo.

[Brian Lewis]

fix(SchoolAccreditation): correctly calculate score at Standard level

Resolves #1058

→ <<cset d6f869a469ca>>