-- FUNCTION: internal.pgetprogrammeregistrationregionlistf(bigint, character varying, character varying, numeric, numeric)

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

CREATE OR REPLACE FUNCTION internal.pgetprogrammeregistrationregionlistf(
	par_dataimportid bigint,
	par_programme character varying,
	par_hierarchy character varying,
	par_ignoreexportedalreadylearners numeric DEFAULT 0,
	par_ignoreexportedalreadyclasses numeric DEFAULT 0)
    RETURNS TABLE(hierachyentityid integer, "Hierarchy" character varying, regionentityid integer, region character varying, learnercount numeric, classcount numeric, extractdate text, extractdatetime text, regionfilename text, programmename character varying, emailto text, emailcc text) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
DECLARE
    var_ProgrammeID SMALLINT;
    var_ProgrammeName VARCHAR(100);
	 var_RowCount BIGINT;
	 var_programdetail varchar(100);
	 var_prgramdetaillen int;
BEGIN

	DROP TABLE IF EXISTS t$learners;   
    DROP TABLE IF EXISTS t$regions;
	DROP TABLE IF EXISTS t$learnerstemp;
	DROP TABLE IF EXISTS t$learnerstemp1;
	DROP TABLE IF EXISTS t$learnerstemp2;
	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
    */
    SELECT
        programmeid, name
        INTO var_ProgrammeID, var_ProgrammeName
        FROM dbo.programme
        WHERE CAST (programmeid AS VARCHAR(100)) = par_Programme OR LOWER(code) = LOWER(par_Programme) OR LOWER(name) = LOWER(par_Programme);
    --PERFORM dbo.fgetschoolhierarchy(s.schoolid);
	
 RAISE NOTICE 'var_ProgrammeID: % , var_ProgrammeName: %',var_ProgrammeID, var_ProgrammeName;
 RAISE NOTICE 'varibles used: %,%,%,%',var_ProgrammeID,par_dataimportid,par_programme,par_hierarchy;

