-- PROCEDURE: internal.ptransferreadingeggs2(bigint, bit, bit, bit, bit, integer)

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

CREATE OR REPLACE PROCEDURE internal.ptransferreadingeggs2(
	par_dataimportid bigint,
	par_calcweektot bit DEFAULT '1'::"bit",
	par_updatelearnerfromsource bit DEFAULT '1'::"bit",
	par_nodatainsert bit DEFAULT '0'::"bit",
	par_validateonly bit DEFAULT '0'::"bit",
	par_debugmode integer DEFAULT 0)
LANGUAGE 'plpgsql'
AS $BODY$

DECLARE
		var_SourceDataImportID BIGINT DEFAULT (SELECT
        sourcedataimportid
        FROM internal.dataimport
        WHERE dataimportid = par_DataImportID);
		par_SourceDataDate			date;
		par_StartDate				date;
		par_SourceDataDateS		varchar(20);
		par_SourceDataDateYear		integer;
		par_dbname					varchar(255) DEFAULT 'DW';
		par_LogDescription			varchar(255);
		par_LogData				varchar(2048);
		par_RowCount				int;
		par_LogLevel				char(1);
		par_Sql					TEXT;
		par_PrevSourceDataImportID	bigint;
		par_TranCountOnEntry  bigint DEFAULT 0;
		par_SourceRowCount int DEFAULT 0;
		ptemporalstatus$par_StatusChanged NUMERIC;
   		ptemporalstatus$ReturnCode INTEGER;
BEGIN

	DROP TABLE IF EXISTS t$personhisttemp;
	DROP TABLE IF EXISTS t$readingeggs2_staging;
	
	IF par_dataimportid IS NULL THEN
		RAISE NOTICE 'DATA IMPORT IS NULL';
		--RETURN 1;
	END IF;
	

	SELECT
		--par_SourceDataDate		= DataContractDataDate, 
		--par_StartDate          =   DataContractDataDate::date - INTERVAL '6 day', --DATEADD(day,-6,DataContractDataDate),
		--par_SourceDataDateYear	=DATE_PART('year', DataContractDataDate::date),
		--par_SourceDataDateS	= TO_CHAR(par_SourceDataDate::DATE, 'yyyy-MM-dd')
		
		DataContractDataDate,
		DataContractDataDate::date - INTERVAL '6 day',
		DATE_PART('year', DataContractDataDate::date),
		TO_CHAR(DataContractDataDate::DATE, 'yyyy-MM-dd')
		INTO
		par_SourceDataDate,
		par_StartDate,
		par_SourceDataDateYear,
		par_SourceDataDateS
		
	FROM dbo.dataimportdatacontract_staging
	WHERE DataImportID = var_SourceDataImportID;	

	SELECT DI.SourceDataImportID INTO par_PrevSourceDataImportID 
	FROM internal.DataImport DI 
	JOIN dbo.dataimportdatacontract_staging SDI ON DI.SourceDataImportID = SDI.DataImportID
	WHERE DI.DataImportStatus = 'Success' AND SDI.DataContractDataDate < par_SourceDataDate AND SDI.DataSourceTypeID = 2 AND DATE_PART('year',SDI.DataContractDataDate) = par_SourceDataDateYear
	ORDER BY SDI.DataContractDataDate DESC, DI.StartTime DESC LIMIT 1 ;
	
	par_LogDescription = FORMAT('SourceDataImportID=%s, par_SourceDataDateYear=%s, par_SourceDataDate=%s, PrevSourceDataImportID=%s', var_SourceDataImportID, par_SourceDataDateYear, par_SourceDataDateS, par_PrevSourceDataImportID);
	
	RAISE NOTICE 'par_PrevSourceDataImportID: %',par_PrevSourceDataImportID ;
	CALL internal.pDataImportLogInsert(par_DataImportID, par_LogDescription);
	RAISE NOTICE 'LOGS DONE ';
	CREATE TEMPORARY TABLE par_SyncUserNameTable 
	(
		ProgrammeID smallint not null,
		Code varchar(10) not null
	);
	
	Raise Notice 'par_DataImportID %s', par_DataImportID;
	INSERT INTO par_SyncUserNameTable(ProgrammeID, Code)
	SELECT DISTINCT P.ProgrammeID, P.Code
	FROM dbo.SystemParameter SP
	JOIN dbo.Programme P ON SP.Value1 = P.ProgrammeID::character varying
	WHERE SP.Category = 'Processing' AND SP.SubCategory = 'Learners' AND SP.Name = 'Sync Username' AND SP.Value = 4::character varying;
	
	raise notice 'inserted par_SyncUserNameTable';
	
	/* Create temp table for readingeggs2_staging */
	CREATE TEMPORARY TABLE t$readingeggs2_staging 
	AS 
	SELECT * FROM dbo.readingeggs2_staging D
	WHERE D.DataImportID = var_SourceDataImportID;
	
