-- PROCEDURE: internal.pupdatesourcelearner(bigint, integer, integer)

-- DROP PROCEDURE IF EXISTS internal.pupdatesourcelearner(bigint, integer, integer);

CREATE OR REPLACE PROCEDURE internal.pupdatesourcelearner(
	par_sourcedataimportid bigint,
	par_updatepersonid integer DEFAULT 2,
	INOUT return_code integer DEFAULT 0)
LANGUAGE 'plpgsql'
AS $BODY$
/* 0 = No, 1 = Update All, 2 = Update Only Nulls */
/* @SourceDataYearOnly      smallint = null */
DECLARE
    var_TranCountOnEntry INTEGER
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the @@TRANCOUNT function. Create a user-defined function.]
    = @@TRANCOUNT
    */;
    var_SourceDataDescription VARCHAR(250);
    var_SourceRowCount BIGINT;
    var_SourceDataDate DATE;
    var_SourceDataYear SMALLINT;
    var_SourceDataDateS VARCHAR(10);
    var_SourceDataType VARCHAR(100);
    var_DataSourceTypeID INTEGER;
    var_RowCount BIGINT;
    var_LastUpdated TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP(3);
    var_LastFriday DATE;
    var_CurrentYear SMALLINT;
    var_PreviousYear SMALLINT;
    var_SourceDataImportIDChanged BIGINT;
    var_SourceDataImportIDMerged BIGINT;
    sql$rowcount BIGINT;
	update_diag INTEGER;
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;
    */
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMAT(VARCHAR,VARCHAR) function. Create a user-defined function.]
    */
	DROP TABLE IF EXISTS t$changeddata;
    DROP TABLE IF EXISTS t$mergeddata;
    DROP TABLE IF EXISTS t$sourcedata;
    DROP TABLE IF EXISTS t$sourcedata2;
	DROP TABLE IF EXISTS t$schoolmatch;
	DROP TABLE IF EXISTS t$otherschoolmatch;
	DROP TABLE IF EXISTS t$matches;	

	RAISE NOTICE 'Begin';
    SELECT
        DI.DataContractDescription,
        DI.DataContractDataDate,
        TO_CHAR(DI.DataContractDataDate :: DATE, 'yyyy-MM-dd'),
        DI.DataSourceType,
        DI.DataSourceTypeID,
        EXTRACT (YEAR FROM DI.DataContractDataDate)
    INTO
            var_SourceDataDescription,
            var_SourceDataDate,
            var_SourceDataDateS,
            var_SourceDataType,
            var_DataSourceTypeID,
            var_SourceDataYear
        FROM dbo.dataimportdatacontract_staging DI
        WHERE DI.DataImportID = par_sourcedataimportid
            AND DI.DataImportStatus = 'Success'
            AND DI.DataSourceTypeID IN (8,11,14,18);

RAISE NOTICE '%: var_SourceDataDescription: %', timeofday(), var_SourceDataDescription;   
RAISE NOTICE 'var_SourceDataYear: %', var_SourceDataYear;   
--RAISE NOTICE 'var_SourceDataDate: %', var_SourceDataDate;   

    IF var_SourceDataDescription IS NULL THEN
        -- RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %', '50000', 16, 0, '@SourceDataImportID=%I64d not found.', par_SourceDataImportID USING ERRCODE = '50000';
        return_code := 1;
        RETURN;
    END IF;

RAISE NOTICE 'var_DataSourceTypeID: %', var_DataSourceTypeID;   
    IF var_DataSourceTypeID = 8 THEN
        SELECT
            dataimportid
            INTO var_SourceDataImportIDChanged
            FROM dbo.dataimportdatacontract_staging AS di
            WHERE di.datasourcetypeid = 29 AND di.datacontractdatadate = var_SourceDataDate AND LOWER(di.dataimportstatus) = LOWER('Success') order by dataimportid desc;
        SELECT
            dataimportid
            INTO var_SourceDataImportIDMerged
            FROM dbo.dataimportdatacontract_staging AS di
            WHERE di.datasourcetypeid = 30 AND di.datacontractdatadate = var_SourceDataDate AND LOWER(di.dataimportstatus) = LOWER('Success') order by dataimportid desc;
    END IF;
	
	RAISE NOTICE 'var_SourceDataImportIDChanged: %',var_SourceDataImportIDChanged;
	RAISE NOTICE 'var_SourceDataImportIDMerged: %',var_SourceDataImportIDMerged;
	
    -- RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %. Argument: %. Argument: %. Argument: %', '50000', 0, 0, 'SourceDataImportID=%I64d, @SourceDataDate=%s, @SourceDataImportIDChanged=%I64d, @SourceDataImportIDMerged=%I64d', par_SourceDataImportID, var_SourceDataDateS, var_SourceDataImportIDChanged, var_SourceDataImportIDMerged USING ERRCODE = '50000';
    /* SET @SourceDataYearOnly = @DataYear; */
    var_CurrentYear = var_SourceDataYear;
    var_PreviousYear = (var_SourceDataYear - 1)::INT;
    var_LastFriday = (SELECT
        MAX(date)
        FROM dbo.dimdate AS dd
        WHERE dd.date <= var_SourceDataDate AND LOWER(dd.weekdayname) = LOWER('Friday'));
RAISE NOTICE 'var_LastFriday: %', var_LastFriday;   
    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;
RAISE NOTICE 'var_SourceDataImportIDChanged: %', var_SourceDataImportIDChanged;  

      CREATE TEMP TABLE t$changeddata(
		  		rownumber BIGINT,
                submissiondate DATE,
                olddddlearnerid INTEGER,
                newdddlearnerid INTEGER ,
                olddddlearneridexists INTEGER  DEFAULT (0),
                newdddlearneridexists INTEGER  DEFAULT (0),
                inmergeddata INTEGER DEFAULT (0),
                status VARCHAR(100) NULL);
				
        IF var_SourceDataImportIDChanged IS NOT NULL THEN
		
	RAISE NOTICE 'IF STATEMENT: %', var_SourceDataImportIDChanged; 

				
