Passed
Push — master ( 171f0a...e75045 )
by Alexander
02:52 queued 01:17
created

things3.things3.Things3.get_seinfeld()   A

Complexity

Conditions 1

Size

Total Lines 7
Code Lines 5

Duplication

Lines 0
Ratio 0 %

Importance

Changes 0
Metric Value
cc 1
eloc 5
nop 2
dl 0
loc 7
rs 10
c 0
b 0
f 0
1
#!/usr/bin/env python3
2
# -*- coding: utf-8 -*-
3
4
"""Simple read-only API for Things 3."""
5
6
from __future__ import print_function
7
8
import sys
9
from random import shuffle
10
from datetime import datetime, timedelta
11
import os
12
from os import environ, path
13
import getpass
14
import configparser
15
from pathlib import Path
16
import sqlite3
17
import webbrowser
18
19
# the new core library, migration ongoing
20
import things
21
from things3 import __version__
22
23
# pylint: disable=R0904,R0902
24
25
26
class Things3:
27
    """Simple read-only API for Things 3."""
28
29
    # Database info
30
    FILE_CONFIG = str(Path.home()) + "/.kanbanviewrc"
31
    FILE_DB = (
32
        "/Library/Group Containers/"
33
        "JLMPQHK86H.com.culturedcode.ThingsMac/"
34
        "Things Database.thingsdatabase/main.sqlite"
35
    )
36
    TABLE_TASK = "TMTask"
37
    TABLE_AREA = "TMArea"
38
    TABLE_TAG = "TMTag"
39
    TABLE_TASKTAG = "TMTaskTag"
40
    DATE_CREATE = "creationDate"
41
    DATE_MOD = "userModificationDate"
42
    DATE_DUE = "dueDate"
43
    DATE_START = "startDate"
44
    DATE_STOP = "stopDate"
45
    IS_INBOX = "start = 0"  # noqa
46
    IS_ANYTIME = "start = 1"
47
    IS_SOMEDAY = "start = 2"
48
    IS_SCHEDULED = f"{DATE_START} IS NOT NULL"
49
    IS_NOT_SCHEDULED = f"{DATE_START} IS NULL"
50
    IS_DUE = f"{DATE_DUE} IS NOT NULL"  # noqa
51
    IS_RECURRING = "recurrenceRule IS NOT NULL"
52
    IS_NOT_RECURRING = "recurrenceRule IS NULL"  # noqa
53
    IS_TASK = "type = 0"
54
    IS_PROJECT = "type = 1"
55
    IS_HEADING = "type = 2"
56
    IS_TRASHED = "trashed = 1"
57
    IS_NOT_TRASHED = "trashed = 0"
58
    IS_OPEN = "status = 0"
59
    IS_CANCELLED = "status = 2"
60
    IS_DONE = "status = 3"
61
    RECURRING_IS_NOT_PAUSED = "instanceCreationPaused = 0"
62
    RECURRING_HAS_NEXT_STARTDATE = "nextInstanceStartDate IS NOT NULL"
63
    MODE_TASK = "type = 0"
64
    MODE_PROJECT = "type = 1"
65
66
    # Variables
67
    debug = False
68
    user = getpass.getuser()
69
    database = f"/Users/{user}/{FILE_DB}"
70
    filter = ""
71
    tag_waiting = "Waiting"
72
    tag_mit = "MIT"
73
    tag_cleanup = "Cleanup"
74
    tag_seinfeld = "Seinfeld"
75
    tag_a = "A"
76
    tag_b = "B"
77
    tag_c = "C"
78
    tag_d = "D"
79
    stat_days = 365
80
    anonymize = False
81
    config = configparser.ConfigParser()
82
    config.read(FILE_CONFIG, encoding="utf-8")
83
    mode = "to-do"
84
    filter_project = None
85
    filter_area = None
86
    debug_text = ""
87
88
    # pylint: disable=R0913
