USP_MEMBERSHIP_ADDMEMBERSHIPTRANSACTION

CREATE procedure dbo.USP_MEMBERSHIP_ADDMEMBERSHIPTRANSACTION
(
  @MEMBERSHIPID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,
  @CONSTITUENTID uniqueidentifier,
  @MEMBERSHIPPROGRAMID uniqueidentifier = null,
  @MEMBERSHIPLEVELID uniqueidentifier = null,
  @MEMBERSHIPLEVELTERMID uniqueidentifier = null,
  @MEMBERSHIPLEVELTYPECODEID uniqueidentifier = null,
  @TRANSACTIONDATE datetime = null,
  @EXPIRATIONDATE datetime = null,
  @NUMBEROFCHILDREN smallint = 0,
  @COMMENTS nvarchar(1000) = null,
  @ISGIFT bit = 0,
  @GIVENBYID uniqueidentifier = null,
  @SENDRENEWALCODE smallint = 1,
  @MEMBERS xml = null,
  @MEMBERSHIPCARDS xml = null,
  @ADDONS xml = null,
  @REVENUESPLITID uniqueidentifier = null,
  @TRANSACTIONID uniqueidentifier = null output
)
as
begin
  set nocount on;

  if @MEMBERSHIPID is null
    set @MEMBERSHIPID = newid();

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

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

  begin try

    declare @MEMBERSTABLE table (
      ID uniqueidentifier,
      SALESORDERITEMMEMBERID uniqueidentifier,
      CONSTITUENTID uniqueidentifier,
      ISPRIMARY bit
    );

    insert into @MEMBERSTABLE
      select
        T.members.value('(MEMBERID)[1]','uniqueidentifier') as 'ID',
        T.members.value('(SALESORDERITEMMEMBERID)[1]','uniqueidentifier') as 'SALESORDERITEMMEMBERID',
        T.members.value('(CONSTITUENTID)[1]','uniqueidentifier') as 'CONSTITUENTID',
        T.members.value('(ISPRIMARY)[1]','bit') as 'ISPRIMARY'
      from @MEMBERS.nodes('/MEMBERS/ITEM') T(members);

    update @MEMBERSTABLE set
      ID = newid()
    where (ID is null)
    or (ID = '00000000-0000-0000-0000-000000000000');

    update @MEMBERSTABLE
      set ID = newid()
    from @MEMBERSTABLE MEMBERS
    inner join dbo.MEMBER ON MEMBERS.ID = MEMBER.ID
    where MEMBERS.CONSTITUENTID <> MEMBER.CONSTITUENTID;


    declare @MEMBERSHIPCARDSTABLE table (
      ID uniqueidentifier,
      SALESORDERITEMMEMBERID uniqueidentifier,
      NAMEONCARD nvarchar(700),
      EXPIRATIONDATE datetime,
      MEMBERID uniqueidentifier,
      ACTIONCODE tinyint -- 0: Insert; 1: Update; 2: Cancel.

    );

    insert into @MEMBERSHIPCARDSTABLE
      select
        T.cards.value('(MEMBERSHIPCARDID)[1]','uniqueidentifier') as 'ID',
        T.cards.value('(SALESORDERITEMMEMBERID)[1]','uniqueidentifier') as 'SALESORDERITEMMEMBERID',
        T.cards.value('(NAMEONCARD)[1]','nvarchar(700)') as 'NAMEONCARD',
        --The "GETLATESTTIME" date function has been inlined here for performance (the part with "dateadd(ms, -003...")...

        dateadd(ms, -003, dateadd(d, 1, cast(cast(T.cards.value('(EXPIRATIONDATE)[1]','datetime') as date) as datetime))) as 'EXPIRATIONDATE',
        null as 'MEMBERID',
        0
      from @MEMBERSHIPCARDS.nodes('/MEMBERSHIPCARDS/ITEM') T(cards)
      where T.cards.value('(NAMEONCARD)[1]','nvarchar(700)') <> '';

    update @MEMBERSHIPCARDSTABLE set
      ID = newid()
    where (ID is null)
    or (ID = '00000000-0000-0000-0000-000000000000');

    update @MEMBERSHIPCARDSTABLE set
      MEMBERID = M.ID
    from @MEMBERSHIPCARDSTABLE C
    inner join @MEMBERSTABLE M on C.SALESORDERITEMMEMBERID = M.SALESORDERITEMMEMBERID;

    update @MEMBERSHIPCARDSTABLE
      set ID = newid()
    from @MEMBERSHIPCARDSTABLE MEMBERSHIPCARDS
    inner join dbo.MEMBERSHIPCARD ON MEMBERSHIPCARDS.ID = MEMBERSHIPCARD.ID
    where MEMBERSHIPCARDS.MEMBERID <> MEMBERSHIPCARD.MEMBERID;

    declare @ADDONSTABLE table (
      ADDONID uniqueidentifier,
      ADDONTYPECODE tinyint,
      PRICE money,
      QUANTITY int,
      SPLITID uniqueidentifier
    );

    insert into @ADDONSTABLE
      select
        T.addons.value('(ADDONID)[1]','uniqueidentifier'),
        T.addons.value('(ADDONTYPECODE)[1]','tinyint'),
        T.addons.value('(PRICE)[1]','money'),
        T.addons.value('(QUANTITY)[1]','int'),
        T.addons.value('(SPLITID)[1]','uniqueidentifier')
      from @ADDONS.nodes('/ADDONS/ITEM') T(addons);

    set @TRANSACTIONDATE = cast(@TRANSACTIONDATE as date);

    if @EXPIRATIONDATE is not null
      set @EXPIRATIONDATE = dbo.UFN_DATE_GETLATESTTIME(@EXPIRATIONDATE);

    declare @ACTIONCODE tinyint;

    -- determine action code

    declare @PREVEXPRDATE datetime,
            @PREVLEVELID uniqueidentifier,
            @PREVACTIONCODE tinyint;

    select top 1
      @PREVEXPRDATE = EXPIRATIONDATE,
      @PREVLEVELID = MEMBERSHIPLEVELID,
      @PREVACTIONCODE = ACTIONCODE
    from dbo.MEMBERSHIPTRANSACTION
    where MEMBERSHIPID = @MEMBERSHIPID
    and TRANSACTIONDATE <= @TRANSACTIONDATE
    order by TRANSACTIONDATE desc, DATEADDED desc;

    set @ACTIONCODE = dbo.UFN_MEMBERSHIPTRANSACTION_DETERMINENEWACTIONCODE(@PREVLEVELID,
                                                                           @PREVEXPRDATE,
                                                                           @PREVACTIONCODE,
                                                                           @MEMBERSHIPLEVELID,
                                                                           @TRANSACTIONDATE);

    if @ACTIONCODE = 0 and not exists (select 1 from dbo.MEMBERSHIP where MEMBERSHIP.ID = @MEMBERSHIPID)
    begin
      -- Join a new membership


      -- Multiple memberships validation

      declare @MULTIPLEMEMBERSHIPSALLOWED int;
      select
        @MULTIPLEMEMBERSHIPSALLOWED = ALLOWMULTIPLEMEMBERSHIPS
      from dbo.MEMBERSHIPPROGRAM
      where ID = @MEMBERSHIPPROGRAMID;

      if @MULTIPLEMEMBERSHIPSALLOWED = 0
        if exists (
          select 1 
          from dbo.MEMBER 
          inner join dbo.MEMBERSHIP
          on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
          where
            MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID 
            and STATUSCODE <> 1
            and MEMBER.ISDROPPED <> 1
            and MEMBER.CONSTITUENTID in (
              select CONSTITUENTID
              from @MEMBERSTABLE
            )
        )
          raiserror('This membership program does not allow multiple memberships from the same constituent.', 13, 1);

      insert into dbo.MEMBERSHIP (
        ID,
        MEMBERSHIPPROGRAMID,
        MEMBERSHIPLEVELID,
        MEMBERSHIPLEVELTERMID,
        MEMBERSHIPLEVELTYPECODEID,
        JOINDATE,
        EXPIRATIONDATE,
        NUMBEROFCHILDREN,
        COMMENTS,
        ISGIFT,
        GIVENBYID,
        SENDRENEWALCODE,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
      ) values (
        @MEMBERSHIPID,
        @MEMBERSHIPPROGRAMID,
        @MEMBERSHIPLEVELID,
        @MEMBERSHIPLEVELTERMID,
        @MEMBERSHIPLEVELTYPECODEID,
        @TRANSACTIONDATE,
        @EXPIRATIONDATE,
        @NUMBEROFCHILDREN,
        '',
        @ISGIFT,
        @GIVENBYID,
        @SENDRENEWALCODE,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
      );

    end
    else
    begin
      -- Renew/Upgrade/Downgrade/Re-join an existing membership

      if (@EXPIRATIONDATE is not null)
        if @EXPIRATIONDATE < (select JOINDATE from dbo.MEMBERSHIP where ID = @MEMBERSHIPID)
          raiserror('The expiration date must come after the transaction date.', 13, 1);

      update dbo.MEMBERSHIP set
        MEMBERSHIPLEVELID = @MEMBERSHIPLEVELID, 
        MEMBERSHIPLEVELTERMID = @MEMBERSHIPLEVELTERMID, 
        MEMBERSHIPLEVELTYPECODEID = @MEMBERSHIPLEVELTYPECODEID,
        NUMBEROFCHILDREN = @NUMBEROFCHILDREN,
        ISGIFT = @ISGIFT,
        GIVENBYID = @GIVENBYID,
        SENDRENEWALCODE = @SENDRENEWALCODE,
        EXPIRATIONDATE = @EXPIRATIONDATE,
        LASTRENEWEDON = @TRANSACTIONDATE,
        STATUSCODE = 0,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        ID = @MEMBERSHIPID;
    end

    -- Save membership transaction

    if @TRANSACTIONID is null
      set @TRANSACTIONID = newid()

    insert into dbo.MEMBERSHIPTRANSACTION (
      ID,
      MEMBERSHIPID,
      MEMBERSHIPLEVELID,
      MEMBERSHIPLEVELTERMID,
      MEMBERSHIPLEVELTYPECODEID,
      TRANSACTIONDATE,
      EXPIRATIONDATE,
      REVENUESPLITID,
      ISGIFT,
      DONORID,
      NUMBEROFCHILDREN,
      COMMENTS,
      ACTIONCODE,
      ADDEDBYID,
      CHANGEDBYID,
      DATEADDED,
      DATECHANGED
    ) values (
      @TRANSACTIONID,
      @MEMBERSHIPID,
      @MEMBERSHIPLEVELID,
      @MEMBERSHIPLEVELTERMID,
      @MEMBERSHIPLEVELTYPECODEID,
      @TRANSACTIONDATE,
      @EXPIRATIONDATE,
      @REVENUESPLITID,
      @ISGIFT,
      @GIVENBYID,
      @NUMBEROFCHILDREN,
      @COMMENTS,
      @ACTIONCODE,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    );

    --Save Addons

    declare @TRANSACTIONCURRENCYID uniqueidentifier;
    declare @BASECURRENCYID uniqueidentifier;
    declare @BASEEXCHANGERATEID uniqueidentifier;
    declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
    declare @DATE datetime;

    select
      @TRANSACTIONCURRENCYID = FT.TRANSACTIONCURRENCYID,
      @BASECURRENCYID = isnull(EXT.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID),
      @BASEEXCHANGERATEID = FT.BASEEXCHANGERATEID,
      @ORGANIZATIONEXCHANGERATEID = FT.ORGEXCHANGERATEID,
      @DATE = FT.DATE
    from
        dbo.FINANCIALTRANSACTION as FT
    inner join
        dbo.REVENUE_EXT as EXT on EXT.ID = FT.ID
    inner join
        dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
    left outer join
        dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I as V with (noexpand) on V.FINANCIALTRANSACTIONID = FT.ID
    where
        LI.ID = @REVENUESPLITID
        and FT.DELETEDON is null;

    insert into dbo.MEMBERSHIPADDON(ID, MEMBERSHIPID, ADDONID, QUANTITY, EXPIRATIONDATE, MEMBERSHIPTRANSACTIONID, REVENUESPLITID, PURCHASEPRICE,
                TRANSACTIONPURCHASEPRICE, ORGANIZATIONPURCHASEPRICE, TRANSACTIONCURRENCYID, BASECURRENCYID, BASEEXCHANGERATEID,
                ORGANIZATIONEXCHANGERATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    select 
      newid(),
      @MEMBERSHIPID,
      ADDONID,
      QUANTITY,
      @EXPIRATIONDATE,
      @TRANSACTIONID,
      SPLITID,
      case when @BASEEXCHANGERATEID is null then PRICE
        else dbo.UFN_CURRENCY_CONVERT(PRICE, @BASEEXCHANGERATEID)
      end,
      PRICE,
      case when @ORGANIZATIONEXCHANGERATEID is null then PRICE
        else dbo.UFN_CURRENCY_CONVERT(PRICE, @ORGANIZATIONEXCHANGERATEID) 
      end,
      @TRANSACTIONCURRENCYID,
      @BASECURRENCYID,
      @BASEEXCHANGERATEID,
      @ORGANIZATIONEXCHANGERATEID,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    from @ADDONSTABLE

    -- Save members

    if @ACTIONCODE = 0 and not exists (select 1 from dbo.MEMBERSHIP where MEMBERSHIP.ID = @MEMBERSHIPID)
    begin
      insert into dbo.MEMBER (
        ID,
        MEMBERSHIPID,
        CONSTITUENTID,
        ISPRIMARY,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
      ) select ID,
          @MEMBERSHIPID,
          CONSTITUENTID,
          ISPRIMARY,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
      from @MEMBERSTABLE
    end
    else
    begin
      --Remove dropped members

      update dbo.MEMBER set
        ISDROPPED = 1,
        ISPRIMARY = 0,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where [MEMBER].ID in (
        select ID from dbo.UFN_MEMBERSHIP_GETMEMBERS(@MEMBERSHIPID)
        EXCEPT
        select MEMBERS.ID from @MEMBERSTABLE MEMBERS
      );

      --check to see which members are going to not be dropped anymore... sync their memberids via SALESORDERITEMMEMBER

      update @MEMBERSHIPCARDSTABLE set
        CARDS.MEMBERID = MEMBER.ID
      from @MEMBERSHIPCARDSTABLE CARDS
      inner join dbo.SALESORDERITEMMEMBER on SALESORDERITEMMEMBER.ID = CARDS.SALESORDERITEMMEMBERID
      inner join dbo.MEMBER on MEMBER.CONSTITUENTID = SALESORDERITEMMEMBER.CONSTITUENTID
      where MEMBER.MEMBERSHIPID = @MEMBERSHIPID
      and MEMBER.ISDROPPED = 1
      and CARDS.MEMBERID <> MEMBER.ID;

      --Update existing members

      update dbo.MEMBER set
        MEMBER.ISPRIMARY = MEMBERS.ISPRIMARY,
        MEMBER.CHANGEDBYID = @CHANGEAGENTID,
        MEMBER.DATECHANGED = @CURRENTDATE
      from dbo.MEMBER
      inner join @MEMBERSTABLE MEMBERS on MEMBER.CONSTITUENTID = MEMBERS.CONSTITUENTID
      where MEMBER.MEMBERSHIPID = @MEMBERSHIPID;


      --Add new members

      insert into dbo.MEMBER(ID, CONSTITUENTID, MEMBERSHIPID, ISPRIMARY, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        select ID, MEMBERS.CONSTITUENTID, @MEMBERSHIPID, ISPRIMARY, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from @MEMBERSTABLE MEMBERS
        where MEMBERS.CONSTITUENTID not in (
          select CONSTITUENTID
          from dbo.MEMBER
          where MEMBER.MEMBERSHIPID = @MEMBERSHIPID
          and MEMBER.ISDROPPED = 0
        );
    end

    -- Save membership cards

    -- Get existing membership cards

    declare @EXISTINGCARDS table (
      ID uniqueidentifier,
      MEMBERID  uniqueidentifier,
      EXPIRATIONDATE datetime,
      NAMEONCARD nvarchar(700),
      STATUSCODE tinyint
    );

    insert into @EXISTINGCARDS 
      select
        MEMBERSHIPCARD.ID, 
        MEMBERSHIPCARD.MEMBERID, 
        MEMBERSHIPCARD.EXPIRATIONDATE, 
        MEMBERSHIPCARD.NAMEONCARD, 
        MEMBERSHIPCARD.STATUSCODE
      from dbo.MEMBERSHIPCARD 
      inner join dbo.MEMBER on MEMBERSHIPCARD.MEMBERID = MEMBER.ID 
      where MEMBER.MEMBERSHIPID = @MEMBERSHIPID
      and MEMBERSHIPCARD.STATUSCODE <> 2;

    if @@rowcount = 0
    begin
      -- No existing membership cards

      insert into dbo.[MEMBERSHIPCARD] (
        [ID],
        [MEMBERID],
        [NAMEONCARD],
        [EXPIRATIONDATE],
        [ADDEDBYID],
        [CHANGEDBYID],
        [DATEADDED],
        [DATECHANGED]
      )
      select
        [ID],
        [MEMBERID],
        [NAMEONCARD],
        [EXPIRATIONDATE],
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
      from @MEMBERSHIPCARDSTABLE;
    end
    else
    begin
      -- Mark the cards need to be updated, do not update expiration date if the expiration date of existing care is null

      update @MEMBERSHIPCARDSTABLE set
        ACTIONCODE = 1,
        EXPIRATIONDATE = case when EXISTINGCARDS.EXPIRATIONDATE is null then
                           null
                         when (EXISTINGCARDS.EXPIRATIONDATE > NEWCARDS.EXPIRATIONDATE and EXISTINGCARDS.STATUSCODE = 1) then
                           EXISTINGCARDS.EXPIRATIONDATE
                         else
                           NEWCARDS.EXPIRATIONDATE
                         end
      from @MEMBERSHIPCARDSTABLE NEWCARDS
      inner join @EXISTINGCARDS EXISTINGCARDS on NEWCARDS.ID = EXISTINGCARDS.ID;

      -- If existing membership card is printed

      update @MEMBERSHIPCARDSTABLE set
        ACTIONCODE = 2
      from @MEMBERSHIPCARDSTABLE NEWCARDS 
      inner join @EXISTINGCARDS EXISTINGCARDS on NEWCARDS.ID = EXISTINGCARDS.ID 
      where EXISTINGCARDS.STATUSCODE = 1 and 
        (((EXISTINGCARDS.EXPIRATIONDATE is not null) and EXISTINGCARDS.EXPIRATIONDATE < NEWCARDS.EXPIRATIONDATE) or
        EXISTINGCARDS.NAMEONCARD <> NEWCARDS.NAMEONCARD);

      insert @MEMBERSHIPCARDSTABLE 
        select
      newid(),
          SALESORDERITEMMEMBERID,
          NAMEONCARD,
          EXPIRATIONDATE,
          MEMBERID,
          0
        from @MEMBERSHIPCARDSTABLE 
        where ACTIONCODE = 2;

      -- if existing membership card no longer exists in the XML table

      insert @MEMBERSHIPCARDSTABLE 
        select
          ID,
          null,
          NAMEONCARD,
          EXPIRATIONDATE,
          MEMBERID,
          2
        from @EXISTINGCARDS 
        where ID not in (select ID from @MEMBERSHIPCARDSTABLE);

      -- Make sure cards that should be cancelled are cancelled first.

      update dbo.MEMBERSHIPCARD set
        MEMBERSHIPCARD.STATUSCODE = 2,
        MEMBERSHIPCARD.CHANGEDBYID = @CHANGEAGENTID,
        MEMBERSHIPCARD.DATECHANGED = @CURRENTDATE
      from dbo.MEMBERSHIPCARD
      inner join @MEMBERSHIPCARDSTABLE on MEMBERSHIPCARD.ID = [@MEMBERSHIPCARDSTABLE].ID
      where [@MEMBERSHIPCARDSTABLE].ACTIONCODE = 2;

      update dbo.[MEMBERSHIPCARD] set
        [MEMBERSHIPCARD].[NAMEONCARD] = NEWCARDS.[NAMEONCARD],
        [MEMBERSHIPCARD].[EXPIRATIONDATE] = NEWCARDS.[EXPIRATIONDATE],
        [MEMBERSHIPCARD].[MEMBERID] = NEWCARDS.[MEMBERID],
        [MEMBERSHIPCARD].[STATUSCODE] = [MEMBERSHIPCARD].[STATUSCODE],
        [MEMBERSHIPCARD].[CHANGEDBYID] = @CHANGEAGENTID,
        [MEMBERSHIPCARD].[DATECHANGED] = @CURRENTDATE
      from dbo.[MEMBERSHIPCARD]
      inner join @MEMBERSHIPCARDSTABLE as NEWCARDS on [MEMBERSHIPCARD].ID = NEWCARDS.ID
      where NEWCARDS.ACTIONCODE <> 2;

      insert into dbo.[MEMBERSHIPCARD] (
        [ID],
        [MEMBERID],
        [NAMEONCARD],
        [EXPIRATIONDATE],
        [ADDEDBYID],
        [CHANGEDBYID],
        [DATEADDED],
        [DATECHANGED]
      )
      select
        [ID],
        [MEMBERID],
        [NAMEONCARD],
        [EXPIRATIONDATE],
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
      from @MEMBERSHIPCARDSTABLE as NEWCARDS
      where NEWCARDS.ACTIONCODE = 0;
    end
  end try

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

  return 0;
end