sport_admin_api/app/admin/controller/Kefu.php

<?php


namespace app\admin\controller;

use app\BaseController;
use app\admin\model\Admin;
use app\admin\model\Sign;
use app\admin\model\KefuWork;
use app\admin\model\User;
use app\admin\model\UserView;
use app\admin\model\KefuTime;
use app\admin\model\OperationData;
use app\admin\model\Department;
use app\admin\model\Config;
use app\admin\model\Score;
use app\admin\model\KefuLog;
use app\enterprise\model\{Message,Friend};
use Exception;
use think\facade\Db;

class Kefu extends BaseController
{

    //当前数据统计
    public function statistics()
    {
        // 统计
        $data['visit_count'] = UserView::count(); //当前客服页访问量
        $data['online_count'] = User::where('role',3)->where('is_online', '>', 0)->count(); //当前客服在线人数
        $data['handling_count'] = User::where('service_status',2)->count(); //当前客服接线中的总数
        $data['today_handling_count'] = KefuTime::where('type', 3)->where('created_at', '>=', date('Y-m-d'))->count(); //今日客服接线总数

        return $this->success($data);
    }

    /**
     * @api {get} /today 今日在线客服列表
     */
    public function today()
    {
        try {
            $params = $this->request->param();
            $page = $params['page'] ?? 1;
            $limit = $params['limit'] ?? 15;
            $query = Db::table('la_kefu_work')->alias('kefu_work')
                        // 关键：写完整的 库名.bot_users，彻底避免加 la_ 前缀
                        ->join(env('database.DATABASE').'.bot_users user', 'kefu_work.admin_id = user.uid', 'left')
                        ->where('kefu_work.created_at', '>=', date('Y-m-d 00:00:00'))
                        ->where('user.role', 3);

            if (isset($params['is_online']) && $params['is_online'] != '') {
                $query->where('user.is_online', $params['is_online']);
            }

            $count = $query->count();
            $list = $query->field(['kefu_work.id', 'user.user_id','user.realname as nickname','user.is_online', 'user.chat_num','kefu_work.transfer_num','kefu_work.intervention_num','kefu_work.completed_num','kefu_work.created_at'])
                        ->order('user.is_online', 'desc')  
                        ->limit($limit)
                        ->page($page)
                        ->select();
            foreach ($list as &$value) {
                $value['chat_num'] = intval($value['chat_num']);//当前接线数
                $value['transfer_num'] = intval($value['transfer_num']);
                $value['intervention_num'] = intval($value['intervention_num']);
                $value['completed_num'] = intval($value['completed_num']);
                $value['inprocess'] = User::where('cs_uid',$value['user_id'])->where('service_status',2)->count();//进行中
                $value['unprocessed'] = User::where('cs_uid',$value['user_id'])->where('service_status',1)->count();//待处理
                $chat_num = $value['inprocess'] + $value['unprocessed'];
                if ($chat_num != $value['chat_num']) {
                    $value['chat_num'] = $chat_num;
                    User::where('user_id', $value['user_id'])->update(['chat_num' => $chat_num]);
                }
            }
            
        } catch (Exception $e) {
            return $this->error($e->getMessage());
        }
        return $this->success(['count' => $count, 'list' => $list]);
    }

