def ms_query_seller_id_order_list_by_user_id(user_id, order_status, ps,
pn):
sql = "SELECT o.order_no orderNo, o.order_status orderStatus, CASE o.order_status WHEN 10 THEN '待下单'" \
" WHEN 20 THEN '待上门' WHEN 30 THEN '待结款' WHEN 40 THEN '已完成' WHEN 50 THEN '已取消'" \
" END AS orderStatusDesc, CASE WHEN ( oi.order_type = 10 AND oi.pay_status = 10 ) THEN '未支付定金' " \
"WHEN ( oi.order_type = 10 AND oi.pay_status = 20 ) THEN '已支付定金' WHEN ( oi.order_type = 20 AND " \
"oi.pay_status = 20 ) THEN '未支付尾款' WHEN ( oi.order_type = 20 AND oi.pay_status = 20 ) " \
"THEN '已支付尾款' END AS payStatusDesc, o.earnest_money_price, o.tail_money_price, " \
"bu.mobile buyerMobile, CASE o.service_type WHEN 10 THEN '苗叔上门种植服务' " \
"WHEN 20 THEN '苗叔上门养护服务' END AS serviceTypeDesc, o.shop_name shopName FROM " \
"`ms-order`.t_service_order o LEFT JOIN `ms-order`.t_service_order_item oi ON o.id = oi.order_id " \
" LEFT JOIN `ms-user`.t_base_user u ON u.id = o.seller_id LEFT JOIN `ms-user`.t_base_user bu ON " \
"bu.id = o.buyer_id WHERE u.id = %s AND o.order_status = %s GROUP BY oi.order_id " \
"ORDER BY o.create_time DESC LIMIT %s, %s" % (user_id, order_status, (pn-1)*ps, ps)
order_list = DataBaseOperate().operate(host_ip, "ms-order", sql)
return order_list
def ms_query_shop_info_by_seller_id(seller_id, current=True):
if current:
sql = 'SELECT ta.address, ta.area, ta.city, ta.lat, ta.lng, ta.province, ta.shop_id, ' \
'ts.avatar, ts.contact, ts.mobile, ts.`name`, IFNULL(tpv.pv,0) pv, ts.seller_id, ts.`status`, ts.type ' \
'FROM `ms-shop`.t_shop ts ' \
'LEFT JOIN `ms-shop`.t_address ta ON ta.shop_id = ts.id ' \
'LEFT JOIN t_page_view AS tpv ON tpv.shop_id = ts.id ' \
'WHERE ta.`status`=10 and ts.seller_id = %s;' % seller_id
else:
sql = 'SELECT ta.address, ta.area, ta.city, ta.lat, ta.lng, ta.province, ta.shop_id, ' \
'ts.avatar, ts.contact, ts.mobile, ts.`name`, IFNULL(tpv.pv,0) pv, ts.seller_id, ts.`status`, ts.type ' \
'FROM `ms-shop`.t_shop ts ' \
'LEFT JOIN `ms-shop`.t_address ta ON ta.shop_id = ts.id ' \
'LEFT JOIN t_page_view AS tpv ON tpv.shop_id = ts.id ' \
'WHERE ts.seller_id = %s;' % seller_id
get_shop_info = DataBaseOperate().operate(host_ip, "ms-shop", sql)
return get_shop_info
def test0006(self, login_user_mobile="13828898130", passwd="8dw1bC"):
"""
运营后台端-分类上下架.正向流程-郭勇
接口路径:POST /admin/category/update-status
:return:
"""
db_get_category = DataBaseOperate().operate("39.104.28.40", "ms-product",
"SELECT id,parent,orders FROM t_category "
"WHERE is_delete = 0 LIMIT 50")
random_category = random.choice(db_get_category)
if random_category["parent"] != 0:
category_type = 2
else:
category_type = 1
set_category_status = random.choice([10, 20])
self.__set_user(login_user_mobile, passwd, True)
self.log.info("开始执行运营后台端-分类上下架接口正向测试，接口路径/admin/category/update-status")
self.log.info("将%s状态设置为%s" % (random_category["id"], set_category_status))
self.product_test._admin_category_update_status(category_type, random_category["id"], set_category_status)
def test0003(self, login_user_mobile="13828898130", passwd="8dw1bC"):
"""
运营后台端-分类详情.正向流程-郭勇
接口路径:POST /admin/category/detail
:return:
"""
# 随机获取一个一季分类信息
db_get_category = DataBaseOperate().operate("39.104.28.40", "ms-product",
"SELECT id,parent FROM t_category "
"WHERE `status` = 10 AND is_delete = 0 LIMIT 50")
random_category = random.choice(db_get_category)
if random_category["parent"] != 0:
category_type = 2
else:
category_type = 1
self.__set_user(login_user_mobile, passwd, True)
self.log.info("开始执行运营后台端-分类详情接口正向测试，接口路径/admin/category/detail")
self.log.info("查看%s级分类%s详情" % (category_type, random_category["id"]))
self.product_test._admin_category_detail(category_type, random_category["id"])
def test0004(self, login_user_mobile="13828898130", passwd="8dw1bC"):
"""
运营后台端-编辑分类.正向流程-郭勇
接口路径:POST /admin/category/edit
:return:
"""
# 随机获取一个一季分类信息
category_name = self.faker_zh.word()
category_status = random.choice([10, 20])
db_get_category = DataBaseOperate().operate("39.104.28.40", "ms-product",
"SELECT id,parent FROM t_category "
"WHERE `status` = 10 AND is_delete = 0 LIMIT 50")
random_category = random.choice(db_get_category)
if random_category["parent"] != 0:
current_category_type = 2
else:
current_category_type = 1
self.__set_user(login_user_mobile, passwd, True)
self.log.info("开始执行运营后台端-编辑分类接口正向测试，接口路径/admin/category/edit")
self.log.info("将id %s原有分类，名称修改为%s，状态修改为%s" % (random_category["id"], category_name, category_status))
self.product_test._admin_category_edit(current_category_type, random_category["id"], category_name, category_status, int(time.time()), None, None)
def test0051(self, login_user_mobile="13828898130", passwd="8dw1bC"):
"""
单接口测试
web端-生成病例.正向流程-郭勇
接口路径：POST /admin/plant-case/create
:return:
"""
excel_info = xlrd.open_workbook(
path.abspath(".") +
"/utils/Docs/PestDatabase.xlsx").sheet_by_index(0)
# 生成植物病例信息
pest_row = random.randint(1, 337)
plant_case_topic = excel_info.cell_value(pest_row, 0) + \
excel_info.cell_value(pest_row, 2) + \
"有" + \
excel_info.cell_value(pest_row, 1)
plant_case_symptom = excel_info.cell_value(pest_row, 3)
plant_case_treatment_method = excel_info.cell_value(pest_row, 6) \
if \
len(excel_info.cell_value(pest_row, 6)) > len(excel_info.cell_value(pest_row, 7)) \
else \
excel_info.cell_value(pest_row, 7)
self.plantTest.set_user(login_user_mobile, passwd, True)
db_get_users = DataBaseOperate().operate(
"39.104.28.40", "ms-user", "SELECT id FROM t_base_user "
"WHERE account_type = 1 "
"AND is_delete = 0 "
"AND account_status = 1 "
"LIMIT 10")
db_get_user_id = random.choice(db_get_users)["id"]
# 开始执行测试
self.log.info("开始执行web端-生成病例接口正向测试，接口路径/admin/plant-case/create")
self.log.info("给用户%d生成了病例。\n病例主题：%s\n症状描述：%s\n诊断处方：%s\n" %
(db_get_user_id, plant_case_topic, plant_case_symptom,
plant_case_treatment_method))
self.plantTest.admin_plant_case_create(plant_case_topic,
plant_case_symptom,
plant_case_treatment_method,
db_get_user_id)
def test0032(self, login_user_mobile="13592478554"):
"""
隐藏房间.正向流程-郭勇
接口路径：POST /mobile/room/show-status
:return:
"""
db_get_user_id = self.plantTest.set_user(login_user_mobile).user_id
# 通过当前登录用户获取，当前用户家庭中的所有房间
db_get_user_homes = DataBaseOperate().operate(
"39.104.28.40", "ms-plant",
"SELECT id FROM t_room WHERE home_id = ("
"SELECT home_id FROM t_member WHERE user_id = %s )"
" AND is_delete = 0" % db_get_user_id)
room_ids = []
for x in db_get_user_homes:
room_ids.append(x['id'])
room_id = random.choice(room_ids)
room_status = random.randint(0, 1)
self.log.info("开始执行隐藏房间正向测试，接口路径/mobile/room/show-status")
self.log.debug("将房间%d隐藏状态设置为%d" % (room_id, room_status))
self.plantTest.mobile_room_show_status(room_id, room_status)
def test0037(self, login_user_mobile="13592478554"):
"""
单接口测试
家人备注.正向流程-郭勇
接口路径：POST /mobile/member-nick/edit
:return:
"""
db_get_user_id = self.plantTest.set_user(login_user_mobile).user_id
self.log.info("开始执行家人备注正向测试，接口路径/mobile/member-nick/edit")
# 获取当前家庭中所有成员
db_get_user_home_users = DataBaseOperate().operate(
"39.104.28.40", "ms-plant",
"SELECT user_id FROM t_member WHERE home_id = ("
"SELECT home_id FROM t_member WHERE user_id = %s) "
"AND user_id != %s" % (db_get_user_id, db_get_user_id))
get_users = []
for x in db_get_user_home_users:
get_users.append(x['user_id'])
# 随机获给一个家庭成员进行备注
nick_user = random.choice(get_users)
nick_name = self.faker_zh.name_female()
self.plantTest.mobile_member_nick_edit(nick_user, nick_name)
def ms_query_supplier_store_unit_list_by_is_delete(is_delete):
sql = 'SELECT count(*) FROM t_store_unit WHERE is_delete= %s;' % is_delete
user_info = DataBaseOperate().operate(host_ip, "ms-product", sql)
return user_info
def ms_query_supplier_category_list_by_category_id(category_id):
sql = 'SELECT count(*) FROM t_category WHERE `status` = 10 AND parent= %s;' % category_id
user_info = DataBaseOperate().operate(host_ip, "ms-product", sql)
return user_info
def miaoshu_query_user_info_by_email(email, user_type):
sql = 'SELECT * FROM t_user WHERE email = %s AND is_delete = 0 AND account_type = %s;' \
% (str(email), str(user_type))
user_info = DataBaseOperate().operate(host_ip, "ms-user", sql)
return user_info
def ms_query_city_list_by_parent_id(parent_id=0):
sql = 'SELECT `id`,`name` FROM `ms-kbms`.t_region te WHERE te.parent_id= %s' % str(
parent_id)
city_list = DataBaseOperate().operate("39.104.28.40", "ms-kbms", sql)
return city_list
def ms_mobile_plant_add_by_room_id(room_id):
sql = 'SELECT t_room.home_id,t_plant.room_id,t_plant.plant_wiki_id,t_plant.plant_name,t_plant.water_cycle_time,' \
't_plant.img_url FROM `ms-plant`.t_room,`ms-plant`.t_plant WHERE t_plant.room_id=t_room.id AND ' \
't_plant.room_id = %s ORDER BY t_plant.id DESC' % room_id
plant_list = DataBaseOperate().operate("39.104.28.40", "ms-plant", sql)
return plant_list[0]
def ms_query_supplier_store_list_by_shop_id(shop_id):
sql = 'SELECT count(*) FROM t_product WHERE shop_id= %s;' % shop_id
user_info = DataBaseOperate().operate(host_ip, "ms-product", sql)
return user_info
def ms_query_supplier_freight_template_get_by_freight_id(freight_id):
sql = 'SELECT id, title, freigh_per_km, free_price, free_distance, free_price_status,' \
'free_distance_status FROM t_freight_template WHERE id= %s;' % freight_id
user_info = DataBaseOperate().operate(host_ip, "ms-shop", sql)
return user_info
def ms_query_supplier_freight_template_all_by_shop_id(shop_id):
sql = 'SELECT count(*) FROM t_freight_template WHERE shop_id= %s AND is_delete=0;' % shop_id
user_info = DataBaseOperate().operate(host_ip, "ms-shop", sql)
return user_info
def ms_query_all_address_info_by_shop_id(shop_id):
sql = 'SELECT id,shop_id, lng,lat,province,city,area,address,`status` FROM t_address WHERE shop_id=%s ' \
'ORDER BY id DESC' % shop_id
get_address_info = DataBaseOperate().operate(host_ip, "ms-shop", sql)
return get_address_info
def ms_query_latest_address_info_by_shop_id(shop_id):
sql = 'SELECT id,shop_id, lng,lat,province,city,area,address FROM t_address WHERE shop_id=%s AND' \
'`status` = 10 ;' % shop_id
get_address_info = DataBaseOperate().operate(host_ip, "ms-shop", sql)
return get_address_info
def ms_query_supplier_freight_template_delete_by_freight_id(freight_id):
sql = 'SELECT id, shop_id, is_delete FROM t_freight_template WHERE id= %s;' % freight_id
user_info = DataBaseOperate().operate(host_ip, "ms-shop", sql)
return user_info
def ms_query_channel_product_category_info_by_id_delete(is_delete):
sql = 'SELECT id,`name`,parent FROM t_category WHERE is_delete=%s ORDER BY id DESC' % is_delete
product_category_info = DataBaseOperate().operate(
"39.104.28.40", "ms-product", sql)
return product_category_info
def ms_query_supplier_status_update_by_product_pcode(product_pcode):
sql = 'SELECT pcode, status, is_delete FROM t_product WHERE pcode= %s;' % product_pcode
user_info = DataBaseOperate().operate(host_ip, "ms-product", sql)
return user_info
def ms_query_supplier_shop_get_by_user_id(user_id):
sql = 'SELECT * FROM t_shop WHERE seller_id = %s;' % user_id
user_info = DataBaseOperate().operate(host_ip, "ms-shop", sql)
return user_info
def ms_query_supplier_store_update_by_pcode(pcode_list):
sql = 'SELECT pcode, store FROM t_product WHERE pcode in %s;' % pcode_list
user_info = DataBaseOperate().operate(host_ip, "ms-product", sql)
return user_info
def ms_query_supplier_update_shop_info_by_user_id(user_id):
sql = 'SELECT name, mobile, contact, avatar FROM t_shop WHERE seller_id = %s;' % user_id
user_info = DataBaseOperate().operate(host_ip, "ms-shop", sql)
return user_info
def ms_shop_t_address_list_by_shop_id(shop_id):
sql = 'SELECT * FROM `ms-shop`.t_address WHERE shop_id = %s and status=10' % str(
shop_id)
shop_list = DataBaseOperate().operate("39.104.28.40", "ms-shop", sql)
return shop_list
def ms_query_supplier_update_address_by_shop_id(shop_id):
sql = 'SELECT province,city,area,address FROM t_shop_ext_supplier WHERE shop_id = %s;' % shop_id
user_info = DataBaseOperate().operate(host_ip, "ms-shop", sql)
return user_info
def miaoshu_query_user_info_by_mobile(mobile, user_type):
sql = 'SELECT * FROM `ms-user`.t_base_user tu ' \
'WHERE tu.mobile = %s AND tu.is_delete = 0 AND tu.account_type = %s;' % (str(mobile), str(user_type))
user_info = DataBaseOperate().operate(host_ip, "ms-user", sql)
return user_info
def ms_query_supplier_switch_status_by_shop_id(shop_id):
sql = 'SELECT status FROM t_shop WHERE id = %s;' % shop_id
user_info = DataBaseOperate().operate(host_ip, "ms-shop", sql)
return user_info
def ms_query_user_address_by_user_id(user_id):
sql = 'SELECT * FROM t_address WHERE user_id = %s AND is_delete = 0 order by is_default DESC,edit_time DESC;'\
% (str(user_id))
address_list_info = DataBaseOperate().operate(host_ip, "ms-user", sql)
return address_list_info
def ms_query_supplier_category_list_all_by_status(status):
sql = 'SELECT count(*) FROM t_category WHERE `status` = %s;' % status
user_info = DataBaseOperate().operate(host_ip, "ms-product", sql)
return user_info