def get(self, request):
sdate = request.GET.get('sdate',
(datetime.today() -
timedelta(days=1)).strftime('%Y-%m-%d'))
edate = request.GET.get('edate',
(datetime.today() -
timedelta(days=1)).strftime('%Y-%m-%d'))
transform_dict = {'install': 0, 'register': 0}
sql = """SELECT SUM(cpd_data) AS install, SUM(cpa_data) AS register, report_date
FROM cdd_channel_report_day
WHERE report_date >= '{sdate}' AND report_date <= '{edate}' AND platform <> 1
GROUP BY report_date;""".format(sdate=sdate, edate=edate)
cursor = connection.cursor()
cursor.execute(sql)
rows = namedtuplefetchall(cursor)
if rows:
transform_dict['install'] = rows[0].install
transform_dict['register'] = rows[0].register
chart_data = [{
'action': '安装',
'value': transform_dict['install']
}, {
'action': '注册',
'value': transform_dict['register']
}]
data = {'chart_data': chart_data}
return Response(data)
def get(self, requset):
cursor = connection.cursor()
month_dict = {}
for i in range(12):
key = '{i}月'.format(i=i)
month_dict[key] = 0
sql = """SELECT EXTRACT(MONTH from report_date) as month, SUM(cps_data) as cps
FROM cdd_channel_report_day
GROUP BY EXTRACT(MONTH from report_date);"""
cursor.execute(sql)
rows = namedtuplefetchall(cursor)
for row in rows:
key = ''.join([str(int(row.month)), '月'])
month_dict[key] = row.cps
chart_data = []
for key, value in month_dict.items():
d = {'month': key, 'money': value / 100}
chart_data.append(d)
data = {'chart_data': chart_data}
return Response(data)
def get_channel_price(cursor, records):
# 获取渠道价格
for key, record in records.items():
sql = """SELECT cdd_channel_info.commission AS channel_commission,
cdd_proxy_info.commission AS proxy_commission,
cdd_proxy_info.proxy_type
FROM cdd_channel_info
JOIN cdd_proxy_info ON cdd_channel_info.proxy_id = cdd_proxy_info.id
WHERE cdd_channel_info.id = {channel_id}""".format(
channel_id=key[1])
cursor.execute(sql)
rows = namedtuplefetchall(cursor)
if not rows:
continue
if rows[0].proxy_type == 1:
record['cps_rate'] = rows[0].channel_commission
elif rows[0].proxy_type == 2:
record['cpa_price'] = rows[0].proxy_commission
elif rows[0].proxy_type == 3:
record['cpd_price'] = rows[0].proxy_commission
elif rows[0].proxy_type == 4:
record['cpc_price'] = rows[0].proxy_commission
elif rows[0].proxy_type == 5:
record['cpm_price'] = rows[0].proxy_commission
record['proxy_type'] = rows[0].proxy_type
return records
def to_representation(self, instance):
ret = super(UserSerializer, self).to_representation(instance)
cursor = connection.cursor()
sql = """SELECT cdd_proxy_info.proxy_name, cdd_bd_info.bd_name
FROM cdd_channel_info
JOIN cdd_proxy_info ON cdd_channel_info.proxy_id = cdd_proxy_info.id
JOIN cdd_bd_info ON cdd_proxy_info.bd_id = cdd_bd_info.id
WHERE cdd_channel_info.id = {channel}""".format(channel=instance.channel)
cursor.execute(sql)
rows = namedtuplefetchall(cursor)
user = self.context['user']
if user.role == 'channel':
ret.update({
'nick_name': ''.join([instance.phone_number[0:3], '****', instance.phone_number[7:]]),
'phone_number': ''.join([instance.phone_number[0:3], '****', instance.phone_number[7:]])
})
invite_count = User.objects.filter(inviter_id=instance.id).count()
statistic = UserWallet.objects.filter(user_id=instance.id).aggregate(balance=Sum('balance'),
balance_freeze=Sum('balance_freeze'))
ret.update({
'balance': statistic.get('balance'),
'balance_freeze': statistic.get('balance_freeze'),
'invite_count': invite_count,
'proxy_name': rows[0].proxy_name if rows else '',
'bd_name': rows[0].bd_name if rows else '',
'reg_time': instance.reg_time.strftime('%Y-%m-%d %H:%M:%S'),
'last_login_time': instance.last_login_time.strftime('%Y-%m-%d %H:%M:%S')
})
return ret
def get_proxy_data(cursor, records, date_str):
sql = """SELECT platform, proxy_id, SUM(cps_data) AS cps_data, SUM(cpa_data) AS cpa_data, SUM(cpd_data) AS cpd_data,
SUM(cpc_data) AS cpc_data, SUM(cpm_data) AS cpm_data, SUM(pay_user) AS pay_user,
SUM(order_count) AS order_count, SUM(coupon_fee) AS coupon_fee, SUM(charge_money) AS charge_money
FROM cdd_channel_report_day
JOIN cdd_channel_info ON cdd_channel_report_day.channel_id = cdd_channel_info.id
WHERE report_date = '{date}'
GROUP BY platform, proxy_id;""".format(date=date_str)
cursor.execute(sql)
rows = namedtuplefetchall(cursor)
for row in rows:
key = (row.platform, row.proxy_id)
if key not in records:
_default = copy.deepcopy(default_data)
records.setdefault(key, _default)
records[key]['cps_data'] = row.cps_data
records[key]['cpa_data'] = row.cpa_data
records[key]['cpd_data'] = row.cpd_data
records[key]['cpc_data'] = row.cpc_data
records[key]['cpm_data'] = row.cpm_data
records[key]['pay_user'] = row.pay_user
records[key]['order_count'] = row.order_count
records[key]['coupon_fee'] = row.coupon_fee
records[key]['charge_money'] = row.charge_money
return records
def number_bet_data_analysis(date_str=None):
if not date_str:
date_str = (datetime.today() - timedelta(days=1)).strftime('%Y-%m-%d')
cursor = connection.cursor()
sql = """SELECT SUM(total_money) AS total_money, cdd_lottery.lottery_name, lottery_period
FROM cdd_lottery_order_ticket
JOIN cdd_lottery ON cdd_lottery_order_ticket.lottery_alias = cdd_lottery.lottery_alias
WHERE order_date = '{date}' AND ticket_status = '0000'
GROUP BY cdd_lottery.lottery_name, lottery_period;""".format(
date=date_str)
cursor.execute(sql)
rows = namedtuplefetchall(cursor)
obj_list = []
for row in rows:
try:
obj = NumberBetDataDay.objects.get(
lottery_type=row.lottery_name,
lottery_period=row.lottery_period,
report_date=datetime.strptime(date_str, '%Y-%m-%d'))
obj.total_money = row.total_money
obj.save()
except NumberBetDataDay.DoesNotExist:
obj = NumberBetDataDay(lottery_type=row.lottery_name,
lottery_period=row.lottery_period,
total_money=row.total_money,
report_date=datetime.strptime(
date_str, '%Y-%m-%d'))
obj_list.append(obj)
NumberBetDataDay.objects.bulk_create(obj_list)
return
def sports_bet_data_analysis(date_str=None):
if not date_str:
date_str = (datetime.today() - timedelta(days=1)).strftime('%Y-%m-%d')
cursor = connection.cursor()
sql = """SELECT SUM(bet_money) AS total_money, game AS lottery_type
FROM cdd_game_order
WHERE order_date = '{date}' AND order_status IN (2, 5, 6)
GROUP BY game""".format(date=date_str)
cursor.execute(sql)
rows = namedtuplefetchall(cursor)
obj_list = []
for row in rows:
try:
obj = SportsBetDataDay.objects.get(lottery_type=row.lottery_type,
report_date=datetime.strptime(
date_str, '%Y-%m-%d'))
obj.total_money = row.total_money
obj.save()
except SportsBetDataDay.DoesNotExist:
obj = SportsBetDataDay(lottery_type=row.lottery_type,
total_money=row.total_money,
report_date=datetime.strptime(
date_str, '%Y-%m-%d'))
obj_list.append(obj)
SportsBetDataDay.objects.bulk_create(obj_list)
return
def pay_data_analysis(date_str=None):
if not date_str:
date_str = (datetime.today() - timedelta(days=1)).strftime('%Y-%m-%d')
cursor = connections['pay'].cursor()
sql = """SELECT SUM(total_fee) AS total_money, service_config.srv_name, pay_type
FROM orders
JOIN service_config ON orders.service_id = service_config.id
WHERE created_at::date = '{date}' AND status = 'success'
GROUP BY service_config.srv_name, pay_type""".format(
date=date_str)
cursor.execute(sql)
rows = namedtuplefetchall(cursor)
obj_list = []
for row in rows:
try:
obj = PayDataDay.objects.get(pay_type=row.pay_type,
srv_name=row.srv_name,
report_date=datetime.strptime(
date_str, '%Y-%m-%d'))
obj.total_money = row.total_money
obj.save()
except PayDataDay.DoesNotExist:
obj = PayDataDay(pay_type=row.pay_type,
srv_name=row.srv_name,
total_money=row.total_money,
report_date=datetime.strptime(
date_str, '%Y-%m-%d'))
obj_list.append(obj)
PayDataDay.objects.bulk_create(obj_list)
return
def get(self, request):
try:
lottery = request.GET['lottery']
keyword = request.GET['keyword']
except Exception as e:
return Response({'message': '参数不完整！'})
page = int(request.GET.get('page', 1))
page_size = settings.REST_FRAMEWORK['PAGE_SIZE']
offset = (page - 1) * page_size
cursor = connection.cursor()
if lottery == 'number':
data_sql = """SELECT user_id, cdd_lottery_order.id AS order_pk, order_id, lottery_alias, amount, total_money, bonus_money, order_status, order_time
FROM cdd_lottery_order
JOIN cdd_user ON cdd_lottery_order.user_id = cdd_user.id
WHERE CAST(cdd_user.id AS TEXT) LIKE '{keyword}' OR cdd_user.nick_name ~ '{keyword}' OR
cdd_user.id_card ~ '{keyword}' OR cdd_user.bank_card ~ '{keyword}' OR
order_id ~ '{keyword}' OR lottery_period ~ '{keyword}'
LIMIT {page_size} OFFSET {offset}""".format(keyword=keyword, page_size=page_size, offset=offset)
count_sql = """SELECT COUNT(1) AS count
FROM cdd_lottery_order
JOIN cdd_user ON cdd_lottery_order.user_id = cdd_user.id
WHERE CAST(cdd_user.id AS TEXT) LIKE '{keyword}' OR cdd_user.nick_name ~ '{keyword}' OR
cdd_user.id_card ~ '{keyword}' OR cdd_user.bank_card ~ '{keyword}' OR
order_id ~ '{keyword}' OR lottery_period ~ '{keyword}'""".format(keyword=keyword)
else:
data_sql = """SELECT user_id, cdd_game_order.id AS order_pk, order_id, game, bet_multi, bet_type, bet_money, bonus_money, order_status, order_time
FROM cdd_game_order
JOIN cdd_user ON cdd_game_order.user_id = cdd_user.id
WHERE CAST(cdd_user.id AS TEXT) LIKE '{keyword}' OR cdd_user.nick_name ~ '{keyword}' OR
cdd_user.id_card ~ '{keyword}' OR cdd_user.bank_card ~ '{keyword}' OR
order_id ~ '{keyword}'
LIMIT {page_size} OFFSET {offset}""".format(keyword=keyword, page_size=page_size, offset=offset)
count_sql = """SELECT COUNT(1) AS count
FROM cdd_game_order
JOIN cdd_user ON cdd_game_order.user_id = cdd_user.id
WHERE CAST(cdd_user.id AS TEXT) LIKE '{keyword}' OR cdd_user.nick_name ~ '{keyword}' OR
cdd_user.id_card ~ '{keyword}' OR cdd_user.bank_card ~ '{keyword}' OR
order_id ~ '{keyword}'""".format(keyword=keyword)
cursor.execute(data_sql)
data_rows = dictfetchall(cursor)
for row in data_rows:
row['order_time'] = row['order_time'].strftime('%Y-%m-%d %H:%M:%S')
cursor.execute(count_sql)
count_rows = namedtuplefetchall(cursor)
count = count_rows[0].count
data = {
'count': count,
'data': data_rows
}
return Response(data)
def get_pay_user(cursor, records, date_str):
# 数字彩付费人次
sql = """SELECT COUNT(DISTINCT cdd_lottery_order_ticket.user_id) AS user_count, cdd_user.platform, cdd_user.channel,
proxy_type
FROM cdd_lottery_order_ticket
JOIN cdd_user ON cdd_lottery_order_ticket.user_id = cdd_user.id
JOIN cdd_channel_info ON cdd_user.channel = cdd_channel_info.id
JOIN cdd_proxy_info ON cdd_channel_info.proxy_id = cdd_proxy_info.id
WHERE cdd_lottery_order_ticket.order_date = '{date}' AND cdd_lottery_order_ticket.ticket_status = '0000'
GROUP BY cdd_user.platform, cdd_user.channel, proxy_type;""".format(
date=date_str)
cursor.execute(sql)
rows = namedtuplefetchall(cursor)
for row in rows:
key = (row.platform, row.channel)
if key not in records:
_default = copy.deepcopy(default_data)
records.setdefault(key, _default)
records[key]['pay_user'] = row.user_count
records[key]['proxy_type'] = row.proxy_type
# 竞技彩付费人次
sql = """SELECT COUNT(DISTINCT cdd_game_order.user_id) AS user_count, cdd_user.platform, cdd_user.channel,
proxy_type
FROM cdd_game_order
JOIN cdd_user ON cdd_game_order.user_id = cdd_user.id
JOIN cdd_channel_info ON cdd_user.channel = cdd_channel_info.id
JOIN cdd_proxy_info ON cdd_channel_info.proxy_id = cdd_proxy_info.id
WHERE order_date = '{date}' AND order_status IN (2, 5, 6)
GROUP BY cdd_user.platform, cdd_user.channel, proxy_type;""".format(
date=date_str)
cursor.execute(sql)
rows = namedtuplefetchall(cursor)
for row in rows:
key = (row.platform, row.channel)
if key not in records:
_default = copy.deepcopy(default_data)
records.setdefault(key, _default)
records[key]['pay_user'] += row.user_count
records[key]['proxy_type'] = row.proxy_type
return records
def get_cps_data(cursor, records, date_str):
# 数字彩订单总额
sql = """SELECT cdd_user.platform, cdd_user.channel, SUM(cdd_lottery_order_ticket.total_money) AS total_money,
proxy_type
FROM cdd_lottery_order_ticket
JOIN cdd_user ON cdd_lottery_order_ticket.user_id = cdd_user.id
JOIN cdd_channel_info ON cdd_user.channel = cdd_channel_info.id
JOIN cdd_proxy_info ON cdd_channel_info.proxy_id = cdd_proxy_info.id
WHERE cdd_lottery_order_ticket.order_date = '{date}' AND cdd_lottery_order_ticket.ticket_status = '0000'
GROUP BY cdd_user.platform, cdd_user.channel, proxy_type;""".format(
date=date_str)
cursor.execute(sql)
rows = namedtuplefetchall(cursor)
for row in rows:
key = (row.platform, row.channel)
if key not in records:
_default = copy.deepcopy(default_data)
records.setdefault(key, _default)
records[key]['cps_data'] = row.total_money
records[key]['proxy_type'] = row.proxy_type
# 竞技彩订单总额
sql = """SELECT cdd_user.platform, cdd_user.channel, SUM(cdd_game_order.bet_money) AS total_money, proxy_type
FROM cdd_game_order
JOIN cdd_user ON cdd_game_order.user_id = cdd_user.id
JOIN cdd_channel_info ON cdd_user.channel = cdd_channel_info.id
JOIN cdd_proxy_info ON cdd_channel_info.proxy_id = cdd_proxy_info.id
WHERE cdd_game_order.order_date = '{date}' AND cdd_game_order.order_status IN (2, 5, 6)
GROUP BY cdd_user.platform, cdd_user.channel, proxy_type;""".format(
date=date_str)
cursor.execute(sql)
rows = namedtuplefetchall(cursor)
for row in rows:
key = (row.platform, row.channel)
if key not in records:
_default = copy.deepcopy(default_data)
records.setdefault(key, _default)
records[key]['cps_data'] += row.total_money
records[key]['proxy_type'] = row.proxy_type
return records
def get(self, request):
cursor = connection.cursor()
date_str = datetime.today().strftime('%Y-%m-%d')
register_date_str = (datetime.today() -
timedelta(days=1)).strftime('%Y-%m-%d')
platform_dict = {1: 0, 2: 0, 3: 0}
sql = """SELECT COUNT(1) AS count, platform
FROM cdd_user
WHERE reg_time::DATE = '{date}'
GROUP BY platform;""".format(date=date_str)
cursor.execute(sql)
rows = namedtuplefetchall(cursor)
total_count = 0
for row in rows:
total_count += row.count
platform_dict[row.platform] = row.count
sql = """SELECT COUNT(DISTINCT user_id) AS login_count
FROM cdd_user_login_log
WHERE login_time::DATE = '{login_date}'
AND user_id IN (SELECT id FROM cdd_user WHERE reg_time::DATE = '{register_date}');
""".format(login_date=date_str, register_date=register_date_str)
cursor.execute(sql)
login_count = namedtuplefetchall(cursor)[0].login_count
data = {
'h5_count': platform_dict[1],
'android_count': platform_dict[2],
'ios_count': platform_dict[3],
'total_count': total_count,
'rate':
round(login_count / total_count * 100, 2) if total_count else 0
}
return Response(data)
def to_representation(self, instance):
ret = super(UserDataDaySerializer, self).to_representation(instance)
cursor = connection.cursor()
sql = """SELECT cdd_proxy_info.proxy_name, cdd_bd_info.bd_name
FROM cdd_channel_info
JOIN cdd_proxy_info ON cdd_channel_info.proxy_id = cdd_proxy_info.id
JOIN cdd_bd_info ON cdd_proxy_info.bd_id = cdd_bd_info.id
WHERE cdd_channel_info.id = {channel}""".format(
channel=instance.channel_id)
cursor.execute(sql)
rows = namedtuplefetchall(cursor)
ret.update({
'proxy_name':
rows[0].proxy_name,
'bd_name':
rows[0].bd_name,
'day1':
round(instance.day1 / instance.cpa_data *
100, 2) if instance.cpa_data else 0,
'day3':
round(instance.day3 / instance.cpa_data *
100, 2) if instance.cpa_data else 0,
'day7':
round(instance.day7 / instance.cpa_data *
100, 2) if instance.cpa_data else 0,
'day15':
round(instance.day15 / instance.cpa_data *
100, 2) if instance.cpa_data else 0,
'day30':
round(instance.day30 / instance.cpa_data *
100, 2) if instance.cpa_data else 0,
'day60':
round(instance.day60 / instance.cpa_data *
100, 2) if instance.cpa_data else 0,
'day90':
round(instance.day90 / instance.cpa_data *
100, 2) if instance.cpa_data else 0,
'arpu':
round(instance.cps_data / instance.cpa_data /
100, 2) if instance.cpa_data else 0
})
return ret
def get_cpc_data(cursor, records, date_str):
sql = """SELECT COUNT(1) AS cpc_data, cdd_event_info.platform, cdd_event_info.channel_id, proxy_type
FROM cdd_event_info
JOIN cdd_channel_info ON cdd_event_info.channel_id = cdd_channel_info.id
JOIN cdd_proxy_info ON cdd_channel_info.proxy_id = cdd_proxy_info.id
WHERE cdd_event_info.created_at::DATE = '{date}' AND cdd_event_info.id = 1
GROUP BY cdd_event_info.platform, cdd_event_info.channel_id, proxy_type;""".format(
date=date_str)
cursor.execute(sql)
rows = namedtuplefetchall(cursor)
for row in rows:
key = (row.platform, row.channel_id)
if key not in records:
_default = copy.deepcopy(default_data)
records.setdefault(key, _default)
records[key]['cpc_data'] = row.cpc_data
records[key]['proxy_type'] = row.proxy_type
return records
def get_cpa_data(cursor, records, date_str):
# 用户数
sql = """SELECT COUNT(cdd_user.id) AS user_count, platform, channel, proxy_type
FROM cdd_user
JOIN cdd_channel_info ON cdd_user.channel = cdd_channel_info.id
JOIN cdd_proxy_info ON cdd_channel_info.proxy_id = cdd_proxy_info.id
WHERE reg_time::DATE = '{date}'
GROUP BY platform, channel, cdd_proxy_info.proxy_type;""".format(
date=date_str)
cursor.execute(sql)
rows = namedtuplefetchall(cursor)
for row in rows:
key = (row.platform, row.channel)
if key not in records:
_default = copy.deepcopy(default_data)
records.setdefault(key, _default)
records[key]['cpa_data'] = row.user_count
records[key]['proxy_type'] = row.proxy_type
return records
def get_coupon_fee(cursor, records, date_str):
# 活动成本
sql = """SELECT SUM(face) AS total_money, cdd_user.platform, cdd_user.channel, proxy_type
FROM cdd_user_coupon JOIN cdd_user ON cdd_user_coupon.user_id = cdd_user.id
JOIN cdd_channel_info ON cdd_user.channel = cdd_channel_info.id
JOIN cdd_proxy_info ON cdd_channel_info.proxy_id = cdd_proxy_info.id
WHERE used_date = '{date}' AND coupon_status = 1
GROUP BY cdd_user.platform, cdd_user.channel, proxy_type;""".format(
date=date_str)
cursor.execute(sql)
rows = namedtuplefetchall(cursor)
for row in rows:
key = (row.platform, row.channel)
if key not in records:
_default = copy.deepcopy(default_data)
records.setdefault(key, _default)
records[key]['coupon_fee'] = row.total_money
records[key]['proxy_type'] = row.proxy_type
return records
def get_charge_money(cursor, records, date_str):
# 充值总额
sql = """SELECT SUM(charge_money) AS total_money, cdd_user.platform, cdd_user.channel, proxy_type
FROM cdd_wallet_recharge
JOIN cdd_user ON cdd_wallet_recharge.user_id = cdd_user.id
JOIN cdd_channel_info ON cdd_user.channel = cdd_channel_info.id
JOIN cdd_proxy_info ON cdd_channel_info.proxy_id = cdd_proxy_info.id
WHERE order_time::DATE = '{date}' AND pay_status = 1
GROUP BY cdd_user.platform, cdd_user.channel, proxy_type;""".format(
date=date_str)
cursor.execute(sql)
rows = namedtuplefetchall(cursor)
for row in rows:
key = (row.platform, row.channel)
if key not in records:
_default = copy.deepcopy(default_data)
records.setdefault(key, _default)
records[key]['charge_money'] = row.total_money
records[key]['proxy_type'] = row.proxy_type
return records
def get(self, request):
cursor = connection.cursor()
date_str = datetime.today().strftime('%Y-%m-%d')
platform_dict = {1: 0, 2: 0, 3: 0}
# 统计订单金额
sql = """SELECT SUM(bet_money) AS money, cdd_user.platform
FROM cdd_game_order
JOIN cdd_user ON cdd_game_order.user_id = cdd_user.id
WHERE order_date = '{date}' AND order_status IN (2, 5, 6)
GROUP BY cdd_user.platform;""".format(date=date_str)
cursor.execute(sql)
rows = namedtuplefetchall(cursor)
total_money = 0
for row in rows:
total_money += row.money
platform_dict[row.platform] = row.money
# 统计下单人次
sql = """SELECT COUNT(DISTINCT cdd_game_order.user_id) AS user_count
FROM cdd_game_order
JOIN cdd_user ON cdd_game_order.user_id = cdd_user.id
WHERE cdd_game_order.order_date = '{date}';""".format(
date=date_str)
cursor.execute(sql)
user_count = namedtuplefetchall(cursor)[0].user_count
# 统计订单数
sql = """SELECT COUNT(1) AS order_count
FROM cdd_game_order
WHERE order_date = '{date}';""".format(date=date_str)
cursor.execute(sql)
order_count = namedtuplefetchall(cursor)[0].order_count
# 统计柱状图
date_dict = {}
for i in range(1, 31):
key = (datetime.today() - timedelta(days=i)).strftime('%Y-%m-%d')
date_dict[key] = 0
start_date = (datetime.today() -
timedelta(days=31)).strftime('%Y-%m-%d')
end_date = (datetime.today() - timedelta(days=1)).strftime('%Y-%m-%d')
sql = """SELECT SUM(total_money) / 100 AS money, CAST(report_date AS TEXT) AS date_str
FROM cdd_sports_bet_report_day
WHERE report_date >= '{start_date}' AND report_date <= '{end_date}'
GROUP BY report_date;""".format(start_date=start_date,
end_date=end_date)
cursor.execute(sql)
rows = namedtuplefetchall(cursor)
for row in rows:
date_dict[row.date_str] = row.money
chart_data = []
for key, value in date_dict.items():
d = {'date': key, 'money': value}
chart_data.append(d)
data = {
'total_money': total_money,
'h5_money': platform_dict[1],
'android_money': platform_dict[2],
'ios_money': platform_dict[3],
'user_count': user_count,
'order_count': order_count,
'chart_data': chart_data
}
return Response(data)
def get(self, request):
sdate = request.GET.get('sdate',
(datetime.today() -
timedelta(days=1)).strftime('%Y-%m-%d'))
edate = request.GET.get('edate',
(datetime.today() -
timedelta(days=1)).strftime('%Y-%m-%d'))
lottery_dict = {
'FT': 0,
'BT': 0,
'dlc': 0,
'JXK3': 0,
'3d': 0,
'dlt': 0,
'ssq': 0
}
# 统计数字彩
sql = """SELECT SUM(total_money) AS money, lottery_alias
FROM cdd_lottery_order
WHERE order_date >= '{sdate}' AND order_date <= '{edate}'
GROUP BY lottery_alias;""".format(sdate=sdate, edate=edate)
cursor = connection.cursor()
cursor.execute(sql)
rows = namedtuplefetchall(cursor)
number_money = 0
for row in rows:
number_money += row.money
lottery_dict[row.lottery_alias] = row.money
# 统计竞技彩
sql = """SELECT SUM(bet_money) AS money, game AS lottery_alias
FROM cdd_game_order
WHERE order_date >= '{sdate}' AND order_date <= '{edate}'
GROUP BY game;""".format(sdate=sdate, edate=edate)
cursor = connection.cursor()
cursor.execute(sql)
rows = namedtuplefetchall(cursor)
sports_money = 0
for row in rows:
sports_money += row.money
lottery_dict[row.lottery_alias] = row.money
total_money = number_money + sports_money
chart_data = [{
'item': '竞彩足球',
'count': lottery_dict['FT']
}, {
'item': '竞彩篮球',
'count': lottery_dict['BT']
}, {
'item': '多乐彩',
'count': lottery_dict['dlc']
}, {
'item': "新快3",
'count': lottery_dict['JXK3']
}, {
'item': "福彩3D",
'count': lottery_dict['3d']
}, {
'item': "大乐透",
'count': lottery_dict['dlt']
}, {
'item': "双色球",
'count': lottery_dict['ssq']
}]
data = {'chart_data': chart_data, 'total_money': total_money / 100}
return Response(data)