<?php


namespace app\admin\service\consultant;


use app\model\Consultant;
use app\model\MarketCustomer;
use app\model\MarketCustomerFollow;
use app\model\MarketCustomerLogs;
use app\model\SysDept;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use support\Db;
use support\exception\BusinessException;
use support\Request;
use Tinywan\Jwt\JwtToken;

class CustomService
{
    const DIFF_TIME = (60 * 60 * 24 * 30); //调入团队公池 x4 = 调入集团公池
    /**
     * Notes: 返回选项配置信息
     * User: yb
     * Date: 2024/8/5
     * Time: 16:05
     * @return array
     */
    public static function config()
    {
        return MarketCustomer::config();
    }

    public static function commonSearch($params)
    {
        $where = [];
        $currentTime = time();
        $diffNums = self::DIFF_TIME;
        $deptIds = TeamService::getIdsByUser();
        $belongStatus = $params['belong_status'] ?? 1;
        if (false === $deptIds) {
            //无权限
            $where[] = ['dept_id', '=', 0];
        } else if (is_array($deptIds)) {
            //指定团队下的权限
            if (!empty($params['dept_id'])) {
                $deptId = end($params['dept_id']);
                $deptIds = SysDept::where('dept_super_path','like', "%/{$deptId}/%")->where('dept_category', '=', TeamService::DEPT_CATEGORY)->pluck('dept_id')->toArray();
            }
            $where[] = [function($query) use ($deptIds) {
                $query->whereIn('dept_id', $deptIds);
            }];
            $where[] = [function($query) use ($diffNums, $currentTime){
                $query->orWhereRaw("check_status IN (-1, 1)")
                    ->orWhereRaw("check_status = 2 AND current_status IN (-1,3,4)")
                    ->orWhereRaw("((visit_time + {$diffNums}) - {$currentTime} > 0 AND current_status = 2 AND check_status = 2)")
                    ->orWhereRaw("((visit_time + {$diffNums}) - {$currentTime} <= 0 AND (visit_time + ({$diffNums} * 4)) - {$currentTime} > 0 AND current_status = 2 AND check_status = 2 AND belong_status = 1)")->whereOr('belong_status','<>', 1);
            }];

        } else {
            if (!empty($params['dept_id'])) {
                $deptId = end($params['dept_id']);
                $deptIds = SysDept::where('dept_super_path','like', "%/{$deptId}/%")->where('dept_category', '=', TeamService::DEPT_CATEGORY)->pluck('dept_id')->toArray();
                $where[] = [function($query) use ($deptIds) {
                    $query->whereIn('dept_id', $deptIds);
                }];
            }
        }

        if (!empty($params['name'])) {
            $keywords = $params['name'];
            $where[] = [function($query) use ($keywords) {
                $query->orWhere('name', 'like', "%{$keywords}%")->orWhere('mobile', 'like', "%{$keywords}%");
            }];
        }
        if (!empty($params['mobile'])) {
            $mobile = $params['mobile'];
            $where[] = [function($query) use ($mobile) {
                $query->where('mobile', 'like', "%{$mobile}%");
            }];
        }
        if (!empty($params['consultant_name'])) {
            $consultantIds = Consultant::orWhere('name', 'like', "%{$params['consultant_name']}%")
                ->orWhere('mobile', 'like', "%{$params['consultant_name']}%")
                ->pluck('id')->toArray();
            $where[] = [function($query) use ($consultantIds) {
                $query->whereIn('consultant_id', $consultantIds);
            }];
        }
        if (!empty($params['report_name'])) {
            $consultantIds = Consultant::orWhere('name', 'like', "%{$params['report_name']}%")
                ->orWhere('mobile', 'like', "%{$params['report_name']}%")
                ->pluck('id')->toArray();
            $where[] = [function($query) use ($consultantIds) {
                $query->whereIn('report_consultant_id', $consultantIds);
            }];
        }
        if (!empty($params['type'])) {
            $where[] = ['type', '=', $params['type']];
        }
        if (!empty($params['visit_time'])) {
            $datetime = $params['visit_time'];
            $datetime[0] = strtotime($datetime[0].' 00:00:00');
            $datetime[1] = strtotime($datetime[1].' 23:59:59');
            $where[] = [function($query) use ($datetime) {
                $query->whereBetween('visit_time', $datetime);
            }];
        }
        if (!empty($params['visit_date'])) {
            $visitDate = $params['visit_date'];
            $visitDate[0] = strtotime($visitDate[0].' 00:00:00');
            $visitDate[1] = strtotime($visitDate[1].' 23:59:59');
            $where[] = [function($query) use ($visitDate) {
                $query->whereBetween('visit_time', $visitDate);
            }];
        }
        if (!empty($params['created_date'])) {
            $createdDate = $params['created_date'];
            $createdDate[0] = strtotime($createdDate[0].' 00:00:00');
            $createdDate[1] = strtotime($createdDate[1].' 23:59:59');
            $where[] = [function($query) use ($createdDate) {
                $query->whereBetween('created_at', $createdDate);
            }];
        }
        if (!empty($params['check_date'])) {
            $checkDate = $params['check_date'];
            $checkDate[0] = strtotime($checkDate[0].' 00:00:00');
            $checkDate[1] = strtotime($checkDate[1].' 23:59:59');
            $where[] = [function($query) use ($checkDate) {
                $query->whereBetween('check_time', $checkDate)->where('current_status', '>', 1);
            }];
        }

        if (!empty($params['current_status'])) {
            $currentStatus = $params['current_status'];
            if ($currentStatus == -2) {
                //检索公池状态
                $where[] = [function($query) use ($diffNums, $currentTime){
                    $query->whereRaw("((visit_time + {$diffNums}) - {$currentTime} <= 0 AND (visit_time + ({$diffNums} * 4)) - {$currentTime} > 0 AND current_status = 2 AND check_status = 2 AND belong_status = 1)");
                }];
            } else {
                $where[] = ['current_status', '=', $currentStatus];
                $where[] = [function($query) use ($diffNums, $currentTime){
                    $query->orWhereRaw("check_status IN (-1, 1)")
                        ->orWhereRaw("check_status = 2 AND current_status IN (-1,3,4)")
                        ->orWhereRaw("((visit_time + {$diffNums}) - {$currentTime} > 0 AND current_status = 2 AND check_status = 2)");
                }];
            }
        }
        if (!empty($params['consultant_id'])) {
            $consultantId = $params['consultant_id'];
            $where[] = [function($query) use ($consultantId) {
                $query->orWhere('consultant_id', $consultantId)->orWhere('report_consultant_id', $consultantId);
            }];
        }
        if (!empty($params['custom'])) {
            $keywords = $params['custom'];
            $where[] = [function($query) use ($keywords) {
                $query->orWhere('name', 'like', "%{$keywords}%")->orWhere('mobile', 'like', "%{$keywords}%");
            }];
        }
        if (!empty($params['check_status'])) {
            $where[] = ['check_status', '=', $params['check_status']];
        }
        if (!empty($params['stat_report_status'])) {
            //已报备 1
            $statReportStatus = $params['stat_report_status'];
            if ($statReportStatus == 1) {
                $where[] = [function($query) use ($diffNums, $currentTime) {
                    $query->whereRaw("((visit_time + {$diffNums}) - {$currentTime} > 0)")->where('current_status', '=', 1)->where('check_status', '=', 2);
                }];
            }
            //已锁定 2
            if ($statReportStatus == 2) {
                $where[] = [function($query) {
                    $query->whereIn('current_status', [2,3,4])->where('check_status', '=', 2);
                }];
            }
            //已失效 -1
            if ($statReportStatus == -1) {
                $where[] = [function($query) use ($diffNums, $currentTime){
                    $query->orWhere('current_status', '=', -1)->orWhereRaw("((visit_time + {$diffNums}) - {$currentTime} <= 0 AND current_status = 1 AND check_status = 2)")->orWhere('check_status', '=', -1);
                }];
            }
        }
        if (isset($params['require_area'])) {
            if (is_numeric($params['require_area'])) {
                $where[] = ['require_area', '=', ($params['require_area'] + 1)];
            }
        }
        if (isset($params['requirement'])) {
            if (is_numeric($params['requirement'])) {
                $where[] = ['requirement', '=', ($params['requirement'] + 1)];
            }
        }
        if (isset($params['age_range'])) {
            if (is_numeric($params['age_range'])) {
                $where[] = ['age_range', '=', ($params['age_range'] + 1)];
            }
        }
        if (isset($params['visit_type'])) {
            if (is_numeric($params['visit_type'])) {
                $where[] = ['visit_type', '=', $params['visit_type']];
            }
        }
        if (isset($params['region'])) {
            if (is_numeric($params['region'])) {
                $where[] = ['region', '=', ($params['region'] + 1)];
            }
        }
        if (isset($params['purpose'])) {
            if (is_numeric($params['purpose'])) {
                $where[] = ['purpose', '=', ($params['purpose'] + 1)];
            }
        }
        if (isset($params['level'])) {
            if (is_numeric($params['level'])) {
                $where[] = ['level', '=', ($params['level'] + 1)];
            }
        }
        if (!empty($params['belong_status'])) {
            $belongStatus = $params['belong_status'];
            if ($belongStatus == 1) {
                //未流入公池
                $where[] = ['belong_status', '=', 1];
                $where[] = [function($query) use ($diffNums, $currentTime){
                    $query->orWhereRaw("check_status IN (-1, 1)")
                        ->orWhereRaw("check_status = 2 AND current_status IN (-1,3,4)")
                        ->orWhereRaw("((visit_time + {$diffNums}) - {$currentTime} > 0 AND current_status = 2 AND check_status = 2)");
                }];
            } else {
                if (true === $deptIds) {
                    if (!empty($params['pub_status'])) {
                        if ($params['pub_status'] == 2) {
                            //仅检索团队公池
                            $where[] = [function($query) use ($diffNums, $currentTime){
                                $query->orWhereRaw("((visit_time + {$diffNums}) - {$currentTime} <= 0 AND (visit_time + ({$diffNums} * 4)) - {$currentTime} > 0 AND current_status = 2 AND check_status = 2 AND belong_status = 1)")->whereOr('belong_status','<>', 1);
                            }];
                        } else {
                            //仅检索集团公池
                            $where[] = [function($query) use ($diffNums, $currentTime){
                                $query->orWhereRaw("((visit_time + ({$diffNums} * 4)) - {$currentTime} <= 0 AND current_status = 2 AND check_status = 2 AND belong_status = 1)")->whereOr('belong_status','<>', 1);
                            }];
                        }
                    } else {
                        //超管权限
                        $where[] = [function($query) use ($diffNums, $currentTime){
                            $query->orWhereRaw("((visit_time + {$diffNums}) - {$currentTime} <= 0 AND current_status = 2 AND check_status = 2 AND belong_status = 1)")->whereOr('belong_status','<>', 1);
                        }];
                    }

                } else if (is_array($deptIds)) {
                    //团队权限
                    $where[] = [function($query) use ($diffNums, $currentTime){
                        $query->orWhereRaw("((visit_time + {$diffNums}) - {$currentTime} <= 0 AND (visit_time + ({$diffNums} * 4)) - {$currentTime} > 0 AND current_status = 2 AND check_status = 2 AND belong_status = 1)")->whereOr('belong_status','<>', 1);
                    }];
                } else {
                    //无权限
                }

            }
        }


        return $where;
    }

