--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/contrib/zeiterfassung	Fri Aug 01 18:13:31 2008 +0200
@@ -0,0 +1,185 @@
+#!/usr/bin/env python
+# -*- coding: utf-8 -*-
+#
+# zeiterfassung
+# -------------
+# (c) 2008 by Sascha L. Teichmann <sascha.teichmann@intevation.de>
+#
+# Simple script which generates lines for zeiterfassung.txt files.
+#
+# This is Free Software licensed under the terms of GPLv3 or later.
+# For details see LICENSE coming with the source of 'getan'.
+#
+import os
+import os.path
+import sys
+import getopt
+import re
+
+from pysqlite2 import dbapi2 as db
+
+from datetime import date
+
+DEFAULT_DATABASE = "time.db"
+
+TYPE_OF_ENTRY = "-"
+
+WORKPACKAGE = re.compile("^\[(\w*)(\s|\])")
+
+USAGE = '''usage: %s <options>
+    with <options>
+    [--user=|-u <user>]        : Name of user,   default: $USER
+    [--database=|-d <database>]: getan database, default: time.db
+    [--project|-p]             : Key of output project, default: all
+    [--list|-l]                : list all projects
+    [--help|-h]                : This text'''
+
+LIST_PROJECTS = '''
+SELECT key, description, active FROM projects
+'''
+
+PROJECT_ID_BY_KEY = '''
+SELECT id FROM projects where key = :key
+'''
+
+ALL_PROJECT_IDS = '''
+SELECT id, key FROM projects;
+'''
+
+ENTRIES = '''
+SELECT 
+    date(start_time), 
+    sum(strftime('%s', stop_time) - strftime('%s', start_time)), 
+    'no description'
+FROM entries 
+WHERE 
+    project_id = :project_id AND 
+    (description IS NULL or length(description) = 0) -- trim() function is missing
+GROUP BY round(julianday(start_time))
+UNION
+SELECT date(start_time), strftime('%s', stop_time) - strftime('%s', start_time), description
+FROM entries 
+WHERE 
+    project_id = :project_id AND 
+    description IS NOT NULL AND length(description) > 0
+'''
+
+def human_time(s):
+    s_h = s % 3600
+    h = s / 3600
+    m = s_h / 60
+    if (s_h % 60) >= 30:
+        m += 1
+        if m == 60:
+            m = 0
+            h += 1
+    return "%2d:%02d" % (h, m)
+
+class TermError(Exception):
+
+    def __init__(self, msg):
+        Exception.__init__(self)
+        self.msg = msg
+
+    def __str__(self):
+        return repr(self.msg)
+
+def tolerantClose(c):
+    if c:
+        try: c.close()
+        except: pass
+
+def usage(exit_code = 0):
+    print USAGE % sys.argv[0]
+    sys.exit(exit_code)
+
+def main():
+
+    database      = DEFAULT_DATABASE
+    user          = None
+    list_projects = False
+    project       = None
+
+    opts, args = getopt.getopt(
+        sys.argv[1:],
+        'd:u:p:hl',
+        ['database=', 'user=', 'project', 'help', 'list'])
+
+    for opt, val in opts:
+        if opt in ("--database", "-d"):
+            database = val
+        elif opt in ("--user", "-u"):
+            user = val
+        elif opt in ("--project", "-p"):
+            project = val
+        elif opt in ("--help", "-h"):
+            usage()
+        elif opt in ("--list", "-l"):
+            list_projects = True
+
+    if not user:
+        user = os.getenv("USER")
+
+    if not os.path.isfile(database):
+        print >> sys.stderr, "'%s' does not exist or is not a file." % database
+        sys.exit(1)
+
+    con, cur = None, None
+    try:
+        try:
+            con = db.connect(database)
+            cur = con.cursor()
+
+            if list_projects:
+                cur.execute(LIST_PROJECTS)
+                while True:
+                    row = cur.fetchone()
+                    if not row: break
+                    print "%s %s %s" % (row[0], row[2] and "*" or "-", row[1])
+            else:
+                if project:
+                    cur.execute(PROJECT_ID_BY_KEY, { 'key': project })
+                    row = cur.fetchone()
+                    if row is None:
+                        raise TermError("There is no project with key '%s'" % project)
+                    project_ids = [[row[0], project]]
+                else:
+                    cur.execute(ALL_PROJECT_IDS);
+                    project_ids = cur.fetchall()
+
+                for project_id, project in project_ids:
+                    print "# project: %s" % project
+                    cur.execute(ENTRIES, {'project_id': project_id})
+                    total = 0
+                    while True:
+                        row = cur.fetchone()
+                        if not row: break
+                        d = date(*map(int, row[0].split('-'))) 
+                        t = max(60, row[1])
+                        c = row[2]
+                        total += t
+                        workpackage = "----"
+                        if c:
+                            m = WORKPACKAGE.match(c)
+                            if m:
+                                workpackage = m.group(1)
+                                c = c[m.end():].strip()
+                        print "%s %sh %s %3s [%s] %s" % (
+                            d.strftime("%d.%m.%Y"),
+                            human_time(t),
+                            TYPE_OF_ENTRY,
+                            user,
+                            workpackage,
+                            c)
+                    print "# total: %sh" % human_time(total)
+        finally:
+            tolerantClose(cur)
+            tolerantClose(con)
+
+    except TermError, e:
+        print >> sys.stderr, "error: %s" % e.msg
+
+if __name__ == '__main__':
+    main()
+
+# vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8: