<?php
namespace app\common\service\wareHousFee\jingdong\wareHousingFees;
use app\common\model\wareHousFee\jingdong\wareHousingFees\WareHousingFeesItemizationModel;
use app\common\model\wareHousFee\jingdong\wareHousingFees\WareHousingFeesQuoteModel;
use think\facade\Db;
use think\facade\Log;
class WareHousingFeeService
{
public function __construct(){}
/**
* @notes 京东仓储服务费核对逻辑
* @param $yearMonth
* @return bool
* @throws DataNotFoundException
* @throws DbException
* @throws ModelNotFoundException
* @author 胡军
* @date 2025/06/27
*/
public function wareHousingFeeVerifyDo(string $yearMonth): bool
{
// 1. 获取月份时间范围
$monthTimeRange = $this->getMonthTimeRange($yearMonth);
if (!$monthTimeRange) {
Log::error("无效的月份格式: {$yearMonth}");
return false;
}
// 2. 预加载报价数据
$quoteList = WareHousingFeesQuoteModel::where('service_type', 'IN', ['出库单', '退供单', '退货单'])
->column('first_three_items,additional_items', 'service_type');
//print_r($quoteList);die;
/*Array
(
[出库单] => Array
(
[first_three_items] => 1.500
[additional_items] => 0.250
[service_type] => 出库单
)
[退供单] => Array
(
[first_three_items] => 0.500
[additional_items] => 0.500
[service_type] => 退供单
)
[退货单] => Array
(
[first_three_items] => 3.000
[additional_items] => 0.500
[service_type] => 退货单
)
)*/
if (empty($quoteList)) {
Log::warning("京东仓储服务费报价表为空,月份: {$yearMonth}");
return false;
}
// 3. 配置参数(从环境变量获取或使用默认值)
$batchSize = 5; // 批次大小即每次读取的数据条数
$reconnectInterval = 200; // 每10万条重新连接一次数据库(200批次=10万条)
$gcInterval = 20; // 垃圾回收间隔
$updateCount = 0; // 处理计数
$batchCount = 0; // 批次计数
$status = true; // 整体状态
// 性能监控变量
$startTime = microtime(true);
$startMemory = memory_get_usage();
try {
// 4. 创建基础查询
$query = WareHousingFeesItemizationModel::whereBetween('business_time', [
$monthTimeRange['startTime'],
$monthTimeRange['endTime']
]);
// 5. 分批次处理数据
$query->chunk($batchSize, function($items) use (
&$updateCount,
&$batchCount,
&$status,
$quoteList,
$reconnectInterval,
$gcInterval,
$yearMonth,
$startTime,
$startMemory
) {
print_r($items);
$batchCount++;
//print_r($items->toArray());die;
// 6. 分组统计(按单据号和单据类型分组)
$groupedData = [];
foreach ($items as $item) {
$key = $item->document_number . '|' . $item->document_type;
//echo $key;
//ESL00000022972118012|出库单
//ESL00000022971633940|退货单
//ESL00000022971819716|退供单
//ESL00000022972118012|出库单
if (!isset($groupedData[$key])) {
$groupedData[$key] = [
'document_number' => $item->document_number,
'document_type' => $item->document_type,
'total_quantity' => 0,
'total_settlement_amount' => 0
];
}
$groupedData[$key]['total_quantity'] += $item->quantity;// 数量累加
$groupedData[$key]['total_settlement_amount'] += $item->settlement_amount;//结算金额累加
}
/*echo "<pre>";
print_r($groupedData);
echo "</pre>";die;
Array
(
[ESL00000022972118012|出库单] => Array
(
[document_number] => ESL00000022972118012
[document_type] => 出库单
[total_quantity] => 4
[total_settlement_amount] => 3
)
[ESL00000022971633940|退货单] => Array
(
[document_number] => ESL00000022971633940
[document_type] => 退货单
[total_quantity] => 1
[total_settlement_amount] => 1.5
)
[ESL00000022971819716|退供单] => Array
(
[document_number] => ESL00000022971819716
[document_type] => 退供单
[total_quantity] => 1
[total_settlement_amount] => 0
)
)*/
// 7. 计算理论金额和差额
$updateData = [];
foreach ($groupedData as $data) {
//单据类型
$docType = $data['document_type'];
//数量累加
$totalQty = $data['total_quantity'];
//结算金额累加
$settlementAmount = $data['total_settlement_amount'];
//理论金额
$theoreticalAmount = 0;
//判断类型 根据报价单以及数量 拿到具体的首三件以及大于三件的具体价格进行计算
switch ($docType) {
case '出库单':
if (isset($quoteList['出库单'])) {
$quote = $quoteList['出库单'];
$theoreticalAmount = ($totalQty <= 3)
? $quote['first_three_items']
: $quote['first_three_items'] + ($totalQty - 3) * $quote['additional_items'];
}
break;
case '退供单':
if (isset($quoteList['退供单'])) {
$quote = $quoteList['退供单'];
$theoreticalAmount = $quote['first_three_items'] * $totalQty;
}
break;
case '退货单':
if (isset($quoteList['退货单'])) {
$quote = $quoteList['退货单'];
$theoreticalAmount = ($totalQty <= 3)
? $quote['first_three_items']
: $quote['first_three_items'] + ($totalQty - 3) * $quote['additional_items'];
}
break;
}
// 跳过理论金额为0的异常情况
if ($theoreticalAmount == 0) {
Log::warning("京东仓储费计算失败: {$data['document_number']}, 单据类型: {$docType}");
continue;
}
// 计算费用差额: 结算金额累加 - 理论金额
$balance = $settlementAmount - $theoreticalAmount;
$updateData[] = [
//单据编号
'document_number' => $data['document_number'],
//单据类型
'document_type' => $docType,
//总数量
'total_quantity' => $totalQty,
//结算总金额
'total_settlement_amount' => $settlementAmount,
//理论金额
'theoretical_amount' => $theoreticalAmount,
//差异
'balance' => $balance,
//所属年月
'yearMonth' => $yearMonth
];
}
// 8. $updateData为最后要插入结果表的数据 批量更新数据库(支持插入和更新)
if (!empty($updateData)) {
$this->batchUpsert($updateData);
$updateCount += count($updateData);
}
// 9. 内存管理(释放不再使用的变量)
unset($items, $groupedData, $updateData);
// 10. 定期执行垃圾回收
if ($batchCount % $gcInterval === 0) {
gc_collect_cycles();
}
// 11. 定期重连数据库(避免长连接超时)
if ($batchCount % $reconnectInterval === 0) {
$this->reconnectDatabase();
Log::info("京东仓储服务费用核对已处理 {$updateCount} 条,进行数据库重连");
}
// 12. 输出进度日志(每10批次记录一次)
if ($batchCount % 10 === 0) {
$memUsage = round((memory_get_usage() - $startMemory) / 1024 / 1024, 2);
$timeElapsed = round(microtime(true) - $startTime, 2);
Log::info("处理进度: {$updateCount}条, 内存占用: {$memUsage}MB, 耗时: {$timeElapsed}秒");
}
});
}catch (\Throwable $e) {
Log::error("京东仓储费核对系统运行异常: {$e->getMessage()}");
$status = false;
} finally {
Db::close(); // 最终关闭数据库连接
}
// 13. 生成最终统计日志
$peakMemory = round(memory_get_peak_usage() / 1024 / 1024, 2);
$totalTime = round(microtime(true) - $startTime, 2);
Log::info("京东仓储费核对完成: {$yearMonth}, 处理单据: {$updateCount}条, 峰值内存: {$peakMemory}MB, 总耗时: {$totalTime}秒");
return $status;
}
// 数据库重连方法,用于长时间运行的任务,避免连接超时
private function reconnectDatabase()
{
try {
$connection = \think\facade\Db::connect();
$connection->close(); // 关闭当前连接
$connection->connect(); // 重新建立连接
} catch (\Exception $e) {
// 记录重连失败日志,但不中断程序执行
Log::error('【菜鸟退货入仓费用核对数据库重连失败】' . $e->getMessage());
}
}
// 获取月份时间范围(返回包含开始和结束时间的数组)
private function getMonthTimeRange(string $yearMonth): ?array
{
if (!preg_match('/^\d{4}-\d{2}$/', $yearMonth)) {
return null;
}
$startTime = date('Y-m-01 00:00:00', strtotime($yearMonth));
$endTime = date('Y-m-t 23:59:59', strtotime($yearMonth));
return ['startTime' => $startTime, 'endTime' => $endTime];
}
// 批量插入或更新数据(使用INSERT ON DUPLICATE KEY UPDATE语法)
private function batchUpsert(array $data): void
{
if (empty($data)) return;
// 按1000条数据分块处理,避免SQL语句过长
// TODO:根据自己服务器性能适度调整大小 反复测试找到最合适的值即可
$chunks = array_chunk($data, 1000);
foreach ($chunks as $chunk) {
$values = [];
$params = [];
// 构建SQL语句的VALUES部分
foreach ($chunk as $row) {
$values[] = "(?, ?, ?, ?, ?, ?, ?)";
$params[] = $row['document_number'];
$params[] = $row['document_type'];
$params[] = $row['total_quantity'];
$params[] = $row['total_settlement_amount'];
$params[] = $row['theoretical_amount'];
$params[] = $row['balance'];
$params[] = $row['yearMonth'];
}
// 构建完整的INSERT ON DUPLICATE KEY UPDATE语句
// 使用了 MySQL 的 INSERT ... ON DUPLICATE KEY UPDATE 语法。这个语法允许我们尝试插入一行,如果该行已经存在(根据唯一索引或主键判断),则改为执行更新操作
// 务必创建la_storage_service_yes 表的唯一键索引:ALTER TABLE `la_storage_service_yes` ADD UNIQUE KEY `idx_document_number` (`document_number`);
// 否则ON DUPLICATE KEY UPDATE语法不生效!!!
$sql = "INSERT INTO la_storage_service_yes
(document_number, document_type, total_quantity, total_settlement_amount, theoretical_amount, balance, yearMonth)
VALUES " . implode(',', $values) . "
ON DUPLICATE KEY UPDATE
document_type = VALUES(document_type),
total_quantity = VALUES(total_quantity),
total_settlement_amount = VALUES(total_settlement_amount),
theoretical_amount = VALUES(theoretical_amount),
balance = VALUES(balance),
yearMonth = VALUES(yearMonth)";
try {
Db::execute($sql, $params);
} catch (\Exception $e) {
Log::error("京东仓储费批量更新失败: {$e->getMessage()}\nSQL语句: {$sql}");
throw $e;
}
}
}
}
结合我的代码进行改造吧!追加上临时表帮我彻底解决2000条只处理了500条的问题,记得保留注释信息
最新发布