    /**
     * Notes: 列表
     * User: yb
     * Date: 2024/8/6
     * Time: 15:07
     * @param Request $request
     */
    public static function index(Request $request)
    {
        $format = $request->get('format', 'normal');
        $limit = (int)$request->get('pageSize', $format === 'tree' ? 1000 : 10);
        $limit = $limit <= 0 ? 10 : $limit;
        $params = $request->get();
        $page = (int)$request->get('page');
        $page = $page > 0 ? $page : 1;
        $currentTime = time();
        $diffNums = self::DIFF_TIME;
        $where = self::commonSearch($params);
        $paginator = MarketCustomer::where($where)->orderBy('visit_time', 'desc')->paginate($limit, '*', 'page', $page);
        $total = $paginator->total();
        $items = $paginator->items();
        if (!empty($items)) {
            //查询顾问信息
            $consultantKeys = [];
            $reportConsultantIds = $paginator->pluck('report_consultant_id')->toArray();
            $consultantIds = $paginator->pluck('consultant_id')->toArray();
            if (!empty($consultantIds) || !empty($reportConsultantIds)) {
                if (!empty($consultantIds)) {
                    //去重
                    $consultantIds = array_unique($consultantIds);
                    //排序
                    $consultantIds = array_values($consultantIds);
                }
                if (!empty($reportConsultantIds)) {
                    //去重
                    $reportConsultantIds = array_unique($reportConsultantIds);
                    //排序
                    $reportConsultantIds = array_values($reportConsultantIds);
                }
                $consultantIds = array_merge($consultantIds, $reportConsultantIds);

                $consultantList = Consultant::whereIn('id', $consultantIds)->select(['id', 'name', 'mobile'])->get();
                if (!$consultantList->isEmpty()) {
                    foreach ($consultantList->toArray() as $consultantItem) {
                        $consultantKeys[$consultantItem['id']] = $consultantItem;
                    }
                }
            }


            $teamKeys = TeamService::getItemKeys();
            foreach ($items as &$item) {
                $visitTime = $item->visit_time;
                $visitTimeStamp = strtotime($visitTime);
                $diff = ($visitTimeStamp + $diffNums) - $currentTime;
                if ($diff > 0) {
                    $item->belong_status = 1;
                } else {
                    if (abs($diff) - (4*$diffNums) > 0) {
                        $item->belong_status = 3;
                    } else {
                        $item->belong_status = 2;
                    }
                }
                $item->diff_nums =  ($diff > 0) ? $diff : 0;
                $item->consultant_name = $consultantKeys[$item->consultant_id]['name'] ?? '';
                $item->consultant_mobile = $consultantKeys[$item->consultant_id]['mobile'] ?? '';
                $item->report_consultant_name = $consultantKeys[$item->report_consultant_id]['name'] ?? '';
                $item->report_consultant_mobile = $consultantKeys[$item->report_consultant_id]['mobile'] ?? '';
                $item->team_name = TeamService::getTeamName($teamKeys, $item->dept_id);
                $item->mask_mobile = self::handlePhone($item->mobile);
            }
        }
        $data = [
            'total' => $total,
            'rows' => $items
        ];
        return json_success('success', $data);
    }

