-- FUNCTION: dbo.plearnersregisterfromsourcelearnerf(uuid, character varying, character varying, numeric, integer)

-- DROP FUNCTION IF EXISTS dbo.plearnersregisterfromsourcelearnerf(uuid, character varying, character varying, numeric, integer);

CREATE OR REPLACE FUNCTION dbo.plearnersregisterfromsourcelearnerf(
	par_executionid uuid DEFAULT NULL::uuid,
	par_schools character varying DEFAULT NULL::character varying,
	par_programmes character varying DEFAULT NULL::character varying,
	par_silent numeric DEFAULT 0,
	par_debugmode integer DEFAULT 0)
    RETURNS TABLE(dataimportid numeric, learnercount numeric) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
DECLARE
    var_TranCountOnEntry INTEGER
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the @@TRANCOUNT function. Create a user-defined function.]
    = @@TRANCOUNT
    */;
    var_DataDate DATE;
    var_SourceYear SMALLINT;
    var_RowCount BIGINT;
    var_SourceDataImportIDs VARCHAR(2048);
    var_DataSource VARCHAR(2048);
    var_DataImportID BIGINT;
    var_Message VARCHAR(2048);
    pupdatedataimport$ReturnCode INTEGER;
BEGIN

		drop table if exists t$schoolslist;
		drop table if exists t$schools;
		drop table if exists t$uniqueschools;
		drop table if exists t$schoolgrades;
		drop table if exists t$uniqueschoolgrades;
		drop table if exists t$learners;
		DROP TABLE IF EXISTS t$programmeidtable;
		DROP TABLE IF EXISTS t$newdataimportidtable;
    /*
    [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
    */
    var_DataDate = clock_timestamp();
    var_SourceYear = date_part('year', var_DataDate);
	Raise Notice '%',par_Programmes;
	raise notice '%',var_SourceYear;
	
    CREATE TEMPORARY TABLE t$programmeidtable
    (programmeid SMALLINT);
	
    RAISE NOTICE 'CREATE TEMPORARY TABLE t$programmeidtable';
	
    IF LOWER(par_Programmes) = LOWER('ALL') THEN
        INSERT INTO t$programmeidtable
        SELECT
            4
        UNION
        SELECT
            8
        UNION
        SELECT
            9;
    ELSE
        INSERT INTO t$programmeidtable
        SELECT
            p.programmeid
            FROM dbo.programme_dw AS p
            JOIN (SELECT
                value
                FROM split_part(par_Programmes, ','::varchar,1::int)
                 ) AS f
                ON LOWER(f.value) = LOWER(p.code) OR LOWER(f.value) = LOWER(p.name) OR
                CASE
                    WHEN aws_sqlserver_ext.ISNUMERIC(f.value) THEN CAST (f.value AS INTEGER)
                    ELSE NULL
                END = p.programmeid;
    END IF;
	
    CREATE TEMPORARY TABLE t$schoolslist
    (emiscode VARCHAR(9),
        schoolid INTEGER);
		
RAISE NOTICE 'CREATE TEMPORARY TABLE t$schoolslist';  

    IF (LOWER(par_Schools) = LOWER('ALL')) THEN
        INSERT INTO t$schoolslist
        SELECT
            emiscode, schoolid
            FROM dbo.school_dw;
    END IF;

    IF (LOWER(par_Schools) IN (LOWER('WC'), LOWER('CEMIS'))) THEN
        INSERT INTO t$schoolslist
        SELECT
            emiscode, schoolid
            FROM dbo.school_dw
            WHERE LOWER(LEFT(emiscode, 1)) = LOWER('1');
    ELSE
        IF (LOWER(par_Schools) IN (LOWER('NotWC'), LOWER('DDD'))) THEN
            INSERT INTO t$schoolslist
            SELECT
                emiscode, schoolid
                FROM dbo.school_dw
                WHERE LOWER(LEFT(emiscode, 1)) <> LOWER('1');
        ELSE
            IF (aws_sqlserver_ext.isnumeric(LEFT(par_Schools, 1)) = true) THEN
                INSERT INTO t$schoolslist
                SELECT
                    emiscode, schoolid
                    FROM dbo.school_dw
                    WHERE emiscode IN (SELECT
                        value
                        FROM split_part(par_Schools, ','::varchar,1::int));
            END IF;
        END IF;
    END IF;
	
    CREATE INDEX ix_t$schoolslist_ix1 ON t$schoolslist
        (emiscode);
    CREATE INDEX ix_t$schoolslist_ix2 ON t$schoolslist
        (schoolid);
		
    CREATE TEMPORARY TABLE t$schools
    (schoolid INTEGER,
        emiscode VARCHAR(9),
        schoolname VARCHAR(100),
        programmeenvironmentid INTEGER,
        programmeid SMALLINT,
        implementationstatusid SMALLINT,
        grades VARCHAR(100),
        gradecount SMALLINT);
		
