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 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 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 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 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 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 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 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_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):
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)
