851 lines
26 KiB
Markdown
851 lines
26 KiB
Markdown
# 计划预测 - 计算列逻辑详解
|
||
|
||
> 最后更新：2026-06-23（第二版，精确到代码级细节）
|
||
>
|
||
> 本文档按代码执行路径逐步记录计划预测页面所有计算列的逻辑，不省略任何细节。
|
||
|
||
---
|
||
|
||
## 一、数据源说明
|
||
|
||
### 1.1 销量数据 → `daily_outbound` 表
|
||
|
||
取**最新日期**：
|
||
```python
|
||
latest = query("SELECT MAX(日期) AS d FROM daily_outbound")[0]['d']
|
||
# → latest_str = 格式化后的日期字符串
|
||
```
|
||
|
||
以最新日期为基准日，逐天向前构造日期列表：
|
||
```python
|
||
dates_3 = [latest-0d, latest-1d, latest-2d] # 3天
|
||
dates_7 = [latest-0d, latest-1d, latest-2d, latest-3d, latest-4d, latest-5d, latest-6d] # 7天
|
||
dates_14 = [latest-0d .. latest-13d] # 14天
|
||
dates_30 = [latest-0d .. latest-29d] # 30天
|
||
dates_60 = [latest-0d .. latest-59d] # 60天
|
||
```
|
||
|
||
SQL 查询原始数据：
|
||
```sql
|
||
SELECT 货品编号, 日期, SUM(货品数量) AS 日销量
|
||
FROM daily_outbound
|
||
WHERE 日期 >= dates_60[-1]
|
||
GROUP BY 货品编号, 日期
|
||
```
|
||
|
||
数据整理为：`{product_code: {date_string: qty}}`
|
||
- `qty = int(r['日销量']) if r['日销量'] else 0`
|
||
|
||
### 1.2 库存数据 → `inventory_detail` 表
|
||
|
||
取**最新库存日期**：
|
||
```python
|
||
latest = query("SELECT MAX(data_date) AS d FROM inventory_detail")[0]['d']
|
||
```
|
||
|
||
**库存**（排除 15 号仓库）：
|
||
```sql
|
||
SELECT product_code, SUM(available_qty) AS total_qty
|
||
FROM inventory_detail
|
||
WHERE data_date = '{latest}' AND warehouse_code != '15'
|
||
GROUP BY product_code
|
||
```
|
||
→ `inv_map[pc] = int(r['total_qty']) if r['total_qty'] else 0`
|
||
|
||
**加工厂库存**（仅 15 号仓库）：
|
||
```sql
|
||
SELECT product_code, SUM(available_qty) AS total_qty
|
||
FROM inventory_detail
|
||
WHERE data_date = '{latest}' AND warehouse_code = '15'
|
||
GROUP BY product_code
|
||
```
|
||
→ `inv15_map[pc] = int(r['total_qty']) if r['total_qty'] else 0`
|
||
|
||
**生产在途 + 采购在途**（所有仓库合计）：
|
||
```sql
|
||
SELECT product_code,
|
||
SUM(production_in_transit) AS prod_in_transit,
|
||
SUM(purchase_in_transit) AS purch_in_transit
|
||
FROM inventory_detail
|
||
WHERE data_date = '{latest}'
|
||
GROUP BY product_code
|
||
```
|
||
→ `transit_map[pc] = {'生产在途': int(prod_in_transit) or 0, '采购在途': int(purch_in_transit) or 0}`
|
||
|
||
### 1.3 预测配置 → `sales_predict_config` 表
|
||
|
||
```sql
|
||
SELECT * FROM sales_predict_config ORDER BY id
|
||
```
|
||
→ `sp_map = {r['trend_type']: r for r in ...}`
|
||
|
||
每条记录字段：`trend_type`, `avg_3d`, `avg_7d`, `avg_14d`, `adjust_factor`
|
||
|
||
### 1.4 全局配置 → `global_config` 表
|
||
|
||
```sql
|
||
SELECT config_key, config_value FROM global_config
|
||
```
|
||
→ `gc = {'safety_stock_days': '7', 'suggested_return_days': '15', ...}`
|
||
|
||
默认值（数据库无数据时）:
|
||
```python
|
||
gc = {
|
||
'safety_stock_days': '7',
|
||
'suggested_return_days': '15',
|
||
}
|
||
```
|
||
|
||
---
|
||
|
||
## 二、销量统计列
|
||
|
||
### 2.1 昨日销量
|
||
```python
|
||
# 取 latest_str（最新日期）当天该产品的出货量
|
||
yesterday_qty = dmap.get(latest_str, 0)
|
||
```
|
||
**类型**：整数（`int`，但最终存储时未额外转换，直接存 Python int）
|
||
|
||
### 2.2 3天平均销量
|
||
```python
|
||
qty_3 = [dmap.get(d, 0) for d in dates_3] # 列表，长度3
|
||
avg_3 = int(round(sum(qty_3) / 3))
|
||
```
|
||
**类型**：`int`，先 `round` 再 `int`
|
||
|
||
### 2.3 7天平均销量
|
||
```python
|
||
qty_7 = [dmap.get(d, 0) for d in dates_7] # 列表，长度7
|
||
avg_7 = int(round(sum(qty_7) / 7))
|
||
```
|
||
|
||
### 2.4 14天平均销量
|
||
```python
|
||
qty_14 = [dmap.get(d, 0) for d in dates_14] # 列表，长度14
|
||
avg_14 = int(round(sum(qty_14) / 14))
|
||
```
|
||
|
||
### 2.5 30天平均销量
|
||
```python
|
||
qty_30 = [dmap.get(d, 0) for d in dates_30] # 列表，长度30
|
||
avg_30 = int(round(sum(qty_30) / 30))
|
||
```
|
||
|
||
### 2.6 60天平均销量
|
||
```python
|
||
qty_60 = [dmap.get(d, 0) for d in dates_60] # 列表，长度60
|
||
avg_60 = int(round(sum(qty_60) / 60))
|
||
```
|
||
|
||
### 2.7 3天最高销量
|
||
```python
|
||
max_3 = max(qty_3)
|
||
```
|
||
|
||
### 2.8 7天最高销量
|
||
```python
|
||
max_7 = max(qty_7)
|
||
```
|
||
|
||
### 2.9 14天最高销量
|
||
```python
|
||
max_14 = max(qty_14)
|
||
```
|
||
|
||
### 2.10 30天最高销量
|
||
```python
|
||
max_30 = max(qty_30)
|
||
```
|
||
|
||
### 2.11 14天最低销量
|
||
```python
|
||
min_14 = min(qty_14)
|
||
```
|
||
|
||
### 2.12 14天标准差
|
||
```python
|
||
def _calc_stddev(qty_list):
|
||
n = len(qty_list)
|
||
if n < 2:
|
||
return 0
|
||
avg = sum(qty_list) / n
|
||
variance = sum((x - avg) ** 2 for x in qty_list) / n
|
||
return math.sqrt(variance)
|
||
|
||
std_14 = int(round(_calc_stddev(qty_14)))
|
||
```
|
||
**公式**：
|
||
```
|
||
avg = sum(qty_14) / 14
|
||
variance = sum((x - avg)² for x in qty_14) / 14 # 总体标准差（分母n，而非n-1）
|
||
std = sqrt(variance)
|
||
```
|
||
**类型**：输出为 `int(round(...))`
|
||
|
||
### 2.13 最低销量偏差率
|
||
```python
|
||
base = avg_3 if avg_3 != 0 else 1
|
||
最低销量偏差率 = round((min_14 - avg_3) / base, 4)
|
||
```
|
||
**公式**：`(14天最低销量 - 3天平均销量) / max(3天平均销量, 1)`，保留4位小数
|
||
- 注意：`base` 用的是 `avg_3`（int），如果 `avg_3 == 0` 则 `base = 1`
|
||
- 不是用 `max(avg_3, 1)` 而是条件赋值
|
||
- 由于此时 `avg_3` 是 int，`min_14` 也是 int，除法结果可能是负数
|
||
|
||
### 2.14 最高销量偏差率
|
||
```python
|
||
最高销量偏差率 = round((max_14 - avg_3) / base, 4)
|
||
```
|
||
**公式**：`(14天最高销量 - 3天平均销量) / max(3天平均销量, 1)`，保留4位小数
|
||
|
||
### 2.15 极值判断
|
||
```python
|
||
极小值 = 0.7
|
||
极大值 = 1
|
||
|
||
if 最低销量偏差率 <= 极小值 and 最高销量偏差率 >= 极大值:
|
||
极值判断 = '极大/极小'
|
||
elif 最低销量偏差率 < 极小值:
|
||
极值判断 = '极小'
|
||
elif 最高销量偏差率 > 极大值:
|
||
极值判断 = '极大'
|
||
else:
|
||
极值判断 = '正常'
|
||
```
|
||
**判定细节**：
|
||
- `<= 0.7`（含等于）算极小；`< 0.7`（不含等于）才触发"极小"
|
||
- `>= 1.0`（含等于）算极大；`> 1.0`（不含等于）才触发"极大"
|
||
- 同时满足两个条件 → `极大/极小`
|
||
- 优先级：`极大/极小` > `极小` > `极大` > `正常`
|
||
|
||
### 2.16 14天波动率
|
||
```python
|
||
avg_base = avg_14 if avg_14 != 0 else 1
|
||
波动率 = round(std_14 / avg_base, 4)
|
||
```
|
||
**公式**：`14天标准差 / max(14天平均销量, 1)`，保留4位小数
|
||
|
||
### 2.17 波动风险
|
||
```python
|
||
if 波动率 <= 0.5:
|
||
波动风险 = 'X'
|
||
elif 波动率 <= 1:
|
||
波动风险 = 'Y'
|
||
else:
|
||
波动风险 = 'Z'
|
||
```
|
||
**分级**：
|
||
| 条件 | 结果 |
|
||
|------|------|
|
||
| 波动率 ≤ 0.5 | `X` |
|
||
| 0.5 < 波动率 ≤ 1.0 | `Y` |
|
||
| 波动率 > 1.0 | `Z` |
|
||
|
||
---
|
||
|
||
## 三、销售趋势（10 种）
|
||
|
||
### 函数签名
|
||
```python
|
||
_determine_trend(avg_3, avg_7, avg_14)
|
||
```
|
||
|
||
### 调用方式
|
||
```python
|
||
# 有销量数据时
|
||
row['销售趋势'] = _determine_trend(s['3天平均销量'], s['7天平均销量'], s['14天平均销量'])
|
||
|
||
# 无销量数据时
|
||
row['销售趋势'] = _determine_trend(0, 0, 0)
|
||
```
|
||
|
||
### 完整逻辑（按代码执行顺序判定）
|
||
```python
|
||
def _determine_trend(avg_3, avg_7, avg_14):
|
||
a3, a7, a14 = float(avg_3), float(avg_7), float(avg_14)
|
||
|
||
# 规则1：全部为0
|
||
if a3 == 0 and a7 == 0 and a14 == 0:
|
||
return '近期无销售'
|
||
|
||
# 防除零
|
||
base_7 = a7 if a7 != 0 else 1
|
||
base_14 = a14 if a14 != 0 else 1
|
||
|
||
# 计算比值和差值
|
||
ratio_3_7 = a3 / base_7 # 3天/7天 比值
|
||
ratio_7_14 = a7 / base_14 # 7天/14天 比值
|
||
diff_3_7 = abs(a3 - a7) # 3天与7天的绝对差值
|
||
diff_7_14 = abs(a7 - a14) # 7天与14天的绝对差值
|
||
|
||
# 规则2：开始上升
|
||
if a3 > a7 and 0.95 <= ratio_7_14 <= 1.05:
|
||
return '开始上升'
|
||
|
||
# 规则3：开始下跌
|
||
if a3 < a7 and 0.95 <= ratio_7_14 <= 1.05:
|
||
return '开始下跌'
|
||
|
||
# 规则4：平稳趋势（差值均 ≤ 20）
|
||
if diff_3_7 <= 20 and diff_7_14 <= 20:
|
||
return '平稳趋势'
|
||
|
||
# 规则5：上升趋稳（3天≈7天 且 7天>14天）
|
||
if 0.95 <= ratio_3_7 <= 1.05 and a7 > a14:
|
||
return '上升趋稳'
|
||
|
||
# 规则6：下跌趋稳（3天≈7天 且 7天<14天）
|
||
if 0.95 <= ratio_3_7 <= 1.05 and a7 < a14:
|
||
return '下跌趋稳'
|
||
|
||
# 规则7：持续上升（3天>7天>14天）
|
||
if a3 > a7 and a7 > a14:
|
||
return '持续上升'
|
||
|
||
# 规则8：急跌（3天<7天 且 7天>14天）
|
||
if a3 < a7 and a7 > a14:
|
||
return '急跌'
|
||
|
||
# 规则9：持续下跌（3天<7天<14天）
|
||
if a3 < a7 and a7 < a14:
|
||
return '持续下跌'
|
||
|
||
# 规则10：急增（3天>7天 且 7天<14天）
|
||
if a3 > a7 and a7 < a14:
|
||
return '急增'
|
||
|
||
# 兜底
|
||
return '平稳趋势'
|
||
```
|
||
|
||
**注意**：
|
||
- 所有输入先用 `float()` 转换
|
||
- `0.95 <= ratio <= 1.05` 表示波动在 ±5% 以内视为"≈"
|
||
- `diff <= 20` 表示绝对差值 ≤ 20 视为"平稳"
|
||
- 规则有先后顺序：前三条（近期无销售/开始上升/开始下跌）优先匹配，然后才是平稳，最后是各趋势
|
||
|
||
---
|
||
|
||
## 四、产品分级
|
||
|
||
### 函数签名
|
||
```python
|
||
get_product_grade(monthly_sales, product_name='', product_type='')
|
||
```
|
||
|
||
### 调用方式
|
||
```python
|
||
row['产品分级'] = get_product_grade(
|
||
row.get('30天平均销量', 0) * 30, # 月销量 = 30天平均销量 × 30
|
||
row.get('product_name', ''),
|
||
row.get('产品种类', row.get('product_type', ''))
|
||
)
|
||
```
|
||
|
||
### 完整逻辑
|
||
```python
|
||
def get_product_grade(monthly_sales, product_name='', product_type=''):
|
||
# 前置过滤1：赠品或含"晒图卡" → 不参与评级
|
||
if product_type == '赠品' or '晒图卡' in (product_name or ''):
|
||
return '不参与评级'
|
||
|
||
# 前置过滤2：小样 → 最高C级
|
||
if product_type == '小样':
|
||
return 'C级'
|
||
|
||
# 类型转换
|
||
try:
|
||
ms = float(monthly_sales)
|
||
except:
|
||
return 'C级'
|
||
|
||
# 销量分级
|
||
if ms >= 10000:
|
||
return 'S级'
|
||
elif ms >= 5000:
|
||
return 'A级'
|
||
elif ms >= 3000:
|
||
return 'B级'
|
||
else:
|
||
return 'C级'
|
||
```
|
||
|
||
**注意**：
|
||
- `monthly_sales` 已在外层乘以 30：`row.get('30天平均销量', 0) * 30`，所以 `ms` 是月销量估算值
|
||
- `product_type` 是数据库字段（不是"产品种类"），但外层调用传了 `row.get('产品种类', row.get('product_type', ''))`，优先用"产品种类"，兜底用数据库字段
|
||
|
||
---
|
||
|
||
## 五、是否新品
|
||
|
||
### 函数签名
|
||
```python
|
||
is_new_product(created_at_str)
|
||
```
|
||
|
||
### 调用方式
|
||
```python
|
||
row['是否新品'] = is_new_product(row.get('创建时间', ''))
|
||
```
|
||
|
||
### 完整逻辑
|
||
```python
|
||
def is_new_product(created_at_str):
|
||
if not created_at_str:
|
||
return '返单品'
|
||
|
||
try:
|
||
if isinstance(created_at_str, str):
|
||
created = datetime.strptime(created_at_str[:10], '%Y-%m-%d')
|
||
else:
|
||
created = created_at_str
|
||
|
||
if hasattr(created, 'strftime'):
|
||
five_months_ago = datetime.now() - relativedelta(months=5)
|
||
return '新品' if created >= five_months_ago else '返单品'
|
||
return '返单品'
|
||
except:
|
||
return '返单品'
|
||
```
|
||
|
||
**逻辑**：创建时间 ≥ 当前时间往前推5个月 → `新品`，否则 → `返单品`
|
||
- 无创建时间或解析失败都返回 `返单品`
|
||
|
||
---
|
||
|
||
## 六、产品种类
|
||
|
||
### 函数签名
|
||
```python
|
||
get_product_type_cached(name, brand, db_product_type=None)
|
||
```
|
||
|
||
### 调用方式
|
||
```python
|
||
row['产品种类'] = get_product_type_cached(
|
||
row.get('product_name', ''),
|
||
row.get('brand', ''),
|
||
row.get('product_type')
|
||
)
|
||
```
|
||
|
||
### 完整逻辑
|
||
```python
|
||
PACKAGE_KEYWORDS = {
|
||
'瓶子', '泵头', '软管', '标签', '标贴', '盖子', '彩盒', '收缩膜',
|
||
'阀门', '瓶标', '喷枪', '海绵头', '内料', '膜袋', '唇膏管',
|
||
'面膜布', '滴管', '说明书', '卡托', '中盒', '展示盒', '底托', '次抛管',
|
||
'旋盖', '瓶体', '礼袋', '管子', '条码', '瓶盖', '丝带', '按摩棒',
|
||
'刮板', '刮痧板', '拉菲草', '膜纸', '硅胶刷', '刮勺', '加工', '勺子',
|
||
'瓶身', '喷雾头', '瓶贴', '上药器', '内袋', '套膜', '胶头', '塑封膜',
|
||
'翻盖', '贴纸', '珍珠棉', '洗眼杯', '棉棒',
|
||
}
|
||
|
||
def _get_product_type(name, brand, db_product_type=None):
|
||
if not name:
|
||
return '其他'
|
||
|
||
# 1. 数据库标记为赠品
|
||
if db_product_type and db_product_type == '赠品':
|
||
return '赠品'
|
||
|
||
# 2. 名称含包材关键词
|
||
for kw in PACKAGE_KEYWORDS:
|
||
if kw in name:
|
||
return '包材'
|
||
|
||
# 3. 名称含"小样"
|
||
if '小样' in name:
|
||
return '小样'
|
||
|
||
# 4. 有品牌且不为空/无
|
||
if brand and brand.strip() and brand.strip() != '无':
|
||
return '成品'
|
||
|
||
# 5. 名称含"赠品"
|
||
if '赠品' in name:
|
||
return '赠品'
|
||
|
||
# 6. 数据库标记为成品
|
||
if db_product_type and db_product_type in ('成品',):
|
||
return db_product_type
|
||
|
||
# 7. 兜底
|
||
return '其他'
|
||
```
|
||
|
||
**判定优先级**：赠品(数据库) > 包材(关键词) > 小样(关键词) > 成品(有品牌) > 赠品(关键词) > 成品(数据库兜底) > 其他
|
||
|
||
---
|
||
|
||
## 七、日销量预测
|
||
|
||
### 函数签名
|
||
```python
|
||
compute_daily_forecast(row, sales_predict_map)
|
||
```
|
||
|
||
### 调用方式
|
||
```python
|
||
row['日销量预测'] = compute_daily_forecast(row, sp_map)
|
||
# sp_map = {r['trend_type']: r for r in sales_predict_config_cached()}
|
||
```
|
||
|
||
### 完整逻辑
|
||
```python
|
||
def compute_daily_forecast(row, sales_predict_map):
|
||
avg3 = float(row.get('3天平均销量', 0) or 0)
|
||
avg7 = float(row.get('7天平均销量', 0) or 0)
|
||
avg14 = float(row.get('14天平均销量', 0) or 0)
|
||
max30 = float(row.get('30天最高销量', 0) or 0)
|
||
trend = row.get('销售趋势', '平稳趋势')
|
||
|
||
# 如果全部为0 → 无法预测
|
||
if avg3 == 0 and avg7 == 0 and avg14 == 0 and max30 == 0:
|
||
return 0
|
||
|
||
# 根据趋势类型获取预测系数
|
||
cfg = sales_predict_map.get(trend, {})
|
||
w3 = float(cfg.get('avg_3d', 0.5)) # 3天权重，默认0.5
|
||
w7 = float(cfg.get('avg_7d', 0.3)) # 7天权重，默认0.3
|
||
w14 = float(cfg.get('avg_14d', 0.2)) # 14天权重，默认0.2
|
||
adj = float(cfg.get('adjust_factor', 1.0)) # 调节系数，默认1.0
|
||
|
||
# 加权计算
|
||
forecast = (avg3 * w3 + avg7 * w7 + avg14 * w14) * adj
|
||
return round(forecast)
|
||
```
|
||
|
||
**注意**：
|
||
- 所有取值用了 `row.get(..., 0) or 0` —— 如果值为 `None` 或 `0` 或 `''`，都统一变 `0`
|
||
- 查找配置时用 `trend`（即"销售趋势"的10种值之一）匹配 `sales_predict_config.trend_type`
|
||
- 找不到趋势配置时使用默认值（`w3=0.5, w7=0.3, w14=0.2, adj=1.0`）
|
||
- 最后结果 `round(forecast)` 取整
|
||
|
||
---
|
||
|
||
## 八、支撑天数列（5 列）
|
||
|
||
### 全部在 `_calc_support_days(row, gc)` 中计算
|
||
|
||
### 前置处理
|
||
```python
|
||
safety_days = int(gc.get('safety_stock_days', '7'))
|
||
return_days = int(gc.get('suggested_return_days', '15'))
|
||
forecast_val = row.get('日销量预测', 0)
|
||
fcast = float(forecast_val) if forecast_val not in ('近期无销售', 0, '0') else 0
|
||
|
||
inv_val = float(row.get('库存', 0) or 0)
|
||
inv15_val = float(row.get('加工厂库存', 0) or 0)
|
||
prod_transit = float(row.get('生产在途', 0) or 0)
|
||
purch_transit = float(row.get('采购在途', 0) or 0)
|
||
```
|
||
|
||
**注意 `fcast` 的取值逻辑**：
|
||
```python
|
||
fcast = float(forecast_val) if forecast_val not in ('近期无销售', 0, '0') else 0
|
||
```
|
||
- 如果 `日销量预测` 的值是字符串 `'近期无销售'`（虽然在预测函数中不会出现，但代码做了兼容），则视为 0
|
||
- 如果值是 `0` 或 `'0'`，也视为 0
|
||
|
||
### 8.1 库存可支撑天数
|
||
```python
|
||
if fcast == 0:
|
||
row['库存可支撑天数'] = 999
|
||
else:
|
||
row['库存可支撑天数'] = round(inv_val / fcast, 1) if fcast > 0 else 999
|
||
```
|
||
**公式**：`库存 / 日销量预测`，保留 1 位小数
|
||
**无销量时**：999
|
||
|
||
### 8.2 库存工厂可支撑天数（前端显示为"现货采购在途"）
|
||
```python
|
||
if fcast == 0:
|
||
row['库存工厂可支撑天数'] = 999
|
||
else:
|
||
row['库存工厂可支撑天数'] = round(purch_transit / fcast, 1) if fcast > 0 else 999
|
||
```
|
||
**公式**：`采购在途 / 日销量预测`，保留 1 位小数
|
||
|
||
### 8.3 库存工厂待生产可支撑天数（前端显示为"定制品在途"）
|
||
```python
|
||
if fcast == 0:
|
||
row['库存工厂待生产可支撑天数'] = 999
|
||
else:
|
||
row['库存工厂待生产可支撑天数'] = round(prod_transit / fcast, 1) if fcast > 0 else 999
|
||
```
|
||
**公式**：`生产在途 / 日销量预测`，保留 1 位小数
|
||
|
||
### 8.4 加工厂库存支撑天数
|
||
```python
|
||
if fcast == 0:
|
||
row['加工厂库存支撑天数'] = 999
|
||
else:
|
||
row['加工厂库存支撑天数'] = round(inv15_val / fcast, 1) if fcast > 0 else 999
|
||
```
|
||
**公式**：`加工厂库存 / 日销量预测`，保留 1 位小数
|
||
|
||
### 8.5 总支撑天数
|
||
```python
|
||
if fcast == 0:
|
||
row['总支撑天数'] = 999
|
||
else:
|
||
total_inv = prod_transit + inv15_val
|
||
row['总支撑天数'] = round(total_inv / fcast, 1) if fcast > 0 else 999
|
||
```
|
||
**公式**：`(生产在途 + 加工厂库存) / 日销量预测`，保留 1 位小数
|
||
|
||
---
|
||
|
||
## 九、库存健康指数
|
||
|
||
```python
|
||
sup_days = row.get('库存可支撑天数', 999)
|
||
s1 = sup_days if isinstance(sup_days, (int, float)) and sup_days != 999 else None
|
||
lead_h = float(row.get('生产货期', 0) or 0)
|
||
logis_h = float(row.get('物流时间', 0) or 0)
|
||
|
||
if s1 is not None and (lead_h + logis_h) > 0:
|
||
health = s1 / (lead_h + logis_h)
|
||
row['库存健康指数'] = round(min(health, 999), 1)
|
||
else:
|
||
row['库存健康指数'] = 999
|
||
```
|
||
|
||
**逻辑细节**：
|
||
- `s1` 只有当 `库存可支撑天数` 是数字类型且不等于 999 时才有效
|
||
- 即：有销量（支撑天数不为 999）时才计算健康指数
|
||
- 公式：`库存可支撑天数 / (生产货期 + 物流时间)`
|
||
- 上限 999：`min(health, 999)`
|
||
- 保留 1 位小数：`round(..., 1)`
|
||
- 分母为 0 时（货期+物流时间为 0）→ 返回 999
|
||
|
||
---
|
||
|
||
## 十、风险把控
|
||
|
||
```python
|
||
created = row.get('创建时间', '')
|
||
sup_days_val = row.get('库存可支撑天数', 999)
|
||
|
||
try:
|
||
sup_days_val = float(sup_days_val)
|
||
except:
|
||
sup_days_val = 999
|
||
|
||
days_since_created = 999
|
||
try:
|
||
if created:
|
||
if isinstance(created, str):
|
||
created_dt = datetime.strptime(created[:10], '%Y-%m-%d')
|
||
else:
|
||
created_dt = created
|
||
days_since_created = (datetime.now() - created_dt).days
|
||
except:
|
||
pass
|
||
|
||
if days_since_created < 90:
|
||
row['风险把控'] = '新品'
|
||
elif days_since_created > 180 and sup_days_val > 240:
|
||
row['风险把控'] = '需启动清库存'
|
||
elif days_since_created > 90 and sup_days_val > 90:
|
||
row['风险把控'] = '呆滞品'
|
||
else:
|
||
row['风险把控'] = '正常'
|
||
```
|
||
|
||
**判定表**（按代码执行顺序）：
|
||
|
||
| 条件 | 结果 | 说明 |
|
||
|------|------|------|
|
||
| 创建天数 < 90 | `新品` | 优先匹配 |
|
||
| 创建天数 > 180 **且** 库存可支撑天数 > 240 | `需启动清库存` | 超半年库存过多 |
|
||
| 创建天数 > 90 **且** 库存可支撑天数 > 90 | `呆滞品` | 超过3个月且库存能撑3个月 |
|
||
| 其他 | `正常` | |
|
||
|
||
**注意**：
|
||
- `days_since_created` 默认 999，如果创建时间解析失败将保持 999
|
||
- 第一个条件 `< 90` 优先于后面的条件（新品的判断级别最高）
|
||
- `days_since_created > 180` 是严格大于 180（不含等于）
|
||
- `sup_days_val > 240` 和 `> 90` 都是严格大于（不含等于）
|
||
|
||
---
|
||
|
||
## 十一、建议下单数量 & 实际下单数量
|
||
|
||
### 11.1 建议下单数量
|
||
```python
|
||
if fcast == 0 and inv_val > 0:
|
||
row['建议下单数量'] = 0
|
||
else:
|
||
row['建议下单数量'] = int(fcast * (safety_days + lead_h + logis_h))
|
||
```
|
||
**公式**：`int(日销量预测 × (安全库存天数 + 生产货期 + 物流时间))`
|
||
|
||
**边界条件**：
|
||
- 如果日销量预测为 0 但库存 > 0 → 结果为 0（有库存但无销量，不下单）
|
||
- 如果日销量预测为 0 且库存也为 0 → 进入 `else` 分支，结果 = `int(0 * ...)` = 0
|
||
- 如果日销量预测 > 0 → 正常计算并用 `int()` 截断小数
|
||
|
||
### 11.2 实际下单数量
|
||
```python
|
||
if row['建议下单数量'] == 0:
|
||
row['实际下单数量'] = 0
|
||
else:
|
||
row['实际下单数量'] = ((row['建议下单数量'] + 999) // 1000) * 1000
|
||
```
|
||
**公式**：`((建议下单数量 + 999) // 1000) * 1000`
|
||
|
||
**效果**：**按 1000 向上取整**
|
||
- 1 → 1000
|
||
- 999 → 1000
|
||
- 1000 → 1000（1000+999=1999//1000=1 → 1×1000=1000）
|
||
- 1001 → 2000
|
||
- 1234 → 2000
|
||
|
||
### 11.3 正式下单数量
|
||
- **非自动计算列**
|
||
- 用户手动编辑输入
|
||
- 编辑交互：回车+确认 → 保存到后端 | 取消/Esc/失焦 → 恢复编辑前原值
|
||
|
||
---
|
||
|
||
## 十二、建议回货数量
|
||
|
||
```python
|
||
total_transit = prod_transit + purch_transit
|
||
|
||
if fcast > 0 and total_transit > 0:
|
||
calc_val = fcast * return_days - inv_val
|
||
round_up = int(calc_val * 10 + 0.99999) / 10
|
||
if round_up < 0:
|
||
round_up = 0
|
||
row['建议回货数量'] = round_up if round_up <= total_transit else total_transit
|
||
if row['建议回货数量'] < 0:
|
||
row['建议回货数量'] = 0
|
||
else:
|
||
row['建议回货数量'] = 0
|
||
```
|
||
|
||
**公式**：
|
||
```
|
||
calc_val = 日销量预测 × 建议回货天数 - 库存
|
||
round_up = ceil_1(calc_val) # 向上取整到1位小数（通过 int(x * 10 + 0.99999) / 10 实现）
|
||
最终值 = min(round_up, 总在途)
|
||
最终值 = max(最终值, 0)
|
||
```
|
||
|
||
**注意**：
|
||
- `return_days` 来自全局配置 `suggested_return_days`（默认 15 天）
|
||
- 向上取整方式：`int(calc_val * 10 + 0.99999) / 10`，等价于 `math.ceil(calc_val * 10) / 10`
|
||
- 上限为 `total_transit`（生产在途 + 采购在途的总和）
|
||
- 下限为 0
|
||
- 仅当 `fcast > 0 and total_transit > 0` 时才计算，否则返回 0
|
||
|
||
---
|
||
|
||
## 十三、本地数据库字段
|
||
|
||
以下列直接从 `products` 表（或关联 `suppliers` 表）读取，不经计算：
|
||
|
||
```sql
|
||
SELECT
|
||
p.product_code,
|
||
p.product_name,
|
||
p.brand,
|
||
p.product_type,
|
||
p.specification,
|
||
p.cost_price,
|
||
p.lead_time, -- 对应"生产货期"
|
||
p.logistics_time, -- 对应"物流时间"
|
||
p.min_order_qty, -- 对应"最低起订量"
|
||
p.created_at, -- 对应"创建时间"
|
||
COALESCE(s.supplier_name, p.supplier_name_match) AS "供应商名称",
|
||
p.procurement_follower, -- 对应"采购跟单"
|
||
p.procurement_planner, -- 对应"采购计划"
|
||
p.operator, -- 对应"运营专员"
|
||
p.developer, -- 对应"开发专员"
|
||
p.platform, -- 对应"归属平台"
|
||
p.category1, -- 对应"一级类目"
|
||
p.category2, -- 对应"二级类目"
|
||
p.category3, -- 对应"三级类目"
|
||
p.bom -- 对应"物料清单"
|
||
FROM products p
|
||
LEFT JOIN suppliers s ON p.supplier_code = s.supplier_code
|
||
```
|
||
|
||
---
|
||
|
||
## 十四、注入流程（代码执行路径）
|
||
|
||
数据从 SQL 取出后，按以下顺序注入每一行：
|
||
|
||
```python
|
||
_enrich_row_with_planning_data(row, sales, inv_map, inv15_map, transit_map, sp_map, gc):
|
||
① row['库存'] = inv_map.get(pc, 0)
|
||
② row['加工厂库存'] = inv15_map.get(pc, 0)
|
||
t = transit_map.get(pc, {})
|
||
③ row['生产在途'] = t.get('生产在途', 0)
|
||
④ row['采购在途'] = t.get('采购在途', 0)
|
||
|
||
⑤ 销量数据注入（如果 pc in sales）:
|
||
- 昨日销量, 3/7/14/30/60天平均销量
|
||
- 3/7/14/30天最高销量, 14天最低销量
|
||
- 14天标准差, 最低/最高销量偏差率
|
||
- 极值判断, 14天波动率, 波动风险
|
||
否则全部赋 0
|
||
|
||
⑥ row['销售趋势'] = _determine_trend(avg3, avg7, avg14)
|
||
|
||
⑦ row['产品分级'] = get_product_grade(30天平均*30, product_name, 产品种类/产品类型)
|
||
|
||
⑧ row['是否新品'] = is_new_product(创建时间)
|
||
|
||
⑨ row['日销量预测'] = compute_daily_forecast(row, sp_map)
|
||
|
||
⑩ _calc_support_days(row, gc):
|
||
- 库存可支撑天数 / 库存工厂可支撑天数 / 库存工厂待生产可支撑天数
|
||
- 加工厂库存支撑天数 / 总支撑天数
|
||
- 库存健康指数
|
||
- 风险把控
|
||
- 建议下单数量 / 实际下单数量
|
||
- 建议回货数量
|
||
```
|
||
|
||
---
|
||
|
||
## 附录：依赖关系图
|
||
|
||
```
|
||
[daily_outbound 表] [inventory_detail 表]
|
||
│ │
|
||
├─ 昨日销量 ├─ 库存（≠15号仓）
|
||
├─ 3/7/14/30/60天平均 ├─ 加工厂库存（15号仓）
|
||
├─ 3/7/14/30天最高 ├─ 生产在途
|
||
├─ 14天最低 └─ 采购在途
|
||
├─ 14天标准差 ──→ 14天波动率 ──→ 波动风险 │
|
||
├─ 最低/最高偏差率 ──→ 极值判断 ▼
|
||
└─ [平均值] ──→ 销售趋势 [products 表]
|
||
│ ├─ 生产货期/物流时间
|
||
▼ ├─ 创建时间 ──→ 是否新品
|
||
[sales_predict_config] │ └─→ 风险把控
|
||
├─ avg_3d/w7/w14 └─ 品牌/产品类型 ──→ 产品分级
|
||
└─ adjust_factor │
|
||
│ ▼
|
||
▼ [产品种类判定]
|
||
[日销量预测] (关键词/品牌规则)
|
||
│
|
||
┌────────────────────┼────────────────────┐
|
||
▼ ▼ ▼
|
||
[支撑天数×5] [库存健康指数] [全局配置]
|
||
├─ 库存可支撑天数 = 支撑天数 ├─ safety_stock_days
|
||
├─ 库存工厂可支撑 ÷ (货期+物流) ├─ suggested_return_days
|
||
├─ 库存工厂待生产可支撑 │
|
||
├─ 加工厂库存支撑 ▼
|
||
└─ 总支撑天数 [下单数量]
|
||
├─ 建议下单数量
|
||
├─ 实际下单数量（千位取整）
|
||
└─ 建议回货数量
|
||
```
|