things3_to_kanban.get_rows() - Code Metrics - Inspection of "refactoring to enhance code qulity" - AlexanderWillner/KanbanView - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( f1da93...b76d54 )

by Alexander

created 2020-04-02 10:25 UTC

things3_to_kanban.get_rows() A

↳ Parent: things3_to_kanban

Complexity

Conditions

Size

Total Lines	34
Code Lines	5

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
eloc	5
dl	0
loc	34
rs	10
c	0
b	0
f	0
cc	1
nop	1

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

"""KanbanView for Things 3."""

from __future__ import print_function

__author__ = "Luc Beaulieu and Alexander Willner"
__copyright__ = "Copyright 2018 Luc Beaulieu / 2020 Alexander Willner"
__credits__ = ["Luc Beaulieu", "Alexander Willner"]
__license__ = "unknown"
__version__ = "1.1.0"
__maintainer__ = "Alexander Willner"
__email__ = "[email protected]"
__status__ = "Development"

import sqlite3
import webbrowser
import codecs
from os.path import expanduser, dirname, realpath
from os import environ
from random import shuffle

# Basic config
FILE_SQLITE = '~/Library/Containers/'\
              'com.culturedcode.ThingsMac.beta/Data/Library/'\
              'Application Support/Cultured Code/Things/Things.sqlite3'\
    if not environ.get('THINGSDB') else environ.get('THINGSDB')
ANONYMIZE = bool(environ.get('ANONYMIZE'))
TAG_WAITING = "Waiting" if not environ.get('TAG_WAITING') \
    else environ.get('TAG_WAITING')
TAG_MIT = "MIT" if not environ.get('TAG_MIT') \
    else environ.get('TAG_MIT')

# Basic variables
FILE_SQLITE = expanduser(FILE_SQLITE)
FILE_HTML = dirname(realpath(__file__)) + '/kanban.html'

CURSOR = sqlite3.connect(FILE_SQLITE).cursor()

# Database layout info
TASKTABLE = "TMTask"
AREATABLE = "TMArea"
TAGTABLE = "TMTag"
TASKTAGTABLE = "TMTaskTag"
ISNOTTRASHED = "TASK.trashed = 0"
ISTRASHED = "TASK.trashed = 1"
ISOPEN = "TASK.status = 0"
ISNOTSTARTED = "TASK.start = 0"
ISCANCELLED = "TASK.status = 2"
ISCOMPLETED = "TASK.status = 3"
ISSTARTED = "TASK.start = 1"
ISPOSTPONED = "TASK.start = 2"
ISTASK = "TASK.type = 0"
ISPROJECT = "TASK.type = 1"
ISHEADING = "TASK.type = 2"
ISOPENTASK = ISTASK + " AND " + ISNOTTRASHED + " AND " + ISOPEN

# Queries
LIST_SOMEDAY = ISOPENTASK + " AND " + ISPOSTPONED + \
    " AND TASK.startdate IS NULL AND TASK.recurrenceRule IS NULL" + \
    " ORDER BY TASK.duedate DESC, TASK.creationdate DESC"
LIST_INBOX = ISOPENTASK + " AND " + ISNOTSTARTED + \
    " ORDER BY TASK.duedate DESC , TASK.todayIndex"
LIST_ANYTIME = ISOPENTASK + " AND " + ISSTARTED + \
    " AND TASK.startdate is NULL" + \
    " AND (TASK.area NOT NULL OR TASK.project in (SELECT uuid FROM " + \
    TASKTABLE + \
    " WHERE uuid=TASK.project AND start=1" + \
    " AND trashed=0))" + \
    " ORDER BY TASK.duedate DESC , TASK.todayIndex"
LIST_TODAY = ISOPENTASK + " AND " + ISSTARTED + \
    " AND TASK.startdate is NOT NULL" + \
    " ORDER BY TASK.duedate DESC , TASK.todayIndex"
LIST_UPCOMING = ISOPENTASK + " AND " + ISPOSTPONED + \
    " AND (TASK.startDate NOT NULL OR TASK.recurrenceRule NOT NULL)" + \
    " ORDER BY TASK.startdate, TASK.todayIndex"
LIST_WAITING = ISOPENTASK + \
    " AND TAGS.tags=(SELECT uuid FROM " + TAGTABLE + \
    " WHERE title='" + TAG_WAITING + "')" + \
    " ORDER BY TASK.duedate DESC , TASK.todayIndex"