89
    def __init__(
90
        self,
0 ignored issues
show
Coding Style introduced by
Wrong hanging indentation before block (add 4 spaces).
Loading history...
91
        database=None,
0 ignored issues
show
Coding Style introduced by
Wrong hanging indentation before block (add 4 spaces).
Loading history...
92
        tag_waiting=None,
0 ignored issues
show
Coding Style introduced by
Wrong hanging indentation before block (add 4 spaces).
Loading history...
93
        tag_mit=None,
0 ignored issues
show
Coding Style introduced by
Wrong hanging indentation before block (add 4 spaces).
Loading history...
94
        tag_cleanup=None,
0 ignored issues
show
Coding Style introduced by
Wrong hanging indentation before block (add 4 spaces).
Loading history...
95
        tag_seinfeld=None,
0 ignored issues
show
Coding Style introduced by
Wrong hanging indentation before block (add 4 spaces).
Loading history...
96
        tag_a=None,
0 ignored issues
show
Coding Style introduced by
Wrong hanging indentation before block (add 4 spaces).
Loading history...
97
        tag_b=None,
0 ignored issues
show
Coding Style introduced by
Wrong hanging indentation before block (add 4 spaces).
Loading history...
98
        tag_c=None,
0 ignored issues
show
Coding Style introduced by
Wrong hanging indentation before block (add 4 spaces).
Loading history...
99
        tag_d=None,
0 ignored issues
show
Coding Style introduced by
Wrong hanging indentation before block (add 4 spaces).
Loading history...
100
        stat_days=None,
0 ignored issues
show
Coding Style introduced by
Wrong hanging indentation before block (add 4 spaces).
Loading history...
101
        anonymize=None,
0 ignored issues
show
Coding Style introduced by
Wrong hanging indentation before block (add 4 spaces).
Loading history...
102
        debug_text="",
0 ignored issues
show
Coding Style introduced by
Wrong hanging indentation before block (add 4 spaces).
Loading history...
103
    ):
104
        self.debug_text = debug_text
105
106
        cfg = self.get_from_config(tag_waiting, "TAG_WAITING")
107
        self.tag_waiting = cfg if cfg else self.tag_waiting
108
        self.set_config("TAG_WAITING", self.tag_waiting)
109
110
        cfg = self.get_from_config(anonymize, "ANONYMIZE")
111
        self.anonymize = (cfg == "True") if (cfg == "True") else self.anonymize
112
        self.set_config("ANONYMIZE", self.anonymize)
113
114
        cfg = self.get_from_config(tag_mit, "TAG_MIT")
115
        self.tag_mit = cfg if cfg else self.tag_mit
116
        self.set_config("TAG_MIT", self.tag_mit)
117
118
        cfg = self.get_from_config(tag_cleanup, "TAG_CLEANUP")
119
        self.tag_cleanup = cfg if cfg else self.tag_cleanup
120
        self.set_config("TAG_CLEANUP", self.tag_cleanup)
121
122
        cfg = self.get_from_config(tag_seinfeld, "TAG_SEINFELD")
123
        self.tag_seinfeld = cfg if cfg else self.tag_seinfeld
124
        self.set_config("TAG_SEINFELD", self.tag_seinfeld)
125
126
        cfg = self.get_from_config(tag_a, "TAG_A")
127
        self.tag_a = cfg if cfg else self.tag_a
128
        self.set_config("TAG_A", self.tag_a)
129
130
        cfg = self.get_from_config(tag_b, "TAG_B")
131
        self.tag_b = cfg if cfg else self.tag_b
132
        self.set_config("TAG_B", self.tag_b)
133
134
        cfg = self.get_from_config(tag_c, "TAG_C")
135
        self.tag_c = cfg if cfg else self.tag_c
136
        self.set_config("TAG_C", self.tag_c)
137
138
        cfg = self.get_from_config(tag_d, "TAG_D")
139
        self.tag_d = cfg if cfg else self.tag_d
