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

// Datatype for SQL payments
type Payment struct {
	Id		int
	Customerid	int
	Amount		float64
	Date		time.Time
	Billids		[]int
}

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

// Useful to Map some old ids to New ones
type IdMap struct {
	Old int
	New int
}

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',
	    paymentid INTEGER DEFAULT 0); 
        CREATE TABLE payments(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            customerid INTEGER DEFAULT NULL,
            amount REAL DEFAULT 0,
	    bills VARCHAR(240),
            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(bonus+sli+"Specify Task: ")
			if task == "" {
				nm,st := GetTaskSums(currproject.Id)
				if len(nm) > 0 {
					ch := Multichoice("What Task should be Started at "+timstr+"?",st)
					bonus = line("xxx",true)
					if ch>=0{
						task = nm[ch]
						break
					}
				}
				fmt.Println(bonus+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)
					bonus = line("xxx",true)
					if ch>=0{
						task = nm[ch]
					//	bonus = line("xxx",true)
					}else{
						fmt.Println(bonus+nli,boldRed("Aborted"))
						fmt.Println(bonus+frame(negR(),false))
						return
					}
				}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(bonus+sli+"Specify Task: ")
			if task == "" {
				nm,st := GetTaskSums(currproject.Id)
				if len(nm) > 0 {
					ch := Multichoice("What Task should be restarted?",st)
					bonus = line("xxx",true)
					if ch>=0{
						task = nm[ch]
					//	bonus = line("xxx",true)
						break
					}
				}
				fmt.Println(bonus+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 DESC LIMIT ?", count)
	}
	checkErr(err)
	var id, proj, payid 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, &payid)
		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.Local().Format("2006.01.02"))
		//fmt.Println(p)
		//if p == "1791.09.30" {
		if payid == 0 {
			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))
}

// Show Full Bill info of single bill, fullest to true for related tasks
func ShowBill(in bill,fullest bool) {
	boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
	//boldRed := color.New(color.FgRed, color.Bold).SprintFunc()
	addText := ""
	projn,custn := getProjectName(in.project)
	var tsks []Task
	if fullest {
		rows, err := db.Query("SELECT * FROM timetable WHERE checkout = ? ORDER BY stop DESC", in.id)
		checkErr(err)
		var id,prj,chk int
		var start, stop time.Time
		var tas,comm string
		defer rows.Close()
		for rows.Next() {
			err = rows.Scan(&id, &prj, &start, &stop, &tas, &comm, &chk)
			checkErr(err)
			tsks = append(tsks,Task{id,prj,start,stop,tas,comm,chk})
		}
		for _,t := range tsks {
			stastr := t.Start.Local().Format("2006-01-02 15:04")
			endstr := t.Stop.Local().Format("15:04")
			addText += fmt.Sprintf("%sId: %v - Project: %v : %s (%s - %s)\n",nli,t.Id,t.Projectid,t.Taskname,stastr,endstr)
		}
	}
	fmt.Println(frame(boldGreen("Show Bill ", in.id),true))
	if fullest {
		fmt.Printf("%sId: %v - InvoiceNumber: %v\n",nli,in.id,in.identity)
	}else{
		fmt.Println(nli," This Bill:")
	}
	fmt.Printf("%sFor: %s\n",nli,custn)
	fmt.Printf("%sProject: %s\n",nli,projn)
	fmt.Println(sub("Bill Items"))
	fmt.Println(StrLines(ShowItems(in.items),nli))
	if fullest {
		fmt.Println(sub("Billed Tasks"))
		fmt.Print(addText)
	}
	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, payid 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, &payid)
		checkErr(err)
		itms := strings2items(taskstr, timestr, hourstr, moneystr)
		prname, _ := getProjectName(proj)
		bi := bill{id, ident, timerange, proj, prname, date, paid, itms, payid}
		out = append(out, bi)
	}
	return
}

// Save Customer in DB and return its new id
func SaveCustomer(in Customer) (int) {
	//tila := in.Lastbill.Local().Format("2006-01-02 15:04 MST")
	tila := in.Lastbill.Local().Format("2006-01-02 15:04")
	stmt, err := db.Prepare("INSERT INTO customers (company, name ,address ,satz ,lastbill) values(?, ?, ?, ?, ?)")
	checkErr(err)
	res, err := stmt.Exec(in.Company,in.Name,in.Address,in.Satz,tila)
	checkErr(err)
	newid, err := res.LastInsertId()
	checkErr(err)
	return int(newid)
}

