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