USP_DATAFORMTEMPLATE_EDIT_RATESCALE_2

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_RATESCALE_2 (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @NAME nvarchar(100),
    @DESCRIPTION nvarchar(255),
    @ISDEFAULT bit,
    @ISSINGLEPRICE bit,
    @AMOUNT money,
    @PRICES xml,
    @APPLICATIONS xml,
    @GROUPSALESGROUPTYPECODEID uniqueidentifier,
    @USEPERTICKETAFTERMAX bit
)
as

    set nocount on;

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    begin try
        if exists (
            select    1
            from @APPLICATIONS.nodes('/APPLICATIONS/ITEM') T(c)
            where T.c.value('(TYPECODE)[1]','tinyint') = 0  -- PROGRAM

                and dbo.UFN_PROGRAM_ISPREREGISTERED(T.c.value('(PROGRAMID)[1]', 'uniqueidentifier')) = 1
            )
            raiserror('BBERR_INVALIDPROGRAM', 13, 1);                    

        --Check to ensure nothing bad happens!

        if @ISSINGLEPRICE = 1
            set @USEPERTICKETAFTERMAX = 0;

        if @ISDEFAULT = 1 and exists (select 1 from dbo.RATESCALE where ISDEFAULT = 1 and ID <> @ID)
        begin
            update dbo.RATESCALE set
                ISDEFAULT = 0,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where
                ID <> @ID
        end

        update dbo.RATESCALE set
            NAME = @NAME,
            DESCRIPTION = @DESCRIPTION,
            ISDEFAULT = @ISDEFAULT,
            GROUPSALESGROUPTYPECODEID = @GROUPSALESGROUPTYPECODEID,
            USEPERTICKETAFTERMAX = @USEPERTICKETAFTERMAX,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where ID = @ID


        declare @PRICETABLE table
        (
            ID uniqueidentifier,
            GROUPMINIMUM smallint,
            GROUPMAXIMUM smallint,
            AMOUNT money
        )

        if @ISSINGLEPRICE = 0
        begin
            insert into @PRICETABLE
            ( ID, GROUPMINIMUM, GROUPMAXIMUM, AMOUNT )
            select
                case T.c.value('(ID)[1]','uniqueidentifier')
                    when '00000000-0000-0000-0000-000000000000' 
                        then newid()
                    else 
                        case when T.c.value('(ID)[1]','uniqueidentifier') is null
                            then newid()
                        else
                            T.c.value('(ID)[1]','uniqueidentifier') end
                    end,
                null,
                T.c.value('(SIZE)[1]','smallint'),
                T.c.value('(AMOUNT)[1]','money')
            from
                @PRICES.nodes('/PRICES/ITEM') T(c)
            order by T.c.value('(SIZE)[1]','smallint')

            if exists (select 1 from @PRICETABLE where GROUPMAXIMUM <= 0)
            begin
                raiserror('BBERR_GROUPSIZE', 13, 1);
                return 1;
            end
            if exists (select 1 from @PRICETABLE where AMOUNT < 0)
            begin
                raiserror('BBERR_MINPRICE', 13, 1);
                return 1;
            end

            if @AMOUNT < 0
            begin
                raiserror('BBERR_MINOVERAMOUNT', 13, 1);
                return 1;
            end

            update @PRICETABLE set
                GROUPMINIMUM = isnull((
                                select max(GROUPMAXIMUM)
                                from @PRICETABLE PT2
                                where PT2.GROUPMAXIMUM < [@PRICETABLE].GROUPMAXIMUM),0)

            update @PRICETABLE set
                GROUPMINIMUM = GROUPMINIMUM + 1
            where GROUPMINIMUM <> 0

            delete from @PRICETABLE
            where GROUPMAXIMUM = 0
        end
        else
        begin
            if @AMOUNT < 0
            begin
                raiserror('BBERR_MINAMOUNT', 13, 1);
                return 1;
            end
        end


        declare @MAXID uniqueidentifier
        select @MAXID = ID from dbo.RATESCALEPRICE
        where RATESCALEID = @ID and GROUPMAXIMUM = -1

        insert into @PRICETABLE
        ( ID, GROUPMINIMUM,    GROUPMAXIMUM, AMOUNT )
        select
            isnull(@MAXID,newid()),
            isnull(GROUPMAX + 1,0),
            -1,
            @AMOUNT
        from (select max(GROUPMAXIMUM) as GROUPMAX from @PRICETABLE) PT        

        declare @e int;
        declare @contextCache varbinary(128);

        set @contextCache = CONTEXT_INFO();

        if not @CHANGEAGENTID is null
          set CONTEXT_INFO @CHANGEAGENTID

        -- Remove non-existing prices

        delete from dbo.RATESCALEPRICE
        where 
            RATESCALEID = @ID and
            (
                not exists
                (    
                    select 1 from @PRICETABLE
                    where [@PRICETABLE].ID = RATESCALEPRICE.ID
                )
            )

        if not @contextCache is null
            set CONTEXT_INFO @contextCache

        select @e=@@error;

        if @e<>0 return -456; --always return non-zero sp result if an error occurs    


        -- Update existing prices

        update dbo.RATESCALEPRICE set
            GROUPMINIMUM = [@PRICETABLE].GROUPMINIMUM,
            GROUPMAXIMUM = [@PRICETABLE].GROUPMAXIMUM,
            AMOUNT = [@PRICETABLE].AMOUNT,
            DATECHANGED = @CURRENTDATE,
            CHANGEDBYID = @CHANGEAGENTID
        from @PRICETABLE
        where
            RATESCALEPRICE.ID = [@PRICETABLE].ID and
            (
                RATESCALEPRICE.GROUPMINIMUM <> [@PRICETABLE].GROUPMINIMUM or
                RATESCALEPRICE.GROUPMAXIMUM <> [@PRICETABLE].GROUPMAXIMUM or
                RATESCALEPRICE.AMOUNT <> [@PRICETABLE].AMOUNT
            )

        -- Insert new prices

        insert into dbo.RATESCALEPRICE
        (
            ID,
            RATESCALEID,
            AMOUNT,
            GROUPMINIMUM,
            GROUPMAXIMUM,
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        )
        select
            ID,
            @ID,
            AMOUNT,
            GROUPMINIMUM,
            GROUPMAXIMUM,
            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from @PRICETABLE
        where
            not exists
            (
                select 1 from dbo.RATESCALEPRICE
                where RATESCALEPRICE.ID = [@PRICETABLE].ID
            )

        -- If this includes per ticket pricing, ensure the max price has the proper value

        if @USEPERTICKETAFTERMAX = 1 and @ISSINGLEPRICE = 0
        begin
            declare @MAXPRICE money;
            select top(1) @MAXPRICE = AMOUNT
            from dbo.RATESCALEPRICE
            where RATESCALEID = @ID
            order by GROUPMAXIMUM desc

            update dbo.RATESCALEPRICE
            set AMOUNT = @MAXPRICE,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where RATESCALEID = @ID and GROUPMAXIMUM = -1
        end

        declare @MAXPERCENT decimal(5,2) = 0.0
        select
            @MAXPERCENT = isnull(sum(T.c.value('(PERCENT)[1]','decimal(5,2)')),0)
        from
            @APPLICATIONS.nodes('/APPLICATIONS/ITEM') T(c)

        if @MAXPERCENT <> 100.0
        begin
            raiserror('BBERR_TOTALPERCENT', 13, 1);
            return 1;
        end

        exec dbo.USP_RATESCALE_GETRATESCALEAPPLICATIONS_UPDATEFROMXML @ID, @APPLICATIONS, @CHANGEAGENTID, @CURRENTDATE;

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

return 0;