CREATE OR REPLACE function portal.puserredirectto(
    IN par_userid VARCHAR)

RETURNS TABLE (userid VARCHAR(50), programme varchar(10), url VARCHAR(50), urlparameters TEXT, "Default" smallint) 

AS 
$BODY$
DECLARE
    var_Sql VARCHAR(500);
    var_PersonID INTEGER;
    var_ProgrammeCode VARCHAR(10);
    var_Username VARCHAR(250);
    var_Password VARCHAR(250);
    var_SchoolID INTEGER;
    var_SchoolLearnerID BIGINT;
    var_RoleDemo NUMERIC(1, 0);
    var_Date DATE DEFAULT clock_timestamp();
    var_LearnerGradeID SMALLINT;
    var_Quiz VARCHAR(100);
    var_QuizInstanceID INTEGER;
    var_QuizSystem VARCHAR(50);
    var_URL VARCHAR(500);
    var_AlwaysQuiz INTEGER DEFAULT 0;
    var_FirstName VARCHAR(50);
    var_LastName VARCHAR(50);
    var_QuizInstanceIDID INTEGER;
    var_QuizIDID INTEGER;

BEGIN

    SELECT qi.quizinstanceid, qi.quizid
    INTO var_QuizInstanceIDID, var_QuizIDID
    FROM dbo.quizinstance qi
    WHERE var_Date BETWEEN qi.startdate AND qi.enddate
    ORDER BY qi.startdate NULLS FIRST, qi.quizinstanceid NULLS FIRST
    LIMIT 1;

