USP_DATALIST_FAFEVENTIINFORMATIONWIDGET

CREATE procedure dbo.USP_DATALIST_FAFEVENTIINFORMATIONWIDGET
(
     @IWREGISTRANTID    uniqueidentifier = NULL 
    ,@IWTEAMID            uniqueidentifier = NULL 
    ,@IWRECORDTYPE        tinyint             = 0  -- 1:Team, 3:Household, 2:Company  

    ,@EVENTID            uniqueidentifier = NULL 
)
as
begin
    set nocount on ;

    declare @IWCONSTITUENTID  uniqueidentifier  
    declare @PARTICIPANTNAME  nvarchar(200)  
    declare @NUMOFDONOR    integer  
    declare @TOTALDONATION   money  
    declare @TEAMNAME    nvarchar(200)  
    declare @NUMOFCOMPANYMEMBER  integer  
    declare @NUMOFTEAM    integer  
    declare @NUMOFHOUSEHOLD   integer  
    declare @NUMOFNOTEAMINDIVIDUAL integer  
    declare @NUMOFTEAMMEMBER  integer  
    declare @NUMOFHOUSEHOLDMEMBER  integer  

    declare @REGISTRANTRAISED  money  
    declare @TEAMRAISED    money  
    declare @COMPANIESRAISED  money  
    declare @TEAMDONATION   money  
    declare @REGISTRANTDONATION  money  

    declare @HISTORYEVENTPARTICIPATED integer = 0  
    declare @HISTORYMEMBER   integer = 0  
    declare @HISTORYNUMOFTEAM  integer = 0  
    declare @HISTORYNUMOFDONOR  integer = 0  
    declare @HISTORYTOTALDONATION money = 0  
    declare @RECORDTYPECODE   tinyint = 0  
    declare @PARENTTEAMID   uniqueidentifier = null  
    declare @PARENTTEAMNAME   nvarchar(200)  
    declare @PARENTTEAMTYPECODE tinyint = 0 

    declare @PRIORYEAREVENTID  uniqueidentifier = null  
    declare @PRIORTEAMID            uniqueidentifier  

    select @PRIORYEAREVENTID = PRIORYEAREVENTID  
    from dbo.EVENTEXTENSION (nolock) where EVENTID = @EVENTID;  

 --Join as team participant page    

    IF ( @IWRECORDTYPE = 0 AND @IWREGISTRANTID IS NOT NULL )  
    begin  
        select  
            @PARTICIPANTNAME = isnull(C.FIRSTNAME + ' ' , '') + C.KEYNAME ,  
            @IWCONSTITUENTID = R.CONSTITUENTID  
        from dbo.REGISTRANT R (nolock)  
        LEFT JOIN dbo.CONSTITUENT C (nolock) ON R.CONSTITUENTID = C.ID  
        where R.ID = @IWREGISTRANTID;  

  -- to support pledge recognition  

        select @NUMOFDONOR=DONORCOUNT, @TOTALDONATION=AMOUNTRAISED from dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL_2(@EVENTID) where CONSTITUENTID=@IWCONSTITUENTID 

        -- History: numbers are calculated base on previous linked event (only one) which this CONSTITUENT joined    

        if ( @PRIORYEAREVENTID IS NOT NULL )  
        begin  
            select @HISTORYNUMOFDONOR=DONORCOUNT, @HISTORYTOTALDONATION=AMOUNTRAISED from dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL_2(@PRIORYEAREVENTID) where CONSTITUENTID=@IWCONSTITUENTID 

            select @HISTORYEVENTPARTICIPATED = count(*)            -- 1 or 0  

            FROM REGISTRANT WHERE EVENTID = @PRIORYEAREVENTID AND CONSTITUENTID = @IWCONSTITUENTID  

            if (@IWTEAMID IS NOT NULL)  
                select @HISTORYMEMBER = MEMBERCOUNT from dbo.UFN_FAF_GROUPTOTALSROLLUP(@PRIORYEAREVENTID, @IWTEAMID)  
        end  

        if (@IWTEAMID IS NOT NULL)  
        begin  
      --GET TEAM NAME INFO FROM ONE REGISTRANT, WE ACUTALLY DON'T NEED TO GET PARA IWTEAMID. WE can get teamid from registranid    

            select  
                @TEAMNAME  = isnull ( TFT.NAME , '' ) ,  
                @IWTEAMID  = TFT.ID ,  
                @PARENTTEAMID = TFT.PARENTTEAMID ,  
                @RECORDTYPECODE = isnull ( TE.TYPECODE , 1 ) ,  
                @PARENTTEAMNAME = TFT2.NAME  
            from REGISTRANT R (nolock)  
            left join TEAMFUNDRAISER TFR (nolock) ON R.CONSTITUENTID = TFR.CONSTITUENTID  
            left join TEAMFUNDRAISINGTEAMMEMBER TFTM (nolock) ON TFR.ID = TFTM.TEAMFUNDRAISERID  
            left join TEAMFUNDRAISINGTEAM TFT (nolock) ON TFTM.TEAMFUNDRAISINGTEAMID = TFT.ID  
            left join TEAMEXTENSION TE (nolock) ON TFTM.TEAMFUNDRAISINGTEAMID = TE.TEAMFUNDRAISINGTEAMID  
           left join TEAMFUNDRAISINGTEAM TFT2 (nolock) ON TFT2.ID = TFT.PARENTTEAMID  
            where R.ID = @IWREGISTRANTID  
                AND R.EVENTID = @EVENTID AND TE.EVENTID = @EVENTID;  

      --GET NUM OF TEAMMEMBER    

            select @NUMOFTEAMMEMBER = COUNT(ID)  
            from dbo.TEAMFUNDRAISINGTEAMMEMBER (nolock) where TEAMFUNDRAISINGTEAMID = @IWTEAMID;  
        end  
    end  

    --Join as team/company/household participant page    

    else if ( @IWRECORDTYPE > 0 AND @IWREGISTRANTID IS NULL AND @IWTEAMID IS NOT NULL )  
    BEGIN  
        --GET TEAM NAME INFO FROM team record    

        select  
            @TEAMNAME = ISNULL ( TFT.NAME , '' ) ,  
            @IWTEAMID = TFT.ID ,  
            @PARENTTEAMID = TFT.PARENTTEAMID ,  
            @RECORDTYPECODE = ISNULL ( TE.TYPECODE , 1 ) ,  
            @IWCONSTITUENTID = TE.TEAMCONSTITUENTID ,  
            @PARENTTEAMNAME = TFT2.NAME  
        from TEAMFUNDRAISINGTEAM TFT (nolock)  
        inner join TEAMEXTENSION TE (nolock) ON TFT.ID = TE.TEAMFUNDRAISINGTEAMID  
        left join TEAMFUNDRAISINGTEAM TFT2 (nolock) ON TFT2.ID = TFT.PARENTTEAMID  
        where TFT.ID = @IWTEAMID AND TE.EVENTID = @EVENTID;  

        --GET NUM OF TEAMMEMBER  

        select @NUMOFTEAMMEMBER = MEMBERCOUNT from dbo.UFN_FAF_GROUPTOTALSROLLUP(@EVENTID, @IWTEAMID)  

        --Get TEAM CAPTAIN REGISTRANTID AND CONSTITUENTID    

        SELECT  @PARTICIPANTNAME = dbo.UFN_REGISTRANT_GETNAME ( R.ID ) ,  
                @IWREGISTRANTID = R.ID  
        FROM TEAMFUNDRAISINGTEAMCAPTAIN TFTC (nolock)  
        LEFT JOIN REGISTRANT R (nolock) ON TFTC.CONSTITUENTID = R.CONSTITUENTID  
        WHERE TFTC.TEAMFUNDRAISINGTEAMID = @IWTEAMID;  

        --select @NUMOFDONOR = DONORCOUNT, @TOTALDONATION = AMOUNTRAISED

        -- from dbo.UFN_FAF_GROUPTOTALSROLLUP(@EVENTID, @IWTEAMID);

        -- use new table FAFGROUPSUMMARYINFORMATION

        Select @NUMOFDONOR = TOTALDONORCOUNT, @TOTALDONATION = TOTALFUNDRAISE from dbo.FAFGROUPSUMMARYINFORMATION 
          where ID = @IWTEAMID and EVENTID = @EVENTID

        IF ( @PRIORYEAREVENTID IS NOT NULL )  
        begin  
            select @PRIORTEAMID = tft.ID  
            from TEAMFUNDRAISINGTEAM tft  
            left outer join  TEAMEXTENSION te ON tft.ID = te.TEAMFUNDRAISINGTEAMID  
            where te.TEAMCONSTITUENTID = @IWCONSTITUENTID and te.EVENTID = @PRIORYEAREVENTID;  

            SELECT @HISTORYEVENTPARTICIPATED = count(*)  
            FROM TEAMFUNDRAISINGTEAM tft1  
            left outer join TEAMEXTENSION te on tft1.ID = te.TEAMFUNDRAISINGTEAMID  
            left outer join TEAMEXTENSION te2 on te.TEAMCONSTITUENTID = te2.TEAMCONSTITUENTID   
            WHERE  tft1.ID = @IWTEAMID AND te2.EVENTID = @PRIORYEAREVENTID;  

            select @HISTORYNUMOFDONOR = DONORCOUNT, @HISTORYTOTALDONATION = AMOUNTRAISED, @HISTORYMEMBER = MEMBERCOUNT
            from UFN_FAF_GROUPTOTALSROLLUP(@PRIORYEAREVENTID, @PRIORTEAMID)

            IF ( @IWRECORDTYPE = 2 )    --company  

            BEGIN  
                --Only get teams not households  

                SELECT @HISTORYNUMOFTEAM = count(*)  
                FROM TEAMFUNDRAISINGTEAM tft  
                left outer join TEAMEXTENSION te on tft.ID = te.TEAMFUNDRAISINGTEAMID   
                WHERE tft.PARENTTEAMID = @PRIORTEAMID and te.TYPECODE = 1;  
            end  
        end  
    end  

    Select @PARENTTEAMTYPECODE = TYPECODE from TEAMEXTENSION where TEAMFUNDRAISINGTEAMID = @PARENTTEAMID

    --Return values or output fields

    select  
        @IWREGISTRANTID AS REGISTRANTID ,  
        @IWTEAMID AS TEAMID ,  
        @PARTICIPANTNAME AS PARTICIPANTNAME ,  
        isnull ( @NUMOFDONOR , 0 ) AS NUMOFDONOR ,  
        isnull ( @TOTALDONATION , 0 ) AS TOTALDONATION ,  
        isnull ( @NUMOFCOMPANYMEMBER , 0 ) AS NUMOFCOMPANYMEMBER ,  
        isnull ( @NUMOFTEAM , 0 ) AS NUMOFTEAM ,  
        isnull ( @NUMOFHOUSEHOLD , 0 ) AS NUMOFHOUSEHOLD ,  
        isnull ( @NUMOFNOTEAMINDIVIDUAL , 0 ) AS NUMOFNOTEAMINDIVIDUAL ,  
        isnull ( @NUMOFTEAMMEMBER , 0 ) AS NUMOFTEAMMEMBER ,  
        isnull ( @NUMOFHOUSEHOLDMEMBER , 0 ) AS NUMOFHOUSEHOLDMEMBER ,  
        isnull ( @HISTORYEVENTPARTICIPATED , 0 ) AS HISTORYEVENTPARTICIPATED ,  
        isnull ( @HISTORYMEMBER , 0 ) AS HISTORYMEMBER ,  
        isnull ( @HISTORYNUMOFTEAM , 0 ) AS HISTORYNUMOFTEAM ,  
        isnull ( @HISTORYNUMOFDONOR , 0 ) AS HISTORYNUMOFDONOR ,  
        isnull ( @HISTORYTOTALDONATION , 0 ) AS HISTORYTOTALDONATION ,  
        @TEAMNAME AS TEAMNAME ,  
        @RECORDTYPECODE AS RECORDTYPECODE ,  
        @PARENTTEAMNAME AS PARENTTEAMNAME ,  
        @PARENTTEAMID AS PARENTTEAMID ,  
        @IWCONSTITUENTID AS CONSTITUENTID ,  
        @EVENTID AS EVENTID,
        @PARENTTEAMTYPECODE As PARENTTEAMTYPECODE;  
end