Example #1
0
File: users.go Project: henrydo/ags
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
}
Example #2
0
File: users.go Project: henrydo/ags
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

}
Example #3
0
File: users.go Project: henrydo/ags
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
}
Example #4
0
File: users.go Project: henrydo/ags
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
}
Example #5
0
File: users.go Project: henrydo/ags
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
}
Example #6
0
File: users.go Project: henrydo/ags
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
}
Example #7
0
File: users.go Project: henrydo/ags
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
}
Example #8
0
File: users.go Project: henrydo/ags
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
}
Example #9
0
File: users.go Project: henrydo/ags
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
}
Example #10
0
File: users.go Project: henrydo/ags
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
}
Example #11
0
File: auth.go Project: henrydo/ags
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
}
Example #12
0
File: users.go Project: henrydo/ags
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}
}
Example #13
0
File: users.go Project: henrydo/ags
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
	}
}
Example #14
0
File: auth.go Project: henrydo/ags
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)
	}
}
Example #15
0
File: users.go Project: henrydo/ags
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)
}
Example #16
0
File: users.go Project: henrydo/ags
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
}
Example #17
0
File: users.go Project: henrydo/ags
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
}
Example #18
0
File: users.go Project: henrydo/ags
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
	}
}
Example #19
0
File: users.go Project: henrydo/ags
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
}
Example #20
0
File: users.go Project: henrydo/ags
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
}
Example #21
0
File: auth.go Project: henrydo/ags
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
}
Example #22
0
File: users.go Project: henrydo/ags
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

}
Example #23
0
File: users.go Project: henrydo/ags
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
}