package main

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

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

type project struct{
    id int
    name 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
    checkout int // id of bill
}

type customer struct{
    id int
    company string
    name string
    address string
    satz float64
    lastbill time.Time
}

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
}

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 DEFAULT 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 DEFAULT NULL);
        CREATE TABLE customers(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            company VARCHAR(240),
            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),
            timerange VARCHAR(240),
            project INTEGER NOT NULL,
            tasks VARCHAR(240),
            times VARCHAR(240),
            hours VARCHAR(240),
            moneys VARCHAR(240),
            paid TIMESTAMP DEFAULT datetime('1791-09-30 19:07','utc'),
            date TIMESTAMP DEFAULT datetime('1791-09-30 19:07','utc') );
        `
        _, 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)
    }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"
        //zone, _ := time.Now().Zone()
        if isDate(tim) {
            timstr = getDate(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 := 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 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 := getInput("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)
}

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 = string(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,&timestr,&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,&timestr,&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
}

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
}

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

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

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

    for rows.Next() {
        err = rows.Scan(&pid, &name, &first, &last, &finished, &customer)
        checkErr(err)
        outpr.id = pid
        outpr.name = name
        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)

    var id,project,checkout int
    var start,stop time.Time
    var tsk string

    defer rows.Close()
    for rows.Next() {
        err = rows.Scan(&id,&project,&start,&stop,&tsk,&checkout)
        outtask = append(outtask,task{id,project,start,stop,tsk,checkout})
    }
    return
}
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
}

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)

    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
}

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

    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

    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
}

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() {
    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() {
    boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
    fmt.Println(boldGreen("Adding new Customer"))
    com := getInput("Enter Customer Company: ")
    nam := getInput("Enter Customer Name: ")
    add := getInput("Enter Address (separate lines by ;  [Street;Zip;City;Country]): ")
    satstr := getInput("Hourly Rate: ")
    sat,err := strconv.ParseFloat(satstr,64)
    checkErr(err)

    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()
    fmt.Println(boldGreen("Creating new Project"))
    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) {
    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 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, &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)

    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.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()
    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.finished = finish
        //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")
    //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)
        fmt.Printf(" %v:%s: %s, Rate:%.2f(€/h) , Last:%s\n",uid,comp,name,satz,last.Local().Format("2006-01-02 15:04 MST"))
    }
}

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)

    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 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, &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)
            }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 isSure("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 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("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 := getInput("Enter New:")
    if in!=""{
        comp=in
    }
    fmt.Println("Old Name:",name)
    in = getInput("Enter New:")
    if in!=""{
        name=in
    }
    fmt.Println("Old Adress:",addr)
    in = getInput("Enter New:")
    if in!=""{
        addr=in
    }
    fmt.Println("Old Hourly Rate:",satz)
    for{
        satzstr := getInput("Enter New:")
        if satzstr!=""{
            satz,err = strconv.ParseFloat(satzstr,64)
            if err != nil {
                fmt.Println(satzstr,boldRed("can not be Parsed as a Float."), "Try a shape of X.X")
                //os.Exit(0)
            }else{break}
        }else{break}
    }
    stmt, err := db.Prepare("UPDATE customers SET company = ?, name = ?, address = ?, satz = ? WHERE id = ?")
    checkErr(err)
    _, err = stmt.Exec(comp,name,addr,satz,id)
    checkErr(err)
    fmt.Println("...Customer",id,"Updated")
}

func editTask(id int) {
    boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
    boldRed := color.New(color.FgRed, color.Bold).SprintFunc()
    fmt.Println(boldGreen("Edit Task ",id))
    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("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()
    fmt.Println(boldGreen("Edit Project ",id))
    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("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 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
    }
}
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 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 checkErr(err error) {
    if err != nil {
        panic(err)
    }
}*/