SQL QUERY :
SET NOCOUNT ON
DECLARE @CurrentUserTypeID INT = 1 ------ <---- Pass Current User Type
-----------------------------------
DECLARE @tblUserType TABLE
(
UserTypeID INT
, TypeName VARCHAR(50)
, MenuRights BIGINT
)
INSERT INTO @tblUserType
VALUES (1, 'Admin', 1)
, (2, 'GenralManager', 2)
, (3, 'SalesManager', 4)
, (4, 'SalesMan', 8)
, (5, 'Client', 16)
DECLARE @tblMenu TABLE
(
MenuID INT
, MenuName VARCHAR(50)
, MenuRights BIGINT
)
INSERT INTO @tblMenu
VALUES (1, 'Admin Setting', 1)
, (2, 'Genral Setting', 3)
, (3, 'Sales Report', 7)
, (4, 'Sales Order Folloup', 15)
, (5, 'Client FeedBack', 31)
DECLARE @UserRights BIGINT = 0
SELECT @UserRights = MenuRights
FROM @tblUserType
WHERE UserTypeID = @CurrentUserTypeID
SELECT *
FROM @tblMenu
WHERE MenuRights & @UserRights = @UserRights
Admin Menu List :
MenuID MenuName MenuRights
----------- -------------------------------------------------- --------------------
1 Admin Setting 1
2 Genral Setting 3
3 Sales Report 7
4 Sales Order Folloup 15
5 Client FeedBack 31
GenralManager Menu List :
MenuID MenuName MenuRights
----------- -------------------------------------------------- --------------------
2 Genral Setting 3
3 Sales Report 7
4 Sales Order Folloup 15
5 Client FeedBack 31
SalesManager Menu List :
MenuID MenuName MenuRights
----------- -------------------------------------------------- --------------------
3 Sales Report 7
4 Sales Order Folloup 15
5 Client FeedBack 31
No comments:
Post a Comment