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)