USP_REPORT_DESIGNATIONPROGRESS
Returns the data necessary for the Campaign Progress report.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @DESIGNATIONLEVELID | uniqueidentifier | IN | |
| @GOALID | uniqueidentifier | IN | |
| @CURRENCYCODE | tinyint | IN |
Definition
Copy
CREATE procedure dbo.USP_REPORT_DESIGNATIONPROGRESS
(
@DESIGNATIONLEVELID uniqueidentifier = null,
@GOALID uniqueidentifier = null,
@CURRENCYCODE tinyint = 0
)
as begin
declare @STARTDATE datetime;
declare @ENDDATE datetime;
select @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(DLG.STARTDATE),
@ENDDATE = dbo.UFN_DATE_GETLATESTTIME(DLG.ENDDATE)
from dbo.DESIGNATIONLEVELGOAL DLG
where DLG.ID = @GOALID;
-- If the user has selected "Base" then use the existing logic to return the values
if @CURRENCYCODE = 0
begin
select
'http://www.blackbaud.com/DESIGNATIONID?DESIGNATIONID=' + CONVERT(nvarchar(36),D.ID) as [DESIGNATIONID],
DL.NAME LEVEL1NAME,
DL2.NAME LEVEL2NAME,
DL3.NAME LEVEL3NAME,
DL4.NAME LEVEL4NAME,
DL5.NAME LEVEL5NAME,
DL.ID DESIGNATIONLEVEL1ID,
D.DESIGNATIONLEVEL2ID DESIGNATIONLEVEL2ID,
D.DESIGNATIONLEVEL3ID DESIGNATIONLEVEL3ID,
D.DESIGNATIONLEVEL4ID DESIGNATIONLEVEL4ID,
D.DESIGNATIONLEVEL5ID DESIGNATIONLEVEL5ID,
DLT.DESCRIPTION DESIGNATIONLEVELTYPE,
coalesce(DL5.DESIGNATIONLEVELCATEGORYCODEID,DL4.DESIGNATIONLEVELCATEGORYCODEID,DL3.DESIGNATIONLEVELCATEGORYCODEID,DL2.DESIGNATIONLEVELCATEGORYCODEID) LASTCATEGORYCODEID,
coalesce(DL5.DESIGNATIONLEVELTYPEID,DL4.DESIGNATIONLEVELTYPEID,DL3.DESIGNATIONLEVELTYPEID,DL2.DESIGNATIONLEVELTYPEID) LASTTYPEID,
LASTLEVELTYPE.DESCRIPTION LASTTYPE,
DR1C.DESCRIPTION REPORTCODE1,
DR2C.DESCRIPTION REPORTCODE2,
case when D.DESIGNATIONLEVEL2ID is null then coalesce(DLG.GOAL, 0)
else coalesce(DG.GOAL, 0)
end DESIGNATIONGOAL,
coalesce(DLG.GOAL, 0) OVERALLGOAL,
DLG.NAME GOALNAME,
DLG.STARTDATE STARTDATE,
DLG.ENDDATE ENDDATE,
ISNULL(CONVERT(nvarchar(10), DLG.STARTDATE, 101) + ' to ' + CONVERT(nvarchar(10), DLG.ENDDATE, 101), '') DATERANGE,
coalesce(NEWCOMMITMENT.TOTALNEWCOMMITMENT,0) - (coalesce(NEWCOMMITMENT.TOTALWRITEOFFS,0)) TOTALEXPECTED,
coalesce(RECEIVED.TOTALRECEIVED,0) TOTALRECEIVED,
coalesce(PLANNEDGIFT.TOTALPLANNEDGIFT,0) TOTALPLANNEDGIFT,
isnull(REVINFO.NUMGIFTS,0) - isnull(REVINFO.NUMREFUNDED,0) TOTALGIFTS,
isnull(REVINFO.NUMDONORS,0) TOTALDONORS,
case when D.DESIGNATIONLEVEL2ID is null then 1
when D.DESIGNATIONLEVEL3ID is null then 2
when D.DESIGNATIONLEVEL4ID is null then 3
when D.DESIGNATIONLEVEL5ID is null then 4
else 5 end as LEVEL,
coalesce(cast(D.DESIGNATIONLEVEL1ID as nvarchar(36)), '') + coalesce(cast(D.DESIGNATIONLEVEL2ID as nvarchar(36)), '')
+ coalesce(cast(D.DESIGNATIONLEVEL3ID as nvarchar(36)), '') + coalesce(cast(D.DESIGNATIONLEVEL4ID as nvarchar(36)), '')
+ coalesce(cast(D.DESIGNATIONLEVEL5ID as nvarchar(36)), '') as [PATH],
case when D.DESIGNATIONLEVEL2ID is null then ''
when D.DESIGNATIONLEVEL3ID is null then coalesce(cast(D.DESIGNATIONLEVEL1ID as nvarchar(36)), '')
when D.DESIGNATIONLEVEL4ID is null then coalesce(cast(D.DESIGNATIONLEVEL1ID as nvarchar(36)), '') + coalesce(cast(D.DESIGNATIONLEVEL2ID as nvarchar(36)), '')
when D.DESIGNATIONLEVEL5ID is null then coalesce(cast(D.DESIGNATIONLEVEL1ID as nvarchar(36)), '') + coalesce(cast(D.DESIGNATIONLEVEL2ID as nvarchar(36)), '')
+ coalesce(cast(D.DESIGNATIONLEVEL3ID as nvarchar(36)), '')
else coalesce(cast(D.DESIGNATIONLEVEL1ID as nvarchar(36)), '') + coalesce(cast(D.DESIGNATIONLEVEL2ID as nvarchar(36)), '')
+ coalesce(cast(D.DESIGNATIONLEVEL3ID as nvarchar(36)), '') + coalesce(cast(D.DESIGNATIONLEVEL4ID as nvarchar(36)), '')
end as [PARENTPATH],
FNC_CURRENCYGETPROPERTIES.ID as CURRENCYID,
FNC_CURRENCYGETPROPERTIES.ISO4217 as CURRENCYISO,
FNC_CURRENCYGETPROPERTIES.DECIMALDIGITS as CURRENCYDECIMALDIGITS,
FNC_CURRENCYGETPROPERTIES.CURRENCYSYMBOL as CURRENCYSYMBOL,
FNC_CURRENCYGETPROPERTIES.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
D.ISACTIVE
from
dbo.DESIGNATIONLEVEL DL
inner join dbo.DESIGNATIONLEVELTYPE DLT on DLT.ID = DL.DESIGNATIONLEVELTYPEID
left join dbo.DESIGNATION D on DL.ID = D.DESIGNATIONLEVEL1ID
left join dbo.DESIGNATIONREPORT1CODE DR1C on DR1C.ID = D.DESIGNATIONREPORT1CODEID
left join dbo.DESIGNATIONREPORT2CODE DR2C on DR2C.ID = D.DESIGNATIONREPORT2CODEID
left join dbo.DESIGNATIONLEVELGOAL as DLG on DL.ID = DLG.DESIGNATIONLEVELID
left join dbo.DESIGNATIONGOAL as DG on DLG.ID = DG.DESIGNATIONLEVELGOALID and D.ID = DG.DESIGNATIONID
left join dbo.DESIGNATIONLEVEL DL2 on D.DESIGNATIONLEVEL2ID = DL2.ID
left join dbo.DESIGNATIONLEVEL DL3 on D.DESIGNATIONLEVEL3ID = DL3.ID
left join dbo.DESIGNATIONLEVEL DL4 on D.DESIGNATIONLEVEL4ID = DL4.ID
left join dbo.DESIGNATIONLEVEL DL5 on D.DESIGNATIONLEVEL5ID = DL5.ID
left join dbo.DESIGNATIONLEVELTYPE LASTLEVELTYPE on LASTLEVELTYPE.ID = COALESCE(DL5.DESIGNATIONLEVELTYPEID,DL4.DESIGNATIONLEVELTYPEID,DL3.DESIGNATIONLEVELTYPEID,DL2.DESIGNATIONLEVELTYPEID)
outer apply dbo.UFN_DESIGNATION_NEWCOMMITMENTREVENUEINCURRENCY(@STARTDATE,@ENDDATE, D.BASECURRENCYID, D.ID) NEWCOMMITMENT
outer apply dbo.UFN_DESIGNATION_REVENUERECEIVEDINCURRENCY(@STARTDATE,@ENDDATE, D.BASECURRENCYID, D.ID) RECEIVED
left join dbo.UFN_DESIGNATION_REVENUECOUNTS_2(@STARTDATE,@ENDDATE) REVINFO on D.ID = REVINFO.DESIGNATIONID
outer apply dbo.UFN_DESIGNATION_PLANNEDGIFTREVENUEINCURRENCY(@STARTDATE,@ENDDATE, D.BASECURRENCYID, D.ID) PLANNEDGIFT
outer apply dbo.UFN_CURRENCY_GETPROPERTIES(D.BASECURRENCYID) FNC_CURRENCYGETPROPERTIES
/*#IDSETEXTENSION*/
where (DL.ID = @DESIGNATIONLEVELID OR @DESIGNATIONLEVELID IS NULL)
and (DLG.ID = @GOALID or @GOALID is null)
order by LEVEL1NAME, GOALNAME, LEVEL2NAME, LEVEL3NAME, LEVEL4NAME, LEVEL5NAME
end
else -- The user has selected "Organization". Use the "INCURRENCY" function(s) to return the converted amounts
begin
-- Return all data in Organization currency
declare @CURRENCYID uniqueidentifier;
declare @CURRENCYISO nvarchar(3);
declare @CURRENCYDECIMALDIGITS tinyint;
declare @CURRENCYSYMBOL nvarchar(5);
declare @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;
select
@CURRENCYID = ID,
@CURRENCYISO = ISO4217,
@CURRENCYDECIMALDIGITS = DECIMALDIGITS,
@CURRENCYSYMBOL = CURRENCYSYMBOL,
@CURRENCYSYMBOLDISPLAYSETTINGCODE = SYMBOLDISPLAYSETTINGCODE
from CURRENCY where ID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
select
'http://www.blackbaud.com/DESIGNATIONID?DESIGNATIONID=' + CONVERT(nvarchar(36),D.ID) as [DESIGNATIONID],
DL.NAME LEVEL1NAME,
DL2.NAME LEVEL2NAME,
DL3.NAME LEVEL3NAME,
DL4.NAME LEVEL4NAME,
DL5.NAME LEVEL5NAME,
DL.ID DESIGNATIONLEVEL1ID,
D.DESIGNATIONLEVEL2ID DESIGNATIONLEVEL2ID,
D.DESIGNATIONLEVEL3ID DESIGNATIONLEVEL3ID,
D.DESIGNATIONLEVEL4ID DESIGNATIONLEVEL4ID,
D.DESIGNATIONLEVEL5ID DESIGNATIONLEVEL5ID,
DLT.DESCRIPTION DESIGNATIONLEVELTYPE,
coalesce(DL5.DESIGNATIONLEVELCATEGORYCODEID,DL4.DESIGNATIONLEVELCATEGORYCODEID,DL3.DESIGNATIONLEVELCATEGORYCODEID,DL2.DESIGNATIONLEVELCATEGORYCODEID) LASTCATEGORYCODEID,
coalesce(DL5.DESIGNATIONLEVELTYPEID,DL4.DESIGNATIONLEVELTYPEID,DL3.DESIGNATIONLEVELTYPEID,DL2.DESIGNATIONLEVELTYPEID) LASTTYPEID,
LASTLEVELTYPE.DESCRIPTION LASTTYPE,
DR1C.DESCRIPTION REPORTCODE1,
DR2C.DESCRIPTION REPORTCODE2,
case when D.DESIGNATIONLEVEL2ID is null then coalesce(DLG.ORGANIZATIONGOAL, 0) else coalesce(DG.ORGANIZATIONGOAL, 0) end DESIGNATIONGOAL,
coalesce(DLG.ORGANIZATIONGOAL, 0) OVERALLGOAL,
DLG.NAME GOALNAME,
DLG.STARTDATE STARTDATE,
DLG.ENDDATE ENDDATE,
ISNULL(CONVERT(nvarchar(10), DLG.STARTDATE, 101) + ' to ' + CONVERT(nvarchar(10), DLG.ENDDATE, 101), '') DATERANGE,
coalesce(NEWCOMMITMENT.TOTALNEWCOMMITMENT,0) - (coalesce(NEWCOMMITMENT.TOTALWRITEOFFS,0)) TOTALEXPECTED,
coalesce(RECEIVED.TOTALRECEIVED,0) TOTALRECEIVED,
coalesce(PLANNEDGIFT.TOTALPLANNEDGIFT,0) TOTALPLANNEDGIFT,
isnull(REVINFO.NUMGIFTS,0) - isnull(REVINFO.NUMREFUNDED,0) TOTALGIFTS,
isnull(REVINFO.NUMDONORS,0) TOTALDONORS,
case when D.DESIGNATIONLEVEL2ID is null then 1
when D.DESIGNATIONLEVEL3ID is null then 2
when D.DESIGNATIONLEVEL4ID is null then 3
when D.DESIGNATIONLEVEL5ID is null then 4
else 5 end as LEVEL,
coalesce(cast(D.DESIGNATIONLEVEL1ID as nvarchar(36)), '') + coalesce(cast(D.DESIGNATIONLEVEL2ID as nvarchar(36)), '')
+ coalesce(cast(D.DESIGNATIONLEVEL3ID as nvarchar(36)), '') + coalesce(cast(D.DESIGNATIONLEVEL4ID as nvarchar(36)), '')
+ coalesce(cast(D.DESIGNATIONLEVEL5ID as nvarchar(36)), '') as [PATH],
case when D.DESIGNATIONLEVEL2ID is null then ''
when D.DESIGNATIONLEVEL3ID is null then coalesce(cast(D.DESIGNATIONLEVEL1ID as nvarchar(36)), '')
when D.DESIGNATIONLEVEL4ID is null then coalesce(cast(D.DESIGNATIONLEVEL1ID as nvarchar(36)), '') + coalesce(cast(D.DESIGNATIONLEVEL2ID as nvarchar(36)), '')
when D.DESIGNATIONLEVEL5ID is null then coalesce(cast(D.DESIGNATIONLEVEL1ID as nvarchar(36)), '') + coalesce(cast(D.DESIGNATIONLEVEL2ID as nvarchar(36)), '')
+ coalesce(cast(D.DESIGNATIONLEVEL3ID as nvarchar(36)), '')
else coalesce(cast(D.DESIGNATIONLEVEL1ID as nvarchar(36)), '') + coalesce(cast(D.DESIGNATIONLEVEL2ID as nvarchar(36)), '')
+ coalesce(cast(D.DESIGNATIONLEVEL3ID as nvarchar(36)), '') + coalesce(cast(D.DESIGNATIONLEVEL4ID as nvarchar(36)), '')
end as [PARENTPATH],
@CURRENCYID as CURRENCYID,
@CURRENCYISO as CURRENCYISO,
@CURRENCYDECIMALDIGITS as CURRENCYDECIMALDIGITS,
@CURRENCYSYMBOL as CURRENCYSYMBOL,
@CURRENCYSYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
D.ISACTIVE
from
dbo.DESIGNATIONLEVEL DL
inner join dbo.DESIGNATIONLEVELTYPE DLT on DLT.ID = DL.DESIGNATIONLEVELTYPEID
left join dbo.DESIGNATION D on DL.ID = D.DESIGNATIONLEVEL1ID
left join dbo.DESIGNATIONREPORT1CODE DR1C on DR1C.ID = D.DESIGNATIONREPORT1CODEID
left join dbo.DESIGNATIONREPORT2CODE DR2C on DR2C.ID = D.DESIGNATIONREPORT2CODEID
left join dbo.DESIGNATIONLEVELGOAL as DLG on DL.ID = DLG.DESIGNATIONLEVELID
left join dbo.DESIGNATIONGOAL as DG on DLG.ID = DG.DESIGNATIONLEVELGOALID and D.ID = DG.DESIGNATIONID
left join dbo.DESIGNATIONLEVEL DL2 on D.DESIGNATIONLEVEL2ID = DL2.ID
left join dbo.DESIGNATIONLEVEL DL3 on D.DESIGNATIONLEVEL3ID = DL3.ID
left join dbo.DESIGNATIONLEVEL DL4 on D.DESIGNATIONLEVEL4ID = DL4.ID
left join dbo.DESIGNATIONLEVEL DL5 on D.DESIGNATIONLEVEL5ID = DL5.ID
left join dbo.DESIGNATIONLEVELTYPE LASTLEVELTYPE on LASTLEVELTYPE.ID = COALESCE(DL5.DESIGNATIONLEVELTYPEID,DL4.DESIGNATIONLEVELTYPEID,DL3.DESIGNATIONLEVELTYPEID,DL2.DESIGNATIONLEVELTYPEID)
outer apply dbo.UFN_DESIGNATION_NEWCOMMITMENTREVENUEINCURRENCY(@STARTDATE,@ENDDATE, @CURRENCYID, D.ID) NEWCOMMITMENT
outer apply dbo.UFN_DESIGNATION_REVENUERECEIVEDINCURRENCY(@STARTDATE,@ENDDATE, @CURRENCYID, D.ID) RECEIVED
left join dbo.UFN_DESIGNATION_REVENUECOUNTS_2(@STARTDATE,@ENDDATE) REVINFO on D.ID = REVINFO.DESIGNATIONID
outer apply dbo.UFN_DESIGNATION_PLANNEDGIFTREVENUEINCURRENCY(@STARTDATE,@ENDDATE, @CURRENCYID, D.ID) PLANNEDGIFT
/*#IDSETEXTENSION*/
where (DL.ID = @DESIGNATIONLEVELID OR @DESIGNATIONLEVELID IS NULL)
and (DLG.ID = @GOALID or @GOALID is null)
order by LEVEL1NAME, GOALNAME, LEVEL2NAME, LEVEL3NAME, LEVEL4NAME, LEVEL5NAME
end
end