def index(cursor, config):
current_row = cursor.execute("""
SELECT
id,
start_time,
end_time,
description,
ROUND((COALESCE(end_time, strftime('%s', 'now')) - start_time) / CAST(3600 AS FLOAT), 2) AS hours
FROM entry
WHERE start_time = (select max(start_time) from entry where sheet = 'default')
""").fetchone()
todays_tasks_rows = cursor.execute("""
SELECT
id,
start_time,
end_time,
description,
ROUND((COALESCE(end_time, strftime('%s', 'now')) - start_time) / CAST(3600 AS FLOAT), 2) AS hours
FROM entry
WHERE start_time > strftime('%s', strftime('%Y-%m-%d', 'now', 'localtime'), 'utc') AND sheet = 'default'
ORDER BY start_time DESC
""").fetchall()
lookup_helper = ChiliprojectConnector(db = cursor)
current = TimesheetRow.from_row(current_row)
current.set_lookup_handler(lookup_helper)
hours_total = 0
todays_tasks = []
for task_row in todays_tasks_rows:
task = TimesheetRow.from_row(task_row)
task.set_meta(
get_entry_meta(
cursor,
task.id
)
)
task.set_lookup_handler(lookup_helper)
hours_total = hours_total + task.total_hours
todays_tasks.append(task)
template_name = "snapshot.html"
try:
template_name = config.get('template', 'snapshot')
except (NoSectionError, NoOptionError):
pass
return render_template(template_name,
current = current,
human_username = config.get('temp', 'human_name'),
todays_tasks = todays_tasks,
hours_total = hours_total
)
def index(cursor, config):
current_row = cursor.execute("""
SELECT
id,
start_time,
end_time,
description,
ROUND((COALESCE(end_time, strftime('%s', 'now')) - start_time) / CAST(3600 AS FLOAT), 2) AS hours
FROM entry
WHERE start_time = (select max(start_time) from entry where sheet = 'default')
""").fetchone()
todays_tasks_rows = cursor.execute("""
SELECT
id,
start_time,
end_time,
description,
ROUND((COALESCE(end_time, strftime('%s', 'now')) - start_time) / CAST(3600 AS FLOAT), 2) AS hours
FROM entry
WHERE start_time > strftime('%s', strftime('%Y-%m-%d', 'now', 'localtime'), 'utc') AND sheet = 'default'
ORDER BY start_time DESC
""").fetchall()
lookup_helper = ChiliprojectConnector(db=cursor)
current = TimesheetRow.from_row(current_row)
current.set_lookup_handler(lookup_helper)
hours_total = 0
todays_tasks = []
for task_row in todays_tasks_rows:
task = TimesheetRow.from_row(task_row)
task.set_meta(get_entry_meta(cursor, task.id))
task.set_lookup_handler(lookup_helper)
hours_total = hours_total + task.total_hours
todays_tasks.append(task)
template_name = "snapshot.html"
try:
template_name = config.get('template', 'snapshot')
except (NoSectionError, NoOptionError):
pass
return render_template(template_name,
current=current,
human_username=config.get('temp', 'human_name'),
todays_tasks=todays_tasks,
hours_total=hours_total)
def get_entries(self, day):
self.db.execute("""
SELECT
id,
start_time,
COALESCE(end_time, STRFTIME('%s', 'now')) as end_time,
description,
ROUND(
(
COALESCE(end_time, strftime('%s', 'now'))
- start_time
)
/ CAST(3600 AS FLOAT), 2
) AS hours
FROM
entry
WHERE
start_time > STRFTIME('%s', ?, 'utc')
AND
start_time < STRFTIME('%s', ?, 'utc', '1 day')
AND
sheet = 'default'
""", (day.strftime("%Y-%m-%d"), day.strftime("%Y-%m-%d"), ))
results = self.db.fetchall()
helper = ChiliprojectConnector(
self.db,
username=self.username,
password=self.password
)
final_results = []
for result in results:
entry = TimesheetRow.from_row(result)
entry.set_lookup_handler(helper)
entry.set_meta(
get_entry_meta(self.db, result[0])
)
final_results.append(entry)
return final_results
def get_entries(self, day):
self.db.execute("""
SELECT
id,
start_time,
COALESCE(end_time, STRFTIME('%s', 'now')) as end_time,
description,
ROUND(
(
COALESCE(end_time, strftime('%s', 'now'))
- start_time
)
/ CAST(3600 AS FLOAT), 2
) AS hours
FROM
entry
WHERE
start_time > STRFTIME('%s', ?, 'utc')
AND
start_time < STRFTIME('%s', ?, 'utc', '1 day')
AND
sheet = 'default'
""", (day.strftime("%Y-%m-%d"), day.strftime("%Y-%m-%d"), ))
results = self.db.fetchall()
helper = ChiliprojectConnector(
self.db,
username=self.username,
password=self.password
)
final_results = []
for result in results:
entry = TimesheetRow.from_row(result)
entry.set_lookup_handler(helper)
entry.set_meta(
get_entry_meta(self.db, result[0])
)
final_results.append(entry)
return final_results
def in_(db, args, extra=None, change=False):
parser = optparse.OptionParser(usage='''usage: %prog in [NOTES...]
Start the timer for the current timesheet. Must be called before out.
Notes may be specified for this period. This is exactly equivalent to
%prog in; %prog alter''')
parser.add_option('-s', '--switch', dest='switch', type='string',
help='Switch to another timesheet before starting the timer.'
)
parser.add_option('-o', '--out', dest='out', action='store_true',
default=False, help='Clocks out before clocking in'
)
parser.add_option('-a', '--at', dest='at', type='string',
help='Set time of clock-in'
)
parser.add_option('-t', '--ticket', dest='ticket_number', type='string',
default=None, help='Set ticket number'
)
parser.add_option('--billable', dest='billable', action='store_true',
default=True, help='Marks entry as billable'
)
parser.add_option('--non-billable', dest='billable', action='store_false',
default=True, help='Marks entry as non-billable'
)
cmdutil.add_user_specified_attributes(db, parser)
opts, args = parser.parse_args(args=args)
metadata = cmdutil.collect_user_specified_attributes(db, opts)
metadata['billable'] = 'yes' if opts.billable else 'no'
if opts.ticket_number:
metadata['ticket_number'] = opts.ticket_number
if opts.switch:
sheet = opts.switch
switch(db, [sheet])
else:
sheet = dbutil.get_current_sheet(db)
timestamp = cmdutil.parse_date_time_or_now(opts.at)
if opts.out:
clock_out(db, timestamp=timestamp)
running = dbutil.get_active_info(db, sheet)
if running is not None:
raise SystemExit('error: timesheet already active')
most_recent_clockout = dbutil.get_most_recent_clockout(db, sheet)
description = u' '.join(args) or None
if most_recent_clockout:
(id, start_time, prev_timestamp, prev_desc) = most_recent_clockout
prev_meta = dbutil.get_entry_meta(db, id)
if timestamp < prev_timestamp:
raise SystemExit('error: time periods could end up overlapping')
current_sheet = dbutil.get_current_sheet(db)
if change and db.config.has_option(current_sheet, 'autocontinue'):
if not description:
description = prev_desc
for p_key, p_value in prev_meta.items():
if p_key not in metadata.keys() or not metadata[p_key]:
metadata[p_key] = p_value
db.execute(u'''
insert into entry (
sheet, start_time, description, extra
) values (?,?,?,?)
''', (sheet, timestamp, description, extra))
entry_id = db.cursor.lastrowid
dbutil.update_entry_meta(db, entry_id, metadata)
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 in_(db, args, extra=None, change=False):
parser = optparse.OptionParser(usage='''usage: %prog in [NOTES...]
Start the timer for the current timesheet. Must be called before out.
Notes may be specified for this period. This is exactly equivalent to
%prog in; %prog alter''')
parser.add_option(
'-s',
'--switch',
dest='switch',
type='string',
help='Switch to another timesheet before starting the timer.')
parser.add_option('-o',
'--out',
dest='out',
action='store_true',
default=False,
help='Clocks out before clocking in')
parser.add_option('-a',
'--at',
dest='at',
type='string',
help='Set time of clock-in')
parser.add_option('-t',
'--ticket',
dest='ticket_number',
type='string',
default=None,
help='Set ticket number')
parser.add_option('--billable',
dest='billable',
action='store_true',
default=True,
help='Marks entry as billable')
parser.add_option('--non-billable',
dest='billable',
action='store_false',
default=True,
help='Marks entry as non-billable')
cmdutil.add_user_specified_attributes(db, parser)
opts, args = parser.parse_args(args=args)
metadata = cmdutil.collect_user_specified_attributes(db, opts)
metadata['billable'] = 'yes' if opts.billable else 'no'
if opts.ticket_number:
metadata['ticket_number'] = opts.ticket_number
if opts.switch:
sheet = opts.switch
switch(db, [sheet])
else:
sheet = dbutil.get_current_sheet(db)
timestamp = cmdutil.parse_date_time_or_now(opts.at)
if opts.out:
clock_out(db, timestamp=timestamp)
running = dbutil.get_active_info(db, sheet)
if running is not None:
raise SystemExit('error: timesheet already active')
most_recent_clockout = dbutil.get_most_recent_clockout(db, sheet)
description = u' '.join(args) or None
if most_recent_clockout:
(id, start_time, prev_timestamp, prev_desc) = most_recent_clockout
prev_meta = dbutil.get_entry_meta(db, id)
if timestamp < prev_timestamp:
raise SystemExit('error: time periods could end up overlapping')
current_sheet = dbutil.get_current_sheet(db)
if change and db.config.has_option(current_sheet, 'autocontinue'):
if not description:
description = prev_desc
for p_key, p_value in prev_meta.items():
if p_key not in metadata.keys() or not metadata[p_key]:
metadata[p_key] = p_value
db.execute(
u'''
insert into entry (
sheet, start_time, description, extra
) values (?,?,?,?)
''', (sheet, timestamp, description, extra))
entry_id = db.cursor.lastrowid
dbutil.update_entry_meta(db, entry_id, metadata)
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 format_eu(db, sheet, where, show_ids=False, sdate=None, edate=None):
"""
Attention: Assumes that all queried data is from one month!
"""
if sdate is None or edate is None:
raise SystemExit("Please specify start and end or month for export.")
import csv
writer = csv.writer(sys.stdout, delimiter=';', dialect='excel')
def daterange(start_date, end_date):
for n in range(int((end_date - start_date).days) + 1):
yield start_date + timedelta(n)
def round_working_hours(t):
"""
Rounding with half hour steps.
"""
return round(t * 2 / 60.0 / 60.0, 0) / 2
day_total = None
db.execute(
u'''
select
id,
date(e.start_time, 'unixepoch', 'localtime') as day,
ifnull(sum(ifnull(e.end_time, strftime('%%s', 'now')) -
e.start_time), 0) as day_total,
ifnull(e.description, '') as description
from
entry e
where
e.sheet = ?%s
group by
id
order by
day asc;
''' % where, (sheet, ))
rows = db.fetchall()
export_data = {}
description_list = []
for r in rows:
cell_id = r[0]
cell_date = r[1]
cell_workhours = r[2]
cell_description = r[3]
# get metadata (especially the workpackage (WP) column)
ticket_metadata = dbutil.get_entry_meta(db, r[0])
try:
cell_wp = int(ticket_metadata["wp"])
except:
cell_wp = 0
# create a simple list with all descriptions
if cell_description not in description_list:
description_list.append(cell_description)
# create export data as table: rows=WP columns=data
if cell_wp not in export_data:
export_data[cell_wp] = {}
if cell_date not in export_data[cell_wp]:
export_data[cell_wp][cell_date] = 0.0
export_data[cell_wp][cell_date] += cell_workhours
# csv export
# header
dates = [d.strftime("%Y-%m-%d") for d in daterange(sdate, edate)]
writer.writerow(["WP"] + dates)
for wp in range(1, 8):
if wp in export_data:
columns = export_data[wp]
else:
columns = {}
curr_row = [wp]
for d in dates:
if d in columns:
hours = round_working_hours(columns[d])
if hours > 0:
curr_row.append(str(hours).replace(".", ","))
else:
curr_row.append("")
else:
curr_row.append("")
writer.writerow(curr_row)
writer.writerow([])
writer.writerow([])
writer.writerow(["Descriptions:", ", ".join(description_list)])
def format_eu(db, sheet, where, show_ids=False, sdate=None, edate=None):
"""
Attention: Assumes that all queried data is from one month!
"""
if sdate is None or edate is None:
raise SystemExit("Please specify start and end or month for export.")
import csv
writer = csv.writer(sys.stdout, delimiter=';', dialect='excel')
def daterange(start_date, end_date):
for n in range(int((end_date - start_date).days) + 1):
yield start_date + timedelta(n)
def round_working_hours(t):
"""
Rounding with half hour steps.
"""
return round(t*2/60.0/60.0, 0)/2
day_total = None
db.execute(u'''
select
id,
date(e.start_time, 'unixepoch', 'localtime') as day,
ifnull(sum(ifnull(e.end_time, strftime('%%s', 'now')) -
e.start_time), 0) as day_total,
ifnull(e.description, '') as description
from
entry e
where
e.sheet = ?%s
group by
id
order by
day asc;
''' % where, (sheet,))
rows = db.fetchall()
export_data = {}
description_list = []
for r in rows:
cell_id = r[0]
cell_date = r[1]
cell_workhours = r[2]
cell_description = r[3]
# get metadata (especially the workpackage (WP) column)
ticket_metadata = dbutil.get_entry_meta(db, r[0])
try:
cell_wp = int(ticket_metadata["wp"])
except:
cell_wp = 0
# create a simple list with all descriptions
if cell_description not in description_list:
description_list.append(cell_description)
# create export data as table: rows=WP columns=data
if cell_wp not in export_data:
export_data[cell_wp] = {}
if cell_date not in export_data[cell_wp]:
export_data[cell_wp][cell_date] = 0.0
export_data[cell_wp][cell_date] += cell_workhours
# csv export
# header
dates = [d.strftime("%Y-%m-%d") for d in daterange(sdate, edate)]
writer.writerow(["WP"] + dates)
for wp in range(1, 8):
if wp in export_data:
columns = export_data[wp]
else:
columns = {}
curr_row = [wp]
for d in dates:
if d in columns:
hours = round_working_hours(columns[d])
if hours > 0:
curr_row.append(str(hours).replace(".", ","))
else:
curr_row.append("")
else:
curr_row.append("")
writer.writerow(curr_row)
writer.writerow([])
writer.writerow([])
writer.writerow(["Descriptions:", ", ".join(description_list)])
