USP_DATALIST_COMMUNICATIONHEADER

Pull info for a BBNC user in Communication section base on an event he/she joined
Parameters

Parameter	Parameter Type	Mode	Description
@ID	int	IN	SEQUENCEID
@EVENTID	uniqueidentifier	IN	eventid
Definition

Copy


CREATE procedure dbo.USP_DATALIST_COMMUNICATIONHEADER
(
@ID integer,
@EVENTID uniqueidentifier = NULL
)
as
    set nocount on;
    --tphan 06/08/2010: 

    --Input: ID is CONSTITUENT.SEQUENCEID, EVENTID (the datalist spec allow to pass multi input para)

    --Output: Return only one record with info as REGISTRANTID, CONSTITUENTID, TYPECODE (ROLE), TEAMNAME, TEAMID, PARENTTEAMID, Userlogin FULLNAME, Primary EMAILADDRESS, STATUS, EVENTID


    --SELECT        R.ID AS REGISTRANTID, R.CONSTITUENTID, ISNULL(TE.TYPECODE,0) AS ROLE, TFT.NAME AS TEAMNAME, TFT.ID AS TEAMID, TFT.PARENTTEAMID, dbo.UFN_REGISTRANT_GETNAME(R.ID) as FULLNAME, E.EMAILADDRESS AS EMAILADDRESS, 0 AS STATUS

    If @EVENTID IS NULL
        Begin    
            SELECT        R.ID AS REGISTRANTID, R.CONSTITUENTID, ISNULL(TE.TYPECODE,0) AS TYPECODE, TFT.NAME AS TEAMNAME, 
                        TFT.ID AS TEAMID, TFT.PARENTTEAMID, dbo.UFN_REGISTRANT_GETNAME(R.ID) as FULLNAME, 
                        E.EMAILADDRESS AS EMAILADDRESS, 0 AS STATUS, R.EVENTID

            FROM dbo.CONSTITUENT C 
            INNER JOIN  dbo.REGISTRANT R ON C.ID=R.CONSTITUENTID
            LEFT JOIN   dbo.EMAILADDRESS E ON (R.CONSTITUENTID = E.CONSTITUENTID AND E.ISPRIMARY = 1)
            LEFT JOIN    dbo.TEAMFUNDRAISER TFR ON R.CONSTITUENTID = TFR.CONSTITUENTID
            LEFT JOIN    dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM ON TFR.ID = TFTM.TEAMFUNDRAISERID
            LEFT JOIN    dbo.TEAMFUNDRAISINGTEAM TFT ON TFTM.TEAMFUNDRAISINGTEAMID = TFT.ID
            LEFT JOIN    dbo.TEAMEXTENSION TE ON TFTM.TEAMFUNDRAISINGTEAMID = TE.TEAMFUNDRAISINGTEAMID
            WHERE        C.SEQUENCEID=@ID
        End
    Else
        Begin
            SELECT        R.ID AS REGISTRANTID, R.CONSTITUENTID, ISNULL(TE.TYPECODE,0) AS TYPECODE, TFT.NAME AS TEAMNAME, 
                        TFT.ID AS TEAMID, TFT.PARENTTEAMID, dbo.UFN_REGISTRANT_GETNAME(R.ID) as FULLNAME, 
                        E.EMAILADDRESS AS EMAILADDRESS, 0 AS STATUS, R.EVENTID

            FROM dbo.CONSTITUENT C 
            INNER JOIN  dbo.REGISTRANT R ON C.ID=R.CONSTITUENTID
            LEFT JOIN   dbo.EMAILADDRESS E ON (R.CONSTITUENTID = E.CONSTITUENTID AND E.ISPRIMARY = 1)
            LEFT JOIN    dbo.TEAMFUNDRAISER TFR ON R.CONSTITUENTID = TFR.CONSTITUENTID
            LEFT JOIN    dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM ON TFR.ID = TFTM.TEAMFUNDRAISERID
            LEFT JOIN    dbo.TEAMFUNDRAISINGTEAM TFT ON TFTM.TEAMFUNDRAISINGTEAMID = TFT.ID
            LEFT JOIN    dbo.TEAMEXTENSION TE ON TFTM.TEAMFUNDRAISINGTEAMID = TE.TEAMFUNDRAISINGTEAMID
            WHERE        C.SEQUENCEID=@ID
            AND            R.EVENTID=@EVENTID
        End