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