RAISE NOTICE 'merged data';

       CREATE TEMPORARY TABLE t$mergeddata(
            	rownumber BIGINT NOT NULL,
                submissiondate DATE NOT NULL,
                olddddlearnerid INTEGER NOT NULL,
                shareddddlearnerid INTEGER NOT NULL,
                olddddlearneridexists INTEGER NULL DEFAULT (0),
                shareddddlearneridexists INTEGER NULL DEFAULT (0),
                status VARCHAR(100) NULL);
				
            INSERT INTO t$changeddata (rownumber, submissiondate, olddddlearnerid, newdddlearnerid)
            SELECT
                rownumber, 
                make_date(CAST(SUBSTR(submissiondate, 5, 4) AS INTEGER), CAST(SUBSTR(submissiondate, 3, 2) AS INTEGER), CAST(SUBSTR(submissiondate, 1, 2) AS INTEGER)),
                l.olddddlearnerid, l.newdddlearnerid
                FROM dbo.learnerdddchangedid_staging AS l
                WHERE l.dataimportid = var_SourceDataImportIDChanged
                ORDER BY rownumber NULLS FIRST;

           Raise Notice '135';
            CREATE INDEX ix_t$changeddata_ix1 ON t$changeddata
                (olddddlearnerid, newdddlearnerid);
				
            INSERT INTO t$mergeddata (rownumber, submissiondate, olddddlearnerid, shareddddlearnerid)
            SELECT
                rownumber, 
                make_date(CAST(SUBSTR(submissiondate, 5, 4) AS INTEGER), CAST(SUBSTR(submissiondate, 3, 2) AS INTEGER), CAST(SUBSTR(submissiondate, 1, 2) AS INTEGER)),
                l.olddddlearnerid, l.shareddddlearnerid
                FROM dbo.learnerdddmergedid_staging AS l
                WHERE l.dataimportid = var_SourceDataImportIDMerged
                ORDER BY l.rownumber NULLS FIRST;
            /*
            [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
            RAISERROR('Merged: Source Rows: %d', 0, 0, @@ROWCOUNT) WITH NOWAIT;
            */
            /* IF @DebugMode >= 2 */
            /* BEGIN */
            /* SELECT '#ChangedData' '#ChangedData', * FROM #ChangedData; */
            /* SELECT '#MergedData' '#MergedData', * FROM #MergedData */
            /* END */
        END IF;

        SELECT
            COUNT(*)
            INTO var_RowCount
            FROM dbo.learner_staging
            WHERE dataimportid = par_SourceDataImportID AND ((var_DataSourceTypeID = 8 AND dddlearnerid IS NULL) OR (var_DataSourceTypeID = 14 AND cemislearnerid IS NULL) OR (var_DataSourceTypeID = 11 AND dddlearnerid IS NULL AND cemislearnerid IS NULL));
			
RAISE NOTICE 'var_RowCount: %', var_RowCount;                           
        -- RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %', '50000', 0, 0, 'Ignoring %I64d rows because DDDLearnerID and CEMISLearnerID is null', var_RowCount USING ERRCODE = '50000';
RAISE NOTICE 'CREATE TEMPORARY TABLE t$sourcedata'; 

		CREATE TEMPORARY TABLE t$sourcedata
        AS
        WITH t$sourcedata (year, sourcesystem, sourcelearnerid, datejoined, dateleft, rownumber, lastsubmissiondate, schoolname, emiscode, dddlearnerid, firstname, lastname, accessionnumber, idnumber, dateofbirth, gender, race, luritsnumber, citizenship, passportnumber, homelanguage, instructionlanguage, grade, class, classname, extid, extidtype, personid, idnumberinvalidreason, idnumberinvalid, cemislearnerid, interim, interm)
        AS (SELECT
            date_part('year', lastsubmissiondate),
            CASE
                WHEN (var_DataSourceTypeID IN (8)) OR (var_DataSourceTypeID = 11 AND dddlearnerid IS NOT NULL) THEN 'DDD'
                WHEN (var_DataSourceTypeID IN (14, 18)) OR (var_DataSourceTypeID = 11 AND cemislearnerid IS NOT NULL) THEN 'CEMIS'
                ELSE NULL
            END,
            CASE
                WHEN (var_DataSourceTypeID IN (8)) OR (var_DataSourceTypeID = 11 AND dddlearnerid IS NOT NULL) THEN CAST (dddlearnerid AS VARCHAR(30))
                WHEN (var_DataSourceTypeID IN (14, 18)) OR (var_DataSourceTypeID = 11 AND cemislearnerid IS NOT NULL) THEN cemislearnerid
                ELSE NULL
            END,
            CASE
                WHEN (var_DataSourceTypeID IN (8)) OR (var_DataSourceTypeID = 11 AND datejoined IS NOT NULL) THEN datejoined
                WHEN (var_DataSourceTypeID IN (14, 18) AND datejoined IS NOT NULL) THEN datejoined
                WHEN (var_DataSourceTypeID IN (14, 18) AND datejoined IS NULL) OR (var_DataSourceTypeID = 11 AND datejoined IS NULL) THEN make_date(var_SourceDataYear, 1, 1)
                ELSE NULL
            END, dateleft, rownumber,
            /* [LastSubmissionDate], */
            CASE
                WHEN lastsubmissiondate <= var_SourceDataDate THEN lastsubmissiondate
                ELSE var_LastFriday
            END AS lastsubmissiondate, schoolname, emiscode, dddlearnerid, firstname, lastname, accessionnumber, idnumber, dateofbirth, gender, race, luritsnumber, citizenship, passportnumber, homelanguage, instructionlanguage,
            CASE
                WHEN grade = 'Pre-R' THEN - 1
                WHEN grade = 'R' THEN 0
                ELSE CAST (grade AS SMALLINT)
            END AS grade, class, classname, extid, extidtype, personid, idnumberinvalidreason, idnumberinvalid, cemislearnerid, interim, interm
            FROM dbo.learner_staging AS l
            WHERE dataimportid = par_SourceDataImportID AND NOT ((var_DataSourceTypeID = 8 AND dddlearnerid IS NULL) OR (var_DataSourceTypeID IN (14, 18) AND cemislearnerid IS NULL) OR (var_DataSourceTypeID = 11 AND dddlearnerid IS NULL AND cemislearnerid IS NULL)))
        /* AND YEAR(LastSubmissionDate) = @DataYear */
        SELECT
            ROW_NUMBER() OVER (PARTITION BY sourcesystem, sourcelearnerid, x.emiscode, datejoined ORDER BY dateleft DESC, classname DESC, rownumber) AS dupseq, x.*, CAST (NULL AS INTEGER) AS mappedpersonid, CAST (NULL AS BIGINT) AS mappedschoollearnerid, s.schoolid,
            /* H.EntityID2 ProvinceEntityID, */
            COALESCE(eyt.startweekfriday, eytx.startweekfriday) AS term1startweekfriday,
            CASE
                WHEN LOWER(LEFT(x.emiscode, 1)) = LOWER('1') THEN COALESCE(eyt.startweekfriday, eytx.startweekfriday)
                ELSE (COALESCE(eyt.startweekfriday, eytx.startweekfriday))::TIMESTAMP + (CAST (dbo.fgetsystemparameter('Processing', 'Rollover', 'Usable Data Friday', 0, '4') AS SMALLINT) - 1::NUMERIC || ' WEEK')::INTERVAL
            END AS datausablefromdate
            FROM t$sourcedata AS x
            LEFT OUTER JOIN dbo.school AS s
                ON LOWER(x.emiscode) = LOWER(s.emiscode)
            LEFT OUTER JOIN dbo.vwentityhierarchyname AS h
                ON s.schoolid = h.schoolid AND h.entityid1 = 1
            LEFT OUTER JOIN dbo.entityyearterm AS eyt
                ON h.entityid2 = eyt.entityid AND eyt.year = var_CurrentYear AND eyt.term = 1 AND x.year = var_CurrentYear
            CROSS JOIN LATERAL (SELECT
                MIN(startweekfriday) AS startweekfriday
                FROM dbo.entityyearterm
                WHERE year = var_CurrentYear AND term = 1 AND x.year = var_CurrentYear) AS eytx;

