USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[usp_GetManageRights]    Script Date: 2/1/2018 12:15:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ====================================================  
-- Author:  Magic Software  
-- Create date: 19-Jan-2018  
-- Description: To get all the license types
-- ====================================================

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_GetManageRights]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_GetManageRights]
GO
  
CREATE 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


GO