python调用SAP脚本下载库存报表MB52

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

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