USP_SIMPLEDATALIST_MEMBERSHIP_MIDTERMUPGRADELEVELS

CREATE procedure dbo.USP_SIMPLEDATALIST_MEMBERSHIP_MIDTERMUPGRADELEVELS
(
    @MEMBERSHIPID uniqueidentifier
)
as
    declare @CURRENTPROGRAMID uniqueidentifier;
    declare @CURRENTTERMLENGTHCODE tinyint;
    declare @CURRENTTERMTIMELENGTH tinyint;
    declare @CURRENTLEVELSEQUENCE int;
    declare @CURRENTMEMBERSALLOWED int;

    select
        @CURRENTPROGRAMID = MEMBERSHIP.MEMBERSHIPPROGRAMID,
        @CURRENTTERMLENGTHCODE = MEMBERSHIPLEVELTERM.TERMLENGTHCODE,
        @CURRENTTERMTIMELENGTH = MEMBERSHIPLEVELTERM.TERMTIMELENGTH,
        @CURRENTLEVELSEQUENCE = MEMBERSHIPLEVEL.SEQUENCE,
        @CURRENTMEMBERSALLOWED = MEMBERSHIPLEVEL.MEMBERSALLOWED
    from dbo.MEMBERSHIP
        inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
        inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
    where
        MEMBERSHIP.ID = @MEMBERSHIPID

    select 
        MEMBERSHIPLEVEL.ID as VALUE, 
        MEMBERSHIPLEVEL.NAME + ' - $' + convert(nvarchar(20),dbo.UFN_MEMBERSHIP_GETMIDTERMUPGRADEPRICE(@MEMBERSHIPID, MEMBERSHIPLEVEL.ID)) as LABEL
    from dbo.MEMBERSHIPLEVEL
    where
        MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @CURRENTPROGRAMID and
        MEMBERSHIPLEVEL.SEQUENCE > @CURRENTLEVELSEQUENCE and
        MEMBERSHIPLEVEL.ISACTIVE = 1 and
        MEMBERSHIPLEVEL.MEMBERSALLOWED >= (select count(1) 
                                            from dbo.[MEMBER] 
                                            where 
                                                [MEMBER].[MEMBERSHIPID] = @MEMBERSHIPID
                                                and [MEMBER].[ISDROPPED] = 0
                                            ) and
        [MEMBERSHIPLEVEL].[CARDSALLOWED] >=    (select count(1) 
                                            from dbo.[MEMBERSHIPCARD] 
                                            inner join dbo.[MEMBER] 
                                                on [MEMBERSHIPCARD].[MEMBERID] = [MEMBER].[ID] 
                                            where 
                                                [MEMBER].[MEMBERSHIPID] = @MEMBERSHIPID and 
                                                [MEMBERSHIPCARD].[STATUSCODE] <> 2
                                            ) and
        exists (select ID from dbo.MEMBERSHIPLEVELTERM 
            where LEVELID = MEMBERSHIPLEVEL.ID and 
                    TERMLENGTHCODE = @CURRENTTERMLENGTHCODE and 
                    TERMTIMELENGTH = @CURRENTTERMTIMELENGTH and
                    ISACTIVE = 1)
    order by
        MEMBERSHIPLEVEL.SEQUENCE asc