知识点1：SQL读取并创建dataframe
知识点2：python发送邮件
知识点3：dataframe中某列series取唯一值
知识点4：dataframe切片
知识点5：dataframe中如何应用apply函数
知识点6：函数的应用
下面例子是如何从数据库中取数，并使用dataframe运算，然后输出Excel并邮件通知对应用户
"""
参数字段：
1.账套
2.MRP版本号
3.人员编号
4.收件人邮箱地址
测试料号：4116020034
"""
from typing import Any, Union
import pandas as pd
import numpy as np
from pandas import Series, DataFrame
from pandas.core.generic import NDFrame
from sqlalchemy import create_engine
import sys
import datetime
import ssl
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header
from exchangelib import DELEGATE, Account, Credentials, Configuration, NTLM, Message, Mailbox, HTMLBody, FileAttachment
from exchangelib.protocol import BaseProtocol, NoVerifyHTTPAdapter
engine = create_engine("oracle://%s:%s@xxxxx" % (sys.argv[1], sys.argv[1]), encoding='utf-8', echo=False)
g_path = "/u1/out/%s_%s_cmrr510.xlsx" % (sys.argv[3], sys.argv[2])
print('--------------需求数据--------------')
# 读取需求表
l_sql = "SELECT mss01, mss03, SUM(mss041 + mss043 + mss044 - mss053) xqqty, 0 dcqty, mss_v " \
"FROM mss_file WHERE mss_v = \'%s\' AND mss01 NOT LIKE 'A%%' AND mss01 IN (SELECT DISTINCT mst01 " \
"FROM MST_FILE a WHERE MST_V = \'%s\' AND EXISTS (SELECT 1 FROM PMN_FILE B WHERE A.MST06 = B.PMN01 " \
"AND A.MST061 = B.PMN02)) GROUP BY mss01, mss03, mss_v having SUM(mss041 + mss043 + mss044 - mss053) >0" \
" ORDER BY mss01, mss03" % (sys.argv[2], sys.argv[2])
# print(l_sql)
data_xq = pd.read_sql(l_sql, engine)
# print(data_xq.head(5))
# data_xq = data_xq.rename(columes = {})
print('--------------需求数据--------------')
print('--------------供应数据--------------')
# 读取供应表
l_sql = "SELECT mst01,mst03,mst06,mst061,mst08 gyqty,0 dcqty,mst_v FROM mst_file a WHERE mst_v = \'%s\' AND " \
"EXISTS (SELECT 1 FROM PMN_FILE B WHERE A.MST06 = B.PMN01 AND A.MST061 = B.PMN02) AND mst05 = '63' order by mst01,mst03" % (
sys.argv[2])
data_gy = pd.read_sql(l_sql, engine)
print(data_gy.head(5))
print('--------------供应数据--------------')
print('--------------库存数据--------------')
# 读取库存数据
l_sql = "SELECT mss01,SUM(mss051+mss052) kcqty,0 dcqty,mss_v FROM mss_file WHERE mss_v = \'%s\' and mss01 in " \
"(SELECT DISTINCT mst01 FROM MST_FILE a WHERE MST_V = \'%s\' AND EXISTS (SELECT 1 FROM PMN_FILE B WHERE A.MST06 = B.PMN01 AND A.MST061 = B.PMN02))" \
" GROUP BY mss01,mss_v HAVING SUM(mss051+mss052) > 0" % (sys.argv[2], sys.argv[2])
data_kc = pd.read_sql(l_sql, engine)
print('--------------库存数据--------------')
print('--------------循环供应物料进行逻辑处理--------------')
# ---定义对冲列表
dc_list = []
# 定义库存对冲函数
# 逻辑：循环需求数据，根据库存数量去对冲需求数量，然后更新库存对冲数量和需求对冲数量
def kcdc(item):
list_xq_index = data_xq[data_xq['mss01'] == item].index.tolist() # 检索出需求dataframe中的index存起，多个时距日多行数据
list_kc_index = data_kc[data_kc['mss01'] == item].index.tolist() # 检索出库存dataframe中的index存起，理论就一行
if len(list_kc_index) == 0:
return
for i in range(len(data_xq[data_xq['mss01'] == item])):
l_xqsyl = data_xq.iloc[list_xq_index[i]]['xqqty'] - data_xq.iloc[list_xq_index[i]]['dcqty'] # 剩余的需求量
if (l_xqsyl > 0): # 如果选中的data_xq行的需求量大于0，则进行处理
l_kcsyl = data_kc.iloc[list_kc_index[0]]['kcqty'] - data_kc.iloc[list_kc_index[0]]['dcqty'] # 剩余的库存量
if (l_xqsyl >= l_kcsyl): # 如果当前的需求数量大于等于库存数量
data_kc.loc[list_kc_index[0], 'dcqty'] = data_kc.loc[list_kc_index[0], 'kcqty']
data_xq.loc[list_xq_index[i], 'dcqty'] = data_xq.loc[list_xq_index[i], 'dcqty'] + l_kcsyl
else:
data_kc.loc[list_kc_index[0], 'dcqty'] = data_kc.loc[list_kc_index[0], 'dcqty'] + l_xqsyl
data_xq.loc[list_xq_index[i], 'dcqty'] = data_xq.loc[list_xq_index[i], 'xqqty']
def podc(item):
list_xq_index = data_xq01[data_xq01['mss01'] == item].index.tolist() # 检索出需求dataframe中的index存起，多个时距日多行数据
for i in range(len(data_xq01[data_xq01['mss01'] == item])):
l_xqsyl = data_xq01.iloc[list_xq_index[i]]['xqqty'] - data_xq01.iloc[list_xq_index[i]]['dcqty'] # 剩余的需求量
data_gy01 = data_gy[(data_gy['mst01'] == item) & (data_gy['gyqty'] > data_gy['dcqty'])]
for index, row in data_gy01.iterrows(): # 筛选对应供应物料的行进行循环
l_gysyl = row['gyqty'] - row['dcqty']
if (l_xqsyl > l_gysyl):
data_gy.loc[index, 'dcqty'] = data_gy.loc[index, 'gyqty']
data_xq01.loc[list_xq_index[i], 'dcqty'] = data_xq01.loc[list_xq_index[i], 'dcqty'] + l_gysyl
l_xqsyl = l_xqsyl - l_gysyl
list_dc = [data_gy.loc[index, 'mst06'], data_gy.loc[index, 'mst061'],
data_xq01.iloc[list_xq_index[i]]['mss03'], l_gysyl]
dc_list.append(list_dc)
continue
else:
data_gy.loc[index, 'dcqty'] = data_gy.loc[index, 'dcqty'] + l_xqsyl
data_xq01.loc[list_xq_index[i], 'dcqty'] = data_xq01.loc[list_xq_index[i], 'xqqty']
list_dc = [data_gy.loc[index, 'mst06'], data_gy.loc[index, 'mst061'],
data_xq01.iloc[list_xq_index[i]]['mss03'], l_xqsyl]
dc_list.append(list_dc)
l_xqsyl = 0
break
# ---首先将库存库存对冲掉，得到净需求
list01 = data_xq['mss01'].unique()
for item in list01:
kcdc(item) # 利用库存对冲当前物料需求
data_xq01 = data_xq[data_xq['xqqty'] > data_xq['dcqty']] # 重新抓取净需求
data_xq01.reset_index(inplace=True)
# print(data_xq01.head(5))
data_xq01 = data_xq01.iloc[:, 1:]
print(data_xq01.head(5))
# ---其次用得到的净需求使用在对外订单进行对冲
list02 = data_xq01['mss01'].unique()
for item in list02:
podc(item) # 利用PO进行需求对冲
# print(dc_list)
data_res = pd.DataFrame(dc_list, columns=['pmn01', 'pmn02', 'jydate', 'qty'])
data_res = data_res.iloc[:, 0:3]
print(data_res.head(5))
data_res01 = data_res.groupby(['pmn01', 'pmn02']).min()
data_res01 = data_res01.reset_index()
data_res01['mss_v'] = sys.argv[2]
print(data_res01.head(5))
# --逻辑如下：
# data_res01为对冲的采购单，在供应里面，但是未对冲的采购单需要取消，对冲表的采购单行需要和供应表对应的采购单行比较交期，
# 如果对冲表交期在供应表之前,则建议措施应该为提前，相等则不变，之后则为延后
# 1.提前 2.延后 3.取消 4.保持
jy_list = []
for index, row in data_gy.iterrows():
data_res02 = data_res01[(data_res01['pmn01'] == row['mst06']) & (data_res01['pmn02'] == row['mst061'])]
if len(data_res02) == 0:
list_jy = [row['mst06'], row['mst061'], 3, row['mst03']]
jy_list.append(list_jy)
else:
if (data_res02['jydate'].iloc[0] < row['mst03']): # 提前
list_jy = [row['mst06'], row['mst061'], 1, data_res02['jydate'].iloc[0]]
jy_list.append(list_jy)
elif (data_res02['jydate'].iloc[0] == row['mst03']): # 保持
list_jy = [row['mst06'], row['mst061'], 4, data_res02['jydate'].iloc[0]]
jy_list.append(list_jy)
else: # 延后
list_jy = [row['mst06'], row['mst061'], 2, data_res02['jydate'].iloc[0]]
jy_list.append(list_jy)
# print(jy_list)
data_res02 = pd.DataFrame(jy_list, columns=['pmn01', 'pmn02', 'jycs', 'jydate'])
data_res02['mss_v'] = sys.argv[2]
def infoadd(df):
l_sql = "select pmm04,pmm09,pmc03,pmn04,ima02,ima021,pmn20,pml01,pml02,ima35,pmn33,pmn35,gen02 from pmn_file " \
" left join ima_file on pmn04 = ima01 left join pml_file on (pmn24 = pml01 and pmn25 = pml02), pmm_file " \
" left join pmc_file on pmm09 = pmc01 left join gen_file on pmm12 = gen01 " \
" where pmm01 = pmn01 and pmn01 = \'%s\' and pmn02 = \'%s\'" % (df['pmn01'], df['pmn02'])
result = engine.execute(l_sql)
return result.fetchall()[0]
def add_pmk12(x):
l_sql = "select gen02 from pmk_file left join gen_file on gen01 = pmk12 where pmk01 = \'%s\'" % (x)
result = engine.execute(l_sql)
res = result.fetchall()
if len(res) > 0:
return res[0]
else:
return '--'
def add_jyjhdate(df):
l_sql = "select nvl(ima48,0),nvl(ima49,0),nvl(ima491,0) from ima_file where ima01 = \'%s\'" % (df['pmn04'])
result = engine.execute(l_sql)
res = result.fetchall()[0]
n = res[1] + res[2]
return df['jydate'] - datetime.timedelta(days=n)
data_res02[['pmm04', 'pmm09', 'pmc03', 'pmn04', 'ima02', 'ima021', 'pmn20', 'pml01', 'pml02', 'ima35', 'pmn33', 'pmn35',
'cgy']] \
= data_res02[['pmn01', 'pmn02']].apply(infoadd, axis=1, result_type='expand')
data_res02['qgy'] = data_res02['pml01'].apply(add_pmk12)
data_res02['jyjhdate'] = data_res02[['pmn04', 'jydate']].apply(add_jyjhdate, axis=1, result_type='expand')
data = data_res02[
['pmn01', 'pmn02', 'pmm04', 'pmm09', 'pmc03', 'qgy', 'cgy', 'pmn04', 'ima02', 'ima021', 'pmn20', 'pml01', 'pml02',
'ima35', 'pmn33', 'jyjhdate', 'pmn35', 'jydate', 'jycs']]
# 汉化建议措施
def jycs_hf(x):
if x == 1:
return '提前'
elif x == 2:
return '延后'
elif x == 3:
return '取消'
else:
return '保持'
data['jycs_zh'] = data['jycs'].apply(jycs_hf)
data = data[
['pmn01', 'pmn02', 'pmm04', 'pmm09', 'pmc03', 'qgy', 'cgy', 'pmn04', 'ima02', 'ima021', 'pmn20', 'pml01', 'pml02',
'ima35', 'pmn33', 'jyjhdate', 'pmn35', 'jydate', 'jycs_zh']]
data.columns = ['采购单号', '项次', '下单日期', '供应商编号', '供应商简称', '请购员', '采购员', '物料编号', '品名', '规格', '采购数量', '请购单号', '项次', '仓库编号',
'原始交货日期', '建议交货日期',
'原始到库日期', '建议到库日期', '建议措施']
data.to_excel(g_path)
print('--------------循环供应物料进行逻辑处理--------------')
print('--------------邮件输出--------------')
def sendmail(l_path, l_touser):
# 定义收件人和发件人
sender = '[email protected]'
receivers = l_touser
# 创建一个带附件的实例
message = MIMEMultipart()
# message['From'] = Header("ERP", 'utf-8')
# message['To'] = Header("收件人", 'utf-8')
subject = 'OPO交期建议表'
message['Subject'] = Header(subject, 'utf-8')
# 邮件正文内容
message.attach(MIMEText('详细内容见附件', 'plain', 'utf-8'))
# 构造附件1，传送当前目录下的 test.txt 文件
att1 = MIMEText(open(l_path, 'rb').read(), 'base64', 'utf-8')
att1["Content-Type"] = 'application/octet-stream'
# 这里的filename可以任意写，写什么名字，邮件中显示什么名字
att1["Content-Disposition"] = 'attachment; filename="opo_advise_report.xlsx"'
message.attach(att1)
# smtpObj = smtplib.SMTP('email.keboda.com')
smtpObj = smtplib.SMTP('172.16.999.999')
smtpObj.sendmail(sender, receivers, message.as_string())
print('Send Suc')
sendmail(g_path, sys.argv[4])
print('--------------邮件输出--------------')
标签：index,dcqty,list,发送,print,xq,data,pandas,邮件 From： https://www.cnblogs.com/smarttony/p/16710794.html