Python 写的 智慧记 进销存 辅助 程序 导入导出 excel 可打印 Pyside6版

图

这图是第2版，

现在发布原型版

 代码：

order_system_pyside6.py

from PySide6.QtWidgets import (QApplication, QMainWindow, QWidget, QVBoxLayout,
                                 QHBoxLayout, QLabel, QLineEdit, QPushButton, QMessageBox,
                                 QTableWidget, QTableWidgetItem, QComboBox, QFrame,
                                 QHeaderView, QFileDialog, QDialog, QGroupBox, QGridLayout,
                                 QRadioButton, QTextBrowser)
from PySide6.QtCore import Qt, QDate
from PySide6.QtGui import QDoubleValidator
import sqlite3
from datetime import datetime
import pandas as pd
import hashlib
import json
import os
import sys
from login_pyside6 import LoginWindow

class OrderSystem(QMainWindow):
    def __init__(self):
        super().__init__()
        self.init_db()
        self.create_menu()  # 添加菜单栏
        self.init_ui()
        self.load_all_orders()
        
    def init_db(self):
        """初始化数据库"""
        try:
            os.makedirs('db', exist_ok=True)
            self.conn = sqlite3.connect('db/orders.db')
            self.create_table()
        except sqlite3.Error as e:
            QMessageBox.critical(self, "错误", f"数据库连接失败：{str(e)}")
            sys.exit(1)
            
    def create_table(self):
        """创建数据表"""
        cursor = self.conn.cursor()
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS orders (
            order_date TEXT,
            order_number TEXT,
            customer TEXT,
            product TEXT,
            unit TEXT,
            quantity REAL,
            price REAL,
            discount REAL,
            final_price REAL,
            total REAL,
            remarks TEXT,
            discount_amount REAL,
            discount_total REAL,
            delivery TEXT,
            payment_received REAL,
            end_customer TEXT,
            notes TEXT,
            business TEXT
        )
        ''')
        self.conn.commit()
        
    def init_ui(self):
        """初始化用户界面"""
        self.setWindowTitle("智慧记辅助系统")
        self.setMinimumSize(1200, 800)
        
        # 创建中央部件
        central_widget = QWidget()
        self.setCentralWidget(central_widget)
        main_layout = QVBoxLayout(central_widget)
        
        # 创建查询区域
        search_frame = QFrame()
        search_frame.setFrameStyle(QFrame.StyledPanel)
        search_layout = QHBoxLayout(search_frame)
        
        # 添加查询条件输入框
        self.search_fields = {}
        search_items = [
            ('order_number', '单据编号'),
            ('customer', '客户名称'),
            ('product', '品名规格'),
            ('date_from', '开始日期'),
            ('date_to', '结束日期'),
            ('business', '营业员')
        ]
        
        for field, label in search_items:
            field_layout = QVBoxLayout()
            label_widget = QLabel(label)
            self.search_fields[field] = QLineEdit()
            field_layout.addWidget(label_widget)
            field_layout.addWidget(self.search_fields[field])
            search_layout.addLayout(field_layout)
        
        # 添加查询和重置按钮
        button_layout = QVBoxLayout()
        search_button = QPushButton("查询")
        search_button.clicked.connect(self.search_orders)
        reset_button = QPushButton("重置")
        reset_button.clicked.connect(self.reset_search)
        button_layout.addWidget(search_button)
        button_layout.addWidget(reset_button)
        search_layout.addLayout(button_layout)
        
        main_layout.addWidget(search_frame)
        
        # 创建按钮区域
        button_layout = QHBoxLayout()
        self.create_buttons(button_layout)
        main_layout.addLayout(button_layout)
        
        # 创建表格
        self.create_tables()
        main_layout.addWidget(self.table)
        
        # 居中显示
        self.center_window()
        
    def create_buttons(self, layout):
        """创建按钮"""
        buttons = [
            ("新增", self.add_order),
            ("编辑", self.edit_order),
            ("删除", self.delete_selected),
            ("查看", self.view_order),
            ("导入Excel", self.import_from_excel),
            ("导出Excel", self.export_to_excel),
            ("导出模板", self.export_template),
            ("统计报表", self.show_statistics)
        ]
        
        for text, slot in buttons:
            button = QPushButton(text)
            button.clicked.connect(slot)
            layout.addWidget(button)
            
    def create_tables(self):
        """创建表格"""
        self.table = QTableWidget()
        headers = [
            "单据日期", "单据编号", "客户名称", "品名规格", "单位",
            "数量", "原价", "单行折扣率(%)", "折后价", "金额",
            "备注", "整单折扣率(%)", "折后金额", "运费",
            "本单已收", "结算账户", "说明", "营业员"
        ]
        
        self.table.setColumnCount(len(headers))
        self.table.setHorizontalHeaderLabels(headers)
        self.table.horizontalHeader().setSectionResizeMode(QHeaderView.Interactive)
        self.table.horizontalHeader().setStretchLastSection(True)
        
        # 设置列宽
        for i in range(len(headers)):
            self.table.setColumnWidth(i, 100)
            
    def center_window(self):
        """居中显示窗口"""
        screen_geometry = QApplication.primaryScreen().geometry()
        x = (screen_geometry.width() - self.width()) // 2
        y = (screen_geometry.height() - self.height()) // 2
        self.move(x, y)
        
    def calculate_total(self):
        """计算金额"""
        try:
            quantity = float(self.entries['quantity'].text() or 0)
            price = float(self.entries['price'].text() or 0)
            discount = float(self.entries['discount'].text() or 100)
            
            final_price = price * discount / 100
            self.entries['final_price'].setText(f"{final_price:.2f}")
            
            total = quantity * final_price
            self.entries['total'].setText(f"{total:.2f}")
        except ValueError:
            pass
            
    def validate_data(self):
        """数据验证"""
        errors = []
        
        # 验证必填字段
        required_fields = {
            'order_date': '单据日期',
            'order_number': '单据编号',
            'customer': '客户名称',
            'product': '品名规格',
            'unit': '单位',
            'quantity': '数量',
            'price': '原价'
        }
        
        for field, name in required_fields.items():
            if not self.entries[field].text().strip():
                errors.append(f"{name}不能为空")
                
        if errors:
            QMessageBox.critical(self, "验证错误", "\n".join(errors))
            return False
        return True
        
    def save_order(self):
        """保存订单"""
        if not self.validate_data():
            return
            
        try:
            values = []
            for field in self.entries:
                value = self.entries[field].text().strip()
                if field in ['quantity', 'price', 'discount', 'final_price', 
                            'total', 'discount_amount', 'discount_total', 
                            'payment_received']:
                    try:
                        value = float(value) if value else 0.0
                    except ValueError:
                        value = 0.0
                values.append(value)
                
            cursor = self.conn.cursor()
            cursor.execute('SELECT COUNT(*) FROM orders WHERE order_number = ?', (values[1],))
            if cursor.fetchone()[0] > 0:
                reply = QMessageBox.question(self, "警告", "单据编号已存在，是否继续保存？",
                                          QMessageBox.Yes | QMessageBox.No)
                if reply == QMessageBox.No:
                    return
                    
            cursor.execute('''
            INSERT INTO orders VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
            ''', values)
            self.conn.commit()
            
            self.load_all_orders()
            self.set_default_values()
            QMessageBox.information(self, "成功", "订单保存成功！")
            
        except Exception as e:
            self.conn.rollback()
            QMessageBox.critical(self, "错误", f"保存失败：{str(e)}")
            
    def clear_fields(self):
        """清空输入框"""
        for entry in self.entries.values():
            entry.clear()
        self.set_default_values()
        
    def edit_selected(self):
        """编辑选中的记录"""
        selected = self.table.selectedItems()
        if not selected:
            QMessageBox.warning(self, "提示", "请先选择一条记录")
            return
            
        row = selected[0].row()
        for col, field in enumerate(self.entries):
            self.entries[field].setText(self.table.item(row, col).text())
            
    def delete_selected(self):
        """删除选中的记录"""
        selected = self.table.selectedItems()
        if not selected:
            QMessageBox.warning(self, "提示", "请先选择一条记录")
            return
            
        reply = QMessageBox.question(self, "确认", "确定要删除选中的记录吗？",
                                   QMessageBox.Yes | QMessageBox.No)
        if reply == QMessageBox.Yes:
            row = selected[0].row()
            order_number = self.table.item(row, 1).text()
            
            cursor = self.conn.cursor()
            cursor.execute('DELETE FROM orders WHERE order_number = ?', (order_number,))
            self.conn.commit()
            
            self.load_all_orders()
            
    def import_from_excel(self):
        """从Excel导入数据"""
        filename, _ = QFileDialog.getOpenFileName(
            self, "选择Excel文件", "",
            "Excel Files (*.xlsx *.xls);;All Files (*)"
        )
        if not filename:
            return
            
        try:
            df = pd.read_excel(filename)
            if self.validate_excel_data(df):
                self.import_excel_data(df)
        except Exception as e:
            QMessageBox.critical(self, "错误", f"导入失败：{str(e)}")
            
    def export_to_excel(self):
        """导出到Excel"""
        filename, _ = QFileDialog.getSaveFileName(
            self, "保存Excel文件", "",
            "Excel Files (*.xlsx);;All Files (*)"
        )
        if not filename:
            return
            
        try:
            cursor = self.conn.cursor()
            cursor.execute('SELECT * FROM orders')
            data = cursor.fetchall()
            
            columns = [
                "单据日期", "单据编号", "客户名称", "品名规格", "单位",
                "数量", "原价", "单行折扣率(%)", "折后价", "金额",
                "备注", "整单折扣率(%)", "折后金额", "运费",
                "本单已收", "结算账户", "说明", "营业员"
            ]
            
            df = pd.DataFrame(data, columns=columns)
            df.to_excel(filename, index=False, engine='openpyxl')
            QMessageBox.information(self, "成功", "数据导出成功！")
            
        except Exception as e:
            QMessageBox.critical(self, "错误", f"导出失败：{str(e)}")
            
    def export_template(self):
        """导出模板"""
        filename, _ = QFileDialog.getSaveFileName(
            self, "保存模板", "订单导入模板.xlsx",
            "Excel Files (*.xlsx);;All Files (*)"
        )
        if not filename:
            return
            
        try:
            # 创建示例数据
            sample_data = {
                "单据日期": ["2024-01-01"],
                "单据编号": ["XSD202401001"],
                "客户名称": ["示例客户"],
                "品名规格": ["示例产品"],
                "单位": ["个"],
                "数量": [1],
                "原价": [100],
                "单行折扣率(%)": [100],
                "折后价": [100],
                "金额": [100],
                "备注": ["备注示例"],
                "整单折扣率(%)": [0],
                "折后金额": [100],
                "运费": [0],
                "本单已收": [0],
                "结算账户": ["结算账户示例"],
                "说明": ["说明示例"],
                "营业员": ["营业员示例"]
            }
            
            # 创建DataFrame并导出到Excel
            df = pd.DataFrame(sample_data)
            df.to_excel(filename, index=False, engine='openpyxl')
            
            QMessageBox.information(self, "成功", "模板导出成功！\n请按照模板格式准备数据后再进行导入。")
            
        except Exception as e:
            QMessageBox.critical(self, "错误", f"导出模板失败：{str(e)}")
            
    def show_statistics(self):
        """显示统计报表"""
        stats_dialog = StatisticsDialog(self.conn, self)
        stats_dialog.exec()
        
    def load_all_orders(self):
        """加载所有订单"""
        cursor = self.conn.cursor()
        cursor.execute('SELECT * FROM orders')
        data = cursor.fetchall()
        
        self.table.setRowCount(len(data))
        for row, record in enumerate(data):
            for col, value in enumerate(record):
                item = QTableWidgetItem(str(value))
                self.table.setItem(row, col, item)
                
    def set_default_values(self):
        """设置默认值"""
        today = datetime.now().strftime('%Y-%m-%d')
        self.entries['order_date'].setText(today)
        self.entries['discount'].setText('100')
        
        # 生成新的单据编号
        cursor = self.conn.cursor()
        cursor.execute('''
        SELECT MAX(order_number) FROM orders 
        WHERE order_number LIKE ?
        ''', [f'XSD{today.replace("-", "")}%'])
        
        last_number = cursor.fetchone()[0]
        if last_number:
            try:
                seq = int(last_number[-3:]) + 1
                new_number = f'XSD{today.replace("-", "")}{seq:03d}'
            except ValueError:
                new_number = f'XSD{today.replace("-", "")}001'
        else:
            new_number = f'XSD{today.replace("-", "")}001'
            
        self.entries['order_number'].setText(new_number)
        
    def validate_excel_data(self, df):
        """验证Excel数据"""
        required_columns = [
            "单据日期", "单据编号", "客户名称", "品名规格", "单位",
            "数量", "原价", "单行折扣率(%)", "折后价", "金额"
        ]
        
        missing_columns = [col for col in required_columns if col not in df.columns]
        if missing_columns:
            QMessageBox.critical(self, "错误", f"Excel文件缺少以下必需列：\n{', '.join(missing_columns)}")
            return False
        return True
        
    def import_excel_data(self, df):
        """导入Excel数据"""
        try:
            cursor = self.conn.cursor()
            for _, row in df.iterrows():
                values = []
                for field in self.entries:
                    excel_field = {
                        'order_date': '单据日期',
                        'order_number': '单据编号',
                        'customer': '客户名称',
                        'product': '品名规格',
                        'unit': '单位',
                        'quantity': '数量',
                        'price': '原价',
                        'discount': '单行折扣率(%)',
                        'final_price': '折后价',
                        'total': '金额',
                        'remarks': '备注',
                        'discount_amount': '整单折扣率(%)',
                        'discount_total': '折后金额',
                        'delivery': '运费',
                        'payment_received': '本单已收',
                        'end_customer': '结算账户',
                        'notes': '说明',
                        'business': '营业员'
                    }[field]
                    
                    value = row.get(excel_field, '')
                    if pd.isna(value):
                        value = 0 if field in ['quantity', 'price', 'discount', 'final_price',
                                             'total', 'discount_amount', 'discount_total',
                                             'payment_received'] else ''
                    values.append(value)
                    
                cursor.execute('''
                INSERT INTO orders VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
                ''', values)
                
            self.conn.commit()
            self.load_all_orders()
            QMessageBox.information(self, "成功", "数据导入成功！")
            
        except Exception as e:
            self.conn.rollback()
            QMessageBox.critical(self, "错误", f"导入数据失败：{str(e)}")
            
    def view_order(self):
        """查看订单详情"""
        selected = self.table.selectedItems()
        if not selected:
            QMessageBox.warning(self, "提示", "请先选择一条记录")
            return
            
        row = selected[0].row()
        order_number = self.table.item(row, 1).text()  # 获取单据编号
        
        dialog = OrderDetailDialog(self.conn, order_number, self)
        dialog.exec()

    def search_orders(self):
        """执行订单查询"""
        try:
            # 构建查询条件
            conditions = []
            params = []
            
            # 单据编号查询
            if self.search_fields['order_number'].text().strip():
                conditions.append("order_number LIKE ?")
                params.append(f"%{self.search_fields['order_number'].text().strip()}%")
            
            # 客户名称查询
            if self.search_fields['customer'].text().strip():