Quarterly Report

[Ghislain Hachey]

As per Brian's email:

The Quarterly survey is quite a different animal to the annual one. The annual one is a simple example of what Pineapples calls a survey – as I mentioned the other day it is cut down from the Kiribati survey, so there is quite a bit of a head-start in implementing that, given that we have a full eForm working in Kiribati.

The quarterly ones collect a small set of data - and I would be trying to talk them out of capturing the names of individuals who have transferred and dropped out – just numbers would do (of course this area touches on the much bigger question of using a school mis like schooltool.)

In the Pineapples model, I think the best place to put these quarterly reports is under Inspections. Inspection are basically data collections from a school – may be collected by a third party or self-reported – doesn’t really matter.

So we define an Inspection Type – Quarterly Survey.

We create a new inspection set of this type four times a year, in the table Inspection Set. This groups together the round of reporting (2016-Q1, 2016-Q2) etc

Each school’s return hangs off that in SchoolInspection. We can see therefore all quarterly returns from a school, and all schools within an inspection set.

Finally, we’ll need something customised here – probably another table to hold the 7 or 8 specific numbers collected on that report. This table will have a 1-1 relation to school inspection.

Alternatively, (mentioned for the sake of completeness!) SchoolInspection could get an Xml field, and the specific data items on the Quarterly survey are bundled into that Xml. SQL server is very good at digging values from Xml, which can be presented in a query exactly as though they were relational fields ( or just handed back to the client as a blob). This is fine if you just want to look at detail data without aggregating, but if you need to aggregate things it can be slow to do it this way.

I like this idea because it will let us use some of the work we have already done on inspections.

The school book stocktake would also fit into this framework, except in that case, we’d have a table of StockHoldings in 1-many relation to SchoolInspection.

Given that we have to build some of this book stuff in SI, going down this path could give you more material you can make use of in RMI.

[Ghislain Hachey]

feat(School): add support to collect/display quarterly report

BREAKING CHANGES: This work makes use of the School Inspections framework by extending the SchoolInspection table with a one-to-one relationship with another QuarterlyReport table. Some databases changes are required for this branch. Refer to issue 153 for some more details

Closes #153

→ <<cset 99eb117249bf>>

[Ghislain Hachey]

Here is the necessary SQL for using the schools inspection framework for our quarterly reports here.

--- New table to hold quarterly report data
CREATE TABLE QuarterlyReport
    ( 
        qrID INT PRIMARY KEY, --- Key set to same as inspID
        qrNumDaysOpen FLOAT(2),
        qrAggDaysAtt FLOAT(2),
        qrAggDaysAbs FLOAT(2),
        qrAggDaysMem AS qrAggDaysAtt + qrAggDaysAbs,
        qrAvgDailyAtt AS qrAggDaysAtt / qrNumDaysOpen,
        qrAvgDailyMem FLOAT(2),
        qrTotEnrolToDate INT, 
        qrPupilEnrolLastDay INT
        )
    GO

ALTER TABLE QuarterlyReport ADD FOREIGN KEY (qrID) REFERENCES SchoolInspection(inspID) ON DELETE CASCADE ON UPDATE CASCADE
GO

--- Quarterly report lookup
INSERT INTO lkpInspectionTypes (intyCode,intyDesc,intyForm) VALUES ('QUARTER','Quarterly Report','fdlgSchoolInspection');

