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 activity(req):
p_start_date, p_end_date, start_date, end_date = get_date_from_req(req)
sql = """
select
activity.activity_id,
ac.start_time,
ac.title,
sum(orders.total_fee) as sales,
count(orders.total_fee) as salenum
from
flashsale_order as orders
join shop_items_product as products on orders.item_id=products.id
join flashsale_activity_product as activity on activity.model_id = products.model_id
join flashsale_activity_entry as ac on ac.id = activity.activity_id
where orders.created > %s
and orders.created < %s
and products.model_id != 0
and orders.pay_time is not null
group by activity_id
order by sales desc
"""
items = execute_sql(get_cursor(), sql, [format_datetime(start_date), format_datetime(end_date)])
return render(req, 'yunying/product/activity.html', locals())
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 index(req):
p_start_date, p_end_date, start_date, end_date = get_date_from_req(req)
cid = req.GET.get('category') or None
sql = """
select item_id, title, count(*) as count
from flashsale_order
where pay_time >= %s and pay_time < %s
group by item_id
order by count desc
limit 1000
"""
sql = """
SELECT
flashsale_order.item_id as item_id,
flashsale_order.total_fee,
flashsale_order.title as title,
flashsale_order.pay_time,
supplychain_sale_category.cid as cid,
supplychain_sale_category.name as category_name,
count(*) as count
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
"""
if cid:
cid = get_children_cids(cid=cid)
cid = ', '.join(map(lambda x: '"%s"' % x, cid))
print cid
sql += 'and supplychain_sale_category.cid in (%s)' % cid
sql += """
group by flashsale_order.item_id
order by count desc
limit 1000
"""
products = execute_sql(get_cursor(), sql, [format_datetime(start_date), format_datetime(end_date)])
# def byfunc(item):
# category = get_root_category(item['cid'])
# item['category_name'] = category.name
# return category.name
# products = map(byfunc, products)
categories = SaleCategory.objects.filter(is_parent=True)
return render(req, 'yunying/product/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 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 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 index(req):
"""
红包+退款+退款补邮费+兑换订单+妈妈收益佣金=个人账户增加额+妈妈账户增加额+红包支出+个人账户消费支出+个人账户兑换订单支出
"""
p_start_date, p_end_date, start_date, end_date = get_date_from_req(req)
sql = """
SELECT
budget_log_type as category, sum(flow_amount)*0.01 as amount, budget_type
FROM
flashsale_userbudgetlog
WHERE
created > '{0}'
and created < '{1}'
group by budget_log_type, budget_type
""".format(p_start_date, p_end_date)
xiaolu_wallet = execute_sql(get_cursor(), sql)
for item in xiaolu_wallet:
item['category'] = dict(BudgetLog.BUDGET_LOG_CHOICES).get(item['category'])
xiaolu_wallet_in = [x for x in xiaolu_wallet if x['budget_type'] == 0]
xiaolu_wallet_out = [x for x in xiaolu_wallet if x['budget_type'] == 1]
sql = """
SELECT
carry_type as category, SUM(carry_num)*0.01 as amount
FROM
flashsale_xlmm_carry_record
WHERE
created > '{0}'
and created < '{1}'
AND STATUS = 2
GROUP BY
carry_type
""".format(p_start_date, p_end_date)
mama_wallet_in = execute_sql(get_cursor(), sql)
for item in mama_wallet_in:
item['category'] = dict(CarryRecord.CARRY_TYPES).get(item['category'])
sql = """
SELECT
sum(value)*0.01 as amount, cash_out_type as category
from xiaolumm_cashout
WHERE
created > '{0}'
and created < '{1}'
and status='approved'
GROUP BY
cash_out_type
""".format(p_start_date, p_end_date)
mama_wallet_out = execute_sql(get_cursor(), sql)
for item in mama_wallet_out:
item['category'] = dict(CashOut.TYPE_CHOICES).get(item['category'])
sql = """
SELECT
sum(amount)*0.01 as amount
FROM
flashsale_envelop
WHERE
created > '{0}'
and created < '{1}'
and status='confirm'
""".format(p_start_date, p_end_date)
envelope_out = execute_sql(get_cursor(), sql)
print envelope_out
return render(req, 'yunying/money/index.html', locals())
def rank(req):
p_start_date, p_end_date, start_date, end_date = get_date_from_req(req)
tab = req.GET.get('tab', 'total')
if tab == 'order':
sql = """
SELECT mama_id, SUM(carry_num)/100 as money FROM `flashsale_xlmm_order_carry`
where created > %s
and created < %s
and carry_num > 0
and status in (1,2)
group by mama_id
order by sum(carry_num) DESC
LIMIT 0, 100
"""
if tab == 'click':
sql = """
SELECT mama_id, SUM(total_value)/100 as money FROM `flashsale_xlmm_click_carry`
where created > %s
and created < %s
and total_value > 0
and status in (1,2)
group by mama_id
order by sum(total_value) DESC
LIMIT 0, 100
"""
if tab == 'invite':
sql = """
SELECT referal_from_mama_id as mama_id, count(*) as money FROM `flashsale_xlmm_referal_relationship`
where created > %s
and created < %s
group by referal_from_mama_id
order by count(*) desc
LIMIT 0, 100
"""
if tab == 'award':
sql = """
SELECT mama_id, SUM(carry_num)/100 as money FROM `flashsale_xlmm_award_carry`
where created > %s
and created < %s
and carry_num > 0
and status in (2)
and mama_id > 0
group by mama_id
order by sum(carry_num) DESC
LIMIT 0, 100
"""
if tab == 'total':
sql = """
SELECT mama_id, SUM(carry_num)/100 as money FROM `flashsale_xlmm_carry_record`
where created > %s
and created < %s
and status in (2)
GROUP by mama_id
order by sum(carry_num) desc
LIMIT 0, 100
"""
items = execute_sql(
get_cursor(), sql,
[format_datetime(start_date),
format_datetime(end_date)])
items = dict([(x['mama_id'], x['money']) for x in items])
mama_ids = [x for x in items.keys()]
sql = """
SELECT
xiaolumm_xiaolumama.id,
flashsale_customer.created,
flashsale_customer.mobile,
flashsale_customer.nick
FROM
`xiaolumm_xiaolumama`
JOIN flashsale_customer ON flashsale_customer.unionid = xiaolumm_xiaolumama.openid
WHERE
xiaolumm_xiaolumama.id IN %s
"""
if mama_ids:
mamas = execute_sql(get_cursor(), sql, [mama_ids])
for mama in mamas:
money = items[mama['id']]
if isinstance(money, dict):
continue
mama['money'] = money
items[mama['id']] = mama
items = sorted(items.items(), key=lambda x: x[1]['money'], reverse=True)
items = [x[1] for x in items]
return render(req, 'yunying/mama/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())
def new_mama(req):
p_start_date, p_end_date, start_date, end_date = get_date_from_req(req)
cursor = get_cursor()
sql = """
SELECT id, created, last_renew_type, openid FROM xiaolumm_xiaolumama
where created >= %s and created < %s
and charge_status='charged'
and last_renew_type in (3, 15, 183, 365)
"""
mamas = execute_sql(
cursor, sql, [format_datetime(start_date),
format_datetime(end_date)])
p_mamas = ReferalRelationship.objects\
.filter(created__gte=start_date).values('referal_from_mama_id')
p_mamas = [x['referal_from_mama_id'] for x in p_mamas]
xufei_mamas = PotentialMama.objects\
.filter(created__gte=start_date, is_full_member=True).values('potential_mama')
xufei_mamas = set([x['potential_mama'] for x in xufei_mamas])
click_mamas = CarryRecord.objects \
.filter(created__gte=start_date, carry_type=CarryRecord.CR_CLICK).values('mama_id')
click_mamas = set([x['mama_id'] for x in click_mamas])
open_app_mamas = MamaDailyAppVisit.objects.filter(created__gte=start_date)\
.exclude(device_type=MamaDailyAppVisit.DEVICE_MOZILLA).values('mama_id')
open_app_mamas = set([x['mama_id'] for x in open_app_mamas])
def byfunc(item):
return item['created']
def pfunc(items):
return len(list(set([x['id'] for x in items]) & set(p_mamas)))
def yaoqing_count_func(items):
la = [x['id'] for x in items]
lb = p_mamas
return len(filter(lambda x: x in la, lb))
def finish_task_func(items):
o_mamas = set([x['id'] for x in items])
return len(list(o_mamas & click_mamas))
def open_app_func(items):
return len(list(set([x['id'] for x in items]) & open_app_mamas))
def buyfunc(items):
count = 0
for item in items:
unionid = item['openid']
customer = Customer.objects.filter(unionid=unionid).first()
has_buy = SaleTrade.objects.filter(buyer_id=customer.id,
total_fee__gt=1).exists()
if has_buy:
count += 1
return count
def xufeifunc(items):
return len(list(set([x['id'] for x in items]) & xufei_mamas))
x_axis, new_chart_items = generate_chart_data(mamas,
xaris='created',
key=None,
yaris=len,
start_date=start_date,
end_date=end_date)
x_axis, chart_items = generate_chart_data(mamas,
xaris='created',
key=None,
yaris=pfunc,
start_date=start_date,
end_date=end_date)
# x_axis, yaoqing_count_chart_items = generate_chart_data(
# mamas, xaris='created', key=None, yaris=yaoqing_count_func, start_date=start_date, end_date=end_date)
# x_axis, buy_chart_items = generate_chart_data(
# mamas, xaris='created', key=None, yaris=buyfunc, start_date=start_date, end_date=end_date)
x_axis, open_app_chart_items = generate_chart_data(mamas,
xaris='created',
key=None,
yaris=open_app_func,
start_date=start_date,
end_date=end_date)
x_axis, xufei_chart_items = generate_chart_data(mamas,
xaris='created',
key=None,
yaris=xufeifunc,
start_date=start_date,
end_date=end_date)
x_axis, finish_task_chart_items = generate_chart_data(
mamas,
xaris='created',
key=None,
yaris=finish_task_func,
start_date=start_date,
end_date=end_date)
z_items = {}
yaoqing_data = chart_items and chart_items.values()[0]
# yaoqing_count_data = yaoqing_count_chart_items.values()[0]
new_mama_data = new_chart_items and new_chart_items.values()[0]
xufei_mama_data = xufei_chart_items and xufei_chart_items.values()[0]
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)