一种电子发票数据的模糊查询方法

1、比如下面的所有字段，通过模糊查询，主要任何字段能模糊匹配都可以查询出来

2、python实现方法

# 查询发票
    def query_statistics_table(self):
        query_text = self.query_input.text().strip()
        query_field = self.query_select.currentText() # 获取用户选择的查询字段

        # 字段映射关系
        field_mapping = {
            "全部": "全部",
            "开票日期": "date",
            "购买方名称": "buyer_name",
            "销售方名称": "seller_name",
            "货物名称": "items",
            "发票号码": "invoice_no", 
            "发票类型": "invoice_type",
            "报销日期": "reimbur_date",
            "报销部门": "reimbur_dep",
            "报销人": "reimbur_user",
            "凭证号": "voucher_no",
            "入库时间": "created_at"
        }

        if  query_field == "全部":
            if query_text:
                results = self.database.query_invoices_by_all_fields(query_text)
            else:
                results =  self.database.query_all_invoices()
        else:
            field = field_mapping.get(query_field)
            if field:
                results = self.database.query_invoices_by_field(field, query_text)
            else:
                results = []
        if not results:
            QMessageBox.information(self, "提示", "没有找到相关数据。")
            self.query_all_invoices() #  查询无结果时，也重新显示所有发票 (可选，可根据需求调整)
            return
        self.update_table_data(results) #  调用 update_table_data 方法更新表格数据
    # 查询所有发票
    def query_all_invoices(self):
        results = self.database.query_all_invoices()
        if not results:
            QMessageBox.information(self, "提示", "没有数据可以显示。")
            return
        self.update_table_data(results) # 调用 update_table_data 方法更新表格数据