RAISE NOTICE 'CREATE TEMPORARY TABLE t$schools';      
    /*
    [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
    INSERT #Schools
    	(SchoolID, EMISCode, SchoolName, ProgrammeEnvironmentID, ProgrammeID, ImplementationStatusID)
    	SELECT DISTINCT SEP.SchoolID, SEP.EMISCode, SEP.Name, SEP.ProgrammeEnvironmentID, SEP.ProgrammeID, SEP.ImplementationStatusID
    	FROM ClickDW.dbo.vwSchoolEnvironmentProgramme SEP
    	JOIN #SchoolsList SX ON SEP.SchoolID = SX.SchoolID
    	JOIN @ProgrammeIDTable P ON SEP.ProgrammeID = P.ProgrammeID
    	JOIN (SELECT DISTINCT PE.ProgrammeEnvironmentID FROM ClickDW.dbo.ProgrammeEnvironment FOR SYSTEM_TIME FROM '2020-01-01' TO '9999-12-31' PE WHERE PE.ImplementationStatusID in (4,13)) PEACTIVE ON SEP.ProgrammeEnvironmentID = PEACTIVE.ProgrammeEnvironmentID
    	WHERE 1=1
    		AND SEP.ProgrammeManagementLevelID = 1 --only look at automatic
    		AND LEN(SEP.EMISCode) = 9
    		AND ISNUMERIC(SEP.EMISCOde) = 1 -- filter out ECD and AS
    		AND SEP.ImplementationStatusID IN (4,13)
    */

    INSERT INTO t$schools
        (SchoolID, EMISCode, SchoolName, ProgrammeEnvironmentID, ProgrammeID, ImplementationStatusID)
		SELECT DISTINCT SEP.SchoolID, SEP.EMISCode, SEP.Name, SEP.ProgrammeEnvironmentID, SEP.ProgrammeID, SEP.ImplementationStatusID
        FROM dbo.vwschoolenvironmentprogramme_dw SEP
        JOIN t$schoolslist SX ON SEP.SchoolID = SX.SchoolID
        JOIN t$programmeidtable P ON SEP.ProgrammeID = P.ProgrammeID
		 WHERE 1=1
            AND SEP.ProgrammeManagementLevelID = 1 --only look at automatic
            AND LENGTH(SEP.EMISCode) = 9
            AND aws_sqlserver_ext.isnumeric(SEP.EMISCOde)  -- = 1 -- filter out ECD and AS
            AND SEP.ImplementationStatusID IN (4,13);

--         SELECT DISTINCT SEP.SchoolID, SEP.EMISCode, SEP.Name, SEP.ProgrammeEnvironmentID, SEP.ProgrammeID, SEP.ImplementationStatusID
--         FROM dbo.vwschoolenvironmentprogramme_dw SEP
--         JOIN t$schoolslist SX ON SEP.SchoolID = SX.SchoolID
--         JOIN t$programmeidtable P ON SEP.ProgrammeID = P.ProgrammeID
-- /*
--         JOIN (SELECT DISTINCT PE.ProgrammeEnvironmentID 
--               FROM dbo.programmeenvironment_dw
--                 FOR SYSTEM_TIME 
--                 FROM '2020-01-01' TO '9999-12-31' PE 
--               WHERE PE.ImplementationStatusID in (4,13)) PEACTIVE ON SEP.ProgrammeEnvironmentID = PEACTIVE.ProgrammeEnvironmentID
-- */
--         WHERE 1=1
--             AND SEP.ProgrammeManagementLevelID = 1 --only look at automatic
--             AND LENGTH(SEP.EMISCode) = 9
--             AND SEP.EMISCOde::numeric = 1 -- filter out ECD and AS
--             AND SEP.ImplementationStatusID IN (4,13);

    -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %. Argument: %', '50000', 0, 0, '#Schools: %d', "var_@ROWCOUNT" USING ERRCODE = '50000';
    CREATE UNIQUE INDEX ix_t$schools_ix1 ON t$schools
        (schoolid, programmeid);
    CREATE UNIQUE INDEX ix_t$schools_ix2 ON t$schools
        (programmeenvironmentid);
		
    CREATE TEMPORARY TABLE t$uniqueschools
    AS
    SELECT DISTINCT
        schoolid, emiscode, schoolname
        FROM t$schools;
		
