import math
import os,sys, win32com.client
import clipboard
from pprint import pprint
import csv
def get_mb52(session, args = {}, plant = '0001'):
result = download_from_sap(session, args=args, plant = plant)
if not result: #no error from sap download
full_file_name = save_csv(session, 'mb52')
print('full_file_name=', full_file_name)
if full_file_name:
result = get_data_from_file(full_file_name)
return result
def download_from_sap(session, args={}, plant = '0001'):
"""args {'material':['0789','09980'], 'location':['R023','R024'], 'plant':'0001'}"""
if not args:
args = get_plant_args(plant, 'stock_param')
#plant = args.get('plant')
location_list = args.get('location')
material_list = args.get('material')
session.findById("wnd[0]/tbar[0]/okcd").Text = "/nmb52"
session.findById("wnd[0]").sendVKey(0)
if session.findById("wnd[0]/sbar").MessageType == "E":
msg = session.findById("wnd[0]/sbar").Text
print(msg)
return msg
field_id = "wnd[0]/usr/btn%_MATNR_%_APP_%-VALU_PUSH"
materials = os.linesep.join(material_list) #'\r\n'
clipboard.copy(materials)
session.findById(field_id).press()
session.findById("wnd[1]/tbar[0]/btn[16]").press()
session.findById("wnd[1]/tbar[0]/btn[24]").press()
session.findById("wnd[1]/tbar[0]/btn[8]").press()
session.findById("wnd[0]/usr/ctxtWERKS-LOW").Text = "0001" #设置工厂代码
if location_list:
locations = os.linesep.join(location_list) #'\r\n'
clipboard.copy(locations)
session.findById("wnd[0]/usr/btn%_LGORT_%_APP_%-VALU_PUSH").press()
session.findById("wnd[1]/tbar[0]/btn[16]").press() #删除之前的输入
session.findById("wnd[1]/tbar[0]/btn[24]").press()
session.findById("wnd[1]/tbar[0]/btn[8]").press()
session.findById("wnd[0]/usr/chkPA_SOND").Selected = True
session.findById("wnd[0]/usr/ctxtSO_SOBKZ-LOW").Text = "k"
session.findById("wnd[0]/usr/chkNOZERO").Selected = True
session.findById("wnd[0]/usr/chkNOVALUES").Selected = True
session.findById("wnd[0]/usr/chkNEGATIV").Selected = False
session.findById("wnd[0]/usr/radPA_FLT").Select()
session.findById("wnd[0]/usr/ctxtP_VARI").Text = "/MB52_xxx" #设置固定的输出格式layout
session.findById("wnd[0]/tbar[1]/btn[8]").press()
#there is no data for selection
if session.ActiveWindow.Name == "wnd[1]":
msg = session.findById("wnd[1]").PopupDialogText
print(msg)
session.findById("wnd[1]").sendVKey(0)
if msg == "There is no data for the selection":
return
session.findById("wnd[0]/tbar[1]/btn[45]").press()
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").select()
session.findById("wnd[1]/tbar[0]/btn[0]").press()
def get_data_from_file(file_name):
field_map = {
"Material": "item_code",
"SLoc": "location",
"S": "special_stock",
"Stck no.":"supplier",
"Unrestricted": "available_qty",
"Unrestr.": "available_qty",
"Qual.Insp.": "quality_insp",
"BUn": "uom"
}
data = []
with open(file_name, "r", newline='', encoding='utf-8') as csvfile:
rows = csv.reader(csvfile, delimiter = '\t')
for i, row in enumerate(rows):
if i<3 or not row:
continue
if i == 3:
field_index = {f:get_field_index(row, f) for f in field_map.keys()}
print('field index', field_index)
else:
doc = {}
for k, v in field_map.items():
index = field_index.get(k)
if index != -1:
doc[v] = row[index]
for f in ['available_qty','quality_insp']:
if doc.get(f):
doc[f] = doc[f].replace(',','').replace(" ","")
doc[f] = doc[f] and float(doc[f]) or 0
data.append(doc)
data = [[d.get('item_code'), d.get('location'), d.get('special_stock'), d.get('supplier'),
d.get('available_qty'), d.get('quality_insp'), d.get('uom')] for d in data]
pprint('get_data_from_file 2 records %s' % data[:2])
return data
def save_csv(session, tcode, file_folder=None):
if file_folder:
session.findById("wnd[1]/usr/ctxtDY_PATH").text = file_folder
else:
file_folder = session.findById("wnd[1]/usr/ctxtDY_PATH").text
file_name = f"{tcode}_{datetime.datetime.now():%y%m%d_%H%M%S}.csv"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").text = file_name
session.findById("wnd[1]/usr/ctxtDY_FILE_ENCODING").text = "4110" #UTF8 编码，默认0000中文会显示乱码
session.findById("wnd[1]/tbar[0]/btn[0]").press()
result = None
full_file_name = os.path.join(file_folder, file_name)
time.sleep(1)
for i in range(720):
if os.path.exists(full_file_name):
result = full_file_name
break
time.sleep(1)
return result
def get_field_index(row, field):
index = -1
for i,label in enumerate(row):
if field == label or (label and field == label.strip()): #truncate beginning and ending space
index = i
break
if index == -1: print("missing field %s in the layout" % field)
return index