    /**
     * @api {get} /list 客服数据展板列表
     */
    public function list()
    {
        try {
            $params = $this->request->param();
            $page = $params['page'] ?? 1;
            $limit = $params['limit'] ?? 15;
            $order_field = $params['field'] ?? 'kefu_work.id';
            $order = $params['order'] ?? 'desc';
            $query = KefuWork::alias('kefu_work')
                        ->join('admin', 'kefu_work.admin_id = admin.id', 'left')
                        ->join(env('database.DATABASE').'.bot_users user', 'kefu_work.admin_id = user.uid', 'left')
                        ->where('user.from', 0);

            if (isset($params['is_online'])) {
                $query = $query->where('user.is_online', $params['is_online']);
            }
            if (!empty($params['start_time'])) {
                $query = $query->where('kefu_work.created_at', '>=', $params['start_time'].' 00:00:00');
            }
            if (!empty($params['end_time'])) {
                $query = $query->where('kefu_work.created_at', '<=', $params['end_time'].' 23:59:59');
            }

            $count = $query->count();
            $list = $query->field(['admin.username','admin.nickname','user.is_online', 'kefu_work.*'])
                        ->order($order_field, $order)
                        ->order('kefu_work.id', 'desc')
                        ->limit($limit)
                        ->page($page)
                        ->select();
            $kefu_time = KefuTime::where('admin_id', $this->admin_id)->where('status', 0)->column('start_time', 'type');
            foreach ($list as &$value) {
                if (isset($kefu_time[4])) {
                    $value['service_time'] += time() - $kefu_time[4]; 
                }
                if (isset($kefu_time[3])) {
                    $value['chat_time'] += time() - $kefu_time[3]; 
                }
                if (isset($kefu_time[1])) {
                    $value['busy_time'] += time() - $kefu_time[1]; 
                }
                $value['avg_time'] = $value['service_time'] && $value['chat_num'] ? intval($value['service_time'] / $value['chat_num']) : 0; //客服平均服务时间
                $value['online_time'] = formatSecondsToTime($value['online_time']);
                $value['busy_time'] = formatSecondsToTime($value['busy_time']);
                $value['chat_time'] = formatSecondsToTime($value['chat_time']);
                $value['service_time'] = formatSecondsToTime($value['service_time']);
            }
            
        } catch (Exception $e) {
            return $this->error($e->getMessage());
        }
        return $this->success(['count' => $count, 'list' => $list]);
    }

    /**
     * @api {get} /chat 总接线数据
     */
    public function chat()
    {
        try {
            $params = $this->request->param();
            $start_time = empty($params['start_time']) ? date('Y-m-d') : $params['start_time'];
            $end_time = empty($params['end_time']) ? date('Y-m-d') : $params['end_time'];

            $list = OperationData::where('type', 1)
                    ->where('date', '>=', $start_time)
                    ->where('date', '<=', $end_time)
                    ->limit(100)
                    ->field(['num','date'])
                    ->select();
            if ($start_time == date('Y-m-d') || $end_time == date('Y-m-d')) {
                $list[] = [
                    'num' => KefuTime::where('type', 3)
                        ->where('created_at', '>=', date('Y-m-d 00:00:00'))
                        ->where('created_at', '<=', date('Y-m-d 23:59:59'))
                        ->count(),
                    'date' => date('Y-m-d'),
                ];
            }  
        } catch (Exception $e) {
            return $this->error($e->getMessage());
        }
        return $this->success(['count' => count($list), 'list' => $list]);
    }

    //客服签到
    public function sign()
    {
        try {
            Db::startTrans();

            $admin_id = $this->admin_id;
            KefuWork::addNum($admin_id, 'sign_num');//客服签到次数更新
            $sign = Sign::where('admin_id', $admin_id)->where('created_at', '>=', date('Y-m-d'))->order('id', 'desc')->find();
            if ($sign && $sign->time == 0) {
                $sign->time = time() - strtotime($sign->created_at);
                $sign->updated_at = date('Y-m-d H:i:s');
                $sign->save();
            }
            //签到记录
            Sign::create([          
                'admin_id' => $admin_id,
            ]);
            
            Admin::where('id', $admin_id)->update(['is_sign' => 0]);
            Db::commit();
        } catch (\Exception $e) {
            Db::rollback();
            return $this->error($e->getMessage());
        }
        //通知客服已签到
        $user_id = User::getCsId($admin_id);
        wsSendMsg($user_id,'sign',['is_sign'=>0]);

        //上线通知
        wsSendMsg(0,'isOnline',['id'=>$user_id, 'is_online'=>1]);

        return $this->success([], '签到成功');
    }

