722 lines
26 KiB
Python
722 lines
26 KiB
Python
"""出库数据路由"""
|
||
from flask import Blueprint, request, jsonify, session, redirect, url_for, render_template, send_file, Response
|
||
from services.db import query
|
||
from routes.auth import login_required, role_required, operation_required
|
||
from services.field_perms import filter_rows as _filter_field_rows
|
||
|
||
data_bp = Blueprint('data', __name__)
|
||
|
||
# 筛选项变化频率远低于页面访问频率。短时缓存仅用于筛选项，避免每次
|
||
# 打开出库明细都执行 5 次 DISTINCT 全表查询；数据更新后最多 60 秒刷新。
|
||
_data_filter_cache = {'expires_at': 0, 'data': None}
|
||
|
||
|
||
|
||
@data_bp.route('/data')
|
||
@login_required
|
||
def data_page():
|
||
if not role_required('data'):
|
||
return redirect(url_for('auth.index'))
|
||
return render_template('base.html', page='data_detail')
|
||
|
||
|
||
@data_bp.route('/data/summary')
|
||
@login_required
|
||
def data_summary_page():
|
||
if not role_required('data'):
|
||
return redirect(url_for('auth.index'))
|
||
return render_template('base.html', page='data_summary')
|
||
|
||
|
||
@data_bp.route('/data/detail')
|
||
@login_required
|
||
def data_detail_page():
|
||
if not role_required('data'):
|
||
return redirect(url_for('auth.index'))
|
||
return render_template('base.html', page='data_detail')
|
||
|
||
|
||
# ─── API: 数据查询 ───
|
||
@data_bp.route('/api/data/list')
|
||
@login_required
|
||
def data_list():
|
||
page = int(request.args.get('page', 1))
|
||
size = int(request.args.get('size', 50))
|
||
offset = (page - 1) * size
|
||
|
||
date_from = request.args.get('date_from', '')
|
||
date_to = request.args.get('date_to', '')
|
||
brand_filter = request.args.get('brand', '')
|
||
shop_filter = request.args.get('shop', '')
|
||
source_filter = request.args.get('source', '')
|
||
warehouse_filter = request.args.get('warehouse', '')
|
||
search = request.args.get('search', '')
|
||
|
||
where = ["货品编号 != '合计:'"]
|
||
params = []
|
||
|
||
if date_from:
|
||
where.append("日期 >= %s")
|
||
params.append(date_from)
|
||
if date_to:
|
||
where.append("日期 <= %s")
|
||
params.append(date_to)
|
||
if brand_filter:
|
||
where.append("品牌=%s")
|
||
params.append(brand_filter)
|
||
if shop_filter:
|
||
where.append("店铺=%s")
|
||
params.append(shop_filter)
|
||
if source_filter:
|
||
src_vals = {source_filter}
|
||
display_name = SOURCE_MAP_CN.get(source_filter)
|
||
if display_name:
|
||
src_vals.add(display_name)
|
||
else:
|
||
# Check if source_filter is a known display name (reverse lookup)
|
||
for code, name in SOURCE_MAP_CN.items():
|
||
if source_filter == name:
|
||
src_vals.add(code)
|
||
break
|
||
if len(src_vals) > 1:
|
||
placeholders = ', '.join(['%s'] * len(src_vals))
|
||
where.append(f"数据来源 IN ({placeholders})")
|
||
params.extend(sorted(src_vals))
|
||
else:
|
||
where.append("数据来源=%s")
|
||
params.append(source_filter)
|
||
if warehouse_filter:
|
||
where.append("仓库=%s")
|
||
params.append(warehouse_filter)
|
||
if search:
|
||
where.append("(货品编号 LIKE %s OR 货品名称 LIKE %s)")
|
||
params.extend([f"%{search}%", f"%{search}%"])
|
||
|
||
where_clause = " AND ".join(where)
|
||
|
||
total = query(f"SELECT COUNT(*) AS cnt FROM daily_outbound WHERE {where_clause}", params)[0]['cnt']
|
||
|
||
rows = query(f"""
|
||
SELECT id, 日期, 货品编号, 货品名称, 品牌, 仓库, 店铺,
|
||
一级类目, 二级类目, 三级类目, 货品数量, 货品成本, 数据来源
|
||
FROM daily_outbound
|
||
WHERE {where_clause}
|
||
ORDER BY 日期 DESC, 货品数量 DESC, id DESC
|
||
LIMIT {size} OFFSET {offset}
|
||
""", params)
|
||
|
||
for row in rows:
|
||
if '日期' in row and row['日期']:
|
||
row['日期'] = row['日期'].strftime('%Y-%m-%d') if hasattr(row['日期'], 'strftime') else str(row['日期'])[:10]
|
||
|
||
_filter_field_rows('data_detail', rows, session.get('user_id'), session.get('role'),
|
||
page_view=bool(role_required('data_detail')),
|
||
page_edit=operation_required('data_detail', 'query'))
|
||
return jsonify({
|
||
"code": 0,
|
||
"data": {
|
||
"total": total,
|
||
"page": page,
|
||
"size": size,
|
||
"rows": rows
|
||
}
|
||
})
|
||
|
||
|
||
@data_bp.route('/api/data/export')
|
||
@login_required
|
||
def data_export():
|
||
"""导出历史出库明细数据，沿用出库明细页面当前筛选条件。"""
|
||
from io import BytesIO
|
||
import openpyxl
|
||
from openpyxl.styles import Font, Alignment, PatternFill
|
||
|
||
date_from = request.args.get('date_from', '')
|
||
date_to = request.args.get('date_to', '')
|
||
brand_filter = request.args.get('brand', '')
|
||
shop_filter = request.args.get('shop', '')
|
||
source_filter = request.args.get('source', '')
|
||
warehouse_filter = request.args.get('warehouse', '')
|
||
search = request.args.get('search', '')
|
||
selected_fields = [f for f in request.args.get('fields', '').split(',') if f]
|
||
|
||
field_defs = {
|
||
'date': ('日期', 14), 'product_code': ('货品编号', 22), 'product_name': ('货品名称', 30),
|
||
'brand': ('品牌', 16), 'quantity': ('货品数量', 12), 'unit_cost': ('成本价', 14),
|
||
'amount': ('金额', 14), 'warehouse': ('仓库', 18), 'shop': ('店铺', 18),
|
||
'source': ('数据来源', 16),
|
||
}
|
||
if not selected_fields:
|
||
selected_fields = list(field_defs)
|
||
selected_fields = [f for f in selected_fields if f in field_defs]
|
||
if not selected_fields:
|
||
return jsonify({'code': 400, 'msg': '请至少选择一个导出字段'}), 400
|
||
|
||
where = ["货品编号 != '合计:'"]
|
||
params = []
|
||
if date_from:
|
||
where.append("日期 >= %s"); params.append(date_from)
|
||
if date_to:
|
||
where.append("日期 <= %s"); params.append(date_to)
|
||
if brand_filter:
|
||
where.append("品牌=%s"); params.append(brand_filter)
|
||
if shop_filter:
|
||
where.append("店铺=%s"); params.append(shop_filter)
|
||
if source_filter:
|
||
src_vals = {source_filter}
|
||
display_name = SOURCE_MAP_CN.get(source_filter)
|
||
if display_name:
|
||
src_vals.add(display_name)
|
||
else:
|
||
for code, name in SOURCE_MAP_CN.items():
|
||
if source_filter == name:
|
||
src_vals.add(code)
|
||
break
|
||
placeholders = ', '.join(['%s'] * len(src_vals))
|
||
where.append(f"数据来源 IN ({placeholders})")
|
||
params.extend(sorted(src_vals))
|
||
if warehouse_filter:
|
||
where.append("仓库=%s"); params.append(warehouse_filter)
|
||
if search:
|
||
where.append("(货品编号 LIKE %s OR 货品名称 LIKE %s)")
|
||
params.extend([f"%{search}%", f"%{search}%"])
|
||
|
||
rows = query(f"""
|
||
SELECT 日期, 货品编号, 货品名称, 品牌, 货品数量, 货品成本,
|
||
仓库, 店铺, 数据来源
|
||
FROM daily_outbound
|
||
WHERE {' AND '.join(where)}
|
||
ORDER BY 日期 DESC, 货品数量 DESC
|
||
""", params)
|
||
|
||
headers = [field_defs[f][0] for f in selected_fields]
|
||
source_names = SOURCE_MAP_CN
|
||
wb = openpyxl.Workbook()
|
||
ws = wb.active
|
||
ws.title = '历史出库明细'
|
||
ws.append(headers)
|
||
for cell in ws[1]:
|
||
cell.font = Font(bold=True, color='FFFFFF')
|
||
cell.fill = PatternFill('solid', fgColor='1890FF')
|
||
cell.alignment = Alignment(horizontal='center', vertical='center')
|
||
for row in rows:
|
||
total_cost = float(row.get('货品成本') or 0)
|
||
quantity = float(row.get('货品数量') or 0)
|
||
values = []
|
||
for field in selected_fields:
|
||
key = field_defs[field][0]
|
||
if field == 'date':
|
||
value = row.get('日期', '')
|
||
value = value.strftime('%Y-%m-%d') if value and hasattr(value, 'strftime') else (str(value)[:10] if value else '')
|
||
elif field == 'quantity':
|
||
value = quantity
|
||
elif field == 'unit_cost':
|
||
value = round(total_cost / quantity, 4) if quantity else 0
|
||
elif field == 'amount':
|
||
value = total_cost
|
||
elif field == 'source':
|
||
value = source_names.get(row.get('数据来源'), row.get('数据来源') or '')
|
||
else:
|
||
value = row.get(key, '')
|
||
values.append(value if value is not None else '')
|
||
ws.append(values)
|
||
widths = [field_defs[f][1] for f in selected_fields]
|
||
for idx, width in enumerate(widths, 1):
|
||
ws.column_dimensions[openpyxl.utils.get_column_letter(idx)].width = width
|
||
ws.freeze_panes = 'A2'
|
||
ws.auto_filter.ref = ws.dimensions
|
||
output = BytesIO()
|
||
wb.save(output)
|
||
output.seek(0)
|
||
return send_file(output, as_attachment=True,
|
||
download_name='历史出库明细.xlsx',
|
||
mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
|
||
|
||
|
||
SOURCE_MAP_CN = {
|
||
'wdt': '旺店通',
|
||
'jdbz': '京东泊紫',
|
||
'jdgj': '京东国际',
|
||
'jdjwt': '京东洁维塔',
|
||
'tmgj': '天猫国际',
|
||
'dymuto': '抖音M店',
|
||
'dymtuo': '抖音M店',
|
||
'jdsmj': '京东适美家',
|
||
}
|
||
|
||
|
||
# Reverse map: Chinese name → code, so stale Chinese-name entries in DB get normalized back
|
||
_SOURCE_MAP_REVERSE = {v: k for k, v in SOURCE_MAP_CN.items()}
|
||
|
||
|
||
def _dedup_sources(raw_sources):
|
||
"""Deduplicate sources by display name, keeping the raw DB value (prefers codes over
|
||
Chinese names since SQL sorts codes first), preserving order.
|
||
Also normalizes any Chinese-name entries back to canonical codes."""
|
||
seen = set()
|
||
deduped = []
|
||
for s in raw_sources:
|
||
# normalize: if a Chinese name is in DB, map it back to the code
|
||
code = _SOURCE_MAP_REVERSE.get(s)
|
||
if code is not None:
|
||
s = code
|
||
display = SOURCE_MAP_CN.get(s, s)
|
||
if display not in seen:
|
||
seen.add(display)
|
||
deduped.append(s) # keep the raw DB value so SQL filtering still works
|
||
return deduped
|
||
|
||
|
||
@data_bp.route('/api/data/dates')
|
||
@login_required
|
||
def data_dates():
|
||
import time
|
||
now = time.time()
|
||
if _data_filter_cache['data'] is not None and now < _data_filter_cache['expires_at']:
|
||
return jsonify({'code': 0, 'data': _data_filter_cache['data']})
|
||
|
||
dates = query("SELECT DISTINCT 日期 FROM daily_outbound ORDER BY 日期 DESC")
|
||
brands = query("SELECT DISTINCT 品牌 FROM daily_outbound ORDER BY 品牌")
|
||
shops = query("SELECT DISTINCT 店铺 FROM daily_outbound WHERE 店铺 IS NOT NULL AND 店铺 != '' ORDER BY 店铺")
|
||
sources = query("SELECT DISTINCT 数据来源 FROM daily_outbound WHERE 数据来源 IS NOT NULL AND 数据来源 != '' ORDER BY 数据来源")
|
||
warehouses = query("SELECT DISTINCT 仓库 FROM daily_outbound WHERE 仓库 IS NOT NULL AND 仓库 != '' ORDER BY 仓库")
|
||
data = {
|
||
"dates": [d['日期'].strftime('%Y-%m-%d') if hasattr(d['日期'], 'strftime') else d['日期'] for d in dates],
|
||
"brands": [b['品牌'] for b in brands],
|
||
"shops": [s['店铺'] for s in shops],
|
||
"sources": _dedup_sources([s['数据来源'] for s in sources]),
|
||
"warehouses": [w['仓库'] for w in warehouses]
|
||
}
|
||
_data_filter_cache.update({'expires_at': now + 60, 'data': data})
|
||
return jsonify({'code': 0, 'data': data})
|
||
|
||
|
||
@data_bp.route('/api/data/summary')
|
||
@login_required
|
||
def outbound_summary():
|
||
"""按货品编号+日期汇总出库量"""
|
||
page = int(request.args.get('page', 1))
|
||
size = int(request.args.get('size', 50))
|
||
offset = (page - 1) * size
|
||
|
||
date_from = request.args.get('date_from', '')
|
||
date_to = request.args.get('date_to', '')
|
||
search = request.args.get('search', '')
|
||
where = ["货品编号 != '' AND 货品编号 IS NOT NULL"]
|
||
params = []
|
||
|
||
if date_from:
|
||
where.append("DATE(日期) >= %s")
|
||
params.append(date_from)
|
||
if date_to:
|
||
# 日期选择器按自然日筛选，包含结束日期当天的全部记录。
|
||
where.append("DATE(日期) <= %s")
|
||
params.append(date_to)
|
||
if search:
|
||
where.append("(货品编号 LIKE %s OR 货品名称 LIKE %s)")
|
||
params.extend([f"%{search}%", f"%{search}%"])
|
||
|
||
where_clause = " AND ".join(where)
|
||
|
||
# 汇总查询
|
||
count_result = query(f"""
|
||
SELECT COUNT(DISTINCT CONCAT(日期, '_', 货品编号)) AS cnt
|
||
FROM daily_outbound
|
||
WHERE {where_clause}
|
||
""", params)
|
||
total = count_result[0]['cnt'] if count_result else 0
|
||
|
||
rows = query(f"""
|
||
SELECT 日期, 货品编号, 货品名称, 品牌,
|
||
SUM(货品数量) AS total_qty,
|
||
ROUND(SUM(货品成本), 2) AS total_cost
|
||
FROM daily_outbound
|
||
WHERE {where_clause}
|
||
GROUP BY 日期, 货品编号, 货品名称, 品牌
|
||
ORDER BY 日期 DESC, total_qty DESC
|
||
LIMIT {size} OFFSET {offset}
|
||
""", params)
|
||
|
||
for row in rows:
|
||
if '日期' in row and row['日期']:
|
||
row['日期'] = row['日期'].strftime('%Y-%m-%d') if hasattr(row['日期'], 'strftime') else str(row['日期'])[:10]
|
||
row['total_qty'] = int(row['total_qty']) if row['total_qty'] else 0
|
||
row['total_cost'] = float(row['total_cost']) if row['total_cost'] else 0
|
||
|
||
summary_result = query(f"""
|
||
SELECT COALESCE(SUM(货品数量), 0) AS total_qty,
|
||
COALESCE(ROUND(SUM(货品成本), 2), 0) AS total_cost
|
||
FROM daily_outbound
|
||
WHERE {where_clause}
|
||
""", params)
|
||
summary = summary_result[0] if summary_result else {}
|
||
|
||
return jsonify({
|
||
"code": 0,
|
||
"data": {
|
||
"total": total,
|
||
"page": page,
|
||
"size": size,
|
||
"summary": {
|
||
"total_qty": int(summary.get('total_qty') or 0),
|
||
"total_cost": float(summary.get('total_cost') or 0)
|
||
},
|
||
"total_qty": int(summary.get('total_qty') or 0),
|
||
"total_cost": float(summary.get('total_cost') or 0),
|
||
"rows": rows
|
||
}
|
||
})
|
||
|
||
|
||
@data_bp.route('/api/data/summary-total')
|
||
@login_required
|
||
def outbound_summary_total():
|
||
"""返回数据汇总当前筛选条件下的全量合计，不受分页影响。"""
|
||
date_from = request.args.get('date_from', '')
|
||
date_to = request.args.get('date_to', '')
|
||
search = request.args.get('search', '')
|
||
where = ["货品编号 != '' AND 货品编号 IS NOT NULL"]
|
||
params = []
|
||
if date_from:
|
||
where.append("DATE(日期) >= %s")
|
||
params.append(date_from)
|
||
if date_to:
|
||
where.append("DATE(日期) <= %s")
|
||
params.append(date_to)
|
||
if search:
|
||
where.append("(货品编号 LIKE %s OR 货品名称 LIKE %s)")
|
||
params.extend([f"%{search}%", f"%{search}%"])
|
||
result = query(f"""
|
||
SELECT COALESCE(SUM(货品数量), 0) AS total_qty,
|
||
COALESCE(ROUND(SUM(货品成本), 2), 0) AS total_cost
|
||
FROM daily_outbound WHERE {' AND '.join(where)}
|
||
""", params)
|
||
row = result[0] if result else {}
|
||
return jsonify({'code': 0, 'data': {
|
||
'total_qty': int(row.get('total_qty') or 0),
|
||
'total_cost': float(row.get('total_cost') or 0)
|
||
}})
|
||
|
||
|
||
@data_bp.route('/api/data/summary-stats')
|
||
@login_required
|
||
def outbound_summary_stats():
|
||
"""出库汇总统计：总出库量 + 趋势（按月/日，可按SKU筛选）"""
|
||
search = request.args.get('search', '')
|
||
group_by = request.args.get('group_by', 'month') # month or day
|
||
month = request.args.get('month', '') # 钻取某月时传入 YYYY-MM
|
||
|
||
where = ["货品编号 != '' AND 货品编号 IS NOT NULL"]
|
||
params = []
|
||
|
||
if search:
|
||
where.append("(货品编号 LIKE %s OR 货品名称 LIKE %s)")
|
||
params.extend([f"%{search}%", f"%{search}%"])
|
||
|
||
where_clause = " AND ".join(where)
|
||
|
||
# 总出库量（不受钻取影响）
|
||
total = query(f"""
|
||
SELECT SUM(货品数量) AS total_out_qty,
|
||
COUNT(DISTINCT 货品编号) AS total_sku,
|
||
COUNT(DISTINCT 日期) AS total_days
|
||
FROM daily_outbound
|
||
WHERE {where_clause}
|
||
""", params)
|
||
|
||
# 趋势数据
|
||
if group_by == 'day' and month:
|
||
# 钻取：查看某月的每日趋势
|
||
where.append("DATE_FORMAT(日期, '%%Y-%%m') = %s")
|
||
params.append(month)
|
||
where_clause = " AND ".join(where)
|
||
trend = query(f"""
|
||
SELECT 日期, SUM(货品数量) AS day_qty
|
||
FROM daily_outbound
|
||
WHERE {where_clause}
|
||
GROUP BY 日期
|
||
ORDER BY 日期 ASC
|
||
""", params)
|
||
for row in trend:
|
||
if '日期' in row and row['日期']:
|
||
row['日期'] = row['日期'].strftime('%Y-%m-%d') if hasattr(row['日期'], 'strftime') else str(row['日期'])[:10]
|
||
row['day_qty'] = int(row['day_qty']) if row['day_qty'] else 0
|
||
else:
|
||
# 按月聚合
|
||
trend = query(f"""
|
||
SELECT DATE_FORMAT(日期, '%%Y-%%m') AS month, SUM(货品数量) AS month_qty
|
||
FROM daily_outbound
|
||
WHERE {where_clause}
|
||
GROUP BY DATE_FORMAT(日期, '%%Y-%%m')
|
||
ORDER BY month ASC
|
||
""", params)
|
||
for row in trend:
|
||
row['month'] = row.get('month') or ''
|
||
row['month_qty'] = int(row.get('month_qty') or 0)
|
||
|
||
stats = total[0] if total else {}
|
||
|
||
return jsonify({
|
||
"code": 0,
|
||
"data": {
|
||
"total_out_qty": int(stats.get('total_out_qty') or 0),
|
||
"total_sku": int(stats.get('total_sku') or 0),
|
||
"total_days": int(stats.get('total_days') or 0),
|
||
"group_by": group_by,
|
||
"month": month,
|
||
"trend": trend
|
||
}
|
||
})
|
||
|
||
|
||
@data_bp.route('/api/data/sku-trend')
|
||
@login_required
|
||
def outbound_sku_trend():
|
||
"""商品日出库趋势：按日聚合出库量，排除赠品/小样（可指定商品）"""
|
||
product_code = request.args.get('product_code', '')
|
||
search = request.args.get('search', '')
|
||
date_from = request.args.get('date_from', '')
|
||
date_to = request.args.get('date_to', '')
|
||
|
||
where = ["d.货品编号 != '' AND d.货品编号 IS NOT NULL"]
|
||
params = []
|
||
|
||
# 排除赠品、小样：products.product_type='赠品' 或 名称含赠品/小样
|
||
where.append("(p.product_type IS NULL OR p.product_type NOT IN ('赠品','小样'))")
|
||
where.append("(d.货品名称 NOT LIKE '%%赠品%%' AND d.货品名称 NOT LIKE '%%小样%%')")
|
||
|
||
if product_code:
|
||
where.append("d.货品编号=%s")
|
||
params.append(product_code)
|
||
if search:
|
||
where.append("(d.货品编号 LIKE %s OR d.货品名称 LIKE %s)")
|
||
params.extend([f"%{search}%", f"%{search}%"])
|
||
if date_from:
|
||
where.append("d.日期 >= %s")
|
||
params.append(date_from)
|
||
if date_to:
|
||
where.append("d.日期 <= %s")
|
||
params.append(date_to)
|
||
|
||
where_clause = " AND ".join(where)
|
||
|
||
trend = query(f"""
|
||
SELECT d.日期, SUM(d.货品数量) AS day_qty
|
||
FROM daily_outbound d
|
||
LEFT JOIN products p ON p.product_code = d.货品编号
|
||
WHERE {where_clause}
|
||
GROUP BY d.日期
|
||
ORDER BY d.日期 ASC
|
||
""", params)
|
||
|
||
for row in trend:
|
||
if '日期' in row and row['日期']:
|
||
row['日期'] = row['日期'].strftime('%Y-%m-%d') if hasattr(row['日期'], 'strftime') else str(row['日期'])[:10]
|
||
row['day_qty'] = int(row['day_qty']) if row['day_qty'] else 0
|
||
|
||
total_qty = sum(row['day_qty'] for row in trend)
|
||
return jsonify({
|
||
"code": 0,
|
||
"data": {
|
||
"total_qty": total_qty,
|
||
"product_code": product_code,
|
||
"trend": trend
|
||
}
|
||
})
|
||
|
||
|
||
@data_bp.route('/api/data/sku-detail')
|
||
@login_required
|
||
def outbound_sku_detail():
|
||
"""查看某个SKU在某天的出库明细（按仓库+店铺展开）"""
|
||
product_code = request.args.get('product_code', '')
|
||
date_str = request.args.get('date', '')
|
||
|
||
if not product_code or not date_str:
|
||
return jsonify({"code": 1, "msg": "缺少参数"})
|
||
|
||
rows = query("""
|
||
SELECT 仓库, 店铺, 数据来源,
|
||
SUM(货品数量) AS qty,
|
||
ROUND(SUM(货品成本), 2) AS cost
|
||
FROM daily_outbound
|
||
WHERE 货品编号 = %s AND 日期 = %s
|
||
GROUP BY 仓库, 店铺, 数据来源
|
||
ORDER BY qty DESC
|
||
""", (product_code, date_str))
|
||
|
||
for row in rows:
|
||
row['qty'] = int(row['qty']) if row['qty'] else 0
|
||
row['cost'] = float(row['cost']) if row['cost'] else 0
|
||
|
||
return jsonify({
|
||
"code": 0,
|
||
"data": {
|
||
"product_code": product_code,
|
||
"date": date_str,
|
||
"details": rows
|
||
}
|
||
})
|
||
|
||
|
||
@data_bp.route('/api/data/search_filters')
|
||
@login_required
|
||
def data_search_filters():
|
||
field = request.args.get('field', '')
|
||
q = request.args.get('q', '')
|
||
if not field or not q:
|
||
return jsonify({"code": 0, "data": []})
|
||
col_map = {'warehouse': '仓库', 'shop': '店铺', 'brand': '品牌'}
|
||
col = col_map.get(field, field)
|
||
kw = f'%{q}%'
|
||
rows = query(f"SELECT DISTINCT `{col}` FROM daily_outbound WHERE `{col}` LIKE %s AND `{col}` IS NOT NULL AND `{col}` != '' ORDER BY `{col}` LIMIT 20", [kw])
|
||
return jsonify({"code": 0, "data": [r[col] for r in rows]})
|
||
|
||
|
||
# ─── API: 出库明细导入 ───
|
||
OUTBOUND_IMPORT_HEADERS = [
|
||
'日期', '货品编号', '货品名称', '品牌', '仓库', '店铺',
|
||
'一级类目', '二级类目', '三级类目', '货品数量', '货品成本', '数据来源',
|
||
]
|
||
OUTBOUND_IMPORT_SAMPLE = [
|
||
'2026-09-15', 'POZ-XFS-7645-TM', '示例：泊紫汀兰青少年去屑止痒洗发水', '泊紫汀兰',
|
||
'1蝶雅广州新仓(蝶雅自营)', '抖音-泊紫汀兰Pozitilan母婴旗舰店',
|
||
'个人护理', '', '', '6', '44.0100', 'wdt',
|
||
]
|
||
|
||
|
||
def _resolve_outbound_source(val):
|
||
"""数据来源支持填写编码（wdt）或中文名（旺店通），统一存编码。"""
|
||
from routes.inventory import _import_text
|
||
s = _import_text(val)
|
||
if not s:
|
||
return 'wdt'
|
||
if s in SOURCE_MAP_CN:
|
||
return s
|
||
for code, name in SOURCE_MAP_CN.items():
|
||
if s == name:
|
||
return code
|
||
return s
|
||
|
||
|
||
@data_bp.route('/api/data/import_template')
|
||
@login_required
|
||
def data_import_template():
|
||
"""下载出库明细导入模板 CSV"""
|
||
if not operation_required('data_detail', 'import'):
|
||
return jsonify({"code": 403, "msg": "无导入权限"})
|
||
import csv
|
||
import io
|
||
output = io.StringIO()
|
||
writer = csv.writer(output)
|
||
writer.writerow(OUTBOUND_IMPORT_HEADERS)
|
||
writer.writerow(OUTBOUND_IMPORT_SAMPLE)
|
||
csv_data = output.getvalue()
|
||
output.close()
|
||
return Response(
|
||
csv_data,
|
||
mimetype='text/csv',
|
||
headers={'Content-Disposition': 'attachment; filename=outbound_import_template.csv'}
|
||
)
|
||
|
||
|
||
@data_bp.route('/api/data/import', methods=['POST'])
|
||
@login_required
|
||
def data_import():
|
||
"""导入出库明细 CSV：同「日期+数据来源+货品编号+仓库+店铺」先删后插，避免重复。"""
|
||
if not operation_required('data_detail', 'import'):
|
||
return jsonify({"code": 403, "msg": "无导入权限"})
|
||
|
||
if 'file' not in request.files:
|
||
return jsonify({"code": 400, "msg": "未上传文件"})
|
||
f = request.files['file']
|
||
if not f.filename:
|
||
return jsonify({"code": 400, "msg": "未选择文件"})
|
||
|
||
from routes.inventory import (
|
||
_parse_import_rows, _normalize_import_date, _import_int, _import_float,
|
||
_import_text, _is_invalid_product_code,
|
||
)
|
||
|
||
headers, data_rows = _parse_import_rows(f.read())
|
||
if headers is None:
|
||
return jsonify({"code": 400, "msg": "CSV文件至少需要表头+1行数据，请确认编码与格式正确"})
|
||
|
||
matched = sum(1 for h in OUTBOUND_IMPORT_HEADERS if h in headers)
|
||
if matched < 2:
|
||
return jsonify({
|
||
"code": 400,
|
||
"msg": "CSV列名不匹配\n预期列名：%s\n实际列名：%s" % (','.join(OUTBOUND_IMPORT_HEADERS), ','.join(headers))
|
||
})
|
||
|
||
def cell(row, name):
|
||
idx = headers.index(name) if name in headers else -1
|
||
return row[idx] if 0 <= idx < len(row) else ''
|
||
|
||
records = []
|
||
errors = []
|
||
for i, row in enumerate(data_rows, start=2):
|
||
code = _import_text(cell(row, '货品编号'))
|
||
if _is_invalid_product_code(code):
|
||
continue
|
||
date_val = _normalize_import_date(cell(row, '日期'))
|
||
if not date_val:
|
||
errors.append('第%d行：日期无法识别（需 YYYY-MM-DD）' % i)
|
||
continue
|
||
source = _resolve_outbound_source(cell(row, '数据来源'))
|
||
records.append((
|
||
code, _import_text(cell(row, '货品名称')), _import_text(cell(row, '品牌')),
|
||
_import_text(cell(row, '仓库')), _import_text(cell(row, '店铺')),
|
||
_import_text(cell(row, '一级类目')), _import_text(cell(row, '二级类目')),
|
||
_import_text(cell(row, '三级类目')), _import_int(cell(row, '货品数量')),
|
||
_import_float(cell(row, '货品成本')), date_val, source,
|
||
))
|
||
|
||
if not records:
|
||
msg = '没有可导入的数据' + ('：' + '；'.join(errors[:5]) if errors else '')
|
||
return jsonify({"code": 400, "msg": msg})
|
||
|
||
from services.db import get_db
|
||
conn = get_db()
|
||
try:
|
||
with conn.cursor() as cur:
|
||
for rec in records:
|
||
cur.execute(
|
||
"DELETE FROM daily_outbound WHERE 日期=%s AND 数据来源=%s AND 货品编号=%s "
|
||
"AND 仓库=%s AND 店铺=%s",
|
||
(rec[10], rec[11], rec[0], rec[3], rec[4])
|
||
)
|
||
cur.executemany(
|
||
"INSERT INTO daily_outbound "
|
||
"(货品编号, 货品名称, 品牌, 仓库, 店铺, 一级类目, 二级类目, 三级类目, "
|
||
" 货品数量, 货品成本, 日期, 数据来源, 数据类型) "
|
||
"VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,'出库数据')",
|
||
records
|
||
)
|
||
conn.commit()
|
||
except Exception as e:
|
||
conn.rollback()
|
||
return jsonify({"code": 500, "msg": "导入失败：%s" % e})
|
||
finally:
|
||
conn.close()
|
||
|
||
try:
|
||
from services.cache import refresh_all_caches
|
||
refresh_all_caches()
|
||
except Exception:
|
||
pass
|
||
|
||
try:
|
||
from services.db import add_operation_log
|
||
add_operation_log('出库明细导入', '导入出库明细 %d 条（跳过%d条）' % (len(records), len(errors)),
|
||
page='data_detail')
|
||
except Exception:
|
||
pass
|
||
|
||
msg = '✅ 成功导入 %d 条' % len(records)
|
||
if errors:
|
||
msg += '，%d 行未导入：%s' % (len(errors), '；'.join(errors[:5]))
|
||
if len(errors) > 5:
|
||
msg += ' 等'
|
||
return jsonify({"code": 0, "msg": msg, "imported": len(records), "failed": len(errors)})
|