-- FUNCTION: internal.pextractprogrammeregistrationregionlearnerlistreadingeggsf(integer, character varying, character varying, numeric, numeric)

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

CREATE OR REPLACE FUNCTION internal.pextractprogrammeregistrationregionlearnerlistreadingeggsf(
	par_dataimportid integer,
	par_hierarchy character varying,
	par_region character varying,
	par_ignoreexportedalready numeric DEFAULT 0,
	par_updateexportlog numeric DEFAULT 1)
    RETURNS TABLE(firstname character varying, lastname character varying, grade character varying, "Teacher email" character varying, "School code" character varying, studentid integer, schoolname character varying, previousloginlastactivitydate character varying, previousloginlastrelesson integer, previousloginlastrexlesson integer, previousloginlastfplesson integer) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$

DECLARE
    var_ProgrammeID SMALLINT;
    var_ProgrammeName VARCHAR(100);
    var_HierarchyEntityID INTEGER;
    var_RegionHierarchyEntityID INTEGER;
    var_RegionEntityID INTEGER;
	var_Grade VARCHAR(100);
	var_programdetail varchar(100);
	 var_prgramdetaillen int;
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
    */
	
	DROP TABLE IF EXISTS t$resultset;
	DROP TABLE IF EXISTS t$resultsettemp;
	DROP TABLE IF EXISTS t$schoolentitytemp;
	DROP TABLE IF EXISTS t$resultsettemp1;
	DROP TABLE IF EXISTS t$resultsettempdetail;
	DROP TABLE IF EXISTS t$export;
	
    IF par_DataImportID IS NULL THEN
        RETURN QUERY
        SELECT
            CAST (NULL AS VARCHAR(100)) AS firstname, CAST (NULL AS VARCHAR(100)) AS lastname, CAST (NULL AS VARCHAR(10)) AS grade, CAST (NULL AS VARCHAR(255)) AS "Teacher email", CAST (NULL AS VARCHAR(30)) AS "School code", CAST (NULL AS INTEGER) AS studentid, CAST (NULL AS VARCHAR(200)) AS schoolname, CAST (NULL AS VARCHAR(10)) AS previousloginlastactivitydate, CAST (NULL AS INTEGER) AS previousloginlastrelesson, CAST (NULL AS INTEGER) AS previousloginlastrexlesson, CAST (NULL AS INTEGER) AS previousloginlastfplesson
            WHERE 1 = 0;
        
    END IF;
    CREATE TEMPORARY TABLE t$resultsettemp
    (firstname VARCHAR(100) NOT NULL,
        lastname VARCHAR(100) NOT NULL,
        grade VARCHAR(20) NOT NULL,
        "Teacher email" VARCHAR(255) NOT NULL,
        "School code" VARCHAR(30) NOT NULL,
        studentid INTEGER NOT NULL,
        personid INTEGER NOT NULL,
        /* */
        schoolname VARCHAR(200) NULL,
        previousloginlastactivitydate VARCHAR(10) NULL,
        previousloginlastrelesson INTEGER NULL,
        previousloginlastrexlesson INTEGER NULL,
        previousloginlastfplesson INTEGER NULL,
		audittimestamp timestamp without time zone	
	);
	 CREATE TEMPORARY TABLE t$resultset
    (firstname VARCHAR(100) NOT NULL,
        lastname VARCHAR(100) NOT NULL,
        grade VARCHAR(20) NOT NULL,
        "Teacher email" VARCHAR(255) NOT NULL,
        "School code" VARCHAR(30) NOT NULL,
        studentid INTEGER NOT NULL,
        personid INTEGER NOT NULL,
        /* */
        schoolname VARCHAR(200) NULL,
        previousloginlastactivitydate VARCHAR(10) NULL,
        previousloginlastrelesson INTEGER NULL,
        previousloginlastrexlesson INTEGER NULL,
        previousloginlastfplesson INTEGER NULL
	);
		
    SELECT
        programmeid, name
        INTO var_ProgrammeID, var_ProgrammeName
        FROM dbo.programme
        WHERE programmeid = 4;
		
