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']   = [];

        $insert = [
            'lrr'  => $params['lrr'],
            'dwmc' => $params['dwmc'],
            'xjdw' => $params['xjdw'],
            'jcqy' => $params['jcqy'],
            'jclx' => $params['jclx'],
            'lrsj' => $params['lrsj'],
            'jcwt' => $params['jcwt'],
            'wtms' => $params['wtms'],
            'dkpz' => $params['dkpz'],
        ];

        $res = DB::connection('mysql_fwview')->table('uf_wghgl_new')->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('xjdw1', $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('xjdw1', $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) {
            $allcount = DB::connection('mysql_fwview')->table('formtable_main_974')
                          ->where('szxmc', $depart)->count();

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

            if ($detail >= $allcount) {
                $lastname = DB::connection('mysql_fwview')->table('hrmresource')
                              ->where('id', $val->lrr)->value('lastname');

                $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) {
            if ($depart == 'all') {
                $wghgl = DB::connection('mysql_fwview')->table('uf_wghgl_new')
                           ->where(DB::raw('DATE(lrsj)'), $val->lrdate)
                           ->where('lrr', $person_id)
                           ->groupBy('xjdw')
                           ->select('xjdw')
                           ->get();

                $lastname = DB::connection('mysql_fwview')->table('hrmresource')
                              ->where('id', $person_id)->value('lastname');

                foreach ($wghgl as $k => $v) {
                    $allcount = DB::connection('mysql_fwview')->table('formtable_main_974')
                                  ->where('szxmc', $v->xjdw)->count();

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

                    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,
                        ];
                    } else {
                        continue;
                    }
                }
            } else {
                $allcount = DB::connection('mysql_fwview')->table('formtable_main_974')
                              ->where('szxmc', $depart)->count();

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

                if ($detail >= $allcount) {
                    $lastname = DB::connection('mysql_fwview')->table('hrmresource')
                                  ->where('id', $val->lrr)->value('lastname');

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

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

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

        return $result;
    }
}