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