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
Exemplo n.º 2
0
 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()
Exemplo n.º 6
0
#
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={
Exemplo n.º 7
0
 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
Exemplo n.º 11
0
 def __init__(self):
     self.zentaodb = MyDbHelper("zentao").conn
     self.day = CalendarUtils.delta_day()
     self.ubug = self.get_user_buglist()