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

<?php

namespace Modules\OpcData\Http\Controllers\Api;

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

class CarIllegalDownController extends BaseController
{

    public function getList(Request $request) {

        $mineCode = isset($request->mine_code) ? $request->mine_code : null;     // 矿分类
        $stateTime = isset($request->date) ? $request->date : date("Y-m-d"); // 统计日期

        $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, $mineCode);

            $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, $mineCode);
            $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, $mineCode);
            $categories3[]= date("m-d", strtotime("-".$i." day"));;
            $data3['data'][] = $res[0]->con;
        }
        $data3['name']="数量";
        $res2 = [
            'categories'=>array_reverse($categories3),
            'series'=>[array_reverse($data3)],
        ];

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

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

        $sqlStr = "select count(car_num) con from down_car_speed_warn where state_date = '{$stateDate}'";

        return $this->executeSql($sqlStr, 2, $mineCode);

    }

    // 超速详细列表
    public function getCarIllegalList($startTime, $endTime, $mineCode='zaoquan', $page=0, $perPage = 15) {
        $date = '2023-11-07';

        if ($startTime == null) return null;

        $sqlStr = 'SELECT b.numberplate, b.car_type, b.department, a.warn_start_time, a.warn_stop_time, a.act_val, a.warn_val ';
        $sqlStr .= 'FROM down_car_speed_warn a ';
        $sqlStr .= 'join down_car_base_info b on a.car_num = b.car_num ';
        $sqlStr .= 'where a.state_date = "'.$startTime.'"';

        $conn   = 'etl_'.$mineCode;

        $dbResult = $this->executeSql($sqlStr, 4, $mineCode);

        for($i = 0; $i<count($dbResult); $i++) {
            $val = $dbResult[$i];
            $data[] = [
                'car_number' => $val->numberplate,
                'speed' => $val->act_val.'km/h',
                'time' => $val->warn_start_time,
                'place' => $val->department
            ];
        }
        return $data ?? null;

    }


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

        $sqlStr = /** @lang text */
            "select count(case when warn_start_time >= '{$startTime}'
                and warn_start_time < '{$endTime}' then car_num end) con
               from down_car_speed_warn where state_date = '".date("Y-m-d", strtotime($startTime))."'";

        // return $sqlStr;

        return $this->executeSql($sqlStr, 4, $mineCode);

    }

    public function executeSql($sqlStr, $modelname = -1, $mineCode='zaoquan') {

        $conn   = 'etl_'.$mineCode;

        try{
            $opcDB =  DB::connection($conn);
            $dbResult = $opcDB->select($sqlStr);
            return $dbResult;
        } catch (\Exception $e) {
            switch ($modelname) {
                case 1:
                    return $this->error(-1, '统计超速数量出错！');
                case 2:
                    return $this->error(-1, '统计日超速出错！');
                case 4:
                    return $this->error(-1, '获取详细列表出错！');
                default:
                    return $this->error(-1, '未知错误！');
            }

        }
    }
}