USP_FAFGROUPSUMMARYINFORMATION_CALCULATE

CREATE procedure dbo.USP_FAFGROUPSUMMARYINFORMATION_CALCULATE
(
                @NUMBERPROCESSED integer = 0 output
)
as
begin
set nocount on;


declare @CHANGEAGENTID uniqueidentifier,
              @CURRENTDATE datetime;

set @NUMBERPROCESSED = 0;

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

--begin tran

begin try

  -- get child groups and member counts

    exec dbo.USP_FAFGROUP_CALCULATE_GROUPSANDMEMBERS @NUMBERPROCESSED output, @CHANGEAGENTID=@CHANGEAGENTID, @CURRENTDATE=@CURRENTDATE

    -- get donor count, total raised, gift count

    exec dbo.USP_FAFGROUP_CALCULATE_DONORGIFTCNTANDTOTALRAISED  @NUMBERPROCESSED output, @CHANGEAGENTID=@CHANGEAGENTID, @CURRENTDATE=@CURRENTDATE

    -- get communication sent, 

    exec dbo.USP_FAFGROUP_CALCULATE_COMMSENTCNT @NUMBERPROCESSED output, @CHANGEAGENTID=@CHANGEAGENTID, @CURRENTDATE=@CURRENTDATE

  -- get total number of groups returned to a group and group retention rate

  exec dbo.USP_FAFGROUP_CALCULATE_GROUPRETENTIONRATE  @NUMBERPROCESSED output, @CHANGEAGENTID=@CHANGEAGENTID, @CURRENTDATE=@CURRENTDATE

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

  exec dbo.USP_FAFGROUP_CALCULATE_PARTICIPANTRETENTIONRATE @NUMBERPROCESSED output, @CHANGEAGENTID=@CHANGEAGENTID, @CURRENTDATE=@CURRENTDATE

    /*
    --get participant recruited, 
    merge into dbo.FAFGROUPSUMMARYINFORMATION as Target
    using
    (    
        select  R.GROUPID, FL.EVENTID, TX.TEAMCONSTITUENTID, TX.TYPECODE, count(FC.TYPEGUID) PARTICIPANTRECRUITED     
        from    dbo.FAFCOMMUNICATIONSLOG FL
        join dbo.FAFEVENTCOMMUNICATIONCHANNEL FC on fl.EMAILJOBID = fc.EMAILJOBID
        join dbo.UFN_REGISTRANT_GROUPMEMBERLIST() R on R.REGISTRANTID = fc.TYPEGUID
        join dbo.TEAMEXTENSION TX (nolock) on TX.TEAMFUNDRAISINGTEAMID = R.GROUPID
        group by R.GROUPID, FL.EVENTID, TX.TEAMCONSTITUENTID, TX.TYPECODE    
    ) as source on Target.ID = source.GROUPID and Target.EVENTID = source.EventID
    when matched then
        update set
            TOTALPARTICIPANTRECRUITED = source.PARTICIPANTRECRUITED,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED   = @CURRENTDATE        
    when not matched then
        insert(ID, GROUPTYPECODE, EVENTID, GROUPCONSTITUENTID, TOTALPARTICIPANTRECRUITED, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values(source.GROUPID, source.TYPECODE, source.EventID, source.TEAMCONSTITUENTID, source.PARTICIPANTRECRUITED, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
    */    

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


  set @NUMBERPROCESSED = isnull(@NUMBERPROCESSED, 0)


end try
begin catch  

  exec dbo.USP_RAISE_ERROR;
    --IF @@TRANCOUNT > 0

        --ROLLBACK TRANSACTION;


end catch

--IF @@TRANCOUNT > 0

   --COMMIT TRANSACTION;  


end