127 lines
2.9 KiB
Python
127 lines
2.9 KiB
Python
from dbpool import getConnection, getOne, getMany, putOne, call
|
|
import datetime
|
|
import decimal
|
|
import dateparser
|
|
|
|
def get_zahlungen_by_mieter(mieter_id):
|
|
return getMany("""
|
|
SELECT id,
|
|
mieter,
|
|
datum_ist,
|
|
datum_soll
|
|
betrag,
|
|
kommentar
|
|
FROM zahlung
|
|
WHERE mieter = ?
|
|
""", [ mieter_id ], "Zahlung")
|
|
|
|
def get_zahlung(id):
|
|
return getOne("""
|
|
SELECT id,
|
|
mieter,
|
|
datum_ist,
|
|
datum_soll
|
|
betrag,
|
|
kommentar
|
|
FROM zahlung
|
|
WHERE id = ?
|
|
""", [ id ], "Zahlung")
|
|
|
|
|
|
def get_forderungen_by_mieter(mieter_id):
|
|
return getMany("""
|
|
SELECT id,
|
|
mieter,
|
|
datum,
|
|
betrag,
|
|
kommentar,
|
|
ref_wohnung
|
|
FROM forderung
|
|
WHERE mieter = ?
|
|
""", [ mieter_id ], "Forderung")
|
|
|
|
def get_forderung(id):
|
|
return getOne("""
|
|
SELECT id,
|
|
mieter,
|
|
datum,
|
|
betrag,
|
|
kommentar,
|
|
ref_wohnung
|
|
FROM forderung
|
|
WHERE id = ?
|
|
""", [ id ], "Forderung")
|
|
|
|
def get_zahlungforderung_by_mieter_and_year(mieter_id, year):
|
|
if year == 0:
|
|
year = datetime.datetime.now().year
|
|
start_date = "{}-01-01".format(year)
|
|
end_date = "{}-12-31".format(year)
|
|
return getMany("""
|
|
SELECT
|
|
id,
|
|
mieter,
|
|
datum_soll,
|
|
datum_ist,
|
|
betrag_zahlung,
|
|
betrag_forderung,
|
|
zf_type,
|
|
kommentar
|
|
FROM zahlung_forderung
|
|
WHERE mieter = ? AND
|
|
datum_soll BETWEEN ? AND ?
|
|
""", [mieter_id, start_date, end_date], "ZahlungForderung")
|
|
|
|
def get_saldo_by_mieter_and_year(mieter_id, year):
|
|
if year == 0:
|
|
year = datetime.datetime.now().year
|
|
start_date = "{}-01-01".format(year)
|
|
end_date = "{}-12-31".format(year)
|
|
sumZahlungen = getOne("""
|
|
SELECT
|
|
ROUND(sum(betrag), 2) as sum
|
|
FROM zahlung
|
|
WHERE mieter = ? AND
|
|
datum_soll BETWEEN ? AND ?
|
|
""", [mieter_id, start_date, end_date], "SumZahlung")
|
|
sumForderungen = getOne("""
|
|
SELECT
|
|
ROUND(sum(betrag), 2) as sum
|
|
FROM forderung
|
|
WHERE mieter = ? AND
|
|
datum BETWEEN ? AND ?
|
|
""", [mieter_id, start_date, end_date], "SumZahlung")
|
|
print("Zahlung: {}, Forderung: {}".format(sumZahlungen, sumForderungen))
|
|
decimal.getcontext().prec = 2
|
|
sumZ = sumZahlungen["sum"]
|
|
if not sumZ:
|
|
sumZ = 0
|
|
sumF = sumForderungen["sum"]
|
|
if not sumF:
|
|
sumF = 0
|
|
saldo = sumZ - sumF
|
|
return {
|
|
"forderungen": float(sumF),
|
|
"saldo": float(saldo),
|
|
"zahlungen": float(sumZ)
|
|
}
|
|
|
|
def put_zahlung(**args):
|
|
try:
|
|
body = args["body"]
|
|
datum_soll_raw = body["datum_soll"]
|
|
datum_ist_raw = body["datum_ist"]
|
|
|
|
print("Input of put_zahlung: {}".format(body))
|
|
datum_soll = dateparser.parse(datum_soll_raw, languages=["de"])
|
|
datum_ist = dateparser.parse(datum_ist_raw, languages=["de"])
|
|
return putOne("""
|
|
INSERT INTO zahlung (datum_soll, datum_ist, mieter, betrag, kommentar)
|
|
VALUES(?, ?, ?, ?, ?)
|
|
""", [ datum_soll, datum_ist, body["mieter"], body["betrag"], body["kommentar"] ], "Zahlung")
|
|
except KeyError as e:
|
|
print("Some parameter missing: {}".format(e))
|
|
return str(e), 500
|
|
|
|
def insertAllForMonth():
|
|
return call("insert_monatl_miet_forderung") |