USP_FAFEVENT_REVENUESUMMARY
Show revenue summary report for an FAF Event
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @EVENTID | uniqueidentifier | IN | |
| @STARTDATE | datetime | IN | |
| @ENDDATE | datetime | IN |
Definition
Copy
CREATE procedure dbo.USP_FAFEVENT_REVENUESUMMARY
(
@EVENTID uniqueidentifier = null,
@STARTDATE datetime = null,
@ENDDATE datetime = null
)
as
begin
--SET @EVENTID = 'AAC21129-AF62-4997-8E89-6828E109F194'
IF (@EVENTID IS NOT NULL)
BEGIN
--Field name parameters
DECLARE @RevenueSummaryReport AS TABLE (id uniqueidentifier, GroupName nvarchar(25), ChildCategory nvarchar(25), TotalRevenue float, RevenueRetained float, NewRevenueRetained float)
DECLARE @REVENUETYPECODE integer = 0 --0:Donation, 1:Registration, 15:Sponsorship, 7: MatchingGift
DECLARE @LASTYEAREVENTID uniqueidentifier --Last year event id
--Revenue Types
--Donation: Actual, Goal, Percent Achieved, Count
DECLARE @VarGroupName nvarchar(25) = ''
DECLARE @VarTempChildCategory nvarchar(25) = ''
DECLARE @VarTempTotalRevenueActual money = 0.0
DECLARE @VarTempRevenueRetainedActual money = 0.0
DECLARE @VarTempNewRevenueRetainedActual money = 0.0
DECLARE @VarTempTotalRevenueGoal money = 0.0
DECLARE @VarTempRevenueRetainedGoal money = 0.0
DECLARE @VarTempNewRevenueRetainedGoal money = 0.0
DECLARE @VarTempTotalRevenueAchieved float = 0
DECLARE @VarTempRevenueRetainedAchieved float = 0
DECLARE @VarTempNewRevenueRetainedAchieved float = 0
DECLARE @VarTempTotalRevenueCount integer = 0
DECLARE @VarTempRevenueRetainedCount integer = 0
DECLARE @VarTempNewRevenueRetainedCount integer = 0
DECLARE @VarLastYearEventRevenueTotal money = 0.0
select @LASTYEAREVENTID = PRIORYEAREVENTID from dbo.EVENTEXTENSION where EVENTID = @EVENTID
---A-Donation------------------------------------------------
SET @VarGroupName = 'Donation'
---1-Actual
---For donations, we want to count matching gift revenue as well
SET @VarTempChildCategory = 'Actual'
SELECT @VarTempTotalRevenueActual=ISNULL(sum(DA.TOTAL),0.0),@VarTempTotalRevenueCount=ISNULL(sum(DA.TOTALCOUNT),0) FROM dbo.UFN_FAFEVENT_GETEVENTTOTAL_BY_CATEGORY(@EVENTID,@STARTDATE,@ENDDATE,0) DA WHERE DA.REVENUETYPECODE in (0, 7)
SELECT @VarTempRevenueRetainedActual=ISNULL(sum(DA.TOTAL),0.0),@VarTempRevenueRetainedCount=ISNULL(sum(DA.TOTALCOUNT),0) FROM dbo.UFN_FAFEVENT_GETEVENTTOTAL_BY_CATEGORY(@EVENTID,@STARTDATE,@ENDDATE,1) DA WHERE DA.REVENUETYPECODE in (0, 7)
If @VarTempTotalRevenueActual > @VarTempRevenueRetainedActual
SET @VarTempNewRevenueRetainedActual = @VarTempTotalRevenueActual - @VarTempRevenueRetainedActual
INSERT INTO @RevenueSummaryReport (id, GroupName, ChildCategory, TotalRevenue, RevenueRetained, NewRevenueRetained)
VALUES(NewID(),@VarGroupName,@VarTempChildCategory,@VarTempTotalRevenueActual,@VarTempRevenueRetainedActual,@VarTempNewRevenueRetainedActual)
---2-Goal
SET @VarTempChildCategory = 'Goal'
SELECT @VarTempTotalRevenueGoal=ISNULL(EG.REVENUETOTAL - (EG.REVENUEREGISTRATION + EG.REVENUESPONSOR),0.0) FROM dbo.EVENTGOAL EG (NOLOCK) LEFT JOIN dbo.EVENT E (NOLOCK)ON EG.EVENTID = E.ID WHERE E.ID = @EVENTID
--get registration revenue of last year event
SELECT @VarLastYearEventRevenueTotal=ISNULL(DA.TOTAL,0.0) FROM dbo.UFN_FAFEVENT_GETEVENTTOTAL_BY_CATEGORY(@LASTYEAREVENTID ,null,null,0) DA WHERE DA.REVENUETYPECODE=@REVENUETYPECODE
SELECT @VarTempRevenueRetainedGoal=ISNULL((EG.REVENUERETENTION * (EG.REVENUETOTAL - (EG.REVENUEREGISTRATION + EG.REVENUESPONSOR))),0.0)* @VarLastYearEventRevenueTotal FROM dbo.EVENTGOAL EG (NOLOCK) LEFT JOIN dbo.EVENT E (NOLOCK)ON EG.EVENTID = E.ID WHERE E.ID = @EVENTID
If @VarTempTotalRevenueGoal > @VarTempRevenueRetainedGoal
SET @VarTempNewRevenueRetainedGoal = @VarTempTotalRevenueGoal - @VarTempRevenueRetainedGoal
INSERT INTO @RevenueSummaryReport (id, GroupName, ChildCategory, TotalRevenue, RevenueRetained, NewRevenueRetained)
VALUES(NewID(),@VarGroupName,@VarTempChildCategory,@VarTempTotalRevenueGoal,@VarTempRevenueRetainedGoal,@VarTempNewRevenueRetainedGoal)
---3-Percent Achieved
SET @VarTempChildCategory = '% Achieved'
If (@VarTempTotalRevenueGoal > 0)
SET @VarTempTotalRevenueAchieved = @VarTempTotalRevenueActual / @VarTempTotalRevenueGoal
If (@VarTempRevenueRetainedGoal > 0)
SET @VarTempRevenueRetainedAchieved = @VarTempRevenueRetainedActual / @VarTempRevenueRetainedGoal
If (@VarTempNewRevenueRetainedGoal > 0)
SET @VarTempNewRevenueRetainedAchieved = @VarTempNewRevenueRetainedActual / @VarTempNewRevenueRetainedGoal
INSERT INTO @RevenueSummaryReport (id, GroupName, ChildCategory, TotalRevenue, RevenueRetained, NewRevenueRetained)
VALUES(NewID(),@VarGroupName,@VarTempChildCategory,@VarTempTotalRevenueAchieved,@VarTempRevenueRetainedAchieved,@VarTempNewRevenueRetainedAchieved)
---4-Count
SET @VarTempChildCategory = 'Count'
--@VarTempTotalRevenueCount: already got at Actual query
If @VarTempTotalRevenueCount > 0
SET @VarTempNewRevenueRetainedCount = @VarTempTotalRevenueCount - @VarTempRevenueRetainedCount
INSERT INTO @RevenueSummaryReport (id, GroupName, ChildCategory, TotalRevenue, RevenueRetained, NewRevenueRetained)
VALUES(NewID(),@VarGroupName,@VarTempChildCategory,@VarTempTotalRevenueCount,@VarTempRevenueRetainedCount,@VarTempNewRevenueRetainedCount)
-------------------------------------------------------------
---A-Registrations------------------------------------------------
SET @VarGroupName = 'Registrations'
SET @REVENUETYPECODE = 1
--Reset all var
SET @VarTempChildCategory = ''
SET @VarTempTotalRevenueActual = 0.0
SET @VarTempRevenueRetainedActual = 0.0
SET @VarTempNewRevenueRetainedActual = 0.0 --need to write retained function here
SET @VarTempTotalRevenueGoal = 0.0
SET @VarTempRevenueRetainedGoal = 0.0
SET @VarTempNewRevenueRetainedGoal = 0.0 --need to write retained function here
SET @VarTempTotalRevenueAchieved = 0
SET @VarTempRevenueRetainedAchieved = 0
SET @VarTempNewRevenueRetainedAchieved = 0
SET @VarTempTotalRevenueCount = 0
SET @VarTempRevenueRetainedCount = 0
SET @VarTempNewRevenueRetainedCount = 0
---1-Actual
SET @VarTempChildCategory = 'Actual'
SELECT @VarTempTotalRevenueActual=ISNULL(DA.TOTAL,0.0),@VarTempTotalRevenueCount=ISNULL(DA.TOTALCOUNT,0) FROM dbo.UFN_FAFEVENT_GETEVENTTOTAL_BY_CATEGORY(@EVENTID,@STARTDATE,@ENDDATE,0) DA WHERE DA.REVENUETYPECODE=@REVENUETYPECODE
SELECT @VarTempRevenueRetainedActual=ISNULL(DA.TOTAL,0.0),@VarTempRevenueRetainedCount=ISNULL(DA.TOTALCOUNT,0) FROM dbo.UFN_FAFEVENT_GETEVENTTOTAL_BY_CATEGORY(@EVENTID,@STARTDATE,@ENDDATE,1) DA WHERE DA.REVENUETYPECODE=@REVENUETYPECODE
If @VarTempTotalRevenueActual > @VarTempRevenueRetainedActual
SET @VarTempNewRevenueRetainedActual = @VarTempTotalRevenueActual - @VarTempRevenueRetainedActual
INSERT INTO @RevenueSummaryReport (id, GroupName, ChildCategory, TotalRevenue, RevenueRetained, NewRevenueRetained)
VALUES(NewID(),@VarGroupName,@VarTempChildCategory,@VarTempTotalRevenueActual,@VarTempRevenueRetainedActual,@VarTempNewRevenueRetainedActual)
---2-Goal
SET @VarTempChildCategory = 'Goal'
SELECT @VarTempTotalRevenueGoal=ISNULL(EG.REVENUEREGISTRATION,0.0) FROM dbo.EVENTGOAL EG (NOLOCK) LEFT JOIN dbo.EVENT E (NOLOCK)ON EG.EVENTID = E.ID WHERE E.ID = @EVENTID
--SET @VarTempRevenueRetainedGoal = 0.0 --need to write new function here
--get registration revenue of last year event
SELECT @VarLastYearEventRevenueTotal=ISNULL(DA.TOTAL,0.0) FROM dbo.UFN_FAFEVENT_GETEVENTTOTAL_BY_CATEGORY(@LASTYEAREVENTID ,null,null,0) DA WHERE DA.REVENUETYPECODE=@REVENUETYPECODE
SELECT @VarTempRevenueRetainedGoal=ISNULL(EG.REVENUEREGISTRATIONRETENTION,0.0) * @VarLastYearEventRevenueTotal FROM dbo.EVENTGOAL EG (NOLOCK) LEFT JOIN dbo.EVENT E (NOLOCK)ON EG.EVENTID = E.ID WHERE E.ID = @EVENTID
--SELECT @VarTempRevenueRetainedGoal=ISNULL(EG.REVENUEREGISTRATIONRETENTION*100,0.0) FROM dbo.EVENTGOAL EG (NOLOCK) LEFT JOIN dbo.EVENT E (NOLOCK)ON EG.EVENTID = E.ID WHERE E.ID = @EVENTID
If @VarTempTotalRevenueGoal > @VarTempRevenueRetainedGoal
SET @VarTempNewRevenueRetainedGoal = @VarTempTotalRevenueGoal - @VarTempRevenueRetainedGoal
INSERT INTO @RevenueSummaryReport (id, GroupName, ChildCategory, TotalRevenue, RevenueRetained, NewRevenueRetained)
VALUES(NewID(),@VarGroupName,@VarTempChildCategory,@VarTempTotalRevenueGoal,@VarTempRevenueRetainedGoal,@VarTempNewRevenueRetainedGoal)
---3-Percent Achieved
SET @VarTempChildCategory = '% Achieved'
If (@VarTempTotalRevenueGoal > 0)
SET @VarTempTotalRevenueAchieved = @VarTempTotalRevenueActual / @VarTempTotalRevenueGoal
If (@VarTempRevenueRetainedGoal > 0)
SET @VarTempRevenueRetainedAchieved = @VarTempRevenueRetainedActual / @VarTempRevenueRetainedGoal
If (@VarTempNewRevenueRetainedGoal > 0)
SET @VarTempNewRevenueRetainedAchieved = @VarTempNewRevenueRetainedActual / @VarTempNewRevenueRetainedGoal
INSERT INTO @RevenueSummaryReport (id, GroupName, ChildCategory, TotalRevenue, RevenueRetained, NewRevenueRetained)
VALUES(NewID(),@VarGroupName,@VarTempChildCategory,@VarTempTotalRevenueAchieved,@VarTempRevenueRetainedAchieved,@VarTempNewRevenueRetainedAchieved)
---4-Count
SET @VarTempChildCategory = 'Count'
--@VarTempTotalRevenueCount: same
If @VarTempTotalRevenueCount > 0
SET @VarTempNewRevenueRetainedCount = @VarTempTotalRevenueCount - @VarTempRevenueRetainedCount
INSERT INTO @RevenueSummaryReport (id, GroupName, ChildCategory, TotalRevenue, RevenueRetained, NewRevenueRetained)
VALUES(NewID(),@VarGroupName,@VarTempChildCategory,@VarTempTotalRevenueCount,@VarTempRevenueRetainedCount,@VarTempNewRevenueRetainedCount)
/*
---------- PLEDGES
--- Pledges
SET @VarGroupName = 'Pledges'
SET @REVENUETYPECODE = 1
--Reset all var
SET @VarTempChildCategory = ''
SET @VarTempTotalRevenueActual = 0.0
SET @VarTempRevenueRetainedActual = 0.0
SET @VarTempNewRevenueRetainedActual = 0.0 --need to write retained function here
SET @VarTempTotalRevenueGoal = 0.0
SET @VarTempRevenueRetainedGoal = 0.0
SET @VarTempNewRevenueRetainedGoal = 0.0 --need to write retained function here
SET @VarTempTotalRevenueAchieved = 0
SET @VarTempRevenueRetainedAchieved = 0
SET @VarTempNewRevenueRetainedAchieved = 0
SET @VarTempTotalRevenueCount = 0
SET @VarTempRevenueRetainedCount = 0
SET @VarTempNewRevenueRetainedCount = 0
SET @VarTempChildCategory = 'Actual'
SELECT @VarTempTotalRevenueActual=ISNULL(sum(DA.TOTAL),0.0),@VarTempTotalRevenueCount=ISNULL(sum(DA.TOTALCOUNT),0) FROM dbo.UFN_FAFEVENT_GETEVENTTOTAL_BY_CATEGORY(@EVENTID,@STARTDATE,@ENDDATE,0) DA WHERE DA.REVENUETYPECODE = 2
SELECT @VarTempRevenueRetainedActual=ISNULL(sum(DA.TOTAL),0.0),@VarTempRevenueRetainedCount=ISNULL(sum(DA.TOTALCOUNT),0) FROM dbo.UFN_FAFEVENT_GETEVENTTOTAL_BY_CATEGORY(@EVENTID,@STARTDATE,@ENDDATE,1) DA WHERE DA.REVENUETYPECODE = 2
If @VarTempTotalRevenueActual > @VarTempRevenueRetainedActual
SET @VarTempNewRevenueRetainedActual = @VarTempTotalRevenueActual - @VarTempRevenueRetainedActual
INSERT INTO @RevenueSummaryReport (id, GroupName, ChildCategory, TotalRevenue, RevenueRetained, NewRevenueRetained)
VALUES(NewID(),@VarGroupName,@VarTempChildCategory,@VarTempTotalRevenueActual,@VarTempRevenueRetainedActual,@VarTempNewRevenueRetainedActual)
SET @VarTempChildCategory = 'Goal'
SELECT @VarTempTotalRevenueGoal=ISNULL(EG.REVENUETOTAL - (EG.REVENUEREGISTRATION + EG.REVENUESPONSOR),0.0) FROM dbo.EVENTGOAL EG (NOLOCK) LEFT JOIN dbo.EVENT E (NOLOCK)ON EG.EVENTID = E.ID WHERE E.ID = @EVENTID
SELECT @VarTempRevenueRetainedGoal=ISNULL(((EG.REVENUERETENTION*100) * (EG.REVENUETOTAL - (EG.REVENUEREGISTRATION + EG.REVENUESPONSOR))),0.0) FROM dbo.EVENTGOAL EG (NOLOCK) LEFT JOIN dbo.EVENT E (NOLOCK)ON EG.EVENTID = E.ID WHERE E.ID = @EVENTID
If @VarTempTotalRevenueGoal > @VarTempRevenueRetainedGoal
SET @VarTempNewRevenueRetainedGoal = @VarTempTotalRevenueGoal - @VarTempRevenueRetainedGoal
INSERT INTO @RevenueSummaryReport (id, GroupName, ChildCategory, TotalRevenue, RevenueRetained, NewRevenueRetained)
VALUES(NewID(),@VarGroupName,@VarTempChildCategory,@VarTempTotalRevenueGoal,@VarTempRevenueRetainedGoal,@VarTempNewRevenueRetainedGoal)
---3-Percent Achieved
SET @VarTempChildCategory = '% Achieved'
If (@VarTempTotalRevenueGoal > 0)
SET @VarTempTotalRevenueAchieved = @VarTempTotalRevenueActual / @VarTempTotalRevenueGoal
If (@VarTempRevenueRetainedGoal > 0)
SET @VarTempRevenueRetainedAchieved = @VarTempRevenueRetainedActual / @VarTempRevenueRetainedGoal
If (@VarTempNewRevenueRetainedGoal > 0)
SET @VarTempNewRevenueRetainedAchieved = @VarTempNewRevenueRetainedActual / @VarTempNewRevenueRetainedGoal
INSERT INTO @RevenueSummaryReport (id, GroupName, ChildCategory, TotalRevenue, RevenueRetained, NewRevenueRetained)
VALUES(NewID(),@VarGroupName,@VarTempChildCategory,@VarTempTotalRevenueAchieved,@VarTempRevenueRetainedAchieved,@VarTempNewRevenueRetainedAchieved)
---4-Count
SET @VarTempChildCategory = 'Count'
--@VarTempTotalRevenueCount: already got at Actual query
If @VarTempTotalRevenueCount > 0
SET @VarTempNewRevenueRetainedCount = @VarTempTotalRevenueCount - @VarTempRevenueRetainedCount
INSERT INTO @RevenueSummaryReport (id, GroupName, ChildCategory, TotalRevenue, RevenueRetained, NewRevenueRetained)
VALUES(NewID(),@VarGroupName,@VarTempChildCategory,@VarTempTotalRevenueCount,@VarTempRevenueRetainedCount,@VarTempNewRevenueRetainedCount)
--- END of PLEDGES
*/
---------- Pending gifts
--- Pledges
SET @VarGroupName = 'Pending'
SET @REVENUETYPECODE = 2
--Reset all var
SET @VarTempChildCategory = ''
SET @VarTempTotalRevenueActual = 0.0
SET @VarTempRevenueRetainedActual = 0.0
SET @VarTempNewRevenueRetainedActual = 0.0 --need to write retained function here
SET @VarTempTotalRevenueGoal = 0.0
SET @VarTempRevenueRetainedGoal = 0.0
SET @VarTempNewRevenueRetainedGoal = 0.0 --need to write retained function here
SET @VarTempTotalRevenueAchieved = 0
SET @VarTempRevenueRetainedAchieved = 0
SET @VarTempNewRevenueRetainedAchieved = 0
SET @VarTempTotalRevenueCount = 0
SET @VarTempRevenueRetainedCount = 0
SET @VarTempNewRevenueRetainedCount = 0
SET @VarTempChildCategory = 'Actual'
SELECT @VarTempTotalRevenueActual=ISNULL(sum(DA.TOTAL),0.0),@VarTempTotalRevenueCount=ISNULL(sum(DA.TOTALCOUNT),0) FROM dbo.UFN_FAFEVENT_GETEVENTTOTAL_BY_CATEGORY(@EVENTID,@STARTDATE,@ENDDATE,0) DA WHERE DA.REVENUETYPECODE = 4
SELECT @VarTempRevenueRetainedActual=ISNULL(sum(DA.TOTAL),0.0),@VarTempRevenueRetainedCount=ISNULL(sum(DA.TOTALCOUNT),0) FROM dbo.UFN_FAFEVENT_GETEVENTTOTAL_BY_CATEGORY(@EVENTID,@STARTDATE,@ENDDATE,1) DA WHERE DA.REVENUETYPECODE = 4
select @VarTempTotalRevenueActual = sum(RS.Amount) ,
@VarTempTotalRevenueCount = count(*)
from dbo.EVENT E With (NOLOCK)
join dbo.REVENUE R With (NOLOCK) on E.APPEALID = R.APPEALID and E.ID = @EVENTID
join dbo.REVENUESPLIT RS With (NOLOCK) on R.ID = RS.REVENUEID and (R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE = 17 )
join dbo.CONSTITUENT DONS With (NOLOCK) on R.CONSTITUENTID = DONS.ID
left join dbo.REVENUEPAYMENTMETHOD RPM With (NOLOCK) on RPM.REVENUEID = R.ID
where APPLICATION = 'Pending gift'
AND R.DATE BETWEEN ISNULL(@STARTDATE, '1/1/1900') AND ISNULL(@ENDDATE, '12/30/2999')
print @VarTempTotalRevenueActual
print @VarTempTotalRevenueCount
If @VarTempTotalRevenueActual > @VarTempRevenueRetainedActual
SET @VarTempNewRevenueRetainedActual = @VarTempTotalRevenueActual - @VarTempRevenueRetainedActual
INSERT INTO @RevenueSummaryReport (id, GroupName, ChildCategory, TotalRevenue, RevenueRetained, NewRevenueRetained)
VALUES(NewID(),@VarGroupName,@VarTempChildCategory,@VarTempTotalRevenueActual,@VarTempRevenueRetainedActual,@VarTempNewRevenueRetainedActual)
SET @VarTempChildCategory = 'Goal'
SELECT @VarTempTotalRevenueGoal=ISNULL(EG.REVENUETOTAL - (EG.REVENUEREGISTRATION + EG.REVENUESPONSOR),0.0) FROM dbo.EVENTGOAL EG (NOLOCK) LEFT JOIN dbo.EVENT E (NOLOCK)ON EG.EVENTID = E.ID WHERE E.ID = @EVENTID
--get Pledges revenue of last year event
SELECT @VarLastYearEventRevenueTotal=ISNULL(DA.TOTAL,0.0) FROM dbo.UFN_FAFEVENT_GETEVENTTOTAL_BY_CATEGORY(@LASTYEAREVENTID ,null,null,0) DA WHERE DA.REVENUETYPECODE=@REVENUETYPECODE
SELECT @VarTempRevenueRetainedGoal=ISNULL((EG.REVENUERETENTION * (EG.REVENUETOTAL - (EG.REVENUEREGISTRATION + EG.REVENUESPONSOR))),0.0)* @VarLastYearEventRevenueTotal FROM dbo.EVENTGOAL EG (NOLOCK) LEFT JOIN dbo.EVENT E (NOLOCK)ON EG.EVENTID = E.ID WHERE E.ID = @EVENTID
If @VarTempTotalRevenueGoal > @VarTempRevenueRetainedGoal
SET @VarTempNewRevenueRetainedGoal = @VarTempTotalRevenueGoal - @VarTempRevenueRetainedGoal
INSERT INTO @RevenueSummaryReport (id, GroupName, ChildCategory, TotalRevenue, RevenueRetained, NewRevenueRetained)
VALUES(NewID(),@VarGroupName,@VarTempChildCategory,@VarTempTotalRevenueGoal,@VarTempRevenueRetainedGoal,@VarTempNewRevenueRetainedGoal)
---3-Percent Achieved
SET @VarTempChildCategory = '% Achieved'
If (@VarTempTotalRevenueGoal > 0)
SET @VarTempTotalRevenueAchieved = @VarTempTotalRevenueActual / @VarTempTotalRevenueGoal
If (@VarTempRevenueRetainedGoal > 0)
SET @VarTempRevenueRetainedAchieved = @VarTempRevenueRetainedActual / @VarTempRevenueRetainedGoal
If (@VarTempNewRevenueRetainedGoal > 0)
SET @VarTempNewRevenueRetainedAchieved = @VarTempNewRevenueRetainedActual / @VarTempNewRevenueRetainedGoal
INSERT INTO @RevenueSummaryReport (id, GroupName, ChildCategory, TotalRevenue, RevenueRetained, NewRevenueRetained)
VALUES(NewID(),@VarGroupName,@VarTempChildCategory,@VarTempTotalRevenueAchieved,@VarTempRevenueRetainedAchieved,@VarTempNewRevenueRetainedAchieved)
---4-Count
SET @VarTempChildCategory = 'Count'
--@VarTempTotalRevenueCount: already got at Actual query
If @VarTempTotalRevenueCount > 0
SET @VarTempNewRevenueRetainedCount = @VarTempTotalRevenueCount - @VarTempRevenueRetainedCount
INSERT INTO @RevenueSummaryReport (id, GroupName, ChildCategory, TotalRevenue, RevenueRetained, NewRevenueRetained)
VALUES(NewID(),@VarGroupName,@VarTempChildCategory,@VarTempTotalRevenueCount,@VarTempRevenueRetainedCount,@VarTempNewRevenueRetainedCount)
--- END of PENDING gifts
-------------------------------------------------------------
---A-Sponsorships------------------------------------------------
SET @VarGroupName = 'Sponsorships'
SET @REVENUETYPECODE = 15
--Reset all var
SET @VarTempChildCategory = ''
SET @VarTempTotalRevenueActual = 0.0
SET @VarTempRevenueRetainedActual = 0.0
SET @VarTempNewRevenueRetainedActual = 0.0 --need to write retained function here
SET @VarTempTotalRevenueGoal = 0.0
SET @VarTempRevenueRetainedGoal = 0.0
SET @VarTempNewRevenueRetainedGoal = 0.0 --need to write retained function here
SET @VarTempTotalRevenueAchieved = 0
SET @VarTempRevenueRetainedAchieved = 0
SET @VarTempNewRevenueRetainedAchieved = 0
SET @VarTempTotalRevenueCount = 0
SET @VarTempRevenueRetainedCount = 0
SET @VarTempNewRevenueRetainedCount = 0
set @VarLastYearEventRevenueTotal = 0
---1-Actual
SET @VarTempChildCategory = 'Actual'
SELECT @VarTempTotalRevenueActual=ISNULL(DA.TOTAL,0.0),@VarTempTotalRevenueCount=ISNULL(DA.TOTALCOUNT,0) FROM dbo.UFN_FAFEVENT_GETEVENTTOTAL_BY_CATEGORY(@EVENTID,@STARTDATE,@ENDDATE,0) DA WHERE DA.REVENUETYPECODE=@REVENUETYPECODE
SELECT @VarTempRevenueRetainedActual=ISNULL(DA.TOTAL,0.0),@VarTempRevenueRetainedCount=ISNULL(DA.TOTALCOUNT,0) FROM dbo.UFN_FAFEVENT_GETEVENTTOTAL_BY_CATEGORY(@EVENTID,@STARTDATE,@ENDDATE,1) DA WHERE DA.REVENUETYPECODE=@REVENUETYPECODE
If @VarTempTotalRevenueActual > @VarTempRevenueRetainedActual
SET @VarTempNewRevenueRetainedActual = @VarTempTotalRevenueActual - @VarTempRevenueRetainedActual
INSERT INTO @RevenueSummaryReport (id, GroupName, ChildCategory, TotalRevenue, RevenueRetained, NewRevenueRetained)
VALUES(NewID(),@VarGroupName,@VarTempChildCategory,@VarTempTotalRevenueActual,@VarTempRevenueRetainedActual,@VarTempNewRevenueRetainedActual)
---2-Goal
SET @VarTempChildCategory = 'Goal'
SELECT @VarTempTotalRevenueGoal=ISNULL(EG.REVENUESPONSOR,0.0) FROM dbo.EVENTGOAL EG (NOLOCK) LEFT JOIN dbo.EVENT E (NOLOCK)ON EG.EVENTID = E.ID WHERE E.ID = @EVENTID
--get sponsorship revenue of last year event
SELECT @VarLastYearEventRevenueTotal=ISNULL(DA.TOTAL,0.0) FROM dbo.UFN_FAFEVENT_GETEVENTTOTAL_BY_CATEGORY(@LASTYEAREVENTID ,null,null,0) DA WHERE DA.REVENUETYPECODE=@REVENUETYPECODE
SELECT @VarTempRevenueRetainedGoal=ISNULL(EG.REVENUESPONSORRETENTION,0.0) * @VarLastYearEventRevenueTotal FROM dbo.EVENTGOAL EG (NOLOCK) LEFT JOIN dbo.EVENT E (NOLOCK)ON EG.EVENTID = E.ID WHERE E.ID = @EVENTID
If @VarTempTotalRevenueGoal > @VarTempRevenueRetainedGoal
SET @VarTempNewRevenueRetainedGoal = @VarTempTotalRevenueGoal - @VarTempRevenueRetainedGoal
INSERT INTO @RevenueSummaryReport (id, GroupName, ChildCategory, TotalRevenue, RevenueRetained, NewRevenueRetained)
VALUES(NewID(),@VarGroupName,@VarTempChildCategory,@VarTempTotalRevenueGoal,@VarTempRevenueRetainedGoal,@VarTempNewRevenueRetainedGoal)
---3-Percent Achieved
SET @VarTempChildCategory = '% Achieved'
If (@VarTempTotalRevenueGoal > 0)
SET @VarTempTotalRevenueAchieved = @VarTempTotalRevenueActual / @VarTempTotalRevenueGoal
If (@VarTempRevenueRetainedGoal > 0)
SET @VarTempRevenueRetainedAchieved = @VarTempRevenueRetainedActual / @VarTempRevenueRetainedGoal
If (@VarTempNewRevenueRetainedGoal > 0)
SET @VarTempNewRevenueRetainedAchieved = @VarTempNewRevenueRetainedActual / @VarTempNewRevenueRetainedGoal
INSERT INTO @RevenueSummaryReport (id, GroupName, ChildCategory, TotalRevenue, RevenueRetained, NewRevenueRetained)
VALUES(NewID(),@VarGroupName,@VarTempChildCategory,@VarTempTotalRevenueAchieved,@VarTempRevenueRetainedAchieved,@VarTempNewRevenueRetainedAchieved)
---4-Count
SET @VarTempChildCategory = 'Count'
--@VarTempTotalRevenueCount: same way
If @VarTempTotalRevenueCount > 0
SET @VarTempNewRevenueRetainedCount = @VarTempTotalRevenueCount - @VarTempRevenueRetainedCount
INSERT INTO @RevenueSummaryReport (id, GroupName, ChildCategory, TotalRevenue, RevenueRetained, NewRevenueRetained)
VALUES(NewID(),@VarGroupName,@VarTempChildCategory,@VarTempTotalRevenueCount,@VarTempRevenueRetainedCount,@VarTempNewRevenueRetainedCount)
-------------------------------------------------------------
---A-Total------------------------------------------------
SET @VarGroupName = 'Total'
SET @REVENUETYPECODE = 1
--Reset all var
SET @VarTempChildCategory = ''
SET @VarTempTotalRevenueActual = 0.0
SET @VarTempRevenueRetainedActual = 0.0
SET @VarTempNewRevenueRetainedActual = 0.0 --need to write retained function here
SET @VarTempTotalRevenueGoal = 0.0
SET @VarTempRevenueRetainedGoal = 0.0
SET @VarTempNewRevenueRetainedGoal = 0.0 --need to write retained function here
SET @VarTempTotalRevenueAchieved = 0
SET @VarTempRevenueRetainedAchieved = 0
SET @VarTempNewRevenueRetainedAchieved = 0
SET @VarTempTotalRevenueCount = 0
SET @VarTempRevenueRetainedCount = 0
SET @VarTempNewRevenueRetainedCount = 0
---1-Actual
SET @VarTempChildCategory = 'Actual'
--Total = DonationActual + RegistrationsActual + SponsorshipActual
--SELECT * FROM @RevenueSummaryReport WHERE ChildCategory = 'Actual'
SELECT @VarTempTotalRevenueActual=SUM(TotalRevenue), @VarTempRevenueRetainedActual=SUM(RevenueRetained),@VarTempNewRevenueRetainedActual=SUM(NewRevenueRetained) FROM @RevenueSummaryReport WHERE ChildCategory = 'Actual'
INSERT INTO @RevenueSummaryReport (id, GroupName, ChildCategory, TotalRevenue, RevenueRetained, NewRevenueRetained)
VALUES(NewID(),@VarGroupName,@VarTempChildCategory,@VarTempTotalRevenueActual,@VarTempRevenueRetainedActual,@VarTempNewRevenueRetainedActual)
---2-Goal
SET @VarTempChildCategory = 'Goal'
--get total revenue of last year event
SELECT @VarLastYearEventRevenueTotal=SUM(ISNULL(TOTAL,0.0)) FROM dbo.UFN_FAFEVENT_GETEVENTTOTAL_BY_CATEGORY(@LASTYEAREVENTID ,null,null,0)
SELECT @VarTempTotalRevenueGoal=ISNULL(EG.REVENUETOTAL,0.0),@VarTempRevenueRetainedGoal=ISNULL(EG.REVENUERETENTION,0.0)*@VarLastYearEventRevenueTotal FROM dbo.EVENTGOAL EG (NOLOCK) LEFT JOIN dbo.EVENT E (NOLOCK)ON EG.EVENTID = E.ID WHERE E.ID = @EVENTID
If @VarTempTotalRevenueGoal > @VarTempRevenueRetainedGoal
SET @VarTempNewRevenueRetainedGoal = @VarTempTotalRevenueGoal - @VarTempRevenueRetainedGoal
INSERT INTO @RevenueSummaryReport (id, GroupName, ChildCategory, TotalRevenue, RevenueRetained, NewRevenueRetained)
VALUES(NewID(),@VarGroupName,@VarTempChildCategory,@VarTempTotalRevenueGoal,@VarTempRevenueRetainedGoal,@VarTempNewRevenueRetainedGoal)
---3-Percent Achieved
SET @VarTempChildCategory = '% Achieved'
If (@VarTempTotalRevenueGoal > 0)
SET @VarTempTotalRevenueAchieved = @VarTempTotalRevenueActual / @VarTempTotalRevenueGoal
If (@VarTempRevenueRetainedGoal > 0)
SET @VarTempRevenueRetainedAchieved = @VarTempRevenueRetainedActual / @VarTempRevenueRetainedGoal
If (@VarTempNewRevenueRetainedGoal > 0)
SET @VarTempNewRevenueRetainedAchieved = @VarTempNewRevenueRetainedActual / @VarTempNewRevenueRetainedGoal
INSERT INTO @RevenueSummaryReport (id, GroupName, ChildCategory, TotalRevenue, RevenueRetained, NewRevenueRetained)
VALUES(NewID(),@VarGroupName,@VarTempChildCategory,@VarTempTotalRevenueAchieved,@VarTempRevenueRetainedAchieved,@VarTempNewRevenueRetainedAchieved)
---4-Count
SET @VarTempChildCategory = 'Count'
SELECT @VarTempTotalRevenueCount=SUM(TotalRevenue), @VarTempRevenueRetainedCount=SUM(RevenueRetained) FROM @RevenueSummaryReport WHERE ChildCategory = 'Count'
If @VarTempTotalRevenueCount > 0
SET @VarTempNewRevenueRetainedCount = @VarTempTotalRevenueCount - @VarTempRevenueRetainedCount
INSERT INTO @RevenueSummaryReport (id, GroupName, ChildCategory, TotalRevenue, RevenueRetained, NewRevenueRetained)
VALUES(NewID(),@VarGroupName,@VarTempChildCategory,@VarTempTotalRevenueCount,@VarTempRevenueRetainedCount,@VarTempNewRevenueRetainedCount)
--Never use SELECT * b/c it costs more
SELECT ID, GroupName,ChildCategory,TotalRevenue,RevenueRetained,NewRevenueRetained FROM @RevenueSummaryReport
-------
END
end