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();
|
}
|
