diff options
Diffstat (limited to 'sqlite.go')
| -rw-r--r-- | sqlite.go | 91 |
1 files changed, 74 insertions, 17 deletions
@@ -19,7 +19,8 @@ type project struct{ name string first time.Time last time.Time - finish int + finish bool + customer int } type task struct{ @@ -47,7 +48,8 @@ func initDB(filename string) { name VARCHAR(240) NOT NULL, first TIMESTAMP DEFAULT CURRENT_TIMESTAMP, last TIMESTAMP DEFAULT CURRENT_TIMESTAMP, - finished INTEGER NOT NULL); + finished INTEGER NOT NULL, + customer INTEGER DEFAULT NULL); CREATE TABLE timetable( id INTEGER PRIMARY KEY AUTOINCREMENT, project INTEGER NOT NULL, @@ -55,6 +57,12 @@ func initDB(filename string) { stop TIMESTAMP DEFAULT '1791-09-30 19:07', task VARCHAR(240) NOT NULL, checkout INTEGER NOT NULL); + CREATE TABLE customers( + id INTEGER PRIMARY KEY AUTOINCREMENT, + name VARCHAR(240), + address VARCHAR(240), + satz REAL, + lastbill TIMESTAMP DEFAULT '1791-09-30 19:07'); ` _, err = db.Exec(sqlstmt) checkErr(err) @@ -66,7 +74,9 @@ func initDB(filename string) { } func newTaskTime(proj int, tim string) { + boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() boldRed := color.New(color.FgRed, color.Bold).SprintFunc() + fmt.Println(boldGreen("Start Project at ",tim)) if (opentask.id == 0) { timstr := "1791-09-30 19:07" if isDate(tim) { @@ -101,7 +111,9 @@ func newTaskTime(proj int, tim string) { } func newTask(proj int) { + boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() boldRed := color.New(color.FgRed, color.Bold).SprintFunc() + fmt.Println(boldGreen("Starting new Task")) if (opentask.id == 0) { task := getInput("Specify Task: ") stmt, err := db.Prepare("INSERT INTO timetable(project, task, checkout) values(?, ?, ?)") @@ -122,7 +134,9 @@ func newTask(proj int) { } func closeTaskTime(tim string) { + boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() boldRed := color.New(color.FgRed, color.Bold).SprintFunc() + fmt.Println(boldGreen("Stoping Task ",opentask.id,":",opentask.taskname)) if opentask.id==0 { fmt.Println(boldRed("There is no Open Task")) return @@ -162,7 +176,9 @@ func closeTaskTime(tim string) { } func closeTask() { + boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() boldRed := color.New(color.FgRed, color.Bold).SprintFunc() + fmt.Println(boldGreen("Stoping Task ",opentask.id,":",opentask.taskname)) if opentask.id==0 { fmt.Println(boldRed("There is no Open Task")) return @@ -234,9 +250,19 @@ func newProject() { boldRed := color.New(color.FgRed, color.Bold).SprintFunc() if (opentask.id == 0) { nam := getInput("Enter Project Name: ") - stmt, err := db.Prepare("INSERT INTO projects(name, finished) values(?, ?)") + icust := 0 + for{ + cust := getInput("Enter Customer id (0 for none): ") + icust,err = strconv.Atoi(cust) + if (err == nil && (isCustomer(icust) || icust == 0)) { + break + } else { + fmt.Println(cust,boldRed("is an invalid ID or Not a known Customer")) + } + } + stmt, err := db.Prepare("INSERT INTO projects(name, finished,customer) values(?, ?, ?)") checkErr(err) - _, err = stmt.Exec(nam,0) + _, err = stmt.Exec(nam,0,icust) checkErr(err) fmt.Println(" Project Created:",nam) getLastProject() @@ -285,16 +311,18 @@ func getLastProject() { var first,nfirst time.Time var last,nlast time.Time var finish,nfinish int + var custom,ncustom int for rows.Next() { - err = rows.Scan(&uid, &prname, &first, &last, &finish) + err = rows.Scan(&uid, &prname, &first, &last, &finish, &custom) checkErr(err) if (last.After(nlast)){ nuid=uid nprname=prname nfirst=first nlast=last - nfinish=finish + nfinish=finish + ncustom=custom } } rows.Close() //good habit to close @@ -303,11 +331,14 @@ func getLastProject() { currproject.name = nprname currproject.first = nfirst currproject.last = nlast - currproject.finish = nfinish + currproject.finish = nfinish != 0 + currproject.customer = ncustom } func setProject (nid int) { + boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() boldRed := color.New(color.FgRed, color.Bold).SprintFunc() + fmt.Println(boldGreen("Opening Project ",nid)) if !isProject(nid){ fmt.Println(boldRed("There is no Project"),nid) return @@ -331,7 +362,7 @@ func setProject (nid int) { currproject.name = prname currproject.first = first currproject.last = last - currproject.finish = finish + currproject.finish = finish != 0 updateProject(uid) } else { fmt.Println(boldRed("There is an Open Task")) @@ -348,17 +379,19 @@ func allProjects() { var first time.Time var last time.Time var finish int + var customer int fmt.Println("___All Projects________________") for rows.Next() { - err = rows.Scan(&uid, &prname, &first, &last, &finish) + err = rows.Scan(&uid, &prname, &first, &last, &finish, &customer) checkErr(err) - fmt.Println(" ",uid,prname,first.Local().Format("2006-09-02 19:13"),last.Local().Format("2006-09-02 19:13"),finish) + fmt.Println(" ",uid,prname,first.Local().Format("2006-09-02 19:13"),last.Local().Format("2006-09-02 19:13"),finish,customer) } rows.Close() //good habit to close } func editTask(id int) { + boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() boldRed := color.New(color.FgRed, color.Bold).SprintFunc() var chk,prj int var start,stop time.Time @@ -375,7 +408,7 @@ func editTask(id int) { } rows.Close() //good habit to close - fmt.Println("Edit Task",id) + fmt.Println(boldGreen("Edit Task ",id)) fmt.Println("Old Name:",task) in := getInput("Enter New:") if in!=""{ @@ -433,16 +466,17 @@ func editTask(id int) { } func editProject(id int) { + boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() boldRed := color.New(color.FgRed, color.Bold).SprintFunc() - var fin int + var fin,cust int var first time.Time var name,nfirst string - rows,err := db.Query("SELECT name, first, finished FROM projects WHERE id = $1",id) + rows,err := db.Query("SELECT name, first, finished, customer FROM projects WHERE id = $1",id) checkErr(err) if rows.Next() { - err = rows.Scan(&name, &first, &fin) + err = rows.Scan(&name, &first, &fin, &cust) checkErr(err) - fmt.Println("Edit Project",id) + fmt.Println(boldGreen("Edit Project ",id)) fmt.Println("Old Name:",name) in := getInput("Enter New:") if in!=""{ @@ -462,15 +496,26 @@ func editProject(id int) { } }else{break} } + fmt.Println("Old Customer:",cust) + for{ + custs := getInput("Enter Customer id (0 for none): ") + icust,err := strconv.Atoi(custs) + if (err == nil && (isCustomer(icust) || icust == 0)) { + cust = icust + break + } else { + fmt.Println(custs,boldRed("is an invalid ID or Not a known Customer")) + } + } }else{ fmt.Println(boldRed("There Is No Project"),id) return //os.Exit(0) } rows.Close() //good habit to close - stmt, err := db.Prepare("UPDATE projects SET name = ?, last = datetime(?,'utc') WHERE id = ?") + stmt, err := db.Prepare("UPDATE projects SET name = ?, last = datetime(?,'utc'), customer = ? WHERE id = ?") checkErr(err) - _, err = stmt.Exec(name,nfirst,id) + _, err = stmt.Exec(name,nfirst,cust,id) checkErr(err) updateProject(id) fmt.Println("...Project",id,"Updated") @@ -488,6 +533,18 @@ func isProject(id int) bool { } } +func isCustomer(id int) bool { + + rows,err := db.Query("SELECT * FROM customers WHERE id = $1",id) + checkErr(err) + defer rows.Close() + if rows.Next() { + return true + } else { + return false + } +} + func getDate(in string) string { r := regexp.MustCompile(`(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2})`) return r.FindString(in) |