zhijiayun/app/Http/Controllers/Manager/Process/ViolationProduct.php

<?php

namespace App\Http\Controllers\Manager\Process;

use App\Http\Controllers\Controller;
use App\Http\Requests\Manager\Process\ViolationProduct as Request;
use App\Models\Manager\Process\ViolationProduct as ViolationProductModel;
use App\Jobs\Manager\Process\ViolationProductJobs;
use App\Models\Manager\Personnel\Employee as EmployeeModel;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\IOFactory;
use App\Models\Manager\Process\ViolationProductMember as ViolationProductMemberModel;
use App\Models\Manager\Citys as CitysModel;
use Illuminate\Support\Facades\DB;

/**
 * 违规处理-违规商品
 * @author    唐远望
 * @version   1.0
 * @date      2025-12-08
 */
class ViolationProduct extends Controller
{

    /**
     * 列表
     * @author    唐远望
     * @version   1.0
     * @date      2025-12-08
     * 
     */
    public function list(Request $request, ViolationProductModel $ViolationProductModel, EmployeeModel $EmployeeModel, ViolationProductMemberModel $ViolationProductMemberModel)
    {
        $request->scene('list')->validate();
        $admin_company_id = request('admin_company_id', '0');
        $company_id = request('access_token.company_id', '0');
        $is_admin = request('access_token.is_admin', '0'); //是否管理员操作 0=是1=否
        $user_id = request('access_token.uid', 0);
        // 查询条件
        $map  = [];
        $limit = request('limit', config('page_num', 10));
        $status    = request('status', '');
        $start_time = request('start_time', '');
        $end_time = request('end_time', '');
        $product_name = request('product_name', '');
        $product_names = request('product_names', '');
        $first_responsible_person = request('first_responsible_person', '');
        $responsible_person = request('responsible_person', '');
        $platform = request('platform', '');
        $company_name = request('company_name', '');
        $company_names = request('company_names', '');
        $store_name = request('store_name', '');
        $store_names = request('store_names', '');
        $source_responsible_person = request('source_responsible_person', '');
        $processing_status = request('processing_status', '');
        $product_specs = request('product_specs', '');
        $online_posting_count = request('online_posting_count', '');
        $category_name = request('category_name', '');
        $id    = request('id', '');
        $online_posting_cunt = request('online_posting_cunt', '');
        $continuous_listing_count = request('continuous_listing_count', '');
        $province_ids = request('province_ids', '');
        $city_ids = request('city_ids', '');
        $shipment_province_ids = request('shipment_province_ids', '');
        $shipment_city_ids = request('shipment_city_ids', '');
        // 时间条件
        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 ($category_name) $map[] = ['category_name', 'like', "%$category_name%"];
        if ($company_name) $map[] = ['company_name', 'like', "%$company_name%"];
        if ($id) $map[] = ['id', '=', $id];
        if ($online_posting_cunt) $map[] = ['online_posting_count', '=', $online_posting_cunt];
        if ($continuous_listing_count) $map[] = ['continuous_listing_count', '=', $continuous_listing_count];

        $violation_product_where = [];
        // 权限判断
        if ($is_admin != 1 && $company_id != 0) {
            $violation_product_where['company_id'] = $company_id;
        } else {
            $violation_product_where['company_id']  = $admin_company_id;
        }
        $ViolationProductModel = $ViolationProductModel->where($violation_product_where);

        //多选平台查询
        if ($platform && is_string($platform)) {
            $platform = explode(',', $platform);
            $ViolationProductModel = $ViolationProductModel->whereIn('platform', $platform);
        }
        //多选处理状态查询
        if ($processing_status && is_string($processing_status)) {
            $processing_status = explode(',', $processing_status);
            $ViolationProductModel = $ViolationProductModel->whereIn('processing_status', $processing_status);
        }
        //多选状态查询
        if ($status && is_string($status)) {
            $status = explode(',', $status);
            $ViolationProductModel = $ViolationProductModel->whereIn('status', $status);
        }
        //多选店铺名称查询
        if ($store_names && is_string($store_names)) {
            $store_names = explode(',', $store_names);
            $ViolationProductModel = $ViolationProductModel->whereIn('store_name', $store_names);
        }
        //多选违规挂网次数查询
        if ($online_posting_count && is_string($online_posting_count)) {
            $online_posting_count = explode(',', $online_posting_count);
            $ViolationProductModel = $ViolationProductModel->whereIn('online_posting_count', $online_posting_count);
        }
        //多选规格查询
        if ($product_specs && is_string($product_specs)) {
            $product_specs = explode(',', $product_specs);
            $ViolationProductModel = $ViolationProductModel->whereIn('product_specs', $product_specs);
        }
        //多选商品查询
        if ($product_names && is_string($product_names)) {
            $product_names = explode(',', $product_names);
            $ViolationProductModel = $ViolationProductModel->whereIn('product_name', $product_names);
        }
        //多选公司查询
        if ($company_names && is_string($company_names)) {
            $company_names = explode(',', $company_names);
            $ViolationProductModel = $ViolationProductModel->whereIn('company_name', $company_names);
        }
        //多选第一责任人
        if ($first_responsible_person && is_string($first_responsible_person)) {
            $first_responsible_person = explode(',', $first_responsible_person);
            $subQuery = $ViolationProductMemberModel->whereIn('employee_id', $first_responsible_person)->distinct('violation_product_logid')->select('violation_product_logid');
            $ViolationProductModel = $ViolationProductModel->whereIn('id', function ($query1) use ($subQuery) {
                $query1->select('violation_product_logid')->fromSub($subQuery, 'sub1');
            });
        }
        //多选责任人
        if ($responsible_person && is_string($responsible_person)) {
            $responsible_person = explode(',', $responsible_person);
            $subQuery = $ViolationProductMemberModel->whereIn('employee_id', $responsible_person)->distinct('violation_product_logid')->select('violation_product_logid');
            $ViolationProductModel = $ViolationProductModel->whereIn('id', function ($query1) use ($subQuery) {
                $query1->select('violation_product_logid')->fromSub($subQuery, 'sub1');
            });
        }
        //多选溯源责任人
        if ($source_responsible_person && is_string($source_responsible_person)) {
            $source_responsible_person = explode(',', $source_responsible_person);
            $subQuery = $ViolationProductMemberModel->whereIn('employee_id', $source_responsible_person)->distinct('violation_product_logid')->select('violation_product_logid');
            $ViolationProductModel = $ViolationProductModel->whereIn('id', function ($query1) use ($subQuery) {
                $query1->select('violation_product_logid')->fromSub($subQuery, 'sub1');
            });
        }
        //多选省份
        if ($province_ids && is_string($province_ids)) {
            $province_ids = explode(',', $province_ids);
            $ViolationProductModel = $ViolationProductModel->whereIn('province_id', $province_ids);
        }
        //多选城市
        if ($city_ids && is_string($city_ids)) {
            $city_ids = explode(',', $city_ids);
            $ViolationProductModel = $ViolationProductModel->whereIn('city_id', $city_ids);
        }
        //多选发货省份
        if ($shipment_province_ids && is_string($shipment_province_ids)) {
            $shipment_province_ids = explode(',', $shipment_province_ids);
            $ViolationProductModel = $ViolationProductModel->whereIn('shipment_province_id', $shipment_province_ids);
        }
        //多选发货城市
        if ($shipment_city_ids && is_string($shipment_city_ids)) {
            $shipment_city_ids = explode(',', $shipment_city_ids);
            $ViolationProductModel = $ViolationProductModel->whereIn('shipment_city_id', $shipment_city_ids);
        }

        if ($is_admin != 1 && $company_id != 0) {
            $result = $ViolationProductModel->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,%");
            })
                ->where($map)
                ->orderByDesc('id')
                ->paginate($limit)->toarray();
        } else {
            $result = $ViolationProductModel
                ->where($map)
                ->orderByDesc('id')
                ->paginate($limit)->toarray();
        }
        // 分配数据
        if (!$result)  json_send(['code' => 'success', 'msg' => '获取成功', 'data' => []]);
        if (isset($result['data']) && count($result['data']) > 0) {
            foreach ($result['data'] as $key => $value) {
                //查询第一责任人名称
                $first_responsible_person = explode(',', $value['first_responsible_person']);
                $first_responsible_person_name = $EmployeeModel->whereIn('id', $first_responsible_person)->pluck('name')->toarray();
                $result['data'][$key]['first_responsible_person_name'] = $first_responsible_person_name;
                //查询责任人名称
                $responsible_person = explode(',', $value['responsible_person']);
                $responsible_person_name = $EmployeeModel->whereIn('id', $responsible_person)->pluck('name')->toarray();
                $result['data'][$key]['responsible_person_name'] = $responsible_person_name;
                //查询来源责任人名称
                $source_responsible_person = explode(',', $value['source_responsible_person']);
                $source_responsible_person_name = $EmployeeModel->whereIn('id', $source_responsible_person)->pluck('name')->toarray();
                $result['data'][$key]['source_responsible_person_name'] = $source_responsible_person_name;
            }
        }
        // 加载模板
        return        json_send(['code' => 'success', 'msg' => '获取成功', 'data' => $result]);
    }

    /**
     * 列表导出
     * @author 唐远望
     * @version 1.0
     * @date 2025-06-17
     */
    public function export_excel(Request $request, ViolationProductModel $ViolationProductModel, EmployeeModel $EmployeeModel, ViolationProductMemberModel $ViolationProductMemberModel)
    {
        $request->scene('export_excel')->validate();
        $admin_company_id = request('admin_company_id', '0');
        $company_id = request('access_token.company_id', '0');
        $is_admin = request('access_token.is_admin', '0'); //是否管理员操作 0=是1=否
        $user_id = request('access_token.uid', 0);
        // 查询条件
        $map  = [];
        $limit = request('limit', config('page_num', 10));
        $status    = request('status', '');
        $start_time = request('start_time', '');
        $end_time = request('end_time', '');
        $product_name = request('product_name', '');
        $product_names = request('product_names', '');
        $first_responsible_person = request('first_responsible_person', '');
        $responsible_person = request('responsible_person', '');
        $platform = request('platform', '');
        $company_name = request('company_name', '');
        $store_name = request('store_name', '');
        $store_names = request('store_names', '');
        $source_responsible_person = request('source_responsible_person', '');
        $processing_status = request('processing_status', '');
        $product_specs = request('product_specs', '');
        $online_posting_count = request('online_posting_count', '');
        $category_name = request('category_name', '');
        $province_ids = request('province_ids', '');
        $city_ids = request('city_ids', '');
        $shipment_province_ids = request('shipment_province_ids', '');
        $shipment_city_ids = request('shipment_city_ids', '');
        // 时间条件
        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 ($category_name) $map[] = ['category_name', 'like', "%$category_name%"];

        $violation_product_where = [];
        // 权限判断
        if ($is_admin != 1 && $company_id != 0) {
            $violation_product_where['company_id'] = $company_id;
        } else {
            $violation_product_where['company_id']  = $admin_company_id;
        }
        $ViolationProductModel = $ViolationProductModel->where($violation_product_where);

        //多选平台查询
        if ($platform && is_string($platform)) {
            $platform = explode(',', $platform);
            $ViolationProductModel = $ViolationProductModel->whereIn('platform', $platform);
        }
        //多选处理状态查询
        if ($processing_status && is_string($processing_status)) {
            $processing_status = explode(',', $processing_status);
            $ViolationProductModel = $ViolationProductModel->whereIn('processing_status', $processing_status);
        }
        //多选状态查询
        if ($status && is_string($status)) {
            $status = explode(',', $status);
            $ViolationProductModel = $ViolationProductModel->whereIn('status', $status);
        }
        //多选店铺名称查询
        if ($store_names && is_string($store_names)) {
            $store_names = explode(',', $store_names);
            $ViolationProductModel = $ViolationProductModel->whereIn('store_name', $store_names);
        }
        //多选违规挂网次数查询
        if ($online_posting_count && is_string($online_posting_count)) {
            $online_posting_count = explode(',', $online_posting_count);
            $ViolationProductModel = $ViolationProductModel->whereIn('online_posting_count', $online_posting_count);
        }
        //多选规格查询
        if ($product_specs && is_string($product_specs)) {
            $product_specs = explode(',', $product_specs);
            $ViolationProductModel = $ViolationProductModel->whereIn('product_specs', $product_specs);
        }
        //多选商品查询
        if ($product_names && is_string($product_names)) {
            $product_names = explode(',', $product_names);
            $ViolationProductModel = $ViolationProductModel->whereIn('product_name', $product_names);
        }
        //多选公司查询
        if ($company_name && is_string($company_name)) {
            $company_name = explode(',', $company_name);
            $ViolationProductModel = $ViolationProductModel->whereIn('company_name', $company_name);
        }
        //多选第一责任人
        if ($first_responsible_person && is_string($first_responsible_person)) {
            $first_responsible_person = explode(',', $first_responsible_person);
            $subQuery = $ViolationProductMemberModel->whereIn('employee_id', $first_responsible_person)->distinct('violation_product_logid')->select('violation_product_logid');
            $ViolationProductModel = $ViolationProductModel->whereIn('id', function ($query1) use ($subQuery) {
                $query1->select('violation_product_logid')->fromSub($subQuery, 'sub1');
            });
        }
        //多选责任人
        if ($responsible_person && is_string($responsible_person)) {
            $responsible_person = explode(',', $responsible_person);
            $subQuery = $ViolationProductMemberModel->whereIn('employee_id', $responsible_person)->distinct('violation_product_logid')->select('violation_product_logid');
            $ViolationProductModel = $ViolationProductModel->whereIn('id', function ($query1) use ($subQuery) {
                $query1->select('violation_product_logid')->fromSub($subQuery, 'sub1');
            });
        }
        //多选溯源责任人
        if ($source_responsible_person && is_string($source_responsible_person)) {
            $source_responsible_person = explode(',', $source_responsible_person);
            $subQuery = $ViolationProductMemberModel->whereIn('employee_id', $source_responsible_person)->distinct('violation_product_logid')->select('violation_product_logid');
            $ViolationProductModel = $ViolationProductModel->whereIn('id', function ($query1) use ($subQuery) {
                $query1->select('violation_product_logid')->fromSub($subQuery, 'sub1');
            });
        }
        //多选省份
        if ($province_ids && is_string($province_ids)) {
            $province_ids = explode(',', $province_ids);
            $ViolationProductModel = $ViolationProductModel->whereIn('province_id', $province_ids);
        }
        //多选城市
        if ($city_ids && is_string($city_ids)) {
            $city_ids = explode(',', $city_ids);
            $ViolationProductModel = $ViolationProductModel->whereIn('city_id', $city_ids);
        }
        //多选发货省份
        if ($shipment_province_ids && is_string($shipment_province_ids)) {
            $shipment_province_ids = explode(',', $shipment_province_ids);
            $ViolationProductModel = $ViolationProductModel->whereIn('shipment_province_id', $shipment_province_ids);
        }
        //多选发货城市
        if ($shipment_city_ids && is_string($shipment_city_ids)) {
            $shipment_city_ids = explode(',', $shipment_city_ids);
            $ViolationProductModel = $ViolationProductModel->whereIn('shipment_city_id', $shipment_city_ids);
        }

        if ($is_admin != 1 && $company_id != 0) {
            $result_totle = $ViolationProductModel->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,%");
            })
                ->where($map)
                ->orderByDesc('id')
                ->count();
        } else {
            $result_totle = $ViolationProductModel->where($map)
                ->orderByDesc('id')
                ->count();
        }

        // 数量过多时，限制导出
        if ($result_totle > 10000) {
            return json_send(['code' => 'error', 'msg' => '导出数据过多，请缩小查询条件']);
        }
        if ($is_admin != 1 && $company_id != 0) {
            $result = $ViolationProductModel->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,%");
            })
                ->where($map)
                ->orderByDesc('id')
                ->get()->toarray();
        } else {
            $result = $ViolationProductModel->where($map)
                ->orderByDesc('id')
                ->get()->toarray();
        }
        // 分配数据
        if (!$result)  return json_send(['code' => 'error', 'msg' => '暂无数据']);
        // 处理责任人展示信息
        $list_data = $this->processing_responsible_person($result);
        // 导出数据
        $this->export_download($list_data);
    }


    /**
     * 处理责任人展示信息
     * @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)
    {
        // 创建一个新的 Spreadsheet 对象
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        //合并单元格
        $sheet->mergeCells('A1:Q1');
        $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', '快照URL');
        $sheet->setCellValue('I2', '商品规格');
        $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', '发货城市');

        $platform_data = [
            '0' => '全部',
            '1' => '淘宝',
            '2' => '京东',
            '3' => '拼多多',
            '4' => '美团',
            '5' => '药师帮',
            '6' => '1药城',
            '7' => '药久久',
        ];
        $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_name']);
            $sheet->setCellValue('F' . $row, $item['inventory']);
            $sheet->setCellValue('G' . $row, $item['sales']);
            $sheet->setCellValue('H' . $row, $item['snapshot_url']);
            $sheet->setCellValue('I' . $row, $item['product_specs']);
            $sheet->setCellValue('J' . $row, $item['online_posting_count']);
            $sheet->setCellValue('K' . $row, $item['continuous_listing_count']);
            $sheet->setCellValue('L' . $row, $item['link_url']);
            $sheet->setCellValue('M' . $row, $item['store_name']);
            $sheet->setCellValue('N' . $row, $item['company_name']);
            $sheet->setCellValue('O' . $row, $item['company_category_name']);
            $sheet->setCellValue('P' . $row, $item['social_credit_code']);
            $sheet->setCellValue('Q' . $row, $item['province_name']);
            $sheet->setCellValue('R' . $row, $item['city_name']);
            $sheet->setCellValue('S' . $row, $source_responsible_person_name);
            $sheet->setCellValue('T' . $row, isset($processing_status_text[$item['processing_status']]) ? $processing_status_text[$item['processing_status']] : '');
            $sheet->setCellValue('U' . $row, isset($status_text[$item['status']]) ? $status_text[$item['status']] : '');
            $sheet->setCellValue('V' . $row, date('Y-m-d H:i:s', $item['insert_time']));
            $sheet->setCellValue('W' . $row, $item['shipment_city_name']);
            $sheet->setCellValue('X' . $row, $item['shipment_city_name']);
            $row++;
        }
        foreach (range('A', 'P') as $column) {
            $sheet->getColumnDimension($column)->setAutoSize(true);
        }
        // 生成 Excel 文件
        $writer = new Xlsx($spreadsheet);

        // 直接输出到浏览器（下载）
        $filename = '禁止挂网商品数据' . date('YmdHis') . '.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');
        $writer->save('php://output');
        exit;
    }


    /**
     * 详情
     * @author    唐远望
     * @version   1.0
     * @date      2025-12-08
     */
    public function detail(Request $request, ViolationProductModel $ViolationProductModel, EmployeeModel $EmployeeModel)
    {
        $request->scene('detail')->validate();
        $admin_company_id = request('admin_company_id', '0');
        $company_id = request('access_token.company_id', '0');
        $is_admin = request('access_token.is_admin', '0'); //是否管理员操作 0=是1=否
        // 接收参数
        $id = request('id', 0);
        $map = ['id' => $id];
        // 权限判断
        if ($is_admin != 1 && $company_id != 0) {
            $map['company_id'] = $company_id;
        } else {
            $map['company_id']  = $admin_company_id;
        }
        $data = $ViolationProductModel->where($map)->first();
        if (!$data)     return json_send(['code' => 'error', 'msg' => '记录不存在']);
        //查询第一责任人名称
        $first_responsible_person = explode(',', $data->first_responsible_person);
        $first_responsible_person_name = $EmployeeModel->whereIn('id', $first_responsible_person)->pluck('name')->toarray();
        $data->first_responsible_person_name = $first_responsible_person_name;
        //查询责任人名称
        $responsible_person = explode(',', $data->responsible_person);
        $responsible_person_name = $EmployeeModel->whereIn('id', $responsible_person)->pluck('name')->toarray();
        $data->responsible_person_name = $responsible_person_name;
        //查询来源责任人名称
        $source_responsible_person = explode(',', $data->source_responsible_person);
        $source_responsible_person_name = $EmployeeModel->whereIn('id', $source_responsible_person)->pluck('name')->toarray();
        $data->source_responsible_person_name = $source_responsible_person_name;
        // 加载模板
        return json_send(['code' => 'success', 'msg' => '获取成功', 'data' => $data]);
    }

    /**
     * 添加
     * @author    唐远望
     * @version   1.0
     * @date      2025-12-08
     * 
     */
    public function add(Request $request, ViolationProductModel $ViolationProductModel)
    {
        $request->scene('add')->validate();
        $admin_company_id = request('admin_company_id', '0');
        $company_id = request('access_token.company_id', '0');
        $is_admin = request('access_token.is_admin', '0'); //是否管理员操作 0=是1=否
        // 接收数据
        $all_data = request()->all();
        $store_scope = request('store_scope', '');
        $all_data['store_scope'] = $store_scope;
        //查询是否存在
        $map = ['product_name' => $all_data['product_name'], 'product_specs' => $all_data['product_specs']];
        // 权限判断
        if ($is_admin != 1 && $company_id != 0) {
            $map['company_id'] = $company_id;
            $all_data['company_id'] = $company_id;
        } else {
            $map['company_id']  = $admin_company_id;
            $all_data['company_id'] = $admin_company_id;
        }
        $data = $ViolationProductModel->where($map)->first();
        if ($data)     return json_send(['code' => 'error', 'msg' => '记录已存在']);
        // 写入数据表
        $result     =  $ViolationProductModel->addLowPriceGoods($all_data);
        // 如果操作失败
        if (!$result)     return json_send(['code' => 'error', 'msg' => '新增失败']);
        // 告知结果
        return json_send(['code' => 'success', 'msg' => '新增成功']);
    }

    /**
     * 修改
     * @author    唐远望
     * @version   1.0
     * @date      2025-12-08
     * 
     */
    public function edit(Request $request, ViolationProductModel $ViolationProductModel)
    {
        $request->scene('edit')->validate();
        $admin_company_id = request('admin_company_id', '0');
        $company_id = request('access_token.company_id', '0');
        $is_admin = request('access_token.is_admin', '0'); //是否管理员操作 0=是1=否
        // 接收参数
        $id         = request('id', 0);
        // 接收数据
        $all_data = request()->all();
        $store_scope = request('store_scope', '');
        $all_data['store_scope'] = $store_scope;
        //查询是否存在
        $map = ['product_name' => $all_data['product_name'], 'product_specs' => $all_data['product_specs']];
        // 权限判断
        if ($is_admin != 1 && $company_id != 0) {
            $map['company_id'] = $company_id;
            $all_data['company_id'] = $company_id;
        } else {
            $map['company_id']  = $admin_company_id;
            $all_data['company_id'] = $admin_company_id;
        }
        $data = $ViolationProductModel->where($map)->where('id', '!=', $id)->first();
        if ($data)     return json_send(['code' => 'error', 'msg' => '记录已存在']);
        // 更新数据表
        $where = ['id' => $id];
        $result =  $ViolationProductModel->updateLowPriceGoods($where, $all_data);
        // 如果操作失败
        if (!$result)     return json_send(['code' => 'error', 'msg' => '修改失败']);
        // 告知结果
        return json_send(['code' => 'success', 'msg' => '修改成功']);
    }

    /**
     * 修改状态
     * @author    唐远望
     * @version   1.0
     * @date      2025-12-08
     * 
     */
    public function set_status(Request $request, ViolationProductModel $ViolationProductModel)
    {
        // 验证参数
        $request->scene('set_status')->validate();
        $admin_company_id = request('admin_company_id', '0');
        $company_id = request('access_token.company_id', '0');
        $is_admin = request('access_token.is_admin', '0'); //是否管理员操作 0=是1=否
        // 接收数据
        $id                = request('id', 0);
        $status            = request('status', 0);
        // 查询用户
        $where = ['id' => $id];
        // 权限判断
        if ($is_admin != 1 && $company_id != 0) {
            $where['company_id'] = $company_id;
        } else {
            $where['company_id'] = $admin_company_id;
        }
        $ViolationProduct = $ViolationProductModel->where($where)->first();
        if (!$ViolationProduct) return json_send(['code' => 'error', 'msg' => '记录不存在']);
        $ViolationProduct->status = $status;
        $ViolationProduct->update_time = time();
        // 执行修改
        $result            =  $ViolationProduct->save();
        // 提示新增失败
        if (!$result)    return json_send(['code' => 'error', 'msg' => '设置失败']);
        // 告知结果
        return             json_send(['code' => 'success', 'msg' => '设置成功']);
    }

    /**
     * 修改处理状态
     * @author    唐远望
     * @version   1.0
     * @date      2025-12-08
     * 
     */
    public function set_processing_status(Request $request, ViolationProductModel $ViolationProductModel)
    {
        // 验证参数
        $request->scene('set_processing_status')->validate();
        $admin_company_id = request('admin_company_id', '0');
        $company_id = request('access_token.company_id', '0');
        $is_admin = request('access_token.is_admin', '0'); //是否管理员操作 0=是1=否
        // 接收数据
        $id                = request('id', 0);
        $processing_status = request('processing_status', 0);
        // 查询用户
        $where = ['id' => $id];
        // 权限判断
        if ($is_admin != 1 && $company_id != 0) {
            $where['company_id'] = $company_id;
        } else {
            $where['company_id'] = $admin_company_id;
        }
        $ViolationProduct = $ViolationProductModel->where($where)->first();
        if (!$ViolationProduct) return json_send(['code' => 'error', 'msg' => '记录不存在']);
        $ViolationProduct->processing_status = $processing_status;
        $ViolationProduct->update_time = time();
        // 执行修改
        $result            =  $ViolationProduct->save();
        // 提示新增失败
        if (!$result)    return json_send(['code' => 'error', 'msg' => '设置失败']);
        // 告知结果
        return             json_send(['code' => 'success', 'msg' => '设置成功']);
    }

    /**
     * 删除
     * @author    唐远望
     * @version   1.0
     * @date      2025-12-08
     * 
     */
    public function delete(Request $request, ViolationProductModel $ViolationProductModel)
    {
        // 验证参数
        $request->scene('delete')->validate();
        $admin_company_id = request('admin_company_id', '0');
        $company_id = request('access_token.company_id', '0');
        $is_admin = request('access_token.is_admin', '0'); //是否管理员操作 0=是1=否
        // 接收数据
        $id = request('id', 0);
        // 查询用户
        $where = ['id' => $id];
        // 权限判断
        if ($is_admin != 1 && $company_id != 0) {
            $where['company_id'] = $company_id;
        } else {
            $where['company_id'] = $admin_company_id;
        }
        $ViolationProduct = $ViolationProductModel->where($where)->first();
        if (!$ViolationProduct) return json_send(['code' => 'error', 'msg' => '记录不存在']);
        // 执行删除
        $result =  $ViolationProduct->delete();
        // 提示删除失败
        if (!$result)    return json_send(['code' => 'error', 'msg' => '删除失败']);
        // 告知结果
        return             json_send(['code' => 'success', 'msg' => '删除成功']);
    }

    /**
     * 执行数据清洗
     * @author    唐远望
     * @version   1.0
     * @date      2025-12-11
     * 
     */
    public function data_cleaning(Request $request)
    {
        // 验证参数
        $request->scene('data_cleaning')->validate();
        $admin_company_id = request('admin_company_id', '0');
        $company_id = request('access_token.company_id', '0');
        $is_admin = request('access_token.is_admin', '0'); //是否管理员操作 0=是1=否
        $admin_id   = request('access_token.uid', 0); //用户ID
        // 权限判断
        if ($is_admin != 1 && $company_id != 0) {
        } else {
            $company_id  = $admin_company_id;
        }
        $message_data = ['page' => '1', 'limit' => 50, 'admin_id' => $admin_id, 'is_admin' => $is_admin, 'company_id' => $company_id];
        ViolationProductJobs::dispatch($message_data);
        // ViolationProductJobs::dispatchSync($message_data);
        // 告知结果
        return             json_send(['code' => 'success', 'msg' => '执行成功']);
    }



    /**
     * 下载导入模板
     * @author    唐远望
     * @version   1.0
     * @date      2025-12-31
     * 
     */
    public function download_template()
    {
        // 创建一个新的 Spreadsheet 对象
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        //合并单元格
        $sheet->mergeCells('A1:P1');
        $sheet->setCellValue('A1', '禁止挂网商品导入模板'); // 设置合并后的单元格内容
        // 获取合并后的单元格样式对象
        $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', '销量');
        $sheet->setCellValue('J2', '库存');
        $sheet->setCellValue('K2', '快照URL');
        $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', '检索采集日期(Y-m-d)*');
        $sheet->setCellValue('U2', '发货省份');
        $sheet->setCellValue('V2', '发货城市');
        // 生成 Excel 文件
        $writer = new Xlsx($spreadsheet);

        // 直接输出到浏览器（下载）
        $filename = '禁止挂网商品导入模板.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');
        $writer->save('php://output');
        exit;
    }


    /**
     * 导入Excel数据
     * @author    唐远望
     * @version   1.0
     * @date      2025-12-31
     * 
     */
    public function import_data(Request $request, ViolationProductModel $ViolationProductModel, EmployeeModel $EmployeeModel, CitysModel $CitysModel)
    {
        $request->scene('import_data')->validate();
        $admin_company_id = request('admin_company_id', '0');
        $company_id = request('access_token.company_id', '0');
        $is_admin = request('access_token.is_admin', '0'); //是否管理员操作 0=是1=否
        $file = $request->file('file');
        // 加载Excel文件
        $spreadsheet = IOFactory::load($file->getPathname());
        $sheet = $spreadsheet->getActiveSheet();
        // 获取所有数据
        $data = $sheet->toArray();
        if (empty($data) || count($data) < 2) return json_send(['code' => 'error', 'msg' => '导入数据为空']);
        $result = $EmployeeModel->select(['id', 'name'])->get()->keyBy('name')->toarray();
        $platform_data = $ViolationProductModel->platform_data();
        $province_id_data = $CitysModel->get_province_id_list();
        $city_id_data = $CitysModel->get_city_id_list();

        // 过滤空行
        $data = array_filter($data, function ($row) {
            // 过滤掉所有值都为空的行
            return !empty(array_filter($row, function ($value) {
                return !is_null($value) && $value !== '';
            }));
        });

        DB::beginTransaction();
        try {
            foreach ($data as $key => $item) {
                $key_num = $key + 1;
                if ($key < 2) continue;
                //强制必传参数校验
                $res_data = $this->import_data_check($key, $item);
                if ($res_data) return  json_send($res_data);
                $first_responsible_person_id = [];
                $first_responsible_person_name =   $item[0];
                if ($first_responsible_person_name) {
                    $first_responsible_person_name = explode(',', $first_responsible_person_name);
                    foreach ($first_responsible_person_name as $k => $v) {
                        $v = trim($v);
                        if (isset($result[$v])) {
                            $first_responsible_person_id[] = $result[$v]['id'];
                        }
                    }
                }
                $responsible_person_id = [];
                $responsible_person_name =   $item[1];
                if ($responsible_person_name) {
                    $responsible_person_name = explode(',', $responsible_person_name);
                    foreach ($responsible_person_name as $k => $v) {
                        $v = trim($v);
                        if (isset($result[$v])) {
                            $responsible_person_id[] = $result[$v]['id'];
                        }
                    }
                }
                $source_responsible_person_id = [];
                $source_responsible_person_name = $item[18];
                if ($source_responsible_person_name) {
                    $source_responsible_person_name = explode(',', $source_responsible_person_name);
                    foreach ($source_responsible_person_name as $k => $v) {
                        $v = trim($v);
                        if (isset($result[$v])) {
                            $source_responsible_person_id[] = $result[$v]['id'];
                        }
                    }
                }
                $province_name = $item[16];
                //特殊地区1级移除市
                if ($province_name && in_array($province_name, ['北京市', '天津市', '上海市', '重庆市'])) {
                    //移除市这个字符
                    $province_name = trim(str_replace('市', '', $province_name));
                } else if ($province_name && in_array($province_name, ['北京', '天津', '上海', '重庆'])) {
                } else if ($province_name && in_array($province_name, ['内蒙古', '广西', '西藏', '新疆', '宁夏'])) {
                    switch ($province_name) {
                        case '内蒙古':
                            $province_name = '内蒙古自治区';
                            break;
                        case '广西':
                            $province_name = '广西壮族自治区';
                            break;
                        case '西藏':
                            $province_name = '西藏自治区';
                            break;
                        case '新疆':
                            $province_name = '新疆维吾尔自治区';
                            break;
                        case '宁夏':
                            $province_name = '宁夏回族自治区';
                            break;
                    }
                } else if ($province_name && in_array($province_name, ['内蒙古自治区', '广西壮族自治区', '西藏自治区', '新疆维吾尔自治区', '宁夏回族自治区'])) {
                } else if (strpos($province_name, '省') === false) {
                    //是否存在市省，如果不存在则补全
                    if (strpos($province_name, '省') === false) {
                        $province_name = $province_name . '省';
                    }
                }

                if (!isset($province_id_data[$province_name])) return json_send(['code' => 'error', 'msg' => "第{$key_num}行省份信息不完整", 'data' => $item]);
                $city_name = $item[17];
                if (!isset($city_id_data[$city_name])) return json_send(['code' => 'error', 'msg' => "第{$key_num}行城市信息不完整", 'data' => $item]);

                $shipment_province_name = isset($item[20]) ? $item[20] : '';
                //特殊地区1级移除市
                if ($shipment_province_name && in_array($province_name, ['北京市', '天津市', '上海市', '重庆市'])) {
                    //移除市这个字符
                    $shipment_province_name = trim(str_replace('市', '', $shipment_province_name));
                } else if ($shipment_province_name && in_array($shipment_province_name, ['北京', '天津', '上海', '重庆'])) {
                } else if ($shipment_province_name && in_array($shipment_province_name, ['内蒙古', '广西', '西藏', '新疆', '宁夏'])) {
                    switch ($shipment_province_name) {
                        case '内蒙古':
                            $shipment_province_name = '内蒙古自治区';
                            break;
                        case '广西':
                            $shipment_province_name = '广西壮族自治区';
                            break;
                        case '西藏':
                            $shipment_province_name = '西藏自治区';
                            break;
                        case '新疆':
                            $shipment_province_name = '新疆维吾尔自治区';
                            break;
                        case '宁夏':
                            $shipment_province_name = '宁夏回族自治区';
                            break;
                    }
                } else if ($shipment_province_name && in_array($shipment_province_name, ['内蒙古自治区', '广西壮族自治区', '西藏自治区', '新疆维吾尔自治区', '宁夏回族自治区'])) {
                } else if (strpos($shipment_province_name, '省') === false) {
                    //是否存在市省，如果不存在则补全
                    if (strpos($shipment_province_name, '省') === false) {
                        $shipment_province_name = $shipment_province_name . '省';
                    }
                }
                $shipment_province_id ='0';
                if (!isset($province_id_data[$shipment_province_name])){
                    $shipment_province_name = '';
                }else{
                    $shipment_province_id = $province_id_data[$shipment_province_name]['id'];
                }
                $shipment_city_id = '0';
                $shipment_city_name = isset($item[21]) ? $item[21] : '';
                if (!isset($city_id_data[$shipment_city_name])){
                    $shipment_city_name = '';
                }else{
                    $shipment_city_id = $city_id_data[$shipment_city_name]['id'];
                }

                // 权限判断
                if ($is_admin != 1 && $company_id != 0) {
                    $insert_product_data['company_id'] = $company_id;
                } else {
                    $insert_product_data['company_id']  = $admin_company_id;
                }
                $insert_product_data['first_responsible_person'] = !empty($first_responsible_person_id) ? implode(',', $first_responsible_person_id) : ''; //第一责任人ID集合
                $insert_product_data['responsible_person'] = !empty($responsible_person_id) ? implode(',', $responsible_person_id) : ''; //责任人ID集合
                $insert_product_data['platform'] = isset($platform_data[$item[2]]) ? $platform_data[$item[2]] : '0'; // 平台
                $insert_product_data['category_name'] = $item[3] ? $item[4] : ''; // 商品分类
                $insert_product_data['product_name'] = $item[4]; // 商品名称
                $insert_product_data['product_specs'] = $item[5]; // 商品规格
                $insert_product_data['online_posting_count'] = isset($item[6]) ? $item[6] : 1; // 累计挂网次数
                $insert_product_data['continuous_listing_count'] = isset($item[7]) ? $item[7] : 1; // 连续挂网次数
                $insert_product_data['sales'] = isset($item[8]) ? $item[8] : 0; // 销量
                $insert_product_data['inventory'] = isset($item[9]) ? $item[9] : 0; // 库存
                $insert_product_data['snapshot_url'] = isset($item[10]) ? $item[10] : ''; // 快照URL
                $insert_product_data['link_url'] = $item[11]; // 链接地址
                $insert_product_data['store_name'] = $item[12]; // 店铺名称
                $insert_product_data['company_name'] = $item[13]; // 公司名称
                $insert_product_data['company_category_name'] = isset($item[14]) ? $item[14] : ''; // 公司分类
                $insert_product_data['social_credit_code'] = $item[15]; // 信用代码
                $insert_product_data['province_name'] = $item[16]; // 省份
                $insert_product_data['province_id'] = isset($province_id_data[$province_name]) ? $province_id_data[$province_name]['id'] : 0; // 省份ID
                $insert_product_data['city_name'] = $item[17]; // 城市
                $insert_product_data['city_id'] = isset($city_id_data[$city_name]) ? $city_id_data[$city_name]['id'] : 0; // 城市ID
                $insert_product_data['area_info'] = ''; // 详细地址
                $insert_product_data['source_responsible_person'] = !empty($source_responsible_person_id) ? implode(',', $source_responsible_person_id) : ''; //溯源责任人ID集合
                $insert_product_data['processing_status'] = 1; //处理状态1=待处理2=购买中3=已溯源4=回收凭据已上传5=已回收6=拒绝回收7=已下架8=无法处理
                $insert_product_data['status'] = 0; //状态0=有效=无效
                $insert_product_data['insert_time'] = time();
                $insert_product_data['source_id'] = 0; // 原始数据ID
                $insert_product_data['scrape_date'] = $item[19]; // 检索采集日期
                $insert_product_data['shipment_province_id'] = $shipment_province_id; // 发货省份id
                $insert_product_data['shipment_province_name'] = $shipment_province_name; // 发货省份
                $insert_product_data['shipment_city_id'] = $shipment_city_id; // 发货城市id
                $insert_product_data['shipment_city_name'] = $shipment_city_name; // 发货城市
                //插入数据
                $ViolationProductModel->addViolationProduct($insert_product_data, true);
            }
            DB::commit();
            return json_send(['code' => 'success', 'msg' => '导入成功']);
            // 成功处理...
        } catch (\Exception $e) {
            DB::rollBack();
            // 错误处理...
            return json_send(['code' => 'error', 'msg' => '导入失败', 'data' => $e->getMessage()]);
        }
    }

    /**
     * 导入Excel数据必传参数校验
     * @author    唐远望
     * @version   1.0
     * @date      2025-12-31
     * 
     */
    private function import_data_check($key, $item)
    {
        $key = $key + 1;
        if (!$item[2]) {
            return ['code' => 'error', 'msg' => "第{$key}行平台不能为空", 'data' => $item];
        }
        if (!$item[4]) {
            return ['code' => 'error', 'msg' => "第{$key}行商品名称不能为空", 'data' => $item];
        }
        if (!$item[5]) {
            return ['code' => 'error', 'msg' => "第{$key}行商品规格不能为空", 'data' => $item];
        }
        if (!$item[11]) {
            return ['code' => 'error', 'msg' => "第{$key}行链接地址不能为空", 'data' => $item];
        }
        if (!$item[12]) {
            return ['code' => 'error', 'msg' => "第{$key}行店铺名称不能为空", 'data' => $item];
        }
        if (!$item[13]) {
            return ['code' => 'error', 'msg' => "第{$key}行公司名称不能为空", 'data' => $item];
        }
        if (!$item[15]) {
            return ['code' => 'error', 'msg' => "第{$key}行信用代码不能为空", 'data' => $item];
        }
        if (!$item[16]) {
            return ['code' => 'error', 'msg' => "第{$key}行省份不能为空", 'data' => $item];
        }
        if (!$item[17]) {
            return ['code' => 'error', 'msg' => "第{$key}行城市不能为空", 'data' => $item];
        }
        if (!$item[19]) {
            return ['code' => 'error', 'msg' => "第{$key}行检索采集日期不能为空", 'data' => $item];
        } else {
            //校验格式是否为Y-m-d
            if (!preg_match('/^[0-9]{4}-[0-9]{2}-[0-9]{2}$/', $item[19])) {
                return ['code' => 'error', 'msg' => "第{$key}行检索采集日期格式不正确", 'data' => $item];
            }
            if (strtotime($item[19] . ' 00:00:00') > time()) {
                return ['code' => 'error', 'msg' => "第{$key}行检索采集日期不能大于当前日期", 'data' => $item];
            }
        }
    }
}