186 lines
5.7 KiB
Python
186 lines
5.7 KiB
Python
"""
|
||
12月2025年の仕訳データを詳しく確認し、売上高の差異原因を特定する
|
||
"""
|
||
import psycopg2
|
||
|
||
conn = psycopg2.connect(
|
||
host='192.168.0.61',
|
||
port=55432,
|
||
dbname='njts_acct',
|
||
user='njts_app',
|
||
password='njts_app2025'
|
||
)
|
||
cur = conn.cursor()
|
||
|
||
print("=" * 80)
|
||
print("【分析1】月別売上高の集計 (2025年6月〜12月)")
|
||
print("=" * 80)
|
||
|
||
cur.execute("""
|
||
SELECT
|
||
DATE_TRUNC('month', je.entry_date)::date AS month,
|
||
COALESCE(SUM(jl.credit), 0) - COALESCE(SUM(jl.debit), 0) AS revenue
|
||
FROM journal_entries je
|
||
JOIN journal_lines jl ON je.journal_entry_id = jl.journal_entry_id
|
||
JOIN accounts a ON jl.account_id = a.account_id
|
||
WHERE je.is_latest = true
|
||
AND je.is_deleted = false
|
||
AND je.entry_date >= '2025-06-01'
|
||
AND je.entry_date <= '2025-12-31'
|
||
AND a.account_code = '701'
|
||
GROUP BY DATE_TRUNC('month', je.entry_date)
|
||
ORDER BY month
|
||
""")
|
||
rows = cur.fetchall()
|
||
total_sys = 0
|
||
for row in rows:
|
||
print(f" {row[0].strftime('%Y年%m月')}: {row[1]:>12,.0f} 円")
|
||
total_sys += row[1]
|
||
print(f" {'合計':>10}: {total_sys:>12,.0f} 円")
|
||
print(f"\n 税理士の売上高合計: 28,896,368 円")
|
||
print(f" 差額: {total_sys - 28896368:>12,.0f} 円")
|
||
|
||
print()
|
||
print("=" * 80)
|
||
print("【分析2】2025年12月の全仕訳明細")
|
||
print("=" * 80)
|
||
|
||
cur.execute("""
|
||
SELECT
|
||
je.entry_date,
|
||
je.journal_entry_id,
|
||
je.description,
|
||
a.account_code,
|
||
a.account_name,
|
||
jl.debit,
|
||
jl.credit
|
||
FROM journal_entries je
|
||
JOIN journal_lines jl ON je.journal_entry_id = jl.journal_entry_id
|
||
JOIN accounts a ON jl.account_id = a.account_id
|
||
WHERE je.is_latest = true
|
||
AND je.is_deleted = false
|
||
AND je.entry_date >= '2025-12-01'
|
||
AND je.entry_date <= '2025-12-31'
|
||
ORDER BY je.entry_date, je.journal_entry_id, jl.journal_line_id
|
||
""")
|
||
rows = cur.fetchall()
|
||
|
||
if rows:
|
||
print(f"{'日付':<12} {'ID':<6} {'摘要':<30} {'科目CD':<8} {'科目名':<15} {'借方':>12} {'貸方':>12}")
|
||
print("-" * 100)
|
||
for row in rows:
|
||
print(f"{str(row[0]):<12} {row[1]:<6} {str(row[2])[:28]:<30} {str(row[3]):<8} {str(row[4]):<15} {row[5] if row[5] else 0:>12,.0f} {row[6] if row[6] else 0:>12,.0f}")
|
||
else:
|
||
print("2025年12月の仕訳データが見つかりません！")
|
||
|
||
print()
|
||
print("=" * 80)
|
||
print("【分析3】売上高差異の内訳 (3,432,724円の差異原因)")
|
||
print("=" * 80)
|
||
|
||
# Monthly revenue breakdown
|
||
print("\n月別売上高（システム vs 税理士推定）:")
|
||
print(" ※税理士の月別内訳は不明なため、12月分の存在確認のみ実施")
|
||
|
||
print()
|
||
print("=" * 80)
|
||
print("【分析4】2025年12月の売上関連仕訳のみ")
|
||
print("=" * 80)
|
||
|
||
cur.execute("""
|
||
SELECT
|
||
je.entry_date,
|
||
je.journal_entry_id,
|
||
je.description,
|
||
a.account_code,
|
||
a.account_name,
|
||
jl.debit,
|
||
jl.credit
|
||
FROM journal_entries je
|
||
JOIN journal_lines jl ON je.journal_entry_id = jl.journal_entry_id
|
||
JOIN accounts a ON jl.account_id = a.account_id
|
||
WHERE je.is_latest = true
|
||
AND je.is_deleted = false
|
||
AND je.entry_date >= '2025-12-01'
|
||
AND je.entry_date <= '2025-12-31'
|
||
AND a.account_code IN ('701', '702', '201') -- 売上高、受取利息、売掛金
|
||
ORDER BY je.entry_date, je.journal_entry_id
|
||
""")
|
||
rows = cur.fetchall()
|
||
dec_revenue = 0
|
||
if rows:
|
||
print(f"{'日付':<12} {'ID':<6} {'摘要':<30} {'科目名':<15} {'借方':>12} {'貸方':>12}")
|
||
print("-" * 90)
|
||
for row in rows:
|
||
credit = row[6] if row[6] else 0
|
||
debit = row[5] if row[5] else 0
|
||
if row[4] == '売上高':
|
||
dec_revenue += credit - debit
|
||
print(f"{str(row[0]):<12} {row[1]:<6} {str(row[2])[:28]:<30} {str(row[4]):<15} {debit:>12,.0f} {credit:>12,.0f}")
|
||
print(f"\n 2025年12月の売上高: {dec_revenue:>12,.0f} 円")
|
||
else:
|
||
print("2025年12月の売上関連仕訳なし")
|
||
|
||
print()
|
||
print("=" * 80)
|
||
print("【分析5】費用項目の差異詳細")
|
||
print("=" * 80)
|
||
|
||
system_data = {
|
||
"役員報酬": 2520000,
|
||
"給料手当": 7520000,
|
||
"法定福利費": 1439958,
|
||
"福利厚生費": 243760,
|
||
"外注費": 7490000,
|
||
"交際費": 806883,
|
||
"会議費": 440642,
|
||
"旅費交通費": 131763,
|
||
"通信費": 32063,
|
||
"消耗品費": 1061190,
|
||
"地代家賃": 1128184,
|
||
"支払手数料": 19470,
|
||
"支払報酬料": 165000,
|
||
"保険料": 33950,
|
||
"租税公課": -161590,
|
||
}
|
||
|
||
accountant_data = {
|
||
"役員報酬": 2520000,
|
||
"給料手当": 2700000,
|
||
"賞与": 4300000,
|
||
"法定福利費": 1588604,
|
||
"福利厚生費": 242978,
|
||
"外注費": 7490000,
|
||
"交際費": 915007,
|
||
"会議費": 219070,
|
||
"旅費交通費": 522149,
|
||
"通信費": 31459,
|
||
"消耗品費": 1027007,
|
||
"修繕費": 986,
|
||
"新聞図書費": 9900,
|
||
"支払手数料": 168800,
|
||
"地代家賃": 1081822,
|
||
"保険料": 33950,
|
||
"租税公課": 33100,
|
||
"法人税住民税事業税": 747,
|
||
}
|
||
|
||
all_keys = sorted(set(list(system_data.keys()) + list(accountant_data.keys())))
|
||
print(f"{'費用科目':<15} {'税理士':>12} {'システム':>12} {'差異(+は自社多)':>15} 備考")
|
||
print("-" * 65)
|
||
total_diff = 0
|
||
for k in all_keys:
|
||
acc = accountant_data.get(k, 0)
|
||
sys = system_data.get(k, 0)
|
||
diff = sys - acc
|
||
total_diff += diff
|
||
note = ""
|
||
if diff != 0:
|
||
note = "← 要確認" if abs(diff) > 100000 else ""
|
||
print(f"{k:<15} {acc:>12,.0f} {sys:>12,.0f} {diff:>+15,.0f} {note}")
|
||
|
||
print(f"{'合計':<15} {sum(accountant_data.values()):>12,.0f} {sum(system_data.values()):>12,.0f} {total_diff:>+15,.0f}")
|
||
print(f"\n※費用差異: システムの費用が {total_diff:+,.0f} 円 (負=税理士の費用が多い)")
|
||
|
||
conn.close()
|