    /**
     * Notes: 添加客户
     * User: yb
     * Date: 2024/8/6
     * Time: 11:20
     */
    public static function add($params)
    {
        $params = MarketCustomer::handleNumParams($params);
        $mobile = $params['mobile'];
        //查询客户手机号是否已经存在
        if (MarketCustomer::checkCustomExists($mobile)) {
            return json_fail('客户已经登记过了');
        }
        //查询顾问信息
        $reportConsultantId = $params['report_consultant_id'];
        $consultantInfo = Consultant::firstWhere(['id' => $reportConsultantId]);
        if (empty($consultantInfo)) {
            return json_fail('报备顾问信息不存在');
        }
        $deptId = $consultantInfo->dept_id;
        $insertData = [
            'name' => $params['name'],
            'mobile' => $mobile,
            'consultant_id' => 0,
            'create_consultant_id' => $reportConsultantId,
            'report_consultant_id' => $reportConsultantId,
            'dept_id' => $deptId,
            'gender' => $params['gender'] ?? null,
            'visit_type' => $params['visit_type'] ?? null,
            'require_area' => $params['require_area'] ?? null,
            'area' => $params['area'] ?? null,
            'requirement' => $params['requirement'] ?? null,
            'age_range' => $params['age_range'] ?? null,
            'focus' => $params['focus'] ?? null,
            'region' => $params['region'] ?? null,
            'purpose' => $params['purpose'] ?? null,
            'level' => $params['level'] ?? null,
            'type' => $params['type'] ?? null,
            'check_status' => 1,
            'visit_time' => $params['visit_time'] ?? null,
            'note' => $params['note'] ?? '',
            'belong_status' => 1,
            'current_status' => $params['current_status'] ?? null,
            'created_at' => time()
        ];

        Db::beginTransaction();
        try {
            //查询会员表中是否存在该客户
//            $memberId = Member::where('member_mobile', $insertData['mobile'])->value('member_id');
//            if (empty($memberId)) {
//                $result = MemberService::add([
//                    'account_name'=> $insertData['name'],
//                    'member_category' => '售房客户',
//                    'mobile' => $insertData['mobile'],
//                    'source' => 'HOUSE']);
//                $code = $result->getStatusCode();
//                if ($code == 200) {
//                    $content = $result->rawBody();
//                    if (is_json($content)) {
//                        $content = json_decode($content, 1);
//                        if ($content['code'] == 200) {
//                            $memberId = $content['data']['member_id'];
//                        } else {
//                            throw new BusinessException('新增会员失败');
//                        }
//                    } else {
//                        throw new BusinessException('新增会员失败');
//                    }
//                } else {
//                    throw new BusinessException('新增会员失败');
//                }
//            }
//            $insertData['member_id'] = $memberId;
            $customId = MarketCustomer::insertGetId($insertData);
            Db::commit();
        }catch (BusinessException|\Exception $e){
            Db::rollBack();
            return json_fail($e->getMessage());
        }
        if ($customId) {
            return json_success('添加成功');
        } else {
            return json_fail('添加失败');
        }
    }

    /**
     * Notes: 编辑客户
     * User: yb
     * Date: 2024/8/6
     * Time: 16:53
     * @param $params
     */
    public static function update($params)
    {
        $params = MarketCustomer::handleNumParams($params);
        $mobile = $params['mobile'];
        //查询客户手机号是否已经存在
        if (MarketCustomer::checkCustomExists($mobile, $params['id'])) {
            return json_fail('客户已经登记过了');
        }
        $updateData = [
            'name' => $params['name'],
            'mobile' => $params['mobile'],
            'gender' => $params['gender'] ?? null,
            'visit_type' => $params['visit_type'] ?? null,
            'require_area' => $params['require_area'] ?? null,
            'area' => $params['area'] ?? null,
            'requirement' => $params['requirement'] ?? null,
            'age_range' => $params['age_range'] ?? null,
            'focus' => $params['focus'] ?? null,
            'region' => $params['region'] ?? null,
            'purpose' => $params['purpose'] ?? null,
            'level' => $params['level'] ?? null,
            'type' => $params['type'] ?? null,
            'note' => $params['note'] ?? '',
            'current_status' => $params['current_status'] ?? null,
            'updated_at' => time()
        ];
        if (!empty($params['update_visit_time'])) {
            if ($params['update_visit_time'] == 1) {
                $updateData['visit_time'] = $params['visit_time'] ?? null;
            }
        }
        $result = MarketCustomer::where('id', $params['id'])->update($updateData);
        if ($result) {
            return json_success('编辑成功');
        } else {
            return json_fail('编辑失败');
        }
    }

