USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[GetModulesByUserId]    Script Date: 2/1/2018 12:15:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Magic Software
-- Create date: 12-May-2009
-- Description:	To get the list of blocked user who have attempt 5 times wrong login
-- =============================================

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

CREATE PROCEDURE [dbo].[GetModulesByUserId] 
	-- Add the parameters for the stored procedure here
	@iUserId int
AS
BEGIN
	-- returns the metadata
	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	SELECT * FROM ((SELECT * FROM (SELECT DISTINCT A1.Id, A1.Title, A1.ParentId, ISNULL((SELECT Activity.Title FROM Activity
	WHERE Activity.Id = A1.ParentId ),'') as Parent, Priority
	FROM AIAUserActivity INNER JOIN RoleToActivity 
	ON AIAUserActivity.RoleId = RoleTOActivity.RoleId
	INNER JOIN Activity A1
	ON RoleToActivity.ActivityId = A1.Id
	WHERE AIAUserActivity.UserId = @iUserId AND A1.IsActive = 1 ) RoleModel
	WHERE ParentId!=0
	)
	UNION
	(SELECT A1.Id, A1.Title, A1.ParentId, (SELECT Activity.Title FROM Activity
	WHERE Activity.Id = A1.ParentId) as Parent, Priority
	FROM AIAUserActivity INNER JOIN RoleToActivity 
	ON AIAUserActivity.ActivityId = RoleTOActivity.ActivityId
	INNER JOIN Activity A1
	ON RoleToActivity.ActivityId = A1.Id
	WHERE AIAUserActivity.UserId = @iUserId AND A1.IsActive = 1)) Roles 
	ORDER BY (CASE ParentId WHEN 0 THEN 9999 ELSE ParentId END), Priority 
END



	
GO