zhijiayun/app/Http/Controllers/Manager/Statistics/OverviewPanel.php

<?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;

/**
 * 报表统计-概览面板
 * @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();
        $limit = request('limit', config('page_num', 10));
        $start_time = request('start_time', '');
        $end_time = request('end_time', '');
        // 时间条件
        $map  = [];
        if ($start_time) $map[] = ['insert_time', '>=', strtotime($start_time)];
        if ($end_time) $map[]   = ['insert_time', '<=', strtotime($end_time)];
        $result = $violationProductModel->where($map)->where('status', 0)
            ->select(['company_name', DB::raw('count(link_url) as count')])->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)
    {
        $start_time = request('start_time', '');
        $end_time = request('end_time', '');
        // 时间条件
        $map  = [];
        if ($start_time) $map[] = ['insert_time', '>=', strtotime($start_time)];
        if ($end_time) $map[]   = ['insert_time', '<=', strtotime($end_time)];
        $result = $violationProductModel->where($map)->where('status', 0)
            ->select(['company_name', DB::raw('count(link_url) as count')])->orderby('count', 'desc')
            ->groupby('company_name')->get()->toarray();
        //执行下载
        $this->Violation_export_download($result);
    }


    /**
     * 禁止挂网链接数统计-导出下载
     * @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++;
        }
        // 生成 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-26
     * 
     */
    public function get_violation_company_count(request $request, ViolationProductModel $violationProductModel)
    {
        $request->scene('get_violation_company_count')->validate();
        $limit = request('limit', config('page_num', 10));
        $start_time = request('start_time', '');
        $end_time = request('end_time', '');
        // 时间条件
        $map  = [];
        if ($start_time) $map[] = ['insert_time', '>=', strtotime($start_time)];
        if ($end_time) $map[]   = ['insert_time', '<=', strtotime($end_time)];
        $result = $violationProductModel->where($map)->where('status', 0)
            ->select(['company_name', DB::raw('count(company_name) as count')])->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)
    {
        $start_time = request('start_time', '');
        $end_time = request('end_time', '');
        // 时间条件
        $map  = [];
        if ($start_time) $map[] = ['insert_time', '>=', strtotime($start_time)];
        if ($end_time) $map[]   = ['insert_time', '<=', strtotime($end_time)];
        $result = $violationProductModel->where($map)->where('status', 0)
            ->select(['company_name', DB::raw('count(company_name) as count')])->orderby('count', 'desc')
            ->groupby('company_name')->get()->toarray();
        //执行下载
        $this->ViolationCompany_export_download($result);
    }

    /**
     * 禁止挂网公司月度统计-导出下载
     * @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++;
        }
        // 生成 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-26
     * 
     */
    public function getLowPriceLinkCount(request $request, LowPriceGoodsModel $LowPriceGoodsModel)
    {
        $request->scene('getLowPriceLinkCount')->validate();
        $limit = request('limit', config('page_num', 10));
        $start_time = request('start_time', '');
        $end_time = request('end_time', '');
        // 时间条件
        $map  = [];
        if ($start_time) $map[] = ['insert_time', '>=', strtotime($start_time)];
        if ($end_time) $map[]   = ['insert_time', '<=', strtotime($end_time)];
        $result = $LowPriceGoodsModel->where($map)->where('status', 0)
            ->select(['company_name', DB::raw('count(link_url) as count')])->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)
    {
        $start_time = request('start_time', '');
        $end_time = request('end_time', '');
        // 时间条件
        $map  = [];
        if ($start_time) $map[] = ['insert_time', '>=', strtotime($start_time)];
        if ($end_time) $map[]   = ['insert_time', '<=', strtotime($end_time)];
        $result = $LowPriceGoodsModel->where($map)->where('status', 0)
            ->select(['company_name', DB::raw('count(link_url) as count')])->orderby('count', 'desc')
            ->groupby('company_name')->get()->toarray();
        //执行下载
        $this->LowPrice_export_download($result);
    }

    /**
     * 低价违规挂网链接数统计-导出下载
     * @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++;
        }
        // 生成 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-26
     * 
     */
    public function getLowPriceCompanyCount(request $request, LowPriceGoodsModel $LowPriceGoodsModel)
    {
        $request->scene('getLowPriceCompanyCount')->validate();
        $limit = request('limit', config('page_num', 10));
        $start_time = request('start_time', '');
        $end_time = request('end_time', '');
        // 时间条件
        $map  = [];
        if ($start_time) $map[] = ['insert_time', '>=', strtotime($start_time)];
        if ($end_time) $map[]   = ['insert_time', '<=', strtotime($end_time)];
        $result = $LowPriceGoodsModel->where($map)->where('status', 0)
            ->select(['company_name', DB::raw('count(company_name) as count')])->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)
    {
        $start_time = request('start_time', '');
        $end_time = request('end_time', '');
        // 时间条件
        $map  = [];
        if ($start_time) $map[] = ['insert_time', '>=', strtotime($start_time)];
        if ($end_time) $map[]   = ['insert_time', '<=', strtotime($end_time)];
        $result = $LowPriceGoodsModel->where($map)->where('status', 0)
            ->select(['company_name', DB::raw('count(company_name) as count')])->orderby('count', 'desc')
            ->groupby('company_name')->get()->toarray();
        //执行下载
        $this->LowPriceCompany_export_download($result);
    }
    /**
     * 低价违规挂网公司月度统计-导出下载
     * @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++;
        }
        // 生成 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;
    }
}