140
        self.set_config("TAG_D", self.tag_d)
141
142
        cfg = self.get_from_config(stat_days, "STAT_DAYS")
143
        self.stat_days = cfg if cfg else self.stat_days
144
        self.set_config("STAT_DAYS", self.stat_days)
145
146
        cfg = self.get_from_config(database, "THINGSDB")
147
        self.database = cfg if cfg else self.database
148
        # Automated migration to new database location in Things 3.12.6/3.13.1
149
        # --------------------------------
150
        try:
151
            with open(self.database, encoding="utf-8") as f_d:
152
                if "Your database file has been moved there" in f_d.readline():
153
                    self.database = f"/Users/{self.user}/{self.FILE_DB}"
154
        except (UnicodeDecodeError, FileNotFoundError, PermissionError):
155
            pass  # binary file (old database) or doesn't exist
156
        # --------------------------------
157
        self.set_config("THINGSDB", self.database)
158
159
    def set_config(self, key, value, domain="DATABASE"):
160
        """Write variable to config."""
161
        if domain not in self.config:
162
            self.config.add_section(domain)
163
        if value is not None and key is not None:
164
            self.config.set(domain, str(key), str(value))
165
            with open(self.FILE_CONFIG, "w+", encoding="utf-8") as configfile:
166
                self.config.write(configfile)
167
168
    def get_config(self, key, domain="DATABASE"):
169
        """Get variable from config."""
170
        result = None
171
        if domain in self.config and key in self.config[domain]:
172
            result = path.expanduser(self.config[domain][key])
173
        return result
174
175
    def get_from_config(self, variable, key, domain="DATABASE"):
176
        """Set variable. Priority: input, environment, config"""
177
        result = None
178
        if variable is not None:
179
            result = variable
180
        elif environ.get(key):
181
            result = environ.get(key)
182
        elif domain in self.config and key in self.config[domain]:
183
            result = path.expanduser(self.config[domain][key])
184
        return result
185
186
    @staticmethod
187
    def anonymize_string(string):
188
        """Scramble text."""
189
        if string is None:
190
            return None
191
        string = list(string)
192
        shuffle(string)
193
        string = "".join(string)
194
        return string
195
196
    @staticmethod
197
    def dict_factory(cursor, row):
198
        """Convert SQL result into a dictionary"""
199
        dictionary = {}
200
        for idx, col in enumerate(cursor.description):
201
            dictionary[col[0]] = row[idx]
202
        return dictionary
203
204
    def anonymize_tasks(self, tasks):
205
        """Scramble output for screenshots."""
206
        if self.anonymize:
207
            for task in tasks:
208
                task["title"] = self.anonymize_string(task["title"])
209
                task["context"] = (
210
                    self.anonymize_string(task["context"]) if "context" in task else ""
211
                )
212
        return tasks
213
214
    def defaults(self):
215
        """Some default options for the new API."""
216
        return dict(
217
            type=self.mode,
218
            project=self.filter_project,
219
            area=self.filter_area,
220
            filepath=self.database,
221
        )
222
223
    def convert_new_things_lib(self, tasks):
224
        """Convert tasks from new library to old expectations."""
225
        for task in tasks:
226
            task["context"] = (
227
                task.get("project_title")
228
                or task.get("area_title")
229
                or task.get("heading_title")
230
            )
231
            task["context_uuid"] = (
232
                task.get("project") or task.get("area") or task.get("heading")
233
            )
234
            task["due"] = task.get("deadline")
235
            task["started"] = task.get("start_date")
236
            task["size"] = things.projects(
237
                task["uuid"], count_only=True, filepath=self.database
238
            )
239
        tasks.sort(key=lambda task: task["title"] or "", reverse=False)
240
        tasks = self.anonymize_tasks(tasks)
241
        return tasks
242
243
    def get_inbox(self):
244
        """Get tasks from inbox."""
245
        tasks = things.inbox(**self.defaults())
