def teardown_database_session(error):
"""Clean up the db connection at the end of request
"""
database_session = flask.g.pop("database_session", None)
if database_session is not None:
get_session().remove()
def get_team_summary_schedule(
team_id: int, start: int, end: int, period: float
) -> List[Tuple]:
"""
Get statistics on the schedules for a given team, with limits on dates, and
a period over which averages and other similar statistics are calculated.
:param team_id: The ID for the team to search for
:param start: Start week of the filter, inclusive, as an ordinal ISO week date
:param end: End week of the filter, inclusive, as an ordinal ISO week date
:returns: The summary of the schedule as a list of 3-tuples. Each tuple
contains the average number of hours worked by a given user on a given
project, the user ID, and the project ID.
"""
session = db.get_session()
results = (
session.query(func.sum(Schedule.hours) / period, User.name, Project.name)
.filter(Project.team_id == team_id)
.filter(Schedule.week >= start)
.filter(Schedule.week <= end)
.group_by(Schedule.user_id, Schedule.project_id)
.join(User)
.join(Project)
.all()
)
return results
def add_to_team(team_id: int, join_token_str: str) -> Optional[JoinToken]:
"""
Adds a given join token to the team, replacing any existing join token.
:param team_id: Team to which to add the join_token
:param join_token_str: Join token string to add to the team.
:return: Returns a JoinToken object if the team exists, or None otherwise.
"""
session = db.get_session()
team: Team = session.query(Team).filter(Team.id == team_id).one_or_none()
if not team:
return None
# This gets rid of the tokens and deletes the relationship as well
if team.join_tokens:
for join_token in team.join_tokens:
session.delete(join_token)
join_token = JoinToken(token_str=join_token_str)
team.join_tokens = [join_token]
session.add(join_token)
session.commit()
return join_token
def set_day_off(team_id: int, date: datetime.date, hours_off: int) -> bool:
"""
Set the number of hours off for a given day. If the number of hours off is
set to 0, the entry is erased from the database, if already there.
:param team_id: The ID of the team for which you're setting the day off
:param date: Date to set
:param hours_off: Number of hours off. Can be a number between 0 and 8
:return: True if there were no issues. False if either the team does not
exist, or an invalid number of hours were sent.
"""
session = db.get_session()
team = session.query(Team).filter(Team.id == team_id).one_or_none()
if not team:
return False
if hours_off < 0 or hours_off > 8:
return False
week = Week.withdate(date).toordinal()
day_off = (
session.query(DayOff)
.filter(DayOff.date == date)
.filter(DayOff.team_id == team_id)
.one_or_none()
)
if day_off:
if hours_off == 0:
session.delete(day_off)
else:
day_off.hours_off = hours_off
elif hours_off != 0:
session.add(DayOff(team=team, date=date, hours_off=hours_off, week=week))
session.commit()
return True
def get_project_by_id(id: int) -> Optional[Project]:
"""
Return a project given the ID (database unique key).
:param id: The ID to search with.
:return: Returns Project with given ID, or None if it does not exist.
"""
session = db.get_session()
return session.query(Project).filter(Project.id == id).one_or_none()
def set_owner(team_id: int, owner_id: int) -> Optional[Team]:
session = db.get_session()
team = session.query(Team).filter(Team.id == team_id).one_or_none()
owner = session.query(User).filter(User.id == owner_id).one_or_none()
if not team or not owner:
return None
team.owner = owner
session.commit()
return team
def create(name: str, owner_id: int) -> Optional[Team]:
session = db.get_session()
owner = session.query(User).filter(User.id == owner_id).one_or_none()
if not owner:
return None
team = Team(name=name, owner=owner, users=[owner])
session.add(team)
session.commit()
set_team(owner.id, team.id)
return team
def save_token(*, user_id: int, token: str) -> bool:
session = db.get_session()
user = session.query(User).filter(User.id == user_id).one_or_none()
# If user does not exist, do not save token and return False as error
if not user:
return False
session.add(Token(token_str=token, user=user))
session.commit()
return True
def get_project_schedules(project_id: int) -> Dict[WeekUser, Schedule]:
"""
Returns all schedules for a given project.
:param project_id: The ID of the project for which to get schedules.
:returns: A dictionary of the schedules of a given project, key-ed by
user-week pairs.
"""
session = db.get_session()
schedules = session.query(Schedule).filter(Schedule.project_id == project_id).all()
return {WeekUser(sched.week, sched.user_id): sched for sched in schedules}
def by_team_id(team_id: int) -> Optional[JoinToken]:
"""
Returns existing join token for a given team.
:param team_id: ID of the team to look for a join token of.
:return: Returns a JoinToken object if the team exists and has one, or
None otherwise.
"""
session = db.get_session()
team: Team = session.query(Team).filter(Team.id == team_id).one_or_none()
if not team:
return None
return team.join_tokens[0] if team.join_tokens else None
def verify_token(token_str: str) -> Token:
"""Looks for a token string in the database and returns the instance associated with it from the database.
If the token was not found returns None.
Arguments:
token_str {str} -- Token string to verify
Returns:
Token -- Token
"""
token = (db.get_session().query(Token).filter(
Token.token_str == token_str).one_or_none())
return token
def team_by_join_token(join_token_str: str) -> Optional[Team]:
"""
Returns the team associated with a given join token.
:param join_token_str: The join token to search over
:return: Returns a Team if the join token exists, or None otherwise
"""
session = db.get_session()
join_token = (
session.query(JoinToken)
.filter(JoinToken.token_str == join_token_str)
.one_or_none()
)
if not join_token:
return None
return join_token.team
def get_project_week_schedule(project_id: int, week: int) -> Dict[int, Schedule]:
"""
Gets all the schedules of a given project for a given week any user who
logged hours on that project for the week.
:param project_id: The ID of the project being searched.
:param week: The week for which to search for the schedule.
:returns: The schedule for a given week and project, if one exists. None
otherwise
"""
session = db.get_session()
schedules = (
session.query(Schedule)
.filter(Schedule.project_id == project_id, Schedule.week == week)
.all()
)
return {sched.user_id: sched for sched in schedules}
def add_project(name: str, team_id: int) -> Optional[Project]:
"""
Adds a project into the DB and returns that same project.
:param name: Human-readable name of the project
:param team_id: The ID of the team this project is assigned to.
:return: The created project, or none if there was an issue.
"""
session = db.get_session()
team = session.query(Team).filter(Team.id == team_id).one_or_none()
if not team:
return None
project = Project(name=name, team=team)
session.add(project)
session.commit()
return project
def get_team_schedules(team_id: int, start: int, end: int) -> List[Schedule]:
"""
Gets all the schedules for all the projects owned by a given team, filtered
to fall between two weeks.
:param team_id: The ID of the team from which schedules are being fetched.
:param start: Start week of the filter, inclusive, as an ordinal ISO week date
:param end: End week of the filter, inclusive, as an ordinal ISO week date
:returns: A list of all schedules for said team.
"""
session = db.get_session()
return (
session.query(Schedule)
.join(Project)
.filter(Project.team_id == team_id)
.filter(Schedule.week >= start)
.filter(Schedule.week <= end)
.all()
)
def get_schedule(user_id: int, project_id: int, week: int) -> Optional[Schedule]:
"""
Returns the schedule for a given user and project in a given week, if such
a schedule exists.
:param user_id: ID of the user for which to fetch the schedule.
:param project_id: ID of the project for which to fetch the schedule.
:param week: The week for which to fetch the schedule.
:returns: The schedule if it exists, or None if it does not exist.
"""
session = db.get_session()
return (
session.query(Schedule)
.filter(
Schedule.user_id == user_id,
Schedule.project_id == project_id,
Schedule.week == week,
)
.one_or_none()
)
def set_schedule(
user_id: int, project_id: int, week: int, hours: int
) -> Optional[Schedule]:
"""
Logs the number of hours a given user plans to work on a given project for
a given week. This will override existing values if present.
:param user_id: The ID of the user that will be logged for these hours.
:param project_id: The ID of the project on which the suer will work.
:param week: The week number, where week 0 is the week staring on the 1st
of January 1AD.
:param hours: The number of hours to be logged for that week.
:returns: The schedule created if none existed for that week/project
combination, the exsting schedule if it was already present, or none if
either the user_id or project_id did not correspond to this user, or if
the user is not part of the project.
"""
session = db.get_session()
user = session.query(User).filter(User.id == user_id).one_or_none()
project = session.query(Project).filter(Project.id == project_id).one_or_none()
if not user or not project:
return None
if project not in user.team.projects:
return None
schedule = (
session.query(Schedule)
.filter(Schedule.project_id == project_id, Schedule.week == week)
.one_or_none()
)
if schedule:
return schedule
schedule = Schedule(user=user, project=project, week=week, hours=hours)
session.add(schedule)
session.commit()
return schedule
def get_days_off(
team_id: int, start_week: Optional[int] = None, end_week: Optional[int] = None
) -> List[DayOff]:
"""
Returns all the hours taken off between two week ordinals, if they are
provided.
:param team_id: ID of the team for which to fetch days off.
:param start_week: The lower bound for the days off taken, which is ignored
if it is none.
:param end_week: The lower bound for the days off taken, which is ignored if
it is none.
:return: Returns a list of days off in the date range. Any week not included
is assumed to have no hours off.
:
"""
session = db.get_session()
query = session.query(DayOff).filter(DayOff.team_id == team_id)
if start_week:
query = query.filter(DayOff.week >= start_week)
if end_week:
query = query.filter(DayOff.week <= end_week)
return query.all()
def get_user_schedules(
user_id: int, start: int, end: int
) -> Dict[WeekProject, Schedule]:
"""
Returns all the schedules for a given user by week, filtered by weeks.
:param user_id: ID of the user for which to get schedules.
:param start: The lower bound (inclusive) for the dates, or None if there is
no lower bound.
:param end: The upper bound (inclusive) for the dates, or None if there is
no upper bound.
:returns: A dictionary of schedules for the user in a, key-ed by
week-project pairs, which are enforced to be unique in the API.
"""
session = db.get_session()
schedules = (
session.query(Schedule)
.filter(Schedule.user_id == user_id)
.filter(Schedule.week >= start)
.filter(Schedule.week <= end)
.all()
)
return {WeekProject(sched.week, sched.project_id): sched for sched in schedules}
def get_from_id(team_id: int) -> Optional[Team]:
session = db.get_session()
return session.query(Team).filter(Team.id == team_id).one_or_none()
import logging
from app import settings, db
from .users_seeder import UsersSeeder
log = logging.getLogger(__name__)
Session = db.get_session(settings.DB_URI)
def database_seeder(table=None):
"""Run migrations for all tables, or specified table"""
if table == 'users':
log.info('Seeding users table')
UsersSeeder(Session).run()
def destroy_token(token: Token) -> None:
session = db.get_session()
session.delete(token)
session.commit()
def environment_ids(self):
return get_all_accessible_environment_ids_by_uid(self.id,
session=get_session())
