-- FUNCTION: internal.pactivateschoolreadingeggsf(bigint)

-- DROP FUNCTION IF EXISTS internal.pactivateschoolreadingeggsf(bigint);

CREATE OR REPLACE FUNCTION internal.pactivateschoolreadingeggsf(
	par_dataimportid bigint)
    RETURNS TABLE(schoolid integer, emiscode character varying, schoolname character varying, programme character varying, programmecode character varying, province character varying, district character varying, reportingregion character varying, classcount integer, activeclasscount integer, learnercount integer, activelearnercount integer, activelearner double precision, activelearneractivity double precision, activegradecount integer, gradeactivatedcount integer) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
DECLARE
    var_TranCountOnEntry INTEGER = 0
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the @@TRANCOUNT function. Create a user-defined function.]
    = @@TRANCOUNT
    */;
    var_DataDate DATE;
    var_Year SMALLINT;
    var_Friday DATE;
    var_ActivateDate DATE;
    var_SourceDataImportID BIGINT;
    var_SourcePrevDataImportID BIGINT;
    var_LogDescription VARCHAR(255);
    var_LogData VARCHAR(2048);
    var_RowCount INTEGER;
    var_Sql TEXT;
    ptemporalstatus$par_StatusChanged NUMERIC;
    ptemporalstatus$ReturnCode INTEGER;
	
   
BEGIN
	DROP TABLE IF EXISTS activerule$pactivateschoolreadingeggs;
    DROP TABLE IF EXISTS activatewithtable$pactivateschoolreadingeggs;
   	DROP TABLE IF EXISTS t$pendingschool;
   	DROP TABLE IF EXISTS t$rawdata;
   	DROP TABLE IF EXISTS t$activatepe;
   	DROP TABLE IF EXISTS t$targetgrade;
	DROP TABLE IF EXISTS t$learnerdata;
    DROP TABLE IF EXISTS t$gradeData;
    DROP TABLE IF EXISTS t$gradeChecks;
    DROP TABLE IF EXISTS t$schoolCalc;
	
     /*
    [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;
    */
    SELECT
        sdi.dataimportid, sdi.datacontractdatadate, sdi.datacontractdatadate + (- 2::NUMERIC || ' DAY')::INTERVAL, date_part('year', sdi.datacontractdatadate), sdi.datacontractdatadate + (- 6::NUMERIC || ' DAY')::INTERVAL
        INTO var_SourceDataImportID, var_DataDate, var_Friday, var_Year, var_ActivateDate
        FROM internal.dataimport AS di
        JOIN dbo.dataimportdatacontract_staging AS sdi
            ON sdi.dataimportid = di.sourcedataimportid
        WHERE di.dataimportid = par_DataImportID;
		
	RAISE NOTICE 'par_DataImportID: %',par_DataImportID;
	RAISE NOTICE 'var_SourceDataImportID: %',var_SourceDataImportID;
	RAISE NOTICE 'var_DataDate: %',var_DataDate;
	
    SELECT
        MAX(di.dataimportid)
        INTO var_SourcePrevDataImportID
        FROM dbo.dataimportdatacontract_staging AS di
        JOIN internal.dataimport AS dwdi
            ON di.dataimportid = dwdi.sourcedataimportid
        WHERE di.datasourcetypeid = 2 AND LOWER(di.dataimportstatus) = LOWER('Success') AND LOWER(dwdi.dataimportstatus) = LOWER('Success') AND di.datacontractdatadate < var_DataDate;
		
	RAISE NOTICE 'var_SourcePrevDataImportID: %',var_SourcePrevDataImportID;
	RAISE NOTICE 'CREATE TEMPORARY TABLE activerule$pactivateschoolreadingeggs';
    CREATE TEMPORARY TABLE activerule$pactivateschoolreadingeggs
    (hierarchyid INTEGER,
        ruletype VARCHAR(20),
        rulevalue DOUBLE PRECISION,
        rulevaluetype VARCHAR(1));
		
    INSERT INTO activerule$pactivateschoolreadingeggs (hierarchyid, ruletype, rulevalue, rulevaluetype)
    SELECT
		NULLIF(SUBSTR(sp.name, LENGTH('Auto Activate Rule: ') + 1, 100),'')::Integer AS hierarchyid,
	   		sp.value1 AS ruletype,
        CASE
            WHEN LOWER(RIGHT(sp.value2, 1)) = LOWER('%') THEN CAST (LEFT(sp.value2, LENGTH(sp.value2) - 1) AS DOUBLE PRECISION) / 100.0
            ELSE CAST (sp.value2 AS DOUBLE PRECISION)
        END AS rulevalue,
        CASE
            WHEN LOWER(RIGHT(sp.value2, 1)) = LOWER('%') THEN '%'
            ELSE ''
        END AS rulevaluetype
        FROM dbo.systemparameter AS sp
        JOIN dbo.programme AS p
            ON p.programmeid = 4 AND (LOWER(p.code) LIKE LOWER(sp.value) OR CAST (p.programmeid AS VARCHAR(30)) = sp.value)
        WHERE LOWER(sp.category) = LOWER('Processing') AND LOWER(sp.subcategory) = LOWER('ProgrammeEnvironment') AND LOWER(sp.name) SIMILAR TO LOWER('Auto Activate Rule: %');
		
		GET DIAGNOSTICS var_RowCount = ROW_COUNT;  
		RAISE NOTICE 'activerule$pactivateschoolreadingeggs  inserted rows: %',var_RowCount;
		
	RAISE NOTICE 'CREATE TEMPORARY TABLE activatewithtable$pactivateschoolreadingeggs';
	
    CREATE TEMPORARY TABLE activatewithtable$pactivateschoolreadingeggs
    (hierarchyid INTEGER,
        otherprogrammeid SMALLINT);
		
    INSERT INTO activatewithtable$pactivateschoolreadingeggs (hierarchyid, otherprogrammeid)
    SELECT DISTINCT
        NULLIF(SUBSTR(sp.name, LENGTH('Auto Activate Programme: ') + 1, 100),'')::Integer, CAST (sp.value1 AS SMALLINT)
        FROM dbo.systemparameter AS sp
        WHERE 
		LOWER(sp.category) = LOWER('Processing') AND LOWER(sp.subcategory) = LOWER('ProgrammeEnvironment') AND LOWER(sp.name) SIMILAR TO LOWER('Auto Activate Programme: %') AND sp.value = '4';