LIST_MIT = ISOPENTASK + " AND " + ISSTARTED + \
    " AND TASK.startdate is NOT NULL" + \
    " AND TAGS.tags=(SELECT uuid FROM " + TAGTABLE + \
    " WHERE title='" + TAG_MIT + "')" + \
    " ORDER BY TASK.duedate DESC , TASK.todayIndex"


def anonymize(word):
    """Scramble output for screenshots."""

    if ANONYMIZE is True:
        word = list(word)
        shuffle(word)
        word = ''.join(word)
    return word


def get_rows(sql):
    """Query Things database."""

    sql = """
        SELECT DISTINCT
            TASK.uuid,
            TASK.title,
            CASE
                WHEN AREA.title IS NOT NULL THEN AREA.title
                WHEN PROJECT.title IS NOT NULL THEN PROJECT.title
                WHEN HEADING.title IS NOT NULL THEN HEADING.title
            END,
            CASE
                WHEN AREA.uuid IS NOT NULL THEN AREA.uuid
                WHEN PROJECT.uuid IS NOT NULL THEN PROJECT.uuid
            END,
            CASE
                WHEN TASK.recurrenceRule IS NULL
                THEN date(TASK.dueDate,"unixepoch")
            ELSE NULL
            END
        FROM
            TMTask AS TASK
        LEFT JOIN
            TMTaskTag TAGS ON TAGS.tasks = TASK.uuid
        LEFT OUTER JOIN
            TMTask PROJECT ON TASK.project = PROJECT.uuid
        LEFT OUTER JOIN
            TMArea AREA ON TASK.area = AREA.uuid
        LEFT OUTER JOIN
            TMTask HEADING ON TASK.actionGroup = HEADING.uuid
        WHERE """ + sql
    CURSOR.execute(sql)
    return CURSOR.fetchall()


def write_html_column(uid, file, header, sql):
    """Create a column in the output."""

    rows = get_rows(sql)

    file.write('<div id="left' + str(uid) + '"><div class="inner"><h2>' +
               header + ' <span class="size">' +
               str(len(rows)) + '</span></h2>')

    for row in rows:
        task_uuid = str(row[0]) if row[0] is not None else ''
        task_title = anonymize(str(row[1])) if row[1] is not None else ''
        context_title = anonymize(str(row[2])) if row[2] is not None else ''
        context_uuid = str(row[3]) if row[3] is not None else ''
        deadline = str(row[4]) if row[4] is not None else ''

        task_link = '<a href="things:///show?id=' + task_uuid + '">' + \
            task_title + '</a>' if task_uuid != '' else task_title
        context_link = '<a href="things:///show?id=' + context_uuid + '">' + \
            context_title + '</a>' if context_uuid != '' else context_title
        css_class = 'hasProject' if context_title != '' else 'hasNoProject'
        css_class = 'hasDeadline' if deadline != '' else css_class

        file.write('<div id="box">' + task_link +
                   '<div class="deadline">' + deadline + '</div>' +
                   '<div class="area ' + css_class + '">' + context_link +
                   '</div>' +
                   '</div>')
    file.write("</div></div>")


def write_html_header(file):
    """Write HTML header."""

    message = """<head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
        <link rel="stylesheet" href="../resources/style.css">
        </head>

        <body>
        <img id="logo" src="../resources/logo.png" alt="logo" />
        """
    file.write(message)


def write_html_footer(file):
    """Write HTML footer."""

    message = """
        <div id="foot"><br />
        Copyright &copy;2018 Luc Beaulieu / 2020 Alexander Willner
        </div></body></html>"""
    file.write(message)


def write_html_columns(file):
    """Write HTML columns."""

    write_html_column(1, file, "Backlog", LIST_SOMEDAY)
    write_html_column(2, file, "Upcoming", LIST_UPCOMING)
    write_html_column(3, file, "Waiting", LIST_WAITING)
    write_html_column(4, file, "Inbox", LIST_INBOX)
    write_html_column(5, file, "MIT", LIST_MIT)
    write_html_column(6, file, "Today", LIST_TODAY)
    write_html_column(7, file, "Next", LIST_ANYTIME)


def main():
    """Convert Things 3 database to Kanban HTML view."""

    file = codecs.open(FILE_HTML, 'w', 'utf-8')

    write_html_header(file)

    write_html_columns(file)

    write_html_footer(file)

    file.close()
    CURSOR.close()

    webbrowser.open_new_tab('file://' + FILE_HTML)


if __name__ == "__main__":
    main()