GET DIAGNOSTICS var_RowCount = ROW_COUNT;
RAISE NOTICE 'Insert rowcount: %',var_RowCount;
-- 		CREATE INDEX IF NOT EXISTS ix_t$schoolslist_ix1 ON t$schoolslist
--                 (sourcelearnerid);

RAISE NOTICE ' DELETE FROM t$sourcedata';
        DELETE FROM t$sourcedata
            WHERE dupseq <> 1;
			
        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
        -- RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %', '50000', 0, 0, 'Ignoring %I64d rows because of duplicate key.', var_RowCount USING ERRCODE = '50000';
		
        CREATE INDEX ix_t$sourcedata_ix1 ON t$sourcedata
            (year);
		CREATE INDEX ix_t$sourcedata_ix2 ON t$sourcedata
            (mappedpersonid,sourcelearnerid);
        /* IF @DataSourceTypeID IN (8) */

        BEGIN
            DELETE FROM t$sourcedata
                WHERE Year NOT IN (var_CurrentYear, var_PreviousYear);
            GET DIAGNOSTICS var_RowCount = ROW_COUNT;
            -- RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %', '50000', 0, 0, 'Removing all invalid year data: %I64d', var_RowCount USING ERRCODE = '50000';
            DELETE FROM t$sourcedata
                WHERE lastsubmissiondate < datausablefromdate AND Year = var_CurrentYear;
            GET DIAGNOSTICS var_RowCount = ROW_COUNT;
            -- RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %. Argument: %', '50000', 0, 0, 'Removing %d data prior to usable date: %I64d', var_CurrentYear, var_RowCount USING ERRCODE = '50000';
           
		   SELECT
                COUNT(*)
                INTO var_RowCount
                FROM t$sourcedata
                WHERE Year = var_CurrentYear;
				
            -- RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %. Argument: %', '50000', 0, 0, '%d year data: %I64d', var_CurrentYear, var_RowCount USING ERRCODE = '50000';
RAISE NOTICE 'var_RowCount1: %', var_RowCount;   

            SELECT
                COUNT(*)
                INTO var_RowCount
                FROM t$sourcedata
                WHERE Year = var_PreviousYear;
				
            -- RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %. Argument: %', '50000', 0, 0, '%d year data: %I64d', var_PreviousYear, var_RowCount USING ERRCODE = '50000';
RAISE NOTICE 'var_RowCount2: %', var_RowCount;  
            INSERT INTO internal.processingschool (schoolid, item, timestamp)
            SELECT
                x.schoolid, 'Year First Usable Data', CURRENT_TIMESTAMP(3)
                FROM (SELECT DISTINCT
                    x.schoolid
                    FROM t$sourcedata AS x
                    WHERE Year = var_CurrentYear) AS x
                CROSS JOIN LATERAL (SELECT
                    COUNT(*) AS count
                    FROM dbo.sourcelearner AS sl
                    WHERE sl.schoolid = x.schoolid AND sl.year = var_CurrentYear) AS sl
                LEFT OUTER JOIN internal.processingschool AS p
                    ON x.schoolid = p.schoolid AND LOWER(p.item) = LOWER('Year First Usable Data') AND date_part('year', p.timestamp) = var_CurrentYear
                WHERE sl.Count = 0 AND p.schoolid IS NULL;
            -- RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %', '50000', 0, 0, 'Year First Usable Data: %d', "var_@ROWCOUNT" USING ERRCODE = '50000';
			
RAISE NOTICE 'processingschool1: %', var_RowCount; 

            INSERT INTO internal.processingschool (schoolid, item, timestamp)
            SELECT
                x.schoolid, 'Year First Usable Data - Update', CURRENT_TIMESTAMP(3)
                FROM (SELECT DISTINCT
                    x.schoolid
                    FROM t$sourcedata AS x
                    WHERE Year = var_CurrentYear) AS x
                CROSS JOIN LATERAL (SELECT
                    COUNT(*) AS count
                    FROM dbo.sourcelearner AS sl
                    WHERE sl.schoolid = x.schoolid AND sl.year = var_CurrentYear) AS sl
                CROSS JOIN LATERAL (SELECT
                    COUNT(*) AS count
                    FROM dbo.sourcelearner AS sl
                    WHERE sl.schoolid = x.schoolid AND sl.year = var_PreviousYear) AS slp
                LEFT OUTER JOIN internal.processingschool AS p
                    ON x.schoolid = p.schoolid AND LOWER(p.item) = LOWER('Year First Usable Data - Update') AND date_part('year', p.timestamp) = var_CurrentYear
                WHERE sl.Count = 0 AND slp.count = 0 AND p.schoolid IS NULL;
            -- RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %', '50000', 0, 0, 'Year First Usable Data - Update: %d', "var_@ROWCOUNT" USING ERRCODE = '50000';
			
RAISE NOTICE 'processingschool2: %', var_RowCount;   

            INSERT INTO internal.processingschool (schoolid, item, timestamp)
            SELECT
                x.schoolid, 'Year First Usable Data - Recalc Usage', CURRENT_TIMESTAMP(3)
                FROM (SELECT DISTINCT
                    x.schoolid
                    FROM t$sourcedata AS x
                    WHERE Year = var_CurrentYear) AS x
                CROSS JOIN LATERAL (SELECT
                    COUNT(*) AS count
                    FROM dbo.sourcelearner AS sl
                    WHERE sl.schoolid = x.schoolid AND sl.year = var_CurrentYear) AS sl
                CROSS JOIN LATERAL (SELECT
                    COUNT(*) AS count
                    FROM dbo.sourcelearner AS sl
                    WHERE sl.schoolid = x.schoolid AND sl.year = var_PreviousYear) AS slp
                LEFT OUTER JOIN internal.processingschool AS p
                    ON x.schoolid = p.schoolid AND LOWER(p.item) = LOWER('Year First Usable Data - Recalc Usage') AND date_part('year', p.timestamp) = var_CurrentYear
                WHERE sl.Count = 0 AND slp.count = 0 AND p.schoolid IS NULL;
            -- RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %', '50000', 0, 0, 'Year First Usable Data - Recalc Usage: %d', "var_@ROWCOUNT" USING ERRCODE = '50000';
			
