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}