I'm a huge fan of the Pomodoro.app. It helps me to focus on my work and it controlls my messenger status (Adium) to tell my colleagues when I'm busy. Now I even use it to log my activies and put them into a SQLite database for reporting.

Clue code

The Pomodoro.app can invoke AppleScripts on certain events. I'm not a big fan of the AppleScript-Language but I know how to make it run external programs. For that reason I wrote a few lines of Python to insert the activities into the database.

Frist put the Python-Script somewhere. Then launch Pomodoro , go to Preferences / Scripts, check End and put this script into the field:

-- Applescript:
do shell script "/Users/stefan/timesheet_add.py -t \"$pomodoroName\" -m \"$duration\""

Just replace the path to timesheet_add.py. The parameters $pomodoroName (name of the activity) and $duration (time in minutes) will be passed to the script.

Database

The table structure is as simple as:

CREATE TABLE activities (
  day text,
  activity text,
  minutes text,
  added timestamp
);

This timesheet view groups all activities with the same name on a certain day and shows the time HH:MM formated:

CREATE VIEW timesheet AS
   select day, activity , (sum(minutes)/60) || ':' || (sum(minutes)%60) as hours 
   from activities
   group by day,activity

The next one only selects activities of the current week:

CREATE VIEW timesheet_current_week AS
   select day,activity, (sum(minutes)/60) || ':' || (sum(minutes)%60) as hours 
   from activities 
   where strftime('%YW%W', day) = strftime('%YW%W', date('now')) 
   group by day,activity

With a slightly altered WHERE condition to select last weeks activities:

CREATE VIEW timesheet_last_week AS
  select day,activity, (sum(minutes)/60) || ':' || (sum(minutes)%60) as hours
  from activities
  where strftime('%YW%W', day) = strftime('%YW%W', date('now', '-7 days')) 
  group by day,activity

There is even a view that indicates if you worked too much (100% = 480 minutes = 8 hours):

CREATE VIEW completeness_current_week AS
  select
   day,
   (sum(minutes)/60) || ':' || (sum(minutes)%60) as hours,
   round(((100.0/480.0)*sum(minutes)), 2) as percent
  from activities 
  where strftime('%YW%W', day) = strftime('%YW%W', date('now'))
  group by day

I use SQLite Professional (at that time it was free) to display the views. It automatically detects changes on the database file and refreshes the current view.