spGetSpecialPages

CREATE procedure [dbo].[spGetSpecialPages] (
    @ClientSitesID int,
    @IncludeLockedArtifacts bit = 0
)
as

declare @Pages TABLE(ID int, PageName nvarchar(256), PageType nvarchar(256), Seq int)

insert into @Pages (id, pagename, PageType, seq)
select id, pagename, 'Privacy Page', 10 
from dbo.SitePages
where id=(select CAST(VALUE as int) from dbo.CMSSITESETTING where CLIENTSITESID=@ClientSitesID AND ENUMID=5) -- corresponds to ESiteSetting.PrivacyPageID

insert into @Pages (id, pagename, PageType, seq)
select id, pagename, 'View on the Web Email Page', 25 
from dbo.SitePages
where id=(select CAST(VALUE as int) from dbo.CMSSITESETTING where CLIENTSITESID=@ClientSitesID AND ENUMID=52) and Deleted<>1 -- corresponds to ESiteSetting.EmailViewOnWebPageID

insert into @Pages (id, pagename,PageType, seq)
select id, pagename, 'Login Page', 5 
from dbo.SitePages
where id=(select CAST(VALUE as int) from dbo.CMSSITESETTING where CLIENTSITESID=@ClientSitesID AND ENUMID=3) -- corresponds to ESiteSetting.LoginPageID

insert into @Pages (id, pagename,PageType, seq)
select id, pagename, 'Home Page', 1 
from dbo.SitePages
where id=(select CAST(VALUE as int) from dbo.CMSSITESETTING where CLIENTSITESID=@ClientSitesID AND ENUMID=2) -- corresponds to ESiteSetting.DefaultPageID

insert into @Pages (id, pagename, PageType, seq)
select distinct VPC.pageid, VPC.pagename, 'Email Preferences Page', 20 from vwPageContent VPC
inner join dbo.SitePages SP on SP.ID = VPC.PageID
LEFT OUTER JOIN
(select PAGEMODELITEM.OBJECTGUID
from dbo.PAGEMODELITEM
inner join dbo.PAGEMODEL on PAGEMODEL.ID = PAGEMODELITEM.PAGEMODELID
where PAGEMODEL.LOCKARTIFACTS = 1
) PMI on PMI.OBJECTGUID = VPC.PageGuid
WHERE SP.ClientSitesID = @ClientSitesID
and VPC.IsTemplatePage = 0
and VPC.ContentTypesID = 50
and (PMI.OBJECTGUID is null OR @IncludeLockedArtifacts = 1)

insert into @Pages (id, pagename, PageType, seq)
select distinct sp.ID, sp.PageName, 'Email Preferences Page', 20
from dbo.SitePages sp
inner join dbo.PageContent pc on sp.ClientSitesID = @ClientSitesID and pc.SitePagesID = sp.ID
inner join dbo.SiteContent sc on pc.SiteContentID = sc.ID and sc.ContentTypesID = 121
inner join dbo.APEXFORMCONTENT afc on afc.ID = sc.Guid
inner join dbo.APEXFORM af on af.ID = afc.FORMID and af.FORMTYPEID = '89D3C4CC-4D01-4E8B-B82E-04AD966EEE09'

insert into @Pages (ID, PageName, PageType, Seq)
select id, pagename, 'Registration Page', 1 
from dbo.SitePages
where id=(select CAST(VALUE as int) from dbo.CMSSITESETTING where CLIENTSITESID=@ClientSitesID AND ENUMID=32) -- corresponds to ESiteSetting.FAFRoleSelectionPageID

insert into @Pages (ID, PageName, PageType, Seq)
select id, pagename, 'Donation Page', 1 
from dbo.SitePages
where id=(select CAST(VALUE as int) from dbo.CMSSITESETTING where CLIENTSITESID=@ClientSitesID AND ENUMID=46) -- corresponds to ESiteSetting.FAFDonationPageID

--Web forms user registration page
insert into @Pages (ID, PageName, PageType, Seq)
select SitePages.ID, PageName, 'User Registration Page', 3 
from dbo.SitePages
inner join dbo.MICROSITESETTING    on SitePages.ID =  cast(VALUE as int) and MICROSITESETTING.MICROSITESETTING = 17
where 
    --Basic cms is installed
    dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('e5e0494b-ba0f-4e23-b8fb-a59112dbf3c8') = 1 and
    --Locked artifact check
    (
        @IncludeLockedArtifacts = 1 or 
        not exists (
            select 1
            from dbo.PAGEMODEL
            inner join dbo.PAGEMODELITEM
                on PAGEMODEL.ID = PAGEMODELITEM.PAGEMODELID
            where
                PAGEMODEL.LOCKARTIFACTS = 1 and
                PAGEMODELITEM.PRIMARYKEY = SitePages.ID
        )
    )

select * from @Pages
order by seq