package jnpf.limsMapper;
|
|
import jnpf.base.mapper.SuperMapper;
|
import jnpf.limsEntity.LimsJianyanXiangEntity;
|
import org.apache.ibatis.annotations.Mapper;
|
import org.apache.ibatis.annotations.Param;
|
import org.apache.ibatis.annotations.Select;
|
import org.apache.ibatis.annotations.Update;
|
|
import java.util.List;
|
|
/**
|
* 检验任务"要求完成日期"提醒 daily 扫描专用 mapper。
|
*
|
* <p>4 个 @Select 各自负责一个分支（D7/D3/D1/EXPIRED），命中条件相互互斥，
|
* 不在同一次跑批里重复命中。各分支共同前置过滤：
|
* <ul>
|
* <li>未软删；wancheng_riqi 非空；jiancha_ren 非空</li>
|
* <li>jianyan_zhuangtai ∈ {pending_test, audit_rejected} —— 还未完成结果录入</li>
|
* <li>对应标记尚未追加到 remind_flags</li>
|
* </ul>
|
*
|
* <p>未绑 mybatis-plus 的 SuperMapper 通用增删改 —— 用 @Select 自定义 SQL
|
* 绕开多租户拦截器对列裁剪的副作用（见 LimsQingyandanReportServiceImpl 注释）。
|
*/
|
@Mapper
|
public interface LimsJianceTaskRemindMapper extends SuperMapper<LimsJianyanXiangEntity> {
|
|
/**
|
* D-N：wancheng_riqi 距今天还有 N 天（date 差值）且 remind_flags 不含对应标记。
|
*
|
* <p>列别名：自定义 @Select 不走 MyBatis-Plus 的 @TableField 映射，
|
* 必须用 AS 把 db 列名改成 Java 字段名（特别是 f_id → id）。
|
* 不发提醒的列（biz_type、规格、批量等）省略不查。
|
*/
|
@Select("SELECT f_id AS id," +
|
" f_tenant_id AS tenantId," +
|
" jiancha_ren AS jianchaRen," +
|
" renwu_fenpeiren AS renwuFenpeiren," +
|
" chanpin_mingcheng AS chanpinMingcheng," +
|
" pihao AS pihao," +
|
" jianyan_xiangmu_mingcheng AS jianyanXiangmuMingcheng," +
|
" wancheng_riqi AS wanchengRiqi," +
|
" remind_flags AS remindFlags" +
|
" FROM lims_jianyan_xiang " +
|
" WHERE (f_delete_mark IS NULL OR f_delete_mark <> 1) " +
|
" AND wancheng_riqi IS NOT NULL " +
|
" AND jiancha_ren IS NOT NULL AND jiancha_ren <> '' " +
|
" AND jianyan_zhuangtai IN ('pending_test', 'audit_rejected') " +
|
" AND wancheng_riqi::date - CURRENT_DATE = #{days} " +
|
" AND (remind_flags IS NULL OR NOT (#{flag} = ANY(string_to_array(remind_flags, ',')))) " +
|
" AND NOT EXISTS (SELECT 1 FROM lims_message_event e " +
|
" WHERE e.tenant_id = COALESCE(lims_jianyan_xiang.f_tenant_id, '0') " +
|
" AND e.occurrence_key = CONCAT('LIMS.INSPECTION.TASK.DUE:', lims_jianyan_xiang.f_id, ':', " +
|
" TO_CHAR(lims_jianyan_xiang.wancheng_riqi, 'YYYYMMDD'), ':', #{flag}))")
|
List<LimsJianyanXiangEntity> selectDueIn(@Param("days") int days,
|
@Param("flag") String flag);
|
|
/** EXPIRED：wancheng_riqi < today 且尚未发过过期告警。 */
|
@Select("SELECT f_id AS id," +
|
" f_tenant_id AS tenantId," +
|
" jiancha_ren AS jianchaRen," +
|
" renwu_fenpeiren AS renwuFenpeiren," +
|
" chanpin_mingcheng AS chanpinMingcheng," +
|
" pihao AS pihao," +
|
" jianyan_xiangmu_mingcheng AS jianyanXiangmuMingcheng," +
|
" wancheng_riqi AS wanchengRiqi," +
|
" remind_flags AS remindFlags" +
|
" FROM lims_jianyan_xiang " +
|
" WHERE (f_delete_mark IS NULL OR f_delete_mark <> 1) " +
|
" AND wancheng_riqi IS NOT NULL " +
|
" AND jiancha_ren IS NOT NULL AND jiancha_ren <> '' " +
|
" AND jianyan_zhuangtai IN ('pending_test', 'audit_rejected') " +
|
" AND wancheng_riqi < CURRENT_DATE " +
|
" AND (remind_flags IS NULL OR NOT ('EXPIRED' = ANY(string_to_array(remind_flags, ',')))) " +
|
" AND NOT EXISTS (SELECT 1 FROM lims_message_event e " +
|
" WHERE e.tenant_id = COALESCE(lims_jianyan_xiang.f_tenant_id, '0') " +
|
" AND e.occurrence_key = CONCAT('LIMS.INSPECTION.TASK.OVERDUE:', lims_jianyan_xiang.f_id, ':', " +
|
" TO_CHAR(lims_jianyan_xiang.wancheng_riqi, 'YYYYMMDD'), ':OVERDUE'))")
|
List<LimsJianyanXiangEntity> selectExpired();
|
|
/**
|
* 追加标记到 remind_flags（CSV 拼接，CONCAT_WS 自动处理 null）。
|
* 仅对仍处于"还未完成"状态的行追加，避免标记已完成项。
|
*/
|
@Update("UPDATE lims_jianyan_xiang " +
|
" SET remind_flags = CASE " +
|
" WHEN remind_flags IS NULL OR remind_flags = '' THEN #{flag} " +
|
" WHEN #{flag} = ANY(string_to_array(remind_flags, ',')) THEN remind_flags " +
|
" ELSE CONCAT(remind_flags, ',', #{flag}) " +
|
" END " +
|
" WHERE f_id = #{id} " +
|
" AND (f_delete_mark IS NULL OR f_delete_mark <> 1)")
|
int appendFlag(@Param("id") String id, @Param("flag") String flag);
|
}
|
