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<String> 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<Map<String, Object>> findDueItems(LocalDate until) {
|
StringBuilder sql = new StringBuilder();
|
List<Object> 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<Map<String, Object>> 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<String, Object> findRemind(Long id) {
|
List<Map<String, Object>> 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<Long> 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<Long> 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<String, Object> row) {
|
List<String> columns = new ArrayList<>();
|
List<Object> args = new ArrayList<>();
|
columns.add("ID");
|
args.add(IdGenerater.getId());
|
for (Map.Entry<String, Object> 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<String, Object> values) {
|
StringBuilder sql = new StringBuilder("update BUS_ASSET_REMIND set ");
|
List<Object> args = new ArrayList<>();
|
for (Map.Entry<String, Object> 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;
|
}
|
}
|