playlet/app/Service/MassMsgRuleS...

<?php


namespace App\Service;


use App\Log;
use App\Models\AuthorizeCorp;
use App\Models\CustomerAssignmentRecord;
use App\Models\CustomerDetails;
use App\Models\DjUser;
use App\Models\MassMsg;
use App\Models\MassMsgRecord;
use App\Models\Material;
use App\Models\OperationGroup;
use App\Models\RadarCustomerDetail;
use App\Models\System\Users;
use App\Models\Tag;
use App\Models\TencentAdConf;
use App\Models\View\CustomerDetail;
use App\RedisModel;
use App\Support\EmailQueue;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use App\Models\Es\MassMsgSendDetailEs;

class MassMsgRuleService
{
    /*
     * 设置群发消息规则
     * */
    public static function setRule($ruleId, $params)
    {
        try {
            if(2 == $params['operate_type']){
                if(1 == $params['is_operation']) {
                    $senders = MassMsgRuleService::getSenderListByOperatorGroupId($params['operator_group_id']);
                    $params['senders'] = '';
                } else {
                    $senders = json_decode($params['multiple_senders'], 1);
                    $params['senders'] = $params['multiple_senders'];
                }

                $corpIdList = MassMsgRuleService::getCorpIdListBySenders($senders);
                $params['corpid'] = implode(',', $corpIdList);

            } else {
                # 校验附件信息合法性
                $attachmentsVerifyCode = MassMsgRuleService::attachmentsVerify($params['attachments'], $params['corpid']);
                if($attachmentsVerifyCode) return $attachmentsVerifyCode;
            }

            if($ruleId){    // 编辑群发规则
                unset($params['multiple_senders']);
                # 判断是否为未发送状态
                $ruleInfo = MassMsg::where('id', $ruleId)->first();
                if(!isset($ruleInfo->enable) || !in_array($ruleInfo->enable, [-1, 1])) {
                    return 2102;
                }

                $result = MassMsg::where('id', $ruleId)->update($params);
            } else {
                # 设置新群发规则
                $massMsgModel = new MassMsg();
                $massMsgModel->admin_id          = $params['admin_id'];
                $massMsgModel->corpid            = $params['corpid'];
                $massMsgModel->name              = $params['name'];
                $massMsgModel->send_type         = $params['send_type'];
                $massMsgModel->send_time         = $params['send_time'];
                $massMsgModel->senders           = $params['senders'];
                $massMsgModel->chat_type         = $params['chat_type'];
                $massMsgModel->customer_filter   = $params['customer_filter'];
                $massMsgModel->content           = $params['content'];
                $massMsgModel->attachments       = html_entity_decode($params['attachments']);
                $massMsgModel->gender            = $params['gender'];
                $massMsgModel->is_customize_time = $params['is_customize_time'];
                $massMsgModel->timeline          = $params['timeline'];
                $massMsgModel->add_time_start    = $params['add_time_start'];
                $massMsgModel->add_time_end      = $params['add_time_end'];
                $massMsgModel->tag_screen_type   = $params['tag_screen_type'];
                $massMsgModel->tag_list          = $params['tag_list'];
                $massMsgModel->exclude_tag_list  = $params['exclude_tag_list'];
                // 付费情况
                $massMsgModel->pay_status        = $params['pay_status'];
                $massMsgModel->pay_num_min       = $params['pay_num_min'];
                $massMsgModel->pay_num_max       = $params['pay_num_max'];
                # 运营类型
                $massMsgModel->operate_type      = $params['operate_type'];
                # 添加客服n days之后
                $massMsgModel->add_days_later    = $params['add_days_later'];
                $massMsgModel->operator_group_id = $params['operator_group_id'];
                $massMsgModel->is_operation      = $params['is_operation'];

                $result = $massMsgModel->save();
                $ruleId = isset($massMsgModel->id) ? $massMsgModel->id : '';
            }

            if(!$result) return 2103;

            if(2 == $params['operate_type']){
                RedisModel::lPush(MassMsg::MASS_MSG_ESTIMATED_MULTIPLE_CORP_USER_COUNT_RDS, $ruleId);
            } else {
                RedisModel::lPush(MassMsg::MASS_MSG_ESTIMATED_USER_COUNT_RDS, $ruleId);
            }
        } catch (\Exception $e) {
            Log::logError('群发规则设置发生异常', [
                'line' => $e->getLine(),
                'msg' => $e->getMessage(),
                'params' => $params
            ], 'SetMassMsgRule');

            return 2101;
        }

        return 0;
    }

