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 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 displ_total(t):
if not summary:
return cmdutil.timedelta_hms_display(timedelta(seconds=t))
return str(round(t/60.0/60.0, 2))
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 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)
def displ_total(t):
if not summary:
return cmdutil.timedelta_hms_display(timedelta(seconds=t))
return str(round(t / 60.0 / 60.0, 2))
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)
