def saveButton_OnClick(self, evt):
db = DBManager
schema = db.load()[
3] # Load schema name from static load method in DBManager class
text = self.ed.GetText() # Get text from editor
query = Query()
# If logic to determine if this is a new note or not
if self.overwrite == False: # If it is a new note...
# SQL
sql = f"INSERT INTO {schema}.Note " \
f"VALUES ({self.id}, '{json.dumps(text)}')"
if query.genericQuery(sql, False) != 1: # Make query
msg = wx.MessageBox('Notes Added Successcully!', 'Success!')
else:
msg = wx.MessageBox(
'Something went wrong. Contact a system administrator immediately.',
'Error')
else: # Not a new note
sql = f"UPDATE Note SET text = '{json.dumps(text)}' WHERE ticketId = {self.id}"
if query.genericQuery(sql, False) != 1: # Make the query
msg = wx.MessageBox('Notes Updated Successcully!', 'Success!')
else:
msg = wx.MessageBox(
'Something went wrong. Contact a system administrator immediately.',
'Error')
self.Close()
def updateButton_OnClick(self, evt):
if self.GetListCtrl().GetFirstSelected() == -1:
pass
else:
query = Query()
rowid = self.GetListCtrl().GetFirstSelected()
userId = self.GetListCtrl().GetItemText(rowid, 0)
accessLevel = int(self.GetListCtrl().GetItemText(rowid, 6))
if self.passwdTxtCtrl.GetValue(
) != '': # User changed a password (field is not blank)
sql = f"UPDATE User SET username = '******', " \
f"firstName = '{self.firstNameTxtCtrl.GetValue()}', "\
f"lastName = '{self.lastNameTxtCtrl.GetValue()}', " \
f"passwordHash = '{Credentials.passwordHash(self.passwdTxtCtrl.GetValue())}', " \
f"department = '{self.departmentTxtCtrl.GetValue()}', isAdmin = {1 if self.GetAccessChoice(self.accessLevelChoice.GetStringSelection()) != 0 else 0}, " \
f"accessLevel = {self.GetAccessChoice(self.accessLevelChoice.GetStringSelection())} WHERE userId = {userId}"
else: # User didn't enter any password; Don't change it.
sql = f"UPDATE User SET username = '******', " \
f"firstName = '{self.firstNameTxtCtrl.GetValue()}', " \
f"lastName = '{self.lastNameTxtCtrl.GetValue()}', " \
f"department = '{self.departmentTxtCtrl.GetValue()}', isAdmin = {1 if self.GetAccessChoice(self.accessLevelChoice.GetStringSelection()) != 0 else 0}, " \
f"accessLevel = {self.GetAccessChoice(self.accessLevelChoice.GetStringSelection())} WHERE userId = {userId}"
if query.genericQuery(sql, False) == 1:
msg = wx.MessageBox(
'There was an issue with your query. Make sure all boxes are filled.',
'User Update Error')
else:
msg = wx.MessageBox('User Successfully Updated!',
'User Update Success')
self.refreshDB()
def QueryDBStatus(self):
query = Query()
sql = "SELECT * FROM User LIMIT 1"
result = query.genericQuery(sql, False)
if result != 1:
self.EmptyText.SetLabel('Database is up.')
else:
self.EmptyText.SetLabel('Database is down. Contact IT.')
def refreshDB(self):
query = Query()
result = query.genericQuery(
self.sql,
False) # Make query with SQL code passed to default constructor
#for item in result:
#self.queueListCtrl.Append(item[:])
ListFormat.listwriter(self, result)
def refreshDB(self):
self.userList.DeleteAllItems()
sql = f"SELECT userId, username, firstName, lastName, department, isAdmin, accessLevel FROM User"
query = Query()
users = query.genericQuery(sql, False)
for user in users:
if user[:][5] == b'\x01':
tup = user[:5] + ('Administrator', ) + user[6:]
else:
tup = user[:5] + ('Standard User', ) + user[6:]
self.userList.Append(tup)
def getNotes(self, id):
db = DBManager
schema = db.load()[3]
query = Query()
sql = f"SELECT text " \
f"FROM {schema}.Note " \
f"WHERE ticketId = {id}"
try:
result = query.genericQuery(sql, False)
return json.loads(result[0][0])
except:
return 1
def listItem_OnClick(self, evt):
rowid = self.queueListCtrl.GetFirstSelected()
id = self.queueListCtrl.GetItemText(rowid, 0) # Get row id
print(id)
query = Query()
desc = query.getDescById(id)[0][0] # Make query
# Create DescViewer object called win
win = DescViewer(self,
'Description Viewer',
desc=desc,
pos=wx.DefaultPosition,
size=(430, 430),
style=wx.DEFAULT_FRAME_STYLE)
win.Show(True) # Display win
win.SetFocus()
def addUser_OnClick(self, evt):
query = Query()
sql = f"INSERT INTO User VALUES (NULL, '{self.usernameTxtCtrl.GetValue()}', " \
f"'{self.firstNameTxtCtrl.GetValue()}', '{self.lastNameTxtCtrl.GetValue()}', " \
f"'{Credentials.passwordHash(self.passwdTxtCtrl.GetValue())}', " \
f"'{self.departmentTxtCtrl.GetValue()}', " \
f"{1 if self.GetAccessChoice(self.accessLevelChoice.GetStringSelection()) != 0 else 0}, " \
f"{self.GetAccessChoice(self.accessLevelChoice.GetStringSelection())});"
if query.genericQuery(sql, False) == 1:
msg = wx.MessageBox(
'There was an issue with your query. Make sure all boxes are filled.',
'Add User Error')
else:
msg = wx.MessageBox('User Successfully Added!', 'Add User Success')
self.refreshDB()
def passwordHasher(username, passwd):
query = Query()
shaObj = hashlib.sha256()
shaObj.update(passwd.encode())
print(shaObj.hexdigest())
result = query.userValidate(username, shaObj.hexdigest())
if result == 0:
return 0
elif result == 1:
return 1
elif result == 2:
return "Password is incorrect"
elif result == 3:
return "Username is incorrect"
else:
return "That user account is disabled. Contact IT if this is an error."
def removeUser_OnClick(self, evt):
if self.GetListCtrl().GetFirstSelected() == -1:
pass
else:
query = Query()
rowid = self.GetListCtrl().GetFirstSelected()
userId = self.GetListCtrl().GetItemText(rowid, 0)
sql = f'UPDATE User SET accessLevel = 0 WHERE userId = {userId}'
if query.genericQuery(sql, False) == 1:
msg = wx.MessageBox(
'There was an issue with your query. Make sure all boxes are filled.',
'Disable User Error')
else:
msg = wx.MessageBox('User Successfully Disabled!',
'Disable User Success')
self.refreshDB()
def generateLayout(self, start, end):
printHead = f"For date range {start} - {end}:\n\n"
totalJobs = 0
completed = 0
submitted = 0
halted = 0
inProgress = 0
query = Query()
sql = f"SELECT * FROM Support_Ticket WHERE submitDate BETWEEN '{start}' AND '{end}'"
result = query.genericQuery(sql, False)
resultSize = len(result)
self.gauge.SetRange(resultSize)
if result != 1:
for item in result:
totalJobs += 1
if item[:][4] == 'Completed':
completed += 1
elif item[:][4] == 'In Progress':
inProgress += 1
elif item[:][4] == 'Halted':
halted += 1
else:
submitted += 1
printHead += f"Total Submitted Jobs: {totalJobs}\n\n" \
f"Job Status\n" \
f"----------------------------------------\n" \
f"Submitted: {submitted}\n" \
f"In Progress: {inProgress}\n" \
f"Halted: {halted}\n" \
f"Completed: {completed}\n\n"
if self.RadioButton0.GetValue() == True: # Numerical Report
PrintMe.printPreview(self, printHead)
elif self.RadioButton1.GetValue() == True:
if self.CheckBox0.IsChecked():
printHead += f"\nCompleted Tickets: {completed}\n" \
"ID NAME DATE/TIME CATEGORY COMPLETED BY\n" \
"----- ------------ ------------------- --------------- ----------------\n"
for item in result:
if item[:][4] == 'Completed':
printHead += f"{str(item[:][0]):5} {Credentials.getUsername(item[:][1]):12} " \
f"{str(item[:][2]):19} {item[:][3]:15} " \
f"{Credentials.getUsername(item[:][6]):16}\n"
self.updateProgress(resultSize)
if self.CheckBox1.IsChecked():
printHead += f"\nIncomplete Tickets: {submitted + halted + inProgress}\n" \
"ID NAME DATE/TIME CATEGORY\n" \
"----- ------------ ------------------- ---------------\n"
for item in result:
if item[:][4] != 'Completed':
printHead += f"{str(item[:][0]):5} {Credentials.getUsername(item[:][1]):12} " \
f"{str(item[:][2]):19} {item[:][3]:15}\n"
self.updateProgress(resultSize)
if self.CheckBox2.IsChecked():
graphBarChar = u'\u2587'
maxValue = max([submitted, inProgress, halted, completed])
scaleFactor = 30 / maxValue if maxValue > 0 else 1
printHead += f"\nGraphs\n" \
f"-------------\n" \
f"Submitted: |{graphBarChar * int(submitted * scaleFactor)}\n\n" \
f"In Progress:|{graphBarChar * int(inProgress * scaleFactor)}\n\n" \
f"Halted: |{graphBarChar * int(halted * scaleFactor)}\n\n" \
f"Completed: |{graphBarChar * int(completed * scaleFactor)}\n" \
f"-------------"
print(printHead)
win = DescViewer(self,
f"Generated Report",
printHead,
size=self.Size)
win.Show()
win.SetFocus()
else:
msg = wx.MessageBox(
'There was an error generating the report. Try altering the date range and try again.',
'Report Error')
def getUsername(userId):
query = Query()
sql = f"SELECT username FROM User WHERE userId = {userId}"
username = query.genericQuery(sql, False)
return username[0][0]
def getUserId(username):
query = Query()
sql = f"SELECT userId FROM User WHERE username = '******'"
userId = query.genericQuery(sql, False)
return userId