def formatmsg(msg): """ 格式化并重构msg,获取合适用于直观显示的用户名,对公众号和群消息特别处置 """ timetuple = time.localtime(msg['CreateTime']) timestr = time.strftime("%Y-%m-%d %H:%M:%S", timetuple) # print(msg['CreateTime'], timetuple, timestr) men_wc = getcfpoptionvalue('everwebchat', get_host_uuid(), 'host_nickname') # 信息延时登记入专门数据库文件 dbname = touchfilepath2depth(getdirmain() / "data" / "db" / f"wcdelay_{men_wc}.db") inserttimeitem2db(dbname, msg['CreateTime']) # owner = itchat.web_init() meu_wc = getcfpoptionvalue('everwebchat', get_host_uuid(), 'host_username') send = (msg['FromUserName'] == meu_wc) if 'NickName' in msg["User"].keys(): showname = msg['User']['NickName'] if len(msg['User']['RemarkName']) > 0: showname = msg['User']['RemarkName'] elif 'UserName' in msg['User'].keys(): showname = msg['User']['UserName'] elif 'userName' in msg['User'].keys(): showname = msg['User']['userName'] else: showname = "" log.warning(f"NickName或者UserName或者userName键值不存在哦") showmsgexpanddictetc(msg) # 过滤掉已经研究过属性公众号信息,对于尚未研究过的显示详细信息 ignoredmplist = getinivaluefromnote('webchat', 'ignoredmplist') imlst = re.split('[,,]', ignoredmplist) ismp = type(msg['User']) == itchat.storage.MassivePlatform if ismp and (showname not in imlst): showmsgexpanddictetc(msg) # print(f"{showname}\t{imlst}") # 处理群消息 if type(msg['User']) == itchat.storage.templates.Chatroom: isfrom = msg['FromUserName'].startswith('@@') isto = msg['ToUserName'].startswith('@@') # qunmp = isfrom or isto # showmsgexpanddictetc(msg) if isfrom: # print(f"(群)\t{msg['ActualNickName']}", end='') showname += f"(群){msg['ActualNickName']}" elif isto: # print(f"(群)\t{msg['User']['Self']['NickName']}", end='') showname += f"(群){msg['User']['Self']['NickName']}" # print(f"\t{msg['Type']}\t{msg['MsgType']}\t{msg['Text']}") # print(f"\t{send}\t{msg['Type']}\t{msg['Text']}") fmtext = msg['Text'] finnalmsg = {'fmId': msg['MsgId'], 'fmTime': timestr, 'fmSend': send, 'fmSender': showname, 'fmType': msg['Type'], 'fmText': fmtext} return finnalmsg
def chulixls_orderdetails(orderfile: Path): try: content = xlrd.open_workbook( filename=orderfile, encoding_override='gb18030') df = pd.read_excel(content, index_col=0, parse_dates=True, engine='xlrd') log.info(f'读取{orderfile}') # print(list(df.columns)) except UnicodeDecodeError as ude: log.critical(f'读取{orderfile}时出现解码错误。{ude}') return # ['日期', '单据编号', '摘要', '单位全名', '仓库全名', '商品编号', '商品全名', '规格', '型号', '产地', '单位', '数量', '单价', '金额', '数量1', '单价1', # '金额1', '数量2', '单价2', '金额2'] totalin = ['%.2f' % df.loc[df.index.max()]['数量'], '%.2f' % df.loc[df.index.max()]['金额']] # 从最后一行获取数量合计和金额合计,以备比较 print(df['日期'].iloc[1], end='\t') print(totalin, end='\t') # df[xiangmu[0]] = None # df = df.loc[:, ['日期', '单据编号', '单据类型', xiangmu[0], '摘要', '备注', '商品备注', xiangmu[1], # '单价', '单位', '数量', '金额', '单位全名', '仓库全名', '部门全名']] df = df.loc[:, df.columns[:-6]] df['日期'] = pd.to_datetime(df['日期']) # df['备注'] = df['备注'].astype(object) dfdel = df[ (df.单位全名.isnull().values == True) & ((df.单据编号.isnull().values == True) | (df.单据编号 == '小计') | (df.单据编号 == '合计'))] hangdel = list(dfdel.index) # print(hangdel) df1 = df.drop(hangdel) # 丢掉小计和合计行,另起DataFrame dfzhiyuan = df1[df1.单位全名.isnull().values == True] # 提取出项目名称行号 zyhang = list(dfzhiyuan.index) zyming = list(dfzhiyuan['单据编号']) # 项目名称 # 每次填充df到最后一行,依次滚动更新之 df['员工名称'] = None for i in range(len(zyhang)): df.loc[zyhang[i]:, '员工名称'] = zyming[i] # 丢掉项目名称行,留下纯数据 dfdel = df[df.单位全名.isnull().values == True] # print(dfdel[['日期', '单据编号', '数量', '金额']]) hangdel = list(dfdel.index) # print(hangdel) dfout = df.drop(hangdel) dfout.index = range(len(dfout)) dfout = pd.DataFrame(dfout) # print(dfout) # print(dfout.head(10)) log.info('共有%d条有效记录' % len(dfout)) # print(list(dfout.columns)) if (totalin[0] == '%.2f' % dfout.sum()['数量']) & (totalin[1] == '%.2f' % dfout.sum()['金额']): dfgrp = dfout.groupby(['员工名称']).sum()[['数量', '金额']] dfgrp.loc['汇总'] = dfgrp.sum() print(dfgrp.loc['汇总'].values) return dfout else: log.warning(f'对读入文件《{orderfile}》的数据整理有误!总数量和总金额对不上!') return
def log2note(noteguid, loglimit, levelstr='', notetitle='everwork日志信息'): namestr = 'everlog' if levelstr == 'CRITICAL': levelstrinner = levelstr + ':' levelstr4title = '严重错误' countnameinini = 'everlogcc' else: levelstrinner = levelstr levelstr4title = '' countnameinini = 'everlogc' # log.info(getdirmain()) pathlog = getdirmain() / 'log' files = os.listdir(str(pathlog)) loglines = [] for fname in files[::-1]: # log.info(fname) if not fname.startswith('everwork.log'): log.warning(f'文件《{fname}》不是合法的日志文件,跳过。') continue with open(pathlog / fname, 'r', encoding='utf-8') as flog: charsnum2showinline = getinivaluefromnote('everlog', 'charsnum2showinline') # print(f"log行最大显示字符数量为:\t{charsnum2showinline}") loglines = loglines + [line.strip()[:charsnum2showinline] for line in flog if line.find(levelstrinner) >= 0] ptn = re.compile('\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2}') tmlst = [pd.to_datetime(re.match(ptn, x).group()) for x in loglines if re.match(ptn, x)] loglines = [x for x in loglines if re.match(ptn, x)] logsr = pd.Series(loglines, index=tmlst) logsr = logsr.sort_index() # print(logsr.index) # print(logsr) loglines = list(logsr) # log.info(loglines[:20]) # print(len(loglines)) print(f'日志的{levelstr4title}记录共有{len(loglines)}条,只取时间最近的{loglimit}条') if not (everlogc := getcfpoptionvalue(namestr, namestr, countnameinini)): everlogc = 0
def iprecord(): """ 获取本机ip """ device_id = getdeviceid() ip = wifi = wifiid = tun = None ethlst = get_ip4alleth() print(ethlst) if platform.system() == 'Windows': ip = ethlst[0][1] cmd = 'netsh wlan show interfaces' rrr = os.popen(cmd) rrrinfo = rrr.readlines() print(rrrinfo) for line in rrrinfo: if line.find('BSSID') >= 0: wifi = line.split(':', 1)[1].strip() continue if line.find('SSID') >= 0: wifiid = line.split(':', 1)[1].strip() continue return ip, wifi, wifiid, tun, device_id for ethinfo in ethlst: name, ipinner = ethinfo if name.startswith('tun'): tun = ipinner continue if name.startswith('wlan'): wifiinfo = termux_wifi_connectioninfo() print(wifiinfo) wifi = wifiinfo['ssid'] if wifi.find('unknown ssid') >= 0: logstr = f'WIFI处于假连状态:{wifi}\t{ipinner}' log.warning(logstr) wifi = None continue wifiid = wifiinfo['bssid'] ip = ipinner return ip, wifi, wifiid, tun, device_id
def set_timeout(num, callback): def wrap(func): def handle( signum, frame ): # 收到信号 SIGALRM 后的回调函数,第一个参数是信号的数字,第二个参数是the interrupted stack frame. raise RuntimeError def to_do(*args, **kwargs): try: if (sysstr := platform.system()) == "Linux": # print(sysstr) signal.signal(signal.SIGALRM, handle) # 设置信号和回调函数 signal.alarm(num) # 设置 num 秒的闹钟 # print('start alarm signal.') r = func(*args, **kwargs) # print('close alarm signal.') signal.alarm(0) # 关闭闹钟 return r else: r = func(*args, **kwargs) logstr = f"{sysstr}\t非linux系统,啥也没做。" log.warning(logstr) return r
def geturlcontentwrite2excel(ownername, url): """ 处理url,提取网页内容,有效数据写入数据文件,return url valid, if not valid, [url] """ excelpath, ownpy = makeexcelfileownpy(ownername) tdf = getsinglepage(url) print(tdf) if tdf.shape[0] > 0: roomid = tdf.iloc[0, 0] recorddf = pd.read_excel(excelpath) #vdrop dupliceres at first, I studun there for many times recorddf.drop_duplicates(['roomid', 'guestid'], inplace=True) oldsize = recorddf.shape[0] rstdf = recorddf.append( tdf, ignore_index=True) # 使用ignore_index开关,避免追加数据因为索引问题导致错乱 # 修正用户别名 rstdf = fixnamebyguestid(rstdf) rstdf = rstdf.sort_values(by=['time', 'score'], ascending=[False, False]) rstdf = rstdf.drop_duplicates(['roomid', 'guestid']) print(rstdf.iloc[:16, ]) if rstdf.shape[0] == oldsize: descstr = f"room {roomid} is already recorded. till then the recordsize is {oldsize}" log.warning(descstr) else: excelwriter = pd.ExcelWriter(excelpath) rstdf.to_excel(excelwriter, index=False, encoding='utf-8') excelwriter.close() descstr = f"{rstdf.shape[0]}条记录写入文件\t{excelpath}, new roomid {roomid} record done now.\n{tdf[['guest', 'score']]}" log.info(descstr) outurl = url else: descstr = f"no valid content there, 这个网页貌似无效\t{url}" log.critical(descstr) outurl = f"[{url}]" return outurl, descstr
def findnewcronthenupdate(): """ 查看当前登录用户的cron自动运行配置表是否有更新(修改时间),并视情况更新 """ # 获取用户名,方便适配不同的运行平台 r = os.popen('whoami') me = r.read()[:-1] # print(me) # print("This is just a test for jupytext.") # 打开配置表,过滤掉注释并简单检查(由6部分构成)是否符合规范 cronpath = Path('/data/data/com.termux/files/usr/var/spool/cron/crontabs') if not cronpath.exists(): cronpath = cronpath.parents[1] print(cronpath) log.warning("maybe newer linux, directory change") cronfile = cronpath / me cfl = open(cronfile, 'r').readlines() # print(f"{cfl}") cflen = [len(x.split()) for x in cfl if not x.startswith('#')] # print(cflen) clean = True for it in cflen: clean = clean and (it >= 6) if not clean: break # 进入更新判断处置环节 if clean: findnewthenupdatenote(cronfile, 'eversys', 'everwork', 'cron', 'cron自动运行排期表') else: logstr = f"自动运行排表有误,请检查。{len(cflen)}\t{cflen}" log.critical(logstr)
def details2db(filename, sheetname, xiangmu, tablename): """ :param filename: :param sheetname: :param xiangmu: =['职员名称','商品全名'],['产品名称','经办人'] :param tablename: :return: """ df = pd.read_excel(str(dirmainpath / 'data' / filename), sheetname='%s' % sheetname, index_col=0, parse_dates=True) log.info('读取%s' % filename) print(list(df.columns)) # ['日期', '单据编号', '摘要', '单据类型', '备注', '商品备注', '商品编号', '商品全名', # '规格', '型号', '产地', '单位', '数量', '单价', '金额', '含税单价', '价税合计', '成本金额', '毛利', '毛利率', # '单位全名', '仓库全名', '部门全名'] totalin = [ '%.2f' % df.loc[df.index.max()]['数量'], '%.2f' % df.loc[df.index.max()]['金额'] ] # 从最后一行获取数量合计和金额合计,以备比较 # print(totalin) df[xiangmu[0]] = None df = df.loc[:, [ '日期', '单据编号', '单据类型', xiangmu[0], '摘要', '备注', '商品备注', xiangmu[1], '单价', '单位', '数量', '金额', '单位全名', '仓库全名', '部门全名' ]] df['日期'] = pd.to_datetime(df['日期']) df['备注'] = df['备注'].astype(object) dfdel = df[(df.单位全名.isnull().values == True) & ((df.单据编号.isnull().values == True) | (df.单据编号 == '小计') | (df.单据编号 == '合计'))] hangdel = list(dfdel.index) df1 = df.drop(hangdel) # 丢掉小计和合计行,另起DataFrame dfzhiyuan = df1[df1.单位全名.isnull().values == True] # 提取出项目名称行号 zyhang = list(dfzhiyuan.index) zyming = list(dfzhiyuan['单据编号']) # 项目名称 # 每次填充df到最后一行,依次滚动更新之 for i in range(len(zyhang)): df.loc[zyhang[i]:, xiangmu[0]] = zyming[i] # 丢掉项目名称行,留下纯数据 dfdel = df[df.单位全名.isnull().values == True] # print(dfdel[['日期', '单据编号', '数量', '金额']]) hangdel = list(dfdel.index) # print(hangdel) dfout = df.drop(hangdel) dfout.index = range(len(dfout)) log.info('共有%d条有效记录' % len(dfout)) # return dfout # print(dfout.head(10)) # 读取大数据的起止日期,不交叉、不是前置则可能是合法数据,二次检查后放行 cnxp = lite.connect(dbpathquandan) # dfout.to_sql(name=tablename, con=cnx, if_exists='replace', chunksize=10000) # log.info('成功从数据文件《%s》中添加%d条记录到总数据表中。' %(filename, len(dfout))) if (totalin[0] == '%.2f' % dfout.sum()['数量']) & (totalin[1] == '%.2f' % dfout.sum()['金额']): dfall = pd.read_sql_query( 'select 日期, sum(金额) as 销售额 from %s group by 日期 order by 日期' % tablename, cnxp, parse_dates=['日期']) datestr4data = '【待插入S:%s,E:%s】【目标数据S:%s,E:%s】' \ % (dfout['日期'].min(), dfout['日期'].max(), dfall['日期'].min(), dfall['日期'].max()) if (dfall['日期'].max() >= dfout['日期'].min()) or (dfall['日期'].min() >= dfout['日期'].max()): log.warning('时间交叉了,请检查数据!%s' % datestr4data) # exit(2) else: print(dfout.tail()) if xiangmu[0] == '职员名称': dfout = chengbenjiaupdatedf(dfout, cnxp) log.info('要更新%d记录中的成本价和毛利内容' % dfout.shape[0]) print('请仔细检查!%s' % datestr4data) print('如果确保无误,请放行下面两行代码') # dfout.to_sql(name=tablename, con=cnxp, if_exists='append', chunksize=10000) # log.info('成功从数据文件《%s》中添加%d条记录到总数据表中。' % (filename, len(dfout))) else: log.warning('对读入文件《%s》的数据整理有误!总数量和总金额对不上!' % filename) cnxp.close() return dfout
itemnew = [ f'{ip}\t{wifi}\t{wifiid}\t{tun}\t{nowstr}'] itemnew.extend(itemnewr) # print(itemnew) readinifromnote() device_name = getcfpoptionvalue('everinifromnote', 'device', device_id) if not device_name: device_name = device_id setcfpoptionvalue(namestr, device_id, 'ipr', ip) setcfpoptionvalue(namestr, device_id, 'wifir', str(wifi)) setcfpoptionvalue(namestr, device_id, 'wifiidr', str(wifiid)) setcfpoptionvalue(namestr, device_id, 'tunr', str(tun)) start = datetime.datetime.now().strftime('%F %T') setcfpoptionvalue(namestr, device_id, 'start', start) # 把笔记输出放到最后,避免更新不成功退出影响数据逻辑 imglist2note(get_notestore(), [], guid, f'服务器_{device_name}_ip更新记录', "<pre>" + "\n".join(itemnew) + "</pre>") if __name__ == '__main__': if not_IPython(): log.info( f'开始运行文件\t{__file__}\t……\t{sys._getframe().f_code.co_name}\t{sys._getframe().f_code.co_filename}') if (bsdict := battery_status())['percentage'] >= 20: showiprecords() else: log.warning("手机电量低于20%,跳过ip轮询") # print(f"{self.__class__.__name__}") if not_IPython(): log.info(f'文件\t{__file__}\t执行完毕')
'datetime', 'latitude', 'longitude', 'altitude', 'accuracy', 'bearing', 'speed', 'elapsedMs', 'provider' ] locinfo = termux_location() print(locinfo) nowstr = datetime.datetime.now().strftime('%F %T') if locinfo == False: itemnewr = [f"{nowstr}\t{str(locinfo)}"] else: itemnewr = [ f"{nowstr}\t{locinfo[tlst[1]]}\t{locinfo[tlst[2]]}" f"\t{locinfo[tlst[3]]}\t{locinfo[tlst[4]]}" f"\t{locinfo[tlst[5]]}\t{locinfo[tlst[6]]}" f"\t{locinfo[tlst[7]]}\t{locinfo[tlst[8]]}" ] itemnewr.extend(itemread) print(itemnewr[:numlimit]) write2txt(txtfilename, itemnewr) # %% if __name__ == '__main__': if not_IPython(): log.info(f'运行文件\t{__file__}……') if (bsdict := battery_status())['percentage'] >= 20: foot2record() else: log.warning("手机电量低于20%,跳过GPS定位轮询") if not_IPython(): print(f"完成文件{__file__}\t的运行")
def orderdetails_check4product_customer(): targetpath = dirmainpath / 'data' / 'work' / '订单明细' # chulixls_order(targetpath / '订单明细20180614.xls.xls') dforder, hasnew = chulidataindir_orderdetails(targetpath) if (not hasnew) and False: log.info(f'订单明细数据无新增数据,本次产品和客户校验跳过。') return cnxp = lite.connect(dbpathquandan) dataokay(cnxp) dfchanpin = pd.read_sql(f'select * from product', cnxp, index_col='index') print(dfchanpin.columns) dfchanpingrp = dfchanpin.groupby(['商品全名']).count() print(dfchanpin.shape[0]) # dforder = pd.read_sql(f'select 商品全名, 商品编号, 单价, 金额 from xiaoshoumingxi', cnxp) print(dforder.columns) dict_mapping = {'单价': 'max', '金额': 'sum'} dfordergrp = dforder.groupby( ['商品全名', '商品编号'], as_index=False).agg(dict_mapping) dfordergrp.index = dfordergrp['商品全名'] print(dfordergrp.shape[0]) dfall = dfordergrp.join(dfchanpingrp, how='outer') # print(dfall) # dfall['商品编号'] = dfall['商品编号'].apply(lambda x: str(int(x)) if np.isnan(x) == False else x) dfduibichanpin = dfall[np.isnan(dfall.品牌名称)][['商品编号', '单价', '金额']] if dfduibichanpin.shape[0] > 0: chanpinnotin = list(dfduibichanpin.index) lasthash = getcfpoptionvalue('everdata', 'dataraw', 'chanpinhash') nowhash = hash(str(chanpinnotin)) if nowhash != lasthash: chanpinnotinxlspath = dirmainpath / 'data' / 'work' / '未录入新品.xlsx' with pd.ExcelWriter(chanpinnotinxlspath, mode='a') as writer: nowstr = datetime.datetime.now().strftime("%Y%m%d") dfduibichanpin.to_excel(writer, sheet_name=f"{nowstr}({dfduibichanpin.shape[0]})") log.critical(f'产品档案需要更新,共有{dfduibichanpin.shape[0]}个产品未包含:{chanpinnotin[:5]}…...,输出至文件{chanpinnotinxlspath}') setcfpoptionvalue('everdata', 'dataraw', 'chanpinhash', f"{nowhash}") else: log.warning(f'产品档案需要更新,共有{dfduibichanpin.shape[0]}个产品未包含:{chanpinnotin[:5]}...,列表hash为{nowhash}') return False dfkehu = pd.read_sql(f'select * from customer', cnxp, index_col='index') print(dfkehu.columns) dfkehugrp = dfkehu.groupby(['往来单位']).count() print(dfkehugrp.shape[0]) # dforder = pd.read_sql(f'select 单位全名, 数量, 金额 from xiaoshoumingxi', cnxp) print(dforder.columns) dict_mapping = {'数量': 'sum', '金额': 'sum'} dfordergrp = dforder.groupby(['单位全名'], as_index=False).agg(dict_mapping) dfordergrp.index = dfordergrp['单位全名'] print(dfordergrp.shape[0]) dfall = dfordergrp.join(dfkehugrp, how='outer') # print(dfall) # dfall['商品编号'] = dfall['商品编号'].apply(lambda x: str(int(x)) if np.isnan(x) == False else x) dfduibikehu = dfall[np.isnan(dfall.往来单位编号)][['往来单位编号', '数量', '金额']] if dfduibikehu.shape[0] > 0: kehunotin = list(dfduibikehu.index) for item in kehunotin: print(f"{item}\t{str2hex(item)}") lasthash = getcfpoptionvalue('everdata', 'dataraw', 'kehuhash') nowhash = hash(str(kehunotin)) if nowhash != lasthash: kehunotinxlspath = dirmainpath / 'data' / 'work' / '未录入客户.xlsx' with pd.ExcelWriter(kehunotinxlspath, mode='a') as writer: nowstr = datetime.datetime.now().strftime("%Y%m%d") dfduibikehu.to_excel(writer, sheet_name=f"{nowstr}({dfduibikehu.shape[0]})") log.critical(f'客户档案需要更新,共有{dfduibikehu.shape[0]}个客户未包含:{kehunotin[:5]}…...,,输出至文件《{kehunotinxlspath}》') setcfpoptionvalue('everdata', 'dataraw', 'kehuhash', f"{nowhash}") else: log.warning(f'客户档案需要更新,共有{dfduibikehu.shape[0]}个客户未包含:{kehunotin[:5]}...,列表hash为{nowhash}') return False cnxp.close()
signal.signal(signal.SIGALRM, handle) # 设置信号和回调函数 signal.alarm(num) # 设置 num 秒的闹钟 # print('start alarm signal.') r = func(*args, **kwargs) # print('close alarm signal.') signal.alarm(0) # 关闭闹钟 return r else: r = func(*args, **kwargs) logstr = f"{sysstr}\t非linux系统,啥也没做。" log.warning(logstr) return r except RuntimeError as e123: logstr = f"{func}出现错误。\t{e123}" log.warning(logstr) callback() return to_do return wrap # %% [markdown] # ### after_timeout() # %% def after_timeout(): """ 超时后的处理函数