hqyt/app/Service/RdGenerateService.php

<?php

namespace App\Service;

use App\Model\RD;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

class RdGenerateService extends Service
{
    public function generate(array $data)
    {
        if (empty($data['month'])) {
            return [false, '请选择生成研发人员工时单年月'];
        }

        try {
            DB::transaction(function () use ($data) {
                $this->doGenerate($data['month']);
            });
        } catch (\Throwable $e) {
            return [false, $e->getMessage()];
        }

        return [true, ''];
    }

    protected function doGenerate(string $month)
    {
        $monthStart = Carbon::createFromFormat('Y-m', $month)->startOfMonth()->timestamp;
        $monthEnd   = Carbon::createFromFormat('Y-m', $month)->endOfMonth()->timestamp;

        // 1. 获取日历配置
        $calendar = DB::table('calendar')
            ->where('del_time', 0)
            ->where('time', $monthStart)
            ->first();

        if (!$calendar) throw new \Exception('当月日历未配置');

        $workDays = DB::table('calendar_details')
            ->where('del_time', 0)
            ->where('calendar_id', $calendar->id)
            ->where('is_work', 1)
            ->orderBy('time')
            ->pluck('time')
            ->toArray();

        if (empty($workDays)) throw new \Exception('当月无工作日');

        // 2. 获取相关项目并清理旧数据
        $items = DB::table('item')
            ->where('del_time', 0)
            ->where('is_use', 1)
            ->where('start_time', '<=', $monthEnd)
            ->where('end_time', '>=', $monthStart)
            ->orderBy('id','desc')
            ->get();

        $itemIds = $items->pluck('id')->toArray();
        if (!empty($itemIds)) {
            $rds = DB::table('rd')
                ->where('del_time', 0)
                ->where('type', RD::type_one)
                ->whereIn('item_id', $itemIds)
                ->whereBetween('order_time', [$monthStart, $monthEnd])
                ->pluck('id')
                ->toArray();

            if (!empty($rds)) {
                // 分片删除，防止 SQL 过长
                foreach (array_chunk($rds, 1000) as $chunk) {
                    DB::table('rd_details')->whereIn('rd_id', $chunk)->delete();
                    DB::table('rd')->whereIn('id', $chunk)->delete();
                }
            }
        }

        // 3. 预加载所有员工关联关系 (核心优化：内存化)
        $itemDetails = DB::table('item_details')
            ->where('del_time', 0)
            ->whereIn('item_id', $itemIds)
            ->where('type', 1)
            ->get()
            ->groupBy('data_id'); // 以员工ID分组

        $employeeIds = $itemDetails->keys()->toArray();

        // 4. 预加载员工月工时配置
        $employeeMonths = DB::table('employee_details')
            ->where('del_time', 0)
            ->whereIn('employee_id', $employeeIds)
            ->where('time', $calendar->time)
            ->get()
            ->keyBy('employee_id');

        $pendingRds = []; // 待插入的主表数据
        $rdToEmployeeMap = []; // 临时映射：order_number => employee_id

        // 5. 内存循环处理
        foreach ($employeeIds as $employeeId) {
            $empMonthConfig = $employeeMonths->get($employeeId);
            if (!$empMonthConfig || $empMonthConfig->total_hours_2 <= 0) continue;

            // 获取该员工参与的项目集合
            $myProjectIds = $itemDetails->get($employeeId)->pluck('item_id')->toArray();
            $employeeItems = $items->whereIn('id', $myProjectIds);

            // 计算该员工全月分布并填充到待插入数组
            $this->buildEmployeeRds(
                $employeeId,
                $employeeItems,
                $workDays,
                $empMonthConfig,
                $pendingRds,
                $rdToEmployeeMap
            );
        }

        // 6. 批量插入主表并同步明细表
        if (!empty($pendingRds)) {
            // 分批插入主表 (MySQL 默认限制一次约 10MB)
            foreach (array_chunk($pendingRds, 1000) as $chunk) {
                DB::table('rd')->insert($chunk);
            }

            // 通过 order_number 反查回生成的 ID
            $insertedRds = DB::table('rd')
                ->whereIn('order_number', array_keys($rdToEmployeeMap))
                ->select('id', 'order_number', 'crt_time')
                ->get();

            $pendingDetails = [];
            foreach ($insertedRds as $rd) {
                $pendingDetails[] = [
                    'rd_id'    => $rd->id,
                    'data_id'  => $rdToEmployeeMap[$rd->order_number],
                    'type'     => 1,
                    'crt_time' => $rd->crt_time,
                    'upd_time' => $rd->crt_time,
                    'del_time' => 0
                ];
            }

            foreach (array_chunk($pendingDetails, 1000) as $chunk) {
                DB::table('rd_details')->insert($chunk);
            }
        }
    }

