USP_DATAFORMTEMPLATE_EDIT_RATESCALE_PRICES_2
The save procedure used by the edit dataform template "Rate Scale Prices Edit Data Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter indicating the ID of the record being edited. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @AMOUNT | money | IN | Group of any size pays |
| @ISSINGLEPRICE | bit | IN | |
| @PRICES | xml | IN | Prices |
| @USEPERTICKETAFTERMAX | bit | IN | Group over size pays |
Definition
Copy
create procedure dbo.USP_DATAFORMTEMPLATE_EDIT_RATESCALE_PRICES_2
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@AMOUNT money,
@ISSINGLEPRICE bit,
@PRICES xml,
@USEPERTICKETAFTERMAX bit
)
as
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()
--Check to ensure nothing bad happens!
if @ISSINGLEPRICE = 1
set @USEPERTICKETAFTERMAX = 0;
begin try
update dbo.RATESCALE
set
USEPERTICKETAFTERMAX = @USEPERTICKETAFTERMAX,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
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,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where RATESCALEID = @ID and GROUPMAXIMUM = -1
end
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0;