package cn.iocoder.yudao.module.mes.dal.mysql.wm.transaction;
|
|
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.transaction.vo.MesWmStockSummaryRespVO;
|
import cn.iocoder.yudao.module.mes.controller.admin.wm.transaction.vo.MesWmTransactionPageReqVO;
|
import cn.iocoder.yudao.module.mes.dal.dataobject.wm.transaction.MesWmTransactionDO;
|
import com.baomidou.mybatisplus.core.metadata.IPage;
|
import org.apache.ibatis.annotations.Mapper;
|
import org.apache.ibatis.annotations.Param;
|
import org.apache.ibatis.annotations.Select;
|
|
import java.time.LocalDateTime;
|
|
/**
|
* MES 库存事务流水 Mapper
|
*/
|
@Mapper
|
public interface MesWmTransactionMapper extends BaseMapperX<MesWmTransactionDO> {
|
|
default PageResult<MesWmTransactionDO> selectPage(MesWmTransactionPageReqVO reqVO) {
|
return selectPage(reqVO, new LambdaQueryWrapperX<MesWmTransactionDO>()
|
.eqIfPresent(MesWmTransactionDO::getItemId, reqVO.getItemId())
|
.likeIfPresent(MesWmTransactionDO::getBatchCode, reqVO.getBatchCode())
|
.eqIfPresent(MesWmTransactionDO::getWarehouseId, reqVO.getWarehouseId())
|
.eqIfPresent(MesWmTransactionDO::getLocationId, reqVO.getLocationId())
|
.eqIfPresent(MesWmTransactionDO::getAreaId, reqVO.getAreaId())
|
.eqIfPresent(MesWmTransactionDO::getType, reqVO.getType())
|
.eqIfPresent(MesWmTransactionDO::getBizType, reqVO.getBizType())
|
.likeIfPresent(MesWmTransactionDO::getBizCode, reqVO.getBizCode())
|
.eqIfPresent(MesWmTransactionDO::getMaterialStockId, reqVO.getMaterialStockId())
|
.geIfPresent(MesWmTransactionDO::getTransactionTime, reqVO.getStartTime())
|
.leIfPresent(MesWmTransactionDO::getTransactionTime, reqVO.getEndTime())
|
.orderByDesc(MesWmTransactionDO::getTransactionTime));
|
}
|
|
/**
|
* 收发存汇总：按期初、本期入库、本期出库、期末四个口径按「物料 + 仓库」汇总
|
*/
|
@Select("""
|
<script>
|
SELECT t.item_id AS itemId,
|
t.warehouse_id AS warehouseId,
|
COALESCE(SUM(CASE WHEN t.transaction_time < #{startTime} THEN t.quantity ELSE 0 END), 0) AS openingQuantity,
|
COALESCE(SUM(CASE WHEN t.transaction_time >= #{startTime} AND t.transaction_time <= #{endTime} AND t.quantity > 0 THEN t.quantity ELSE 0 END), 0) AS inQuantity,
|
COALESCE(SUM(CASE WHEN t.transaction_time >= #{startTime} AND t.transaction_time <= #{endTime} AND t.quantity < 0 THEN -t.quantity ELSE 0 END), 0) AS outQuantity,
|
COALESCE(SUM(CASE WHEN t.transaction_time <= #{endTime} THEN t.quantity ELSE 0 END), 0) AS closingQuantity
|
FROM mes_wm_transaction t
|
WHERE t.deleted = 0
|
AND t.transaction_time <= #{endTime}
|
<if test="itemId != null"> AND t.item_id = #{itemId}</if>
|
<if test="warehouseId != null"> AND t.warehouse_id = #{warehouseId}</if>
|
GROUP BY t.item_id, t.warehouse_id
|
ORDER BY t.item_id, t.warehouse_id
|
</script>
|
""")
|
IPage<MesWmStockSummaryRespVO> selectStockSummaryPage(IPage<MesWmStockSummaryRespVO> page,
|
@Param("itemId") Long itemId,
|
@Param("warehouseId") Long warehouseId,
|
@Param("startTime") LocalDateTime startTime,
|
@Param("endTime") LocalDateTime endTime);
|
|
/**
|
* 查找虚拟线边库库存记录对应的原始出库仓库信息
|
* <p>
|
* 通过虚拟线边库的 materialStockId，找到首次入库事务（IN），
|
* 再通过 relatedTransactionId 找到关联的出库事务（OUT），
|
* 获取物料从哪个实际仓库出库进入虚拟线边库的。
|
*
|
* @param materialStockId 虚拟线边库库存记录 ID
|
* @return 原始出库事务（包含 warehouseId/locationId/areaId），不存在返回 null
|
*/
|
default MesWmTransactionDO selectOriginalOutTransaction(Long materialStockId) {
|
// 1. 找到该库存记录的首次入库事务
|
MesWmTransactionDO firstInTransaction = selectOne(new LambdaQueryWrapperX<MesWmTransactionDO>()
|
.eq(MesWmTransactionDO::getMaterialStockId, materialStockId)
|
.eq(MesWmTransactionDO::getType, 1) // IN
|
.orderByAsc(MesWmTransactionDO::getId)
|
.last("LIMIT 1"));
|
if (firstInTransaction == null || firstInTransaction.getRelatedTransactionId() == null) {
|
return null;
|
}
|
// 2. 通过 relatedTransactionId 找到原始出库事务
|
return selectById(firstInTransaction.getRelatedTransactionId());
|
}
|
|
}
|