    /**
     * Notes: 删除客户
     * User: yb
     * Date: 2024/8/2
     * Time: 13:33
     * @param $ids
     * @return \support\Response
     */
    public static function delete($ids)
    {
        if (!$ids) {
            return json_fail("数据错误");
        }
        if (!is_array($ids)) {
            $ids = [$ids];
        }
        Db::beginTransaction();
        try {
            if (is_array($ids)) {
                MarketCustomer::whereIn('id', $ids)->delete();
                MarketCustomerFollow::whereIn('market_customer_id', $ids)->delete();//删除跟进记录
                MarketCustomerLogs::whereIn('market_customer_id', $ids)->delete();//删除移交记录
            } else {
                MarketCustomer::where('id', $ids)->delete();
                MarketCustomerFollow::where('market_customer_id', $ids)->delete();//删除跟进记录
                MarketCustomerLogs::where('market_customer_id', $ids)->delete();//删除移交记录
            }
            Db::commit();
        } catch (\Exception $e) {
            Db::rollBack();
            return json_fail('删除失败');
        }

        return json_success('删除成功');
    }

    /**
     * Notes: 跟进记录
     * User: yb
     * Date: 2024/8/7
     * Time: 9:00
     * @param Request $request
     */
    public static function follow(Request $request)
    {
        $format = $request->get('format', 'normal');
        $limit = (int)$request->get('pageSize', $format === 'tree' ? 1000 : 10);
        $limit = $limit <= 0 ? 10 : $limit;
        $params = $request->get();
        $page = (int)$request->get('page');
        $page = $page > 0 ? $page : 1;
        $where = [];
        $whereCustom = [];
        $whereConsultant = [];
        if (!empty($params['market_customer_id'])) {
            $marketCustomerIds = $params['market_customer_id'];
            $where[] = [function($query) use ($marketCustomerIds) {
                $query->whereIn('market_customer_id', $marketCustomerIds);
            }];
        }
        if (!empty($params['consultant_id'])) {
            $consultantId = $params['consultant_id'];
            $where[] = [function($query) use ($consultantId) {
                $query->whereIn('consultant_id', $consultantId);
            }];
        }
        if (!empty($params['follow_way'])) {
            $where[] = ['follow_way', '=', $params['follow_way']];
        }
        if (!empty($params['custom'])) {
            $custom = $params['custom'];
            $whereCustom[] = [function($query) use ($custom) {
                $query->orWhere('name', 'like', "%{$custom}%")->orWhere('mobile', 'like', "%{$custom}%");
            }];
        }
        if (!empty($params['consultant'])) {
            $consultant = $params['consultant'];
            $whereConsultant[] = [function($query) use ($consultant) {
                $query->orWhere('name', 'like', "%{$consultant}%")->orWhere('mobile', 'like', "%{$consultant}%");
            }];
        }
        if (!empty($params['follow_time'])) {
            $datetime = $params['follow_time'];
            $datetime[0] = strtotime($datetime[0].' 00:00:00');
            $datetime[1] = strtotime($datetime[1].' 23:59:59');
            $where[] = [function($query) use ($datetime) {
                $query->whereBetween('follow_time', $datetime);
            }];
        }
        $paginator = MarketCustomerFollow::with(['custom', 'consultant'])->whereHas('custom', function($query) use ($whereCustom){
            $query->where($whereCustom);
        })->whereHas('consultant', function($query) use ($whereConsultant) {
            $query->where($whereConsultant);
        })->where($where)->orderBy('follow_time', 'desc')->paginate($limit, '*', 'page', $page);
        $total = $paginator->total();
        $items = $paginator->items();
        foreach ($items as &$item) {
            if (!empty($item->custom)) {
                $item->custom->mask_mobile = self::handlePhone($item->custom->mobile);
            }
        }
        $data = [
            'total' => $total,
            'rows' => $items
        ];
        return json_success('success', $data);
    }

    /**
     * Notes: 删除跟进记录
     * User: yb
     * Date: 2024/8/7
     * Time: 11:25
     * @param $ids
     */
    public static function deleteFollow($ids)
    {
        if (!$ids) {
            return json_fail("数据错误");
        }
        if (!is_array($ids)) {
            $ids = [$ids];
        }
        try {
            if (is_array($ids)) {
                MarketCustomerFollow::whereIn('id', $ids)->delete();
            } else {
                MarketCustomerFollow::where('id', $ids)->delete();
            }
        } catch (\Exception $e) {
            return json_fail('删除失败');
        }

        return json_success('删除成功');
    }

    /**
     * Notes: 转移客户
     * User: yb
     * Date: 2024/8/7
     * Time: 14:27
     * @param $params
     */
    public static function moveCustom($params)
    {
        $userId = JwtToken::getCurrentId();
        $moveType = $params['move_type'] ?? 0;
        if ($moveType == 1) {
            $currentDeptId = $params['dept_id'];
            $currentConsultantId = 0;
        } else {
            $currentConsultantId = $params['consultant_id'] ?? 0; //当前顾问
            $consultantInfo = Consultant::firstWhere(['id' => $currentConsultantId]);
            if (empty($consultantInfo)) {
                return json_fail('顾问不存在');
            }
            $currentDeptId = $consultantInfo->dept_id; //当前部门
        }
        $consultantIds = $params['move_consultant_id'] ?? [];
        $customIds = $params['move_market_customer_id'] ?? [];
        $note = $params['note'] ?? '';
        $logsInertData  = [];
        $now = time();
        $whereCustom = [];
        if (!empty($customIds)) {
            $whereCustom[] = [function($query) use ($customIds) {
                $query->whereIn('id', $customIds);
            }];
        }
        if (!empty($consultantIds)) {
            $whereCustom[] = [function($query) use ($consultantIds) {
                $query->whereIn('consultant_id', $consultantIds);
            }];
        }
        $customList = MarketCustomer::where($whereCustom)->select(['id','consultant_id','dept_id','visit_time','check_status','current_status'])->get();
        if (!$customList->isEmpty()) {
            foreach ($customList as$item) {
                $logsInertData[] = [
                    'market_customer_id' => $item->id,
                    'consultant_id' => $currentConsultantId,
                    'dept_id' => $currentDeptId,
                    'before_consultant_id' => $item->consultant_id,
                    'before_dept_id' => $item->dept_id,
                    'note' => $note,
                    'change_user_id' => $userId,
                    'created_at' => $now
                ];
            }
        }
        if ($customList->isEmpty()) {
            return json_fail('移交的客户信息不存在');
        }
        if (empty($logsInertData)) {
            return json_fail('移交记录不能为空');
        }
        $diffTime = self::DIFF_TIME;
        Db::beginTransaction();
        $result = false;
        try {
            foreach ($customList as $item) {
                if ($currentConsultantId != 0) {
                    //移交至指定顾问
                    //判断一下是否到了到访的过期时间，如果过期了就更新到访时间，没过期时间就不变
                    if ($item->check_status == 2 && $item->current_status == 2) {
                        $visitTime = $item->visit_time;
                        $diff = strtotime($visitTime) + $diffTime - time();
                        if ($diff <= 0) {
                            $item->visit_time = time();
                        }
                    }
                } else {
                    //移交至其他团队
                    $item->visit_time = time() - self::DIFF_TIME;
                }
                $item->consultant_id = $currentConsultantId;
                $item->dept_id = $currentDeptId;
                $item->save();
            }
            $result = MarketCustomerLogs::insert($logsInertData);
            Db::commit();
        }catch (BusinessException|\Exception $e) {
            Db::rollBack();
            return json_fail($e->getMessage());
        }
        if ($result) {
            return json_success('移交成功');
        } else {
            return json_fail('移交失败');
        }

    }

