Mercurial > getan > getan
comparison getan/backend.py @ 550:28b1c18c900f
Specify project keys in getan-report with SQL patterns
* In getan-report project keys can now be specified with SQL patterns.
This allows projects and their sub-projects to be better selected for output.
% - zero, one, or multiple characters
_ - single character
| author | Magnus Schieder <mschieder@intevation.de> |
|---|---|
| date | Tue, 11 Feb 2020 15:46:37 +0100 |
| parents | fea767901dbc |
| children | fbe26b0e59ed |
comparison
equal
deleted
inserted
replaced
| 549:413cabeca333 | 550:28b1c18c900f |
|---|---|
| 78 FROM entries | 78 FROM entries |
| 79 GROUP BY project_id) ON project_id = id | 79 GROUP BY project_id) ON project_id = id |
| 80 WHERE active | 80 WHERE active |
| 81 ''' | 81 ''' |
| 82 | 82 |
| 83 LOAD_ACTIVE_PROJECTS_LIKE = ''' | |
| 84 SELECT id, key, description, total | |
| 85 FROM projects LEFT JOIN | |
| 86 (SELECT | |
| 87 project_id, | |
| 88 sum(strftime('%s', stop_time) - strftime('%s', start_time)) AS total | |
| 89 FROM entries | |
| 90 GROUP BY project_id) ON project_id = id | |
| 91 WHERE active and key LIKE :project_id | |
| 92 ''' | |
| 93 | |
| 83 LOAD_ACTIVE_PROJECT = ''' | 94 LOAD_ACTIVE_PROJECT = ''' |
| 84 SELECT id, key, description, total | 95 SELECT id, key, description, total |
| 85 FROM projects LEFT JOIN | 96 FROM projects LEFT JOIN |
| 86 (SELECT | 97 (SELECT |
| 87 project_id, | 98 project_id, |
| 270 return projects | 281 return projects |
| 271 | 282 |
| 272 finally: | 283 finally: |
| 273 close(cur) | 284 close(cur) |
| 274 | 285 |
| 286 def load_projects_like(self, key): | |
| 287 """ Loads active projects matching the SQL LIKE pattern from the | |
| 288 database and returns them as array. """ | |
| 289 cur = None | |
| 290 try: | |
| 291 cur = self.con.cursor() | |
| 292 cur.execute(LOAD_ACTIVE_PROJECTS_LIKE, {"project_id": key}) | |
| 293 | |
| 294 projects = [] | |
| 295 while True: | |
| 296 row = cur.fetchone() | |
| 297 | |
| 298 if not row: | |
| 299 break | |
| 300 # check key | |
| 301 if not row[1]: | |
| 302 raise InvalidProjectKeyError("Project with id %s needs " | |
| 303 "a key" % row[0]) | |
| 304 proj = Project(self, *row) | |
| 305 projects.append(proj) | |
| 306 | |
| 307 logger.info("found %i active projects." % len(projects)) | |
| 308 return projects | |
| 309 | |
| 310 finally: | |
| 311 close(cur) | |
| 312 | |
| 275 def load_recover(self): | 313 def load_recover(self): |
| 276 """If there is an entry in the recovery table, the entry is moved to | 314 """If there is an entry in the recovery table, the entry is moved to |
| 277 its project.""" | 315 its project.""" |
| 278 cor = None | 316 cor = None |
| 279 try: | 317 try: |