def second():
df = dfinput.copy()
df['weekday'] = df['riqi'].dt.weekday
df.set_index('riqi', inplace=True)
df['workday'] = isworkday(df.index).set_index('date')['work']
return df
def first():
df = dfinput.copy()
df['weekday'] = df['riqi'].dt.weekday
df['workday'] = df['riqi'].apply(lambda x: isworkday([x])['work'])
df.set_index('riqi', inplace=True)
print(df)
def showdutyonfunc(dtlist: list = None, zglist: list = None):
cnxwp = lite.connect(dbpathworkplan)
dfduty = pd.read_sql('select * from dutyon',
cnxwp,
parse_dates=['ruzhi', 'lizhi'])
# print(dfduty)
if dfduty is None:
return
# 处理参数：空则从本月1日到今天；一个则默认是起始日期，到今天；数组则判断大小
if len(dtlist) == 0:
dtto = pd.to_datetime(datetime.datetime.today())
dtfrom = pd.to_datetime(dtto.strftime('%Y-%m-01'))
elif len(dtlist) == 1:
dtto = pd.to_datetime(datetime.datetime.today())
dtshuru = pd.to_datetime(dtlist[0])
dtfrom = dtshuru
else:
dtto = max(dtlist)
dtfrom = min(dtlist)
# 首尾界限处理，最早公司创立日，最晚是今天
if dtfrom < pd.to_datetime('2010-10-22'):
dtfrom = pd.to_datetime('2010-10-22')
if dtto > pd.to_datetime(datetime.datetime.today()):
dtto = pd.to_datetime(datetime.datetime.today())
dtlistinner = pd.date_range(dtfrom, dtto, freq='D')
# print(dtfrom, type(dtfrom), dtto, type(dtto))
if zglist:
dfdutytarget = dfduty
zaizhi = zglist
else:
dfdutytarget = dfduty[((pd.isnull(dfduty.lizhi)) |
(dfduty.lizhi >= dtfrom))]
# print(dfdutytarget)
dfdutytarget = dfdutytarget[dfdutytarget.ruzhi <= dtto]
# print(dfdutytarget)
zaizhi = list(dfdutytarget.groupby('name').count().index)
print(f'{len(zaizhi)}\t{zaizhi}')
dfquanti = isworkday(list(dtlistinner))
dtquanti = dfquanti.groupby('xingzhi').count()['date'].rename('公司')
dtquanti['起始日期'] = dtfrom.strftime('%F')
dtquanti['截止日期'] = dtto.strftime('%F')
# print(list(dtquanti))
# 挂接高温记录统计
dfhot = pd.read_sql('select * from hot', cnxwp, parse_dates=['date'])
# print(dfhot)
dfhotgrp = dfhot.groupby('date',
as_index=False).count()[['date', 'mingmu']]
# print(dfhotgrp)
dfhotworkday = isworkday(list(dfhotgrp['date']))
dfhotworkday = dfhotworkday[dfhotworkday.work]
dfhotdone = dfhotworkday[(dfhotworkday.date >= dtfrom)
& (dfhotworkday.date <= dtto)]
dtquanti['高温'] = dfhotdone.shape[0]
# 挂接雨天记录统计
dfrain = pd.read_sql('select * from rain', cnxwp, parse_dates=['date'])
# print(dfhot)
dfraingrp = dfrain.groupby('date',
as_index=False).count()[['date', 'mingmu']]
# print(dfhotgrp)
dfrainworkday = isworkday(list(dfraingrp['date']))
dfrainworkday = dfrainworkday[dfrainworkday.work]
dfraindone = dfrainworkday[(dfrainworkday.date >= dtfrom)
& (dfrainworkday.date <= dtto)]
dtquanti['下雨'] = dfraindone.shape[0]
dslist = [dtquanti]
for zg in zaizhi:
# print(zg)
zgruzhimax = dfdutytarget[dfdutytarget.name == zg]['ruzhi'].max()
dtfrom4 = dtfrom
if dtfrom4 < pd.to_datetime(zgruzhimax):
dtfrom4 = pd.to_datetime(zgruzhimax)
dfgzmaxruizhi = dfdutytarget[(dfdutytarget.name == zg)
& (dfdutytarget.ruzhi == zgruzhimax)]
# print(dfgzmaxruizhi)
zgruzhi, zglizhi = dfdutytarget.loc[dfgzmaxruizhi.index,
['ruzhi', 'lizhi']].values[0]
# print(zgruzhi, type(zgruzhi), zglizhi, type(zglizhi))
dfzgwork = isworkday(list(dtlistinner), zg, fromthen=True)
# print(dfzgwork)
dfzgwork = dfzgwork[dfzgwork.date >= zgruzhi]
dtto4 = dtto
if pd.notnull(zglizhi):
if dtto4 > pd.to_datetime(zglizhi):
dtto4 = pd.to_datetime(zglizhi)
dfzgwork = dfzgwork[dfzgwork.date <= zglizhi]
dszg = dfzgwork.groupby('xingzhi').sum()['tianshu']
# print(dszg.name)
dszg = pd.Series(dszg)
dszg = dszg.rename(zg)
dszg['起始日期'] = dtfrom4.strftime('%F')
dszg['截止日期'] = dtto4.strftime('%F')
# 挂接高温记录统计
dfhotworkday = isworkday(list(dfhotgrp['date']), zg, fromthen=True)
dfhotworkday = dfhotworkday[dfhotworkday.work]
dfhotdone = dfhotworkday[(dfhotworkday.date >= dtfrom4)
& (dfhotworkday.date <= dtto4)]
# 修正上了半天班的情况
xiuzheng = dfhotdone[(dfhotdone.xingzhi == '上班')
& (dfhotdone.tianshu < 1)].shape[0]
# print(xiuzheng)
dszg['高温'] = dfhotdone.shape[0] - xiuzheng
# 挂接高温记录统计
dfrainworkday = isworkday(list(dfraingrp['date']), zg, fromthen=True)
dfrainworkday = dfrainworkday[dfrainworkday.work]
dfraindone = dfrainworkday[(dfrainworkday.date >= dtfrom4)
& (dfrainworkday.date <= dtto4)]
# 修正上了半天班的情况
xiuzheng = dfraindone[(dfraindone.xingzhi == '上班')
& (dfraindone.tianshu < 1)].shape[0]
# print(xiuzheng)
dszg['下雨'] = dfraindone.shape[0] - xiuzheng
# print(f'{dszg.name}：{list(dszg)}')
dslist.append(dszg)
dfgzduty = pd.DataFrame(dslist)
dfgzduty.fillna(0, inplace=True)
# print(dfgzduty)
# return dfgzduty, dtquanti, zaizhi
if '上班' in list(dfgzduty.columns):
dfshangban = pd.DataFrame(dfgzduty['上班'].rename('出勤'))
else:
dfshangban = pd.DataFrame()
# print(dfout)
clsnames = list(dfgzduty.columns)
# print(clsnames)
clsjiaxiu = [
x for x in clsnames if (x.find('上班') < 0) & (x.find('请假') < 0)
& (x.find('旷') < 0) & (x.find('日期') < 0)
& (x.find('高温') < 0) & (x.find('下雨') < 0)
]
# print(clsjiaxiu)
# print(dfgzduty.loc[:, clsjiaxiu])
dfjiaxiusum = dfgzduty.loc[:, clsjiaxiu].apply(lambda x: sum(x),
axis=1).rename('放休年')
# dfout.append(dfjiaxiusum)
# clsqingjia = [x for x in clsnames if x.find('假') > 0]
# dfqingjia = dfgzduty.loc[:, clsqingjia].apply(lambda x: sum(x), axis=1).rename('事年')
# dfout.append(dfqingjia)
# dfout = pd.concat([dfshangban, dfjiaxiusum, dfqingjia], axis=1)
dfout = pd.concat([dfshangban, dfjiaxiusum], axis=1)
if len([x for x in clsnames if x.find('旷工') >= 0]) > 0:
dfkuangong = pd.DataFrame(dfgzduty['旷工'])
dfout = pd.concat([dfout, dfkuangong], axis=1)
if len([x for x in clsnames if x.find('请假') >= 0]) > 0:
dfqingjia = pd.DataFrame(dfgzduty['请假'])
dfout = pd.concat([dfout, dfqingjia], axis=1)
dfout = pd.DataFrame(dfout)
dfout.fillna(0, inplace=True)
dfout['在职天数'] = dfout.apply(lambda x: sum(x), axis=1)
# print(dfout)
dfout = pd.concat([dfout, dfgzduty.loc[:, ['起始日期', '截止日期']]], axis=1)
dfout = pd.DataFrame(dfout)
# print(dfout)
# dfout.sort_values(['截止日期', '出勤'], ascending=[False, False], inplace=True)
clsout = list(dfout.columns)
# print(clsout)
clsnew = clsout[-2:] + [clsout[-3]] + clsout[:-3]
# print(clsnew)
dfout = dfout.loc[:, clsnew]
# 挂接打卡记录统计
dfcheckin = pd.read_sql_query('select * from checkin',
cnxwp,
parse_dates=['date'])
# dfcheckin.columns = ['date', 'name', 'mingmu', 'shenpi']
cnxwp.close()
dfcheckin = dfcheckin[(dfcheckin.date >= dtfrom)
& (dfcheckin.date <= dtto)]
dfcheckinout = dfcheckin[dfcheckin['shenpi'] == ''].groupby(
['name', 'mingmu']).count()['shenpi'].unstack()
if dfcheckinout.shape[0] > 0:
print(dfcheckinout)
dfout = pd.concat([dfout, dfcheckinout], axis=1)
dfout = pd.concat([dfout, pd.DataFrame(dfgzduty['高温'])], axis=1)
dfout = pd.concat([dfout, pd.DataFrame(dfgzduty['下雨'])], axis=1)
dfout = pd.DataFrame(dfout)
# print(dfout.columns)
dfout.fillna(0, inplace=True)
dfout.index.names = [f'{dfout.shape[0] - 1}']
return dfout, dtfrom, dtto
with pathmagic.context():
from life.noteweather import getrainfromgoogledrive
dfinput = getrainfromgoogledrive()
print(dfinput)
# +
with pathmagic.context():
from work.dutyon import showdutyonfunc
from func.pdtools import isworkday
from func.wrapfuncs import timethis
from func.first import dbpathworkplan
df = dfinput.copy()
# dfworkday = showdutyonfunc(['2019-01-01'])
print(isworkday(df['riqi']).set_index('date'))
df['weekday'] = df['riqi'].dt.weekday
@timethis
def first():
df = dfinput.copy()
df['weekday'] = df['riqi'].dt.weekday
df['workday'] = df['riqi'].apply(lambda x: isworkday([x])['work'])
df.set_index('riqi', inplace=True)
print(df)
@timethis
def second():
df = dfinput.copy()
def planfenxifunc():
# global dbpathworkplan
cnxp = lite.connect(dbpathworkplan)
tablename_updated = 'planupdated'
errorshowstr = '更新业务日志汇总笔记时出现错误。'
try:
# fetchattendance_from_evernote()
note_store = get_notestore()
# 从印象笔记中取得在职业务列表
persons = BeautifulSoup(
note_store.getNoteContent('992afcfb-3afb-437b-9eb1-7164d5207564'),
'html.parser')
# print(persons)
pslist = list()
patn = re.compile('\s*[,，]\s*')
for ddiv in persons.find_all('div'):
psitem = re.split(patn, ddiv.get_text())
if len(psitem) == 3:
atom = list()
atom.append(psitem[0])
atom.append(pd.to_datetime(psitem[1]))
atom.append(pd.to_datetime(psitem[2]))
pslist.append(atom)
personsdf = pd.DataFrame(pslist,
columns=['name', 'shanggang', 'ligang'])
personsdfzaigang = personsdf[pd.isnull(personsdf['ligang'])]
# print(personsdfzaigang)
persons = list(personsdfzaigang['name'])
print(persons)
evernoteapijiayi()
# 更新相应数据表内容
updatedb_workplan(note_store, persons)
# 从数据表中查询日志更新记录，只针对在职业务人员
# 组装sql语句使用的set，形如('梅富忠','陈益','周莉')
personsetstr = '('
for pr in ['\'' + x + '\'' for x in persons]:
personsetstr += pr + ','
personsetstr = personsetstr[:-1] + ')'
# print(personsetstr)
sqlstr = f'select distinct * from {tablename_updated} where name in {personsetstr} ' \
f'order by date desc, name, updatedtime desc'
dfsource = pd.read_sql(sqlstr,
cnxp,
parse_dates=['date', 'updatedtime'])
# 通过ini中记录的有效日志条目（以日期为单位）数量判断是否有有效的日志更新条目
updatablelist = []
for person in persons:
planitemscount = dfsource.loc[dfsource.name == person].shape[0]
print(f'{person}日志更新记录有{planitemscount}条')
if cfpworkplan.has_option('业务计划总结itemscount', person):
updatable = planitemscount > cfpworkplan.getint(
'业务计划总结itemscount', person)
else:
updatable = True
updatablelist.append(updatable)
print(f'{persons}，{updatablelist}')
updatableall = False
for i in range(len(updatablelist)):
updatableall |= updatablelist[i]
if updatableall:
break
if updatableall: # or True:
dayscount = cfpworkplan.getint('业务计划总结dayscount', 'count')
today = pd.to_datetime(datetime.datetime.today().strftime('%F'))
workdays = isworkday([today - datetime.timedelta(days=60)],
'全体',
fromthen=True)
# print(workdays)
dtqishi = workdays[workdays.work].groupby(
'date').count().sort_index(ascending=False).index[dayscount -
1]
print(f'最近{dayscount}个工作日（公司）的起始日期：{dtqishi}')
# dtqishi = today - datetime.timedelta(days=dayscount)
dtqujian = pd.date_range(dtqishi, today, freq='D').values
dfqujian = pd.DataFrame()
for person in persons:
resultlist = isworkday(dtqujian, person)
dftmp = pd.DataFrame(
resultlist,
columns=['date', 'name', 'work', 'xingzhi', 'tianshu'])
shanggangdate = personsdf[personsdf.name ==
person]['shanggang'].values[0]
dftmp = dftmp[dftmp.date >= shanggangdate]
if dfqujian.shape[0] == 0:
dfqujian = dftmp
else:
dfqujian = dfqujian.append(dftmp)
dfsourcequjian = dfsource[dfsource.date >= dtqishi]
# print(dfqujian)
# print(dfsourcequjian)
dfresult = pd.merge(dfqujian,
dfsourcequjian,
on=['date', 'name'],
how='outer')
# dflast = dfresult[dfresult.work == True].sort_values(['date', 'name'], ascending=[False, True])
dflast = dfresult.sort_values(['date', 'name'],
ascending=[False, True])
df = dflast.loc[:, [
'name', 'date', 'contentlength', 'updatedtime', 'xingzhi',
'tianshu'
]]
df2show = df.drop_duplicates(['name', 'date'], keep='last')
print(f'去重前记录数为：{df.shape[0]}，去重后记录是：{df2show.shape[0]}')
df2show.columns = ['业务人员', '计划日期', '内容字数', '日志更新时间', '出勤', '天数']
# print(df2show)
def hege(a, b, c):
freelist = ['请假']
if c in freelist:
return f'{c}'
if pd.isnull(b):
# print(f'{a}\t{b}')
return '未交'
if pd.to_datetime(a) > pd.to_datetime(b):
return '准时'
else:
return '延迟'
col_names = list(df2show.columns)
col_names.append('提交')
df2show = df2show.reindex(columns=col_names)
df2show.loc[:, ['提交']] = df2show.apply(
lambda x: hege(x.计划日期, x.日志更新时间, x.出勤), axis=1)
print(df2show[pd.isnull(df2show.日志更新时间)])
df2show['计划日期'] = df2show['计划日期'].apply(
lambda x: x.strftime('%m-%d'))
df2show['日志更新时间'] = df2show['日志更新时间'].apply(
lambda x: x.strftime('%m-%d %H:%M') if pd.notnull(x) else '')
df2show['内容字数'] = df2show['内容字数'].apply(lambda x: str(int(x))
if pd.notnull(x) else '')
df2show = df2show.loc[:, [
'业务人员', '计划日期', '内容字数', '日志更新时间', '提交', '出勤'
]]
freeday = ['周日']
df2show = df2show[df2show['出勤'].map(lambda x: x not in freeday)]
# print(df2show)
for ix in df2show.index:
yanchiweitijiao = ['延迟', '未交']
queqin = ['请假']
if df2show.loc[ix]['出勤'] in queqin:
for clname in df2show.columns:
df2show.loc[ix, clname] = df2show.loc[ix, clname].join(
['<span style=\"color:gray\">', '</span>'])
elif df2show.loc[ix]['提交'] in yanchiweitijiao:
for clname in df2show.columns:
df2show.loc[ix, clname] = df2show.loc[ix, clname] \
.join(['<span style=\"color:red\">', '</span>'])
df2show = df2show.loc[:, ['业务人员', '计划日期', '内容字数', '日志更新时间', '提交']]
# descdb(df2show)
neirong = tablehtml2evernote(df2show,
'业务工作日志提交情况汇总（最近%d工作日）' % dayscount,
withindex=False)
neirong = html.unescape(neirong)
# print(neirong)
guid = cfpworkplan.get('业务计划总结guid', '汇总')
huizongnoteupdatedtime = datetime.datetime.now().strftime('%F %T')
imglist2note(note_store, [], guid,
'业务工作日志提交情况汇总（%s）' % huizongnoteupdatedtime, neirong)
for person in persons:
dfperson = dfsource.loc[dfsource.name == person]
planitemscount = dfperson.shape[0]
if cfpworkplan.has_option('业务计划总结itemscount', person):
updatable = planitemscount > cfpworkplan.getint(
'业务计划总结itemscount', person)
else:
updatable = True
if updatable:
if dfperson.shape[0] == 0:
continue
log.info('%s的业务日志条目数增加至%d，日志更新表中最新日期为：%s。' %
(person, planitemscount,
str(dfperson.iloc[0]['nianyueri'])))
cfpworkplan.set('业务计划总结itemscount', person,
'%d' % planitemscount)
cfpworkplan.write(open(iniworkplanpath, 'w', encoding='utf-8'))
except OSError as ose:
if ose.errno == 10054:
log.critical(f'远程主机发脾气了，强行断线。')
log.critical(f'{errorshowstr}Windows错误：{ose}')
except AttributeError as ae:
log.critical(f'{errorshowstr}属性错误：{ae}')
except Exception as eee:
log.critical(f'{errorshowstr}{eee}')
# raise eee
finally:
cnxp.close()