def add_tester():
"""
新增测试人员
:return:{
‘msg’: string,
'status': int
}
"""
# 接受入参
tester_name = request.json.get('testerName')
tester_email = request.json.get('testerEmail')
logging.log(1, tester_email)
print(tester_name)
print(tester_email)
if tester_name and tester_email:
sql = 'SELECT * FROM tester WHERE tester.email = "%s";' % tester_email
if db(sql):
res = {'msg': '该测试人员已存在', 'status': 2001}
else:
insert_sql = 'INSERT INTO "tester" ("name", "email") VALUES ("%s", "%s");' \
% (tester_name, tester_email)
status = db(insert_sql)
if status:
res = {'msg': '成功', 'status': 1}
else:
res = {'msg': '系统错误', 'status': 500}
else:
res = {'msg': '参数错误', 'status': 4001}
return json.dumps(res, ensure_ascii=False)
def add_project():
"""
新建项目
:return:
"""
# 接收入参
planner_id = request.json.get('plannerId')
print(planner_id)
print(type(planner_id))
project_name = request.json.get('projectName')
if project_name and planner_id:
sql = 'SELECT * FROM project WHERE project_name = "%s";' % project_name
if db(sql):
res = {'msg': '项目已存在', 'status': 2001}
else:
insert_sql = 'INSERT INTO project ("planner_id", "project_name") VALUES (%i, "%s")' \
% (planner_id, project_name)
status = db(insert_sql)
if status:
res = {'msg': '成功', 'status': 1}
else:
res = {'msg': '系统错误', 'status': 500}
else:
res = {'msg': '参数错误', 'status': 4001}
return json.dumps(res, ensure_ascii=False)
def edit_project():
"""
编辑项目
:return:
"""
# 接收入参
project_id = request.json.get('projectId')
planner_id = request.json.get('plannerId')
project_name = request.json.get('projectName')
if project_id and planner_id and project_name:
sql = 'SELECT * FROM project WHERE project_id = "%i";' % project_id
if db(sql):
update_sql = 'UPDATE project SET planner_id="%i", project_name="%s" WHERE project_id="%i"'\
% (planner_id, project_name, project_id)
status = db(update_sql)
if status:
res = {'msg': '成功', 'status': 1}
else:
res = {'msg': '系统错误', 'status': 500}
else:
res = {'msg': '项目不存在', 'status': 2001}
else:
res = {'msg': '参数错误', 'status': 4001}
return json.dumps(res, ensure_ascii=False)
def add_developer():
"""
新增开发人员
:return:
"""
# 接受入参
developer_name = request.json.get('developerName')
developer_email = request.json.get('developerEmail')
develop_type = request.json.get('developType')
if developer_name and developer_email:
sql = 'SELECT * FROM developer WHERE developer.email = "%s";' % developer_email
if db(sql):
res = {'msg': '该开发人员已存在', 'status': 2001}
else:
if select_develop_type_exist(develop_type):
insert_sql = 'INSERT INTO "developer" ("type_id", "name", "email") VALUES ("%i", "%s", "%s");' \
% (develop_type, developer_name, developer_email)
status = db(insert_sql)
if status:
res = {'msg': '成功', 'status': 1}
else:
res = {'msg': '系统错误', 'status': 500}
else:
res = {'msg': '开发类型不存在', 'status': 4001}
else:
res = {'msg': '参数错误', 'status': 4001}
return json.dumps(res, ensure_ascii=False)
def delete_tester():
"""
删除测试人员
:return:{
‘msg’: string,
'status': int
}
"""
tester_id = request.json.get('testerId')
if tester_id:
sql = 'SELECT * FROM tester WHERE tester_id = "%i";' % tester_id
if db(sql):
delete_sql = 'DELETE FROM tester WHERE tester_id="%i";' % tester_id
status = db(delete_sql)
if status:
res = {'msg': '成功', 'status': 1}
else:
res = {'msg': '系统错误', 'status': 500}
else:
res = {'msg': '项目不存在', 'status': 2001}
else:
res = {'msg': '参数错误', 'status': 4001}
return json.dumps(res, ensure_ascii=False)
def get_tester_base_info(tester_id):
"""
根据测试人员id获取测试人员基础信息
:param tester_id: 测试人员ID
:return: tester_info_list
{
'testerId': int,
'testerName': string,
'testerEmail':string
}
"""
sql = """
SELECT
tester.tester_id,
tester.name
FROM
tester
WHERE
tester.tester_id = "%i"
""" % tester_id
# 获取测试基础信息
result = db(sql)
if result:
print(result)
temp = {
'testerId': result[0][0],
'testerName': result[0][1],
'testerEmail': result[0][2]
}
return temp
else:
return []
def get_bug_list_with_project(project_id):
"""
根据项目id获取项目对应bug
:param project_id:
:return:bug_id_list
[int]
"""
sql = """
SELECT
bug.bug_id
FROM
project_phases
INNER JOIN project ON project_phases.project_id = project.project_id
INNER JOIN bug ON bug.phase_id = project_phases.phase_id
WHERE
project.project_id = "%i"
""" % project_id
temp = []
result = db(sql)
if result:
print(result)
for i in range(len(result)):
temp.append(result[i][0])
return temp
else:
return []
def get_project_list():
"""
返回项目列表信息
:return: {
'projectId': int,
'projectName': string,
'planner': string,
}
"""
sql = """
SELECT
project.project_id,
project.project_name,
planner.name
FROM
project
INNER JOIN planner ON project.planner_id = planner.planner_id
"""
temp = []
result = db(sql)
if result:
print(result)
for i in range(len(result)):
temp.append(
{
'projectId': result[i][0],
'projectName': result[i][0],
'planner': result[i][0]
}
)
return temp
else:
return []
def get_bug_with_tester_and_project(tester_id, project_id):
"""
根据项目人员负责的项目获取项目中该测试人员负责的Bug
:return: bug_id_list
[int]
"""
sql = """
SELECT
bug.bug_id
FROM
bug
INNER JOIN project_phases ON bug.phase_id = project_phases.phase_id
INNER JOIN project ON project_phases.project_id = project.project_id
INNER JOIN tester ON bug.tester_id = tester.tester_id
WHERE
tester.tester_id = "%i" AND
project.project_id = "%i"
""" % (tester_id, project_id)
temp = []
result = db(sql)
if result:
print(result)
for i in range(len(result)):
temp.append(result[i][0])
return temp
else:
return []
def get_project_base_info(project_id):
"""
根据项目ID获取项目基础信息
:param project_id: 项目ID
:return:
{
'projectId': int,
'projectName': string,
'planner': string,
}
"""
sql = """
SELECT
project.project_id,
project.project_name,
planner.name
FROM
project
INNER JOIN planner ON project.planner_id = planner.planner_id
WHERE
project.project_id = "%i";
""" % project_id
# 获取项目基础信息
result = db(sql)
if result:
print(result)
temp = {
'projectId': result[0][0],
'projectName': result[0][1],
'planner': result[0][2],
}
return temp
else:
return []
def get_tester_list():
"""
获取测试人员列表
:return:
[
{
'testerId': int,
'testerName': string,
'testerEmail': string
}
]
"""
sql = """
SELECT
tester.tester_id,
tester.name,
tester.email
FROM
tester
"""
temp = []
result = db(sql)
if result:
print(result)
for i in range(len(result)):
temp.append({
'testerId': result[i][0],
'testerName': result[i][1],
'testerEmail': result[i][2]
})
return temp
else:
return []
def delete_developer():
"""
删除开发人员
:return:
"""
developer_id = request.json.get('developerId')
if developer_id:
sql = 'SELECT * FROM developer WHERE developer_id = "%i";' % developer_id
if db(sql):
delete_sql = 'DELETE FROM developer WHERE developer_id="%i";' % developer_id
status = db(delete_sql)
if status:
res = {'msg': '成功', 'status': 1}
else:
res = {'msg': '系统错误', 'status': 500}
else:
res = {'msg': '人员不存在', 'status': 2001}
else:
res = {'msg': '参数错误', 'status': 4001}
return json.dumps(res, ensure_ascii=False)
def delete_project():
"""
删除项目
:return:
"""
# 接收入参
project_id = request.json.get('projectId')
if project_id:
sql = 'SELECT * FROM project WHERE project_id = "%i";' % project_id
if db(sql):
delete_sql = 'DELETE FROM project WHERE project_id="%i";' % project_id
status = db(delete_sql)
if status:
res = {'msg': '成功', 'status': 1}
else:
res = {'msg': '系统错误', 'status': 500}
else:
res = {'msg': '项目不存在', 'status': 2001}
else:
res = {'msg': '参数错误', 'status': 4001}
return json.dumps(res, ensure_ascii=False)
def edit_tester():
"""
编辑测试人员
:return:{
‘msg’: string,
'status': int
}
"""
# 接受入参
tester_id = request.json.get('testerId')
tester_name = request.json.get('testerName')
tester_email = request.json.get('testerEmail')
print(tester_id)
print(tester_name)
print(tester_email)
if tester_name and tester_email and tester_id:
sql = 'SELECT * FROM tester WHERE tester.tester_id = "%i";' % tester_id
if db(sql):
sql = 'SELECT * FROM tester WHERE tester.email = "%s";' % tester_email
if db(sql):
update_sql = 'UPDATE tester SET name="%s", email="%s" WHERE tester_id="%i"' \
% (tester_name, tester_email, tester_id)
status = db(update_sql)
if status:
res = {'msg': '成功', 'status': 1}
else:
res = {'msg': '系统错误', 'status': 500}
else:
res = {'msg': '邮箱不可重复', 'status': 2001}
else:
res = {'msg': '该测试人员不存在', 'status': 2001}
else:
res = {'msg': '参数错误', 'status': 4001}
return json.dumps(res, ensure_ascii=False)
def edit_developer():
"""
编辑开发人员
:return:{
‘msg’: string,
'status': int
}
"""
# 接受入参
developer_id = request.json.get('developerId')
developer_name = request.json.get('developerName')
developer_email = request.json.get('developerEmail')
develop_type = request.json.get('developType')
print(developer_id)
print(developer_name)
print(developer_email)
if developer_name and developer_email and developer_id:
sql = 'SELECT * FROM developer WHERE developer.developer_id = "%s";' % developer_id
if db(sql):
res = {'msg': '该开发人员已存在', 'status': 2001}
else:
if select_develop_type_exist(develop_type):
update_sql = 'UPDATE developer SET type_id = "%i", name="%s", email="%s" WHERE developer_id="%i"' \
% (develop_type, developer_name, developer_email, developer_id)
status = db(update_sql)
if status:
res = {'msg': '成功', 'status': 1}
else:
res = {'msg': '系统错误', 'status': 500}
else:
res = {'msg': '开发类型不存在', 'status': 4001}
else:
res = {'msg': '参数错误', 'status': 4001}
return json.dumps(res, ensure_ascii=False)
def get_phase_info_with_project(project_id):
"""
根据项目ID获取对应项目进度
:param project_id: 项目ID
:return: developer_list
{
'phaseId': int,
'name': string,
'startTime' timestamp,
'endTime' timestamp
}
"""
sql = """
SELECT
project_phases.phase_id,
test_plan.plan_name,
project_phases.start_time,
project_phases.end_time
FROM
project
INNER JOIN project_phases ON project_phases.project_id = project.project_id
INNER JOIN test_plan ON project_phases.plan_id = test_plan.plan_id
WHERE
project.project_id = "%i";
""" % project_id
temp = []
result = db(sql)
if result:
print(result)
for i in range(len(result)):
temp.append(
{
'phaseId': result[i][0],
'name': result[i][1],
'startTime': result[i][2],
'endTime': result[i][3]
}
)
return temp
else:
return []
def select_develop_type_exist(type_id):
"""
查询开发类型是否存在
:return: boolean
"""
sql = """
SELECT
*
FROM
develop_type
WHERE
develop_type.type_id = "%i"
""" % type_id
result = db(sql)
print(result)
if result:
return True
else:
return False
def get_developer_with_project(project_id):
"""
根据项目ID获取与之关联的开发人员列表
:param project_id: 项目ID
:return: developer_list
{
'developerId': int,
'name': string,
'developType': string
}
"""
sql = """
SELECT
developer.developer_id,
developer.name,
develop_type.type_name
FROM
developer
INNER JOIN develop_type ON developer.type_id = develop_type.type_id
INNER JOIN develop ON develop.developer_id = developer.developer_id
WHERE
develop.project_id = "%i";
""" % project_id
temp = []
result = db(sql)
if result:
print(result)
for i in range(len(result)):
temp.append(
{
'developerId': result[i][0],
'name': result[i][1],
'developType': result[i][2]
}
)
return temp
else:
return []
def get_develop_type_with_type_id(type_id):
"""
根据开发类型id获取对应类型名称
:param type_id:
:return: string
"""
sql = """
SELECT
develop_type.type_name
FROM
develop_type
WHERE
develop_type.type_id = "%i"
""" % type_id
result = db(sql)
print(result)
if result:
return result[0][0]
else:
return []
def get_project_list_with_developer(developer_id):
"""
根据开发人员获取其负责的项目列表
:return: project_info_list
[
{
'projectId': int,
'projectName': string
}
]
"""
sql = """
SELECT
project.project_id,
project.project_name
FROM
project
INNER JOIN develop ON develop.project_id = project.project_id
INNER JOIN developer ON develop.developer_id = developer.developer_id
WHERE
developer.developer_id = "%s"
""" % developer_id
temp = []
# 获取项目id列表
result = db(sql)
if result:
print(result)
for i in range(len(result)):
temp.append({
'projectId': result[i][0],
'projectName': result[i][1]
})
return temp
else:
return []
def get_project_list_with_tester(tester_id):
"""
根据测试人员获取其负责的项目列表
:return: project_info_list
[
{
'projectId': int,
'projectName': string
}
]
"""
sql = """
SELECT
project.project_id,
project.project_name
FROM
tester
INNER JOIN test ON test.tester_id = tester.tester_id
INNER JOIN project ON test.project_id = project.project_id
WHERE
test.tester_id = "%i"
""" % tester_id
temp = []
# 获取项目id列表
result = db(sql)
if result:
print(result)
for i in range(len(result)):
temp.append({
'projectId': result[i][0],
'projectName': result[i][1]
})
return temp
else:
return []
def get_developer_list():
"""
获取开发人员列表
:return:
[
{
'developerId': int,
'typeId': int,
'developerName': string,
'developerEmail': string
}
]
"""
sql = """
SELECT
developer.developer_id,
developer.type_id,
developer.name,
developer.email
FROM
developer
"""
temp = []
result = db(sql)
if result:
print(result)
for i in range(len(result)):
temp.append({
'developerId': result[i][0],
'typeId': result[i][1],
'developerName': result[i][2],
'developerEmail': result[i][3]
})
return temp
else:
return []
def get_tester_with_project(project_id):
"""
根据项目ID获取与之关联的测试人员列表
:param project_id: 项目ID
:return: developer_list
{
'testerId': int,
'name': string
}
"""
sql = """
SELECT
tester.tester_id,
tester.name
FROM
test
INNER JOIN tester ON test.tester_id = tester.tester_id
WHERE
test.project_id = "%i";
""" % project_id
temp = []
result = db(sql)
if result:
print(result)
for i in range(len(result)):
temp.append(
{
'testerId': result[i][0],
'name': result[i][1],
}
)
return temp
else:
return []
def get_developer_base_info(developer_id):
"""
根据测试人员id获取测试人员基础信息
:param developer_id: 测试人员ID
:return: developer_info_list
{
'developerId': int,
'typeId': int,
'developerName': string,
'developerEmail': string
}
"""
sql = """
SELECT
developer.developer_id,
developer.type_id,
developer.name,
developer.email
FROM
developer
WHERE
developer.developer_id = "%i"
""" % developer_id
# 获取测试基础信息
result = db(sql)
if result:
print(result)
temp = {
'developerId': result[0][0],
'typeId': result[0][1],
'developerName': result[0][2],
'developerEmail': result[0][3]
}
return temp
else:
return []