    /**
     * Notes: 转移记录
     * User: yb
     * Date: 2024/8/7
     * Time: 15:33
     * @param Request $request
     */
    public static function moveLogs(Request $request)
    {
        $format = $request->get('format', 'normal');
        $limit = (int)$request->get('pageSize', $format === 'tree' ? 1000 : 10);
        $limit = $limit <= 0 ? 10 : $limit;
        $params = $request->get();
        $page = (int)$request->get('page');
        $page = $page > 0 ? $page : 1;
        $platform = 0;
        $where = [];
        $whereCustom = [];
        $whereCurrent = [];
        $whereBefore = [];
        $whereOpBack = [];
        $whereOpFront = [];
        if (!empty($params['market_customer_id'])) {
            $where[] = ['market_customer_id', '=', $params['market_customer_id']];
        }
        if (!empty($params['consultant_id'])) {
            $consultantIds = $params['consultant_id'];
            $where[] = [function($query) use ($consultantIds) {
                $query->orWhereIn('consultant_id', $consultantIds)->orWhereIn('before_consultant_id', $consultantIds);
            }];
        }
        if (!empty($params['custom'])) {
            //客户信息
            $custom = $params['custom'];
            $whereCustom[] = ['name', 'like', "%{$custom}%"];
        }
        if (!empty($params['current_consultant'])) {
            //当前员工信息
            $current = $params['current_consultant'];
            $whereCurrent[] = ['name', 'like', "%{$current}%"];
        }
        if (!empty($params['before_consultant'])) {
            //转移前员工信息
            $before = $params['before_consultant'];
            $whereBefore[] = ['name', 'like', "%{$before}%"];
        }
        if (!empty($params['platform'])) {
            $platform = $params['platform'];
            if ($platform == 1) {
                //后台
                $where[] = ['change_user_id', '<>', null];
                $where[] = ['change_user_id', '=', null];
            } else {
                //小程序
                $where[] = ['change_user_id', '=', null];
                $where[] = ['change_user_id', '<>', null];
            }
        }
        if (!empty($params['op_name'])) {
            $opName = $params['op_name'];
            if (!empty($platform)) {
                if ($platform == 1) {
                    $whereOpBack[] = ['user_name', 'like', "%{$opName}%"];
                } else {
                    $whereOpFront[] = ['name', 'like', "%{$opName}%"];
                }

            } else {
                $whereOpBack[] = [function($query) use ($opName) {
                    $query->orWhere('user_name', 'like', "%{$opName}%");
                }];
                $whereOpFront[] = [function($query) use ($opName) {
                    $query->orWhere('name', 'like', "%{$opName}%");
                }];
            }
        }
        if (!empty($params['created_at'])) {
            $createdDate = $params['created_at'];
            $createdDate[0] = strtotime($createdDate[0].' 00:00:00');
            $createdDate[1] = strtotime($createdDate[1].' 23:59:59');
            $where[] = [function($query) use ($createdDate) {
                $query->whereBetween('created_at', $createdDate);
            }];
        }
        $paginator = MarketCustomerLogs::with(['custom' => function($query) {
            $query->select('id', 'name', 'mobile');
        }, 'beforeMan' => function($query) {
            $query->select('id', 'name', 'mobile');
        }, 'currentMan' => function($query) {
            $query->select('id', 'name', 'mobile');
        }, 'beforeDept' => function($query) {
            $query->select('dept_id', 'dept_name');
        }, 'currentDept' => function($query) {
            $query->select('dept_id', 'dept_name');
        }])
//            ->whereHas('custom', function ($query) use ($whereCustom) {
//                $query->where($whereCustom);
//            })
//            ->whereHas('currentMan', function ($query) use ($whereCurrent) {
//                $query->where($whereCurrent);
//            })
//            ->whereHas('beforeMan', function ($query) use ($whereBefore) {
//                $query->where($whereBefore);
//            })
            ->where($where)
            ->orderBy('created_at', 'desc')
            ->paginate($limit, '*', 'page', $page);
        $total = $paginator->total();
        $items = $paginator->items();
        foreach ($items as &$item) {
            if (!empty($item->custom)) {
                $item->custom->mask_mobile = self::handlePhone($item->custom->mobile);
            }
        }
        $data = [
            'total' => $total,
            'rows' => $items
        ];
        return json_success('success', $data);
    }

    /**
     * Notes: 审核客户
     * User: yb
     * Date: 2024/8/16
     * Time: 11:21
     * @param $params
     */
    public static function checkCustom($params)
    {
        $adminId = JwtToken::getCurrentId();
        //查询客户是否已经存在
        $customId = $params['id'];
        $checkStatus = $params['check_status'];
        if (!in_array($checkStatus, [-1,2])) {
            return json_fail('状态值非法');
        }
        $info = MarketCustomer::firstWhere(['id' => $customId]);
        if (empty($info)) {
            return json_fail('客户不存在');
        }
        $status = $info->check_status;
        if ($status != 1) {
            return json_fail('客户不是待转到访状态');
        }
        $mobile = $info->mobile;
        if ($checkStatus == 2) {
            if (MarketCustomer::checkCustomExists($mobile)) {
                return json_fail('客户已经存在');
            }
        }
        //查询顾问的团队信息
        $deptId = Consultant::where('id', $params['consultant_id'])->value('dept_id');
        $result = false;
        Db::beginTransaction();
        try {
            $info->check_status = $checkStatus;
            $info->current_status = $checkStatus;
            if ($checkStatus == -1) {
                //拒绝录入拒绝理由
                $info->check_note = $params['note'] ?? '无拒绝理由';
            }
            if ($checkStatus == 2) {
                //更新到访保护期时间
                $info->visit_time = time();
            }
            $info->dept_id = $deptId ?? 0;
            $info->consultant_id = $params['consultant_id'];
            $info->check_time = time();
            $info->check_admin_id = $adminId;
            $result = $info->save();
            if ($checkStatus == 2) {
                //将其他待审的相同手机号的改拒绝
                $where = [
                    ['mobile' , '=', $mobile],
                    ['check_status', '=', 1],
                    ['id', '<>', $customId]
                ];
                MarketCustomer::where($where)->update(['check_time' => time(),'check_status' => -1, 'current_status' => -1,'check_note' => '客户已经被其他登记']);
            }
            Db::commit();
        }catch (BusinessException|\Exception $e){
            Db::rollBack();
            return json_fail($e->getMessage());
        }
        if ($result) {
            return json_success('操作成功');
        } else {
            return json_fail('操作失败');
        }


    }

