157 lines
6.2 KiB
Python
157 lines
6.2 KiB
Python
#!/usr/bin/env python
|
||
# -*- coding: utf-8 -*-
|
||
|
||
import os
|
||
import sys
|
||
|
||
sys.path.insert(0, os.path.join(os.path.dirname(__file__), 'backend'))
|
||
|
||
os.environ.setdefault("DB_HOST", "localhost")
|
||
os.environ.setdefault("DB_PORT", "5432")
|
||
os.environ.setdefault("DB_NAME", "accounting_db")
|
||
os.environ.setdefault("DB_USER", "postgres")
|
||
os.environ.setdefault("DB_PASSWORD", "root")
|
||
|
||
from app.core.database import get_connection
|
||
from decimal import Decimal
|
||
|
||
def check_account_701():
|
||
"""シンプルに701の6月クレジット合計を確認"""
|
||
|
||
with get_connection() as conn:
|
||
with conn.cursor() as cur:
|
||
print("=" * 80)
|
||
print("702年6月 账户701 シンプルなクエリ")
|
||
print("=" * 80)
|
||
|
||
# 単純に701の信用合計
|
||
sql = """
|
||
SELECT
|
||
COALESCE(SUM(l.credit), 0) as total_credit,
|
||
COUNT(*) as line_count
|
||
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)
|
||
result = cur.fetchone()
|
||
|
||
print(f"\n【 シンプルなSELECT 】")
|
||
print(f"701の6月クレジット合計: {result['total_credit']:,}")
|
||
print(f"対象行数: {result['line_count']}")
|
||
|
||
# 詳細を見る
|
||
sql_detail = """
|
||
SELECT
|
||
e.journal_entry_id,
|
||
e.entry_date,
|
||
e.description,
|
||
l.credit,
|
||
e.is_deleted,
|
||
e.parent_entry_id
|
||
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 e.entry_date, e.journal_entry_id
|
||
"""
|
||
|
||
cur.execute(sql_detail)
|
||
details = cur.fetchall()
|
||
|
||
print(f"\n【 詳細データ（クレジット > 0） 】")
|
||
total = Decimal(0)
|
||
for row in details:
|
||
print(f"ID:{row['journal_entry_id']:5d} | {row['entry_date']} | {row['description']:40s} | " +
|
||
f"クレジット:{row['credit']:>12,} | 削除:{row['is_deleted']} | Parent:{row['parent_entry_id']}")
|
||
total += row['credit']
|
||
|
||
print(f"\n集計: {total:,}")
|
||
|
||
# 試算表サービスのフィルター条件と比較
|
||
print(f"\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 j.description NOT LIKE '%[逆仕訳]%'
|
||
AND child.journal_entry_id IS NULL
|
||
"""
|
||
|
||
cur.execute(sql_filtered)
|
||
result_filtered = cur.fetchone()
|
||
|
||
print(f"フィルター後のクレジット: {result_filtered['credit']:,}")
|
||
print(f"差異: {result['total_credit'] - result_filtered['credit']:,}")
|
||
|
||
# どの条件で除外されているか分析
|
||
print(f"\n【 除外原因の分析 】")
|
||
|
||
# deleted = true の件数
|
||
cur.execute("""
|
||
SELECT COUNT(*) as cnt
|
||
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 e.is_deleted = true
|
||
""")
|
||
deleted_count = cur.fetchone()['cnt']
|
||
print(f"is_deleted = true: {deleted_count}件")
|
||
|
||
# 逆仕訳の件数
|
||
cur.execute("""
|
||
SELECT COUNT(*) as cnt, COALESCE(SUM(l.credit), 0) as 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'
|
||
AND e.description LIKE '%[逆仕訳]%'
|
||
""")
|
||
result = cur.fetchone()
|
||
print(f"[逆仕訳]を含む: {result['cnt']}件, クレジット合計: {result['credit']:,}")
|
||
|
||
# parent_entry_idがある件数
|
||
cur.execute("""
|
||
SELECT COUNT(*) as cnt, COALESCE(SUM(l.credit), 0) as 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'
|
||
AND e.parent_entry_id IS NOT NULL
|
||
""")
|
||
result = cur.fetchone()
|
||
print(f"parent_entry_id != NULL: {result['cnt']}件, クレジット合計: {result['credit']:,}")
|
||
|
||
# 親として参照されている件数
|
||
cur.execute("""
|
||
SELECT COUNT(*) as cnt, COALESCE(SUM(l.credit), 0) as 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'
|
||
AND e.journal_entry_id IN (
|
||
SELECT parent_entry_id FROM journal_entries WHERE parent_entry_id IS NOT NULL
|
||
)
|
||
""")
|
||
result = cur.fetchone()
|
||
print(f"親として参照されている: {result['cnt']}件, クレジット合計: {result['credit']:,}")
|
||
|
||
if __name__ == "__main__":
|
||
check_account_701()
|