UFN_REGISTRANT_GETDATEANDPAYMENTMETHODLISTS

CREATE function dbo.UFN_REGISTRANT_GETDATEANDPAYMENTMETHODLISTS
(
    @REGISTRANTID uniqueidentifier
)
returns @RESULTS table (DATELIST nvarchar(max), PAYMENTMETHODLIST nvarchar(max), SALESMETHODLIST nvarchar(max), APPUSERLIST nvarchar(max))
as
begin
    declare @DATELIST nvarchar(max) = null;
    declare @METHODLIST nvarchar(max) = null;
    declare @APPUSERLIST nvarchar(max) = null;
    declare @SALESMETHODLIST nvarchar(max) = null;
    declare @SEPERATER nchar(2) = N', ';

    select
        @APPUSERLIST = dbo.UDA_BUILDLISTWITHDELIMITER(distinct APPUSER.DISPLAYNAME, @SEPERATER),
        @SALESMETHODLIST = dbo.UDA_BUILDLISTWITHDELIMITER(distinct isnull(SALESORDER.SALESMETHODTYPE, N'Back office'), @SEPERATER)
    from
        dbo.EVENTREGISTRANTPAYMENT
    inner join
        dbo.FINANCIALTRANSACTIONLINEITEM on EVENTREGISTRANTPAYMENT.PAYMENTID = FINANCIALTRANSACTIONLINEITEM.ID
    inner join
        dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
    left outer join
        dbo.SALESORDER on FINANCIALTRANSACTION.ID = SALESORDER.REVENUEID
    left outer join
        dbo.APPUSER on SALESORDER.APPUSERID = APPUSER.ID
    where
        EVENTREGISTRANTPAYMENT.REGISTRANTID = @REGISTRANTID;

    -- Back office event registrations

    select
        @METHODLIST = coalesce(@METHODLIST, '') + 
                    coalesce(
                        case 
                            when @METHODLIST like '%' + 
                                            case REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE
                                                when 10 then OTHERPAYMENTMETHODCODE.DESCRIPTION
                                                else REVENUEPAYMENTMETHOD.PAYMENTMETHOD
                                            end + '%' then null
                            else case REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE
                                    when 10 then OTHERPAYMENTMETHODCODE.DESCRIPTION
                                    else REVENUEPAYMENTMETHOD.PAYMENTMETHOD
                                end                        
                        end + @SEPERATER, ''
                    )
    from
        dbo.EVENTREGISTRANTPAYMENT
    inner join
        dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.ID = EVENTREGISTRANTPAYMENT.PAYMENTID
    inner join
        dbo.FINANCIALTRANSACTION as FT on FT.ID = LI.FINANCIALTRANSACTIONID
    inner join
        dbo.REVENUEPAYMENTMETHOD on FT.ID = REVENUEPAYMENTMETHOD.REVENUEID
    left outer join
        dbo.OTHERPAYMENTMETHODDETAIL on REVENUEPAYMENTMETHOD.ID = OTHERPAYMENTMETHODDETAIL.ID
    left outer join
        dbo.OTHERPAYMENTMETHODCODE on OTHERPAYMENTMETHODDETAIL.OTHERPAYMENTMETHODCODEID = OTHERPAYMENTMETHODCODE.ID
    where
        EVENTREGISTRANTPAYMENT.REGISTRANTID = @REGISTRANTID
        and REVENUEPAYMENTMETHOD.AMOUNT > 0
        and FT.DELETEDON is null
        and LI.DELETEDON is null
        and LI.TYPECODE <> 1  -- Reversal

    order by
        FT.DATE;

    -- Front office event registrations

    select
        @METHODLIST = coalesce(@METHODLIST, '') + 
                    coalesce(
                        case 
                            when @METHODLIST like '%' + 
                                                case REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE
                                                    when 10 then OTHERPAYMENTMETHODCODE.DESCRIPTION
                                                    else REVENUEPAYMENTMETHOD.PAYMENTMETHOD
                                                end + '%' then null
                            else case REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE
                                    when 10 then OTHERPAYMENTMETHODCODE.DESCRIPTION
                                    else REVENUEPAYMENTMETHOD.PAYMENTMETHOD
                                end                        
                        end + @SEPERATER, ''
                    )
    from
        dbo.SALESORDER
    inner join
        dbo.SALESORDERITEM on SALESORDER.ID = SALESORDERITEM.SALESORDERID
    inner join
        dbo.SALESORDERITEMEVENTREGISTRATION on SALESORDERITEM.ID = SALESORDERITEMEVENTREGISTRATION.ID
    inner join
        dbo.SALESORDERPAYMENT on SALESORDER.ID = SALESORDERPAYMENT.SALESORDERID
    inner join
        dbo.REVENUEPAYMENTMETHOD on SALESORDERPAYMENT.PAYMENTID = REVENUEPAYMENTMETHOD.REVENUEID
    left outer join
        dbo.OTHERPAYMENTMETHODDETAIL on REVENUEPAYMENTMETHOD.ID = OTHERPAYMENTMETHODDETAIL.ID
    left outer join
        dbo.OTHERPAYMENTMETHODCODE on OTHERPAYMENTMETHODDETAIL.OTHERPAYMENTMETHODCODEID = OTHERPAYMENTMETHODCODE.ID
    where
        SALESORDERITEMEVENTREGISTRATION.REGISTRANTID = @REGISTRANTID
    order by
        SALESORDER.TRANSACTIONDATE;

    if (@METHODLIST like '%' + @SEPERATER)
        set @METHODLIST = substring(@METHODLIST, 0, len(@METHODLIST));

    select
        @DATELIST = coalesce(@DATELIST, '') + 
                    coalesce(
                        case 
                            when @DATELIST like '%' + convert(nvarchar, cast(FT.DATE as datetime)) + '%' then null
                            else convert(nvarchar, cast(FT.DATE as datetime))
                        end + @SEPERATER, ''
                    )
    from
        dbo.EVENTREGISTRANTPAYMENT
    inner join
        dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.ID = EVENTREGISTRANTPAYMENT.PAYMENTID
    inner join
        dbo.FINANCIALTRANSACTION as FT on FT.ID = LI.FINANCIALTRANSACTIONID
    where
        EVENTREGISTRANTPAYMENT.REGISTRANTID = @REGISTRANTID
    group by
        FT.ID, FT.DATE
    order by
        FT.DATE;

    if (@DATELIST like '%' + @SEPERATER)
        set @DATELIST = substring(@DATELIST, 0, len(@DATELIST));

    insert into @RESULTS (DATELIST, PAYMENTMETHODLIST, SALESMETHODLIST, APPUSERLIST) values (@DATELIST, @METHODLIST, @SALESMETHODLIST, @APPUSERLIST);

    return;
end