本文目录导读：
我来为您介绍几种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);
使用建议
-
数据准备：将盘点数据整理为CSV格式（SKU, 数量）
-
分批处理：大量数据时建议分批次（每次100-500条）
-
错误处理：添加完善的错误处理和日志记录
-
性能优化：
- 使用预处理语句
- 添加索引优化查询
- 考虑使用批处理或事务
-
安全考虑：
- SQL注入防护
- 数据验证
- 操作日志
这些脚本可以根据您的具体需求进行调整和扩展。
