package zbooksoft.fams.dao; import com.ruili.wcp.common.IdGenerater; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Repository; import java.sql.Date; import java.time.LocalDate; import java.util.ArrayList; import java.util.Collections; import java.util.List; import java.util.Map; import java.util.regex.Pattern; /** * 到期提醒:到期资产扫描、提醒记录、系统消息、提醒设置的数据访问 * @author ycq * @date 2026-10-16 */ @Repository public class RemindDao { public static final String ADMIN_GROUP_CODE = "FAMS_ASSET_ADMIN"; public static final String SETTING_KEYWORD = "到期提醒设置"; public static final String SETTING_DAYS_AHEAD = "提前天数"; private static final Pattern SAFE_COLUMN = Pattern.compile("^[A-Z_]+$"); private static final String SYSTEM_SENDER = "系统"; private static final long SYSTEM_SENDER_ID = 0L; /** 提醒类型 -> 卡片上的到期日字段;借用到期只看借用中的资产 */ private static final String[][] DUE_COLUMNS = { {"质保到期", "WARRANTY_EXPIRE_DATE", ""}, {"维保到期", "NEXT_MAINTAIN_DATE", ""}, {"检定/年检到期", "VERIFY_EXPIRE_DATE", ""}, {"借用到期", "BORROW_EXPIRE_DATE", " and ASSET_STATE = '借用'"}}; @Autowired private JdbcTemplate jdbcTemplate; /** * 读取关键字「到期提醒设置」中的「提前天数」 * @return 配置值,未配置返回null */ public String findDaysAhead() { List values = jdbcTemplate.queryForList("select p.param_value from sys_keyword_param p join sys_keyword k on k.keyword_id = p.keyword_id " + "where k.keyword_name = ? and p.param_name = ?", String.class, SETTING_KEYWORD, SETTING_DAYS_AHEAD); return values.isEmpty() ? null : values.get(0); } /** * 查到期日在指定日期之前(含已过期)的资产,每种到期类型各一行 * @param until 截止日期(含) * @return ASSET_ID、REMIND_TYPE、DUE_DATE 及展示字段 */ public List> findDueItems(LocalDate until) { StringBuilder sql = new StringBuilder(); List args = new ArrayList<>(); for (String[] due : DUE_COLUMNS) { if (sql.length() > 0) { sql.append(" union all "); } sql.append("select ID as ASSET_ID, ASSET_NO, ASSET_NAME, USER_ID, USER_NAME, USE_DEPT_NAME, LOCATION_NAME, ? as REMIND_TYPE, ") .append(safe(due[1])).append(" as DUE_DATE from BUS_ASSET_CARD where ifnull(DELETE_STATE, 0) = 0 and ifnull(ASSET_NO, '') <> '' ") .append("and ifnull(ASSET_STATE, '') <> '报废' and ").append(due[1]).append(" is not null and ").append(due[1]).append(" < ?") .append(due[2]); args.add(due[0]); args.add(Date.valueOf(until.plusDays(1))); } return jdbcTemplate.queryForList(sql.toString(), args.toArray()); } /** * 查待处理与已处理的提醒(用于去重与失效判断) * @return 提醒行 */ public List> findActiveReminds() { return jdbcTemplate.queryForList("select ID, ASSET_ID, REMIND_TYPE, DUE_DATE, REMIND_STATE from BUS_ASSET_REMIND where REMIND_STATE in ('待处理', '已处理')"); } /** * 按主键查提醒 * @param id 提醒ID * @return 提醒行,不存在返回null */ public Map findRemind(Long id) { List> rows = jdbcTemplate.queryForList("select ID, REMIND_STATE from BUS_ASSET_REMIND where ID = ?", id); return rows.isEmpty() ? null : rows.get(0); } /** * 查「资产管理员」群组的成员(到期提醒消息的接收人) * @return 用户ID */ public List findAdminUserIds() { return jdbcTemplate.queryForList("select gu.user_id from sys_group_user gu join sys_group g on g.group_id = gu.group_id where g.group_code = ?", Long.class, ADMIN_GROUP_CODE); } /** * 查资产卡片的主表单ID(消息里打开卡片用) * @return 表单ID,没有返回null */ public Long findCardFormId() { List ids = jdbcTemplate.queryForList("select form_id from cnf_form where main_table_name = 'BUS_ASSET_CARD' order by enable_set_main_form desc, sort_num", Long.class); return ids.isEmpty() ? null : ids.get(0); } /** * 新增提醒记录 * @param row 提醒字段(大写列名),不含ID */ public void insertRemind(Map row) { List columns = new ArrayList<>(); List args = new ArrayList<>(); columns.add("ID"); args.add(IdGenerater.getId()); for (Map.Entry entry : row.entrySet()) { columns.add(safe(entry.getKey())); args.add(entry.getValue()); } jdbcTemplate.update("insert into BUS_ASSET_REMIND (" + String.join(",", columns) + ", CREATE_USER_NAME, CREATE_TIME, MODIFY_TIME) values (" + String.join(",", Collections.nCopies(columns.size(), "?")) + ", '" + SYSTEM_SENDER + "', now(), now())", args.toArray()); } /** * 按列更新提醒记录 * @param id 提醒ID * @param values 列名到值 */ public void updateRemind(Long id, Map values) { StringBuilder sql = new StringBuilder("update BUS_ASSET_REMIND set "); List args = new ArrayList<>(); for (Map.Entry entry : values.entrySet()) { sql.append(safe(entry.getKey())).append(" = ?, "); args.add(entry.getValue()); } sql.append("MODIFY_TIME = now() where ID = ?"); args.add(id); jdbcTemplate.update(sql.toString(), args.toArray()); } /** * 发一条系统消息;接收人姓名为空时按用户ID查 * @param receiverId 接收人ID * @param receiverName 接收人姓名,可空 * @param content 消息内容 * @param url 点击消息打开的地址 */ public void insertMessage(Long receiverId, String receiverName, String content, String url) { jdbcTemplate.update("insert into sys_message (message_id, send_user, send_user_id, message_content, receive_user, receive_user_id, send_time, is_read, url, open_type) " + "values (?, ?, ?, ?, coalesce(nullif(?, ''), (select true_name from sys_account where user_id = ?)), ?, now(), 0, ?, 0)", IdGenerater.getId(), SYSTEM_SENDER, SYSTEM_SENDER_ID, content, receiverName, receiverId, receiverId, url); } private static String safe(String column) { if (!SAFE_COLUMN.matcher(column).matches()) { throw new IllegalArgumentException("非法列名:" + column); } return column; } }