summaryrefslogtreecommitdiff
path: root/sqlite.go
diff options
context:
space:
mode:
Diffstat (limited to 'sqlite.go')
-rw-r--r--sqlite.go166
1 files changed, 150 insertions, 16 deletions
diff --git a/sqlite.go b/sqlite.go
index b3cafb8..40f6fd1 100644
--- a/sqlite.go
+++ b/sqlite.go
@@ -47,6 +47,15 @@ type Customer struct {
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
@@ -63,6 +72,7 @@ type bill struct {
date time.Time
paid time.Time
items []billitem
+ paymentid int
}
// Useful to Map some old ids to New ones
@@ -120,7 +130,14 @@ func initDB(filename string) {
hours VARCHAR(240),
moneys VARCHAR(240),
paid TIMESTAMP DEFAULT '1791-09-30 19:07',
- date 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,
@@ -389,7 +406,7 @@ func showLastBills(count int) {
rows, err = db.Query("SELECT * FROM bills ORDER BY date DESC LIMIT ?", count)
}
checkErr(err)
- var id, proj int
+ var id, proj, payid int
var ident, timerange string
var date, paid time.Time
var taskstr, timestr, hourstr, moneystr string
@@ -406,16 +423,16 @@ func showLastBills(count int) {
i := 0
for rows.Next() {
i++
- err = rows.Scan(&id, &ident, &timerange, &proj, &taskstr, &timestr, &hourstr, &moneystr, &paid, &date)
+ 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"))
+ //p := fmt.Sprintf("%v", paid.Local().Format("2006.01.02"))
//fmt.Println(p)
- //if p == "1791-09-30 19:07:00 +0000 UTC" {
- if p == "1791.09.30" {
+ //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"))
@@ -475,18 +492,18 @@ func loadBills(in []int) (out []bill) {
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 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)
+ 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}
+ bi := bill{id, ident, timerange, proj, prname, date, paid, itms, payid}
out = append(out, bi)
}
return
@@ -505,6 +522,27 @@ func SaveCustomer(in Customer) (int) {
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")
@@ -784,7 +822,8 @@ func closeTask(loud bool) {
}
}
}
-
+//TODO Adapt Function to Save paymentid Or dump it completely
+// TODO Make an uncheck function
func checkBill(bid int) {
boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
boldRed := color.New(color.FgRed, color.Bold).SprintFunc()
@@ -1229,10 +1268,15 @@ func GetTaskIds() []int {
return ids
}
-// Returns slice of ids of all Bills
-func GetBillIds() []int {
- var ids []int
+// 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
@@ -1250,7 +1294,7 @@ func GetAllBills() (out []bill) {
//var ids []int
rows, err := db.Query("SELECT * FROM bills") // ORDER BY id DESC")
checkErr(err)
- var id,prj int
+ var id,prj,payid int
var ide,trg,prn string
var dat,pai time.Time
var itms []billitem
@@ -1262,7 +1306,7 @@ func GetAllBills() (out []bill) {
checkErr(err)
prn,_ = getProjectName(id)
itms = strings2items(tsks,tims,hrs,mos)
- out = append(out, bill{id,ide,trg,prj,prn,dat,pai,itms})
+ out = append(out, bill{id,ide,trg,prj,prn,dat,pai,itms,payid})
}
return
}
@@ -1418,6 +1462,96 @@ func showOpenProject(alone bool) {
//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
+ for _,it := range bi.items {
+ bsum += it.Money
+ }
+ fmt.Printf("%s %s : %s (%s) %.2f[€]\n",nli,bi.identity,bi.projectname,bi.date.Format("2006-01-02 15:04"),bsum)
+ }
+ if amtfl != sum {
+ fmt.Println(sli,boldRed(" The sum of the billsi (",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}
+ SavePayment(paym)
+ fmt.Println(frame(posR(),false))
+ }else{
+ fmt.Println(frame("Closing payment",false))
+ }//fmt.Println(paym)
+}
func addCustomer() {
boldRed := color.New(color.FgRed, color.Bold).SprintFunc()
boldGreen := color.New(color.FgGreen, color.Bold).SprintFunc()
@@ -2204,7 +2338,7 @@ func isCustomer(id int) bool {
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]`)
generated by cgit v1.2.3 (git 2.25.1) at 2025-11-08 22:31:14 +0000