RAISE NOTICE 'processingschool3: %', var_RowCount;      

        END;
        
        SELECT
            COUNT(*)
            INTO var_SourceRowCount
            FROM dbo.learner_staging
            WHERE dataimportid = par_SourceDataImportID;
			
        -- RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %. Argument: %. Argument: %. Argument: %. Argument: %', '50000', 0, 0, 'Processing ''%s'' source: ''%s''[%I64d] for %s [%I64d rows]', var_SourceDataType, var_SourceDataDescription, par_SourceDataImportID, var_SourceDataDateS, var_SourceRowCount USING ERRCODE = '50000';
RAISE NOTICE '%: var_SourceRowCount: %', timeofday(), var_SourceRowCount;

		/* SELECT * FROM #SourceData; */
        /* RETURN */
        /* UPDATE S SET MappedPersonID = ID.PersonID, MappedSchoolLearnerID = SL.SchoolLearnerID */
        /* FROM #SourceData S */
        /* JOIN PersonExternalID ID ON ID.PersonExternalIDType = CASE WHEN S.SourceSystem = 'DDD' THEN 9 WHEN S.SourceSystem = 'CEMIS' THEN 6 END AND S.SourceLearnerID = ID.ExternalID */
        /* CROSS APPLY (SELECT TOP 1 * FROM SchoolLearner SL WHERE ID.PersonID = SL.PersonID AND S.SchoolID = SL.SchoolID AND S.DateLeft IS NOT NULL ORDER BY SL.StartDate DESC, SL.EndDate DESC) SL */
        /* --LEFT JOIN SchoolLearner SL ON ID.personID = SL.PersonID AND S.SchoolID = SL.SchoolID AND S.DateJoined = SL.StartDate */
        /* WHERE @UpdatePersonID IN (1,2) AND S.MappedPersonID IS NULL */
        /* RAISERROR('Set MappedPersonID, MappedSchoolLearnerID from PersonExternalID,SchoolLearner(1): %d', 0, 0, @@ROWCOUNT) WITH NOWAIT; */

RAISE NOTICE ' UPDATE t$sourcedata';
--       UPDATE t$sourcedata AS s
--         SET mappedpersonid = id.personid, mappedschoollearnerid = sl.schoollearnerid
--         FROM t$sourcedata
--         JOIN dbo.personexternalid AS id
--             ON id.personexternalidtype =
--             CASE
--                 WHEN t$sourcedata.sourcesystem = 'DDD' THEN 9
--                 WHEN t$sourcedata.sourcesystem = 'CEMIS' THEN 6
--             END AND t$sourcedata.sourcelearnerid = id.externalid::VARCHAR
-- 			JOIN (SELECT distinct
-- 				*
-- 				FROM dbo.schoollearner AS sl
-- 				--WHERE id.personid = sl.personid AND t$sourcedata.schoolid = sl.schoolid AND t$sourcedata.datejoined <= sl.startdate
-- 				ORDER BY sl.enddate DESC NULLS FIRST
-- 				) AS sl on id.personid = sl.personid AND t$sourcedata.schoolid = sl.schoolid AND t$sourcedata.datejoined <= sl.startdate
--             /* LEFT JOIN SchoolLearner SL ON ID.personID = SL.PersonID AND S.SchoolID = SL.SchoolID AND S.DateJoined = SL.StartDate */
--             WHERE par_UpdatePersonID IN (1, 2) AND t$sourcedata.mappedpersonid IS NULL and s.rownumber = t$sourcedata.rownumber;

			
UPDATE t$sourcedata s  
	SET mappedpersonid = id.personid, mappedschoollearnerid = sl.schoollearnerid
		FROM dbo.personexternalid id 
		join (SELECT distinct *           
            FROM dbo.schoollearner AS sl
            ORDER BY sl.enddate DESC NULLS FIRST
            )sl ON   id.personid = sl.personid 		
			
		WHERE par_UpdatePersonID IN (1,2) AND s.MappedPersonID IS NULL 
			AND id.personexternalidtype =
            CASE
                WHEN s.sourcesystem = 'DDD' THEN 9
                WHEN s.sourcesystem = 'CEMIS' THEN 6
            END 
			AND s.sourcelearnerid = id.externalid::VARCHAR
			AND s.schoolid = sl.schoolid 
			AND s.datejoined <= sl.startdate
			;

		
		
        --RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %', '50000', 0, 0, 'Set MappedPersonID, MappedSchoolLearnerID from PersonExternalID,SchoolLearner(2): %d', "var_@ROWCOUNT" USING ERRCODE = '50000';
get diagnostics update_diag = row_count;
RAISE NOTICE '%: Update1: %', timeofday(), update_diag;

        /* SELECT * FROM #SourceData WHERE DDDLearnerID = 26411949 */
--         UPDATE t$sourcedata AS s
--         SET mappedpersonid = id.mappedpersonid, mappedschoollearnerid = id.mappedschoollearnerid
--         FROM t$sourcedata
--         CROSS JOIN LATERAL (SELECT
--             mappedpersonid, mappedschoollearnerid
--             FROM dbo.sourcelearner
--             WHERE sourcelearnerid = t$sourcedata.sourcelearnerid AND LOWER(emiscode) = LOWER(t$sourcedata.emiscode) AND datejoined = t$sourcedata.datejoined
--             ORDER BY sysendtime DESC NULLS FIRST
--             LIMIT 1) AS id
--             WHERE par_UpdatePersonID IN (1, 2) AND t$sourcedata.mappedpersonid IS NULL and s.rownumber = t$sourcedata.rownumber;

		UPDATE t$sourcedata AS s
        SET mappedpersonid = id.mappedpersonid, mappedschoollearnerid = id.mappedschoollearnerid
        FROM 
        (SELECT DISTINCT 
           sourcelearnerid,emiscode, mappedpersonid,datejoined, mappedschoollearnerid,sysendtime
            FROM dbo.sourcelearner 
            ORDER BY sysendtime DESC NULLS FIRST
            ) AS id
            WHERE par_UpdatePersonID IN (1, 2) AND s.mappedpersonid IS NULL AND id.sourcelearnerid = s.sourcelearnerid AND LOWER(id.emiscode) = LOWER(s.emiscode) AND id.datejoined = s.datejoined;

get diagnostics update_diag = row_count;
RAISE NOTICE '%: Update2: %', timeofday(), update_diag;
        -- RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %', '50000', 0, 0, 'Set MappedPersonID, MappedSchoolLearnerID from SourceLearner(1): %d', "var_@ROWCOUNT" USING ERRCODE = '50000';
        /* SELECT * FROM #SourceData WHERE DDDLearnerID = 26411949 */

