655 lines
30 KiB
Python
655 lines
30 KiB
Python
'''
|
|
Finished Goods Interface - AS400 to Jomar Inventory Synchronization
|
|
|
|
Created on Jun 24, 2015
|
|
@author: Wes
|
|
|
|
PURPOSE:
|
|
Reads CSV files generated by the AS400 when inventory is scanned in shipping and creates
|
|
corresponding Jomar ERP inventory transactions to keep the two systems in sync.
|
|
|
|
INPUT:
|
|
CSV files in D:\INTERFACE\ named as400_jsi_#####.csv (where ##### is a sequential transaction number).
|
|
Each row represents a roll/piece with columns:
|
|
[0] ORDER - AS400 order number (e.g. U17435)
|
|
[1] LINE - AS400 line number
|
|
[2] TICKET - Roll/piece ticket number (unique identifier)
|
|
[3] FINISHED_YARDS
|
|
[4] INVOICED_YARDS
|
|
[5] BIN - Physical bin location
|
|
[6] WAREHOUSE - Warehouse code (SH, BG, B8, etc.)
|
|
[7] WEIGHT - Roll weight in lbs
|
|
[8] SPAT - Pattern code
|
|
[9] SCOL - Color code
|
|
|
|
Can be run two ways:
|
|
- No arguments: processes all CSV files in PATH
|
|
- Single file path argument: processes just that file (preferred when using the PowerShell
|
|
file watcher to avoid collisions between concurrent runs)
|
|
|
|
PROCESSING LOGIC (per row):
|
|
1. Validation checks (skip row to error list if any fail):
|
|
- Duplicate ticket within same transmission
|
|
- Invalid bin (HLD, or blank bin in SH warehouse)
|
|
- Finished yards <= 0
|
|
- Invoiced yards <= 0
|
|
|
|
2. Order/line lookup in Jomar:
|
|
- Query TXUYUV00 (order header) to map AS400 order -> Jomar order number
|
|
- Query TXUYUF01 (order detail) + TXMYAT03 (item info) to find Jomar line number
|
|
- Verify pattern and color match between AS400 and Jomar
|
|
|
|
3. Transaction generation based on roll state in Jomar (TXRYRT00 roll/tag master):
|
|
a. Roll does NOT exist in Jomar:
|
|
-> Create Production Receipt (08) transaction to add the roll
|
|
-> Sets warehouse/bin, yardage, weight, lot, NAFTA qualification
|
|
-> Pull orders (PULL_ORDERS list) get no order/line linkage
|
|
|
|
b. Roll ALREADY exists in Jomar:
|
|
- If shipped yards > 0: error (roll already shipped, can't modify)
|
|
- If yardage or weight differs from current values:
|
|
-> Create adjustment Production Receipt (08) with the delta
|
|
- If bin or warehouse differs:
|
|
-> Create Movement (06) transaction to relocate the roll
|
|
- If nothing differs: skip (log and ignore)
|
|
|
|
All transactions are written to TXRYBW10 (the finished goods interface table) and
|
|
processed immediately by Jomar.
|
|
|
|
ERROR HANDLING:
|
|
- Rows that fail validation or lookup are collected in eRows
|
|
- Error rows are written to D:\INTERFACE\ERRORS\ERRORS_{lot}.csv
|
|
- Error CSV is emailed to ROLL_ERROR_RECIPIENTS
|
|
- If a file with a lower transaction number is still unprocessed when a newer file arrives,
|
|
the script alerts TX_ERROR_RECIPIENTS and exits (prevents out-of-order processing)
|
|
|
|
OUTPUT:
|
|
- Processed CSV files are moved to D:\INTERFACE\PROCESSED\
|
|
- Log file written to D:\INTERFACE\Finished_Goods_Interface.log
|
|
|
|
TEST_MODE (when True):
|
|
- Prevents files being moved to the PROCESSED folder
|
|
- Suppresses email notifications
|
|
- Rolls back all database transactions instead of committing
|
|
'''
|
|
import ConfigParser
|
|
import csv, os, smtplib
|
|
from email import Encoders
|
|
from email.MIMEBase import MIMEBase
|
|
from email.MIMEImage import MIMEImage
|
|
from email.MIMEMultipart import MIMEMultipart
|
|
from email.MIMEText import MIMEText
|
|
from email.Utils import COMMASPACE, formatdate
|
|
from glob import glob
|
|
import logging
|
|
from operator import itemgetter
|
|
import sys, sqlite3, re
|
|
|
|
from sqlalchemy import create_engine
|
|
from sqlalchemy.ext.declarative import declarative_base
|
|
from sqlalchemy.orm import sessionmaker
|
|
|
|
|
|
TEST_MODE = False
|
|
LOG_LEVEL = logging.DEBUG
|
|
PATH = 'D:\\INTERFACE\\'
|
|
PULL_ORDERS = ['U17435', 'U32614', 'U37151', 'U38029', 'U39702', 'U38664', 'U30473', 'U61478', 'U64631', 'U74634', 'U89496']
|
|
IN_FILE_FORMAT = "[\S\s]+as400_jsi_(?P<txnum>[\d][\d][\d][\d][\d]).csv"
|
|
|
|
ROLL_ERROR_RECIPIENTS = ['user1@example.com', 'user2@example.com']
|
|
TX_ERROR_RECIPIENTS = ['admin@example.com', 'user1@example.com']
|
|
|
|
ORDER = 0
|
|
LINE = 1
|
|
TICKET = 2
|
|
FINISHED_YARDS = 3
|
|
INVOICED_YARDS = 4
|
|
BIN = 5
|
|
WAREHOUSE = 6
|
|
WEIGHT = 7
|
|
SPAT = 8
|
|
SCOL = 9
|
|
|
|
#This script must be run as SUNBURYTEXTILE\Administrator
|
|
#engine = create_engine("mssql+pyodbc://jsisbprod", echo=False)
|
|
engine = create_engine("mssql+pyodbc://{}:{}@jsisbprod".format(
|
|
os.environ['JOMAR_DB_UID'], os.environ['JOMAR_DB_PWD']), echo=False)
|
|
Base = declarative_base(engine)
|
|
metadata = Base.metadata
|
|
|
|
class TXRYBW10(Base):
|
|
__tablename__ = 'TXRYBW10' #Finished good interface table
|
|
__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 TXRYRT00(Base):
|
|
__tablename__ = 'TXRYRT00' #Roll/tag master
|
|
__table_args__ = {'autoload':True}
|
|
class ItemInfo(Base):
|
|
__tablename__ = 'TXMYAT03'
|
|
__table_args__ = {'autoload':True}
|
|
|
|
'''
|
|
MODIFIED - 01/04/2018 - SB-20
|
|
This table is refreshed from the AS400 every hour.
|
|
Need this to determine NAFTA qualification
|
|
'''
|
|
class HTSCDSFL(Base):
|
|
__tablename__ = 'as400_HTSCDSFL'
|
|
__table_args__ = {'autoload':True}
|
|
|
|
def load_email_credentials():
|
|
vault = sqlite3.connect('D:\\FTP\\ftp.db')
|
|
cursor = vault.execute("SELECT * from accounts where code = 'DNR'")
|
|
for row in cursor:
|
|
return [row[1], row[2]]
|
|
|
|
'''
|
|
I found the bulk of this function already written on someone's blog for sending mail
|
|
via outlook 365. I made a few modifications for our purposes.
|
|
'''
|
|
def send_mail(send_from, send_to, subject, text, files=None,
|
|
data_attachments=None, server="smtp.example.com", port=25,
|
|
tls=False, html=False, images=None,
|
|
username=None, password=None,
|
|
config_file=None, config=None):
|
|
|
|
if files is None:
|
|
files = []
|
|
|
|
if images is None:
|
|
images = []
|
|
|
|
if data_attachments is None:
|
|
data_attachments = []
|
|
|
|
if config_file is not None:
|
|
config = ConfigParser.ConfigParser()
|
|
config.read(config_file)
|
|
|
|
if config is not None:
|
|
server = config.get('smtp', 'server')
|
|
port = config.get('smtp', 'port')
|
|
tls = config.get('smtp', 'tls').lower() in ('true', 'yes', 'y')
|
|
username = config.get('smtp', 'username')
|
|
password = config.get('smtp', 'password')
|
|
|
|
msg = MIMEMultipart('related')
|
|
msg['From'] = send_from
|
|
msg['To'] = send_to if isinstance(send_to, basestring) else COMMASPACE.join(send_to)
|
|
msg['Date'] = formatdate(localtime=True)
|
|
msg['Subject'] = subject
|
|
|
|
msg.attach( MIMEText(text, 'html' if html else 'plain') )
|
|
|
|
for f in files:
|
|
part = MIMEBase('application', "octet-stream")
|
|
part.set_payload( open(f,"rb").read() )
|
|
Encoders.encode_base64(part)
|
|
part.add_header('Content-Disposition', 'attachment; filename="%s"' % os.path.basename(f))
|
|
msg.attach(part)
|
|
|
|
for f in data_attachments:
|
|
part = MIMEBase('application', "octet-stream")
|
|
part.set_payload( f['data'] )
|
|
Encoders.encode_base64(part)
|
|
part.add_header('Content-Disposition', 'attachment; filename="%s"' % f['filename'])
|
|
msg.attach(part)
|
|
|
|
for (n, i) in enumerate(images):
|
|
fp = open(i, 'rb')
|
|
msgImage = MIMEImage(fp.read())
|
|
fp.close()
|
|
msgImage.add_header('Content-ID', '<image{0}>'.format(str(n+1)))
|
|
msg.attach(msgImage)
|
|
|
|
smtp = smtplib.SMTP(server, int(port))
|
|
if tls:
|
|
smtp.starttls()
|
|
|
|
#if username is not None:
|
|
# smtp.login(username, password)
|
|
smtp.sendmail(send_from, send_to, msg.as_string())
|
|
smtp.close()
|
|
|
|
'''
|
|
Opens connection to database returning a session object.
|
|
'''
|
|
def loadSession():
|
|
global engine
|
|
Session = sessionmaker(bind=engine)
|
|
session = Session()
|
|
return session
|
|
|
|
def initializeLogger(name='log'):
|
|
global logger
|
|
logger = logging.getLogger(__name__)
|
|
logger.setLevel(LOG_LEVEL)
|
|
handler = logging.FileHandler('D:\\INTERFACE\\' + name + '.log')
|
|
handler.setLevel(LOG_LEVEL)
|
|
formatter = logging.Formatter('%(asctime)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)
|
|
|
|
'''
|
|
MODIFIED - 01/04/2018 - SB-20
|
|
This function is the brains of the operation regarding the NAFTA changes.
|
|
Simple lookup
|
|
'''
|
|
def NAFTA(session, pattern, color):
|
|
logger.debug('Attempting lookup in HTSCDSFL to determine NAFTA qualification')
|
|
logger.debug('SELECT * FROM as400_HTSCDSFL WHERE as400_HTSCDSFL.HTSPAT = \'%s\' AND as400_HTSCDSFL.HTSCLR = \'%s\';', pattern, color)
|
|
p = session.query(HTSCDSFL).filter(HTSCDSFL.HTSPAT == pattern, HTSCDSFL.HTSCLR == color)
|
|
for r in p:
|
|
logger.debug('Record found in HTSCDSFL. Value of HTSNQC is \'%s\'', r.HTSNQC)
|
|
if r.HTSNQC == 'X':
|
|
return 'Y'
|
|
else:
|
|
return 'N'
|
|
logger.debug('No record found in HTSCDSFL!')
|
|
return 'N'
|
|
|
|
if __name__ == '__main__':
|
|
os.chdir('D:\INTERFACE')
|
|
initializeLogger('Finished_Goods_Interface')
|
|
logger.info('********************************************************************************')
|
|
if(len(sys.argv) < 2):
|
|
logger.info('No args supplied, processing all files in folder...')
|
|
logger.debug('Obtaining list of CSV files in ' + PATH + '\\')
|
|
files = glob(PATH + '\*.csv')
|
|
|
|
# Verify file name and check for any previous transactions hung up in folder
|
|
elif re.match(IN_FILE_FORMAT, sys.argv[1], re.IGNORECASE):
|
|
logger.info('Processing single file: ' + sys.argv[1])
|
|
files = [sys.argv[1]]
|
|
|
|
in_file_tx = int(re.match(IN_FILE_FORMAT, sys.argv[1], re.IGNORECASE).group('txnum')) #parse transaction num from file name and cast to int
|
|
ls = glob(PATH + '\*.csv')
|
|
for f in ls:
|
|
test = int(re.match(IN_FILE_FORMAT, f, re.IGNORECASE).group('txnum'))
|
|
if test < (in_file_tx - 1):
|
|
logger.error('Trying to process: %s but %s is still in folder.', sys.argv[1], f)
|
|
|
|
try:
|
|
if not TEST_MODE:
|
|
logger.info('Alerting relevant people to this situation...')
|
|
|
|
msg = 'For whatever reason <b>' + f + '</b> has not finished processing before <b>' + sys.argv[1] + '</b> was submitted.'
|
|
dnr_account = load_email_credentials()
|
|
send_mail('donotreply@example.com', TX_ERROR_RECIPIENTS, 'TRANSACTION HUNG UP IN FGI!', msg,
|
|
username=dnr_account[0], password=dnr_account[1], html=True)
|
|
except:
|
|
logger.error('Could not send e-mail. Check internet connection.')
|
|
|
|
logger.error('THERE ARE OLDER TRANSACTIONS THAT HAVE NOT PROCESSED!! Exting...')
|
|
sys.exit()
|
|
|
|
# Invalid file specified. print error msg and exit.
|
|
else:
|
|
logger.error('File name: %s does not conform to as400_jsi_#####.csv', sys.argv[1])
|
|
sys.exit()
|
|
|
|
session = loadSession()
|
|
|
|
'''
|
|
Process each CSV file
|
|
as400_jsi_00001.csv
|
|
'''
|
|
for f in files:
|
|
if not('as400_jsi' in f):
|
|
logger.debug('Skipping ' + f + ' wrong file name...')
|
|
continue
|
|
|
|
rows = []
|
|
eRows = []
|
|
jLot = f.split('_')[2].split('.')[0]
|
|
ERRORS = False #This can be set True in a number of places, This conditions whether or not an e-mail is sent
|
|
|
|
logger.info('****************************************')
|
|
logger.info('Loading file ' + f)
|
|
|
|
#Read file fully into rows
|
|
with open(f, 'rb') as g:
|
|
reader = csv.reader(g)
|
|
for row in reader:
|
|
rows.append(row)
|
|
|
|
#rows = sorted(rows, key=itemgetter(0, 1, 2)) #sort rows read by Order,Line,Piece since AS400 seems to supply data in random order
|
|
logger.info(str(len(rows)) + ' records read.')
|
|
tickets_processed = []
|
|
|
|
'''
|
|
Process each record in file
|
|
'''
|
|
for row in reversed(rows):
|
|
#print row[ORDER], row[LINE], row[TICKET], row[FINISHED_YARDS], row[INVOICED_YARDS], row[BIN]
|
|
if row[TICKET] in tickets_processed:
|
|
logger.warn('Encountered ticket: ' + row[TICKET] + ' more than once in single transmission!')
|
|
row.append('DUPLICATE TICKET ROW. DOUBLE CHECK YARDAGE/WEIGHT IN JOMAR!!!')
|
|
eRows.append(row)
|
|
ERRORS = True
|
|
continue
|
|
|
|
jOrder = 'null'
|
|
jLine = -1
|
|
jItem = -1
|
|
|
|
#if(row[ORDER] in PULL_ORDERS):
|
|
#logger.warn(row[ORDER] + ' is a pull order. Ignoring for now! Ticket: ' + row[TICKET])
|
|
#row.append('**IGNORING PULL ORDERS FOR NOW**')
|
|
#eRows.append(row)
|
|
#ERRORS = True
|
|
#continue
|
|
|
|
#Neil says no pieces in HLD bin should make it this far; just in case...
|
|
if(row[BIN] == 'HLD' or (row[WAREHOUSE] == 'SH' and row[BIN] == ' ')):
|
|
#print row[ORDER], row[LINE], row[TICKET], row[FINISHED_YARDS], row[INVOICED_YARDS], row[BIN]
|
|
logger.warn(row[WAREHOUSE] + '-' + row[BIN] + ' is not a valid bin! Ticket: ' + row[TICKET])
|
|
row.append('INVALID BIN')
|
|
eRows.append(row)
|
|
ERRORS = True
|
|
continue
|
|
|
|
if(float(row[FINISHED_YARDS]) <= 0):
|
|
#print row[ORDER], row[LINE], row[TICKET], row[FINISHED_YARDS], row[INVOICED_YARDS], row[BIN]
|
|
logger.warn('Finished Yards must be > 0. Ticket: ' + row[TICKET])
|
|
row.append('Finished Yards must be > 0')
|
|
eRows.append(row)
|
|
ERRORS = True
|
|
continue
|
|
|
|
if(float(row[INVOICED_YARDS]) <= 0):
|
|
#print row[ORDER], row[LINE], row[TICKET], row[FINISHED_YARDS], row[INVOICED_YARDS], row[BIN]
|
|
logger.warn('Invoiced Yards must be > 0. Ticket: ' + row[TICKET])
|
|
row.append('Invoiced Yards must be > 0')
|
|
eRows.append(row)
|
|
ERRORS = True
|
|
continue
|
|
|
|
#Get Jomar order number
|
|
logger.info('Querying order header table for Jomar order number...')
|
|
logger.debug('SELECT * FROM TXUYUV00 WHERE TXUYUV00.UBNR = \'001\' AND TXUYUV00.UPLT = \'00\' AND TXUYUV00.UBLA = \'SO\' AND (TXUYUV00.URBPO = \'' + row[ORDER] + '\' OR TXUYUV00.URBPO = \'' + row[ORDER][1:] + '\');')
|
|
q = session.query(TXUYUV00).order_by(TXUYUV00.UBLN).filter(TXUYUV00.UBNR == '001', TXUYUV00.UPLT == '00', TXUYUV00.UBLA == 'SO', ((TXUYUV00.URBPO == row[ORDER]) | (TXUYUV00.URBPO == row[ORDER][1:])))
|
|
if(q.count() != 1 and row[ORDER]):
|
|
#print row[ORDER], row[LINE], row[TICKET], row[FINISHED_YARDS], row[INVOICED_YARDS], row[BIN]
|
|
logger.warn('Cannot locate order (' + row[ORDER] + ') in Jomar system. Ticket: ' + row[TICKET])
|
|
row.append('Cannot locate order in Jomar system.')
|
|
eRows.append(row)
|
|
ERRORS = True
|
|
continue
|
|
for r in q:
|
|
jOrder = r.UBLN
|
|
logger.info('Jomar order: ' + jOrder + ' Ticket: ' + row[TICKET])
|
|
#print 'Jomar Order:', jOrder, 'Matches Sunbury Order:', row[ORDER]
|
|
|
|
#Get Jomar Line Number
|
|
logger.info('Querying order detail table for Jomar 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(int(row[LINE])) + ';')
|
|
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 == int(row[LINE]))
|
|
if(q.count() != 1 and row[ORDER]):
|
|
#print row[ORDER], row[LINE], row[TICKET], row[FINISHED_YARDS], row[INVOICED_YARDS], row[BIN]
|
|
logger.warn('AS400 Line: ' + row[LINE] + ' does not exist on Jomar order: ' + jOrder)
|
|
row.append('Jomar Order: ' + jOrder + ' - There are no lines on this order where AS400 Line Number = ' + row[LINE] + ' Ticket: ' + row[TICKET])
|
|
eRows.append(row)
|
|
ERRORS = True
|
|
continue
|
|
skip = False
|
|
#Verify pattern and color match b/t AS400 and Jomar
|
|
for r in q:
|
|
if(r[1].XFLX33.strip().upper() != row[SPAT].strip() and row[ORDER]):
|
|
skip = True
|
|
ERRORS = True
|
|
logger.warn('Pattern Mismatch! Jomar: ' + r[1].XFLX33 + ' AS400: ' + row[SPAT].strip() + ' Ticket: ' + row[TICKET])
|
|
row.append('Pattern Mismatch! Jomar: ' + r[1].XFLX33 + ' AS400: ' + row[SPAT].strip())
|
|
eRows.append(row)
|
|
if(int(r[1].XFLX34) != int(row[SCOL].strip()) and row[ORDER]):
|
|
skip = True
|
|
ERRORS = True
|
|
logger.warn('Color Mismatch! Jomar: ' + r[1].XFLX34 + ' AS400: ' + row[SCOL].strip() + ' Ticket: ' + row[TICKET])
|
|
row.append('Color Mismatch! Jomar: ' + r[1].XFLX34 + ' AS400: ' + row[SCOL].strip())
|
|
eRows.append(row)
|
|
|
|
if(skip == False):
|
|
jLine = r[0].VLFN
|
|
jItem = r[0].VANR
|
|
logger.info('Jomar line: ' + str(jLine) + ' Ticket: ' + row[TICKET])
|
|
logger.info('Jomar item: ' + str(jItem) + ' Ticket: ' + row[TICKET])
|
|
#print ' Jomar Line:', jLine, 'Matches Sunbury Line:', row[LINE]
|
|
|
|
if(skip):
|
|
continue
|
|
|
|
'''
|
|
Not doing this anymore for obvious reasons... Wish I would've gotten the information
|
|
concerning the other possible inventory transactions before 04/20/16......
|
|
'''
|
|
#If a roll with this ticket number exists in the inventory, get rid of it
|
|
#session.query(TXRYRT00).filter(TXRYRT00.RTRNR == row[TICKET]).delete()
|
|
|
|
tickets_processed.append(row[TICKET]) #Made it past all of the error checks, add this ticket to processed list.
|
|
|
|
logger.info('Querying Roll/Tag master to see if this ticket exists in Jomar already. Ticket: ' + row[TICKET])
|
|
logger.debug('SELECT COUNT(*) FROM TXRYRT00 WHERE TXRYRT00.RTRNR = ' + row[TICKET] + ';')
|
|
q = session.query(TXRYRT00).filter(TXRYRT00.RTRNR == row[TICKET]).count()
|
|
if(q == 0): #Do an 08 transaction...
|
|
logger.info('No previous record of roll. Adding production receipt (08) transaction. Ticket: ' + row[TICKET])
|
|
i = TXRYBW10()
|
|
i.RWAPL = '00'
|
|
i.RWSPL = '00'
|
|
i.RWSLA = 'SO'
|
|
i.RWPLT = '00'
|
|
i.RWBNR = '001'
|
|
i.RWALA = 'SO'
|
|
i.RWBEW = '08'
|
|
'''
|
|
MODIFIED - 01/04/2018 - SB-20
|
|
Set RTGRML to 'US' for all rolls
|
|
'''
|
|
i.RWGRML = 'US'
|
|
if(row[ORDER] not in PULL_ORDERS):
|
|
i.RWALN = jOrder
|
|
i.RWSLN = jOrder
|
|
i.RWAFN = jLine
|
|
i.RWSFN = jLine
|
|
else:
|
|
i.RWALN = None
|
|
i.RWSLN = None
|
|
i.RWAFN = None
|
|
i.RWSFN = None
|
|
i.RWANR = jItem
|
|
i.RWCHA = jLot
|
|
i.RWRNR = row[TICKET]
|
|
i.RWPGLG = row[FINISHED_YARDS]
|
|
i.RWPLG = row[INVOICED_YARDS]
|
|
if(row[BIN] == 'BAG'):
|
|
logger.debug('BAG bin = BG Warehouse. Ticket: ' + row[TICKET])
|
|
i.RWLOC = 'BG'
|
|
i.RWLPL = ''
|
|
elif(row[BIN].strip() == '08'):
|
|
logger.debug('08 bin = B8 Warehouse. Ticket: ' + row[TICKET])
|
|
i.RWLOC = 'B8'
|
|
i.RWLPL = ''
|
|
else:
|
|
if(row[WAREHOUSE] == 'SH'):
|
|
i.RWLPL = row[BIN]
|
|
else:
|
|
i.RWLPL = ''
|
|
i.RWLOC = row[WAREHOUSE]
|
|
i.RWPWG = int(float(row[WEIGHT]))
|
|
#i.RWTWG = int(float(row[WEIGHT])) #This was screwing up proformas. This is a tare weight field and so it should be zero for our purposes
|
|
i.RWTWG = 0
|
|
i.RWQAL = '01'
|
|
|
|
'''
|
|
MODIFIED - 01/04/2018 - SB-20
|
|
Determine if roll is NAFTA qualified.
|
|
'''
|
|
i.RWORCD = NAFTA(session, row[SPAT].strip(), str(int(row[SCOL].strip())))
|
|
|
|
session.add(i)
|
|
else:
|
|
logger.debug('Roll already exists. Querying roll/tag master again to determine what to do...')
|
|
logger.debug('SELECT * FROM TXRYRT00 WHERE TXRYRT00.RTRNR = ' + row[TICKET] + ';')
|
|
p = session.query(TXRYRT00).filter(TXRYRT00.RTRNR == row[TICKET])
|
|
for r in p:
|
|
'''
|
|
MODIFIED 01/23/17
|
|
Ran across a scenario where rolls already shipped in Jomar came through the interface again.
|
|
Now checking for shipped yardage here so the script can kick out an error instead of compounding issue.
|
|
'''
|
|
if float(r.RTSLG) > 0:
|
|
tickets_processed.remove(row[TICKET]) #Remove this ticket from processed list
|
|
logger.warn('Shipped yards greater than zero! Ticket: %s', row[TICKET])
|
|
row.append('Shipped yards > 0')
|
|
eRows.append(row)
|
|
ERRORS = True
|
|
continue
|
|
|
|
current_lot = r.RTCHA
|
|
current_inv_yards = r.RTPLG
|
|
current_fin_yards = r.RTPGLG
|
|
current_inv_lbs = r.RTPWG
|
|
current_bin = r.RTLPL
|
|
current_warehouse = r.RTLOC
|
|
dest_bin = row[BIN]
|
|
dest_warehouse = row[WAREHOUSE]
|
|
|
|
#Adjust bins and warehouses for bagging machine nonsense....
|
|
if(row[BIN].strip() == '08'):
|
|
dest_bin = ''
|
|
dest_warehouse = 'B8'
|
|
if(row[BIN].strip() == 'BAG'):
|
|
dest_bin = ''
|
|
dest_warehouse = 'BG'
|
|
|
|
if((float(current_inv_yards) != float(row[INVOICED_YARDS])) or (int(current_inv_lbs) != int(float(row[WEIGHT]))) or (float(current_fin_yards) != float(row[FINISHED_YARDS]))):
|
|
logger.info('Roll exists but length/weight differ. Adjusting via a Production Receipt transaction (08) Ticket: ' + row[TICKET])
|
|
|
|
if (float(current_inv_yards) != float(row[INVOICED_YARDS])):
|
|
logger.debug('Current Invoiced Yards = %s Transmitted = %s Adjustment = %s', current_inv_yards, row[INVOICED_YARDS], str(float(row[INVOICED_YARDS]) - float(current_inv_yards)))
|
|
if (int(current_inv_lbs) != int(float(row[WEIGHT]))):
|
|
logger.debug('Current Weight = %s Transmitted = %s Adjustment = %s', current_inv_lbs, row[WEIGHT], (int(float(row[WEIGHT])) - int(current_inv_lbs)))
|
|
if (float(current_fin_yards) != float(row[FINISHED_YARDS])):
|
|
logger.debug('Current Finished Yards = %s Transmitted = %s Adjustment = %s', current_fin_yards, row[FINISHED_YARDS], str(float(row[FINISHED_YARDS]) - float(current_fin_yards)))
|
|
|
|
i = TXRYBW10()
|
|
i.RWAPL = '00'
|
|
i.RWSPL = '00'
|
|
i.RWSLA = 'SO'
|
|
i.RWPLT = '00'
|
|
i.RWBNR = '001'
|
|
i.RWALA = 'SO'
|
|
i.RWBEW = '08'
|
|
if(row[ORDER] not in PULL_ORDERS):
|
|
i.RWALN = jOrder
|
|
i.RWSLN = jOrder
|
|
i.RWAFN = jLine
|
|
i.RWSFN = jLine
|
|
else:
|
|
i.RWALN = None
|
|
i.RWSLN = None
|
|
i.RWAFN = None
|
|
i.RWSFN = None
|
|
i.RWANR = jItem
|
|
i.RWCHA = current_lot
|
|
i.RWRNR = row[TICKET]
|
|
i.RWPGLG = str(float(row[FINISHED_YARDS]) - float(current_fin_yards))
|
|
i.RWPLG = str(float(row[INVOICED_YARDS]) - float(current_inv_yards))
|
|
i.RWLOC = current_warehouse.strip()
|
|
i.RWLPL = current_bin.strip()
|
|
i.RWPWG = (int(float(row[WEIGHT])) - int(current_inv_lbs))
|
|
#i.RWTWG = (int(float(row[WEIGHT])) - int(current_inv_lbs))
|
|
i.RWTWG = 0
|
|
i.RWQAL = '01'
|
|
session.add(i)
|
|
|
|
if((current_bin.strip() != dest_bin.strip()) or (current_warehouse.strip() != dest_warehouse.strip())):
|
|
logger.info('Roll exists but bins are different. Adding a movement (06) transaction...' + current_warehouse + '-' + current_bin + ' --> ' + dest_warehouse + '-' + dest_bin + ' Ticket: ' + row[TICKET])
|
|
i = TXRYBW10()
|
|
i.RWAPL = '00'
|
|
i.RWSPL = '00'
|
|
i.RWSLA = 'SO'
|
|
i.RWPLT = '00'
|
|
i.RWBNR = '001'
|
|
i.RWALA = 'SO'
|
|
i.RWBEW = '06'
|
|
if(row[ORDER] not in PULL_ORDERS):
|
|
i.RWALN = jOrder
|
|
i.RWSLN = jOrder
|
|
i.RWAFN = jLine
|
|
i.RWSFN = jLine
|
|
else:
|
|
i.RWALN = None
|
|
i.RWSLN = None
|
|
i.RWAFN = None
|
|
i.RWSFN = None
|
|
i.RWANR = jItem
|
|
i.RWCHA = current_lot
|
|
i.RWRNR = row[TICKET]
|
|
i.RWPGLG = row[FINISHED_YARDS]
|
|
i.RWPLG = row[INVOICED_YARDS]
|
|
i.RWLOC = dest_warehouse.strip() #Xfer to warehouse
|
|
i.RWLPL = dest_bin.strip() #Xfer to bin
|
|
i.RWLO2 = current_warehouse.strip() #Xfer from warehouse
|
|
i.RWLP2 = current_bin.strip() #Xfer from bin
|
|
i.RWPL2 = '00' #Xfer from plant
|
|
i.RWPWG = int(float(row[WEIGHT]))
|
|
#i.RWTWG = int(float(row[WEIGHT]))
|
|
i.RWTWG = 0
|
|
i.RWQAL = '01'
|
|
session.add(i)
|
|
|
|
if( (float(current_inv_yards) == float(row[INVOICED_YARDS])) and (int(current_inv_lbs) == int(float(row[WEIGHT]))) and
|
|
(current_bin.strip() == dest_bin.strip()) and (current_warehouse.strip() == dest_warehouse.strip()) and
|
|
(float(current_fin_yards) == float(row[FINISHED_YARDS]))):
|
|
logger.info('Nothing appears to be different. Ignoring. Ticket: ' + row[TICKET])
|
|
|
|
'''
|
|
End record processing
|
|
'''
|
|
|
|
#If errors were encountered during processing, e-mail the suspect rows to Aaron in a csv
|
|
if ERRORS:
|
|
#Dump eRows to errors.csv
|
|
logger.debug("Dumping error rows to: " + PATH + "ERRORS\\ERRORS_" + jLot + ".csv")
|
|
with open(PATH + "ERRORS\\ERRORS_" + jLot + ".csv", "wb") as g:
|
|
writer = csv.writer(g)
|
|
writer.writerows(eRows)
|
|
try:
|
|
if not TEST_MODE:
|
|
logger.info('E-mailing Aaron list of error rows...')
|
|
|
|
dnr_account = load_email_credentials()
|
|
send_mail('donotreply@example.com', ROLL_ERROR_RECIPIENTS, 'AS400 -> Jomar Inventory Errors', 'See Attachment.',
|
|
username=dnr_account[0], password=dnr_account[1], files=[PATH + "ERRORS\\ERRORS_" + jLot + ".csv"])
|
|
except:
|
|
logger.error('Could not send e-mail. Check internet connection.')
|
|
|
|
if not TEST_MODE:
|
|
logger.debug("Moving " + f + " to " + "PROCESSED\\" + f.split("\\")[2])
|
|
os.rename(f, PATH + "PROCESSED\\" + f.split("\\")[2])
|
|
'''
|
|
End file processing
|
|
'''
|
|
|
|
#When all files have been processed commit new records to interface table.
|
|
if(TEST_MODE):
|
|
logger.warn('**TEST MODE ENABLED. ROLLING BACK DATABASE TRANSACTIONS**')
|
|
session.rollback()
|
|
else:
|
|
logger.debug('Committing database transactions...')
|
|
session.commit()
|
|
logger.debug('Closing database connection.')
|
|
session.close()
|