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 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'])->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);
                $data['areaid'] = $params['xjdw'].($matches[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']
        ];

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

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

        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_fwview')->table('hrmdepartment')
                   ->where('subcompanyid1', 49)
                   ->where('departmentname', 'like', '%' . '洗煤厂')
                   ->where('id', '!=', 1602)->get();

        $depart_arr = [];
        foreach ($depart as $key => $val) {
            $depart_name = str_replace('洗选中心', '', $val->DEPARTMENTNAME);
            $depart_arr[$key] = [
                'name' => $depart_name,

            ];
            $query = DB::connection('mysql_fwview')->table('uf_wghgl_new')
                       ->join('hrmresource', 'uf_wghgl_new.lrr', '=', 'hrmresource.id')
                       ->where('xjdw', $depart_name)
                       ->where('lrsj', 'like', '%' . $date . '%')
                       ->groupBy('lrr')->select(
                            [
                                'hrmresource.lastname as person_name',
                                'uf_wghgl_new.lrr as person_id'
                            ]
                        )->get();
            $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_fwview')->table('uf_wghgl_new')
                   ->where('lrr', $person_id)
                   ->where('lrsj', 'like', '%' . $date . '%')
                   ->where('xjdw', $depart)
                   ->orderBy('lrsj', 'asc')->get();
        $query = json_decode($query);
        if ($query) {
            foreach ($query as $key => $val) {
                $area_arr[] = $val->jcqy;
                $res[] = [
                    'area'    => $val->jcqy,
                    'date'    => $val->lrsj,
                    'data_id' => $val->id,
                    'url'     => '/mobilemode/formbasebrowserview.jsp?billid=' . $val->id . '&modeId=580&formId=-976',
                ];
            }
            $area_list = DB::connection('mysql_fwview')->table('formtable_main_974')
                           ->whereNotIn('qymc', $area_arr)
                           ->where('szxmc', $depart)->orderBy('areaid', 'asc')->get();
            foreach ($area_list as $area) {
                $res[] = [
                    'area'    => $area->qymc,
                    'date'    => '',
                    'data_id' => '',
                    'url'     => '',
                ];
            }

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

        return $result;
    }

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

        $depart = DB::connection('mysql_fwview')->table('hrmdepartment')
                    ->where('subcompanyid1', 49)
                    ->where('departmentname', 'like', '%' . '洗煤厂')
                    ->where('id', '!=', 1602)->get();

        $depart_arr = [];
        foreach ($depart as $key => $val) {
            $depart_name = str_replace('洗选中心', '', $val->DEPARTMENTNAME);
            $depart_arr[$key] = $depart_name;
        }

        $result['data'] = $depart_arr;

        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_fwview')->table('uf_wghgl_new');

        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->groupBy('lrdate', 'xjdw', 'lrr', 'dwmc')
                   ->select(
                       [
                           DB::raw('DATE(lrsj) as lrdate'),
                           'xjdw',
                           'lrr',
                           'dwmc'
                       ]
                   )
                   ->get();

        $data = [];
        $pie  = [];
        foreach ($query as $key => $val) {
            $lastname = DB::connection('mysql_fwview')->table('hrmresource')
                          ->where('id', $val->lrr)->value('lastname');
            $allcount = DB::connection('mysql_fwview')->table('formtable_main_974')
                          ->where('szxmc', $val->xjdw)->count();

            $detail = DB::connection('mysql_fwview')->table('uf_wghgl_new')
                        ->where('xjdw', $val->xjdw)
                        ->where(DB::raw('DATE(lrsj)'), $val->lrdate)
                        ->where('lrr', $val->lrr)->count();

            if ($allcount <= 0) {
                continue;
            }

//            if ($detail >= $allcount) {
                $data[$val->lrr] = [
                    'department' => $val->dwmc,
                    'lastname'   => $lastname,
                    'person_id'  => $val->lrr,
                    'count'      => isset($data[$val->lrr]['count']) ? $data[$val->lrr]['count'] + 1 : 1,
                ];
                $pie[$val->lrr] = [
                    'name'  => $lastname,
                    'value' => isset($data[$val->lrr]['value']) ? $data[$val->lrr]['value'] + 1 : 1,
                ];
//            } else {
//                continue;
//            }
        }

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

        $pie_arr = array_column($pie, 'value');
        array_multisort($pie_arr, SORT_DESC, $pie);

        $result['data']['list'] = $data;
        $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_fwview')->table('uf_wghgl_new')->where('lrr', $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->groupBy('lrdate', 'xjdw')
                       ->select(
                           [
                               'xjdw',
                               DB::raw('DATE(lrsj) as lrdate'),
                           ]
                       )
                       ->get();

        $data = [];
        foreach ($query as $key => $val) {
            $lastname = DB::connection('mysql_fwview')->table('hrmresource')
                          ->where('id', $person_id)->value('lastname');

            $allcount = DB::connection('mysql_fwview')->table('formtable_main_974')
                          ->where('szxmc', $val->xjdw)->count();

            $detail = DB::connection('mysql_fwview')->table('uf_wghgl_new')
                        ->where('xjdw', $val->xjdw)
                        ->where(DB::raw('DATE(lrsj)'), $val->lrdate)
                        ->where('lrr', $person_id)->count();

            if ($allcount <= 0) {
                continue;
            }

            if ($detail >= $allcount) {
                $data[] = [
                    'lastname'   => $lastname,
                    'person_id'  => $person_id,
                    'depart'     => $val->xjdw,
                    'date'       => $val->lrdate
                ];
            } else {
                continue;
            }
        }

        $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'
                ]
            )->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_fwview')->table('uf_wghgl_new');

        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();
            $query = $query->whereIn('lrr', $person_id);
        }

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

        $data = [];
        foreach ($query as $key => $val) {
            $lastname = DB::connection('mysql_fwview')->table('hrmresource')
                          ->where('id', $val->lrr)->value('lastname');
            $allcount = DB::connection('mysql_fwview')->table('formtable_main_974')
                          ->where('szxmc', $val->xjdw)->count();

            $detail = DB::connection('mysql_fwview')->table('uf_wghgl_new')
                        ->where('xjdw', $val->xjdw)
                        ->where(DB::raw('DATE(lrsj)'), $val->lrdate)
                        ->where('lrr', $val->lrr)->count();

            if ($allcount <= 0) {
                continue;
            }

            if ($detail >= $allcount) {
                $data[] = [
                    'depart'      => $val->xjdw,
                    'lastname'    => $lastname,
                    'person_id'   => $val->lrr,
                    'date'        => $val->lrdate,
                    'is_complete' => 1
                ];
            } else {
                $data[] = [
                    'depart'      => $val->xjdw,
                    'lastname'    => $lastname,
                    'person_id'   => $val->lrr,
                    'date'        => $val->lrdate,
                    'is_complete' => 0
                ];
            }
        }

        $result['data'] = $data;

        return $result;
    }
}