chat_db = Db::connect('chat_db'); $out=strtotime("+15 minute"); $data = ['login_timeout' => $out]; $where = ['admin_id'=>cookie("newshop_admin_id")]; $this->chat_db -> name('chat_login_state') -> where($where) -> update($data); } //缓存有效期 private $cache_time = [ 'sel_admin_list' => 60, //单位:秒s 'sel_admin_info' => 60, 'sel_user_list' => 60, 'sel_user_info' => 60, //用户资料卡 'sel_order_list' => 60, //用户订单列表 ]; //分配【会话】给【接待数最少】的客服 function user_to_admin($store_id='') { $where['a.store_id']=$store_id; //门店 $where['a.state']=0; //在线 $where['a.login_timeout']=['>',time()]; $r = $this->chat_db-> name('chat_login_state') -> alias('a') -> join('(select g.admin_id,count(g.admin_id)num from (select a.user_id,a.admin_id,b.msg,b.add_time from wxd_chat_receive a left join (select user_id,msg,add_time as add_time from wxd_chat_msg where store_id='.$store_id.' and concat(user_id,add_time) in (select concat(user_id,MAX(add_time)) from wxd_chat_msg where chat_type=0 and store_id='.$store_id.' group by user_id)) b on a.user_id=b.user_id where a.state=0 and a.store_id='.$store_id.' and b.msg<>\'\') g GROUP BY g.admin_id ) b','a.admin_id=b.admin_id','left') -> field('a.admin_id,a.admin_ip,b.num') -> where($where) -> order('b.num asc') -> find(); //当admin为空时要重新找 if(empty($r['admin_id'])){ if(empty($r['admin_id'])){ $where0['store_id']=$store_id; //门店 $where0['state']=0; $r = $this->chat_db-> name('chat_login_state')-> where($where0)->field('admin_id,admin_ip') ->order('id asc')->find(); } } return $r; } //获取客服信息 function sel_admin_info() { //未继承Base,取不到session('admin_id'),故取cookie $admin_id = cookie("newshop_admin_id"); $r = M('admin') -> alias('a') -> join('store b','a.store_id=b.store_id','left') -> field('a.*,b.store_name,b.store_logo') -> where('a.admin_id = '.$admin_id) // -> cache($this-> cache_time[$this->request->action()]) -> find(); return json_encode($r); } //根据admin_id获取聊天列表(主界面左侧) function sel_user_list($admin_id='') { $admin_id=0; //双向传参目的:兼容前后台调用 if ($this->request->method() == 'POST'){ $admin_id = I('admin_id'); } if(empty($admin_id)) $admin_id=getAdminId(); $stoid=getAdmStoId(); /*--接上服务--*/ $koarr= $this->chat_db->name('chat_msg')->where('is_read',0) ->where('store_id',$stoid) ->where('admin_id',$admin_id)->field('user_id')->select(); if($koarr) { $uidarr = get_arr_column($koarr, 'user_id'); $this->chat_db->name('chat_receive')->where('admin_id', $admin_id)->where('user_id', 'in', $uidarr)->save(['state' => 0]); } $str_sql='select a.user_id,a.user_ip,a.admin_ip,b.msg,b.add_time,b.session_id,b.msg_type,c.msg_uc from wxd_chat_receive a left join (select user_id,msg,add_time as add_time,session_id,msg_type from wxd_chat_msg where store_id='.$stoid.' and concat(user_id,add_time) in (select concat(user_id,MAX(add_time)) from wxd_chat_msg where admin_id='.$admin_id.' and chat_type=0 and store_id='.$stoid.' group by user_id)) b on a.user_id=b.user_id left join (select user_id,count(1) as msg_uc from wxd_chat_msg where is_read=0 and admin_id='.$admin_id.' and store_id='.$stoid.' group by user_id) as c on a.user_id=c.user_id where a.admin_id='.$admin_id.' and a.state=0 and store_id='.$stoid.' group by a.user_id'; mlog($str_sql,'sel_user_list/'. $stoid); //即时会话表join每个用户的最后一条消息、时间、未读数 $r = $this->chat_db-> query($str_sql); if (!empty($r)){ //以查询出的user_id为条件,查询满足条件的user信息 $array = array(); foreach ($r as $value){ array_push($array,$value['user_id']); } // $array = array_column($r, 'user_id'); php 5.5+ $map = array('user_id' => ['in',$array]); $r1 = M('users') -> field('user_id,nickname,vipname,head_pic') -> where($map) -> select(); //匹配 if (!empty($r1)){ $array = array(); foreach ($r1 as $key00 => $value00){ $array[$value00['user_id']] = $value00; } foreach ($r as $key => $value){ if(empty($value['add_time'])) unset($r[$key]); if(empty($value['msg'])) unset($r[$key]); $r[$key]['part_time'] = date('H:i',$value['add_time']); $r[$key]['msg_uc'] = $value['msg_uc']?$value['msg_uc']:0; $r[$key]['msg'] = $value['msg']?$value['msg']:''; if (array_key_exists($value['user_id'],$array)){ $r[$key]['nickname'] = $array[$value['user_id']]['nickname']?$array[$value['user_id']]['nickname']:$array[$value['user_id']]['vipname']; $r[$key]['vipname'] = $array[$value['user_id']]['vipname']; $r[$key]['head_pic'] = $array[$value['user_id']]['head_pic']; } } } foreach ($r as $key => $value) { if (empty($value['add_time'])) unset($r[$key]); if (empty($value['msg'])) unset($r[$key]); } } return json($r); } //根据admin_id获取历史记录(主界面左侧) function sel_history_list($admin_id='') { //双向传参目的:兼容前后台调用 if ($this->request->method() == 'POST'){ $admin_id = I('admin_id'); } //即时会话表join每个用户的最后一条消息、时间 $r = $this->chat_db-> query('select a.user_id,b.msg,b.add_time,b.msg_type from wxd_chat_receive a left join (select user_id,msg,FROM_UNIXTIME(add_time) as add_time,msg_type from wxd_chat_msg where concat(user_id,add_time) in (select concat(user_id,MAX(add_time)) as add_time from wxd_chat_msg where admin_id='.$admin_id.' and chat_type=0 group by user_id)) b on a.user_id=b.user_id where a.admin_id='.$admin_id.' and a.state=1 group by a.user_id'); if (!empty($r)){ //以查询出的user_id为条件,查询满足条件的user信息 $array = array(); foreach ($r as $value){ array_push($array,$value['user_id']); } // $array = array_column($r, 'user_id'); php 5.5+ $map = array('user_id' => ['in',$array]); $r1 = M('users') -> field('user_id,nickname,vipname,head_pic') -> where($map) // -> cache($this-> cache_time[$this->request->action()]) -> select(); //匹配 if (!empty($r1)){ $array = array(); foreach ($r1 as $key => $value){ $array[$value['user_id']] = $value; } foreach ($r as $key => $value){ $r[$key]['part_time'] = date('H:i',strtotime($value['add_time'])); // $r[$key]['part_time'] = date('H:i',$value['add_time']); $r[$key]['msg'] = $value['msg']?$value['msg']:''; if (array_key_exists($value['user_id'],$array)){ $r[$key]['nickname'] = $array[$value['user_id']]['nickname']?$array[$value['user_id']]['nickname']:$array[$value['user_id']]['vipname']; $r[$key]['vipname'] = $array[$value['user_id']]['vipname']; $r[$key]['head_pic'] = $array[$value['user_id']]['head_pic']; } } } } return json($r); } //获取用户资料 && 购买统计 function sel_user_info($user_id='') { //双向传参目的:兼容前后台调用 if ($this->request->method() == 'POST'){ $user_id = I('user_id'); } $data = M('users') -> alias('a') -> join('pick_up b','a.pickup_id=b.pickup_id','left') -> field('IFNULL(a.user_id,"未填写") as user_id,IFNULL(a.nickname,"未填写") as nickname,IFNULL(a.vipname,"未填写") as vipname, CASE WHEN a.mobile = "" THEN "未填写" else IFNULL(a.mobile,"未填写") end as mobile, CASE a.birthday WHEN 0 THEN "未填写" else DATE_FORMAT(FROM_UNIXTIME(a.birthday),"%Y-%m-%d") end as birthday,IFNULL(b.pickup_name,"未绑定") as pickup_name') -> where('a.user_id = '.$user_id) // -> cache($this-> cache_time[$this->request->action()]) -> find(); $avg = M('order') -> field('FROM_UNIXTIME(MAX(add_time)) as add_time,SUM(total_amount) as sum,COUNT(1) as count,ROUND(SUM(total_amount)/COUNT(1),2) as avg') -> where('user_id='.$user_id.' and pay_status=1') // -> where('user_id='.$user_id.' and order_status=4') -> order('add_time desc') // -> cache($this-> cache_time[$this->request->action()]) -> select(); $r = array( 'data' => $data, 'count' => $avg[0]['count'], //总单数 'sum' => $avg[0]['sum'] ? $avg[0]['sum'] : 0 , //总金额 'avg' => $avg[0]['avg'] ? $avg[0]['avg'] : 0 , //每单均价 'last_time' => $avg[0]['add_time'] ? $avg[0]['add_time'] : '无记录', //最后购买时间 ); return json($r); } //获取用户历史订单 function sel_order_list($user_id='') { //双向传参目的:兼容前后台调用 if ($this->request->method() == 'POST'){ $user_id = I('user_id'); } $r = M('order') -> alias('a') -> join('pick_up b','a.pickup_id=b.pickup_id','left') -> where('a.user_id='.$user_id.' and a.pay_status=1') -> field('a.order_id,a.order_sn, FROM_UNIXTIME(a.add_time) as add_time,a.total_amount,b.pickup_name') -> order('a.add_time desc') -> limit(20) // -> cache($this-> cache_time[$this->request->action()]) -> select(); //每笔订单任取一张商品图片 if (!empty($r)){ //以查询出的ordre_id为条件,查询满足条件的从表商品,取出商品图片路径 $array = array(); foreach ($r as $value){ array_push($array,$value['order_id']); } $map = array('a.order_id' => ['in',$array]); $img = M('order_goods') -> alias('a') -> join('goods b','a.goods_id=b.goods_id','left') -> field('a.order_id,MAX(b.original_img) as original_img') -> group('a.order_id') -> where($map) -> select(); //匹配 if (!empty($img)){ $array = array(); foreach ($img as $key => $value){ $array[$value['order_id']] = $value; } foreach ($r as $key => $value){ if (array_key_exists($value['order_id'],$array)){ $r[$key]['original_img'] = $array[$value['order_id']]['original_img']; } } } } return json($r); } //更新数据:前台调用 function update_data($data=[]){ $data = I('POST.'); $r = []; $r = $this ->chat_db-> name($data['table']) -> where($data['where']) -> update($data['data']); return json($r); } //获取客服未读消息数 function sel_admin_uc($admin_id=''){ $admin_id=0; $admin_id = I('admin_id'); if(empty($admin_id)) $admin_id=getAdminId(); $strsql="select count(1) as count from wxd_chat_msg where is_read=0 and admin_id=".$admin_id." and user_id in(select user_id from wxd_chat_receive where admin_id=".$admin_id." and state=0)"; $r = $this->chat_db->query($strsql); $data['count']=$r[0]['count']; $this->updata_login_outtime(); return json($data); } //时时更新超时时间 public function updata_login_outtime(){ $out=strtotime("+15 minute"); $rs=$this->chat_db->name('chat_login_state')->where('admin_id',getAdminId())->find(); if ($rs ){ $dda['login_timeout'] =$out; $this->chat_db->name('chat_login_state')->where('admin_id', getAdminId())->save($dda); } //对于客户人员超时的处理 $o_arr=$this->chat_db->name('chat_login_state') ->where('login_timeout<'.time())->field('admin_id')->select(); if($o_arr) { $o_arr1 = get_arr_column($o_arr, 'admin_id'); $this->chat_db->name('chat_login_state')->where('admin_id','in',$o_arr1)->save(['state' => 2]); $this->chat_db->name('chat_receive')->where('admin_id','in',$o_arr1)->delete(); } /*--对于不是在线客服的admin_id,要进行清理--*/ $uarr =$this->chat_db->name('chat_login_state') ->where('store_id',getAdmStoId())->field('admin_id')->select(); if($uarr){ $o_arr1 = get_arr_column($uarr, 'admin_id'); $o_arr2=M('admin')->where('is_custom_service',0)->where('store_id',getAdmStoId()) ->where('admin_id','in',$o_arr1)->field('admin_id')->select(); $o_arr3 = get_arr_column($o_arr2, 'admin_id'); if($o_arr3){ $this->chat_db->name('chat_login_state')->where('admin_id','in',$o_arr3)->delete(); $this->chat_db->name('chat_receive')->where('admin_id','in',$o_arr3)->delete(); } } //设置cookie,避免过期 Cookie::set('newshop_admin_stoid',getAdmStoId()); Cookie::set('newshop_admin_id',getAdminId()); } //获取是否在线 public function is_online(){ $stoid=I('stoid'); $admin_id=I('tadmin_id'); $where['store_id']=$stoid; $where['admin_id']=$admin_id; $where['state']=0; $result=$this->chat_db->name('chat_login_state')->where($where)->find(); if($result){ return json(['ison'=>1]); }else{ return json(['ison'=>0]); } } }