<?php

namespace App\Http\Controllers\manager\Statistics;

use App\Http\Controllers\Controller;
use App\Http\Requests\Manager\Statistics\OverviewPanel as request;
use App\Models\Manager\Process\LowPriceGoods as LowPriceGoodsModel;
use App\Models\Manager\Process\ViolationProduct as ViolationProductModel;
use Illuminate\Support\Facades\DB;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use Illuminate\Support\Carbon;
use App\Models\Manager\Process\LowPriceGoodsMember as LowPriceGoodsMemberModel;
use App\Models\Manager\Process\ViolationProductMember as ViolationProductMemberModel;
use App\Servers\Aliyun\Oss;

/**
 * 报表统计-概览面板
 * @author 唐远望
 * @version   1.0
 * @date      2025-12-26
 * 
 */
class OverviewPanel extends Controller
{

    /**
     * 禁止挂网链接数统计
     * @author 唐远望
     * @version   1.0
     * @date      2025-12-26
     * 
     */
    public function getViolationLinkCount(request $request, ViolationProductModel $violationProductModel)
    {
        $request->scene('getViolationLinkCount')->validate();
        $company_id = request('access_token.company_id', '0');
        $is_admin = request('access_token.is_admin', '0');//是否管理员操作 0=是1=否
        $limit = request('limit', config('page_num', 10));
        $yesterdayStart = Carbon::yesterday()->startOfDay()->getTimestamp(); // 昨天开始时间 00:00:00
        $yesterdayEnd = Carbon::yesterday()->endOfDay()->getTimestamp(); // 昨天结束时间 23:59:59
        $start_time_string = request('start_time','');
        $end_time_string = request('end_time','');
        $start_time = $start_time_string? strtotime($start_time_string.' 00:00:00') : $yesterdayStart;
        $end_time = $end_time_string? strtotime($end_time_string.' 23:59:59') : $yesterdayEnd;
        $todayStart = Carbon::today()->startOfDay()->getTimestamp(); // 今天开始时间 00:00:00
        if($start_time > $todayStart || $end_time > $todayStart) return  json_send(['code' => 'error', 'msg' => '不支持查询今天或以后时间', 'data' => '']);
        //校验开始时间结束时间是否在同一天
        if(date('Y-m-d', $start_time)!= date('Y-m-d', $end_time)) return  json_send(['code' => 'error', 'msg' => '开始时间和结束时间必须在同一天', 'data' => '']);
        // 时间条件
        $map  = [];
        if ($start_time) $map[] = ['insert_time', '>=', $start_time];
        if ($end_time) $map[]   = ['insert_time', '<=', $end_time];
        $result = $violationProductModel->where($map)->where('status', 0)
            ->select(['company_name', DB::raw('count(link_url) as count')])->distinct('link_url')->orderby('count', 'desc')
            ->groupby('company_name')->paginate($limit);
        return  json_send(['code' => 'success', 'msg' => '获取成功', 'data' => $result]);
    }

    /**
     * 禁止挂网链接数统计-导出
     * @author 唐远望
     * @version   1.0
     * @date      2025-12-26
     * 
     */
    public function Violation_export(request $request, ViolationProductModel $violationProductModel)
    {
        $company_id = request('access_token.company_id', '0');
        $is_admin = request('access_token.is_admin', '0');//是否管理员操作 0=是1=否
        $yesterdayStart = Carbon::yesterday()->startOfDay()->getTimestamp(); // 昨天开始时间 00:00:00
        $yesterdayEnd = Carbon::yesterday()->endOfDay()->getTimestamp(); // 昨天结束时间 23:59:59
        $start_time_string = request('start_time','');
        $end_time_string = request('end_time','');
        $start_time = $start_time_string? strtotime($start_time_string.' 00:00:00') : $yesterdayStart;
        $end_time = $end_time_string? strtotime($end_time_string.' 23:59:59') : $yesterdayEnd;
        $todayStart = Carbon::today()->startOfDay()->getTimestamp(); // 今天开始时间 00:00:00
        if($start_time > $todayStart || $end_time > $todayStart) return  json_send(['code' => 'error', 'msg' => '不支持查询今天或以后时间', 'data' => '']);
        //校验开始时间结束时间是否在同一天
        if(date('Y-m-d', $start_time)!= date('Y-m-d', $end_time)) return  json_send(['code' => 'error', 'msg' => '开始时间和结束时间必须在同一天', 'data' => '']);
        // 时间条件
        $map  = [];
        if ($start_time) $map[] = ['insert_time', '>=', $start_time];
        if ($end_time) $map[]   = ['insert_time', '<=', $end_time];
        $result = $violationProductModel->where($map)->where('status', 0)
            ->select(['company_name', DB::raw('count(link_url) as count')])->distinct('link_url')->orderby('count', 'desc')
            ->groupby('company_name')->get()->toarray();
        //执行下载
        $oss_url = $this->Violation_export_download($result);
        return  json_send(['code' => 'success', 'msg' => '获取成功', 'data' => $oss_url]);
    }


    /**
     * 禁止挂网链接数统计-导出下载
     * @author 唐远望
     * @version 1.0
     * @date 2025-06-17
     */
    public function Violation_export_download($data)
    {
        // 创建一个新的 Spreadsheet 对象
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        //合并单元格
        $sheet->mergeCells('A1:B1');
        $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', '链接挂网数量');

        // 填充数据
        $row = 3; // 从第3行开始
        foreach ($data as $item) {
            $sheet->setCellValue('A' . $row, $item['company_name']);
            $sheet->setCellValue('B' . $row, $item['count']);
            $row++;
        }
        $file_id = date('YmdHis');
        // 创建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);

        $Oss                = new Oss();
        $oss_url                = $Oss->uploadFile($filename, $fullPath);
        if ($oss_url)           @unlink($fullPath);
        return $oss_url;
    }

