USP_REPORT_APPEALPERFORMANCE
Data retrieval for Appeal Performance report.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @STARTDATE | datetime | IN | |
| @ENDDATE | datetime | IN | |
| @CATEGORYID | uniqueidentifier | IN | |
| @BUSINESSUNITID | uniqueidentifier | IN | |
| @REPORTCODEID | uniqueidentifier | IN | |
| @SITEID | uniqueidentifier | IN | |
| @BYBREAKDOWN | bit | IN | |
| @REPORTUSERID | nvarchar(128) | IN | |
| @APPEALID | uniqueidentifier | IN | |
| @CURRENCYCODE | tinyint | IN | |
| @ALTREPORTUSERID | nvarchar(128) | IN | |
| @IDSETID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.[USP_REPORT_APPEALPERFORMANCE]
(
@STARTDATE datetime = null,
@ENDDATE datetime = null,
@CATEGORYID uniqueidentifier = null,
@BUSINESSUNITID uniqueidentifier = null,
@REPORTCODEID uniqueidentifier = null,
@SITEID uniqueidentifier = null,
@BYBREAKDOWN bit = null,
@REPORTUSERID nvarchar(128) = null,
@APPEALID uniqueidentifier = null,
@CURRENCYCODE tinyint = null,
@ALTREPORTUSERID nvarchar(128) = null,
@IDSETID uniqueidentifier = null
)
as
begin
set nocount on;
set transaction isolation level read uncommitted;
declare @CURRENTAPPUSERID uniqueidentifier;
declare @CURRENCYID uniqueidentifier;
declare @ORGANIZATIONCURRENCYID uniqueidentifier;
declare @CURRENCYDECIMALDIGITS tinyint;
declare @CURRENCYROUNDINGTYPECODE tinyint;
declare @SQL nvarchar(max);
declare @PARAMETERS nvarchar(max);
declare @COMMONFILTERS nvarchar(max) = '';
declare @IDSETDBOBJECTNAME nvarchar(128);
declare @DBOBJECTTYPE tinyint;
declare @TOTALDONORCOUNT integer;
declare @TOTALGOAL money;
declare @TOTALSOLICITEDCOUNT integer;
declare @TOTALGIFTCOUNT integer;
declare @TOTALCASHGIFTCOUNT integer;
declare @TOTALREGULARGIFTCOUNT integer;
set @STARTDATE = dbo.[UFN_DATE_GETEARLIESTTIME](@STARTDATE);
set @ENDDATE = dbo.[UFN_DATE_GETLATESTTIME](@ENDDATE);
set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);
set @CURRENCYCODE = isnull(@CURRENCYCODE, 1);
set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
if @CURRENCYCODE = 1
select
@CURRENCYID = [CURRENCY].[ID],
@CURRENCYDECIMALDIGITS = [CURRENCY].[DECIMALDIGITS],
@CURRENCYROUNDINGTYPECODE = [CURRENCY].[ROUNDINGTYPECODE]
from dbo.[CURRENCY]
where [CURRENCY].[ID] = @ORGANIZATIONCURRENCYID;
else if @CURRENCYCODE = 2
select
@CURRENCYID = [CURRENCY].[ID],
@CURRENCYDECIMALDIGITS = [CURRENCY].[DECIMALDIGITS],
@CURRENCYROUNDINGTYPECODE = [CURRENCY].[ROUNDINGTYPECODE]
from dbo.[CURRENCY]
where [CURRENCY].[ID] = dbo.[UFN_APPUSER_GETBASECURRENCY](@CURRENTAPPUSERID);
else
begin
set @CURRENCYCODE = 0;
select
@CURRENCYID = [CURRENCY].[ID],
@CURRENCYDECIMALDIGITS = [CURRENCY].[DECIMALDIGITS],
@CURRENCYROUNDINGTYPECODE = [CURRENCY].[ROUNDINGTYPECODE]
from dbo.[APPEAL]
inner join dbo.[CURRENCY] on [APPEAL].[BASECURRENCYID] = [CURRENCY].[ID]
where [APPEAL].[ID] = @APPEALID;
end
if @CATEGORYID is not null set @COMMONFILTERS =
'[A].[APPEALCATEGORYCODEID] = @CATEGORYID' + char(13);
if @BUSINESSUNITID is not null set @COMMONFILTERS = @COMMONFILTERS + case when len(@COMMONFILTERS) > 0 then ' and ' else '' end +
'exists (select top 1 1 from dbo.[APPEALBUSINESSUNIT] where [APPEALBUSINESSUNIT].[BUSINESSUNITCODEID] = @BUSINESSUNITID and [APPEALBUSINESSUNIT].[APPEALID] = [A].[ID])' + char(13);
if @REPORTCODEID is not null set @COMMONFILTERS = @COMMONFILTERS + case when len(@COMMONFILTERS) > 0 then ' and ' else '' end +
'[A].[APPEALREPORT1CODEID] = @REPORTCODEID' + char(13);
if @SITEID is not null set @COMMONFILTERS = @COMMONFILTERS + case when len(@COMMONFILTERS) > 0 then ' and ' else '' end +
'[A].[SITEID] = @SITEID' + char(13);
if @APPEALID is not null set @COMMONFILTERS = @COMMONFILTERS + case when len(@COMMONFILTERS) > 0 then ' and ' else '' end +
'[A].[ID] = @APPEALID' + char(13);
if @IDSETID is not null
begin
select
@IDSETDBOBJECTNAME = [DBOBJECTNAME],
@DBOBJECTTYPE = [OBJECTTYPE]
from dbo.[IDSETREGISTER]
where [ID] = @IDSETID;
if left(@IDSETDBOBJECTNAME, 1) <> '[' set @IDSETDBOBJECTNAME = '[' + @IDSETDBOBJECTNAME + ']';
if @DBOBJECTTYPE = 1
set @IDSETDBOBJECTNAME = @IDSETDBOBJECTNAME + '()';
else if @DBOBJECTTYPE = 2
set @IDSETDBOBJECTNAME = @IDSETDBOBJECTNAME + '(''' + convert(nvarchar(36), @IDSETID) + ''')';
set @COMMONFILTERS = @COMMONFILTERS + case when len(@COMMONFILTERS) > 0 then ' and ' else '' end +
'[A].[ID] in (select [ID] from dbo.' + @IDSETDBOBJECTNAME + ') ' + char(13);
end
if object_id('tempdb..#USP_REPORT_APPEALPERFORMANCE') is not null
drop table #USP_REPORT_APPEALPERFORMANCE;
create table #USP_REPORT_APPEALPERFORMANCE (
[APPEALID] nvarchar(100) collate database_default,
[NAME] nvarchar(100) collate database_default,
[GOAL] money,
[TOTALGOAL] money,
[SOLICITEDCOUNT] integer,
[DONORCOUNT] integer,
[GIFTCOUNT] integer,
[TOTALGIFTCOUNT] integer,
[TOTALDONORS] integer,
[DESIGNATIONID] nvarchar(100) collate database_default,
[DESNAME] nvarchar(512) collate database_default,
[DESTOTALRAISED] money,
[DESTOTALRECEIVED] money,
[DESTOTALRECEIVED_REGULAR] money,
[GIFTCOUNT_REGULAR] integer,
[TOTALGIFTCOUNT_REGULAR] integer,
[DESTOTALRECEIVED_CASH] money,
[GIFTCOUNT_CASH] integer,
[TOTALGIFTCOUNT_CASH] money,
[DESGROSSAMOUNT] money,
[CURRENCYID] uniqueidentifier,
[CURRENCYISO] nvarchar(3) collate database_default,
[CURRENCYDECIMALDIGITS] tinyint,
[CURRENCYSYMBOL] nvarchar(5) collate database_default,
[CURRENCYSYMBOLDISPLAYSETTINGCODE] tinyint,
[TOTALSOLICITEDCOUNT] integer,
[APPEALID_GUID] uniqueidentifier,
[DESNAMEFORORDER] nvarchar(512) collate database_default);
create index IX_USP_REPORT_APPEALPERFORMANCE_TOTALS
on #USP_REPORT_APPEALPERFORMANCE ([APPEALID_GUID])
include (
[DONORCOUNT],
[GOAL],
[SOLICITEDCOUNT],
[GIFTCOUNT],
[GIFTCOUNT_CASH],
[GIFTCOUNT_REGULAR]
);
set @SQL =
'insert into #USP_REPORT_APPEALPERFORMANCE (' + char(13) +
' [APPEALID],' + char(13) +
' [NAME],' + char(13) +
' [GOAL],' + char(13) +
' [SOLICITEDCOUNT],' + char(13) +
' [DONORCOUNT],' + char(13) +
' [GIFTCOUNT],' + char(13) +
' [DESIGNATIONID],' + char(13) +
' [DESNAME],' + char(13) +
' [DESTOTALRAISED],' + char(13) +
' [DESTOTALRECEIVED],' + char(13) +
' [DESTOTALRECEIVED_REGULAR],' + char(13) +
' [GIFTCOUNT_REGULAR],' + char(13) +
' [DESTOTALRECEIVED_CASH],' + char(13) +
' [GIFTCOUNT_CASH],' + char(13) +
' [DESGROSSAMOUNT],' + char(13) +
' [CURRENCYID],' + char(13) +
' [CURRENCYISO],' + char(13) +
' [CURRENCYDECIMALDIGITS],' + char(13) +
' [CURRENCYSYMBOL],' + char(13) +
' [CURRENCYSYMBOLDISPLAYSETTINGCODE],' + char(13) +
' [APPEALID_GUID],' + char(13) +
' [DESNAMEFORORDER]' + char(13) +
')' + char(13) +
'select' + char(13) +
' ''http://www.blackbaud.com/APPEALID?APPEALID='' + convert(nvarchar(36), [A].[ID]),' + char(13) +
' [A].[NAME],' + char(13) +
' [A].[GOALINCURRENCY],' + char(13) +
' isnull([SOLICITEDCOUNTS].[SOLICITEDCOUNT], 0),' + char(13) +
' isnull([REVENUECOUNTS].[DONORCOUNT], 0),' + char(13) +
' isnull([REVENUECOUNTS].[GIFTCOUNT], 0),' + char(13) +
' ''http://www.blackbaud.com/DESIGNATIONID?DESIGNATIONID='' + convert(nvarchar(36), isnull([DRI].[DESID], [DPE].[DESID])),' + char(13) +
' isnull([DRI].[DESNAME], [DPE].[DESNAME]),' + char(13) +
' isnull([DRI].[DESTOTALRECEIVED], 0) + isnull([DPE].[DESTOTALPLEDGED], 0) - isnull([DPE].[DESPLEDGESWRITTENOFF], 0),' + char(13) +
' isnull([DRI].[DESTOTALRECEIVED], 0) + isnull([DPE].[DESPLEDGESPAID], 0),' + char(13) +
' isnull([DRI].[DESTOTALRECEIVED_REGULAR], 0),' + char(13) +
' isnull([REVENUECOUNTS].[REGULARGIFTCOUNT], 0),' + char(13) +
' isnull([DRI].[DESTOTALRECEIVED], 0) + (isnull([DPE].[DESTOTALPLEDGED], 0) - (isnull([DPE].[DESPLEDGESPAID], 0) + isnull([DPE].[DESPLEDGESWRITTENOFF], 0))) - isnull([DRI].[DESTOTALRECEIVED_REGULAR], 0),' + char(13) +
' isnull([REVENUECOUNTS].[CASHGIFTCOUNT], 0) as [GIFTCOUNT_CASH],' + char(13) +
' isnull([DRI].[DESGROSSAMOUNT], 0),' + char(13) +
' [CURRENCYPROPERTIES].[ID],' + char(13) +
' [CURRENCYPROPERTIES].[ISO4217],' + char(13) +
' [CURRENCYPROPERTIES].[DECIMALDIGITS],' + char(13) +
' [CURRENCYPROPERTIES].[CURRENCYSYMBOL],' + char(13) +
' [CURRENCYPROPERTIES].[SYMBOLDISPLAYSETTINGCODE],' + char(13) +
' [A].[ID],' + char(13) +
' [DRI].[DESNAME]' + char(13) +
'from' + char(13) +
'(' + char(13) +
' select' + char(13) +
' [PAYMENTSTODESIGNATION].[DESID],' + char(13) +
' [PAYMENTSTODESIGNATION].[DESNAME],' + char(13) +
' [PAYMENTSTODESIGNATION].[DESTOTALRECEIVED],' + char(13) +
' [PAYMENTSTODESIGNATION].[DESTOTALRECEIVED_REGULAR],' + char(13) +
' [PAYMENTSTODESIGNATION].[DESGROSSAMOUNT],' + char(13) +
' [PAYMENTSTODESIGNATION].[APPEALID]' + char(13) +
' from dbo.[UFN_DESIGNATION_RAISEDBYAPPEAL_INCURRENCY_INLINE](@STARTDATE, @ENDDATE, @CURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYCODE,' + char(13) +
' @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) as [PAYMENTSTODESIGNATION]' + char(13);
if len(@COMMONFILTERS) > 0 set @SQL = @SQL +
' inner join dbo.[APPEAL] as [A] on [A].[ID] = [PAYMENTSTODESIGNATION].[APPEALID]' + char(13) +
' where ' + @COMMONFILTERS;
set @SQL = @SQL +
') as [DRI]' + char(13) +
'full join' + char(13) +
'(' + char(13) +
' select' + char(13) +
' [PLEDGESTODESIGNATION].[DESID],' + char(13) +
' [PLEDGESTODESIGNATION].[DESNAME],' + char(13) +
' [PLEDGESTODESIGNATION].[DESTOTALPLEDGED],' + char(13) +
' [PLEDGESTODESIGNATION].[DESPLEDGESPAID],' + char(13) +
' [PLEDGESTODESIGNATION].[DESPLEDGESWRITTENOFF],' + char(13) +
' [PLEDGESTODESIGNATION].[APPEALID]' + char(13) +
' from dbo.[UFN_DESIGNATION_PLEDGEDBYAPPEAL_INCURRENCY_INLINE](@STARTDATE, @ENDDATE, @CURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYCODE,' + char(13) +
' @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) as [PLEDGESTODESIGNATION]' + char(13);
if len(@COMMONFILTERS) > 0 set @SQL = @SQL +
' inner join dbo.[APPEAL] as [A] on [A].[ID] = [PLEDGESTODESIGNATION].[APPEALID]' + char(13) +
' where ' + @COMMONFILTERS;
set @SQL = @SQL +
') as [DPE] on ([DPE].[APPEALID] = [DRI].[APPEALID] and ([DPE].[DESID] = [DRI].[DESID] or ([DPE].[DESID] is null and [DRI].[DESID] is null)))' + char(13) +
'right outer join dbo.[UFN_APPEAL_GETGOALINCURRENCY_BULK2](@CURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE, 1) as [A] ' +
'on [A].[ID] =[DRI].[APPEALID] or [A].[ID] = [DPE].[APPEALID]' + char(13) +
'left outer join dbo.[UFN_APPEAL_REVENUECOUNTS_2](@STARTDATE, @ENDDATE) as [REVENUECOUNTS] on [REVENUECOUNTS].[APPEALID] = [A].[ID]' + char(13) +
'left outer join dbo.[UFN_APPEAL_SOLICITEDCOUNTS](@STARTDATE, @ENDDATE) as [SOLICITEDCOUNTS] on [SOLICITEDCOUNTS].[APPEALID] = [A].[ID]' + char(13) +
'outer apply dbo.[UFN_CURRENCY_GETPROPERTIES](isnull(@CURRENCYID, [A].[BASECURRENCYID])) as [CURRENCYPROPERTIES]' + char(13) +
'where dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [A].[SITEID]) = 1' + char(13);
if len(@COMMONFILTERS) > 0 set @SQL = @SQL +
'and ' + @COMMONFILTERS;
set @SQL = @SQL +
'order by [A].[NAME], [DRI].[DESNAME];';
set @PARAMETERS = '@STARTDATE datetime, @ENDDATE datetime, @CATEGORYID uniqueidentifier, @BUSINESSUNITID uniqueidentifier, @REPORTCODEID uniqueidentifier, ' +
'@SITEID uniqueidentifier, @CURRENCYID uniqueidentifier, @ORGANIZATIONCURRENCYID uniqueidentifier, @CURRENCYCODE tinyint, ' +
'@CURRENCYDECIMALDIGITS tinyint, @CURRENCYROUNDINGTYPECODE tinyint, @CURRENTAPPUSERID uniqueidentifier, @APPEALID uniqueidentifier';
exec sp_executesql @SQL,
@PARAMETERS,
@STARTDATE = @STARTDATE,
@ENDDATE = @ENDDATE,
@CATEGORYID = @CATEGORYID,
@BUSINESSUNITID = @BUSINESSUNITID,
@REPORTCODEID = @REPORTCODEID,
@SITEID = @SITEID,
@CURRENCYID = @CURRENCYID,
@ORGANIZATIONCURRENCYID = @ORGANIZATIONCURRENCYID,
@CURRENCYCODE = @CURRENCYCODE,
@CURRENCYDECIMALDIGITS = @CURRENCYDECIMALDIGITS,
@CURRENCYROUNDINGTYPECODE = @CURRENCYROUNDINGTYPECODE,
@CURRENTAPPUSERID = @CURRENTAPPUSERID,
@APPEALID = @APPEALID;
select
@TOTALDONORCOUNT = sum([DONORCOUNT]),
@TOTALGOAL = sum([GOAL]),
@TOTALSOLICITEDCOUNT = sum([SOLICITEDCOUNT]),
@TOTALGIFTCOUNT = sum([GIFTCOUNT]),
@TOTALCASHGIFTCOUNT = sum([GIFTCOUNT_CASH]),
@TOTALREGULARGIFTCOUNT = sum([GIFTCOUNT_REGULAR])
from (
select distinct
[APPEALID_GUID],
[DONORCOUNT],
[GOAL],
[SOLICITEDCOUNT],
[GIFTCOUNT],
[GIFTCOUNT_CASH],
[GIFTCOUNT_REGULAR]
from #USP_REPORT_APPEALPERFORMANCE
) as [TEMP];
update #USP_REPORT_APPEALPERFORMANCE set
[TOTALGOAL] = @TOTALGOAL,
[TOTALGIFTCOUNT] = @TOTALGIFTCOUNT,
[TOTALDONORS] = @TOTALDONORCOUNT,
[TOTALGIFTCOUNT_REGULAR] = @TOTALREGULARGIFTCOUNT,
[TOTALGIFTCOUNT_CASH] = @TOTALCASHGIFTCOUNT,
[TOTALSOLICITEDCOUNT] = @TOTALSOLICITEDCOUNT;
select
[APPEALID],
[NAME],
[GOAL],
[TOTALGOAL],
[SOLICITEDCOUNT],
[DONORCOUNT],
[GIFTCOUNT],
[TOTALGIFTCOUNT],
[TOTALDONORS],
[DESIGNATIONID],
[DESNAME],
[DESTOTALRAISED],
[DESTOTALRECEIVED],
[DESTOTALRECEIVED_REGULAR],
[GIFTCOUNT_REGULAR],
[TOTALGIFTCOUNT_REGULAR],
[DESTOTALRECEIVED_CASH],
[GIFTCOUNT_CASH],
[TOTALGIFTCOUNT_CASH],
[DESGROSSAMOUNT],
[CURRENCYID],
[CURRENCYISO],
[CURRENCYDECIMALDIGITS],
[CURRENCYSYMBOL],
[CURRENCYSYMBOLDISPLAYSETTINGCODE],
[TOTALSOLICITEDCOUNT]
from #USP_REPORT_APPEALPERFORMANCE
order by [NAME], [DESNAMEFORORDER];
drop table #USP_REPORT_APPEALPERFORMANCE;
end