path: root/sqlite.go

package main

import (
    "database/sql"
    "fmt"
    "time"
    "os"
    "bufio"
    "strings"
    "regexp"
    _ "github.com/mattn/go-sqlite3"
)

type project struct{
    id int
    name string
    first time.Time
    last time.Time
    finish 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")
        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);
        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);
        `
        _, err = db.Exec(sqlstmt)
        checkErr(err)
    }else{
        db, err = sql.Open("sqlite3", filename)
        checkErr(err)
        fmt.Println("Opening DB")
    }
}

func newTaskTime(proj int, tim string) {
    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,"is Not a Valid Timestring! use: 'YYYY-MM-DD HH:MM' or 'HH:MM'")
            os.Exit(0)
        }
        stmt, err := db.Prepare("INSERT INTO timetable(project, start, task, checkout) values(?, datetime(?,'utc'), ?, ?)")
        fmt.Println(timstr)
        task := getInput("Specify Task: ")
        //stmt, err := db.Prepare("INSERT INTO timetable(project, start, task, checkout) values(?, datetime(?,'localtime'), ?, ?)")
        checkErr(err)
        if (proj == 0) {
            _, err = stmt.Exec(currproject.id,timstr,task,0)
        }else{
            _, err = stmt.Exec(proj,timstr,task,0)
        }
        checkErr(err)
        //stmt, err = db.Prepare("UPDATE timetable SET start = datetime('?') WHERE id = ?")
        //checkErr(err)
        //_, err = stmt.Exec(opentask.id)
        //checkErr(err)

        fmt.Println("...new task inserted into",currproject.name,": ",task)
        getOpenTask()
        updateProject(currproject.id)
    } else {
        fmt.Println("Another Task is already Open")
        showOpenTask()
    }

}

func newTask(proj int) {
    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("Another Task is already Open")
        showOpenTask()
    }
}

func closeTaskTime(tim string) {
    //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,"is Not a Valid Timestring! use: 'YYYY-MM-DD HH:MM' or 'HH:MM'")
        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("Cannot Stop before the Beginning!")
    }
    //fmt.Println(tim,timt)
}

func closeTask() {
    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("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, currproject.last.Local().Format("2006 Mon Jan _2 15:04 2006"))
    }
}


func newProject() {
    if (opentask.id == 0) {
        nam := getInput("Enter Project Name: ")
        stmt, err := db.Prepare("INSERT INTO projects(name, finished) values(?, ?)")
        checkErr(err)
        _, err = stmt.Exec(nam,0)
        checkErr(err)
        fmt.Println("   Project Created:",nam)
        getLastProject()
    } else {
        fmt.Println("There is an Open Task")
        showOpenTask()
    }
}

func getClosedTasks(num int) {
    //var rows sql.Rows
    //var err error
    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.Println("Gesamt:",sum,"h")
}

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

    for rows.Next() {
        err = rows.Scan(&uid, &prname, &first, &last, &finish)
        checkErr(err)
        if (last.After(nlast)){
            nuid=uid
            nprname=prname
            nfirst=first
            nlast=last
            nfinish=finish
        }
    }
    rows.Close() //good habit to close
   // fmt.Println(nuid,"Last Project",nprname)
   // fmt.Println(nlast)
   // fmt.Println("----------------------------------")

    currproject.id = nuid
    currproject.name = nprname
    currproject.first = nfirst
    currproject.last = nlast
    currproject.finish = nfinish
   // getLastTasks(0)
}

func setProject (nid int) {
    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

    for rows.Next() {
        err = rows.Scan(&uid, &prname, &first, &last, &finish)
        checkErr(err)
   }
    rows.Close() //good habit to close
    currproject.id = uid
    currproject.name = prname
    currproject.first = first
    currproject.last = last
    currproject.finish = finish
    updateProject(uid)
}

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

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

    fmt.Println("___All Projects________________")
    for rows.Next() {
        err = rows.Scan(&uid, &prname, &first, &last, &finish)
        checkErr(err)
        fmt.Println(" ",uid,prname,first.Local().Format("2006-09-02 19:13"),last.Local().Format("2006-09-02 19:13"),finish)
    }
    rows.Close() //good habit to close
}

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 checkErr(err error) {
    if err != nil {
        panic(err)
    }
}