def test_students_vacate_add(self, add_info):
# 新增按钮
Service.get_ele_location_method(self.driver, 'css',
"#content > div.row.con-margin.con-body-con > div > div > div > "
"div.col-lg-12.col-md-12.col-xs-12.con-body-padding.text-left > div > button").click()
# 新增操作
StudentsVacate.students_vacate_add(self.driver, add_info)
# 数据库数据量查询
sql_result = Utility.get_connect_one(
"select count(student_id) from student_leave",
students_vacate_page_data["test_students_vacate_query"]["sql_bath"]
)[0]
# 新增操作
# StudentsVacate.students_vacate_add(self.driver, add_info)
# 数据库数据量查询
sql_result_again = Utility.get_connect_one(
"select count(student_id) from student_leave",
students_vacate_page_data["test_students_vacate_query"]["sql_bath"]
)[0]
# 断言
if sql_result < sql_result_again:
actual = "add-students-vacate-success"
else:
actual = "add-students-vacate-fail"
# 预期不符，截图
Service.get_screen(self.driver, actual, add_info["expect"],
"/query_students_vacate", "C:/Users/wang/Desktop/woniuboss4.0/gui/error")
self.assertEqual(actual, add_info["expect"])
def students_vacate_query_sql(cls, query_info, path):
base_sql = "select count(student_id) from student_leave"
flag = 0
if query_info["region"] != "全部" and query_info["region"] != "":
region_table = "select count(s.student_id) from student_leave s " \
"inner join class c on s.class_id=c.class_id where " \
"c.region_id in (select region_id from region where region_name='%s')" \
% query_info["region"]
base_sql = region_table
flag = 1
if query_info["status"] != "全部" and query_info["status"] != "":
if flag:
base_sql = base_sql + " and leave_status='%s'" %query_info["status"]
else:
base_sql = base_sql + " where leave_status='%s'" %query_info["status"]
flag = 1
if query_info["name"] != "":
status_sql = "select student_id from student where student_name='%s'" % (
query_info["name"])
status_id = Utility.get_connect_one(status_sql, path)[0]
if flag:
base_sql = base_sql + " and student_id=%d" % (status_id)
else:
base_sql = base_sql + " where student_id=%d" % (status_id)
return Utility.get_connect_one(base_sql + ";", path)[0]
def test_marketing_query(self, marketing_query_info, expect):
# 添加cookie信息，跳转到登陆后首页
Service.add_cookie(self.driver, self.url, "../config/data_cookie")
# 点击市场营销
Service.get_ele(self.driver, By.PARTIAL_LINK_TEXT, "市场营销").click()
Marketing.marketing_query(self.driver, marketing_query_info)
# 判断是否查询出数据
query_result = Service.get_ele(self.driver, By.CSS_SELECTOR,
"table#netCus-table tbody").text
# 市场营销有上传过数据
if query_result != "无符合条件的记录":
# 前端页面显示数据数，根据状态（不同状态数量不同）
query_count_result = Service.get_ele(
self.driver, By.XPATH, "//span[@class='pagination-info']").text
result = re.match('^显示.*，总.*?(\d+)', query_count_result)
# 查询数据库，对比数据条数是否正确
if marketing_query_info['status'] == "全部" and marketing_query_info[
'area'] == "全部":
sql = 'select count(customer_id) from customer where source="%s" and ' \
'create_time between "%s" and "%s"'%('网络',marketing_query_info['starttime'],marketing_query_info['endtime'])
query_connect_count = Utility.get_connect_one(
sql, "../config/data_base")
elif marketing_query_info[
'status'] == "全部" and marketing_query_info['area'] != "全部":
sql = 'select count(customer_id) from customer where source="%s" and region="%s" and create_time between "%s" and "%s"' \
%('网络',marketing_query_info['area'],marketing_query_info['starttime'],marketing_query_info['endtime'])
query_connect_count = Utility.get_connect_one(
sql, "../config/data_base")
elif marketing_query_info[
'status'] != "全部" and marketing_query_info['area'] == "全部":
sql = 'select count(customer_id) from customer where source="%s" and last_status="%s" and create_time between "%s" and "%s"' \
% ('网络', marketing_query_info['status'],marketing_query_info['starttime'],marketing_query_info['endtime'])
query_connect_count = Utility.get_connect_one(
sql, "../config/data_base")
else:
sql = 'select count(customer_id) from customer where source="%s" and last_status="%s" and region="%s" and create_time between "%s" and "%s"' \
% ('网络', marketing_query_info['status'],marketing_query_info['area'],marketing_query_info['starttime'],marketing_query_info['endtime'])
query_connect_count = Utility.get_connect_one(
sql, "../config/data_base")
if int(result.group(1)) == int(query_connect_count[0]):
actual = 'query-pass'
else:
actual = 'query-fail'
self.assertEqual(actual, expect)
else:
print("查询为空")
self.driver.quit()
def test_add_employee(self, add_info):
# 数据库员工数量
add_sql = page_data["test_add_employee"]["add_sql"]
sql_result = Utility.get_connect_one(
add_sql, page_data["test_add_employee"]["sql_bath"])
PersonnelManagement.add_employee(self.driver, add_info)
# 数据库对比
sql_result_again = Utility.get_connect_one(
add_sql, page_data["test_add_employee"]["sql_bath"])
if sql_result_again > sql_result:
actual = "add_employee-success"
else:
actual = "add_employee-fail"
self.assertEqual(actual, add_info["expect"])
def person_query_sql(cls, query_info, path):
base_sql = "select count(employee_id) from employee"
flag = 0
if query_info["region"] != "全部" and query_info["region"] != "":
region_sql = "select region_id from region where region_name='%s'" %query_info["region"]
region_id = Utility.get_connect_one(region_sql, path)[0]
if flag:
base_sql = base_sql + " and region_id=%d"%region_id
else:
base_sql = base_sql + " where region_id=%d"%region_id
flag = 1
if query_info["department"] != "全部" and query_info["department"] != "":
department_sql = "select department_id from department where department_name='%s' and region_id=%d" %(query_info["department"],region_id)
department_id = Utility.get_connect_one(department_sql, path)[0]
if flag:
base_sql = base_sql + " and department_id=%d"%department_id
else:
base_sql = base_sql + " where department_id=%d"%department_id
flag = 1
if query_info["status"] != "全部" and query_info["status"] != "":
status_sql = "select dict_key from dictionary_data where dict_type_id=2 and dict_value='%s'" % (
query_info["status"])
status_id = Utility.get_connect_one(status_sql, path)[0]
if flag:
base_sql = base_sql + " and emp_status='%s'"%status_id
else:
base_sql = base_sql + " where emp_status='%s'"%status_id
flag = 1
if query_info["name"] != "":
if flag:
base_sql = base_sql + " and employee_name like '%s'"%(query_info["name"]+"%")
else:
base_sql = base_sql + " where employee_name like '%s'"%(query_info["name"]+"%")
return Utility.get_connect_one(base_sql+";", path)[0]
def class_manager_query_sql(cls, query_info, path):
base_sql = "select count(class_id) from class"
flag = 0
if query_info["region"] != "全部" and query_info["region"] != "":
region_sql = "select region_id from region where region_name='%s'" % query_info["region"]
region_id = Utility.get_connect_one(region_sql, path)[0]
if flag:
base_sql = base_sql + " and region_id=%d" % region_id
else:
base_sql = base_sql + " where region_id=%d" % region_id
flag = 1
if query_info["class_status"] != "全部" and query_info["class_status"] != "":
status_sql = "select dict_key from dictionary_data where dict_type_id=37 and dict_value='%s'" % (
query_info["class_status"])
status_id = Utility.get_connect_one(status_sql, path)[0]
if flag:
base_sql = base_sql + " and opening_status='%s'" % status_id
else:
base_sql = base_sql + " where opening_status='%s'" % status_id
flag = 1
return Utility.get_connect_one(base_sql + ";", path)[0]
def marketing_query_sql(cls, query_info, path):
base_sql = "select count(customer_id) from customer"
flag = 0
if query_info["area"] != "全部" and query_info["area"] != "":
area_sql = "select region_id from region where region_name='%s'" % query_info["area"]
area_id = Utility.get_connect_one(area_sql, path)[0]
if flag:
base_sql = base_sql + " and region_id=%d" % area_id
else:
base_sql = base_sql + " where region_id=%d" % area_id
flag = 1
if query_info["status"] != "全部" and query_info["status"] != "":
status_sql = "select dict_key from dictionary_data where dict_type_id=1 and dict_value='%s'" % (
query_info["status"])
last_status = Utility.get_connect_one(status_sql, path)[0]
if flag:
base_sql = base_sql + " and last_status='%s'"%(last_status)
else:
base_sql = base_sql + " where last_status='%s'"%(last_status)
flag = 1
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]
if flag:
base_sql = base_sql + " and source='%s'"%(source)
else:
base_sql = base_sql + " where source='%s'"%(source)
flag = 1
if query_info["starttime"] != "" and query_info["endtime"] != "":
if flag:
base_sql = base_sql + \
" and create_time between '%s' and '%s'"%(query_info["starttime"], query_info["endtime"])
else:
base_sql = base_sql + \
" where create_time between '%s' and '%s'"%(query_info["starttime"], query_info["endtime"])
flag = 1
elif query_info["starttime"] == "" and query_info["endtime"] != "":
if flag:
base_sql = base_sql + \
" and create_time before '%s'"%(query_info["endtime"])
else:
base_sql = base_sql + \
" where create_time before '%s'"%(query_info["endtime"])
flag = 1
elif query_info["starttime"] != "" and query_info["endtime"] == "":
if flag:
base_sql = base_sql + \
" and create_time after '%s'"%(query_info["starttime"])
else:
base_sql = base_sql + \
" where create_time after '%s'"%(query_info["starttime"])
flag = 1
if query_info["name"] != "" and query_info["name"] == "一瓢":
query_info["name"] = "1A5EF59031E0FDEB"
if flag:
base_sql = base_sql + " and name='%s'" % (query_info["name"] + "%")
else:
base_sql = base_sql + " where name='%s'" % (query_info["name"] + "%")
return Utility.get_connect_one(base_sql + ";", path)[0]
def test_training_resource(self, training_resource_info, expect):
# 获取sql
def joint_sql():
time_base_sql = ' and create_time between "%s" and "%s" and type="下载"' % (
training_resource_info['date1'],
training_resource_info['date2'])
if training_resource_info['poolSelect']=="全部" and training_resource_info['empNameSelect']=="全部" and\
training_resource_info['statusSelect']=="全部" and training_resource_info['sourceSelect']=="全部" and\
training_resource_info['date1']=="" and training_resource_info['date2']!="":
sql = 'select count(customer_id) from customer where pool_type != "public"' + time_base_sql
return sql
if training_resource_info['poolSelect']=="全部" and training_resource_info['empNameSelect']=="全部" and\
training_resource_info['statusSelect']=="全部" and training_resource_info['sourceSelect']!="全部":
sql = 'select count(customer_id) from customer where source = "%s" and pool_type != "public"'\
%training_resource_info['sourceSelect']+time_base_sql
return sql
elif training_resource_info['poolSelect']!="全部" and training_resource_info['empNameSelect']=="全部" and\
training_resource_info['statusSelect']=="全部" and training_resource_info['sourceSelect']!="全部":
sql = 'select count(customer_id) from customer where source = "%s" and pool_type = "%s"' \
% (training_resource_info['sourceSelect'],training_resource_info['poolSelect'])+time_base_sql
return sql
elif training_resource_info['poolSelect']!="全部" and training_resource_info['empNameSelect']=="全部" and\
training_resource_info['statusSelect']!="全部" and training_resource_info['sourceSelect']!="全部":
sql = 'select count(customer_id) from customer where source = "%s" and pool_type = "%s" and last_status="%s"' \
% (training_resource_info['sourceSelect'],training_resource_info['poolSelect'],
training_resource_info['statusSelect'])+time_base_sql
return sql
# 获取cookie
self.training_resource_init()
# 选择加载资源模块
Service.get_ele(
self.driver, By.CSS_SELECTOR,
"#content > div.row.con-margin.con-body-header > div:nth-child(1) > ul > li.active > a"
).click()
TrainingResource.training_resource(self.driver, training_resource_info)
# 系统提示搜索条件
if Service.is_Element_present(
self.driver, By.CSS_SELECTOR,
"body > div.bootbox.modal.fade.mydialog.in > div > div > "
"div.modal-footer > button"):
actual = "query-fail"
self.assertEqual(actual, expect)
# 关闭
self.driver.quit()
else:
# 判断是否查询出数据
training_resource_query_result = Service.get_ele(
self.driver, By.CSS_SELECTOR,
"table#personal-table tbody").text
# 市场营销有上传过数据
if training_resource_query_result != "无符合条件的记录":
# 前端页面显示数据数，根据状态（不同状态数量不同）
query_count_result = Service.get_ele(
self.driver, By.CSS_SELECTOR, "span.pagination-info").text
result = re.match('^显示.*，总.*?(\d+)', query_count_result)
print(result.group(1))
sql = joint_sql()
print(sql)
if sql:
query_connect_count = Utility.get_connect_one(
sql, "../config/data_base")
print(query_connect_count)
if int(result.group(1)) == int(query_connect_count[0]):
actual = 'query-pass'
else:
actual = 'query-fail'
self.assertEqual(actual, expect)
else:
print("查询结果为空")
self.driver.close()
