USP_FAFREGISTRANTBENEFIT_CALCULATE

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

 set @NUMBERPROCESSED = 0;     

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

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

   declare @EXECUTIONDATE datetime    

 -- This will come from SCHEDULE         

  set @EXECUTIONDATE = ( select coalesce(max(DATECHANGED),'1/1/1977') from [FAFREGISTRANTBENEFITSUMMARY] )        
  -- This will come from SCHEDULE         


-- Every night incentives get recreated, can't use audit table to keep track 

--delete [FAFREGISTRANTBENEFITSUMMARY] where [BENEFITTYPE] = 1




create table #BenefitData      
(       
 REGISTRANTID uniqueidentifier,        
 BENEFITID uniqueidentifier    ,    
 BENEFITTYPECODE int,  
 CONSTITUENTID uniqueidentifier,  
 EVENTID uniqueidentifier     
)      


insert into #BenefitData         
 select   
 regb.REGISTRANTID,   
 ben.ID [BENEFITID],   
 regb.[BENEFITTYPECODE],  
 r.[CONSTITUENTID],  
 r.[EVENTID]         
 from REGISTRANTBENEFIT regb (nolock)       
 inner join Benefit ben (nolock)       
 on ben.[ID] = regb.[BENEFITID]   
 inner join [REGISTRANT] r (nolock)  
 on r.[ID] = regb.[REGISTRANTID]    
 where   regb.BENEFITTYPECODE in (0)    and     
 ( regb.[DATECHANGED] > @EXECUTIONDATE or        
 ben.[DATECHANGED] > @EXECUTIONDATE )  

 union

 select   
 regb.REGISTRANTID,   
 ben.ID [BENEFITID],   
 regb.[BENEFITTYPECODE],  
 r.[CONSTITUENTID],  
 r.[EVENTID]         
 from REGISTRANTBENEFIT regb (nolock)       
 inner join Benefit ben (nolock)       
 on ben.[ID] = regb.[BENEFITID]   
 inner join [REGISTRANT] r (nolock)  
 on r.[ID] = regb.[REGISTRANTID]    
 where   regb.BENEFITTYPECODE in (1)  

 create index IX_I on #BenefitData ( REGISTRANTID, BENEFITID )     


create table #BenefitSummary     
(       
 REGISTRANTID uniqueidentifier,        
 EVENTID uniqueidentifier,    
 CONSTITUENTID uniqueidentifier,    
 BENEFITNAME nvarchar(200) collate DATABASE_DEFAULT,    
 TOTALVALUE money,    
 BENEFITID uniqueidentifier,      
 BENEFITS nvarchar(800) collate DATABASE_DEFAULT,    
 DATECHANGED datetime,    
 DATEADDED datetime,    
 ADDEDBYID uniqueidentifier,    
 CHANGEDBYID uniqueidentifier,    
 BENEFITTYPE int    
)         

insert into #BenefitSummary     
select   
 r.[ID] [REGISTRANTID],         
 r.[EVENTID],         
 r.[CONSTITUENTID],         
 b.[NAME] [BENEFITNAME],         
 rb.[TOTALVALUE],         
 b.[ID] [BENEFITID],        
 B.[NAME] + ' (' +    

 coalesce( CAST ( stuff (        
 (        
  select ',' +  rbex.[BENEFITPREFERENCE]         
  from [REGISTRANTBENEFITEXTENSION] rbex         
  where rbex.[BENEFITID] = rb.[BENEFITID] and rbex.REGISTRANTID = rb.REGISTRANTID           
  for xml path('')        
 ),1,1,'') as nvarchar(500)),'') + ')'  as BENEFITS,  

 @CURRENTDATE [DATECHANGED],        
 @CURRENTDATE [DATEADDED],        
 @CHANGEAGENTID [ADDEDBYID],        
 @CHANGEAGENTID [CHANGEDBYID]  ,    
 bd.BENEFITTYPECODE      
 from [REGISTRANT] r (nolock)        
 inner join [REGISTRANTBENEFIT] rb (nolock)        
  on r.[ID] = rb.[REGISTRANTID]            
 inner join [BENEFIT] b (nolock)        
  on b.[ID] = rb.[BENEFITID]            
 inner join #BenefitData bd         
  on bd.registrantid = rb.REGISTRANTID and bd.benefitid = rb.BENEFITID  and bd.benefittypecode = rb.benefittypecode                
 where rb.BENEFITTYPECODE in (0,1)           
create index IX_BENETIFDATA_REGISTRANTIDBENEFITID on #BenefitData ( REGISTRANTID, BENEFITID )     


/*
delete [FAFREGISTRANTBENEFITSUMMARY]  
from [FAFREGISTRANTBENEFITSUMMARY] f  
join #BenefitSummary b  
 on f.[CONSTITUENTID] = b.[CONSTITUENTID] and f.[EVENTID] = b.[EVENTID] and f.[BENEFITTYPE] = b.[BENEFITTYPE]   
*/
create table #FAFREGISTRANTBENEFITSUMMARY_STAGE
(
    [EVENTID] [uniqueidentifier],
    [CONSTITUENTID] [uniqueidentifier] ,
    [BENEFITS] [nvarchar](500) collate DATABASE_DEFAULT,
    [BENEFITTYPE] [int],
    [TOTALVALUE] [money] ,
    [ADDEDBYID] [uniqueidentifier],
    [CHANGEDBYID] [uniqueidentifier]
)

