Mercurial > getan
changeset 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 | 413cabeca333 |
children | d44ee9cd7261 |
files | CHANGES getan/__init__.py getan/backend.py getan/template.py scripts/getan-report |
diffstat | 5 files changed, 47 insertions(+), 4 deletions(-) [+] |
line wrap: on
line diff
--- a/CHANGES Mon Feb 03 15:51:33 2020 +0100 +++ b/CHANGES Tue Feb 11 15:46:37 2020 +0100 @@ -1,5 +1,11 @@ (unreleased) + * 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 + Patch by Magnus Schieder + * Improve getan-report template zeiterfassung-hierarchy1: Remove daily summary value and match the German date formatting.
--- a/getan/__init__.py Mon Feb 03 15:51:33 2020 +0100 +++ b/getan/__init__.py Tue Feb 11 15:46:37 2020 +0100 @@ -6,5 +6,5 @@ # This is Free Software licensed under the terms of GPLv3 or later. # For details see LICENSE coming with the source of 'getan'. -__version_info__ = ("3", "4") +__version_info__ = ("3", "5" "dev1") __version__ = '.'.join(__version_info__)
--- a/getan/backend.py Mon Feb 03 15:51:33 2020 +0100 +++ b/getan/backend.py Tue Feb 11 15:46:37 2020 +0100 @@ -80,6 +80,17 @@ WHERE active ''' +LOAD_ACTIVE_PROJECTS_LIKE = ''' +SELECT id, key, description, total +FROM projects LEFT JOIN +(SELECT + project_id, + sum(strftime('%s', stop_time) - strftime('%s', start_time)) AS total + FROM entries + GROUP BY project_id) ON project_id = id + WHERE active and key LIKE :project_id +''' + LOAD_ACTIVE_PROJECT = ''' SELECT id, key, description, total FROM projects LEFT JOIN @@ -272,6 +283,33 @@ finally: close(cur) + def load_projects_like(self, key): + """ Loads active projects matching the SQL LIKE pattern from the + database and returns them as array. """ + cur = None + try: + cur = self.con.cursor() + cur.execute(LOAD_ACTIVE_PROJECTS_LIKE, {"project_id": key}) + + projects = [] + while True: + row = cur.fetchone() + + if not row: + break + # check key + if not row[1]: + raise InvalidProjectKeyError("Project with id %s needs " + "a key" % row[0]) + proj = Project(self, *row) + projects.append(proj) + + logger.info("found %i active projects." % len(projects)) + return projects + + finally: + close(cur) + def load_recover(self): """If there is an entry in the recovery table, the entry is moved to its project."""
--- a/getan/template.py Mon Feb 03 15:51:33 2020 +0100 +++ b/getan/template.py Tue Feb 11 15:46:37 2020 +0100 @@ -106,8 +106,7 @@ if not project: projects = backend.load_projects() else: - project = backend.load_project(project) - projects = [project] + projects = backend.load_projects_like(project) if week is not None: u_week = "%02d" % unix_week(week, year)
--- a/scripts/getan-report Mon Feb 03 15:51:33 2020 +0100 +++ b/scripts/getan-report Tue Feb 11 15:46:37 2020 +0100 @@ -35,7 +35,7 @@ external templates must be stored in ~/.getan/templates/ to be able to call them.""") parser.add_argument('-u', '--user', help='name of user') - parser.add_argument('-p', '--project', help='key of output project') + parser.add_argument('-p', '--project', help='key (sql pattern) of output project') parser.add_argument('-w', '--week', type=int, help='week of year') parser.add_argument('-y', '--year', type=int, help='year') parser.add_argument('-c', '--lastweek', help='entries of last working week',