def __init__(self, from_date, to_date, config=default_config):
self.from_date = from_date
self.to_date = to_date
self.config = config
self.summary = []
self.template_file_name = 'report.html'
self.report_title = '{}至{}禅道报告'.format(self.from_date, self.to_date)
self.conn = MyDbHelper("zentao").conn
def get_basedata(self):
zentaodb = MyDbHelper("zentao").conn
task_sql = "select zt_taskestimate.date,zt_taskestimate.`left`,zt_taskestimate.consumed,zt_taskestimate.work,zt_task.name as taskname,zt_user.realname,zt_dept.name as department,zt_project.name as projectname from zt_taskestimate left JOIN zt_task on zt_taskestimate.task=zt_task.id left join zt_user on zt_taskestimate.account=zt_user.account left join zt_dept on zt_user.dept =zt_dept.id left join zt_project on zt_task.project=zt_project.id " \
"where date >='%s' and date <='%s' and zt_taskestimate.consumed !=0 " %(self.month_se[0],self.month_se[1])
taskinfo = pd.read_sql_query(task_sql, zentaodb)
#处理记录的html
# taskinfo['work']=taskinfo['work'].str.replace(r'(<audio .*>)', r'', regex=True)
cols = [
'realname',
'projectname',
'taskname',
'work',
'left',
'consumed',
'date',
'department',
]
taskinfo = taskinfo[cols]
taskinfo.rename(columns={
'projectname': '项目',
'department': '部门',
'taskname': '任务名',
'realname': '姓名',
'work': '汇报日志',
'left': '剩余工时',
'consumed': '已消耗',
'date': '日期',
},
inplace=True)
return taskinfo
def get_project_bug_resolve(self,mid):
"""
返回某个项目当天解决的bug数量
:param mid:
:return:
"""
name_sql = "select name FROM zt_project WHERE id = {}".format(mid)
test_sql = "SELECT project, COUNT(id) from zt_bug WHERE project = {} AND DATE_FORMAT(resolvedDate,'%Y%m%d')= DATE_FORMAT(CURRENT_DATE,'%Y%m%d') AND deleted = '0' AND status = 'resolved' or status = 'closed' GROUP BY project;;".format(
mid)
mydb = MyDbHelper("zentao")
project = mydb.get_one(name_sql)
result = mydb.get_all(test_sql)
result[0].update({"项目名称": result[0].pop("project")})
result[0].update({"当天解决bug数": result[0].pop("COUNT(id)")})
result[0]['项目名称'] = project['name']
a = dingRobot('test')
title = ("当天解决的bug数汇总:\n")
content = str(result[0])
a.markdown(title,content)
def get_test_bugs(self, mid):
'''
返回某个项目每天新增的bug数
:param 版本id, 项目id:
:return: ([{'project': 'mid', 'COUNT(id)': 新增bug数, 'openedBy': '提出者'}]
'''
name_sql = "select name FROM zt_project WHERE id = {}".format(mid)
test_sql = ("SELECT d.name 项目名称,COUNT( a.id ) 新增bug数,a.type bug类型,b.realname bug提出者,c.name bug类型 FROM zt_bug a "
"join zt_user b on a.openedBy = b.account join zt_module c ON a.module = c.id join zt_project d ON a.project = d.id "
"WHERE a.project = {} AND DATE_FORMAT( a.openedDate, '%Y-%m-%d' )= CURRENT_DATE AND a.deleted = '0' GROUP BY a.openedBy;").format(mid)
mydb = MyDbHelper("zentao")
result = mydb.get_all(test_sql)
test_list = []
for item in result:
if item['bug类型'] == "front":
item['bug类型'] = "前端代码问题"
elif item['bug类型'] == "codeerror":
item['bug类型'] = "后端代码问题"
elif item['bug类型'] == "history":
item['bug类型'] = "历史遗留"
elif item['bug类型'] == "install":
item['bug类型'] = "安装部署"
elif item['bug类型'] == "others":
item['bug类型'] = "其他"
elif item['bug类型'] == "designdefect":
item['bug类型'] = "需求文档"
elif item['bug类型'] == "security":
item['bug类型'] = "安全问题"
elif item['bug类型'] == "user":
item['bug类型'] = "运营反馈"
elif item['bug类型'] == "automation":
item['bug类型'] = "测试脚本"
test_list.append(item)
a = dingRobot('test')
title = ("当天新增的bug数量:\n")
content = str(test_list)
a.markdown(title, content)
def __init__(self):
self.conn = MyDbHelper("zentao").conn
self.deptlist = self.dept_man_list()
#
from CommonLib.SQL.sqlhelper import MyDbHelper
from CommonLib.selfApi.Carlendar import CalendarUtils
import pandas as pd
sd = CalendarUtils.delta_week()[0]
ed = CalendarUtils.delta_week()[1]
yd = CalendarUtils.delta_day(-1)
delay_sql = "SELECT ROUND((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(deadline))/60/60/24) delay ,z1.id,z1.name as taskname,z4.name as projectname,deadline, z2.realname as openedBy,z3.realname as assignedTo FROM zt_task z1 JOIN zt_user z2 on z1.openedBy = z2.account JOIN zt_user z3 ON z1.assignedTo = z3.account JOIN zt_project z4 on z1.project =z4.id WHERE DATE_FORMAT(deadline,'%Y%m%d') < DATE_FORMAT(CURRENT_DATE,'%Y%m%d') AND z1.status = 'doing' AND z1.left!=0 AND z1.parent !='-1' AND z1.deleted='0' "
task7days_sql = "select zt.id,z4.name as deptname,zu.realname as cname,z3.realname as dname from zt_task zt left join zt_user zu on zt.openedBy=zu.account left join zt_user z3 on zt.assignedTo=z3.account left join zt_dept z4 on zu.dept=z4.id where DATEDIFF (deadline , estStarted) > 7 and zt.deleted='0' and zt.parent !='-1' and zt.status='doing' "
noreport_sql = "select realname,z2.name from zt_user z1 left join zt_dept z2 on z1.dept=z2.id where deleted='0' and dept !='0' and dept <= 10 and mobile='' EXCEPT SELECT zu.realname,z3.name FROM zt_taskestimate zt left join zt_user zu on zt.account=zu.account left join zt_dept z3 on zu.dept=z3.id WHERE date='%s'" % yd
notask_sql = "select zu.realname,zd.name as deptname from zt_user zu left join zt_dept zd on zu.dept=zd.id where zu.dept!=0 and zu.dept<=10 and zu.deleted='0' EXCEPT (select distinct z2.realname,z3.name as deptname from zt_task z1 left join zt_user z2 on z1.assignedTo=z2.account left join zt_dept z3 on z2.dept=z3.id where z1.deleted='0' and z1.assignedDate >= '%s' and z1.status in ('wait','doing') union select distinct z2.realname,z3.name as deptname from zt_task z1 left join zt_user z2 on z1.finishedBy=z2.account left join zt_dept z3 on z2.dept=z3.id where z1.deleted='0' and finishedDate BETWEEN '%s' and '%s' union select distinct z2.realname,z3.name as deptname from zt_taskestimate z1 left join zt_user z2 on z1.account=z2.account left join zt_dept z3 on z2.dept=z3.id where z1.date='%s')" % (
sd, sd, ed, yd)
zentaodb = MyDbHelper("zentao").conn
delay_data = pd.read_sql_query(delay_sql, zentaodb)
delay_data.rename(columns={
'delay': '延期天数',
'id': '任务ID',
'openedBy': '创建人',
'assignedTo': '执行人',
'taskname': '任务',
'projectname': '项目'
},
inplace=True)
cols = ['任务ID', '项目', '任务', '创建人', '执行人', 'deadline', '延期天数']
delay_data = delay_data[cols]
task7days_data = pd.read_sql_query(task7days_sql, zentaodb)
task7days_data.rename(columns={
def __init__(self):
self.zentaodb = MyDbHelper("zentao").conn
class Reporter:
"""报告生成类"""
def __init__(self, from_date, to_date, config=default_config):
self.from_date = from_date
self.to_date = to_date
self.config = config
self.summary = []
self.template_file_name = 'report.html'
self.report_title = '{}至{}禅道报告'.format(self.from_date, self.to_date)
self.conn = MyDbHelper("zentao").conn
def get_user_stat(self, user, from_date, to_date):
"""
返回一个用户的报告
:param user: 禅道account
:param from_date: 起始日期
:param to_date: 终止日期
:return: {
'account': 用户account,
'realname': 用户真名,
'bug': {
'open': 创建bug情况,
'close': 关闭bug情况,
'active': 激活bug情况,
'resolve': 解决bug情况,
'current': 当前被指派bug情况
},
'task': {
'do': 完成任务工时情况,
'current': 当前被指派任务情况
}
}
"""
print('正在获取%s报告……' % user)
return {
'account': user,
'realname': self._get_user_realname(user),
'from_date': from_date,
'to_date': to_date,
'bug': {
'open': self._query_user_open_bug(user, from_date, to_date),
'close': self._query_user_close_bug(user, from_date, to_date),
'active': self._query_user_active_bug(user, from_date,
to_date),
'resolve':
self._query_user_resolve_bug(user, from_date, to_date),
'current': self._query_user_current_bug(user)
},
'task': {
'do': self._query_user_do_task(user, from_date, to_date),
'current': self._query_user_current_task(user),
'short_period':
self._query_user_short_period_task(user, to_date)
}
}
def _get_user_realname(self, user):
"""
查询account真名
:param user:
:return: realname
"""
cursor = self.conn.cursor()
cursor.execute("SELECT `realname` FROM zt_user WHERE account=%s",
(user, ))
entry = cursor.fetchone()
return entry
def _query_user_open_bug(self, user, from_date, to_date):
"""
查询用户创建的bug
:param user: 禅道account
:param from_date: 起始日期
:param to_date: 终止日期
:return: {
'detail': [查询出的列表，包括日期，严重程度，bug总数，bug详情],
'summary': {'致命':1, '严重':3, '一般': 5, '提示': '6'},
'total': 总数
}
"""
stat = {}
query_detail = "SELECT `day`, `severity`, `bugopen`, `bugs` FROM `ztv_userdayopenbug` WHERE `account` = %s AND `day` BETWEEN %s AND %s"
stat['detail'] = self._query(query_detail, (user, from_date, to_date))
query_summary = "SELECT `severity`, SUM(`bugopen`) FROM `ztv_userdayopenbug` WHERE `account` = %s AND `day` BETWEEN %s AND %s GROUP BY severity"
summary = self._query(query_summary, (user, from_date, to_date))
print(summary)
stat['summary'] = {i[0]: i[1] for i in summary}
stat['total'] = sum([i[1] for i in summary])
print("openbug" + str(stat))
return stat
def _query_user_close_bug(self, user, from_date, to_date):
"""
查询用户关闭的bug
:param user: 禅道account
:param from_date: 起始日期
:param to_date: 终止日期
:return: {
'detail': [查询出的列表，包括日期，严重程度，bug总数，bug详情],
'summary': {'致命':1, '严重':3, '一般': 5, '提示': '6'},
'total': 总数
}
"""
stat = {}
query_detail = "SELECT `day`, `severity`, `bugclose`, `bugs` FROM `ztv_userdayclosebug` WHERE `account` = %s AND `day` BETWEEN %s AND %s"
stat['detail'] = self._query(query_detail, (user, from_date, to_date))
query_summary = "SELECT `severity`, SUM(`bugclose`) FROM `ztv_userdayclosebug` WHERE `account` = %s AND `day` BETWEEN %s AND %s GROUP BY severity"
summary = self._query(query_summary, (user, from_date, to_date))
stat['summary'] = {i[0]: i[1] for i in summary}
stat['total'] = sum([i[1] for i in summary])
return stat
def _query_user_active_bug(self, user, from_date, to_date):
"""
查询用户激活的bug
:param user: 禅道account
:param from_date: 起始日期
:param to_date: 终止日期
:return: {
'detail': [查询出的列表，包括日期，严重程度，bug总数，bug详情],
'summary': {'致命':1, '严重':3, '一般': 5, '提示': '6'},
'total': 总数
}
"""
stat = {}
query_detail = "SELECT `day`, `severity`, `bugactive`, `bugs` FROM `ztv_userdayactivebug` WHERE `account` = %s AND `day` BETWEEN %s AND %s"
stat['detail'] = self._query(query_detail, (user, from_date, to_date))
query_summary = "SELECT `severity`, SUM(`bugactive`) FROM `ztv_userdayactivebug` WHERE `account` = %s AND `day` BETWEEN %s AND %s GROUP BY severity"
summary = self._query(query_summary, (user, from_date, to_date))
stat['summary'] = {i[0]: i[1] for i in summary}
stat['total'] = sum([i[1] for i in summary])
return stat
def _query_user_resolve_bug(self, user, from_date, to_date):
"""
查询用户解决的bug
:param user: 禅道account
:param from_date: 起始日期
:param to_date: 终止日期
:return: {
'detail': [查询出的列表，包括日期，严重程度，bug总数，bug详情],
'summary': {'致命':1, '严重':3, '一般': 5, '提示': '6'},
'total': 总数
}
"""
stat = {}
query_detail = "SELECT `day`, `severity`, `bugresolve`, `bugs` FROM `ztv_userdayresolvebug` WHERE `account` = %s AND `day` BETWEEN %s AND %s"
stat['detail'] = self._query(query_detail, (user, from_date, to_date))
query_summary = "SELECT `severity`, SUM(`bugresolve`) FROM `ztv_userdayresolvebug` WHERE `account` = %s AND `day` BETWEEN %s AND %s GROUP BY severity"
summary = self._query(query_summary, (user, from_date, to_date))
try:
stat['summary'] = {i[0]: i[1] for i in summary}
stat['total'] = sum([i[1] for i in summary])
except:
pass
return stat
def _query_user_current_bug(self, user):
"""
查询用户当前被指派的bug
:param user: 禅道account
:return: {
'detail': [查询出的列表，包括严重程度，bug总数，bug详情],
'summary': {'致命':1, '严重':3, '一般': 5, '提示': '6'},
'total': 总数
}
"""
stat = {}
query_detail = "SELECT `severity`, `bugassign`, `bugs` FROM `ztv_usercurrentbug` WHERE `account` = %s"
detail = self._query(query_detail, (user, ))
stat['detail'] = detail
try:
stat['summary'] = {i[0]: i[1] for i in detail}
stat['total'] = sum([i[1] for i in detail])
return stat
except:
pass
return stat
def _query_user_do_task(self, user, from_date, to_date):
"""
查询用户完成和进行工时的task
:param user: 禅道account
:param from_date: 起始日期
:param to_date: 终止日期
:return: {
'detail': [查询出的列表，包括日期，taskid，taskname，消耗工时],
'total_consumed': 总消耗工时
}
"""
stat = {}
query_detail = "SELECT `day`, `taskid`, `taskname`, `consumed` FROM `ztv_userdaydotask` WHERE `account` = %s AND `day` BETWEEN %s AND %s"
stat['detail'] = self._query(query_detail, (user, from_date, to_date))
stat['total_consumed'] = sum([i[3] for i in stat['detail']])
return stat
def _query_user_current_task(self, user):
"""
查询用户当前被指派的task
:param user: 禅道account
:return: {
'detail': [查询出的列表，包括状态，指派task总数，task详细],
'summary': {'doing':1, 'done':3, 'wait': 5},
'total': 总数
}
"""
stat = {}
query_detail = "SELECT `status`, `taskassign`, `tasks` FROM `ztv_usercurrenttask` WHERE `account` = %s"
detail = self._query(query_detail, (user, ))
stat['detail'] = detail
try:
stat['summary'] = {i[0]: i[1] for i in detail}
stat['total'] = sum([i[1] for i in detail])
except:
pass
return stat
def _query_user_short_period_task(self, user, to_date):
"""
查询用户未来短期task情况
:param user: 禅道account
:return: {
'detail': 查询出的列表，包括deadline，taskid，taskname, taskstatus, estimate, consumed, left
'summary': {'estimate':预计消耗总工时, 'consumed':已经消耗总工时, 'left':剩余总工时 },
}
"""
stat = {}
deadline = datetime.strptime(
to_date, '%Y-%m-%d') + timedelta(days=self.config.SHORT_PERIOD_DAY)
deadline_str = deadline.strftime('%Y-%m-%d')
query_detail = "select `zt_task`.`deadline` AS `deadline`,`zt_task`.`id` AS `id`,`zt_task`.`name` AS `name`,`zt_task`.`status` AS `status`,`zt_task`.`estimate` AS `estimate`,`zt_task`.`consumed` AS `consumed`,`zt_task`.`left` AS `left` from `zt_task` where ((`zt_task`.`assignedTo` = %s) AND (`zt_task`.`parent` <> -1) AND (`zt_task`.`deadline` <= %s) and (`zt_task`.`status` not in ('closed','cancel')));"
detail = self._query(query_detail, (user, deadline_str))
stat['detail'] = detail
query_summary = "select ROUND(sum(`zt_task`.`estimate`), 2) AS `estimate`,ROUND(sum(`zt_task`.`consumed`),2) AS `consumed`,ROUND(sum(`zt_task`.`left`),2) AS `left` from `zt_task` where ((`zt_task`.`assignedTo` = %s) AND (`zt_task`.`parent` <> -1) AND (`zt_task`.`deadline` <= %s) and (`zt_task`.`status` not in ('closed','cancel')))"
summary = self._query(query_summary, (user, deadline_str))
if len(summary) > 0:
stat['summary'] = {
'estimate': summary[0][0],
'consumed': summary[0][1],
'left': summary[0][2]
}
else:
stat['summary'] = {'estimate': 0, 'consumed': 0, 'left': 0}
stat['period'] = self.config.SHORT_PERIOD_DAY
return stat
def _query(self, query, params):
cursor = self.conn.cursor()
cursor.execute(query, params)
entries = cursor.fetchall()
print('执行SQL:%s' % cursor._executed)
return entries
def gen_summary(self):
print('正在获取报告……')
for user in self.config.ZENTAO_USERS:
self.summary.append(
self.get_user_stat(user, self.from_date, self.to_date))
return self.summary
def gen_html_report(self):
print('正在生成报告……')
template_path = os.path.join(self.config.TEMPLATES_PATH,
self.template_file_name)
if not os.path.isdir(self.config.REPORTS_PATH):
os.makedirs(self.config.REPORTS_PATH)
report_path = os.path.join(self.config.REPORTS_PATH,
self.report_title + '.html')
with open(template_path, "r", encoding='utf-8') as fp_r:
template_content = fp_r.read()
with open(report_path, 'w', encoding='utf-8') as fp_w:
rendered_content = Template(
template_content,
# extensions=["jinja2.ext.loopcontrols"]
).render({
'title': self.report_title,
'summary': self.summary
})
fp_w.write(rendered_content)
print('报告生成成功，保存位置：%s' % report_path)
def send_email(self):
pass
def __init__(self):
self.zentaodb = MyDbHelper("zentao").conn
self.day=CalendarUtils.delta_day(-1)
def undone_list(self):
udsql="select id,name from zt_project where status in ('doing','wait') and deleted='0'"
res=MyDbHelper("zentao").query_sql(udsql)
return res
def __init__(self):
self.zentaodb = MyDbHelper("zentao").conn
self.day = CalendarUtils.delta_day()
self.ubug = self.get_user_buglist()