USP_REVENUESPLIT_UPDATESOLICITORS

CREATE procedure dbo.USP_REVENUESPLIT_UPDATESOLICITORS
(
  @REVENUESPLITID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier,
  @CHANGEDATE datetime,
  @UPDATEOPTION tinyint = 0 -- 0=No update, 1=Update based on defaults

)
as
set nocount on;

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

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

declare @ORGANIZATIONCURRENCYID uniqueidentifier=dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
declare @BASECURRENCYID uniqueidentifier=null;
declare @BASETOORGANIZATIONEXCHANGERATEID uniqueidentifier=null;
declare @AMOUNT money;
declare @DATE datetime;
declare @APPLICATIONTYPE tinyint
declare @CONTEXT_CACHE varbinary(128);

select
  @APPLICATIONTYPE = REVENUESPLIT_EXT.APPLICATIONCODE,
  @DATE = cast(REVENUE.DATE as datetime),
  @BASECURRENCYID = CS.BASECURRENCYID
from
  dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = REVENUESPLIT.ID
  inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUESPLIT.FINANCIALTRANSACTIONID = REVENUE.ID
    inner join dbo.PDACCOUNTSYSTEM PD on PD.ID = REVENUE.PDACCOUNTSYSTEMID
    inner join dbo.CURRENCYSET CS on CS.ID = PD.CURRENCYSETID
where
  REVENUESPLIT.ID = @REVENUESPLITID;

exec dbo.USP_CURRENCY_GETCURRENCYVALUES
  @AMOUNT=null, 
  @DATE=@DATE,
  @BASECURRENCYID=@BASECURRENCYID,
  @BASEEXCHANGERATEID=null,
  @TRANSACTIONCURRENCYID=null,
  @BASEAMOUNT=null,
  @ORGANIZATIONCURRENCYID=@ORGANIZATIONCURRENCYID,
  @ORGANIZATIONAMOUNT=null,
  @ORGANIZATIONEXCHANGERATEID=null,
  @LOOKUPORGANIZATIONEXCHANGERATE=0,
  @BASETOORGANIZATIONEXCHANGERATEID=@BASETOORGANIZATIONEXCHANGERATEID output

/* Store the updated amounts in a temp table for bulk merge */
declare @SOLICITORAMOUNTS table
  (SOLICITORID uniqueidentifier, AMOUNT money, ORGANIZATIONAMOUNT money, SEQUENCE integer)

