1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
|
// Copyright (C) 2022 The Qt Company Ltd.
// SPDX-License-Identifier: LicenseRef-Qt-Commercial OR LGPL-3.0-only OR GPL-2.0-only OR GPL-3.0-only
pragma Singleton
import QtQml
import QtQuick.LocalStorage
QtObject {
id: root
property var _db
function _database() {
if (_db)
return _db
try {
let db = LocalStorage.openDatabaseSync("ToDoList", "1.0", "ToDoList application database")
db.transaction(function (tx) {
tx.executeSql(`CREATE TABLE IF NOT EXISTS projects (
project_id INTEGER PRIMARY KEY AUTOINCREMENT,
project_name TEXT NOT NULL CHECK(project_name != ''),
project_note TEXT
)`);
})
db.transaction(function (tx) {
tx.executeSql(`CREATE TABLE IF NOT EXISTS tasks (
task_id INTEGER PRIMARY KEY AUTOINCREMENT,
task_name TEXT CHECK(task_name != ''),
done INTEGER,
project_id,
FOREIGN KEY(project_id) REFERENCES projects(project_id)
)`);
})
_db = db
} catch (error) {
console.log("Error opening database: " + error)
};
return _db
}
function getProjects() {
let projects = []
root._database().transaction(function (tx) {
let results = tx.executeSql('SELECT * FROM projects')
for (let i = 0; i < results.rows.length; i++) {
let projectRow = results.rows.item(i)
let projectId = projectRow.project_id
let completedTasks = Math.max(countDoneTasksByProject(projectId).rows.length, 0)
let totalTasks = Math.max(countTaskByProject(projectId).rows.length, 0)
projects.push({
"projectName": projectRow.project_name,
"projectId": projectId,
"projectNote": projectRow.project_note ?? "",
"completedTasks": completedTasks,
"totalTasks": totalTasks
})
}
})
return projects
}
function newProject(projectName) {
let results
root._database().transaction(function (tx) {
results = tx.executeSql("INSERT INTO projects (project_name) VALUES(?)", [projectName])
})
return results
}
function updateProjectNote(projectId, projectNote) {
root._database().transaction(function (tx) {
tx.executeSql("UPDATE projects set project_note=? WHERE project_id=?", [projectNote, projectId])
})
}
function updateProjectName(projectId, projectName) {
root._database().transaction(function (tx) {
tx.executeSql("UPDATE projects set project_name=? WHERE project_id=?", [projectName, projectId])
})
}
function deleteProject(projectId) {
root._database().transaction(function (tx) {
deleteAllTasks(projectId)
tx.executeSql("DELETE FROM projects WHERE project_id = ?", [projectId])
})
}
function getTaskByProject(projectId) {
if (!projectId)
return
let tasks = []
root._database().transaction(function (tx) {
let results = tx.executeSql("SELECT * FROM tasks WHERE project_id = " + [projectId] + " ORDER BY done")
for (let i = 0; i < results.rows.length; i++) {
let row = results.rows.item(i)
tasks.push({
"taskId": row.task_id,
"taskName": row.task_name,
"done": row.done === 1 ? true : false
})
}
})
return tasks
}
function countTaskByProject(projectId) {
let results
root._database().transaction(function (tx) {
results = tx.executeSql('SELECT task_id FROM tasks WHERE project_id =' + [projectId])
})
return results
}
function countDoneTasksByProject(projectId) {
let results
root._database().transaction(function (tx) {
results = tx.executeSql("SELECT task_id FROM tasks WHERE project_id =" + [projectId] + " AND done = 1")
})
return results
}
function newTask(projectId, taskName) {
let results
root._database().transaction(function (tx) {
results = tx.executeSql("INSERT INTO tasks (task_name, done, project_id) VALUES(?, 0, ?)", [taskName, projectId])
})
return results
}
function updateTaskName(taskId, taskName) {
root._database().transaction(function (tx) {
tx.executeSql("UPDATE tasks set task_name=? WHERE task_id=?", [taskName, taskId])
})
}
function updateDoneState(taskId, doneState) {
root._database().transaction(function (tx) {
tx.executeSql("UPDATE tasks set done=? WHERE task_id=?", [doneState, taskId])
})
}
function deleteAllTasks(projectId) {
root._database().transaction(function (tx) {
tx.executeSql("DELETE FROM tasks WHERE project_id =" + projectId)
})
}
function deleteTask(taskId) {
root._database().transaction(function (tx) {
tx.executeSql("DELETE FROM tasks WHERE task_id = ?", [taskId])
})
}
}
|
