USP_DATALIST_FAFTOPFUNDRAISER

CREATE procedure dbo.USP_DATALIST_FAFTOPFUNDRAISER
(
    @EVENTID uniqueidentifier,

    @TEAMFUNDRAISINGTEAMID uniqueidentifier = NULL,    --not used

    @ROLETYPE tinyint = 0,    --not used

    @COMPANIESOPT bit = 0,    --not used

    @TEAMSOPT bit = 0,        --not used

    @HOUSEHOLDOPT bit = 0,    --not used

    @PARTICIPANTOPT bit = 0,  --not used


    @COMPANIESNUM int = 0,
    @TEAMSNUM int = 0,
    @HOUSEHOLDNUM int = 0,
    @PARTICIPANTNUM int = 0
)WITH EXECUTE AS CALLER
as

begin
    SET NOCOUNT ON;

    declare @EVENTNAME varchar(100), @APPEALID uniqueidentifier

    select @EVENTNAME = NAME, @APPEALID = APPEALID
    from dbo.EVENT (nolock) where ID = @EVENTID;

    declare @result table
    (
        AMOUNTRAISED money,
        FUNDRAISINGGOAL money,
        GROUPTYPEID uniqueidentifier,
        FUNDRAISERNAME nvarchar(308),
        HOUSEHOLDNAME nvarchar(200),
        TEAMNAME nvarchar(200),
        COMPANYNAME nvarchar(200),
        SITEIMAGESID int,
        FUNDRAISERTYPE nvarchar(50),
        STORYTEXT nvarchar(max),
        ISPRIVATE bit,
        TEAMFUNDRAISINGTEAMID uniqueidentifier,
        REGISTRANTID uniqueidentifier
    )

    -- try retrieving data from cache first

    set transaction isolation level read uncommitted;
    begin transaction;

    declare @IDOC int;
    declare @CACHEKEY as nvarchar(512);
    declare @CACHEDATA as xml;
    declare @LIFESPANSECONDS int;

    set @LIFESPANSECONDS = 60;
    set @CACHEDATA = null;
    set @CACHEKEY = 'FAFTOPFUNDRAISER' 
                    + '_' + convert(nvarchar(4), @COMPANIESNUM) 
                    + '_' + convert(nvarchar(4), @TEAMSNUM)
                    + '_' + convert(nvarchar(4), @HOUSEHOLDNUM)
                    + '_' + convert(nvarchar(4), @PARTICIPANTNUM);

    exec dbo.USP_FAFEVENTDATACACHE_GET @DATA = @CACHEDATA output, @EVENTID = @EVENTID, @CACHEKEY = @CACHEKEY;

    if @CACHEDATA is null    -- start calculations

        begin
            -- update cache ISCALCULATING flag

            exec dbo.USP_FAFEVENTDATACACHE_CALCULATIONSTARTS @EVENTID = @EVENTID, @CACHEKEY = @CACHEKEY;

            if @PARTICIPANTNUM > 0
            begin
                insert into @result
                select top (@PARTICIPANTNUM) 
                        PRT.AMOUNTRAISED
                      , Rx.TARGETFUNDRAISINGGOAL as FUNDRAISINGGOAL
                      , C.ID as GROUPTYPEID
                      , C.NAME AS FUNDRAISERNAME
                      , case when TE.TYPECODE=3 then TFT.NAME else NULL end as HOUSEHOLDNAME
                      , case when TE.TYPECODE=1 then TFT.NAME when TE2.TYPECODE=1 then TFT2.NAME else NULL end as TEAMNAME
                      , case when TE.TYPECODE=2 then TFT.NAME when TE2.TYPECODE=2 then TFT2.NAME when TE3.TYPECODE=2 then TFT3.NAME else NULL end as COMPANYNAME
                      , SIE.SITEIMAGESID
                      , 'Participants' as FUNDRAISERTYPE
                      , St.STORYTEXT
                      , Rx.ISPRIVATE
                      , TFTM.TEAMFUNDRAISINGTEAMID
                      , Reg.ID as REGISTRANTID 
                from dbo.REGISTRANT Reg (nolock)            
                inner join dbo.CONSTITUENT C (nolock) ON C.ID = Reg.CONSTITUENTID 
                inner join dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL_2(@EVENTID) PRT on Reg.CONSTITUENTID = PRT.CONSTITUENTID

                left join dbo.REGISTRANTEXTENSION Rx (nolock)    ON Reg.ID = Rx.REGISTRANTID 
                left join dbo.STORY St (nolock)  ON Reg.ID = St.REGISTRANTID 
                left outer join dbo.SITEIMAGESEXTENSION SIE on SIE.ID = ST.FAFIMAGESID

                left join dbo.TEAMFUNDRAISER TFR (nolock)    ON C.ID = TFR.CONSTITUENTID and TFR.APPEALID = @APPEALID
                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 AND TE.EVENTID = @EVENTID

                left join dbo.TEAMFUNDRAISINGTEAM TFT2 (nolock)  ON TFT.PARENTTEAMID = TFT2.ID 
                left join dbo.TEAMEXTENSION TE2 (nolock)    ON TFT2.ID= TE2.TEAMFUNDRAISINGTEAMID  and TE2.EVENTID  = @EVENTID
                left join dbo.TEAMFUNDRAISINGTEAM TFT3 (nolock)  ON TFT2.PARENTTEAMID = TFT3.ID  
                left join dbo.TEAMEXTENSION TE3 (nolock)    ON TFT3.ID= TE3.TEAMFUNDRAISINGTEAMID     and TE3.EVENTID = @EVENTID
                where Reg.EVENTID = @EVENTID and PRT.AMOUNTRAISED > 0
                order by AMOUNTRAISED DESC
            end

            if @TEAMSNUM > 0
            begin
                insert into @result
                select top (@TEAMSNUM)
                    GT.TOTALFUNDRAISE as AMOUNTRAISED 
                    , TFT.GOAL as FUNDRAISINGGOAL
                    , TE.TEAMFUNDRAISINGTEAMID as GROUPTYPEID
                    , NULL as FUNDRAISERNAME
                    , NULL as HOUSEHOLDNAME
                    , TFT.NAME as TEAMNAME
                    , TFT2.NAME as COMPANYNAME
                    , SIE.SITEIMAGESID
                    , 'Teams' as FUNDRAISERTYPE
                    , ST.STORYTEXT
                    , 0 as ISPRIVATE
                    , null as TEAMFUNDRAISINGTEAMID
                    , null as REGISTRANTID
                from dbo.FAFGROUPSUMMARYINFORMATION GT (nolock)    
                inner join dbo.TEAMEXTENSION TE (nolock) on GT.GROUPCONSTITUENTID = TE.TEAMCONSTITUENTID 
                inner join dbo.TEAMFUNDRAISINGTEAM TFT (nolock) on TE.TEAMFUNDRAISINGTEAMID = TFT.ID
                left outer join dbo.TEAMFUNDRAISINGTEAM TFT2 (nolock) ON TFT.PARENTTEAMID = TFT2.ID
                left outer join dbo.STORY ST (nolock) ON TFT.ID = ST.TEAMFUNDRAISINGTEAMID AND ST.EVENTID =  @EVENTID
                left outer join dbo.SITEIMAGESEXTENSION SIE (nolock) on SIE.ID = ST.FAFIMAGESID
                where 
          GROUPTYPECODE = 1 and TOTALFUNDRAISE > 0 
          -- SHL BBIS Bug 385999; Because the TEAMEXTENSION and FAFGROUPSUMMARYINFORMATION can apply to the same tema leader for different events, we must specify the events on both tables

          and GT.EVENTID = @EVENTID  
          and TE.EVENTID = @EVENTID
                order by TOTALFUNDRAISE desc
            end

            if @HOUSEHOLDNUM > 0
            begin
                insert into @result
                select top (@HOUSEHOLDNUM)
                    GT.TOTALFUNDRAISE as AMOUNTRAISED
                    , TFT.GOAL as FUNDRAISINGGOAL
                    , TE.TEAMFUNDRAISINGTEAMID as GROUPTYPEID
                    , NULL as FUNDRAISERNAME
                    , TFT.NAME as HOUSEHOLDNAME
                    , case when TE2.TYPECODE=1 then TFT2.NAME else NULL end as TEAMNAME
                    , case when TE2.TYPECODE=2 then TFT2.NAME when TE3.TYPECODE=2 then TFT3.NAME else NULL end as COMPANYNAME 
                    , SIE.SITEIMAGESID
                    , 'Households' as FUNDRAISERTYPE
                    , ST.STORYTEXT
                    , dbo.UFN_FAFEVENT_GETGROUPPAGEPRIVACYfromGROUPLEADER(TE.TEAMFUNDRAISINGTEAMID, 3) as ISPRIVATE
                    , null as TEAMFUNDRAISINGTEAMID
                    , null as REGISTRANTID
                from dbo.FAFGROUPSUMMARYINFORMATION GT (nolock)    
                inner join dbo.TEAMEXTENSION TE (nolock) on GT.GROUPCONSTITUENTID = TE.TEAMCONSTITUENTID 
                inner join dbo.TEAMFUNDRAISINGTEAM TFT (nolock) on TE.TEAMFUNDRAISINGTEAMID = TFT.ID
                left outer join dbo.STORY ST (nolock) ON TE.TEAMFUNDRAISINGTEAMID = ST.TEAMFUNDRAISINGTEAMID AND ST.EVENTID = @EVENTID
                left outer join dbo.SITEIMAGESEXTENSION SIE (nolock) on SIE.ID = ST.FAFIMAGESID
                left outer join dbo.TEAMFUNDRAISINGTEAM TFT2 (nolock) ON TFT.PARENTTEAMID = TFT2.ID 
                left outer join dbo.TEAMEXTENSION TE2 (nolock) ON TFT2.ID = TE2.TEAMFUNDRAISINGTEAMID and TE2.EVENTID = @EVENTID
                left outer join dbo.TEAMFUNDRAISINGTEAM TFT3 (nolock) ON TFT2.PARENTTEAMID = TFT3.ID  
                left outer join dbo.TEAMEXTENSION TE3 (nolock) ON TFT3.ID = TE3.TEAMFUNDRAISINGTEAMID     and TE3.EVENTID = @EVENTID
                where GROUPTYPECODE = 3 and TOTALFUNDRAISE > 0    
        -- SHL BBIS Bug 385999; Because the TEAMEXTENSION and FAFGROUPSUMMARYINFORMATION can apply to the same tema leader for different events, we must specify the events on both tables

        and GT.EVENTID = @EVENTID  
        and TE.EVENTID = @EVENTID
        order by AMOUNTRAISED DESC
            end

            if @COMPANIESNUM > 0
            begin
                insert into @result
                select top (@COMPANIESNUM)
                    GT.TOTALFUNDRAISE as AMOUNTRAISED
                    , TFT.GOAL as FUNDRAISINGGOAL
                    , TE.TEAMFUNDRAISINGTEAMID as GROUPTYPEID
                    , null as FUNDRAISERNAME
                    , null as HOUSEHOLDNAME
                    , null as TEAMNAME
                    , TFT.NAME as COMPANYNAME
                    , SIE.SITEIMAGESID
                    , 'Companies' as FUNDRAISERTYPE
                    , ST.STORYTEXT
                    , 0 as ISPRIVATE
                    , null as TEAMFUNDRAISINGTEAMID
                    , null as REGISTRANTID
                from dbo.FAFGROUPSUMMARYINFORMATION GT (nolock)    
                inner join dbo.TEAMEXTENSION TE (nolock) on GT.GROUPCONSTITUENTID = TE.TEAMCONSTITUENTID 
                inner join dbo.TEAMFUNDRAISINGTEAM TFT (nolock) on TE.TEAMFUNDRAISINGTEAMID = TFT.ID
                left outer join dbo.STORY ST (nolock) ON TE.TEAMFUNDRAISINGTEAMID = ST.TEAMFUNDRAISINGTEAMID AND ST.EVENTID = @EVENTID
                left outer join dbo.SITEIMAGESEXTENSION SIE (nolock) on SIE.ID = ST.FAFIMAGESID
                where GROUPTYPECODE = 2 and TOTALFUNDRAISE > 0 
        -- SHL BBIS Bug 385999; Because the TEAMEXTENSION and FAFGROUPSUMMARYINFORMATION can apply to the same tema leader for different events, we must specify the events on both tables

        and GT.EVENTID = @EVENTID  
        and TE.EVENTID = @EVENTID
                order by AMOUNTRAISED DESC
            end

            -- convert the result to xml

            set @CACHEDATA = (select * from @result for xml raw);
            if @CACHEDATA is null
                set @CACHEDATA = '<rows />';
            else
                set @CACHEDATA= '<rows>' + convert(nvarchar(max), @CACHEDATA) + '</rows>';

            select @LIFESPANSECONDS = CONVERT(int, ISNULL(Value, '60')) from dbo.SETTING where Name = 'FAFEventTotalCache';

            -- cache the result

            exec dbo.USP_FAFEVENTDATACACHE_ADD
                @EVENTID = @EVENTID,
                @CACHEKEY = @CACHEKEY,
                @DATA = @CACHEDATA,
                @LIFESPANSECONDS = @LIFESPANSECONDS;
        end
    else
        begin
            begin try
                exec sp_xml_preparedocument @IDOC output, @CACHEDATA;

                insert into @result
                select * from openxml(@IDOC, '/rows/row', 0) with (
                    AMOUNTRAISED money,
                    FUNDRAISINGGOAL money,
                    GROUPTYPEID uniqueidentifier,
                    FUNDRAISERNAME nvarchar(308),
                    HOUSEHOLDNAME nvarchar(200),
                    TEAMNAME nvarchar(200),
                    COMPANYNAME nvarchar(200),
                    SITEIMAGESID int,
                    FUNDRAISERTYPE nvarchar(50),
                    STORYTEXT nvarchar(max),
                    ISPRIVATE bit,
                    TEAMFUNDRAISINGTEAMID uniqueidentifier,
                    REGISTRANTID uniqueidentifier        
                );

                exec sp_xml_removedocument @IDOC;
            end try
            begin catch
                -- do nothing

            end catch
        end

    commit transaction;        

/** NEED TO MAINTAIN THE ORDER FOR OUTPUT UNLESS YOU HAVE A GOOD REASON TO CHANGE THIS ORDER - 
be sure to check how to pass backward compatibility unit test if this order is change **/

    select AMOUNTRAISED,
          FUNDRAISINGGOAL,
          GROUPTYPEID,
          FUNDRAISERNAME,
          HOUSEHOLDNAME,
          TEAMNAME,
          COMPANYNAME,
          SITEIMAGESID,
          FUNDRAISERTYPE,
          STORYTEXT,
          ISPRIVATE, 
          @EVENTNAME as EVENTNAME,
          TEAMFUNDRAISINGTEAMID,
          REGISTRANTID
    from @result
    order by AMOUNTRAISED DESC, FUNDRAISERTYPE

END