video_system/Modules/Log/Http/Controllers/LogController.php

<?php

namespace Modules\Log\Http\Controllers;

use Illuminate\Contracts\Support\Renderable;
use Illuminate\Http\Request;
use Illuminate\Routing\Controller;
use DB;
use Modules\Admin\Auxiliary\View\TableAuxiliary;
use Modules\Admin\Http\Controllers\BaseController;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class LogController extends BaseController
{
    /**
     * Display a listing of the resource.
     * @return Renderable
     */
    public function index(Request $request)
    {
        $this->menusActive[0] = 'adminLog';

        //查询当前登录用户权限内的区域
        $user = DB::table('users')->where('staff_num',$request->user()->staff_num)->first();
        $mine_array = explode(';',$user->mine_role);//权限内的矿区
        $mine_all = DB::table('mine_list')->where('deleted_at',null)->get();//所有区域
        $mine_use = [];//权限内矿区下所有区域
        foreach($mine_all as $k=>$v){
            if(in_array(explode('|',$v->degree)[0],$mine_array)){
                $mine_use[] = $v->id;
            }
        }

        $log_list = DB::table('log')
            ->select('log.*','mine_list.title','mine_list.degree','camera_list.camera_name')
            ->leftJoin('mine_list','mine_list.id','=','log.mine_id')
            ->leftJoin('camera_list','camera_list.id','=','log.camera_id')
            ->whereIn('log.mine_id',$mine_use)
            ->orderBy('log.created_at', 'desc');

        if ($request->has('mine_id') && $request->input('mine_id')) {
            $log_list = $log_list->where('mine_list.degree','like',$request->input('mine_id').'|%');
        }

        if ($request->has('camera_name') && $request->input('camera_name')) {
            $log_list = $log_list->where('camera_list.camera_name','like','%'.$request->input('camera_name').'%');
        }

        if ($request->has('status') && $request->input('status')) {
            if($request->input('status') == 1){
                $log_list = $log_list->where('log.status',0);
            }elseif($request->input('status') == 2){
                $log_list = $log_list->where('log.status',1);
            }
        }

        if($request->input('start_time') != null && $request->input('end_time') != null){
            $start_time = $request->input('start_time')." 00:00:00";
            $end_time = $request->input('start_time')." 23:59:59";
            $log_list = $log_list->whereBetween('log.created_at',[$start_time,$end_time]);
        }

        if($request->input('start_time') == null && $request->input('end_time') != null){
            $end_time = $request->input('start_time')." 23:59:59";
            $log_list = $log_list->where('log.created_at','<=',$request->input('end_time'));
        }

        if($request->input('start_time') != null && $request->input('end_time') == null){
            $start_time = $request->input('start_time')." 00:00:00";
            $log_list = $log_list->where('log.created_at','>=',$request->input('start_time'));
        }

        $log_list = $log_list->paginate(50);
        if(count($log_list)>0){
            for($i=0;$i<count($log_list);$i++){
                $degree = explode('|',$log_list[$i]->degree);
                $mine = DB::table('mine_list')->where('id',$degree[0])->get();
                $log_list[$i]->mine_name = $mine[0]->title;
                $log_list[$i]->camera_id = $log_list[$i]->camera_id."(".$degree[0].")";

                $title = '';

                if(count($degree) == 1){
                    for($j=0;$j<count($degree);$j++){
                        $title = $title.DB::table('mine_list')->where('id',$degree[$j])->get()[0]->title.'/';
                    }
                }
                if(count($degree) > 1){
                    for($j=1;$j<count($degree);$j++){
                        $title = $title.DB::table('mine_list')->where('id',$degree[$j])->get()[0]->title.'/';
                    }
                }
                $log_list[$i]->title = substr($title,0,strlen($title)-1);
            }
        }
        //权限内矿区列表
        $mine_list = DB::table('mine_list')->whereIn('id',$mine_array)->get()->toArray();
        $mine_search = [];
        foreach($mine_list as $k=>$v){
            $mine_search[$v->id] = $v->title;
        }

        //摄像头状态
        $status = [1=>'正常',2=>'异常'];

        $tableObj = new TableAuxiliary('log', $log_list);
        $tableObj->topActions = ['export_condition'];
        $tableObj->actionBtns = [];
        $tableObj->search('select', 'mine_id', '选择矿区', $mine_search);
        $tableObj->search('input', 'camera_name', '摄像头名称');
        $tableObj->search('select', 'status', '摄像头状态', $status);
        $tableObj->search('date', 'start_time', '开始时间');
        $tableObj->search('date', 'end_time', '结束时间');
        $tableObj->column('mine_name', '矿区名称');
        $tableObj->column('title', '区域名称');
        $tableObj->column('camera_id', '摄像头ID');
        $tableObj->column('camera_name', '摄像头名称');
        $tableObj->column('status', '状态',function ($state, $item) {
            if ($item->status == 0) {
                $state = '正常';
            }else{
                $state = '异常';
            }
            return $state;
        });
        $tableObj->column('log', '返回信息');
        $tableObj->column('created_at', '开始时间');
        return $this->tableList($tableObj);
    }

    public function export(Request $request){
        //查询当前登录用户权限内的区域
        $user = DB::table('users')->where('staff_num',$request->user()->staff_num)->first();
        $mine_array = explode(';',$user->mine_role);//权限内的矿区
        $mine_all = DB::table('mine_list')->where('deleted_at',null)->get();//所有区域
        $mine_use = [];//权限内矿区下所有区域
        foreach($mine_all as $k=>$v){
            if(in_array(explode('|',$v->degree)[0],$mine_array)){
                $mine_use[] = $v->id;
            }
        }

        $log_list = DB::table('log')
            ->select('log.*','mine_list.title','mine_list.degree','camera_list.camera_name')
            ->leftJoin('mine_list','mine_list.id','=','log.mine_id')
            ->leftJoin('camera_list','camera_list.id','=','log.camera_id')
            ->whereIn('log.mine_id',$mine_use)
            ->orderBy('camera_list.camera_name', 'desc');

        if ($request->has('mine_id') && $request->input('mine_id')) {
            $log_list = $log_list->where('mine_list.degree','like',$request->input('mine_id').'|%');
            $mine_export = DB::table('mine_list')->where('id',$request->input('mine_id'))->get()[0]->title;
        }else{
            $mine_export = '全矿';
        }

        if ($request->has('camera_name') && $request->input('camera_name')) {
            $log_list = $log_list->where('camera_list.camera_name','like','%'.$request->input('camera_name').'%');
        }

        if ($request->has('status') && $request->input('status')) {
            if($request->input('status') == 1){
                $log_list = $log_list->where('log.status',0);
                $status_export = '正常摄像头';
            }elseif($request->input('status') == 2){
                $log_list = $log_list->where('log.status',1);
                $status_export = '异常摄像头';
            }
        }else{
            $status_export = '';
        }

        if($request->input('start_time') != null && $request->input('end_time') != null){
            $start_time = $request->input('start_time')." 00:00:00";
            $end_time = $request->input('start_time')." 23:59:59";
            $log_list = $log_list->whereBetween('log.created_at',[$start_time,$end_time]);
            $time_export = $request->input('start_time').'到'.$request->input('end_time');
        }

        elseif($request->input('start_time') == null && $request->input('end_time') != null){
            $end_time = $request->input('start_time')." 23:59:59";
            $log_list = $log_list->where('log.created_at','<=',$request->input('end_time'));
            $time_export = '截止'.$request->input('end_time').'前';
        }

        elseif($request->input('start_time') != null && $request->input('end_time') == null){
            $start_time = $request->input('start_time')." 00:00:00";
            $log_list = $log_list->where('log.created_at','>=',$request->input('start_time'));
            $time_export = $request->input('end_time').'至今';
        }

        else{
            $time_export = '';
        }

        $log_list = $log_list->get()->toArray();

        if(count($log_list)>0){
            for($i=0;$i<count($log_list);$i++){
                $degree = explode('|',$log_list[$i]->degree);
                $mine = DB::table('mine_list')->where('id',$degree[0])->get();
                $log_list[$i]->mine_name = $mine[0]->title;
                if($log_list[$i]->status == 0){
                    $log_list[$i]->status = '正常';
                }else{
                    $log_list[$i]->status = '异常';
                }
            }
        }

        //去掉重复数据改为次数
        $new_log_list = [];
        foreach($log_list as $log){
            $repeat = 0;//不在新数组
            if(count($new_log_list)>0){
                for($i=0;$i<count($new_log_list);$i++){
                    if($log->camera_name==$new_log_list[$i]['camera_name'] && $log->status==$new_log_list[$i]['status']){
                        $new_log_list[$i]['count']++;
                        $repeat = 1;//在新数组
                    }
                }
            }

            if($repeat == 0){//不在新数组
                $size = count($new_log_list);
                $new_log_list[$size]['mine_name'] = $log->mine_name;
                $new_log_list[$size]['title'] = $log->title;
                $new_log_list[$size]['camera_id'] = $log->camera_id;
                $new_log_list[$size]['camera_name'] = $log->camera_name;
                $new_log_list[$size]['status'] = $log->status;
                $new_log_list[$size]['log'] = $log->log;
                $new_log_list[$size]['created_at'] = $log->created_at;
                $new_log_list[$size]['count'] = 1;
            }
        }
        $spreadsheet = new Spreadsheet();
        $sheet       = $spreadsheet->getActiveSheet();
        $sheet->getDefaultRowDimension()->setRowHeight(40);
        $sheet->getColumnDimension('A')->setWidth(25);
        $sheet->getColumnDimension('B')->setWidth(25);
        $sheet->getColumnDimension('C')->setWidth(20);
        $sheet->getColumnDimension('D')->setWidth(40);
        $sheet->getColumnDimension('E')->setWidth(20);
        $sheet->getColumnDimension('F')->setWidth(20);
        $sheet->getColumnDimension('G')->setWidth(30);
        $sheet->getColumnDimension('H')->setWidth(20);
        $sheet->setCellValue('A1', $mine_export.$status_export.$time_export.'访问记录')
            ->mergeCells('A1:H1')->getStyle('A1:H1')->getFont()->setSize(22)->setBold(true);
        $sheet->setCellValue('A2', '矿区名称');
        $sheet->setCellValue('B2', '区域名称');
        $sheet->setCellValue('C2', '摄像头ID');
        $sheet->setCellValue('D2', '摄像头名称');
        $sheet->setCellValue('E2', '状态');
        $sheet->setCellValue('F2', '返回信息');
        $sheet->setCellValue('G2', '访问时间');
        $sheet->setCellValue('H2', '次数');

        $rowNum = 3;
        $row_start = 3;
        $row_end = 0;
        if(count($new_log_list)>0){
            for($i=0;$i<count($new_log_list);$i++){
                $row_start = $rowNum;
                $sheet->setCellValue('A'.$rowNum, $new_log_list[$i]['mine_name']);
                $sheet->setCellValue('B'.$rowNum, $new_log_list[$i]['title']);
                $sheet->setCellValue('C'.$rowNum, $new_log_list[$i]['camera_id']);
                $sheet->setCellValue('D'.$rowNum, $new_log_list[$i]['camera_name']);
                $sheet->setCellValue('E'.$rowNum, $new_log_list[$i]['status']);
                $sheet->setCellValue('F'.$rowNum, $new_log_list[$i]['log']);
                $sheet->setCellValue('G'.$rowNum, $new_log_list[$i]['created_at']);
                $sheet->setCellValue('H'.$rowNum, $new_log_list[$i]['count']);
                $rowNum++;
            }
            $row_end = $rowNum-1;
            $sheet->mergeCells('A'.$row_start.':A'.$row_end.'')->mergeCells('B'.$row_start.':B'.$row_end.'')->mergeCells('C'.$row_start.':C'.$row_end.'')->mergeCells('D'.$row_start.':D'.$row_end.'')->mergeCells('E'.$row_start.':E'.$row_end.'')->mergeCells('F'.$row_start.':F'.$row_end.'')->mergeCells('G'.$row_start.':G'.$row_end.'')->mergeCells('H'.$row_start.':H'.$row_end.'');
        }

        $sheet->getStyle('A1:H' . ($rowNum-1))
            ->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER)->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->getStyle('A2:H2' . ($rowNum))->getFont()->setSize(18);
        $sheet->getStyle('A3:H' . ($rowNum))->getFont()->setSize(14);
        $sheet->getStyle('A1:H1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFD9D9D9');

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出07Excel文件
        header('Content-Disposition: attachment;filename='.$mine_export.$status_export.$time_export.'访问记录'.'.xlsx');//告诉浏览器输出浏览器名称
        header('Cache-Control: max-age=0');//禁止缓存
        $writer = new Xlsx($spreadsheet);
        unset($spreadsheet);
        $writer->save('php://output');
//        return redirect('/admin/product/list');
        dd($new_log_list);
        dd($mine_export.$status_export.$time_export.'访问记录');
    }
}