CREATE OR REPLACE FUNCTION reporting.pschoollastsubmission(
    par_schoolid integer DEFAULT NULL::integer,
    par_emiscode character varying DEFAULT NULL::character varying)
    RETURNS TABLE(province character varying, region character varying, district character varying, emis character varying, "SCHOOL NAME" character varying, "IMPLEMENTATION TYPE" character varying, status character varying, grades character varying, "LAST SUBMISSION" date) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
DECLARE
    var_ProgrammeID SMALLINT DEFAULT 4;
    var_Year SMALLINT DEFAULT date_part('year', clock_timestamp());
    var_TimePeriodFrom TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT make_date(var_Year, 1, 1);
    var_TimePeriodTo TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT '9999-12-31 23:59:59';
BEGIN
    /*
    [7810 - Severity CRITICAL - PostgreSQL doesn't support the SET NOCOUNT. If need try another way to send message back to the client application.]
    SET NOCOUNT ON;
    */
    CREATE TEMPORARY TABLE t$schools
    (schoolid INTEGER,
        grades VARCHAR(100),
        levels VARCHAR(100),
        gradecount SMALLINT,
        lastsubmission DATE,
        emiscode VARCHAR(9));

RAISE NOTICE 'TEMPORARY TABLE t$schools';

    /*[9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]*/
    INSERT INTO t$Schools
        (SchoolID, EMISCode)
        SELECT DISTINCT SEP.SchoolID, SEP.EMISCode
        FROM dbo.vwSchoolEnvironmentProgramme SEP --FOR SYSTEM_TIME FROM @TimePeriodFrom TO @TimePeriodTo SEP
        WHERE SEP.ImplementationStatusID IN (13,4)      -- Planned,
            AND SEP.ProgrammeID = var_ProgrammeID
            AND SEP.ProgrammeManagementLevelID = 1 --only look at automatic
            AND LENGTH(SEP.EMISCode) = 9
            --AND ISNUMERIC(SEP.EMISCOde) = 1 -- filter out ECD and AS
            AND ((par_schoolid IS NULL) OR (par_schoolid IS NOT NULL AND SEP.SchoolID = par_schoolid))
            AND ((par_emiscode IS NULL) OR (par_emiscode IS NOT NULL AND SEP.EMISCode = par_emiscode));
    

RAISE NOTICE 'INSERT #Schools';

    CREATE UNIQUE INDEX ix_t$schools_ix1 ON t$schools
        (schoolid);
    CREATE TEMPORARY TABLE t$schoolgrades
    (schoolid INTEGER,
        gradeid SMALLINT,
        managementlevel INTEGER);

RAISE NOTICE 'CREATE UNIQUE INDEX ix_t$schools_ix1';

    
    /*[9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]*/
    INSERT INTO t$SchoolGrades 
        (SchoolID, GradeID, ManagementLevel)
        SELECT DISTINCT SEP.SchoolID, TG.GradeID, TG.LearnerManagementLevelID
        FROM dbo.vwSchoolEnvironmentProgramme AS SEP --FOR SYSTEM_TIME FROM @TimePeriodFrom TO @TimePeriodTo SEP
        LEFT JOIN dbo.ProgrammeEnvironmentHasTargetGrade TG ON SEP.ProgrammeEnvironmentID = TG.ProgrammeEnvironmentID
        WHERE SEP.ImplementationStatusID IN (13,4)      -- Planned,
            AND SEP.ProgrammeID = var_ProgrammeID
            AND SEP.ProgrammeManagementLevelID = 1
            AND LENGTH(SEP.EMISCode) = 9;
            --AND ISNUMERIC(SEP.EMISCOde) = 1
            --AND TSTZRANGE(var_TimePeriodFrom, var_TimePeriodTo);

RAISE NOTICE 'INSERT #SchoolGrades';

    /* filter out ECD and AS */
    CREATE UNIQUE INDEX ix_t$schoolgrades_ix1 ON t$schoolgrades
        (schoolid, gradeid);
        
    UPDATE t$schools AS s
    SET grades = g.grades, levels = g.levels, gradecount = g.gradecount
    FROM (SELECT
        --g.schoolid, string_agg(CAST (gradeid AS VARCHAR(30)), ';') WITHIN GROUP (ORDER BY gradeid ASC NULLS FIRST) AS grades, string_agg(CAST (managementlevel AS VARCHAR(30)), ';') WITHIN GROUP (ORDER BY gradeid ASC NULLS FIRST) AS levels, COUNT(*) AS gradecount
        g.schoolid, string_agg(CAST (gradeid AS VARCHAR(30)), ';' ORDER BY gradeid ASC NULLS FIRST) AS grades, string_agg(CAST (managementlevel AS VARCHAR(30)), ';' ORDER BY gradeid ASC NULLS FIRST) AS levels, COUNT(*) AS gradecount        
        FROM t$schoolgrades AS g
        GROUP BY g.schoolid) AS g
        WHERE g.schoolid = s.schoolid;
        
    --PERFORM dbo.fgetschoolhierarchy(ts.schoolid);
    
    UPDATE t$schools AS s
    SET lastsubmission = sl.lastsubmissiondate
    FROM (SELECT
        sds.schoolid, MAX(sds.submissiondate) AS lastsubmissiondate
        FROM dbo.schoolhasdatasource AS sds
        WHERE sds.datasourcetypeid IN (1, 2)
        GROUP BY sds.schoolid) AS sl
        WHERE s.schoolid = sl.schoolid;

RAISE NOTICE 'CREATE UNIQUE INDEX ix_t$schoolgrades_ix1';

    /* JOIN */
    /* ( */
    /* SELECT SL.EMISCode, */
    /* MAX(SL.LastSubmissionDate)   LastSubmissionDate */
    /* FROM SourceLearner SL */
    /* GROUP BY SL.EMISCode */
    /* ) SL ON SL.EMISCode = S.EMISCode */
    
    /*
    [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]*/
    RETURN QUERY
    SELECT DISTINCT
            COALESCE(H.EntityName2,'')                          PROVINCE,
            COALESCE(H.ReportEntityName2, '')                   REGION,
            COALESCE(H.EntityName4,'')                          DISTRICT,
            SEP.EMISCode                                        EMIS,
            SEP.Name                                            "SCHOOL NAME",
            IT.Description                                      "IMPLEMENTATION TYPE",
            COALESCE(IST.Description,'')                        STATUS,
            TS.Grades                                           GRADES,
            TS.LastSubmission                                   "LAST SUBMISSION"
        FROM t$Schools TS
        INNER JOIN dbo.vwSchoolEnvironmentProgramme SEP ON SEP.SchoolID = TS.SchoolID and SEP.ProgrammeID = var_ProgrammeID
        LEFT JOIN dbo.ImplementationType IT ON SEP.ImplementationTypeID = IT.ImplementationTypeID
        LEFT JOIN dbo.ImplementationStatus IST ON SEP.ImplementationStatusID = IST.ImplementationStatusID
        CROSS JOIN LATERAL dbo.fGetSchoolHierarchy(TS.SchoolID) H
        ORDER BY SEP.Name;

RAISE NOTICE 'SELECT FROM #Schools TS';

    --return_code := 1;
    --RETURN;
    
    
    DROP TABLE IF EXISTS t$schools;
    
    /*
    
    Temporary table must be removed before end of the function.
    */
    
    
    DROP TABLE IF EXISTS t$schoolgrades;
   
    /*
    
    Temporary table must be removed before end of the function.
    */
END;
$BODY$;

ALTER FUNCTION reporting.pschoollastsubmission(integer, character varying)
    OWNER TO postgres;
