def track():
"""
Returns a response as a str message is either checked in or checked out.
Redirects response from error if it fails.
:rtype: str
"""
req = request.form
validate_user(req)
user = DbConnector().send_query(
"SELECT id,checked_in,current_project FROM users WHERE user_id = %s",
(req['user_id'], ))
# Weird way of checking if a index exists in the database.
if len(user) > 0:
print(user)
new_checked_in_state = not user[0][1]
DbConnector().send_query(
"INSERT INTO `tracking` (`id`, `checked_in`, `user_id`, `project_id`, `timestamp`) "
+ "VALUES (NULL, %s, %s, %s, current_timestamp());",
(new_checked_in_state, user[0][0], user[0][2]))
# Updates the users current team
DbConnector().send_query(
"UPDATE users SET checked_in = %s WHERE user_id = %s",
(new_checked_in_state, req['user_id']))
if new_checked_in_state:
return "Success. Time tracking is now active."
return "Success. Time tracking is now inactive."
return "Error has occurred, Something went wrong. Please panic..."
def __init__(self):
with open('jobs/job_specs.yaml', 'r') as f:
self.job_types = yaml.load(f, Loader=yaml.SafeLoader)['type']
dbFilePath = os.path.join(os.path.dirname(os.path.abspath(__file__)),
"../db", "job_manager.sqlite")
os.makedirs(os.path.dirname(dbFilePath), exist_ok=True)
self.db = DbConnector(dbFilePath, read_only=False)
def create_project():
"""
Creates a new project for a user with the name specified in POST payload.
Returns a response as a str message, redirects response from error if it fails.
:rtype: str
"""
# loads payload as json then converts it to a dictionary
req = request.form
projectname = req['text']
projectname = projectname.strip(
) # removes the spaces infront and after the name
projectname = projectname.replace(
'"', '') # removes the quotes from the names if you try to add them
# Check if the project name isn't empty
if len(projectname) <= 0:
return "Error: please don't leave the name on blank"
# Checks if the project dosen't exist
if not project_exists(req['text']):
# If it doesnt exists it goes here
response = DbConnector().send_query(
"INSERT INTO project (id, name) VALUES (NULL, %s)",
(projectname, ))
return "The project: '" + projectname + "' created successfully"
# check if the project already exists
if project_exists(req['text']):
# If it exists it goes here
res = DbConnector().send_query(
"SELECT id FROM project WHERE name = %s", (projectname, ))
return "Error has occurred, project: '" + projectname + "' already exists"
def create_team():
"""
Creates a new team with the name of name specified in POST payload.
Returns a response as a str message, redirects response from error if it fails.
:rtype: str
"""
# loads payload as json then converts it to a dictionary
req = request.form
teamname = req['text']
teamname = teamname.strip(
) # removes the spaces infront and after the name
teamname = teamname.replace(
'"', '') # removes the quotes from the names if you try to add them
# Check if the team name isn't empty
if len(teamname) <= 0:
return "Error: please don't leave the name on blank"
# Checks if the team dosen't exist
if not team_exists(req['text']):
# If it doesnt exists it goes here
response = DbConnector().send_query(
"INSERT INTO teams (`id`, `name`) VALUES (NULL, %s)", (teamname, ))
return "The team: '" + teamname + "' created successfully"
# check if the team already exists
if team_exists(req['text']):
# If it exists it goes here
res = DbConnector().send_query("SELECT id FROM teams WHERE name = %s",
(teamname, ))
return "Error has occurred, Team: '" + teamname + "' already exists"
def get_worked_time(user_id):
"""
Gets the time worked in a day for the specific user.id as a string.
:rtype: str
"""
import datetime
response = DbConnector().send_query(
"SELECT MIN(`timestamp`) AS first_timestamp, MAX(`timestamp`) AS last_timestamp FROM `tracking` WHERE (`user_id` = %s) AND (UNIX_TIMESTAMP(CURRENT_DATE()) < UNIX_TIMESTAMP(`timestamp`)) ORDER BY id LIMIT 1",
(user_id, ))[0]
# If checked_in is true then else.
if len(response) > 0:
checked_in = DbConnector().send_query(
"SELECT `checked_in` FROM users WHERE `id` = %s",
(user_id, ))[0][0]
if response[0] == response[1]:
latest_timestamp = response[0]
# Gives a time delta object with the difference
difference = datetime.datetime.utcnow() - latest_timestamp
return _str_format_delta(
difference,
"{hours} Hours {minutes} minutes"), bool(checked_in)
else:
check_out_timestamp = response[0][4]
check_in_timestamp = response[1][4]
difference = check_out_timestamp - check_in_timestamp
return _str_format_delta(
difference,
"{hours} Hours {minutes} minutes"), bool(checked_in)
return "0 Hours 0 Minutes", False
def test_send_query(self):
"""
Tests to send a query to and checks that there is a response.
"""
connector = DbConnector()
response = connector.send_query("Show tables;")
self.assertTrue(len(response) > 0)
def test_get_connection(self):
"""
Tests that the connector get_connection returns a mysql.connector object.
"""
import mysql.connector as mysql
connector = DbConnector()
self.assertTrue(connector.get_connection() is not None)
def test_close_connection(self):
"""
Tests to check that a dbConnector returns true when trying to close a connection.
"""
connector = DbConnector()
self.assertTrue(connector.open_connection())
self.assertTrue(connector.close_connection())
def reset_run(self):
print("resetting data")
DbConnector.reset_raw_data(self)
self.run_saved = False
self.total_distance_bar.value = 0.00
self.avg_time_per_500m_label.text = "0:00:00"
self.best_run_bar.value = 0.00
self.insta_spm_label.text = "0.00"
self.total_distance_m_label.text = "0"
def channels_listall(token: str) -> dict:
"""
Provide a list of all channels (and their associated details)
:param token: user's token
:return: dictionary of list of dictionary with keys 'channel_id' and 'name'
which includes all the channels
"""
#token_operation = TokenJwt()
# check if the token is valid
if check_valid(token) is False:
raise AccessError(description='error occurred: token is not valid')
dictionary = {
'channels': [],
}
# get info for all list
db_connect = DbConnector()
db_connect.cursor()
sql = "SELECT channel_id, name FROM project.channel_data"
db_connect.execute(sql)
ret = db_connect.fetchall()
for channel in ret:
dictionary['channels'].append({
'channel_id': channel[0],
'name': channel[1]
})
# close database connection
db_connect.close()
return dictionary
def channels_create(token: str, name: str, is_public: bool) -> dict:
"""
Creates a new channel with that name that is either a public or private channel
:param token: user's token
:param name: channel's name
:param is_public: boolean expression of whether the channel is public or private
:return: the dictionary with key 'channel_id'
"""
token_operation = TokenJwt()
# check if the token is valid
if check_valid(token) is False:
raise AccessError(description='error occurred: token is not valid')
if len(name) > 20:
raise InputError(
description='error occurred: Name is more than 20 characters long')
u_id = token_operation.get_uid(token)
#
db_connect = DbConnector()
db_connect.cursor()
sql = "INSERT INTO project.channel_data(name, member, owner,is_public) VALUES (%s,%s, %s, %s);"
value = (name, [u_id], [u_id], is_public)
db_connect.execute(sql, value)
# get the channel_id
sql = "SELECT MAX(channel_id) FROM project.channel_data"
db_connect.execute(sql)
ret = db_connect.fetchone()
channel_id = ret[0]
return {
'channel_id': channel_id,
}
def update_project():
"""
Updates a specific project name with a new name specified by the POST payload.
Returns a str saying Successfully updated project with a new name of :placeholder_name: or -
error if error occurred or project did not exist.
:rtype: str
"""
# loads payload as json then converts it to a dictionary
req = request.form
updateString = req['text']
# If theres more than one citation in the string.
if updateString.count('"') > 1:
splt_char = '"'
updateString = updateString.strip()
# Checks whether it is the old or the new name that contains 2 words.
if updateString.find('"') == 0:
K = 2 # The instance of the splt_char where the string should be split
else:
K = 1
temp = updateString.split(splt_char)
# Splits at each occurence of splt_char
split_text = splt_char.join(temp[:K]), splt_char.join(temp[K:])
# Joins split_text, a tuple, with temp - split between the second instance of splt_char
old_name = split_text[0]
new_name = split_text[1]
old_name = re.sub(r'"', "", old_name)
new_name = re.sub(r'"', "", new_name)
# Removes all "
old_name = old_name.strip()
new_name = new_name.strip()
# Removes leading and trailing whitespace
else:
updateString = updateString.strip()
# Splits the string at the first space
split_text = updateString.split(" ", 1)
old_name = split_text[0]
new_name = split_text[1]
if project_exists(new_name):
# Gives an error if the name you want to change already exists
res = DbConnector().send_query(
"SELECT id FROM project WHERE name = %s", (new_name, ))
return "Error has occurred, project: '" + new_name + "' already exists"
if project_exists(old_name):
# If the project name you want to change exist...
response = DbConnector().send_query(
"UPDATE project SET name = %s WHERE name = %s",
(new_name, old_name))
# If the sql response doesn't say '1 row(s) affected.' Then something went wrong.
if response == "1 row(s) affected.":
return "Project name successfully updated from '" + old_name + "' to '" + new_name + "'"
return "Error has occurred, The specified project '" + old_name + "' does not exist"
def test_send_insert_query(self):
"""
Tests sending a insert query to the database to see what response the database gives.
:return:
"""
connector = DbConnector()
sql = "INSERT INTO teams (name) VALUES (%s)"
val = ["John"]
response = connector.send_query(sql, val)
print(response)
self.assertTrue(len(response) > 0)
self.assertEqual(response, "1 row(s) affected.")
def user_profile_setname(token, name_first, name_last):
# check if the token is valid
if check_valid(token) is False:
raise AccessError(description='error occurred: token is not valid')
if len(name_first) > 50 or len(
name_first) < 1: # check length of name_first
raise InputError(
description='error occurred: first name is not between \
1 and 50 characters inclusively in length')
if len(name_last) > 50 or len(name_last) < 1:
raise InputError(
description='error occurred: last name is not between \
1 and 50 characters inclusively in length')
# get user's u_id from token
token_operation = TokenJwt()
u_id = token_operation.get_uid(token)
db_connect = DbConnector()
db_connect.cursor()
sql = "UPDATE project.user_data SET name_first=(%s), \
name_last=(%s) WHERE u_id=(%s)"
value = (name_first, name_last, u_id)
db_connect.execute(sql, value)
db_connect.close()
return {}
def get_id(user_id):
"""
This will get the user id if a user with that specified user_id exists.
:rtype: int: the id for the specific user.
"""
res = DbConnector().send_query(
"SELECT `id` FROM users WHERE `user_id` = %s", (user_id, ))
return res[0][0]
def get_team_id(team_name):
"""
This will get the team id if a team with that specified name exists.
:rtype: int: the id for the specific team_name.
"""
res = DbConnector().send_query("SELECT `id` FROM teams WHERE `name` = %s",
(team_name, ))
return res
def get_project_id(project_name):
"""
This will get the project id if a project with that specified name exists.
:rtype: int: the id for the specific project_name.
"""
res = DbConnector().send_query(
"SELECT `id` FROM project WHERE `name` = %s", (project_name, ))
return res
def get_user_id(name):
"""
This will get the user_id if a user with that specified name exists.
:rtype: str: user_id for the specific user.
"""
res = DbConnector().send_query(
"SELECT `user_id` FROM users WHERE `name` = %s", (name, ))
return res[0][0]
def user_profile_uploadphoto(token, img_url, x_start, y_start, x_end, y_end):
token_operation = TokenJwt()
# check if the token is valid
if check_valid(token) is False:
raise AccessError(description='error occurred: token is not valid')
try:
urllib.request.urlopen(img_url)
except urllib.error.HTTPError:
raise InputError(description='error occurred: \
img_url returns an HTTP status other than 200.')
# check img is jpg
if not img_url.lower().endswith('.jpg'):
raise InputError(
description='error occurred: Image uploaded is not a JPG')
if not os.path.exists('./image'):
os.makedirs('./image')
# get user's u_id from token
u_id = token_operation.get_uid(token)
image_address = './image/' + str(u_id) + '.jpg'
urllib.request.urlretrieve(img_url, image_address)
image_object = Image.open(image_address)
width, height = image_object.size
if (int(x_start) < 0 or int(x_start) > width or int(x_end) < 0
or int(x_end) > width or int(y_start) < 0 or int(y_start) > height
or int(y_end) < 0 or int(y_end) > height):
os.remove(image_address)
raise InputError(description="x_start, y_start, x_end, y_end are not \
within the dimensions of the image at the URL")
if int(x_start) >= int(x_end) or int(y_start) >= int(y_end):
os.remove(image_address)
raise InputError(description="start value can't exceed end value")
cropped = image_object.crop((x_start, y_start, x_end, y_end))
cropped.save(image_address)
base_url = flask.request.host_url
image_url = base_url + 'image/' + str(u_id) + '.jpg'
db_connect = DbConnector()
db_connect.cursor()
sql = "UPDATE project.user_data SET profile_img_url=(%s) WHERE u_id=(%s)"
value = (image_url, u_id)
db_connect.execute(sql, value)
# close database connection
db_connect.close()
return {}
def user_create(req):
"""
This will create a user index in the database if.
returns true if user was created successfully else false.
:type req: dict containing the user data sent from slack.
:rtype: void
"""
var = [req['user_id'], parse_name(req['user_name'])]
sql = "INSERT INTO users (user_id, name) VALUES (%s, %s)"
DbConnector().send_query(sql, var)
def user_exists(user_id):
"""
This will check if a user exists by the user_id.
Will return true if the user exists, else false.
:type user_id: str
:rtype: bool true if exits else false.
"""
connector = DbConnector().send_query(
"SELECT 1 FROM users WHERE user_id = '" + user_id + "'")
return len(connector) > 0
def user_is_flockr_owner(u_id):
db_connect = DbConnector()
db_connect.cursor()
sql = "SELECT owner FROM project.flockr_data"
db_connect.execute(sql)
owner_list = db_connect.fetchone()[0]
if u_id in owner_list:
return True
return False
def join_team():
"""
This will make the POST payload specified User join a specified team that will be passed through as a argument in -
the payload.
Redirects response from error if it fails.
:rtype: str
"""
req = request.form
validate_user(req)
team_id = DbConnector().send_query("SELECT id FROM teams WHERE name = %s",
(req['text'], ))
if len(team_id) > 0:
# print("Team found with id of: " + str(team_id[0][0]))
# Updates the users current team
DbConnector().send_query(
"UPDATE users SET current_team = %s WHERE user_id = %s",
(str(team_id[0][0]), req['user_id']))
return "Team successfully joined."
return "Error has occurred. The specified team does not exist."
def main():
header_cols, raw_rows = convert_transactions_to_json()
print('header_cols:', header_cols)
print('raw_rows:', json.dumps(raw_rows[0], indent=2))
sql = "INSERT INTO transactions (date, description, amount, category, labels, notes, year) VALUES\n"
# format rows into the following format
"""
INSERT INTO transactions (date, description, amount, category, labels, notes)
(DATE, DESCRIPTION, AMOUNT, CATEGORY, LABELS, NOTES)
"""
insert_lines = [ " ('%s', '%s', %s, '%s', '%s', '%s', '%s')" % (row['date'], row['description'], row['amount'], row['category'], row['labels'], row['notes'], row['year']) for row in raw_rows ]
# build sql statements
for line in insert_lines:
line = line.replace('""', 'NULL')
sql += f"{line},\n"
# remove last ,
sql = sql[:-2]
with open('test.txt', 'w') as f:
f.write(sql)
# insert into database
db = DbConnector()
db.execute_sql(db.process_delete_results, 'DELETE FROM transactions;')
db.execute_sql(db.process_insert_results, sql)
def display_project():
"""
Returns a list of all the projects ordered by their indexes POST does not contain any arguments.
:rtype: object
"""
response = DbConnector().send_query("SELECT name FROM project")
ret_str = "These are the currently existing projects\n"
counter = 1
# loops through the list and appends the name to the output ret_str.
for row in response:
ret_str += str(counter) + ". " + row[0] + "\n"
counter += 1
return ret_str
def join_project():
"""
This will make the POST payload specified User join a specified project that will be passed through as a argument -
in the payload.
Redirects response from error if it fails.
:rtype: str
"""
req = request.form
validate_user(req)
project_id = DbConnector().send_query(
"SELECT id FROM project WHERE name = %s", (req['text'], ))
# Weird way of checking if a index exists in the database.
if len(project_id) > 0:
# print("Team found with id of: " + str(project_id[0][0]))
# Updates the users current team
DbConnector().send_query(
"UPDATE users SET current_project = %s WHERE user_id = %s",
(str(project_id[0][0]), req['user_id']))
return "Project successfully joined."
return "Error has occurred. The specified project does not exist."
def channels_list(token: str) -> dict:
"""
Provide a list of all channels (and their associated details)
that the authorised user is part of
:param token: user's token
:return: dictionary of list of dictionary with keys 'channel_id' and 'name'
which includes the channels that the user is a part of
"""
dictionary = {
'channels': [],
}
token_operation = TokenJwt()
# check if the token is valid
if check_valid(token) is False:
raise AccessError(description='error occurred: token is not valid')
# get user's u_id from token
u_id = token_operation.get_uid(token)
# get info in database
db_connect = DbConnector()
db_connect.cursor()
sql = '''
SELECT channel_id, name
FROM project.channel_data c
INNER JOIN project.user_data u ON u.u_id = ANY (c.member)
WHERE u.u_id = (%s);
'''
value = (u_id, )
db_connect.execute(sql, value)
ret = db_connect.fetchall()
for channel in ret:
dictionary["channels"].append({
'channel_id': channel[0],
'name': channel[1]
})
db_connect.close()
return dictionary
def standup_start(token, channel_id, length):
token_operation = TokenJwt()
# check if the token is valid
if check_valid(token) is False:
raise AccessError(description='error occurred: token is not valid')
# check channel_id is not valid
db_connect = DbConnector()
db_connect.cursor()
sql = "SELECT channel_id FROM project.channel_data WHERE channel_id=(%s)"
value = (channel_id, )
db_connect.execute(sql, value)
ret = db_connect.fetchone()
if ret is None:
raise InputError(
description='error occured: Channel ID is not a valid channel')
curr_time = int(time.time())
exist_active = standup_active(token, channel_id)["is_active"]
if exist_active is True:
raise InputError(description='error occurred:\
An active standup is currently running in this channel'
)
finish_time = curr_time + length
# get id from token
u_id = token_operation.get_uid(token)
sql = "INSERT INTO project.active_data (standup_uid, channel_id, time_finish) VALUES (%s,%s,%s)"
value = (u_id, channel_id, int(finish_time))
db_connect.execute(sql, value)
# time_dict = {
# 'standup_uid': u_id,
# 'channel_id': channel_id,
# 'time_finish': int(finish_time),
# 'message': ""
# }
# ACTIVE_DATA.append(time_dict)
time1 = threading.Timer(length, send_standup_message, [channel_id])
time1.start()
return {'time_finish': int(finish_time)}
def delete_project():
"""
Deletes a project from the database depending on the input written in the POST payload.
Returns a str saying Successfully deleted :placeholder_name: or error if error occurred or project did not exist.
:rtype: str
"""
# loads payload as json then converts it to a dictionary
req = request.form
projectname = req['text']
if project_exists(req['text']):
# If it exists it goes here
response = DbConnector().send_query(
"DELETE FROM project WHERE name = %s", (req['text'], ))
# If the sql response doesn't say '1 row(s) affected.' Then something went wrong.
if response == "1 row(s) affected.":
return "Project: '" + projectname + "' successfully deleted"
return "Error has occurred, The specified project '" + projectname + "' does not exist"
def user_profile(token, u_id):
# check if the token is valid
if check_valid(token) is False:
raise AccessError(description='error occurred: token is not valid')
db_connect = DbConnector()
db_connect.cursor()
sql = "SELECT email, name_first, name_last, handle, \
profile_img_url FROM project.user_data WHERE u_id=(%s);"
value = (u_id, )
db_connect.execute(sql, value)
ret = db_connect.fetchone()
# check uid is valid
if ret is None:
raise InputError(
description='error occurred: User with u_id is not a valid user')
user_dict = {} # for return
email = ret[0]
name_first = ret[1]
name_last = ret[2]
handle_str = ret[3]
profile_img_url = ret[4]
user_dict['u_id'] = u_id
user_dict['email'] = email
user_dict['name_first'] = name_first
user_dict['name_last'] = name_last
user_dict['handle_str'] = handle_str
user_dict['profile_img_url'] = profile_img_url
# close database connection
db_connect.close()
return {'user': user_dict}
