SchoolInspection year should be school year, not calendar year

[Brian Lewis]

No description provided.

[Brian Lewis]

First commit is SQL objects that formalise the calculation of SchoolYear from a date. These ultimately depend on a new sysParam SCHOOLYEAR_STARTDATE

which is accessible via common.SchoolYearStartDate.

Two function return scalar values:

common.SchoolYearInt

common.SchoolYearStr

common.SchoolYear returns a rowset (single row) containing columns yrand yrStr

‌

Previous references to common.FiscalYear have been changed.

‌

[Brian Lewis]

feat(inspections): year is set to SchoolYear, not Calendar year

→ <<cset 7ea02ddf8846>>

[Brian Lewis]

update script to set InspectionYear to calculated school year using the various objects above:

UPDATE pInspectionRead.SchoolInspections
SET InspectionSetID = null
, InspectionSetName = null
, InspectionYear = null
from pInspectionRead.SchoolInspections
    WHERE InspectionYear <> common.SchoolYearInt(StartDate,@YearStart)

‌

[Ghislain Hachey]

From discussion with Brian. A one time thing to do.

first thing you need to do in your develop databases is:

INSERT INTO SysParams
(paramName, paramUse, paramText)
VALUES (''
, 'month, day of date of start of school year yyy-mm-dd (year ignored)'
, '2000-10-01'
)

In other words, the school year rolls over on 1 Oct. this is already done in live fedemis and miemis. this is the update script:

begin transaction
declare @YearStart date = common.SchoolYearStartDate();
Select @YearStart YearStartSysParam
Declare @INSP TABLE
(
id int
, wasYear int
)

Select inspID
, StartDate, EndDate
, InspectionSetID, InspectionYear, InspectionSetName
, FY.*
from pInspectionRead.SchoolInspections I
    CROSS APPLY common.SchoolYear(StartDate,@YearStart) FY

    WHERE InspectionYear <> FY.yr

INSERT INTO @INSP
Select inspID
, InspectionYear

from pInspectionRead.SchoolInspections I
    CROSS APPLY common.SchoolYear(StartDate,@YearStart) FY

    WHERE InspectionYear <> FY.yr

UPDATE pInspectionRead.SchoolInspections
SET InspectionSetID = null
, InspectionSetName = null
, InspectionYear = null
from pInspectionRead.SchoolInspections
    WHERE InspectionYear <> common.SchoolYearInt(StartDate,@YearStart)

Select count(inspID)
from pInspectionRead.SchoolInspections I
    CROSS APPLY common.SchoolYear(StartDate,@YearStart) FY

    WHERE InspectionYear <> FY.yr

Select inspID
, StartDate, EndDate
, InspectionSetID, InspectionYear, InspectionSetName
, FY.*
, wasYear
from pInspectionRead.SchoolInspections I
    CROSS APPLY common.SchoolYear(StartDate,@YearStart) FY
INNER JOIN @INSP INSP
    ON I.inspID = INSP.id

rollback

‌

[Brian Lewis]

feat(schoolaccreditation): sql changes; output Note, determine school year

Resolves #1238

→ <<cset 84b762490813>>