def listeAlleMonateEinkommen(self):
aSumme = 0
aAnzMonate = 0
cur = self.openAlleMonateEinkommen()
if (cur == None):
return aAnzMonate, aSumme, []
ea = []
for row in cur:
s = "%s | %20s" % (row[0], T.sDM(row[1]))
# print s
k = {
'jahr': row[0],
'monat': row[1],
'betrag': row[2],
'sDMG': T.sDM(row[2]),
'sDMU': T.sDM(row[3]),
'sDMS': T.sDM(row[4])
}
aSumme = aSumme + row[2]
aAnzMonate = aAnzMonate + 1
# print k
ea.append(k)
if aAnzMonate > 0:
aSumme = aSumme / aAnzMonate
return aAnzMonate, aSumme, ea
def htmlAlleJahreEA(self):
aAnzahl, aSumme, ea = self.listeAlleJahreEA()
s = ''
for l in ea:
jahr = l['jahr']
# print type(konto),konto
betrag = l['betrag']
sSaldo = l['sDM']
sDME = l['sDME']
sDMA = l['sDMA']
if (betrag < 0):
aKlasse = 'class=table-right-currency-red'
else:
aKlasse = 'class=table-right-currency'
# print type(konto),konto
s = '%s <tr><td class=table-left>%s</td><td class=table-right-currency>%s</td><td class=table-right-currency>%s</td><td %s>%s</td></tr>' % (
s, jahr, sDME, sDMA, aKlasse, sSaldo)
return (
"<table>"
"<tr><th class=table-left>Jahr</th><th class=table-right-currency>Einnahmen</th><th class=table-right-currency>Ausgaben</th><th class=table-right-currency>Saldo</th></tr>"
"%s"
"<tr><th class=table-left>Durchschnitt für %d Jahre</th><th class=table-right-currency></th><th class=table-right-currency></th><th class=table-right-currency>%s</th></tr>"
"</table>") % (s, aAnzahl, T.sDM(aSumme))
return (
"<table>"
"<tr><th class=table-left>Jahr</th><th class=table-right-currency>Saldo</th></tr>"
"%s"
"<tr><th class=table-left>Durchschnitt</th><th class=table-right-currency>%s</th></tr>"
"</table>") % (s, T.sDM(aSumme))
def kontenhtml(self):
if self.is_angemeldet():
k = wnfportal_dm_konten.dmKonten()
summe = T.sDM(k.summeAlleKonten())
s = ''
for l in k.listeAlleKonten():
konto = l['konto'] # .encode('utf-8')
saldo = l['saldo']
# print type(konto),konto
s = '%s <tr><td class=table-left>%s</td><td class=table-right-currency>%s</td></tr>' % (
s, konto, saldo)
# s = konto
# print s
# s='<tr><td class=table-left>bb</td><td class=table-right-currency>aa</td></tr>'
s = (
"<table>"
"<tr><th class=table-left>Konto</th><th class=table-right-currency>Stand</th></tr>"
"%s"
"<tr><th class=table-left>Summe</th><th class=table-right-currency>%s</th></tr>"
"</table>") % (s, summe)
summe = T.sDM(k.summeProjektWintergarten())
s = (
"%s"
"<table>"
"<tr><th class=table-left><a href=/projektWintergarten2017 >Projekt Wintergarten 2017</a></th><th class=table-right-currency>%s</th></tr>"
"</table>") % (s, summe)
k.closeConnection
return s
else:
return ""
def listeLetzteEA(self):
aSumme = 0
aSQL = """
SELECT E.ID,E.DATUM,E.KURZ,E.BETRAG
FROM KO_KUBEA E
WHERE E.DATUM >= %s
ORDER BY E.DATUM DESC,E.KURZ
"""
aSQL = aSQL % (T.wnfDateToSQL(T.wnfTagVorVor8Wochen()))
print(aSQL)
cur = self.sqlOpen(aSQL)
if (cur == None):
return aSumme, []
ea = []
for row in cur:
s = "%s | %s" % (row[1].strftime("%d.%m.%y"), row[2])
ttmmjj = "%s" % (row[1].strftime("%d.%m.%y"))
# print (s)
k = {
'konto_ea_id': row[0],
'datum': str(row[1]),
'ttmmjj': ttmmjj,
'kurz': row[2],
'betrag': T.sDM(row[3]),
'datumkurz': s
}
aSumme = aSumme + row[3]
print(k)
ea.append(k)
return aSumme, ea
def listeProjektK(self, aProjekt_ID):
print(aProjekt_ID)
aSumme = 0
aSQL = """
SELECT K.ID,MAX(E.DATUM),K.KURZ,SUM(E.BETRAG)
FROM KO_KUBEA E
LEFT JOIN KO_KUBKAT K ON K.ID=E.KAT_ID
WHERE E.PROJEKT_ID = %d
AND NOT E.KAT_ID IS NULL
GROUP BY K.ID,K.KURZ
ORDER BY 4,K.KURZ
"""
aSQL = aSQL % (aProjekt_ID)
print(aSQL)
cur = self.sqlOpen(aSQL)
if (cur == None):
return aSumme, []
ea = []
for row in cur:
s = "%s | %s" % (row[1].strftime("%d.%m.%y"), row[2])
ttmmjj = "%s" % (row[1].strftime("%d.%m.%y"))
# print (s)
k = {
'konto_ea_id': row[0],
'datum': str(row[1]),
'ttmmjj': ttmmjj,
'kurz': row[2],
'betrag': T.sDM(row[3]),
'datumkurz': s
}
aSumme = aSumme + row[3]
print(k)
ea.append(k)
return aSumme, ea
def htmlAlleMonateEA(self):
aAnzahl, aSumme, ea = self.listeAlleMonateEA()
# print(type(ea))
s = ''
for l in ea:
# print(l)
monat = "%2d/%d" % (l['monat'], l['jahr'])
betrag = l['betrag']
sSaldo = l['sDM']
sDME = l['sDME']
sDMA = l['sDMA']
if (betrag < 0):
aKlasse = 'class=table-right-currency-red'
else:
aKlasse = 'class=table-right-currency'
# print type(konto),konto
sDME = '<a href="monatea/%d/%d/">%s</a>' % (l['jahr'], l['monat'],
sDME)
s = '%s <tr>' \
'<td class=table-left>%s</td>' \
'<td %s>%s</td>' \
'<td class=table-right-currency>%s</td>' \
'<td class=table-right-currency>%s</td>' \
'</tr>' \
% (s, monat, aKlasse, sSaldo, sDME, sDMA)
# print(s)
return (
"<table>"
"<tr><th class=table-left>Monat</th><th class=table-right-currency>Saldo</th><th class=table-right-currency>Einnahmen</th><th class=table-right-currency>Ausgaben</th></tr>"
"%s"
"<tr><th class=table-left>Durchschnitt für %d Monate</th><th class=table-right-currency></th><th class=table-right-currency></th><th class=table-right-currency>%s</th></tr>"
"</table>") % (s, aAnzahl, T.sDM(aSumme))
def listeProjekt(self, aProjekt_ID):
print(aProjekt_ID)
aSumme = 0
aSQL = """
SELECT E.ID,E.DATUM,E.KURZ,E.BETRAG
FROM KO_KUBEA E
WHERE E.PROJEKT_ID = %d
ORDER BY E.DATUM DESC,E.KURZ
"""
aSQL = aSQL % (aProjekt_ID)
print(aSQL)
cur = self.sqlOpen(aSQL)
if (cur == None):
return aSumme, []
ea = []
for row in cur:
s = "%s | %s" % (row[1].strftime("%d.%m.%y"), row[2])
ttmmjj = "%s" % (row[1].strftime("%d.%m.%y"))
# print (s)
k = {
'konto_ea_id': row[0],
'datum': str(row[1]),
'ttmmjj': ttmmjj,
'kurz': row[2],
'betrag': T.sDM(row[3]),
'datumkurz': s
}
aSumme = aSumme + row[3]
print(k)
ea.append(k)
return aSumme, ea
def analyseAusgabenVonBis(self, aVon, aBis):
"""
Alle EA bis 10 % zusammenfassen
"""
aSQL = """
SELECT ABS(SUM(E.BETRAG)),K.KURZ,K.ID
FROM KO_KUBEA E
LEFT JOIN KO_KUBKST K ON K.ID=E.KST_ID
WHERE E.IGNORIEREN = 0
AND NOT E.KST_ID IS NULL
AND E.BETRAG < 0
AND E.DATUM BETWEEN %s AND %s
GROUP BY K.KURZ,K.ID
ORDER BY 2
"""
aSQL = aSQL % (aVon, aBis)
print(aSQL)
cur = self.sqlOpen(aSQL)
if (cur == None):
return 0, [], [], []
ea = []
kst = []
aRecord = []
aRecKst = []
aSumme = 0
for row in cur:
# s = "%s | %20s" % (row[0], T.sDM(row[1]))
# print s
k = {'betrag': row[0], 'kurz': row[1], 'KST_ID': row[2]}
aSumme = aSumme + row[0]
# print(aSumme)
aRecKst.append(k)
a10Prozent = aSumme / 20
print(aSumme, a10Prozent)
for k in aRecKst:
if (k['betrag'] < a10Prozent):
x = {
'betrag': k['betrag'],
'sDM': T.sDM(k['betrag']),
'kurz': k['kurz'],
'ID': k['KST_ID']
}
aRecord.append(x)
else:
rx = self.analyseAusgabenVonBis10Prozent(
k['KST_ID'], k['kurz'], aVon, aBis, a10Prozent)
for x in rx:
aRecord.append(x)
print(aRecord)
for x in aRecord:
ea.append(round(x['betrag']))
kst.append(x['kurz'])
print(kst)
print(ea)
self.closeConnection()
return aSumme, ea, kst, aRecord
def analyseAusgabenVonBis10Prozent(self, aKst_ID, aKst_Kurz, aVon, aBis,
a10Prozent):
aSQL = """
SELECT SUM(ABS(E.BETRAG)),K.KURZ,K.ID
FROM KO_KUBEA E
LEFT JOIN KO_KUBKAT K ON K.ID=E.KAT_ID
WHERE E.IGNORIEREN = 0
AND E.KST_ID = %d
AND E.BETRAG < 0
AND E.DATUM BETWEEN %s AND %s
GROUP BY K.KURZ,K.ID
ORDER BY 2
"""
aSQL = aSQL % (aKst_ID, aVon, aBis)
print(aSQL)
cur = self.sqlOpen(aSQL)
if (cur == None):
return []
aRec = []
aRest = 0
for row in cur:
if (row[0] > a10Prozent):
x = {
'betrag': row[0],
'sDM': T.sDM(row[0]),
'kurz': '%s - %s' % (aKst_Kurz, row[1]),
'ID': aKst_ID
}
aRec.append(x)
else:
aRest = aRest + row[0]
if aRest > 0:
x = {
'betrag': aRest,
'sDM': T.sDM(aRest),
'kurz': '%s' % (aKst_Kurz),
'ID': aKst_ID
}
aRec.append(x)
return aRec
def dreiSpaltig(self, aSQL, c1, c2, c3, aLink, c4):
rows = self.listTabelleAsRows(aSQL)
css = []
aSumme = 0
aAnz = 0
s = '<th class=table-3c-spalte1>%s</th><th class=table-3c-spalte2>%s</th><th class=table-3c-spalte3>%s</th>'
s = s % ('Datum', 'Bezeichnung', 'Betrag')
css = css + [s]
for r in rows:
s = '<td class=table-3c-spalte1>%s</td><td class=table-3c-spalte2>%s</td><td class=table-3c-spalte3>%s</td>'
if c4 and r[c4]:
aHref = '<a href="%s/%d">%s</a>' % (aLink, r[c4], r[c2])
else:
aHref = r[c2]
s = s % (r[c1], aHref, T.sDM(r[c3]))
css = css + [s]
aSumme = aSumme + r[c3]
aAnz += 1
s = '<td class=table-3c-spalte1>%s</td><td class=table-3c-spalte2>%s</td><td class=table-3c-spalte3>%s</td>'
s = s % (aAnz, '', T.sDM(aSumme))
css = css + [s]
return css
def listeAlleMonateEA(self):
aSumme = 0
aAnzMonate = 0
aSQL = """
SELECT
EXTRACT(YEAR FROM E.DATUM) AS JAHR,
EXTRACT(MONTH FROM E.DATUM) AS MONAT,
SUM(E.BETRAG),
SUM(CASE WHEN E.BETRAG > 0 THEN E.BETRAG END) AS Einnahme,
SUM(CASE WHEN E.BETRAG < 0 THEN E.BETRAG END) AS Ausgabe
FROM KO_KUBEA E
WHERE E.IGNORIEREN = 0
GROUP BY EXTRACT(YEAR FROM E.DATUM),EXTRACT(MONTH FROM E.DATUM)
ORDER BY 1 DESC,2 DESC
"""
# print(aSQL)
cur = self.sqlOpen(aSQL)
if (cur == None):
return aAnzMonate, aSumme, []
ea = []
for row in cur:
s = "%s/%s | %20s" % (row[0], row[1], T.sDM(row[2]))
# print(s)
k = {
'jahr': row[0],
'monat': row[1],
'betrag': row[2],
'sDM': T.sDM(row[2]),
'sDME': T.sDM(row[3]),
'sDMA': T.sDM(row[4])
}
aSumme = aSumme + row[1]
aAnzMonate = aAnzMonate + 1
# print(k)
ea.append(k)
if aAnzMonate > 0:
aSumme = aSumme / aAnzMonate
return aAnzMonate, aSumme, ea
def htmlLetzteEA(self):
aSumme, ea = self.listeLetzteEA()
s = ''
for l in ea:
datum = l['ttmmjj'] # .encode('utf-8')
kurz = l['kurz'] # .encode('utf-8')
betrag = l['betrag']
s = '%s <tr><td class=table-3c-spalte1>%s</td><td class=table-3c-spalte2>%s</td><td class=table-3c-spalte3>%s</td></tr>' % (
s, datum, kurz, betrag)
return (
"<table>"
"<tr><th class=table-3c-spalte1>Datum</th><th class=table-3c-spalte2>Bezeichnung</th><th class=table-3c-spalte3>Betrag</th></tr>"
"%s"
"<tr><th class=table-3c-spalte1></th><th class=table-3c-spalte2>Summe</th><th class=table-3c-spalte3>%s</th></tr>"
"</table>") % (s, T.sDM(aSumme))
def htmlEAMonatlich(self):
aSumme, ea = self.analyseEAMonatlich()
s = ''
for l in ea:
kurz = l['kurz'] # .encode('utf-8')
betrag = l['betrag']
durchschnitt = l['durchschnitt']
s = '%s <tr><td class=table-3c-spalte1>%s</td><td class=table-3c-spalte3>%.2f</td><td class=table-3c-spalte3>%.2f</td></tr>' % (
s, kurz, betrag, durchschnitt)
return (
"<table>"
"<tr><th class=table-3c-spalte1>Bezeichnung</th><th class=table-3c-spalte3>Betrag</th><th class=table-3c-spalte3>Durchschnitt</th></tr>"
"%s"
"<tr><th class=table-3c-spalte1></th><th class=table-3c-spalte2>Summe</th><th class=table-3c-spalte3>%s</th></tr>"
"</table>") % (s, T.sDM(aSumme))
def listeAlleKonten(self):
aSQL = """
SELECT E.KUB_ID,K.KURZ,SUM(E.BETRAG)
FROM KO_KUBEA E
LEFT JOIN KO_KUB K ON K.ID=E.KUB_ID
GROUP BY K.KURZ,E.KUB_ID
HAVING SUM(E.BETRAG)<>0
ORDER BY K.KURZ
"""
cur = self.sqlOpen(aSQL)
if (cur == None):
return []
konten = []
for row in cur:
k = {'konto_id': row[0], 'konto': row[1], 'saldo': T.sDM(row[2])}
# print k
konten.append(k)
return konten
def htmldiagrammVonBis(self, aVon, aBis, dn):
aSumme, aData, aLabels, aRecord = self.analyseAusgabenVonBis(
aVon, aBis)
p = '/home/wnf/Entwicklung/PycharmProjects/wnfportal/wnfportal_python/www/img/'
self.diagrammKostenartVonBis(p, dn, aData, aLabels)
s = ''
for l in aRecord:
aLabel = l['ID']
kurz = l['kurz'] # .encode('utf-8')
betrag = l['sDM']
s = '%s <tr><td class=table-3c-spalte1>%s</td><td class=table-3c-spalte2>%s</td><td class=table-3c-spalte3>%s</td></tr>' % (
s, aLabel, kurz, betrag)
tabelle = (
"<table>"
"<tr><th class=table-3c-spalte1>Kurz</th><th class=table-3c-spalte2>Bezeichnung</th><th class=table-3c-spalte3>Betrag</th></tr>"
"%s"
"<tr><th class=table-3c-spalte1></th><th class=table-3c-spalte2>Summe</th><th class=table-3c-spalte3>%s</th></tr>"
"</table>") % (s, T.sDM(aSumme))
return ('<img src="img/%s.png" alt="Diagramm"> %s' % (dn, tabelle))
def htmlAlleMonateEinkommen(self):
aAnzahl, aSumme, ea = self.listeAlleMonateEinkommen()
s = ''
for l in ea:
monat = "%2d/%d" % (l['monat'], l['jahr'])
betrag = l['betrag']
sDMG = l['sDMG']
sDMU = l['sDMU']
sDMS = l['sDMS']
if (betrag < 0):
aKlasse = 'class=table-right-currency-red'
else:
aKlasse = 'class=table-right-currency'
# print type(konto),konto
s = '%s <tr><td class=table-left>%s</td><td class=table-right-currency>%s</td><td class=table-right-currency>%s</td><td %s>%s</td></tr>' % (
s, monat, sDMU, sDMS, aKlasse, sDMG)
return (
"<table>"
"<tr><th class=table-left>Monat</th><th class=table-right-currency>Uwe</th><th class=table-right-currency>Sabine</th><th class=table-right-currency>Gesamt</th></tr>"
"%s"
"<tr><th class=table-left>Durchschnitt für %d Monate</th><th class=table-right-currency></th><th class=table-right-currency></th><th class=table-right-currency>%s</th></tr>"
"</table>") % (s, aAnzahl, T.sDM(aSumme))
def listeKostenartVonBis(self, aVon, aBis):
aSQL = """
SELECT ABS(SUM(E.BETRAG)),K.KURZ,K.ID
FROM KO_KUBEA E
LEFT JOIN KO_KUBKST K ON K.ID=E.KST_ID
WHERE E.IGNORIEREN = 0
AND NOT E.KST_ID IS NULL
AND E.BETRAG < 0
AND E.DATUM BETWEEN %s AND %s
GROUP BY K.KURZ,K.ID
ORDER BY 2
"""
aSQL = aSQL % (aVon, aBis)
print(aSQL)
cur = self.sqlOpen(aSQL)
if (cur == None):
return [], []
ea = []
kst = []
aRecord = []
aSumme = 0
for row in cur:
# s = "%s | %20s" % (row[0], T.sDM(row[1]))
# print s
ea.append(round(row[0]))
aSumme = aSumme + row[0]
kst.append(row[1])
k = {
'betrag': row[0],
'sDM': T.sDM(row[0]),
'kurz': row[1],
'ID': row[2]
}
# print(aSumme)
aRecord.append(k)
return aSumme, ea, kst, aRecord
def jsonAlleJahreEA(self):
aSumme, ea = self.listeAlleJahreEA()
j = {'summe': T.sDM(aSumme), 'ea': ea}
return j
def jsonLetzteEA(self):
aSumme, ea = self.listeLetzteEA()
j = {'summe': T.sDM(aSumme), 'ea': ea}
return j
def jsonAlleKonten(self):
j = {
'summe': T.sDM(self.summeAlleKonten()),
'konten': self.listeAlleKonten()
}
return j