    /**
     * 客服接线
     */
    public function handleChat()
    { 
        try {
            Db::startTrans();
            $user_id = $this->request->param('user_id');
            $user = User::where('user_id', $user_id)->find();
            if (!$user) {
                return $this->error('用户不存在');
            }
            if ($user->service_status >= 1) {
                return $this->error('用户已接线');
            }
            $cs_uid = User::getCsId($this->admin_id);
            $user->cs_uid = $cs_uid;
            $user->service_status = 2;
            $user->service_start = time();
            $user->timeout_type = 0;
            $user->save();

            //更新客服接线数量
            User::where('user_id', $cs_uid)->update(['chat_num'=>Db::raw('chat_num+1')]);
            //客服接线次数更新
            KefuWork::addNum($this->admin_id, 'chat_num');
            //客服对接记录表
            KefuLog::addData($cs_uid, $user_id, 1);

            $friend = Friend::where('create_user', $user_id)->order('create_time', 'desc')->find();
            if ($friend) {
                $robot_id = $friend->friend_user_id;
                $friend->friend_user_id = $cs_uid;
                $friend->save();
                $chat_identify = $cs_uid . '-' . $user_id;
                Message::where(['from_user' => $user_id, 'to_user' => $robot_id])->update(['to_user' => $cs_uid, 'chat_identify' => $chat_identify, 'is_read' => 1]);
                Message::where(['from_user' => $robot_id, 'to_user' => $user_id])->update(['from_user' => $cs_uid, 'chat_identify' => $chat_identify, 'is_read' => 1]);
            }

            Db::commit();
            //通知客服已接线
            wsSendMsg(0,'handleChat',['user_id'=>$user_id]);

            //自动回复-人工客服接线成功
            Message::sendAutoReply('transfer_to_human_success', $user_id, $cs_uid,$user->language);
        } catch (\Exception $e) {
            Db::rollback();
            return $this->error($e->getMessage());
        }
        return $this->success([], '');
    }

    /**
     * 客服转线
     */
    public function transferChat()
    { 
        try {
            Db::startTrans();
            $user_id = $this->request->param('user_id');
            $cs_uid = $this->request->param('cs_uid');

            $user = User::where('user_id', $user_id)->find();
            if (!$user) {
                return $this->error('用户不存在');
            }
            $old_cs_uid = $user->cs_uid;
            $admin_id = User::getAdminId($cs_uid);
            $user->cs_uid = $cs_uid;
            $user->service_status = 1;
            $user->service_start = time();
            $user->timeout_type = 0;
            $user->save();
            //更新客服接线数量
            User::where('user_id', $cs_uid)->update(['chat_num'=>Db::raw('chat_num+1')]);
            //客服接线次数更新
            KefuWork::addNum($admin_id, 'chat_num');
            
            //客服对接记录表
            KefuLog::addData($cs_uid, $user_id, 2);

            $friend = Friend::where('create_user', $user_id)->order('create_time', 'desc')->find();
            if ($friend) {
                $friend->friend_user_id = $cs_uid;
                $friend->save();
                
                $chat_identify = $cs_uid . '-' . $user_id;
                Message::where(['from_user' => $user_id, 'to_user' => $old_cs_uid])->update(['to_user' => $cs_uid, 'chat_identify' => $chat_identify, 'is_read' => 1]);
                Message::where(['from_user' => $old_cs_uid, 'to_user' => $user_id])->update(['from_user' => $cs_uid, 'chat_identify' => $chat_identify, 'is_read' => 1]);
                KefuTime::endData($admin_id, 3, $old_cs_uid); //结束接线时间
            }

            Db::commit();

            //通知关闭聊天框
            wsSendMsg($old_cs_uid,'closeChat',['user_id'=>$user_id, 'realname' => $user->realname, 'is_warning' => 1]);

            //通知客服已接线
            wsSendMsg(0,'handleChat',['user_id'=>$user_id]);

            //自动回复-人工客服接线成功
            Message::sendAutoReply('transfer_to_human_success', $user_id, $cs_uid, $user->language);
        } catch (\Exception $e) {
            Db::rollback();
            return $this->error($e->getMessage());
        }
        return $this->success([], '');
    }

