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:
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)