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.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 PurchaseDao {
|
|
public static final String PURCHASE_TABLE = "BUS_PURCHASE_APPLY";
|
public static final String PURCHASE_ITEM_TABLE = "BUS_PURCHASE_APPLY_ITEM";
|
public static final String RECEIPT_TABLE = "BUS_ASSET_RECEIPT";
|
public static final String RECEIPT_ITEM_TABLE = "BUS_ASSET_RECEIPT_ITEM";
|
|
private static final Pattern SAFE_COLUMN = Pattern.compile("^[A-Z_]+$");
|
|
@Autowired
|
private JdbcTemplate jdbcTemplate;
|
|
/**
|
* 按主键查采购申请
|
* @param id 采购申请ID
|
* @return 采购申请行,不存在返回null
|
*/
|
public Map<String, Object> findPurchase(Long id) {
|
return first(jdbcTemplate.queryForList("select * from " + PURCHASE_TABLE + " where ID = ?", id));
|
}
|
|
/**
|
* 查采购申请的明细,按创建顺序
|
* @param purchaseId 采购申请ID
|
* @return 明细行
|
*/
|
public List<Map<String, Object>> findPurchaseItems(Long purchaseId) {
|
return jdbcTemplate.queryForList("select * from " + PURCHASE_ITEM_TABLE + " where APPLY_ID = ? order by CREATE_TIME, ID", purchaseId);
|
}
|
|
/**
|
* 按主键查采购明细
|
* @param id 明细ID
|
* @return 明细行,不存在返回null
|
*/
|
public Map<String, Object> findPurchaseItem(Long id) {
|
return first(jdbcTemplate.queryForList("select * from " + PURCHASE_ITEM_TABLE + " where ID = ?", id));
|
}
|
|
/**
|
* 按列更新采购申请
|
* @param id 采购申请ID
|
* @param values 列名到值
|
*/
|
public void updatePurchase(Long id, Map<String, Object> values) {
|
update(PURCHASE_TABLE, id, values);
|
}
|
|
/**
|
* 采购明细累加已到货数量
|
* @param itemId 采购明细ID
|
* @param quantity 本次到货数量
|
*/
|
public void addReceivedQty(Long itemId, int quantity) {
|
jdbcTemplate.update("update " + PURCHASE_ITEM_TABLE + " set RECEIVED_QTY = ifnull(RECEIVED_QTY, 0) + ?, MODIFY_TIME = now() where ID = ?",
|
quantity, itemId);
|
}
|
|
/**
|
* 删除采购申请下的全部明细
|
* @param purchaseId 采购申请ID
|
*/
|
public void deletePurchaseItems(Long purchaseId) {
|
jdbcTemplate.update("delete from " + PURCHASE_ITEM_TABLE + " where APPLY_ID = ?", purchaseId);
|
}
|
|
/**
|
* 按主键查到货入库单
|
* @param id 入库单ID
|
* @return 入库单行,不存在返回null
|
*/
|
public Map<String, Object> findReceipt(Long id) {
|
return first(jdbcTemplate.queryForList("select * from " + RECEIPT_TABLE + " where ID = ?", id));
|
}
|
|
/**
|
* 查入库单明细
|
* @param receiptId 入库单ID
|
* @return 明细行
|
*/
|
public List<Map<String, Object>> findReceiptItems(Long receiptId) {
|
return jdbcTemplate.queryForList("select * from " + RECEIPT_ITEM_TABLE + " where RECEIPT_ID = ? order by CREATE_TIME, ID", receiptId);
|
}
|
|
/**
|
* 统计入库单明细条数
|
* @param receiptId 入库单ID
|
* @return 条数
|
*/
|
public int countReceiptItems(Long receiptId) {
|
Integer count = jdbcTemplate.queryForObject("select count(1) from " + RECEIPT_ITEM_TABLE + " where RECEIPT_ID = ?", Integer.class, receiptId);
|
return count == null ? 0 : count;
|
}
|
|
/**
|
* 按主键查入库明细
|
* @param id 明细ID
|
* @return 明细行,不存在返回null
|
*/
|
public Map<String, Object> findReceiptItem(Long id) {
|
return first(jdbcTemplate.queryForList("select * from " + RECEIPT_ITEM_TABLE + " where ID = ?", id));
|
}
|
|
/**
|
* 新增入库明细
|
* @param row 明细字段(大写列名),不含ID
|
*/
|
public void insertReceiptItem(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 " + RECEIPT_ITEM_TABLE + " (" + String.join(",", columns) + ", CREATE_TIME, MODIFY_TIME) values ("
|
+ String.join(",", Collections.nCopies(columns.size(), "?")) + ", now(), now())", args.toArray());
|
}
|
|
/**
|
* 按列更新入库单
|
* @param id 入库单ID
|
* @param values 列名到值
|
*/
|
public void updateReceipt(Long id, Map<String, Object> values) {
|
update(RECEIPT_TABLE, id, values);
|
}
|
|
/**
|
* 按列更新入库明细
|
* @param id 明细ID
|
* @param values 列名到值
|
*/
|
public void updateReceiptItem(Long id, Map<String, Object> values) {
|
update(RECEIPT_ITEM_TABLE, id, values);
|
}
|
|
/**
|
* 删除入库单下的全部明细
|
* @param receiptId 入库单ID
|
*/
|
public void deleteReceiptItems(Long receiptId) {
|
jdbcTemplate.update("delete from " + RECEIPT_ITEM_TABLE + " where RECEIPT_ID = ?", receiptId);
|
}
|
|
private void update(String table, Long id, Map<String, Object> values) {
|
if (values.isEmpty()) {
|
return;
|
}
|
StringBuilder sql = new StringBuilder("update ").append(table).append(" 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());
|
}
|
|
private static String safe(String column) {
|
if (!SAFE_COLUMN.matcher(column).matches()) {
|
throw new IllegalArgumentException("非法列名:" + column);
|
}
|
return column;
|
}
|
|
private static Map<String, Object> first(List<Map<String, Object>> rows) {
|
return rows.isEmpty() ? null : rows.get(0);
|
}
|
}
|