USP_DATAFORMTEMPLATE_ADD_RATESCALE

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

set nocount on;

if @ID is null
    set @ID = newid()

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

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

begin try
    --Check to ensure nothing bad happens!

    if @ISSINGLEPRICE = 1
        set @USEPERTICKETAFTERMAX = 0;

    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);

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

    insert into dbo.RATESCALE
    (
        ID, 
        NAME, 
        DESCRIPTION, 
        ISDEFAULT, 
        INCLUDEALLPROGRAMS,
        INCLUDEALLFEES,
        INCLUDEALLRESOURCES,
        INCLUDEALLSTAFFRESOURCES,
        GROUPSALESGROUPTYPECODEID,
        USEPERTICKETAFTERMAX,
        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
    )
    values
    (
        @ID, 
        @NAME, 
        @DESCRIPTION, 
        @ISDEFAULT, 
        1,
        1,
        1,
        1,
        @GROUPSALESGROUPTYPECODEID,
        @USEPERTICKETAFTERMAX,
        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    )

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

    if @ISSINGLEPRICE = 0
    begin


        insert into @PRICETABLE
        ( ID, GROUPMINIMUM, GROUPMAXIMUM, AMOUNT )
        select
            newid(),
            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

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


    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

    -- 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,
            DATECHANGED = @CURRENTDATE,
            CHANGEDBYID = @CHANGEAGENTID                            
        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_ADDFROMXML @ID, @APPLICATIONS, @CHANGEAGENTID, @CURRENTDATE;

end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0