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, |
|
|
|
|
91
|
|
|
database=None, |
|
|
|
|
92
|
|
|
tag_waiting=None, |
|
|
|
|
93
|
|
|
tag_mit=None, |
|
|
|
|
94
|
|
|
tag_cleanup=None, |
|
|
|
|
95
|
|
|
tag_seinfeld=None, |
|
|
|
|
96
|
|
|
tag_a=None, |
|
|
|
|
97
|
|
|
tag_b=None, |
|
|
|
|
98
|
|
|
tag_c=None, |
|
|
|
|
99
|
|
|
tag_d=None, |
|
|
|
|
100
|
|
|
stat_days=None, |
|
|
|
|
101
|
|
|
anonymize=None, |
|
|
|
|
102
|
|
|
debug_text="", |
|
|
|
|
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
|
|
|
|