USP_FAFNFGCAMPAIGNLEVEL_CALCULATE

CREATE procedure dbo.USP_FAFNFGCAMPAIGNLEVEL_CALCULATE
(
        @NUMBERPROCESSED integer = 0 output,
        @CHANGEAGENTID uniqueidentifier = null
)
as
begin
set nocount on;

declare @CURRENTDATE datetime;

if @CHANGEAGENTID is null
  exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

set @CURRENTDATE = GETDATE();

-- get total number of participant returned to a group and its retention rate, 


--begin try

    merge into dbo.FAFNFGLEVELSUMMARY as Target
    using
    (    
        select AL.ID, AL.CAMPAIGNID, AL.NFGID,
            Sum(isnull(FGSI.TOTALFUNDRAISE,0)) TOTALRAISED, 
            Sum(isnull(FGSI.TOTALGROUPS, -1) + 1) TOTALGROUP,
            Sum(isnull(FGSI.TOTALPARTICIPANTS,0))TOTALPARTICIPANT, 
            Sum(isnull(FGSI.TOTALCOMMUNICATIONSENT,0))TOTALCOMMUNICATIONSENT, 
            Sum(isnull(FGSI.TOTALDONORCOUNT,0))TOTALDONOR,   
            Sum(isnull(FGSI.TOTALGIFTCOUNT,0))TOTALGIFT
        from dbo.UFN_FAFNFGCAMPAIGN_GETALLLEVELS() AL
      left join dbo.TEAMEXTENSION TX (nolock) on AL.CHILDID = TX.NFGCAMPAIGNLEVELID
      left join dbo.FAFGROUPSUMMARYINFORMATION FGSI (nolock) on FGSI.ID = TX.TEAMFUNDRAISINGTEAMID
        group by AL.CAMPAIGNID, AL.NFGID, AL.ID

    ) as source on Target.ID = source.ID 
    when matched and (source.TOTALRAISED <> Target.TOTALRAISED or source.TOTALGROUP <> Target.TOTALGROUP or source.TOTALPARTICIPANT <> Target.TOTALPARTICIPANT
                    or source.TOTALDONOR <> Target.TOTALDONOR or source.TOTALCOMMUNICATIONSENT <> Target.TOTALCOMMUNICATIONSENT or source.TOTALGIFT <> Target.TOTALGIFT ) then
        update set
            TOTALRAISED = source.TOTALRAISED,
            TOTALGROUP = source.TOTALGROUP,
            TOTALPARTICIPANT = source.TOTALPARTICIPANT,            
            TOTALDONOR = source.TOTALDONOR,
            TOTALCOMMUNICATIONSENT = source.TOTALCOMMUNICATIONSENT,
            TOTALGIFT = source.TOTALGIFT,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED   = @CURRENTDATE        
    when not matched then
        insert(ID, NFGID, CAMPAIGNID, TOTALRAISED, TOTALGROUP, TOTALPARTICIPANT, TOTALDONOR, TOTALCOMMUNICATIONSENT, TOTALGIFT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values(source.ID, source.NFGID, source.CAMPAIGNID, source.TOTALRAISED, source.TOTALGROUP, source.TOTALPARTICIPANT, source.TOTALDONOR, source.TOTALCOMMUNICATIONSENT,source.TOTALGIFT,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);


  select @NUMBERPROCESSED=COUNT(*) from dbo.FAFNFGLEVELSUMMARY (nolock)
    where ISROOT = 0 and DATECHANGED = @CURRENTDATE

--end try

--begin catch  

    --exec dbo.USP_RAISE_ERROR;

--end catch


end