CREATE OR REPLACE FUNCTION reporting.pgeneratereports(
    par_report character varying,
    par_filterbyhierarchy character varying DEFAULT 'South Africa'::character varying,
    par_filterbyentitytype character varying DEFAULT NULL::character varying,
    par_filterbyentity character varying DEFAULT NULL::character varying,
    par_paramdate date DEFAULT NULL::date,
    par_paramyearterm character varying DEFAULT NULL::character varying,
    par_paramprogramme character varying DEFAULT NULL::character varying,
    par_reportlevel character varying DEFAULT NULL::character varying,
    par_paramextra character varying DEFAULT NULL::character varying,
    par_byclass integer DEFAULT 0,
    par_implementationstatus character varying DEFAULT NULL::character varying,
    par_dateinfilename integer DEFAULT 1,
    par_firstintermreports integer DEFAULT 0,
    par_firstintermemail integer DEFAULT 0)
    RETURNS TABLE(seq integer, total integer, description character varying, urlparameters text, filename text, itemid bigint, itemtype character varying, foldername character varying, paramdate date, paramyearterm character varying, folderdate date, checkdatastoredproc character varying, checkdatastoredprocparams character varying, grade character varying, class character varying, emailoverride integer) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
/* ccyyTt, e.g. 2019T2 */
DECLARE
    var_ProgrammeID SMALLINT;
    var_ParamYYMMDD VARCHAR(6);
    var_ParamDateYYYYMMDD VARCHAR(8);
    var_ParamDateYYYYMMDDDash VARCHAR(10);
    var_ParamYear SMALLINT;
    var_ParamYearS VARCHAR(4);
    var_ParamTerm SMALLINT;
    var_ParamTermS VARCHAR(1);
    var_ParamYearTermID SMALLINT;
    var_ParamYearTermIDS VARCHAR(10);
    var_ParamTermStartDate DATE;
    var_ParamTermEndDate DATE;
    var_FilenamePrefix VARCHAR(500);
    var_RootFolder VARCHAR(200);
    var_TimePeriodStart DATE;
    var_TimePeriodEnd DATE;
    var_UrlDatePart VARCHAR(500);
    var_FillEntityHierarchyName NUMERIC(1, 0) DEFAULT 0;
    var_FolderDate DATE;
    var_FileDate VARCHAR(30);
    var_CheckDataStoredProc VARCHAR(250) DEFAULT NULL;
    var_CheckDataStoredProcParams VARCHAR(250) DEFAULT NULL;
    var_RootEntityID INTEGER;
    var_ReportRootEntityID INTEGER;
    var_UseSchoolFilter NUMERIC(1, 0) DEFAULT 0;
    var_YearTermID INTEGER DEFAULT (SELECT
        MAX(yeartermid)
        FROM dbo.entityyearterm
        WHERE endweekfriday <= clock_timestamp());
    crsrgeneratereport CURSOR FOR
    SELECT
        reportlevel
        FROM reportleveltable$pgeneratereports;
BEGIN
    <<exitproc>>
    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
        */
        /*
        [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
        IF @ParamProgramme IS NULL OR TRIM(@ParamProgramme) = '' SET @ParamProgramme = 'Reading Eggs';
        */
        IF par_paramprogramme IS NULL OR TRIM(par_paramprogramme) = '' THEN
            par_paramprogramme := 'Reading Eggs';
        END IF;

        var_RootEntityID := (SELECT
            entityid
            FROM dbo.entity
            WHERE LOWER(name) = LOWER(par_FilterByHierarchy) AND entityid = rootentityid);
RAISE NOTICE 'var_RootEntityID: %', var_RootEntityID;

        var_ReportRootEntityID := dbo.fgetrelatedhierarchyrootentityid(var_RootEntityID, '%Report Regions%');
RAISE NOTICE 'var_ReportRootEntityID: %', var_ReportRootEntityID;

        IF par_DateInFileName IS NULL THEN
            par_DateInFileName := 1;
        END IF;

        CREATE TEMPORARY TABLE t$filterbyentitytable
        (entity VARCHAR(255));
RAISE NOTICE 'CREATE TEMPORARY TABLE t$filterbyentitytable';
        /*
        [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
        INSERT t$filterbyentitytable SELECT TRIM(value) FROM string_split(par_filterbyentity,',')
        */
        INSERT INTO t$filterbyentitytable SELECT trim(regexp_split_to_table) FROM regexp_split_to_table(par_filterbyentity,'[,]');        
RAISE NOTICE 'INSERT INTO t$filterbyentitytable';

        CREATE TEMPORARY TABLE t$programmeidtable
        (programmeid SMALLINT);
RAISE NOTICE 'CREATE TEMPORARY TABLE t$programmeidtable';        
        /*
        [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
        INSERT t$programmeidtable
            SELECT DISTINCT P.ProgrammeID
            FROM Programme P
            JOIN (SELECT TRIM(value) Programme FROM string_split(@ParamProgramme,',')) X ON X.Programme = P.Name OR X.Programme = P.Code OR X.Programme = CAST(P.ProgrammeID AS varchar)
        */
        INSERT INTO t$programmeidtable
            SELECT DISTINCT P.ProgrammeID
            FROM dbo.Programme P
            JOIN (SELECT TRIM(regexp_split_to_table) Programme FROM regexp_split_to_table(par_paramprogramme,'[,]')) X ON X.Programme = P.Name 
                                                    OR X.Programme = P.Code OR X.Programme = CAST(P.ProgrammeID AS varchar);        
RAISE NOTICE 'INSERT INTO t$programmeidtable';

        var_ProgrammeID := (SELECT
            programmeid
            FROM t$programmeidtable
            LIMIT 1);
RAISE NOTICE 'var_ProgrammeID: %', var_ProgrammeID;

        /*
        [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers., 9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
        IF TRIM(par_implementationstatus) = '' OR TRIM(par_implementationstatus) = '.' OR par_implementationstatus IS NULL SET par_implementationstatus = '4';
        */
        IF TRIM(par_implementationstatus) = '' OR TRIM(par_implementationstatus) = '.' OR par_implementationstatus IS NULL THEN 
            par_implementationstatus := '4'; 
        END IF;
RAISE NOTICE 'par_implementationstatus: %', par_implementationstatus;

        CREATE TEMPORARY TABLE t$implementationstatustable
        (implementationstatusid SMALLINT);
RAISE NOTICE 'CREATE TEMPORARY TABLE t$implementationstatustable';

        /*
        [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
        INSERT t$implementationstatustable
            SELECT DISTINCT I.ImplementationStatusID
            FROM (SELECT TRIM(value) Status FROM string_split(par_implementationstatus,',')) X
            JOIN ImplementationStatus I ON X.Status = I.Code OR X.Status = I.Description OR X.Status = CAST(I.ImplementationStatusID AS varchar)
        */
        INSERT INTO t$implementationstatustable
        SELECT DISTINCT I.ImplementationStatusID
        FROM (SELECT TRIM(regexp_split_to_table) Status FROM regexp_split_to_table(par_implementationstatus,'[,]')) X
        JOIN dbo.ImplementationStatus I ON X.Status = I.Code OR X.Status = I.Description OR X.Status = CAST(I.ImplementationStatusID AS varchar);    
RAISE NOTICE 'INSERT INTO t$implementationstatustable';

        CREATE TEMPORARY TABLE t$school
        (programmeid SMALLINT);
RAISE NOTICE 'CREATE TEMPORARY TABLE t$school';

        CREATE TEMPORARY TABLE t$result
        (description VARCHAR(2000) NOT NULL,
            urlparameters VARCHAR(2000) NOT NULL,
            filename VARCHAR(2000) NOT NULL,
            itemid BIGINT NULL,
            itemtype VARCHAR(250) NULL,
            /* */
            province VARCHAR(200) NULL,
            region VARCHAR(200) NULL,
            district VARCHAR(200) NULL,
            schoolfolder VARCHAR(200) DEFAULT ('School Reports'),
            grade VARCHAR(5) NULL DEFAULT (''),
            class VARCHAR(100) NULL DEFAULT (''),
            checkdatastoredprocparams VARCHAR(500) NULL,
            emailoverride INTEGER DEFAULT (2)
            /* 0 no email, 1 send email, 2 default to package */
        );
RAISE NOTICE 'CREATE TEMPORARY TABLE t$result';        

        CREATE TEMPORARY TABLE t$schoolfilter
        (schoolid INTEGER);
