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 findCategory(Long id) { List> 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 findLocation(Long id) { List> 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> 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) 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> 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> 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 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 findUserIdsByTrueName(String trueName) { return jdbcTemplate.queryForList("select user_id from sys_account where true_name = ?", Long.class, trueName); } /** * 按完整位置或位置名称查存放位置 * @param name 完整位置或位置名称 * @return 匹配的位置列表 */ public List> 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 values) { if (values.isEmpty()) { return; } StringBuilder sql = new StringBuilder("update BUS_ASSET_CARD set "); List args = new ArrayList<>(); for (Map.Entry 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 card) { long id = IdGenerater.getId(); List columns = new ArrayList<>(); List args = new ArrayList<>(); columns.add("ID"); args.add(id); for (Map.Entry 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 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")); } }