def query_deliver_resource_query_sql(cls, query_info, path):
base_sql = "select count(customer_id) from customer where true"
# 地区
if query_info["region"] != "全部" and query_info["region"] != "":
base_sql = base_sql + " and region_id='%s'" % (
query_info["region"])
# 部门
if query_info["deptId"] != "全部" and query_info["deptId"] != "":
base_sql = base_sql + " and department_id='%s'" % (
query_info["deptId"])
# 最后废弃人(从老师到废弃表中查询，自己废弃的记录，再来匹配，public状态的)
if query_info["workId"] != "全部" and query_info["workId"] != "":
customer_sql = "select customer_id from abandon_record where work_id='%s';" % (
query_info["workId"])
customer_id = Utility.get_connect_one(customer_sql, path)[0]
base_sql = base_sql + " and work_id in '%s'" % (customer_id)
# 状态 lastStatus
if query_info["status"] != "全部" and query_info["status"] != "":
base_sql = base_sql + " and last_status='%s'" % (
query_info["status"])
# 来源
if query_info["source"] != "全部" and query_info["source"] != "":
base_sql = base_sql + " and source='%s'" % (query_info["source"])
# 姓名
if query_info["cusInfo"] != "" and query_info["cusInfo"] == "小红旗":
info = "30DEFFB90BC0E5B38AF9118A5E8B2309"
base_sql = base_sql + " and name='%s'" % (info)
return Utility.get_connect_one(base_sql + ";", path)[0]
def test_tracking_training_resource(self, param):
sql = "select count(tracking_record_id) from tracking_record"
count_one = Utility.get_connect_one(sql, "../config/data_base")[0]
resp = Service.post_arguments(self.session, param["url"],
param["step"])
# 解密、日期格式（创建日期）
count_two = Utility.get_connect_one(sql, "../config/data_base")[0]
if resp.text == "newStudent" or count_two > count_one:
actual = "tracking-success"
else:
actual = "tracking-fail"
self.assertEqual(actual, param["expect"])
def training_resource_query_sql(cls, query_info, path):
base_sql = "select count(customer_id) from customer where pool_type!='public'"
if query_info["poolType"] != "全部" and query_info["poolType"] != "":
base_sql = "select count(customer_id) from customer where pool_type='%s'" % (
query_info["poolType"])
if query_info["lastStatus"] != "全部" and query_info["lastStatus"] != "":
status_sql = "select dict_key from dictionary_data where dict_type_id=1 and dict_value='%s'" % (
query_info["lastStatus"])
last_status = Utility.get_connect_one(status_sql, path)[0]
base_sql = base_sql + " and last_status='%s'" % (last_status)
if query_info["source"] != "全部" and query_info["source"] != "":
source_sql = "select dict_key from dictionary_data where dict_type_id=4 and dict_value='%s'" % (
query_info["source"])
source = Utility.get_connect_one(source_sql, path)[0]
base_sql = base_sql + " and source='%s'" % (source)
if query_info["s_time"] != "" and query_info["e_time"] != "":
base_sql = base_sql + \
" and create_time between '%s' and '%s'" % (
query_info["s_time"], query_info["e_time"])
elif query_info["s_time"] == "" and query_info["e_time"] != "":
base_sql = base_sql + \
" and create_time before '%s'" % (query_info["e_time"])
elif query_info["s_time"] != "" and query_info["e_time"] == "":
base_sql = base_sql + \
" and create_time after '%s'" % (query_info["s_time"])
# 咨询师
if query_info["workId"] != "全部" and query_info["workId"] != "":
consult_sql = "select work_id from employee where employee_name='%s';" % (
query_info["workId"])
consult = Utility.get_connect_one(consult_sql, path)[0]
base_sql = base_sql + " and work_id='%s'" % (consult)
if query_info["cusInfo"] != "" and query_info["cusInfo"] == "一瓢":
info = "1A5EF59031E0FDEB"
base_sql = base_sql + " and name='%s'" % (info)
return Utility.get_connect_one(base_sql + ";", path)[0]
def allot_resource_query_sql(cls, query_info, path):
base_sql = "select count(customer_id) from customer where work_id='0' and allot_time =''"
if query_info["source"] != "全部" and query_info["source"] != "":
source_sql = "select dict_key from dictionary_data where dict_type_id=4 and dict_value='%s'" % (
query_info["source"])
source = Utility.get_connect_one(source_sql, path)[0]
base_sql = base_sql + " and source='%s'" % (source)
if query_info["info"] != "" and query_info["info"] == "软件小达人":
info = "BF4051071EFC93BE29B8A46D5F3C97CD"
base_sql = base_sql + " and name='%s'" % (info)
return Utility.get_connect_one(base_sql + ";", path)[0]
def test_claim(self, url, claim_info, expect):
claim_url = self.base_url + url["claim_url"]
# 提交前，看数量
sql = "select count(work_id) from customer where pool_type='public'"
count_sql = Utility.get_connect_one(sql, '../../config/data_base')
# 无响应
Service.post_arguments(self.session, claim_url, claim_info)
# 提交后
count_sql_again = Utility.get_connect_one(sql,
'../../config/data_base')
# 无响应，数据库查询
if count_sql_again[0] <= count_sql[0]:
# 响应断言
actual = "claim-pass"
else:
actual = "claim-pass"
self.assertEqual(actual, expect)
def test_commit_allocating_resource(self, url, commit_allocating_resource_info, expect):
open_allocating_resource_url = self.base_url + url["commit_allocating_resource_info"]
# 提交前，看数量
sql = "select count(work_id) from customer where work_id='%s'" % (commit_allocating_resource_info["work_id"])
count_work_id = Utility.get_connect_one(sql, '../../config/data_base')
# 响应为空
Service.post_arguments(self.session, open_allocating_resource_url, commit_allocating_resource_info)
# 提交后
count_work_id_commit = Utility.get_connect_one(sql, '../../config/data_base')
# 无响应，数据库查询
if count_work_id_commit[0] >= count_work_id[0]:
actual = "commit-allocating-resource-pass"
else:
actual = "commit-allocating-resource-fail"
self.assertEqual(actual, expect)
def test_tracking_resource(self, url, tracking_resource_info, tracking_resource_sql, expect):
# 调用之前，先查看数据库的记录数
recode_count = Utility.get_connect_one(tracking_resource_sql["sql"], "../../config/data_base")
# 数据准备
tracking_resource_url = self.base_url + url["tracking_resource_url"]
tracking_resource_url_resp = TrainingResource.tracking_resource(
self.session, tracking_resource_url, tracking_resource_info
)
print(tracking_resource_url_resp.text)
# 响应无内容，在数据库添加记录判断
recode_count_again = Utility.get_connect_one(tracking_resource_sql["sql"], "../../config/data_base")
# 电话断言
if recode_count_again[0] > recode_count[0]:
actual = "tracking-resource-pass"
else:
actual = "tracking-resource-fail"
self.assertEqual(actual, expect)
def test_submit(self, url, submit_info, expect):
submit_url = self.base_url + url["submit_url"]
# 提交前，看数量
sql = "select count(work_id) from customer where work_id='%s'" % (
submit_info["workId"])
count_work_id = Utility.get_connect_one(sql, '../../config/data_base')
submit_resp = Transferee.submit_trans(self.session, submit_url,
submit_info)
# 提交后
count_work_id_submit = Utility.get_connect_one(
sql, '../../config/data_base')
# 无响应，数据库查询
if count_work_id_submit[0] >= count_work_id[0]:
actual = "submit-pass"
else:
actual = "submit-fail"
self.assertEqual(actual, expect)
def test_target_tracking_training_resource(self, param):
resp = Service.post_arguments(self.session, param["url"],
param["step"])
sql = "select tracking_times from customer where customer_id = '%s'" % (
param["step"]["id"])
count_one = Utility.get_connect_one(sql, "../config/data_base")[0]
if count_one == len(resp.json()):
actual = "target-tracking-success"
else:
actual = "target-tracking-fail"
self.assertEqual(actual, param["expect"])
def update_deliver_resource_query_sql(cls, query_info, path):
base_sql = "select count(customer_id) from customer where true"
# 地区
if query_info["regionId"] != "全部" and query_info["regionId"] != "":
base_sql = base_sql + " and region_id='%s'" % (
query_info["regionId"])
# 部门
if query_info["deptId"] != "全部" and query_info["deptId"] != "":
base_sql = base_sql + " and department_id='%s'" % (
query_info["deptId"])
# 咨询师
if query_info["workId"] != "全部" and query_info["workId"] != "":
base_sql = base_sql + " and work_id='%s'" % (query_info["workId"])
return Utility.get_connect_one(base_sql + ";", path)[0]
def test_query_trans_feree(self, url, query_trans_feree_info, expect):
# 数据准备
query_trans_feree_url = self.base_url + url["query_trans_feree_url"]
query_trans_feree_resp = Transferee.query_trans_feree(
self.session, query_trans_feree_url, query_trans_feree_info)
query_result = query_trans_feree_resp.json()
# 拼接sql
sql = Service.sql_query_trans_feree(
query_trans_feree_info, "../../config/transferee_query_sql")
# 调用数据库
sql_result = Utility.get_connect_one(sql, "../../config/data_base")
# print(sql_result)
# 断言
if sql_result[0] == query_result["totalRow"]:
actual = "query-trans-feree-pass"
else:
actual = "query-trans-feree-pass"
self.assertEqual(actual, expect)
def test_query_training_resource(self, url, query_training_resource_info, decode_info, expect):
# 数据准备
query_training_resource_url = self.base_url + url["query_training_resource_url"]
query_training_resource_resp = TrainingResource.query_training_resource(
self.session, query_training_resource_url, query_training_resource_info)
query_result = query_training_resource_resp.json()
# 拼接sql
sql = Service.sql_query_training_resource(decode_info, "../../config/base_sql")
# 调用数据库
sql_result = Utility.get_connect_one(sql, "../../config/data_base")
# 断言
if sql_result[0] == query_result["totalRow"] and WoniuBoss.decode(
decode_info["cusInfo"]) == query_result["list"][0]["name"]:
actual = "query-training-resource-pass"
else:
actual = "query-training-resource-fail"
self.assertEqual(actual, expect)
def test_query_common_resource_pool(self, url,
query_common_resource_pool_info,
expect):
# 数据准备，调用
query_common_resource_pool_url = self.base_url + url[
"query_common_resource_pool_url"]
query_common_resource_pool_resp = Service.post_arguments(
self.session, query_common_resource_pool_url,
query_common_resource_pool_info)
query_common_resp_result = query_common_resource_pool_resp.json()
# 查询数据库
sql = Service.sql_query_common_resource_pool(
query_common_resource_pool_info,
"../../config/query_common_resource_pool_sql")
sql_result = Utility.get_connect_one(sql, "../../config/data_base")
# 响应数据数量
if sql_result[0] == query_common_resp_result["totalRow"]:
actual = "query-common-resource-pass"
else:
actual = "query-common-resource-fail"
self.assertEqual(actual, expect)
def sql_query_training_resource(cls, query_info, path):
base_sql = Utility.read_json(path)
# 1111111
if query_info["poolType"] == "" and query_info["empName"] == "" and query_info["lastStatus"] == "" and \
query_info["source"] == "" and query_info["s_time"] == "" and query_info["e_time"] == "" and \
query_info["cusInfo"] == "" :
return base_sql["1"]
# 1112222
elif query_info["poolType"] == "" and query_info["empName"] == "" and query_info["lastStatus"] == "" and \
query_info["source"] != "" and query_info["s_time"] != "" and query_info["e_time"] != "" and \
query_info["cusInfo"] != "" :
return base_sql["2"] % (query_info["source"],
query_info["cusInfo"],
query_info["s_time"], query_info["e_time"])
# 1221122
elif query_info["poolType"] == "" and query_info["empName"] != "" and query_info["lastStatus"] != "" and \
query_info["source"] == "" and query_info["s_time"] == "" and query_info["e_time"] != "" and \
query_info["cusInfo"] != "" :
# 判断用户信息 类别
condition = Service.judge_cusInfo(query_info["cusInfo"])
empName_work_id = Utility.get_connect_one(
"select work_id from employee where employee_name='%s'" %
(query_info["empName"]), '../../config/data_base')
if condition == 1:
return base_sql["3"][0] % (
empName_work_id, query_info["lastStatus"], empName_work_id,
query_info["e_time"])
elif condition == 2:
return base_sql["3"][1] % (
empName_work_id, query_info["lastStatus"], empName_work_id,
query_info["e_time"])
elif condition == 3:
return base_sql["3"][2] % (
empName_work_id, query_info["lastStatus"], empName_work_id,
query_info["e_time"])
# 1222211
elif query_info["poolType"] == "" and query_info["empName"] != "" and query_info["lastStatus"] != "" and \
query_info["source"] != "" and query_info["s_time"] != "" and query_info["e_time"] == "" and \
query_info["cusInfo"] == "" :
empName_work_id = Utility.get_connect_one(
"select work_id from employee where employee_name='%s'" %
(query_info["empName"]), '../../config/data_base')
return base_sql["4"] % (empName_work_id, query_info["lastStatus"],
query_info["source"], query_info["s_time"])
# 2121212
elif query_info["poolType"] != "" and query_info["empName"] == "" and query_info["lastStatus"] != "" and \
query_info["source"] == "" and query_info["s_time"] != "" and query_info["e_time"] == "" and \
query_info["cusInfo"] != "":
condition = Service.judge_cusInfo(query_info["cusInfo"])
if condition == 1:
return base_sql["5"][0] % (
query_info["poolType"], query_info["lastStatus"],
query_info["cusInfo"], query_info["s_time"])
elif condition == 2:
return base_sql["5"][1] % (
query_info["poolType"], query_info["lastStatus"],
query_info["cusInfo"], query_info["s_time"])
elif condition == 3:
return base_sql["5"][2] % (
query_info["poolType"], query_info["lastStatus"],
query_info["cusInfo"], query_info["s_time"])
# 2122121
elif query_info["poolType"] != "" and query_info["empName"] == "" and query_info["lastStatus"] != "" and \
query_info["source"] != "" and query_info["s_time"] == "" and query_info["e_time"] != "" and \
query_info["cusInfo"] == "":
return base_sql["6"] % (query_info["poolType"],
query_info["lastStatus"],
query_info["source"], query_info["e_time"])
# 2211221
elif query_info["poolType"] != "" and query_info["empName"] != "" and query_info["lastStatus"] == "" and \
query_info["source"] == "" and query_info["s_time"] != "" and query_info["e_time"] != "" and \
query_info["cusInfo"] == "":
empName_work_id = Utility.get_connect_one(
"select work_id from employee where employee_name='%s'" %
(query_info["empName"]), '../../config/data_base')
return base_sql["7"] % (query_info["poolType"], empName_work_id,
query_info["s_time"], query_info["e_time"])
# 2212112
elif query_info["poolType"] != "" and query_info["empName"] != "" and query_info["lastStatus"] == "" and \
query_info["source"] != "" and query_info["s_time"] == "" and query_info["e_time"] == "" and \
query_info["cusInfo"] != "":
condition = Service.judge_cusInfo(query_info["cusInfo"])
empName_work_id = Utility.get_connect_one(
"select work_id from employee where employee_name='%s'" %
(query_info["empName"]), '../../config/data_base')
if condition == 1:
return base_sql["8"][0] % (
query_info["poolType"], empName_work_id,
query_info["source"], query_info["cusInfo"])
elif condition == 2:
return base_sql["8"][1] % (
query_info["poolType"], empName_work_id,
query_info["source"], query_info["cusInfo"])
elif condition == 3:
return base_sql["8"][2] % (
query_info["poolType"], empName_work_id,
query_info["source"], query_info["cusInfo"])