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)
Beispiel #4
0
    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)
Beispiel #5
0
 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']
Beispiel #6
0
 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)
Beispiel #9
0
    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)