    /**
     * 禁止挂网公司月度统计
     * @author 唐远望
     * @version   1.0
     * @date      2025-12-26
     * 
     */
    public function get_violation_company_count(request $request, ViolationProductModel $violationProductModel)
    {
        $request->scene('get_violation_company_count')->validate();
        $company_id = request('access_token.company_id', '0');
        $is_admin = request('access_token.is_admin', '0');//是否管理员操作 0=是1=否
        $limit = request('limit', config('page_num', 10));
        $yesterdayStart = Carbon::now()->startOfMonth()->getTimestamp(); // 本月第一天 00:00:00
        $yesterdayEnd = Carbon::yesterday()->endOfDay()->getTimestamp(); // 昨天结束时间 23:59:59
        $start_time_string = request('start_time','');
        $end_time_string = request('end_time','');
        $start_time = $start_time_string? strtotime($start_time_string.' 00:00:00') : $yesterdayStart;
        $end_time = $end_time_string? strtotime($end_time_string.' 23:59:59') : $yesterdayEnd;
        $todayStart = Carbon::today()->startOfDay()->getTimestamp(); // 今天开始时间 00:00:00
        if($start_time > $todayStart || $end_time > $todayStart) return  json_send(['code' => 'error', 'msg' => '不支持查询今天或以后时间', 'data' => '']);
        //校验开始时间结束时间是否在同一个月
        if(date('Y-m', $start_time)!= date('Y-m', $end_time)) return  json_send(['code' => 'error', 'msg' => '开始时间和结束时间必须在同一个月', 'data' => '']);
        // 时间条件
        $map  = [];
        if ($start_time) $map[] = ['insert_time', '>=', $start_time];
        if ($end_time) $map[]   = ['insert_time', '<=', $end_time];
        $result = $violationProductModel->where($map)->where('status', 0)
            ->select(['company_name', DB::raw('count(company_name) as count')])->distinct('company_name')->orderby('count', 'desc')
            ->groupby('company_name')->paginate($limit);
        return  json_send(['code' => 'success', 'msg' => '获取成功', 'data' => $result]);
    }

    /**
     * 禁止挂网公司月度统计-导出
     * @author 唐远望
     * @version   1.0
     * @date      2025-12-26
     * 
     */
    public function violation_company_export(request $request, ViolationProductModel $violationProductModel)
    {
        $company_id = request('access_token.company_id', '0');
        $is_admin = request('access_token.is_admin', '0');//是否管理员操作 0=是1=否
        $yesterdayStart = Carbon::now()->startOfMonth()->getTimestamp(); // 本月第一天 00:00:00
        $yesterdayEnd = Carbon::yesterday()->endOfDay()->getTimestamp(); // 昨天结束时间 23:59:59
        $start_time_string = request('start_time','');
        $end_time_string = request('end_time','');
        $start_time = $start_time_string? strtotime($start_time_string.' 00:00:00') : $yesterdayStart;
        $end_time = $end_time_string? strtotime($end_time_string.' 23:59:59') : $yesterdayEnd;
        $todayStart = Carbon::today()->startOfDay()->getTimestamp(); // 今天开始时间 00:00:00
        if($start_time > $todayStart || $end_time > $todayStart) return  json_send(['code' => 'error', 'msg' => '不支持查询今天或以后时间', 'data' => '']);
        //校验开始时间结束时间是否在同一个月
        if(date('Y-m', $start_time)!= date('Y-m', $end_time)) return  json_send(['code' => 'error', 'msg' => '开始时间和结束时间必须在同一个月', 'data' => '']);
        // 时间条件
        $map  = [];
        if ($start_time) $map[] = ['insert_time', '>=', $start_time];
        if ($end_time) $map[]   = ['insert_time', '<=', $end_time];
        $result = $violationProductModel->where($map)->where('status', 0)
            ->select(['company_name', DB::raw('count(company_name) as count')])->distinct('company_name')->orderby('count', 'desc')
            ->groupby('company_name')->get()->toarray();
        //执行下载
        $oss_url = $this->ViolationCompany_export_download($result);
        return  json_send(['code' => 'success', 'msg' => '获取成功', 'data' => $oss_url]);
    }

    /**
     * 禁止挂网公司月度统计-导出下载
     * @author 唐远望
     * @version   1.0
     * @date      2025-12-26
     * 
     */
    public function ViolationCompany_export_download($data)
    {
        // 创建一个新的 Spreadsheet 对象
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        //合并单元格
        $sheet->mergeCells('A1:B1');
        $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', '挂网数量');

        // 填充数据
        $row = 3; // 从第3行开始
        foreach ($data as $item) {
            $sheet->setCellValue('A' . $row, $item['company_name']);
            $sheet->setCellValue('B' . $row, $item['count']);
            $row++;
        }
        $file_id = date('YmdHis');
        // 创建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);

        $Oss                = new Oss();
        $oss_url                = $Oss->uploadFile($filename, $fullPath);
        if ($oss_url)           @unlink($fullPath);
        return $oss_url;
    }


