def get_phase_list_with_project(project_id):
"""
根据项目ID获取对应阶段列表
:param project_id:
:return:
"""
sql = """
SELECT
project_phases.phase_id,
test_plan.plan_name
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 AND
test_plan.plan_name != '兼容性测试'
ORDER BY
test_plan.plan_id ASC
""" % project_id
result = db(sql)
if result:
logger.debug(result)
return result
else:
return False
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:
logger.debug(result)
for i in range(len(result)):
temp.append({
'testerId': result[i][0],
'name': result[i][1],
})
return temp
else:
return []
def edit_platform(platform_id, pass_rate, tag_id, start_time=None, end_time=None):
"""
编辑测试阶段细分记录
:param end_time:
:param start_time:
:param platform_id:
:param pass_rate:
:param tag_id:
:return:
"""
sql = """
UPDATE test_platform
SET"""
if start_time:
sql += " test_platform.start_time=%r," % start_time
if end_time:
sql += " test_platform.end_time=%r," % end_time
sql += """
test_platform.pass_rate=%.2f,
test_platform.tag_id=%i
WHERE
id=%i;
""" % (pass_rate, tag_id, platform_id)
status = db(sql)
if status:
return 1
else:
return 0
def get_category_list():
"""
获取BUG分类列表
:return:
"""
sql = """
SELECT
*
FROM
bug_category
"""
temp = []
result = db(sql)
if result:
logger.debug(result)
for i in range(len(result)):
temp.append({
'categoryId': result[i][0],
'categoryName': result[i][1]
})
return temp
else:
return []
def get_bug_developer_count_with_project(project_id):
"""
根据项目获取开发人员跟进BUG占比
:return:
"""
sql = """
SELECT
Count(bug.bug_id) AS count,
developer.name AS developer
FROM
developer
INNER JOIN bug ON bug.developer_id = developer.developer_id
INNER JOIN project_phases ON bug.phase_id = project_phases.phase_id
INNER JOIN project ON project_phases.project_id = project.project_id
WHERE
project.project_id = %i
GROUP BY
developer.name
""" % project_id
result = db(sql)
if result:
logger.debug(result)
return result
else:
return False
def get_bug_type_count_with_project_phase(project_id):
"""
根据项目id获取每个项目阶段BUG类型数
:param project_id:
:return:
"""
sql = """
SELECT
bug_type.type_name,
test_plan.plan_name,
Count(1)
FROM
bug
INNER JOIN bug_type ON bug.bug_type = bug_type.type_id
INNER JOIN project_phases ON bug.phase_id = project_phases.phase_id
INNER JOIN test_plan ON project_phases.plan_id = test_plan.plan_id
WHERE
project_phases.project_id = %i
GROUP BY
bug_type.type_name,
test_plan.plan_id
ORDER BY
bug_type.type_name ASC,
test_plan.plan_id ASC
""" % project_id
result = db(sql)
if result:
logger.debug(result)
return result
else:
return False
def get_bug_category_count_with_project(project_id):
"""
获取项目BUG分类
:return:
"""
sql = """
SELECT
Count( bug.bug_id ) AS count,
bug_category.category_name AS category
FROM
bug
INNER JOIN bug_category ON bug.category = bug_category.category_id
INNER JOIN project_phases ON bug.phase_id = project_phases.phase_id
INNER JOIN project ON project_phases.project_id = project.project_id
WHERE
project.project_id = %i
GROUP BY
bug_category.category_name
""" % project_id
result = db(sql)
if result:
logger.debug(result)
return result
else:
return False
def get_bug_type_count_with_project(project_id):
"""
获取每个项目对应异常类型统计
:return:
"""
sql = """
SELECT
Count( bug.bug_id ) AS count,
bug_type.type_name AS type
FROM
bug
INNER JOIN bug_type ON bug.bug_type = bug_type.type_id
INNER JOIN project_phases ON bug.phase_id = project_phases.phase_id
INNER JOIN project ON project_phases.project_id = project.project_id
WHERE
project.project_id = %i
GROUP BY
bug_type.type_name
""" % project_id
result = db(sql)
if result:
logger.debug(result)
return result
else:
return False
def get_model_data_with_project(project_id=None):
"""
获取异常分类模块数据
:return:
"""
sql = """
SELECT
bug.model
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
"""
if project_id:
sql += "WHERE project.project_id = %i" % project_id
temp = []
result = db(sql)
if result:
for value in result:
if "-" in value[0]:
list_temp = value[0].split('-')
temp += list_temp[1:]
return temp
def edit_project(project_id, planner_id, project_name, doc_url, test_time,
publish_time):
"""
编辑项目信息
:param project_id:
:param planner_id:
:param project_name:
:param doc_url:
:param test_time:
:param publish_time:
:return:
"""
sql = """
UPDATE project
SET"""
if test_time:
sql += " test_time=%r," % test_time
if publish_time:
sql += " publish_time=%r," % publish_time
sql += """
planner_id=%i,
project_name='%s',
doc_url='%s'
WHERE
project_id=%i;
""" % (planner_id, project_name, doc_url, project_id)
status = db(sql)
if status:
return 1
else:
return 0
def get_plan_with_project_and_plan(project_id, plan_id):
"""
根据项目id与计划id获取项目有关进度
:return:
"""
sql = """
SELECT
project_phases.phase_id
FROM
project_phases
WHERE
project_phases.project_id = '%i' AND
project_phases.plan_id = '%i'
""" % (project_id, plan_id)
temp = []
result = db(sql)
if result:
logger.debug(result)
for i in range(len(result)):
temp.append(result[i][0])
return temp
else:
return []
def get_bug_count_by_env():
"""
获取BUG环境分类
:return:
"""
sql = """
SELECT
bug.is_online AS env,
Count(bug.bug_id) AS count
FROM
tester
INNER JOIN bug ON bug.tester_id = tester.tester_id
GROUP BY
bug.is_online
"""
result = db(sql)
if result:
logger.debug(result)
temp = []
for item in result:
if item[0]:
temp.append(('线上异常', item[1]))
else:
temp.append(('开发异常', item[1]))
return temp
else:
return False
def get_project_info_with_phase(phase_id):
"""
根据项目阶段id获取与之对应的项目信息
:param phase_id:
:return:project_info_list
{
'projectId': int,
'projectName': string
}
"""
sql = """
SELECT
project.project_id,
project.project_name
FROM
project_phases
INNER JOIN project ON project_phases.project_id = project.project_id
WHERE
project_phases.phase_id = '%i'
""" % phase_id
result = db(sql)
if result:
logger.debug(result)
temp = {'projectId': result[0][0], 'projectName': result[0][1]}
return temp
else:
return []
def get_platform_tag_with_project_id(project_id):
"""
根据项目id获取对应tag列表
:param project_id:
:return:
"""
sql = """
SELECT
platform_tag.tag_id,
platform_tag.tag_name
FROM
platform_tag
WHERE
platform_tag.project_id=%i;
""" % project_id
temp = []
result = db(sql)
if result:
logger.debug(result)
for i in range(len(result)):
temp.append({'tagId': result[i][0], 'tagName': result[i][1]})
return temp
else:
return []
def get_plan_info_with_phase(phase_id):
"""
根据项目阶段id获取预置对应的测试计划
:param phase_id:
:return:plan_info_list
{
'planId': int,
'planName': string
}
"""
sql = """
SELECT
test_plan.plan_id,
test_plan.plan_name
FROM
project_phases
INNER JOIN test_plan ON project_phases.plan_id = test_plan.plan_id
WHERE
project_phases.phase_id = '%i'
""" % phase_id
result = db(sql)
if result:
logger.debug(result)
temp = {'planId': result[0][0], 'planName': result[0][1]}
return temp
else:
return []
def get_plan_with_project_id(project_id):
"""
根据项目id获取项目有关进度
:return:
"""
sql = """
SELECT
project_phases.phase_id,
project_phases.plan_id
FROM
project_phases
WHERE
project_phases.project_id = '%s'
""" % project_id
temp = []
result = db(sql)
if result:
logger.debug(result)
for i in range(len(result)):
temp.append({
'phaseId': result[i][0],
'planId': result[i][1],
})
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:
logger.debug(result)
for i in range(len(result)):
temp.append(result[i][0])
return temp
else:
return []
def get_tester_info_with_bug(bug_id):
"""
根据BUG ID获取与之关联的测试人员信息
:param bug_id:
:return:
"""
sql = """
SELECT
tester.tester_id,
tester.name,
tester.email
FROM
bug
INNER JOIN tester ON bug.tester_id = tester.tester_id
WHERE
bug.bug_id = "%i"
""" % bug_id
result = db(sql)
if result:
logger.debug(result)
temp = {
'testerId': result[0][0],
'testerName': result[0][1],
'testerEmail': result[0][2]
}
return temp
else:
return []
def add_bug(tester_id, developer_id, phase_id, bug_type, category, kb_id,
title, model, create_time, close_time, is_finished, is_closed,
is_online):
"""
新增Bug
:param tester_id:
:param developer_id:
:param phase_id:
:param bug_type:
:param category:
:param kb_id:
:param title:
:param model:
:param create_time:
:param close_time:
:param is_finished:
:param is_closed:
:param is_online:
:return:
"""
sql = """
INSERT INTO bug (tester_id, developer_id, phase_id, bug_type, category, kb_id, title, model,
create_time, close_time, is_finished, is_closed, is_online)
VALUES
(%i, %i, %i, %i, %i, %i, '%s', '%s', %r, %r, %s, %s, %s)
""" % (tester_id, developer_id, phase_id, bug_type, category, kb_id, title,
model, create_time, close_time, is_finished, is_closed, is_online)
# logger.debug(sql)
status = db(sql)
if status:
return 1
else:
return 0
def get_update_log_list():
"""
获取更新日志
:return:
"""
sql = """
SELECT
log.log_id,
log.content,
log.commit_time
FROM
log
ORDER BY
log.commit_time DESC
"""
temp = []
# 获取更新日志信息
result = db(sql)
if result:
logger.debug(result)
for i in range(len(result)):
temp.append({
'logId': result[i][0],
'content': result[i][1],
'commitTime': get_format_str(result[i][2]),
})
return temp
else:
return []
def get_project_list(category_id=None):
"""
返回项目列表信息
:return: {
'projectId': int,
'projectName': string,
'planner': string,
'category': string
}
"""
sql = """
SELECT
project.project_id,
project.project_name,
project.planner_id,
project_category.category_name as category,
project.doc_url,
project.test_time,
project.publish_time
FROM
project
INNER JOIN project_category ON project.category = project_category.category_id
"""
if category_id:
sql += "WHERE project.category = %i" % int(category_id)
temp = []
result = db(sql)
if result:
logger.debug(result)
for i in range(len(result)):
tester = get_tester_with_project(result[i][0])
tester_list = []
for item in tester:
tester_list.append(item['testerId'])
temp.append({
'projectId': result[i][0],
'projectName': result[i][1],
'planner': result[i][2],
'category': result[i][3],
'docUrl': result[i][4],
'testTime': result[i][5],
'publishTime': result[i][6],
'tester': tester_list
})
return temp
else:
return []
def get_phase_info_with_project(project_id):
"""
根据项目ID获取对应项目进度
:param project_id: 项目ID
:return: developer_list
{
'phaseId': int,
'name': string,
'startTime' timestamp,
'endTime' timestamp，
'count' int
}
"""
sql = """
SELECT
project_phases.phase_id,
test_plan.plan_name,
project_phases.start_time,
project_phases.end_time,
Count(bug.bug_id) as count
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
INNER JOIN bug ON bug.phase_id = project_phases.phase_id
WHERE
project.project_id = "%i"
GROUP BY
project_phases.phase_id,
test_plan.plan_name,
project_phases.start_time,
project_phases.end_time
ORDER BY
test_plan.plan_id ASC
""" % project_id
temp = []
result = db(sql)
if result:
logger.debug(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],
'count': result[i][4]
})
return temp
else:
return []
def add_test_record_with_tester_and_project(tester_id, project_id):
"""
添加测试人员与项目之间的跟进关系
:return:
"""
sql = """
INSERT INTO test(tester_id, project_id)
VALUES
('%i', '%i')
""" % (tester_id, project_id)
status = db(sql)
if status:
return 1
else:
return 0
def delete_bug_with_kb_id(kb_id):
"""
通过KB ID删除BUG
:param kb_id:
:return:
"""
sql = """
DELETE FROM bug WHERE kb_id='%i';
""" % kb_id
logger.debug(sql)
status = db(sql)
if status:
return 1
else:
return 0
def delete_bug_with_id(bug_id):
"""
通过BUG ID删除BUG
:param bug_id:
:return:
"""
sql = """
DELETE FROM bug WHERE bug_id='%i';
""" % bug_id
logger.debug(sql)
status = db(sql)
if status:
return 1
else:
return 0
def edit_bug(tester_id, developer_id, phase_id, bug_type, category, kb_id,
title, model, create_time, close_time, is_finished, is_closed,
is_online):
"""
编辑Bug
:param tester_id:
:param developer_id:
:param phase_id:
:param bug_type:
:param category:
:param kb_id:
:param title:
:param model:
:param create_time:
:param close_time:
:param is_finished:
:param is_closed:
:param is_online:
:return:
"""
sql = """
UPDATE
bug
SET
tester_id = %i,
developer_id = %i,
phase_id = %i,
bug_type = %i,
category = %i,
title = '%s',
model = '%s',
create_time = %i,
close_time = %i,
is_finished = '%s',
is_closed = '%s',
is_online = '%s'
WHERE
kb_id = %i;
""" % (tester_id, developer_id, phase_id, bug_type, category, title, model,
create_time, close_time, is_finished, is_closed, is_online, kb_id)
# logger.debug(sql)
status = db(sql)
if status:
return 1
else:
return 0
def add_phase(project_id, plan_id):
"""
新建项目阶段
:param project_id:
:param plan_id:
:return:
"""
sql = """
INSERT INTO project_phases (project_id, plan_id)
VALUES
('%i', '%i')
""" % (project_id, plan_id)
status = db(sql)
if status:
return 1
else:
return 0
def get_bug_trend_with_project(project_id):
"""
BUG趋势统计
:return:
"""
date = '%Y-%m-%d'
sql = """
SELECT
date,
COUNT( COUNT )
FROM
(
SELECT
FROM_UNIXTIME( bug.create_time, '%s' ) AS date,
Count( bug.bug_id ) AS count
FROM
project
INNER JOIN project_phases ON project.project_id = project_phases.project_id
INNER JOIN bug ON project_phases.phase_id = bug.phase_id
WHERE
project.project_id = %i
GROUP BY
bug.create_time
ORDER BY
bug.create_time ASC
) AS data
GROUP BY
date
ORDER BY
date ASC
""" % (date, project_id)
result = db(sql)
if result:
logger.debug(result)
temp = {
'startDate': result[0][0],
'endDate': result[-1][0],
'data': {}
}
for item in result:
temp['data'][item[0]] = item[1]
return temp
else:
return False
def get_project_insert_id():
"""
获取新增后id
:return:
"""
sql = """
SELECT project_id
FROM project
ORDER BY project_id
DESC LIMIT 1
"""
result = db(sql)
if result:
return result[0][0]
else:
return False
def add_platform_tag(project_id, tag_name):
"""
新增细分标签
:param project_id:
:param tag_name:
:return:
"""
sql = """
INSERT INTO `platform_tag` ( platform_tag.project_id, platform_tag.tag_name )
VALUES
( %i, '%s');
""" % (project_id, tag_name)
status = db(sql)
if status:
return 1
else:
return 0