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'))