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<String, Object> findBill(String table, Long id) {
|
List<Map<String, Object>> 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<String, Object> values) {
|
StringBuilder sql = new StringBuilder("update ").append(safeName(table)).append(" set ");
|
List<Object> args = new ArrayList<>();
|
for (Map.Entry<String, Object> 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<Map<String, Object>> findCards(List<Long> 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<Long> ids, String billNo, List<String> allowedStates) {
|
List<Object> 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<Long, String> findBeforeStates(String billNo, String changeType) {
|
Map<Long, String> result = new HashMap<>();
|
for (Map<String, Object> 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<Map<String, Object>> findCandidates(List<String> allowedStates, String keyword, String billNo, int limit) {
|
List<Object> 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<String> 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<String> 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<String> 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;
|
}
|
}
|