食堂采购系统源码数据库表结构与库存算法实现详解

CREATE TABLE goods (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  category_id BIGINT,
  unit VARCHAR(20),
  spec VARCHAR(100),
  enabled TINYINT DEFAULT 1,
  created_at DATETIME
);

CREATE TABLE supplier (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(200),
  contact VARCHAR(50),
  phone VARCHAR(20),
  status TINYINT DEFAULT 1
);

CREATE TABLE warehouse (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100),
  location VARCHAR(200)
);

CREATE TABLE inventory (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  goods_id BIGINT,
  warehouse_id BIGINT,
  quantity DECIMAL(10,2) DEFAULT 0,
  amount DECIMAL(12,2) DEFAULT 0,
  version INT DEFAULT 0,
  UNIQUE KEY uk_goods_wh(goods_id, warehouse_id)
);

CREATE TABLE inventory_log (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  goods_id BIGINT,
  warehouse_id BIGINT,
  type VARCHAR(20),
  quantity DECIMAL(10,2),
  price DECIMAL(10,2),
  amount DECIMAL(12,2),
  ref_no VARCHAR(50),
  created_at DATETIME
);

@Transactional
public void stockIn(Long goodsId, Long warehouseId,
                    BigDecimal qty, BigDecimal price) {

    BigDecimal amount = qty.multiply(price);

    // 1 写流水
    inventoryLogMapper.insert(new InventoryLog(
            goodsId, warehouseId, "IN", qty, price, amount
    ));

    // 2 更新库存
    inventoryMapper.addStock(goodsId, warehouseId, qty, amount);
}

UPDATE inventory
SET quantity = quantity + #{qty},
    amount = amount + #{amount},
    version = version + 1
WHERE goods_id = #{goodsId}
AND warehouse_id = #{warehouseId};

UPDATE inventory
SET quantity = quantity - #{qty},
    amount = amount - #{amount},
    version = version + 1
WHERE goods_id = #{goodsId}
AND warehouse_id = #{warehouseId}
AND quantity >= #{qty}
AND version = #{version};

@Transactional
public void stockOut(Long goodsId, Long warehouseId,
                     BigDecimal qty) {

    Inventory inv = inventoryMapper.select(goodsId, warehouseId);

    if (inv.getQuantity().compareTo(qty) < 0) {
        throw new RuntimeException("库存不足");
    }

    BigDecimal avgPrice =
            inv.getAmount().divide(inv.getQuantity(), 2, RoundingMode.HALF_UP);

    BigDecimal amount = avgPrice.multiply(qty);

    int rows = inventoryMapper.reduceStock(
            goodsId, warehouseId, qty, amount, inv.getVersion());

    if (rows == 0) {
        throw new RuntimeException("库存并发冲突，请重试");
    }

    inventoryLogMapper.insert(
            new InventoryLog(goodsId, warehouseId, "OUT", qty, avgPrice, amount)
    );
}

amount / quantity