400 lines
14 KiB
Python
400 lines
14 KiB
Python
'''
|
|
Created on Sep 18, 2015
|
|
|
|
Interface a Kravet PO csv file into Jomar EDI tables
|
|
|
|
@author: Wes
|
|
'''
|
|
from alert import send_alert_html
|
|
|
|
import datetime, csv, logging, sys, os, re, StringIO
|
|
from itertools import groupby, tee
|
|
from sqlalchemy import create_engine
|
|
from sqlalchemy.ext.declarative import declarative_base
|
|
from sqlalchemy.orm import sessionmaker
|
|
from operator import itemgetter
|
|
|
|
RECIPIENTS = ['admin@example.com']
|
|
SUBJECT = 'New Kravet 850 Processed.'
|
|
MESSAGE = None
|
|
|
|
TEST_MODE = False
|
|
|
|
STM_ITEM_PATTERN = "[\s\S]+[*]\d+$"
|
|
KFI_ITEM_PATTERN = "\d+[.]\d+[.]\d+"
|
|
|
|
STREET = '1500 HIGHWAY 29 SOUTH'
|
|
CITY = 'ANDERSON'
|
|
|
|
PO_NUM = 0
|
|
LINE_NUM = 1
|
|
SHIPMENT_NUM = 2
|
|
VENDOR_NAME = 3
|
|
CUSTOMER_NUM = 4
|
|
ORDER_DATE = 5
|
|
SHIP_TO_NAME = 6
|
|
SHIP_1 = 7
|
|
SHIP_2 = 8
|
|
SHIP_CITY = 9
|
|
SHIP_STATE = 10
|
|
SHIP_ZIP = 11
|
|
SHIP_COUNTRY = 12
|
|
BILL_1 = 13
|
|
BILL_2 = 14
|
|
BILL_CITY = 15
|
|
BILL_STATE = 16
|
|
BILL_ZIP = 17
|
|
STM_ITEM = 18
|
|
FINISHES = 19
|
|
KRAVET_ITEM = 20
|
|
ORDERED_ITEM = 21
|
|
QTY = 22
|
|
UNITS = 23
|
|
UNIT_PRICE = 24
|
|
EXT_PRICE = 25
|
|
NOTES = 26
|
|
PAYMENT_TERMS = 27
|
|
FREIGHT_TERMS = 28
|
|
SHIP_VIA = 29
|
|
CURRENCY = 30
|
|
DESCRIPTION = 31
|
|
SIDEMARK = 34
|
|
|
|
logger = None
|
|
LOG_LEVEL = logging.DEBUG
|
|
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}
|
|
#----------------------------------------------------------------------
|
|
|
|
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)
|
|
|
|
'''
|
|
Most of the data we receive from Kravet needs to be "massaged" into something that's
|
|
actually usable. Trying to consolidate these "fixes" into one place...
|
|
|
|
For reference, the agreed upon format for the STM item field is:
|
|
|
|
PATTERN*COLOR
|
|
|
|
Where:
|
|
* PATTERN contains only letters, spaces, ', and -
|
|
* COLOR contains only digits
|
|
|
|
Examples:
|
|
What they sent:
|
|
KIRSTEN 73383*2015
|
|
MULBERRY 73151*COL.825
|
|
|
|
What they should have sent:
|
|
KIRSTEN*2015
|
|
MULBERRY*0825
|
|
'''
|
|
def massageSTM(fromKravet):
|
|
global logger
|
|
og = fromKravet
|
|
if not (fromKravet.count('*') == 1):
|
|
#logger.error("Unexpected format encountered for vendor item string...")
|
|
return fromKravet
|
|
|
|
fromKravet = re.sub("[^A-Za-z0-9*'\-\s]+", '', fromKravet) #Remove all special characters besides *, ', -, and spaces
|
|
|
|
#Today's kludge brought to you by: 'KIRSTEN 73383*2015'
|
|
i = fromKravet.index('*')
|
|
fromKravet = re.sub('[0-9]+', '', fromKravet[:i]).strip() + fromKravet[i:] #Remove any digits to the left of *
|
|
|
|
#The following bandaid was inspired by: 'MULBERRY 73151*COL.825'
|
|
i = fromKravet.index('*')
|
|
fromKravet = fromKravet[:i] + re.sub('[A-Za-z]+', '', fromKravet[i:]).strip() #Remove non-digits to the right of *
|
|
|
|
#Zero pad 3 digits colors while we're at it...
|
|
i = fromKravet.index('*') + 1
|
|
fromKravet = fromKravet[:i] + fromKravet[i:].zfill(4)
|
|
|
|
logger.debug('Massaging STM item column: %s --> %s', og, fromKravet)
|
|
return fromKravet
|
|
|
|
'''
|
|
|
|
'''
|
|
def massageKFI(fromKravet):
|
|
global logger
|
|
og = fromKravet
|
|
|
|
if(fromKravet.startswith('GWF-')):
|
|
fromKravet = fromKravet[4:]
|
|
|
|
logger.debug('Massaging KFI item column: %s --> %s', og, fromKravet)
|
|
return fromKravet
|
|
|
|
def lookUpSunburyItem(session, k_item, ven):
|
|
global logger
|
|
k_item = k_item.replace('#','') #Yet another bandaid fix for Kravet...
|
|
if(re.compile(STM_ITEM_PATTERN).match(k_item) == None):
|
|
logger.error('Kravet transmitted junk in the STM item column: ' + k_item)
|
|
return ''
|
|
|
|
k = k_item.split('*')
|
|
k_pat = k[0]
|
|
k_col = re.compile(r'[^\d.]+').sub('', k[1]).zfill(4)
|
|
q = session.query(ItemXref,ItemInfo).order_by(ItemXref.UACCOL).filter(ItemXref.UACCNR == ven, ItemInfo.XFLX33 == k_pat, ItemInfo.XFLX34 == k_col, ItemXref.UACANR == ItemInfo.XANR)
|
|
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:
|
|
session.close()
|
|
logger.warn('Failed to lookup item # for: ' + k_pat + '-' + k_col + ' is the cross reference entered in Jomar?')
|
|
return ''
|
|
#raise RuntimeError('Failed to lookup Sunbury item for STM Pattern: ' + k_pat + ' Color: ' + k_col + ' is the cross reference entered in Jomar?')
|
|
|
|
def processKravetPo(inFile):
|
|
global MESSAGE
|
|
os.chdir('D:\FTP')
|
|
initializeLogger('kf_850_interface')
|
|
logger.info('****************************************')
|
|
fileName = inFile.split('\\')[-1]
|
|
rawLines = []
|
|
session = loadSession()
|
|
|
|
logger.info('Loading: ' + inFile)
|
|
try:
|
|
f = open(inFile, 'r')
|
|
except IOError:
|
|
logger.fatal('Unable to load file. Did you specify the full path? Exiting...')
|
|
return
|
|
filename = inFile.split('\\')[-1]
|
|
|
|
try:
|
|
reader = csv.reader(f)
|
|
for row in reader:
|
|
rawLines.append(row)
|
|
finally:
|
|
f.close()
|
|
|
|
rawLines.pop(0) #Remove first line as it is just column headers
|
|
|
|
if(len(rawLines) == 0):
|
|
logger.warning('They sent us an empty file...')
|
|
logger.info('Moving ' + inFile + ' to D:\\FTP\\OLD\\KF\\')
|
|
os.rename(inFile, 'OLD\\KF\\' + fileName)
|
|
session.close()
|
|
return
|
|
#sys.exit("Nothing to process.")
|
|
|
|
rawLines.sort(key=itemgetter(0)) #Ensure raw data is sorted by PO number
|
|
purchaseOrders = groupby(rawLines, itemgetter(0)) #Create group by iterator based on PO number
|
|
|
|
for poNumber, lines in purchaseOrders:
|
|
logger.info('***')
|
|
logger.info('Processing purchase order ' + poNumber)
|
|
|
|
#Create a copy of the lines iterator so we can pull the first line of the PO now to fill in header info
|
|
lines, flb = tee(lines)
|
|
fl = flb.next()
|
|
|
|
header = OrderHeader()
|
|
#Order header constants
|
|
header.UBNR = '001'
|
|
header.UPLT = '00'
|
|
header.ULOAD = 'N'
|
|
header.UBLA = 'SO'
|
|
header.FLX01 = '00'
|
|
header.URLRV = '0' #An ID column, in case the same PO is transmitted twice both may be added to DB and sorted out in Jomar
|
|
|
|
header.UDAT = int(datetime.datetime.strptime(filename, "Kravet_to_vendor_%d-%b-%Y.csv").strftime('%Y%m%d')) #Transaction Date -> Order Date
|
|
logger.info('PO Date: ' + str(header.UDAT))
|
|
|
|
header.UIHS = int(datetime.datetime.strptime(fl[ORDER_DATE], "%d/%m/%Y").strftime('%Y%m%d'))
|
|
logger.info('Ordered On: ' + str(header.UIHS))
|
|
|
|
header.FOB = fl[FREIGHT_TERMS]
|
|
logger.info('FOB: ' + header.FOB)
|
|
|
|
header.XTX5 = fl[PAYMENT_TERMS]
|
|
logger.info('Payment terms: ' + header.XTX5)
|
|
|
|
header.UIHR = poNumber
|
|
|
|
if(fl[CUSTOMER_NUM] == 'KFI' or fl[CUSTOMER_NUM] == '3190'):
|
|
logger.info('Column 5 = "KFI" using customer number: 3190')
|
|
header.UKDR = '3190'
|
|
header.UKDN = '319000'
|
|
header.UNA2 = 'KRAVET FABRICS'
|
|
if(fl[SHIP_1].startswith(STREET) and fl[SHIP_CITY].startswith(CITY)):
|
|
header.UKDN = '319003'
|
|
elif(fl[CUSTOMER_NUM] == 'LJI' or fl[CUSTOMER_NUM] == '3191'):
|
|
logger.info('Column 5 = "LJI" using customer number: 3191')
|
|
header.UKDR = '3191'
|
|
header.UKDN = '319100'
|
|
header.UNA2 = 'LEE JOFA'
|
|
if(fl[SHIP_1].startswith(STREET) and fl[SHIP_CITY].startswith(CITY)):
|
|
header.UKDN = '319101'
|
|
elif(fl[CUSTOMER_NUM] == 'BRF' or fl[CUSTOMER_NUM] == '746'):
|
|
logger.info('Column 5 = "BRF" using customer number: 0746')
|
|
header.UKDR = '0746'
|
|
header.UKDN = '074600'
|
|
header.UNA2 = 'BRUNSCHWIG & FILS'
|
|
if(fl[SHIP_1].startswith(STREET) and fl[SHIP_CITY].startswith(CITY)):
|
|
header.UKDN = '074600'
|
|
elif(fl[CUSTOMER_NUM] == 'GIS'):
|
|
logger.info('Column 5 = "GIS" using customer number: 3194')
|
|
header.UKDR = '3194'
|
|
header.UKDN = '319400'
|
|
header.UNA2 = 'KRAVET FABRICS/GIS'
|
|
if(fl[SHIP_1].startswith(STREET) and fl[SHIP_CITY].startswith(CITY)):
|
|
header.UKDN = '319401'
|
|
elif(fl[CUSTOMER_NUM] == 'GSR'):
|
|
logger.info('Column 5 = "GSR" using customer number: 3194')
|
|
header.UKDR = '3194'
|
|
header.UKDN = '319400'
|
|
header.UNA2 = 'KRAVET FABRICS/GIS'
|
|
if(fl[SHIP_1].startswith(STREET) and fl[SHIP_CITY].startswith(CITY)):
|
|
header.UKDN = '319401'
|
|
else:
|
|
logger.error('Unknown Customer Code: ' + fl[CUSTOMER_NUM])
|
|
session.close()
|
|
raise ValueError('Unknown Customer Code: ' + fl[CUSTOMER_NUM])
|
|
|
|
if(len(fl[SHIP_TO_NAME].strip()) > 0):
|
|
header.UNA2 = fl[SHIP_TO_NAME]
|
|
header.UNA3 = fl[SHIP_1]
|
|
header.UNA4 = fl[SHIP_2]
|
|
header.UORT = fl[SHIP_CITY]
|
|
header.USTA = fl[SHIP_STATE]
|
|
header.ULND = fl[SHIP_COUNTRY]
|
|
header.UPLZ = fl[SHIP_ZIP]
|
|
|
|
line = 1
|
|
orderLines = []
|
|
for l in lines:
|
|
oline = OrderDetail()
|
|
oline.VBNR = '001'
|
|
oline.VPLT = '00'
|
|
oline.VQUALT = '01'
|
|
oline.VUOM = 'YDS'
|
|
oline.VBLA = 'SO'
|
|
oline.VLFN = line
|
|
logger.info('Order Line ' + str(line))
|
|
|
|
oline.VMGS = l[QTY]
|
|
logger.info('Quantity Ordered: ' + oline.VMGS + ' ' + l[UNITS] )
|
|
|
|
oline.VPR2 = l[UNIT_PRICE]
|
|
logger.info('Unit Prince: ' + oline.VPR2)
|
|
|
|
#Attempt to fix various common issues with the xref data sent from Kravet
|
|
stmItem = massageSTM(l[STM_ITEM])
|
|
kravetItem = massageKFI(l[KRAVET_ITEM])
|
|
|
|
oline.VANR = lookUpSunburyItem(session, stmItem, header.UKDR)
|
|
oline.VKDR = header.UKDR
|
|
oline.VKDL = header.UKDN
|
|
oline.VIHR = l[PO_NUM]
|
|
oline.VTXT1 = l[NOTES][:65]
|
|
logger.info('Notes: ' + oline.VTXT1)
|
|
|
|
#If possible parse Sunbury pattern and color and insert into database
|
|
if not(re.compile(STM_ITEM_PATTERN).match(stmItem) == None):
|
|
sItem = stmItem.split('*')
|
|
oline.VFLX01 = sItem[0]
|
|
oline.VFLX02 = sItem[1]
|
|
else:
|
|
logger.warn('STM item is in an unusual format: ' + stmItem)
|
|
|
|
#If possible parse Kravet pattern and color and insert into database
|
|
if not(re.compile(KFI_ITEM_PATTERN).match(kravetItem) == None):
|
|
kItem = kravetItem.split('.')
|
|
oline.VFLX03 = kItem[0]
|
|
oline.VFLX04 = kItem[1]
|
|
else:
|
|
logger.warn('Kravet item number is in an unusual format: ' + l[KRAVET_ITEM])
|
|
|
|
oline.VRLRV = '0' #ID column for adding duplicate POs to EDI tables
|
|
line = line + 1
|
|
orderLines.append(oline)
|
|
|
|
#Check for duplicate PO(s) already in database; increment URLRV/VRLRV accordingly if so
|
|
dups = session.query(OrderHeader).filter(OrderHeader.UBNR == '001', OrderHeader.UPLT == '00', OrderHeader.UKDR == header.UKDR, OrderHeader.UIHR == header.UIHR).count()
|
|
if(dups > 0):
|
|
logger.warn('PO ' + header.UIHR + ' already in 850 tables. Adding this anyway with sequence number ' + str(dups) + ' it may be deleted in Jomar if not needed.')
|
|
header.URLRV = str(dups)
|
|
for line in orderLines:
|
|
line.VRLRV = str(dups)
|
|
|
|
session.add(header)
|
|
for line in orderLines:
|
|
session.add(line)
|
|
|
|
if not TEST_MODE:
|
|
logger.info('Writing records to database...')
|
|
session.commit()
|
|
else:
|
|
logger.warn('**TEST MODE ENABLED ROLLING BACK TRANSACTIONS**')
|
|
session.rollback()
|
|
|
|
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\\KF\\')
|
|
os.rename(inFile, 'OLD\\KF\\' + fileName)
|
|
|
|
MESSAGE.truncate(0)
|
|
|
|
if __name__ == '__main__':
|
|
if(len(sys.argv) < 2):
|
|
print 'Must supply fully qualified path to csv file ex:'
|
|
print 'python kf_850.py D:\\FTP\\Kravet_to_vendor_25-MAY-2016.csv'
|
|
else:
|
|
processKravetPo(sys.argv[1])
|