    protected function buildEmployeeRds($employeeId, $employeeItems, $workDays, $config, &$pendingRds, &$rdToEmployeeMap)
    {
        $totalMinutes = intval(round($config->total_hours_2 * 60) / 60) * 60;

        // 过滤有效日期
        $validDays = [];
        foreach ($workDays as $day) {
            $dayItems = [];
            foreach ($employeeItems as $item) {
                if ($day >= $item->start_time && $day <= $item->end_time) {
                    $dayItems[] = $item;
                }
            }
            if (!empty($dayItems)) $validDays[$day] = $dayItems;
        }

        if (empty($validDays)) return;

        // 分配每日时长
        $daysCount = count($validDays);
        $avgDailyMinutes = intval(floor($totalMinutes / $daysCount) / 60) * 60;
        $remainderMinutes = $totalMinutes - ($avgDailyMinutes * $daysCount);

        $dailyMinutesMap = [];
        foreach (array_keys($validDays) as $day) {
            $dailyMinutesMap[$day] = $avgDailyMinutes;
        }

        if ($remainderMinutes > 0) {
            $extraHours = $remainderMinutes / 60;
            $dayKeys = array_keys($validDays);
            shuffle($dayKeys);
            for ($i = 0; $i < $extraHours; $i++) {
                $targetDay = $dayKeys[$i % $daysCount];
                if ($dailyMinutesMap[$targetDay] < 480) {
                    $dailyMinutesMap[$targetDay] += 60;
                } else {
                    $extraHours++;
                }
            }
        }

        // 生成具体数据行
        foreach ($validDays as $day => $dayItems) {
            $dailyMinutes = $dailyMinutesMap[$day];
            if ($dailyMinutes <= 0) continue;

            $remaining = $dailyMinutes;
            $numItems = count($dayItems);

            foreach ($dayItems as $idx => $item) {
                if ($idx === $numItems - 1) {
                    $slotMinutes = $remaining;
                } else {
                    $maxSlot = intval(floor($remaining * 0.7) / 60) * 60;
                    $slotMinutes = ($maxSlot >= 60) ? mt_rand(1, $maxSlot / 60) * 60 : 0;
                }

                if ($slotMinutes > 0) {
                    $rowData = $this->calculateTimeSlots($item->id, $day, $slotMinutes, $employeeId);
                    $pendingRds[] = $rowData;
                    $rdToEmployeeMap[$rowData['order_number']] = $employeeId;
                }
                $remaining -= $slotMinutes;
            }
        }
    }

    protected function calculateTimeSlots($itemId, $dayTime, $minutes,$employeeId)
    {
        $availableMinutes = 480;
        $minutes = min($minutes, $availableMinutes);

        $maxOffset = $availableMinutes - $minutes;
        $randomOffset = (intval($maxOffset / 30) > 0) ? mt_rand(0, intval($maxOffset / 30)) * 30 : 0;

        $dayStart   = Carbon::createFromTimestamp($dayTime)->setTime(8, 0);
        $lunchStart = Carbon::createFromTimestamp($dayTime)->setTime(11, 30);
        $lunchEnd   = Carbon::createFromTimestamp($dayTime)->setTime(12, 0);

        $start = $dayStart->copy()->addMinutes($randomOffset);
        $end = $start->copy();

        if ($start->lt($lunchStart)) {
            $morningLeft = $lunchStart->diffInMinutes($start);
            if ($minutes <= $morningLeft) {
                $end->addMinutes($minutes);
            } else {
                $end = $lunchEnd->copy()->addMinutes($minutes - $morningLeft);
            }
        } else {
            $end->addMinutes($minutes);
        }

        $crtTime = Carbon::createFromTimestamp($dayTime)->setTime(16, 30)->addSeconds(mt_rand(0, 1800))->timestamp;

        return [
            'crt_id' => $employeeId,
            'crt_time' => $crtTime,
            'order_time' => $dayTime,
            'item_id' => $itemId,
            'start_time_hour' => $start->hour,
            'start_time_min' => $start->minute,
            'end_time_hour' => $end->hour,
            'end_time_min' => $end->minute,
            'total_hours' => $minutes,
            'order_number' => 'RD' . date('Ymd', $dayTime) . mt_rand(1000, 9999) . substr(uniqid(), -3),
            'type' => 1,
            'del_time' => 0
        ];
    }
}