def fenzu(items, x=None, key=None, y=None, func_groupby=None):
# 先按key分组
if not key:
series = groupby(items, lambda x: 'all')
else:
series = groupby(items, lambda x: x[key])
x_axis = [
x.strftime('%Y-%m-%d')
for x in generate_date(start_date, end_date)[:-1]
]
for k, v in series.items():
# 再按x分组
if y:
chart_items = process(groupby(v, lambda x: x['x']),
lambda x: int(x[0].get(y)))
else:
chart_items = process(groupby(v, lambda x: x['x']), len)
chart_items = dict(chart_items)
for x in x_axis:
if not chart_items.get(x, None):
chart_items[x] = 0
chart_items = sorted(chart_items.items(),
key=lambda x: x[0],
reverse=False)
series[k] = chart_items
def generate_chart_data(items,
xaris=None,
key=None,
yaris=None,
start_date=None,
end_date=None):
"""
{
k1: {
'2016-08': [{}],
'2016-09': [{}],
},
k2: {
'2016-08': [{}],
'2016-09': [{}],
},
}
"""
# 先按key分组
if not key:
series = groupby(items, lambda x: 'all')
else:
series = groupby(items, key)
x_axis = [
i.strftime('%Y-%m-%d')
for i in generate_date(start_date, end_date)[:-1]
]
for k, v in series.items():
# 再按x分组
if yaris:
chart_items = process(groupby(v, lambda x: x[xaris]), yaris)
else:
chart_items = process(groupby(v, lambda x: x[xaris]), len)
# 补全空缺日期
chart_items = dict(chart_items)
for x in x_axis:
if not chart_items.get(x, None):
chart_items[x] = 0
# 按日期排序
chart_items = sorted(chart_items.items(),
key=lambda x: x[0],
reverse=False)
series[k] = chart_items
chart_items = {}
for k, v in series.items():
chart_items[k] = [x[1] for x in v]
return x_axis, chart_items
def carry(req):
# sql = """
# SELECT mama_id, sum(carry_num) as money FROM flashsale_xlmm_carry_record
# where status=2
# group by mama_id
# """
p_start_date, p_end_date, start_date, end_date = get_date_from_req(req)
sql = """
SELECT mama_id, sum(carry_num) as money FROM flashsale_xlmm_carry_record
where status in (1, 2) and mama_id in (
SELECT xiaolumm_xiaolumama.id FROM xiaolumm_xiaolumama
where xiaolumm_xiaolumama.agencylevel=3
and created > %s
and created < %s
)
group by mama_id
"""
queryset = execute_sql(
get_cursor(), sql,
[format_datetime(start_date),
format_datetime(end_date)])
sql = """
SELECT count(*) as count FROM xiaolumm_xiaolumama
where xiaolumm_xiaolumama.agencylevel=3
and created > %s
and created < %s
"""
one_mama_count = execute_sql(
get_cursor(), sql,
[format_datetime(start_date),
format_datetime(end_date)])[0]['count']
def byfunc(item):
money = item['money']
return generate_range(
float(money) / 100.0, [5, 10, 20, 30, 50, 100, 200, 500])
# if money < 3000:
# return u'小于30'
# elif money < 10000:
# return u'30-100'
# else:
# return u'大于100'
pie_products = groupby(queryset, byfunc)
pie_products = process(pie_products, len)
piechart = dict(pie_products)
has_carry_count = len(queryset)
sum_carry = sum([x['money'] for x in queryset]) / 100
avg_carry = None
if has_carry_count:
avg_carry = '%.2f' % (sum_carry / has_carry_count)
return render(req, 'yunying/mama/carry.html', locals())
def retain(req):
p_start_date, p_end_date, start_date, end_date = get_date_from_req(req)
mamas = XiaoluMama.objects.filter(created__gte=start_date,
created__lt=end_date,
agencylevel=3)
sql = """
SELECT * FROM flashsale_xlmm_mamadailyappvisit
where created > %s
and created < %s
"""
uvs = execute_sql(get_cursor(), sql,
[format_datetime(start_date),
format_datetime(end_date)])
def func(items):
return set([x.id for x in items])
mamas = groupby(mamas, lambda x: x.created)
mamas = process(mamas, func)
mamas = sorted(mamas, key=lambda x: x[0])
uvs = groupby(uvs, lambda x: x['created'])
uvs = process(uvs, lambda x: set([y['mama_id'] for y in x]))
uvs = sorted(uvs, key=lambda x: x[0])
col_date = [x[0] for x in uvs]
result = []
for date, mama_ids in mamas:
row = []
for d2, m2 in uvs:
jiaoji = len(list(mama_ids & m2))
mama_ids_count = len(list(mama_ids))
row.append([
jiaoji, mama_ids_count,
'%.2f%%' % (jiaoji * 100.0 / mama_ids_count)
])
result.append((date, row))
print result
return render(req, 'yunying/mama/retain.html', locals())
def score(req):
start_date = datetime(2016, 9, 27)
end_date = datetime(2016, 9, 28)
# mamas = XiaoluMama.objects.filter(created__gte=start_date, created__lt=end_date)[:10]
# mamas = XiaoluMama.objects.filter(id='24543')[:10]
# mamas = mongo.mama_score.find() # .sort('score.score', -1).limit(100)
# mamas = list(mamas)
mamas = []
def by_score(item):
return item['score']['score']
data = groupby(mamas, by_score)
data = process(data, len)
# for mama in mamas:
# score = get_mama_score(mama)
# mama.score = score
# mama.invite_score = get_mama_invite_score(mama)
return render(req, 'yunying/mama/score.html', locals())
def salecategory(req):
p_start_date, p_end_date, start_date, end_date = get_date_from_req(req)
cursor = get_cursor()
sql = """
SELECT
flashsale_order.item_id,
flashsale_order.total_fee,
flashsale_order.title,
flashsale_order.pay_time,
supplychain_sale_category.cid,
supplychain_sale_category.name
FROM flashsale_order
join shop_items_product on shop_items_product.id=flashsale_order.item_id
join flashsale_modelproduct on shop_items_product.model_id=flashsale_modelproduct.id
join supplychain_sale_category on flashsale_modelproduct.salecategory_id=supplychain_sale_category.id
where flashsale_order.pay_time >= %s and flashsale_order.pay_time < %s
"""
products = execute_sql(cursor, sql, [format_datetime(start_date), format_datetime(end_date)])
def byfunc(item):
# return item['name']
category = get_root_category(item['cid'])
return category.name
def pfunc(items):
return int(sum([x['total_fee'] for x in items]))
pie_products = groupby(products, byfunc)
pie_products = process(pie_products, pfunc)
piechart = dict(pie_products)
x_axis, chart_items = generate_chart_data(
products, xaris='pay_time', key=byfunc, yaris=pfunc, start_date=start_date, end_date=end_date)
charts = [generate_chart('商品类目销售额', x_axis, chart_items, width='1000px')]
return render(req, 'yunying/product/category.html', locals())
def process_items(items):
def get_origin_price(item):
extras = item['extras']
extras = simplejson.loads(extras)
origin_price = extras.get('origin_price')
item['origin_price'] = origin_price
return item
def cal_profit(items):
res = {
'id': items[0]['id'],
'created': items[0]['created'],
'flow_amount': items[0]['flow_amount'],
'origin_price': sum([x['origin_price'] for x in items])
}
return res
items = map(get_origin_price, items)
items = filter(lambda x: x['origin_price'], items)
items = groupby(items, lambda x: x['id'])
items = process(items, cal_profit)
items = [x[1] for x in items]
return items
def coupon_rank(req):
mama_id = req.GET.get('mama_id')
q_page = req.GET.get('page') or 1
today = datetime.today()
yesterday = datetime.today() - timedelta(days=1)
start_date = datetime(yesterday.year, yesterday.month, yesterday.day)
end_date = datetime(today.year, today.month, today.day)
data = {}
if mama_id:
mamas = XiaoluMama.objects.using('default').filter(id=mama_id)
else:
mamas = XiaoluMama.objects.using('default').filter(
elite_score__gt=0).order_by('-elite_score')
total_count = mamas.count()
# mamas = mamas[:100]
# data_levels = process(groupby(mamas, lambda x: x.elite_level), len)
p = Paginator(mamas, 50)
cur_page = p.page(q_page)
p.show_page_range = [
x for x in p.page_range if 10 >= (x - cur_page.number) >= -10
]
mamas = cur_page.object_list
for item in mamas:
customer = Customer.objects.using('default').filter(
unionid=item.openid).first()
data[item.id] = {
'id': int(item.id),
'elite_score': item.elite_score,
'elite_level': item.elite_level,
'referal_from': item.referal_from,
'customer': customer,
'manager': item.mama_manager,
'yesterday_score': calc_xlmm_elite_score(item.id, start_date,
end_date)
# 'score': calc_xlmm_elite_score(item.id)
}
mama_ids = [int(x.id) for x in mamas]
def by_level(mm_id):
mm = XiaoluMama.objects.using('default').filter(
id=mm_id, referal_from=XiaoluMama.INDIRECT).first()
if mm:
return mm.elite_level
else:
return 'indirect'
for item in mamas:
fans = ReferalRelationship.objects.using('default').filter(
referal_from_mama_id=item.id,
referal_type__in=[
XiaoluMama.ELITE, XiaoluMama.HALF, XiaoluMama.FULL
])
res = process(
groupby([fan.referal_to_mama_id for fan in fans], by_level), len)
data[item.id]['team'] = res
# 买券
sql = """
SELECT
coupon_to_mama_id ,
sum(coupon_value * coupon_num) AS val,
sum(coupon_num) AS num
FROM
`xiaoludb`.`flashsale_coupon_transfer_record`
WHERE
transfer_type = 4
AND coupon_to_mama_id IN (%s)
and transfer_status=3
GROUP BY
coupon_to_mama_id
ORDER BY
val DESC
""" % ','.join(map(str, mama_ids))
records = execute_sql(get_cursor(), sql)
for item in records:
data[int(item['coupon_to_mama_id'])]['in_buy_coupon'] = {
'val': item['val'],
'num': item['num']
}
# 用券买货
sql = """
SELECT
coupon_from_mama_id ,
sum(coupon_value * coupon_num) AS val,
sum(coupon_num) AS num
FROM
`xiaoludb`.`flashsale_coupon_transfer_record`
WHERE
transfer_type = 3
AND coupon_from_mama_id IN (%s)
and transfer_status=3
GROUP BY
coupon_from_mama_id
ORDER BY
val DESC
""" % ','.join(map(str, mama_ids))
records = execute_sql(get_cursor(), sql)
for item in records:
data[int(item['coupon_from_mama_id'])]['out_consumed'] = {
'val': item['val'],
'num': item['num']
}
# 转给下属
sql = """
SELECT
coupon_from_mama_id ,
sum(coupon_value * coupon_num) AS val,
sum(coupon_num) AS num
FROM
`xiaoludb`.`flashsale_coupon_transfer_record`
WHERE
transfer_type = 2
AND coupon_from_mama_id IN (%s)
and transfer_status=3
GROUP BY
coupon_from_mama_id
ORDER BY
val DESC
""" % ','.join(map(str, mama_ids))
records = execute_sql(get_cursor(), sql)
for item in records:
data[int(item['coupon_from_mama_id'])]['out_transfer'] = {
'val': item['val'],
'num': item['num']
}
# 总进券
sql = """
SELECT
coupon_to_mama_id ,
sum(coupon_value * coupon_num) AS val,
sum(coupon_num) AS num
FROM
`xiaoludb`.`flashsale_coupon_transfer_record`
WHERE
coupon_to_mama_id IN (%s)
and transfer_status=3
GROUP BY
coupon_to_mama_id
""" % ','.join(map(str, mama_ids))
records = execute_sql(get_cursor(), sql)
for item in records:
data[int(item['coupon_to_mama_id'])]['in'] = {
'val': item['val'],
'num': item['num']
}
# 总出券
sql = """
SELECT
coupon_from_mama_id ,
sum(coupon_value * coupon_num) AS val,
sum(coupon_num) AS num
FROM
`xiaoludb`.`flashsale_coupon_transfer_record`
WHERE
coupon_from_mama_id IN (%s)
and transfer_status=3
GROUP BY
coupon_from_mama_id
""" % ','.join(map(str, mama_ids))
records = execute_sql(get_cursor(), sql)
for item in records:
data[int(item['coupon_from_mama_id'])]['out'] = {
'val': item['val'],
'num': item['num']
}
data = sorted(data.values(), key=lambda x: x['elite_score'], reverse=True)
# count = total_count
# cur_page = {
# 'has_previous': '',
# 'has_next': '',
# 'number': int(page),
# }
# p = {}
# p['show_page_range'] = [x+1 for x in range(count/40+1)]
# print count, cur_page, p
return render(req, 'yunying/mama/coupon_rank.html', locals())
def tab(req):
p_start_date, p_end_date, start_date, end_date = get_date_from_req(req)
query = req.GET.get('sql', '')
query_name = req.GET.get('query_name', 'xx')
func_groupby = req.GET.get('func_groupby', '')
p_key_desc = req.GET.get('key_desc', '')
if func_groupby.strip() == '':
func_groupby = ''
if not query:
return render(req, 'yunying/mama/index.html', locals())
if p_key_desc:
key_desc = simplejson.loads(p_key_desc)
else:
key_desc = None
sql = query.format(**{
'start_date': '"%s"' % p_start_date,
'end_date': '"%s"' % p_end_date
})
key = None
items = execute_sql(get_cursor(), sql)
if items:
key = 'k' if 'k' in items[0].keys() else None
y = 'y' if 'y' in items[0].keys() else None
# 先按key分组
if not key:
if func_groupby:
exec func_groupby in locals()
series = groupby(items, func_g)
else:
series = groupby(items, lambda x: 'all')
else:
series = groupby(items, lambda x: x[key])
x_axis = []
for k, v in series.items():
# 再按x分组
if y:
chart_items = process(groupby(v, lambda x: x['x']),
lambda x: int(x[0].get(y)))
else:
chart_items = process(groupby(v, lambda x: x['x']), len)
chart_items = dict(chart_items)
x_axis += chart_items.keys()
series[k] = chart_items
x_axis = sorted(list(set(x_axis)), cmp=_sort_by_x, reverse=False)
for k, v in series.items():
for x in x_axis:
if not v.get(x, None):
v[x] = 0
v = sorted(v.items(),
key=lambda x: x[0],
cmp=_sort_by_x,
reverse=False)
series[k] = v
weixin_items = {}
for k, v in series.items():
if key_desc:
k = key_desc.get(str(k), 'UNKOWN')
weixin_items[k] = [x[1] for x in v]
charts = []
charts.append(
generate_chart(query_name, x_axis, weixin_items, width='1000px'))
return render(req, 'yunying/mama/index.html', locals())