USP_DATAFORMTEMPLATE_VIEW_SPONSOR

The load procedure used by the view dataform template "Sponsor View 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(700)	INOUT	Name
@ADDRESS	nvarchar(300)	INOUT	ADDRESS
@PHONENUMBER	nvarchar(100)	INOUT	PHONENUMBER
@PHONETYPE	nvarchar(100)	INOUT	Phone type
@EMAILADDRESS	UDT_EMAILADDRESS	INOUT	EMAILADDRESS
@WEBADDRESS	UDT_WEBADDRESS	INOUT	Web
@STATUS	nvarchar(20)	INOUT	Sponsor status
@ISINACTIVE	bit	INOUT	ISINACTIVE
@PICTURE	varbinary	INOUT	Image
@PICTURETHUMBNAIL	varbinary	INOUT	Image thumbnail
@SPONSORSINCE	datetime	INOUT	Sponsor since
@LASTSPONSORSHIP	nvarchar(100)	INOUT	Last sponsorship
@SPONSORSHIPCOUNT	int	INOUT	Active sponsorships
@SPONSORSHIPTOTALAMOUNT	money	INOUT	Sponsor paid to date
@ISSPONSORPROFILE	bit	INOUT	ISSPONSORPROFILE
@LOOKUPID	nvarchar(100)	INOUT	Lookup ID
@CONSTITUENTID	uniqueidentifier	INOUT	CONSTITUENTID
@ISPROSPECT	bit	INOUT	ISPROSPECT
@ISFORMERPROSPECT	bit	INOUT	ISFORMERPROSPECT
@ISFUNDRAISER	bit	INOUT	ISFUNDRAISER
@ISFORMERFUNDRAISER	bit	INOUT	ISFORMERFUNDRAISER
@ISVOLUNTEER	bit	INOUT	ISVOLUNTEER
@ISFORMERVOLUNTEER	bit	INOUT	ISFORMERVOLUNTEER
@ISVENDOR	bit	INOUT	ISVENDOR
@ISALUMNUS	bit	INOUT	ISALUMNUS
@ISSTUDENT	bit	INOUT	ISSTUDENT
@ISFORMERSTUDENT	bit	INOUT	ISFORMERSTUDENT
@ISGROUPMEMBER	bit	INOUT	ISGROUPMEMBER
@ISPLANNEDGIVER	bit	INOUT	ISPLANNEDGIVER
@ISGROUP	bit	INOUT	ISGROUP
@GROUPTYPE	nvarchar(300)	INOUT	Group type
@GROUPMEMBERCOUNT	int	INOUT	No. of members
@DONOTTERMINATE	bit	INOUT	DONOTTERMINATE
@PASTDUETOTAL	money	INOUT	Total past due
@SHOWPASTDUE	bit	INOUT	SHOWPASTDUE
@SPONSORTYPECODE	tinyint	INOUT	SPONSORTYPECODE
@ISCOMMITTEE	bit	INOUT	ISCOMMITTEE
@ISCOMMITTEEMEMBER	bit	INOUT	ISCOMMITTEEMEMBER
@UNIQUEOPPORTUNITIESFORGIFTDONOR	bit	INOUT	UNIQUEOPPORTUNITIESFORGIFTDONOR
@ISREGISTRANT	bit	INOUT	ISREGISTRANT
@ISFACULTY	bit	INOUT	ISFACULTY
@ISFORMERSPONSOR	bit	INOUT	ISFORMERSPONSOR
@HASSTEWARDSHIPPLAN	bit	INOUT	HASSTEWARDSHIPPLAN
@BASECURRENCYID	uniqueidentifier	INOUT	BASECURRENCYID
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@CONSTITUENTINACTIVITYREASON	nvarchar(63)	INOUT	Inactive reason
@DONOTMAIL	bit	INOUT	DONOTMAIL
@DONOTEMAIL	bit	INOUT	DONOTEMAIL
@DONOTPHONE	bit	INOUT	DONOTPHONE
@PHONEISCONFIDENTIAL	bit	INOUT	PHONEISCONFIDENTIAL
@ADDRESSISCONFIDENTIAL	bit	INOUT	ADDRESSISCONFIDENTIAL
@ADDRESSID	uniqueidentifier	INOUT	ADDRESSID
@PHONENUMBERID	uniqueidentifier	INOUT	PHONENUMBERID
@EMAILADDRESSID	uniqueidentifier	INOUT	EMAILADDRESSID
@ISHOUSEHOLD	bit	INOUT	ISHOUSEHOLD
@ISORG	bit	INOUT	ISORG
@HASRECEIVEDFUNDS	bit	INOUT	HASRECEIVEDFUNDS
@FINANCIALCOMMITMENTCOUNT	int	INOUT	Active financial commitments
@SINGLESPONSORSHIP	bit	INOUT	SINGLESPONSORSHIP
@USER_GRANTED_CONSTITPERSONALINFO_EDIT	bit	INOUT
@SOCIALMEDIAACCOUNTS	xml	INOUT

