// sqlite.go contains all database operations
package main

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

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

// Datatype of SQL projects table
type Project struct {
	Id       int
	Name     string
	Comment  string
	First    time.Time
	Last     time.Time
	Finished int // id of last paid bill
	Customer int
}

// Datatype of SQL timetable table
type Task struct {
	Id        int
	Projectid int
	Start     time.Time
	Stop      time.Time
	Taskname  string
	Comment   string
	Checkout  int // id of bill
}

// Datatype of SQL customer table
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 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
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 := `
        CREATE TABLE projects(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name VARCHAR(240) NOT NULL,
            comment VARCHAR(240) DEFAULT '',
            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,
            comment VARCHAR(240) DEFAULT '',
            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 '1791-09-30 19:07',
            date TIMESTAMP DEFAULT '1791-09-30 19:07' ); 
	CREATE TABLE vars(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            pauseid INTEGER DEFAULT NULL,
            last TIMESTAMP DEFAULT '1791-09-30 19:07',
	    color INTEGER DEFAULT 1 );
	CREATE TRIGGER first AFTER INSERT ON timetable
	    BEGIN
	        update vars SET last = datetime('now') WHERE id = 1;
	    END;
	CREATE TRIGGER latest AFTER UPDATE ON timetable
	    BEGIN
	        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())
	}
}

func lastUsage() (out string) {
	var dat time.Time
	rows, err := db.Query("SELECT last FROM vars WHERE id = 1")
	checkErr(err)
	for rows.Next() {
		err = rows.Scan(&dat)
		checkErr(err)
	}
	out = dat.Local().Format("2006 Mon Jan _2 15:04")
	return
}

// Get the color set in vars table
func GetColor() (col int) {
	col = 1
	rows, err := db.Query("SELECT color FROM vars WHERE id = $1",1)
	checkErr(err)
	for rows.Next() {
		err = rows.Scan(&col)
		checkErr(err)
	}
	return
}

// Set the color in vars table
func SetColor(col int) {
	stmt, err := db.Prepare("UPDATE vars SET color = ? WHERE id = 1")
	checkErr(err)
	_, err = stmt.Exec(col)
	checkErr(err)
}

func getPauseTask() (id int) {
	rows, err := db.Query("SELECT pauseid FROM vars WHERE id = 1")
	checkErr(err)
	for rows.Next() {
		err = rows.Scan(&id)
		checkErr(err)
	}
	return
}

func setPauseTask(id int) {
	stmt, err := db.Prepare("UPDATE vars SET pauseid = ? WHERE id = 1 ")
	checkErr(err)
	_, err = stmt.Exec(id)
	checkErr(err)
	pausetask = id
}

func newTaskTime(tim string) {
	boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
	boldRed := color.New(color.FgRed, color.Bold).SprintFunc()
	fmt.Println(frame(boldGreen("Starting Task at ", tim),true))
	bonus := ""
	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(nli,tim, boldRed("is Not a Valid Timestring!"), "use: 'YYYY-MM-DD HH:MM' or 'HH:MM'")
			fmt.Println(frame(negR(),false))
			return
			//os.Exit(0)
		}
		stmt, err := db.Prepare("INSERT INTO timetable(project, start, task, checkout) values(?, datetime(?,'utc'), ?, ?)")
		checkErr(err)
		fmt.Println(nli+timstr)
		task := ""
		for{
			task = getInterInput(sli+"Specify Task: ")
			if task == "" {
				nm,st := GetTaskSums(currproject.Id)
				if len(nm) > 0 {
					ch := Multichoice("What Task should be Started at "+timstr+"?",st)
					task = nm[ch]
					bonus = line("xxx",true)
					break
				}
				fmt.Println(nli,boldRed("An empty Taskname is not acceptable"))
			}else{break}
		}
		//if proj == 0 {
		_, err = stmt.Exec(currproject.Id, timstr, task, 0)
		//} else {
		//	_, err = stmt.Exec(proj, timstr, task, 0)
		//}
		checkErr(err)

		fmt.Println(bonus+nli+"...new task inserted into", currproject.Name, ": ", task)
		fmt.Println(bonus+frame(posR(),false))
		getOpenTask()
		updateProject(currproject.Id)
	} else {
		fmt.Println(nli+boldRed("Another Task is already Open"))
		//fmt.Println(frame("Close Task First",false))
		showCurrentTask()
	}

}

func newTask(resume bool) {
	boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
	boldRed := color.New(color.FgRed, color.Bold).SprintFunc()
	task := ""
	bonus := ""
	if resume {
		fmt.Println(frame(boldGreen("Resuming Task"),true))
	}else{
		fmt.Println(frame(boldGreen("Starting Task Now"),true))
	}
	if opentask.Id > 0 {
		fmt.Println(nli+boldRed("Another Task is already Open"))
		//showOpenTask()
		fmt.Println(frame(negR(),false))
		return
	}
	if resume {
		if pausetask == 0 {
			fmt.Println(nli+boldRed("No Task was Paused"))
			if isInterSure(nli+"Resume older task?"){
				nm,st := GetTaskSums(currproject.Id)
				if len(nm)>0 {
					ch := Multichoice("What Task should be resumed?",st)
					task = nm[ch]
					bonus = line("xxx",true)
				}else{
					fmt.Println(nli,boldRed("There are no Tasks to resume"))
					fmt.Println(frame(negR(),false))
					return

				}
			}else{
				fmt.Println(frame(negR(),false))
				return
			}
		} else {
			idx := []int{pausetask}
			tsks := GetSelectedTasks(idx)
			fulltask := tsks[0]
			fmt.Println(nli+"Resuming Task ", pausetask, " - ", fulltask.Taskname)
			//fmt.Println()
			task = fulltask.Taskname
		}
	} else {
		//fmt.Println(boldGreen("Starting new Task"))
		for{
			task = getInterInput(sli+"Specify Task: ")
			if task == "" {
				nm,st := GetTaskSums(currproject.Id)
				if len(nm) > 0 {
					ch := Multichoice("What Task should be restarted?",st)
					task = nm[ch]
					bonus = line("xxx",true)
					break
				}
				fmt.Println(nli,boldRed("An empty Taskname is not acceptable"))
			}else{break}
		}
	}
	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(bonus+nli+"...New Task inserted into", currproject.Name, ": ", task)
	}
	fmt.Println(bonus+frame(posR(),false))
	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()
	//Show 5 recent bills
	showLastBills(5)
	fmt.Println(frame(boldGreen("Creating New Bill"),true))
	invno := getInterInput(sli+"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(frame("Bill "+ invno+" Created with ID "+lid),false)
	fmt.Println(nli+"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(nli+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)
	fmt.Println(frame(posR(),false))
}

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.Println(frame(boldGreen("All Bills"),true))
		//fmt.Print("___All Previous Bills_______\n")
	} else {
		str := fmt.Sprintf("Previous %v Bills", count)
		fmt.Println(frame(boldGreen(str),true))
	}
	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("%s %v:%s - %s (%v) %.1f[h]: %.2f[€] - ",nli, 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(nli+"\n"+nli,boldRed("      NONE"))
	}
	fmt.Println(nli)
	fmt.Println(frame("",false))
}

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))
	fmt.Println(frame(boldGreen("Stoping Task"),true))
	if opentask.Id == 0 {
		fmt.Println(nli+boldRed("There is no Open Task"))
		fmt.Println(frame(negR(),false))
		return
	}else{
		fmt.Println(nli,"ID ",opentask.Id, ":", opentask.Taskname)
	}
	//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(nli,tim, boldRed("is Not a Valid Timestring!"), "use: 'YYYY-MM-DD HH:MM' or 'HH:MM'")
		fmt.Println(frame(negR(),false))
		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")
		com := ""
		if isInterSure(sli+"Do You Want to enter a Comment?") {
			com = getInterMultiInput(nli+"Comment:")
		}
		fmt.Println(nli,"...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)
		fmt.Println(frame(posR(),false))
	} else {
		fmt.Println(nli,boldRed("Cannot Stop before the Beginning!"))
		fmt.Println(frame(negR(),false))
		return
	}
	//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(frame(boldGreen("Stoping Task"),true))
		if opentask.Id == 0 {
			fmt.Println(nli,boldRed("There is no Open Task"))
			fmt.Println(frame(negR(),false))
			return
		}else{
			fmt.Println(nli,"ID ",opentask.Id, ":", opentask.Taskname)
		}
		//fmt.Println(boldGreen("Stoping Task ", opentask.id, ":", opentask.taskname))
	}else{
		if opentask.Id == 0 {
			fmt.Println(boldRed("There is no Open Task"))
		}
	}
	if time.Now().After(opentask.Start.Local()) {
		com := ""
		if loud {
			if isInterSure(sli+"Do You Want to enter a Comment?") {
				com = getInterMultiInput(nli+"Comment:")
			}
			fmt.Println(nli+"...Closing Task", opentask.Id)
			fmt.Println(frame(posR(),false))
		}
		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 {
		if loud {
			fmt.Println(nli,boldRed("Cannot Stop before the Beginning!"))
			fmt.Println(frame(negR(),false))
		}else{
			fmt.Println(boldRed("Cannot Stop before the Begining!"))
		}
	}
}

func checkBill(bid int) {
	boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
	boldRed := color.New(color.FgRed, color.Bold).SprintFunc()
	var custid int
	fmt.Println(frame(boldGreen("Checking Bill"),true))
	if !isBill(bid) {
		fmt.Println(nli,bid, boldRed("is not a known bill ID"))
		fmt.Println(frame(negR(),false))
		return
	}

	bar := []int{bid}
	bill := loadBills(bar)

	if len(bill) < 1 {
		fmt.Println(nli, boldRed("The Bill cannot be loaded."))
		fmt.Println(frame(negR(),false))
		return
	} else {
		pr, cu := getProjectName(bill[0].project)
		custid = getCustomerId(bill[0].project)
		//fmt.Println(bill[0])
		fmt.Println(nli+"Marking Bill ", bid, " as Paid:\n"+nli, bill[0].identity, "\n"+nli+" For", cu, " :", pr)
		_, _, ho, ma := items2strings(bill[0].items)
		hsum := sumFloatArray(string2FloatArray(ho, ";"))
		msum := sumFloatArray(string2FloatArray(ma, ";"))
		fmt.Printf(nli+" Date: %s  Hours: %.1f[h]  Sum: %.2f[€]\n"+nli, 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"

	timin := getInterInput(sli+"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(nli+timin, boldRed("is Not a Valid Datestring!"), "use: 'YYYY-MM-DD'")
		fmt.Println(frame(negR(),false))
		return
	}
	fmt.Println(nli+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)
	fmt.Println(frame(posR(),false))
}

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

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
}

// return a project and a customer struct that corresponds
// to a project id. 
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
}
// Return the []customers corresponding to an []int of customer ids
func GetSelectedCustomers(in []int) (out []Customer) {
	ins := strings.Trim(strings.Replace(fmt.Sprint(in), " ", " , ", -1), "[]")
	que := fmt.Sprintf("SELECT * FROM customers WHERE id IN (%s)", ins)
	rows, err := db.Query(que)
	checkErr(err)

	var id int
	var rat float64
	var last time.Time
	var nam, com, add string

	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&id, &com, &nam, &add, &rat,  &last)
		out = append(out, Customer{id,com,nam,add,rat,last})
	}
	return
}

// Return the []projects corresponding to an []int of project ids
func GetSelectedProjects(in []int) (out []Project) {
	ins := strings.Trim(strings.Replace(fmt.Sprint(in), " ", " , ", -1), "[]")
	que := fmt.Sprintf("SELECT * FROM projects WHERE id IN (%s)", ins)
	rows, err := db.Query(que)
	checkErr(err)

	var id,fin, cust int
	var first, last time.Time
	var nam, com string

	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&id, &nam, &com, &first, &last, &fin, &cust)
		out = append(out, Project{id,nam,com,first,last,fin,cust})
	}
	return
}

// Return the []tasks corresponding to an []int of task ids
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, 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
}
// Loop through all Tasks in a Slice and 
// Return count, 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("02.01."), hstop.Local().Format("02.01.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
}

// Returns two Lists of Task ids and corresponding strings for Multichoice selection.
// Depending on showcust the name of the customer will be displayed too
// Depending on exclude Tasks of project 0 will be in the Return Lists
func GetTaskList(in []int, showcust,exclude 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")
/*		if exclude {
			rows, err = db.Query("SELECT id, project, start, stop, task FROM timetable WHERE stop != '1791-09-30 19:07' AND checkout = 0 AND project != 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)
		pr, cu := getProjectName(project)
		if project != lastpr {
			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)

		if !(exclude && cu == "No one") {
			outids = append(outids, id)
			outstr = append(outstr, fmt.Sprintf("%s%s", pre, txt))
		}
	}
	return outids, outstr
}

// Returns slice of ids of all Projects except Project 0
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
}

// Returns slice of ids of all Customers except Customer 0
func GetCustomerIds() []int {
	var ids []int
	rows, err := db.Query("SELECT id FROM customers 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
}

// Returns Slice of All Task ids except open ones
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
}

// Get All Tasks of Project with prid as a slice of Tasknames and
// a curresponding slice of strings to display
func GetTaskSums(prid int) (names,strings []string ) {
	rows, err := db.Query("SELECT task FROM timetable WHERE stop != '1791-09-30 19:07' AND project = $1",prid)
	checkErr(err)
	var nam string
	for rows.Next() {
		err = rows.Scan(&nam)
		checkErr(err)
		if !IsStrElement(nam,names){
			names = append(names, nam)
		}
	}
	for _,na := range names {
		var ids []int
		var id int
		quer := fmt.Sprintf("SELECT id FROM timetable WHERE stop != '1791-09-30 19:07' AND task = '%s' AND project = %v",na,prid)
		//rows, err = db.Query("SELECT id FROM timetable WHERE stop != '1791-09-30 19:07' AND task = ' $1 ' AND project = $2",na,prid)
		rows, err = db.Query(quer)
		checkErr(err)
		for rows.Next() {
			err = rows.Scan(&id)
			checkErr(err)
			ids = append(ids,id)
		}
		tsks := GetSelectedTasks(ids)
		//fmt.Println(na,ids)
		//fmt.Println(tsks)
		count,hours,daterange := AnalyzeTasks(tsks)
		full := fmt.Sprintf("%s:  %vx (%s) %.2f[h]",na,count,daterange,hours)
		strings = append(strings, full)

	}

	return
}

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 showStatus(full bool) {
/*	boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
	fmt.Println(frame(boldGreen("Status"),true))
	fmt.Println(sub("Current Project"))
	fmt.Println(nli,currproject.id, ":", currproject.name, "- Started:", currproject.first.Local().Format("Mon _2 Jan 2006"))
	fmt.Println(nli,"  Last Changes", currproject.last.Local().Format("2006 Mon Jan _2 15:04"))
*/
	showOpenProject(true)

	if full {
		ShowProjectSum()
		getClosedTasks(0)
	}

	showCurrentTask()

/*	if opentask.id == 0 {
		if pausetask > 0 {
			st := fmt.Sprintf("Task %v Paused", pausetask)
			fmt.Println(frame(st,false))
		} else {
			fmt.Println(frame("No Open Tasks",false))
		}
	} else {
		fmt.Println(sub("Open Task"))
		dur := float64(time.Now().Sub(opentask.start)) / (1000000000 * 60 * 60)
		fmt.Printf("%s %v: %v - (%v) - %.2f h\n", nli, opentask.id, opentask.taskname, opentask.start.Local().Format("Mon Jan _2 2006 15:04"), dur)
		fmt.Println(frame("",false))
	}
*/
}

// Get all Tasks of the current Project and display them with simmilar name
func ShowProjectSum() {
	_,st := GetTaskSums(currproject.Id)
	fmt.Println(sub("Tasks"))
	fmt.Println(StrLines(st,nli))
	fmt.Println(sub(""))
}

func showCurrentTask() {
	if opentask.Id == 0 {
		if pausetask > 0 {
			//fmt.Printf("___Task %v Paused___________\n", pausetask)
			st := fmt.Sprintf("Task %v Paused", pausetask)
			fmt.Println(frame(st,false))
		} else {
			//fmt.Println("___No Open Tasks____________")
			fmt.Println(frame("No Open Tasks",false))
		}
	} else {
		//fmt.Println("___Open Task________________")
		fmt.Println(sub("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")
		fmt.Printf("%s %v: %v - (%v) - %.2f h\n", nli, opentask.Id, opentask.Taskname, opentask.Start.Local().Format("Mon Jan _2 2006 15:04"), dur)
		fmt.Println(frame("",false))
	}
}

func showOpenProject(alone bool) {
	boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
	//fmt.Println("___Last Project_____________")
	if alone {
		fmt.Println(frame(boldGreen("Current Project"),true))
	}
	fmt.Println(nli,currproject.Id, ":", currproject.Name, "- Started:", currproject.First.Local().Format("Mon _2 Jan 2006"))
	fmt.Println(nli,"  Last Changes", currproject.Last.Local().Format("2006 Mon Jan _2 15:04"))
	//fmt.Println(frame("Current Project",true))
}

func addCustomer() {
	boldRed := color.New(color.FgRed, color.Bold).SprintFunc()
	boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
	fmt.Println(frame(boldGreen("Adding new Customer"),true))
	com := getInterInput(sli+"Enter Customer Company: ")
	nam := getInterInput(sli+"Enter Customer Name: ")
	add := getInterInput(sli+"Enter Address (separate lines by ;  [Street;Zip;City;Country]): ")
	sat := 0.0
	for {
		satstr := getInterInput(sli+"Hourly Rate: ")
		sat, err = strconv.ParseFloat(satstr, 64)
		//checkErr(err)
		if err != nil {
			fmt.Println(nli,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(nli,boldGreen("   Customer Successfully Added:"), com, nam, add, sat)
	fmt.Println(frame(posR(),false))
}

func newProject() {
	boldRed := color.New(color.FgRed, color.Bold).SprintFunc()
	boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
	fmt.Println(frame(boldGreen("Creating new Project"),true))
	if opentask.Id > 0 {
		fmt.Println(nli,boldRed("There is an Open Task"))
		fmt.Println(frame(negR(),false))
		//showOpenTask()
		return
	}
	if pausetask > 0 {
		fmt.Println(sli,boldRed("Task ", pausetask, " pause status removed"))
		fmt.Println(nli)
		setPauseTask(0)
	}
	//fmt.Println(boldGreen("Creating new Project"))
	nam := getInterInput(sli+"Enter Project Name: ")
	icust := 0
	allCustomers(true)
	for {
		cust := getInterInput(sli+"Enter Customer id: ")
		icust, err = strconv.Atoi(cust)
		if err == nil && (isCustomer(icust) || icust == 0) {
			break
		} else {
			fmt.Println(nli,cust, boldRed("is an invalid ID or Not a known Customer"))
		}
	}
	comm := ""
	if isInterSure(sli+"Do you want to Comment the Project?") {
		comm = getInterMultiInput(nli+"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(nli,"   Project Created:", nam)
	fmt.Println(frame(posR(),false))
	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_______________")
			fmt.Println(sub("Billed 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("%s %v: %v (%v-%v) - %.2f h\n", nli, 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)
		st := fmt.Sprintf("Billed: %.2f h", sum)
		fmt.Println(sub(st))
		fmt.Println(nli)
	}
	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_______________")
			fmt.Println(sub("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("%s %v: %v (%v-%v) - %.2f h\n", nli, 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)
		st := fmt.Sprintf("Unbilled: %.2f[h]  Total: %.2f[h]", sum2, sum+sum2)
		fmt.Println(sub(st))
	}
	rows.Close()
}

func getLastProject() {
	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

}

func setProject(nid int) {
	boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
	boldRed := color.New(color.FgRed, color.Bold).SprintFunc()
	fmt.Println(frame(boldGreen("Opening Project ", nid),true))
	if opentask.Id > 0 {
		fmt.Println(nli,boldRed("There is an Open Task"))
		//fmt.Println(frame("",false))
		showCurrentTask()
		return
	}
	if pausetask > 0 {
		fmt.Println(sli,boldRed("Task ", pausetask, " pause status removed"))
		fmt.Println(nli)
		setPauseTask(0)
	}
	if !isProject(nid) {
		//fmt.Println(boldGreen("Opening Project ", nid))
	//} else {
		fmt.Println(nli,boldRed("There is no Project"), nid)
		fmt.Println(frame("",false))
		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

	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)

	showOpenProject(false)
	showCurrentTask()
}

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
}

func allCustomers(inline bool) {
	boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
	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________________")
	if inline {
		fmt.Println(sub(boldGreen("All Customers")))
	}else{
		fmt.Println(frame(boldGreen("All Customers"),true))
	}
	cnt := 0
	for rows.Next() {
		cnt++
		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"
		}
		if uid > 0 {
			fmt.Printf("%s %v:%s: %s, Rate: %.2f[€/h] , Last Paid Bill: %s\n", nli, uid, comp, name, satz, lstr)
		}
	}
	if cnt==0 {
		fmt.Println(nli,"    Nobody")
	}
	if inline {
		fmt.Println(sub(""))
	}else{
		fmt.Println(frame("",false))
	}
}

func allProjects() {
	boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
	//fmt.Println(boldGreen("Loading all customers"))
	//fmt.Println("___All Projects________________")
	fmt.Println(frame(boldGreen("All Projects"),true))
	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, comm string
		var first time.Time
		var last time.Time
		var finish int
		var customer, check int
		var start, stop time.Time

		fmt.Printf("%s%s %s: %s, %s\n",ssli,li,"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("%s %v:%s \n", sli, uid, prname)
				fmt.Printf("%s  Unbilled: %.2f[h]  Billed: %.2f[h] | Total: %.2f[h]\n", nli, 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("%s  First: %s, Last:%s, \n%s\n", nli, first.Local().Format("2006-01-02 15:04 MST"), last.Local().Format("2006-01-02 15:04 MST"),nli)
			} else {
				if uid >0 {
					fmt.Print(sli,"        Nothing\n")
				}
			}
			rows2.Close() //good habit to close
		}
		rows.Close() //good habit to close
	}
	rows3.Close() //good habit to close
	fmt.Println(frame("",false))
	//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(frame(boldGreen("Deleting Bill ", id),true))
	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("%s %v: For %v- %v (%v) - %.1f[h] : %.2f[€]\n", nli, identity, custr, prstr, date.Local().Format("2006 Mon Jan _2"), hsum, msum)
		if isInterSure(sli+"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(nli,boldGreen("Bill ", id, " deleted successfully!"))
		} else {
			fmt.Println(frame(negR(),false))
			return
		}
	} else {
		fmt.Println(nli,boldRed(id, " is Not a known Bill!"))
		showLastBills(0)
	}
	fmt.Println(frame("",false))
}

func deleteTask(id int) {
	boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
	boldRed := color.New(color.FgRed, color.Bold).SprintFunc()
	fmt.Println(frame(boldGreen("Deleting Task ", id),true))
	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("%s %v: %v (%v-%v) - %.2f h\n   Comments:\n%s\n", nli, prj, task, start.Local().Format("2006 Mon Jan _2 15:04"), stop.Local().Format("15:04"), dur, comm)
		if isInterSure(sli+"Are You Sure?") {
			stmt, err := db.Prepare("DELETE FROM timetable WHERE id = ?")
			checkErr(err)
			_, err = stmt.Exec(id)
			checkErr(err)
			fmt.Println(nli,boldGreen("Task ", id, " deleted successfully!"))
		} else {
			fmt.Println(frame(negR(),false))
			return
		}
	} else {
		fmt.Println(nli,boldRed(id, " is Not a known Task!"))
	}
	fmt.Println(frame("",false))
}

func editCustomer(id int) {
	boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
	boldRed := color.New(color.FgRed, color.Bold).SprintFunc()
	fmt.Println(frame(boldGreen("Editing Customer ", id),true))
	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(nli+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, nli)
	/*fmt.Println("Old Name:",name)
	  in = getInterInput("Enter New:")
	  if in!=""{
	      name=in
	  }*/
	name = getNewInterInput("New Customer Name: ", name, nli)
	/*fmt.Println("Old Adress:",addr)
	  in = getInterInput("Enter New:")
	  if in!=""{
	      addr=in
	  }*/
	addr = getNewInterInput("New Adress: ", addr, nli)
	//fmt.Println("Old Hourly Rate:",satz)
	for {
		satzstr := getNewInterInput("New Hourly Rate: ", fmt.Sprintf("%.2f", satz), nli)
		satz, err = strconv.ParseFloat(satzstr, 64)
		if err != nil {
			fmt.Println(nli,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(nli,"...Customer", id, "Updated")
	fmt.Println(frame("",false))
}

func editTask(id int) {
	boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
	boldRed := color.New(color.FgRed, color.Bold).SprintFunc()
	fmt.Println(frame(boldGreen("Edit Task ", id),true))
	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(nli,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, nli)

	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, nli)
		if !isDateTime(newstartstr) {
			fmt.Println(nli, newstartstr, boldRed("is Not a Valid Timestring!"), "use: 'YYYY-MM-DD HH:MM'")
		} else {
			startstr = newstartstr
			break
		}
	}
	//fmt.Println("Old End:",stopstr)
	for {
		newend := getNewInterInput("New Stop time: ", stopstr, nli)
		if isDateTime(newend) {
			stopstr = newend
			break
		} else {
			fmt.Println(nli, 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), nli)
		prj, err = strconv.Atoi(newprj)
		if err != nil {
			fmt.Println(nli,newprj, boldRed("is Not a Valid id."), "Try an Integer instead")
		}
		if !isProject(prj) {
			fmt.Println(nli, boldRed("There is no project"), prj)
		} else {
			break
		}
	}
	comm = getNewInterMultiInput("New Comment: ", comm, nli)

	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(nli, "...Task", id, "Updated")
	fmt.Println(frame("",false))
}

func editProject(id int) {
	boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
	boldRed := color.New(color.FgRed, color.Bold).SprintFunc()
	fmt.Println(frame(boldGreen("Edit Project ", id),true))
	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, nli)
		nfirst = first.Local().Format("2006-01-02 15:04")
		// Get New PRoject Begin Date
		for {
			newfirststr := getNewInterInput("New Begin time: ", nfirst, nli)
			if !isDateTime(newfirststr) {
				fmt.Println(nli,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), nli)
			icust, err := strconv.Atoi(newcu)
			if err != nil {
				fmt.Println(nli,newcu, boldRed("is Not a Valid id."), "Try an Integer instead")
			} else if !isCustomer(icust) {
				fmt.Println(nli,boldRed("There is no Customer"), icust)
			} else {
				cust = icust
				break
			}
		}
		// Get Comment
		comm = getNewInterMultiInput("New Comment: ", comm, nli)
	} else {
		fmt.Println(nli,boldRed("There Is No Project"), id)
		fmt.Println(frame(negR(),false))
		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(nli,"...Project", id, "Updated")
	fmt.Println(frame("",false))
}

func PromptColor(col int) (mastercol *color.Color){
	boldBlue := color.New(color.FgBlue, color.Bold)//.SprintFunc()
        boldRed := color.New(color.FgRed, color.Bold)//.SprintFunc()
        boldMag := color.New(color.FgMagenta, color.Bold)//.SprintFunc()
        boldCyan := color.New(color.FgCyan, color.Bold)//.SprintFunc()
        boldGreen := color.New(color.FgGreen, color.Bold)//.SprintFunc()
        boldYell := color.New(color.FgYellow, color.Bold)//.SprintFunc()
        //boldCol := color.New(color.FgYellow, color.Bold).SprintFunc()

	SetColor(col)
	switch col {
	case 0:
		mastercol = boldMag
	case 1:
		mastercol = boldBlue
	case 2:
		mastercol = boldCyan
	case 3:
		mastercol = boldGreen
	case 4:
		mastercol = boldYell
	case 5:
		mastercol = boldRed
	}
	return
}

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 getDateTime(in string) string {
	r := regexp.MustCompile(`(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2})`)
	//r := regexp.MustCompile(`(\d{4})-(((0)[0-9])|((1)[0-2]))-([0-2][0-9]|(3)[0-1]) ([01]?[0-9]|2[0-3]):[0-5][0-9]`)
	return r.FindString(in)
}

func getDate(in string) string {
	//r := regexp.MustCompile(`(\d{4})-(\d{2})-(\d{2})`)
	r := regexp.MustCompile(`(\d{4})-(((0)[0-9])|((1)[0-2]))-([0-2][0-9]|(3)[0-1])`)
	return r.FindString(in)
}

func getTime(in string) string {
	//r := regexp.MustCompile(`(\d{2}):(\d{2})`)
	r := regexp.MustCompile(`([01]?[0-9]|2[0-3]):[0-5][0-9]`)
	return r.FindString(in)
}

func isDateTime(in string) bool {
	//match, _ := regexp.MatchString(`(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2})`, in)
	//match, _ := regexp.MatchString(`(\d{4})-(((0)[0-9])|((1)[0-2]))-([0-2][0-9]|(3)[0-1]) ([01]?[0-9]|2[0-3]):[0-5][0-9]`, in)
	const form = "2006-01-02 15:04"
	_,err := time.Parse(form,in)
	if err == nil {
		return true
	}
	return false
}

func isDate(in string) bool {
	//match, _ := regexp.MatchString(`(\d{4})-(\d{2})-(\d{2})`, in)
	//match, _ := regexp.MatchString(`(\d{4})-(((0)[0-9])|((1)[0-2]))-([0-2][0-9]|(3)[0-1])`, in)
	//return match
	const form = "2006-01-02"
	_,err := time.Parse(form,in)
	if err == nil {
		return true
	}
	return false
}

func isTime(in string) bool {
	//match, _ := regexp.MatchString(`(\d{2}):(\d{2})`, in)
	//match, _ := regexp.MatchString(`([01]?[0-9]|2[0-3]):[0-5][0-9]`, in)
	//return match
	const form = "15:04"
	_,err := time.Parse(form,in)
	if err == nil {
		return true
	}
	return false
}

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