USP_DATALIST_PARTICIPATION_HISTORY

CREATE PROCEDURE dbo.USP_DATALIST_PARTICIPATION_HISTORY 
        ( @EVENTID uniqueidentifier,
          @ADDRESSBOOKFAFID uniqueidentifier,
          @CLIENTUSERSID integer
        )WITH EXECUTE AS CALLER
        as


        BEGIN
        SET NOCOUNT ON;

    DECLARE    @EVENTID_PREV uniqueidentifier, @EVENTID_PREV2 uniqueidentifier, @SEQUENCEID int, @REGISTRANTCONSTITUENTID uniqueidentifier
    SET @REGISTRANTCONSTITUENTID = dbo.UFN_CONSTITUENT_GET_BY_CLIENTUSERID(@CLIENTUSERSID)
    DECLARE @REGISTRANTTEAMID uniqueidentifier
    SET @REGISTRANTTEAMID = (select TFT.ID from CONSTITUENT C
                LEFT JOIN   dbo.TEAMFUNDRAISER TFR (NOLOCK) ON C.ID = TFR.CONSTITUENTID   
                LEFT JOIN   dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM (NOLOCK) ON TFR.ID = TFTM.TEAMFUNDRAISERID   
                LEFT JOIN   dbo.TEAMFUNDRAISINGTEAM TFT (NOLOCK) ON TFTM.TEAMFUNDRAISINGTEAMID = TFT.ID AND TFR.APPEALID= TFT.APPEALID  
                LEFT JOIN   dbo.EVENT E (NOLOCK) ON TFR.APPEALID = E.APPEALID  
                where C.ID = @REGISTRANTCONSTITUENTID
                AND E.ID = @EVENTID)

    SELECT    @EVENTID_PREV=    PRIORYEAREVENTID FROM EVENTEXTENSION WHERE EVENTID= @EVENTID;
    SELECT    @EVENTID_PREV2=    PRIORYEAREVENTID FROM EVENTEXTENSION WHERE EVENTID= @EVENTID_PREV;

    ----------------ALT WAY TO GET ROLES--------------------------

    --SELECT @SEQUENCEID= SEQUENCEID FROM CONSTITUENT WHERE ID IN (SELECT CONSTITUENTID FROM ADDRESSBOOKFAF WHERE ID= @ADDRESSBOOKFAFID);


    --DECLARE @SYSTEM_ROLES varchar (100), @SYSTEM_ROLES_PREV varchar (100);

    --DECLARE @GETSYSTEMROLES             TABLE (ID INT NOT NULL, ROLEGUID UNIQUEIDENTIFIER, NAME VARCHAR(100), HASROLE BIT)

    ------

    --INSERT  INTO @GETSYSTEMROLES        (ID, ROLEGUID, NAME, HASROLE) 

    --EXEC    USP_DATALIST_GETSYSTEMROLES @EVENTID=@EVENTID, @SEQUENCEID=@SEQUENCEID, @CLIENTUSERSID= @CLIENTUSERSID

    --------------


    --DECLARE @GETSYSTEMROLES_PREV TABLE (ID INT NOT NULL, ROLEGUID UNIQUEIDENTIFIER, NAME VARCHAR(100), HASROLE BIT)

    ------

    --INSERT  INTO @GETSYSTEMROLES_PREV (ID, ROLEGUID, NAME, HASROLE) 

    --EXEC    USP_DATALIST_GETSYSTEMROLES @EVENTID=@EVENTID_PREV, @SEQUENCEID=@SEQUENCEID, @CLIENTUSERSID= @CLIENTUSERSID

    -------------


    --SELECT @SYSTEM_ROLES=      ISNULL(@SYSTEM_ROLES, '')      + SUBSTRING(NAME,4, LEN(NAME)) + ', '    FROM @GETSYSTEMROLES      WHERE HASROLE= 1;    

    --SELECT @SYSTEM_ROLES_PREV= ISNULL(@SYSTEM_ROLES_PREV, '') + SUBSTRING(NAME,4, LEN(NAME)) + ', '    FROM @GETSYSTEMROLES_PREV WHERE HASROLE= 1;

    ----------------ALT WAY TO GET ROLES--------------------------


    --AJ: can use UFN_FUNDRAISINGGROUP_MEMBERSNUMBER instead of View, if needed

    WITH    GroupFacts            as ( select distinct ID, MEMBERNUMBER, eventid  from [V_QUERY_REGISTRANT_FUNDRAISINGGROUP] where EVENTID =@EVENTID )
        , PriorGroupFacts   as ( select distinct ID, MEMBERNUMBER, eventid  from [V_QUERY_REGISTRANT_FUNDRAISINGGROUP] where EVENTID = @EVENTID_PREV )
        , PriorGroupFacts2  as ( select distinct ID, MEMBERNUMBER, eventid  from [V_QUERY_REGISTRANT_FUNDRAISINGGROUP] where EVENTID = @EVENTID_PREV2 )

    SELECT  DISTINCT A.FIRSTNAME, A.LASTNAME, E.ID AS EVENTID, E.NAME AS EVENTNAME, E.STARTDATE AS EVENTSTARTDATE    

              --, 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 TFR.CONSTITUENTID  IS NOT NULL THEN 'MEMBER' ELSE 'PARTICIPANT' END AS CATEGORYTYPE


            --, (SELECT UPPER(LEFT(@SYSTEM_ROLES, LEN(@SYSTEM_ROLES)-1))) as GROUPTYPE

            , (SELECT  TOP(1) UPPER(ROLE) from dbo.UFN_REGISTRANT_GETFAFROLE  (@EVENTID, null) 
                WHERE  CONSTITUENTID= (SELECT CONSTITUENTID FROM ADDRESSBOOKFAF WHERE ID= @ADDRESSBOOKFAFID)) as GROUPTYPE         
            , '' as CATEGORYTYPE

        , CASE WHEN TFR.CONSTITUENTID     IS NOT NULL THEN 'C' END AS EVENTINDICATOR
              , NULL AS CATEGORYLEADER
              , NULL AS GROUPAMOUNTRAISED        

              --, CASE WHEN TE.TYPECODE IN (1, 2) THEN  RX.FUNDRAISINGGOAL ELSE RX.TARGETFUNDRAISINGGOAL END AS FUNDRAISINGGOAL

            , RX.TARGETFUNDRAISINGGOAL AS FUNDRAISINGGOAL
                , dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL(REG.ID, E.ID) AS REGISTRANTAMOUNTRAISED  

                , H.TEAMNAME, H.COMPANYNAME, H.TEAMGOAL, H.COMPANYGOAL
                , CASE WHEN    dbo.UFN_REVENUE_GETTEAMRAISEDTOTAL(TGF.ID, TGF.EVENTID) <> 0 AND dbo.UFN_REVENUE_GETTEAMRAISEDTOTAL(TGFP.ID,TGF.EVENTID) <> 0 
                             THEN    dbo.UFN_REVENUE_GETTEAMRAISEDTOTAL(TGF.ID, TGF.EVENTID)/dbo.UFN_REVENUE_GETTEAMRAISEDTOTAL(TGFP.ID,TGF.EVENTID)                    
                   ELSE 0 END AS TEAMREVENUERETAINED

                , CASE WHEN    dbo.UFN_REVENUE_GETCOMPANYRAISEDTOTAL(CGF.ID,CGF.EVENTID) <> 0 AND dbo.UFN_REVENUE_GETCOMPANYRAISEDTOTAL(CGFP.ID, CGFP.EVENTID) <> 0             
                            THEN    dbo.UFN_REVENUE_GETCOMPANYRAISEDTOTAL(CGF.ID,CGF.EVENTID)/dbo.UFN_REVENUE_GETCOMPANYRAISEDTOTAL(CGFP.ID, CGFP.EVENTID)    
                   ELSE 0 END as COMPANYREVENUERETAINED

              , TGF.MEMBERNUMBER/TGFP.MEMBERNUMBER AS TEAMPARTICIPANTRETAINED         
                , CGF.MEMBERNUMBER/CGFP.MEMBERNUMBER AS COMPANYPARTICIPANTRETAINED     

            , CASE WHEN H.TEAMID      IS NOT NULL    THEN dbo.UFN_REVENUE_GETTEAMRAISEDTOTAL(H.TEAMID, E.ID)                      END AS TEAMRAISED
                  , CASE WHEN H.COMPANYID   IS NOT NULL    THEN dbo.UFN_REVENUE_GETCOMPANYRAISEDTOTAL(H.COMPANYID, E.ID)          END AS COMPANYRAISED
                  , CASE WHEN H.HouseholdID IS NOT NULL    THEN dbo.UFN_REVENUE_GETHOUSEHOLDRAISEDTOTAL(H.HouseholdID, E.ID)    END AS HOUSEHOLDRAISED 
            , H.HOUSEHOLDGOAL

            , H.HouseholdName
            , CASE WHEN    dbo.UFN_REVENUE_GETHOUSEHOLDRAISEDTOTAL(HGF.ID, HGF.EVENTID) <> 0 AND dbo.UFN_REVENUE_GETHOUSEHOLDRAISEDTOTAL(HGFP.ID,HGFP.EVENTID) <> 0 
                             THEN    dbo.UFN_REVENUE_GETHOUSEHOLDRAISEDTOTAL(HGF.ID, HGF.EVENTID)/dbo.UFN_REVENUE_GETHOUSEHOLDRAISEDTOTAL(HGFP.ID,HGFP.EVENTID)                    
                   ELSE 0 END AS HOUSEHOLDREVENUERETAINED
            ,HGF.MEMBERNUMBER/HGFP.MEMBERNUMBER AS HOUSEHOLDPARTICIPANTRETAINED        

    FROM                  dbo.ADDRESSBOOKFAF A                        (NOLOCK) 
    LEFT JOIN            dbo.REGISTRANT REG                            (NOLOCK) ON A.CONSTITUENTID = REG.CONSTITUENTID
    LEFT JOIN            [dbo].[REGISTRANTEXTENSION] Rx          (NOLOCK) ON REG.ID =Rx.REGISTRANTID 
    LEFT JOIN            dbo.CONSTITUENT C                                (NOLOCK) ON C.ID =REG.CONSTITUENTID 
    LEFT JOIN            dbo.TEAMFUNDRAISER TFR                    (NOLOCK) ON C.ID = TFR.CONSTITUENTID 
    LEFT JOIN            dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM        (NOLOCK) ON TFR.ID = TFTM.TEAMFUNDRAISERID 
    LEFT JOIN            dbo.TEAMFUNDRAISINGTEAM TFT                (NOLOCK) ON TFTM.TEAMFUNDRAISINGTEAMID = TFT.ID AND    TFR.APPEALID= TFT.APPEALID
    LEFT JOIN            dbo.TEAMEXTENSION TE                    (NOLOCK) ON TFTM.TEAMFUNDRAISINGTEAMID = TE.TEAMFUNDRAISINGTEAMID 
    LEFT JOIN            [dbo].[TEAMFUNDRAISINGTEAMCAPTAIN] TFTC (NOLOCK) ON TFT.ID= TFTC.TEAMFUNDRAISINGTEAMID
    LEFT JOIN            [dbo].[EVENT] E                         (NOLOCK) ON TFR.APPEALID = E.APPEALID
    --LEFT JOIN        dbo.EVENTEXTENSION EE                                    (NOLOCK) ON    EE.EVENTID    = E.ID


    OUTER APPLY        UFN_FAF_GETTEAMCOMPANYHOUSEHOLDINFO_BY_REGISTRANT(@EVENTID, REG.CONSTITUENTID) H
    OUTER APPLY        UFN_FAF_GETTEAMCOMPANYHOUSEHOLDINFO_BY_REGISTRANT(@EVENTID_PREV, REG.CONSTITUENTID) HP

    LEFT JOIN            GroupFacts TGF                ON    TGF.EVENTID    =     @EVENTID                AND TGF.ID    = H.TEAMID
    LEFT JOIN            PriorGroupFacts  TGFP    ON    TGFP.EVENTID = @EVENTID_PREV    AND TGFP.ID = HP.TEAMID

    LEFT JOIN            GroupFacts  CGF              ON    CGF.EVENTID     = @EVENTID                AND CGF.ID  = H.COMPANYID
    LEFT JOIN            PriorGroupFacts  CGFP    ON    CGFP.EVENTID = @EVENTID_PREV    AND CGFP.ID = HP.COMPANYID

    LEFT JOIN            GroupFacts  HGF              ON    CGF.EVENTID     = @EVENTID                AND HGF.ID  = H.HouseholdID
    LEFT JOIN            PriorGroupFacts  HGFP    ON    CGFP.EVENTID = @EVENTID_PREV    AND HGFP.ID = HP.HouseholdID

    WHERE                  A.ID= @ADDRESSBOOKFAFID AND A.CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCONSTITUENTID(@REGISTRANTCONSTITUENTID))
    AND           E.ID= @EVENTID 
    AND           REG.EVENTID= @EVENTID
    --AND           (TFT.ID = @REGISTRANTTEAMID or TFT.PARENTTEAMID = @REGISTRANTTEAMID)


    UNION
    SELECT  DISTINCT A.FIRSTNAME, A.LASTNAME, E.ID AS EVENTID, E.NAME AS EVENTNAME, E.STARTDATE AS EVENTSTARTDATE    

            --, 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 TFR.CONSTITUENTID     IS NOT NULL THEN 'MEMBER' ELSE 'PARTICIPANT' END AS CATEGORYTYPE


            --, (SELECT UPPER(LEFT(@SYSTEM_ROLES_PREV, LEN(@SYSTEM_ROLES_PREV)-1))) AS GROUPTYPE

            , (SELECT  UPPER(ROLE) from dbo.UFN_REGISTRANT_GETFAFROLE  (@EVENTID_PREV, null) 
                  WHERE  CONSTITUENTID= (SELECT CONSTITUENTID FROM ADDRESSBOOKFAF WHERE ID= @ADDRESSBOOKFAFID)) as GROUPTYPE         

            , '' AS CATEGORYTYPE

                , CASE WHEN TFR.CONSTITUENTID     IS NOT NULL THEN 'P' END AS EVENTINDICATOR
              , NULL AS CATEGORYLEADER
              , NULL AS GROUPAMOUNTRAISED        

              --, CASE WHEN TE.TYPECODE IN (1, 2) THEN  RX.FUNDRAISINGGOAL ELSE RX.TARGETFUNDRAISINGGOAL END AS FUNDRAISINGGOAL

            , RX.TARGETFUNDRAISINGGOAL AS FUNDRAISINGGOAL
                  , dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL(REG.ID, E.ID) AS REGISTRANTAMOUNTRAISED  

                  , H.TEAMNAME, H.COMPANYNAME, H.TEAMGOAL, H.COMPANYGOAL
                  , CASE WHEN    dbo.UFN_REVENUE_GETTEAMRAISEDTOTAL(TGF.ID, TGF.EVENTID) <> 0 AND dbo.UFN_REVENUE_GETTEAMRAISEDTOTAL(TGFP.ID,TGF.EVENTID) <> 0 
                               THEN    dbo.UFN_REVENUE_GETTEAMRAISEDTOTAL(TGF.ID, TGF.EVENTID)/dbo.UFN_REVENUE_GETTEAMRAISEDTOTAL(TGFP.ID,TGF.EVENTID)                    
                   ELSE 0 END AS TEAMREVENUERETAINED

                  , CASE WHEN    dbo.UFN_REVENUE_GETCOMPANYRAISEDTOTAL(CGF.ID,CGF.EVENTID) <> 0 AND dbo.UFN_REVENUE_GETCOMPANYRAISEDTOTAL(CGFP.ID, CGFP.EVENTID) <> 0             
                              THEN    dbo.UFN_REVENUE_GETCOMPANYRAISEDTOTAL(CGF.ID,CGF.EVENTID)/dbo.UFN_REVENUE_GETCOMPANYRAISEDTOTAL(CGFP.ID, CGFP.EVENTID)    
                   ELSE 0 END as COMPANYREVENUERETAINED

                , TGF.MEMBERNUMBER/TGFP.MEMBERNUMBER AS TEAMPARTICIPANTRETAINED         
                  , CGF.MEMBERNUMBER/CGFP.MEMBERNUMBER AS COMPANYPARTICIPANTRETAINED

            , CASE WHEN H.TEAMID      IS NOT NULL    THEN dbo.UFN_REVENUE_GETTEAMRAISEDTOTAL(H.TEAMID, E.ID)                      END AS TEAMRAISED
                  , CASE WHEN H.COMPANYID   IS NOT NULL    THEN dbo.UFN_REVENUE_GETCOMPANYRAISEDTOTAL(H.COMPANYID, E.ID)          END AS COMPANYRAISED
                  , CASE WHEN H.HouseholdID IS NOT NULL    THEN dbo.UFN_REVENUE_GETHOUSEHOLDRAISEDTOTAL(H.HouseholdID, E.ID)    END AS HOUSEHOLDRAISED 
            , H.HOUSEHOLDGOAL

            , H.HouseholdName
            , CASE WHEN    dbo.UFN_REVENUE_GETHOUSEHOLDRAISEDTOTAL(HGF.ID, HGF.EVENTID) <> 0 AND dbo.UFN_REVENUE_GETHOUSEHOLDRAISEDTOTAL(HGFP.ID,HGFP.EVENTID) <> 0 
                             THEN    dbo.UFN_REVENUE_GETHOUSEHOLDRAISEDTOTAL(HGF.ID, HGF.EVENTID)/dbo.UFN_REVENUE_GETHOUSEHOLDRAISEDTOTAL(HGFP.ID,HGFP.EVENTID)                    
                   ELSE 0 END AS HOUSEHOLDREVENUERETAINED
            ,HGF.MEMBERNUMBER/HGFP.MEMBERNUMBER AS HOUSEHOLDPARTICIPANTRETAINED        

    FROM                  dbo.ADDRESSBOOKFAF A                        (NOLOCK) 
    LEFT JOIN            dbo.REGISTRANT REG                            (NOLOCK) ON A.CONSTITUENTID = REG.CONSTITUENTID
    LEFT JOIN            [dbo].[REGISTRANTEXTENSION] Rx    (NOLOCK) ON REG.ID =Rx.REGISTRANTID 
    LEFT JOIN            dbo.CONSTITUENT C                                (NOLOCK) ON C.ID =REG.CONSTITUENTID 
    LEFT JOIN            dbo.TEAMFUNDRAISER TFR                    (NOLOCK) ON C.ID = TFR.CONSTITUENTID 
    LEFT JOIN            dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM        (NOLOCK) ON TFR.ID = TFTM.TEAMFUNDRAISERID 
    LEFT JOIN            dbo.TEAMFUNDRAISINGTEAM TFT                (NOLOCK) ON TFTM.TEAMFUNDRAISINGTEAMID = TFT.ID AND    TFR.APPEALID= TFT.APPEALID
    LEFT JOIN            dbo.TEAMEXTENSION TE                    (NOLOCK) ON TFTM.TEAMFUNDRAISINGTEAMID = TE.TEAMFUNDRAISINGTEAMID 
    LEFT JOIN            [dbo].[TEAMFUNDRAISINGTEAMCAPTAIN] TFTC (NOLOCK) ON TFT.ID= TFTC.TEAMFUNDRAISINGTEAMID
    LEFT JOIN            [dbo].[EVENT] E                         (NOLOCK) ON TFR.APPEALID = E.APPEALID
    --LEFT JOIN        dbo.EVENTEXTENSION EE                        (NOLOCK) ON EE.PRIORYEAREVENTID    = E.ID


    OUTER APPLY        UFN_FAF_GETTEAMCOMPANYHOUSEHOLDINFO_BY_REGISTRANT(@EVENTID_PREV, REG.CONSTITUENTID) H
    OUTER APPLY        UFN_FAF_GETTEAMCOMPANYHOUSEHOLDINFO_BY_REGISTRANT(@EVENTID_PREV2, REG.CONSTITUENTID) HP

    LEFT JOIN            PriorGroupFacts        TGF            ON    TGF.EVENTID    =     @EVENTID_PREV        AND TGF.ID    = H.TEAMID
    LEFT JOIN            PriorGroupFacts2  TGFP        ON    TGFP.EVENTID = @EVENTID_PREV2        AND TGFP.ID = HP.TEAMID

    LEFT JOIN            PriorGroupFacts        CGF            ON    CGF.EVENTID     = @EVENTID_PREV      AND CGF.ID  = H.COMPANYID
    LEFT JOIN            PriorGroupFacts2    CGFP        ON    CGFP.EVENTID = @EVENTID_PREV2        AND CGFP.ID = HP.COMPANYID

    LEFT JOIN            PriorGroupFacts        HGF            ON    CGF.EVENTID     = @EVENTID_PREV      AND HGF.ID  = H.HouseholdID
    LEFT JOIN            PriorGroupFacts2    HGFP        ON    CGFP.EVENTID = @EVENTID_PREV2        AND HGFP.ID = HP.HouseholdID

    WHERE                 A.ID= @ADDRESSBOOKFAFID AND A.CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCONSTITUENTID(@REGISTRANTCONSTITUENTID))
    AND          E.ID= @EVENTID_PREV
    AND                     REG.EVENTID =@EVENTID_PREV
    ORDER BY E.STARTDATE  DESC
        END