RAISE NOTICE 'CREATE TEMPORARY TABLE t$uniqueschools';              
    -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %. Argument: %', '50000', 0, 0, '#UniqueSchools: %d', "var_@ROWCOUNT" USING ERRCODE = '50000';
	
    CREATE TEMPORARY TABLE t$schoolgrades
    (schoolid INTEGER,
        programmeid SMALLINT,
        gradeid INTEGER,
        managementlevel INTEGER);
		
RAISE NOTICE 'CREATE TEMPORARY TABLE t$schoolgrades';                  
    /*
    [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
    INSERT #SchoolGrades
    	(SchoolID, ProgrammeID, GradeID, ManagementLevel)
    	SELECT S.SchoolID, S.ProgrammeID, TG.GradeID, TG.LearnerManagementLevelID
    	FROM #Schools S
    	JOIN ClickDW.dbo.ProgrammeEnvironmentHasTargetGrade FOR SYSTEM_TIME AS OF @DataDate TG ON S.ProgrammeEnvironmentID = TG.ProgrammeEnvironmentID AND TG.ActiveFrom <= @DataDate
    	WHERE TG.LearnerManagementLevelID = 1
    */

    INSERT INTO t$schoolgrades
        (SchoolID, ProgrammeID, GradeID, ManagementLevel)
    SELECT S.SchoolID, S.ProgrammeID, TG.GradeID, TG.LearnerManagementLevelID
    FROM t$schools S
/* 
    JOIN dbo.programmeenvironmenthastargetgrade_dw 
        FOR SYSTEM_TIME AS OF @DataDate TG ON S.ProgrammeEnvironmentID = TG.ProgrammeEnvironmentID AND TG.ActiveFrom <= @DataDate
*/ 
    JOIN dbo.programmeenvironmenthastargetgrade_dw TG ON S.ProgrammeEnvironmentID = TG.ProgrammeEnvironmentID AND TG.ActiveFrom <= var_DataDate
    WHERE TG.LearnerManagementLevelID = 1;

    -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %. Argument: %', '50000', 0, 0, '#SchoolGrades: %d', "var_@ROWCOUNT" USING ERRCODE = '50000';
    CREATE UNIQUE INDEX ix_t$schoolgrades_ix1 ON t$schoolgrades
        (schoolid, programmeid, gradeid);
		
    CREATE TEMPORARY TABLE t$uniqueschoolgrades
    AS
    SELECT DISTINCT
        schoolid, gradeid
        FROM t$schoolgrades;
		