-- Copy data from STAGING
	CREATE TEMPORARY TABLE t$ReadingEggs2
	(
		RowNumber bigint NOT NULL,
		"student id" INTEGER NULL,
		"student first name" varchar(50) NULL,
		"student last name" varchar(50) NULL,
		"student grade" smallint null,
		"student login" varchar(50) NULL,
		"school code" varchar(20) NULL,
		"school name" varchar(70) NULL,
		"teacher id" INTEGER NULL,
		"teacher name" varchar(70) NULL,
		"re_lesson_placement" smallint NULL,
		"re_lesson" smallint NULL,
		"re_spelling" smallint NULL,
		"re_certificates" smallint NULL,
		"re_golden_eggs" INTEGER NULL,
		"map 1 reading % score" smallint NULL,
		"map 2 reading % score" smallint NULL,
		"map 3 reading % score" smallint NULL,
		"map 4 reading % score" smallint NULL,
		"map 5 reading % score" smallint NULL,
		"map 6 reading % score" smallint NULL,
		"map 7 reading % score" smallint NULL,
		"map 8 reading % score" smallint NULL,
		"map 9 reading % score" smallint NULL,
		"map 10 reading % score" smallint NULL,
		"map 11 reading % score" smallint NULL,
		"map 12 reading % score" smallint NULL,
		"rex eggs" INTEGER NULL,
		"rex_comp_placement" smallint NULL,
		"rex_comp_lesson" smallint NULL,
		"comprehension gym quiz average %" smallint NULL,
		"library books read (deprecated)" smallint NULL,
		"library quiz average % (deprecated)" smallint NULL,
		"rex_lesson_placement" smallint NULL,
		"rex_lesson" smallint NULL,
		"lexile gain" smallint NULL,
		"my lessons quiz average % (deprecated)" smallint NULL,
		"last fiction assessment lexile" smallint NULL,
		"fiction quiz average %" smallint NULL,
		"last non fiction assessment lexile" smallint NULL,
		"non fiction quiz average %" smallint NULL,
		"rex_spelling_placement" decimal(9, 5) NULL,
		"rex_spelling" decimal(9, 5) NULL,
		"english skills spelling lessons completed" smallint NULL,
		"english skills spelling quiz average %" smallint NULL,
		"library books read" smallint NULL,
		"library quiz average %" smallint NULL,
		"rex_usage" INTEGER NULL,
		"external student id" INTEGER NULL,
		"re sessions" smallint NULL,
		"re time (sec)" INTEGER NULL,
		"re_usage" smallint NULL,
		"rex sessions" smallint NULL,
		"rex time (sec)" INTEGER NULL,
		"re_driving_test" smallint NULL,
		"re_stories_written" smallint NULL,
		"re_books_read" smallint NULL,
		"re_reading_min_lexile" smallint NULL,
		"re_reading_max_lexile" smallint NULL,
		"rex_stadium_game_count" smallint NULL,
		"rex_lessons_min_lexile" smallint NULL,
		"rex_lessons_max_lexile" smallint NULL,
		"fp_lesson_count" smallint NULL,
		"fp_lesson_quiz_count" smallint NULL,
		"fp_book_quiz_count" smallint NULL,
		"fp time (sec)" INTEGER null,
		-- Mapped fields
		ProgrammeEnvironmentID int null,
		SchoolID int null,
		PersonIDFromStudentID int null,
		PersonIDFromExternalID int null,
		StudentIDFromExternalID int null,
		PersonID int null,
		Okay bit not null default('1'),
		Ignore bit not null default('0'),
		Duplicate bit not null default('0'),
		Merged bit not null default('0'),
		Historic bit not null default('0'),
		"original external student id - replace" int null,
		"original external student id - merge" int null,
		PreviousFile bit null,
		FetchPreviousData bit null,
		LearnerManagementLevelID smallint null
	);
	
	Raise Notice 'Created temp table :  t$ReadingEggs2';
	RAISE NOTICE 'par_PrevSourceDataImportID: %',par_PrevSourceDataImportID ;

	INSERT INTO t$ReadingEggs2
		(
			RowNumber,
	 		"student id", 
			"student first name", 
			"student last name", 
			"student grade", 
			"student login", 
			"school code", 
			"school name", 
			"teacher id", 
			"teacher name", 
			"re_lesson_placement", 
			"re_lesson", 
			"re_spelling", 
			"re_certificates", 
			"re_golden_eggs", 
			"map 1 reading % score", 
			"map 2 reading % score", 
			"map 3 reading % score", 
			"map 4 reading % score", 
			"map 5 reading % score", 
			"map 6 reading % score", 
			"map 7 reading % score", 
			"map 8 reading % score", 
			"map 9 reading % score", 
			"map 10 reading % score", 
			"map 11 reading % score", 
			"map 12 reading % score", 
			"rex eggs", 
			"rex_comp_placement", 
			"rex_comp_lesson", 
			"comprehension gym quiz average %", 
			"library books read (deprecated)", 
			"library quiz average % (deprecated)", 
			"rex_lesson_placement", 
			"rex_lesson", 
			"lexile gain", 
			"my lessons quiz average % (deprecated)", 
			"last fiction assessment lexile", 
			"fiction quiz average %", 
			"last non fiction assessment lexile", 
			"non fiction quiz average %", 
			"rex_spelling_placement", 
			"rex_spelling", 
			"english skills spelling lessons completed", 
			"english skills spelling quiz average %", 
			"library books read", 
			"library quiz average %", 
			"rex_usage", 
			"external student id", 
			"re sessions", 
			"re time (sec)", 
			"re_usage", 
			"rex sessions", 
			"rex time (sec)", 
			"re_driving_test", 
			"re_stories_written", 
			"re_books_read", 
			"re_reading_min_lexile", 
			"re_reading_max_lexile", 
			"rex_stadium_game_count", 
			"rex_lessons_min_lexile", 
			"rex_lessons_max_lexile", 
			"fp_lesson_count", 
			"fp_lesson_quiz_count", 
			"fp_book_quiz_count", 
			"fp time (sec)",
			previousfile, 
			fetchpreviousdata
	)
	SELECT
		D.RowNumber, 
		D."student id", 
		TRIM(D."student first name"), 
		TRIM(D."student last name"), 
		CASE
			--WHEN "student Grade" = '0' THEN NUll
			WHEN D."student grade" = 'Rec' THEN 0
			ElSE CAST(D."student grade" AS smallint)
		END,
		TRIM(D."student login"), 
		TRIM(D."school code"), 
		TRIM(D."school name"), 
		D."teacher id", 
		TRIM(D."teacher name"), 
		D."re_lesson_placement", 
		D."re_lesson", 
		D."re_spelling", 
		D."re_certificates", 
		D."re_golden_eggs", 
		D."map 1 reading % score", 
		D."map 2 reading % score", 
		D."map 3 reading % score", 
		D."map 4 reading % score", 
		D."map 5 reading % score", 
		D."map 6 reading % score", 
		D."map 7 reading % score", 
		D."map 8 reading % score", 
		D."map 9 reading % score", 
		D."map 10 reading % score", 
		D."map 11 reading % score", 
		D."map 12 reading % score", 
		D."rex eggs", 
		D."rex_comp_placement", 
		D."rex_comp_lesson", 
		D."comprehension gym quiz average %", 
		D."library books read (deprecated)", 
		D."library quiz average % (deprecated)", 
		D."rex_lesson_placement", 
		D."rex_lesson", 
		D."lexile gain", 
		D."my lessons quiz average % (deprecated)", 
		D."last fiction assessment lexile", 
		D."fiction quiz average %", 
		D."last non fiction assessment lexile", 
		D."non fiction quiz average %", 
		D."rex_spelling_placement", 
		D."rex_spelling", 
		D."english skills spelling lessons completed", 
		D."english skills spelling quiz average %", 
		D."library books read", 
		D."library quiz average %", 
		D."rex_usage", 
		D."external student id", 
		D."re sessions", 
		D."re time (sec)", 
		D."re_usage", 
		D."rex sessions", 
		D."rex time (sec)", 
		D."re_driving_test", 
		D."re_stories_written", 
		D."re_books_read", 
		D."re_reading_min_lexile", 
		D."re_reading_max_lexile", 
		D."rex_stadium_game_count", 
		D."rex_lessons_min_lexile", 
		D."rex_lessons_max_lexile", 
		D."fp_lesson_count", 
		D."fp_lesson_quiz_count", 
		D."fp_book_quiz_count", 
		D."fp time (sec)",
		CASE 
			WHEN par_PrevSourceDataImportID IS NUll THEN NUll
				WHEN P."student id" IS NOT NUll THEN 1::bit
			ElSE 0::bit
		END,
		0::bit
	FROM t$readingeggs2_staging D
	LEFT JOIN t$readingeggs2_staging P ON P.DataImportID = par_PrevSourceDataImportID AND D."student id" = P."student id"
	WHERE D.DataImportID = var_SourceDataImportID 
		AND NOT (D."student first name" lIKE 'Demo student %' OR 
			D."student login" lIKE 'demo!_student!_%' ESCAPE '!' OR 
			D."student login" lIKE 'demostudent%')
	ORDER BY D.RowNumber;

	Raise Notice 'Done with update';

	--DECLARE par_SourceRowCount int = par_par_ROWCOUNT;
	GET DIAGNOSTICS par_SourceRowCount = ROW_COUNT;
	par_LogDescription = FORMAT('Source Rows: %s', par_SourceRowCount); 
	CALL internal.pdataimportloginsert(par_DataImportID, par_LogDescription);

