collect_schedule/app/Jobs/OpenWork/External/UserExport.php

<?php

namespace App\Jobs\OpenWork\External;

use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use Illuminate\Support\Facades\Cache;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use App\Servers\DB\DbService;
use App\Models\OpenWork\DownloadTask as DownloadTaskModel;
use App\Servers\Aliyun\Oss;


/**
 * 导出客户数据-下载
 * @author 唐远望
 * @version 2.0
 * @date 2025-09-09
 */
class UserExport implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    protected $message_data;
    protected $Records;
    protected $file_id;

    /**
     * Create a new job instance.
     *
     * @return void
     */
    public function __construct(array $message_data)
    {
        $this->message_data = $message_data;
        $this->file_id = $message_data['file_id'];
    }

    public function getCorpId()
    {
        return $this->message_data['corpid'] ?? null;
    }

    public function handle()
    {
        $chunkSize = 1000;
        $file_id = $this->file_id;
        $corpid = $this->message_data['corpid'];
        (new DbService())->getConnectionNameByCorpId($corpid);
        // 标记为处理中
        Cache::put('export_status_' . $file_id, 'processing', 86400);

        // 获取导出参数
        $exportParams = Cache::get('export_params_' . $file_id);
        if (!$exportParams) {
            $this->fail(new \Exception('导出参数不存在'));
            return;
        }
        // 获取模型
        $modelClass = $exportParams['model'];
        $Model = new $modelClass;
        // 构建查询
        $query = $Model->query()
            ->join('openwork_external_follow', 'openwork_external_follow.external_userid', '=', 'openwork_external.external_userid')
            ->where($exportParams['map'])
            ->select(['openwork_external.*', 'openwork_external_follow.status', 'openwork_external_follow.follow_userid', 'openwork_external_follow.add_way', 'openwork_external_follow.remark', 'openwork_external_follow.remark_corp_name', 'openwork_external_follow.remark_mobiles', 'openwork_external_follow.createtime'])
            ->orderByDesc('openwork_external_follow.createtime')
            ->with(['external_tags']);

        // 标签筛选
        if (!empty($exportParams['external_userid'])) {
            $query->whereIn('openwork_external_follow.external_userid', $exportParams['external_userid']);
        }

        // 创建Excel文件
        $filename = '企业客户信息_' . $file_id . '.xlsx';
        $fullPath = public_path('uploads/exports/' . $filename);
        // 确保目录存在
        if (!file_exists(dirname($fullPath))) {
            mkdir(dirname($fullPath), 0755, true);
        }
        // 创建一个新的 Spreadsheet 对象
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // 设置表头
        $headers = [
            'A' => '授权方企微ID',
            'B' => '企业客户ID',
            'C' => '客户名称',
            'D' => '所属企业',
            'E' => '所属客服',
            'F' => '客户状态',
            'G' => '客户备注',
            'H' => '添加时间',
            'I' => '用户标签'
        ];

        foreach ($headers as $col => $value) {
            $sheet->setCellValue($col . '1', $value);
        }

        // 分块处理数据
        $row = 2;
        $query->chunk($chunkSize, function ($chunk) use (&$row, $sheet) {
            foreach ($chunk as $item) {
                $item = $item->toArray();
                // 加载标签关系
                $tag_id_name_array = !empty($item['external_tags']) ? array_column($item['external_tags'], 'tag_name') : [];
                if (!empty($tag_id_name_array)) {
                    $tag_id_name_array = array_filter($tag_id_name_array);
                }
                $tag_id_name = count($tag_id_name_array) > 0 ? implode(',', $tag_id_name_array) : '';
                $sheet->setCellValueExplicit('A' . $row, $item['corpid'], \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                $sheet->setCellValueExplicit('B' . $row, $item['external_userid'], \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                $sheet->setCellValueExplicit('C' . $row, $item['name'], \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                $sheet->setCellValueExplicit('D' . $row, $item['corp_name'], \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                $sheet->setCellValueExplicit('E' . $row, $item['follow_userid'], \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                $sheet->setCellValueExplicit('F' . $row, $item['status'] == 0 ? '正常' : '流失', \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                $sheet->setCellValueExplicit('G' . $row, $item['remark'], \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                $sheet->setCellValueExplicit('H' . $row, date('Y-m-d H:i:s', $item['insert_time']), \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                $sheet->setCellValueExplicit('I' . $row, $tag_id_name, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                $row++;
            }

            // 每处理完一个块，释放内存
            unset($chunk);
            gc_collect_cycles();
        });

        // 保存文件
        $writer = new Xlsx($spreadsheet);
        $writer->save($fullPath);

        // 清理
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet, $writer);

        // 更新状态和文件路径
        Cache::put('export_status_' . $file_id, 'completed', 86400);
        Cache::put('export_file_path_' . $file_id, $filename, 86400);

        // 清理参数缓存
        Cache::forget('export_params_' . $file_id);
        $Oss                = new Oss();
        $oss_url                = $Oss->uploadFile($filename,$fullPath);
        if ($oss_url)           @unlink($fullPath);
        //查询下载任务
        $downloadTask = DownloadTaskModel::where(['file_id' => $file_id, 'corpid' => $corpid])->first();
        if ($downloadTask) {
            $downloadTask->url =$oss_url;
            $downloadTask->file_dir_name =$filename;
            $downloadTask->update_time = time();
            $downloadTask->status = 1;
            $downloadTask->save();
        }
    }

    public function failed(\Exception $exception)
    {
        // 处理失败情况
        Cache::put('export_status_' . $this->file_id, 'failed', 86400);
        Cache::put('export_error_' . $this->file_id, $exception->getMessage(), 86400);
    }
}