summaryrefslogtreecommitdiff
path: root/sqlite.go
diff options
context:
space:
mode:
Diffstat (limited to 'sqlite.go')
-rw-r--r--sqlite.go115
1 files changed, 74 insertions, 41 deletions
diff --git a/sqlite.go b/sqlite.go
index 073559d..d5f196e 100644
--- a/sqlite.go
+++ b/sqlite.go
@@ -43,6 +43,7 @@ type Customer struct {
Company string
Name string
Address string
+ Uid string
Satz float64
Lastbill time.Time // Last time a bill was paid
}
@@ -73,6 +74,7 @@ type bill struct {
paid time.Time
items []billitem
paymentid int
+ ust float64
}
// Useful to Map some old ids to New ones
@@ -133,7 +135,8 @@ func initDB(filename string) {
name VARCHAR(240),
address VARCHAR(240) DEFAULT 'None',
satz REAL DEFAULT 1,
- lastbill TIMESTAMP DEFAULT '1791-09-30 19:07' );
+ lastbill TIMESTAMP DEFAULT '1791-09-30 19:07',
+ uid VARCHAR(240) DEFAULT 'None');
CREATE TABLE bills(
id INTEGER PRIMARY KEY AUTOINCREMENT,
identity VARCHAR(240),
@@ -145,7 +148,8 @@ func initDB(filename string) {
moneys VARCHAR(240),
paid TIMESTAMP DEFAULT '1791-09-30 19:07',
date TIMESTAMP DEFAULT '1791-09-30 19:07',
- paymentid INTEGER DEFAULT 0);
+ paymentid INTEGER DEFAULT 0,
+ ust REAL DEFAULT 0);
CREATE TABLE payments(
id INTEGER PRIMARY KEY AUTOINCREMENT,
customerid INTEGER DEFAULT NULL,
@@ -384,9 +388,9 @@ func newBill(proj int) (int, string) {
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'))")
+ stmt, err := db.Prepare("INSERT INTO bills (identity, project, ust, date) values(?, ?, ?, datetime('now'))")
checkErr(err)
- answ, err := stmt.Exec(invno, proj)
+ answ, err := stmt.Exec(invno, proj, config.Ust)
checkErr(err)
lid, _ := answ.LastInsertId()
//fmt.Println(frame("Bill "+ invno+" Created with ID "+lid),false)
@@ -398,9 +402,9 @@ 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 = ?")
+ stmt, err := db.Prepare("UPDATE bills SET identity = ?, timerange = ?, tasks = ?, times = ?, hours = ?, moneys = ?, ust = ? WHERE id = ?")
checkErr(err)
- _, err = stmt.Exec(in.identity, in.timerange, tasks, times, hours, moneys, in.id)
+ _, err = stmt.Exec(in.identity, in.timerange, tasks, times, hours, moneys, in.ust, in.id)
checkErr(err)
fmt.Println(frame(posR(),false))
}
@@ -424,6 +428,7 @@ func showLastBills(count int) {
var ident, timerange string
var date, paid time.Time
var taskstr, timestr, hourstr, moneystr string
+ var ust float64
defer rows.Close()
//fmt.Println("___Open Task________________")
@@ -437,12 +442,17 @@ func showLastBills(count int) {
i := 0
for rows.Next() {
i++
- err = rows.Scan(&id, &ident, &timerange, &proj, &taskstr, &timestr, &hourstr, &moneystr, &paid, &date, &payid)
+ err = rows.Scan(&id, &ident, &timerange, &proj, &taskstr, &timestr, &hourstr, &moneystr, &paid, &date, &payid, &ust)
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)
+ tx := msum * ust / 100
+ if (ust == 0) {
+ fmt.Printf("%s %v:%s - %s (%v) %.1f[h]: %.2f[€] - ",nli, id, ident, prn, date.Local().Format("2006.01.02"), hsum, msum)
+ }else{
+ fmt.Printf("%s %v:%s - %s (%v) %.1f[h]: %.2f[€] (excl. %.2f[€] USt) - ",nli, id, ident, prn, date.Local().Format("2006.01.02"), hsum, msum, tx)
+ }
//p := fmt.Sprintf("%v", paid.Local().Format("2006.01.02"))
//fmt.Println(p)
//if p == "1791.09.30" {
@@ -510,14 +520,15 @@ func loadBills(in []int) (out []bill) {
var ident, timerange string
var date, paid time.Time
var taskstr, timestr, hourstr, moneystr string
+ var ust float64
defer rows.Close()
for rows.Next() {
- err = rows.Scan(&id, &ident, &timerange, &proj, &taskstr, &timestr, &hourstr, &moneystr, &paid, &date, &payid)
+ err = rows.Scan(&id, &ident, &timerange, &proj, &taskstr, &timestr, &hourstr, &moneystr, &paid, &date, &payid, &ust)
checkErr(err)
itms := strings2items(taskstr, timestr, hourstr, moneystr)
prname, _ := getProjectName(proj)
- bi := bill{id, ident, timerange, proj, prname, date, paid, itms, payid}
+ bi := bill{id, ident, timerange, proj, prname, date, paid, itms, payid, ust}
out = append(out, bi)
}
return
@@ -527,9 +538,9 @@ func loadBills(in []int) (out []bill) {
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(?, ?, ?, ?, ?)")
+ stmt, err := db.Prepare("INSERT INTO customers (company, name ,address ,uid ,satz ,lastbill) values(?, ?, ?, ?, ?)")
checkErr(err)
- res, err := stmt.Exec(in.Company,in.Name,in.Address,in.Satz,tila)
+ res, err := stmt.Exec(in.Company,in.Name,in.Address,in.Uid,in.Satz,tila)
checkErr(err)
newid, err := res.LastInsertId()
checkErr(err)
@@ -585,9 +596,9 @@ func SaveBill(in bill) (int) {
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, paymentid) values(?, ?, ?,?, ?, ?, ?, datetime(?,'utc'), datetime(?,'utc'), ?)")
+ stmt, err := db.Prepare("INSERT INTO bills (identity, timerange, project, tasks, times, hours, moneys, paid, date, paymentid, ust) values(?, ?, ?,?, ?, ?, ?, datetime(?,'utc'), datetime(?,'utc'), ?, ?)")
checkErr(err)
- res, err := stmt.Exec(in.identity, in.timerange, in.project, tsks, dats, tims,mos, tidat, tipai,in.paymentid)
+ res, err := stmt.Exec(in.identity, in.timerange, in.project, tsks, dats, tims,mos, tidat, tipai,in.paymentid, in.ust)
checkErr(err)
newid, err := res.LastInsertId()
checkErr(err)
@@ -1085,17 +1096,18 @@ func GetProject(id int) (outpr Project, outcu Customer) {
row, err := db.Query("SELECT * FROM customers WHERE id = ?", customer)
checkErr(err)
var cid int
- var comp, cuname, addy string
+ var comp, cuname, addy, uid string
var satz float64
var lastb time.Time
for row.Next() {
- err = row.Scan(&cid, &comp, &cuname, &addy, &satz, &lastb)
+ err = row.Scan(&cid, &comp, &cuname, &addy, &satz, &lastb, &uid)
checkErr(err)
outcu.Id = cid
outcu.Company = comp
outcu.Name = cuname
outcu.Address = addy
+ outcu.Uid = uid
outcu.Satz = satz
outcu.Lastbill = lastb
}
@@ -1111,12 +1123,12 @@ func GetSelectedCustomers(in []int) (out []Customer) {
var id int
var rat float64
var last time.Time
- var nam, com, add string
+ var nam, com, add, uid 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})
+ err = rows.Scan(&id, &com, &nam, &add, &rat, &last, &uid)
+ out = append(out, Customer{id,com,nam,add,uid,rat,last})
}
return
}
@@ -1374,14 +1386,15 @@ func GetAllBills() (out []bill) {
var dat,pai time.Time
var itms []billitem
var tsks,tims,hrs,mos string
+ var ust float64
defer rows.Close()
for rows.Next() {
- err = rows.Scan(&id,&ide,&trg,&prj,&tsks,&tims,&hrs,&mos,&pai,&dat,&payid)
+ err = rows.Scan(&id,&ide,&trg,&prj,&tsks,&tims,&hrs,&mos,&pai,&dat,&payid,&ust)
checkErr(err)
prn,_ = getProjectName(id)
itms = strings2items(tsks,tims,hrs,mos)
- out = append(out, bill{id,ide,trg,prj,prn,dat,pai,itms,payid})
+ out = append(out, bill{id,ide,trg,prj,prn,dat,pai,itms,payid,ust})
}
return
}
@@ -1664,6 +1677,7 @@ func addCustomer() {
com := getInterInput(sli+"Enter Customer Company: ")
nam := getInterInput(sli+"Enter Customer Name: ")
add := getInterInput(sli+"Enter Address (separate lines by ; [Street;Zip;City;Country]): ")
+ uid := getInterInput(sli+"Enter Customers UID: ")
sat := 0.0
for {
satstr := getInterInput(sli+"Hourly Rate: ")
@@ -1676,11 +1690,11 @@ func addCustomer() {
}
}
- stmt, err := db.Prepare("INSERT INTO customers(company, name, address, satz) values(?, ?, ?, ?)")
+ stmt, err := db.Prepare("INSERT INTO customers(company, name, address, uid, satz) values(?, ?, ?, ?, ?)")
checkErr(err)
- _, err = stmt.Exec(com, nam, add, sat)
+ _, err = stmt.Exec(com, nam, add, uid, sat)
checkErr(err)
- fmt.Println(nli,boldGreen(" Customer Successfully Added:"), com, nam, add, sat)
+ fmt.Println(nli,boldGreen(" Customer Successfully Added:"), com, nam, add,uid, sat)
fmt.Println(frame(posR(),false))
}
@@ -1945,6 +1959,7 @@ func allCustomers(inline bool) {
var comp string
var name string
var addr string
+ var cuid string
var satz float64
var last time.Time
@@ -1957,7 +1972,7 @@ func allCustomers(inline bool) {
cnt := 0
for rows.Next() {
cnt++
- err = rows.Scan(&uid, &comp, &name, &addr, &satz, &last)
+ err = rows.Scan(&uid, &comp, &name, &addr, &cuid, &satz, &last)
checkErr(err)
lstr := last.Local().Format("2006-01-02 15:04 MST")
if lstr == "1791-09-30 20:12 LMT" {
@@ -1989,10 +2004,11 @@ func allProjects() {
var com string
var nam string
var adr string
+ var uid string
var sat float64
var lst time.Time
for rows3.Next() {
- err = rows3.Scan(&cid, &com, &nam, &adr, &sat, &lst)
+ err = rows3.Scan(&cid, &com, &nam, &adr, &uid, &sat, &lst)
checkErr(err)
rows, err := db.Query("SELECT * FROM projects WHERE customer = $1", cid)
@@ -2095,6 +2111,7 @@ func DeletePayment(id int) {
_, err = stmt.Exec(id)
checkErr(err)
fmt.Println(nli,boldGreen("Payment ", id, " deleted successfully!"))
+ //TODO Remove Bill Paed?!?
}
}else{
fmt.Println(nli,boldRed("There is no Payment "),id)
@@ -2280,6 +2297,7 @@ func EditPayment(id int) {
var mybills []bill
bsum := 0.0
hsum := 0.0
+ tsum := 0.0
if len(billstr)>0 {
biids = String2IntArray(billstr,";")
@@ -2357,6 +2375,7 @@ func EditPayment(id int) {
hsum = 0.0
bsum = 0.0
+ tsum = 0.0
for _,bi := range mybills{
bisum := 0.0
for _,it := range bi.items {
@@ -2365,15 +2384,17 @@ func EditPayment(id int) {
}
fmt.Printf("%s %s : %s (%s) %.1f[h] %.2f[€]\n",nli,bi.identity,bi.projectname,bi.date.Format("2006-01-02 15:04"),hsum,bisum)
bsum += bisum
+ tsum += bisum * bi.ust / 100
}
- if amount != bsum {
+
+ if amount != (bsum + tsum) {
fmt.Println(sub(""))
- fmt.Println(nli,boldRed(" The sum of the bills (",bsum,"€ for",hsum,"h) does not match Payment amount!"))
- if amount > bsum {
- fmt.Printf("%s %.2f € Overpaid\n",nli,(amount-bsum) )
+ fmt.Println(nli,boldRed(" The sum of the bills (",bsum,"€ +",tsum,"€ Tax for",hsum,"h) does not match Payment amount!"))
+ if amount > (bsum + tsum) {
+ fmt.Printf("%s %.2f € Overpaid\n",nli,(amount-bsum-tsum) )
}
- if amount < bsum {
- fmt.Printf("%s %.2f € Underpaid\n",nli,(amount-bsum) )
+ if amount < (bsum + tsum) {
+ fmt.Printf("%s %.2f € Underpaid\n",nli,(amount-bsum-tsum) )
}
}
@@ -2385,12 +2406,12 @@ 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 comp, name, addr, uid string
var satz float64
- rows, err := db.Query("SELECT company, name, address, satz FROM customers WHERE id = $1", id)
+ rows, err := db.Query("SELECT company, name, address, uid, satz FROM customers WHERE id = $1", id)
checkErr(err)
if rows.Next() {
- err = rows.Scan(&comp, &name, &addr, &satz)
+ err = rows.Scan(&comp, &name, &addr, &uid, &satz)
checkErr(err)
} else {
fmt.Println(nli+boldRed("There Is No Customer"), id)
@@ -2402,6 +2423,7 @@ func editCustomer(id int) {
comp = getNewInterInput("New Company Name: ", comp, nli)
name = getNewInterInput("New Customer Name: ", name, nli)
addr = getNewInterInput("New Adress: ", addr, nli)
+ uid = getNewInterInput("New UID: ", uid, nli)
for {
satzstr := getNewInterInput("New Hourly Rate: ", fmt.Sprintf("%.2f", satz), nli)
satz, err = strconv.ParseFloat(satzstr, 64)
@@ -2411,9 +2433,9 @@ func editCustomer(id int) {
break
}
}
- stmt, err := db.Prepare("UPDATE customers SET company = ?, name = ?, address = ?, satz = ? WHERE id = ?")
+ stmt, err := db.Prepare("UPDATE customers SET company = ?, name = ?, address = ?, uid = ?, satz = ? WHERE id = ?")
checkErr(err)
- _, err = stmt.Exec(comp, name, addr, satz, id)
+ _, err = stmt.Exec(comp, name, addr, uid, satz, id)
checkErr(err)
fmt.Println(nli,"...Customer", id, "Updated")
fmt.Println(frame("",false))
@@ -2426,10 +2448,11 @@ func editBill(id int) {
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)
+ var ust float64
+ rows, err := db.Query("SELECT identity, timerange, project, tasks, times, hours, moneys, paid, date, ust FROM bills WHERE id = $1", id)
checkErr(err)
if rows.Next() {
- err = rows.Scan(&ident, &timran, &prj, &taskstr, &timesstr, &hoursstr, &moneysstr, &pai, & dat)
+ err = rows.Scan(&ident, &timran, &prj, &taskstr, &timesstr, &hoursstr, &moneysstr, &pai, &dat, &ust)
} else {
fmt.Println(nli,boldRed("There Is No Bill"), id)
return
@@ -2477,6 +2500,16 @@ func editBill(id int) {
break
}
}
+ for {
+ uststr := getNewInterInput("New USt in %: ",fmt.Sprint(ust), nli)
+ ust, err = strconv.ParseFloat(uststr, 64)
+ //checkErr(err)
+ if err != nil {
+ fmt.Println(nli,uststr, boldRed("can not be Parsed as a Float."), "Try a sh ape of X.X")
+ } else {
+ break
+ }
+ }
fmt.Println(sli,"Current Items:")
itms := strings2items(taskstr,timesstr,hoursstr,moneysstr)
fmt.Println(StrLines(ShowItems(itms),nli))
@@ -2506,9 +2539,9 @@ func editBill(id int) {
}
}
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 = ?")
+ stmt, err := db.Prepare("UPDATE bills SET identity = ?, timerange = ?, project = ?, tasks = ?, times = ?, hours = ?, moneys = ?, paid = datetime(?,'utc'), date = datetime(?,'utc'), ust = ? WHERE id = ?")
checkErr(err)
- _, err = stmt.Exec(ident,timran, prj, taskstr, timesstr, hoursstr, moneysstr, paistr, datstr, id)
+ _, err = stmt.Exec(ident,timran, prj, taskstr, timesstr, hoursstr, moneysstr, paistr, datstr, ust, id)
checkErr(err)
fmt.Println(nli,"...Bill", id, "Updated")
fmt.Println(frame("",false))
generated by cgit v1.2.3 (git 2.25.1) at 2025-11-08 22:20:25 +0000