USP_CLONESITE_MEMBERSHIP

CREATE procedure dbo.USP_CLONESITE_MEMBERSHIP
(
@SourceClientSitesID int,
@TargetClientSitesID int,
@CurrentUserID int,
@CopyBatchGuid uniqueidentifier
)
as


    --Insert new email acknowledgement templates

    declare @IDs xml
    set @IDs = 
    (
        select CM.EmailTemplateID as ID
        from dbo.ClientMemberships CM
        inner join dbo.SiteContent SC on SC.CopySourceID = CM.SiteContentID
        where SC.CopyBatchGUID = @CopyBatchGuid
        FOR XML RAW, ELEMENTS
    )        
    exec spCloneSite_EmailTemplate @TargetClientSitesID,@CurrentUserID,@CopyBatchGUID,@IDs

    insert into dbo.ClientMemberships(SiteContentID,OwnerID,Deleted,Guid,XMLData,EmailTemplateID,MerchantAccount,CartMessage,ShoppingCartPageID,CartSendMembershipAcknowledgementAlways,AcknowledgementBlock)
    select
    SC.ID,
    SC.OwnerID,
    CM.Deleted,
    NEWID(),
    CM.XMLData,
    ET.ID,
    CM.MerchantAccount,
    dbo.UFN_UPDATECOPYCONTENTHTML (CM.CartMessage,@CopyBatchGuid),
    ISNULL(SP.ID, CM.ShoppingCartPageID),
    CM.CartSendMembershipAcknowledgementAlways,
    dbo.UFN_UPDATECOPYCONTENTHTML (CM.AcknowledgementBlock,@CopyBatchGuid)
    from dbo.ClientMemberships CM
    inner join dbo.SiteContent SC on SC.CopySourceID = CM.SiteContentID
    inner join dbo.EmailTemplate ET on ET.CopySourceID = CM.EmailTemplateID
    left outer join dbo.SitePages SP on SP.CopySourceID = CM.ShoppingCartPageID and SP.CopyBatchGUID = @CopyBatchGuid
    where SC.CopyBatchGUID = @CopyBatchGuid and ET.CopyBatchGUID = @CopyBatchGuid

    --Update XML Data

    declare @Data xml
    set @Data = 
    (
        select P.SiteContentID, CM.ID as Value
        from dbo.UFN_GETXMLPROPERTYFORSITECOPY(@CopyBatchGuid,117, 'ClientMembershipID') P
        inner join dbo.ClientMemberships CM on CM.SiteContentID = P.SiteContentID
        FOR XML RAW, ELEMENTS
    )    
    exec USP_UPDATEXMLPROPERTYFORSITECOPY 'ClientMembershipID', @Data    

    --Update XML Data

    set @Data = 
    (
        select P.SiteContentID, CM.ID as Value
        from dbo.UFN_GETXMLPROPERTYFORSITECOPY(@CopyBatchGuid,59, 'ClientMembershipID') P
        inner join dbo.ClientMemberships CM on CM.SiteContentID = P.SiteContentID
        FOR XML RAW, ELEMENTS
    )    
    exec USP_UPDATEXMLPROPERTYFORSITECOPY 'ClientMembershipID', @Data        

    --Update XML Data

    set @Data = 
    (        
        select P.SiteContentID, SI.ID as Value
        from dbo.UFN_GETXMLPROPERTYFORSITECOPY(@CopyBatchGuid,117, 'FinderImgUrl') P
        inner join dbo.SiteImages SI on SI.CopySourceID = P.Value
        where SI.CopyBatchGuid = @CopyBatchGuid
        FOR XML RAW, ELEMENTS
    )    
    exec USP_UPDATEXMLPROPERTYFORSITECOPY 'FinderImgUrl', @Data