playlet/app/Service/CircleMassMs...

<?php
/**
 * Created by PhpStorm.
 * User: shensong
 * Date: 2022/11/10
 * Time: 14:39
 */

namespace App\Service;


use App\Log;
use App\Models\CircleMassMsgRecord;
use App\Models\CustomerDetails;
use App\Models\DjUser;
use App\Models\Es\CircleMassMsgSendDetailEs;
use App\Models\System\Users;
use App\Support\EmailQueue;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class CircleMassMsgRecordService
{
    /**
     * 获取群发列表
     * @param $corpid string 企业ID
     * @param $ruleId integer
     * @param $sendTimeStart string 发送时间查询-起始时间
     * @param $sendTimeEnd string 发送时间查询-结束时间
     * @param $sortColumn string 排序字段
     * @param $sortMethod string 排序方式*
     * @param $page integer 当前页码数
     * @param $pageSize integer 每页显示条数
     * @return mixed
     * */
    public static function recordList($corpid, $ruleId, $sendTimeStart, $sendTimeEnd, $sortColumn, $sortMethod
        , $page, $pageSize, &$errno)
    {
        try {
            list($list, $count) = CircleMassMsgRecord::getRecordLists($corpid, $ruleId, $sendTimeStart
                , $sendTimeEnd, $sortColumn, $sortMethod, $page, $pageSize);

            # 将该企微下的所有客服信息查询出来备用
            $adminData = Users::query()->select(['id','name'])->get();

            # 处理数据
            foreach($list as $datum) {
                # 消息条数
//                $contentCount = empty($datum->content) ? 0 : 1;
//                $attachmentCount = empty($datum->attachments) ? 0 : count(json_decode($datum->attachments, true));
//                $datum->msg_count = $contentCount + $attachmentCount;

                # 解析json，读取发送规则以及标题
                $filterData = json_decode($datum->filter_data, 1);
                $adminId = $filterData['admin_id'] ?? 0;

                # 创建人信息
                $adminInfo = $adminData->where('id', $adminId)->first();
                $datum->creator = isset($adminInfo->name) ? $adminInfo->name : '';

                # 群发标题
                $datum->name = $filterData['name'] ?? '';

                # 群发规则
                $datum->rule_detail = self::getRuleDetail($filterData);

                unset($datum->filter_data);
            }
        } catch (\Exception $e) {
            Log::logError('获取群发列表过程发生异常', [
                'file'  => $e->getFile(),
                'line'  => $e->getLine(),
                'msg'   => $e->getMessage(),
                'trace' => $e->getTraceAsString(),
            ], 'CircleMassMsgRuleList');

            $errno = 3601;

            return [[], 0];
        }

        return [$list, $count];
    }

    public static function getRuleDetail($ruleInfo)
    {
//        # 客户添加客服时间
//        $addTime = turn_minute($ruleInfo['interval_time']);
//
//        $ruleDetail = '客户添加客服'.$addTime.'后;';
//
//        # 通知时间
//        if(1 == $ruleInfo['interval_type']) {
//            // 指定时间
//            $timePointsArr = json_decode($ruleInfo['time_points'], 1);
//            foreach($timePointsArr as $timePoint) {
//                $ruleDetail .= '当天'.$timePoint.'提醒发送;';
//            }
//
//            $ruleDetail = rtrim(';', $ruleDetail);
//        } else {
//            // 间隔时间
//            $ruleDetail .= '后台每'.$ruleInfo['interval'].'小时分批提醒发送';
//        }

        # 描述信息
        $days = floor($ruleInfo['interval_time'] / 60 / 24);
        $hour = ($ruleInfo['interval_time'] / 60) % 24;
        $minute = $ruleInfo['interval_time'] % 60;

        $ruleDetail = '客户加客服' .$days. '天' .$hour. '小时' .$minute. '分钟后,';
        if($ruleInfo['interval_type'] == 1) {
            $ruleDetail .= '当天';
            $timePoints = json_decode($ruleInfo['time_points'], true);
            foreach ($timePoints as $points) {
                $ruleDetail .= $points . '  ';
            }

            $ruleDetail .= "提醒发送";
        } elseif ($ruleInfo['interval_type'] == 2) {
            $ruleDetail .= '后台每'.$ruleInfo['interval'].'小时分批提醒发送';
        }

        return $ruleDetail;
    }

    /**
     * 获取群发详情
     * @param $corpid string 企业ID
     * @param $ruleId integer 群发规则ID
     * @param $sendNum integer
     * @param $sendDate string
     * @return mixed
     * */
    public static function recordDetail($corpid, $ruleId, $sendNum, $sendDate, &$errno)
    {
        try{
            $detail = [];

            # 获取发送记录
            $recordList = CircleMassMsgRecord::query()->selectRaw('corpid, rule_id, send_num, create_time
            , send_date, content, attachments, filter_data, status')->where('corpid', $corpid)
                ->where('rule_id', $ruleId)->where('send_num', $sendNum)
                ->where('send_date', $sendDate)->get();

            if($recordList->isEmpty()) return [];

            # 判断发送状态
            $statusList = array_column($recordList->toArray(), 'status');
            foreach($recordList as $recordInfo) {
                $detail = json_decode(json_encode($recordInfo), 1);
            }
            $detail['status'] = self::getRecordSendStatus($statusList);

            # 群发规则信息
            $ruleInfo = json_decode($detail['filter_data'], 1);
            $detail['rule_info'] = $ruleInfo;

            if($ruleInfo['is_all'] != 1 && !empty($ruleInfo['senders'])) {
                # 发送人
                $detail['sender_name'] = DjUser::query()->where("corpid",$corpid)
                    ->whereIn("user_id",explode(',',$ruleInfo['senders']))
                    ->pluck("name")->toArray();
            }

            # 群发规则
            $detail['rule_detail'] = self::getRuleDetail($ruleInfo);

            # 获取创建人信息
            $detail['creator'] = Users::query()->where('id', $ruleInfo['admin_id'])->value('name');

            # 处理附件
            $attachments = json_decode($detail['attachments'], 1);
            if(!empty($attachments)) {
                foreach ($attachments as $key=>&$attachment) {
                    if(isset($attachment['msgtype']) && $attachment['msgtype'] == 'radar') {    // 雷达附件信息回显
                        $radarId = $attachment['radar']['radar_id'] ?? 0;
                        $radarInfo = RadarService::getRadarContent($corpid, $radarId);
                        if(empty($radarInfo)) {
                            unset($attachment[$key]);
                            continue;
                        }
                        $attachment['radar'] = $radarInfo;
                    }
                }
            }

            # 消息条数
            $contentCount = empty($detail['content']) ? 0 : 1;
            $attachmentCount = empty($detail['attachments']) ? 0 : count($attachments);
            $detail['msg_count'] = $contentCount + $attachmentCount;
            $detail['attachments'] = json_encode($attachments, 256);

        } catch (\Exception $e) {
            Log::logError('群发详情获取过程发生异常', [
                'line'      => $e->getLine(),
                'msg'       => $e->getMessage(),
                'trace'     => $e->getTraceAsString(),
                'rule_id'   => $ruleId,
                'send_date' => $sendDate,
                'send_num'  => $sendNum,
            ], 'circleRecordDetail');

            $errno = 3602;
            return [];
        }

        return $detail;
    }

    public static function getRecordSendStatus($statusList)
    {
        if(array_search('-1', $statusList)){// 含有-1（发送失败状态）
            return -1;
        } else if(array_search('1', $statusList)){// 含有1 （发送中状态）
            return 2;
        } else {// 全部为2(发送完成状态)
            return 3;
        }
    }

    public static function overview($corpid, $ruleId, $sendNum, $sendDate)
    {
        $sendRecordTotal =  CircleMassMsgRecord::query()->where("corpid",$corpid)
            ->where("rule_id",$ruleId)
            ->where('send_num', $sendNum)
            ->where('send_date', $sendDate)
            ->selectRaw("count(CASE WHEN status = 1 THEN 1 END) as un_sender_count")  // 未发送成员
            ->selectRaw("count(CASE WHEN status = 2 THEN 1 END) as sender_count")  // 已发送成员
            ->selectRaw("sum(send_success) as send_success")  // 发送成功
            ->selectRaw("sum(send_fail) as send_fail")  // 未送达人数
            ->first();

        $sendRecordTotal->send_success = empty($sendRecordTotal->send_success) ? 0 : $sendRecordTotal->send_success;
        $sendRecordTotal->send_fail    = empty($sendRecordTotal->send_fail) ? 0 : $sendRecordTotal->send_fail;

        $retData = [];
        #已发送人员
        $retData['executed_sender_count'] = $sendRecordTotal->sender_count;
        #发送用户
        $retData['receive_customer_count'] = $sendRecordTotal->send_success;
        #未发送成员
        $retData['un_execute_sender_count'] = $sendRecordTotal->un_sender_count;
        #未送达客户
        $retData['un_receive_customer_count'] = $sendRecordTotal->send_fail ;
        #客户接收达上限
        $retData['receive_fail_with_limit'] = CircleMassMsgSendDetailEs::massSendCustCount($ruleId, $sendNum, $sendDate, null,3);
        #因为不是好友发送失败
        $retData['receive_fail_with_not_friend'] = CircleMassMsgSendDetailEs::massSendCustCount($ruleId, $sendNum, $sendDate, null,4);

        return $retData;
    }

    public static function senderList($corpid, $ruleId, $sendNum, $sendDate, $type, $keyword, $page, $pageSize)
    {
        /**已发送成员统计**/
        $sendRecord =  CircleMassMsgRecord::query()->where("corpid",$corpid)
            ->where("rule_id",$ruleId)
            ->where('send_num', $sendNum)
            ->where('send_date', $sendDate)
            ->select(["sender", "filter_data"])
            ->selectRaw("min(send_time) as send_time")  //发送时间
            ->selectRaw("sum(send_fail) as send_fail")  //失败次数
            ->selectRaw("sum(send_success) as send_success") //成功次数
            ->groupBy("sender")
            ->get()->toArray();

        $ruleInfo = isset($sendRecord[0]['filter_data']) ? json_decode($sendRecord[0]['filter_data'], 1) : [];
        $lastSendTime = isset($ruleInfo['last_send_time']) ? strtotime($ruleInfo['last_send_time']) : null;
        $nextSendTime = isset($ruleInfo['next_send_time']) ? strtotime($ruleInfo['next_send_time']) : null;

        $sendRecord = array_column($sendRecord, null, 'sender');

        #根据查询类型不同，获取当页展示的成员ID集合
        $allSendersIdArr = [];
        switch ($type){
            case "all":     //全部成员
                $allSendersIdArr = CircleMassMsgRecord::query()
                    ->select('sender')
                    ->where("rule_id",$ruleId)
                    ->where('send_num', $sendNum)
                    ->where('send_date', $sendDate)
                    ->pluck('sender');
                break;
            case "sent":    //已发送的成员
                $allSendersIdArr = CircleMassMsgRecord::query()
                    ->select('sender')
                    ->where("rule_id",$ruleId)
                    ->where('send_num', $sendNum)
                    ->where('send_date', $sendDate)
                    ->where('status', 2)
                    ->pluck('sender');
                break;
            case "unsent":  //未发送的成员
                $allSendersIdArr = CircleMassMsgRecord::query()
                    ->select('sender')
                    ->where("rule_id",$ruleId)
                    ->where('send_num', $sendNum)
                    ->where('send_date', $sendDate)
                    ->where('status', 1)
                    ->pluck('sender');
                break;
            case "fail":    //发送失败的成员
                $allSendersIdArr = CircleMassMsgRecord::query()
                    ->select('sender')
                    ->where("rule_id",$ruleId)
                    ->where('send_num', $sendNum)
                    ->where('send_date', $sendDate)
                    ->where('status', -1)
                    ->pluck('sender');
                break;
        }

        if(!empty($allSendersIdArr) && !is_array($allSendersIdArr)) $allSendersIdArr = $allSendersIdArr->toArray();

        /**查询发送成员**/
        $query = DjUser::query()->where("corpid",$corpid)
            ->whereIn("user_id",$allSendersIdArr);
        /**关键词查询**/
        if(!empty($keyword)){
            $query->where("name","like","%$keyword%");
        }

        $total = $query->count();   //总计总数
        $list  = $query->select("user_id","name","avatar")
            ->offset(($page-1)*$pageSize)
            ->limit($pageSize)
            ->get()->toArray();

        /**查询客服对应客户数**/
        $userCustomerCountList = CustomerDetails::suffix($corpid)
            ->selectRaw('user_id, count(1) as count')
            ->where('loss_status', 1)
            ->where('corpid', $corpid)
            ->whereIn('user_id', array_column($list,'user_id'))
            ->where(function($query) use ($lastSendTime, $nextSendTime) {
                if($lastSendTime && $nextSendTime){
                    $query->where('createtime', '>=', $lastSendTime)
                        ->where('createtime', '<', $nextSendTime);
                }
            })
            ->groupBy('user_id')
            ->get()
            ->keyBy('user_id')
            ->toArray();
        foreach ($list as $k => $item){
            #补充最早群发时间
            $list[$k]['send_time']  = isset($sendRecord[$item['user_id']]['send_time']) ? $sendRecord[$item['user_id']]['send_time'] : '';
            #补充好友数量
            $list[$k]['customer_num'] = isset($userCustomerCountList[$item['user_id']]['count']) ? $userCustomerCountList[$item['user_id']]['count'] : 0;
            #已送达人数
            $list[$k]['send_fail']  = isset($sendRecord[$item['user_id']]['send_fail']) ? $sendRecord[$item['user_id']]['send_fail'] : 0;
            #未送达人数
            $list[$k]['send_success']  = isset($sendRecord[$item['user_id']]['send_success']) ? $sendRecord[$item['user_id']]['send_success'] : 0;
        }

        return [$total,$list];
    }

    /*
     * 输出excel
     * @param $corpid
     * @param $ruleId
     * @param $sendNum
     * @param $sendDate
     * @param $type
     * @param $keyword
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     * @return mixed
     */
    public static function senderListExport($corpid, $ruleId, $sendNum, $sendDate, $type,$keyword)
    {
        try {
            list($total, $list) = self::senderList($corpid, $ruleId, $sendNum, $sendDate, $type, $keyword, 1, 100000);

            $spreadSheet = new Spreadsheet();
            $sheet = $spreadSheet->getActiveSheet();

            $rowIndex = 1;
            $columnIndex = 1;

            /**设置标题**/
            $title = ['成员昵称', '群发时间', '好友数量', '已送达人数', '发送失败次数'];
            foreach ($title as $name) {
                $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $name);
            }

            /**列表内容**/
            foreach ($list as $row => $item) {
                $rowIndex++;
                $columnIndex = 1;     //重置列索引
                $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $item['name']);
                $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $item['send_time']);
                $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $item['customer_num']);
                $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $item['send_success']);
                $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $item['send_fail']);
            }

            $exportType = [
                'all'    => '全部成员',
                'sent'   => '已发送成员',
                'unsent' => '未发送成员',
                'fail'   => '发送失败成员',
            ];
            $fileNameExt = isset($exportType[$type]) ? $exportType[$type] : "列表";
            $writer = IOFactory::createWriter($spreadSheet, 'Xlsx');
            header('Content-Type:application/vnd.ms-excel');
            header('Content-Disposition:attachment;filename=客户群发成员详情导出-' . $fileNameExt . '.xlsx');
            header('Cache-Control:max-age=0');
            $writer->save('php://output');

        } catch (\Exception $e) {
            $logContent = [
                'params' => "corpid=$corpid , ruleId=$ruleId , sendNum=$sendNum, sendDate=$sendDate, type=$type, keyword=$keyword",
                'msg'    => $e->getMessage(),
                'line'   => $e->getLine()
            ];
            EmailQueue::rPush('导出客户群发成员详情出现错误', json_encode($logContent), ['song.shen@kuxuan-inc.com'], '猎羽');
            return false;
        }
    }

    /*
     * 群发详情-客户详情列表
     */
    public static function massSendCustList($ruleId, $sendNum, $sendDate, $sender, $name, $type, $page, $pageSize)
    {
        return CircleMassMsgSendDetailEs::massSendCustList($ruleId, $sendNum, $sendDate, null, $sender, $name, $type, $page, $pageSize);
    }

    /*
     * 群发详情-客户详情列表
     **/
    public static function massSendCustListExport($ruleId, $sendNum, $sendDate, $sender, $name, $type)
    {
        try {
            $spreadSheet =  new Spreadsheet();
            $sheet = $spreadSheet->getActiveSheet();

            $rowIndex = 1;
            $columnIndex = 1;

            /**设置标题**/
            $title = ['客户昵称','发送成员','送达时间','状态描述'];
            foreach ($title as $titleVal){
                $sheet->setCellValueByColumnAndRow($columnIndex++,$rowIndex,$titleVal);
            }

            $page = 1;
            $pageSize = 1000;
            while (1){
                list($list,$total) =  CircleMassMsgSendDetailEs::massSendCustList($ruleId, $sendNum, $sendDate, null
                    , $sender, $name, $type, $page++, $pageSize);
                /**列表内容**/
                foreach ($list as $row =>$item){
                    $rowIndex++;
                    $columnIndex=1;     //重置列索引
                    $sheet->setCellValueByColumnAndRow($columnIndex++,$rowIndex,$item['external_username']);
                    $sheet->setCellValueByColumnAndRow($columnIndex++,$rowIndex,$item['sender_name']);
                    $sheet->setCellValueByColumnAndRow($columnIndex++,$rowIndex,$item['send_time']);
                    $sheet->setCellValueByColumnAndRow($columnIndex++,$rowIndex,$item['status_description']);
                }
                if ($total<$pageSize) break;    //停止游标获取完整列表
            }

            $writer = IOFactory::createWriter($spreadSheet, 'Xlsx');
            header('Content-Type:application/vnd.ms-excel');
            header('Content-Disposition:attachment;filename=客户群发详情导出.xlsx');
            header('Cache-Control:max-age=0');
            $writer->save('php://output');


        } catch(\Exception $e) {
            $logContent = [
                'params' => " ruleId=$ruleId , sendNum=$sendNum, sendDate=$sendDate, sender=$sender , name=$name , type=$type",
                'msg'    => $e->getMessage(),
                'line'   => $e->getLine()
            ];
            EmailQueue::rPush('导出客户群发详情出现错误', json_encode($logContent), ['song.shen@kuxuan-inc.com'], []);
            return false;
        }

    }
}