-- FUNCTION: internal.pextractreflectivelearningregistrationlearnerlistf(character varying, numeric, numeric)

-- DROP FUNCTION IF EXISTS internal.pextractreflectivelearningregistrationlearnerlistf(character varying, numeric, numeric);

CREATE OR REPLACE FUNCTION internal.pextractreflectivelearningregistrationlearnerlistf(
	par_hierarchy character varying,
	par_ignoreexportedalready numeric DEFAULT 0,
	par_updateexportlog numeric DEFAULT 1)
    RETURNS TABLE(school character varying, grade character varying, class character varying, username character varying, password character varying, uniquelearnerid integer) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$

DECLARE
    var_ProgrammeID INTEGER DEFAULT 9;
    var_HierarchyEntityID INTEGER;
    var_ReportHierarchyEntitID INTEGER;
BEGIN

	DROP TABLE IF EXISTS t$schoollearnertable;
	DROP TABLE IF EXISTS t$schoollearnertemp1;
	DROP TABLE IF EXISTS t$schoolentitytemp;
	DROP TABLE IF EXISTS t$schoollearnertemp;
	
    /*
    [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
        h.entityid, dbo.fgetrelatedhierarchyrootentityid(h.entityid, 'Click Foundation Report Regions%')
        INTO var_HierarchyEntityID, var_ReportHierarchyEntitID
        FROM dbo.entityhierarchy AS h
        WHERE h.entitytypeid = 1 AND h.entitylevel = 1 AND LOWER(h.entityname) = LOWER(par_Hierarchy);
	
	RAISE NOTICE 'var_HierarchyEntityID: % , var_ReportHierarchyEntitID: %',var_HierarchyEntityID,var_ReportHierarchyEntitID;
		
    CREATE TEMPORARY TABLE t$schoollearnertable
    (schoollearnerid INTEGER,
        personid INTEGER);
		
	CREATE TEMPORARY TABLE t$schoollearnertemp1
    (
		schoollearnerid INTEGER,
        personid INTEGER,
		schoolid INTEGER,
		programmeid INTEGER
	);	
	
    --PERFORM dbo.fgetschoolhierarchy(sl.schoolid);
	RAISE NOTICE 'INSERT INTO t$schoollearnertable';
	
--     INSERT INTO t$schoollearnertable (schoollearnerid, personid)
--     SELECT DISTINCT
--         schoollearnerid, sl.personid
--         FROM dbo.personhasprogramme AS pp
--         JOIN dbo.schoollearner AS sl
--             ON pp.personid = sl.personid AND sl.enddate = '9999-12-31'
--         JOIN dbo.vwschoolenvironmentprogramme AS sep
--             ON sl.schoolid = sep.schoolid AND sep.programmeid = var_ProgrammeID AND sep.implementationstatusid IN (4, 13) AND aws_sqlserver_ext.ISNUMERIC(sep.emiscode)
--         CROSS JOIN Lateral (SELECT
--             *
--             FROM dbo.fgetschoolhierarchy(sl.schoolid)) AS h
--         LEFT OUTER JOIN internal.dataexportaudit AS dea
--             ON LOWER(dea.item) = LOWER('Person') AND pp.programmeid = dea.programmeid AND pp.personid = dea.itemid
--         WHERE pp.programmeid = var_ProgrammeID AND pp.registerdate >= '2021-01-01' AND pp.username IS NOT NULL 
-- 		AND var_HierarchyEntityID = h.entityid1 
-- 		AND ((par_IgnoreExportedAlready = 1) OR (par_IgnoreExportedAlready = 0 AND dea.dataexportauditid IS NULL));

    INSERT INTO t$schoollearnertemp1 (schoollearnerid, personid,schoolid,programmeid)
    SELECT 
        schoollearnerid, sl.personid,sl.schoolid,pp.programmeid
        FROM dbo.personhasprogramme AS pp
        JOIN dbo.schoollearner AS sl
            ON pp.personid = sl.personid AND sl.enddate = '9999-12-31'
        JOIN dbo.vwschoolenvironmentprogramme AS sep
            ON sl.schoolid = sep.schoolid AND sep.programmeid = var_ProgrammeID AND sep.implementationstatusid IN (4, 13) AND aws_sqlserver_ext.ISNUMERIC(sep.emiscode)        
        WHERE pp.programmeid = var_ProgrammeID AND pp.registerdate >= '2021-01-01' AND pp.username IS NOT NULL;

	RAISE NOTICE 'INSERT INTO t$schoollearnertemp1 Done';	
	
	CREATE TEMP TABLE t$schoolentitytemp
	as
	SELECT DISTINCT
	eh.entityid1, 		
	eh.schoolid	
	FROM dbo.vwentityhierarchyname AS eh
	JOIN dbo.entity AS e1 ON eh.entityid1 = e1.entityid AND e1.entitytypeid = 1              
	ORDER BY eh.entityid1 DESC NULLS FIRST;
	
	RAISE NOTICE 'INSERT INTO t$schoollearnertemp';
	CREATE TEMP TABLE  t$schoollearnertemp
	AS
	SELECT 
				pp.schoollearnerid, pp.personid,dea.dataexportauditid
			FROM t$schoollearnertemp1 AS pp
			JOIN t$schoolentitytemp AS h ON h.schoolid = pp.schoolid
			LEFT JOIN internal.dataexportaudit AS dea
				ON LOWER(dea.item) = LOWER('Person') AND pp.programmeid = dea.programmeid AND pp.personid = dea.itemid
			WHERE 1 = h.entityid1 ;
			
	RAISE NOTICE 'INSERT INTO t$schoollearnertable';
	
	INSERT INTO t$schoollearnertable (schoollearnerid, personid)
	SELECT 
			pp.schoollearnerid, pp.personid
        FROM t$schoollearnertemp AS pp        
		WHERE ((par_IgnoreExportedAlready = 1) OR (par_IgnoreExportedAlready = 0 AND pp.dataexportauditid IS NULL));
	
	RAISE NOTICE 'INSERT INTO t$schoollearnertable Done';
	

	
		RAISE NOTICE 'Done with INSERT';
		
		RAISE NOTICE 'Start return query';
   RETURN QUERY
    SELECT DISTINCT ON (nl.personid)
        sep.name AS "School",SC.GradeID::character varying AS "Grade", sc.classname AS "Class", pp.username AS "Username", sep.learnerpassword AS "Password", nl.personid AS "UniqueLearnerID"
        FROM t$schoollearnertable AS nl
        JOIN dbo.personhasprogramme AS pp
            ON nl.personid = pp.personid AND pp.programmeid = var_ProgrammeID
        JOIN dbo.schoollearner AS sl
            ON nl.schoollearnerid = sl.schoollearnerid
--         CROSS JOIN Lateral (SELECT
--             *
--             FROM dbo.schoollearneryear AS sly
--             WHERE sly.schoollearnerid = sl.schoollearnerid
--             ORDER BY sly.year DESC NULLS FIRST
--             LIMIT 1) AS sly
		JOIN (
			SELECT DISTINCT on (sly.schoollearnerid) *  
			FROM dbo.schoollearneryear AS sly
			ORDER BY sly.schoollearnerid,sly.year DESC NULLS FIRST
		) AS sly on sly.schoollearnerid = sl.schoollearnerid
        JOIN dbo.vwschoolenvironmentprogramme AS sep
            ON sl.schoolid = sep.schoolid AND pp.programmeid = sep.programmeid
        JOIN dbo.schoolclass AS sc
            ON sly.schoolclassid = sc.schoolclassid
        ORDER BY nl.personid,sep.name NULLS FIRST, sly.gradeid NULLS FIRST, sc.classname NULLS FIRST, pp.username NULLS FIRST;
		
	RAISE NOTICE 'End return query';
	
    IF par_UpdateExportLog = 1 THEN
        INSERT INTO internal.dataexportaudit (item, itemid, programmeid)
        SELECT DISTINCT
            'Person', ns.personid, var_ProgrammeID
            FROM t$schoollearnertable AS ns
            LEFT OUTER JOIN internal.dataexportaudit AS dea
                ON LOWER(dea.item) = LOWER('Person') AND dea.programmeid = var_ProgrammeID AND dea.itemid = ns.personid
            WHERE dea.dataexportauditid IS NULL;
    END IF;
	
--     DROP TABLE IF EXISTS t$schoollearnertable;
-- 	DROP TABLE IF EXISTS t$schoollearnertemp1;
END;
$BODY$;

ALTER FUNCTION internal.pextractreflectivelearningregistrationlearnerlistf(character varying, numeric, numeric)
    OWNER TO postgres;
