From 34981c30a78e8bbd7791131059a9210f9928b62c Mon Sep 17 00:00:00 2001
From: 刘光辉 <347230014@qq.com>
Date: 星期四, 17 九月 2026 09:24:09 +0800
Subject: [PATCH] Merge remote-tracking branch 'origin/master' into master
---
jnpf-lims/jnpf-lims-biz/src/main/java/jnpf/limsMapper/LimsBusinessReminderMapper.java | 178 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 178 insertions(+), 0 deletions(-)
diff --git a/jnpf-lims/jnpf-lims-biz/src/main/java/jnpf/limsMapper/LimsBusinessReminderMapper.java b/jnpf-lims/jnpf-lims-biz/src/main/java/jnpf/limsMapper/LimsBusinessReminderMapper.java
new file mode 100644
index 0000000..860de5b
--- /dev/null
+++ b/jnpf-lims/jnpf-lims-biz/src/main/java/jnpf/limsMapper/LimsBusinessReminderMapper.java
@@ -0,0 +1,178 @@
+package jnpf.limsMapper;
+
+import jnpf.limsEntity.*;
+import org.apache.ibatis.annotations.Mapper;
+import org.apache.ibatis.annotations.Select;
+
+import java.util.List;
+
+/**
+ * Read-only M1 reminder queries. Event-table exclusion keeps the daily scan bounded.
+ */
+@Mapper
+public interface LimsBusinessReminderMapper {
+
+ @Select("SELECT x.f_id AS id," +
+ " x.f_tenant_id AS tenantId," +
+ " j.mingcheng AS name," +
+ " j.bianhao AS code," +
+ " j.jiliang_ren AS recipientUserId," +
+ " x.jiliang_tixing_riqi AS reminderDate," +
+ " x.xiaci_jiliang_shijian AS dueDate" +
+ " FROM lims_shebei_yiqi_xinxi x" +
+ " JOIN LATERAL (" +
+ " SELECT r.mingcheng, r.bianhao, r.jiliang_ren" +
+ " FROM lims_shebei_yiqi_jiliang r" +
+ " WHERE r.yiqi_id = x.f_id" +
+ " AND COALESCE(r.f_tenant_id, '0') = COALESCE(x.f_tenant_id, '0')" +
+ " AND (r.f_delete_mark IS NULL OR r.f_delete_mark <> 1)" +
+ " AND r.xiaci_jiliang_shijian = x.xiaci_jiliang_shijian" +
+ " AND r.jiliang_ren IS NOT NULL AND r.jiliang_ren <> ''" +
+ " ORDER BY r.shangci_jiliang_shijian DESC NULLS LAST, r.f_id DESC" +
+ " LIMIT 1" +
+ " ) j ON TRUE" +
+ " WHERE (x.f_delete_mark IS NULL OR x.f_delete_mark <> 1)" +
+ " AND (x.biz_status IS NULL OR x.biz_status <> 'yibaofei')" +
+ " AND x.jiliang_tixing_riqi IS NOT NULL" +
+ " AND x.xiaci_jiliang_shijian IS NOT NULL" +
+ " AND x.jiliang_tixing_riqi::date <= CURRENT_DATE" +
+ " AND NOT EXISTS (SELECT 1 FROM lims_message_event e" +
+ " WHERE e.tenant_id = COALESCE(x.f_tenant_id, '0')" +
+ " AND e.occurrence_key = CONCAT('LIMS.EQUIPMENT.CALIBRATION.DUE:', x.f_id, ':'," +
+ " TO_CHAR(x.xiaci_jiliang_shijian, 'YYYYMMDD')))")
+ List<LimsEquipmentCalibrationReminderRow> selectEquipmentCalibrationDue();
+
+ @Select("SELECT x.f_id AS id," +
+ " x.f_tenant_id AS tenantId," +
+ " j.mingcheng AS name," +
+ " j.bianhao AS code," +
+ " j.yanzheng_ren AS recipientUserId," +
+ " x.yanzheng_tixing_riqi AS reminderDate," +
+ " x.xiaci_yanzheng_shijian AS dueDate" +
+ " FROM lims_shebei_yiqi_xinxi x" +
+ " JOIN LATERAL (" +
+ " SELECT r.mingcheng, r.bianhao, r.yanzheng_ren" +
+ " FROM lims_shebei_yiqi_yanzheng r" +
+ " WHERE r.yiqi_id = x.f_id" +
+ " AND COALESCE(r.f_tenant_id, '0') = COALESCE(x.f_tenant_id, '0')" +
+ " AND (r.f_delete_mark IS NULL OR r.f_delete_mark <> 1)" +
+ " AND r.xiaci_yanzheng_shijian = x.xiaci_yanzheng_shijian" +
+ " AND r.yanzheng_ren IS NOT NULL AND r.yanzheng_ren <> ''" +
+ " ORDER BY r.shangci_yanzheng_shijian DESC NULLS LAST, r.f_id DESC LIMIT 1" +
+ " ) j ON TRUE" +
+ " WHERE (x.f_delete_mark IS NULL OR x.f_delete_mark <> 1)" +
+ " AND (x.biz_status IS NULL OR x.biz_status <> 'yibaofei')" +
+ " AND x.yanzheng_tixing_riqi IS NOT NULL" +
+ " AND x.xiaci_yanzheng_shijian IS NOT NULL" +
+ " AND x.yanzheng_tixing_riqi::date <= CURRENT_DATE" +
+ " AND NOT EXISTS (SELECT 1 FROM lims_message_event e" +
+ " WHERE e.tenant_id = COALESCE(x.f_tenant_id, '0')" +
+ " AND e.occurrence_key = CONCAT('LIMS.EQUIPMENT.VERIFICATION.DUE:', x.f_id, ':'," +
+ " TO_CHAR(x.xiaci_yanzheng_shijian, 'YYYYMMDD')))")
+ List<LimsEquipmentCalibrationReminderRow> selectEquipmentVerificationDue();
+
+ @Select("SELECT z.f_id AS id," +
+ " z.f_tenant_id AS tenantId," +
+ " z.f_foreign_id AS planId," +
+ " z.yangpin_mingcheng AS sampleName," +
+ " z.yangpin_bianma AS sampleCode," +
+ " z.pihao AS batchNo," +
+ " z.guancha_ren AS recipientUserId," +
+ " z.guancha_riqi AS observationDate" +
+ " FROM lims_liuyang_jihua_zixiang z" +
+ " WHERE (z.f_delete_mark IS NULL OR z.f_delete_mark <> 1)" +
+ " AND (z.biz_status IS NULL OR z.biz_status <> 'disabled')" +
+ " AND (z.shifou_yiguancha IS NULL OR z.shifou_yiguancha <> 'yes')" +
+ " AND z.guancha_riqi IS NOT NULL" +
+ " AND z.guancha_riqi::date <= CURRENT_DATE" +
+ " AND z.guancha_ren IS NOT NULL AND z.guancha_ren <> ''" +
+ " AND NOT EXISTS (SELECT 1 FROM lims_message_event e" +
+ " WHERE e.tenant_id = COALESCE(z.f_tenant_id, '0')" +
+ " AND e.occurrence_key = CONCAT('LIMS.RETAIN.SAMPLE.OBSERVATION.DUE:', z.f_id, ':'," +
+ " TO_CHAR(z.guancha_riqi, 'YYYYMMDD')))")
+ List<LimsRetainObservationReminderRow> selectRetainObservationDue();
+
+ @Select("SELECT r.jihua_id AS planId, r.f_tenant_id AS tenantId," +
+ " MAX(r.jihua_mingcheng) AS planName, MAX(r.jihua_leixing) AS planType," +
+ " r.jihua_riqi AS plannedDate," +
+ " STRING_AGG(DISTINCT NULLIF(r.jiance_quyu, ''), '銆�') AS monitoringArea," +
+ " STRING_AGG(DISTINCT NULLIF(r.jiejing_jibie, ''), '銆�') AS cleanlinessLevel," +
+ " STRING_AGG(DISTINCT NULLIF(r.jiance_xiangmu, ''), '銆�') AS monitoringItems" +
+ " FROM lims_huanjing_jiance_jilu r" +
+ " WHERE (r.f_delete_mark IS NULL OR r.f_delete_mark <> 1)" +
+ " AND r.biz_status = 'enabled'" +
+ " AND r.jihua_riqi IS NOT NULL AND r.jihua_riqi::date <= CURRENT_DATE" +
+ " AND r.quyang_riqi IS NULL AND (r.quyang_ren IS NULL OR r.quyang_ren = '')" +
+ " AND NOT EXISTS (SELECT 1 FROM lims_message_event e" +
+ " WHERE e.tenant_id = COALESCE(r.f_tenant_id, '0')" +
+ " AND e.occurrence_key = CONCAT('LIMS.ENVIRONMENT.SAMPLING.DUE:', r.jihua_id, ':'," +
+ " TO_CHAR(r.jihua_riqi, 'YYYYMMDD')))" +
+ " GROUP BY r.jihua_id, r.f_tenant_id, r.jihua_riqi")
+ List<LimsEnvironmentSamplingReminderRow> selectEnvironmentSamplingDue();
+
+ @Select("SELECT z.f_id AS id, z.f_tenant_id AS tenantId, z.bianhao AS batchCode," +
+ " z.tiaoma_bianma AS barcode, z.mingcheng AS name, z.guige AS specification," +
+ " z.shengyuliang AS remainingQuantity, z.danwei AS unit," +
+ " z.cunfang_weizhi AS storageLocation, z.youxiaoqi AS expiryDate," +
+ " (z.youxiaoqi::date - CURRENT_DATE) AS remainingDays," +
+ " CASE (z.youxiaoqi::date - CURRENT_DATE)" +
+ " WHEN 30 THEN 'D30' WHEN 7 THEN 'D7' WHEN 1 THEN 'D1' ELSE 'D0' END AS reminderStage" +
+ " FROM lims_haocai_ruku_zixiang z" +
+ " WHERE (z.f_delete_mark IS NULL OR z.f_delete_mark <> 1)" +
+ " AND z.biz_status IN ('weichuku', 'yikaiping')" +
+ " AND z.shengyuliang ~ '^\\s*[0-9]+(\\.[0-9]+)?\\s*$'" +
+ " AND z.shengyuliang::numeric > 0 AND z.youxiaoqi IS NOT NULL" +
+ " AND (z.youxiaoqi::date - CURRENT_DATE) IN (30, 7, 1, 0)" +
+ " AND NOT EXISTS (SELECT 1 FROM lims_message_event e" +
+ " WHERE e.tenant_id = COALESCE(z.f_tenant_id, '0')" +
+ " AND e.occurrence_key = CONCAT('LIMS.CONSUMABLE.BATCH.EXPIRY.DUE:', z.f_id, ':'," +
+ " TO_CHAR(z.youxiaoqi, 'YYYYMMDD'), ':'," +
+ " CASE (z.youxiaoqi::date - CURRENT_DATE)" +
+ " WHEN 30 THEN 'D30' WHEN 7 THEN 'D7' WHEN 1 THEN 'D1' ELSE 'D0' END))")
+ List<LimsConsumableExpiryReminderRow> selectConsumableExpiryDue();
+
+ @Select("SELECT l.f_id AS id, l.f_tenant_id AS tenantId, l.yangpin_mingcheng AS sampleName," +
+ " l.yangpin_bianma AS sampleCode, l.pihao AS batchNo," +
+ " l.liuyang_xiancunliang AS currentQuantity," +
+ " COALESCE(NULLIF(l.liuyang_xiancunliang_danwei, ''), l.liuyang_danwei) AS unit," +
+ " l.liuyang_weizhi AS storageLocation, l.baocun_qixian AS retentionDeadline" +
+ " FROM lims_liuyang_jilu l" +
+ " WHERE (l.f_delete_mark IS NULL OR l.f_delete_mark <> 1)" +
+ " AND l.baocun_qixian IS NOT NULL AND l.baocun_qixian::date <= CURRENT_DATE" +
+ " AND (l.xiaohui_danhao IS NULL OR l.xiaohui_danhao = '')" +
+ " AND COALESCE(l.biz_status, '') NOT IN ('daixiaohui', 'yixiaohui', 'destroyed')" +
+ " AND NOT EXISTS (SELECT 1 FROM lims_message_event e" +
+ " WHERE e.tenant_id = COALESCE(l.f_tenant_id, '0')" +
+ " AND e.occurrence_key = CONCAT('LIMS.RETAIN.SAMPLE.DESTRUCTION.DUE:', l.f_id, ':'," +
+ " TO_CHAR(l.baocun_qixian, 'YYYYMMDD')))")
+ List<LimsRetainDestructionReminderRow> selectRetainDestructionDue();
+
+ @Select("SELECT c.f_id AS id, c.kaoca_jihua_id AS planId, c.f_tenant_id AS tenantId," +
+ " p.bianhao AS planCode, p.kaoca_mingcheng AS planName," +
+ " p.yangpin_mingcheng AS sampleName, c.pihao AS batchNo," +
+ " c.zhouqi AS period, c.zhouqi_danwei AS periodUnit," +
+ " c.jiahua_anpai_riqi AS plannedDate," +
+ " STRING_AGG(DISTINCT NULLIF(z.jianyan_xiangmu_mingcheng, ''), '銆�') AS inspectionItems," +
+ " STRING_AGG(DISTINCT NULLIF(z.jiancha_banzu, ''), ',') AS organizationIds" +
+ " FROM lims_wendingxing_kaoca_jihua_zhouqi c" +
+ " JOIN lims_wendingxing_kaoca_jihua p ON p.f_id = c.kaoca_jihua_id" +
+ " CROSS JOIN LATERAL regexp_split_to_table(" +
+ " TRIM(BOTH '[] ' FROM COALESCE(c.jiancha_xiangmu_ids, '')), '\\s*,\\s*') item(item_id)" +
+ " JOIN zhiliang_biaozhun_guanli_zixiang z" +
+ " ON z.f_id = TRIM(BOTH '\"' FROM item.item_id)" +
+ " AND COALESCE(z.f_tenant_id, '0') = COALESCE(c.f_tenant_id, '0')" +
+ " WHERE (c.f_delete_mark IS NULL OR c.f_delete_mark <> 1)" +
+ " AND (p.f_delete_mark IS NULL OR p.f_delete_mark <> 1)" +
+ " AND (z.f_delete_mark IS NULL OR z.f_delete_mark <> 1)" +
+ " AND (c.shifou_yizhixing IS NULL OR c.shifou_yizhixing <> 'yes')" +
+ " AND (c.shifou_jixu_zhixing IS NULL OR c.shifou_jixu_zhixing <> 'no')" +
+ " AND c.jiahua_anpai_riqi IS NOT NULL AND c.jiahua_anpai_riqi::date <= CURRENT_DATE" +
+ " AND NOT EXISTS (SELECT 1 FROM lims_message_event e" +
+ " WHERE e.tenant_id = COALESCE(c.f_tenant_id, '0')" +
+ " AND e.occurrence_key = CONCAT('LIMS.STABILITY.PERIOD.DUE:', c.f_id, ':'," +
+ " TO_CHAR(c.jiahua_anpai_riqi, 'YYYYMMDD')))" +
+ " GROUP BY c.f_id, c.kaoca_jihua_id, c.f_tenant_id, p.bianhao, p.kaoca_mingcheng," +
+ " p.yangpin_mingcheng, c.pihao, c.zhouqi, c.zhouqi_danwei, c.jiahua_anpai_riqi" +
+ " HAVING COUNT(NULLIF(z.jiancha_banzu, '')) > 0")
+ List<LimsStabilityPeriodReminderRow> selectStabilityPeriodDue();
+}
--
Gitblit v1.8.0