--         UPDATE t$sourcedata AS s
--         SET mappedpersonid = id.mappedpersonid, mappedschoollearnerid = id.mappedschoollearnerid
--         FROM t$sourcedata
--         CROSS JOIN LATERAL (SELECT
--             mappedpersonid, mappedschoollearnerid
--             FROM dbo.sourcelearner
--             WHERE sourcelearnerid = t$sourcedata.sourcelearnerid AND LOWER(emiscode) = LOWER(t$sourcedata.emiscode) AND ((year = Year) OR (year = Year - 1 AND t$sourcedata.dateleft IS NULL))
--             ORDER BY sysendtime DESC NULLS FIRST
--             LIMIT 1) AS id
--             WHERE par_UpdatePersonID IN (1, 2) AND t$sourcedata.mappedpersonid IS NULL and s.rownumber = t$sourcedata.rownumber;

		UPDATE t$sourcedata AS s
        SET mappedpersonid = id.mappedpersonid, mappedschoollearnerid = id.mappedschoollearnerid
        FROM 
         (SELECT DISTINCT
            sourcelearnerid,emiscode,mappedpersonid,year, mappedschoollearnerid,sysendtime
            FROM dbo.sourcelearner          
            ORDER BY sysendtime DESC NULLS FIRST
            ) AS id
            WHERE par_UpdatePersonID IN (1, 2) AND s.mappedpersonid IS NULL AND  id.sourcelearnerid = s.sourcelearnerid AND LOWER(id.emiscode) = LOWER(s.emiscode) AND ((id.year = s.Year) OR (id.year = s.Year - 1 AND s.dateleft IS NULL));

        -- RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %', '50000', 0, 0, 'Set MappedPersonID, MappedSchoolLearnerID from SourceLearner(2): %d', "var_@ROWCOUNT" USING ERRCODE = '50000';
        /* SELECT * FROM #SourceData WHERE DDDLearnerID = 26411949 */

get diagnostics update_diag = row_count;
RAISE NOTICE '%: Update3: %', timeofday(), update_diag;
        DELETE FROM t$sourcedata
            WHERE mappedschoollearnerid IS NULL AND Year = var_PreviousYear;
        -- RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %', '50000', 0, 0, 'Delete previous year unmapped data: %d', "var_@ROWCOUNT" USING ERRCODE = '50000'
        /* SELECT * FROM #SourceData WHERE DDDLearnerID = 26411949 */
        -- ;

get diagnostics update_diag = row_count;
RAISE NOTICE '%: Delete1: %', timeofday(), update_diag;
return;-- Testing
		CREATE TEMP TABLE t$schoolmatch AS  
		SELECT
		s.rownumber, t.year, s.sourcesystem, s.sourcelearnerid, s.emiscode, s.datejoined
		FROM t$sourcedata AS s
		CROSS JOIN LATERAL (SELECT
			year, emiscode, datejoined
			FROM dbo.sourcelearner AS t
			WHERE s.sourcesystem = t.sourcesystem::VARCHAR AND s.sourcelearnerid = t.sourcelearnerid::VARCHAR AND s.emiscode = t.emiscode AND s.datejoined = t.datejoined
			ORDER BY year DESC NULLS FIRST, datejoined DESC NULLS FIRST
			LIMIT 1) AS t;

		CREATE TEMP TABLE t$rownumbers AS
		SELECT rownumber FROM t$schoolmatch;
		
		CREATE TEMP TABLE t$otherschoolmatch AS 	
		SELECT
		s.rownumber, t.year, s.sourcesystem, s.sourcelearnerid, t.emiscode, t.datejoined
		FROM t$sourcedata AS s
		LEFT JOIN t$rownumbers tn on tn.rownumber = s.rownumber
		CROSS JOIN LATERAL (SELECT
			year, emiscode, datejoined
			FROM dbo.sourcelearner AS t
			WHERE s.sourcesystem = t.sourcesystem::VARCHAR AND s.sourcelearnerid = t.sourcelearnerid::VARCHAR
			ORDER BY year DESC NULLS FIRST, datejoined DESC NULLS FIRST
			LIMIT 1) AS t
		WHERE tn.rownumber is null AND s.sourcesystem = 'CEMIS';

		CREATE TEMP TABLE t$matches AS 	
		SELECT DISTINCT
		rownumber, year, sourcesystem, sourcelearnerid, emiscode, datejoined
		FROM (SELECT
			rownumber, year, sourcesystem, sourcelearnerid, emiscode, datejoined
			FROM t$schoolmatch
		UNION
		SELECT
			rownumber, year, sourcesystem, sourcelearnerid, emiscode, datejoined
			FROM t$otherschoolmatch) AS x;

		CREATE TEMPORARY TABLE t$sourcedata2
        AS        
        SELECT
            s.Year AS year, s.sourcesystem AS sourcesystem, s.sourcelearnerid AS sourcelearnerid, s.emiscode AS emiscode, s.datejoined AS datejoined,
            CASE
                WHEN t.dateleft IS NOT NULL AND t.dateleftsetreason IS NOT NULL THEN t.dateleft
                ELSE s.dateleft
            END AS dateleft,
            /* S.DateLeft                       DateLeft, */
            s.lastsubmissiondate AS lastsubmissiondate,
            CASE
                WHEN s.schoolname IS NULL THEN ss.name
                ELSE s.schoolname
            END AS schoolname,
            CASE
                WHEN s.firstname IS NULL THEN t.firstname
                ELSE s.firstname
            END AS firstname,
            CASE
                WHEN s.lastname IS NULL THEN t.lastname
                ELSE s.lastname
            END AS lastname,
            CASE
                WHEN s.dateofbirth IS NULL THEN t.dateofbirth
                ELSE s.dateofbirth
            END AS dateofbirth,
            CASE
                WHEN s.gender IS NULL THEN t.gender
                ELSE s.gender
            END AS gender,
            CASE
                WHEN s.race IS NULL THEN t.race
                ELSE s.race
            END AS race,
            CASE
                WHEN s.homelanguage IS NULL THEN t.homelanguage
                ELSE s.homelanguage
            END AS homelanguage,
            CASE
                WHEN s.instructionlanguage IS NULL THEN t.instructionlanguage
                ELSE s.instructionlanguage
            END AS instructionlanguage,
            CASE
                WHEN s.citizenship IS NULL THEN t.citizenship
                ELSE s.citizenship
            END AS citizenship, s.grade AS grade, s.class AS class, s.classname AS classname,
            CASE
                WHEN s.accessionnumber IS NULL THEN (CASE
                    WHEN LOWER(s.emiscode) = LOWER(t.emiscode) THEN t.accessionnumber
                    ELSE NULL
                END)
                ELSE s.accessionnumber
            END AS accessionnumber,
            CASE
                WHEN s.luritsnumber IS NULL THEN t.luritsnumber
                ELSE s.luritsnumber
            END AS luritsnumber,
            CASE
                WHEN s.passportnumber IS NULL THEN t.passportnumber
                ELSE s.passportnumber
            END AS passportnumber,
            CASE
                WHEN s.idnumber IS NULL THEN t.idnumber
                ELSE s.idnumber
            END AS idnumber,
            CASE
                WHEN s.idnumber IS NULL THEN s.idnumberinvalid
                ELSE t.idnumberinvalid
            END AS idnumberinvalid,
            CASE
                WHEN s.idnumber IS NULL THEN s.idnumberinvalidreason
                ELSE t.idnumberinvalidreason
            END AS idnumberinvalidreason, par_SourceDataImportID AS sourcedataimportid, s.rownumber AS sourcerownumber, var_SourceDataDate AS sourcedatadate, s.dddlearnerid AS dddlearnerid, s.cemislearnerid AS cemislearnerid,
            CASE
                WHEN par_UpdatePersonID = 0 THEN t.mappedpersonid
                WHEN par_UpdatePersonID = 1 AND s.mappedpersonid IS NOT NULL THEN s.mappedpersonid
            /* WHEN @UpdatePersonID = 1 AND S.MappedPersonID */
                WHEN par_UpdatePersonID = 2 AND t.mappedpersonid IS NULL THEN s.mappedpersonid
                ELSE t.mappedpersonid
            END AS mappedpersonid,
            CASE
                WHEN par_UpdatePersonID = 0 THEN t.mappedschoollearnerid
                WHEN par_UpdatePersonID = 1 AND s.mappedpersonid IS NOT NULL THEN s.mappedschoollearnerid
            /* WHEN @UpdatePersonID = 1 AND S.MappedPersonID */
                WHEN par_UpdatePersonID = 2 AND t.mappedpersonid IS NULL THEN s.mappedschoollearnerid
                WHEN s.schoolid <> t.schoolid THEN NULL
                WHEN s.mappedschoollearnerid <> t.mappedschoollearnerid THEN s.mappedschoollearnerid
                ELSE t.mappedschoollearnerid
            END AS mappedschoollearnerid, s.schoolid, var_LastUpdated AS lastupdate, s.interim, s.interm, t.dateleftsetreason
            FROM t$sourcedata AS s
            LEFT OUTER JOIN dbo.school AS ss
                ON LOWER(s.emiscode) = LOWER(ss.emiscode)
            LEFT OUTER JOIN t$matches AS m
                ON s.rownumber = m.rownumber
            LEFT OUTER JOIN dbo.sourcelearner AS t
                ON m.year = t.year AND m.sourcesystem = t.sourcesystem::VARCHAR AND m.sourcelearnerid = t.sourcelearnerid::VARCHAR AND LOWER(m.emiscode) = LOWER(t.emiscode) AND m.datejoined = t.datejoined;
        -- RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %', '50000', 0, 0, '#SourceData2: %d', "var_@ROWCOUNT" USING ERRCODE = '50000';
		DROP TABLE IF EXISTS t$schoolmatch;
		DROP TABLE IF EXISTS t$otherschoolmatch;
		DROP TABLE IF EXISTS t$matches;	