246
        tasks = self.convert_new_things_lib(tasks)
247
        return tasks
248
249
    def get_today(self):
250
        """Get tasks from today."""
251
        tasks = things.today(**self.defaults())
252
        tasks = self.convert_new_things_lib(tasks)
253
        tasks.sort(key=lambda task: task.get("started", ""), reverse=True)
254
        tasks.sort(key=lambda task: task.get("todayIndex", ""), reverse=False)
255
        return tasks
256
257
    def get_task(self, area=None, project=None):
258
        """Get tasks."""
259
        tasks = things.tasks(area=area, project=project, filepath=self.database)
260
        tasks = self.convert_new_things_lib(tasks)
261
        return tasks
262
263
    def get_someday(self):
264
        """Get someday tasks."""
265
        tasks = things.someday(**self.defaults())
266
        tasks = self.convert_new_things_lib(tasks)
267
        tasks.sort(key=lambda task: task["deadline"] or "", reverse=True)
268
        return tasks
269
270
    def get_upcoming(self):
271
        """Get upcoming tasks."""
272
        tasks = things.upcoming(**self.defaults())
273
        tasks = self.convert_new_things_lib(tasks)
274
        tasks.sort(key=lambda task: task["started"] or "", reverse=False)
275
        return tasks
276
277
    def get_waiting(self):
278
        """Get waiting tasks."""
279
        tasks = self.get_tag(self.tag_waiting)
280
        tasks.sort(key=lambda task: task["started"] or "", reverse=False)
281
        return tasks
282
283
    def get_mit(self):
284
        """Get most important tasks."""
285
        return self.get_tag(self.tag_mit)
286
287
    def get_tag(self, tag):
288
        """Get task with specific tag."""
289
        try:
290
            tasks = things.tasks(tag=tag, **self.defaults())
291
            tasks = self.convert_new_things_lib(tasks)
292
        except ValueError:
293
            tasks = []
294
        if tag in [self.tag_waiting]:
295
            tasks.sort(key=lambda task: task["started"] or "", reverse=False)
296
        return tasks
297
298
    def get_tag_today(self, tag):
299
        """Get today tasks with specific tag."""
300
        tasks = things.today(tag=tag, **self.defaults())
301
        tasks = self.convert_new_things_lib(tasks)
302
        return tasks
303
304
    def get_anytime(self):
305
        """Get anytime tasks."""
306
        query = f"""
307
                TASK.{self.IS_NOT_TRASHED} AND
308
                TASK.{self.IS_TASK} AND
309
                TASK.{self.IS_OPEN} AND
310
                TASK.{self.IS_ANYTIME} AND
311
                TASK.{self.IS_NOT_SCHEDULED} AND (
312
                    (
313
                        PROJECT.title IS NULL OR (
314
                            PROJECT.{self.IS_ANYTIME} AND
315
                            PROJECT.{self.IS_NOT_SCHEDULED} AND
316
                            PROJECT.{self.IS_NOT_TRASHED}
317
                        )
318
                    ) AND (
319
                        HEADPROJ.title IS NULL OR (
320
                            HEADPROJ.{self.IS_ANYTIME} AND
321
                            HEADPROJ.{self.IS_NOT_SCHEDULED} AND
322
                            HEADPROJ.{self.IS_NOT_TRASHED}
323
                        )
324
                    )
325
                )
326
                ORDER BY TASK.duedate DESC , TASK.todayIndex
327
                """
328
        if self.filter:
329
            # ugly hack for Kanban task view on project
330
            query = f"""
331
                TASK.{self.IS_NOT_TRASHED} AND
332
                TASK.{self.IS_TASK} AND
333
                TASK.{self.IS_OPEN} AND
334
                TASK.{self.IS_ANYTIME} AND
335
                TASK.{self.IS_NOT_SCHEDULED} AND (
336
                    (
337
                        PROJECT.title IS NULL OR (
338
                            PROJECT.{self.IS_NOT_TRASHED}
339
                        )
340
                    ) AND (
341
                        HEADPROJ.title IS NULL OR (
342
                            HEADPROJ.{self.IS_NOT_TRASHED}
343
                        )
344
                    )
345
                )
346
                ORDER BY TASK.duedate DESC , TASK.todayIndex
347
                """
