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 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 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 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)