一种电子发票数据的模糊查询方法
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()