def Connect_status(self):
try:
ConnectDatabase()
except Exception as error:
self.statusBar().showMessage('Failed connect to database.')
else:
self.statusBar().showMessage('Connected to database.')
ConnectDatabase().close()
def AddEntry_Data(self):
self.db = ConnectDatabase()
part_id = self.comboBoxNewEntryPart.currentText()
entry_employee = self.comboBoxNewEntryEmployee.currentText()
entry_status = self.comboBoxNewEntryStatus.currentText()
entry_quantity = self.spinBoxNewEntryQuantity.value()
entry_description = self.lineNewEntryDescription.toPlainText()
entry_date = self.dateNewEntryDate.text()
try:
self.cur = self.db.cursor()
self.cur.execute('SAVEPOINT SP1')
self.cur.execute(
'''INSERT INTO entry (part_id, entry_status, entry_qty, entry_desc, entry_date, entry_employee)
VALUES ((SELECT id FROM part WHERE part_name = %s) , %s , %s , %s , %s , %s)''',
(part_id, entry_status, entry_quantity, entry_description,
entry_date, entry_employee))
except Exception as error:
self.cur.execute('ROLLBACK TO SAVEPOINT SP1')
self.AddEntry_reset()
self.error_popup("Input Error", "Failed to add entry")
else:
self.cur.execute('RELEASE SAVEPOINT SP1')
self.db.commit()
self.AddEntry_reset()
self.success_popup("Entry", "New Entry Added.")
self.db.close()
def EditEntry_edit(self):
edit_id = int(self.lineEditEntryId.text())
entry_employee = self.comboBoxEditEntryEmployee.currentText()
entry_status = self.comboBoxEditEntryStatus.currentText()
entry_quantity = self.spinBoxEditEntryQuantity.value()
entry_date = self.dateEditEntryDate.text()
entry_description = self.lineEditEntryDescription.toPlainText()
self.db = ConnectDatabase()
try:
self.cur = self.db.cursor()
self.cur.execute('SAVEPOINT SP1')
try:
self.cur.execute('''UPDATE entry SET entry_status = %s, entry_qty = %s, entry_desc = %s, entry_date
= %s, entry_employee = %s WHERE id = %s''', (entry_status, entry_quantity, entry_description,
entry_date, entry_employee, edit_id))
except Exception as error:
self.cur.execute('ROLLBACK TO SAVEPOINT SP1')
self.error_popup("Input Error", "Fail to update Entry.")
else:
self.cur.execute('RELEASE SAVEPOINT SP1')
self.db.commit()
self.success_popup("Entry", "Successfully updated Entry.")
except Exception as error:
self.cur.execute('ROLLBACK TO SAVEPOINT SP1')
self.error_popup("Input Error", "Fail to connect database.")
self.db.close()
def EditPart_edit(self):
edit_id = int(self.lineEditPartId.text())
part_name = self.lineEditPartName.text()
part_department = self.comboBoxEditPartDepartment.currentText()
part_manufacturer = self.lineEditPartManufacturer.text()
part_model = self.lineEditPartModel.text()
part_category = self.comboBoxEditPartCategory.currentText()
part_storage = self.comboBoxEditPartStorage.currentText()
part_limit = self.spinBoxEditPartLimit.value()
part_description = self.lineEditPartDescription.toPlainText()
self.db = ConnectDatabase()
try:
self.cur = self.db.cursor()
self.cur.execute('SAVEPOINT SP1')
try:
self.cur.execute(
'''UPDATE part SET part_name = %s, part_department = %s, part_manufacturer = %s,
part_model = %s, part_category = %s, part_storage = %s, part_description = %s, part_stocklimit = %s WHERE
id = %s''', (part_name, part_department, part_manufacturer,
part_model, part_category, part_storage,
part_description, part_limit, edit_id))
except Exception as error:
self.cur.execute('ROLLBACK TO SAVEPOINT SP1')
self.error_popup("Input Error", "Fail to update Part.")
else:
self.cur.execute('RELEASE SAVEPOINT SP1')
self.db.commit()
self.success_popup("Part", "Successfully updated Part.")
except Exception as error:
self.cur.execute('ROLLBACK TO SAVEPOINT SP1')
self.error_popup("Input Error", "Fail to connect database.")
self.db.close()
def ListPart_show(self):
field_name = self.lineListPartName.text()
field_department = self.comboBoxListPartDepartment.currentText()
field_category = self.comboBoxListPartCategory.currentText()
field_storage = self.comboBoxListPartStorage.currentText()
# field_stocklevel = self.comboBoxListPartStockLevel.currentText()
search_name = '%{}%'.format(field_name)
search_department = '%{}%'.format(field_department)
search_category = '%{}%'.format(field_category)
search_storage = '%{}%'.format(field_storage)
# search_stocklevel = '%{}%'.format(field_stocklevel)
self.db = ConnectDatabase()
self.cur = self.db.cursor()
try:
self.cur.execute(
'''SELECT part.id, part.part_name, part.part_department, part.part_manufacturer, part.part_model, part.part_category, part.part_storage, part.part_stocklimit,
SUM ( ( CASE
WHEN entry.entry_status = 'In' THEN 1
ELSE -1 END)*entry.entry_qty) AS stock_quantity,
CASE
WHEN SUM( (CASE WHEN entry.entry_status = 'In' THEN 1 ELSE -1 END)* entry.entry_qty) < part.part_stocklimit/2 THEN 'Critical'
WHEN SUM( (CASE WHEN entry.entry_status = 'In' THEN 1 ELSE -1 END)* entry.entry_qty) < part.part_stocklimit THEN 'Low'
WHEN SUM( (CASE WHEN entry.entry_status = 'In' THEN 1 ELSE -1 END)* entry.entry_qty) >= part.part_stocklimit THEN 'Normal'
WHEN SUM( (CASE WHEN entry.entry_status = 'In' THEN 1 ELSE -1 END)* entry.entry_qty) > part.part_stocklimit*2 THEN 'High'
ELSE 'Normal' END AS stock_level,
part.part_description
FROM part
INNER JOIN entry ON entry.part_id = part.id
GROUP BY part.id
HAVING part.part_name ILIKE %s AND part.part_department ILIKE %s AND part.part_category ILIKE %s AND part.part_storage ILIKE %s ORDER BY part.id ASC''',
(search_name, search_department, search_category,
search_storage))
fetch_data = self.cur.fetchall()
except Exception as error:
print(error)
self.error_popup("Duplicate Error", str(error))
else:
if fetch_data is None:
self.tableWidgetListPart.setRowCount(0)
self.error_popup("List Part", "No records found!")
else:
self.tableWidgetListPart.horizontalHeader(
).setSectionResizeMode(0, 20)
self.tableWidgetListPart.horizontalHeader(
).setSectionResizeMode(1, 20)
self.tableWidgetListPart.setRowCount(0)
self.tableWidgetListPart.insertRow(0)
for row, form in enumerate(fetch_data):
for column, item in enumerate(form):
self.tableWidgetListPart.setItem(
row, column, QTableWidgetItem(str(item)))
column += 1
row_position = self.tableWidgetListPart.rowCount()
self.tableWidgetListPart.insertRow(row_position)
self.tableWidgetListPart.resizeColumnsToContents()
self.tableWidgetListPart.horizontalHeader(
).setSectionResizeMode(10, QHeaderView.Stretch)
self.db.close()
def ShowStatus_entry(self):
self.db = ConnectDatabase()
self.cur = self.db.cursor()
self.cur.execute('SELECT status_name FROM status ORDER BY status_name ASC')
status_list = self.cur.fetchall()
for status in status_list:
self.comboBoxEditEntryStatus.addItem(status[0])
self.db.close()
def ShowEmployee_custom(self):
self.db = ConnectDatabase()
self.cur = self.db.cursor()
self.cur.execute('SELECT employee_name FROM employee ORDER BY employee_name ASC')
employee_list = self.cur.fetchall()
for employee in employee_list:
self.comboBoxEditEntryEmployee.addItem(employee[0])
self.db.close()
def ShowName_part(self):
self.db = ConnectDatabase()
self.cur = self.db.cursor()
self.cur.execute('SELECT part_name FROM part ORDER BY part_name ASC')
part_list = self.cur.fetchall()
for part in part_list:
self.comboBoxNewEntryPart.addItem(part[0])
self.db.close()
def ShowCategory_custom(self):
self.db = ConnectDatabase()
self.cur = self.db.cursor()
self.cur.execute(
'SELECT category_name FROM category ORDER BY category_name ASC')
category_list = self.cur.fetchall()
for category in category_list:
self.comboBoxListPartCategory.addItem(category[0])
self.db.close()
def ShowStorage_custom(self):
self.db = ConnectDatabase()
self.cur = self.db.cursor()
self.cur.execute(
'SELECT storage_name FROM storage ORDER BY storage_name ASC')
storage_list = self.cur.fetchall()
for storage in storage_list:
self.comboBoxListPartStorage.addItem(storage[0])
self.db.close()
def ShowDepartment_custom(self):
self.db = ConnectDatabase()
self.cur = self.db.cursor()
self.cur.execute(
'SELECT department_name FROM department ORDER BY department_name ASC'
)
department_list = self.cur.fetchall()
for department in department_list:
self.comboBoxListEntryDepartment.addItem(department[0])
self.db.close()
def EditPart_search(self, part_id):
self.db = ConnectDatabase()
try:
self.cur = self.db.cursor()
self.cur.execute('''SELECT * FROM part WHERE id = %s''',
[(part_id, )])
fetch_data = self.cur.fetchone()
self.lineEditPartName.setText(fetch_data[1])
self.comboBoxEditPartDepartment.setCurrentText(fetch_data[2])
self.lineEditPartManufacturer.setText(fetch_data[3])
self.lineEditPartModel.setText(fetch_data[4])
self.comboBoxEditPartCategory.setCurrentText(fetch_data[5])
self.comboBoxEditPartStorage.setCurrentText(fetch_data[6])
self.spinBoxEditPartLimit.setValue(fetch_data[8])
self.lineEditPartDescription.setPlainText(fetch_data[7])
except Exception as error:
self.error_popup("Input Error", "Part not found.")
self.db.close()
def EditEntry_undo(self):
edit_id = int(self.lineEditEntryId.text())
self.db = ConnectDatabase()
try:
self.cur = self.db.cursor()
self.cur.execute('''SELECT entry.id, part.part_name, entry.entry_status, entry.entry_qty,
entry.entry_desc, entry.entry_date, entry.entry_employee FROM entry JOIN part ON entry.part_id = part.id
WHERE entry.id = %s''', [(edit_id,)])
fetch_data = self.cur.fetchone()
self.lineEditEntryName.setText(fetch_data[1])
self.comboBoxEditEntryStatus.setCurrentText(fetch_data[4])
self.spinBoxEditEntryQuantity.setValue(fetch_data[3])
self.lineEditEntryDescription.setPlainText(fetch_data[2])
self.dateEditEntryDate.setDate(fetch_data[5])
self.comboBoxEditEntryEmployee.setCurrentText(fetch_data[6])
except Exception as error:
self.error_popup("Input Error", "Failed to undo Entry.")
self.db.close()
def EditPart_undo(self):
edit_id = int(self.lineEditPartId.text())
self.db = ConnectDatabase()
try:
self.cur = self.db.cursor()
self.cur.execute('''SELECT * FROM part where id = %s''',
[(edit_id, )])
fetch_data = self.cur.fetchone()
self.lineEditPartName.setText(fetch_data[1])
self.comboBoxEditPartDepartment.setCurrentText(fetch_data[2])
self.lineEditPartManufacturer.setText(fetch_data[3])
self.lineEditPartModel.setText(fetch_data[4])
self.comboBoxEditPartCategory.setCurrentText(fetch_data[5])
self.comboBoxEditPartStorage.setCurrentText(fetch_data[6])
self.spinBoxEditPartLimit.setValue(fetch_data[8])
self.lineEditPartDescription.setPlainText(fetch_data[7])
except Exception as error:
self.error_popup("Input Error", "Failed to undo Part.")
self.db.close()
def EditEntry_delete(self):
edit_id = int(self.lineEditEntryId.text())
self.db = ConnectDatabase()
try:
self.cur = self.db.cursor()
self.cur.execute('SAVEPOINT SP1')
try:
self.cur.execute('''DELETE FROM entry WHERE id = %s''', [(edit_id,)])
except IntegrityError as error:
self.cur.execute('ROLLBACK TO SAVEPOINT SP1')
self.error_popup("Input Error", "Entry cannot be deleted due to part data of the entry exists.")
else:
self.cur.execute('RELEASE SAVEPOINT SP1')
self.db.commit()
self.success_popup("Entry", "Successfully deleted Entry.")
except Exception as error:
self.cur.execute('ROLLBACK TO SAVEPOINT SP1')
self.error_popup("Input Error", "Failed to delete Entry.")
self.db.close()
def SearchEditPart_search(self):
search_id = self.lineSearchEditId.text()
try:
part_id = int(search_id) # Convert text into integer
except Exception as error:
self.error_popup("Input Error", "Please enter numbers only.")
else:
self.db = ConnectDatabase()
self.cur = self.db.cursor()
self.cur.execute('SAVEPOINT SP1')
self.cur.execute('''SELECT * FROM part where id = %s''',
[(part_id, )]) # Check if part id available in db
fetch_data = self.cur.fetchone()
if fetch_data is None: # If returns None or Id not in database
self.cur.execute('ROLLBACK TO SAVEPOINT SP1')
self.error_popup("Invalid Input", "Part not registered.")
else:
self.EditPart_window(part_id)
self.db.close()
self.close()
def AddCategory_custom(self):
category_name = self.lineCustomCategory.text()
self.db = ConnectDatabase()
try:
self.cur = self.db.cursor()
self.cur.execute('SAVEPOINT SP1')
try:
self.cur.execute('INSERT INTO category (category_name) VALUES (%s)', (category_name,))
except psycopg2.IntegrityError:
self.cur.execute('ROLLBACK TO SAVEPOINT SP1')
self.lineCustomCategory.clear()
self.error_popup("Duplicate Input", "Category already exist. Please enter different input.")
else:
self.cur.execute('RELEASE SAVEPOINT SP1')
self.db.commit()
self.lineCustomCategory.clear()
self.success_popup("Category", "New Category Added.")
self.db.close()
except Exception as error:
self.lineCustomCategory.clear()
self.error_popup("Input Error", "Failed to add category")
def ListEntry_show(self):
field_name = self.lineListEntryPartName.text()
field_department = self.comboBoxListEntryDepartment.currentText()
field_status = self.comboBoxListEntryStatus.currentText()
search_name = '%{}%'.format(field_name)
search_department = '%{}%'.format(field_department)
search_status = '%{}%'.format(field_status)
self.db = ConnectDatabase()
self.cur = self.db.cursor()
try:
self.cur.execute(
'''SELECT entry.id, entry.entry_date, part.part_name, part.part_department,
entry.entry_status, entry.entry_qty, entry.entry_employee, entry.entry_desc FROM entry JOIN part ON
entry.part_id = part.id WHERE part.part_name ILIKE %s AND part.part_department ILIKE %s AND
entry.entry_status ILIKE %s ORDER BY entry.id ASC''',
(search_name, search_department, search_status))
fetch_data = self.cur.fetchall()
except Exception as error:
self.error_popup("Duplicate Error", "Failed to connect database")
else:
if fetch_data is None:
self.tableWidgetListEntry.setRowCount(0)
self.error_popup("List Entry", "No records found!")
else:
self.tableWidgetListEntry.setRowCount(0)
self.tableWidgetListEntry.insertRow(0)
for row, form in enumerate(fetch_data):
for column, item in enumerate(form):
self.tableWidgetListEntry.setItem(
row, column, QTableWidgetItem(str(item)))
column += 1
row_position = self.tableWidgetListEntry.rowCount()
self.tableWidgetListEntry.insertRow(row_position)
self.tableWidgetListEntry.resizeColumnsToContents()
self.tableWidgetListEntry.horizontalHeader(
).setSectionResizeMode(7, QHeaderView.Stretch)
self.db.close()
def AddPart_Data(self):
part_name = self.lineNewPartName.text()
part_department = self.comboBoxNewPartDepartment.currentText()
part_manufacturer = self.lineNewPartManufacturer.text()
part_model = self.lineNewPartModel.text()
part_category = self.comboBoxNewPartCategory.currentText()
part_storage = self.comboBoxNewPartStorage.currentText()
part_limit = self.spinBoxNewPartLimit.value()
part_description = self.lineNewPartDescription.toPlainText()
self.db = ConnectDatabase()
try:
self.cur = self.db.cursor()
self.cur.execute('SAVEPOINT SP1')
try:
self.cur.execute(
'''INSERT INTO part (part_name, part_department, part_manufacturer, part_model, part_category, part_storage, part_description, part_stocklimit)
VALUES (%s , %s , %s , %s , %s , %s , %s , %s)''',
(part_name, part_department, part_manufacturer, part_model,
part_category, part_storage, part_description,
part_limit))
except IntegrityError as error:
self.cur.execute('ROLLBACK TO SAVEPOINT SP1')
self.AddPart_reset()
self.error_popup(
"Duplicate Error",
"Name of part already exists! Please use different name")
else:
self.cur.execute('RELEASE SAVEPOINT SP1')
self.db.commit()
self.AddPart_reset()
self.success_popup("Part", "New Part Added.")
except Exception as error:
self.cur.execute('ROLLBACK TO SAVEPOINT SP1')
self.AddPart_reset()
self.error_popup("Input Error", "Failed to add part.")
self.db.close()