Hermes CI Fix
9de6e522a4
- 数据层: product_sales表+导入脚本(93条, 酣客1-8月商品销售排行榜) - 后端: GET /api/cma/products/matrix?entity_id&months 横轴=销售趋势(线性回归), 纵轴=加权毛利率, 气泡=销售额 - 前端: ProductMatrix.vue ECharts散点图+象限卡片+明细表, 支持酣客/博海切换 - 验证: API四象限分类正确, 数据联动, 前端路由200, 全部通过
103 lines
4.9 KiB
Python
103 lines
4.9 KiB
Python
#!/usr/bin/env python3
|
|
"""导入酣客1-8月商品销售排行榜到product_sales表"""
|
|
import openpyxl
|
|
import glob
|
|
import os
|
|
import re
|
|
import pymysql
|
|
|
|
# DB连接
|
|
DB_CONFIG = {
|
|
'host': '127.0.0.1',
|
|
'user': 'cma_user',
|
|
'password': 'cma_pass_2026',
|
|
'database': 'cma',
|
|
'charset': 'utf8mb4',
|
|
}
|
|
|
|
# 8份排行榜文件（URL编码的路径）
|
|
files = {
|
|
'2026-01': '/root/.hermes/profiles/wecom-finance/cache/documents/doc_7c0e51888b10_%E5%95%86%E5%93%81%E9%94%80%E5%94%AE%E6%8E%92%E8%A1%8C%E6%A6%9C%EF%BC%88%E9%99%95%E8%A5%BF%E9%85%A3%E5%AE%A22026%E5%B9%B41%E6%9C%88%EF%BC%89.xlsx',
|
|
'2026-02': '/root/.hermes/profiles/wecom-finance/cache/documents/doc_309cee3d3d78_%E5%95%86%E5%93%81%E9%94%80%E5%94%AE%E6%8E%92%E8%A1%8C%E6%A6%9C%EF%BC%88%E9%99%95%E8%A5%BF%E9%85%A3%E5%AE%A22026%E5%B9%B42%E6%9C%88%EF%BC%89.xlsx',
|
|
'2026-03': '/root/.hermes/profiles/wecom-finance/cache/documents/doc_fdc1228e7173_%E5%95%86%E5%93%81%E9%94%80%E5%94%AE%E6%8E%92%E8%A1%8C%E6%A6%9C%EF%BC%88%E9%99%95%E8%A5%BF%E9%85%A3%E5%AE%A22026%E5%B9%B43%E6%9C%88%EF%BC%89.xlsx',
|
|
'2026-04': '/root/.hermes/profiles/wecom-finance/cache/documents/doc_da10cb16765c_%E5%95%86%E5%93%81%E9%94%80%E5%94%AE%E6%8E%92%E8%A1%8C%E6%A6%9C%EF%BC%88%E9%99%95%E8%A5%BF%E9%85%A3%E5%AE%A22026%E5%B9%B44%E6%9C%88%EF%BC%89.xlsx',
|
|
'2026-05': '/root/.hermes/profiles/wecom-finance/cache/documents/doc_bda951a0f6e3_%E5%95%86%E5%93%81%E9%94%80%E5%94%AE%E6%8E%92%E8%A1%8C%E6%A6%9C%EF%BC%88%E9%99%95%E8%A5%BF%E9%85%A3%E5%AE%A22026%E5%B9%B45%E6%9C%88%EF%BC%89.xlsx',
|
|
'2026-06': '/root/.hermes/profiles/wecom-finance/cache/documents/doc_6fcb80d2a02d_%E5%95%86%E5%93%81%E9%94%80%E5%94%AE%E6%8E%92%E8%A1%8C%E6%A6%9C%EF%BC%88%E9%99%95%E8%A5%BF%E9%85%A3%E5%AE%A22026%E5%B9%B46%E6%9C%88%EF%BC%89.xlsx',
|
|
'2026-07': '/root/.hermes/profiles/wecom-finance/cache/documents/doc_a449da246d17_%E5%95%86%E5%93%81%E9%94%80%E5%94%AE%E6%8E%92%E8%A1%8C%E6%A6%9C%EF%BC%88%E9%99%95%E8%A5%BF%E9%85%A3%E5%AE%A22026%E5%B9%B47%E6%9C%88%EF%BC%89.xlsx',
|
|
'2026-08': '/root/.hermes/profiles/wecom-finance/cache/documents/doc_ac2b807b1517_%E5%95%86%E5%93%81%E9%94%80%E5%94%AE%E6%8E%92%E8%A1%8C%E6%A6%9C%EF%BC%88%E9%99%95%E8%A5%BF%E9%85%A3%E5%AE%A22026%E5%B9%B48%E6%9C%88%EF%BC%89.xlsx',
|
|
}
|
|
|
|
def import_file(conn, cursor, period, path):
|
|
"""导入单个月份Excel"""
|
|
if not os.path.exists(path):
|
|
print(f" ⚠️ 文件不存在: {period}")
|
|
return 0
|
|
wb = openpyxl.load_workbook(path, read_only=True, data_only=True)
|
|
ws = wb[wb.sheetnames[0]]
|
|
count = 0
|
|
for r in range(2, ws.max_row + 1):
|
|
code = ws.cell(r, 2).value # 商品编码
|
|
name = ws.cell(r, 3).value # 商品名称
|
|
sales = ws.cell(r, 4).value # 销售金额
|
|
cost = ws.cell(r, 6).value # 成本金额
|
|
gross = ws.cell(r, 7).value # 毛利
|
|
margin = ws.cell(r, 10).value # 毛利率(%)
|
|
qty = ws.cell(r, 13).value # 销售数量
|
|
unit = ws.cell(r, 14).value # 单位
|
|
|
|
# 跳过合计行（编码为空）和空行
|
|
if code is None or str(code).strip() == '':
|
|
continue
|
|
if name is None or str(name).strip() == '':
|
|
continue
|
|
# 跳过汇总行（如"上期库存"）
|
|
if '上期' in str(name) or '合计' in str(name) or '总计' in str(name):
|
|
continue
|
|
|
|
code = str(code).strip()
|
|
name = str(name).strip()
|
|
sales = float(sales or 0)
|
|
cost = float(cost or 0)
|
|
gross = float(gross or 0)
|
|
margin = float(margin or 0)
|
|
qty = int(qty or 0)
|
|
unit = str(unit or '').strip()
|
|
|
|
cursor.execute("""
|
|
INSERT INTO product_sales (entity_id, product_code, product_name, period_month,
|
|
sales_amount, cost_amount, gross_profit, gross_margin_rate, sales_qty, unit)
|
|
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
|
|
ON DUPLICATE KEY UPDATE
|
|
product_name=VALUES(product_name),
|
|
sales_amount=VALUES(sales_amount),
|
|
cost_amount=VALUES(cost_amount),
|
|
gross_profit=VALUES(gross_profit),
|
|
gross_margin_rate=VALUES(gross_margin_rate),
|
|
sales_qty=VALUES(sales_qty),
|
|
unit=VALUES(unit)
|
|
""", (1, code, name, period, sales, cost, gross, margin, qty, unit))
|
|
count += 1
|
|
wb.close()
|
|
return count
|
|
|
|
def main():
|
|
conn = pymysql.connect(**DB_CONFIG)
|
|
cursor = conn.cursor()
|
|
total = 0
|
|
for period, path in files.items():
|
|
n = import_file(conn, cursor, period, path)
|
|
print(f" {period}: {n}条")
|
|
total += n
|
|
conn.commit()
|
|
print(f"\n✅ 共导入 {total} 条")
|
|
|
|
# 验证
|
|
cursor.execute("SELECT period_month, COUNT(*), ROUND(SUM(sales_amount)) FROM product_sales GROUP BY period_month ORDER BY period_month")
|
|
for row in cursor.fetchall():
|
|
print(f" {row[0]}: {row[1]}商品, 销售合计{row[2]:,.0f}")
|
|
cursor.close()
|
|
conn.close()
|
|
|
|
if __name__ == '__main__':
|
|
main()
|