RAISE NOTICE 'CREATE TEMPORARY TABLE t$schoolfilter';        

        IF LOWER(par_Report) LIKE LOWER('%Weekly%') OR LOWER(par_Report) IN (LOWER('Login Cards'), LOWER('Learner Logins - Home Learning'), LOWER('Login Cards - Home Learning')) THEN
            IF LOWER(par_Report) IN (LOWER('Weekly Learner Extract (RE) - School Level'), LOWER('Weekly Learner Extract (RE) - Regional Level'), LOWER('Weekly Learner Extract - School Level'), LOWER('Weekly Learner Extract - Regional Level'), LOWER('Login Cards'), LOWER('Learner Logins - Home Learning'), LOWER('Login Cards - Home Learning')) THEN
                IF par_ParamDate = '9999-12-31' THEN
                    IF LOWER(par_ParamExtra) = LOWER('Learners=NEW') THEN
                        par_ParamDate := CAST (clock_timestamp() AS DATE);
                        var_FolderDate := (SELECT
                            weekfriday
                            FROM dbo.dimdate
                            WHERE date = par_ParamDate);
RAISE NOTICE 'par_ParamDate1: %', par_ParamDate;
RAISE NOTICE 'var_FolderDate1: %', var_FolderDate;                        
                    ELSE
                        IF LOWER(par_ParamExtra) = LOWER('Learners=ALL') THEN
                            par_ParamDate := CAST (clock_timestamp() AS DATE);
                            var_FolderDate := (SELECT
                                MAX(dd.date)
                                FROM dbo.dimdate AS dd
                                WHERE dd.date <= dateadd(day, - 2, clock_timestamp()) AND LOWER(dd.weekdayname) = LOWER('Friday'));
RAISE NOTICE 'par_ParamDate2: %', par_ParamDate;
RAISE NOTICE 'var_FolderDate2: %', var_FolderDate;                        
                        END IF;
                    END IF;
                ELSE
                    IF LOWER(par_ParamExtra) = LOWER('Learners=NEW') THEN
                        var_FolderDate := (SELECT
                            weekfriday
                            FROM dbo.dimdate
                            WHERE date = par_ParamDate);
RAISE NOTICE 'var_FolderDate3: %', var_FolderDate;                                                
                    ELSE
                        IF LOWER(par_ParamExtra) = LOWER('Learners=ALL') THEN
                            var_FolderDate := (SELECT
                                MAX(dd.date)
                                FROM dbo.dimdate AS dd
                                WHERE dd.date <= dateadd(day, - 2, par_ParamDate::INTEGER) AND LOWER(dd.weekdayname) = LOWER('Friday'));
RAISE NOTICE 'var_FolderDate4: %', var_FolderDate;
                        END IF;
                    END IF;
                END IF;
            ELSE
                IF par_ParamDate = '9999-12-31' OR par_ParamDate IS NULL THEN
                    par_ParamDate := (SELECT
                        MAX(dd.date)
                        FROM dbo.dimdate AS dd
                        WHERE dd.date <= dateadd(day, - 2, clock_timestamp()) AND LOWER(dd.weekdayname) = LOWER('Friday'));
RAISE NOTICE 'par_ParamDate5: %', par_ParamDate;                    
                END IF;
            END IF;
            par_ParamYearTerm := NULL;

            SELECT
                CAST (year AS VARCHAR(4)) || 'T' || CAST (term AS VARCHAR(1))
                INTO par_ParamYearTerm
                FROM dbo.entityyearterm AS eyt
                WHERE par_ParamDate BETWEEN eyt.startdate AND eyt.endweekfriday + (4::NUMERIC || ' DAY')::INTERVAL
                ORDER BY eyt.enddate DESC NULLS FIRST
                LIMIT 1;
RAISE NOTICE 'SELECT1';

            IF var_FolderDate IS NULL THEN
                var_FolderDate := par_ParamDate;
RAISE NOTICE 'var_FolderDate1: %', var_FolderDate;                                    
            END IF;

            IF par_ParamYearTerm IS NULL THEN
                IF LOWER(par_Report) IN (LOWER('Learner Logins - Home Learning'), LOWER('Login Cards - Home Learning'), LOWER('Reading Eggs Exception Report')) THEN
                    SELECT
                        CAST (year AS VARCHAR(4)) || 'T' || CAST (term AS VARCHAR(1))
                        INTO par_ParamYearTerm
                        FROM dbo.entityyearterm AS eyt
                        WHERE par_ParamDate < eyt.endweekfriday + (4::NUMERIC || ' DAY')::INTERVAL
                        ORDER BY eyt.enddate ASC NULLS FIRST                        
                        LIMIT 1;
RAISE NOTICE 'SELECT2';                        
                END IF;
            END IF;

            IF par_ParamYearTerm IS NULL THEN
                EXIT exitproc;
            END IF;
        ELSE
            IF LOWER(par_Report) LIKE LOWER('%Termly%') THEN
                IF LOWER(par_ParamYearTerm) = LOWER('9999T4') THEN
                    par_ParamYearTerm := (SELECT
                        CAST (year AS VARCHAR(30)) || 'T' || CAST (term AS VARCHAR(30))
                        FROM dbo.yearterm
                        WHERE yeartermid = var_YearTermID);
RAISE NOTICE 'par_ParamYearTerm1: %', par_ParamYearTerm;                                        
                END IF;

                IF par_ParamDate = '9999-12-31' THEN
                    par_ParamDate := (SELECT
                        enddate
                        FROM dbo.entityyearterm
                        WHERE yeartermid = var_YearTermID AND endweekfriday <= clock_timestamp()
                        LIMIT 1);
RAISE NOTICE 'par_ParamDate6: %', par_ParamDate;                                                            
                END IF;
            ELSE
                IF LOWER(par_Report) IN (LOWER('School Last Submission Extract')) THEN
                    par_ParamDate := clock_timestamp();
                    var_FolderDate := clock_timestamp();
RAISE NOTICE 'par_ParamDate7: %', par_ParamDate;                    
RAISE NOTICE 'var_FolderDate7: %', var_FolderDate;
                    /*
                    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
                    SELECT TOP 1 @ParamYearTerm=FORMATMESSAGE('%dT%d',Year, Term) FROM EntityYearTerm WHERE GETDATE() < EndWeekFriday ORDER BY Year, Term
                    */
                    SELECT concat('%dT%d',Year, Term) 
                    INTO par_paramyearterm
                    FROM dbo.EntityYearTerm 
                    WHERE NOW() < EndWeekFriday 
                    ORDER BY Year, Term
                    limit 1;                    
RAISE NOTICE 'par_paramyearterm7: %', par_paramyearterm;  

                ELSE
                    IF LOWER(par_Report) IN (LOWER('Reading Eggs Exception Report')) THEN
                        par_ParamDate := (SELECT
                            di.datacontractdatadate
                            FROM staging.dataimportdatacontract AS di
                            WHERE di.datasourcetypeid = 2 AND LOWER(di.dataimportstatus) = LOWER('Success')
                            ORDER BY di.datacontractdatadate DESC NULLS FIRST, di.dataimportcompletetime DESC NULLS FIRST
                            LIMIT 1);
                        var_FolderDate := par_ParamDate;
RAISE NOTICE 'par_ParamDate8: %', par_ParamDate;                    
RAISE NOTICE 'var_FolderDate8: %', var_FolderDate;                        
                        /*
                        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
                        SELECT TOP 1 @ParamYearTerm=FORMATMESSAGE('%dT%d',Year, Term) FROM EntityYearTerm WHERE GETDATE() < EndWeekFriday ORDER BY Year, Term
                        */
                    SELECT concat('%dT%d',Year, Term) 
                    INTO par_paramyearterm
                    FROM dbo.EntityYearTerm 
                    WHERE NOW() < EndWeekFriday 
                    ORDER BY Year, Term
                    limit 1;                    
RAISE NOTICE 'par_paramyearterm8: %', par_paramyearterm;  

                    ELSE
                        IF LOWER(par_Report) IN (LOWER('DDD File Check')) THEN
                            /* SET @ParamDate = GETDATE(); */
                            var_FolderDate := par_ParamDate;
RAISE NOTICE 'var_FolderDate9: %', var_FolderDate;                            
                            /*
                            [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
                            SELECT TOP 1 @ParamYearTerm=FORMATMESSAGE('%dT%d',Year, Term) FROM EntityYearTerm WHERE @ParamDate < EndWeekFriday ORDER BY Year, Term
                            */
                    SELECT concat('%dT%d',Year, Term) 
                    INTO par_paramyearterm
                    FROM dbo.EntityYearTerm 
                    WHERE par_ParamDate < EndWeekFriday 
                    ORDER BY Year, Term
                    limit 1;                    
RAISE NOTICE 'par_paramyearterm9: %', par_paramyearterm;                              
                        END IF;
                    END IF;
                END IF;
            END IF;
        END IF;
RAISE NOTICE 'END IF 1';

        var_ParamYear := (CASE
            WHEN LENGTH(par_ParamYearTerm) = 6 THEN CAST (LEFT(par_ParamYearTerm, 4) AS SMALLINT)
            ELSE NULL
        END);
