video_system/Modules/OpcData/Http/Controllers/Api/CarDownCurrInfoController.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 CarDownCurrInfoController extends BaseController
{
    public function getList(Request $request) {
        $mineCode = isset($request->mine_code) ? $request->mine_code : null;     // 矿分类
        $carList = $this->getCarInfoList($mineCode);

        $dataTotal = $this->getCarCon($mineCode);

        $data = [
            "total"=>reset($dataTotal[0]),
            "list"=>$carList
        ];
        return json_encode($data);
    }


    public function driverList(Request $request) {
        $mineCode = isset($request->mine_code) ? $request->mine_code : null;     // 矿分类
        $sqlStr = "select per_name, dep from down_personnel_info";
        $list = $this->executeSql($sqlStr);

        $sqlStr = 'SELECT dep, count(1) con FROM `down_personnel_info` GROUP BY dep';
        $groupCon = $this->executeSql($sqlStr);

        $data = [
            'charts'=>$groupCon,
            'list'=>$list
        ];

        return json_encode($data);
    }

    public function taskList(Request  $request) {
        $sqlStr = /** @lang text */
            "SELECT apply_time, task_state, task_info, dest, t1.car_model, t2.per_name, t2.dep
               FROM down_task_info t
               left join down_car_base_info t1 on t.car_num = t1.car_num
               left join down_personnel_info t2 on t.driver_num = t2.per_num
              where DATE_FORMAT(apply_time,'%Y-%m-%d') = (select max(DATE_FORMAT(apply_time,'%Y-%m-%d')) FROM down_task_info)
              ORDER BY task_state desc, apply_time";
        $list = $this->executeSql($sqlStr);

        $sqlStr = /** @lang text */
            "select task_state, count(1) con from down_task_info
              where DATE_FORMAT(apply_time,'%Y-%m-%d') =
                    (select max(DATE_FORMAT(apply_time,'%Y-%m-%d'))
                       FROM down_task_info)
              GROUP BY task_state";
        $groupCon = $this->executeSql($sqlStr);

        $data = [
            'charts'=>$groupCon,
            'list'=>$list
        ];

        return json_encode($data);
    }

    public function getCarCon($mineCode='zaoquan') {
        $sqlStr = /** @lang text */
            "SELECT count(1) con FROM down_car_site_status where out_time is null or out_time < in_time";

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

    // 当前信息详情
    public function getCarInfoList($mineCode='zaoquan', $page=0, $perPage = 15) {

        if ($mineCode == null) return null;

        $sqlStr = /** @lang text */
            "SELECT numberplate,
                    in_time,
                    speed_avg,
                    car_type,
                    t1.task_info,
                    t2.per_name
               FROM down_car_site_status t
               left join down_task_info t1 on t.task_num = t1.task_num
               left join down_personnel_info t2 on t.per_num = t2.per_num
              where site_tag = 1 ";

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

        for($i = 0; $i<count($dbResult); $i++) {
            $val = $dbResult[$i];
            $data[] = [
                'car_num' => $val->numberplate,
//                'speed' => $val->speed_avg.'km/h',
                'car_type' => $val->car_type,
                'task_info' => $val->task_info,
                'per_name' => $val->per_name,
                'in_time' => $val->in_time
            ];
        }
        return $data ?? null;

    }


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

        }
    }
}