Getinfo.php
14.9 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
<?php
/**
* Author: Panweicheng
* Date: 2017-11-08
*/
namespace app\mchat\controller;
use think\Controller;
use think\Cookie;
use think\Db;
use think\Session;
use think\cache;
use app\admin\controller\Base;
class Getinfo extends Controller
{
function _initialize() {
$this->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]);
}
}
}