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

<?php

namespace app\admin\controller;


use  app\admin\model\Countmidtable;
use app\admin\model\Users as Usersmodel;
use app\admin\model\Groups as GroupsModel;
use app\admin\model\Servicetimelog as ServicetimelogModel;
use app\admin\model\Kfstatetimes as KfstatetimesModel;
use app\admin\model\Office;


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


    /**
     * 工作报表
     *
     * @access public
     * @return array JsonString
     */
    public function index()
    {
        $param = input('param.');
        $startTime = isset($param['start']) === true ? $param['start'] : date('Y-m-d', strtotime("-6 day"));
        $endTime = isset($param['end']) === true ? $param['end'] : date('Y-m-d');
        $timeInterval = $this->Date_segmentation($startTime, $endTime);
        $queueData = []; // 排队数据.
        $chatData = []; // 消息数据.
        $serviceData = []; // 会话数据.
        $xData = []; // x轴数据.
        $system = []; // 系统数据.
        $evaluateGood = 0;  // 好评数.
        $evaluateSecondary = 0;  // 中评数.
        $evaluateBad = 0;  // 差评数.
        $evaluateNo = 0;  // 未评数.
        $alllWaitTime = 0;  // 总排队时长.
        $serviceTime = 0;  // 总会话时长.
        $cpdTime = 0;  // 总相应时长.
        foreach ($timeInterval['days_list'] as $k => $value) {
            $allCount = 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',
                'alarm_lineTime',
                'alarm_cvtOvertime',
                'alarm_corresponding',
            ];
            // 查询当天的工单.
            $serviceLog = model('serviceLog')->selectServiceLog($field, $join, $chatLogCountWhere);
            foreach ($serviceLog as $va) {
                $allCount += $va['alarm_count'];
                $alllWaitTime += $va['alarm_lineTime'];
                $serviceTime += $va['alarm_cvtOvertime'];
                $cpdTime += $va['alarm_corresponding'];
                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']]) === false) {
                    $system[$va['system']] = 1;

                } else {
                    $system[$va['system']]++;
                }
            }

            // 会话总数.
            $xData[] = $value;
            $chatData[$k] = $allCount;
            // 当天工单总数.
            $serviceData[$k] = model('serviceLog')->countServiceLog($chatLogCountWhere);
        }//end foreach
        $systemCount = [];
        foreach ($system as $k => $v) {
            $systemCount[] = [
                'value' => $v,
                'name' => $k,
            ];
        }

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

        $defaultTime = "$startTime , $endTime";

        $allChat = 0;  // 会话总数.
        $allService = 0;  // 消息总数.
        $allQueue = 0;  // 排队总数.
        foreach ($chatData as $v) {
            $allChat += $v;
        }
        foreach ($serviceData as $v) {
            $allService += $v;
        }
        foreach ($queueData as $v) {
            $allQueue += $v;
        }

        // 转接量.
        $transferWhere['ctime'] = [
            'between',
            [
                $startTime,
                $endTime,
            ],
        ];
        $transfer = model('ServiceturnLog')->countSlg($transferWhere);
        // 答问比.
        $replyWhere['time_line'] = [
            'between',
            [
                strtotime($startTime),
                strtotime($endTime),
            ],
        ];
        $allChatLog = model('ChatLog')->selectChatLog($replyWhere);
        $replyWhere['from_id'] = ['like', 'KF%'];
        $reply = model('ChatLog')->selectChatLog($replyWhere);


        $this->assign([
            'xData' => json_encode($xData),
            'chatData' => json_encode($chatData),
            'serviceData' => json_encode($serviceData),
            'queueData' => json_encode($queueData),
            'systemCount' => json_encode($systemCount),
            'defaultTime' => json_encode($defaultTime),
            'allChat' => $allChat,
            'allService' => $allService,
            'allQueue' => $allQueue,
            'transfer' => floor($transfer / 2),
            'reply' => ($allChatLog ? sprintf("%.2f", (($reply / ($allChatLog - $reply)) * 100)) : '--'),
            'waitTime' => ($allQueue ? ceil($alllWaitTime / $allQueue) : '--'),
            'serviceTime' => ($allService ? ceil($serviceTime / $allService) : '--'),
            'cpdTime' => ($allService ? ceil($cpdTime / $allService) : '--'),
            'evaluateGood' => (($evaluateGood + $evaluateSecondary + $evaluateBad) ? sprintf("%.2f", (($evaluateGood / ($evaluateGood + $evaluateSecondary + $evaluateBad)) * 100)) : '--'),
            'evaluateGet' => (($evaluateNo + $evaluateGood + $evaluateSecondary + $evaluateBad) ? sprintf("%.2f", ((($evaluateGood + $evaluateSecondary + $evaluateBad) / ($evaluateNo + $evaluateGood + $evaluateSecondary + $evaluateBad)) * 100)) : '--'),
            'evaluate' => json_encode([
                'evaluateGood' => $evaluateGood,
                'evaluateSecondary' => $evaluateSecondary,
                'evaluateBad' => $evaluateBad,
                'evaluateNo' => $evaluateNo,
            ]),
        ]);

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

    }//end index()


    public function attendancereport()
    {

        $goups = (new GroupsModel)->where(['status' => 1])->select();
        $serday1 = date("Y-m-d", strtotime('-7 day'));
        $serday2 = date("Y-m-d", strtotime('-1 day'));
        $defday = $serday1 . ' , ' . $serday2;
        $toExcel = input('param.toExcel', 0);


        if (request()->isAjax() || $toExcel) {

            $param = input('param.');
            $limit = $param['pageSize'];
            $offset = ($param['pageNumber'] - 1) * $limit;

            $param = input('param.searchText');
            if (empty($param)) {
                $param = '0|0';
            }
            list($zone, $sgroup) = explode('|', $param);
            if (empty($zone)) {
                $zoneArray1 = [$serday1, $serday2];
            } else {
                $zonesarr = explode(',', $zone);
                $dd = $this->Date_segmentation($zonesarr['0'], $zonesarr['1']);
                $zoneArray1 = $dd['days_list'];
            }
            $zoneArray = array_map(function ($i) {
                return "'$i'";
            }, $zoneArray1);
            $uidarr = (new Usersmodel())->getUidsBygid(intval($sgroup), 1, 1);

            if (!$uidarr) {
                return json(['rows' => [], 'total' => 0]);
            }

            $model1 = new ServicetimelogModel();
            $model2 = new  KfstatetimesModel();
            $ret1 = $model1->getDayData($zoneArray1, $uidarr['uids']);
            $ret2 = $model2->getDayData($zoneArray1, $uidarr['uids']);
            if (!$toExcel) {
                $retall = $this->reportDataFormat($ret1, $ret2, $uidarr['objs'], $zoneArray1, $limit, $offset);
                return json($retall);
            } else {
                $title = ['账号', '姓名', '日期', '首次登陆时间', '最后登出时间', '在线时长', '登陆时长', '隐身时长', '休息时长', '在线占比', '隐身占比', '休息占比'];
                $titleKey = ['account', 'uname', 'day', 'of1', 'of0', 'all', 's1', 's2', 's3', 's1p', 's2p', 's3p'];
                $retall = $this->reportDataFormat($ret1, $ret2, $uidarr['objs'], $zoneArray1, $limit, $offset, true);
                (new Office())->outdata('考勤数据导出', $retall, $title, $titleKey);
                return true;
            }
        }

        $this->assign('groups', $goups);
        $this->assign('dzone', $defday);

        return $this->fetch();
    }

    function reportDataFormat($onoffdata, $timesdata, $uobjarr, $dataArray, $limit, $offset, $returnAll = 0)
    {
        $allData = [];
        $all_last = [];
        $stnulldata = ['s0' => 0, 's1' => 0, 's2' => 0, 's3' => 0, 'all' => 0, 's1p' => 0, 's2p' => 0, 's3p' => 0];

        foreach ($dataArray as $day) {
            foreach ($uobjarr as $uid => $user) {
                $kfuid = 'KF' . $uid;
                if (isset($onoffdata[$day][$kfuid])) {
                    $allData[$day][$kfuid] = ['of0' => $onoffdata[$day][$kfuid][0], 'of1' => $onoffdata[$day][$kfuid][1]];
                } else {
                    $allData[$day][$kfuid] = ['of0' => '', 'of1' => ''];
                }

                $uinfo_now = ['uname' => $uobjarr[$uid]->user_name, 'account' => $uobjarr[$uid]->user_account, 'day' => $day, 'uid' => $uid];
                if (isset($timesdata[$day][$kfuid])) {
                    $all_last[] = array_merge($timesdata[$day][$kfuid], $allData[$day][$kfuid], $uinfo_now);
                } else {
                    $all_last[] = array_merge($stnulldata, $allData[$day][$kfuid], $uinfo_now);
                }
            }
        }

        usort($all_last, function ($a, $b) {
            return $a['uid'] == $b['uid'] ? (($a['day'] > $b['day']) ? 1 : -1) : ($a['uid'] > $b['uid'] ? 1 : -1);
        });

        foreach ($all_last as $key => $val) {
            $all_last[$key]['s1'] = secendToHourMinit($all_last[$key]['s1']);
            $all_last[$key]['s2'] = secendToHourMinit($all_last[$key]['s2']);
            $all_last[$key]['s3'] = secendToHourMinit($all_last[$key]['s3']);
            $all_last[$key]['all'] = secendToHourMinit($all_last[$key]['all']);
            $all_last[$key]['s1p'] = perDisplay($all_last[$key]['s1p']);
            $all_last[$key]['s2p'] = perDisplay($all_last[$key]['s2p']);
            $all_last[$key]['s3p'] = perDisplay($all_last[$key]['s3p']);
            unset($all_last[$key]['uid']);
            unset($all_last[$key]['s0']);
        }


        //导出数据
        if ($returnAll) {
            return $all_last;
        }

        $len = count($all_last);
        $return = ['rows' => [], 'total' => $len];
        if ($offset >= $len) {
            return $return;
        }

        $begin = $offset;
        $end = ($offset + $limit);
        $end = ($end > $len) ? $len - 1 : $end;

        for ($i = $begin; $i < $end; $i++) {
            $return['rows'][] = $all_last[$i];
        }

        return $return;
    }


    /**
     * 时间分割
     *
     * @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;
        }

        $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()
    {
        $toExcel = input('param.toExcel', 0);

        if (request()->isAjax() || $toExcel) {
            $param = input('param.');
            $limit = $param['pageSize'];
            $offset = ($param['pageNumber'] - 1) * $limit;
            $start = time() - 86400;
            $end = time() - 86400;

            $where = [];
            //用户名称
            if (strlen($param['searchText'])) {
                if($param['user'] == 'user_account'){
                    $where['user_account'] = array('like', '%' . $param['searchText'] . '%');
                }
                if($param['user'] == 'user_name'){
                    $where['user_name'] = array('like', '%' . $param['searchText'] . '%');
                }
            }
            if (strlen($param['start']) && strlen($param['end']) && $param['start'] <= $param['end']) {
                $start = strtotime($param['start']);
                $end = strtotime($param['end'] . ' 23:59:59');
            }
            $result = db('users')->limit($offset, $limit)->where($where)->order('id','asc')->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();
                //回复率
                $result[$key]['reply'] = '';
                if (!empty($result[$key]['service_num'])) {
                    $n = $a / $result[$key]['service_num'] * 100;
                    $result[$key]['reply'] = round($n, 2) . '%';
                }

                //答问比
                $result[$key]['answer_problem'] = '';
                if ($result[$key]['receive_words'] != 0) {
                    $num = $result[$key]['send_words'] / $result[$key]['receive_words'] * 100;
                    $result[$key]['answer_problem'] = round($num, 2) . '%';
                }
                if($result[$key]['receive_words'] == 0 && $result[$key]['send_words'] != 0){
                    $result[$key]['answer_problem'] = '100%';
                }
                if($result[$key]['receive_words'] == 0 && $result[$key]['send_words'] == 0){
                    $result[$key]['answer_problem'] = '0%';
                }
                //平均会话时长
                $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();
                //参评率
                $result[$key]['appraise'] = '';
                if ($result[$key]['service_num'] != 0) {
                    $result[$key]['appraise'] = $m / $result[$key]['service_num'] * 100;
                    $result[$key]['appraise'] = round($result[$key]['appraise'], 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();
                //相对满意度率
                $result[$key]['pleased'] = '';
                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;

            if (!$toExcel) {
                return json($return);
            } else {
                $head = ['账号', '名称', '工号', '接入会话量', '总消息量', '接入转接量', '回复率', '答问比', '平均会话时长', '平均响应时长', '参评率', '相对满意度率', '满意评价次数', '一般评价次数', '不满意评价次数', '留言处理量'];
                $key = ['user_account', 'user_name', 'user_job_number', 'service_num', 'send_words', 'transfer_in', 'reply', 'answer_problem', 'conversation', 'response', 'appraise', 'pleased', 'pleased_num', 'commonly', 'no_pleased', 'message'];
                (new Office())->outdata('工作报表数据导出', $result, $head, $key);
                return true;
            }
            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