    /*
     * 低价违规挂网链接数统计
     * @author 唐远望
     * @version   1.0
     * @date      2025-12-26
     * 
     */
    public function getLowPriceLinkCount(request $request, LowPriceGoodsModel $LowPriceGoodsModel)
    {
        $request->scene('getLowPriceLinkCount')->validate();
        $company_id = request('access_token.company_id', '0');
        $is_admin = request('access_token.is_admin', '0');//是否管理员操作 0=是1=否
        $limit = request('limit', config('page_num', 10));
        $yesterdayStart = Carbon::yesterday()->startOfDay()->getTimestamp(); // 昨天开始时间 00:00:00
        $yesterdayEnd = Carbon::yesterday()->endOfDay()->getTimestamp(); // 昨天结束时间 23:59:59
        $start_time_string = request('start_time','');
        $end_time_string = request('end_time','');
        $start_time = $start_time_string? strtotime($start_time_string.' 00:00:00') : $yesterdayStart;
        $end_time = $end_time_string? strtotime($end_time_string.' 23:59:59') : $yesterdayEnd;
        $todayStart = Carbon::today()->startOfDay()->getTimestamp(); // 今天开始时间 00:00:00
        if($start_time > $todayStart || $end_time > $todayStart) return  json_send(['code' => 'error', 'msg' => '不支持查询今天或以后时间', 'data' => '']);
        //校验开始时间结束时间是否在同一天
        if(date('Y-m-d', $start_time)!= date('Y-m-d', $end_time)) return  json_send(['code' => 'error', 'msg' => '开始时间和结束时间必须在同一天', 'data' => '']);
        // 时间条件
        $map  = [];
        if ($start_time) $map[] = ['insert_time', '>=', $start_time];
        if ($end_time) $map[]   = ['insert_time', '<=', $end_time];
        $result = $LowPriceGoodsModel->where($map)->where('status', 0)
            ->select(['company_name', DB::raw('count(link_url) as count')])->distinct('link_url')->orderby('count', 'desc')
            ->groupby('company_name')->paginate($limit);
        return  json_send(['code' => 'success', 'msg' => '获取成功', 'data' => $result]);
    }

    /**
     * 低价违规挂网链接数统计-导出
     * @author 唐远望
     * @version   1.0
     * @date      2025-12-26
     * 
     */
    public function low_price_export(request $request, LowPriceGoodsModel $LowPriceGoodsModel)
    {
        $company_id = request('access_token.company_id', '0');
        $is_admin = request('access_token.is_admin', '0');//是否管理员操作 0=是1=否
        $yesterdayStart = Carbon::yesterday()->startOfDay()->getTimestamp(); // 昨天开始时间 00:00:00
        $yesterdayEnd = Carbon::yesterday()->endOfDay()->getTimestamp(); // 昨天结束时间 23:59:59
        $start_time_string = request('start_time','');
        $end_time_string = request('end_time','');
        $start_time = $start_time_string? strtotime($start_time_string.' 00:00:00') : $yesterdayStart;
        $end_time = $end_time_string? strtotime($end_time_string.' 23:59:59') : $yesterdayEnd;
        $todayStart = Carbon::today()->startOfDay()->getTimestamp(); // 今天开始时间 00:00:00
        if($start_time > $todayStart || $end_time > $todayStart) return  json_send(['code' => 'error', 'msg' => '不支持查询今天或以后时间', 'data' => '']);
        //校验开始时间结束时间是否在同一天
        if(date('Y-m-d', $start_time)!= date('Y-m-d', $end_time)) return  json_send(['code' => 'error', 'msg' => '开始时间和结束时间必须在同一天', 'data' => '']);
        // 时间条件
        $map  = [];
        if ($start_time) $map[] = ['insert_time', '>=', $start_time];
        if ($end_time) $map[]   = ['insert_time', '<=', $end_time];
        $result = $LowPriceGoodsModel->where($map)->where('status', 0)
            ->select(['company_name', DB::raw('count(link_url) as count')])->distinct('link_url')->orderby('count', 'desc')
            ->groupby('company_name')->get()->toarray();
        //执行下载
        $oss_url = $this->LowPrice_export_download($result);
        return  json_send(['code' => 'success', 'msg' => '获取成功', 'data' => $oss_url]);
    }

    /**
     * 低价违规挂网链接数统计-导出下载
     * @author 唐远望
     * @version   1.0
     * @date      2025-12-26
     * 
     */
    public function LowPrice_export_download($data)
    {
        // 创建一个新的 Spreadsheet 对象
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        //合并单元格
        $sheet->mergeCells('A1:B1');
        $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', '链接挂网数量');

        // 填充数据
        $row = 3; // 从第3行开始
        foreach ($data as $item) {
            $sheet->setCellValue('A' . $row, $item['company_name']);
            $sheet->setCellValue('B' . $row, $item['count']);
            $row++;
        }
        $file_id = date('YmdHis');
        // 创建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);

        $Oss                = new Oss();
        $oss_url                = $Oss->uploadFile($filename, $fullPath);
        if ($oss_url)           @unlink($fullPath);
        return $oss_url;
    }

    /*
     * 低价违规挂网公司月度统计
     * @author 唐远望
     * @version   1.0
     * @date      2025-12-26
     * 
     */
    public function getLowPriceCompanyCount(request $request, LowPriceGoodsModel $LowPriceGoodsModel)
    {
        $request->scene('getLowPriceCompanyCount')->validate();
        $company_id = request('access_token.company_id', '0');
        $is_admin = request('access_token.is_admin', '0');//是否管理员操作 0=是1=否
        $limit = request('limit', config('page_num', 10));
        $yesterdayStart = Carbon::now()->startOfMonth()->getTimestamp(); // 本月第一天 00:00:00
        $yesterdayEnd = Carbon::yesterday()->endOfDay()->getTimestamp(); // 昨天结束时间 23:59:59
        $start_time_string = request('start_time','');
        $end_time_string = request('end_time','');
        $start_time = $start_time_string? strtotime($start_time_string.' 00:00:00') : $yesterdayStart;
        $end_time = $end_time_string? strtotime($end_time_string.' 23:59:59') : $yesterdayEnd;
        $todayStart = Carbon::today()->startOfDay()->getTimestamp(); // 今天开始时间 00:00:00
        if($start_time > $todayStart || $end_time > $todayStart) return  json_send(['code' => 'error', 'msg' => '不支持查询今天或以后时间', 'data' => '']);
        //校验开始时间结束时间是否在同一个月
        if(date('Y-m', $start_time)!= date('Y-m', $end_time)) return  json_send(['code' => 'error', 'msg' => '开始时间和结束时间必须在同一个月', 'data' => '']);
        // 时间条件
        $map  = [];
        if ($start_time) $map[] = ['insert_time', '>=', $start_time];
        if ($end_time) $map[]   = ['insert_time', '<=', $end_time];
        $result = $LowPriceGoodsModel->where($map)->where('status', 0)
            ->select(['company_name', DB::raw('count(company_name) as count')])->distinct('company_name')->orderby('count', 'desc')
            ->groupby('company_name')->paginate($limit);
        return  json_send(['code' => 'success', 'msg' => '获取成功', 'data' => $result]);
    }