-- Match School Code
	Raise Notice 'Match School Code';
	UPDATE t$ReadingEggs2 D
	SET  ProgrammeEnvironmentID = PE.ProgrammeEnvironmentID, 
		SchoolID = SE.SchoolID,
		LearnerManagementLevelID = TG.LearnerManagementLevelID
-- 	FROM t$ReadingEggs2 D
	FROM dbo.ProgrammeEnvironment PE --  ON D."school code" = PE.ProgrammeCode AND PE.ProgrammeID = 4
    JOIN dbo.SchoolEnvironment SE ON PE.EnvironmentID = SE.EnvironmentID
	LEFT JOIN dbo.ProgrammeEnvironmentHasTargetGrade TG ON PE.ProgrammeEnvironmentID = TG.ProgrammeEnvironmentID --AND D."student grade" = TG.GradeID
	WHERE  D."school code" = PE.ProgrammeCode AND PE.ProgrammeID = 4 AND D."student grade" = TG.GradeID;

	--SET par_RowCount = par_par_ROWCOUNT;
	--SET par_LogDescription = FORMAT('ProgrammeEnvironment matches on School Code: %d', par_RowCount);  CALL_ internal.pDataImportLogInsert par_DataImportID, par_LogDescription;

	DELETE FROM t$ReadingEggs2 WHERE ProgrammeEnvironmentID IS NULL;
Raise Notice 'Delete null ProgrammeEnvironmentIDs';
-- Delete existing DW data
		IF par_nodatainsert = 0::"bit"
		THEN
			DELETE FROM dbo.LearnerDataReadingEggs WHERE Date = par_SourceDataDate;