    public static function getSenderListByOperatorGroupId($operatorGroupId)
    {
        $errno = 0;
        $operationDetail = OperationGroupService::groupDetail($operatorGroupId, -1, $errno);

        if($errno > 0) {
            EmailQueue::rPush('根据运营组ID查询运营信息失败', json_encode(['group_id' => $operatorGroupId]), ['song.shen@kuxan-inc.com'], '猎羽');
            return [];
        }

        return array_map(function($item) {
            $item['corpid'] = $item['app_id'];
            return $item;
        },json_decode(json_encode($operationDetail->corp_user_list), 1));
    }

    /*
     * 获取群发条件匹配的外部联系人数
     * */
    public static function getCustomerMatchCount($params, &$total)
    {
        try {
            $corpid = $params['corpid'];
            if(!is_array($params['senders'])) {
                $senders = !empty($params['senders']) ? explode(',', $params['senders']) : [];
            } else {
                $senders = $params['senders'];
            }
//            $search = array('loss_status' => 1);
            $search = array('can_receive' => 1, 'loss_status' => 1);
            $customerFilter = $params['customer_filter'];
            if($customerFilter){
                $gender = (!is_null($params['gender']) && $params['gender'] != '') ? explode(',', $params['gender']) : [];
                $isCustomizeTime = $params['is_customize_time'];

                if($isCustomizeTime) {
                    $addTimeStart = $params['add_time_start'];
                    $addTimeEnd = $params['add_time_end'];
                } else {
                    $timeLine = $params['timeline'];
                    $sendTime = is_null($params['send_time']) ? date('Y-m-d H:i:s') : $params['send_time'];
                    $addTimeStart = date('Y-m-d H:i:s', strtotime('-'.$timeLine.' hours', strtotime($sendTime)));
                    $addTimeEnd = $sendTime;
                }

                $addDaysLater = $params['add_days_later'] ?? null;
                if($addDaysLater) {
                    $addDaysStart = null;
                    $addDaysEnd = date('Y-m-d H:i:s', strtotime('-'.$addDaysLater.' days', time()));
                    $addTimeStart = !empty($addTimeStart) ? $addTimeStart : $addDaysStart;
                    if(!empty($addTimeEnd)) {
                        $addTimeEnd = ($addTimeEnd < $addDaysEnd) ? $addTimeEnd : $addDaysEnd;
                    } else {
                        $addTimeEnd = $addDaysEnd;
                    }
                }

                $tagScreenType = $params['tag_screen_type'] ?? null;
                $tagList = $params['tag_list'] ?? null;
                $excludeTagList = $params['exclude_tag_list'] ?? null;

                // 付费情况
                $payStatus = $params['pay_status'];
                $payNumMin = $params['pay_num_min'];
                $payNumMax = $params['pay_num_max'];

                if(!empty($gender)) {
                    $search['gender'] = $gender;
                }


                if($addTimeStart) {
                    $addTimeStartTimestamp = strtotime($addTimeStart);
                    if($addTimeStartTimestamp !== false) {
                        $search['add_time_start'] = $addTimeStartTimestamp;
                    }
                }

                if($addTimeEnd) {
                    $addTimeEndTimestamp = strtotime($addTimeEnd);
                    if($addTimeEndTimestamp !== false) {
                        $search['add_time_end'] = $addTimeEndTimestamp;
                    }
                }

                if($tagScreenType) {
                    $search['tag_screen_type'] = $tagScreenType;
                    if($tagScreenType == 1 && empty($tagList)) {
                        $total = 0;
                        return 400;
                    }

                    $search['tag_list'] = explode(',', $tagList);
                    # 检测标签内容为标签名称还是md5值，若为前者则将标签名称替换
                    if(isset($search['tag_list'][0]) && strlen($search['tag_list'][0]) != 32) {
                        $search['tag_list'] = Tag::getTagListByName($corpid, $search['tag_list']);
                    }
                }

                if($excludeTagList) {
                    $search['exclude_tag_list'] = explode(',', $excludeTagList);
                    if(isset($search['exclude_tag_list'][0]) && strlen($search['exclude_tag_list'][0]) != 32) {
                        $search['exclude_tag_list'] = Tag::getTagListByName($corpid, $search['exclude_tag_list']);
                    }
                }

                if (is_numeric($payStatus)) {
                    $search['pay_status'] = $payStatus;
                }

                if (is_numeric($payNumMin) && $payNumMin > 0) {
                    $search['pay_num_min'] = $payNumMin;
                }

                if (is_numeric($payNumMax) && $payNumMax > 0) {
                    $search['pay_num_max'] = $payNumMax;
                }
            }

            if(!empty($senders)) {
                $search['user_ids'] = $senders;
            }

            Log::logInfo('', $search, 'multiple_corp_tag_test');

            $total = CustomerDetails::getCustomerMatchCount($corpid, $search);
        } catch (\Exception $e) {
            Log::logError('获取群发条件匹配的外部联系人数过程发生异常', [
                'line' => $e->getLine(),
                'msg' => $e->getMessage()
            ], 'GetCustomerTotal-Exception');
            $total = 0;
        }

        return 0;
    }

