From ad7b23c17dbe05e7e6a4ad3ea023f692f832a938 Mon Sep 17 00:00:00 2001 From: Nikolaus Gotsche Date: Wed, 29 Aug 2018 04:27:16 +0200 Subject: LaTeX templating and bills begin --- sqlite.go | 143 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++----- 1 file changed, 132 insertions(+), 11 deletions(-) (limited to 'sqlite.go') 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() -- cgit v1.2.3