Python_Examples/stm_etl/FTP/mg_997.py

'''
Created on Dec 13, 2016

To generate daily 997 document for Momentum.
This particular 997 is confirming all POs received by us for the day.

I created a table called mg_850_997 in the database to support this script.
This table is filled by mg_850.py as it processes purchase orders from Momentum.

@author: Wes
'''
from alert import send_alert_html
from glob import glob

import datetime
import sys
import os, re, StringIO
import logging

import pyodbc
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

RECIPIENTS = ['admin@example.com']
SUBJECT = '997 Document Sent to Momentum'
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

########################################################################
#This table lists POs by transaction number and date
#mg_850.py is fills this table as it processes POs
class mg_850_997(Base):
    __tablename__ = 'mg_850_997' 
    __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)

'''
Gotta get transaction number from AS400 until ALL transmissions
are handled on the new system... whoopee
'''
def getTransNumber():
    connection = pyodbc.connect(
        driver='{iSeries Access ODBC Driver}',
        system='SUNBURY',
        uid=os.environ['AS400_UID'],
        pwd=os.environ['AS400_PWD'])
    c1 = connection.cursor()
    ret = -1
    
    c1.execute('select * from stmdata.MGTX#FL')
    for row in c1:
        ret = int(row[1]) + 1
        
    c1.close()
        
    return ret

'''
Update the Momentum transaction number in the AS400 database
'''
def updateTransNumber(txnum):
    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()
    c1.execute('UPDATE STMDATA.MGTX#FL SET MGSEQ# = ' + str(txnum))
    c1.close()

def generateMomentum997(date):
    global logger
    os.chdir('D:\FTP')
    initializeLogger('mg_850_interface')

    # @TODO: Properly handle sequence numbers eventually
    logger.info('Grabbing next outgoing transaction number from the AS400...')
    trans_no = getTransNumber()

    logger.info('Opening database connection...')
    session = loadSession()

    '''
    Each element of purchase orders will be structred like this:
    [ 15600, ['21495','21540'] ]
    In this example 15600 is the incoming transaction number we are confirming.
    21495 and 21540 are the purchase orders that were transmitted in that transaction
    '''
    purchase_orders = []
    level = -1
    pos = []
    q = session.query(mg_850_997).order_by(mg_850_997.trans_no).filter(mg_850_997.proc_date == date)
    for row in q:
        #First interation special case
        if level == -1:
            level = row.trans_no
            logger.info('             Processing Transaction: %s', level)

        #Check if transaction number changed since last record
        if not (level == row.trans_no):
            logger.info('Purchase orders on this transaction: %s', pos)
            purchase_orders.append([level, pos]) #Write this transaction data to list
            #Reset loop vars
            pos = []
            level = row.trans_no
            logger.info('             Processing Transaction: %s', level)

        pos.append(row.po)
        
    #Write final transcation data to list
    logger.info('Purchase orders on this transaction: %s', pos)
    purchase_orders.append([level, pos])

    logger.debug('Closing database connection...')
    session.close()

    #Write out to file here
    lines_997 = []
    lines_997.append('P~\n')
    lines_997.append('TP~12~5702863800~12~9498338886~%s~%s~\n' % (date, trans_no))
    for t in purchase_orders:
        lines_997.append('H~%s~%s~\n' % (t[0], date))
        for p in t[1]:
            lines_997.append('D~%s~\n' % (p.zfill(5)))
    lines_997.append('S~%s~\n' % (len(lines_997)))

    #IF testing just dump transmission lines to console
    if(TEST_MODE):
        for line in lines_997:
            print line

    if not(TEST_MODE):
        logger.info('Creating D:\\FTP\\SEND_MG\\SBR_997_%s.txt', trans_no)
        f = open('SEND_MG\\SBR_997_' + str(trans_no) + '.txt', 'w') 
        f.writelines(lines_997)
        f.close()

        try:
            logger.info('Sending e-mail alert...')
            send_alert_html(RECIPIENTS, SUBJECT, attachments=glob('D:\\FTP\\SEND_MG\\SBR_997_*.txt'), message=MESSAGE.getvalue())
        except:
            logger.error('Unable to send e-mail. Is the internet out?')

        updateTransNumber(trans_no) #Update transaction number in AS400

if __name__ == '__main__':
    if(len(sys.argv) < 2):
        pass
    else:
        generateMomentum997(sys.argv[1])