def showiprecords():
"""
综合输出ip记录
"""
namestr = 'everip'
ip, wifi, wifiid, tun, device_id = iprecord()
if ip is None:
logstr = '无效ip，可能是没有处于联网状态'
log.critical(logstr)
sys.exit(1)
print(f'{ip}\t{wifi}\t{wifiid}\t{tun}\t{device_id}')
if not (guid := getcfpoptionvalue(namestr, device_id, 'guid')):
token = getcfpoptionvalue('everwork', 'evernote', 'token')
note_store = get_notestore()
parentnotebook = note_store.getNotebook(
'4524187f-c131-4d7d-b6cc-a1af20474a7f')
evernoteapijiayi()
note = ttypes.Note()
note.title = f'服务器_{device_id}_ip更新记录'
# note.title = "hengchu"
print(note.title)
note = makenote(token,
note_store,
note.title,
notebody='',
parentnotebook=parentnotebook)
guid = note.guid
setcfpoptionvalue(namestr, device_id, 'guid', guid)
def gettitleandcontent(ntguid: str):
ns = get_notestore()
nttitle = ns.getNote(ntguid, False, False, False, False).title
evernoteapijiayi()
ntcontent = ns.getNoteContent(ntguid)
evernoteapijiayi()
return nttitle, ntcontent
def jilunote(noteinfos):
"""
读取ifttt自动通过gmail转发至evernote生成的地段进出记录，统计作图展示
:rtype: Null
"""
# itemstr = BeautifulSoup(get_notestore().getNoteContent(noteinfos[0]), "html.parser"). \
# get_text().replace('\r', '').replace('\n', '') # 文本化后去掉回车、换行符等
itemstr = BeautifulSoup(get_notestore().getNoteContent(noteinfos[0]), "html.parser"). \
get_text().split('\r\n') # 文本化后去掉回车、换行符等，通过split转换为list
# print('笔记："%s" 中提取内容' %noteinfos[5])
# print(itemstr)
evernoteapijiayi()
return itemstr
def log2notes():
namestr = 'everlog'
device_id = getdeviceid()
token = getcfpoptionvalue('everwork', 'evernote', 'token')
# log.info(token)
if not (logguid := getcfpoptionvalue(namestr, device_id, 'logguid')):
note_store = get_notestore()
parentnotebook = note_store.getNotebook(
'4524187f-c131-4d7d-b6cc-a1af20474a7f')
evernoteapijiayi()
note = ttypes.Note()
note.title = f'服务器_{device_id}_日志信息'
notelog = makenote(token, note_store, note.title,
notebody='', parentnotebook=parentnotebook)
logguid = notelog.guid
setcfpoptionvalue(namestr, device_id, 'logguid', logguid)
def setguid():
try:
guid = cfpp.get(tosection, aa)
if len(guid) > 0:
# print('笔记《' + str(aa) + zhuti + '》已存在，guid为：' + guid)
return
except Exception as ee:
log.info('笔记《' + str(aa) + zhuti + '》不存在，将被创建……%s' % str(ee))
note = ttypes.Note()
note.title = nbfbdf.loc[aa]['title']
# print(aa + '\t\t' + note.title, end='\t\t')
parentnotebook = note_store.getNotebook(nbfbdf.loc[aa]['guid'])
evernoteapijiayi()
note = makenote(token,
note_store,
note.title,
parentnotebook=parentnotebook)
# print(note.guid + '\t\t' + note.title)
cfpp.set(tosection, aa, note.guid)
def newchatnote():
"""
构建新的聊天记录笔记(置于笔记本《notification》中)并返回
"""
note_store = get_notestore()
# parentnotebook = \
# note_store.getNotebook('4524187f-c131-4d7d-b6cc-a1af20474a7f')
parentnotebook = \
note_store.getNotebook(getinivaluefromnote(
'notebookguid', 'notification'))
evernoteapijiayi()
note = ttypes.Note()
note.title = f"微信（{getowner()['User']['NickName']}）记录:" \
f"{time.strftime('%Y-%m-%d_%H:%M:%S', time.localtime(time.time()))}"
print(note.title)
token = getcfpoptionvalue('everwork', 'evernote', 'token')
notechat = makenote(token, note_store, note.title, notebody='',
parentnotebook=parentnotebook)
return notechat
def foot2show(df4dis):
"""
展示足迹
"""
namestr = 'everloc'
if (device_id := getcfpoptionvalue(namestr, namestr, 'device_id')) is None:
device_id = getdeviceid()
setcfpoptionvalue(namestr, namestr, 'device_id', device_id)
device_id = str(device_id)
if (guid := getcfpoptionvalue(namestr, device_id, 'guid')) is None:
token = getcfpoptionvalue('everwork', 'evernote', 'token')
note_store = get_notestore()
parentnotebook = note_store.getNotebook(
'4524187f-c131-4d7d-b6cc-a1af20474a7f')
evernoteapijiayi()
# note = ttypes.Note()
title = f'手机_{device_id}_location更新记录'
# note.title = "hengchu"
print(title)
note = makenote(token,
note_store,
title,
notebody='',
parentnotebook=parentnotebook)
guid = note.guid
setcfpoptionvalue(namestr, device_id, 'guid', guid)
imglst = []
ds = df4dis['distance']
today = datetime.datetime.now().strftime('%F')
def getnote(guidin):
notein = note_store.getNote(guidin, True, True, False, False)
evernoteapijiayi()
return notein
def chulioldversion():
note_store = get_notestore()
notes = findnotefromnotebook(token, '2c8e97b5-421f-461c-8e35-0f0b1a33e91c',
'业务推广')
for ii in range(len(notes)):
note = notes[ii]
guid = note[0]
titles = note[1].split('—')
if len(titles) >= 2:
person = titles[-1]
print('%s\t%s' % (person, guid))
# if person != '黄壮': #开关，某人记录未读完是专项处理
# continue
else:
continue
verlist = note_store.listNoteVersions(token, guid)
evernoteapijiayi()
print(len(verlist))
print(verlist[0])
# NoteVersionId(updateSequenceNum=472609, updated=1527677247000, saved=1527682577000,
# title='业务推广日工作总结和计划——徐志伟')
items = []
note_store = get_notestore()
for ver in verlist:
print(
f'{ver.updateSequenceNum}\t{timestamp2str(int(ver.updated / 1000))}'
f'\t{timestamp2str(int(ver.saved / 1000))}')
try:
# if ver.updateSequenceNum >= 349273: #开关，断点续传
# continue
notever = note_store.getNoteVersion(token, guid,
ver.updateSequenceNum,
True, True, True)
evernoteapijiayi()
except Exception as eeee:
log.critical('%s业务日志读取版本%d中时出现错误，终止操作进入下一轮。%s' %
(person, ver.updateSequenceNum, str(eeee)))
break
soup = BeautifulSoup(notever.content,
"html.parser").get_text().strip()
planitems = chulinote_workplan(soup)
if len(planitems) == 0:
log.info('%s业务日志版本%d中无有效日志记录，跳过此版本。' %
(person, ver.updateSequenceNum))
continue
item = list()
item.append(person)
datestr = datetime.date.strftime(planitems[0][0], '%Y-%m-%d')
item.append(datestr)
item.append(planitems[0][0])
item.append(planitems[0][1])
item.append(len(planitems[0][1]))
item.append(timestamp2str(int(notever.updated / 1000)))
items.append(item)
print(items)
cnxp = lite.connect(dbpathworkplan)
tablename_updated = 'planupdated'
dfupdate = pd.DataFrame(items,
columns=[
'name', 'nianyueri', 'date', 'content',
'contentlength', 'updatedtime'
])
dfupdate.to_sql(tablename_updated,
cnxp,
index=False,
if_exists='append')
cnxp.close()
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()
def updatedb_workplan(note_store, persons):
# global dbpathworkplan, token, cfp, cfpworkplan
cnxp = lite.connect(dbpathworkplan)
tablename_plan = 'personplan'
tablename_updated = 'planupdated'
try:
liuxin = 5
for person in persons:
# print(person)
# if person != '梅富忠':
# continue
guid = cfpworkplan.get('业务计划总结guid', person).lower()
# print(guid)
if note_store is None:
note = get_notestore().getNote(guid, True, True, False, False)
log.debug(f'处理{person}日志时notestore失效，重构一个再来。')
else:
note = note_store.getNote(guid, True, True, False, False)
evernoteapijiayi()
# print(timestamp2str(int(note.updated/1000)))
# print(note.updateSequenceNum)
if cfpworkplan.has_option('业务计划总结updatenum', person):
usnini = cfpworkplan.getint('业务计划总结updatenum', person)
updatable = note.updateSequenceNum > usnini
else:
usnini = 0
updatable = True
if updatable is False:
log.info(f'{person}的工作日志本轮查询中无更新，跳过。')
continue
verlist = note_store.listNoteVersions(token, guid) # 历史版本，不含当前最新版本
evernoteapijiayi()
# 当前版本和历史版本组成轮训池
usnlist = [note.updateSequenceNum] + [
x.updateSequenceNum
for x in verlist if x.updateSequenceNum > usnini
]
# print(f'{note.updateSequenceNum}\t{usnini}\t{usnlist}')
log.info(f'业务主管{person}的日志有更新，版本号列表为：{usnlist}')
for verusn in usnlist[::-1]: # 倒过来，从最早的笔记版本开始处理
if verusn == note.updateSequenceNum:
vernote = note
else:
vernote = note_store.getNoteVersion(
token, guid, verusn, True, True, True)
evernoteapijiayi()
soup = BeautifulSoup(vernote.content,
"html.parser").get_text().strip()
planitems = chulinote_workplan(soup)
# print(planitems[:3])
if len(planitems) == 0:
log.info('%s业务日志中无有效日志记录，跳过。' % person)
continue
# 通过已存储项目的日期（如果没有就默认是当前项目列表最早日期的前一天）截取需要处理的项目并追加到相应数据表中
sqlstr = 'select max(date) from %s where name=\'%s\'' % (
tablename_plan, person)
dftmp = pd.read_sql(sqlstr, cnxp)
datemaxdb = pd.to_datetime(dftmp.iloc[0, 0])
print(f'{person}的日志数据表中最新条目日期为{datemaxdb}')
if datemaxdb is None:
datemaxdb = planitems[-1][0] + datetime.timedelta(days=-1)
planitemsxinxiancount = 0
for i in range(len(planitems)):
planitemsxinxiancount += 1
if planitems[i][0] <= datemaxdb:
break
baddate = False
for i in range(1, planitemsxinxiancount): # 逐项检查，看是否有时间倒序问题存在
baddate |= planitems[i - 1][0] < planitems[i][0]
if baddate:
log.critical(
f'{person}业务日志（版本号：{verusn}）中存在日期倒置：{planitems[i - 1][0]}，位于第{i}条。跳过。'
)
break
if baddate:
continue
if len(planitems) > liuxin: # 固化内容存储，预留5天可修改，随着记录更新追加到数据表中
dfrizhi = pd.DataFrame(planitems,
columns=['date', 'content'])
dfrizhi['name'] = person
dfrizhi = dfrizhi[['name', 'date', 'content']]
dfrizhi.sort_values(['date'], inplace=True) # 升序排列
dfrizhichuli = dfrizhi[dfrizhi.date > datemaxdb] # 取尾部记录
# print(dfrizhichuli)
if dfrizhichuli.shape[0] > liuxin:
dfrizhiappend = dfrizhichuli[:dfrizhichuli.shape[0] -
liuxin] # 取头部
dfrizhiappend.to_sql(tablename_plan,
cnxp,
index=False,
if_exists='append')
log.info(
f'{person}的业务日志有{dfrizhiappend.shape[0]}条追加到日志内容表中，最新日期为：{datemaxdb}'
)
sqlstr = 'select max(date) from %s where name=\'%s\'' % (
tablename_updated, person)
dftmp = pd.read_sql(sqlstr, cnxp)
datemaxdb = pd.to_datetime(dftmp.iloc[0, 0])
if datemaxdb is None:
datemaxdb = planitems[-1][0] + datetime.timedelta(days=-1)
print(f'{person}的日志更新数据表中最近有效日期为{datemaxdb}')
dfitems = pd.DataFrame(planitems, columns=['date', 'content'])
dfadd = dfitems[dfitems.date > datemaxdb]
if dfadd.shape[0] > 0:
print(dfadd)
else:
log.info(
f'{person}业务日志（版本号：{verusn}）暂无有效新内容，最新有效日期为{planitems[0][0]}'
)
continue
itemss = list()
for ix in dfadd.index:
item = list()
item.append(person)
datestr = datetime.date.strftime(dfadd.loc[ix, 'date'],
'%Y-%m-%d')
item.append(datestr)
item.append(dfadd.loc[ix, 'date'])
item.append(dfadd.loc[ix, 'content'])
item.append(len(dfadd.loc[ix, 'content']))
item.append(timestamp2str(int(vernote.updated / 1000)))
itemss.append(item)
dfupdate = pd.DataFrame(itemss,
columns=[
'name', 'nianyueri', 'date',
'content', 'contentlength',
'updatedtime'
])
print(dfupdate)
dfupdate.to_sql(tablename_updated,
cnxp,
index=False,
if_exists='append')
log.info(f'{person}的业务日志有{dfupdate.shape[0]}条追加到日志内容表中')
cfpworkplan.set('业务计划总结updatenum', person, '%d' % verusn)
cfpworkplan.write(open(iniworkplanpath, 'w', encoding='utf-8'))
else:
log.info('下列人员的日志笔记正常处置完毕：%s' % persons)
except Exception as eee:
log.critical('读取工作日志笔记更新入日志内容表和日志更新表时发生错误。%s' % str(eee))
finally:
cnxp.close()
def showorderstat():
# xlsfile = 'data\\work\\销售订单\\销售订单20180606__20180607034848_480667.xls'
# dforder = chulixls_order(xlsfile)
# global workplannotebookguid
workplannotebookguid = '2c8e97b5-421f-461c-8e35-0f0b1a33e91c'
pathor = dirmainpath / 'data' / 'work' / '销售订单'
dforder = chulidataindir_order(pathor)
# print(dforder.dtypes)
dingdanxiaoshouyuedufenxi(dforder)
dforder = dforder.loc[:, ['日期', '订单编号', '区域', '类型', '客户名称', '业务人员', '订单金额']]
dforder.sort_values(by=['日期', '订单编号', '业务人员'], ascending=False, inplace=True)
zuixinriqi = dforder.groupby(['日期'])['日期'].size().index.max()
orderdatestr = zuixinriqi.strftime('%F')
print(orderdatestr, end='\t')
dforderzuixinriqi = dforder[dforder.日期 == zuixinriqi]
print(dforderzuixinriqi.shape[0])
persons = list(dforderzuixinriqi.groupby('业务人员')['业务人员'].count().index)
# print(persons)
notestr = '每日销售订单核对'
if cfpzysm.has_section(notestr) is False:
cfpzysm.add_section(notestr)
cfpzysm.write(open(inizysmpath, 'w', encoding='utf-8'))
for person in persons:
if cfpzysm.has_option(notestr + 'guid', person) is False:
try:
notestore = get_notestore()
plannote = ttypes.Note()
plannote.title = notestr + person
nbody = '<?xml version="1.0" encoding="UTF-8"?>'
nbody += '<!DOCTYPE en-note SYSTEM "http://xml.evernote.com/pub/enml2.dtd">'
nbody += '<en-note>%s</en-note>' % plannote.title
plannote.content = nbody
plannote.notebookGuid = workplannotebookguid
token = cfp.get('evernote', 'token')
note = notestore.createNote(token, plannote)
evernoteapijiayi()
cfpzysm.set(notestr + 'guid', person, '%s' % note.guid)
cfpzysm.write(open(inizysmpath, 'w', encoding='utf-8'))
log.info('成功创建%s的%s笔记' % (person, notestr))
except Exception as ee:
log.critical('创建%s的%s笔记时出现错误。%s' % (person, notestr, str(ee)))
continue
if cfpzysm.has_option(notestr + 'guid', person + '最新订单日期'):
ordertoday = cfpzysm.get(notestr + 'guid', person + '最新订单日期')
if zuixinriqi <= pd.to_datetime(ordertoday): # and False: # 调试开关，强行生成图表
continue
dfperson = dforderzuixinriqi[dforderzuixinriqi.业务人员 == person]
dfpersonsum = dfperson.groupby('业务人员').sum()['订单金额']
del dfperson['业务人员']
del dfperson['日期']
print(person, end='\t')
print(dfpersonsum[0], end='\t')
personguid = cfpzysm.get(notestr + 'guid', person)
print(personguid)
neirong = tablehtml2evernote(dftotal2top(dfperson), f'{orderdatestr}{notestr}——{person}', withindex=False)
# print(neirong)
try:
notestore = get_notestore()
imglist2note(notestore, [], personguid, '%s——%s（%s）' % (notestr, person, orderdatestr), neirong)
cfpzysm.set(notestr + 'guid', person + '最新订单日期', '%s' % orderdatestr)
cfpzysm.write(open(inizysmpath, 'w', encoding='utf-8'))
except Exception as eeee:
log.critical('更新笔记%s——%s（%s）时出现严重错误。%s' % (notestr, person, orderdatestr, str(eeee)))
else:
log.info('下列人员的销售订单正常处置完毕：%s' % persons)
yuechuriqi = pd.to_datetime(f"{zuixinriqi.strftime('%Y')}-{zuixinriqi.strftime('%m')}-01")
dfsales = pd.DataFrame(dforder[dforder.日期 >= yuechuriqi])
dfsales = dfsales.groupby(['区域', '类型', '客户名称', '业务人员'], as_index=False).sum()
dfsales.sort_values(['区域', '订单金额'], inplace=True)
notestr = '销售订单金额（月）'
if cfpzysm.has_section(notestr) is False:
cfpzysm.add_section(notestr)
cfpzysm.write(open(inizysmpath, 'w', encoding='utf-8'))
for person in persons:
if cfpzysm.has_option(notestr, person) is False:
try:
notestore = get_notestore()
plannote = ttypes.Note()
plannote.title = notestr + person
nbody = '<?xml version="1.0" encoding="UTF-8"?>'
nbody += '<!DOCTYPE en-note SYSTEM "http://xml.evernote.com/pub/enml2.dtd">'
nbody += '<en-note>%s</en-note>' % plannote.title
plannote.content = nbody
plannote.notebookGuid = workplannotebookguid
# cfp, cfppath = getcfp('everwork')
token = cfp.get('evernote', 'token')
note = notestore.createNote(token, plannote)
evernoteapijiayi()
cfpzysm.set(notestr, person, '%s' % note.guid)
cfpzysm.write(open(inizysmpath, 'w', encoding='utf-8'))
log.info('成功创建%s的%s笔记' % (person, notestr))
except Exception as ee:
log.critical('创建%s的%s笔记时出现错误。%s' % (person, notestr, str(ee)))
continue
if cfpzysm.has_option(notestr, person + '最新订单日期'):
ordertoday = cfpzysm.get(notestr, person + '最新订单日期')
if zuixinriqi <= pd.to_datetime(ordertoday): # and False:
continue
dfperson = dfsales[dfsales.业务人员 == person]
dfpersonsum = dfperson['订单金额'].sum()
del dfperson['业务人员']
print(person, end='\t')
print(dfpersonsum, end='\t')
personguid = cfpzysm.get(notestr, person)
print(personguid)
neirong = tablehtml2evernote(dftotal2top(dfperson), f'{orderdatestr[:-3]}{notestr}', withindex=False)
# print(neirong)
try:
notestore = get_notestore()
imglist2note(notestore, [], personguid, '%s——%s（%s）' % (notestr, person, orderdatestr[:-3]), neirong)
cfpzysm.set(notestr, person + '最新订单日期', '%s' % orderdatestr)
cfpzysm.write(open(inizysmpath, 'w', encoding='utf-8'))
except Exception as eeee:
log.critical('更新笔记%s——%s（%s）时出现严重错误。%s' % (notestr, person, orderdatestr, str(eeee)))
else:
log.info('下列人员的销售订单金额月度分析正常处置完毕：%s' % persons)
dfsales = pd.DataFrame(dforder)
dfsales = dfsales.groupby(['日期', '业务人员'], as_index=False).sum()
# persons = list(dfsales.groupby('业务人员')['业务人员'].count().index)
# print(persons)
dfsales.sort_values(['日期'], inplace=True)
notestr = '销售金额分析图表'
if cfpzysm.has_section(notestr) is False:
cfpzysm.add_section(notestr)
cfpzysm.write(open(inizysmpath, 'w', encoding='utf-8'))
for person in persons:
if cfpzysm.has_option(notestr, person) is False:
try:
notestore = get_notestore()
plannote = ttypes.Note()
plannote.title = notestr + person
nbody = '<?xml version="1.0" encoding="UTF-8"?>'
nbody += '<!DOCTYPE en-note SYSTEM "http://xml.evernote.com/pub/enml2.dtd">'
nbody += '<en-note>%s</en-note>' % plannote.title
plannote.content = nbody
plannote.notebookGuid = workplannotebookguid
# cfp, cfppath = getcfp('everwork')
token = cfp.get('evernote', 'token')
note = notestore.createNote(token, plannote)
evernoteapijiayi()
cfpzysm.set(notestr, person, '%s' % note.guid)
cfpzysm.write(open(inizysmpath, 'w', encoding='utf-8'))
log.info('成功创建%s的%s笔记' % (person, notestr))
except Exception as ee:
log.critical('创建%s的%s笔记时出现错误。%s' % (person, notestr, str(ee)))
continue
dfperson = dfsales[dfsales.业务人员 == person]
zuixinriqi = dfperson.groupby(['日期'])['日期'].size().index.max()
orderdatestr = zuixinriqi.strftime('%F')
if cfpzysm.has_option(notestr, person + '最新订单日期'):
ordertoday = cfpzysm.get(notestr, person + '最新订单日期')
# print(f'{zuixinriqi}\t{ordertoday}')
if zuixinriqi <= pd.to_datetime(ordertoday): # and False: # 调试开关，强行生成图表
continue
dfpersonsum = dfperson['订单金额'].sum()
dfperson = dfperson.groupby(['日期']).sum()
# del dfperson['业务人员']
print(person, end='\t')
print(dfpersonsum, end='\t')
personguid = cfpzysm.get(notestr, person)
print(personguid)
# neirong = tablehtml2evernote(dftotal2top(dfperson), f'{orderdatestr[:-3]}{notestr}', withindex=False)
neirong = ""
# print(neirong)
try:
notestore = get_notestore()
imglist = dfin2imglist(dfperson, cum=True)
imglist2note(notestore, imglist, personguid, '%s——%s（%s）' % (notestr, person, orderdatestr[:-3]), neirong)
cfpzysm.set(notestr, person + '最新订单日期', '%s' % orderdatestr)
cfpzysm.write(open(inizysmpath, 'w', encoding='utf-8'))
except Exception as eeee:
log.critical('更新笔记%s——%s（%s）时出现严重错误。%s' % (notestr, person, orderdatestr, str(eeee)))
else:
log.info('下列人员的销售金额分析图表正常处置完毕：%s' % persons)
def dingdanxiaoshouyuedufenxi(dforder):
dfall = dforder.loc[:, :]
dfall['年月'] = dfall['日期'].apply(lambda x: x.strftime('%Y%m'))
# descdb(dfall)
zuijinchengjiaori = max(dfall['日期'])
print(f'数据集最新日期：{zuijinchengjiaori.strftime("%F")}')
if cfpdata.has_option('ordersaleguidquyu', '数据最新日期'):
daterec = pd.to_datetime(cfpdata.get('ordersaleguidquyu', '数据最新日期'))
if daterec >= zuijinchengjiaori: # and False:
log.info(f'订单数据集无更新，返回')
return
zuiyuanriqi = zuijinchengjiaori + datetime.timedelta(days=-365)
zuiyuanyuechu = pd.to_datetime(f"{zuiyuanriqi.strftime('%Y-%m')}-01")
print(zuiyuanyuechu.strftime("%F"))
dfkehu = dfall.groupby(['单位编号', '客户名称', '区域', '类型'], as_index=False).count()
dfkehu.drop_duplicates(['单位编号'], keep='last', inplace=True)
dfkehuzhengli = dfkehu[['单位编号', '客户名称', '区域', '类型']]
dfkehuzhengli.index = dfkehuzhengli['单位编号']
del dfkehuzhengli['单位编号']
# descdb(dfkehuzhengli)
dsquyuzuixinriqi = pd.Series(dfall[dfall.日期 == zuijinchengjiaori].groupby('区域').count().index.values)
# print(dsquyuzuixinriqi.values)
dfyuetongji = dfall[dfall.日期 >= zuiyuanyuechu].groupby(by=['年月', '单位编号'], as_index=False, sort=True)['订单金额'].sum()
dfyuetongji['订单金额'] = dfyuetongji['订单金额'].astype(int)
# descdb(dfyuetongji)
dfpivot = dfyuetongji.pivot(index='单位编号', values='订单金额', columns='年月')
dfpivot = pd.DataFrame(dfpivot)
cls = list(dfpivot.columns)
# print(cls)
# for cl in cls:
# dfpivot[cl] = dfpivot[cl].astype(int)
dfpivot['单位编号'] = dfpivot.index
dfpivot['客户名称'] = dfpivot['单位编号'].apply(lambda x: dfkehuzhengli.loc[x][0])
dfpivot['区域'] = dfpivot['单位编号'].apply(lambda x: dfkehuzhengli.loc[x][1])
dfpivot['类型'] = dfpivot['单位编号'].apply(lambda x: dfkehuzhengli.loc[x][2])
clsnew = ['客户名称', '区域', '类型'] + cls
# print(clsnew)
dfpivot = dfpivot.loc[:, clsnew]
dfpivot['成交月数'] = dfpivot.apply(lambda x: x[-13:].count(), axis=1)
def shouciyuefen(xx):
for i in range(len(xx)):
# print(xx[i])
if xx[i] > 0:
return i
else:
return 12
dfpivot['首次成交月份'] = dfpivot.apply(lambda x: clsnew[shouciyuefen(x[3:16]) + 3], axis=1)
dfpivot['首交月数'] = dfpivot.apply(lambda x: 13 - shouciyuefen(x[3:16]), axis=1)
def zuijinyuefen(xx):
for i in range(len(xx) - 1, -1, -1):
# print(f'{xx[i]}\t{i}')
if xx[i] > 0:
return i
else:
return 12
dfpivot['最近成交月份'] = dfpivot.apply(lambda x: clsnew[zuijinyuefen(x[3:16]) + 3], axis=1)
dfpivot['尾交月数'] = dfpivot.apply(lambda x: 13 - zuijinyuefen(x[3:16]), axis=1)
dfpivot['有效月数'] = dfpivot['首交月数'] - dfpivot['尾交月数'] + 1
dfpivot.fillna(0, inplace=True)
dfpivot['年总金额'] = dfpivot.apply(lambda x: sum(x[3:16]), axis=1)
# print(dfpivot.iloc[0, :])
dfpivot['年总金额'] = dfpivot['年总金额'].astype(int)
def youxiaoyuejun(jine, yueshu):
if yueshu == 0:
return 0
else:
return jine / yueshu
dfpivot['有效月均'] = dfpivot.apply(lambda x: youxiaoyuejun(x.年总金额, x.有效月数), axis=1)
dfpivot['有效月均'] = dfpivot['有效月均'].astype(int)
dfpivot.sort_values(['区域', '有效月均'], ascending=[True, False], inplace=True)
# descdb(dfpivot)
# clsnewnew = clsnew + ['有效月均', '年总金额']
# dfout = dfpivot[(dfpivot.有效月均 > 300) | (dfpivot.首交月数 < 5)]
# print(dfout[dfout.首交月数 < 5])
dfout = dfpivot
# dfshow = dfout.loc[:, clsnewnew]
dfshow = dfout.loc[:, :]
dfshow.fillna(0, inplace=True)
for cl in cls:
dfshow[cl] = dfshow[cl].astype(int)
# print(dfshow[dfshow.类型 == 'I'])
# descdb(dfshow)
cnx = lite.connect(dbpathworkplan)
# dfshow.to_sql('tmptable', cnx, index=True, if_exists='replace')
cursor = cnx.cursor()
cursor.execute(f'attach database \'{dbpathquandan}\' as \'C\'')
dfquyu = pd.read_sql('select * from C.quyu', cnx, index_col='index')
dfquyu.drop_duplicates(['区域'], keep='first', inplace=True)
dfquyu.index = dfquyu['区域']
del dfquyu['区域']
# descdb(dfquyu)
dfleixing = pd.read_sql('select * from C.leixing', cnx, index_col='index')
dfleixing.index = dfleixing['编码']
del dfleixing['编码']
# descdb(dfleixing)
dfshow['区域名称'] = dfshow['区域'].apply(lambda x: dfquyu.loc[x][0])
# print(dfshow[dfshow.类型 == '0'])
dfshow['类型小类'] = dfshow['类型'].apply(lambda x: dfleixing.loc[x][0])
dfshow['类型大类'] = dfshow['类型'].apply(lambda x: dfleixing.loc[x][1])
# descdb(dfshow)
cnx.close()
writer = pd.ExcelWriter(str(dirmainpath / 'data' / '客户销售总表.xlsx'))
dfzhongduan = dfshow[dfshow.类型大类 == '终端客户']
# dfzhongduan.to_excel('test4kehuquandan.xlsx')
# print(dfzhongduan.dtypes)
# print(dfzhongduan.head(5))
pd.DataFrame(dfzhongduan).to_excel(writer, sheet_name='客户销售全单')
log.info('成功输出《客户销售全单》')
writer.close()
# descdb(dfzhongduan)
targetlist = list()
dfzdall = dfzhongduan.loc[:, :]
# descdb(dfzdall)
notestore = get_notestore()
quyuset = set(dsquyuzuixinriqi.apply(lambda x: dfquyu.loc[x][0]).values)
print(quyuset)
# quyuset = set(list(dfzdall['区域名称']))
for qy in quyuset:
dfslice = dfzdall[dfzdall.区域名称 == qy]
dfslicesingle = dfslice.loc[:, :]
del dfslicesingle['区域名称']
# descdb(dfslicesingle)
print(qy, end='\t')
print(dfslicesingle.shape[0], end='\t')
# descdb(dfslicesingle)
if cfpdata.has_option('guidquyunb', qy):
nbguid = cfpdata.get('guidquyunb', qy)
else:
try:
notebook = ttypes.Notebook()
notebook.name = qy
notebook = notestore.createNotebook(notebook)
nbguid = notebook.guid
cfpdata.set('guidquyunb', qy, nbguid)
cfpdata.write(open(inidatanotefilepath, 'w', encoding='utf-8'))
except OSError as eeeee:
nbguid = None
log.critical(f'创建《{qy}》笔记本时出现错误。{eeeee}')
print(nbguid, end='\t')
if cfpdata.has_option('ordersaleguidquyu', qy + 'guid'):
ntguid = cfpdata.get('ordersaleguidquyu', qy + 'guid')
else:
try:
note = ttypes.Note()
note.title = qy + "订单金额年度分析"
note.content = '<?xml version="1.0" encoding="UTF-8"?>' \
'<!DOCTYPE en-note SYSTEM "http://xml.evernote.com/pub/enml2.dtd">'
note.content += '<en-note>专营休闲美食</en-note>'
note.notebookGuid = nbguid
note = notestore.createNote(note)
evernoteapijiayi()
ntguid = note.guid
cfpdata.set('ordersaleguidquyu', qy + 'guid', ntguid)
cfpdata.write(open(inidatanotefilepath, 'w', encoding='utf-8'))
except OSError as ee:
ntguid = None
log.critical(f'创建《{qy}订单金额年度分析》笔记时出现错误。{ee}')
print(ntguid)
target = list()
target.append(qy)
target.append(ntguid)
target.append(dfslicesingle)
targetlist.append(target)
# descdb(dfshow[dfshow.index.str.find('XF') >= 0])
lqzlist = [['连锁客户', '0e8ba322-4874-4627-a6de-13c69fffc88d'],
['渠道客户', '4AC03027-5929-415B-9711-0A8170263189'.lower()],
['直销客户', 'B0731D74-C268-417E-A8B7-7BE3EE590FAE'.lower()]
]
for [mingmu, mmguid] in lqzlist:
dfls = dfshow.loc[:, :]
dfls = dfls[dfls.类型大类 == mingmu]
# dfls = dfls[str(dfls.index)[7:9] == 'XF']
del dfls['类型大类']
dfls.sort_values(['有效月均'], ascending=False, inplace=True)
target = list()
target.append(mingmu)
target.append(mmguid)
target.append(dfls)
targetlist.append(target)
lslist = [['学府超市', 'XF', 'ce26a763-81cc-421f-8430-22dab21ba43e'],
['红生超市', 'HS', '1F0995DA-1DEC-4333-8EA6-8C85B54E2B71'.lower()],
['五分钟', 'WF', '41518B63-FF81-4719-BFE0-0E5BBFBC295A'.lower()]
]
for [mingcheng, bianma, guid] in lslist:
dfls = dfshow.loc[:, :]
dfls = dfls[dfls.index.str.find(bianma) >= 0]
del dfls['类型大类']
if dfls.shape[0] == 0:
log.info(f'连锁超市{mingcheng}没有数据记录，跳过')
continue
dfls.sort_values(['有效月均'], ascending=False, inplace=True)
target = list()
target.append(mingcheng)
target.append(guid)
target.append(dfls)
targetlist.append(target)
# print(targetlist)
for [qy, ntguid, dfslicesingle] in targetlist:
try:
dfzdclnames = list(dfslicesingle.columns)
# print(dfzdclnames)
dfzdclnames3 = dfzdclnames[3:16]
dfzdclnamesnew = [dfzdclnames[0]] + [dfzdclnames[-2]] + dfzdclnames3 + [dfzdclnames[23]] + [dfzdclnames[22]]
# print(dfzdclnamesnew)
stattitle = f'总客户：{dfslicesingle.shape[0]}，' \
f'在线客户（前三个月有成交记录）：{dfslicesingle[dfslicesingle.尾交月数 <= 4].shape[0]}，' \
f'本月成交客户：{dfslicesingle[dfslicesingle.尾交月数 == 1].shape[0]}'
imglist2note(notestore, [], ntguid, qy + '订单金额年度分析',
tablehtml2evernote(dftotal2top(dfslicesingle.loc[:, dfzdclnamesnew]), stattitle,
withindex=False, setwidth=False))
cfpdata.set('ordersaleguidquyu', qy + 'count', f'{dfslicesingle.shape[0]}')
cfpdata.write(open(inidatanotefilepath, 'w', encoding='utf-8'))
log.info(f'{qy}数据项目成功更新')
except OSError as eee:
log.critical(f'《{qy}订单金额年度分析》笔记更新时出现错误。{eee}')
# print(dfslicesingle.shape[0])
else:
cfpdata.set('ordersaleguidquyu', '数据最新日期', f'{zuijinchengjiaori}')
cfpdata.write(open(inidatanotefilepath, 'w', encoding='utf-8'))