// Save Payment in DB and return its new id
func SavePayment(in Payment) (int) {
	tila := in.Date.Local().Format("2006-01-02 15:04")
	billids := IntArray2String(in.Billids,";")
	if in.Id == 0 {
		stmt, err := db.Prepare("INSERT INTO payments (customerid, amount ,bills ,date) values(?, ?, ?, ?)")
		checkErr(err)
		res, err := stmt.Exec(in.Customerid,in.Amount,billids,tila)
		checkErr(err)
		newid, err := res.LastInsertId()
		checkErr(err)
		return int(newid)
	} else {
		stmt, err := db.Prepare("UPDATE payments SET customerid = ? , amount = ? , bills = ? , date = ? ) WHERE id = ?")
		checkErr(err)
		_, err = stmt.Exec(in.Customerid,in.Amount,billids,tila,in.Id)
		checkErr(err)
		return in.Id
	}
}

// Save Project in DB and return its new id
func SaveProject(in Project) (int) {
	tifi := in.First.Local().Format("2006-01-02 15:04")
	tila := in.Last.Local().Format("2006-01-02 15:04")
	stmt, err := db.Prepare("INSERT INTO projects (name ,comment ,first ,last ,finished ,customer) values(?, ?, datetime(?,'utc'), datetime(?,'utc'), ?, ?)")
	checkErr(err)
	res, err := stmt.Exec(in.Name,in.Comment,tifi,tila,in.Finished,in.Customer)
	checkErr(err)
	newid, err := res.LastInsertId()
	checkErr(err)
	return int(newid)
}

// Save Task in DB
func SaveTask(in Task) {
	tista := in.Start.Local().Format("2006-01-02 15:04")
	tisto := in.Stop.Local().Format("2006-01-02 15:04")
	stmt, err := db.Prepare("INSERT INTO timetable (project, start ,stop ,task ,comment ,checkout) values(?, datetime(?,'utc'), datetime(?,'utc'), ?, ?, ?)")
	checkErr(err)
	_, err = stmt.Exec(in.Projectid, tista, tisto, in.Taskname, in.Comment, in.Checkout)
	checkErr(err)
}

// Save Bill in DB
func SaveBill(in bill) {
	tidat := in.date.Local().Format("2006-01-02 15:04")
	tipai := in.paid.Local().Format("2006-01-02 15:04")
	tsks,dats,tims,mos := items2strings(in.items)
	stmt, err := db.Prepare("INSERT INTO bills (identity, timerange, project, tasks, times, hours, moneys, paid, date) values(?, ?, ?,?, ?, ?, ?, datetime(?,'utc'), datetime(?,'utc'))")
	checkErr(err)
	_, err = stmt.Exec(in.identity, in.timerange, in.project, tsks, dats, tims,mos, tidat, tipai)
	checkErr(err)
}

// Loop through Array of []Customers, []Projects and []Tasks, update new db ids and call their respected SaveFunctions
func SaveImportsToDB(cus []Customer, prs []Project, tsk []Task, bls []bill) {
	boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
	var cusids, prids []IdMap
	//var acc,rej []Project
	fmt.Println(frame(boldGreen("Saving Imported Data to DB"),true))
	fmt.Println(nli," Customers:",len(cus))
	fmt.Println(nli,"  Projects:",len(prs))
	fmt.Println(nli,"     Tasks:",len(tsk))
	fmt.Println(nli,"     Bills:",len(bls))
	fmt.Println(sub(""))
	if isInterSure(nli+" Are You sure to Import them all?") {
		i := 0
		for _,c := range cus {
			id := SaveCustomer(c)
			cusids=append(cusids,IdMap{c.Id,id})
			i++
		}
		//fmt.Println(cusids)
		prs = ChangeCustomerIds(cusids,prs)
		for _,p := range prs {
			id := SaveProject(p)
			prids=append(prids,IdMap{p.Id,id})
			i++
		}
		//fmt.Println(prids)
		tsk = ChangeProjectIds(prids,tsk)
		for _,t := range tsk {
			SaveTask(t)
			i++
		}
		bls = ChangeBillIds(prids,bls)
		for _,t := range bls {
			SaveBill(t)
			i++
		}
		fmt.Println(nli,i,"items imported")
		fmt.Println(frame(posR(),false))
	}else{
		fmt.Println(frame(negR(),false))
	}
}

// Swap the old Ids in []Project array from imported 
// cutsomers to the new ones
func ChangeCustomerIds(idm []IdMap, prs []Project) ([]Project) {
	out := prs
	for ip,p := range prs {
		out[ip].Customer = GetMapId(p.Customer,idm)
	}
	return out
}

// Swap the old Ids in []Task array from imported 
// projects to the new ones
func ChangeProjectIds(idm []IdMap, tsk []Task) ([]Task) {
	out := tsk
	for it,t := range tsk {
		out[it].Projectid = GetMapId(t.Projectid,idm)
	}
	return out
}