RAISE NOTICE 'par_Hierarchy: %', par_Hierarchy;
    SELECT
        h.entityid, dbo.fgetrelatedhierarchyrootentityid(h.entityid, 'Click Foundation Report Region%')
        INTO var_HierarchyEntityID, var_RegionHierarchyEntityID
        FROM dbo.entityhierarchy AS h
        WHERE LOWER(h.entityname) = LOWER(par_Hierarchy) AND h.entitytypeid = 1 AND h.entitylevel = 1;
		
    SELECT
        h.entityid2
        INTO var_RegionEntityID
        FROM dbo.entityhierarchy AS h
        WHERE h.entityid1 = var_RegionHierarchyEntityID AND LOWER(h.entityname2) = LOWER(par_Region);
    /* SELECT @HierarchyEntityID, @RegionHierarchyEntityID, @RegionEntityID */
   RAISE NOTICE 'CREATE TEMPORARY TABLE t$export';
    CREATE TEMPORARY TABLE t$export
    (itemid BIGINT);
    /* SELECT FirstName, LastName, Grade, [Teacher email], [School code], StudentID, PersonID, SchoolID FROM #Result ORDER BY [School code], Grade, [Teacher email], LastName, FirstName; */
    RAISE NOTICE 'var_ProgrammeID: %',var_ProgrammeID;
	RAISE NOTICE 'var_ProgrammeName: %',var_ProgrammeName;
	RAISE NOTICE 'var_RegionEntityID: %',var_RegionEntityID;
	/* Insert data into resultsettemp */
	
CREATE TEMP TABLE t$schoolentitytemp AS  		
SELECT DISTINCT
        eh.entityid1,
		eh.schoolid, 		 
		rh.entityid2 AS reportentityid2
        FROM dbo.vwentityhierarchyname AS eh
        JOIN dbo.entity AS e1
            ON eh.entityid1 = e1.entityid AND e1.entitytypeid = 1
        JOIN (SELECT DISTINCT
            hd.relatedrootentityid,hd.rootentityid
            FROM dbo.hierarchydefinition AS hd
            JOIN dbo.entity AS e
                ON hd.rootentityid = e.entityid
            WHERE   hd.entitytypeid = 7 AND LOWER(e.name) SIMILAR TO LOWER('%Report Regions%')) AS rx ON rx.relatedrootentityid = e1.entityid
        LEFT OUTER JOIN dbo.vwentityhierarchyname AS rh
            ON rx.rootentityid = rh.entityid1 AND rh.schoolid = eh.schoolid     
        ORDER BY eh.entityid1 DESC NULLS FIRST;	
				
		var_programdetail = LOWER(concat('Create: ',var_ProgrammeName));
		var_prgramdetaillen = length(var_programdetail);
		
		CREATE TEMPORARY TABLE t$resultsettempdetail	
		AS
		SELECT 
    		P.FirstName,
    		P.LastName,
    		CASE WHEN SLY.GradeID = 0 THEN 0 ELSE   SLY.GradeID  END AS grade,
    		coalesce(('grade' || G.Code ||
    					'@' || REPLACE(TRIM(CASE
    						WHEN S.Name LIKE '% PS' THEN LEFT(S.Name, length(S.Name)-3)
    						WHEN S.Name LIKE '% HS' THEN LEFT(S.Name, length(S.Name)-3)
    						WHEN S.Name LIKE '% AS' THEN LEFT(S.Name, length(S.Name)-3)
    						WHEN S.Name LIKE '% CS' THEN LEFT(S.Name, length(S.Name)-3)
    						WHEN S.Name LIKE '% ECD' THEN LEFT(S.Name, length(S.Name)-4)
    						ELSE S.Name
    					END),' ','') || '.co.za'
    		),'')											AS								"Teacher email",
    		P.PersonID										AS								"StudentID",
    		P.PersonID,
    		S.Name,
			I.audittimestamp,
			s.schoolid,
			LOWER(SUBSTRING(I.Detail, 1, var_prgramdetaillen)) detail
    	FROM internal.DataImportAudit I
    	JOIN dbo.SchoolLearner SL ON I.ItemID = SL.PersonID AND SL.EndDate = '9999-12-31'
    	JOIN dbo.SchoolLearnerYear SLY ON SL.SchoolLearnerID = SLY.SchoolLearnerID AND SLY.Year = date_part('year', CURRENT_DATE)
    	INNER JOIN LATERAL (SELECT COUNT(*) Count FROM dbo.SourceLearner X WHERE SLY.SchoolLearnerID = X.MappedSchoolLearnerID AND SLY.Year = X.Year AND X.DateLeft IS NULL) X ON TRUE
    	JOIN dbo.Person P ON I.ItemID = P.PersonID
    	JOIN dbo.Grade G ON SLY.GradeID = G.GradeID
    	JOIN dbo.School S ON SL.SchoolID = S.SchoolID
    	WHERE I.DataImportID = par_dataimportid
    		AND I.Item = 'PersonHasProgramme'
    		AND I.Action = 'C'
