USP_SALESBYPAYMENTMETHODREPORTDATA

CREATE procedure dbo.USP_SALESBYPAYMENTMETHODREPORTDATA (
    @FROMDATE datetime = null,
    @TODATE datetime = null,
    @APPUSERID uniqueidentifier = null
)
as
    set nocount on;

    set @FROMDATE = cast(@FROMDATE as date);
    set @TODATE = dbo.UFN_DATE_GETLATESTTIME(@TODATE);

    --sales order payments

    select
        [REVENUEPAYMENTMETHOD].[ID] as [PAYMENTID],
        dbo.UFN_APPUSER_GETNAME([SALESORDERPAYMENT].[APPUSERID]) as [TRANSACTIONSELLER],
        [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE],
        [REVENUEPAYMENTMETHOD].[PAYMENTMETHOD],
        convert(nvarchar(20), SALESORDER.SEQUENCEID) as [SALESORDERNUMBER],
        'http://www.blackbaud.com/SALESORDERLINK?SALESORDERLINK=' + CONVERT(nvarchar(36),SALESORDER.ID) as [SALESORDERLINK],
        cast([SALESORDERPAYMENT].[PAYMENTDATEWITHTIMEOFFSET] as datetime) as [TRANSACTIONDATE],
        [SALESORDERPAYMENT].[AMOUNT] as [AMOUNTPAID],
        case [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] 
            when 0 then ''
            when 1 then (
            select [CHECKNUMBER] + ', ' + convert(nvarchar(20), dbo.UFN_DATE_FROMFUZZYDATE(CHECKDATE), 101)
                from dbo.[CHECKPAYMENTMETHODDETAIL]
                where ID = [REVENUEPAYMENTMETHOD].[ID]
            )
            when 2 then (
            select dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITTYPECODEID) 
                from dbo.[CREDITCARDPAYMENTMETHODDETAIL] 
                where ID = [REVENUEPAYMENTMETHOD].[ID]
            )
            when 10 then (
            select dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION(OTHERPAYMENTMETHODCODEID)
                from dbo.[OTHERPAYMENTMETHODDETAIL]
                where ID = [REVENUEPAYMENTMETHOD].[ID]
            )
        end as [PAYMENTDETAILS],
        case [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE]
            when 2 then (
                select dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITTYPECODEID) 
                from dbo.[CREDITCARDPAYMENTMETHODDETAIL] 
                where ID = [REVENUEPAYMENTMETHOD].[ID]
            )
            when 10 then (
                select dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION(OTHERPAYMENTMETHODCODEID)
                from dbo.[OTHERPAYMENTMETHODDETAIL]
                where ID = [REVENUEPAYMENTMETHOD].[ID]
            )
            else ''
        end as [PAYMENTTYPE],
        NF.NAME as [PATRONNAME]
    from
        dbo.FINANCIALTRANSACTION as FT
    inner join
        dbo.[REVENUEPAYMENTMETHOD] on [REVENUEPAYMENTMETHOD].[REVENUEID] = FT.ID
    inner join
        dbo.[SALESORDERPAYMENT] on [SALESORDERPAYMENT].[PAYMENTID] = FT.ID
    inner join
        dbo.[SALESORDER] on [SALESORDERPAYMENT].[SALESORDERID] = [SALESORDER].[ID]
    outer apply
        dbo.UFN_CONSTITUENT_DISPLAYNAME(SALESORDER.CONSTITUENTID) as NF
    where
        (
            @APPUSERID is null
            or [SALESORDERPAYMENT].[APPUSERID] = @APPUSERID
        ) and
        FT.CALCULATEDDATE between @FROMDATE and @TODATE and
        ([SALESORDER].[STATUSCODE] = 1 or [SALESORDER].[SALESMETHODTYPECODE] = 3) and
        [SALESORDERPAYMENT].[AMOUNT] <> 0
        and FT.DELETEDON is null

    union all
    --refunds

    select
        [REVENUEPAYMENTMETHOD].[ID] as [PAYMENTID],
        dbo.UFN_APPUSER_GETNAME([CREDITPAYMENT].[APPUSERID]) as [APPLICATIONUSER],
        [CREDITPAYMENT].[PAYMENTMETHODCODE],
        [CREDITPAYMENT].[PAYMENTMETHOD],
        'Refund' as [SALESORDERNUMBER],
        'http://www.blackbaud.com/REFUNDLINK?REFUNDLINK=' + CONVERT(nvarchar(36), FT.ID) as REFUNDLINK,
        cast([CREDITPAYMENT].[CREDITPAYMENTDATEWITHTIMEOFFSET] as datetime),
        -[CREDITPAYMENT].[AMOUNT] as [AMOUNTPAID],
        case [CREDITPAYMENT].[PAYMENTMETHODCODE] 
            when 0 then ''
            when 1 then ''
            when 2 then (
            select dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITTYPECODEID) 
                from dbo.[CREDITCARDPAYMENTMETHODDETAIL] 
                where ID = [REVENUEPAYMENTMETHOD].[ID]
            )
            when 10 then (
            dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION([CREDITPAYMENT].[OTHERPAYMENTMETHODCODEID])
            )
        end as [PAYMENTDETAILS],
        case [CREDITPAYMENT].[PAYMENTMETHODCODE]
            when 2 then (
                select dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITTYPECODEID) 
                from dbo.[CREDITCARDPAYMENTMETHODDETAIL] 
                where ID = [REVENUEPAYMENTMETHOD].[ID]
            )
            when 10 then (
                dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION([CREDITPAYMENT].[OTHERPAYMENTMETHODCODEID])
            )
            else ''
        end as [PAYMENTTYPE],
        NF.NAME as [PATRONNAME]
    from
        dbo.FINANCIALTRANSACTION as FT
    inner join
        dbo.[CREDITPAYMENT] on [CREDITPAYMENT].[CREDITID] = FT.ID
    left join
        dbo.FINANCIALTRANSACTIONLINEITEM as REFUNDEDLI on REFUNDEDLI.ID = [CREDITPAYMENT].[REVENUESPLITID]
    inner join
        dbo.[REVENUEPAYMENTMETHOD] on [REVENUEPAYMENTMETHOD].[REVENUEID] in (CREDITPAYMENT.REVENUEID, REFUNDEDLI.FINANCIALTRANSACTIONID)
    outer apply
        dbo.UFN_CONSTITUENT_DISPLAYNAME(FT.CONSTITUENTID) as NF
    where
        (
            @APPUSERID is null
            or FT.APPUSERID = @APPUSERID
        ) and
        FT.CALCULATEDDATE between @FROMDATE and @TODATE and
        [CREDITPAYMENT].[AMOUNT] <> 0 and
        FT.TYPECODE = 23  -- Refund


    --security deposit payments

    union all
    select
        [REVENUEPAYMENTMETHOD].[ID] as [PAYMENTID],
        dbo.UFN_APPUSER_GETNAME([RESERVATIONSECURITYDEPOSITPAYMENT].[APPUSERID]) as [APPLICATIONUSER],
        [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE],
        [REVENUEPAYMENTMETHOD].[PAYMENTMETHOD],
        convert(nvarchar(20), SALESORDER.SEQUENCEID) as [SALESORDERNUMBER],
        'http://www.blackbaud.com/SALESORDERLINK?SALESORDERLINK=' + CONVERT(nvarchar(36), [RESERVATIONSECURITYDEPOSITPAYMENT].RESERVATIONID) as [SALESORDERLINK],
        cast([RESERVATIONSECURITYDEPOSITPAYMENT].[PAYMENTDATEWITHTIMEOFFSET] as datetime),
        [RESERVATIONSECURITYDEPOSITPAYMENT].[AMOUNT] as [AMOUNTPAID],
        case [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] 
            when 0 then ''
            when 1 then (
            select [CHECKNUMBER] + ', ' + convert(nvarchar(20), dbo.UFN_DATE_FROMFUZZYDATE(CHECKDATE), 101)
                from dbo.[CHECKPAYMENTMETHODDETAIL]
                where ID = [REVENUEPAYMENTMETHOD].[ID]
            )
            when 2 then (
            select dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITTYPECODEID) 
                from dbo.[CREDITCARDPAYMENTMETHODDETAIL] 
                where ID = [REVENUEPAYMENTMETHOD].[ID]
            )
            when 10 then (
            select dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION(OTHERPAYMENTMETHODCODEID)
                from dbo.[OTHERPAYMENTMETHODDETAIL]
                where ID = [REVENUEPAYMENTMETHOD].[ID]
            )
        end as [PAYMENTDETAILS],
        case [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE]
            when 2 then (
                select dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITTYPECODEID) 
                from dbo.[CREDITCARDPAYMENTMETHODDETAIL] 
                where ID = [REVENUEPAYMENTMETHOD].[ID]
            )
            when 10 then (
                select dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION(OTHERPAYMENTMETHODCODEID)
                from dbo.[OTHERPAYMENTMETHODDETAIL]
                where ID = [REVENUEPAYMENTMETHOD].[ID]
            )
            else ''
        end as [PAYMENTTYPE],
        NF.NAME as [PATRONNAME]
    from
        dbo.[REVENUEPAYMENTMETHOD]
    inner join
        dbo.[RESERVATIONSECURITYDEPOSITPAYMENT] on [RESERVATIONSECURITYDEPOSITPAYMENT].[PAYMENTID] = [REVENUEPAYMENTMETHOD].[REVENUEID]
    inner join
        dbo.[SALESORDER] on [RESERVATIONSECURITYDEPOSITPAYMENT].[RESERVATIONID] = [SALESORDER].[ID]
    outer apply
        dbo.UFN_CONSTITUENT_DISPLAYNAME(SALESORDER.CONSTITUENTID) as NF
    where
        (
            @APPUSERID is null
            or [RESERVATIONSECURITYDEPOSITPAYMENT].[APPUSERID] = @APPUSERID
        ) and
        cast([RESERVATIONSECURITYDEPOSITPAYMENT].[PAYMENTDATEWITHTIMEOFFSET] as datetime) between @FROMDATE and @TODATE and
        [RESERVATIONSECURITYDEPOSITPAYMENT].[AMOUNT] <> 0
    order by 
        [TRANSACTIONSELLER] ASC,
        [REVENUEPAYMENTMETHOD].[PAYMENTMETHOD] ASC,
        [SALESORDERNUMBER] ASC,
        [AMOUNTPAID] ASC;

    return 0;