PHP项目库存盘点如何脚本批量核对库存数量

wen PHP项目 2026-06-24 216

本文目录导读：

基础数据库比对方案
CSV文件批量导入
带进度显示的批量处理
生成差异报告
完整命令行脚本示例
使用建议

我来为您介绍几种PHP脚本批量核对库存数量的方案：

基础数据库比对方案

<?php
// inventory_check.php
class InventoryChecker {
    private $db;
    public function __construct($host, $user, $pass, $dbname) {
        $this->db = new mysqli($host, $user, $pass, $dbname);
        if ($this->db->connect_error) {
            die("连接失败: " . $this->db->connect_error);
        }
    }
    // 批量核对库存
    public function batchCheck($items) {
        $results = [];
        foreach ($items as $item) {
            $sku = $this->db->real_escape_string($item['sku']);
            $physical_qty = intval($item['quantity']);
            // 查询系统库存
            $sql = "SELECT id, product_name, quantity AS system_qty 
                    FROM inventory 
                    WHERE sku = '$sku'";
            $result = $this->db->query($sql);
            if ($row = $result->fetch_assoc()) {
                $diff = $physical_qty - $row['system_qty'];
                $status = ($diff == 0) ? '正确' : '差异';
                $results[] = [
                    'sku' => $sku,
                    'product' => $row['product_name'],
                    'system_qty' => $row['system_qty'],
                    'physical_qty' => $physical_qty,
                    'difference' => $diff,
                    'status' => $status
                ];
                // 记录差异
                if ($diff != 0) {
                    $this->logDiscrepancy($sku, $row['system_qty'], $physical_qty, $diff);
                }
            } else {
                $results[] = [
                    'sku' => $sku,
                    'product' => '未找到',
                    'system_qty' => 0,
                    'physical_qty' => $physical_qty,
                    'difference' => $physical_qty,
                    'status' => '未匹配'
                ];
            }
        }
        return $results;
    }
    // 记录差异日志
    private function logDiscrepancy($sku, $system_qty, $physical_qty, $diff) {
        $sql = "INSERT INTO inventory_check_log 
                (sku, system_qty, physical_qty, difference, check_time, status) 
                VALUES ('$sku', $system_qty, $physical_qty, $diff, NOW(), '差异')";
        $this->db->query($sql);
    }
}
// 使用示例
$checker = new InventoryChecker('localhost', 'root', 'password', 'inventory_db');
// 从CSV文件导入盘点数据
$items = [
    ['sku' => 'SKU001', 'quantity' => 100],
    ['sku' => 'SKU002', 'quantity' => 50],
    ['sku' => 'SKU003', 'quantity' => 75]
];
$results = $checker->batchCheck($items);

CSV文件批量导入

<?php
// csv_importer.php
class CSVInventoryImporter {
    private $checker;
    public function __construct($checker) {
        $this->checker = $checker;
    }
    // 导入CSV文件进行盘点
    public function importFromCSV($csvFile) {
        $items = [];
        if (($handle = fopen($csvFile, "r")) !== FALSE) {
            // 读取表头
            $headers = fgetcsv($handle);
            while (($data = fgetcsv($handle)) !== FALSE) {
                $items[] = [
                    'sku' => $data[0],  // 假设第一列是SKU
                    'quantity' => $data[1]  // 假设第二列是数量
                ];
            }
            fclose($handle);
        }
        return $this->checker->batchCheck($items);
    }
}
// 使用示例
$checker = new InventoryChecker($host, $user, $pass, $dbname);
$importer = new CSVInventoryImporter($checker);
$results = $importer->importFromCSV('inventory_check.csv');

带进度显示的批量处理

