# HG changeset patch
# User Magnus Schieder <mschieder@intevation.de>
# Date 1581432397 -3600
# Node ID 28b1c18c900fda5ad7a2e9ff3dcb0ee9fb2e001d
# Parent  413cabeca3332f18ebb548527e298eedce940249
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

diff -r 413cabeca333 -r 28b1c18c900f CHANGES
--- 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.
 
diff -r 413cabeca333 -r 28b1c18c900f getan/__init__.py
--- 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__)
diff -r 413cabeca333 -r 28b1c18c900f getan/backend.py
--- 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."""
diff -r 413cabeca333 -r 28b1c18c900f getan/template.py
--- 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)
diff -r 413cabeca333 -r 28b1c18c900f scripts/getan-report
--- 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',