USP_REPORT_PRINTMEMBERSHIPCARDSNOPICTURETWOPERPAGE_OUTPUT

create procedure dbo.USP_REPORT_PRINTMEMBERSHIPCARDSNOPICTURETWOPERPAGE_OUTPUT
(
    @ID uniqueidentifier
)
as
    set nocount on;

    declare @MEMBERSHIPCARDSTOBEPRINTED table (
        MEMBERSHIPID uniqueidentifier,
        MEMBERSHIPCARDID uniqueidentifier,
        MEMBERSHIPPROGRAMNAME nvarchar(100),
        MEMBERSHIPLEVELNAME nvarchar(100),
        ORIGINALCARDNUMBER nvarchar(100),
        CARDNUMBER nvarchar(100),
        MEMBERSHIPLOOKUPID nvarchar(100),
        NAMEONCARD nvarchar(700),
        MEMBERSINCE int,
        EXPIRATIONDATE datetime,
        MEMBERSHIPCARDID2 uniqueidentifier,
        MEMBERSHIPPROGRAMNAME2 nvarchar(100),
        MEMBERSHIPLEVELNAME2 nvarchar(100),
        ORIGINALCARDNUMBER2 nvarchar(100),
        CARDNUMBER2 nvarchar(100),
        MEMBERSHIPLOOKUPID2 nvarchar(100),
        NAMEONCARD2 nvarchar(700),
        MEMBERSINCE2 int,
        EXPIRATIONDATE2 datetime
    )

    -- Table holds membership cards to be printed

    insert into @MEMBERSHIPCARDSTOBEPRINTED (
        MEMBERSHIPID,
        MEMBERSHIPCARDID,
        MEMBERSHIPPROGRAMNAME,
        MEMBERSHIPLEVELNAME,
        ORIGINALCARDNUMBER,
        CARDNUMBER,
        MEMBERSHIPLOOKUPID,
        NAMEONCARD,
        MEMBERSINCE,
        EXPIRATIONDATE
    )

    -- Select membership cards, if membership has multiple members,

    -- select the membership card with the smallest card number

    select
        MEMBERSHIP.ID, -- MEMBERSHIPID

        MEMBERSHIPCARD.ID, -- MEMBERSHIPCARDID

        dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MEMBERSHIP.MEMBERSHIPPROGRAMID), -- MEMBERSHIPPROGRAMNAME

        dbo.UFN_MEMBERSHIPLEVEL_GETNAME(MEMBERSHIP.MEMBERSHIPLEVELID), -- MEMBERSHIPLEVELNAME

        MEMBERSHIPCARD.CARDNUMBER, -- ORIGINALCARDNUMBER

        '*' + MEMBERSHIPCARD.CARDNUMBER + '*', -- CARDNUMBER

        MEMBERSHIP.LOOKUPID, -- MEMBERSHIPLOOKUPID

        MEMBERSHIPCARD.NAMEONCARD, -- NAMEONCARD

        (select datepart(year, min(MEMBERSHIPTRANSACTION.TRANSACTIONDATE))
            from dbo.MEMBERSHIPTRANSACTION
            where MEMBERSHIPTRANSACTION.MEMBERSHIPID = MEMBERSHIP.ID and MEMBERSHIPTRANSACTION.ACTIONCODE = 0
        ), -- MEMBERSINCE

        MEMBERSHIPCARD.EXPIRATIONDATE -- EXPIRATIONDATE

    from dbo.MEMBERSHIPCARD
    inner join dbo.MEMBER 
        on MEMBERSHIPCARD.MEMBERID = MEMBER.ID
    inner join dbo.MEMBERSHIP  
        on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
    where MEMBERSHIPCARD.PRINTMEMBERSHIPCARDSPROCESSSTATUSID = @ID
        and MEMBERSHIP.ID in (
            select MS.ID 
            from dbo.MEMBERSHIP MS
            inner join dbo.MEMBER M 
                on MS.ID = M.MEMBERSHIPID
            inner join dbo.MEMBERSHIPCARD MSC
                on M.ID = MSC.MEMBERID 
            group by MS.ID 
            having MEMBERSHIPCARD.CARDNUMBER = min(MSC.CARDNUMBER)
        )

    -- Update the fields of the second card

    update @MEMBERSHIPCARDSTOBEPRINTED 
    set 
        MEMBERSHIPCARDID2 = MEMBERSHIPCARD.ID, 
        MEMBERSHIPPROGRAMNAME2 = dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MEMBERSHIP.MEMBERSHIPPROGRAMID), 
        MEMBERSHIPLEVELNAME2 = dbo.UFN_MEMBERSHIPLEVEL_GETNAME(MEMBERSHIP.MEMBERSHIPLEVELID),
        ORIGINALCARDNUMBER2 = MEMBERSHIPCARD.CARDNUMBER,
        CARDNUMBER2 = '*' + MEMBERSHIPCARD.CARDNUMBER + '*', 
        MEMBERSHIPLOOKUPID2 = MEMBERSHIP.LOOKUPID,
        NAMEONCARD2 = MEMBERSHIPCARD.NAMEONCARD,
        MEMBERSINCE2 = (select datepart(year, min(MEMBERSHIPTRANSACTION.TRANSACTIONDATE))
            from dbo.MEMBERSHIPTRANSACTION
            where MEMBERSHIPTRANSACTION.MEMBERSHIPID = MEMBERSHIP.ID and MEMBERSHIPTRANSACTION.ACTIONCODE = 0
        ),
        EXPIRATIONDATE2 = MEMBERSHIPCARD.EXPIRATIONDATE
    from dbo.MEMBERSHIPCARD
    inner join dbo.MEMBER 
        on MEMBERSHIPCARD.MEMBERID = MEMBER.ID
    inner join dbo.MEMBERSHIP  
        on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
    inner join @MEMBERSHIPCARDSTOBEPRINTED MSCPRINTED
        on MSCPRINTED.MEMBERSHIPID = MEMBERSHIP.ID 
    where MEMBERSHIPCARD.PRINTMEMBERSHIPCARDSPROCESSSTATUSID = @ID
        and MEMBERSHIP.ID in (
            select MS.ID 
            from dbo.MEMBERSHIP MS
            inner join dbo.MEMBER M 
                on MS.ID = M.MEMBERSHIPID
            inner join dbo.MEMBERSHIPCARD MSC
                on M.ID = MSC.MEMBERID
            where MSC.CARDNUMBER not in (select ORIGINALCARDNUMBER from @MEMBERSHIPCARDSTOBEPRINTED)
            group by MS.ID 
            having MEMBERSHIPCARD.CARDNUMBER = min(MSC.CARDNUMBER)
        )

    select * from @MEMBERSHIPCARDSTOBEPRINTED 

    return 0;