--     PERFORM dbo.fgetschoolhierarchy(sep.schoolid );
--     PERFORM dbo.fgetschoolhierarchy(s.schoolid);

		GET DIAGNOSTICS var_RowCount = ROW_COUNT;  
		RAISE NOTICE 'activatewithtable$pactivateschoolreadingeggs  inserted rows: %',var_RowCount;

    RAISE NOTICE 'CREATE TEMPORARY TABLE t$pendingschool';
        CREATE TEMPORARY TABLE t$pendingschool
        (hierarchyid INTEGER,
            schoolid INTEGER,
            programmeid SMALLINT,
            programmename VARCHAR(50),
            programmecode VARCHAR(30),
            programmeenvironmentid INTEGER,
            programmemanagementlevelid SMALLINT,
            activate NUMERIC(1, 0),
            classcount INTEGER,
            activeclasscount INTEGER,
            learnercount INTEGER,
            activelearnercount INTEGER,
            activelearneractivity DOUBLE PRECISION,
            activegradecount INTEGER,
            gradeactivatedcount INTEGER);
       
        
        INSERT INTO t$pendingschool(HierarchyID, SchoolID, ProgrammeID, ProgrammeCode, ProgrammeEnvironmentID, ProgrammeManagementLevelID, ProgrammeName)
        		SELECT H.EntityID1, SEP.SchoolID, SEP.ProgrammeID, SEP.ProgrammeCode, SEP.ProgrammeEnvironmentID, SEP.ProgrammeManagementLevelID, P.Name
        		FROM dbo.vwSchoolEnvironmentProgramme SEP
        		JOIN dbo.Programme P ON SEP.ProgrammeID = P.ProgrammeID
        		LEFT   JOIN LATERAL dbo.fGetSchoolHierarchy(SEP.SchoolID) H ON true
        		WHERE SEP.ProgrammeID = 4
        			AND
        			(
        				SEP.ImplementationStatusID IN (1, 3, 13) -- Pending, Pipeline, Planned
        				---- REMOVE
        				--OR
        				--(
        				--	SEP.ImplementationStatusID = 4 AND SEP.ImplementationStatusActiveDate = '2020-08-17'
        				--)
        			)
        			AND SEP.ProgrammeCode IS NOT NULL;
			
		GET DIAGNOSTICS var_RowCount = ROW_COUNT;  
		RAISE NOTICE 't$pendingschool inserted rows: %',var_RowCount;
        
        --RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %', '50000', 0, 0, 'Pending/Pipeline/Planned Schools: %d', "var_@ROWCOUNT" USING ERRCODE = '50000';
        /* INSERT #PendingSchool(SchoolID, ProgrammeID, ProgrammeCode, ProgrammeEnvironmentID, ProgrammeManagementLevelID, ProgrammeName) */
        /* SELECT SEP.SchoolID, SEP.ProgrammeID, SEP.ProgrammeCode, SEP.ProgrammeEnvironmentID, SEP.ProgrammeManagementLevelID, P.[Name] */
        /* FROM #PendingSchool X */
        /* --CROSS JOIN (SELECT TRY_CAST(Value1 AS smallint) OtherProgrammeID FROM SystemParameter SP WHERE SP.Category = 'Processing' AND SP.SubCategory = 'ProgrammeEnvironment' AND SP.Name = 'Activate With' AND Value = '4') XP */
        /* JOIN Programme P ON 4 = P.ProgrammeID */
        /* JOIN vwSchoolEnvironmentProgramme SEP ON X.SchoolID = SEP.SchoolID AND 4 = SEP.ProgrammeID */
        /* WHERE SEP.ImplementationStatusID IN (1, 3, 13) -- Pending, Pipeline, Planned */
        
        /*
        [7927 - Severity CRITICAL - PostgreSQL doesn't support OUTER joins for self-reference to the label from UPDATE / DELETE in the FROM clause and without PK on table-for-update. Perform a manual conversion.]*/
        UPDATE t$pendingschool ps
		SET ActiveGradeCount = COALESCE(U.ActiveGradeCount,0),
			GradeActivatedCount = 0
