USP_FAFREGISTRANT_EXTEND_CALCULATE

CREATE procedure dbo.USP_FAFREGISTRANT_EXTEND_CALCULATE
(
    @NUMBERPROCESSED integer = 0 output,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTDATE datetime = null
)
as

begin

    DECLARE @TOPID uniqueidentifier
    set @TOPID = ( select top 1 id
    from [FAFREGISTRANTPENDINGSUMMARY] )

    declare @STARTDATE datetime 

    -- This will come from SCHEDULE       

    set @STARTDATE = ( select coalesce(max(DATECHANGED),'7/4/1776') from [FAFREGISTRANTPENDINGSUMMARY] )      
    -- This will come from SCHEDULE       


    if @STARTDATE is null or @TOPID is null
        SET @STARTDATE = '7/4/1776 00:00:00'

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

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

 /* no longer needed       
;With RegistrantMembers as
(
    select distinct constituentid 
    from dbo.REVENUERECOGNITION (nolock)
    where DATECHANGED >= @STARTDATE
)
,Donors as 
(
    select R.ID as REVENUEID, 
    RS.ID as REVENUESPLITID, 
    RS.AMOUNT ,
    R.DATEADDED, 
    RR.CONSTITUENTID as RECOGNITIONCONSTITUENTID
    from dbo.EVENT E With (NOLOCK)
    join dbo.REVENUE R With (NOLOCK) on E.APPEALID = R.APPEALID --and E.ID = @EVENTID 
    join dbo.REVENUESPLIT RS With (NOLOCK) on R.ID = RS.REVENUEID and R.TRANSACTIONTYPECODE = 9 
    left join dbo.REVENUERECOGNITION RR (nolock) on RR.REVENUESPLITID = RS.ID 
    inner join RegistrantMembers rm on RR.CONSTITUENTID = rm.CONSTITUENTID 


),
EventMembers as 
(
    select R.ID as RECORDID, 
    0 as TYPECODE, 
    C.ID as CONSTITUENTID,
    R.EVENTID
    from dbo.REGISTRANT R With (NOLOCK)
    join dbo.CONSTITUENT C With (NOLOCK) on R.CONSTITUENTID = C.ID 
    inner join RegistrantMembers rm on r.CONSTITUENTID = rm.CONSTITUENTID 
)

*/

declare  @REGTEMP TABLE
(
    TRANSACTIONAMOUNT decimal,
    TOTALAMOUNTRECEIVED decimal,
    EVENTID uniqueidentifier,
    CONSTITUENTID uniqueidentifier,
    ADDEDBYID uniqueidentifier,
    [CHANGEDBYID] uniqueidentifier
)

-- use temp table to avoid microsoft defect. 

    insert into @REGTEMP
    select   
     isnull( F.[AMOUNTPENDING],0) AS TRANSACTIONAMOUNT,  
     0 as TOTALAMOUNTRECEIVED,  
     F.EVENTID,  
     F.CONSTITUENTID ,  
     @CHANGEAGENTID [ADDEDBYID],  
     @CHANGEAGENTID [CHANGEDBYID]
 from FAFRAISEDTOTAL_CATEGORIZED F  
 where f.[DATECHANGED] >= @STARTDATE  


 insert into dbo.[FAFREGISTRANTPENDINGSUMMARY]   
( [TRANSACTIONAMOUNT], [TOTALAMOUNTRECEIVED], [EVENTID], [CONSTITUENTID], [ADDEDBYID], [CHANGEDBYID] )  
    select       
     isnull( r.TRANSACTIONAMOUNT, 0 ),     
     isnull( r.TOTALAMOUNTRECEIVED,  0 )  ,  
     r.EVENTID,      
     r.CONSTITUENTID ,      
     r.[ADDEDBYID],      
     r.[CHANGEDBYID]      
 from @REGTEMP r  
 left join dbo.[FAFREGISTRANTPENDINGSUMMARY] f  
 on f.[EVENTID] = r.[EVENTID] and f.[CONSTITUENTID] = r.[CONSTITUENTID]  
 where f.[CONSTITUENTID] is null  


 update fp    
 set  
  fp.[TRANSACTIONAMOUNT]  = isnull( r.[TRANSACTIONAMOUNT],0) ,  
     fp.[TOTALAMOUNTRECEIVED] = isnull( r.[TOTALAMOUNTRECEIVED], 0) ,  
     fp.[CHANGEDBYID]   = isnull( r.[CHANGEDBYID], @CHANGEAGENTID )   
 from dbo.[FAFREGISTRANTPENDINGSUMMARY] fp  
 left join @REGTEMP r  
 on fp.[EVENTID] = r.[EVENTID] and fp.[CONSTITUENTID] = r.[CONSTITUENTID]  
