USP_DATAFORMTEMPLATE_ADD_PROGRAMCOPY
The save procedure used by the add dataform template "Program Copy".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @OLDPROGRAMID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @NAME | nvarchar(100) | IN | Name |
| @DESCRIPTION | nvarchar(255) | IN | Description |
| @SINGLELOCATION | uniqueidentifier | IN | Location |
| @LOCATIONS | xml | IN | Location |
| @HOLDLISTID | uniqueidentifier | IN | Holds |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @CAPACITY | int | IN | Capacity |
| @PROGRAMCATEGORYCODEID | uniqueidentifier | IN | Category |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_PROGRAMCOPY
(
@ID uniqueidentifier = null output,
@OLDPROGRAMID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@NAME nvarchar(100) = '',
@DESCRIPTION nvarchar(255) = '',
@SINGLELOCATION uniqueidentifier = null,
@LOCATIONS xml = null,
@HOLDLISTID uniqueidentifier = null,
@CURRENTAPPUSERID uniqueidentifier,
@CAPACITY int = 0,
@PROGRAMCATEGORYCODEID uniqueidentifier = null
)
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()
declare @ISPREREGISTERED bit;
select @ISPREREGISTERED = ISPREREGISTERED from dbo.PROGRAM where ID = @OLDPROGRAMID;
begin try
-- handle inserting the data
insert into dbo.PROGRAM
(
ID,
NAME,
DESCRIPTION,
HOLDLISTID,
CAPACITY,
ISDAILYADMISSION,
PROGRAMCATEGORYCODEID,
ISPREREGISTERED,
PRICELISTID,
DEFAULTRATESCALEID,
DEFAULTPRICINGSTRUCTURECODE,
PUBLICDESCRIPTIONTEXT,
PUBLICDESCRIPTIONHTML,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
@ID,
@NAME,
@DESCRIPTION,
@HOLDLISTID,
@CAPACITY,
ISDAILYADMISSION,
@PROGRAMCATEGORYCODEID,
ISPREREGISTERED,
PRICELISTID,
DEFAULTRATESCALEID,
DEFAULTPRICINGSTRUCTURECODE,
PUBLICDESCRIPTIONTEXT,
PUBLICDESCRIPTIONHTML,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.PROGRAM
where
ID = @OLDPROGRAMID;
if @SINGLELOCATION is null
begin
-- Default 0 for SEQUENCE since it is a new field and may not be passed in
set @LOCATIONS = (
select
T.c.value('(CAPACITY)[1]','int') as 'CAPACITY',
T.c.value('(EVENTLOCATIONID)[1]','uniqueidentifier') as 'EVENTLOCATIONID',
T.c.value('(ID)[1]','uniqueidentifier') as 'ID',
isnull(T.c.value('(SEQUENCE)[1]','int'), 0) as 'SEQUENCE'
from
@LOCATIONS.nodes('/LOCATIONS/ITEM') T(c)
for xml raw('ITEM'),type,elements,root('LOCATIONS'),binary base64
);
exec dbo.USP_PROGRAM_GETSEQUENCEDLOCATIONS_ADDFROMXML @ID, @LOCATIONS, @CHANGEAGENTID, @CURRENTDATE;
end
else
begin
insert into dbo.PROGRAMLOCATION
(
ID,
EVENTLOCATIONID,
PROGRAMID,
ISDEFAULT,
SEQUENCE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
newid(),
@SINGLELOCATION,
@ID,
1,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
--populate the sales type with the same sales types as the previous program
insert into dbo.PROGRAMSALESMETHOD
(
ID,
PROGRAMID,
SALESMETHODID,
ONSALETYPECODE,
ONSALEDATE,
ONSALETIME,
ONSALETIMEBEFORE,
ONSALEENDTYPECODE,
ONSALEENDINTERVAL,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
@ID,
SALESMETHODID,
case
when ONSALETYPECODE = 1 and ONSALEDATE < dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE) then 0
else ONSALETYPECODE
end,
ONSALEDATE,
ONSALETIME,
ONSALETIMEBEFORE,
ONSALEENDTYPECODE,
ONSALEENDINTERVAL,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.PROGRAMSALESMETHOD
where
PROGRAMID = @OLDPROGRAMID;
--populate the prices with the same prices as the previous program
insert into dbo.PROGRAMPRICE
(
ID,
PROGRAMID,
PRICETYPECODEID,
FACEPRICE,
SEQUENCE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
@ID,
PRICETYPECODEID,
FACEPRICE,
SEQUENCE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.PROGRAMPRICE
where
PROGRAMID = @OLDPROGRAMID;
--copies the program discounts
exec dbo.USP_PROGRAM_COPYDISCOUNTS @OLDPROGRAMID, @ID, @CHANGEAGENTID;
--copies the ticket templates
insert into dbo.PROGRAMDOCUMENT
(
ID,
PROGRAMID,
DOCUMENTID,
SEQUENCE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
@ID,
DOCUMENTID,
SEQUENCE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.PROGRAMDOCUMENT
where
PROGRAMID = @OLDPROGRAMID
--copies the program fees
insert into dbo.PROGRAMFEE
(
ID,
PROGRAMID,
FEEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
@ID,
FEEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.PROGRAMFEE
where
PROGRAMID=@OLDPROGRAMID;
--copies the program taxes
insert into dbo.PROGRAMTAX
(
ID,
PROGRAMID,
TAXID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
@ID,
TAXID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.PROGRAMTAX
where
PROGRAMID=@OLDPROGRAMID;
--insert gl mapping
insert into dbo.PROGRAMGLMAPPING
(
ID,
ACCOUNTNUMBER,
PROJECTCODE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
@ID,
ACCOUNTNUMBER,
PROJECTCODE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.PROGRAMGLMAPPING
where
ID=@OLDPROGRAMID;
--insert program resources
insert into dbo.PROGRAMRESOURCE
(
ID,
PROGRAMID,
RESOURCEID,
QUANTITYNEEDED,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
@ID,
RESOURCEID,
QUANTITYNEEDED,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.PROGRAMRESOURCE
where
PROGRAMID=@OLDPROGRAMID;
--insert program staff resources
insert into dbo.PROGRAMSTAFFRESOURCE
(
ID,
PROGRAMID,
VOLUNTEERTYPEID,
JOBID,
QUANTITYNEEDED,
FILLEDBYCODE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
@ID,
VOLUNTEERTYPEID,
null,
QUANTITYNEEDED,
FILLEDBYCODE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.PROGRAMSTAFFRESOURCE
where
PROGRAMID = @OLDPROGRAMID;
-- Copying preregistered programs (preferences)
if @ISPREREGISTERED = 1
begin
declare @PREFERENCESGROUPTABLE table (OLDID uniqueidentifier, NEWID uniqueidentifier, NAME nvarchar(100));
insert into @PREFERENCESGROUPTABLE
select ID,
newid(),
NAME
from dbo.PROGRAMPREFERENCEGROUP
where PROGRAMID = @OLDPROGRAMID
insert into dbo.PROGRAMPREFERENCEGROUP
(
ID,
PROGRAMID,
NAME,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
select
NEWID,
@ID,
NAME,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @PREFERENCESGROUPTABLE
insert into dbo.PROGRAMPREFERENCE
(
ID,
NAME,
SEQUENCE,
PROGRAMPREFERENCEGROUPID,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
select newid(),
PP.NAME,
PP.SEQUENCE,
PGT.NEWID,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from dbo.PROGRAMPREFERENCE PP
inner join @PREFERENCESGROUPTABLE PGT
on PGT.OLDID = PP.PROGRAMPREFERENCEGROUPID
declare @REGISTRATIONSECTIONS table (OLDID uniqueidentifier, NEWID uniqueidentifier, REGISTRATIONSECTIONCODEID uniqueidentifier, SEQUENCE int);
insert into @REGISTRATIONSECTIONS (OLDID, NEWID, REGISTRATIONSECTIONCODEID, SEQUENCE)
select
ID,
newID(),
REGISTRATIONSECTIONCODEID,
SEQUENCE
from dbo.PROGRAMEVENTREGISTRATIONSECTION
where PROGRAMID = @OLDPROGRAMID;
insert into dbo.PROGRAMEVENTREGISTRATIONSECTION (ID, PROGRAMID, EVENTID, REGISTRATIONSECTIONCODEID, SEQUENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
NEWID,
@ID,
null,
REGISTRATIONSECTIONCODEID,
SEQUENCE,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @REGISTRATIONSECTIONS;
insert into dbo.PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION (ID, PROGRAMEVENTREGISTRATIONSECTIONID, REGISTRATIONINFORMATIONID, SEQUENCE, REQUIRED, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
newID(),
REGISTRATIONSECTIONS.NEWID,
PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION.REGISTRATIONINFORMATIONID,
PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION.SEQUENCE,
PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION.REQUIRED,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @REGISTRATIONSECTIONS REGISTRATIONSECTIONS
inner join dbo.PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION
on REGISTRATIONSECTIONS.OLDID = PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION.PROGRAMEVENTREGISTRATIONSECTIONID;
end
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0