Python Pyside6 加Sqlite3 写一个 通用 进销存 系统 初型
图：
说明：
进销存管理系统说明文档
功能模块
1. 首页
- 显示关键业务数据
-
- 商品总数
- 供应商总数
- 本月采购金额
- 本月销售金额
- 显示预警信息
-
- 库存不足预警
- 待付款采购单
- 待收款销售单
2. 商品管理
- 商品信息维护
-
- 商品编码（唯一标识）
- 商品名称
- 规格型号
- 单位
- 分类
- 进货价
- 销售价
- 库存数量
- 预警数量
- 支持批量导入导出
- 支持搜索和筛选
3. 供应商管理
- 供应商信息维护
-
- 供应商名称（唯一标识）
- 联系人
- 联系电话
- 地址
- 账期
- 支持批量导入导出
- 支持搜索和筛选
4. 客户管理
- 客户信息维护
-
- 客户名称（唯一标识）
- 联系人
- 联系电话
- 地址
- 电子邮箱
- 备注信息
- 基本操作
-
- 新建客户：添加新的客户信息
- 编辑客户：修改现有客户信息
- 删除客户：删除未使用的客户（已有销售订单的客户不能删除）
- 搜索客户：按名称、联系人、电话等信息搜索
- 数据导入导出
-
- 支持从Excel导入客户数据
- 支持导出客户数据到Excel
- 导入时自动处理重复数据
- 数据验证
-
- 客户名称不能重复
- 删除前检查销售订单关联
- 导入数据格式验证
5. 采购管理
- 采购单管理
-
- 新建采购单
- 编辑采购单
- 删除采购单
- 查看采购历史
- 采购单信息
-
- 采购单号（自动生成）
- 供应商
- 采购日期
- 付款状态
- 入库状态
- 商品明细
-
-
- 商品
- 数量
- 单价
- 金额
-
- 支持搜索和筛选
6. 销售管理
- 销售单管理
-
- 新建销售单
- 编辑销售单
- 删除销售单
- 查看销售历史
- 销售单信息
-
- 销售单号（自动生成）
- 客户信息
- 销售日期
- 收款状态
- 出库状态
- 商品明细
-
-
- 商品
- 数量
- 单价
- 金额
-
- 支持搜索和筛选
7. 库存管理
- 库存状态查看
-
- 商品名称
- 当前库存
- 预警库存
- 库存状态
- 库存调整
-
- 手动入库
- 手动出库
- 调整原因记录
- 变动历史查询
-
- 调整时间
- 调整类型
- 调整数量
- 调整原因
- 支持搜索和筛选
8. 系统备份
- 数据备份功能
-
- 创建备份：将当前数据库导出为备份文件
- 自动生成包含时间戳的文件名
- 可选择备份文件保存位置
- 数据恢复功能
-
- 从备份文件恢复数据
- 恢复前自动创建当前数据的备份
- 支持确认提示，防止误操作
- 使用建议
-
- 定期创建数据备份
- 将备份文件保存在不同的存储设备上
- 重要操作前先创建备份
- 系统升级前创建备份
财务管理
财务管理模块提供了全面的财务操作和分析功能，包括收付款管理、财务统计、应收应付对账等功能。
收付款记录
- 新建收款：记录客户的付款信息
-
- 选择关联销售单号
- 输入收款金额
- 选择支付方式（现金/银行转账/支付宝/微信）
- 添加备注信息
- 新建付款：记录向供应商的付款信息
-
- 选择关联采购单号
- 输入付款金额
- 选择支付方式
- 添加备注信息
- 搜索功能：支持按关键字搜索收付款记录
财务统计
- 时间范围选择：
-
- 本月
- 上月
- 最近7天
- 最近30天
- 全部
- 统计指标：
-
- 总收入
- 总支出
- 净收入
应收对账
- 客户应收账款管理：
-
- 按客户查看应收账款
- 时间筛选（全部/本月/上月/本年/上年）
- 显示销售总额、已收金额、应收金额
- 详细的销售订单列表（含收款状态）
- 导出功能：
-
- 导出Excel格式的对账单
- 支持打印对账单（含打印预览）
应付对账
- 供应商应付账款管理：
-
- 按供应商查看应付账款
- 时间筛选（全部/本月/上月/本年/上年）
- 显示采购总额、已付金额、应付金额
- 详细的采购订单列表（含付款状态）
- 导出功能：
-
- 导出Excel格式的对账单
- 支持打印对账单（含打印预览）
使用说明
- 收付款操作：
-
- 点击"新建收款"或"新建付款"按钮
- 选择关联单号，系统会自动填充相关金额
- 填写实际收付款金额和支付方式
- 可添加备注信息
- 确认保存
- 财务统计：
-
- 选择要查看的时间范围
- 系统自动计算并显示收入、支出和净收入
- 应收对账：
-
- 选择客户和时间范围
- 查看应收账款汇总和明细
- 可导出Excel或打印对账单
- 应付对账：
-
- 选择供应商和时间范围
- 查看应付账款汇总和明细
- 可导出Excel或打印对账单
使用说明
1. 系统初始化
- 首次使用请先维护基础数据：
-
- 添加商品信息
- 添加供应商信息
- 添加客户信息
- 设置商品库存预警值
2. 日常操作
- 采购入库
-
- 新建采购单
- 选择供应商
- 添加商品明细
- 设置入库状态
- 销售出库
-
- 新建销售单
- 选择客户
- 添加商品明细
- 设置出库状态
- 库存管理
-
- 定期盘点库存
- 及时处理库存预警
- 系统维护
-
- 及时备份数据
- 定期检查系统运行状态
3. 数据备份
- 定期备份
-
- 进入"系统备份"页面
- 点击"创建备份"按钮
- 选择备份文件保存位置
- 确认备份成功
- 数据恢复
-
- 进入"系统备份"页面
- 点击"恢复备份"按钮
- 选择要恢复的备份文件
- 确认恢复操作
- 等待恢复完成
注意事项
- 数据安全
-
- 定期备份数据库
- 将备份文件保存在安全的位置
- 定期检查备份文件的完整性
- 重要操作前先创建备份
- 定期备份数据库
- 妥善保管系统账号密码
- 操作规范
-
- 严格按照操作流程执行
- 认真核对数据后再保存
- 异常处理
-
- 遇到问题及时记录
- 系统故障及时报修
代码：
主窗口 main.py
import sys
from PySide6.QtWidgets import (QApplication, QMainWindow, QWidget, QVBoxLayout,
QHBoxLayout, QTabWidget, QPushButton, QLabel)
from PySide6.QtCore import Qt
from database import Database
from products import ProductsTab
from dashboard import DashboardTab
from suppliers import SuppliersTab
from customers import CustomersTab
from purchase import PurchaseTab
from sales import SalesTab
from inventory import InventoryTab
from finance import FinanceTab
from backup import BackupWidget
class MainWindow(QMainWindow):
def __init__(self, db):
super().__init__()
self.db = db
self.init_ui()
def init_ui(self):
self.setWindowTitle('进销存管理系统')
self.resize(1200, 800)
# Create tab widget
tab_widget = QTabWidget()
tab_widget.addTab(ProductsTab(self.db), '商品管理')
tab_widget.addTab(SuppliersTab(self.db), '供应商管理')
tab_widget.addTab(CustomersTab(self.db), '客户管理')
tab_widget.addTab(PurchaseTab(self.db), '采购管理')
tab_widget.addTab(SalesTab(self.db), '销售管理')
tab_widget.addTab(InventoryTab(self.db), '库存管理')
tab_widget.addTab(FinanceTab(self.db), '财务管理')
tab_widget.addTab(BackupWidget('inventory.db'), '系统备份')
self.setCentralWidget(tab_widget)
def main():
app = QApplication(sys.argv)
db = Database() # Create database instance
window = MainWindow(db) # Pass database instance to MainWindow
window.show()
sys.exit(app.exec())
if __name__ == '__main__':
main() 
商品管理 products.py
from PySide6.QtWidgets import (QWidget, QVBoxLayout, QHBoxLayout, QPushButton,
QLabel, QLineEdit, QTableWidget, QTableWidgetItem,
QMessageBox, QHeaderView, QFileDialog, QDialog,
QFormLayout, QDoubleSpinBox, QSpinBox)
from PySide6.QtCore import Qt
import openpyxl
from datetime import datetime
import os
class ProductEditDialog(QDialog):
def __init__(self, product=None, parent=None):
super().__init__(parent)
self.product = product
self.init_ui()
def init_ui(self):
self.setWindowTitle('编辑商品' if self.product else '新增商品')
layout = QFormLayout(self)
# Create input fields
self.code = QLineEdit(self.product[0] if self.product else '')
self.name = QLineEdit(self.product[1] if self.product else '')
self.specification = QLineEdit(self.product[2] if self.product else '')
self.unit = QLineEdit(self.product[3] if self.product else '')
self.category = QLineEdit(self.product[4] if self.product else '')
self.cost_price = QDoubleSpinBox()
self.cost_price.setMaximum(999999.99)
self.cost_price.setDecimals(2)
if self.product:
self.cost_price.setValue(float(self.product[5]))
self.selling_price = QDoubleSpinBox()
self.selling_price.setMaximum(999999.99)
self.selling_price.setDecimals(2)
if self.product:
self.selling_price.setValue(float(self.product[6]))
self.stock_quantity = QSpinBox()
self.stock_quantity.setMaximum(999999)
if self.product:
self.stock_quantity.setValue(int(self.product[7]))
self.warning_quantity = QSpinBox()
self.warning_quantity.setMaximum(999999)
if self.product:
self.warning_quantity.setValue(int(self.product[8]))
# Add fields to layout
layout.addRow('商品编码:', self.code)
layout.addRow('商品名称:', self.name)
layout.addRow('规格型号:', self.specification)
layout.addRow('单位:', self.unit)
layout.addRow('分类:', self.category)
layout.addRow('成本价:', self.cost_price)
layout.addRow('销售价:', self.selling_price)
layout.addRow('库存数量:', self.stock_quantity)
layout.addRow('库存预警值:', self.warning_quantity)
# Add buttons
buttons_layout = QHBoxLayout()
save_btn = QPushButton('保存')
save_btn.clicked.connect(self.accept)
cancel_btn = QPushButton('取消')
cancel_btn.clicked.connect(self.reject)
buttons_layout.addWidget(save_btn)
buttons_layout.addWidget(cancel_btn)
layout.addRow(buttons_layout)
def get_data(self):
return [
self.code.text(),
self.name.text(),
self.specification.text(),
self.unit.text(),
self.category.text(),
str(self.cost_price.value()),
str(self.selling_price.value()),
str(self.stock_quantity.value()),
str(self.warning_quantity.value())
]
class ProductsTab(QWidget):
def __init__(self, db):
super().__init__()
self.db = db
self.init_ui()
self.load_products()
self.editing = False
def init_ui(self):
layout = QVBoxLayout(self)
# Create top toolbar
toolbar = QHBoxLayout()
# Add product button
self.add_btn = QPushButton('添加商品')
self.add_btn.clicked.connect(self.add_product)
toolbar.addWidget(self.add_btn)
# Edit product button
self.edit_btn = QPushButton('编辑商品')
self.edit_btn.clicked.connect(self.edit_product)
toolbar.addWidget(self.edit_btn)
# Delete product button
self.delete_btn = QPushButton('删除商品')
self.delete_btn.clicked.connect(self.delete_product)
toolbar.addWidget(self.delete_btn)
# Export button
self.export_btn = QPushButton('导出Excel')
self.export_btn.clicked.connect(self.export_to_excel)
toolbar.addWidget(self.export_btn)
# Import button
self.import_btn = QPushButton('导入Excel')
self.import_btn.clicked.connect(self.import_from_excel)
toolbar.addWidget(self.import_btn)
# Search box
self.search_input = QLineEdit()
self.search_input.setPlaceholderText('搜索商品...')
self.search_input.textChanged.connect(self.search_products)
toolbar.addWidget(self.search_input)
toolbar.addStretch()
layout.addLayout(toolbar)
# Create table
self.table = QTableWidget()
self.table.setColumnCount(9)
self.table.setHorizontalHeaderLabels([
'商品编码', '商品名称', '规格型号', '单位', '分类',
'成本价', '销售价', '库存数量', '库存预警值'
])
header = self.table.horizontalHeader()
header.setSectionResizeMode(QHeaderView.Stretch)
# Enable selection of entire rows
self.table.setSelectionBehavior(QTableWidget.SelectRows)
self.table.setSelectionMode(QTableWidget.SingleSelection)
layout.addWidget(self.table)
def export_to_excel(self):
try:
# Create a new workbook and select the active sheet
wb = openpyxl.Workbook()
ws = wb.active
ws.title = "商品列表"
# Write headers
headers = [
'商品编码', '商品名称', '规格型号', '单位', '分类',
'成本价', '销售价', '库存数量', '库存预警值'
]
for col, header in enumerate(headers, 1):
ws.cell(row=1, column=col, value=header)
# Write data
for row in range(self.table.rowCount()):
for col in range(self.table.columnCount()):
item = self.table.item(row, col)
value = item.text() if item else ''
ws.cell(row=row+2, column=col+1, value=value)
# Get save file name
file_name = f"商品列表_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"
file_path, _ = QFileDialog.getSaveFileName(
self, "导出Excel", file_name, "Excel Files (*.xlsx)")
if file_path:
wb.save(file_path)
QMessageBox.information(self, "成功", "导出成功！")
except Exception as e:
QMessageBox.warning(self, "错误", f"导出失败: {str(e)}")
def import_from_excel(self):
try:
# Get file name
file_path, _ = QFileDialog.getOpenFileName(
self, "选择Excel文件", "", "Excel Files (*.xlsx)")
if not file_path:
return
# Load workbook
wb = openpyxl.load_workbook(file_path)
ws = wb.active
# Get all rows
rows = list(ws.rows)
if len(rows) < 2: # Check if file has data (header + at least one row)
QMessageBox.warning(self, "错误", "Excel文件为空或格式不正确")
return
# Verify headers
expected_headers = [
'商品编码', '商品名称', '规格型号', '单位', '分类',
'成本价', '销售价', '库存数量', '库存预警值'
]
headers = [cell.value for cell in rows[0]]
if headers != expected_headers:
QMessageBox.warning(self, "错误", "Excel文件格式不正确，请使用导出的模板")
return
# Begin transaction
conn = self.db.connect()
cursor = conn.cursor()
try:
# Process each row
for row in rows[1:]: # Skip header row
values = [cell.value for cell in row]
# Convert numeric values
values[5] = float(values[5]) if values[5] else 0 # cost_price
values[6] = float(values[6]) if values[6] else 0 # selling_price
values[7] = int(values[7]) if values[7] else 0 # stock_quantity
values[8] = int(values[8]) if values[8] else 0 # warning_quantity
# Insert or update product
query = '''INSERT OR REPLACE INTO products
(code, name, specification, unit, category,
cost_price, selling_price, stock_quantity, warning_quantity)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)'''
cursor.execute(query, values)
conn.commit()
QMessageBox.information(self, "成功", "导入成功！")
self.load_products() # Refresh table
except Exception as e:
conn.rollback()
raise e
except Exception as e:
QMessageBox.warning(self, "错误", f"导入失败: {str(e)}")
def load_products(self):
self.table.setRowCount(0)
query = '''SELECT code, name, specification, unit, category,
cost_price, selling_price, stock_quantity, warning_quantity
FROM products'''
products = self.db.fetch_query(query)
for row, product in enumerate(products):
self.table.insertRow(row)
for col, value in enumerate(product):
item = QTableWidgetItem(str(value) if value is not None else '')
self.table.setItem(row, col, item)
def add_product(self):
dialog = ProductEditDialog(parent=self)
if dialog.exec_():
values = dialog.get_data()
try:
# Check if product code exists
cursor = self.db.connect().cursor()
cursor.execute("SELECT code FROM products WHERE code = ?", (values[0],))
if cursor.fetchone():
QMessageBox.warning(self, "错误", f"商品编码 '{values[0]}' 已存在")
return
# Insert new product
query = '''INSERT INTO products
(code, name, specification, unit, category,
cost_price, selling_price, stock_quantity, warning_quantity)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)'''
self.db.execute_query(query, tuple(values))
self.load_products()
except Exception as e:
QMessageBox.warning(self, '错误', f'保存失败: {str(e)}')
def edit_product(self):
current_row = self.table.currentRow()
if current_row < 0:
QMessageBox.warning(self, "提示", "请先选择要编辑的商品")
return
# Get current product data
values = []
for col in range(self.table.columnCount()):
item = self.table.item(current_row, col)
values.append(item.text() if item else '')
dialog = ProductEditDialog(values, self)
if dialog.exec_():
new_values = dialog.get_data()
try:
# Check if new code exists (if code was changed)
if new_values[0] != values[0]:
cursor = self.db.connect().cursor()
cursor.execute("SELECT code FROM products WHERE code = ?", (new_values[0],))
if cursor.fetchone():
QMessageBox.warning(self, "错误", f"商品编码 '{new_values[0]}' 已存在")
return
# Update product
query = '''UPDATE products
SET code = ?, name = ?, specification = ?, unit = ?,
category = ?, cost_price = ?, selling_price = ?,
stock_quantity = ?, warning_quantity = ?
WHERE code = ?'''
self.db.execute_query(query, tuple(new_values + [values[0]]))
self.load_products()
except Exception as e:
QMessageBox.warning(self, '错误', f'保存失败: {str(e)}')
def delete_product(self):
current_row = self.table.currentRow()
if current_row < 0:
QMessageBox.warning(self, "提示", "请先选择要删除的商品")
return
product_code = self.table.item(current_row, 0).text()
product_name = self.table.item(current_row, 1).text()
reply = QMessageBox.question(self, '确认删除',
f'确定要删除商品 "{product_name}" 吗？',
QMessageBox.Yes | QMessageBox.No)
if reply == QMessageBox.Yes:
try:
# Check if product is referenced in other tables
cursor = self.db.connect().cursor()
# Check purchase orders
cursor.execute("""
SELECT COUNT(*) FROM purchase_order_details pod
JOIN products p ON pod.product_id = p.id
WHERE p.code = ?
""", (product_code,))
if cursor.fetchone()[0] > 0:
QMessageBox.warning(self, "错误", "该商品已存在采购记录，无法删除")
return
# Check sales orders
cursor.execute("""
SELECT COUNT(*) FROM sales_order_details sod
JOIN products p ON sod.product_id = p.id
WHERE p.code = ?
""", (product_code,))
if cursor.fetchone()[0] > 0:
QMessageBox.warning(self, "错误", "该商品已存在销售记录，无法删除")
return
# Check inventory records
cursor.execute("""
SELECT COUNT(*) FROM inventory_records ir
JOIN products p ON ir.product_id = p.id
WHERE p.code = ?
""", (product_code,))
if cursor.fetchone()[0] > 0:
QMessageBox.warning(self, "错误", "该商品已存在库存记录，无法删除")
return
# Delete the product
self.db.execute_query("DELETE FROM products WHERE code = ?", (product_code,))
self.load_products()
QMessageBox.information(self, "成功", "商品已删除")
except Exception as e:
QMessageBox.warning(self, '错误', f'删除失败: {str(e)}')
def search_products(self):
search_text = self.search_input.text().lower()
for row in range(self.table.rowCount()):
match = False
for col in range(self.table.columnCount()):
item = self.table.item(row, col)
if item and search_text in item.text().lower():
match = True
break
self.table.setRowHidden(row, not match)
def on_item_changed(self, item):
row = item.row()
try:
# Get all values from the row
values = []
for col in range(self.table.columnCount()):
cell_item = self.table.item(row, col)
values.append(cell_item.text() if cell_item else '')
# Update or insert into database
query = '''INSERT OR REPLACE INTO products
(code, name, specification, unit, category,
cost_price, selling_price, stock_quantity, warning_quantity)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)'''
# Convert numeric values
values[5] = float(values[5]) if values[5] else 0 # cost_price
values[6] = float(values[6]) if values[6] else 0 # selling_price
values[7] = int(values[7]) if values[7] else 0 # stock_quantity
values[8] = int(values[8]) if values[8] else 0 # warning_quantity
self.db.execute_query(query, tuple(values))
except Exception as e:
QMessageBox.warning(self, '错误', f'保存失败: {str(e)}')
self.load_products() # Reload to revert changes 
供应商管理 suppliers.py
from PySide6.QtWidgets import (QWidget, QVBoxLayout, QHBoxLayout, QPushButton,
QLabel, QLineEdit, QTableWidget, QTableWidgetItem,
QMessageBox, QHeaderView, QFileDialog, QDialog,
QFormLayout)
from PySide6.QtCore import Qt
import openpyxl
from datetime import datetime
class SupplierEditDialog(QDialog):
def __init__(self, supplier=None, parent=None):
super().__init__(parent)
self.supplier = supplier
self.init_ui()
def init_ui(self):
self.setWindowTitle('编辑供应商' if self.supplier else '新增供应商')
layout = QFormLayout(self)
# Create input fields
self.name = QLineEdit(self.supplier[0] if self.supplier else '')
self.contact_person = QLineEdit(self.supplier[1] if self.supplier else '')
self.phone = QLineEdit(self.supplier[2] if self.supplier else '')
self.address = QLineEdit(self.supplier[3] if self.supplier else '')
self.payment_terms = QLineEdit(self.supplier[4] if self.supplier else '')
# Add fields to layout
layout.addRow('供应商名称:', self.name)
layout.addRow('联系人:', self.contact_person)
layout.addRow('联系电话:', self.phone)
layout.addRow('地址:', self.address)
layout.addRow('账期:', self.payment_terms)
# Add buttons
buttons_layout = QHBoxLayout()
save_btn = QPushButton('保存')
save_btn.clicked.connect(self.accept)
cancel_btn = QPushButton('取消')
cancel_btn.clicked.connect(self.reject)
buttons_layout.addWidget(save_btn)
buttons_layout.addWidget(cancel_btn)
layout.addRow(buttons_layout)
def get_data(self):
return [
self.name.text(),
self.contact_person.text(),
self.phone.text(),
self.address.text(),
self.payment_terms.text()
]
class SuppliersTab(QWidget):
def __init__(self, db):
super().__init__()
self.db = db
self.init_ui()
self.load_suppliers()
self.editing = False
def init_ui(self):
layout = QVBoxLayout(self)
# Create toolbar
toolbar = QHBoxLayout()
# Add supplier button
self.add_btn = QPushButton('新建供应商')
self.add_btn.clicked.connect(self.add_supplier)
toolbar.addWidget(self.add_btn)
# Edit supplier button
self.edit_btn = QPushButton('编辑供应商')
self.edit_btn.clicked.connect(self.edit_supplier)
toolbar.addWidget(self.edit_btn)
# Delete supplier button
self.delete_btn = QPushButton('删除供应商')
self.delete_btn.clicked.connect(self.delete_supplier)
toolbar.addWidget(self.delete_btn)
# Import/Export buttons
self.import_btn = QPushButton('导入')
self.import_btn.clicked.connect(self.import_from_excel)
toolbar.addWidget(self.import_btn)
self.export_btn = QPushButton('导出')
self.export_btn.clicked.connect(self.export_to_excel)
toolbar.addWidget(self.export_btn)
# Search box
self.search_input = QLineEdit()
self.search_input.setPlaceholderText('搜索供应商...')
self.search_input.textChanged.connect(self.search_suppliers)
toolbar.addWidget(self.search_input)
toolbar.addStretch()
layout.addLayout(toolbar)
# Create table
self.table = QTableWidget()
self.table.setColumnCount(5)
self.table.setHorizontalHeaderLabels([
'供应商名称', '联系人', '联系电话', '地址', '账期'
])
header = self.table.horizontalHeader()
header.setSectionResizeMode(QHeaderView.Stretch)
# Enable selection of entire rows
self.table.setSelectionBehavior(QTableWidget.SelectRows)
self.table.setSelectionMode(QTableWidget.SingleSelection)
layout.addWidget(self.table)
def add_supplier(self):
dialog = SupplierEditDialog(parent=self)
if dialog.exec_():
values = dialog.get_data()
try:
# Check if supplier name exists
cursor = self.db.connect().cursor()
cursor.execute("SELECT name FROM suppliers WHERE name = ?", (values[0],))
if cursor.fetchone():
QMessageBox.warning(self, "错误", f"供应商 '{values[0]}' 已存在")
return
# Insert new supplier
query = '''INSERT INTO suppliers
(name, contact_person, phone, address, payment_terms)
VALUES (?, ?, ?, ?, ?)'''
self.db.execute_query(query, tuple(values))
self.load_suppliers()
except Exception as e:
QMessageBox.warning(self, '错误', f'保存失败: {str(e)}')
def edit_supplier(self):
current_row = self.table.currentRow()
if current_row < 0:
QMessageBox.warning(self, "提示", "请先选择要编辑的供应商")
return
# Get current supplier data
values = []
for col in range(self.table.columnCount()):
item = self.table.item(current_row, col)
values.append(item.text() if item else '')
dialog = SupplierEditDialog(values, self)
if dialog.exec_():
new_values = dialog.get_data()
try:
# Check if new name exists (if name was changed)
if new_values[0] != values[0]:
cursor = self.db.connect().cursor()
cursor.execute("SELECT name FROM suppliers WHERE name = ?", (new_values[0],))
if cursor.fetchone():
QMessageBox.warning(self, "错误", f"供应商 '{new_values[0]}' 已存在")
return
# Update supplier
query = '''UPDATE suppliers
SET name = ?, contact_person = ?, phone = ?,
address = ?, payment_terms = ?
WHERE name = ?'''
self.db.execute_query(query, tuple(new_values + [values[0]]))
self.load_suppliers()
except Exception as e:
QMessageBox.warning(self, '错误', f'保存失败: {str(e)}')
def delete_supplier(self):
current_row = self.table.currentRow()
if current_row < 0:
QMessageBox.warning(self, "提示", "请先选择要删除的供应商")
return
supplier_name = self.table.item(current_row, 0).text()
reply = QMessageBox.question(self, '确认删除',
f'确定要删除供应商 "{supplier_name}" 吗？',
QMessageBox.Yes | QMessageBox.No)
if reply == QMessageBox.Yes:
try:
# Check if supplier is referenced in purchase orders
cursor = self.db.connect().cursor()
cursor.execute("""
SELECT COUNT(*) FROM purchase_orders po
JOIN suppliers s ON po.supplier_id = s.id
WHERE s.name = ?
""", (supplier_name,))
if cursor.fetchone()[0] > 0:
QMessageBox.warning(self, "错误", "该供应商已有采购记录，无法删除")
return
# Delete the supplier
self.db.execute_query("DELETE FROM suppliers WHERE name = ?", (supplier_name,))
self.load_suppliers()
QMessageBox.information(self, "成功", "供应商已删除")
except Exception as e:
QMessageBox.warning(self, '错误', f'删除失败: {str(e)}')
def export_to_excel(self):
try:
# Create a new workbook and select the active sheet
wb = openpyxl.Workbook()
ws = wb.active
ws.title = "供应商列表"
# Write headers
headers = ['供应商名称', '联系人', '联系电话', '地址', '账期']
for col, header in enumerate(headers, 1):
ws.cell(row=1, column=col, value=header)
# Write data
for row in range(self.table.rowCount()):
for col in range(self.table.columnCount()):
item = self.table.item(row, col)
value = item.text() if item else ''
ws.cell(row=row+2, column=col+1, value=value)
# Get save file name
file_name = f"供应商列表_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"
file_path, _ = QFileDialog.getSaveFileName(
self, "导出Excel", file_name, "Excel Files (*.xlsx)")
if file_path:
wb.save(file_path)
QMessageBox.information(self, "成功", "导出成功！")
except Exception as e:
QMessageBox.warning(self, "错误", f"导出失败: {str(e)}")
def import_from_excel(self):
try:
# Get file name
file_path, _ = QFileDialog.getOpenFileName(
self, "选择Excel文件", "", "Excel Files (*.xlsx)")
if not file_path:
return
# Load workbook
wb = openpyxl.load_workbook(file_path)
ws = wb.active
# Get all rows
rows = list(ws.rows)
if len(rows) < 2: # Check if file has data (header + at least one row)
QMessageBox.warning(self, "错误", "Excel文件为空或格式不正确")
return
# Verify headers
expected_headers = ['供应商名称', '联系人', '联系电话', '地址', '账期']
headers = [cell.value for cell in rows[0]]
if headers != expected_headers:
QMessageBox.warning(self, "错误", "Excel文件格式不正确，请使用导出的模板")
return
# Begin transaction
conn = self.db.connect()
cursor = conn.cursor()
try:
# Process each row
duplicates = []
new_suppliers = []
for row in rows[1:]: # Skip header row
values = [cell.value if cell.value is not None else '' for cell in row]
supplier_name = values[0]
# Check if supplier exists
cursor.execute("SELECT name FROM suppliers WHERE name = ?", (supplier_name,))
if cursor.fetchone():
duplicates.append(values)
else:
new_suppliers.append(values)
# Handle duplicates
if duplicates:
duplicate_names = "\n".join(d[0] for d in duplicates)
reply = QMessageBox.question(self, "发现重复供应商",
f"以下供应商已存在:\n{duplicate_names}\n\n是否更新这些供应商的信息？",
QMessageBox.Yes | QMessageBox.No)
if reply == QMessageBox.Yes:
# Update existing suppliers
for values in duplicates:
query = '''UPDATE suppliers
SET contact_person = ?, phone = ?, address = ?, payment_terms = ?
WHERE name = ?'''
cursor.execute(query, (values[1], values[2], values[3], values[4], values[0]))
# Insert new suppliers
for values in new_suppliers:
query = '''INSERT INTO suppliers
(name, contact_person, phone, address, payment_terms)
VALUES (?, ?, ?, ?, ?)'''
cursor.execute(query, values)
conn.commit()
# Show summary
msg = f"导入完成！\n新增供应商：{len(new_suppliers)}个"
if duplicates:
if reply == QMessageBox.Yes:
msg += f"\n更新供应商：{len(duplicates)}个"
else:
msg += f"\n跳过重复供应商：{len(duplicates)}个"
QMessageBox.information(self, "成功", msg)
self.load_suppliers() # Refresh table
except Exception as e:
conn.rollback()
raise e
except Exception as e:
QMessageBox.warning(self, "错误", f"导入失败: {str(e)}")
def load_suppliers(self):
self.editing = True # Prevent triggering item change events
self.table.setRowCount(0)
query = '''SELECT name, contact_person, phone, address, payment_terms
FROM suppliers'''
suppliers = self.db.fetch_query(query)
for row, supplier in enumerate(suppliers):
self.table.insertRow(row)
for col, value in enumerate(supplier):
item = QTableWidgetItem(str(value) if value is not None else '')
self.table.setItem(row, col, item)
self.editing = False # Re-enable item change events
def search_suppliers(self):
search_text = self.search_input.text().lower()
for row in range(self.table.rowCount()):
# Only search in the supplier name column (column 0)
item = self.table.item(row, 0) # Get supplier name cell
match = item and search_text in item.text().lower()
self.table.setRowHidden(row, not match) 客户管理 customers.py
from PySide6.QtWidgets import (QWidget, QVBoxLayout, QHBoxLayout, QPushButton,
QLabel, QLineEdit, QTableWidget, QTableWidgetItem,
QMessageBox, QHeaderView, QFileDialog, QDialog,
QFormLayout)
from PySide6.QtCore import Qt
import openpyxl
from datetime import datetime
class SupplierEditDialog(QDialog):
def __init__(self, supplier=None, parent=None):
super().__init__(parent)
self.supplier = supplier
self.init_ui()
def init_ui(self):
self.setWindowTitle('编辑供应商' if self.supplier else '新增供应商')
layout = QFormLayout(self)
# Create input fields
self.name = QLineEdit(self.supplier[0] if self.supplier else '')
self.contact_person = QLineEdit(self.supplier[1] if self.supplier else '')
self.phone = QLineEdit(self.supplier[2] if self.supplier else '')
self.address = QLineEdit(self.supplier[3] if self.supplier else '')
self.payment_terms = QLineEdit(self.supplier[4] if self.supplier else '')
# Add fields to layout
layout.addRow('供应商名称:', self.name)
layout.addRow('联系人:', self.contact_person)
layout.addRow('联系电话:', self.phone)
layout.addRow('地址:', self.address)
layout.addRow('账期:', self.payment_terms)
# Add buttons
buttons_layout = QHBoxLayout()
save_btn = QPushButton('保存')
save_btn.clicked.connect(self.accept)
cancel_btn = QPushButton('取消')
cancel_btn.clicked.connect(self.reject)
buttons_layout.addWidget(save_btn)
buttons_layout.addWidget(cancel_btn)
layout.addRow(buttons_layout)
def get_data(self):
return [
self.name.text(),
self.contact_person.text(),
self.phone.text(),
self.address.text(),
self.payment_terms.text()
]
class SuppliersTab(QWidget):
def __init__(self, db):
super().__init__()
self.db = db
self.init_ui()
self.load_suppliers()
self.editing = False
def init_ui(self):
layout = QVBoxLayout(self)
# Create toolbar
toolbar = QHBoxLayout()
# Add supplier button
self.add_btn = QPushButton('新建供应商')
self.add_btn.clicked.connect(self.add_supplier)
toolbar.addWidget(self.add_btn)
# Edit supplier button
self.edit_btn = QPushButton('编辑供应商')
self.edit_btn.clicked.connect(self.edit_supplier)
toolbar.addWidget(self.edit_btn)
# Delete supplier button
self.delete_btn = QPushButton('删除供应商')
self.delete_btn.clicked.connect(self.delete_supplier)
toolbar.addWidget(self.delete_btn)
# Import/Export buttons
self.import_btn = QPushButton('导入')
self.import_btn.clicked.connect(self.import_from_excel)
toolbar.addWidget(self.import_btn)
self.export_btn = QPushButton('导出')
self.export_btn.clicked.connect(self.export_to_excel)