def jiaoyankehuchanpin():
cnx = lite.connect(dbpathquandan)
dataokay(cnx)
df = pd.read_sql_query(
'select xiaoshoumingxi.商品全名 as 品名,xiaoshoumingxi.商品编号,product.* from xiaoshoumingxi left outer join product on '
'xiaoshoumingxi.商品全名 = product.商品全名 where product.商品全名 is null', cnx)
df.describe()
# print(df)
print(df.groupby(['品名']).count())
df = pd.read_sql_query(
'select xiaoshoumingxi.单位全名,customer.* from xiaoshoumingxi left outer join customer on '
'xiaoshoumingxi.单位全名 = customer.往来单位 where customer.往来单位 is null', cnx)
df = df.groupby('单位全名').sum()
df.describe()
print(df)
df = pd.read_sql_query('select * from xiaoshoumingxi', cnx)
df.info()
df.describe()
dfqc = df.drop_duplicates()
dfqc.info()
cnx.close()
def pinpaifenxido():
cnx = lite.connect(dbpathquandan)
dataokay(cnx)
readinifromnote()
namestr = 'brand'
cfpininote, cfpininotepath = getcfp('everinifromnote')
if cfpininote.has_option(namestr, 'brandcount'):
brandcount = cfpininote.getint(namestr, 'brandcount')
else:
brandcount = 5
fenbuorquyu = getinivaluefromnote('brand', 'fenbuorquyu')
pinpaifenxi(cnx, daysbefore=5, brandnum=brandcount, fenbu=fenbuorquyu)
cnx.close()
# dfp = details2db('商品进货明细表（2018.10.1-2018.10.31）.xlsx',
# '商品进货明细表（2018.10.1-2018.10.31）',
# ['产品名称', '经办人'],
# 'jinghuomingxi')
# writer = pd.ExcelWriter(str(dirmainpath / 'data' / '进货分析.xlsx'))
# dfp.to_excel(writer, sheet_name='商品进货记录', freeze_panes={1, 2})
# dfg = dfp.groupby(['产品名称', '单价'], as_index=False) \
# .apply(lambda t: t[t.日期 == t.日期.min()][['产品名称', '日期', '单价']]).sort_values(['产品名称', '日期'])
# print(dfg.shape[0])
# print(dfg.tail(10))
# dfg.to_excel(writer, sheet_name='进货价格变动记录', freeze_panes={1, 2})
# writer.close()
cnxx = lite.connect(dbpathquandan)
dataokay(cnxx)
desclitedb(cnxx)
# jiaoyankehuchanpin()
# for i in range(len(dfs)):
# dfs.loc[i, '成本单价'] = chengbenjia(dfs.iloc[i]['商品全名'], dfs.iloc[i]['日期'], dfgqc)
# descdb(dfs)
#
# dfgg = dfp.groupby(['产品名称'], as_index=False).apply(lambda t: t[t.日期 == t.日期.max()])\
# .sort_values(['日期'], ascending=False)
# dfgg.to_excel(writer, sheet_name='进货价格最新', freeze_panes={1, 2})
#
# writer.save()
# writer.close()
# customerweihu2systable()
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()