func addProductToReviewboard(req *restful.Request, resp *restful.Response) {
log.Debug("Try to add product to reviewboard")
p := new(ReviewboardProduct)
err := req.ReadEntity(&p)
if err != nil {
resp.WriteErrorString(http.StatusBadRequest, http.StatusText(http.StatusBadRequest))
return
}
var id int64
var ret int
b, userid := auth.AuthHandler(req.Request, resp.ResponseWriter)
if !b {
log.Debug("This is a visitor")
b, visitorid := auth.AuthVisitorHandler(req.Request, resp.ResponseWriter)
if !b {
id = auth.AddVisitorCookie(req.Request, resp.ResponseWriter)
} else {
id, _ = strconv.ParseInt(visitorid, 10, 64)
}
ret = dbAddProductToReviewboard(2, id, p)
} else {
id, err := strconv.ParseInt(userid, 10, 64)
if err != nil {
ret = http.StatusInternalServerError
} else {
ret = dbAddProductToReviewboard(1, id, p)
}
}
if ret == http.StatusOK {
resp.WriteHeader(http.StatusOK)
} else {
resp.WriteErrorString(ret, http.StatusText(ret))
}
}
func getReviewboardDetail(req *restful.Request, resp *restful.Response) {
log.Debug("Try to get reviewboard detail")
b, userid := auth.AuthHandler(req.Request, resp.ResponseWriter)
var ret int
var ps []ReviewboardProduct
c := new(Reviewboard)
if !b {
log.Debug("This is a visitor")
ps, ret = getVisitorReviewboardDetail(req, resp)
c.Identity = "Visitor"
c.Products = ps
} else {
id, err := strconv.ParseInt(userid, 10, 64)
if err != nil {
ret = http.StatusInternalServerError
} else {
ps, ret = dbGetReviewboardDetail(1, id)
c.Identity = "Customer"
c.Products = ps
}
}
if ret == http.StatusOK {
resp.WriteEntity(c)
} else {
resp.WriteErrorString(ret, http.StatusText(ret))
}
}
func getAllProducts(req *restful.Request, resp *restful.Response) {
log.Debug("get all products")
allProducts, ret := dbGetAllProducts()
if ret == http.StatusOK {
log.Debug("write all products info")
resp.WriteEntity(allProducts)
} else {
resp.WriteErrorString(ret, http.StatusText(ret))
}
}
func getVisitorReviewboardDetail(req *restful.Request, resp *restful.Response) ([]ReviewboardProduct, int) {
b, visitorid := auth.AuthVisitorHandler(req.Request, resp.ResponseWriter)
if !b {
log.Debug("Add a new visitor cookie")
auth.AddVisitorCookie(req.Request, resp.ResponseWriter)
return nil, http.StatusNotFound
} else {
log.Debug("Get detail for an old visitor, %d", visitorid)
id, err := strconv.ParseInt(visitorid, 10, 64)
if err != nil {
return nil, http.StatusNotFound
}
return dbGetReviewboardDetail(2, id)
}
}
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
}
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
}
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
}
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
}
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
}
//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
}
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
}
}
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
}
func getTotalPageNumber(req *restful.Request, resp *restful.Response) {
pageNumber, ret := dbGetTotalPageNumber()
if ret == http.StatusOK {
log.Debug("pageNumber is %f", pageNumber)
resp.WriteEntity(pageNumber)
} else {
resp.WriteErrorString(ret, http.StatusText(ret))
}
}
func genDataByCond(req *restful.Request, resp *restful.Response) {
log.Debug("try to gen report with cond : %s", req.PathParameter("cond"))
cond := req.PathParameter("cond")
reportData, ret := dbGenDataByCond(cond)
if ret == http.StatusOK {
resp.WriteEntity(reportData)
} else {
resp.WriteErrorString(ret, http.StatusText(ret))
}
}
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
}
func findEnquiresByCond(req *restful.Request, resp *restful.Response) {
log.Debug("try to find enquires with cond : %s", req.PathParameter("cond"))
cond := req.PathParameter("cond")
es, ret := dbFindEnquiresByCond(cond)
if ret == http.StatusOK {
resp.WriteEntity(es)
} else {
resp.WriteErrorString(ret, http.StatusText(ret))
}
}
func findProductByNavName(req *restful.Request, resp *restful.Response) {
log.Debug("try to find product with nav name : %s", req.PathParameter("navname"))
product := new(Product)
product.NavName = req.PathParameter("navname")
ret := dbFindProductByNavName(product)
if ret == http.StatusOK {
resp.WriteEntity(product)
} else {
resp.WriteErrorString(ret, http.StatusText(ret))
}
}
func getEnquiresCountByCond(req *restful.Request, resp *restful.Response) {
log.Debug("try to get enquires count with cond : %s", req.PathParameter("cond"))
cond := req.PathParameter("cond")
escount, ret := dbGetEnquiresCountByCond(cond)
if ret == http.StatusOK {
resp.WriteEntity(escount)
} else {
resp.WriteErrorString(ret, http.StatusText(ret))
}
}
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
}
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
}
func getEnquire(req *restful.Request, resp *restful.Response) {
log.Debug("try to get enquire with id : %s", req.PathParameter("EnquireId"))
userId, err := strconv.ParseInt(req.Attribute("agsuserid").(string), 10, 64)
if err != nil {
resp.WriteError(http.StatusInternalServerError, err)
}
id := req.PathParameter("EnquireId")
e := new(Enquire)
e.Id = auth.Decode(id)
ret := dbGetEnquire(e, userId)
if ret == http.StatusOK {
e.Id = id
resp.WriteEntity(e)
} else {
resp.WriteErrorString(ret, http.StatusText(ret))
}
}
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
}
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
}
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
}
}
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
}
func dbGetTotalPageNumber() (float64, int) {
log.Debug("get total page number")
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 1, http.StatusOK
}
var n sql.NullFloat64
queryLogSql := "SELECT count(*) FROM article"
dbHandler.QueryRow(queryLogSql).Scan(&n)
return math.Ceil(float64(n.Float64 / float64(pageArticlesLimit))), http.StatusOK
}
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
}
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
}
func SendMail(receiver string, subject string, message string) int {
if auth == nil {
auth = smtp.PlainAuth(
"",
config.GetValue("SenderEmail"),
config.GetValue("SenderPassword"),
config.GetValue("SmtpServer"))
}
log.Debug("auth is %v", auth)
body := "To: " + receiver + "\r\nSubject: " + subject + "\r\n\r\n" + message
err := smtp.SendMail(
config.GetValue("SmtpServer")+":"+config.GetValue("SmtpPort"),
auth,
config.GetValue("SenderEmail"),
[]string{receiver},
[]byte(body))
if err != nil {
log.Info("mail %s sent to %s", subject, receiver)
return http.StatusForbidden
}
return http.StatusOK
}
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
}