def test_register_statistics(self):
url = "https://jr.huanqiu.com/set-cookie/?key=version-grey&val=pre"
l = self.s.get(url)
register_statis = read_config().get_string('pre_online',
'register_statis')
file_name = '注册统计'
copy_file(file_name)
file_path = '..\\testStatistics\\' + file_name + '.xls'
write_xls_time('2018-08-01', file_path)
xls_list = get_xls(file_path)
for i in range(1, len(xls_list)):
start_time = xls_list[i]['开始时间']
end_time = xls_list[i]['结束时间']
param_time = time.strftime(
'%Y-%m-%d', time.strptime(start_time, '%Y-%m-%d %H:%M:%S'))
register_sql = "SELECT COUNT(*) FROM users WHERE create_at BETWEEN '" + str(
start_time) + "' AND '" + str(end_time) + "'"
write_xls(i + 1, 4, float(db_result(register_sql)[0][0]),
file_path)
r = self.s.get(str(register_statis) + '&start_time=' +
str(param_time) + '&end_time=' + str(param_time),
json={
"Content-Type":
"application/json;charset=UTF-8",
"session":
test_Login.test_login(self, 'pre_online')
})
json_dict = json.loads(r.text)
return_list = json_dict['data']['list']
for j in return_list:
write_xls(i + 1, 2, float(j['success_regist_people']),
file_path)
write_xls(i + 1, 3, float(j['fail_regist_people']), file_path)
def test_order_count(self):
url = "https://jr.huanqiu.com/set-cookie/?key=version-grey&val=pre"
l = self.s.get(url)
order_count = read_config().get_string('pre_online', 'order_count')
json_data = {
"Content-Type": "application/json;charset=UTF-8",
"session": test_Login.test_login(self, 'pre_online')
}
pageNO = 1
file_name = '订单合计'
copy_file(file_name)
file_path = '..\\testStatistics\\' + file_name + '.xls'
write_xls_time('2018-07-25', file_path)
xls_list = get_xls(file_path)
for i in range(1, len(xls_list)):
start_time = xls_list[i]['开始时间']
end_time = xls_list[i]['结束时间']
r = self.s.get(str(order_count) + str(pageNO) + '&channel=&date=' +
str(start_time) + '|' + str(end_time),
json=json_data)
json_dict = json.loads(r.text)
if json_dict['data']['total'] > 10:
r = self.s.get(
str(order_count) +
str(pageNO + int(json_dict['data']['total'] / 11)) +
'&channel=&date=' + str(start_time) + '|' + str(end_time),
json=json_data)
json_dict1 = json.loads(r.text)
account1 = json_dict1['data']['data'][
len(json_dict1['data']['data']) - 1]
if account1['amount'] == '--':
write_xls(i + 1, 2, float(0), file_path)
else:
write_xls(i + 1, 2, float(account1['amount']), file_path)
write_xls(i + 1, 3, float(account1['order_sum']), file_path)
write_xls(i + 1, 4, float(account1['people_sum']), file_path)
else:
account = json_dict['data']['data'][
len(json_dict['data']['data']) - 1]
if account['amount'] == '--':
write_xls(i + 1, 2, float(0), file_path)
else:
write_xls(i + 1, 2, float(account['amount']), file_path)
write_xls(i + 1, 3, float(account['order_sum']), file_path)
write_xls(i + 1, 4, float(account['people_sum']), file_path)
amount_sql = "select sum(amount) from orders where create_at BETWEEN '" + start_time + "' AND '" + end_time + "' and status in ('HOLDING','DONE')"
order_sql = "SELECT COUNT(order_no) FROM orders where create_at BETWEEN '" + start_time + "' AND '" + end_time + "' and status in ('HOLDING','DONE')"
member_sql = "select count(DISTINCT member_id) from orders where create_at BETWEEN '" + start_time + "' AND '" + end_time + "' and status in ('HOLDING','DONE')"
if db_result(amount_sql) == ((None, ), ):
write_xls(i + 1, 5, float(0), file_path)
else:
write_xls(i + 1, 5, db_result(amount_sql)[0][0], file_path)
write_xls(i + 1, 6, db_result(order_sql)[0][0], file_path)
write_xls(i + 1, 7, db_result(member_sql)[0][0], file_path)
def test_statistics(self):
url = "https://jr.huanqiu.com/set-cookie/?key=version-grey&val=pre"
l = self.s.get(url)
statistics = read_config().get_string('pre_online', 'statistics')
json_data = {
"Content-Type": "application/json;charset=UTF-8",
"session": test_Login.test_login(self, 'pre_online')
}
file_name = '站岗资金'
copy_file(file_name)
file_path = '..\\testStatistics\\' + file_name + '.xls'
write_xls_time('2018-07-15', file_path)
xls_list = get_xls(file_path)
for i in range(1, len(xls_list)):
start_time = xls_list[i]['开始时间']
param_time = time.strftime(
'%Y-%m-%d', time.strptime(start_time, '%Y-%m-%d %H:%M:%S'))
'''
@Description:转换时间节点，sql中需要下一天的时间，当前时间加1天
'''
end_time = datetime.datetime.strptime(
param_time, '%Y-%m-%d') + datetime.timedelta(days=1)
r = self.s.get(statistics + str(param_time) + '|' +
str(param_time),
json=json_data)
json_dict = json.loads(r.text)
balance = json_dict['data']['站岗资金情况']['balance']
for j in balance:
write_xls(i + 1, 2, float(j['amount']), file_path)
write_xls(i + 1, 3, float(j['users']), file_path)
amount_sql = "SELECT SUM(o.amount),COUNT(DISTINCT member_id) FROM (SELECT member_id, SUM(amount) AS amount FROM transaction_record WHERE deal_status = '2' AND create_time >= '" + str(
start_time) + " ' AND create_time < '" + str(
end_time.strftime('%Y-%m-%d')
) + "' GROUP BY member_id) o WHERE o.amount > 0"
amount = db_result(amount_sql)[0][0]
if amount == None:
write_xls(i + 1, 4, float(0), file_path)
else:
write_xls(i + 1, 4, float(amount), file_path)
users = db_result(amount_sql)[0][1]
write_xls(i + 1, 5, float(users), file_path)
def test_channel_order_count(self):
url = "https://jr.huanqiu.com/set-cookie/?key=version-grey&val=pre"
l = self.s.get(url)
url = read_config().get_string('pre_online', 'channel_statistics')
file_name = '渠道订单合计'
file_path = '..\\testStatistics\\' + file_name + '.xls'
copy_file(file_name)
write_xls_time('2018-10-30', file_path)
xls_list = get_xls(file_path)
for i in range(1, len(xls_list)):
start_time = xls_list[i]['开始时间']
end_time = xls_list[i]['结束时间']
r = self.s.get(url + str(start_time) + '|' + str(end_time),
json={
"Content-Type":
"application/json;charset=UTF-8",
"session":
test_Login.test_login(self, 'pre_online')
})
json_dict = json.loads(r.text)
account = json_dict['data']['订单渠道'][len(json_dict['data']['订单渠道'])
- 1]
if account['amount'] == '--':
write_xls(i + 1, 2, float(0), file_path)
else:
write_xls(i + 1, 2, float(account['amount']), file_path)
if account['order_no'] == '--':
write_xls(i + 1, 3, float(0), file_path)
else:
write_xls(i + 1, 3, float(account['order_no']), file_path)
amount_sql = "select sum(amount) from orders where create_at BETWEEN '" + start_time + "' AND '" + end_time + "' and status in ('HOLDING','DONE')"
order_sql = "select COUNT(DISTINCT order_no) from orders where create_at BETWEEN '" + start_time + "' AND '" + end_time + "' and status in ('HOLDING','DONE')"
if db_result(amount_sql) == ((None, ), ):
write_xls(i + 1, 4, float(0), file_path)
else:
write_xls(i + 1, 4, float(db_result(amount_sql)[0][0]),
file_path)
write_xls(i + 1, 5, float(db_result(order_sql)[0][0]), file_path)
def test_platform_statistics(self):
platform_statistics = read_config().get_string('statistics',
'platform_statistics')
json_data = {
"Content-Type": "application/json;charset=UTF-8",
"session": test_Login.test_login(self)
}
file_name = "H5统计数据"
file_path = '..\\testStatistics\\' + file_name + '.xls'
xls_list = get_xls(file_path)
for i in range(1, len(xls_list)):
start_time = xls_list[i]['开始时间']
end_time = xls_list[i]['结束时间']
date_time = time.strftime(
'%Y-%m-%d', time.strptime(start_time, '%Y-%m-%d %H:%M:%S'))
r = self.s.get(platform_statistics + str(date_time),
json=json_data)
json_dict = json.loads(r.text)
'''
@Description:当日投资用户数
'''
sum_name_now = json_dict['data']['sum_name_now']
write_xls(i + 1, 6, float(sum_name_now), file_path)
sum_amount = json_dict['data']['sum_amount']
def test_statistics_opr(self):
url = read_config().get_string('statistics', 'statistics_opr')
json_data = {
"Content-Type": "application/json;charset=UTF-8",
"session": test_Login.test_login(self)
}
# year = 2018
# month = 9
file_name = "H5"
# copy_file(file_name)
file_path = '..\\testStatistics\\' + file_name + '.xls'
ADD_UP_IN_COME = ''
# write_time(year,month,file_path)
xls_list = get_xls(file_path)
for i in range(1, len(xls_list)):
start_time = xls_list[i]['开始时间']
end_time = xls_list[i]['结束时间']
param_time = time.strftime(
'%Y%m%d', time.strptime(start_time, '%Y-%m-%d %H:%M:%S'))
r = self.s.get(url + str(param_time), json=json_data)
json_dict = json.loads(r.text)
ADD_UP_IN_COME = json_dict['OVERVIEW']['ADD_UP_IN_COME']
print(ADD_UP_IN_COME)
return ADD_UP_IN_COME
def test_reimbursement_statistics(self):
url = "https://jr.huanqiu.com/set-cookie/?key=version-grey&val=pre"
l = self.s.get(url)
json_data = {"Content-Type": "application/json;charset=UTF-8", "session": test_Login.test_login(self,'pre_online')}
return_money_statistics = read_config().get_string('pre_online','return_money_statistics')
file_name = '回款统计1'
file_path = '..\\testStatistics\\' + file_name + '.xls'
copy_file(file_name)
write_xls_time('2018-07-25',file_path)
xls_list = get_xls(file_path)
for i in range(1, len(xls_list)):
start_time = xls_list[i]['开始时间']
end_time = xls_list[i]['结束时间']
user_sql = "SELECT COUNT(DISTINCT member_id) FROM orders WHERE end_time BETWEEN '" + start_time + "' AND '" + end_time + "'"
total_sql = "SELECT sum(amount+outputmonery) FROM orders WHERE end_time BETWEEN '" + start_time + "' AND '" + end_time + "' AND `status` = 'DONE'"
forty_sql = "SELECT sum(amount+outputmonery) FROM orders WHERE end_time BETWEEN '" + start_time + "' AND '" + end_time + "' AND time_long=45 AND `status` = 'DONE'"
forty_user_sql = "SELECT COUNT(DISTINCT member_id) FROM orders WHERE end_time BETWEEN '" + start_time + "' AND '" + end_time + "' AND time_long=45 AND `status` = 'DONE'"
ninety_sql = "SELECT sum(amount+outputmonery) FROM orders WHERE end_time BETWEEN '" + start_time + "' AND '" + end_time + "' AND time_long=90 AND `status` = 'DONE'"
ninety_user_sql = "SELECT COUNT(DISTINCT member_id) FROM orders WHERE end_time BETWEEN '" + start_time + "' AND '" + end_time + "' AND time_long=90 AND `status` = 'DONE'"
one_sql = "SELECT sum(amount+outputmonery) FROM orders WHERE end_time BETWEEN '" + start_time + "' AND '" + end_time + "' AND time_long=180 AND `status` = 'DONE'"
one_user_sql = "SELECT COUNT(DISTINCT member_id) FROM orders WHERE end_time BETWEEN '" + start_time + "' AND '" + end_time + "' AND time_long=180 AND `status` = 'DONE'"
three_sql = "SELECT sum(amount+outputmonery) FROM orders WHERE end_time BETWEEN '" + start_time + "' AND '" + end_time + "' AND time_long=365 AND `status` = 'DONE'"
three_user_sql = "SELECT COUNT(DISTINCT member_id) FROM orders WHERE end_time BETWEEN '" + start_time + "' AND '" + end_time + "' AND time_long=365 AND `status` = 'DONE'"
user = db_result(user_sql)
total = db_result(total_sql)
forty = db_result(forty_sql)
forty_user = db_result(forty_user_sql)
ninety = db_result(ninety_sql)
ninety_user = db_result(ninety_user_sql)
one = db_result(one_sql)
one_user = db_result(one_user_sql)
three = db_result(three_sql)
three_user = db_result(three_user_sql)
r = self.s.get(
return_money_statistics + str(start_time) + '|' + str(end_time),
json = json_data
)
json_dict = json.loads(r.text)
return_money_list = json_dict['data']['回款情况'].get('return_money')
for j in range(len(return_money_list)):
write_xls(i + 1, 2, float(return_money_list[j]['user']), file_path)
write_xls(i + 1, 4, float(return_money_list[j]['total']), file_path)
write_xls(i + 1, 6, float(return_money_list[j]['forty_five']), file_path)
write_xls(i + 1, 7, float(return_money_list[j]['forty_five_user']), file_path)
write_xls(i + 1, 10, float(return_money_list[j]['ninety']), file_path)
write_xls(i + 1, 11, float(return_money_list[j]['ninety_user']), file_path)
write_xls(i + 1, 14, float(return_money_list[j]['one_hundred_and_eighty']), file_path)
write_xls(i + 1, 15, float(return_money_list[j]['one_hundred_and_eighty_user']), file_path)
write_xls(i + 1, 18, float(return_money_list[j]['three_hundred_and_sixty']), file_path)
write_xls(i + 1, 19, float(return_money_list[j]['three_hundred_and_sixty_user']), file_path)
write_xls(i + 1, 3, float(user[0][0]), file_path)
if total == ((None,),):
write_xls(i + 1, 5, float(0), file_path)
else:
write_xls(i + 1, 5, float(total[0][0]), file_path)
write_xls(i + 1, 9, float(forty_user[0][0]), file_path)
if forty == ((None,),):
write_xls(i + 1, 8, float(0), file_path)
else:
write_xls(i + 1, 8, float(forty[0][0]), file_path)
if ninety == ((None,),):
write_xls(i + 1, 12, float(0), file_path)
else:
write_xls(i + 1, 12, float(ninety[0][0]), file_path)
write_xls(i + 1, 13, float(ninety_user[0][0]), file_path)
if one == ((None,),):
write_xls(i + 1, 16, float(0), file_path)
else:
write_xls(i + 1, 16, float(one[0][0]), file_path)
write_xls(i + 1, 17, float(one_user[0][0]), file_path)
if three == ((None,),):
write_xls(i + 1, 20, float(0), file_path)
else:
write_xls(i + 1, 20, float(three[0][0]), file_path)
write_xls(i + 1, 21, float(three_user[0][0]), file_path)
def test_investment_num(self):
url = "https://jr.huanqiu.com/set-cookie/?key=version-grey&val=pre"
l = self.s.get(url)
platform_statistics = read_config().get_string('pre_h5','platform_statistics')
order_count = read_config().get_string('pre_online','order_count')
users_statistics = read_config().get_string('pre_online','users_statistics')
query_orders = read_config().get_string('pre_online','query_orders')
file_name = "当日投资用户数"
file_path = '..\\testStatistics\\' + file_name + '.xls'
copy_file(file_name)
write_xls_time('2018-07-15', file_path)
xls_list = get_xls(file_path)
for i in range(1,len(xls_list)):
start_time = xls_list[i]['开始时间']
end_time = xls_list[i]['结束时间']
param_time = time.strftime('%Y-%m-%d', time.strptime(start_time, '%Y-%m-%d %H:%M:%S'))
r = self.s.get(
platform_statistics + str(param_time),
json={"Content-Type": "application/json;charset=UTF-8", "huanqiu_backend_session": test_Login.test_login(self,'pre_h5')}
)
json_dict = json.loads(r.text)
'''
@Description:H5 当日投资用户数
'''
h5_sum_name_now = json_dict['data']['sum_name_now']
write_xls(i + 1, 2, float(h5_sum_name_now), file_path)
'''
@Description:订单统计--购买总人数
'''
pageNO = 1
order = self.s.get(
str(order_count) + str(pageNO) + '&channel=&date=' + str(start_time) + '|' + str(end_time),
json={"Content-Type": "application/json;charset=UTF-8", "session": test_Login.test_login(self,'pre_online')}
)
json_dict_order = json.loads(order.text)
if json_dict_order['data']['total'] > 10:
ord = self.s.get(
str(order_count) + str(pageNO + int(json_dict_order['data']['total']/11)) + '&channel=&date=' + str(start_time) + '|' + str(end_time),
json={"Content-Type": "application/json;charset=UTF-8", "session": test_Login.test_login(self,'pre_online')}
)
json_dict_ord = json.loads(ord.text)
acc = json_dict_ord['data']['data'][len(json_dict_ord['data']['data']) - 1]
write_xls(i + 1, 3, float(acc['people_sum']), file_path)
else:
account = json_dict_order['data']['data'][len(json_dict_order['data']['data']) - 1]
write_xls(i + 1, 3, float(account['people_sum']), file_path)
'''
@Description:用户统计--当日购买总人数
'''
user = self.s.get(
str(users_statistics) + str(start_time) + '|' + str(end_time),
json={"Content-Type": "application/json;charset=UTF-8", "session": test_Login.test_login(self,'pre_online')}
)
json_dict_user = json.loads(user.text)
for j in json_dict_user['data']['用户情况']:
write_xls(i + 1, 4, float(j['users']), file_path)
query = self.s.get(
str(query_orders) + str(start_time) + '|' + str(end_time),
json={"Content-Type": "application/json;charset=UTF-8","session": test_Login.test_login(self, 'pre_online')}
)
json_dict_query = json.loads(query.text)
write_xls(i + 1, 5, float(json_dict_query['data']['total']), file_path)
def test_stock_statistics(self):
url = "https://jr.huanqiu.com/set-cookie/?key=version-grey&val=pre"
l = self.s.get(url)
stock_statistics = read_config().get_string('pre_online','stock_statistics')
file_name = '存量合计'
file_path = '..\\testStatistics\\' + file_name + '.xls'
copy_file(file_name)
write_xls_time('2018-07-15',file_path)
xls_list = get_xls(file_path)
json_data = {"Content-Type": "application/json;charset=UTF-8", "session": test_Login.test_login(self,'pre_online')}
for i in range(1, len(xls_list)) :
start_time = xls_list[i]['开始时间']
end_time = xls_list[i]['结束时间']
done_sum_sql1 = "SELECT SUM(amount) FROM orders WHERE create_at >= '" + str(start_time) + "' AND create_at <= '" + str(end_time) + "' AND `status` IN('HOLDING','DONE')"
done_sum_sql2 = "SELECT SUM(amount) FROM orders WHERE end_time >= '" + str(start_time) + "' AND end_time <= '" + str(end_time) + "' AND `status` IN('HOLDING','DONE')"
stock_four_sql = "SELECT sum(amount) from orders where create_at BETWEEN '" + start_time + "' AND '" + end_time + "' and status in ('HOLDING') and time_long = 45"
stock_ninth_sql = "SELECT sum(amount) from orders where create_at BETWEEN '" + start_time + "' AND '" + end_time + "' and status in ('HOLDING') and time_long = 90"
stock_one_sql = "SELECT sum(amount) from orders where create_at BETWEEN '" + start_time + "' AND '" + end_time + "' and status in ('HOLDING') and time_long = 180"
stock_three_sql = "SELECT sum(amount) from orders where create_at BETWEEN '" + start_time + "' AND '" + end_time + "' and status in ('HOLDING') and time_long = 365"
done_sum1 = db_result(done_sum_sql1)
done_sum2 = db_result(done_sum_sql2)
stock_four = db_result(stock_four_sql)
stock_ninth = db_result(stock_ninth_sql)
stock_one = db_result(stock_one_sql)
stock_three = db_result(stock_three_sql)
r = self.s.get(
stock_statistics + str(start_time) + '|' + str(end_time),
json = json_data
)
json_dict = json.loads(r.text)
stock_list = json_dict['data']['存量情况'].get('stock_list')
for j in range(len(stock_list)):
write_xls(i + 1, 2, float(stock_list[j]['done_sum']), file_path)
write_xls(i + 1, 3, float(stock_list[j]['total']), file_path)
write_xls(i + 1, 4, float(stock_list[j]['stock_four']), file_path)
write_xls(i + 1, 5, float(stock_list[j]['stock_ninth']), file_path)
write_xls(i + 1, 6, float(stock_list[j]['stock_one_hundred']), file_path)
write_xls(i + 1, 7, float(stock_list[j]['stock_three_hundred']), file_path)
if done_sum1 == ((None,),):
if done_sum2 == ((None,),):
write_xls(i + 1, 8 , float(0), file_path)
else:
write_xls(i + 1, 8, float(done_sum2[0][0]), file_path)
else:
if done_sum2 == ((None,),):
write_xls(i + 1, 8, float(done_sum1[0][0]), file_path)
else:
write_xls(i + 1, 8, float(done_sum1[0][0]) - float(done_sum2[0][0]), file_path)
'''
@Description:当前存量金额
'''
stock_ord_smt_sql = "select case when SUM(amount) is null then 0 else SUM(amount) end as amount from orders where status ='HOLDING' and create_at <= '" + str(end_time) + "'"
reimbursement_sql = "select case when sum(amount) is null then 0 else sum(amount) end as amount from transaction_record where deal_status=2 and create_time <= '" + str(end_time) + "' and trade_type=5"
write_xls(i + 1, 9, float(db_result(stock_ord_smt_sql)[0][0]) - float(db_result(reimbursement_sql)[0][0]), file_path)
if stock_four == ((None,),) :
write_xls(i + 1, 10, float(0), file_path)
else:
write_xls(i + 1, 10, float(stock_four[0][0]), file_path)
if stock_ninth == ((None,),) :
write_xls(i + 1, 11, float(0), file_path)
else:
write_xls(i + 1, 11, float(stock_ninth[0][0]), file_path)
if stock_one == ((None,),):
write_xls(i + 1, 12, float(0), file_path)
else:
write_xls(i + 1, 12, float(stock_one[0][0]), file_path)
if stock_three == ((None,),):
write_xls(i + 1, 13, float(0), file_path)
else:
write_xls(i + 1, 13, float(stock_three[0][0]), file_path)
def test_net_loan_reg_user(self):
url = "https://jr.huanqiu.com/set-cookie/?key=version-grey&val=pre"
l = self.s.get(url)
register_statis = read_config().get_string('pre_online',
'register_statis')
users_statistics = read_config().get_string('pre_online',
'users_statistics')
platform_statistics = read_config().get_string('pre_h5',
'platform_statistics')
file_name = '当日网贷注册用户数'
file_path = '..\\testStatistics\\' + file_name + '.xls'
copy_file(file_name)
write_xls_time('2018-08-15', file_path)
xls_list = get_xls(file_path)
for i in range(1, len(xls_list)):
start_time = xls_list[i]['开始时间']
end_time = xls_list[i]['结束时间']
param_time = time.strftime(
'%Y-%m-%d', time.strptime(start_time, '%Y-%m-%d %H:%M:%S'))
p = self.s.get(platform_statistics + str(param_time),
json={
"Content-Type":
"application/json;charset=UTF-8",
"huanqiu_backend_session":
test_Login.test_login(self, 'pre_h5')
})
json_dict_p = json.loads(p.text)
write_xls(i + 1, 2, float(json_dict_p['data']['sum_name_now']),
file_path)
r = self.s.get(register_statis + '&start_time=' + str(param_time) +
'&end_time=' + str(param_time),
json={
"Content-Type":
"application/json;charset=UTF-8",
"session":
test_Login.test_login(self, 'pre_online')
})
json_dict = json.loads(r.text)
for j in json_dict['data']['list']:
'''
@Description:pc返回注册成功人数
'''
write_xls(i + 1, 3, float(j['success_regist_people']),
file_path)
m = self.s.get(
users_statistics + str(param_time) + '|' + str(param_time),
json={
"Content-Type": "application/json;charset=UTF-8",
"session": test_Login.test_login(self, 'pre_online')
})
json_dict_m = json.loads(m.text)
for k in json_dict_m['data']['用户情况']:
'''
@Description:pc返回当日注册人数
'''
write_xls(i + 1, 4, float(k['mobile']), file_path)
'''
@Description:数据库当日网贷注册用户数
'''
curr_day_user_sql = "SELECT COUNT(DISTINCT mobile) from users where create_at BETWEEN '" + str(
start_time) + "' AND '" + str(end_time) + "'"
curr_day_user = db_result(curr_day_user_sql)
write_xls(i + 1, 5, float(curr_day_user[0][0]), file_path)
def test_add_up_reg_user(self):
url = "https://jr.huanqiu.com/set-cookie/?key=version-grey&val=pre"
l = self.s.get(url)
platform_statistics = read_config().get_string('pre_h5',
'platform_statistics')
register_statis = read_config().get_string('pre_online',
'register_statis')
users_statistics = read_config().get_string('pre_online',
'users_statistics')
file_name = '累计注册用户数'
file_path = '..\\testStatistics\\' + file_name + '.xls'
copy_file(file_name)
write_xls_time('2018-08-15', file_path)
xls_list = get_xls(file_path)
for i in range(1, len(xls_list)):
start_time = xls_list[i]['开始时间']
end_time = xls_list[i]['结束时间']
param_time = time.strftime(
'%Y-%m-%d', time.strptime(start_time, '%Y-%m-%d %H:%M:%S'))
r = self.s.get(platform_statistics + str(param_time),
json={
"Content-Type":
"application/json;charset=UTF-8",
"huanqiu_backend_session":
test_Login.test_login(self, 'pre_h5')
})
json_dict_r = json.loads(r.text)
'''
@Description:H5累计注册用户数
'''
sum_zhuce_num = json_dict_r['data']['sum_zhuce_num']
write_xls(i + 1, 2, float(sum_zhuce_num), file_path)
'''
@Description:后台注册统计-注册成功人数
'''
register = self.s.get(
str(register_statis) + '&start_time=' + str(param_time) +
'&end_time=' + str(param_time),
json={
"Content-Type":
"application/json;charset=UTF-8",
"huanqiu_backend_session":
test_Login.test_login(self, 'pre_online')
})
json_dict_register = json.loads(register.text)
for j in json_dict_register['data']['list']:
write_xls(i + 1, 3, float(j['success_regist_people']),
file_path)
'''
@Description:后台 用户统计-注册人数
'''
user = self.s.get(str(users_statistics) + str(start_time) + '|' +
str(end_time),
json={
"Content-Type":
"application/json;charset=UTF-8",
"session":
test_Login.test_login(self, 'pre_online')
})
json_dict_user = json.loads(user.text)
for k in json_dict_user['data']['用户情况']:
write_xls(i + 1, 4, float(k['mobile']), file_path)
def test_dev_platform_statistics(self):
url = "https://jr.huanqiu.com/set-cookie/?key=version-grey&val=pre"
l = self.s.get(url)
platform_statistics = read_config().get_string('pre_h5',
'platform_statistics')
statistics_opr = read_config().get_string('pre_h5', 'statistics_opr')
file_name = "H5"
file_path = '..\\testStatistics\\' + file_name + '.xls'
copy_file(file_name)
write_xls_time('2018-07-25', file_path)
xls_list = get_xls(file_path)
for i in range(1, len(xls_list)):
start_time = xls_list[i]['开始时间']
end_time = xls_list[i]['结束时间']
'''
@Description:转换时间格式
'''
param_time = time.strftime(
'%Y-%m-%d', time.strptime(start_time, '%Y-%m-%d %H:%M:%S'))
date_time = time.strftime(
'%Y%m%d', time.strptime(start_time, '%Y-%m-%d %H:%M:%S'))
r = self.s.get(platform_statistics + str(param_time),
json={
"Content-Type":
"application/json;charset=UTF-8",
"huanqiu_backend_session":
test_Login.test_login(self, 'pre_h5')
})
json_dict = json.loads(r.text)
t = self.s.get(statistics_opr + str(date_time))
json_dict_t = json.loads(t.text)
ADD_UP_IN_COME = json_dict_t['OVERVIEW']['ADD_UP_IN_COME']
ADD_UP_PAY_USER = json_dict_t['OVERVIEW']['ADD_UP_PAY_USER']
'''
@Description:累计投资金额
'''
write_xls(i + 1, 2, float(json_dict['data']['sum_amount']),
file_path)
'''
@Description:累计投资金额
'''
add_up_ord_amt_sql = "select sum(amount) from orders where status in ('HOLDING','DONE')"
write_xls(i + 1, 3, float(db_result(add_up_ord_amt_sql)[0][0]),
file_path)
'''
@Description:玖富累计投资金额
'''
write_xls(i + 1, 4, float(json_dict['data']['sum_amount_9f']),
file_path)
'''
@Description:天天热卖累计交易额
'''
write_xls(i + 1, 5,
float(json_dict['data']['sum_amount_tiantian']),
file_path)
'''
@Description:当日交易额
'''
write_xls(i + 1, 6, float(json_dict['data']['sum_amount_now']),
file_path)
'''
@Description:当日交易额
'''
curr_day_ord_amt_sql = "SELECT sum(amount) from orders WHERE `status` in ('DONE','HOLDING') and create_at BETWEEN '" + str(
start_time) + "' and '" + str(end_time) + "'"
curr_day_ord_amt = db_result(curr_day_ord_amt_sql)
if curr_day_ord_amt == ((None, ), ):
write_xls(i + 1, 7, float(0), file_path)
else:
write_xls(i + 1, 7,
float(db_result(curr_day_ord_amt_sql)[0][0]),
file_path)
'''
@Description:玖富当日交易额
'''
write_xls(i + 1, 8, float(json_dict['data']['sum_amount_now_9f']),
file_path)
'''
@Description:天天热卖当日交易额
'''
write_xls(i + 1, 9,
float(json_dict['data']['sum_amount_now_tiantian']),
file_path)
'''
@Description:累计投资用户数
'''
write_xls(i + 1, 10, float(json_dict['data']['sum_name']),
file_path)
'''
@Description:累计投资用户数
'''
add_up_pay_user_sql = "select count(distinct mobile) from orders where status in ('HOLDING','DONE')"
write_xls(i + 1, 11, float(db_result(add_up_pay_user_sql)[0][0]),
file_path)
'''
@Description:天天热卖累计投资用户数
'''
write_xls(i + 1, 12, float(ADD_UP_PAY_USER), file_path)
'''
@Description:当日投资用户数
'''
write_xls(i + 1, 13, float(json_dict['data']['sum_name_now']),
file_path)
'''
@Description:当日投资用户数
'''
curr_day_pay_user_sql = "SELECT COUNT(DISTINCT member_id) from orders WHERE `status` in ('DONE','HOLDING') and create_at BETWEEN '" + str(
start_time) + "' and '" + str(end_time) + "'"
write_xls(i + 1, 14, float(db_result(curr_day_pay_user_sql)[0][0]),
file_path)
'''
@Description:天天热卖累计交易额
'''
# write_xls(i +1, 5,float(json_dict['data']['sum_amount_tiantian']), file_path)
'''
@Description:当前存量金额
'''
write_xls(i + 1, 15, float(json_dict['data']['sum_stock_amount']),
file_path)
'''
@Description:玖富当前存量金额
'''
write_xls(i + 1, 16,
float(json_dict['data']['sum_stock_amount_9f']),
file_path)
'''
@Description:天天热卖当前存量金额
'''
write_xls(i + 1, 17,
float(json_dict['data']['sum_stock_num_now_tiantian']),
file_path)
'''
@Description:当前存量金额
'''
stock_ord_smt_sql = "select case when SUM(amount) is null then 0 else SUM(amount) end as amount from orders where status ='HOLDING' and create_at <= '" + str(
end_time) + "'"
reimbursement_sql = "select case when sum(amount) is null then 0 else sum(amount) end as amount from transaction_record where deal_status=2 and create_time <= '" + str(
end_time) + "' and trade_type=5"
write_xls(
i + 1, 18,
float(db_result(stock_ord_smt_sql)[0][0]) -
float(db_result(reimbursement_sql)[0][0]), file_path)
'''
@Description:累计注册用户数
'''
write_xls(i + 1, 19, float(json_dict['data']['sum_zhuce_num']),
file_path)
'''
@Description:累计注册用户数
'''
add_up_reg_user_sql = "SELECT COUNT(DISTINCT mobile) FROM users"
write_xls(i + 1, 20, float(db_result(add_up_reg_user_sql)[0][0]),
file_path)
write_xls(i + 1, 21, float(json_dict['data']['sum_zhuce_num_now']),
file_path)
write_xls(i + 1, 22,
float(json_dict['data']['sum_zhuce_num_now_9f']),
file_path)
'''
@Description:当前存量用户数
'''
write_xls(i + 1, 23, float(json_dict['data']['user_holding']),
file_path)
'''
@Description:当前存量用户数
'''
curr_day_stock_ord_user_sql = "SELECT COUNT(DISTINCT member_id) from orders WHERE `status` in ('HOLDING')"
write_xls(i + 1, 24,
float(db_result(curr_day_stock_ord_user_sql)[0][0]),
file_path)
'''
@Description:为客户赚取收益
'''
write_xls(i + 1, 25, float(json_dict['data']['user_rate_amount']),
file_path)
'''
@Description:为用户赚取收益
'''
add_up_in_come_sql = "select SUM(outputmonery) from orders where status in ('HOLDING','DONE') and create_at <= '" + str(
end_time) + "'"
write_xls(
i + 1, 26,
float(db_result(add_up_in_come_sql)[0][0]) +
float(ADD_UP_IN_COME), file_path)
'''
@Description:当日网贷注册用户数
'''
curr_day_user_sql = "SELECT COUNT(DISTINCT mobile) from users where create_at BETWEEN '" + str(
start_time) + "' AND '" + str(end_time) + "'"
# write_xls(i + 1, 18, float(db_result(curr_day_user_sql)[0][0]), file_path)
'''
@Description:当日网贷投资金额
'''
curr_day_total_sql = "SELECT sum(amount) from orders WHERE `status` in ('DONE','HOLDING') and create_at BETWEEN '" + str(
start_time) + "' and '" + str(end_time) + "'"
curr_day_total = db_result(curr_day_total_sql)
# if curr_day_total == ((None,),):
# write_xls(i + 1, 19, float(0), file_path)
# else:
# write_xls(i + 1, 19, float(curr_day_total[0][0]), file_path)
'''
@Description:累计交易额
'''
add_up_ord_sql = "select sum(amount) from orders where status in ('HOLDING','DONE')"
def test_trade_vol_statistics(self):
url = "https://jr.huanqiu.com/set-cookie/?key=version-grey&val=pre"
l = self.s.get(url)
file_name = '当日交易额'
copy_file(file_name)
file_path = '..\\testStatistics\\' + file_name + '.xls'
write_xls_time('2018-07-15', file_path)
order_count = read_config().get_string('pre_online', 'order_count')
channel_order = read_config().get_string('pre_online',
'channel_statistics')
users_statistics = read_config().get_string('pre_online',
'users_statistics')
platform_statistics = read_config().get_string('pre_h5',
'platform_statistics')
pageNO = 1
xls_list = get_xls(file_path)
for i in range(1, len(xls_list)):
start_time = xls_list[i]['开始时间']
end_time = xls_list[i]['结束时间']
param_time = time.strftime(
'%Y-%m-%d', time.strptime(start_time, '%Y-%m-%d %H:%M:%S'))
r = self.s.get(platform_statistics + str(param_time),
json={
"Content-Type":
"application/json;charset=UTF-8",
"huanqiu_backend_session":
test_Login.test_login(self, 'pre_h5')
})
json_dict = json.loads(r.text)
'''
@Description:H5当日交易额
'''
sum_amount_now = json_dict['data']['sum_amount_now']
write_xls(i + 1, 2, float(sum_amount_now), file_path)
sum_amount_now_9f = json_dict['data']['sum_amount_now_9f']
write_xls(i + 1, 3, float(sum_amount_now_9f), file_path)
'''
@Description:PC 订单统计-当日投资金额
'''
order = self.s.get(str(order_count) + str(pageNO) +
'&channel=&date=' + str(start_time) + '|' +
str(end_time),
json={
"Content-Type":
"application/json;charset=UTF-8",
"huanqiu_backend_session":
test_Login.test_login(self, 'pre_online')
})
json_dict_order = json.loads(order.text)
if json_dict_order['data']['total'] > 10:
ord = self.s.get(
str(order_count) +
str(pageNO + int(json_dict_order['data']['total'] / 11)) +
'&channel=&date=' + str(start_time) + '|' + str(end_time),
json={
"Content-Type":
"application/json;charset=UTF-8",
"huanqiu_backend_session":
test_Login.test_login(self, 'pre_online')
})
json_dict_ord = json.loads(ord.text)
account = json_dict_ord['data']['data'][
len(json_dict_ord['data']['data']) - 1]
write_xls(i + 1, 4, float(account['amount']), file_path)
else:
amount = json_dict_order['data']['data'][
len(json_dict_order['data']['data']) - 1]
if amount['amount'] == '--':
write_xls(i + 1, 4, float(0), file_path)
else:
write_xls(i + 1, 4, float(amount['amount']), file_path)
'''
@Description:PC 渠道订单统计-订单金额
'''
channel = self.s.get(str(channel_order) + str(param_time) + '|' +
str(end_time),
json={
"Content-Type":
"application/json;charset=UTF-8",
"session":
test_Login.test_login(self, 'pre_online')
})
json_dict_channel = json.loads(channel.text)
if json_dict_channel['data']['total'] == 1:
write_xls(i + 1, 5, float(0), file_path)
elif json_dict_channel['data']['total'] > 10:
chanel = self.s.get(
str(channel_order) + str(param_time) + '|' + str(end_time),
json={
"Content-Type": "application/json;charset=UTF-8",
"session": test_Login.test_login(self, 'pre_online')
})
json_dict_chanel = json.loads(chanel.text)
chanel_amount = json_dict_chanel['data']['订单渠道'][
len(json_dict_chanel['data']['订单渠道']) - 1]
write_xls(i + 1, 5, float(chanel_amount['amount']), file_path)
else:
channel_sta = self.s.get(
str(channel_order) + str(param_time) + '|' + str(end_time),
json={
"Content-Type": "application/json;charset=UTF-8",
"session": test_Login.test_login(self, 'pre_online')
})
json_dict_channel_sta = json.loads(channel_sta.text)
channel_sta_amount = json_dict_channel_sta['data']['订单渠道'][
len(json_dict_channel_sta['data']['订单渠道']) - 1]
write_xls(i + 1, 5, float(channel_sta_amount['amount']),
file_path)
'''
@Description:PC 资金统计-当日购买总额
'''
user = self.s.get(str(users_statistics) + str(param_time) + '|' +
str(param_time),
json={
"Content-Type":
"application/json;charset=UTF-8",
"session":
test_Login.test_login(self, 'pre_online')
})
json_dict_user = json.loads(user.text)
account_user = json_dict_user['data']['资金情况'][
len(json_dict_user['data']['资金情况']) - 1]
write_xls(i + 1, 6, float(account_user['total_amount']), file_path)