USP_REPORT_REFUNDS

create procedure dbo.USP_REPORT_REFUNDS
(
    @ITEMTYPECODE tinyint = null,
    @REFUNDMETHODTYPECODE tinyint = null,
    @OTHERPAYMENTMETHODCODEID uniqueidentifier = null,
    @STARTDATE datetime = null,
    @ENDDATE datetime = null,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @INCLUDESENTCHECKS bit = null
)
as
    set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
    set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

    declare @USERGRANTEDCONSTITUENTPAGE bit = 0; 
    declare @USERGRANTEDREFUNDPAGE bit = 0;

    if dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
        begin
            select 
                @USERGRANTEDCONSTITUENTPAGE = 1, 
                @USERGRANTEDREFUNDPAGE = 1;
        end
    else
        begin
            select 
                @USERGRANTEDCONSTITUENTPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, '0C836902-A398-47a0-91EB-8B66E434148E'),
                @USERGRANTEDREFUNDPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, 'b2130dfa-51f6-4205-87e6-7d132573cb4d');                
        end

    declare @REFUNDS table (
        ID uniqueidentifier,
        TRANSACTIONDATE datetime,
        CONSTITUENTID uniqueidentifier,
        SALESORDERID uniqueidentifier,
        AMOUNT money
    );


    insert into @REFUNDS
        select
            FT.ID,
            cast(FT.DATE as datetime),
            FT.CONSTITUENTID,
            CREDIT_EXT.SALESORDERID,
            FT.BASEAMOUNT
        from dbo.FINANCIALTRANSACTION FT
        inner join dbo.CREDIT_EXT on FT.ID = CREDIT_EXT.ID
        where FT.TYPECODE = 23
            and (@STARTDATE is null or FT.CALCULATEDDATE >= @STARTDATE)
            and (@ENDDATE is null or FT.CALCULATEDDATE <= @ENDDATE)
            and (
                (
                    @ITEMTYPECODE in (0,4) and CREDIT_EXT.SALESORDERID is not null -- Orders

                )
                or (
                    @ITEMTYPECODE = 0
                    and exists(
                            select EXT.ID
                            from dbo.CREDITITEM_EXT EXT
                            where EXT.TYPECODE in (1,2,6,16)                        -- Membership, Donation, Event registration, Membership add-ons

                                and EXT.CREDITID = FT.ID
                    )
                )
                or (
                    @ITEMTYPECODE = 1
                    and exists(
                            select EXT.ID
                            from dbo.CREDITITEM_EXT EXT
                            where EXT.TYPECODE = 2                                -- Donation

                                and EXT.CREDITID = FT.ID
                    )
                )
                or (
                    @ITEMTYPECODE = 2
                    and exists(
                            select EXT.ID
                            from dbo.CREDITITEM_EXT EXT
                            where EXT.TYPECODE = 6                                -- Event registration

                                and EXT.CREDITID = FT.ID
                    )
                )
                or (
                    @ITEMTYPECODE = 3
                    and exists(
                            select EXT.ID
                            from dbo.CREDITITEM_EXT EXT
                            where EXT.TYPECODE in (1,16)                                -- Membership, Membership add-ons

                                and EXT.CREDITID = FT.ID
                    )
                )                
            )
            and (
                @REFUNDMETHODTYPECODE = 0
                or exists(
                    select ID
                    from dbo.CREDITPAYMENT
                    where CREDITPAYMENT.CREDITID = FT.ID
                        and CREDITPAYMENT.PAYMENTMETHODCODE = @REFUNDMETHODTYPECODE - 1
                        and (
                            (CREDITPAYMENT.OTHERPAYMENTMETHODCODEID is null and @OTHERPAYMENTMETHODCODEID is null)
                            or CREDITPAYMENT.OTHERPAYMENTMETHODCODEID = @OTHERPAYMENTMETHODCODEID
                        )
                )
            )
            and (
                @INCLUDESENTCHECKS = 1
                or not exists (
                        select CREDITPAYMENT.ID
                        from dbo.CREDITPAYMENT
                        inner join dbo.CREDITPAYMENTCHECKPAYMENTMETHODDETAIL
                            on CREDITPAYMENT.ID = CREDITPAYMENTCHECKPAYMENTMETHODDETAIL.ID
                        where CREDITPAYMENT.CREDITID = FT.ID
                            and CREDITPAYMENT.PAYMENTMETHODCODE = 1
                            and CREDITPAYMENTCHECKPAYMENTMETHODDETAIL.CHECKDATE <> '00000000'
                    )
            );

    declare @DATA table (
        CREDITID uniqueidentifier,
        CONSTITUENTID uniqueidentifier,
        [DATE] datetime,
        ITEMDESCRIPTION nvarchar(max),
        ADDRESSID uniqueidentifier,
        DATEPROCESSED date,
        CHECKNUMBER nvarchar(20),
        AMOUNT money
    );

    -- Populate data table

    insert into @DATA
        select
            FILTERED.ID,
            FILTERED.CONSTITUENTID,
            FILTERED.TRANSACTIONDATE,
            coalesce('Order ' + cast(SALESORDER.SEQUENCEID as nvarchar) + ': ', '') + dbo.UFN_CREDIT_GETITEMLIST(FILTERED.ID),
            CHECKINFO.ADDRESSID,
            case CHECKINFO.CHECKDATE
                when '00000000' then null
                else CHECKINFO.CHECKDATE
            end as DATEPROCESSED,
            CHECKINFO.CHECKNUMBER,
            FILTERED.AMOUNT as AMOUNT
        from @REFUNDS FILTERED
        left outer join dbo.SALESORDER
            on FILTERED.SALESORDERID = SALESORDER.ID
        outer apply (
            select top(1)
                CREDITPAYMENTCHECKPAYMENTMETHODDETAIL.ADDRESSID,
                CREDITPAYMENTCHECKPAYMENTMETHODDETAIL.CHECKDATE,
                CREDITPAYMENTCHECKPAYMENTMETHODDETAIL.CHECKNUMBER
            from dbo.CREDITPAYMENT
            inner join dbo.CREDITPAYMENTCHECKPAYMENTMETHODDETAIL
                on CREDITPAYMENT.ID = CREDITPAYMENTCHECKPAYMENTMETHODDETAIL.ID
            where CREDITPAYMENT.CREDITID = FILTERED.ID
                and CREDITPAYMENT.PAYMENTMETHODCODE = 1
        ) as CHECKINFO;

    select 
        'http://www.blackbaud.com/CREDITID?CREDITID=' + CONVERT(nvarchar(36), DATA.CREDITID) as REFUNDLINK,
        @USERGRANTEDREFUNDPAGE as USERGRANTEDREFUNDLINK,
        'http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID=' + CONVERT(nvarchar(36), DATA.CONSTITUENTID) as CONSTITUENTLINK,
        @USERGRANTEDCONSTITUENTPAGE as USERGRANTEDCONSTITUENTLINK,
        DATA.[DATE],
        DATA.ITEMDESCRIPTION,
        CONSTITUENTNAMEFORMAT.NAME as CONSTITUENT,
        dbo.UFN_ADDRESS_GETDESCRIPTION(DATA.ADDRESSID) as ADDRESS,
        dbo.UFN_CREDIT_GETPAYMENTMETHODLIST(DATA.CREDITID) as REFUNDMETHOD,
        DATA.DATEPROCESSED,
        DATA.CHECKNUMBER,
        DATA.AMOUNT
    from @DATA DATA
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(DATA.CONSTITUENTID) as CONSTITUENTNAMEFORMAT
    order by DATA.[DATE] desc;

    return 0;