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