251 lines
9.5 KiB
Python
251 lines
9.5 KiB
Python
'''
|
|
Created on September 12, 2016
|
|
|
|
Interface ACF "Inventory Shipped" transmission.
|
|
For now I think I'm going to insert 08 transactions into TXRYBW10; creating rolls in Jomar and putting them
|
|
right into the ACF drop shipment warehouse.
|
|
|
|
@author: Wes
|
|
'''
|
|
import csv, logging, sys, os, re
|
|
from sqlalchemy import create_engine
|
|
from sqlalchemy.ext.declarative import declarative_base
|
|
from sqlalchemy.orm import sessionmaker
|
|
import pyodbc
|
|
|
|
TEST_MODE = False
|
|
|
|
# @TODO: Could probably do with making this pattern a little more discerning for the portion preceding the '/'
|
|
PROD_ROLL_PAT = ".+[\/]\d+"
|
|
|
|
#Assign the column indexes to descriptive variables; makes the code below a bit easier to follow.
|
|
CUST_ID = 0
|
|
ROLL_NUMBER = 1
|
|
SHIP_TO_ADDR_NAME = 2
|
|
CONTRACT_NUMBER = 3
|
|
STYLE_NAME = 4
|
|
STYLE_DESC = 5
|
|
DATE_RECEIVED = 6
|
|
DATE_COMPLETED = 7
|
|
DUE_DATE = 8
|
|
YARDS_RECEIVED = 9
|
|
YARDS_FINISHED = 10
|
|
ACF_SHIP_DATE = 11
|
|
SHIP_METHOD = 12
|
|
PICK_BATCH = 13
|
|
TRACKING = 14
|
|
UPS_COST = 15
|
|
CUST_FINISH_NO = 16
|
|
CUST_PO_NO = 17
|
|
|
|
logger = None
|
|
LOG_LEVEL = logging.DEBUG
|
|
#engine = create_engine("mssql+pyodbc://jsisbprod", echo=False)
|
|
engine = create_engine("mssql+pyodbc://jTest", echo=False)
|
|
Base = declarative_base(engine)
|
|
metadata = Base.metadata
|
|
|
|
########################################################################
|
|
class TXRYBW10(Base):
|
|
__tablename__ = 'TXRYBW10'
|
|
__table_args__ = {'autoload':True}
|
|
class TXUYUV00(Base):
|
|
__tablename__ = 'TXUYUV00' #Order Header
|
|
__table_args__ = {'autoload':True}
|
|
class TXUYUF01(Base):
|
|
__tablename__ = 'TXUYUF01' #Order Detail
|
|
__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
|
|
#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)
|
|
|
|
def queryTICKETFL(uNum, ticket, aPatt):
|
|
connection = pyodbc.connect(
|
|
driver='{iSeries Access ODBC Driver}',
|
|
system='SUNBURY',
|
|
uid=os.environ['AS400_UID'],
|
|
pwd=os.environ['AS400_PWD'])
|
|
c1 = connection.cursor()
|
|
ret = []
|
|
|
|
c1.execute("SELECT LINE#, PATNAM, COLOR# FROM STMDATA.TICKETFL WHERE ORDERA = '" + uNum + "' AND TICK# = " + ticket)
|
|
for row in c1:
|
|
for d in row:
|
|
ret.append(d)
|
|
c1.close()
|
|
|
|
#Verify that the pattern name ACF transmitted matches our records and return if so
|
|
if(len(ret) > 0 and ret[1].strip() == aPatt):
|
|
return ret
|
|
else:
|
|
return []
|
|
|
|
def backToSunbury(customerRoll, record):
|
|
global logger
|
|
# @TODO: Method stub for when we eventually do need to process these.
|
|
if(customerRoll):
|
|
logger.debug('Ignoring roll that did not drop ship: ' + record[ROLL_NUMBER])
|
|
else:
|
|
logger.debug('Ignoring non-production roll number: ' + record[ROLL_NUMBER])
|
|
|
|
def shippedToCust(customerRoll, record):
|
|
global logger
|
|
# @TODO: Method stub for when we eventually do need to process these.
|
|
if(customerRoll):
|
|
pass
|
|
else:
|
|
logger.debug('Ignoring non-production roll number: ' + record[ROLL_NUMBER])
|
|
|
|
def createInventoryTransaction(jOrder, jLine, jItem, roll, yards):
|
|
jLot = None #Optional we don't use lot numbers for rolls, could maybe throw YYYYMMdd in here to tie the jomar rolls to the tranmission.
|
|
|
|
i = TXRYBW10()
|
|
i.RWAPL = '00'
|
|
i.RWSPL = '00'
|
|
i.RWSLA = 'SO'
|
|
i.RWPLT = '00'
|
|
i.RWBNR = '001'
|
|
i.RWALA = 'SO'
|
|
i.RWBEW = '08' #Transaction type. '08' = Production receipt. This creates a new roll
|
|
|
|
i.RWALN = jOrder
|
|
i.RWSLN = jOrder
|
|
i.RWAFN = jLine
|
|
i.RWSFN = jLine
|
|
|
|
i.RWANR = jItem
|
|
i.RWCHA = jLot
|
|
i.RWRNR = roll
|
|
i.RWPGLG = yards
|
|
i.RWPLG = yards
|
|
i.RWPWG = 0 #This is a weight column. What to do about this I wonder?
|
|
i.RWTWG = 0 #This is a weight column. This should equal RWPWG
|
|
|
|
#All rolls process by this script are going into ACF drop ship warehouse for now
|
|
i.RWLOC = '11' #Warehouse
|
|
i.RWLPL = '' #Bin number. ACF warehouse is not bin tracked so ''
|
|
|
|
return i
|
|
|
|
def processACF(inFile):
|
|
global logger
|
|
os.chdir('D:\FTP')
|
|
initializeLogger('ACF_interface')
|
|
logger.info('****************************************')
|
|
session = loadSession()
|
|
prodRollPat = re.compile(PROD_ROLL_PAT)
|
|
rawLines = []
|
|
|
|
logger.info('Loading: ' + inFile)
|
|
try:
|
|
f = open(inFile, 'r')
|
|
reader = csv.reader(f)
|
|
for row in reader:
|
|
rawLines.append(row)
|
|
except IOError:
|
|
logger.fatal('Unable to load file. Exiting...')
|
|
return
|
|
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...')
|
|
# @TODO: Figure out naming convention for these so we can save them in \OLD\ACF
|
|
#logger.info('Moving ' + inFile + ' to D:\\FTP\\OLD\\KF\\')
|
|
#os.rename(inFile, 'OLD\\KF\\' + fileName)
|
|
sys.exit("Nothing to process.")
|
|
|
|
#Main processing loop
|
|
for line in rawLines:
|
|
#There ID for us is 'ST' evidently, we shouldn't process anything that isn't ST. Just in case...
|
|
if not line[CUST_ID] == 'ST':
|
|
logger.warn('Ignoring non-ST row...')
|
|
|
|
#Check data in "Roll Number" column to see if it's a production roll
|
|
#Production pieces should be in "ORDER#/ROLL#" format
|
|
if not(prodRollPat.match(line[ROLL_NUMBER]) == None):
|
|
|
|
#Process only rolls that drop shipped
|
|
#This is kinda hacky. Just looking for 'Sunbury' in the ship-to name...
|
|
if not 'sunbury' in line[SHIP_TO_ADDR_NAME].lower():
|
|
logger.info('**********')
|
|
as400Order, rollNumber = line[ROLL_NUMBER].split('/')
|
|
jOrder = None
|
|
jLine = None
|
|
jItem = None
|
|
logger.info('ACF drop shipped roll ' + rollNumber + ' to customer. Transferring it to warehouse 11...')
|
|
|
|
#Translate AS400 order number to Jomar Order number
|
|
logger.debug('SELECT * FROM TXUYUV00 WHERE TXUYUV00.UBNR = \'001\' AND TXUYUV00.UPLT = \'00\' AND TXUYUV00.UBLA = \'SO\' AND (TXUYUV00.URBPO = \'' + as400Order + '\' OR TXUYUV00.URBPO = \'' + as400Order[1:] + '\');')
|
|
q = session.query(TXUYUV00).order_by(TXUYUV00.UBLN).filter(TXUYUV00.UBNR == '001', TXUYUV00.UPLT == '00', TXUYUV00.UBLA == 'SO', ((TXUYUV00.URBPO == as400Order) | (TXUYUV00.URBPO == as400Order[1:])))
|
|
if(q.count() == 1):
|
|
for r in q:
|
|
jOrder = r.UBLN
|
|
logger.info('AS400 Order: ' + as400Order + ' --> Jomar Order: ' + jOrder)
|
|
else:
|
|
logger.error('Could not locate Jomar order for: ' + as400Order + ' skipping...')
|
|
continue
|
|
|
|
ticketflInfo = queryTICKETFL(as400Order, rollNumber, line[STYLE_DESC])
|
|
if(len(ticketflInfo) == 0):
|
|
logger.error('Failed to look up ticket in AS400. Skipping...')
|
|
continue
|
|
as400Line = ticketflInfo[0]
|
|
colorNum = ticketflInfo[2]
|
|
|
|
#Look up order line in Jomar using Jomar order number + AS400 line number
|
|
logger.debug('SELECT VLFN, VANR, XFLX33, XFLX34 FROM TXUYUF01 LEFT JOIN TXMYAT03 ON TXUYUF01.VBNR = TXMYAT03.XBNR AND TXUYUF01.VPLT = TXMYAT03.XPLT AND TXUYUF01.VANR = TXMYAT03.XANR WHERE TXUYUF01.VBNR = \'001\' AND TXUYUF01.VPLT = \'00\' AND TXUYUF01.VBLA = \'SO\' AND TXUYUF01.VBLN = \'' + jOrder + '\' AND TXUYUF01.VPLIN = ' + str(as400Line) + ';')
|
|
q = session.query(TXUYUF01,ItemInfo).order_by(TXUYUF01.VBLN).filter(TXUYUF01.VBNR == ItemInfo.XBNR, TXUYUF01.VPLT == ItemInfo.XPLT, TXUYUF01.VANR == ItemInfo.XANR).filter(TXUYUF01.VBNR == '001', TXUYUF01.VPLT == '00', TXUYUF01.VBLA == 'SO', TXUYUF01.VBLN == jOrder, TXUYUF01.VPLIN == as400Line)
|
|
if(q.count() == 1):
|
|
for r in q:
|
|
jLine = r[0].VLFN #Jomar Line Number
|
|
jItem = r[0].VANR #Jomar Item Number
|
|
logger.info('AS400 Order Line: ' + str(as400Line) + ' --> Jomar Line: ' + str(jLine))
|
|
logger.info('Jomar Item Number: ' + jItem)
|
|
|
|
#Create inventory transaction here
|
|
print createInventoryTransaction(jOrder, jLine, jItem, rollNumber, line[YARDS_FINISHED])
|
|
else:
|
|
backToSunbury(True, line)
|
|
else:
|
|
if not 'sunbury' in line[SHIP_TO_ADDR_NAME].lower():
|
|
#Drop shipped
|
|
shippedToCust(False, line)
|
|
else:
|
|
#Back to Sunbury
|
|
backToSunbury(False, line)
|
|
|
|
if __name__ == '__main__':
|
|
if(len(sys.argv) < 2):
|
|
print 'No file name specified!'
|
|
else:
|
|
processACF(sys.argv[1])
|