USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTMEMBERSHIPPROGRAM

                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTMEMBERSHIPPROGRAM
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @MEMBERSHIPLEVELNAME nvarchar(100) = null output,
                    @MEMBERSHIPTERM nvarchar(12) = null output,
                    @HIDEMEMBERSHIPTERM bit = null output,
                    @AMOUNT money = null output,
                    @BASECURRENCYID uniqueidentifier = null output,
                    @MEMBERSHIPSTATUS nvarchar(15) = null output,
                    @MEMBERS xml = null output,
                    @NUMBEROFMEMBERS integer = null output,
                    @CHILDREN integer = null output,
                    @EXPIRATIONDATE date = null output,
                    @RENEWALWINDOWEXPIRATIONDATE date = null output,
                    @MEMBERSHIPSTATUSAFTERRENEWALWINDOW tinyint = null output,
                    @MEMBERSINCEDATE date = null output,
                    @ISGIFT bit = null output,
                    @GIVENBYID uniqueidentifier = null output,
                    @GIVENBYNAME nvarchar(154) = null output,
                    @NEXTLEVEL nvarchar(100) = null output,
                    @UPGRADECOST money = null output,
                    @CONSTITUENTID uniqueidentifier = null output,
                    @MEMBERSHIPID uniqueidentifier = null output,
                    @RENEWALWINDOWREVENUETYPECODE tinyint = null output,
                    @AFTEREXPIRATION integer = null output,
                    @PROGRAMTYPECODE tinyint = null output,
                    @PROGRAMTYPE nvarchar(40) = null output,
                    @PAYS nvarchar(40) = null output,
                    @BALANCE money = null output,
                    @NEXTPAYMENT date = null output,
                    @PAIDINFULLDATE date = null output,
                    @TOTALPAYMENTSMADE int = null output,
                    @LASTPAIDDATE date = null output,
                    @LASTPAIDAMOUNT money = null output,
                    @FREQUENCY nvarchar(40) = null output,
                    @INSTALLMENTAMOUNT money = null output,
                    @PAYSINSTALLMENTS bit = null output,
                    @REVENUEID uniqueidentifier = null output,
                    @MEMBERID uniqueidentifier = null output,
                    @MEMBERSHIPSTATUSCODE integer = null output,
                    @PROGRAMISACTIVE bit = null output,
                    @CANCELLATIONREASONTIP nvarchar(50) = null output,
                    @AUTOMATICALLYRENEWMEMBERSHIP bit = null output,
                    @LASTPAIDINFO nvarchar(40) = null output,
                    @WHEREISREVENUETRACKEDCODE tinyint = null output,
                    @ISUPGRADEABLE bit = null output,
                    @PDACCOUNTSYSTEMID uniqueidentifier = null output,
                    @PENDINGBATCHNUMBER nvarchar(100) = null output,
                    @HASOUTSTANDINGPLEDGE bit = null output,
                    @HASMEMBERSHIPLEVELTYPES bit = null output
                )
                as

                declare @PROGRAMBASEDONCODE tinyint = 0, @MEMBERSHIPTRANSACTIONBASEAMOUNT money;

                set nocount on;

                set @DATALOADED = 0;

                set @MEMBERS =
                    (
                        select
                            NAMEFORMAT.NAME as MEMBERNAME,
                            CONSTITUENT.LOOKUPID,
                            MEMBERS.CONSTITUENTID as MEMBERCONSTITUENTID,
                            MEMBERS.ISPRIMARY
                        from dbo.UFN_MEMBERSHIP_GETACTIVEMEMBERS(@ID) MEMBERS
                            inner join dbo.CONSTITUENT on MEMBERS.CONSTITUENTID = CONSTITUENT.ID
                            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NAMEFORMAT
                        order by MEMBERS.ISPRIMARY desc
                        for xml raw('ITEM'),type,elements,root('MEMBERS'),BINARY BASE64
                    );

                select @NUMBEROFMEMBERS = count(CONSTITUENTID)
                from dbo.UFN_MEMBERSHIP_GETACTIVEMEMBERS(@ID);

                select
                    @CONSTITUENTID = MEMBER.CONSTITUENTID,
                    @MEMBERID = MEMBER.ID
                from dbo.MEMBERSHIP
                    inner join dbo.MEMBER on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
                where MEMBERSHIP.ID = @ID and MEMBER.ISPRIMARY = 1;

                set @MEMBERSHIPID = @ID;

                select
                    @DATALOADED = 1,
                    @MEMBERSHIPLEVELNAME = MEMBERSHIPLEVEL.NAME,
                    @MEMBERSHIPTERM = case when (select count(ID) from dbo.MEMBERSHIPLEVELTERM TERMS where MEMBERSHIPLEVEL.ID = TERMS.LEVELID) > 1 and MEMBERSHIPPROGRAM.PROGRAMTYPECODE = 0 then cast(MEMBERSHIPLEVELTERM.TERMTIMELENGTH as nvarchar(5)) + ' ' + MEMBERSHIPLEVELTERM.TERMLENGTH else null end,
                    @HIDEMEMBERSHIPTERM = case when (select count(ID) from dbo.MEMBERSHIPLEVELTERM TERMS where MEMBERSHIPLEVEL.ID = TERMS.LEVELID) > 1 and MEMBERSHIPPROGRAM.PROGRAMTYPECODE = 0 then 0 else 1 end,
                    @AMOUNT = MEMBERSHIPLEVELTERM.AMOUNT,
                    @BASECURRENCYID = MEMBERSHIPLEVELTERM.BASECURRENCYID,
                    @MEMBERSHIPSTATUS =  MEMBERSHIP.STATUS,
                    @MEMBERSHIPSTATUSCODE = MEMBERSHIP.STATUSCODE,
                    @CHILDREN = MEMBERSHIP.NUMBEROFCHILDREN,
                    @EXPIRATIONDATE = MEMBERSHIP.EXPIRATIONDATE,
                    @RENEWALWINDOWEXPIRATIONDATE = dbo.UFN_MEMBERSHIP_GETRENEWALWINDOWENDDATE(MEMBERSHIP.ID, MEMBERSHIP.EXPIRATIONDATE),
                    @MEMBERSHIPSTATUSAFTERRENEWALWINDOW = dbo.UFN_MEMBERSHIP_GETSTATUSCODE_AFTERRENEWALWINDOW(MEMBERSHIPLEVEL.ID),
                    @MEMBERSINCEDATE = MEMBERSHIP.JOINDATE,
                    @ISGIFT = MEMBERSHIP.ISGIFT,
                    @GIVENBYID = MEMBERSHIP.GIVENBYID,
                    @GIVENBYNAME = CONSTITUENT.NAME,
                    @RENEWALWINDOWREVENUETYPECODE = MEMBERSHIPLEVEL.RENEWALWINDOWREVENUETYPECODE,
                    @AFTEREXPIRATION = MEMBERSHIPLEVEL.AFTEREXPIRATION,
                    @PROGRAMTYPECODE = MEMBERSHIPPROGRAM.PROGRAMTYPECODE,
                    @PROGRAMTYPE = MEMBERSHIPPROGRAM.PROGRAMTYPE,
                    @PROGRAMBASEDONCODE = MEMBERSHIPPROGRAM.PROGRAMBASEDONCODE,
                    @PROGRAMISACTIVE = MEMBERSHIPPROGRAM.ISACTIVE,
                    @CANCELLATIONREASONTIP = MEMBERSHIPCANCELLATIONCODE.CODE + ' - ' + MEMBERSHIPCANCELLATIONCODE.DESCRIPTION,
                    @AUTOMATICALLYRENEWMEMBERSHIP = MEMBERSHIP.AUTOMATICALLYRENEWMEMBERSHIP,
                    @WHEREISREVENUETRACKEDCODE = MEMBERSHIPPROGRAM.WHEREISREVENUETRACKEDCODE,
                    @HASMEMBERSHIPLEVELTYPES = case when (select count(ID) from dbo.MEMBERSHIPLEVELTYPE MLT where LEVELID = MEMBERSHIPLEVEL.ID) > 0 then 1 else 0 end
                from dbo.MEMBERSHIP
                    inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
                    inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
                    inner join dbo.MEMBERSHIPPROGRAM on MEMBERSHIP.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
                    left join dbo.CONSTITUENT on CONSTITUENT.ID = MEMBERSHIP.GIVENBYID
                    left join dbo.MEMBERSHIPCANCELLATIONCODE on MEMBERSHIP.CANCELLATIONREASONCODEID = MEMBERSHIPCANCELLATIONCODE.ID
                where MEMBERSHIP.ID = @ID;

                set @TOTALPAYMENTSMADE = 0;

                declare @LASTPAIDTRANSACTIONAMOUNT money;

                select top 1
                    @LASTPAIDAMOUNT = MEMBERSHIPTRANSACTIONAMOUNT.AMOUNT,
                    @LASTPAIDDATE =  TRANSACTIONDATE,
                    @LASTPAIDTRANSACTIONAMOUNT = MEMBERSHIPTRANSACTIONAMOUNT.AMOUNT,
                    @PDACCOUNTSYSTEMID = FINANCIALTRANSACTION.PDACCOUNTSYSTEMID
                from dbo.MEMBERSHIPTRANSACTION MT
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = MT.REVENUESPLITID
                    inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                    outer apply dbo.UFN_MEMBERSHIPTRANSACTION_AMOUNT(MT.ID, 1) as MEMBERSHIPTRANSACTIONAMOUNT  -- 1 = Currency base amount

                where MT.MEMBERSHIPID = @MEMBERSHIPID
                and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
                and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                order by MT.TRANSACTIONDATE DESC, MT.DATEADDED DESC;

                set @REVENUEID = dbo.UFN_MEMBERSHIP_GETPLEDGE(@MEMBERSHIPID);

                declare @ISPENDING bit = 0;

                if @REVENUEID is not null
                begin
                    select
                        @TOTALPAYMENTSMADE = count(distinct FT.ID),
                        @LASTPAIDDATE = max(FT.DATE)
                    from dbo.INSTALLMENTSPLIT S
                        join dbo.INSTALLMENTSPLITPAYMENT SP on S.ID = SP.INSTALLMENTSPLITID and S.PLEDGEID = @REVENUEID
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on SP.PAYMENTID = FTLI.ID
                        inner join dbo.FINANCIALTRANSACTION FT on FTLI.FINANCIALTRANSACTIONID = FT.ID
                        where FTLI.TYPECODE <> 1
                        and FTLI.DELETEDON is null;

                    select
                        @INSTALLMENTAMOUNT = INSTALLMENT.AMOUNT,
                        @NEXTPAYMENT = INSTALLMENT.DATE,
                        @FREQUENCY = REVENUESCHEDULE.FREQUENCY
                    from MEMBERSHIP
                        left join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = @REVENUEID
                        left join dbo.INSTALLMENT on INSTALLMENT.ID = dbo.UFN_PLEDGE_GETNEXTINSTALLMENT(@REVENUEID, null)
                        left join dbo.REVENUESCHEDULE ON FINANCIALTRANSACTION.ID = REVENUESCHEDULE.ID
                    where MEMBERSHIP.ID = @MEMBERSHIPID
                  and FINANCIALTRANSACTION.DELETEDON is null;

                    select @BALANCE = dbo.UFN_PLEDGE_GETBALANCE(@REVENUEID);
                    set @PAYSINSTALLMENTS = 1;

                    set @ISPENDING = (select REVENUESCHEDULE.ISPENDING from dbo.REVENUESCHEDULE where REVENUESCHEDULE.ID = @REVENUEID);

                    if @ISPENDING = 1
                    begin
                            select top 1 
                                @PENDINGBATCHNUMBER = BATCH.BATCHNUMBER
                            from dbo.BATCH
                            inner join dbo.BATCHMEMBERSHIPDUES on BATCHMEMBERSHIPDUES.BATCHID = BATCH.ID and dbo.UFN_MEMBERSHIP_GETPLEDGE(BATCHMEMBERSHIPDUES.EXISTINGMEMBERSHIPID) = @REVENUEID
                            where 
                                BATCH.STATUSCODE not in (1, 2) 
                                and BATCHMEMBERSHIPDUES.EXISTINGMEMBERSHIPID is not null
                                and BATCHMEMBERSHIPDUES.REVENUETYPECODE = 1;
                    end

                end
                else --OK now try it as a recurring

                begin
                    set @REVENUEID = dbo.UFN_MEMBERSHIP_GETRECURRINGGIFT_2(@MEMBERSHIPID, 1);
                    if @REVENUEID is not null
                    begin
                        select
                            @NEXTPAYMENT = case when REVENUESCHEDULE.NEXTTRANSACTIONDATE > REVENUESCHEDULE.ENDDATE then null else REVENUESCHEDULE.NEXTTRANSACTIONDATE end,
                            @FREQUENCY = REVENUESCHEDULE.FREQUENCY
                        from dbo.FINANCIALTRANSACTION
                            inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = FINANCIALTRANSACTION.ID
                            inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = FINANCIALTRANSACTION.ID
                            left join dbo.CHANNELCODE on CHANNELCODE.ID = REVENUE_EXT.CHANNELCODEID
                        where FINANCIALTRANSACTION.ID = @REVENUEID
                        and FINANCIALTRANSACTION.DELETEDON is null;

                        set @LASTPAIDDATE = null;
                        set @LASTPAIDAMOUNT = null;

                        select top 1
                            @LASTPAIDDATE = FINANCIALTRANSACTION.[DATE],
                            @LASTPAIDAMOUNT = sum(RECURRINGGIFTINSTALLMENTPAYMENT.AMOUNT)
                        from dbo.RECURRINGGIFTINSTALLMENT
                            inner join dbo.RECURRINGGIFTINSTALLMENTPAYMENT on RECURRINGGIFTINSTALLMENT.ID = RECURRINGGIFTINSTALLMENTPAYMENT.RECURRINGGIFTINSTALLMENTID
                            inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = RECURRINGGIFTINSTALLMENTPAYMENT.PAYMENTID
                        where
                            RECURRINGGIFTINSTALLMENT.REVENUEID = @REVENUEID and
                            FINANCIALTRANSACTION.DELETEDON is null
                        group by FINANCIALTRANSACTION.[DATE]
                        order by FINANCIALTRANSACTION.[DATE] desc;

                        set @ISPENDING = (select REVENUESCHEDULE.ISPENDING from dbo.REVENUESCHEDULE where REVENUESCHEDULE.ID = @REVENUEID);

                        if @ISPENDING = 1
                        begin
                                select top 1 
                                    @PENDINGBATCHNUMBER = BATCH.BATCHNUMBER
                                from dbo.BATCH
                                inner join dbo.BATCHMEMBERSHIPDUES on BATCHMEMBERSHIPDUES.BATCHID = BATCH.ID and dbo.UFN_MEMBERSHIP_GETRECURRINGGIFT(BATCHMEMBERSHIPDUES.EXISTINGMEMBERSHIPID) = @REVENUEID
                                where 
                                    BATCH.STATUSCODE not in (1, 2) 
                                    and BATCHMEMBERSHIPDUES.EXISTINGMEMBERSHIPID is not null
                                    and BATCHMEMBERSHIPDUES.REVENUETYPECODE = 1;
                        end

                    end
                    else
                    begin
                            --If this is a lifetime program and it wasn't trapped by the installment plan check above, then the term option is pay in full and the number of payments is 1.

                            if @PROGRAMTYPECODE = 2
                            begin
                                    set @TOTALPAYMENTSMADE = 1;
                            end
                    end
                end

                if @BALANCE = 0 OR @BALANCE IS NULL SET @PAIDINFULLDATE = @LASTPAIDDATE
                    set @PAYS = cast(@INSTALLMENTAMOUNT as nvarchar(max)) + ' ' + lower(@FREQUENCY); --this will get formatted properly in the VB


                declare @OBTAINLEVELCODE tinyint;
                declare @MOSTRECENTTRANSACTIONID uniqueidentifier;
                declare @MOSTRECENTLEVEL uniqueidentifier;

                select top 1
                    @OBTAINLEVELCODE = MEMBERSHIPLEVEL.OBTAINLEVELCODE,
                    @MOSTRECENTTRANSACTIONID = MEMBERSHIPTRANSACTION.ID,
                    @MOSTRECENTLEVEL = MEMBERSHIPLEVEL.ID,
                    @MEMBERSHIPTRANSACTIONBASEAMOUNT = MEMBERSHIPTRANSACTION.BASEAMOUNT
                from dbo.MEMBERSHIPTRANSACTION
                    inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
                where MEMBERSHIPTRANSACTION.MEMBERSHIPID = @MEMBERSHIPID
                order by MEMBERSHIPTRANSACTION.TRANSACTIONDATE DESC, MEMBERSHIPTRANSACTION.DATEADDED DESC;

                set @AMOUNT = @LASTPAIDTRANSACTIONAMOUNT;
                if @WHEREISREVENUETRACKEDCODE = 1
                    set @AMOUNT = @MEMBERSHIPTRANSACTIONBASEAMOUNT;

                if @OBTAINLEVELCODE = 1 and @WHEREISREVENUETRACKEDCODE = 0
                begin
                    select @AMOUNT = SUM(AMOUNT)
                    from dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
                    where (ORIGINALMEMBERSHIPTRANSACTIONID = @MOSTRECENTTRANSACTIONID);
                end

                set @ISUPGRADEABLE = dbo.UFN_MEMBERSHIP_ISUPGRADEABLE(@MEMBERSHIPID,default);

                set @HASOUTSTANDINGPLEDGE = dbo.UFN_MEMBERSHIP_HASOUTSTANDINGPLEDGE(@ID);