MYSQL 盘点功能加批次

为了实现一个批次功能，我们需要在现有的库存和盘点系统中添加批次管理。批次管理可以帮助我们跟踪不同批次的商品，例如生产日期、过期日期等。我们将添加一个新的表 base_batch 来存储批次信息，并在 base_inventory 和 base_inventory_count 表中添加批次ID字段以关联批次信息。
表结构示例
base_batch 表结构
id (批次ID)
goods_id (商品ID)
batch_number (批次号)
production_date (生产日期)
expiration_date (过期日期)
base_inventory 表结构
id (库存ID)
goods_id (商品ID)
batch_id (批次ID)
quantity (库存数量)
location (库存位置)
base_inventory_count 表结构
id (盘点记录ID)
inventory_id (库存ID)
counted_quantity (盘点数量)
count_date (盘点日期)
status (盘点状态，例如 'pending', 'completed', 'discrepancy')
SQL 表创建示例

创建 base_batch 表

CREATE TABLE base_batch (
    id INT AUTO_INCREMENT PRIMARY KEY,
    goods_id INT NOT NULL,
    batch_number VARCHAR(50) NOT NULL,
    production_date DATE NOT NULL,
    expiration_date DATE,
    FOREIGN KEY (goods_id) REFERENCES base_goods(id)
);

修改 base_inventory 表

ALTER TABLE base_inventory
ADD COLUMN batch_id INT,
ADD CONSTRAINT fk_batch_id FOREIGN KEY (batch_id) REFERENCES base_batch(id);

修改 base_inventory_count 表

ALTER TABLE base_inventory_count
ADD COLUMN batch_id INT,
ADD CONSTRAINT fk_batch_id_count FOREIGN KEY (batch_id) REFERENCES base_batch(id);

控制器方法实现
我们需要在控制器中实现以下功能：
获取批次列表：用于选择需要盘点的批次。
添加批次记录：记录新的批次信息。
获取库存列表：用于选择需要盘点的商品和批次。
添加盘点记录：记录盘点数量和状态，关联批次信息。
查看盘点记录：查看所有盘点记录及其状态，包括批次信息。
1. 获取批次列表

public function getBatchList()
{
    if (Request::isPost()) {
        $data = Request::post(['limit', 'page']);
        try {
            validate(BatchValidate::class)->check($data);
        } catch (ValidateException $exception) {
            $judge = $exception->getMessage();
            return $this->show(
                config('status.error'),
                config('status.error'),
                $judge
            );
        }

        $page = isset($data['page']) ? $data['page'] : 1;
        $limit = isset($data['limit']) ? $data['limit'] : 10;
        $offset = ($page - 1) * $limit;

        $batchData = Db::name('base_batch')
            ->alias('b')
            ->join('base_goods g', 'b.goods_id = g.id')
            ->field('b.id as batch_id, g.name as goods_name, b.batch_number, b.production_date, b.expiration_date')
            ->limit($limit)
            ->offset($offset)
            ->select();

        // 获取总记录数
        $total = Db::name('base_batch')
            ->alias('b')
            ->join('base_goods g', 'b.goods_id = g.id')
            ->count();

        // 返回结果
        $result = [
            'code' => 0,
            'msg' => '',
            'count' => $total,
            'data' => $batchData
        ];

        return json($result);
    }

    $data['url'] = request()->pathinfo();
    View::assign(['data' => $data]);
    return View::fetch();
}


2. 添加批次记录

public function addBatch()
{
    if (Request::isPost()) {
        $data = Request::post(['goods_id', 'batch_number', 'production_date', 'expiration_date']);
        try {
            validate(BatchValidate::class)->check($data);
        } catch (ValidateException $exception) {
            $judge = $exception->getMessage();
            return $this->show(
                config('status.error'),
                config('status.error'),
                $judge
            );
        }

        $batchData = [
            'goods_id' => $data['goods_id'],
            'batch_number' => $data['batch_number'],
            'production_date' => $data['production_date'],
            'expiration_date' => $data['expiration_date']
        ];

        $result = Db::name('base_batch')->insert($batchData);

        if ($result) {
            return json([
                'code' => 0,
                'msg' => '批次记录添加成功'
            ]);
        } else {
            return json([
                'code' => 1,
                'msg' => '批次记录添加失败'
            ]);
        }
    }

    $data['url'] = request()->pathinfo();
    View::assign(['data' => $data]);
    return View::fetch();
}


3. 获取库存列表

