SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_Roles_CreateUpdateDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_Roles_CreateUpdateDelete]
GO







-- sp_helptext cs_Roles_CreateUpdateDelete


CREATE PROCEDURE [dbo].cs_Roles_CreateUpdateDelete
(
	@RoleID	uniqueidentifier out,
	@DeleteRole	bit = 0,
	@Name		nvarchar(256) = '',
	@Description	nvarchar(512) = '',
	@SettingsID int

)
AS
DECLARE	@ApplicationName nvarchar(256)
Select @ApplicationName = ApplicationName from cs_SiteSettings where SettingsID = @SettingsID

DECLARE @ApplicationId UNIQUEIDENTIFIER
SELECT  @ApplicationId = NULL
SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName

if(@ApplicationId IS NULL)
RETURN
 

IF @RoleID is not null
Begin

	--validate application/role context
	IF NOT EXISTS(Select RoleId FROM aspnet_Roles where RoleId = @RoleID  and ApplicationId = @ApplicationId)
	RETURN


	-- Are we deleting the role?
	IF @DeleteRole = 1
	BEGIN
	
		-- delete all users in the role
		DELETE 
			aspnet_UsersInRoles
		WHERE 
			RoleId = @RoleID

		-- delete all product permissions using the role
		DELETE 
			cs_ProductPermissions
		WHERE 
			RoleID = @RoleID and SettingsID = @SettingsID
	
		-- delete all section permissions using the role
		DELETE 
			cs_SectionPermissions
		WHERE 
			RoleID = @RoleID and SettingsID = @SettingsID
	
	
		-- finally we can delete the actual role
		DELETE 
			aspnet_Roles
		WHERE 
			RoleId = @RoleID
	
	END
	ELSE
	BEGIN -- Are we updating a forum
		-- Update the role
		UPDATE 
			aspnet_Roles
		SET
			RoleName = @Name,
			LoweredRoleName = Lower(@Name),
			Description = @Description
		WHERE 
			RoleId = @RoleID
	END
END
ELSE
BEGIN

	-- Create a new Forum
	INSERT INTO 
		aspnet_Roles (
			RoleName, 
			LoweredRoleName,
			Description
			)
		VALUES (
			@Name,
			Lower(@Name),
			@Description
			)
	
	Select @RoleID = RoleId FROM aspnet_Roles where LoweredRoleName = Lower(@Name) and ApplicationId = @ApplicationId

END





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Roles_CreateUpdateDelete] to public
go