1			#!/usr/bin/env python3
2			# -- coding: utf-8 --
3
4			"""KanbanView for Things 3."""
5
6			from __future__ import print_function
7
8			__author__ = "Luc Beaulieu and Alexander Willner"
9			__copyright__ = "Copyright 2018 Luc Beaulieu / 2020 Alexander Willner"
10			__credits__ = ["Luc Beaulieu", "Alexander Willner"]
11			__license__ = "unknown"
12			__version__ = "1.1.0"
13			__maintainer__ = "Alexander Willner"
14			__email__ = "[email protected]"
15			__status__ = "Development"
16
17			import sqlite3
18			import webbrowser
19			import codecs
20			from os.path import expanduser, dirname, realpath
21			from os import environ
22			from random import shuffle
23
24			# Basic config
25			FILE_SQLITE = '~/Library/Containers/'\
26			'com.culturedcode.ThingsMac.beta/Data/Library/'\
27			'Application Support/Cultured Code/Things/Things.sqlite3'\
28			if not environ.get('THINGSDB') else environ.get('THINGSDB')
29			ANONYMIZE = bool(environ.get('ANONYMIZE'))
30			TAG_WAITING = "Waiting" if not environ.get('TAG_WAITING') \
31			else environ.get('TAG_WAITING')
32			TAG_MIT = "MIT" if not environ.get('TAG_MIT') \
33			else environ.get('TAG_MIT')
34
35			# Basic variables
36			FILE_SQLITE = expanduser(FILE_SQLITE)
37			FILE_HTML = dirname(realpath(__file__)) + '/kanban.html'
38
39			CURSOR = sqlite3.connect(FILE_SQLITE).cursor()
40
41			# Database layout info
42			TASKTABLE = "TMTask"
43			AREATABLE = "TMArea"
44			TAGTABLE = "TMTag"
45			TASKTAGTABLE = "TMTaskTag"
46			ISNOTTRASHED = "TASK.trashed = 0"
47			ISTRASHED = "TASK.trashed = 1"
48			ISOPEN = "TASK.status = 0"
49			ISNOTSTARTED = "TASK.start = 0"
50			ISCANCELLED = "TASK.status = 2"
51			ISCOMPLETED = "TASK.status = 3"
52			ISSTARTED = "TASK.start = 1"
53			ISPOSTPONED = "TASK.start = 2"
54			ISTASK = "TASK.type = 0"
55			ISPROJECT = "TASK.type = 1"
56			ISHEADING = "TASK.type = 2"
57			ISOPENTASK = ISTASK + " AND " + ISNOTTRASHED + " AND " + ISOPEN
58
59			# Queries
60			LIST_SOMEDAY = ISOPENTASK + " AND " + ISPOSTPONED + \
61			" AND TASK.startdate IS NULL AND TASK.recurrenceRule IS NULL" + \
62			" ORDER BY TASK.duedate DESC, TASK.creationdate DESC"
63			LIST_INBOX = ISOPENTASK + " AND " + ISNOTSTARTED + \
64			" ORDER BY TASK.duedate DESC , TASK.todayIndex"
65			LIST_ANYTIME = ISOPENTASK + " AND " + ISSTARTED + \
66			" AND TASK.startdate is NULL" + \
67			" AND (TASK.area NOT NULL OR TASK.project in (SELECT uuid FROM " + \
68			TASKTABLE + \
69			" WHERE uuid=TASK.project AND start=1" + \
70			" AND trashed=0))" + \
71			" ORDER BY TASK.duedate DESC , TASK.todayIndex"
72			LIST_TODAY = ISOPENTASK + " AND " + ISSTARTED + \
73			" AND TASK.startdate is NOT NULL" + \
74			" ORDER BY TASK.duedate DESC , TASK.todayIndex"
75			LIST_UPCOMING = ISOPENTASK + " AND " + ISPOSTPONED + \
76			" AND (TASK.startDate NOT NULL OR TASK.recurrenceRule NOT NULL)" + \
77			" ORDER BY TASK.startdate, TASK.todayIndex"
78			LIST_WAITING = ISOPENTASK + \
79			" AND TAGS.tags=(SELECT uuid FROM " + TAGTABLE + \
80			" WHERE title='" + TAG_WAITING + "')" + \
81			" ORDER BY TASK.duedate DESC , TASK.todayIndex"
82			LIST_MIT = ISOPENTASK + " AND " + ISSTARTED + \
83			" AND TASK.startdate is NOT NULL" + \
84			" AND TAGS.tags=(SELECT uuid FROM " + TAGTABLE + \
85			" WHERE title='" + TAG_MIT + "')" + \
86			" ORDER BY TASK.duedate DESC , TASK.todayIndex"
87
88
89			def anonymize(word):
90			"""Scramble output for screenshots."""
91
92			if ANONYMIZE is True:
93			word = list(word)
94			shuffle(word)
95			word = ''.join(word)
96			return word
97
98
99			def get_rows(sql):
100			"""Query Things database."""
101
102			sql = """
103			SELECT DISTINCT
104			TASK.uuid,
105			TASK.title,
106			CASE
107			WHEN AREA.title IS NOT NULL THEN AREA.title
108			WHEN PROJECT.title IS NOT NULL THEN PROJECT.title
109			WHEN HEADING.title IS NOT NULL THEN HEADING.title
110			END,
111			CASE
112			WHEN AREA.uuid IS NOT NULL THEN AREA.uuid
113			WHEN PROJECT.uuid IS NOT NULL THEN PROJECT.uuid
114			END,
115			CASE
116			WHEN TASK.recurrenceRule IS NULL
117			THEN date(TASK.dueDate,"unixepoch")
118			ELSE NULL
119			END
120			FROM
121			TMTask AS TASK
122			LEFT JOIN
123			TMTaskTag TAGS ON TAGS.tasks = TASK.uuid
124			LEFT OUTER JOIN
125			TMTask PROJECT ON TASK.project = PROJECT.uuid
126			LEFT OUTER JOIN
127			TMArea AREA ON TASK.area = AREA.uuid
128			LEFT OUTER JOIN
129			TMTask HEADING ON TASK.actionGroup = HEADING.uuid
130			WHERE """ + sql
131			CURSOR.execute(sql)
132			return CURSOR.fetchall()
133
134
135			def write_html_column(uid, file, header, sql):
136			"""Create a column in the output."""
137
138			rows = get_rows(sql)
139
140			file.write('<div id="left' + str(uid) + '"><div class="inner"><h2>' +
141			header + ' <span class="size">' +
142			str(len(rows)) + '</span></h2>')
143
144			for row in rows:
145			task_uuid = str(row[0]) if row[0] is not None else ''
146			task_title = anonymize(str(row[1])) if row[1] is not None else ''
147			context_title = anonymize(str(row[2])) if row[2] is not None else ''
148			context_uuid = str(row[3]) if row[3] is not None else ''
149			deadline = str(row[4]) if row[4] is not None else ''
150
151			task_link = '<a href="things:///show?id=' + task_uuid + '">' + \
152			task_title + '</a>' if task_uuid != '' else task_title
153			context_link = '<a href="things:///show?id=' + context_uuid + '">' + \
154			context_title + '</a>' if context_uuid != '' else context_title
155			css_class = 'hasProject' if context_title != '' else 'hasNoProject'
156			css_class = 'hasDeadline' if deadline != '' else css_class
157
158			file.write('<div id="box">' + task_link +
159			'<div class="deadline">' + deadline + '</div>' +
160			'<div class="area ' + css_class + '">' + context_link +
161			'</div>' +
162			'</div>')
163			file.write("</div></div>")
164
165
166			def write_html_header(file):
167			"""Write HTML header."""
168
169			message = """<head>
170			<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
171			<link rel="stylesheet" href="../resources/style.css">
172			</head>
173
174			<body>
175			<img id="logo" src="../resources/logo.png" alt="logo" />
176			"""
177			file.write(message)
178
179
180			def write_html_footer(file):
181			"""Write HTML footer."""
182
183			message = """
184			<div id="foot"><br />
185			Copyright ©2018 Luc Beaulieu / 2020 Alexander Willner
186			</div></body></html>"""
187			file.write(message)
188
189
190			def write_html_columns(file):
191			"""Write HTML columns."""
192
193			write_html_column(1, file, "Backlog", LIST_SOMEDAY)
194			write_html_column(2, file, "Upcoming", LIST_UPCOMING)
195			write_html_column(3, file, "Waiting", LIST_WAITING)
196			write_html_column(4, file, "Inbox", LIST_INBOX)
197			write_html_column(5, file, "MIT", LIST_MIT)
198			write_html_column(6, file, "Today", LIST_TODAY)
199			write_html_column(7, file, "Next", LIST_ANYTIME)
200
201
202			def main():
203			"""Convert Things 3 database to Kanban HTML view."""
204
205			file = codecs.open(FILE_HTML, 'w', 'utf-8')
206
207			write_html_header(file)
208
209			write_html_columns(file)
210
211			write_html_footer(file)
212
213			file.close()
214			CURSOR.close()
215
216			webbrowser.open_new_tab('file://' + FILE_HTML)
217
218
219			if __name__ == "__main__":
220			main()
221

AlexanderWillner / KanbanView

Push — master ( f1da93...b76d54 )

things3_to_kanban.get_rows() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like