timetrack

#!/usr/bin/env python
"""
Copyright (c) 2009, Aaron Bycoffe
All rights reserved.

Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions
are met:
1. Redistributions of source code must retain the above copyright
   notice, this list of conditions and the following disclaimer.
2. Redistributions in binary form must reproduce the above copyright
   notice, this list of conditions and the following disclaimer in the
   documentation and/or other materials provided with the distribution.
3. The name of the author may not be used to endorse or promote products
   derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS OR
IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT,
INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT
NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF
THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.


A script for tracking hours spent on projects. Data is stored in a sqlite
database.

If using a version of Python earlier than 2.5, the sqlite module is required.

Usage:
    $ timetrack -p testproject -t 4 # Record 4 hours on testproject
    Total hours spent on testproject: 4.0
    $ timetrack -p anotherproject -t 6.5 # Record 6.5 hours on anotherproject   
    Total hours spent on anotherproject: 6.5
    $ timetrack -p testproject -t 3.25 # Record 3.25 hours on testproject
    Total hours spent on testproject: 7.5
    $ timetrack # With no arguments, will show a report on all projects:
    Time tracking report for aaron
    --------------------------------------------------
    Project, Hours
    testproject, 7.5
    anotherproject, 6.5

    # With just a -p argument, will show a report on
    # the specified project.
    $ timetrack -p testproject
    Time tracking report for aaron
    --------------------------------------------------
    Project, Hours
    testproject, 7.5

    $ timetrack -d testproject # delete testproject
    Are you sure you want to delete testproject?
    [yes/no] yes
    Project testproject deleted


To do:
    - Reporting by time period (last N days, last N weeks, etc.)
    - Fuller report showing comments on each submission (when user uses -p
      without -t)
"""
from decimal import Decimal
import getopt
import os
import sqlite3
import sys
import time


def create_tables(cursor):
    """To be used the first time this script is run by this user.
    """
    queries = ["""CREATE TABLE projects
                    (id INTEGER PRIMARY KEY,
                     name TEXT)
               """,
               """CREATE TABLE hours
                    (project_id INTEGER,
                     hours REAL,
                     timestamp INTEGER,
                     comment TEXT
                     )
               """, ]
    for query in queries:
        cursor.execute(query)
        cursor.connection.commit()

def create_cursor():
    """If the database already exists, connect to it
    and return a cursor object.

    If not, create the database and tables, then
    return the cursor object.
    """
    db = '/'.join([os.getenv('HOME'), 'timetracker', ])
    if not os.path.isfile(db):
        connection = sqlite3.connect(db)
        cursor = connection.cursor()
        create_tables(cursor)
    else:
        connection = sqlite3.connect(db)
        cursor = connection.cursor()
    return cursor


def save_hours(cursor, project, hours, comment=None):
    """Save hours to the database.
    """
    # Check whether the project already exists.
    cursor.execute("SELECT id FROM projects WHERE name = ?", [str(project), ])
    result = cursor.fetchone()
    if result:
        project_id = result[0]
    else:
        cursor.execute("""INSERT INTO projects
                          (name)
                          VALUES
                          (?)""", [project, ])
        cursor.connection.commit()
        project_id = cursor.lastrowid

    cursor.execute("""INSERT INTO hours
                        (project_id, hours, timestamp, comment)
                        VALUES
                        (?, ?, ?, ?)""",
                    [project_id, float(hours), int(time.time()), comment, ])
    cursor.connection.commit()

    return project_id, cursor.lastrowid

def get_project_hours(cursor, project_id):
    """Calculate the total number of hours spent on the given project.
    """
    cursor.execute("SELECT SUM(hours) FROM hours WHERE project_id = ?",
            [project_id, ])
    cursor.connection.commit()
    return cursor.fetchone()[0]


def print_report(cursor, project=None):
    if not project:
        cursor.execute("""SELECT p.name, sum(h.hours) FROM projects p INNER JOIN hours
                h ON p.id = h.project_id GROUP BY p.name""")
    else:
        cursor.execute("""SELECT p.name, sum(h.hours) FROM projects p INNER JOIN hours
                h ON p.id = h.project_id 
                WHERE p.name = ?
                GROUP BY p.name""", [project, ])
    print "Time tracking report for %s" % os.getenv('USER')
    print "-"*50
    print "Project, Hours"
    for name, hours in cursor.fetchall():
        print ', '.join([name, unicode(hours),])


def delete_project(cursor, project):
    delete = raw_input('Are you sure you want to delete %s?\n[yes/no]  ' % project)
    if delete != 'yes':
        return False

    # Get project ID so its hours can be deleted.
    cursor.execute("SELECT id FROM projects WHERE name = ?", [project, ])
    if not cursor.rowcount:
        return False

    id = cursor.fetchone()[0]
    cursor.execute("DELETE FROM hours WHERE project_id = ?", [id, ])
    cursor.execute("DELETE FROM projects WHERE id = ?", [id, ])
    cursor.connection.commit()
    return True


def _main():
    cursor = create_cursor()

    opts, args = getopt.getopt(sys.argv[1:], "p:t:m:d:", [])

    if not opts:
        print_report(cursor)
        sys.exit(os.EX_OK)

    opts = dict(opts)

    if '-d' in opts: # delete a project
        project = opts['-d']
        deleted = delete_project(cursor, project)
        if deleted:
            print 'Project %s deleted' % project
        else:
            print 'No projects deleted'
        sys.exit(os.EX_OK)

    if '-p' in opts and '-t' not in opts: # single-project report
        project = opts['-p']
        print_report(cursor, project)
        sys.exit(os.EX_OK)

    #if '-p' not in opts or '-t' not in opts:
    #    raise ValueError("You must specify both the project name and number of hours")

    project = opts['-p']
    hours = opts['-t']
    try:
        hours = Decimal(hours)
    except ValueError:
        sys.exit(os.EX_DATAERR)

    comment = None
    if '-m' in opts:
        comment = opts['-m']

    project_id, hours_id = save_hours(cursor, project, hours, comment)

    total_project_hours = get_project_hours(cursor, project_id)

    print "Total hours spent on %s: %s" % (project, total_project_hours)


if __name__ == '__main__':
    _main()