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):
date = datetime.datetime.strptime(self.request.GET.get('date'), '%d.%m.%Y')
start_date = datetime.datetime.combine(date, day_start)
end_date = datetime.datetime.combine(date, day_end)
entries_p = self.session.query(User.id, User.name, func.min(PresenceEntry.ts), func.max(PresenceEntry.ts))\
.filter(User.id==PresenceEntry.user_id)\
.filter((User.location=="poznan") | (User.location==None))\
.filter(PresenceEntry.ts>=start_date)\
.filter(PresenceEntry.ts<=end_date)\
.group_by(User.id, User.name)\
.order_by(User.name)
entries_w = self.session.query(User.id, User.name, func.min(PresenceEntry.ts), func.max(PresenceEntry.ts))\
.filter(User.id==PresenceEntry.user_id)\
.filter(User.location=="wroclaw")\
.filter(PresenceEntry.ts>=start_date)\
.filter(PresenceEntry.ts<=end_date)\
.group_by(User.id, User.name)\
.order_by(User.name)
return dict(
entries_p=((user_id, user_name, start, stop, start.time() > hour_9) for (user_id, user_name, start, stop) in entries_p),
entries_w=((user_id, user_name, start, stop, start.time() > hour_9) for (user_id, user_name, start, stop) in entries_w),
date=date,
prev_date=previous_day(date),
next_date=next_day(date),
excuses=excuses.presence(),
justification=excuses.presence_status(date, self.request.user.id),
)
def get(self):
date = self.request.GET.get('date')
if date:
date = datetime.datetime.strptime(date, '%d.%m.%Y')
else:
date = datetime.date.today()
start_date = datetime.datetime.combine(date, day_start)
end_date = datetime.datetime.combine(date, day_end)
def get_entries(city):
if city in ['wroclaw', 'poznan']:
return self.session.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_lates(city):
if city in ['wroclaw', 'poznan']:
return self.session.query(User.id, User.name, Late.late_start, Late.late_end)\
.filter(User.id == Late.user_id)\
.filter(User.location == city)\
.filter(Late.date == date)\
.order_by(User.name)
def get_absence(city):
if city in ['wroclaw', 'poznan']:
return self.session.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)
return dict(
date=date,
prev_date=h.previous_day(date),
next_date=h.next_day(date),
excuses=excuses.presence(),
justification=excuses.presence_status(date, self.request.user.id),
city=[
dict(
name=u'Poznań',
entries=((user_id, user_name, start, stop, start.time() > hour_9) for (user_id, user_name, start, stop) in get_entries('poznan')),
late=get_lates('poznan'),
absence=get_absence('poznan'),
),
dict(
name=u'Wrocław',
entries=((user_id, user_name, start, stop, start.time() > hour_9) for (user_id, user_name, start, stop) in get_entries('wroclaw')),
late=get_lates('wroclaw'),
absence=get_absence('wroclaw'),
),
],
)
def get(self):
date = self.request.GET.get('date')
if date:
date = datetime.datetime.strptime(date, '%d.%m.%Y')
else:
date = datetime.date.today()
start_date = datetime.datetime.combine(date, day_start)
end_date = datetime.datetime.combine(date, day_end)
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_lates(city):
return DBSession.query(User.id, User.name, Late.late_start, Late.late_end)\
.filter(User.id == Late.user_id)\
.filter(User.location == city)\
.filter(Late.date == date)\
.order_by(User.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)
locations = []
for name, (fullname, shortcut) in self.request.user.get_locations():
presences = dict(
shortcut=shortcut,
name=fullname,
entries=((user_id, user_name, start, stop,
start.time() > hour_9)
for (user_id, user_name, start,
stop) in get_entries(name)),
late=get_lates(name),
absence=get_absence(name),
)
locations.append(presences)
return dict(
date=date,
prev_date=h.previous_day(date),
next_date=h.next_day(date),
excuses=excuses.presence(),
justification=excuses.presence_status(date, self.request.user.id),
locations=locations,
)
def get(self):
date = self.request.GET.get('date')
if date:
date = datetime.datetime.strptime(date, '%d.%m.%Y')
else:
date = datetime.date.today()
start_date = datetime.datetime.combine(date, day_start)
end_date = datetime.datetime.combine(date, day_end)
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_lates(city):
return DBSession.query(User.id, User.name, Late.late_start, Late.late_end)\
.filter(User.id == Late.user_id)\
.filter(User.location == city)\
.filter(Late.date == date)\
.order_by(User.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)
locations = []
for name, (fullname, shortcut) in self.request.user.get_locations():
presences = dict(
shortcut=shortcut,
name=fullname,
entries=((user_id, user_name, start, stop, start.time() > hour_9) for (user_id, user_name, start, stop) in get_entries(name)),
late=get_lates(name),
absence=get_absence(name),
)
locations.append(presences)
return dict(
date=date,
prev_date=h.previous_day(date),
next_date=h.next_day(date),
excuses=excuses.presence(),
justification=excuses.presence_status(date, self.request.user.id),
locations=locations,
)
def _group_by_user_monthly(self, data, user_id):
result = {}
_excuses = excuses.presence()
holidays = Holiday.all()
for date, presence, leave in data:
day_data = result.setdefault(date.day, [0, 0, deltazero])
day_data[0] = presence.strftime('%H:%M:%S')
day_data[1] = leave.strftime('%H:%M:%S')
late = presence - datetime.datetime.combine(presence.date(), am9)
late = late if late > datetime.timedelta(minutes=1) and not Holiday.is_holiday(date, holidays=holidays) else deltazero
excuse = '-'
if presence.strftime('%Y-%m-%d') in _excuses.get(user_id, {}).keys():
excuse = _excuses[user_id][presence.strftime('%Y-%m-%d')]
day_data[2] = late, excuse
return result
def _group_by_user_monthly(self, data, user_id):
result = {}
_excuses = excuses.presence()
holidays = Holiday.all()
for date, presence, leave in data:
day_data = result.setdefault(date.day, [0, 0, deltazero])
day_data[0] = presence.strftime('%H:%M:%S')
day_data[1] = leave.strftime('%H:%M:%S')
late = presence - datetime.datetime.combine(presence.date(), am9)
late = late if late > datetime.timedelta(
minutes=1) and not Holiday.is_holiday(
date, holidays=holidays) else deltazero
excuse = '-'
if presence.strftime('%Y-%m-%d') in _excuses.get(user_id,
{}).keys():
excuse = _excuses[user_id][presence.strftime('%Y-%m-%d')]
day_data[2] = late, excuse
return result
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 = self.session.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.freelancer==False)\
.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,
)