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@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: bjoern@57: 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@10: [--encoding=|-e encoding] : encoding of output, default: none teichmann@13: [--week=]|-w ] : week of year bjoern@51: [--year=]|-y ] : 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 bricks@46: (strftime('%Y', start_time) ) = :year 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 bricks@46: (strftime('%Y', start_time) ) = :year 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 bjoern@51: description IS NOT NULL AND length(description) > 0 bjoern@51: ''' bjoern@51: bjoern@51: ENTRIES_YEAR = ''' bjoern@51: SELECT bjoern@51: date(start_time), bjoern@51: sum(strftime('%s', stop_time) - strftime('%s', start_time)), bjoern@51: 'no description' AS description bjoern@51: FROM entries bjoern@51: WHERE bjoern@51: project_id = :project_id AND bjoern@51: (description IS NULL or length(description) = 0) -- trim() function is missing bjoern@51: GROUP BY round(julianday(start_time)) bjoern@51: UNION bjoern@51: SELECT date(start_time), strftime('%s', stop_time) - strftime('%s', start_time), description bjoern@51: FROM entries bjoern@51: WHERE bjoern@51: project_id = :project_id AND bricks@46: (strftime('%Y', start_time) ) = :year 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 bricks@46: year = None teichmann@8: teichmann@8: opts, args = getopt.getopt( teichmann@8: sys.argv[1:], bjoern@51: 'd:u:p:e:hl:w:y:', bricks@46: ['database=', 'user=', 'project=', 'encoding=', 'help', 'list', 'week=', 'year=']) 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 bricks@46: elif opt in ("--year", "-y"): bricks@46: year = val teichmann@8: teichmann@8: if not user: teichmann@8: user = os.getenv("USER") teichmann@8: bjoern@51: proj_year = year bjoern@51: if not proj_year: bjoern@51: proj_year = date.today().strftime("%Y") bricks@46: 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: bjoern@51: cur.execute(WEEK_ENTRIES, {'project_id': project_id, 'week': week, 'year' : proj_year}) bjoern@51: elif not year: bjoern@51: cur.execute(ENTRIES, {'project_id': project_id}) teichmann@13: else: bjoern@51: cur.execute(ENTRIES_YEAR, {'project_id': project_id, 'year':proj_year}) 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() bricks@46: c = c.replace('\x1b', '') 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: