USP_DATAFORMTEMPLATE_VIEW_PATRON_MEMBERSHIP

The load procedure used by the view dataform template "Patron Membership 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.
@MEMBERSHIPPROGRAMNAME	nvarchar(100)	INOUT	MEMBERSHIPPROGRAMNAME
@MEMBERSHIPLEVELNAME	nvarchar(100)	INOUT	MEMBERSHIPLEVELNAME
@MEMBERSHIPEXPIRATION	datetime	INOUT	MEMBERSHIPEXPIRATION
@INRENEWALWINDOW	bit	INOUT	INRENEWALWINDOW
@ISLAPSED	bit	INOUT	ISLAPSED
@ISCANCELLED	bit	INOUT	ISCANCELLED
@ISPENDING	bit	INOUT	ISPENDING
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@ORDERID	uniqueidentifier	INOUT	ORDERID
@MEMBERSHIPID	uniqueidentifier	INOUT	MEMBERSHIPID

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_PATRON_MEMBERSHIP
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @MEMBERSHIPPROGRAMNAME nvarchar(100) = null output,
    @MEMBERSHIPLEVELNAME nvarchar(100) = null output,
    @MEMBERSHIPEXPIRATION datetime = null output,
    @INRENEWALWINDOW bit = null output,
    @ISLAPSED bit = null output,
    @ISCANCELLED bit = null output,
    @ISPENDING bit = null output,
    @CURRENTAPPUSERID uniqueidentifier,
    @ORDERID uniqueidentifier = null output,
    @MEMBERSHIPID uniqueidentifier = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    declare @CURRENTDATE datetime = getdate();

    select 
        @DATALOADED = 1,
        @MEMBERSHIPPROGRAMNAME = dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MEMBERSHIP.MEMBERSHIPPROGRAMID),
        @MEMBERSHIPEXPIRATION = case when MEMBERSHIP.STATUSCODE = 1 then MT.TRANSACTIONDATE  else MEMBERSHIP.EXPIRATIONDATE end,
        @MEMBERSHIPLEVELNAME = dbo.UFN_MEMBERSHIPLEVEL_GETNAME(MEMBERSHIP.MEMBERSHIPLEVELID),
        @INRENEWALWINDOW = case when @CURRENTDATE between dateadd(month, MEMBERSHIPLEVEL.BEFOREEXPIRATION * -1, MEMBERSHIP.EXPIRATIONDATE) and
                                dateadd(month, MEMBERSHIPLEVEL.AFTEREXPIRATION, MEMBERSHIP.EXPIRATIONDATE) then 1
                                else 0 end,
        @ISLAPSED = case when @CURRENTDATE > MEMBERSHIP.EXPIRATIONDATE then 1
                else 0 end,
        @ISCANCELLED = case when MEMBERSHIP.STATUSCODE = 1 then 1
                else 0 end,
        @ISPENDING = case MEMBERSHIP.STATUSCODE
            when 2 then
                1
            else
                0
        end
    from dbo.MEMBER
        inner join dbo.MEMBERSHIP on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
        inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
        left join dbo.MEMBERSHIPTRANSACTION MT on (MEMBERSHIP.ID = MT.MEMBERSHIPID and MT.ACTIONCODE = 4) and (MT.ID in (select top 1 ID from dbo.MEMBERSHIPTRANSACTION MT2 where MT2.ACTIONCODE = 4 and MT2.MEMBERSHIPID = MEMBERSHIP.ID order by MT2.TRANSACTIONDATE desc))
    where MEMBERSHIP.ID = @ID

    select 
        @ORDERID = ID
    from dbo.SALESORDER
    where STATUSCODE = 0
    and SALESMETHODTYPECODE = 1
    and APPUSERID = @CURRENTAPPUSERID

    set @MEMBERSHIPID = @ID

    return 0;