fnGetFAFMenuItems

CREATE FUNCTION [dbo].[fnGetFAFMenuItems]( 
         @UserID int, 
         @ClientsID int, 
         @ContentID int, 
         @IsAdmin bit 
        )
        RETURNS @MenuItems TABLE ( 
          [Id] int,  
          [ParentId] int,  
          ContentID int, 
          Caption nvarchar(256), 
          Tooltip nvarchar(256), 
          SitePageID int, 
          PageTabID int, 
          URL nvarchar(2048), 
          Target nvarchar(256), 
          RightImgPad int, 
          TreeIndex nvarchar(256), 
          LinkType tinyint,
          PageGuid uniqueidentifier, 
          LoginContentCount int,
          IsActive bit
         )AS
         begin
            declare @temp TABLE ( 
               [Id] int,  
               [ParentId] int,  
               ContentID int, 
               Caption nvarchar(256), 
               Tooltip nvarchar(256), 
               SitePageID int, 
               PageTabID int, 
               URL nvarchar(2048), 
               Target nvarchar(256), 
               RightImgPad int, 
               TreeIndex nvarchar(256),
               LinkType tinyint,
               IsActive bit,
               PageGuid uniqueidentifier, 
               LoginContentCount int,
               ParticipantPageType varchar(10),
               unique([SitePageID],[ID],[ContentID])
            ) 
                insert into @temp 
                       select MI.[Id], 
                              MI.ParentID, 
                              MI.ContentID, 
                              MI.Caption, 
                              MI.Tooltip, 
                              MI.SitePageID, 
                              MI.pageTabId, 
                              MI.URL, 
                              MI.Target, 
                              MI.RightImgPad, 
                              MI.Treeindex,
                              MI.LinkType,
                              MI.IsActive,
                              NULL,0
                              , case when pc.ContentTypesID Is Null then 'NA' when MI.PageTabID = 0 then 'Individual' when MI.PageTabID = 1 then 'Team' when MI.PageTabID = 2 then 'Company' when MI.PageTabID = 3 then 'Household' end
                         from MenuItems as MI
                        left join (select pc.SitePagesID, pc.SiteContentID, sc.ContentTypesID 
         from PageContent pc inner join SiteContent sc on pc.SiteContentID = sc.ID and sc.ContentTypesID = 130) pc on pc.SitePagesID = MI.SitePageID 
                          where MI.Contentid=@contentID 
                          And MI.IsActive=1

                       if not exists (select 1 from UserRoles ur, ClientRoles cr where ur.ClientRolesID = cr.ID and cr.Guid = 'FD5734D1-9E84-4B82-8B4A-C6322EB0F9BF' and ur.ClientUsersID = @UserID)
                       delete from @temp where ParticipantPageType = 'Team'   

                       if not exists (select 1 from UserRoles ur, ClientRoles cr where ur.ClientRolesID = cr.ID and cr.Guid = '61CBFC50-B232-4267-A20F-1F2E88879293' and ur.ClientUsersID = @UserID)
                       delete from @temp where ParticipantPageType = 'Company'

                       if not exists (select 1 from UserRoles ur, ClientRoles cr where ur.ClientRolesID = cr.ID and (cr.Guid = 'BC6B6D42-C7E6-4983-9A99-09ABAE79F452' or cr.guid='3182787B-18A8-4B5E-BCBE-B56D97A6F88B') and ur.ClientUsersID = @UserID)
                       delete from @temp where ParticipantPageType = 'Household'

                       if not exists (select 1 from UserRoles ur, ClientRoles cr where ur.ClientRolesID = cr.ID and cr.Guid = 'BDA181EC-4575-4BBD-9C6F-57CBFF264B7D' and ur.ClientUsersID = @UserID)
                       delete from @temp where ParticipantPageType = 'Individual'

                      update @temp set [PageGuid] = sp.GUID
                      from  @temp t INNER JOIN dbo.SitePages sp ON sp.ID = t.SitePageID

                      update @temp set [SitePageID] = 0
                      where PageGuid is null AND LinkType = 0

                      update @temp set LoginContentCount=1
                                from @temp t 
                      where exists(select * from [vwPageContent] vw
                                where t.SitePageID=vw.PageID and vw.contenttypesid=33 
                      )


                    insert into @MenuItems ( 
                    [Id], [ParentId], [ContentID], [Caption], [Tooltip], [SitePageID],[PageTabID], [URL], [Target], 
                    [RightImgPad], [TreeIndex], [LinkType], [PageGuid], [LoginContentCount],[IsActive]) 
                    select [Id], [ParentId], [ContentID], [Caption], [Tooltip], [SitePageID],[PageTabID], [URL], [Target], 
                    [RightImgPad], [TreeIndex], [LinkType], [PageGuid], [LoginContentCount],[IsActive] from @temp
                    order by TreeIndex 

                    return
                    end