75 lines
2.2 KiB
Python
75 lines
2.2 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()
|
||
|
||
# 现在的余额（ID 445已删除，ID 444还在）
|
||
cur.execute("""
|
||
SELECT
|
||
SUM(CASE WHEN jl.debit > 0 THEN jl.debit ELSE 0 END) as debit_total,
|
||
SUM(CASE WHEN jl.credit > 0 THEN jl.credit 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.entry_date >= '2025-06-01'
|
||
AND je.entry_date <= '2025-08-31'
|
||
AND jl.account_id = 2
|
||
AND je.is_deleted = false
|
||
AND je.is_latest = true
|
||
""")
|
||
|
||
result = cur.fetchone()
|
||
if result:
|
||
debit, credit = result
|
||
debit = debit or 0
|
||
credit = credit or 0
|
||
balance = debit - credit
|
||
print("=" * 100)
|
||
print("【現在の状況】ID 445削除済み、ID 444はまだ存在")
|
||
print("=" * 100)
|
||
print(f"借方合計: {debit:>15,.0f}")
|
||
print(f"貸方合計: {credit:>15,.0f}")
|
||
print(f"期末残高: {balance:>15,.0f}")
|
||
print(f"期待値: {14916322:>15,.0f}")
|
||
print(f"差額: {abs(balance - 14916322):>15,.0f}")
|
||
|
||
# 现在检查删除 ID 444 后的情况
|
||
print("\n" + "=" * 100)
|
||
print("【シミュレーション】ID 444も削除した場合")
|
||
print("=" * 100)
|
||
|
||
cur.execute("""
|
||
SELECT
|
||
SUM(CASE WHEN jl.debit > 0 THEN jl.debit ELSE 0 END) as debit_total,
|
||
SUM(CASE WHEN jl.credit > 0 THEN jl.credit 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.entry_date >= '2025-06-01'
|
||
AND je.entry_date <= '2025-08-31'
|
||
AND jl.account_id = 2
|
||
AND je.is_deleted = false
|
||
AND je.is_latest = true
|
||
AND je.journal_entry_id != 444
|
||
""")
|
||
|
||
result2 = cur.fetchone()
|
||
if result2:
|
||
debit2, credit2 = result2
|
||
debit2 = debit2 or 0
|
||
credit2 = credit2 or 0
|
||
balance2 = debit2 - credit2
|
||
print(f"借方合計: {debit2:>15,.0f}")
|
||
print(f"貸方合計: {credit2:>15,.0f}")
|
||
print(f"期末残高: {balance2:>15,.0f}")
|
||
print(f"期待値: {14916322:>15,.0f}")
|
||
print(f"差額: {abs(balance2 - 14916322):>15,.0f}")
|
||
|
||
cur.close()
|
||
conn.close()
|