import sys
from PyQt5.QtWidgets import (QApplication, QMainWindow, QWidget, QVBoxLayout, QHBoxLayout,
QLabel, QLineEdit, QPushButton, QFrame, QScrollArea, QGridLayout,
QDialog, QMessageBox, QTableWidget, QTableWidgetItem, QProgressBar)
from PyQt5.QtCore import Qt, QTimer, QPoint, QEvent, QObject
from PyQt5.QtGui import QFont, QPixmap
import pymssql
import os
from datetime import datetime, date
# ==================== 支持滑动穿透的按钮 ====================
class SwipeThroughButton(QPushButton):
def __init__(self, text="", parent=None):
super().__init__(text, parent)
self._start_pos = None
self._is_click = False
self._move_threshold = 10
self.setAttribute(Qt.WA_AcceptTouchEvents)
self.installEventFilter(self)
def mousePressEvent(self, event):
self._start_pos = event.pos()
self._is_click = True
event.ignore()
def mouseMoveEvent(self, event):
if self._start_pos is not None:
delta = (event.pos() - self._start_pos).manhattanLength()
if delta > self._move_threshold:
self._is_click = False
event.ignore()
def mouseReleaseEvent(self, event):
if self._is_click and self.rect().contains(event.pos()):
QTimer.singleShot(1, self.clicked.emit)
self._start_pos = None
self._is_click = False
event.ignore()
def eventFilter(self, obj, event):
if obj != self:
return False
t = event.type()
if t in (QEvent.TouchBegin, QEvent.TouchUpdate, QEvent.TouchEnd):
return False
return False
# ==================== 惯性滚动区域(无反弹)====================
class TouchScrollArea(QScrollArea):
def __init__(self, parent=None):
super().__init__(parent)
self.setVerticalScrollBarPolicy(Qt.ScrollBarAsNeeded)
self.setHorizontalScrollBarPolicy(Qt.ScrollBarAlwaysOff)
self.setWidgetResizable(True)
self._is_touching = False
self._last_pos = QPoint()
self._velocity_y = 0.0
self._last_move_time = 0
self._inertial_timer = QTimer(self)
self._inertial_timer.timeout.connect(self._inertial_scroll)
self._inertial_timer.setInterval(16)
self._deceleration_factor = 0.92
self._min_velocity = 0.1
self._scroll_step_multiplier = 1.8
def event(self, event):
if event.type() in (QEvent.TouchBegin, QEvent.TouchUpdate, QEvent.TouchEnd):
return self.handle_touch_event(event)
return super().event(event)
def handle_touch_event(self, event):
current_time = event.timestamp() or datetime.now().timestamp() * 1000
touch_points = event.touchPoints()
if event.type() == QEvent.TouchBegin:
self._is_touching = True
self._inertial_timer.stop()
if touch_points:
pos = touch_points[0].pos()
self._last_pos = pos
self._last_move_time = current_time
self._velocity_y = 0.0
return True
elif event.type() == QEvent.TouchUpdate and self._is_touching and touch_points:
pos = touch_points[0].pos()
delta_y = pos.y() - self._last_pos.y()
time_delta = current_time - self._last_move_time
if time_delta > 0:
velocity = delta_y / time_delta
self._velocity_y = 0.7 * self._velocity_y + 0.3 * velocity
scroll_bar = self.verticalScrollBar()
new_value = scroll_bar.value() - int(delta_y * self._scroll_step_multiplier)
scroll_bar.setValue(new_value)
self._last_pos = pos
self._last_move_time = current_time
return True
elif event.type() == QEvent.TouchEnd:
self._is_touching = False
if abs(self._velocity_y) > 0.1:
self._inertial_timer.start()
return True
return False
def _inertial_scroll(self):
scroll_bar = self.verticalScrollBar()
current_value = scroll_bar.value()
step = self._velocity_y * 16
new_value = current_value - int(step)
min_val, max_val = scroll_bar.minimum(), scroll_bar.maximum()
if new_value <= min_val:
scroll_bar.setValue(min_val)
self._velocity_y = 0.0
self._inertial_timer.stop()
elif new_value >= max_val:
scroll_bar.setValue(max_val)
self._velocity_y = 0.0
self._inertial_timer.stop()
else:
scroll_bar.setValue(new_value)
self._velocity_y *= self._deceleration_factor
if abs(self._velocity_y) < self._min_velocity:
self._inertial_timer.stop()
def mousePressEvent(self, event):
if event.button() == Qt.LeftButton:
self._is_touching = True
self._inertial_timer.stop()
self._last_pos = event.pos()
self._last_move_time = event.timestamp() or 0
self._velocity_y = 0.0
super().mousePressEvent(event)
def mouseMoveEvent(self, event):
if self._is_touching:
current_time = event.timestamp() or 0
delta_y = event.pos().y() - self._last_pos.y()
time_delta = current_time - self._last_move_time
if time_delta > 0:
velocity = delta_y / time_delta
self._velocity_y = 0.7 * self._velocity_y + 0.3 * velocity
scroll_bar = self.verticalScrollBar()
new_value = scroll_bar.value() - int(delta_y * self._scroll_step_multiplier)
scroll_bar.setValue(new_value)
self._last_pos = event.pos()
self._last_move_time = current_time
super().mouseMoveEvent(event)
def mouseReleaseEvent(self, event):
self._is_touching = False
if abs(self._velocity_y) > 0.1:
self._inertial_timer.start()
super().mouseReleaseEvent(event)
# ==================== 数据库函数 ====================
def get_product_detail_by_code(product_code):
serverName = 'DESKTOP-FTR9JGA:1433'
userName = 'sa'
passWord = 'abc123456789'
try:
conn = pymssql.connect(serverName, userName, passWord, "tempdb", charset='UTF8', tds_version='7.0')
except Exception as e:
print(f"数据库连接失败: {str(e)}")
return "用法用量信息待补充", "功能主治信息待补充"
cursor = conn.cursor()
sql_usage = "SELECT gsp_zycf FROM [byyyv6].[dbo].[t_item_info] WHERE [sx#] = %s"
sql_indications = "SELECT gsp_ypyt FROM [byyyv6].[dbo].[t_item_info] WHERE [sx#] = %s"
usage = "用法用量信息待补充"
indications = "功能主治信息待补充"
try:
cursor.execute(sql_usage, (product_code,))
usage_result = cursor.fetchone()
cursor.execute(sql_indications, (product_code,))
indications_result = cursor.fetchone()
if usage_result and usage_result[0]:
try:
if isinstance(usage_result[0], bytes):
usage = usage_result[0].encode('latin1').decode('GB18030')
else:
if isinstance(usage_result[0], str) and not all(ord(c) < 128 for c in usage_result[0]):
usage = usage_result[0].encode('latin1').decode('GB18030')
else:
usage = str(usage_result[0])
except:
usage = str(usage_result[0]) if usage_result[0] else "用法用量信息待补充"
if indications_result and indications_result[0]:
try:
if isinstance(indications_result[0], bytes):
indications = indications_result[0].encode('latin1').decode('GB18030')
else:
if isinstance(indications_result[0], str) and not all(ord(c) < 128 for c in indications_result[0]):
indications = indications_result[0].encode('latin1').decode('GB18030')
else:
indications = str(indications_result[0])
except:
indications = str(indications_result[0]) if indications_result[0] else "功能主治信息待补充"
except Exception as e:
print(f"查询产品详情失败: {str(e)}")
cursor.close()
conn.close()
return usage, indications
def get_product_valid_dates(product_code):
serverName = 'DESKTOP-FTR9JGA:1433'
userName = 'sa'
passWord = 'abc123456789'
try:
conn = pymssql.connect(serverName, userName, passWord, "tempdb", charset='UTF8', tds_version='7.0')
except Exception as e:
print(f"数据库连接失败: {str(e)}")
return []
cursor = conn.cursor()
sql = """
SELECT
batch_no AS 批号,
item_no AS 产品编码,
valid_date AS 有效期,
(in_qty - ISNULL(out_qty, 0)) AS 剩余数量,
in_amt / NULLIF(in_qty, 0) AS 成本均价
FROM [byyyv6].[dbo].[t_batch_total]
WHERE item_no = %s
AND (out_qty IS NULL OR out_qty < in_qty)
ORDER BY batch_no
"""
batch_info = []
try:
cursor.execute(sql, (product_code,))
results = cursor.fetchall()
for row in results:
batch = {}
try:
batch['batch_no'] = str(row[0]) if row[0] else ""
batch['item_no'] = str(row[1]) if row[1] else ""
if row[2]:
if isinstance(row[2], bytes):
try:
valid_date_str = row[2].encode('latin1').decode('GB18030')
except:
valid_date_str = str(row[2])
else:
valid_date_str = str(row[2])
if ' ' in valid_date_str:
batch['valid_date'] = valid_date_str.split(' ')[0]
elif 'T' in valid_date_str:
batch['valid_date'] = valid_date_str.split('T')[0]
else:
batch['valid_date'] = valid_date_str
else:
batch['valid_date'] = "未知"
batch['remaining_qty'] = int(row[3]) if row[3] else 0
batch['cost_avg'] = float(row[4]) if row[4] else 0.0
batch_info.append(batch)
except Exception as e:
print(f"处理批次信息时出错: {str(e)}")
continue
except Exception as e:
print(f"查询批次信息失败: {str(e)}")
cursor.close()
conn.close()
return batch_info
def get_products_by_category(xl_value):
serverName = 'DESKTOP-FTR9JGA:1433'
userName = 'sa'
passWord = 'abc123456789'
try:
conn = pymssql.connect(serverName, userName, passWord, "tempdb", charset='UTF8', tds_version='7.0')
except Exception as e:
print(f"数据库连接失败: {str(e)}")
return []
cursor = conn.cursor()
sql = """
SELECT
t.[sx#], t.na, t.gg, t.gsp_scqy, t.cd, t.lsj, t.dj, t.tm,
t.gsp_pjwh, t.gsp_zycf, t.gsp_ypyt, t.bzt,
COALESCE(SUM(b.in_qty - ISNULL(b.out_qty, 0)), 0) as stock
FROM [byyyv6].[dbo].[t_item_info] t
LEFT JOIN [byyyv6].[dbo].[t_batch_total] b ON t.[sx#] = b.item_no
WHERE t.xl = %s
AND (b.out_qty IS NULL OR b.out_qty < b.in_qty)
GROUP BY t.[sx#], t.na, t.gg, t.gsp_scqy, t.cd, t.lsj, t.dj, t.tm,
t.gsp_pjwh, t.gsp_zycf, t.gsp_ypyt, t.bzt
HAVING COALESCE(SUM(b.in_qty - ISNULL(b.out_qty, 0)), 0) > 0
ORDER BY stock DESC, t.[sx#]
"""
products = []
try:
cursor.execute(sql, (xl_value,))
spxx = cursor.fetchall()
for row in spxx:
product = {}
code = str(row[0])
product['code'] = code
# 构造价格字符串:零售价/会员价
retail_price = float(row[5]) if row[5] else 0.0
member_price = float(row[6]) if row[6] else retail_price
product['price'] = f"{retail_price:.2f}/{member_price:.2f}"
product['image'] = f'//Bf-202412262110/d/电脑桌面/广告图片2/{code}.jpg'
name_part = row[1] if row[1] is not None else ""
spec_part = row[2] if row[2] is not None else ""
try:
if isinstance(name_part, bytes):
name_part = name_part.encode('latin1').decode('GB18030')
elif isinstance(name_part, str) and not all(ord(c) < 128 for c in name_part):
name_part = name_part.encode('latin1').decode('GB18030')
if isinstance(spec_part, bytes):
spec_part = spec_part.encode('latin1').decode('GB18030')
elif isinstance(spec_part, str) and not all(ord(c) < 128 for c in spec_part):
spec_part = spec_part.encode('latin1').decode('GB18030')
except:
pass
product['name'] = f"{code}/{name_part}{spec_part}"
product['stock'] = int(row[12])
usage, indications = get_product_detail_by_code(code)
product['usage'] = usage
product['indications'] = indications
batch_info = get_product_valid_dates(code)
valid_dates = []
for batch in batch_info:
if batch.get('valid_date') != "未知":
try:
for fmt in ['%Y-%m-%d', '%Y/%m/%d']:
try:
d = datetime.strptime(batch['valid_date'], fmt).date()
valid_dates.append(d)
break
except:
continue
except:
pass
product['nearest_valid_date'] = min(valid_dates) if valid_dates else date(9999, 12, 31)
products.append(product)
except Exception as e:
print(f"查询产品失败: {str(e)}")
cursor.close()
conn.close()
products.sort(key=lambda x: x['nearest_valid_date'])
return products
def search_products(search_term):
"""根据关键词搜索产品,返回完整的12列信息"""
serverName = 'DESKTOP-FTR9JGA:1433'
userName = 'sa'
passWord = 'abc123456789'
try:
conn = pymssql.connect(serverName, userName, passWord, "byyyv6", charset='UTF8', tds_version='7.0')
except Exception as e:
print(f"数据库连接失败: {str(e)}")
return []
cursor = conn.cursor()
trace_code = None
if len(search_term) == 20 and search_term.isdigit():
trace_code = search_term[:7]
# ✅ 关键:必须 SELECT 所有需要的字段(共12个)
sql = """
SELECT
[sx#], na, gg, gsp_scqy, cd, lsj, dj, tm, gsp_pjwh, gsp_zycf, gsp_ypyt, bzt
FROM [byyyv6].[dbo].[t_item_info]
WHERE
na LIKE %s OR
CAST([sx#] AS NVARCHAR(50)) = %s OR
CAST(tm AS NVARCHAR(50)) = %s OR
gsp_pjwh LIKE %s OR
gsp_ypyt LIKE %s
"""
params = (
f'%{search_term}%', # na LIKE
search_term, # [sx#] =
search_term, # tm =
f'%{search_term}%', # gsp_pjwh LIKE
f'%{search_term}%' # gsp_ypyt LIKE
)
if trace_code:
sql += " OR zsmbsm LIKE %s"
params += (f'%{trace_code}',)
try:
cursor.execute(sql, params)
results = cursor.fetchall()
print(f"search_products 查询到 {len(results)} 条结果")
except Exception as e:
print(f"执行搜索SQL失败: {e}")
results = []
# 解码处理
decoded_results = []
for row in results:
if not row:
continue
decoded_row = []
for value in row:
if isinstance(value, bytes):
try:
decoded_value = value.decode('GB18030')
except:
try:
decoded_value = value.encode('latin1').decode('GB18030')
except:
decoded_value = str(value)
elif value is None:
decoded_value = ""
else:
decoded_value = str(value)
decoded_row.append(decoded_value)
# 补齐缺失字段到12位(防止索引越界)
while len(decoded_row) < 12:
decoded_row.append("")
decoded_results.append(tuple(decoded_row))
cursor.close()
conn.close()
return decoded_results
def search_get_product(search_term):
"""根据搜索词获取产品列表(显示全部结果,有库存优先,按有效期排序)"""
spxx = search_products(search_term)
if not spxx:
print(f"搜索 '{search_term}' 没有找到结果")
return []
serverName = 'DESKTOP-FTR9JGA:1433'
userName = 'sa'
passWord = 'abc123456789'
try:
conn = pymssql.connect(serverName, userName, passWord, "tempdb", charset='UTF8', tds_version='7.0')
except Exception as e:
print(f"数据库连接失败: {str(e)}")
return []
cursor = conn.cursor()
products = []
for i in range(len(spxx)):
row = spxx[i]
if len(row) < 12:
print(f"警告:第{i}行数据字段不足,补全处理")
row = list(row) + [''] * (12 - len(row))
try:
product_code = str(row[0]).strip() if row[0] else ""
if not product_code:
print(f"跳过无编码的产品(第{i}行)")
continue
product = {'code': product_code}
# 处理名称和规格(支持中文解码)
try:
name_part = row[1] if row[1] is not None else ""
spec_part = row[2] if row[2] is not None else ""
if isinstance(name_part, str) and not all(ord(c) < 128 for c in name_part):
try:
name_part = name_part.encode('latin1').decode('GB18030')
except:
pass
if isinstance(spec_part, str) and not all(ord(c) < 128 for c in spec_part):
try:
spec_part = spec_part.encode('latin1').decode('GB18030')
except:
pass
product['name'] = f"{product_code}/{name_part}{spec_part}"
except Exception as e:
print(f"处理产品名称时出错: {str(e)}")
product['name'] = f"{product_code}/产品信息"
# ✅ 提取价格(零售价/会员价)
try:
retail_price = float(row[5]) if row[5] and row[5].strip() != "" else 0.0
except (ValueError, TypeError):
retail_price = 0.0
try:
member_price = float(row[6]) if row[6] and row[6].strip() != "" else retail_price
except (ValueError, TypeError):
member_price = retail_price
product['price'] = f"{retail_price:.2f}/{member_price:.2f}"
product['image'] = f'//Bf-202412262110/d/电脑桌面/广告图片2/{product_code}.jpg'
# 获取库存
cursor.execute("""
SELECT SUM(COALESCE(in_qty, 0)) - SUM(COALESCE(out_qty, 0))
FROM [byyyv6].[dbo].[t_batch_total]
WHERE item_no = %s
""", (product_code,))
stock_result = cursor.fetchone()
total_stock = int(stock_result[0]) if stock_result and stock_result[0] is not None else 0
product['stock'] = max(0, total_stock)
# 获取用法用量、功能主治
usage, indications = get_product_detail_by_code(product_code)
product['usage'] = usage
product['indications'] = indications
# 获取批次信息用于排序
batch_info = get_product_valid_dates(product_code)
product['batch_info'] = batch_info
valid_dates = []
for batch in batch_info:
if batch.get('valid_date') and batch['valid_date'] != "未知":
for fmt in ['%Y-%m-%d', '%Y/%m/%d']:
try:
d = datetime.strptime(batch['valid_date'], fmt).date()
valid_dates.append(d)
break
except:
continue
product['nearest_valid_date'] = min(valid_dates) if valid_dates else date(9999, 12, 31)
products.append(product)
except Exception as inner_e:
print(f"处理第{i}个产品时出错: {str(inner_e)}")
continue
cursor.close()
conn.close()
print(f"总共查询到 {len(products)} 个产品")
# 排序:有库存优先,再按有效期由近到远
def sort_key(p):
has_stock = p['stock'] > 0
return (0 if has_stock else 1), p['nearest_valid_date']
products.sort(key=sort_key)
return products
# ==================== 产品详情弹窗 ====================
class ProductDetailDialog(QDialog):
def __init__(self, name, usage, indications, batch_info, product_code, parent=None):
super().__init__(parent)
self.name = name
self.usage = usage
self.indications = indications
self.batch_info = batch_info
self.product_code = product_code
self.initUI()
def initUI(self):
self.setWindowTitle("产品详情")
self.setFixedSize(1400, 700)
self.setStyleSheet("""
QDialog {
background-color: white;
border: 2px solid #3498db;
border-radius: 8px;
}
""")
main_layout = QHBoxLayout(self)
main_layout.setContentsMargins(10, 10, 10, 10)
main_layout.setSpacing(10)
left_widget = QWidget()
left_widget.setFixedWidth(900)
left_layout = QVBoxLayout(left_widget)
left_layout.setContentsMargins(0, 0, 0, 0)
name_label = QLabel(self.name)
name_label.setStyleSheet("""
QLabel {
font-size: 24px;
font-weight: bold;
color: #2c3e50;
border-bottom: 1px solid #ecf0f1;
padding-bottom: 12px;
}
""")
left_layout.addWidget(name_label)
usage_label = QLabel(f"用法用量: {self.usage}")
usage_label.setWordWrap(True)
usage_label.setStyleSheet("""
QLabel {
font-size: 24px;
color: #34495e;
margin-bottom: 20px;
}
""")
left_layout.addWidget(usage_label)
left_layout.addSpacing(20)
indications_label = QLabel(f"功能主治: {self.indications}")
indications_label.setWordWrap(True)
indications_label.setStyleSheet("""
QLabel {
font-size: 24px;
color: #34495e;
margin-top: 20px;
}
""")
left_layout.addWidget(indications_label)
batch_title = QLabel("批次信息:")
batch_title.setStyleSheet("""
QLabel {
font-size: 18px;
font-weight: bold;
color: #2c3e50;
margin-top: 20px;
}
""")
left_layout.addWidget(batch_title)
if self.batch_info:
self.batch_table = QTableWidget()
self.batch_table.setColumnCount(4)
self.batch_table.setHorizontalHeaderLabels(["批号", "有效期", "剩余数量","产品均价"])
self.batch_table.setRowCount(len(self.batch_info))
for i, batch in enumerate(self.batch_info):
self.batch_table.setItem(i, 0, QTableWidgetItem(batch.get('batch_no', '')))
self.batch_table.setItem(i, 1, QTableWidgetItem(batch.get('valid_date', '')))
self.batch_table.setItem(i, 2, QTableWidgetItem(str(batch.get('remaining_qty', 0))))
self.batch_table.setItem(i, 3, QTableWidgetItem(str(round(batch.get('cost_avg', 0),2))))
self.batch_table.horizontalHeader().setStretchLastSection(True)
self.batch_table.resizeColumnsToContents()
self.batch_table.setMaximumHeight(200)
left_layout.addWidget(self.batch_table)
else:
no_batch_label = QLabel("暂无批次信息")
no_batch_label.setStyleSheet("font-size: 16px; color: #7f8c8d;")
left_layout.addWidget(no_batch_label)
left_layout.addStretch()
right_widget = QWidget()
right_widget.setFixedWidth(450)
right_widget.setStyleSheet 根据这部分代码结合之前的要求把这部分代码补全
最新发布