151 lines
5.3 KiB
Python
151 lines
5.3 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()
|
||
|
||
print("=" * 140)
|
||
print("【策略调整：找出6月、7月、8月都出现的完全相同仕訳】")
|
||
print("=" * 140)
|
||
|
||
# 找出在多个月份中都出现相同描述和金额的仕訳
|
||
cur.execute("""
|
||
WITH monthly_entries AS (
|
||
SELECT
|
||
je.description,
|
||
COALESCE(SUM(jl.debit), 0)::BIGINT as total_debit,
|
||
COALESCE(SUM(jl.credit), 0)::BIGINT as total_credit,
|
||
EXTRACT(MONTH FROM je.entry_date)::INT as month,
|
||
COUNT(*) as month_count
|
||
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.is_latest = true
|
||
AND EXTRACT(YEAR FROM je.entry_date) = 2025
|
||
AND EXTRACT(MONTH FROM je.entry_date) IN (6, 7, 8)
|
||
GROUP BY je.journal_entry_id, je.description, EXTRACT(MONTH FROM je.entry_date)
|
||
),
|
||
multi_month_patterns AS (
|
||
SELECT
|
||
description,
|
||
total_debit,
|
||
total_credit,
|
||
COUNT(DISTINCT month) as month_count,
|
||
STRING_AGG(month::TEXT, ',' ORDER BY month) as months
|
||
FROM monthly_entries
|
||
GROUP BY description, total_debit, total_credit
|
||
HAVING COUNT(DISTINCT month) > 1
|
||
ORDER BY month_count DESC, total_debit DESC
|
||
)
|
||
SELECT * FROM multi_month_patterns
|
||
""")
|
||
|
||
multi_month = cur.fetchall()
|
||
print(f"\n找到 {len(multi_month)} 个在多个月份出现相同仕訳的模式\n")
|
||
|
||
duplicates_for_deletion = []
|
||
|
||
for description, debit, credit, month_count, months in multi_month:
|
||
print(f"【{month_count}個月出現】{description}")
|
||
print(f" 金額: 借={debit:>12,} 貸={credit:>12,}")
|
||
print(f" 月份: {months}")
|
||
|
||
# 查找这个模式在7月和8月的所有entry_id
|
||
cur.execute("""
|
||
SELECT
|
||
je.journal_entry_id,
|
||
TO_CHAR(je.entry_date, 'YYYY-MM-DD') as entry_date,
|
||
EXTRACT(MONTH FROM je.entry_date)::INT as month
|
||
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.is_latest = true
|
||
AND EXTRACT(YEAR FROM je.entry_date) = 2025
|
||
AND EXTRACT(MONTH FROM je.entry_date) IN (7, 8)
|
||
AND je.description = %s
|
||
GROUP BY je.journal_entry_id, je.entry_date
|
||
HAVING COALESCE(SUM(jl.debit), 0) = %s
|
||
AND COALESCE(SUM(jl.credit), 0) = %s
|
||
""", (description, debit, credit))
|
||
|
||
july_august_entries = cur.fetchall()
|
||
for entry_id, entry_date, month in july_august_entries:
|
||
print(f" ├─ ID={entry_id:3d} | {entry_date}")
|
||
duplicates_for_deletion.append({
|
||
'entry_id': entry_id,
|
||
'date': entry_date,
|
||
'month': month,
|
||
'description': description,
|
||
'debit': debit,
|
||
'credit': credit
|
||
})
|
||
print()
|
||
|
||
print("\n" + "=" * 140)
|
||
print(f"【汇总】在7月/8月中找到的重复记录")
|
||
print("=" * 140)
|
||
|
||
if duplicates_for_deletion:
|
||
print(f"\n共 {len(duplicates_for_deletion)} 条可能的复制记录：\n")
|
||
|
||
for dup in duplicates_for_deletion:
|
||
print(f"ID={dup['entry_id']:3d} | {dup['date']} | 借={dup['debit']:>12,} 貸={dup['credit']:>12,}")
|
||
print(f" └─ {dup['description'][:100]}")
|
||
|
||
# 计算直接的删除影响
|
||
print("\n" + "-" * 140)
|
||
|
||
cur.execute("""
|
||
SELECT
|
||
COALESCE(SUM(COALESCE(jl.debit, 0) - COALESCE(jl.credit, 0)), 0) as balance
|
||
FROM journal_lines jl
|
||
INNER JOIN journal_entries je ON jl.journal_entry_id = je.journal_entry_id
|
||
WHERE jl.account_id = 2 -- 普通預金
|
||
AND je.is_deleted = false
|
||
AND je.is_latest = true
|
||
""")
|
||
|
||
current_balance = cur.fetchone()[0]
|
||
print(f"删除前普通預金期末残高: {current_balance:>15,.0f}円")
|
||
|
||
# 计算这些重复记录对普通預金的影响
|
||
deletion_impact = 0
|
||
for dup in duplicates_for_deletion:
|
||
cur.execute("""
|
||
SELECT COALESCE(SUM(COALESCE(jl.debit, 0) - COALESCE(jl.credit, 0)), 0)
|
||
FROM journal_lines jl
|
||
WHERE jl.journal_entry_id = %s
|
||
AND jl.account_id = 2 -- 普通預金
|
||
""", (dup['entry_id'],))
|
||
|
||
impact = cur.fetchone()[0]
|
||
deletion_impact += impact
|
||
print(f" ID={dup['entry_id']:3d}: {impact:>15,.0f}円 ({dup['description'][:30]}...)")
|
||
|
||
expected_after = current_balance - deletion_impact
|
||
|
||
print(f"\n这些记录对普通預金的总影响: {deletion_impact:>15,.0f}円")
|
||
print(f"删除后预期普通預金期末残高: {expected_after:>15,.0f}円")
|
||
print(f"\n期望目标值: 14,916,322円")
|
||
print(f"删除后vs目标值差距: {14_916_322 - expected_after:>15,.0f}円")
|
||
|
||
if expected_after == 14_916_322:
|
||
print("\n✓ 完美匹配！")
|
||
else:
|
||
print(f"\n⚠ 仍有差距，说明可能还有其他需要删除的记录")
|
||
else:
|
||
print("没有找到可能的复制记录")
|
||
|
||
# 保存删除列表
|
||
delete_ids = [dup['entry_id'] for dup in duplicates_for_deletion]
|
||
print(f"\n【要删除的IDs】{delete_ids}")
|
||
|
||
cur.close()
|
||
conn.close()
|