insert into #FAFREGISTRANTBENEFITSUMMARY_STAGE
( [EVENTID], [CONSTITUENTID], [TOTALVALUE],[BENEFITTYPE],[BENEFITS],[ADDEDBYID],[CHANGEDBYID])  
select    
 summary.[EVENTID],     
 summary.[CONSTITUENTID] ,    
 sum(summary.totalvalue) [TOTALVALUE],    
 summary.[BENEFITTYPE],    
 coalesce( CAST ( stuff (      
 (      
  select ',' +  replace( rbex.[BENEFITS], '()', '')    
  from #BenefitSummary rbex       
  where rbex.[CONSTITUENTID] = summary.[CONSTITUENTID] and rbex.[EVENTID] = summary.[EVENTID]     and rbex.BENEFITTYPE = summary.BENEFITTYPE    
  for xml path('')      
  ),1,1,'') as nvarchar(500)),'')   as BENEFITS,    
 --@CURRENTDATE [DATECHANGED],    

 --@CURRENTDATE [DATEADDED],        

 @CHANGEAGENTID [ADDEDBYID],        
 @CHANGEAGENTID [CHANGEDBYID]      
 from  #BenefitSummary summary    
 group by summary.[EVENTID], summary.[CONSTITUENTID] ,summary.[BENEFITTYPE]    ;


 update fs
    set 
    fs.[TOTALVALUE] = 0,
    fs.[BENEFITS] = ''
 from [FAFREGISTRANTBENEFITSUMMARY] fs
 left join #FAFREGISTRANTBENEFITSUMMARY_STAGE fss
 on fs.CONSTITUENTID = fss.CONSTITUENTID and fs.EVENTID =fss.EVENTID and fs.BENEFITTYPE = fss.BENEFITTYPE 
 where fss.CONSTITUENTID is null
 and fs.BENEFITTYPE = 1
 and (fs.[TOTALVALUE] <> 0 and fs.BENEFITS <> '' )

insert into [FAFREGISTRANTBENEFITSUMMARY] (EVENTID, CONSTITUENTID, TOTALVALUE, BENEFITTYPE, BENEFITS, DATECHANGED, DATEADDED, ADDEDBYID , CHANGEDBYID )
select    
 s.[EVENTID],     
 s.[CONSTITUENTID],     
 s.[TOTALVALUE],   
 s.[BENEFITTYPE],     
 s.BENEFITS,      
 @CURRENTDATE [DATECHANGED],      
 @CURRENTDATE [DATEADDED],          
 @CHANGEAGENTID [ADDEDBYID],          
 @CHANGEAGENTID  [CHANGEDBYID]    
 from  #FAFREGISTRANTBENEFITSUMMARY_STAGE s
left join [FAFREGISTRANTBENEFITSUMMARY] t
on  t.[EVENTID] = s.[EVENTID] and t.[BENEFITTYPE] = s.[BENEFITTYPE] and t.[CONSTITUENTID] = s.[CONSTITUENTID] 
where t.[CONSTITUENTID] is null

merge [FAFREGISTRANTBENEFITSUMMARY] as t        
using        
(        
select    
 summary.[EVENTID],     
 summary.[CONSTITUENTID] ,    
 summary.[TOTALVALUE],    
 summary.[BENEFITTYPE],    
 summary.BENEFITS,    
 @CURRENTDATE [DATECHANGED],    
 @CURRENTDATE [DATEADDED],        
 @CHANGEAGENTID [ADDEDBYID],        
 @CHANGEAGENTID [CHANGEDBYID]      
 from  #FAFREGISTRANTBENEFITSUMMARY_STAGE summary      
) as s        
on (  t.[EVENTID] = s.[EVENTID] and t.[BENEFITTYPE] = s.[BENEFITTYPE] and t.[CONSTITUENTID] = s.[CONSTITUENTID] )        
/* when not matched by target        
 then insert        
 (          
 [EVENTID],        
 [CONSTITUENTID],        
 [TOTALVALUE],    
 [BENEFITTYPE],    
 [BENEFITS],          
 [DATECHANGED],        
 [DATEADDED],        
 [ADDEDBYID],        
 [CHANGEDBYID]  
 )        
 values        
 (            
 s.[EVENTID],        
 s.[CONSTITUENTID],         
 s.[TOTALVALUE],    
 s.[BENEFITTYPE],         
 s.[BENEFITS],       
 s.[DATECHANGED],        
 s.[DATEADDED],        
 s.[ADDEDBYID],        
 s.[CHANGEDBYID]  
 )        */
 when matched        
 then update        
 set         
 t.[TOTALVALUE] = s.[TOTALVALUE],        
 t.[BENEFITS] =    s.[BENEFITS],         
 t.[DATECHANGED] = s.[DATECHANGED],             
 t.[CHANGEDBYID] = s.[CHANGEDBYID];        



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

  set @NUMBERPROCESSED = isnull(@NUMBERPROCESSED, 0)  


 drop table #BenefitData    
 drop table #BenefitSummary    
 drop table #FAFREGISTRANTBENEFITSUMMARY_STAGE
 end