Function achieve （Cont.）

Item Search

• 添加一个新函数search_item，用于实现商品搜索的功能。
• 参数：keyword (为了模糊查询)

# search items by keywords
def search_item(keyword):cursor, db = connect_database()sql = f"SELECT * FROM item WHERE itemName LIKE '%{keyword}%'"results = ""try:cursor.execute(sql)results = cursor.fetchall()except:print("Error: unable to fetch data")db.close()return results

在OrderWindow类中添加一个新按钮和相应的槽函数，用于调用刚刚添加的search_item函数。

def search_item_button(self):# clean the widgetself.ui.searchResult_table.setRowCount(0)# set widget column as 4self.ui.searchResult_table.setColumnCount(4)self.ui.searchResult_table.setHorizontalHeaderLabels(['Item ID', 'Item Name', 'Price', 'Description'])# Horizontal autofillself.ui.searchResult_table.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)keyword = self.ui.searchInput_text.text()items = functions.search_item(keyword)for i in range(len(items)):item = items[i]row = self.ui.searchResult_table.rowCount()self.ui.searchResult_table.insertRow(row)for j in range(len(item)):item = QTableWidgetItem(str(items[i][j]))self.ui.searchResult_table.setItem(row, j, item)

Summary:

1. 名为search_item_button的按钮
2. 创建了一个名为search_item的新槽函数

Item Purchase

• 添加新函数add_purchase，用于实现商品购买的功能。
• 参数：customer_id, item_id

# Add purchase record to database
def add_purchase(customer_id, item_id):cursor, db = connect_database()sql = f"INSERT INTO purchase (customer_id, item_id) VALUES ({customer_id}, {item_id})"try:cursor.execute(sql)db.commit()db.close()return "Purchase added successfully."except:db.rollback()db.close()return "Error: Unable to add purchase record."

• 添加新函数get_all_purchases，用于实现order records查看的功能。

# Retrieve all purchases from database
def get_all_purchases():cursor, db = connect_database()sql = "SELECT customer.name, item.name FROM purchase INNER JOIN customer ON purchase.customer_id=customer.id INNER JOIN item ON purchase.item_id=item.id"cursor.execute(sql)results = cursor.fetchall()db.close()return results

在OrderWindow类中添加一个新按钮和相应的槽函数，用于调用刚刚添加的add_purchase函数和get_all_purchases函数。

def purchase_item(self):# Get selected customer and item from the UIselected_customer = self.ui.customerList.currentItem().text()selected_item = self.ui.itemList.currentItem().text()# Get the customer id and item id from the databasecustomer_id = functions.get_customer_id(selected_customer)item_id = functions.get_item_id(selected_item)# Add the purchase record to the databasemessage = functions.add_purchase(customer_id, item_id)QMessageBox.about(self, "Message", message)def show_all_purchases(self):#clean this widgetself.ui.purchase_table.setRowCount(0)#set widget colummn as 2self.ui.purchase_table.setColumnCount(2)self.ui.purchase_table.setHorizontalHeaderLabels(['Customer','Item'])#Horizontal autofillself.ui.purchase_table.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)purchases = functions.get_all_purchases()for i in range(len(purchases)):row = self.ui.purchase_table.rowCount()self.ui.purchase_table.insertRow(row)for j in range(len(purchases[i])):item = QTableWidgetItem(str(purchases[i][j]))self.ui.purchase_table.setItem(row, j, item)