def update_host_log_as_processed(self, hostLogId, messageType): config = python_config.read_db_config() host = config.get('host') user = config.get('user') wcsDatabase = config.get('wcsdatabase') password = config.get('password') try: connection = mysql.connector.connect(host=host, user=user, database=wcsDatabase, password=password) cursor = connection.cursor() sql = "UPDATE host_logs SET type = %s where id = %s" updateValues = (messageType, hostLogId) cursor.execute(sql, updateValues) connection.commit() cursor.close() connection.close() except Exception as e: print(e) finally: cursor.close() connection.close()
def getLogs(): try: config = python_config.read_db_config() host = config.get('host') user = config.get('user') database = config.get('database') #wcsDatabase = config.get('wcsdatabase') password = config.get('password') connection = mysql.connector.connect( host= host, user= user, database= database, password= password ) cursor = connection.cursor() SQL = "select * from host_logs where type = 'ASGNCOMP' || type = 'ORDRCOMP' || type = 'CONTCOMP' || type = 'ROUTCOMP' || type = 'ADDCONTA'" cursor.execute(SQL) result = cursor.fetchall() return result except Exception as e: print(e)
def updateOrderComplete(self, connection): config = python_config.read_db_config() host = config.get('host') user = config.get('user') database = config.get('database') password = config.get('password') try: connection = mysql.connector.connect(host=host, user=user, database=database, password=password) cursor = connection.cursor() updateOrderCompleteSQL = ("UPDATE dat_master SET " "o_comp = %s, " "updated_at = %s " "WHERE route_no = %s " "AND stop_no = %s") # currentTimeStamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S') currentTimeStamp = datetime.now().strftime( '%Y-%m-%d %H:%M:%S.%f')[:-3] updateOrderValues = (1, currentTimeStamp, self.Route, self.Stop) cursor.execute(updateOrderCompleteSQL, updateOrderValues) connection.commit() rowcount = cursor.rowcount print("Rows updated: " + str(rowcount)) cursor.close() connection.close() if rowcount > 0: return True else: return False except Exception as e: print(e) exc_type, exc_value, exc_traceback = sys.exc_info() lines = traceback.format_exception(exc_type, exc_value, exc_traceback) exceptionMsg = exc_value exceptionDetails = ''.join('!! ' + line for line in lines) GlobalFunctions.logExceptionStackTrace(exceptionMsg, exceptionDetails) hostLog.dbLog("Eby_OrderComplete", "Upd Err", self.AsciiRequestMessage) return False finally: cursor.close() connection.close()
def get(database='database'): config = python_config.read_db_config() host = config.get('host') user = config.get('user') database = config.get(database) password = config.get('password') con = mysql.connector.connect(host=host, user=user, database=database, password=password) return con
def dbLog(source, m_typ, message): config = python_config.read_db_config() host = config.get('host') user = config.get('user') database = config.get('wcsdatabase') password = config.get('password') # host = "10.22.56.11" # user = "******" # database = "wcs" # password = "******" # host = "localhost" # user = "******" # database = "wcs" # password = "******" try: connection = mysql.connector.connect(host=host, user=user, database=database, password=password) cursor = connection.cursor() currentTimeStamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S.f')[:-3] insertLogSql = ("INSERT INTO host_logs " "(source, type, message, created_at, updated_at) " "VALUES (%s, %s, %s, %s, %s)") parsedMessage = str(message).replace("b", "").replace( "\\", "").replace("\x02", "").replace("\x03", "") newLog = (source, m_typ, parsedMessage, currentTimeStamp, currentTimeStamp) cursor.execute(insertLogSql, newLog) connection.commit() except Exception as e: print(e) #test = log("Basic YWE6YQ==", "https://dev.pendantautomation.com", "WXS to Host", "Dock Scan Log Request", "Log Scan KD3101017-001") #print(test)
def removeUnneededAssignments(self, connection): config = python_config.read_db_config() host = config.get('host') user = config.get('user') database = config.get('database') password = config.get('password') try: connection = mysql.connector.connect(host=host, user=user, database=database, password=password) cursor = connection.cursor() deleteAssignmentSQL = ("DELETE FROM dat_master " "WHERE assignment_id = %s " "AND c_comp = 0") deleteAssignmentValues = (self.AssignmentID, ) cursor.execute(deleteAssignmentSQL, deleteAssignmentValues) connection.commit() cursor.close() connection.close() except Exception as e: print(e) exc_type, exc_value, exc_traceback = sys.exc_info() lines = traceback.format_exception(exc_type, exc_value, exc_traceback) exceptionMsg = exc_value exceptionDetails = ''.join('!! ' + line for line in lines) GlobalFunctions.logExceptionStackTrace(exceptionMsg, exceptionDetails) hostLog.dbLog("Eby_AssignmentComplete", "Upd Err", self.AsciiRequestMessage) return False finally: cursor.close() connection.close()
def getDatMasterByContainerId(self, containerId): config = python_config.read_db_config() host = config.get('host') user = config.get('user') database = config.get('database') password = config.get('password') try: connection = mysql.connector.connect(host=host, user=user, database=database, password=password) cursor = connection.cursor() getByContainerIdSQL = "SELECT * FROM dat_master WHERE container_id = %s" selectData = (containerId, ) cursor.execute(getByContainerIdSQL, selectData) result = cursor.fetchone() cursor.close() connection.close() return result except Exception as e: print(e) #connection.rollback() exc_type, exc_value, exc_traceback = sys.exc_info() lines = traceback.format_exception(exc_type, exc_value, exc_traceback) exceptionMsg = exc_value exceptionDetails = ''.join('!! ' + line for line in lines) GlobalFunctions.logExceptionStackTrace(exceptionMsg, exceptionDetails) hostLog.dbLog("Eby_ContainerComplete", "Upd Err", self.AsciiRequestMessage) finally: cursor.close() connection.close()
def getMasterRecordByAssignmentId(self): config = python_config.read_db_config() host = config.get('host') user = config.get('user') database = config.get('database') password = config.get('password') try: connection = mysql.connector.connect( host= host, user= user, database= database, password= password ) cursor = connection.cursor(buffered=True) #getByContainerIdSQL = "SELECT * FROM dat_master WHERE container_id = %s" sql = "SELECT date FROM assignment.dat_master WHERE assignment_id=" + "'" + str(self.AssignmentID) + "'" #selectData = (self.ContainerID,) cursor.execute(sql) result = cursor.fetchone() cursor.close() connection.close() return result except Exception as e: print(e) #connection.rollback() exc_type, exc_value, exc_traceback = sys.exc_info() lines = traceback.format_exception(exc_type, exc_value, exc_traceback) exceptionMsg = exc_value exceptionDetails = ''.join('!! ' + line for line in lines) GlobalFunctions.logExceptionStackTrace(exceptionMsg, exceptionDetails) hostLog.dbLog("Eby_NewContainer", "Upd Err", self.AsciiRequestMessage) finally: cursor.close() connection.close()
def getDatFileRecordByContainerId(containerId): try: config = python_config.read_db_config() host = config.get('host') user = config.get('user') database = config.get('database') password = config.get('password') assignmentConnection = mysql.connector.connect(host=host, user=user, database=database, password=password) cursor = assignmentConnection.cursor() sql = "select * from dat_master where container_id = %s" queryValues = (containerId, ) cursor.execute(sql, queryValues) result = cursor.fetchone() if result == None: return "ContainerNotFound" cursor.close() assignmentConnection.close() return result except Exception as e: exc_type, exc_value, exc_traceback = sys.exc_info() lines = traceback.format_exception(exc_type, exc_value, exc_traceback) exceptionMsg = exc_value.msg exceptionDetails = ''.join('!! ' + line for line in lines) GlobalFunctions.logExceptionStackTrace(exceptionMsg, exceptionDetails)
import requests import API_02_HostLog as hostLog import API_03_httpStatusCodes as httpMessage import time import API_04_PLCLog as plcLog import requests import python_config import mysql.connector import datetime from pylogix import PLC import sys import atexit import Eby_DockScanPause as scanPause import Eby_02_DashboardModal as modal config = python_config.read_db_config() host = config.get('host') user = config.get('user') database = config.get('wcsdatabase') password = config.get('password') logging = python_config.read_logging_config() auth = logging.get('auth') domain = logging.get('domain') plcIP = "10.22.56.34" # Data Types STRUCT = 160 BOOL = 193 SINT = 194 INT = 195
def updateContainerAsComplete(self, connection): config = python_config.read_db_config() host = config.get('host') user = config.get('user') database = config.get('database') password = config.get('password') try: connection = mysql.connector.connect(host=host, user=user, database=database, password=password) cursor = connection.cursor() updateContainerSQL = ("UPDATE dat_master SET " "c_comp = %s, " "carton_qty = %s, " "qc_flag = %s, " "updated_at = %s " "WHERE container_id = %s ") #currentTimeStamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S') currentTimeStamp = datetime.now().strftime( '%Y-%m-%d %H:%M:%S.%f')[:-3] updateContainerValues = (1, self.CigaretteQuantity, int(self.QCFlag), currentTimeStamp, self.ContainerID) cursor.execute(updateContainerSQL, updateContainerValues) connection.commit() rowcount = cursor.rowcount print("Rows updated: " + str(rowcount)) #Web-72 datMaster = self.getDatMasterByContainerId(self.ContainerID) pickCode = datMaster[6][:3] if pickCode == "001": Eby_Jurisdiction_Processor.process(self.ContainerID) cursor.close() connection.close() if rowcount > 0: return True else: return False except Exception as e: print(e) exc_type, exc_value, exc_traceback = sys.exc_info() lines = traceback.format_exception(exc_type, exc_value, exc_traceback) exceptionMsg = exc_value exceptionDetails = ''.join('!! ' + line for line in lines) GlobalFunctions.logExceptionStackTrace(exceptionMsg, exceptionDetails) hostLog.dbLog("Eby_ContainerComplete", "Upd Err", self.AsciiRequestMessage) return False finally: cursor.close() connection.close()
def saveNewContainer(self): loggingConfig = python_config.read_logging_config() enabled = loggingConfig.get('enabled') auth = loggingConfig.get('auth') domain = loggingConfig.get('domain') existingRecord = self.doesNewContainerAlreadyExist() if existingRecord is not None: if enabled == "1": #the ContainerID hostLog.log(auth, domain, "HOST to WXS", "Dupl", existingRecord[2]) return config = python_config.read_db_config() host = config.get('host') user = config.get('user') database = config.get('database') password = config.get('password') date = None assignmentRecords = self.getMasterRecordByAssignmentId() if assignmentRecords is not None and len(assignmentRecords) > 0: date = assignmentRecords[0] # (rjw 2020-11-14 11:47) -- needed to add in assignment date based on original date of assignment drop in case this ADDCONTA comes after midnight # date = "SELECT date FROM assignment.dat_master WHERE assignment_id=" + "'" + str(self.AssignmentID) + "'" # cursor.execute(date) # date = str(cursor.fetchone()[0]) #print(date) try: connection = mysql.connector.connect( host= host, user= user, database= database, password= password ) cursor = connection.cursor() addNewContainerSQL = ("INSERT INTO dat_master " "(record_id, container_id, assignment_id, route_no, stop_no, pick_code, pick_type, jurisdiction, carton_qty, c_comp, a_comp, o_comp, r_comp, assign_name, status, date, created_at, updated_at) " "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)") currentTimeStamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S.%f')[:-3] newContainer = ( self.MessageID, self.ContainerID, self.AssignmentID, self.RouteNumber, self.StopNumber, self.PickArea, self.PickType, self.Jurisdiction, self.NumberCartons, 0, 0, 0, 0, 'SOCKET', 'Pending', date, currentTimeStamp, currentTimeStamp ) cursor.execute(addNewContainerSQL, newContainer) connection.commit() rowcount = cursor.rowcount print("Rows inserted: " + str(rowcount)) cursor.close() connection.close() return True except Exception as e: print(e) #connection.rollback() exc_type, exc_value, exc_traceback = sys.exc_info() lines = traceback.format_exception(exc_type, exc_value, exc_traceback) exceptionMsg = exc_value exceptionDetails = ''.join('!! ' + line for line in lines) GlobalFunctions.logExceptionStackTrace(exceptionMsg, exceptionDetails) hostLog.dbLog("Eby_NewContainer", "Upd Err", self.AsciiRequestMessage) return False finally: cursor.close() connection.close()