CREATE PROCEDURE [tp2].[tpp_user_login] (
    @xusername NVARCHAR(510)
    ,@xpassword NVARCHAR(510)
    ,@xis_admin NVARCHAR(256)
    )
AS
DECLARE @xconvsys_date DATETIME
DECLARE @xxlog_attempts INT
DECLARE @xxexists INT
DECLARE @xxuser_id CHAR(20)
DECLARE @xxpassword NVARCHAR(510)
DECLARE @xxbad_login_ctr INT
DECLARE @xxlogin_ctr INT
DECLARE @xxsplit NVARCHAR(50)
DECLARE @xxterminated_on DATETIME
DECLARE @xxstatus INT
DECLARE @xxdummy INT
DECLARE @xxlogged_on DATETIME
DECLARE @xxacc_lock_period INT
DECLARE @xxlocked_out NVARCHAR(510)

SET @xconvsys_date = getdate()

SELECT @xxuser_id = e.id
    ,@xxpassword = e.password
    ,@xxsplit = e.split
    ,@xxterminated_on = terminated_on
FROM cmt_person e
WHERE e.username = upper(@xusername)

IF (@@rowcount = 0)
BEGIN
    SELECT @xxexists = 0
END

IF (@xxexists = 0)
BEGIN
    BEGIN
        IF (@xis_admin = 'true')
        BEGIN
            BEGIN
                SELECT @xxuser_id = 'emplo000000000000001'
                    ,@xxpassword = i.password
                    ,@xxsplit = 'domin000000000000001'
                FROM tpt_config_info i
            END
        END
        ELSE
        BEGIN
            RAISERROR 20000 'fg;21000'

            RETURN
        END
    END
END

IF (
        substring(@xxuser_id, 1, 5) = 'emplo'
        AND @xis_admin <> 'true'
        )
BEGIN
    IF (
            @xxterminated_on IS NOT NULL
            AND @xxterminated_on < getdate()
            )
    BEGIN
        RAISERROR (
                20000
                ,'fg;20957'
                ,1
                )

        RETURN
    END
END

EXEC fgp_policy_fetch 10046
    ,@xxsplit
    ,NULL
    ,NULL
    ,0
    ,@xxlog_attempts OUTPUT

IF (1 > 0)
BEGIN
    SELECT @xxbad_login_ctr = bad_login_ctr
        ,@xxlogin_ctr = login_ctr
        ,@xxlogged_on = logged_on
    FROM tpt_person_log
    WHERE id = @xxuser_id

    IF (@@rowcount = 0)
    BEGIN
        SELECT @xxbad_login_ctr = - 1
    END

    IF (@xxbad_login_ctr = - 1)
    BEGIN
        SELECT @xxdummy = @xxdummy

        BEGIN TRAN

        INSERT INTO tpt_person_log (
            id
            ,login_ctr
            ,bad_login_ctr
            ,logged_on
            ,flags
            )
        VALUES (
            @xxuser_id
            ,0
            ,0
            ,getdate()
            ,'1000000000'
            )

        SELECT @xxbad_login_ctr = 0

        SELECT @xxlogin_ctr = 0

        SELECT @xxlogged_on = getdate()

        COMMIT
    END

    IF (@xpassword <> @xxpassword)
    BEGIN
        SELECT @xxbad_login_ctr = @xxbad_login_ctr + 1
    END
    ELSE
    BEGIN
        IF (
                (
                    @xxbad_login_ctr < @xxlog_attempts
                    OR @xxlog_attempts IS NULL
                    )
                AND @xis_admin <> 'true'
                )
        BEGIN
            SELECT @xxlogin_ctr = @xxlogin_ctr + 1

            SELECT @xxbad_login_ctr = 0
        END
    END

    IF (
            @xxbad_login_ctr = @xxlog_attempts
            AND @xxbad_login_ctr <> 0
            )
    BEGIN
        SELECT @xxdummy = @xxdummy

        BEGIN TRAN

        EXEC fgp_notification_post @xxuser_id
            ,30002
            ,@xconvsys_date
            ,''
            ,@xxsplit

        COMMIT
    END

    IF (
            @xxbad_login_ctr >= @xxlog_attempts
            AND @xis_admin <> 'true'
            )
    BEGIN
        SELECT @xxlocked_out = 'true'

        EXEC fgp_policy_fetch 20013
            ,@xxsplit
            ,NULL
            ,NULL
            ,0
            ,@xxacc_lock_period OUTPUT

        SELECT @xxacc_lock_period = CASE
                WHEN @xxacc_lock_period = 0
                    THEN NULL
                ELSE @xxacc_lock_period
                END

        IF (@xxacc_lock_period IS NOT NULL)
        BEGIN
            IF (getdate() > dateadd(dd, @xxacc_lock_period, @xxlogged_on))
            BEGIN
                SELECT @xxdummy = @xxdummy

                SELECT @xxbad_login_ctr = 0

                SELECT @xxlocked_out = 'false'
            END
            ELSE
            BEGIN
                SELECT @xxlocked_out = 'true'
            END
        END
        ELSE
        BEGIN
            SELECT @xxlocked_out = 'true'
        END
    END

    SELECT @xxdummy = @xxdummy

    BEGIN TRAN

    UPDATE tpt_person_log
    SET bad_login_ctr = @xxbad_login_ctr
        ,login_ctr = @xxlogin_ctr
    WHERE id = @xxuser_id

    COMMIT

    IF (@xxlocked_out = 'true')
    BEGIN
        RAISERROR 20000 'fg;20959'

        RETURN
    END
END

IF (@xpassword <> @xxpassword)
BEGIN
    RAISERROR 20000 'fg;21000'

    RETURN
END

UPDATE tpt_person_log
SET logged_on = getdate()
WHERE id = @xxuser_id
GO