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
}
Exemple #3
0
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
}
Exemple #5
0
// --------------------------------------------------------------------------------
// 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
}
Exemple #6
0
// 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
}
Exemple #7
0
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()
}
Exemple #8
0
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()
}
Exemple #9
0
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()
}
Exemple #10
0
// 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
}
Exemple #11
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
}
Exemple #12
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
}
// 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
}
Exemple #14
0
// ----------------------------------------------------------------------------------------------------
// 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
}
Exemple #15
0
// 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
}