385 lines
16 KiB
Python
385 lines
16 KiB
Python
"""库存计算相关函数"""
|
||
from services.db import query
|
||
from services.cache import RefreshableCache, register_cache
|
||
|
||
|
||
# ⚠️ 特殊成本价规则：以下商品不使用库存表中的成本价，使用单独指定的正确成本价。
|
||
# 键为商品编码，值为正确成本价。
|
||
SPECIAL_COST_PRICES = {
|
||
'POZ-FM-0184-TM-SM': 0.055, # 库存表55 → 正确0.055
|
||
'POZ-XFS-9014-TM-MD': 0.062, # 库存表31.32 → 正确0.062
|
||
'POZ-FM-9742-TM-MD': 0.181, # 库存表28.08 → 正确0.181
|
||
'POZ-MM-0580-TM-MD': 0.22, # 库存表26 → 正确0.22
|
||
'POZ-SKS-3374-LD': 0.1, # 库存表25 → 正确0.1
|
||
'POZ-SKS-3367-LD': 0.1, # 库存表25 → 正确0.1
|
||
'POZ-SKS-3350-LD': 0.1, # 库存表25 → 正确0.1
|
||
'POZ-SKS-3343-LD': 0.1, # 库存表25 → 正确0.1
|
||
}
|
||
# 以下商品的库存总数量不统计20号仓库。
|
||
EXCLUDE_WAREHOUSE_20_PRODUCTS = {
|
||
'POZ-03033',
|
||
'POZ-DXPW-1668-PDD',
|
||
'AH-YS-0584-TMGJ',
|
||
'POZ-JHY-7022',
|
||
'SEDATE-YMT-1682-PDD',
|
||
}
|
||
# 以下无产品资料商品固定归属成品，不按20号仓库自动归为包材。
|
||
FORCE_FINISHED_PRODUCTS = {
|
||
'POZ-WPH-0137',
|
||
'（AVA-YS-3252)',
|
||
'（UO-AJS-3362)',
|
||
'(WPK-CBZT-9707)',
|
||
'【UOUONE】LHJ0385',
|
||
}
|
||
FORCE_FINISHED_PRODUCTS_SQL = ', '.join(
|
||
"'%s'" % code.replace("'", "''") for code in sorted(FORCE_FINISHED_PRODUCTS)
|
||
)
|
||
|
||
|
||
def active_warehouse_condition(inventory_alias='inventory_detail'):
|
||
"""SQL condition that excludes warehouses marked disabled in warehouse management.
|
||
|
||
Inventory rows whose warehouse is not maintained in ``warehouses`` remain eligible;
|
||
only an explicit ``disabled='是'`` removes a warehouse from calculations. Numeric
|
||
warehouse codes are compared by value so 29/029 and 35/035 behave consistently.
|
||
"""
|
||
alias = inventory_alias
|
||
return f"""NOT EXISTS (
|
||
SELECT 1 FROM warehouses disabled_wh
|
||
WHERE COALESCE(disabled_wh.disabled, '否') = '是'
|
||
AND (
|
||
disabled_wh.warehouse_code = {alias}.warehouse_code
|
||
OR (
|
||
disabled_wh.warehouse_code REGEXP '^[0-9]+$'
|
||
AND {alias}.warehouse_code REGEXP '^[0-9]+$'
|
||
AND CAST(disabled_wh.warehouse_code AS UNSIGNED) = CAST({alias}.warehouse_code AS UNSIGNED)
|
||
)
|
||
)
|
||
)"""
|
||
|
||
|
||
def resolve_cost_price(product_code, inventory_price):
|
||
"""解析产品成本价：命中特殊规则的用单独指定的正确成本价，否则用库存表成本价。"""
|
||
if product_code in SPECIAL_COST_PRICES:
|
||
return SPECIAL_COST_PRICES[product_code]
|
||
return inventory_price
|
||
|
||
|
||
def _get_latest_inv_date():
|
||
"""获取最新库存数据日期"""
|
||
latest = query("SELECT MAX(data_date) AS d FROM inventory_detail")[0]['d']
|
||
if hasattr(latest, 'strftime'):
|
||
return latest.strftime('%Y-%m-%d')
|
||
return str(latest)[:10]
|
||
|
||
|
||
def _compute_product_inventory():
|
||
"""计算每个产品的最新库存（排除15号仓库）"""
|
||
latest_str = _get_latest_inv_date()
|
||
rows = query(f"""
|
||
SELECT product_code, SUM(available_qty) AS total_qty
|
||
FROM inventory_detail
|
||
WHERE data_date = %s AND warehouse_code != '15'
|
||
AND {active_warehouse_condition('inventory_detail')}
|
||
AND NOT (product_code IN ('POZ-03033', 'POZ-DXPW-1668-PDD', 'AH-YS-0584-TMGJ', 'POZ-JHY-7022', 'SEDATE-YMT-1682-PDD')
|
||
AND LPAD(warehouse_code, 2, '0') = '20')
|
||
GROUP BY product_code
|
||
""", (latest_str,))
|
||
|
||
inv_map = {}
|
||
for r in rows:
|
||
inv_map[r['product_code']] = int(r['total_qty']) if r['total_qty'] else 0
|
||
return inv_map
|
||
|
||
|
||
def _compute_raw_inventory_15():
|
||
"""计算每个产品的最新加工厂库存（仅15号仓库）"""
|
||
latest_str = _get_latest_inv_date()
|
||
rows = query(
|
||
"SELECT product_code, SUM(available_qty) AS total_qty "
|
||
"FROM inventory_detail "
|
||
"WHERE data_date = %s AND warehouse_code = '15' "
|
||
f"AND {active_warehouse_condition('inventory_detail')} "
|
||
"GROUP BY product_code",
|
||
(latest_str,))
|
||
inv15_map = {}
|
||
for r in rows:
|
||
inv15_map[r['product_code']] = int(r['total_qty']) if r['total_qty'] else 0
|
||
return inv15_map
|
||
|
||
# 缓存版获取最新加工厂库存
|
||
inv15_cache = RefreshableCache(maxsize=1)
|
||
register_cache(inv15_cache)
|
||
|
||
|
||
@inv15_cache.wrap
|
||
def compute_product_inventory_15():
|
||
"""缓存版加工厂库存计算（支持跨 worker 主动刷新）"""
|
||
return _compute_raw_inventory_15()
|
||
|
||
|
||
def compute_product_inventory_by_property(property_keyword):
|
||
"""根据仓库属性标签计算每个产品的库存（如云仓、外仓）"""
|
||
wh_codes = query(
|
||
"SELECT warehouse_code FROM warehouses WHERE FIND_IN_SET(%s, warehouse_property) > 0 "
|
||
"AND COALESCE(disabled, '否') != '是'",
|
||
(property_keyword,)
|
||
)
|
||
# 仓库管理和库存明细的编号格式可能不同（例如管理表为035、明细为35）。
|
||
# 对纯数字仓号按数值匹配，其他编号仍按原字符串精确匹配。
|
||
numeric_codes = set()
|
||
text_codes = set()
|
||
for row in wh_codes:
|
||
code = str(row.get('warehouse_code') or '').strip()
|
||
if not code:
|
||
continue
|
||
if code.isdigit():
|
||
numeric_codes.add(int(code))
|
||
else:
|
||
text_codes.add(code)
|
||
if not numeric_codes and not text_codes:
|
||
return {}
|
||
|
||
latest_str = _get_latest_inv_date()
|
||
match_clauses = []
|
||
params = [latest_str]
|
||
if numeric_codes:
|
||
placeholders = ','.join(['%s'] * len(numeric_codes))
|
||
match_clauses.append(
|
||
"warehouse_code REGEXP '^[0-9]+$' "
|
||
f"AND CAST(warehouse_code AS UNSIGNED) IN ({placeholders})"
|
||
)
|
||
params.extend(sorted(numeric_codes))
|
||
if text_codes:
|
||
placeholders = ','.join(['%s'] * len(text_codes))
|
||
match_clauses.append(f"warehouse_code IN ({placeholders})")
|
||
params.extend(sorted(text_codes))
|
||
|
||
rows = query(f"""
|
||
SELECT product_code, SUM(available_qty) AS total_qty
|
||
FROM inventory_detail
|
||
WHERE data_date = %s AND ({' OR '.join(match_clauses)})
|
||
GROUP BY product_code
|
||
""", tuple(params))
|
||
|
||
inv_map = {}
|
||
for r in rows:
|
||
inv_map[r['product_code']] = int(r['total_qty']) if r['total_qty'] else 0
|
||
return inv_map
|
||
|
||
# 缓存版云仓库存
|
||
_cloud_inv_cache = RefreshableCache(maxsize=1)
|
||
register_cache(_cloud_inv_cache)
|
||
|
||
|
||
@_cloud_inv_cache.wrap
|
||
def compute_cloud_inventory_cached():
|
||
"""缓存版云仓库存计算（支持跨 worker 主动刷新）"""
|
||
return compute_product_inventory_by_property('云仓')
|
||
|
||
|
||
# 缓存版外仓库存
|
||
_ext_inv_cache = RefreshableCache(maxsize=1)
|
||
register_cache(_ext_inv_cache)
|
||
|
||
|
||
@_ext_inv_cache.wrap
|
||
def compute_external_inventory_cached():
|
||
"""缓存版外仓库存计算（支持跨 worker 主动刷新）"""
|
||
return compute_product_inventory_by_property('外仓')
|
||
|
||
|
||
def compute_product_in_transit():
|
||
"""计算每个产品的最新生产在途、采购在途（所有仓库合计）"""
|
||
latest_str = _get_latest_inv_date()
|
||
rows = query(
|
||
"SELECT product_code, SUM(production_in_transit) AS prod_in_transit, "
|
||
"SUM(purchase_in_transit) AS purch_in_transit "
|
||
"FROM inventory_detail "
|
||
f"WHERE data_date = %s AND {active_warehouse_condition('inventory_detail')} "
|
||
"GROUP BY product_code",
|
||
(latest_str,))
|
||
transit_map = {}
|
||
for r in rows:
|
||
transit_map[r['product_code']] = {
|
||
'生产在途': int(r['prod_in_transit']) if r['prod_in_transit'] else 0,
|
||
'采购在途': int(r['purch_in_transit']) if r['purch_in_transit'] else 0,
|
||
}
|
||
return transit_map
|
||
|
||
# 缓存版在途库存
|
||
_transit_cache = RefreshableCache(maxsize=1)
|
||
register_cache(_transit_cache)
|
||
|
||
|
||
@_transit_cache.wrap
|
||
def compute_product_in_transit_cached():
|
||
"""缓存版在途库存计算（支持跨 worker 主动刷新）"""
|
||
return compute_product_in_transit()
|
||
|
||
|
||
def _determine_trend(avg_3, avg_7, avg_14):
|
||
"""根据3/7/14天销量均值判断销售趋势（共10种）"""
|
||
a3, a7, a14 = float(avg_3), float(avg_7), float(avg_14)
|
||
|
||
# 全部为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
|
||
ratio_7_14 = a7 / base_14
|
||
diff_3_7 = abs(a3 - a7)
|
||
diff_7_14 = abs(a7 - a14)
|
||
|
||
# 应用趋势规则
|
||
if a3 > a7 and 0.95 <= ratio_7_14 <= 1.05:
|
||
return '开始上升'
|
||
if a3 < a7 and 0.95 <= ratio_7_14 <= 1.05:
|
||
return '开始下跌'
|
||
if diff_3_7 <= 20 and diff_7_14 <= 20:
|
||
return '平稳趋势'
|
||
if 0.95 <= ratio_3_7 <= 1.05 and a7 > a14:
|
||
return '上升趋稳'
|
||
if 0.95 <= ratio_3_7 <= 1.05 and a7 < a14:
|
||
return '下跌趋稳'
|
||
if a3 > a7 and a7 > a14:
|
||
return '持续上升'
|
||
if a3 < a7 and a7 > a14:
|
||
return '急跌'
|
||
if a3 < a7 and a7 < a14:
|
||
return '持续下跌'
|
||
if a3 > a7 and a7 < a14:
|
||
return '急增'
|
||
return '平稳趋势'
|
||
|
||
|
||
def _determine_trend_cached(avg_3, avg_7, avg_14):
|
||
"""趋势判断（带缓存）"""
|
||
return _determine_trend(avg_3, avg_7, avg_14)
|
||
|
||
|
||
# 主库存计算缓存
|
||
inv_cache = RefreshableCache(maxsize=1)
|
||
register_cache(inv_cache)
|
||
|
||
|
||
@inv_cache.wrap
|
||
def compute_product_inventory_cached():
|
||
"""缓存版库存计算（支持跨 worker 主动刷新）"""
|
||
return _compute_product_inventory()
|
||
|
||
|
||
def compute_inventory_cost_summary(latest_date=None):
|
||
"""按系统数据概览口径计算当前库存成本汇总（与 /api/dashboard/stats 一致）。
|
||
|
||
规则：
|
||
- 成本价：命中 SPECIAL_COST_PRICES 的商品用单独指定的正确成本价，其余用库存表成本价
|
||
- 成本价与库存数量使用同一个核对日期（latest_date，默认最新库存日期）
|
||
- 排除内料（编码含 -NL 或名称含"内料"）
|
||
- 成品/包材分类：有产品资料的按 product_type；无资料按仓库 20 号=包材、其他=成品
|
||
- 20号仓库特定商品（EXCLUDE_WAREHOUSE_20_PRODUCTS）不统计
|
||
- 生产在途按成品BOM中"内料"项的库存成本价计算；采购在途按成品成本价计算
|
||
返回 dict：total_cost / finished_cost / packaging_cost / 各分项 / latest_date
|
||
"""
|
||
if not latest_date:
|
||
latest_date = _get_latest_inv_date()
|
||
|
||
# 成本价（同一核对日期）
|
||
_latest_cost = query("""
|
||
SELECT product_code, ANY_VALUE(cost_price) AS pr
|
||
FROM inventory_detail WHERE data_date = %s GROUP BY product_code
|
||
""", (latest_date,))
|
||
_cost_map = {r['product_code']: resolve_cost_price(r['product_code'], float(r['pr']) if r['pr'] else 0) for r in _latest_cost}
|
||
|
||
# 成品/包材分类汇总
|
||
_inventory_rows = query(f"""
|
||
SELECT id.product_code,
|
||
SUM(CASE WHEN
|
||
(p.product_type IN ('成品', '清库存', '现货', '赠品') AND p.deleted_at IS NULL)
|
||
OR (p.product_code IS NULL AND (LPAD(id.warehouse_code, 2, '0') != '20'
|
||
OR id.product_code IN ('POZ-WPH-0137', '（AVA-YS-3252)', '（UO-AJS-3362)', '(WPK-CBZT-9707)', '【UOUONE】LHJ0385')))
|
||
THEN id.available_qty ELSE 0 END) AS finished_available_qty,
|
||
SUM(CASE WHEN
|
||
(p.product_type IN ('成品', '清库存', '现货', '赠品') AND p.deleted_at IS NULL)
|
||
OR (p.product_code IS NULL AND (LPAD(id.warehouse_code, 2, '0') != '20'
|
||
OR id.product_code IN ('POZ-WPH-0137', '（AVA-YS-3252)', '（UO-AJS-3362)', '(WPK-CBZT-9707)', '【UOUONE】LHJ0385')))
|
||
THEN id.production_in_transit ELSE 0 END) AS finished_production_in_transit,
|
||
SUM(CASE WHEN
|
||
(p.product_type IN ('成品', '清库存', '现货', '赠品') AND p.deleted_at IS NULL)
|
||
OR (p.product_code IS NULL AND (LPAD(id.warehouse_code, 2, '0') != '20'
|
||
OR id.product_code IN ('POZ-WPH-0137', '（AVA-YS-3252)', '（UO-AJS-3362)', '(WPK-CBZT-9707)', '【UOUONE】LHJ0385')))
|
||
THEN id.purchase_in_transit ELSE 0 END) AS finished_purchase_in_transit,
|
||
SUM(CASE WHEN
|
||
(p.product_type = '包材' AND p.product_name NOT LIKE %s AND p.deleted_at IS NULL)
|
||
OR (p.product_code IS NULL AND LPAD(id.warehouse_code, 2, '0') = '20'
|
||
AND id.product_code NOT IN ('POZ-WPH-0137', '（AVA-YS-3252)', '（UO-AJS-3362)', '(WPK-CBZT-9707)', '【UOUONE】LHJ0385'))
|
||
THEN id.available_qty ELSE 0 END) AS packaging_available_qty,
|
||
SUM(CASE WHEN
|
||
(p.product_type = '包材' AND p.product_name NOT LIKE %s AND p.deleted_at IS NULL)
|
||
OR (p.product_code IS NULL AND LPAD(id.warehouse_code, 2, '0') = '20'
|
||
AND id.product_code NOT IN ('POZ-WPH-0137', '（AVA-YS-3252)', '（UO-AJS-3362)', '(WPK-CBZT-9707)', '【UOUONE】LHJ0385'))
|
||
THEN id.purchase_in_transit ELSE 0 END) AS packaging_purchase_in_transit
|
||
FROM inventory_detail id
|
||
LEFT JOIN products p ON id.product_code = p.product_code
|
||
WHERE id.data_date = %s
|
||
AND {active_warehouse_condition('id')}
|
||
AND id.product_code NOT LIKE %s
|
||
AND id.product_name NOT LIKE %s
|
||
AND NOT (id.product_code IN ('POZ-03033', 'POZ-DXPW-1668-PDD', 'AH-YS-0584-TMGJ', 'POZ-JHY-7022', 'SEDATE-YMT-1682-PDD')
|
||
AND LPAD(id.warehouse_code, 2, '0') = '20')
|
||
GROUP BY id.product_code
|
||
""", ('%内料%', '%内料%', latest_date, '%-NL%', '%内料%'))
|
||
|
||
# BOM 内料成本价（生产在途成本用）
|
||
_inner_cost_rows = query("""
|
||
SELECT b.product_code, b.material_code,
|
||
COALESCE(mi.cost_price, m.cost_price, 0) AS inner_cost
|
||
FROM bom b
|
||
LEFT JOIN (
|
||
SELECT product_code, ANY_VALUE(cost_price) AS cost_price
|
||
FROM inventory_detail WHERE data_date = %s GROUP BY product_code
|
||
) mi ON mi.product_code = b.material_code
|
||
LEFT JOIN products m ON m.product_code = b.material_code
|
||
WHERE (b.material_name LIKE %s OR b.material_code LIKE %s)
|
||
""", (latest_date, '%内料%', '%-NL'))
|
||
_inner_cost_map = {
|
||
r['product_code']: resolve_cost_price(r['material_code'], float(r['inner_cost'] or 0)) if r['material_code'] else 0
|
||
for r in _inner_cost_rows
|
||
}
|
||
|
||
finished_inventory_cost = finished_production_transit_cost = finished_purchase_transit_cost = 0.0
|
||
packaging_inventory_cost = packaging_purchase_transit_cost = 0.0
|
||
for r in _inventory_rows:
|
||
price = _cost_map.get(r['product_code'], 0)
|
||
finished_inventory_cost += float(r['finished_available_qty'] or 0) * price
|
||
finished_production_transit_cost += float(r['finished_production_in_transit'] or 0) * _inner_cost_map.get(r['product_code'], 0)
|
||
finished_purchase_transit_cost += float(r['finished_purchase_in_transit'] or 0) * price
|
||
packaging_inventory_cost += float(r['packaging_available_qty'] or 0) * price
|
||
packaging_purchase_transit_cost += float(r['packaging_purchase_in_transit'] or 0) * price
|
||
|
||
finished_inventory_cost = round(finished_inventory_cost, 2)
|
||
finished_production_transit_cost = round(finished_production_transit_cost, 2)
|
||
finished_purchase_transit_cost = round(finished_purchase_transit_cost, 2)
|
||
packaging_inventory_cost = round(packaging_inventory_cost, 2)
|
||
packaging_purchase_transit_cost = round(packaging_purchase_transit_cost, 2)
|
||
finished_cost = round(finished_inventory_cost + finished_production_transit_cost + finished_purchase_transit_cost, 2)
|
||
packaging_cost = round(packaging_inventory_cost + packaging_purchase_transit_cost, 2)
|
||
total_cost = round(finished_cost + packaging_cost, 2)
|
||
|
||
return {
|
||
'latest_date': latest_date,
|
||
'total_cost': total_cost,
|
||
'finished_cost': finished_cost,
|
||
'packaging_cost': packaging_cost,
|
||
'finished_inventory_cost': finished_inventory_cost,
|
||
'finished_production_transit_cost': finished_production_transit_cost,
|
||
'finished_purchase_transit_cost': finished_purchase_transit_cost,
|
||
'packaging_inventory_cost': packaging_inventory_cost,
|
||
'packaging_purchase_transit_cost': packaging_purchase_transit_cost,
|
||
}
|