USP_DATAFORMTEMPLATE_VIEW_REGISTRANTPAGEEXPRESSION_2

The load procedure used by the view dataform template "Registrant Page Expression View Form 2"

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.
@CONSTITUENTNAME	nvarchar(700)	INOUT	Name
@CONSTITUENTID	uniqueidentifier	INOUT	CONSTITUENTID
@ISGUEST	bit	INOUT	ISGUEST
@ISUNKNOWNGUEST	bit	INOUT	ISUNKNOWNGUEST
@GUESTOFREGISTRANTID	uniqueidentifier	INOUT	GUESTOFREGISTRANTID
@GUESTOFREGISTRANTNAME	nvarchar(700)	INOUT	GUESTOFREGISTRANTNAME
@EVENTID	uniqueidentifier	INOUT	EVENTID
@EVENTNAME	nvarchar(100)	INOUT	EVENTNAME
@HASAPPEAL	bit	INOUT	HASAPPEAL
@ATTENDED	bit	INOUT	ATTENDED
@WILLNOTATTEND	bit	INOUT	WILLNOTATTEND
@ATTRIBUTEDEFINED	bit	INOUT	ATTRIBUTEDEFINED
@SMARTFIELDDEFINED	bit	INOUT	SMARTFIELDDEFINED
@BENEFITSWAIVED	bit	INOUT	BENEFITSWAIVED
@HOSTREGISTRANTID	uniqueidentifier	INOUT	HOSTREGISTRANTID
@HOSTCONSTITUENTNAME	nvarchar(700)	INOUT	Host name
@HOSTCONSTITUENTID	uniqueidentifier	INOUT	HOSTCONSTITUENTID
@HOSTATTENDED	bit	INOUT	HOSTATTENDED
@HOSTWILLNOTATTEND	bit	INOUT	HOSTWILLNOTATTEND
@ISCANCELLED	bit	INOUT	ISCANCELLED
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@ISEVENTSPEAKER	bit	INOUT	ISEVENTSPEAKER
@ISFRIENDSASKINGFRIENDS	bit	INOUT	ISFRIENDSASKINGFRIENDS
@ISORGANIZATION	bit	INOUT	ISORGANIZATION
@PARENTGROUPTYPE	nvarchar(20)	INOUT	PARENTGROUPTYPE
@PARENTGROUPID	uniqueidentifier	INOUT	PARENTGROUPID
@HASPARENTGROUP	bit	INOUT	HASPARENTGROUP
@ISHOUSEHOLDMEMBER	bit	INOUT	ISHOUSEHOLDMEMBER
@ISHOUSEHOLDLEADER	bit	INOUT	ISHOUSEHOLDLEADER
@DESIGNATIONSONFEES	bit	INOUT	DESIGNATIONSONFEES
@HASEVENTDESIGNATIONPAYMENT	bit	INOUT	HASEVENTDESIGNATIONPAYMENT
@TOTALDESIGNATIONSMATCHRECEIPTAMOUNT	bit	INOUT	TOTALDESIGNATIONSMATCHRECEIPTAMOUNT
@PARENTGROUPNAME	nvarchar(200)	INOUT	PARENTGROUPNAME
@ISREGISTRATIONBENEFIT	bit	INOUT	ISREGISTRATIONBENEFIT
@ISWAIVER	bit	INOUT	ISWAIVER
@FRIENDSASKINGFRIENDSPAGEHEADER	nvarchar(250)	INOUT	FRIENDSASKINGFRIENDSPAGEHEADER
@ISWALKIN	bit	INOUT	ISWALKIN
@USERMARKEDATTENDANCE	bit	INOUT	USERMARKEDATTENDANCE
@EVENTISPROCESSED	bit	INOUT	EVENTISPROCESSED
@USER_GRANTED_CONSTITPERSONALINFO_EDIT	bit	INOUT
@ISREFUNDABLE	bit	INOUT
@TOTALFEES	money	INOUT
@ISPROMOTEREQUIRED	bit	INOUT
@ISSCHEDULEDEVENT	bit	INOUT
@HASLINKEDOPPORTUNITY	bit	INOUT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_REGISTRANTPAGEEXPRESSION_2
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @CONSTITUENTNAME nvarchar(700) = null output,
    @CONSTITUENTID uniqueidentifier = null output,
    @ISGUEST bit = null output,
    @ISUNKNOWNGUEST bit = null output,
    @GUESTOFREGISTRANTID uniqueidentifier = null output,
    @GUESTOFREGISTRANTNAME nvarchar(700) = null output,
    @EVENTID uniqueidentifier = null output,
    @EVENTNAME nvarchar(100) = null output,
    @HASAPPEAL bit = null output,
    @ATTENDED bit = null output,
    @WILLNOTATTEND bit = null output,
    @ATTRIBUTEDEFINED bit = null output,
    @SMARTFIELDDEFINED bit = null output,
    @BENEFITSWAIVED bit = null output,
    @HOSTREGISTRANTID uniqueidentifier = null output,
    @HOSTCONSTITUENTNAME nvarchar(700) = null output,
    @HOSTCONSTITUENTID uniqueidentifier = null output,
    @HOSTATTENDED bit = null output,
    @HOSTWILLNOTATTEND bit = null output,
    @ISCANCELLED bit = null output,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @ISEVENTSPEAKER bit = null output,
    @ISFRIENDSASKINGFRIENDS bit = null output,
    @ISORGANIZATION bit = null output,
    @PARENTGROUPTYPE nvarchar(20) = null output,
    @PARENTGROUPID uniqueidentifier = null output,
    @HASPARENTGROUP bit = null output,
    @ISHOUSEHOLDMEMBER bit = null output,
    @ISHOUSEHOLDLEADER bit = null output,
    @DESIGNATIONSONFEES bit = null output,
    @HASEVENTDESIGNATIONPAYMENT bit = null output,
    @TOTALDESIGNATIONSMATCHRECEIPTAMOUNT bit = null output,
    @PARENTGROUPNAME nvarchar(200) = null output,
    @ISREGISTRATIONBENEFIT bit = null output,
    @ISWAIVER bit = null output,
    @FRIENDSASKINGFRIENDSPAGEHEADER nvarchar(250) = null output,
    @ISWALKIN bit = null output,
    @USERMARKEDATTENDANCE bit = null output,
    @EVENTISPROCESSED bit = null output,
    @USER_GRANTED_CONSTITPERSONALINFO_EDIT bit = null output,
    @ISREFUNDABLE bit = null output,
    @TOTALFEES money = null output,
    @ISPROMOTEREQUIRED bit = null output,
    @ISSCHEDULEDEVENT bit = null output,
    @HASLINKEDOPPORTUNITY bit = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    select @EVENTID=EVENTID from REGISTRANT where ID=@ID;

    set @ISSCHEDULEDEVENT = 0;

    select
        @ISSCHEDULEDEVENT = 1
    from dbo.REGISTRANT
    inner join dbo.EVENT on EVENT.ID = REGISTRANT.EVENTID
    where
        REGISTRANT.ID = @ID and
        EVENT.PROGRAMID is not null;

    set @HASLINKEDOPPORTUNITY = 0;
    if exists (select top 1 ID from dbo.EVENTREGISTRATIONOPPORTUNITY where ID = @ID)
        set @HASLINKEDOPPORTUNITY = 1;

    select
        @DATALOADED = 1,
        @CONSTITUENTNAME = dbo.UFN_REGISTRANT_GETNAME(REGISTRANT.ID),
        @CONSTITUENTID = REGISTRANT.CONSTITUENTID,
        @ISGUEST = case when REGISTRANT.GUESTOFREGISTRANTID is null then 0 else 1 end,
        @ISUNKNOWNGUEST = case when REGISTRANT.CONSTITUENTID is null then 1 else 0 end,
        @GUESTOFREGISTRANTID = REGISTRANT.GUESTOFREGISTRANTID,
        @GUESTOFREGISTRANTNAME = GUESTOF_NF.NAME,
        @EVENTNAME = EVENT.NAME,
        @HASAPPEAL = case when EVENT.APPEALID is null then 0 else 1 end,
        @ATTENDED = REGISTRANT.ATTENDED,
        @WILLNOTATTEND = REGISTRANT.WILLNOTATTEND,
        @ISCANCELLED = dbo.UFN_REGISTRANT_ISCANCELLED(REGISTRANT.ID),
        @ATTRIBUTEDEFINED = dbo.UFN_ATTRIBUTECATEGORY_DEFINEDFORRECORDTYPE_FORAPPUSER('REGISTRANT',@CURRENTAPPUSERID),
        @SMARTFIELDDEFINED = dbo.UFN_SMARTFIELD_DEFINEDFORRECORDTYPE('REGISTRANT'),
        @BENEFITSWAIVED = REGISTRANT.BENEFITSWAIVED,
        @HOSTREGISTRANTID = 
            case
                when REGISTRANT.GUESTOFREGISTRANTID is null then REGISTRANT.ID
                else REGISTRANT.GUESTOFREGISTRANTID
            end,
        @ISEVENTSPEAKER = cast(isnull((select 1 from dbo.EVENTSPEAKER where EVENTSPEAKER.CONSTITUENTID = REGISTRANT.CONSTITUENTID and EVENTSPEAKER.EVENTID = REGISTRANT.EVENTID),0) as bit),
        @ISFRIENDSASKINGFRIENDS = dbo.UFN_IS_FRIENDS_ASKING_FRIENDS_EVENT(REGISTRANT.EVENTID),
        @ISORGANIZATION = CONSTITUENT.ISORGANIZATION,
        @PARENTGROUPTYPE = LOWER(case when TEAMEXTENSION.[TYPE] is not null then TEAMEXTENSION.[TYPE] else '' end),
        @PARENTGROUPID = TEAMEXTENSION.TEAMFUNDRAISINGTEAMID,
        @HASPARENTGROUP = case when TEAMEXTENSION.TEAMFUNDRAISINGTEAMID is not null then 1 else 0 end,
        @ISHOUSEHOLDMEMBER = case when er.RoleCode=4 then 1 else 0 end,
        @ISHOUSEHOLDLEADER = case when er.RoleCode=3 then 1 else 0 end,
        @DESIGNATIONSONFEES = EVENT.DESIGNATIONSONFEES,
        @TOTALDESIGNATIONSMATCHRECEIPTAMOUNT = 
            case when (EVENT.DESIGNATIONSONFEES = 1) and 
                (select sum(RECEIPTAMOUNT) from dbo.REGISTRANTREGISTRATION where REGISTRANTID = @ID) <> (select sum(AMOUNT) from dbo.REGISTRANTDESIGNATION where REGISTRANTID = @ID)
                then 0
                else 1
            end,
        @PARENTGROUPNAME = TEAMFUNDRAISINGTEAM.NAME,
        @ISREGISTRATIONBENEFIT = case when (select count(*) from dbo.REGISTRANTBENEFITEXTENSION RBE where RBE.REGISTRANTID = REGISTRANT.ID and RBE.BENEFITTYPECODE = 0) > 0 then 1 else 0 end,
        @ISWAIVER =
        case when REGISTRANTEXTENSION.WAIVERID is not null   then 1   else 0  end,
            ---cast(isnull((select 1 from dbo.REGISTRANTBENEFITEXTENSION RBE where RBE.REGISTRANTID = REGISTRANT.ID and RBE.BENEFITTYPECODE = 0),0) as bit)

        --

        @ISWALKIN = REGISTRANT.ISWALKIN,
        @USERMARKEDATTENDANCE = REGISTRANT.USERMARKEDATTENDANCE,
        @EVENTISPROCESSED = EVENT.ISPROCESSED,
        @ISREFUNDABLE = 
            case when coalesce((select sum(AMOUNT) from dbo.EVENTREGISTRANTPAYMENT where REGISTRANTID = @ID), 0) > coalesce((select sum(CREDITITEM.TOTAL) from dbo.CREDITITEMEVENTREGISTRATION inner join dbo.CREDITITEM on CREDITITEMEVENTREGISTRATION.ID = CREDITITEM.ID where CREDITITEMEVENTREGISTRATION.REGISTRANTID = @ID), 0)
                then 1
                else 0
            end,
        @TOTALFEES = coalesce((select sum(EVENTREGISTRANTPAYMENT.AMOUNT) from dbo.EVENTREGISTRANTPAYMENT where EVENTREGISTRANTPAYMENT.REGISTRANTID = @ID),0)
    from dbo.REGISTRANT
    left join dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
    left join dbo.CONSTITUENT on REGISTRANT.CONSTITUENTID = CONSTITUENT.ID
    left join dbo.UFN_REGISTRANT_GETFAFROLE(@EVENTID, null) er on er.REGISTRANTID=REGISTRANT.ID
    left join dbo.TEAMFUNDRAISINGTEAM on TEAMFUNDRAISINGTEAM.ID=er.TEAMFUNDRAISINGTEAMID
    left join dbo.TEAMEXTENSION on TEAMEXTENSION.TEAMFUNDRAISINGTEAMID = TEAMFUNDRAISINGTEAM.ID
    left join dbo.REGISTRANT as GUESTOF on GUESTOF.ID = REGISTRANT.GUESTOFREGISTRANTID
    left join REGISTRANTEXTENSION on REGISTRANTEXTENSION.REGISTRANTID = REGISTRANT.ID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(GUESTOF.CONSTITUENTID) GUESTOF_NF
    left join dbo.EVENTREGISTRANTPAYMENT on REGISTRANT.ID = EVENTREGISTRANTPAYMENT.REGISTRANTID
    where 
        REGISTRANT.ID = @ID;

    ;With GRPCNT as 
    (
    select
        count(TFTM2.TEAMFUNDRAISINGTEAMID) as members,
        TFTC.ID as CAPTAINID,
        X.CAPTAINCNT   
    from dbo.REGISTRANT R (nolock)
    join dbo.TEAMFUNDRAISER TF (nolock) on R.CONSTITUENTID = TF.CONSTITUENTID
    join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM (nolock) on TF.ID = TFTM.TEAMFUNDRAISERID
    join dbo.TEAMEXTENSION TX (nolock) on TX.TEAMFUNDRAISINGTEAMID = TFTM.TEAMFUNDRAISINGTEAMID and TX.EVENTID = R.EVENTID
    left join dbo.TEAMFUNDRAISINGTEAMCAPTAIN TFTC (nolock) on TFTC.CONSTITUENTID = R.CONSTITUENTID and TFTC.TEAMFUNDRAISINGTEAMID = TX.TEAMFUNDRAISINGTEAMID
    left join (select COUNT(ID) CAPTAINCNT, TFTC2.TEAMFUNDRAISINGTEAMID 
                            from dbo.TEAMFUNDRAISINGTEAMCAPTAIN TFTC2 (nolock)
                            where TFTC2.TEAMFUNDRAISINGTEAMID = @PARENTGROUPID 
                            group by TFTC2.TEAMFUNDRAISINGTEAMID) X on X.TEAMFUNDRAISINGTEAMID = TX.TEAMFUNDRAISINGTEAMID
    left join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM2 (nolock) on TFTM2.TEAMFUNDRAISINGTEAMID = TFTM.TEAMFUNDRAISINGTEAMID
    where R.ID = @ID
    group by TFTC.ID, X.CAPTAINCNT                       
    )        
    select @ISPROMOTEREQUIRED = case when CAPTAINID is not null and CAPTAINCNT < 2 and members > 1 then 1 else 0 end from GRPCNT 

    if @ISFRIENDSASKINGFRIENDS = 1
    begin
        if @PARENTGROUPNAME is null
            select @FRIENDSASKINGFRIENDSPAGEHEADER= R.Role from dbo.UFN_REGISTRANT_GETFAFROLE(@EVENTID, NULL) R where R.REGISTRANTID = @ID
        else
            select @FRIENDSASKINGFRIENDSPAGEHEADER= R.Role + ': ' + @PARENTGROUPNAME from dbo.UFN_REGISTRANT_GETFAFROLE(@EVENTID, NULL) R where R.REGISTRANTID = @ID
        end

        if @DATALOADED = 1
        begin
            --Host fields

            select
                @HOSTCONSTITUENTNAME = NF.NAME,
                @HOSTCONSTITUENTID = REGISTRANT.CONSTITUENTID,
                @HOSTATTENDED = REGISTRANT.ATTENDED,
                @HOSTWILLNOTATTEND = REGISTRANT.WILLNOTATTEND
            from
                dbo.REGISTRANT
                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REGISTRANT.CONSTITUENTID) NF
            where 
                REGISTRANT.ID = @HOSTREGISTRANTID;

            set @HASEVENTDESIGNATIONPAYMENT = 0;
            if exists(
                select 1 
                from dbo.EVENTREGISTRANTPAYMENT
                    inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = EVENTREGISTRANTPAYMENT.PAYMENTID
                where 
                    EVENTREGISTRANTPAYMENT.REGISTRANTID = isnull(@GUESTOFREGISTRANTID,@ID) 
                    and REVENUESPLIT.APPLICATIONCODE = 1 
                    and REVENUESPLIT.TYPECODE = 0)
                set @HASEVENTDESIGNATIONPAYMENT = 1;

                if dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 
                begin
                    set @USER_GRANTED_CONSTITPERSONALINFO_EDIT = 1;
                end
            else 
            begin
                set @USER_GRANTED_CONSTITPERSONALINFO_EDIT = 
                case 
                when @ISORGANIZATION = 1 then dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'a9954902-ea62-48ae-8e6f-4e2ed3c3f4f9')
                else dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'788AB947-26ED-40C4-865E-8FE29577E593')
            end;
        end
    end

    return 0;