public function getInventoryList()
{
    if (Request::isPost()) {
        $data = Request::post(['limit', 'page']);
        try {
            validate(InventoryValidate::class)->check($data);
        } catch (ValidateException $exception) {
            $judge = $exception->getMessage();
            return $this->show(
                config('status.error'),
                config('status.error'),
                $judge
            );
        }

        $page = isset($data['page']) ? $data['page'] : 1;
        $limit = isset($data['limit']) ? $data['limit'] : 10;
        $offset = ($page - 1) * $limit;

        $inventoryData = Db::name('base_inventory')
            ->alias('i')
            ->join('base_goods g', 'i.goods_id = g.id')
            ->join('base_batch b', 'i.batch_id = b.id')
            ->field('i.id as inventory_id, g.name as goods_name, b.batch_number, i.quantity as current_quantity, i.location')
            ->limit($limit)
            ->offset($offset)
            ->select();

        // 获取总记录数
        $total = Db::name('base_inventory')
            ->alias('i')
            ->join('base_goods g', 'i.goods_id = g.id')
            ->join('base_batch b', 'i.batch_id = b.id')
            ->count();

        // 返回结果
        $result = [
            'code' => 0,
            'msg' => '',
            'count' => $total,
            'data' => $inventoryData
        ];

        return json($result);
    }

    $data['url'] = request()->pathinfo();
    View::assign(['data' => $data]);
    return View::fetch();
}


4. 添加盘点记录

public function addInventoryCount()
{
    if (Request::isPost()) {
        $data = Request::post(['inventory_id', 'counted_quantity']);
        try {
            validate(InventoryCountValidate::class)->check($data);
        } catch (ValidateException $exception) {
            $judge = $exception->getMessage();
            return $this->show(
                config('status.error'),
                config('status.error'),
                $judge
            );
        }

        $inventoryId = $data['inventory_id'];
        $countedQuantity = $data['counted_quantity'];

        // 获取当前库存数量和批次ID
        $currentInventory = Db::name('base_inventory')
            ->where('id', $inventoryId)
            ->find();

        if (!$currentInventory) {
            return json([
                'code' => 1,
                'msg' => '库存记录不存在'
            ]);
        }

        $currentQuantity = $currentInventory['quantity'];
        $batchId = $currentInventory['batch_id'];

        // 计算差异
        $status = 'completed';
        if ($countedQuantity != $currentQuantity) {
            $status = 'discrepancy';
        }

        // 插入盘点记录
        $countData = [
            'inventory_id' => $inventoryId,
            'batch_id' => $batchId,
            'counted_quantity' => $countedQuantity,
            'status' => $status
        ];

        $result = Db::name('base_inventory_count')->insert($countData);

        if ($result) {
            return json([
                'code' => 0,
                'msg' => '盘点记录添加成功'
            ]);
        } else {
            return json([
                'code' => 1,
                'msg' => '盘点记录添加失败'
            ]);
        }
    }

    $data['url'] = request()->pathinfo();
    View::assign(['data' => $data]);
    return View::fetch();
}

5. 查看盘点记录

public function getInventoryCountList()
{
    if (Request::isPost()) {
        $data = Request::post(['limit', 'page']);
        try {
            validate(InventoryCountValidate::class)->check($data);
        } catch (ValidateException $exception) {
            $judge = $exception->getMessage();
            return $this->show(
                config('status.error'),
                config('status.error'),
                $judge
            );
        }

        $page = isset($data['page']) ? $data['page'] : 1;
        $limit = isset($data['limit']) ? $data['limit'] : 10;
        $offset = ($page - 1) * $limit;

        $countData = Db::name('base_inventory_count')
            ->alias('c')
            ->join('base_inventory i', 'c.inventory_id = i.id')
            ->join('base_goods g', 'i.goods_id = g.id')
            ->join('base_batch b', 'i.batch_id = b.id')
            ->field('c.id as count_id, g.name as goods_name, b.batch_number, i.quantity as current_quantity, c.counted_quantity, c.status, c.count_date')
            ->limit($limit)
            ->offset($offset)
            ->select();

        // 获取总记录数
        $total = Db::name('base_inventory_count')
            ->alias('c')
            ->join('base_inventory i', 'c.inventory_id = i.id')
            ->join('base_goods g', 'i.goods_id = g.id')
            ->join('base_batch b', 'i.batch_id = b.id')
            ->count();

        // 返回结果
        $result = [
            'code' => 0,
            'msg' => '',
            'count' => $total,
            'data' => $countData
        ];

        return json($result);
    }

    $data['url'] = request()->pathinfo();
    View::assign(['data' => $data]);
    return View::fetch();
}

验证规则示例

BatchValidate 验证规则

namespace app\common\validate;

use think\Validate;

class BatchValidate extends Validate
{
    protected $rule = [
        'goods_id' => 'require|integer|gt:0',
        'batch_number' => 'require|max:50',
        'production_date' => 'require|date',
        'expiration_date' => 'date',
        'limit' => 'require|integer|gt:0',
        'page' => 'require|integer|gt:0',
    ];

    protected $message = [
        'goods_id.require' => '商品ID不能为空',
        'goods_id.integer' => '商品ID必须是整数',
        'goods_id.gt' => '商品ID必须大于0',
        'batch_number.require' => '批次号不能为空',
        'batch_number.max' => '批次号长度不能超过50个字符',
        'production_date.require' => '生产日期不能为空',
        'production_date.date' => '生产日期格式不正确',
        'expiration_date.date' => '过期日期格式不正确',
        'limit.require' => '每页数量不能为空',
        'limit.integer' => '每页数量必须是整数',
        'limit.gt' => '每页数量必须大于0',
        'page.require' => '页码不能为空',
        'page.integer' => '页码必须是整数',
        'page.gt' => '页码必须大于0',
    ];
}