Definition

Copy


    CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_SPONSOR (
        @ID uniqueidentifier, --Back to constituent id
        @DATALOADED bit = 0 output,
        @NAME nvarchar(700) = null output,
        @ADDRESS nvarchar(300) = null output,
        @PHONENUMBER nvarchar(100) = null output,
        @PHONETYPE nvarchar(100) = null output,
        @EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
        @WEBADDRESS dbo.UDT_WEBADDRESS = null output,
        @STATUS nvarchar(20) = null output,
        @ISINACTIVE bit = null output,
        @PICTURE varbinary(max) = null output,
        @PICTURETHUMBNAIL varbinary(max) = null output,
        @SPONSORSINCE datetime = null output,
        @LASTSPONSORSHIP nvarchar(100) = null output,
        @SPONSORSHIPCOUNT int = null output,
        @SPONSORSHIPTOTALAMOUNT money = null output,
        @ISSPONSORPROFILE bit = null output,
        @LOOKUPID nvarchar(100) = null output,
        @CONSTITUENTID uniqueidentifier = null output,
        @ISPROSPECT bit = null output,
        @ISFORMERPROSPECT bit = null output,
        @ISFUNDRAISER bit = null output,
        @ISFORMERFUNDRAISER bit = null output,
        @ISVOLUNTEER bit = null output,
        @ISFORMERVOLUNTEER bit = null output,
        @ISVENDOR bit = null output,
        @ISALUMNUS bit = null output,
        @ISSTUDENT bit = null output,
        @ISFORMERSTUDENT bit = null output,
        @ISGROUPMEMBER bit = null output,
        @ISPLANNEDGIVER bit = null output,
        @ISGROUP bit = null output,
        @GROUPTYPE nvarchar(300) = null output,
        @GROUPMEMBERCOUNT integer = null output,
        @DONOTTERMINATE bit = null output,
        @PASTDUETOTAL money = null output,
        @SHOWPASTDUE bit = null output,
        @SPONSORTYPECODE tinyint = null output,
        @ISCOMMITTEE bit = null output,
        @ISCOMMITTEEMEMBER bit = null output,
        @UNIQUEOPPORTUNITIESFORGIFTDONOR bit = null output,
        @ISREGISTRANT bit = null output,
        @ISFACULTY bit = null output,
        @ISFORMERSPONSOR bit = null output,
        @HASSTEWARDSHIPPLAN bit = null output,
        @BASECURRENCYID uniqueidentifier = null output,
        @CURRENTAPPUSERID uniqueidentifier = null,
        @CONSTITUENTINACTIVITYREASON nvarchar(63) = null output,
        @DONOTMAIL bit = null output,
        @DONOTEMAIL bit = null output,
        @DONOTPHONE bit = null output,
        @PHONEISCONFIDENTIAL bit = null output,
        @ADDRESSISCONFIDENTIAL bit = null output,
        @ADDRESSID uniqueidentifier = null output,
        @PHONENUMBERID uniqueidentifier = null output,
        @EMAILADDRESSID uniqueidentifier = null output,
        @ISHOUSEHOLD bit = null output,
        @ISORG bit = null output,
        @HASRECEIVEDFUNDS bit = null output,
        @FINANCIALCOMMITMENTCOUNT int = null output,
        @SINGLESPONSORSHIP bit = null output,
        @USER_GRANTED_CONSTITPERSONALINFO_EDIT bit = null output,
        @SOCIALMEDIAACCOUNTS xml = null output
        )
    as
    set nocount on;
    -- be sure to set this, in case the select returns no rows
    set @DATALOADED = 0;

    declare @CURRENTDATE datetime;

    set @CURRENTDATE = getdate();
    set @ISSPONSORPROFILE = 1;

    declare @CURRENTDATEEARLIESTTIME date;

    set @CURRENTDATEEARLIESTTIME = @CURRENTDATE;

    declare @CONSTITUENTISSPONSOR bit = 0;
    declare @CONSTITUENTISFORMERSPONSOR bit = 0;

    exec dbo.USP_SPONSOR_GETINFORMATION @CONSTITUENTID = @ID,
        @ISSPONSOR = @CONSTITUENTISSPONSOR output,
        @ISFORMERSPONSOR = @CONSTITUENTISFORMERSPONSOR output;

    set @BASECURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID)

    select @DATALOADED = 1,
        @CONSTITUENTID = C.ID,
        @NAME = NF.name,
        @ADDRESS = dbo.UFN_BUILDFULLADDRESS(A.ID, A.ADDRESSBLOCK, A.CITY, A.STATEID, A.POSTCODE, A.COUNTRYID),
        @DONOTMAIL = A.DONOTMAIL,
        @ADDRESSISCONFIDENTIAL = A.ISCONFIDENTIAL,
        @ADDRESSID = A.ID,
        @PHONENUMBER = PH.NUMBER,
     @PHONETYPE = dbo.UFN_PHONETYPECODE_GETDESCRIPTION(PH.PHONETYPECODEID),
        @DONOTPHONE = PH.DONOTCALL,
        @PHONEISCONFIDENTIAL = PH.ISCONFIDENTIAL,
        @PHONENUMBERID = PH.ID,
        @EMAILADDRESS = E.EMAILADDRESS,
        @DONOTEMAIL = E.DONOTEMAIL,
        @EMAILADDRESSID = E.ID,
        @WEBADDRESS = C.WEBADDRESS,
        @PICTURE = C.PICTURETHUMBNAIL,
        @ISINACTIVE = C.ISINACTIVE,
        @HASSTEWARDSHIPPLAN = dbo.UFN_CONSTITUENT_HASSTEWARDSHIPPLAN(@ID),
        @LOOKUPID = C.LOOKUPID
    from dbo.CONSTITUENT C
    left outer join dbo.ADDRESS A
        on A.CONSTITUENTID = C.ID
            and A.ISPRIMARY = 1
    left outer join dbo.PHONE PH
        on PH.CONSTITUENTID = C.ID
            and PH.ISPRIMARY = 1
    left outer join dbo.EMAILADDRESS E
        on E.CONSTITUENTID = C.ID
            and E.ISPRIMARY = 1
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF
    where C.ID = @ID
        and (
            @CONSTITUENTISSPONSOR = 1
            or @CONSTITUENTISFORMERSPONSOR = 1
            );

    --SOCIAL MEDIA ACCOUNTS
    select @SOCIALMEDIAACCOUNTS = dbo.UFN_CONSTITUENT_GETSOCIALMEDIAACCOUNTS_TOITEMLISTXML(@CONSTITUENTID);

    if @ISINACTIVE = 1
        select @CONSTITUENTINACTIVITYREASON = dbo.UFN_CONSTITUENTINACTIVITYREASONCODE_TRANSLATE(CONSTITUENTINACTIVEDETAIL.CONSTITUENTINACTIVITYREASONCODEID)
        from dbo.CONSTITUENTINACTIVEDETAIL
        where ID = @ID

    --SPONSOR SINCE
    select @SPONSORSINCE = min(DATEFROM)
    from dbo.SPONSORDATERANGE
    where CONSTITUENTID = @ID

    --LAST SPONSORSHIP, SPONSORTYPECODE
    select top 1 @LASTSPONSORSHIP = case 
            when nullif(max(DATETO), CURRENT_TIMESTAMP) >= CURRENT_TIMESTAMP
                then 'Current'
            else max(DATETO)
            end,
        @SPONSORTYPECODE = case 
            when min(SPONSORTYPECODE) = max(SPONSORTYPECODE)
                then min(SPONSORTYPECODE)
            else 0
            end
    from dbo.SPONSORDATERANGE
    where CONSTITUENTID = @ID
    group by case 
            when DATEFROM <= @CURRENTDATEEARLIESTTIME
                then 0
            else 1
            end,
        case 
            when (
                    DATETO is null
                    or DATETO > @CURRENTDATEEARLIESTTIME
                    )
                then 0
            else 1
            end
    order by case 
            when DATEFROM <= @CURRENTDATEEARLIESTTIME
                then 0
            else 1
            end,
        case 
            when (
                    DATETO is null
                    or DATETO > @CURRENTDATEEARLIESTTIME
                    )
                then 0
            else 1
            end

    --SPONSORSHIP COUNT
    set @SPONSORSHIPCOUNT = dbo.UFN_SPONSORSHIP_GETACTIVESPONSORSHIPSFORSPONSOR(@ID)
    --Financial Commitment Count
    set @FINANCIALCOMMITMENTCOUNT = dbo.UFN_SPONSORSHIP_GETACTIVEFINANCIALCOMMITMENTFORSPONSOR(@ID)

    --Status
    if @SPONSORSHIPCOUNT > 0
        set @STATUS = 'Active'
    else
        set @STATUS = 'Inactive'

    --TOTAL AMOUNT OF ALL SPONSORSHIP payments
    select @SPONSORSHIPTOTALAMOUNT = isnull(sum(case REVENUESPLIT.TRANSACTIONCURRENCYID
                    when @BASECURRENCYID
                        then REVENUESPLIT.TRANSACTIONAMOUNT
                    else dbo.UFN_CURRENCY_CONVERT(REVENUESPLIT.TRANSACTIONAMOUNT, dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(REVENUESPLIT.TRANSACTIONCURRENCYID, @BASECURRENCYID, REVENUE.date, 1, null))
                    end), 0)
    from dbo.REVENUESPLIT
    inner join dbo.REVENUE
        on REVENUESPLIT.REVENUEID = REVENUE.ID
    where REVENUESPLIT.TYPECODE = 9
        and REVENUE.TRANSACTIONTYPECODE = 0
        and REVENUE.CONSTITUENTID = @ID

    --TOTAL AMOUNT DUE
    create table #SPONSORSHIPREVENUE (
        ID uniqueidentifier,
        [DATE] datetime,
        TRANSACTIONCURRENCYID uniqueidentifier
        )

    insert into #SPONSORSHIPREVENUE (
        ID,
        [DATE],
        TRANSACTIONCURRENCYID
        )
    select REVENUE.ID,
        REVENUE.date,
        REVENUESPLIT.TRANSACTIONCURRENCYID
    from dbo.REVENUE
    inner join dbo.REVENUESPLIT
        on REVENUESPLIT.REVENUEID = REVENUE.ID
    inner join dbo.SPONSORSHIP
        on SPONSORSHIP.REVENUESPLITID = REVENUESPLIT.id
    where REVENUE.CONSTITUENTID = @ID
        and SPONSORSHIP.STATUSCODE = 1;

    select @PASTDUETOTAL = isnull(sum(case r.TRANSACTIONCURRENCYID
                    when @BASECURRENCYID
                        then PASTDUEAMOUNT.AMOUNT
                    else dbo.UFN_CURRENCY_CONVERT(PASTDUEAMOUNT.AMOUNT, dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(R.TRANSACTIONCURRENCYID, @BASECURRENCYID, R.date, 1, null))
                    end), 0)
    from #SPONSORSHIPREVENUE r
    inner join dbo.UFN_RECURRINGGIFT_GETPASTDUEAMOUNT_BULK_2(getdate(), 0) PASTDUEAMOUNT
        on r.ID = PASTDUEAMOUNT.ID;

    --CONSTITUENCIES
    select @ISPROSPECT = dbo.UFN_CONSTITUENT_ISPROSPECT(@ID),
        @ISFORMERPROSPECT = dbo.UFN_CONSTITUENT_ISFORMERPROSPECT(@ID),
        @ISFUNDRAISER = dbo.UFN_CONSTITUENT_ISFUNDRAISER(@ID),
        @ISFORMERFUNDRAISER = dbo.UFN_CONSTITUENT_ISFORMERFUNDRAISER(@ID),
        @ISVOLUNTEER = dbo.UFN_CONSTITUENT_ISVOLUNTEER(@ID),
        @ISFORMERVOLUNTEER = dbo.UFN_CONSTITUENT_ISFORMERVOLUNTEER(@ID),
        @ISVENDOR = dbo.UFN_CONSTITUENT_ISVENDOR(@ID),
        @ISALUMNUS = dbo.UFN_CONSTITUENT_ISALUMNUS(@ID),
        @ISSTUDENT = dbo.UFN_CONSTITUENT_ISSTUDENT(@ID),
        @ISFORMERSTUDENT = dbo.UFN_CONSTITUENT_ISFORMERSTUDENT(@ID),
        @ISGROUPMEMBER = dbo.UFN_CONSTITUENT_HASGROUPMEMBERRECORD(@ID),
        @ISPLANNEDGIVER = dbo.UFN_CONSTITUENT_ISPLANNEDGIVER(@ID),
        @ISGROUP = C.ISGROUP,
        @ISORG = C.ISORGANIZATION,
        @ISCOMMITTEE = dbo.UFN_CONSTITUENT_ISCOMMITTEE(@ID),
        @ISCOMMITTEEMEMBER = dbo.UFN_CONSTITUENT_ISCOMMITTEEMEMBER(@ID),
        @ISREGISTRANT = dbo.UFN_CONSTITUENT_ISREGISTRANT(@ID),
        @ISFACULTY = dbo.UFN_CONSTITUENT_ISFACULTY(@ID),
        @ISFORMERSPONSOR = dbo.UFN_CONSTITUENT_ISFORMERSPONSOR(@ID)
    from dbo.CONSTITUENT as C
    left join dbo.DECEASEDCONSTITUENT
        on DECEASEDCONSTITUENT.ID = C.ID
    left join dbo.DISSOLVEDGROUP
        on DISSOLVEDGROUP.ID = C.ID
    where C.ID = @ID;

    --MEMBER COUNT
    select @GROUPMEMBERCOUNT = count(GM.ID)
    from dbo.GROUPMEMBER GM
    left join dbo.GROUPMEMBERDATERANGE as GMDR
        on GM.ID = GMDR.GROUPMEMBERID
    where GM.GROUPID = @ID
        and (
            (
                GMDR.DATEFROM is null
                and (
                    GMDR.DATETO is null
                    or GMDR.DATETO > @CURRENTDATE
                    )
                )
            or (
                GMDR.DATETO is null
                and (
                    GMDR.DATEFROM is null
                    or GMDR.DATEFROM <= @CURRENTDATE
                    )
                )
            or (
                GMDR.DATEFROM <= @CURRENTDATE
                and GMDR.DATETO > @CURRENTDATE
                )
            )

    --CONSTIT GROUP FIELDS
    -- group fields
    select @GROUPTYPE = coalesce((
                select GT.name
                from dbo.GROUPTYPE GT
                where GT.ID = G.GROUPTYPEID
                ), 'Household'),
        @ISHOUSEHOLD = case 
            when G.GROUPTYPECODE = 0
                then 1
            else 0
            end
    from dbo.GROUPDATA G
    where G.ID = @ID;

    -- sponsor termination code
    select @DONOTTERMINATE = DONOTTERMINATE,
        @UNIQUEOPPORTUNITIESFORGIFTDONOR = UNIQUEOPPORTUNITIESFORGIFTDONOR
    from dbo.SPONSOR S
    where S.ID = @ID;

    -- Show past due?
    --Always showing past due now that the payment handling rules replace the RECURRINGGIFTSETTING
    set @SHOWPASTDUE = 1
    set @HASRECEIVEDFUNDS = dbo.UFN_CONSTITUENT_HASRECEIVEDFUNDS(@ID)

    declare @RECORDSCOUNT int;

    select @RECORDSCOUNT = count(ID)
    from dbo.SPONSORSHIPCOMMITMENT
 where CONSTITUENTID = @ID;

    set @SINGLESPONSORSHIP = 0;

    if @RECORDSCOUNT = 1
        set @SINGLESPONSORSHIP = 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 @ISORG = 1
                    then dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, 'a9954902-ea62-48ae-8e6f-4e2ed3c3f4f9')
                when @ISHOUSEHOLD = 1
                    then dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, '2f419cfd-9e70-406c-8277-ba2c4b7bdfba')
                when @ISGROUP = 1
                    then dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, '292ee330-a63c-4cc5-98c2-a1168a1b7150')
                else dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, '788AB947-26ED-40C4-865E-8FE29577E593')
                end;
    end

    return 0;