path: root/sqlite.go

package main

import (
    "database/sql"
    "fmt"
    "time"
    "os"
    "bufio"
    "strings"
    "regexp"
    "strconv"

    _ "github.com/mattn/go-sqlite3"
    "github.com/fatih/color"
)

type project struct{
    id int
    name string
    first time.Time
    last time.Time
    finish bool
    customer int
}

type task struct{
    id int
    projectid int
    start time.Time
    stop time.Time
    taskname string
    checkout bool
}

var db *sql.DB
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)
        checkErr(err)
        fmt.Println("Creating new DB",filename)
        sqlstmt := `
        CREATE TABLE projects(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name VARCHAR(240) NOT NULL,
            first TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            last TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            finished INTEGER NOT NULL,
            customer INTEGER DEFAULT NULL);
        CREATE TABLE timetable(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            project INTEGER NOT NULL,
            start TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            stop TIMESTAMP DEFAULT '1791-09-30 19:07',
            task VARCHAR(240) NOT NULL,
            checkout INTEGER NOT NULL);
        CREATE TABLE customers(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name VARCHAR(240),
            address VARCHAR(240) DEFAULT 'None',
            satz REAL DEFAULT 1,
            lastbill TIMESTAMP DEFAULT '1791-09-30 19:07');
        CREATE TABLE bills(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            identity VARCHAR(240),
            project INTEGER NOT NULL,
            tasks VARCHAR(240),
            text VARCHAR(240),
            date TIMESTAMP DEFAULT '1791-09-30 19:07');
        `
        _, err = db.Exec(sqlstmt)
        checkErr(err)
        stmt, err := db.Prepare("INSERT INTO customers(id, name) values(?, ?)")
        checkErr(err)
        _, err = stmt.Exec(0,"Annonymus")
        checkErr(err)
    }else{
        db, err = sql.Open("sqlite3", filename)
        checkErr(err)
        fmt.Println("Opening DB",filename)
    }
}

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"
        if isDate(tim) {
            timstr = getDate(tim)
        }else if isTime(tim) {
            currdate := time.Now().UTC().Format("2006-01-02")
            timstr = currdate+" "+getTime(tim)
        }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 := getInput("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(proj int) {
    boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
    boldRed := color.New(color.FgRed, color.Bold).SprintFunc()
    fmt.Println(boldGreen("Starting new Task"))
    if (opentask.id == 0) {
        task := getInput("Specify Task: ")
        stmt, err := db.Prepare("INSERT INTO timetable(project, task, checkout) values(?, ?, ?)")
        checkErr(err)
        if (proj == 0) {
            _, err = stmt.Exec(currproject.id,task,0)
        }else{
            _, err = stmt.Exec(proj,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 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 isDate(tim) {
        timstr = getDate(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)
    
    //fmt.Println(timst,timt,opentask.start)
    if (timt.After(opentask.start)) {
        //timstr := timt.UTC().Format("2006-01-02 15:04")
        fmt.Println("...Closing Task",opentask.id,"at",timst)
        stmt, err := db.Prepare("UPDATE timetable SET stop = datetime(?,'utc') WHERE id = ?")
        checkErr(err)
        _, err = stmt.Exec(timstr,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() {
    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
    }
    if (time.Now().After(opentask.start.Local())) {
        fmt.Println("...Closing Task",opentask.id)
        stmt, err := db.Prepare("UPDATE timetable SET stop = datetime('now') WHERE id = ?")
        checkErr(err)
        _, err = stmt.Exec(opentask.id)
        checkErr(err)
        opentask.id=0
        updateProject(opentask.projectid)
    }else{
        fmt.Println(boldRed("Cannot Stop before the Beginning!"))
    }
}

func updateProject(id int) {
    stmt, err := db.Prepare("UPDATE projects SET last = datetime('now') WHERE id = ?")
    checkErr(err)
    _, err = stmt.Exec(id)
    checkErr(err)
}

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 != 0
}

func showOpenTask() {
    if (opentask.id==0) {
        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")
    }
}

func showLastProject() {
    if (currproject.id==0) {
        fmt.Println("Empty DB")
    } else {
        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() {
    nam := getInput("Enter Customer Name: ")
    add := getInput("Enter Address (separate lines by ;): ")
    satstr := getInput("Hourly Rate: ")
    sat,err := strconv.ParseFloat(satstr,64)
    checkErr(err)

    stmt, err := db.Prepare("INSERT INTO customers(name, address, satz) values(?, ?, ?)")
    checkErr(err)
    _, err = stmt.Exec(nam,add,sat)
    checkErr(err)
    fmt.Println("   Customer Added:",nam, add, sat)
}

func newProject() {
    boldRed := color.New(color.FgRed, color.Bold).SprintFunc()
    if (opentask.id == 0) {
        nam := getInput("Enter Project Name: ")
        icust := 0
        allCustomers()
        for{
            cust := getInput("Enter Customer id (0 for none): ")
            icust,err = strconv.Atoi(cust)
            if (err == nil && (isCustomer(icust) || icust == 0)) {
                break
            } else {
                fmt.Println(cust,boldRed("is an invalid ID or Not a known Customer"))
            }
        }
        stmt, err := db.Prepare("INSERT INTO projects(name, finished,customer) values(?, ?, ?)")
        checkErr(err)
        _, err = stmt.Exec(nam,0,icust)
        checkErr(err)
        fmt.Println("   Project Created:",nam)
        getLastProject()
    } else {
        fmt.Println(boldRed("There is an Open Task"))
        showOpenTask()
    }
}

func getClosedTasks(num int) {
    fmt.Println("___Past Tasks_______________")
    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 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 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 string
    var sum, dur float64 = 0.0, 0.0
    checkErr(err)

    for rows.Next() {
        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
    }
    rows.Close()
    fmt.Println("____________________________")
    fmt.Printf("Gesamt: %.2f h\n",sum)
}

func getLastProject() {
    rows,err := db.Query("SELECT * FROM projects")
    checkErr(err)

    var uid,nuid int
    var prname,nprname 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, &first, &last, &finish, &custom)
        checkErr(err)
        if (last.After(nlast)){
            nuid=uid
            nprname=prname
            nfirst=first
            nlast=last
            nfinish=finish 
            ncustom=custom
        }
    }
    rows.Close() //good habit to close

    currproject.id = nuid
    currproject.name = nprname
    currproject.first = nfirst
    currproject.last = nlast
    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()
    fmt.Println(boldGreen("Opening Project ",nid))
    if !isProject(nid){
        fmt.Println(boldRed("There is no Project"),nid)
        return
    }
    if (opentask.id == 0) {
        rows,err := db.Query("SELECT * FROM projects WHERE id = $1",nid)
        checkErr(err)

        var uid int
        var prname string
        var first time.Time
        var last time.Time
        var finish int
        var custo int

        for rows.Next() {
            err = rows.Scan(&uid, &prname, &first, &last, &finish, &custo)
            checkErr(err)
        }
        rows.Close() //good habit to close
        currproject.id = uid
        currproject.name = prname
        currproject.first = first
        currproject.last = last
        currproject.finish = finish != 0
        currproject.customer = custo
        updateProject(uid)
    } else {
        fmt.Println(boldRed("There is an Open Task"))
        showOpenTask()
    }
}

func allCustomers() {
    rows,err := db.Query("SELECT * FROM customers")
    checkErr(err)

    var uid int
    var name string
    var addr string
    var satz float64
    var last time.Time

    fmt.Println("___All Customers________________")
    for rows.Next() {
        err = rows.Scan(&uid, &name, &addr, &satz, &last)
        checkErr(err)
        fmt.Printf(" %v:%s, Rate:%.2f(€/h) , Last:%s\n",uid,name,satz,last.Local().Format("2006-01-02 15:04 MST"))
    }
}

func allProjects() {
    fmt.Println("___All Projects________________")
    rows3,err := db.Query("SELECT * FROM customers")
    checkErr(err)

    var cid int
    var nam string
    var adr string
    var sat float64
    var lst time.Time
    for rows3.Next() {
        err = rows3.Scan(&cid, &nam, &adr, &sat, &lst)
        checkErr(err)

        rows,err := db.Query("SELECT * FROM projects WHERE customer = $1",cid)
        checkErr(err)

        var uid int
        var prname string
        var first time.Time
        var last time.Time
        var finish int
        var customer int
        var start,stop time.Time

        fmt.Println("____For",nam)
        for rows.Next() {
            err = rows.Scan(&uid, &prname, &first, &last, &finish, &customer)
            checkErr(err)
            rows2,err := db.Query("SELECT start, stop FROM timetable WHERE project = $1 AND checkout = 0 AND stop != '1791-09-30 19:07'",uid)
            checkErr(err)
            sum := 0.0
            for rows2.Next() {
                err = rows2.Scan(&start,&stop)
                checkErr(err)
                sum += float64(stop.Sub(start))/(1000000000*60*60)
            }

            fmt.Printf(" %v:%s \n  First: %s, Last:%s, Total:%.2f(h) ,Fin:%v, For:%v\n",uid,prname,first.Local().Format("2006-01-02 15:04 MST"),last.Local().Format("2006-01-02 15:04 MST"),sum,finish,customer)
            rows2.Close() //good habit to close
        }
        rows.Close() //good habit to close
    }
    rows3.Close() //good habit to close
    fmt.Println("_______________________________\n")
}

func deleteTask(id int) {
    boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
    boldRed := color.New(color.FgRed, color.Bold).SprintFunc()
    var chk,prj int
    var start,stop time.Time
    var task string
    rows,err := db.Query("SELECT project, start, stop, task, checkout FROM timetable WHERE id = $1",id)
    checkErr(err)
    if rows.Next() {
        err = rows.Scan(&prj, &start, &stop, &task, &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",prj,task,start.Local().Format("2006 Mon Jan _2 15:04"),stop.Local().Format("15:04"),dur)
        if isSure() {
            stmt, err := db.Prepare("DELETE FROM timetable WHERE id = ?")
            checkErr(err)
            _, err = stmt.Exec(id)
            checkErr(err)
            fmt.Println(boldGreen(id," deleted successfully!"))
        } else {
            return
        }
    }else{
        fmt.Println(boldRed(id," is Not a known Task!"))
    }
}

func editTask(id int) {
    boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
    boldRed := color.New(color.FgRed, color.Bold).SprintFunc()
    var chk,prj int
    var start,stop time.Time
    var task,startstr,stopstr string
    rows,err := db.Query("SELECT project, start, stop, task, checkout FROM timetable WHERE id = $1",id)
    checkErr(err)
    if rows.Next() {
        err = rows.Scan(&prj, &start, &stop, &task, &chk)
        checkErr(err)
    }else{
        fmt.Println(boldRed("There Is No Task"),id)
        return
        //os.Exit(0)
    }
    rows.Close() //good habit to close

    fmt.Println(boldGreen("Edit Task ",id))
    fmt.Println("Old Name:",task)
    in := getInput("Enter New:")
    if in!=""{
        task=in
    }
    startstr=start.Local().Format("2006-01-02 15:04")
    stopstr=stop.Local().Format("2006-01-02 15:04")
    for{
        fmt.Println("Old Start:",startstr)
        newstart := getInput("Enter New:")
        if newstart!=""{
            if isDate(newstart) {
                startstr=newstart
                break
            }else{
                fmt.Println(newstart,boldRed("is Not a Valid Timestring!"), "use: 'YYYY-MM-DD HH:MM' or 'HH:MM'")
                //os.Exit(0)
            }
        }else{break}
    }
    fmt.Println("Old End:",stopstr)
    for{
        newend := getInput("Enter New:")
        if newend!=""{
            if isDate(newend) {
                stopstr=newend
                break
            }else{
                fmt.Println(newend,boldRed("is Not a Valid Timestring!"), "use: 'YYYY-MM-DD HH:MM' or 'HH:MM'")
                //os.Exit(0)
            }
        }else{break}
    }
    fmt.Println("Old Project:",prj)
    for{
        newprj := getInput("Enter New:")
        if newprj!=""{
            prj,err = strconv.Atoi(newprj)
            if err != nil {
                fmt.Println(newprj,boldRed("is Not a Valid id."), "Try an Integer instead")
                //os.Exit(0)
            }
            if !isProject(prj) {
                fmt.Println(boldRed("There is no project"),prj,"Try -all for Project List")
                //os.Exit(0)
            }else{break}
        }else{break}
    }
    stmt, err := db.Prepare("UPDATE timetable SET task = ?, start = datetime(?,'utc'), stop = datetime(?,'utc'), project = ? WHERE id = ?")
    checkErr(err)
    _, err = stmt.Exec(task,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()
    var fin,cust int
    var first time.Time
    var name,nfirst string
    rows,err := db.Query("SELECT name, first, finished, customer FROM projects WHERE id = $1",id)
    checkErr(err)
    if rows.Next() {
        err = rows.Scan(&name, &first, &fin, &cust)
        checkErr(err)
        fmt.Println(boldGreen("Edit Project ",id))
        fmt.Println("Old Name:",name)
        in := getInput("Enter New:")
        if in!=""{
            name=in
        }
        nfirst=first.Local().Format("2006-01-02 15:04")
        fmt.Println("Old Begin:",nfirst)
        for{
            newfirst := getInput("Enter New:")
            if newfirst!=""{
                if isDate(newfirst) {
                    nfirst=newfirst
                    break
                }else{
                    fmt.Println(newfirst,boldRed("is Not a Valid Timestring!"), "use: 'YYYY-MM-DD HH:MM' or 'HH:MM'")
                    //os.Exit(0)
                }
            }else{break}
        }
        fmt.Println("Old Customer:",cust)
        for{
            custs := getInput("Enter Customer id (0 for none): ")
            icust,err := strconv.Atoi(custs)
            if (err == nil && (isCustomer(icust) || icust == 0)) {
                cust = icust
                break
            } else {
                fmt.Println(custs,boldRed("is an invalid ID or Not a known Customer"))
            }
        }
    }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 = ?, last = datetime(?,'utc'), customer = ? WHERE id = ?")
    checkErr(err)
    _, err = stmt.Exec(name,nfirst,cust,id)
    checkErr(err)
    updateProject(id)
    fmt.Println("...Project",id,"Updated")
}

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
    }
}

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
    }
}

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
    }
}

func getDate(in string) string {
    r := regexp.MustCompile(`(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2})`)
    return r.FindString(in)
}

func getTime(in string) string {
    r := regexp.MustCompile(`(\d{2}):(\d{2})`)
    return r.FindString(in)
}

func isDate(in string) bool {
    match, _ := regexp.MatchString(`(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2})`,in)
    return match
}

func isTime(in string) bool {
    match, _ := regexp.MatchString(`(\d{2}):(\d{2})`,in)
    return match
}

func getInput(quest string) string {
    fmt.Print(quest)
    in := bufio.NewReader(os.Stdin)
    line, err := in.ReadString('\n')
    line = strings.TrimSuffix(line, "\n")
    checkErr(err)
    return line
}

func isSure() bool {
    fmt.Print("Are You Sure ? (type 'yes' to confirm) : ")
    in := bufio.NewReader(os.Stdin)
    line, err := in.ReadString('\n')
    line = strings.TrimSuffix(line, "\n")
    checkErr(err)

    if line == "yes" {
        return true
    } else {
        return false
    }
}

func checkErr(err error) {
    if err != nil {
        panic(err)
    }
}