Issues (4)

things3/things3.py (1 issue)

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
import glob
13
from os import environ, path
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/JLMPQHK86H.com.culturedcode.ThingsMac"
33
        "/ThingsData-*/Things Database.thingsdatabase/main.sqlite"
34
    )
35
    TABLE_TASK = "TMTask"
36
    TABLE_AREA = "TMArea"
37
    TABLE_TAG = "TMTag"
38
    TABLE_TASKTAG = "TMTaskTag"
39
    DATE_CREATE = "creationDate"
40
    DATE_MOD = "userModificationDate"
41
    DATE_DUE = "deadline"
42
    DATE_START = "startDate"
43
    DATE_STOP = "stopDate"
44
    IS_INBOX = "start = 0"  # noqa
45
    IS_ANYTIME = "start = 1"
46
    IS_SOMEDAY = "start = 2"
47
    IS_SCHEDULED = f"{DATE_START} IS NOT NULL"
48
    IS_NOT_SCHEDULED = f"{DATE_START} IS NULL"
49
    IS_DUE = f"{DATE_DUE} IS NOT NULL"  # noqa
50
    IS_RECURRING = "rt1_recurrenceRule IS NOT NULL"
51
    IS_NOT_RECURRING = "rt1_recurrenceRule IS NULL"  # noqa
52
    IS_TASK = "type = 0"
53
    IS_PROJECT = "type = 1"
54
    IS_HEADING = "type = 2"
55
    IS_TRASHED = "trashed = 1"
56
    IS_NOT_TRASHED = "trashed = 0"
57
    IS_OPEN = "status = 0"
58
    IS_CANCELLED = "status = 2"
59
    IS_DONE = "status = 3"
60
    RECURRING_IS_NOT_PAUSED = "rt1_instanceCreationPaused = 0"
61
    RECURRING_HAS_NEXT_STARTDATE = "rt1_nextInstanceStartDate IS NOT NULL"
62
    MODE_TASK = "type = 0"
63
    MODE_PROJECT = "type = 1"
64
65
    # Variables
66
    debug = False
67
    database = next(glob.iglob(os.path.expanduser(FILE_DB)))
68
    filter = ""
69
    tag_waiting = "Waiting"
70
    tag_mit = "MIT"
71
    tag_cleanup = "Cleanup"
72
    tag_seinfeld = "Seinfeld"
73
    tag_a = "A"
74
    tag_b = "B"
75
    tag_c = "C"
76
    tag_d = "D"
77
    stat_days = 365
78
    anonymize = False
79
    config = configparser.ConfigParser()
80
    config.read(FILE_CONFIG, encoding="utf-8")
81
    mode = "to-do"
82
    filter_project = None
83
    filter_area = None
84
    debug_text = ""
85
86
    # pylint: disable=R0913,C0330
87
    def __init__(
88
        self,
89
        database=None,
90
        tag_waiting=None,
91
        tag_mit=None,
92
        tag_cleanup=None,
93
        tag_seinfeld=None,
94
        tag_a=None,
95
        tag_b=None,
96
        tag_c=None,
97
        tag_d=None,
98
        stat_days=None,
99
        anonymize=None,
100
        debug_text="",
101
    ):
102
        self.debug_text = debug_text
103
104
        cfg = self.get_from_config(tag_waiting, "TAG_WAITING")
105
        self.tag_waiting = cfg if cfg else self.tag_waiting
106
        self.set_config("TAG_WAITING", self.tag_waiting)
107
108
        cfg = self.get_from_config(anonymize, "ANONYMIZE")
109
        self.anonymize = (cfg == "True") if (cfg == "True") else self.anonymize
110
        self.set_config("ANONYMIZE", self.anonymize)
111
112
        cfg = self.get_from_config(tag_mit, "TAG_MIT")
113
        self.tag_mit = cfg if cfg else self.tag_mit
114
        self.set_config("TAG_MIT", self.tag_mit)
115
116
        cfg = self.get_from_config(tag_cleanup, "TAG_CLEANUP")
117
        self.tag_cleanup = cfg if cfg else self.tag_cleanup
