USP_DATALIST_GETTEAMMEMBERS

CREATE procedure dbo.USP_DATALIST_GETTEAMMEMBERS
(
    @IWTEAMID        uniqueidentifier = NULL,
    @IWPARENTTEAMID uniqueidentifier = NULL,
    @EVENTID        uniqueidentifier = null,
    @RECORDTYPECODE tinyint = 0
)
as
begin
    set nocount on;

    --Pull list of team members or household members for that teamid

    if (@IWTEAMID IS NOT NULL AND @IWPARENTTEAMID IS NULL AND @EVENTID IS NOT NULL AND @RECORDTYPECODE <> 0)
    begin
        select 
            R.ID AS REGISTRANTID
            ,ISNULL(dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL(R.ID,R.EVENTID),0) AS DONATIONAMOUNT
            ,dbo.UFN_REGISTRANT_GETNAME(R.ID) AS TEAMMEMBERNAME
            ,R.CONSTITUENTID AS CONSTITUENTID
            , R.EVENTID
            , TFT.NAME AS TEAMNAME
            , TFT.ID AS TEAMID
            , TFT.PARENTTEAMID
            , 0 AS ROLECODE
            , RE.ISPRIVATE
            , dbo.UFN_FAFEVENT_GETPARTICIPANTDONORSCOUNT(@EVENTID, R.CONSTITUENTID) AS NUMOFDONORS
            , (CASE WHEN R.CONSTITUENTID=(SELECT DISTINCT TFTC.CONSTITUENTID FROM TEAMFUNDRAISINGTEAMCAPTAIN TFTC WHERE TFTC.TEAMFUNDRAISINGTEAMID=@IWTEAMID AND TFTC.CONSTITUENTID=R.CONSTITUENTID)  THEN 1 ELSE 0 END) AS ISGROUPLEADER            
        FROM    REGISTRANT R 
        LEFT    JOIN TEAMFUNDRAISER TFR ON R.CONSTITUENTID = TFR.CONSTITUENTID
        LEFT    JOIN REGISTRANTEXTENSION RE ON R.ID=RE.REGISTRANTID
        LEFT    JOIN TEAMFUNDRAISINGTEAMMEMBER TFTM ON TFR.ID = TFTM.TEAMFUNDRAISERID
        LEFT    JOIN TEAMFUNDRAISINGTEAM TFT ON TFTM.TEAMFUNDRAISINGTEAMID = TFT.ID
        WHERE    TFT.ID=@IWTEAMID
        AND        R.EVENTID=@EVENTID
        ORDER BY R.DATEADDED
    END
     --Pull list of teams or households for that paranteamid (per company)

    ELSE IF (@IWPARENTTEAMID IS NOT NULL AND @IWTEAMID IS NULL AND @EVENTID IS NOT NULL AND @RECORDTYPECODE <> 0)
    BEGIN
        SELECT 
             NULL AS REGISTRANTID
            --,(select AMOUNTRAISED from UFN_FAF_GROUPTOTALSROLLUP(TE.EVENTID, TFT.ID)) AS DONATIONAMOUNT

      , (select TOTALFUNDRAISE from dbo.FAFGROUPSUMMARYINFORMATION where ID = TFT.ID and EVENTID = TE.EVENTID) As DONATIONAMOUNT
            ,NULL AS TEAMMEMBERNAME
            ,TE.TEAMCONSTITUENTID AS CONSTITUENTID
            ,TE.EVENTID
            , TFT.NAME AS TEAMNAME
            , TFT.ID AS TEAMID
            , TFT.PARENTTEAMID
            , TE.TYPECODE AS ROLECODE
            , dbo.UFN_FAFEVENT_GETGROUPPAGEPRIVACYFROMGROUPLEADER(TFT.ID,3) AS ISPRIVATE
            ,dbo.UFN_FAFEVENT_GETPARTICIPANTDONORSCOUNT(@EVENTID, TE.TEAMCONSTITUENTID) AS NUMOFDONORS
            , 0 AS ISGROUPLEADER
        FROM    TEAMFUNDRAISINGTEAM TFT INNER JOIN TEAMEXTENSION TE ON TFT.ID = TE.TEAMFUNDRAISINGTEAMID
        WHERE    TFT.PARENTTEAMID=@IWPARENTTEAMID AND TE.STATUS <> 'pending'
        AND        TE.EVENTID=@EVENTID
    END
    --Pull list of donors whom do general donation to team/household/company

    -- Added rollup per registrant so we avoid duplicate names in the donors

    -- list in the widget.

    ELSE IF (@IWTEAMID IS NOT NULL AND @IWPARENTTEAMID IS NULL AND @EVENTID IS NOT NULL AND @RECORDTYPECODE = 0)  
    BEGIN
        declare @TEAMCONSTITUENTID uniqueidentifier = null
        selecT    @TEAMCONSTITUENTID = TEAMCONSTITUENTID 
        from    dbo.TEAMEXTENSION (nolock) 
        where    TEAMFUNDRAISINGTEAMID = @IWTEAMID

        select 
             NULL AS REGISTRANTID
            ,sum(isnull(EVENT_REVENUE.[ORIGINAL AMOUNT],0)) AS DONATIONAMOUNT
            ,C.NAME AS TEAMMEMBERNAME
            ,C.ID AS CONSTITUENTID
            ,@EVENTID AS EVENTID
            ,NULL AS TEAMNAME
            ,@IWTEAMID AS TEAMID
            ,NULL AS PARENTTEAMID
            ,@RECORDTYPECODE AS ROLECODE
            ,0 AS ISPRIVATE
            ,dbo.UFN_FAFEVENT_GETPARTICIPANTDONORSCOUNT(@EVENTID, @TEAMCONSTITUENTID) AS NUMOFDONORS
            , 0 AS ISGROUPLEADER
        from        [dbo].[UFN_REVENUE_EVENT](@EVENTID) AS EVENT_REVENUE  
        inner join    REVENUERECOGNITION 
            on        REVENUERECOGNITION.CONSTITUENTID <> EVENT_REVENUE.CONSTITUENTID 
            and        REVENUERECOGNITION.REVENUESPLITID = EVENT_REVENUE.REVENUESPLITID  
        inner join    REVENUESPLIT RS (nolock) 
            on        RS.ID=EVENT_REVENUE.REVENUESPLITID
        inner join    REVENUE R (nolock) 
            on        R.ID=RS.REVENUEID
        inner join    CONSTITUENT C (nolock) 
            on        C.ID=R.CONSTITUENTID
        where        REVENUERECOGNITION.CONSTITUENTID = @TEAMCONSTITUENTID 
        group        by  
             C.NAME 
            ,C.ID  
    end
end