InventoryValidate 验证规则

namespace app\common\validate;

use think\Validate;

class InventoryValidate extends Validate
{
    protected $rule = [
        'limit' => 'require|integer|gt:0',
        'page' => 'require|integer|gt:0',
    ];

    protected $message = [
        'limit.require' => '每页数量不能为空',
        'limit.integer' => '每页数量必须是整数',
        'limit.gt' => '每页数量必须大于0',
        'page.require' => '页码不能为空',
        'page.integer' => '页码必须是整数',
        'page.gt' => '页码必须大于0',
    ];
}


InventoryCountValidate 验证规则

namespace app\common\validate;

use think\Validate;

class InventoryCountValidate extends Validate
{
    protected $rule = [
        'inventory_id' => 'require|integer|gt:0',
        'counted_quantity' => 'require|integer|gt:0',
        'limit' => 'require|integer|gt:0',
        'page' => 'require|integer|gt:0',
    ];

    protected $message = [
        'inventory_id.require' => '库存ID不能为空',
        'inventory_id.integer' => '库存ID必须是整数',
        'inventory_id.gt' => '库存ID必须大于0',
        'counted_quantity.require' => '盘点数量不能为空',
        'counted_quantity.integer' => '盘点数量必须是整数',
        'counted_quantity.gt' => '盘点数量必须大于0',
        'limit.require' => '每页数量不能为空',
        'limit.integer' => '每页数量必须是整数',
        'limit.gt' => '每页数量必须大于0',
        'page.require' => '页码不能为空',
        'page.integer' => '页码必须是整数',
        'page.gt' => '页码必须大于0',
    ];
}


总结
通过上述步骤，你可以在控制器中实现一个完整的批次管理和盘点功能。这些功能可以帮助你更好地跟踪不同批次的商品，并进行准确的盘点。以下是完整的控制器方法示例：

public function getBatchList()
{
    if (Request::isPost()) {
        $data = Request::post(['limit', 'page']);
        try {
            validate(BatchValidate::class)->check($data);
        } catch (ValidateException $exception) {
            $judge = $exception->getMessage();
            return $this->show(
                config('status.error'),
                config('status.error'),
                $judge
            );
        }

        $page = isset($data['page']) ? $data['page'] : 1;
        $limit = isset($data['limit']) ? $data['limit'] : 10;
        $offset = ($page - 1) * $limit;

        $batchData = Db::name('base_batch')
            ->alias('b')
            ->join('base_goods g', 'b.goods_id = g.id')
            ->field('b.id as batch_id, g.name as goods_name, b.batch_number, b.production_date, b.expiration_date')
            ->limit($limit)
            ->offset($offset)
            ->select();

        // 获取总记录数
        $total = Db::name('base_batch')
            ->alias('b')
            ->join('base_goods g', 'b.goods_id = g.id')
            ->count();

        // 返回结果
        $result = [
            'code' => 0,
            'msg' => '',
            'count' => $total,
            'data' => $batchData
        ];

        return json($result);
    }

    $data['url'] = request()->pathinfo();
    View::assign(['data' => $data]);
    return View::fetch();
}

public function addBatch()
{
    if (Request::isPost()) {
        $data = Request::post(['goods_id', 'batch_number', 'production_date', 'expiration_date']);
        try {
            validate(BatchValidate::class)->check($data);
        } catch (ValidateException $exception) {
            $judge = $exception->getMessage();
            return $this->show(
                config('status.error'),
                config('status.error'),
                $judge
            );
        }

        $batchData = [
            'goods_id' => $data['goods_id'],
            'batch_number' => $data['batch_number'],
            'production_date' => $data['production_date'],
            'expiration_date' => $data['expiration_date']
        ];

        $result = Db::name('base_batch')->insert($batchData);

        if ($result) {
            return json([
                'code' => 0,
                'msg' => '批次记录添加成功'
            ]);
        } else {
            return json([
                'code' => 1,
                'msg' => '批次记录添加失败'
            ]);
        }
    }

    $data['url'] = request()->pathinfo();
    View::assign(['data' => $data]);
    return View::fetch();
}

public function getInventoryList()
{
    if (Request::isPost()) {
        $data = Request::post(['limit', 'page']);
        try {
            validate(InventoryValidate::class)->check($data);
        } catch (ValidateException $exception) {
            $judge = $exception->getMessage();
            return $this->show(
                config('status.error'),
                config('status.error'),
                $judge
            );
        }

        $page = isset($data['page']) ? $data['page'] : 1;
        $limit = isset($data['limit']) ? $data['limit'] : 10;
        $offset = ($page - 1) * $limit;

        $inventoryData = Db::name('base_inventory')
            ->alias('i')
            ->join('base_goods