66 lines
2.5 KiB
Python
66 lines
2.5 KiB
Python
import psycopg2
|
||
conn = psycopg2.connect(host='192.168.0.61', port=55432, dbname='njts_acct', user='njts_app', password='njts_app2025')
|
||
cur = conn.cursor()
|
||
|
||
cur.execute("""
|
||
SELECT a.account_code, a.account_name,
|
||
COALESCE(SUM(jl.debit),0) AS debit,
|
||
COALESCE(SUM(jl.credit),0) AS credit,
|
||
COALESCE(SUM(jl.debit),0) - COALESCE(SUM(jl.credit),0) AS balance
|
||
FROM accounts a
|
||
JOIN journal_lines jl ON a.account_id = jl.account_id
|
||
JOIN journal_entries je ON jl.journal_entry_id = je.journal_entry_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_type = 'expense'
|
||
GROUP BY a.account_id, a.account_code, a.account_name
|
||
HAVING COALESCE(SUM(jl.debit),0) + COALESCE(SUM(jl.credit),0) > 0
|
||
ORDER BY a.account_code
|
||
""")
|
||
rows = cur.fetchall()
|
||
print("=== 費用科目 残高試算表（2025年6月〜12月）===")
|
||
print(f"{'科目CD':<6} {'科目名':<15} {'期間借方':>12} {'期間貸方':>12} {'残高':>12}")
|
||
print("-" * 62)
|
||
total_bal = 0
|
||
for r in rows:
|
||
print(f"{r[0]:<6} {r[1]:<15} {r[2]:>12,.0f} {r[3]:>12,.0f} {r[4]:>12,.0f}")
|
||
total_bal += r[4]
|
||
print("-" * 62)
|
||
print(f"{'費用合計':<22} {total_bal:>12,.0f}")
|
||
|
||
cur.execute("""
|
||
SELECT COALESCE(SUM(jl.credit),0) - COALESCE(SUM(jl.debit),0)
|
||
FROM journal_lines jl
|
||
JOIN accounts a ON jl.account_id = a.account_id
|
||
JOIN journal_entries je ON jl.journal_entry_id = je.journal_entry_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_type = 'revenue'
|
||
""")
|
||
total_rev = cur.fetchone()[0]
|
||
print()
|
||
print(f"収益合計 : {total_rev:>12,.0f}")
|
||
print(f"当期純損益 : {total_rev - total_bal:>12,.0f}")
|
||
conn.close()
|
||
|
||
|
||
|
||
cur.execute("""
|
||
SELECT je.entry_date, je.description, jl.debit, jl.credit, jl.line_description
|
||
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 = '813'
|
||
ORDER BY je.entry_date
|
||
""")
|
||
rows = cur.fetchall()
|
||
print()
|
||
print('=== 813給料手当 の仕訳明細 ===')
|
||
for r in rows:
|
||
desc = str(r[4]) if r[4] else ''
|
||
print(f' {r[0]} {str(r[1])[:45]:<45} 借:{r[2] or 0:>10,.0f} 貸:{r[3] or 0:>10,.0f} {desc}')
|
||
|
||
conn.close()
|