spCloneSite_Content

CREATE procedure [dbo].[spCloneSite_Content]
(
@SourceClientSitesID int,
@TargetClientSitesID int,
@CurrentUserID int,
@CopyBatchGuid uniqueidentifier,
@TemplatesOnly bit
)

as

begin try

    begin transaction

    declare @ContentToCopy table([ID] int, [Guid] uniqueidentifier)

    if @TemplatesOnly = 0
    begin
        insert into @ContentToCopy
        select SC.ID, SC.Guid FROM SiteContent SC
        inner join dbo.UFN_GETCLONESITECONTENTTYPES() C on SC.ContentTypesID = C.ID
        WHERE ClientSitesID = @SourceClientSitesID AND Deleted = 0 
        AND SC.ShareInSiteCopy = 0
    end

    else
    begin
        insert into @ContentToCopy
        select distinct SiteContent.ID, SiteContent.Guid
        from SitePages
        inner join PageContent on SitePages.ID = PageContent.SitePagesID
        inner join SiteContent on SiteContent.ID = PageContent.SiteContentID
        inner join dbo.UFN_GETCLONESITECONTENTTYPES() C on SiteContent.ContentTypesID = C.ID
        where SitePages.IsTemplatePage = 1 AND SitePages.Deleted = 0 AND SiteContent.ClientSitesID = @SourceClientSitesID AND SiteContent.Deleted = 0 
        AND SiteContent.ShareInSiteCopy = 0
    end

    delete @ContentToCopy
    from @ContentToCopy CTC
    inner join PAGEMODELITEM PMI on PMI.OBJECTGUID = CTC.Guid
    inner join dbo.PAGEMODEL PM on PM.ID = PMI.PAGEMODELID
    where PM.LOCKARTIFACTS = 1

    INSERT INTO SiteContent
    (Title, XMLData, ExpireDate, PublishDate, AuthorizedEditRoles, CacheTime, ShowMobile, ClientSitesID, Guid, OwnerID, Deleted, Approved, ContentTypesID, Description, IsTimePublished, CreateDate, UpdateDate, LanguageTarget, WorkflowID, TestMode, CopySourceID, CopyBatchGUID, Discoverable, DiscoverableTitle, DiscoverableSummary, ChangedByCMSUserID, BBSPEnabled)
    SELECT
    Title,
    dbo.UFN_UPDATECOPYCONTENTHTML(XMLData,@CopyBatchGUID),   --this will replace the images and pids etc that are in html for helplets and contenthtml

    ExpireDate,
    PublishDate,
    AuthorizedEditRoles,
    CacheTime,
    ShowMobile,
    @TargetClientSitesID,
    newid(),
    OwnerID,
    Deleted,
    Approved,
    ContentTypesID,
    Description,
    IsTimePublished,
    CreateDate,
    UpdateDate,
    LanguageTarget,
    WorkflowID,
    TestMode,
    SC.ID,
    @CopyBatchGUID,
    SC.Discoverable,
    SC.DiscoverableTitle,
    SC.DiscoverableSummary,
    @CurrentUserID,
  BBSPEnabled
    FROM SiteContent SC
    inner join @ContentToCopy CTC on CTC.ID = SC.ID

    -- copy over the latest site content version info from the original parts

    insert into dbo.SiteContentVersion (SiteContentID, [XMLData], [Status], [StatusUpdatedBy], [StatusUpdatedOn], [Comments])
    select SCnew.ID, dbo.UFN_UPDATECOPYCONTENTHTML(SCV.XMLData,@CopyBatchGUID), SCV.Status, SCV.StatusUpdatedBy, SCV.StatusUpdatedOn, SCV.Comments
        from
    (
        select MAX(SCV.ID) as ID
        from dbo.SiteContentVersion SCV
        inner join @ContentToCopy CTC on CTC.ID = SCV.SiteContentID
        group by SCV.SiteContentID
    ) LATEST
    inner join dbo.SiteContentVersion SCV on SCV.ID = LATEST.ID
    inner join SiteContent SCnew on SCnew.CopySourceID = SCV.SITECONTENTID and SCnew.CopyBatchGUID = @CopyBatchGuid    

    -- copy over the tags for the parts we have just copied

    insert into dbo.SITECONTENTTAG (SITECONTENTID, TAGCODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    select SCnew.ID, SCT.TAGCODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
    from dbo.SITECONTENTTAG SCT
    inner join @ContentToCopy CTC on CTC.ID = SCT.SITECONTENTID
    inner join SiteContent SCnew on SCnew.CopySourceID = SCT.SITECONTENTID and SCnew.CopyBatchGUID = @CopyBatchGuid    

    -- copy over the folders for the parts we have just copied

    insert into dbo.SITECONTENTFOLDERS (CONTENTID, SITEFOLDERSID)
    select SC.ID, SF.FolderID
    from dbo.SITECONTENTFOLDERS SCT
    inner join dbo.SiteFolders SF on SF.CopySourceID = SCT.SITEFOLDERSID and SF.CopyBatchGUID = @CopyBatchGuid
    inner join dbo.SiteContent SC on SC.CopySourceID = SCT.CONTENTID and SC.CopyBatchGUID = @CopyBatchGuid    

    INSERT INTO Search (SearchTypeId,SiteContentId,ContentUpdateDate,ExtendedKey,Title,SearchText,TagText,SearchDate1,SearchDate2,SearchInt1)
    select
    S.SearchTypeId,
    SC.ID,
    S.ContentUpdateDate,
    S.ExtendedKey,
    S.Title,
    dbo.UFN_UPDATECOPYCONTENTHTML(S.SearchText,@CopyBatchGUID),
    S.TagText,
    S.SearchDate1,
    S.SearchDate2,
    S.SearchInt1
    from dbo.Search S
    inner join dbo.SiteContent SC on SC.CopySourceID = S.SiteContentId
    where SC.CopyBatchGUID = @CopyBatchGuid

    commit transaction
end try
begin catch
    rollback transaction
    UPDATE SiteCopy SET ErrorLog = ErrorLog +  'Error Copying Parts in Clone Site Content:' + ERROR_MESSAGE() + CHAR(13) WHERE ID = @CopyBatchGUID
end catch