# HG changeset patch # User Sascha L. Teichmann # Date 1217607211 -7200 # Node ID 20414d892f041bf799a6fa2073a8c33300e1e247 # Parent 80f0e17208ba5792f0bdfb40c30dbecf90860ba9 Added script to generate lines for zeiterfassung.txt diff -r 80f0e17208ba -r 20414d892f04 ChangeLog --- a/ChangeLog Thu Jul 31 23:49:05 2008 +0200 +++ b/ChangeLog Fri Aug 01 18:13:31 2008 +0200 @@ -1,3 +1,14 @@ +2008-08-01 Sascha L. Teichmann + + * contrib/zeiterfassung: New. generates lines for zeiterfassung.txt. + usage: ./contrib/zeiterfassung + with + [--user=|-u ] : Name of user, default: $USER + [--database=|-d ]: getan database, default: time.db + [--project|-p] : Key of output project, default: all + [--list|-l] : list all projects + [--help|-h] : This text + 2008-07-31 Sascha L. Teichmann * getan: Feature wish 8: F1 is like -1, F2 is like -2 diff -r 80f0e17208ba -r 20414d892f04 contrib/zeiterfassung --- /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 +# +# 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 + with + [--user=|-u ] : Name of user, default: $USER + [--database=|-d ]: 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: