video_system/Modules/Admin/Services/ApiService.php

<?php
/**
 * Created by PhpStorm.
 * User: qiuzijian
 * Date: 6/22/24
 * Time: 12:34 PM
 */

namespace Modules\Admin\Services;

use App\Enum\ApiEnum;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Modules\Admin\Entities\Menu;

class ApiService{

    public function __construct()
    {

    }

    //网格化整改
    public static function editConition($params)
    {
        $result['status'] = true;
        $result['msg']    = ApiEnum::RETURN_SUCCESS;
        $result['data']   = [];

        $update = [
            'zgzp' => $params['zgpz'],
            'jcwt' => '无'
        ];

        DB::connection('mysql_fwe10')->table('uf_zhxx_qlcxjjl')->where('id',$params['id'])->update($update);

        DB::connection('mysql_fwe10')->table('uf_zhxx_qlctzgg')->where('gglx_obj',$params['id'])->update(['IS_DELETE'=>1]);

        return $result;
    }

    //网格化录入
    public static function addConition($params)
    {
        $result['status'] = true;
        $result['msg']    = ApiEnum::RETURN_SUCCESS;
        $result['data']   = [];

        //区域字典录入
        $area = DB::connection('mysql_fwe10')->table('uf_zhxx_qlcqyzd')->where('szxmc',$params['xjdw'])->where('IS_DELETE',0)->orderBy('areaid','desc')->get();

        $is_insert = true;

        if(count($area) > 0){
            for($i=0;$i<count($area);$i++){
                if($params['jcqy'] == $area[$i]->qymc){
                    $is_insert = false;
                }
            }
        }

        if($is_insert){
            $data['ID'] = mt_rand(pow(10, 18), pow(10, 18) + 999999999);
            $data['FORM_DATA_ID'] = $data['ID'];
            $data['DATA_INDEX'] = 0.0;
            $data['CREATE_TIME'] = date('Y-m-d H:i:s');
            $data['TENANT_KEY'] = 't1zz9w8165';
            $data['IS_DELETE'] = 0;
            $data['DELETE_TYPE'] = 0;
            $data['FT_STATUS'] = 0;

            if(count($area) > 0){
                preg_match_all('/[0-9]+/', $area[0]->areaid, $matches);
                if(isset($matches[0][0])){
                    $data['areaid'] = $params['xjdw'].($matches[0][0] + 1);
                }
            }else{
                $data['areaid'] = $params['xjdw'].'1';
            }
            $data['qymc'] = $params['jcqy'];

            $data['szxmc'] = $params['xjdw'];

            DB::connection('mysql_fwe10')->table('uf_zhxx_qlcqyzd')->insert($data);
        }

        //巡检记录录入
        $id = mt_rand(pow(10, 18), pow(10, 18) + 999999999);
        $insert = [
            'ID' => $id,
            'FORM_DATA_ID' => $id,
            'DATA_INDEX' => 0.0,
            'CREATE_TIME' => date('Y-m-d H:i:s'),
            'TENANT_KEY' => 't1zz9w8165',
            'IS_DELETE' => 0,
            'DELETE_TYPE' => 0,
            'FT_STATUS' => 0,

            'xjqy' => $params['jcqy'],
            'dkpz' => $params['dkpz'],
            'lrr'  => $params['lrmc'].'|'.$params['lrr'],
            'jclx' => $params['jclx'],
            'lrsj' => $params['lrsj'],
            'dwmc' => $params['dwmc'],
            'jcwt' => $params['jcwt'],
            'xjdw' => $params['xjdw'],
            'wtms' => $params['wtms'],
            'sjr' => $params['sjr'],
            'lat' => $params['lat'],
            'lng' => $params['lng'],
            'dkwz' => $params['dkwz']
        ];

        if($params['jcwt'] == '有'){
            $insert['zgr'] = $params['zgr'];
        }

        DB::connection('mysql_fwe10')->table('uf_zhxx_qlcxjjl')->insert($insert);

        //发通知
        if($params['jcwt'] == '有'){
            $tzgg_first = DB::connection('mysql_fwe10')->table('uf_zhxx_qlcxjjl')->orderBy('CREATE_TIME','desc')->limit(1)->get();
            $id = mt_rand(pow(10, 18), pow(10, 18) + 999999999);
            $zgr_name = explode('|',$params['zgr'])[0];
            $tzgg = [
                'ID' => $id,
                'FORM_DATA_ID' => $id,
                'DATA_INDEX' => 0.0,
                'CREATE_TIME' => date('Y-m-d H:i:s'),
                'TENANT_KEY' => 't1zz9w8165',
                'IS_DELETE' => 0,
                'DELETE_TYPE' => 0,
                'FT_STATUS' => 0,

                'gglx' => 3,
                'gglx_obj' => $tzgg_first[0]->ID,
                'ggrq' => date('Y-m-d H:i:s'),
                'ggnr' => $zgr_name.',你有一条巡检记录需要整改,请及时'

            ];

            DB::connection('mysql_fwe10')->table('uf_zhxx_qlctzgg')->insert($tzgg);
        }

        return $result;
    }

