-- PROCEDURE: dbo.pupdatelearnercemis(bigint, integer)

-- DROP PROCEDURE IF EXISTS dbo.pupdatelearnercemis(bigint, integer);

CREATE OR REPLACE PROCEDURE dbo.pupdatelearnercemis(
	par_dataimportid bigint,
	par_debugmode integer DEFAULT 0)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    var_TranCountOnEntry INTEGER
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the @@TRANCOUNT function. Create a user-defined function.]
    = @@TRANCOUNT
    */;
    var_DataDate TIMESTAMP WITHOUT TIME ZONE;
    var_RowCount INTEGER;
    learner1 INTEGER;
    learner2 INTEGER;
    learner3 INTEGER;
    learner4 INTEGER;
    learner5 INTEGER;
BEGIN

	DROP TABLE IF EXISTS t$tempemis;
    /*
    [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
    */
    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
            datacontractdatadate
            INTO var_DataDate
            FROM dbo.dataimportdatacontract
            WHERE dataimportid = par_DataImportID;
RAISE NOTICE 'SELECT datacontractdatadate INTO var_DataDate';     
RAISE NOTICE 'var_DataDate: %', var_DataDate;     

        SELECT
            COUNT(*)
            INTO var_RowCount
            FROM dbo.learner AS l
            WHERE l.dataimportid = par_DataImportID;
RAISE NOTICE 'SELECT COUNT(*) INTO var_RowCount';     

         -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %. Argument: %', '50000', 0, 0, 'Learner data: %d', var_RowCount USING ERRCODE = '50000';
/*          drop table if exists t$sourcelearner_dw;
        create temporary table t$sourcelearner_dw
        as
        SELECT
            sourcelearnerid, datejoined, emiscode, year
            FROM dbo.sourcelearner_dw
            WHERE dateleft IS NULL
            ORDER BY year DESC NULLS FIRST, datejoined DESC NULLS FIRST;
*/          
/*
         UPDATE dbo.learner AS l
         SET datejoined = j.datejoined
         FROM dbo.learner
         CROSS JOIN LATERAL (SELECT
             datejoined
             FROM dbo.sourcelearner_dw
             WHERE LOWER(emiscode) = LOWER(dbo.learner.emiscode) 
                AND LOWER(sourcelearnerid) = LOWER(dbo.learner.cemislearnerid) 
                AND dateleft IS NULL
             ORDER BY year DESC NULLS FIRST, datejoined DESC NULLS FIRST
             LIMIT 1) AS j
             WHERE dbo.learner.dataimportid = par_DataImportID 
                AND dbo.learner.datejoined IS NULL 
                AND j.datejoined IS NOT NULL;
*/
create temporary table t$sourcelearner_dw
as
select j.*, l.dataimportid 
FROM dbo.learner l
inner JOIN (SELECT distinct on (sourcelearnerid, emiscode) emiscode, sourcelearnerid, year, datejoined
             FROM dbo.sourcelearner_dw
             where dateleft IS NULL
             group by  emiscode, sourcelearnerid, year, datejoined
             ORDER BY sourcelearnerid, emiscode, year DESC NULLS FIRST, datejoined DESC NULLS FIRST) AS j on 
                 LOWER(j.emiscode) = LOWER(l.emiscode) 
                  AND LOWER(j.sourcelearnerid) = LOWER(l.cemislearnerid) 
 WHERE l.dataimportid = par_DataImportID 
  AND l.datejoined IS NULL 
  AND j.datejoined IS NOT NULL;
  
UPDATE dbo.learner
SET datejoined = sl.datejoined
from t$sourcelearner_dw sl
where dbo.learner.emiscode = sl.emiscode    
    AND dbo.learner.dataimportid = sl.dataimportid
    AND dbo.learner.cemislearnerid = sl.sourcelearnerid;

        get diagnostics learner1 = row_count;
RAISE NOTICE 'UPDATE dbo.learner1';
RAISE NOTICE 'learner1: %', learner1;

        -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %. Argument: %', '50000', 0, 0, 'Existing Learners DateJoined set: %d', "var_@ROWCOUNT" USING ERRCODE = '50000';