118
        self.set_config("TAG_CLEANUP", self.tag_cleanup)
119
120
        cfg = self.get_from_config(tag_seinfeld, "TAG_SEINFELD")
121
        self.tag_seinfeld = cfg if cfg else self.tag_seinfeld
122
        self.set_config("TAG_SEINFELD", self.tag_seinfeld)
123
124
        cfg = self.get_from_config(tag_a, "TAG_A")
125
        self.tag_a = cfg if cfg else self.tag_a
126
        self.set_config("TAG_A", self.tag_a)
127
128
        cfg = self.get_from_config(tag_b, "TAG_B")
129
        self.tag_b = cfg if cfg else self.tag_b
130
        self.set_config("TAG_B", self.tag_b)
131
132
        cfg = self.get_from_config(tag_c, "TAG_C")
133
        self.tag_c = cfg if cfg else self.tag_c
134
        self.set_config("TAG_C", self.tag_c)
135
136
        cfg = self.get_from_config(tag_d, "TAG_D")
137
        self.tag_d = cfg if cfg else self.tag_d
138
        self.set_config("TAG_D", self.tag_d)
139
140
        cfg = self.get_from_config(stat_days, "STAT_DAYS")
141
        self.stat_days = cfg if cfg else self.stat_days
142
        self.set_config("STAT_DAYS", self.stat_days)
143
144
        cfg = self.get_from_config(database, "THINGSDB")
145
        self.database = cfg if cfg else self.database
146
        # Automated migration to new database location in Things 3.12.6/3.13.1
147
        # --------------------------------
148
        try:
149
            with open(self.database, encoding="utf-8") as f_d:
150
                if "Your database file has been moved there" in f_d.readline():
151
                    self.database = f"/Users/{self.user}/{self.FILE_DB}"
0 ignored issues
show
The Instance of Things3 does not seem to have a member named user.

This check looks for calls to members that are non-existent. These calls will fail.

The member could have been renamed or removed.

Loading history...
152
        except (UnicodeDecodeError, FileNotFoundError, PermissionError):
153
            pass  # binary file (old database) or doesn't exist
154
        # --------------------------------
155
        self.set_config("THINGSDB", self.database)
156
157
    def set_config(self, key, value, domain="DATABASE"):
158
        """Write variable to config."""
159
        if domain not in self.config:
160
            self.config.add_section(domain)
161
        if value is not None and key is not None:
162
            self.config.set(domain, str(key), str(value))
163
            with open(self.FILE_CONFIG, "w+", encoding="utf-8") as configfile:
164
                self.config.write(configfile)
165
166
    def get_config(self, key, domain="DATABASE"):
167
        """Get variable from config."""
168
        result = None
169
        if domain in self.config and key in self.config[domain]:
170
            result = path.expanduser(self.config[domain][key])
171
        return result
172
173
    def get_from_config(self, variable, key, domain="DATABASE"):
174
        """Set variable. Priority: input, environment, config"""
175
        result = None
176
        if variable is not None:
177
            result = variable
178
        elif environ.get(key):
179
            result = environ.get(key)
180
        elif domain in self.config and key in self.config[domain]:
181
            result = path.expanduser(self.config[domain][key])
182
        return result
183
184
    @staticmethod
185
    def anonymize_string(string):
186
        """Scramble text."""
187
        if string is None:
188
            return None
189
        string = list(string)
190
        shuffle(string)
191
        string = "".join(string)
192
        return string
193
194
    @staticmethod
195
    def dict_factory(cursor, row):
196
        """Convert SQL result into a dictionary"""
197
        dictionary = {}
198
        for idx, col in enumerate(cursor.description):
199
            dictionary[col[0]] = row[idx]
200
        return dictionary
201
202
    def anonymize_tasks(self, tasks):
203
        """Scramble output for screenshots."""
204
        if self.anonymize:
205
            for task in tasks:
206
                task["title"] = self.anonymize_string(task["title"])
207
                task["context"] = (
208
                    self.anonymize_string(task["context"]) if "context" in task else ""
209
                )
210
        return tasks
211
212
    def defaults(self):
213
        """Some default options for the new API."""