-- 			SET par_RowCount = par_par_ROWCOUNT;
			GET DIAGNOSTICS par_RowCount = ROW_COUNT;
			par_LogDescription = FORMAT('LearnerDataReadingEggs deleted: %s', par_RowCount); 
			CALL internal.pDataImportLogInsert(par_DataImportID, par_LogDescription);
		END IF;

-- Match on Student ID
Raise Notice 'Match on Student ID';
		UPDATE t$ReadingEggs2 D SET 
			PersonID				= ID.PersonID, 
			PersonIDFromStudentID	= ID.PersonID
-- 		FROM t$ReadingEggs2 D
		FROM dbo.PersonExternalID ID --ON ID.PersonExternalIDType = 200 AND CAST(D."student id" AS varchar(50)) = ID.ExternalID
		WHERE ID.PersonExternalIDType = 200 AND CAST(D."student id" AS varchar(50)) = ID.ExternalID AND D.Okay = 1::bit;
			--AND (D.PreviousFile = 1 OR D.PreviousFile IS NULL);
		--SET par_RowCount = par_par_ROWCOUNT;
		
		par_LogDescription = FORMAT('Person matches on Student ID: %s', par_RowCount);  
		CALL internal.pDataImportLogInsert (par_DataImportID,par_LogDescription);

-- Match on historic Student ID
Raise Notice 'Match on historic Student ID';

		CREATE TEMP TABLE t$personhisttemp
		as
		select IDH.*
		FROM dbo.PersonExternalIDHistory IDH
		JOIN t$ReadingEggs2 D ON  CAST(D."student id" AS varchar(50)) = IDH.ExternalID
		where IDH.PersonExternalIDType = 200
			AND D.Okay = 1::bit;
		
		Raise Notice 'Match on historic Student ID TEMP DONE';
		UPDATE t$ReadingEggs2 D SET 
			PersonID				= IDH.PersonID, 
			PersonIDFromStudentID	= IDH.PersonID, 
			Ignore					= 1::bit, 
			Okay					= 0::bit, 
			Historic				= 1::bit
-- 		FROM t$ReadingEggs2 AS D
		FROM t$personhisttemp IDH -- ON IDH.PersonExternalIDType = 200 AND CAST(D."student id" AS varchar(50)) = IDH.ExternalID
 		LEFT JOIN dbo.PersonExternalID ID ON ID.PersonExternalIDType = 200 AND ID.ExternalID = IDH.ExternalID -- AND ID.ExternalID IS NOT NULL
		WHERE CAST(D."student id" AS varchar(50)) = IDH.ExternalID 
-- 			AND D.Okay = 1::bit
			AND ID.PersonID IS NULL;
			--AND (D.PreviousFile = 1 OR D.PreviousFile IS NULL);
			
		DROP TABLE IF EXISTS t$personhisttemp;
		Raise Notice 'Match on historic Student ID  DONE';
-- 		SET par_RowCount = par_par_ROWCOUNT;
		GET DIAGNOSTICS par_RowCount = ROW_COUNT;
		par_LogDescription := FORMAT('Person matches on Student ID (Historic): %s', par_RowCount);  
		CALL internal.pDataImportLogInsert (par_DataImportID,par_LogDescription);
		

-- Match on External Student ID (first time appearance of StudentID)
Raise Notice 'Match on External Student ID (first time appearance of StudentID)';
		UPDATE t$ReadingEggs2 D SET 
			PersonID				= P.TargetPersonID, 
			PersonIDFromExternalID	= P.TargetPersonID, 
			StudentIDFromExternalID = NULL, 
			Historic				= 0::bit, 
			PersonIDFromStudentID	= NULL
-- 		FROM t$ReadingEggs2 D
		FROM dbo.vwTargetPersonID P --ON D."external student id" = P.PersonID
		LEFT JOIN dbo.PersonExternalID ID ON ID.PersonExternalIDType = 200 AND P.TargetPersonID = ID.PersonID
		JOIN dbo.PersonHasProgramme PP ON P.PersonID = PP.PersonID AND PP.ProgrammeID = 4
		WHERE  D."external student id" = P.PersonID 
			AND D.Okay = 1::bit
			AND (
				(D.PreviousFile = 0::bit) 
				OR (D.PreviousFile IS NULL AND D.PersonIDFromStudentID IS NULL)
				OR (D.PreviousFile IS NULL AND D.PersonIDFromStudentID IS NOT NULL AND D.Historic = 1::bit)
			)
			AND ID.PersonID IS NULL
			AND ((PP.UserName IS NULL) OR (PP.UserName IS NOT NULL AND PP.UserName = D."student login"));

-- 		SET par_RowCount = par_par_ROWCOUNT;
		GET DIAGNOSTICS par_RowCount = ROW_COUNT;
		par_LogDescription = FORMAT('Person matches on External Student ID (First time): %s', par_RowCount);
		CALL internal.pDataImportLogInsert (par_DataImportID, par_LogDescription);

-- Match on External Student ID (Previous)
Raise Notice 'Match on External Student ID (Previous)';
		UPDATE t$ReadingEggs2 D SET 
			PersonID				= P.TargetPersonID, 
			PersonIDFromExternalID	= P.TargetPersonID, 
			StudentIDFromExternalID = NULL, 
			FetchPreviousData		= 1::bit