    /**
     * 低价违规挂网公司月度统计-导出
     * @author 唐远望
     * @version   1.0
     * @date      2025-12-26
     * 
     */
    public function low_price_company_export(request $request, LowPriceGoodsModel $LowPriceGoodsModel)
    {
        $company_id = request('access_token.company_id', '0');
        $is_admin = request('access_token.is_admin', '0');//是否管理员操作 0=是1=否
        $yesterdayStart = Carbon::now()->startOfMonth()->getTimestamp(); // 本月第一天 00:00:00
        $yesterdayEnd = Carbon::yesterday()->endOfDay()->getTimestamp(); // 昨天结束时间 23:59:59
        $start_time_string = request('start_time','');
        $end_time_string = request('end_time','');
        $start_time = $start_time_string? strtotime($start_time_string.' 00:00:00') : $yesterdayStart;
        $end_time = $end_time_string? strtotime($end_time_string.' 23:59:59') : $yesterdayEnd;
        $todayStart = Carbon::today()->startOfDay()->getTimestamp(); // 今天开始时间 00:00:00
        if($start_time > $todayStart || $end_time > $todayStart) return  json_send(['code' => 'error', 'msg' => '不支持查询今天或以后时间', 'data' => '']);
        //校验开始时间结束时间是否在同一个月
        if(date('Y-m', $start_time)!= date('Y-m', $end_time)) return  json_send(['code' => 'error', 'msg' => '开始时间和结束时间必须在同一个月', 'data' => '']);
        // 时间条件
        $map  = [];
        if ($start_time) $map[] = ['insert_time', '>=', $start_time];
        if ($end_time) $map[]   = ['insert_time', '<=', $end_time];
        $result = $LowPriceGoodsModel->where($map)->where('status', 0)
            ->select(['company_name', DB::raw('count(company_name) as count')])->distinct('company_name')->orderby('count', 'desc')
            ->groupby('company_name')->get()->toarray();
        //执行下载
        $oss_url = $this->LowPriceCompany_export_download($result);
        return  json_send(['code' => 'success', 'msg' => '获取成功', 'data' => $oss_url]);
    }
    /**
     * 低价违规挂网公司月度统计-导出下载
     * @author 唐远望
     * @version   1.0
     * @date      2025-12-26
     * 
     */
    public function LowPriceCompany_export_download($data)
    {
        // 创建一个新的 Spreadsheet 对象
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        //合并单元格
        $sheet->mergeCells('A1:B1');
        $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', '挂网数量');

        // 填充数据
        $row = 3; // 从第3行开始
        foreach ($data as $item) {
            $sheet->setCellValue('A' . $row, $item['company_name']);
            $sheet->setCellValue('B' . $row, $item['count']);
            $row++;
        }
        $file_id = date('YmdHis');
        // 创建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);

        $Oss                = new Oss();
        $oss_url                = $Oss->uploadFile($filename, $fullPath);
        if ($oss_url)           @unlink($fullPath);
        return $oss_url;
    }

