// 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
}
// Delete Product Property by product, property name and value
func DeleteProductProperty(productId int, propertyName string, property string) {
	conn := db.Connectp()
	defer db.CloseConn(conn)

	stmt, err := conn.Prepare("delete from product_property where " +
		"product_id = ? and property_name = ? and value = ? limit 1")
	if db.Err(err) {
		panic(err.Error())
	}
	defer stmt.Close()

	_, err = stmt.Exec(productId, propertyName, property)
	if db.Err(err) {
		panic(err.Error())
	}
}
Beispiel #3
0
func ClearProductStock(productId int) error {
	conn := db.Connectp()
	defer db.CloseConn(conn)

	stmt, err := conn.Prepare("delete from product_sku where product_id = ?")
	if db.Err(err) {
		return err
	}
	defer stmt.Close()

	_, err = stmt.Exec(productId)
	if db.Err(err) {
		return err
	}
	return nil
}
Beispiel #4
0
// private
func setProductCSValue(productId int, color string, size string,
	field string, stock int, price float64) {

	conn := db.Connectp()
	defer db.CloseConn(conn)

	_sql := fmt.Sprintf("insert into product_sku (product_id, color, size, %v) values (?,?,?,?) on duplicate key update %v = ?", field, field)

	stmt, err := conn.Prepare(_sql)
	defer db.CloseStmt(stmt) // the safe way to close.
	if db.Err(err) {
		panic(err.Error())
	}

	if field == "stock" {
		_, err := stmt.Exec(productId, color, size, stock, stock)
		if err != nil {
			debug.Error(err)
		}
	} else if field == "price" {
		_, err := stmt.Exec(productId, color, size, price, stock)
		if err != nil {
			debug.Error(err)
		}
	}
}
// Set customer private price, panic if any error occurs.
func AddProductProperty(productId int, propertyName string, property string) {
	conn := db.Connectp()
	defer db.CloseConn(conn)

	sql := "insert into product_property " +
		"(product_id, property_name, value) values(?,?,?)"

	stmt, err := conn.Prepare(sql)
	if db.Err(err) {
		panic(err.Error())
	}
	defer stmt.Close()

	_, err = stmt.Exec(productId, propertyName, property)
	if db.Err(err) {
		panic(err.Error())
	}
}
Beispiel #6
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
}
//
// Delete Product Property by product, property name and value
//
func DeleteOneProductProperty(productId int, propertyName string) {
	// fmt.Println("______________________________________________________________")
	// fmt.Println(productId)
	// fmt.Println(propertyName)
	if productId <= 0 {
		panic("Error when DeleteOneProductProperty: productId: " + string(productId))
	}

	conn := db.Connectp()
	defer db.CloseConn(conn)

	stmt, err := conn.Prepare("delete from product_property where " +
		"product_id = ? and property_name = ?")
	if db.Err(err) {
		panic(err.Error())
	}
	defer stmt.Close()
	_, err = stmt.Exec(productId, propertyName)
	if db.Err(err) {
		panic(err.Error())
	}
}
Beispiel #9
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
}
Beispiel #10
0
/* Set customer private price */
func SetCustomerPrice(personId int, productId int, price float64) error {
	if logdebug {
		log.Printf("[dal] Set customer price for %v on %v, $%v", personId, productId, price)
	}

	conn := db.Connectp()
	defer db.CloseConn(conn)

	// first get price xxx. TODO performance.
	stmt, err := conn.Prepare("insert into customer_special_price " +
		"(person_id, product_id, price, create_time, last_use_time) " +
		"values(?,?,?,?,?)")
	if db.Err(err) {
		return err
	}
	defer stmt.Close()

	_, err = stmt.Exec(personId, productId, price, time.Now(), nil)
	if err != nil {
		return err
	}
	return nil
}
Beispiel #11
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
}