--- InspectionSet for all previous quarterly reports (automatic primary key)
INSERT INTO InspectionSet (inspsetName,inspsetType,inspsetYear,escnID) VALUES ('2014/Q1','QUARTER',2014,NULL);
INSERT INTO InspectionSet (inspsetName,inspsetType,inspsetYear,escnID) VALUES ('2014/Q2','QUARTER',2014,NULL);
INSERT INTO InspectionSet (inspsetName,inspsetType,inspsetYear,escnID) VALUES ('2014/Q3','QUARTER',2014,NULL);
INSERT INTO InspectionSet (inspsetName,inspsetType,inspsetYear,escnID) VALUES ('2014/Q4','QUARTER',2014,NULL);
INSERT INTO InspectionSet (inspsetName,inspsetType,inspsetYear,escnID) VALUES ('2015/Q1','QUARTER',2015,NULL);
INSERT INTO InspectionSet (inspsetName,inspsetType,inspsetYear,escnID) VALUES ('2015/Q2','QUARTER',2015,NULL);
INSERT INTO InspectionSet (inspsetName,inspsetType,inspsetYear,escnID) VALUES ('2015/Q3','QUARTER',2015,NULL);
INSERT INTO InspectionSet (inspsetName,inspsetType,inspsetYear,escnID) VALUES ('2015/Q4','QUARTER',2015,NULL);
INSERT INTO InspectionSet (inspsetName,inspsetType,inspsetYear,escnID) VALUES ('2016/Q1','QUARTER',2016,NULL);
INSERT INTO InspectionSet (inspsetName,inspsetType,inspsetYear,escnID) VALUES ('2016/Q2','QUARTER',2016,NULL);
INSERT INTO InspectionSet (inspsetName,inspsetType,inspsetYear,escnID) VALUES ('2016/Q3','QUARTER',2016,NULL);
INSERT INTO InspectionSet (inspsetName,inspsetType,inspsetYear,escnID) VALUES ('2016/Q4','QUARTER',2016,NULL);

--- Sample school inspections specific to SIEMIS data (get auto generated key from DB)
INSERT INTO SchoolInspection (schNo,inspBy,inspsetID) VALUES ('AIL100','Principale Name',1); 
INSERT INTO SchoolInspection (schNo,inspBy,inspsetID) VALUES ('AIL101','Head Teacher Name',1);

--- Sample associated one-to-one quarterly reports (again, sample data specific to SIEMIS data)
INSERT INTO QuarterlyReport (qrID,qrNumDaysOpen,qrAggDaysAtt,qrAggDaysAbs,qrAvgDailyMem,qrTotEnrolToDate,qrPupilEnrolLastDay) VALUES (406,50,378,72,42,50,0);
INSERT INTO QuarterlyReport (qrID,qrNumDaysOpen,qrAggDaysAtt,qrAggDaysAbs,qrAvgDailyMem,qrTotEnrolToDate,qrPupilEnrolLastDay) VALUES (407,50,2046,54,45,50,50);

--- View pInspectionRead.QuarterlyReports of quarterly reports
CREATE VIEW pInspectionRead.QuarterlyReports
WITH VIEW_METADATA
AS
SELECT SI.inspID, SI.schNo, SI.inspStart AS StartDate, SI.inspEnd AS EndDate, SI.inspNote AS Note, SI.inspBy AS InspectedBy, QR.qrNumDaysOpen AS NumDaysOpen, QR.qrAggDaysAtt AS AggDaysAtt, QR.qrAggDaysAbs AS AggDaysAbs, QR.qrAggDaysMem As AggDaysMem, QR.qrAvgDailyAtt AS AvgDailyAtt, QR.qrAvgDailyMem AS AvgDailyMem, QR.qrTotEnrolToDate AS TotEnrolToDate, QR.qrPupilEnrolLastDay AS PupilEnrolLastDay, ISET.inspsetName AS InspQuarterlyReport, ISET.inspsetType
FROM dbo.SchoolInspection AS SI
    LEFT OUTER JOIN
    dbo.InspectionSet AS ISET ON SI.inspsetID = ISET.inspsetID
    LEFT OUTER JOIN
    dbo.QuarterlyReport AS QR ON SI.inspID = QR.qrID
    LEFT OUTER JOIN
    dbo.lkpInspectionTypes AS IT ON ISET.inspsetType = IT.intyCode
WHERE ISET.inspsetType = 'QUARTER'

--- Below are simply minor changes to two existing objects which can be done manually

--- Minor required change in view [pInspectionRead].[SchoolInspections] (see WHERE clause)
SELECT SI.inspID, SI.schNo, SI.inspPlanned AS PlannedStartDate, SI.inspStart AS StartDate, SI.inspEnd AS EndDate, SI.inspNote AS Note, SI.inspBy AS InspectedBy, ISET.inspsetName AS InspProgram, ISET.inspsetType AS InspTypeCode, IT.intyDesc AS InspType
FROM dbo.SchoolInspection AS SI
    LEFT OUTER JOIN
    dbo.InspectionSet AS ISET ON SI.inspsetID = ISET.inspsetID
    LEFT OUTER JOIN
    dbo.lkpInspectionTypes AS IT ON ISET.inspsetType = IT.intyCode
WHERE ISET.inspsetType = 'SCHINF'