-- 		FROM t$pendingschool PS
		FROM (
        			SELECT PS.ProgrammeEnvironmentID, COUNT(*) ActiveGradeCount
        			FROM t$pendingschool PS
        			--JOIN dbo.ProgrammeEnvironmentHasTargetGrade FOR SYSTEM_TIME AS OF @Friday TG ON PS.ProgrammeEnvironmentID = TG.ProgrammeEnvironmentID AND TG.ActiveFrom <= @Friday
					 JOIN dbo.ProgrammeEnvironmentHasTargetGrade  TG ON PS.ProgrammeEnvironmentID = TG.ProgrammeEnvironmentID AND TG.ActiveFrom <= Var_Friday
        			GROUP BY PS.ProgrammeEnvironmentID
        		) U WHERE PS.ProgrammeEnvironmentID = U.ProgrammeEnvironmentID;
-- 		UPDATE PS SET ActiveGradeCount = ISNULL(U.ActiveGradeCount, 0), GradeActivatedCount = 0
--         		FROM t$pendingschool PS
--         		LEFT JOIN
--         		(
--         			SELECT PS.ProgrammeEnvironmentID, COUNT(*) ActiveGradeCount
--         			FROM t$pendingschool PS
--         			--JOIN dbo.ProgrammeEnvironmentHasTargetGrade FOR SYSTEM_TIME AS OF @Friday TG ON PS.ProgrammeEnvironmentID = TG.ProgrammeEnvironmentID AND TG.ActiveFrom <= @Friday
-- 					 JOIN dbo.ProgrammeEnvironmentHasTargetGrade  TG ON PS.ProgrammeEnvironmentID = TG.ProgrammeEnvironmentID AND TG.ActiveFrom <= Var_Friday
--         			GROUP BY PS.ProgrammeEnvironmentID
--         		) U ON PS.ProgrammeEnvironmentID = U.ProgrammeEnvironmentID;

        GET DIAGNOSTICS var_RowCount = ROW_COUNT;  
		RAISE NOTICE 't$pendingschool updated rows: %',var_RowCount;
		RAISE NOTICE 'var_Friday: %',var_Friday;
		RAISE NOTICE 'var_SourceDataImportID: %',var_SourceDataImportID;
		RAISE NOTICE 'var_SourcePrevDataImportID: %',var_SourcePrevDataImportID;
		RAISE NOTICE 'CREATE TEMPORARY TABLE t$rawdata';
        CREATE TEMPORARY TABLE t$rawdata
        AS
        SELECT
            s.hierarchyid, s.schoolid, s.programmecode, s.programmeenvironmentid, sly.gradeid, sly.schoolclassid, sl.personid,
            CASE
                WHEN re."re time (sec)" IS NULL AND re."rex time (sec)" IS NULL THEN NULL
                ELSE COALESCE(re."re time (sec)", 0) + COALESCE(re."rex time (sec)", 0)
            END AS tot,
			(COALESCE(re."re time (sec)", 0) + COALESCE(re."rex time (sec)", 0)) - (COALESCE(re1."re time (sec)", 0) + COALESCE(re1."rex time (sec)", 0)) AS totmovement
            FROM t$pendingschool AS s
            JOIN dbo.schoollearner AS sl
                ON s.schoolid = sl.schoolid  AND var_Friday BETWEEN sl.startdate AND sl.enddate
            JOIN dbo.schoollearneryear AS sly
                ON sl.schoollearnerid = sly.schoollearnerid  AND sly.year = var_Year
            JOIN dbo.personhasprogramme AS pp
                ON sl.personid = pp.personid AND pp.programmeid = 4
            JOIN dbo.vwtargetpersonid AS tp
                ON sl.personid = tp.targetpersonid
            LEFT OUTER JOIN dbo.readingeggs2_staging AS re
                ON re.dataimportid = var_SourceDataImportID AND LOWER(re."school code") = LOWER(s.programmecode) AND re."external student id" = tp.personid
            LEFT OUTER JOIN dbo.readingeggs2_staging AS re1
                ON re1.dataimportid = var_SourcePrevDataImportID AND LOWER(re."school code") = LOWER(re1."school code") AND re."student id" = re1."student id"
            WHERE s.programmeid = 4 and re."re time (sec)" IS not NULl
        /* IF @ActivateRuleType = 'Learner' */
        /* BEGIN */
        ;
        
		GET DIAGNOSTICS var_RowCount = ROW_COUNT;  
		RAISE NOTICE 'Create temp table t$rawdata rows: %',var_RowCount;

		create temp table t$learnerData
		as
		SELECT MAX(D.HierarchyID) HierarchyID, D.SchoolID SchoolID, SUM(CASE WHEN D.ToTMovement > 0 THEN 1 ELSE 0 END ) ActiveLearnerCount, COUNT(*) LearnerCount
        			FROM t$rawdata D