    public static function getMultipleCorpCustomerMatchCount($params, &$total)
    {
        try {
            $data = $params;
            if(1 == $params['is_operation']) {
                $senders = self::getSenderListByOperatorGroupId($params['operator_group_id']);//从运营组中查询客服以及企微信息
            } else {
                $senders = json_decode($params['multiple_senders'], 1);
            }

            foreach ($senders as $item) {
                $singleTotal = 0;
                $data['corpid'] = $item['corpid'];
                $data['senders'] = implode(',', $item['user_list']);
                self::getCustomerMatchCount($data, $singleTotal);

                $total += $singleTotal;
            }

        } catch (\Exception $exception) {
            Log::logError('多企微获取群发条件匹配的外部联系人数过程发生异常', [
                'params' => $params,
                'line' => $exception->getLine(),
                'msg' => $exception->getMessage()
            ], 'GetCustomerTotal-Exception');

            EmailQueue::rPush('多企微获取群发条件匹配的外部联系人数过程发生异常', '', ['song.shen@kuxuan-inc.com'], '猎羽');
        }
        return 0;
    }

    /*
     * 更新群发规则状态
     * @param $ruleId integer 群发规则ID
     * @param $status integer 群发规则待更新到的状态值 状态 -2:已删除 -1：发送失败 1:待发送 2:正在发送中 3:发送完成
     * */
    public static function updateStatus($ruleId, $status)
    {
        # 修改群发规则状态为已发送
        $updateStatus = MassMsg::updateRuleStatus($ruleId, $status);
        if(!$updateStatus) {
            Log::logError('规则状态更新失败', [
                'rule_id' => $ruleId,
                'status' => $status
            ], 'updateRuleStatus');
        }

        return $updateStatus;
    }

    /**
     * 更新群发状态
     * */
    public static function updateRuleStatus($corpid, $ruleId, $status)
    {
        # 验证规则是否存在
        $isExist = MassMsg::where('id', $ruleId)->exists();
        if(!$isExist) return 2106;

        # 变更规则状态
        $result = MassMsg::where('id', $ruleId)->update(['enable' => $status]);

        if(!$result) return 2107;

        return 0;
    }

