USP_DATAFORMTEMPLATE_EDITSAVE_REVENUETRANSACTIONCHANGECONSTITUENT_4

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITSAVE_REVENUETRANSACTIONCHANGECONSTITUENT_4
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @NEWCONSTITUENTID uniqueidentifier,
  @CONSTITUENTACCOUNTID uniqueidentifier,
  @RESETRECOGNITIONCREDITS bit,
  @RESETSOLICITORS bit,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @RESETMATCHINGGIFTCLAIMS bit,
  @SEPAMANDATEID uniqueidentifier
)
as
  set nocount on;

  -- Verify the new constituent isn't the same as the previous one

  declare @PREVIOUSCONSTITUENTID uniqueidentifier;
  declare @TRANSACTIONTYPECODE tinyint;
  declare @POSTSTATUSCODE tinyint;

  select top 1 
    @PREVIOUSCONSTITUENTID = CONSTITUENTID,
    @TRANSACTIONTYPECODE = TYPECODE,
    @POSTSTATUSCODE = POSTSTATUSCODE
  from dbo.FINANCIALTRANSACTION
  where ID = @ID;

  if @PREVIOUSCONSTITUENTID = @NEWCONSTITUENTID
  begin
    raiserror('NEWCONSTITUENTCANNOTBECURRENTCONSTITUENT', 13, 1);
    return 1;
  end

  -- Verify the user has access to the new constituent

  declare @DATAFORMINSTANCEID uniqueidentifier;
  set @DATAFORMINSTANCEID = '41EB5B17-BD5C-49B6-9538-CB360FA95EB4';
  declare @ISADMIN bit;
  set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

  if (@ISADMIN = 0)
    begin
      select top(1) ID
      from CONSTITUENT
      where
      CONSTITUENT.ID = @NEWCONSTITUENTID
      and(
        dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORCONSTIT(@CURRENTAPPUSERID, @DATAFORMINSTANCEID, CONSTITUENT.ID) = 1
        and
        exists(select SITEID from dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(CONSTITUENT.ID)
            where dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORSITE(@CURRENTAPPUSERID, @DATAFORMINSTANCEID, SITEID) = 1)
      )

      if @@ROWCOUNT = 0
        begin
          raiserror('BBERR_NEWCONSTITUENT_NOACCESS',13,1);
          return 0;
        end
    end

  declare @CHANGEDATE datetime;
  set @CHANGEDATE = getdate();

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

  -- Get payment code

  declare @PAYMENTMETHODCODE tinyint, @REVENUEPAYMENTMETHODID uniqueidentifier;
  select 
    @PAYMENTMETHODCODE = PAYMENTMETHODCODE,
    @REVENUEPAYMENTMETHODID = ID
  from 
    dbo.REVENUEPAYMENTMETHOD 
  where
    REVENUEID = @ID;

  -- Verify the transaction hasn't already been posted

  declare @ISPOSTED bit;
  select
    @ISPOSTED = 
    case 
      when exists (select 1 from dbo.REVENUEPOSTED RP where RP.ID = @ID) then 1
      else 0
    end

  if @ISPOSTED = 1
  begin
    raiserror('TRANSACTIONCANNOTBEPOSTED', 13, 1);
    return 1;
  end

  -- Direct debit account is required for payments

  if @TRANSACTIONTYPECODE = 0 and @CONSTITUENTACCOUNTID is null
  begin
    if @PAYMENTMETHODCODE = 3 -- Direct debit

    begin
      raiserror('BBERR_ACCOUNTREQUIREDFORDIRECTDEBITPAYMENTS', 13, 1);
      return 1;
    end
    else if @PAYMENTMETHODCODE = 11 -- Standing order

    begin
      raiserror('BBERR_ACCOUNTREQUIREDFORSTANDINGORDERPAYMENTS', 13, 1);
      return 1;         
    end
  end

  begin try

    declare @contextCache varbinary(128);

    --cache current context information

    set @contextCache = CONTEXT_INFO();

    --set CONTEXT_INFO to @CHANGEAGENTID

    set CONTEXT_INFO @CHANGEAGENTID;

    exec dbo.USP_REVENUE_UPDATERERECEIPTS @ID, @CHANGEAGENTID, @CHANGEDATE;

    -- begin WI 292233

    -- update the acknowledgee on letters that have not been sent

    update
      dbo.REVENUELETTER
    set
      REVENUELETTER.ACKNOWLEDGEEID = @NEWCONSTITUENTID,
      REVENUELETTER.CHANGEDBYID = @CHANGEAGENTID, 
      REVENUELETTER.DATECHANGED = @CHANGEDATE
    where
      REVENUELETTER.REVENUEID = @ID
      and REVENUELETTER.ACKNOWLEDGEEID = @PREVIOUSCONSTITUENTID
      and (REVENUELETTER.PROCESSDATE is null and REVENUELETTER.ACKNOWLEDGEDATE is null);

    -- if we're going to be resending letters, update the acknowledgee on letters that haven't been sent

    -- (if we're not going to be resending letters then the sent letters should reflect the state they were in when they were sent)

    if (coalesce((select top 1 REACKNOWLEDGEREVENUE from dbo.ACKNOWLEDGEMENTPREFERENCE), 0)) = 1
      update
        dbo.REVENUELETTER
      set
        REVENUELETTER.ACKNOWLEDGEEID = @NEWCONSTITUENTID,
        REVENUELETTER.CHANGEDBYID = @CHANGEAGENTID, 
        REVENUELETTER.DATECHANGED = @CHANGEDATE
      where
        REVENUELETTER.REVENUEID = @ID
        and REVENUELETTER.ACKNOWLEDGEEID = @PREVIOUSCONSTITUENTID
        and (REVENUELETTER.PROCESSDATE is not null or REVENUELETTER.ACKNOWLEDGEDATE is not null);
    -- end WI 292233


    exec dbo.USP_REVENUELETTER_SETREACKNOWLEDGEMENTS @ID, @CHANGEAGENTID, @CHANGEDATE;

    -- Gift Aid is for UK only

    if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1
      -- Create manual refunds for any claimed revenue splits

      exec dbo.USP_REVENUE_CREATEREFUNDS @ID, 1, @CHANGEAGENTID, @CHANGEDATE;

    -- Update the constituent for all revenue entries belonging to the transaction

    update dbo.FINANCIALTRANSACTION set 
      CONSTITUENTID = @NEWCONSTITUENTID,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CHANGEDATE
    where FINANCIALTRANSACTION.ID = @ID;

    update dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT
    set CONSTITUENTID = @NEWCONSTITUENTID,
    CHANGEDBYID = @CHANGEAGENTID,
    DATECHANGED = @CHANGEDATE
    from dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEMADJUSTMENT.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
    where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
    and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 1
    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
    and FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0

    if @RESETRECOGNITIONCREDITS = 1
    begin
      --Cache CONTEXT INFO

      set @contextCache = CONTEXT_INFO();

      if not @CHANGEAGENTID is null
        set CONTEXT_INFO @CHANGEAGENTID;

      -- Remove previous recognition credits

      delete from dbo.REVENUERECOGNITION where REVENUESPLITID in (select ID from dbo.FINANCIALTRANSACTIONLINEITEM where FINANCIALTRANSACTIONID = @ID);

      --Remove other recognition credits

      delete dbo.RECOGNITIONCREDIT
      from dbo.RECOGNITIONCREDIT
      inner join dbo.DONORCHALLENGEENCUMBERED on RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID = DONORCHALLENGEENCUMBERED.ID
      where RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 1
        and DONORCHALLENGEENCUMBERED.REVENUESPLITID in (select ID from dbo.FINANCIALTRANSACTIONLINEITEM where FINANCIALTRANSACTIONID = @ID);

      --Remove credits on the donor challenge claim to match edit form

      delete dbo.REVENUERECOGNITION
      from dbo.REVENUERECOGNITION
      inner join dbo.FINANCIALTRANSACTIONLINEITEM on REVENUERECOGNITION.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
      inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
      inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
      inner join dbo.DONORCHALLENGEENCUMBERED on FINANCIALTRANSACTION.ID = DONORCHALLENGEENCUMBERED.MATCHEDREVENUEID
      where DONORCHALLENGEENCUMBERED.REVENUESPLITID in (select ID from dbo.FINANCIALTRANSACTIONLINEITEM where FINANCIALTRANSACTIONID = @ID)
        and FINANCIALTRANSACTION.CONSTITUENTID <> REVENUERECOGNITION.CONSTITUENTID
        and REVENUESPLIT_EXT.DESIGNATIONID = DONORCHALLENGEENCUMBERED.DESIGNATIONID;

      --Restore CONTEXT_INFO

      if not @contextCache is null
        set CONTEXT_INFO @contextCache;

      -- Create the new default recognition credits

      insert into dbo.REVENUERECOGNITION
      (
        REVENUESPLITID, 
        CONSTITUENTID, 
        AMOUNT,
        REVENUERECOGNITIONTYPECODEID,
        EFFECTIVEDATE,
        ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED,
        ORGANIZATIONAMOUNT,
        BASECURRENCYID,
        ORGANIZATIONEXCHANGERATEID
      )
      select
        RS.ID,
        RR.CONSTITUENTID,
        RR.AMOUNT,
        RR.REVENUERECOGNITIONTYPECODEID,
        FT.DATE,
        @CHANGEAGENTID,@CHANGEAGENTID,@CHANGEDATE,@CHANGEDATE,
        FT.ORGAMOUNT,
        V.BASECURRENCYID,
        FT.ORGEXCHANGERATEID
      from dbo.FINANCIALTRANSACTION FT
      inner join REVENUE_EXT RE on RE.ID = FT.ID
      inner join dbo.FINANCIALTRANSACTIONLINEITEM RS
        on RS.FINANCIALTRANSACTIONID = FT.ID
      left join dbo.RECURRINGGIFTACTIVITY RGA on RGA.PAYMENTREVENUEID = FT.ID
      inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V on V.FINANCIALTRANSACTIONID = FT.ID
      cross apply dbo.UFN_REVENUEDETAIL_GETDEFAULTRECOGNITIONS(RE.GIVENANONYMOUSLY, FT.CONSTITUENTID, RS.BASEAMOUNT, FT.DATE, RGA.SOURCEREVENUEID) RR
      where FT.ID = @ID;
    end

    if @PAYMENTMETHODCODE in (3, 11) -- Direct debit or standing order

    begin
      if @TRANSACTIONTYPECODE in (1, 2) -- Pledge or recurring gift

      begin
        if @CONSTITUENTACCOUNTID is not null
        begin
          if @PAYMENTMETHODCODE = 3 -- Direct debit

          begin
            update dbo.REVENUESCHEDULEDIRECTDEBITPAYMENT set
              CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID,
              SEPAMANDATEID = @SEPAMANDATEID,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CHANGEDATE
            where REVENUESCHEDULEDIRECTDEBITPAYMENT.ID = @ID;
          end
          else if @PAYMENTMETHODCODE = 11 -- Standing order

          begin
            update dbo.REVENUESCHEDULESTANDINGORDERPAYMENT set
              CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CHANGEDATE
            where REVENUESCHEDULESTANDINGORDERPAYMENT.ID = @ID;
          end
        end
        else
        begin
          -- No longer autopay

          update dbo.REVENUEPAYMENTMETHOD set
            PAYMENTMETHODCODE = 9,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CHANGEDATE
          where 
            REVENUEID = @ID;

          -- Delete entry

          exec dbo.USP_REVENUESCHEDULEDIRECTDEBITPAYMENT_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;
        end
      end
      else if @TRANSACTIONTYPECODE = 0 -- Payment

      begin
        if @PAYMENTMETHODCODE = 3 -- Direct debit

        begin

          --Run this before we update the payment method details, because we need to see if the SEPA Mandate has changed

          exec dbo.USP_SEPAMANDATE_PAYMENTMADE 
            @SEPAMANDATEID, 
            null, --BATCHROWID

            @ID, --REVENUEID

            @CHANGEAGENTID;

          update dbo.DIRECTDEBITPAYMENTMETHODDETAIL set
            CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID,
            SEPAMANDATEID = @SEPAMANDATEID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CHANGEDATE
          where DIRECTDEBITPAYMENTMETHODDETAIL.ID = @REVENUEPAYMENTMETHODID;
        end
        else if @PAYMENTMETHODCODE = 11 -- Standing order

        begin
          update dbo.STANDINGORDERPAYMENTMETHODDETAIL set 
            CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CHANGEDATE
          where ID = @REVENUEPAYMENTMETHODID;
        end
      end
    end

    -- Clear GL

    delete from dbo.REVENUEGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0; 
    delete from dbo.WRITEOFFGLDISTRIBUTION where WRITEOFFID in (select WO.ID from dbo.WRITEOFF WO where WO.REVENUEID = @ID) and OUTDATED = 0;
    delete from dbo.STOCKSALEGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;
    delete from dbo.PROPERTYDETAILGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;
    delete from dbo.BENEFITGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;
    delete from dbo.GIFTINKINDSALEGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;

    if @TRANSACTIONTYPECODE = 0
    begin
      delete from dbo.GLTRANSACTION
      where GLTRANSACTION.ID in (select GIFTFEEGLDISTRIBUTION.GLTRANSACTIONID 
        from dbo.GIFTFEEGLDISTRIBUTION
        inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = GIFTFEEGLDISTRIBUTION.REVENUESPLITGIFTFEEID
        where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID and GIFTFEEGLDISTRIBUTION.OUTDATED = 0);

      delete from dbo.REVENUESPLITGIFTFEE
      where REVENUESPLITGIFTFEE.ID in (select FINANCIALTRANSACTIONLINEITEM.ID from dbo.FINANCIALTRANSACTIONLINEITEM where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID);

      exec dbo.USP_PAYMENT_ADDGIFTFEES @ID, @NEWCONSTITUENTID, @CHANGEAGENTID, @CHANGEDATE;
    end

    -- Add new GL distributions

    if @POSTSTATUSCODE <> 3 -- 3: Do not post

    begin
      -- Add new GL distributions

      exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CHANGEDATE;

      -- Add new stock detail GL distributions

      if @PAYMENTMETHODCODE = 4 and exists(select ID from dbo.STOCKSALE where STOCKDETAILID = @REVENUEPAYMENTMETHODID)
        exec dbo.USP_SAVE_STOCKDETAILGLDISTRIBUTION @ID, @CHANGEAGENTID, @CHANGEDATE;

      -- Add new gift-in-kind detail GL distributions

      if @PAYMENTMETHODCODE = 6 and exists(select ID from dbo.GIFTINKINDSALE where GIFTINKINDPAYMENTMETHODDETAILID = @REVENUEPAYMENTMETHODID)
        exec dbo.USP_SAVE_GIFTINKINDPAYMENTMETHODDETAILGLDISTRIBUTION @ID, @CHANGEAGENTID, @CHANGEDATE;


      -- Add new property detail GL distributions

      if @PAYMENTMETHODCODE = 5 and exists(select ID from dbo.PROPERTYDETAIL where ID = @REVENUEPAYMENTMETHODID and ISSOLD = 1)
        exec dbo.USP_SAVE_PROPERTYDETAILGLDISTRIBUTION @ID, @CHANGEAGENTID, @CHANGEDATE;

      -- Add new write-off GL distributions

      if @TRANSACTIONTYPECODE = 1
        exec dbo.USP_SAVE_WRITEOFFGLDISTRIBUTION @ID, @CHANGEAGENTID, @CHANGEDATE;

      -- Add new benefit GL distributions

      if @TRANSACTIONTYPECODE <> 15 --Membership installment plan

        exec dbo.USP_SAVE_BENEFITGLDISTRIBUTION_FINANCIALTRANSACTION @ID, @CHANGEAGENTID, @CHANGEDATE;

      declare @DEPOSITID uniqueidentifier;
      select @DEPOSITID = DEPOSITID
      from dbo.BANKACCOUNTDEPOSITPAYMENT
      where ID = @ID;

      if @DEPOSITID is not null
        exec dbo.USP_BANKACCOUNTDEPOSIT_OVERWRITEPAYMENTDEBITACCOUNTS @ID, @DEPOSITID, @CHANGEAGENTID, @CHANGEDATE;
    end

    --reset CONTEXT_INFO to previous value

    if not @contextCache is null
      set CONTEXT_INFO @contextCache;

    if @RESETMATCHINGGIFTCLAIMS = 1
    begin
      -- Create default matching gifts for the new constituent

      exec USP_REVENUE_MATCHINGGIFT_CHANGECONSTITUENT @ID, @CHANGEAGENTID, @CHANGEDATE, @CURRENTAPPUSERID;
    end

    if @TRANSACTIONTYPECODE = 2 -- only do this for recurring gifts

        insert into dbo.RECURRINGGIFTAMENDMENT(ID,FINANCIALTRANSACTIONID,AMENDMENTTYPECODE,DATE,CONSTITUENTID,PREVIOUSCONSTITUENTID,
                                                ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
        values(newid(),@ID,4,getdate(),@NEWCONSTITUENTID,@PREVIOUSCONSTITUENTID,@CHANGEAGENTID,@CHANGEAGENTID,@CHANGEDATE,@CHANGEDATE)

  end try
  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;