diff options
Diffstat (limited to 'sqlite.go')
| -rw-r--r-- | sqlite.go | 2431 |
1 files changed, 1220 insertions, 1211 deletions
@@ -1,65 +1,65 @@ +// sqlite.go contains all database operations package main import ( - "database/sql" - "fmt" - "time" - "os" - //"bufio" - "strings" - "regexp" - "strconv" - "unicode/utf8" - - _ "github.com/mattn/go-sqlite3" - "github.com/fatih/color" + "database/sql" + "fmt" + "os" + "regexp" + "strconv" + "strings" + "time" + "unicode/utf8" + + "github.com/fatih/color" + _ "github.com/mattn/go-sqlite3" ) -type project struct{ - id int - name string - comment string - first time.Time - last time.Time - finished int // id of last paid bill - customer int +type project struct { + id int + name string + comment string + first time.Time + last time.Time + finished int // id of last paid bill + customer int } -type task struct{ - id int - projectid int - start time.Time - stop time.Time - taskname string - comment string - checkout int // id of bill +type task struct { + id int + projectid int + start time.Time + stop time.Time + taskname string + comment string + checkout int // id of bill } -type customer struct{ - id int - company string - name string - address string - satz float64 - lastbill time.Time // Last time a bill was paid +type customer struct { + id int + company string + name string + address string + satz float64 + lastbill time.Time // Last time a bill was paid } -type billitem struct{ - Task string - Time string - Hours float64 - Money float64 +type billitem struct { + Task string + Time string + Hours float64 + Money float64 } -type bill struct{ - id int - identity string //invoice number - timerange string - project int - projectname string - date time.Time - paid time.Time - items []billitem +type bill struct { + id int + identity string //invoice number + timerange string + project int + projectname string + date time.Time + paid time.Time + items []billitem } var db *sql.DB @@ -68,12 +68,16 @@ var currproject project var opentask task var pausetask int +// Database Operations + +// initDB tries to open an sqlite database of given filename +// and if it doesnt exit create it in the propper structure. func initDB(filename string) { - if _, err := os.Stat(filename); os.IsNotExist(err) { - db, err = sql.Open("sqlite3", filename) - checkErr(err) - fmt.Println("Creating new DB",filename) - sqlstmt := ` + if _, err := os.Stat(filename); os.IsNotExist(err) { + db, err = sql.Open("sqlite3", filename) + checkErr(err) + fmt.Println("Creating new DB", filename) + sqlstmt := ` CREATE TABLE projects( id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(240) NOT NULL, @@ -121,31 +125,31 @@ func initDB(filename string) { update vars SET last = datetime('now') WHERE id = 1; END; ` - _, err = db.Exec(sqlstmt) - checkErr(err) - stmt, err := db.Prepare("INSERT INTO customers(id,company,name) values(?, ?, ?)") - checkErr(err) - _, err = stmt.Exec(0,"No one","Specific") - checkErr(err) - stmt, err = db.Prepare("INSERT INTO projects(id,name,customer,finished) values(?, ?, ?, ?)") - checkErr(err) - _, err = stmt.Exec(0,"None",0,0) - checkErr(err) - 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 ," - Last Usage:",lastUsage()) - } + _, err = db.Exec(sqlstmt) + checkErr(err) + stmt, err := db.Prepare("INSERT INTO customers(id,company,name) values(?, ?, ?)") + checkErr(err) + _, err = stmt.Exec(0, "No one", "Specific") + checkErr(err) + stmt, err = db.Prepare("INSERT INTO projects(id,name,customer,finished) values(?, ?, ?, ?)") + checkErr(err) + _, err = stmt.Exec(0, "None", 0, 0) + checkErr(err) + 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, " - Last Usage:", lastUsage()) + } } func lastUsage() (out string) { var dat time.Time - rows,err := db.Query("SELECT last FROM vars WHERE id = 1") - checkErr(err) + rows, err := db.Query("SELECT last FROM vars WHERE id = 1") + checkErr(err) for rows.Next() { err = rows.Scan(&dat) checkErr(err) @@ -155,8 +159,8 @@ func lastUsage() (out string) { } func getPauseTask() (id int) { - rows,err := db.Query("SELECT pauseid FROM vars WHERE id = 1") - checkErr(err) + rows, err := db.Query("SELECT pauseid FROM vars WHERE id = 1") + checkErr(err) for rows.Next() { err = rows.Scan(&id) checkErr(err) @@ -165,1304 +169,1309 @@ func getPauseTask() (id int) { } func setPauseTask(id int) { - stmt, err := db.Prepare("UPDATE vars SET pauseid = ? WHERE id = 1 ") - checkErr(err) - _, err = stmt.Exec(id) - checkErr(err) + stmt, err := db.Prepare("UPDATE vars SET pauseid = ? WHERE id = 1 ") + checkErr(err) + _, err = stmt.Exec(id) + checkErr(err) pausetask = id } func newTaskTime(proj int, tim string) { - boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() - boldRed := color.New(color.FgRed, color.Bold).SprintFunc() - fmt.Println(boldGreen("Start Project at ",tim)) - if (opentask.id == 0) { - timstr := "1791-09-30 19:07" - //zone, _ := time.Now().Zone() - if isDateTime(tim) { - timstr = getDateTime(tim) - //timst = timstr+" "+zone - }else if isTime(tim) { - currdate := time.Now().Local().Format("2006-01-02") - timstr = currdate+" "+getTime(tim) - //timst = timstr+" "+zone - }else { - fmt.Println(tim,boldRed("is Not a Valid Timestring!"), "use: 'YYYY-MM-DD HH:MM' or 'HH:MM'") - return - //os.Exit(0) - } - stmt, err := db.Prepare("INSERT INTO timetable(project, start, task, checkout) values(?, datetime(?,'utc'), ?, ?)") - fmt.Println(timstr) - task := getInterInput("Specify Task: ") - checkErr(err) - if (proj == 0) { - _, err = stmt.Exec(currproject.id,timstr,task,0) - }else{ - _, err = stmt.Exec(proj,timstr,task,0) - } - checkErr(err) - - fmt.Println("...new task inserted into",currproject.name,": ",task) - getOpenTask() - updateProject(currproject.id) - } else { - fmt.Println(boldRed("Another Task is already Open")) - showOpenTask() - } + boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() + boldRed := color.New(color.FgRed, color.Bold).SprintFunc() + fmt.Println(boldGreen("Start Project at ", tim)) + if opentask.id == 0 { + timstr := "1791-09-30 19:07" + //zone, _ := time.Now().Zone() + if isDateTime(tim) { + timstr = getDateTime(tim) + //timst = timstr+" "+zone + } else if isTime(tim) { + currdate := time.Now().Local().Format("2006-01-02") + timstr = currdate + " " + getTime(tim) + //timst = timstr+" "+zone + } else { + fmt.Println(tim, boldRed("is Not a Valid Timestring!"), "use: 'YYYY-MM-DD HH:MM' or 'HH:MM'") + return + //os.Exit(0) + } + stmt, err := db.Prepare("INSERT INTO timetable(project, start, task, checkout) values(?, datetime(?,'utc'), ?, ?)") + fmt.Println(timstr) + task := getInterInput("Specify Task: ") + checkErr(err) + if proj == 0 { + _, err = stmt.Exec(currproject.id, timstr, task, 0) + } else { + _, err = stmt.Exec(proj, timstr, task, 0) + } + checkErr(err) + + fmt.Println("...new task inserted into", currproject.name, ": ", task) + getOpenTask() + updateProject(currproject.id) + } else { + fmt.Println(boldRed("Another Task is already Open")) + showOpenTask() + } } func newTask(resume bool) { - boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() - boldRed := color.New(color.FgRed, color.Bold).SprintFunc() - task := "" - if (opentask.id > 0) { - fmt.Println(boldRed("Another Task is already Open")) - showOpenTask() - return - } - if resume { - if pausetask == 0 { - fmt.Println(boldRed("No Task was Paused")) + boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() + boldRed := color.New(color.FgRed, color.Bold).SprintFunc() + task := "" + if opentask.id > 0 { + fmt.Println(boldRed("Another Task is already Open")) + showOpenTask() return - }else{ - idx := []int{pausetask} - tsks := getSelectedTasks(idx) - fulltask := tsks[0] - fmt.Println(boldGreen("Resuming Task ",pausetask," - ",fulltask.taskname)) - //fmt.Println() - task = fulltask.taskname - } - }else{ - fmt.Println(boldGreen("Starting new Task")) - task = getInterInput("Specify Task: ") - } - stmt, err := db.Prepare("INSERT INTO timetable(project, task, checkout) values(?, ?, ?)") - checkErr(err) - _, err = stmt.Exec(currproject.id,task,0) - checkErr(err) - if !resume{ - fmt.Println("...New Task inserted into",currproject.name,": ",task) - } - getOpenTask() - updateProject(currproject.id) + } + if resume { + if pausetask == 0 { + fmt.Println(boldRed("No Task was Paused")) + return + } else { + idx := []int{pausetask} + tsks := getSelectedTasks(idx) + fulltask := tsks[0] + fmt.Println(boldGreen("Resuming Task ", pausetask, " - ", fulltask.taskname)) + //fmt.Println() + task = fulltask.taskname + } + } else { + fmt.Println(boldGreen("Starting new Task")) + task = getInterInput("Specify Task: ") + } + stmt, err := db.Prepare("INSERT INTO timetable(project, task, checkout) values(?, ?, ?)") + checkErr(err) + _, err = stmt.Exec(currproject.id, task, 0) + checkErr(err) + if !resume { + fmt.Println("...New Task inserted into", currproject.name, ": ", task) + } + getOpenTask() + updateProject(currproject.id) } -func newBill(proj int) (int,string) { - boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() - //boldRed := color.New(color.FgRed, color.Bold).SprintFunc() - fmt.Println(boldGreen("Creating New Bill")) - //Show 5 recent bills - showLastBills(5) - invno := getInterInput("Invoice Number: ") - stmt, err := db.Prepare("INSERT INTO bills (identity, project, date) values(?, ?, datetime('now'))") - checkErr(err) - answ, err := stmt.Exec(invno,proj) - checkErr(err) - lid,_ := answ.LastInsertId() - fmt.Println("Bill",invno,"Created with ID",lid) - return int(lid), invno +func newBill(proj int) (int, string) { + boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() + //boldRed := color.New(color.FgRed, color.Bold).SprintFunc() + fmt.Println(boldGreen("Creating New Bill")) + //Show 5 recent bills + showLastBills(5) + invno := getInterInput("Invoice Number: ") + stmt, err := db.Prepare("INSERT INTO bills (identity, project, date) values(?, ?, datetime('now'))") + checkErr(err) + answ, err := stmt.Exec(invno, proj) + checkErr(err) + lid, _ := answ.LastInsertId() + fmt.Println("Bill", invno, "Created with ID", lid) + return int(lid), invno } func saveBill(in bill) { - boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() - tasks,times,hours,moneys := items2strings(in.items) - fmt.Println(boldGreen("Saving Bill"),in.id) - stmt, err := db.Prepare("UPDATE bills SET identity = ?, timerange = ?, tasks = ?, times = ?, hours = ?, moneys = ? WHERE id = ?") - checkErr(err) - _, err = stmt.Exec(in.identity,in.timerange,tasks,times,hours,moneys,in.id) - checkErr(err) + boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() + tasks, times, hours, moneys := items2strings(in.items) + fmt.Println(boldGreen("Saving Bill"), in.id) + stmt, err := db.Prepare("UPDATE bills SET identity = ?, timerange = ?, tasks = ?, times = ?, hours = ?, moneys = ? WHERE id = ?") + checkErr(err) + _, err = stmt.Exec(in.identity, in.timerange, tasks, times, hours, moneys, in.id) + checkErr(err) } func showLastBills(count int) { - boldRed := color.New(color.FgRed, color.Bold).SprintFunc() - boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() - cn := "all" - if count > 0 { - cn = fmt.Sprint(count) - } - fmt.Printf(boldGreen("Loading %s Bills\n"),cn) - rows, err := db.Query("SELECT * FROM timetable") - if count == 0 { - rows, err = db.Query("SELECT * FROM bills ORDER BY date ASC") - }else if count > 0 { - rows, err = db.Query("SELECT * FROM bills ORDER BY date ASC LIMIT ?",count) - } - checkErr(err) - var id,proj int - var ident,timerange string - var date,paid time.Time - var taskstr,timestr,hourstr,moneystr string - defer rows.Close() - - //fmt.Println("___Open Task________________") - if count == 0 { - fmt.Print("___All Previous Bills_______\n") - }else{ - fmt.Printf("___Previous %v Bills________\n",count) - } - i := 0 - for rows.Next() { - i++ - err = rows.Scan(&id,&ident,&timerange,&proj,&taskstr,×tr,&hourstr,&moneystr,&paid,&date) - checkErr(err) - prn,_ := getProjectName(proj) - hsum := sumFloatArray(string2FloatArray(hourstr,";")) - msum := sumFloatArray(string2FloatArray(moneystr,";")) - fmt.Printf(" %v:%s - %s (%v) %.1f[h]: %.2f[€] - ",id,ident,prn,date.Local().Format("2006.01.02"),hsum,msum) - p := fmt.Sprintf("%v",paid) - if p == "1791-09-30 19:07:00 +0000 UTC" { - fmt.Print(boldRed("OPEN\n")) - }else{ - fmt.Printf(boldGreen("%v\n"),paid.Local().Format("2006.01.02")) - } - } - if i == 0 { - fmt.Println(boldRed("\n NONE")) - } - fmt.Println(" ") + boldRed := color.New(color.FgRed, color.Bold).SprintFunc() + boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() + cn := "all" + if count > 0 { + cn = fmt.Sprint(count) + } + fmt.Printf(boldGreen("Loading %s Bills\n"), cn) + rows, err := db.Query("SELECT * FROM timetable") + if count == 0 { + rows, err = db.Query("SELECT * FROM bills ORDER BY date ASC") + } else if count > 0 { + rows, err = db.Query("SELECT * FROM bills ORDER BY date ASC LIMIT ?", count) + } + checkErr(err) + var id, proj int + var ident, timerange string + var date, paid time.Time + var taskstr, timestr, hourstr, moneystr string + defer rows.Close() + + //fmt.Println("___Open Task________________") + if count == 0 { + fmt.Print("___All Previous Bills_______\n") + } else { + fmt.Printf("___Previous %v Bills________\n", count) + } + i := 0 + for rows.Next() { + i++ + err = rows.Scan(&id, &ident, &timerange, &proj, &taskstr, ×tr, &hourstr, &moneystr, &paid, &date) + checkErr(err) + prn, _ := getProjectName(proj) + hsum := sumFloatArray(string2FloatArray(hourstr, ";")) + msum := sumFloatArray(string2FloatArray(moneystr, ";")) + fmt.Printf(" %v:%s - %s (%v) %.1f[h]: %.2f[€] - ", id, ident, prn, date.Local().Format("2006.01.02"), hsum, msum) + p := fmt.Sprintf("%v", paid) + if p == "1791-09-30 19:07:00 +0000 UTC" { + fmt.Print(boldRed("OPEN\n")) + } else { + fmt.Printf(boldGreen("%v\n"), paid.Local().Format("2006.01.02")) + } + } + if i == 0 { + fmt.Println(boldRed("\n NONE")) + } + fmt.Println(" ") } -func loadBills(in []int) (out []bill){ - ins := strings.Trim(strings.Replace(fmt.Sprint(in)," "," , ",-1),"[]") - que := fmt.Sprintf("SELECT * FROM bills WHERE id IN (%s) ORDER BY project DESC",ins) - rows,err := db.Query(que) - - var id,proj int - var ident,timerange string - var date,paid time.Time - var taskstr,timestr,hourstr,moneystr string - - defer rows.Close() - for rows.Next() { - err = rows.Scan(&id,&ident,&timerange,&proj,&taskstr,×tr,&hourstr,&moneystr,&paid,&date) - checkErr(err) - itms := strings2items(taskstr,timestr,hourstr,moneystr) - prname,_ := getProjectName(proj) - bi := bill{id,ident,timerange,proj,prname,date,paid,itms} - out = append(out,bi) - } - return +func loadBills(in []int) (out []bill) { + ins := strings.Trim(strings.Replace(fmt.Sprint(in), " ", " , ", -1), "[]") + que := fmt.Sprintf("SELECT * FROM bills WHERE id IN (%s) ORDER BY project DESC", ins) + rows, err := db.Query(que) + + var id, proj int + var ident, timerange string + var date, paid time.Time + var taskstr, timestr, hourstr, moneystr string + + defer rows.Close() + for rows.Next() { + err = rows.Scan(&id, &ident, &timerange, &proj, &taskstr, ×tr, &hourstr, &moneystr, &paid, &date) + checkErr(err) + itms := strings2items(taskstr, timestr, hourstr, moneystr) + prname, _ := getProjectName(proj) + bi := bill{id, ident, timerange, proj, prname, date, paid, itms} + out = append(out, bi) + } + return } func strings2items(tasks, times, hours, moneys string) (out []billitem) { - ta := string2StringArray(tasks,";") - ti := string2StringArray(times,";") - ho := string2FloatArray(hours,";") - mo := string2FloatArray(moneys,";") - for i,_ := range ta{ - out = append(out,billitem{ta[i],ti[i],ho[i],mo[i]}) - } - return + ta := string2StringArray(tasks, ";") + ti := string2StringArray(times, ";") + ho := string2FloatArray(hours, ";") + mo := string2FloatArray(moneys, ";") + for i, _ := range ta { + out = append(out, billitem{ta[i], ti[i], ho[i], mo[i]}) + } + return } func items2strings(in []billitem) (tasks, times, hours, moneys string) { - var tsk,tim []string - var hrs,mny []float64 - for _,item := range in { - tsk = append(tsk,item.Task) - tim = append(tim,item.Time) - hrs = append(hrs,item.Hours) - mny = append(mny,item.Money) - } - tasks = stringArray2String(tsk,";") - times = stringArray2String(tim,";") - hours = strings.Trim(strings.Replace(fmt.Sprint(hrs)," ",";",-1),"[]") - moneys = strings.Trim(strings.Replace(fmt.Sprint(mny)," ",";",-1),"[]") - return + var tsk, tim []string + var hrs, mny []float64 + for _, item := range in { + tsk = append(tsk, item.Task) + tim = append(tim, item.Time) + hrs = append(hrs, item.Hours) + mny = append(mny, item.Money) + } + tasks = stringArray2String(tsk, ";") + times = stringArray2String(tim, ";") + hours = strings.Trim(strings.Replace(fmt.Sprint(hrs), " ", ";", -1), "[]") + moneys = strings.Trim(strings.Replace(fmt.Sprint(mny), " ", ";", -1), "[]") + return } func closeTaskTime(tim string) { - boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() - boldRed := color.New(color.FgRed, color.Bold).SprintFunc() - fmt.Println(boldGreen("Stoping Task ",opentask.id,":",opentask.taskname)) - if opentask.id==0 { - fmt.Println(boldRed("There is no Open Task")) - return - } - //timt,err := time.Parse("2006-01-02 15:04",tim) - timst,timstr := "1791-09-30 19:07","1791-09-30 19:07" - zone, _ := time.Now().Zone() - if isDateTime(tim) { - timstr = getDateTime(tim) - timst = timstr+" "+zone - } else if isTime(tim) { - timstr = time.Now().Local().Format("2006-01-02")+" "+getTime(tim) - timst = timstr+" "+zone - } else { - fmt.Println(tim,boldRed("is Not a Valid Timestring!"), "use: 'YYYY-MM-DD HH:MM' or 'HH:MM'") - return - //os.Exit(0) - - } - timt,err := time.Parse("2006-01-02 15:04 MST",timst) - checkErr(err) + boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() + boldRed := color.New(color.FgRed, color.Bold).SprintFunc() + fmt.Println(boldGreen("Stoping Task ", opentask.id, ":", opentask.taskname)) + if opentask.id == 0 { + fmt.Println(boldRed("There is no Open Task")) + return + } + //timt,err := time.Parse("2006-01-02 15:04",tim) + timst, timstr := "1791-09-30 19:07", "1791-09-30 19:07" + zone, _ := time.Now().Zone() + if isDateTime(tim) { + timstr = getDateTime(tim) + timst = timstr + " " + zone + } else if isTime(tim) { + timstr = time.Now().Local().Format("2006-01-02") + " " + getTime(tim) + timst = timstr + " " + zone + } else { + fmt.Println(tim, boldRed("is Not a Valid Timestring!"), "use: 'YYYY-MM-DD HH:MM' or 'HH:MM'") + return + //os.Exit(0) - //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'), comment = ? WHERE id = ?") - checkErr(err) - _, err = stmt.Exec(timstr,com,opentask.id) - checkErr(err) - opentask.id=0 - updateProject(opentask.projectid) - }else{ - fmt.Println(boldRed("Cannot Stop before the Beginning!")) - } - //fmt.Println(tim,timt) + } + timt, err := time.Parse("2006-01-02 15:04 MST", timst) + checkErr(err) + + //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'), comment = ? WHERE id = ?") + checkErr(err) + _, err = stmt.Exec(timstr, com, opentask.id) + checkErr(err) + opentask.id = 0 + updateProject(opentask.projectid) + } else { + fmt.Println(boldRed("Cannot Stop before the Beginning!")) + } + //fmt.Println(tim,timt) } func closeTask(loud bool) { - boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() - boldRed := color.New(color.FgRed, color.Bold).SprintFunc() - if loud { - fmt.Println(boldGreen("Stoping Task ",opentask.id,":",opentask.taskname)) - } - if opentask.id==0 { - fmt.Println(boldRed("There is no Open Task")) - return - } - if (time.Now().After(opentask.start.Local())) { - com := "" + boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() + boldRed := color.New(color.FgRed, color.Bold).SprintFunc() if loud { - if isInterSure("Do You Want to enter a Comment?"){ - com = getInterMultiInput("Comment:") + fmt.Println(boldGreen("Stoping Task ", opentask.id, ":", opentask.taskname)) + } + if opentask.id == 0 { + fmt.Println(boldRed("There is no Open Task")) + return + } + if time.Now().After(opentask.start.Local()) { + com := "" + if loud { + if isInterSure("Do You Want to enter a Comment?") { + com = getInterMultiInput("Comment:") + } + fmt.Println("...Closing Task", opentask.id) } - fmt.Println("...Closing Task",opentask.id) - } - stmt, err := db.Prepare("UPDATE timetable SET stop = datetime('now'), comment = ? WHERE id = ?") - checkErr(err) - _, err = stmt.Exec(com,opentask.id) - checkErr(err) - opentask.id=0 - updateProject(opentask.projectid) - }else{ - fmt.Println(boldRed("Cannot Stop before the Beginning!")) - } + stmt, err := db.Prepare("UPDATE timetable SET stop = datetime('now'), comment = ? WHERE id = ?") + checkErr(err) + _, err = stmt.Exec(com, opentask.id) + checkErr(err) + opentask.id = 0 + updateProject(opentask.projectid) + } else { + fmt.Println(boldRed("Cannot Stop before the Beginning!")) + } } func checkBill(bid int) { - boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() - boldRed := color.New(color.FgRed, color.Bold).SprintFunc() - var custid 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 - } + if !isBill(bid) { + fmt.Println(bid, boldRed("is not a known bill ID")) + return + } - bar := []int{bid} - bill := loadBills(bar) + 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) - hsum := sumFloatArray(string2FloatArray(ho,";")) - msum := sumFloatArray(string2FloatArray(ma,";")) - fmt.Printf(" Date: %s Hours: %.1f[h] Sum: %.2f[€]\n",bill[0].date.Local().Format("2006-01-02"),hsum,msum) - //fmt.Println(ta) - } + 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) + hsum := sumFloatArray(string2FloatArray(ho, ";")) + msum := sumFloatArray(string2FloatArray(ma, ";")) + fmt.Printf(" Date: %s Hours: %.1f[h] Sum: %.2f[€]\n", bill[0].date.Local().Format("2006-01-02"), hsum, msum) + //fmt.Println(ta) + } - //timst,timstr := "1791-09-30 19:07","1791-09-30 19:07" - //zone, _ := time.Now().Zone() - timstr := "1791-09-30 19:07" + //timst,timstr := "1791-09-30 19:07","1791-09-30 19:07" + //zone, _ := time.Now().Zone() + timstr := "1791-09-30 19:07" - timin := getInterInput("Specify Date (YYYY-MM-DD): ") + timin := getInterInput("Specify Date (YYYY-MM-DD): ") - if isDateTime(timin) { - timstr = getDateTime(timin) - }else if isDate(timin) { - //currdate := time.Now().Local().Format("2006-01-02") - timstr = getDate(timin)+" 12:00" - }else { - fmt.Println(timin,boldRed("is Not a Valid Datestring!"), "use: 'YYYY-MM-DD'") - return - } - fmt.Println(boldGreen(timstr)) - stmt, err := db.Prepare("UPDATE bills SET paid = datetime(?,'utc') WHERE id = ?") - 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) + if isDateTime(timin) { + timstr = getDateTime(timin) + } else if isDate(timin) { + //currdate := time.Now().Local().Format("2006-01-02") + timstr = getDate(timin) + " 12:00" + } else { + fmt.Println(timin, boldRed("is Not a Valid Datestring!"), "use: 'YYYY-MM-DD'") + return + } + fmt.Println(boldGreen(timstr)) + stmt, err := db.Prepare("UPDATE bills SET paid = datetime(?,'utc') WHERE id = ?") + 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) { - ins := strings.Trim(strings.Replace(fmt.Sprint(in)," "," , ",-1),"[]") - que := fmt.Sprintf("UPDATE timetable SET checkout = ? WHERE id IN (%s)",ins) - //rows,err := db.Query(que) - stmt, err := db.Prepare(que) - checkErr(err) - _, err = stmt.Exec(billid) - checkErr(err) +func checkTasks(in []int, billid int) { + ins := strings.Trim(strings.Replace(fmt.Sprint(in), " ", " , ", -1), "[]") + que := fmt.Sprintf("UPDATE timetable SET checkout = ? WHERE id IN (%s)", ins) + //rows,err := db.Query(que) + stmt, err := db.Prepare(que) + checkErr(err) + _, err = stmt.Exec(billid) + checkErr(err) } func uncheckTasks(billid int) { - //ins := strings.Trim(strings.Replace(fmt.Sprint(in)," "," , ",-1),"[]") - //que := fmt.Sprintf("UPDATE timetable SET checkout = ? WHERE id IN (%s)",ins) - //rows,err := db.Query(que) - //stmt, err := db.Prepare(que) - stmt, err := db.Prepare("UPDATE timetable SET checkout = 0 WHERE checkout = ?") - checkErr(err) - _, err = stmt.Exec(billid) - checkErr(err) + //ins := strings.Trim(strings.Replace(fmt.Sprint(in)," "," , ",-1),"[]") + //que := fmt.Sprintf("UPDATE timetable SET checkout = ? WHERE id IN (%s)",ins) + //rows,err := db.Query(que) + //stmt, err := db.Prepare(que) + stmt, err := db.Prepare("UPDATE timetable SET checkout = 0 WHERE checkout = ?") + checkErr(err) + _, err = stmt.Exec(billid) + checkErr(err) } func updateProject(id int) { - stmt, err := db.Prepare("UPDATE projects SET last = datetime('now') WHERE id = ?") - checkErr(err) - _, err = stmt.Exec(id) - checkErr(err) + stmt, err := db.Prepare("UPDATE projects SET last = datetime('now') WHERE id = ?") + checkErr(err) + _, err = stmt.Exec(id) + 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 + 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) - var nam,com string - for rows.Next() { - err = rows.Scan(&com, &nam) - checkErr(err) - } - cust = fmt.Sprintf("%s: %s",com,nam) - cust = fmt.Sprintf("%s",com) - return + 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) + cust = fmt.Sprintf("%s", com) + 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 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 checkCustomerProjects(in []int) (multicust,multiproj bool, projid []int) { - ins := strings.Trim(strings.Replace(fmt.Sprint(in)," "," , ",-1),"[]") - que := fmt.Sprintf("SELECT project FROM timetable WHERE id IN (%s) ORDER BY project DESC",ins) - rows,err := db.Query(que) - prid, cuid, altprid, altcuid := 0,0,0,0 - multicust, multiproj = false,false - - defer rows.Close() - for rows.Next() { - err = rows.Scan(&prid) - checkErr(err) - if altprid == 0{ - altprid = prid - projid = append(projid,prid) - }else{ - if altprid != prid { - multiproj = true - altprid = prid - projid = append(projid,prid) - } - } - - crows, err := db.Query("SELECT customer FROM projects WHERE id = ?",prid) - checkErr(err) - for crows.Next() { - err = crows.Scan(&cuid) - checkErr(err) - if altcuid == 0{ - altcuid = cuid - }else{ - if altcuid != cuid { - multicust = true - altcuid = cuid - } - } - } - } - return +func checkCustomerProjects(in []int) (multicust, multiproj bool, projid []int) { + ins := strings.Trim(strings.Replace(fmt.Sprint(in), " ", " , ", -1), "[]") + que := fmt.Sprintf("SELECT project FROM timetable WHERE id IN (%s) ORDER BY project DESC", ins) + rows, err := db.Query(que) + prid, cuid, altprid, altcuid := 0, 0, 0, 0 + multicust, multiproj = false, false + + defer rows.Close() + for rows.Next() { + err = rows.Scan(&prid) + checkErr(err) + if altprid == 0 { + altprid = prid + projid = append(projid, prid) + } else { + if altprid != prid { + multiproj = true + altprid = prid + projid = append(projid, prid) + } + } + + crows, err := db.Query("SELECT customer FROM projects WHERE id = ?", prid) + checkErr(err) + for crows.Next() { + err = crows.Scan(&cuid) + checkErr(err) + if altcuid == 0 { + altcuid = cuid + } else { + if altcuid != cuid { + multicust = true + altcuid = cuid + } + } + } + } + return } -func getProject(id int) (outpr project,outcu customer) { - rows, err := db.Query("SELECT * FROM projects WHERE id = ?",id) - checkErr(err) - var pid,customer,finished int - var first,last time.Time - var name,comm string - - for rows.Next() { - 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 - outpr.customer = customer - } - row, err := db.Query("SELECT * FROM customers WHERE id = ?",customer) - checkErr(err) - var cid int - var comp,cuname,addy string - var satz float64 - var lastb time.Time - - for row.Next() { - err = row.Scan(&cid, &comp, &cuname, &addy, &satz, &lastb) - checkErr(err) - outcu.id = cid - outcu.company = comp - outcu.name = cuname - outcu.address = addy - outcu.satz = satz - outcu.lastbill = lastb - } - return +func getProject(id int) (outpr project, outcu customer) { + rows, err := db.Query("SELECT * FROM projects WHERE id = ?", id) + checkErr(err) + var pid, customer, finished int + var first, last time.Time + var name, comm string + + for rows.Next() { + 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 + outpr.customer = customer + } + row, err := db.Query("SELECT * FROM customers WHERE id = ?", customer) + checkErr(err) + var cid int + var comp, cuname, addy string + var satz float64 + var lastb time.Time + + for row.Next() { + err = row.Scan(&cid, &comp, &cuname, &addy, &satz, &lastb) + checkErr(err) + outcu.id = cid + outcu.company = comp + outcu.name = cuname + outcu.address = addy + outcu.satz = satz + outcu.lastbill = lastb + } + return } func getSelectedTasks(in []int) (outtask []task) { - ins := strings.Trim(strings.Replace(fmt.Sprint(in)," "," , ",-1),"[]") - que := fmt.Sprintf("SELECT * FROM timetable WHERE id IN (%s)",ins) - rows,err := db.Query(que) - checkErr(err) + ins := strings.Trim(strings.Replace(fmt.Sprint(in), " ", " , ", -1), "[]") + que := fmt.Sprintf("SELECT * FROM timetable WHERE id IN (%s)", ins) + rows, err := db.Query(que) + checkErr(err) - var id,project,checkout int - var start,stop time.Time - var tsk,com string + var id, project, checkout int + var start, stop time.Time + var tsk, com string - defer rows.Close() - for rows.Next() { - err = rows.Scan(&id,&project,&start,&stop,&tsk,&com,&checkout) - outtask = append(outtask,task{id,project,start,stop,tsk,com,checkout}) - } - return + defer rows.Close() + for rows.Next() { + err = rows.Scan(&id, &project, &start, &stop, &tsk, &com, &checkout) + outtask = append(outtask, task{id, project, start, stop, tsk, com, checkout}) + } + return } //Return sum of Hours and Dateranges func analyzeTasks(in []task) (count int, hours float64, duration string) { - var lstart,hstop time.Time - for i,t := range in { - if i==0 { - lstart=t.start - hstop=t.stop - } - count++ - dur := float64(t.stop.Sub(t.start))/(1000000000*60*60) - hours=hours+dur - if lstart.After(t.start){ - lstart=t.start - } - if hstop.Before(t.stop){ - hstop=t.stop - } - //txt := fmt.Sprintf("%s - (%v) - %.2f h",task, durstr, dur) - } - duration = fmt.Sprintf("%v - %v",lstart.Local().Format("01.02."),hstop.Local().Format("01.02.2006")) - //duration = fmt.Sprintf("%v - %v",lstart.Local().Format("01.02.2006"),hstop.Local().Format("01.02.2006")) - return + var lstart, hstop time.Time + for i, t := range in { + if i == 0 { + lstart = t.start + hstop = t.stop + } + count++ + dur := float64(t.stop.Sub(t.start)) / (1000000000 * 60 * 60) + hours = hours + dur + if lstart.After(t.start) { + lstart = t.start + } + if hstop.Before(t.stop) { + hstop = t.stop + } + //txt := fmt.Sprintf("%s - (%v) - %.2f h",task, durstr, dur) + } + duration = fmt.Sprintf("%v - %v", lstart.Local().Format("01.02."), hstop.Local().Format("01.02.2006")) + //duration = fmt.Sprintf("%v - %v",lstart.Local().Format("01.02.2006"),hstop.Local().Format("01.02.2006")) + return } -func getProjectList(in []int) ([]int,[]string) { - var outids []int - var outstr []string +func getProjectList(in []int) ([]int, []string) { + var outids []int + var outstr []string - ins := strings.Trim(strings.Replace(fmt.Sprint(in)," "," , ",-1),"[]") - que := fmt.Sprintf("SELECT id, name FROM projects WHERE id IN (%s) ORDER BY last DESC",ins) - rows,err := db.Query(que) - checkErr(err) + ins := strings.Trim(strings.Replace(fmt.Sprint(in), " ", " , ", -1), "[]") + que := fmt.Sprintf("SELECT id, name FROM projects WHERE id IN (%s) ORDER BY last DESC", ins) + rows, err := db.Query(que) + checkErr(err) - var id int - var name string + var id int + var name string - defer rows.Close() - for rows.Next() { - err = rows.Scan(&id, &name) - checkErr(err) - outids = append(outids,id) - outstr = append(outstr,name) - } - return outids,outstr + defer rows.Close() + for rows.Next() { + err = rows.Scan(&id, &name) + checkErr(err) + outids = append(outids, id) + outstr = append(outstr, name) + } + return outids, outstr } -func getTaskList(in []int,showcust bool) ([]int, []string) { - var outids []int - var outstr []string - lastpr := 0 - pre := "" - prelen := 0 - ins := strings.Trim(strings.Replace(fmt.Sprint(in)," "," , ",-1),"[]") - que := fmt.Sprintf("SELECT id, project, start, stop, task FROM timetable WHERE id IN (%s) ORDER BY project DESC, stop DESC",ins) - rows,err := db.Query(que) - checkErr(err) - //rows,err := db.Query("SELECT id, project, start, stop, task FROM timetable WHERE id IN (?) ORDER BY project DESC, stop DESC",ins) - //rows,err := db.Query("SELECT id, project, start, stop, task FROM timetable WHERE stop != '1791-09-30 19:07' AND checkout = 0 AND id IN ? ORDER BY project DESC, stop DESC",in) - if (len(in)==1 && in[0]==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") - }//else{ - // rows,err = db.Query("SELECT id, project, start, stop, task FROM timetable WHERE stop != '1791-09-30 19:07' AND checkout = 0 AND id IN ? ORDER BY project DESC, stop DESC",in) - //} - 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,cu := getProjectName(project) - if showcust { - pre =fmt.Sprintf("%v| %v: ",cu,pr) - }else{ - 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 getProjectIds() ([]int) { - var ids []int - rows,err := db.Query("SELECT id FROM projects WHERE id != 0")// ORDER BY id DESC") - checkErr(err) - var id int +func getTaskList(in []int, showcust bool) ([]int, []string) { + var outids []int + var outstr []string + lastpr := 0 + pre := "" + prelen := 0 + ins := strings.Trim(strings.Replace(fmt.Sprint(in), " ", " , ", -1), "[]") + que := fmt.Sprintf("SELECT id, project, start, stop, task FROM timetable WHERE id IN (%s) ORDER BY project DESC, stop DESC", ins) + rows, err := db.Query(que) + checkErr(err) + //rows,err := db.Query("SELECT id, project, start, stop, task FROM timetable WHERE id IN (?) ORDER BY project DESC, stop DESC",ins) + //rows,err := db.Query("SELECT id, project, start, stop, task FROM timetable WHERE stop != '1791-09-30 19:07' AND checkout = 0 AND id IN ? ORDER BY project DESC, stop DESC",in) + if len(in) == 1 && in[0] == 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") + } //else{ + // rows,err = db.Query("SELECT id, project, start, stop, task FROM timetable WHERE stop != '1791-09-30 19:07' AND checkout = 0 AND id IN ? ORDER BY project DESC, stop DESC",in) + //} + 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, cu := getProjectName(project) + if showcust { + pre = fmt.Sprintf("%v| %v: ", cu, pr) + } else { + 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) - defer rows.Close() - for rows.Next() { - err = rows.Scan(&id) - checkErr(err) - ids = append(ids,id) - } - return ids + outids = append(outids, id) + outstr = append(outstr, fmt.Sprintf("%s%s", pre, txt)) + } + return outids, outstr } +func getProjectIds() []int { + var ids []int + rows, err := db.Query("SELECT id FROM projects WHERE id != 0") // ORDER BY id DESC") + checkErr(err) + var id int + defer rows.Close() + for rows.Next() { + err = rows.Scan(&id) + checkErr(err) + ids = append(ids, id) + } + return ids +} -func getTaskIds() ([]int) { - var ids []int - rows,err := db.Query("SELECT id FROM timetable WHERE stop != '1791-09-30 19:07'")// ORDER BY id DESC") - checkErr(err) - var id int +func getTaskIds() []int { + var ids []int + rows, err := db.Query("SELECT id FROM timetable WHERE stop != '1791-09-30 19:07'") // ORDER BY id DESC") + checkErr(err) + var id int - defer rows.Close() - for rows.Next() { - err = rows.Scan(&id) - checkErr(err) - ids = append(ids,id) - } - return ids + defer rows.Close() + for rows.Next() { + err = rows.Scan(&id) + checkErr(err) + ids = append(ids, id) + } + return ids } func getOpenTask() { - rows,err := db.Query("SELECT id, project, start, task, checkout FROM timetable WHERE stop = '1791-09-30 19:07'") - checkErr(err) - var id,project,checkout int - var task string - var start time.Time - - defer rows.Close() - for rows.Next() { - err = rows.Scan(&id, &project, &start , &task, &checkout) - checkErr(err) - //fmt.Println(id, "Open Task:", task, project, start) - } - //rows.Close() //good habit to close - opentask.id = id - opentask.projectid = project - opentask.start = start - opentask.taskname = task - opentask.checkout = checkout - //opentask.checkout = checkout != 0 + rows, err := db.Query("SELECT id, project, start, task, checkout FROM timetable WHERE stop = '1791-09-30 19:07'") + checkErr(err) + var id, project, checkout int + var task string + var start time.Time + + defer rows.Close() + for rows.Next() { + err = rows.Scan(&id, &project, &start, &task, &checkout) + checkErr(err) + //fmt.Println(id, "Open Task:", task, project, start) + } + //rows.Close() //good habit to close + opentask.id = id + opentask.projectid = project + opentask.start = start + opentask.taskname = task + opentask.checkout = checkout + //opentask.checkout = checkout != 0 } func showOpenTask() { - if (opentask.id==0) { - if pausetask > 0 { - fmt.Printf ("___Task %v Paused___________\n",pausetask) - }else{ - fmt.Println("___No Open Tasks____________") + if opentask.id == 0 { + if pausetask > 0 { + fmt.Printf("___Task %v Paused___________\n", pausetask) + } else { + fmt.Println("___No Open Tasks____________") + } + } else { + fmt.Println("___Open Task________________") + dur := float64(time.Now().Sub(opentask.start)) / (1000000000 * 60 * 60) + fmt.Printf(" %v: %v - (%v) - %.2f h\n", opentask.id, opentask.taskname, opentask.start.Local().Format("Mon Jan _2 2006 15:04"), dur) + //fmt.Println(opentask.id,":", opentask.taskname,"-", opentask.start.Local().Format("Mon Jan _2 2006 15:04"),dur,"h") } - } else { - fmt.Println("___Open Task________________") - dur := float64(time.Now().Sub(opentask.start))/(1000000000*60*60) - fmt.Printf(" %v: %v - (%v) - %.2f h\n",opentask.id, opentask.taskname, opentask.start.Local().Format("Mon Jan _2 2006 15:04"),dur) - //fmt.Println(opentask.id,":", opentask.taskname,"-", opentask.start.Local().Format("Mon Jan _2 2006 15:04"),dur,"h") - } } func showLastProject() { - fmt.Println("___Last Project_____________") - fmt.Println(currproject.id,":", currproject.name,"- Started:",currproject.first.Local().Format("Mon _2 Jan 2006")) - fmt.Println(" Last Changes",currproject.last.Local().Format("2006 Mon Jan _2 15:04")) + fmt.Println("___Last Project_____________") + fmt.Println(currproject.id, ":", currproject.name, "- Started:", currproject.first.Local().Format("Mon _2 Jan 2006")) + fmt.Println(" Last Changes", currproject.last.Local().Format("2006 Mon Jan _2 15:04")) } func addCustomer() { - boldRed := color.New(color.FgRed, color.Bold).SprintFunc() - boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() - fmt.Println(boldGreen("Adding new Customer")) - com := getInterInput("Enter Customer Company: ") - nam := getInterInput("Enter Customer Name: ") - add := getInterInput("Enter Address (separate lines by ; [Street;Zip;City;Country]): ") - sat := 0.0 - for { - satstr := getInterInput("Hourly Rate: ") - sat,err = strconv.ParseFloat(satstr,64) - //checkErr(err) - if err != nil { - fmt.Println(satstr,boldRed("can not be Parsed as a Float."), "Try a shape of X.X") - }else{break} - } + boldRed := color.New(color.FgRed, color.Bold).SprintFunc() + boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() + fmt.Println(boldGreen("Adding new Customer")) + com := getInterInput("Enter Customer Company: ") + nam := getInterInput("Enter Customer Name: ") + add := getInterInput("Enter Address (separate lines by ; [Street;Zip;City;Country]): ") + sat := 0.0 + for { + satstr := getInterInput("Hourly Rate: ") + sat, err = strconv.ParseFloat(satstr, 64) + //checkErr(err) + if err != nil { + fmt.Println(satstr, boldRed("can not be Parsed as a Float."), "Try a shape of X.X") + } else { + break + } + } - stmt, err := db.Prepare("INSERT INTO customers(company, name, address, satz) values(?, ?, ?, ?)") - checkErr(err) - _, err = stmt.Exec(com,nam,add,sat) - checkErr(err) - fmt.Println(boldGreen(" Customer Successfully Added:"),com ,nam, add, sat) + stmt, err := db.Prepare("INSERT INTO customers(company, name, address, satz) values(?, ?, ?, ?)") + checkErr(err) + _, err = stmt.Exec(com, nam, add, sat) + checkErr(err) + fmt.Println(boldGreen(" Customer Successfully Added:"), com, nam, add, sat) } func newProject() { - boldRed := color.New(color.FgRed, color.Bold).SprintFunc() - boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() - if (opentask.id > 0) { - fmt.Println(boldRed("There is an Open Task")) - showOpenTask() - return - } - if pausetask > 0 { - fmt.Println(boldRed("Task ",pausetask," pause status removed")) - setPauseTask(0) - } - fmt.Println(boldGreen("Creating new Project")) - nam := getInterInput("Enter Project Name: ") - icust := 0 - allCustomers() - for{ - cust := getInterInput("Enter Customer id: ") - icust,err = strconv.Atoi(cust) - if (err == nil && (isCustomer(icust) || icust == 0)) { - break - } else { - fmt.Println(cust,boldRed("is an invalid ID or Not a known Customer")) - } - } + boldRed := color.New(color.FgRed, color.Bold).SprintFunc() + boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() + if opentask.id > 0 { + fmt.Println(boldRed("There is an Open Task")) + showOpenTask() + return + } + if pausetask > 0 { + fmt.Println(boldRed("Task ", pausetask, " pause status removed")) + setPauseTask(0) + } + fmt.Println(boldGreen("Creating new Project")) + nam := getInterInput("Enter Project Name: ") + icust := 0 + allCustomers() + for { + cust := getInterInput("Enter Customer id: ") + icust, err = strconv.Atoi(cust) + if err == nil && (isCustomer(icust) || icust == 0) { + break + } else { + fmt.Println(cust, boldRed("is an invalid ID or Not a known Customer")) + } + } comm := "" - if isInterSure("Do you want to Comment the Project?"){ + 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,comm,0,icust) - checkErr(err) - fmt.Println(" Project Created:",nam) - getLastProject() + checkErr(err) + _, err = stmt.Exec(nam, comm, 0, icust) + checkErr(err) + fmt.Println(" Project Created:", nam) + getLastProject() } func getClosedTasks(num int) { - rows,err := db.Query("SELECT * FROM timetable WHERE stop != '1791-09-30 19:07' ORDER BY datetime(start)",currproject.id) - checkErr(err) - if num > 0 { - rows,err = db.Query("SELECT * FROM timetable WHERE project = $1 AND checkout > 0 AND stop != '1791-09-30 19:07'ORDER BY datetime(start) DESC LIMIT $2",currproject.id,num) - checkErr(err) - } else { - rows,err = db.Query("SELECT * FROM timetable WHERE project = $1 AND checkout > 0 AND stop != '1791-09-30 19:07'ORDER BY datetime(start)",currproject.id) - checkErr(err) - } - var id, proj,check int - var sta,sto time.Time - var tas,com string - var sum, dur float64 = 0.0, 0.0 - checkErr(err) - first := true - //if err != nil && err != sql.ErrNoRows { - // fmt.Println("___Billed Tasks_______________") - //} - for rows.Next() { - if first { - fmt.Println("___Billed Tasks_______________") - first = false - } - err = rows.Scan(&id, &proj, &sta, &sto, &tas, &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) - //fmt.Println(id,tas,sta.Local().Format("2006 Mon Jan _2 15:04"),sto.Local().Format("15:04"),dur,"h") - sum += dur - } - if !first{ - fmt.Println("____________________________") - fmt.Printf("Billed: %.2f h\n",sum) - } - rows.Close() + rows, err := db.Query("SELECT * FROM timetable WHERE stop != '1791-09-30 19:07' ORDER BY datetime(start)", currproject.id) + checkErr(err) + if num > 0 { + rows, err = db.Query("SELECT * FROM timetable WHERE project = $1 AND checkout > 0 AND stop != '1791-09-30 19:07'ORDER BY datetime(start) DESC LIMIT $2", currproject.id, num) + checkErr(err) + } else { + rows, err = db.Query("SELECT * FROM timetable WHERE project = $1 AND checkout > 0 AND stop != '1791-09-30 19:07'ORDER BY datetime(start)", currproject.id) + checkErr(err) + } + var id, proj, check int + var sta, sto time.Time + var tas, com string + var sum, dur float64 = 0.0, 0.0 + checkErr(err) + first := true + //if err != nil && err != sql.ErrNoRows { + // fmt.Println("___Billed Tasks_______________") + //} + for rows.Next() { + if first { + fmt.Println("___Billed Tasks_______________") + first = false + } + err = rows.Scan(&id, &proj, &sta, &sto, &tas, &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) + //fmt.Println(id,tas,sta.Local().Format("2006 Mon Jan _2 15:04"),sto.Local().Format("15:04"),dur,"h") + sum += dur + } + if !first { + fmt.Println("____________________________") + fmt.Printf("Billed: %.2f h\n", sum) + } + rows.Close() - if num > 0 { - rows,err = db.Query("SELECT * FROM timetable WHERE project = $1 AND checkout = 0 AND stop != '1791-09-30 19:07'ORDER BY datetime(start) DESC LIMIT $2",currproject.id,num) - checkErr(err) - } else { - rows,err = db.Query("SELECT * FROM timetable WHERE project = $1 AND checkout = 0 AND stop != '1791-09-30 19:07'ORDER BY datetime(start)",currproject.id) - checkErr(err) - } - sum2 := 0.0 - first = true - //if err != nil && err != sql.ErrNoRows{ - // fmt.Println("___Past Tasks_______________") - //} - for rows.Next() { - if first { - fmt.Println("___Past Tasks_______________") - first = false - } - 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) - //fmt.Println(id,tas,sta.Local().Format("2006 Mon Jan _2 15:04"),sto.Local().Format("15:04"),dur,"h") - sum2 += dur - } - //if err != nil && err != sql.ErrNoRows{ - if !first{ - fmt.Println("____________________________") - fmt.Printf("Unbilled: %.2f[h] Total: %.2f[h]\n",sum2,sum+sum2) - } - rows.Close() + if num > 0 { + rows, err = db.Query("SELECT * FROM timetable WHERE project = $1 AND checkout = 0 AND stop != '1791-09-30 19:07'ORDER BY datetime(start) DESC LIMIT $2", currproject.id, num) + checkErr(err) + } else { + rows, err = db.Query("SELECT * FROM timetable WHERE project = $1 AND checkout = 0 AND stop != '1791-09-30 19:07'ORDER BY datetime(start)", currproject.id) + checkErr(err) + } + sum2 := 0.0 + first = true + //if err != nil && err != sql.ErrNoRows{ + // fmt.Println("___Past Tasks_______________") + //} + for rows.Next() { + if first { + fmt.Println("___Past Tasks_______________") + first = false + } + 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) + //fmt.Println(id,tas,sta.Local().Format("2006 Mon Jan _2 15:04"),sto.Local().Format("15:04"),dur,"h") + sum2 += dur + } + //if err != nil && err != sql.ErrNoRows{ + if !first { + fmt.Println("____________________________") + fmt.Printf("Unbilled: %.2f[h] Total: %.2f[h]\n", sum2, sum+sum2) + } + rows.Close() } func getLastProject() { - rows,err := db.Query("SELECT * FROM projects") - checkErr(err) + rows, err := db.Query("SELECT * FROM projects") + checkErr(err) - var uid,nuid int - 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,&prcom, &first, &last, &finish, &custom) - checkErr(err) - if (last.After(nlast)){ - nuid=uid - nprname=prname - nprcom=prcom - nfirst=first - nlast=last - nfinish=finish - ncustom=custom - } - } - rows.Close() //good habit to close - - currproject.id = nuid - currproject.name = nprname - currproject.comment = nprcom - currproject.first = nfirst - currproject.last = nlast - currproject.finished = nfinish - //currproject.finish = nfinish != 0 - currproject.customer = ncustom + var uid, nuid int + 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, &prcom, &first, &last, &finish, &custom) + checkErr(err) + if last.After(nlast) { + nuid = uid + nprname = prname + nprcom = prcom + nfirst = first + nlast = last + nfinish = finish + ncustom = custom + } + } + rows.Close() //good habit to close + + currproject.id = nuid + currproject.name = nprname + currproject.comment = nprcom + currproject.first = nfirst + currproject.last = nlast + currproject.finished = nfinish + //currproject.finish = nfinish != 0 + currproject.customer = ncustom } -func setProject (nid int) { - boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() - boldRed := color.New(color.FgRed, color.Bold).SprintFunc() - if (opentask.id > 0) { - fmt.Println(boldRed("There is an Open Task")) - showOpenTask() - return - } - if pausetask > 0 { - fmt.Println(boldRed("Task ",pausetask," pause status removed")) - setPauseTask(0) - } - if isProject(nid){ - fmt.Println(boldGreen("Opening Project ",nid)) - }else{ - fmt.Println(boldRed("There is no Project"),nid) - return - } +func setProject(nid int) { + boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() + boldRed := color.New(color.FgRed, color.Bold).SprintFunc() + if opentask.id > 0 { + fmt.Println(boldRed("There is an Open Task")) + showOpenTask() + return + } + if pausetask > 0 { + fmt.Println(boldRed("Task ", pausetask, " pause status removed")) + setPauseTask(0) + } + if isProject(nid) { + fmt.Println(boldGreen("Opening Project ", nid)) + } else { + fmt.Println(boldRed("There is no Project"), nid) + return + } + + rows, err := db.Query("SELECT * FROM projects WHERE id = $1", nid) + checkErr(err) + + var uid int + var prname, comm string + var first time.Time + var last time.Time + var finish int + var custo int - rows,err := db.Query("SELECT * FROM projects WHERE id = $1",nid) - checkErr(err) - - var uid int - 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, &comm, &first, &last, &finish, &custo) - checkErr(err) - } - rows.Close() //good habit to close - currproject.id = uid - currproject.name = prname + for rows.Next() { + 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 - //currproject.finish = finish != 0 - currproject.customer = custo - updateProject(uid) + currproject.first = first + currproject.last = last + currproject.finished = finish + //currproject.finish = finish != 0 + currproject.customer = custo + updateProject(uid) } func getCustomerList() (outint []int, outstr []string) { - rows,err := db.Query("SELECT id, company, name FROM customers") - checkErr(err) - var id int - var comp string - var name string - for rows.Next() { - err = rows.Scan(&id, &comp, &name) - checkErr(err) - st := fmt.Sprintf("%s: %s",comp,name) - outint = append(outint,id) - outstr = append(outstr, st) - } - return + rows, err := db.Query("SELECT id, company, name FROM customers") + checkErr(err) + var id int + var comp string + var name string + for rows.Next() { + err = rows.Scan(&id, &comp, &name) + checkErr(err) + st := fmt.Sprintf("%s: %s", comp, name) + outint = append(outint, id) + outstr = append(outstr, st) + } + return } 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 - var last time.Time - - fmt.Println("___All Customers________________") - for rows.Next() { - err = rows.Scan(&uid, &comp, &name, &addr, &satz, &last) - checkErr(err) - 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) - } + 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 + var last time.Time + + fmt.Println("___All Customers________________") + for rows.Next() { + err = rows.Scan(&uid, &comp, &name, &addr, &satz, &last) + checkErr(err) + 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) + } } func allProjects() { - boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() - fmt.Println(boldGreen("Loading all customers")) - fmt.Println("___All Projects________________") - rows3,err := db.Query("SELECT * FROM customers") - checkErr(err) + boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() + fmt.Println(boldGreen("Loading all customers")) + fmt.Println("___All Projects________________") + rows3, err := db.Query("SELECT * FROM customers") + 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, &com, &nam, &adr, &sat, &lst) + 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, &com, &nam, &adr, &sat, &lst) - checkErr(err) - - rows,err := db.Query("SELECT * FROM projects WHERE customer = $1",cid) - checkErr(err) - - var uid int - var prname,comm string - var first time.Time - var last time.Time - var finish int - var customer,check int - var start,stop time.Time - - fmt.Println("____For",com,nam) - for rows.Next() { - 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) - sumb,sumo := 0.0,0.0 - for rows2.Next() { - err = rows2.Scan(&start,&stop,&check) - checkErr(err) - if check == 0 { - sumo += float64(stop.Sub(start))/(1000000000*60*60) - }else{ - sumb += float64(stop.Sub(start))/(1000000000*60*60) - } - } - - //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) - 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, \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") - } - rows2.Close() //good habit to close - } - rows.Close() //good habit to close - } - rows3.Close() //good habit to close - fmt.Println("_______________________________\n") + rows, err := db.Query("SELECT * FROM projects WHERE customer = $1", cid) + checkErr(err) + + var uid int + var prname, comm string + var first time.Time + var last time.Time + var finish int + var customer, check int + var start, stop time.Time + + fmt.Println("____For", com, nam) + for rows.Next() { + 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) + sumb, sumo := 0.0, 0.0 + for rows2.Next() { + err = rows2.Scan(&start, &stop, &check) + checkErr(err) + if check == 0 { + sumo += float64(stop.Sub(start)) / (1000000000 * 60 * 60) + } else { + sumb += float64(stop.Sub(start)) / (1000000000 * 60 * 60) + } + } + + //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) + 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, \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") + } + rows2.Close() //good habit to close + } + rows.Close() //good habit to close + } + rows3.Close() //good habit to close + fmt.Println("_______________________________\n") } func deleteBill(id int) { - boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() - boldRed := color.New(color.FgRed, color.Bold).SprintFunc() - fmt.Println(boldGreen("Deleting Bill ",id)) - var prj int - var identity,moneys,hours string - var date time.Time - - rows,err := db.Query("SELECT project, date, identity, hours, moneys FROM bills WHERE id = $1",id) - checkErr(err) - if rows.Next() { - err = rows.Scan(&prj, &date, &identity, &hours, &moneys) - checkErr(err) - rows.Close() //good habit to close - prstr,custr := getProjectName(prj) - hsum := sumFloatArray(string2FloatArray(hours,";")) - msum := sumFloatArray(string2FloatArray(moneys,";")) - fmt.Printf("%v: For %v- %v (%v) - %.1f[h] : %.2f[€]\n",identity,custr,prstr,date.Local().Format("2006 Mon Jan _2"),hsum,msum) - if isInterSure("Are You Sure?") { - uncheckTasks(id) //Set corresponding Tasks to checkout=0 - stmt, err := db.Prepare("DELETE FROM bills WHERE id = ?") - checkErr(err) - _, err = stmt.Exec(id) - checkErr(err) - fmt.Println(boldGreen("Bill ",id," deleted successfully!")) - } else { - return - } - }else{ - fmt.Println(boldRed(id," is Not a known Bill!")) - showLastBills(0) - } + boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() + boldRed := color.New(color.FgRed, color.Bold).SprintFunc() + fmt.Println(boldGreen("Deleting Bill ", id)) + var prj int + var identity, moneys, hours string + var date time.Time + + rows, err := db.Query("SELECT project, date, identity, hours, moneys FROM bills WHERE id = $1", id) + checkErr(err) + if rows.Next() { + err = rows.Scan(&prj, &date, &identity, &hours, &moneys) + checkErr(err) + rows.Close() //good habit to close + prstr, custr := getProjectName(prj) + hsum := sumFloatArray(string2FloatArray(hours, ";")) + msum := sumFloatArray(string2FloatArray(moneys, ";")) + fmt.Printf("%v: For %v- %v (%v) - %.1f[h] : %.2f[€]\n", identity, custr, prstr, date.Local().Format("2006 Mon Jan _2"), hsum, msum) + if isInterSure("Are You Sure?") { + uncheckTasks(id) //Set corresponding Tasks to checkout=0 + stmt, err := db.Prepare("DELETE FROM bills WHERE id = ?") + checkErr(err) + _, err = stmt.Exec(id) + checkErr(err) + fmt.Println(boldGreen("Bill ", id, " deleted successfully!")) + } else { + return + } + } else { + fmt.Println(boldRed(id, " is Not a known Bill!")) + showLastBills(0) + } } func deleteTask(id int) { - boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() - boldRed := color.New(color.FgRed, color.Bold).SprintFunc() - fmt.Println(boldGreen("Deleting Task ",id)) - var chk,prj int - var start,stop time.Time - 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, &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 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) - _, err = stmt.Exec(id) - checkErr(err) - fmt.Println(boldGreen("Task ",id," deleted successfully!")) - } else { - return - } - }else{ - fmt.Println(boldRed(id," is Not a known Task!")) - } + boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() + boldRed := color.New(color.FgRed, color.Bold).SprintFunc() + fmt.Println(boldGreen("Deleting Task ", id)) + var chk, prj int + var start, stop time.Time + 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, &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 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) + _, err = stmt.Exec(id) + checkErr(err) + fmt.Println(boldGreen("Task ", id, " deleted successfully!")) + } else { + return + } + } else { + fmt.Println(boldRed(id, " is Not a known Task!")) + } } func editCustomer(id int) { - boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() - boldRed := color.New(color.FgRed, color.Bold).SprintFunc() - fmt.Println(boldGreen("Editing Customer ",id)) - 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 := getInterInput("Enter New:") - if in!=""{ - comp=in - }*/ - comp = getNewInterInput("New Company Name: ",comp) - /*fmt.Println("Old Name:",name) - in = getInterInput("Enter New:") - if in!=""{ - name=in - }*/ - name = getNewInterInput("New Customer Name: ",name) - /*fmt.Println("Old Adress:",addr) - in = getInterInput("Enter New:") - if in!=""{ - addr=in - }*/ - addr = getNewInterInput("New Adress: ",addr) - //fmt.Println("Old Hourly Rate:",satz) - for{ - satzstr := getNewInterInput("New Hourly Rate: ",fmt.Sprintf("%.2f",satz)) - 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} - /*satzstr := getInterInput("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") + boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() + boldRed := color.New(color.FgRed, color.Bold).SprintFunc() + fmt.Println(boldGreen("Editing Customer ", id)) + 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 := getInterInput("Enter New:") + if in!=""{ + comp=in + }*/ + comp = getNewInterInput("New Company Name: ", comp) + /*fmt.Println("Old Name:",name) + in = getInterInput("Enter New:") + if in!=""{ + name=in + }*/ + name = getNewInterInput("New Customer Name: ", name) + /*fmt.Println("Old Adress:",addr) + in = getInterInput("Enter New:") + if in!=""{ + addr=in + }*/ + addr = getNewInterInput("New Adress: ", addr) + //fmt.Println("Old Hourly Rate:",satz) + for { + satzstr := getNewInterInput("New Hourly Rate: ", fmt.Sprintf("%.2f", satz)) + 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 + } + /*satzstr := getInterInput("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() - fmt.Println(boldGreen("Edit Task ",id)) - var chk,prj int - var start,stop time.Time - 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, &comm, &chk) - checkErr(err) - }else{ - fmt.Println(boldRed("There Is No Task"),id) - return - //os.Exit(0) - } - rows.Close() //good habit to close - -/* 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{ - 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 + boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() + boldRed := color.New(color.FgRed, color.Bold).SprintFunc() + fmt.Println(boldGreen("Edit Task ", id)) + var chk, prj int + var start, stop time.Time + 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, &comm, &chk) + checkErr(err) + } else { + fmt.Println(boldRed("There Is No Task"), id) + return + //os.Exit(0) } - } - //fmt.Println("Old End:",stopstr) - for{ - 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'") - } - } - //fmt.Println("Old Project:",prj) - for{ - 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") - } - if !isProject(prj) { - fmt.Println(boldRed("There is no project"),prj) - }else{break} - } - 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,comm,startstr,stopstr,prj,id) - checkErr(err) - updateProject(prj) - fmt.Println("...Task",id,"Updated") -} - -func editProject(id int) { - boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() - boldRed := color.New(color.FgRed, color.Bold).SprintFunc() - fmt.Println(boldGreen("Edit Project ",id)) - var fin,cust int - var first time.Time - 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, &comm, &first, &fin, &cust) - checkErr(err) - - name = getNewInterInput("New Name: ",name) - nfirst=first.Local().Format("2006-01-02 15:04") - // 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 + rows.Close() //good habit to close + + /* 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 { + 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 } } - // Get New Customer - for{ - newcu := getNewInterInput("New Customer id: ",fmt.Sprint(cust)) - icust,err := strconv.Atoi(newcu) + //fmt.Println("Old End:",stopstr) + for { + 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'") + } + } + //fmt.Println("Old Project:",prj) + for { + newprj := getNewInterInput("New Project id: ", fmt.Sprint(prj)) + prj, err = strconv.Atoi(newprj) 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 + fmt.Println(newprj, boldRed("is Not a Valid id."), "Try an Integer instead") + } + if !isProject(prj) { + fmt.Println(boldRed("There is no project"), prj) + } else { 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 = ?, comment = ?, last = datetime(?,'utc'), customer = ? WHERE id = ?") - checkErr(err) - _, err = stmt.Exec(name,comm,nfirst,cust,id) - checkErr(err) - updateProject(id) - fmt.Println("...Project",id,"Updated") + 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, comm, startstr, stopstr, prj, id) + checkErr(err) + updateProject(prj) + fmt.Println("...Task", id, "Updated") +} + +func editProject(id int) { + boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc() + boldRed := color.New(color.FgRed, color.Bold).SprintFunc() + fmt.Println(boldGreen("Edit Project ", id)) + var fin, cust int + var first time.Time + 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, &comm, &first, &fin, &cust) + checkErr(err) + + name = getNewInterInput("New Name: ", name) + nfirst = first.Local().Format("2006-01-02 15:04") + // 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 = ?, comment = ?, last = datetime(?,'utc'), customer = ? WHERE id = ?") + checkErr(err) + _, err = stmt.Exec(name, comm, nfirst, cust, id) + checkErr(err) + updateProject(id) + fmt.Println("...Project", id, "Updated") } func isBill(id int) bool { - rows,err := db.Query("SELECT * FROM bills WHERE id = $1",id) - checkErr(err) - defer rows.Close() - if rows.Next() { - return true - } else { - return false - } + rows, err := db.Query("SELECT * FROM bills WHERE id = $1", id) + checkErr(err) + defer rows.Close() + if rows.Next() { + return true + } else { + return false + } } func isTask(id int) bool { - rows,err := db.Query("SELECT * FROM timetable WHERE id = $1",id) - checkErr(err) - defer rows.Close() - if rows.Next() { - return true - } else { - return false - } + rows, err := db.Query("SELECT * FROM timetable WHERE id = $1", id) + checkErr(err) + defer rows.Close() + if rows.Next() { + return true + } else { + return false + } } func isProject(id int) bool { - rows,err := db.Query("SELECT * FROM projects WHERE id = $1",id) - checkErr(err) - defer rows.Close() - if rows.Next() { - return true - } else { - return false - } + rows, err := db.Query("SELECT * FROM projects WHERE id = $1", id) + checkErr(err) + defer rows.Close() + if rows.Next() { + return true + } else { + return false + } } func isCustomer(id int) bool { - rows,err := db.Query("SELECT * FROM customers WHERE id = $1",id) - checkErr(err) - defer rows.Close() - if rows.Next() { - return true - } else { - return false - } + rows, err := db.Query("SELECT * FROM customers WHERE id = $1", id) + checkErr(err) + defer rows.Close() + if rows.Next() { + return true + } else { + return false + } } func getDateTime(in string) string { - r := regexp.MustCompile(`(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2})`) - return r.FindString(in) + r := regexp.MustCompile(`(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2})`) + return r.FindString(in) } func getDate(in string) string { - r := regexp.MustCompile(`(\d{4})-(\d{2})-(\d{2})`) - return r.FindString(in) + r := regexp.MustCompile(`(\d{4})-(\d{2})-(\d{2})`) + return r.FindString(in) } func getTime(in string) string { - r := regexp.MustCompile(`(\d{2}):(\d{2})`) - return r.FindString(in) + r := regexp.MustCompile(`(\d{2}):(\d{2})`) + return r.FindString(in) } func isDateTime(in string) bool { - match, _ := regexp.MatchString(`(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2})`,in) - return match + match, _ := regexp.MatchString(`(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2})`, in) + return match } func isDate(in string) bool { - match, _ := regexp.MatchString(`(\d{4})-(\d{2})-(\d{2})`,in) - return match + match, _ := regexp.MatchString(`(\d{4})-(\d{2})-(\d{2})`, in) + return match } func isTime(in string) bool { - match, _ := regexp.MatchString(`(\d{2}):(\d{2})`,in) - return match + match, _ := regexp.MatchString(`(\d{2}):(\d{2})`, in) + return match } /* @@ -1490,23 +1499,23 @@ func isSure() bool { } }*/ -func isElement(some int, group []int) (bool) { - for _,e := range group { - if e == some { - return true - } - } - return false +func isElement(some int, group []int) bool { + for _, e := range group { + if e == some { + return true + } + } + return false } -func removeItems(master, rem []int) ([]int) { - var out []int - for _,v := range master { - if !isElement(v,rem) { - out = append(out,v) - } - } - return out +func removeItems(master, rem []int) []int { + var out []int + for _, v := range master { + if !isElement(v, rem) { + out = append(out, v) + } + } + return out } /* |