    /*
     * 获取群发详情
     * @param $corpid string 企业ID
     * @param $ruleId integer 群发规则ID
     * */
    public static function ruleDetail($corpid, $ruleId, &$errno)
    {
        try{
            $detail = MassMsg::selectRaw('id as rule_id, admin_id, corpid, name, send_type, send_time,'
                .' senders, content, attachments, enable, gender, add_time_start, add_time_end, tag_screen_type, tag_list,'
                .' exclude_tag_list, customer_filter, estimated_user, pay_status, pay_num_min, pay_num_max,'
                .' is_customize_time, timeline, operate_type, operator_group_id, add_days_later, is_operation')
                ->where('id', $ruleId)->first();

            if(empty($detail)) return [];

            if(1 == $detail->operate_type) {
                # 发送人
                $detail->sender_name = DjUser::where("corpid",$corpid)
                    ->whereIn("user_id",explode(',',$detail->senders))
                    ->pluck("name")->toArray();
                $detail->sender_group = null;
            } else {
                $senderList = [];
                $senderSqlList = [];
                $senderArray = json_decode($detail->senders, 1);
                if(1 != $detail->is_operation) {
                    # 直接查询
                    foreach($senderArray as $value) {
                        foreach($value['user_list'] as $userId) {
                            $senderList[] = ['corpid' => $value['corpid'], 'user_id' => $userId];
                            $senderSqlList[] = '("'.$value['corpid'].'","'.$userId.'")';
                        }
                    }

                    $corpIdList = array_unique(array_column($senderArray, 'corpid'));
                    $corpInfoList = AuthorizeCorp::whereIn('corpid', $corpIdList)->get();
                    if(empty($senderSqlList)) {
                        $senderInfoList = DjUser::where('enable', 1)->get();
                    } else {
                        $senderInfoList = DjUser::whereRaw('(corpid, user_id) in (' . implode(',',$senderSqlList) . ')')
                            ->where('enable', 1)->get();
                    }


                    $senderNameList = [];
                    foreach ($senderList as $item) {
                        $corpName = $corpInfoList->where('corpid', $item['corpid'])->first();
                        $corpName = $corpName->corp_name ?? '';
                        $senderName = $senderInfoList->where('corpid', $item['corpid'])->where('user_id', $item['user_id'])->first();
                        $senderName = $senderName->name ?? '';
                        $senderNameList[] = $corpName.'-'.$senderName;
                    }

                    $detail->sender_name = $senderNameList;
                    $detail->multiple_senders = json_decode($detail->senders, 1);
                    $detail->sender_group = null;
                } else {
                    # 查询运营组信息
                    $detail->sender_name = null;
                    $detail->sender_group = OperationGroup::where('id', $detail->operator_group_id)
                        ->value('group_name');
                }
            }

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

            $attachments = json_decode($detail->attachments, true);
            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(),
                'rule_id' => $ruleId
            ], 'ruleDetail');

