USP_DATAFORMTEMPLATE_VIEW_RE7GIVINGSUMMARY2

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@FIRSTGIFTAMOUNT	money	INOUT
@FIRSTGIFTDATE	datetime	INOUT
@LATESTGIFTAMOUNT	money	INOUT
@LATESTGIFTDATE	datetime	INOUT
@LARGESTGIFTAMOUNT	money	INOUT
@LARGESTGIFTDATE	datetime	INOUT
@TOTALGIFTSGIVEN	int	INOUT
@TOTALGIFTAMOUNT	money	INOUT
@FIRSTGIFTTYPE	nvarchar(100)	INOUT
@FIRSTGIFTDESIGNATION	nvarchar(100)	INOUT
@LARGESTGIFTTYPE	nvarchar(100)	INOUT
@LARGESTGIFTDESIGNATION	nvarchar(100)	INOUT
@LATESTGIFTTYPE	nvarchar(100)	INOUT
@LATESTGIFTDESIGNATION	nvarchar(100)	INOUT
@LARGESTGIFTCONSTITUENT	nvarchar(100)	INOUT
@FIRSTGIFTCONSTITUENT	nvarchar(100)	INOUT
@LATESTGIFTCONSTITUENT	nvarchar(100)	INOUT
@HASFIRSTGIFT	bit	INOUT
@HASLATESTGIFT	bit	INOUT
@HASLARGESTGIFT	bit	INOUT
Definition

Copy

                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_RE7GIVINGSUMMARY2
                (
                    @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,
                    @HASFIRSTGIFT bit = null output,
                    @HASLATESTGIFT bit = null output,
                    @HASLARGESTGIFT bit = 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
                (GIVINGSUMMARY.LARGESTGIFTDATE is null or
                  ((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
            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
                (GIVINGSUMMARY.FIRSTGIFTDATE is null or
                  ((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
            order by
                case when GIVINGSUMMARY.FIRSTGIFTDATE IS null then 1 else 0 end,
                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
                (GIVINGSUMMARY.LATESTGIFTDATE is null or
                  ((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
            order by
                GIVINGSUMMARY.LATESTGIFTDATE desc,
                GIVINGSUMMARY.LATESTGIFTAMOUNT desc
        end


        set @HASFIRSTGIFT = 0;
        set @HASLATESTGIFT = 0;
        set @HASLARGESTGIFT = 0;

         if Not (@FIRSTGIFTDATE is null and
             @FIRSTGIFTAMOUNT = 0 and
             @FIRSTGIFTTYPE = '' and
             @FIRSTGIFTDESIGNATION = ''
            )
          set @HASFIRSTGIFT = 1;

         if Not (@LATESTGIFTDATE is null and
             @LATESTGIFTAMOUNT = 0 and
             @LATESTGIFTTYPE = '' and
             @LATESTGIFTDESIGNATION = ''
            )
          set @HASLATESTGIFT = 1;

         if Not (@LARGESTGIFTDATE is null and
             @LARGESTGIFTAMOUNT = 0 and
             @LARGESTGIFTTYPE = '' and
             @LARGESTGIFTDESIGNATION = ''
            )
          set @HASLARGESTGIFT = 1;

        return 0;