USP_DATAFORMTEMPLATE_VIEW_RE7GIVINGSUMMARY

The load procedure used by the view dataform template "RE7 Integration Giving Summary 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.
@FIRSTGIFTAMOUNT	money	INOUT	First gift amount
@FIRSTGIFTDATE	datetime	INOUT	First gift date
@LATESTGIFTAMOUNT	money	INOUT	Latest gift amount
@LATESTGIFTDATE	datetime	INOUT	Latest gift date
@LARGESTGIFTAMOUNT	money	INOUT	Largest gift amount
@LARGESTGIFTDATE	datetime	INOUT	Largest gift date
@TOTALGIFTSGIVEN	int	INOUT	Total number of gifts
@TOTALGIFTAMOUNT	money	INOUT	Total giving
@FIRSTGIFTTYPE	nvarchar(100)	INOUT	First gift type
@FIRSTGIFTDESIGNATION	nvarchar(100)	INOUT	First gift designation
@LARGESTGIFTTYPE	nvarchar(100)	INOUT	Largest gift type
@LARGESTGIFTDESIGNATION	nvarchar(100)	INOUT	Largest gift designation
@LATESTGIFTTYPE	nvarchar(100)	INOUT	Latest gift type
@LATESTGIFTDESIGNATION	nvarchar(100)	INOUT	Latest gift designation
@LARGESTGIFTCONSTITUENT	nvarchar(100)	INOUT	Largest gift constituent
@FIRSTGIFTCONSTITUENT	nvarchar(100)	INOUT	First gift constituent
@LATESTGIFTCONSTITUENT	nvarchar(100)	INOUT	Latest gift constituent

Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_RE7GIVINGSUMMARY
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @FIRSTGIFTAMOUNT money = null output,
                    @FIRSTGIFTDATE datetime = null output,
                    @LATESTGIFTAMOUNT money = null output,
                    @LATESTGIFTDATE datetime = null output,
                    @LARGESTGIFTAMOUNT money = null output,
                    @LARGESTGIFTDATE datetime = null output,
                    @TOTALGIFTSGIVEN int = null output,
                    @TOTALGIFTAMOUNT money = null output,
                    @FIRSTGIFTTYPE nvarchar(100) = null output,
                    @FIRSTGIFTDESIGNATION nvarchar(100) = null output,
                    @LARGESTGIFTTYPE nvarchar(100) = null output,
                    @LARGESTGIFTDESIGNATION nvarchar(100) = null output,
                    @LATESTGIFTTYPE nvarchar(100) = null output,
                    @LATESTGIFTDESIGNATION nvarchar(100) = null output,
          @LARGESTGIFTCONSTITUENT nvarchar(100) = null output,
          @FIRSTGIFTCONSTITUENT nvarchar(100) = null output,
          @LATESTGIFTCONSTITUENT nvarchar(100) = null output
                ) as
                    set nocount on;

                    set @DATALOADED = 1;


          declare @ISGROUP bit;
          select
            @ISGROUP = ISGROUP
          from 
            dbo.CONSTITUENT
          where 
            ID = @ID


          if @ISGROUP = 0
          begin
                      select
                          @TOTALGIFTSGIVEN =          GIVINGSUMMARY.TOTALGIFTSGIVEN,
                          @TOTALGIFTAMOUNT =          GIVINGSUMMARY.TOTALGIFTAMOUNT,    

                          @LARGESTGIFTTYPE =          GIVINGSUMMARY.LARGESTGIFTTYPE,
                          @LARGESTGIFTDESIGNATION = GIVINGSUMMARY.LARGESTGIFTDESIGNATION,
              @LARGESTGIFTAMOUNT=          GIVINGSUMMARY.LARGESTGIFTAMOUNT,
                          @LARGESTGIFTDATE =          GIVINGSUMMARY.LARGESTGIFTDATE,

              @FIRSTGIFTAMOUNT =          GIVINGSUMMARY.FIRSTGIFTAMOUNT,
                          @FIRSTGIFTDATE =            GIVINGSUMMARY.FIRSTGIFTDATE,
                          @FIRSTGIFTTYPE =            GIVINGSUMMARY.FIRSTGIFTTYPE,
                          @FIRSTGIFTDESIGNATION =      GIVINGSUMMARY.FIRSTGIFTDESIGNATION,

              @LATESTGIFTAMOUNT =          GIVINGSUMMARY.LATESTGIFTAMOUNT,
                          @LATESTGIFTDATE =            GIVINGSUMMARY.LATESTGIFTDATE,
                          @LATESTGIFTTYPE =            GIVINGSUMMARY.LATESTGIFTTYPE,
                          @LATESTGIFTDESIGNATION =  GIVINGSUMMARY.LATESTGIFTDESIGNATION

                      from
                          dbo.RE7INTEGRATIONGIVINGSUMMARY GIVINGSUMMARY
                      where
                          GIVINGSUMMARY.ID = @ID

                  end
          else
          begin
             -- Select Totals Data
             select
                  @TOTALGIFTSGIVEN = sum(GIVINGSUMMARY.TOTALGIFTSGIVEN),
                  @TOTALGIFTAMOUNT = sum(GIVINGSUMMARY.TOTALGIFTAMOUNT)
             from
                dbo.CONSTITUENT
            left join
                dbo.GROUPMEMBER on CONSTITUENT.ID = GROUPMEMBER.GROUPID
            left join
                dbo.RE7INTEGRATIONGIVINGSUMMARY GIVINGSUMMARY on GROUPMEMBER.MEMBERID = GIVINGSUMMARY.ID
            where
                CONSTITUENT.ID = @ID;


            -- Select Largest Gift Data
            select top(1)
                @LARGESTGIFTTYPE = GIVINGSUMMARY.LARGESTGIFTTYPE,
                @LARGESTGIFTDESIGNATION = GIVINGSUMMARY.LARGESTGIFTDESIGNATION,
                @LARGESTGIFTAMOUNT = GIVINGSUMMARY.LARGESTGIFTAMOUNT,
                @LARGESTGIFTDATE = GIVINGSUMMARY.LARGESTGIFTDATE,
                @LARGESTGIFTCONSTITUENT = GROUPCONSTITUENT.NAME
             from
                dbo.CONSTITUENT
            left join
                dbo.GROUPMEMBER on CONSTITUENT.ID = GROUPMEMBER.GROUPID
            left join
                dbo.CONSTITUENT GROUPCONSTITUENT on GROUPMEMBER.MEMBERID = GROUPCONSTITUENT.ID
            left join
                dbo.RE7INTEGRATIONGIVINGSUMMARY GIVINGSUMMARY on GROUPMEMBER.MEMBERID = GIVINGSUMMARY.ID
            inner join
                dbo.GROUPMEMBERDATERANGE on
                GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID and
                (GROUPMEMBERDATERANGE.DATEFROM is null or GROUPMEMBERDATERANGE.DATEFROM <= GIVINGSUMMARY.LARGESTGIFTDATE) and
                (GROUPMEMBERDATERANGE.DATETO is null or GROUPMEMBERDATERANGE.DATETO >= GIVINGSUMMARY.LARGESTGIFTDATE)
            where
                CONSTITUENT.ID = @ID and
                GIVINGSUMMARY.ID is not null and
                GIVINGSUMMARY.LARGESTGIFTAMOUNT > 0 and
                GIVINGSUMMARY.LARGESTGIFTDATE is not null
            order by
                GIVINGSUMMARY.LARGESTGIFTAMOUNT desc,
                GIVINGSUMMARY.LARGESTGIFTDATE desc;

             -- Select First Gift Data
             select top(1)
                @FIRSTGIFTAMOUNT = GIVINGSUMMARY.FIRSTGIFTAMOUNT,
                @FIRSTGIFTDATE = GIVINGSUMMARY.FIRSTGIFTDATE,
                @FIRSTGIFTTYPE = GIVINGSUMMARY.FIRSTGIFTTYPE,
                @FIRSTGIFTDESIGNATION = GIVINGSUMMARY.FIRSTGIFTDESIGNATION,
                @FIRSTGIFTCONSTITUENT = GROUPCONSTITUENT.NAME
             from
                dbo.CONSTITUENT
            left join
                dbo.GROUPMEMBER on CONSTITUENT.ID = GROUPMEMBER.GROUPID
            left join
                dbo.CONSTITUENT GROUPCONSTITUENT on GROUPMEMBER.MEMBERID = GROUPCONSTITUENT.ID
            left join
                dbo.RE7INTEGRATIONGIVINGSUMMARY GIVINGSUMMARY on GROUPMEMBER.MEMBERID = GIVINGSUMMARY.ID
            inner join
                dbo.GROUPMEMBERDATERANGE on
                GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID and
                (GROUPMEMBERDATERANGE.DATEFROM is null or GROUPMEMBERDATERANGE.DATEFROM <= GIVINGSUMMARY.FIRSTGIFTDATE) and
                (GROUPMEMBERDATERANGE.DATETO is null or GROUPMEMBERDATERANGE.DATETO >= GIVINGSUMMARY.FIRSTGIFTDATE)
            where
                CONSTITUENT.ID = @ID and
                GIVINGSUMMARY.ID is not null and
                GIVINGSUMMARY.FIRSTGIFTAMOUNT > 0 and
                GIVINGSUMMARY.FIRSTGIFTDATE is not null
            order by
                GIVINGSUMMARY.FIRSTGIFTDATE asc,
                GIVINGSUMMARY.FIRSTGIFTAMOUNT desc;

            -- Select Latest Gift Data
            select top(1)
                @LATESTGIFTAMOUNT = GIVINGSUMMARY.LATESTGIFTAMOUNT,
                @LATESTGIFTDATE = GIVINGSUMMARY.LATESTGIFTDATE,
                @LATESTGIFTTYPE = GIVINGSUMMARY.LATESTGIFTTYPE,
                @LATESTGIFTDESIGNATION = GIVINGSUMMARY.LATESTGIFTDESIGNATION,
                @LATESTGIFTCONSTITUENT = GROUPCONSTITUENT.NAME
            from
                dbo.CONSTITUENT
            left join
                dbo.GROUPMEMBER on CONSTITUENT.ID = GROUPMEMBER.GROUPID
            left join
                dbo.CONSTITUENT GROUPCONSTITUENT on GROUPMEMBER.MEMBERID = GROUPCONSTITUENT.ID
            left join
                dbo.RE7INTEGRATIONGIVINGSUMMARY GIVINGSUMMARY on GROUPMEMBER.MEMBERID = GIVINGSUMMARY.ID
            inner join
                dbo.GROUPMEMBERDATERANGE on
                GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID and
                (GROUPMEMBERDATERANGE.DATEFROM is null or GROUPMEMBERDATERANGE.DATEFROM <= GIVINGSUMMARY.LATESTGIFTDATE) and
                (GROUPMEMBERDATERANGE.DATETO is null or GROUPMEMBERDATERANGE.DATETO >= GIVINGSUMMARY.LATESTGIFTDATE)
            where
                CONSTITUENT.ID = @ID and
                GIVINGSUMMARY.ID is not null and
                GIVINGSUMMARY.LATESTGIFTAMOUNT > 0 and
                GIVINGSUMMARY.LATESTGIFTDATE is not null
            order by
                GIVINGSUMMARY.LATESTGIFTDATE desc,
                GIVINGSUMMARY.LATESTGIFTAMOUNT desc
        end

        return 0;