    /**
     * Notes: 数据统计分析
     * User: yb
     * Date: 2024/8/17
     * Time: 11:27
     * @param $params
     */
    public static function statisticsIndex(Request $request)
    {
        $params = $request->get();
        $where = self::commonSearch($params);
        //客户总数
        $total = MarketCustomer::where($where)->count();
        //公池客户
        $pubTotal = MarketCustomer::where($where)->where([[function($query){
            $diffNums = self::DIFF_TIME;
            $query->orWhereRaw("((visit_time + {$diffNums}) - UNIX_TIMESTAMP() <= 0 AND current_status = 2 AND check_status = 2 AND belong_status = 1)")->whereOr('belong_status','<>', 1);
        }]])->count();
        //已报备
        $checkTotal = MarketCustomer::where($where)->where('check_status', 1)->count();
        //已到访
        $visitTotal = MarketCustomer::where($where)->where(
            [[function($query) {
                $diffNums = self::DIFF_TIME;
                $query->whereRaw("((visit_time + {$diffNums}) - UNIX_TIMESTAMP() > 0) AND current_status = 2 AND check_status = 2")->where('belong_status', 1);
            }]]
        )->count();
        //已来电
        $mobileTotal = MarketCustomer::where($where)->where([[function($query) {
            $query->where('check_status', '=', 2)->where('current_status', '=', 1);
        }]])->count();
        //已到访
//        $visitTotal = MarketCustomer::where($where)->where([[function($query) {
//            $query->where('check_status', '=', 2)->where('current_status', '=', 2);
//        }]])->count();
        //已缴费
        $payTotal = MarketCustomer::where($where)->where([[function($query) {
            $query->where('check_status', '=', 2)->where('current_status', '=', 3)->where('belong_status', 1);
        }]])->count();
        //已成交
        $dealTotal = MarketCustomer::where($where)->where([[function($query) {
            $query->where('check_status', '=', 2)->where('current_status', '=', 4)->where('belong_status', 1);
        }]])->count();
        //已缴费 + 已成交 / 客户总数
        //报备失败
        $failTotal = MarketCustomer::where($where)->where('current_status', -1)->count();
        $rant = 0;
        if ($total > 0) {
            $rant = ($payTotal + $dealTotal) / $total;
        }
        $rant = sprintf('%.2f', $rant);
        $data = [
            'total' => $total,
            'pub_total' => $pubTotal,
            'check_total' => $checkTotal,
            'report_total' => 0,
            'mobile_total' => $mobileTotal,
            'visit_total' => $visitTotal,
            'pay_total' => $payTotal,
            'deal_total' => $dealTotal,
            'fail_total' => $failTotal,
            'rant' => $rant,
        ];
        return json_success('请求成功', $data);

    }

    /**
     * Notes: 前12个月统计数据
     * User: yb
     * Date: 2024/8/21
     * Time: 15:56
     */
    public static function statisticsMonth()
    {
        $months = month_12();
        sort($months);
        $startDate = $months[0];
        $startDateTime = strtotime($startDate.'/01 00:00:00');
        $endDateTime = time();
        $where = [
            ['created_at', '>=', $startDateTime],
            ['created_at', '<=', $endDateTime]
        ];
        $currentTime = time();
        $diffNums = self::DIFF_TIME;
        $deptIds = TeamService::getIdsByUser();
        if (false === $deptIds) {
            //无权限
            $where[] = ['dept_id', '=', 0];
        } else if (is_array($deptIds)) {
            //指定团队下的权限
            if (!empty($params['dept_id'])) {
                $deptId = end($params['dept_id']);
                $deptIds = SysDept::where('dept_super_path','like', "%/{$deptId}/%")->where('dept_category', '=', TeamService::DEPT_CATEGORY)->pluck('dept_id')->toArray();
            }
            $where[] = [function($query) use ($deptIds) {
                $query->whereIn('dept_id', $deptIds);
            }];
        } else {
            if (!empty($params['dept_id'])) {
                $deptId = end($params['dept_id']);
                $deptIds = SysDept::where('dept_super_path','like', "%/{$deptId}/%")->where('dept_category', '=', TeamService::DEPT_CATEGORY)->pluck('dept_id')->toArray();
                $where[] = [function($query) use ($deptIds) {
                    $query->whereIn('dept_id', $deptIds);
                }];
            }
        }
        $selectRaw = Db::raw("DATE_FORMAT(FROM_UNIXTIME(created_at), '%Y/%m') as date,count(*) as nums");
        $groupRaw = Db::raw("DATE_FORMAT(FROM_UNIXTIME(created_at), '%Y/%m')");
        $totalData = MarketCustomer::where($where)->selectRaw($selectRaw)->groupByRaw($groupRaw)->get();
        $payData = MarketCustomer::where($where)->where([['current_status', '=', 3], ['check_status', '=', 2]])->selectRaw($selectRaw)->groupByRaw($groupRaw)->get();
        $visitData = MarketCustomer::where($where)->where([['current_status', '=', 2], ['check_status', '=', 2]])->selectRaw($selectRaw)->groupByRaw($groupRaw)->get();
        $dealData = MarketCustomer::where($where)->where([['current_status', '=', 4], ['check_status', '=', 2]])->selectRaw($selectRaw)->groupByRaw($groupRaw)->get();
        $returnData = [
            'total' => $totalData->pluck('nums','date'),
            'pay' => $payData->pluck('nums','date'),
            'visit' => $visitData->pluck('nums','date'),
            'deal' => $dealData->pluck('nums','date'),
            'date' => $months
        ];
        return json_success('请求成功', $returnData);

    }