RAISE NOTICE 'var_ParamYear10: %', var_ParamYear;     

        var_ParamTerm := (CASE
            WHEN LENGTH(par_ParamYearTerm) = 6 THEN CAST (RIGHT(par_ParamYearTerm, 1) AS SMALLINT)
            ELSE NULL
        END);
RAISE NOTICE 'var_ParamTerm10: %', var_ParamTerm;

        IF var_ParamYear IS NULL AND var_ParamTerm IS NULL AND par_ParamDate IS NOT NULL THEN
            SELECT DISTINCT
                yt.yeartermid, yt.year, yt.term, CAST (yt.year AS VARCHAR(30)) || 'T' || CAST (yt.term AS VARCHAR(30)), yt.startdate, yt.enddate
                INTO var_ParamYearTermID, var_ParamYear, var_ParamTerm, par_ParamYearTerm, var_ParamTermStartDate, var_ParamTermEndDate
                FROM dbo.entityyearterm AS yt
                WHERE par_ParamDate BETWEEN yt.startdate AND yt.enddate;
RAISE NOTICE 'SELECT DISTINCT 1';                
        ELSE
            IF var_ParamYear IS NOT NULL AND var_ParamTerm IS NOT NULL THEN
                SELECT DISTINCT
                    yt.yeartermid, yt.startdate, yt.enddate
                    INTO var_ParamYearTermID, var_ParamTermStartDate, var_ParamTermEndDate
                    FROM dbo.entityyearterm AS yt
                    WHERE yt.year = var_ParamYear AND yt.term = var_ParamTerm;
RAISE NOTICE 'SELECT DISTINCT 2';

                IF var_FolderDate IS NULL THEN
                    var_FolderDate := var_ParamTermEndDate;
RAISE NOTICE 'var_FolderDate11: %', var_FolderDate;                    
                END IF;
            END IF;
        END IF;

        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMAT(VARCHAR,VARCHAR) function. Create a user-defined function.]
        SET @ParamYYMMDD           = FORMAT(@ParamDate,'yyMMdd');
        */
        var_ParamYYMMDD := TO_CHAR(par_paramdate :: DATE, 'yyMMdd');
RAISE NOTICE 'var_ParamYYMMDD12: %', var_ParamYYMMDD;                            

        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMAT(VARCHAR,VARCHAR) function. Create a user-defined function.]
        SET @ParamDateYYYYMMDD     = FORMAT(@ParamDate,'yyyyMMdd');
        */
        var_ParamDateYYYYMMDD := TO_CHAR(par_paramdate :: DATE, 'yyyyMMdd');
RAISE NOTICE 'var_ParamDateYYYYMMDD12: %', var_ParamDateYYYYMMDD;                                    

        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMAT(VARCHAR,VARCHAR) function. Create a user-defined function.]
        SET @ParamDateYYYYMMDDDash = FORMAT(@ParamDate,'yyyy-MM-dd');
        */
        var_ParamDateYYYYMMDDDash := TO_CHAR(par_paramdate :: DATE, 'yyyy-MM-dd');
RAISE NOTICE 'var_ParamDateYYYYMMDDDash12: %', var_ParamDateYYYYMMDDDash;                                    

        var_ParamTermS := COALESCE(CAST (var_ParamTerm AS VARCHAR(1)), '');
        var_ParamYearS := COALESCE(CAST (var_ParamYear AS VARCHAR(4)), '');
        -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %. Argument: %', '50000', 0, 0, 'ParamDate: %s', var_ParamDateYYYYMMDD USING ERRCODE = '50000';

        IF LOWER(par_Report) IN (LOWER('Weekly Usage Report - School Level'), LOWER('Weekly Usage Report - Regional Level')) THEN
RAISE NOTICE 'IF 2';
            var_FileDate := var_ParamDateYYYYMMDD;
            var_TimePeriodStart := par_ParamDate;
            var_TimePeriodEnd := par_ParamDate;
            var_UrlDatePart := ClickCOMMON.dbo.fUrlParameter('HierarchyID', var_RootEntityID) || '&' + ClickCOMMON.dbo.fUrlParameter('ProgrammeID', var_ProgrammeID) || '&' + ClickCOMMON.dbo.fUrlParameter('YearTerm', par_ParamYearTerm) || '&' + ClickCommon.dbo.fUrlParameter('WeekDate', var_ParamDateYYYYMMDDDash);
            var_FilenamePrefix := 'Usage Report - Weekly';
            /*
            [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMAT(VARCHAR,VARCHAR) function. Create a user-defined function.]
            SET @RootFolder      = 'Weekly - ' + FORMAT(@FolderDate,'yyyyMMdd');
            */
            var_RootFolder := concat('Weekly - ',TO_CHAR(var_FolderDate :: DATE, 'yyyyMMdd'));
            var_FillEntityHierarchyName := 1;
        ELSE
RAISE NOTICE 'IF 3';        
            IF LOWER(par_Report) IN (LOWER('Termly Usage Report - School Level'), LOWER('Termly Usage Report - Regional Level')) THEN
RAISE NOTICE 'IF 4';            
                var_FileDate := var_ParamYearS || ' T' || var_ParamTermS;
                var_TimePeriodStart := var_ParamTermStartDate;
                var_TimePeriodEnd := var_ParamTermEndDate;
                var_UrlDatePart := ClickCOMMON.dbo.fUrlParameter('HierarchyID', var_RootEntityID) || '&' + ClickCOMMON.dbo.fUrlParameter('ProgrammeID', var_ProgrammeID) || '&' + ClickCOMMON.dbo.fUrlParameter('YearTerm', par_ParamYearTerm);
                var_FilenamePrefix := 'Usage Report - Termly';
                var_RootFolder := 'Termly - ' || var_FileDate;
                var_FillEntityHierarchyName := 1;
            ELSE
RAISE NOTICE 'IF 5';            
                IF LOWER(par_Report) IN (LOWER('Weekly Usage Data Extract')) THEN
RAISE NOTICE 'IF 6';                
                    var_FileDate := var_ParamDateYYYYMMDD;
                    var_TimePeriodStart := par_ParamDate;
                    var_TimePeriodEnd := par_ParamDate;
                    var_UrlDatePart := ClickCOMMON.dbo.fUrlParameter('HierarchyID', var_RootEntityID) || '&' + ClickCOMMON.dbo.fUrlParameter('ProgrammeID', var_ProgrammeID) || '&' + ClickCOMMON.dbo.fUrlParameter('YearTerm', par_ParamYearTerm) || '&' + ClickCommon.dbo.fUrlParameter('WeekDate', var_ParamDateYYYYMMDDDash);
                    var_FilenamePrefix := 'Weekly Usage Data Extract';
                    var_RootFolder := '';
                    var_FillEntityHierarchyName := 0;
                ELSE
RAISE NOTICE 'IF 7';                
                    IF LOWER(par_Report) IN (LOWER('Termly Usage Data Extract')) THEN
RAISE NOTICE 'IF 8';                    
                        var_FileDate := var_ParamYearS;
                        var_TimePeriodStart := par_ParamDate;
                        var_TimePeriodEnd := par_ParamDate;
                        var_UrlDatePart := ClickCOMMON.dbo.fUrlParameter('HierarchyID', var_RootEntityID) || '&' + ClickCOMMON.dbo.fUrlParameter('ProgrammeID', var_ProgrammeID) || '&' + ClickCOMMON.dbo.fUrlParameter('YearTerm', par_ParamYearTerm);
                        var_FilenamePrefix := 'Termly Usage Data Extract';
                        var_RootFolder := '';
                        var_FillEntityHierarchyName := 0;
                    ELSE
RAISE NOTICE 'IF 9';                    
                        IF LOWER(par_Report) IN (LOWER('Weekly Learner Extract (RE) - School Level'), LOWER('Weekly Learner Extract (RE) - Regional Level')) THEN
RAISE NOTICE 'IF 10';                        
                            var_FileDate := var_ParamDateYYYYMMDD;
                            var_TimePeriodStart := par_ParamDate;
                            var_TimePeriodEnd := par_ParamDate;
                            var_UrlDatePart := ClickCOMMON.dbo.fUrlParameter('HierarchyID', var_RootEntityID) || '&' + ClickCOMMON.dbo.fUrlParameter('ProgrammeID', var_ProgrammeID);
                            /* + '&' + ClickCOMMON.dbo.fUrlParameter('YearTerm',@ParamYearTerm) + '&' + ClickCommon.dbo.fUrlParameter('WeekDate',@ParamDateYYYYMMDDDash); */

                            IF LOWER(par_ParamExtra) = LOWER('Learners=NEW') THEN
