video_system/Modules/OpcData/Http/Controllers/Api/CarIllegalController.php

<?php

namespace Modules\OpcData\Http\Controllers\Api;

use App\Http\Controllers\Api\BaseController;
use Illuminate\Contracts\Support\Renderable;
use Illuminate\Routing\Controller;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;

class CarIllegalController  extends BaseController
{

    public function getList(Request $request) {

        $mineCode = isset($request->mine_code) ? $request->mine_code : null;     // 矿分类
        $listStartTime = isset($request->date) ? $request->date.' 00:00:00' : date("Y-m-d 00:00:00");   // 开始时间
        $listEndTime = isset($request->date) ? $request->date.' 23:59:59' : date("Y-m-d 23:59:59");     // 结束时间

        $page = isset($request->page) ? $request->page : 0;     // 分页
        $perPage = isset($request->perPage) ? $request->perPage : 20;     // 每页显示几条

        // 超速违章统计
        for($i = 1; $i < 7; $i++) {

            // 今日
            $date = date("Y-m-d");
            $stime = sprintf("%02d",($i * 4 - 4)).":00:00";
            $etime = ($i * 4) == 24 ? "23:59:59":sprintf("%02d",($i * 4)).":00:00";

            $startTime = $date.' '.$stime;
            $endTime = $date.' '.$etime;

            $res = $this->getCarIllegalByTimeSpan($startTime, $endTime);

            $categories1[]= substr($etime, 0, -3);
            $data1[] = $res[0]->con;

            // 昨日
            $yesterday = date("Y-m-d", strtotime("-1 day"));
            $yStartTime = $yesterday.' '.$stime;
            $yEndTime = $yesterday.' '.$etime;

            $res = $this->getCarIllegalByTimeSpan($yStartTime, $yEndTime);
            $data2[] = $res[0]->con;
        }
        // 超速违章统计
        $res1 = [
            'categories'=>$categories1,
            'series'=>[
                [
                    "name"=>"今日",
                    "data"=> $data1
                ],
                [
                    "name"=>"昨日",
                    "data"=> $data2
                ],
            ],
        ];

        // 一周内超速趋势
        for($i = 1; $i < 7; $i++) {

            $stime = date("Y-m-d 00:00:00", strtotime("-".$i." day"));
            $etime = date("Y-m-d 23:59:59", strtotime("-".$i." day"));

            $res = $this->getCarIllegalByTimeSpan($stime, $etime);
            $categories3[]= date("m-d", strtotime("-".$i." day"));;
            $data3['data'][] = $res[0]->con;
        }
        $res2 = [
            'categories'=>array_reverse($categories3),
            'series'=>array_reverse($data3),
        ];

        // 违章数据列表
        $dataList = $this->getCarIllegalList($listStartTime, $listEndTime, $page, $perPage);
        $dataTotal = $this->getCarIllegalCount($mineCode);
        $data = [
            "total"=>$dataTotal[0]->total,
            "speeding_con"=>$res1,
            "speeding_week"=>$res2,
            "detail_list"=>$dataList
        ];
        return json_encode($data);
    }

    // 今日超速统计
    public function getCarIllegalCount($mineCode='zaoquan') {

        $sqlStr = "SELECT count(DISTINCT state_date, plate_number, DATE_FORMAT(illegal_time,'%Y-%m-%d %h'), point_name, illegal_speed, speed_limit) total  ";
        $sqlStr .= "FROM cars_violation ";
        $sqlStr .= "WHERE DATE_FORMAT(illegal_time,'%Y-%m-%d') = curdate() ";
        $sqlStr .= "AND plate_number != '无车牌' ";

        $conn   = 'etl_'.$mineCode;
        try {
            $opcDB =  DB::connection($conn);
            $dbResult = $opcDB->select($sqlStr);
            return $dbResult;
        } catch (\Exception $e) {
            return $this->error(-1, '今日超速统计出错！');
        }
    }
    // 超速详细列表
    public function getCarIllegalList($startTime, $endTime, $mineCode='zaoquan', $page=0, $perPage = 15) {
        $sqlStr = "SELECT DISTINCT state_date, plate_number, substr(illegal_time, 1, 16 ) illegal_time, point_name, illegal_speed, speed_limit ";
        $sqlStr .= "FROM cars_violation ";
        $sqlStr .= "WHERE illegal_time >='".$startTime."' and illegal_time < '".$endTime."' ";
        $sqlStr .= "AND plate_number != '无车牌' ";
        $sqlStr .= "order by illegal_time desc ";
        // $sqlStr .= "limit ".$page.", ".$perPage."";
        // return $sqlStr;
        $conn   = 'etl_zaoquan';
        try{
            $opcDB =  DB::connection($conn);
            $dbResult = $opcDB->select($sqlStr);
            for($i = 0; $i<count($dbResult); $i++) {
                $val = $dbResult[$i];
                $data[] = [
                    'car_number' => $val->plate_number,
                    'speed' => $val->illegal_speed.'km/h',
                    'time' => $val->illegal_time,
                    'place' => $val->point_name
                ];
            }
            return $data;
        } catch (\Exception $e) {
            return $this->error(-1, '超速详细列表出错！');
        }
    }

    // 根据时段统计超速量
    public function getCarIllegalByTimeSpan($startTime, $endTime, $mineCode='zaoquan') {

        $sqlStr = "select count(case when illegal_time >= '".$startTime."' and illegal_time < '".$endTime."' then 1 end) con ";
        // $sqlStr .= "count(case when illegal_time >= '".$startTime."' and illegal_time < '".$endTime."' then 1 end) con_yestday ";
        $sqlStr .= "from ( SELECT distinct state_date, plate_number, substr(illegal_time, 1, 13) illegal_time, point_name, illegal_speed, speed_limit ";
        $sqlStr .= "from cars_violation where state_date ='".date("Y-m-d", strtotime($startTime))."' ";
        $sqlStr .= "and plate_number != '无车牌' ";
        $sqlStr .= ") t";

        // return $sqlStr;

        $conn   = 'etl_zaoquan';
        try{
            $opcDB =  DB::connection($conn);
            $dbResult = $opcDB->select($sqlStr);
            return $dbResult;
        } catch (\Exception $e) {
            return $this->error(-1, '根据时段统计超速量出错！');
        }

    }

}