214
        return dict(
215
            type=self.mode,
216
            project=self.filter_project,
217
            area=self.filter_area,
218
            filepath=self.database,
219
        )
220
221
    def convert_new_things_lib(self, tasks):
222
        """Convert tasks from new library to old expectations."""
223
        for task in tasks:
224
            task["context"] = (
225
                task.get("project_title")
226
                or task.get("area_title")
227
                or task.get("heading_title")
228
            )
229
            task["context_uuid"] = (
230
                task.get("project") or task.get("area") or task.get("heading")
231
            )
232
            task["due"] = task.get("deadline")
233
            task["started"] = task.get("start_date")
234
            task["size"] = things.projects(
235
                task["uuid"], count_only=True, filepath=self.database
236
            )
237
        tasks.sort(key=lambda task: task["title"] or "", reverse=False)
238
        tasks = self.anonymize_tasks(tasks)
239
        return tasks
240
241
    def get_inbox(self):
242
        """Get tasks from inbox."""
243
        tasks = things.inbox(**self.defaults())
244
        tasks = self.convert_new_things_lib(tasks)
245
        return tasks
246
247
    def get_today(self):
248
        """Get tasks from today."""
249
        tasks = things.today(**self.defaults())
250
        tasks = self.convert_new_things_lib(tasks)
251
        tasks.sort(key=lambda task: task.get("started", ""), reverse=True)
252
        tasks.sort(key=lambda task: task.get("todayIndex", ""), reverse=False)
253
        return tasks
254
255
    def get_task(self, area=None, project=None):
256
        """Get tasks."""
257
        tasks = things.tasks(area=area, project=project, filepath=self.database)
258
        tasks = self.convert_new_things_lib(tasks)
259
        return tasks
260
261
    def get_someday(self):
262
        """Get someday tasks."""
263
        tasks = things.someday(**self.defaults())
264
        tasks = self.convert_new_things_lib(tasks)
265
        tasks.sort(key=lambda task: task["deadline"] or "", reverse=True)
266
        return tasks
267
268
    def get_upcoming(self):
269
        """Get upcoming tasks."""
270
        tasks = things.upcoming(**self.defaults())
271
        tasks = self.convert_new_things_lib(tasks)
272
        tasks.sort(key=lambda task: task["started"] or "", reverse=False)
273
        return tasks
274
275
    def get_waiting(self):
276
        """Get waiting tasks."""
277
        tasks = self.get_tag(self.tag_waiting)
278
        tasks.sort(key=lambda task: task["started"] or "", reverse=False)
279
        return tasks
280
281
    def get_mit(self):
282
        """Get most important tasks."""
283
        return self.get_tag(self.tag_mit)
284
285
    def get_tag(self, tag):
286
        """Get task with specific tag."""
287
        try:
288
            tasks = things.tasks(tag=tag, **self.defaults())
289
            tasks = self.convert_new_things_lib(tasks)
290
        except ValueError:
291
            tasks = []
292
        if tag in [self.tag_waiting]:
293
            tasks.sort(key=lambda task: task["started"] or "", reverse=False)
294
        return tasks
295
296
    def get_tag_today(self, tag):
297
        """Get today tasks with specific tag."""
298
        tasks = things.today(tag=tag, **self.defaults())
299
        tasks = self.convert_new_things_lib(tasks)
300
        return tasks
301
302
    def get_anytime(self):
303
        """Get anytime tasks."""
304
        query = f"""
305
                TASK.{self.IS_NOT_TRASHED} AND
306
                TASK.{self.IS_TASK} AND
307
                TASK.{self.IS_OPEN} AND
308
                TASK.{self.IS_ANYTIME} AND
309
                TASK.{self.IS_NOT_SCHEDULED} AND (
310
                    (
311
                        PROJECT.title IS NULL OR (
312
                            PROJECT.{self.IS_ANYTIME} AND
313
                            PROJECT.{self.IS_NOT_SCHEDULED} AND
314
                            PROJECT.{self.IS_NOT_TRASHED}
315
                        )
316
                    ) AND (
317
                        HEADPROJ.title IS NULL OR (
318
                            HEADPROJ.{self.IS_ANYTIME} AND
319
                            HEADPROJ.{self.IS_NOT_SCHEDULED} AND
320
                            HEADPROJ.{self.IS_NOT_TRASHED}
321
                        )
322
                    )
323
                )
324
                ORDER BY TASK.deadline DESC , TASK.todayIndex
325
                """
