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

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

CREATE OR REPLACE FUNCTION internal.pextractmatificregistrationschoollist(
	par_hierarchy character varying,
	par_ignoreexportedalready numeric DEFAULT 0,
	par_updateexportlog numeric DEFAULT 1)
    RETURNS TABLE("SCHOOL_CODE" character varying, "NAME" character varying, "Province" character varying, "Region" character varying, "District" character varying, "PHONE" character varying, "EMAIL" character varying, "POSTCODE" character varying, "Contact First Name" character varying, "Contact Last Name" character varying, "Contact Email Address" character varying) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
DECLARE
    var_ProgrammeID INTEGER DEFAULT 8;
    var_HierarchyEntityID INTEGER;
    var_ReportHierarchyEntitID INTEGER;
BEGIN

  CREATE INDEX IF NOT EXISTS dataexportaudit_item_idx1
    ON internal.dataexportaudit USING btree
    (item COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS dataexportaudit_itemid_idx1
    ON internal.dataexportaudit USING btree
    (itemid ASC NULLS LAST)
    TABLESPACE pg_default;
    
    /*
    [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 t$schooltable
    (schoolid INTEGER);
--     PERFORM dbo.fgetschoolhierarchy(sl.schoolid);
	
		CREATE TEMP TABLE t$schoolentitytemp AS  		
		SELECT DISTINCT
				eh.entityid1,
				eh.schoolid,
				eh.entityid2,
				eh.entityname2,
				eh.entityname4,
				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;	
				
		RAISE NOTICE 't$schooltable';


    CREATE TEMP TABLE t$tempschool
	AS
	SELECT DISTINCT
            sl.schoolid
            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)::INT = 1
            JOIN t$schoolentitytemp  H ON H.schoolid = sl.schoolid
            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))
			  ;
			  
			  


INSERT INTO t$schooltable (schoolid)
SELECT
        s.schoolid
        FROM t$tempschool AS s
        LEFT OUTER JOIN internal.dataexportaudit AS dea
            ON LOWER(dea.item) = LOWER('School') AND var_ProgrammeID = dea.programmeid AND s.schoolid = dea.itemid
        WHERE ((par_IgnoreExportedAlready = 1) OR (par_IgnoreExportedAlready = 0 AND dea.dataexportauditid IS NULL));


		
	raise notice 'return query';
	return Query
    SELECT DISTINCT ON (sep.emiscode) 
        sep.emiscode AS "SCHOOL_CODE", sep.name AS "NAME", h.entityname2 AS "Province", h.entityname2 AS "Region", h.entityname4 AS "District", COALESCE(rc.cellphone, '') AS "PHONE", ''::character varying AS "EMAIL", ''::character varying AS "POSTCODE", COALESCE(rc.firstname, '')::character varying AS "Contact First Name", COALESCE(rc.lastname, '')::character varying AS "Contact Last Name", COALESCE(rc.email, '')::character varying AS "Contact Email Address"
        FROM t$schooltable AS ns
        JOIN dbo.vwschoolenvironmentprogramme AS sep
            ON sep.schoolid = ns.schoolid AND sep.programmeid = var_ProgrammeID
        JOIN t$schoolentitytemp  H ON H.schoolid = ns.schoolid
        LEFT JOIN (SELECT distinct on (p.personid)
            p.*, e.value AS email, c.value AS cellphone, ep.entityid
            FROM dbo.entityperson AS ep
            JOIN dbo.personrolehasentityperson AS prep
                ON ep.entityid = prep.entityid AND ep.personid = prep.personid
            JOIN dbo.person AS p
                ON ep.personid = p.personid
            LEFT OUTER JOIN dbo.personhascontact AS e
                ON p.personid = e.personid AND e.contacttypeid = 4 AND e.Order = 1
            LEFT OUTER JOIN dbo.personhascontact AS c
                ON p.personid = c.personid AND c.contacttypeid = 1 AND c.Order = 1
            WHERE  prep.personroleid = 12
            ) AS rc on rc.entityid = h.entityid2 
        ORDER BY sep.emiscode,sep.name NULLS FIRST;

    IF par_UpdateExportLog = 1 THEN
        INSERT INTO internal.dataexportaudit (item, itemid, programmeid)
        SELECT
            'School', ns.schoolid, var_ProgrammeID
            FROM t$schooltable AS ns
			;
    END IF;
    DROP TABLE IF EXISTS t$schooltable;
END;
$BODY$;

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