Ejemplo n.º 1
0
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())
Ejemplo n.º 2
0
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())
Ejemplo n.º 3
0
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())
Ejemplo n.º 4
0
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())
Ejemplo n.º 5
0
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())
Ejemplo n.º 6
0
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())
Ejemplo n.º 7
0
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())
Ejemplo n.º 8
0
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())
Ejemplo n.º 9
0
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())
Ejemplo n.º 10
0
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())
Ejemplo n.º 11
0
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())
Ejemplo n.º 12
0
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())
Ejemplo n.º 13
0
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)
Ejemplo n.º 14
0
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})
Ejemplo n.º 15
0
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())
Ejemplo n.º 16
0
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())
Ejemplo n.º 17
0
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())
Ejemplo n.º 18
0
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())
Ejemplo n.º 19
0
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())
Ejemplo n.º 20
0
    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