WITH paid AS (
 SELECT p."order" AS order_id, SUM(p.cents) AS cents FROM payments p, settings s
 WHERE p.status = 'captured' AND julianday(p.at) <= julianday(s.cutoff) GROUP BY p."order"
), refunded AS (
 SELECT r."order" AS order_id, SUM(r.cents) AS cents FROM refunds r, settings s
 WHERE r.status = 'completed' AND julianday(r.at) <= julianday(s.cutoff) GROUP BY r."order"
)
SELECT o.id AS order_id, COALESCE(p.cents,0)-COALESCE(r.cents,0) AS net_cents,
 l.cents AS ledger_cents, COALESCE(p.cents,0)-COALESCE(r.cents,0)-l.cents AS difference_cents
FROM orders o JOIN ledger l ON l."order"=o.id
LEFT JOIN paid p ON p.order_id=o.id LEFT JOIN refunded r ON r.order_id=o.id
WHERE o.status='never' AND o.currency='CNY';