'''
Created on January 19, 2018

Anderson plant needs an ASN for rolls we weave for them.
This script is pulling data from our current implementation of Jomar (before we are on CF Jomar)
So, GR-Custom Fabrics is set up as customer 8000 in our Jomar; they will be flagged for 856 documents.
This should allow me to use the standard ASN tables I've used for Maharam and Momentum.

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

import datetime
import logging
import sys
import StringIO

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql.expression import update

# Only 2 patterns in the beginning; might as well just use a dict
NAFTA = {"ADENA PURE" : "Y", "TAILORED INDIGO" : "N"}
CF_ITEM = {"ADENA PURE" : "FF 0000063322", "TAILORED INDIGO" : "FF 0000063311"}

RECIPIENTS = ['admin@example.com']
SUBJECT = 'Shipment Notices Transmitted to GR-Anderson'
MESSAGE = None

TEST_MODE = True
LOG_LEVEL = logging.DEBUG
ACC_NUMBERS = ['8000']

engine = create_engine("mssql+pyodbc://jsisbprod", echo=False)
Base = declarative_base(engine)
metadata = Base.metadata

class CSPASN00(Base):
    __tablename__ = 'CSPASN00'
    __table_args__ = {'autoload':True}
class CSPASN01(Base):
    __tablename__ = 'CSPASN01' 
    __table_args__ = {'autoload':True}
class CSPASN02(Base):
    __tablename__ = 'CSPASN02' 
    __table_args__ = {'autoload':True}
class CSPASN03(Base):
    __tablename__ = 'CSPASN03' 
    __table_args__ = {'autoload':True}
class CSPASN04(Base):
    __tablename__ = 'CSPASN04'  
    __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 - %(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)
    
'''
ASN tables

00 - List of shipments
01 - Contains shipment date for shipments in 01
02 - List of packing slips on each shipment
'''
def create_GR_Anderson_Ship_Notice():
    global MESSAGE
    initializeLogger('MAH_856_GENERATE')
    session = loadSession()
    shipments_to_flag_processed = []

    logger.info('Querying CSPASN0[0,1] for unprocessed shipments...')
    shipmentQ = session.query(CSPASN00,CSPASN01).order_by(CSPASN00.MSTBIL).filter(CSPASN00.CUSTNO.in_(ACC_NUMBERS)).filter(CSPASN00.ABNR == CSPASN01.ABNR, CSPASN00.APLT == CSPASN01.APLT, CSPASN00.CUSTNO == CSPASN01.CUSTNO, CSPASN00.MSTBIL == CSPASN01.MSTBIL)
    for shipment in shipmentQ:
        if(shipment[0].PROCSS == 'Y'):
            continue

        lines = []
        
        bGroup = shipment[0].ABNR
        plant = shipment[0].APLT
        cust = shipment[0].CUSTNO
        shipmentNumber = shipment[0].MSTBIL
        shipmentDate = datetime.datetime.strptime(str(shipment[1].SHPDTE), "%Y%m%d")

        logger.info('Processing shipment number: ' + shipmentNumber)
        logger.info('                Shipped on: ' + shipmentDate.strftime('%m/%d/%Y'))
        shipments_to_flag_processed.append(shipmentNumber)

        logger.info('Querying CSPASN02 for packing lists on this shipment...')
        packingListQ = session.query(CSPASN02).order_by(CSPASN02.BOLNUM).filter(CSPASN02.ABNR == bGroup, CSPASN02.APLT == plant, CSPASN02.CUSTNO == cust, CSPASN02.MSTBIL == shipmentNumber)
        for packingList in packingListQ:
            plNumber = packingList.BOLNUM
            poNumber = packingList.PONUM
            stmOrder = packingList.ORDNUM

            # Header record if required create here

            # Attempt to join xref table in here?
            detailQ = session.query(CSPASN03,CSPASN04).order_by(CSPASN04.LINE).filter(CSPASN03.ABNR == CSPASN04.ABNR, CSPASN03.APLT == CSPASN04.APLT, CSPASN03.CUSTNO == CSPASN04.CUSTNO,
                                                                                      CSPASN03.MSTBIL == CSPASN04.MSTBIL, CSPASN03.BOLNUM == CSPASN04.BOLNUM, CSPASN03.CTN == CSPASN04.CTN).filter(CSPASN03.ABNR == bGroup,
                                                                                                                                                                     CSPASN03.APLT == plant,
                                                                                                                                                                     CSPASN03.CUSTNO == cust,
                                                                                                                                                                     CSPASN03.MSTBIL == shipmentNumber,
                                                                                                                                                                     CSPASN03.BOLNUM == plNumber)
            detailCount = detailQ.count()
            for detail in detailQ:
                print CF_ITEM[detail[1].SB_PATTERNAME], NAFTA[detail[1].SB_PATTERNAME]

        # Write lines out to file here; one file per shipment

    if(len(shipments_to_flag_processed) > 0 and not TEST_MODE):
        ex = update(CSPASN00.__table__).where(CSPASN00.MSTBIL.in_(shipments_to_flag_processed)).values(PROCSS=u"Y")
        session.execute(ex)
        ex = None
        logger.debug('Comitting changes to database...')
        session.commit()
    if(len(shipments_to_flag_processed) == 0):
        logger.info('Nothing to send!')
    else:
        pass #add e-mail alert code here

    logger.debug('Closing database connection...')
    session.close()
    
    MESSAGE.truncate(0)

if __name__ == '__main__':
    create_GR_Anderson_Ship_Notice()