예제 #1
0
 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()
예제 #2
0
파일: AddEntry.py 프로젝트: Tenesh/PartLoop
    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()
예제 #3
0
 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()
예제 #4
0
 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()
예제 #5
0
    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()
예제 #6
0
 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()
예제 #7
0
 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()
예제 #8
0
파일: AddEntry.py 프로젝트: Tenesh/PartLoop
 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()
예제 #9
0
 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()
예제 #10
0
 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()
예제 #11
0
 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()
예제 #12
0
 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()
예제 #13
0
 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()
예제 #14
0
 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()
예제 #15
0
 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()
예제 #16
0
 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()
예제 #17
0
 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")
예제 #18
0
 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()
예제 #19
0
 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()