-- 		FROM t$ReadingEggs2 D
		FROM dbo.vwTargetPersonID P -- ON D."external student id" = P.PersonID
		LEFT JOIN LATERAL (SELECT  UserName FROM dbo.PersonHasProgramme PP WHERE P.PersonID = PP.PersonID AND PP.ProgrammeID = 4 ORDER BY PP.SysEndTime DESC LIMIT 1) PP ON TRUE
-- 		LEFT JOIN t$ReadingEggs2 D1 ON P.TargetPersonID = D1.PersonID AND D1.Okay = 1::bit
		LEFT JOIN dbo.PersonExternalID ID ON P.TargetPersonID = ID.PersonID AND ID.PersonExternalIDType = 200
		WHERE D."external student id" = P.PersonID 
			AND D.Okay = 1::bit
			AND D.PersonIDFromStudentID IS NULL
			AND D.PersonIDFromExternalID IS NULL
			AND D.PersonID IS NULL
			AND ID.PersonID IS NULL
			AND ((PP.UserName IS NULL) OR (PP.UserName IS NOT NULL AND PP.UserName = D."student login"));

-- 		SET par_RowCount = par_par_ROWCOUNT;
		GET DIAGNOSTICS par_RowCount = ROW_COUNT;
		par_LogDescription := FORMAT('Person matches on External Student ID: %s', par_RowCount);  
		CALL internal.pDataImportLogInsert(par_DataImportID, par_LogDescription);

		--SELECT * FROM t$ReadingEggs2 WHERE FetchPreviousData = 1

-- Remove unmatched learners
Raise Notice 'Remove unmatched learners';
		--SELECT COUNT(*) FROM t$ReadingEggs2
		DELETE
		FROM t$ReadingEggs2 D
		WHERE PersonID IS NULL;

		--UPDATE D SET PersonID = ISNULL(D.PersonIDFromStudentID, D.PersonIDFromExternalID)
		--FROM t$ReadingEggs2 D
		--WHERE D.Okay = 1
			

-- Check for duplicate PersonIDs
Raise Notice 'Check for duplicate PersonIDs';
		 par_RowCount = COALESCE((SELECT COUNT(*) FROM (SELECT D.PersonID FROM t$ReadingEggs2 D WHERE D.Okay = 1::bit GROUP BY D.PersonID HAVING COUNT(*) > 1) X),0);

		IF par_RowCount > 0
		THEN
			INSERT INTO internal.DataImportLog
			(DataImportID, LogLevelID, DataID, Code, "Description", "Data")
			SELECT par_DataImportID, 2, D.RowNumber, 10005, 'Duplicate Match', 'Duplicated (' + CAST(X.DuplicateCount AS varchar(20)) + ') PersonID (' + CAST(D.PersonID AS varchar(20)) + '), Student ID(' + CAST("Student ID" AS varchar(20)) + ') External Student ID (' + CAST("External Student ID" AS varchar(20)) + ').'
			FROM t$ReadingEggs2 D
			JOIN
			(
				SELECT D.PersonID, COUNT(*) DuplicateCount FROM t$ReadingEggs2 D WHERE D.Okay = 1::bit GROUP BY D.PersonID HAVING COUNT(*) > 1
			) X ON X.PersonID = D.PersonID
			ORDER BY D.PersonID;

			par_LogData = par_RowCount::varchar(20);
			CALL internal.pDataImportLogInsert(par_DataImportID, 'Duplicate External Student ID', 'E', par_LogData, 0, 10005);

			UPDATE t$ReadingEggs2 D SET 
				Duplicate = 1,
				Okay      = CASE WHEN REID.PersonID IS NULL THEN 0 ELSE 1 END,
				Ignore    = CASE
								WHEN REID.PersonID IS NULL THEN 0
								WHEN REID.StudentID = D."Student ID" THEN 0
								ELSE 1
							END
-- 			FROM t$ReadingEggs2 D
			FROM (SELECT D.PersonID FROM t$ReadingEggs2 D WHERE D.Okay=1::bit GROUP BY D.PersonID HAVING COUNT(*) > 1) X  -- ON X.PersonID = D.PersonID
			LEFT JOIN dbo.vwPersonReadingEggsStudentID REID ON D.PersonID = REID.PersonID
			WHERE X.PersonID = D.PersonID;

			IF par_DebugMode >= 1
			THEN
				SELECT 'Duplicate' , D.Seq, R.*
				FROM t$ReadingEggs2 R
				JOIN (SELECT PersonID, ROW_NUMBER() OVER (ORDER BY PersonID) Seq FROM (SELECT DISTINCT PersonID FROM t$ReadingEggs2 WHERE Duplicate = 1) X) D  ON R.PersonID = D.PersonID
				WHERE R.Duplicate = 1 
				ORDER BY R.PersonID, R.Ignore;
			END IF;

		END IF;

		--SELECT COUNT(*) FROM t$ReadingEggs2
		DELETE FROM t$ReadingEggs2 WHERE Okay = 0::bit OR Ignore = 1::bit;
		--SELECT COUNT(*) FROM t$ReadingEggs2
		DELETE FROM t$ReadingEggs2 WHERE PersonID IS NULL;
		--SELECT COUNT(*) FROM t$ReadingEggs2

