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 __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_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
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()