diff options
Diffstat (limited to 'sqlite.go')
| -rw-r--r-- | sqlite.go | 123 |
1 files changed, 123 insertions, 0 deletions
@@ -65,6 +65,12 @@ type bill struct { items []billitem } +// 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 @@ -430,6 +436,123 @@ func loadBills(in []int) (out []bill) { 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 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) +} + +// Loop through Array of []Projects and call SaveProject +func SaveImportsToDB(cus []Customer, prs []Project, tsk []Task) { + 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(sub("")) + for _,c := range cus { + id := SaveCustomer(c) + cusids=append(cusids,IdMap{c.Id,id}) + } + fmt.Println(cusids) + prs = ChangeCustomerIds(cusids,prs) + for _,p := range prs { + id := SaveProject(p) + prids=append(prids,IdMap{p.Id,id}) + } + fmt.Println(prids) + tsk = ChangeProjectIds(prids,tsk) + for _,t := range tsk { + SaveTask(t) + } +} + +// 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 +} + +// 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 strings2items(tasks, times, hours, moneys string) (out []billitem) { ta := String2StringArray(tasks, ";") ti := String2StringArray(times, ";") |