76 lines
2.5 KiB
Python
76 lines
2.5 KiB
Python
import psycopg2
|
||
|
||
conn = psycopg2.connect(
|
||
host="192.168.0.61",
|
||
port=55432,
|
||
database="njts_acct",
|
||
user="njts_app",
|
||
password="njts_app2025"
|
||
)
|
||
|
||
cur = conn.cursor()
|
||
|
||
# 查找所有"遠達ビル 事務所費用"相关的仕訳
|
||
print("=" * 100)
|
||
print("全期間の遠達ビル 事務所費用 相關仕訳")
|
||
print("=" * 100)
|
||
|
||
cur.execute("""
|
||
SELECT
|
||
je.journal_entry_id,
|
||
je.entry_date,
|
||
je.description,
|
||
je.revision_count,
|
||
je.is_latest,
|
||
je.is_deleted,
|
||
je.original_entry_id,
|
||
COALESCE(SUM(jl.debit), 0) as debit_total,
|
||
COALESCE(SUM(jl.credit), 0) as credit_total
|
||
FROM journal_entries je
|
||
LEFT JOIN journal_lines jl ON je.journal_entry_id = jl.journal_entry_id
|
||
WHERE je.description LIKE '%遠達ビル 事務所費用%'
|
||
GROUP BY je.journal_entry_id, je.entry_date, je.description, je.revision_count, je.is_latest, je.is_deleted, je.original_entry_id
|
||
ORDER BY je.journal_entry_id ASC
|
||
""")
|
||
|
||
results = cur.fetchall()
|
||
|
||
if results:
|
||
for entry_id, entry_date, description, rev_count, is_latest, is_deleted, original_id, debit, credit in results:
|
||
deleted_mark = "【削除】" if is_deleted else " "
|
||
latest_mark = "✓" if is_latest else "✗"
|
||
print(f"ID={entry_id:3d} {entry_date} {deleted_mark} [{latest_mark}v{rev_count}] original_id={original_id} | 借={debit:>10,.0f} 貸={credit:>10,.0f} | {description}")
|
||
|
||
# 特に、最新版（is_latest=true && is_deleted=false）を検出
|
||
print("\n" + "=" * 100)
|
||
print("試算表に計上されるべき仕訳 (is_latest=true AND is_deleted=false)")
|
||
print("=" * 100)
|
||
|
||
cur.execute("""
|
||
SELECT
|
||
je.journal_entry_id,
|
||
je.entry_date,
|
||
je.description,
|
||
je.revision_count,
|
||
COALESCE(SUM(jl.debit), 0) as debit_total,
|
||
COALESCE(SUM(jl.credit), 0) as credit_total
|
||
FROM journal_entries je
|
||
LEFT JOIN journal_lines jl ON je.journal_entry_id = jl.journal_entry_id
|
||
WHERE je.description LIKE '%遠達ビル 事務所費用%'
|
||
AND je.is_latest = true
|
||
AND je.is_deleted = false
|
||
GROUP BY je.journal_entry_id, je.entry_date, je.description, je.revision_count
|
||
ORDER BY je.journal_entry_id ASC
|
||
""")
|
||
|
||
results2 = cur.fetchall()
|
||
|
||
if results2:
|
||
for entry_id, entry_date, description, rev_count, debit, credit in results2:
|
||
print(f"ID={entry_id} {entry_date} v{rev_count} | 借={debit:>10,.0f} 貸={credit:>10,.0f} | {description}")
|
||
else:
|
||
print("該当データなし（正常）")
|
||
|
||
cur.close()
|
||
conn.close()
|