326
        if self.filter:
327
            # ugly hack for Kanban task view on project
328
            query = f"""
329
                TASK.{self.IS_NOT_TRASHED} AND
330
                TASK.{self.IS_TASK} AND
331
                TASK.{self.IS_OPEN} AND
332
                TASK.{self.IS_ANYTIME} AND
333
                TASK.{self.IS_NOT_SCHEDULED} AND (
334
                    (
335
                        PROJECT.title IS NULL OR (
336
                            PROJECT.{self.IS_NOT_TRASHED}
337
                        )
338
                    ) AND (
339
                        HEADPROJ.title IS NULL OR (
340
                            HEADPROJ.{self.IS_NOT_TRASHED}
341
                        )
342
                    )
343
                )
344
                ORDER BY TASK.deadline DESC , TASK.todayIndex
345
                """
346
        return self.get_rows(query)
347
348
    def get_completed(self):
349
        """Get completed tasks."""
350
        tasks = things.completed(**self.defaults())
351
        tasks = self.convert_new_things_lib(tasks)
352
        return tasks
353
354
    def get_cancelled(self):
355
        """Get cancelled tasks."""
356
        tasks = things.canceled(**self.defaults())
357
        tasks = self.convert_new_things_lib(tasks)
358
        return tasks
359
360
    def get_trashed(self):
361
        """Get trashed tasks."""
362
        query = f"""
363
                TASK.{self.IS_TRASHED} AND
364
                TASK.{self.IS_TASK}
365
                ORDER BY TASK.{self.DATE_STOP}
366
                """
367
        return self.get_rows(query)
368
369
    def get_projects(self, area=None):
370
        """Get projects."""
371
        projects = things.projects(area=area, filepath=self.database)
372
        projects = self.convert_new_things_lib(projects)
373
        for project in projects:
374
            project["size"] = things.todos(
375
                project=project["uuid"], count_only=True, filepath=self.database
376
            )
377
        return projects
378
379
    def get_areas(self):
380
        """Get areas."""
381
        areas = things.areas(filepath=self.database)
382
        areas = self.convert_new_things_lib(areas)
383
        for area in areas:
384
            area["size"] = things.todos(
385
                area=area["uuid"], count_only=True, filepath=self.database
386
            )
387
        return areas
388
389
    def get_all(self):
390
        """Get all tasks."""
391
        tasks = things.tasks(**self.defaults())
392
        tasks = self.convert_new_things_lib(tasks)
393
        return tasks
394
395
    def get_due(self):
396
        """Get due tasks."""
397
        tasks = things.deadlines(**self.defaults())
398
        tasks = self.convert_new_things_lib(tasks)
399
        tasks.sort(key=lambda task: task["deadline"] or "", reverse=False)
400
        return tasks
401
402
    def get_lint(self):
403
        """Get tasks that float around"""
404
        query = f"""
405
            TASK.{self.IS_NOT_TRASHED} AND
406
            TASK.{self.IS_OPEN} AND
407
            TASK.{self.IS_TASK} AND
408
            (TASK.{self.IS_SOMEDAY} OR TASK.{self.IS_ANYTIME}) AND
409
            TASK.project IS NULL AND
410
            TASK.area IS NULL AND
411
            TASK.heading IS NULL
412
            """
413
        return self.get_rows(query)
414
415
    def get_empty_projects(self):
416
        """Get projects that are empty"""
