MYSQL 盘点功能

为了实现一个MySQL盘点功能，我们需要设计一个盘点表来记录盘点信息，并可能需要关联库存表来获取当前库存情况。假设我们有以下表：
base_inventory：库存表
base_inventory_count：盘点表
表结构示例
base_inventory 表结构
id (库存ID)
goods_id (商品ID)
quantity (库存数量)
location (库存位置)
base_inventory_count 表结构
id (盘点记录ID)
inventory_id (库存ID)
counted_quantity (盘点数量)
count_date (盘点日期)
status (盘点状态，例如 'pending', 'completed', 'discrepancy')
SQL 表创建示例

创建 base_inventory 表

CREATE TABLE base_inventory (
    id INT AUTO_INCREMENT PRIMARY KEY,
    goods_id INT NOT NULL,
    quantity INT NOT NULL,
    location VARCHAR(255) NOT NULL,
    FOREIGN KEY (goods_id) REFERENCES base_goods(id)
);


创建 base_inventory_count 表

CREATE TABLE base_inventory_count (
    id INT AUTO_INCREMENT PRIMARY KEY,
    inventory_id INT NOT NULL,
    counted_quantity INT NOT NULL,
    count_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status ENUM('pending', 'completed', 'discrepancy') NOT NULL DEFAULT 'pending',
    FOREIGN KEY (inventory_id) REFERENCES base_inventory(id)
);

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

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')
            ->field('i.id as inventory_id, g.name as goods_name, 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')
            ->count();

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

        return json($result);
    }

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

2. 添加盘点记录

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'];

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

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

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

        // 插入盘点记录
        $countData = [
            'inventory_id' => $inventoryId,
            '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();
}

3. 查看盘点记录

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')
            ->field('c.id as count_id, g.name as goods_name, 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')
            ->count();

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

        return json($result);
    }

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

解释
获取库存列表：
从 base_inventory 表中获取库存信息，并关联 base_goods 表以获取商品名称。
支持分页查询。
添加盘点记录：
接收盘点数量和库存ID。
验证数据并插入 base_inventory_count 表。
计算当前库存数量与盘点数量的差异，并设置盘点状态。
查看盘点记录：
从 base_inventory_count 表中获取盘点记录，并关联 base_inventory 和 base_goods 表以获取详细信息。
支持分页查询。
验证规则示例

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',
    ];
}


总结
通过上述步骤，你可以在控制器中实现一个完整的盘点功能，包括获取库存列表、添加盘点记录和查看盘点记录。这些功能可以帮助你更好地管理库存和盘点过程。