/*        drop table if exists t$sourcelearner_dw;
        create temporary table t$sourcelearner_dw
        as
        SELECT
            sourcelearnerid, datejoined, emiscode, year
            FROM dbo.sourcelearner_dw
            WHERE aws_sqlserver_ext.datediff('week', datejoined::TIMESTAMP, var_DataDate::TIMESTAMP) <= 4
            ORDER BY year DESC NULLS FIRST, datejoined DESC NULLS FIRST;

         UPDATE dbo.learner AS l
         SET datejoined = j4.datejoined
         FROM dbo.learner
         CROSS JOIN LATERAL (SELECT
             datejoined
             FROM t$sourcelearner_dw
             WHERE LOWER(emiscode) = LOWER(dbo.learner.emiscode) 
                AND LOWER(sourcelearnerid) = LOWER(dbo.learner.cemislearnerid) 
                AND aws_sqlserver_ext.datediff('week', datejoined::TIMESTAMP, var_DataDate::TIMESTAMP) <= 4
             ORDER BY year DESC NULLS FIRST, datejoined DESC NULLS FIRST
             LIMIT 1) AS j4
             WHERE dbo.learner.dataimportid = par_DataImportID 
                AND dbo.learner.datejoined IS NULL 
                AND j4.datejoined IS NOT NULL;
*/    
create temporary table t$sourcelearner_dw
as
select j.*, l.dataimportid 
FROM dbo.learner l
inner JOIN (SELECT distinct on (sourcelearnerid, emiscode) emiscode, sourcelearnerid, year, datejoined
             FROM dbo.sourcelearner_dw
             where aws_sqlserver_ext.datediff('week', datejoined::TIMESTAMP, var_DataDate::TIMESTAMP) <= 4
             group by  emiscode, sourcelearnerid, year, datejoined
             ORDER BY sourcelearnerid, emiscode, year DESC NULLS FIRST, datejoined DESC NULLS FIRST) AS j on 
                 LOWER(j.emiscode) = LOWER(l.emiscode) 
                  AND LOWER(j.sourcelearnerid) = LOWER(l.cemislearnerid) 
 WHERE l.dataimportid = par_DataImportID 
  AND l.datejoined IS NULL 
  AND j.datejoined IS NOT NULL;
  
UPDATE dbo.learner
SET datejoined = sl.datejoined
from t$sourcelearner_dw sl
where dbo.learner.emiscode = sl.emiscode    
    AND dbo.learner.dataimportid = sl.dataimportid
    AND dbo.learner.cemislearnerid = sl.sourcelearnerid;            
            get diagnostics learner2 = row_count;
RAISE NOTICE 'UPDATE dbo.learner2';
RAISE NOTICE 'learner2: %', learner2;

        -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %. Argument: %', '50000', 0, 0, 'Existing Learners DateJoined set (< 4 weeks): %d', "var_@ROWCOUNT" USING ERRCODE = '50000';
/*     drop table if exists t$sourcelearner_dw;
     create temporary table t$sourcelearner_dw
     as
     SELECT
         sourcelearnerid, datejoined, emiscode, year
         FROM dbo.sourcelearner_dw
         WHERE aws_sqlserver_ext.datediff('week', datejoined::TIMESTAMP, var_DataDate::TIMESTAMP) > 4
         ORDER BY year DESC NULLS FIRST, datejoined DESC NULLS FIRST;
            
        UPDATE dbo.learner AS l
        SET datejoined = j4.datejoined
        FROM dbo.learner
        CROSS JOIN LATERAL (SELECT
            datejoined
            FROM t$sourcelearner_dw
            WHERE LOWER(emiscode) = LOWER(dbo.learner.emiscode) 
             AND LOWER(sourcelearnerid) = LOWER(dbo.learner.cemislearnerid) 
             AND aws_sqlserver_ext.datediff('week', datejoined::TIMESTAMP, var_DataDate::TIMESTAMP) > 4
            ORDER BY year DESC NULLS FIRST, datejoined DESC NULLS FIRST
            LIMIT 1) AS j4
            WHERE dbo.learner.dataimportid = par_DataImportID 
             AND dbo.learner.datejoined IS NULL 
             AND j4.datejoined IS NOT NULL;
*/
create temporary table t$sourcelearner_dw
as
select j.*, l.dataimportid 
FROM dbo.learner l
inner JOIN (SELECT distinct on (sourcelearnerid, emiscode) emiscode, sourcelearnerid, year, datejoined
             FROM dbo.sourcelearner_dw
             where aws_sqlserver_ext.datediff('week', datejoined::TIMESTAMP, var_DataDate::TIMESTAMP) > 4
             group by  emiscode, sourcelearnerid, year, datejoined
             ORDER BY sourcelearnerid, emiscode, year DESC NULLS FIRST, datejoined DESC NULLS FIRST) AS j on 
                 LOWER(j.emiscode) = LOWER(l.emiscode) 
                  AND LOWER(j.sourcelearnerid) = LOWER(l.cemislearnerid) 
 WHERE l.dataimportid = par_DataImportID 
  AND l.datejoined IS NULL 
  AND j.datejoined IS NOT NULL;
  
