OpenClaw
89 lines
2.7 KiB
Python
89 lines
2.7 KiB
Python
import psycopg2
|
|
|
|
# Verbindung zur Datenbank
|
|
conn = psycopg2.connect(
|
|
host='localhost',
|
|
port=5432,
|
|
database='buchhaltung',
|
|
user='postgres',
|
|
password='postgres'
|
|
)
|
|
|
|
cur = conn.cursor()
|
|
|
|
print('=== NEBENKOSTEN IMPORT ===\n')
|
|
|
|
# Prüfe vorhandene Einträge
|
|
cur.execute("SELECT COUNT(*) FROM nebenkosten WHERE jahr BETWEEN 2020 AND 2024")
|
|
count = cur.fetchone()[0]
|
|
print(f'Vorhandene Einträge 2020-2024: {count}')
|
|
|
|
if count > 0:
|
|
print('Lösche vorhandene Einträge...')
|
|
cur.execute("DELETE FROM nebenkosten WHERE jahr BETWEEN 2020 AND 2024")
|
|
conn.commit()
|
|
print('Gelöscht.\n')
|
|
|
|
# Alle Abrechnungen
|
|
abrechnungen = [
|
|
(2020, 'Zingelstr. 14', 'Kevin Körger', 28.34, 16.73, None, 2.86, 7.23, 1.52),
|
|
(2020, 'Zingelstr. 14', 'Johanna Krohn / Tobias Welling', 34.19, 16.73, None, 6.67, 7.23, 3.56),
|
|
(2021, 'Zingelstr. 14', 'Kevin Körger', 471.91, 214.35, 75.29, 33.68, 130.64, 17.95),
|
|
(2021, 'Zingelstr. 14', 'Johanna Krohn / Tobias Welling', 530.10, 203.70, 75.29, 78.59, 130.64, 41.89),
|
|
(2022, 'Ahornweg 6', 'Kevin Körger', 487.72, 230.00, 52.73, 33.68, 153.36, 17.95),
|
|
(2022, 'Ahornweg 6', 'Johanna Krohn / Tobias Welling', 556.56, 230.00, 52.73, 78.59, 153.36, 41.89),
|
|
(2023, 'Ahornweg 6', 'Kevin Körger', 148.20, 63.47, 17.34, 11.07, 50.42, 5.90),
|
|
(2023, 'Ahornweg 6', 'Johanna Krohn / Tobias Welling', 170.83, 63.47, 17.34, 25.84, 50.42, 13.77),
|
|
(2023, 'Zingelstr. 14', 'Yvonne Brandt', 501.48, 129.58, 102.53, 75.36, 153.85, 40.17),
|
|
(2024, 'Zingelstr. 14', 'Yvonne Brandt', 675.56, 115.41, 175.76, 112.27, 212.28, 59.84),
|
|
]
|
|
|
|
query = """
|
|
INSERT INTO nebenkosten
|
|
(jahr, wohnung, mieter, nebenkosten, versicherung, heizkosten, wasser, muell, sonstiges)
|
|
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
|
|
"""
|
|
|
|
imported = 0
|
|
for data in abrechnungen:
|
|
try:
|
|
cur.execute(query, data)
|
|
imported += 1
|
|
print(f'OK {data[2]} {data[0]}: {data[3]} EUR')
|
|
except Exception as e:
|
|
print(f'FEHLER bei {data[2]} {data[0]}: {e}')
|
|
|
|
conn.commit()
|
|
|
|
print(f'\n=== VERIFIZIERUNG ===')
|
|
print(f'Importiert: {imported} von {len(abrechnungen)}')
|
|
|
|
# Zeige alle Einträge
|
|
cur.execute("""
|
|
SELECT jahr, mieter, wohnung, nebenkosten
|
|
FROM nebenkosten
|
|
WHERE jahr BETWEEN 2020 AND 2024
|
|
ORDER BY jahr, mieter
|
|
""")
|
|
|
|
print('\nAlle Einträge:')
|
|
for row in cur.fetchall():
|
|
print(f' {row[0]}: {row[1]} ({row[2]}) - {row[3]} €')
|
|
|
|
# Summen pro Jahr
|
|
cur.execute("""
|
|
SELECT jahr, COUNT(*), SUM(nebenkosten)
|
|
FROM nebenkosten
|
|
WHERE jahr BETWEEN 2020 AND 2024
|
|
GROUP BY jahr
|
|
ORDER BY jahr
|
|
""")
|
|
|
|
print('\nSummen pro Jahr:')
|
|
for row in cur.fetchall():
|
|
print(f' {row[0]}: {row[1]} Abrechnungen, {row[2]:.2f} €')
|
|
|
|
cur.close()
|
|
conn.close()
|
|
print('\n✅ IMPORT ABGESCHLOSSEN')
|