USP_FAFGROUP_CALCULATE_COMMSENTCNT

CREATE procedure dbo.USP_FAFGROUP_CALCULATE_COMMSENTCNT
(
                @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();

-- get communication sent, 


begin try
    merge into dbo.FAFGROUPSUMMARYINFORMATION as Target
    using
    (    
        select A.GROUPID, A.EVENTID, A.TEAMCONSTITUENTID, A.TYPECODE, SUM(A.COMMSENTCNT + A.SOCIALNETWORKPOSTSCNT) COMMUNICATIONSENT 
      from (    
          select 
              G.PARENTID as GROUPID, TX.TYPECODE, G.PARENTTEAMCONSTITUENTID TEAMCONSTITUENTID, G.EVENTID, isnull(SSNWK.SOCIALNETWORKPOSTSCNT, 0) SOCIALNETWORKPOSTSCNT , isnull(CMMS.COMMSENTCNT, 0) COMMSENTCNT
              from dbo.UFN_FAFGROUP_GETALLGROUPS() G 
              join dbo.TEAMEXTENSION TX (nolock) on TX.TEAMFUNDRAISINGTEAMID = G.PARENTID
              left join (
                  select TX.TEAMFUNDRAISINGTEAMID, count(SSP.ID) as SOCIALNETWORKPOSTSCNT 
          from dbo.TEAMEXTENSION TX (nolock)
                  join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM (nolock) on TFTM.TEAMFUNDRAISINGTEAMID = TX.TEAMFUNDRAISINGTEAMID
                  join dbo.TEAMFUNDRAISER TF (nolock) on TF.ID = TFTM.TEAMFUNDRAISERID
                  join dbo.REGISTRANT R (nolock) on R.CONSTITUENTID = TF.CONSTITUENTID AND R.EVENTID = TX.EVENTID
                  join dbo.FAFEVENTSOCIALNETWORKPOST SSP (nolock) on SSP.REGISTRANTID = R.ID
                  group by TX.TEAMFUNDRAISINGTEAMID             
              ) SSNWK on SSNWK.TEAMFUNDRAISINGTEAMID = G.TEAMID
              left join (
                  select TX.TEAMFUNDRAISINGTEAMID, COUNT(F.ID) as COMMSENTCNT
                  from dbo.TEAMEXTENSION TX (nolock)
                  join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM (nolock) on TFTM.TEAMFUNDRAISINGTEAMID = TX.TEAMFUNDRAISINGTEAMID
                  join dbo.TEAMFUNDRAISER TF (nolock) on TF.ID = TFTM.TEAMFUNDRAISERID
                  join dbo.CONSTITUENT C (nolock) on C.ID = TF.CONSTITUENTID 
                  join dbo.BackOfficeSystemPeople BOSP(nolock) on BOSP.BackofficeRecordID = C.SEQUENCEID and BOSP.BackOfficeSystemID = 0   
                  join dbo.BackOfficeSystemUsers BOSU(nolock) on BOSU.BackofficePeopleID = BOSP.ID and BOSU.[CURRENT] = 1  
                  join dbo.ClientUsers CU(nolock) on CU.ID = BOSU.ClientUsersID and CU.Deleted = 0 
                  join dbo.FAFCOMMUNICATIONSLOG F (nolock) on CU.ID = F.CLIENTUSERSID and TX.EVENTID = F.EVENTID 
                  group by TX.TEAMFUNDRAISINGTEAMID
              ) CMMS on CMMS.TEAMFUNDRAISINGTEAMID = G.TEAMID            
              where SSNWK.SOCIALNETWORKPOSTSCNT > 0 Or CMMS.COMMSENTCNT > 0                        
      ) A         
      group by A.GROUPID,A.TEAMCONSTITUENTID,  A.EVENTID, A.TYPECODE 

    ) as source on Target.ID = source.GROUPID and Target.EVENTID = source.EventID   
    when matched and (source.COMMUNICATIONSENT <> Target.TOTALCOMMUNICATIONSENT) then
        update set
            TOTALCOMMUNICATIONSENT = source.COMMUNICATIONSENT,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED   = @CURRENTDATE        
    when not matched then
        insert(ID, GROUPTYPECODE, EVENTID, GROUPCONSTITUENTID, TOTALCOMMUNICATIONSENT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values(source.GROUPID, source.TYPECODE, source.EventID, source.TEAMCONSTITUENTID, source.COMMUNICATIONSENT, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

end try
begin catch
   exec dbo.USP_RAISE_ERROR;
end catch

end