package zbooksoft.fams.dao; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Repository; import java.util.ArrayList; import java.util.Collections; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.regex.Pattern; /** * 资产使用流程单据(借用、调配、闲置退还、封存解封、维修、报废)与卡片加锁的数据访问 * @author ycq * @date 2026-10-16 */ @Repository public class AssetApplyDao { private static final Pattern SAFE_NAME = Pattern.compile("^[A-Za-z_]+$"); private static final String CARD_COLUMNS = "ID, ASSET_NO, ASSET_NAME, SPEC_MODEL, ASSET_STATE, LOCK_STATE, LOCK_BILL_NO, USE_DEPT_ID, " + "USE_DEPT_NAME, USER_ID, USER_NAME, LOCATION_ID, LOCATION_NAME"; @Autowired private JdbcTemplate jdbcTemplate; /** * 按主键查单据 * @param table 单据表名(来自单据类型枚举) * @param id 单据ID * @return 单据行,不存在返回null */ public Map findBill(String table, Long id) { List> rows = jdbcTemplate.queryForList("select * from " + safeName(table) + " where ID = ?", id); return rows.isEmpty() ? null : rows.get(0); } /** * 按列更新单据 * @param table 单据表名 * @param id 单据ID * @param values 列名到值 */ public void updateBill(String table, Long id, Map values) { StringBuilder sql = new StringBuilder("update ").append(safeName(table)).append(" set "); List args = new ArrayList<>(); for (Map.Entry entry : values.entrySet()) { sql.append(safeName(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 ids 资产ID * @return 卡片行,按资产编号排序 */ public List> findCards(List ids) { if (ids.isEmpty()) { return Collections.emptyList(); } return jdbcTemplate.queryForList("select " + CARD_COLUMNS + " from BUS_ASSET_CARD where ID in (" + placeholders(ids.size()) + ") and ifnull(DELETE_STATE, 0) = 0 order by ASSET_NO", ids.toArray()); } /** * 给资产加在途锁:只锁状态允许、且未锁定或已被本单锁定的资产 * @param ids 资产ID * @param billNo 单据编号 * @param allowedStates 允许的资产状态 * @return 满足条件的资产数(MySQL驱动默认返回匹配行数,已被本单锁定的也计入) */ public int lockCards(List ids, String billNo, List allowedStates) { List args = new ArrayList<>(); args.add(billNo); args.addAll(ids); args.addAll(allowedStates); args.add(billNo); return jdbcTemplate.update("update BUS_ASSET_CARD set LOCK_STATE = 1, LOCK_BILL_NO = ? where ID in (" + placeholders(ids.size()) + ") and ASSET_STATE in (" + placeholders(allowedStates.size()) + ") and (ifnull(LOCK_STATE, 0) = 0 or LOCK_BILL_NO = ?)", args.toArray()); } /** * 解除单据对资产的在途锁 * @param billNo 单据编号 */ public void unlockCards(String billNo) { jdbcTemplate.update("update BUS_ASSET_CARD set LOCK_STATE = 0, LOCK_BILL_NO = null where LOCK_BILL_NO = ?", billNo); } /** * 查单据某类变动记录里各资产的变动前状态(维修完成时恢复用) * @param billNo 单据编号 * @param changeType 变动类型 * @return 资产ID到变动前状态 */ public Map findBeforeStates(String billNo, String changeType) { Map result = new HashMap<>(); for (Map row : jdbcTemplate.queryForList( "select ASSET_ID, BEFORE_STATE from BUS_ASSET_CHANGE_LOG where BILL_NO = ? and CHANGE_TYPE = ?", billNo, changeType)) { result.put(((Number) row.get("ASSET_ID")).longValue(), (String) row.get("BEFORE_STATE")); } return result; } /** * 查可选资产:状态允许、未被其他单据锁定,可按关键字过滤 * @param allowedStates 允许的资产状态 * @param keyword 编号/名称/规格/使用人/位置关键字,可空 * @param billNo 当前单据编号(被本单锁定的资产也可选),可空 * @param limit 最多返回条数 * @return 资产行 */ public List> findCandidates(List allowedStates, String keyword, String billNo, int limit) { List args = new ArrayList<>(allowedStates); StringBuilder sql = new StringBuilder("select " + CARD_COLUMNS + " from BUS_ASSET_CARD where ifnull(DELETE_STATE, 0) = 0 " + "and ifnull(ASSET_NO, '') <> '' and ASSET_STATE in (" + placeholders(allowedStates.size()) + ") " + "and (ifnull(LOCK_STATE, 0) = 0 or LOCK_BILL_NO = ?)"); args.add(billNo == null ? "" : billNo); if (keyword != null && !keyword.isEmpty()) { String like = "%" + keyword + "%"; sql.append(" and (ASSET_NO like ? or ASSET_NAME like ? or SPEC_MODEL like ? or USER_NAME like ? or LOCATION_NAME like ? or USE_DEPT_NAME like ?)"); for (int i = 0; i < 6; i++) { args.add(like); } } sql.append(" order by ASSET_NO limit ?"); args.add(limit); return jdbcTemplate.queryForList(sql.toString(), args.toArray()); } /** * 按平台表ID查表名(流程监听器用) * @param tableId cnf_table主键 * @return 表名,不存在返回null */ public String findTableName(Long tableId) { List names = jdbcTemplate.queryForList("select table_name from cnf_table where table_id = ?", String.class, tableId); return names.isEmpty() ? null : names.get(0); } /** * 按视图ID查主表名(列表删除/按钮事件用) * @param viewId 视图ID * @return 主表名,不存在返回null */ public String findViewTableName(Long viewId) { List names = jdbcTemplate.queryForList("select main_table_name from cnf_view where view_id = ?", String.class, viewId); return names.isEmpty() ? null : names.get(0); } /** * 按表单ID查主表名(表单保存事件用) * @param formId 表单ID * @return 主表名,不存在返回null */ public String findFormTableName(Long formId) { List names = jdbcTemplate.queryForList("select main_table_name from cnf_form where form_id = ?", String.class, formId); return names.isEmpty() ? null : names.get(0); } private static String placeholders(int count) { return String.join(",", Collections.nCopies(count, "?")); } private static String safeName(String name) { if (name == null || !SAFE_NAME.matcher(name).matches()) { throw new IllegalArgumentException("非法表名或列名:" + name); } return name; } }