USP_DATAFORMTEMPLATE_EDITLOAD_MEMBERSHIPLEVEL2

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_MEMBERSHIPLEVEL2 (
    @ID nvarchar(50),
  @TABID tinyint = null output,
    @DATALOADED bit = 0 output,
    @TSLONG bigint = 0 output,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @BASECURRENCYID uniqueidentifier = null output,
    @MEMBERSHIPLEVELS xml = null output,
    @RECURRINGPRICES xml = null output,
    @PROGRAMTYPECODE tinyint = null output,
    @PROGRAMBASEDONCODE tinyint = null output,
    @ANNUALLYPAYMENTOPTION bit = null output,
    @SEMIANNUALLYPAYMENTOPTION bit = null output,
    @QUARTERLYPAYMENTOPTION bit = null output,
    @MONTHLYPAYMENTOPTION bit = null output,
    @MULTITERMPRICES xml = null output,
    @LIFETIMEPAYMENTOPTIONS xml = null output,
    @HASMULTIPLETERMS bit = null output,
    @MULTIPLETERMSUNITS xml = null output,
    @ANNUALLYOPTIONACTIVE bit = null output,
    @SEMIANNUALLYOPTIONACTIVE bit = null output,
    @QUARTERLYOPTIONACTIVE bit = null output,
    @MONTHLYOPTIONACTIVE bit = null output,
    @ISTYPEPROGRAM bit = null output,
    @LIFETIMEINSTALLMENTPOSTSTATUSCODE tinyint = null output
)
as

    set nocount on;
  declare @MEMBERSHIPPROGRAMID uniqueidentifier
  declare @PARSEINDEX tinyint
  set @PARSEINDEX = charindex('@',@ID)

    set @DATALOADED = 0
    set @TSLONG = 0
    set @TABID = substring(@ID,0,@PARSEINDEX)
  set @MEMBERSHIPPROGRAMID = substring(@ID, @PARSEINDEX + 1, LEN(@ID) - @PARSEINDEX)
    -----

    -- Fetch program data

    -----

    select top 1
        @PROGRAMTYPECODE = PROGRAMTYPECODE,
        @PROGRAMBASEDONCODE = PROGRAMBASEDONCODE,
        @HASMULTIPLETERMS = 
            case 
                when --annual program with a single term

                    PROGRAMTYPECODE = 0 AND (MP.MULTIPLETERMS = 0) then 0
                when --recurring program (has special terms and prices)

                    PROGRAMTYPECODE = 1 then 0
                else 1 --All programs use multiple terms unless specified in above in the when clauses.

            end,
        @BASECURRENCYID = MP.BASECURRENCYID,
        @ISTYPEPROGRAM = MP.ISTYPEPROGRAM,
        @LIFETIMEINSTALLMENTPOSTSTATUSCODE = case when MP.PROGRAMTYPECODE = 2 then MP.INSTALLMENTPOSTSTATUSCODE end
    from
        dbo.MEMBERSHIPPROGRAM MP
    where MP.ID = @MEMBERSHIPPROGRAMID

    -----

    -- Fetch level data

    -----


    set @MEMBERSHIPLEVELS =
    (
        select * from (
        select
            ROWNUM = row_number() over (partition by ML.ID order by ML.ID, ML.SEQUENCE),
            ML.ID,
            ML.NAME,
            ML.SEQUENCE,
            ML.DESCRIPTION,
            ML.OBTAINLEVELCODE,
            AMOUNT = coalesce(MLT.AMOUNT, 0),
            LOWAMOUNT = coalesce(MLT.LOWAMOUNT,0),
            ML.MEMBERSALLOWED,
            ML.CARDSALLOWED,
            ML.CHILDRENALLOWED,
            TIERCODEID = TC.ID,
            ML.FORCEMANUALDOWNGRADES,
            HASMULTIPLETERMS = 1,
            ML.ISACTIVE,
            ML.MEMBERSHIPTERMTYPECODE,
        MP.BASECURRENCYID
                ,dbo.UFN_MEMBERSHIPLEVEL_INUSE(ML.ID) [INUSE]
        from dbo.MEMBERSHIPPROGRAM MP
            inner join dbo.MEMBERSHIPLEVEL ML on MP.ID = ML.MEMBERSHIPPROGRAMID
            left join dbo.MEMBERSHIPLEVELTERM MLT on ML.ID = MLT.LEVELID
            left outer join dbo.TIERCODE TC on ML.TIERCODEID = TC.ID
        where MP.ID = @MEMBERSHIPPROGRAMID
        ) as L
      where rownum = 1
        order by SEQUENCE
                        for xml raw('ITEM'),type,elements,root('MEMBERSHIPLEVELS'),BINARY BASE64
    )

    if @PROGRAMTYPECODE = 0 and @HASMULTIPLETERMS = 1 
    begin
      -----

      -- Fetch annual multiple term payment options

      -----

      set @MULTIPLETERMSUNITS = (
      select ID = newid(),
          TERMTIMELENGTH,
        TERMLENGTH = TERMLENGTHCODE,
        TERMSEQUENCE = SEQUENCE,
        ISACTIVE = ISACTIVE,
                INUSE = INUSE
      from
         dbo.UFN_MEMBERSHIPPROGRAM_GETTERMS(@MEMBERSHIPPROGRAMID)
      order by SEQUENCE
      for xml raw('ITEM'),type,elements,root('MULTIPLETERMSUNITS'),BINARY BASE64)
    end

    -----

    -- Fetch recurring payment data

    -----


    set @RECURRINGPRICES =
    (
        select distinct
            (select top 1 ID from dbo.MEMBERSHIPLEVELTERM where LEVELID = ML.ID) as ID,
            ML.ID as LEVELID,
            ML.NAME as LEVELNAME,
            case when ML.ISACTIVE = 1 then 'Yes' else 'no' end as ISACTIVE,
            MP.BASECURRENCYID,
      ML.SEQUENCE,
            (select ID from dbo.MEMBERSHIPLEVELTERM where RECURRINGPAYMENTOPTIONCODE = 0 and LEVELID = ML.ID) as ANNUALLYTERMID,
            (select AMOUNT from dbo.MEMBERSHIPLEVELTERM where RECURRINGPAYMENTOPTIONCODE = 0 and LEVELID = ML.ID) as ANNUALLYPRICE,
            (select ID from dbo.MEMBERSHIPLEVELTERM where RECURRINGPAYMENTOPTIONCODE = 1 and LEVELID = ML.ID) as SEMIANNUALLYTERMID,
            (select AMOUNT from dbo.MEMBERSHIPLEVELTERM where RECURRINGPAYMENTOPTIONCODE = 1 and LEVELID = ML.ID) as SEMIANNUALLYPRICE,
            (select ID from dbo.MEMBERSHIPLEVELTERM where RECURRINGPAYMENTOPTIONCODE = 2 and LEVELID = ML.ID) as QUARTERLYTERMID,
            (select AMOUNT from dbo.MEMBERSHIPLEVELTERM where RECURRINGPAYMENTOPTIONCODE = 2 and LEVELID = ML.ID) as QUARTERLYPRICE,
            (select ID from dbo.MEMBERSHIPLEVELTERM where RECURRINGPAYMENTOPTIONCODE = 3 and LEVELID = ML.ID) as MONTHLYTERMID,
            (select AMOUNT from dbo.MEMBERSHIPLEVELTERM where RECURRINGPAYMENTOPTIONCODE = 3 and LEVELID = ML.ID) as MONTHLYPRICE
        from
            dbo.MEMBERSHIPLEVEL ML
            inner join dbo.MEMBERSHIPLEVELTERM MLT on MLT.LEVELID = ML.ID
            inner join dbo.MEMBERSHIPPROGRAM MP on MP.ID = ML.MEMBERSHIPPROGRAMID AND MP.PROGRAMTYPECODE = 1
        where
            MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
        order by ML.SEQUENCE
        for xml raw('ITEM'),type,elements,root('RECURRINGPRICES'),BINARY BASE64
    )

    -----

    -- Fetch lifetime payment options

    -----

    set @LIFETIMEPAYMENTOPTIONS = 
    (
        select PROGRAMID = MEMBERSHIPPROGRAMID,
            TERMSEQUENCE = SEQUENCE,
            PAYMENTOPTION = LIFETIMEPAYMENTOPTIONCODE,
            NUMBEROFPAYMENTS = LIFETIMENUMBEROFPAYMENTS,
            ISACTIVE,
            INUSE
        from
            dbo.UFN_MEMBERSHIPPROGRAM_GETLIFETIMEPAYMENTOPTIONS(@MEMBERSHIPPROGRAMID)
        order by SEQUENCE
        for xml raw('ITEM'),type,elements,root('LIFETIMEPAYMENTOPTIONS'),BINARY BASE64
    );

    -----

    -- Fetch multiple terms pricing data

    -----

  if @HASMULTIPLETERMS = 1
  begin
      with CTE_MULTIPLEPRICES
      as
      (
      select MEMBERSHIPLEVELTERM.LEVELID, MEMBERSHIPLEVELTERM.ID, ROW_NUMBER() over (partition by MEMBERSHIPLEVELTERM.LEVELID order by MEMBERSHIPLEVELTERM.SEQUENCE) as SEQUENCE
      from dbo.MEMBERSHIPLEVEL
      inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIPLEVELTERM.LEVELID = MEMBERSHIPLEVEL.ID
      where MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
      )
      select @MULTITERMPRICES = 
      (
          select distinct
          ML.ID,
          MP.PROGRAMBASEDONCODE,
          MP.PROGRAMTYPECODE,
          MP.BASECURRENCYID,
          ML.NAME as LEVEL,
          ML.ID as LEVELID,
          ML.SEQUENCE as LEVELSEQUENCE,
          ML.OBTAINLEVELCODE as OBTAINLEVELCODE,
          case when ML.ISACTIVE = 1 then 'Yes' else 'No' end as ISACTIVE,
        MLT1.ID as TERMID1,
        dbo.UFN_MEMBERSHIPLEVELTERMS_PAYMENTOPTIONDESCRIPTION(MLT1.ID) AS TERM1,
        MLT1.ISACTIVE as TERMACTIVE1,
          MLT1.LIFETIMEPAYMENTOPTIONCODE as LIFETIMEPAYMENTOPTIONCODE1,
          MLT1.LIFETIMENUMBEROFPAYMENTS as NUMBEROFPAYMENTS1,
          MLT1.LOWAMOUNT as LOWAMOUNT1,
          MLT1.AMOUNT as AMOUNT1,
          MLT1.SEQUENCE as TERMSEQUENCE1,
                MLT2.ID as TERMID2,
          dbo.UFN_MEMBERSHIPLEVELTERMS_PAYMENTOPTIONDESCRIPTION(MLT2.ID) AS TERM2,
        MLT2.ISACTIVE as TERMACTIVE2,
          MLT2.LIFETIMEPAYMENTOPTIONCODE as LIFETIMEPAYMENTOPTIONCODE2,
          MLT2.LIFETIMENUMBEROFPAYMENTS as NUMBEROFPAYMENTS2,
          MLT2.LOWAMOUNT as LOWAMOUNT2,
          MLT2.AMOUNT as AMOUNT2,
          MLT2.SEQUENCE as TERMSEQUENCE2,
                MLT3.ID as TERMID3,
          dbo.UFN_MEMBERSHIPLEVELTERMS_PAYMENTOPTIONDESCRIPTION(MLT3.ID) AS TERM3,
        MLT3.ISACTIVE as TERMACTIVE3,
          MLT3.LIFETIMEPAYMENTOPTIONCODE as LIFETIMEPAYMENTOPTIONCODE3,
          MLT3.LIFETIMENUMBEROFPAYMENTS as NUMBEROFPAYMENTS3,
    MLT3.LOWAMOUNT as LOWAMOUNT3,
          MLT3.AMOUNT as AMOUNT3,
          MLT3.SEQUENCE as TERMSEQUENCE3,
                MLT4.ID as TERMID4,
          dbo.UFN_MEMBERSHIPLEVELTERMS_PAYMENTOPTIONDESCRIPTION(MLT4.ID) AS TERM4,
        MLT4.ISACTIVE as TERMACTIVE4,
          MLT4.LIFETIMEPAYMENTOPTIONCODE as LIFETIMEPAYMENTOPTIONCODE4,
          MLT4.LIFETIMENUMBEROFPAYMENTS as NUMBEROFPAYMENTS4,
          MLT4.LOWAMOUNT as LOWAMOUNT4,
          MLT4.AMOUNT as AMOUNT4,
          MLT4.SEQUENCE as TERMSEQUENCE4,
                MLT5.ID as TERMID5,
          dbo.UFN_MEMBERSHIPLEVELTERMS_PAYMENTOPTIONDESCRIPTION(MLT5.ID) AS TERM5,
        MLT5.ISACTIVE as TERMACTIVE5,
          MLT5.LIFETIMEPAYMENTOPTIONCODE as LIFETIMEPAYMENTOPTIONCODE5,
          MLT5.LIFETIMENUMBEROFPAYMENTS as NUMBEROFPAYMENTS5,
          MLT5.LOWAMOUNT as LOWAMOUNT5,
          MLT5.AMOUNT as AMOUNT5,
          MLT5.SEQUENCE as TERMSEQUENCE5,
                MLT6.ID as TERMID6,
          dbo.UFN_MEMBERSHIPLEVELTERMS_PAYMENTOPTIONDESCRIPTION(MLT6.ID) AS TERM6,
        MLT6.ISACTIVE as TERMACTIVE6,
          MLT6.LIFETIMEPAYMENTOPTIONCODE as LIFETIMEPAYMENTOPTIONCODE6,
          MLT6.LIFETIMENUMBEROFPAYMENTS as NUMBEROFPAYMENTS6,
          MLT6.LOWAMOUNT as LOWAMOUNT6,
          MLT6.AMOUNT as AMOUNT6,
          MLT6.SEQUENCE as TERMSEQUENCE6,
                MLT7.ID as TERMID7,
          dbo.UFN_MEMBERSHIPLEVELTERMS_PAYMENTOPTIONDESCRIPTION(MLT7.ID) AS TERM7,
        MLT7.ISACTIVE as TERMACTIVE7,
          MLT7.LIFETIMEPAYMENTOPTIONCODE as LIFETIMEPAYMENTOPTIONCODE7,
          MLT7.LIFETIMENUMBEROFPAYMENTS as NUMBEROFPAYMENTS7,
          MLT7.LOWAMOUNT as LOWAMOUNT7,
          MLT7.AMOUNT as AMOUNT7,
          MLT7.SEQUENCE as TERMSEQUENCE7,
                MLT8.ID as TERMID8,
          dbo.UFN_MEMBERSHIPLEVELTERMS_PAYMENTOPTIONDESCRIPTION(MLT8.ID) AS TERM8,
        MLT8.ISACTIVE as TERMACTIVE8,
          MLT8.LIFETIMEPAYMENTOPTIONCODE as LIFETIMEPAYMENTOPTIONCODE8,
          MLT8.LIFETIMENUMBEROFPAYMENTS as NUMBEROFPAYMENTS8,
          MLT8.LOWAMOUNT as LOWAMOUNT8,
          MLT8.AMOUNT as AMOUNT8,
          MLT8.SEQUENCE as TERMSEQUENCE8,
                MLT9.ID as TERMID9,
          dbo.UFN_MEMBERSHIPLEVELTERMS_PAYMENTOPTIONDESCRIPTION(MLT9.ID) AS TERM9,
        MLT9.ISACTIVE as TERMACTIVE9,
          MLT9.LIFETIMEPAYMENTOPTIONCODE as LIFETIMEPAYMENTOPTIONCODE9,
          MLT9.LIFETIMENUMBEROFPAYMENTS as NUMBEROFPAYMENTS9,
          MLT9.LOWAMOUNT as LOWAMOUNT9,
          MLT9.AMOUNT as AMOUNT9,
          MLT9.SEQUENCE as TERMSEQUENCE9,
                MLT10.ID as TERMID10,
          dbo.UFN_MEMBERSHIPLEVELTERMS_PAYMENTOPTIONDESCRIPTION(MLT10.ID) AS TERM10,
        MLT10.ISACTIVE as TERMACTIVE10,
          MLT10.LIFETIMEPAYMENTOPTIONCODE as LIFETIMEPAYMENTOPTIONCODE1,
          MLT10.LIFETIMENUMBEROFPAYMENTS as NUMBEROFPAYMENTS10,
          MLT10.LOWAMOUNT as LOWAMOUNT10,
          MLT10.AMOUNT as AMOUNT10,
          MLT10.SEQUENCE as TERMSEQUENCE10
          from 
              dbo.MEMBERSHIPPROGRAM MP
              left outer join dbo.MEMBERSHIPLEVEL ML ON MP.ID = ML.MEMBERSHIPPROGRAMID
              left outer join CTE_MULTIPLEPRICES CTE_MP1 on CTE_MP1.LEVELID = ML.ID and CTE_MP1.SEQUENCE = 1
              left outer join dbo.MEMBERSHIPLEVELTERM MLT1 on MLT1.ID = CTE_MP1.ID
              left outer join CTE_MULTIPLEPRICES CTE_MP2 on CTE_MP2.LEVELID = ML.ID and CTE_MP2.SEQUENCE = 2
              left outer join dbo.MEMBERSHIPLEVELTERM MLT2 on MLT2.ID = CTE_MP2.ID
              left outer join CTE_MULTIPLEPRICES CTE_MP3 on CTE_MP3.LEVELID = ML.ID and CTE_MP3.SEQUENCE = 3
              left outer join dbo.MEMBERSHIPLEVELTERM MLT3 on MLT3.ID = CTE_MP3.ID 
              left outer join CTE_MULTIPLEPRICES CTE_MP4 on CTE_MP4.LEVELID = ML.ID and CTE_MP4.SEQUENCE = 4
              left outer join dbo.MEMBERSHIPLEVELTERM MLT4 on MLT4.ID = CTE_MP4.ID 
              left outer join CTE_MULTIPLEPRICES CTE_MP5 on CTE_MP5.LEVELID = ML.ID and CTE_MP5.SEQUENCE = 5
              left outer join dbo.MEMBERSHIPLEVELTERM MLT5 on MLT5.ID = CTE_MP5.ID 
              left outer join CTE_MULTIPLEPRICES CTE_MP6 on CTE_MP6.LEVELID = ML.ID and CTE_MP6.SEQUENCE = 6
              left outer join dbo.MEMBERSHIPLEVELTERM MLT6 on MLT6.ID = CTE_MP6.ID
              left outer join CTE_MULTIPLEPRICES CTE_MP7 on CTE_MP7.LEVELID = ML.ID and CTE_MP7.SEQUENCE = 7 
              left outer join dbo.MEMBERSHIPLEVELTERM MLT7 on MLT7.ID = CTE_MP7.ID 
              left outer join CTE_MULTIPLEPRICES CTE_MP8 on CTE_MP8.LEVELID = ML.ID and CTE_MP8.SEQUENCE = 8
              left outer join dbo.MEMBERSHIPLEVELTERM MLT8 on MLT8.ID = CTE_MP8.ID 
              left outer join CTE_MULTIPLEPRICES CTE_MP9 on CTE_MP9.LEVELID = ML.ID and CTE_MP9.SEQUENCE = 9
              left outer join dbo.MEMBERSHIPLEVELTERM MLT9 on MLT9.ID = CTE_MP9.ID
              left outer join CTE_MULTIPLEPRICES CTE_MP10 on CTE_MP10.LEVELID = ML.ID and CTE_MP10.SEQUENCE = 10
              left outer join dbo.MEMBERSHIPLEVELTERM MLT10 on MLT10.ID = CTE_MP10.ID
          where ML.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
          order by ML.SEQUENCE
            for xml raw('ITEM'),type,elements,root('MULTITERMPRICES'),BINARY BASE64
      )
    end

    -----

    -- Fetch options

    -----


    declare @PAYMENTOPTIONS table
    (
        OPTIONCODE tinyint,
        ISACTIVE tinyint
    )

    insert into @PAYMENTOPTIONS
    (
        OPTIONCODE, ISACTIVE
    )
    select distinct
        MLT.RECURRINGPAYMENTOPTIONCODE, MLT.ISACTIVE
    from
        dbo.MEMBERSHIPLEVELTERM MLT
            inner join dbo.MEMBERSHIPLEVEL ML on ML.ID = MLT.LEVELID
    where
        MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID

    set @ANNUALLYPAYMENTOPTION =
        case
            when exists (select OPTIONCODE from @PAYMENTOPTIONS where OPTIONCODE = 0) then 1
            else 0
        end

    set @ANNUALLYOPTIONACTIVE =
        case
            when exists (select OPTIONCODE from @PAYMENTOPTIONS where OPTIONCODE = 0 and ISACTIVE = 1) then 1
            else 0
        end

    set @SEMIANNUALLYPAYMENTOPTION =
        case
            when exists (select OPTIONCODE from @PAYMENTOPTIONS where OPTIONCODE = 1) then 1
            else 0
        end

    set @SEMIANNUALLYOPTIONACTIVE =
        case
            when exists (select OPTIONCODE from @PAYMENTOPTIONS where OPTIONCODE = 1 and ISACTIVE = 1) then 1
            else 0
        end

    set @QUARTERLYPAYMENTOPTION =
        case
            when exists (select OPTIONCODE from @PAYMENTOPTIONS where OPTIONCODE = 2) then 1
            else 0
        end

    set @QUARTERLYOPTIONACTIVE =
        case
            when exists (select OPTIONCODE from @PAYMENTOPTIONS where OPTIONCODE = 2 and ISACTIVE = 1) then 1
            else 0
        end

    set @MONTHLYPAYMENTOPTION =
        case
            when exists (select OPTIONCODE from @PAYMENTOPTIONS where OPTIONCODE = 3) then 1
            else 0
        end

    set @MONTHLYOPTIONACTIVE =
        case
            when exists (select OPTIONCODE from @PAYMENTOPTIONS where OPTIONCODE = 3 and ISACTIVE = 1) then 1
            else 0
        end

    set @DATALOADED = 1

    return 0;