spGetSiteAssestsCount

CREATE PROCEDURE [dbo].[spGetSiteAssestsCount]
(
@ClientSitesID int,
@CopyablePartsOnly bit,
@TemplatePartsOnly bit
)    
AS

Declare @LivePages int
Declare @LiveTemplates int
Declare @LiveEmailTemplates int
Declare @LiveEcardTemplates int
Declare @LiveParts int
Declare @LiveStyleSheets int
Declare @LiveImages int
Declare @LiveLayouts int
Declare @LiveVanityURLs int

Select @LivePages = COUNT(*) from SitePages SP
left outer join (select PAGEMODELITEM.OBJECTGUID
                 from [dbo].PAGEMODEL
                 inner join [dbo].PAGEMODELITEM on PAGEMODEL.ID = PAGEMODELITEM.PAGEMODELID
                 where PAGEMODEL.LOCKARTIFACTS=1)PM on PM.OBJECTGUID = SP.Guid
Where SP.IsTemplatePage = 0 and SP.Deleted = 0 and SP.ClientSitesID = @ClientSitesID and PM.OBJECTGUID is null

Select @LiveLayouts = COUNT(*) from SiteLayouts SL
left outer join (select PAGEMODELITEM.OBJECTGUID
                 from [dbo].PAGEMODEL
                 inner join [dbo].PAGEMODELITEM on PAGEMODEL.ID = PAGEMODELITEM.PAGEMODELID
                 where PAGEMODEL.LOCKARTIFACTS=1)PM on PM.OBJECTGUID = SL.Guid
Where SL.Deleted = 0 and SL.ClientSitesID = @ClientSitesID and PM.OBJECTGUID is null

Select @LiveTemplates = COUNT(*) from SitePages SP
left outer join (select PAGEMODELITEM.OBJECTGUID
                 from [dbo].PAGEMODEL
                 inner join [dbo].PAGEMODELITEM on PAGEMODEL.ID = PAGEMODELITEM.PAGEMODELID
                 where PAGEMODEL.LOCKARTIFACTS=1)PM on PM.OBJECTGUID = SP.Guid
Where SP.IsTemplatePage = 1 and SP.Deleted = 0 and SP.ClientSitesID = @ClientSitesID and PM.OBJECTGUID is null

select @LiveEmailTemplates = COUNT(1)
                from dbo.[EMAILTEMPLATE] ET
                left outer join vwEmailTemplateDataSources DS on DS.DataSourceID = ET.DataSourceID
                left outer join (select PAGEMODELITEM.OBJECTGUID
                                from [dbo].PAGEMODEL
                                inner join [dbo].PAGEMODELITEM on PAGEMODEL.ID = PAGEMODELITEM.PAGEMODELID
                                where PAGEMODEL.LOCKARTIFACTS=1)PM on PM.OBJECTGUID = ET.Guid
                where
                ET.Type = 0
                and ET.IsInternalUse = 0
                and ET.Deleted = 0
                and ET.ClientsID = 1
                and DS.DataSourceID is not null
                and PM.OBJECTGUID is null 
                and ET.ClientSitesID = @ClientSitesID

select @LiveEcardTemplates = COUNT(1) from dbo.ECardTemplate ET
where ET.Deleted = 0 AND ET.ISSAMPLE = 0 and ET.ClientSitesID = @ClientSitesID

---------- Build Part Command ----------
declare @Select as nvarchar(4000)
declare @Where as nvarchar(4000)
declare @Cmd as nvarchar(4000)

set @Select = 'select @i = COUNT(DISTINCT(SC.ID)) from dbo.vwSiteContent SC'
set @Where = ' where SC.ClientSitesID = ' + cast(@ClientSitesID as nvarchar)

if @TemplatePartsOnly = 1
begin
    set @Select = @Select + ' inner join dbo.PageContent PC on SC.ID = PC.SiteContentID inner join dbo.SitePages SP on SP.ID = PC.SitePagesID'
    set @Where = @Where + ' and SP.IsTemplatePage = 1 and SP.Deleted = 0'
end        

if @CopyablePartsOnly = 1
    set @Select = @Select + ' inner join dbo.UFN_GETCLONESITECONTENTTYPES() C on SC.ContentTypesID = C.ID'


set @Select = @Select + ' left outer join (select PAGEMODELITEM.OBJECTGUID'
set @Select = @Select + ' from [dbo].PAGEMODEL'
set @Select = @Select + ' inner join [dbo].PAGEMODELITEM on PAGEMODEL.ID = PAGEMODELITEM.PAGEMODELID'
set @Select = @Select + ' where PAGEMODEL.LOCKARTIFACTS=1)PM on PM.OBJECTGUID = SC.Guid'
set @Where = @Where + ' and PM.OBJECTGUID is null'

set @Cmd = @Select + @Where

exec sp_executesql @query = @Cmd, @params = N'@i int output', @i = @LiveParts output
---------- End Part Command ----------

Select @LiveStyleSheets = COUNT(*) from SiteStyleSheets SSS
left outer join (select PAGEMODELITEM.OBJECTGUID
                 from [dbo].PAGEMODEL
                 inner join [dbo].PAGEMODELITEM on PAGEMODEL.ID = PAGEMODELITEM.PAGEMODELID
                 where PAGEMODEL.LOCKARTIFACTS=1)PM on PM.OBJECTGUID = SSS.Guid
Where SSS.Deleted = 0 and SSS.ClientSitesID = @ClientSitesID and PM.OBJECTGUID is null

Select @LiveImages = COUNT(*) from SiteImages SI
left outer join (select PAGEMODELITEM.OBJECTGUID
                 from [dbo].PAGEMODEL
                 inner join [dbo].PAGEMODELITEM on PAGEMODEL.ID = PAGEMODELITEM.PAGEMODELID
                 where PAGEMODEL.LOCKARTIFACTS=1)PM on PM.OBJECTGUID = SI.Guid
Where SI.Deleted = 0 and Type = 0 and SI.ClientSitesID = @ClientSitesID and PM.OBJECTGUID is null

Select @LiveVanityURLs = COUNT(*) from VanityURL VU
left outer join dbo.SitePages SP on SP.ID = VU.PageID
left outer join (select PAGEMODELITEM.OBJECTGUID
                 from [dbo].PAGEMODEL
                 inner join [dbo].PAGEMODELITEM on PAGEMODEL.ID = PAGEMODELITEM.PAGEMODELID
                 where PAGEMODEL.LOCKARTIFACTS=1)PM on PM.OBJECTGUID = SP.Guid
Where PM.OBJECTGUID is null and VU.ClientSitesID = @ClientSitesID

select @LivePages as 'LivePages', @LiveTemplates as 'LiveTemplates', @LiveParts as 'LiveParts', @LiveStyleSheets as 'LiveStyleSheets', @LiveImages as 'LiveImages', @LiveLayouts as 'LiveLayouts', @LiveVanityURLs as 'LiveVanityURLs', @LiveEmailTemplates as 'LiveEmailTemplates', @LiveEcardTemplates as 'LiveEcardTemplates'