def user_special_ratio(ip, user_id):
now_time = int(round(time.time() * 1000))
#查询用户的临时采购费率+临时销售费率
select1 = f"SELECT cbp_ratio,commission_ratio FROM `ecloud_user`.`user_special_ratio` WHERE `user_id` = '{user_id}' AND `deleted` = '-1' AND `end_time` >= {now_time};"
select2 = f"SELECT * FROM `ecloud_user`.`user_sale_special_ratio` WHERE `user_id` = '{user_id}' AND `deleted` = '-1' AND `end_time` >= {now_time};"
data1 = SQL(ip).do_mysql_dict(select1)
data2 = SQL(ip).do_mysql_dict(select2)
buy_cbp_ratio = None
buy_cash_ratio = None
sale_cash_ratio = None
if data1 != ():
buy_cbp_ratio = data1[0]['cbp_ratio']
buy_cash_ratio = data1[0]['commission_ratio']
if data2 != ():
sale_cash_ratio = data2[0]['commission_ratio']
if buy_cbp_ratio == None and buy_cash_ratio == None and sale_cash_ratio == None:
data3 = None
else:
data3 = (buy_cbp_ratio, buy_cash_ratio, sale_cash_ratio)
return data3
def platform_promotion_ratio(ip):
now_time = int(round(time.time() * 1000))
# 查询是否有活动费率
select = f"SELECT * FROM `ecloud_user`.`platform_promotion_ratio` WHERE `deleted` = '-1' AND `end_time` >= {now_time};"
data = SQL(ip).do_mysql_dict(select)
if data != ():
buy_cbp_ratio = data[0]['cbp_ratio']
buy_cash_ratio = data[0]['commission_ratio']
sale_cash_ratio = data[0]['sale_commission_ratio']
return buy_cbp_ratio, buy_cash_ratio, sale_cash_ratio
def wallet_detail(ip, order):
'''
:param ip: 数据库IP
:param order: 订单号
:return: 流水详情
'''
# SQL语句
select = f'SELECT b.user_id,b.type,b.biz_type,b.changes,b.result,b.current,b.note,b.category from `ecloud_orders`.`orders` a, `ecloud_orders`.`wallet_detail` b where a.order_num="{order}" and a.id=b.source_id;'
# 使用fetchall()方法获取查询结果 (接收全部的返回结果)
data = SQL(ip).do_mysql_tuple(select)
return data
def bind_user_relationship(ip, user_id):
'''
:param order: 用户id
:return: 用户绑定的销售/业务焕商/TCO
'''
# SQL语句
select = "SELECT business_user_id,bind_type FROM `ecloud_user2`.`bind_user_relationship` WHERE `user_id` = '{0}' AND `is_valid` = '1';".format(
user_id)
data = SQL(ip).do_mysql_dict(select)
if data != ():
for i in range(len(data)):
business_user_id = data[i]['business_user_id']
data2 = business_relationship(ip, business_user_id)
if data2 == ():
data.pop(i)
else:
data=None
return data
def regional_agent(ip, phone):
'''
:param phone: 手机号
:return:上级城市焕商/代理商
'''
# SQL语句
# 查上级代理商
select1 = "select a.type,u.id FROM (select c.id,c.type,c.province,c.city,c.area from ecloud_user.company c LEFT JOIN ecloud_user.`user` u ON (c.area=u.area and c.type = 3 )or (c.city=u.city and c.type=2) OR (c.province=u.province and c.type =1) where u.phone='{0}') a LEFT JOIN ecloud_user.`user` u ON a.id = u.company_id;".format(
phone)
# 查询上级城市焕商
select2 = "select p.signed_user_id,p.area_name from ecloud_user.partner_agent_area p LEFT JOIN ecloud_user.`user` u ON (p.province_name=u.province and p.city_name=u.city and p.area_name=u.area) or (p.province_name=u.province and p.city_name=u.city and p.area_name='') OR ( p.province_name=u.province and p.city_name='' and p.area_name='') where u.phone ='{0}';".format(
phone)
data = SQL(ip).do_mysql_dict(select1)
# 如果查询上级代理商，数据为空 则根据sql2查询上级城市焕商
if data == ():
data = SQL(ip).do_mysql_dict(select2)
return data
def current(ip, order):
'''
:param order: 订单号
:return: 钱包变化值 tuple
'''
select = 'SELECT b.current from `ecloud_orders`.`orders` a, `ecloud_orders`.`wallet_detail` b where a.order_num="%s" and a.id=b.source_id;' % (
order)
data = SQL(ip).do_mysql_tuple(select)
return data
def chengshihuanshang(ip, user_id):
# 查询城市焕商具体是市代还是区代
select = "SELECT area_code FROM `ecloud_user`.`partner_agent_area` WHERE `signed_user_id` = {0}".format(
user_id)
data = SQL(ip).do_mysql_dict(select)
if data[0]['area_code'] == '':
data = "市分佣比例"
else:
data = "区分佣比例"
return data
def reserve_fund_sql(ip, user_id):
'''
:param user_id: 买家id
:return: 未消耗充值金额,储备池金额 dict
'''
# 查储备池
select = "SELECT sum(r.charge_amount) charge_amount,sum(r.reserve_fund) reserve_fund FROM ecloud_orders.reserve_fund_orders r WHERE r.user_id= {0} and r.is_use=0;".format(
user_id)
data = SQL(ip).do_mysql_dict(select)
return data[0]
def platform_second_payagent_ratio(ip, platform_id):
'''
:param order: 平台
:return: 二级分佣比例
'''
select = "SELECT agent_id,sales_ratio,tco_ratio,free_sales_ratio FROM `ecloud_orders`.`second_payagent_ratio` WHERE `agent_id` = {0};".format(
platform_id)
data = SQL(ip).do_mysql_dict(select)
data = xunzhao(data)
return data
def user_phone(ip, user_id):
'''
:param order: 平台
:return: 二级分佣比例
'''
# 使用execute()执行SQL语句
select = "SELECT phone FROM `ecloud_user`.`user` WHERE `id` = {0};".format(
user_id)
# 使用fetchall()方法获取查询结果 (接收全部的返回结果)
data = SQL(ip).do_mysql_dict(select)
return data[0]['phone']
def personal(ip, phone):
'''
:param phone: 手机号
:return: 绑定的个人焕商 dict
'''
# 查是否绑定个人焕商
select = "SELECT u.agent_id FROM ecloud_user.`user` u WHERE u.phone='{0}';".format(phone)
data = SQL(ip).do_mysql_dict(select)
# 如果agent_id的值为None
if (data[0])['agent_id'] == None or (data[0])['agent_id'] == 0:
data = {"个人焕商": None}
else:
data = {"个人焕商": (data[0])['agent_id']}
return data
def ratio(ip, province_id, city_id, area_id, personal_id):
zong_id = [province_id, city_id, area_id, personal_id]
user_id = tuple(quchong(zong_id, None))
if user_id == ():
data = None
else:
# 查上级分佣比例
if len(user_id) == 1:
user_id = user_id[0]
select = "SELECT user_id,ratio,type FROM ecloud_orders.user_agent_ratio WHERE user_id = {0};".format(
user_id)
else:
select = "SELECT user_id,ratio,type FROM ecloud_orders.user_agent_ratio WHERE user_id in {0};".format(
user_id)
data = SQL(ip).do_mysql_dict(select)
return data
def user_grade_ratio(ip, user_id):
global grade
#查询用户的等级，获取等级支付服务费费率
select = f"SELECT grade_id FROM `ecloud_user`.`user` WHERE `id` = '{user_id}';"
data = SQL(ip).do_mysql_dict(select)
if data[0]['grade_id'] == 1:
grade = "普通会员"
elif data[0]['grade_id'] == 2:
grade = "白银会员"
elif data[0]['grade_id'] == 3:
grade = "黄金会员"
elif data[0]['grade_id'] == 4:
grade = "铂金会员"
elif data[0]['grade_id'] == 5:
grade = "钻石会员"
buy_cbp_ratio = cbp[grade]
buy_cash_ratio = cash[grade]
sale_cash_ratio = buy_cbp_ratio + buy_cash_ratio
return buy_cbp_ratio, buy_cash_ratio, sale_cash_ratio
def second_payagent_ratio(ip, province_id, city_id, area_id):
'''
:param order: 省市区平台
:return: 二级分佣比例
'''
zong_id = [province_id, city_id, area_id]
user_id = tuple(quchong(zong_id, None))
# 使用execute()执行SQL语句
if len(user_id) == 3:
select = "SELECT agent_id,sales_ratio,tco_ratio,free_sales_ratio FROM `ecloud_orders`.`second_payagent_ratio` WHERE `agent_id` IN {0} order by field(agent_id ,{1},{2},{3});".format(
user_id, user_id[0], user_id[1], user_id[2])
elif len(user_id) == 2:
select = "SELECT agent_id,sales_ratio,tco_ratio,free_sales_ratio FROM `ecloud_orders`.`second_payagent_ratio` WHERE `agent_id` IN {0} order by field(agent_id ,{1},{2});".format(
user_id, user_id[0], user_id[1])
else:
select = "SELECT agent_id,sales_ratio,tco_ratio,free_sales_ratio FROM `ecloud_orders`.`second_payagent_ratio` WHERE `agent_id` = {0};".format(
user_id[0])
data = SQL(ip).do_mysql_dict(select)
data = xunzhao(data)
return data
def test_2_duibi(self, item):
try:
sheet_name=item['sheet_name']
case_id=item['case_id']
title=item['title']
ip = IP[item['surroundings']]
payment_method = item['payment_method']
member_level = item['member_level']
buyer_identity = item['buyer_identity']
seller_identity = item['seller_identity']
test_data=eval(item['data'])
proportion = eval(item['proportion'])
reserve_fund = eval(item['reserve_fund'])
superior=eval(item['superior'])
order = item['order']
if buyer_identity == "公海用户":
if seller_identity == "个人焕商" or seller_identity == "非焕商且已绑定个人焕商":
charge_amount = reserve_fund['charge_amount']
reserve_fund = reserve_fund['reserve_fund']
else:
charge_amount = None
reserve_fund = None
else:
charge_amount = None
reserve_fund = None
calculation_data = CalculationData().calculation_data(ip, payment_method, member_level,
buyer_identity, seller_identity, proportion,
charge_amount, reserve_fund, order)
transaction_second_payagent_ratio = eval(item['second_payagent_ratio'])
if payment_method in ["易贝", "易贝券"]:
bind_buyer_relationship_data = eval(item['bind_relationship_data'])
expected_moban = MoBan(buyer_identity, seller_identity, member_level, payment_method,
order).expected_moban(ip, test_data, superior,reserve_fund, calculation_data,
transaction_second_payagent_ratio,
bind_buyer_relationship_data)
elif payment_method in ["抵工资", "家人购", "现金"]:
bind_buyer_relationship_data = eval(item['bind_relationship_data']["储备金二级分佣对象"])
bind_payer_relationship_data = eval(item['bind_relationship_data']["支付服务费二级分佣对象"])
expected_moban = MoBan(buyer_identity, seller_identity, member_level, payment_method,
order).expected_moban(ip, test_data, superior,reserve_fund, calculation_data,
transaction_second_payagent_ratio,
bind_buyer_relationship_data, bind_payer_relationship_data)
# 写回Excel用
fanhui = FanHui().fan_hui(ip, order, expected_moban)
sql_data = SQL(ip).wallet_detail(order)
for i in range(0, len(expected_moban)):
self.assertEqual(expected_moban[i], sql_data[i])
self.assertEqual(expected_moban, sql_data)
my_logger.info("用例{0}正确！{1}".format(case_id, title))
TestResult = 'Pass'
Error = None
except AssertionError as e:
my_logger.info("用例错误！错误原因是第{0}行，{1}：".format(i + 1, e))
TestResult = 'Failed'
Error = "用例错误！错误原因是：第{0}行，{1}：".format(i + 1, e)
raise e # 异常处理完后记得抛出
finally: # 不管怎样都得写入Excel
DoExcel().write_back(test_case_path, sheet_name, case_id + 1,
str(fanhui[0]), str(fanhui[2]), str(expected_moban),
str(sql_data), TestResult, str(Error))
def test_1_buy_goods(self, item):
my_logger.info("----------开始执行用例{0}，环境是{1}----------".format(
item['case_id'], item['surroundings']))
ip = IP[item['surroundings']]
H5_Login_url = url(item['surroundings'])[0]
H5_home_url = url(item['surroundings'])[1]
buyer_phone = eval(item['data'])['buyer_phone']
seller_phone = eval(item['data'])['seller_phone']
# 获取绑定关系
superior = SuperiorTemplate().superior_template_main(
ip, item['payment_method'], item['data'], buyer_phone)
Boss_login_url = url(item['surroundings'])[2]
operational_setting = eval(item['operational_setting'])
print("----------开始BOSS后台设置运营分佣比例操作----------")
try:
self.driver.get(Boss_login_url)
#Boss后台设置运营分佣比例
BossSetting(self.driver).main(ip, item['payment_method'], superior,
operational_setting)
print("----------BOSS后台运营分佣比例设置完毕----------")
except Exception as e:
# 截图
self.report_dir.get_screenshot(self.driver)
raise e # 异常处理完后记得抛出
finally:
self.driver.quit()
print("----------开始Web操作----------")
try:
mobile_emulation = {'deviceName': 'iPhone X'}
chrome_options = webdriver.ChromeOptions()
chrome_options.add_experimental_option("mobileEmulation",
mobile_emulation)
driver = webdriver.Chrome(options=chrome_options)
driver.get(H5_Login_url)
self.lg = H5_LoginPage(driver)
self.lg.login(buyer_phone)
#定位
time.sleep(5)
Location().location(driver)
buyer_identity = item['buyer_identity']
seller_identity = item['seller_identity']
#充值服务费，增加储备池
if buyer_identity == "公海用户":
if seller_identity == "个人焕商" or seller_identity == "非焕商且已绑定个人焕商":
driver.find_element(*MyIndex.myIndex).click()
time.sleep(6)
# # 充值
Recharge().recharge(driver)
time.sleep(2)
# 支付按键
BuyGoods(driver).pay()
time.sleep(2)
# 写回储备池和充值金额
user_id = eval(item['data'])["买家"]
reserve_fund_data = SQL(ip).reserve_fund_data(user_id)
DoExcel.reserve_fund(test_case_path,
item['sheet_name'], item['case_id'],
str(reserve_fund_data))
# time.sleep(5)
driver.get(H5_home_url)
time.sleep(2)
# 选择商品
Seach_Goods().seach_goods(driver, item['goodsname'])
# 购买流程
BuyGoods(driver).BuyGood(item['payment_method'])
time.sleep(3)
# 卖家操作
driver.get(H5_Login_url)
lg = H5_LoginPage(driver)
lg.login(seller_phone)
time.sleep(2)
driver.get(H5_home_url)
time.sleep(2)
driver.find_element(*MyIndex.myIndex).click()
time.sleep(2)
# 滚动至元素【销售订单】可见，点击
ActionChains(driver).move_to_element(
driver.find_element(*MyIndex.saleOrderList)).perform()
driver.execute_script('window.scrollBy(0,500)')
time.sleep(2)
driver.find_element(*MyIndex.saleOrderList).click()
time.sleep(5)
# 确认订单
WebDriverWait(driver, 10).until(
EC.visibility_of_element_located(
(MyIndex.confirm_Order))).click()
time.sleep(6)
# 根据商品名判断流程
if "实物商品" in item['goodsname']:
ReceiptDelivery(driver).entity_good(H5_Login_url, buyer_phone)
elif "本地服务" in item['goodsname']:
ReceiptDelivery(driver).coupon_good(H5_Login_url, H5_home_url,
buyer_phone, seller_phone)
elif "商企服务" in item['goodsname']:
ReceiptDelivery(driver).Business_Services(
H5_Login_url, buyer_phone)
# 写回订单号
buyerid = eval(item['data'])['买家']
order = DoExcel.get_order(ip, test_case_path, item['sheet_name'],
item['case_id'], buyerid)
# 获取绑定关系，写回Excel
superior = SuperiorTemplate().superior_template_main(
ip, item['payment_method'], item['data'], buyer_phone)
DoExcel.superior(test_case_path, item['sheet_name'],
item['case_id'], str(superior))
# 获取上级分佣比例，写回Excel
proportion = SuperiorTemplate().fenyong_template_main(
ip, item['payment_method'], superior)
DoExcel.fenyong_bili(test_case_path, item['sheet_name'],
item['case_id'], str(proportion))
if buyer_identity == "公海用户":
if seller_identity == "个人焕商" or seller_identity == "非焕商且已绑定个人焕商":
# 跳回卖家，解除伙伴绑定
driver.get(H5_Login_url)
lg = H5_LoginPage(driver)
if seller_identity == "个人焕商":
lg.login(seller_phone)
elif seller_identity == "非焕商且已绑定个人焕商":
bangding_phone = eval(item['data'])['bangding_phone']
lg.login(bangding_phone)
time.sleep(2)
driver.get(H5_home_url)
time.sleep(2)
driver.find_element(*MyIndex.myIndex).click()
time.sleep(2)
driver.find_element(*MyIndex.myPartner).click()
time.sleep(3)
# 解绑
UntiePartner().untie_partner(driver, item['surroundings'])
my_logger.info("----------前端操作执行完毕----------")
ip = IP[item['surroundings']]
data = eval(item['data'])
buyer_id = data['买家']
# 查询买家是否绑定销售/业务焕商/TCO
if item['payment_method'] in ["易贝", "易贝券"]:
# 获取买家绑定的销售/业务焕商/TCO dict
bind_buyer_relationship_data = BingRelationshipData(
).bing_relationship_data(ip, item['payment_method'], data,
buyer_id)
# 把买家上级销售/业务焕商的上级写回Excel
DoExcel().bing_sale_id(test_case_path, item['sheet_name'],
item['case_id'],
str(bind_buyer_relationship_data))
elif item['payment_method'] in ["抵工资", "家人购", "现金"]:
bind_relationship_data = BingRelationshipData(
).bing_relationship_data(ip, item['payment_method'], data,
buyer_id)
bind_buyer_relationship_data = bind_relationship_data[0]
bind_payer_relationship_data = bind_relationship_data[1]
# 买家上级销售/业务焕商的上级，写回模板
bind_buyer_relationship_id = {
"储备金二级分佣对象": bind_buyer_relationship_data,
"支付服务费二级分佣对象": bind_payer_relationship_data
}
DoExcel().bing_sale_id(test_case_path, item['sheet_name'],
item['case_id'],
str(bind_buyer_relationship_id))
# 获取这笔订单应该【使用】的二级分佣比例
transaction_second_payagent_ratio = TransactionSecondPayagentRatio(
).transaction_second_payagent_ratio(ip, item['payment_method'],
superior, data)
# 把这笔订单所使用的二级分佣比例写回Excel
DoExcel().second_payagent_ratio(
test_case_path, item['sheet_name'], item['case_id'],
str(transaction_second_payagent_ratio))
except Exception as e:
# 截图
self.report_dir.get_screenshot(driver)
driver.quit()
raise e # 异常处理完后记得抛出
# finally:
# driver.quit()
try:
my_logger.info("----------开始进行对比----------")
ip = IP[item['surroundings']]
if buyer_identity == "公海用户":
if seller_identity == "个人焕商" or seller_identity == "非焕商且已绑定个人焕商":
charge_amount = reserve_fund_data['charge_amount']
reserve_fund = reserve_fund_data['reserve_fund']
else:
charge_amount = None
reserve_fund = None
else:
charge_amount = None
reserve_fund = None
calculation_data = CalculationData().calculation_data(
ip, item['payment_method'], item['member_level'],
buyer_identity, seller_identity, proportion, charge_amount,
reserve_fund, order)
if item['payment_method'] in ["易贝", "易贝券"]:
expected_moban = MoBan(buyer_identity, seller_identity,
item['member_level'],
item['payment_method'],
order).expected_moban(
ip, data, superior, reserve_fund,
calculation_data,
transaction_second_payagent_ratio,
bind_buyer_relationship_data)
elif item['payment_method'] in ["抵工资", "家人购", "现金"]:
expected_moban = MoBan(buyer_identity, seller_identity,
item['member_level'],
item['payment_method'],
order).expected_moban(
ip, data, superior, reserve_fund,
calculation_data,
transaction_second_payagent_ratio,
bind_buyer_relationship_data,
bind_payer_relationship_data)
# 写回Excel用
fanhui = FanHui().fan_hui(ip, order, expected_moban)
sql_data = SQL(ip).wallet_detail(order)
for i in range(0, len(expected_moban)):
self.assertEqual(expected_moban[i], sql_data[i])
self.assertEqual(expected_moban, sql_data)
my_logger.info("用例{0}正确！{1}".format(item['case_id'],
item['title']))
TestResult = 'Pass'
Error = None
except Exception as e:
#截图
self.report_dir.get_screenshot(driver)
my_logger.info("用例错误！错误原因是第{0}行，{1}：".format(i + 1, e))
TestResult = 'Failed'
Error = "用例错误！错误原因是：第{0}行，{1}：".format(i + 1, e)
raise e # 异常处理完后记得抛出
finally: # 不管怎样都得写入Excel
DoExcel().write_back(test_case_path, item['sheet_name'],
item['case_id'] + 1, str(fanhui[0]),
str(fanhui[2]), str(expected_moban),
str(sql_data), TestResult, str(Error))
# time.sleep(2)
my_logger.info("----------对比结束----------")
my_logger.info("----------用例{0}执行完毕----------".format(item['case_id']))