def update_total_income(self):
"""Return the total of monthly income for the
currently logged in user, plus any one off incomes
for the current month. To be displayed on
the main menu summary screen"""
with DataBaseObject() as db:
monthly_query = """SELECT SUM(value) FROM income
WHERE user = %s
AND recurring = '1'
"""
monthly_args = (self.manager.current_user)
one_off_query = """SELECT SUM(value) FROM income
WHERE user = %s
AND recurring = '0'
AND month(date) = %s
"""
one_off_args = (self.manager.current_user, dt.today().month)
monthly_incomes = db.fetch_data(monthly_query, monthly_args)
one_off_incomes = db.fetch_data(one_off_query, one_off_args)
monthly = monthly_incomes[0][0]
oneoff = one_off_incomes[0][0]
if monthly == None:
monthly = 0
if oneoff == None:
oneoff = 0
self.manager.total_income = str(round(monthly + oneoff, 2))
self.manager.current_screen.ids.total_income.text \
= f"£{(str(self.manager.total_income))}"
def check_user_and_pass(self):
with DataBaseObject() as db:
query = """SELECT * FROM users
WHERE username = %s
AND password = %s
"""
args = (self.username, self.password)
userquery = db.fetch_data(query, args)
return userquery
def get_all_bills(self):
"""Return the current monthly bills
from the database"""
with DataBaseObject() as db:
query = """SELECT monthly_value, bill_name FROM bills
WHERE user = %s
"""
args = (self.manager.current_user)
billquery = db.fetch_data(query, args)
return billquery
def get_monthly_incomes(self):
"""Return the current recurring incomes
from the database"""
with DataBaseObject() as db:
query = """SELECT value, income_name FROM income
WHERE user = %s
AND recurring = '1'
"""
args = (self.manager.current_user)
incomequery = db.fetch_data(query, args)
return incomequery
def get_one_off_incomes(self):
"""Return the one off incomes from the
current month from the database"""
with DataBaseObject() as db:
query = """SELECT value, income_name FROM income
WHERE user = %s
AND recurring = '0'
AND month(date) = %s
"""
args = (self.manager.current_user, dt.today().month)
incomequery = db.fetch_data(query, args)
return incomequery
def get_payments_from_db(self):
"""Queries the database and returns the
total of payments for the currently logged
in user for the current month."""
with DataBaseObject() as db:
query = """SELECT value, category, extra_details FROM payments
WHERE user = %s
AND month(date) = %s
"""
args = (self.manager.current_user, dt.today().month)
paymentquery = db.fetch_data(query, args)
return paymentquery
def update_total_bills(self):
"""Queries the database to return all recurring monthly
bills for the currently logged in user and sum the total
to display on the summary screen"""
with DataBaseObject() as db:
query = """SELECT sum(monthly_value) FROM bills
WHERE user = %s
"""
args = (self.manager.current_user)
billquery = db.fetch_data(query, args)
if billquery[0][0] == None:
self.manager.total_bills = "0"
else:
self.manager.total_bills = str(round(billquery[0][0], 2))
self.manager.current_screen.ids.total_bills.text \
= f"£{str(self.manager.total_bills)}"
def update_total_spend(self):
"""Return the total of payments for the
currently logged in user for the current month.
To be displayed on the main menu summary screen"""
with DataBaseObject() as db:
query = """SELECT SUM(value) FROM payments
WHERE user = %s
AND month(date) = %s
"""
args = (self.manager.current_user, dt.today().month)
spendquery = db.fetch_data(query, args)
if spendquery[0][0] == None:
self.manager.total_spend = "0"
else:
self.manager.total_spend = str(round(spendquery[0][0], 2))
self.manager.current_screen.ids.total_spend.text \
= f"£{str(self.manager.total_spend)}"
def delete_last_income():
with DataBaseObject() as db:
db.run_database_command(
"DELETE FROM income order by UID DESC limit 1")
def delete_last_payment():
with DataBaseObject() as db:
db.run_database_command(
"DELETE FROM payments order by UID DESC limit 1")