def update_table_data(self, results):
        """更新表格数据，避免重复代码, 并更新统计信息"""
        self.statistics_table.clearContents()
        self.statistics_table.setRowCount(len(results))

        total_amount = 0 #  初始化金额合计
        for row, (invoice_id, date, buyer_name, seller_name, items, pretax_amount, 
            tax_rate, tax, amount, invoice_no, invoice_type, reimbur_date, 
            reimbur_dep, reimbur_user, voucher_no, created_at) in enumerate(results): #  !!!  SQL 查询结果需要包含 invoice_id  !!!
            self.statistics_table.setItem(row, 0, QTableWidgetItem(date))
            self.statistics_table.setItem(row, 1, QTableWidgetItem(buyer_name))
            self.statistics_table.setItem(row, 2, QTableWidgetItem(seller_name))
            self.statistics_table.setItem(row, 3, QTableWidgetItem(items))
            
            pretax_amount_str = str(pretax_amount) # 先将 pretax_amount 转换为字符串
            if pretax_amount_str.startswith("¥"): # 判断字符串是否以 "¥" 开头
                pretax_amount_str = pretax_amount_str[1:] # 如果是，则去除第一个字符 "¥"
            try:
                pretax_amount_val = float(pretax_amount_str) if pretax_amount else 0 # 尝试将去除 "¥" 后的字符串转换为 float
            except ValueError: #  捕获转换失败的异常，例如去除 "¥" 后仍然不是有效数字
                pretax_amount_val = 0 #  转换失败时，将金额置为 0，并可以考虑添加日志或提示
                logging.info(f"警告: 金额转换失败，发票日期: {date}, 金额字符串: {pretax_amount_str}") #  打印警告信息，方便调试
            self.statistics_table.setItem(row, 4, QTableWidgetItem(str(pretax_amount_val)))

            self.statistics_table.setItem(row, 5, QTableWidgetItem(tax_rate))

            tax_str = str(tax) # 先将 tax 转换为字符串
            if tax_str.startswith("¥"): # 判断字符串是否以 "¥" 开头
                tax_str = tax_str[1:] # 如果是，则去除第一个字符 "¥"
            try:
                tax_val = float(tax_str) if tax else 0 # 尝试将去除 "¥" 后的字符串转换为 float
            except ValueError: #  捕获转换失败的异常，例如去除 "¥" 后仍然不是有效数字
                tax_val = 0 #  转换失败时，将金额置为 0，并可以考虑添加日志或提示
                logging.info(f"警告: 金额转换失败，发票日期: {date}, 金额字符串: {tax_str}") #  打印警告信息，方便调试
            self.statistics_table.setItem(row, 6, QTableWidgetItem(str(tax_val)))
            
            amount_str = str(amount) # 先将 amount 转换为字符串
            if amount_str.startswith("¥"): # 判断字符串是否以 "¥" 开头
                amount_str = amount_str[1:] # 如果是，则去除第一个字符 "¥"
            try:
                amount_val = float(amount_str) if amount else 0 # 尝试将去除 "¥" 后的字符串转换为 float
            except ValueError: #  捕获转换失败的异常，例如去除 "¥" 后仍然不是有效数字
                amount_val = 0 #  转换失败时，将金额置为 0，并可以考虑添加日志或提示
                logging.info(f"警告: 金额转换失败，发票日期: {date}, 金额字符串: {amount_str}") #  打印警告信息，方便调试
            self.statistics_table.setItem(row, 7, QTableWidgetItem(str(amount_val)))
            self.statistics_table.setItem(row, 8, QTableWidgetItem(invoice_no))
            self.statistics_table.setItem(row, 9, QTableWidgetItem(invoice_type))
            self.statistics_table.setItem(row, 10, QTableWidgetItem(reimbur_date))
            self.statistics_table.setItem(row, 11, QTableWidgetItem(reimbur_dep))
            self.statistics_table.setItem(row, 12, QTableWidgetItem(reimbur_user))
            self.statistics_table.setItem(row, 13, QTableWidgetItem(voucher_no))
            item_created_at = QTableWidgetItem(created_at) #  创建 "入库时间" 列的 QTableWidgetItem
            item_created_at.setTextAlignment(Qt.AlignmentFlag.AlignCenter) #  !!!  设置单元格内容居中对齐  !!!
            self.statistics_table.setItem(row, 14, item_created_at) #  设置 "入库时间" 列的单元格

            #  !!!  获取并显示标记信息  !!!
            marks = self.database.get_marks_for_invoice_id(invoice_id) #  !!!  调用 get_marks_for_invoice_id 方法获取标记  !!!
            mark_names = ", ".join([mark['name'] for mark in marks]) #  将标记名称连接成字符串，用逗号分隔
            item_marks = QTableWidgetItem(mark_names) #  创建 "标记信息" 列的 QTableWidgetItem
            item_marks.setTextAlignment(Qt.AlignmentFlag.AlignCenter)  # !!!  设置单元格内容居中对齐  !!!
            self.statistics_table.setItem(row, 15, item_marks) #  设置 "标记信息" 列的单元格 

3、相应的数据库语句代码如下：

def query_all_invoices(self):
        sql = """SELECT 
            id, date, buyer_name, seller_name, items,
            pretax_amount, tax_rate, tax, amount, invoice_no,
            invoice_type, reimbur_date, reimbur_dep, reimbur_user,
            voucher_no, created_at
            FROM invoices"""
        self.cursor.execute(sql)
        return self.cursor.fetchall()

    def query_invoices_by_field(self, field, value):
        sql = f"""SELECT 
            id, date, buyer_name, seller_name, items,
            pretax_amount, tax_rate, tax, amount, invoice_no,
            invoice_type, reimbur_date, reimbur_dep, reimbur_user,
            voucher_no, created_at
            FROM invoices
            WHERE {field} LIKE ?"""
        self.cursor.execute(sql, (f"%{value}%",))
        return self.cursor.fetchall()

    def query_invoices_by_all_fields(self, keyword):
        sql = """SELECT 
            id, date, buyer_name, seller_name, items,
            pretax_amount, tax_rate, tax, amount, invoice_no,
            invoice_type, reimbur_date, reimbur_dep, reimbur_user,
            voucher_no, created_at
            FROM invoices
            WHERE buyer_name LIKE ? OR seller_name LIKE ? OR items LIKE ? OR reimbur_dep LIKE ? OR reimbur_user LIKE ? """
        self.cursor.execute(sql, (f"%{keyword}%",)*5)  # 根据实际参数数量调整
        return self.cursor.fetchall()