spClone_ClientEvent

CREATE procedure [dbo].[spClone_ClientEvent]
(
    @PKID int output,
    @ContentID int,
    @CurrentUsersID int,
    @OldContentID int = null
)
as
    declare @ClientEventsID integer;
    set @ClientEventsID = @PKID;

    declare @newGuid uniqueidentifier;
    set @newGuid = newid();

    insert into dbo.ClientEvents ( 
        ClientsID,
        OwnerID,
        Guid,
        FundID,
        ConstitCodeID,
        RedirectPageID,
        ContentID,
        MerchantAccount,
        ShoppingCartPageID,
        CartMessage,
        CartSendRegistrationAcknowledgementAlways,
        AcknowledgementBlock,
        NoMGPledges 
    ) select
        ClientsID,
        @CurrentUsersID,
        @newGuid,
        FundID,
        ConstitCodeID,
        RedirectPageID,
        @ContentID,
        MerchantAccount,
        ShoppingCartPageID,
        CartMessage,
        CartSendRegistrationAcknowledgementAlways,
        AcknowledgementBlock,
        NoMGPledges 
    from
        dbo.ClientEvents
    where       
        ID = @PKID;

    select @PKID = @@identity;

    declare @EventItemsID integer;
    declare @NewEventItemsID integer;
    declare items_cursor cursor for 
        select ID
        from        
            dbo.EventItems
        where       
            ClientEventsID = @ClientEventsID;

    open items_cursor;

    fetch next from items_cursor into @EventItemsID;

    while @@FETCH_STATUS = 0
    begin

        insert into dbo.EventItems ( 
            ClientEventsID,
            BackOfficeID,
            LimitCapacity,
            PageLink,
            DisplayName,
            DisplayDesc,
            ShowRelations,
            Attributes,
            Target,
            PageTabID,
            BaseCurrencyID,
            DisplayOrder
        ) select               
            @PKID,
            BackOfficeID,
            LimitCapacity,
            PageLink,
            DisplayName,
            DisplayDesc,
            ShowRelations,
            Attributes,
            Target,
            PageTabID,
            BaseCurrencyID,
            DisplayOrder
        from        
            dbo.EventItems
        where       
            ID = @EventItemsID;

        select  @NewEventItemsID = @@identity;

        insert into dbo.EventItemPrices ( 
            EventItemsID,
            BackOfficeID,
            Description,
            NumParticipants,
            Publish,
            AllowAnonRegistrants,
            DisplayOrder
        ) select               
            @NewEventItemsID,
            BackOfficeID,
            Description,
            NumParticipants,
            Publish,
            AllowAnonRegistrants,
            DisplayOrder
        from        
            dbo.EventItemPrices
        where       
            EventItemsID = @EventItemsID;

        fetch next from items_cursor into @EventItemsID;
    end

    close items_cursor;
    deallocate items_cursor;

    -- For Event Registration Form 2.0, copy CMSEVENTOPTIONS (price units and attributes data)
    if 154 = (select top 1 ContentTypesID from dbo.SiteContent where ID = @ContentID) and @OldContentID is not null
    begin

        --Get the old part's CMSEventOptionID from its XMLData
        declare @XMLData xml = null                    
        select @XMLData = XmlData from dbo.SiteContent sc where sc.ID = @OldContentID

        declare @Property nvarchar(400) = 'CMSEventOptionID'
        declare @oldCMSEventOptionID uniqueidentifier
        select @oldCMSEventOptionID  = convert(uniqueidentifier, @XMLData.value('(XMLHashTable[1]/ItemArray[1]/Item[@Name=sql:variable("@Property")][1]/Value[1]/text()[1])', 'nvarchar(max)') )

        --Copy the CMSEventOption data
        declare @CHANGEAGENTID uniqueidentifier 
        exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

        declare @newCMSEventOptionID uniqueidentifier = NEWID()
        insert into CMSEVENTOPTION (ID, OPTIONDATA, ADDEDBYID, CHANGEDBYID)
        select @newCMSEventOptionID, OPTIONDATA, @CHANGEAGENTID, @CHANGEAGENTID
        FROM dbo.CMSEVENTOPTION
        where ID = @oldCMSEventOptionID

        --Update the new OPTIONDATA blob to point to the new ContentID, and its own CMSEVENTOPTION.ID
        set @XMLData = null
        select @XMLData = OptionData
        from dbo.CMSEVENTOPTION
        where ID = @newCMSEventOptionID

        declare @NewValue nvarchar(50) = convert(nvarchar(50), @ContentID)
        set @XMLData.modify('replace value of (Event2Options[1]/Event2RegContentObjectID[1]/text()[1]) with sql:variable("@NewValue")')
        set @NewValue = CONVERT(nvarchar(50), @newCMSEventOptionID)
        set @XMLData.modify('replace value of (Event2Options[1]/ID[1]/text()[1]) with sql:variable("@NewValue")')

        update dbo.CMSEVENTOPTION
        set OPTIONDATA = @XMLData
        where ID = @newCMSEventOptionID

        --Update new part's XMLData to use the new CMSEventOption's ID
        set @XMLData = null
        select @XMLData = XmlData
        from dbo.SiteContent sc
        where sc.ID = @ContentID

        if @XMLData is not null 
        begin
            set @Property = 'CMSEventOptionID'
            set @NewValue = convert(nvarchar(50), @newCMSEventOptionID)
            set @XMLData.modify('replace value of (XMLHashTable[1]/ItemArray[1]/Item[@Name=sql:variable("@Property")][1]/Value[1]/text()[1]) with sql:variable("@NewValue")')

            update SC
            set SC.XMLData = '<?xml version="1.0" encoding="utf-16"?>' + CAST(@XMLData as nvarchar(max))
            from  dbo.SiteContent SC
            where SC.ID = @ContentID
        end
    end

    exec spAuditThis @CurrentUsersID, 2, @newGuid, 16;