UFN_DESIGNATION_GETPROGRESS
This function returns progress information for the fundraising reports.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @DESIGNATIONLEVELID | uniqueidentifier | IN | |
| @GOALID | uniqueidentifier | IN | |
| @REPORTCODE1ID | uniqueidentifier | IN | |
| @REPORTCODE2ID | uniqueidentifier | IN | |
| @ROLLUPTOTALS | bit | IN | |
| @GROUPBY | tinyint | IN |
Definition
Copy
CREATE function dbo.UFN_DESIGNATION_GETPROGRESS
(
@DESIGNATIONLEVELID uniqueidentifier,
@GOALID uniqueidentifier,
@REPORTCODE1ID uniqueidentifier = null,
@REPORTCODE2ID uniqueidentifier = null,
@ROLLUPTOTALS bit = 0,
@GROUPBY tinyint = null
)
returns @DESIGNATIONPROGRESS table
(
DESIGNATIONID uniqueidentifier NOT NULL,
LEVEL1NAME nvarchar(100),
LEVEL2NAME nvarchar(100),
LEVEL3NAME nvarchar(100),
LEVEL4NAME nvarchar(100),
LEVEL5NAME nvarchar(100),
DESIGNATIONLEVEL1ID uniqueidentifier,
DESIGNATIONLEVEL2ID uniqueidentifier,
DESIGNATIONLEVEL3ID uniqueidentifier,
DESIGNATIONLEVEL4ID uniqueidentifier,
DESIGNATIONLEVEL5ID uniqueidentifier,
DESIGNATIONLEVELTYPE nvarchar(100),
LASTCATEGORYCODEID uniqueidentifier,
LASTTYPEID uniqueidentifier,
LASTTYPE nvarchar(100),
REPORTCODE1 nvarchar(100),
REPORTCODE2 nvarchar(100),
DESIGNATIONGOAL money,
OVERALLGOAL money,
GOALNAME nvarchar(100),
STARTDATE datetime,
ENDDATE datetime,
DATERANGE nvarchar(24),
TOTALEXPECTED money,
TOTALRECEIVED money,
TOTALGIFTS int,
TOTALDONORS int,
APPEALNAME nvarchar(100),
APPEALRECEIVED money,
APPEALEXPECTED money,
APPEALNUMGIFTS int,
APPEALNUMDONORS int,
APPEALMAXGIFT money,
APPEALPLANNEDGIFT money,
TOTALPLANNEDGIFT money
)
as
begin
declare @STARTDATE datetime;
declare @ENDDATE datetime;
select @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(STARTDATE),
@ENDDATE = dbo.UFN_DATE_GETLATESTTIME(ENDDATE)
from dbo.DESIGNATIONLEVELGOAL where ID = @GOALID;
with APPEALRECEIVED_CTE as (
select case when @GROUPBY = 1 then APPEAL.ID
when @GROUPBY = 2 then ABU.BUSINESSUNITCODEID
when @GROUPBY = 3 then APPEAL.APPEALREPORT1CODEID
end as APPEALID,
AR.DESID,
SUM(AR.DESTOTALRECEIVED) as RECEIVED
from dbo.UFN_DESIGNATION_RAISEDBYAPPEAL(@STARTDATE,@ENDDATE) AR
inner join dbo.APPEAL on AR.APPEALID = APPEAL.ID
left join dbo.APPEALBUSINESSUNIT ABU on ABU.APPEALID = APPEAL.ID
group by AR.DESID, case when @GROUPBY = 1 then APPEAL.ID
when @GROUPBY = 2 then ABU.BUSINESSUNITCODEID
when @GROUPBY = 3 then APPEAL.APPEALREPORT1CODEID
end),
APPEALNEWCOMMITMENT_CTE as (
select
case when @GROUPBY = 1 then APPEAL.ID
when @GROUPBY = 2 then ABU.BUSINESSUNITCODEID
when @GROUPBY = 3 then APPEAL.APPEALREPORT1CODEID
end as APPEALID,
ANC.DESID,
sum(ANC.DESTOTALNEWCOMMITMENT) - sum(ANC.DESNEWCOMMITMENTWRITTENOFF) as NEWCOMMITMENT
from dbo.UFN_DESIGNATION_NEWCOMMITMENTREVENUEBYAPPEAL(@STARTDATE,@ENDDATE) ANC
inner join dbo.APPEAL on ANC.APPEALID = APPEAL.ID
left join dbo.APPEALBUSINESSUNIT ABU on ABU.APPEALID = APPEAL.ID
group by ANC.DESID, case when @GROUPBY = 1 then APPEAL.ID
when @GROUPBY = 2 then ABU.BUSINESSUNITCODEID
when @GROUPBY = 3 then APPEAL.APPEALREPORT1CODEID
end),
APPEALPLANNEDGIFTREVENUE_CTE as (
select
case when @GROUPBY = 1 then APPEAL.ID
when @GROUPBY = 2 then ABU.BUSINESSUNITCODEID
when @GROUPBY = 3 then APPEAL.APPEALREPORT1CODEID
end as APPEALID,
APG.DESID,
sum(APG.DESTOTALPLANNEDGIFT) as PLANNEDGIFT
from dbo.UFN_DESIGNATION_PLANNEDGIFTREVENUEBYAPPEAL(@STARTDATE,@ENDDATE) APG
inner join dbo.APPEAL on APG.APPEALID = APPEAL.ID
left join dbo.APPEALBUSINESSUNIT ABU on ABU.APPEALID = APPEAL.ID
group by APG.DESID, case when @GROUPBY = 1 then APPEAL.ID
when @GROUPBY = 2 then ABU.BUSINESSUNITCODEID
when @GROUPBY = 3 then APPEAL.APPEALREPORT1CODEID
end),
APPEALCOUNTS_CTE as (
select case when @GROUPBY = 1 then APPEAL.ID
when @GROUPBY = 2 then ABU.BUSINESSUNITCODEID
when @GROUPBY = 3 then APPEAL.APPEALREPORT1CODEID
end as APPEALID,
AC.DESIGNATIONID,
sum(AC.NUMDONORS) AS NUMDONORS,
SUM(AC.NUMGIFTS) AS NUMGIFTS,
MAX(AC.MAXGIFT) AS MAXGIFT
from dbo.UFN_DESIGNATION_REVENUECOUNTSBYAPPEAL(@STARTDATE,@ENDDATE) AC
inner join dbo.APPEAL on AC.APPEALID = APPEAL.ID
left join dbo.APPEALBUSINESSUNIT ABU on ABU.APPEALID = APPEAL.ID
group by AC.DESIGNATIONID, case when @GROUPBY = 1 then APPEAL.ID
when @GROUPBY = 2 then ABU.BUSINESSUNITCODEID
when @GROUPBY = 3 then APPEAL.APPEALREPORT1CODEID
end),
APPEALS_CTE as
(select case @GROUPBY when 1 then APPEAL.ID
when 2 then ABU.BUSINESSUNITCODEID
when 3 then APPEAL.APPEALREPORT1CODEID
end as APPEALID,
case @GROUPBY when 1 then APPEAL.NAME
when 2 then BUSINESSUNITCODE.DESCRIPTION
when 3 then APPEALREPORTCODE.DESCRIPTION
end as APPEALNAME
from dbo.APPEAL
left join dbo.APPEALBUSINESSUNIT ABU on ABU.APPEALID = APPEAL.ID
left join dbo.BUSINESSUNITCODE on ABU.BUSINESSUNITCODEID = BUSINESSUNITCODE.ID
left join dbo.APPEALREPORT1CODE APPEALREPORTCODE on APPEAL.APPEALREPORT1CODEID = APPEALREPORTCODE.ID)
insert into @DESIGNATIONPROGRESS
select distinct
D.ID DESIGNATIONID,
DL.NAME LEVEL1NAME,
dbo.UFN_DESIGNATIONLEVEL_GETNAME(D.DESIGNATIONLEVEL2ID) LEVEL2NAME,
dbo.UFN_DESIGNATIONLEVEL_GETNAME(D.DESIGNATIONLEVEL3ID) LEVEL3NAME,
dbo.UFN_DESIGNATIONLEVEL_GETNAME(D.DESIGNATIONLEVEL4ID) LEVEL4NAME,
dbo.UFN_DESIGNATIONLEVEL_GETNAME(D.DESIGNATIONLEVEL5ID) LEVEL5NAME,
DL.ID DESIGNATIONLEVEL1ID,
D.DESIGNATIONLEVEL2ID DESIGNATIONLEVEL2ID,
D.DESIGNATIONLEVEL3ID DESIGNATIONLEVEL3ID,
D.DESIGNATIONLEVEL4ID DESIGNATIONLEVEL4ID,
D.DESIGNATIONLEVEL5ID DESIGNATIONLEVEL5ID,
FPT.DESCRIPTION DESIGNATIONLEVELTYPE,
COALESCE(DL5.DESIGNATIONLEVELCATEGORYCODEID,DL4.DESIGNATIONLEVELCATEGORYCODEID,DL3.DESIGNATIONLEVELCATEGORYCODEID,DL2.DESIGNATIONLEVELCATEGORYCODEID,DL.DESIGNATIONLEVELCATEGORYCODEID) LASTCATEGORYCODEID,
COALESCE(DL5.DESIGNATIONLEVELTYPEID,DL4.DESIGNATIONLEVELTYPEID,DL3.DESIGNATIONLEVELTYPEID,DL2.DESIGNATIONLEVELTYPEID,DL.DESIGNATIONLEVELTYPEID) LASTTYPEID,
dbo.UFN_DESIGNATIONLEVELTYPE_GETDESCRIPTION(COALESCE(DL5.DESIGNATIONLEVELTYPEID,DL4.DESIGNATIONLEVELTYPEID,DL3.DESIGNATIONLEVELTYPEID,DL2.DESIGNATIONLEVELTYPEID,DL.DESIGNATIONLEVELTYPEID)) LASTTYPE,
RC1.DESCRIPTION REPORTCODE1,
RC2.DESCRIPTION REPORTCODE2,
case when D.DESIGNATIONLEVEL2ID is null then coalesce(DLG.GOAL, 0)
else coalesce(DG.GOAL, 0) end DESIGNATIONGOAL,
ISNULL(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,
(NEWCOMMITMENT.TOTALNEWCOMMITMENT - NEWCOMMITMENT.TOTALWRITEOFFS) TOTALEXPECTED,
RECEIVED.TOTALRECEIVED TOTALRECEIVED,
REVCOUNTS.NUMGIFTS TOTALGIFTS,
REVCOUNTS.NUMDONORS TOTALDONORS,
A.APPEALNAME,
coalesce(AREC.RECEIVED, 0) as APPEALRECEIVED,
coalesce(ANC.NEWCOMMITMENT, 0) as APPEALEXPECTED,
coalesce(AC.NUMGIFTS, 0) as APPEALNUMGIFTS,
coalesce(AC.NUMDONORS, 0) as APPEALNUMDONORS,
coalesce(AC.MAXGIFT, 0) as APPEALMAXGIFT,
coalesce(APG.PLANNEDGIFT, 0) as APPEALPLANNEDGIFT,
coalesce(PLANNEDGIFT.TOTALPLANNEDGIFT, 0) TOTALPLANNEDGIFT
from APPEALS_CTE APPEALS cross join
dbo.DESIGNATIONLEVEL DL
inner join dbo.DESIGNATIONLEVELTYPE FPT on DL.DESIGNATIONLEVELTYPEID = FPT.ID
inner join dbo.DESIGNATION D on DL.ID = D.DESIGNATIONLEVEL1ID
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.DESIGNATIONREPORT1CODE as RC1 on D.DESIGNATIONREPORT1CODEID = RC1.ID
left join dbo.DESIGNATIONREPORT2CODE as RC2 on D.DESIGNATIONREPORT2CODEID = RC2.ID
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.UFN_DESIGNATION_REVENUECOUNTS(@STARTDATE,@ENDDATE) REVCOUNTS on D.ID = REVCOUNTS.DESIGNATIONID
left join dbo.UFN_DESIGNATION_REVENUERECEIVED(@STARTDATE,@ENDDATE) RECEIVED on D.ID = RECEIVED.DESIGNATIONID
left join dbo.UFN_DESIGNATION_NEWCOMMITMENTREVENUE(@STARTDATE,@ENDDATE) NEWCOMMITMENT on D.ID = NEWCOMMITMENT.DESIGNATIONID
left join dbo.UFN_DESIGNATION_PLANNEDGIFTREVENUE(@STARTDATE,@ENDDATE) PLANNEDGIFT on D.ID = PLANNEDGIFT.DESIGNATIONID
left join APPEALRECEIVED_CTE AREC on D.ID = AREC.DESID and APPEALS.APPEALID = AREC.APPEALID
left join APPEALNEWCOMMITMENT_CTE ANC on D.ID = ANC.DESID and APPEALS.APPEALID = ANC.APPEALID
left join APPEALPLANNEDGIFTREVENUE_CTE APG on D.ID = APG.DESID and APPEALS.APPEALID = APG.APPEALID
left join APPEALCOUNTS_CTE AC on D.ID = AC.DESIGNATIONID and APPEALS.APPEALID = AC.APPEALID
left join APPEALS_CTE A on A.APPEALID = coalesce(AREC.APPEALID, ANC.APPEALID, APG.APPEALID)
where (DL.ID = @DESIGNATIONLEVELID)
and (DLG.ID = @GOALID or @GOALID is null)
and (RC1.ID = @REPORTCODE1ID or @REPORTCODE1ID is null) and
(RC2.ID = @REPORTCODE2ID or @REPORTCODE2ID is null);
return
end