--- Small change to StoredProcedure [pSchoolRead].[SchoolReadEx] (see last SELECT statement)

USE [SIEMIS-TEST]
GO
/****** Object:  StoredProcedure [pSchoolRead].[SchoolReadEx]    Script Date: 7/19/2016 2:16:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  Brian Lewis
-- Create date: 13 08 2015
-- Description: Read multiple reordsets about school
-- =============================================
ALTER PROCEDURE [pSchoolRead].[SchoolReadEx]
 -- Add the parameters for the stored procedure here
 @SchoolNo nvarchar(50)
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 SELECT * from Schools WHERE schNo = @schoolNo
 -- survey summary data
 exec pEnrolmentRead.schoolAnnualSummary  @schoolNo
 Select * from pExamRead.SchoolExams WHERE schNo = @schoolNo
 Select * from pInspectionRead.SchoolInspections WHERE schNo = @schoolNo
 Select * from pInspectionRead.QuarterlyReports WHERE schNo = @schoolNo
END

[Brian Lewis]

All good.

A couple of things I would suggest in the SQL:

1) Build a View

Build a view to represent the Quarterly Report, that joins SchoolInspection onto this quarterly report table.

thus:

CREATE VIEW dbo.QuarterlyReportView
WITH VIEW_METADATA
AS

Select SP.*
,   qrNumDaysOpen ,
        qrAggDaysAtt ,
        qrAggDaysAbs ,
        qrAggDaysMem ,
        qrDaysNotDue ,
        qrAvgDailyAtt ,
        qrAvgDailyMem ,
        qrTotEnrolToDate , 
        qrPupilEnrolLastDay 

FRom SchoolInspection SP
INNER JOIN QuarterlyReport QR
    ON SP.inspID = QR.qrID

GO 

SELECT * from QuarterlyReportView

Then you can define TRIGGERS on this view, so that it can be edited as though it were a table.

This trigger will handle both INSERT and UPDATE - DELETE you would probably do separately.

Note there is an assumption in here that there will by only one record in the batch being updated for each schNo / inspsetId combination....

CREATE TRIGGER QuarterlyReportView_Update
   ON  dbo.QuarterlyReportView
   INSTEAD OF UPDATE, INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    INSERT INTO SchoolInspection
    ( schNo
        , inspsetID
    )
    SELECT schNo 
    , inspsetID             -- etc
    from INSERTED
    WHERE inspID is null            -- ie new ones

    INSERT INTO QuarterlyReport
    (qrID
    , qrAggDaysAtt              --- etc
    )
    Select Sp.inspID
    , I.qrAggDaysAtt
    FROM INSERTED I
        INNER JOIN SchoolInspection SP
            ON I.schNo = SP.schNo
            AND I.inspsetID = SP.inspsetID
    WHERE I.inspID is null                  -- new ones


    -- now handle updates

    UPDATE SchoolInspection
    SEt inspPlanned = I.inspPlanned
    FROM SchoolInspection
        INNER JOIN INSERTED I
            ON SchoolInspection.inspID = I.inspID

    UPDATE QuarterlyReport
    SET qrAggDaysAtt = I.qrAggdaysAtt
    FROM QuarterlyReport
        INNER JOIN INSERTED I
            ON QuarterlyReport.qrID = I.inspID


END
GO

With this in place, everything from the data layer up to the surface can treat the QuarterlyReportView as though it were a native table, which is a simplification.

[Ghislain Hachey]

Thanks for the tip on trigger. The view though I already had it and it is being called at the end of your stored proc. See the code below --- View pInspectionRead.QuarterlyReports of quarterly reports in the SQL stuff I put above. Is that not the same as what you are suggesting? Leaving me with only the trigger to make the view writable? Should i have not put it into the pInspectionRead schema?

[Ghislain Hachey]

feat(School): add support to collect/display quarterly report

BREAKING CHANGES: This work makes use of the School Inspections framework by extending the SchoolInspection table with a one-to-one relationship with another QuarterlyReport table. Some databases changes are required for this branch. Refer to issue 153 for some more details

Closes #153

→ <<cset 6f7ce91dfb5c>>

[Ghislain Hachey]

I will add the trigger to insert/update when I work on the issue of CRUD'ing the quarterly reports. For now the reports are manually uploaded with focus on displaying the reports and some related chart analysis.

[Ghislain Hachey]

Take note I had to change data type of some columns in QuarterlyReport table. I edited the SQL above.

[Ghislain Hachey]

Note I also dropped a column inthe QuarterlyReport and the view pInspectionRead.QuarterlyReports

[Ghislain Hachey]

Note I also modified the insert statement to load a couple of sample data (due to removed column in QuarterlyReport table)

[Ghislain Hachey]

feat(School): add support to collect/display quarterly report

BREAKING CHANGES: This work makes use of the School Inspections framework by extending the SchoolInspection table with a one-to-one relationship with another QuarterlyReport table. Some databases changes are required for this branch. Refer to issue 153 for some more details

Closes #153

→ <<cset 6e5567908e69>>

[Ghislain Hachey]

feat(School): add support to collect/display quarterly report

BREAKING CHANGES: This work makes use of the School Inspections framework by extending the SchoolInspection table with a one-to-one relationship with another QuarterlyReport table. Some databases changes are required for this branch. Refer to issue 153 for some more details

Closes #153

→ <<cset 12a191ad58e8>>

[Brian Lewis]

fyi - this is (one way!) how to get those inserted autonumbers in the sql script - use SCOPE_IDENTITY()

declare @newInspID int
INSERT INTO InspectionSet (inspsetName,inspsetType,inspsetYear,escnID) VALUES ('2016/Q4','QUARTER',2016,NULL);
select @newInspID = SCOPE_IDENTITY()
declare @newkey int
--- Sample school inspections specific to SIEMIS data (get auto generated key from DB)
INSERT INTO SchoolInspection (schNo,inspBy,inspsetID) VALUES ('10010','Principal Name', @newInspID); 
select @newKey = SCOPE_IDENTITY();  -- scope_identity() returns the most recent identity value inserted in this scope
--- Sample associated one-to-one quarterly reports (again, sample data specific to SIEMIS data)
INSERT INTO QuarterlyReport (qrID, qrNumDaysOpen,qrAggDaysAtt,qrAggDaysAbs,qrAvgDailyMem,qrTotEnrolToDate,qrPupilEnrolLastDay) 
    VALUES (@newKey,50,378,72,42,50,0);

INSERT INTO SchoolInspection (schNo,inspBy,inspsetID) VALUES ('10090','Head Teacher Name',@newInspID);
select @newKey = SCOPE_IDENTITY()
INSERT INTO QuarterlyReport (qrID,qrNumDaysOpen,qrAggDaysAtt,qrAggDaysAbs,qrAvgDailyMem,qrTotEnrolToDate,qrPupilEnrolLastDay) 
VALUES (@newKey,50,2046,54,45,50,50);
GO

[Brian Lewis]

ALTER TABLE QuarterlyReport ADD FOREIGN KEY (qrID) REFERENCES SchoolInspection(inspID) 
ON DELETE CASCADE ON UPDATE CASCADE

I can;t get these cascades to work? It won;t add the relation. But - I think we can live without them... inspID is an autonumber, so it will never UPDATE. For delete, I think let's drive it from Quarterly Report, and have that delete SchoolInspection. This can be done through triggers on an updatable view as mentioned before.

[Brian Lewis]

Prefer this, becuaes there are already various inspection types kicking around that should appear in the inspections list:

ALTER VIEW [pInspectionRead].[SchoolInspections]
WITH VIEW_METADATA
AS
Select inspID 
, schNo
, inspPlanned PlannedStartDate
, inspStart StartDate
, inspEnd EndDate
, inspNote Note
, inspBy InspectedBy
, inspsetName   InspProgram
, inspsetType   InspTypeCode
, intyDesc      InspType
FROM SchoolInspection SI
    LEFT JOIN InspectionSet ISET
        ON SI.inspsetID = ISET.inspsetID
    LEFT JOIN lkpInspectionTypes IT
        ON ISET.inspsetType = IT.intyCode
WHERE ISET.inspsetType <> 'QUARTER'

GO

[Brian Lewis]

feat(School): add support to collect/display quarterly report

BREAKING CHANGES: This work makes use of the School Inspections framework by extending the SchoolInspection table with a one-to-one relationship with another QuarterlyReport table. Some databases changes are required for this branch. Refer to issue 153 for some more details

Closes #153

→ <<cset 12a191ad58e8>>