    /**
     * 选择上级及部门一下的其他客服
     */
    public function select() 
    {
        // $department_id = Admin::where('id', $this->admin_id)->value('department_id');
        // $parent_department_id = Department::where('id', $department_id)->value('parent_id');
        // $where[] = ['department_id', '=', $parent_department_id];

        $cs_uids = Department::getDepartmentCsUids($this->admin_id);
        $list = Admin::alias('admin')
                ->join(env('database.DATABASE').'.bot_users user', 'admin.id = user.uid', 'left')
                // ->join('user', 'admin.id = user.uid', 'left')
                ->where('user.role', '>', 0)
                ->where('user.status', 1)
                // ->where('admin.department_id', $department_id)->whereOr(function ($query) use ($parent_department_id) {
                //     if ($parent_department_id) {
                //         $query->where('admin.department_id', $parent_department_id);
                //     }
                // })
                //->where('admin.id','<>', $this->admin_id)
                ->whereIn('user.user_id', $cs_uids)
                ->order('user.is_online', 'desc')
                ->order('admin.department_id', 'asc')
                ->field(['admin.id','user.user_id','user.account','user.realname','user.avatar','user.is_online','admin.nickname','admin.username'])
                ->select()
                ->toArray();
        return $this->success($list);
    }

    /**
     * 客服结束会话
     */
    public function finishedChat()
    { 
        try {
            Db::startTrans();
            $user_id = $this->request->param('user_id');

            $user = User::where('user_id', $user_id)->where('role', 0)->find();
            if (!$user) {
                return $this->error('用户不存在');
            }
            
            if ($user->service_status != 3) {
                KefuTime::endData($user->uid, 3, $user->cs_uid); //结束接线时间
                $old_cs_uid = $user->cs_uid;
                
                //转成机器人聊天
                $user->service_status = -1;
                $user->service_time = 0;
                $user->timeout_type = 0;
                $user->cs_uid = getAutoCsUid();//获取机器人ID
                $user->save();

                $cs_uid = $user->cs_uid;

                $friend = Friend::where('create_user', $user_id)->order('create_time', 'desc')->find();
                if ($friend) {
                    $old_cs_uid = $friend->friend_user_id;
                    $friend->friend_user_id = $cs_uid;
                    $friend->save();
                    
                    $chat_identify = $cs_uid . '-' . $user_id;
                    Message::where(['from_user' => $user_id, 'to_user' => $old_cs_uid])->update(['to_user' => $cs_uid, 'chat_identify' => $chat_identify, 'is_read' => 1]);
                    Message::where(['from_user' => $old_cs_uid, 'to_user' => $user_id])->update(['from_user' => $cs_uid, 'chat_identify' => $chat_identify, 'is_read' => 1]);
                }

                //客服对接记录表
                KefuLog::addData($cs_uid, $user_id, 3);

                Db::commit();

                //通知关闭聊天框
                wsSendMsg($old_cs_uid,'closeChat',['user_id'=>$user_id, 'realname' => $user->realname, 'is_warning' => 1]);

                //通知客服已结束
                wsSendMsg(0,'handleChat',['user_id'=>$user_id]);
    
                //给用户发送客服评分的消息
                $user_open_comment = Config::getFieldValue('user_open_comment');
                if ($user_open_comment == 1 ) {
                    $param = [
                        'id' => \utils\Str::getUuid(),
                        'type' => 'score',
                        'status' => 'going',
                        'sendTime' => time() * 1000,
                        'toContactId' => $user_id,
                        'content' => Config::getFieldValue('kefu_finished_chat', $user->language),
                        'file_id' => 0,
                        'is_group' => 0,
                        'user_id' => $cs_uid,
                        'extends' => json_encode([
                            'cs_uid' => $old_cs_uid,
                        ]),
                    ];
                    if (!empty($param['content'])) {
                        Message::sendMsg($param, 0);
                    }
                }
            }
        } catch (\Exception $e) {
            Db::rollback();
            return $this->error($e->getMessage());
        }
        return $this->success([], '');
    }

    /**
     * @api {get} 客服评分列表
     */
    public function score()
    {
        try {
            $params = $this->request->param();
            $page = $params['page'] ?? 1;
            $limit = $params['limit'] ?? 15;
            
            $cs_uid = $params['user_id'] ?? User::getCsId($this->admin_id);
            $query = Score::alias('score')
                        ->join(env('database.DATABASE').'.bot_users user', 'score.user_id = user.user_id', 'left')
                        // ->join('user', 'score.user_id = user.user_id', 'left')
                        ->where('score.cs_uid', $cs_uid);

            $count = $query->count();
            $list = $query->field(['score.*','user.realname'])
                        ->order('id', 'desc')
                        ->limit($limit)
                        ->page($page)
                        ->select();
            
        } catch (Exception $e) {
            return $this->error($e->getMessage());
        }
        return $this->success(['count' => $count, 'list' => $list]);
    }

