zhijiayun/app/Jobs/Manager/Other/ExportLowPriceGoodsJobs.php

<?php

namespace App\Jobs\Manager\Other;

use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use App\Http\Requests\Manager\Process\LowPriceGoods as Request;
use App\Models\Manager\Process\LowPriceGoods as LowPriceGoodsModel;
use App\Models\Manager\Personnel\Employee as EmployeeModel;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use App\Models\Manager\Process\LowPriceGoodsMember as LowPriceGoodsMemberModel;
use App\Servers\Aliyun\Oss;
use App\Facades\Servers\Logs\Log;
use App\Models\Manager\Other\DownloadTask as DownloadTaskModel;
use Illuminate\Support\Facades\Cache;


/**
 * 违规处理-导出低价挂网数据处理队列
 * @author    唐远望
 * @version   1.0
 * @date      2026-04-01
 */
class ExportLowPriceGoodsJobs implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;
    protected $message_data;
    protected $company_id;
    /**
     * Create a new job instance.
     *
     * @return void
     */
    public function __construct(array $message_data)
    {
        $this->message_data = $message_data;
    }

    /**
     * Execute the job.
     *
     * @return void
     */
    public function handle()
    {
        try {
            $fileId = $this->message_data['file_id'];
            $company_id = $this->message_data['company_id'];
            $user_id = $this->message_data['user_id'];
            $DownloadTaskModel = new DownloadTaskModel();
            $fileName = '低价挂网商品数据' . $fileId . '.xlsx';
            $downloadLog                 = [
                'insert_time' => time(),
                'company_id' => $company_id,
                'operator_userid' => $user_id,
                'file_name' => $fileName,
                'file_id' => $fileId,
                'url' => '',
            ];
            // 写入数据表
            $DownloadTaskModel->insertGetId($downloadLog);
            $this->export_excel($this->message_data);
        } catch (\Exception $e) {
            Log::info('job_error', '违规数据-导出低价挂网数据处理队列失败', ['data' => $this->message_data, 'error' => $e->getMessage()]);
            //失败后清除缓存
            Cache::forget('ExportLowPriceGoodsJobs_' . $this->company_id);
        }
    }


    /**
     * 列表导出
     * @author 唐远望
     * @version 1.0
     * @date 2025-06-17
     */
    public function export_excel($message_data)
    {
        $LowPriceGoodsModel = new LowPriceGoodsModel();
        $LowPriceGoodsMemberModel = new LowPriceGoodsMemberModel();
        $EmployeeModel = new EmployeeModel();
        $admin_company_id = $message_data['admin_company_id'];
        $company_id = $message_data['company_id'];
        $is_admin = $message_data['is_admin']; //是否管理员操作 0=是1=否
        $user_id = $message_data['user_id'];
        $file_id = $message_data['file_id'];
        // 查询条件
        $map  = [];
        $job_page = isset($message_data['job_page']) ? $message_data['job_page'] : 1;
        $limit = 1000; //每次处理1000条
        $status    = $message_data['status'] ?? '';
        $start_time = $message_data['start_time'] ?? '';
        $end_time = $message_data['end_time'] ?? '';
        $product_name = $message_data['product_name'] ?? '';
        $product_names = $message_data['product_names'] ?? '';
        $first_responsible_person = $message_data['first_responsible_person'] ?? '';
        $responsible_person = $message_data['responsible_person'] ?? '';
        $platform = $message_data['platform'] ?? '';
        $company_name = $message_data['company_name'] ?? '';
        $store_name = $message_data['store_name'] ?? '';
        $anonymous_store_name = $message_data['anonymous_store_name'] ?? '';
        $store_names = $message_data['store_names'] ?? '';
        $source_responsible_person = $message_data['source_responsible_person'] ?? '';
        $processing_status = $message_data['processing_status'] ?? '';
        $product_specs = $message_data['product_specs'] ?? '';
        $online_posting_count = $message_data['online_posting_count'] ?? '';
        $category_name = $message_data['category_name'] ?? '';
        $province_ids = $message_data['province_ids'] ?? '';
        $city_ids = $message_data['city_ids'] ?? '';
        $shipment_province_ids = $message_data['shipment_province_ids'] ?? '';
        $shipment_city_ids = $message_data['shipment_city_ids'] ?? '';
        $product_brand = $message_data['product_brand'] ?? '';
        $collection_time_start_time = $message_data['collection_time_start_time'] ?? '';
        $collection_time_end_time = $message_data['collection_time_end_time'] ?? '';
        $online_posting_min_price = $message_data['online_posting_min_price'] ?? '';
        $online_posting_max_price = $message_data['online_posting_max_price'] ?? '';
        $is_ultra_low_price = $message_data['is_ultra_low_price'] ?? '';
        $merge_city_ids = $message_data['merge_city_ids'] ?? '';
        $merge_province_ids = $message_data['merge_province_ids'] ?? '';

        // 时间条件
        if ($collection_time_start_time) $map[] = ['collection_time', '>=', strtotime($collection_time_start_time)];
        if ($collection_time_end_time) $map[] = ['collection_time', '<=', strtotime($collection_time_end_time)];
        if ($start_time) $map[] = ['insert_time', '>=', strtotime($start_time)];
        if ($end_time) $map[]   = ['insert_time', '<=', strtotime($end_time)];
        // 其他条件
        if ($product_name) $map[] = ['product_name', 'like', "%$product_name%"];
        if ($store_name) $map[]   = ['store_name', 'like', "%$store_name%"];
        if ($anonymous_store_name) $map[]   = ['anonymous_store_name', 'like', "%$anonymous_store_name%"];
        if ($category_name) $map[] = ['category_name', 'like', "%$category_name%"];
        if ($product_brand) $map[] = ['product_brand', 'like', "%$product_brand%"];
        if ($online_posting_min_price) $map[] = ['online_posting_price', '>=', $online_posting_min_price];
        if ($online_posting_max_price) $map[] = ['online_posting_price', '<=', $online_posting_max_price];
        if ($is_ultra_low_price)  $map[] = ['is_ultra_low_price', '=', $is_ultra_low_price];

        $low_price_goods_where = [];
        // 权限判断
        if ($is_admin != 1 && $company_id != 0) {
            $low_price_goods_where['company_id'] = $company_id;
        } else {
            $low_price_goods_where['company_id']  = $admin_company_id;
        }
        $LowPriceGoodsModel = $LowPriceGoodsModel->where($low_price_goods_where);
        //多选平台查询
        if ($platform && is_string($platform)) {
            $platform = explode(',', $platform);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('platform', $platform);
        }
        //多选处理状态查询
        if ($processing_status && is_string($processing_status)) {
            $processing_status = explode(',', $processing_status);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('processing_status', $processing_status);
        }
        //多选状态查询
        if ($status && is_string($status)) {
            $status = explode(',', $status);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('status', $status);
        }
        //多选店铺名称查询
        if ($store_names && is_string($store_names)) {
            $store_names = explode(',', $store_names);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('store_name', $store_names);
        }
        //多选违规挂网次数查询
        if ($online_posting_count && is_string($online_posting_count)) {
            $online_posting_count = explode(',', $online_posting_count);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('online_posting_count', $online_posting_count);
        }
        //多选规格查询
        if ($product_specs && is_string($product_specs)) {
            $product_specs = explode(',', $product_specs);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('product_specs', $product_specs);
        }
        //多选商品查询
        if ($product_names && is_string($product_names)) {
            $product_names = explode(',', $product_names);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('product_name', $product_names);
        }
        //多选公司查询
        if ($company_name && is_string($company_name)) {
            $company_name = explode(',', $company_name);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('company_name', $company_name);
        }
        //多选第一责任人
        if ($first_responsible_person && is_string($first_responsible_person)) {
            $first_responsible_person = explode(',', $first_responsible_person);
            $subQuery = $LowPriceGoodsMemberModel->whereIn('employee_id', $first_responsible_person)->where('duty_type',1)->distinct('lowprice_product_logid')->select('lowprice_product_logid');
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('id', function ($query1) use ($subQuery) {
                $query1->select('lowprice_product_logid')->fromSub($subQuery, 'sub1');
            });
        }
        //多选责任人
        if ($responsible_person && is_string($responsible_person)) {
            $responsible_person = explode(',', $responsible_person);
            $subQuery = $LowPriceGoodsMemberModel->whereIn('employee_id', $responsible_person)->where('duty_type',2)->distinct('lowprice_product_logid')->select('lowprice_product_logid');
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('id', function ($query1) use ($subQuery) {
                $query1->select('lowprice_product_logid')->fromSub($subQuery, 'sub1');
            });
        }
        //多选溯源责任人
        if ($source_responsible_person && is_string($source_responsible_person)) {
            $source_responsible_person = explode(',', $source_responsible_person);
            $subQuery = $LowPriceGoodsMemberModel->whereIn('employee_id', $source_responsible_person)->where('duty_type',3)->distinct('lowprice_product_logid')->select('lowprice_product_logid');
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('id', function ($query1) use ($subQuery) {
                $query1->select('lowprice_product_logid')->fromSub($subQuery, 'sub1');
            });
        }
        //多选省份
        if ($province_ids && is_string($province_ids)) {
            $province_ids = explode(',', $province_ids);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('province_id', $province_ids);
        }
        //多选城市
        if ($city_ids && is_string($city_ids)) {
            $city_ids = explode(',', $city_ids);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('city_id', $city_ids);
        }
        //多选发货省份
        if ($shipment_province_ids && is_string($shipment_province_ids)) {
            $shipment_province_ids = explode(',', $shipment_province_ids);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('shipment_province_id', $shipment_province_ids);
        }
        //多选发货城市
        if ($shipment_city_ids && is_string($shipment_city_ids)) {
            $shipment_city_ids = explode(',', $shipment_city_ids);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('shipment_city_id', $shipment_city_ids);
        }
        //多合并省份
        if ($merge_province_ids && is_string($merge_province_ids)) {
            $merge_province_ids = explode(',', $merge_province_ids);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('merge_province_id', $merge_province_ids);
        }
        //多合并市
        if ($merge_city_ids && is_string($merge_city_ids)) {
            $merge_city_ids = explode(',', $merge_city_ids);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('merge_city_id', $merge_city_ids);
        }
        $key_name = 'ExportLowPriceGoodsJobs_' . $low_price_goods_where['company_id'];
        //创建缓存
        Cache::put($key_name, [], 60 * 60 * 24);
        $personnel_roles_info = $EmployeeModel->leftjoin('personnel_roles', 'personnel_roles.id', '=', 'personnel_employee.role_id')
            ->where('personnel_employee.id', $user_id)->select(['personnel_employee.id', 'personnel_roles.identity'])
            ->first();
        //角色身份1=普通2=管理员
        if(!empty($personnel_roles_info) && $personnel_roles_info->identity == 2){ 
            $is_admin = 1;
        }
        $query = $LowPriceGoodsModel->where($map);
        if ($is_admin != 1 && $company_id != 0) {
            $query = $query->where(function ($q) use ($user_id) {
                $q->where('first_responsible_person', 'like', "%,$user_id,%")
                    ->orWhere('responsible_person', 'like', "%,$user_id,%")
                    ->orWhere('source_responsible_person', 'like', "%,$user_id,%");
            });
        }

        $query->orderByDesc('insert_time')->orderByDesc('collection_time')
            ->chunkById($limit, function ($rows) use ($key_name) {
                $result_data = $rows->toArray();
                if (empty($result_data)) {
                    return true;
                }

                $list_data = $this->processing_responsible_person($result_data);
                $list_data_info = Cache::get($key_name) ?: [];
                $list_new_data = !empty($list_data_info) ? array_merge($list_data_info, $list_data) : $list_data;
                Cache::put($key_name, $list_new_data, 60 * 60 * 24);
                // 每处理完一个块，释放内存
                unset($result_data);
                gc_collect_cycles();
                return true;
            });

        $export_data_info = Cache::get($key_name) ?: [];
        $this->export_download($export_data_info, $low_price_goods_where['company_id'], $file_id);
        return;
    }


    /**
     * 处理责任人展示信息
     * @author 唐远望
     * @version 1.0
     * @date 2025-12-17
     */
    public function processing_responsible_person($result)
    {
        $EmployeeModel = new EmployeeModel();
        //获取所有员工
        $employeee_data = $EmployeeModel->select(['id', 'name'])->get()->toarray();
        $employeee_list = [];
        if (!empty($employeee_data)) {
            foreach ($employeee_data as $key => $value) {
                $employeee_list[$value['id']] = $value['name'];
            }
        }
        if (isset($result) && count($result) > 0) {
            foreach ($result as $key => $value) {
                //查询第一责任人名称
                $first_responsible_person = $value['first_responsible_person'] != '' ? explode(',', $value['first_responsible_person']) : [];
                $first_responsible_person_name = [];
                if (!empty($first_responsible_person)) {
                    foreach ($first_responsible_person as $k => $v) {
                        if (isset($employeee_list[$v])) {
                            $first_responsible_person_name[] = $employeee_list[$v];
                        }
                    }
                }
                $result[$key]['first_responsible_person_name'] = $first_responsible_person_name;
                //查询责任人名称
                $responsible_person = $value['responsible_person'] != '' ? explode(',', $value['responsible_person']) : [];
                $responsible_person_name = [];
                if (!empty($responsible_person)) {
                    foreach ($responsible_person as $k => $v) {
                        if (isset($employeee_list[$v])) {
                            $responsible_person_name[] = $employeee_list[$v];
                        }
                    }
                }
                $result[$key]['responsible_person_name'] = $responsible_person_name;
                //查询来源责任人名称
                $source_responsible_person = $value['source_responsible_person'] != '' ? explode(',', $value['source_responsible_person']) : [];
                $source_responsible_person_name = [];
                if (!empty($source_responsible_person)) {
                    foreach ($source_responsible_person as $k => $v) {
                        if (isset($employeee_list[$v])) {
                            $source_responsible_person_name[] = $employeee_list[$v];
                        }
                    }
                }
                $result[$key]['source_responsible_person_name'] = $source_responsible_person_name;
            }
        }
        return $result;
    }

    /**
     * 导出下载
     * @author 唐远望
     * @version 1.0
     * @date 2025-06-17
     */
    public function export_download($data, $company_id, $file_id)
    {
        // 创建一个新的 Spreadsheet 对象
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        //合并单元格
        $sheet->mergeCells('A1:U1');
        $sheet->setCellValue('A1', '低价挂网商品导出（导出时间：' . date('Y-m-d H:i:s', time()) . ')'); // 设置合并后的单元格内容
        // 获取合并后的单元格样式对象
        $style = $sheet->getStyle('A1');
        // 设置水平居中和垂直居中
        $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER)->setVertical(Alignment::VERTICAL_CENTER);
        // 然后设置行高以适应两行文本
        $sheet->getRowDimension(1)->setRowHeight(40); // 设置行高，单位是磅(point)
        // 设置表头
        $sheet->setCellValue('A2', '第一责任人');
        $sheet->setCellValue('B2', '责任人');
        $sheet->setCellValue('C2', '平台');
        $sheet->setCellValue('D2', '商品类型');
        $sheet->setCellValue('E2', '品牌名称');
        $sheet->setCellValue('F2', '商品名称');
        $sheet->setCellValue('G2', '库存');
        $sheet->setCellValue('H2', '销量');
        $sheet->setCellValue('I2', '快照URL');
        $sheet->setCellValue('J2', '商品规格');
        $sheet->setCellValue('K2', '监控价格');
        $sheet->setCellValue('L2', '挂网价格');
        $sheet->setCellValue('M2', '是否超低价格');
        $sheet->setCellValue('N2', '超低监控价格');
        $sheet->setCellValue('O2', '累计挂网次数');
        $sheet->setCellValue('P2', '连续挂网次数');
        $sheet->setCellValue('Q2', '链接地址');
        $sheet->setCellValue('R2', '店铺名称');
        $sheet->setCellValue('S2', '匿名店铺名称');
        $sheet->setCellValue('T2', '公司名称');
        $sheet->setCellValue('U2', '商业类型');
        $sheet->setCellValue('V2', '信用代码');
        $sheet->setCellValue('W2', '公司省');
        $sheet->setCellValue('X2', '公司市');
        $sheet->setCellValue('Y2', '处理状态');
        $sheet->setCellValue('Z2', '店铺省');
        $sheet->setCellValue('AA2', '店铺市');
        $sheet->setCellValue('AB2', '采集时间');
        $sheet->setCellValue('AC2', '清洗时间');

        //平台0=全部，1=淘宝，2=京东，3=拼多多，4=美团，5=药师帮，6=1药城，7=药九九,8=药易购，9=药帮忙，10=熊猫药药11=药房网
        $platform_data = [
            '0' => '全部',
            '1' => '淘宝',
            '2' => '京东',
            '3' => '拼多多',
            '4' => '美团',
            '5' => '药师帮',
            '6' => '1药城',
            '7' => '药九九',
            '8' => '药易购',
            '9' => '药帮忙',
            '10' => '熊猫药药',
            '11' => '药房网',
        ];
        $processing_status_text = [
            '1' => '待处理',
        ];
        $status_text = [
            '0' => '有效',
            '1' => '无效',
        ];
        // 填充数据
        $row = 3; // 从第2行开始
        foreach ($data as $item) {
            $first_responsible_person_name = !empty($item['first_responsible_person_name']) ? implode(',', $item['first_responsible_person_name']) : '';
            $responsible_person_name = !empty($item['responsible_person_name']) ? implode(',', $item['responsible_person_name']) : '';
            $source_responsible_person_name = !empty($item['source_responsible_person_name']) ? implode(',', $item['source_responsible_person_name']) : '';
            $sheet->setCellValue('A' . $row, $first_responsible_person_name);
            $sheet->setCellValue('B' . $row, $responsible_person_name);
            $sheet->setCellValue('C' . $row, isset($platform_data[$item['platform']]) ? $platform_data[$item['platform']] : '');
            $sheet->setCellValue('D' . $row, $item['category_name']);
            $sheet->setCellValue('E' . $row, $item['product_brand']);
            $sheet->setCellValue('F' . $row, $item['product_name']);
            $sheet->setCellValue('G' . $row, $item['inventory']);
            $sheet->setCellValue('H' . $row, $item['sales']);
            $sheet->setCellValue('I' . $row, $item['snapshot_url']);
            $sheet->setCellValue('J' . $row, $item['product_specs']);
            $sheet->setCellValue('K' . $row, $item['suggested_price']);
            $sheet->setCellValue('L' . $row, $item['online_posting_price']);
            $sheet->setCellValue('M' . $row, $item['is_ultra_low_price'] == 1 ? '是' : '否');
            $sheet->setCellValue('N' . $row, $item['ultra_low_price']);
            $sheet->setCellValue('O' . $row, $item['online_posting_count']);
            $sheet->setCellValue('P' . $row, $item['continuous_listing_count']);
            $sheet->setCellValue('Q' . $row, $item['link_url']);
            $sheet->setCellValue('R' . $row, $item['store_name']);
            $sheet->setCellValue('S' . $row, $item['anonymous_store_name']);
            $sheet->setCellValue('T' . $row, $item['company_name']);
            $sheet->setCellValue('U' . $row, $item['company_category_name']);
            $sheet->setCellValue('V' . $row, $item['social_credit_code']);
            $sheet->setCellValue('W' . $row, $item['province_name']);
            $sheet->setCellValue('X' . $row, $item['city_name']);
            $sheet->setCellValue('Y' . $row, isset($processing_status_text[$item['processing_status']]) ? $processing_status_text[$item['processing_status']] : '');
            $sheet->setCellValue('Z' . $row, $item['shipment_province_name']);
            $sheet->setCellValue('AA' . $row, $item['shipment_city_name']);
            $sheet->setCellValue('AB' . $row, !empty($item['collection_time']) ? date('Y-m-d H:i:s', $item['collection_time']) : '');
            $sheet->setCellValue('AC' . $row, date('Y-m-d H:i:s', $item['insert_time']));
            $row++;
        }
        foreach (range('A', 'AC') as $column) {
            $sheet->getColumnDimension($column)->setAutoSize(true);
        }
        // 创建Excel文件
        $filename = '低价挂网商品数据' . $file_id . '.xlsx';
        $path = public_path('uploads/exports/');
        $fullPath = $path . $filename;
        if (!is_dir($path))         mkdir($path, 0777, true);

        // 生成 Excel 文件
        $writer = new Xlsx($spreadsheet);
        $writer->save($fullPath);
        // 清理
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet, $writer);
        //释放redis缓存
        Cache::forget('ExportLowPriceGoodsJobs_' . $company_id);
        $Oss                = new Oss();
        $oss_url                = $Oss->uploadFile($filename, $fullPath);
        if ($oss_url)           @unlink($fullPath);
        // 记录下载任务
        $downloadTask = DownloadTaskModel::where(['file_id' => $file_id, 'company_id' => $company_id])->first();
        if ($downloadTask) {
            $downloadTask->url = $oss_url;
            $downloadTask->file_dir_name = $filename;
            $downloadTask->update_time = time();
            $downloadTask->status = 1;
            $downloadTask->save();
        }
        return $oss_url;
    }


    public function failed(\Throwable $exception)
    {
        Log::info('job_error', '违规数据-导出低价挂网数据队列完全失败', ['data' => $this->message_data, 'error' => $exception]);
        if ($this->company_id) {
            //失败后清除缓存
            Cache::forget('ExportLowPriceGoodsJobs_' . $this->company_id);
        }
    }
}