UPDATE dbo.learner
SET datejoined = sl.datejoined
from t$sourcelearner_dw sl
where dbo.learner.emiscode = sl.emiscode    
    AND dbo.learner.dataimportid = sl.dataimportid
    AND dbo.learner.cemislearnerid = sl.sourcelearnerid;               
         get diagnostics learner3 = row_count;
RAISE NOTICE 'UPDATE dbo.learner3';
RAISE NOTICE 'learner3: %', learner3;

        -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %. Argument: %', '50000', 0, 0, 'Existing Learners DateJoined set (> 4 weeks): %d', "var_@ROWCOUNT" USING ERRCODE = '50000';
        UPDATE dbo.learner AS l
        SET datejoined = var_DataDate
            WHERE l.dataimportid = par_DataImportID AND l.datejoined IS NULL;
            get diagnostics learner4 = row_count;
RAISE NOTICE 'UPDATE dbo.learner4';
RAISE NOTICE 'learner4: %', learner4;

        -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %. Argument: %', '50000', 0, 0, 'New Learners DateJoined set: %d', "var_@ROWCOUNT" USING ERRCODE = '50000';
/*        
        UPDATE dbo.sourcelearner_dw
        SET dateleft =
        CASE
            WHEN date_part('year', var_DataDate) > date_part('year', s.lastsubmissiondate) THEN make_date(date_part('year', s.lastsubmissiondate)::int, 12, 31)
            ELSE var_DataDate
        END

        FROM dbo.sourcelearner_dw AS s
        JOIN (SELECT
            s.sourcesystem, s.sourcelearnerid, s.emiscode, s.year, s.datejoined, s.dateleft
            FROM (SELECT
                s.sourcesystem, s.sourcelearnerid, s.emiscode, s.year, s.datejoined, s.dateleft, ROW_NUMBER() OVER (PARTITION BY s.sourcelearnerid, s.emiscode ORDER BY s.year DESC, s.datejoined DESC) AS seq
                FROM dbo.sourcelearner_dw AS s
                WHERE LOWER(s.sourcesystem) = LOWER('CEMIS') AND s.emiscode IN (SELECT DISTINCT
                    emiscode
                    FROM dbo.learner
                    WHERE dataimportid = par_DataImportID)) AS s
            LEFT OUTER JOIN dbo.learner AS l
                ON l.dataimportid = par_DataImportID AND LOWER(s.sourcelearnerid) = LOWER(l.cemislearnerid) AND LOWER(s.emiscode) = LOWER(l.emiscode)
            WHERE s.Seq = 1 AND s.dateleft IS NULL AND l.cemislearnerid IS NULL) AS x
            ON LOWER(s.sourcesystem) = LOWER(x.sourcesystem) AND LOWER(s.sourcelearnerid) = LOWER(x.sourcelearnerid) AND LOWER(s.emiscode) = LOWER(x.emiscode) AND s.year = x.year AND s.datejoined = x.datejoined;
*/            
        drop table if exists t$sourcelearner_dw;
RAISE NOTICE 'LEARNER 5 START : %', par_dataimportid;
-- return;--testing

