def on_clipboard_text(self, clipboard, text, data):
# first check that we have a date selected
fact = self.fact_tree.get_selected_fact()
if not fact:
return
if isinstance(fact, dt.date):
selected_date = fact
else:
selected_date = fact.date
fact = stuff.Fact(text.decode("utf-8"))
if not all((fact.activity, fact.start_time, fact.end_time)):
return
fact.start_time = fact.start_time.replace(year = selected_date.year,
month = selected_date.month,
day = selected_date.day)
fact.end_time = fact.end_time.replace(year = selected_date.year,
month = selected_date.month,
day = selected_date.day)
new_id = runtime.storage.add_fact(fact)
# You can do that?! - copy/pasted an activity
trophies.unlock("can_do_that")
if new_id:
self.fact_tree.select_fact(new_id)
def activity_name_edited_cb(self, cell, path, new_text, model):
id = model[path][0]
category_id = model[path][2]
activities = runtime.storage.get_category_activities(category_id)
prev = None
for activity in activities:
if id == activity['id']:
prev = activity['name']
else:
# avoid two activities in same category with same name
if activity['name'].lower() == new_text.lower():
if id == -1: # that was a new activity
self.activity_store.remove(model.get_iter(path))
self.select_activity(activity['id'])
return False
if id == -1: #new activity -> add
model[path][0] = runtime.storage.add_activity(new_text.decode("utf-8"), category_id)
else: #existing activity -> update
new = new_text.decode("utf-8")
runtime.storage.update_activity(id, new, category_id)
# size matters - when editing activity name just changed the case (bar -> Bar)
if prev != new and prev.lower() == new.lower():
trophies.unlock("size_matters")
model[path][1] = new_text
return True
def on_db_file_change(monitor, gio_file, event_uri, event):
if event == gio.FILE_MONITOR_EVENT_CHANGES_DONE_HINT:
if gio_file.query_info(gio.FILE_ATTRIBUTE_ETAG_VALUE).get_etag() == self.__last_etag:
# ours
return
elif event == gio.FILE_MONITOR_EVENT_CREATED:
# treat case when instead of a move, a remove and create has been performed
self.con = None
if event in (gio.FILE_MONITOR_EVENT_CHANGES_DONE_HINT, gio.FILE_MONITOR_EVENT_CREATED):
print "DB file has been modified externally. Calling all stations"
self.dispatch_overwrite()
# plan "b" – synchronize the time tracker's database from external source while the tracker is running
trophies.unlock("plan_b")
def __remove_activity(self, id):
""" check if we have any facts with this activity and behave accordingly
if there are facts - sets activity to deleted = True
else, just remove it"""
query = "select count(*) as count from facts where activity_id = ?"
bound_facts = self.fetchone(query, (id,))['count']
if bound_facts > 0:
self.execute("UPDATE activities SET deleted = 1 WHERE id = ?", (id,))
else:
self.execute("delete from activities where id = ?", (id,))
# Finished! - deleted an activity with more than 50 facts on it
if bound_facts >= 50:
trophies.unlock("finished")
def simple(facts, start_date, end_date, format, path):
facts = copy.deepcopy(facts) # dont want to do anything bad to the input
report_path = stuff.locale_from_utf8(path)
if format == "tsv":
writer = TSVWriter(report_path)
elif format == "xml":
writer = XMLWriter(report_path)
elif format == "ical":
writer = ICalWriter(report_path)
else: #default to HTML
writer = HTMLWriter(report_path, start_date, end_date)
writer.write_report(facts)
# some assembly required - hidden - saved a report for single day
if start_date == end_date:
trophies.unlock("some_assembly_required")
# I want this on my desk - generated over 10 different reports
if trophies.check("on_my_desk") == False:
current = trophies.increment("reports_generated")
if current == 10:
trophies.unlock("on_my_desk")
def on_help_clicked(self, *args):
gtk.show_uri(gtk.gdk.Screen(), "ghelp:hamster-applet", 0L)
trophies.unlock("basic_instructions")
return False
def _finish(self, report, facts):
# group by date
name_category = lambda fact: (fact.category, fact.activity)
by_date = []
for date, date_facts in itertools.groupby(facts, lambda fact:fact.date):
by_name = sorted(date_facts, key=name_category)
by_date_rows = []
for (category, activity), ac_facts in itertools.groupby(by_name, name_category):
duration = dt.timedelta()
for fact in ac_facts:
duration += fact.delta
by_date_rows.append(Template(self.by_date_row_template).safe_substitute(
dict(activity = activity,
category = category,
duration = stuff.format_duration(duration),
duration_minutes = "%d" % (stuff.duration_minutes(fact.delta)),
duration_decimal = "%.2f" % (stuff.duration_minutes(fact.delta) / 60.0),
)
))
by_date_total_rows = []
for category, c_facts in itertools.groupby(by_name, lambda fact:fact.category):
duration = dt.timedelta()
for fact in c_facts:
duration += fact.delta
by_date_total_rows.append(Template(self.by_date_total_row_template).safe_substitute(
dict(category = category,
duration = stuff.format_duration(duration),
duration_minutes = "%d" % (stuff.duration_minutes(fact.delta)),
duration_decimal = "%.2f" % (stuff.duration_minutes(fact.delta) / 60.0),
)
))
res = Template(self.by_date_template).safe_substitute(
dict(date = fact.date.strftime(
# date column format for each row in HTML report
# Using python datetime formatting syntax. See:
# http://docs.python.org/library/time.html#time.strftime
C_("html report","%b %d, %Y")),
by_date_activity_rows = "\n".join(by_date_rows),
by_date_category_rows = "\n".join(by_date_total_rows)
))
by_date.append(res)
data = dict(
title = self.title,
totals_by_day_title = _("Totals by Day"),
activity_log_title = _("Activity Log"),
activity_totals_heading = _("totals by activity"),
category_totals_heading = _("totals by category"),
show_prompt = _("Show:"),
header_date = _("Date"),
header_activity = _("Activity"),
header_category = _("Category"),
header_tags = _("Tags"),
header_start = _("Start"),
header_end = _("End"),
header_duration = _("Duration"),
header_description = _("Description"),
data_dir = runtime.data_dir,
show_template = _("Show template"),
template_instructions = _("You can override it by storing your version in %(home_folder)s") % {'home_folder': runtime.home_data_dir},
all_activities_rows = "\n".join(self.fact_rows),
by_date_rows = "\n".join(by_date)
)
report.write(Template(self.main_template).safe_substitute(data))
if self.override:
# my report is better than your report - overrode and ran the default report
trophies.unlock("my_report")
return
def run_fixtures(self):
self.start_transaction()
# defaults
work_category = {"name": _("Work"),
"entries": [_("Reading news"),
_("Checking stocks"),
_("Super secret project X"),
_("World domination")]}
nonwork_category = {"name": _("Day-to-day"),
"entries": [_("Lunch"),
_("Watering flowers"),
_("Doing handstands")]}
"""upgrade DB to hamster version"""
version = self.fetchone("SELECT version FROM version")["version"]
current_version = 9
if version < 2:
"""moving from fact_date, fact_time to start_time, end_time"""
self.execute("""
CREATE TABLE facts_new
(id integer primary key,
activity_id integer,
start_time varchar2(12),
end_time varchar2(12))
""")
self.execute("""
INSERT INTO facts_new
(id, activity_id, start_time)
SELECT id, activity_id, fact_date || fact_time
FROM facts
""")
self.execute("DROP TABLE facts")
self.execute("ALTER TABLE facts_new RENAME TO facts")
# run through all facts and set the end time
# if previous fact is not on the same date, then it means that it was the
# last one in previous, so remove it
# this logic saves our last entry from being deleted, which is good
facts = self.fetchall("""
SELECT id, activity_id, start_time,
substr(start_time,1, 8) start_date
FROM facts
ORDER BY start_time
""")
prev_fact = None
for fact in facts:
if prev_fact:
if prev_fact['start_date'] == fact['start_date']:
self.execute("UPDATE facts SET end_time = ? where id = ?",
(fact['start_time'], prev_fact['id']))
else:
#otherwise that's the last entry of the day - remove it
self.execute("DELETE FROM facts WHERE id = ?", (prev_fact["id"],))
prev_fact = fact
#it was kind of silly not to have datetimes in first place
if version < 3:
self.execute("""
CREATE TABLE facts_new
(id integer primary key,
activity_id integer,
start_time timestamp,
end_time timestamp)
""")
self.execute("""
INSERT INTO facts_new
(id, activity_id, start_time, end_time)
SELECT id, activity_id,
substr(start_time,1,4) || "-"
|| substr(start_time, 5, 2) || "-"
|| substr(start_time, 7, 2) || " "
|| substr(start_time, 9, 2) || ":"
|| substr(start_time, 11, 2) || ":00",
substr(end_time,1,4) || "-"
|| substr(end_time, 5, 2) || "-"
|| substr(end_time, 7, 2) || " "
|| substr(end_time, 9, 2) || ":"
|| substr(end_time, 11, 2) || ":00"
FROM facts;
""")
self.execute("DROP TABLE facts")
self.execute("ALTER TABLE facts_new RENAME TO facts")
#adding categories table to categorize activities
if version < 4:
#adding the categories table
self.execute("""
CREATE TABLE categories
(id integer primary key,
name varchar2(500),
color_code varchar2(50),
category_order integer)
""")
# adding default categories, and make sure that uncategorized stays on bottom for starters
# set order to 2 in case, if we get work in next lines
self.execute("""
INSERT INTO categories
(id, name, category_order)
VALUES (1, ?, 2);
""", (nonwork_category["name"],))
#check if we have to create work category - consider work everything that has been determined so, and is not deleted
work_activities = self.fetchone("""
SELECT count(*) as work_activities
FROM activities
WHERE deleted is null and work=1;
""")['work_activities']
if work_activities > 0:
self.execute("""
INSERT INTO categories
(id, name, category_order)
VALUES (2, ?, 1);
""", (work_category["name"],))
# now add category field to activities, before starting the move
self.execute(""" ALTER TABLE activities
ADD COLUMN category_id integer;
""")
# starting the move
# first remove all deleted activities with no instances in facts
self.execute("""
DELETE FROM activities
WHERE deleted = 1
AND id not in(select activity_id from facts);
""")
# moving work / non-work to appropriate categories
# exploit false/true = 0/1 thing
self.execute(""" UPDATE activities
SET category_id = work + 1
WHERE deleted is null
""")
#finally, set category to -1 where there is none
self.execute(""" UPDATE activities
SET category_id = -1
WHERE category_id is null
""")
# drop work column and forget value of deleted
# previously deleted records are now unsorted ones
# user will be able to mark them as deleted again, in which case
# they won't appear in autocomplete, or in categories
# resurrection happens, when user enters the exact same name
self.execute("""
CREATE TABLE activities_new (id integer primary key,
name varchar2(500),
activity_order integer,
deleted integer,
category_id integer);
""")
self.execute("""
INSERT INTO activities_new
(id, name, activity_order, category_id)
SELECT id, name, activity_order, category_id
FROM activities;
""")
self.execute("DROP TABLE activities")
self.execute("ALTER TABLE activities_new RENAME TO activities")
if version < 5:
self.execute("ALTER TABLE facts add column description varchar2")
if version < 6:
# facts table could use an index
self.execute("CREATE INDEX idx_facts_start_end ON facts(start_time, end_time)")
self.execute("CREATE INDEX idx_facts_start_end_activity ON facts(start_time, end_time, activity_id)")
# adding tags
self.execute("""CREATE TABLE tags (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
name TEXT NOT NULL,
autocomplete BOOL DEFAULT true)""")
self.execute("CREATE INDEX idx_tags_name ON tags(name)")
self.execute("CREATE TABLE fact_tags(fact_id integer, tag_id integer)")
self.execute("CREATE INDEX idx_fact_tags_fact ON fact_tags(fact_id)")
self.execute("CREATE INDEX idx_fact_tags_tag ON fact_tags(tag_id)")
if version < 7:
self.execute("""CREATE TABLE increment_facts (id integer primary key autoincrement,
activity_id integer,
start_time timestamp,
end_time timestamp,
description varchar2)""")
self.execute("""INSERT INTO increment_facts(id, activity_id, start_time, end_time, description)
SELECT id, activity_id, start_time, end_time, description from facts""")
self.execute("DROP table facts")
self.execute("ALTER TABLE increment_facts RENAME TO facts")
if version < 8:
# working around sqlite's utf-f case sensitivity (bug 624438)
# more info: http://www.gsak.net/help/hs23820.htm
self.execute("ALTER TABLE activities ADD COLUMN search_name varchar2")
activities = self.fetchall("select * from activities")
statement = "update activities set search_name = ? where id = ?"
for activity in activities:
self.execute(statement, (activity['name'].lower(), activity['id']))
# same for categories
self.execute("ALTER TABLE categories ADD COLUMN search_name varchar2")
categories = self.fetchall("select * from categories")
statement = "update categories set search_name = ? where id = ?"
for category in categories:
self.execute(statement, (category['name'].lower(), category['id']))
if version < 9:
# adding full text search
self.execute("""CREATE VIRTUAL TABLE fact_index
USING fts3(id, name, category, description, tag)""")
# at the happy end, update version number
if version < current_version:
#lock down current version
self.execute("UPDATE version SET version = %d" % current_version)
print "updated database from version %d to %d" % (version, current_version)
# oldtimer – database version structure had been performed on startup (thus we know that he has been on at least 2 versions)
trophies.unlock("oldtimer")
"""we start with an empty database and then populate with default
values. This way defaults can be localized!"""
category_count = self.fetchone("select count(*) from categories")[0]
if category_count == 0:
work_cat_id = self.__add_category(work_category["name"])
for entry in work_category["entries"]:
self.__add_activity(entry, work_cat_id)
nonwork_cat_id = self.__add_category(nonwork_category["name"])
for entry in nonwork_category["entries"]:
self.__add_activity(entry, nonwork_cat_id)
self.end_transaction()
def __add_fact(self, serialized_fact, start_time, end_time = None, temporary = False):
fact = stuff.Fact(serialized_fact,
start_time = start_time,
end_time = end_time)
if not fact.activity or start_time is None: # sanity check
return 0
# get tags from database - this will create any missing tags too
tags = [dict(zip(('id', 'name', 'autocomplete'), row))
for row in self.GetTagIds(fact.tags)]
now = datetime.datetime.now()
# if in future - roll back to past
if start_time > datetime.datetime.now():
start_time = dt.datetime.combine(now.date(), start_time.time())
if start_time > now:
start_time -= dt.timedelta(days = 1)
if end_time and end_time > now:
end_time = dt.datetime.combine(now.date(), end_time.time())
if end_time > now:
end_time -= dt.timedelta(days = 1)
# now check if maybe there is also a category
category_id = None
if fact.category:
category_id = self.__get_category_id(fact.category)
if not category_id:
category_id = self.__add_category(fact.category)
trophies.unlock("no_hands")
# try to find activity, resurrect if not temporary
activity_id = self.__get_activity_by_name(fact.activity,
category_id,
resurrect = not temporary)
if not activity_id:
activity_id = self.__add_activity(fact.activity,
category_id, temporary)
else:
activity_id = activity_id['id']
# if we are working on +/- current day - check the last_activity
if (dt.datetime.now() - start_time <= dt.timedelta(days=1)):
# pull in previous facts
facts = self.__get_todays_facts()
previous = None
if facts and facts[-1]["end_time"] == None:
previous = facts[-1]
if previous and previous['start_time'] < start_time:
# check if maybe that is the same one, in that case no need to restart
if previous["activity_id"] == activity_id \
and set(previous["tags"]) == set([tag["name"] for tag in tags]) \
and (previous["description"] or "") == (fact.description or ""):
return None
# if no description is added
# see if maybe previous was too short to qualify as an activity
if not previous["description"] \
and 60 >= (start_time - previous['start_time']).seconds >= 0:
self.__remove_fact(previous['id'])
# now that we removed the previous one, see if maybe the one
# before that is actually same as the one we want to start
# (glueing)
if len(facts) > 1 and 60 >= (start_time - facts[-2]['end_time']).seconds >= 0:
before = facts[-2]
if before["activity_id"] == activity_id \
and set(before["tags"]) == set([tag["name"] for tag in tags]):
# resume and return
update = """
UPDATE facts
SET end_time = null
WHERE id = ?
"""
self.execute(update, (before["id"],))
return before["id"]
else:
# otherwise stop
update = """
UPDATE facts
SET end_time = ?
WHERE id = ?
"""
self.execute(update, (start_time, previous["id"]))
# done with the current activity, now we can solve overlaps
if not end_time:
end_time = self.__squeeze_in(start_time)
else:
self.__solve_overlaps(start_time, end_time)
# finally add the new entry
insert = """
INSERT INTO facts (activity_id, start_time, end_time, description)
VALUES (?, ?, ?, ?)
"""
self.execute(insert, (activity_id, start_time, end_time, fact.description))
fact_id = self.__last_insert_rowid()
#now link tags
insert = ["insert into fact_tags(fact_id, tag_id) values(?, ?)"] * len(tags)
params = [(fact_id, tag["id"]) for tag in tags]
self.execute(insert, params)
self.__remove_index([fact_id])
return fact_id
def __solve_overlaps(self, start_time, end_time):
"""finds facts that happen in given interval and shifts them to
make room for new fact
"""
if end_time is None or start_time is None:
return
# possible combinations and the OR clauses that catch them
# (the side of the number marks if it catches the end or start time)
# |----------------- NEW -----------------|
# |--- old --- 1| |2 --- old --- 1| |2 --- old ---|
# |3 ----------------------- big old ------------------------ 3|
query = """
SELECT a.*, b.name, c.name as category
FROM facts a
LEFT JOIN activities b on b.id = a.activity_id
LEFT JOIN categories c on b.category_id = c.id
WHERE (end_time > ? and end_time < ?)
OR (start_time > ? and start_time < ?)
OR (start_time < ? and end_time > ?)
ORDER BY start_time
"""
conflicts = self.fetchall(query, (start_time, end_time,
start_time, end_time,
start_time, end_time))
for fact in conflicts:
# won't eliminate as it is better to have overlapping entries than loosing data
if start_time < fact["start_time"] and end_time > fact["end_time"]:
continue
# split - truncate until beginning of new entry and create new activity for end
if fact["start_time"] < start_time < fact["end_time"] and \
fact["start_time"] < end_time < fact["end_time"]:
logging.info("splitting %s" % fact["name"])
# truncate until beginning of the new entry
self.execute("""UPDATE facts
SET end_time = ?
WHERE id = ?""", (start_time, fact["id"]))
fact_name = fact["name"]
# create new fact for the end
new_fact = stuff.Fact(fact["name"],
category = fact["category"],
description = fact["description"],
)
new_fact_id = self.__add_fact(new_fact.serialized_name(), end_time, fact["end_time"])
# copy tags
tag_update = """INSERT INTO fact_tags(fact_id, tag_id)
SELECT ?, tag_id
FROM fact_tags
WHERE fact_id = ?"""
self.execute(tag_update, (new_fact_id, fact["id"])) #clone tags
trophies.unlock("split")
# overlap start
elif start_time < fact["start_time"] < end_time:
logging.info("Overlapping start of %s" % fact["name"])
self.execute("UPDATE facts SET start_time=? WHERE id=?",
(end_time, fact["id"]))
# overlap end
elif start_time < fact["end_time"] < end_time:
logging.info("Overlapping end of %s" % fact["name"])
self.execute("UPDATE facts SET end_time=? WHERE id=?",
(start_time, fact["id"]))
def on_tabs_window_state_changed(self, window, event):
# not enough space - maximized the overview window
maximized = window.get_window().get_state(
) & gtk.gdk.WINDOW_STATE_MAXIMIZED
if maximized:
trophies.unlock("not_enough_space")
