USP_SMARTFIELD_TOTALDONATIONAMOUNTRAISEDTEAMCURWEEK
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @TYPECODE | tinyint | IN | |
| @WEEKORMONTHCODE | tinyint | IN | |
| @ASOF | datetime | IN |
Definition
Copy
CREATE procedure dbo.USP_SMARTFIELD_TOTALDONATIONAMOUNTRAISEDTEAMCURWEEK
(
@TYPECODE tinyint= 1,
@WEEKORMONTHCODE tinyint= 1,
@ASOF datetime
)
as
set nocount on
-- write sql to calculate the smart field values based on the parameters and ASOF date
IF (@TYPECODE= 2)
BEGIN
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id( N'tempdb..'+'#TEMP_Get_TFTID') )
DROP TABLE #TEMP_Get_TFTID
----------------
IF NOT EXISTS (SELECT * FROM tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id( N'tempdb..'+'#TEMP_Get_TFTID') )
BEGIN
CREATE TABLE #TEMP_Get_TFTID ( PRIM_ID int IDENTITY(1,1) primary key, TEAMFUNDRAISINGTEAMID uniqueidentifier)
-----
INSERT INTO #TEMP_Get_TFTID ( TEAMFUNDRAISINGTEAMID)
-------
SELECT DISTINCT TFT2.PARENTTEAMID FROM TEAMFUNDRAISINGTEAM TFT1 JOIN TEAMFUNDRAISINGTEAM TFT2 ON TFT1.ID= TFT2.PARENTTEAMID
END
----------------------------------
IF @WEEKORMONTHCODE= 1
SELECT TOT.ID, SUM(TOT.AMOUNT) AS DONATION
FROM
-- additional donation made by team members
(SELECT TFT.ID, SUM(RS.AMOUNT) AS AMOUNT
FROM dbo.TEAMFUNDRAISINGTEAMMEMBER TM
JOIN dbo.TEAMFUNDRAISINGTEAM TFT ON TM.TEAMFUNDRAISINGTEAMID = TFT.ID
--JOIN dbo.TEAMFUNDRAISINGTEAM TFT2 ON TFT.ID= TFT2.PARENTTEAMID
JOIN dbo.TEAMFUNDRAISER TF ON TM.TEAMFUNDRAISERID = TF.ID
JOIN dbo.REGISTRANT R ON TF.CONSTITUENTID = R.CONSTITUENTID
JOIN dbo.EVENTEXTENSION EE ON R.EVENTID = EE.EVENTID
JOIN dbo.REVENUE RV ON RV.CONSTITUENTID = R.CONSTITUENTID
JOIN dbo.REVENUESPLIT RS ON RS.REVENUEID = RV.ID
WHERE RS.TYPECODE = 0
AND RV.TRANSACTIONTYPECODE = 0
AND TFT.ID IN (SELECT TEAMFUNDRAISINGTEAMID FROM #TEMP_Get_TFTID)
GROUP BY TFT.ID
UNION ALL
-- donations collected by team members
SELECT TFT.ID, SUM(RS.AMOUNT) AS AMOUNT
FROM dbo.TEAMFUNDRAISINGTEAMMEMBER TM
JOIN dbo.TEAMFUNDRAISINGTEAM TFT ON TM.TEAMFUNDRAISINGTEAMID = TFT.ID
--JOIN dbo.TEAMFUNDRAISINGTEAM TFT2 ON TFT.ID= TFT2.PARENTTEAMID
JOIN dbo.TEAMFUNDRAISER TF ON TM.TEAMFUNDRAISERID = TF.ID
JOIN dbo.REGISTRANT R ON TF.CONSTITUENTID = R.CONSTITUENTID
JOIN dbo.EVENTEXTENSION EE ON R.EVENTID = EE.EVENTID
JOIN dbo.REVENUERECOGNITION RR ON RR.CONSTITUENTID = R.CONSTITUENTID
JOIN dbo.REVENUESPLIT RS ON RS.ID = RR.REVENUESPLITID
JOIN dbo.REVENUE RV ON RV.ID = RS.REVENUEID
WHERE RS.TYPECODE = 0
AND RV.TRANSACTIONTYPECODE = 0 AND RV.CONSTITUENTID <> R.CONSTITUENTID
AND TFT.ID IN (SELECT TEAMFUNDRAISINGTEAMID FROM #TEMP_Get_TFTID)
GROUP BY TFT.ID) TOT
JOIN TEAMEXTENSION TE ON TOT.ID= TE.TEAMFUNDRAISINGTEAMID --AND TE.TYPECODE= @TYPECODE
AND TE.DATEADDED <= getdate() -7
GROUP BY TOT.ID
ORDER BY TOT.ID
ELSE
SELECT TOT.ID, SUM(TOT.AMOUNT) AS DONATION
FROM
-- additional donation made by team members
(SELECT TFT.ID, SUM(RS.AMOUNT) AS AMOUNT
FROM dbo.TEAMFUNDRAISINGTEAMMEMBER TM
JOIN dbo.TEAMFUNDRAISINGTEAM TFT ON TM.TEAMFUNDRAISINGTEAMID = TFT.ID
--JOIN dbo.TEAMFUNDRAISINGTEAM TFT2 ON TFT.ID= TFT2.PARENTTEAMID
JOIN dbo.TEAMFUNDRAISER TF ON TM.TEAMFUNDRAISERID = TF.ID
JOIN dbo.REGISTRANT R ON TF.CONSTITUENTID = R.CONSTITUENTID
JOIN dbo.EVENTEXTENSION EE ON R.EVENTID = EE.EVENTID
JOIN dbo.REVENUE RV ON RV.CONSTITUENTID = R.CONSTITUENTID
JOIN dbo.REVENUESPLIT RS ON RS.REVENUEID = RV.ID
WHERE RS.TYPECODE = 0
AND RV.TRANSACTIONTYPECODE = 0
AND TFT.ID IN (SELECT TEAMFUNDRAISINGTEAMID FROM #TEMP_Get_TFTID)
GROUP BY TFT.ID
UNION ALL
-- donations collected by team members
SELECT TFT.ID, SUM(RS.AMOUNT) AS AMOUNT
FROM dbo.TEAMFUNDRAISINGTEAMMEMBER TM
JOIN dbo.TEAMFUNDRAISINGTEAM TFT ON TM.TEAMFUNDRAISINGTEAMID = TFT.ID
--JOIN dbo.TEAMFUNDRAISINGTEAM TFT2 ON TFT.ID= TFT2.PARENTTEAMID
JOIN dbo.TEAMFUNDRAISER TF ON TM.TEAMFUNDRAISERID = TF.ID
JOIN dbo.REGISTRANT R ON TF.CONSTITUENTID = R.CONSTITUENTID
JOIN dbo.EVENTEXTENSION EE ON R.EVENTID = EE.EVENTID
JOIN dbo.REVENUERECOGNITION RR ON RR.CONSTITUENTID = R.CONSTITUENTID
JOIN dbo.REVENUESPLIT RS ON RS.ID = RR.REVENUESPLITID
JOIN dbo.REVENUE RV ON RV.ID = RS.REVENUEID
WHERE RS.TYPECODE = 0
AND RV.TRANSACTIONTYPECODE = 0 AND RV.CONSTITUENTID <> R.CONSTITUENTID
AND TFT.ID IN (SELECT TEAMFUNDRAISINGTEAMID FROM #TEMP_Get_TFTID)
GROUP BY TFT.ID) TOT
JOIN TEAMEXTENSION TE ON TOT.ID= TE.TEAMFUNDRAISINGTEAMID --AND TE.TYPECODE= @TYPECODE
AND TE.DATEADDED BETWEEN CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,-1,getdate()))-1),DATEADD(mm,-1,getdate())),101)
AND CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(getdate())),getdate()),101)
GROUP BY TOT.ID
ORDER BY TOT.ID
END
ELSE
BEGIN
IF @WEEKORMONTHCODE= 1
SELECT TOT.ID, SUM(TOT.AMOUNT) AS DONATION
FROM
-- additional donation made by team members
(SELECT TFT.ID, SUM(RS.AMOUNT) AS AMOUNT
FROM dbo.TEAMFUNDRAISINGTEAMMEMBER TM
JOIN dbo.TEAMFUNDRAISINGTEAM TFT ON TM.TEAMFUNDRAISINGTEAMID = TFT.ID
JOIN dbo.TEAMFUNDRAISER TF ON TM.TEAMFUNDRAISERID = TF.ID
JOIN dbo.REGISTRANT R ON TF.CONSTITUENTID = R.CONSTITUENTID
JOIN dbo.EVENTEXTENSION EE ON R.EVENTID = EE.EVENTID
JOIN dbo.REVENUE RV ON RV.CONSTITUENTID = R.CONSTITUENTID
JOIN dbo.REVENUESPLIT RS ON RS.REVENUEID = RV.ID
WHERE RS.TYPECODE = 0
AND RV.TRANSACTIONTYPECODE = 0
GROUP BY TFT.ID
UNION ALL
-- donations collected by team members
SELECT TFT.ID, SUM(RS.AMOUNT) AS AMOUNT
FROM dbo.TEAMFUNDRAISINGTEAMMEMBER TM
JOIN dbo.TEAMFUNDRAISINGTEAM TFT ON TM.TEAMFUNDRAISINGTEAMID = TFT.ID
JOIN dbo.TEAMFUNDRAISER TF ON TM.TEAMFUNDRAISERID = TF.ID
JOIN dbo.REGISTRANT R ON TF.CONSTITUENTID = R.CONSTITUENTID
JOIN dbo.EVENTEXTENSION EE ON R.EVENTID = EE.EVENTID
JOIN dbo.REVENUERECOGNITION RR ON RR.CONSTITUENTID = R.CONSTITUENTID
JOIN dbo.REVENUESPLIT RS ON RS.ID = RR.REVENUESPLITID
JOIN dbo.REVENUE RV ON RV.ID = RS.REVENUEID
WHERE RS.TYPECODE = 0
AND RV.TRANSACTIONTYPECODE = 0 AND RV.CONSTITUENTID <> R.CONSTITUENTID
GROUP BY TFT.ID) TOT
JOIN TEAMEXTENSION TE ON TOT.ID= TE.TEAMFUNDRAISINGTEAMID AND TE.TYPECODE= @TYPECODE
AND TE.DATEADDED <= getdate() -7
GROUP BY TOT.ID
ORDER BY TOT.ID
ELSE
SELECT TOT.ID, SUM(TOT.AMOUNT) AS DONATION
FROM
-- additional donation made by team members
(SELECT TFT.ID, SUM(RS.AMOUNT) AS AMOUNT
FROM dbo.TEAMFUNDRAISINGTEAMMEMBER TM
JOIN dbo.TEAMFUNDRAISINGTEAM TFT ON TM.TEAMFUNDRAISINGTEAMID = TFT.ID
JOIN dbo.TEAMFUNDRAISER TF ON TM.TEAMFUNDRAISERID = TF.ID
JOIN dbo.REGISTRANT R ON TF.CONSTITUENTID = R.CONSTITUENTID
JOIN dbo.EVENTEXTENSION EE ON R.EVENTID = EE.EVENTID
JOIN dbo.REVENUE RV ON RV.CONSTITUENTID = R.CONSTITUENTID
JOIN dbo.REVENUESPLIT RS ON RS.REVENUEID = RV.ID
WHERE RS.TYPECODE = 0
AND RV.TRANSACTIONTYPECODE = 0
GROUP BY TFT.ID
UNION ALL
-- donations collected by team members
SELECT TFT.ID, SUM(RS.AMOUNT) AS AMOUNT
FROM dbo.TEAMFUNDRAISINGTEAMMEMBER TM
JOIN dbo.TEAMFUNDRAISINGTEAM TFT ON TM.TEAMFUNDRAISINGTEAMID = TFT.ID
JOIN dbo.TEAMFUNDRAISER TF ON TM.TEAMFUNDRAISERID = TF.ID
JOIN dbo.REGISTRANT R ON TF.CONSTITUENTID = R.CONSTITUENTID
JOIN dbo.EVENTEXTENSION EE ON R.EVENTID = EE.EVENTID
JOIN dbo.REVENUERECOGNITION RR ON RR.CONSTITUENTID = R.CONSTITUENTID
JOIN dbo.REVENUESPLIT RS ON RS.ID = RR.REVENUESPLITID
JOIN dbo.REVENUE RV ON RV.ID = RS.REVENUEID
WHERE RS.TYPECODE = 0
AND RV.TRANSACTIONTYPECODE = 0 AND RV.CONSTITUENTID <> R.CONSTITUENTID
GROUP BY TFT.ID) TOT
JOIN TEAMEXTENSION TE ON TOT.ID= TE.TEAMFUNDRAISINGTEAMID AND TE.TYPECODE= @TYPECODE
AND TE.DATEADDED BETWEEN CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,-1,getdate()))-1),DATEADD(mm,-1,getdate())),101)
AND CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(getdate())),getdate()),101)
GROUP BY TOT.ID
ORDER BY TOT.ID
END