-- Update Student ID
Raise Notice 'Update Student ID';
		--SELECT * FROM t$ReadingEggs2 WHERE PersonID=198990

		INSERT INTO dbo.PersonExternalID
		(PersonID, ExternalID, PersonExternalIDType)
		SELECT D.PersonIDFromExternalID, CAST(D."student id" AS varchar(50)), 200
		FROM t$ReadingEggs2 D
		WHERE D.PersonIDFromExternalID IS NOT NULL
			--AND (D.PreviousFile = 0 OR D.PreviousFile IS NULL)
			AND D.StudentIDFromExternalID IS NULL;

-- 		SET par_RowCount = par_par_ROWCOUNT;
		GET DIAGNOSTICS par_RowCount = ROW_COUNT;
		par_LogDescription := FORMAT('PersonExternalID inserted: %s', par_RowCount); 
		CALL internal.pDataImportLogInsert (par_DataImportID, par_LogDescription);

		--SELECT *
		--FROM t$ReadingEggs2 D
		--WHERE D.FetchPreviousData = 1

-- Update Login
Raise Notice 'Update Login';
		IF EXISTS(
			SELECT *
			FROM t$ReadingEggs2 D
			JOIN dbo.PersonHasProgramme PP ON D.PersonID = PP.PersonID AND PP.ProgrammeID = 4
			WHERE PP.UserName IS NULL OR PP.UserName <> D."student login"
			)
		THEN
			CALL internal.pTemporalStatus('dbo.PersonHasProgramme', 0,NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);

			UPDATE dbo.PersonHasProgramme PP SET UserName = D."student login", UserNameDate = par_SourceDataDate
			FROM t$ReadingEggs2 D
-- 			JOIN dbo.PersonHasProgramme PP ON D.PersonID = PP.PersonID AND PP.ProgrammeID = 4
			WHERE D.PersonID = PP.PersonID AND PP.ProgrammeID = 4 AND (PP.UserName IS NULL OR PP.UserName <> D."student login");

-- 			SET par_RowCount = par_par_ROWCOUNT;
			GET DIAGNOSTICS par_RowCount = ROW_COUNT;
			par_LogData := par_RowCount::varchar(20);
			CALL internal.pDataImportLogInsert(par_DataImportID, 'Student Login updated', 'W', par_LogData, 0, 20004);

			IF EXISTS(SELECT * FROM par_SyncUserNameTable)
			THEN
				UPDATE dbo.PersonHasProgramme PP SET UserName = D."student login", UserNameDate = par_SourceDataDate
				FROM t$ReadingEggs2 D
				CROSS JOIN par_SyncUserNameTable SUN
-- 				JOIN dbo.PersonHasProgramme PP ON D.PersonID = PP.PersonID AND SUN.ProgrammeID = PP.ProgrammeID
				WHERE D.PersonID = PP.PersonID AND SUN.ProgrammeID = PP.ProgrammeID AND PP.UserName IS NULL; --OR PP.UserName <> D."Student Login")

-- 				SET par_RowCount = par_par_ROWCOUNT;
				GET DIAGNOSTICS par_RowCount = ROW_COUNT;
				par_LogDescription := FORMAT('Student Login updated (other Programmes) : %s', par_RowCount);  
				CALL internal.pDataImportLogInsert(par_DataImportID, par_LogDescription);
			END IF;

			CALL internal.pTemporalStatus ('dbo.PersonHasProgramme', 1,NULL, 1, par_StatusChanged => ptemporalstatus$par_StatusChanged, return_code => ptemporalstatus$ReturnCode);

		END IF;

-- End PersonHasProgramme and PersonExternalID where learner disappeared from file.
Raise Notice 'End PersonHasProgramme and PersonExternalID where learner disappeared from file.';
		CREATE TEMP TABLE t$EndPHP AS
		SELECT DISTINCT PP.*
		FROM dbo.PersonHasProgramme PP
		LEFT JOIN t$ReadingEggs2 RE ON PP.PersonID = RE.PersonID
		WHERE PP.ProgrammeID = 4
			AND RE.PersonID IS NULL
			AND PP.RegisterDate <= par_SourceDataDate
			AND PP.UserName IS NOT NULL
			AND lower(PP.username) NOT LIKE 'test%';
			--AND RE.LearnerManagementLevelID IN (2) -- Self Managed

-- 		SET par_RowCount = par_par_ROWCOUNT;
		GET DIAGNOSTICS par_RowCount = ROW_COUNT;
		par_LogDescription := FORMAT('RE Programme to end: %s', par_RowCount);
		CALL internal.pDataImportLogInsert(par_DataImportID, par_LogDescription);
		
		Raise notice 'CREATED TEMP TABLE t$EndPHP';
		IF par_RowCount > 0
		THEN
			DELETE
			FROM dbo.PersonExternalID ID
			where ID.PersonID IN (SELECT X.PersonID FROM t$EndPHP X ) AND  ID.PersonExternalIDType = 200;
-- 			JOIN t$EndPHP X ON ID.PersonID = X.PersonID
-- 			WHERE ID.PersonExternalIDType = 200;
			Raise notice 'DELETED FROM dbo.PersonExternalID';