    /**
     * @api {get} 客服对接记录表
     */
    public function log()
    {
        try {
            $params = $this->request->param();
            $page = $params['page'] ?? 1;
            $limit = $params['limit'] ?? 15;
            $keyword = $params['keyword'] ?? '';
            $cs_name = $params['cs_name'] ?? '';
            $user_id = $params['user_id'] ?? '';
            $is_online = $params['is_online'] ?? '';
            
            $query = KefuLog::alias('log')
                        ->join(env('database.DATABASE').'.bot_users user', 'log.user_id = user.user_id', 'left')
                        ->join(env('database.DATABASE').'.bot_users kefu', 'log.cs_uid = kefu.user_id', 'left');
                        // ->join('user', 'log.user_id = user.user_id', 'left')
                        // ->join('user kefu', 'log.cs_uid = kefu.user_id', 'left');
            if ($keyword) {
                $query = $query->where('user.realname|user.user_id', 'like', "%{$keyword}%");
            }
            if ($cs_name) {
                $query = $query->where('kefu.realname', 'like', "%{$cs_name}%");
            }
            if ($user_id) {
                $query = $query->where('user.user_id', $user_id);
            }
                
            if ($is_online != '' && is_numeric($is_online)) {
                $query = $query->where('user.is_online', $is_online);
            }

            $count = $query->count();
            $list = $query->field(['log.*','user.realname','user.is_online','user.avatar','kefu.realname as cs_name'])
                        ->order('id', 'desc')
                        ->limit($limit)
                        ->page($page)
                        ->select();
            
        } catch (Exception $e) {
            return $this->error($e->getMessage());
        }
        return $this->success(['count' => $count, 'list' => $list]);
    }

    //客服接线数量
    public function hasChat(){
        $chat_num = (int)User::where('uid', $this->admin_id)->where('role', '>', 0)->value('chat_num');
        return $this->success(['chat_num' => $chat_num]);
    }

    //翻译
    public function translate() 
    {
        try {
            $language_code = $this->request->param('language_code') ?? $this->lang;
            $msg_id = $this->request->param('msg_id');
            $content = Message::where('msg_id',$msg_id)->where('type','text')->value('content');
            //谷歌翻译
            $content = google_translate($content, $language_code);
            return $this->success( ['content' => $content]);
            
        } catch (\Exception $e) {
            return $this->error('翻译失败：' . $e->getMessage());
        }
        return $this->success( ['content' => '']);
    }
    
    /**
     * @api {get} 客服网址访问记录
     */
    public function website()
    {

        try {
            $params = $this->request->param();
            $page = $params['page'] ?? 1;
            $limit = $params['limit'] ?? 15;
            
            $query = UserView::alias('user_view')
                        ->join('website', 'user_view.website_id = website.id', 'left');

            if (!empty($params['start_time'])) {
                $query = $query->where('user_view.created_at', '>=', $params['start_time'].' 00:00:00');
            }
            if (!empty($params['end_time'])) {
                $query = $query->where('user_view.created_at', '<=', $params['end_time'].' 23:59:59');
            }

            $count = $query->group('user_view.website_id')->count();
            $list = $query->field(['user_view.website_id','website.url', 'website.num'])
                        ->order('website.num', 'desc')
                        ->group('user_view.website_id')
                        ->limit($limit)
                        ->page($page)
                        ->select();
        } catch (Exception $e) {
            return $this->error($e->getMessage());
        }
        return $this->success(['count' => $count, 'list' => $list]);
    }

    //检测客服是否可以转接此用户
    public function isChat()
    {
        $user_id = (int)$this->request->param('user_id');
        if (!$user_id) return $this->error('参数错误');
        $is_chat = User::where('user_id', $user_id)->whereIn('service_status', [0,1,2])->value('user_id') ? 0 : 1;
        return $this->success(['is_chat' => $is_chat]);
    }


}