RAISE NOTICE 'var_QuizInstanceIDID: %', var_QuizInstanceIDID; 
RAISE NOTICE 'var_QuizIDID: %', var_QuizIDID; 
RAISE NOTICE 'par_UserID: %', par_UserID; 

    /*
    [7810 - Severity CRITICAL - PostgreSQL doesn't support the SET NOCOUNT. If need try another way to send message back to the client application.]
    SET NOCOUNT ON;
    */
  --   var_Sql := 'SELECT 
		-- var_PersonID=PersonID,
		-- @ProgrammeCode=Programme,
		-- @Username=Username,
		-- @Password=Password,
		-- @SchoolID=SchoolID,
		-- @SchoolLearnerID=SchoolLearnerID,
		-- @RoleDemo=RoleDemo,
		-- @FirstName=FirstName,
		-- @LastName=LastName
	 -- FROM OPENROWSET(''SQLNCLI'',''Server=localhost;Trusted_Connection=Yes;'',''EXEC ClickDW.portal.pUserByUserID @UserID=''''' || CAST (par_UserID AS VARCHAR(50)) || ''''''')';

    SELECT u.personid, u.programme, u.username, u.password, u.schoolid, u.schoollearnerid, u.roledemo, u.firstname, u.lastname 
    from portal.pUserByUserID(par_UserID) AS u
    into
        var_PersonID,
        var_ProgrammeCode,
        var_Username,
        var_Password,
        var_SchoolID,
        var_SchoolLearnerID,
        var_RoleDemo,
        var_FirstName,
        var_LastName;


    /* PRINT @sql */
    
    /*
    [7672 - Severity CRITICAL - Automatic conversion of this command is not supported. Perform a manual conversion.]
    EXEC sp_executesql @stmt=@sql,
    		@params=N'@PersonID int OUTPUT, @ProgrammeCode varchar(10) OUTPUT, @Username nvarchar(250) OUTPUT, @Password nvarchar(250) OUTPUT, @SchoolID int OUTPUT, @SchoolLearnerID bigint OUTPUT, @RoleDemo bit OUTPUT, @FirstName varchar(50) OUTPUT, @LastName varchar(50) OUTPUT',
    		@PersonID=@PersonID OUTPUT, @ProgrammeCode=@ProgrammeCode OUTPUT, @Username=@Username OUTPUT, @Password=@Password OUTPUT, @SchoolID=@SchoolID OUTPUT, @SchoolLearnerID=@SchoolLearnerID OUTPUT, @RoleDemo=@RoleDemo OUTPUT, @FirstName=@FirstName OUTPUT, @LastName=@LastName OUTPUT;
    */

RAISE NOTICE 'var_PersonID: %', var_PersonID; 

    SELECT
        gradeid
        INTO var_LearnerGradeID
        FROM dbo.schoollearneryear AS sly
        WHERE sly.schoollearnerid = var_SchoolLearnerID AND sly.year = date_part('year', var_Date);

    IF var_RoleDemo = 1 AND LOWER(var_LastName) = LOWER('QuizUser') AND LOWER(var_FirstName) LIKE LOWER('%Multi') THEN
        var_AlwaysQuiz := 1;
    END IF;
    -- RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %. Argument: %. Argument: %. Argument: %', '50000', 0, 0, '@SchoolID=%d, @GradeID=%d, @AlwaysQuiz=%d, @PersonID=%d', var_SchoolID, var_LearnerGradeID, var_AlwaysQuiz, var_PersonID USING ERRCODE = '50000';
    /* TOP 1 */

    SELECT
        qi.name, qi.quizinstanceid, qs.sourcesystem, qs.sourcequizurl
        INTO var_Quiz, var_QuizInstanceID, var_QuizSystem, var_URL
        FROM dbo.quizinstance AS qi
        JOIN dbo.quiz AS q
            ON qi.quizid = q.quizid
        JOIN dbo.quizinstancesource AS qs
            ON qi.quizinstanceid = qs.quizinstanceid AND var_Date BETWEEN qs.startdate AND qs.enddate
        JOIN dbo.quizinstanceschoolgrade AS qisg
            ON qi.quizinstanceid = qisg.quizinstanceid AND var_Date BETWEEN qisg.startdate AND qisg.enddate AND qisg.schoolid = var_SchoolID AND qisg.gradeid = var_LearnerGradeID
        CROSS JOIN (SELECT
            COUNT(*) AS gradecount
            FROM dbo.quizinstanceschoolgrade AS x
            WHERE x.quizinstanceid = var_QuizInstanceIDID AND x.schoolid = var_SchoolID) AS qis
        CROSS JOIN (SELECT
            COUNT(*) AS donecount
            FROM dbo.learnerdataquiz AS d
            WHERE var_AlwaysQuiz = 0 AND d.quizid = var_QuizIDID AND d.quizinstanceid = var_QuizInstanceIDID AND d.personid = var_PersonID AND d.date <= var_Date AND d.complete = '1') AS lq
        WHERE var_Date BETWEEN qi.startdate AND qi.enddate AND
        /* AND ((QIS.GradeCount = 0) OR (QIS.GradeCount > 0 AND QISG.QuizInstanceID IS NOT NULL)) */
        lq.donecount = 0 AND qs.sourcequizurl IS NOT NULL
        ORDER BY qi.startdate NULLS FIRST, qi.quizinstanceid NULLS FIRST;
    -- RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %', '50000', 0, 0, 'QuizInstance results: %d', "var_@ROWCOUNT" USING ERRCODE = '50000';
    RAISE NOTICE '%', 'quiz: ' || var_Quiz;

    IF var_Quiz IS NOT NULL THEN
        RETURN QUERY
        SELECT
            CAST (par_UserID AS VARCHAR(50)) AS userid, 'QUIZ' AS programme, var_URL AS url, 'id=' || CAST (var_QuizInstanceID AS VARCHAR(20)) || '&userID=' || par_UserID AS urlparameters, aws_sqlserver_ext.tomsbit(0) AS "Default";
    ELSE
        RETURN QUERY
        SELECT
            CAST (par_UserID AS VARCHAR(50)) AS userid, var_ProgrammeCode AS programme, p.loginurl AS url, '' AS urlparameters, aws_sqlserver_ext.tomsbit(1) AS "Default"
            FROM dbo.programme AS p
            WHERE LOWER(p.code) = LOWER(var_ProgrammeCode);
    END IF;
    /*
    SELECT
    FROM PersonHasProgramme PP
    JOIN Programme P ON PP.ProgrammeID = P.ProgrammeID
    WHERE PP.UserID = @UserID
    
    SELECT
    	CAST(U.UserID AS varchar(50)) 'ID',
    	U.PersonID				'PersonID',
    	PROG.Code				'Programme',
    	U.UserName				'Username',
    	CASE
    		WHEN U.[Password] IS NOT NULL THEN U.[Password]
    		WHEN PE.LearnerPassword IS NOT NULL THEN PE.LearnerPassword
    		ELSE NULL
    	END						'Password',
    	P.FirstName				'FirstName',
    	P.LastName				'LastName',
    	CAST(CASE WHEN U.SysEndTime = '9999-12-31 23:59:59' THEN 1 ELSE 0 END AS bit) ActiveStatus,
    	SL.SchoolID				SchoolID
    FROM PersonHasProgramme U
    JOIN Programme PROG ON U.ProgrammeID = PROG.ProgrammeID
    JOIN Person P ON U.PersonID = P.PersonID
    JOIN SchoolLearner SL ON P.PersonID = SL.PersonID AND GETDATE() BETWEEN SL.StartDate AND SL.EndDate
    JOIN SchoolEnvironment SE ON SL.SchoolID = SE.SchoolID
    JOIN ProgrammeEnvironment PE ON SE.EnvironmentID = PE.EnvironmentID AND PROG.ProgrammeID = PE.ProgrammeID AND PE.ImplementationStatusID = 4
    WHERE U.UserID = @UserID
    ORDER BY
    	U.SysEndTime DESC;
    */

END;
$BODY$
LANGUAGE plpgsql;