USP_DATAFORMTEMPLATE_VIEW_WEBFORMS_REGISTEREDEVENT_DATA
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | |
| @DATALOADED | bit | INOUT | |
| @NAME | nvarchar(100) | INOUT | |
| @ISACTIVE | bit | INOUT | |
| @PUBLICDESCRIPTIONHTML | nvarchar(max) | INOUT | |
| @REGISTRATIONOPTIONS | xml | INOUT | |
| @EVENTPREFERENCEGROUPS | xml | INOUT | |
| @STARTDATE | datetime | INOUT | |
| @STARTTIME | UDT_HOURMINUTE | INOUT | |
| @ENDTIME | UDT_HOURMINUTE | INOUT | |
| @ISEXPIRED | bit | INOUT | |
| @AVAILABLECAPACITY | int | INOUT | |
| @ISMULTILEVEL | bit | INOUT | |
| @EVENTTYPECODE | tinyint | INOUT | |
| @PRICETYPES | xml | INOUT | |
| @PROGRAMID | uniqueidentifier | INOUT | |
| @ISONSALE | bit | INOUT | |
| @ACKNOWLEDGEMENTEMAILTEMPLATEID | int | INOUT | |
| @ONSALEDATETIME | datetime | INOUT | |
| @EVENTREGISTRATIONINFORMATION | xml | INOUT |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_WEBFORMS_REGISTEREDEVENT_DATA
(
@ID uniqueidentifier,
@DATALOADED bit = 0 output,
@NAME nvarchar(100) = null output,
@ISACTIVE bit = null output,
@PUBLICDESCRIPTIONHTML nvarchar(max) = null output,
@REGISTRATIONOPTIONS xml = null output,
@EVENTPREFERENCEGROUPS xml = null output,
@STARTDATE datetime = null output,
@STARTTIME dbo.UDT_HOURMINUTE = null output,
@ENDTIME dbo.UDT_HOURMINUTE = null output,
@ISEXPIRED bit = null output, --Is after the end of the on-sale window
@AVAILABLECAPACITY integer = null output,
@ISMULTILEVEL bit = null output,
@EVENTTYPECODE tinyint = null output, --0: special, 1: preregistered
@PRICETYPES xml = null output,
@PROGRAMID uniqueidentifier = null output,
@ISONSALE bit = null output, --Is after the beginning of the on-sale window
@ACKNOWLEDGEMENTEMAILTEMPLATEID int = null output,
@ONSALEDATETIME datetime = null output,
@EVENTREGISTRATIONINFORMATION xml = null output
)
as
set nocount on;
-- be sure to set this, in case the select returns no rows
set @DATALOADED = 0;
declare @ENDDATE datetime = null;
declare @CAPACITY integer = null;
declare @CAPACITYRESERVED integer = 0
declare @SUPERCEDESPROGRAMPUBLICDESCRIPTION bit = 0
select
@DATALOADED = 1,
@NAME = [EVENT].[NAME],
@ISACTIVE = case
when [PROGRAM].[ID] is null then [EVENT].[ISACTIVE]
else [PROGRAM].[ISACTIVE]
end,
@SUPERCEDESPROGRAMPUBLICDESCRIPTION = [EVENT].[SUPERCEDESPROGRAMPUBLICDESCRIPTION],
@PUBLICDESCRIPTIONHTML = [EVENT].[PUBLICDESCRIPTIONHTML],
@STARTDATE = dbo.UFN_DATE_ADDHOURMINUTE([EVENT].[STARTDATE], [EVENT].[STARTTIME]),
@ENDDATE = dbo.UFN_DATE_ADDHOURMINUTE([EVENT].[ENDDATE], [EVENT].[ENDTIME]),
@STARTTIME = [EVENT].[STARTTIME],
@ENDTIME = isnull([EVENT].[ENDTIME],''),
@CAPACITY = [EVENT].[CAPACITY],
@CAPACITYRESERVED = [CAPACITY].[RESERVED],
@ISMULTILEVEL = case
when exists (
select 1
from dbo.EVENTMANAGEMENTOPTIONS
where EVENTID = EVENT.ID
) then 1
else 0
end,
@EVENTTYPECODE = case when [PROGRAM].[ID] is not null then 1 else 0 end,
@PROGRAMID = [PROGRAM].[ID],
@ACKNOWLEDGEMENTEMAILTEMPLATEID = isnull([EVENT_MICROSITEEMAILTEMPLATE].[EMAILTEMPLATEID], [PROGRAM_MICROSITEEMAILTEMPLATE].[EMAILTEMPLATEID]),
@EVENTREGISTRATIONINFORMATION = dbo.UFN_EVENT_GETREGISTRATIONINFORMATION_WEBFORMS_TOITEMLISTXML(EVENT.ID)
from dbo.[EVENT]
left join dbo.[EVENT_MICROSITEEMAILTEMPLATE] on
([EVENT].[ID] = [EVENT_MICROSITEEMAILTEMPLATE].[EVENTID]) and ([EVENT_MICROSITEEMAILTEMPLATE].[ACTIVE] = 1)
left join dbo.[PROGRAM] on
[EVENT].[PROGRAMID] = [PROGRAM].[ID]
left join dbo.[PROGRAM_MICROSITEEMAILTEMPLATE] on
([PROGRAM].[ID] = [PROGRAM_MICROSITEEMAILTEMPLATE].[PROGRAMID]) and ([PROGRAM_MICROSITEEMAILTEMPLATE].[ACTIVE] = 1)
outer apply (
select count(REGISTRANT.ID) as [RESERVED]
from dbo.[REGISTRANT]
where
[REGISTRANT].[EVENTID] = @ID and
[REGISTRANT].[ISCANCELLED] = 0 and
[REGISTRANT].[WILLNOTATTEND] = 0
) [CAPACITY]
where [EVENT].[ID] = @ID
declare @SALESMETHODID uniqueidentifier
select @SALESMETHODID = ID from dbo.[SALESMETHOD] where [TYPECODE] = 2
if @DATALOADED = 1
begin
declare @EXPIRATIONDATE datetime = null
declare @EXPIRATIONDATEWITHOFFSET datetimeoffset = null
declare @CURRENTDATETIMEOFFSET datetimeoffset = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(sysutcdatetime(), 1);
if @EVENTTYPECODE = 0 --Special events
begin
--Calculate expiration from end of start date
if @STARTDATE > @ENDDATE
begin
set @ENDDATE = @STARTDATE
set @EXPIRATIONDATE = dbo.UFN_DATE_GETLATESTTIME(@STARTDATE)
end
else if @ENDTIME = ''
set @EXPIRATIONDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE)
else
set @EXPIRATIONDATE = @ENDDATE
set @EXPIRATIONDATEWITHOFFSET = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(@EXPIRATIONDATE, 0);
set @ISEXPIRED = case when @EXPIRATIONDATEWITHOFFSET < @CURRENTDATETIMEOFFSET then 1 else 0 end
set @AVAILABLECAPACITY = @CAPACITY - @CAPACITYRESERVED
if @AVAILABLECAPACITY < 0
set @AVAILABLECAPACITY = 0
set @REGISTRATIONOPTIONS = (
select
[EVENTPRICE].[ID],
[EVENTPRICE].[NAME] as [DESCRIPTION],
[EVENTPRICE].[AMOUNT],
[EVENTPRICE].[RECEIPTAMOUNT],
[EVENTPRICE].[REGISTRATIONCOUNT],
[EVENTREGISTRATIONTYPE].[ISACTIVE] as [TYPEISACTIVE]
from dbo.[EVENTPRICE]
inner join dbo.[EVENTREGISTRATIONTYPE]
on [EVENTPRICE].[EVENTREGISTRATIONTYPEID] = [EVENTREGISTRATIONTYPE].[ID]
where [EVENTPRICE].[EVENTID] = @ID
order by [EVENTPRICE].[NAME] asc
for xml raw('ITEM'),type,elements,root('REGISTRATIONOPTIONS'),binary base64
)
set @ISONSALE = 1
end
else if @EVENTTYPECODE = 1 --Preregistered events
begin
select
@ISONSALE = case
when
[EVENTSALESMETHOD].[ID] is null or
@CURRENTDATETIMEOFFSET < [EVENTSALESMETHOD].[ONSALEDATETIMEWITHOFFSET]
then 0
else 1
end,
@ISEXPIRED = case
when @CURRENTDATETIMEOFFSET >
--Event expiration date with offset
(
case [ONSALEEND].TYPECODE
when 0 then [EVENT].[STARTDATETIMEWITHOFFSET]
when 1 then dateadd(mi, -1 * [ONSALEEND].INTERVAL, [EVENT].[STARTDATETIMEWITHOFFSET])
when 2 then dateadd(hh, -1 * [ONSALEEND].INTERVAL, [EVENT].[STARTDATETIMEWITHOFFSET])
when 3 then dateadd(mi, [ONSALEEND].INTERVAL, [EVENT].[STARTDATETIMEWITHOFFSET])
end
)
then 1
else 0
end,
@ONSALEDATETIME = [EVENTSALESMETHOD].ONSALEDATETIMEWITHOFFSET
from dbo.[EVENT]
left join dbo.[EVENTSALESMETHOD]
on
[EVENT].[ID] = [EVENTSALESMETHOD].[EVENTID] and
[EVENTSALESMETHOD].[SALESMETHODID] = @SALESMETHODID
outer apply(
select top 1
ONSALEENDTYPECODE,
ONSALEENDINTERVAL
from dbo.PROGRAMSALESMETHOD
where
[PROGRAMSALESMETHOD].[PROGRAMID] = [EVENT].[PROGRAMID] and
[PROGRAMSALESMETHOD].[SALESMETHODID] = @SALESMETHODID
) [PROGRAMSALESMETHOD]
cross apply (
select
isnull([PROGRAMSALESMETHOD].ONSALEENDTYPECODE, 0) as [TYPECODE],
ONSALEENDINTERVAL as [INTERVAL]
) as [ONSALEEND]
where [EVENT].[ID] = @ID
select @AVAILABLECAPACITY = AVAILABILITY
from dbo.V_PROGRAMEVENT_TICKETCOUNTS
where ID = @ID
set @PRICETYPES = (
select
[EVENTPRICE].[PRICETYPECODEID] as [ID],
[EVENTPRICE].[DESCRIPTION],
[EVENTPRICE].[FACEPRICE] as [AMOUNT],
[EVENTPRICE].[ISACTIVE],
case
when exists(
select 1
from dbo.UFN_SALESMETHOD_ALLOWEDPRICETYPES(2, 1) [SMP]
where [SMP].ID = [EVENTPRICE].[PRICETYPECODEID]
)
then 1
else 0
end [APPROVEDONLINE]
from dbo.UFN_PROGRAMEVENT_GETPRICES_2(@ID, 1) as [EVENTPRICE]
for xml raw('ITEM'),type,elements,root('PRICETYPES'),binary base64
)
if @SUPERCEDESPROGRAMPUBLICDESCRIPTION = 0
begin
select @PUBLICDESCRIPTIONHTML = [PUBLICDESCRIPTIONHTML]
from dbo.[PROGRAM]
where [ID] = @PROGRAMID
end
end
set @EVENTPREFERENCEGROUPS = (
select
EVENTPREFERENCEGROUP.ID,
EVENTPREFERENCEGROUP.NAME,
(
select
[ID],
[NAME],
[SEQUENCE]
from dbo.[EVENTPREFERENCE]
where [EVENTPREFERENCEGROUPID] = [EVENTPREFERENCEGROUP].[ID]
order by [SEQUENCE]
for xml raw('ITEM'),type,elements,root('EVENTPREFERENCEOPTIONS'),binary base64
)
from dbo.EVENTPREFERENCEGROUP
where EVENTPREFERENCEGROUP.EVENTID = @ID
for xml raw('ITEM'),type,elements,root('EVENTPREFERENCEGROUPS'),binary base64
)
end
return 0;