    //网格化人员轨迹查询条件
    public static function getQueryConitions($date = '')
    {
        $result['status'] = true;
        $result['msg']    = ApiEnum::RETURN_SUCCESS;
        $result['data']   = [];

        if (!$date) {
            $date = date('Y-m-d', time());
        }

//        $depart = DB::connection('mysql_fwe10')->table('uf_zhxx_qlcqyzd')->pluck('szxmc')->toArray();
//        $depart = array_unique($depart);
//        $depart = array_values($depart);

//        $data = DB::connection('mysql_fwe10')->table('uf_zhxx_qlcxjjl')->where(DB::raw('QUARTER(lrsj)'), DB::raw('QUARTER(CURDATE())'))->pluck('xjdw')->toArray();
        $data = DB::connection('mysql_fwe10')->table('uf_zhxx_qlcxjjl')->where('lrsj', '>=', DB::raw("DATE_SUB(NOW(), INTERVAL 6 MONTH)"))->pluck('xjdw')->toArray();
        $counts = array_count_values($data);// 1. 统计每个值的出现次数
        arsort($counts);// 2. 按出现次数降序排序
        $depart = array_keys($counts);// 3. 只保留键（去重），并按频率排序

        $depart_arr = [];
        foreach ($depart as $key => $val) {
            $depart_arr[$key] = [
                'name' => $val
            ];

            $query = DB::connection('mysql_fwe10')->table('uf_zhxx_qlcxjjl')
                ->where('xjdw', $val)
                ->where('lrsj', 'like', '%' . $date . '%')
                ->groupBy('lrr')->select(
                    [
                        'lrr'
                    ]
                )->get();

            if(count($query) > 0){
                for($i=0;$i<count($query);$i++){
                    $lrr = explode('|',$query[$i]->lrr);
                    $query[$i]->person_name = $lrr[0];
                    $query[$i]->person_id = $lrr[1];
                }
            }

            $query = json_decode($query);
            $depart_arr[$key]['person_list'] = $query;
        }

        $result['data'] = $depart_arr;

        return $result;
    }

    //网格化人员轨迹
    public static function getPersonTravel($person_id, $depart, $date = '')
    {
        $result['status'] = true;
        $result['msg']    = ApiEnum::RETURN_SUCCESS;
        $result['data']   = [];

        if (!$date) {
            $date = date('Y-m-d', time());
        }

        $res = [];
        $area_arr = [];

        $query = DB::connection('mysql_fwe10')->table('uf_zhxx_qlcxjjl')
            ->where('lrr', $person_id)
            ->where('lrsj', 'like', '%' . $date . '%')
            ->where('xjdw', $depart)
            ->orderBy('create_time', 'asc')->get();
        $query = json_decode($query);

        $result['list2'] = [];

        if ($query) {
            foreach ($query as $key => $val) {
                // dd($val);
                if (!in_array($val->xjqy, $area_arr)) {
                    $res[] = [
                        'area'    => $val->xjqy,
                        'date'    => $val->lrsj,
                        'data_id' => $val->ID,
                        'url'     => 'http://anstatic.nxmy.com:8011/assets/html/xixuan/pages/detail.html?id='.$val->ID,
                    ];
                    $area_arr[] = $val->xjqy;
                }

                $result['list2'][$key]['area'] = $val->xjqy;
                $result['list2'][$key]['time'] = $val->CREATE_TIME;
                $result['list2'][$key]['data_id'] = $val->ID;
            }

            $area_list = DB::connection('mysql_fwe10')->table('uf_zhxx_qlcqyzd')
                ->whereNotIn('qymc', $area_arr)
                ->where('szxmc', $depart)->where('IS_DELETE',0)->orderBy('areaid', 'asc')->get();
            foreach ($area_list as $area) {
                $res[] = [
                    'area'    => $area->qymc,
                    'date'    => '',
                    'data_id' => '',
                    'url'     => '',
                ];
            }

            $result['list1'] = $res;
        }

        return $result;
    }

    //网格化巡检记录查询条件
    public static function getResultConition()
    {
        $result['status'] = true;
        $result['msg']    = ApiEnum::RETURN_SUCCESS;
        $result['data']   = [];

        // $area_list = DB::connection('mysql_fwe10')->table('uf_zhxx_qlcqyzd')->pluck('szxmc')->toArray();
        // $area_list = array_unique($area_list);
        // $area_list = array_values($area_list);

        $area_list = DB::connection('mysql_fwe10')->table('uf_zhxx_qlcqyzd')->pluck('szxmc')->toArray();
        $area_list = array_unique($area_list);
        $area_list = array_values($area_list);

//        $data = DB::connection('mysql_fwe10')->table('uf_zhxx_qlcxjjl')->where(DB::raw('QUARTER(lrsj)'), DB::raw('QUARTER(CURDATE())'))->pluck('xjdw')->toArray();
        $data = DB::connection('mysql_fwe10')->table('uf_zhxx_qlcxjjl')->where('lrsj', '>=', DB::raw("DATE_SUB(NOW(), INTERVAL 6 MONTH)"))->pluck('xjdw')->toArray();
        $counts = array_count_values($data);// 1. 统计每个值的出现次数
        arsort($counts);// 2. 按出现次数降序排序
        $area_list = array_keys($counts);// 3. 只保留键（去重），并按频率排序

        $result['data'] = $area_list;

        return $result;
    }

    //网格化巡检记录查询
    public static function getResultRecord($depart = 'all', $date_type = 'week')
    {
        $result['status'] = true;
        $result['msg']    = ApiEnum::RETURN_SUCCESS;
        $result['data']   = [];

        $query = DB::connection('mysql_fwe10')->table('uf_zhxx_qlcxjjl');
        if ($depart != 'all') {
            $query = $query->where('xjdw', $depart);
        }
        if ($date_type == 'month') {
            $query = $query->where(DB::raw('MONTH(lrsj)'), DB::raw('MONTH(CURDATE())'));
        } else if ($date_type == 'quarter') {
            $query = $query->where(DB::raw('QUARTER(lrsj)'), DB::raw('QUARTER(CURDATE())'));
        } else {
            $query = $query->where(DB::raw('WEEK(lrsj)'), DB::raw('WEEK(CURDATE())'));
        }
        $query = $query->get();

        $people_list = [];
        if(count($query) > 0){
            for($i=0;$i<count($query);$i++){
                if(!in_array($query[$i]->lrr,$people_list)){
                    $people_list[] = $query[$i]->lrr;
                }
            }
        }

        $list = [];
        $pie = [];
        if(count($people_list) > 0){
            for($i=0;$i<count($people_list);$i++){
                $area_list = [];
                for($j=0;$j<count($query);$j++){
                    if($people_list[$i] == $query[$j]->lrr){
                        $list[$i]['department'] = str_replace("洗选中心", "", $query[$j]->xjdw);
                        $area_list[] = $query[$j]->xjqy;
                    }
                }
                $unit_zd = DB::connection('mysql_fwe10')->table('uf_zhxx_qlcqyzd')->where('szxmc',$list[$i]['department'])->where('IS_DELETE',0)->pluck('qymc');

                $minCount = PHP_INT_MAX;

                foreach ($unit_zd as $itemB) {
                    // 如果数组A中不包含数组B中的某个项，则直接退出
                    if (!in_array($itemB, $area_list)) {
                        $minCount = 0;
                    }

                    // 计算该项在数组A中出现的次数
                    $count = 0;
                    foreach ($area_list as $itemA) {
                        if ($itemA == $itemB) {
                            $count++;
                        }
                    }

                    // 更新最少的数量
                    $minCount = min($minCount, $count);
                }

                $lrr = explode('|',$people_list[$i]);
                $list[$i]['lastname'] = $lrr[0];
                $list[$i]['person_id'] = $lrr[1];
                $list[$i]['count'] = $minCount;

                $pie[$i]['name'] = $lrr[0];
                $pie[$i]['value'] = $minCount;
            }
        }

        usort($list, function($a, $b) {
            return $b['count'] <=> $a['count']; // 降序
        });

        $result['data']['list'] = $list;
        $result['data']['pie'] = $pie;

        return $result;
    }

