def post(self):
rows = self.session.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_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 post(self):
args = self.request.json
year = int(args.pop('year'))
if 1999 > year > 2099:
return dict(status='nok', reason='Zły rok %s' % year)
leaves = Leave.query.filter(Leave.year==year).all()
leaves = groupby(leaves, lambda l: l.user_id)
for user_id, (mandated, remarks) in args.iteritems():
user_id = int(user_id)
try:
mandated = int(mandated)
except Exception:
user = User.query.get(user_id)
return dict(
status='nok',
reason=self._(u'Wrong hours format: ${hours} for user ${name}', hours=mandated, name=user.name)
)
if 0 > mandated or mandated > 99:
user = User.query.get(user_id)
return dict(
status='nok',
reason=self._(u'Wrong hours number: ${hours} for user ${name}', hours=mandated, name=user.name)
)
leave_obj = leaves.get(user_id)
if not leave_obj:
leave_obj = Leave(user_id=user_id, year=year)
else:
leave_obj = leave_obj[0]
leave_obj.number = mandated
leave_obj.remarks = remarks
self.session.add(leave_obj)
return dict(status='ok')
def _get_data(self):
entries = self.session.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 ( 'freelancer' = ANY(u.groups) ) 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) OR
(u.start_full_time_work IS NULL 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 post(self):
rows = self.session.query('cid', 'cname', 'uid', 'uname', 'date', 'time').from_statement("""
SELECT c.id as cid, c.name as cname, u.id as uid, u.name as uname, 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.name, 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_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 post(self):
tickets_id = self.request.POST.get('tickets_id').split(',')
trackers_id = self.request.POST.get('trackers_id').split(',')
if not tickets_id:
return dict()
tickets_id = [ int(ticket_id) for ticket_id in tickets_id if ticket_id.isdigit()]
trackers_id = [ int(tracker_id) for tracker_id in trackers_id if tracker_id.isdigit()]
data = zip(trackers_id, tickets_id)
data = groupby(data, lambda x: x[0], lambda x: x[1])
titles = self._fetch_bugs_titles(data)
return titles
def _fetch_bugs_titles(self, tracker_ticket):
query = self.session.query
config_obj = ApplicationConfig.get_current_config()
user = query(User).filter(
User.id == config_obj.hours_ticket_user_id).first()
tracker_creds = query(Tracker.id, Tracker, TrackerCredentials)\
.filter(TrackerCredentials.tracker_id==Tracker.id)\
.filter(TrackerCredentials.user_id==User.id)\
.filter(User.id==config_obj.hours_ticket_user_id)
tracker_creds = groupby(tracker_creds, lambda x: x[0], lambda x: x[1:])
fetchers = {}
for tracker_id, ticket_ids in tracker_ticket.iteritems():
ticket_ids = [ticket_id for ticket_id in ticket_ids if ticket_id]
if not tracker_id in tracker_creds:
continue
tracker, creds = tracker_creds[tracker_id][0]
mapping = {creds.login.lower(): user}
for i, portion in enumerate(
partition(ticket_ids, MAX_TICKETS_PER_REQUEST)):
fetcher = get_fetcher(tracker, creds, mapping)
fetcher.fetch_bug_titles_and_depends_on(portion)
fetchers[(tracker.name, i)] = fetcher
ticket_titles = {}
start = time()
while len(fetchers):
for key, fetcher in fetchers.iteritems():
if fetcher.isReady():
if fetcher.error:
ERROR(u"Fetcher for tracker %s failed with %r" %
(key[0], fetcher.error))
del fetchers[key]
break # continue ignoring exception - titles and depends_on will be empty
#raise fetcher.error
for bug_id, bug in fetcher.bugs.iteritems():
if type(bug['title']) == str:
ticket_titles['%s_%s' %
(fetcher.tracker.id, bug_id
)] = bug['title'].decode('utf-8')
else:
ticket_titles['%s_%s' % (fetcher.tracker.id,
bug_id)] = bug['title']
del fetchers[key]
break
else: # no fetcher is ready yet
if time() - start > MAX_TIMEOUT:
ERROR(u'%s requests timed-out' % len(fetchers))
break # continue without exception - titles and depends_on will be empty
# raise FetchException(u'%s requests timed-out for tracker %s' % (len(fetchers), tracker.name))
else:
sleep(0.1)
return ticket_titles
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 _fetch_bugs_titles(self, tracker_ticket):
query = self.session.query
config_obj = ApplicationConfig.get_current_config()
user = query(User).filter(User.id == config_obj.hours_ticket_user_id).first()
tracker_creds = (
query(Tracker.id, Tracker, TrackerCredentials)
.filter(TrackerCredentials.tracker_id == Tracker.id)
.filter(TrackerCredentials.user_id == User.id)
.filter(User.id == config_obj.hours_ticket_user_id)
)
tracker_creds = groupby(tracker_creds, lambda x: x[0], lambda x: x[1:])
fetchers = {}
for tracker_id, ticket_ids in tracker_ticket.iteritems():
ticket_ids = [ticket_id for ticket_id in ticket_ids if ticket_id]
if not tracker_id in tracker_creds:
continue
tracker, creds = tracker_creds[tracker_id][0]
mapping = {creds.login.lower(): user}
for i, portion in enumerate(partition(ticket_ids, MAX_TICKETS_PER_REQUEST)):
fetcher = get_fetcher(tracker, creds, mapping)
fetcher.fetch_bug_titles_and_depends_on(portion)
fetchers[(tracker.name, i)] = fetcher
ticket_titles = {}
start = time()
while len(fetchers):
for key, fetcher in fetchers.iteritems():
if fetcher.isReady():
if fetcher.error:
ERROR(u"Fetcher for tracker %s failed with %r" % (key[0], fetcher.error))
del fetchers[key]
break # continue ignoring exception - titles and depends_on will be empty
# raise fetcher.error
for bug_id, bug in fetcher.bugs.iteritems():
if type(bug["title"]) == str:
ticket_titles["%s_%s" % (fetcher.tracker.id, bug_id)] = bug["title"].decode("utf-8")
else:
ticket_titles["%s_%s" % (fetcher.tracker.id, bug_id)] = bug["title"]
del fetchers[key]
break
else: # no fetcher is ready yet
if time() - start > MAX_TIMEOUT:
ERROR(u"%s requests timed-out" % len(fetchers))
break # continue without exception - titles and depends_on will be empty
# raise FetchException(u'%s requests timed-out for tracker %s' % (len(fetchers), tracker.name))
else:
sleep(0.1)
return ticket_titles
def post(self):
tickets_id = self.request.POST.get('tickets_id').split(',')
trackers_id = self.request.POST.get('trackers_id').split(',')
if not tickets_id:
return dict()
tickets_id = [
int(ticket_id) for ticket_id in tickets_id if ticket_id.isdigit()
]
trackers_id = [
int(tracker_id) for tracker_id in trackers_id
if tracker_id.isdigit()
]
data = zip(trackers_id, tickets_id)
data = groupby(data, lambda x: x[0], lambda x: x[1])
titles = self._fetch_bugs_titles(data)
return titles
def get(self):
active_projects_only = self.request.GET.get('active_projects_only', '1')
clients = DBSession.query(Client, Project)\
.outerjoin(Project, Client.id==Project.client_id)
if active_projects_only == '1':
clients = clients.filter(Project.active==True)
clients = groupby(clients, keyfunc=lambda x: x[0], part=lambda x: x[1]).items()
clients = sorted(clients, key=lambda x: x[0].name)
return dict(
clients=clients,
active_projects_only=active_projects_only,
counter=Counter()
)
def get(self):
active_projects_only = self.request.GET.get('active_projects_only',
'1')
clients = self.session.query(Client, Project)\
.filter(Client.id==Project.client_id)
if active_projects_only == '1':
clients = clients.filter(Project.active == True)
clients = groupby(clients, keyfunc=lambda x: x[0],
part=lambda x: x[1]).items()
clients = sorted(clients, key=lambda x: x[0].name)
return dict(clients=clients,
active_projects_only=active_projects_only,
counter=Counter())
def get_lates(self, start, end):
lates = self.session.query(Late.user_id, Late.date, Late.explanation)
lates = lates.filter(Late.date>=start) \
.filter(Late.date<=end)
lates = h.groupby(
lates,
lambda x: x[0],
lambda x: x[1:],
)
lates_groupped = {}
for user_id, lates in lates.iteritems():
if not user_id in lates_groupped:
lates_groupped[user_id] = {}
for date, explanation in lates:
date = date.strftime('%Y-%m-%d')
lates_groupped[user_id][date] = explanation
return lates_groupped
def get_lates(self, start, end):
lates = DBSession.query(Late.user_id, Late.date, Late.explanation)
lates = lates.filter(Late.date>=start) \
.filter(Late.date<=end)
lates = h.groupby(
lates,
lambda x: x[0],
lambda x: x[1:],
)
lates_groupped = {}
for user_id, lates in lates.iteritems():
if not user_id in lates_groupped:
lates_groupped[user_id] = {}
for date, explanation in lates:
date = date.strftime('%Y-%m-%d')
lates_groupped[user_id][date] = explanation
return lates_groupped
def post(self):
args = self.request.json
year = int(args.pop('year'))
if 1999 > year > 2099:
return dict(status='nok', reason='Zły rok %s' % year)
leaves = Leave.query.filter(Leave.year == year).all()
leaves = groupby(leaves, lambda l: l.user_id)
for user_id, (mandated, remarks) in args.iteritems():
user_id = int(user_id)
try:
mandated = int(mandated)
except Exception:
user = User.query.get(user_id)
return dict(
status='nok',
reason=self._(
u'Wrong hours format: ${hours} for user ${name}',
hours=mandated,
name=user.name))
if 0 > mandated or mandated > 99:
user = User.query.get(user_id)
return dict(
status='nok',
reason=self._(
u'Wrong hours number: ${hours} for user ${name}',
hours=mandated,
name=user.name))
leave_obj = leaves.get(user_id)
if not leave_obj:
leave_obj = Leave(user_id=user_id, year=year)
else:
leave_obj = leave_obj[0]
leave_obj.number = mandated
leave_obj.remarks = remarks
self.session.add(leave_obj)
return dict(status='ok')
def get_absences(self, start, end, users):
holidays = Holiday.query \
.filter(Holiday.date >= start) \
.filter(Holiday.date <= end) \
.all()
holidays = [i.date.isoformat() for i in holidays]
absences = DBSession.query(
Absence.user_id,
Absence.date_start,
Absence.date_end,
Absence.type,
Absence.remarks,
)
absences = absences.filter(Absence.date_start>=start)\
.filter(Absence.date_start<=end)
absences = h.groupby(
absences,
lambda x: x[0],
lambda x: x[1:],
)
months = {i: 0 for i in range(1,13)}
absences_groupped = {int(u['id']): {} for u in users}
td = datetime.timedelta
for user_id, absences in absences.iteritems():
# We don't want users that aren't being shown
if not user_id in absences_groupped:
continue
for start, end, type_, remarks in absences:
if end < start: # what is this I don't even
continue
if type_ == 'l4': # no illness leaves
continue
month = start.month
tmp_start = start
while month <= end.month:
# tmp_end is last day of current month
tmp_end = datetime.date(
start.year,
month,
monthrange(start.year, month)[1]
)
if month < end.month: # is it the last month of absence?
length = (tmp_end-tmp_start).days + 1
else: # or is it not?
length = (end-tmp_start).days + 1
# Remove all holidays (weekends + holidays)
while tmp_start <= tmp_end and tmp_start <= end:
if (tmp_start.isoformat() in holidays or
tmp_start.isoweekday() > 5):
length -= 1
tmp_start += td(days=1)
months[month] += length
month += 1 # To the next month!
if month <= 12: # But only if there are any months left!
tmp_start = tmp_start.replace(month=month, day=1)
length = (end-start).days + 1
start = start.isoformat()
absences_groupped[user_id][start] = (length, type_, remarks)
return absences_groupped, months
def get(self):
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)
def get_project_worked_hours(project_id, worked_hours):
worked_hours = filter(lambda x: x[0] == project_id, worked_hours)
return worked_hours[0][1] if worked_hours else 0.0
tickets_end_date = datetime.now()
tickets_start_date = tickets_end_date.replace(day=1)
tickets_end_date = tickets_end_date.replace(day=1)
tickets_end_date += relativedelta(months=1)
tickets_end_date -= relativedelta(days=1)
tickets_last_month_end_date = tickets_start_date - timedelta(days=1)
tickets_last_month_start_date = tickets_last_month_end_date.replace(
day=1)
teams = DBSession.query(Team_m, TeamMember.user_id)\
.outerjoin(TeamMember)
team_to_project = DBSession.query(Team_m.id, Project, Client)\
.filter(Sprint.team_id==Team_m.id)\
.filter(Sprint.project_id==Project.id)\
.filter(Project.client_id==Client.id)\
.order_by(Sprint.end.desc())
teams = h.groupby(
teams,
lambda x: x[0],
lambda x: x[1]
)
team_to_project = h.groupby(
team_to_project,
lambda x: x[0],
lambda x: x[1:]
)
projects_ids = []
for value in team_to_project.values():
for projectAndClient in value:
projects_ids.append(projectAndClient[0].id)
this_month_worked_hours = get_worked_hours(
tickets_start_date, tickets_end_date, projects_ids)
last_month_worked_hours = get_worked_hours(
tickets_last_month_start_date, tickets_start_date,
projects_ids)
result = []
for team, members in teams.iteritems():
team = team.to_dict()
team['users'] = members
projects = team_to_project.get(team['id'], [])
team['projects'] = [
dict(
id=project.id,
name=project.name,
client=dict(
id=client.id,
name=client.name,
),
this_month_worked_hours=
get_project_worked_hours(project.id,
this_month_worked_hours),
last_month_worked_hours=
get_project_worked_hours(project.id,
last_month_worked_hours)
) for project, client in projects
]
result.append(team)
return dict(
teams=result
)
def get_absences(self, start, end, users):
holidays = Holiday.query \
.filter(Holiday.date >= start) \
.filter(Holiday.date <= end) \
.all()
holidays = [i.date.isoformat() for i in holidays]
absences = self.session.query(
Absence.user_id,
Absence.date_start,
Absence.date_end,
Absence.type,
Absence.remarks,
)
absences = absences.filter(Absence.date_start>=start)\
.filter(Absence.date_start<=end)
absences = h.groupby(
absences,
lambda x: x[0],
lambda x: x[1:],
)
months = {i: 0 for i in range(1, 13)}
absences_groupped = {int(u['id']): {} for u in users}
td = datetime.timedelta
for user_id, absences in absences.iteritems():
# We don't want users that aren't being shown
if not user_id in absences_groupped:
continue
for start, end, type_, remarks in absences:
if end < start: # what is this I don't even
continue
if type_ == 'l4': # no illness leaves
continue
month = start.month
tmp_start = start
while month <= end.month:
# tmp_end is last day of current month
tmp_end = datetime.date(start.year, month,
monthrange(start.year, month)[1])
if month < end.month: # is it the last month of absence?
length = (tmp_end - tmp_start).days + 1
else: # or is it not?
length = (end - tmp_start).days + 1
# Remove all holidays (weekends + holidays)
while tmp_start <= tmp_end and tmp_start <= end:
if (tmp_start.isoformat() in holidays
or tmp_start.isoweekday() > 5):
length -= 1
tmp_start += td(days=1)
months[month] += length
month += 1 # To the next month!
if month <= 12: # But only if there are any months left!
tmp_start = tmp_start.replace(month=month, day=1)
length = (end - start).days + 1
start = start.isoformat()
absences_groupped[user_id][start] = (length, type_, remarks)
return absences_groupped, months