RAISE NOTICE 'IF 11';                            
                                var_FilenamePrefix := 'New Learner Extract - Weekly';
                                var_CheckDataStoredProc := 'reporting.pWeeklyLearnerExtractRE';
                                /*
                                [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
                                SET @CheckDataStoredProcParams = FORMATMESSAGE('@HierarchyID=%d,@ProgrammeID=%d,@Learners=''NEW'',',@RootEntityID,4);
                                */
                                var_CheckDataStoredProcParams := concat('@HierarchyID=%d,@ProgrammeID=%d,@Learners=''NEW'',',var_RootEntityID,4);
                            ELSE
RAISE NOTICE 'IF 12';                            
                                var_FilenamePrefix := 'Learner Extract - Weekly';
                            END IF;
                            /*
                            [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMAT(VARCHAR,VARCHAR) function. Create a user-defined function.]
                            SET @RootFolder      = 'Weekly - ' + FORMAT(@FolderDate,'yyyyMMdd');
                            */
                            var_RootFolder := concat('Weekly - ',TO_CHAR(var_FolderDate :: DATE, 'yyyyMMdd'));
                            var_FillEntityHierarchyName := 1;
                        ELSE
RAISE NOTICE 'IF 13';                        
                            IF LOWER(par_Report) IN (LOWER('Weekly Learner Extract - School Level'), LOWER('Weekly Learner Extract - Regional Level')) THEN
RAISE NOTICE 'IF 14';                            
                                var_FileDate := var_ParamDateYYYYMMDD;
                                var_TimePeriodStart := par_ParamDate;
                                var_TimePeriodEnd := par_ParamDate;
                                var_UrlDatePart := ClickCOMMON.dbo.fUrlParameter('HierarchyID', var_RootEntityID);

                                IF LOWER(par_ParamExtra) = LOWER('Learners=NEW') THEN
RAISE NOTICE 'IF 15';                                
                                    var_FilenamePrefix := 'New Learner Extract - Weekly';
                                    var_CheckDataStoredProc := 'reporting.pWeeklyLearnerExtract';
                                    /*
                                    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
                                    SET @CheckDataStoredProcParams = FORMATMESSAGE('@HierarchyID=%d,@Learners=''NEW'',',@RootEntityID);
                                    */
                                    var_CheckDataStoredProcParams := concat('@HierarchyID=%d,@ProgrammeID=%d,@Learners=''NEW'',',var_RootEntityID,4);

                                ELSE
RAISE NOTICE 'IF 16';                                
                                    var_FilenamePrefix := 'Learner Extract - Weekly';
                                END IF;
                                /*
                                [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMAT(VARCHAR,VARCHAR) function. Create a user-defined function.]
                                SET @RootFolder      = 'Weekly - ' + FORMAT(@FolderDate,'yyyyMMdd');
                                */
                                var_RootFolder := concat('Weekly - ',TO_CHAR(var_FolderDate :: DATE, 'yyyyMMdd'));
                                var_FillEntityHierarchyName := 1;
                                INSERT INTO t$school (programmeid)
                                VALUES (4),
                                (8);

                                IF LOWER(par_ParamExtra) = LOWER('Learners=NEW') THEN
RAISE NOTICE 'IF 17';                                
                                    var_UseSchoolFilter := 1;
                                    INSERT INTO t$schoolfilter (schoolid)
                                    SELECT DISTINCT
                                        schoolid
                                        FROM dbo.personhasprogramme AS pp
                                        JOIN t$school AS p
                                            ON pp.programmeid = p.programmeid
                                        JOIN dbo.schoollearner AS sl
                                            ON pp.personid = sl.personid AND sl.enddate = '9999-12-31'
                                        WHERE pp.usernamedate = par_ParamDate;
                                END IF;
                            ELSE
RAISE NOTICE 'IF 18';                            
                                IF LOWER(par_Report) IN (LOWER('Learner Logins - Home Learning'), LOWER('Login Cards - Home Learning')) THEN
RAISE NOTICE 'IF 19';                                
                                    var_FileDate := var_ParamDateYYYYMMDD;
                                    var_TimePeriodStart := par_ParamDate;
                                    var_TimePeriodEnd := par_ParamDate;
                                    var_UrlDatePart := ClickCOMMON.dbo.fUrlParameter('HierarchyID', var_RootEntityID) || '&' + ClickCOMMON.dbo.fUrlParameter('ProgrammeID', var_ProgrammeID);
                                    /* + '&' + ClickCOMMON.dbo.fUrlParameter('YearTerm',@ParamYearTerm) + '&' + ClickCommon.dbo.fUrlParameter('WeekDate',@ParamDateYYYYMMDDDash); */

                                    IF LOWER(par_Report) IN (LOWER('Learner Logins - Home Learning')) THEN
RAISE NOTICE 'IF 20';                                    
                                        var_FilenamePrefix := 'Learner List';
                                    ELSE
RAISE NOTICE 'IF 21';                                    
                                        IF LOWER(par_Report) IN (LOWER('Login Cards - Home Learning')) THEN
                                            var_FilenamePrefix := 'Login Cards';
                                        END IF;
                                    END IF;
                                    /*
                                    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMAT(VARCHAR,VARCHAR) function. Create a user-defined function.]
                                    SET @RootFolder      = 'Weekly - ' + FORMAT(@FolderDate,'yyyyMMdd');
                                    */
                                    var_RootFolder := concat('Weekly - ',TO_CHAR(var_FolderDate :: DATE, 'yyyyMMdd'));
                                    var_FillEntityHierarchyName := 1;
                                ELSE
RAISE NOTICE 'IF 22';                                
                                    IF LOWER(par_Report) IN (LOWER('Login Cards')) THEN
RAISE NOTICE 'IF 23';                                    
                                        var_FileDate := var_ParamDateYYYYMMDD;
                                        var_TimePeriodStart := par_ParamDate;
                                        var_TimePeriodEnd := par_ParamDate;
                                        var_UrlDatePart := ClickCOMMON.dbo.fUrlParameter('HierarchyID', var_RootEntityID);
                                        /* + '&' + ClickCOMMON.dbo.fUrlParameter('YearTerm',@ParamYearTerm) + '&' + ClickCommon.dbo.fUrlParameter('WeekDate',@ParamDateYYYYMMDDDash); */

                                        IF LOWER(par_ParamExtra) = LOWER('Learners=NEW') THEN
RAISE NOTICE 'IF 24';                                        
                                            var_FilenamePrefix := 'New Login Cards';
                                            var_CheckDataStoredProc := 'reporting.pWeeklyLearnerExtract';
                                            /*
                                            [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
                                            SET @CheckDataStoredProcParams = FORMATMESSAGE('@HierarchyID=%d,@Learners=''NEW'',',@RootEntityID);
                                            */
                                            var_CheckDataStoredProcParams := concat('@HierarchyID=%d,@ProgrammeID=%d,@Learners=''NEW'',',var_RootEntityID,4);
                                        ELSE
RAISE NOTICE 'IF 25';                                        
                                            var_FilenamePrefix := 'Login Cards';
                                        END IF;
                                        /*
                                        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMAT(VARCHAR,VARCHAR) function. Create a user-defined function.]
                                        SET @RootFolder      = 'Weekly - ' + FORMAT(@FolderDate,'yyyyMMdd');
                                        */
                                        var_RootFolder := concat('Weekly - ',TO_CHAR(var_FolderDate :: DATE, 'yyyyMMdd'));
                                        var_FillEntityHierarchyName := 1;
                                        INSERT INTO t$school (programmeid)
                                        VALUES (4),
                                        (8);

                                        IF LOWER(par_ParamExtra) = LOWER('Learners=NEW') THEN
RAISE NOTICE 'IF 26';                                        
                                            var_UseSchoolFilter := 1;
                                            INSERT INTO t$schoolfilter (schoolid)
                                            SELECT DISTINCT
                                                schoolid
                                                FROM dbo.personhasprogramme AS pp
                                                JOIN t$school AS p
                                                    ON pp.programmeid = p.programmeid
                                                JOIN dbo.schoollearner AS sl
                                                    ON pp.personid = sl.personid AND sl.enddate = '9999-12-31'
                                                WHERE pp.usernamedate = par_ParamDate;
                                        END IF;
                                    ELSE
RAISE NOTICE 'IF 27';                                    
                                        IF LOWER(par_Report) IN (LOWER('School Last Submission Extract')) THEN
RAISE NOTICE 'IF 28';                                        
                                            var_FileDate := var_ParamDateYYYYMMDD;
                                            var_TimePeriodStart := par_ParamDate;
                                            var_TimePeriodEnd := par_ParamDate;
                                            var_UrlDatePart := '';
                                            var_FilenamePrefix := 'School Last Submission Extract';
                                            var_RootFolder := '';
                                            var_FillEntityHierarchyName := 0;