-- 			SET par_RowCount = par_par_ROWCOUNT;
			GET DIAGNOSTICS par_RowCount = ROW_COUNT;
			par_LogDescription = FORMAT('RE Student ID cleared: %s', par_RowCount);
			CALL internal.pDataImportLogInsert(par_DataImportID, par_LogDescription);

			DELETE
			FROM dbo.PersonHasProgramme PP
			WHERE PP.PersonID IN (SELECT X.PersonID FROM t$EndPHP X)
			AND PP.ProgrammeID IN (SELECT  X.ProgrammeID FROM t$EndPHP X);
			--JOIN #EndPHP X ON PP.PersonID = X.PersonID AND PP.ProgrammeID = X.ProgrammeID
			Raise notice 'DELETED FROM dbo.PersonHasProgramme';
-- 			SET par_RowCount = par_par_ROWCOUNT;
			GET DIAGNOSTICS par_RowCount = ROW_COUNT;
			par_LogDescription := FORMAT('RE PersonHasProgramme ended: %s', par_RowCount);
			CALL internal.pDataImportLogInsert(par_DataImportID, par_LogDescription);
		END IF;

-- Insert Data
Raise Notice 'Insert Data';
		IF par_nodatainsert = 0::"bit"
		THEN
			INSERT INTO dbo.LearnerDataReadingEggs
			("date", "personid", "organisationid", "dataimportid", "sourcerownumber", "student grade", "teacher id", "teacher name", "re_lesson_placement", "re_lesson", "re_spelling", "re_certificates", "re_golden_eggs", "map 1 reading % score", "map 2 reading % score", "map 3 reading % score", "map 4 reading % score", "map 5 reading % score", "map 6 reading % score", "map 7 reading % score", "map 8 reading % score", "map 9 reading % score", "map 10 reading % score", "map 11 reading % score", "map 12 reading % score", "rex eggs", "rex_comp_placement", "rex_comp_lesson", "comprehension gym quiz average %", "rex_lesson_placement", "rex_lesson", "lexile gain", "last fiction assessment lexile", "fiction quiz average %", "last non fiction assessment lexile", "non fiction quiz average %", "rex_spelling_placement", "rex_spelling", "english skills spelling lessons completed", "english skills spelling quiz average %", "library books read", "library quiz average %", "rex_usage", "re sessions", "re time (sec)", "re_usage", "rex sessions", "rex time (sec)", "re_driving_test", "re_stories_written", "re_books_read", "re_reading_min_lexile", "re_reading_max_lexile", "rex_stadium_game_count", "rex_lessons_min_lexile", "rex_lessons_max_lexile", "fp_lesson_count", "fp_lesson_quiz_count", "fp_book_quiz_count", "fp time (sec)")
			SELECT
				par_SourceDataDate, 
				D.PersonID,
				1 "organisationid", 
				par_DataImportID,
				D.RowNumber,
				D."student grade",
				D."teacher id",
				D."teacher name",
				"re_lesson_placement", 
				"re_lesson", 
				"re_spelling", 
				"re_certificates", 
				"re_golden_eggs", 
				"map 1 reading % score", 
				"map 2 reading % score", 
				"map 3 reading % score", 
				"map 4 reading % score", 
				"map 5 reading % score", 
				"map 6 reading % score", 
				"map 7 reading % score", 
				"map 8 reading % score", 
				"map 9 reading % score", 
				"map 10 reading % score", 
				"map 11 reading % score", 
				"map 12 reading % score", 
				"rex eggs",
				"rex_comp_placement", 
				"rex_comp_lesson", 
				"comprehension gym quiz average %", 
				"rex_lesson_placement", 
				"rex_lesson", 
				"lexile gain", 
				"last fiction assessment lexile", 
				"fiction quiz average %", 
				"last non fiction assessment lexile", 
				"non fiction quiz average %", 
				"rex_spelling_placement", 
				"rex_spelling", 
				"english skills spelling lessons completed", 
				"english skills spelling quiz average %", 
				"library books read", 
				"library quiz average %", 
				"rex_usage", 
				"re sessions", 
				"re time (sec)", 
				"re_usage", 
				"rex sessions", 
				"rex time (sec)", 
				"re_driving_test", 
				"re_stories_written", 
				"re_books_read", 
				"re_reading_min_lexile", 
				"re_reading_max_lexile", 
				"rex_stadium_game_count", 
				"rex_lessons_min_lexile", 
				"rex_lessons_max_lexile", 
				"fp_lesson_count", 
				"fp_lesson_quiz_count", 
				"fp_book_quiz_count", 
				"fp time (sec)"
			FROM t$ReadingEggs2 D;

