USP_DATAFORMTEMPLATE_EDIT_MEMBERSHIPPROGRAMRENEWAL_2

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_MEMBERSHIPPROGRAMRENEWAL_2 (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @MEMBERSHIPTERMTYPE tinyint,
  @DUESBASEDEXPIRATIONDATETYPE tinyint,
  @EXPIRATIONENDOFPRIORMONTH bit,
  @EXPIRATIONENDOFPRIORMONTHCUTOFF tinyint,
  @RENEWALWINDOWSTART tinyint,
  @RENEWALWINDOWEND tinyint,
  @AUTOMATICRENEWALRADIO tinyint,
  @AUTOMATICRENEWALTIMETYPE tinyint,
  @RENEWALWINDOWSTARTCUTOFFDAY tinyint,
  @AFTEREXPIRATIONTIMEPERIOD1 tinyint,
  @AFTEREXPIRATIONCLASSIFICATION1 tinyint,
  @AFTEREXPIRATIONTIMEPERIOD2 tinyint,
  @AFTEREXPIRATIONCLASSIFICATION2 tinyint,
  @AFTEREXPIRATIONTIMEPERIOD3 tinyint,
  @AFTEREXPIRATIONCLASSIFICATION3 tinyint,
  @CLASSIFICATIONAFTERRENEWALWINDOWTYPE tinyint,
  @REVENUEAFTERRENEWALWINDOWTYPE tinyint,
  @DUESBASEDEXPIRATIONDATE dbo.UDT_MONTHDAY,
  @AUTOMATICRENEWALNUMBERDAY tinyint,
  @AUTOMATICRENEWALNUMBERMONTH tinyint,
  @AUTOMATICRENEWALDAYOFMONTH int,
  @PROGRAMBASEDONCODE tinyint,
  @EXPIRATIONDATES xml
)
as

    set nocount on;

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

    declare @MEMBERSHIPTERMTYPE_DATEVARIESWITHSTARTDATE tinyint = 0
    declare @MEMBERSHIPTERMTYPE_DATEISFIXED tinyint = 1
    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

  declare @RENEWALINFOID uniqueidentifier

  begin try

  update dbo.MEMBERSHIPPROGRAM set
      EXPIRESONCODE = @DUESBASEDEXPIRATIONDATETYPE,
      BACKDATEMEMBERSHIPS = @EXPIRATIONENDOFPRIORMONTH,
      CUTOFFDAY = case when @EXPIRATIONENDOFPRIORMONTH = 1 then @EXPIRATIONENDOFPRIORMONTHCUTOFF else 0 end,
    RENEWALWINDOWSTARTTYPECODE = @AUTOMATICRENEWALRADIO,
    RENEWALWINDOWSTARTTIMECODE = case when @AUTOMATICRENEWALTIMETYPE = 0 then @AUTOMATICRENEWALNUMBERDAY else @AUTOMATICRENEWALNUMBERMONTH end,
    RENEWALWINDOWSTARTINTERVALCODE = @AUTOMATICRENEWALTIMETYPE,
    RENEWALWINDOWSTARTCUTOFFDAY = case when @AUTOMATICRENEWALRADIO = 1 then @AUTOMATICRENEWALDAYOFMONTH else 1 end,
    RENEWALWINDOWREVENUETYPECODE = @REVENUEAFTERRENEWALWINDOWTYPE,
    NONRENEWALACTIONTYPECODE = @CLASSIFICATIONAFTERRENEWALWINDOWTYPE,
    CHANGEDBYID = @CHANGEAGENTID,
    DATECHANGED = @CURRENTDATE
  where
      ID = @ID

    if @MEMBERSHIPTERMTYPE = @MEMBERSHIPTERMTYPE_DATEISFIXED
    begin
        declare @newexpirationdates table (
            ID uniqueidentifier,
            EXPIRATIONDATE char(4),
            PUSHNEXTDATE char(4),
            SEQUENCE int
        )
        insert into @newexpirationdates
            select  ID = T.c.value('(ID)[1]', 'uniqueidentifier'),
                    EXPIRATIONDATE = T.c.value('(EXPIRATIONDATE)[1]', 'char(4)'),
                    PUSHNEXTDATE = T.c.value('(PUSHNEXTDATE)[1]', 'char(4)'),
                    SEQUENCE = row_number() over (order by T.c.value('(EXPIRATIONDATE)[1]', 'char(4)'), T.c.value('(PUSHNEXTDATE)[1]', 'char(4)'))
            from @EXPIRATIONDATES.nodes('/EXPIRATIONDATES/ITEM') T(c)
        ;with
        target_tbl as (select MEMBERSHIPPROGRAMENDDATE.* from dbo.MEMBERSHIPPROGRAMENDDATE where MEMBERSHIPPROGRAMID = @ID)
        merge target_tbl as MPE
        using (
            select ID, EXPIRATIONDATE, PUSHNEXTDATE, SEQUENCE
            from @newexpirationdates
        ) as src on MPE.ID = src.ID
        when matched then
            update set
                EXPIRATIONDATE = src.EXPIRATIONDATE,
                PUSHNEXTDATE = src.PUSHNEXTDATE,
                SEQUENCE = src.SEQUENCE

        when not matched by target then
            insert 
            (
                ID,
                MEMBERSHIPPROGRAMID,
                EXPIRATIONDATE,
                PUSHNEXTDATE,
                SEQUENCE,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            values (
                newid(),
                @ID,
                src.EXPIRATIONDATE,
                src.PUSHNEXTDATE,
                src.SEQUENCE,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            )

        when not matched by source then
            DELETE;
    end
    else
    begin
        -- Remove any outdated date information
        delete from dbo.MEMBERSHIPPROGRAMENDDATE
        where MEMBERSHIPPROGRAMID = @ID
    end

  update dbo.MEMBERSHIPLEVEL set
      MEMBERSHIPTERMTYPECODE = @MEMBERSHIPTERMTYPE,
      BEFOREEXPIRATION = @RENEWALWINDOWSTART,
      AFTEREXPIRATION = @RENEWALWINDOWEND,
      RENEWALWINDOWSTARTTYPECODE = @AUTOMATICRENEWALRADIO,
      RENEWALWINDOWSTARTINTERVALCODE = @AUTOMATICRENEWALTIMETYPE,
      RENEWALWINDOWSTARTCUTOFFDAY = case when @AUTOMATICRENEWALRADIO = 1 then @AUTOMATICRENEWALDAYOFMONTH else 1 end,
      NONRENEWALACTIONTYPECODE = @CLASSIFICATIONAFTERRENEWALWINDOWTYPE,
      RENEWALWINDOWREVENUETYPECODE = @REVENUEAFTERRENEWALWINDOWTYPE,
      RENEWALWINDOWSTARTTIMECODE = case when @AUTOMATICRENEWALTIMETYPE = 0 then @AUTOMATICRENEWALNUMBERDAY else @AUTOMATICRENEWALNUMBERMONTH end,
    MEMBERSHIPLEVELRENEWAL1ID = null,
    MEMBERSHIPLEVELRENEWAL2ID = null,
    MEMBERSHIPLEVELRENEWAL3ID = null
  where
      MEMBERSHIPPROGRAMID = @ID


  delete from 
      dbo.MEMBERSHIPLEVELRENEWALRULES
  where
      MEMBERSHIPLEVELID in (select ID from MEMBERSHIPLEVEL where MEMBERSHIPPROGRAMID = @ID)


  delete from 
    dbo.MEMBERSHIPLEVELRENEWAL
  where
    MEMBERSHIPLEVELID in (select ID from MEMBERSHIPLEVEL where MEMBERSHIPPROGRAMID = @ID)


    if @AFTEREXPIRATIONCLASSIFICATION1 is not null
                    begin
                        set @RENEWALINFOID = newid()

                        -- create new default record
                        insert into dbo.MEMBERSHIPPROGRAMRENEWAL
                        (
                            ID, MEMBERSHIPPROGRAMID, STATUSCODE,
                            INTERVALCODE, ADDEDBYID, CHANGEDBYID,
                            DATEADDED, DATECHANGED
                        )
                        values
                        (
                            @RENEWALINFOID, @ID, @AFTEREXPIRATIONCLASSIFICATION1,
                            @AFTEREXPIRATIONTIMEPERIOD1, @CHANGEAGENTID, @CHANGEAGENTID,
                            @CURRENTDATE, @CURRENTDATE
                        )

                        -- link default record to this one
                        update
                            dbo.MEMBERSHIPPROGRAM
                        set
                            MEMBERSHIPPROGRAMRENEWAL1ID = @RENEWALINFOID
                        where
                            ID = @ID


                        declare @RENEWALIDTABLE1 table (RENEWALID uniqueidentifier, MEMBERSHIPLEVELID uniqueidentifier)
                        insert into @RENEWALIDTABLE1 (RENEWALID,MEMBERSHIPLEVELID) select newid(),ID from dbo.MEMBERSHIPLEVEL where MEMBERSHIPPROGRAMID = @ID
                        -- create new level-specific records
                        insert into dbo.MEMBERSHIPLEVELRENEWAL
                        (
                            ID, MEMBERSHIPLEVELID, STATUSCODE,
                            INTERVALCODE, ADDEDBYID, CHANGEDBYID,
                            DATEADDED, DATECHANGED
                        )
                        select
                            RENEWALID, MEMBERSHIPLEVELID, @AFTEREXPIRATIONCLASSIFICATION1,
                            @AFTEREXPIRATIONTIMEPERIOD1, @CHANGEAGENTID, @CHANGEAGENTID,
                            @CURRENTDATE, @CURRENTDATE
                        from @RENEWALIDTABLE1

                        -- initialize all levels to default
                        update
                            dbo.MEMBERSHIPLEVEL
                        set
                                        MEMBERSHIPLEVELRENEWAL1ID = RIT.RENEWALID
                          from dbo.MEMBERSHIPLEVEL
                          inner join @RENEWALIDTABLE1 RIT on RIT.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
                    end

                    if @AFTEREXPIRATIONCLASSIFICATION2 is not null
                    begin
                        set @RENEWALINFOID = newid()

                        -- create new default record
                        insert into dbo.MEMBERSHIPPROGRAMRENEWAL
                        (
                            ID, MEMBERSHIPPROGRAMID, STATUSCODE,
                            INTERVALCODE, ADDEDBYID, CHANGEDBYID,
                            DATEADDED, DATECHANGED
                        )
                        values
                        (
                            @RENEWALINFOID, @ID, @AFTEREXPIRATIONCLASSIFICATION2,
                            @AFTEREXPIRATIONTIMEPERIOD2, @CHANGEAGENTID, @CHANGEAGENTID,
                            @CURRENTDATE, @CURRENTDATE
                        )

                        -- link default record to this one
                        update
                            dbo.MEMBERSHIPPROGRAM
                        set
                            MEMBERSHIPPROGRAMRENEWAL2ID = @RENEWALINFOID
                        where
                            ID = @ID

                        declare @RENEWALIDTABLE2 table (RENEWALID uniqueidentifier, MEMBERSHIPLEVELID uniqueidentifier)
                        insert into @RENEWALIDTABLE2 (RENEWALID,MEMBERSHIPLEVELID) select newid(),ID from dbo.MEMBERSHIPLEVEL where MEMBERSHIPPROGRAMID = @ID
                        -- create new level-specific records
                        insert into dbo.MEMBERSHIPLEVELRENEWAL
                        (
                            ID, MEMBERSHIPLEVELID, STATUSCODE,
                            INTERVALCODE, ADDEDBYID, CHANGEDBYID,
                            DATEADDED, DATECHANGED
                        )
                        select
                            RENEWALID, MEMBERSHIPLEVELID, @AFTEREXPIRATIONCLASSIFICATION2,
                            @AFTEREXPIRATIONTIMEPERIOD2, @CHANGEAGENTID, @CHANGEAGENTID,
                            @CURRENTDATE, @CURRENTDATE
                        from
                            @RENEWALIDTABLE2


                        -- initialize all levels to default
                        update
                            dbo.MEMBERSHIPLEVEL
                        set
                                        MEMBERSHIPLEVELRENEWAL2ID =  RIT.RENEWALID
                          from dbo.MEMBERSHIPLEVEL
                          inner join @RENEWALIDTABLE2 RIT on RIT.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID

                    end

                    if @AFTEREXPIRATIONCLASSIFICATION3 is not null
                    begin
                        set @RENEWALINFOID = newid()

                        -- create new default record
                        insert into dbo.MEMBERSHIPPROGRAMRENEWAL
                        (
                            ID, MEMBERSHIPPROGRAMID, STATUSCODE,
                            INTERVALCODE, ADDEDBYID, CHANGEDBYID,
                            DATEADDED, DATECHANGED
                        )
                        values
                        (
                            @RENEWALINFOID, @ID, @AFTEREXPIRATIONCLASSIFICATION3,
                            @AFTEREXPIRATIONTIMEPERIOD3, @CHANGEAGENTID, @CHANGEAGENTID,
                            @CURRENTDATE, @CURRENTDATE
                        )

                        -- link default record to this one
                        update
                            dbo.MEMBERSHIPPROGRAM
                        set
                            MEMBERSHIPPROGRAMRENEWAL3ID = @RENEWALINFOID
                        where
                            ID = @ID

                        -- create new level-specific records
                        declare @RENEWALIDTABLE3 table (RENEWALID uniqueidentifier, MEMBERSHIPLEVELID uniqueidentifier)
                        insert into @RENEWALIDTABLE3 (RENEWALID,MEMBERSHIPLEVELID) select newid(),ID from dbo.MEMBERSHIPLEVEL where MEMBERSHIPPROGRAMID = @ID

                        insert into dbo.MEMBERSHIPLEVELRENEWAL
                        (
                            ID, MEMBERSHIPLEVELID, STATUSCODE,
                            INTERVALCODE, ADDEDBYID, CHANGEDBYID,
                            DATEADDED, DATECHANGED
                        )
                        select
                            RENEWALID, MEMBERSHIPLEVELID, @AFTEREXPIRATIONCLASSIFICATION3,
                            @AFTEREXPIRATIONTIMEPERIOD3, @CHANGEAGENTID, @CHANGEAGENTID,
                            @CURRENTDATE, @CURRENTDATE
                        from
                            @RENEWALIDTABLE3

                        -- initialize all levels to default
                        update
                            dbo.MEMBERSHIPLEVEL
                        set
                                        MEMBERSHIPLEVELRENEWAL3ID =   RIT.RENEWALID
                          from dbo.MEMBERSHIPLEVEL
                          inner join @RENEWALIDTABLE3 RIT on RIT.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
                    end


      insert into dbo.MEMBERSHIPLEVELRENEWALRULES(
            MEMBERSHIPLEVELID,
            BEFOREEXPIRATION,
            AFTEREXPIRATION,
            STARTDATE,
            MEMBERSHIPLEVELRENEWAL1ID,
            MEMBERSHIPLEVELRENEWAL2ID,
            MEMBERSHIPLEVELRENEWAL3ID,
            NONRENEWALACTIONTYPECODE,
            ADDEDBYID, 
              CHANGEDBYID, 
              DATEADDED, 
              DATECHANGED
          )
          select
          MEMBERSHIPLEVEL.ID,
          MEMBERSHIPLEVEL.BEFOREEXPIRATION,
          MEMBERSHIPLEVEL.AFTEREXPIRATION,
          @CURRENTDATE,
          MEMBERSHIPLEVEL.MEMBERSHIPLEVELRENEWAL1ID,
          MEMBERSHIPLEVEL.MEMBERSHIPLEVELRENEWAL2ID,
          MEMBERSHIPLEVEL.MEMBERSHIPLEVELRENEWAL3ID,
          MEMBERSHIPLEVEL.NONRENEWALACTIONTYPECODE,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
          from dbo.MEMBERSHIPLEVEL
          where MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @ID

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

return 0;