<?php
// batch_check_with_progress.php
class BatchInventoryChecker {
    private $db;
    private $batchSize = 100; // 每批处理数量
    public function checkWithProgress($items, $callback = null) {
        $totalItems = count($items);
        $processedItems = 0;
        $results = [];
        // 分批处理
        for ($offset = 0; $offset < $totalItems; $offset += $this->batchSize) {
            $batch = array_slice($items, $offset, $this->batchSize);
            // 处理当前批次
            foreach ($batch as $item) {
                $result = $this->checkSingleItem($item);
                $results[] = $result;
                $processedItems++;
                // 回调函数，用于显示进度
                if ($callback) {
                    $progress = ($processedItems / $totalItems) * 100;
                    $callback($progress, $processedItems, $totalItems, $result);
                }
            }
            // 防止数据库超时
            usleep(100000); // 0.1秒延迟
        }
        return $results;
    }
    private function checkSingleItem($item) {
        // 单件商品核对逻辑
        // ...
    }
}
// 进度回调示例
$checker = new BatchInventoryChecker();
$results = $checker->checkWithProgress($items, function($progress, $current, $total, $result) {
    echo sprintf("进度: %.1f%% (%d/%d)\n", $progress, $current, $total);
    if ($result['status'] == '差异') {
        echo "⚠️ 差异: {$result['sku']} - 系统:{$result['system_qty']}, 实际:{$result['physical_qty']}\n";
    }
});

生成差异报告

<?php
// report_generator.php
class InventoryReportGenerator {
    // 生成Excel兼容的CSV报告
    public function generateCSVReport($results, $filename = 'inventory_report.csv') {
        $handle = fopen($filename, 'w');
        // 添加CSV BOM用于Excel正确显示中文
        fprintf($handle, chr(0xEF).chr(0xBB).chr(0xBF));
        // 写入表头
        fputcsv($handle, ['SKU', '商品名称', '系统数量', '盘点数量', '差异', '状态']);
        $summary = [
            'total' => 0,
            'correct' => 0,
            'difference' => 0,
            'unmatched' => 0
        ];
        // 写入数据
        foreach ($results as $row) {
            fputcsv($handle, [
                $row['sku'],
                $row['product'],
                $row['system_qty'],
                $row['physical_qty'],
                $row['difference'],
                $row['status']
            ]);
            $summary['total']++;
            switch($row['status']) {
                case '正确':
                    $summary['correct']++;
                    break;
                case '差异':
                    $summary['difference']++;
                    break;
                case '未匹配':
                    $summary['unmatched']++;
                    break;
            }
        }
        // 写入汇总信息
        fputcsv($handle, []); // 空行
        fputcsv($handle, ['汇总信息']);
        fputcsv($handle, ['总计盘点', $summary['total']]);
        fputcsv($handle, ['正确数量', $summary['correct']]);
        fputcsv($handle, ['差异数量', $summary['difference']]);
        fputcsv($handle, ['未匹配数量', $summary['unmatched']]);
        fclose($handle);
        return $filename;
    }
}

完整命令行脚本示例

#!/usr/bin/php
<?php
// inventory_batch_check.php
// 配置
$config = [
    'db' => [
        'host' => 'localhost',
        'user' => 'root',
        'pass' => 'password',
        'name' => 'inventory_db'
    ],
    'input_file' => 'inventory_check.csv',
    'output_file' => 'check_result.csv'
];
// 主逻辑
function main($config) {
    echo "开始库存盘点...\n";
    // 1. 连接数据库
    $db = new mysqli($config['db']['host'], $config['db']['user'], 
                    $config['db']['pass'], $config['db']['name']);
    // 2. 读取盘点数据
    $items = [];
    if (($handle = fopen($config['input_file'], "r")) !== FALSE) {
        fgetcsv($handle); // 跳过表头
        while (($data = fgetcsv($handle)) !== FALSE) {
            $items[] = ['sku' => $data[0], 'quantity' => intval($data[1])];
        }
        fclose($handle);
    }
    echo "导入 " . count($items) . " 条盘点记录\n";
    // 3. 执行核对
    $checker = new InventoryChecker($config['db']['host'], $config['db']['user'],
                                   $config['db']['pass'], $config['db']['name']);
    $barWidth = 50;
    $results = [];
    $total = count($items);
    foreach ($items as $index => $item) {
        $result = $checker->checkSingleItem($item); // 简化版
        $results[] = $result;
        // 进度条
        $progress = ($index + 1) / $total;
        $filled = round($progress * $barWidth);
        $bar = str_repeat('=', $filled) . str_repeat(' ', $barWidth - $filled);
        printf("\r[%s] %.1f%%", $bar, $progress * 100);
    }
    echo "\n核对完成!\n";
    // 4. 生成报告
    $reportGen = new InventoryReportGenerator();
    $reportFile = $reportGen->generateCSVReport($results, $config['output_file']);
    echo "报告已生成: $reportFile\n";
}
main($config);