-- 			SET par_RowCount = par_par_ROWCOUNT;
			GET DIAGNOSTICS par_RowCount = ROW_COUNT;
			par_LogDescription = FORMAT('LearnerDataReadingEggs inserted: %s', par_RowCount);  
			CALL internal.pDataImportLogInsert(par_DataImportID, par_LogDescription);

			IF EXISTS(SELECT * FROM t$ReadingEggs2 WHERE FetchPreviousData = 1::BIT)
			THEN
				par_RowCount = (SELECT COUNT(*) FROM (SELECT DISTINCT PersonID, "student id" FROM t$ReadingEggs2 WHERE FetchPreviousData = 1) X);

				--RAISERROR('LearnerDataReadingEggs (Historic) inserting: %d', 0, 0, par_RowCount) WITH NOWAIT;

				INSERT INTO dbo.LearnerDataReadingEggs
				("date", "personid", "organisationid", "dataimportid", "sourcerownumber", "student grade", "teacher id", "teacher name", "re_lesson_placement", 
				"re_lesson", "re_spelling", "re_certificates", "re_golden_eggs", "map 1 reading % score", "map 2 reading % score", "map 3 reading % score", "map 4 reading % score", "map 5 reading % score", 
				"map 6 reading % score", "map 7 reading % score", "map 8 reading % score", "map 9 reading % score", "map 10 reading % score", "map 11 reading % score", "map 12 reading % score", "rex eggs", 
				"rex_comp_placement", "rex_comp_lesson", "comprehension gym quiz average %", "rex_lesson_placement", "rex_lesson", 
				"lexile gain", "last fiction assessment lexile", "fiction quiz average %", "last non fiction assessment lexile", "non fiction quiz average %", 
				"rex_spelling_placement", "rex_spelling", "english skills spelling lessons completed", "english skills spelling quiz average %", "library books read", "library quiz average %", "rex_usage",
				"re sessions", "re time (sec)", "re_usage", "rex sessions", "rex time (sec)", "re_driving_test", "re_stories_written", "re_books_read", "re_reading_min_lexile", "re_reading_max_lexile", "rex_stadium_game_count", "rex_lessons_min_lexile", "rex_lessons_max_lexile", "fp_lesson_count", "fp_lesson_quiz_count", "fp_book_quiz_count", "fp time (sec)")
				SELECT CAST(DI.DataContractDataDate AS date), P.PersonID, 1, D.DataImportID, D.RowNumber, 
					CASE
						--WHEN "Student Grade" = '0' THEN NULL
						WHEN D."student grade" = 'Rec' THEN 0
						ELSE CAST(D."student grade" AS smallint)
					END,
					D."teacher id", 
					TRIM(D."teacher name"), 
					D."re_lesson_placement", 
					D."re_lesson", 
					D."re_spelling", 
					D."re_certificates", 
					D."re_golden_eggs", 
					D."map 1 reading % score", 
					D."map 2 reading % score", 
					D."map 3 reading % score", 
					D."map 4 reading % score", 
					D."map 5 reading % score", 
					D."map 6 reading % score", 
					D."map 7 reading % score", 
					D."map 8 reading % score", 
					D."map 9 reading % score", 
					D."map 10 reading % score", 
					D."map 11 reading % score", 
					D."map 12 reading % score", 
					D."rex eggs", 
					D."rex_comp_placement", 
					D."rex_comp_lesson", 
					D."comprehension gym quiz average %", 
					D."rex_lesson_placement", 
					D."rex_lesson", 
					D."lexile gain", 
					D."last fiction assessment lexile", 
					D."fiction quiz average %", 
					D."last non fiction assessment lexile", 
					D."non fiction quiz average %", 
					D."rex_spelling_placement", 
					D."rex_spelling", 
					D."english skills spelling lessons completed", 
					D."english skills spelling quiz average %", 
					D."library books read", 
					D."library quiz average %", 
					D."rex_usage", 
					D."re sessions", 
					D."re time (sec)", 
					D."re_usage", 
					D."rex sessions", 
					D."rex time (sec)", 
					D."re_driving_test", 
					D."re_stories_written", 
					D."re_books_read", 
					D."re_reading_min_lexile", 
					D."re_reading_max_lexile", 
					D."rex_stadium_game_count", 
					D."rex_lessons_min_lexile", 
					D."rex_lessons_max_lexile", 
					D."fp_lesson_count", 
					D."fp_lesson_quiz_count", 
					D."fp_book_quiz_count", 
					D."fp time (sec)"
				FROM ( SELECT DISTINCT PersonID, "student id" FROM t$ReadingEggs2 WHERE FetchPreviousData = 1) P
				JOIN t$readingeggs2_staging D ON P."student id" = D."student id"
				JOIN dbo.dataimportdatacontract_staging DI ON D.DataImportID = DI.DataImportID AND DI.DataImportStatus = 'Success' AND DI.DataSourceTypeID = 2
				LEFT JOIN dbo.LearnerDataReadingEggs T ON P.PersonID = T.PersonID AND DI.DataContractDataDate = T."date"
				WHERE D.DataImportID <> var_SourceDataImportID
					AND T.PersonID IS NULL;

-- 				SET par_RowCount = par_par_ROWCOUNT;
				GET DIAGNOSTICS par_RowCount = ROW_COUNT;
				par_LogDescription = FORMAT('LearnerDataReadingEggs (Historic) inserted: %s', par_RowCount);
				CALL internal.pDataImportLogInsert(par_DataImportID, par_LogDescription);
			END IF;
		END IF;

		IF par_nodatainsert = 0::"bit" AND par_CalcWeekToT = 1::bit
		THEN
			CALL internal.pUpdateReadingEggsWeekToT(par_CalcDate := par_SourceDataDate);
		END IF;

		IF par_TranCountOnEntry = 0
		THEN
			--RAISERROR('COMMIT TRAN', 0, 0) WITH NOWAIT;
			COMMIT;
		END IF;
-- 	 EXCEPTION
--             WHEN OTHERS THEN
--                 IF par_TranCountOnEntry = 0 THEN
-- 					RAISE NOTICE 'ERROR';
--                     ROLLBACK;
--                 END IF;
	Drop table if exists par_SyncUserNameTable;
	Drop table if exists t$readingeggs2;
	Drop table if exists t$EndPHP;
	DROP TABLE IF EXISTS t$personhisttemp;
	DROP TABLE IF EXISTS t$readingeggs2_staging;

END;
$BODY$;