// Swap the old Ids in []bill array from imported 
// projects to the new ones
func ChangeBillIds(idm []IdMap, bll []bill) ([]bill) {
	out := bll
	for it,t := range bll {
		out[it].project = GetMapId(t.project,idm)
	}
	return out
}

// Check which entry holds the old id and return the new one
func GetMapId(id int, idm []IdMap) (int) {
	if id == 0 {
		return 0
	}
	some := 0
	for _,i := range idm {
		if id == i.Old {
			some = i.New
		}
	}
	return some
}

/*
	fmt.Println(nli,"Rejected Entries:",len(rej)," Accepted Entries:",len(acc))
	if len(rej) > 0 {
		fmt.Println(sub("Rejections"))
		for _,r := range rej {
			fmt.Printf("%s %v: %s (%s - %s) - Fin: %v, Cutomer: %v\n",nli ,r.Id,r.Name,r.First.Local().Format("2006-01-02 15:04 MST"),r.Last.Local().Format("2006-01-02 15:04 MST"),r.Finished,r.Customer)
		}
		fmt.Println(nli)
	}
	if len(acc) > 0 {
		fmt.Println(sub("Accepted and Imported"))
		for _,r := range acc {
			fmt.Printf("%s %v: %s (%s - %s) - Fin: %v, Cutomer: %v\n",nli ,r.Id,r.Name,r.First.Local().Format("2006-01-02 15:04 MST"),r.Last.Local().Format("2006-01-02 15:04 MST"),r.Finished,r.Customer)
		}
		fmt.Println(nli)
	}
	fmt.Println(frame("",false))
	*/

func ShowItems(itms []billitem) (out []string) {
	out = append(out, "SomeShit")
	//doneinfo2 := ""
	sumh,summ := 0.0, 0.0
	cnt := 0
	for _,itm := range itms {
		out = append(out, fmt.Sprintf("  %s (%s)  - %v(h) - %v€",itm.Task,itm.Time,itm.Hours,itm.Money))
		sumh += itm.Hours
		summ += itm.Money
		cnt += 1
	}
	out[0] =  fmt.Sprintf("Summary: %v Items, %v (h), %v€",cnt,sumh,summ)

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

// TODO Make an uncheck function
func checkBill(billid, payid, projectid int, datestr string) {
	//boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
	boldRed := color.New(color.FgRed, color.Bold).SprintFunc()
	custid := getCustomerId(projectid)
	//var custid int
	//fmt.Println(frame(boldGreen("Checking Bill"),true))
	if !isBill(billid) {
		fmt.Println(nli,billid, 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'), paymentid = ? WHERE id = ?")
	checkErr(err)
	_, err = stmt.Exec(datestr, payid, billid)
	checkErr(err)

	stmt, err = db.Prepare("UPDATE customers SET lastbill = datetime(?,'utc') WHERE id = ?")
	checkErr(err)
	_, err = stmt.Exec(datestr, 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 Comments of all selected Tasks if they exist as a []string
func GetSelectedComments(in []Task) (comms []string) {
	for _,tsk := range in {
		if len(tsk.Comment) > 0{
			comms = append(comms,fmt.Sprintf("%s - %s",tsk.Taskname,tsk.Comment))
		}
	}
	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
}

// Returns a List of All Bills in id list and returns a slice of the corresponding ids ordered
func GetBillList(in []int) ([]int, []string) {
	var outids []int
	var outstr []string

	ins := strings.Trim(strings.Replace(fmt.Sprint(in), " ", " , ", -1), "[]")
	que := fmt.Sprintf("SELECT id, identity, project FROM bills WHERE id IN (%s) ORDER BY date DESC", ins)
	rows, err := db.Query(que)
	checkErr(err)

	var id,prj int
	var name string

	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&id, &name, &prj)
		checkErr(err)
		outids = append(outids, id)
		pro, cust := getProjectName(prj)
		outstr = append(outstr, fmt.Sprintf("%s - %s for %s",name, pro, cust))
	}
	return outids, outstr
}

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
}

// Returns slice of ids of Bills if parameter open is true only open bills are included 
func GetBillIds(open bool) []int {
	rows, err := db.Query("SELECT id FROM bills") // ORDER BY id DESC")
	var ids []int
	if open{
		rows, err = db.Query("SELECT id FROM bills WHERE paymentid == 0") // ORDER BY id DESC")
	}else{
		rows, err = db.Query("SELECT id FROM bills") // 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 Bills 
func GetAllBills() (out []bill) {
	//var ids []int
	rows, err := db.Query("SELECT * FROM bills") // ORDER BY id DESC")
	checkErr(err)
	var id,prj,payid int
	var ide,trg,prn string
	var dat,pai time.Time
	var itms []billitem
	var tsks,tims,hrs,mos string

	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&id,&ide,&trg,&prj,&tsks,&tims,&hrs,&mos,&pai,&dat)
		checkErr(err)
		prn,_ = getProjectName(id)
		itms = strings2items(tsks,tims,hrs,mos)
		out = append(out, bill{id,ide,trg,prj,prn,dat,pai,itms,payid})
	}
	return
}

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

// Show status of a Project of given id
func ShowProjectStatus(id int) {
	pr,cu := GetProject(id)
	showProject(pr,cu)

	ShowProjectSum(id)
	ShowClosedTasks(0,id)

	//showCurrentTask()
}

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(currproject.Id)
		ShowClosedTasks(0,currproject.Id)
	}

	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 given Project id  and display them with simmilar name
func ShowProjectSum(id int) {
	_,st := GetTaskSums(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))
	}
}

