133 lines
4.9 KiB
Python
133 lines
4.9 KiB
Python
#!/usr/bin/env python
|
||
# -*- coding: utf-8 -*-
|
||
"""
|
||
診断スクリプト：2025年6月の701の1,400,000円を特定
|
||
"""
|
||
|
||
import os
|
||
import sys
|
||
from decimal import Decimal
|
||
|
||
sys.path.insert(0, os.path.join(os.path.dirname(__file__), 'backend'))
|
||
|
||
os.environ['DB_HOST'] = 'localhost'
|
||
os.environ['DB_NAME'] = 'accounting_db'
|
||
os.environ['DB_USER'] = 'postgres'
|
||
os.environ['DB_PASSWORD'] = 'root'
|
||
os.environ['DB_PORT'] = '5432'
|
||
|
||
from app.core.database import get_connection
|
||
|
||
with get_connection() as conn:
|
||
with conn.cursor() as cur:
|
||
print("=" * 120)
|
||
print("診断：2025年6月の701（売上高）の確認")
|
||
print("=" * 120)
|
||
|
||
# トータルを確認
|
||
sql_total = """
|
||
SELECT COALESCE(SUM(l.credit), 0) as total_credit
|
||
FROM journal_lines l
|
||
JOIN journal_entries e ON e.journal_entry_id = l.journal_entry_id
|
||
WHERE l.account_id = 701
|
||
AND e.entry_date >= '2025-06-01'
|
||
AND e.entry_date <= '2025-06-30'
|
||
"""
|
||
|
||
cur.execute(sql_total)
|
||
result = cur.fetchone()
|
||
print(f"\n全トランザクション合計クレジット: {result['total_credit']:,.0f}")
|
||
print(f"期待値: 4,082,728")
|
||
print(f"差異: {result['total_credit'] - 4082728:,.0f}")
|
||
|
||
# 詳細を表示
|
||
print("\n【 全トランザクション詳細 】")
|
||
sql_detail = """
|
||
SELECT
|
||
e.journal_entry_id,
|
||
e.entry_date,
|
||
SUBSTRING(e.description, 1, 35) as desc,
|
||
l.credit,
|
||
CASE WHEN e.description LIKE '[%' THEN 'YES(逆?)' ELSE 'NO' END as has_bracket,
|
||
CASE WHEN e.parent_entry_id IS NOT NULL THEN e.parent_entry_id ELSE '-' END as parent_id,
|
||
CASE WHEN e.is_deleted THEN 'YES' ELSE 'NO' END as deleted
|
||
FROM journal_lines l
|
||
JOIN journal_entries e ON e.journal_entry_id = l.journal_entry_id
|
||
WHERE l.account_id = 701
|
||
AND e.entry_date >= '2025-06-01'
|
||
AND e.entry_date <= '2025-06-30'
|
||
AND l.credit > 0
|
||
ORDER BY l.credit DESC, e.entry_date, e.journal_entry_id
|
||
"""
|
||
|
||
cur.execute(sql_detail)
|
||
rows = cur.fetchall()
|
||
|
||
total = Decimal(0)
|
||
for row in rows:
|
||
print(f"ID:{row['journal_entry_id']:5d} | {row['entry_date']} | {row['desc']:35s} | " +
|
||
f"credit:{row['credit']:>12,.0f} | bracket:{row['has_bracket']:6s} | parent:{row['parent_id']:5s} | del:{row['deleted']}")
|
||
total += row['credit']
|
||
|
||
print(f"\n合計: {total:,.0f}")
|
||
|
||
# 1,400,000の特定
|
||
print("\n【 1,400,000円の交易を探す 】")
|
||
sql_1400 = """
|
||
SELECT
|
||
e.journal_entry_id,
|
||
e.entry_date,
|
||
e.description,
|
||
COALESCE(SUM(l.credit), 0) as credit,
|
||
e.parent_entry_id,
|
||
e.is_deleted
|
||
FROM journal_lines l
|
||
JOIN journal_entries e ON e.journal_entry_id = l.journal_entry_id
|
||
WHERE l.account_id = 701
|
||
AND e.entry_date >= '2025-06-01'
|
||
AND e.entry_date <= '2025-06-30'
|
||
GROUP BY e.journal_entry_id, e.entry_date, e.description, e.parent_entry_id, e.is_deleted
|
||
ORDER BY COALESCE(SUM(l.credit), 0) DESC
|
||
"""
|
||
|
||
cur.execute(sql_1400)
|
||
rows = cur.fetchall()
|
||
|
||
for row in rows:
|
||
if abs(row['credit'] - 1400000) < 1:
|
||
print(f"★★★ found! ★★★")
|
||
print(f" ID: {row['journal_entry_id']}")
|
||
print(f" 日付: {row['entry_date']}")
|
||
print(f" 説明: {row['description']}")
|
||
print(f" クレジット: {row['credit']:,.0f}")
|
||
print(f" 親ID: {row['parent_entry_id']}")
|
||
print(f" 削除: {row['is_deleted']}")
|
||
break
|
||
else:
|
||
print("1,400,000の交易見つからず。次点：")
|
||
for row in rows[:5]:
|
||
print(f" ID:{row['journal_entry_id']} | {row['credit']:>12,.0f} | desc:{row['description'][:30]}")
|
||
|
||
# 現在のフィルター条件をテスト
|
||
print("\n【 フィルター条件のテスト 】")
|
||
sql_filtered = """
|
||
SELECT COALESCE(SUM(l.credit), 0) as credit
|
||
FROM journal_lines l
|
||
JOIN journal_entries j ON j.journal_entry_id = l.journal_entry_id
|
||
LEFT JOIN journal_entries child ON child.parent_entry_id = j.journal_entry_id
|
||
WHERE l.account_id = 701
|
||
AND j.entry_date >= '2025-06-01'
|
||
AND j.entry_date <= '2025-06-30'
|
||
AND j.is_deleted = false
|
||
AND NOT (j.description LIKE '[%')
|
||
AND child.journal_entry_id IS NULL
|
||
"""
|
||
|
||
cur.execute(sql_filtered)
|
||
result = cur.fetchone()
|
||
print(f"フィルター後のクレジット: {result['credit']:,.0f}")
|
||
print(f"期待値: 4,082,728")
|
||
print(f"差異: {result['credit'] - 4082728:,.0f}")
|
||
|
||
print("\n処理完了")
|