get diagnostics update_diag = row_count;		
RAISE NOTICE '%: t$sourcedata2: %', timeofday(), update_diag;   

        /* SELECT * FROM #SourceData2 WHERE DDDLearnerID = 26411949 */
        
        /*
        [7927 - Severity CRITICAL - PostgreSQL doesn't support OUTER joins for self-reference to the label from UPDATE / DELETE in the FROM clause and without PK on table-for-update. Perform a manual conversion.]
        UPDATE S SET
                    DateLeft = ISNULL(EYT1.EndDate,EYT.StartDate),
                    DateLeftSetReason = 'InTerm'
                FROM #SourceData2 S
                CROSS APPLY (SELECT TOP 1 * FROM vwSchoolEnvironmentProgramme SEP WHERE SEP.SchoolID = S.SchoolID AND SEP.ImplementationStatusID IN (4,13)) SEP
                JOIN EntityHierarchy H ON S.SchoolID = H.SchoolID AND H.EntityID1 = 1
                JOIN EntityYearTerm EYT ON H.EntityID2 = EYT.EntityID AND S.LastSubmissionDate BETWEEN EYT.StartDate AND EYT.EndWeekFriday
                LEFT JOIN EntityYearTerm EYT1 ON H.EntityID2 = EYT1.EntityID AND EYT.[Year] = EYT1.[Year] AND EYT.Term - 1 = EYT1.Term
                WHERE S.InTerm = 0 AND S.DateLeft IS NULL AND S.DateLeftSetReason IS NULL
                    AND SEP.ProgrammeManagementLevelID = 1
        */

        UPDATE t$sourcedata2 TS SET
                    DateLeft = COALESCE(EYT1.EndDate,EYT.StartDate),
                    DateLeftSetReason = 'InTerm'
                FROM t$sourcedata2 AS S
                CROSS JOIN LATERAL (SELECT * FROM dbo.vwSchoolEnvironmentProgramme AS SEP 
                                WHERE SEP.SchoolID = S.SchoolID AND SEP.ImplementationStatusID IN (4,13)
                                limit 1
                           ) AS SEP
                JOIN dbo.EntityHierarchy AS H ON S.SchoolID = H.SchoolID AND H.EntityID1 = 1
                JOIN dbo.EntityYearTerm AS EYT ON H.EntityID2 = EYT.EntityID AND S.LastSubmissionDate BETWEEN EYT.StartDate AND EYT.EndWeekFriday
                LEFT JOIN dbo.EntityYearTerm AS EYT1 ON H.EntityID2 = EYT1.EntityID AND EYT.Year = EYT1.Year AND EYT.Term - 1 = EYT1.Term
                WHERE S.InTerm::int = 0 AND S.DateLeft IS NULL AND S.DateLeftSetReason IS NULL
                    AND SEP.ProgrammeManagementLevelID = 1 and S.sourcelearnerid = TS.sourcelearnerid;

