I am seeing some weirdness with some refund entries in the db and civi UI. Or I am misunderstanding.
Here is an example of a contact that looks correct to me (refunds have two entries, A positive for the initial charge and a negative for the refunds):
select
ft.id as trxn_id,
ft.trxn_date,
CASE
WHEN ft.total_amount > 0 THEN 'inflow'
ELSE 'outflow'
END as designation,
ft.total_amount as trxn_amount,
ft.currency,
c.contact_id,
c.contribution_status_id,
c.financial_type_id,
c.id as civi_id,
c.receive_date
FROM civicrm.civicrm_financial_trxn ft
LEFT JOIN civicrm.civicrm_entity_financial_trxn eft ON ft.id=eft.financial_trxn_id AND entity_table='civicrm_contribution'
LEFT JOIN civicrm.civicrm_contribution c on c.id = eft.entity_id
LEFT JOIN civicrm.civicrm_option_value AS cov ON CAST(c.payment_instrument_id AS UNSIGNED) = CAST(cov.value AS UNSIGNED) AND CAST(cov.option_group_id AS UNSIGNED) = CAST(10 AS UNSIGNED)
LEFT JOIN civicrm.civicrm_value_1_gift_data_7 AS gift ON c.id = gift.entity_id
LEFT JOIN civicrm.wmf_contribution_extra AS extra ON c.id = extra.entity_id
LEFT JOIN civicrm.civicrm_contribution_tracking AS t ON c.id = t.contribution_id
WHERE c.contact_id = 3687444
AND c.id is not null
AND ft.is_payment=1
AND c.financial_type_id=14;
And here is one where there are just positive values (which are counting towards their donation total in the civi ui) marked refund without a corresponding original charge:
select
ft.id as trxn_id,
ft.trxn_date,
CASE
WHEN ft.total_amount > 0 THEN 'inflow'
ELSE 'outflow'
END as designation,
ft.total_amount as trxn_amount,
ft.currency,
c.contact_id,
c.contribution_status_id,
c.financial_type_id,
c.id as civi_id,
c.receive_date
FROM civicrm.civicrm_financial_trxn ft
LEFT JOIN civicrm.civicrm_entity_financial_trxn eft ON ft.id=eft.financial_trxn_id AND entity_table='civicrm_contribution'
LEFT JOIN civicrm.civicrm_contribution c on c.id = eft.entity_id
LEFT JOIN civicrm.civicrm_option_value AS cov ON CAST(c.payment_instrument_id AS UNSIGNED) = CAST(cov.value AS UNSIGNED) AND CAST(cov.option_group_id AS UNSIGNED) = CAST(10 AS UNSIGNED)
LEFT JOIN civicrm.civicrm_value_1_gift_data_7 AS gift ON c.id = gift.entity_id
LEFT JOIN civicrm.wmf_contribution_extra AS extra ON c.id = extra.entity_id
LEFT JOIN civicrm.civicrm_contribution_tracking AS t ON c.id = t.contribution_id
WHERE c.contact_id = 57805094
AND c.id is not null
AND ft.is_payment=1
AND c.financial_type_id=14;
I do just exclude refunds from normal reporting, but am working on something for finance reconciliation where in and outflows are needing to be represented.