def do_job(self):
"""
SELECT *, coalesce(end_time,now)-start_time
FROM task t, subject s
WHERE t.subject_id=s.id AND start_time > GetDate() - @days
ORDER BY start_time
:return:
"""
session = DBSession()
now = datetime.now()
time_worked = (func.julianday(func.coalesce(Task.end_time, now)) -
func.julianday(Task.start_time)) * 86400
query = session.query(Task.start_time,
func.coalesce(Task.end_time, now),
time_worked,
Subject.title,
Task.title) \
.filter(Subject.id == Task.subject_id) \
.filter(func.date(Task.start_time) > func.date('now', '-%s day' % self.args.days)) \
.order_by(Task.start_time)
print()
table = PrettyTable(['Start', 'End', 'Time', 'Subject', 'Title'])
table.align["Title"] = "l"
total_time = 0
day_total = 0
last_date = None
for row in query:
if last_date == None:
last_date = row[0].date()
if row[0].date() != last_date:
table.add_row(
['', '',
timedelta(seconds=round(day_total)), '', ''])
last_date = row[0].date()
day_total = 0
day_total += row[2]
total_time += row[2]
table.add_row([
row[0],
row[1],
timedelta(seconds=round(row[2])),
row[3],
row[4],
])
if day_total > 0:
table.add_row(
['', '', timedelta(seconds=round(day_total)), '', ''])
print(table)
print('Total Work time: %s\n' % timedelta(seconds=total_time))
def do_job(self):
"""
SELECT sum(t.end_time - t.start_time)
FOM task t
GROUP BY day(t.start_time)
WHERE start_time > GetDate() - @days
:return:
"""
session = DBSession()
daywork = func.sum(func.julianday(Task.end_time) - func.julianday(Task.start_time)) * 86400
day = func.date(Task.start_time)
query = session.query(day, daywork) \
.group_by(day) \
.filter(func.date(Task.start_time) > func.date('now', '-%s day' % self.args.days)) \
.filter(Task.end_time != None) \
.order_by(Task.start_time)
print()
table = PrettyTable(['Day', 'Work Time', 'Graph'])
table.align["Graph"] = "l"
total_hours = timedelta(0)
last_day = None
for row in query:
day = datetime.strptime(row[0], '%Y-%m-%d').date()
if last_day:
diff = (day - last_day)
for i in range(diff.days - 1):
table.add_row([last_day + timedelta(i + 1), 0, ''])
worktime = timedelta(seconds=round(row[1]))
total_hours += worktime
table.add_row([day, worktime, '#' * int(round((row[1] * 60 / 86400)))])
last_day = day
print(table)
print('Total Work time: %s\n' % total_hours)