def fillTableWidget(self):
conn = DbConnector().connect()
cur = conn.cursor()
cur.execute("SELECT * FROM entries ORDER BY entry_id DESC LIMIT 40")
result = cur.fetchall()
self.ui.tableWidget.setRowCount(0)
for rowNumber, rowData in enumerate(result):
self.ui.tableWidget.insertRow(rowNumber)
date = str(rowData[1].strftime("%d.%m.%Y"))
self.ui.tableWidget.setItem(rowNumber, 0, QtWidgets.QTableWidgetItem(str(rowData[0])))
self.ui.tableWidget.setItem(rowNumber, 4, QtWidgets.QTableWidgetItem(str(rowData[6])))
self.ui.tableWidget.setItem(rowNumber, 1, QtWidgets.QTableWidgetItem(str(date)))
self.ui.tableWidget.setItem(rowNumber, 2, QtWidgets.QTableWidgetItem(str(rowData[2])))
self.ui.tableWidget.setItem(rowNumber, 3, QtWidgets.QTableWidgetItem(str(rowData[3])))
self.ui.tableWidget.setItem(rowNumber, 5, QtWidgets.QTableWidgetItem(str(rowData[4])))
self.ui.tableWidget.setItem(rowNumber, 6, QtWidgets.QTableWidgetItem(str(rowData[5])))
self.ui.tableWidget.setColumnHidden(0, True)
y, m, d = str(datetime.now().date()).split("-")
cur.execute("SELECT SUM(amount) FROM entries WHERE typ = 'Ausgabe' AND date_part('month', date::date) = '{0}' AND date_part('year', date::date) = '{1}'".format(m, y))
resultExpense = cur.fetchall()
expense = resultExpense[0][0]
if expense == None:
expense = 0
self.ui.totalExpense.setText(str(expense))
cur.execute("SELECT SUM(amount) FROM entries WHERE typ = 'Einnahme' AND date_part('month', date::date) = '{0}' AND date_part('year', date::date) = '{1}'".format(m, y))
resultReceipt = cur.fetchall()
receipt = resultReceipt[0][0]
if receipt == None:
receipt = 0
self.ui.totalReceipt.setText(str(receipt))
diff = receipt - expense
self.ui.totalDiff.setText(str(diff))
def deleteEntry(self):
if self.serverIsOn() == True:
conn = DbConnector().connect()
cur = conn.cursor()
cur.execute("""DELETE FROM entries where entry_id = '{0}'""".format(self.id))
conn.commit()
self.windowClose()
else:
self.msgDbCritical()
def getIncomeCategories(self):
if self.serverIsOn() == True:
conn = DbConnector().connect()
cur = conn.cursor()
cur.execute("SELECT * FROM category WHERE typ = 'Einnahme'")
results = cur.fetchall()
s = set()
for result in results:
s.add(result[1])
self.ui.comboBoxCategory.addItems(s)
else:
self.msgDbCritical()
def deleteCategory(self):
id = self.uic.lineEditId.text()
if self.serverIsOn() == True:
conn = DbConnector().connect()
cur = conn.cursor()
cur.execute(
"""DELETE FROM category where category_id = '{0}'""".format(
id))
conn.commit()
self.windowClose()
else:
self.msgDbCritical()
def updateCategory(self):
category = self.uic.lineEditCategory.text()
typ = self.uic.comboBoxTyp.currentText()
id = self.uic.lineEditId.text()
if self.serverIsOn() == True:
conn = DbConnector().connect()
cur = conn.cursor()
cur.execute(
"""UPDATE category SET category = '{0}', typ = '{1}' WHERE category_id = '{2}'"""
.format(category, typ, id))
conn.commit()
self.windowClose()
else:
self.msgDbCritical()
def changeCategory(self, id):
conn = DbConnector().connect()
cur = conn.cursor()
cur.execute(
"SELECT * FROM category where category_id = '{0}'".format(id))
results = cur.fetchall()
for item in results:
id, category, typ = item
self.uic.lineEditId.setText(str(id))
self.uic.lineEditCategory.setText(str(category))
if str(typ) == "Ausgabe":
self.uic.comboBoxTyp.setCurrentIndex(0)
else:
self.uic.comboBoxTyp.setCurrentIndex(1)
def writeDB(self):
category = self.ui.lineEditCategory.text()
typ = self.ui.comboBoxInOrOutcome.currentText()
if self.serverIsOn() == True:
conn = DbConnector().connect()
cur = conn.cursor()
cur.execute(
"""INSERT INTO category (category_id, category, typ) VALUES (default, '{0}', '{1}')"""
.format(category, typ))
conn.commit()
else:
self.msgDbCritical()
self.ui.lineEditCategory.clear()
self.fillTableWidgetCat()
def updateEntry(self):
y, m, d = self.ui.dateEdit.date().getDate()
date = str(d) + "." + str(m) + "." + str(y)
category = self.ui.comboBoxCategory.currentText()
amount = self.ui.doubleSpinBoxAmount.value()
cue = self.ui.lineEditCue.text()
remark = self.ui.lineEditRemark.text()
if self.serverIsOn() == True:
conn = DbConnector().connect()
cur = conn.cursor()
cur.execute("""UPDATE entries SET date = '{0}', category = '{1}', amount = '{2}', cue = '{3}', remark = '{4}' WHERE entry_id = '{5}'""".format(date, category, amount, cue, remark, self.id))
conn.commit()
self.windowClose()
else:
self.msgDbCritical()
def writeDB(self):
y, m, d = self.ui.dateEdit.date().getDate()
date = str(d) + "." + str(m) + "." + str(y)
category = self.ui.comboBoxCategory.currentText()
amount = self.ui.doubleSpinBoxAmount.value()
cue = ""
remark = self.ui.lineEditRemark.text()
print(date, category, amount, remark)
if self.serverIsOn() == True:
conn = DbConnector().connect()
cur = conn.cursor()
cur.execute(
"""INSERT INTO entries (entry_id, typ, date, category, amount, cue, remark) VALUES (default, 'Einnahme', '{0}', '{1}', '{2}', '{3}', '{4}')"""
.format(date, category, amount, cue, remark))
conn.commit()
else:
self.msgDbCritical()
def fillTableWidgetCat(self):
if self.serverIsOn() == True:
conn = DbConnector().connect()
cur = conn.cursor()
cur.execute("SELECT * FROM category ORDER BY category_id ASC")
result = cur.fetchall()
self.ui.tableWidget.setRowCount(0)
for rowNumber, rowData in enumerate(result):
self.ui.tableWidget.insertRow(rowNumber)
self.ui.tableWidget.setItem(
rowNumber, 0, QtWidgets.QTableWidgetItem(str(rowData[0])))
self.ui.tableWidget.setItem(
rowNumber, 1, QtWidgets.QTableWidgetItem(str(rowData[1])))
self.ui.tableWidget.setItem(
rowNumber, 2, QtWidgets.QTableWidgetItem(str(rowData[2])))
else:
self.msgDbCritical()
def changeEntry(self, id):
conn = DbConnector().connect()
cur = conn.cursor()
cur.execute("SELECT * FROM entries where entry_id = '{0}'".format(id))
results = cur.fetchall()
for item in results:
entry_id, date, category, amount, cue, remark, typ = item
self.ui.dateEdit.setDate(date)
self.ui.lineEditCue.setText(cue)
self.ui.lineEditRemark.setText(remark)
self.ui.doubleSpinBoxAmount.setValue(float(amount))
if typ == "Ausgabe":
self.getExpenseCategories()
self.ui.comboBoxCategory.setCurrentText(category)
else:
self.getIncomeCategories()
self.ui.comboBoxCategory.setCurrentText(category)
def fillTableWidget(self):
conn = DbConnector().connect()
cur = conn.cursor()
month = self.ui.comboBoxMonth.currentText()
year = self.ui.comboBoxYear.currentText()
if month != "*" and year == "*":
self.msgSelectionFault()
else:
if month == "*" and year == "*":
cur.execute("SELECT * FROM entries ORDER BY date DESC")
elif month == "*" and year != "*":
cur.execute(
"SELECT * FROM entries WHERE date_part('year', date::date) = '{0}' ORDER BY date DESC"
.format(year))
else:
cur.execute(
"SELECT * FROM entries WHERE date_part('month', date::date) = '{0}' AND date_part('year', date::date) = '{1}' ORDER BY date DESC"
.format(month, year))
result = cur.fetchall()
self.ui.tableWidget.setRowCount(0)
for rowNumber, rowData in enumerate(result):
self.ui.tableWidget.insertRow(rowNumber)
date = str(rowData[1].strftime("%d.%m.%Y"))
self.ui.tableWidget.setItem(
rowNumber, 0, QtWidgets.QTableWidgetItem(str(rowData[0])))
self.ui.tableWidget.setItem(
rowNumber, 4, QtWidgets.QTableWidgetItem(str(rowData[6])))
self.ui.tableWidget.setItem(
rowNumber, 1, QtWidgets.QTableWidgetItem(str(date)))
self.ui.tableWidget.setItem(
rowNumber, 2, QtWidgets.QTableWidgetItem(str(rowData[2])))
self.ui.tableWidget.setItem(
rowNumber, 3, QtWidgets.QTableWidgetItem(str(rowData[3])))
self.ui.tableWidget.setItem(
rowNumber, 5, QtWidgets.QTableWidgetItem(str(rowData[4])))
self.ui.tableWidget.setItem(
rowNumber, 6, QtWidgets.QTableWidgetItem(str(rowData[5])))
self.ui.tableWidget.setColumnHidden(0, True)
if month == "*" and year == "*":
cur.execute(
"SELECT SUM(amount) FROM entries WHERE typ = 'Ausgabe'")
elif month == "*" and year != "*":
cur.execute(
"SELECT SUM(amount) FROM entries WHERE typ = 'Ausgabe' AND date_part('year', date::date) = '{0}'"
.format(year))
else:
cur.execute(
"SELECT SUM(amount) FROM entries WHERE typ = 'Ausgabe' AND date_part('month', date::date) = '{0}' AND date_part('year', date::date) = '{1}'"
.format(month, year))
resultExpense = cur.fetchall()
expense = resultExpense[0][0]
if expense == None:
expense = 0
self.ui.totalExpense.setText(str(expense))
if month == "*" and year == "*":
cur.execute(
"SELECT SUM(amount) FROM entries WHERE typ = 'Einnahme'")
elif month == "*" and year != "*":
cur.execute(
"SELECT SUM(amount) FROM entries WHERE typ = 'Einnahme' AND date_part('year', date::date) = '{0}'"
.format(year))
else:
cur.execute(
"SELECT SUM(amount) FROM entries WHERE typ = 'Einnahme' AND date_part('month', date::date) = '{0}' AND date_part('year', date::date) = '{1}'"
.format(month, year))
resultReceipt = cur.fetchall()
receipt = resultReceipt[0][0]
if receipt == None:
receipt = 0
self.ui.totalReceipt.setText(str(receipt))
diff = receipt - expense
self.ui.totalDiff.setText(str(diff))
self.ui.totalEntries.setText(str(self.ui.tableWidget.rowCount()))