RAISE NOTICE 'var_FileDate28: %',var_FileDate;  
RAISE NOTICE 'var_TimePeriodStart28: %',var_TimePeriodStart;  
RAISE NOTICE 'var_TimePeriodEnd28: %',var_TimePeriodEnd;  
RAISE NOTICE 'var_UrlDatePart28: %',var_UrlDatePart;  
RAISE NOTICE 'var_FilenamePrefix28: %',var_FilenamePrefix;  
RAISE NOTICE 'var_RootFolder28: %',var_RootFolder;  
RAISE NOTICE 'var_FillEntityHierarchyName28: %',var_FillEntityHierarchyName;  

                                        ELSE
RAISE NOTICE 'IF 29';                                        
                                            IF LOWER(par_Report) IN (LOWER('Reading Eggs Exception Report')) THEN
RAISE NOTICE 'IF 30';                                            
                                                var_FileDate := var_ParamYYMMDD;
                                                var_TimePeriodStart := par_ParamDate;
                                                var_TimePeriodEnd := par_ParamDate;
                                                var_UrlDatePart := '';
                                                var_FilenamePrefix := 'RE Exception Report';
                                                var_RootFolder := '';
                                                var_FillEntityHierarchyName := 0;
                                            ELSE
RAISE NOTICE 'IF 31';                                            
                                                IF LOWER(par_Report) IN (LOWER('DDD File Check')) THEN
RAISE NOTICE 'IF 32';                                                
                                                    /*
                                                    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMAT(VARCHAR,VARCHAR) function. Create a user-defined function.]
                                                    SET @FileDate        = FORMAT(@ParamDate,'yyyy-MM-dd');
                                                    */
                                                    var_FileDate := TO_CHAR(par_ParamDate :: DATE, 'yyyy-MM-dd');
                                                    var_TimePeriodStart := par_ParamDate;
                                                    var_TimePeriodEnd := par_ParamDate;
                                                    var_UrlDatePart := '';
                                                    var_FilenamePrefix := 'DDD File Check';
                                                    var_RootFolder := '';
                                                    var_FillEntityHierarchyName := 0;
                                                END IF;
                                            END IF;
                                        END IF;
                                    END IF;
                                END IF;
                            END IF;
                        END IF;
                    END IF;
                END IF;
            END IF;
        END IF;
RAISE NOTICE 'END IF 32';        
        CREATE TEMPORARY TABLE t$entityhierarchyname
        (entityid INTEGER,
            entityname VARCHAR(250),
            entitytypeid SMALLINT,
            provinceid INTEGER,
            regionid INTEGER,
            districtid INTEGER,
            province VARCHAR(250),
            region VARCHAR(250),
            district VARCHAR(250),
            circuit VARCHAR(250),
            schoolid INTEGER,
            schoolname VARCHAR(250),
            emiscode VARCHAR(9),
            firstinterm NUMERIC(1, 0));
RAISE NOTICE 'CREATE TEMPORARY TABLE t$entityhierarchyname';

RAISE NOTICE 'var_FillEntityHierarchyName: %', var_FillEntityHierarchyName;        
        IF var_FillEntityHierarchyName = 1 THEN
RAISE NOTICE 'IF 40';
            IF NOT EXISTS (SELECT
                *
                FROM t$school) THEN
                INSERT INTO t$school
                VALUES (var_ProgrammeID);
            END IF;
            /* SELECT * FROM @SchoolProgrammeIDTable; */
            
            /*
            [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers., 9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
            INSERT #EntityHierarchyName(EntityID, EntityName, EntityTypeID, ProvinceID, RegionID, DistrictID, Province, Region, District, Circuit, SchoolID, SchoolName, EMISCode, FirstInTerm)
            
                    -- Active Schools
                    SELECT H.EntityID, H.EntityName, H.EntityTypeID, H.EntityID2, HR.EntityID2, H.EntityID4, H.EntityName2, ISNULL(HR.EntityName2,'Unknown Region'), H.EntityName4, ISNULL(H.EntityName5,''), H.SchoolID, H.SchoolName, H.EMISCode,
                        CASE
                            WHEN (YFUD.Timestamp IS NOT NULL AND DATEDIFF(day,TimeStamp,@ParamDate) <= 6) THEN 1
                            WHEN (YFUD.Timestamp IS NOT NULL AND DATEDIFF(day,TimeStamp,@ParamDate) > 6 AND PDNUM.Count = 1) THEN 1
                            ELSE 0
                        END
                    FROM vwEntityHierarchyName FOR SYSTEM_TIME FROM @TimePeriodStart TO @TimePeriodEnd H
                    LEFT JOIN vwEntityHierarchyName FOR SYSTEM_TIME FROM @TimePeriodStart TO @TimePeriodEnd HR ON HR.EntityID1 = @ReportRootEntityID AND H.SchoolID = HR.SchoolID
                    OUTER APPLY (SELECT COUNT(*) OtherCount FROM vwEntityHierarchy O JOIN Entity O1 ON O.EntityID1 = O1.EntityID WHERE O.EntityID1 <> H.EntityID1 AND O.SchoolID = H.SchoolID AND O1.EntityTypeID = 1 AND O.EntityID1 <> 1) OH
                    JOIN vwSchoolEnvironmentProgramme FOR SYSTEM_TIME FROM @TimePeriodStart TO @TimePeriodEnd SEP ON H.SchoolID = SEP.SchoolID
                    JOIN @SchoolProgrammeIDTable P ON SEP.ProgrammeID = P.ProgrammeID
                    JOIN EntityYearTerm EYT ON H.EntityID2 = EYT.EntityID AND @ParamDate BETWEEN EYT.StartDate AND DATEADD(DAY,4,EYT.EndWeekFriday)
                    CROSS APPLY (SELECT * FROM DimDate DD WHERE DD.[Date] = @ParamDate) PD
                    CROSS APPLY (SELECT COUNT(*) Count FROM DimDate DD WHERE DD.[Date] BETWEEN EYT.StartDate AND EYT.EndDate AND DD.WeekDayName = PD.WeekDayName AND DD.[Date] <= PD.[Date]) PDNUM
                    OUTER APPLY (SELECT TOP 1 TimeStamp FROM internal.ProcessingSchool P WHERE P.SchoolID = H.SchoolID AND P.Item = 'Year First Usable Data' AND YEAR(Timestamp) = EYT.Year AND TimeStamp <= @ParamDate) YFUD
                    WHERE H.EntityID1 = @RootEntityID
                        AND SEP.ImplementationStatusID IN (SELECT ImplementationStatusID FROM t$implementationstatustable WHERE ImplementationStatusID = 4)
                        AND SEP.[Name] NOT IN ('Click Foundation','Click Foundation Trust','Yebo Volunteers')
                        AND (OH.OtherCount = 0)
                        AND
                        (
                            (@FirstInTermReports IS NULL OR @FirstInTermReports = 0)
                            OR
                            (
                                (@FirstInTermReports = 1)
                                AND
                                (
                                    (YFUD.Timestamp IS NOT NULL AND DATEDIFF(day,TimeStamp,@ParamDate) <= 6)
                                    OR
                                    (YFUD.Timestamp IS NOT NULL AND DATEDIFF(day,TimeStamp,@ParamDate) > 6 AND PDNUM.Count = 1)
                                )
                            )
                        )
            
                    -- Planned Schools (with registered learners)
                    UNION
                    SELECT H.EntityID, H.EntityName, H.EntityTypeID, H.EntityID2, HR.EntityID2, H.EntityID4, H.EntityName2, ISNULL(HR.EntityName2,'Unknown Region'), H.EntityName4, ISNULL(H.EntityName5,''), H.SchoolID, H.SchoolName, H.EMISCode,
                        CASE
                            WHEN (YFUD.Timestamp IS NOT NULL AND DATEDIFF(day,TimeStamp,@ParamDate) <= 6) THEN 1
                            WHEN (YFUD.Timestamp IS NOT NULL AND DATEDIFF(day,TimeStamp,@ParamDate) > 6 AND PDNUM.Count = 1) THEN 1
                            ELSE 0
                        END
                    FROM vwEntityHierarchyName FOR SYSTEM_TIME FROM @TimePeriodStart TO @TimePeriodEnd H
                    LEFT JOIN vwEntityHierarchyName FOR SYSTEM_TIME FROM @TimePeriodStart TO @TimePeriodEnd HR ON HR.EntityID1 = @ReportRootEntityID AND H.SchoolID = HR.SchoolID
                    OUTER APPLY (SELECT COUNT(*) OtherCount FROM vwEntityHierarchy O JOIN Entity O1 ON O.EntityID1 = O1.EntityID WHERE O.EntityID1 <> H.EntityID1 AND O.SchoolID = H.SchoolID AND O1.EntityTypeID = 1 AND O.EntityID1 <> 1) OH
                    JOIN vwSchoolEnvironmentProgramme FOR SYSTEM_TIME FROM @TimePeriodStart TO @TimePeriodEnd SEP ON H.SchoolID = SEP.SchoolID
                    JOIN @SchoolProgrammeIDTable P ON SEP.ProgrammeID = P.ProgrammeID
                    JOIN EntityYearTerm EYT ON H.EntityID2 = EYT.EntityID AND @ParamDate BETWEEN EYT.StartDate AND DATEADD(DAY,4,EYT.EndWeekFriday)
                    CROSS APPLY (SELECT * FROM DimDate DD WHERE DD.[Date] = @ParamDate) PD
                    CROSS APPLY (SELECT COUNT(*) Count FROM DimDate DD WHERE DD.[Date] BETWEEN EYT.StartDate AND EYT.EndDate AND DD.WeekDayName = PD.WeekDayName AND DD.[Date] <= PD.[Date]) PDNUM
                    OUTER APPLY (SELECT TOP 1 TimeStamp FROM internal.ProcessingSchool P WHERE P.SchoolID = H.SchoolID AND P.Item = 'Year First Usable Data' AND YEAR(Timestamp) = EYT.Year) YFUD
                    CROSS APPLY
                    (
                        SELECT COUNT(*) LearnerCount, ISNULL(SUM(CASE WHEN PP.UserName IS NOT NULL THEN 1 ELSE 0 END),0) RegisteredLearnerCount
                        FROM SchoolLearner SL
                        JOIN PersonHasProgramme PP ON SL.PersonID = PP.PersonID AND PP.ProgrammeID = P.ProgrammeID
                        WHERE SL.SchoolID = H.SchoolID
                            AND SL.EndDate = '9999-12-31'
                    ) L
                    WHERE H.EntityID1 = @RootEntityID
                        AND SEP.ImplementationStatusID IN (SELECT ImplementationStatusID FROM t$implementationstatustable WHERE ImplementationStatusID = 13)
                        AND SEP.[Name] NOT IN ('Click Foundation','Click Foundation Trust','Yebo Volunteers')
                        AND L.LearnerCount > 0
                        AND (L.RegisteredLearnerCount / NULLIF(L.LearnerCount,0)) >= 0.5
                        AND
                        (
                            (@FirstInTermReports IS NULL OR @FirstInTermReports = 0)
                            OR
                            (
                                (@FirstInTermReports = 1)
                                AND
                                (
                                    (YFUD.Timestamp IS NOT NULL AND DATEDIFF(day,TimeStamp,@ParamDate) <= 6)
                                    OR
                                    (YFUD.Timestamp IS NOT NULL AND DATEDIFF(day,TimeStamp,@ParamDate) > 6 AND PDNUM.Count = 1)
                                )
                            )
                        )
                        AND (OH.OtherCount = 0)
            */
            IF var_UseSchoolFilter = 1 THEN
                DELETE FROM t$entityhierarchyname
                    WHERE sf.schoolid IS NULL;
            END IF;
        END IF;
        /* SELECT *FROM #EntityHierarchyName; */

        IF LOWER(par_Report) IN (LOWER('Weekly Usage Report - School Level'), LOWER('Termly Usage Report - School Level'), LOWER('Weekly Learner Extract (RE) - School Level'), LOWER('Weekly Learner Extract - School Level'), LOWER('Login Cards')) THEN