/* Create temp table for emiscode */
create temporary table t$tempemis
        as
		SELECT DISTINCT
		emiscode
		FROM dbo.learner
		WHERE dataimportid = par_DataImportID;

		drop table if exists t$templearners;
		drop table if exists t$templearnersdata;

		CREATE TEMP TABLE t$templearners
		AS
		SELECT
		s.sourcesystem, s.sourcelearnerid, s.emiscode, s.year, s.datejoined, s.dateleft, ROW_NUMBER() 
		 OVER (
			PARTITION BY s.sourcelearnerid, s.emiscode 
			ORDER BY s.year DESC, s.datejoined DESC
		) AS seq
		FROM dbo.sourcelearner_dw AS s
		LEFT JOIN t$tempemis EM on s.emiscode = EM.emiscode
		WHERE LOWER(s.sourcesystem) = LOWER('CEMIS')
			AND EM.emiscode IS NOT NULL;

		CREATE TEMP TABLE t$templearnersdata
		AS				 
		SELECT
		s.sourcesystem, s.sourcelearnerid, s.emiscode, s.year, s.datejoined, s.dateleft
		FROM t$templearners AS s
		LEFT OUTER JOIN dbo.learner AS l
			ON l.dataimportid = 5894 AND LOWER(s.sourcelearnerid) = LOWER(l.cemislearnerid) AND LOWER(s.emiscode) = LOWER(l.emiscode)
		WHERE s.Seq = 1 AND s.dateleft IS NULL AND l.cemislearnerid IS NULL;

	create temporary table t$sourcelearner_dw
        as
	SELECT s.sourcesystem, s.sourcelearnerid, s.emiscode, s.year, s.datejoined, s.dateleft, s.lastsubmissiondate -- * -- Was all but give error on duplicate column name when tying to update            
			FROM dbo.sourcelearner_dw AS s
			JOIN t$templearnersdata AS x
				ON LOWER(s.sourcesystem) = LOWER(x.sourcesystem) AND LOWER(s.sourcelearnerid) = LOWER(x.sourcelearnerid) AND LOWER(s.emiscode) = LOWER(x.emiscode) AND s.year = x.year AND s.datejoined = x.datejoined;

-- 	create temporary table t$sourcelearner_dw
--         as
--         SELECT s.sourcesystem, s.sourcelearnerid, s.emiscode, s.year, s.datejoined, s.dateleft, s.lastsubmissiondate -- * -- Was all but give error on duplicate column name when tying to update            
--         FROM dbo.sourcelearner_dw AS s

--         JOIN (SELECT
--             s.sourcesystem, s.sourcelearnerid, s.emiscode, s.year, s.datejoined, s.dateleft
--             FROM (SELECT
--                 s.sourcesystem, s.sourcelearnerid, s.emiscode, s.year, s.datejoined, s.dateleft, ROW_NUMBER() 
-- 				 OVER (
-- 					PARTITION BY s.sourcelearnerid, s.emiscode 
-- 					ORDER BY s.year DESC, s.datejoined DESC
-- 				) AS seq
--                 FROM dbo.sourcelearner_dw AS s
--                 WHERE LOWER(s.sourcesystem) = LOWER('CEMIS') AND s.emiscode IN (
-- 					select emiscode from t$tempemis)) AS s
--             LEFT OUTER JOIN dbo.learner AS l
--                 ON l.dataimportid = par_DataImportID AND LOWER(s.sourcelearnerid) = LOWER(l.cemislearnerid) AND LOWER(s.emiscode) = LOWER(l.emiscode)
--             WHERE s.Seq = 1 AND s.dateleft IS NULL AND l.cemislearnerid IS NULL) AS x
--             ON LOWER(s.sourcesystem) = LOWER(x.sourcesystem) AND LOWER(s.sourcelearnerid) = LOWER(x.sourcelearnerid) AND LOWER(s.emiscode) = LOWER(x.emiscode) AND s.year = x.year AND s.datejoined = x.datejoined;
			
			DROP TABLE IF EXISTS t$tempemis;
			drop table if exists t$templearners;
			drop table if exists t$templearnersdata;
		
RAISE NOTICE 'LEARNER 5 UPDATE  : %' ,var_DataDate;

        UPDATE dbo.sourcelearner_dw
        SET dateleft =
        CASE
            WHEN extract(year from '2021-08-17 19:00:01'::timestamp)  > extract(year from s.lastsubmissiondate) THEN make_date(extract(year from s.lastsubmissiondate)::int, 12, 31)
            ELSE var_DataDate
        END
        FROM t$sourcelearner_dw s
        WHERE dbo.sourcelearner_dw.year = s.year AND dbo.sourcelearner_dw.sourcelearnerid = s.sourcelearnerid;
		
RAISE NOTICE 'LEARNER 5 END';
        /* ORDER BY S.EMISCode, S.SourceLearnerID, S.Year */
        get diagnostics learner5 = row_count;
RAISE NOTICE 'UPDATE dbo.sourcelearner_dw1';
RAISE NOTICE 'learner5: %', learner5;

        -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %. Argument: %', '50000', 0, 0, 'Learners DateLeft set: %d', "var_@ROWCOUNT" USING ERRCODE = '50000';

--         IF var_TranCountOnEntry = 0 THEN
--             COMMIT;
--         END IF;
--         EXCEPTION
--             WHEN OTHERS THEN
--                 IF var_TranCountOnEntry = 0 THEN
--                     ROLLBACK;
--                 END IF;
--                 -- RAISE;
    END;
END;
$BODY$;
