func dbFindCustomersByCond(cond string) ([]Customer, int) {
dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
if err != nil {
log.Fatal("%v", err)
}
defer dbHandler.Close()
sqlCond, limit, offset := buildSqlCond(cond)
log.Debug("get customers for %s", cond)
querySql := fmt.Sprintf("SELECT id, name, cover_photo, desc, phone, email FROM user WHERE type=1 AND %s LIMIT %d OFFSET %d ", sqlCond, limit, offset)
stmt, err := dbHandler.Prepare(querySql)
if err != nil {
log.Debug("querySql: %s", querySql)
log.Error("Prepare failed : %v", err)
return nil, http.StatusInternalServerError
}
defer stmt.Close()
rows, err := stmt.Query()
if err != nil {
log.Fatal("Query customers failed, something changed on db schema? : %v ", err)
return nil, http.StatusNotFound
}
defer rows.Close()
customers := make([]Customer, 0, limit)
for rows.Next() {
var customerId sql.NullInt64
var name, coverPhoto, desc, phone, email sql.NullString
rows.Scan(&customerId, &name, &coverPhoto, &desc, &phone, &email)
customers = append(customers, Customer{strconv.FormatInt(customerId.Int64, 10), name.String, coverPhoto.String, "", phone.String, email.String, nil})
}
return customers, http.StatusOK
}
func dbSearchCustomers(t string, p int) ([]Customer, int) {
dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
if err != nil {
log.Fatal("%v", err)
}
defer dbHandler.Close()
offset := customerPageLimit * (p - 1)
querySql := fmt.Sprintf("select id, name, cover_photo, phone, desc, email from user where type=1 and (name like '%%%s%%' or phone like '%%%s%%' or email like '%%%s%%') order by id limit %d offset %d", t, t, t, customerPageLimit, offset)
stmt, err := dbHandler.Prepare(querySql)
if err != nil {
log.Debug("querySql: %s", querySql)
log.Error("Prepare failed : %v", err)
return nil, http.StatusInternalServerError
}
defer stmt.Close()
rows, err := stmt.Query()
if err != nil {
log.Fatal("Query customers failed, something changed on db schema? : %v ", err)
return nil, http.StatusNotFound
}
defer rows.Close()
customers := make([]Customer, 0)
for rows.Next() {
var customerId sql.NullInt64
var name, coverPhoto, phone, desc, email sql.NullString
rows.Scan(&customerId, &name, &coverPhoto, &phone, &desc, &email)
customers = append(customers, Customer{strconv.FormatInt(customerId.Int64, 10), name.String, coverPhoto.String, desc.String, phone.String, email.String, nil})
}
return customers, http.StatusOK
}
func dbCreateCustomer(c *Customer) int {
log.Debug("try to create user %v", c)
dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
if err != nil {
log.Fatal("%v", err)
}
defer dbHandler.Close()
var userType = 1
if len(c.Email) == 0 {
userType = 2
}
stmt, err := dbHandler.Prepare("INSERT INTO user (type, name, email, cover_photo, phone, desc) VALUES (?,?,?,?,?,?)")
if err != nil {
log.Error("%v", err)
return http.StatusInternalServerError
}
defer stmt.Close()
_, err = stmt.Exec(userType, c.Name, c.Email, c.CoverPhoto, c.Phone, c.Desc)
if err != nil {
log.Error("%v", err)
return http.StatusBadRequest
}
return http.StatusOK
}
func dbSaveCustomer(c *Customer) int {
log.Debug("try to save user %v", c)
dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
if err != nil {
log.Fatal("%v", err)
}
defer dbHandler.Close()
var userType = 1
if len(c.Email) == 0 {
userType = 2
}
stmt, err := dbHandler.Prepare("UPDATE user SET type=?, name=?, email=?, cover_photo=?, phone=?, desc=? WHERE id=?")
if err != nil {
log.Error("%v", err)
return http.StatusInternalServerError
}
defer stmt.Close()
_, err = stmt.Exec(userType, c.Name, c.Email, c.CoverPhoto, c.Phone, c.Desc, c.Id)
if err != nil {
log.Error("%v", err)
return http.StatusBadRequest
}
return http.StatusOK
}
func dbVerifyRecover(magic string) (int, int64) {
dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
if err != nil {
log.Fatal("%v", err)
}
defer dbHandler.Close()
var getSql = "SELECT u.id FROM user u, user_recover_pass urp WHERE u.email=urp.email AND urp.magic=?"
var value sql.NullInt64
err = dbHandler.QueryRow(getSql, magic).Scan(&value)
if err != nil {
if err == sql.ErrNoRows {
log.Info("No magic found for %s", magic)
return http.StatusNotFound, 0
} else {
log.Error("DB query failed: %v", err)
return http.StatusInternalServerError, 0
}
}
var delSql = "DELETE from user_recover_pass WHERE magic=?"
_, err = dbHandler.Exec(delSql, magic)
if err != nil {
log.Error("SQL: %s", delSql)
return http.StatusInternalServerError, 0
}
return http.StatusOK, value.Int64
}
func buildSqlCond(cond string) (string, int, int) {
dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
if err != nil {
log.Fatal("%v", err)
}
defer dbHandler.Close()
var offset = 0
var limit = 100
conds := strings.Split(cond, "&")
if len(conds) == 0 || len(conds) > 10 {
return "id>0", limit, offset
}
var sqlString = make([]string, 0, 10)
for _, c := range conds {
v := strings.Split(c, "=")
if len(v) != 2 {
return "id>0", limit, offset
}
switch v[0] {
case "id":
sqlString = append(sqlString, "id="+v[1])
case "name":
sqlString = append(sqlString, fmt.Sprintf("name like '%%%s%%'", v[1]))
case "desc":
sqlString = append(sqlString, fmt.Sprintf("desc like '%%%s%%'", v[1]))
case "phone":
sqlString = append(sqlString, fmt.Sprintf("phone like '%%%s%%'", v[1]))
case "email":
sqlString = append(sqlString, fmt.Sprintf("email like '%%%s%%'", v[1]))
case "offset":
o, err := strconv.Atoi(v[1])
if err != nil {
offset = 0
} else {
offset = o
}
case "limit":
l, err := strconv.Atoi(v[1])
if err != nil {
limit = 100
} else {
limit = l
}
}
}
if len(sqlString) == 0 {
return "id>0", limit, offset
}
return strings.Join(sqlString, " AND "), limit, offset
}
func dbUpdateUserType(user *User, user_type int) int {
dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
if err != nil {
log.Fatal("%v", err)
}
defer dbHandler.Close()
var setSql = "UPDATE user set type=? WHERE id=? "
_, err = dbHandler.Exec(setSql, user_type, user.Id)
if err != nil {
log.Error("SQL: %s", setSql)
log.Error("DB exec failed : %v", err)
return http.StatusInternalServerError
}
return http.StatusOK
}
func dbInsertRecoverInfo(email, magic string) int {
dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
if err != nil {
log.Fatal("%v", err)
}
defer dbHandler.Close()
var setSql = "INSERT OR REPLACE INTO user_recover_pass (email, magic) VALUES (?,?) "
_, err = dbHandler.Exec(setSql, email, magic)
if err != nil {
log.Error("SQL: %s", setSql)
log.Error("DB exec failed, insert recover info, email: %s, magic %s, failed : %v", email, magic, err)
return http.StatusInternalServerError
}
return http.StatusOK
}
func dbSearchCustomersCount(t string) (int64, int) {
dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
if err != nil {
log.Fatal("%v", err)
}
defer dbHandler.Close()
querySql := fmt.Sprintf("select count(id) from user where type=1 and (name like '%%%s%%' or phone like '%%%s%%' or email like '%%%s%%') ", t, t, t)
var n sql.NullInt64
err = dbHandler.QueryRow(querySql).Scan(&n)
if err != nil {
log.Debug("sql : %s", querySql)
log.Error("DB query failed: %v", err)
return 0, http.StatusInternalServerError
}
return n.Int64, http.StatusOK
}
func dbFindCustomer(c *Customer) int {
log.Debug("get customer detail for %d", c.Id)
dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
if err != nil {
log.Fatal("%v", err)
}
defer dbHandler.Close()
querySql := "SELECT id, name, cover_photo, desc, phone, email FROM user WHERE type in (1,2) AND id=? "
var id sql.NullInt64
var name, coverPhoto, desc, phone, email sql.NullString
err = dbHandler.QueryRow(querySql, c.Id).Scan(&id, &name, &coverPhoto, &desc, &phone, &email)
if err != nil {
if err == sql.ErrNoRows {
log.Info("No customer found for %d", c.Id)
return http.StatusNotFound
} else {
log.Debug("sql : %s", querySql)
log.Error("DB query failed: %v", err)
return http.StatusInternalServerError
}
}
c.Name = name.String
c.CoverPhoto = coverPhoto.String
c.Desc = desc.String
c.Phone = phone.String
c.Email = email.String
queryLogSql := "SELECT operation_type, operation_detail, operation_time FROM user_log WHERE user_id=? ORDER BY id DESC LIMIT 100"
rows, err := dbHandler.Query(queryLogSql, c.Id)
defer rows.Close()
logs := make([]CustomerLog, 0, 100)
for rows.Next() {
var operation_type, operation_detail sql.NullString
var operation_time time.Time
rows.Scan(&operation_type, &operation_detail, &operation_time)
logs = append(logs, CustomerLog{c.Id, operation_type.String, operation_detail.String, operation_time.Format(timeLayout)})
}
c.Logs = logs
return http.StatusOK
}
func dbCreateVisitor(session_time string) int64 {
dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
if err != nil {
log.Fatal("%v", err)
}
defer dbHandler.Close()
var setSql = "INSERT INTO visitor (session_time) VALUES (?) "
r, err := dbHandler.Exec(setSql, session_time)
if err != nil {
log.Error("SQL: %s", setSql)
log.Error("DB exec failed, insert visiotr: %s, failed : %v", session_time, err)
return 0
}
id, _ := r.LastInsertId()
return id
}
func GetUserAddr(userid int64) *UserAddress {
dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
if err != nil {
log.Fatal("%v", err)
}
defer dbHandler.Close()
querySql := "SELECT id, receiver, address, city, province, postal, country, phone from user_address where is_default=1 and user_id=? "
var uaid sql.NullInt64
var receiver, address, city, province, postal, country, phone sql.NullString
err = dbHandler.QueryRow(querySql, userid).Scan(&uaid, &receiver, &address, &city, &province, &postal, &country, &phone)
if err != nil {
log.Error("Sql: %s", querySql)
log.Error("DB choose shipping failed, %v", err)
return nil
}
return &UserAddress{strconv.FormatInt(uaid.Int64, 10), receiver.String, address.String, city.String, province.String, postal.String, country.String, phone.String, 1}
}
func dbFindUser(user *User) int {
log.Debug("try to find user with id : %v", user.Id)
dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
if err != nil {
log.Fatal("%v", err)
}
defer dbHandler.Close()
if len(user.Id) == 0 {
return http.StatusNotFound
}
stmt, err := dbHandler.Prepare("SELECT type, name, email, phone, cover_photo FROM user WHERE id=? LIMIT 1")
if err != nil {
log.Error("%v", err)
return http.StatusInternalServerError
}
defer stmt.Close()
var name, email, phone, cover_photo sql.NullString
var user_type sql.NullInt64
err = stmt.QueryRow(user.Id).Scan(&user_type, &name, &email, &phone, &cover_photo)
if err != nil {
log.Error("%v", err)
if err == sql.ErrNoRows {
return http.StatusNotFound
} else {
return http.StatusInternalServerError
}
}
if !name.Valid {
return http.StatusNotFound
} else {
user.Type = user_type.Int64
user.Email = email.String
user.Name = name.String
user.Phone = phone.String
user.CoverPhoto = cover_photo.String
return http.StatusOK
}
}
func updateReviewboardOwner(req *http.Request, id string) {
log.Debug("Update Reviewboard Owner")
dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
if err != nil {
log.Fatal("%v", err)
}
defer dbHandler.Close()
b, vid := AuthVisitorHandler(req, nil)
if !b {
log.Debug("Auth Visitor failed")
return
}
var updateSql = "UPDATE reviewboard set customer_type=1, customer_id=? WHERE customer_type=2 AND customer_id=?"
log.Error("Sql: %s", updateSql)
_, err = dbHandler.Exec(updateSql, id, vid)
if err != nil {
log.Error("Sql: %s", updateSql)
log.Error("Update reviewboard owner failed: %v", err)
}
}
func setShipping(req *restful.Request, resp *restful.Response) {
dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
if err != nil {
log.Fatal("%v", err)
}
defer dbHandler.Close()
userId, err := strconv.ParseInt(req.Attribute("agsuserid").(string), 10, 64)
ua := new(UserAddress)
err = req.ReadEntity(&ua)
if err != nil {
resp.WriteError(http.StatusInternalServerError, err)
return
}
var setSql string
var r sql.Result
if len(ua.Id) != 0 {
//update
setSql = "UPDATE user_address SET receiver=?, address=?, city=?, province=?, postal=?, country=?, phone=?, is_default=? WHERE id=?"
r, err = dbHandler.Exec(setSql, ua.Name, ua.Address, ua.City, ua.Province, ua.Postal, ua.Country, ua.Phone, ua.IsDefault, ua.Id)
} else {
//insert
setSql = "INSERT INTO user_address (user_id, receiver, address, city, province, postal, country, phone, is_default) VALUES (?, ?,?, ?,?,?,?,?,?) "
r, err = dbHandler.Exec(setSql, userId, ua.Name, ua.Address, ua.City, ua.Province, ua.Postal, ua.Country, ua.Phone, ua.IsDefault)
}
if err != nil {
log.Error("SQL: %s", setSql)
resp.WriteError(http.StatusInternalServerError, err)
return
}
if len(ua.Id) == 0 {
id, _ := r.LastInsertId()
ua.Id = strconv.FormatInt(id, 10)
}
resp.WriteEntity(ua)
}
func dbDeleteUser(id string) int {
log.Debug("try to delete user id %v", id)
dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
if err != nil {
log.Fatal("%v", err)
}
defer dbHandler.Close()
stmt, err := dbHandler.Prepare("DELETE FROM user WHERE id=?")
if err != nil {
log.Error("%v", err)
return http.StatusInternalServerError
}
defer stmt.Close()
_, err = stmt.Exec(id)
if err != nil {
log.Error("%v", err)
return http.StatusBadRequest
}
return http.StatusOK
}
func dbCreateCustomerLog(c *CustomerLog) int {
log.Debug("try to create customer log %v", c)
dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
if err != nil {
log.Fatal("%v", err)
}
defer dbHandler.Close()
stmt, err := dbHandler.Prepare("INSERT INTO user_log (user_id, operation_type, operation_detail) VALUES (?,?,?)")
if err != nil {
log.Error("%v", err)
return http.StatusInternalServerError
}
defer stmt.Close()
_, err = stmt.Exec(c.CustomerId, c.OperationType, c.OperationDetail)
if err != nil {
log.Error("%v", err)
return http.StatusBadRequest
}
return http.StatusOK
}
func dbCheckUser(user *User) int {
log.Debug("try to find user with id : %v | %v", user.Email, user.Pass)
dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
if err != nil {
log.Fatal("%v", err)
}
defer dbHandler.Close()
stmt, err := dbHandler.Prepare("SELECT id, type, name, email, phone, cover_photo FROM user WHERE email=? AND pass=? LIMIT 1")
if err != nil {
log.Error("%v", err)
return http.StatusInternalServerError
}
defer stmt.Close()
var id, user_type sql.NullInt64
var name, email, phone, cover_photo sql.NullString
err = stmt.QueryRow(user.Email, user.Pass).Scan(&id, &user_type, &name, &email, &phone, &cover_photo)
if err != nil {
log.Error("%v", err)
if err == sql.ErrNoRows {
return http.StatusNotFound
} else {
return http.StatusInternalServerError
}
}
if !id.Valid {
return http.StatusNotFound
} else {
user.Id = strconv.FormatInt(id.Int64, 10)
user.Type = user_type.Int64
user.Name = name.String
user.Email = email.String
user.Phone = phone.String
user.CoverPhoto = cover_photo.String
return http.StatusOK
}
}
func dbUpdateUser(user *User) int {
log.Debug("try to update user %v", user)
dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
if err != nil {
log.Fatal("%v", err)
}
defer dbHandler.Close()
stmt, err := dbHandler.Prepare("UPDATE user SET name=?, phone=?, cover_photo=? WHERE id=? ")
if err != nil {
log.Error("%v", err)
return http.StatusInternalServerError
}
defer stmt.Close()
_, err = stmt.Exec(user.Name, user.Phone, user.CoverPhoto, user.Id)
if err != nil {
log.Error("%v", err)
return http.StatusBadRequest
}
return http.StatusOK
}
func dbCreateUser(user *User) int {
log.Debug("try to create user %v", user)
dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
if err != nil {
log.Fatal("%v", err)
}
defer dbHandler.Close()
stmt, err := dbHandler.Prepare("INSERT INTO user (type, name, email, pass) VALUES (1, ?,?,?)")
if err != nil {
log.Error("%v", err)
return http.StatusInternalServerError
}
defer stmt.Close()
_, err = stmt.Exec(user.Name, user.Email, user.Pass)
if err != nil {
log.Error("%v", err)
return http.StatusBadRequest
}
return http.StatusOK
}
func saveVisitorSesstionTime(id string, t string) int {
log.Debug("try to update user %s session time", id)
dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
if err != nil {
log.Fatal("%v", err)
}
defer dbHandler.Close()
stmt, err := dbHandler.Prepare("UPDATE visitor SET session_time=? WHERE id=? ")
if err != nil {
log.Error("prepare update visitor session time failed: %v", err)
return http.StatusInternalServerError
}
defer stmt.Close()
_, err = stmt.Exec(t, id)
if err != nil {
log.Error("execute update visitor session time failed: %v", err)
return http.StatusInternalServerError
}
return http.StatusOK
}
func dbGetShippingOptions(userid int64) ([]UserAddress, int) {
dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
if err != nil {
log.Fatal("%v", err)
}
defer dbHandler.Close()
var getSql = "SELECT id, receiver, address, city, province, postal, country, phone, is_default FROM user_address WHERE user_id=?"
rows, err := dbHandler.Query(getSql, userid)
if err != nil {
return nil, http.StatusInternalServerError
}
defer rows.Close()
uas := make([]UserAddress, 0, 2)
for rows.Next() {
var id, is_default sql.NullInt64
var name, address, city, province, postal, country, phone sql.NullString
rows.Scan(&id, &name, &address, &city, &province, &postal, &country, &phone, &is_default)
uas = append(uas, UserAddress{strconv.FormatInt(id.Int64, 10), name.String, address.String, city.String, province.String, postal.String, country.String, phone.String, is_default.Int64})
}
return uas, http.StatusOK
}
func dbEmailExist(email string) bool {
dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
if err != nil {
log.Fatal("%v", err)
}
defer dbHandler.Close()
if len(email) == 0 {
return false
}
var getSql = "SELECT id FROM user WHERE email=?"
var value sql.NullInt64
err = dbHandler.QueryRow(getSql, email).Scan(&value)
if err != nil {
if err == sql.ErrNoRows {
log.Info("No email found for %s", email)
return false
} else {
log.Error("DB query failed: %v", err)
return false
}
}
return true
}