def get_details2(self, order_list):
if not order_list: return []
elif len(order_list) == 1:
_sql = "SELECT * FROM oms_order_detail_new WHERE order_sid = %s"
_param = order_list[0].sid
else:
_sql = "SELECT * FROM oms_order_detail_new WHERE order_sid in %s"
_param = [o.sid for o in order_list]
_details = self._ori_db.query(_sql, _param)
_order_map = Util.map_key(order_list, 'sid')
for d in _details:
d['pay_time'] = _order_map[d.order_sid].pay_time
d['payment_type'] = _order_map[d.order_sid].payment_type
return _details
def rank(self, keys, shop_name, start_time, end_time):
"""根据keys，对指定门店在指定日期范围内进行排序
:keys: 用于排序合并的指定键值list，可选范围 供应商id/品牌/品类/商品名/款号，可以是范围内的1-N个值
:shop_name: 门店名
:start_time: 统计起始时间
:end_time: 统计结束时间
:returns: 统计结果list
"""
# 先按时间选取订单号
_sql = "SELECT order_no, member_sid FROM oms_order_new WHERE pay_time BETWEEN %s AND %s AND order_status>1"
_orders = self._db.query(_sql, start_time, end_time)
_order_no_list = [order.order_no for order in _orders]
_orders_map = Util.map_key(_orders, 'order_no')
_columns = []
for key in keys:
key = str(key)
if key in self._n2c: _columns.append(self._n2c[key])
keys = _columns
if not _columns: return []
_columns = _columns + ["order_no", "sale_price"] # 订单号，单件售价
_sql = "SELECT "+",".join(_columns) + " FROM oms_order_detail_new WHERE order_no in %s AND shop_sid in %s "
_param = []
shop_map = {"所有门店":"1001,1002,1004,1005,1006,1007,1008,1010,1011,1301,1311,5001","王府井店":"1001","亚运村店":"1002","五棵松店":"1004", "中关村店":"1005","朝阳门店":"1006","三里河店":"1007","来广营店":"1008","回龙观店":"1010","草桥店":"1011","下沙店":"1301","笕桥店":"1311","虚拟代销DC":"5001","北京门店":"1001,1002,1004,1005,1006,1007,1008,1010,1011","杭州门店":"1301,1311"}
cate_map = {'65':'儿童','66':'服饰','67':'户外','68':'家居用品','69':'毛纺','70':'男装','71':'内衣','72':'女装','73':'皮具','74':'鞋', '75':'休闲','76':'羽绒','77':'运动','78':'营销用品','80':'美妆护肤'}
_shop_id_list = shop_map[str(shop_name)].split(",")
_param.append(_shop_id_list)
if not _order_no_list: return []
if len(_order_no_list) == 1: _order_no_list.append(0) # torndb bug 兼容
_details = Util.query_large_list(self._db, _sql, _order_no_list, param=_param)
_tmp = dict()
for d in _details:
_group_key = "@".join(str(d.get(key)) for key in keys)
if not _group_key in _tmp:
_tmp[_group_key] = dict(sale_money_sum=0, sale_all_sum=0, orders=dict())
_group = _tmp[_group_key]
for _key in keys: _group[_key] = d.get(_key)
_group['sale_money_sum'] += d.sale_price
_group['sale_all_sum'] += 1
_group['orders'][d.order_no] = _orders_map[d.order_no].member_sid
# 转换为list形式
_data = [] # keys..., sale_money_sum, sale_all_sum, orders, member_rate
_row_keys = keys + ['sale_money_sum', 'sale_all_sum', 'order_sum', 'member_sum', 'member_rate']
_row_title = [self._c2n.get(key) for key in _row_keys]
# 统计order和member_rate
for _group_key in _tmp:
_group = _tmp[_group_key]
for _order in _group['orders']:
_group['order_sum'] = _group.get('order_sum', 0) + 1
if _group['orders'][_order]: _group['member_sum'] = _group.get('member_sum',0) + 1
_group['member_rate'] = float(_group.get('member_sum',0))/_group['order_sum'] if _group.get('order_sum') else 0
_group['member_rate'] = "%0.2f%%" % (_group['member_rate'] * 100)
for _group_key in _tmp:
_row = []
_group = _tmp[_group_key]
for key in _row_keys:
if key == 'left(category_sid,2)' :
if _group.get(key) in cate_map.keys():
_row.append(cate_map[_group.get(key)])
else :
_row.append('未知')
else :
_row.append(_group.get(key))
_data.append(_row)
# 以sale_money_sum排序
_idx = _row_keys.index('sale_money_sum')
_data = [_row_title] + sorted(_data, key=lambda s : s[_idx], reverse=True)
return _data