-- 			AND LOWER(I.Detail) like  LOWER(concat('Create: ', var_ProgrammeName,'%'))
    		AND X.Count > 0;
			
	CREATE TEMPORARY TABLE t$resultsettemp1	
		AS
		SELECT I.FirstName,
    		I.LastName,
    		I.grade,
			I."Teacher email",
    		I."StudentID",
    		I.PersonID,
    		I.Name,
			I.audittimestamp,
			I.schoolid
		FROM t$resultsettempdetail I
		WHERE I.detail = var_programdetail;
		
    INSERT INTO t$resultsettemp
    	(FirstName,LastName,Grade,"Teacher email","School code",StudentID,PersonID,
    	SchoolName, PreviousLoginLastActivityDate, PreviousLoginLastRELesson, PreviousLoginLastREXLesson, PreviousLoginLastFPLesson,audittimestamp)
		SELECT I.FirstName,I.LastName,I.Grade,I."Teacher email",
				SEP.ProgrammeCode AS "School code",I."StudentID",I.PersonID,
				I.name,
				to_char(PPA.LastMovementDate,'yyyy-MM-dd'),
				LDRE.RE_Lesson,
				LDRE.REX_Lesson,
				LDRE.FP_lesson_count,
				I.audittimestamp
			FROM t$resultsettemp1 I    	
			JOIN dbo.vwSchoolEnvironmentProgramme SEP ON I.SchoolID = SEP.SchoolID AND SEP.ProgrammeID = var_ProgrammeID
			JOIN t$schoolentitytemp H ON H.schoolid = I.schoolid
			LEFT JOIN dbo.PersonProgrammeActivity PPA ON I.PersonID = PPA.PersonID AND PPA.ProgrammeID = var_ProgrammeID
			LEFT JOIN dbo.LearnerDataReadingEggs LDRE ON PPA.PersonID = LDRE.PersonID AND LDRE.Date = PPA.LastMovementDate
			WHERE H.ReportEntityID2 = var_RegionEntityID;
