Show All Customers; Sort All Projects By Customer

2 files changed, 66 insertions, 20 deletions

diff --git a/main.go b/main.go
index a674f5a..d17972c 100644
--- a/main.go
+++ b/main.go
@@ -83,7 +83,7 @@ func init() {
 
     // CUSTOMIZE USAGE
     flag.Usage = func() {
-        fmt.Fprintf(os.Stderr, "Usage of %s: [Options] [Database]\n   Version 0.1.3\n\n       [Database] - must have .db fileextension, only one allowed\n\n       [Options]:\n", os.Args[0])
+        fmt.Fprintf(os.Stderr, "Usage of %s   Version 0.1.4:\n   %s [Options] [Database]\n\n       [Database] - must have .db fileextension, only one allowed\n\n       [Options]:\n", os.Args[0],os.Args[0])
         flag.PrintDefaults()
     }
 
diff --git a/sqlite.go b/sqlite.go
index 8874e80..d651852 100644
--- a/sqlite.go
+++ b/sqlite.go
@@ -60,12 +60,23 @@ func initDB(filename string) {
         CREATE TABLE customers(
             id INTEGER PRIMARY KEY AUTOINCREMENT,
             name VARCHAR(240),
-            address VARCHAR(240),
-            satz REAL,
+            address VARCHAR(240) DEFAULT 'None',
+            satz REAL DEFAULT 1,
             lastbill TIMESTAMP DEFAULT '1791-09-30 19:07');
+        CREATE TABLE bills(
+            id INTEGER PRIMARY KEY AUTOINCREMENT,
+            identity VARCHAR(240),
+            project INTEGER NOT NULL,
+            tasks VARCHAR(240),
+            text VARCHAR(240),
+            date TIMESTAMP DEFAULT '1791-09-30 19:07');
         `
         _, err = db.Exec(sqlstmt)
         checkErr(err)
+        stmt, err := db.Prepare("INSERT INTO customers(id, name) values(?, ?)")
+        checkErr(err)
+        _, err = stmt.Exec(0,"Annonymus")
+        checkErr(err)
     }else{
         db, err = sql.Open("sqlite3", filename)
         checkErr(err)
@@ -263,6 +274,7 @@ func newProject() {
     if (opentask.id == 0) {
         nam := getInput("Enter Project Name: ")
         icust := 0
+        allCustomers()
         for{
             cust := getInput("Enter Customer id (0 for none): ")
             icust,err = strconv.Atoi(cust)
@@ -384,34 +396,68 @@ func setProject (nid int) {
     }
 }
 
-func allProjects() {
-    rows,err := db.Query("SELECT * FROM projects")
+func allCustomers() {
+    rows,err := db.Query("SELECT * FROM customers")
     checkErr(err)
 
     var uid int
-    var prname string
-    var first time.Time
+    var name string
+    var addr string
+    var satz float64
     var last time.Time
-    var finish int
-    var customer int
-    var start,stop time.Time
 
-    fmt.Println("___All Projects________________")
+    fmt.Println("___All Customers________________")
     for rows.Next() {
-        err = rows.Scan(&uid, &prname, &first, &last, &finish, &customer)
+        err = rows.Scan(&uid, &name, &addr, &satz, &last)
+        checkErr(err)
+        fmt.Printf(" %v:%s, Rate:%.2f(€/h) , Last:%s\n",uid,name,satz,last.Local().Format("2006-01-02 15:04 MST"))
+    }
+}
+
+func allProjects() {
+    fmt.Println("___All Projects________________")
+    rows3,err := db.Query("SELECT * FROM customers")
+    checkErr(err)
+
+    var cid int
+    var nam string
+    var adr string
+    var sat float64
+    var lst time.Time
+    for rows3.Next() {
+        err = rows3.Scan(&cid, &nam, &adr, &sat, &lst)
         checkErr(err)
-        rows2,err := db.Query("SELECT start, stop FROM timetable WHERE project = $1 AND checkout = 0 AND stop != '1791-09-30 19:07'",uid)
+
+        rows,err := db.Query("SELECT * FROM projects WHERE customer = $1",cid)
         checkErr(err)
-        sum := 0.0
-        for rows2.Next() {
-            err = rows2.Scan(&start,&stop)
+
+        var uid int
+        var prname string
+        var first time.Time
+        var last time.Time
+        var finish int
+        var customer int
+        var start,stop time.Time
+
+        fmt.Printf("____For %s________________\n",nam)
+        for rows.Next() {
+            err = rows.Scan(&uid, &prname, &first, &last, &finish, &customer)
             checkErr(err)
-            sum += float64(stop.Sub(start))/(1000000000*60*60)
-        }
+            rows2,err := db.Query("SELECT start, stop FROM timetable WHERE project = $1 AND checkout = 0 AND stop != '1791-09-30 19:07'",uid)
+            checkErr(err)
+            sum := 0.0
+            for rows2.Next() {
+                err = rows2.Scan(&start,&stop)
+                checkErr(err)
+                sum += float64(stop.Sub(start))/(1000000000*60*60)
+            }
 
-        fmt.Printf(" %v:%s First: %s, Last:%s, Total:%.2f(h) ,Fin:%v, For:%v\n",uid,prname,first.Local().Format("2006-09-02 19:13"),last.Local().Format("2006-09-02 19:13"),sum,finish,customer)
+            fmt.Printf(" %v:%s \n  First: %s, Last:%s, Total:%.2f(h) ,Fin:%v, For:%v\n",uid,prname,first.Local().Format("2006-01-02 15:04 MST"),last.Local().Format("2006-01-02 15:04 MST"),sum,finish,customer)
+            rows2.Close() //good habit to close
+        }
+        rows.Close() //good habit to close
     }
-    rows.Close() //good habit to close
+    rows3.Close() //good habit to close
 }
 
 func deleteTask(id int) {