USP_DATALIST_GIVING_HISTORY

CREATE procedure dbo.USP_DATALIST_GIVING_HISTORY
    ( @EVENTID uniqueidentifier,
    @ADDRESSBOOKFAFID uniqueidentifier
      --, @CLIENTUSERSID integer

    )WITH EXECUTE AS CALLER
    as

    BEGIN
    SET NOCOUNT ON;

  DECLARE @CONSTITUENTID uniqueidentifier, @DONORCONSTITUENTID uniqueidentifier, @CLIENTUSERSID integer, @PRIORYEAREVENTID uniqueidentifier

  SELECT @DONORCONSTITUENTID=  CONSTITUENTID, @CLIENTUSERSID= CLIENTUSERSID FROM ADDRESSBOOKFAF    WHERE ID= @ADDRESSBOOKFAFID
  SELECT @CONSTITUENTID= dbo.UFN_CONSTITUENT_GET_BY_CLIENTUSERID(@CLIENTUSERSID)
  SET @PRIORYEAREVENTID= (select top 1 PRIORYEAREVENTID FROM EVENTEXTENSION WHERE EVENTID= @EVENTID)

  DECLARE    @GROUPCONSTITUENTID uniqueidentifier, @GROUPTYPE varchar(9)
  ----

  SELECT    @GROUPCONSTITUENTID= TEAMCONSTITUENTID, @GROUPTYPE= TEAMEXTENSION.TYPE
  FROM        TEAMEXTENSION                            (NOLOCK)
  JOIN        TEAMFUNDRAISINGTEAM                (NOLOCK)    ON        TEAMFUNDRAISINGTEAM.ID    = [TEAMEXTENSION].TEAMFUNDRAISINGTEAMID
  JOIN        TEAMFUNDRAISINGTEAMMEMBER    (NOLOCK)    ON        TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID = TEAMFUNDRAISINGTEAM.ID
  JOIN        TEAMFUNDRAISER                        (NOLOCK)    ON        TEAMFUNDRAISER.ID = TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISERID
  WHERE        TEAMFUNDRAISER.CONSTITUENTID    = @CONSTITUENTID AND TEAMEXTENSION.EVENTID= @EVENTID
  --------------------------------------------------


  SELECT         DISTINCT A.FIRSTNAME, A.LASTNAME, DL.DATEADDED AS DATE, DL.CURRENTDONATIONAMOUNT AS AMOUNT, E.ID AS EVENTID, E.NAME + ' (Current)' AS EVENTNAME, TFT.NAME AS TEAMNAME
                   --, CASE WHEN TE.TYPECODE= 1 THEN 'TEAM'     WHEN TE.TYPECODE= 2 THEN 'COMPANY' WHEN TE.TYPECODE= 3 THEN 'HOUSEHOLD' ELSE 'INDIVIDUAL' END as GROUPTYPE

               , CASE WHEN FT1.DONORID IS NOT NULL THEN 'Personal' WHEN FT2.DONORID IS NOT NULL THEN @GROUPTYPE END as GROUPTYPE         
                     --, '' as SEP, A.ID AS ADDRESSBOOKFAFID, A.CLIENTUSERSID, REG.ID AS REGISTRANTID, A.CONSTITUENTID


  FROM              dbo.ADDRESSBOOKFAF A                    (NOLOCK) 
  LEFT JOIN        dbo.REGISTRANT REG                        (NOLOCK)     ON    A.CONSTITUENTID = REG.CONSTITUENTID
  LEFT JOIN        dbo.CONSTITUENT C                            (NOLOCK)    ON    C.ID =REG.CONSTITUENTID 

  LEFT JOIN        dbo.UFN_FAFADDRESSBOOK_DONORS_LIST    (@EVENTID, @CLIENTUSERSID)  DL  ON A.CONSTITUENTID= DL.CONSTITUENTID    
  LEFT JOIN        dbo.UFN_FAFEVENT_TRANSACTIONS          (@EVENTID)  FT1  ON A.CONSTITUENTID= FT1.DONORID    AND FT1.RECEIVERID= @CONSTITUENTID        
  LEFT JOIN        dbo.UFN_FAFEVENT_TRANSACTIONS          (@EVENTID)  FT2  ON A.CONSTITUENTID= FT2.DONORID    AND FT2.RECEIVERID= @GROUPCONSTITUENTID    

  LEFT JOIN        dbo.EVENT E                                        (NOLOCK) ON    DL.APPEALID = E.APPEALID
  LEFT JOIN        dbo.TEAMFUNDRAISER TFR                            (NOLOCK) ON TFR.CONSTITUENTID = @CONSTITUENTID
  LEFT JOIN        dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM    (NOLOCK) ON TFR.ID = TFTM.TEAMFUNDRAISERID 
  LEFT JOIN        dbo.TEAMFUNDRAISINGTEAM TFT                    (NOLOCK) ON TFTM.TEAMFUNDRAISINGTEAMID = TFT.ID 
  LEFT JOIN        dbo.TEAMEXTENSION TE                                (NOLOCK) ON TFTM.TEAMFUNDRAISINGTEAMID = TE.TEAMFUNDRAISINGTEAMID 

  WHERE            E.ID= @EVENTID
  AND                A.ID= @ADDRESSBOOKFAFID

  UNION  ALL

  SELECT         DISTINCT A.FIRSTNAME, A.LASTNAME, DL.DATEADDED AS DATE, DL.CURRENTDONATIONAMOUNT AS AMOUNT, E.ID AS EVENTID, E.NAME AS EVENTNAME, TFT.NAME AS TEAMNAME
                   --, CASE WHEN TE.TYPECODE= 1 THEN 'TEAM'     WHEN TE.TYPECODE= 2 THEN 'COMPANY' WHEN TE.TYPECODE= 3 THEN 'HOUSEHOLD' ELSE 'INDIVIDUAL' END as GROUPTYPE

                          , CASE WHEN FT1.DONORID IS NOT NULL THEN 'Personal' WHEN FT2.DONORID IS NOT NULL THEN @GROUPTYPE END as GROUPTYPE
             --, '' as SEP, A.ID AS ADDRESSBOOKFAFID, A.CLIENTUSERSID, REG.ID AS REGISTRANTID, A.CONSTITUENTID


  FROM              dbo.ADDRESSBOOKFAF A                    (NOLOCK) 
  LEFT JOIN        dbo.REGISTRANT REG                        (NOLOCK)     ON    A.CONSTITUENTID = REG.CONSTITUENTID
  LEFT JOIN        dbo.CONSTITUENT C                            (NOLOCK)    ON    C.ID =REG.CONSTITUENTID 
  LEFT JOIN        dbo.UFN_FAFADDRESSBOOK_DONORS_LIST  (@PRIORYEAREVENTID, @CLIENTUSERSID)  DL  ON A.CONSTITUENTID= DL.CONSTITUENTID    
  LEFT JOIN        dbo.UFN_FAFEVENT_TRANSACTIONS          (@PRIORYEAREVENTID)  FT1  ON A.CONSTITUENTID= FT1.DONORID    AND FT1.RECEIVERID= @CONSTITUENTID        
  LEFT JOIN        dbo.UFN_FAFEVENT_TRANSACTIONS          (@PRIORYEAREVENTID)  FT2  ON A.CONSTITUENTID= FT2.DONORID    AND FT2.RECEIVERID= @GROUPCONSTITUENTID    

  LEFT JOIN        dbo.EVENT E                                        (NOLOCK) ON    DL.APPEALID = E.APPEALID
  LEFT JOIN        dbo.TEAMFUNDRAISER TFR                            (NOLOCK) ON TFR.CONSTITUENTID = @CONSTITUENTID
  LEFT JOIN        dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM    (NOLOCK) ON TFR.ID = TFTM.TEAMFUNDRAISERID 
  LEFT JOIN        dbo.TEAMFUNDRAISINGTEAM TFT                    (NOLOCK) ON TFTM.TEAMFUNDRAISINGTEAMID = TFT.ID 
  LEFT JOIN        dbo.TEAMEXTENSION TE                                (NOLOCK) ON TFTM.TEAMFUNDRAISINGTEAMID = TE.TEAMFUNDRAISINGTEAMID 

  WHERE       E.ID  IN (SELECT PRIORYEAREVENTID FROM EVENTEXTENSION WHERE EVENTID= @EVENTID)
  AND                A.ID=@ADDRESSBOOKFAFID
  ORDER BY       DL.DATEADDED DESC
    END