348
        return self.get_rows(query)
349
350
    def get_completed(self):
351
        """Get completed tasks."""
352
        tasks = things.completed(**self.defaults())
353
        tasks = self.convert_new_things_lib(tasks)
354
        return tasks
355
356
    def get_cancelled(self):
357
        """Get cancelled tasks."""
358
        tasks = things.canceled(**self.defaults())
359
        tasks = self.convert_new_things_lib(tasks)
360
        return tasks
361
362
    def get_trashed(self):
363
        """Get trashed tasks."""
364
        query = f"""
365
                TASK.{self.IS_TRASHED} AND
366
                TASK.{self.IS_TASK}
367
                ORDER BY TASK.{self.DATE_STOP}
368
                """
369
        return self.get_rows(query)
370
371
    def get_projects(self, area=None):
372
        """Get projects."""
373
        projects = things.projects(area=area, filepath=self.database)
374
        projects = self.convert_new_things_lib(projects)
375
        for project in projects:
376
            project["size"] = things.todos(
377
                project=project["uuid"], count_only=True, filepath=self.database
378
            )
379
        return projects
380
381
    def get_areas(self):
382
        """Get areas."""
383
        areas = things.areas(filepath=self.database)
384
        areas = self.convert_new_things_lib(areas)
385
        for area in areas:
386
            area["size"] = things.todos(
387
                area=area["uuid"], count_only=True, filepath=self.database
388
            )
389
        return areas
390
391
    def get_all(self):
392
        """Get all tasks."""
393
        tasks = things.tasks(**self.defaults())
394
        tasks = self.convert_new_things_lib(tasks)
395
        return tasks
396
397
    def get_due(self):
398
        """Get due tasks."""
399
        tasks = things.deadlines(**self.defaults())
400
        tasks = self.convert_new_things_lib(tasks)
401
        tasks.sort(key=lambda task: task["deadline"] or "", reverse=False)
402
        return tasks
403
404
    def get_lint(self):
405
        """Get tasks that float around"""
406
        query = f"""
407
            TASK.{self.IS_NOT_TRASHED} AND
408
            TASK.{self.IS_OPEN} AND
409
            TASK.{self.IS_TASK} AND
410
            (TASK.{self.IS_SOMEDAY} OR TASK.{self.IS_ANYTIME}) AND
411
            TASK.project IS NULL AND
412
            TASK.area IS NULL AND
413
            TASK.actionGroup IS NULL
414
            """
415
        return self.get_rows(query)
416
417
    def get_empty_projects(self):
418
        """Get projects that are empty"""
419
        query = f"""
420
            TASK.{self.IS_NOT_TRASHED} AND
421
            TASK.{self.IS_OPEN} AND
422
            TASK.{self.IS_PROJECT} AND
423
            TASK.{self.IS_ANYTIME}
424
            GROUP BY TASK.uuid
425
            HAVING
426
                (SELECT COUNT(uuid)
427
                 FROM TMTask AS PROJECT_TASK
428
                 WHERE
429
                   PROJECT_TASK.project = TASK.uuid AND
430
                   PROJECT_TASK.{self.IS_NOT_TRASHED} AND
431
                   PROJECT_TASK.{self.IS_OPEN} AND
432
                   (PROJECT_TASK.{self.IS_ANYTIME} OR
433
                    PROJECT_TASK.{self.IS_SCHEDULED} OR
434
                      (PROJECT_TASK.{self.IS_RECURRING} AND
435
                       PROJECT_TASK.{self.RECURRING_IS_NOT_PAUSED} AND
436
                       PROJECT_TASK.{self.RECURRING_HAS_NEXT_STARTDATE}
437
                      )
438
                   )
439
                ) = 0
440
            """
