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 findPurchase(Long id) { return first(jdbcTemplate.queryForList("select * from " + PURCHASE_TABLE + " where ID = ?", id)); } /** * 查采购申请的明细,按创建顺序 * @param purchaseId 采购申请ID * @return 明细行 */ public List> 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 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 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 findReceipt(Long id) { return first(jdbcTemplate.queryForList("select * from " + RECEIPT_TABLE + " where ID = ?", id)); } /** * 查入库单明细 * @param receiptId 入库单ID * @return 明细行 */ public List> 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 findReceiptItem(Long id) { return first(jdbcTemplate.queryForList("select * from " + RECEIPT_ITEM_TABLE + " where ID = ?", id)); } /** * 新增入库明细 * @param row 明细字段(大写列名),不含ID */ public void insertReceiptItem(Map row) { List columns = new ArrayList<>(); List args = new ArrayList<>(); columns.add("ID"); args.add(IdGenerater.getId()); for (Map.Entry 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 values) { update(RECEIPT_TABLE, id, values); } /** * 按列更新入库明细 * @param id 明细ID * @param values 列名到值 */ public void updateReceiptItem(Long id, Map 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 values) { if (values.isEmpty()) { return; } StringBuilder sql = new StringBuilder("update ").append(table).append(" set "); List args = new ArrayList<>(); for (Map.Entry 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 first(List> rows) { return rows.isEmpty() ? null : rows.get(0); } }