Cleaned the last commit0.3.0

1 files changed, 52 insertions, 13 deletions

diff --git a/sqlite.go b/sqlite.go
index 82d2587..666e2ca 100644
--- a/sqlite.go
+++ b/sqlite.go
@@ -41,7 +41,7 @@ type customer struct{
     name string
     address string
     satz float64
-    lastbill time.Time
+    lastbill time.Time  // Last time a bill was paid
 }
 
 type billitem struct{
@@ -112,9 +112,13 @@ func initDB(filename string) {
             id INTEGER PRIMARY KEY AUTOINCREMENT,
             pauseid INTEGER DEFAULT NULL,
             last TIMESTAMP DEFAULT '1791-09-30 19:07');
+	CREATE TRIGGER first AFTER INSERT ON timetable
+	    BEGIN
+	        update vars SET last = datetime('now') WHERE id = 1;
+	    END;
 	CREATE TRIGGER latest AFTER UPDATE ON timetable
 	    BEGIN
-	        update vars SET last = datetime('now','utc') WHERE id = 1;
+	        update vars SET last = datetime('now') WHERE id = 1;
 	    END;
         `
 	_, err = db.Exec(sqlstmt)
@@ -127,17 +131,29 @@ 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'))")
+        stmt, err = db.Prepare("INSERT INTO vars(pauseid,last) values(?,datetime('now'))")
         checkErr(err)
         _, err = stmt.Exec(0)
         checkErr(err)
     }else{
         db, err = sql.Open("sqlite3", filename)
         checkErr(err)
-        fmt.Println("Opening DB",filename)
+        fmt.Println("Opening DB", filename ,"  -  Last Usage:",lastUsage())
     }
 }
 
+func lastUsage() (out string) {
+	var dat time.Time
+	rows,err := db.Query("SELECT last FROM vars WHERE id = 1")
+        checkErr(err)
+	for rows.Next() {
+		err = rows.Scan(&dat)
+		checkErr(err)
+	}
+	out = dat.Local().Format("2006 Mon Jan _2 15:04")
+	return
+}
+
 func newTaskTime(proj int, tim string) {
     boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
     boldRed := color.New(color.FgRed, color.Bold).SprintFunc()
@@ -232,7 +248,7 @@ func showLastBills(count int) {
     boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
     cn := "all"
     if count > 0 {
-        cn = string(count)
+        cn = fmt.Sprint(count)
     }
     fmt.Printf(boldGreen("Loading %s Bills\n"),cn)
     rows, err := db.Query("SELECT * FROM timetable")
@@ -399,13 +415,21 @@ func closeTask() {
 func checkBill(bid int) {
     boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
     boldRed := color.New(color.FgRed, color.Bold).SprintFunc()
+    var custid int
+
+    if !isBill(bid) {
+	fmt.Println(bid,boldRed("is not a known bill ID"))
+	return
+    }
 
     bar := []int{bid}
     bill := loadBills(bar)
+
     if len(bill)<1 {
 	return
     }else{
 	pr,cu := getProjectName(bill[0].project)
+	custid = getCustomerId(bill[0].project)
 	//fmt.Println(bill[0]) 
 	fmt.Println(boldGreen("Checking Bill ",bid," as Paid:\n",bill[0].identity),"\n For",cu," :",pr)
 	_,_,ho,ma := items2strings(bill[0].items)
@@ -415,10 +439,6 @@ func checkBill(bid int) {
 	//fmt.Println(ta)
     }
 
-    if !isBill(bid) {
-	fmt.Println(bid,boldRed("is not a known bill ID"))
-	return
-    }
     //timst,timstr := "1791-09-30 19:07","1791-09-30 19:07"
     //zone, _ := time.Now().Zone()
     timstr := "1791-09-30 19:07"
@@ -439,6 +459,10 @@ func checkBill(bid int) {
     checkErr(err)
     _, err = stmt.Exec(timstr,bid)
     checkErr(err)
+    stmt, err = db.Prepare("UPDATE customers SET lastbill = datetime(?,'utc') WHERE id = ?")
+    checkErr(err)
+    _, err = stmt.Exec(timstr,custid)
+    checkErr(err)
 }
 
 func checkTasks(in []int,billid int) {
@@ -469,6 +493,16 @@ func updateProject(id int) {
     checkErr(err)
 }
 
+func getCustomerId(id int) (cust int) {
+    rows, err := db.Query("SELECT customer FROM projects WHERE id = ?",id)
+    checkErr(err)
+    for rows.Next() {
+        err = rows.Scan(&cust)
+        checkErr(err)
+    }
+    return
+}
+
 func getCustomerName(id int) (cust string) {
     rows, err := db.Query("SELECT company, name FROM customers WHERE id = ?",id)
     checkErr(err)
@@ -988,7 +1022,11 @@ func allCustomers() {
     for rows.Next() {
         err = rows.Scan(&uid, &comp, &name, &addr, &satz, &last)
         checkErr(err)
-        fmt.Printf(" %v:%s: %s, Rate:%.2f(€/h) , Last:%s\n",uid,comp,name,satz,last.Local().Format("2006-01-02 15:04 MST"))
+	lstr := last.Local().Format("2006-01-02 15:04 MST")
+	if lstr == "1791-09-30 20:12 LMT"{
+		lstr = "Never"
+	}
+        fmt.Printf(" %v:%s: %s, Rate: %.2f[€/h] , Last Paid Bill: %s\n",uid,comp,name,satz,lstr)
     }
 }
 
@@ -1013,7 +1051,7 @@ func allProjects() {
         checkErr(err)
 
         var uid int
-        var prname string
+        var prname,comm string
         var first time.Time
         var last time.Time
         var finish int
@@ -1022,7 +1060,7 @@ func allProjects() {
 
         fmt.Println("____For",com,nam)
         for rows.Next() {
-            err = rows.Scan(&uid, &prname, &first, &last, &finish, &customer)
+            err = rows.Scan(&uid, &prname, &comm, &first, &last, &finish, &customer)
             checkErr(err)
             rows2,err := db.Query("SELECT start, stop, checkout FROM timetable WHERE project = $1 AND stop != '1791-09-30 19:07'",uid)
             checkErr(err)
@@ -1041,7 +1079,8 @@ func allProjects() {
             if (sumo+sumb) > 0 {
                 fmt.Printf(" %v:%s \n",uid,prname)
                 fmt.Printf("  Unbilled: %.2f[h]  Billed: %.2f[h] | Total: %.2f[h]\n",sumo,sumb,sumo+sumb)
-                fmt.Printf("  First: %s, Last:%s, Fin:%v, For:%v\n\n",first.Local().Format("2006-01-02 15:04 MST"),last.Local().Format("2006-01-02 15:04 MST"),finish,customer)
+                //fmt.Printf("  First: %s, Last:%s, Fin:%v, For:%v\n\n",first.Local().Format("2006-01-02 15:04 MST"),last.Local().Format("2006-01-02 15:04 MST"),finish,customer)
+                fmt.Printf("  First: %s, Last:%s, \n\n",first.Local().Format("2006-01-02 15:04 MST"),last.Local().Format("2006-01-02 15:04 MST"))
             }else{
                 fmt.Print(" Nothing\n")
             }