417
        query = f"""
418
            TASK.{self.IS_NOT_TRASHED} AND
419
            TASK.{self.IS_OPEN} AND
420
            TASK.{self.IS_PROJECT} AND
421
            TASK.{self.IS_ANYTIME}
422
            GROUP BY TASK.uuid
423
            HAVING
424
                (SELECT COUNT(uuid)
425
                 FROM TMTask AS PROJECT_TASK
426
                 WHERE
427
                   PROJECT_TASK.project = TASK.uuid AND
428
                   PROJECT_TASK.{self.IS_NOT_TRASHED} AND
429
                   PROJECT_TASK.{self.IS_OPEN} AND
430
                   (PROJECT_TASK.{self.IS_ANYTIME} OR
431
                    PROJECT_TASK.{self.IS_SCHEDULED} OR
432
                      (PROJECT_TASK.{self.IS_RECURRING} AND
433
                       PROJECT_TASK.{self.RECURRING_IS_NOT_PAUSED} AND
434
                       PROJECT_TASK.{self.RECURRING_HAS_NEXT_STARTDATE}
435
                      )
436
                   )
437
                ) = 0
438
            """
439
        return self.get_rows(query)
440
441
    def get_largest_projects(self):
442
        """Get projects that are empty"""
443
        query = f"""
444
            SELECT
445
                TASK.uuid,
446
                TASK.title AS title,
447
                {self.DATE_CREATE} AS created,
448
                {self.DATE_MOD} AS modified,
449
                (SELECT COUNT(uuid)
450
                 FROM TMTask AS PROJECT_TASK
451
                 WHERE
452
                   PROJECT_TASK.project = TASK.uuid AND
453
                   PROJECT_TASK.{self.IS_NOT_TRASHED} AND
454
                   PROJECT_TASK.{self.IS_OPEN}
455
                ) AS tasks
456
            FROM
457
                {self.TABLE_TASK} AS TASK
458
            WHERE
459
               TASK.{self.IS_NOT_TRASHED} AND
460
               TASK.{self.IS_OPEN} AND
461
               TASK.{self.IS_PROJECT}
462
            GROUP BY TASK.uuid
463
            ORDER BY tasks COLLATE NOCASE DESC
464
            """
465
        return self.execute_query(query)
466
467
    def get_daystats(self):
468
        """Get a history of task activities"""
469
        query = f"""
470
                WITH RECURSIVE timeseries(x) AS (
471
                    SELECT 0
472
                    UNION ALL
473
                    SELECT x+1 FROM timeseries
474
                    LIMIT {self.stat_days}
475
                )
476
                SELECT
477
                    date(julianday("now", "-{self.stat_days} days"),
478
                         "+" || x || " days") as date,
479
                    CREATED.TasksCreated as created,
480
                    CLOSED.TasksClosed as completed,
481
                    CANCELLED.TasksCancelled as cancelled,
482
                    TRASHED.TasksTrashed as trashed
483
                FROM timeseries
484
                LEFT JOIN
485
                    (SELECT COUNT(uuid) AS TasksCreated,
486
                        date({self.DATE_CREATE},"unixepoch") AS DAY
487
                        FROM {self.TABLE_TASK} AS TASK
488
                        WHERE DAY NOT NULL
489
                          AND TASK.{self.IS_TASK}
490
                        GROUP BY DAY)
491
                    AS CREATED ON CREATED.DAY = date
492
                LEFT JOIN
493
                    (SELECT COUNT(uuid) AS TasksCancelled,
494
                        date(stopDate,"unixepoch") AS DAY
495
                        FROM {self.TABLE_TASK} AS TASK
496
                        WHERE DAY NOT NULL
497
                          AND TASK.{self.IS_CANCELLED} AND TASK.{self.IS_TASK}
498
                        GROUP BY DAY)
499
                        AS CANCELLED ON CANCELLED.DAY = date
500
                LEFT JOIN
501
                    (SELECT COUNT(uuid) AS TasksTrashed,
502
                        date({self.DATE_MOD},"unixepoch") AS DAY
503
                        FROM {self.TABLE_TASK} AS TASK
504
                        WHERE DAY NOT NULL
505
                          AND TASK.{self.IS_TRASHED} AND TASK.{self.IS_TASK}
506
                        GROUP BY DAY)
507
                        AS TRASHED ON TRASHED.DAY = date
508
                LEFT JOIN
509
                    (SELECT COUNT(uuid) AS TasksClosed,
510
                        date(stopDate,"unixepoch") AS DAY
511
                        FROM {self.TABLE_TASK} AS TASK
512
                        WHERE DAY NOT NULL
513
                          AND TASK.{self.IS_DONE} AND TASK.{self.IS_TASK}
514
                        GROUP BY DAY)
515
                        AS CLOSED ON CLOSED.DAY = date
516
                """