// Show a project
func showProject(prj Project,cus Customer) {
	boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
	fmt.Println(frame(boldGreen("Project "+prj.Name),true))
	fmt.Println(nli,prj.Id, ":", prj.Name, "- Started:", prj.First.Local().Format("Mon _2 Jan 2006"))
	fmt.Println(nli,"  Last Changes", prj.Last.Local().Format("2006 Mon Jan _2 15:04"))
	fmt.Println(nli,"  For:", cus.Company,"-",cus.Name," Hourly Rate:",cus.Satz,"[€/h]")
}

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

// Unser Input to create a new Payment
func AddPayment(dat string) {
	//TODO
	boldRed := color.New(color.FgRed, color.Bold).SprintFunc()
	boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
	const form = "2006-01-02"
	date,_ := time.Parse(form,getDate(dat))
	if dat == "jetzt" {
		date = time.Now().Local()//.Format("2006-01-02")
	}
	fmt.Println(frame(boldGreen("Adding new Payment"),true))
	amtfl := 0.0
	for{
		amtstr := getInterInput(sli+"Enter Amount: ")
		amtfl, err = strconv.ParseFloat(amtstr, 64)
		if err != nil {
			fmt.Println(nli,amtstr, boldRed("can not be Parsed as a Float."), "Try a shape of X.X")
		} else {
			break
		}
	}
	allCustomers(true)
	icust := 0
	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"))
		}
	}
	billids := GetBillIds(true)
	listids,billlist := GetBillList(billids)
	var selids []int
	for{
		res := Checklist("What Bills are includes in this Payment",billlist)
		out := func() (c []int) {
			for _,v := range res {
				if v > -1 {
					c = append(c, listids[v])
				}
			}
			return
		}
		selids = out()
		if len(selids)>0{
			break
		}else{
			fmt.Println(boldRed("At least one Bill should Correspont to a payment!"))
			getInterInput("<continue>")
		}
	}
	mybills := loadBills(selids)
	sum := 0.0
	for _,bill := range mybills {
		for _,it := range bill.items {
			sum += it.Money
		}
	}

	fmt.Println(frame(boldGreen("New Payment"),true))
	fmt.Println(nli,"  Amount:",amtfl)
	fmt.Println(nli,"    Date:",date.Format("2006-01-02"))
	fmt.Println(nli,"Customer:",getCustomerName(icust))
	fmt.Println(sub("Invoices"))
	for _,bi := range mybills{
		bsum := 0.0
		hsum := 0.0
		for _,it := range bi.items {
			bsum += it.Money
			hsum += it.Hours
		}
		fmt.Printf("%s   %s : %s (%s) %.1f[h] %.2f[€]\n",nli,bi.identity,bi.projectname,bi.date.Format("2006-01-02 15:04"),hsum,bsum)
	}
	if amtfl != sum {
		fmt.Println(sli,boldRed("  The sum of the bills (",sum,"€) does not match Payment amount!"))
		if amtfl > sum {
			fmt.Printf("%s   %.2f € Overpaid\n",nli,(amtfl-sum) )
		}
		if amtfl < sum {
			fmt.Printf("%s   %.2f € Underpaid\n",nli,(amtfl-sum) )
		}
	}
	if isInterSure(sli+"Do you want to continue?") {
		paym := Payment{0,icust,amtfl,date,selids}
		pid := SavePayment(paym)
		for _,bi := range mybills {
			checkBill(bi.id,pid,bi.project,date.UTC().Format("2006-01-02 15:04"))
		}
		fmt.Println(frame(posR(),false))
	}else{
		fmt.Println(frame("Closing payment",false))
	}//fmt.Println(paym)checkBill(billid, payid, projectid int, datestr string)
}
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()
}