    //全流程巡检记录排名
    public static function getRecordSort($depart,$start_date,$end_date)
    {
//        $result['status'] = true;
//        $result['msg']    = ApiEnum::RETURN_SUCCESS;
//        $result['data']   = [];

        $query = DB::connection('mysql_fwe10')->table('uf_zhxx_qlcxjjl');
        if ($depart != 'all') {
            $query = $query->where('xjdw', $depart);
        }
        $query = $query->whereBetween('lrsj',[$start_date,$end_date]);
        $query = $query->get();

        $people_list = [];
        if(count($query) > 0){
            for($i=0;$i<count($query);$i++){
                if(!in_array($query[$i]->lrr,$people_list)){
                    $people_list[] = $query[$i]->lrr;
                }
            }
        }

        $list = [];
//        $pie = [];
        if(count($people_list) > 0){
            for($i=0;$i<count($people_list);$i++){
                $area_list = [];
                for($j=0;$j<count($query);$j++){
                    if($people_list[$i] == $query[$j]->lrr){
                        $list[$i]['department'] = str_replace("洗选中心", "", $query[$j]->xjdw);
                        $area_list[] = $query[$j]->xjqy;
                    }
                }
                $unit_zd = DB::connection('mysql_fwe10')->table('uf_zhxx_qlcqyzd')->where('szxmc',$list[$i]['department'])->where('IS_DELETE',0)->pluck('qymc');

                $minCount = PHP_INT_MAX;

                foreach ($unit_zd as $itemB) {
                    // 如果数组A中不包含数组B中的某个项，则直接退出
                    if (!in_array($itemB, $area_list)) {
                        $minCount = 0;
                    }

                    // 计算该项在数组A中出现的次数
                    $count = 0;
                    foreach ($area_list as $itemA) {
                        if ($itemA == $itemB) {
                            $count++;
                        }
                    }

                    // 更新最少的数量
                    $minCount = min($minCount, $count);
                }

                $lrr = explode('|',$people_list[$i]);
                $list[$i]['lastname'] = $lrr[0];
                $list[$i]['person_id'] = $lrr[1];
                $list[$i]['count'] = $minCount;

//                $pie[$i]['name'] = $lrr[0];
//                $pie[$i]['value'] = $minCount;
            }
        }

        usort($list, function($a, $b) {
            return $b['count'] <=> $a['count']; // 降序
        });

        if(count($list) > 0){
            for($i=0;$i<count($list);$i++){
                $list[$i]['sort'] = $i+1;
            }
        }

        $result['list'] = $list;
//        $result['data']['pie'] = $pie;

        return $result;
    }

    //网格化巡检记录列表
    public static function getResultList($person_id, $depart = 'all', $date_type = 'week')
    {
        $result['status'] = true;
        $result['msg']    = ApiEnum::RETURN_SUCCESS;
        $result['data']   = [];

        $query = DB::connection('mysql_fwe10')->table('uf_zhxx_qlcxjjl')->where('lrr', 'like' ,'%'.$person_id.'%');

        if ($depart != 'all') {
            $query = $query->where('xjdw', $depart);
        }

        if ($date_type == 'month') {
            $query = $query->where(DB::raw('MONTH(lrsj)'), DB::raw('MONTH(CURDATE())'));
        } else if ($date_type == 'quarter') {
            $query = $query->where(DB::raw('QUARTER(lrsj)'), DB::raw('QUARTER(CURDATE())'));
        } else {
            $query = $query->where(DB::raw('WEEK(lrsj)'), DB::raw('WEEK(CURDATE())'));
        }

        $query = $query->select(
            [
                'xjqy',
                DB::raw('DATE(lrsj) as lrdate'),
                'lrr',
                'xjdw',
                'ID'
            ]
        )->get();

        $data = [];
        foreach ($query as $key => $val) {
            $lrr = explode('|',$val->lrr);

            $data[] = [
                'lastname'   => $lrr[0],
                'person_id'  => $val->lrr,
                'depart'     => $val->xjdw,
                'date'       => $val->lrdate,
                'xjqy'       => $val->xjqy,
                'data_id'    => $val->ID
            ];
        }

        $data_arr = array_column($data, 'date');
        array_multisort($data_arr, SORT_DESC, $data);

        $result['data']['list'] = $data;

        return $result;
    }

