package cn.iocoder.yudao.module.mes.dal.mysql.wm.materialstock;
|
|
import cn.iocoder.yudao.framework.common.pojo.PageResult;
|
import cn.iocoder.yudao.framework.mybatis.core.mapper.BaseMapperX;
|
import cn.iocoder.yudao.framework.mybatis.core.query.LambdaQueryWrapperX;
|
import cn.iocoder.yudao.module.mes.controller.admin.wm.materialstock.vo.MesWmMaterialStockListReqVO;
|
import cn.iocoder.yudao.module.mes.controller.admin.wm.materialstock.vo.MesWmMaterialStockPageReqVO;
|
import cn.iocoder.yudao.module.mes.dal.dataobject.wm.materialstock.MesWmMaterialStockDO;
|
import cn.iocoder.yudao.module.mes.enums.wm.MesWmStockStateEnum;
|
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
|
import org.apache.ibatis.annotations.Mapper;
|
|
import java.math.BigDecimal;
|
import java.util.Collection;
|
import java.util.List;
|
|
/**
|
* MES 库存台账 Mapper
|
*/
|
@Mapper
|
public interface MesWmMaterialStockMapper extends BaseMapperX<MesWmMaterialStockDO> {
|
|
default PageResult<MesWmMaterialStockDO> selectPage(MesWmMaterialStockPageReqVO reqVO,
|
Collection<Long> itemTypeIds,
|
Collection<Long> itemIds,
|
Long virtualWarehouseId,
|
Collection<Long> warehouseIds) {
|
LambdaQueryWrapperX<MesWmMaterialStockDO> wrapper = new LambdaQueryWrapperX<MesWmMaterialStockDO>()
|
.inIfPresent(MesWmMaterialStockDO::getItemTypeId, itemTypeIds)
|
.inIfPresent(MesWmMaterialStockDO::getItemId, itemIds)
|
.likeIfPresent(MesWmMaterialStockDO::getBatchCode, reqVO.getBatchCode())
|
.eqIfPresent(MesWmMaterialStockDO::getBatchId, reqVO.getBatchId())
|
.eqIfPresent(MesWmMaterialStockDO::getWarehouseId, reqVO.getWarehouseId())
|
.inIfPresent(MesWmMaterialStockDO::getWarehouseId, warehouseIds)
|
.eqIfPresent(MesWmMaterialStockDO::getLocationId, reqVO.getLocationId())
|
.eqIfPresent(MesWmMaterialStockDO::getAreaId, reqVO.getAreaId())
|
.eqIfPresent(MesWmMaterialStockDO::getVendorId, reqVO.getVendorId())
|
.eqIfPresent(MesWmMaterialStockDO::getStockState, reqVO.getStockState())
|
.eqIfPresent(MesWmMaterialStockDO::getFrozen, reqVO.getFrozen());
|
wrapper.ne(MesWmMaterialStockDO::getQuantity, BigDecimal.ZERO)
|
.orderByAsc(MesWmMaterialStockDO::getReceiptTime);
|
// 虚拟仓过滤（Service 层已将 virtualFilter 解析为 virtualWarehouseId）
|
if (virtualWarehouseId != null) {
|
if (MesWmMaterialStockPageReqVO.VIRTUAL_FILTER_ONLY.equals(reqVO.getVirtualFilter())) {
|
wrapper.eq(MesWmMaterialStockDO::getWarehouseId, virtualWarehouseId);
|
} else if (MesWmMaterialStockPageReqVO.VIRTUAL_FILTER_EXCLUDE.equals(reqVO.getVirtualFilter())) {
|
wrapper.ne(MesWmMaterialStockDO::getWarehouseId, virtualWarehouseId);
|
}
|
}
|
return selectPage(reqVO, wrapper);
|
}
|
|
default Long selectCountByWarehouseId(Long warehouseId) {
|
return selectCount(MesWmMaterialStockDO::getWarehouseId, warehouseId);
|
}
|
|
default Long selectCountByLocationId(Long locationId) {
|
return selectCount(MesWmMaterialStockDO::getLocationId, locationId);
|
}
|
|
default Long selectCountByAreaId(Long areaId) {
|
return selectCount(MesWmMaterialStockDO::getAreaId, areaId);
|
}
|
|
default List<MesWmMaterialStockDO> selectListByIds(Collection<Long> ids) {
|
return selectByIds(ids);
|
}
|
|
default void updateByIds(Collection<Long> ids, MesWmMaterialStockDO updateObj) {
|
update(updateObj, new LambdaUpdateWrapper<MesWmMaterialStockDO>()
|
.in(MesWmMaterialStockDO::getId, ids));
|
}
|
|
/**
|
* 增量更新库存数量
|
*
|
* @param id 库存记录编号
|
* @param count 变动数量（正数=增加，负数=扣减）
|
* @param checkFlag 是否校验库存充足（为 true 时扣减不允许变为负数）
|
* @return 影响行数
|
*/
|
default int updateQuantity(Long id, BigDecimal count, boolean checkFlag) {
|
LambdaUpdateWrapper<MesWmMaterialStockDO> updateWrapper = new LambdaUpdateWrapper<MesWmMaterialStockDO>()
|
.eq(MesWmMaterialStockDO::getId, id)
|
.setSql("quantity = quantity + " + count);
|
if (checkFlag && count.compareTo(BigDecimal.ZERO) < 0) {
|
updateWrapper.ge(MesWmMaterialStockDO::getQuantity, count.abs()); // CAS 防负库存
|
}
|
return update(null, updateWrapper);
|
}
|
|
/**
|
* 增量更新库存占用量
|
*
|
* @param id 库存记录编号
|
* @param count 变动数量（正数=增加占用，负数=减少占用）
|
* @param checkFlag 是否校验可用量充足（为 true 时占用不允许超过可用量）
|
* @return 影响行数
|
*/
|
default int updateReservedQuantity(Long id, BigDecimal count, boolean checkFlag) {
|
LambdaUpdateWrapper<MesWmMaterialStockDO> updateWrapper = new LambdaUpdateWrapper<MesWmMaterialStockDO>()
|
.eq(MesWmMaterialStockDO::getId, id)
|
.setSql("reserved_quantity = reserved_quantity + " + count);
|
if (checkFlag && count.compareTo(BigDecimal.ZERO) > 0) {
|
// 校验可用量充足：quantity - frozen_quantity - reserved_quantity >= count
|
updateWrapper.apply("quantity - IFNULL(frozen_quantity, 0) - IFNULL(reserved_quantity, 0) >= {0}", count);
|
}
|
return update(null, updateWrapper);
|
}
|
|
/**
|
* 增量更新库存在途量
|
*
|
* @param id 库存记录编号
|
* @param count 变动数量（正数=增加在途，负数=减少在途）
|
* @return 影响行数
|
*/
|
default int updateInTransitQuantity(Long id, BigDecimal count) {
|
LambdaUpdateWrapper<MesWmMaterialStockDO> updateWrapper = new LambdaUpdateWrapper<MesWmMaterialStockDO>()
|
.eq(MesWmMaterialStockDO::getId, id)
|
.setSql("in_transit_quantity = in_transit_quantity + " + count);
|
return update(null, updateWrapper);
|
}
|
|
/**
|
* 按组合键查询库存记录
|
*
|
* 唯一键：(itemId, warehouseId, locationId, areaId, batchId, vendorId)
|
* 同一物料同一库位，不同供应商/批次的库存分开管理
|
*/
|
default MesWmMaterialStockDO selectByCompositeKey(Long itemId, Long warehouseId, Long locationId,
|
Long areaId, Long batchId, Long vendorId) {
|
LambdaQueryWrapperX<MesWmMaterialStockDO> wrapper = new LambdaQueryWrapperX<MesWmMaterialStockDO>()
|
.eqIfPresent(MesWmMaterialStockDO::getItemId, itemId)
|
.eqIfPresent(MesWmMaterialStockDO::getWarehouseId, warehouseId)
|
.eqIfPresent(MesWmMaterialStockDO::getLocationId, locationId)
|
.eqIfPresent(MesWmMaterialStockDO::getAreaId, areaId);
|
// batchId=null 时精确匹配 is null，避免 eqIfPresent 跳过条件导致匹配到其他批次
|
if (batchId != null) {
|
wrapper.eq(MesWmMaterialStockDO::getBatchId, batchId);
|
} else {
|
wrapper.isNull(MesWmMaterialStockDO::getBatchId);
|
}
|
// vendorId=null 时不限制，匹配任意供应商的库存
|
// （避免因供应商维度拆分，OUT 事务找不到真正有库存的记录而扣错到 vendor_id IS NULL 的记录）
|
if (vendorId != null) {
|
wrapper.eq(MesWmMaterialStockDO::getVendorId, vendorId);
|
}
|
// 多记录时优先取库存量最多的（避免扣到零库存或负库存的记录）
|
wrapper.last("ORDER BY quantity DESC LIMIT 1");
|
return selectOne(wrapper);
|
}
|
|
/**
|
* 按组合键查询库存记录（忽略供应商维度，仅合格库）
|
*
|
* 用于移库场景：移库时将合格库存合并到目标库位已有合格库存，不区分供应商
|
* 只匹配合格库(20)，避免将移入数量合并到同批次的暂存库记录
|
*/
|
default MesWmMaterialStockDO selectByCompositeKeyIgnoreVendor(Long itemId, Long warehouseId, Long locationId,
|
Long areaId, Long batchId) {
|
LambdaQueryWrapperX<MesWmMaterialStockDO> wrapper = new LambdaQueryWrapperX<MesWmMaterialStockDO>()
|
.eq(MesWmMaterialStockDO::getItemId, itemId)
|
.eq(MesWmMaterialStockDO::getWarehouseId, warehouseId)
|
.eq(MesWmMaterialStockDO::getLocationId, locationId)
|
.eq(MesWmMaterialStockDO::getAreaId, areaId)
|
.eq(MesWmMaterialStockDO::getStockState, MesWmStockStateEnum.QUALIFIED.getState());
|
// batchId=null 时精确匹配 is null
|
if (batchId != null) {
|
wrapper.eq(MesWmMaterialStockDO::getBatchId, batchId);
|
} else {
|
wrapper.isNull(MesWmMaterialStockDO::getBatchId);
|
}
|
wrapper.last("LIMIT 1");
|
return selectOne(wrapper);
|
}
|
|
/**
|
* 按组合键查询合格库库存记录（用于出库事务匹配）
|
*
|
* 同批次可能同时存在暂存库(10)与合格库(20)两条记录（种子质检按袋拆数量），
|
* 出库必须只从合格库匹配，避免扣到未质检的暂存库存。
|
*/
|
default MesWmMaterialStockDO selectQualifiedByCompositeKey(Long itemId, Long warehouseId, Long locationId,
|
Long areaId, Long batchId, Long vendorId) {
|
LambdaQueryWrapperX<MesWmMaterialStockDO> wrapper = new LambdaQueryWrapperX<MesWmMaterialStockDO>()
|
.eq(MesWmMaterialStockDO::getItemId, itemId)
|
.eq(MesWmMaterialStockDO::getWarehouseId, warehouseId)
|
.eq(MesWmMaterialStockDO::getLocationId, locationId)
|
.eq(MesWmMaterialStockDO::getAreaId, areaId)
|
.eq(MesWmMaterialStockDO::getStockState, MesWmStockStateEnum.QUALIFIED.getState());
|
// batchId=null 时精确匹配 is null
|
if (batchId != null) {
|
wrapper.eq(MesWmMaterialStockDO::getBatchId, batchId);
|
} else {
|
wrapper.isNull(MesWmMaterialStockDO::getBatchId);
|
}
|
if (vendorId != null) {
|
wrapper.eq(MesWmMaterialStockDO::getVendorId, vendorId);
|
}
|
wrapper.last("ORDER BY quantity DESC LIMIT 1");
|
return selectOne(wrapper);
|
}
|
|
default List<MesWmMaterialStockDO> selectList(MesWmMaterialStockListReqVO reqVO) {
|
return selectList(reqVO, null);
|
}
|
|
default List<MesWmMaterialStockDO> selectList(MesWmMaterialStockListReqVO reqVO, Collection<Long> warehouseIds) {
|
return selectList(new LambdaQueryWrapperX<MesWmMaterialStockDO>()
|
.eqIfPresent(MesWmMaterialStockDO::getWarehouseId, reqVO.getWarehouseId())
|
.inIfPresent(MesWmMaterialStockDO::getWarehouseId, warehouseIds)
|
.eqIfPresent(MesWmMaterialStockDO::getLocationId, reqVO.getLocationId())
|
.eqIfPresent(MesWmMaterialStockDO::getAreaId, reqVO.getAreaId())
|
.eqIfPresent(MesWmMaterialStockDO::getItemId, reqVO.getItemId())
|
.eqIfPresent(MesWmMaterialStockDO::getBatchId, reqVO.getBatchId())
|
.eqIfPresent(MesWmMaterialStockDO::getVendorId, reqVO.getVendorId())
|
.eqIfPresent(MesWmMaterialStockDO::getStockState, reqVO.getStockState())
|
.likeIfPresent(MesWmMaterialStockDO::getBatchCode, reqVO.getBatchCode())
|
.geIfPresent(MesWmMaterialStockDO::getUpdateTime, reqVO.getStartTime())
|
.leIfPresent(MesWmMaterialStockDO::getUpdateTime, reqVO.getEndTime())
|
.ne(MesWmMaterialStockDO::getQuantity, BigDecimal.ZERO)
|
.orderByAsc(MesWmMaterialStockDO::getReceiptTime));
|
}
|
|
/**
|
* 按物料编号批量查询库存记录（用于物料需求库存校验）
|
*/
|
default List<MesWmMaterialStockDO> selectListByItemAndBatch(Long itemId, Long batchId) {
|
return selectList(new LambdaQueryWrapperX<MesWmMaterialStockDO>()
|
.eq(MesWmMaterialStockDO::getItemId, itemId)
|
.eqIfPresent(MesWmMaterialStockDO::getBatchId, batchId)
|
.gt(MesWmMaterialStockDO::getQuantity, BigDecimal.ZERO)
|
.orderByAsc(MesWmMaterialStockDO::getReceiptTime));
|
}
|
|
/**
|
* 按物料/批次查询合格库库存记录（仅 stock_state=20，且仅限合格仓库）
|
*
|
* <p>同批次可能同时存在暂存库(10)与合格库(20)两条记录（种子质检按袋拆数量），
|
* 销售出库选库存只能从合格库记录中选，杜绝选中未质检的暂存库存；
|
* 同时限定合格仓库（type=20），避免从暂存仓库中误选合格态库存。</p>
|
*/
|
default List<MesWmMaterialStockDO> selectQualifiedListByItemAndBatch(Long itemId, Long batchId,
|
Collection<Long> qualifiedWarehouseIds) {
|
return selectList(new LambdaQueryWrapperX<MesWmMaterialStockDO>()
|
.eq(MesWmMaterialStockDO::getItemId, itemId)
|
.eqIfPresent(MesWmMaterialStockDO::getBatchId, batchId)
|
.eq(MesWmMaterialStockDO::getStockState, MesWmStockStateEnum.QUALIFIED.getState())
|
.in(MesWmMaterialStockDO::getWarehouseId, qualifiedWarehouseIds)
|
.gt(MesWmMaterialStockDO::getQuantity, BigDecimal.ZERO)
|
.orderByAsc(MesWmMaterialStockDO::getReceiptTime));
|
}
|
|
/**
|
* 按仓库批次查询全部库存记录（含暂存库与合格库，quantity>0）
|
*
|
* <p>用于种子质检按袋码流转：定位该批次暂存库记录扣减 1 袋，以及定位/新建合格库记录累加 1 袋。</p>
|
*/
|
default List<MesWmMaterialStockDO> selectStockListByBatchId(Long batchId) {
|
return selectList(new LambdaQueryWrapperX<MesWmMaterialStockDO>()
|
.eq(MesWmMaterialStockDO::getBatchId, batchId)
|
.gt(MesWmMaterialStockDO::getQuantity, BigDecimal.ZERO)
|
.orderByAsc(MesWmMaterialStockDO::getReceiptTime));
|
}
|
|
default List<MesWmMaterialStockDO> selectListByItemIds(Collection<Long> itemIds) {
|
if (itemIds == null || itemIds.isEmpty()) {
|
return java.util.Collections.emptyList();
|
}
|
return selectList(new LambdaQueryWrapperX<MesWmMaterialStockDO>()
|
.in(MesWmMaterialStockDO::getItemId, itemIds)
|
.ne(MesWmMaterialStockDO::getQuantity, BigDecimal.ZERO));
|
}
|
|
/**
|
* 统计批次在暂存库中的库存记录数（用于种子质检前校验袋码/批次是否处于待质检状态）
|
*
|
* @param batchId 仓库批次ID
|
* @return 暂存库库存记录数
|
*/
|
default Long selectTempStockCountByBatchId(Long batchId) {
|
return selectCount(new LambdaQueryWrapperX<MesWmMaterialStockDO>()
|
.eq(MesWmMaterialStockDO::getBatchId, batchId)
|
.eq(MesWmMaterialStockDO::getStockState, MesWmStockStateEnum.TEMP.getState())
|
.gt(MesWmMaterialStockDO::getQuantity, BigDecimal.ZERO));
|
}
|
|
}
|
