CREATE OR REPLACE function portal.puserbyusernamerecord(
    IN par_username VARCHAR, 
    IN par_programmecode VARCHAR DEFAULT null,
    IN par_activeonly BOOLEAN  DEFAULT true) RETURNS TABLE (ID VARCHAR(50), PersonID int, Programme varchar(10), Username VARCHAR(50),
                                                        Password VARCHAR(50), 
            FirstName VARCHAR(50), LastName VARCHAR(50), ActiveStatus smallint, SchoolID int, SchoolLearnerID bigint, RoleDemo smallint) 
AS 
$BODY$
/* DROP PROC portal.pUserByUserNameTest; */
DECLARE
    var_FilterProgrammeID SMALLINT DEFAULT NULL;
    var_UserID UUID;
    var_Password VARCHAR(250);
    var_PersonID INTEGER;
    var_ProgrammeID SMALLINT;
    var_SysStartTime TIMESTAMP(0) WITHOUT TIME ZONE;
    var_SysEndTime TIMESTAMP(0) WITHOUT TIME ZONE;

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;
    */
    BEGIN
        /*
        [7674 - Severity CRITICAL - Automatic conversion of TRANSACTION_ISOLATION_LEVEL clause of SET statement is not supported. Perform a manual conversion.]
        SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
        */
        IF par_ProgrammeCode IS NOT NULL THEN
            var_FilterProgrammeID := (SELECT
                programmeid
                FROM dbo.programme
                WHERE LOWER(code) = LOWER(par_ProgrammeCode));
        END IF;

        IF LOWER(par_UserName) <> LOWER('') THEN
            SELECT
                pp.userid, pp.personid, pp.programmeid, pp.username, pp.password, pp.sysstarttime, pp.sysendtime
                INTO var_UserID, var_PersonID, var_ProgrammeID, par_UserName, var_Password, var_SysStartTime, var_SysEndTime
                FROM dbo.personhasprogramme AS pp
                WHERE ((var_FilterProgrammeID IS NULL) OR (var_FilterProgrammeID IS NOT NULL AND pp.programmeid = var_FilterProgrammeID)) AND LOWER(pp.username) = LOWER(par_UserName)
                ORDER BY pp.sysstarttime NULLS FIRST
                LIMIT 1;            

            IF var_PersonID IS NULL THEN
                SELECT
                    pp.userid, pp.personid, pp.programmeid, pp.username, pp.password, pp.sysstarttime, pp.sysendtime
                    INTO var_UserID, var_PersonID, var_ProgrammeID, par_UserName, var_Password, var_SysStartTime, var_SysEndTime
                    FROM dbo.personhasprogrammehistory AS pp
                    WHERE ((var_FilterProgrammeID IS NULL) OR (var_FilterProgrammeID IS NOT NULL AND pp.programmeid = var_FilterProgrammeID)) AND LOWER(pp.username) = LOWER(par_UserName)
                    ORDER BY pp.sysendtime DESC NULLS FIRST
                    LIMIT 1;
            END IF;
        END IF;

        RAISE NOTICE '%', var_PersonID;        
        
        RETURN QUERY
        SELECT
            CAST (var_UserID AS VARCHAR(50)) AS "ID", var_PersonID AS "PersonID", prog.code AS "Programme", par_UserName AS "Username",
            CASE
                WHEN pe.learnerpassword IS NOT NULL THEN pe.learnerpassword
                WHEN var_Password IS NOT NULL THEN var_Password
                ELSE NULL
            END AS "Password", p.firstname AS "FirstName", p.lastname AS "LastName", aws_sqlserver_ext.tomsbit(CASE
                WHEN var_SysEndTime = '9999-12-31 23:59:59' THEN 1
                ELSE 0
            END) AS "ActiveStatus", sl.schoolid AS "SchoolID", sl.schoollearnerid AS "SchoolLearnerID", aws_sqlserver_ext.tomsbit(CASE
                WHEN rdemo.personroleid IS NOT NULL THEN 1
                ELSE 0
            END) AS "RoleDemo"
            FROM dbo.person AS p
            JOIN dbo.programme AS prog
                ON prog.programmeid = var_ProgrammeID
            CROSS JOIN (SELECT
                sl.schoolid, sl.schoollearnerid
                FROM dbo.schoollearner AS sl
                JOIN dbo.schoollearneryear AS sly
                    ON sl.schoollearnerid = sly.schoollearnerid
                WHERE sl.personid = var_PersonID AND clock_timestamp() BETWEEN sl.startdate AND sl.enddate
                ORDER BY sly.year DESC NULLS FIRST, sl.startdate DESC NULLS FIRST, sl.schoollearnerid DESC NULLS FIRST
                LIMIT 1) AS sl
            LEFT OUTER JOIN dbo.schoolenvironment AS se
                ON sl.schoolid = se.schoolid
            LEFT OUTER JOIN dbo.programmeenvironment AS pe
                ON se.environmentid = pe.environmentid AND prog.programmeid = pe.programmeid AND pe.implementationstatusid IN (4, 13)
            LEFT OUTER JOIN dbo.personrolehasperson AS rdemo
                ON p.personid = rdemo.personid AND rdemo.personroleid = 4
            WHERE p.personid = var_PersonID;

        /*
        [7674 - Severity CRITICAL - Automatic conversion of TRANSACTION_ISOLATION_LEVEL clause of SET statement is not supported. Perform a manual conversion.]
        SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
        */
        EXCEPTION
            WHEN OTHERS THEN
                /*
                [7674 - Severity CRITICAL - Automatic conversion of TRANSACTION_ISOLATION_LEVEL clause of SET statement is not supported. Perform a manual conversion.]
                SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
                */
                RAISE;
    END;

END;
$BODY$
LANGUAGE plpgsql;