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():
"""
超时后的处理函数
