USP_GLOBALCHANGE_ADDCAMPAIGNTOMEMBERSHIPPROGRAMPAYMENT

CREATE procedure dbo.USP_GLOBALCHANGE_ADDCAMPAIGNTOMEMBERSHIPPROGRAMPAYMENT
(
  @CHANGEAGENTID uniqueidentifier = null,
  @ASOF as datetime = null,
  @NUMBERADDED int = 0 output,
  @NUMBEREDITED int = 0 output,
  @NUMBERDELETED int = 0 output,
  @MEMBERSHIPPROGRAMID uniqueidentifier = null
)

as
  set nocount on;

  set @NUMBERADDED = 0;
  set @NUMBEREDITED = 0;
  set @NUMBERDELETED = 0;

  declare @CURRENTDATE date = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

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

  begin try

    declare @PROGRAM_CAMPAIGNS table
    (
      MEMBERSHIPLEVELID uniqueidentifier,
      CAMPAIGNID uniqueidentifier,
      CAMPAIGNSUBPRIORITYID uniqueidentifier,
      DATEFROM datetime,
      DATETO datetime
    )

    --All the campaigns on the program

    insert into @PROGRAM_CAMPAIGNS
      select
        ML.ID,
        MPC.CAMPAIGNID,
        MPC.CAMPAIGNSUBPRIORITYID,
        MPC.DATEFROM,
        MPC.DATETO
      from
        dbo.MEMBERSHIPPROGRAM MP
      inner join
        dbo.MEMBERSHIPLEVEL ML on ML.MEMBERSHIPPROGRAMID = MP.ID
      inner join
        dbo.MEMBERSHIPPROGRAMCAMPAIGN MPC on MPC.MEMBERSHIPPROGRAMID = MP.ID
      where
        MP.ID = @MEMBERSHIPPROGRAMID and
        ML.OVERRIDECAMPAIGNS = 0

      union all

      select
        ML.ID,
        MLC.CAMPAIGNID,
        MLC.CAMPAIGNSUBPRIORITYID,
        MLC.DATEFROM,
        MLC.DATETO
      from
        dbo.MEMBERSHIPLEVEL ML
      inner join
        dbo.MEMBERSHIPLEVELCAMPAIGN MLC on MLC.MEMBERSHIPLEVELID = ML.ID
      where
        ML.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID and
        ML.OVERRIDECAMPAIGNS = 1;

    declare @EXISTING_MEMBERSHIPS table
    (
      REVENUESPLITID uniqueidentifier,
      MEMBERSHIPLEVELID uniqueidentifier,
      MEMBERSHIPDATE datetime
    );

    --Membership Payments

    insert into @EXISTING_MEMBERSHIPS
    (
      REVENUESPLITID,
      MEMBERSHIPLEVELID,
      MEMBERSHIPDATE
    )
    select distinct
      MST.REVENUESPLITID,
      ML.ID,
      cast(FT.DATE as datetime)
    from
      dbo.MEMBERSHIPTRANSACTION MST
    inner join
      dbo.MEMBERSHIP MS on MS.ID = MST.MEMBERSHIPID
    inner join
      dbo.MEMBERSHIPLEVEL ML on ML.ID = MS.MEMBERSHIPLEVELID
    inner join
      dbo.FINANCIALTRANSACTIONLINEITEM FTLI on MST.REVENUESPLITID = FTLI.ID
    inner join
      dbo.FINANCIALTRANSACTION FT on FTLI.FINANCIALTRANSACTIONID = FT.ID
    where
      MS.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID and
      MST.REVENUESPLITID is not null and
      FTLI.DELETEDON is null and
      FTLI.TYPECODE <> 1 and
      FT.DELETEDON is null;

    --Installment Plan Payments

    insert into @EXISTING_MEMBERSHIPS
    (
      REVENUESPLITID,
      MEMBERSHIPLEVELID,
      MEMBERSHIPDATE
    )
    select distinct
      PAYMENTSPLIT.ID,
      ML.ID,
      cast(PAYMENT.DATE as datetime)
    from
      dbo.MEMBERSHIPTRANSACTION MST
    inner join
      dbo.MEMBERSHIP MS on MS.ID = MST.MEMBERSHIPID
    inner join
      dbo.MEMBERSHIPLEVEL ML on ML.ID = MS.MEMBERSHIPLEVELID
    inner join
      dbo.FINANCIALTRANSACTIONLINEITEM FTLI on MST.REVENUESPLITID = FTLI.ID
    inner join
      dbo.FINANCIALTRANSACTION FT on FTLI.FINANCIALTRANSACTIONID = FT.ID
    inner join
      dbo.INSTALLMENTSPLITPAYMENT ISP on FT.ID = ISP.PLEDGEID
    inner join
      dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTSPLIT on PAYMENTSPLIT.ID = ISP.PAYMENTID
    inner join
      dbo.FINANCIALTRANSACTION PAYMENT on PAYMENTSPLIT.FINANCIALTRANSACTIONID = PAYMENT.ID
    where
      MS.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID and
      MST.REVENUESPLITID is not null and
      FTLI.DELETEDON is null and
      FTLI.TYPECODE <> 1 and
      FT.DELETEDON is null;

    --Recurring Membership Payments

    insert into @EXISTING_MEMBERSHIPS
    (
      REVENUESPLITID,
      MEMBERSHIPLEVELID,
      MEMBERSHIPDATE
    )
    select distinct
      PAYMENTSPLIT.ID,
      ML.ID,
      cast(PAYMENT.DATE as datetime)
    from
      dbo.MEMBERSHIPTRANSACTION MST
    inner join
      dbo.MEMBERSHIP MS on MS.ID = MST.MEMBERSHIPID
    inner join
      dbo.MEMBERSHIPLEVEL ML on ML.ID = MS.MEMBERSHIPLEVELID
    inner join
      dbo.FINANCIALTRANSACTIONLINEITEM FTLI on MST.REVENUESPLITID = FTLI.ID
    inner join
      dbo.FINANCIALTRANSACTION FT on FTLI.FINANCIALTRANSACTIONID = FT.ID
    inner join
      dbo.RECURRINGGIFTACTIVITY RGA on FT.ID = RGA.SOURCEREVENUEID
    inner join
      dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTSPLIT on RGA.PAYMENTREVENUEID = PAYMENTSPLIT.ID
    inner join
      dbo.FINANCIALTRANSACTION PAYMENT on PAYMENTSPLIT.FINANCIALTRANSACTIONID = PAYMENT.ID
    where
      MS.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID and
      MST.REVENUESPLITID is not null and
      FTLI.DELETEDON is null and
      FTLI.TYPECODE <> 1 and
      FT.DELETEDON is null;

    declare @CONTEXTCACHE varbinary(128) = context_info();

    if @CHANGEAGENTID is not null
      set context_info @CHANGEAGENTID;

    --Delete any existing campaigns that do not align with the program/level campaigns

    delete
      RSC
    from
      dbo.REVENUESPLITCAMPAIGN RSC
    inner join
      @EXISTING_MEMBERSHIPS EM on EM.REVENUESPLITID = RSC.REVENUESPLITID
    where
      not exists
      (
        select top 1 1 from
          @PROGRAM_CAMPAIGNS PC
        where
          RSC.CAMPAIGNID = PC.CAMPAIGNID and
          isnull(RSC.CAMPAIGNSUBPRIORITYID, 0X00) = isnull(PC.CAMPAIGNSUBPRIORITYID, 0X00)
      );

    set @NUMBERDELETED  = @@ROWCOUNT;

    if @CONTEXTCACHE is not null
      set context_info @CONTEXTCACHE;

    --Insert any campaigns that fall within the membership's last activity and do not already exist

    insert into dbo.REVENUESPLITCAMPAIGN
    (
      REVENUESPLITID,
      CAMPAIGNID,
      CAMPAIGNSUBPRIORITYID,
      ADDEDBYID, CHANGEDBYID,
      DATEADDED, DATECHANGED
    )
    select
      EM.REVENUESPLITID,
      PC.CAMPAIGNID,
      PC.CAMPAIGNSUBPRIORITYID,
      @CHANGEAGENTID, @CHANGEAGENTID,
      @CURRENTDATE, @CURRENTDATE
    from
      @EXISTING_MEMBERSHIPS EM
    inner join
      @PROGRAM_CAMPAIGNS PC on PC.MEMBERSHIPLEVELID = EM.MEMBERSHIPLEVELID
    where
      (
        --(EM.MEMBERSHIPDATE is null) or

        (EM.MEMBERSHIPDATE between coalesce(DATEFROM, EM.MEMBERSHIPDATE) and coalesce(DATETO, EM.MEMBERSHIPDATE))
      ) and
      not exists
      (
        select top 1 1 from
          dbo.REVENUESPLITCAMPAIGN RSC
        where
          RSC.REVENUESPLITID = EM.REVENUESPLITID and
          RSC.CAMPAIGNID = PC.CAMPAIGNID and
          isnull(RSC.CAMPAIGNSUBPRIORITYID, 0X00) = isnull(PC.CAMPAIGNSUBPRIORITYID, 0X00)
      );

    set @NUMBERADDED = @@ROWCOUNT;

  end try

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