RAISE NOTICE 'IF 41';        
            /*
            [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function., 7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
            INSERT #Result(ItemID, ItemType, [Description], URLParameters, [FileName], Province, Region, District, SchoolFolder, Grade, Class, CheckDataStoredProcParams, EmailOverride)
                    SELECT DISTINCT
                        CASE WHEN @ByClass = 1 THEN SC.SchoolClassID ELSE X.SchoolID END ItemID,
                        CASE WHEN @ByClass = 1 THEN 'SchoolClass' ELSE 'School' END Item,
                        X.SchoolName,
                        --ClickCOMMON.dbo.fUrlParameter('SchoolID', X.SchoolID) + '&' + ClickCOMMON.dbo.fUrlParameter('SchoolClassID',SC.SchoolClassID) + '&' + CASE WHEN LEN(@UrlDatePart) > 0 THEN '&' + @UrlDatePart ELSE '' END,
                        --@FilenamePrefix + 'Grade ' + ISNULL(SC.SASAMSClassName,SC.ClassName) + ' - ' + @FileDate,
                        CASE
                            WHEN @ByClass = 1 THEN ClickCOMMON.dbo.fUrlParameter('SchoolID', X.SchoolID) + '&' + ClickCOMMON.dbo.fUrlParameter('SchoolClassID',SC.SchoolClassID) + CASE WHEN LEN(@UrlDatePart) > 0 THEN '&' + @UrlDatePart ELSE '' END
                            ELSE ClickCOMMON.dbo.fUrlParameter('SchoolID', X.SchoolID) + CASE WHEN LEN(@UrlDatePart) > 0 THEN '&' + @UrlDatePart ELSE '' END
                        END URLParameters,
                        CASE
                            WHEN @ByClass = 1 THEN @FilenamePrefix + ' - Grade ' + SC.Class
                            ELSE  @FilenamePrefix + ' - ' + X.SchoolName
                        END + CASE WHEN @DateInFileName = 1 THEN ' - ' + @FileDate ELSE '' END [FileName],
                        X.Province,
                        X.Region,
                        X.District,
                        CASE WHEN @ByClass = 1 THEN X.SchoolName ELSE '' END SchoolFolder,
                        CASE WHEN @ByClass = 1 THEN SC.Grade ELSE '' END Grade,
                        CASE WHEN @ByClass = 1 THEN SC.Class ELSE '' END Class,
                        CASE
                            WHEN @CheckDataStoredProc IS NOT NULL AND @ByClass = 0 THEN @CheckDataStoredProcParams + FORMATMESSAGE('@SchoolID=%d',X.SchoolID)
                            WHEN @CheckDataStoredProc IS NOT NULL AND @ByClass = 1 THEN @CheckDataStoredProcParams + FORMATMESSAGE('@SchoolID=%d,@SchoolClassID=%d',X.SchoolID,SC.SchoolClassID)
                            ELSE NULL
                        END,
                        CASE
                            WHEN @FirstInTermEmail = 1 AND X.FirstInTerm = 1 THEN 1
                            WHEN @FirstInTermEmail = 1 AND X.FirstInTerm = 0 THEN 0
                            ELSE 2
                        END
                    FROM
                    (
                        -- Country, Province, District, School
                        SELECT H.SchoolID, H.SchoolName, H.Province, H.Region, H.District, H.FirstInTerm
                        FROM #EntityHierarchyName H
                        WHERE H.EntityTypeID = 7 AND par_filterbyentityType LIKE 'Country'
                        UNION
                        SELECT H.SchoolID, H.SchoolName, H.Province, H.Region, H.District, H.FirstInTerm
                        FROM #EntityHierarchyName H
                        JOIN t$filterbyentitytable E ON H.Province = E.Entity
                        WHERE H.EntityTypeID = 7 AND par_filterbyentityType LIKE 'Province'
                        UNION
                        SELECT H.SchoolID, H.SchoolName, H.Province, H.Region, H.District, H.FirstInTerm
                        FROM #EntityHierarchyName H
                        JOIN t$filterbyentitytable E ON H.Region = E.Entity
                        WHERE H.EntityTypeID = 7 AND par_filterbyentityType LIKE '%Reporting Region'
                        UNION
                        SELECT H.SchoolID, H.SchoolName, H.Province, H.Region, H.District, H.FirstInTerm
                        FROM #EntityHierarchyName H
                        JOIN t$filterbyentitytable E ON H.District = E.Entity
                        WHERE H.EntityTypeID = 7 AND par_filterbyentityType LIKE 'District'
                        UNION
                        SELECT H.SchoolID, H.SchoolName, H.Province, H.Region, H.District, H.FirstInTerm
                        FROM #EntityHierarchyName H
                        JOIN t$filterbyentitytable E ON H.Circuit = E.Entity
                        WHERE H.EntityTypeID = 7 AND par_filterbyentityType LIKE 'Circuit'
                        UNION
                        SELECT H.SchoolID, H.SchoolName, H.Province, H.Region, H.District, H.FirstInTerm
                        FROM #EntityHierarchyName H
                        JOIN t$filterbyentitytable E ON (H.SchoolName = E.Entity OR H.EMISCode = E.Entity)
                        WHERE H.EntityTypeID = 7 AND par_filterbyentityType LIKE 'School'
                    ) X
                    OUTER APPLY
                    (
                        SELECT SC.SchoolID, G.GradeID, G.Code Grade, SC.SchoolClassID, ISNULL(SC.SASAMSClassName,SC.ClassName) Class
                        FROM SchoolClass SC
                        JOIN Grade G ON SC.GradeID = G.GradeID
                        CROSS APPLY
                        (
                            SELECT COUNT(*) LearnerCount
                            FROM SchoolLearnerYear SLY
                            WHERE SLY.SchoolClassID = SC.SchoolClassID AND SLY.[Year] = YEAR(@ParamDate)
                        ) L
                        WHERE @ByClass = 1 AND X.SchoolID = SC.SchoolID AND SC.[Year] = YEAR(@ParamDate)
                            AND L.LearnerCount > 0
                    ) SC
                    WHERE (@ByClass = 0) OR (@ByClass = 1 AND SC.SchoolClassID IS NOT NULL)
            */
            BEGIN
            END;
        ELSE