--         			JOIN var_ActiveRule R ON D.HierarchyID = R.HierarchyID AND R.RuleType = 'Learner'
					JOIN activerule$pactivateschoolreadingeggs R ON D.HierarchyID = R.HierarchyID AND R.RuleType = 'Learner'
        			GROUP BY D.SchoolID;
		GET DIAGNOSTICS var_RowCount = ROW_COUNT;  
		RAISE NOTICE 'create temp table t$learnerData rows: %',var_RowCount;
--         WITH learnerData(HierarchyID, SchoolID, ActiveLearnerCount, LearnerCount) AS
--         		(
--         			SELECT MAX(D.HierarchyID), D.SchoolID, SUM(CASE WHEN D.ToTMovement > 0 THEN 1 ELSE 0 END), COUNT(*)
--         			FROM t$rawdata D
-- --         			JOIN var_ActiveRule R ON D.HierarchyID = R.HierarchyID AND R.RuleType = 'Learner'
-- 					JOIN activerule$pactivateschoolreadingeggs R ON D.HierarchyID = R.HierarchyID AND R.RuleType = 'Learner'
--         			GROUP BY D.SchoolID
--         		)
        		UPDATE t$pendingschool sl  SET
        			Activate = 1,
        			ClassCount = null,
        			ActiveClassCount = null,
        			ActiveLearnerActivity = null,
        			LearnerCount = L.LearnerCount,
        			ActiveLearnerCount = L.ActiveLearnerCount
        		FROM t$pendingschool S
--         		JOIN var_ActiveRule R ON S.HierarchyID = R.HierarchyID AND R.RuleType = 'Learner'
				JOIN activerule$pactivateschoolreadingeggs R ON  S.HierarchyID = R.HierarchyID AND R.RuleType = 'Learner'
        		JOIN t$learnerData L ON S.SchoolID = L.SchoolID
        		WHERE
				sl.schoolid = s.schoolid
        			and S.Activate IS NULL
        			AND
        			(
        				((R.RuleValueType = '%') AND ((CAST(L.ActiveLearnerCount AS float) / CAST(NULLIF(L.LearnerCount,0) AS float)) >= R.RuleValue))
        				OR
        				((R.RuleValueType = '' ) AND (L.ActiveLearnerCount >= R.RuleValue))
        			)
        
        		--END
        		--ELSE IF @ActivateRuleType = 'Grade'
        		--BEGIN
        		;
       
	   	GET DIAGNOSTICS var_RowCount = ROW_COUNT;  
		RAISE NOTICE 'UPDATE t$pendingschool rows: %',var_RowCount;
        /*
        [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers., 9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]*/

	   CREATE TEMP TABLE t$gradeData
	   AS 
	   SELECT
			MAX(D.HierarchyID) HierarchyID,
			D.SchoolID SchoolID,
			D.GradeID GradeID,
			COUNT(D.PersonID) LearnerCount,
			COUNT(CASE WHEN D.ToT IS NOT NULL AND D.TotMovement > 0 THEN D.PersonID ELSE NULL END) ActiveLearnerCount,
			--CAST(COUNT(CASE WHEN D.ToT IS NOT NULL AND D.TotMovement > 0 THEN D.PersonID ELSE NULL END) AS float) / CAST(COUNT(D.PersonID) AS float) ActiveLearner,
			AVG(CAST(CASE WHEN D.ToT IS NOT NULL AND D.ToTMovement > 0 THEN D.ToTMovement ELSE NULL END AS float)) / 60.0 ActiveLearnerActivity
		FROM t$rawdata D
