USP_PRINTMEMBERSHIPCARDS_GENERATECARDSNUMBER

CREATE procedure dbo.USP_PRINTMEMBERSHIPCARDS_GENERATECARDSNUMBER (
    @PRINTMEMBERSHIPCARDSPROCESSSTATUSID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @OUTPUTTABLENAME nvarchar(100) = null
)
as
    set nocount on;

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

    begin try

        declare @SQL nvarchar(max)

        if @OUTPUTTABLENAME is null
            select @OUTPUTTABLENAME = TABLENAME
                from dbo.BUSINESSPROCESSOUTPUT
                where BUSINESSPROCESSSTATUSID = @PRINTMEMBERSHIPCARDSPROCESSSTATUSID

        -- Update membership card number

        set @SQL = '
            update dbo.MEMBERSHIPCARD
            set CARDNUMBER = convert(nvarchar(100), COMPUTEDMEMBERSHIPID) + 
                    case 
                        when (case 
                                when M.LASTCARDNUMBER < isnull(CARDNUMERTABLE.SUFFIX, 0)
                                    then isnull(CARDNUMERTABLE.SUFFIX, 0)
                                else M.LASTCARDNUMBER
                            end + SUFFIXTABLE.ROWNUMBER < 10) then ''00'' + convert(char(1), case 
                                                                                            when M.LASTCARDNUMBER < isnull(CARDNUMERTABLE.SUFFIX, 0)
                                                                                                then isnull(CARDNUMERTABLE.SUFFIX, 0)
                                                                                            else M.LASTCARDNUMBER
                                                                                        end + SUFFIXTABLE.ROWNUMBER)
                        when (case 
                                when M.LASTCARDNUMBER < isnull(CARDNUMERTABLE.SUFFIX, 0)
                                    then isnull(CARDNUMERTABLE.SUFFIX, 0)
                                else M.LASTCARDNUMBER
                            end + SUFFIXTABLE.ROWNUMBER >= 10) and (case 
                                                                        when M.LASTCARDNUMBER < isnull(CARDNUMERTABLE.SUFFIX, 0)
                                                                            then isnull(CARDNUMERTABLE.SUFFIX, 0)
                                                                        else M.LASTCARDNUMBER
                                                                    end + SUFFIXTABLE.ROWNUMBER < 100) then ''0'' + convert(char(2), case 
                                                                                                                                    when M.LASTCARDNUMBER < isnull(CARDNUMERTABLE.SUFFIX, 0)
                                                                                                                                        then isnull(CARDNUMERTABLE.SUFFIX, 0)
                                                                                                                                    else M.LASTCARDNUMBER
                                                                                                                                end + SUFFIXTABLE.ROWNUMBER)
                        when (case 
                                when M.LASTCARDNUMBER < isnull(CARDNUMERTABLE.SUFFIX, 0)
                                    then isnull(CARDNUMERTABLE.SUFFIX, 0)
                                else M.LASTCARDNUMBER
                            end + SUFFIXTABLE.ROWNUMBER >=100) and (case 
                                                                        when M.LASTCARDNUMBER < isnull(CARDNUMERTABLE.SUFFIX, 0)
                                                                            then isnull(CARDNUMERTABLE.SUFFIX, 0)
                                                                        else M.LASTCARDNUMBER
                                                                    end + SUFFIXTABLE.ROWNUMBER < 1000) then convert(char(3), case 
                                                                                                                                when M.LASTCARDNUMBER < isnull(CARDNUMERTABLE.SUFFIX, 0)
                                                                                                                                    then isnull(CARDNUMERTABLE.SUFFIX, 0)
                                                                                                                                else M.LASTCARDNUMBER
                                                                                                                            end + SUFFIXTABLE.ROWNUMBER)
                    end,

                PRINTDATE = getdate(),
                PRINTEDBYID = ''' + convert(nvarchar(100), @CHANGEAGENTID) + ''',
                PRINTMEMBERSHIPCARDSPROCESSSTATUSID = ''' + convert(nvarchar(100), @PRINTMEMBERSHIPCARDSPROCESSSTATUSID) + ''',
                DATECHANGED = getdate(), 
                CHANGEDBYID = ''' + convert(nvarchar(100), @CHANGEAGENTID) + '''

                from ' + @OUTPUTTABLENAME + ' OUTPUTTABLE
                inner join dbo.MEMBERSHIPCARD MC on OUTPUTTABLE.MEMBERSHIPCARDID = MC.ID
                inner join dbo.MEMBERSHIP M on OUTPUTTABLE.ID = M.ID
                left join (select MEMBERSHIP.SEQUENCEID, MAX(CONVERT(int, right(MEMBERSHIPCARD.CARDNUMBER, 3))) as SUFFIX
                            from dbo.MEMBERSHIPCARD
                            inner join dbo.MEMBER on MEMBERSHIPCARD.MEMBERID = MEMBER.ID
                            inner join dbo.MEMBERSHIP on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
                            where MEMBERSHIPCARD.CARDNUMBER like convert(nvarchar(100), MEMBERSHIP.SEQUENCEID) + ''%''
              and ISNUMERIC(right(MEMBERSHIPCARD.CARDNUMBER, 3)) = 1 
                            group by MEMBERSHIP.SEQUENCEID) as CARDNUMERTABLE
                    on M.SEQUENCEID = CARDNUMERTABLE.SEQUENCEID
                inner join (select MEMBERSHIPCARDID,
                                row_number() over (partition by ID order by COMPUTEDMEMBERSHIPID) as ROWNUMBER
                            from ' + @OUTPUTTABLENAME + ') as SUFFIXTABLE 
                    on SUFFIXTABLE.MEMBERSHIPCARDID = OUTPUTTABLE.MEMBERSHIPCARDID
            where MC.STATUSCODE = 0'
        exec (@SQL)

        -- Update output table

        set @SQL = '
            update ' + @OUTPUTTABLENAME + '
                set CARDNUMBER = ''*'' + MC.CARDNUMBER + ''*'',
                    CARDNUMBERDISPLAY = MC.CARDNUMBER
                from ' + @OUTPUTTABLENAME + ' OUTPUTTABLE
                inner join dbo.MEMBERSHIPCARD MC on OUTPUTTABLE.MEMBERSHIPCARDID = MC.ID
                inner join dbo.MEMBERSHIP M on OUTPUTTABLE.ID = M.ID
                '
        exec (@SQL)

        -- Update last card number of the membership        

        set @SQL = '
            update dbo.MEMBERSHIP 
            set LASTCARDNUMBER = SUFFIX
            from dbo.MEMBERSHIP M 
            inner join (select ID, MAX(CONVERT(int, right(CARDNUMBERDISPLAY, 3))) as SUFFIX from ' + @OUTPUTTABLENAME + ' 
                        group by ID) as MC on M.ID = MC.ID
        '
        exec (@SQL)

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

    return 0;