// get properties by product and properties' name
func GetProductProperties(propertyId int, propertiesName string) (values []string) {
conn, _ := db.Connect()
defer db.CloseConn(conn) // should use db.CloseConn or conn.Close()?
stmt, err := conn.Prepare("select value from product_property where " +
"product_id=? and property_name=? order by id asc")
defer db.CloseStmt(stmt)
if db.Err(err) {
panic(err.Error())
// should here be empty return?
}
rows, err := stmt.Query(propertyId, propertiesName)
defer db.CloseRows(rows)
if db.Err(err) {
return nil
}
// big performance issue, maybe.
for rows.Next() {
var propertyValue string
rows.Scan(&propertyValue)
values = append(values, propertyValue)
}
return values
}
// Delete Product Property by product, property name and value
func DeleteProductProperty(productId int, propertyName string, property string) {
conn := db.Connectp()
defer db.CloseConn(conn)
stmt, err := conn.Prepare("delete from product_property where " +
"product_id = ? and property_name = ? and value = ? limit 1")
if db.Err(err) {
panic(err.Error())
}
defer stmt.Close()
_, err = stmt.Exec(productId, propertyName, property)
if db.Err(err) {
panic(err.Error())
}
}
func ClearProductStock(productId int) error {
conn := db.Connectp()
defer db.CloseConn(conn)
stmt, err := conn.Prepare("delete from product_sku where product_id = ?")
if db.Err(err) {
return err
}
defer stmt.Close()
_, err = stmt.Exec(productId)
if db.Err(err) {
return err
}
return nil
}
// private
func setProductCSValue(productId int, color string, size string,
field string, stock int, price float64) {
conn := db.Connectp()
defer db.CloseConn(conn)
_sql := fmt.Sprintf("insert into product_sku (product_id, color, size, %v) values (?,?,?,?) on duplicate key update %v = ?", field, field)
stmt, err := conn.Prepare(_sql)
defer db.CloseStmt(stmt) // the safe way to close.
if db.Err(err) {
panic(err.Error())
}
if field == "stock" {
_, err := stmt.Exec(productId, color, size, stock, stock)
if err != nil {
debug.Error(err)
}
} else if field == "price" {
_, err := stmt.Exec(productId, color, size, price, stock)
if err != nil {
debug.Error(err)
}
}
}
// Set customer private price, panic if any error occurs.
func AddProductProperty(productId int, propertyName string, property string) {
conn := db.Connectp()
defer db.CloseConn(conn)
sql := "insert into product_property " +
"(product_id, property_name, value) values(?,?,?)"
stmt, err := conn.Prepare(sql)
if db.Err(err) {
panic(err.Error())
}
defer stmt.Close()
_, err = stmt.Exec(productId, propertyName, property)
if db.Err(err) {
panic(err.Error())
}
}
func DailySalesData_alldata(startTime string, excludeYangYi bool) (model.ProductSales, error) {
var conn *sql.DB
var stmt *sql.Stmt
var err error
if conn, err = db.Connect(); err != nil {
return nil, err
}
defer conn.Close()
_sql := `
select
DATE_FORMAT(o.create_time, '%Y-%m-%d'), sum(od.quantity)
from
order_detail od
left join ` + "`" + `order` + "`" + ` o on od.order_track_number = o.track_number
where
o.type in (?,?)
and o.status in (?,?,?,?)
and o.create_time >= ?
and od.product_id <> ?
group by
DATE_FORMAT(o.create_time, '%Y-%m-%d')
order by
DATE_FORMAT(o.create_time, '%Y-%m-%d') asc
`
if stmt, err = conn.Prepare(_sql); err != nil {
return nil, err
}
defer stmt.Close()
var excluded_product_id = 0
if excludeYangYi {
excluded_product_id = base.STAT_EXCLUDED_PRODUCT
}
rows, err := stmt.Query(
model.Wholesale, model.SubOrder, // model.ShippingInstead, // 查子订单
"toprint", "todeliver", "delivering", "done",
startTime, excluded_product_id,
)
if db.Err(err) {
return nil, err
}
defer rows.Close() // db.CloseRows(rows) // use db.CloseRows or rows.Close()? Is rows always nun-nil?
ps := model.ProductSales{}
for rows.Next() {
p := new(model.SalesNode)
rows.Scan(&p.Key, &p.Value)
ps = append(ps, p)
}
return ps, nil
}
func SettleAccount(startTime, endTime time.Time, factoryId int64) (*model.ProductSalesTable, error) {
var conn *sql.DB
var stmt *sql.Stmt
var err error
if conn, err = db.Connect(); err != nil {
return nil, err
}
defer conn.Close()
startTime = startTime.UTC().Truncate(time.Hour * 24)
endTime = endTime.UTC().Truncate(time.Hour*24).AddDate(0, 0, 1)
_sql := `
select product_id, sum(stock), send_time
from inventory i
where
send_time >= ?
and send_time <= ?
and provider_id = ?
group by DATE_FORMAT(send_time, '%Y-%m-%d'), product_id
`
if stmt, err = conn.Prepare(_sql); err != nil {
return nil, err
}
defer stmt.Close()
// now := time.Now()
rows, err := stmt.Query(
startTime,
endTime,
factoryId,
// model.Wholesale, model.SubOrder, // model.ShippingInstead, // 查子订单
// "toprint", "todeliver", "delivering", "done",
// base.STAT_EXCLUDED_PRODUCT,
// startTime,
// startTime,
)
if db.Err(err) {
return nil, err
}
defer rows.Close() // db.CloseRows(rows) // use db.CloseRows or rows.Close()? Is rows always nun-nil?
// the final result
ps := model.NewProductSalesTable()
var (
productId int64
stock int
send_time time.Time
)
for rows.Next() {
rows.Scan(&productId, &stock, &send_time)
ps.Set(send_time.Format("2006-01-02"), productId, stock)
}
return ps, nil
}
//
// Delete Product Property by product, property name and value
//
func DeleteOneProductProperty(productId int, propertyName string) {
// fmt.Println("______________________________________________________________")
// fmt.Println(productId)
// fmt.Println(propertyName)
if productId <= 0 {
panic("Error when DeleteOneProductProperty: productId: " + string(productId))
}
conn := db.Connectp()
defer db.CloseConn(conn)
stmt, err := conn.Prepare("delete from product_property where " +
"product_id = ? and property_name = ?")
if db.Err(err) {
panic(err.Error())
}
defer stmt.Close()
_, err = stmt.Exec(productId, propertyName)
if db.Err(err) {
panic(err.Error())
}
}
// --------------------------------------------------------------------------------
// Product's top buyer list. In product/detail page.
func ProductBestBuyerList(productId int) (model.BestBuyerList, error) {
var conn *sql.DB
var stmt *sql.Stmt
var err error
if conn, err = db.Connect(); err != nil {
return nil, err
}
defer conn.Close()
_sql := `
select -- d.product_id, p.Name,
o.customer_id, pp.Name, sum(d.quantity), d.selling_price ` +
"from `order` o " +
` right join order_detail d on d.order_track_number = o.track_number
left join product p on d.product_id = p.Id
left join person pp on o.customer_id=pp.Id
where
-- and o.customer_id = 305
d.product_id = ?
and o.type in (?,?)
and o.status in (?,?,?,?)
-- and o.create_time >= "2015-08-14" -- and o.create_time < "2015-03-23 23:55:55"
-- and o.track_number=1501161519337773 -- debug
group BY d.product_id, o.customer_id
order by sum(d.quantity) desc
`
if stmt, err = conn.Prepare(_sql); err != nil {
return nil, err
}
defer stmt.Close()
rows, err := stmt.Query(
productId,
model.Wholesale, model.SubOrder, // model.ShippingInstead, // 查子订单
"toprint", "todeliver", "delivering", "done",
)
if db.Err(err) {
return nil, err
}
defer rows.Close() // db.CloseRows(rows) // use db.CloseRows or rows.Close()? Is rows always nun-nil?
ps := model.BestBuyerList{}
for rows.Next() {
p := new(model.BestBuyerListItem)
rows.Scan(&p.CustomerId, &p.CustomerName, &p.Quantity, &p.SalePrice)
ps = append(ps, p)
}
return ps, nil
}
/* Set customer private price */
func SetCustomerPrice(personId int, productId int, price float64) error {
if logdebug {
log.Printf("[dal] Set customer price for %v on %v, $%v", personId, productId, price)
}
conn := db.Connectp()
defer db.CloseConn(conn)
// first get price xxx. TODO performance.
stmt, err := conn.Prepare("insert into customer_special_price " +
"(person_id, product_id, price, create_time, last_use_time) " +
"values(?,?,?,?,?)")
if db.Err(err) {
return err
}
defer stmt.Close()
_, err = stmt.Exec(personId, productId, price, time.Now(), nil)
if err != nil {
return err
}
return nil
}
// TodayStat returns statistics of latest n days.
// TODO: return the second parameter as error
func TodayStat(startTime time.Time, n int) ([]*model.SumStat, error) {
var debug_print_time = false
var conn *sql.DB
var stmt *sql.Stmt
var err error
if conn, err = db.Connect(); err != nil {
return nil, err
}
defer conn.Close()
startTime = startTime.UTC().Truncate(time.Hour*24).AddDate(0, 0, 1)
endTime := startTime.AddDate(0, 0, -n).Truncate(time.Hour * 24)
if debug_print_time {
fmt.Println("((((())))) ---- start time:", startTime)
fmt.Println("((((())))) ---- end time:", endTime)
}
// 这个sql会自动将时间转换为utc时间进行搜索。因此传入的时间无需转换时区。
_sql := `
select DATE_FORMAT(o.create_time, '%Y-%m-%d') as 'date',
count(distinct o.track_number) as 'norder',
sum(od.quantity) as 'nsold',
sum(od.quantity * od.selling_price) as '总价' ` +
"from `order` o " + `
right join order_detail od on o.track_number = od.order_track_number
where
o.create_time<?
and o.create_time >= ?
and DATEDIFF(o.create_time,?) > ?
and o.type in (?,?)
and o.status in (?,?,?,?)
and od.product_id<>?
group by DATEDIFF(o.create_time,?)
order by DATEDIFF(o.create_time,?) asc
`
if stmt, err = conn.Prepare(_sql); err != nil {
return nil, err
}
defer stmt.Close()
// now := time.Now()
rows, err := stmt.Query(
startTime,
endTime,
startTime, -n,
model.Wholesale, model.SubOrder, // model.ShippingInstead, // 查子订单
"toprint", "todeliver", "delivering", "done",
base.STAT_EXCLUDED_PRODUCT,
startTime,
startTime,
)
if db.Err(err) {
return nil, err
}
defer rows.Close() // db.CloseRows(rows) // use db.CloseRows or rows.Close()? Is rows always nun-nil?
// the final result
ps := []*model.SumStat{}
for rows.Next() {
p := new(model.SumStat)
rows.Scan(&p.Id, &p.NOrder, &p.NSold, &p.TotalPrice)
// update average.
p.AvgPrice = p.TotalPrice / float64(p.NSold)
ps = append(ps, p)
}
return ps, nil
}