--         		JOIN var_ActiveRule R ON D.HierarchyID = R.HierarchyID AND R.RuleType = 'Grade'					
		JOIN activerule$pactivateschoolreadingeggs R ON D.HierarchyID = R.HierarchyID AND R.RuleType = 'Grade'
		GROUP BY D.SchoolID, D.GradeID;
		
		CREATE TEMP TABLE t$gradeChecks
		AS       		
		SELECT
			MAX(D.HierarchyID) HierarchyID,
			D.SchoolID SchoolID,
			COUNT(*) GradeCount,
			SUM(CASE
					WHEN R.RuleValueType = ''  AND D.ActiveLearnerActivity >= 10.0 AND D.ActiveLearnerCount >= R.RuleValue THEN 1
					WHEN R.RuleValueType = '%' AND D.ActiveLearnerActivity >= 10.0 AND (CAST(D.ActiveLearnerCount AS float) / CAST(NULLIF(D.LearnerCount,0) AS float)) >= R.RuleValue THEN 1
					ELSE 0
				END) QualifyingGrade
		FROM t$gradeData D
--         		JOIN var_ActiveRule R ON D.HierarchyID = R.HierarchyID AND R.RuleType = 'Grade'					
		JOIN activerule$pactivateschoolreadingeggs R ON D.HierarchyID = R.HierarchyID AND R.RuleType = 'Grade'
		GROUP BY D.SchoolID;
		
		CREATE TEMP TABLE t$schoolCalc
		AS
		SELECT
			MAX(D.HierarchyID) HierarchyID,
			D.SchoolID SchoolID,
			COUNT(*) LearnerCount,
			CAST(COUNT(CASE WHEN D.ToT IS NOT NULL AND D.TotMovement > 0 THEN D.PersonID ELSE NULL END) AS float) ActiveLearnerCount,
			AVG(CAST(CASE WHEN D.ToT IS NOT NULL AND D.ToTMovement > 0 THEN D.ToTMovement ELSE NULL END AS float)) / 60.0 ActiveLearnerActivity
		FROM t$rawdata D
--         		JOIN var_ActiveRule R ON D.HierarchyID = R.HierarchyID AND R.RuleType = 'Grade'				
		JOIN activerule$pactivateschoolreadingeggs R ON D.HierarchyID = R.HierarchyID AND R.RuleType = 'Grade'
		GROUP BY D.SchoolID;
   
					
