Padreug
4d6ea8f163
The column was renamed to fee_fraction (schema_version 13), so the main query has been failing outright with "no such column: t.fee_percent" — the tool only ever worked in --summary and --inventory mode. Caught while reconciling sintra's cassettes, where listing transactions was the obvious first step and didn't work. Refs #40
153 lines
4.1 KiB
Bash
153 lines
4.1 KiB
Bash
#!/usr/bin/env bash
|
|
# atm-transactions — Query ATM transaction history from SQLite
|
|
#
|
|
# Usage:
|
|
# atm-transactions # Show all transactions
|
|
# atm-transactions --today # Today's transactions
|
|
# atm-transactions --type cash_in # Only cash-in
|
|
# atm-transactions --type cash_out # Only cash-out
|
|
# atm-transactions --last 5 # Last 5 transactions
|
|
# atm-transactions --since 2026-02-28 # Since a date
|
|
# atm-transactions --summary # Summary totals only
|
|
# atm-transactions --inventory # Current cassette & cashbox state
|
|
# atm-transactions --csv # Output as CSV (pipe to file)
|
|
|
|
set -euo pipefail
|
|
|
|
DB="/var/lib/bitspire/state.db"
|
|
|
|
if [ ! -f "$DB" ]; then
|
|
echo "ERROR: Database not found at $DB" >&2
|
|
exit 1
|
|
fi
|
|
|
|
FORMAT="-column -header"
|
|
|
|
sql() {
|
|
sqlite3 $FORMAT "$DB" "$1"
|
|
}
|
|
|
|
# Parse arguments
|
|
TYPE=""
|
|
LIMIT=""
|
|
SINCE=""
|
|
SUMMARY=false
|
|
INVENTORY=false
|
|
TODAY=false
|
|
CSV=false
|
|
|
|
while [[ $# -gt 0 ]]; do
|
|
case "$1" in
|
|
--type)
|
|
TYPE="$2"; shift 2 ;;
|
|
--last)
|
|
LIMIT="$2"; shift 2 ;;
|
|
--since)
|
|
SINCE="$2"; shift 2 ;;
|
|
--today)
|
|
TODAY=true; shift ;;
|
|
--summary)
|
|
SUMMARY=true; shift ;;
|
|
--inventory)
|
|
INVENTORY=true; shift ;;
|
|
--csv)
|
|
CSV=true; FORMAT="-csv -header"; shift ;;
|
|
-h|--help)
|
|
echo "Usage: atm-transactions [OPTIONS]"
|
|
echo ""
|
|
echo "Options:"
|
|
echo " --type cash_in|cash_out Filter by transaction type"
|
|
echo " --last N Show last N transactions"
|
|
echo " --since YYYY-MM-DD Show transactions since date"
|
|
echo " --today Show today's transactions"
|
|
echo " --summary Show summary totals"
|
|
echo " --inventory Show cassette & cashbox state"
|
|
echo " --csv Output as CSV (pipe to file)"
|
|
echo " -h, --help Show this help"
|
|
exit 0 ;;
|
|
*)
|
|
echo "Unknown option: $1" >&2; exit 1 ;;
|
|
esac
|
|
done
|
|
|
|
# Inventory mode
|
|
if $INVENTORY; then
|
|
echo "=== Cassettes ==="
|
|
sql "SELECT denomination AS 'Denom', count AS 'Bills', denomination * count AS 'Value' FROM cassettes ORDER BY denomination;"
|
|
echo ""
|
|
echo "=== Cashbox ==="
|
|
sql "SELECT total_bills AS 'Bills', total_fiat_cents / 100 AS 'Total', CASE WHEN last_emptied_at IS NOT NULL THEN datetime(last_emptied_at / 1000, 'unixepoch') ELSE 'never' END AS 'Last Emptied' FROM cashbox;"
|
|
exit 0
|
|
fi
|
|
|
|
# Summary mode
|
|
if $SUMMARY; then
|
|
echo "=== Transaction Summary ==="
|
|
sql "SELECT
|
|
type AS 'Type',
|
|
COUNT(*) AS 'Count',
|
|
SUM(fiat_cents) / 100 AS 'Total Fiat',
|
|
SUM(sats) AS 'Total Sats',
|
|
SUM(fee_sats) AS 'Total Fee Sats',
|
|
MIN(fiat_cents) / 100 AS 'Min Fiat',
|
|
MAX(fiat_cents) / 100 AS 'Max Fiat'
|
|
FROM transactions
|
|
GROUP BY type;"
|
|
echo ""
|
|
echo "=== Daily Breakdown ==="
|
|
sql "SELECT
|
|
date(created_at / 1000, 'unixepoch') AS 'Date',
|
|
type AS 'Type',
|
|
COUNT(*) AS 'Txns',
|
|
SUM(fiat_cents) / 100 AS 'Fiat',
|
|
SUM(sats) AS 'Sats'
|
|
FROM transactions
|
|
GROUP BY date(created_at / 1000, 'unixepoch'), type
|
|
ORDER BY 1 DESC, 2;"
|
|
exit 0
|
|
fi
|
|
|
|
# Build WHERE clause
|
|
WHERE=""
|
|
CONDITIONS=()
|
|
|
|
if [ -n "$TYPE" ]; then
|
|
CONDITIONS+=("type = '$TYPE'")
|
|
fi
|
|
|
|
if $TODAY; then
|
|
CONDITIONS+=("date(created_at / 1000, 'unixepoch') = date('now')")
|
|
fi
|
|
|
|
if [ -n "$SINCE" ]; then
|
|
CONDITIONS+=("date(created_at / 1000, 'unixepoch') >= '$SINCE'")
|
|
fi
|
|
|
|
if [ ${#CONDITIONS[@]} -gt 0 ]; then
|
|
WHERE="WHERE $(IFS=' AND '; echo "${CONDITIONS[*]}")"
|
|
fi
|
|
|
|
# Build LIMIT clause
|
|
LIMIT_SQL=""
|
|
if [ -n "$LIMIT" ]; then
|
|
LIMIT_SQL="LIMIT $LIMIT"
|
|
fi
|
|
|
|
# Main query
|
|
sql "SELECT
|
|
t.txid AS 'TX ID',
|
|
t.type AS 'Type',
|
|
t.currency AS 'Cur',
|
|
t.fiat_cents / 100 AS 'Fiat',
|
|
t.sats AS 'Sats',
|
|
t.fee_sats AS 'Fee Sats',
|
|
printf('%.1f%%', t.fee_fraction * 100) AS 'Fee %',
|
|
CASE WHEN t.exchange_rate > 0 THEN printf('%.0f', t.exchange_rate) ELSE '-' END AS 'Rate',
|
|
datetime(t.created_at / 1000, 'unixepoch') AS 'Time (UTC)',
|
|
group_concat('Q' || tb.denomination || 'x' || tb.count) AS 'Bills'
|
|
FROM transactions t
|
|
LEFT JOIN transaction_bills tb ON t.txid = tb.txid
|
|
$WHERE
|
|
GROUP BY t.txid
|
|
ORDER BY t.created_at DESC
|
|
$LIMIT_SQL;"
|