LaTeX templating and bills begin

1 files changed, 132 insertions, 11 deletions

diff --git a/sqlite.go b/sqlite.go
index 7a8dc4d..38b488e 100644
--- a/sqlite.go
+++ b/sqlite.go
@@ -9,6 +9,7 @@ import (
     "strings"
     "regexp"
     "strconv"
+    "unicode/utf8"
 
     _ "github.com/mattn/go-sqlite3"
     "github.com/fatih/color"
@@ -37,6 +38,7 @@ 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)
@@ -59,6 +61,7 @@ func initDB(filename string) {
             checkout INTEGER NOT NULL);
         CREATE TABLE customers(
             id INTEGER PRIMARY KEY AUTOINCREMENT,
+            company VARCHAR(240),
             name VARCHAR(240),
             address VARCHAR(240) DEFAULT 'None',
             satz REAL DEFAULT 1,
@@ -66,16 +69,19 @@ func initDB(filename string) {
         CREATE TABLE bills(
             id INTEGER PRIMARY KEY AUTOINCREMENT,
             identity VARCHAR(240),
+            timerange VARCHAR(240),
             project INTEGER NOT NULL,
             tasks VARCHAR(240),
-            text VARCHAR(240),
+            times VARCHAR(240),
+            hours VARCHAR(240),
+            moneys 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(?, ?)")
+        stmt, err := db.Prepare("INSERT INTO customers(id,company,name) values(?, ?, ?)")
         checkErr(err)
-        _, err = stmt.Exec(0,"Annonymus")
+        _, err = stmt.Exec(0,"None","Annonymus")
         checkErr(err)
     }else{
         db, err = sql.Open("sqlite3", filename)
@@ -214,6 +220,66 @@ func updateProject(id int) {
     checkErr(err)
 }
 
+func getCustomerName(id int) (cust string) {
+    rows, err := db.Query("SELECT company, name FROM customers WHERE id = ?",id)
+    checkErr(err)
+    var nam,com string
+    for rows.Next() {
+        err = rows.Scan(&com, &nam)
+        checkErr(err)
+    }
+    cust = fmt.Sprintf("%s: %s",com,nam)
+    return
+}
+
+func getProjectName(id int) (pro string,cust string) {
+    rows, err := db.Query("SELECT name, customer FROM projects WHERE id = ?",id)
+    checkErr(err)
+    //rows, err := stmt.Exec(id)
+    //checkErr(err)
+    cid := 0
+    for rows.Next() {
+        err = rows.Scan(&pro, &cid)
+        checkErr(err)
+    }
+    cust = getCustomerName(cid)
+    return
+}
+
+func getUnfinishedList() ([]int, []string) {
+    var outids []int
+    var outstr []string
+    lastpr := 0
+    pre := ""
+    prelen := 0
+    rows,err := db.Query("SELECT id, project, start, stop, task FROM timetable WHERE stop != '1791-09-30 19:07' AND checkout = 0 ORDER BY project DESC, stop DESC")
+    var id,project int
+    var task string
+    var start,stop time.Time
+
+    defer rows.Close()
+    for rows.Next() {
+        err = rows.Scan(&id, &project, &start, &stop, &task)
+        checkErr(err)
+        if project != lastpr {
+            pr,_ := getProjectName(project)
+            pre =fmt.Sprintf(" %v: ",pr)
+            prelen = utf8.RuneCountInString(pre)
+            lastpr = project
+            //outstr[len(outstr)-1]=fmt.Sprintf("%s\n%s - %s",outstr[len(outstr)-1],cu,pr)
+        }else{
+            pre = strings.Repeat(" ", prelen)
+        }
+        dur := float64(stop.Sub(start))/(1000000000*60*60)
+        durstr := fmt.Sprintf("%v - %v",start.Local().Format("Mon Jan _2 2006 15:04"),stop.Local().Format("15:04"))
+        txt := fmt.Sprintf("%s - (%v) - %.2f h",task, durstr, dur)
+
+        outids = append(outids,id)
+        outstr = append(outstr,fmt.Sprintf("%s%s",pre,txt))
+    }
+    return outids,outstr
+}
+
 func getOpenTask() {
     rows,err := db.Query("SELECT id, project, start, task, checkout FROM timetable WHERE stop = '1791-09-30 19:07'")
     checkErr(err)
@@ -256,17 +322,18 @@ func showLastProject() {
     }
 }
 func addCustomer() {
+    com := getInput("Enter Customer Company: ")
     nam := getInput("Enter Customer Name: ")
-    add := getInput("Enter Address (separate lines by ;): ")
+    add := getInput("Enter Address (separate lines by ;  [Street;Zip;City]): ")
     satstr := getInput("Hourly Rate: ")
     sat,err := strconv.ParseFloat(satstr,64)
     checkErr(err)
 
-    stmt, err := db.Prepare("INSERT INTO customers(name, address, satz) values(?, ?, ?)")
+    stmt, err := db.Prepare("INSERT INTO customers(company, name, address, satz) values(?, ?, ?, ?)")
     checkErr(err)
-    _, err = stmt.Exec(nam,add,sat)
+    _, err = stmt.Exec(com,nam,add,sat)
     checkErr(err)
-    fmt.Println("   Customer Added:",nam, add, sat)
+    fmt.Println("   Customer Added:",com ,nam, add, sat)
 }
 
 func newProject() {
@@ -398,9 +465,11 @@ func setProject (nid int) {
 
 func allCustomers() {
     rows,err := db.Query("SELECT * FROM customers")
+    //rows,err := db.Query("SELECT (id, company, name, address, satz, lastbill) FROM customers")
     checkErr(err)
 
     var uid int
+    var comp string
     var name string
     var addr string
     var satz float64
@@ -408,9 +477,9 @@ func allCustomers() {
 
     fmt.Println("___All Customers________________")
     for rows.Next() {
-        err = rows.Scan(&uid, &name, &addr, &satz, &last)
+        err = rows.Scan(&uid, &comp, &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"))
+        fmt.Printf(" %v:%s: %s, Rate:%.2f(€/h) , Last:%s\n",uid,comp,name,satz,last.Local().Format("2006-01-02 15:04 MST"))
     }
 }
 
@@ -420,12 +489,13 @@ func allProjects() {
     checkErr(err)
 
     var cid int
+    var com string
     var nam string
     var adr string
     var sat float64
     var lst time.Time
     for rows3.Next() {
-        err = rows3.Scan(&cid, &nam, &adr, &sat, &lst)
+        err = rows3.Scan(&cid, &com, &nam, &adr, &sat, &lst)
         checkErr(err)
 
         rows,err := db.Query("SELECT * FROM projects WHERE customer = $1",cid)
@@ -439,7 +509,7 @@ func allProjects() {
         var customer int
         var start,stop time.Time
 
-        fmt.Println("____For",nam)
+        fmt.Println("____For",com,nam)
         for rows.Next() {
             err = rows.Scan(&uid, &prname, &first, &last, &finish, &customer)
             checkErr(err)
@@ -490,6 +560,57 @@ func deleteTask(id int) {
     }
 }
 
+func editCustomer(id int) {
+    boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
+    boldRed := color.New(color.FgRed, color.Bold).SprintFunc()
+    var comp,name,addr string
+    var satz float64
+    rows,err := db.Query("SELECT company, name, address, satz FROM customers WHERE id = $1",id)
+    checkErr(err)
+    if rows.Next() {
+        err = rows.Scan(&comp, &name, &addr, &satz)
+        checkErr(err)
+    }else{
+        fmt.Println(boldRed("There Is No Customer"),id)
+        return
+        //os.Exit(0)
+    }
+    rows.Close() //good habit to close
+
+    fmt.Println(boldGreen("Edit Customer",id))
+    fmt.Println("Old Company Name:",comp)
+    in := getInput("Enter New:")
+    if in!=""{
+        comp=in
+    }
+    fmt.Println("Old Name:",name)
+    in = getInput("Enter New:")
+    if in!=""{
+        name=in
+    }
+    fmt.Println("Old Adress:",addr)
+    in = getInput("Enter New:")
+    if in!=""{
+        addr=in
+    }
+    fmt.Println("Old Hourly Rate:",satz)
+    for{
+        satzstr := getInput("Enter New:")
+        if satzstr!=""{
+            satz,err = strconv.ParseFloat(satzstr,64)
+            if err != nil {
+                fmt.Println(satzstr,boldRed("can not be Parsed as a Float."), "Try a shape of X.X")
+                //os.Exit(0)
+            }else{break}
+        }else{break}
+    }
+    stmt, err := db.Prepare("UPDATE customers SET company = ?, name = ?, address = ?, satz = ? WHERE id = ?")
+    checkErr(err)
+    _, err = stmt.Exec(comp,name,addr,satz,id)
+    checkErr(err)
+    fmt.Println("...Customer",id,"Updated")
+}
+
 func editTask(id int) {
     boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
     boldRed := color.New(color.FgRed, color.Bold).SprintFunc()