def running(db, args):
parser = OptionParser(usage='''usage: %prog running
Print all active sheets and any messages associated with them.''')
opts, args = parser.parse_args(args=args)
db.execute(u'''
select
entry.sheet,
ifnull(entry.description, '--')
from
entry
where
entry.end_time is null
order by
entry.sheet asc;
''')
cmdutil.pprint_table([(u'Timesheet', u'Description')] + db.fetchall())
def running(db, args):
parser = OptionParser(usage='''usage: %prog running
Print all active sheets and any messages associated with them.''')
opts, args = parser.parse_args(args=args)
db.execute(u'''
select
entry.sheet,
ifnull(entry.description, '--')
from
entry
where
entry.end_time is null
order by
entry.sheet asc;
''')
cmdutil.pprint_table([(u'Timesheet', u'Description')] + db.fetchall())
def running(db):
"""Show all running timesheets
Usage: t (running | active)
Print all active sheets and any messages associated with them.
"""
db.execute('''
select
entry.sheet,
ifnull(entry.description, '--')
from
entry
where
entry.end_time is null
order by
entry.sheet asc;
''')
cmdutil.pprint_table([('Timesheet', 'Description')] + db.fetchall())
def format_timebook(db, sheet, where, group='off'):
db.execute(u'''
select count(*) > 0 from entry where sheet = ?%s
''' % where, (sheet,))
if not db.fetchone()[0]:
print '(empty)'
return
displ_time = lambda t: time.strftime('%H:%M:%S', time.localtime(t))
displ_date = lambda t: time.strftime('%b %d, %Y',
time.localtime(t))
displ_total = lambda t: \
cmdutil.timedelta_hms_display(timedelta(seconds=t))
last_day = None
table = [['Day', 'Start End', 'Duration', 'Notes']]
db.execute(u'''
select
date(e.start_time, 'unixepoch', 'localtime') as day,
ifnull(sum(ifnull(e.end_time, strftime('%%s', 'now')) -
e.start_time), 0) as day_total
from
entry e
where
e.sheet = ?%s
group by
day
order by
day asc;
''' % where, (sheet,))
days = db.fetchall()
days_iter = iter(days)
db.execute(u'''
select
date(e.start_time, 'unixepoch', 'localtime') as day,
e.start_time as start,
e.end_time as end,
ifnull(e.end_time, strftime('%%s', 'now')) - e.start_time as
duration,
ifnull(e.description, '') as description
from
entry e
where
e.sheet = ?%s
order by
day asc;
''' % where, (sheet,))
entries = db.fetchall()
for i, (day, start, end, duration, description) in \
enumerate(entries):
date = displ_date(start)
diff = displ_total(duration)
if end is None:
trange = '%s -' % displ_time(start)
else:
trange = '%s - %s' % (displ_time(start), displ_time(end))
if last_day == day:
# If this row doesn't represent the first entry of the
# day, don't display anything in the day column.
table.append(['', trange, diff, description])
else:
if last_day is not None:
# Use day_total set (below) from the previous
# iteration. This is skipped the first iteration,
# since last_day is None.
table.append(['', '', displ_total(day_total), ''])
cur_day, day_total = days_iter.next()
assert day == cur_day
table.append([date, trange, diff, description])
last_day = day
db.execute(u'''
select
ifnull(sum(ifnull(e.end_time, strftime('%%s', 'now')) -
e.start_time), 0) as total
from
entry e
where
e.sheet = ?%s;
''' % where, (sheet,))
total = displ_total(db.fetchone()[0])
table += [['', '', displ_total(day_total), ''],
['Total', '', total, '']]
if group == 'off':
cmdutil.pprint_table(table, footer_row=True)
else:
# group format is on
def str2delta(t):
dt = datetime.strptime(t, '%H:%M:%S')
return timedelta(hours=dt.hour,
minutes=dt.minute,
seconds=dt.second)
format_delta = lambda d: cmdutil.timedelta_hms_display(d)
new_table = [table[0]]
match_dict = {
'SCRIPTS': [],
'WES': [],
'USP': [],
'REG': [],
'OTHERS': [],
'QA': []
}
prev_descr = ''
# cycle through all lines except the ones containing totals
for row in [entry for entry in table[1:] if entry[1] != '']:
matched = 0
description = row[3]
for key in match_dict:
if description == '':
description = prev_descr
if key in description:
match_dict[key].append(row[2:])
prev_descr = description
matched = 1
break
if matched == 0:
match_dict['OTHERS'].append(row[2:])
grand_total = timedelta(0)
for key in match_dict:
sub_total = timedelta(0)
comments = ''
for row in match_dict[key]:
duration = row[0]
comment = row[1].encode('ascii', 'ignore')
sub_total += str2delta(duration)
if key == 'OTHERS':
comments += "\t{}~{}\n".format(duration,
comment)
elif comment not in comments:
comments += "\t{}\n".format(comment)
print "Task: {}\nHours: {}\nComments:".format(key,
format_delta(sub_total))
print comments
grand_total += sub_total
print "-- GRAND TOTAL: {}".format(format_delta(grand_total))
print "-- should be -: {}".format(total)
def list(db, args):
parser = OptionParser(usage='''usage: %prog list
List the available timesheets.''')
parser.add_option('-s', '--simple', dest='simple',
action='store_true', help='Only display the names \
of the available timesheets.')
opts, args = parser.parse_args(args=args)
if opts.simple:
db.execute(
u'''
select
distinct sheet
from
entry
order by
sheet asc;
''')
print u'\n'.join(r[0] for r in db.fetchall())
return
table = [[' Timesheet', 'Running', 'Today', 'Total time']]
db.execute(u'''
select
e1.sheet as name,
e1.sheet = meta.value as is_current,
ifnull((select
strftime('%s', 'now') - e2.start_time
from
entry e2
where
e1.sheet = e2.sheet and e2.end_time is null), 0
) as active,
(select
ifnull(sum(ifnull(e3.end_time, strftime('%s', 'now')) -
e3.start_time), 0)
from
entry e3
where
e1.sheet = e3.sheet and
e3.start_time > strftime('%s', date('now'))
) as today,
ifnull(sum(ifnull(e1.end_time, strftime('%s', 'now')) -
e1.start_time), 0) as total
from
entry e1, meta
where
meta.key = 'current_sheet'
group by e1.sheet
order by e1.sheet asc;
''')
sheets = db.fetchall()
if len(sheets) == 0:
print u'(no sheets)'
return
for (name, is_current, active, today, total) in sheets:
cur_name = '%s%s' % ('*' if is_current else ' ', name)
active = str(timedelta(seconds=active)) if active != 0 \
else '--'
today = str(timedelta(seconds=today))
total_time = str(timedelta(seconds=total))
table.append([cur_name, active, today, total_time])
cmdutil.pprint_table(table)
def format_timebook(db, sheet, where):
db.execute(
u'''
select count(*) > 0 from entry where sheet = ?%s
''' % where, (sheet, ))
if not db.fetchone()[0]:
print '(empty)'
return
displ_time = lambda t: time.strftime('%H:%M:%S', time.localtime(t))
displ_date = lambda t: time.strftime('%b %d, %Y', time.localtime(t))
displ_total = lambda t: \
cmdutil.timedelta_hms_display(timedelta(seconds=t))
last_day = None
table = [['Day', 'Start End', 'Duration', 'Notes']]
db.execute(
u'''
select
date(e.start_time, 'unixepoch', 'localtime') as day,
ifnull(sum(ifnull(e.end_time, strftime('%%s', 'now')) -
e.start_time), 0) as day_total
from
entry e
where
e.sheet = ?%s
group by
day
order by
day asc;
''' % where, (sheet, ))
days = db.fetchall()
days_iter = iter(days)
db.execute(
u'''
select
date(e.start_time, 'unixepoch', 'localtime') as day,
e.start_time as start,
e.end_time as end,
ifnull(e.end_time, strftime('%%s', 'now')) - e.start_time as
duration,
ifnull(e.description, '') as description
from
entry e
where
e.sheet = ?%s
order by
day asc;
''' % where, (sheet, ))
entries = db.fetchall()
for i, (day, start, end, duration, description) in \
enumerate(entries):
date = displ_date(start)
diff = displ_total(duration)
if end is None:
trange = '%s -' % displ_time(start)
else:
trange = '%s - %s' % (displ_time(start), displ_time(end))
if last_day == day:
# If this row doesn't represent the first entry of the
# day, don't display anything in the day column.
table.append(['', trange, diff, description])
else:
if last_day is not None:
# Use day_total set (below) from the previous
# iteration. This is skipped the first iteration,
# since last_day is None.
table.append(['', '', displ_total(day_total), ''])
cur_day, day_total = days_iter.next()
assert day == cur_day
table.append([date, trange, diff, description])
last_day = day
db.execute(
u'''
select
ifnull(sum(ifnull(e.end_time, strftime('%%s', 'now')) -
e.start_time), 0) as total
from
entry e
where
e.sheet = ?%s;
''' % where, (sheet, ))
total = displ_total(db.fetchone()[0])
table += [['', '', displ_total(day_total), ''], ['Total', '', total, '']]
cmdutil.pprint_table(table, footer_row=True)
def list(db, args):
parser = OptionParser(usage='''usage: %prog list
List the available timesheets.''')
parser.add_option('-s',
'--simple',
dest='simple',
action='store_true',
help='Only display the names \
of the available timesheets.')
opts, args = parser.parse_args(args=args)
if opts.simple:
db.execute(u'''
select
distinct sheet
from
entry
order by
sheet asc;
''')
print u'\n'.join(r[0] for r in db.fetchall())
return
table = [[' Timesheet', 'Running', 'Today', 'Total time']]
db.execute(u'''
select
e1.sheet as name,
e1.sheet = meta.value as is_current,
ifnull((select
strftime('%s', 'now') - e2.start_time
from
entry e2
where
e1.sheet = e2.sheet and e2.end_time is null), 0
) as active,
(select
ifnull(sum(ifnull(e3.end_time, strftime('%s', 'now')) -
e3.start_time), 0)
from
entry e3
where
e1.sheet = e3.sheet and
e3.start_time > strftime('%s', date('now'))
) as today,
ifnull(sum(ifnull(e1.end_time, strftime('%s', 'now')) -
e1.start_time), 0) as total
from
entry e1, meta
where
meta.key = 'current_sheet'
group by e1.sheet
order by e1.sheet asc;
''')
sheets = db.fetchall()
if len(sheets) == 0:
print u'(no sheets)'
return
for (name, is_current, active, today, total) in sheets:
cur_name = '%s%s' % ('*' if is_current else ' ', name)
active = str(timedelta(seconds=active)) if active != 0 \
else '--'
today = str(timedelta(seconds=today))
total_time = str(timedelta(seconds=total))
table.append([cur_name, active, today, total_time])
cmdutil.pprint_table(table)
def format_timebook(db, sheet, where, show_ids=False, summary=False):
db.execute(u'''
select count(*) > 0 from entry where sheet = ?%s
''' % where, (sheet,))
if not db.fetchone()[0]:
print '(empty)'
return
displ_time = lambda t: time.strftime('%H:%M:%S', time.localtime(t))
displ_date = lambda t: time.strftime('%b %d, %Y',
time.localtime(t))
def displ_total(t):
if not summary:
return cmdutil.timedelta_hms_display(timedelta(seconds=t))
return str(round(t/60.0/60.0, 2))
last_day = None
day_total = None
db.execute(u'''
select
date(e.start_time, 'unixepoch', 'localtime') as day,
ifnull(sum(ifnull(e.end_time, strftime('%%s', 'now')) -
e.start_time), 0) as day_total
from
entry e
where
e.sheet = ?%s
group by
day
order by
day asc;
''' % where, (sheet,))
days = db.fetchall()
days_iter = iter(days)
if summary:
db.execute(u'''
select
date(e.start_time, 'unixepoch', 'localtime') as day,
min(e.start_time) as start,
max(e.end_time) as end,
sum(ifnull(e.end_time, strftime('%%s', 'now')) - e.start_time) as
duration,
ifnull(e.description, '') as description,
min(id)
from
entry e
where
e.sheet = ?%s
group by
date(e.start_time, 'unixepoch', 'localtime'),
ifnull(e.description, '')
order by
day asc;
''' % where, (sheet,))
else:
db.execute(u'''
select
date(e.start_time, 'unixepoch', 'localtime') as day,
e.start_time as start,
e.end_time as end,
ifnull(e.end_time, strftime('%%s', 'now')) - e.start_time as
duration,
ifnull(e.description, '') as description,
id
from
entry e
where
e.sheet = ?%s
order by
day asc;
''' % where, (sheet,))
entries = db.fetchall()
# Get list of total metadata keys
db.execute(u'''
select
distinct key, count(entry_id)
from entry_meta
inner join entry
on entry.id = entry_meta.entry_id
where
entry.sheet = ?
%s
group by key
order by count(entry_id) desc
''' % where, (sheet, ))
metadata_keys = db.fetchall()
extra_count = len(metadata_keys)
if show_ids:
extra_count = extra_count + 1
table = []
table_header = ['Day', 'Start End', 'Duration']
for key in metadata_keys:
table_header.append(
key[0].title().replace('_', ' ')
)
table_header.append('Notes')
if show_ids:
table_header.append('ID')
table.append(table_header)
for i, (day, start, end, duration, description, id) in \
enumerate(entries):
id = str(id)
date = displ_date(start)
diff = displ_total(duration)
if end is None:
trange = '%s -' % displ_time(start)
else:
trange = '%s - %s' % (displ_time(start), displ_time(end))
if last_day == day:
# If this row doesn't represent the first entry of the
# day, don't display anything in the day column.
row = ['']
else:
if day_total:
table.append(['', '', displ_total(day_total), '']
+ [''] * extra_count
)
row = [date]
cur_day, day_total = days_iter.next()
row.extend([
trange, diff
])
ticket_metadata = dbutil.get_entry_meta(db, id)
for meta in metadata_keys:
key = meta[0]
row.append(
ticket_metadata[key] if (
key in ticket_metadata.keys()
) else ''
)
row.append(description)
if show_ids:
row.append(id)
table.append(row)
last_day = day
db.execute(u'''
select
ifnull(sum(ifnull(e.end_time, strftime('%%s', 'now')) -
e.start_time), 0) as total
from
entry e
where
e.sheet = ?%s;
''' % where, (sheet,))
total = displ_total(db.fetchone()[0])
table += [['', '', displ_total(day_total), ''] + [''] * extra_count,
['Total', '', total, '',] + [''] * extra_count]
cmdutil.pprint_table(table, footer_row=True)
def format_timebook(db, sheet, where):
db.execute('''
select count(*) > 0 from entry where sheet = ?%s
''' % where, (sheet,))
if not db.fetchone()[0]:
print('(empty)')
return
displ_time = lambda t: time.strftime('%H:%M:%S', time.localtime(t))
displ_date = lambda t: time.strftime('%b %d, %Y',
time.localtime(t))
displ_total = lambda t: \
cmdutil.timedelta_hms_display(timedelta(seconds=t))
last_day = None
table = [['Day', 'Start End', 'Duration', 'Notes']]
db.execute('''
select
date(e.start_time, 'unixepoch', 'localtime') as day,
ifnull(sum(ifnull(e.end_time, strftime('%%s', 'now')) -
e.start_time), 0) as day_total
from
entry e
where
e.sheet = ?%s
group by
day
order by
day asc;
''' % where, (sheet,))
days = db.fetchall()
days_iter = iter(days)
db.execute('''
select
date(e.start_time, 'unixepoch', 'localtime') as day,
e.start_time as start,
e.end_time as end,
ifnull(e.end_time, strftime('%%s', 'now')) - e.start_time as
duration,
ifnull(e.description, '') as description
from
entry e
where
e.sheet = ?%s
order by
e.start_time asc;
''' % where, (sheet,))
entries = db.fetchall()
for i, (day, start, end, duration, description) in \
enumerate(entries):
date = displ_date(start)
diff = displ_total(duration)
if end is None:
trange = '%s -' % displ_time(start)
else:
trange = '%s - %s' % (displ_time(start), displ_time(end))
if last_day == day:
# If this row doesn't represent the first entry of the
# day, don't display anything in the day column.
table.append(['', trange, diff, description])
else:
if last_day is not None:
# Use day_total set (below) from the previous
# iteration. This is skipped the first iteration,
# since last_day is None.
table.append(['', '', displ_total(day_total), ''])
cur_day, day_total = next(days_iter)
assert day == cur_day
table.append([date, trange, diff, description])
last_day = day
db.execute('''
select
ifnull(sum(ifnull(e.end_time, strftime('%%s', 'now')) -
e.start_time), 0) as total
from
entry e
where
e.sheet = ?%s;
''' % where, (sheet,))
total = displ_total(db.fetchone()[0])
table += [['', '', displ_total(day_total), ''],
['Total', '', total, '']]
cmdutil.pprint_table(table, footer_row=True)
def list(db, simple=False):
"""Show the available timesheets
Usage: t (list | ls) [-s, --simple]
Options:
-s, --simple Only display the names of the available timesheets.
"""
if simple:
db.execute(
'''
select
distinct sheet
from
entry
order by
sheet asc;
''')
print('\n'.join(r[0] for r in db.fetchall()))
return
table = [[' Timesheet', 'Running', 'Today', 'Total time']]
db.execute('''
select
e1.sheet as name,
e1.sheet = meta.value as is_current,
ifnull((select
strftime('%s', 'now') - e2.start_time
from
entry e2
where
e1.sheet = e2.sheet and e2.end_time is null), 0
) as active,
(select
ifnull(sum(ifnull(e3.end_time, strftime('%s', 'now')) -
e3.start_time), 0)
from
entry e3
where
e1.sheet = e3.sheet and
e3.start_time > strftime('%s', date('now'))
) as today,
ifnull(sum(ifnull(e1.end_time, strftime('%s', 'now')) -
e1.start_time), 0) as total
from
entry e1, meta
where
meta.key = 'current_sheet'
group by e1.sheet
order by e1.sheet asc;
''')
sheets = db.fetchall()
if len(sheets) == 0:
print('(no sheets)')
return
for (name, is_current, active, today, total) in sheets:
cur_name = '%s%s' % ('*' if is_current else ' ', name)
active = str(timedelta(seconds=active)) if active != 0 \
else '--'
today = str(timedelta(seconds=today))
total_time = str(timedelta(seconds=total))
table.append([cur_name, active, today, total_time])
cmdutil.pprint_table(table)
def format_timebook(db, sheet, where, show_ids=False):
db.execute(
u'''
select count(*) > 0 from entry where sheet = ?%s
''' % where, (sheet, ))
if not db.fetchone()[0]:
print '(empty)'
return
displ_time = lambda t: time.strftime('%H:%M:%S', time.localtime(t))
displ_date = lambda t: time.strftime('%b %d, %Y', time.localtime(t))
displ_total = lambda t: \
cmdutil.timedelta_hms_display(timedelta(seconds=t))
last_day = None
day_total = None
db.execute(
u'''
select
date(e.start_time, 'unixepoch', 'localtime') as day,
ifnull(sum(ifnull(e.end_time, strftime('%%s', 'now')) -
e.start_time), 0) as day_total
from
entry e
where
e.sheet = ?%s
group by
day
order by
day asc;
''' % where, (sheet, ))
days = db.fetchall()
days_iter = iter(days)
db.execute(
u'''
select
date(e.start_time, 'unixepoch', 'localtime') as day,
e.start_time as start,
e.end_time as end,
ifnull(e.end_time, strftime('%%s', 'now')) - e.start_time as
duration,
ifnull(e.description, '') as description,
id
from
entry e
where
e.sheet = ?%s
order by
day asc;
''' % where, (sheet, ))
entries = db.fetchall()
# Get list of total metadata keys
db.execute(
u'''
select
distinct key, count(entry_id)
from entry_meta
inner join entry
on entry.id = entry_meta.entry_id
where
entry.sheet = ?
%s
group by key
order by count(entry_id) desc
''' % where, (sheet, ))
metadata_keys = db.fetchall()
extra_count = len(metadata_keys)
if show_ids:
extra_count = extra_count + 1
table = []
table_header = ['Day', 'Start End', 'Duration']
for key in metadata_keys:
table_header.append(key[0].title().replace('_', ' '))
table_header.append('Notes')
if show_ids:
table_header.append('ID')
table.append(table_header)
for i, (day, start, end, duration, description, id) in \
enumerate(entries):
id = str(id)
date = displ_date(start)
diff = displ_total(duration)
if end is None:
trange = '%s -' % displ_time(start)
else:
trange = '%s - %s' % (displ_time(start), displ_time(end))
if last_day == day:
# If this row doesn't represent the first entry of the
# day, don't display anything in the day column.
row = ['']
else:
if day_total:
table.append(['', '', displ_total(day_total), ''] +
[''] * extra_count)
row = [date]
cur_day, day_total = days_iter.next()
row.extend([trange, diff])
ticket_metadata = dbutil.get_entry_meta(db, id)
for meta in metadata_keys:
key = meta[0]
row.append(ticket_metadata[key] if (
key in ticket_metadata.keys()) else '')
row.append(description)
if show_ids:
row.append(id)
table.append(row)
last_day = day
db.execute(
u'''
select
ifnull(sum(ifnull(e.end_time, strftime('%%s', 'now')) -
e.start_time), 0) as total
from
entry e
where
e.sheet = ?%s;
''' % where, (sheet, ))
total = displ_total(db.fetchone()[0])
table += [['', '', displ_total(day_total), ''] + [''] * extra_count,
[
'Total',
'',
total,
'',
] + [''] * extra_count]
cmdutil.pprint_table(table, footer_row=True)
