USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTGROUP_GIVINGHISTORY

The load procedure used by the view dataform template "Constituent Group Giving History 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.
@GROUPINCLUDESMEMBERGIVING	bit	INOUT	Group includes member giving
@TOTALNUMBERFROMGROUP	int	INOUT	Total number of gifts from group
@TOTALNUMBERFROMGROUPMEMBERS	int	INOUT	Total number of gifts from members
@TOTALGROUPAMOUNT	money	INOUT	Total giving from group
@TOTALGROUPMEMBERAMOUNT	money	INOUT	Total giving from members
@ISHOUSEHOLD	bit	INOUT	Is household
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@TOTALGROUPMEMBERAMOUNTGIFTAID	money	INOUT	Total giving from members including Gift Aid
Definition

Copy


            CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTGROUP_GIVINGHISTORY (
                @ID uniqueidentifier,
                @DATALOADED bit = 0 output,
                @GROUPINCLUDESMEMBERGIVING bit = null output,
                @TOTALNUMBERFROMGROUP int = null output,
                @TOTALNUMBERFROMGROUPMEMBERS int = null output,
                @TOTALGROUPAMOUNT money = null output,
                @TOTALGROUPMEMBERAMOUNT money = null output,
                @ISHOUSEHOLD bit = null output,
                @CURRENTAPPUSERID uniqueidentifier = null,
                @TOTALGROUPMEMBERAMOUNTGIFTAID money = null output
            ) as
            set nocount on;

            set @DATALOADED = 0;

            declare @CURRENTDATE datetime;
            set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

            declare @SECURITYFEATUREID uniqueidentifier;
            declare @SECURITYFEATURETYPE tinyint;
            set @SECURITYFEATUREID = '2253c9fe-3489-49f4-b174-3752090b8ec6';
            set @SECURITYFEATURETYPE = 1;

            -- households always include member giving, other types get looked up

            select 
                @GROUPINCLUDESMEMBERGIVING = 
                    case
                        when GD.GROUPTYPECODE = 0 then 1
                        when GD.GROUPTYPECODE = 1 then GT.INCLUDEMEMBERGIVING
                    end,
                @ISHOUSEHOLD = 
                    case
                        when GD.GROUPTYPECODE = 0 then 1
                        when GD.GROUPTYPECODE = 1 then 0
                    end,
                @DATALOADED = 1
            from dbo.GROUPDATA GD
                left join dbo.GROUPTYPE GT on GD.GROUPTYPEID = GT.ID
            where GD.ID=@ID;

            -- calculate the member giving

            if @GROUPINCLUDESMEMBERGIVING = 1
            begin
                select
                    @TOTALNUMBERFROMGROUPMEMBERS = count(R.ID),
                    @TOTALGROUPMEMBERAMOUNT = cast(sum(cast(RS.BASEAMOUNT as decimal(20,5)) - cast(coalesce(WO.AMOUNT, 0) as decimal(20,5))) as money),
                    @TOTALGROUPMEMBERAMOUNTGIFTAID = coalesce(cast(sum(cast(case R.TYPECODE when 0 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT(RS.ID, 1)
                                                                              when 1 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNT(RS.ID)
                                                                              else RS.BASEAMOUNT end as decimal(20,5)) - cast(coalesce(WO.AMOUNT, 0) as decimal(20,5))) as money), 0)
                from
                    dbo.FINANCIALTRANSACTION R
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM RS on RS.FINANCIALTRANSACTIONID = R.ID
                    inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = RS.ID
                    left join
                    (   
                        select
                            WRITEOFF.REVENUEID,
                            sum(coalesce(INSTALLMENTWRITEOFF.AMOUNT,0)) AMOUNT
                        from
                            dbo.WRITEOFF
                            left join dbo.INSTALLMENTWRITEOFF on INSTALLMENTWRITEOFF.WRITEOFFID = WRITEOFF.ID
                        group by
                            WRITEOFF.REVENUEID
                    ) WO on WO.REVENUEID = R.ID
                    left join
                    (
                        select
                            INSTALLMENTPAYMENT.PLEDGEID,
                            sum(coalesce(INSTALLMENTPAYMENT.AMOUNT,0)) AMOUNT    
                        from
                            dbo.INSTALLMENTPAYMENT
                        group by
                            INSTALLMENTPAYMENT.PLEDGEID
                    ) PAYMENT on PAYMENT.PLEDGEID = R.ID
                    inner join dbo.GROUPMEMBER GM on R.CONSTITUENTID = GM.MEMBERID
                    left join dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
                    left join dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PGR on PGR.REVENUEID = R.ID
                    left join dbo.PLANNEDGIFT PG on PG.ID = PGR.PLANNEDGIFTID
                where GM.GROUPID = @ID 
                    and R.DELETEDON is null
                    and RS.TYPECODE <> 1
                    and RS.DELETEDON is null
                    and (R.TYPECODE = 1 
                            or (R.TYPECODE = 0 and RSE.APPLICATIONCODE in (0, 3))
                            or (R.TYPECODE = 4 and PG.VEHICLECODE in (0,1,2,5,6,7,8,9,10,11,12,13))
                         )
                    and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= R.DATE))
                        or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= R.DATE)) 
                        or (GMDR.DATEFROM <= R.DATE and GMDR.DATETO >= R.DATE))
                    and exists
                        (
                            select top 1 REVSITES.SITEID from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RS.ID) REVSITES
                            where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 
                                or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
                        )
            end

            -- calculate the giving by the group itself

            select
                @TOTALNUMBERFROMGROUP = count(R.ID),
                @TOTALGROUPAMOUNT = cast(sum(cast(RS.BASEAMOUNT as decimal(20,5)) - cast(coalesce(WO.AMOUNT, 0) as decimal(20,5))) as money)
            from
                dbo.FINANCIALTRANSACTION R
                inner join dbo.FINANCIALTRANSACTIONLINEITEM RS on RS.FINANCIALTRANSACTIONID = R.ID
                inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = RS.ID
                left join
                    (select
                        WRITEOFF.REVENUEID,
                        sum(coalesce(INSTALLMENTWRITEOFF.AMOUNT,0)) AMOUNT
                    from
                        dbo.WRITEOFF
                    left join
                        dbo.INSTALLMENTWRITEOFF
                    on
                        INSTALLMENTWRITEOFF.WRITEOFFID = WRITEOFF.ID
                    group by
                        WRITEOFF.REVENUEID) WO
                on
                    WO.REVENUEID = R.ID
                left join
                    (select
                        INSTALLMENTPAYMENT.PLEDGEID,
                        sum(coalesce(INSTALLMENTPAYMENT.AMOUNT,0)) AMOUNT    
                    from
                        dbo.INSTALLMENTPAYMENT
                    group by
                        INSTALLMENTPAYMENT.PLEDGEID) PAYMENT
                on
                    PAYMENT.PLEDGEID = R.ID
                left join dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PGR on PGR.REVENUEID = R.ID
                left join dbo.PLANNEDGIFT PG on PG.ID = PGR.PLANNEDGIFTID
            where (R.TYPECODE = 1 
                    or (R.TYPECODE = 0 and RSE.APPLICATIONCODE in (0, 3))
                    or (R.TYPECODE = 4 and PG.VEHICLECODE in (0,1,2,5,6,7,8,9,10,11,12,13))
                   )
                and R.CONSTITUENTID = @ID
                and exists
                    (
                        select top 1 REVSITES.SITEID from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RS.ID) REVSITES
                        where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 
                            or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
                    );

            select @TOTALGROUPMEMBERAMOUNT = coalesce(@TOTALGROUPMEMBERAMOUNT, 0);
            select @TOTALGROUPAMOUNT = coalesce(@TOTALGROUPAMOUNT, 0);
            select @TOTALNUMBERFROMGROUP = coalesce(@TOTALNUMBERFROMGROUP, 0);
            select @TOTALNUMBERFROMGROUPMEMBERS = coalesce(@TOTALNUMBERFROMGROUPMEMBERS, 0);

            return 0;