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