-- FUNCTION: internal.pupdatelearnerfromreadingeggsf(bigint, character varying, integer)

-- DROP FUNCTION IF EXISTS internal.pupdatelearnerfromreadingeggsf(bigint, character varying, integer);

CREATE OR REPLACE FUNCTION internal.pupdatelearnerfromreadingeggsf(
	par_dataimportid bigint,
	par_programmecode character varying DEFAULT NULL::character varying,
	par_debugmode integer DEFAULT 0)
    RETURNS SETOF refcursor 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$

DECLARE
    var_TranCountOnEntry INTEGER
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the @@TRANCOUNT function. Create a user-defined function.]
    =  @@TRANCOUNT
    */;
    var_SourceDataImportID BIGINT DEFAULT (SELECT
        sourcedataimportid
        FROM internal.dataimport
        WHERE dataimportid = par_DataImportID);
    var_SourceDataDate DATE;
    var_StartDate DATE;
    var_SourceDataDateS VARCHAR(20);
    var_SourceDataDateYear SMALLINT;
    var_ProgrammeID SMALLINT DEFAULT 4;
    var_PersonExternalIDType SMALLINT DEFAULT 200;
    
BEGIN

	DROP TABLE IF EXISTS t$readingeggstemp;
	DROP TABLE IF EXISTS t$updatelearnerfromsource;
	
    SELECT
    		 DataContractDataDate,
    		DataContractDataDate - INTERVAL '6 day',  
    		date_part('year',DataContractDataDate),
    		to_char(var_SourceDataDate, 'yyyy-MM-dd') INTO var_SourceDataDate,var_StartDate,var_SourceDataDateYear,var_SourceDataDateS
    	FROM dbo.dataimportdatacontract_staging
    	WHERE DataImportID = var_SourceDataImportID;
		
	RAISE NOTICE 'var_SourceDataImportID: %',var_SourceDataImportID;
	RAISE NOTICE 'var_SourceDataDate: %',var_SourceDataDate;
  	Raise Notice 'Create temp';
    BEGIN
	
		CREATE TEMP TABLE t$readingeggstemp
		as
		SELECT
		*
		FROM dbo.readingeggs2_staging RE
		WHERE RE.DataImportID = var_SourceDataImportID;
	
	RAISE NOTICE 'CREATE TEMP READINGEGGS';
	
        CREATE TEMP TABLE t$updatelearnerfromsource AS
          SELECT
        			RE."student id"::varchar ExternalID,
        			RE."student first name"				FirstName,
        			RE."student last name"				LastName,
        			RE."student login"					UserName,
        			CASE WHEN RE."student grade" = 'Rec' THEN 0 ELSE NULLIF(RE."student grade",'-')::smallint END GradeID,
        			RE."school code"					ProgrammeCode,
        			RE."teacher name"					SourceClassName,
        			CASE
        				WHEN RE."teacher name" IS NULL THEN NULL
        				WHEN RE."teacher name" = S.Name THEN RE."teacher name"
        				WHEN strpos(S.Name, RE."teacher name") > 0 THEN TRIM(REPLACE(RE."teacher name",S.Name,''))
        				ELSE RE."teacher name"
        			END									ClassName,
        			REID.PersonID						PersonID,
        			PE.ProgrammeEnvironmentID			ProgrammeEnvironmentID
        		FROM t$readingeggstemp RE
        		-- JOIN ProgrammeEnvironment FOR SYSTEM_TIME AS OF var_SourceDataDate PE ON RE."School code" = PE.ProgrammeCode AND PE.ProgrammeID = var_ProgrammeID AND PE.ImplementationStatusID = 4
        		-- JOIN SchoolEnvironment FOR SYSTEM_TIME AS OF var_SourceDataDate SE ON PE.EnvironmentID = SE.EnvironmentID
				JOIN dbo.ProgrammeEnvironment PE ON RE."school code" = PE.ProgrammeCode AND PE.ProgrammeID = var_ProgrammeID AND PE.ImplementationStatusID = 4
        		JOIN dbo.SchoolEnvironment SE ON PE.EnvironmentID = SE.EnvironmentID
        		JOIN dbo.School S ON SE.SchoolID = S.SchoolID
        		--JOIN ProgrammeEnvironmentHasTargetGrade FOR SYSTEM_TIME AS OF @SourceDataDate TG ON PE.ProgrammeEnvironmentID = TG.ProgrammeEnvironmentID AND CASE WHEN RE."Student Grade" = 'Rec' THEN 0 ELSE TRY_CAST(RE."Student Grade" AS smallint) END = TG.GradeID AND @SourceDataDate >= TG.ActiveFrom
				JOIN dbo.ProgrammeEnvironmentHasTargetGrade TG ON PE.ProgrammeEnvironmentID = TG.ProgrammeEnvironmentID AND CASE WHEN RE."student grade" = 'Rec' THEN 0 ELSE NULLIF(RE."student grade",'-')::smallint END = TG.GradeID AND var_SourceDataDate >= TG.ActiveFrom
        		LEFT JOIN dbo.PersonExternalID REID ON CAST(RE."student id" AS varchar(50)) = REID.ExternalID AND REID.PersonExternalIDType = var_PersonExternalIDType
--         		WHERE RE.DataImportID = var_SourceDataImportID
--         			AND
				WHERE RE."student first name" NOT LIKE 'Demo Student %' AND RE."student login" NOT LIKE 'demo!_student!_%' ESCAPE '!' AND RE."student login" NOT LIKE 'demostudent%'
        			AND TG.LearnerManagementLevelID = 2 -- Self Managed grade
					AND ((par_programmecode IS NULL) OR (par_programmecode IS NOT NULL AND RE."school code" = par_programmecode)); 
  		Raise Notice 'calling SP';
        /* SELECT * FROM #UpdateLearnerFromSource */
	RETURN QUERY
       SELECT * 
	   FROM internal.pupdatelearnerfromsourcef(par_DataImportID := par_DataImportID, par_StartDate := var_StartDate, par_Date := var_SourceDataDate, par_ExternalIDType := var_PersonExternalIDType, par_ProgrammeID := var_ProgrammeID, par_ProgrammeCode := par_ProgrammeCode, par_DebugMode := par_DebugMode);   

        
--         EXCEPTION
--             WHEN OTHERS THEN
--                     --ROLLBACK;
-- 					Raise Notice 'Error';
		
    END;
    
    DROP TABLE IF EXISTS t$updatelearnerfromsource;
 
END;
$BODY$;

ALTER FUNCTION internal.pupdatelearnerfromreadingeggsf(bigint, character varying, integer)
    OWNER TO postgres;
