477 lines
20 KiB
Python
477 lines
20 KiB
Python
'''
|
|
Created on Nov 25, 2014
|
|
|
|
To process 850 documents from Momentum.
|
|
|
|
@author: Wes
|
|
'''
|
|
from alert import send_alert_html
|
|
|
|
import datetime, time
|
|
import sys
|
|
import os, re, StringIO
|
|
import logging
|
|
|
|
from sqlalchemy import create_engine
|
|
from sqlalchemy.ext.declarative import declarative_base
|
|
from sqlalchemy.orm import sessionmaker
|
|
from sqlalchemy import or_
|
|
|
|
RECIPIENTS = ['admin@example.com']
|
|
SUBJECT = 'New Momentum 850 Processed.'
|
|
MESSAGE = None
|
|
|
|
TEST_MODE = False
|
|
|
|
logger = None
|
|
LOG_LEVEL = logging.DEBUG
|
|
DELIMITER = '~'
|
|
engine = create_engine("mssql+pyodbc://jsisbprod", echo=False)
|
|
Base = declarative_base(engine)
|
|
metadata = Base.metadata
|
|
|
|
########################################################################
|
|
class OrderHeader(Base):
|
|
__tablename__ = 'CSPEDI50H' #EDI 850 Header Table
|
|
__table_args__ = {'autoload':True}
|
|
class OrderDetail(Base):
|
|
__tablename__ = 'CSPEDI50D' #EDI 850 Detail Table
|
|
__table_args__ = {'autoload':True}
|
|
class ItemXref(Base):
|
|
__tablename__ = 'TXUYAT01' #Customer Item -> Sunbury Item reference table
|
|
__table_args__ = {'autoload':True}
|
|
class ItemInfo(Base):
|
|
__tablename__ = 'TXMYAT03'
|
|
__table_args__ = {'autoload':True}
|
|
class mg_850_997(Base):
|
|
__tablename__ = 'mg_850_997'
|
|
__table_args__ = {'autoload':True}
|
|
class TXUYKD00(Base):
|
|
__tablename__ = 'TXUYKD00'
|
|
__table_args__ = {'autoload':True}
|
|
#----------------------------------------------------------------------
|
|
|
|
def loadSession():
|
|
global engine
|
|
Session = sessionmaker(bind=engine)
|
|
session = Session()
|
|
return session
|
|
|
|
def initializeLogger(name='log'):
|
|
global logger
|
|
global MESSAGE
|
|
#logger = logging.getLogger(__name__)
|
|
logger = logging.getLogger(name)
|
|
if len(logger.handlers) > 0:
|
|
return logger
|
|
logger.setLevel(LOG_LEVEL)
|
|
handler = logging.FileHandler('D:\\FTP\\logs\\' + name + '.log')
|
|
handler.setLevel(LOG_LEVEL)
|
|
formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
|
|
handler.setFormatter(formatter)
|
|
logger.addHandler(handler)
|
|
|
|
#Also send logs > DEBUG to stdout
|
|
ch = logging.StreamHandler(sys.stdout)
|
|
ch.setLevel(logging.INFO)
|
|
formatter = logging.Formatter('%(asctime)s - %(levelname)s - %(message)s')
|
|
ch.setFormatter(formatter)
|
|
logger.addHandler(ch)
|
|
|
|
#Capture log messages in a string for e-mail alert
|
|
MESSAGE = StringIO.StringIO()
|
|
sh = logging.StreamHandler(MESSAGE)
|
|
sh.setLevel(logging.DEBUG)
|
|
sh.setFormatter(formatter)
|
|
logger.addHandler(sh)
|
|
|
|
'''
|
|
This method reproduces the results of the RPG code in MG850I lines 721 thru 751.
|
|
|
|
Basically creates a 10 digit Sunbury PO based on their PO# and a prefix we determine
|
|
based on their Sunbury customer number
|
|
|
|
Turns out that for whatever reason the decision was made to have Momentum only send the
|
|
last 5 digits of their PO and we rebuild it on our end.... why? Eventually I'm going to ask
|
|
them to just send their real PO.
|
|
'''
|
|
def getSunburyPONum(sentPONum, sunburyCust):
|
|
if(sunburyCust >= 339800 and sunburyCust <= 339899):
|
|
return (4009300000 + sentPONum)
|
|
elif(sunburyCust >= 431800 and sunburyCust <= 431899):
|
|
return (2009300000 + sentPONum)
|
|
elif(sunburyCust >= 523000 and sunburyCust <= 523099):
|
|
return (8209300000 + sentPONum)
|
|
|
|
#We don't want to blow things up if this fails so just insert what they sent to us
|
|
return sentPONum
|
|
|
|
'''
|
|
*** DEPRECATED DO NOT USE ***
|
|
*** Preserving this in case I feel like laughing at how bad it is in the future ***
|
|
|
|
This method reproduces the results of the RPG code in MG850I lines 670 thru 719.
|
|
|
|
Although this would be better if we just queried the server with the address to get
|
|
the ship to #.
|
|
'''
|
|
def getSunburyCustNum(sentVendorId, sentVendorAddr1, sentVendorCity):
|
|
global logger
|
|
addressList = ['17811 FITCH ST.', '2600 SURRETT DR.', '1450 EAST WALNUT AVE.', '4552 136TH AVE.', '1715 MEYERSIDE DR, UNIT #2', '655 BRIGHAM RD., STE 100']
|
|
cityList = ['IRVINE', 'ARCHDALE', 'FULLERTON', 'HOLLAND', 'MISSISSAUGA ON', 'GREENSBORO']
|
|
|
|
#Set up base account numbers
|
|
ret = 0
|
|
if(sentVendorId == 6975):
|
|
ret = 339899
|
|
elif(sentVendorId == 36):
|
|
ret = 431899
|
|
elif(sentVendorId == 7738):
|
|
ret = 523099
|
|
elif(sentVendorId == 2082):
|
|
ret = 406399
|
|
else:
|
|
logger.error('They\'ve transmitted a vendor number we haven\'t encountered before: ' + str(sentVendorId))
|
|
raise ValueError("They've transmitted a vendor number we haven't encountered before: " + str(sentVendorId))
|
|
|
|
#Calculate ship to portion of account number based on transmitted bill to address
|
|
if(sentVendorAddr1 == addressList[0] and sentVendorCity == cityList[0]):
|
|
return (ret - 99)
|
|
elif(ret == 339899):
|
|
if(sentVendorAddr1 == addressList[1] and sentVendorCity == cityList[1]):
|
|
return (ret - 98)
|
|
elif(ret == 431899):
|
|
if(sentVendorAddr1 == addressList[3] and sentVendorCity == cityList[3]):
|
|
return (ret - 98)
|
|
elif(ret == 523099):
|
|
if(sentVendorAddr1 == addressList[1] and sentVendorCity == cityList[1]):
|
|
return (ret - 98)
|
|
elif(sentVendorAddr1 == addressList[5] and sentVendorCity == cityList[5]):
|
|
return (ret - 98)
|
|
elif(sentVendorAddr1 == addressList[2] and sentVendorCity == cityList[2]):
|
|
return (ret - 97)
|
|
elif(sentVendorAddr1 == addressList[3] and sentVendorCity == cityList[3]):
|
|
return (ret - 96)
|
|
elif(sentVendorAddr1 == addressList[4] and sentVendorCity == cityList[4]):
|
|
return (ret - 95)
|
|
|
|
return ret #If the address transmitted is not recognized return 99 ship to code for 'WILL ADVISE'
|
|
|
|
'''
|
|
Attempting to rewrite the above so it's not garbage
|
|
'''
|
|
def getSunburyCustNum2(session, sentVendorId, sentVendorAddr1, sentVendorCity):
|
|
global logger
|
|
parent = ''
|
|
sentVendorAddr1 = sentVendorAddr1.upper()
|
|
sentVendorAddr1 = sentVendorAddr1.replace('DR.', 'DRIVE') #Probably the first of many such bandaids...
|
|
sentVendorAddr1 = sentVendorAddr1.replace('RD., STE 100', 'RD') #Bandiad number 2
|
|
|
|
if(sentVendorId == 6975):
|
|
parent = '3398'
|
|
elif(sentVendorId == 36):
|
|
parent = '4318'
|
|
elif(sentVendorId == 7738):
|
|
parent = '5230'
|
|
elif(sentVendorId == 2082):
|
|
parent = '4063'
|
|
else:
|
|
logger.error('They\'ve transmitted a vendor number we haven\'t encountered before: ' + str(sentVendorId))
|
|
raise ValueError("They've transmitted a vendor number we haven't encountered before: " + str(sentVendorId))
|
|
|
|
logger.debug('Attempting to look up ship to account. Parent: %s, Street: %s, City: %s ', parent, sentVendorAddr1, sentVendorCity)
|
|
q = session.query(TXUYKD00).filter(TXUYKD00.KKDR == parent, TXUYKD00.KNA3 == sentVendorAddr1, TXUYKD00.KORT == sentVendorCity)
|
|
if(q.count() == 1):
|
|
for row in q:
|
|
logger.debug('Ship to account found: %s', row.KKDN)
|
|
return row.KKDN
|
|
else:
|
|
logger.debug('Ship to account not found. Using: %s', (parent + '00'))
|
|
return (parent + '00')
|
|
|
|
def lookUpSunburyItem(session, patt, colo, ven):
|
|
global logger
|
|
k_pat = patt
|
|
k_col = re.compile(r'[^\d.]+').sub('', colo).zfill(4)
|
|
q = session.query(ItemXref,ItemInfo).order_by(ItemXref.UACCOL).filter(ItemXref.UACCNR == str(ven)[:-2], ItemInfo.XFLX33 == k_pat, ItemInfo.XFLX34 == k_col, ItemXref.UACANR == ItemInfo.XANR, or_(ItemXref.UACCOD == None, ItemXref.UACCOD != 'N'))
|
|
if(q.count() == 1):
|
|
for row in q:
|
|
logger.info('STM Pattern: ' + k_pat + ' Color: ' + k_col + ' --> Sunbury Item: ' + row[0].UACANR)
|
|
return row[0].UACANR
|
|
else:
|
|
logger.warn('Failed to lookup item number for: ' + k_pat + '-' + k_col + ' is the cross reference entered in Jomar?')
|
|
if(q.count() > 1):
|
|
for r in q:
|
|
logger.debug(r)
|
|
return ''
|
|
|
|
def processMomentumPo(inFile):
|
|
global MESSAGE
|
|
os.chdir('D:\FTP')
|
|
initializeLogger('mg_850_interface')
|
|
logger.info('****************************************')
|
|
seq = '00000'
|
|
|
|
#Read transmission file into list
|
|
logger.info('Processing: ' + inFile)
|
|
f = open(inFile, 'r')
|
|
seq = inFile.split('_')[2].split('.')[0]
|
|
logger.info('Sequence Number: ' + seq)
|
|
lines = f.readlines()
|
|
f.close()
|
|
|
|
#Trim white space chars off the end of lines
|
|
for x in range(0, len(lines)):
|
|
lines[x] = lines[x].strip()
|
|
|
|
#Check number of lines parsed against number supposed to be transmitted
|
|
rec = lines[len(lines) - 1].split(DELIMITER)
|
|
if not(rec[0] in 'S'):
|
|
logger.error('Transmission file missing summary record..')
|
|
raise ValueError('Transmission file missing summary record..')
|
|
elif not((len(lines) - 1) == int(rec[1])):
|
|
logger.error('Number of records actually transmitted does not match what they said they transmitted..')
|
|
raise ValueError('Number of records actually transmitted does not match what they said they transmitted..')
|
|
else:
|
|
logger.info('They transmitted ' + rec[1] + ' lines and we received ' + str((len(lines) - 1)) + ' lines. Good.')
|
|
|
|
#Determine if file is prod, test, or invalid
|
|
if(lines[0] in 'P'):
|
|
logger.info('Production Transmission File')
|
|
elif(lines[0] in 'T'):
|
|
logger.info('Test Transmission File')
|
|
else:
|
|
logger.error('Invalid file: The first line should = "P" or "T"')
|
|
raise ValueError('Invalid file: The first line should = "P" or "T"')
|
|
|
|
#parse control record
|
|
logger.info('Parsing Control Record...')
|
|
rec = lines[1].split(DELIMITER)
|
|
if not(rec[0] in 'TP'):
|
|
logger.error('Control Record Corrupt/Missing!')
|
|
raise ValueError('Control Record Corrupt/Missing...')
|
|
|
|
sender_edi_qual = int(rec[1])
|
|
sender_edi_id = int(rec[2])
|
|
recvr_edi_qual = int(rec[3])
|
|
recvr_edi_id = int(rec[4])
|
|
transaction_date = datetime.datetime.strptime(rec[5], "%Y%m%d")
|
|
transaction_no = int(rec[6])
|
|
|
|
logger.debug('\t Sender EDI Qualifier: ' + str(sender_edi_qual))
|
|
logger.debug('\t Sender EDI ID Number: ' + str(sender_edi_id))
|
|
logger.debug('\tReceiver EDI Qualifier: ' + str(recvr_edi_qual))
|
|
logger.debug('\tReceiver EDI ID Number: ' + str(recvr_edi_id))
|
|
logger.debug('\t Transaction Date: ' + transaction_date.strftime('%m/%d/%Y'))
|
|
logger.debug('\t Transastion Control #: ' + str(transaction_no))
|
|
|
|
#########################BEGIN NEW LOOP#########################
|
|
logger.info('Opening database connection...')
|
|
session = loadSession()
|
|
orderHeaders = []
|
|
orderLines = []
|
|
po_997 = []
|
|
lineNum = 1
|
|
po_number = 0
|
|
for x in range(2, (len(lines) - 1)):
|
|
rec = lines[x].split(DELIMITER)
|
|
if not(rec[0] in ['H', 'D']):
|
|
logger.error('Encountered a non-header/detail record in body of transmission. Record: ' + str(x))
|
|
logger.error(lines[x])
|
|
raise ValueError('Record ' + str(x) + ' appears to be corrupt!')
|
|
if(rec[0] == 'H'):
|
|
#Parse Header Record
|
|
logger.info('Parsing Purchase Order Header...')
|
|
lineNum = 1
|
|
|
|
po_type = rec[1].strip()
|
|
vendor_id = int(rec[2])
|
|
po_number = int(rec[3])
|
|
po_997.append([transaction_no, rec[3].zfill(5)])
|
|
po_date = datetime.datetime.strptime(rec[4], "%Y%m%d")
|
|
bill_to_name = rec[5].strip()
|
|
bill_to_addr1 = rec[6].strip()
|
|
bill_to_addr2 = rec[7].strip()
|
|
bill_to_city = rec[8].strip()
|
|
bill_to_state = rec[9].strip()
|
|
bill_to_zip = rec[10].strip()
|
|
ship_to_name = rec[11].strip()
|
|
ship_to_addr1 = rec[12].strip()
|
|
ship_to_addr2 = rec[13].strip()
|
|
ship_to_city = rec[14].strip()
|
|
ship_to_state = rec[15].strip()
|
|
ship_to_zip = rec[16].strip()
|
|
ship_via = rec[17].strip()
|
|
pay_terms = rec[18].strip()
|
|
fob = rec[19].strip()
|
|
tax_exempt_no = rec[20].strip()
|
|
vendor_message = rec[21].strip() #Not used apparently
|
|
order_message = rec[22].strip()
|
|
express_order = rec[23].strip()
|
|
req_ship_date = datetime.datetime.strptime(rec[24], "%Y%m%d")
|
|
|
|
logger.debug('\t PO Type: ' + po_type)
|
|
logger.debug('\t Vendor ID: ' + str(vendor_id))
|
|
logger.debug('\t PO Number: ' + str(po_number))
|
|
logger.debug('\t PO Date: ' + po_date.strftime('%m/%d/%Y'))
|
|
logger.debug('\t Bill To Name: ' + bill_to_name)
|
|
logger.debug('\t Bill To Address 1: ' + bill_to_addr1)
|
|
logger.debug('\t Bill To Address 2: ' + bill_to_addr2)
|
|
logger.debug('\t Bill To City: ' + bill_to_city)
|
|
logger.debug('\t Bill To State: ' + bill_to_state)
|
|
logger.debug('\t Bill To Zip: ' + bill_to_zip)
|
|
logger.debug('\t Ship To Name: ' + ship_to_name)
|
|
logger.debug('\t Ship To Address 1: ' + ship_to_addr1)
|
|
logger.debug('\t Ship To Address 2: ' + ship_to_addr2)
|
|
logger.debug('\t Ship To City: ' + ship_to_city)
|
|
logger.debug('\t Ship To State: ' + ship_to_state)
|
|
logger.debug('\t Ship To Zip: ' + ship_to_zip)
|
|
logger.debug('\t Freight Carrier: ' + ship_via)
|
|
logger.debug('\t Payment Terms: ' + pay_terms)
|
|
logger.debug('\t FOB Point: ' + fob)
|
|
logger.debug('\t Tax Exempt Number: ' + tax_exempt_no)
|
|
logger.debug('\t Vendor Message: ' + vendor_message)
|
|
logger.debug('\t Order Message: ' + order_message)
|
|
logger.debug('\t Express Order Code: ' + express_order)
|
|
logger.debug('\t Requested Ship Date: ' + req_ship_date.strftime('%m/%d/%Y'))
|
|
|
|
#sunburyCust = getSunburyCustNum(vendor_id, ship_to_addr1, ship_to_city)
|
|
sunburyCust = getSunburyCustNum2(session, vendor_id, ship_to_addr1, ship_to_city)
|
|
#logger.info('Using sold-to account number: ' + str(sunburyCust))
|
|
logger.info('Using sold-to account number: ' + sunburyCust[:4])
|
|
logger.info('Using ship-to account number: ' + sunburyCust)
|
|
logger.info('PO Number: ' + str(getSunburyPONum(po_number, int(sunburyCust))))
|
|
|
|
#Create Object to be persisted to database
|
|
header = OrderHeader()
|
|
#Set up constant values in order header record
|
|
header.UBNR = '001' #Business Unit
|
|
header.UPLT = '00' #Plant #
|
|
header.ULOAD = 'N' #Flag to allow order to be processed in Jomar EDI workbench
|
|
header.UBLA = 'SO' #Jomar order type "SO" = Sales Order
|
|
header.UDAT = int(transaction_date.strftime('%Y%m%d')) #Transaction Date -> Order Date
|
|
header.FOB = ' ' #This customer doesn't transmit this information
|
|
header.UIHR = getSunburyPONum(po_number, int(sunburyCust)) #Will we continue to calculate the PO numbers in this way?
|
|
header.UIHS = int(po_date.strftime('%Y%m%d')) #PO date
|
|
#header.UKDN = (sunburyCust + 1) #Ship to customer #
|
|
header.UKDN = sunburyCust #Ship to customer #
|
|
header.UKDR = str(sunburyCust)[:4] #Bill to customer #
|
|
header.XTX5 = pay_terms #Payment terms
|
|
header.XTX6 = order_message #Jomar "notes" field; sticking order message here
|
|
header.URLRV = '0' #An ID column of sorts, in case the same PO is transmitted twice
|
|
header.UNA2 = ship_to_name
|
|
header.UNA3 = ship_to_addr1
|
|
header.UNA4 = ship_to_addr2
|
|
header.UORT = ship_to_city
|
|
header.USTA = ship_to_state[:2]
|
|
header.ULND = 'US'
|
|
header.UPLZ = ship_to_zip
|
|
if(po_type == 'ORIGINAL'):
|
|
header.FLX01 = '00'
|
|
|
|
orderHeaders.append(header)
|
|
elif(rec[0] == 'D'):
|
|
logger.info('Parsing PO Line Record...')
|
|
|
|
vendor_pattern = rec[1].strip()
|
|
vendor_color = rec[2]
|
|
vendor_finish = rec[3].strip()
|
|
prod_desc = rec[4].strip()
|
|
qty_ordered = float(rec[5])
|
|
unit_of_measure = rec[6].strip()
|
|
price_per_unit = float(rec[7])
|
|
comment_1 = rec[8].strip()
|
|
comment_2 = rec[9].strip()
|
|
comment_3 = rec[10].strip()
|
|
comment_4 = rec[11].strip()
|
|
comment_5 = rec[12].strip()
|
|
|
|
logger.debug('\t STM Pattern: ' + vendor_pattern)
|
|
logger.debug('\t STM Color: ' + str(vendor_color))
|
|
logger.debug('\t STM Finish: ' + vendor_finish)
|
|
logger.debug('\t Product Description: ' + prod_desc)
|
|
logger.debug('\t Quantity Ordered: ' + str(qty_ordered))
|
|
logger.debug('\t Units Priced By: ' + unit_of_measure)
|
|
logger.debug('\t Price per Unit: ' + str(price_per_unit))
|
|
logger.debug('\t Comment 1: ' + comment_1)
|
|
logger.debug('\t Comment 2: ' + comment_2)
|
|
logger.debug('\t Comment 3: ' + comment_3)
|
|
logger.debug('\t Comment 4: ' + comment_4)
|
|
logger.debug('\t Comment 5: ' + comment_5)
|
|
|
|
#Look up internal item number
|
|
i = prod_desc.split('-')[1]
|
|
s = lookUpSunburyItem(session, vendor_pattern, str(vendor_color), sunburyCust)
|
|
internal_item = s
|
|
#logger.info('Sunbury item number for ' + i + ' = ' + str(s))
|
|
|
|
oline = OrderDetail()
|
|
oline.VBNR = '001'
|
|
oline.VPLT = '00'
|
|
oline.VQUALT = '01'
|
|
oline.VUOM = 'YDS'
|
|
oline.VBLA = 'SO'
|
|
oline.VLFN = lineNum
|
|
oline.VMGS = qty_ordered
|
|
oline.VPR2 = price_per_unit
|
|
oline.VANR = internal_item
|
|
oline.VKDR = str(sunburyCust)[:4]
|
|
#oline.VKDL = (sunburyCust + 1)
|
|
oline.VKDL = sunburyCust
|
|
oline.VIHR = getSunburyPONum(po_number, int(sunburyCust))
|
|
oline.VFLX01 = vendor_pattern
|
|
oline.VFLX02 = vendor_color
|
|
oline.VRLRV = '0'
|
|
lineNum = lineNum + 1
|
|
orderLines.append(oline)
|
|
|
|
#Check for duplicate PO(s) already in database; increment URLRV/VRLRV accordingly if so
|
|
for h in orderHeaders:
|
|
dups = session.query(OrderHeader).filter(OrderHeader.UBNR == '001', OrderHeader.UPLT == '00', OrderHeader.UKDR == h.UKDR, OrderHeader.UIHR == h.UIHR).count()
|
|
if(dups > 0):
|
|
logger.warning('PO ' + str(h.UIHR) + ' already in 850 tables. Adding this anyway with sequence number ' + str(dups) + ' it may be deleted in Jomar if not needed.')
|
|
h.URLRV = str(dups)
|
|
for line in orderLines:
|
|
if(line.VIHR == h.UIHR):
|
|
line.VRLRV = str(dups)
|
|
|
|
#submit
|
|
logger.info('Writing records to database...')
|
|
for h in orderHeaders:
|
|
session.add(h)
|
|
for line in orderLines:
|
|
session.add(line)
|
|
|
|
for po in po_997:
|
|
c = mg_850_997()
|
|
c.trans_no,c.po = po
|
|
c.proc_date = time.strftime('%Y%m%d')
|
|
session.add(c)
|
|
|
|
if not(TEST_MODE):
|
|
session.commit()
|
|
else:
|
|
logger.warn('**RUNNING IN TEST MODE ROLLING BACK TRANSACTION**')
|
|
session.rollback()
|
|
|
|
session.close()
|
|
|
|
if not(TEST_MODE):
|
|
try:
|
|
logger.info('Sending e-mail alert...')
|
|
send_alert_html(RECIPIENTS, SUBJECT, attachments=[inFile], message=MESSAGE.getvalue())
|
|
except:
|
|
logger.error('Unable to send e-mail. Is the internet out?')
|
|
|
|
if not(TEST_MODE):
|
|
logger.info('Moving ' + inFile + ' to D:\\FTP\\OLD\\MG\\')
|
|
os.rename(inFile, 'D:\\FTP\\OLD\\MG\\' + inFile.split('\\')[-1])
|
|
|
|
MESSAGE.truncate(0)
|
|
|
|
if __name__ == '__main__':
|
|
if(len(sys.argv) < 2):
|
|
pass
|
|
else:
|
|
processMomentumPo(sys.argv[1])
|