517
        return self.execute_query(query)
518
519
    def get_minutes_today(self):
520
        """Count the planned minutes for today."""
521
522
        tasks = things.today(**self.defaults())
523
        tasks = self.convert_new_things_lib(tasks)
524
        minutes = 0
525
        for task in tasks:
526
            for tag in task.get("tags", []):
527
                try:
528
                    minutes += int(tag)
529
                except ValueError:
530
                    pass
531
        return [{"minutes": minutes}]
532
533
    def get_seinfeld(self, tag):
534
        """Tasks logged recently with a specific tag."""
535
536
        stop_date = (datetime.today() - timedelta(days=66)).strftime("%Y-%m-%d")
537
        tasks = things.logbook(**self.defaults(), stop_date=stop_date, tag=tag)
538
        tasks = self.convert_new_things_lib(tasks)
539
        return tasks
540
541
    def get_cleanup(self):
542
        """Tasks and projects that need work."""
543
        result = []
544
        result.extend(self.get_lint())
545
        result.extend(self.get_empty_projects())
546
        result.extend(self.get_tag(self.tag_cleanup))
547
        result = [i for n, i in enumerate(result) if i not in result[n + 1 :]]
548
        return result
549
550
    def reset_config(self):
551
        """Reset the configuration."""
552
        print("Deleting: " + self.FILE_CONFIG)
553
        os.remove(self.FILE_CONFIG)
554
555
    def feedback(self):
556
        """Send feedback."""
557
558
        recipient = "[email protected]"
559
        subject = "[KanbanView] Feedback"
560
        body = f"""
561
Description:
562
Version: {__version__}
563
564
Steps that will reproduce the problem?
565
1.
566
2.
567
3.
568
569
What is the expected result?
570
571
572
What happens instead?
573
574
575
Possible workaround:
576
577
578
Any additional information:
579
========= DEBUG INFORMATION =========
580
{self.debug_text}
581
========= DEBUG INFORMATION =========
582
        """
583
        # with open("body.txt", "r") as b:
584
        #     body = b.read()
585
        # body = body.replace(" ", "%20")
586
        print(body)
587
        webbrowser.open(
588
            "mailto:?to=" + recipient + "&subject=" + subject + "&body=" + body, new=1
589
        )
590
591
    @staticmethod
592
    def get_not_implemented():
593
        """Not implemented warning."""
594
        return [{"title": "not implemented"}]
595
596
    def get_rows(self, sql):
597
        """Query Things database."""
