def getOprWorkTime(cur):
"""
生成个人工时执行情况
:param cur: 数据源
:param mongodb：issue数据源
:return: 内容
"""
global TotalMember, orgWT, doc
"""考勤情况"""
_print("考勤情况：", title=True, title_lvl=2)
_print(u'数据来源于“钉钉”考勤系统。')
data1 = getChkOnAm(cur)
data2 = getChkOnPm(cur)
if len(data1)>0 and len(data2)>0:
_f1, _f2, _f3 = doHour.doChkOnHour(data1, data2)
doc.addPic(_f3)
doc.addText(u"图1 考勤分布总体情况", align=WD_ALIGN_PARAGRAPH.CENTER)
doc.addPic(_f1)
doc.addText(u"图2 考勤（上班时间）分布情况", align=WD_ALIGN_PARAGRAPH.CENTER)
doc.addPic(_f2)
doc.addText(u"图3 考勤（下班时间）分布情况", align=WD_ALIGN_PARAGRAPH.CENTER)
else:
_print("【无“考勤”数据】")
_print("请假情况：", title=True, title_lvl=2)
_print(u'数据来源于“钉钉”考勤系统。')
doc.addTable(1, 2, col_width=(2, 4))
_title = (('text', u'名称'), ('text', u'关联的审批单'))
doc.addRow(_title)
_sql = 'select KQ_NAME,KQ_REF from checkon_t ' \
'where KQ_REF != "#" and created_at > "2018-03-19 12:00:00" and' \
' str_to_date(KQ_DATE,"%%y-%%m-%%d") between "%s" and "%s"' % (st_date, ed_date)
_res = doSQL(cur, _sql)
_old_row = ()
for _row in _res:
_text = (('text', u"%s" % _row[0]),
('text', (u"%s" % _row[1]).replace('^',' '))
)
if _old_row != _text:
doc.addRow(_text)
_old_row = _text
doc.setTableFont(8)
_print("")
_print("工作日志工时统计：", title=True, title_lvl=2)
_print(u'数据来源于任务管理系统。')
orgWT = ()
for _grp in GroupName:
"""mongoDB数据库
"""
mongodb = mongodb_class.mongoDB(ProjectAlias[_grp])
_print(_grp, title=True, title_lvl=3)
_sql = 'select MM_XM from member_t where MM_POST="%s" and MM_ZT=1' % _grp
_res = doSQL(cur, _sql)
for _row in _res:
if u"%s" % _row[0] in sp_name:
continue
_search = {"author": {'$regex': ".*%s.*" % _row[0]},
"$and": [{"started": {"$gte": "%s" % st_date}},
{"started": {"$lt": "%s" % ed_date}}]}
_cur = mongodb.handler('worklog', 'find', _search)
_n = 0.
for _rec in _cur:
_n += float(_rec['timeSpentSeconds'])/3600.
# print "---> " + _grp + " " + _row[0] + " : %0.2f" % _n
if _n == 0.:
continue
_color = None
_s = "[员工：" + str(_row[0]) + "，工作 %0.2f 工时" % _n
if _n > float(workhours):
_s = _s + "，加班 %0.2f 工时" % (_n - workhours) + "，占比 %0.2f %%" % ((_n-workhours)*100./workhours)
_color = (255, 0, 0)
if _n<workhours:
_s = _s + "，剩余 %0.2f 工时" % (workhours - _n) + "，占比 %0.2f %%" % ((workhours-_n)*100/workhours)
_color = (50, 100, 50)
_s = _s + ']'
_print(_s, color=_color)
orgWT = orgWT + (_n,)
if len(orgWT)>0:
_fn = doHour.doOprHour(orgWT, workhours)
doc.addPic(_fn)
doc.addText(u"图4 本周“人-工时”分布情况", align=WD_ALIGN_PARAGRAPH.CENTER)
"""插入分页"""
doc.addPageBreak()
_print("工作日志明细：", title=True, title_lvl=2)
_print(u'数据来源于任务管理系统。')
for _grp in GroupName:
"""mongoDB数据库
"""
mongodb = mongodb_class.mongoDB(ProjectAlias[_grp])
_print(_grp, title=True, title_lvl=3)
doc.addTable(1, 4, col_width=(2, 3, 2, 1))
_title = (('text', u'名称'), ('text', u'任务'), ('text', u'开始时间'), ('text', u'耗时'))
doc.addRow(_title)
_sql = 'select MM_XM from member_t where MM_ZT=1 and MM_POST="%s"' % _grp
_res = doSQL(cur, _sql)
for _row in _res:
if u"%s" % _row[0] in sp_name:
continue
_text = (('text', u"%s" % str(_row[0])),
('text', ""),
('text', ""),
('text', "")
)
doc.addRow(_text)
_search = {"author": {'$regex': ".*%s.*" % _row[0]},
"$and": [{"started": {"$gte": "%s" % st_date}}, {"started": {"$lt": "%s" % ed_date}}]}
_cur = mongodb.handler('worklog', 'find', _search).sort([('started', 1)])
_tot = 0.
for _rec in _cur:
_text = (('text', ""),
('text', (u"%s:\n%s" % (_rec['issue'], _rec['comment']))),
('text', _rec['started'].split('T')[0]),
('text', _rec['timeSpent'])
)
doc.addRow(_text)
_tot += float(_rec['timeSpentSeconds'])/3600.
_text = (('text', "-"),
('text', u"小计"),
('text', ""),
('text', "%0.2f" % _tot)
)
doc.addRow(_text)
doc.setTableFont(8)
_print("")
"""插入分页"""
doc.addPageBreak()
def getOprWorkTime(cur):
global TotalMember, orgWT, doc
_sql = 'select MM_XM from member_t'
_res = doSQL(cur,_sql)
TotalMember = 0
orgWT = ()
for _row in _res:
_sql = 'select sum(TK_GZSJ) from task_t where TK_ZXR="' + str(_row[0]) + '"' + " and created_at between '%s' and '%s'" % (st_date, ed_date)
_n = doSQLcount(cur,_sql)
if _n is None:
continue
if _n>0:
TotalMember = TotalMember + 1
_print("在岗总人数：%d" % TotalMember)
_sql = 'select sum(TK_GZSJ) from task_t' + " where created_at between '%s' and '%s'" % (st_date, ed_date)
_total_workdays = doSQLcount(cur, _sql)
if _total_workdays is not None:
_print("总工作量：%d （工时）" % _total_workdays)
if _total_workdays > 0:
_a = TotalMember * 80 * float(numb_days)
_b = _total_workdays*1000
_c = int(_b/_a)
_s = "工作效率：%d %%" % _c
if _c > 100:
_s = _s + "，超标 %0.2f%%" % (_c - 100.)
if _c < 100:
_s = _s + "，剩余 %0.2f%%" % (100.- _c)
else:
_s = "工作效率：0%"
_print(_s)
_print("1、考勤分布：", title=True, title_lvl=2)
data1 = getChkOnAm(cur)
data2 = getChkOnPm(cur)
if len(data1)>0 and len(data2)>0:
_f1,_f2,_f3 = doHour.doChkOnHour(data1,data2)
doc.addPic(_f3)
doc.addText(u"图1 考勤分布总体情况", align=WD_ALIGN_PARAGRAPH.CENTER)
doc.addPic(_f1)
doc.addText(u"图2 考勤（上班时间）分布情况", align=WD_ALIGN_PARAGRAPH.CENTER)
doc.addPic(_f2)
doc.addText(u"图3 考勤（下班时间）分布情况", align=WD_ALIGN_PARAGRAPH.CENTER)
else:
_print("【无“考勤”数据】")
_print("2、最耗时的工作（前15名）：", title=True, title_lvl=2)
_sql = 'select TK_RW,TK_ZXR,TK_GZSJ,TK_XMBH from task_t' + " where created_at between '%s' and '%s' order by TK_GZSJ+0 desc" % (st_date, ed_date)
_res = doSQL(cur,_sql)
if len(_res)>0:
for _i in range(15):
if str(_res[_i][3]) != "#":
_print( '%d）'% (_i+1) + str(_res[_i][1]) + ' 执行【' +str(_res[_i][3]) + '，' + str(_res[_i][0]) + '】任务时，耗时 ' + str(_res[_i][2]) + ' 工时')
elif str(_res[_i][0] != "#"):
_print( '%d）'% (_i+1) + str(_res[_i][1]) + ' 执行【 非项目类：' + str(_res[_i][0]) + '】任务时，耗时 ' + str(_res[_i][2]) + ' 工时')
else:
_print( '%d）'% (_i+1) + str(_res[_i][1]) + ' 执行【 非项目类】任务时，耗时 ' + str(_res[_i][2]) + ' 工时')
_print("3、明细：", title=True, title_lvl=2)
_sql = 'select MM_XM from member_t'
_res = doSQL(cur,_sql)
for _row in _res:
_sql = 'select sum(TK_GZSJ) from task_t where TK_ZXR="' + str(_row[0]) + '"' + " and created_at between '%s' and '%s'" % (st_date, ed_date)
_n = doSQLcount(cur,_sql)
if _n is None:
continue
if _n==0:
continue
_color = None
_s = "[员工：" + str(_row[0])+ "，工作 %d 工时" % _n
if _n>workhours:
_s = _s + "，加班 %d 工时" % (_n - workhours) + "，占比 %d %%" % ((_n-workhours)*100/workhours)
_color = (255,0,0)
if _n<workhours:
_s = _s + "，剩余 %d 工时" % (workhours - _n) + "，占比 %d %%" % ((workhours-_n)*100/workhours)
_color = (50, 100, 50)
_s = _s + ']'
_print(_s, color=_color)
orgWT = orgWT + (_n,)
if len(orgWT)>0:
_fn = doHour.doOprHour(orgWT, workhours)
doc.addPic(_fn)
doc.addText(u"图4 本周“人-工时”分布情况", align=WD_ALIGN_PARAGRAPH.CENTER)