这个是软件运行的基本页面，可能比较简陋，个人更倾向于防呆设计，但是测试基本功能没什么问题。
楼主使用的系统是鼎捷E10的ERP,其中的SQL部分可能需要大家自己去根据自己的环境进行修改。
原理是数据库取值，然后把值绘制到PDF文件上，再打印PDF文件。
微信图片_2025-01-14_154043_312.png (94.11 KB, 下载次数: 0)
下载附件
2025-1-14 15:40 上传
[Python] 纯文本查看 复制代码import sys
from PyQt5.QtWidgets import QApplication, QWidget, QVBoxLayout, QHBoxLayout, QLabel, QLineEdit, QPushButton, QTextEdit, \
QMessageBox, QGraphicsView, QGraphicsScene
from PyQt5.QtCore import QThread, pyqtSignal, Qt
from PyQt5.QtGui import QPixmap, QIcon
from reportlab.pdfgen import canvas
from reportlab.pdfbase import pdfmetrics
from reportlab.pdfbase.ttfonts import TTFont
import pyodbc
import os
import json
import re # 导入正则表达式模块
import fitz # PyMuPDF，用于PDF渲染
from PIL import Image
import win32print
import win32api
# 注册中文字体（确保 SimHei.ttf 文件存在或替换为其他中文字体文件路径）
font_path = 'C:\Windows\Fonts\simkai.ttf' # 确保这里是你中文字体文件的路径
pdfmetrics.registerFont(TTFont('simkai', font_path))
en_font_path ='C:\Windows\Fonts/times.ttf'
pdfmetrics.registerFont(TTFont('times', en_font_path))
class QueryThread(QThread):
query_result_signal = pyqtSignal(dict)
def __init__(self, doc_no, conn, parent=None):
super().__init__(parent)
self.doc_no = doc_no
self.conn = conn
def run(self):
if self.conn is None:
self.query_result_signal.emit({'error': '数据库连接失败.'})
return
result = self.parent().execute_sales_delivery_query(self.doc_no)
if result is not None:
self.query_result_signal.emit(result)
else:
self.query_result_signal.emit({'error': '系统不存在此单号.'})
class SalesDeliveryApp(QWidget):
def __init__(self):
super().__init__()
self.conn = None
self.selected_template = None # 用于存储用户选择的模板
self.customer_code_template_map = {} # 用于存储 CUSTOMER_CODE 与模板文件的映射关系
self.initUI()
self.conn = self.connect_to_db()
self.load_customer_code_template_map() # 加载 CUSTOMER_CODE 与模板文件的映射关系
def execute_sales_delivery_query(self, doc_no):
try:
cursor = self.conn.cursor()
query = """
WITH RollQuantities AS (
SELECT
cc.CUSTOMER_CODE, -- 包含 CUSTOMER_CODE
ci.CUSTOMER_ITEM_CODE,
ci.CUSTOMER_ITEM_NAME,
ci.CUSTOMER_ITEM_SPECIFICATION,
ss.CUSTOMER_ORDER_NO,
sd.BUSINESS_QTY,
FORMAT(s.TRANSACTION_DATE, 'yyyyMMdd') AS TRANSACTION_DATE,
CAST(
CASE
WHEN PATINDEX('%[0-9][0-9][0-9][0-9]m/[RS]%', ci.CUSTOMER_ITEM_SPECIFICATION) > 0 THEN
SUBSTRING(
ci.CUSTOMER_ITEM_SPECIFICATION,
PATINDEX('%[0-9][0-9][0-9][0-9]m/[RS]%', ci.CUSTOMER_ITEM_SPECIFICATION),
4 -- 假设数字总是四位
)
WHEN PATINDEX('%[0-9][0-9][0-9]m/[RS]%', ci.CUSTOMER_ITEM_SPECIFICATION) > 0 THEN
SUBSTRING(
ci.CUSTOMER_ITEM_SPECIFICATION,
PATINDEX('%[0-9][0-9][0-9]m/[RS]%', ci.CUSTOMER_ITEM_SPECIFICATION),
3 -- 假设数字总是三位
)
ELSE NULL
END AS INT
) AS RollQuantity
FROM
SALES_DELIVERY s
JOIN
SALES_DELIVERY_D sd ON s.SALES_DELIVERY_ID = sd.SALES_DELIVERY_ID
JOIN
SALES_ORDER_DOC ss ON sd.SALES_ORDER_DOC_ID = ss.SALES_ORDER_DOC_ID
JOIN
CUSTOMER cc ON s.CUSTOMER_ID = cc.CUSTOMER_BUSINESS_ID
JOIN
CUSTOMER_ITEM ci ON sd.CUSTOMER_ITEM_ID = ci.CUSTOMER_ITEM_ID
WHERE
s.DOC_NO = ?
)
SELECT
r.CUSTOMER_CODE, -- 包含 CUSTOMER_CODE
r.CUSTOMER_ITEM_CODE,
r.CUSTOMER_ITEM_NAME,
r.CUSTOMER_ITEM_SPECIFICATION,
r.CUSTOMER_ORDER_NO,
CAST(r.BUSINESS_QTY AS INT) AS BUSINESS_QTY,
r.RollQuantity,
CAST(FLOOR(CAST(r.BUSINESS_QTY AS DECIMAL(18, 2)) / CAST(r.RollQuantity AS DECIMAL(18, 2))) AS INT) AS Q_NUM,
r.TRANSACTION_DATE,
LEFT(r.TRANSACTION_DATE,4) AS TRANSACTION_DATE_YEAR,
SUBSTRING(r.TRANSACTION_DATE,5,2) AS TRANSACTION_DATE_MONTH,
SUBSTRING(r.TRANSACTION_DATE,7,2) AS TRANSACTION_DATE_DAY
FROM
RollQuantities r
WHERE
r.RollQuantity IS NOT NULL AND r.RollQuantity != 0 -- 确保 RollQuantity 不为空且非零
AND CAST(r.BUSINESS_QTY AS DECIMAL(18, 2)) / CAST(r.RollQuantity AS DECIMAL(18, 2)) > 0 -- 确保 Q_NUM 是正数
"""
cursor.execute(query, (doc_no,))
rows = cursor.fetchall()
if rows:
detail_data = [(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11]) for row in rows]
return {'details': detail_data}
else:
return None
except pyodbc.Error as e:
return None
def set_font_size(self, widget, font_size=14):
font = widget.font()
font.setPointSize(font_size)
widget.setFont(font)
def initUI(self):
self.setWindowTitle('标签打印')
self.setGeometry(300, 300, 550, 600) # 增大窗口尺寸
# 添加应用程序图标
self.setWindowIcon(QIcon('rh.ico')) # 确保路径正确
main_layout = QVBoxLayout()
main_layout.setSpacing(10) # 增加控件之间的间距
doc_no_layout = QHBoxLayout()
doc_no_layout.setSpacing(10) # 增加控件之间的间距
doc_no_label = QLabel('销货单单号:')
self.doc_no_input = QLineEdit()
self.set_font_size(doc_no_label, 14) # 设置字体大小
self.set_font_size(self.doc_no_input, 14) # 设置字体大小
doc_no_layout.addWidget(doc_no_label)
doc_no_layout.addWidget(self.doc_no_input)
main_layout.addLayout(doc_no_layout)
self.query_button = QPushButton('查询', self)
self.set_font_size(self.query_button, 14) # 设置字体大小
self.query_button.clicked.connect(self.query_sales_delivery)
main_layout.addWidget(self.query_button)
self.result_display = QTextEdit()
self.set_font_size(self.result_display, 14) # 设置字体大小
self.result_display.setReadOnly(True)
main_layout.addWidget(self.result_display)
# 添加生成并打印标签按钮
self.print_label_button = QPushButton('打印标签', self)
self.set_font_size(self.print_label_button, 14) # 设置字体大小
self.print_label_button.clicked.connect(self.print_label)
main_layout.addWidget(self.print_label_button)
# 添加预览标签按钮
self.preview_label_button = QPushButton('预览标签', self)
self.set_font_size(self.preview_label_button, 14) # 设置字体大小
self.preview_label_button.clicked.connect(self.preview_label)
main_layout.addWidget(self.preview_label_button)
# 添加预览界面
self.graphics_view = QGraphicsView()
self.graphics_scene = QGraphicsScene()
self.graphics_view.setScene(self.graphics_scene)
main_layout.addWidget(self.graphics_view)
self.setLayout(main_layout)
def connect_to_db(self):
try:
conn_str = (
r'DRIVER={ODBC Driver 18 for SQL Server};'
r'SERVER=;'#数据库地址
r'DATABASE=;'#库名
r'UID=;'#用户
r'PWD=;'#密码
r'TrustServerCertificate=yes;'#忽略证书验证
)
conn = pyodbc.connect(conn_str)
return conn
except pyodbc.Error as e:
sys.exit(1)
def load_customer_code_template_map(self):
"""加载客户编号到模板文件"""
template_dir = 'templates'
if os.path.exists(template_dir) and os.path.isdir(template_dir):
for template_file in os.listdir(template_dir):
if template_file.endswith('.json'):
customer_code = os.path.splitext(template_file)[0]
self.customer_code_template_map[customer_code] = template_file
else:
QMessageBox.critical(self, "Error", f"模板数据不存在: {template_dir}")
def delete_label_files(self):
"""删除所有模板文件"""
for file_name in os.listdir('.'):
if file_name.startswith('label_') and file_name.endswith('.pdf'):
file_path = os.path.join('.', file_name)
try:
os.remove(file_path)
except Exception as e:
sys.exit(1)
def query_sales_delivery(self):
doc_no = self.doc_no_input.text().strip()
self.delete_label_files()
# 添加正则表达式验证逻辑
pattern = r'^2401-\d{9}$'
if not re.match(pattern, doc_no):
self.show_message_box("Warning", "请输入正确的销货单单号格式（2401-后跟9位数字）。")
return
self.query_button.setEnabled(False)
self.thread = QueryThread(doc_no, self.conn, parent=self)
self.thread.query_result_signal.connect(self.handle_query_result)
self.thread.start()
def handle_query_result(self, query_result):
self.query_button.setEnabled(True)
if query_result:
if 'error' in query_result:
# 处理错误信息
self.show_message_box("Info", query_result['error'])
# 清空查询结果展示框
self.result_display.clear()
else:
self.display_query_result(query_result)
self.auto_select_template(query_result) # 自动选择模板
else:
# 清空查询结果展示框
self.result_display.clear()
def auto_select_template(self, query_result):
"""Automatically select the template based on CUSTOMER_CODE."""
if query_result and query_result['details']:
customer_code = query_result['details'][0][0] # 假设所有记录的 CUSTOMER_CODE 都相同
if customer_code in self.customer_code_template_map:
self.selected_template = self.customer_code_template_map[customer_code]
else:
self.doc_no_input.clear()
# 清空查询结果展示框
self.result_display.clear()
self.show_message_box("Warning", "未找到匹配的模板，请重新输入销货单单号。")
def fetch_sales_delivery(self, doc_no):
return self.execute_sales_delivery_query(doc_no)
def display_query_result(self, query_result):
detail_data = query_result['details']
result_text = ""
for detail in detail_data:
result_text += f"客户编号: {detail[0]}\n" # 添加客户代码显示
result_text += f"销货日期: {detail[8]}\n"
result_text += f"客户物料编码: {detail[1]}\n"
result_text += f"客户物料名称: {detail[2]}\n"
result_text += f"客户物料规格: {detail[3]}\n"
result_text += f"业务数量: {detail[5]}\n"
result_text += f"卷数数量: {detail[7]}\n\n"
self.result_display.setText(result_text)
def load_template(self, template_file):
"""Load and parse the template file for a given client."""
template_path = os.path.join('templates', template_file)
if not os.path.exists(template_path):
self.show_message_box("Error", "Template file not found.")
return None
try:
with open(template_path, 'r', encoding='utf-8') as f:
return json.load(f)
except json.JSONDecodeError:
return None
def generate_label(self, doc_no, is_preview=False, include_background=True):
if not self.selected_template:
self.show_message_box("Warning", "未找到匹配的模板，请重新输入销货单单号。")
return None
query_result = self.fetch_sales_delivery(doc_no)
if not query_result:
return None
detail_data = query_result['details']
# 加载模板
template = self.load_template(self.selected_template)
if not template:
return None
# 从模板中读取宽度和高度
label_width_cm = template.get('width_cm', 12) # 默认宽度为12cm
label_height_cm = template.get('height_cm', 7.4) # 默认高度为7.4cm
# 将厘米转换为点数（1英寸=72点，1厘米≈28.3465点）
label_width = label_width_cm * 28.3465
label_height = label_height_cm * 28.3465
# 修改文件名以销货单号命名
filename = f"label_{doc_no}_preview.pdf" if is_preview else f"label_{doc_no}.pdf"
c = canvas.Canvas(filename, pagesize=(label_width, label_height))
width, height = label_width, label_height
# 加载背景图片
background_image_path = template.get('background_image')
if background_image_path and include_background:
background_image_full_path = os.path.join('templates', background_image_path)
if not os.path.exists(background_image_full_path):
self.show_message_box("Error", f"背景图片未找到: {background_image_full_path}")
return None
# 生成每条记录的一页 PDF 文件
for i, detail in enumerate(detail_data):
detail_dict = {
'CUSTOMER_ITEM_CODE': detail[1],
'CUSTOMER_ITEM_NAME': detail[2],
'CUSTOMER_ITEM_SPECIFICATION': detail[3],
'CUSTOMER_ORDER_NO': detail[4],
'BUSINESS_QTY': str(detail[5]),
'RollQuantity': str(detail[6]), # 确保 RollQuantity 字段被正确添加
'Q_NUM': str(detail[7]),
'TRANSACTION_DATE': detail[8],
'TRANSACTION_DATE_YEAR': detail[9],
'TRANSACTION_DATE_MONTH': detail[10],
'TRANSACTION_DATE_DAY': detail[11],
}
# 根据 Q_NUM 的数量生成相应数量的PDF页面
q_num = int(detail_dict['Q_NUM'])
for page in range(q_num):
# 绘制背景图片
if background_image_path and include_background:
c.drawImage(background_image_full_path, 0, 0, width, height)
# 绘制标签内容
for template_field in template.get('fields', []):
field_name = template_field['name']
x = template_field['x']
y = template_field['y'] # 使用模板中的 y 坐标
font_size = template_field.get('font_size', 12)
font_family = template_field.get('font_family', 'SimHei')
# 查找对应的数据
value = detail_dict.get(field_name, '')
c.setFont(font_family, font_size)
c.drawString(x, height - y, value) # 只绘制值，不绘制字段名称
# 绘制厂商名字
vendor_name = template.get('vendor_name', '')
vendor_x = template.get('vendor_x', 0)
vendor_y = template.get('vendor_y', 0)
vendor_font_size = template.get('vendor_font_size', 12)
vendor_font_family = template.get('vendor_font_family', 'SimHei')
c.setFont(vendor_font_family, vendor_font_size)
c.drawString(vendor_x, height - vendor_y, vendor_name)
# 绘制米数
meter_name = template.get('meter_name', '')
meter_x = template.get('meter_x', 0)
meter_y = template.get('meter_y', 0)
meter_font_size = template.get('meter_font_size', 12)
meter_font_family = template.get('meter_font_family', 'SimHei')
c.setFont(meter_font_family, meter_font_size)
c.drawString(meter_x, height - meter_y, meter_name)
# 添加新页
if page
另外附上json文件
[JavaScript] 纯文本查看 复制代码
{
"CUSTOMER_CODE": "C0027",
"width_cm": 10.5,
"height_cm": 6.5,
"background_image": "futai.png",
"fields": [
{
"name": "CUSTOMER_ITEM_NAME",
"x": 62,
"y": 82,
"font_size": 7,
"font_family": "simkai",
"alignment": "left"
},
{
"name": "CUSTOMER_ITEM_CODE",
"x": 200,
"y": 82,
"font_size": 7,
"font_family": "times",
"alignment": "left"
},
{
"name": "CUSTOMER_ITEM_SPECIFICATION",
"x": 62,
"y": 98,
"font_size": 6,
"font_family": "times",
"alignment": "left"
},
{
"name": "RollQuantity",
"x": 220,
"y": 98,
"font_size": 7,
"font_family": "times",
"alignment": "left"
},
{
"name": "CUSTOMER_ORDER_NO",
"x": 200,
"y": 66,
"font_size": 7,
"font_family": "times",
"alignment": "left"
},
{
"name": "TRANSACTION_DATE",
"x": 210,
"y": 114,
"font_size": 7,
"font_family": "times",
"alignment": "left"
}
],
"vendor_name": "公司",
"vendor_x": 66,
"vendor_y": 65,
"vendor_font_size": 14,
"vendor_font_family": "simkai",
"meter_name": "M",
"meter_x": 235,
"meter_y": 98,
"meter_font_size": 7,
"meter_font_family": "times"
}
