USP_FAFINCENTIVEBENEFIT_CALCULATE

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

declare @CHANGEAGENTID uniqueidentifier, @CURRENTDATE datetime;
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
set @CURRENTDATE = GETDATE();
set @NUMBERPROCESSED = 0;

begin try

  IF EXISTS ( SELECT * FROM tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id( N'tempdb..'+'#TEMP_GETREGISTRANT_EVENT_FAFINCENTIVELEVEL_BENEFITS') )  
    DROP TABLE #TEMP_GETREGISTRANT_EVENT_FAFINCENTIVELEVEL_BENEFITS

  CREATE TABLE #TEMP_GETREGISTRANT_EVENT_FAFINCENTIVELEVEL_BENEFITS
  (
    prim_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    REGISTRANTID uniqueidentifier NOT NULL,
    [Total amount raised] money,
    PROGRESSMEMBERECRUITMENTGOAL money,
    PROGRESSDONORRETENTIONGOAL decimal(5, 2),
    PROGRESSCOMMUNICATIONGOAL int,
    EVENTID uniqueidentifier,
    BENEFITSWAIVED bit,

    FAFINCENTIVELEVELID uniqueidentifier,
    TYPECODE tinyint,
    MAXIMUMAMOUNT MONEY,
    BENEFITID uniqueidentifier,
    INCENTIVENAME varchar(100),
    BENEFITNAME varchar(100),
    MAXIMUMNUMBER integer,
    MAXIMUMRETENTION decimal(6, 5),

    ALLOWPARTICIPANTSWAIVEBENEFITS BIT,
    ALLOWPARTICIPANTONLYRECEIVEBENEFITLEVELCODE TINYINT,

    MAXIMUMALL decimal(20, 5),

    QUANTITY int,
    UNITVALUE money,
    TOTALVALUE money,
    DETAILS nvarchar(260),
    BASECURRENCYID uniqueidentifier
  )

  ---------------------------


  insert into #TEMP_GETREGISTRANT_EVENT_FAFINCENTIVELEVEL_BENEFITS
    select
      R.ID,
      dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL(R.ID, R.EVENTID) AS [Total amount raised],
      ((SELECT  COUNT(fc.TYPEGUID) from FAFCOMMUNICATIONSLOG fl join FAFEVENTCOMMUNICATIONCHANNEL fc on fl.EMAILJOBID = fc.EMAILJOBID 
          WHERE fl.CLIENTUSERSID IN (SELECT dbo.UFN_CLIENTUSERID_GET_BY_CONSTITUENT (R.CONSTITUENTID)) AND EVENTID= R.EVENTID)) AS PROGRESSMEMBERECRUITMENTGOAL,
      (SELECT PERCENTAGEOFDONORSRETAINED FROM V_QUERY_REGISTRANT_FUNDRAISINGTOTAL WHERE ID= R.ID) AS PROGRESSDONORRETENTIONGOAL,
      dbo.UFN_FAFINCENTIVEBENEFIT_COMMUNICATIONS(R.ID) AS PROGRESSCOMMUNICATIONGOAL,
      R.EVENTID,
      R.BENEFITSWAIVED,
      FI.ID,
      FI.TYPECODE,
      FI.MAXIMUMAMOUNT,
      FI.BENEFITID,
      FI.INCENTIVENAME,
      FI.BENEFITNAME,
      FI.MAXIMUMNUMBER,
      FI.MAXIMUMRETENTION,
      EA.ALLOWPARTICIPANTSWAIVEBENEFITS,
      EA.ALLOWPARTICIPANTONLYRECEIVEBENEFITLEVELCODE,
      case when TYPECODE = 1 then MAXIMUMAMOUNT when TYPECODE = 2 or TYPECODE = 4 then MAXIMUMNUMBER when TYPECODE = 3 then MAXIMUMRETENTION end as MAXIMUMALL,
      FI.QUANTITY,
      FI.UNITVALUE,
      FI.[TOTALVALUE],
      FI.DETAILS,
      E.BASECURRENCYID
    from
      REGISTRANT R
      join 
      (
        select
          FL.ID,
          FL.TYPECODE,
          FL.MAXIMUMAMOUNT,
          FLB.BENEFITID,
          FL.NAME AS INCENTIVENAME,
          B.NAME AS BENEFITNAME,
          FL.MAXIMUMNUMBER,
          FL.MAXIMUMRETENTION,
          FL.EVENTID,
          FLB.QUANTITY,
          B.VALUE as UNITVALUE,
          B.VALUE * FLB.QUANTITY as [TOTALVALUE],
          B.[DESCRIPTION] AS DETAILS
        from
          FAFINCENTIVELEVEL FL
          JOIN FAFINCENTIVELEVELBENEFIT FLB ON FL.ID = FLB.FAFINCENTIVELEVELID
          JOIN BENEFIT B ON FLB.BENEFITID= B.ID
      ) FI ON FI.EVENTID = R.EVENTID
      LEFT join EVENT E ON E.ID = R.EVENTID
      LEFT JOIN EVENTATTRIBUTES EA ON EA.EVENTID = R.EVENTID
    WHERE
      FI.ID IS NOT NULL AND EA.ID IS NOT NULL AND E.ISACTIVE = 1

  delete from REGISTRANTBENEFIT where REGISTRANTID IN (SELECT REGISTRANTID FROM #TEMP_GETREGISTRANT_EVENT_FAFINCENTIVELEVEL_BENEFITS) AND BENEFITTYPECODE > 0 AND BENEFITTYPECODE < 5

  DELETE FROM #TEMP_GETREGISTRANT_EVENT_FAFINCENTIVELEVEL_BENEFITS
  WHERE prim_id NOT IN
  (
    SELECT prim_id FROM #TEMP_GETREGISTRANT_EVENT_FAFINCENTIVELEVEL_BENEFITS
    WHERE
    (
      (TYPECODE = 1 AND [Total amount raised] >= MAXIMUMAMOUNT)
      OR
      (TYPECODE = 2 AND PROGRESSMEMBERECRUITMENTGOAL >= MAXIMUMNUMBER)
      OR
      (TYPECODE = 3 AND PROGRESSDONORRETENTIONGOAL >= MAXIMUMRETENTION * 100)
      OR
      (TYPECODE = 4 AND PROGRESSCOMMUNICATIONGOAL >= MAXIMUMNUMBER)
    )
  )

  Insert into REGISTRANTBENEFIT (ID, REGISTRANTID, BENEFITID, UNITVALUE, QUANTITY, TOTALVALUE, DETAILS, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,
    BASECURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONEXCHANGERATEID, EVENTBASECURRENCYTOTALVALUE, ORGANIZATIONTOTALVALUE, BENEFITTYPECODE)

    select newid(), REGISTRANTID, BENEFITID, UNITVALUE, QUANTITY, TOTALVALUE, DETAILS, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE,
      BASECURRENCYID, null, null, TOTALVALUE, TOTALVALUE, TYPECODE
    from #TEMP_GETREGISTRANT_EVENT_FAFINCENTIVELEVEL_BENEFITS
    WHERE ALLOWPARTICIPANTONLYRECEIVEBENEFITLEVELCODE = 0

    union all

    select newid(), TM.REGISTRANTID, BENEFITID, UNITVALUE, QUANTITY, TOTALVALUE, DETAILS, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE,
      BASECURRENCYID, null, null, TOTALVALUE, TOTALVALUE, TM.TYPECODE
    FROM #TEMP_GETREGISTRANT_EVENT_FAFINCENTIVELEVEL_BENEFITS TM
    JOIN 
    (
      SELECT
        REGISTRANTID,
        TYPECODE,
        MAX(MAXIMUMALL) as MAXIMUMALL
      FROM #TEMP_GETREGISTRANT_EVENT_FAFINCENTIVELEVEL_BENEFITS
      group by REGISTRANTID, TYPECODE
    ) MX ON MX.REGISTRANTID = TM.REGISTRANTID AND MX.TYPECODE = TM.TYPECODE AND MX.MAXIMUMALL = TM.MAXIMUMALL
    WHERE ALLOWPARTICIPANTONLYRECEIVEBENEFITLEVELCODE = 1

  select @NUMBERPROCESSED = count(*) from REGISTRANTBENEFIT 
  where REGISTRANTID IN (SELECT REGISTRANTID FROM #TEMP_GETREGISTRANT_EVENT_FAFINCENTIVELEVEL_BENEFITS) AND BENEFITTYPECODE > 0 AND BENEFITTYPECODE < 5

end try
begin catch
  exec dbo.USP_RAISE_ERROR;
end catch

end