Added Comments and Variables in DB. sql not backward compatible :(0.2.11

1 files changed, 113 insertions, 90 deletions

diff --git a/sqlite.go b/sqlite.go
index f0633ae..82d2587 100644
--- a/sqlite.go
+++ b/sqlite.go
@@ -18,6 +18,7 @@ import (
 type project struct{
     id int
     name string
+    comment string
     first time.Time
     last time.Time
     finished int // id of last paid bill
@@ -30,6 +31,7 @@ type task struct{
     start time.Time
     stop time.Time
     taskname string
+    comment string
     checkout int // id of bill
 }
 
@@ -75,6 +77,7 @@ func initDB(filename string) {
         CREATE TABLE projects(
             id INTEGER PRIMARY KEY AUTOINCREMENT,
             name VARCHAR(240) NOT NULL,
+            comment VARCHAR(240) DEFAULT '',
             first TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
             last TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
             finished INTEGER DEFAULT NULL,
@@ -85,6 +88,7 @@ func initDB(filename string) {
             start TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
             stop TIMESTAMP DEFAULT '1791-09-30 19:07',
             task VARCHAR(240) NOT NULL,
+            comment VARCHAR(240) DEFAULT '',
             checkout INTEGER DEFAULT NULL);
         CREATE TABLE customers(
             id INTEGER PRIMARY KEY AUTOINCREMENT,
@@ -92,7 +96,7 @@ func initDB(filename string) {
             name VARCHAR(240),
             address VARCHAR(240) DEFAULT 'None',
             satz REAL DEFAULT 1,
-            lastbill TIMESTAMP DEFAULT '1791-09-30 19:07');
+            lastbill TIMESTAMP DEFAULT '1791-09-30 19:07' );
         CREATE TABLE bills(
             id INTEGER PRIMARY KEY AUTOINCREMENT,
             identity VARCHAR(240),
@@ -102,10 +106,18 @@ func initDB(filename string) {
             times VARCHAR(240),
             hours VARCHAR(240),
             moneys VARCHAR(240),
-            paid TIMESTAMP DEFAULT datetime('1791-09-30 19:07','utc'),
-            date TIMESTAMP DEFAULT datetime('1791-09-30 19:07','utc') );
+            paid TIMESTAMP DEFAULT '1791-09-30 19:07',
+            date TIMESTAMP DEFAULT '1791-09-30 19:07' ); 
+	CREATE TABLE vars(
+            id INTEGER PRIMARY KEY AUTOINCREMENT,
+            pauseid INTEGER DEFAULT NULL,
+            last TIMESTAMP DEFAULT '1791-09-30 19:07');
+	CREATE TRIGGER latest AFTER UPDATE ON timetable
+	    BEGIN
+	        update vars SET last = datetime('now','utc') WHERE id = 1;
+	    END;
         `
-        _, err = db.Exec(sqlstmt)
+	_, err = db.Exec(sqlstmt)
         checkErr(err)
         stmt, err := db.Prepare("INSERT INTO customers(id,company,name) values(?, ?, ?)")
         checkErr(err)
@@ -115,6 +127,10 @@ func initDB(filename string) {
         checkErr(err)
         _, err = stmt.Exec(0,"None",0,0)
         checkErr(err)
+        stmt, err = db.Prepare("INSERT INTO vars(pauseid,last) values(?,datetime('now','utc'))")
+        checkErr(err)
+        _, err = stmt.Exec(0)
+        checkErr(err)
     }else{
         db, err = sql.Open("sqlite3", filename)
         checkErr(err)
@@ -338,10 +354,14 @@ func closeTaskTime(tim string) {
     //fmt.Println(timst,timt,opentask.start)
     if (timt.After(opentask.start)) {
         //timstr := timt.UTC().Format("2006-01-02 15:04")
+        com := ""
+	if isInterSure("Do You Want to enter a Comment?"){
+	    com = getInterMultiInput("Comment:")
+	}
         fmt.Println("...Closing Task",opentask.id,"at",timst)
-        stmt, err := db.Prepare("UPDATE timetable SET stop = datetime(?,'utc') WHERE id = ?")
+        stmt, err := db.Prepare("UPDATE timetable SET stop = datetime(?,'utc'), comment = ? WHERE id = ?")
         checkErr(err)
-        _, err = stmt.Exec(timstr,opentask.id)
+        _, err = stmt.Exec(timstr,com,opentask.id)
         checkErr(err)
         opentask.id=0
         updateProject(opentask.projectid)
@@ -360,10 +380,14 @@ func closeTask() {
         return
     }
     if (time.Now().After(opentask.start.Local())) {
+        com := ""
+	if isInterSure("Do You Want to enter a Comment?"){
+	    com = getInterMultiInput("Comment:")
+	}
         fmt.Println("...Closing Task",opentask.id)
-        stmt, err := db.Prepare("UPDATE timetable SET stop = datetime('now') WHERE id = ?")
+        stmt, err := db.Prepare("UPDATE timetable SET stop = datetime('now'), comment = ? WHERE id = ?")
         checkErr(err)
-        _, err = stmt.Exec(opentask.id)
+        _, err = stmt.Exec(com,opentask.id)
         checkErr(err)
         opentask.id=0
         updateProject(opentask.projectid)
@@ -517,13 +541,14 @@ func getProject(id int) (outpr project,outcu customer) {
     checkErr(err)
     var pid,customer,finished int
     var first,last time.Time
-    var name string
+    var name,comm string
 
     for rows.Next() {
-        err = rows.Scan(&pid, &name, &first, &last, &finished, &customer)
+        err = rows.Scan(&pid, &name, &comm, &first, &last, &finished, &customer)
         checkErr(err)
         outpr.id = pid
         outpr.name = name
+	outpr.comment = comm
         outpr.first = first
         outpr.last = last
         outpr.finished = finished
@@ -557,12 +582,12 @@ func getSelectedTasks(in []int) (outtask []task) {
 
     var id,project,checkout int
     var start,stop time.Time
-    var tsk string
+    var tsk,com string
 
     defer rows.Close()
     for rows.Next() {
-        err = rows.Scan(&id,&project,&start,&stop,&tsk,&checkout)
-        outtask = append(outtask,task{id,project,start,stop,tsk,checkout})
+        err = rows.Scan(&id,&project,&start,&stop,&tsk,&com,&checkout)
+        outtask = append(outtask,task{id,project,start,stop,tsk,com,checkout})
     }
     return
 }
@@ -771,9 +796,13 @@ func newProject() {
                 fmt.Println(cust,boldRed("is an invalid ID or Not a known Customer"))
             }
         }
-        stmt, err := db.Prepare("INSERT INTO projects(name, finished,customer) values(?, ?, ?)")
+	comm := ""
+	if isInterSure("Do you want to Comment the Project?"){
+		comm = getInterMultiInput("New Comment: ")
+        }
+	stmt, err := db.Prepare("INSERT INTO projects(name, comment, finished, customer) values(?, ?, ?, ?)")
         checkErr(err)
-        _, err = stmt.Exec(nam,0,icust)
+        _, err = stmt.Exec(nam,comm,0,icust)
         checkErr(err)
         fmt.Println("   Project Created:",nam)
         getLastProject()
@@ -795,7 +824,7 @@ func getClosedTasks(num int) {
     }
     var id, proj,check int
     var sta,sto time.Time
-    var tas string
+    var tas,com string
     var sum, dur float64 = 0.0, 0.0
     checkErr(err)
     first := true
@@ -837,7 +866,7 @@ func getClosedTasks(num int) {
             fmt.Println("___Past Tasks_______________")
             first = false
         }
-        err = rows.Scan(&id, &proj, &sta, &sto, &tas, &check)
+        err = rows.Scan(&id, &proj, &sta, &sto, &tas, &com, &check)
         checkErr(err)
         dur = float64(sto.Sub(sta))/(1000000000*60*60)
         fmt.Printf("%v: %v (%v-%v) - %.2f h\n",id,tas,sta.Local().Format("2006 Mon Jan _2 15:04"),sto.Local().Format("15:04"),dur)
@@ -857,18 +886,19 @@ func getLastProject() {
     checkErr(err)
 
     var uid,nuid int
-    var prname,nprname string
+    var prname,nprname,prcom,nprcom string
     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, &custom)
+        err = rows.Scan(&uid, &prname,&prcom, &first, &last, &finish, &custom)
         checkErr(err)
         if (last.After(nlast)){
             nuid=uid
             nprname=prname
+	    nprcom=prcom
             nfirst=first
             nlast=last
             nfinish=finish
@@ -879,6 +909,7 @@ func getLastProject() {
 
     currproject.id = nuid
     currproject.name = nprname
+    currproject.comment = nprcom
     currproject.first = nfirst
     currproject.last = nlast
     currproject.finished = nfinish
@@ -899,19 +930,20 @@ func setProject (nid int) {
         checkErr(err)
 
         var uid int
-        var prname string
+        var prname,comm string
         var first time.Time
         var last time.Time
         var finish int
         var custo int
 
         for rows.Next() {
-            err = rows.Scan(&uid, &prname, &first, &last, &finish, &custo)
+            err = rows.Scan(&uid, &prname, &comm, &first, &last, &finish, &custo)
             checkErr(err)
         }
         rows.Close() //good habit to close
         currproject.id = uid
         currproject.name = prname
+	currproject.comment = comm
         currproject.first = first
         currproject.last = last
         currproject.finished = finish
@@ -1061,16 +1093,16 @@ func deleteTask(id int) {
     fmt.Println(boldGreen("Deleting Task ",id))
     var chk,prj int
     var start,stop time.Time
-    var task string
-    rows,err := db.Query("SELECT project, start, stop, task, checkout FROM timetable WHERE id = $1",id)
+    var task,comm string
+    rows,err := db.Query("SELECT project, start, stop, task, comment, checkout FROM timetable WHERE id = $1",id)
     checkErr(err)
     if rows.Next() {
-        err = rows.Scan(&prj, &start, &stop, &task, &chk)
+        err = rows.Scan(&prj, &start, &stop, &task, &comm, &chk)
         checkErr(err)
         rows.Close() //good habit to close
         //fmt.Println(boldGreen("Delete Task", id))
         dur := float64(stop.Sub(start))/(1000000000*60*60)
-        fmt.Printf("%v: %v (%v-%v) - %.2f h\n",prj,task,start.Local().Format("2006 Mon Jan _2 15:04"),stop.Local().Format("15:04"),dur)
+        fmt.Printf("%v: %v (%v-%v) - %.2f h\n   Comments:\n%s\n",prj,task,start.Local().Format("2006 Mon Jan _2 15:04"),stop.Local().Format("15:04"),dur,comm)
         if isInterSure("Are You Sure?") {
             stmt, err := db.Prepare("DELETE FROM timetable WHERE id = ?")
             checkErr(err)
@@ -1152,11 +1184,11 @@ func editTask(id int) {
     fmt.Println(boldGreen("Edit Task ",id))
     var chk,prj int
     var start,stop time.Time
-    var task,startstr,stopstr string
-    rows,err := db.Query("SELECT project, start, stop, task, checkout FROM timetable WHERE id = $1",id)
+    var task,startstr,stopstr,comm string
+    rows,err := db.Query("SELECT project, start, stop, task, comment, checkout FROM timetable WHERE id = $1",id)
     checkErr(err)
     if rows.Next() {
-        err = rows.Scan(&prj, &start, &stop, &task, &chk)
+        err = rows.Scan(&prj, &start, &stop, &task, &comm, &chk)
         checkErr(err)
     }else{
         fmt.Println(boldRed("There Is No Task"),id)
@@ -1165,57 +1197,50 @@ func editTask(id int) {
     }
     rows.Close() //good habit to close
 
-    fmt.Println("Old Name:",task)
+/*    fmt.Println("Old Name:",task)
     in := getInterInput("Enter New:")
     if in!=""{
         task=in
-    }
+    }*/
+    task = getNewInterInput("New Task Name: ",task)
+
     startstr=start.Local().Format("2006-01-02 15:04")
     stopstr=stop.Local().Format("2006-01-02 15:04")
     for{
-        fmt.Println("Old Start:",startstr)
-        newstart := getInterInput("Enter New:")
-        if newstart!=""{
-            if isDateTime(newstart) {
-                startstr=newstart
-                break
-            }else{
-                fmt.Println(newstart,boldRed("is Not a Valid Timestring!"), "use: 'YYYY-MM-DD HH:MM' or 'HH:MM'")
-                //os.Exit(0)
-            }
-        }else{break}
-    }
-    fmt.Println("Old End:",stopstr)
+	newstartstr := getNewInterInput("New Start time: ",startstr)
+	if !isDateTime(newstartstr) {
+                fmt.Println(newstartstr,boldRed("is Not a Valid Timestring!"), "use: 'YYYY-MM-DD HH:MM'")
+	}else{
+		startstr = newstartstr
+		break
+	}
+    }
+    //fmt.Println("Old End:",stopstr)
     for{
-        newend := getInterInput("Enter New:")
-        if newend!=""{
+        newend := getNewInterInput("New Stop time: ",stopstr)
             if isDateTime(newend) {
                 stopstr=newend
                 break
             }else{
                 fmt.Println(newend,boldRed("is Not a Valid Timestring!"), "use: 'YYYY-MM-DD HH:MM' or 'HH:MM'")
-                //os.Exit(0)
             }
-        }else{break}
     }
-    fmt.Println("Old Project:",prj)
+    //fmt.Println("Old Project:",prj)
     for{
-        newprj := getInterInput("Enter New:")
-        if newprj!=""{
+        newprj := getNewInterInput("New Project id: ",fmt.Sprint(prj))
             prj,err = strconv.Atoi(newprj)
             if err != nil {
                 fmt.Println(newprj,boldRed("is Not a Valid id."), "Try an Integer instead")
-                //os.Exit(0)
             }
             if !isProject(prj) {
-                fmt.Println(boldRed("There is no project"),prj,"Try -all for Project List")
-                //os.Exit(0)
+                fmt.Println(boldRed("There is no project"),prj)
             }else{break}
-        }else{break}
     }
-    stmt, err := db.Prepare("UPDATE timetable SET task = ?, start = datetime(?,'utc'), stop = datetime(?,'utc'), project = ? WHERE id = ?")
+    comm = getNewInterMultiInput("New Comment: ",comm)
+
+    stmt, err := db.Prepare("UPDATE timetable SET task = ?, comment = ?, start = datetime(?,'utc'), stop = datetime(?,'utc'), project = ? WHERE id = ?")
     checkErr(err)
-    _, err = stmt.Exec(task,startstr,stopstr,prj,id)
+    _, err = stmt.Exec(task,comm,startstr,stopstr,prj,id)
     checkErr(err)
     updateProject(prj)
     fmt.Println("...Task",id,"Updated")
@@ -1227,51 +1252,49 @@ func editProject(id int) {
     fmt.Println(boldGreen("Edit Project ",id))
     var fin,cust int
     var first time.Time
-    var name,nfirst string
-    rows,err := db.Query("SELECT name, first, finished, customer FROM projects WHERE id = $1",id)
+    var name,nfirst,comm string
+    rows,err := db.Query("SELECT name, comment, first, finished, customer FROM projects WHERE id = $1",id)
     checkErr(err)
     if rows.Next() {
-        err = rows.Scan(&name, &first, &fin, &cust)
+        err = rows.Scan(&name, &comm, &first, &fin, &cust)
         checkErr(err)
-        fmt.Println("Old Name:",name)
-        in := getInterInput("Enter New:")
-        if in!=""{
-            name=in
-        }
+
+	name = getNewInterInput("New Name: ",name)
         nfirst=first.Local().Format("2006-01-02 15:04")
-        fmt.Println("Old Begin:",nfirst)
-        for{
-            newfirst := getInterInput("Enter New:")
-            if newfirst!=""{
-                if isDateTime(newfirst) {
-                    nfirst=newfirst
-                    break
-                }else{
-                    fmt.Println(newfirst,boldRed("is Not a Valid Timestring!"), "use: 'YYYY-MM-DD HH:MM' or 'HH:MM'")
-                    //os.Exit(0)
-                }
-            }else{break}
-        }
-        fmt.Println("Old Customer:",cust)
-        for{
-            custs := getInterInput("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"))
-            }
-        }
+	// Get New PRoject Begin Date
+	for{
+		newfirststr := getNewInterInput("New Begin time: ",nfirst)
+		if !isDateTime(newfirststr) {
+			fmt.Println(newfirststr,boldRed("is Not a Valid Timestring!"), "use: 'YYYY-MM-DD HH:MM'")
+		}else{
+			nfirst = newfirststr
+			break
+		}
+	}
+	// Get New Customer
+	for{
+		newcu := getNewInterInput("New Customer id: ",fmt.Sprint(cust))
+		icust,err := strconv.Atoi(newcu)
+		if err != nil {
+			fmt.Println(newcu,boldRed("is Not a Valid id."), "Try an Integer instead")
+		}else if !isCustomer(icust) {
+			fmt.Println(boldRed("There is no Customer"),icust)
+		}else{
+			cust = icust
+			break
+		}
+	}
+	// Get Comment
+	comm = getNewInterMultiInput("New Comment: ",comm)
     }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'), customer = ? WHERE id = ?")
+    stmt, err := db.Prepare("UPDATE projects SET name = ?, comment = ?, last = datetime(?,'utc'), customer = ? WHERE id = ?")
     checkErr(err)
-    _, err = stmt.Exec(name,nfirst,cust,id)
+    _, err = stmt.Exec(name,comm,nfirst,cust,id)
     checkErr(err)
     updateProject(id)
     fmt.Println("...Project",id,"Updated")