    /**
     * Notes: 导出数据
     * User: yb
     * Date: 2024/8/17
     * Time: 13:01
     */
    public static function exportData(Request $request)
    {
        $currentTime = time();
        $diffNums = self::DIFF_TIME;
        $params = $request->get();
        $where = self::commonSearch($params);
        if (!empty($params['custom_ids'])) {
            $customIds = $params['custom_ids'];
            $where[] = [function($query) use ($customIds){
                $query->whereIn('id', $customIds);
            }];
        }
        $list = MarketCustomer::where($where)->orderBy('visit_time', 'desc')->get();
        if (!$list->isEmpty()) {
            $consultantKeys = [];
            //查询顾问信息
            $consultantList = Consultant::select(['id', 'name', 'mobile'])->get();
            if (!$consultantList->isEmpty()) {
                foreach ($consultantList->toArray() as $consultantItem) {
                    $consultantKeys[$consultantItem['id']] = $consultantItem;
                }
            }
            $teamKeys = TeamService::getItemKeys();
            $returnData = [];
            $genderData = ['', '男', '女'];
            $typeData = ['', '来电', '来访'];
            $currentStatusData = [-1 => '报备失败', 1 => '已报备', 2 => '已到访', 3 => '已缴费', 4 => '已成交'];
            $textConfig = self::config();
            //合并数组并去重
            $type1 = $textConfig['visit_type']['type1'] ?? [];
            $type2 = $textConfig['visit_type']['type2'] ?? [];
            $textTypeKeys = [];
            foreach ($type1 as $k1 => $v1) {
                $textTypeKeys[$k1] = $v1;
            }
            foreach ($type2 as $k2 => $v2) {
                $textTypeKeys[$k2] = $v2;
            }
            foreach ($list as &$item) {
                $visitTime = $item->visit_time;
                $visitTimeStamp = strtotime($visitTime);
                $diff = ($visitTimeStamp + $diffNums) - $currentTime;
                $currentStatus = $item->current_status;//当前状态
                $checkStatus = $item->check_status;//审核状态
                if ($diff <= 0 && $checkStatus == 2 && $currentStatus > 1) {
                    $currentStatusText = '公池';
                } else {
                    $currentStatusText = $currentStatusData[$item->current_status] ?? '';
                }
                $ageRangeText = $item->age_range > 0 ? $textConfig['age_range'][$item->age_range - 1] ?? '' : '';
                $levelText = $item->level > 0 ? $textConfig['level'][$item->level - 1] ?? '' : '';
                $regionText = $item->region > 0 ? $textConfig['region'][$item->region - 1] ?? '' : '';
                $visitTypeText = $textTypeKeys[$item->visit_type] ?? '';

                $returnData[] = [
                    'name' => $item->name, //客户姓名
                    'mobile' => $item->mobile, //客户电话
                    'mask_mobile' => self::handlePhone($item->mobile),
                    'gender' => $genderData[$item->gender] ?? '', //客户性别
                    "report_consultant_name" => $consultantKeys[$item->report_consultant_id]['name'] ?? '', //报备人
                    'consultant_name' => $consultantKeys[$item->consultant_id]['name'] ?? '', //健康顾问
                    'team_name' => TeamService::getTeamName($teamKeys, $item->dept_id), //所属团队
                    'current_status' => $currentStatusText, //当前状态
                    'visit_time' => $item->visit_time, //到访时间
                    "age_range" => $ageRangeText, //年龄区间
                    "level" => $levelText, //客户等级
                    "region" => $regionText, //所在区域
                    "visit_type" => $visitTypeText, //来源
                    'created_at' => date('Y-m-d H:i:s', strtotime($item->created_at)) //报备时间
                ];
            }
            return json_success('请求成功', $returnData);
        }

    }
    /**
     * Notes: 对手机号加密处理
     * User: yb
     * Date: 2024/8/8
     * Time: 15:41
     * @param $val
     * @return string|string[]
     */
    public static function handlePhone($val)
    {
        return substr_replace($val, '****', 3, 4);
    }

    /**
     * Notes: 指定顾问
     * User: yb
     * Date: 2024/9/5
     * Time: 12:52
     * @param $params
     */
    public static function appoint($params)
    {
        if (empty($params['id'])) {
            return json_fail('请选择客户');
        }
        if (empty($params['report_consultant_id'])) {
            return json_fail('请选择指定顾问');
        }
        $userId = JwtToken::getCurrentId();
        $relationUserId = $userId;
        $customId = $params['id'];
        $reportConsultantId = $params['report_consultant_id'];
        $customInfo = MarketCustomer::firstWhere(['id' => $customId]);
        if (empty($customInfo)) {
            return json_fail('客户信息不存在');
        }
        $consultantInfo = Consultant::firstWhere(['id' => $reportConsultantId]);
        if (empty($consultantInfo)) {
            return json_fail('顾问信息不存在');
        }
        $currentDeptId = $consultantInfo->dept_id; //当前部门
        $now = time();
        $logData = [
            'market_customer_id' => $customId,
            'consultant_id' => $reportConsultantId,
            'dept_id' => $currentDeptId,
            'before_consultant_id' => $customInfo->report_consultant_id,
            'before_dept_id' => $customInfo->dept_id,
            'note' => $params['note'] ?? '判客指定报备顾问',
            'change_user_id' => $relationUserId,
            'change_consultant_id' => 0,
            'created_at' => $now
        ];
        Db::beginTransaction();
        $result = false;
        try {
            $customInfo->report_consultant_id = $reportConsultantId;
            $customInfo->dept_id = $currentDeptId;
            $result = $customInfo->save();
            MarketCustomerLogs::insert($logData);
            Db::commit();
        }catch (BusinessException|\Exception $e) {
            Db::rollBack();
            return json_fail($e->getMessage());
        }
        if ($result) {
            return json_success('指定成功');
        } else {
            return json_fail('指定失败');
        }


    }