-- 	   WITH gradeData(HierarchyID, SchoolID, GradeID, LearnerCount, ActiveLearnerCount, ActiveLearnerActivity) AS
--         		(
--         			SELECT
--         				MAX(D.HierarchyID),
--         				D.SchoolID,
--         				D.GradeID,
--         				COUNT(D.PersonID) LearnerCount,
--         				COUNT(CASE WHEN D.ToT IS NOT NULL AND D.TotMovement > 0 THEN D.PersonID ELSE NULL END) ActiveLearnerCount,
--         				--CAST(COUNT(CASE WHEN D.ToT IS NOT NULL AND D.TotMovement > 0 THEN D.PersonID ELSE NULL END) AS float) / CAST(COUNT(D.PersonID) AS float) ActiveLearner,
--         				AVG(CAST(CASE WHEN D.ToT IS NOT NULL AND D.ToTMovement > 0 THEN D.ToTMovement ELSE NULL END AS float)) / 60.0 ActiveLearnerActivity
--         			FROM t$rawdata D
-- --         		JOIN var_ActiveRule R ON D.HierarchyID = R.HierarchyID AND R.RuleType = 'Grade'					
--         			JOIN activerule$pactivateschoolreadingeggs R ON D.HierarchyID = R.HierarchyID AND R.RuleType = 'Grade'
--         			GROUP BY D.SchoolID, D.GradeID
--         		)
--         		, gradeChecks(HierarchyID, SchoolID, GradeCount, QualifyingGrade) AS
--         		(
--         			SELECT
--         				MAX(D.HierarchyID),
--         				D.SchoolID,
--         				COUNT(*),
--         				SUM(CASE
--         						WHEN R.RuleValueType = ''  AND D.ActiveLearnerActivity >= 10.0 AND D.ActiveLearnerCount >= R.RuleValue THEN 1
--         						WHEN R.RuleValueType = '%' AND D.ActiveLearnerActivity >= 10.0 AND (CAST(D.ActiveLearnerCount AS float) / CAST(NULLIF(D.LearnerCount,0) AS float)) >= R.RuleValue THEN 1
--         						ELSE 0
--         					END) QualifyGrade
--         			FROM gradeData D
-- --         		JOIN var_ActiveRule R ON D.HierarchyID = R.HierarchyID AND R.RuleType = 'Grade'					
--         			JOIN activerule$pactivateschoolreadingeggs R ON D.HierarchyID = R.HierarchyID AND R.RuleType = 'Grade'
--         			GROUP BY D.SchoolID
--         		)
--         		, schoolCalc(HierarchyID, SchoolID, LearnerCount, ActiveLearnerCount, ActiveLearnerActivity) AS
--         		(
--         			SELECT
--         				MAX(D.HierarchyID),
--         				D.SchoolID,
--         				COUNT(*),
--         				CAST(COUNT(CASE WHEN D.ToT IS NOT NULL AND D.TotMovement > 0 THEN D.PersonID ELSE NULL END) AS float),
--         				AVG(CAST(CASE WHEN D.ToT IS NOT NULL AND D.ToTMovement > 0 THEN D.ToTMovement ELSE NULL END AS float)) / 60.0
--         			FROM t$rawdata D
-- --         		JOIN var_ActiveRule R ON D.HierarchyID = R.HierarchyID AND R.RuleType = 'Grade'				
--         			JOIN activerule$pactivateschoolreadingeggs R ON D.HierarchyID = R.HierarchyID AND R.RuleType = 'Grade'
--         			GROUP BY D.SchoolID
--         		)
        		UPDATE t$pendingschool sl  SET
        			Activate = 1,
        			ClassCount = C.GradeCount,
        			ActiveClassCount = C.QualifyingGrade,
        			ActiveLearnerActivity = SC.ActiveLearnerActivity,
        			LearnerCount = SC.LearnerCount,
        			ActiveLearnerCount = SC.ActiveLearnerCount
        		FROM t$pendingschool S
--         		JOIN var_ActiveRule R ON S.HierarchyID = R.HierarchyID AND R.RuleType = 'Grade'				
        		JOIN activerule$pactivateschoolreadingeggs R ON S.HierarchyID = R.HierarchyID AND R.RuleType = 'Grade'
        		JOIN t$gradeChecks C ON S.SchoolID = C.SchoolID
        		JOIN t$schoolCalc SC ON S.SchoolID = SC.SchoolID
        		WHERE
					sl.schoolid = s.schoolid
        			AND S.Activate IS NULL
        			AND C.QualifyingGrade >= 1;
					
        GET DIAGNOSTICS var_RowCount = ROW_COUNT;  
		RAISE NOTICE 'UPDATE t$pendingschool rows: %',var_RowCount;

		/* END */
        UPDATE t$pendingschool
        SET activate = 0
            WHERE activate IS NULL;
			
        UPDATE t$pendingschool AS ps
        SET activate = 0
        FROM dbo.programmeenvironment AS pe
            WHERE ps.activate = 1 AND pe.implementationstatusid = 4 AND ps.programmeenvironmentid = pe.programmeenvironmentid;
        /* REMOVE */
        /* AND PE.SysStartTime < '2020-08-17' */
		RAISE NOTICE 'before CREATE TEMPORARY TABLE t$ActivatePE if statment';
        IF EXISTS (SELECT
            *
            FROM t$pendingschool
            WHERE activate = 1) THEN
            CREATE TEMPORARY TABLE t$activatepe
            (programmeenvironmentid INTEGER);
           
		RAISE NOTICE 'CREATE TEMPORARY TABLE t$ActivatePE';

