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 <sascha.teichmann@intevation.de>
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@10: import codecs
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 <options>
teichmann@8: with <options>
teichmann@9: [--user=|-u <user>] : Name of user, default: $USER
teichmann@9: [--database=|-d <database>]: getan database, default: time.db
teichmann@9: [--project=|-p <key>] : Key of output project, default: all
teichmann@10: [--encoding=|-e encoding] : encoding of output, default: none
teichmann@13: [--week=]|-w <week>] : week of year
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@13: WEEK_ENTRIES = '''
teichmann@13: SELECT
teichmann@13: date(start_time) AS t,
teichmann@13: sum(strftime('%s', stop_time) - strftime('%s', start_time)),
teichmann@13: 'no description' AS description
teichmann@13: FROM entries
teichmann@13: WHERE
teichmann@13: project_id = :project_id AND
teichmann@13: (description IS NULL or length(description) = 0) -- trim() function is missing
teichmann@13: AND (strftime('%W', start_time) = :week OR strftime('%W', stop_time) = :week)
teichmann@13: GROUP BY round(julianday(start_time))
teichmann@13: UNION
teichmann@13: SELECT date(start_time) AS s, strftime('%s', stop_time) - strftime('%s', start_time), description
teichmann@13: FROM entries
teichmann@13: WHERE
teichmann@13: project_id = :project_id AND
teichmann@13: description IS NOT NULL AND length(description) > 0
teichmann@13: AND (strftime('%W', start_time) = :week OR strftime('%W', stop_time) = :week)
teichmann@13: ORDER BY t
teichmann@13: '''
teichmann@13:
teichmann@8: ENTRIES = '''
teichmann@8: SELECT
teichmann@8: date(start_time),
teichmann@8: sum(strftime('%s', stop_time) - strftime('%s', start_time)),
teichmann@11: 'no description' AS 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: h = s / 3600
teichmann@11: m = (s % 3600) / 60
teichmann@11: if (s % 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@10: encoding = None
teichmann@13: week = None
teichmann@8:
teichmann@8: opts, args = getopt.getopt(
teichmann@8: sys.argv[1:],
teichmann@13: 'd:u:p:e:hl:w:',
teichmann@13: ['database=', 'user=', 'project=', 'encoding=', 'help', 'list', 'week='])
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@10: elif opt in ("--encoding", "-e"):
teichmann@10: encoding = val
teichmann@8: elif opt in ("--help", "-h"):
teichmann@8: usage()
teichmann@8: elif opt in ("--list", "-l"):
teichmann@8: list_projects = True
teichmann@13: elif opt in ("--week", "-w"):
teichmann@13: week = val
teichmann@8:
teichmann@8: if not user:
teichmann@8: user = os.getenv("USER")
teichmann@8:
teichmann@10: if encoding:
teichmann@10: Writer = codecs.getwriter(encoding)
teichmann@10: sys.stdout = Writer(sys.stdout)
teichmann@10:
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@11: if encoding:
teichmann@11: con.text_factory = lambda s: s.decode(encoding)
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@13: if not week is None:
teichmann@13: cur.execute(WEEK_ENTRIES, {'project_id': project_id, 'week': week})
teichmann@13: else:
teichmann@13: 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()
thomas@16: 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: