def update_recipe(self, recipe_id):
"""
Updates recipe in DB and refreshes page
:param recipe_id: (int) ID of recipe stored in DB
:return: None
"""
validated_data = self.validate_input()
if validated_data:
cfg.db_execute(
sql='''UPDATE recipes SET title=?, `desc`=? WHERE id=?;''',
variables=(validated_data[0], validated_data[1], recipe_id))
query = self.parent.search.get()
if query == 'search...':
self.parent.refresh_frame()
else:
self.parent.refresh_frame(query)
if self.parent.list.exists(recipe_id):
self.parent.list.selection_set(recipe_id)
self.parent.list.focus_set()
self.parent.list.focus(recipe_id)
else:
self.parent.deactivate_buttons()
self.parent.show_frame(int(recipe_id))
self.destroy()
def delete_revenue(self, revenue_id):
"""
Deletes revenue from DB and refreshes trees elements
:param revenue_id: (int) ID of revenue stored in DB
:return: None
"""
cfg.db_execute(sql='''DELETE FROM revenues WHERE id=?;''',
variables=(revenue_id, ))
self.parent.refresh_frame()
self.destroy()
def add_revenue(self, revenue_type):
"""
Adds new revenue to DB and refreshes trees elements
:param revenue_type: (str) type of revenue (income, expense)
:return: None
"""
validated_data = self.validate_input()
if validated_data:
cfg.db_execute(
sql='''INSERT INTO revenues VALUES (NULL, ?, ?, ?, ?, ?);''',
variables=(validated_data[0], revenue_type, validated_data[1],
validated_data[2], cfg.CURRENT_USER['id']))
self.parent.refresh_frame()
self.destroy()
def update_revenue(self, revenue_id):
"""
Updates revenue selected by user and refreshes trees elements
:param revenue_id: (int) ID of revenue stored in DB
:return: None
"""
validated_data = self.validate_input()
if validated_data:
cfg.db_execute(sql='''UPDATE revenues
SET name=?, amount=?, create_date=?
WHERE id=?;''',
variables=(validated_data[0], validated_data[1],
validated_data[2], revenue_id))
self.parent.refresh_frame()
self.destroy()
def authenticate(self):
"""
Validate user input, authenticate user and saves his details to CURRENT_USER global variable
:return: None
"""
# VALIDATES USERNAME
username = self.username_val.get()
if not username:
cfg.ValidationError(self,
text='Username can\'t be empty!',
padx=10,
pady=10)
return
user = cfg.db_execute(sql='''SELECT * FROM users WHERE username=?;''',
variables=(username, ),
cursor_type='fetchone')
if user is None:
cfg.ValidationError(self,
text='Incorrect username or password!',
padx=10,
pady=10)
return
# todo hash password
# VALIDATES PASSWORD
password = self.password_val.get()
if not password or password != user['password']:
cfg.ValidationError(self,
text='Incorrect username or password!',
padx=10,
pady=10)
return
# LOGS USER
self.parent.login({'id': user['id'], 'username': user['username']})
self.destroy()
def delete_recipe(self, recipe_id):
"""
Deletes recipe from DB and refreshes tree elements
:param recipe_id: (int) ID of recipe stored in DB
:return: None
"""
cfg.db_execute(sql='''DELETE FROM recipes WHERE id=?;''',
variables=(recipe_id, ))
query = self.parent.search.get()
if query == 'search...':
self.parent.refresh_frame()
else:
self.parent.refresh_frame(query)
self.parent.show_frame(0)
self.parent.deactivate_buttons()
self.destroy()
def add_recipe(self):
"""
Adds new recipe to DB and refreshes page
:return: None
"""
validated_data = self.validate_input()
if validated_data:
recipe_id = cfg.db_execute(
sql='''INSERT INTO recipes VALUES (NULL, ?, ?, ?);''',
variables=(validated_data[0], validated_data[1],
cfg.CURRENT_USER['id']),
cursor_type='last_id')
query = self.parent.search.get()
if query == 'search...':
self.parent.refresh_frame()
else:
self.parent.refresh_frame(query)
if self.parent.list.exists(str(recipe_id)):
self.parent.list.selection_set(str(recipe_id))
self.parent.list.focus_set()
self.parent.list.focus(str(recipe_id))
self.parent.activate_buttons()
else:
self.parent.deactivate_buttons()
self.parent.show_frame(recipe_id)
self.destroy()
def refresh_frame(self):
"""
Refreshes budget page data, deactivates buttons, inserts correct values to trees and sets actual balance
:return: None
"""
# CLEARS TREE
self.exp_tree.delete(*self.exp_tree.get_children())
self.inc_tree.delete(*self.inc_tree.get_children())
self.deactivate_buttons()
# RETRIEVES AND ADDS ACTUAL ELEMENTS TO TREES
revenues = cfg.db_execute(sql='''SELECT * FROM revenues
WHERE user_id=?
ORDER BY create_date DESC, amount DESC;''',
variables=(cfg.CURRENT_USER['id'], ))
for revenue in revenues:
if revenue['revenue_type'] == 'expense':
self.exp_tree.insert('',
'end',
values=(revenue['id'], revenue['name'],
'{:.2f}'.format(
revenue['amount']),
revenue['create_date']))
else:
self.inc_tree.insert('',
'end',
values=(revenue['id'], revenue['name'],
'{:.2f}'.format(
revenue['amount']),
revenue['create_date']))
# SETS ACTUAL BALANCE
self.balance_label['text'] = 'Current balance: {:.2f}'.format(
BudgetFrame.get_balance())
def refresh_frame(self, query=None):
"""
Refreshes cook book page data, inserts correct values to list tree
:param query: (str) data passed by user to filter list tree
:return: None
"""
# CLEARS FRAMES DICT AND LIST ELEMENTS
self.frames.clear()
self.list.delete(*self.list.get_children())
if query:
recipes = cfg.db_execute(sql='''SELECT * FROM recipes
WHERE title LIKE ? AND user_id=?
ORDER BY title COLLATE NOCASE ASC;''',
variables=('%' + query + '%',
cfg.CURRENT_USER['id']))
else:
recipes = cfg.db_execute(sql='''SELECT * FROM recipes
WHERE user_id=?
ORDER BY title COLLATE NOCASE ASC;''',
variables=(cfg.CURRENT_USER['id'], ))
# CREATES WELCOME FRAME
recipes.append({
'id':
0,
'title':
'Choose recipe',
'desc':
'To see details choose one of available recipes from list on the left'
})
# CREATES FRAMES FROM DATA FOUNDED IN DB
for recipe in recipes:
frame = Recipe(self.recipe,
self,
title=recipe['title'],
desc=recipe['desc'])
frame.grid(row=0, column=0, sticky='nsew')
self.frames[recipe['id']] = frame
# ADDS ELEMENT TO LIST
for recipe in recipes:
if recipe['id'] == 0:
continue
self.list.insert('',
'end',
iid=recipe['id'],
values=(recipe['title'].capitalize(),
recipe['desc']))
def get_balance():
budget = cfg.db_execute(
sql=
'''SELECT IFNULL(SUM(CASE WHEN revenue_type='income' THEN amount ELSE 0 END) -
SUM(CASE WHEN revenue_type='expense' THEN amount ELSE 0 END), 0) AS balance
FROM revenues
WHERE user_id=?;''',
variables=(cfg.CURRENT_USER['id'], ))
return budget[0]['balance']
def create_user(self):
"""
Saves user to DB and logs by saving user details to CURRENT_USER global variable
:return: None
"""
user = self.validate_input()
# todo hash password
if user:
user_id = cfg.db_execute(
sql='''INSERT INTO users VALUES (NULL, ?, ?);''',
variables=(user[0], user[1]),
cursor_type='lastrowid')
# LOGS USER
self.parent.login({'id': user_id, 'username': user[0]})
self.destroy()
def export_to_csv():
"""
Creates and saves csv file in selected by user directory, open file with default system application
:return: None
"""
# SHOWS ASK FOR DIRECTORY WINDOW
file_dir = filedialog.asksaveasfilename(
initialdir='/',
title='Select file',
filetypes=(('csv file', '*.csv'), ('all files', '*.*')))
if not file_dir:
return
# CREATES FILE IN SELECTED DIRECTORY
with open(file_dir, 'w', encoding='utf-8') as csv_file:
field_names = ['name', 'revenue_type', 'amount', 'create_date']
writer = csv.DictWriter(csv_file, fieldnames=field_names)
writer.writeheader()
# FINDS DATA STORED IN DB
revenues = cfg.db_execute(
sql='''SELECT name, revenue_type, amount, create_date
FROM revenues
WHERE user_id=?;''',
variables=(cfg.CURRENT_USER['id'], ))
# SAVES DATA IN FILE
for revenue in revenues:
writer.writerow(revenue)
writer.writerow({
'name': '',
'revenue_type': 'SUM',
'amount': BudgetFrame.get_balance(),
'create_date': ''
})
# OPENS SAVED FILE IN DEFAULT SYSTEM APPLICATION
if sys.platform.startswith('darwin'):
subprocess.call(('open', file_dir))
# FOR WINDOWS
elif os.name == 'nt':
os.startfile(file_dir)
return
# FOR UNIX
elif os.name == 'posix':
subprocess.call(('xdg-open', file_dir))
def validate_input(self):
"""
Validate user input
:return: tuple containing validated data, None if data was incorrect
"""
# VALIDATES USERNAME
username = self.username_val.get()
if not username:
cfg.ValidationError(self,
text='Username can\'t be empty!',
padx=10,
pady=10)
return
users = cfg.db_execute(
sql='''SELECT username FROM users WHERE username=?;''',
variables=(username, ))
if users:
cfg.ValidationError(self,
text='Username is occupied by another user',
padx=10,
pady=10)
return
# VALIDATES PASSWORDS
password = self.password_val.get()
password_conf = self.password_conf_val.get()
if not password:
cfg.ValidationError(self,
text='Password can\'t be empty!',
padx=10,
pady=10)
return
if password != password_conf:
cfg.ValidationError(self,
text='Passwords do not match!',
padx=10,
pady=10)
return
return username, password