dbo.usp_GetManageRights.sql
3.64 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
USE [AIADatabaseV5]
GO
/****** Object: StoredProcedure [dbo].[usp_GetManageRights] Script Date: 2/15/2018 11:39:56 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_GetManageRights] --5615,'General Admin'
@UserId int,
@RoleName varchar(100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @RoleId int
Set @RoleId=(Select Id from UserType Where Title=@RoleName)
-- Insert statements for procedure here
Select Tablequery.Id, Tablequery.Title,Tablequery.ParentId,Tablequery.Priority ,(case when (SELECT Count(A1.Id)
FROM Activity AS A1 INNER JOIN
AIAUserActivity AS UA ON A1.Id = UA.ActivityId
WHERE UA.UserId = @UserId AND UA.RoleId = @RoleId AND
A1.ParentId = Tablequery.Id)>0 then '1' else '0' end) as MenuStatus from
(SELECT Id, Title,ParentId,Priority FROM Activity
WHERE IsActive = 1 AND ParentId=0 AND Title NOT IN ('Product','Logout')
ANd Id NOT IN (SELECT DISTINCT A.ParentId FROM Activity A, RoleToActivity RA Where A.Id=RA.ActivityId AND RA.RoleId = 2)
) as Tablequery ORDER BY Tablequery.ParentId, Tablequery.Priority
--SELECT Id, Title,ParentId,[Priority],(case when (SELECT Count(A1.Id)
--FROM Activity AS A1 INNER JOIN
-- AIAUserActivity AS UA ON A1.Id = UA.ActivityId
--WHERE UA.UserId = @UserId AND UA.RoleId = @RoleId AND
--A1.ParentId = a2.Id)>0 then '1' else '0' end) as MenuStatus FROM Activity a2
--WHERE a2.IsActive = 1 AND a2.ParentId=0 AND a2.Title NOT IN ('Product','Logout')
--ANd a2.Id NOT IN (SELECT DISTINCT A.ParentId FROM Activity A, RoleToActivity RA Where A.Id=RA.ActivityId AND RA.RoleId = 2)
--ORDER BY a2.ParentId, a2.Priority
END