if @UPDATEOPTION = 1
begin
  if @APPLICATIONTYPE in (
    2, -- Pledge

    7, -- MG Pledge

    6, -- Planned Gift

    8, -- Grant award

    13 -- Donor challenge 

  )
  begin
    /* Calculate the default amounts for solicitors tied to the original pledge */
    insert @SOLICITORAMOUNTS (SOLICITORID, AMOUNT, ORGANIZATIONAMOUNT, SEQUENCE)
    select 
      SOURCESOLICITOR.CONSTITUENTID,
      (SOURCESOLICITOR.AMOUNT*
      dbo.UFN_INSTALLMENTSPLITPAYMENT_GETAMOUNTINCURRENCY(ISP.ID, @BASECURRENCYID)/
      SOURCESPLIT.BASEAMOUNT) AMOUNT,
      case
        when @BASECURRENCYID <> @ORGANIZATIONCURRENCYID
          then dbo.UFN_CURRENCY_CONVERT(
            (SOURCESOLICITOR.AMOUNT*
             dbo.UFN_INSTALLMENTSPLITPAYMENT_GETAMOUNTINCURRENCY(ISP.ID, @BASECURRENCYID)/
             SOURCESPLIT.BASEAMOUNT), @BASETOORGANIZATIONEXCHANGERATEID)
        else (SOURCESOLICITOR.AMOUNT*
              dbo.UFN_INSTALLMENTSPLITPAYMENT_GETAMOUNTINCURRENCY(ISP.ID, @BASECURRENCYID)/
              SOURCESPLIT.BASEAMOUNT)
      end ORGANIZATIONAMOUNT,
      SOURCESOLICITOR.SEQUENCE
    from
      dbo.INSTALLMENTSPLITPAYMENT ISP
      inner join INSTALLMENTSPLIT on ISP.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
      inner join dbo.FINANCIALTRANSACTIONLINEITEM SOURCESPLIT on SOURCESPLIT.FINANCIALTRANSACTIONID = ISP.PLEDGEID
      inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = SOURCESPLIT.ID and REVENUESPLIT_EXT.DESIGNATIONID = INSTALLMENTSPLIT.DESIGNATIONID
      inner join dbo.REVENUESOLICITOR SOURCESOLICITOR on SOURCESPLIT.ID = SOURCESOLICITOR.REVENUESPLITID
    where ISP.PAYMENTID = @REVENUESPLITID
      and SOURCESPLIT.BASEAMOUNT <> 0
            and SOURCESPLIT.DELETEDON is null and SOURCESPLIT.TYPECODE != 1;

    --Temporary table to store Solicitors.        

    declare @TEMPSOLICITOR table
      (SOLICITORID uniqueidentifier, AMOUNT money, ORGANIZATIONAMOUNT money, SEQUENCE integer)

    --Combine amount in case of paying more amount than pledge.

    insert @TEMPSOLICITOR (SOLICITORID, AMOUNT, ORGANIZATIONAMOUNT, SEQUENCE)
    select SOLICITORAMOUNT.SOLICITORID, sum(SOLICITORAMOUNT.AMOUNT), sum(SOLICITORAMOUNT.ORGANIZATIONAMOUNT), max(SOLICITORAMOUNT.SEQUENCE)
    from @SOLICITORAMOUNTS SOLICITORAMOUNT
    group by SOLICITORAMOUNT.SOLICITORID

    delete from @SOLICITORAMOUNTS where SOLICITORID in (select SOLICITORID from @TEMPSOLICITOR)

    insert @SOLICITORAMOUNTS (SOLICITORID, AMOUNT, ORGANIZATIONAMOUNT, SEQUENCE)
    select TEMPSOLICITOR.SOLICITORID, TEMPSOLICITOR.AMOUNT, TEMPSOLICITOR.ORGANIZATIONAMOUNT, TEMPSOLICITOR.SEQUENCE 
    from @TEMPSOLICITOR TEMPSOLICITOR

  end
  else if @APPLICATIONTYPE = 3
  begin   
    insert @SOLICITORAMOUNTS (SOLICITORID, AMOUNT, ORGANIZATIONAMOUNT, SEQUENCE)
    select
      SOURCESOLICITOR.CONSTITUENTID,
      SOURCESOLICITOR.AMOUNT*PAYMENTSPLIT.BASEAMOUNT/SOURCESPLIT.BASEAMOUNT,
      case
        when @BASECURRENCYID <> @ORGANIZATIONCURRENCYID
          then dbo.UFN_CURRENCY_CONVERT(SOURCESOLICITOR.AMOUNT*PAYMENTSPLIT.BASEAMOUNT/SOURCESPLIT.BASEAMOUNT, @BASETOORGANIZATIONEXCHANGERATEID)
        else SOURCESOLICITOR.AMOUNT*PAYMENTSPLIT.BASEAMOUNT/SOURCESPLIT.BASEAMOUNT
      end,
      SOURCESOLICITOR.SEQUENCE
    from
      dbo.RECURRINGGIFTACTIVITY RGA 
      inner join dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTSPLIT on RGA.PAYMENTREVENUEID = PAYMENTSPLIT.ID
            inner join dbo.REVENUESPLIT_EXT PX on PX.ID = PAYMENTSPLIT.ID
      inner join dbo.FINANCIALTRANSACTIONLINEITEM SOURCESPLIT on SOURCESPLIT.FINANCIALTRANSACTIONID = RGA.SOURCEREVENUEID
            inner join dbo.REVENUESPLIT_EXT SX on SX.ID = SOURCESPLIT.ID
      inner join dbo.REVENUESOLICITOR SOURCESOLICITOR on SOURCESPLIT.ID = SOURCESOLICITOR.REVENUESPLITID
    where RGA.PAYMENTREVENUEID = @REVENUESPLITID
      and SOURCESPLIT.BASEAMOUNT <> 0
            and SOURCESPLIT.DELETEDON is null and SOURCESPLIT.TYPECODE != 1
            and PAYMENTSPLIT.DELETEDON is null and PAYMENTSPLIT.TYPECODE != 1;      
  end
  else if @APPLICATIONTYPE in (1,5)
  begin
    set @CONTEXT_CACHE = CONTEXT_INFO();
    set context_info @CHANGEAGENTID;

    delete from dbo.REVENUESOLICITOR where REVENUESPLITID = @REVENUESPLITID

    if @CONTEXT_CACHE is not null
      set context_info @CONTEXT_CACHE;
  end