-- 		CREATE TEMPORARY TABLE t$ActivatePE
-- 		(
-- 			ProgrammeEnvironmentID bigint
-- 		);
		   
		   ---OUTUT ERROR 274.
		   WITH updated AS(
            UPDATE dbo.programmeenvironment SET ImplementationStatusID = 4
            			--OUTPUT inserted.ProgrammeEnvironmentID --INTO t$ActivatePE(ProgrammeEnvironmentID)
            			FROM dbo.programmeenvironment PE
            			JOIN
            			(
            				SELECT PS.ProgrammeEnvironmentID
            				FROM t$pendingschool PS
            				WHERE PS.Activate = 1
            				UNION
            				SELECT SEP.ProgrammeEnvironmentID
            				FROM t$pendingschool PS
            				JOIN activatewithtable$pactivateschoolreadingeggs AWT ON PS.HierarchyID = AWT.HierarchyID
            				JOIN dbo.vwSchoolEnvironmentProgramme SEP ON PS.SchoolID = SEP.SchoolID AND SEP.ProgrammeID = AWT.OtherProgrammeID
            				WHERE PS.Activate = 1
            					AND SEP.ImplementationStatusID IN (1, 3, 13)
            			) PS ON PS.ProgrammeEnvironmentID = PE.ProgrammeEnvironmentID
			   )
			   INSERT INTO t$ActivatePE SELECT * FROM updated;
          
            GET DIAGNOSTICS var_RowCount = ROW_COUNT;
			RAISE NOTICE 't$ActivatePE ROW COUNT: %',var_RowCount;
			
            INSERT INTO internal.dataimportlog (dataimportid, loglevelid, dataid, code, description, data)
            SELECT
                par_DataImportID, 2, sep.schoolid, 20006, 'School ' || p.code || ' Activated', sep.name || ' activated for ' || p.code
                FROM t$activatepe AS x
                JOIN dbo.vwschoolenvironmentprogramme AS sep
                    ON x.programmeenvironmentid = sep.programmeenvironmentid
                JOIN dbo.programme AS p
                    ON sep.programmeid = p.programmeid;
					
			GET DIAGNOSTICS var_RowCount = ROW_COUNT;
			RAISE NOTICE 'INSERT INTO internal.dataimportlog ROW COUNT: %',var_RowCount;	
			
            var_LogData := CAST (var_RowCount AS VARCHAR(20));
			
            CALL internal.pdataimportloginsert(par_DataImportID, 'School Programmes Activated', 'I', var_LogData, 0, 20006);
            /* SELECT PS.ProgrammeEnvironmentID, PE.SysEndTime, PEH.SysEndTime, @ActivateDate */
            /* FROM #ActivatePE PS */
            /* JOIN ProgrammeEnvironment PE ON PS.ProgrammeEnvironmentID = PE.ProgrammeEnvironmentID */
            /* JOIN ProgrammeEnvironmentHistory PEH ON PE.ProgrammeEnvironmentID = PEH.ProgrammeEnvironmentID AND PE.SysStartTime = PEH.SysEndTime */
            var_Sql := '
			UPDATE ProgrammeEnvironmentHistory PEH 
				SET SysEndTime = '|| var_ActivateDate ||'
			FROM t$ActivatePE PS
			JOIN ProgrammeEnvironment PE ON PS.ProgrammeEnvironmentID = PE.ProgrammeEnvironmentID
			WHERE  PE.ProgrammeEnvironmentID = PEH.ProgrammeEnvironmentID AND PE.SysStartTime = PEH.SysEndTime;

			UPDATE ProgrammeEnvironment PE SET SysStartTime ='|| var_ActivateDate ||'
			FROM t$ActivatePE PS
			WHERE PS.ProgrammeEnvironmentID = PE.ProgrammeEnvironmentID;
			';
			EXECUTE var_Sql;
			
            CALL internal.ptemporalstatus('dbo.ProgrammeEnvironment', 0,par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
            /*
            [7672 - Severity CRITICAL - Automatic conversion of this command is not supported. Perform a manual conversion.]
            EXEC sp_executesql @stmt=@Sql, @params=N'@ActivateDate date', @ActivateDate=@ActivateDate
            */
			EXECUTE var_Sql;
			
            CALL internal.ptemporalstatus('dbo.ProgrammeEnvironment', 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);

            IF EXISTS (SELECT
                *
                FROM t$pendingschool
                WHERE activate = 1 AND activegradecount = 0) THEN
                CREATE TEMPORARY TABLE t$targetgrade
                AS
                WITH rawdata (programmeenvironmentid, programmemanagementlevelid, "Student Grade")
                AS (SELECT
                    s.programmeenvironmentid, s.programmemanagementlevelid, re."Student Grade"
                    FROM t$pendingschool AS s
                    JOIN dbo.readingeggs2_stagingAS re
                        ON re.dataimportid = var_SourceDataImportID AND re."School code" = s.programmecode
                    WHERE s.programmeid = 4 AND s.activate = 1 AND s.activegradecount = 0 AND LOWER(re."Student First Name") SIMILAR TO LOWER('Demo Student%') AND LOWER(re."Student First Name") NOT LIKE LOWER('DemoStudent%') AND LOWER(re."Student Login") NOT LIKE LOWER('demostudent%') AND LOWER(re."Student Login") NOT LIKE LOWER('demo#_student%') ESCAPE '#' AND re."Student Grade" IS NOT NULL
                    GROUP BY s.programmeenvironmentid, re."Student Grade")
                
				SELECT DISTINCT
                    programmeenvironmentid, programmemanagementlevelid,
                    CASE
                        WHEN LOWER("Student Grade") = LOWER('Rec') THEN 0
                        ELSE
                        CASE
                            WHEN aws_sqlserver_ext.ISNUMERIC("Student Grade") THEN CAST ("Student Grade" AS SMALLINT)
                            ELSE NULL
                        END
                    END AS gradeid
                    FROM rawdata;
                
				CALL internal.ptemporalstatus('dbo.ProgrammeEnvironmentHasTargetGrade', 0, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
                
				INSERT INTO dbo.programmeenvironmenthastargetgrade (programmeenvironmentid, gradeid, activefrom, sysstarttime, learnermanagementlevelid)
                SELECT
                    tg.programmeenvironmentid, tg.gradeid, var_Friday + (- 4::NUMERIC || ' DAY')::INTERVAL, var_Friday + (- 4::NUMERIC || ' DAY')::INTERVAL, tg.programmemanagementlevelid
                    FROM t$targetgrade AS tg;
                
				CALL internal.ptemporalstatus('dbo.ProgrammeEnvironmentHasTargetGrade', 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);
               
				   UPDATE t$pendingschool AS ps
					SET gradeactivatedcount = tg.gradeactivatedcount
					FROM (SELECT
						tg.programmeenvironmentid, COUNT(*) AS gradeactivatedcount
						FROM t$targetgrade AS tg
						GROUP BY tg.programmeenvironmentid) AS tg
						WHERE ps.activate = 1 AND ps.activegradecount = 0 AND ps.programmeenvironmentid = tg.programmeenvironmentid;
            END IF;
        END IF;
       
        RETURN QUERY
		SELECT 		PS.SchoolID,
        			COALESCE(S.emiscode,'')				EMISCode,
        			COALESCE(S.name,'')					SchoolName,
        			COALESCE(PS.programmename,'')			Programme,
        			COALESCE(PE.programmecode,'')			ProgrammeCode,
        			COALESCE(H.entityname2,'')			Province,
        			COALESCE(H.entityname4,'')			District,
        			COALESCE(H.reportentityname2,'Unknown')	ReportingRegion,
        			PS.ClassCount,
        			PS.ActiveClassCount,
        			PS.LearnerCount,
        			PS.ActiveLearnerCount,
        			CAST(PS.ActiveLearnerCount AS float) / NULLIF(CAST(PS.LearnerCount AS float),0) ActiveLearner,
        			PS.ActiveLearnerActivity,
        			PS.ActiveGradeCount,
        			PS.GradeActivatedCount
        		FROM  t$pendingschool PS
        		JOIN dbo.school S ON PS.SchoolID = S.SchoolID
        		JOIN dbo.programmeEnvironment PE ON PS.ProgrammeEnvironmentID = PE.ProgrammeEnvironmentID
        		LEFT JOIN LATERAL dbo.fGetSchoolHierarchy(S.SchoolID) H ON true
        		WHERE PS.Activate = 1
        		ORDER BY S.Name, PS.ProgrammeName;
        
       
    DROP TABLE IF EXISTS activerule$pactivateschoolreadingeggs;
    DROP TABLE IF EXISTS activatewithtable$pactivateschoolreadingeggs;
    DROP TABLE IF EXISTS t$pendingschool;
    DROP TABLE IF EXISTS t$rawdata;
    DROP TABLE IF EXISTS t$activatepe;
    DROP TABLE IF EXISTS t$targetgrade;
	DROP TABLE IF EXISTS t$learnerdata;
    DROP TABLE IF EXISTS t$gradeData;
    DROP TABLE IF EXISTS t$gradeChecks;
    DROP TABLE IF EXISTS t$schoolCalc;
-- 	 IF var_TranCountOnEntry = 0 THEN
--             COMMIT;
--         END IF;
--         EXCEPTION
--             WHEN OTHERS THEN
--                 IF var_TranCountOnEntry = 0 THEN
--                     ROLLBACK;
--                 END IF;
--                 RAISE;
RAISE NOTICE 'Done';
END;
$BODY$;

ALTER FUNCTION internal.pactivateschoolreadingeggsf(bigint)
    OWNER TO postgres;
