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 query(req):
sql = req.GET.get('sql') or ''
query_name = req.GET.get('query_name') or ''
if sql:
result = execute_sql(get_cursor(), sql)
return render(req, 'yunying/sql/query.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 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)
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 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 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 show(req):
mama_id = req.GET.get('mama_id') or None
customer = None
mama = None
if mama_id and len(mama_id) == 11:
mobile = mama_id
customer = Customer.objects.filter(mobile=mobile).first()
if customer:
mama = XiaoluMama.objects.filter(openid=customer.unionid).first()
mama_id = mama.id if mama else None
else:
mama = XiaoluMama.objects.filter(id=mama_id).first()
if mama:
customer = Customer.objects.filter(unionid=mama.openid).first()
if mama:
mama.last_renew_type = dict(XiaoluMama.RENEW_TYPE).get(
mama.last_renew_type)
if customer:
wx_fans = WeixinFans.objects.filter(unionid=customer.unionid)
orders = SaleOrder.objects.filter(buyer_id=customer.id) \
.exclude(pay_time__isnull=True).order_by('-created')
for order in orders:
order.status = dict(SaleOrder.ORDER_STATUS).get(order.status)
referal_mama = ReferalRelationship.objects.filter(
referal_to_mama_id=mama_id).first()
fans = ReferalRelationship.objects.filter(referal_from_mama_id=mama_id)
carry_record = CarryRecord.objects.filter(
mama_id=mama_id).order_by('-created')
award_carry = AwardCarry.objects.filter(
mama_id=mama_id).order_by('-created')
order_carry = OrderCarry.objects.filter(
mama_id=mama_id).order_by('-created')
click_carry = ClickCarry.objects.filter(
mama_id=mama_id).order_by('-created')
fortune = MamaFortune.objects.filter(mama_id=mama_id).first()
if fortune:
cash_num = fortune.cash_num_display()
else:
cash_num = 0
sql = """
SELECT * FROM flashsale_xlmm_mamadailyappvisit
where mama_id = %s
order by created desc
limit 10
"""
visit_record = execute_sql(get_cursor(), sql, [mama_id])
for item in visit_record:
item['device_type'] = dict(MamaDailyAppVisit.DEVICE_TYPES).get(
item['device_type'])
if not mama_id:
mama_id = ''
# sql = """
# select count(money) as rank from
# (SELECT SUM(carry_num) as money FROM flashsale_xlmm_carry_record
# where status in (1,2)
# group by mama_id
# order by money desc
# ) as rank
# where money > %s
# """
carry_total = sum(
[x.carry_num for x in carry_record if x.status in (1, 2)])
carry_total_confirm = sum(
[x.carry_num for x in carry_record if x.status in (2, )])
# rank = execute_sql(get_cursor(), sql, [carry_total])[0]
# score_all = int((1 - rank['rank'] / 20000.0) * 100) if carry_total > 0 else 0
# sql = """
# select count(money) as rank from
# (SELECT SUM(carry_num) as money FROM flashsale_xlmm_order_carry
# where status in (1,2)
# group by mama_id
# order by money desc
# ) as rank
# where money > %s
# """
# order_carry_total = sum([x.carry_num for x in order_carry if x.status in (1, 2)])
# rank = execute_sql(get_cursor(), sql, [order_carry_total])[0]
# score_order = int((1 - rank['rank'] / 20000.0) * 100) if order_carry_total > 0 else 0
# sql = """
# select count(money) as rank from
# (SELECT SUM(total_value) as money FROM flashsale_xlmm_click_carry
# where status in (1,2)
# group by mama_id
# order by money desc
# ) as rank
# where money > %s
# """
# click_carry_total = sum([x.total_value for x in click_carry if x.status in (1, 2)])
# rank = execute_sql(get_cursor(), sql, [click_carry_total])[0]
# score_click = int((1 - rank['rank'] / 20000.0) * 100) if click_carry_total > 0 else 0
# score_buy = 0
# sql = """
# select count(*) as rank from (
# SELECT count(*) as count FROM flashsale_xlmm_referal_relationship
# group by referal_from_mama_id
# ) as rank
# where count > %s
# """
# fans_total = fans.count()
# rank = execute_sql(get_cursor(), sql, [fans_total])[0]
# score_fans = int((1 - rank['rank'] / 20000.0) * 100) if fans_total > 0 else 0
# chart = {'name': 'name', 'width': '400px', 'score': score_all}
# chart['score'] = [
# score_click,
# score_order,
# # score_buy,
# score_fans,
# score_all,
# ]
return render(req, 'yunying/mama/show.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)
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})
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 table(req, table_name):
sql = 'desc %s.%s' % (db_name, table_name)
items = execute_sql(get_cursor(), sql)
return render(req, 'yunying/database/table.html', locals())
def index(req):
cursor = get_cursor()
sql = 'show tables from %s' % db_name
items = execute_sql(cursor, sql)
return render(req, 'yunying/database/index.html', locals())
def list(req):
q_customer = req.GET.get('customer')
q_xlmm = req.GET.get('xlmm')
q_page = req.GET.get('page') or 1
sql = """
SELECT
flashsale_customer.created,
flashsale_customer.nick,
flashsale_customer.mobile,
xiaolumm_xiaolumama.id as mama_id
FROM flashsale_customer
left join xiaolumm_xiaolumama on xiaolumm_xiaolumama.openid=flashsale_customer.unionid
where flashsale_customer.mobile != ''
"""
if q_xlmm == 'not':
sql += """
and xiaolumm_xiaolumama.id is null
"""
if q_xlmm == 'yes':
sql += """
and xiaolumm_xiaolumama.id is not null
"""
if q_xlmm in ['3', '15', '183', '365']:
sql += """
and xiaolumm_xiaolumama.charge_status = 'charged'
and xiaolumm_xiaolumama.last_renew_type = %s
""" % (q_xlmm)
if q_customer == 'has_buy':
sql += """
and flashsale_customer.first_paytime is not null
"""
if q_customer == 'no_buy':
sql += """
and flashsale_customer.first_paytime is null
"""
sql += """
order by flashsale_customer.created desc
limit 1000
"""
queryset = execute_sql(get_cursor(), sql)
p = Paginator(queryset, 50)
cur_page = p.page(q_page)
p.show_page_range = [
x for x in p.page_range if 3 >= (x - cur_page.number) >= -3
]
queryset = cur_page.object_list
for item in queryset:
mama_id = item.get('mama_id')
if not mama_id:
continue
sq = """
SELECT sum(carry_num) as money FROM flashsale_xlmm_order_carry
where mama_id = %s
and status in (1, 2)
"""
order_carrys = execute_sql(get_cursor(), sq, [mama_id])
order_carry = order_carrys[0]['money'] if order_carrys else None
item['order_carry'] = order_carry / 100 if order_carry else 0
sql1 = """
SELECT * FROM flashsale_xlmm_mamadailyappvisit
where mama_id = %s
order by created desc
limit 30
"""
visit_record = execute_sql(get_cursor(), sql1, [mama_id])
def func(item):
now = datetime.now()
delta = (now - item['created']).days
if delta <= 7:
return '7'
elif delta <= 14:
return '14'
elif delta <= 30:
return '30'
else:
return '>30'
visit_record = groupby(visit_record, func)
visit_record['7'] = len(visit_record.get('7', []))
visit_record['14'] = len(visit_record.get('14',
[])) + visit_record['7']
visit_record['30'] = len(visit_record.get('30',
[])) + visit_record['14']
item['visit'] = visit_record
return render(req, 'yunying/customer/list.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 index(self, request, *args, **kwargs):
"""
GET /rest/v2/mmcarry
小鹿妈妈累计收益(只计算返点和兑换订单利润)
"""
customer = Customer.getCustomerByUser(user=request.user)
mama = customer.getXiaolumm()
if not mama:
return Response({'code': 1, 'msg': '没有mama'})
result = []
sql = """
SELECT
bg.id, bg.created, bg.flow_amount, coupon.extras
FROM
flashsale_userbudgetlog AS bg
join flashsale_coupon_transfer_record as ctr on ctr.order_no=bg.uni_key
join flashsale_transfer_detail as ctd on ctd.transfer_id=ctr.id
join flashsale_user_coupon as coupon on coupon.id = ctd.coupon_id
WHERE
bg.customer_id = "{}"
AND bg.budget_log_type = 'exchg'
and bg.status = 0
and ctr.transfer_type = 8
and ctd.transfer_type = 8
""".format(customer.id)
items = execute_sql(get_cursor(conn='default'), sql)
items = process_items(items)
for item in items:
origin_price = item['origin_price']
profit = item['flow_amount'] - origin_price
result.append({
'type': '兑换订单',
'amount': profit,
'created': item['created'],
'budget_log_id': item['id']
})
sql = """
SELECT
bg.id, bg.created, bg.flow_amount, coupon.extras
FROM
flashsale_userbudgetlog AS bg
join flashsale_coupon_transfer_record as ctr on ctr.id=bg.referal_id
join flashsale_transfer_detail as ctd on ctd.transfer_id=ctr.id
join flashsale_user_coupon as coupon on coupon.id = ctd.coupon_id
WHERE
bg.customer_id = "{}"
and bg.budget_log_type = 'rtexchg'
""".format(customer.id)
items = execute_sql(get_cursor(conn='default'), sql)
items = process_items(items)
for item in items:
origin_price = item['origin_price']
profit = item['flow_amount'] - origin_price
result.append({
'type': '取消兑换订单',
'amount': -profit,
'created': item['created'],
'budget_log_id': item['id']
})
bgs = BudgetLog.objects.filter(budget_log_type=BudgetLog.BG_FANDIAN,
status=BudgetLog.CONFIRMED,
customer_id=customer.id)
for bg in bgs:
result.append({
'type': '返点',
'amount': bg.flow_amount,
'created': bg.created,
'budget_log_id': bg.id
})
result = sorted(result, key=lambda x: x['created'], reverse=True)
total = sum([x['amount'] for x in result])
queryset = self.paginate_queryset(result)
resp = self.get_paginated_response(queryset)
resp.data['total'] = total
return resp