Ejemplo n.º 1
0
func dbCreateArticle(article *Article) int {
	log.Debug("try to create article %v", article)

	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	stmt, err := dbHandler.Prepare("INSERT INTO article (title, navname, cover_photo, intro, content, create_by_user_id, last_modify_time) VALUES (?,?,?,?,?,?, datetime('now','localtime','utc'))")
	if err != nil {
		log.Error("%v", err)
		return http.StatusInternalServerError
	}
	defer stmt.Close()

	r, err := stmt.Exec(article.Title, article.NavName, article.CoverPhoto, article.Intro, article.Content, article.UserId)
	if err != nil {
		log.Error("%v", err)
		return http.StatusBadRequest
	}
	id, _ := r.LastInsertId()
	article.Id = strconv.FormatInt(id, 10)

	return http.StatusOK
}
Ejemplo n.º 2
0
func dbGetEnquireProducts(id string) []ReviewboardProduct {
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	querySql := "SELECT id, product_id, product_navname, product_name, cover_photo, price FROM enquire_product WHERE enquire_id=?"

	stmt, err := dbHandler.Prepare(querySql)
	if err != nil {
		log.Debug("querySql: %s", querySql)
		log.Error("Prepare failed: %v", err)
		return nil
	}
	defer stmt.Close()
	rows, err := stmt.Query(id)
	if err != nil {
		log.Error("Query enquire products failed, error : %v", err)
		return nil
	}
	defer rows.Close()

	ps := make([]ReviewboardProduct, 0)
	for rows.Next() {
		var id, product_id sql.NullInt64
		var product_name, product_navname, cover_photo sql.NullString
		var price sql.NullFloat64
		rows.Scan(&id, &product_id, &product_navname, &product_name, &cover_photo, &price)
		ps = append(ps, ReviewboardProduct{product_id.Int64, product_navname.String, product_name.String, cover_photo.String, price.Float64})
	}
	return ps
}
Ejemplo n.º 3
0
//usertype: 1:customer, 2:visitor
func dbGetReviewboardDetail(usertype int, userid int64) ([]ReviewboardProduct, int) {
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	querySql := "SELECT id, product_id, product_navname, product_name, cover_photo, price FROM reviewboard WHERE customer_type=? AND customer_id=? "

	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(usertype, userid)
	if err != nil {
		log.Error("Query reviewboard detail failed, error : %v", err)
		return nil, http.StatusNotFound
	}
	defer rows.Close()

	ps := make([]ReviewboardProduct, 0)
	for rows.Next() {
		var id, product_id sql.NullInt64
		var product_name, product_navname, cover_photo sql.NullString
		var price sql.NullFloat64
		rows.Scan(&id, &product_id, &product_navname, &product_name, &cover_photo, &price)
		ps = append(ps, ReviewboardProduct{product_id.Int64, product_navname.String, product_name.String, cover_photo.String, price.Float64})
	}
	return ps, http.StatusOK
}
Ejemplo n.º 4
0
func dbAddProduct(p *Product) int {
	log.Debug("try to add new product %v", p)
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	if p.Photos != nil && len(p.Photos) > 0 {
		p.CoverPhoto = p.Photos[0]
	}

	tx, err := dbHandler.Begin()
	insertSql := "INSERT INTO product (nav_name, status, en_name, cn_name, cover_photo, introduction, spec, price, discount) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
	r, err := dbHandler.Exec(insertSql, p.NavName, p.Status, p.EnName, p.CnName, p.CoverPhoto, p.Introduction, p.Spec, p.Price, p.Discount)
	if err != nil {
		tx.Rollback()
		log.Error("SQL: %s, err: %v", insertSql, err)
		return http.StatusInternalServerError
	}
	id, _ := r.LastInsertId()

	stmt, err := dbHandler.Prepare("INSERT INTO product_photo (product_id, url) VALUES (?,?)")
	if err != nil {
		tx.Rollback()
		log.Error("prepare failed : %v", err)
		return http.StatusInternalServerError
	}
	defer stmt.Close()

	for _, url := range p.Photos {
		_, err = stmt.Exec(id, url)
		if err != nil {
			tx.Rollback()
			log.Error("insert product_photo failed: %v", err)
			return http.StatusInternalServerError
		}
	}

	stmt2, err := dbHandler.Prepare("INSERT INTO product_saleurl (product_id, url) VALUES (?,?)")
	if err != nil {
		tx.Rollback()
		log.Error("prepare product_saleurl failed : %v", err)
		return http.StatusInternalServerError
	}
	defer stmt2.Close()

	for _, url := range p.SaleURL {
		_, err = stmt2.Exec(id, url)
		if err != nil {
			tx.Rollback()
			log.Error("insert product_photo failed: %v", err)
			return http.StatusInternalServerError
		}
	}
	tx.Commit()

	return http.StatusOK
}
Ejemplo n.º 5
0
Archivo: share.go Proyecto: henrydo/ags
func setSessionUploadFileUrls(session, file_urls 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 session_upload_files (session, urls) VALUES (?,?) "
	_, err = dbHandler.Exec(setSql, session, file_urls)
	if err != nil {
		log.Error("SQL: %s", setSql)
		log.Error("DB exec failed, insert session: %s, urls %s, failed : %v", session, file_urls, err)
		return http.StatusInternalServerError
	}
	return http.StatusOK
}
Ejemplo n.º 6
0
func dbGetAllArticles() ([]Article, int) {
	log.Debug("get all articles")
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	stmt, err := dbHandler.Prepare("SELECT a.id, a.title, a.navname,a.cover_photo, a.intro, a.content, a.create_by_user_id, u.name, a.create_time, a.last_modify_time from article a, user u WHERE a.create_by_user_id=u.id ORDER BY a.id DESC")
	if err != nil {
		log.Error("%v", err)
		return nil, http.StatusInternalServerError
	}
	defer stmt.Close()
	rows, err := stmt.Query()
	if err != nil {
		log.Fatal("%v", err)
		return nil, http.StatusInternalServerError
	}
	defer rows.Close()

	allarticles := make([]Article, 10)
	for rows.Next() {
		var title, navname, cover_photo, intro, content, username sql.NullString
		var articleid, userid sql.NullInt64
		var createtime, modifytime time.Time
		rows.Scan(&articleid, &title, &navname, &cover_photo, &intro, &content, &userid, &username, &createtime, &modifytime)

		allarticles = append(allarticles, Article{strconv.FormatInt(articleid.Int64, 10), title.String, navname.String, intro.String, content.String, userid.Int64, username.String, "", createtime.Format(timeLayout), modifytime.Format(timeLayout), cover_photo.String})
	}
	rows.Close()
	return allarticles, http.StatusOK
}
Ejemplo n.º 7
0
func dbSearchEnquires(t string, p int) ([]Enquire, int) {
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	offset := enquirePageLimit * (p - 1)
	querySql := fmt.Sprintf("select id, status, customer_id, customer_name, subject, message, followup, create_time, last_modify_time from enquires where subject like '%%%s%%' or customer_name like '%%%s%%' order by id desc limit %d offset %d", t, t, enquirePageLimit, 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 products failed, something changed on db schema? : %v ", err)
		return nil, http.StatusNotFound
	}
	defer rows.Close()

	es := make([]Enquire, 0)
	for rows.Next() {
		var enquire_id, status, customer_id sql.NullInt64
		var customer_name, subject, message, followup sql.NullString
		var create_time, last_modify_time time.Time
		rows.Scan(&enquire_id, &status, &customer_id, &customer_name, &subject, &message, &followup, &create_time, &last_modify_time)
		es = append(es, Enquire{auth.Encode(strconv.FormatInt(enquire_id.Int64, 10)), status.Int64, customer_id.Int64, customer_name.String, subject.String, message.String, 1, followup.String, nil, create_time.Format(timeLayout), last_modify_time.Format(timeLayout)})
	}
	return es, http.StatusOK
}
Ejemplo n.º 8
0
func dbSearchProducts(t string, p int) ([]Product, int) {
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	offset := productPageLimit * (p - 1)
	querySql := fmt.Sprintf("select id, nav_name, status, en_name, cover_photo, price from product WHERE en_name like '%%%s%%' order by id limit %d offset %d", t, productPageLimit, 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 products failed, something changed on db schema? : %v ", err)
		return nil, http.StatusNotFound
	}
	defer rows.Close()

	products := make([]Product, 0)
	for rows.Next() {
		var productId, status sql.NullInt64
		var navName, enName, coverPhoto sql.NullString
		var price sql.NullFloat64
		rows.Scan(&productId, &navName, &status, &enName, &coverPhoto, &price)
		products = append(products, Product{productId.Int64, navName.String, status.Int64, enName.String, "", coverPhoto.String, "", "", price.Float64, 0, nil, nil, nil, nil, nil, nil})
	}
	return products, http.StatusOK
}
Ejemplo n.º 9
0
func dbFollowupEnquire(e *Enquire) int {
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	updateSql := "UPDATE enquires SET employee_id=?, status=?, followup=? where id=?"
	_, err = dbHandler.Exec(updateSql, e.EmployeeId, e.Status, e.Followup, e.Id)
	if err != nil {
		log.Error("Sql: %s", updateSql)
		log.Error("DB followup enquire failed, %v", err)
		return http.StatusInternalServerError
	}
	return http.StatusOK
}
Ejemplo n.º 10
0
func dbGetAllProducts() ([]Product, int) {
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	var getSql = "SELECT count(*) FROM product ORDER BY id DESC "
	var product_count sql.NullInt64
	err = dbHandler.QueryRow(getSql).Scan(&product_count)
	if err != nil {
		if err == sql.ErrNoRows {
			log.Info("sql error")
			return nil, http.StatusInternalServerError
		} else {
			log.Error("DB query failed: %v", err)
			return nil, http.StatusInternalServerError
		}
	}
	if product_count.Int64 == 0 {
		log.Error("No product in db")
		return nil, http.StatusNotFound
	}

	stmt, err := dbHandler.Prepare("SELECT id, nav_name, status, en_name, cn_name, cover_photo, introduction, spec, price, discount FROM product ORDER BY id desc ")
	if err != nil {
		log.Error("Prepare all product failed : %v", err)
		return nil, http.StatusInternalServerError
	}
	defer stmt.Close()
	rows, err := stmt.Query()
	if err != nil {
		log.Fatal("Query all product failed, something changed on db schema? : %v ", err)
		return nil, http.StatusNotFound
	}
	defer rows.Close()

	products := make([]Product, 0, product_count.Int64)
	for rows.Next() {
		var productId, status sql.NullInt64
		var navName, enName, cnName, coverPhoto, introduction, spec sql.NullString
		var price, discount sql.NullFloat64
		rows.Scan(&productId, &navName, &status, &enName, &cnName, &coverPhoto, &introduction, &spec, &price, &discount)
		products = append(products, Product{productId.Int64, navName.String, status.Int64, enName.String, cnName.String, coverPhoto.String, introduction.String, spec.String, price.Float64, discount.Float64, nil, nil, nil, nil, nil, nil})
	}
	return products, http.StatusOK
}
Ejemplo n.º 11
0
func dbAddProductToReviewboard(usertype int, userid int64, p *ReviewboardProduct) int {

	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	insertSql := "INSERT INTO reviewboard (customer_type, customer_id, product_id, product_navname, product_name, cover_photo, price) VALUES (?, ?, ?, ?, ?,  ?, ?) "
	_, err = dbHandler.Exec(insertSql, usertype, userid, p.Id, p.NavName, p.Name, p.CoverPhoto, p.Price)
	if err != nil {
		log.Error("Sql: %s", insertSql)
		log.Error("DB Insert product to reviewboard failed, %v", err)
		return http.StatusInternalServerError
	}
	return http.StatusOK
}
Ejemplo n.º 12
0
func dbFindArticleByNavName(article *Article) int {
	log.Debug("try to find article with title : %v", article.NavName)
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	stmt, err := dbHandler.Prepare("SELECT a.id, a.title, a.cover_photo, a.intro, a.content, a.create_by_user_id, u.name, a.create_time, a.last_modify_time from article a, user u WHERE a.navname = ? AND a.create_by_user_id = u.id ")
	if err != nil {
		log.Error("%v", err)
		return http.StatusInternalServerError
	}
	defer stmt.Close()

	var title, cover_photo, intro, content, username sql.NullString
	var articleid, userid sql.NullInt64
	var createtime, modifytime time.Time
	err = stmt.QueryRow(article.NavName).Scan(&articleid, &title, &cover_photo, &intro, &content, &userid, &username, &createtime, &modifytime)
	if err != nil {
		log.Error("%v", err)
		if err == sql.ErrNoRows {
			return http.StatusNotFound
		} else {
			return http.StatusInternalServerError
		}
	}

	if !title.Valid {
		return http.StatusNotFound
	} else {
		article.Id = strconv.FormatInt(articleid.Int64, 10)
		article.CoverPhoto = cover_photo.String
		article.Title = title.String
		article.Content = content.String
		article.Intro = intro.String
		article.UserId = userid.Int64
		article.UserName = username.String
		article.CreateTime = createtime.Format(timeLayout)
		article.ModifyTime = modifytime.Format(timeLayout)
		return http.StatusOK
	}
}
Ejemplo n.º 13
0
func dbDeleteArticle(id string) int {
	log.Debug("try to delete article 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 article 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
}
Ejemplo n.º 14
0
func dbUpdateArticle(article *Article) int {
	log.Debug("try to update article %v", article)

	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()
	stmt, err := dbHandler.Prepare("UPDATE article SET cover_photo=?, title=?, content=?, last_modify_time=datetime('now','localtime','utc') WHERE id=?")
	if err != nil {
		log.Error("%v", err)
		return http.StatusInternalServerError
	}
	defer stmt.Close()

	_, err = stmt.Exec(article.CoverPhoto, article.Title, article.Content, article.Id)
	if err != nil {
		log.Error("%v", err)
		return http.StatusBadRequest
	}
	return http.StatusOK
}
Ejemplo n.º 15
0
func dbGetEnquire(e *Enquire, userId int64) int {
	log.Debug("get enquire detail for %s", e.Id)
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	querySql := "SELECT id, status, customer_id, customer_name,  subject, message, employee_id, followup,  create_time, last_modify_time FROM enquires WHERE id=?"
	var enquire_id, status, customer_id, employee_id sql.NullInt64
	var customer_name, subject, message, followup sql.NullString
	var create_time, last_modify_time time.Time
	err = dbHandler.QueryRow(querySql, e.Id).Scan(&enquire_id, &status, &customer_id, &customer_name, &subject, &message, &employee_id, &followup, &create_time, &last_modify_time)
	if err != nil {
		if err == sql.ErrNoRows {
			log.Info("No enquire found for %s", e.Id)
			return http.StatusNotFound
		} else {
			log.Debug("sql : %s", querySql)
			log.Error("DB query failed: %v", err)
			return http.StatusInternalServerError
		}
	}
	if userId != customer_id.Int64 {
		u := users.DbFindUser(strconv.FormatInt(userId, 10))
		if u == nil || (u.Type == 1 || u.Type == 2) {
			return http.StatusForbidden
		}
	}

	if !status.Valid {
		return http.StatusNotFound
	} else {
		e.Products = dbGetEnquireProducts(e.Id)
		e.Status = status.Int64
		e.CustomerId = customer_id.Int64
		e.CustomerName = customer_name.String
		e.Subject = subject.String
		e.Message = message.String
		e.EmployeeId = employee_id.Int64
		e.Followup = followup.String
		e.CreateTime = create_time.Format(timeLayout)
		e.ModifyTime = last_modify_time.Format(timeLayout)

		return http.StatusOK
	}
}
Ejemplo n.º 16
0
func dbSearchEnquiresCount(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 enquires where subject like '%%%s%%' or customer_name like '%%%s%%' ", 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
}
Ejemplo n.º 17
0
func dbGenDataSaleAmount(reportData *ReportData) (*ReportData, int) {
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	var querySql string
	switch reportData.Timeframe {
	case "0":
		querySql = "SELECT sum(paid_amount), date(create_time) from orders WHERE status>0 AND  create_time >= (SELECT date(julianday(date('now'))-7)) AND create_time <= (SELECT date(julianday(date('now')))) group by date(create_time)"
	case "1":
		querySql = "SELECT sum(paid_amount), date(create_time) from orders WHERE status>0 AND create_time >= (SELECT date(julianday(date('now'))-30)) AND create_time <= (SELECT date(julianday(date('now')))) group by date(create_time)"
	case "2":
		querySql = "SELECT sum(paid_amount), date(create_time) from orders WHERE status>0 AND create_time >= (SELECT date(julianday(date('now'))-180)) AND create_time <= (SELECT date(julianday(date('now')))) group by date(create_time)"
	case "3":
		querySql = "SELECT sum(paid_amount), date(create_time) from orders WHERE status>0 group by date(create_time)"

	}

	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 orders failed, something changed on db schema? : %v ", err)
		return nil, http.StatusNotFound
	}
	defer rows.Close()

	for rows.Next() {
		//var id_count sql.NullInt64
		var saleamount sql.NullFloat64
		var date_day sql.NullString
		rows.Scan(&saleamount, &date_day)
		reportData.Xvalues = append(reportData.Xvalues, date_day.String)
		reportData.Yvalues = append(reportData.Yvalues, saleamount.Float64)
	}

	return reportData, http.StatusOK
}
Ejemplo n.º 18
0
func dbGetPageArticles(pagenumber int64) ([]Article, int) {
	log.Debug("get page articles for %d", pagenumber)

	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	pageArticlesLimit, err := strconv.ParseInt(config.GetValue("DealsPerPage"), 10, 64)
	if err != nil {
		return nil, http.StatusInternalServerError
	}
	if pagenumber <= 0 {
		return nil, http.StatusBadRequest
	}
	offset := (pagenumber - 1) * pageArticlesLimit

	stmt, err := dbHandler.Prepare("SELECT a.id, a.title, a.navname, a.cover_photo, a.intro,  a.create_by_user_id, u.name, a.create_time, a.last_modify_time from article a, user u WHERE a.create_by_user_id=u.id ORDER BY a.id DESC  limit ? offset ?")
	if err != nil {
		log.Error("%v", err)
		return nil, http.StatusInternalServerError
	}
	defer stmt.Close()
	rows, err := stmt.Query(pageArticlesLimit, offset)
	if err != nil {
		log.Fatal("%v", err)
		return nil, http.StatusInternalServerError
	}
	defer rows.Close()

	allarticles := make([]Article, 0)
	for rows.Next() {
		var title, navname, cover_photo, intro, username sql.NullString
		var articleid, userid sql.NullInt64
		var createtime, modifytime time.Time
		rows.Scan(&articleid, &title, &navname, &cover_photo, &intro, &userid, &username, &createtime, &modifytime)

		allarticles = append(allarticles, Article{strconv.FormatInt(articleid.Int64, 10), title.String, navname.String, intro.String, "", userid.Int64, username.String, "", createtime.Format(timeLayout), modifytime.Format(timeLayout), cover_photo.String})
	}
	rows.Close()
	return allarticles, http.StatusOK
}
Ejemplo n.º 19
0
func dbGetPageDeals(pagenumber int64) ([]Product, int) {
	log.Debug("get page deals for %d", pagenumber)
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	pageDealsLimit, err := strconv.ParseInt(config.GetValue("DealsPerPage"), 10, 64)
	if err != nil {
		return nil, http.StatusInternalServerError
	}
	if pagenumber <= 0 {
		return nil, http.StatusBadRequest
	}

	//show latest deals only
	//offset := (pagenumber - 1) * pageDealsLimit
	offset := 0
	stmt, err := dbHandler.Prepare("SELECT id, nav_name, status, en_name, cn_name, cover_photo, price, discount FROM product WHERE status!=0 ORDER BY id desc limit ? offset ?")
	if err != nil {
		log.Error("Prepare to get page deal failed : %v", err)
		return nil, http.StatusInternalServerError
	}
	defer stmt.Close()
	rows, err := stmt.Query(pageDealsLimit, offset)
	if err != nil {
		log.Fatal("Query page deals failed: %v ", err)
		return nil, http.StatusNotFound
	}
	defer rows.Close()

	products := make([]Product, 0)
	for rows.Next() {
		var productId, status sql.NullInt64
		var navName, enName, cnName, coverPhoto sql.NullString
		var price, discount sql.NullFloat64
		rows.Scan(&productId, &navName, &status, &enName, &cnName, &coverPhoto, &price, &discount)
		products = append(products, Product{productId.Int64, navName.String, status.Int64, enName.String, cnName.String, coverPhoto.String, "", "", price.Float64, discount.Float64, nil, nil, nil, nil, nil, nil})
	}
	return products, http.StatusOK
}
Ejemplo n.º 20
0
func dbGetEnquiresCountByCond(cond string) (int64, int) {
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	sqlCond, _, _ := buildSqlCond(cond)

	log.Debug("get enquires for %s", cond)
	querySql := "SELECT count(id) FROM enquires WHERE ? "
	var escount sql.NullInt64
	err = dbHandler.QueryRow(querySql, sqlCond).Scan(&escount)
	if err != nil {
		log.Debug("sql : %s", querySql)
		log.Error("DB query failed: %v", err)
		return 0, http.StatusInternalServerError
	}
	return escount.Int64, http.StatusOK
}
Ejemplo n.º 21
0
Archivo: share.go Proyecto: henrydo/ags
func getSessionUploadFileUrls(session string) (int, string) {
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	var getSql = "SELECT urls FROM session_upload_files WHERE session=?"
	var value sql.NullString
	err = dbHandler.QueryRow(getSql, session).Scan(&value)
	if err != nil {
		if err == sql.ErrNoRows {
			log.Info("No value found for %s", session)
			return http.StatusNotFound, ""
		} else {
			log.Error("DB query failed: %v", err)
			return http.StatusInternalServerError, ""
		}
	}
	return http.StatusOK, value.String
}
Ejemplo n.º 22
0
func FindProductNames(id string) (string, string) {
	log.Debug("find prodcut names for id %s", id)
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	querySql := "SELECT en_name, nav_name FROM product WHERE id=?"
	var eName, navName sql.NullString

	err = dbHandler.QueryRow(querySql, id).Scan(&eName, &navName)
	if err != nil {
		if err == sql.ErrNoRows {
			log.Info("No product found for %s", id)
			return "", ""
		} else {
			log.Error("DB query failed: %v", err)
			return "", ""
		}
	}
	return eName.String, navName.String
}
Ejemplo n.º 23
0
func FindProductName(id string) string {
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	querySql := "SELECT cn_name FROM product WHERE id=? "
	var cnName sql.NullString

	err = dbHandler.QueryRow(querySql, id).Scan(&cnName)
	if err != nil {
		if err == sql.ErrNoRows {
			log.Info("No product found for %s", id)
			return ""
		} else {
			log.Error("DB query failed: %v", err)
			return ""
		}
	}
	return cnName.String

}
Ejemplo n.º 24
0
func dbFindEnquiresByCond(cond string) ([]Enquire, 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 enquires for %s, %d, %d", sqlCond, limit, offset)
	querySql := fmt.Sprintf("SELECT id, status, customer_id, customer_name, subject, message, employee_id, followup, create_time, last_modify_time FROM enquires WHERE %s ORDER BY id DESC 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 enquire failed, something changed on db schema? : %v ", err)
		return nil, http.StatusNotFound
	}
	defer rows.Close()

	es := make([]Enquire, 0)
	for rows.Next() {
		var enquire_id, status, customer_id, employee_id sql.NullInt64
		var customer_name, subject, message, followup sql.NullString
		var create_time, last_modify_time time.Time
		rows.Scan(&enquire_id, &status, &customer_id, &customer_name, &subject, &message, &employee_id, &followup, &create_time, &last_modify_time)
		es = append(es, Enquire{auth.Encode(strconv.FormatInt(enquire_id.Int64, 10)), status.Int64, customer_id.Int64, customer_name.String, subject.String, message.String, employee_id.Int64, followup.String, nil, create_time.Format(timeLayout), last_modify_time.Format(timeLayout)})
	}
	return es, http.StatusOK
}
Ejemplo n.º 25
0
func dbFindProductByNavName(p *Product) int {
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	navname := p.NavName
	log.Debug("get product detail for %s", navname)
	querySql := "SELECT id, nav_name, status, en_name, cn_name, cover_photo, introduction, spec, price, discount FROM product WHERE nav_name=? "
	var productId, status sql.NullInt64
	var navName, enName, cnName, coverPhoto, introduction, spec sql.NullString
	var price, discount sql.NullFloat64
	err = dbHandler.QueryRow(querySql, navname).Scan(&productId, &navName, &status, &enName, &cnName, &coverPhoto, &introduction, &spec, &price, &discount)
	if err != nil {
		if err == sql.ErrNoRows {
			log.Info("No product found for %s", navname)
			return http.StatusNotFound
		} else {
			log.Error("DB query failed: %v", err)
			return http.StatusInternalServerError
		}
	}
	p.Id = productId.Int64
	p.NavName = navName.String
	p.Status = status.Int64
	p.EnName = enName.String
	p.CnName = cnName.String
	p.CoverPhoto = coverPhoto.String
	p.Introduction = introduction.String
	p.Spec = spec.String
	p.Price = price.Float64
	p.Discount = discount.Float64

	queryPhotoSql := "SELECT url FROM product_photo WHERE product_id=? ORDER BY id DESC LIMIT 100"
	rows, err := dbHandler.Query(queryPhotoSql, p.Id)
	defer rows.Close()

	ps := make([]string, 0, 100)
	for rows.Next() {
		var url sql.NullString
		rows.Scan(&url)
		if len(url.String) > 0 {
			ps = append(ps, url.String)
		}
	}
	p.Photos = ps

	querySaleUrlSql := "SELECT url FROM product_saleurl WHERE product_id=? ORDER BY id DESC LIMIT 100"
	surows, err := dbHandler.Query(querySaleUrlSql, p.Id)
	defer surows.Close()

	su := make([]string, 0, 100)
	for surows.Next() {
		var url sql.NullString
		surows.Scan(&url)
		if len(url.String) > 0 {
			su = append(su, url.String)
		}
	}
	p.SaleURL = su

	return http.StatusOK
}
Ejemplo n.º 26
0
func dbSaveProduct(p *Product) int {
	log.Debug("try to save product %v", p)
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	if p.Photos != nil && len(p.Photos) > 0 {
		p.CoverPhoto = p.Photos[0]
	}

	tx, err := dbHandler.Begin()
	pSql := "UPDATE product SET status=?, en_name=?, cn_name=?, cover_photo=?, introduction=?, spec=?, price=?, discount=? WHERE nav_name=?"
	_, err = dbHandler.Exec(pSql, p.Status, p.EnName, p.CnName, p.CoverPhoto, p.Introduction, p.Spec, p.Price, p.Discount, p.NavName)
	if err != nil {
		tx.Rollback()
		log.Error("SQL: %s, err: %v", pSql, err)
		return http.StatusInternalServerError
	}

	dpSql := "DELETE FROM product_photo where product_id=?"
	_, err = dbHandler.Exec(dpSql, p.Id)
	if err != nil {
		tx.Rollback()
		log.Error("SQL: %s, err: %v", dpSql, err)
		return http.StatusInternalServerError
	}

	stmt, err := dbHandler.Prepare("INSERT INTO product_photo (product_id, url) VALUES (?,?)")
	if err != nil {
		tx.Rollback()
		log.Error("prepare failed : %v", err)
		return http.StatusInternalServerError
	}
	defer stmt.Close()

	for _, url := range p.Photos {
		_, err = stmt.Exec(p.Id, url)
		if err != nil {
			tx.Rollback()
			log.Error("insert product_photo failed: %v", err)
			return http.StatusInternalServerError
		}
	}

	dpSql = "DELETE FROM product_saleurl where product_id=?"
	_, err = dbHandler.Exec(dpSql, p.Id)
	if err != nil {
		tx.Rollback()
		log.Error("SQL: %s, err: %v", dpSql, err)
		return http.StatusInternalServerError
	}

	stmt2, err := dbHandler.Prepare("INSERT INTO product_saleurl (product_id, url) VALUES (?,?)")
	if err != nil {
		tx.Rollback()
		log.Error("prepare product_saleurl failed : %v", err)
		return http.StatusInternalServerError
	}
	defer stmt2.Close()

	for _, url := range p.SaleURL {
		_, err = stmt2.Exec(p.Id, url)
		if err != nil {
			tx.Rollback()
			log.Error("insert product_photo failed: %v", err)
			return http.StatusInternalServerError
		}
	}
	tx.Commit()

	return http.StatusOK
}
Ejemplo n.º 27
0
func dbAddEnquire(e *Enquire) int {
	log.Debug("try to add enquire %v", e)
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	tx, err := dbHandler.Begin()

	currTime := time.Now()
	stmt, err := dbHandler.Prepare("INSERT INTO enquires(status, customer_id, customer_name, subject, message, employee_id, followup, create_time, last_modify_time) VALUES (0, ?, ?, ?, ?, ?, ?, ?,?)")
	if err != nil {
		log.Debug("stmt is %v", stmt)
		log.Error("err: %v", err)
		tx.Rollback()
		return http.StatusInternalServerError
	}
	defer stmt.Close()

	var name = users.FindUserName(strconv.FormatInt(e.CustomerId, 10))
	r, err := stmt.Exec(e.CustomerId, name, e.Subject, e.Message, 0, "", currTime, currTime)
	if err != nil {
		log.Error("%v", err)
		tx.Rollback()
		return http.StatusBadRequest
	}
	id, _ := r.LastInsertId()
	e.Id = strconv.FormatInt(id, 10)

	pStmt, err := dbHandler.Prepare("INSERT INTO enquire_product (enquire_id, user_id, product_id, product_navname, product_name, cover_photo, price) VALUES (?,?,?,?,?,?,?)")
	if err != nil {
		tx.Rollback()
		log.Error("Prepare insert enquire_product failed: %v", err)
		return http.StatusInternalServerError
	}
	defer pStmt.Close()

	for _, op := range e.Products {
		ename, navname := products.FindProductNames(strconv.FormatInt(op.Id, 10))

		_, err = pStmt.Exec(id, e.CustomerId, op.Id, navname, ename, op.CoverPhoto, op.Price)
		if err != nil {
			tx.Rollback()
			log.Error("INSERT enquire product failed: %v", err)
			return http.StatusInternalServerError
		}

	}

	clearCartSql := "DELETE FROM reviewboard WHERE customer_id=? "
	_, err = dbHandler.Exec(clearCartSql, e.CustomerId)
	if err != nil {
		log.Error("Sql: %s", clearCartSql)
		tx.Rollback()
		return http.StatusInternalServerError
	}

	tx.Commit()
	return http.StatusOK

}