spAddUpdate_ClientRoles

Parameters

Parameter	Parameter Type	Mode
@PKID	int	INOUT
@CurrentUsersID	int	IN
@Name	nvarchar(50)	IN
@SystemRole	bit	IN
@SiteAdminRole	bit	IN
@EveryoneRole	bit	IN
@ClientsID	int	IN
@Cascading	bit	IN
@REQueryID	int	IN
@REQueryName	nvarchar(50)	IN
@BBSystem	int	IN
@REConstitOnly	bit	IN
@RolePrivsToClone	int	IN
@Guid	uniqueidentifier	INOUT
@ImageID	int	IN
Definition

Copy

                CREATE   PROCEDURE [dbo].[spAddUpdate_ClientRoles]
                (
                    @PKID        int output,
                    @CurrentUsersID    int,        
                    @Name        nvarchar(50),
                    @SystemRole        bit,
                    @SiteAdminRole  bit,
                    @EveryoneRole   bit,
                    @ClientsID        int,
                    @Cascading        bit,
                    @REQueryID int,
                    @REQueryName nvarchar(50),
                    @BBSystem int,
                    @REConstitOnly    bit,
                    @RolePrivsToClone int,
                    @Guid uniqueidentifier output,
                    @ImageID int
                )
                AS

                begin transaction

                if (@PKID<=0) begin

                    INSERT INTO ClientRoles
                    (
                        Name,
                        SystemRole,
                        SiteAdminRole,
                        EveryoneRole,
                        ClientsId,
                        OwnerID,
                        Cascading,
                        REQueryID,
                        REQueryName,
                        BBSystem,
                        REConstituentsOnly,
                        ImageID,
                        Guid
                    )

                    VALUES
                    (
                        @Name,
                        @SystemRole,
                        @SiteAdminRole,
                        @EveryoneRole,
                        @ClientsID,
                        @CurrentUsersID,
                        @Cascading,
                        @REQueryID,
                        @REQueryName,
                        @BBSystem,
                        @REConstitOnly,
                        @ImageID,
                        isnull(nullif(@Guid, '00000000-0000-0000-0000-000000000000'), newid())
                    )

                    SELECT @PKID = @@Identity

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

                    exec spAuditThis @CurrentUsersID, 1, @Guid, 7


                    if (@RolePrivsToClone>0) begin
                        exec spCloneRolePrivs @RolePrivsToClone, @PKID
                    end

                    end 

                else 

                    begin

                    DECLARE @CurrentQueryID int

                    SELECT @CurrentQueryID = REQueryID 
                    FROM ClientRoles where ID = @PKID
                    --sterling CR331908-100810 we need to update the query ID on the email list for any Chapter that mey be using this Role to define its membership
                    UPDATE EmailList_Query
                    SET QueryID = @REQueryID, QueryName = @REQueryName
                    FROM EmailList_Query elq
                    INNER JOIN Chapters c on c.MemberEmailListID = elq.EmailListID and elq.QueryID = @CurrentQueryID

                    UPDATE ClientRoles SET
                        Name = @Name,
                        SystemRole = @SystemRole,
                        SiteAdminRole = @SiteAdminRole,
                        EveryoneRole = @EveryoneRole,
                        ClientsID = @ClientsID,
                        Cascading = @Cascading,
                        REQueryID=@REQueryID,
                        REQueryName=@REQueryName,
                        BBSystem=@BBSystem,
                        REConstituentsOnly = @REConstitOnly,
                        ImageID = @ImageID
                    WHERE ID=@PKID

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

                    exec spAuditThis @CurrentUsersID, 2, @Guid, 7

                    end

                -- update clientusers set MembershipRefreshedOn=NULL WHERE clientsid=@ClientsID
                commit transaction