end
else -- Previous functionality: ensure the solicitor credit is valid by reducing it if it exceeds the application amount

begin
  insert @SOLICITORAMOUNTS (SOLICITORID, AMOUNT, ORGANIZATIONAMOUNT, SEQUENCE)
  select
    REVENUESOLICITOR.CONSTITUENTID,
    case
      when REVENUESOLICITOR.AMOUNT > REVENUESPLIT.BASEAMOUNT
        then REVENUESPLIT.BASEAMOUNT
      else REVENUESOLICITOR.AMOUNT
    end,
    case 
      when REVENUESOLICITOR.AMOUNT > REVENUESPLIT.BASEAMOUNT
        then REVENUESPLIT.ORGAMOUNT
      else REVENUESOLICITOR.ORGANIZATIONAMOUNT end,
    REVENUESOLICITOR.SEQUENCE
  from
    dbo.REVENUESOLICITOR
    inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESOLICITOR.REVENUESPLITID = REVENUESPLIT.ID
  where
    REVENUESPLIT.ID = @REVENUESPLITID
end

set @CONTEXT_CACHE = CONTEXT_INFO();
set context_info @CHANGEAGENTID;

merge dbo.REVENUESOLICITOR as [TARGET]
using @SOLICITORAMOUNTS as [SOURCE]
on [SOURCE].SOLICITORID = [TARGET].CONSTITUENTID
  and [TARGET].REVENUESPLITID = @REVENUESPLITID
when matched then
  update set
    AMOUNT = [SOURCE].AMOUNT,
    ORGANIZATIONAMOUNT=[SOURCE].ORGANIZATIONAMOUNT,
    CHANGEDBYID=@CHANGEAGENTID, DATECHANGED=@CHANGEDATE
when not matched by target then
  insert (ID, REVENUESPLITID, CONSTITUENTID, AMOUNT, SEQUENCE,
    BASECURRENCYID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID,
    ADDEDBYID, DATEADDED, CHANGEDBYID, DATECHANGED)
  values (newid(), @REVENUESPLITID, [SOURCE].SOLICITORID, [SOURCE].AMOUNT, [SOURCE].SEQUENCE,
    @BASECURRENCYID, [SOURCE].ORGANIZATIONAMOUNT, @BASETOORGANIZATIONEXCHANGERATEID,
    @CHANGEAGENTID, @CHANGEDATE, @CHANGEAGENTID, @CHANGEDATE);

  merge dbo.REVENUESOLICITOR as [TARGET]
using @SOLICITORAMOUNTS as [SOURCE]
on [SOURCE].SOLICITORID = [TARGET].CONSTITUENTID
  and [TARGET].REVENUESPLITID = @REVENUESPLITID
when not matched by source and [TARGET].REVENUESPLITID = @REVENUESPLITID
  then delete;

if @CONTEXT_CACHE is not null
  set context_info @CONTEXT_CACHE;

return 0;