110 lines
3.9 KiB
Python
110 lines
3.9 KiB
Python
import os
|
||
os.environ['DB_HOST'] = '192.168.0.61'
|
||
os.environ['DB_PORT'] = '55432'
|
||
os.environ['DB_NAME'] = 'njts_acct'
|
||
os.environ['DB_USER'] = 'njts_app'
|
||
os.environ['DB_PASSWORD'] = 'njts_app2025'
|
||
|
||
import sys
|
||
sys.path.insert(0, os.path.dirname(__file__))
|
||
|
||
from app.core.database import get_connection
|
||
|
||
conn = get_connection()
|
||
cur = conn.cursor()
|
||
|
||
# 逸速ビル関連の仕訳を確認
|
||
cur.execute("""
|
||
SELECT journal_entry_id, entry_date, description, parent_entry_id, is_deleted
|
||
FROM journal_entries
|
||
WHERE description LIKE '%遠達ビル%'
|
||
ORDER BY journal_entry_id
|
||
""")
|
||
|
||
rows = cur.fetchall()
|
||
print("\n遠達ビル関連の仕訳:")
|
||
for r in rows:
|
||
print(f"ID: {r['journal_entry_id']}, Date: {r['entry_date']}, Desc: {r['description']}, Parent: {r['parent_entry_id']}, is_deleted: {r['is_deleted']}")
|
||
|
||
# parent_entry_idが設定されている仕訳IDの一覧を取得
|
||
cur.execute("""
|
||
SELECT DISTINCT parent_entry_id
|
||
FROM journal_entries
|
||
WHERE parent_entry_id IS NOT NULL
|
||
ORDER BY parent_entry_id
|
||
""")
|
||
|
||
parent_ids = [r['parent_entry_id'] for r in cur.fetchall()]
|
||
print(f"\nparent_entry_idとして参照されているID: {parent_ids}")
|
||
|
||
cur.close()
|
||
conn.close()
|
||
|
||
# 実際の検索クエリと同じロジックでテスト
|
||
print("\n--- 検索クエリのテスト（include_history=False） ---")
|
||
conn2 = get_connection()
|
||
cur2 = conn2.cursor()
|
||
|
||
# まず、条件なしでID 80が取得できるか確認
|
||
cur2.execute("""
|
||
SELECT journal_entry_id, description
|
||
FROM journal_entries
|
||
WHERE journal_entry_id = 80
|
||
""")
|
||
print(f"ID 80の存在確認: {cur2.fetchall()}")
|
||
|
||
# 各条件を個別にチェック
|
||
cur2.execute("SELECT journal_entry_id FROM journal_entries WHERE journal_entry_id = 80 AND is_deleted = false")
|
||
print(f"is_deleted=false: {len(cur2.fetchall())}件")
|
||
|
||
cur2.execute("SELECT journal_entry_id FROM journal_entries WHERE journal_entry_id = 80 AND description NOT LIKE '%[逆仕訳]%'")
|
||
print(f"NOT LIKE '%%[逆仕訳]%%': {len(cur2.fetchall())}件")
|
||
|
||
cur2.execute("""
|
||
SELECT journal_entry_id FROM journal_entries
|
||
WHERE journal_entry_id = 80
|
||
AND journal_entry_id NOT IN (SELECT parent_entry_id FROM journal_entries WHERE parent_entry_id IS NOT NULL)
|
||
""")
|
||
print(f"NOT IN parent_entry_id: {len(cur2.fetchall())}件")
|
||
|
||
cur2.execute("SELECT journal_entry_id FROM journal_entries WHERE journal_entry_id = 80 AND description LIKE '%逸速ビル%'")
|
||
print(f"LIKE '%%逸速ビル%%': {len(cur2.fetchall())}件")
|
||
|
||
cur2.execute("SELECT journal_entry_id FROM journal_entries WHERE journal_entry_id = 80 AND description LIKE '%遠達ビル%'")
|
||
print(f"LIKE '%%遠達ビル%%': {len(cur2.fetchall())}件")
|
||
|
||
sql = """
|
||
SELECT
|
||
je.journal_entry_id,
|
||
je.entry_date,
|
||
je.description,
|
||
je.fiscal_year,
|
||
je.version,
|
||
COALESCE(SUM(jl.debit), 0) as debit_total,
|
||
COALESCE(SUM(jl.credit), 0) as credit_total,
|
||
STRING_AGG(DISTINCT CASE WHEN jl.tax_rate IS NOT NULL THEN jl.tax_rate::text END, ', ' ORDER BY CASE WHEN jl.tax_rate IS NOT NULL THEN jl.tax_rate::text END) as tax_rates
|
||
FROM journal_entries je
|
||
LEFT JOIN journal_lines jl ON je.journal_entry_id = jl.journal_entry_id
|
||
WHERE je.is_deleted = false
|
||
AND je.description NOT LIKE %s
|
||
AND je.journal_entry_id NOT IN (
|
||
SELECT parent_entry_id
|
||
FROM journal_entries
|
||
WHERE parent_entry_id IS NOT NULL
|
||
)
|
||
AND je.entry_date >= %s AND je.entry_date <= %s
|
||
AND je.description LIKE %s
|
||
GROUP BY je.journal_entry_id, je.entry_date, je.description, je.fiscal_year, je.version
|
||
ORDER BY je.entry_date DESC, je.journal_entry_id DESC, je.version DESC
|
||
"""
|
||
|
||
cur2.execute(sql, ['%[逆仕訳]%', '2025-06-01', '2025-06-30', '%逸速ビル%'])
|
||
results = cur2.fetchall()
|
||
|
||
print(f"\n検索結果（{len(results)}件）:")
|
||
for r in results:
|
||
print(f"ID: {r['journal_entry_id']}, Date: {r['entry_date']}, Desc: {r['description']}, Tax: {r['tax_rates']}")
|
||
|
||
cur2.close()
|
||
conn2.close()
|