83 lines
2.5 KiB
Python
83 lines
2.5 KiB
Python
import psycopg2
|
||
from decimal import Decimal
|
||
|
||
conn = psycopg2.connect(host='192.168.0.61', port=55432, dbname='njts_acct', user='njts_app', password='njts_app2025')
|
||
cur = conn.cursor()
|
||
|
||
# 普通預金のaccount_idを取得
|
||
cur.execute("SELECT account_id, account_code, account_name FROM accounts WHERE account_code = '102'")
|
||
acc = cur.fetchone()
|
||
print(f"科目: {acc}")
|
||
account_id = acc[0]
|
||
|
||
# 2026年1月の仕訳明細を全件取得
|
||
cur.execute("""
|
||
SELECT
|
||
jl.line_id,
|
||
je.journal_entry_id,
|
||
je.entry_date,
|
||
je.description,
|
||
jl.debit,
|
||
jl.credit,
|
||
je.is_latest,
|
||
je.is_deleted
|
||
FROM journal_lines jl
|
||
JOIN journal_entries je ON jl.journal_entry_id = je.journal_entry_id
|
||
WHERE jl.account_id = %s
|
||
AND je.entry_date >= '2026-01-01'
|
||
AND je.entry_date <= '2026-01-31'
|
||
AND je.is_deleted = false
|
||
ORDER BY je.entry_date, je.journal_entry_id
|
||
""", (account_id,))
|
||
|
||
rows = cur.fetchall()
|
||
print(f"\n全件（is_latest無視）: {len(rows)}件")
|
||
print("-" * 90)
|
||
debit_all = Decimal(0)
|
||
credit_all = Decimal(0)
|
||
for r in rows:
|
||
jline_id, entry_id, date, desc, debit, credit, is_latest, is_deleted = r
|
||
d = Decimal(str(debit or 0))
|
||
c = Decimal(str(credit or 0))
|
||
debit_all += d
|
||
credit_all += c
|
||
print(f"entry_id={entry_id} date={date} is_latest={is_latest} debit={d:>12} credit={c:>12} | {desc[:30]}")
|
||
|
||
print(f"\n合計 (全件): debit={debit_all:>12} credit={credit_all:>12}")
|
||
|
||
# is_latest=trueのみ
|
||
cur.execute("""
|
||
SELECT
|
||
jl.journal_line_id,
|
||
je.journal_entry_id,
|
||
je.entry_date,
|
||
je.description,
|
||
jl.debit,
|
||
jl.credit
|
||
FROM journal_lines jl
|
||
JOIN journal_entries je ON jl.journal_entry_id = je.journal_entry_id
|
||
WHERE jl.account_id = %s
|
||
AND je.entry_date >= '2026-01-01'
|
||
AND je.entry_date <= '2026-01-31'
|
||
AND je.is_deleted = false
|
||
AND je.is_latest = true
|
||
ORDER BY je.entry_date, je.journal_entry_id
|
||
""", (account_id,))
|
||
|
||
rows2 = cur.fetchall()
|
||
print(f"\nis_latest=true のみ: {len(rows2)}件")
|
||
print("-" * 90)
|
||
debit_latest = Decimal(0)
|
||
credit_latest = Decimal(0)
|
||
for r in rows2:
|
||
line_id, entry_id, date, desc, debit, credit = r
|
||
d = Decimal(str(debit or 0))
|
||
c = Decimal(str(credit or 0))
|
||
debit_latest += d
|
||
credit_latest += c
|
||
print(f"entry_id={entry_id} date={date} debit={d:>12} credit={c:>12} | {desc[:40]}")
|
||
|
||
print(f"\n合計 (is_latest=true): debit={debit_latest:>12} credit={credit_latest:>12}")
|
||
|
||
conn.close()
|