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
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
|
package main
import (
"database/sql"
"fmt"
"time"
"os"
_ "github.com/mattn/go-sqlite3"
)
type project struct{
id int
name string
first time.Time
last time.Time
finish int
}
type task struct{
id int
projectid int
start time.Time
stop time.Time
taskname string
checkout bool
}
var db *sql.DB
var err error
var currproject project
var opentask task
func initDB(filename string) {
if _, err := os.Stat(filename); os.IsNotExist(err) {
db, err = sql.Open("sqlite3", filename)
checkErr(err)
fmt.Println("Creating new DB")
sqlstmt := `
CREATE TABLE projects(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(240) NOT NULL,
first TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
finished INTEGER NOT NULL);
CREATE TABLE timetable(
id INTEGER PRIMARY KEY AUTOINCREMENT,
project INTEGER NOT NULL,
start TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
stop TIMESTAMP DEFAULT '1791-09-30 19:07',
task VARCHAR(240) NOT NULL,
checkout INTEGER NOT NULL);
`
_, err = db.Exec(sqlstmt)
checkErr(err)
}else{
db, err = sql.Open("sqlite3", filename)
checkErr(err)
fmt.Println("Opening DB")
}
}
func newTask(proj int ,task string) {
stmt, err := db.Prepare("INSERT INTO timetable(project, task, checkout) values(?, ?, ?)")
checkErr(err)
if (proj == 0) {
_, err = stmt.Exec(currproject.id,task,0)
}else{
_, err = stmt.Exec(proj,task,0)
}
checkErr(err)
fmt.Println("...New Task inserted into",currproject.name,": ",task)
getOpenTask()
updateProject(currproject.id)
}
func closeTask() {
fmt.Println("...Closing Task",opentask.id)
stmt, err := db.Prepare("UPDATE timetable SET stop = datetime('now') WHERE id = ?")
checkErr(err)
_, err = stmt.Exec(opentask.id)
checkErr(err)
opentask.id=0
updateProject(opentask.projectid)
}
func updateProject(id int) {
stmt, err := db.Prepare("UPDATE projects SET last = datetime('now') WHERE id = ?")
checkErr(err)
_, err = stmt.Exec(id)
checkErr(err)
}
func getOpenTask() {
rows,err := db.Query("SELECT id, project, start, task, checkout FROM timetable WHERE stop = '1791-09-30 19:07'")
checkErr(err)
var id,project,checkout int
var task string
var start time.Time
defer rows.Close()
for rows.Next() {
err = rows.Scan(&id, &project, &start , &task, &checkout)
checkErr(err)
//fmt.Println(id, "Open Task:", task, project, start)
}
//rows.Close() //good habit to close
opentask.id = id
opentask.projectid = project
opentask.start = start
opentask.taskname = task
opentask.checkout = checkout != 0
}
func showOpenTask() {
if (opentask.id==0) {
fmt.Println("___No Open Tasks____________")
} else {
fmt.Println("___Open Task________________")
fmt.Println(opentask.id,":", opentask.taskname, opentask.projectid, opentask.start.Local().Format("Mon Jan _2 15:04"))
}
}
func showLastProject() {
if (currproject.id==0) {
fmt.Println("Empty DB")
} else {
fmt.Println("___Last Project_____________")
fmt.Println(currproject.id,":", currproject.name, currproject.last.Local().Format("Mon Jan _2 15:04 2006"))
}
}
func newProject(nam string) {
stmt, err := db.Prepare("INSERT INTO projects(name, finished) values(?, ?)")
checkErr(err)
_, err = stmt.Exec(nam,0)
checkErr(err)
fmt.Println(" Project Created:",nam)
getLastProject()
}
func getClosedTasks(num int) {
//var rows sql.Rows
//var err error
fmt.Println("___Past Tasks________________")
rows,err := db.Query("SELECT * FROM timetable WHERE stop != '1791-09-30 19:07' ORDER BY datetime(start)",currproject.id)
checkErr(err)
if num > 0 {
rows,err = db.Query("SELECT * FROM timetable WHERE project = $1 AND stop != '1791-09-30 19:07'ORDER BY datetime(start) DESC LIMIT $2",currproject.id,num)
checkErr(err)
} else {
rows,err = db.Query("SELECT * FROM timetable WHERE project = $1 AND stop != '1791-09-30 19:07'ORDER BY datetime(start)",currproject.id)
checkErr(err)
}
var id, proj,check int
var sta,sto time.Time
var tas string
checkErr(err)
for rows.Next() {
err = rows.Scan(&id, &proj, &sta, &sto, &tas, &check)
checkErr(err)
fmt.Println(id,tas,sta.Local().Format("Mon Jan _2 15:04"),sto.Local().Format("15:04"))
}
rows.Close()
}
func getLastProject() {
rows,err := db.Query("SELECT * FROM projects")
checkErr(err)
var uid,nuid int
var prname,nprname string
var first,nfirst time.Time
var last,nlast time.Time
var finish,nfinish int
for rows.Next() {
err = rows.Scan(&uid, &prname, &first, &last, &finish)
checkErr(err)
if (last.After(nlast)){
nuid=uid
nprname=prname
nfirst=first
nlast=last
nfinish=finish
}
}
rows.Close() //good habit to close
// fmt.Println(nuid,"Last Project",nprname)
// fmt.Println(nlast)
// fmt.Println("----------------------------------")
currproject.id = nuid
currproject.name = nprname
currproject.first = nfirst
currproject.last = nlast
currproject.finish = nfinish
// getLastTasks(0)
}
func setProject (nid int) {
rows,err := db.Query("SELECT * FROM projects WHERE id = $1",nid)
checkErr(err)
var uid int
var prname string
var first time.Time
var last time.Time
var finish int
for rows.Next() {
err = rows.Scan(&uid, &prname, &first, &last, &finish)
checkErr(err)
}
rows.Close() //good habit to close
currproject.id = uid
currproject.name = prname
currproject.first = first
currproject.last = last
currproject.finish = finish
updateProject(uid)
}
func allProjects() {
rows,err := db.Query("SELECT * FROM projects")
checkErr(err)
var uid int
var prname string
var first time.Time
var last time.Time
var finish int
fmt.Println("___All Projects________________")
for rows.Next() {
err = rows.Scan(&uid, &prname, &first, &last, &finish)
checkErr(err)
fmt.Println(" ",uid,prname,first.Local().Format("2006-09-12 19:13"),last.Local().Format("2006-09-12 19:13"),finish)
}
rows.Close() //good habit to close
}
func checkErr(err error) {
if err != nil {
panic(err)
}
}
|
