-- exec cs_Section_Permissions_Get 1000, 0
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

create procedure [dbo].cs_Section_Permissions_Get
(
	@SettingsID int,
	@SectionID int = 0,
	@ApplicationType smallint
)
AS


SET Transaction Isolation Level Read UNCOMMITTED

IF ( @SectionID = 0 ) 
	-- Return product permissions
	SELECT
		P.SettingsID, 
		Implied = convert(bit, 1), 
		SectionID = -1, 
		ApplicationKey = null,
		R.RoleName, 
		P.RoleID, 
		AllowMask = convert(bigint, coalesce(P.AllowMask, 0x00)), 
		DenyMask = convert(bigint, coalesce(P.DenyMask, 0x00)) 
	FROM
		cs_ProductPermissions P,
		aspnet_Roles R
	WHERE 
		P.RoleID = R.RoleId AND
		P.ApplicationType = @ApplicationType AND
		P.SettingsID = @SettingsID

	UNION

	-- Return section permissions
	SELECT
		P.SettingsID, 
		Implied = convert(bit, 0), 
		P.SectionID, 
		F.ApplicationKey,
		R.RoleName, 
		P.RoleID, 
		AllowMask = convert(bigint, coalesce(P.AllowMask, 0x00)),  
		DenyMask = convert(bigint, coalesce(P.DenyMask, 0x00))  
	FROM 
		cs_sectionpermissions P,
		aspnet_Roles r,
		cs_Sections F
	WHERE
		P.RoleID = r.RoleId AND
		P.SettingsID = @SettingsID AND
		P.SectionID = F.SectionID AND
		F.ApplicationType = @ApplicationType AND
		P.SettingsID = @SettingsID

ELSE IF (@SectionID > 0)
 
	-- Return product permissions
	SELECT
		P.SettingsID, 
		Implied = convert(bit, 1), 
		SectionID = -1, 
		ApplicationKey = null,
		R.RoleName, 
		P.RoleID, 
		AllowMask = convert(bigint, coalesce(P.AllowMask, 0x00)), 
		DenyMask = convert(bigint, coalesce(P.DenyMask, 0x00)) 
	FROM
		cs_ProductPermissions P,
		aspnet_Roles R
	WHERE 
		P.RoleID = R.RoleId AND
		P.ApplicationType = @ApplicationType AND
		P.SettingsID = @SettingsID

	UNION
	
	-- Return section permissions
	SELECT
		P.SettingsID, 
		Implied = convert(bit, 0), 
		P.SectionID, 
		F.ApplicationKey,
		R.RoleName, 
		P.RoleID, 
		AllowMask = convert(bigint, coalesce(P.AllowMask, 0x00)),  
		DenyMask = convert(bigint, coalesce(P.DenyMask, 0x00))  
	FROM 
		cs_sectionpermissions P,
		aspnet_Roles r,
		cs_Sections F
	WHERE
		P.RoleID = r.RoleId AND
		P.SettingsID = @SettingsID  AND
		P.SectionID = @SectionID  AND
		P.SectionID = F.SectionID AND
		F.ApplicationType = @ApplicationType AND
		P.SettingsID = @SettingsID
	
ELSE

	-- Return product permissions
	SELECT
		P.SettingsID, 
		Implied = convert(bit, 1), 
		SectionID = -1, 
		R.RoleName, 
		R.RoleId, 
		AllowMask = convert(bigint, coalesce(P.AllowMask, 0x00)), 
		DenyMask = convert(bigint, coalesce(P.DenyMask, 0x00)) 
	FROM		
		aspnet_Roles R
		inner join aspnet_Applications A on R.ApplicationId = A.ApplicationId
		inner join cs_SiteSettings ss on A.ApplicationName = ss.ApplicationName
		left outer join cs_ProductPermissions P on P.RoleID = R.RoleId and P.ApplicationType = @ApplicationType and P.SettingsID = @SettingsID
	WHERE 
		
		ss.SettingsID = @SettingsID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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