public function forwardDeliveryFeeVerifyDo($yearMonth):bool{
$monthTimeRange = $this->getMonthTimeRange($yearMonth);
$itemizationMonthList = ItemizationModel::whereBetween('business_time',[$monthTimeRange['startTime'],$monthTimeRange['endTime']])->select()->toArray();
//遍历选中月份的正向配送明细进行规则计算
foreach($itemizationMonthList as $key => $value){
$warehouse = DeliveryRangeModel::where('province',$value['destination_province'])->field('warehouse')->find();
//本应发货仓 (计算规则: 根据正向配送费明细表la_delivery_fee的目的省 与 配送范围表la_delivery_range的省份匹配 省份对应的仓库名称就是本应发货仓)
$itemizationMonthList[$key]['original_warehouse'] = $warehouse->warehouse ? $warehouse->warehouse : '';
//是否跨仓 (计算规则: 若正向配送费明细表la_delivery_fee的仓库名称等于上面拿到的本应发货仓,则标记不跨仓否则标记跨仓 1夸仓 0不夸仓)
$itemizationMonthList[$key]['is_cross_warehouse'] = $value['warehouse_name'] == $warehouse->warehouse ? '0' : '1';
//理论重量 (计算规则: 正向配送明细表la_delivery_fee当中的logistics_order_code物流履约单号去匹配正向配送货品明细表la_delivery_goods_detail当中的logistics_order_no物流履约单号
// 两者是一对多的关系-->一个明细表的物流履约单号对应一个或者多个货品明细表里的物流履约单号;
// 然后拿着正向配送货品明细表la_delivery_goods_detail当中的货品件数quantity,根据字段货品ID-->product_id匹配仓库费基础物量重量信息表la_warehouse_facility当中的京东商品代码jd_product_code或者
// 菜鸟货品ID-->cainiao_product_id去查对应的单品重量,然后用单品重量*正向配送货品明细表la_delivery_goods_detail当中的货品件数quantity之和就是理论重量)
if(!empty($value["logistics_order_code"])){
$goodsDetailsInfo = GoodsDetailModel::where('logistics_order_no',$value['logistics_order_code'])->field('product_id,quantity')->select()->toArray();
//print_r($goodsDetailsInfo);die;
if(!empty($goodsDetailsInfo)){
$weight = 0;
foreach($goodsDetailsInfo as $k => $v){
//single_item_weight单位为克
$singleItemWeightPro = GoodsWeightModel::where('cainiao_product_id',$v['product_id'])->field('single_item_weight')->find();
$weight += $v['quantity'] * $singleItemWeightPro->single_item_weight;
}
//TODO: 3.1415926 保留三位小数 直接就是 3.141 暂定
$itemizationMonthList[$key]['theoretical_weight'] = $weight;
}else{
//$itemizationMonthList[$key]['theoretical_weight'] = 0;
//如果明细表有单号但是货品明细表匹配不到则跳过 不再进行接下来的复杂运算
continue;
}
}else{
//$itemizationMonthList[$key]['theoretical_weight'] = 0;
//如果明细表单号都没有则直接跳过 不再进行接下来的复杂运算
continue;
}
//Kg (计算规则: 理论重量/1000)
//TODO: Kg是否要四舍五入还是直接向上取整?
$itemizationMonthList[$key]['kg'] = $itemizationMonthList[$key]['theoretical_weight'] / 1000;
//重量差异 (计算规则: 仓库名称当中如果包含成都两个关键字,也就是成都仓那么【计费包材体积1kg计泡8000】<1000,取【计费包材体积1kg计泡8000】/1000-Kg;计费包材体积1kg计泡8000】>1000,取max( 计费包材体积1kg计泡8000,计费包材体积计泡8000)/1000-Kg;
// 如果包含沈阳两个关键字,也就是沈阳仓那么取max(计费包材体积1kg计泡8000,计费包材体积计泡8000)/1000-Kg)
if (str_contains($value['warehouse_name'], "成都")) {
$weight_difference = 0;
if($value['bubble_volume_1kg'] < 1000){
$weight_difference = $value['bubble_volume_1kg'] / 1000 - $itemizationMonthList[$key]['kg'];
}elseif($value['bubble_volume_1kg'] > 1000){
$weight_difference = max($value['bubble_volume_1kg'],$value['bubble_volume']) / 1000 - $itemizationMonthList[$key]['kg'];
}
$itemizationMonthList[$key]['weight_difference'] = $weight_difference;
} elseif(str_contains($value['warehouse_name'], "沈阳")) {
$itemizationMonthList[$key]['weight_difference'] = max($value['bubble_volume_1kg'],$value['bubble_volume']) / 1000 - $itemizationMonthList[$key]['kg'];
}
//计费重量 (计算规则: 若0<kg<0.5,则取0.5;若0.5<kg<=1 则取 1;若 kg>1,则向上取整)
$itemizationMonthList[$key]['charge_weight'] = $itemizationMonthList[$key]['kg'] > 1 ? ceil($itemizationMonthList[$key]['kg']) : ($itemizationMonthList[$key]['kg'] < 0.5 ? 0.5 : 1);
//理论费用 (计算规则: 仓库名称+目的省匹配正向配送费报价表:la_delivery_fee_quote,基于计费重量匹配报价表计算金额)
$deliveryFeeQuoteInfo = DeliveryQuoteModel::where('destination','=',$value['destination_province'])->where('warehouse','=',$value['warehouse_name'])->find()->toArray();
$theoreticalFee = $this->calculateTheoreticalFee($itemizationMonthList[$key]['charge_weight'],$deliveryFeeQuoteInfo);
$itemizationMonthList[$key]['theoretical_fee'] = $theoreticalFee;
//理论费用差异 (计算规则:计费金额-理论费用)
$itemizationMonthList[$key]['fee_difference'] = $value['charge_amount'] - $theoreticalFee;
//不跨仓费用 (计算规则:本应发货仓+目的省 正向配送费报价表:la_delivery_fee_quote,基于计费重量匹配报价表计算金额);
//不跨仓的话那么 不跨仓费用应该就等于理论费用
if($itemizationMonthList[$key]['is_cross_warehouse'] == '1'){ //跨仓了就要重新计算不跨仓费用
$deliveryFeeQuoteYesInfo = DeliveryQuoteModel::where('destination','=',$value['destination_province'])->where('warehouse','=',$itemizationMonthList[$key]['original_warehouse'])->find()->toArray();
$itemizationMonthList[$key]['non_cross_warehouse_fee'] = $this->calculateTheoreticalFee($itemizationMonthList[$key]['charge_weight'],$deliveryFeeQuoteYesInfo);
}else{
$itemizationMonthList[$key]['non_cross_warehouse_fee'] = $theoreticalFee;
}
//跨仓差异 (计算规则:计费金额-不跨仓费用)
$itemizationMonthList[$key]['cross_warehouse_difference'] = $value['charge_amount'] - $itemizationMonthList[$key]['non_cross_warehouse_fee'];
}
//因为涉及到的关联关系比较多,未必每一行数据都能正确匹配到相关数据,所以我们只对物流履约单号匹配成功的数据做更新操作
foreach($itemizationMonthList as $key => $value){
if(empty($value['original_warehouse']) || empty($value['theoretical_weight']) || $value['theoretical_weight'] == 0){
//记录日志
Log::warning('【正向配送费核对仓库名称或物流履约单号不匹配】--月份为:'.$yearMonth."的费用核对匹配不到仓库名称或者物流履约单号,明细数据id为".$value['id']);
unset($itemizationMonthList[$key]);
}
}
//批量分批次更新数据库
$status = true;
$batchSize = 50; // 每批10条记录
$totalCount = count($itemizationMonthList);
$batchCount = ceil($totalCount / $batchSize);
$itemizationModel = new ItemizationModel();
for ($i = 0; $i < $batchCount; $i++) {
$batchData = array_slice($itemizationMonthList, $i * $batchSize, $batchSize);
try {
$itemizationModel->startTrans();
// 使用批量更新替代循环单条更新
$itemizationModel->saveAll($batchData);
$itemizationModel->commit();
} catch (\Exception $e) {
$itemizationModel->rollback();
//记录日志
Log::error('【正向配送费核对异常】--月份为:'.$yearMonth."的费用核对发生错误:" . $e->getMessage());
//其中一个批次数据处理失败则直接退出循环 不再继续执行后续批次的数据处理 报错给前端显示
$status = false;
break;
}
}
return $status;
}
我现在需要对数据库当中接近100万的数据进行处理,这是我之前写的业务逻辑代码肯定不行,因为不能一次性读取100万出来去处理,可以使用使用游标分批处理chunk, 然后再高效批量更新,你看一下是我其他模块的处理业务逻辑的代码就是这么做的:
public function ReverseDeliveryFeeVerifyDo($yearMonth): bool
{
$monthTimeRange = $this->getMonthTimeRange($yearMonth);
//使用键值对缓存报价数据
$quoteMap = QuoteModel::column('price', 'warehous');
if (empty($quoteMap)) {
Log::warning("【逆向配送费报价数据缺失】月份:{$yearMonth}");
return false;
}
$status = true;
$batchSize = 500; //批次大小即每次读取的数据条数
$reconnectInterval = 200; //每10万条重新连接一次数据库 200批次后重连(每200批=10万条)
$updateCount = 0;
try {
//使用游标分批处理(减少内存占用)
$query = ItemizationModel::whereBetween('business_time', [
$monthTimeRange['startTime'],
$monthTimeRange['endTime']
])->field('id,billing_amount,warehouse_name');
$query->chunk($batchSize, function ($items) use ($quoteMap, &$updateCount, &$status, $reconnectInterval) {
static $batchCount = 0;
$batchCount++;
//批量更新缓冲区
$updateBuffer = [];
foreach ($items as $item) {
if (isset($quoteMap[$item->warehouse_name])) {
$updateBuffer[$item->id] = [
'theoretical_amount' => $quoteMap[$item->warehouse_name],
'fee_difference' => $item->billing_amount - $quoteMap[$item->warehouse_name]
];
} else {
$updateBuffer[$item->id] = [
'theoretical_amount' => null,
'fee_difference' => null
];
}
}
//高效批量更新
$this->batchUpdate($updateBuffer);
$updateCount += count($updateBuffer);
// 条件重连(减少不必要重连)
if ($batchCount % $reconnectInterval === 0) {
$this->reconnectDatabase();
Log::info("逆向配送费用核对已处理 {$updateCount} 条,进行数据库重连");
}
unset($items, $updateBuffer);
});
} catch (\Exception $e) {
Log::error("逆向配送费核对异常: {$e->getMessage()}");
$status = false;
}
Log::info("逆向配送费用核对处理完成,共更新 {$updateCount} 条记录");
return $status;
}
//高效批量更新方法(单SQL更新多条)
private function batchUpdate(array $data)
{
if (empty($data)) return;
$table = 'la_reverse_delivery_fee';
$cases = [];
$ids = [];
$params = [];
// 构建CASE WHEN更新语句
foreach (['theoretical_amount', 'fee_difference'] as $field) {
$cases[$field] = 'CASE id ';
foreach ($data as $id => $row) {
$cases[$field] .= "WHEN {$id} THEN ? ";
$params[] = $row[$field];
$ids[] = $id;
}
$cases[$field] .= 'END';
}
$idsStr = implode(',', $ids);
/* 最后的执行sql可以视为(案例sql):
UPDATE la_reverse_delivery_fee
SET
theoretical_amount = CASE id
WHEN 1001 THEN 100 -- 当ID=1001时,更新为100
WHEN 1002 THEN 200 -- 当ID=1002时,更新为200
END,
fee_difference = CASE id
WHEN 1001 THEN 5 -- 当ID=1001时,更新为5
WHEN 1002 THEN 10 -- 当ID=1002时,更新为10
END
WHERE id IN (1001,1002);
其实就是巧妙地利用了 SQL 的CASE WHEN语法,将多行更新合并为单条 SQL,是一种常见的数据库优化技巧!
*/
$sql = "UPDATE {$table} SET
theoretical_amount = {$cases['theoretical_amount']},
fee_difference = {$cases['fee_difference']}
WHERE id IN ({$idsStr})";
Db::execute($sql, $params);
}
//数据库重连方法
private function reconnectDatabase()
{
try {
$connection = \think\facade\Db::connect();
$connection->close();
$connection->connect();
} catch (\Exception $e) {
//print_r("数据库连接失败(直接干死结束进程):".$e->getMessage());die;
Log::error('【逆向配送费用核对数据库重连失败】' . $e->getMessage());
}
}
/**
* @notes 根据年月比如2025-05获取当月时间范围 精确到秒 (git有问题未解决 暂时无法写入common.php当中 临时放这)
* @param string $yearMonth
* @return array
* @author 胡军
* @date 2025/06/19
*/
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
];
}
请模仿下面的帮我处理一下我之前的直接取出100万数据进行处理的老代码
最新发布