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