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 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)
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()