USP_DATAFORMTEMPLATE_VIEW_DAILYSALEITEMBUTTONS
The load procedure used by the view dataform template "Daily Sale Item Buttons"
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter used to load the fields defined on the form. |
| @DATALOADED | bit | INOUT | Output parameter indicating whether or not data was actually loaded. |
| @DAILYADMISSIONBUTTONS | xml | INOUT | DAILYADMISSIONBUTTONS |
| @MEMBERSHIPBUTTONS | xml | INOUT | MEMBERSHIPBUTTONS |
| @MEMBERSHIPRENEWBUTTONS | xml | INOUT | MEMBERSHIPRENEWBUTTONS |
| @DISCOUNTBUTTONS | xml | INOUT | DISCOUNTBUTTONS |
| @DONATIONBUTTONS | xml | INOUT | DONATIONBUTTONS |
| @SCHEDULEDPROGRAMBUTTONS | xml | INOUT | SCHEDULEDPROGRAMBUTTONS |
| @HASCONFIGUREDDISCOUNTS | bit | INOUT | System has configured discounts |
| @HASCONFIGUREDSCHEDULEDPROGRAMS | bit | INOUT | System has scheduled programs |
| @EVENTREGISTRATIONBUTTONS | xml | INOUT | EVENTREGISTRATIONBUTTONS |
| @HASCONFIGUREDEVENTREGISTRATIONS | bit | INOUT | System has scheduled programs |
| @COMBINATIONBUTTONS | xml | INOUT | COMBINATIONBUTTONS |
| @MEMBERSHIPPROMOBUTTONS | xml | INOUT | MEMBERSHIPPROMOBUTTONS |
| @HASCONFIGUREDMERCHANDISE | bit | INOUT | System has configured merchandise |
| @HASCONFIGUREDMEMBERSHIPS | bit | INOUT | System has configured memberships |
| @HASMIDTERMUPGRADES | bit | INOUT | Patron has mid-term membership upgrade available |
| @GENERICBUTTONS | xml | INOUT | GENERICBUTTONS |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_DAILYSALEITEMBUTTONS
(
@ID uniqueidentifier,
@DATALOADED bit = 0 output,
@DAILYADMISSIONBUTTONS xml = null output,
@MEMBERSHIPBUTTONS xml = null output,
@MEMBERSHIPRENEWBUTTONS xml = null output,
@DISCOUNTBUTTONS xml = null output,
@DONATIONBUTTONS xml = null output,
@SCHEDULEDPROGRAMBUTTONS xml = null output,
@HASCONFIGUREDDISCOUNTS bit = null output,
@HASCONFIGUREDSCHEDULEDPROGRAMS bit = null output,
@EVENTREGISTRATIONBUTTONS xml = null output,
@HASCONFIGUREDEVENTREGISTRATIONS bit = null output,
@COMBINATIONBUTTONS xml = null output,
@MEMBERSHIPPROMOBUTTONS xml = null output,
@HASCONFIGUREDMERCHANDISE bit = null output,
@HASCONFIGUREDMEMBERSHIPS bit = null output,
@HASMIDTERMUPGRADES bit = null output,
@GENERICBUTTONS xml = null output
)
as
set nocount on;
set @DATALOADED = 1;
declare @CURRENTDATETIME datetime = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEFROMUTC(getutcdate());
declare @CURRENTDATE date = @CURRENTDATETIME;
declare @CURRENTDATETIMEOFFSET datetimeoffset;
set @CURRENTDATETIMEOFFSET = sysdatetimeoffset()
declare @CONSTITUENTID uniqueidentifier = (select [CONSTITUENTID] from dbo.[SALESORDER] where [ID] = @ID)
declare @SALESMETHODID uniqueidentifier
select @SALESMETHODID = [ID]
from dbo.[SALESMETHOD]
where [TYPECODE] = 0
set @DAILYADMISSIONBUTTONS = (
select
[DAILYSALEITEM].[ID] as [QUICKBUTTONID],
[DAILYSALEITEM].[TYPECODE] as [TYPECODE],
[PROGRAM].[ID] as [PROGRAMID],
[PROGRAM].[NAME] as [PROGRAMNAME],
[PRICETYPECODE].[ID] as [PRICETYPECODEID],
[PRICETYPECODE].[DESCRIPTION] as [PRICETYPE],
[PROGRAMPRICE].[FACEPRICE] as [FACEPRICE],
[DAILYSALEITEM].[DESCRIPTIONFIELD1TYPECODE] as [LINE1TYPECODE],
[DAILYSALEITEM].[DESCRIPTIONFIELD2TYPECODE] as [LINE2TYPECODE],
[DAILYSALEITEM].[DESCRIPTIONFIELD3TYPECODE] as [LINE3TYPECODE],
[DAILYSALEITEM].[DESCRIPTIONFIELD1] as [LINE1DESCRIPTION],
[DAILYSALEITEM].[DESCRIPTIONFIELD2] as [LINE2DESCRIPTION],
[DAILYSALEITEM].[DESCRIPTIONFIELD3] as [LINE3DESCRIPTION],
[DAILYSALEITEM].[BUTTONORDER]
from dbo.[DAILYSALEITEM]
inner join dbo.[DAILYSALEITEMPROGRAM]
on [DAILYSALEITEM].[ID] = [DAILYSALEITEMPROGRAM].[ID]
inner join dbo.[PROGRAM]
on [DAILYSALEITEMPROGRAM].[PROGRAMID] = [PROGRAM].[ID]
inner join dbo.[PRICETYPECODE]
on [DAILYSALEITEMPROGRAM].[PRICETYPECODEID] = [PRICETYPECODE].[ID]
inner join dbo.[PROGRAMPRICE]
on
[PROGRAM].[ID] = [PROGRAMPRICE].[PROGRAMID] and
[DAILYSALEITEMPROGRAM].[PRICETYPECODEID] = [PROGRAMPRICE].[PRICETYPECODEID]
where
[DAILYSALEITEM].[ISACTIVE] = 1 and
[DAILYSALEITEMPROGRAM].[ISDAILYADMISSION] = 1 and
[PROGRAM].[ISACTIVE] = 1 and
--Program is valid for sales method
exists (
select 1
from dbo.[PROGRAMSALESMETHOD]
inner join dbo.[SALESMETHOD]
on [PROGRAMSALESMETHOD].[SALESMETHODID] = [SALESMETHOD].[ID]
where
[PROGRAMSALESMETHOD].[PROGRAMID] = [PROGRAM].[ID] and
[SALESMETHOD].[TYPECODE] = 0
) and
--Price type is valid for daily sales
not exists (
select 1
from dbo.[SALESMETHODEXCLUDEDPRICETYPE]
inner join dbo.[SALESMETHOD]
on [SALESMETHODEXCLUDEDPRICETYPE].[SALESMETHODID] = [SALESMETHOD].[ID]
where
[SALESMETHOD].[TYPECODE] = 0 and
[SALESMETHODEXCLUDEDPRICETYPE].[PRICETYPECODEID] = [PRICETYPECODE].[ID]
)
for xml raw ('ITEM'), type, elements, root('DAILYADMISSIONBUTTONS'), BINARY BASE64
)
set @MEMBERSHIPBUTTONS = (
select
[DAILYSALEITEM].[ID] as [QUICKBUTTONID],
[DAILYSALEITEM].[TYPECODE] as [TYPECODE],
coalesce(( --Automatically load pending membership if member has one
select top 1 [MEMBERSHIP].[ID]
from dbo.[MEMBER]
inner join dbo.[MEMBERSHIP]
on [MEMBER].[MEMBERSHIPID] = [MEMBERSHIP].[ID]
where
[MEMBERSHIP].[MEMBERSHIPPROGRAMID] = [MEMBERSHIPPROGRAM].[ID] and
[MEMBER].[CONSTITUENTID] = @CONSTITUENTID and
[MEMBERSHIP].[STATUSCODE] = 2
),'00000000-0000-0000-0000-000000000000') as [INITIALMEMBERSHIPID],
[MEMBERSHIPPROGRAM].[ID] as [MEMBERSHIPPROGRAMID],
[MEMBERSHIPPROGRAM].[NAME] as [MEMBERSHIPPROGRAMNAME],
[MEMBERSHIPLEVELTERM].[AMOUNT] as [FACEPRICE],
[MEMBERSHIPLEVEL].[NAME] as [MEMBERSHIPLEVELNAME],
[MEMBERSHIPLEVEL].[ID] as [MEMBERSHIPLEVELID],
[MEMBERSHIPLEVELTERM].[TERM] as [MEMBERSHIPLEVELTERMNAME],
[MEMBERSHIPLEVELTERM].[ID] as [MEMBERSHIPLEVELTERMID],
coalesce([MEMBERSHIPLEVELTYPECODE].[DESCRIPTION],'') as [MEMBERSHIPLEVELTYPECODENAME],
coalesce([MEMBERSHIPLEVELTYPECODE].[ID],'00000000-0000-0000-0000-000000000000') as [MEMBERSHIPLEVELTYPECODEID],
[DAILYSALEITEM].[DESCRIPTIONFIELD1TYPECODE] as [LINE1TYPECODE],
[DAILYSALEITEM].[DESCRIPTIONFIELD2TYPECODE] as [LINE2TYPECODE],
[DAILYSALEITEM].[DESCRIPTIONFIELD3TYPECODE] as [LINE3TYPECODE],
[DAILYSALEITEM].[DESCRIPTIONFIELD1] as [LINE1DESCRIPTION],
[DAILYSALEITEM].[DESCRIPTIONFIELD2] as [LINE2DESCRIPTION],
[DAILYSALEITEM].[DESCRIPTIONFIELD3] as [LINE3DESCRIPTION],
[DAILYSALEITEM].[BUTTONORDER]
from dbo.[DAILYSALEITEM]
inner join dbo.[DAILYSALEITEMMEMBERSHIP]
on [DAILYSALEITEM].[ID] = [DAILYSALEITEMMEMBERSHIP].[ID]
inner join dbo.[MEMBERSHIPPROGRAM]
on [DAILYSALEITEMMEMBERSHIP].[MEMBERSHIPPROGRAMID] = [MEMBERSHIPPROGRAM].[ID]
inner join dbo.[MEMBERSHIPLEVEL]
on [DAILYSALEITEMMEMBERSHIP].[MEMBERSHIPLEVELID] = [MEMBERSHIPLEVEL].[ID]
inner join dbo.[MEMBERSHIPLEVELTERM]
on [DAILYSALEITEMMEMBERSHIP].[MEMBERSHIPLEVELTERMID] = [MEMBERSHIPLEVELTERM].[ID]
left join dbo.[MEMBERSHIPLEVELTYPECODE]
on [DAILYSALEITEMMEMBERSHIP].[MEMBERSHIPLEVELTYPECODEID] = [MEMBERSHIPLEVELTYPECODE].[ID]
where
[DAILYSALEITEM].[ISACTIVE] = 1 and
[MEMBERSHIPPROGRAM].[ISACTIVE] = 1 and
[MEMBERSHIPLEVEL].[ISACTIVE] = 1 and
--Check that the membership program does allow multiple memberships if the member already has an active membership for that program
not exists (
select 1
from dbo.[MEMBER]
inner join dbo.[MEMBERSHIP]
on [MEMBER].[MEMBERSHIPID] = [MEMBERSHIP].[ID]
where
[MEMBER].[CONSTITUENTID] = @CONSTITUENTID and
[MEMBERSHIPPROGRAM].[ALLOWMULTIPLEMEMBERSHIPS] = 0 and
[MEMBERSHIP].[MEMBERSHIPPROGRAMID] = [MEMBERSHIPPROGRAM].[ID] and
[MEMBER].[ISDROPPED] = 0 and
[MEMBERSHIP].[STATUSCODE] in (0,1)
)
for xml raw ('ITEM'), type, elements, root('MEMBERSHIPBUTTONS'), BINARY BASE64
)
declare @MEMBERSHIPRENEWBUTTONORDER int
select
@MEMBERSHIPRENEWBUTTONORDER = [BUTTONORDER]
from dbo.[DAILYSALESGENERICACTION]
where [TYPECODE] = 7
and [ISACTIVE] = 1
declare @MEMBERSHIPRENEWBUTTONTABLE table (
[TYPECODE] int,
[MEMBERSHIPID] uniqueidentifier,
[NUMBEROFCHILDREN] int,
[COMMENTS] nvarchar(255),
[MEMBERSHIPPROGRAMID] uniqueidentifier,
[MEMBERSHIPPROGRAMNAME] nvarchar(100),
[FACEPRICE] money,
[MEMBERSHIPLEVELNAME] nvarchar(100),
[MEMBERSHIPLEVELID] uniqueidentifier,
[MEMBERSHIPLEVELTERMNAME] nvarchar(100),
[MEMBERSHIPLEVELTERMID] uniqueidentifier,
[MEMBERSHIPLEVELTYPECODENAME] nvarchar(100),
[MEMBERSHIPLEVELTYPECODEID] uniqueidentifier,
[EXPIRATIONDATE] datetime,
[CURRENTEXPIRATIONDATE] datetime,
[RENEWTYPECODE] int,
[SORTORDER] int,
[INRENEWWINDOW] bit,
[BUTTONORDER] int,
[AFTEREXPIRATION] int
)
if @MEMBERSHIPRENEWBUTTONORDER is not null
begin
insert into @MEMBERSHIPRENEWBUTTONTABLE
(
[TYPECODE],
[MEMBERSHIPID],
[NUMBEROFCHILDREN],
[COMMENTS],
[MEMBERSHIPPROGRAMID],
[MEMBERSHIPPROGRAMNAME],
[FACEPRICE],
[MEMBERSHIPLEVELNAME],
[MEMBERSHIPLEVELID],
[MEMBERSHIPLEVELTERMNAME],
[MEMBERSHIPLEVELTERMID],
[MEMBERSHIPLEVELTYPECODENAME],
[MEMBERSHIPLEVELTYPECODEID],
[EXPIRATIONDATE],
[CURRENTEXPIRATIONDATE],
[RENEWTYPECODE],
[SORTORDER],
[INRENEWWINDOW],
[BUTTONORDER],
[AFTEREXPIRATION]
)
select
2 as [TYPECODE],
[MEMBERSHIP].[ID] as [MEMBERSHIPID],
[MEMBERSHIP].[NUMBEROFCHILDREN],
[MEMBERSHIP].[COMMENTS],
[MEMBERSHIPPROGRAM].[ID] as [MEMBERSHIPPROGRAMID],
[MEMBERSHIPPROGRAM].[NAME] as [MEMBERSHIPPROGRAMNAME],
[RENEWOPTIONS].[FACEPRICE],
[RENEWOPTIONS].[MEMBERSHIPLEVELNAME],
[RENEWOPTIONS].[MEMBERSHIPLEVELID],
[RENEWOPTIONS].[MEMBERSHIPLEVELTERMNAME],
[RENEWOPTIONS].[MEMBERSHIPLEVELTERMID],
dbo.UFN_MEMBERSHIPLEVELTYPECODE_GETDESCRIPTION([RENEWOPTIONS].[MEMBERSHIPLEVELTYPECODEID]) as [MEMBERSHIPLEVELTYPECODENAME],
[RENEWOPTIONS].[MEMBERSHIPLEVELTYPECODEID],
[RENEWOPTIONS].[EXPIRATIONDATE],
case [MEMBERSHIP].[STATUSCODE]
when 1 then coalesce((
select top 1 [MEMBERSHIPTRANSACTION].[TRANSACTIONDATE]
from dbo.[MEMBERSHIPTRANSACTION]
where
[MEMBERSHIPTRANSACTION].[MEMBERSHIPID] = [MEMBERSHIP].[ID] and
[MEMBERSHIPTRANSACTION].[ACTIONCODE] = 4
order by [MEMBERSHIPTRANSACTION].[DATEADDED] desc
), [MEMBERSHIP].[EXPIRATIONDATE]
)
else [MEMBERSHIP].[EXPIRATIONDATE]
end as [CURRENTEXPIRATIONDATE],
[RENEWOPTIONS].[RENEWTYPECODE],
case [RENEWOPTIONS].[RENEWTYPECODE]
when 1 then 0
when 0 then 1
when 2 then 2
end as [SORTORDER],
case
when @CURRENTDATETIME > dateadd(month, -1 * [INITIALML].[BEFOREEXPIRATION], [MEMBERSHIP].[EXPIRATIONDATE]) and @CURRENTDATETIME < dateadd(month, [INITIALML].[AFTEREXPIRATION], [MEMBERSHIP].[EXPIRATIONDATE]) then 1
else 0
end as [INRENEWWINDOW],
@MEMBERSHIPRENEWBUTTONORDER as [BUTTONORDER],
[INITIALML].[AFTEREXPIRATION]
from dbo.[MEMBER]
inner join dbo.[MEMBERSHIP]
on [MEMBER].[MEMBERSHIPID] = [MEMBERSHIP].[ID] and [MEMBER].[ISDROPPED] = 0
inner join dbo.[MEMBERSHIPPROGRAM]
on [MEMBERSHIP].[MEMBERSHIPPROGRAMID] = [MEMBERSHIPPROGRAM].[ID]
inner join dbo.[MEMBERSHIPLEVEL] as [INITIALML]
on [MEMBERSHIP].[MEMBERSHIPLEVELID] = [INITIALML].[ID]
--Get the valid renew/upgrade membership levels
cross apply (
select
[MEMBERSHIPLEVEL].[ID] as [MEMBERSHIPLEVELID],
[MEMBERSHIPLEVEL].[NAME] as [MEMBERSHIPLEVELNAME],
[MEMBERSHIPLEVELTERM].[ID] as [MEMBERSHIPLEVELTERMID],
[MEMBERSHIPLEVELTERM].[TERM] as [MEMBERSHIPLEVELTERMNAME],
[MEMBERSHIPLEVELTERM].[AMOUNT] as [FACEPRICE],
[RENEWTYPE].[RENEWTYPECODE] as [RENEWTYPECODE],
case
when [MEMBERSHIP].[STATUSCODE] = 0 and dateadd(month, [INITIALML].[AFTEREXPIRATION], [MEMBERSHIP].[EXPIRATIONDATE]) >= @CURRENTDATE then dbo.UFN_MEMBERSHIP_CALCULATEEXPIRATIONDATEBYLEVEL([MEMBERSHIPLEVEL].[ID], [MEMBERSHIPLEVELTERM].[ID], [MEMBERSHIP].[EXPIRATIONDATE])
else dbo.UFN_MEMBERSHIP_CALCULATEEXPIRATIONDATEBYLEVEL([MEMBERSHIPLEVEL].[ID], [MEMBERSHIPLEVELTERM].[ID], @CURRENTDATE)
end as [EXPIRATIONDATE],
(select [MEMBERSHIP].[MEMBERSHIPLEVELTYPECODEID] from dbo.[MEMBERSHIPLEVELTYPE] where [LEVELID] = [MEMBERSHIPLEVEL].[ID] and [LEVELTYPECODEID] = [MEMBERSHIP].[MEMBERSHIPLEVELTYPECODEID]) as [MEMBERSHIPLEVELTYPECODEID]
from dbo.[MEMBERSHIPLEVEL]
--Get the membership level term equivalent to the current membership level term
--If the upgrade membership level doesn't have an equivalent, we'll handle that on the form
left join dbo.[MEMBERSHIPLEVELTERM]
on
[MEMBERSHIPLEVEL].[ID] = [MEMBERSHIPLEVELTERM].[LEVELID] and
[MEMBERSHIPLEVELTERM].[TERMCODE] = (select [MLT].[TERMCODE] from dbo.[MEMBERSHIPLEVELTERM] as [MLT] where [MLT].[ID] = [MEMBERSHIP].[MEMBERSHIPLEVELTERMID])
cross apply (
select case
when [MEMBERSHIP].[STATUSCODE] = 1 or ([MEMBERSHIP].[MEMBERSHIPLEVELID] = [MEMBERSHIPLEVEL].[ID] and dateadd(month, [INITIALML].[AFTEREXPIRATION], [MEMBERSHIP].[EXPIRATIONDATE]) < @CURRENTDATETIME) then 1 --Rejoin
when [MEMBERSHIP].[MEMBERSHIPLEVELID] = [MEMBERSHIPLEVEL].[ID] then 0 --Renew
else 2 --Upgrade
end as [RENEWTYPECODE]
) as [RENEWTYPE]
where
[MEMBERSHIPLEVEL].[MEMBERSHIPPROGRAMID] = [MEMBERSHIP].[MEMBERSHIPPROGRAMID] and
(
[MEMBERSHIPLEVEL].[ID] = [MEMBERSHIP].[MEMBERSHIPLEVELID] or
--Include the membership level that is one greater than the current one for Upgrade (if it's not a rejoin)
(
[RENEWTYPE].[RENEWTYPECODE] = 2 and
[MEMBERSHIPLEVEL].[ID] = (select top(1) ID
from dbo.MEMBERSHIPLEVEL MASTERML
where MASTERML.SEQUENCE > INITIALML.SEQUENCE
and MASTERML.MEMBERSHIPPROGRAMID = INITIALML.MEMBERSHIPPROGRAMID
and MASTERML.ISACTIVE = 1
and MASTERML.[CHILDRENALLOWED] >= [MEMBERSHIP].[NUMBEROFCHILDREN]
and --Level allows the number of members in the membership
(
(
select count(1)
from dbo.[MEMBER]
where
[MEMBER].[MEMBERSHIPID] = [MEMBERSHIP].[ID]
and [MEMBER].[ISDROPPED] = 0
) <= MASTERML.[MEMBERSALLOWED] or
MASTERML.[MEMBERSALLOWED] = 0
)
and --Level allows for the number of membership cards in the membership
(
(
select count(1)
from dbo.[MEMBERSHIPCARD]
inner join dbo.[MEMBER]
on [MEMBERSHIPCARD].[MEMBERID] = [MEMBER].[ID]
where
[MEMBER].[MEMBERSHIPID] = [MEMBERSHIP].[ID] and
[MEMBERSHIPCARD].[STATUSCODE] <> 2
) <= MASTERML.[CARDSALLOWED] --or
--MASTERML.[CARDSALLOWED] = 0
)
order by SEQUENCE asc)
)
) and
[MEMBERSHIPLEVEL].[ISACTIVE] = 1
) as [RENEWOPTIONS]
where
[MEMBER].[CONSTITUENTID] = @CONSTITUENTID and
[MEMBERSHIPPROGRAM].[ISACTIVE] = 1 and
[MEMBERSHIP].[STATUSCODE] in (0,1)
end
insert into @MEMBERSHIPRENEWBUTTONTABLE
(
[TYPECODE],
[MEMBERSHIPID],
[NUMBEROFCHILDREN],
[COMMENTS],
[MEMBERSHIPPROGRAMID],
[MEMBERSHIPPROGRAMNAME],
[FACEPRICE],
[MEMBERSHIPLEVELNAME],
[MEMBERSHIPLEVELID],
[MEMBERSHIPLEVELTERMNAME],
[MEMBERSHIPLEVELTERMID],
[MEMBERSHIPLEVELTYPECODENAME],
[MEMBERSHIPLEVELTYPECODEID],
[EXPIRATIONDATE],
[CURRENTEXPIRATIONDATE],
[RENEWTYPECODE],
[SORTORDER],
[INRENEWWINDOW],
[BUTTONORDER]
)
select
2 as [TYPECODE],
[MEMBERSHIP].[ID] as [MEMBERSHIPID],
[MEMBERSHIP].[NUMBEROFCHILDREN],
[MEMBERSHIP].[COMMENTS],
[MEMBERSHIPPROGRAM].[ID] as [MEMBERSHIPPROGRAMID],
[MEMBERSHIPPROGRAM].[NAME] as [MEMBERSHIPPROGRAMNAME],
dbo.UFN_MEMBERSHIP_GETMIDTERMUPGRADEPRICE([MEMBERSHIP].[ID], [MEMBERSHIPLEVEL].[ID]) as [FACEPRICE],
[MEMBERSHIPLEVEL].[NAME] as [MEMBERSHIPLEVELNAME],
[MEMBERSHIPLEVEL].[ID] as [MEMBERSHIPLEVELID],
[MEMBERSHIPLEVELTERM].[TERM] as [MEMBERSHIPLEVELTERMNAME],
[MEMBERSHIPLEVELTERM].[ID] as [MEMBERSHIPLEVELTERMID],
null as [MEMBERSHIPLEVELTYPECODENAME],
null as [MEMBERSHIPLEVELTYPECODEID],
[MEMBERSHIP].[EXPIRATIONDATE],
[MEMBERSHIP].[EXPIRATIONDATE] as [CURRENTEXPIRATIONDATE],
3 as [RENEWTYPECODE],
row_number() over (order by MEMBERSHIP.EXPIRATIONDATE asc, MEMBERSHIP.LOOKUPID asc, MEMBERSHIPLEVEL.SEQUENCE asc) + 2 as [SORTORDER],
case
when @CURRENTDATETIME > dateadd(month, -1 * [INITIALML].[BEFOREEXPIRATION], [MEMBERSHIP].[EXPIRATIONDATE]) and @CURRENTDATETIME < dateadd(month, [INITIALML].[AFTEREXPIRATION], [MEMBERSHIP].[EXPIRATIONDATE]) then 1
else 0
end as [INRENEWWINDOW],
@MEMBERSHIPRENEWBUTTONORDER as [BUTTONORDER]
from dbo.[MEMBER]
inner join dbo.[MEMBERSHIP]
on [MEMBER].[MEMBERSHIPID] = [MEMBERSHIP].[ID] and [MEMBER].[ISDROPPED] = 0
inner join dbo.[MEMBERSHIPPROGRAM]
on [MEMBERSHIP].[MEMBERSHIPPROGRAMID] = [MEMBERSHIPPROGRAM].[ID]
inner join dbo.[MEMBERSHIPLEVEL] as [INITIALML]
on [MEMBERSHIP].[MEMBERSHIPLEVELID] = [INITIALML].[ID]
inner join dbo.[MEMBERSHIPLEVEL]
on [MEMBERSHIPLEVEL].[MEMBERSHIPPROGRAMID] = [MEMBERSHIPPROGRAM].[ID]
inner join dbo.[MEMBERSHIPLEVELTERM]
on [MEMBERSHIPLEVEL].[ID] = [MEMBERSHIPLEVELTERM].[LEVELID] and
[MEMBERSHIPLEVELTERM].[TERMCODE] = (select [MLT].[TERMCODE] from dbo.[MEMBERSHIPLEVELTERM] as [MLT] where [MLT].[ID] = [MEMBERSHIP].[MEMBERSHIPLEVELTERMID])
where
[MEMBER].[CONSTITUENTID] = @CONSTITUENTID and
[MEMBERSHIPPROGRAM].[ISACTIVE] = 1 and
[MEMBERSHIP].[STATUSCODE] = 0 and
[MEMBERSHIP].[EXPIRATIONDATE] >= @CURRENTDATE and
[MEMBERSHIPLEVEL].[CHILDRENALLOWED] >= [MEMBERSHIP].[NUMBEROFCHILDREN] and
[MEMBERSHIPLEVEL].[ISACTIVE] = 1 and
--Level allows the number of members in the membership
(
(
select count(1)
from dbo.[MEMBER]
where
[MEMBER].[MEMBERSHIPID] = [MEMBERSHIP].[ID]
and [MEMBER].[ISDROPPED] = 0
) <= [MEMBERSHIPLEVEL].[MEMBERSALLOWED] or
[MEMBERSHIPLEVEL].[MEMBERSALLOWED] = 0
) and
--Level allows for the number of membership cards in the membership
(
(
select count(1)
from dbo.[MEMBERSHIPCARD]
inner join dbo.[MEMBER]
on [MEMBERSHIPCARD].[MEMBERID] = [MEMBER].[ID]
where
[MEMBER].[MEMBERSHIPID] = [MEMBERSHIP].[ID] and
[MEMBERSHIPCARD].[STATUSCODE] <> 2
) <= [MEMBERSHIPLEVEL].[CARDSALLOWED] --or
--[MEMBERSHIPLEVEL].[CARDSALLOWED] = 0
) and
[MEMBERSHIPLEVEL].[SEQUENCE] > [INITIALML].[SEQUENCE]
set @MEMBERSHIPRENEWBUTTONS = (
select *
from @MEMBERSHIPRENEWBUTTONTABLE
--Order by the membership that is nearest to leaving its renewal period
order by
[SORTORDER] asc,
datediff(day, @CURRENTDATE, dateadd(month, [AFTEREXPIRATION], [CURRENTEXPIRATIONDATE])) asc
for xml raw ('ITEM'), type, elements, root('MEMBERSHIPRENEWBUTTONS'), BINARY BASE64
)
set @DISCOUNTBUTTONS = (
select
[DAILYSALEITEM].[ID] as [QUICKBUTTONID],
[DAILYSALEITEM].[TYPECODE] as [TYPECODE],
[DISCOUNT].[ID] as [DISCOUNTID],
[DISCOUNT].[NAME] as [DISCOUNTNAME],
[DISCOUNT].[CALCULATIONTYPE] as [DISCOUNTCALCULATIONTYPE],
[DISCOUNT].[CALCULATIONTYPECODE] as [DISCOUNTCALCULATIONTYPECODE],
0 as [DISCOUNTVALUE], --Use min and max value now
[DAILYSALEITEM].[DESCRIPTIONFIELD1TYPECODE] as [LINE1TYPECODE],
[DAILYSALEITEM].[DESCRIPTIONFIELD2TYPECODE] as [LINE2TYPECODE],
[DAILYSALEITEM].[DESCRIPTIONFIELD3TYPECODE] as [LINE3TYPECODE],
[DAILYSALEITEM].[DESCRIPTIONFIELD1] as [LINE1DESCRIPTION],
[DAILYSALEITEM].[DESCRIPTIONFIELD2] as [LINE2DESCRIPTION],
[DAILYSALEITEM].[DESCRIPTIONFIELD3] as [LINE3DESCRIPTION],
[DAILYSALEITEMDISCOUNT].[DISCOUNTLIMITAPPLICATIONTYPECODE] as [DISCOUNTLIMITAPPLICATIONTYPECODE],
case [DISCOUNT].[APPLIESTOCODE]
when 1 then -- When item-level discount
case [DISCOUNT].[CALCULATIONTYPECODE]
when 1 then --percent
case [DISCOUNT].[DISCOUNTTYPECODE]
when 2 then
(select max([PERCENT]) from dbo.[GROUPSIZEDISCOUNT] where [GROUPSIZEDISCOUNT].[DISCOUNTID] = [DISCOUNT].[ID])
else
(select max([PERCENT])
from
(select [PERCENT] from dbo.[DISCOUNTPRICETYPE] where [DISCOUNTPRICETYPE].[DISCOUNTID] = [DISCOUNT].[ID]
union all
select [DISCOUNT].[MERCHANDISEPERCENT] as [PERCENT]) as [PERCENTS]
)
end
else --amount or specific value
case [DISCOUNT].[DISCOUNTTYPECODE]
when 2 then
(select max([AMOUNT]) from dbo.[GROUPSIZEDISCOUNT] where [GROUPSIZEDISCOUNT].[DISCOUNTID] = [DISCOUNT].[ID])
else
(select max([AMOUNT])
from
(select [AMOUNT] from dbo.[DISCOUNTPRICETYPE] where [DISCOUNTPRICETYPE].[DISCOUNTID] = [DISCOUNT].[ID]
union all
select [DISCOUNT].[MERCHANDISEAMOUNT] as [AMOUNT]) as [AMOUNTS]
)
end
end
else --When order-level discount
case [CALCULATIONTYPECODE]
when 0 then
[DISCOUNT].[AMOUNT]
else
[DISCOUNT].[PERCENT]
end
end DISCOUNTMAXVALUE,
case [DISCOUNT].[APPLIESTOCODE]
when 1 then -- When item-level discount
case [DISCOUNT].[CALCULATIONTYPECODE]
when 1 then --percent
case [DISCOUNT].[DISCOUNTTYPECODE]
when 2 then
(select min([PERCENT]) from dbo.[GROUPSIZEDISCOUNT] where [GROUPSIZEDISCOUNT].[DISCOUNTID] = [DISCOUNT].[ID])
else
(select min([PERCENT])
from
(select [PERCENT] from dbo.[DISCOUNTPRICETYPE] where [DISCOUNTPRICETYPE].[DISCOUNTID] = [DISCOUNT].[ID]
union all
select [DISCOUNT].[MERCHANDISEPERCENT] as [PERCENT]) as [PERCENTS]
where [PERCENT] > 0
)
end
else --amount or specific value
case [DISCOUNT].[DISCOUNTTYPECODE]
when 2 then
(select min([AMOUNT]) from dbo.[GROUPSIZEDISCOUNT] where [GROUPSIZEDISCOUNT].[DISCOUNTID] = [DISCOUNT].[ID])
else
(select min([AMOUNT])
from
(select [AMOUNT] from dbo.[DISCOUNTPRICETYPE] where [DISCOUNTPRICETYPE].[DISCOUNTID] = [DISCOUNT].[ID]
union all
select [DISCOUNT].[MERCHANDISEAMOUNT] as [AMOUNT]) as [AMOUNTS]
where [AMOUNT] > 0
)
end
end
else --When order-level discount
case [CALCULATIONTYPECODE]
when 0 then
[DISCOUNT].[AMOUNT]
else
[DISCOUNT].[PERCENT]
end
end DISCOUNTMINVALUE,
[dbo].[UFN_DISCOUNT_AVAILABLEFORORDER]([DISCOUNT].[ID], @ID) &
[dbo].[UFN_DISCOUNT_ELIGIBLEFORORDER]([DISCOUNT].[ID], @ID)
as [ISENABLED],
[DAILYSALEITEM].[BUTTONORDER]
from dbo.[DAILYSALEITEM]
inner join dbo.[DAILYSALEITEMDISCOUNT]
on [DAILYSALEITEM].[ID] = [DAILYSALEITEMDISCOUNT].[ID]
inner join dbo.[DISCOUNT]
on [DAILYSALEITEMDISCOUNT].[DISCOUNTID] = [DISCOUNT].[ID]
left join dbo.[DISCOUNTPRICETYPE]
on [DISCOUNT].[ID] = [DISCOUNTPRICETYPE].[ID]
where
[DAILYSALEITEM].[ISACTIVE] = 1 and
[DISCOUNT].[ISACTIVE] = 1 and
exists ( select [DISCOUNTAVAILABILITY].[ID]
from dbo.[DISCOUNTAVAILABILITY]
inner join dbo.[DISCOUNTAVAILABILITYSALESMETHOD]
on [DISCOUNTAVAILABILITYSALESMETHOD].[DISCOUNTAVAILABILITYID] = [DISCOUNTAVAILABILITY].[ID]
where [DISCOUNTAVAILABILITYSALESMETHOD].[SALESMETHODID] = @SALESMETHODID and
[DISCOUNTAVAILABILITY].[DISCOUNTID] = [DISCOUNT].[ID])
for xml raw ('ITEM'), type, elements, root('DISCOUNTBUTTONS'), BINARY BASE64
)
set @DONATIONBUTTONS = (
select
[DAILYSALEITEM].[ID] as [QUICKBUTTONID],
[DAILYSALEITEM].[TYPECODE] as [TYPECODE],
[DESIGNATION].[ID] as [DESIGNATIONID],
case [DESIGNATION].[VANITYNAME]
when '' then [DESIGNATION].[NAME]
else [DESIGNATION].[VANITYNAME]
end as [DESIGNATIONNAME],
[DAILYSALEITEMDONATION].[DEFAULTAMOUNT],
[DAILYSALEITEM].[DESCRIPTIONFIELD1TYPECODE] as [LINE1TYPECODE],
[DAILYSALEITEM].[DESCRIPTIONFIELD2TYPECODE] as [LINE2TYPECODE],
[DAILYSALEITEM].[DESCRIPTIONFIELD3TYPECODE] as [LINE3TYPECODE],
[DAILYSALEITEM].[DESCRIPTIONFIELD1] as [LINE1DESCRIPTION],
[DAILYSALEITEM].[DESCRIPTIONFIELD2] as [LINE2DESCRIPTION],
[DAILYSALEITEM].[DESCRIPTIONFIELD3] as [LINE3DESCRIPTION],
[DAILYSALEITEM].[BUTTONORDER]
from dbo.[DAILYSALEITEM]
inner join dbo.[DAILYSALEITEMDONATION]
on [DAILYSALEITEM].[ID] = [DAILYSALEITEMDONATION].[ID]
inner join dbo.[DESIGNATION]
on [DAILYSALEITEMDONATION].[DESIGNATIONID] = [DESIGNATION].[ID]
where
[DAILYSALEITEM].[ISACTIVE] = 1 and
[DESIGNATION].[ISACTIVE] = 1
for xml raw ('ITEM'), type, elements, root('DONATIONBUTTONS'), BINARY BASE64
)
set @SCHEDULEDPROGRAMBUTTONS = (
select
[QUICKBUTTONID],
[TYPECODE],
[PROGRAMID],
[PROGRAMNAME],
[PRICETYPECODEID],
[PRICETYPE],
[FACEPRICE],
[EVENTID],
[EVENTTIME],
[EVENTDATE],
[CAPACITY],
[EVENTLOCATION],
[ISONSALE],
[SCHEDULEOPTIONTYPECODE],
[LINE1TYPECODE],
[LINE2TYPECODE],
[LINE3TYPECODE],
[LINE1DESCRIPTION],
[LINE2DESCRIPTION],
[LINE3DESCRIPTION],
[ISPREREGISTERED],
[BUTTONORDER],
row_number() over(partition by BUTTONORDER order by [EVENTDATE], [EVENTTIME]) as BUTTONSUBORDER
from (
select
[DAILYSALEITEM].[ID] as [QUICKBUTTONID],
[DAILYSALEITEM].[TYPECODE] as [TYPECODE],
[PROGRAM].[ID] as [PROGRAMID],
[PROGRAM].[NAME] as [PROGRAMNAME],
[PRICETYPECODE].[ID] as [PRICETYPECODEID],
[PRICETYPECODE].[DESCRIPTION] as [PRICETYPE],
--[EVENTPRICE].[FACEPRICE] as [FACEPRICE],
EVENTAVAILABILITY.FACEPRICE as FACEPRICE,
[EVENT].[ID] as [EVENTID],
[EVENT].[STARTTIME] as [EVENTTIME],
@CURRENTDATE as [EVENTDATE],
[EVENTAVAILABILITY].[CAPACITY] as [CAPACITY],
EVENTAVAILABILITY.[LOCATION] as [EVENTLOCATION],
EVENTAVAILABILITY.[ISONSALE],
[DAILYSALEITEMPROGRAM].[SCHEDULEOPTIONTYPECODE],
[DAILYSALEITEM].[DESCRIPTIONFIELD1TYPECODE] as [LINE1TYPECODE],
[DAILYSALEITEM].[DESCRIPTIONFIELD2TYPECODE] as [LINE2TYPECODE],
[DAILYSALEITEM].[DESCRIPTIONFIELD3TYPECODE] as [LINE3TYPECODE],
[DAILYSALEITEM].[DESCRIPTIONFIELD1] as [LINE1DESCRIPTION],
[DAILYSALEITEM].[DESCRIPTIONFIELD2] as [LINE2DESCRIPTION],
[DAILYSALEITEM].[DESCRIPTIONFIELD3] as [LINE3DESCRIPTION],
[PROGRAM].[ISPREREGISTERED],
[DAILYSALEITEM].[BUTTONORDER]
from dbo.[DAILYSALEITEM] with (nolock)
inner join dbo.[DAILYSALEITEMPROGRAM] with (nolock)
on [DAILYSALEITEM].[ID] = [DAILYSALEITEMPROGRAM].[ID]
inner join dbo.[PROGRAM] with (nolock)
on [DAILYSALEITEMPROGRAM].[PROGRAMID] = [PROGRAM].[ID]
inner join dbo.[PRICETYPECODE] with (nolock)
on [DAILYSALEITEMPROGRAM].[PRICETYPECODEID] = [PRICETYPECODE].[ID]
inner join dbo.UFN_DAILYSALEITEM_EVENT_GETAVAILABILITY(@CURRENTDATETIMEOFFSET, @SALESMETHODID) EVENTAVAILABILITY
on [PROGRAM].ID = EVENTAVAILABILITY.PROGRAMID and
PRICETYPECODE.ID = EVENTAVAILABILITY.PRICETYPECODEID
inner join [EVENT] with (nolock) on
EVENTAVAILABILITY.EVENTID = [EVENT].ID
where
[DAILYSALEITEMPROGRAM].[SCHEDULEOPTIONTYPECODE] in (1, 2) and
[DAILYSALEITEM].[ISACTIVE] = 1 and
[PRICETYPECODE].[ACTIVE] = 1
union all
select
[DAILYSALEITEM].[ID] as [QUICKBUTTONID],
[DAILYSALEITEM].[TYPECODE] as [TYPECODE],
[PROGRAM].[ID] as [PROGRAMID],
[PROGRAM].[NAME] as [PROGRAMNAME],
[PRICETYPECODE].[ID] as [PRICETYPECODEID],
[PRICETYPECODE].[DESCRIPTION] as [PRICETYPE],
[PROGRAMPRICE].[FACEPRICE] as [FACEPRICE],
null as [EVENTID],
null as [EVENTTIME],
null as [EVENTDATE],
0 as [CAPACITY],
'' as [EVENTLOCATION],
1 as [ISONSALE],
[DAILYSALEITEMPROGRAM].[SCHEDULEOPTIONTYPECODE],
[DAILYSALEITEM].[DESCRIPTIONFIELD1TYPECODE] as [LINE1TYPECODE],
[DAILYSALEITEM].[DESCRIPTIONFIELD2TYPECODE] as [LINE2TYPECODE],
[DAILYSALEITEM].[DESCRIPTIONFIELD3TYPECODE] as [LINE3TYPECODE],
[DAILYSALEITEM].[DESCRIPTIONFIELD1] as [LINE1DESCRIPTION],
[DAILYSALEITEM].[DESCRIPTIONFIELD2] as [LINE2DESCRIPTION],
[DAILYSALEITEM].[DESCRIPTIONFIELD3] as [LINE3DESCRIPTION],
[PROGRAM].[ISPREREGISTERED],
[DAILYSALEITEM].[BUTTONORDER]
from dbo.[DAILYSALEITEM]
inner join dbo.[DAILYSALEITEMPROGRAM]
on [DAILYSALEITEM].[ID] = [DAILYSALEITEMPROGRAM].[ID]
inner join dbo.[PROGRAM]
on [DAILYSALEITEMPROGRAM].[PROGRAMID] = [PROGRAM].[ID]
inner join dbo.[PROGRAMPRICE]
on
[PROGRAM].[ID] = [PROGRAMPRICE].[PROGRAMID] and
[DAILYSALEITEMPROGRAM].[PRICETYPECODEID] = [PROGRAMPRICE].[PRICETYPECODEID]
left join dbo.[PRICETYPECODE]
on
[DAILYSALEITEMPROGRAM].[PRICETYPECODEID] = [PRICETYPECODE].[ID] and
--Price type is valid for daily sales
not exists (
select 1
from dbo.[SALESMETHODEXCLUDEDPRICETYPE]
inner join dbo.[SALESMETHOD]
on [SALESMETHODEXCLUDEDPRICETYPE].[SALESMETHODID] = [SALESMETHOD].[ID]
where
[SALESMETHOD].[TYPECODE] = 0 and
[SALESMETHODEXCLUDEDPRICETYPE].[PRICETYPECODEID] = [PRICETYPECODE].[ID]
)
where
[DAILYSALEITEMPROGRAM].[SCHEDULEOPTIONTYPECODE] = 3 and
[DAILYSALEITEM].[ISACTIVE] = 1 and
[PRICETYPECODE].[ACTIVE] = 1
) as [SCHEDULEDPROGRAMS]
for xml raw ('ITEM'), type, elements, root('SCHEDULEDPROGRAMBUTTONS'), BINARY BASE64
)
set @EVENTREGISTRATIONBUTTONS = (
select
[DAILYSALEITEM].[ID] as [QUICKBUTTONID],
[DAILYSALEITEM].[TYPECODE] as [TYPECODE],
[EVENT].[ID] as [EVENTID],
[EVENT].[NAME] as [EVENTNAME],
[EVENT].[CAPACITY] as [CAPACITY],
[EVENT].[STARTTIME] as [EVENTTIME],
[EVENT].[STARTDATE] as [EVENTDATE],
[EVENTPRICE].[NAME] as [EVENTPRICE],
[EVENTPRICE].[ID] as [EVENTPRICEID],
(select top 1 [EVENTLOCATION].[NAME] from dbo.[EVENTLOCATION] where [EVENTLOCATION].[ID] = [EVENT].[EVENTLOCATIONID]) as [EVENTLOCATION],
[DAILYSALEITEM].[DESCRIPTIONFIELD1TYPECODE] as [LINE1TYPECODE],
[DAILYSALEITEM].[DESCRIPTIONFIELD2TYPECODE] as [LINE2TYPECODE],
[DAILYSALEITEM].[DESCRIPTIONFIELD3TYPECODE] as [LINE3TYPECODE],
[DAILYSALEITEM].[DESCRIPTIONFIELD1] as [LINE1DESCRIPTION],
[DAILYSALEITEM].[DESCRIPTIONFIELD2] as [LINE2DESCRIPTION],
[DAILYSALEITEM].[DESCRIPTIONFIELD3] as [LINE3DESCRIPTION],
[DAILYSALEITEM].[BUTTONORDER]
from dbo.[DAILYSALEITEM]
inner join dbo.[DAILYSALEITEMEVENTREGISTRATION]
on [DAILYSALEITEM].[ID] = [DAILYSALEITEMEVENTREGISTRATION].[ID]
inner join dbo.[EVENT]
on [DAILYSALEITEMEVENTREGISTRATION].[EVENTID] = [EVENT].[ID]
inner join dbo.[EVENTPRICE]
on [DAILYSALEITEMEVENTREGISTRATION].[EVENTPRICEID] = [EVENTPRICE].[ID]
where [DAILYSALEITEM].[ISACTIVE] = 1 and [EVENT].[ISACTIVE] = 1
for xml raw ('ITEM'), type, elements, root('EVENTREGISTRATIONBUTTONS'), BINARY BASE64
)
set @COMBINATIONBUTTONS = (
select
[DAILYSALEITEM].[ID] as [QUICKBUTTONID],
[DAILYSALEITEM].[TYPECODE] as [TYPECODE],
[COMBINATION].[ID] as [COMBINATIONID],
[COMBINATION].[NAME] as [COMBINATIONNAME],
[PRICETYPECODE].[ID] as [PRICETYPECODEID],
[PRICETYPECODE].[DESCRIPTION] as [PRICETYPE],
case when dbo.UFN_COMBINATION_ELIGIBLEFORORDER([COMBINATION].[ID], @CONSTITUENTID) = 1 then 1 else 0 end as [CONSTITUENTELIGIBLE],
(
select sum([PROGRAMGROUPPRICE].[FACEPRICE])
from dbo.[PROGRAMGROUPPRICE]
inner join dbo.[PROGRAMGROUP]
on [PROGRAMGROUPPRICE].[PROGRAMGROUPID] = [PROGRAMGROUP].[ID]
where
[PROGRAMGROUP].[COMBINATIONID] = [COMBINATION].[ID] and
[PROGRAMGROUPPRICE].[COMBINATIONPRICETYPEID] = [COMBINATIONPRICETYPE].[ID]
) as [FACEPRICE],
[DAILYSALEITEM].[DESCRIPTIONFIELD1TYPECODE] as [LINE1TYPECODE],
[DAILYSALEITEM].[DESCRIPTIONFIELD2TYPECODE] as [LINE2TYPECODE],
[DAILYSALEITEM].[DESCRIPTIONFIELD3TYPECODE] as [LINE3TYPECODE],
[DAILYSALEITEM].[DESCRIPTIONFIELD1] as [LINE1DESCRIPTION],
[DAILYSALEITEM].[DESCRIPTIONFIELD2] as [LINE2DESCRIPTION],
[DAILYSALEITEM].[DESCRIPTIONFIELD3] as [LINE3DESCRIPTION],
[DAILYSALEITEM].[BUTTONORDER]
from dbo.[DAILYSALEITEM]
inner join dbo.[DAILYSALEITEMCOMBINATION]
on [DAILYSALEITEM].[ID] = [DAILYSALEITEMCOMBINATION].[ID]
inner join dbo.[COMBINATION]
on [DAILYSALEITEMCOMBINATION].[COMBINATIONID] = [COMBINATION].[ID]
inner join dbo.[COMBINATIONPRICETYPE]
on [COMBINATION].[ID] = [COMBINATIONPRICETYPE].[COMBINATIONID]
inner join dbo.[PRICETYPECODE]
on
[COMBINATIONPRICETYPE].[PRICETYPECODEID] = [PRICETYPECODE].[ID] and
[DAILYSALEITEMCOMBINATION].[PRICETYPECODEID] = [PRICETYPECODE].[ID]
where
[PRICETYPECODE].[ACTIVE] = 1 and
-- Combination level check
[COMBINATION].[ISACTIVE] = 1 and
dbo.UFN_COMBINATION_AVAILABLEFORORDER([COMBINATION].[ID], @SALESMETHODID) = 1 and
[DAILYSALEITEM].[ISACTIVE] = 1
for xml raw ('ITEM'), type, elements, root('COMBINATIONBUTTONS'), BINARY BASE64
)
set @MEMBERSHIPPROMOBUTTONS = (
select
[DAILYSALEITEM].[ID] as [QUICKBUTTONID],
[DAILYSALEITEM].[TYPECODE] as [TYPECODE],
[MEMBERSHIPPROMO].[ID] as [MEMBERSHIPPROMOID],
[MEMBERSHIPPROMO].[NAME] as [MEMBERSHIPPROMONAME],
case [MEMBERSHIPPROMO].[PROMOTIONTYPECODE]
when 0 then [MEMBERSHIPPROMO].[DISCOUNTCALCULATIONTYPE]
when 1 then [MEMBERSHIPPROMO].[EXTENSIONCALCULATIONTYPE]
end as [MEMBERSHIPPROMOCALCULATIONTYPE],
[MEMBERSHIPPROMO].[FORMATTEDVALUE] as [MEMBERSHIPPROMOVALUE],
[DAILYSALEITEM].[DESCRIPTIONFIELD1TYPECODE] as [LINE1TYPECODE],
[DAILYSALEITEM].[DESCRIPTIONFIELD2TYPECODE] as [LINE2TYPECODE],
[DAILYSALEITEM].[DESCRIPTIONFIELD3TYPECODE] as [LINE3TYPECODE],
[DAILYSALEITEM].[DESCRIPTIONFIELD1] as [LINE1DESCRIPTION],
[DAILYSALEITEM].[DESCRIPTIONFIELD2] as [LINE2DESCRIPTION],
[DAILYSALEITEM].[DESCRIPTIONFIELD3] as [LINE3DESCRIPTION],
[DAILYSALEITEM].[BUTTONORDER]
from dbo.[DAILYSALEITEM]
inner join dbo.[DAILYSALEITEMMEMBERSHIPPROMO]
on [DAILYSALEITEM].[ID] = [DAILYSALEITEMMEMBERSHIPPROMO].[ID]
inner join dbo.[MEMBERSHIPPROMO]
on [DAILYSALEITEMMEMBERSHIPPROMO].[MEMBERSHIPPROMOID] = [MEMBERSHIPPROMO].[ID]
where
[DAILYSALEITEM].[ISACTIVE] = 1 and
[MEMBERSHIPPROMO].[ISACTIVE] = 1
for xml raw ('ITEM'), type, elements, root('MEMBERSHIPPROMOBUTTONS'), BINARY BASE64
)
set @GENERICBUTTONS = (
select
[TYPECODE],
[BUTTONORDER]
from dbo.[DAILYSALESGENERICACTION]
for xml raw ('ITEM'), type, elements, root('GENERICBUTTONS'), BINARY BASE64
)
if (exists(select 1 from dbo.[DISCOUNT] where [APPLICATIONTYPECODE] <> 0) or exists(select 1 from dbo.[MEMBERSHIPPROMO] where [ISACTIVE] = 1)) and exists(select 1 from dbo.[DAILYSALESGENERICACTION] where TYPECODE = 3 and ISACTIVE = 1)
set @HASCONFIGUREDDISCOUNTS = 1
else
set @HASCONFIGUREDDISCOUNTS = 0;
if exists(
select 1
from dbo.[PROGRAM]
where
exists (
select 1
from dbo.[EVENT]
where
[EVENT].[PROGRAMID] = [PROGRAM].[ID] and
[EVENT].[STARTDATE] >= @CURRENTDATE
)
) and exists(select 1 from dbo.[DAILYSALESGENERICACTION] where TYPECODE = 1 and ISACTIVE = 1)
set @HASCONFIGUREDSCHEDULEDPROGRAMS = 1
else
set @HASCONFIGUREDSCHEDULEDPROGRAMS = 0
if exists(select 1 from dbo.[DAILYSALESGENERICACTION] where TYPECODE = 5 and ISACTIVE = 1)
set @HASCONFIGUREDEVENTREGISTRATIONS = 1
else
set @HASCONFIGUREDEVENTREGISTRATIONS = 0
if exists(select 1 from dbo.[DAILYSALESGENERICACTION] where TYPECODE = 2 and ISACTIVE = 1)
set @HASCONFIGUREDMEMBERSHIPS = 1
else
set @HASCONFIGUREDMEMBERSHIPS = 0
if exists(select 1 from dbo.[DAILYSALESGENERICACTION] where TYPECODE = 6 and ISACTIVE = 1) and exists(select 1 from dbo.MERCHANDISEPRODUCT where ISACTIVE = 1)
set @HASCONFIGUREDMERCHANDISE = 1
else
set @HASCONFIGUREDMERCHANDISE = 0
if exists(select 1 from dbo.[DAILYSALESGENERICACTION] where TYPECODE = 8 and ISACTIVE = 1) and dbo.UFN_SALESORDER_PATRONHASUPGRADE(@ID) = 1
set @HASMIDTERMUPGRADES = 1
else
set @HASMIDTERMUPGRADES = 0
return 0;