diff options
Diffstat (limited to 'sqlite.go')
| -rw-r--r-- | sqlite.go | 249 |
1 files changed, 249 insertions, 0 deletions
diff --git a/sqlite.go b/sqlite.go new file mode 100644 index 0000000..271a429 --- /dev/null +++ b/sqlite.go @@ -0,0 +1,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) + } +} |