# Create json interface file using mapping rules from idocmapin.txt import os import sys import time import datetime import mysql.connector from collections import OrderedDict import default_setting # Get Current Path dir_path = os.path.dirname(os.path.realpath(__file__)) # Main Processing def main(): mapSegment = [] mapLength = [] mapMap = [] segments = [] outSegments = [] # Default Connection / System Settings defaults = default_setting.defaultSettings() # mySql Connector connection = mysql.connector.connect(user=defaults['dbuser'], password=defaults['dbpwd'],host=defaults['dbhost'],database=defaults['dbase']) cursor = connection.cursor() # Read IDOC Mapping Values file_path_map = os.path.join(dir_path, 'idocmapin.txt') iDocMap = open(file_path_map,"r") # for each line split out segment(eg.HDSUPPORDR), line length in chars.(eg.0039), segment field mapping. for line in iDocMap: line = line.replace('\n','') mapSegment.append(line.split(":")[0]) mapLength.append(line.split(":")[1]) mapMap.append(line.split(":")[2]) iDocMap.close() # Read IDOC Transaction # check for Document_reference if (len(sys.argv)>1): file_path = sys.argv[1] else: file_path = "" # Set path to files folder if (file_path == "" or file_path == None): file_path = os.path.join(dir_path, 'idoctranin.txt') iDocTran = open(file_path,"r") l = 0 outSegments.clear() lineType = "" segmentType = "" # Read lines from the input message file - for each line: for line in iDocTran: line = line.replace('\n','') line = line.replace(',',' ') offset = 0 # loop through all the characters in the message line to try & find a segment id. while len(line) > offset: segment = line[offset:offset+7] if (l == 0): if segment[0:6] == "TABNAM": return False # format segment from NMUK Message if segment[0:2] == "HD": # get the segment name from the input message (eg. SUPPORDR) segment = "HD"+line[offset+10:offset+18] segmentType = line[offset+10:offset+18] lineType = "HD" # print("SEGMENT:"+segment+":"+segmentType) elif segment[0:2] == "DT": segment = "DT"+segmentType; lineType = "DT" # print("SEGMENT:"+segment+":"+segmentType) elif segment[0:2] == "FT": segment = "FT"+segmentType; lineType = "FT" # print("SEGMENT:"+segment+":"+segmentType) # Look for matching Segment in Mapping List segmentFound = False i = 0 while i < len(mapSegment) and segmentFound == False: if len(segment) > 0: if segment.strip() == mapSegment[i].strip(): # print("MAPSEGMENT:"+segment+":"+mapSegment[i]) segments.append(segment) segmentFound = True firstPos = 0 lastPos = 0 detailStr = "" # Split-out segment line from input string segStr = line[offset:offset+int(mapLength[i])] # Split-out Fields Mapping into array mapDetail - 0=name;1=start_position (or +);2=length;3=JSON_name) mapDetail = mapMap[i].split(";") # print("MAPDETAIL:"+str(mapDetail)) d = 0 # Read through the mapping entries while d < len(mapDetail): # get field starting position in segment string if mapDetail[d+1] == "+": firstPos = lastPos else: firstPos = int(mapDetail[d+1]) # Check is Segment is to be included (not = '*' and not ending in ')') if len(mapDetail) > d+2 and mapDetail[d+3] != "*" and mapDetail[d+3][-1:] != ")": # if detailStr contains data - add ',' to the end before adding next segment if len(detailStr) > 0: detailStr = detailStr+"," # add formatted segment detail to comma-seperared output string - as JSON_name;message_value if len(segStr) > firstPos: if mapDetail[d+3] != "" and mapDetail[d+3][-1:] != ")": detailStr = detailStr+mapDetail[d+3]+";"+segStr[firstPos:firstPos+int(mapDetail[d+2])] else: detailStr = detailStr+mapDetail[d]+";"+segStr[firstPos:firstPos+int(mapDetail[d+2])] else: if mapDetail[d+3] != "" and mapDetail[d+3][-1:] != ")": detailStr = detailStr+mapDetail[d+3]+";"+segStr[firstPos:len(detailStr)] else: detailStr = detailStr+mapDetail[d]+";"+segStr[firstPos:len(detailStr)] # store the last position to use as the first position of the next segment if len(mapDetail) > d+2: lastPos = firstPos+int(mapDetail[d+2]) else: lastPos = len(mapDetail) # set index 'd' to the next set of segment mappings in array mapDetail d = d+4 # Store formatted Segments outSegments.append(detailStr) # increment the message offset to position to the start of the next message offset = offset+int(mapLength[i]) i = i+1 # if no matching segment was found offset to the next character in the message if segmentFound == False: offset = offset+1 # print("OUT:"+segment+":"+segmentType+":"+lineType+":"+str(outSegments)) if (lineType == "FT" or lineType == ""): # Create Message Format i = 0 e = 0 # Split-out Message Segments # print("OUT:",len(outSegments),outSegments) # return length = len(outSegments) for i in range(length): segments = outSegments[i].split(",") print(i,segments) if len(segments) > 0 and len(segments[0]) > 0: key = segments[0].split(";")[1] # only include pick order IDOC if (key == "E2LTORH" or key == "Z2LTORH" or key == "E2LTORI"): # Store required segments if (segments[0].split(";")[0].lower() == "segnam" and len(segments) > 1): for segment in segments: try: segName = segment.split(";")[0] segValue = segment.split(";")[1] except: break if (segName == "wareHouse"): wareHouse = segValue.strip() elif (segName == "customerReference"): customerReference = segValue.strip() elif (segName == "orderType"): orderType = segValue.strip() elif (segName == "pickType"): pickType = segValue.strip() elif (segName == "jisInd"): jisInd = segValue.strip() elif (segName == "expectedDate"): expectedDate = segValue.strip() elif (segName == "expectedTime"): expectedTime = segValue.strip() elif (segName == "rollingNumber"): rollingNumber = segValue.strip() elif (segName == "Desc"): description = segValue.strip() elif (segName == "lineNo"): lineNo = segValue.strip() elif (segName == "partNumber"): partNumber = segValue.strip() elif (segName == "expectedQty"): expectedQty = segValue.strip() elif (segName == "uom"): uom = segValue.strip() elif (segName == "BENUM"): benum = segValue.strip() elif (segName == "WEMPF"): wempf = segValue.strip() elif (segName == "WERKS"): werks = segValue.strip() elif (segName == "VLTYP"): vltyp = segValue.strip() elif (segName == "NLTYP"): nltyp = segValue.strip() elif (segName == "NLBER"): nlber = segValue.strip() elif (segName == "NLPLA"): nlpla = segValue.strip() elif (segName == "ABLAD"): ablad = segValue.strip() elif (segName == "VLPLA"): vlpla = segValue.strip() elif (segName == "tray"): tray = segValue.strip() elif (segName == "assy"): assy = segValue.strip() if (key == "E2LTORI"): dateTime = expectedDate[0:4]+"-"+expectedDate[4:6]+"-"+expectedDate[6:8]+" "+expectedTime[0:2]+":"+expectedTime[2:4]+":"+expectedTime[4:6] pickType = ""; if (jisInd == "D"): pickType = "XD" elif (pickType == "J"): pickType = "RP" elif (pickType == "R"): pickType = "R" dataQuery = "INSERT INTO document_segment (id,date_created,created_by,last_updated,last_updated_by,warehouse,customer_reference,order_type,pick_type,jis_ind,part_number,description,line_no,expected_date,expected_qty,uom,benum,wempf,werks,vltyp,nltyp,nlber,nlpla,ablad,vlpla,tray,assy,rolling_number)" dataQuery += " VALUES(DEFAULT,now(),'system',now(),'system','"+str(wareHouse)+"','"+str(customerReference)+"','"+str(orderType)+"','"+str(pickType)+"','"+str(jisInd)+"','"+str(partNumber)+"','"+str(description)+"',"+str(lineNo)+",'"+str(dateTime)+"',"+str(expectedQty)+",'"+str(uom)+"','"+str(benum)+"','"+str(wempf)+"','"+str(werks)+"','"+str(vltyp)+"','"+str(nltyp)+"','"+str(nlber)+"','"+str(nlpla)+"','"+str(ablad)+"','"+str(vlpla)+"','"+str(tray)+"','"+assy+"','"+rollingNumber+"')" try: cursor.execute(dataQuery) connection.commit() except Exception as err: print("mySql Error ",dataQuery,str(err)) # i = i+1 outSegments.clear() # print(wareHouse,customerReference,description,partNumber,benum,wempf,werks,lineNo,vltyp,expectedQty,uom,expectedDate,nltyp,nlber,nlpla) iDocTran.close() return True if __name__ == '__main__': main()