101 lines
2.9 KiB
Python
101 lines
2.9 KiB
Python
'''
|
|
Auto close SOs in Sunbury Jomar based on what went to history in the AS400 last weekend.
|
|
To be run every Monday morning.
|
|
|
|
Created on: 2018-04-05
|
|
|
|
@author: Wes
|
|
'''
|
|
import pyodbc, time, logging, sys, os, StringIO, csv, sqlite3
|
|
from sqlalchemy import create_engine
|
|
from sqlalchemy.ext.declarative import declarative_base
|
|
from sqlalchemy.orm import sessionmaker
|
|
from sqlalchemy.sql.expression import update
|
|
|
|
EXTRACT_QUERY = 'SELECT ordera FROM wes.sotohist'
|
|
|
|
logger = None
|
|
LOG_LEVEL = logging.DEBUG
|
|
|
|
engine = create_engine("mssql+pyodbc://jsisbprod", echo=False)
|
|
Base = declarative_base(engine)
|
|
metadata = Base.metadata
|
|
|
|
# Jomar Order Header Table
|
|
class TXUYUV00(Base):
|
|
__tablename__ = 'TXUYUV00'
|
|
__table_args__ = {'autoload':True}
|
|
|
|
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:\\' + 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.DEBUG)
|
|
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)
|
|
|
|
def loadSession():
|
|
global engine
|
|
Session = sessionmaker(bind=engine)
|
|
session = Session()
|
|
return session
|
|
|
|
def so_auto_close_main():
|
|
os.chdir('D:\\')
|
|
initializeLogger('Jomar_SO_auto_close')
|
|
logger.debug('--------------------------------------------------------------------------------')
|
|
|
|
archived_as400_orders = []
|
|
|
|
logger.info('Opening connection with AS400...')
|
|
connection = pyodbc.connect(
|
|
driver='{iSeries Access ODBC Driver}',
|
|
system='SUNBURY',
|
|
uid=os.environ['AS400_UID'],
|
|
pwd=os.environ['AS400_PWD'],
|
|
autocommit=True)
|
|
c1 = connection.cursor()
|
|
|
|
logger.info('Fetching list of newly archived orders from AS400...')
|
|
for r in c1.execute(EXTRACT_QUERY):
|
|
archived_as400_orders.append(r[0])
|
|
|
|
logger.debug(archived_as400_orders)
|
|
|
|
logger.debug('Disconnecting from AS400...')
|
|
c1.close()
|
|
|
|
logger.info('Connecting to JSISBPROD...')
|
|
session = loadSession()
|
|
|
|
logger.info('Closing Jomar order headers...')
|
|
ex = update(TXUYUV00.__table__).where(TXUYUV00.URBPO.in_(archived_as400_orders)).values(UAKT=u"X")
|
|
session.execute(ex)
|
|
session.commit()
|
|
|
|
logger.debug('Disconnecting from JSISBPROD...')
|
|
session.close()
|
|
|
|
if __name__ == '__main__':
|
|
so_auto_close_main()
|