441
        return self.get_rows(query)
442
443
    def get_largest_projects(self):
444
        """Get projects that are empty"""
445
        query = f"""
446
            SELECT
447
                TASK.uuid,
448
                TASK.title AS title,
449
                {self.DATE_CREATE} AS created,
450
                {self.DATE_MOD} AS modified,
451
                (SELECT COUNT(uuid)
452
                 FROM TMTask AS PROJECT_TASK
453
                 WHERE
454
                   PROJECT_TASK.project = TASK.uuid AND
455
                   PROJECT_TASK.{self.IS_NOT_TRASHED} AND
456
                   PROJECT_TASK.{self.IS_OPEN}
457
                ) AS tasks
458
            FROM
459
                {self.TABLE_TASK} AS TASK
460
            WHERE
461
               TASK.{self.IS_NOT_TRASHED} AND
462
               TASK.{self.IS_OPEN} AND
463
               TASK.{self.IS_PROJECT}
464
            GROUP BY TASK.uuid
465
            ORDER BY tasks COLLATE NOCASE DESC
466
            """
467
        return self.execute_query(query)
468
469
    def get_daystats(self):
470
        """Get a history of task activities"""
471
        query = f"""
472
                WITH RECURSIVE timeseries(x) AS (
473
                    SELECT 0
474
                    UNION ALL
475
                    SELECT x+1 FROM timeseries
476
                    LIMIT {self.stat_days}
477
                )
478
                SELECT
479
                    date(julianday("now", "-{self.stat_days} days"),
480
                         "+" || x || " days") as date,
481
                    CREATED.TasksCreated as created,
482
                    CLOSED.TasksClosed as completed,
483
                    CANCELLED.TasksCancelled as cancelled,
484
                    TRASHED.TasksTrashed as trashed
485
                FROM timeseries
486
                LEFT JOIN
487
                    (SELECT COUNT(uuid) AS TasksCreated,
488
                        date({self.DATE_CREATE},"unixepoch") AS DAY
489
                        FROM {self.TABLE_TASK} AS TASK
490
                        WHERE DAY NOT NULL
491
                          AND TASK.{self.IS_TASK}
492
                        GROUP BY DAY)
493
                    AS CREATED ON CREATED.DAY = date
494
                LEFT JOIN
495
                    (SELECT COUNT(uuid) AS TasksCancelled,
496
                        date(stopDate,"unixepoch") AS DAY
497
                        FROM {self.TABLE_TASK} AS TASK
498
                        WHERE DAY NOT NULL
499
                          AND TASK.{self.IS_CANCELLED} AND TASK.{self.IS_TASK}
500
                        GROUP BY DAY)
501
                        AS CANCELLED ON CANCELLED.DAY = date
502
                LEFT JOIN
503
                    (SELECT COUNT(uuid) AS TasksTrashed,
504
                        date({self.DATE_MOD},"unixepoch") AS DAY
505
                        FROM {self.TABLE_TASK} AS TASK
506
                        WHERE DAY NOT NULL
507
                          AND TASK.{self.IS_TRASHED} AND TASK.{self.IS_TASK}
508
                        GROUP BY DAY)
509
                        AS TRASHED ON TRASHED.DAY = date
510
                LEFT JOIN
511
                    (SELECT COUNT(uuid) AS TasksClosed,
512
                        date(stopDate,"unixepoch") AS DAY
513
                        FROM {self.TABLE_TASK} AS TASK
514
                        WHERE DAY NOT NULL
515
                          AND TASK.{self.IS_DONE} AND TASK.{self.IS_TASK}
516
                        GROUP BY DAY)
517
                        AS CLOSED ON CLOSED.DAY = date
518
                """
519
        return self.execute_query(query)
520
521
    def get_minutes_today(self):
522
        """Count the planned minutes for today."""