--where r.[TRANSACTIONAMOUNT] <> fp.[TRANSACTIONAMOUNT] or r.[TOTALAMOUNTRECEIVED] <> fp.[TOTALAMOUNTRECEIVED]  



 /*
merge dbo.[FAFREGISTRANTPENDINGSUMMARY] as t
using (
    select   
     TRANSACTIONAMOUNT,  
     TOTALAMOUNTRECEIVED,  
     EVENTID,  
     CONSTITUENTID ,  
     [ADDEDBYID],  
     [CHANGEDBYID]  
 from @REGTEMP 
) as s
on (t.[EVENTID] = s.[EVENTID] and t.[CONSTITUENTID] = s.[CONSTITUENTID])
when not matched by target
  then insert(
    [TRANSACTIONAMOUNT],
    [TOTALAMOUNTRECEIVED],
    [EVENTID],
    [CONSTITUENTID],
    [ADDEDBYID],
    [CHANGEDBYID]
  )
  values(
    s.[TRANSACTIONAMOUNT],
    s.[TOTALAMOUNTRECEIVED],
    s.[EVENTID],
    s.[CONSTITUENTID],
    s.[CHANGEDBYID],
    s.[ADDEDBYID]
  )
when matched and   t.[TRANSACTIONAMOUNT] <> s.[TRANSACTIONAMOUNT] or t.[TOTALAMOUNTRECEIVED] <> s.[TOTALAMOUNTRECEIVED]
  then update
    set 
    t.[CHANGEDBYID] = s.[CHANGEDBYID],
    t.[TRANSACTIONAMOUNT] = s.[TRANSACTIONAMOUNT],
    t.[TOTALAMOUNTRECEIVED] = s.[TOTALAMOUNTRECEIVED]    ,
      t.[DATECHANGED] = @CURRENTDATE;
    */

  select @NUMBERPROCESSED= isnull(@@ROWCOUNT, 0)


    set @TOPID = ( select top 1 id
    from [FAFREGISTRANTPREVIOUSSUMMARY] )

     set @STARTDATE = ( select coalesce(max(DATECHANGED),'7/4/1776') from [FAFREGISTRANTPREVIOUSSUMMARY] )     

    if @STARTDATE is null or @TOPID is null
        SET @STARTDATE = '7/4/1776 00:00:00';

With DonorsRetained as
(
select  
    f.CONSTITUENTID, 
    f.EVENTID, 
    dbo.UFN_REVENUE_GETDONORRETENTION( f.CONSTITUENTID, f.EVENTID ) PreviousValue
from FAFRAISEDTOTAL f (nolock)
join EVENTEXTENSION e (nolock)
    on f.EVENTID = e.EVENTID
where f.DATECHANGED >= @STARTDATE
and e.PRIORYEAREVENTID is not null
)    

merge dbo.[FAFREGISTRANTPREVIOUSSUMMARY] as t
using (
     select  top 100 percent
         [CONSTITUENTID],
         [EVENTID],
         [PREVIOUSVALUE][DONORRETENTION],
       @CHANGEAGENTID [ADDEDBYID],
       @CHANGEAGENTID [CHANGEDBYID]
    from DonorsRetained
) as s
on (t.[EVENTID] = s.[EVENTID] and t.[CONSTITUENTID] = s.[CONSTITUENTID])
when not matched by target
  then insert(
    [CONSTITUENTID],
    [EVENTID],
    [DONORRETENTION],
    [ADDEDBYID],
    [CHANGEDBYID]
  )
  values(
    s.[CONSTITUENTID],
    s.[EVENTID],
    s.[DONORRETENTION],
    s.[ADDEDBYID],
    s.[CHANGEDBYID]
  )
when matched  and   t.[DONORRETENTION] <> s.[DONORRETENTION] 
  then update
    set 
    t.[CHANGEDBYID] = s.[CHANGEDBYID],
    t.[DONORRETENTION] = s.[DONORRETENTION],
      t.[DATECHANGED] = @CURRENTDATE;

  select @NUMBERPROCESSED = @NUMBERPROCESSED + isnull(@@ROWCOUNT, 0)

end