Example #1
0
// 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
}
Example #2
0
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
}