523
524
        tasks = things.today(**self.defaults())
525
        tasks = self.convert_new_things_lib(tasks)
526
        minutes = 0
527
        for task in tasks:
528
            for tag in task.get("tags", []):
529
                try:
530
                    minutes += int(tag)
531
                except ValueError:
532
                    pass
533
        return [{"minutes": minutes}]
534
535
    def get_seinfeld(self, tag):
536
        """Tasks logged recently with a specific tag."""
537
538
        stop_date = (datetime.today() - timedelta(days=66)).strftime("%Y-%m-%d")
539
        tasks = things.logbook(**self.defaults(), stop_date=stop_date, tag=tag)
540
        tasks = self.convert_new_things_lib(tasks)
541
        return tasks
542
543
    def get_cleanup(self):
544
        """Tasks and projects that need work."""
545
        result = []
546
        result.extend(self.get_lint())
547
        result.extend(self.get_empty_projects())
548
        result.extend(self.get_tag(self.tag_cleanup))
549
        result = [i for n, i in enumerate(result) if i not in result[n + 1 :]]
550
        return result
551
552
    def reset_config(self):
553
        """Reset the configuration."""
554
        print("Deleting: " + self.FILE_CONFIG)
555
        os.remove(self.FILE_CONFIG)
556
557
    def feedback(self):
558
        """Send feedback."""
559
560
        recipient = "[email protected]"
561
        subject = "[KanbanView] Feedback"
562
        body = f"""
563
Description:
564
Version: {__version__}
565
566
Steps that will reproduce the problem?
567
1.
568
2.
569
3.
570
571
What is the expected result?
572
573
574
What happens instead?
575
576
577
Possible workaround:
578
579
580
Any additional information:
581
========= DEBUG INFORMATION =========
582
{self.debug_text}
583
========= DEBUG INFORMATION =========
584
        """
585
        # with open("body.txt", "r") as b:
586
        #     body = b.read()
587
        # body = body.replace(" ", "%20")
588
        print(body)
589
        webbrowser.open(
590
            "mailto:?to=" + recipient + "&subject=" + subject + "&body=" + body, new=1
591
        )
592
593
    @staticmethod
594
    def get_not_implemented():
595
        """Not implemented warning."""
596
        return [{"title": "not implemented"}]
597
598
    def get_rows(self, sql):
599
        """Query Things database."""
