func checkStartTime(startTime int64, driverUid int64) error {
startTimes := []int64{}
err := dbutil.DbQueryS(Db, &startTimes, "SELECT startTime FROM tb_driver_route NATURAL JOIN tb_route WHERE duid=?", driverUid)
PanicErr(err)
for _, t := range startTimes {
if startTime-t < 1800 || t-startTime < 1800 {
return errors.New("invalid startTime")
}
}
return nil
}
func GetPassengerOrderList(uid int64, page int64, pageCount int64) ([]bean.PassengerOrderResponse, error) {
response := []bean.PassengerOrderResponse{}
err := IsUserExist(uid)
if err != nil {
return response, err
}
err = dbutil.DbQueryS(Db, &response,
"SELECT oid,price,orderTime,orderNo,drid,isEnable,duid, userName,head,mobile,gender "+
"FROM (tb_order NATURAL JOIN tb_order_route) AS t JOIN tb_user ON t.duid = tb_user.uid "+
"WHERE t.duid = ? LIMIT ?,?",
uid, (page-1)*pageCount, pageCount)
return response, err
}
func IsDriverExist(uid int64) error {
params := []bool{}
err := dbutil.DbQueryS(Db, ¶ms, "SELECT isDriver FROM tb_user WHERE uid=? and isDriver=true", uid)
if err != nil {
return err
} else {
if len(params) > 0 {
return nil
} else {
return errors.New("no such user")
}
}
}
func findUserByMobile(mobile string) (bean.User, error) {
user := bean.User{}
slice := []bean.User{}
err := dbutil.DbQueryS(Db, &slice, "SELECT * FROM tb_user WHERE mobile=?", mobile)
PanicErr(err)
if err != nil {
return user, err
} else {
if len(slice) > 0 {
return slice[0], nil
} else {
return user, errors.New("user is not existed")
}
}
}
func FindUserById(uid int64) (bean.User, error) {
user := bean.User{}
slice := []bean.User{}
err := dbutil.DbQueryS(Db, &slice, "SELECT * FROM tb_user WHERE uid=?", uid)
PanicErr(err)
if err != nil {
return user, err
} else {
if len(slice) > 0 {
return slice[0], nil
} else {
return user, errors.New("user is not existed")
}
}
}
//司机提现限制小于余额-冻结金额，订单未完成，金额冻结
func WithDrawBalance(uid int64, money float64) (string, error) {
//检查用户是否存在
err := IsUserExist(uid)
if err != nil {
return "", err
}
var txerr error
txerr = nil
t := time.Now()
unixTime := t.Unix()
//查出所有进行的订单的总额
priceSlice := []float64{}
err = dbutil.DbQueryS(Db, &priceSlice,
"SELECT sum(price) FROM tb_order JOIN tb_order_route ON tb_order.oid = tb_order_route.oid WHERE tb_order.uid=? and tb_order_route.duid !=?, orderTime>?",
uid, uid, unixTime)
if err != nil || len(priceSlice) == 0 {
return "", err
}
icePrice := priceSlice[0]
tx, err := Db.Begin()
recordTxErr(&txerr, &err)
slice := []float64{}
err = dbutil.DbQueryS2(tx, &slice, "SELECT balance FROM tb_user WHERE uid=?", uid)
recordTxErr(&txerr, &err)
if len(slice) > 0 {
if slice[0]-icePrice < money {
return "no enough money", errors.New("no enough money to withdraw or some order had not finished")
} else {
_, err := dbutil.DbUpdate2(tx, "UPDATE tb_user SET balance=balance-? WHERE uid=?", money, uid)
recordTxErr(&txerr, &err)
if txerr != nil {
tx.Rollback()
return "", txerr
} else {
tx.Commit()
return "success", nil
}
}
} else {
tx.Rollback()
return "", errors.New("WithDrawBalance error")
}
}
func FindDriverRoute(uid int64, startTime int64, sLat float64,
sLng float64, eLat float64, eLng float64, sCity string, eCity string) (interface{}, error) {
//先判断用户是否存在，时间是否正确
err := IsUserExist(uid)
if err != nil {
return "", err
}
//匹配附近的司机路线
routes := []bean.FindDriverRoute{}
err = dbutil.DbQueryS(Db, &routes,
"SELECT startTime,sPlace,ePlace,sLat,sLng,eLat,eLng,sCity,eCity,price,drid,duid "+
"FROM tb_route NATURAL JOIN tb_driver_route WHERE "+
"sCity=? and eCity=? and ABS(sLat-?)<=? and ABS(sLng-?)<=? and ABS(eLat-?)<=? and ABS(eLng-?)<=? and ABS(startTime-?)<1800 and isEnable=true",
sCity, eCity, sLat, latValue, sLng, lngValue, eLat, latValue, eLng, lngValue, startTime)
if err == nil {
return routes, nil
} else {
return "", err
}
}
func ListData() error {
var ts []TSt
err := dbutil.DbQueryS(DbCon(), &ts, "select * from ttable where tid>?", 1)
if err != nil {
log.D("Err:%v", err.Error())
return err
}
if len(ts) < 1 {
log.D("Err:%v", "NOT DATA")
return errors.New("NOT DATA")
}
iid, err := dbutil.DbInsert(DbCon(), "insert into ttable(tname,titem,tval,status,time) values('name','item','val','N',now())")
if err != nil {
log.D("Err:%v", err.Error())
return err
}
_, err = dbutil.DbUpdate(DbCon(), "delete from ttable where tid=?", iid)
if err != nil {
log.D("Err:%v", err.Error())
return err
}
return nil
}
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 Method() ([]float64, error) {
balance := []float64{}
err := dbutil.DbQueryS(Db, &balance, "SELECT balance FROM tb_user WHERE uid=1")
return balance, err
}
func DbQueryS(res interface{}, query string, args ...interface{}) error {
return dbutil.DbQueryS(DbConn(), res, query, args...)
}
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
}
}