<?
php
namespace app\common\service\wareHousFee\jingdong\wareHousingFees;
use app\common\model\wareHousFee\jingdong\wareHousingFees\WareHousingFeesItemizationModel;
use app\common\model\wareHousFee\jingdong\wareHousingFees\WareHousingFeesItemVeryModel;
use app\common\model\wareHousFee\jingdong\wareHousingFees\WareHousingFeesQuoteModel;
use app\common\model\wareHousFee\newFie\camelCaseFee\CamelCaseFeeItemizationModel;
use think\db\exception\DataNotFoundException;
use think\db\exception\DbException;
use think\db\exception\ModelNotFoundException;
use think\facade\Log;
class WareHousingFeesService
{
public function __construct(){}
/**
* @notes 京东仓储服务费核对逻辑
* @param $yearMonth
* @return bool
* @throws DataNotFoundException
* @throws DbException
* @throws ModelNotFoundException
* @author 胡军
* @date 2025/06/27
*/
public function wareHousingFeeVerifyDo($yearMonth):bool{
$monthTimeRange = $this->getMonthTimeRange($yearMonth);
//获取时间范围内的数据并进行分组统计
//total_quantity 总数
//total_settlement_amount 总的结算金额
$itemizationMonthList = WareHousingFeesItemizationModel::whereBetween('business_time', [$monthTimeRange['startTime'], $monthTimeRange['endTime']])
->field([
'document_number',
'document_type',
'SUM(quantity) as total_quantity',
'SUM(settlement_amount) as total_settlement_amount'
])
->group('document_number, document_type')
->select()
->toArray();
//一次性读取报价单避免foreach循环 提升效率
$quoteList = WareHousingFeesQuoteModel::select()->toArray();
$quoteListRst = [];
foreach ($quoteList as $item) {
$quoteListRst[$item['service_type']] = $item;
}
if(!
empty($quoteListRst)){
foreach($itemizationMonthList as $key => $value){
//初始化理论金额为0
$itemizationMonthList[$key]['theoretical_amount'] = 0;
if($value['document_type'] == '出库单' && !
empty($quoteListRst['出库单'])){
//$value['total_quantity'] 数量
if($value['total_quantity'] <= 3){
//理论金额
$itemizationMonthList[$key]['theoretical_amount'] = $quoteListRst['出库单']['first_three_items'];
} else {
$itemizationMonthList[$key]['theoretical_amount'] = $quoteListRst['出库单']['first_three_items'] + ($value['total_quantity'] - 3) * $quoteListRst['出库单']['additional_items'];
}
}
if($value['document_type'] == '退供单' && !
empty($quoteListRst['退供单'])){
$itemizationMonthList[$key]['theoretical_amount'] = $quoteListRst['退供单']['first_three_items'] * $value['total_quantity'];
}
if($value['document_type'] == '退货单' && !
empty($quoteListRst['退货单'])){
if($value['total_quantity'] <= 3){
$itemizationMonthList[$key]['theoretical_amount'] = $quoteListRst['退货单']['first_three_items'];
} else {
$itemizationMonthList[$key]['theoretical_amount'] = $quoteListRst['退货单']['first_three_items'] + ($value['total_quantity'] - 3) * $quoteListRst['退货单']['additional_items'];
}
}
//正常计算出来的理论金额不应该是0 那么这个时候就要记录日志便于排查
if($itemizationMonthList[$key]['theoretical_amount'] == 0){
//echo $value['document_number'].
PHP_EOL;
Log::
warning('【京东仓储服务费明细核对】--月份为:'.$yearMonth."的京东仓储服务费订单明细核对匹配不到京东仓储服务费报价表la_storage_service_quotes当中的类型,明细单据编号为".$value['document_number']);
unset($itemizationMonthList[$key]);
continue;
}else{
//差异:结算金额-理论金额
$itemizationMonthList[$key]['balance'] = $value['total_settlement_amount'] - $itemizationMonthList[$key]['theoretical_amount'];
}
}
//批量分批次更新数据库
$status = true;
$batchSize = 50; // 每批10条记录
$totalCount = count($itemizationMonthList);
$batchCount = ceil($totalCount / $batchSize);
$itemizationModel = new WareHousingFeesItemVeryModel();
for ($i = 0; $i < $batchCount; $i++) {
$batchData = array_slice($itemizationMonthList, $i * $batchSize, $batchSize);
$documentNumbers = array_column($batchData, 'document_number'); // 提取当前批次的所有单据号
try {
$itemizationModel->startTrans();
// 批量删除操作（根据单据号）
if (!
empty($documentNumbers)) {
$itemizationModel->whereIn('document_number', $documentNumbers)->delete();
}
//
使用批量更新替代循环单条更新
$itemizationModel->saveAll($batchData);
$itemizationModel->commit();
} catch (\Exception $e) {
$itemizationModel->rollback();
//记录日志
Log::error('【京东仓储服务费明细核对异常】--月份为:'.$yearMonth."的费用核对发生错误:" . $e->getMessage());
//其中一个批次数据处理失败则直接退出循环 不再继续执行后续批次的数据处理 报错给前端显示
$status = false;
break;
}
}
return $status;
}else{
return false;
}
}
/**
* @notes 根据年月比如2025-05获取当月时间范围 精确到秒 (git有问题未解决 暂时无法写入common.
php当中 临时放这)
* @param string $yearMonth
* @return array
* @author 胡军
* @date 2025/06/27
*/
private function getMonthTimeRange(string $yearMonth): array
{
// 验证输入格式 (YYYY-MM)
if (!preg_match('/^\d{4}-(0[1-9]|1[0-2])$/', $yearMonth)) {
throw new InvalidArgumentException('输入格式不正确，必须为YYYY-MM格式');
}
list($year, $month) = explode('-', $yearMonth);
// 构建开始时间
$startTime = "{$year}-{$month}-01 00:00:00";
//
使用DateTime类计算当月最后一天
$lastDay = (new \DateTime("{$year}-{$month}-01"))
->modify('last day of this month')
->format('d');
// 构建结束时间
$endTime = "{$year}-{$month}-{$lastDay} 23:59:59";
return [
'startTime' => $startTime,
'endTime' => $endTime
];
}
}
这是我原有的代码业务逻辑，后来我修改为了如下：
<?
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 = 500; // 批次大小即每次读取的数据条数
$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
) {
$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条进行了处理 如果像你说的一样不累加岂不是修改了我的原有逻辑？
本文探讨了PHP中使用empty()和unset()函数检查与移除变量的正确方式，并提供了具体的代码示例进行说明。
扫一扫
1867
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
