func ChargeBalance(uid int64, money float64) (string, error) { //检查用户是否存在 err := IsUserExist(uid) if err != nil { return "", err } var txerr error txerr = nil t := time.Now() tx, err := Db.Begin() recordTxErr(&txerr, &err) _, err = dbutil.DbUpdate(Db, "UPDATE tb_user SET balance=balance+? WHERE uid=?", money, uid) recordTxErr(&txerr, &err) unixTime := t.Unix() //插入order,生成订单,得oid orderNo := fmt.Sprintf("%02d%2d%02d%4d%2d%2d\n", t.Month(), t.Minute(), t.Day(), t.Year(), t.Second(), t.Hour()) orderNo = orderNo + strconv.FormatInt(unixTime, 10) + strconv.FormatInt(uid, 10) + strconv.FormatInt(0, 10) orderComment := "charge" oid, err := dbutil.DbInsert2(tx, "INSERT INTO tb_order (uid,price,orderTime,orderNo,comment) VALUES (?,?,?,?,?)", uid, money, unixTime, orderNo, orderComment) recordTxErr(&txerr, &err) //创建交易记录 _, err = dbutil.DbInsert2(tx, "INSERT INTO tb_deal (uid,oid,dealTime,dealPrice) VALUES (?,?,?,?)", uid, oid, unixTime, money) recordTxErr(&txerr, &err) if txerr != nil { tx.Rollback() return "", errors.New("ChargeBalance error") } else { tx.Commit() return "success", nil } }
func DriverCancelOrder(uidDriver int64, oid int64, drid int64) (string, error) { err := IsUserExist(uidDriver) if err != nil { return "", err } var txerr error txerr = nil t := time.Now() unixTime := t.Unix() tx, err := Db.Begin() recordTxErr(&txerr, &err) //司机订单置为无效 _, err = dbutil.DbUpdate2(tx, "UPDATE tb_order_route SET isEnable=? WHERE duid=? and oid=? and isEnable=true", false, uidDriver, oid) recordTxErr(&txerr, &err) //司机路线置为无效 _, err = dbutil.DbUpdate2(tx, "UPDATE tb_driver_route SET isEnable=? WHERE drid=?", false, drid) recordTxErr(&txerr, &err) //找出受到影响的乘客 effectedUser := []tmpOrderBean{} err = dbutil.DbQueryS2(tx, &effectedUser, "SELECT uid, price,oid FROM tb_order NATURAL JOIN tb_order_route WHERE drid =? and uid !=?", drid, uidDriver) recordTxErr(&txerr, &err) if len(effectedUser) > 0 { //司机减钱 _, err := dbutil.DbUpdate2(tx, "UPDATE tb_user SET balance=balance-? WHERE uid=?", effectedUser[0].price*float64(len(effectedUser)), uidDriver) recordTxErr(&txerr, &err) _, err = dbutil.DbInsert2(tx, "INSERT INTO tb_deal (uid,oid,dealTime,dealPrice) VALUES (?,?,?,?)", uidDriver, oid, unixTime, -effectedUser[0].price*float64(len(effectedUser))) recordTxErr(&txerr, &err) for _, bean := range effectedUser { //推送给用户告知订单被司机取消 //乘客订单置为无效并退回钱 _, err = dbutil.DbUpdate2(tx, "UPDATE tb_order_route SET isEnable=? WHERE uid=? and oid=?", false, bean.uid, oid) recordTxErr(&txerr, &err) _, err = dbutil.DbUpdate2(tx, "UPDATE tb_user SET balance=balance+? WHERE uid=?", bean.price, bean.uid) recordTxErr(&txerr, &err) //创建交易记录 _, err = dbutil.DbInsert2(tx, "INSERT INTO tb_deal (uid,oid,dealTime,dealPrice) VALUES (?,?,?,?)", bean.uid, bean.oid, unixTime, bean.price) recordTxErr(&txerr, &err) } } if txerr != nil { tx.Rollback() return "", errors.New("database DriverCancelRoute error") } else { tx.Commit() return "success", nil } }
func DriverAddRoute(uid int64, startTime int64, sPlace string, ePlace string, sLat float64, sLng float64, eLat float64, eLng float64, sCity string, eCity string, distance float64) (string, error) { //检查用户是否存在,时间是否已经过去,插入到route err := IsUserExist(uid) if err != nil { return "", err } err = checkStartTime(startTime, uid) if err != nil { return "", err } var txerr error txerr = nil tx, err := Db.Begin() recordTxErr(&txerr, &err) //插入到route 得 rid rid, err := dbutil.DbInsert2(tx, "INSERT INTO tb_route (startTime,sPlace,ePlace,sLat,sLng,eLat,eLng,sCity,eCity) VALUES (?,?,?,?,?,?,?,?,?)", startTime, sPlace, ePlace, sLat, sLng, eLat, eLng, sCity, eCity) recordTxErr(&txerr, &err) //单独插入到driver route得到drid drid, err := dbutil.DbInsert2(tx, "INSERT INTO tb_driver_route (duid,rid,unitPrice,distance) VALUES (?,?,?,?)", uid, rid, UnitPrice, distance) recordTxErr(&txerr, &err) //drid生成订单插入order 得oid t := time.Now() unixTime := t.Unix() orderNo := fmt.Sprintf("%02d%2d%02d%4d%2d%2d\n", t.Month(), t.Minute(), t.Day(), t.Year(), t.Second(), t.Hour()) orderNo = orderNo + strconv.FormatInt(startTime, 10) + strconv.FormatInt(uid, 10) + strconv.FormatInt(drid, 10) orderComment := "create route: \n" + sPlace + " to " + ePlace oid, err := dbutil.DbInsert2(tx, "INSERT INTO tb_order (uid,price,orderTime,orderNo,comment) VALUES (?,?,?,?,?)", uid, UnitPrice*distance, unixTime, orderNo, orderComment) recordTxErr(&txerr, &err) //插入到route order _, err = dbutil.DbInsert2(tx, "INSERT INTO tb_order_route (uid,duid,drid,oid) VALUES (?,?,?,?)", uid, uid, drid, oid) recordTxErr(&txerr, &err) //创建交易记录 _, err = dbutil.DbInsert2(tx, "INSERT INTO tb_deal (uid,oid,dealTime,dealPrice) VALUES (?,?,?,?)", uid, oid, unixTime, 0.0) recordTxErr(&txerr, &err) if txerr != nil { tx.Rollback() return "", errors.New("database DriverAddRoute error") } else { tx.Commit() return "success", nil } }
func PassengerCancelOrder(uidPassenger int64, oid int64, drid int64) (string, error) { err := IsUserExist(uidPassenger) if err != nil { return "", err } var txerr error txerr = nil t := time.Now() unixTime := t.Unix() tx, err := Db.Begin() recordTxErr(&txerr, &err) //乘客订单置为无效 _, err = dbutil.DbUpdate2(tx, "UPDATE tb_order_route SET isEnable=? WHERE uid=? and oid=? and isEnable=true", false, uidPassenger, oid) recordTxErr(&txerr, &err) //找出订单的价钱 prices := []float64{} err = dbutil.DbQueryS2(tx, &prices, "SELECT price FROM tb_order NATURAL JOIN tb_order_route WHERE oid =?", oid) if len(prices) > 0 { //乘客加回钱 _, err := dbutil.DbUpdate2(tx, "UPDATE tb_user SET balance=balance+? WHERE uid=?", prices[0], uidPassenger) recordTxErr(&txerr, &err) //创建交易记录 _, err = dbutil.DbInsert2(tx, "INSERT INTO tb_deal (uid,oid,dealTime,dealPrice) VALUES (?,?,?,?)", uidPassenger, oid, unixTime, prices[0]) recordTxErr(&txerr, &err) //找出司机 tmps := []tmpOrderBean{} err = dbutil.DbQueryS2(tx, &tmps, "SELECT duid, price,oid FROM tb_order NATURAL JOIN tb_order_route WHERE drid =?", drid) recordTxErr(&txerr, &err) //司机减钱 if len(tmps) > 0 { _, err := dbutil.DbUpdate2(tx, "UPDATE tb_user SET balance=balance-? WHERE uid=?", prices[0], tmps[0].uid) recordTxErr(&txerr, &err) //创建交易记录 _, err = dbutil.DbInsert2(tx, "INSERT INTO tb_deal (uid,oid,dealTime,dealPrice) VALUES (?,?,?,?)", tmps[0].uid, tmps[0].oid, unixTime, -prices[0]) recordTxErr(&txerr, &err) } else { e := errors.New("can not find the driver") recordTxErr(&txerr, &e) } } if txerr != nil { tx.Rollback() return "", errors.New("database PassengerCancelRoute error") } else { tx.Commit() return "success", nil } }
func T2(db *sql.DB, t *testing.T) { dbutil.DbQuery(db, "select * from ttable where tid>?", 1) iid, _ := dbutil.DbInsert(db, "insert into ttable(tname,titem,tval,status,time) values('name','item','val','N',now())") dbutil.DbUpdate(db, "delete from ttable where tid=?", iid) if tx, err := db.Begin(); err == nil { dbutil.DbInsert2(tx, "selectt * from ttable where tid>?", 1) tx.Rollback() } if tx, err := db.Begin(); err == nil { dbutil.DbInsert2(tx, "insert into ttable(tname,titem,tval,status,time) values('name','item','val','N',now())") tx.Commit() } }
func InsertTx(tx *sql.Tx, o interface{}, tableName string) (int64, error) { dfs := parseToDbfield(o) s := bytes.Buffer{} s.WriteString("insert into ") s.WriteString(tableName) s.WriteString("(") s.WriteString(selectColumn(dfs)) s.WriteString(")values(") s.WriteString(placeholders(dfs)) s.WriteString(")") log.D("sql:%v", s.String()) log.D("args:%v", args(dfs)) return dbutil.DbInsert2(tx, s.String(), args(dfs)) }
func TestTx(t *testing.T) { G_Dn = "mysql" G_Dsn = TDbCon db, _ := sql.Open("DEM", test.TDbCon) tx, _ := db.Begin() // txi := tx.Txi() // txi2 := txi.(*STTx) fmt.Println(tx) iid, err := dbutil.DbInsert2(tx, "insert into ttable(tname,titem,tval,status,time) values('name','item','val','N',now())") if err != nil { t.Error(err.Error()) return } fmt.Println(iid) tx.Rollback() db.Close() // fmt.Println(tx.Commit()) }
func T(db *sql.DB, t *testing.T) { fmt.Println("----->") err := dbutil.DbExecF(db, "ttable.sql") if err != nil { t.Error(err.Error()) } fmt.Println("abbbb--->") res, err := dbutil.DbQuery(db, "select * from ttable where tid>?", 1) if err != nil { t.Error(err.Error()) return } if len(res) < 1 { t.Error("not data") return } if len(res[0]) < 1 { t.Error("data is empty") return } bys, err := json.Marshal(res) fmt.Println(string(bys)) // var mres []TSt err = dbutil.DbQueryS(db, &mres, "select * from ttable where tid>?", 1) if err != nil { t.Error(err.Error()) return } if len(mres) < 1 { t.Error("not data") return } fmt.Println("...", mres[0].T, util.Timestamp(mres[0].Time), util.Timestamp(time.Now())) fmt.Println(mres, mres[0].Add1) // ivs, err := dbutil.DbQueryInt(db, "select * from ttable where tid") if err != nil { t.Error(err.Error()) return } if len(ivs) < 1 { t.Error("not data") return } // svs, err := dbutil.DbQueryString(db, "select tname from ttable") if err != nil { t.Error(err.Error()) return } if len(svs) < 1 { t.Error("not data") return } // iid, err := dbutil.DbInsert(db, "insert into ttable(tname,titem,tval,status,time) values('name','item','val','N',now())") if err != nil { t.Error(err.Error()) return } fmt.Println(iid) // tx, _ := db.Begin() iid2, err := dbutil.DbInsert2(tx, "insert into ttable(tname,titem,tval,status,time) values('name','item','val','N',now())") if err != nil { t.Error(err.Error()) return } fmt.Println(iid2) tx.Commit() // erow, err := dbutil.DbUpdate(db, "delete from ttable where tid=?", iid) if err != nil { t.Error(err.Error()) return } fmt.Println(erow, "-----") // tx, _ = db.Begin() erow, err = dbutil.DbUpdate2(tx, "delete from ttable where tid=?", iid2) if err != nil { t.Error(err.Error()) return } fmt.Println(erow, "-----") tx.Commit() // _, err = dbutil.DbQuery(db, "selectt * from ttable where tid>?", 1) if err == nil { t.Error("not error") return } _, err = dbutil.DbQueryInt(db, "selectt * from ttable where tid>?", 1) if err == nil { t.Error("not error") return } _, err = dbutil.DbQueryString(db, "selectt * from ttable where tid>?", 1) if err == nil { t.Error("not error") return } _, err = dbutil.DbInsert(db, "selectt * from ttable where tid>?", 1) if err == nil { t.Error("not error") return } tx, _ = db.Begin() _, err = dbutil.DbInsert2(tx, "selectt * from ttable where tid>?", 1) if err == nil { t.Error("not error") return } tx.Rollback() // _, err = dbutil.DbUpdate(db, "selectt * from ttable where tid>?", 1) if err == nil { t.Error("not error") return } // tx, _ = db.Begin() _, err = dbutil.DbUpdate2(tx, "selectt * from ttable where tid>?", 1) if err == nil { t.Error("not error") return } tx.Rollback() // _, err = dbutil.DbQuery(db, "select * from ttable where tid>?", 1, 2) if err == nil { t.Error("not error") return } _, err = dbutil.DbQueryInt(db, "select * from ttable where tid>?", 1, 2) if err == nil { t.Error("not error") return } _, err = dbutil.DbQueryString(db, "select * from ttable where tid>?", 1, 2) if err == nil { t.Error("not error") return } _, err = dbutil.DbInsert(db, "select * from ttable where tid>?", 1, 2) if err == nil { t.Error("not error") return } // tx, _ = db.Begin() _, err = dbutil.DbInsert2(tx, "select * from ttable where tid>?", 1, 2) if err == nil { t.Error("not error") return } tx.Rollback() // _, err = dbutil.DbUpdate(db, "select * from ttable where tid>?", 1, 2) if err == nil { t.Error("not error") return } // tx, _ = db.Begin() _, err = dbutil.DbUpdate2(tx, "select * from ttable where tid>?", 1, 2) if err == nil { t.Error("not error") return } tx.Rollback() // err = dbutil.DbQueryS(nil, nil, "select * from ttable where tid>?", 1) if err == nil { t.Error("not error") return } dbutil.DbQueryInt(nil, "select * from ttable where tid>?", 1, 2) dbutil.DbQueryString(nil, "select * from ttable where tid>?", 1, 2) dbutil.DbInsert(nil, "select * from ttable where tid>?", 1, 2) dbutil.DbUpdate(nil, "select * from ttable where tid>?", 1, 2) dbutil.DbInsert2(nil, "select * from ttable where tid>?", 1, 2) dbutil.DbUpdate2(nil, "select * from ttable where tid>?", 1, 2) }
func PassengerJoinRoute(uid int64, startTime int64, sPlace string, ePlace string, sLat float64, sLng float64, eLat float64, eLng float64, sCity string, eCity string, drid int64, duid int64) (string, error) { //先判断用户是否存在,时间是否正确 err := IsUserExist(uid) if err != nil { return "", err } err = IsDriverExist(duid) if err != nil { return "", err } err = checkStartTime(startTime, uid) if err != nil { return "", err } //用drid找到司机的uid匹配 duids := []int64{} err = dbutil.DbQueryS(Db, &duids, "SELECT duid FROM tb_driver_route WHERE drid=?", drid) if err != nil || len(duids) == 0 { return "", errors.New("no such driver") } else { if duids[0] == duid { return "", errors.New("invalid driver !!!") } } //从数据库得到价钱 prices := []float64{} err = dbutil.DbQueryS(Db, &duids, "SELECT unitPrice*distance FROM tb_driver_route WHERE drid=?", drid) if err != nil || len(prices) == 0 { return "", errors.New("no such route") } price := prices[0] //不够钱提示 balance := []float64{} err = dbutil.DbQueryS(Db, &balance, "SELECT balance FROM tb_user WHERE uid=?", uid) if len(balance) == 0 { return "", errors.New("no such user") } else { if balance[0] < price { return "", errors.New("your balance is not enough, please charge first") } } var txerr error txerr = nil t := time.Now() unixTime := t.Unix() tx, err := Db.Begin() recordTxErr(&txerr, &err) //插入到route rid, err := dbutil.DbInsert2(tx, "INSERT INTO tb_route (startTime,sPlace,ePlace,sLat,sLng,eLat,eLng,sCity,eCity) VALUES (?,?,?,?,?,?,?,?,?)", startTime, sPlace, ePlace, sLat, sLng, eLat, eLng, sCity, eCity) recordTxErr(&txerr, &err) //插入到passenger route _, err = dbutil.DbInsert2(tx, "INSERT INTO tb_passenger_route (uid,rid,price) VALUES (?,?,?)", uid, rid, price) recordTxErr(&txerr, &err) //插入order,生成订单,得oid orderNo := fmt.Sprintf("%02d%2d%02d%4d%2d%2d\n", t.Month(), t.Minute(), t.Day(), t.Year(), t.Second(), t.Hour()) orderNo = orderNo + strconv.FormatInt(startTime, 10) + strconv.FormatInt(uid, 10) + strconv.FormatInt(drid, 10) orderComment := "join route: \n" + sPlace + " to " + ePlace oid, err := dbutil.DbInsert2(tx, "INSERT INTO tb_order (uid,price,orderTime,orderNo,comment) VALUES (?,?,?,?,?)", uid, price, unixTime, orderNo, orderComment) recordTxErr(&txerr, &err) //插入到route order _, err = dbutil.DbInsert2(tx, "INSERT INTO tb_order_route (uid,duid,drid,oid) VALUES (?,?,?,?)", uid, duid, drid, oid) recordTxErr(&txerr, &err) //扣除金额 _, err = dbutil.DbUpdate2(tx, "UPDATE tb_user SET balance=balance-? WHERE uid=?", price, uid) recordTxErr(&txerr, &err) //创建交易记录 _, err = dbutil.DbInsert2(tx, "INSERT INTO tb_deal (uid,oid,dealTime,dealPrice) VALUES (?,?,?,?)", uid, oid, unixTime, -price) recordTxErr(&txerr, &err) //司机余额增加 _, err = dbutil.DbUpdate2(tx, "UPDATE tb_user SET balance=balance+? WHERE uid=?", price, duid) recordTxErr(&txerr, &err) //创建交易记录 _, err = dbutil.DbInsert2(tx, "INSERT INTO tb_deal (uid,oid,dealTime,dealPrice) VALUES (?,?,?,?)", duid, oid, unixTime, price) recordTxErr(&txerr, &err) if txerr != nil { tx.Rollback() return "", errors.New("database passenger join error") } else { tx.Commit() return "success", nil } }