// 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
}