    /**
     * Notes: 导入客户
     * User: yb
     * Date: 2024/9/10
     * Time: 10:30
     * @param $params
     */
    public static function import($params)
    {
        if (empty($params['path'])) {
            return json_fail('文件地址不能为空');
        }
        $path = 'public'.$params['path'];
        if (is_file($path)) {
            $teamKeys = [];
            //查询顾问信息
            $teamData = Db::table('consultant')
                ->leftJoin('sys_dept', 'consultant.dept_id', '=', 'sys_dept.dept_id')
                ->select(['consultant.id', 'consultant.name', 'sys_dept.dept_name', 'sys_dept.dept_id'])
                ->whereNull('consultant.deleted_at')
                ->get();
            if (!($teamData->isEmpty())) {
                foreach ($teamData as $item) {
                    $key = $item->name.'-'.$item->dept_name;
                    $teamKeys[$key] = [
                        'consultant_id' => $item->id,
                        'dept_id' => $item->dept_id,
                        'name' => $item->name,
                        'dept_name' => $item->dept_name
                    ];
                }
            }
            $data = self::readExcel($path);
//            $fields = ['到访时间', '客户姓名', '客户手机号', 'A类', 'B类', 'C类', 'D类', '健康顾问', '报备人', '团队'];
            if (empty($data)) {
                return json_fail('无可导入内容');
            } else {
                $sucNums = 0;
                $errNums = 0;
                $validate = '/^1\d{10}$/';
                $errorData = [];
                Db::beginTransaction();
                try {
                    foreach ($data as $key => $val) {
                        if ($val[3] == 1) {$level = 1;} else if ($val[4] == 1) {$level = 2;} else if ($val[5] == 1) {$level = 3;} else if ($val[6] == 1) {$level = 4;}
                        $line = '姓名：'.$val[1].' 第'.($key + 2).'行：';
                        $mobile = $val[2] ?? '';
                        $mobile = trim($mobile);
                        $error = [];
                        $msg = '';
                        if (empty($val[7]) && empty($val[8])) {
                            $msg = $line.'报备人和顾问必须存在其中之一';
                            $val[10] = '报备人和顾问必须存在其中之一';
//                            throw new \Exception($line.'报备人和顾问必须存在其中之一');
                        }
                        if (empty($val[9])) {
                            $msg = $line.'无团队信息';
                            $val[10] = '无团队信息';
//                            throw new \Exception($line.'无团队信息');
                        }
                        if ($val[0] == '1970-01-01 08:00:00') {
                            $msg = $line.'日期格式错误';
                            $val[10] = '日期格式错误';
//                            throw new \Exception($line.'日期格式错误');
                        }
                        if (empty($mobile)) {
                            $msg = $line.'手机号不能为空';
                            $val[10] = '手机号不能为空';
//                            throw new \Exception($line.'手机号不能为空');
                        }
                        if (!preg_match($validate, $mobile)) {
                            $msg = $line.'手机号格式非法';
                            $val[10] = '手机号格式非法';
//                            throw new \Exception($line.'手机号格式非法');
                        }
                        $consultantName = $val[7];
                        $reportName = $val[8];
                        $teamName = $val[9];
                        $consultantKey = $consultantName.'-'.$teamName;
                        $consultantId = $teamKeys[$consultantKey]['consultant_id'] ?? 0;
                        $reportKey = $reportName.'-'.$teamName;
                        $reportConsultantId = $teamKeys[$reportKey]['consultant_id'] ?? 0;
                        if ($consultantId <= 0 && $reportConsultantId <= 0) {
                            $msg = $line.'健康顾问或报备人都不存在';
                            $val[10] = '健康顾问或报备人都不存在';
//                            throw new \Exception($line.'健康顾问或报备人都不存在');
                        }
                        if ($consultantId > 0) {
                            $deptId = $teamKeys[$consultantKey]['dept_id'] ?? 0;
                        }
                        if ($reportConsultantId > 0) {
                            $deptId = $teamKeys[$reportKey]['dept_id'] ?? 0;
                        } else {
                            if ($consultantId > 0) {
                                $reportConsultantId = $consultantId;
                            }
                        }
                        if (empty($deptId)) {
                            $msg =  $line.'健康顾问或报备人都不存在';
                            $val[10] = '健康顾问或报备人都不存在';
//                            throw new \Exception($line.'团队不存在');
                        }
                        if (MarketCustomer::checkCustomExists($val[2])) {
                            $msg =  $line.'客户已转到访，无法二次录入';
                            $val[10] = '客户已转到访，无法二次录入';
//                            throw new \Exception($line.'客户已转到访，无法二次录入');
                        }

                        $result = false;
                        if (empty($msg)) {
                            //查看手机号是否存在
                            $insertData = [
                                'name' => $val[1],
                                'mobile' => $mobile,
                                'level' => $level ?? null,
                                'consultant_id' => $consultantId,
                                'report_consultant_id' => $reportConsultantId,
                                'create_consultant_id' => $reportConsultantId,
                                'dept_id' => $deptId ?? 0,
                                'belong_status' => 1,
                                'current_status' => 1,
                                'check_status' => 1,
                                'check_note' => '',
                                'type' => 1,
                                'visit_time' => strtotime($val[0]),
                                'created_at' => strtotime($val[0])
                            ];
                            $result = Db::table('market_customer')->insert($insertData);
                        } else {
                            $val[11] = $line;
                            $errorData[] = $val;
                        }
                        if ($result) {
                            $sucNums++;
                        } else {
                            $errNums++;
                        }
                    }
                    Db::commit();
                }catch (\Exception $e) {
                    Db::rollBack();
                    return json_fail($e->getMessage());
                }

            }
        } else {
            return json_fail('文件不存在');
        }
        $total = count($data);
        return json_success("全部数据：{$total}，成功：{$sucNums}，失败：{$errNums}", ['data' => $errorData]);
    }

    /**
     * Notes: 读取excel文件
     * User: yb
     * Date: 2024/9/10
     * Time: 10:41
     * @param $filePath
     */
    public static function readExcel($filePath)
    {
        set_time_limit(0);
        ini_set('memory_limit', '1024M');
        $ext = pathinfo($filePath, PATHINFO_EXTENSION);
        if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
            return json_fail('未知的导入文件类型');
        }
        if ($ext === 'csv') {
            $file = fopen($filePath, 'r');
            $filePath = tempnam(sys_get_temp_dir(), 'import_csv');
            $fp = fopen($filePath, "w");
            $n = 0;
            while ($line = fgets($file)) {
                $line = rtrim($line, "\n\r\0");
                $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
                if ($encoding != 'utf-8') {
                    $line = mb_convert_encoding($line, 'utf-8', $encoding);
                }
                if ($n == 0 || preg_match('/^".*"$/', $line)) {
                    fwrite($fp, $line . "\n");
                } else {
                    fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
                }
                $n++;
            }
            fclose($file) || fclose($fp);
            $reader = new Csv();
        } else if ($ext === 'xls') {
            $reader = new Xls();
        } else {
            $reader = new Xlsx();
        }
        try {
            if (!$PHPExcel = $reader->load($filePath)) {
                return json_fail('未知的导入文件类型');
            }
            $currentSheet = $PHPExcel->getSheet(0);  //读取文件中的第一个工作表
            $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
            $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
            $maxColumnNumber = 10;
            $insertData = [];
            for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
                $values = [];
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getFormattedValue();
                    if ($currentColumn == 1) {
                       $val = date('Y-m-d H:i:s', strtotime($val)); //时间
                    }
                    $values[] = is_null($val) ? '' : $val;
                }
                $insertData[] = $values;
            }
        }catch (\Exception $e) {
            return json_fail($e->getMessage());
        }
        return $insertData;
    }
}