67 lines
2.4 KiB
Python
67 lines
2.4 KiB
Python
import psycopg2
|
||
from psycopg2.extras import RealDictCursor
|
||
|
||
conn = psycopg2.connect('host=192.168.0.61 port=55432 dbname=njts_acct user=njts_app password=njts_app2025')
|
||
cur = conn.cursor(cursor_factory=RealDictCursor)
|
||
|
||
print("=" * 100)
|
||
print("【最新のEntry清單（testで始まるもの）】")
|
||
print("=" * 100)
|
||
|
||
cur.execute('''
|
||
SELECT
|
||
je.journal_entry_id,
|
||
je.entry_date,
|
||
je.description,
|
||
je.parent_entry_id,
|
||
je.is_deleted,
|
||
SUM(CASE WHEN jl.debit::numeric > 0 THEN jl.debit::numeric ELSE 0 END) as debit_total,
|
||
SUM(CASE WHEN jl.credit::numeric > 0 THEN jl.credit::numeric ELSE 0 END) as credit_total
|
||
FROM journal_entries je
|
||
LEFT JOIN journal_lines jl ON je.journal_entry_id = jl.journal_entry_id
|
||
WHERE je.description ILIKE '%test%'
|
||
AND je.entry_date >= '2025-06-01'
|
||
AND je.journal_entry_id >= 375
|
||
GROUP BY je.journal_entry_id, je.entry_date, je.description, je.parent_entry_id, je.is_deleted
|
||
ORDER BY je.journal_entry_id DESC
|
||
LIMIT 10
|
||
''')
|
||
|
||
rows = cur.fetchall()
|
||
print("\n")
|
||
for row in rows:
|
||
parent_str = f"parent={row['parent_entry_id']}" if row['parent_entry_id'] else "parent=None"
|
||
deleted_str = "❌ DELETED" if row['is_deleted'] else "✓ Active"
|
||
debit = row['debit_total'] if row['debit_total'] else 0
|
||
credit = row['credit_total'] if row['credit_total'] else 0
|
||
print(f"Entry#{row['journal_entry_id']}: {deleted_str} | {parent_str}")
|
||
print(f" Desc: {row['description'][:60]}")
|
||
print(f" Amount: 借 {debit:>10,.0f} | 貳 {credit:>10,.0f}")
|
||
print()
|
||
|
||
print("=" * 100)
|
||
print("【試算表（2025年6月売上高）】")
|
||
print("=" * 100)
|
||
|
||
cur.execute('''
|
||
SELECT
|
||
SUM(CASE WHEN jl.debit::numeric > 0 THEN jl.debit::numeric ELSE 0 END) as debit_total,
|
||
SUM(CASE WHEN jl.credit::numeric > 0 THEN jl.credit::numeric ELSE 0 END) as credit_total
|
||
FROM journal_lines jl
|
||
JOIN journal_entries je ON jl.journal_entry_id = je.journal_entry_id
|
||
LEFT JOIN journal_entries child ON child.parent_entry_id = je.journal_entry_id
|
||
WHERE jl.account_id = 41 -- 売上高
|
||
AND EXTRACT(YEAR FROM je.entry_date) = 2025
|
||
AND EXTRACT(MONTH FROM je.entry_date) = 6
|
||
AND je.is_deleted = false
|
||
AND child.journal_entry_id IS NULL
|
||
''')
|
||
|
||
result = cur.fetchone()
|
||
if result:
|
||
debit = result['debit_total'] if result['debit_total'] else 0
|
||
credit = result['credit_total'] if result['credit_total'] else 0
|
||
print(f"\n試算表: 借 {debit:>12,.0f} | 貳 {credit:>12,.0f}")
|
||
|
||
conn.close()
|