Customer-Service/application/admin/controller/Report.php

<?php

namespace app\admin\controller;


use  app\admin\model\Countmidtable;

/**
 * 报表类
 */
class Report extends Base
{


    /**
     * 工作报表
     *
     * @access public
     * @return array JsonString
     */
    public function index()
    {
        $param = input('param.');
        $startTime = isset($param['start']) ? $param['start'] : date('Y-m-d', strtotime("-6 day"));
        $endTime = isset($param['end']) ? $param['end'] : date('Y-m-d');
        $timeInterval = $this->Date_segmentation($startTime, $endTime);
        $queueData = [];
        $chatData = [];
        $serviceData = [];
        $xData = [];
        foreach ($timeInterval['days_list'] as $k => $value) {
            $allCount = 0;
            $evaluateGood = 0;
            $evaluateSecondary = 0;
            $evaluateBad = 0;
            $evaluateNo = 0;
            $dayBegin = strtotime($value);
            $dayEnd = strtotime($value) + 24 * 60 * 60;
            $chatLogCountWhere['start_time'] = ['between', [$dayBegin, $dayEnd]];
            $join = [
                'alarm b' => 'a.servicelog_id = b.servicelog_id',
            ];
            $field = ['alarm_count', 'evaluate_id', 'system'];
            $system = [];
            $systemCount = [];
            // 查询当天的工单.
            $serviceLog = model('serviceLog')->selectServiceLog($field, $join, $chatLogCountWhere);
            foreach ($serviceLog as $ke => $va) {
                $allCount += $va['alarm_count'];
                if ($va['evaluate_id'] == 1) {
                    $evaluateGood++;
                } elseif ($va['evaluate_id'] == 2) {
                    $evaluateSecondary++;
                } elseif ($va['evaluate_id'] == 3) {
                    $evaluateBad++;
                } elseif ($va['evaluate_id'] == 0) {
                    $evaluateNo++;
                }
                if (!isset($system[$va['system']])) {
                    $system[$va['system']] = 1;
                } else {
                    $system[$va['system']]++;
                }
            }
            // 会话总数.
            $xData[] = $value;
            $chatData[$k] = $allCount;
            // 当天工单总数.
            $serviceData[$k] = model('serviceLog')->countServiceLog($chatLogCountWhere);
        }
        foreach ($system as $k => $v) {
            $systemCount[] = [
                'value' => $v,
                'name' => $k,
            ];
        }

        $queueData =(new  Countmidtable())->getQueryDay($timeInterval['days_list']);


        $this->assign([
            'xData' => json_encode($xData),
            'chatData' => json_encode($chatData),
            'serviceData' => json_encode($serviceData),
            'queueData' => json_encode($queueData),
            'systemCount' => json_encode($systemCount),
            'evaluate' => json_encode([
                'evaluateGood' => $evaluateGood,
                'evaluateSecondary' => $evaluateSecondary,
                'evaluateBad' => $evaluateBad,
                'evaluateNo' => $evaluateNo,
            ]),
        ]);

        return $this->fetch('index');

    }//end index()