    /**
     * 商品数量趋势
     * @author 唐远望
     * @version   1.0
     * @date      2025-12-29
     * 
     */
    public function get_product_trend(request $request, LowPriceGoodsModel $LowPriceGoodsModel, ViolationProductModel $violationProductModel, LowPriceGoodsMemberModel $LowPriceGoodsMemberModel, ViolationProductMemberModel $ViolationProductMemberModel)
    {
        $request->scene('get_product_trend')->validate();
        $company_id = request('access_token.company_id', '0');
        $is_admin = request('access_token.is_admin', '0');//是否管理员操作 0=是1=否
        // 查询条件
        $map  = [];
        $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', '');
        // 其他条件
        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 ($platform && is_string($platform)) {
            $platform = explode(',', $platform);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('platform', $platform);
            $violationProductModel = $violationProductModel->whereIn('platform', $platform);
        }
        //多选处理状态查询
        if ($processing_status && is_string($processing_status)) {
            $processing_status = explode(',', $processing_status);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('processing_status', $processing_status);
            $violationProductModel = $violationProductModel->whereIn('processing_status', $processing_status);
        }
        //多选状态查询
        if ($status && is_string($status)) {
            $status = explode(',', $status);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('status', $status);
            $violationProductModel = $violationProductModel->whereIn('status', $status);
        }
        //多选店铺名称查询
        if ($store_names && is_string($store_names)) {
            $store_names = explode(',', $store_names);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('store_name', $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);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('online_posting_count', $online_posting_count);
            $violationProductModel = $violationProductModel->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);
            $violationProductModel = $violationProductModel->whereIn('product_specs', $product_specs);
        }
        //多选商品查询
        if ($product_names && is_string($product_names)) {
            $product_names = explode(',', $product_names);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('product_name', $product_names);
            $violationProductModel = $violationProductModel->whereIn('product_name', $product_names);
        }
        //多选公司查询
        if ($company_name && is_string($company_name)) {
            $company_name = explode(',', $company_name);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('company_name', $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 = $LowPriceGoodsMemberModel->whereIn('employee_id', $first_responsible_person)->distinct('lowprice_product_logid')->select('lowprice_product_logid');
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('id', function ($query1) use ($subQuery) {
                $query1->select('lowprice_product_logid')->fromSub($subQuery, 'sub1');
            });
            $subQuery = $ViolationProductMemberModel->whereIn('employee_id', $first_responsible_person)->distinct('lowprice_product_logid')->select('lowprice_product_logid');
            $violationProductModel = $violationProductModel->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)->distinct('lowprice_product_logid')->select('lowprice_product_logid');
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('id', function ($query1) use ($subQuery) {
                $query1->select('lowprice_product_logid')->fromSub($subQuery, 'sub1');
            });
            $subQuery = $ViolationProductMemberModel->whereIn('employee_id', $responsible_person)->distinct('lowprice_product_logid')->select('lowprice_product_logid');
            $violationProductModel = $violationProductModel->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)->distinct('lowprice_product_logid')->select('lowprice_product_logid');
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('id', function ($query1) use ($subQuery) {
                $query1->select('lowprice_product_logid')->fromSub($subQuery, 'sub1');
            });
            $subQuery = $ViolationProductMemberModel->whereIn('employee_id', $source_responsible_person)->distinct('lowprice_product_logid')->select('lowprice_product_logid');
            $violationProductModel = $violationProductModel->whereIn('id', function ($query1) use ($subQuery) {
                $query1->select('lowprice_product_logid')->fromSub($subQuery, 'sub1');
            });
        }
        if ($start_time == '' || $end_time == '') {
            $start_time = Carbon::now()->startOfMonth()->getTimestamp(); // 本月开始时间
            $end_time = Carbon::today()->endOfDay()->getTimestamp(); // 今天结束时间 23:59:59
        } else {
            $start_time = strtotime($start_time . ' 00:00:00');
            $end_time = strtotime($end_time . ' 23:59:59');
        }
        $low_price_result = $LowPriceGoodsModel->where('insert_time', '>=', $start_time)
            ->where('insert_time', '<=', $end_time)->where($map)
            ->select(
                DB::raw("FROM_UNIXTIME(insert_time, '%Y-%m-%d') AS date"),
                DB::raw('COUNT(id) as daily_total'),
            )
            ->groupBy('date')
            ->orderBy('date')
            ->get()->keyBy('date')->toarray();
        $violation_product_result = $violationProductModel->where('insert_time', '>=', $start_time)
            ->where('insert_time', '<=', $end_time)->where($map)
            ->select(
                DB::raw("FROM_UNIXTIME(insert_time, '%Y-%m-%d') AS date"),
                DB::raw('COUNT(id) as daily_total'),
            )
            ->groupBy('date')
            ->orderBy('date')
            ->get()->keyBy('date')->toarray();
        $moth_list = [];
        $key_index = 0;
        for ($i = $start_time; $i <= $end_time; $i += 86400) {
            $key_day = Carbon::createFromTimestamp($i)->format('Y-m-d');
            $moth_list[$key_index]['date'] = $key_day;
            $moth_list[$key_index]['low_price_totle'] = 0;
            $moth_list[$key_index]['violation_product_totle'] = 0;
            $moth_list[$key_index]['product_totle'] = 0;
            if (isset($low_price_result[$key_day])) {
                $moth_list[$key_index]['low_price_totle'] = $low_price_result[$key_day]['daily_total'];
            }
            if (isset($violation_product_result[$key_day])) {
                $moth_list[$key_index]['violation_product_totle'] = $violation_product_result[$key_day]['daily_total'];
            }
            $moth_list[$key_index]['product_totle'] = $moth_list[$key_index]['low_price_totle'] + $moth_list[$key_index]['violation_product_totle'];
            $key_index++;
        }
        return  json_send(['code' => 'success', 'msg' => '获取成功', 'data' => $moth_list]);
    }

    /**
     * 商品数量趋势-导出
     * @author 唐远望
     * @version   1.0
     * @date      2025-12-29
     * 
     */
    public function product_trend_export(request $request, LowPriceGoodsModel $LowPriceGoodsModel, ViolationProductModel $violationProductModel, LowPriceGoodsMemberModel $LowPriceGoodsMemberModel, ViolationProductMemberModel $ViolationProductMemberModel)
    {
        $request->scene('product_trend_export')->validate();
        $company_id = request('access_token.company_id', '0');
        $is_admin = request('access_token.is_admin', '0');//是否管理员操作 0=是1=否
        // 查询条件
        $map  = [];
        $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', '');
        // 其他条件
        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 ($platform && is_string($platform)) {
            $platform = explode(',', $platform);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('platform', $platform);
            $violationProductModel = $violationProductModel->whereIn('platform', $platform);
        }
        //多选处理状态查询
        if ($processing_status && is_string($processing_status)) {
            $processing_status = explode(',', $processing_status);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('processing_status', $processing_status);
            $violationProductModel = $violationProductModel->whereIn('processing_status', $processing_status);
        }
        //多选状态查询
        if ($status && is_string($status)) {
            $status = explode(',', $status);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('status', $status);
            $violationProductModel = $violationProductModel->whereIn('status', $status);
        }
        //多选店铺名称查询
        if ($store_names && is_string($store_names)) {
            $store_names = explode(',', $store_names);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('store_name', $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);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('online_posting_count', $online_posting_count);
            $violationProductModel = $violationProductModel->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);
            $violationProductModel = $violationProductModel->whereIn('product_specs', $product_specs);
        }
        //多选商品查询
        if ($product_names && is_string($product_names)) {
            $product_names = explode(',', $product_names);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('product_name', $product_names);
            $violationProductModel = $violationProductModel->whereIn('product_name', $product_names);
        }
        //多选公司查询
        if ($company_name && is_string($company_name)) {
            $company_name = explode(',', $company_name);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('company_name', $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 = $LowPriceGoodsMemberModel->whereIn('employee_id', $first_responsible_person)->distinct('lowprice_product_logid')->select('lowprice_product_logid');
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('id', function ($query1) use ($subQuery) {
                $query1->select('lowprice_product_logid')->fromSub($subQuery, 'sub1');
            });
            $subQuery = $ViolationProductMemberModel->whereIn('employee_id', $first_responsible_person)->distinct('lowprice_product_logid')->select('lowprice_product_logid');
            $violationProductModel = $violationProductModel->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)->distinct('lowprice_product_logid')->select('lowprice_product_logid');
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('id', function ($query1) use ($subQuery) {
                $query1->select('lowprice_product_logid')->fromSub($subQuery, 'sub1');
            });
            $subQuery = $ViolationProductMemberModel->whereIn('employee_id', $responsible_person)->distinct('lowprice_product_logid')->select('lowprice_product_logid');
            $violationProductModel = $violationProductModel->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)->distinct('lowprice_product_logid')->select('lowprice_product_logid');
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('id', function ($query1) use ($subQuery) {
                $query1->select('lowprice_product_logid')->fromSub($subQuery, 'sub1');
            });
            $subQuery = $ViolationProductMemberModel->whereIn('employee_id', $source_responsible_person)->distinct('lowprice_product_logid')->select('lowprice_product_logid');
            $violationProductModel = $violationProductModel->whereIn('id', function ($query1) use ($subQuery) {
                $query1->select('lowprice_product_logid')->fromSub($subQuery, 'sub1');
            });
        }
        if ($start_time == '' || $end_time == '') {
            $start_time = Carbon::now()->startOfMonth()->getTimestamp(); // 本月开始时间
            $end_time = Carbon::today()->endOfDay()->getTimestamp(); // 今天结束时间 23:59:59
        } else {
            $start_time = strtotime($start_time . ' 00:00:00');
            $end_time = strtotime($end_time . ' 23:59:59');
        }
        $low_price_result = $LowPriceGoodsModel->where('insert_time', '>=', $start_time)
            ->where('insert_time', '<=', $end_time)->where($map)
            ->select(
                DB::raw("FROM_UNIXTIME(insert_time, '%Y-%m-%d') AS date"),
                DB::raw('COUNT(id) as daily_total'),
            )
            ->groupBy('date')
            ->orderBy('date')
            ->get()->keyBy('date')->toarray();
        $violation_product_result = $violationProductModel->where('insert_time', '>=', $start_time)
            ->where('insert_time', '<=', $end_time)->where($map)
            ->select(
                DB::raw("FROM_UNIXTIME(insert_time, '%Y-%m-%d') AS date"),
                DB::raw('COUNT(id) as daily_total'),
            )
            ->groupBy('date')
            ->orderBy('date')
            ->get()->keyBy('date')->toarray();
        $moth_list = [];
        $key_index = 0;
        for ($i = $start_time; $i <= $end_time; $i += 86400) {
            $key_day = Carbon::createFromTimestamp($i)->format('Y-m-d');
            $moth_list[$key_index]['date'] = $key_day;
            $moth_list[$key_index]['low_price_totle'] = 0;
            $moth_list[$key_index]['violation_product_totle'] = 0;
            $moth_list[$key_index]['product_totle'] = 0;
            if (isset($low_price_result[$key_day])) {
                $moth_list[$key_index]['low_price_totle'] = $low_price_result[$key_day]['daily_total'];
            }
            if (isset($violation_product_result[$key_day])) {
                $moth_list[$key_index]['violation_product_totle'] = $violation_product_result[$key_day]['daily_total'];
            }
            $moth_list[$key_index]['product_totle'] = $moth_list[$key_index]['low_price_totle'] + $moth_list[$key_index]['violation_product_totle'];
            $key_index++;
        }
        //执行下载
        $oss_url = $this->product_trend_export_download($moth_list);
        return  json_send(['code' => 'success', 'msg' => '获取成功', 'data' => $oss_url]);
    }

    /**
     * 商品数量趋势-导出下载
     * @author 唐远望
     * @version   1.0
     * @date      2025-12-29
     * 
     */
    public function product_trend_export_download($data)
    {
        // 创建一个新的 Spreadsheet 对象
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        //合并单元格
        $sheet->mergeCells('A1:D1');
        $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', '总商品数量');

        // 填充数据
        $row = 3; // 从第3行开始
        foreach ($data as $item) {
            $sheet->setCellValue('A' . $row, $item['date']);
            $sheet->setCellValue('B' . $row, $item['low_price_totle']);
            $sheet->setCellValue('C' . $row, $item['violation_product_totle']);
            $sheet->setCellValue('D' . $row, $item['product_totle']);
            $row++;
        }

        $file_id = date('YmdHis');
        // 创建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);

        $Oss                = new Oss();
        $oss_url                = $Oss->uploadFile($filename, $fullPath);
        if ($oss_url)           @unlink($fullPath);
        return $oss_url;
    }

    /**
     * 商家数量趋势
     * @author 唐远望
     * @version   1.0
     * @date      2025-12-29
     * 
     */
    public function get_store_trend(Request $request, LowPriceGoodsModel $LowPriceGoodsModel, ViolationProductModel $violationProductModel, LowPriceGoodsMemberModel $LowPriceGoodsMemberModel, ViolationProductMemberModel $ViolationProductMemberModel)
    {
        $request->scene('get_store_trend')->validate();
        $company_id = request('access_token.company_id', '0');
        $is_admin = request('access_token.is_admin', '0');//是否管理员操作 0=是1=否
        // 查询条件
        $map  = [];
        $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', '');
        // 其他条件
        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 ($platform && is_string($platform)) {
            $platform = explode(',', $platform);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('platform', $platform);
            $violationProductModel = $violationProductModel->whereIn('platform', $platform);
        }
        //多选处理状态查询
        if ($processing_status && is_string($processing_status)) {
            $processing_status = explode(',', $processing_status);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('processing_status', $processing_status);
            $violationProductModel = $violationProductModel->whereIn('processing_status', $processing_status);
        }
        //多选状态查询
        if ($status && is_string($status)) {
            $status = explode(',', $status);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('status', $status);
            $violationProductModel = $violationProductModel->whereIn('status', $status);
        }
        //多选店铺名称查询
        if ($store_names && is_string($store_names)) {
            $store_names = explode(',', $store_names);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('store_name', $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);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('online_posting_count', $online_posting_count);
            $violationProductModel = $violationProductModel->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);
            $violationProductModel = $violationProductModel->whereIn('product_specs', $product_specs);
        }
        //多选商品查询
        if ($product_names && is_string($product_names)) {
            $product_names = explode(',', $product_names);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('product_name', $product_names);
            $violationProductModel = $violationProductModel->whereIn('product_name', $product_names);
        }
        //多选公司查询
        if ($company_name && is_string($company_name)) {
            $company_name = explode(',', $company_name);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('company_name', $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 = $LowPriceGoodsMemberModel->whereIn('employee_id', $first_responsible_person)->distinct('lowprice_product_logid')->select('lowprice_product_logid');
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('id', function ($query1) use ($subQuery) {
                $query1->select('lowprice_product_logid')->fromSub($subQuery, 'sub1');
            });
            $subQuery = $ViolationProductMemberModel->whereIn('employee_id', $first_responsible_person)->distinct('lowprice_product_logid')->select('lowprice_product_logid');
            $violationProductModel = $violationProductModel->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)->distinct('lowprice_product_logid')->select('lowprice_product_logid');
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('id', function ($query1) use ($subQuery) {
                $query1->select('lowprice_product_logid')->fromSub($subQuery, 'sub1');
            });
            $subQuery = $ViolationProductMemberModel->whereIn('employee_id', $responsible_person)->distinct('lowprice_product_logid')->select('lowprice_product_logid');
            $violationProductModel = $violationProductModel->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)->distinct('lowprice_product_logid')->select('lowprice_product_logid');
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('id', function ($query1) use ($subQuery) {
                $query1->select('lowprice_product_logid')->fromSub($subQuery, 'sub1');
            });
            $subQuery = $ViolationProductMemberModel->whereIn('employee_id', $source_responsible_person)->distinct('lowprice_product_logid')->select('lowprice_product_logid');
            $violationProductModel = $violationProductModel->whereIn('id', function ($query1) use ($subQuery) {
                $query1->select('lowprice_product_logid')->fromSub($subQuery, 'sub1');
            });
        }
        if ($start_time == '' || $end_time == '') {
            $start_time = Carbon::now()->startOfMonth()->getTimestamp(); // 本月开始时间
            $end_time = Carbon::today()->endOfDay()->getTimestamp(); // 今天结束时间 23:59:59
        } else {
            $start_time = strtotime($start_time . ' 00:00:00');
            $end_time = strtotime($end_time . ' 23:59:59');
        }
        $low_price_result = $LowPriceGoodsModel->where('insert_time', '>=', $start_time)
            ->where('insert_time', '<=', $end_time)->where($map)
            ->select(
                DB::raw("FROM_UNIXTIME(insert_time, '%Y-%m-%d') AS date"),
                DB::raw('COUNT(DISTINCT store_name) as daily_total'),
            )
            ->groupBy('date')
            ->orderBy('date')
            ->get()->keyBy('date')->toarray();
        $violation_product_result = $violationProductModel->where('insert_time', '>=', $start_time)
            ->where('insert_time', '<=', $end_time)->where($map)
            ->select(
                DB::raw("FROM_UNIXTIME(insert_time, '%Y-%m-%d') AS date"),
                DB::raw('COUNT(DISTINCT store_name) as daily_total'),
            )
            ->groupBy('date')
            ->orderBy('date')
            ->get()->keyBy('date')->toarray();
        $moth_list = [];
        $key_index = 0;
        for ($i = $start_time; $i <= $end_time; $i += 86400) {
            $key_day = Carbon::createFromTimestamp($i)->format('Y-m-d');
            $moth_list[$key_index]['date'] = $key_day;
            $moth_list[$key_index]['low_price_store_totle'] = 0;
            $moth_list[$key_index]['violation_product_store_totle'] = 0;
            $moth_list[$key_index]['product_store_totle'] = 0;
            if (isset($low_price_result[$key_day])) {
                $moth_list[$key_index]['low_price_store_totle'] = $low_price_result[$key_day]['daily_total'];
            }
            if (isset($violation_product_result[$key_day])) {
                $moth_list[$key_index]['violation_product_store_totle'] = $violation_product_result[$key_day]['daily_total'];
            }
            $moth_list[$key_index]['product_store_totle'] = $moth_list[$key_index]['low_price_store_totle'] + $moth_list[$key_index]['violation_product_store_totle'];
            $key_index++;
        }
        return  json_send(['code' => 'success', 'msg' => '获取成功', 'data' => $moth_list]);
    }

    /**
     * 商家数量趋势-导出
     * @author 唐远望
     * @version   1.0
     * @date      2025-12-29
     * 
     */
    public function store_trend_export(Request $request, LowPriceGoodsModel $LowPriceGoodsModel, ViolationProductModel $violationProductModel, LowPriceGoodsMemberModel $LowPriceGoodsMemberModel, ViolationProductMemberModel $ViolationProductMemberModel)
    {
        $request->scene('store_trend_export')->validate();
        $company_id = request('access_token.company_id', '0');
        $is_admin = request('access_token.is_admin', '0');//是否管理员操作 0=是1=否
        // 查询条件
        $map  = [];
        $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', '');
        // 其他条件
        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 ($platform && is_string($platform)) {
            $platform = explode(',', $platform);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('platform', $platform);
            $violationProductModel = $violationProductModel->whereIn('platform', $platform);
        }
        //多选处理状态查询
        if ($processing_status && is_string($processing_status)) {
            $processing_status = explode(',', $processing_status);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('processing_status', $processing_status);
            $violationProductModel = $violationProductModel->whereIn('processing_status', $processing_status);
        }
        //多选状态查询
        if ($status && is_string($status)) {
            $status = explode(',', $status);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('status', $status);
            $violationProductModel = $violationProductModel->whereIn('status', $status);
        }
        //多选店铺名称查询
        if ($store_names && is_string($store_names)) {
            $store_names = explode(',', $store_names);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('store_name', $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);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('online_posting_count', $online_posting_count);
            $violationProductModel = $violationProductModel->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);
            $violationProductModel = $violationProductModel->whereIn('product_specs', $product_specs);
        }
        //多选商品查询
        if ($product_names && is_string($product_names)) {
            $product_names = explode(',', $product_names);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('product_name', $product_names);
            $violationProductModel = $violationProductModel->whereIn('product_name', $product_names);
        }
        //多选公司查询
        if ($company_name && is_string($company_name)) {
            $company_name = explode(',', $company_name);
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('company_name', $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 = $LowPriceGoodsMemberModel->whereIn('employee_id', $first_responsible_person)->distinct('lowprice_product_logid')->select('lowprice_product_logid');
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('id', function ($query1) use ($subQuery) {
                $query1->select('lowprice_product_logid')->fromSub($subQuery, 'sub1');
            });
            $subQuery = $ViolationProductMemberModel->whereIn('employee_id', $first_responsible_person)->distinct('lowprice_product_logid')->select('lowprice_product_logid');
            $violationProductModel = $violationProductModel->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)->distinct('lowprice_product_logid')->select('lowprice_product_logid');
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('id', function ($query1) use ($subQuery) {
                $query1->select('lowprice_product_logid')->fromSub($subQuery, 'sub1');
            });
            $subQuery = $ViolationProductMemberModel->whereIn('employee_id', $responsible_person)->distinct('lowprice_product_logid')->select('lowprice_product_logid');
            $violationProductModel = $violationProductModel->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)->distinct('lowprice_product_logid')->select('lowprice_product_logid');
            $LowPriceGoodsModel = $LowPriceGoodsModel->whereIn('id', function ($query1) use ($subQuery) {
                $query1->select('lowprice_product_logid')->fromSub($subQuery, 'sub1');
            });
            $subQuery = $ViolationProductMemberModel->whereIn('employee_id', $source_responsible_person)->distinct('lowprice_product_logid')->select('lowprice_product_logid');
            $violationProductModel = $violationProductModel->whereIn('id', function ($query1) use ($subQuery) {
                $query1->select('lowprice_product_logid')->fromSub($subQuery, 'sub1');
            });
        }
        if ($start_time == '' || $end_time == '') {
            $start_time = Carbon::now()->startOfMonth()->getTimestamp(); // 本月开始时间
            $end_time = Carbon::today()->endOfDay()->getTimestamp(); // 今天结束时间 23:59:59
        } else {
            $start_time = strtotime($start_time . ' 00:00:00');
            $end_time = strtotime($end_time . ' 23:59:59');
        }
        $low_price_result = $LowPriceGoodsModel->where('insert_time', '>=', $start_time)
            ->where('insert_time', '<=', $end_time)->where($map)
            ->select(
                DB::raw("FROM_UNIXTIME(insert_time, '%Y-%m-%d') AS date"),
                DB::raw('COUNT(DISTINCT store_name) as daily_total'),
            )
            ->groupBy('date')
            ->orderBy('date')
            ->get()->keyBy('date')->toarray();
        $violation_product_result = $violationProductModel->where('insert_time', '>=', $start_time)
            ->where('insert_time', '<=', $end_time)->where($map)
            ->select(
                DB::raw("FROM_UNIXTIME(insert_time, '%Y-%m-%d') AS date"),
                DB::raw('COUNT(DISTINCT store_name) as daily_total'),
            )
            ->groupBy('date')
            ->orderBy('date')
            ->get()->keyBy('date')->toarray();
        $moth_list = [];
        $key_index = 0;
        for ($i = $start_time; $i <= $end_time; $i += 86400) {
            $key_day = Carbon::createFromTimestamp($i)->format('Y-m-d');
            $moth_list[$key_index]['date'] = $key_day;
            $moth_list[$key_index]['low_price_store_totle'] = 0;
            $moth_list[$key_index]['violation_product_store_totle'] = 0;
            $moth_list[$key_index]['product_store_totle'] = 0;
            if (isset($low_price_result[$key_day])) {
                $moth_list[$key_index]['low_price_store_totle'] = $low_price_result[$key_day]['daily_total'];
            }
            if (isset($violation_product_result[$key_day])) {
                $moth_list[$key_index]['violation_product_store_totle'] = $violation_product_result[$key_day]['daily_total'];
            }
            $moth_list[$key_index]['product_store_totle'] = $moth_list[$key_index]['low_price_store_totle'] + $moth_list[$key_index]['violation_product_store_totle'];
            $key_index++;
        }
        // 执行下载
        $oss_url = $this->store_trend_export_download($moth_list);
        return  json_send(['code' => 'success', 'msg' => '获取成功', 'data' => $oss_url]);
    }
    /**
     * 商家数量趋势-导出下载
     * @author 唐远望
     * @version   1.0
     * @date      2025-12-29
     * 
     */
    public function store_trend_export_download($data)
    {
        // 创建一个新的 Spreadsheet 对象
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        //合并单元格
        $sheet->mergeCells('A1:D1');
        $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', '总商品商家数量');

        // 填充数据
        $row = 3; // 从第3行开始
        foreach ($data as $item) {
            $sheet->setCellValue('A' . $row, $item['date']);
            $sheet->setCellValue('B' . $row, $item['low_price_store_totle']);
            $sheet->setCellValue('C' . $row, $item['violation_product_store_totle']);
            $sheet->setCellValue('D' . $row, $item['product_store_totle']);
            $row++;
        }

        $file_id = date('YmdHis');
        // 创建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);

        $Oss                = new Oss();
        $oss_url                = $Oss->uploadFile($filename, $fullPath);
        if ($oss_url)           @unlink($fullPath);
        return $oss_url;
    }
}