USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPPROGRAM_DATA
The load procedure used by the view dataform template "Web Forms Membership Program Data View Data Form"
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. |
| @NAME | nvarchar(100) | INOUT | Membership program name |
| @DESCRIPTION | nvarchar(255) | INOUT | Membership program description |
| @ALLOWMULTIPLEMEMBERSHIPS | bit | INOUT | Program allows multiple memberships |
| @ISACTIVE | bit | INOUT | Membership program is active |
| @LEVELS | xml | INOUT | Membership program levels |
| @PROGRAMTYPECODE | tinyint | INOUT | |
| @PROGRAMBASEDONCODE | tinyint | INOUT | |
| @ADDONS | xml | INOUT |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPPROGRAM_DATA (
@ID uniqueidentifier,
@DATALOADED bit = 0 output,
@NAME nvarchar(100) = null output,
@DESCRIPTION nvarchar(255) = null output,
@ALLOWMULTIPLEMEMBERSHIPS bit = null output,
@ISACTIVE bit = null output,
@LEVELS xml = null output,
@PROGRAMTYPECODE tinyint = null output,
@PROGRAMBASEDONCODE tinyint = null output,
@ADDONS xml = null output
)
as
set nocount on;
set @DATALOADED = 0;
select
@DATALOADED = 1,
@NAME = [MEMBERSHIPPROGRAM].[NAME],
@DESCRIPTION = [MEMBERSHIPPROGRAM].[DESCRIPTION],
@ALLOWMULTIPLEMEMBERSHIPS = [MEMBERSHIPPROGRAM].[ALLOWMULTIPLEMEMBERSHIPS],
@ISACTIVE = [MEMBERSHIPPROGRAM].[ISACTIVE],
@PROGRAMTYPECODE = [MEMBERSHIPPROGRAM].[PROGRAMTYPECODE],
@PROGRAMBASEDONCODE = [MEMBERSHIPPROGRAM].[PROGRAMBASEDONCODE]
from dbo.[MEMBERSHIPPROGRAM] with (nolock)
where [ID] = @ID;
if @DATALOADED = 1
begin
set @LEVELS = (
select
[MEMBERSHIPLEVEL].[ID],
[MEMBERSHIPLEVEL].[NAME],
[MEMBERSHIPLEVEL].[DESCRIPTION],
[MEMBERSHIPLEVEL].[MEMBERSALLOWED],
[MEMBERSHIPLEVEL].[CHILDRENALLOWED],
[MEMBERSHIPLEVEL].[CARDSALLOWED],
[MEMBERSHIPLEVELTERM].[TERMS],
[MEMBERSHIPLEVELTYPE].[TYPES],
[MEMBERSHIPLEVEL].[ISACTIVE],
case when [MICROSITEMEMBERSHIPLEVEL].[ID] is not null then 1 else 0 end as [ISONLINE],
isnull([MEMBERSHIPLEVEL_MICROSITEEMAILTEMPLATE].[EMAILTEMPLATEID], [MEMBERSHIPPROGRAM_MICROSITEEMAILTEMPLATE].[EMAILTEMPLATEID]) as ACKNOWLEDGEMENTEMAILTEMPLATEID
from dbo.[MEMBERSHIPLEVEL] with (nolock)
left join dbo.[MICROSITEMEMBERSHIPLEVEL] with (nolock)
on [MEMBERSHIPLEVEL].[ID] = [MICROSITEMEMBERSHIPLEVEL].[ID]
left join dbo.[MEMBERSHIPLEVEL_MICROSITEEMAILTEMPLATE] on
([MEMBERSHIPLEVEL].[ID] = [MEMBERSHIPLEVEL_MICROSITEEMAILTEMPLATE].[MEMBERSHIPLEVELID]) and ([MEMBERSHIPLEVEL_MICROSITEEMAILTEMPLATE].[ACTIVE] = 1)
left join dbo.[MEMBERSHIPPROGRAM_MICROSITEEMAILTEMPLATE] on
([MEMBERSHIPLEVEL].[MEMBERSHIPPROGRAMID] = [MEMBERSHIPPROGRAM_MICROSITEEMAILTEMPLATE].[MEMBERSHIPPROGRAMID]) and ([MEMBERSHIPPROGRAM_MICROSITEEMAILTEMPLATE].[ACTIVE] = 1)
cross apply ( --Don't show levels without terms
select (
select
[MEMBERSHIPLEVELTERM].[ID],
cast([MEMBERSHIPLEVELTERM].[TERMTIMELENGTH] as nvarchar(5)) + ' ' +
case [MEMBERSHIPLEVELTERM].[TERMLENGTHCODE]
when 0 then--months
case [MEMBERSHIPLEVELTERM].[TERMTIMELENGTH] when 1 then 'month' else 'months' end
when 1 then--years
case [MEMBERSHIPLEVELTERM].[TERMTIMELENGTH] when 1 then 'year' else 'years' end
end as [TERM],
[MEMBERSHIPLEVELTERM].[AMOUNT]
from dbo.[MEMBERSHIPLEVELTERM] with (nolock)
where [LEVELID] = [MEMBERSHIPLEVEL].[ID]
and [MEMBERSHIPLEVELTERM].[ISACTIVE] = 1 --excluding inactive level-term combinations, rather than adding ISACTIVE to the result and filtering in the calling code
order by [MEMBERSHIPLEVELTERM].[SEQUENCE] asc
for xml raw('ITEM'),type,elements,binary base64
) as [TERMS]
) as [MEMBERSHIPLEVELTERM]
outer apply ( --Levels don't need to have types
select (
select
[MEMBERSHIPLEVELTYPE].[ID],
[MEMBERSHIPLEVELTYPECODE].[ID] as [TYPECODEID],
[MEMBERSHIPLEVELTYPECODE].[DESCRIPTION]
from dbo.[MEMBERSHIPLEVELTYPE]
inner join dbo.[MEMBERSHIPLEVELTYPECODE] with (nolock)
on [MEMBERSHIPLEVELTYPE].[LEVELTYPECODEID] = [MEMBERSHIPLEVELTYPECODE].[ID]
where
[MEMBERSHIPLEVELTYPE].[LEVELID] = [MEMBERSHIPLEVEL].[ID] and
[MEMBERSHIPLEVELTYPECODE].[ACTIVE] = 1
order by [MEMBERSHIPLEVELTYPE].[SEQUENCE] asc
for xml raw('ITEM'),type,elements,binary base64
) as [TYPES]
) as [MEMBERSHIPLEVELTYPE]
where
[MEMBERSHIPLEVEL].[MEMBERSHIPPROGRAMID] = @ID and
[MEMBERSHIPLEVEL].[ISACTIVE] = 1
order by [MEMBERSHIPLEVEL].[SEQUENCE] asc
for xml raw('ITEM'),type,elements,root('LEVELS'),binary base64
)
set @ADDONS = (
select
a.ID,
a.NAME,
mpa.[DESCRIPTION],
mpa.PRICE,
mpa.MULTIPLEALLOWED,
a.ADDONTYPECODE as TYPECODE,
a.ADDONTYPE as TYPE
--mpa.TRANSACTIONPRICE,
--mpa.ORGANIZATIONPRICE,
--mpa.BASECURRENCYID,
--mpa.TRANSACTIONCURRENCYID,
--mpa.BASEEXCHANGERATEID,
--mpa.ORGANIZATIONEXCHANGERATEID
from [dbo].[MEMBERSHIPPROGRAMADDON] as mpa
inner join [dbo].[ADDON] as a
on mpa.ADDONID = a.ID
where [MEMBERSHIPPROGRAMID] = @ID
for xml raw('ITEM'),type,elements,root('ADDONS'),binary base64
)
end --if @DATALOADED = 1