RAISE NOTICE 'CREATE TEMPORARY TABLE t$uniqueschoolgrades'; 
raise notice 'var_DataDate: %, var_SourceYear: %',var_DataDate,var_SourceYear;
    /* SELECT * FROM #UniqueSchoolGrades WHERE SchoolID = 100357 */
    -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %. Argument: %', '50000', 0, 0, '#UniqueSchoolGrades: %d', "var_@ROWCOUNT" USING ERRCODE = '50000';
	
    CREATE TEMP TABLE t$learners(
   		LastSubmissionDate Date,
		SchoolName character varying(100),
		Emiscode character varying(10),
		dddlearnerid integer,
		Firstname character varying(50),
		LastName character varying(50),
		AccessionNumber text,
		IDNumber text,
		DateOfBirth Date,
		Gender text,
		Race text,
		LuritsNumber text,
		Citizenship text,
		PassportNumber text,
		DateJoined Date,
		DateLeft Date,
		HomeLanguage text,
		InstructionLanguage text,
		Grade smallint,
		Class integer,
		ClassName text,
		CEMISLearnerID text,
		idNumberInvalid character varying(30),
		idNumberInvalidReason character varying(50),
		SourceDataImportId bigint,
		PersonID integer
	);
	
	
	Insert into t$learners
    --AS
    /* DISTINCT */
    (SELECT 
        var_DataDate AS "LastSubmissionDate", s.schoolname, s.emiscode, l.dddlearnerid, l.firstname, l.lastname, coalesce(l.accessionnumber, '') AS "AccessionNumber", coalesce(l.idnumber, '') AS "IDNumber", l.dateofbirth AS "DateOfBirth", coalesce(l.gender, '') AS "Gender", coalesce(l.race, '') AS "Race", coalesce(l.luritsnumber, '') AS "LuritsNumber", coalesce(l.citizenship, '') AS "Citizenship", coalesce(l.passportnumber, '') AS "PassportNumber", l.datejoined AS "DateJoined", l.dateleft AS "DateLeft", coalesce(l.homelanguage, '') AS "HomeLanguage", coalesce(l.instructionlanguage, '') AS "InstructionLanguage", l.grade AS "Grade", l.class AS "Class", coalesce(l.classname, '') AS "ClassName", coalesce(l.cemislearnerid, '') AS "CEMISLearnerID", idnumberinvalid, idnumberinvalidreason, sourcedataimportid, l.mappedpersonid AS "PersonID"
        FROM dbo.sourcelearner_dw AS l
        JOIN t$uniqueschools AS s
            ON LOWER(l.emiscode) = LOWER(s.emiscode)
        JOIN t$uniqueschoolgrades AS sg
            ON s.schoolid = sg.schoolid AND l.grade = sg.gradeid
        WHERE 
	 -- *** Removed because no data was returned as mappedpersonid and mappedschoollearnerid had values ***
	 -- This will can be put back but no learners will be returned  
	  	 l.mappedpersonid IS NULL AND l.mappedschoollearnerid IS NULL AND 
	 		l.year = var_SourceYear AND l.dateleft IS NULL
-- 	 	and l.idnumber <> '1406121276081' -- ignore this learner for testing
        ORDER BY s.emiscode NULLS FIRST, lastname NULLS FIRST, firstname NULLS FIRST);
-- 		RETURN; -- TESTING
RAISE NOTICE 'CREATE TEMPORARY TABLE t$learners';  

	
    /*
    [7833 - Severity CRITICAL - Migration @@rowcount function in current context is not supported. Perform a manual conversion.]
    SET @RowCount = @@ROWCOUNT;
    */
    -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %. Argument: %', '50000', 0, 0, '#Learners: %d', var_RowCount USING ERRCODE = '50000';

    BEGIN
        IF var_TranCountOnEntry = 0 THEN
            /*
            [7807 - Severity CRITICAL - PostgreSQL does not support explicit transaction management in functions. Perform a manual conversion.]
            BEGIN TRAN
            */
            BEGIN
            END;
        END IF;
		
        SELECT
            string_agg(sourcedataimportid, ',')
            INTO var_SourceDataImportIDs
            FROM (SELECT DISTINCT
                CAST (sourcedataimportid AS VARCHAR(2048)) AS sourcedataimportid
                FROM t$learners) AS l;

        IF var_SourceDataImportIDs IS NULL THEN
            var_SourceDataImportIDs = CAST ((SELECT
                MAX(sourcedataimportid)
                FROM dbo.sourcelearner_dw) AS VARCHAR(2048));
        END IF;
		
        var_DataSource = 'SourceLearner-Registration: ' || var_SourceDataImportIDs;
RAISE NOTICE 'var_DataSource: %',var_DataSource;
        -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %. Argument: %', '50000', 0, 0, '%s', var_DataSource USING ERRCODE = '50000';
        /* IF @SourceDataImportIDs <> '' */
        /* BEGIN */
        /* SELECT @DataImportID = DI.DataImportID */
        /* FROM DataImportDataContract DI */
        /* WHERE DI.DataSourceTypeID = 11 */
        /* AND DI.DataImportStatus = 'Success' */
        /* AND DI.DataContractDataSource = @DataSource */
        /* END */
		     CREATE TEMPORARY TABLE t$newdataimportidtable(
            	datacontractid BIGINT,
                dataimportid BIGINT,
                createtime TIMESTAMP WITHOUT TIME ZONE,
                starttime TIMESTAMP WITHOUT TIME ZONE,
                value1 character varying,
                value2 character varying
			);

        IF var_DataImportID IS NOT NULL THEN
			Raise Notice 'Data Import done before';
        ELSE
       
