<!-- ① 分类递归展开（MySQL 8+） -->
<select id="listCategoryIdsRecursive" resultType="java.lang.Long">
WITH RECURSIVE cte AS (
SELECT id, parent_id
FROM pms_goods_category
WHERE id = #{rootId}
UNION ALL
SELECT c.id, c.parent_id
FROM pms_goods_category c
INNER JOIN cte ON c.parent_id = cte.id
)
SELECT id FROM cte
</select>
<!-- ② 销售选品（价格组价 + 库存；hasStock + 分类集合） -->
<select id="salePage" resultType="com.loveinway.modules.pms.pmsgoods.dto.resp.RespSaleGoodsSelectDTO">
SELECT
g.id AS goodsId,
sku.id AS productSkuId,
u.id AS unitId,
g.goods_name AS goodsName,
sku.sku_name AS skuName,
u.unit_name AS unitName,
COALESCE(cgp.unit_price, sku_unit.unit_price) AS salePrice,
inv.inv_num AS stockNum
FROM pms_goods g
JOIN pms_goods_sku sku
ON sku.goods_id = g.id
AND sku.del_flag = 0
JOIN pms_goods_unit_relation ur
ON ur.goods_id = g.id
AND ur.orderable = 1
AND ur.del_flag = 0
JOIN pms_goods_unit u
ON u.id = ur.unit_id
LEFT JOIN pms_goods_sku_unit sku_unit
ON sku_unit.product_sku_id = sku.id
AND sku_unit.unit_id = u.id
AND sku_unit.del_flag = 0
LEFT JOIN cus_customer_grade_price cgp
ON cgp.product_sku_id = sku.id
AND cgp.unit_id = u.id
AND cgp.customer_grade_id = #{q.customerGradeId}
AND cgp.del_flag = 0
AND cgp.enabled = 1
LEFT JOIN wms_goods_inventory inv
ON inv.product_sku_id = sku.id
AND inv.unit_id = u.id
AND inv.workhouse_id = #{q.workhouseId}
AND inv.del_flag = 0
WHERE g.del_flag = 0
AND g.enabled = 1
AND g.shelves = 1
<if test="q.goodsName != null and q.goodsName != ''">
AND g.goods_name LIKE CONCAT('%', #{q.goodsName}, '%')
</if>
<if test="q.categoryIds != null and q.categoryIds.size() > 0">
AND g.category_id IN
<foreach collection="q.categoryIds" item="cid" open="(" close=")" separator=",">
#{cid}
</foreach>
</if>
<if test="q.hasStock == true">
AND inv.inv_num > 0
</if>
</select>
<!-- ③ 采购选品（报价单时间严格 + 供应商优先级；fallback 最近采购价/成本） -->
<select id="purchasePage" resultType="com.loveinway.modules.pms.pmsgoods.dto.resp.RespPurchaseGoodsSelectDTO">
SELECT
t.goodsId,
t.productSkuId,
t.unitId,
t.goodsName,
t.skuName,
t.unitName,
COALESCE(t.quotePrice, t.lastPurchasePrice, 0) AS purchasePrice,
t.costPrice
FROM (
SELECT
g.id AS goodsId,
sku.id AS productSkuId,
u.id AS unitId,
g.goods_name AS goodsName,
sku.sku_name AS skuName,
u.unit_name AS unitName,
-- 报价单价格（已按优先级挑了“最优一条”）
qp_pick.unit_price AS quotePrice,
-- 最近采购价 / 当前成本价（仓库维度）
cost.last_purchase_price AS lastPurchasePrice,
cost.cost_price AS costPrice
FROM pms_goods g
JOIN pms_goods_sku sku
ON sku.goods_id = g.id
AND sku.del_flag = 0
JOIN pms_goods_unit_relation ur
ON ur.goods_id = g.id
AND ur.purchaseable = 1
AND ur.del_flag = 0
JOIN pms_goods_unit u
ON u.id = ur.unit_id
-- 选“最优报价单明细”：时间严格 + 供应商优先
LEFT JOIN (
SELECT x.product_sku_id, x.unit_id, x.unit_price
FROM (
SELECT
qpi.product_sku_id,
qpi.unit_id,
qpi.unit_price,
ROW_NUMBER() OVER (
PARTITION BY qpi.product_sku_id, qpi.unit_id
ORDER BY
-- ① 供应商优先（如果传入 supplierId）
CASE
WHEN #{q.supplierId} IS NOT NULL AND qp.trade_id = #{q.supplierId} THEN 0
ELSE 1
END,
-- ② 时间越近越优（start_date / quoted_date）
qp.start_date DESC,
qp.quoted_date DESC,
qp.id DESC
) AS rn
FROM pms_quoted_price_info qpi
JOIN pms_quoted_price qp
ON qp.quoted_code = qpi.quoted_code
AND qp.bill_state = 1
AND qp.del_flag = 0
AND qp.enabled = 1
AND qp.trade_type_id = 0 -- 0=供应商
WHERE qpi.del_flag = 0
AND qpi.enabled = 1
-- 时间严格：purchaseDate 必须在 [start_date, end_date] 内
<if test="q.purchaseDate != null">
AND qp.start_date <= #{q.purchaseDate}
AND (qp.end_date IS NULL OR qp.end_date = 0 OR qp.end_date >= #{q.purchaseDate})
</if>
-- 如果传 supplierId：依然允许其他供应商作为 fallback，但会排在后面
<!-- 不在 WHERE 限死 supplierId；让 ORDER BY 做优先级 -->
) x
WHERE x.rn = 1
) qp_pick
ON qp_pick.product_sku_id = sku.id
AND qp_pick.unit_id = u.id
LEFT JOIN wms_goods_cost cost
ON cost.product_sku_id = sku.id
AND cost.unit_id = u.id
AND cost.workhouse_id = #{q.workhouseId}
AND cost.del_flag = 0
WHERE g.del_flag = 0
AND g.enabled = 1
AND g.shelves = 1
<if test="q.goodsName != null and q.goodsName != ''">
AND g.goods_name LIKE CONCAT('%', #{q.goodsName}, '%')
</if>
<if test="q.categoryIds != null and q.categoryIds.size() > 0">
AND g.category_id IN
<foreach collection="q.categoryIds" item="cid" open="(" close=")" separator=",">
#{cid}
</foreach>
</if>
) t
<if test="q.hasStock == true">
-- 采购页若要“只看有库存”：按仓库库存过滤（可选能力）
WHERE EXISTS (
SELECT 1
FROM wms_goods_inventory inv
WHERE inv.workhouse_id = #{q.workhouseId}
AND inv.product_sku_id = t.productSkuId
AND inv.unit_id = t.unitId
AND inv.del_flag = 0
AND inv.inv_num > 0
)
</if>
</select>
<!-- ④ 库房选品（库存 + 成本；hasStock + 分类集合） -->
<select id="warehousePage" resultType="com.loveinway.modules.pms.pmsgoods.dto.resp.RespWarehouseGoodsSelectDTO">
SELECT
g.id AS goodsId,
sku.id AS productSkuId,
u.id AS unitId,
g.goods_name AS goodsName,
sku.sku_name AS skuName,
u.unit_name AS unitName,
inv.inv_num AS invNum,
cost.cost_price AS costPrice
FROM wms_goods_inventory inv
JOIN pms_goods_sku sku
ON sku.id = inv.product_sku_id
JOIN pms_goods g
ON g.id = sku.goods_id
JOIN pms_goods_unit u
ON u.id = inv.unit_id
LEFT JOIN wms_goods_cost cost
ON cost.product_sku_id = inv.product_sku_id
AND cost.unit_id = inv.unit_id
AND cost.workhouse_id = inv.workhouse_id
AND cost.del_flag = 0
WHERE inv.workhouse_id = #{q.workhouseId}
AND inv.del_flag = 0
<if test="q.goodsName != null and q.goodsName != ''">
AND g.goods_name LIKE CONCAT('%', #{q.goodsName}, '%')
</if>
<if test="q.categoryIds != null and q.categoryIds.size() > 0">
AND g.category_id IN
<foreach collection="q.categoryIds" item="cid" open="(" close=")" separator=",">
#{cid}
</foreach>
</if>
<if test="q.hasStock == true">
AND inv.inv_num > 0
</if>
</select>
浙公网安备 33010602011771号