def repo_exists(repo_id, repo_name): ''' Get whether or not the repo already exists in the database. Args: repo_id: The ID of the repository repo_name: The name of the repository Returns: True if the repo exists, False otherwise. ''' # Get new database instance db = settings.getDatabase() cur = db.cursor() query = '''SELECT id FROM dev_repo WHERE id=%s and name=%s;''' data = (int(repo_id), str(repo_name)) cur.execute(query, data) exists = False for tup in cur: result = tup[0] if result is not None: result = int(tup[0]) if result == repo_id: exists = True # commit query db.commit() cur.close() return exists
def get_commit_logs(slack_id, start_date, end_date): ''' Get all the commit logs that this slack user has within the timeframe. Args: slack_id: The user's slack ID start_date: The starting date to search for end_date: The end date to stop search for Returns: A list of data in the form of (repo_name, commit_text, commit_url). ''' result = [] # Get new database instance db = settings.getDatabase() cur = db.cursor() query = '''SELECT DR.name, LDC.message, LDC.url FROM dev_repo DR, log_dev_commit LDC WHERE LDC.user_id=%s AND LDC.repo_id=DR.id AND (LDC.creation BETWEEN %s and %s) ORDER BY LDC.creation DESC;''' data = (str(slack_id), str(start_date), str(end_date)) cur.execute(query, data) for tup in cur: repo_name = str(tup[0]) commit_text = str(tup[1]) commit_url = str(tup[2]) c = (repo_name, commit_text, commit_url) result.append(c) # commit query db.commit() cur.close() return result
def get_slack_uuid(user_id, username): ''' Get the Slack uuid for the specified github user_id and username. Specifically, this allows us to see their github credentials. Args: user_id: The ID of the user username: The name of the user Returns: The UUID of the user for their slack account if it exists, else None. ''' # Get new database instance db = settings.getDatabase() cur = db.cursor() query = '''SELECT U.uuid FROM user U, git_user GU WHERE U.git_id=GU.name AND GU.id=%s and GU.name=%s;''' data = (int(user_id), str(username)) cur.execute(query, data) slack_uuid = None for tup in cur: result = tup[0] if result is not None: slack_uuid = str(result) # commit query db.commit() cur.close() return slack_uuid
def get_all_commit_logs(slack_id): ''' Get all the commit logs that this slack user has. Args: slack_id: The user's slack ID Returns: A list of data in the form of (repo_name, commit_text, commit_url). ''' result = [] # Get new database instance db = settings.getDatabase() cur = db.cursor() query = '''SELECT DR.name, LDC.message, LDC.url FROM dev_repo DR, log_dev_commit LDC WHERE LDC.user_id=%s AND LDC.repo_id=DR.id ORDER BY LDC.creation DESC;''' cur.execute(query, str(slack_id)) for tup in cur: repo_name = str(tup[0]) commit_text = str(tup[1]) commit_url = str(tup[2]) data = (repo_name, commit_text, commit_url) result.append(data) # commit query db.commit() cur.close() return result
def get_user(username): ''' Get the user information for the specified user. Args: username: The name of the user Returns: A tuple representation of the user in the form of (uuid, username, title, team, git_id, monthly_hours). ''' # Get new database instance db = settings.getDatabase() cur = db.cursor() query = '''SELECT uuid, username, title, team, git_id, bitbucket_email, monthly_hours FROM user WHERE username=%s;''' cur.execute(query, [str(username)]) user_data = None for tup in cur: if tup is not None: uuid = str(tup[0]) name = str(tup[1]) title = str(tup[2]) team_id = int(tup[3]) git_id = str(tup[4]) bitbucket_email = str(tup[5]) monthly_hours = int(tup[6]) user_data = (uuid, name, title, team_id, git_id, bitbucket_email, monthly_hours) # commit query db.commit() cur.close() return user_data
def get_git_uuid(slack_id): ''' Get the git id for the specified slack user. Args: slack_id: The UUID for the slack user Returns: The id of the user for their git account if it exists, else None. ''' # Get new database instance db = settings.getDatabase() cur = db.cursor() query = '''SELECT GU.id FROM git_user GU, user U WHERE U.git_id=GU.name AND U.uuid=%s;''' cur.execute(query, str(slack_id)) git_uuid = None for tup in cur: result = tup[0] if result is not None: git_uuid = int(result) # commit query db.commit() cur.close() return git_uuid
def create_commit_log(repo_id, user_id, commit_text, commit_url): ''' Inserts into the database the commit as a log. Args: repo_id: The ID of the repository user_id: The uuid of the user that did the commit commit_text: The text that was in the commit commit_url: The URL for more information on the commit Returns: True if the commit log was successfully created, False if something happened. ''' # Get new database instance db = settings.getDatabase() cur = db.cursor() query = '''INSERT INTO log_dev_commit (repo_id, user_id, message, url) VALUES (%s, %s, %s, %s);''' data = (int(repo_id), str(user_id), str(commit_text), str(commit_url)) cur.execute(query, data) # commit query db.commit() cur.close() return True
def get_state(uuid): ''' Gets the state of the user from the user_session table. Args: uuid: The uuid of the user Returns: The current state of that user. ''' # Get new database instance db = settings.getDatabase() cur = db.cursor() query = '''SELECT state FROM user_session WHERE user_id=%s;''' cur.execute(query, [str(uuid)]) state = None for tup in cur: state = str(tup[0]) # commit query db.commit() cur.close() return state
def get_all_users(): ''' Returns: A list of user information in the database in the form of (uuid, name). ''' # Get new database instance db = settings.getDatabase() cur = db.cursor() query = '''SELECT uuid, username FROM user;''' cur.execute(query) users = [] for tup in cur: uuid = str(tup[0]) name = str(tup[1]) user_pair = (uuid, name) users.append(user_pair) # commit query db.commit() cur.close() return users
def get_work_time(uuid): ''' Gets the work_time attribute from the user_session table, as milliseconds. Args: uuid: The uuid for that user Returns: The time this user has worked, in milliseconds. ''' # Get new database instance db = settings.getDatabase() cur = db.cursor() query = '''SELECT work_time FROM user_session WHERE user_id=%s;''' cur.execute(query, [str(uuid)]) msecs = 0 for tup in cur: msecs = int(tup[0]) # commit query db.commit() cur.close() return msecs
def get_total_worked(uuid): ''' Get the total amount of hours this user has worked THIS month. Args: uuid: The uuid of the user Returns: The number of hours, as a float, that the user worked THIS month. ''' # Get new database instance db = settings.getDatabase() cur = db.cursor() query = '''SELECT SUM(work_time)/3600000 as hours FROM log_user_session WHERE user_id=%s AND MONTH(start) = MONTH(CURRENT_DATE()) AND YEAR(start) = YEAR(CURRENT_DATE());''' cur.execute(query, [str(uuid)]) hours = 0 for tup in cur: result = tup[0] if result is not None: hours = float(tup[0]) # commit query db.commit() cur.close() return hours
def get_all_teams(): ''' Get a list of all the current teams. Returns: A list of all the current teams in the form of (team_id, team_name). ''' # Get new database instance db = settings.getDatabase() cur = db.cursor() query = '''SELECT id, name FROM team;''' cur.execute(query) teams = [] for tup in cur: team_id = int(tup[0]) team_name = str(tup[1]) team_obj = (team_id, team_name) teams.append(team_obj) # commit query db.commit() cur.close() return teams
def exists(uuid): ''' Args: uuid: The uuid for the user Returns: True if the user exists in the database, False otherwise. ''' # Get new database instance db = settings.getDatabase() cur = db.cursor() query = '''SELECT COUNT(*) FROM user WHERE uuid=%s;''' cur.execute(query, [str(uuid)]) valid = False for tup in cur: count = int(tup[0]) if count > 0: valid = True break # commit query db.commit() cur.close() return valid
def get_goal_total(uuid): ''' Get the total amount of hours this user should work in one month. Args: uuid: The uuid of the user Returns: The number of hours, as a float, that the user should work in one month. ''' # Get new database instance db = settings.getDatabase() cur = db.cursor() query = '''SELECT monthly_hours FROM user WHERE uuid=%s;''' cur.execute(query, [str(uuid)]) hours = 0 for tup in cur: hours = int(tup[0]) # commit query db.commit() cur.close() return hours
def create_user_session_log(uuid, work_time, start_time, end_time, verified=None): ''' Creates a user session log in the database for this user. Args: uuid: The uuid for that user work_time: The amount of time, in milliseconds, for this user start_time: The timestamp for when this user started end_time: The timstamp for when this user ended verified: The UUID of the user that verified this user's session ''' # Get new database instance db = settings.getDatabase() cur = db.cursor() if verified is None: query = '''INSERT INTO log_user_session (user_id, work_time, start, end) VALUES (%s, %s, %s, %s);''' data = (uuid, work_time, start_time, end_time) else: query = '''INSERT INTO log_user_session (user_id, work_time, start, end, approved) VALUES (%s, %s, %s, %s, %s);''' data = (uuid, work_time, start_time, end_time, verified) cur.execute(query, data) # commit query db.commit() cur.close()
def _get_slack_uuid(bitbucket_email): ''' Get the Slack UUID of the user with the specified bitbucket_email. Args: bitbucket_email: The email of the user Returns: The Slack UUID for the user, if one exists, otherwise None. ''' # Get new database instance db = settings.getDatabase() cur = db.cursor() query = '''SELECT uuid FROM user U WHERE bitbucket_email=%s;''' cur.execute(query, [str(bitbucket_email)]) slack_uuid = None for tup in cur: result = tup[0] if result is not None: slack_uuid = str(result) # commit query db.commit() cur.close() return slack_uuid
def create_user_session(uuid): ''' Creates a new user session in the user_session table. Args: uuid: The uuid of the user ''' # Get new database instance db = settings.getDatabase() cur = db.cursor() query = '''INSERT INTO user_session (user_id, state, work_time) VALUES (%s, 'OFFLINE', 0);''' cur.execute(query, [str(uuid)]) # commit query db.commit() cur.close()
def set_session_timestamp(uuid): ''' Sets the user's timestamp for this session as now. Args: uuid: The uuid for that user ''' # Get new database instance db = settings.getDatabase() cur = db.cursor() query = '''UPDATE user_session SET updated=CURRENT_TIMESTAMP WHERE user_id=%s''' cur.execute(query, [str(uuid)]) # commit query db.commit() cur.close()
def log_state_change(uuid, state, prev_state): ''' Args: uuid: The uuid for that user state: The current state that the user is in prev_state: The previous state for the user ''' # Get new database instance db = settings.getDatabase() cur = db.cursor() query = '''INSERT INTO log_user_state (user_id, state, prev_state) VALUES (%s, %s, %s);''' data = (str(uuid), str(state), str(prev_state)) cur.execute(query, data) # commit query db.commit() cur.close()
def set_state(uuid, state): ''' Sets the state for the user in the user_session table. Args: uuid: The uuid for that user state: The current state that the user is in ''' # Get new database instance db = settings.getDatabase() cur = db.cursor() query = '''UPDATE user_session SET state=%s WHERE user_id=%s;''' data = (state, uuid) cur.execute(query, data) # commit query db.commit() cur.close()
def verify_session_log(trans_id, verify_uuid): ''' Verifies the session log by signing the uuid to the transaction ID. Args: trans_id: The ID of the session log verify_uuid: The UUID of the user verifying ''' # Get new database instance db = settings.getDatabase() cur = db.cursor() query = '''UPDATE log_user_session SET approved=%s WHERE id=%s;''' data = (str(verify_uuid), int(trans_id)) cur.execute(query, data) # commit query db.commit() cur.close()
def update_work_time(uuid, incr): ''' Updates the work_time attribute in the user_session table. Args: uuid: The uuid for that user incr: The time increment in milliseconds for that user ''' # Get new database instance db = settings.getDatabase() cur = db.cursor() query = '''UPDATE user_session SET work_time=work_time + %s WHERE user_id=%s;''' data = (incr, uuid) cur.execute(query, data) # commit query db.commit() cur.close()
def set_work_time(uuid, time): ''' Sets the work_time attribute in the user_session table. Args: uuid: The uuid for that user time: The time, in msecs, to set the work time for this user ''' # Get new database instance db = settings.getDatabase() cur = db.cursor() query = '''UPDATE user_session SET work_time=%s WHERE user_id=%s;''' data = (int(time), str(uuid)) cur.execute(query, data) # commit query db.commit() cur.close()
def set_user_team(uuid, team): ''' Sets the user to the specified team. Args: uuid: The uuid of the user to set team: The team to set the user to ''' # Get new database instance db = settings.getDatabase() cur = db.cursor() query = '''UPDATE user SET team=%s WHERE uuid=%s;''' data = (int(team), str(uuid)) cur.execute(query, data) # commit query db.commit() cur.close()
def delete_user_session_log(uuid, timestamp): ''' Deletes the log for the user_session that has the same month/day. Only deletes one! Args: uuid: The uuid for that user timestamp: The start of that users session ''' # Get new database instance db = settings.getDatabase() cur = db.cursor() query = '''DELETE FROM log_user_session WHERE user_id=%s AND DATE(start)=%s LIMIT 1;''' data = (str(uuid), str(timestamp)) cur.execute(query, data) # commit query db.commit() cur.close()
def create_team(team_id, team_name): ''' Creates the team in the database. Args: team_id: The ID of the team team_name: The name of the team ''' # Get new database instance db = settings.getDatabase() cur = db.cursor() query = '''INSERT INTO team (id, name) VALUES (%s, %s);''' data = (int(team_id), str(team_name)) cur.execute(query, data) # commit query db.commit() cur.close()
def update_user_session_log(uuid, new_work_time, timestamp, verified): ''' Updates the work time for the user. Only updates one! Args: uuid: The uuid for that user new_work_time: The amount of time in milliseconds for this user timestamp: The start of that users session verified: The UUID of the user that verified this user's session ''' # Get new database instance db = settings.getDatabase() cur = db.cursor() query = '''UPDATE log_user_session SET work_time=%s, approved=%s WHERE user_id=%s AND DATE(start)=%s LIMIT 1;''' data = (int(new_work_time), str(verified), str(uuid), str(timestamp)) cur.execute(query, data) # commit query db.commit() cur.close()
def get_verified_session_logs(slack_id, start_date, end_date): ''' Get all the verified session logs that this slack user has within the timeframe. Args: slack_id: The user's slack ID start_date: The starting date to search for end_date: The end date to stop search for Returns: A list of data in the form of (log_id, user_id, work_time, start_date, end_date, approved). ''' result = [] # Get new database instance db = settings.getDatabase() cur = db.cursor() query = '''SELECT id, user_id, work_time, start, end, approved FROM log_user_session WHERE user_id=%s AND verified IS NOT NULL AND (start BETWEEN %s and %s) ORDER BY start DESC;''' data = (str(slack_id), str(start_date), str(end_date)) cur.execute(query, data) for tup in cur: if tup is not None: log_id = int(tup[0]) user_id = str(tup[1]) work_time = int(tup[2]) start = str(tup[3]) end = str(tup[4]) approved = str(tup[5]) sl = (log_id, user_id, work_time, start, end, approved) result.append(sl) # commit query db.commit() cur.close() return result
def get_session_timestamp(uuid): ''' Gets the user's timestamp for this session. Args: uuid: The uuid for that user ''' # Get new database instance db = settings.getDatabase() cur = db.cursor() query = '''SELECT updated FROM user_session WHERE user_id=%s''' cur.execute(query, [str(uuid)]) result = None for tup in cur: result = tup[0] # commit query db.commit() cur.close() return result
def create_user(uuid, username, title, team, git_id, bitbucket_email, monthly_hours): ''' Creates the user in the database. Args: uuid: The slack uuid for this user username: The slack username for this user title: The title for this user, or their role team: The team ID that this user belongs to git_id: The github username bitbucket_email: The email for the user's bitbucket monthly_hours: How many monthly hours this user is assigned ''' # Get new database instance db = settings.getDatabase() cur = db.cursor() query = '''INSERT INTO user (uuid, username, title, team, git_id, bitbucket_email, monthly_hours) VALUES (%s, %s, %s, %s, %s, %s, %s);''' data = (str(uuid), str(username), str(title), int(team), str(git_id), str(bitbucket_email), int(monthly_hours)) cur.execute(query, data) # commit query db.commit() cur.close()