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)