USP_DATAFORMTEMPLATE_EDIT_COMBINATION

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_COMBINATION (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @NAME nvarchar(100),
    @DESCRIPTION nvarchar(255),
    @EVENTSSAMEDAY bit,
    @PRICETYPES xml
)
as
    set nocount on;

    if @CHANGEAGENTID is null begin
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
    end

    declare @CURRENTDATE datetime = getdate();

    begin try
        if @EVENTSSAMEDAY = 0 and dbo.UFN_COMBINATION_HASONLINEAVAILABILITY(@ID) = 1 begin
            raiserror('BBERR_COMBINATIONEDIT_ONLINECOMBOMUSTBESAMEDAY', 13, 1);
        end

        declare @PRICETYPESTABLE table (
            ID uniqueidentifier,
            PRICETYPECODEID uniqueidentifier,
            SEQUENCE int
        );

        insert into @PRICETYPESTABLE
        select
            ID,
            PRICETYPECODEID,
            SEQUENCE
        from dbo.UFN_COMBINATION_GETPRICETYPES_FROMITEMLISTXML(@PRICETYPES);

        -- Check for daily sales buttons
        if exists (
            select 1
            from dbo.DAILYSALEITEMCOMBINATION
            where COMBINATIONID = @ID
            and PRICETYPECODEID not in (select PRICETYPECODEID from @PRICETYPESTABLE)
        ) begin
            raiserror('BBERR_DAILYSALES_COMBOPRICETYPEINUSE', 13, 1)
        end

        update dbo.COMBINATION set
            NAME = @NAME,
            DESCRIPTION = @DESCRIPTION,
            EVENTSSAMEDAY = @EVENTSSAMEDAY,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where ID = @ID;

        -- Ensures that existing price types will be matched even if
        -- their ID column was changed by the UI
        declare @PRICETYPESUPDATED xml = (
            select
                isnull(OLDPRICETYPES.ID, '00000000-0000-0000-0000-000000000000') as ID,
                NEWPRICETYPES.PRICETYPECODEID,
                NEWPRICETYPES.SEQUENCE
            from @PRICETYPESTABLE as NEWPRICETYPES
            left join (
                select ID, PRICETYPECODEID, SEQUENCE
                from dbo.COMBINATIONPRICETYPE where COMBINATIONID = @ID
            ) as OLDPRICETYPES on NEWPRICETYPES.PRICETYPECODEID = OLDPRICETYPES.PRICETYPECODEID
            for xml raw('ITEM'),type,elements,root('PRICETYPES'),BINARY BASE64
        );

        exec dbo.USP_COMBINATION_GETPRICETYPES_UPDATEFROMXML @ID, @PRICETYPESUPDATED, @CHANGEAGENTID, @CURRENTDATE;

        -- Add new price types into program groups
        if exists(select PROGRAMGROUP.ID from dbo.PROGRAMGROUP where PROGRAMGROUP.COMBINATIONID = @ID) begin
            insert into [PROGRAMGROUPPRICE] (
                [ID],
                [PROGRAMGROUPID],
                [COMBINATIONPRICETYPEID],
                [FACEPRICE],
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            select
                newid(),
                [PROGRAMGROUP].[ID],
                [COMBINATIONPRICETYPE].[ID],
                0,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            from dbo.COMBINATIONPRICETYPE
            inner join dbo.COMBINATION on COMBINATIONPRICETYPE.COMBINATIONID = COMBINATION.ID
            left join dbo.PROGRAMGROUPPRICE on COMBINATIONPRICETYPE.ID = PROGRAMGROUPPRICE.COMBINATIONPRICETYPEID
            full outer join dbo.PROGRAMGROUP on PROGRAMGROUP.COMBINATIONID = COMBINATION.ID
            where COMBINATION.ID = @ID and PROGRAMGROUPPRICE.ID is null;
        end
    end try

    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;