spAddUpdate_ClientUsers

Parameters

Parameter	Parameter Type	Mode
@PKID	int	INOUT
@CurrentUsersID	int	IN
@Password	nvarchar(255)	IN
@UserName	nvarchar(50)	IN
@EMail	nchar	IN
@Active	bit	IN
@LastName	nvarchar(50)	IN
@FirstName	nvarchar(50)	IN
@MiddleInitial	nvarchar(5)	IN
@InternalUser	bit	IN
@ClientsID	int	IN
@BackofficeID	int	IN
@BackOfficeSysID	int	IN
@RegistrationNumber	nvarchar(50)	IN
@ReminderPhrase	nvarchar(150)	IN
@IsSupervisor	bit	IN
@IsProvisional	bit	IN
@TimeZoneID	int	IN
@Guid	uniqueidentifier	INOUT
Definition

Copy


    CREATE PROCEDURE [dbo].[spAddUpdate_ClientUsers]
            (
                @PKID        int output,
                @CurrentUsersID    int,        
                @Password    nvarchar(255),
                @UserName    nvarchar(50),
                @EMail        nchar(100),
                @Active        bit,
                @LastName    nvarchar(50),
                @FirstName    nvarchar(50),
                @MiddleInitial    nvarchar(5),
                @InternalUser    bit,
                @ClientsID    int,
                @BackofficeID    int,
                @BackOfficeSysID    int,
                @RegistrationNumber    nvarchar(50),
                @ReminderPhrase    nvarchar(150),
                @IsSupervisor    bit,
                @IsProvisional bit,
                @TimeZoneID int,
                @Guid uniqueidentifier output
            )
            AS
            BEGIN
                set nocount on

                declare @UserID int

                if @CurrentUsersID <= 0
                    set @UserID = null
                else
                    set @UserID = @CurrentUsersID

                if @TimeZoneID = 0
                    set @TimeZoneID = null
                if (@PKID<=0) 

                begin

                    INSERT INTO ClientUsers
                    (
                        Password    ,
                        UserName    ,
                        EMail        ,
                        Active        ,
                        LastName    ,
                        FirstName    ,
                        MiddleInitial    ,
                        InternalUser    ,
                        ClientsID    ,
                        RegistrationNumber    ,
                        ReminderPhrase,
                        IsSupervisor,
                        IsProvisional,
                        TimeZoneID,
                        CreateID,
                        UpdateID
                    )

                    VALUES
                    (
                        @Password    ,
                        @UserName    ,
                        @EMail        ,
                        @Active        ,
                        @LastName    ,
                        @FirstName    ,
                        @MiddleInitial    ,
                        @InternalUser    ,
                        @ClientsID    ,
                        @RegistrationNumber    ,
                        @ReminderPhrase,
                        @IsSupervisor,
                        @IsProvisional,
                        @TimeZoneID,
                        @UserID,
                        @UserID
                    )

                    SELECT @PKID = SCOPE_IDENTITY()

                    if @UserID is null
                        update ClientUsers 
                        set CreateID = @PKID, UpdateID = @PKID 
                        where ID = @PKID         

                    if CHARINDEX('SocialWebIndicator!', @UserName ) > 0 -- seeing this unique flag, change username to "SocialWeb_<PKID>"

                    begin
                        declare @newname as varchar(100) = 'SocialWeb_' + CONVERT(varchar(15), @PKID)

                        -- if my favorite username taken?

                        if exists(select * from ClientUsers where UserName = @newname)
                            set @newname = 'SocialWeb_' + CONVERT(varchar(50), NEWID()) -- now it's got to be unique


                        update ClientUsers
                        set UserName = @newname
                        where ID = @PKID
                    end

                    select @Guid = Guid from ClientUsers where ID = @PKID

                    exec spAuditThis @CurrentUsersID, 1, @Guid, 6

                    end 

                else 
                begin

                    UPDATE ClientUsers SET
                    Password=@Password    ,
                    UserName=@UserName    ,
                    EMail=@EMail        ,
                    Active=@Active        ,
                    LastName=@LastName    ,
                    FirstName=@FirstName    ,
                    MiddleInitial=@MiddleInitial    ,
                    InternalUser=@InternalUser    ,
                    ClientsID=@ClientsID    ,
                    RegistrationNumber=@RegistrationNumber    ,
                    ReminderPhrase = @ReminderPhrase,
                    IsSupervisor=@IsSupervisor,
                    IsProvisional = @IsProvisional,
                    TimeZoneID = @TimeZoneID,
                    UpdateID = @UserID,
                    UpdateDate = getutcdate()
                    WHERE ID=@PKID

                    select @Guid = Guid from ClientUsers where ID = @PKID

                    exec spAuditThis @CurrentUsersID, 2, @Guid, 6

                    exec [dbo].[sp_ClientUserPasswordHistoryCleanup] @PKID

                end

                if (@BackOfficeID > 0) 
                begin
                    declare @BackOfficeSystemPeopleID int
                    declare @BackOfficeSystemUsersID int
                    exec [dbo].[spGetBackOfficeSystemPeopleID] @BackOfficeID, @BackOfficeSysID, @BackOfficeSystemPeopleID OUTPUT
                    exec [dbo].[spGetBackOfficeSystemUsersID] @PKID, @BackOfficeSystemPeopleID, @BackOfficeSystemUsersID OUTPUT
                end
            end