Mercurial > getan > getan
view getan/contrib/zeiterfassung.py @ 191:64624032611d
Add function to convert from iso to unix week
For evaluation we use the iso week as week parameter which is commonly known in
europe. But SQLite internally uses unix weeks which may differ. Therefore
convert the iso week to the unix week.
author | Björn Ricks <bjoern.ricks@intevation.de> |
---|---|
date | Thu, 17 Jan 2013 11:41:36 +0100 |
parents | 4f1c53bd8ba1 |
children | 9bb175cfaca3 |
line wrap: on
line source
#!/usr/bin/env python # -*- coding: utf-8 -*- # # zeiterfassung # ------------- # (c) 2008 by Sascha L. Teichmann <sascha.teichmann@intevation.de> # (c) 2011, 2012 by Björn Ricks <bjoern.ricks@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 import codecs import locale from pysqlite2 import dbapi2 as db from datetime import date, datetime, timedelta 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>] : Key of output project, default: all [--encoding=|-e encoding] : encoding of output, default: none [--week=]|-w <week>] : week of year [--year=]|-y <year>] : year [--list|-l] : list all projects [--help|-h] : This text [--emtpy|-m] : show empty projects [--lastweek|-c] : entries of last working week''' LIST_PROJECTS = ''' SELECT key, description, active FROM projects ''' PROJECT_ID_BY_KEY = ''' SELECT id, description FROM projects where key = :key ''' ALL_PROJECT_IDS = ''' SELECT id, key, description FROM projects ''' WEEK_ENTRIES = ''' SELECT date(start_time) AS t, sum(strftime('%s', stop_time) - strftime('%s', start_time)), 'no description' AS description FROM entries WHERE project_id = :project_id AND (strftime('%Y', start_time) ) = :year AND (description IS NULL or length(description) = 0) -- trim() function is missing AND (strftime('%W', start_time) = :week OR strftime('%W', stop_time) = :week) GROUP BY round(julianday(start_time)) UNION SELECT date(start_time) AS s, strftime('%s', stop_time) - strftime('%s', start_time), description FROM entries WHERE project_id = :project_id AND (strftime('%Y', start_time) ) = :year AND description IS NOT NULL AND length(description) > 0 AND (strftime('%W', start_time) = :week OR strftime('%W', stop_time) = :week) ORDER BY t ''' ENTRIES = ''' SELECT date(start_time), sum(strftime('%s', stop_time) - strftime('%s', start_time)), 'no description' AS 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 ''' ENTRIES_YEAR = ''' SELECT date(start_time), sum(strftime('%s', stop_time) - strftime('%s', start_time)), 'no description' AS 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 (strftime('%Y', start_time) ) = :year AND description IS NOT NULL AND length(description) > 0 ''' def unix_week(week, year=None): """Convert iso week to unix week For unix week see man date "%W" Args: week: Week number as int to convert year: Year as int. If year is none the current year is used. """ if not year: year = datetime.now().year firstday = date(year, 1, 4) isoweek = firstday.isocalendar()[1] unixweek = int(firstday.strftime("%W")) diff = isoweek - unixweek return week - diff def human_time(s): h = s / 3600 m = (s % 3600) / 60 if (s % 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 encoding = None week = None year = None empty_proj = False if os.path.isfile(DEFAULT_DATABASE): database = os.path.abspath(DEFAULT_DATABASE) else: database = os.path.expanduser(os.path.join("~", ".getan", DEFAULT_DATABASE)) opts, args = getopt.getopt( sys.argv[1:], 'd:u:p:e:hl:w:y:mc', ['database=', 'user=', 'project=', 'encoding=', 'help', 'list', 'week=', 'year=', 'empty', 'lastweek']) 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 ("--encoding", "-e"): encoding = val elif opt in ("--help", "-h"): usage() elif opt in ("--list", "-l"): list_projects = True elif opt in ("--week", "-w"): week = val elif opt in ("--lastweek", "-c") and not week: lastweek = (datetime.now()-timedelta(7)).isocalendar()[1] week = "%02d" % lastweek elif opt in ("--year", "-y"): year = val elif opt in ("--empty", "-m"): empty_proj = True if not user: user = os.getenv("USER") proj_year = year if not proj_year: proj_year = date.today().strftime("%Y") if not encoding: encoding = locale.getdefaultlocale()[1] Writer = codecs.getwriter(encoding) sys.stdout = Writer(sys.stdout) 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) if encoding: con.text_factory = lambda s: s.decode(encoding) 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, row[1]]] else: cur.execute(ALL_PROJECT_IDS); project_ids = cur.fetchall() total = 0 for project_id, project, proj_desc in project_ids: if not week is None: cur.execute(WEEK_ENTRIES, {'project_id': project_id, 'week': week, 'year' : proj_year}) elif not year: cur.execute(ENTRIES, {'project_id': project_id}) else: cur.execute(ENTRIES_YEAR, {'project_id': project_id, 'year':proj_year}) total_proj = 0 row = cur.fetchone() if row or empty_proj: print "# project: %s (%s)" % (project, proj_desc) while row: d = date(*map(int, row[0].split('-'))) t = max(60, row[1]) c = row[2] total_proj += t workpackage = "----" if c: m = WORKPACKAGE.match(c) if m: workpackage = m.group(1) c = c[m.end():].strip() c = c.replace('\x1b', '') print "%s %sh %s %-3s [%s] %s" % ( d.strftime("%d.%m.%Y"), human_time(t), TYPE_OF_ENTRY, user, workpackage, c) row = cur.fetchone() total += total_proj if empty_proj or total_proj > 0: print "# total: %sh\n\n" % human_time(total_proj) print "# total all projects: %sh\n\n" % 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: