USP_RESERVATION_UPDATERATESCALE
Inserts a reservation rate scale if it doesn't exist or is different.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | |
| @RATESCALEID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CURRENTDATE | datetime | IN |
Definition
Copy
CREATE procedure dbo.USP_RESERVATION_UPDATERATESCALE
(
@ID uniqueidentifier,
@RATESCALEID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@CURRENTDATE datetime = null
)
as
begin
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
if @CURRENTDATE is null
set @CURRENTDATE = getdate()
declare @INCLUDEALLPROGRAMS bit = 0
declare @INCLUDEALLFEES bit = 0
declare @INCLUDEALLRESOURCES bit = 0
declare @INCLUDEALLSTAFFRESOURCES bit = 0
declare @USEPERTICKETAFTERMAX bit = 0
select
@INCLUDEALLPROGRAMS = INCLUDEALLPROGRAMS,
@INCLUDEALLFEES = INCLUDEALLFEES,
@INCLUDEALLRESOURCES = INCLUDEALLRESOURCES,
@INCLUDEALLSTAFFRESOURCES = INCLUDEALLSTAFFRESOURCES,
@USEPERTICKETAFTERMAX = USEPERTICKETAFTERMAX
from dbo.RATESCALE
where ID = @RATESCALEID
if not exists(select 1 from dbo.RESERVATIONRATESCALE where ID = @ID)
begin
insert into dbo.RESERVATIONRATESCALE
(
ID,
RATESCALEID,
INCLUDEALLPROGRAMS,
INCLUDEALLFEES,
INCLUDEALLRESOURCES,
INCLUDEALLSTAFFRESOURCES,
USEPERTICKETAFTERMAX,
ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED
)
values
(
@ID,
@RATESCALEID,
@INCLUDEALLPROGRAMS,
@INCLUDEALLFEES,
@INCLUDEALLRESOURCES,
@INCLUDEALLSTAFFRESOURCES,
@USEPERTICKETAFTERMAX,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
)
insert into dbo.RESERVATIONRATESCALEPRICE
(
ID,
RESERVATIONRATESCALEID,
AMOUNT,
GROUPMINIMUM,
GROUPMAXIMUM,
ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED
)
select
newid(),
@ID,
AMOUNT,
GROUPMINIMUM,
GROUPMAXIMUM,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from dbo.RATESCALEPRICE
where
RATESCALEID = @RATESCALEID
if @INCLUDEALLPROGRAMS = 0
begin
insert into dbo.RESERVATIONRATESCALEPROGRAM
(
ID,
RESERVATIONRATESCALEID,
PROGRAMID,
ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED
)
select
newid(),
@ID,
PROGRAMID,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from dbo.RATESCALEPROGRAM
where
RATESCALEID = @RATESCALEID
end
if @INCLUDEALLFEES = 0
begin
insert into dbo.RESERVATIONRATESCALEFEE
(
ID,
RESERVATIONRATESCALEID,
FEEID,
ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED
)
select
newid(),
@ID,
FEEID,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from dbo.RATESCALEFEE
where
RATESCALEID = @RATESCALEID
end
if @INCLUDEALLRESOURCES = 0
begin
insert into dbo.RESERVATIONRATESCALERESOURCE
(
ID,
RESERVATIONRATESCALEID,
RESOURCEID,
ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED
)
select
newid(),
@ID,
RESOURCEID,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from dbo.RATESCALERESOURCE
where
RATESCALEID = @RATESCALEID
end
if @INCLUDEALLSTAFFRESOURCES = 0
begin
insert into dbo.RESERVATIONRATESCALESTAFFRESOURCE
(
ID,
RESERVATIONRATESCALEID,
VOLUNTEERTYPEID,
ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED
)
select
newid(),
@ID,
VOLUNTEERTYPEID,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from dbo.RATESCALESTAFFRESOURCE
where
RATESCALEID = @RATESCALEID
end
end
else
begin
update dbo.RESERVATIONRATESCALE set
RATESCALEID = @RATESCALEID,
INCLUDEALLPROGRAMS = @INCLUDEALLPROGRAMS,
INCLUDEALLFEES = @INCLUDEALLFEES,
INCLUDEALLRESOURCES = @INCLUDEALLRESOURCES,
INCLUDEALLSTAFFRESOURCES = @INCLUDEALLSTAFFRESOURCES,
USEPERTICKETAFTERMAX = @USEPERTICKETAFTERMAX,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @ID
declare @contextCache varbinary(128);
set @contextCache = CONTEXT_INFO();
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
delete from dbo.RESERVATIONRATESCALEPRICE
where RESERVATIONRATESCALEID = @ID
-- reset CONTEXT_INFO to previous value
if not @contextCache is null
set CONTEXT_INFO @contextCache;
insert into dbo.RESERVATIONRATESCALEPRICE
(
ID,
RESERVATIONRATESCALEID,
AMOUNT,
GROUPMINIMUM,
GROUPMAXIMUM,
ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED
)
select
newid(),
@ID,
AMOUNT,
GROUPMINIMUM,
GROUPMAXIMUM,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from dbo.RATESCALEPRICE
where
RATESCALEID = @RATESCALEID
if @INCLUDEALLPROGRAMS = 0
begin
insert into dbo.RESERVATIONRATESCALEPROGRAM
(
ID,
RESERVATIONRATESCALEID,
PROGRAMID,
ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED
)
select
newid(),
@ID,
PROGRAMID,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from dbo.RATESCALEPROGRAM
where
RATESCALEID = @RATESCALEID and
PROGRAMID not in
(
select PROGRAMID from dbo.RESERVATIONRATESCALEPROGRAM
where RESERVATIONRATESCALEID = @ID
)
set @contextCache = CONTEXT_INFO();
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
delete from dbo.RESERVATIONRATESCALEPROGRAM
where
RESERVATIONRATESCALEID = @ID and
PROGRAMID not in
(
select PROGRAMID from dbo.RATESCALEPROGRAM
where RATESCALEID = @RATESCALEID
)
-- reset CONTEXT_INFO to previous value
if not @contextCache is null
set CONTEXT_INFO @contextCache;
end
else
begin
set @contextCache = CONTEXT_INFO();
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
delete from dbo.RESERVATIONRATESCALEPROGRAM
where RESERVATIONRATESCALEID = @ID
-- reset CONTEXT_INFO to previous value
if not @contextCache is null
set CONTEXT_INFO @contextCache;
end
if @INCLUDEALLFEES = 0
begin
insert into dbo.RESERVATIONRATESCALEFEE
(
ID,
RESERVATIONRATESCALEID,
FEEID,
ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED
)
select
newid(),
@ID,
FEEID,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from dbo.RATESCALEFEE
where
RATESCALEID = @RATESCALEID and
FEEID not in
(
select FEEID from dbo.RESERVATIONRATESCALEFEE
where RESERVATIONRATESCALEID = @ID
)
set @contextCache = CONTEXT_INFO();
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
delete from dbo.RESERVATIONRATESCALEFEE
where
RESERVATIONRATESCALEID = @ID and
FEEID not in
(
select FEEID from dbo.RATESCALEFEE
where RATESCALEID = @RATESCALEID
)
-- reset CONTEXT_INFO to previous value
if not @contextCache is null
set CONTEXT_INFO @contextCache;
end
else
begin
set @contextCache = CONTEXT_INFO();
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
delete from dbo.RESERVATIONRATESCALEFEE
where RESERVATIONRATESCALEID = @ID
-- reset CONTEXT_INFO to previous value
if not @contextCache is null
set CONTEXT_INFO @contextCache;
end
if @INCLUDEALLRESOURCES = 0
begin
insert into dbo.RESERVATIONRATESCALERESOURCE
(
ID,
RESERVATIONRATESCALEID,
RESOURCEID,
ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED
)
select
newid(),
@ID,
RESOURCEID,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from dbo.RATESCALERESOURCE
where
RATESCALEID = @RATESCALEID and
RESOURCEID not in
(
select RESOURCEID from dbo.RESERVATIONRATESCALERESOURCE
where RESERVATIONRATESCALEID = @ID
)
set @contextCache = CONTEXT_INFO();
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
delete from dbo.RESERVATIONRATESCALERESOURCE
where
RESERVATIONRATESCALEID = @ID and
RESOURCEID not in
(
select RESOURCEID from dbo.RATESCALERESOURCE
where RATESCALEID = @RATESCALEID
)
-- reset CONTEXT_INFO to previous value
if not @contextCache is null
set CONTEXT_INFO @contextCache;
end
else
begin
set @contextCache = CONTEXT_INFO();
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
delete from dbo.RESERVATIONRATESCALERESOURCE
where RESERVATIONRATESCALEID = @ID
-- reset CONTEXT_INFO to previous value
if not @contextCache is null
set CONTEXT_INFO @contextCache;
end
if @INCLUDEALLSTAFFRESOURCES = 0
begin
insert into dbo.RESERVATIONRATESCALESTAFFRESOURCE
(
ID,
RESERVATIONRATESCALEID,
VOLUNTEERTYPEID,
ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED
)
select
newid(),
@ID,
VOLUNTEERTYPEID,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from dbo.RATESCALESTAFFRESOURCE
where
RATESCALEID = @RATESCALEID and
VOLUNTEERTYPEID not in
(
select VOLUNTEERTYPEID from dbo.RESERVATIONRATESCALESTAFFRESOURCE
where RESERVATIONRATESCALEID = @ID
)
set @contextCache = CONTEXT_INFO();
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
delete from dbo.RESERVATIONRATESCALESTAFFRESOURCE
where
RESERVATIONRATESCALEID = @ID and
VOLUNTEERTYPEID not in
(
select VOLUNTEERTYPEID from dbo.RATESCALERESOURCE
where RATESCALEID = @RATESCALEID
)
-- reset CONTEXT_INFO to previous value
if not @contextCache is null
set CONTEXT_INFO @contextCache;
end
else
begin
set @contextCache = CONTEXT_INFO();
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
delete from dbo.RESERVATIONRATESCALESTAFFRESOURCE
where RESERVATIONRATESCALEID = @ID
-- reset CONTEXT_INFO to previous value
if not @contextCache is null
set CONTEXT_INFO @contextCache;
end
end
end