Mercurial > getan
comparison contrib/zeiterfassung @ 8:20414d892f04
Added script to generate lines for zeiterfassung.txt
author | Sascha L. Teichmann <teichmann@intevation.de> |
---|---|
date | Fri, 01 Aug 2008 18:13:31 +0200 |
parents | |
children | 4e8f5545256d |
comparison
equal
deleted
inserted
replaced
7:80f0e17208ba | 8:20414d892f04 |
---|---|
1 #!/usr/bin/env python | |
2 # -*- coding: utf-8 -*- | |
3 # | |
4 # zeiterfassung | |
5 # ------------- | |
6 # (c) 2008 by Sascha L. Teichmann <sascha.teichmann@intevation.de> | |
7 # | |
8 # Simple script which generates lines for zeiterfassung.txt files. | |
9 # | |
10 # This is Free Software licensed under the terms of GPLv3 or later. | |
11 # For details see LICENSE coming with the source of 'getan'. | |
12 # | |
13 import os | |
14 import os.path | |
15 import sys | |
16 import getopt | |
17 import re | |
18 | |
19 from pysqlite2 import dbapi2 as db | |
20 | |
21 from datetime import date | |
22 | |
23 DEFAULT_DATABASE = "time.db" | |
24 | |
25 TYPE_OF_ENTRY = "-" | |
26 | |
27 WORKPACKAGE = re.compile("^\[(\w*)(\s|\])") | |
28 | |
29 USAGE = '''usage: %s <options> | |
30 with <options> | |
31 [--user=|-u <user>] : Name of user, default: $USER | |
32 [--database=|-d <database>]: getan database, default: time.db | |
33 [--project|-p] : Key of output project, default: all | |
34 [--list|-l] : list all projects | |
35 [--help|-h] : This text''' | |
36 | |
37 LIST_PROJECTS = ''' | |
38 SELECT key, description, active FROM projects | |
39 ''' | |
40 | |
41 PROJECT_ID_BY_KEY = ''' | |
42 SELECT id FROM projects where key = :key | |
43 ''' | |
44 | |
45 ALL_PROJECT_IDS = ''' | |
46 SELECT id, key FROM projects; | |
47 ''' | |
48 | |
49 ENTRIES = ''' | |
50 SELECT | |
51 date(start_time), | |
52 sum(strftime('%s', stop_time) - strftime('%s', start_time)), | |
53 'no description' | |
54 FROM entries | |
55 WHERE | |
56 project_id = :project_id AND | |
57 (description IS NULL or length(description) = 0) -- trim() function is missing | |
58 GROUP BY round(julianday(start_time)) | |
59 UNION | |
60 SELECT date(start_time), strftime('%s', stop_time) - strftime('%s', start_time), description | |
61 FROM entries | |
62 WHERE | |
63 project_id = :project_id AND | |
64 description IS NOT NULL AND length(description) > 0 | |
65 ''' | |
66 | |
67 def human_time(s): | |
68 s_h = s % 3600 | |
69 h = s / 3600 | |
70 m = s_h / 60 | |
71 if (s_h % 60) >= 30: | |
72 m += 1 | |
73 if m == 60: | |
74 m = 0 | |
75 h += 1 | |
76 return "%2d:%02d" % (h, m) | |
77 | |
78 class TermError(Exception): | |
79 | |
80 def __init__(self, msg): | |
81 Exception.__init__(self) | |
82 self.msg = msg | |
83 | |
84 def __str__(self): | |
85 return repr(self.msg) | |
86 | |
87 def tolerantClose(c): | |
88 if c: | |
89 try: c.close() | |
90 except: pass | |
91 | |
92 def usage(exit_code = 0): | |
93 print USAGE % sys.argv[0] | |
94 sys.exit(exit_code) | |
95 | |
96 def main(): | |
97 | |
98 database = DEFAULT_DATABASE | |
99 user = None | |
100 list_projects = False | |
101 project = None | |
102 | |
103 opts, args = getopt.getopt( | |
104 sys.argv[1:], | |
105 'd:u:p:hl', | |
106 ['database=', 'user=', 'project', 'help', 'list']) | |
107 | |
108 for opt, val in opts: | |
109 if opt in ("--database", "-d"): | |
110 database = val | |
111 elif opt in ("--user", "-u"): | |
112 user = val | |
113 elif opt in ("--project", "-p"): | |
114 project = val | |
115 elif opt in ("--help", "-h"): | |
116 usage() | |
117 elif opt in ("--list", "-l"): | |
118 list_projects = True | |
119 | |
120 if not user: | |
121 user = os.getenv("USER") | |
122 | |
123 if not os.path.isfile(database): | |
124 print >> sys.stderr, "'%s' does not exist or is not a file." % database | |
125 sys.exit(1) | |
126 | |
127 con, cur = None, None | |
128 try: | |
129 try: | |
130 con = db.connect(database) | |
131 cur = con.cursor() | |
132 | |
133 if list_projects: | |
134 cur.execute(LIST_PROJECTS) | |
135 while True: | |
136 row = cur.fetchone() | |
137 if not row: break | |
138 print "%s %s %s" % (row[0], row[2] and "*" or "-", row[1]) | |
139 else: | |
140 if project: | |
141 cur.execute(PROJECT_ID_BY_KEY, { 'key': project }) | |
142 row = cur.fetchone() | |
143 if row is None: | |
144 raise TermError("There is no project with key '%s'" % project) | |
145 project_ids = [[row[0], project]] | |
146 else: | |
147 cur.execute(ALL_PROJECT_IDS); | |
148 project_ids = cur.fetchall() | |
149 | |
150 for project_id, project in project_ids: | |
151 print "# project: %s" % project | |
152 cur.execute(ENTRIES, {'project_id': project_id}) | |
153 total = 0 | |
154 while True: | |
155 row = cur.fetchone() | |
156 if not row: break | |
157 d = date(*map(int, row[0].split('-'))) | |
158 t = max(60, row[1]) | |
159 c = row[2] | |
160 total += t | |
161 workpackage = "----" | |
162 if c: | |
163 m = WORKPACKAGE.match(c) | |
164 if m: | |
165 workpackage = m.group(1) | |
166 c = c[m.end():].strip() | |
167 print "%s %sh %s %3s [%s] %s" % ( | |
168 d.strftime("%d.%m.%Y"), | |
169 human_time(t), | |
170 TYPE_OF_ENTRY, | |
171 user, | |
172 workpackage, | |
173 c) | |
174 print "# total: %sh" % human_time(total) | |
175 finally: | |
176 tolerantClose(cur) | |
177 tolerantClose(con) | |
178 | |
179 except TermError, e: | |
180 print >> sys.stderr, "error: %s" % e.msg | |
181 | |
182 if __name__ == '__main__': | |
183 main() | |
184 | |
185 # vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8: |