99 lines
4.2 KiB
Python
99 lines
4.2 KiB
Python
#!/usr/bin/env python
|
||
# -*- coding: utf-8 -*-
|
||
|
||
import os
|
||
import sys
|
||
sys.path.insert(0, 'backend')
|
||
|
||
os.environ['DB_HOST'] = '192.168.0.61'
|
||
os.environ['DB_NAME'] = 'njts_acct'
|
||
os.environ['DB_USER'] = 'njts_app'
|
||
os.environ['DB_PASSWORD'] = 'njts_app2025'
|
||
os.environ['DB_PORT'] = '55432'
|
||
|
||
from app.core.database import get_connection
|
||
|
||
with get_connection() as conn:
|
||
with conn.cursor() as cur:
|
||
# 获取account_id
|
||
cur.execute("SELECT account_id FROM accounts WHERE account_code = '701'")
|
||
aid = cur.fetchone()['account_id']
|
||
|
||
print('\n【 2025年6月1日 - 701科目的所有交易 】\n')
|
||
cur.execute("""
|
||
SELECT
|
||
j.journal_entry_id,
|
||
j.entry_date,
|
||
j.description,
|
||
l.debit,
|
||
l.credit,
|
||
j.parent_entry_id,
|
||
(SELECT COUNT(*) FROM journal_entries child WHERE child.parent_entry_id = j.journal_entry_id) as has_child
|
||
FROM journal_lines l
|
||
JOIN journal_entries j ON j.journal_entry_id = l.journal_entry_id
|
||
WHERE l.account_id = %s
|
||
AND j.entry_date = '2025-06-01'
|
||
ORDER BY j.journal_entry_id
|
||
""", (aid,))
|
||
|
||
entries = cur.fetchall()
|
||
print("Entry# | Debit | Credit | Parent | HasChild | Description")
|
||
print("-" * 100)
|
||
for row in entries:
|
||
parent_str = f"#{row['parent_entry_id']}" if row['parent_entry_id'] else "None"
|
||
print(f"#{row['journal_entry_id']:<5} | {row['debit'] or 0:>10} | {row['credit'] or 0:>10} | {parent_str:<6} | {row['has_child']:<8} | {row['description'][:60]}")
|
||
|
||
print('\n【 分析对冲关系 】\n')
|
||
|
||
# 查找【未計上のため取消】的交易
|
||
cur.execute("""
|
||
SELECT
|
||
j.journal_entry_id,
|
||
j.description,
|
||
l.debit,
|
||
l.credit,
|
||
j.parent_entry_id
|
||
FROM journal_lines l
|
||
JOIN journal_entries j ON j.journal_entry_id = l.journal_entry_id
|
||
WHERE l.account_id = %s
|
||
AND j.entry_date = '2025-06-01'
|
||
AND j.description LIKE '%未計上のため取消%'
|
||
ORDER BY j.journal_entry_id
|
||
""", (aid,))
|
||
|
||
cancel_entries = cur.fetchall()
|
||
print(f"找到【未計上のため取消】的交易: {len(cancel_entries)}笔")
|
||
for row in cancel_entries:
|
||
parent_str = f"Parent=#{row['parent_entry_id']}" if row['parent_entry_id'] else "Parent=None"
|
||
print(f"\nEntry#{row['journal_entry_id']}")
|
||
print(f" {parent_str}")
|
||
print(f" 借: {row['debit'] or 0}")
|
||
print(f" 贷: {row['credit'] or 0}")
|
||
|
||
if row['parent_entry_id']:
|
||
print(f" ↑ 这是 Entry#{row['parent_entry_id']} 的修正交易")
|
||
# 查找原始交易
|
||
cur.execute("""
|
||
SELECT debit, credit FROM journal_lines
|
||
WHERE journal_entry_id = %s AND account_id = %s
|
||
""", (row['parent_entry_id'], aid))
|
||
parent_line = cur.fetchone()
|
||
if parent_line:
|
||
print(f" 原始交易 Entry#{row['parent_entry_id']}: 借={parent_line['debit'] or 0}, 贷={parent_line['credit'] or 0}")
|
||
|
||
print('\n【 用户说应该对冲 1,400,000 】\n')
|
||
print('根据之前的分析:')
|
||
print(' - Entry#357: 贳方 1,400,000 (原始交易 - 现在被过滤了)')
|
||
print(' - Entry#360: 借方 1,400,000 (取消交易 - 现在被计入了)')
|
||
print('')
|
||
print('问题确认：')
|
||
print(' 这两笔确实应该相互抵消（对冲），不应该同时出现在试算表中')
|
||
print('')
|
||
print('当前的过滤逻辑:')
|
||
print(' - 过滤掉了有子交易的父交易 (Entry#357被过滤因为Entry#360是它的子)')
|
||
print(' - 但没有过滤掉作为"取消"的子交易本身 (Entry#360被计入)')
|
||
print('')
|
||
print('需要验证的事项:')
|
||
print(' 1. Entry#360是否还需要有后续的修正交易？')
|
||
print(' 2. 还是应该改变过滤逻辑来排除Entry#360？')
|