// Show all Closed Tasks sorted by Billed and unbilled Tasks, Num Caps the number of Bills Shown (Not Used)
func ShowClosedTasks(num,prid int) {
	rows, err := db.Query("SELECT * FROM timetable WHERE stop != '1791-09-30 19:07' ORDER BY datetime(start)", prid)
	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", prid, 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)", prid)
		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", prid, 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)", prid)
		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 editBill(id int) {
	boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
	boldRed := color.New(color.FgRed, color.Bold).SprintFunc()
	fmt.Println(frame(boldGreen("Edit Bill ", id),true))
	var prj int
	var dat, pai time.Time
	var ident,timran, taskstr, timesstr, hoursstr, moneysstr, datstr, paistr string
	rows, err := db.Query("SELECT identity, timerange, project, tasks, times, hours, moneys, paid, date FROM bills WHERE id = $1", id)
	checkErr(err)
	if rows.Next() {
		err = rows.Scan(&ident, &timran, &prj, &taskstr, &timesstr, &hoursstr, &moneysstr, &pai, & dat)
	} else {
		fmt.Println(nli,boldRed("There Is No Bill"), id)
		return
		//os.Exit(0)
	}
	rows.Close() //good habit to close
	ident = getNewInterInput("New Bill Identifier: ", ident, nli)
	timran = getNewInterInput("New Timerange:", timran, nli)

	paiyr := pai.Local().Format("2006-01-02")
	paistr = pai.Local().Format("2006-01-02 15:04")
	datstr = dat.Local().Format("2006-01-02 15:04")
	for {
		newdatstr := getNewInterInput("New Date: ", datstr, nli)
		if !isDateTime(newdatstr) {
			fmt.Println(nli, newdatstr, boldRed("is Not a Valid Timestring!"), "use: 'YYYY-MM-DD HH:MM'")
		} else {
			datstr = newdatstr
			break
		}
	}
	if paiyr == "1791-09-30" {
		fmt.Println(nli,boldRed("Open"))
	}else{
		for {
			newpaistr := getNewInterInput("New Paid date: ", paistr, nli)
			if !isDateTime(newpaistr) {
				fmt.Println(nli, newpaistr, boldRed("is Not a Valid Timestring!"), "use: 'YYYY-MM-DD HH:MM'")
			} else {
				paistr = newpaistr
				break
			}
		}
	}
	for {
		fmt.Printf("%s  !!Setting a different project id will not change the corresponding Tasks.\n%s  Delete Bill to renew Task selection!!\n",nli,nli)
		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
		}
	}
	fmt.Println(sli,"Current Items:")
	itms := strings2items(taskstr,timesstr,hoursstr,moneysstr)
	fmt.Println(StrLines(ShowItems(itms),nli))

	if isInterSure(sli+"Do You Want to edit them?") {
		for i,_ := range itms {
			itms[i].Task = getNewInterInput("New Task: ", itms[i].Task, nli)
			itms[i].Time = getNewInterInput("New Timerange: ", itms[i].Time, nli)
			for {
				hourstr := getNewInterInput("New Hours: ", fmt.Sprintf("%.2f(h)", itms[i].Hours), nli)
				itms[i].Hours, err = strconv.ParseFloat(hourstr, 64)
				if err != nil {
					fmt.Println(nli,hourstr, boldRed("can not be Parsed as a Float."), "Try a shape of X.X")
				} else {
					break
				}
			}
			for {
				monstr := getNewInterInput("New Sum: ", fmt.Sprintf("%.2f €", itms[i].Money), nli)
				itms[i].Money, err = strconv.ParseFloat(monstr, 64)
				if err != nil {
					fmt.Println(nli,monstr, boldRed("can not be Parsed as a Float."), "Try a shape of X.X")
				} else {
					break
				}
			}
		}
	}
	taskstr,timesstr,hoursstr,moneysstr = items2strings(itms)
	stmt, err := db.Prepare("UPDATE bills SET identity = ?, timerange = ?, project = ?, tasks = ?, times = ?, hours = ?, moneys = ?, paid = datetime(?,'utc'), date = datetime(?,'utc') WHERE id = ?")
	checkErr(err)
	_, err = stmt.Exec(ident,timran, prj, taskstr, timesstr, hoursstr, moneysstr, paistr, datstr, id)
	checkErr(err)
	fmt.Println(nli,"...Bill", 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
	}
}
//TODO Change get Funcs to time.Parse()
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)
    }
}*/