package jnpf.limsMapper;
|
|
import jnpf.base.mapper.SuperMapper;
|
import jnpf.limsEntity.LimsHaocaiAlertRow;
|
import jnpf.limsEntity.LimsHaocaiRukuEntity;
|
import org.apache.ibatis.annotations.Mapper;
|
import org.apache.ibatis.annotations.Param;
|
import org.apache.ibatis.annotations.Select;
|
|
import java.util.List;
|
|
/**
|
* 耗材缺货预警 daily 扫描专用 mapper。
|
*
|
* <p>用 @Select 自定义 SQL：跨 lims 业务表与 base 平台表聚合查询，
|
* 列名按 L007 用 AS 别名映射到 Java 字段名。
|
*/
|
@Mapper
|
public interface LimsHaocaiAlertMapper extends SuperMapper<LimsHaocaiRukuEntity> {
|
|
/**
|
* 查所有"结余总量 < 缺货预警值"的耗材档案。
|
*
|
* <p>按 haocai_id = 档案 f_id 关联入库表，剔除已删除与 jieyu_shuliang='0' 后求和；
|
* LEFT JOIN 保证完全无库存（sum=0）的耗材也能命中。
|
* quehuo_yujing / jieyu_shuliang 是 varchar：PG 下 CAST 遇非数值直接抛错（MySQL 宽松返 0），
|
* 一律先用正则守护，非数值按 NULL 处理（该行不参与求和/不触发预警，不炸整个跑批）。
|
*/
|
@Select("SELECT d.f_id AS id," +
|
" d.f_tenant_id AS tenantId," +
|
" d.mingcheng AS mingcheng," +
|
" d.quehuo_yujing AS quehuoYujing," +
|
" COALESCE(SUM(CASE WHEN r.jieyu_shuliang ~ '^-?[0-9]+(\\.[0-9]+)?$'" +
|
" THEN CAST(r.jieyu_shuliang AS NUMERIC(20,4)) END), 0) AS jieyuSum" +
|
" FROM lims_haocai_dangan d" +
|
" LEFT JOIN lims_haocai_ruku r" +
|
" ON r.haocai_id = d.f_id" +
|
" AND (r.f_delete_mark IS NULL OR r.f_delete_mark <> 1)" +
|
" AND r.jieyu_shuliang IS NOT NULL AND r.jieyu_shuliang <> '' AND r.jieyu_shuliang <> '0'" +
|
" WHERE (d.f_delete_mark IS NULL OR d.f_delete_mark <> 1)" +
|
" AND d.quehuo_yujing ~ '^-?[0-9]+(\\.[0-9]+)?$'" +
|
" AND NOT EXISTS (SELECT 1 FROM lims_message_event e" +
|
" WHERE e.tenant_id = COALESCE(d.f_tenant_id, '0')" +
|
" AND e.occurrence_key = CONCAT('LIMS.CONSUMABLE.STOCK.LOW:', d.f_id, ':'," +
|
" TO_CHAR(CURRENT_DATE, 'YYYYMMDD')))" +
|
" GROUP BY d.f_id, d.f_tenant_id, d.mingcheng, d.quehuo_yujing" +
|
" HAVING COALESCE(SUM(CASE WHEN r.jieyu_shuliang ~ '^-?[0-9]+(\\.[0-9]+)?$'" +
|
" THEN CAST(r.jieyu_shuliang AS NUMERIC(20,4)) END), 0)" +
|
" < CAST(d.quehuo_yujing AS NUMERIC(20,4))")
|
List<LimsHaocaiAlertRow> selectBelowThreshold();
|
|
/**
|
* 按角色编码查该角色下在职（启用、未删）用户 id 列表。
|
* 用 enCode 而非角色 f_id，避免硬编码环境相关主键。
|
* 角色成员存 base_role_relation（f_object_type='user' 小写）。
|
*/
|
@Select("SELECT r.f_object_id" +
|
" FROM base_role_relation r" +
|
" JOIN base_role b ON r.f_role_id = b.f_id" +
|
" JOIN base_user u ON u.f_id = r.f_object_id" +
|
" WHERE b.f_en_code = #{enCode}" +
|
" AND r.f_object_type = 'user'" +
|
" AND (b.f_delete_mark IS NULL OR b.f_delete_mark <> 1)" +
|
" AND (u.f_delete_mark IS NULL OR u.f_delete_mark <> 1)" +
|
" AND u.f_enabled_mark = 1")
|
List<String> selectUserIdsByRoleEnCode(@Param("enCode") String enCode);
|
}
|
