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('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') {
            $start_date = date('Y-m-01') . ' 00:00:00';
            $end_date = date('Y-m-t') . ' 23:59:59';
        } else if ($date_type = 'quarter') {
            $start_date = self::GetInceDay() . ' 00:00:00';
            $end_date = self::GetEndDay() . ' 23:59:59';
        } else {
            $start_date = date('Y-m-d', strtotime('this week monday')) . ' 00:00:00';
            $end_date = date('Y-m-d', strtotime('this week sunday')) . ' 23:59:59';
        }

        $query = $query->where('lrsj', '>=', $start_date)->where('lrsj', '<=', $end_date);

        $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_fwview')->table('uf_wghgl_new')
                   ->join('hrmresource', 'uf_wghgl_new.lrr', '=', 'hrmresource.id')
                   ->where('lrr', $person_id)
                   ->where('xjdw', $depart)
                   ->where('lrsj', 'like', $date . '%')
                   ->orderBy('lrsj', 'desc')
                   ->select(
                       [
                           'uf_wghgl_new.id',
                           'lastname',
                           'jcqy',
                           'lrsj',
                       ]
                   )->get();

        $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_fwview')->table('uf_wghgl_new')
                   ->join('hrmresource', 'uf_wghgl_new.lrr', '=', 'hrmresource.id')
                   ->where('uf_wghgl_new.id', $id)
                   ->select(
                       [
                           'lastname',
                           'dwmc',
                           'xjdw',
                           'jcqy',
                           'jclx',
                           'lrsj',
                           'jcwt',
                           'wtms',
                           'dkpz'
                       ]
                   )->get();

        $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;
    }

    protected static function GetInceDay()
    {
        $month = date("m");
        if ($month >=1 && $month < 4)
        {
            return date('Y') . '-01-01';
        }
        else if ($month >= 4 && $month < 7)
        {
            return date('Y') . '-04-01';
        }
        else if ($month >= 7 && $month < 10)
        {
            return date('Y') . '-07-01';
        }
        else
        {
            return date('Y') . '-10-01';
        }
        return "";
    }

    protected static function GetEndDay()
    {
        $month = date("m");
        if ($month >= 1 && $month < 4)
        {
            return date('Y') . '-03-31';
        }
        else if ($month >= 4 && $month < 7)
        {
            return date('Y') . '-06-30';
        }
        else if ($month >= 7 && $month < 10)
        {
            return date('Y') . '-09-30';
        }
        else
        {
            return date('Y') . '-12-31';
        }
        return "";
    }
}