def transfer_coupon(req):
p_start_date, p_end_date, start_date, end_date = get_date_from_req(req)
x_axis = []
stats_list = []
for day in reversed(range((end_date - start_date).days)):
cur_date = end_date - timedelta(days=day)
stats_list.append(calc_transfer_coupon_data(cur_date.date()))
x_axis.append(cur_date.strftime('%Y-%m-%d'))
name_maps = {
'coupon_sale_num': u'出券张数',
'coupon_sale_amount': u'总出券面额',
'coupon_used_num': u'兑换买货用券数',
'coupon_used_amount': u'买货券面额',
'order_mama_count': u'有收益妈妈数',
'elite_mama_count': u'累计妈妈数',
'new_elite_mama_count': u'新增妈妈',
'active_elite_mama_count': u'活跃妈妈数',
'coin_charge_num': u'小鹿币充值',
'coin_refund_num': u'小鹿币退款',
}
items_dict = {k: [] for k, v in name_maps.iteritems()}
for stats in stats_list:
for k, v in items_dict.iteritems():
v.append(int(stats[k]))
items_dict = dict([(name_maps[k], v) for k, v in items_dict.iteritems()])
charts = [generate_chart(u'精品流通券趋势', x_axis, items_dict, width='1000px')]
return render(req, 'yunying/mama/index.html', locals())
def show(req, id):
p_start_date, p_end_date, start_date, end_date = get_date_from_req(req)
product = Product.objects.get(id=id)
modelproduct = product.get_product_model()
if modelproduct:
saleproduct = modelproduct.saleproduct
sql = """
SELECT pay_time, count(DATE(pay_time)) as count
FROM flashsale_order
where item_id=%s
and pay_time > %s
and pay_time < %s
group by DATE(pay_time)
"""
items = execute_sql(get_cursor(), sql, [id, format_datetime(start_date), format_datetime(end_date)])
sql = """
SELECT flashsale_order.title,
shop_items_productsku.properties_name,
flashsale_order.total_fee,
count(*) as count
FROM flashsale_order
join shop_items_productsku on shop_items_productsku.id=flashsale_order.sku_id
where flashsale_order.item_id=%s
and flashsale_order.pay_time > %s
and flashsale_order.pay_time < %s
group by flashsale_order.sku_id
order by flashsale_order.created desc
"""
order_items = execute_sql(get_cursor(), sql, [id, format_datetime(start_date), format_datetime(end_date)])
order_items = [x.values()+[x['total_fee']*x['count']] for x in order_items]
weixin_items = {
'销量': [int(x['count']) for x in items],
}
x_axis = [x['pay_time'].strftime('%Y-%m-%d') for x in items if x['pay_time'] is not None]
charts = []
charts.append(generate_chart(product.name, x_axis, weixin_items, width='1000px'))
return render(req, 'yunying/product/show.html', locals())
def click(req):
p_start_date, p_end_date, start_date, end_date = get_date_from_req(req)
sql = """
SELECT DATE(created) as date, count(*) as count FROM flashsale_xlmm_unique_visitor
where created > %s and created < %s
group by DATE(created)
order by created
"""
queryset = execute_sql(
get_cursor(), sql,
[format_datetime(start_date),
format_datetime(end_date)])
x_axis = [format_date(x['date']) for x in queryset]
sql = """
SELECT DATE(pay_time) as date, count(*) as count FROM flashsale_trade
where extras_info regexp '.*"mm_linkid": "?[1-9]+"?'
and pay_time > %s and pay_time < %s
and pay_time is not null
group by DATE(pay_time)
"""
orders = execute_sql(
get_cursor(), sql,
[format_datetime(start_date),
format_datetime(end_date)])
items = {
'click': [int(x['count']) for x in queryset],
'orders': [int(x['count']) for x in orders],
}
ratio_data = []
for i, d in enumerate(items['click']):
n = round((items['orders'][i] * 100.0 / d), 2)
ratio_data.append(n)
charts = []
charts.append(generate_chart('UV', x_axis, items, width='1000px'))
return render(req, 'yunying/mama/click.html', locals())
def index(req):
p_start_date, p_end_date, start_date, end_date = get_date_from_req(req)
key = req.GET.get('key', 'created')
sql = """
SELECT * FROM flashsale_xlmm_mamadailyappvisit
where created > %s
and created < %s
"""
items = execute_sql(cursor, sql, [start_date, end_date])
items = process_data(items, lambda x: x[key])
weixin_items = {
'小鹿妈妈日活': [int(x[1]) for x in items],
}
x_axis = [x[0] for x in items if x[0] is not None]
charts = []
charts.append(generate_chart('xxx', x_axis, weixin_items, width='1000px'))
return render(req, 'yunying/mama/index.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 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())
finish_task_data = finish_task_chart_items and finish_task_chart_items.values(
)[0]
open_app_task_data = open_app_chart_items and open_app_chart_items.values(
)[0]
# buy_mama_data = buy_chart_items.values()[0]
ratio_data = []
for i, item in enumerate(new_mama_data):
try:
ratio = round(float(yaoqing_data[i] * 100.0 / item), 2)
except Exception, e:
ratio = 0
ratio_data.append(ratio)
charts = [generate_chart('小鹿妈妈', x_axis, z_items, width='1000px')]
y1 = new_mama_data and max(new_mama_data) or 0 + 100
y1_interval = int(y1 / 10)
y2 = ratio_data and max(ratio_data) or 0 + 10
y2_interval = int(y2 / 10)
return render(req, 'yunying/mama/new_mama.html', locals())
def _sort_by_x(x, y):
try:
x = int(x)
y = int(y)
except Exception:
pass
def index(req):
now = datetime.now()
p_start_date = req.GET.get('start_date', '2016-07-01')
p_end_date = req.GET.get('end_date',
'%s-%s-%s' % (now.year, now.month, now.day + 1))
start_date = datetime.strptime(p_start_date, '%Y-%m-%d')
end_date = datetime.strptime(p_end_date, '%Y-%m-%d')
cursor = connections['product'].cursor()
where = ' created > "{0}" and created < "{1}" '.format(
p_start_date, p_end_date)
sql = """SELECT DATE(created) as day, count(DATE(created))
FROM flashsale_customer where {0} group by DATE(created);""".format(
where)
customers = execute_sql(cursor, sql)
sql = """SELECT DATE(created), count(DATE(created))
FROM xiaolumm_xiaolumama WHERE {0} GROUP by DATE(created) """.format(
where)
xiaolumm = execute_sql(cursor, sql)
sql = """SELECT DATE(created), count(DATE(created))
FROM flashsale_trade where {0} group by DATE(created)""".format(
where)
trades_all = execute_sql(cursor, sql)
sql = """SELECT DATE(created), count(DATE(created))
FROM flashsale_trade where pay_time is not null and {0} group by DATE(created)""".format(
where)
trades_pay = execute_sql(cursor, sql)
sql = """
SELECT DATE(flashsale_trade.pay_time), count(DATE(flashsale_trade.pay_time))
FROM flashsale_trade
join flashsale_customer on flashsale_customer.id=flashsale_trade.buyer_id
join xiaolumm_xiaolumama on flashsale_customer.unionid=xiaolumm_xiaolumama.openid
where flashsale_trade.created > "{0}"
and flashsale_trade.created < "{1}"
and flashsale_trade.pay_time is not null
group by DATE(flashsale_trade.created)
""".format(p_start_date, p_end_date)
xiaolumm_trades = execute_sql(cursor, sql)
sql = """
SELECT DATE(subscribe_time), count(DATE(subscribe_time))
FROM shop_weixin_fans group by DATE(subscribe_time)
"""
weixin_fans = execute_sql(cursor, sql)
customer_items = {
'新增小鹿妈妈': [int(x[1]) for x in xiaolumm],
'新增用户数': [int(x[1]) for x in customers],
}
trade_items = {
'付款订单数': [int(x[1]) for x in trades_pay],
'所有订单（含未付款）': [int(x[1]) for x in trades_all],
'来自小鹿妈妈订单': [int(x[1]) for x in xiaolumm_trades],
}
weixin_items = {
'小鹿美美粉丝': [int(x[1]) for x in weixin_fans],
}
x_axis = [
x.strftime('%Y-%m-%d') for x in generate_date(start_date, end_date)
]
x1_axis = [
x[0].strftime('%Y-%m-%d') for x in weixin_fans if x[0] is not None
]
charts = []
charts.append(generate_chart('customer', x_axis, customer_items))
charts.append(generate_chart('trade', x_axis, trade_items))
charts.append(generate_chart('公众号', x1_axis, weixin_items, width='1200px'))
return render(req, 'yunying/customer/index.html', {'charts': charts})