USP_FAFGROUP_CALCULATE_GROUPSANDMEMBERS

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

--declare @CURRENTDATE datetime;


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

if @CURRENTDATE is null
  set @CURRENTDATE = GETDATE();

begin try
  declare @COUNTS table (
      GROUPID uniqueidentifier,
      EVENTID uniqueidentifier,
      PARTICIPANTS int,
      CHILDGROUPSCNT int,
      TEAMCONSTITUENTID uniqueidentifier,
      TYPECODE tinyint
  );

  insert into @COUNTS (
      GROUPID,
      EVENTID,
      PARTICIPANTS,
      CHILDGROUPSCNT,
      TEAMCONSTITUENTID,
      TYPECODE
  )
  select
      M.GROUPID, M.EventID, M.PARTICIPANTS, M.CHILDGROUPSCNT, TX.TEAMCONSTITUENTID, TX.TYPECODE 
  from
      dbo.UFN_FAFGROUP_GETGROUPANDMEMBERCOUNT() M 
  join
      dbo.TEAMEXTENSION TX (nolock) on TX.TEAMFUNDRAISINGTEAMID = M.GROUPID;

    merge into dbo.FAFGROUPSUMMARYINFORMATION as Target
    using
    (
      select GROUPID, EVENTID, PARTICIPANTS, CHILDGROUPSCNT, TEAMCONSTITUENTID, TYPECODE from @COUNTS
    ) as source on Target.ID = source.GROUPID and Target.EVENTID = source.EventID      
    when matched and (source.PARTICIPANTS <> Target.TOTALPARTICIPANTS or source.CHILDGROUPSCNT <> Target.TOTALGROUPS) then
        update set
            TOTALPARTICIPANTS = source.PARTICIPANTS,
            TOTALGROUPS = source.CHILDGROUPSCNT,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED   = @CURRENTDATE        
    when not matched then
        insert(ID, GROUPTYPECODE, EVENTID, GROUPCONSTITUENTID, TOTALPARTICIPANTS, TOTALGROUPS, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values(source.GROUPID, source.TYPECODE, source.EventID, source.TEAMCONSTITUENTID, source.PARTICIPANTS, source.CHILDGROUPSCNT ,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

  select @NUMBERPROCESSED=COUNT(*) from dbo.FAFGROUPSUMMARYINFORMATION (nolock)
    where DATECHANGED = @CURRENTDATE 

end try
begin catch
   exec dbo.USP_RAISE_ERROR;
end catch

end