RAISE NOTICE 'IF 42';                
            IF LOWER(par_Report) IN (LOWER('Learner Logins - Home Learning'), LOWER('Login Cards - Home Learning')) THEN
RAISE NOTICE 'IF 43';                    
                INSERT INTO t$result (itemid, itemtype, description, urlparameters, filename, province, region, district, schoolfolder, grade, class)
                SELECT DISTINCT
                    sc.schoolclassid, 'SchoolClass', x.schoolname, ClickCOMMON.dbo.fUrlParameter('SchoolID', x.schoolid) || '&' + ClickCOMMON.dbo.fUrlParameter('SchoolClassID', sc.schoolclassid) || '&' ||
                    CASE
                        WHEN LENGTH(var_UrlDatePart) > 0 THEN '&' || var_UrlDatePart
                        ELSE ''
                    END, var_FilenamePrefix || ' - Grade ' || COALESCE(sc.sasamsclassname, sc.classname) ||
                    CASE
                        WHEN par_DateInFileName = 1 THEN ' - ' || var_FileDate
                        ELSE ''
                    END, x.province, x.region, x.district, x.schoolname, g.code, COALESCE(sc.sasamsclassname, sc.classname)
                    FROM LATERAL
                    /* Country, Province, District, School */
                    (SELECT
                        h.schoolid, h.schoolname, h.province, h.region, h.district
                        FROM t$entityhierarchyname AS h
                        WHERE h.entitytypeid = 7 AND LOWER(par_FilterByEntityType) LIKE LOWER('Country')
                    UNION
                    SELECT
                        h.schoolid, h.schoolname, h.province, h.region, h.district
                        FROM t$entityhierarchyname AS h
                        JOIN t$filterbyentitytable AS e
                            ON LOWER(h.province) = LOWER(e.entity)
                        WHERE h.entitytypeid = 7 AND LOWER(par_FilterByEntityType) LIKE LOWER('Province')
                    UNION
                    SELECT
                        h.schoolid, h.schoolname, h.province, h.region, h.district
                        FROM t$entityhierarchyname AS h
                        JOIN t$filterbyentitytable AS e
                            ON LOWER(h.region) = LOWER(e.entity)
                        WHERE h.entitytypeid = 7 AND LOWER(par_FilterByEntityType) SIMILAR TO LOWER('%Reporting Region')
                    UNION
                    SELECT
                        h.schoolid, h.schoolname, h.province, h.region, h.district
                        FROM t$entityhierarchyname AS h
                        JOIN t$filterbyentitytable AS e
                            ON LOWER(h.district) = LOWER(e.entity)
                        WHERE h.entitytypeid = 7 AND LOWER(par_FilterByEntityType) LIKE LOWER('District')
                    UNION
                    SELECT
                        h.schoolid, h.schoolname, h.province, h.region, h.district
                        FROM t$entityhierarchyname AS h
                        JOIN t$filterbyentitytable AS e
                            ON (LOWER(h.schoolname) = LOWER(e.entity) OR LOWER(h.emiscode) = LOWER(e.entity))
                        WHERE h.entitytypeid = 7 AND LOWER(par_FilterByEntityType) LIKE LOWER('School')) AS x
                    JOIN dbo.schoolclass AS sc
                        ON x.schoolid = sc.schoolid AND sc.year = date_part('year', par_ParamDate)
                    JOIN dbo.grade AS g
                        ON sc.gradeid = g.gradeid
                    CROSS JOIN (SELECT
                        COUNT(*) AS learnercount
                        FROM dbo.schoollearneryear AS sly
                        WHERE sly.schoolclassid = sc.schoolclassid AND sly.year = date_part('year', par_ParamDate)) AS l
                    WHERE l.learnercount > 0;
                /* ORDER BY SC.GradeID, SC.ClassName */
            ELSE
RAISE NOTICE 'IF 44';                    
                IF LOWER(par_Report) IN (LOWER('Weekly Usage Report - Regional Level'), LOWER('Termly Usage Report - Regional Level'), LOWER('Weekly Learner Extract (RE) - Regional Level'), LOWER('Weekly Learner Extract - Regional Level')) THEN
RAISE NOTICE 'IF 45';                        
                    IF par_ReportLevel IS NULL OR LOWER(par_ReportLevel) = LOWER('') THEN
                        par_ReportLevel :=
                        CASE
                            WHEN LOWER(par_FilterByEntityType) LIKE LOWER('Country') THEN 'Province'
                            WHEN LOWER(par_FilterByEntityType) LIKE LOWER('Province') THEN 'Reporting Region'
                            WHEN LOWER(par_FilterByEntityType) SIMILAR TO LOWER('%Reporting Region') THEN 'District'
                        END;
                    ELSE
                        IF LOWER(par_ReportLevel) = LOWER('ALL') THEN
                            par_ReportLevel :=
                            CASE
                                WHEN LOWER(par_FilterByEntityType) LIKE LOWER('Country') THEN 'Province,Reporting Region,District'
                                WHEN LOWER(par_FilterByEntityType) LIKE LOWER('Province') THEN 'Reporting Region,District'
                                WHEN LOWER(par_FilterByEntityType) SIMILAR TO LOWER('%Reporting Region') THEN 'District'
                            END;
                        END IF;
                    END IF;
                    CREATE TEMPORARY TABLE reportleveltable$pgeneratereports
                    (reportlevel VARCHAR(250));
                    INSERT INTO reportleveltable$pgeneratereports (reportlevel)
                    SELECT
                        value
                        FROM string_split(par_ReportLevel, ',');
                    OPEN crsrgeneratereport;
                    FETCH NEXT FROM crsrgeneratereport INTO par_ReportLevel;

                    WHILE (CASE FOUND::INT
                        WHEN 0 THEN - 1
                        ELSE 0
                    END) = 0 LOOP
                        /*
                        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
                        INSERT #Result(ItemID, ItemType, [Description], URLParameters, [FileName], Province, Region, District, CheckDataStoredProcParams)
                                    SELECT DISTINCT
                                        X.EntityID,
                                        X.EntityType,
                                        X.EntityName,
                                        ClickCOMMON.dbo.fUrlParameter('EntityType', X.EntityType) + '&' + ClickCOMMON.dbo.fUrlParameter('EntityID',X.EntityID) + CASE WHEN LEN(@UrlDatePart) > 0 THEN '&' + @UrlDatePart ELSE '' END,
                                        @FilenamePrefix + ' - ' + X.EntityName + CASE WHEN @DateInFileName = 1 THEN ' - ' + @FileDate ELSE '' END,
                                        X.Province,
                                        X.Region,
                                        X.District,
                                        CASE
                                            WHEN @CheckDataStoredProc IS NOT NULL THEN @CheckDataStoredProcParams + FORMATMESSAGE('@EntityID=%d',X.EntityID)
                                            ELSE NULL
                                        END
                                    FROM
                                    (
                                        SELECT
                                            CASE
                                                WHEN @ReportLevel LIKE 'Province'          THEN H.Province
                                                WHEN @ReportLevel LIKE '%Reporting Region' THEN H.Region
                                                WHEN @ReportLevel LIKE 'District'          THEN H.District
                                            END EntityName,
                                            CASE
                                                WHEN @ReportLevel LIKE 'Province'          THEN 'Province'
                                                WHEN @ReportLevel LIKE '%Reporting Region' THEN 'Click Reporting Region'
                                                WHEN @ReportLevel LIKE 'District'          THEN 'District'
                                            END EntityType,
                                            CASE
                                                WHEN @ReportLevel LIKE 'Province'          THEN H.ProvinceID
                                                WHEN @ReportLevel LIKE '%Reporting Region' THEN H.RegionID
                                                WHEN @ReportLevel LIKE 'District'          THEN H.DistrictID
                                            END EntityID,
                                            H.Province, H.Region, H.District
                                        FROM #EntityHierarchyName H
                                        WHERE (
                                               ((par_filterbyentityType LIKE 'Country'          )                                      )
                                            OR ((par_filterbyentityType LIKE 'Province'         ) AND (H.Province    = par_filterbyentity))
                                            OR ((par_filterbyentityType LIKE '%Reporting Region') AND (H.Region      = par_filterbyentity))
                                            )
                                    ) X
                        */
                        FETCH NEXT FROM crsrgeneratereport INTO par_ReportLevel;
                    END LOOP;
                    CLOSE crsrgeneratereport;
                    DROP TABLE IF EXISTS reportleveltable$pgeneratereports;
                ELSE
