class UserChoices(object): def __init__(self, empty=False, inactive=False): self.empty = empty self.inactive = inactive self.session = DBSession() def __iter__(self): if self.empty: yield "", u"-- None --" query = ( self.session.query(User.id, User.name) .filter(not_(User.is_client())) .filter(User.is_active == True) .order_by(User.name) ) for id, name in query: yield str(id), name if self.inactive: yield "", " " * 8 query = ( self.session.query(User.id, User.name) .filter(User.is_client()) .filter(User.is_active == False) .order_by(User.name) ) for id, name in query: yield str(id), name
def tmpl_ctx(self): sprint = self.v.get("sprint") if not sprint: sprint_id = self.request.GET.get("sprint_id") sprint = Sprint.query.get(sprint_id) project = Project.query.get(sprint.project_id) sprints = ( DBSession.query(Sprint.project_id, Sprint.worked_hours, Sprint.bugs_worked_hours, Sprint.achieved_points) .filter(Sprint.project_id == sprint.project_id) .all() ) sprint.calculate_velocities(sprints) self.v["project"] = project self.v["sprint"] = sprint prev_sprint = ( DBSession.query(Sprint) .filter(Sprint.project_id == sprint.project_id) .filter(Sprint.start < sprint.start) .order_by(Sprint.start.desc()) .first() ) next_sprint = ( DBSession.query(Sprint) .filter(Sprint.project_id == sprint.project_id) .filter(Sprint.start > sprint.start) .order_by(Sprint.start.asc()) .first() ) return dict(project=project, sprint=sprint, prev_sprint=prev_sprint, next_sprint=next_sprint)
def __init__(self, event): today = datetime.date.today() user = event['request'].user self.arrival = None if not user: return row = DBSession.query('ts').from_statement(""" SELECT MIN(p.ts) as "ts" FROM presence_entry p WHERE DATE(p.ts) = :today AND p.user_id = :user_id """).params(today=today, user_id=user.id).first() if not (row and row.ts): return arrival = row.ts now = datetime.datetime.now() since_morning_hours = float((now - arrival).seconds) / 3600 self.present = since_morning_hours noted = 0.0 row = DBSession.query('time').from_statement(""" SELECT COALESCE(SUM(t.time), 0.0) as "time" FROM time_entry t WHERE t.date = :today AND t.user_id = :user_id AND t.deleted = FALSE """).params(user_id=user.id, today=today).first() if row: noted = row.time self.noted = noted self.remaining = since_morning_hours - noted self.arrival = arrival
def migrate(config_path): from intranet3.models import DBSession, Sprint, SprintBoard import json def migrate_object(obj): board_json = obj.board if not board_json: return board = json.loads(board_json) if not isinstance(board, list): return board = { "board": board, "colors": [], } obj.board = json.dumps(board) for sprint_board in DBSession.query(SprintBoard): migrate_object(sprint_board) for sprint in DBSession.query(Sprint): migrate_object(sprint) transaction.commit()
def get(self): check_role = self.request.GET.get('role') if not check_role or check_role == 'None': pivot_q = DBSession.query(User.id, User.name, User.start_work, User.stop_work, User.roles)\ .filter(User.is_not_client())\ .filter(User.is_active==True)\ .order_by(User.name) else: pivot_q = DBSession.query(User.id, User.name, User.start_work, User.stop_work, User.roles)\ .filter(User.is_not_client())\ .filter(User.is_active==True)\ .filter(User.roles.op('&&')('{%s}'%(check_role)))\ .order_by(User.name) users = {} for s in pivot_q: if s.start_work: users.setdefault(s.start_work.year, [0]*24)[s.start_work.month-1] += 1 if s.stop_work: users.setdefault(s.stop_work.year, [0]*24)[s.stop_work.month-1+12] += 1 role = User.ROLES return dict( start=users, roles=role, check=check_role, quarters_sum=self._quarters_sum, link_with_year=self._create_link_with_year, link_with_month=self._create_link_with_month, link_with_quarter=self._create_link_with_quarter, )
def tmpl_ctx(self): sprint = self.v.get('sprint') if not sprint: sprint_id = self.request.GET.get('sprint_id') sprint = Sprint.query.get(sprint_id) project = Project.query.get(sprint.project_id) sprint.calculate_velocities() self.v['project'] = project self.v['sprint'] = sprint prev_sprint = DBSession.query(Sprint)\ .filter(Sprint.project_id == sprint.project_id)\ .filter(Sprint.start < sprint.start)\ .order_by(Sprint.start.desc()).first() next_sprint = DBSession.query(Sprint) \ .filter(Sprint.project_id == sprint.project_id) \ .filter(Sprint.start > sprint.start) \ .order_by(Sprint.start.asc()).first() return dict( project=project, sprint=sprint, prev_sprint=prev_sprint, next_sprint=next_sprint, )
def get(self): check_role = self.request.GET.get('role') if not check_role or check_role == 'None': pivot_q = DBSession.query(User.id, User.name, User.start_work, User.stop_work, User.roles)\ .filter(User.is_not_client())\ .filter(User.is_active==True)\ .order_by(User.name) else: pivot_q = DBSession.query(User.id, User.name, User.start_work, User.stop_work, User.roles)\ .filter(User.is_not_client())\ .filter(User.is_active==True)\ .filter(User.roles.op('&&')('{%s}'%(check_role)))\ .order_by(User.name) users = {} for s in pivot_q: if s.start_work: users.setdefault(s.start_work.year, [0] * 24)[s.start_work.month - 1] += 1 if s.stop_work: users.setdefault(s.stop_work.year, [0] * 24)[s.stop_work.month - 1 + 12] += 1 role = User.ROLES return dict( start=users, roles=role, check=check_role, quarters_sum=self._quarters_sum, link_with_year=self._create_link_with_year, link_with_month=self._create_link_with_month, link_with_quarter=self._create_link_with_quarter, )
def add_time(cls, orig_bugs, sprint=None): """ @param orig_bugs: list of bugs Add times to bugs, can be used inside and outside the class. """ bugs = dict(((bug.id, bug.project_id), bug) for bug in orig_bugs if bug.project) where_condition = '(t.ticket_id = :bug_id_%s AND t.project_id = :project_id_%s)' conditions = [] params = {} i = 1 for bug in orig_bugs: bug.time = 0.0 bug.sprint_time = 0.0 for bug_id, project_id in bugs: conditions.append(where_condition % (i, i)) params['bug_id_%s' % i] = bug_id params['project_id_%s' % i] = project_id i += 1 if not conditions: return orig_bugs # short circuit to avoid useless (and incorrect) SQL query condition = ' OR '.join(conditions) sql = """ SELECT t.ticket_id as "ticket_id", t.project_id as "project_id", SUM(t.time) as "time" FROM time_entry t WHERE t.deleted = FALSE AND (%s) GROUP BY t.ticket_id, t.project_id """ % (condition, ) query = DBSession.query('ticket_id', 'project_id', 'time').from_statement(sql).params(**params) for bug_id, project_id, time in query: bug = bugs[(bug_id, project_id)] bug.time = time points = bug.scrum.points or 0.0 velocity = ((points / time) * 8.0) if time else 0.0 bug.scrum.velocity = velocity if sprint: params['sprint_start'] = sprint.start params['sprint_end'] = sprint.end sql = """ SELECT t.ticket_id as "ticket_id", t.project_id as "project_id", SUM(t.time) as "time" FROM time_entry t WHERE t.deleted = FALSE AND (%s) AND t.date >= :sprint_start AND t.date <= :sprint_end GROUP BY t.ticket_id, t.project_id """ % (condition, ) query = DBSession.query('ticket_id', 'project_id', 'time').from_statement(sql).params(**params) for bug in orig_bugs: bug.sprint_time = 0.0 for bug_id, project_id, time in query: bugs[(bug_id, project_id)].sprint_time = time return orig_bugs
def get(self): today = datetime.date.today() year = self.request.GET.get('year', today.year) year = int(year) year_start = datetime.date(year, 1, 1) year_end = datetime.date(year, 12, 31) if year == today.year: # if this is current year we calculate hours only to yesterday year_end = today-oneday pivot_q = DBSession.query('id', 'name', 'color', 'date', 'time').from_statement(""" SELECT c.id as id, c.name as name, c.color as color, date_trunc('month', t.date) as date, SUM(t.time) as time FROM time_entry t, project p, client c WHERE t.project_id = p.id AND p.client_id = c.id AND t.date >= :year_start AND t.date <= :year_end AND t.deleted = false GROUP BY c.id, c.name, c.color, date_trunc('month', t.date) """).params(year_start=year_start, year_end=year_end) pivot = {} for p in pivot_q: pivot.setdefault((p.id, p.name, p.color), [0]*12)[p.date.month-1] = int(round(p.time)) pivot = sorted(pivot.iteritems(), key=lambda p: p[0][1]) stats_q = DBSession.query('date', 'time').from_statement(""" SELECT date_trunc('month', t.date) as date, SUM(t.time) as time FROM time_entry t WHERE t.deleted = false GROUP BY date_trunc('month', t.date) """) stats = {} for s in stats_q: stats.setdefault(s.date.year, [0]*12)[s.date.month-1] = int(round(s.time)) # estymation of expected worked hours for current month: month_approx = None if year == today.year and today.day > 1: month_start = datetime.date(today.year, today.month, 1) day_of_week, days_in_month = calendar.monthrange(today.year, today.month) month_end = datetime.date(today.year, today.month, days_in_month) days_worked, days_left = self._get_month_days(month_start, month_end) if not days_worked: days_worked = 1 month_approx = (days_left + days_worked) / days_worked return dict( today=today, year_start=year_start, pivot=pivot, stats=stats, quarters_sum=self._quarters_sum, month_approx=month_approx, )
def get(self): date = self.request.GET.get('date') if date: date = datetime.datetime.strptime(date, '%d.%m.%Y').date() else: date = datetime.date.today() current_data_late = memcache.get(MEMCACHED_NOTIFY_KEY % date) blacklist = self.request.user.notify_blacklist if current_data_late is not None: current_data_late['blacklist'] = blacklist return current_data_late late_query = DBSession.query(User.id, User.name, Late.id, Late.late_start, Late.late_end, Late.explanation, Late.work_from_home) late_query = late_query.filter(User.id == Late.user_id)\ .filter(Late.date == date)\ .filter(Late.deleted == False)\ .order_by(User.name) absences = DBSession.query(User.id, User.name, Absence.id, Absence.date_start, Absence.date_end, Absence.remarks) absences = absences.filter(User.id == Absence.user_id)\ .filter(Absence.deleted == False)\ .filter(Absence.date_start <= date)\ .filter(Absence.date_end >= date)\ .order_by(User.name) current_data_late = dict( lates=[ dict(id=user_id, name=user_name, late_id=late_id, start=start and start.isoformat()[:5] or None, end=end and end.isoformat()[:5] or None, explanation=explanation, work_from_home=work_from_home) for user_id, user_name, late_id, start, end, explanation, work_from_home in late_query ], absences=[ dict(id=user_id, name=user_name, absence_id=absence_id, start=date_start and date_start.strftime('%d.%m.%y') or None, end=date_end and date_end.strftime('%d.%m.%y') or None, remarks=remarks) for user_id, user_name, absence_id, date_start, date_end, remarks in absences ]) memcache.add( MEMCACHED_NOTIFY_KEY % date, current_data_late, 60 * 60 * 24, ) current_data_late['blacklist'] = blacklist return current_data_late
def dispatch(self): form = ClientTimeForm(self.request.POST) if not (self.request.method == 'POST' and form.validate()): return dict(form=form) date = form.date.data month_start, month_end = self._get_start_end_of_month(date) clients = form.clients.data groupby = form.groupby.data q = DBSession.query(Client.name, Project.name, func.sum(TimeEntry.time))\ .filter(TimeEntry.project_id==Project.id)\ .filter(Project.client_id==Client.id)\ .filter(Client.id.in_(clients))\ .filter(TimeEntry.date >= month_start)\ .filter(TimeEntry.date <= month_end)\ .filter(TimeEntry.deleted==False)\ .group_by(Client.name, Project.name) data = q.all() whole_sum = sum([row[2] for row in data]) whole_sum_without_us = sum( [row[2] for row in data if row[0] != config['COMPANY_NAME']]) q = DBSession.query(func.sum(TimeEntry.time))\ .filter(TimeEntry.project_id==Project.id)\ .filter(Project.client_id==Client.id)\ .filter(Client.id != 12)\ .filter(TimeEntry.date >= month_start)\ .filter(TimeEntry.date <= month_end)\ .filter(TimeEntry.deleted==False) our_monthly_hours = q.one()[0] if groupby == 'client': results = {} for client, project, time in data: results.setdefault(client, 0) results[client] += time results = results.items() data = results data.sort(key=itemgetter(0)) # client else: data.sort(key=itemgetter(0, 1)) # client,project return dict( form=form, groupby=groupby, data=data, whole_sum=whole_sum, whole_sum_without_us=whole_sum_without_us, our_monthly_hours=our_monthly_hours, )
def __iter__(self): if self.empty: yield '', u'-- None --' query = DBSession.query(User.id, User.name).filter(User.is_not_client()).filter(User.is_active==True).order_by(User.name) for id, name in query: yield str(id), name if self.inactive: yield '', ' '*8 query = DBSession.query(User.id, User.name).filter(User.is_not_client()).filter(User.is_active==False).order_by(User.name) for id, name in query: yield str(id), name
def dispatch(self): form = ClientTimeForm(self.request.POST) if not (self.request.method == 'POST' and form.validate()): return dict(form=form) date = form.date.data month_start, month_end = self._get_start_end_of_month(date) clients = form.clients.data groupby = form.groupby.data q = DBSession.query(Client.name, Project.name, func.sum(TimeEntry.time))\ .filter(TimeEntry.project_id==Project.id)\ .filter(Project.client_id==Client.id)\ .filter(Client.id.in_(clients))\ .filter(TimeEntry.date >= month_start)\ .filter(TimeEntry.date <= month_end)\ .filter(TimeEntry.deleted==False)\ .group_by(Client.name, Project.name) data = q.all() whole_sum = sum([row[2] for row in data]) whole_sum_without_us = sum([row[2] for row in data if row[0] != config['COMPANY_NAME']]) q = DBSession.query(func.sum(TimeEntry.time))\ .filter(TimeEntry.project_id==Project.id)\ .filter(Project.client_id==Client.id)\ .filter(Client.id != 12)\ .filter(TimeEntry.date >= month_start)\ .filter(TimeEntry.date <= month_end)\ .filter(TimeEntry.deleted==False) our_monthly_hours = q.one()[0] if groupby == 'client': results = {} for client, project, time in data: results.setdefault(client, 0) results[client] += time results = results.items() data = results data.sort(key=itemgetter(0)) # client else: data.sort(key=itemgetter(0,1)) # client,project return dict( form=form, groupby=groupby, data=data, whole_sum=whole_sum, whole_sum_without_us=whole_sum_without_us, our_monthly_hours=our_monthly_hours, )
def add_time(cls, orig_bugs, sprint=None): """ @param orig_bugs: list of bugs Add times to bugs, can be used inside and outside the class. """ bugs = dict(((bug.id, bug.project_id), bug) for bug in orig_bugs if bug.project) where_condition = '(t.ticket_id = :bug_id_%s AND t.project_id = :project_id_%s)' conditions = [] params = {} i = 1 for bug_id, project_id in bugs: conditions.append(where_condition % (i, i)) params['bug_id_%s' % i] = bug_id params['project_id_%s' % i] = project_id i += 1 if not conditions: return orig_bugs # short circuit to avoid useless (and incorrect) SQL query condition = ' OR '.join(conditions) sql = """ SELECT t.ticket_id as "ticket_id", t.project_id as "project_id", SUM(t.time) as "time" FROM time_entry t WHERE t.deleted = FALSE AND (%s) GROUP BY t.ticket_id, t.project_id """ % (condition, ) query = DBSession.query('ticket_id', 'project_id', 'time').from_statement(sql).params(**params) for bug_id, project_id, time in query: bugs[(bug_id, project_id)].time = time if sprint: params['sprint_start'] = sprint.start params['sprint_end'] = sprint.end sql = """ SELECT t.ticket_id as "ticket_id", t.project_id as "project_id", SUM(t.time) as "time" FROM time_entry t WHERE t.deleted = FALSE AND (%s) AND t.date >= :sprint_start AND t.date <= :sprint_end GROUP BY t.ticket_id, t.project_id """ % (condition, ) query = DBSession.query( 'ticket_id', 'project_id', 'time').from_statement(sql).params(**params) for bug in bugs.itervalues(): bug.sprint_time = 0.0 for bug_id, project_id, time in query: bugs[(bug_id, project_id)].sprint_time = time return orig_bugs
def is_coordinator(self): ## circular import :/ from intranet3.models import Project, Client is_coordinator = DBSession.query(exists().where( or_(Client.coordinator_id == self.id, Project.coordinator_id == self.id))).scalar() return is_coordinator
def dispatch(self): project_id = self.request.GET.get('project_id') project = DBSession.query(Project).filter(Project.id==project_id).one() form = ProjectForm(self.request.POST, obj=project) # hack, when user has no permision can_edit_projects (that means that he has only scrum perms) # we do not validate the form if self.request.method == 'POST' and (not self.request.has_perm('can_edit_projects') or form.validate()): project.working_agreement = form.working_agreement.data project.definition_of_done = form.definition_of_done.data project.definition_of_ready = form.definition_of_ready.data project.continuous_integration_url = form.continuous_integration_url.data project.backlog_url = form.backlog_url.data project.status = form.status.data project.sprint_tabs = form.sprint_tabs.data if self.request.has_perm('can_edit_projects'): project.name = form.name.data coordinator_id = int(form.coordinator_id.data) if form.coordinator_id.data.isdigit() else None project.coordinator_id = coordinator_id project.tracker_id = form.tracker_id.data project.turn_off_selectors = form.turn_off_selectors.data project.project_selector = form.project_selector.data project.component_selector = form.component_selector.data project.version_selector = form.version_selector.data project.ticket_id_selector = form.ticket_id_selector.data project.active = form.active.data project.google_card = form.google_card.data project.google_wiki = form.google_wiki.data project.mailing_url = form.mailing_url.data project.status = form.status.data self.flash(self._(u"Project saved")) LOG(u"Project saved") SelectorMapping.invalidate_for(project.tracker_id) return HTTPFound(location=self.request.url_for('/project/edit', project_id=project.id)) return dict(project_id=project.id, form=form)
def get_absence(city): return DBSession.query(User.id, User.name)\ .filter(User.id == Absence.user_id)\ .filter(User.location == city)\ .filter(Absence.date_start <= date)\ .filter(Absence.date_end >= date)\ .order_by(User.name)
def get_logins_mapping(cls, tracker): """ Returns dict user login -> user object for given tracker """ creds_query = DBSession.query(cls, User).filter(cls.tracker_id == tracker.id).filter(cls.user_id == User.id) return dict((credentials.login.lower(), user) for credentials, user in creds_query)
def _annually_report(self, year): year_start = datetime.date(year, 1, 1) year_end = datetime.date(year, 12, 31) excuses_error = None config_obj = ApplicationConfig.get_current_config() query = DBSession.query('uid', 'date', 'presence').from_statement(""" SELECT p.user_id as "uid", date_trunc('day', p.ts) as "date", MIN(p.ts) as "presence" FROM presence_entry p WHERE p.ts >= :year_start AND p.ts <= :year_end GROUP BY p.user_id, date_trunc('day', p.ts) """).params(year_start=year_start, year_end=year_end) data = query.all() users = User.query.filter(User.is_active==True)\ .filter(User.is_not_client())\ .filter(User.is_not_freelancer())\ .order_by(User.name).all() _excuses = excuses.presence() data = self._group_by_user_monthly(data, _excuses) stats = self._prepare_statistics(data) return dict( data=data, users=users, stats=stats, today=datetime.datetime.today(), year_start=year_start, deltazero=deltazero, late_limit=config_obj.monthly_late_limit, excuses_error=excuses_error, )
def get(self): user_id = self.request.GET.get('user_id') month_start, month_end = self._get_month() user = User.query.filter(User.id == user_id).one() query = DBSession.query('date', 'presence', 'leave').from_statement(""" SELECT date_trunc('day', p.ts) as "date", MIN(p.ts) as "presence", MAX(p.ts) as "leave" FROM presence_entry p WHERE p.ts >= :month_start AND date_trunc('day', p.ts) <= :month_end AND p.user_id = :user_id GROUP BY date_trunc('day', p.ts) """).params(month_start=month_start, month_end=month_end, user_id=user_id) data = query.all() holidays = Holiday.all() data = self._group_by_user_monthly(data, user.id) return dict( data=data, user=user, is_holiday=lambda date: Holiday.is_holiday(date, holidays=holidays ), month_start=month_start, month_end=month_end, next_month=next_month(month_start), prev_month=previous_month(month_start), deltazero=deltazero, datetime=datetime, )
def get(self): project_id = self.request.GET.get('project_id') project = m.Project.query.get(project_id) resolved, show_all_bugs, show_all_projects, sort_by_date = self._get_params( ) projects = DBSession.query( m.Client, m.Project).filter(m.Client.id == m.Project.client_id).order_by( m.Client.name, m.Project.name) tracker = project.tracker bugs = Bugs(self.request).get_project(project, resolved) if bugs is not None: # will not be able to fetch, because user does not have credentials for this tracker bugs = sorted(bugs, cmp=h.sorting_by_severity) return dict(unable=False, bugs=bugs, projects=projects, project=project, tracker=tracker, resolved=resolved) else: # fetch is possible return dict(unable=True, projects=projects, project=project, tracker=tracker, resolved=resolved)
def __iter__(self): if self.empty: yield '', u'-- None --' query = DBSession.query(User.id, User.name).filter(User.is_not_client()).filter( User.is_active == True).order_by(User.name) for id, name in query: yield str(id), name if self.inactive: yield '', ' ' * 8 query = DBSession.query(User.id, User.name).filter( User.is_not_client()).filter(User.is_active == False).order_by( User.name) for id, name in query: yield str(id), name
def post(self): rows = DBSession.query('cid', 'cname', 'uid', 'uemail', 'date', 'time').from_statement(""" SELECT c.id as cid, c.name as cname, u.id as uid, u.email as uemail, date_trunc('month', t.date) as date, SUM(t.time) as time FROM time_entry t, project p, client c, "user" u WHERE t.project_id = p.id AND p.client_id = c.id AND t.user_id = u.id AND t.deleted = false GROUP BY c.id, c.name, u.id, u.email, date_trunc('month', t.date) ORDER BY date_trunc('month', t.date) """).all() monthly = h.groupby(rows, lambda row: (row[2], row[-2]), lambda row: row[5]) rows = [( row[1], row[3], row[5], row[4].strftime('%Y-%m-%d'), sum(monthly[row[2], row[-2]]), ) for row in rows] stream = self._to_excel(rows) response = Response( content_type='application/vnd.ms-excel', app_iter=stream, ) response.headers['Cache-Control'] = 'no-cache' response.content_disposition = 'attachment; filename="report-%s.xls"' % datetime.datetime.now().strftime('%d-%m-%Y--%H-%M-%S') return response
def add_time(user_id, date, bug_id, project_id, hours, subject): # try finding existing entry for this bug bug_id = str(bug_id) entry = DBSession.query(TimeEntry).filter(TimeEntry.user_id==user_id) \ .filter(TimeEntry.date==date.date()) \ .filter(TimeEntry.ticket_id==bug_id) \ .filter(TimeEntry.project_id==project_id).first() if not entry: # create new entry entry = TimeEntry( user_id=user_id, date=date.date(), time=hours, description=subject, ticket_id=bug_id, project_id = project_id, modified_ts=date ) LOG(u'Adding new entry') else: # update existing entry if not entry.frozen: entry.time += hours entry.modified_ts = date # TODO: this might remove an already existing lateness LOG(u'Updating existing entry') return entry else: LOG(u'Omission of an existing entry because it is frozen') return entry
def calculate_velocities(self): self.velocity = \ 8.0 * self.achieved_points / self.worked_hours \ if self.worked_hours else 0.0 self.story_velocity = \ 8.0 * self.achieved_points / self.bugs_worked_hours \ if self.bugs_worked_hours else 0.0 sprint_velocities = DBSession.query( Sprint.velocity, Sprint.story_velocity, ).filter(Sprint.project_id == self.project_id) \ .filter(Sprint.id != self.id) \ .all() sprint_velocities.append((self.velocity, self.story_velocity)) velocities, story_velocities = zip(*sprint_velocities) self.velocity_mean = float(sum(velocities)) / len(velocities) self.story_velocity_mean = \ float(sum(story_velocities)) / len(story_velocities) DBSession.add(self)
def _prepare_uber_query(self, start_date, end_date, projects, users, ticket_choice, bug_id=None): uber_query = DBSession.query( Client, Project, TimeEntry.ticket_id, User, Tracker, TimeEntry.description, TimeEntry.date, TimeEntry.time ) uber_query = uber_query.filter(TimeEntry.user_id==User.id)\ .filter(TimeEntry.project_id==Project.id)\ .filter(Project.tracker_id==Tracker.id)\ .filter(Project.client_id==Client.id) if bug_id: uber_query = uber_query.filter(TimeEntry.ticket_id==bug_id) if projects: uber_query = uber_query.filter(TimeEntry.project_id.in_(projects)) uber_query = uber_query.filter(TimeEntry.date>=start_date)\ .filter(TimeEntry.date<=end_date)\ .filter(TimeEntry.deleted==False) if ticket_choice == 'without_bug_only': uber_query = uber_query.filter(TimeEntry.ticket_id=='') elif ticket_choice == 'meetings_only': meeting_ids = [t['value'] for t in TimeEntryForm.PREDEFINED_TICKET_IDS] uber_query = uber_query.filter(TimeEntry.ticket_id.in_(meeting_ids)) if users and users != ([],): uber_query = uber_query.filter(User.id.in_(users)) uber_query = uber_query.order_by( Client.name, Project.name, TimeEntry.ticket_id, User.name ) return uber_query
def get(self): user_id = self.request.GET.get('user_id') month_start, month_end = self._get_month() user = User.query.filter(User.id == user_id).one() query = DBSession.query('date', 'incorrect_count').from_statement(""" SELECT date, COUNT(date) as incorrect_count FROM time_entry s WHERE DATE(s.modified_ts) > s.date AND s.user_id = :user_id AND s.date >= :month_start AND s.date <= :month_end GROUP BY date """).params(month_start=month_start, month_end=month_end, user_id=user.id) data = query.all() data = self._group_by_user_monthly(data, user.id) holidays = Holiday.all() return dict( data=data, user=user, is_holiday=lambda date: Holiday.is_holiday(date, holidays=holidays ), month_start=month_start, month_end=month_end, next_month=next_month(month_start), prev_month=previous_month(month_start), datetime=datetime, )
def get_info(self): entries, sum_worked_hours, sum_bugs_worked_hours = self.get_worked_hours() points_achieved = self.board.points_achieved points = self.board.points total_hours = sum_worked_hours total_bugs_hours = sum_bugs_worked_hours users = [] if self.sprint.team_id: users = DBSession.query(User)\ .filter(User.id.in_(self.sprint.team.users))\ .filter(User.is_active==True)\ .order_by(User.name).all() result = dict( start=self.sprint.start.strftime('%Y-%m-%d'), end=self.sprint.end.strftime('%Y-%m-%d'), days_remaining=h.get_working_days(datetime.date.today(), self.sprint.end), total_bugs = len(self.board.bugs), users=users, ) self.sprint.commited_points = points self.sprint.achieved_points = points_achieved self.sprint.worked_hours = total_hours self.sprint.bugs_worked_hours = total_bugs_hours return result
def get(self): user_id = self.request.GET.get('user_id') month_start, month_end = self._get_month() user = User.query.filter(User.id==user_id).one() query = DBSession.query('date', 'presence', 'leave').from_statement(""" SELECT date_trunc('day', p.ts) as "date", MIN(p.ts) as "presence", MAX(p.ts) as "leave" FROM presence_entry p WHERE p.ts >= :month_start AND date_trunc('day', p.ts) <= :month_end AND p.user_id = :user_id GROUP BY date_trunc('day', p.ts) """).params(month_start=month_start, month_end=month_end, user_id=user_id) data = query.all() holidays = Holiday.all() data = self._group_by_user_monthly(data, user.id) return dict( data=data, user=user, is_holiday=lambda date: Holiday.is_holiday(date, holidays=holidays), month_start=month_start, month_end=month_end, next_month=next_month(month_start), prev_month=previous_month(month_start), deltazero=deltazero, datetime=datetime, )
def post(self): rows = DBSession.query('cid', 'cname', 'uid', 'uemail', 'date', 'time').from_statement(""" SELECT c.id as cid, c.name as cname, u.id as uid, u.email as uemail, date_trunc('month', t.date) as date, SUM(t.time) as time FROM time_entry t, project p, client c, "user" u WHERE t.project_id = p.id AND p.client_id = c.id AND t.user_id = u.id AND t.deleted = false GROUP BY c.id, c.name, u.id, u.email, date_trunc('month', t.date) ORDER BY date_trunc('month', t.date) """).all() monthly = h.groupby(rows, lambda row: (row[2], row[-2]), lambda row: row[5]) rows = [( row[1], row[3], row[5], row[4].strftime('%Y-%m-%d'), sum(monthly[row[2], row[-2]]), ) for row in rows] stream = self._to_excel(rows) response = Response( content_type='application/vnd.ms-excel', app_iter=stream, ) response.headers['Cache-Control'] = 'no-cache' response.content_disposition = 'attachment; filename="report-%s.xls"' % datetime.datetime.now( ).strftime('%d-%m-%Y--%H-%M-%S') return response
def get(self): user_id = self.request.GET.get('user_id') month_start, month_end = self._get_month() user = User.query.filter(User.id==user_id).one() query = DBSession.query('date', 'incorrect_count').from_statement(""" SELECT date, COUNT(date) as incorrect_count FROM time_entry s WHERE DATE(s.modified_ts) > s.date AND s.user_id = :user_id AND s.date >= :month_start AND s.date <= :month_end GROUP BY date """).params(month_start=month_start, month_end=month_end, user_id=user.id) data = query.all() data = self._group_by_user_monthly(data, user.id) holidays = Holiday.all() return dict( data=data, user=user, is_holiday=lambda date: Holiday.is_holiday(date, holidays=holidays), month_start=month_start, month_end=month_end, next_month=next_month(month_start), prev_month=previous_month(month_start), datetime=datetime, )
def _get_data(self): entries = DBSession.query('user_id', 'email', 'month', 'time').from_statement(""" SELECT u.email as "email", u.id as "user_id", date_trunc('month', t.date) as "month", COALESCE(SUM(t.time), 0) as "time" FROM time_entry t, "user" u WHERE t.user_id = u.id AND t.deleted = FALSE AND NOT ( u.groups @> '{"freelancer"}' ) AND u.is_active = true AND (u.start_full_time_work IS NOT NULL AND t.date >= u.start_full_time_work AND t.date >= :date_start) AND t.date <= :date_end GROUP BY u.id, u.email, date_trunc('month', t.date) ORDER BY "month", "email" """).params(date_start=self.start, date_end=self.end) # entries: # (user_id, user_email) -> [(month, hours_worked), (month, hours_worked), (month, hours), ...] entries = h.groupby( entries, lambda row: (row[0], row[1]), lambda row: (row[2], row[3]), ) first_day_of_work = self._get_first_day_of_work() undertime_users = {} expected_users = {} users = [] for (user_id, user), hours in entries.iteritems(): months = [time for month, time in hours] empty_months = len(self.months) - len(months) months = [0] * empty_months + months quarters = sum(months[0:3]), sum(months[3:]) expected_q, expected_m = self._get_expected( first_day_of_work[user]) if expected_q[0] > quarters[0] or expected_q[1] > quarters[1]: if user not in (config['MANAGER_EMAIL'], ): users.append((user_id, user)) undertime_users[user] = quarters, months expected_users[user] = expected_q, expected_m users = sorted(users, key=lambda u: u[1]) return dict( users=users, data=undertime_users, expected=expected_users, )
def action(self): if Holiday.is_holiday(datetime.date.today()): LOG(u"Skipping missing hours reminder, because it's a holiday") return Response(self._(u"Won't remind")) LOG(u"Starting missing hours reminder") today = datetime.date.today() entries = DBSession.query('email', 'name', 'time').from_statement(""" SELECT s.email, s.name, s.time FROM ( SELECT u.email as "email", u.name as "name", ( SELECT COALESCE(SUM(t.time), 0) FROM time_entry t WHERE t.user_id = u.id AND t.date = :today AND t.deleted = FALSE ) as "time" FROM "user" u WHERE NOT u.groups @> '{client}' AND u.is_active ) as s WHERE s.time < :min_hours """).params(today=today, min_hours=MIN_HOURS) for email, name, hours in entries: self._remind_missing_hours(email, name, hours, today) LOG(u"Ending resolved bugs reminder") return Response(self._(u'Reminded everybody'))
def make_admin(config_path): from intranet3.models import DBSession, Base from intranet3.models import User user_login = sys.argv[-1] if len(sys.argv) < 4: print u"Provide user login" return session = DBSession() user = session.query(User).filter(User.email==user_login).first() if not user: print u"No such user: %s" % user_login return if 'admin' in user.groups: print u'Removing %s from group admin' % user.name groups = list(user.groups) groups.remove('admin') user.groups = groups else: print u'Adding %s to group admin' % user.name groups = list(user.groups) groups.append('admin') user.groups = groups session.add(user) transaction.commit()
def make_admin(config_path): from intranet3.models import DBSession, Base from intranet3.models import User user_login = sys.argv[-1] if len(sys.argv) < 4: print u"Provide user login" return session = DBSession() user = session.query(User).filter(User.email == user_login).first() if not user: print u"No such user: %s" % user_login return if 'admin' in user.groups: print u'Removing %s from group admin' % user.name groups = list(user.groups) groups.remove('admin') user.groups = groups else: print u'Adding %s to group admin' % user.name groups = list(user.groups) groups.append('admin') user.groups = groups session.add(user) transaction.commit()
def add_time(user_id, date, bug_id, project_id, hours, subject): # try finding existing entry for this bug bug_id = str(bug_id) entry = DBSession.query(TimeEntry).filter(TimeEntry.user_id==user_id) \ .filter(TimeEntry.date==date.date()) \ .filter(TimeEntry.ticket_id==bug_id) \ .filter(TimeEntry.project_id==project_id).first() if not entry: # create new entry entry = TimeEntry(user_id=user_id, date=date.date(), time=hours, description=subject, ticket_id=bug_id, project_id=project_id, modified_ts=date) LOG(u'Adding new entry') else: # update existing entry if not entry.frozen: entry.time += hours entry.modified_ts = date # TODO: this might remove an already existing lateness LOG(u'Updating existing entry') return entry else: LOG(u'Omission of an existing entry because it is frozen') return entry
class Request(request.Request): def __init__(self, *args, **kwargs): self.db_session = DBSession() #those will be added to templates context, see subscribers.py self.tmpl_ctx = {} super(Request, self).__init__(*args, **kwargs) @reify def user(self): user_id = authenticated_userid(self) if user_id: return self.db_session.query(models.User).get(user_id) def has_perm(self, perm): return has_permission(perm, self.context, self) def is_user_in_group(self, group): return self.user and group in self.user.groups @property def here(self): """The same as request.url but strips scheme + netloc""" url_parts = list(urlparse.urlparse(self.url)) url_parts = ['', ''] + url_parts[2:] result = urlparse.urlunparse(url_parts) return result
def is_coordinator(self): ## circular import :/ from intranet3.models import Project, Client is_coordinator = DBSession.query(exists().where(or_( Client.coordinator_id==self.id, Project.coordinator_id==self.id ))).scalar() return is_coordinator
def get_entries(city): return DBSession.query(User.id, User.name, func.min(PresenceEntry.ts), func.max(PresenceEntry.ts))\ .filter(User.id == PresenceEntry.user_id)\ .filter((User.location == city) | (User.location == None))\ .filter(PresenceEntry.ts >= start_date)\ .filter(PresenceEntry.ts <= end_date)\ .group_by(User.id, User.name)\ .order_by(User.name)
def get(self): resolved, show_all_bugs, show_all_projects, sort_by_date = self._get_params( ) projects = DBSession.query( m.Client, m.Project).filter(m.Client.id == m.Project.client_id).order_by( m.Client.name, m.Project.name) return dict(projects=projects, resolved=resolved)
def get(self): boards = [ board.to_dict() for board in DBSession.query(m.SprintBoard) ] return dict( boards=boards )
def get(self): sprint = self.v["sprint"] bugs = self._fetch_bugs(sprint) sw = SprintWrapper(sprint, bugs, self.request) sprints = DBSession.query(Sprint).filter(Sprint.project_id == sprint.project_id).order_by(Sprint.start.desc()) return dict(bugs=bugs, info=sw.get_info(), sprints=sprints, sprint_tabs=sw.get_tabs())
def get(self): client = self.request.user.get_client() form = SprintListFilterForm(self.request.GET, client=client) active_only = form.active_only.data limit = form.limit.data or 10 sprints = Sprint.query.order_by(Sprint.modified) if client: sprints = sprints.filter(Sprint.client_id == client.id) if form.project_id.data and form.project_id.data != 'None': project_id = int(form.project_id.data) project = Project.query.get(project_id) sprints = sprints.filter(Sprint.project_id == project_id) else: raise HTTPBadRequest all_sprints = copy.copy(sprints) velocity_chart_data = get_velocity_chart_data(all_sprints) if active_only: sprints = sprints.filter(Sprint.end >= datetime.date.today()) if limit: sprints.limit(limit) sprints = sprints.all() if sprints: stats = dict( worked_hours=sum([s.worked_hours for s in sprints]) / len(sprints), achieved=sum([s.achieved_points for s in sprints]) / len(sprints), commited=sum([s.commited_points for s in sprints]) / len(sprints), velocity=sum([s.velocity for s in sprints]) / len(sprints), ) else: stats = None all_sprints_for_velocity = DBSession.query( Sprint.project_id, Sprint.worked_hours, Sprint.bugs_worked_hours, Sprint.achieved_points ).all() for sprint in sprints: associated_sprints = [s for s in all_sprints_for_velocity if s[0]==sprint.project_id] sprint.calculate_velocities(associated_sprints) return dict( sprints=sprints, form=form, project=project, velocity_chart_data=velocity_chart_data, stats=stats, )
def _get_data(self): entries = DBSession.query('user_id', 'email', 'month', 'time').from_statement(""" SELECT u.email as "email", u.id as "user_id", date_trunc('month', t.date) as "month", COALESCE(SUM(t.time), 0) as "time" FROM time_entry t, "user" u WHERE t.user_id = u.id AND t.deleted = FALSE AND NOT ( u.groups @> '{"freelancer"}' ) AND u.is_active = true AND (u.start_full_time_work IS NOT NULL AND t.date >= u.start_full_time_work AND t.date >= :date_start) AND t.date <= :date_end GROUP BY u.id, u.email, date_trunc('month', t.date) ORDER BY "month", "email" """).params(date_start=self.start, date_end=self.end) # entries: # (user_id, user_email) -> [(month, hours_worked), (month, hours_worked), (month, hours), ...] entries = h.groupby( entries, lambda row: (row[0], row[1]), lambda row: (row[2], row[3]), ) first_day_of_work = self._get_first_day_of_work() undertime_users = {} expected_users = {} users = [] for (user_id, user), hours in entries.iteritems(): months = [ time for month, time in hours ] empty_months = len(self.months) - len(months) months = [0] * empty_months + months quarters = sum(months[0:3]), sum(months[3:]) expected_q, expected_m = self._get_expected(first_day_of_work[user]) if expected_q[0] > quarters[0] or expected_q[1] > quarters[1]: if user not in (config['MANAGER_EMAIL'],): users.append((user_id, user)) undertime_users[user] = quarters, months expected_users[user] = expected_q, expected_m users = sorted(users, key=lambda u: u[1]) return dict( users=users, data=undertime_users, expected=expected_users, )
def __iter__(self): if self.first_empty: yield '', '' * 8 query = DBSession.query(User.id, User.name)\ .filter(User.is_not_client())\ .filter(User.is_active==True)\ .order_by(User.name) for client_id, client_name in query: yield str(client_id), client_name if self.inactive: yield '', ' ' * 8 query = DBSession.query(User.id, User.name)\ .filter(User.is_not_client())\ .filter(User.is_active==False)\ .order_by(User.name) for id, name in query: yield str(id), name
def action(self): coordinators = DBSession.query(Project.coordinator_id, User.email) \ .join(User) \ .filter(Project.coordinator_id!=None) \ .group_by(Project.coordinator_id, User) \ .all() manager = DBSession.query(User) \ .filter(User.email == config['MANAGER_EMAIL']) \ .first() bugs = Bugs(self.request, manager).get_all() # Coordinators for user_id, user_email in coordinators: self._send_report(user_id, user_email, bugs) # Manager self._send_report(None, config['MANAGER_EMAIL'], bugs) return Response('ok')
def all(cls, cache=True): holidays = None if cache: holidays = memcache.get(HOLIDAYS_MEMCACHE_KEY) if holidays is None: holidays = dict([ (date[0], True) for date in DBSession.query(Holiday.date) ]) memcache.set(HOLIDAYS_MEMCACHE_KEY, holidays, HOLIDAYS_MEMCACHE_TIME) DEBUG(u"Holidays cached") return holidays
def __iter__(self): if self.first_empty: yield '', ''*8 query = DBSession.query(User.id, User.name)\ .filter(User.is_not_client())\ .filter(User.is_active==True)\ .order_by(User.name) for client_id, client_name in query: yield str(client_id), client_name if self.inactive: yield '', ' '*8 query = DBSession.query(User.id, User.name)\ .filter(User.is_not_client())\ .filter(User.is_active==False)\ .order_by(User.name) for id, name in query: yield str(id), name
def get_all(self, resolved, all_projects=True): bugs = Bugs(self.request).get_all(resolved) people = m.User.query.order_by(m.User.is_freelancer(), m.User.name)\ .filter(m.User.is_not_client())\ .filter(m.User.is_active==True) people = people.all() entries = DBSession.query(m.Client, m.Project)\ .filter(m.Client.id == m.Project.client_id)\ .filter(m.Project.tracker_id == m.Tracker.id)\ .order_by(m.Client.name, m.Project.name) grouped = defaultdict(lambda: defaultdict(lambda: 0)) client_sums = defaultdict(lambda: 0) project_sums = defaultdict(lambda: 0) people_sums = defaultdict(lambda: 0) total = 0 for bug in bugs: project = bug.project.id if bug.project else None client = bug.project.client_id if bug.project else None user = bug.reporter if resolved else bug.owner if not user or not client: continue client_sums[client] += 1 project_sums[project] += 1 people_sums[user.id] += 1 grouped[project][user.id] += 1 total += 1 def client_filter(client, project): """ Filter out projects that don't belongs to client and filter out projects without bugs unless all_project is set to True/1 """ if self.request.is_user_in_group('client'): if client.id == self.request.user.client.id: return project if project_sums[ project.id] or all_projects else None else: return project if project_sums[ project.id] or all_projects else None clients = h.groupby(entries, lambda x: x[0], lambda x: client_filter(x[0], x[1])) all_people = not self.request.is_user_in_group('client') ## for client show only employees with bugs people = [ person for person in people if people_sums[person.id] or all_people ] return bugs, grouped, people, people_sums, client_sums, project_sums, total, clients
def _hours_for_previous_months(self, date): current_month_start = datetime.date(date.year, date.month, 1) time_entries = DBSession.query('user', 'client', 'project', 'time', 'description', 'ticket_id', 'entry_date', 'entry_status').from_statement(""" SELECT u.name as "user", c.name as "client", p.name as "project", t.time as "time", t.description as "description", t.ticket_id as "ticket_id", t.date as "entry_date", t.deleted as "entry_status" FROM time_entry as t, "user" as u, client as c, project as p WHERE t.user_id = u.id AND t.project_id = p.id AND p.client_id = c.id AND DATE(t.modified_ts) = :date AND t.date < :current_month_start ORDER BY u.name, c.name, p.name """).params(current_month_start=current_month_start, date=date).all() if not time_entries: LOG(u"No time entries for previous months %s" % (date, )) return u"No time entries for previous months %s" % (date, ) output = [] tmp_user = '' for user, client, project, time, description, ticket_id, entry_date, entry_status in time_entries: if tmp_user != user: tmp_user = user output.append(u"") output.append(u"%s:" % (user, )) ticket_id = ticket_id and u"[%s] " % ticket_id or u"" status = entry_status and self._(u"[Deleted]") or u"" output.append(u"\t- [%s]%s %s / %s / %s%s %.2f h" % (entry_date, status, client, project, ticket_id, description, time)) message = u'\n'.join(output) topic = self._( u"[intranet] Report with hours added for the previous months") with mail.EmailSender() as email_sender: email_sender.send( config['MANAGER_EMAIL'], topic, message, ) LOG(u"Report with hours added for previous months - started") return message
def get_worked_hours(startDate, endDate, projects_ids): worked_hours = DBSession.query( TimeEntry.project_id, func.sum(TimeEntry.time) ) return worked_hours\ .filter(TimeEntry.project_id.in_(projects_ids))\ .filter(TimeEntry.date >= startDate)\ .filter(TimeEntry.date <= endDate)\ .group_by(TimeEntry.project_id)