600
601
        sql = f"""
602
            SELECT DISTINCT
603
                TASK.uuid,
604
                TASK.title,
605
                CASE
606
                    WHEN AREA.title IS NOT NULL THEN AREA.title
607
                    WHEN PROJECT.title IS NOT NULL THEN PROJECT.title
608
                    WHEN HEADING.title IS NOT NULL THEN HEADING.title
609
                END AS context,
610
                CASE
611
                    WHEN AREA.uuid IS NOT NULL THEN AREA.uuid
612
                    WHEN PROJECT.uuid IS NOT NULL THEN PROJECT.uuid
613
                END AS context_uuid,
614
                CASE
615
                    WHEN TASK.recurrenceRule IS NULL
616
                    THEN strftime('%d.%m.', TASK.dueDate,"unixepoch") ||
617
                         substr(strftime('%Y', TASK.dueDate,"unixepoch"),3, 2)
618
                ELSE NULL
619
                END AS due,
620
                date(TASK.{self.DATE_CREATE},"unixepoch") as created,
621
                date(TASK.{self.DATE_MOD},"unixepoch") as modified,
622
                strftime('%d.%m.', TASK.startDate,"unixepoch") ||
623
                  substr(strftime('%Y', TASK.startDate,"unixepoch"),3, 2)
624
                  as started,
625
                date(TASK.stopDate,"unixepoch") as stopped,
626
                (SELECT COUNT(uuid)
627
                 FROM TMTask AS PROJECT_TASK
628
                 WHERE
629
                   PROJECT_TASK.project = TASK.uuid AND
630
                   PROJECT_TASK.{self.IS_NOT_TRASHED} AND
631
                   PROJECT_TASK.{self.IS_OPEN}
632
                ) AS size,
633
                CASE
634
                    WHEN TASK.{self.IS_TASK} THEN 'task'
635
                    WHEN TASK.{self.IS_PROJECT} THEN 'project'
636
                    WHEN TASK.{self.IS_HEADING} THEN 'heading'
637
                END AS type,
638
                CASE
639
                    WHEN TASK.{self.IS_OPEN} THEN 'open'
640
                    WHEN TASK.{self.IS_CANCELLED} THEN 'cancelled'
641
                    WHEN TASK.{self.IS_DONE} THEN 'done'
642
                END AS status,
643
                TASK.notes
644
            FROM
645
                {self.TABLE_TASK} AS TASK
646
            LEFT OUTER JOIN
647
                {self.TABLE_TASK} PROJECT ON TASK.project = PROJECT.uuid
648
            LEFT OUTER JOIN
649
                {self.TABLE_AREA} AREA ON TASK.area = AREA.uuid
650
            LEFT OUTER JOIN
651
                {self.TABLE_TASK} HEADING ON TASK.actionGroup = HEADING.uuid
652
            LEFT OUTER JOIN
653
                {self.TABLE_TASK} HEADPROJ ON HEADING.project = HEADPROJ.uuid
654
            LEFT OUTER JOIN
655
                {self.TABLE_TASKTAG} TAGS ON TASK.uuid = TAGS.tasks
656
            LEFT OUTER JOIN
657
                {self.TABLE_TAG} TAG ON TAGS.tags = TAG.uuid
658
            WHERE
659
                {self.filter}
660
                {sql}
661
                """
662
663
        return self.execute_query(sql)
664
665
    def execute_query(self, sql):
666
        """Run the actual query"""
667
        if self.debug is True:
668
            print(self.database)
669
            print(sql)
670
        try:
671
            connection = sqlite3.connect(  # pylint: disable=E1101
672
                "file:" + self.database + "?mode=ro", uri=True
673
            )
674
            connection.row_factory = Things3.dict_factory
675
            cursor = connection.cursor()
676
            cursor.execute(sql)
677
            tasks = cursor.fetchall()
678
            tasks = self.anonymize_tasks(tasks)
679
            if self.debug:
680
                for task in tasks:
681
                    print(task)
682
            return tasks
683
        except sqlite3.OperationalError as error:  # pylint: disable=E1101
684
            print(f"Could not query the database at: {self.database}.")
685
            print(f"Details: {error}.")
686
            sys.exit(2)
687
688
    # pylint: disable=C0103
689
    def mode_project(self):
690
        """Hack to switch to project view"""
691
        self.mode = "project"
692
        self.IS_TASK = self.MODE_PROJECT
693
694
    # pylint: disable=C0103
695
    def mode_task(self):
696
        """Hack to switch to project view"""
697
        self.mode = "to-do"
698
        self.IS_TASK = self.MODE_TASK
699
700
    functions = {
701
        "inbox": get_inbox,
702
        "today": get_today,
703
        "next": get_anytime,
704
        "backlog": get_someday,
705
        "upcoming": get_upcoming,
706
        "waiting": get_waiting,
707
        "mit": get_mit,
708
        "completed": get_completed,
709
        "cancelled": get_cancelled,
710
        "trashed": get_trashed,
711
        "projects": get_projects,
712
        "areas": get_areas,
713
        "all": get_all,
714
        "due": get_due,
715
        "lint": get_lint,
716
        "empty": get_empty_projects,
717
        "cleanup": get_cleanup,
718
        "seinfeld": get_seinfeld,
719
        "top-proj": get_largest_projects,
720
        "stats-day": get_daystats,
721
        "stats-min-today": get_minutes_today,
722
        "reset": reset_config,
723
        "feedback": feedback,
724
    }
725