USP_DATALIST_FAFMENUITEMS

CREATE procedure dbo.USP_DATALIST_FAFMENUITEMS
(
  @UserID int, 
  @ClientsID int, 
  @ContentID int, 
  @IsAdmin bit,
  @IncludeInactive bit = 0
)
as
    set nocount on;

    DECLARE @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,
          PageTitle nvarchar(256),
          ParticipantPageType varchar(10),
          CssClass nvarchar(100)
         )

            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]),
               PageTitle nvarchar(256),
               CssClass nvarchar(100)
            ) 
                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,
                              PageTitle,
                              MI.cssclass 
                         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 or @IncludeInactive = 1)


             -- Generate menu items for current user

             if @UserID Is Not Null -- for anonymous user view

                begin 
                if @UserID > 0 

                  begin 

                     if not exists (select 1 from ClientUsers where ID = @UserID And (IsSupervisor = 1 or InternalUser = 1)) -- if a user is supervisor or has supervisor right

                       begin 
                       -- if a user has edit right to the current menu part       

                       if not exists (select 1 from dbo.UFN_CLIENTUSERS_SECURITYEXCEPTIONOBJECTS(@UserID) EO
                                                           inner join dbo.CMSOBJETSECURITYEXCEPTION OSE on OSE.EXCEPTIONOBJECTGUID = EO.GUID
                                                           inner join (select * from dbo.V_CMSSECURABLEOBJECTS where OBJECTTYPEID IN (1)) O on O.OBJECTGUID = OSE.SECUREDOBJECTGUID             
                                                           inner join dbo.CMSOBJECTTASK OT on OT.ID = OSE.OBJECTTASKID 
                                                          where OT.ENUMID > 0
                                                         and RECORDID = @ContentID 
                                       )

                           begin

                           -- BEGIN fundraising pages filter rules

                           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' and ur.ClientUsersID = @UserID)
                           delete from @temp where ParticipantPageType = 'Household'

                           -- A user with FAF Participant role will be able to see the individual menu item; even household member/leader can since Team 24's modification to household.

                           if not exists (select 1 from UserRoles ur, ClientRoles cr where ur.ClientRolesID = cr.ID and cr.Guid = 'A4EF01E5-057B-44CE-AB02-47B1993D44AA' and ur.ClientUsersID = @UserID)
                           delete from @temp where ParticipantPageType = 'Individual'
                           -- END fundraising pages filter rules


                           declare @tmp TABLE(ID int, PID int)
                            insert into @tmp (ID,PID)
                              select 
                               ID,
                               ParentID 
                              from @temp
                                    where ContentID = @ContentID
                                and 
                                (( (IsActive=1 or @IncludeInactive = 1)
                                 and ID not in (select distinct ParentID from menuitems where ContentID=@ContentID and ParentID is not null And (SitePageID <> 0 Or(SitePageID = 0 And URL <> '')) And (IsActive=1 or @IncludeInactive = 1))
                                 and ((SitePageID = 0 And URL <> '')
                                 or (SitePageID <> 0
                                 and SitePageID in ( select RECORDID from dbo.UFN_CLIENTUSERS_SECURITYEXCEPTIONOBJECTS(@UserID) EO
                                                             inner join dbo.CMSOBJETSECURITYEXCEPTION OSE on OSE.EXCEPTIONOBJECTGUID = EO.GUID
                                                             inner join (select * from dbo.V_CMSSECURABLEOBJECTS where OBJECTTYPEID IN (2,3)) O on O.OBJECTGUID = OSE.SECUREDOBJECTGUID             
                                                             inner join dbo.CMSOBJECTTASK OT on OT.ID = OSE.OBJECTTASKID and TASKNAME='View'
                                                           where OT.ENUMID = 0 

                                                         union
                                                         -- All pages which have everyone view right 

                                                         select RECORDID from CMSEVERYONESECURITYPRIVS  CE
                                                           inner join (select * from dbo.V_CMSSECURABLEOBJECTS where OBJECTTYPEID IN (2,3)) O ON O.OBJECTGUID = CE.SECUREDOBJECTGUID
                                                           inner join dbo.CMSOBJECTTASK OT on OT.ID = CE.OBJECTTASKID 
                                                         where OT.TASKNAME = 'View' and CE.VALUE=1                                               

                                                     ) -- if a user has view right to the current page


                                        ))            ) 
                                 or (Caption like '%Login' and (IsActive=1 or @IncludeInactive = 1)) 
                                 or (Caption like '%Logout' and (IsActive=1 or @IncludeInactive = 1)))


                               declare @count int
                                set @count=1    
                               while(@count<=10)
                               begin
                                  insert into @tmp(ID,PID )
                                   select a.ID,a.ParentID 
                                 From MenuItems a
                                    inner join @tmp b on a.id=b.PID
                                   where a.ContentID=@ContentID
                                  set @count=@count+1
                               end


                           delete from @temp where ID In 
                          ( 

                                    select distinct ID 
                                from MenuItems
                                  where ID not in (select distinct ID from @tmp)
                                  and ContentID = @ContentID
                           )

                        end

                      end  

                    end 

                   end 


                      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],[pagetitle],[ParticipantPageType], [CssClass]) 
                    select [Id], [ParentId], [ContentID], [Caption], [Tooltip], [SitePageID],[PageTabID], [URL], [Target], 
                    [RightImgPad], [TreeIndex], [LinkType], [PageGuid], [LoginContentCount],[IsActive] ,[PageTitle], [ParticipantPageType], [CssClass] from @temp
                    order by TreeIndex 

   SELECT * FROM @MenuItems