package zbooksoft.fams.dao;
|
|
import com.ruili.wcp.common.IdGenerater;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.jdbc.core.ConnectionCallback;
|
import org.springframework.jdbc.core.JdbcTemplate;
|
import org.springframework.stereotype.Repository;
|
|
import java.sql.PreparedStatement;
|
import java.sql.ResultSet;
|
import java.util.ArrayList;
|
import java.util.List;
|
import java.util.Map;
|
import java.util.regex.Pattern;
|
|
/**
|
* 资产底座(类别、位置、卡片、变动记录、编号流水)数据访问
|
* @author ycq
|
* @date 2026-10-16
|
*/
|
@Repository
|
public class AssetBaseDao {
|
|
private static final String CATEGORY_COLUMNS = "ID, PARENT_ID, CATEGORY_LEVEL, CATEGORY_CODE, CATEGORY_NAME, TOP_CATEGORY_ID, "
|
+ "TOP_CATEGORY_CODE, DEVICE_TYPE, WARRANTY_MONTHS, MAINTAIN_MONTHS, VERIFY_MONTHS, ENABLE_STATE";
|
|
private static final Pattern SAFE_COLUMN = Pattern.compile("^[A-Z_]+$");
|
|
@Autowired
|
private JdbcTemplate jdbcTemplate;
|
|
/**
|
* 按主键查资产类别
|
* @param id 类别ID
|
* @return 类别行,不存在返回null
|
*/
|
public Map<String, Object> findCategory(Long id) {
|
List<Map<String, Object>> rows = jdbcTemplate.queryForList(
|
"select " + CATEGORY_COLUMNS + " from BUS_ASSET_CATEGORY where ID = ?", id);
|
return rows.isEmpty() ? null : rows.get(0);
|
}
|
|
/**
|
* 判断类别下是否有子类别
|
* @param id 类别ID
|
* @return 有子类别返回true
|
*/
|
public boolean hasChildCategory(Long id) {
|
Integer count = jdbcTemplate.queryForObject("select count(1) from BUS_ASSET_CATEGORY where PARENT_ID = ?", Integer.class, id);
|
return count != null && count > 0;
|
}
|
|
/**
|
* 判断一级类别码是否已被其他一级类别占用
|
* @param code 类别码
|
* @param excludeId 当前类别ID,新增时传0
|
* @return 已占用返回true
|
*/
|
public boolean existsTopCategoryCode(String code, Long excludeId) {
|
Integer count = jdbcTemplate.queryForObject(
|
"select count(1) from BUS_ASSET_CATEGORY where CATEGORY_LEVEL = 1 and upper(CATEGORY_CODE) = ? and ID <> ?",
|
Integer.class, code, excludeId);
|
return count != null && count > 0;
|
}
|
|
/**
|
* 一级类别保存后回填自身的一级类别ID,并把一级类别码同步给所有下级
|
* @param topId 一级类别ID
|
* @param code 一级类别码
|
*/
|
public void syncTopCategory(Long topId, String code) {
|
jdbcTemplate.update("update BUS_ASSET_CATEGORY set TOP_CATEGORY_ID = ?, TOP_CATEGORY_CODE = ? where ID = ?", topId, code, topId);
|
jdbcTemplate.update("update BUS_ASSET_CATEGORY set TOP_CATEGORY_CODE = ? where TOP_CATEGORY_ID = ? and ID <> ?", code, topId, topId);
|
}
|
|
/**
|
* 按主键查存放位置
|
* @param id 位置ID
|
* @return 位置行,不存在返回null
|
*/
|
public Map<String, Object> findLocation(Long id) {
|
List<Map<String, Object>> rows = jdbcTemplate.queryForList(
|
"select ID, PARENT_ID, LOCATION_NAME, FULL_NAME from BUS_ASSET_LOCATION where ID = ?", id);
|
return rows.isEmpty() ? null : rows.get(0);
|
}
|
|
/**
|
* 查直接下级位置
|
* @param parentId 上级位置ID
|
* @return 下级位置列表
|
*/
|
public List<Map<String, Object>> findChildLocations(Long parentId) {
|
return jdbcTemplate.queryForList("select ID, LOCATION_NAME from BUS_ASSET_LOCATION where PARENT_ID = ?", parentId);
|
}
|
|
/**
|
* 更新位置的上级名称与完整位置
|
* @param id 位置ID
|
* @param parentName 上级完整位置
|
* @param fullName 本级完整位置
|
*/
|
public void updateLocationFullName(Long id, String parentName, String fullName) {
|
jdbcTemplate.update("update BUS_ASSET_LOCATION set PARENT_NAME = ?, FULL_NAME = ? where ID = ?", parentName, fullName, id);
|
}
|
|
/**
|
* 同步资产卡片上冗余的存放位置名称
|
* @param locationId 位置ID
|
* @param fullName 完整位置
|
*/
|
public void updateCardLocationName(Long locationId, String fullName) {
|
jdbcTemplate.update("update BUS_ASSET_CARD set LOCATION_NAME = ? where LOCATION_ID = ?", fullName, locationId);
|
}
|
|
/**
|
* 取一级类别码的下一个流水号;用MySQL的upsert + LAST_INSERT_ID在同一连接内原子取号,并发新增不会拿到重复号
|
* @param categoryCode 一级类别码
|
* @return 新流水号,从1开始
|
*/
|
public long nextSerialNo(final String categoryCode) {
|
return jdbcTemplate.execute((ConnectionCallback<Long>) connection -> {
|
try (PreparedStatement upsert = connection.prepareStatement(
|
"insert into BUS_ASSET_SN (ID, CATEGORY_CODE, CURRENT_SN, MODIFY_TIME) values (?, ?, LAST_INSERT_ID(1), now()) "
|
+ "on duplicate key update CURRENT_SN = LAST_INSERT_ID(CURRENT_SN + 1), MODIFY_TIME = now()")) {
|
upsert.setLong(1, IdGenerater.getId());
|
upsert.setString(2, categoryCode);
|
upsert.executeUpdate();
|
}
|
try (PreparedStatement query = connection.prepareStatement("select LAST_INSERT_ID()");
|
ResultSet rs = query.executeQuery()) {
|
rs.next();
|
return rs.getLong(1);
|
}
|
});
|
}
|
|
/**
|
* 判断资产编号是否已存在
|
* @param assetNo 资产编号
|
* @return 已存在返回true
|
*/
|
public boolean existsAssetNo(String assetNo) {
|
Integer count = jdbcTemplate.queryForObject("select count(1) from BUS_ASSET_CARD where ASSET_NO = ?", Integer.class, assetNo);
|
return count != null && count > 0;
|
}
|
|
/**
|
* 查还没有资产编号的卡片(Excel导入后待补全),按创建时间顺序
|
* @param limit 最多返回条数
|
* @return 卡片行
|
*/
|
public List<Map<String, Object>> findCardsWithoutAssetNo(int limit) {
|
return jdbcTemplate.queryForList("select ID, ASSET_NAME, CATEGORY_ID, CATEGORY_NAME, DEVICE_TYPE, ORIGINAL_VALUE, PURCHASE_DATE, "
|
+ "SOURCE_TYPE, SOURCE_BILL_ID, SOURCE_BILL_NO, ASSET_STATE, USE_DEPT_ID, USE_DEPT_NAME, USER_ID, USER_NAME, "
|
+ "LOCATION_ID, LOCATION_NAME, START_USE_DATE, WARRANTY_EXPIRE_DATE, NEXT_MAINTAIN_DATE, SAFETY_USER_ID, "
|
+ "SAFETY_USER_NAME, RISK_LEVEL, REMARK from BUS_ASSET_CARD "
|
+ "where (ASSET_NO is null or ASSET_NO = '') and ifnull(DELETE_STATE, 0) = 0 order by CREATE_TIME, ID limit ?", limit);
|
}
|
|
/**
|
* 按名称查资产类别,附带子类别数用于判断是否末级
|
* @param name 类别名称
|
* @return 同名类别列表
|
*/
|
public List<Map<String, Object>> findCategoriesByName(String name) {
|
return jdbcTemplate.queryForList("select c.ID, c.CATEGORY_NAME, "
|
+ "(select count(1) from BUS_ASSET_CATEGORY s where s.PARENT_ID = c.ID) as CHILD_COUNT "
|
+ "from BUS_ASSET_CATEGORY c where c.CATEGORY_NAME = ?", name);
|
}
|
|
/**
|
* 按部门名称查部门ID
|
* @param deptName 部门名称
|
* @return 同名部门ID列表
|
*/
|
public List<Long> findDeptIdsByName(String deptName) {
|
return jdbcTemplate.queryForList("select dept_id from sys_dept where dept_name = ?", Long.class, deptName);
|
}
|
|
/**
|
* 按姓名查用户ID
|
* @param trueName 姓名
|
* @return 同名用户ID列表
|
*/
|
public List<Long> findUserIdsByTrueName(String trueName) {
|
return jdbcTemplate.queryForList("select user_id from sys_account where true_name = ?", Long.class, trueName);
|
}
|
|
/**
|
* 按完整位置或位置名称查存放位置
|
* @param name 完整位置或位置名称
|
* @return 匹配的位置列表
|
*/
|
public List<Map<String, Object>> findLocationsByName(String name) {
|
return jdbcTemplate.queryForList(
|
"select ID, LOCATION_NAME, FULL_NAME from BUS_ASSET_LOCATION where FULL_NAME = ? or LOCATION_NAME = ?", name, name);
|
}
|
|
/**
|
* 按列更新资产卡片,列名只允许来自调用方的固定白名单
|
* @param id 卡片ID
|
* @param values 列名(大写)到值的映射
|
*/
|
public void updateCard(Long id, Map<String, Object> values) {
|
if (values.isEmpty()) {
|
return;
|
}
|
StringBuilder sql = new StringBuilder("update BUS_ASSET_CARD set ");
|
List<Object> args = new ArrayList<>();
|
for (Map.Entry<String, Object> entry : values.entrySet()) {
|
if (!SAFE_COLUMN.matcher(entry.getKey()).matches()) {
|
throw new IllegalArgumentException("非法列名:" + entry.getKey());
|
}
|
sql.append(args.isEmpty() ? "" : ", ").append(entry.getKey()).append(" = ?");
|
Object value = entry.getValue();
|
args.add(value instanceof String && ((String) value).isEmpty() ? null : value);
|
}
|
sql.append(", MODIFY_TIME = now() where ID = ?");
|
args.add(id);
|
jdbcTemplate.update(sql.toString(), args.toArray());
|
}
|
|
/**
|
* 新增一张资产卡片(程序生成场景,如采购入库),列名只允许大写字母和下划线
|
* @param card 卡片字段(大写列名),不含ID
|
* @return 新卡片ID
|
*/
|
public Long insertCard(Map<String, Object> card) {
|
long id = IdGenerater.getId();
|
List<String> columns = new ArrayList<>();
|
List<Object> args = new ArrayList<>();
|
columns.add("ID");
|
args.add(id);
|
for (Map.Entry<String, Object> entry : card.entrySet()) {
|
if (!SAFE_COLUMN.matcher(entry.getKey()).matches()) {
|
throw new IllegalArgumentException("非法列名:" + entry.getKey());
|
}
|
if ("ID".equals(entry.getKey())) {
|
continue;
|
}
|
Object value = entry.getValue();
|
columns.add(entry.getKey());
|
args.add(value instanceof String && ((String) value).isEmpty() ? null : value);
|
}
|
String placeholders = String.join(",", java.util.Collections.nCopies(columns.size(), "?"));
|
jdbcTemplate.update("insert into BUS_ASSET_CARD (" + String.join(",", columns) + ", CREATE_TIME, MODIFY_TIME) values ("
|
+ placeholders + ", now(), now())", args.toArray());
|
return id;
|
}
|
|
/**
|
* 判断资产是否已有某类变动记录
|
* @param assetId 资产ID
|
* @param changeType 变动类型
|
* @return 已存在返回true
|
*/
|
public boolean existsChangeLog(Long assetId, String changeType) {
|
Integer count = jdbcTemplate.queryForObject(
|
"select count(1) from BUS_ASSET_CHANGE_LOG where ASSET_ID = ? and CHANGE_TYPE = ?", Integer.class, assetId, changeType);
|
return count != null && count > 0;
|
}
|
|
/**
|
* 写一条资产变动记录
|
* @param row 变动记录字段(大写列名)
|
*/
|
public void insertChangeLog(Map<String, Object> row) {
|
jdbcTemplate.update("insert into BUS_ASSET_CHANGE_LOG (ID, ASSET_ID, ASSET_NO, ASSET_NAME, CHANGE_TYPE, BILL_TYPE, BILL_ID, BILL_NO, "
|
+ "BEFORE_STATE, AFTER_STATE, BEFORE_CONTENT, AFTER_CONTENT, OPERATE_USER_ID, OPERATE_USER_NAME, OPERATE_TIME, "
|
+ "CREATE_USER_ID, CREATE_USER_NAME, CREATE_TIME, MODIFY_TIME) "
|
+ "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), ?, ?, now(), now())",
|
IdGenerater.getId(), row.get("ASSET_ID"), row.get("ASSET_NO"), row.get("ASSET_NAME"), row.get("CHANGE_TYPE"),
|
row.get("BILL_TYPE"), row.get("BILL_ID"), row.get("BILL_NO"), row.get("BEFORE_STATE"), row.get("AFTER_STATE"),
|
row.get("BEFORE_CONTENT"), row.get("AFTER_CONTENT"), row.get("OPERATE_USER_ID"), row.get("OPERATE_USER_NAME"),
|
row.get("OPERATE_USER_ID"), row.get("OPERATE_USER_NAME"));
|
}
|
}
|