get diagnostics update_diag = row_count;		
RAISE NOTICE '%: t$sourcedata2a: %', timeofday(), update_diag;   
        -- RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %', '50000', 0, 0, '#SourceData2 InTerm=0 DateLeft Set: %d', "var_@ROWCOUNT" USING ERRCODE = '50000';
        /*
        [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
        UPDATE T SET
                    DateLeft                        = S.DateLeft,
                    LastSubmissionDate              = S.LastSubmissionDate,
                    SchoolName                      = S.SchoolName,
                    FirstName                       = S.FirstName,
                    LastName                        = S.LastName,
                    DateOfBirth                     = S.DateOfBirth,
                    Gender                          = S.Gender,
                    Race                            = S.Race,
                    HomeLanguage                    = S.HomeLanguage,
                    InstructionLanguage             = S.InstructionLanguage,
                    Citizenship                     = S.Citizenship,
                    Grade                           = S.Grade,
                    Class                           = S.Class,
                    ClassName                       = S.ClassName,
                    AccessionNumber                 = S.AccessionNumber,
                    LuritsNumber                    = S.LuritsNumber,
                    PassportNumber                  = S.PassportNumber,
                    IDNumber                        = S.IDNumber,
                    IDNumberInvalid                 = S.IDNumberInvalid,
                    IDNumberInvalidReason           = S.IDNumberInvalidReason,
                    SourceDataImportID              = S.SourceDataImportID,
                    SourceRowNumber                 = S.SourceRowNumber,
                    SourceDataDate                  = S.SourceDataDate,
                    DDDLearnerID                    = S.DDDLearnerID,
                    CEMISLearnerID                  = S.CEMISLearnerID,
                    MappedPersonID                  = S.MappedPersonID,
                    MappedSchoolLearnerID           = S.MappedSchoolLearnerID,
                    LastUpdate                      = S.LastUpdate,
                    Interim                         = S.Interim,
                    InTerm                          = S.InTerm,
                    DateLeftSetReason               = S.DateLeftSetReason
                FROM dbo.SourceLearner T
                JOIN #SourceData2 S ON T.SourceSystem = S.SourceSystem AND T.SourceLearnerID = S.SourceLearnerID AND T.EMISCode = S.EMISCode AND T.DateJoined = S.DateJoined
                WHERE T.[Year] = S.[Year] AND S.LastSubmissionDate >= T.LastSubmissionDate
                    AND @CurrentYear = T.[Year]
        */

UPDATE dbo.SourceLearner T SET
                    DateLeft                        = S.DateLeft,
                    LastSubmissionDate              = S.LastSubmissionDate,
                    SchoolName                      = S.SchoolName,
                    FirstName                       = S.FirstName,
                    LastName                        = S.LastName,
                    DateOfBirth                     = S.DateOfBirth,
                    Gender                          = S.Gender,
                    Race                            = S.Race,
                    HomeLanguage                    = S.HomeLanguage,
                    InstructionLanguage             = S.InstructionLanguage,
                    Citizenship                     = S.Citizenship,
                    Grade                           = S.Grade,
                    Class                           = S.Class::int,
                    ClassName                       = S.ClassName,
                    AccessionNumber                 = S.AccessionNumber,
                    LuritsNumber                    = S.LuritsNumber,
                    PassportNumber                  = S.PassportNumber,
                    IDNumber                        = S.IDNumber,
                    IDNumberInvalid                 = S.IDNumberInvalid,
                    IDNumberInvalidReason           = S.IDNumberInvalidReason,
                    SourceDataImportID              = S.SourceDataImportID,
                    SourceRowNumber                 = S.SourceRowNumber,
                    SourceDataDate                  = S.SourceDataDate::timestamp without time zone,
                    DDDLearnerID                    = S.DDDLearnerID,
                    CEMISLearnerID                  = S.CEMISLearnerID,
                    MappedPersonID                  = S.MappedPersonID,
                    MappedSchoolLearnerID           = S.MappedSchoolLearnerID,
                    LastUpdate                      = S.LastUpdate::timestamp without time zone,
                    Interim                         = S.Interim,
                    InTerm                          = S.InTerm,
                    DateLeftSetReason               = S.DateLeftSetReason
--                 FROM dbo.SourceLearner T
                FROM  t$sourcedata2 S 
-- 				ON T.SourceSystem = S.SourceSystem 
--                     AND T.SourceLearnerID = S.SourceLearnerID 
--                     AND T.EMISCode = S.EMISCode 
--                     AND T.DateJoined = S.DateJoined
                WHERE T.Year = S.Year 
                    AND S.LastSubmissionDate >= T.LastSubmissionDate
                    AND var_CurrentYear = T.Year
					AND T.SourceSystem = S.SourceSystem 
                    AND T.SourceLearnerID = S.SourceLearnerID 
                    AND T.EMISCode = S.EMISCode 
                    AND T.DateJoined = S.DateJoined;

get diagnostics update_diag = row_count;		
RAISE NOTICE '%: t$sourcedata2b: %', timeofday(), update_diag;   
        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
		RAISE NOTICE 'var_rows: %',var_RowCount;
		
        -- RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %. Argument: %', '50000', 0, 0, 'SourceLearner %d Updated: %I64d rows', var_CurrentYear, var_RowCount USING ERRCODE = '50000';
        /*
        [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
        UPDATE T SET
                    DateLeft                        = S.DateLeft,
                    LastSubmissionDate              = S.LastSubmissionDate,
                    SourceDataImportID              = S.SourceDataImportID,
                    SourceRowNumber                 = S.SourceRowNumber,
                    SourceDataDate                  = S.SourceDataDate,
                    LastUpdate                      = S.LastUpdate
                FROM dbo.SourceLearner T
                JOIN #SourceData2 S ON T.SourceSystem = S.SourceSystem AND T.SourceLearnerID = S.SourceLearnerID AND T.EMISCode = S.EMISCode AND T.DateJoined = S.DateJoined
                WHERE T.[Year] = S.[Year] AND S.LastSubmissionDate >= T.LastSubmissionDate
                    AND @PreviousYear = T.[Year]
                    AND ((T.DateLeft IS NOT NULL AND S.DateLeft IS NULL) OR (T.DateLeft IS NULL AND S.DateLeft IS NOT NULL) OR (T.DateLeft <> S.DateLeft))
        */
        UPDATE dbo.SourceLearner T SET
                    DateLeft                        = S.DateLeft,
                    LastSubmissionDate              = S.LastSubmissionDate,
                    SourceDataImportID              = S.SourceDataImportID,
                    SourceRowNumber                 = S.SourceRowNumber,
                    SourceDataDate                  = S.SourceDataDate::timestamp without time zone,
                    LastUpdate                      = S.LastUpdate::timestamp without time zone
--                 FROM dbo.SourceLearner T
                FROM t$sourcedata2 S 
-- 					ON T.SourceSystem = S.SourceSystem 
--                     AND T.SourceLearnerID = S.SourceLearnerID 
--                     AND T.EMISCode = S.EMISCode 
--                     AND T.DateJoined = S.DateJoined
                WHERE T.Year = S.Year AND S.LastSubmissionDate >= T.LastSubmissionDate
                    AND var_PreviousYear = T.Year
                    AND ((T.DateLeft IS NOT NULL AND S.DateLeft IS NULL) 
                            OR (T.DateLeft IS NULL AND S.DateLeft IS NOT NULL) 
                            OR (T.DateLeft <> S.DateLeft))
					AND T.SourceSystem = S.SourceSystem 
                    AND T.SourceLearnerID = S.SourceLearnerID 
                    AND T.EMISCode = S.EMISCode 
                    AND T.DateJoined = S.DateJoined; 

get diagnostics update_diag = row_count;		
RAISE NOTICE '%: t$sourcedata2c: %', timeofday(), update_diag;   
        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
		RAISE NOTICE 'var_rows: %',var_RowCount;
		
        -- RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %. Argument: %', '50000', 0, 0, 'SourceLearner %d Updated: %I64d rows', var_PreviousYear, var_RowCount USING ERRCODE = '50000';
