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