USP_CLONESITE_DONATIONFORM_ECARD

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


    --Insert new email acknowledgement templates

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

    --Insert new entries into eCardPart

    insert into dbo.ECardPart (SiteContentID, MaxHeight, MaxWidth, MaxRecipients, EmailTemplateID, BrowseCategories, ClientDonationsID, UseRichTextMessage, DisplayAllAsSingleFields)
    select SC.ID, ECP.MaxHeight, ECP.MaxWidth, ECP.MaxRecipients, ET.ID, ECP.BrowseCategories, ECP.ClientDonationsID, ECP.UseRichTextMessage, ECP.DisplayAllAsSingleFields
    from dbo.ECardPart ECP
    inner join dbo.SiteContent SC on SC.CopySourceID = ECP.SiteContentID
    inner join dbo.EmailTemplate ET on ET.CopySourceID = ECP.EmailTemplateID
    where SC.CopyBatchGUID = @CopyBatchGuid and ET.CopyBatchGUID = @CopyBatchGuid

    --Find matches between the original and copied eCardPart entries

    declare @EcardPartMapping table ([SourcePartID] int, [NewPartID] int)
    insert into @EcardPartMapping
    select SECP.ID, TECP.ID
    from dbo.ECardPart SECP
    inner join dbo.SiteContent SC on SC.CopySourceID = SECP.SiteContentID
    inner join dbo.ECardPart TECP on TECP.SiteContentID = SC.ID
    where SC.CopyBatchGUID = @CopyBatchGuid

    --Insert new entries into eCardPartTemplate

    insert into dbo.ECardPartTemplate (ECardPartID, ECardTemplateID, SiteFoldersID)
    select EPM.NewPartID, ECPT.ECardTemplateID, ECPT.SiteFoldersID
    from dbo.ECardPartTemplate ECPT
    inner join @EcardPartMapping EPM on EPM.SourcePartID = ECPT.ECardPartID

    -------------------------------------------------------------------------------------------


    -- Insert email acknowledgement templates

    set @IDs = 
    (
        select CD.EmailTemplateID as ID
        from dbo.ClientDonations CD
        inner join dbo.SiteContent SC on SC.CopySourceID = CD.ContentID
        where SC.CopyBatchGUID = @CopyBatchGuid
        FOR XML RAW, ELEMENTS
    )    
    exec spCloneSite_EmailTemplate @TargetClientSitesID, @CurrentUserID, @CopyBatchGUID,@IDs

    -- Insert new entries into ClientDonations

    insert into dbo.ClientDonations (ClientsID,OwnerID,Guid,ConstitCodeID,DonationOptions,TributeOptions,RedirectPageID,MerchantAccount,ContentID,Attributes,DonateBtn,RecurrenceOptions,RecurrenceOptionsTruePledge,NoMGPledges,ShoppingCartPageID,EReceiptPdfForCreditCard,EReceiptPdfForDirectDebit,MarkReceiptedForCreditCard,MarkReceiptedForDirectDebit,EReceiptContent,EReceiptPdfMargin,CartMessage,ECardSendDateAttributeID,ECardRecipientFirstNameAttributeID,ECardRecipientLastNameAttributeID,CartSendDonationAcknowledgementAlways,MinimumGiftAmount,MinimumPledgePaymentAmount,ShowPledgePaymentDueDate,EmailTemplateID,AcknowledgementBlock)
    select 
    CD.ClientsID,
    CD.OwnerID,
    NEWID(),
    CD.ConstitCodeID,
    CD.DonationOptions,
    CD.TributeOptions,
    CD.RedirectPageID,
    CD.MerchantAccount,
    SC.ID,
    CD.Attributes,
    CD.DonateBtn,
    CD.RecurrenceOptions,
    CD.RecurrenceOptionsTruePledge,
    CD.NoMGPledges,
    ISNULL(SP.ID,CD.ShoppingCartPageID) as ShoppingCartPageID,
    CD.EReceiptPdfForCreditCard,
    CD.EReceiptPdfForDirectDebit,
    CD.MarkReceiptedForCreditCard,
    CD.MarkReceiptedForDirectDebit,
    dbo.UFN_UPDATECOPYCONTENTHTML (CD.EReceiptContent, @CopyBatchGuid),
    CD.EReceiptPdfMargin,
    dbo.UFN_UPDATECOPYCONTENTHTML (CD.CartMessage, @CopyBatchGuid),
    CD.ECardSendDateAttributeID,
    CD.ECardRecipientFirstNameAttributeID,
    CD.ECardRecipientLastNameAttributeID,
    CD.CartSendDonationAcknowledgementAlways,
    CD.MinimumGiftAmount,
    CD.MinimumPledgePaymentAmount,
    CD.ShowPledgePaymentDueDate,
    ISNULL(ET.ID,-1),
    dbo.UFN_UPDATECOPYCONTENTHTML (CD.AcknowledgementBlock, @CopyBatchGuid)
    from dbo.ClientDonations CD
    inner join dbo.SiteContent SC on SC.CopySourceID = CD.ContentID
    left outer join dbo.EmailTemplate ET on ET.CopySourceID = CD.EmailTemplateID
    left outer join dbo.SitePages SP on SP.CopySourceID = CD.ShoppingCartPageID and (SP.CopyBatchGUID = @CopyBatchGuid OR SP.CopyBatchGUID IS NULL)
    where SC.CopyBatchGUID = @CopyBatchGuid and (ET.CopyBatchGUID = @CopyBatchGuid OR ET.CopyBatchGUID IS NULL)

    -- Find matches between original and copied Donation part entries

    declare @DonationMapping table (SrcPartID int, NewPartID int)
    insert into @DonationMapping
    select SCD.ID, TCD.ID
    from dbo.ClientDonations SCD
    inner join dbo.SiteContent SC on SC.CopySourceID = SCD.ContentID
    inner join dbo.ClientDonations TCD on TCD.ContentID = SC.ID
    where SC.CopyBatchGUID = @CopyBatchGuid

    -- Insert new entries into DonationDesignations

    insert into dbo.DonationDesignations(ClientDonationsID,BackOfficeID,Name,IsDefault,BackOfficeIDGUID)
    select 
    DM.NewPartID,
    DD.BackOfficeID,
    DD.Name,
    DD.IsDefault,
    DD.BackOfficeIDGUID
    from dbo.DonationDesignations DD
    inner join @DonationMapping DM on DM.SrcPartID = DD.ClientDonationsID

    -- Insert new entries into DonationGivingLevels

    insert into dbo.DonationGivingLevels(ClientDonationsID,Name,Amount)
    select 
    DM.NewPartID,
    DGL.Name,
    DGL.Amount
    from dbo.DonationGivingLevels DGL
    inner join @DonationMapping DM on DM.SrcPartID = DGL.ClientDonationsID

    -- Insert new entries into DonationSources

    insert into dbo.DonationSources(ClientDonationsID,BackOfficeID,Name,IsDefault)
    select 
    DM.NewPartID,
    DS.BackOfficeID,
    DS.Name,
    DS.IsDefault
    from dbo.DonationSources DS
    inner join @DonationMapping DM on DM.SrcPartID = DS.ClientDonationsID 

    ------------------------------------------------------------------------------------------



    --Update the Client Donation ID to point to the copied donation data

    update ECP
    set ECP.ClientDonationsID = CD.ID
    from dbo.ECardPart ECP
    inner join dbo.ClientDonations CD on CD.ContentID = ECP.SiteContentID
    inner join dbo.SiteContent SC on SC.ID = ECP.SiteContentID
    where SC.ContentTypesID = 114 AND SC.CopyBatchGUID = @CopyBatchGuid

    --Update the Ecard Part ID to point to the copied ecard data

    update CD
    set cd.ECardPartID = ECP.ID
    from dbo.ECardPart ECP
    inner join dbo.ClientDonations CD on CD.ContentID = ECP.SiteContentID
    inner join dbo.SiteContent SC on SC.ID = ECP.SiteContentID
    where SC.ContentTypesID = 45 AND SC.CopyBatchGUID = @CopyBatchGuid

    --------------------------------------------------------------------------------------------


    --Update XML Data

    declare @Data xml
    set @Data = 
    (
        select P.SiteContentID, CD.ID as Value
        from dbo.UFN_GETXMLPROPERTYFORSITECOPY(@CopyBatchGuid,45, 'ClientDonationsID') P
        inner join dbo.ClientDonations CD on CD.ContentID = P.SiteContentID
        FOR XML RAW, ELEMENTS
    )    
    exec USP_UPDATEXMLPROPERTYFORSITECOPY 'ClientDonationsID', @Data

    set @Data = 
    (
        select P.SiteContentID, CD.ID as Value
        from dbo.UFN_GETXMLPROPERTYFORSITECOPY(@CopyBatchGuid,114, 'ClientDonationsID') P
        inner join dbo.ClientDonations CD on CD.ContentID = P.SiteContentID
        FOR XML RAW, ELEMENTS
    )    
    exec USP_UPDATEXMLPROPERTYFORSITECOPY 'ClientDonationsID', @Data

    set @Data = 
    (
        select P.SiteContentID, ECP.ID as Value
        from dbo.UFN_GETXMLPROPERTYFORSITECOPY(@CopyBatchGuid,45, 'ECardPartID') P
        inner join dbo.EcardPart ECP on ECP.SiteContentID = P.SiteContentID
        FOR XML RAW, ELEMENTS
    )    
    exec USP_UPDATEXMLPROPERTYFORSITECOPY 'ECardPartID', @Data    

    set @Data = 
    (
        select P.SiteContentID, ECP.ID as Value
        from dbo.UFN_GETXMLPROPERTYFORSITECOPY(@CopyBatchGuid,114, 'ECardPartID') P
        inner join dbo.EcardPart ECP on ECP.SiteContentID = P.SiteContentID
        FOR XML RAW, ELEMENTS
    )    
    exec USP_UPDATEXMLPROPERTYFORSITECOPY 'ECardPartID', @Data