/*        
        INSERT INTO dbo.sourcelearner (year, sourcesystem, sourcelearnerid, emiscode, datejoined, dateleft, lastsubmissiondate, schoolname, firstname, lastname, dateofbirth, gender, race, homelanguage, instructionlanguage, citizenship, grade, class, classname, accessionnumber, luritsnumber, passportnumber, idnumber, idnumberinvalid, idnumberinvalidreason, sourcedataimportid, sourcerownumber, sourcedatadate, dddlearnerid, cemislearnerid, mappedpersonid, lastupdate, mappedschoollearnerid, schoolid, interim, interm, dateleftsetreason)
        SELECT
            Year, s.sourcesystem, s.sourcelearnerid, s.emiscode, s.datejoined, s.dateleft, s.lastsubmissiondate, s.schoolname, s.firstname, s.lastname, s.dateofbirth, s.gender, s.race, s.homelanguage, s.instructionlanguage, s.citizenship, s.grade, s.class, s.classname, s.accessionnumber, s.luritsnumber, s.passportnumber, s.idnumber, s.idnumberinvalid, s.idnumberinvalidreason, s.sourcedataimportid, s.sourcerownumber, s.sourcedatadate, s.dddlearnerid, s.cemislearnerid, s.mappedpersonid, s.lastupdate, s.mappedschoollearnerid, s.schoolid, s.interim, s.interm, s.dateleftsetreason
            FROM t$sourcedata2 AS s
            LEFT OUTER JOIN dbo.sourcelearner AS t
                ON t.sourcesystem = s.sourcesystem AND t.sourcelearnerid = s.sourcelearnerid AND LOWER(t.emiscode) = LOWER(s.emiscode) AND t.datejoined = s.datejoined AND t.year = Year
            WHERE t.year IS NULL AND Year = var_CurrentYear;
*/

        INSERT INTO dbo.sourcelearner (
            year, sourcesystem, sourcelearnerid, emiscode, datejoined, dateleft, lastsubmissiondate, schoolname, 
            firstname, lastname, dateofbirth, gender, race, homelanguage, instructionlanguage, citizenship, grade, 
            class, classname, accessionnumber, luritsnumber, passportnumber, idnumber, idnumberinvalid, idnumberinvalidreason, 
            sourcedataimportid, sourcerownumber, sourcedatadate, dddlearnerid, cemislearnerid, mappedpersonid, 
            lastupdate, mappedschoollearnerid, schoolid, interim, interm, dateleftsetreason,sysstarttime,sysendtime)
        SELECT
            s.Year::int, s.sourcesystem, s.sourcelearnerid, s.emiscode, s.datejoined, s.dateleft, s.lastsubmissiondate, 
            s.schoolname, s.firstname, s.lastname, s.dateofbirth, s.gender, s.race, s.homelanguage, s.instructionlanguage, 
            s.citizenship, s.grade, s.class::int, s.classname, s.accessionnumber, s.luritsnumber, s.passportnumber, 
            s.idnumber, s.idnumberinvalid, s.idnumberinvalidreason, s.sourcedataimportid, s.sourcerownumber, s.sourcedatadate::timestamp without time zone, 
            s.dddlearnerid, s.cemislearnerid, s.mappedpersonid, s.lastupdate::timestamp without time zone, s.mappedschoollearnerid, s.schoolid, s.interim, 
            s.interm, s.dateleftsetreason,MAKE_DATE(EXTRACT(YEAR FROM s.DateJoined)::int,1,1),'9999-12-31 23:59:59'::timestamp without time zone
            FROM t$sourcedata2 AS s
            LEFT OUTER JOIN dbo.sourcelearner AS t
                ON t.sourcesystem = s.sourcesystem 
                AND t.sourcelearnerid = s.sourcelearnerid 
                AND LOWER(t.emiscode) = LOWER(s.emiscode) 
                AND t.datejoined = s.datejoined 
                AND t.year = s.Year
            WHERE t.year IS NULL AND s.Year = var_CurrentYear;

        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
        -- RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %. Argument: %', '50000', 0, 0, 'SourceLearner %d Inserted: %I64d rows', var_CurrentYear, var_RowCount USING ERRCODE = '50000';

get diagnostics update_diag = row_count;
RAISE NOTICE '%: t$sourcelearner: %', timeofday(), update_diag;   
        /*
        [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
        UPDATE SL1 SET DateLeft = SL.DateLeft, DateLeftSetReason = SL.DateLeftSetReason
                FROM SourceLearner SL
                JOIN SourceLearner SL1 ON SL.SourceSYstem = SL1.SourceSystem AND SL.EMISCode = SL1.EMISCode AND SL.[Year] = SL1.[Year]+1 AND SL.SOurceLearnerID = SL1.SourceLearnerID AND SL.DateJoined = SL1.DateJoined
                WHERE SL.DateLeft IS NOT NULL AND SL.Year = @CurrentYear AND YEAR(SL.DateLeft) = SL.Year - 1
                    AND SL1.DateLeft IS NULL
        */
      	RAISE NOTICE 'Updating Source Learner Table';
        UPDATE dbo.SourceLearner SS 
        SET DateLeft = SL.DateLeft, 
            DateLeftSetReason = SL.DateLeftSetReason
        FROM dbo.SourceLearner SL
        JOIN dbo.SourceLearner SL1 ON SL.SourceSYstem = SL1.SourceSystem AND SL.EMISCode = SL1.EMISCode 
            AND SL.Year = SL1.Year+1 AND SL.SourceLearnerID = SL1.SourceLearnerID AND SL.DateJoined = SL1.DateJoined
        WHERE SL.DateLeft IS NOT NULL AND SL.Year = var_CurrentYear AND EXTRACT (YEAR FROM SL.DateLeft) = SL.Year - 1
            AND SL1.DateLeft IS NULL AND SL.SourceLearnerID = SS.SourceLearnerID AND SL.DateJoined = SS.DateJoined;
         
        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
        -- RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %', '50000', 0, 0, 'Updated Previous Year DateLeft: %I64d rows', var_RowCount USING ERRCODE = '50000';
RAISE NOTICE 'sourcelearner1: %', var_RowCount;                     

-- IF var_TranCountOnEntry = 0 THEN
--             COMMIT;
--         END IF;
--         EXCEPTION
--             WHEN OTHERS THEN
--                 IF var_TranCountOnEntry = 0 THEN
--                     ROLLBACK;
--                 END IF;
--                 RAISE;
    END;
    
    
    DROP TABLE IF EXISTS t$changeddata; 
    DROP TABLE IF EXISTS t$mergeddata;        
    DROP TABLE IF EXISTS t$sourcedata;    
    DROP TABLE IF EXISTS t$sourcedata2;
    
END;
$BODY$;