-- CREATE TEMP TABLE t$learners AS  
--     SELECT DISTINCT SL.SchoolLearnerID, SLY.SchoolLearnerYearID, SLY.SchoolClassID, SLY.GradeID, SL.PersonID, SL.SchoolID, H.EntityID1, E.Count ExportedCount
--     	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: ', par_programme,'%'))
--     		AND ((par_hierarchy IS NULL) OR (par_hierarchy IS NOT NULL AND H.EntityName1 = par_hierarchy))
--     		AND X.Count > 0;
		var_programdetail = LOWER(concat('Create: ',par_programme));
		var_prgramdetaillen = length(var_programdetail);
		
		CREATE TEMP TABLE t$learnerstemp1 AS  
		SELECT DISTINCT SL.SchoolLearnerID, SLY.SchoolLearnerYearID, SLY.SchoolClassID, SLY.GradeID, SL.PersonID, SL.SchoolID,I.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', 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: ',par_programme,'%'))    		
				AND X.Count > 0;

		CREATE TEMP TABLE t$learnerstemp AS 
		SELECT DISTINCT SL.SchoolLearnerID, SL.SchoolLearnerYearID, SL.SchoolClassID, SL.GradeID, SL.PersonID, SL.SchoolID,SL.itemid,SL.AuditTimestamp
		FROM t$learnerstemp1 AS SL
		WHERE SL.detail = var_programdetail;
		
		CREATE TEMP TABLE t$schoolentitytemp AS  		
		SELECT DISTINCT
				eh.entityid1, 
				eh.entityname1, 		
				eh.schoolid		
				FROM dbo.vwentityhierarchyname AS eh
				JOIN dbo.entity AS e1
					ON eh.entityid1 = e1.entityid AND e1.entitytypeid = 1
				JOIN  (SELECT DISTINCT
					hd.rootentityid,hd.relatedrootentityid
					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;		
		
			CREATE TEMP TABLE t$learnerstemp2 AS 			
			SELECT DISTINCT SL.SchoolLearnerID, SL.SchoolLearnerYearID, SL.SchoolClassID, SL.GradeID, SL.PersonID, SL.SchoolID , H.EntityID1,SL.itemid,SL.AuditTimestamp
			FROM t$learnerstemp SL
			JOIN dbo.vwSchoolEnvironmentProgramme SEP ON SL.SchoolID = SEP.SchoolID AND SEP.ProgrammeID = var_ProgrammeID
			JOIN t$schoolentitytemp H on H.schoolid = SL.schoolid 
			LEFT JOIN dbo.PersonProgrammeActivity PPA ON SL.PersonID = PPA.PersonID AND PPA.ProgrammeID = var_ProgrammeID
			LEFT JOIN dbo.LearnerDataReadingEggs LDRE ON PPA.PersonID = LDRE.PersonID AND LDRE.Date = PPA.LastMovementDate
			WHERE ((par_hierarchy IS NULL) OR (par_hierarchy IS NOT NULL AND H.EntityName1 = par_hierarchy));

			CREATE TEMP TABLE t$learners AS  
			SELECT DISTINCT SL.SchoolLearnerID, SL.SchoolLearnerYearID, SL.SchoolClassID, SL.GradeID, SL.PersonID, SL.SchoolID , SL.EntityID1 ,COALESCE(E.Count,0) ExportedCount
			FROM t$learnerstemp2 SL
			LEFT JOIN (SELECT DISTINCT E.ItemID ,E.AuditTimestamp, COUNT (*) Count
					FROM internal.DataExportAudit E 
					WHERE E.Item = 'Person' 
					AND E.ProgrammeID = var_ProgrammeID
					GROUP BY E.ItemID ,E.AuditTimestamp
					ORDER BY E.AuditTimestamp DESC					
				   ) E ON SL.itemid = E.itemid AND E.AuditTimestamp = SL.AuditTimestamp;

	
    GET DIAGNOSTICS var_RowCount = ROW_COUNT;
	RAISE NOTICE 'Row count t$learners: %',var_RowCount;
	
	
    /* FROM internal.DataImportAudit I */
    /* JOIN SchoolLearner SL ON I.ItemID = SL.PersonID AND SL.EndDate = '9999-12-31' */
    /* JOIN SchoolLearnerYear SLY ON SL.SchoolLearnerID = SLY.SchoolLearnerID AND SLY.[Year] = YEAR(GETDATE()) */
    /* CROSS APPLY (SELECT TOP 1 H.* FROM EntityHierarchy H JOIN Entity E ON H.EntityID1 = E.EntityID AND E.EntityTypeID = 1 WHERE H.SchoolID = SL.SchoolID ORDER BY H.EntityID1 DESC) H */
    /* WHERE I.[Action] = 'C' */
    /* AND I.DataImportID = @DataImportID */
    /* AND I.Item = 'PersonHasProgramme' */
    /* AND I.Detail = 'Create: ' + @ProgrammeName */
    /* AND ((@Hierarchy IS NULL) OR (@Hierarchy IS NOT NULL AND H.EntityName1 = @Hierarchy)) */
    --RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %', '50000', 0, 0, '#Learners: %d', "var_@ROWCOUNT" USING ERRCODE = '50000';
	
    CREATE TEMPORARY TABLE t$regions
    AS
    SELECT
        s.*, h.entityname1 AS hierarchy, r.entityid2 AS regionentityid, COALESCE(r.entityname2, 'Unknown') AS region
        FROM (SELECT
            schoolid, entityid1, dbo.fgetrelatedhierarchyrootentityid(entityid1, 'Click Foundation Report Regions%') AS reportentityid1
            FROM (SELECT DISTINCT
                schoolid, entityid1
                FROM t$learners AS l) AS s) AS s
        JOIN dbo.entityhierarchy AS h
            ON s.entityid1 = h.entityid1 AND s.schoolid = h.schoolid
        LEFT OUTER JOIN dbo.entityhierarchy AS r
            ON s.reportentityid1 = r.entityid1 AND s.schoolid = r.schoolid;
   -- RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %', '50000', 0, 0, '#Regions: %d', "var_@ROWCOUNT" USING ERRCODE = '50000';

	RETURN QUERY 
    WITH results(HierarchyEntityID, Hierarchy, RegionEntityID, Region, LearnerCount, ClassCount) AS
    	(
    		SELECT
    			R.EntityID1							HierarchyEntityID,
    			R.Hierarchy							Hierarchy,
    			R.RegionEntityID					RegionEntityID,
    			R.Region							Region,
    			SUM(L.Count)						LearnerCount,
    			SUM(C.Count)						ClassCount
    		FROM t$Regions R
    		LEFT JOIN LATERAL
    		(
    			SELECT COUNT(*) Count
    			FROM t$learners L
    			WHERE L.SchoolID = R.SchoolID
    				AND ((par_ignoreexportedalreadylearners = 0 AND L.ExportedCount = 0) OR (par_ignoreexportedalreadylearners = 1))
    		) L ON TRUE
    		LEFT JOIN LATERAL
    		(
    			SELECT COUNT(*) Count
    			FROM (SELECT DISTINCT SchoolID, GradeID FROM t$learners WHERE SchoolID = R.SchoolID AND GradeID IS NOT NULL) X
    			LEFT JOIN internal.DataExportAudit E ON E.Item = 'School Grade' AND E.ProgrammeID = var_ProgrammeID AND E.ItemID =  CAST(X.SchoolID AS bigint) * 1000000 + X.GradeID
    			WHERE
    				((par_ignoreexportedalreadyclasses = 0 AND E.DataExportAuditID IS NULL) OR (par_ignoreexportedalreadyclasses = 1))
    		) C ON TRUE
    		WHERE L.Count > 0 OR C.Count > 0
    		GROUP BY R.EntityID1, R.RegionEntityID, R.Hierarchy, R.Region
    	)

		SELECT
    		R.HierarchyEntityID,
    		R.Hierarchy,
    		R.RegionEntityID,
    		R.Region,
    		R.LearnerCount,
    		R.ClassCount,
    		TO_CHAR(NOW(), 'DD/MM/YYYY')		ExtractDate,
    		TO_CHAR(NOW(), 'yyyyMMddHHmm')	ExtractDateTime,
    		REPLACE(UPPER(R.Region),' ','')		RegionFileName,
    		var_ProgrammeName						ProgrammeName,
    		CASE WHEN O.EmailTo IS NOT NULL THEN O.EmailTo::text ELSE COALESCE(P.Email || COALESCE(';' || E.EmailTo,''),E.EmailCC)::text END EmailTo,
    		CASE WHEN O.EmailTo IS NOT NULL THEN E.EmailCC::text WHEN (P.Email || COALESCE(';' || E.EmailTo,'')) IS NULL THEN '' ELSE E.EmailCC::text END EmailCC
    	FROM results R
    	LEFT JOIN LATERAL
    	(
    		SELECT STRING_AGG((PC.Value ),',') Email
    		FROM dbo.PersonRoleHasEntityPerson PREP
    		JOIN dbo.PersonHasContact PC ON PREP.PersonID = PC.PersonID AND PC.ContactTypeID = 4
    		WHERE PREP.EntityID = R.RegionEntityID
    			AND PREP.PersonRoleID = 13
    	) P ON TRUE
    	LEFT JOIN LATERAL (SELECT  COALESCE(E.Value,'') EmailTo, COALESCE(E.Value1,'') EmailCC FROM dbo.SystemParameter E WHERE E.Category = var_ProgrammeName AND E.SubCategory = 'Registration' AND E.Name = 'Email'  LIMIT 1 ) E ON TRUE
    	LEFT JOIN LATERAL (SELECT  COALESCE(O.Value,'') EmailTo, COALESCE(O.Value1,'') EmailCC FROM dbo.SystemParameter O WHERE O.Category = var_ProgrammeName AND O.SubCategory = CONCAT('Registration ',R.Hierarchy) AND O.Name = 'Email Override' LIMIT 1) O ON TRUE;
 
    
    DROP TABLE IF EXISTS t$learners;   
    DROP TABLE IF EXISTS t$regions;
	DROP TABLE IF EXISTS t$learnerstemp;
	DROP TABLE IF EXISTS t$learnerstemp1;
	DROP TABLE IF EXISTS t$learnerstemp2;
	DROP TABLE IF EXISTS t$schoolentitytemp;
    
END;
$BODY$;

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