    /**
     * 时间分割
     *
     * @access public
     * @return array JsonString
     */
    function Date_segmentation($start_date, $end_date)
    {
        //如果为空，则从今天的0点为开始时间
        if (!empty($start_date))
            $start_date = date('Y-m-d H:i:s', strtotime($start_date));
        else {
            $start_date = date('Y-m-d 00:00:00', time());
        }

        //如果为空，则以明天的0点为结束时间（不存在24:00:00，只会有00:00:00）
        if (!empty($end_date))
            $end_date = date('Y-m-d H:i:s', strtotime($end_date));
        else
            $end_date = date('Y-m-d 00:00:00', strtotime('+1 day'));

        //between 查询 要求必须是从低到高
        if ($start_date > $end_date) {
            $ttt = $start_date;
            $start_date = $end_date;
            $end_date = $ttt;
        } elseif ($start_date == $end_date) {
            echo '时间输入错误';
            die;
        }

        $time_s = strtotime($start_date);
        $time_e = strtotime($end_date);
        $seconds_in_a_day = 86400;

        //生成中间时间点数组（时间戳格式、日期时间格式、日期序列）
        $days_inline_array = array();
        $times_inline_array = array();

        //日期序列
        $days_list = array();
        //判断开始和结束时间是不是在同一天
        $days_inline_array[0] = $start_date;  //初始化第一个时间点
        $times_inline_array[0] = $time_s;     //初始化第一个时间点
        $days_list[] = date('Y-m-d', $time_s);//初始化第一天
        if (date('Y-m-d', $time_s) == date('Y-m-d', $time_e)) {
            $days_inline_array[1] = $end_date;
            $times_inline_array[1] = $time_e;
        } else {
            /**
             * A.取开始时间的第二天凌晨0点
             * B.用结束时间减去A
             * C.用B除86400取商，取余
             * D.用A按C的商循环+86400，取得分割时间点，如果C没有余数，则最后一个时间点 与 循环最后一个时间点一致
             */
            $A_temp = date('Y-m-d 00:00:00', $time_s + $seconds_in_a_day);
            $A = strtotime($A_temp);
            $B = $time_e - $A;
            $C_quotient = floor($B / $seconds_in_a_day);    //商舍去法取整
            $C_remainder = fmod($B, $seconds_in_a_day);               //余数
            $days_inline_array[1] = $A_temp;
            $times_inline_array[1] = $A;
            $days_list[] = date('Y-m-d', $A);              //第二天
            for ($increase_time = $A, $c_count_t = 1; $c_count_t <= $C_quotient; $c_count_t++) {
                $increase_time += $seconds_in_a_day;
                $days_inline_array[] = date('Y-m-d H:i:s', $increase_time);
                $times_inline_array[] = $increase_time;
                $days_list[] = date('Y-m-d', $increase_time);
            }
            $days_inline_array[] = $end_date;
            $times_inline_array[] = $time_e;
        }

        return array(
            'start_date' => $start_date,
            'end_date' => $end_date,
            'days_list' => $days_list,
            'days_inline' => $days_inline_array,
            'times_inline' => $times_inline_array
        );
    }