RAISE NOTICE 't$newdataimportidtable';                                              
			Raise Notice 'par_DataSource %', var_DataSource;
			Raise Notice 'par_ExecutionID %', par_ExecutionID;
			Raise Notice 'par_DataDate %', var_DataDate;
           
		   INSERT INTO t$newdataimportidtable(datacontractid,dataimportid, createtime, starttime, value1,value2)
               SELECT p.datacontractid, p.dataimportid, p.createtime, p.starttime, p.value1,p.value2
				from dbo.pnewdataimportid('Learner - Manual'::character varying, var_DataSource::character varying,var_DataDate::TIMESTAMP WITHOUT TIME ZONE) AS P;
				
            Raise Notice 'call newId successful';
			Raise Notice 'ImportID: %', var_DataImportID;
			
			SELECT
                T.dataimportid
                INTO var_DataImportID
                FROM t$newdataimportidtable AS T;
				
			Raise Notice 'Import ID Assigned: %', var_DataImportID;
           
		   INSERT INTO dbo.learner (dataimportid, rownumber, lastsubmissiondate, province, region, district, circuit, cluster, schoolname, emiscode, dddlearnerid, firstname, lastname, accessionnumber, idnumber, dateofbirth, gender, race, luritsnumber, citizenship, passportnumber, datejoined, dateleft, homelanguage, instructionlanguage, grade, class, classname, extid, extidtype, personid, idnumberinvalidreason, idnumberinvalid, cemislearnerid)
            SELECT
                var_DataImportID, ROW_NUMBER() OVER (ORDER BY emiscode, lastname, firstname), lastsubmissiondate, NULL, NULL, NULL, NULL, NULL, schoolname, emiscode, dddlearnerid, firstname, lastname, accessionnumber, idnumber, dateofbirth, gender, race, luritsnumber, citizenship, passportnumber, datejoined, dateleft, homelanguage, instructionlanguage, grade, class, classname,
               -- Raise Notice 'Case1';
				CASE
					
                    WHEN dddlearnerid IS NOT NULL THEN CAST (dddlearnerid AS VARCHAR(20))
                    WHEN cemislearnerid IS NOT NULL THEN cemislearnerid
                    ELSE NULL
                END,
				
                CASE
					--Raise Notice 'Case2';
                    WHEN dddlearnerid IS NOT NULL THEN 'DDD'
                    WHEN cemislearnerid IS NOT NULL THEN 'CEMIS'
                    ELSE NULL
                END, personid, idnumberinvalidreason, idnumberinvalid, cemislearnerid
                FROM t$learners AS l;
				
			Raise Notice 'Update import';
            GET DIAGNOSTICS var_RowCount = ROW_COUNT;
			
            var_Message = 'Completed. ' || CAST (var_RowCount AS VARCHAR(20)) || ' rows imported.';
			Raise Notice '%',var_Message;
			RAISE NOTICE 'dataimportid: %', var_DataImportID;
			
            CALL  dbo.pupdatedataimport(var_DataImportID, 'Success', var_Message, pupdatedataimport$ReturnCode);
			Raise Notice 'called update importID';
			
            DROP TABLE IF EXISTS newdataimportidtable;
			
        END IF;
		Raise Notice '342, VarIMportID %', var_DataImportID;
        SELECT 
            COUNT(*)
            INTO var_RowCount
            FROM dbo.learner AS D
            WHERE D.dataimportid = var_DataImportID;
 		Raise Notice '348';
        IF par_Silent = 0 THEN
		Raise Notice 'var_DataImportID %', var_DataImportID;
		Raise Notice 'Row count %', var_RowCount;
		
        RETURN QUERY
        SELECT
             var_DataImportID::Numeric AS dataimportid, var_RowCount::Numeric AS learnercount;
        END IF;
--             COMMIT;
       -- END IF;
--         EXCEPTION
--             WHEN OTHERS THEN
				
--                 IF var_TranCountOnEntry = 0 THEN
--                     ROLLBACK;
-- 					RAISE Exception 'Error';
--                 END IF;
                
                
   END;
		drop table if exists t$schoolslist;
		drop table if exists t$schools;
		drop table if exists t$uniqueschools;
		drop table if exists t$schoolgrades;
		drop table if exists t$uniqueschoolgrades;
		drop table if exists t$learners;
		DROP TABLE IF EXISTS t$programmeidtable;
END;
$BODY$;

ALTER FUNCTION dbo.plearnersregisterfromsourcelearnerf(uuid, character varying, character varying, numeric, integer)
    OWNER TO postgres;