RAISE NOTICE 'IF 46';                        
                    IF LOWER(par_Report) SIMILAR TO LOWER('Weekly Usage Data Extract') THEN
                        INSERT INTO t$result (itemid, itemtype, description, urlparameters, filename, province, region, district)
                        SELECT
                            1, 'Country', 'All Schools', var_UrlDatePart, var_FilenamePrefix ||
                            CASE
                                WHEN par_DateInFileName = 1 THEN ' - ' || var_FileDate
                                ELSE ''
                            END, '', '', '';
                    ELSE
                        IF LOWER(par_Report) SIMILAR TO LOWER('Termly Usage Data Extract') THEN
                            INSERT INTO t$result (itemid, itemtype, description, urlparameters, filename, province, region, district)
                            SELECT
                                1, 'Country', 'All Schools', var_UrlDatePart, var_FilenamePrefix ||
                                CASE
                                    WHEN par_DateInFileName = 1 THEN ' - ' || var_FileDate
                                    ELSE ''
                                END, '', '', '';
                        ELSE
                            IF LOWER(par_Report) SIMILAR TO LOWER('School Last Submission Extract') THEN
RAISE NOTICE 'IF 47';                             
                                INSERT INTO t$result (itemid, itemtype, description, urlparameters, filename, province, region, district)
                                SELECT
                                    495, 'Organisation', 'All Schools', '', var_FilenamePrefix ||
                                    CASE
                                        WHEN par_DateInFileName = 1 THEN ' - ' || var_FileDate
                                        ELSE ''
                                    END, '', '', '';
                            ELSE
                                IF LOWER(par_Report) SIMILAR TO LOWER('Reading Eggs Exception Report') THEN
                                    INSERT INTO t$result (itemid, itemtype, description, urlparameters, filename, province, region, district)
                                    SELECT
                                        495, 'Organisation', 'All Schools', '', var_FilenamePrefix ||
                                        CASE
                                            WHEN par_DateInFileName = 1 THEN ' ' || var_FileDate
                                            ELSE ''
                                        END, '', '', '';
                                ELSE
                                    IF LOWER(par_Report) SIMILAR TO LOWER('DDD File Check') THEN
                                        INSERT INTO t$result (itemid, itemtype, description, urlparameters, filename, province, region, district)
                                        SELECT
                                            1, 'Country', 'All Schools', '', var_FilenamePrefix ||
                                            CASE
                                                WHEN par_DateInFileName = 1 THEN ' ' || var_FileDate
                                                ELSE ''
                                            END, '', '', '';
                                    END IF;
                                END IF;
                            END IF;
                        END IF;
                    END IF;
                END IF;
            END IF;
        END IF;
    END;
    RAISE NOTICE 'END IF 46';        
    /*
    [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
    WITH reports([Description], URLParameters, [FileName], ItemID, ItemType, Grade, Class, CheckDataStoredProcParams, EmailOverride) AS
        (
            SELECT DISTINCT
                R.[Description]                                         [Description],
                R.URLParameters                                         [URLParameters],
                CASE WHEN @RootFolder IS NULL OR @RootFolder = '' THEN '' ELSE @RootFolder + '\' END
                + CASE WHEN R.Province IS NULL OR R.Province = '' THEN '' ELSE R.Province + '\' END
                        + CASE WHEN R.ItemType IN ('Click Reporting Region', 'District', 'School','SchoolClass') THEN ISNULL(R.Region,'Unknown Region') + '\' ELSE '' END
                        + CASE WHEN R.ItemType IN ('District', 'School','SchoolClass')                           THEN R.District  + '\' ELSE '' END
                        + CASE WHEN R.ItemType IN ('School','SchoolClass')                         THEN R.SchoolFolder + '\' ELSE '' END
                        + R.[FileName],
                ISNULL(R.ItemID,0)                                      [ItemID],
                R.ItemType                                              [ItemType],
                R.Grade,
                R.Class,
                R.CheckDataStoredProcParams,
                R.EmailOverride
            FROM #Result R
        )
        , reportsCount(Total) AS
        (
            SELECT COUNT(*) FROM reports
        )
        SELECT --TOP 2
            CAST((ROW_NUMBER() OVER (ORDER BY [FileName])) AS int)  [Seq],
            T.[Total],
            R.[Description],
            R.URLParameters + CASE WHEN @ParamExtra IS NULL OR TRIM(@ParamExtra) = '' THEN '' ELSE '&' + @ParamExtra END UrlParameters,
            R.[FileName],
            R.ItemID,
            R.ItemType,
            ISNULL(@RootFolder,'')  FolderName,
            @ParamDate              ParamDate,
            @ParamYearTerm          ParamYearTerm,
            @FolderDate             FolderDate,
            ISNULL(@CheckDataStoredProc,'') CheckDataStoredProc,
            ISNULL(R.CheckDataStoredProcParams,'') CheckDataStoredProcParams,
            ISNULL(Grade,'')        Grade,
            ISNULL(Class,'')        Class,
            ISNULL(EmailOverride,2) EmailOverride
        FROM reports R
        CROSS JOIN reportsCount T
        ORDER BY Seq;
    */

    RETURN QUERY
    WITH reports(Description, URLParameters, FileName, ItemID, ItemType, Grade, Class, CheckDataStoredProcParams, EmailOverride) AS
        (
            SELECT DISTINCT
                R.Description AS Description,
                R.URLParameters AS URLParameters,
                concat(
                CASE WHEN var_RootFolder IS NULL OR var_RootFolder = '' THEN '' ELSE concat(var_RootFolder , '\') END
                    , CASE WHEN R.Province IS NULL OR R.Province = '' THEN '' ELSE concat(R.Province , '\') END
                            , CASE WHEN R.ItemType IN ('Click Reporting Region', 'District', 'School','SchoolClass') THEN concat(COALESCE(R.Region,'Unknown Region') , '\') ELSE '' END
                            , CASE WHEN R.ItemType IN ('District', 'School','SchoolClass') THEN concat(R.District  , '\') ELSE '' END
                            , CASE WHEN R.ItemType IN ('School','SchoolClass') THEN concat(R.SchoolFolder , '\') ELSE '' END
                            , R.FileName
                ) AS FileName,
                COALESCE(R.ItemID,0) AS ItemID,
                R.ItemType AS ItemType,
                R.Grade,
                R.Class,
                R.CheckDataStoredProcParams,
                R.EmailOverride
            FROM t$result R
        )
        , reportsCount(Total) AS
        (
            SELECT COUNT(*) FROM reports
        )
        SELECT --TOP 2
            CAST((ROW_NUMBER() OVER (ORDER BY R.FileName)) AS int) AS Seq,
            T.Total::integer,
            R.Description,
            CONCAT(R.URLParameters , CASE WHEN par_paramextra IS NULL OR TRIM(par_paramextra) = '' THEN '' ELSE concat('&' , par_paramextra) END) AS UrlParameters,
            R.FileName,
            R.ItemID,
            R.ItemType,
            COALESCE(var_RootFolder,'') AS FolderName,
            par_paramdate AS ParamDate,
            par_paramyearterm AS ParamYearTerm,
            var_FolderDate AS FolderDate,
            COALESCE(var_CheckDataStoredProc,'') AS CheckDataStoredProc,
            COALESCE(R.CheckDataStoredProcParams,'') AS CheckDataStoredProcParams,
            COALESCE(R.Grade,'') AS Grade,
            COALESCE(R.Class,'') AS Class,
            COALESCE(R.EmailOverride,2) AS EmailOverride
        FROM reports R
        CROSS JOIN reportsCount T
        ORDER BY Seq;
RAISE NOTICE 'WITH reports RETURN QUERY'; 

    DROP TABLE IF EXISTS t$filterbyentitytable;
    DROP TABLE IF EXISTS t$programmeidtable;
    DROP TABLE IF EXISTS t$implementationstatustable;
    DROP TABLE IF EXISTS t$school;
    DROP TABLE IF exists t$result;
    DROP TABLE IF exists t$schoolfilter;
    DROP TABLE IF exists t$entityhierarchyname;    
    /*
    
    DROP TABLE IF EXISTS t$result;
    */
    /*
    
    Temporary table must be removed before end of the function.
    */
    /*
    
    DROP TABLE IF EXISTS t$schoolfilter;
    */
    /*
    
    Temporary table must be removed before end of the function.
    */
    /*
    
    DROP TABLE IF EXISTS t$entityhierarchyname;
    */
    /*
    
    Temporary table must be removed before end of the function.
    */
END;
$BODY$;

ALTER FUNCTION reporting.pgeneratereports(character varying, character varying, character varying, character varying, date, character varying, character varying, character varying, character varying, integer, character varying, integer, integer, integer)
    OWNER TO postgres;
