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()