598
599
        sql = f"""
600
            SELECT DISTINCT
601
                TASK.uuid,
602
                TASK.title,
603
                CASE
604
                    WHEN AREA.title IS NOT NULL THEN AREA.title
605
                    WHEN PROJECT.title IS NOT NULL THEN PROJECT.title
606
                    WHEN HEADING.title IS NOT NULL THEN HEADING.title
607
                END AS context,
608
                CASE
609
                    WHEN AREA.uuid IS NOT NULL THEN AREA.uuid
610
                    WHEN PROJECT.uuid IS NOT NULL THEN PROJECT.uuid
611
                END AS context_uuid,
612
                CASE
613
                    WHEN TASK.rt1_recurrenceRule IS NULL
614
                    THEN strftime('%d.%m.', TASK.deadline,"unixepoch") ||
615
                         substr(strftime('%Y', TASK.deadline,"unixepoch"),3, 2)
616
                ELSE NULL
617
                END AS due,
618
                date(TASK.{self.DATE_CREATE},"unixepoch") as created,
619
                date(TASK.{self.DATE_MOD},"unixepoch") as modified,
620
                strftime('%d.%m.', TASK.startDate,"unixepoch") ||
621
                  substr(strftime('%Y', TASK.startDate,"unixepoch"),3, 2)
622
                  as started,
623
                date(TASK.stopDate,"unixepoch") as stopped,
624
                (SELECT COUNT(uuid)
625
                 FROM TMTask AS PROJECT_TASK
626
                 WHERE
627
                   PROJECT_TASK.project = TASK.uuid AND
628
                   PROJECT_TASK.{self.IS_NOT_TRASHED} AND
629
                   PROJECT_TASK.{self.IS_OPEN}
630
                ) AS size,
631
                CASE
632
                    WHEN TASK.{self.IS_TASK} THEN 'task'
633
                    WHEN TASK.{self.IS_PROJECT} THEN 'project'
634
                    WHEN TASK.{self.IS_HEADING} THEN 'heading'
635
                END AS type,
636
                CASE
637
                    WHEN TASK.{self.IS_OPEN} THEN 'open'
638
                    WHEN TASK.{self.IS_CANCELLED} THEN 'cancelled'
639
                    WHEN TASK.{self.IS_DONE} THEN 'done'
640
                END AS status,
641
                TASK.notes
642
            FROM
643
                {self.TABLE_TASK} AS TASK
644
            LEFT OUTER JOIN
645
                {self.TABLE_TASK} PROJECT ON TASK.project = PROJECT.uuid
646
            LEFT OUTER JOIN
647
                {self.TABLE_AREA} AREA ON TASK.area = AREA.uuid
648
            LEFT OUTER JOIN
649
                {self.TABLE_TASK} HEADING ON TASK.heading = HEADING.uuid
650
            LEFT OUTER JOIN
651
                {self.TABLE_TASK} HEADPROJ ON HEADING.project = HEADPROJ.uuid
652
            LEFT OUTER JOIN
653
                {self.TABLE_TASKTAG} TAGS ON TASK.uuid = TAGS.tasks
654
            LEFT OUTER JOIN
655
                {self.TABLE_TAG} TAG ON TAGS.tags = TAG.uuid
656
            WHERE
657
                {self.filter}
658
                {sql}
659
                """
660
661
        return self.execute_query(sql)
662
663
    def execute_query(self, sql):
664
        """Run the actual query"""
665
        if self.debug is True:
666
            print(self.database)
667
            print(sql)
668
        try:
669
            connection = sqlite3.connect(  # pylint: disable=E1101
670
                "file:" + self.database + "?mode=ro", uri=True
671
            )
672
            connection.row_factory = Things3.dict_factory
673
            cursor = connection.cursor()
674
            cursor.execute(sql)
675
            tasks = cursor.fetchall()
676
            tasks = self.anonymize_tasks(tasks)
677
            if self.debug:
678
                for task in tasks:
679
                    print(task)
680
            return tasks
681
        except sqlite3.OperationalError as error:  # pylint: disable=E1101
682
            print(f"Could not query the database at: {self.database}.")
683
            print(f"Details: {error}.")
684
            sys.exit(2)
685
686
    # pylint: disable=C0103
687
    def mode_project(self):
688
        """Hack to switch to project view"""
689
        self.mode = "project"
690
        self.IS_TASK = self.MODE_PROJECT
691
692
    # pylint: disable=C0103
693
    def mode_task(self):
694
        """Hack to switch to project view"""
695
        self.mode = "to-do"
696
        self.IS_TASK = self.MODE_TASK
697
698
    functions = {
699
        "inbox": get_inbox,
700
        "today": get_today,
701
        "next": get_anytime,
702
        "backlog": get_someday,
703
        "upcoming": get_upcoming,
704
        "waiting": get_waiting,
705
        "mit": get_mit,
706
        "completed": get_completed,
707
        "cancelled": get_cancelled,
708
        "trashed": get_trashed,
709
        "projects": get_projects,
710
        "areas": get_areas,
711
        "all": get_all,
712
        "due": get_due,
713
        "lint": get_lint,
714
        "empty": get_empty_projects,
715
        "cleanup": get_cleanup,
716
        "seinfeld": get_seinfeld,
717
        "top-proj": get_largest_projects,
718
        "stats-day": get_daystats,
719
        "stats-min-today": get_minutes_today,
720
        "reset": reset_config,
721
        "feedback": feedback,
722
    }
723