def create_account(u: Union[str, int], p: Union[str, int], e: Union[str, int], f: str, l: str) -> None:
cursor = conn.cursor()
e = 'NULL' if e in (None, '') else e
try:
'''
Creates a new entry in the db.
Required two cases as the email field is optional
'''
if e != 'NULL':
cursor.execute(f"""
INSERT INTO users (username,passwd,email_id,first_name,last_name)
VALUES ('{u}','{p}','{e}','{f}','{l}');
""")
else:
cursor.execute(f"""
INSERT INTO users (username,passwd,email_id,first_name,last_name)
VALUES ('{u}','{p}',{e},'{f}','{l}');
""")
conn.commit()
except ConnectionError:
Popup("Error Connecting to Database.",
"Please try again or restart the program")
cursor.close()
def Add_Trans(self, particulars: str, _type: str, amount: float, date: str):
cursor = conn.cursor()
try:
cursor.execute(f"""
INSERT INTO transactions (
user_id,
username,
particulars,
exp_type,
amount,
exp_date
)
VALUES (
{self.user.user_id},
'{self.user.uname}',
'{particulars}',
'{_type}',
{amount},
"{date}"
);
""")
conn.commit()
Popup("Transaction successfully added.")
self.win.Refresh()
except SQLErrors.ProgrammingError:
PopupError("ERROR: Invalid details.\nRectify and try again.")
cursor.close()
def get_transactions(user: Union[str, int],
n: int = 10000,
start_date: str = f"{year}-{month}-1",
end_date: str = f"{year}-{month}-{day}",
asc_or_desc: str = "ASC",
orderer: str = "particulars") -> List[Tuple]:
headings = [
"Particulars",
"Type",
"Amount",
"Date"
]
cursor = conn.cursor()
where_clause_part_1 = f"username = '******'" if type(
user) is str else f"user_id = {user}"
where_clause = where_clause_part_1 + f"""
AND
exp_date BETWEEN '{start_date}' AND '{end_date}'
ORDER BY {orderer} {asc_or_desc}
"""
# <------------ Counts number of transactions falling into the requirements and returns them to the slider ----------------> #
query = f"""
SELECT COUNT(*)
FROM transactions
WHERE {where_clause};
"""
cursor.execute(query)
number_of_records = cursor.fetchone()[0]
cursor.reset()
query = f"""
SELECT particulars,exp_type,amount,exp_date
FROM transactions
WHERE {where_clause}
"""
if number_of_records < n:
limit = f" LIMIT {number_of_records};"
else:
limit = f" LIMIT {n};"
cursor.execute(query+limit)
transactions: List[Tuple] = cursor.fetchall()
print(transactions)
trans_table = Table(transactions, headings, key="table", right_click_menu=["Options", [
"Edit", "Delete"]], enable_events=True) if number_of_records != 0 else Table(["No records to display"], headings=[" "*50], key="table")
return transactions, trans_table, number_of_records
def username_used(user: str) -> bool:
cursor = conn.cursor()
cursor.execute(f"""
SELECT COUNT(username) FROM users
WHERE username = '******';
""")
user_count = cursor.fetchone()[0]
print(f"No. of users with username {user} = {user_count}")
if user_count != 0:
return True
else:
return False
def get_user_details(user: str) -> List[str]:
cursor = conn.cursor()
cursor.execute(f"""
SELECT user_id,username,passwd,email_id,first_name,last_name
FROM users
WHERE username = '******';
""")
user_details = cursor.fetchall()[0]
cursor.close()
return user_details
def get_income_and_expense(user: str) -> Tuple[float, float]:
cursor = conn.cursor()
cursor.execute(f"""
SELECT SUM(amount)
FROM transactions
WHERE
exp_date BETWEEN '{year}-{month}-01' AND '{year}-{month}-{days_in_month}'
AND exp_type = 'CR'
AND username = '******';""")
try:
income = cursor.fetchone()[0]
if income == None:
income = 0
except TypeError:
print("No records found. Setting income to None")
income = None
cursor.execute(f"""
SELECT SUM(amount)
FROM transactions
WHERE
exp_date BETWEEN '{year}-{month}-01' AND '{year}-{month}-{days_in_month}'
AND exp_type = 'DR'
AND username = '******';""")
try:
expense = cursor.fetchone()[0]
if expense == None:
expense = 0
except TypeError:
print("No records found. Setting expense to None")
expense = None
cursor.close()
return (income, expense)
def check_login_info(User: str, Pass: str) -> bool:
cursor = conn.cursor()
try:
cursor.execute(f"""
SELECT username, passwd
FROM users
WHERE username = '******' AND passwd = '{Pass}';
""")
result = cursor.fetchone()
if result not in [[], None, [()]]:
return True
else:
return False
except ConnectionError:
Popup("Error Connecting to Database.",
"Please try again or restart the program")
except SQLErrors.ProgrammingError:
PopupError("ERROR: Invalid credentials.", "Try again")
cursor.close()
[
sg.Button('Cancel'),
sg.Text(space1),
sg.Button('Create account',
button_color=('white', '#008000'))
]]
signup = sg.Window('Signup').Layout(signup_layout)
if not dash_active and event == 'Login':
# dash_active = True
print(event, values)
u_name = values['_name_']
pwd = values['_password_']
login_cursor = conn.cursor()
login_cursor.execute(f"select username,passwd from users;")
if (u_name, pwd) in login_cursor.fetchall():
login_window.Hide()
dash_active = True
# __From here begins the dashboard page__
details = conn.cursor()
details.execute(f"select * from users where username = '******'")
current_user_details = cud = details.fetchall()
'''
TODO:Change the layout such that it shows the users transaction history on login, and having the other functions in or tabs to the side. Yes, it'll be a pain in the arse, but the current layout looks hideously preposterous, you can't deny that. I'll keep this as a todo till I first figure out the functions themselve and create at least a working model of the app. (25/06/2019 15:04)
'''
dashboard_butt_col_layout = [
[
def get_graph_values(start_date: str = f"{year}-{month}-1",
end_date: str = f"{year}-{month}-{day}",
exp_type: str = "All"
):
global graph_active
cursor = conn.cursor()
q_cr = f"""
SELECT particulars,amount,DAY(exp_date)
FROM transactions
WHERE
exp_date BETWEEN "{start_date}" AND "{end_date}"
AND exp_type = "CR"
ORDER BY exp_date;
"""
q_dr = f"""
SELECT particulars,amount,DAY(exp_date)
FROM transactions
WHERE
exp_date BETWEEN "{start_date}" AND "{end_date}"
AND exp_type = "DR"
ORDER BY exp_date;
"""
def plot_graphs():
if exp_type == 'Credit':
q = q_cr
elif exp_type == 'Debit':
q = q_dr
elif exp_type == 'All':
q1 = q_cr
q2 = q_dr
x = np.arange(1, days_in_month)
plt.xticks(np.arange(1,days_in_month+1),range(1,days_in_month+1))
if exp_type in ("Credit", "Debit"):
cursor.execute(q)
points = cursor.fetchall()
x = np.array([point[2] for point in points])
y = np.array([point[1] for point in points])
plt.plot(x, y, marker = "o",label=exp_type)
plt.grid(True)
else:
# <------- Credit -------> #
cursor.execute(q1)
points_1 = cursor.fetchall()
x1 = np.array([point[2] for point in points_1]) # Dates
y1 = np.array([point[1] for point in points_1]) # Amount
cursor.reset()
# <------- Debit -------> #
cursor.execute(q2)
points_2 = cursor.fetchall()
x2 = np.array([point[2] for point in points_2])
y2 = np.array([point[1] for point in points_2])
plt.plot(x1, y1, marker="o", label="Credit")
plt.plot(x2, y2, marker="x", label="Debit")
plt.grid(True)
plt.title(f"Report for the month of {month_name}-{year}")
plt.legend()
fig = plt.gcf() # gcf -> get current figure #
fig_x, fig_y, fig_w, fig_h = fig.bbox.bounds
return fig, fig_w, fig_h
# q_all = f"""
# SELECT particulars,amount,DAY(exp_date)
# FROM transactions
# WHERE
# exp_date BETWEEN "{start_date}" AND "{end_date}"
# ORDER BY exp_date;
# """
if not graph_active:
return plot_graphs()
else:
# plt.clf()
return plot_graphs()
# graph_active = True
cursor.close()