USP_DATAFORMTEMPLATE_EDIT_MEMBERSHIPPROGRAMDUESBASEDRULES_2

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_MEMBERSHIPPROGRAMDUESBASEDRULES_2
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @DUESTREATEDASCONTRIBUTION bit,
  @DUESONEPAYMENTEACHTERM bit,
  @DUESMULTIPLEPAYMENTSEACHTERM bit,
  @DESIGNATIONSFORCONTRIBUTEDPORTION xml,
  @MEMBERSHIPLEVELDESIGNATIONS xml,
  @PROGRAMTYPECODE tinyint,
  @DISCOUNTSFORPROGRAM xml,
  @BASECURRENCYID uniqueidentifier,
  @DEDUCTIBILITYCODE tinyint,
  @DUESINSTALLMENTPOSTSTATUSCODE tinyint
)
as

  set nocount on;

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

  declare @CURRENTDATE datetime = getdate();

  begin try
    update dbo.MEMBERSHIPPROGRAM 
    set
      DUESTREATEDASCONTRIBUTION = @DUESTREATEDASCONTRIBUTION,
      ONEPAYMENTEACHTERM = @DUESONEPAYMENTEACHTERM,
      MULTIPLEPAYMENTSEACHTERM = @DUESMULTIPLEPAYMENTSEACHTERM,
      INSTALLMENTPOSTSTATUSCODE =
      case
        --Only change dues installment post code for annual memberships

        when MEMBERSHIPPROGRAM.PROGRAMTYPECODE = 0 and isnull(@DUESINSTALLMENTPOSTSTATUSCODE,0) <> 0 then @DUESINSTALLMENTPOSTSTATUSCODE
        else INSTALLMENTPOSTSTATUSCODE
      end
    from dbo.MEMBERSHIPPROGRAM
    where ID = @ID;

    declare @contextCache varbinary(128);
    set @contextCache = CONTEXT_INFO();

    if @CHANGEAGENTID is not null
      set CONTEXT_INFO @CHANGEAGENTID;

    --delete designations

    delete from dbo.MEMBERSHIPPROGRAMDESIGNATION
    where ID in
    (
      select ID
      from dbo.MEMBERSHIPPROGRAMDESIGNATION
      where
        MEMBERSHIPPROGRAMDESIGNATION.MEMBERSHIPPROGRAMID = @ID and
        MEMBERSHIPPROGRAMDESIGNATION.ID not in
        (
          select T.c.value('(ID)[1]','uniqueidentifier')
          from @DESIGNATIONSFORCONTRIBUTEDPORTION.nodes('/DESIGNATIONSFORCONTRIBUTEDPORTION/ITEM') T(c)
          where T.c.value('(ID)[1]','uniqueidentifier') is not null
        )
    );

    if @contextCache is not null
      set CONTEXT_INFO @contextCache;

    -- If NOT tax deductible, don't resave designations

    if @DEDUCTIBILITYCODE <> 2
    begin
      --Save designations

      insert into dbo.MEMBERSHIPPROGRAMDESIGNATION
      (
        ID,
        DESIGNATIONID,
        MEMBERSHIPPROGRAMID,
        [PERCENT],
        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
      )
      select
        T.c.value('(ID)[1]','uniqueidentifier'),
        T.c.value('(DESIGNATIONID)[1]','uniqueidentifier'),
        @ID,
        T.c.value('(PERCENT)[1]','decimal(20,4)'),
        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
      from @DESIGNATIONSFORCONTRIBUTEDPORTION.nodes('/DESIGNATIONSFORCONTRIBUTEDPORTION/ITEM') T(c)
      where T.c.value('(ID)[1]','uniqueidentifier') not in (select ID from MEMBERSHIPPROGRAMDESIGNATION where MEMBERSHIPPROGRAMID = @ID);

      update dbo.MEMBERSHIPPROGRAMDESIGNATION
      set
        DESIGNATIONID = T.c.value('(DESIGNATIONID)[1]','uniqueidentifier'),
        DATECHANGED = @CURRENTDATE,
        CHANGEDBYID =  @CHANGEAGENTID,
        [PERCENT] = T.c.value('(PERCENT)[1]','decimal(20,4)')
      from @DESIGNATIONSFORCONTRIBUTEDPORTION.nodes('/DESIGNATIONSFORCONTRIBUTEDPORTION/ITEM') T(c)
      where MEMBERSHIPPROGRAMDESIGNATION.ID = T.c.value('(ID)[1]','uniqueidentifier');
    end

    if @CHANGEAGENTID is not null
      set CONTEXT_INFO @CHANGEAGENTID;

    delete from dbo.MEMBERSHIPLEVELDESIGNATION
    where ID in
    (
      select MEMBERSHIPLEVELDESIGNATION.ID
      from dbo.MEMBERSHIPLEVELDESIGNATION
        inner join MEMBERSHIPLEVEL on MEMBERSHIPLEVELDESIGNATION.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
      where
        MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @ID and
        MEMBERSHIPLEVELDESIGNATION.ID not in
        (
          select T.c.value('(ID)[1]','uniqueidentifier')
          from @MEMBERSHIPLEVELDESIGNATIONS.nodes('/MEMBERSHIPLEVELDESIGNATIONS/ITEM') T(c)
          where T.c.value('(ID)[1]','uniqueidentifier') is not null
        )
    );

    if @contextCache is not null
      set CONTEXT_INFO @contextCache;

    -- If NOT tax deductible, so don't resave designations

    if @DEDUCTIBILITYCODE <> 2
    begin
      insert into dbo.MEMBERSHIPLEVELDESIGNATION
      (
        ID,
        DESIGNATIONID,
        MEMBERSHIPLEVELID,
        [PERCENT],
        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
      )
      select
        newid(),
        T.c.value('(DESIGNATIONID)[1]','uniqueidentifier'),
        T.c.value('(LEVELID)[1]','uniqueidentifier'),
        T.c.value('(PERCENT)[1]','decimal(20,4)'),
        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
      from @MEMBERSHIPLEVELDESIGNATIONS.nodes('/MEMBERSHIPLEVELDESIGNATIONS/ITEM') T(c)
      where
        T.c.value('(ID)[1]','uniqueidentifier') not in
        (
          select MEMBERSHIPLEVELDESIGNATION.ID
          from MEMBERSHIPLEVELDESIGNATION
            inner join MEMBERSHIPLEVEL on MEMBERSHIPLEVELDESIGNATION.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
          where MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @ID
        );

      update dbo.MEMBERSHIPLEVELDESIGNATION set
        DESIGNATIONID = T.c.value('(DESIGNATIONID)[1]','uniqueidentifier'),
        DATECHANGED = @CURRENTDATE, 
        CHANGEDBYID =  @CHANGEAGENTID,
        [PERCENT] = T.c.value('(PERCENT)[1]','decimal(20,4)')
      from @MEMBERSHIPLEVELDESIGNATIONS.nodes('/MEMBERSHIPLEVELDESIGNATIONS/ITEM') T(c)
      where MEMBERSHIPLEVELDESIGNATION.ID = T.c.value('(ID)[1]','uniqueidentifier')
    end

    if @CHANGEAGENTID is not null
      set CONTEXT_INFO @CHANGEAGENTID;

    delete from dbo.MEMBERSHIPPROMOAVAILABILITY 
    where ID in
    (
      select ID
      from dbo.MEMBERSHIPPROMOAVAILABILITY
      where
        MEMBERSHIPPROMOAVAILABILITY.MEMBERSHIPPROGRAMID = @ID and
        MEMBERSHIPPROMOAVAILABILITY.ID not in
        (
          select T.c.value('(MEMBERSHIPPROMOAVAILABILITYID)[1]','uniqueidentifier')
          from @DISCOUNTSFORPROGRAM.nodes('/DISCOUNTSFORPROGRAM/ITEM') T(c)
          where
            T.c.value('(APPLY)[1]','bit') = 1 and
            T.c.value('(MEMBERSHIPPROMOAVAILABILITYID)[1]','uniqueidentifier') is not null
        )
    );

    if @contextCache is not null
      set CONTEXT_INFO @contextCache;

    insert into dbo.MEMBERSHIPPROMOAVAILABILITY
    (
      ID,
      MEMBERSHIPPROMOID,
      MEMBERSHIPPROGRAMID,
      ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
    )
    select
      T.c.value('(MEMBERSHIPPROMOAVAILABILITYID)[1]','uniqueidentifier'),
      T.c.value('(ID)[1]','uniqueidentifier'),
      @ID,
      @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    from @DISCOUNTSFORPROGRAM.nodes('/DISCOUNTSFORPROGRAM/ITEM') T(c)
    where
      T.c.value('(APPLY)[1]','bit') = 1 and
      T.c.value('(MEMBERSHIPPROMOAVAILABILITYID)[1]','uniqueidentifier') not in (select ID from MEMBERSHIPPROMOAVAILABILITY where MEMBERSHIPPROGRAMID = @ID);

    update dbo.MEMBERSHIPPROMOAVAILABILITY
    set
      MEMBERSHIPPROMOID = T.c.value('(ID)[1]','uniqueidentifier'),
      DATECHANGED = @CURRENTDATE, 
      CHANGEDBYID =  @CHANGEAGENTID
    from @DISCOUNTSFORPROGRAM.nodes('/DISCOUNTSFORPROGRAM/ITEM') T(c)
    where
      T.c.value('(APPLY)[1]','bit') = 1 and
      MEMBERSHIPPROMOAVAILABILITY.ID = T.c.value('(MEMBERSHIPPROMOAVAILABILITYID)[1]','uniqueidentifier') and
      MEMBERSHIPPROMOAVAILABILITY.MEMBERSHIPPROGRAMID = @ID;

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

return 0;