    // 工作报表
    public function workreport()
    {
        if(request()->isAjax()){

            $param  = input('param.');
            $limit  = $param['pageSize'];
            $offset = ($param['pageNumber'] - 1) * $limit;
            $where  = [];
            //用户名称
            if (empty($param['searchText']) === false) {
                $where['user_account|user_name|user_job_number'] = array('like','%'.$param['searchText'].'%');
            }
            if (empty($param['start']) === false && empty($param['end']) === false && $param['start'] <= $param['end']) {
                $start = strtotime($param['start']);
                $end = strtotime($param['end'] . ' 23:59:59');
            }
            $result = db('users')->limit($offset, $limit)->where($where)->select();

            foreach($result as $key=>$vo){
                //客服工单数
                $result[$key]['service_num'] = db('service_log')->whereBetween('start_time', [$start, $end])->where('kf_id',$vo['id'])->count();
                //客服发出消息数
                $result[$key]['send_words'] = db('chat_log')->whereBetween('time_line', [$start, $end])->where('from_id','KF'.$vo['id'])->count();
                //接入转接量
                $result[$key]['transfer_in'] = db('serviceturn_log')->whereBetween('ctime', [date('Y-m-d',$start), date('Y-m-d',$end).' 23:59:59'])->where('stype','IN')->where('uid',$vo['id'])->count();
                //客服收到消息数
                $result[$key]['receive_words'] = db('chat_log')->whereBetween('time_line', [$start, $end])->where('to_id','KF'.$vo['id'])->count();
                //客服回复工单数
                $a = db('service_log a')->join('alarm b', 'a.servicelog_id=b.servicelog_id')->where('a.kf_id',$vo['id'])->whereBetween('a.start_time', [$start, $end])->where('b.alarm_respond',2)->count();
                //回复率
                if(!empty($result[$key]['service_num'])){
                    $n = $a/$result[$key]['service_num']*100;
                    $result[$key]['reply'] = round($n,2).'%';
                }

                //答问比
                if($result[$key]['receive_words'] != 0){
                    $num = $result[$key]['send_words']/$result[$key]['receive_words']*100;
                    $result[$key]['answer_problem'] = round($num,2).'%';
                }
                //平均会话时长
                $result[$key]['conversation'] = round(db('service_log a')->join('alarm b', 'a.servicelog_id=b.servicelog_id')->whereBetween('a.start_time', [$start, $end])->where('a.kf_id',$vo['id'])->avg('b.alarm_cvtOvertime'));
                if($result[$key]['conversation'] >= 60){
                    $result[$key]['conversation'] = floor($result[$key]['conversation']/60).'分'.($result[$key]['conversation']%60).'秒';
                }else{
                    $result[$key]['conversation'] = $result[$key]['conversation'].'秒';
                }
                //平均响应时长
                $result[$key]['response'] = round(db('service_log a')->join('alarm b', 'a.servicelog_id=b.servicelog_id')->whereBetween('a.start_time', [$start, $end])->where('a.kf_id',$vo['id'])->avg('b.alarm_corresponding'));
                if($result[$key]['response'] >= 60){
                    $result[$key]['response'] = floor($result[$key]['response']/60).'分'.($result[$key]['response']%60).'秒';
                }else{
                    $result[$key]['response'] = $result[$key]['response'].'秒';
                }
                //已评价的工单数
                $m = db('service_log')->whereBetween('start_time', [$start, $end])->where('kf_id',$vo['id'])->where('evaluate_id','<>',0)->count();
                //参评率
                if($result[$key]['service_num'] != 0){
                    $result[$key]['appraise'] = $m/$result[$key]['service_num']*100;
                    $result[$key]['appraise'] = round($num,2).'%';
                }
                //满意评价
                $result[$key]['pleased_num'] = db('service_log a')->join('evaluate b', 'a.evaluate_id=b.evaluate_id')->whereBetween('a.start_time', [$start, $end])->where('kf_id',$vo['id'])->where('evaluate_name','满意')->count();
                //一般评价
                $result[$key]['commonly'] = db('service_log a')->join('evaluate b', 'a.evaluate_id=b.evaluate_id')->whereBetween('a.start_time', [$start, $end])->where('kf_id',$vo['id'])->where('evaluate_name','一般')->count();
                //不满意评价
                $result[$key]['no_pleased'] = db('service_log a')->join('evaluate b', 'a.evaluate_id=b.evaluate_id')->whereBetween('a.start_time', [$start, $end])->where('kf_id',$vo['id'])->where('evaluate_name','不满意')->count();
                //相对满意度率
                if($m != 0){
                    $result[$key]['pleased'] = $result[$key]['pleased_num']/$m*100;
                    $result[$key]['pleased'] = round($result[$key]['pleased'],2).'%';
                }

                //留言处理量
                $result[$key]['message'] = db('accountsmessage')->whereBetween('dealWith_time', [$start, $end])->where('user_id',$vo['id'])->count();


                // 生成操作按钮
                //$result[$key]['operate'] = $this->makeBtn($vo['id']);
            }

            $return['total'] = db('users')->where($where)->count();  //总数据
            $return['rows'] = $result;

            return json($return);

        }
        //所有客服
        $users = db('users')->select();
        if(!empty($users)){
            $option = '<option value="0">处理人</option>';
            for($i=0;$i<count($users);$i++){
                $option = $option.'<option value="'.$users[$i]['id'].'">'.$users[$i]['user_name'].'</option>';
            }
            $useroption = '<select lay-verify="required" lay-filter="user_id">'.$option.'</select>';
        }

        $this->assign([
            'useroption' => $useroption
        ]);

        return $this->fetch();
    }


}//end class