--     	SELECT DISTINCT
--     		P.FirstName,
--     		P.LastName,
--     		CASE WHEN SLY.GradeID = 0 THEN 0 ELSE   SLY.GradeID  END AS grade,
--     		coalesce(('grade' || G.Code ||
--     					'@' || REPLACE(TRIM(CASE
--     						WHEN S.Name LIKE '% PS' THEN LEFT(S.Name, length(S.Name)-3)
--     						WHEN S.Name LIKE '% HS' THEN LEFT(S.Name, length(S.Name)-3)
--     						WHEN S.Name LIKE '% AS' THEN LEFT(S.Name, length(S.Name)-3)
--     						WHEN S.Name LIKE '% CS' THEN LEFT(S.Name, length(S.Name)-3)
--     						WHEN S.Name LIKE '% ECD' THEN LEFT(S.Name, length(S.Name)-4)
--     						ELSE S.Name
--     					END),' ','') || '.co.za'
--     		),'')											AS								"Teacher email",
--     		SEP.ProgrammeCode								AS								"School code",
--     		P.PersonID										AS								"StudentID",
--     		P.PersonID,
--     		--
--     		S.Name,
--     		to_char(PPA.LastMovementDate,'yyyy-MM-dd'),
--     		LDRE.RE_Lesson,
--     		LDRE.REX_Lesson,
--     		LDRE.FP_lesson_count,
-- 			I.audittimestamp
--     	FROM internal.DataImportAudit I
--     	JOIN dbo.SchoolLearner SL ON I.ItemID = SL.PersonID AND SL.EndDate = '9999-12-31'
--     	JOIN dbo.SchoolLearnerYear SLY ON SL.SchoolLearnerID = SLY.SchoolLearnerID AND SLY.Year = date_part('year', CURRENT_DATE)
--     	INNER JOIN LATERAL (SELECT COUNT(*) Count FROM dbo.SourceLearner X WHERE SLY.SchoolLearnerID = X.MappedSchoolLearnerID AND SLY.Year = X.Year AND X.DateLeft IS NULL) X ON TRUE
--     	JOIN dbo.Person P ON I.ItemID = P.PersonID
--     	JOIN dbo.Grade G ON SLY.GradeID = G.GradeID
--     	JOIN dbo.School S ON SL.SchoolID = S.SchoolID
--     	JOIN dbo.vwSchoolEnvironmentProgramme SEP ON S.SchoolID = SEP.SchoolID AND SEP.ProgrammeID = var_ProgrammeID
--     	INNER JOIN LATERAL dbo.fGetSchoolHierarchy(S.SchoolID) H ON TRUE
-- --     	INNER JOIN LATERAL (SELECT COUNT(*) Count FROM internal.DataExportAudit E WHERE I.ItemID = E.ItemID AND E.Item = 'Person' AND E.ProgrammeID = var_ProgrammeID AND E.AuditTimestamp >= I.AuditTimestamp) E ON TRUE
--     	LEFT JOIN dbo.PersonProgrammeActivity PPA ON P.PersonID = PPA.PersonID AND PPA.ProgrammeID = var_ProgrammeID
--     	LEFT JOIN dbo.LearnerDataReadingEggs LDRE ON PPA.PersonID = LDRE.PersonID AND LDRE.Date = PPA.LastMovementDate
--     	WHERE I.DataImportID = par_dataimportid
--     		AND I.Item = 'PersonHasProgramme'
--     		AND I.Action = 'C'
-- 			AND LOWER(I.Detail) like  LOWER(concat('Create: ', var_ProgrammeName,'%'))
-- --     		AND I.Detail = 'Create: ' || var_ProgrammeName
--     		--AND I.Detail = 'Restart: ' + @ProgrammeName
-- --     		AND ((par_ignoreexportedalready = 1) OR (par_ignoreexportedalready = 0 AND E.Count = 0))
--     		AND H.ReportEntityID2 = var_RegionEntityID
--     		AND X.Count > 0;
   
   /* Populate t$resultset */
   INSERT INTO t$resultset
    	(FirstName,LastName,Grade,"Teacher email","School code",StudentID,PersonID,
    	SchoolName, PreviousLoginLastActivityDate, PreviousLoginLastRELesson, PreviousLoginLastREXLesson, PreviousLoginLastFPLesson)
   SELECT I.FirstName,I.LastName,I.Grade,I."Teacher email",I."School code",I.StudentID,I.PersonID,
    	I.SchoolName,I.PreviousLoginLastActivityDate, I.PreviousLoginLastRELesson, I.PreviousLoginLastREXLesson, I.PreviousLoginLastFPLesson FROM t$resultsettemp I
			INNER JOIN LATERAL (SELECT COUNT(*) Count FROM internal.DataExportAudit E WHERE I.PersonID = E.ItemID AND E.Item = 'Person' AND E.ProgrammeID = 4 AND E.AuditTimestamp >= I.AuditTimestamp
							   ) E ON TRUE
			where ((par_ignoreexportedalready = 1) OR (par_ignoreexportedalready = 0 AND E.Count = 0));
   
    INSERT INTO t$export (itemid)
    SELECT
        personid
        FROM t$resultset;
		
		IF par_UpdateExportLog = 1 AND par_IgnoreExportedAlready = 0 THEN
        INSERT INTO internal.dataexportaudit (item, itemid, programmeid)
        SELECT
            'Person', e.itemid, var_ProgrammeID
            FROM t$export AS e;
        /* LEFT JOIN internal.DataExportAudit A ON E.ItemID = A.ItemID AND A.Item = 'Person' AND A.ProgrammeID = @ProgrammeID */
        /* WHERE A.DataExportAuditID IS NULL */
    END IF;
	
    RETURN QUERY
    SELECT DISTINCT
        r.firstname, r.lastname, r.grade, r."Teacher email", r."School code", r.studentid, r.schoolname, r.previousloginlastactivitydate, r.previousloginlastrelesson, r.previousloginlastrexlesson, r.previousloginlastfplesson
        FROM t$resultset AS r
        ORDER BY "School code" NULLS FIRST, grade NULLS FIRST, "Teacher email" NULLS FIRST, lastname NULLS FIRST, firstname NULLS FIRST;

    
 
    
    DROP TABLE IF EXISTS t$resultset;
	DROP TABLE IF EXISTS t$resultsettemp;
	DROP TABLE IF EXISTS t$schoolentitytemp;
	DROP TABLE IF EXISTS t$resultsettemp1;
	DROP TABLE IF EXISTS t$export;

END;
$BODY$;

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