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;     // 矿分类
        $statisDate = isset($request->date) ? $request->date : date("Y-m-d"); // 统计日期

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

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

            // 今日(每4小时统计)
            $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;

            // 昨日(每4小时统计)
            $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++) {

            $date = date("Y-m-d", strtotime("-".(7-$i)." day"));

            $res = $this->getCarIllegalCount($date, $mineCode);
            $categories3[]= substr($date, -5);
            $data3['data'][] = $res[0]->con ?? 0;
        }
        $data3['name']="数量";
        $res2 = [
            'categories'=>$categories3,
            'series'=>[$data3],
        ];

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

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

        $sqlStr =  /** @lang text */
        "select count(t.car_num) con from (
            select car_num from down_car_speed_warn where statis_date = '".date("Y-m-d", strtotime($statisDate))."'
            union all
            select car_num from down_car_run_red_light_warn where statis_date = '".date("Y-m-d", strtotime($statisDate))."'
        ) t";

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

    }

    // 详细列表
    public function getCarIllegalList($statisDate, $mineCode='zaoquan', $page=0, $perPage = 15) {

        if ($statisDate == null) return null;
        $sqlStr =  /** @lang text */

            "select * from (
                    select '闯红灯' illegal_type,
                           b.numberplate,
                           a.warn_start_time,
                           department,
                           c.light_name illegal_info
                      from down_car_run_red_light_warn a
                        join down_car_base_info b on a.car_num = b.car_num
                        join down_light_info c on a.light_num = c.light_num
                     where statis_date = '".date("Y-m-d", strtotime($statisDate))."'
                     union all
                     select '超速' illegal_type,
                            b.numberplate,
                            a.warn_start_time,
                            b.department,
                            concat(a.act_val, ' km/h')
                     from down_car_speed_warn a
                     join down_car_base_info b on a.car_num = b.car_num
                    where statis_date = '".date("Y-m-d", strtotime($statisDate))."'
                ) t order by warn_start_time desc";

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

        for($i = 0; $i<count($dbResult); $i++) {
            $val = $dbResult[$i];
            $data[] = [
                'car_number' => $val->numberplate,
                'type' => $val->illegal_type,
                'speed' => $val->department,
                'time' => $val->warn_start_time,
                'place' => $val->illegal_info
            ];
        }
        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 (
                select car_num, warn_start_time from down_car_speed_warn where statis_date = '".date("Y-m-d", strtotime($startTime))."'
                union all
                select car_num, warn_start_time from down_car_run_red_light_warn where statis_date = '".date("Y-m-d", strtotime($startTime))."'
            ) t";

        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, '未知错误！');
            }

        }
    }
}