    //网格化巡检记录列表明细
    public static function getResultDlist($person_id, $depart, $date)
    {

        $result['status'] = true;
        $result['msg']    = ApiEnum::RETURN_SUCCESS;
        $result['data']   = [];

        $query = DB::connection('mysql_fwe10')->table('uf_zhxx_qlcxjjl')
            ->where('lrr', $person_id)
            ->where('xjdw', $depart)
            ->where('lrsj', 'like', $date . '%')
            ->orderBy('lrsj', 'desc')
            ->select([
                'ID',
                'lrr',
                'xjqy',
                'lrsj',
            ])->get();

        if(count($query) > 0){
            for($i=0;$i<count($query);$i++){
                $lrr = explode('|',$query[$i]->lrr);
                $query[$i]->lrr_name = $lrr[0];
            }
        }

        $query = json_decode($query);

        $result['data'] = $query;

        return $result;
    }

    //网格化巡检记录明细
    public static function getResultDetails($id)
    {
        $result['status'] = true;
        $result['msg']    = ApiEnum::RETURN_SUCCESS;
        $result['data']   = [];

        $query = DB::connection('mysql_fwe10')->table('uf_zhxx_qlcxjjl')
            ->where('id', $id)
            ->select(
                [
                    'lrr',
                    'dwmc',
                    'xjdw',
                    'xjqy',
                    'jclx',
                    'lrsj',
                    'jcwt',
                    'wtms',
                    'dkpz',
                    'zgzp',
                    'zgr',
                    'dkwz',
                    'CREATE_TIME'
                ]
            )->get();

        if(count($query) > 0){
            for($i=0;$i<count($query);$i++){
                $lrr = explode('|',$query[$i]->lrr);
                $query[$i]->lrr_name = $lrr[0];
            }
        }

        $query = json_decode($query);

        $result['data'] = $query;

        return $result;
    }

    //网格化巡检记录
    public static function getFinalList($start_date, $end_date, $depart = '', $person = '')
    {
        $result['status'] = true;
        $result['msg']    = ApiEnum::RETURN_SUCCESS;
        $result['data']   = [];

        $query = DB::connection('mysql_fwe10')->table('uf_zhxx_qlcxjjl');

        if ($start_date == $end_date) {
            $query = $query->where('lrsj', 'like', $start_date . '%');
        } else {
            $query = $query->where('lrsj', '>=', $start_date . ' 00:00:00')->where('lrsj', '<=', $end_date . ' 23:59:59');
        }

        if ($depart) {
            $query = $query->where('xjdw', $depart);
        }

        if ($person) {
            // $person_id = DB::connection('mysql_fwview')->table('hrmresource')
            //   ->where('lastname', 'like', '%' . $person . '%')->pluck('id')->all();1
            $query = $query->where('lrr', 'like','%'.$person.'%');
        }

        $query = $query->orderBy('lrdate', 'desc')->groupBy('lrsj', 'xjdw', 'lrr','jcwt','id','zgr')
            ->select(
                [
                    DB::raw('DATE(lrsj) as lrdate'),
                    'xjdw',
                    'lrr',
                    'jcwt',
                    'id',
                    'zgr'
                ]
            )
            ->get();

        $data = [];
        foreach ($query as $key => $val) {
            $lastname = explode('|',$val->lrr)[0];
            if($val->jcwt == '有'){
                $is_complete = 0;
            }else{
                $is_complete = 1;
            }

            $zgr = explode('|',$val->zgr);
            $zgr_id = '';
            if(count($zgr) > 1){
                $zgr_id = $zgr[1];
            }
            $data[] = [
                'depart'      => $val->xjdw,
                'lastname'    => $lastname,
                'person_id'   => $val->lrr,
                'date'        => $val->lrdate,
                'is_complete' => $is_complete,
                'id'          => $val->id,
                'zgr_id'      => $zgr_id
            ];

        }

        $result['data'] = $data;

        return $result;
    }
}