USP_FAFNFGCAMPAIGN_CALCULATE

CREATE procedure dbo.USP_FAFNFGCAMPAIGN_CALCULATE
(
   @NUMBERPROCESSED integer = 0 output,
   @CHANGEAGENTID uniqueidentifier = null
)
as
begin

    declare @CURRENTDATE datetime;

if @CHANGEAGENTID is null
  exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
set @CURRENTDATE = GETDATE();

--begin try


  merge into dbo.FAFNFGLEVELSUMMARY as target
  using 
  (
      select CL.ID as NFGLEVELID, VCAM.ID as NFGID, VCAM.CAMPAIGNID, VCAM.TOTALRAISED, VCAM.TOTALDONOR, VCAM.TOTALGIFT, VCAM.TOTALGROUP, VCAM.TOTALCOMMUNICATIONSENT, VCAM.TOTALPARTICIPANT,
                  VCAM.TOTALGROUPRETAINED, VCAM.GROUPRETENTIONRATE, VCAM.TOTALPARTICIPANTRETAINED, VCAM.PARTICIPANTRETENTIONRATE
      from dbo.UFN_FAFNFGCAMPAIGN_GETSUMMARY() VCAM
      join dbo.FAFNFGCAMPAIGNLEVEL CL (nolock) on VCAM.ID = CL.NFGCAMPAIGNID and HIERARCHYPATH.GetLevel() = 1

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

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

--end try

--begin catch

  --exec dbo.USP_RAISE_ERROR;

--end catch



end