func getCustomerPrice(personId int, productId int, number int) ([]*model.CustomerPrice, 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 * from customer_special_price where person_id = ? and product_id = ? order by create_time DESC limit ?"
if stmt, err = conn.Prepare(_sql); err != nil {
return nil, err
}
defer stmt.Close()
rows, err := stmt.Query(personId, productId, number)
if err != nil {
return nil, err
}
defer rows.Close()
models := []*model.CustomerPrice{}
for rows.Next() {
m := new(model.CustomerPrice)
var blackhole sql.NullInt64
err := rows.Scan(&m.Id, &m.PersonId, &m.ProductId, &m.Price, &m.CreateTime, &blackhole /*&m.LastUsedTime */)
if err != nil {
panic(err)
}
models = append(models, m)
}
return models, nil
}
// 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
}
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
}
// --------------------------------------------------------------------------------
// 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
}
// update stock with delta.
func ChangeStatus(productId int, status product.Status) (affacted int64, err error) {
var conn *sql.DB
var stmt *sql.Stmt
if conn, err = db.Connect(); err != nil {
return
}
defer conn.Close()
var _sql = fmt.Sprintf("update product p set p.status = ? where p.id = ? limit 1")
if stmt, err = conn.Prepare(_sql); err != nil {
return
}
defer stmt.Close()
// 3. execute
_, err = stmt.Exec(status, productId)
if err != nil {
return
}
return
}
func Update(name string, key string, value interface{}, floatValue float64, id int64) error {
var conn *sql.DB
var stmt *sql.Stmt
var err error
if conn, err = db.Connect(); err != nil {
return err
}
defer conn.Close()
sql := "update const set `name`=?, `key`=?, `value`=?, doublevalue=? where id=?"
if stmt, err = conn.Prepare(sql); err != nil {
return err
}
defer stmt.Close()
_, err = stmt.Exec(name, key, value, floatValue, id)
if err != nil {
return err
}
return nil // res.RowsAffected()
}
func Set(name string, key string, value interface{}, floatValue float64) error {
var conn *sql.DB
var stmt *sql.Stmt
var err error
if conn, err = db.Connect(); err != nil {
return err
}
defer conn.Close()
sql := "insert into const(`name`, `key`, `value`, doublevalue) values(?,?,?,?) on duplicate key update `key`=?, `value`=?, doublevalue=?"
if stmt, err = conn.Prepare(sql); err != nil {
return err
}
defer stmt.Close()
_, err = stmt.Exec(name, key, value, floatValue, key, value, floatValue)
if err != nil {
return err
}
return nil // res.RowsAffected()
}
func DeleteByName(name string) (int64, error) {
var conn *sql.DB
var stmt *sql.Stmt
var err error
if conn, err = db.Connect(); err != nil {
return 0, err
}
defer conn.Close()
sql := "delete from const where name=?"
if stmt, err = conn.Prepare(sql); err != nil {
return 0, err
}
defer stmt.Close()
// execute
res, err := stmt.Exec(name)
if err != nil {
return 0, err
}
return res.RowsAffected()
}
// update stock with delta.
func UpdateProductStockWithDelta(productId int64, color string, size string, stock int) error {
var conn *sql.DB
var stmt *sql.Stmt
var err error
if conn, err = db.Connect(); err != nil {
return err
}
defer conn.Close()
var _sql = fmt.Sprintf("insert into product_sku (product_id, color, size, price, stock, create_time) values (?,?,?,0,?, now() ) on duplicate key update stock = stock + ?")
if stmt, err = conn.Prepare(_sql); err != nil {
return err
}
defer stmt.Close()
// 3. execute
_, err = stmt.Exec(productId, color, size, stock, stock)
if err != nil {
return err
}
return nil
}
// DAO service
func (s *StatService) CalculateHotSaleProducts(years, months, days int) (*model.HotSales, error) {
start, end := gxl.NatureTimeRangeUTC(years, months, days)
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 product_id,p.name,sum(quantity) from `order_detail` od " +
"left join product p on od.product_id = p.Id " +
"where od.order_track_number in (" +
" SELECT o.track_number FROM `order` o WHERE (`type`=0 or `type`=2) " +
" and (`create_time`>=? and `create_time`<?) " +
") group by product_id order by sum(quantity) desc"
// fmt.Println(_sql)
// fmt.Println("start: ", start)
// fmt.Println("end : ", end)
if stmt, err = conn.Prepare(_sql); err != nil {
return nil, err
}
defer stmt.Close()
rows, err := stmt.Query(start, end)
if err != nil {
return nil, err
}
defer rows.Close()
// collect results.
hs := &model.HotSales{} //HSProduct: []*HotSaleProduct{}
// models := []*model.HotSaleProduct{}
for rows.Next() {
var (
quantity = new(sql.NullInt64)
productId = new(sql.NullInt64)
productName = new(sql.NullString)
)
if err := rows.Scan(productId, productName, quantity); err != nil {
return nil, err
}
m := new(model.HotSaleProduct)
if productId.Valid {
m.ProductId = productId.Int64
}
if productName.Valid {
m.ProductName = productName.String
}
if quantity.Valid {
m.Sales = (int)(quantity.Int64)
}
hs.HSProduct = append(hs.HSProduct, m)
}
sort.Sort(hs.HSProduct)
return hs, nil
}
func (s *StatService) CalculateHotSaleProducts_with_specs(years, months, days int) (*model.HotSales, error) {
start, end := gxl.NatureTimeRangeUTC(years, months, days)
// fmt.Println("=======================")
// fmt.Println("start:", start)
// fmt.Println("start:", end)
// fmt.Println("=======================")
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 product_id,p.name,color,size,sum(quantity) from `order_detail` od " +
"left join product p on od.product_id = p.Id " +
"where od.order_track_number in (" +
" SELECT o.track_number FROM `order` o WHERE (`create_time`>=? and `create_time`<?)" +
` and o.type in (?,?)
and o.status in (?,?,?,?)
` +
// and od.product_id<>? // 需要显示样衣卖掉了多少件。不用去掉。
") group by product_id,color,size order by sum(quantity) desc"
if stmt, err = conn.Prepare(_sql); err != nil {
return nil, err
}
defer stmt.Close()
rows, err := stmt.Query(
start, end,
model.Wholesale, model.SubOrder, // model.ShippingInstead, // 查子订单
"toprint", "todeliver", "delivering", "done",
// base.STAT_EXCLUDED_PRODUCT,
)
if err != nil {
return nil, err
}
defer rows.Close()
// collect results.
pmap := map[int64]*model.HotSaleProduct{}
for rows.Next() {
var (
quantity = new(sql.NullInt64)
color = new(sql.NullString)
size = new(sql.NullString)
productId = new(sql.NullInt64)
productName = new(sql.NullString)
)
if err := rows.Scan(productId, productName, color, size, quantity); err != nil {
return nil, err
}
m := new(model.HotSaleProduct)
if productId.Valid {
m.ProductId = productId.Int64
}
if productName.Valid {
m.ProductName = productName.String
}
if quantity.Valid {
m.Sales = (int)(quantity.Int64)
}
// combine specs.
if color.Valid && size.Valid {
cskey := fmt.Sprintf("%v_%v", color.String, size.String)
hsp, ok := pmap[m.ProductId]
if !ok {
hsp = &model.HotSaleProduct{
ProductId: m.ProductId,
ProductName: m.ProductName,
Sales: 0,
Specs: make(map[string]int),
}
hsp.Specs[cskey] = 0
pmap[m.ProductId] = hsp
}
hsp.Specs[cskey] += m.Sales
hsp.Sales += m.Sales
}
}
hs := &model.HotSales{}
for _, hsp := range pmap {
hs.HSProduct = append(hs.HSProduct, hsp)
}
sort.Sort(hs.HSProduct)
return hs, nil
}
// fill color & sizes to product list.
func FillProductPropertiesByIdSet(models []*model.Product) error {
if nil == models || len(models) == 0 {
return nil
}
var conn *sql.DB
var stmt *sql.Stmt
var err error
if conn, err = db.Connect(); err != nil {
return err
}
defer conn.Close()
var _sql bytes.Buffer // sql buffer
var params = []interface{}{} // params
var index = map[int]*model.Product{}
_sql.WriteString("select product_id, property_name, value from product_property where ")
_sql.WriteString("product_id in (")
for idx, m := range models {
if idx > 0 {
_sql.WriteRune(',')
}
_sql.WriteRune('?')
params = append(params, m.Id)
index[m.Id] = m
}
_sql.WriteString(") order by id asc")
if stmt, err = conn.Prepare(_sql.String()); err != nil {
return err
}
defer stmt.Close()
// 3. execute
rows, err := stmt.Query(params...)
if err != nil {
return err
}
defer rows.Close()
// execute
var productId int
var propertyName string
var value string
for rows.Next() {
err := rows.Scan(&productId, &propertyName, &value)
if err != nil {
return err
}
if product, ok := index[productId]; ok {
if propertyName == "color" {
if product.Colors == nil {
product.Colors = []string{}
}
product.Colors = append(product.Colors, value)
} else if propertyName == "size" {
if product.Sizes == nil {
product.Sizes = []string{}
}
product.Sizes = append(product.Sizes, value)
}
}
}
return nil
}
// ----------------------------------------------------------------------------------------------------
// fill stocks to inventory
func GetAllStocksByIdSet(idset map[int64]bool) (map[int64]model.Stocks, error) {
if nil == idset || len(idset) == 0 {
return nil, nil
}
var conn *sql.DB
var stmt *sql.Stmt
var err error
if conn, err = db.Connect(); err != nil {
return nil, err
}
defer conn.Close()
var _sql bytes.Buffer // sql buffer
var params = []interface{}{} // params
// var index = map[int]*model.Product{}
_sql.WriteString("select id, product_id, color, size, stock from product_sku where ")
_sql.WriteString("product_id in (")
var idx = 0
for id, b := range idset {
if b {
if idx > 0 {
_sql.WriteRune(',')
}
_sql.WriteRune('?')
params = append(params, id)
// index[id] = m
idx += 1
}
}
_sql.WriteRune(')')
if stmt, err = conn.Prepare(_sql.String()); err != nil {
return nil, err
}
defer stmt.Close()
// 3. execute
rows, err := stmt.Query(params...)
if err != nil {
return nil, err
}
defer rows.Close()
// execute
var (
id int
productId int
color string
size string
// price float32
stock int
)
returns := map[int64]model.Stocks{} // productId -> color -> size : stock
for rows.Next() {
err := rows.Scan(&id, &productId, &color, &size /*&price,*/, &stock)
if err != nil {
return nil, err
}
colors, ok := returns[int64(productId)]
if !ok {
colors = model.NewStocks()
returns[int64(productId)] = colors
}
colors.Set(color, size, stock)
}
return returns, 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
}