CREATE OR REPLACE FUNCTION internal.pextractmatificregistrationlearnerlist(IN par_hierarchy character varying, 
	IN par_ignoreexportedalready numeric DEFAULT 0, IN par_updateexportlog numeric DEFAULT 1, 
	INOUT p_refcur refcursor DEFAULT NULL::refcursor)
AS 
$BODY$
DECLARE
    var_ProgrammeID INTEGER DEFAULT 8;
    var_HierarchyEntityID INTEGER;
    var_ReportHierarchyEntitID INTEGER;
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;
    */
    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);

    CREATE TEMPORARY TABLE schoollearnertable$pextractmatificregistrationlearnerlist
    (schoollearnerid INTEGER,
        personid INTEGER);
    PERFORM dbo.fgetschoolhierarchy(sl.schoolid);

    INSERT INTO schoollearnertable$pextractmatificregistrationlearnerlist (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) = 1
        CROSS JOIN (SELECT
            *
            FROM dbo.fgetschoolhierarchy$tmptbl) 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 >= '2020-05-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));

    /*
    [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
    SELECT
    		P.FirstName				'Student First Name',
    		P.LastName				'Student Last Name',
    		ISNULL(PP.UserName,'')	'Username',
    		SEP.LearnerPassword		'Password',
    		CASE
    			WHEN SC.SchoolClassID IS NULL THEN 'Unknown Class'
    			WHEN SC.SASAMSClassName IS NOT NULL THEN SC.SASAMSClassName
    			ELSE SC.ClassName
    		END						'Class Name',
    		SLY.GradeID				'Grade',
    		SEP.EMISCode			'School Code',
    		'grade' + G.Code +
    		CAST(('@' + REPLACE(TRIM(CASE
    								WHEN SEP.[Name] LIKE '% PS' THEN LEFT(SEP.[Name], LEN(SEP.[Name])-3)
    								WHEN SEP.[Name] LIKE '% HS' THEN LEFT(SEP.[Name], LEN(SEP.[Name])-3)
    								WHEN SEP.[Name] LIKE '% AS' THEN LEFT(SEP.[Name], LEN(SEP.[Name])-3)
    								WHEN SEP.[Name] LIKE '% CS' THEN LEFT(SEP.[Name], LEN(SEP.[Name])-3)
    								WHEN SEP.[Name] LIKE '% ECD' THEN LEFT(SEP.[Name], LEN(SEP.[Name])-4)
    								ELSE SEP.[Name]
    							END),' ','') + '.co.za') AS varchar(250)) 'Teacher Email Address',
    		''						'Teacher First Name',
    		''						'Teacher Last Name',
    		P.PersonID				'External ID',
    		''						'Additional Teacher Email (Optional)',
    		''						'Additional Teacher First Name (Optional)',
    		''						'Additional Teacher Last Name (Optional)'
    	FROM @SchoolLearnerTable NL
    	JOIN PersonHasProgramme PP ON NL.PersonID = PP.PersonID AND PP.ProgrammeID = @ProgrammeID
    	JOIN Person P ON PP.PersonID = P.PersonID
    	JOIN SchoolLearner SL ON NL.SchoolLearnerID = SL.SchoolLearnerID
    	CROSS APPLY (SELECT TOP 1 * FROM SchoolLearnerYear SLY WHERE SLY.SchoolLearnerID = SL.SchoolLearnerID ORDER BY SLY.[Year] DESC) SLY
    	JOIN vwSchoolEnvironmentProgramme SEP ON SL.SchoolID = SEP.SchoolID AND PP.ProgrammeID = SEP.ProgrammeID
    	LEFT JOIN SchoolClass SC ON SLY.SchoolClassID = SC.SchoolClassID
    	LEFT JOIN Grade G ON SLY.GradeID = G.GradeID
    	ORDER BY
    		'School Code',
    		'Grade',
    		'Class Name',
    		'Student Last Name',
    		'Student First Name'
    */

    IF par_UpdateExportLog = 1 THEN
        INSERT INTO internal.dataexportaudit (item, itemid, programmeid)
        SELECT DISTINCT
            'Person', ns.personid, var_ProgrammeID
            FROM schoollearnertable$pextractmatificregistrationlearnerlist 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 schoollearnertable$pextractmatificregistrationlearnerlist;
    
END;
$BODY$
LANGUAGE plpgsql;