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