            $errno = 2104;
            return [];
        }

        return $detail;
    }

    /*
     * 获取群发列表
     * @param $corpid string 企业ID
     * @param $creatorId integer 创建人ID集合
     * @param $sendTimeStart string 发送时间查询-起始时间
     * @param $sendTimeEnd string 发送时间查询-结束时间
     * @param $createTimeStart string 创建时间查询-结束时间
     * @param $createTimeEnd string 创建时间查询-结束时间
     * @param $sortColumn string 排序字段
     * @param $sortMethod string 排序方式*
     * @param $page integer 当前页码数
     * @param $pageSize integer 每页显示条数
     * @param $errno
     * @return mixed
     * */
    public static function ruleList($corpid, $creatorId, $keyword, $sendTimeStart, $sendTimeEnd, $createTimeStart,$createTimeEnd
        ,$sortColumn,$sortMethod, $page, $pageSize, &$errno)
    {
        try {
            list($list, $count) = MassMsg::getRuleLists(
                $corpid, $creatorId, $keyword, $sendTimeStart, $sendTimeEnd,$createTimeStart
                ,$createTimeEnd,$sortColumn,$sortMethod, $page, $pageSize
            );

            # 获取创建人信息
            $adminIds = $list->pluck('admin_id');
            $adminData = Users::query()->select(['id','name'])->whereIn('id', $adminIds)->get();

            # 统计发送信息
            $ruleIds = $list->pluck('rule_id');
            $sendStatData = MassMsgRecord::selectRaw("rule_id, sum(send_success) as send_success, "
                ."sum(send_fail) as send_fail, sum(send_total) as send_total")
                ->whereIn('rule_id', $ruleIds)->where('type', 1)->groupBy(['rule_id'])->get();

            $errStatData = MassMsgRecord::query()->select(['errcode', 'rule_id', 'sender', 'corpid'])
                ->whereIn('rule_id', $ruleIds)->where('errcode', '!=', 0)->get();

            $senderIdList = [];
            foreach ($errStatData as $item) {
                $senderIdList[] = '("'.$item['corpid'].'","'.$item['sender'].'")';
            }

            if(!empty($senderIdList)) {
                $senderInfoList = DjUser::query()->whereRaw('(corpid, user_id) in ('. implode(',', $senderIdList).')')
                    ->where('enable', 1)->get();
            } else {
                $senderInfoList = DjUser::query()->where('enable', 1)->get();
            }

            $corpInfoList = AuthorizeCorp::query()->get();

            $errcodeConf = config('qyWechat.errcode');
            # 处理数据
            foreach($list as $datum) {
                # 展示企微名称
                $corpIdList = explode(',', $datum->corpid);
                $corpDataList = $corpInfoList->whereIn('corpid', $corpIdList)->all();
                $datum->corp_name = array_column($corpDataList, 'corp_name');
                # 消息条数
                $contentCount = empty($datum->content) ? 0 : 1;
                $attachmentCount = empty($datum->attachments) ? 0 : count(json_decode($datum->attachments, true));
                $datum->msg_count = $contentCount + $attachmentCount;

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

                # 统计发送情况
                $sendStatInfo = $sendStatData->where('rule_id', $datum->rule_id)->first();
                $datum->send_success = isset($sendStatInfo->send_success) ? $sendStatInfo->send_success : 0;
                $datum->send_fail = isset($sendStatInfo->send_fail) ? $sendStatInfo->send_fail : 0;
                $datum->send_total = isset($sendStatInfo->send_total) ? $sendStatInfo->send_total : 0;
                unset($datum->admin_id);

                # 查询失败信息
                $errList = $errStatData->where('rule_id', $datum->rule_id)->all();
                $err_msg_list = [];
                if(!empty($errList)){
                    foreach ($errList as $item) {// 无可发送的客户
                        // 处理原因：当一次需要发送的客户数很多时，需要处理成多次请求发送，最后一次请求时人数不足，
                        // 导致有可能这一批次所有人均无法正常发送，此种为正常情况，不予展示
                        if($datum->enable != -1 && $item['errcode'] == 41048) {
                            continue;
                        }

                        $err_msg = $errcodeConf[$item['errcode']] ?? null;

                        if(60111 == $item['errcode']) {//UserID不存在
                            // 当出现此种错误时，将客服名称一并拼接起来展示
                            $senderName = $senderInfoList->where('corpid', $item['corpid'])
                            ->where('user_id', $item['sender'])->first();
                            $senderName = $senderName->name ?? '';
                            if(1 == $datum->operate_type) {
                                $err_msg = $senderName.$err_msg;
                            } else {
                                $corpName = $corpInfoList->where('corpid', $item['corpid'])->first();
                                $corpName = $corpName->corp_name ?? '';
                                $err_msg = $corpName.$senderName.$err_msg;
                            }
                        }

                        $err_msg = $datum->enable != -1 ? '部分失败由于'.$err_msg : $err_msg;

                        if(!empty($err_msg) && !in_array($err_msg, $err_msg_list)) {
                            $err_msg_list[] = $err_msg;
                        }
                    }
                }

                $datum->err_msg = $err_msg_list;
            }
        } catch (\Exception $e) {
            Log::logError('获取群发列表过程发生异常', [
                'line' => $e->getLine(),
                'msg' => $e->getMessage(),
            ], 'MassMsgRuleList');

            $errno = 2105;

            return [[], 0];
        }

        return [$list, $count];
    }

    /*
     * 成员消息群发未发送提醒功能
     * @param $corpid
     * @param $ruleId
     * @return int
     */
    public static function noticeUser($corpid, $ruleId)
    {
        #获取规则信息
        $rule_info = MassMsg::query()->where("id",$ruleId)->first();
        if(empty($rule_info)) {
            return 1001;  //参数错误
        }
        #检查是否是待发送状态
        if(!in_array($rule_info->enable,[1,2])){
            return 2108;    //只有待发送和发送中的可以操作提醒
        }

        if(2 == $rule_info->operate_type) {
            return 0;
        }

        # 群发规则
        $toPublishedUserList = explode(',',$rule_info->senders);
        /***规则处于正在发送的状态，排除已发送过的成员名单***/
        if($rule_info->enable == 2){
            #已发送过的成员名单集合
            $sent_user_id_arr = MassMsgRecord::query()->where("rule_id",$ruleId)
                ->pluck("sender")->toArray();   //已发送过的成员列表
            foreach ($toPublishedUserList as $k=>$sender_user_id){
                if(in_array($sender_user_id,$sent_user_id_arr)){
                    unset($toPublishedUserList[$k]);
                }
            }
        }

        if(empty($toPublishedUserList)) return 2109;    //群发消息指定成员为空

        # 发送消息提醒
        $responseData = ApplicationMsgService::sendTextMsg($corpid, '请及时确认客户群发消息任务。', $toPublishedUserList);

        if(isset($responseData['errcode']) && $responseData['errcode'] != 0) {
            $logData = [
                'corpid' => $corpid,
                'rule_id' => $ruleId,
                'responseData' => $responseData
            ];
            EmailQueue::rPush('发送客户群发消息提醒失败', json_encode($logData, JSON_UNESCAPED_UNICODE), ['zhiyuan.chen@kuxuan-inc.com'], '发送客户群发消息提醒失败');
            Log::logError('发送客户群发消息提醒失败', $logData, 'noticeUserFailed');

            return 2512;
        }

        # 记录发送日志
        Log::logInfo('发送客户群发消息提醒成功', [
            'corpid' => $corpid,
            'rule_id' => $ruleId,
            'responseData' => $responseData
        ], 'noticeUserLog');

        return 0;

    }

    /*
     * 统计客户群发消息的数据概览
     * @param $corpid
     * @param $ruleId
     */
    public static function overview($corpid, $ruleId)
    {
        $send_record_total =  MassMsgRecord::query()
            ->where("rule_id",$ruleId)
//            ->selectRaw("count(CASE WHEN status = 1 THEN distinct(user_id) END) as un_sender_count")  // 未发送成员
//            ->selectRaw("count(CASE WHEN status = 2 THEN distinct(user_id) END) as sender_count")  // 已发送成员
            ->selectRaw("sum(send_success) as send_success")  // 发送成功
            ->selectRaw("sum(send_fail) as send_fail")  // 未送达人数
            ->first();

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

        $send_time = MassMsg::query()->where('id', $ruleId)->value('send_time');

        $senderList = MassMsgRecord::query()->where('rule_id', $ruleId)
            ->where('status', 2)->selectRaw('sender, corpid')->groupBy(['corpid', 'sender'])->get();
        $senderCount = count($senderList);
        $unSenderList = MassMsgRecord::query()->where('rule_id', $ruleId)
            ->where('status', 1)->selectRaw('sender, corpid')->groupBy(['corpid', 'sender'])->get();
        $unSenderCount = count($unSenderList);

        $ret_data = [];
        #已发送人员
        $ret_data['executed_sender_count'] = $senderCount;
        #发送用户
        $ret_data['receive_customer_count'] = $send_record_total->send_success;
        #未发送成员
        $ret_data['un_execute_sender_count'] = $unSenderCount;
        #未送达客户
        $ret_data['un_receive_customer_count'] = $send_record_total->send_fail ;
        #客户接收达上限
        $ret_data['receive_fail_with_limit'] = MassMsgSendDetailEs::massSendCustCount($ruleId,$send_time,3);
        #因为不是好友发送失败
        $ret_data['receive_fail_with_not_friend'] = MassMsgSendDetailEs::massSendCustCount($ruleId,$send_time,4);

        return $ret_data;
    }

    /*
     * 成员详情列表
     * @param $corpid
     * @param $ruleId
     * @param $search_type [选择查询的类型：all、sent、unsent、fail]
     * @param $keyword
     * @param $page
     * @param $page_size
     */
    public static function sender_list($corpid, $ruleId, $search_type, $keyword, $page, $page_size)
    {
        if(500 == $page_size) {
            return self::get_all_sender_list($ruleId);
        }

        /**已发送成员统计**/
        $send_record =  MassMsgRecord::where("rule_id",$ruleId)
                ->select(["corpid", "sender"])
                ->selectRaw("min(send_time) as send_time")  //发送时间
                ->selectRaw("sum(send_fail) as send_fail")  //失败次数
                ->selectRaw("sum(send_success) as send_success") //成功次数
                ->where('type', 1)
                ->groupBy(["corpid", "sender"])
                ->get();

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

        $all_senders_id_arr = array_map(function($item){
            return '("' . $item['corpid'] . '","' . $item['sender'] . '")';
        }, $all_senders_id_arr);

        if(empty($all_senders_id_arr)){
            return [0, []];
        }

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

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

        $corpInfoList = AuthorizeCorp::where('enable', 1)->get();

        foreach ($list as $k => $item) {
            #补充最早群发时间
            $sendInfo = $send_record->where('corpid', $item['corpid'])->where('sender', $item['user_id'])->first();
            $sendTime = $sendInfo->send_time ?? '';
            $list[$k]['send_time']  = $sendTime;
            #补充好友数量
            $list[$k]['customer_num'] = CustomerDetails::suffix($item['corpid'])->where('corpid', $item['corpid'])
            ->where('user_id', $item['user_id'])->where('enable', 1)->whereIn('loss_status', [0, 1])->count();
            #已送达人数
            $sendFail = $sendInfo->send_fail ?? 0;
            $list[$k]['send_fail'] = $sendFail;
            #未送达人数
            $sendSuccess = $sendInfo->send_success ?? 0;
            $list[$k]['send_success']  = $sendSuccess;
            $corpInfo = $corpInfoList->where('corpid', $item['corpid'])->first();
            $list[$k]['corp_name'] = $corpInfo->corp_name ?? '';
        }
        return [$total,$list];
    }

    public static function get_all_sender_list($ruleId) {
        $all_senders_id_arr = MassMsgRecord::query()->select(["corpid", "sender"])->where("rule_id",$ruleId)
            ->get()->toArray();

        $all_senders_id_arr = array_map(function($item){
            return '("' . $item['corpid'] . '","' . $item['sender'] . '")';
        }, $all_senders_id_arr);

        if(empty($all_senders_id_arr)){
            return [0, []];
        }

        /**查询发送成员**/
        $query = DjUser::whereRaw("(corpid, user_id) in (" . implode(',', $all_senders_id_arr) . ')');
        $total = $query->count();   //总计总数
        $list  = $query->select("user_id", "name", "avatar", "corpid")->get()->toArray();

        $corpInfoList = AuthorizeCorp::where('enable', 1)->get();
        foreach ($list as $k => $item) {
            $corpInfo = $corpInfoList->where('corpid', $item['corpid'])->first();
            $list[$k]['corp_name'] = $corpInfo->corp_name ?? '';
        }

        return [$total,$list];
    }

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

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

            $row_index = 1;
            $column_index = 1;

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

            /**列表内容**/
            foreach ($list as $row =>$item){
                $row_index++;
                $column_index=1;     //重置列索引

                $sheet->setCellValueByColumnAndRow($column_index++,$row_index,$item['corp_name']);
                $sheet->setCellValueByColumnAndRow($column_index++,$row_index,$item['name']);
                $sheet->setCellValueByColumnAndRow($column_index++,$row_index,$item['send_time']);
                $sheet->setCellValueByColumnAndRow($column_index++,$row_index,$item['customer_num']);
                $sheet->setCellValueByColumnAndRow($column_index++,$row_index,$item['send_success']);
                $sheet->setCellValueByColumnAndRow($column_index++,$row_index,$item['send_fail']);
            }

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


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

    /*
     * 群发详情-客户详情列表
     */
    public static function massSendCustList($rule_id, $sender, $name, $type, $page, $pagesize, $senderCorpid)
    {
        $send_time = MassMsg::where('id', $rule_id)->value('send_time');
        
        if(empty($send_time)){
            return [[], 0];
        }

        # 获取非重试情况下的msgId
        $msgIds = MassMsgRecord::where('rule_id', $rule_id)->where('type', 1)->where('msg_id', '>', '')->pluck('msg_id')->toArray();

        list($data, $count) = MassMsgSendDetailEs::massSendCustList($rule_id, $send_time, $sender, $name, $type, $page, $pagesize, $msgIds, $senderCorpid);
        $corpIdList = array_unique(array_column($data, 'corp_id'));
        $corpDataList = AuthorizeCorp::query()->whereIn('corpid', $corpIdList)->get();
        foreach ($data as $k => $item) {
            $corpName = $corpDataList->where('corpid', $item['corp_id'])->first();
            $data[$k]['corp_name'] = $corpName->corp_name ?? '';
        }

        return [$data, $count];
    }

    /**
    * 群发详情-客户详情列表
    **/
    public static function massSendCustListExport($rule_id, $sender, $name, $type, $senderCorpid)
    {
        try {
            $send_time = MassMsg::where('id', $rule_id)->value('send_time');
            if(empty($send_time)){
                return [[], 0];
            }
            $spreadsheet =  new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();

            $row_index = 1;
            $column_index = 1;

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

            $page = 1;
            $pagesize = 1000;
            # 获取非重试情况下的msgId
            $msgIds = MassMsgRecord::where('rule_id', $rule_id)->where('msg_id', '>', '')->where('type', 1)
                ->where(function($query) use($sender) {
                    if($sender) $query->where('sender', $sender);
                })
                ->pluck('msg_id')->toArray();
            while (1){
                list($list,$total) =  MassMsgSendDetailEs::massSendCustList($rule_id, $send_time, $sender, $name, $type
                    , $page++, $pagesize, $msgIds, $senderCorpid);
                $corpIdList = array_unique(array_column($list, 'corp_id'));
                $corpDataList = AuthorizeCorp::query()->whereIn('corpid', $corpIdList)->get();

                /**列表内容**/
                foreach ($list as $row =>$item){
                    $corpName = $corpDataList->where('corpid', $item['corp_id'])->first();
                    $corpName = $corpName->corp_name ?? '';

                    $row_index++;
                    $column_index=1;     //重置列索引
                    $sheet->setCellValueByColumnAndRow($column_index++,$row_index,$item['external_username']);
                    $sheet->setCellValueByColumnAndRow($column_index++,$row_index,$corpName);
                    $sheet->setCellValueByColumnAndRow($column_index++,$row_index,$item['sender_name']);
                    $sheet->setCellValueByColumnAndRow($column_index++,$row_index,$item['send_time']);
                    $sheet->setCellValueByColumnAndRow($column_index++,$row_index,$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) {
            $log_content = [
                'params' => " ruleId=$rule_id , sender=$sender , name=$name , type=$type",
                'msg' => $e->getMessage(),
                'line' => $e->getLine()
            ];

            Log::logError('导出客户群发详情出现错误', $log_content, 'MassSendCustomerListExport');
            return false;
        }
    }

    /*
     * 校验群发消息的附件信息合法性
     * */
    public static function attachmentsVerify($attachments, $corpid) {
        if(empty($attachments)) {
            return 0;
        }

        if(!is_array($attachments))
            $attachments = json_decode($attachments, true);

        foreach ($attachments as $attachment) {
            $msgType = $attachment['msgtype'] ?? '';
            switch($msgType) {
                case 'miniprogram':
                    # 检出小程序路径中的appId信息
                    $path = $attachment[$msgType]['page'] ?? '';
                    $pathData = parse_url($path);
                    if(isset($pathData['query']) && $pathData['query']) {
                        parse_str($pathData['query'], $query);
                        $appId = $query['wxAppId'] ?? '';
                        if(!empty($appId)) {    // 存在appId，校验与当前corpid是否关联
                            $isExist = TencentAdConf::where('app_id', $appId)->where('enable', 1)
                                ->where('corp_id', $corpid)->exists();
                            if(!$isExist) return 2110;
                        }
                    }
                    break;
            }
        }

        return 0;
    }

    public static function getCorpIdListBySenders($senders) {
        $corpIdList = array_column($senders, 'corpid');
        $corpIdList = array_unique($corpIdList);
        return $corpIdList;
    }

    public static function getLicenseExpirationUserList($params) {
        $corpid = $params['corpid'];
        # 查询企微名称
        $corpName = AuthorizeCorp::query()->where('corpid', $corpid)->where('enable', 1)->value('corp_name');

        if(!is_array($params['senders'])) {
            $senders = !empty($params['senders']) ? explode(',', $params['senders']) : [];
        } else {
            $senders = $params['senders'];
        }

        $userList = DjUser::query()->where('corpid', $corpid)->where(function($query) use ($senders){
            if(!empty($senders)) $query->whereIn('user_id', $senders);
        })->whereRaw('(is_active = 0 or expire_time < "'.date('Y-m-d 00:00:00').'")')
            ->where('enable', 1)->where('status', 1)->pluck('name')->toArray();

        $item = [
            'corpId'   => $corpid,
            'corpName' => $corpName,
            'senders'  => $userList
        ];
        return $item;
    }

    public static function getMultipleCorpLicenseExpirationUserList($params) {
        try {
            $data = $params;
            if(1 == $params['is_operation']) {
                $senders = self::getSenderListByOperatorGroupId($params['operator_group_id']);//从运营组中查询客服以及企微信息
            } else {
                $senders = json_decode($params['multiple_senders'], 1);
            }

            $res = [];
            foreach ($senders as $item) {
                $data['corpid'] = $item['corpid'];
                $data['senders'] = implode(',', $item['user_list']);
                $userInfo = self::getLicenseExpirationUserList($data);
                if(!empty($userInfo['senders'])) {
                    $res[] = $userInfo;
                }
            }

            return $res;
        } catch (\Exception $exception) {
            Log::logError('多企微获取群发条件匹配的外部联系人数过程发生异常', [
                'params' => $params,
                'line' => $exception->getLine(),
                'msg' => $exception->getMessage()
            ], 'GetCustomerTotal-Exception');

            EmailQueue::rPush('多企微获取群发条件匹配的外部联系人数过程发生异常', '', ['song.shen@kuxuan-inc.com'], '猎羽');
            return [];
        }
    }
}