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

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

CREATE OR REPLACE FUNCTION internal.pextractprogrammeregistrationregionclasslistreadingeggsf(
	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, email character varying, trialindicator character, "School code" character varying) 
    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_TrialIndicator VARCHAR(20);
	var_programdetail varchar(100);
	 var_prgramdetaillen int;
	
BEGIN
	DROP TABLE IF EXISTS t$resultset;
	DROP TABLE IF EXISTS t$resultsettemp;
	DROP TABLE IF EXISTS t$resultsettempdetail;
    DROP TABLE IF EXISTS t$export;
	DROP TABLE IF EXISTS t$schoolentitytemp;
    /*
    [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
    */
    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(255)) AS email, CAST (NULL AS CHAR(1)) AS trialindicator, CAST (NULL AS VARCHAR(30)) AS "School code"
            WHERE 1 = 0;
        RETURN;
    END IF;
    CREATE TEMPORARY TABLE t$resultset
    (firstname VARCHAR(100),
        lastname VARCHAR(100),
        email VARCHAR(255),
        trialindicator CHAR(1),
        "School code" VARCHAR(30),
        schoolid INTEGER,
        gradeid SMALLINT,
        itemid BIGINT);
    SELECT
        programmeid, name
        INTO var_ProgrammeID, var_ProgrammeName
        FROM dbo.programme
        WHERE programmeid = 4;
    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 */
    --PERFORM dbo.fgetschoolhierarchy(h.schoolid);
    CREATE TEMPORARY TABLE t$export
    (itemid BIGINT);
    /* SELECT FirstName, LastName, Email, TrialIndicator, [School code], SchoolID FROM #Results ORDER BY [School code], Email, LastName, FirstName; */
    var_TrialIndicator = 'N';
	RAISE NOTICE 'var_ProgrammeName: %',var_ProgrammeName;
	RAISE NOTICE 'var_RegionEntityID: %',var_RegionEntityID;
	RAISE NOTICE 'var_ProgrammeID: %',var_ProgrammeID;
	
	var_programdetail = LOWER(concat('Create: ',var_ProgrammeName));
	var_prgramdetaillen = length(var_programdetail);
		
	CREATE TEMPORARY TABLE t$resultsettempdetail
	AS
	SELECT DISTINCT
				S.Name																		"FirstName",
				p.lastname																	"LastName",
				CONCAT('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'
				),'')																			"Email",
				var_TrialIndicator																"TrialIndicator",
				S.SchoolID																	"SchoolID",
				G.GradeID																	"GradeID",
				CAST(SL.SchoolID AS bigint) * 1000000 + SLY.GradeID 							"ItemID",
				I.AuditTimestamp,
				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',Now())
			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) =  LOWER(concat('Create: ', var_ProgrammeName))
    			AND X.Count > 0;
		
	CREATE TEMPORARY TABLE t$resultsettemp 
	AS
	SELECT I."FirstName",
				I."LastName",
				I."Email",
				I."TrialIndicator",
				I."SchoolID",
				I."GradeID",
				I."ItemID",
				I.AuditTimestamp
		FROM t$resultsettempdetail I
		WHERE I.detail = var_programdetail;
	
	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;	
	
    INSERT INTO t$resultset
    	(FirstName,LastName,Email,TrialIndicator,"School code",SchoolID,GradeID,ItemID)
		SELECT DISTINCT 
			S."FirstName",
    		S."LastName",
    		S."Email",
    		S."TrialIndicator",
     		SEP.ProgrammeCode AS "School code",
    		S."SchoolID",
    		S."GradeID",
    		S."ItemID"
		FROM  t$resultsettemp S
    	JOIN dbo.vwSchoolEnvironmentProgramme SEP ON S."SchoolID" = SEP.SchoolID AND SEP.ProgrammeID = var_ProgrammeID
    	INNER JOIN t$schoolentitytemp  H ON H.schoolid = S."SchoolID"
    	INNER JOIN LATERAL (SELECT COUNT(*) Count FROM internal.DataExportAudit E WHERE CAST(S."SchoolID" AS bigint) * 1000000 + S."GradeID" = E.ItemID AND E.Item = 'School Grade' AND E.ProgrammeID = var_ProgrammeID AND Date_PART('Year',E.AuditTimestamp) = Date_PART('Year',S.AuditTimestamp)) E ON TRUE
    		AND ((par_ignoreexportedalready = 1) OR (par_ignoreexportedalready = 0 AND E.Count = 0))
    		AND H.ReportEntityID2 = var_RegionEntityID;
	RAISE NOTICE 'Done with t$resultset';
	
--     INSERT INTO t$resultset
--     	(FirstName,LastName,Email,TrialIndicator,"School code",SchoolID,GradeID,ItemID)
--     	SELECT DISTINCT
--     		S.Name																		"FirstName",
--     		CONCAT('Grade ', G.Code)													"LastName",
--     		CONCAT('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'
--     		),'')																			"Email",
--     		var_TrialIndicator																"TrialIndicator",
--     		SEP.ProgrammeCode																"School code",
--     		SEP.SchoolID																	"SchoolID",
--     		G.GradeID																	"GradeID",
--     		CAST(SL.SchoolID AS bigint) * 1000000 + SLY.GradeID 							"ItemID"
--     	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',Now())--YEAR(GETDATE())
--     	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 CAST(SL.SchoolID AS bigint) * 1000000 + SLY.GradeID = E.ItemID AND E.Item = 'School Grade' AND E.ProgrammeID = @ProgrammeID AND Date_PART('Year',E.AuditTimestamp) = Date_PART('Year',I.AuditTimestamp)) E ON TRUE
--     	WHERE I.DataImportID = par_dataimportid
--     		AND I.Item = 'PersonHasProgramme'
--     		AND I.Action = 'C'
--     		AND I.Detail = CONCAT('Create: ', var_ProgrammeName)
--     		AND ((par_ignoreexportedalready = 1) OR (par_ignoreexportedalready = 0 AND E.Count = 0))
--     		AND H.ReportEntityID2 = var_RegionEntityID
--     		AND X.Count > 0;
   
    INSERT INTO t$export (itemid)
    SELECT
        itemid
        FROM t$resultset;
	IF par_UpdateExportLog = 1 AND par_IgnoreExportedAlready = 0 THEN
        INSERT INTO internal.dataexportaudit (item, itemid, programmeid)
        SELECT
            'School Grade', e.itemid, var_ProgrammeID
            FROM t$export AS e;
        /* LEFT JOIN internal.DataExportAudit A ON E.ItemID = A.ItemID AND A.Item = 'School Grade' AND A.ProgrammeID = @ProgrammeID AND E.AuditTimestamp >= I.AuditTimestamp */
        /* WHERE A.DataExportAuditID IS NULL */
    END IF;
    --OPEN p_refcur_2 FOR
	Return Query
    SELECT
        t$resultset.firstname, t$resultset.lastname, t$resultset.email, t$resultset.trialindicator, t$resultset."School code"
        FROM t$resultset
        ORDER BY "School code" NULLS FIRST, 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$resultsettempdetail;
    DROP TABLE IF EXISTS t$export;
	DROP TABLE IF EXISTS t$schoolentitytemp;
   
END;
$BODY$;

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