예제 #1
0
 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
예제 #2
0
    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