wanyue_app/app/admin/service/member/MemberService.php

<?php

namespace app\admin\service\member;

use app\common\Tree;
use app\model\ClientPoints;
use app\model\CouponDetail;
use app\model\Device;
use app\model\Family;
use app\model\FamilyMember;
use app\model\Goods;
use app\model\Member;
use app\model\MemberAccount;
use app\model\MemberCert;
use app\model\MemberDevice;
use app\model\MemberInfo;
use app\model\Order;
use app\model\OrderSheet;
use app\model\PayDetail;
use app\model\SysCategory;
use app\model\SysDept;
use app\model\SysSerial;
use app\model\SysUser;
use support\Db;
use support\exception\BusinessException;
use support\Request;
use support\Response;

class MemberService
{
    public static function list(Request $request)
    {
        $page = $request->get('page', 1);
        $pageSize = $request->get('pageSize', 10);
        $name = $request->get('name', '');
        $nickname = $request->get('nickname', '');
        $mobile = $request->get('mobile', '');
        $level = $request->get('level', '');
        $isAuth = $request->get('is_auth', '');
        $addtime = $request->get('member_addtimes', []);
        $type = $request->get('type', '');
        $status = $request->get('member_status', '');

        $rows = Member::with([
            'account' => function ($query) {
                $query->where('member_account_status', 'ACTIVED');
            }
        ])
            ->where('member_is_owner', 'N')
            ->where('member_mobile', '<>', '0000')
            ->leftJoin('member_info', 'member_info.join_info_member_id', 'member.member_id')
            ->leftJoin('member_cert', 'member_cert.join_cert_member_id', 'member.member_id')
//            ->leftjoin('member_account', 'member.member_id', '=', 'member_account.join_account_member_id')
            ->leftJoin('member_role', 'member_role.member_role_id', 'member.join_member_role_id')
//            ->where('member_account.member_account_classify', 'CASH')
            ->when($name != '', function ($query) use ($name) {
                $query->where('member_cert.member_cert_name', 'like', '%' . $name . '%');
            })->when($mobile != '', function ($query) use ($mobile) {
                $query->where('member.member_mobile', 'like', '%' . $mobile . '%');
            })->when($level != '', function ($query) use ($level) {
                if ($level == 'other') {
                    $query->where('member.join_member_role_id', NULL)->orWhere('member.join_member_role_id', '');
                } else if ($level == 'VIP') {
                    $query->where('member.member_is_vip', 'Y');
                } else {
                    $query->where('member_role.member_role_id', $level);
                }
            })->when($isAuth != '', function ($query) use ($isAuth) {
                if ($isAuth == 'Y') {
                    $query->where('member_cert.member_cert_name', '<>', NULL)->where('member_cert.member_cert_nbr', '<>', NULL)->where('member_cert.member_cert_name', '<>', '')->where('member_cert.member_cert_nbr', '<>', '');
                } else {
                    $query->where('member_cert.member_cert_name', NULL)->orWhere('member_cert.member_cert_nbr', NULL)->orWhere('member_cert.member_cert_name', '')->orWhere('member_cert.member_cert_nbr', '');
                }
            })->when(!empty($addtime), function ($query) use ($addtime) {
                $addtime[0] = strtotime($addtime[0]);
                $addtime[1] = strtotime($addtime[1]);
                $query->whereBetween('member.member_addtimes', $addtime);
            })->when(!empty($type), function ($query) use ($type) {
                if ($type == 'todayAdd') {
                    $addtime[0] = strtotime(date('Y-m-d') . ' 00:00:00');
                    $addtime[1] = strtotime(date('Y-m-d') . ' 23:59:59');
                } else if ($type == 'monthAdd') {
                    $addtime[0] = strtotime(date('Y-m-01') . ' 00:00:00');
                    $addtime[1] = strtotime(date('Y-m-t') . ' 23:59:59');
                }
                $query->whereBetween('member.member_addtimes', $addtime);
            })->when(!empty($nickname), function ($query) use ($nickname) {
                $query->where(function ($query2) use ($nickname) {
                    $query2->where('member_info.member_info_nickname', 'like', '%' . $nickname . '%')->orWhere('member.member_mobile', 'like', '%' . $nickname . '%');
                });
            })->when(!empty($status), function ($query) use ($status) {
                $query->where('member_status', $status);
            })
            ->select('member_id', 'member_is_vip', 'member_is_owner', 'member_is_partner', 'member_is_referrer', 'member_classify', 'member_status', 'member_mobile', 'member_from', 'member_addtimes', 'member_extend_json',
                'member_info.member_info_nickname', 'member_info.member_info_headimg', 'member_info.member_info_referee', 'member_info.member_info_business', 'member_info.member_info_service', 'member_info.member_info_city', 'member_info.member_info_address', 'member_info.member_info_gender', 'member_info.member_info_birthday',
                'member_role.member_role_id', 'member_role.member_role_name',
                'member_cert.member_cert_birth', 'member_cert.member_cert_gender', 'member_cert.member_cert_name', 'member_cert.member_cert_nbr', 'member_cert.member_cert_province', 'member_cert.member_cert_addr', 'member_cert.member_cert_face', 'member_cert.member_cert_photo', 'member_cert.member_cert_nation'
//                'member_account.member_account_surplus'
            );
        // 总条数
        $total = $rows->count('member_id');
        $rows = $rows->orderBy('member_addtimes', 'DESC')
            ->forPage($page, $pageSize)
            ->get()
            ->toArray();

        foreach ($rows as &$row) {
            $row['coupon_count'] = CouponDetail::where('join_coupon_detail_member_id', $row['member_id'])->count();
            $row['member_mobile'] = substr($row['member_mobile'], 0, 3) . '****' . substr($row['member_mobile'], 7);
            $row['info'] = [
                'member_info_nickname' => !empty($row['member_info_nickname']) ? $row['member_info_nickname'] : substr($row['member_mobile'], -4, 4) . '会员',
                'member_info_headimg' => !empty($row['member_info_headimg']) ? $row['member_info_headimg'] : '',
                'member_info_referee' => !empty($row['member_info_referee']) ? $row['member_info_referee'] : '',
                'member_info_business' => !empty($row['member_info_business']) ? $row['member_info_business'] : '',
                'member_info_service' => !empty($row['member_info_service']) ? $row['member_info_service'] : '',
                'member_info_city' => !empty($row['member_info_city']) ? explode('-', $row['member_info_city']) : '',
                'member_info_address' => !empty($row['member_info_address']) ? $row['member_info_address'] : '',
                'member_info_birthday' => !empty($row['member_info_birthday']) ? $row['member_info_birthday'] : '',
                'member_info_gender' => !empty($row['member_info_gender']) ? $row['member_info_gender'] : '',
            ];
            if (substr($row['info']['member_info_headimg'], 0, 2) == '..') {
                $row['info']['member_info_headimg'] = '';
            }
            if (!empty($row['info']['member_info_address']) && in_array($row['info']['member_info_address'], ['请选择', '省-市-区'])) {
                $row['info']['member_info_address'] = '';
            }

            $row['cert'] = [
                'member_cert_birth' => $row['member_cert_birth'],
                'member_cert_gender' => $row['member_cert_gender'],
                'member_cert_name' => $row['member_cert_name'],
                'member_cert_nbr' => $row['member_cert_nbr'],
                'member_cert_province' => !empty($row['member_cert_province']) && is_json($row['member_cert_province']) ? json_decode($row['member_cert_province']) : '',
                'member_cert_addr' => $row['member_cert_addr'],
                'member_cert_face' => !empty($row['member_cert_face']) ? getenv('STORAGE_DOMAIN') . $row['member_cert_face'] : '',
                'member_cert_photo' => !empty($row['member_cert_photo']) ? getenv('STORAGE_DOMAIN') . $row['member_cert_photo'] : '',
                'member_cert_nation' => $row['member_cert_nation'],
            ];
            $account = [];
            if (!empty($row['account'])) {
                foreach ($row['account'] as $item) {
                    if ($item['member_account_classify'] == 'WELFARE') {
                        $account['welfare'] = [
                            'member_account_name' => $item['member_account_name'],
                            'member_account_income' => $item['member_account_income'],
                            'member_account_expend' => $item['member_account_expend'],
                            'member_account_surplus' => $item['member_account_surplus'],
                            'member_account_added' => $item['member_account_added'],
                        ];
                    } else if ($item['member_account_classify'] == 'POINTS') {
                        $account['points'] = [
                            'member_account_name' => $item['member_account_name'],
                            'member_account_income' => $item['member_account_income'],
                            'member_account_expend' => $item['member_account_expend'],
                            'member_account_surplus' => $item['member_account_surplus'],
                            'member_account_added' => $item['member_account_added'],
                        ];
                    } else if ($item['member_account_classify'] == 'CASH') {
                        $account['cash'] = [
                            'member_account_name' => $item['member_account_name'],
                            'member_account_income' => $item['member_account_income'],
                            'member_account_expend' => $item['member_account_expend'],
                            'member_account_surplus' => $item['member_account_surplus'],
                            'member_account_added' => $item['member_account_added'],
                        ];
                    } else if ($item['member_account_classify'] == 'CARD') {
                        $account['card'] = [
                            'member_account_name' => $item['member_account_name'],
                            'member_account_income' => $item['member_account_income'],
                            'member_account_expend' => $item['member_account_expend'],
                            'member_account_surplus' => $item['member_account_surplus'],
                            'member_account_added' => $item['member_account_added'],
                        ];
                    } else if ($item['member_account_classify'] == 'VIP') {
                        $account['vip'] = [
                            'member_account_name' => $item['member_account_name'],
                            'member_account_income' => $item['member_account_income'],
                            'member_account_expend' => $item['member_account_expend'],
                            'member_account_surplus' => $item['member_account_surplus'],
                            'member_account_added' => $item['member_account_added'],
                        ];
                    }
                }
            }
            $row['account'] = $account;

            $row['is_auth'] = "未认证";
            if (!empty($row['member_cert_nbr']) && !empty($row['member_cert_name'])) {
                $row['is_auth'] = "已认证";
            }

            $row['source'] = "APP";
            $row['belong'] = [];
            $row['referee'] = [];
            if (!empty($row['member_extend_json'])) {
                $extendJson = json_decode($row['member_extend_json'], true);
                if (isset($extendJson['come_from'])) {
                    $row['source'] = $extendJson['come_from'] ?? '';
                }
                if (isset($extendJson['belong'])) {
                    $user = SysUser::where('user_id', $extendJson['belong']['user_id'])->select('user_name', 'user_mobile')->first();
                    if ($user) {
                        $row['belong']['user_id'] = $extendJson['belong']['user_id'];
                        $row['belong']['username'] = $user->user_name;
                        $row['belong']['user_mobile'] = $user->user_mobile;
                    }
                    $row['belong']['deptName'] = SysDept::where('dept_id', $extendJson['belong']['premises'])->value('dept_name');
                }
                if (isset($extendJson['referee'])) {
                    foreach ($extendJson['referee'] as $key => $referee) {
                        $row['referee'][] = [
                            'name' => $referee,
                            'time' => $key
                        ];
                    }
                }
            }
            // 粉丝
            $fans1 = Member::where('join_invite_member_id', $row['member_id'])
                ->select('member_id')
                ->get()
                ->toArray();
            $fans1Ids = array_column($fans1, 'member_id');
            $fans2Count = Member::whereIn('join_invite_member_id', $fans1Ids)
                ->count();
            $row['fans_total'] = count($fans1) + $fans2Count;

        }

        return json_success('', compact('rows', 'page', 'pageSize', 'total'));
    }


    public static function exportMember(Request $request)
    {
        $memberIds = $request->get('member_id', '');
        $name = $request->get('name', '');
        $mobile = $request->get('mobile', '');
        $level = $request->get('level', '');
        $isAuth = $request->get('is_auth', '');
        $addtime = $request->get('member_addtimes', []);

        $rows = Member::with('account')
            ->where('member_is_owner', 'N')
            ->where('member_mobile', '<>', '0000')
            ->leftJoin('member_info', 'member_info.join_info_member_id', 'member.member_id')
            ->leftJoin('member_cert', 'member_cert.join_cert_member_id', 'member.member_id')
//            ->leftjoin('member_account', 'member.member_id', '=', 'member_account.join_account_member_id')
            ->leftJoin('member_role', 'member_role.member_role_id', 'member.join_member_role_id')
//            ->where('member_account.member_account_classify', 'CASH')
            ->when($memberIds != '', function ($query) use ($memberIds) {
                $query->whereIn('member.member_id', $memberIds);
            })->when($name != '', function ($query) use ($name) {
                $query->where('member_cert.member_cert_name', 'like', '%' . $name . '%');
            })->when($mobile != '', function ($query) use ($mobile) {
                $query->where('member.member_mobile', 'like', '%' . $mobile . '%');
            })->when($level != '', function ($query) use ($level) {
                if ($level == 'other') {
                    $query->where('member.join_member_role_id', NULL)->orWhere('member.join_member_role_id', '');
                } else {
                    $query->where('member_role.member_role_id', $level);
                }
            })->when($isAuth != '', function ($query) use ($isAuth) {
                if ($isAuth == 'Y') {
                    $query->where('member_cert.member_cert_name', '<>', NULL)->where('member_cert.member_cert_nbr', '<>', NULL)->where('member_cert.member_cert_name', '<>', '')->where('member_cert.member_cert_nbr', '<>', '');
                } else {
                    $query->where('member_cert.member_cert_name', NULL)->where('member_cert.member_cert_nbr', NULL)->where('member_cert.member_cert_name', '')->where('member_cert.member_cert_nbr', '');
                }
            })->when(!empty($addtime), function ($query) use ($addtime) {
                $addtime[0] = strtotime($addtime[0]);
                $addtime[1] = strtotime($addtime[1]);
                $query->whereBetween('member.member_addtimes', $addtime);
            })->select('member_id', 'member_is_owner', 'member_classify', 'member_status', 'member_mobile', 'member_from', 'member_addtimes', 'member_extend_json',
                'member_info.member_info_nickname', 'member_info.member_info_headimg', 'member_info.member_info_referee', 'member_info.member_info_business', 'member_info.member_info_service',
                'member_role.member_role_id', 'member_role.member_role_name',
                'member_cert.member_cert_birth', 'member_cert.member_cert_gender', 'member_cert.member_cert_name', 'member_cert.member_cert_nbr', 'member_cert.member_cert_province', 'member_cert.member_cert_addr', 'member_cert.member_cert_face', 'member_cert.member_cert_photo', 'member_cert.member_cert_nation',
//                'member_account.member_account_surplus'
            )->orderBy('member_addtimes', 'DESC')
            ->get()
            ->toArray();

        $exportData = [];
        foreach ($rows as $row) {
            $row['info'] = [
                'member_info_nickname' => !empty($row['member_info_nickname']) ? $row['member_info_nickname'] : substr($row['member_mobile'], -4, 4) . '会员',
                'member_info_headimg' => !empty($row['member_info_headimg']) ? $row['member_info_headimg'] : '',
                'member_info_referee' => !empty($row['member_info_referee']) ? $row['member_info_referee'] : '',
                'member_info_business' => !empty($row['member_info_business']) ? $row['member_info_business'] : '',
                'member_info_service' => !empty($row['member_info_service']) ? $row['member_info_service'] : ''
            ];
            $row['cert'] = [
                'member_cert_name' => $row['member_cert_name'],
            ];
            $account = [];
            if (!empty($row['account'])) {
                foreach ($row['account'] as $item) {
                    if ($item['member_account_classify'] == 'POINTS') {
                        $account['points'] = [
                            'member_account_surplus' => $item['member_account_surplus'],
                            'member_account_added' => $item['member_account_added'],
                        ];
                    } else if ($item['member_account_classify'] == 'CASH') {
                        $account['cash'] = [
                            'member_account_surplus' => $item['member_account_surplus'],
                            'member_account_added' => $item['member_account_added'],
                        ];
                    } else if ($item['member_account_classify'] == 'CARD') {
                        $account['card'] = [
                            'member_account_surplus' => $item['member_account_surplus'],
                            'member_account_added' => $item['member_account_added'],
                        ];
                    }
                }
            }
            // $row['account'] = $account;

            $row['is_auth'] = "未认证";
            if (!empty($row['member_cert_nbr']) && !empty($row['member_cert_name'])) {
                $row['is_auth'] = "已认证";
            }

            $row['source'] = "APP";
            if (!empty($row['member_extend_json'])) {
                $extendJson = json_decode($row['member_extend_json'], true);
                if (isset($extendJson['come_from'])) {
                    $row['source'] = $extendJson['come_from'] ?? '';
                }
            }

            $sourceStr = ['APP' => "APP", 'CUSTOMER' => "客户管理", 'SYSTEM' => "后台"];
            $exportData[] = [
                'member_id' => $row['member_id'],
                'member_info_nickname' => $row['info']['member_info_nickname'] ?? '',
                'member_mobile' => $row['member_mobile'] ?? '',
                'member_cert_name' => $row['cert']['member_cert_name'] ?? '',
                'source' => in_array($row['source'], $sourceStr) ? $sourceStr[$row['source']] : 'APP',
                'cash' => isset($account['cash']) ? $account['cash']['member_account_surplus'] + $account['cash']['member_account_added'] : 0,
                'points' => isset($account['points']) ? $account['points']['member_account_surplus'] + $account['points']['member_account_added'] : 0,
                'is_auth' => $row['is_auth'],
                'level' => $row['member_role_name'] ?? '普通会员',
                'member_addtimes' => $row['member_addtimes'],
                'member_info_referee' => $row['info']['member_info_referee'] ?? '',
                'member_info_business' => $row['info']['member_info_business'] ?? '',
                'member_info_service' => $row['info']['member_info_service'] ?? '',
            ];
        }
        return json_success('', $exportData);
    }


    public static function info($memberId)
    {
        $member = Member::with([
            'cert',
            'info',
            'role' => function ($query) {
                $query->select('member_role_id','member_role_name');
            }
        ])
            ->where('member_id', $memberId)
            ->first();
        if (!$member) {
            return json_fail('查询错误');
        }
        $member = $member->toArray();
        $member['info'] = [
            'member_info_nickname' => !empty($member['info']) && !empty($member['info']['member_info_nickname']) ? $member['info']['member_info_nickname'] : substr($member['member_mobile'], -4, 4) . '会员',
            'member_info_headimg' => !empty($member['info']) && !empty($member['info']['member_info_headimg']) ? $member['info']['member_info_headimg'] : '',
            'member_info_referee' => !empty($member['info']) && !empty($member['info']['member_info_referee']) ? $member['info']['member_info_referee'] : '',
            'member_info_business' => !empty($member['info']) && !empty($member['info']['member_info_business']) ? $member['info']['member_info_business'] : '',
            'member_info_service' => !empty($member['info']) && !empty($member['info']['member_info_service']) ? $member['info']['member_info_service'] : '',
            'member_info_city' => !empty($member['info']) && !empty($member['info']['member_info_city']) ? explode('-', $member['info']['member_info_city']) : '',
            'member_info_address' => !empty($member['info']) && !empty($member['info']['member_info_address']) ? $member['info']['member_info_address'] : '',
            'member_info_gender' => !empty($member['info']) && !empty($member['info']['member_info_gender']) ? $member['info']['member_info_gender'] : '',
            'member_info_birthday' => !empty($member['info']) && !empty($member['info']['member_info_birthday']) ? $member['info']['member_info_birthday'] : '',
        ];
        if (!empty($member['info']['member_info_address']) && in_array($member['info']['member_info_address'], ['请选择', '省-市-区'])) {
            $member['info']['member_info_address'] = '';
        }
        $member['cert'] = [
            'member_cert_birth' => $member['cert']['member_cert_birth'] ?? '',
            'member_cert_gender' => $member['cert']['member_cert_gender'] ?? '',
            'member_cert_name' => $member['cert']['member_cert_name'] ?? '',
            'member_cert_nbr' => $member['cert']['member_cert_nbr'] ?? '',
            'member_cert_province' => !empty($member['cert']) && !empty($member['cert']['member_cert_province']) && is_json($member['cert']['member_cert_province']) ? json_decode($member['cert']['member_cert_province']) : '',
            'member_cert_addr' => $member['cert']['member_cert_addr'] ?? '',
            'member_cert_face' => !empty($member['cert']) && !empty($member['cert']['member_cert_face']) ? getenv('STORAGE_DOMAIN') . $member['cert']['member_cert_face'] : '',
            'member_cert_photo' => !empty($member['cert']) && !empty($member['cert']['member_cert_photo']) ? getenv('STORAGE_DOMAIN') . $member['cert']['member_cert_photo'] : '',
            'member_cert_nation' => $member['cert']['member_cert_nation'] ?? '',
            'member_cert_addtimes' => $member['cert']['member_cert_addtimes'] ?? '',
        ];
        if (!empty($member['cert']['member_cert_addtimes'])) {
            $member['cert']['member_cert_addtimes'] = date('Y-m-d', $member['cert']['member_cert_addtimes']);
        } else {
            $member['cert']['member_cert_addtimes'] = date('Y-m-d', strtotime($member['member_addtimes']));
        }
        if (!empty($member['join_invite_member_id'])) {
            $inviteMember = Member::with([
                'info' => function ($query) {
                    $query->select('join_info_member_id', 'member_info_nickname');
                },
                'cert' => function ($query) {
                    $query->select('join_cert_member_id', 'member_cert_name');
                }
            ])->where('member_id', $member['join_invite_member_id'])
                ->first();
            $inviteMemberName = '';
            if (!empty($inviteMember->cert) && !empty($inviteMember->cert->member_cert_name)) {
                $inviteMemberName = $inviteMember->cert->member_cert_name;
            } elseif (!empty($inviteMemberName->info) && !empty($inviteMemberName->info->member_info_nickname)) {
                $inviteMemberName = $inviteMemberName->info->member_info_nickname;
            }
            $member['invite_member'] = $inviteMemberName . (!empty($inviteMember->member_invite_code) ? '(' . $inviteMember->member_invite_code . ')' : '');
        }

        $member['is_auth'] = "未认证";
        if (!empty($member['cert']['member_cert_nbr']) && !empty($member['cert']['member_cert_name'])) {
            $member['is_auth'] = "已认证";
        }

        $member['source'] = "APP";
        $member['belong'] = [];
        $member['referee'] = [];
        if (!empty($member['member_extend_json'])) {
            $extendJson = json_decode($member['member_extend_json'], true);
            if (isset($extendJson['come_from'])) {
                $member['source'] = $extendJson['come_from'] ?? '';
            }
            if (isset($extendJson['belong'])) {
                $user = SysUser::where('user_id', $extendJson['belong']['user_id'])->select('user_name', 'user_mobile')->first();
                if ($user) {
                    $member['belong']['user_id'] = $extendJson['belong']['user_id'];
                    $member['belong']['username'] = $user->user_name;
                    $member['belong']['user_mobile'] = $user->user_mobile;
                }
                $member['belong']['deptName'] = SysDept::where('dept_id', $extendJson['belong']['premises'])->value('dept_name');
            }
            if (isset($extendJson['referee'])) {
                foreach ($extendJson['referee'] as $key => $referee) {
                    $member['referee'][] = [
                        'name' => $referee,
                        'time' => $key
                    ];
                }
            }
        }
        if (!empty($member['join_invite_member_id'])){
            $inviteMember = Member::with('cert', 'info')
                ->where('member_id',$member['join_invite_member_id'])
                ->select('member_id','member_mobile','member_invite_code')
                ->first();
            if (!empty($inviteMember)){
                $inviteMember = $inviteMember->toArray();
                $mobile = '';
                if (!empty($inviteMember['member_mobile'])){
                    $mobile = $inviteMember['member_mobile'];
                }
                $nickname = '';
                if (!empty($inviteMember['info']) && !empty($inviteMember['info']['member_info_nickname'])){
                    $nickname = $inviteMember['info']['member_info_nickname'];
                }
                $certname = '';
                if (!empty($inviteMember['cert']) && !empty($inviteMember['cert']['member_info_nickname'])){
                    $nickname = $inviteMember['cert']['member_cert_name'];
                }
                $member['invite_name'] = MemberService::getMemberName($mobile,$certname,$nickname);
                $member['invite_code'] =  $inviteMember['member_invite_code'] ?? '';
            }
        }

        return json_success('', $member);
    }

    /**
     * VIP账户明细
     */
    public static function vipInfo(Request $request)
    {
        $memberId = $request->get('member_id', '');
        $page = $request->get('page', 1);
        $pageSize = $request->get('pageSize', 20);

        $account = MemberAccount::where('join_account_member_id', $memberId)
            ->where('member_account_classify', 'VIP')
            ->first();
        if (!$account) {
            return json_fail('暂无数据');
        }
        $balance = $account->member_account_surplus + $account->member_account_added;

        $info = PayDetail::where(function ($query) use ($memberId) {
            $query->where('join_pay_member_id', $memberId)
                // ->whereIn('pay_prepayid', ['W06', 'A01'])
                ->where('pay_category', 'VIP')
                ->where('pay_status', 'SUCCESS');
        })->orWhere(function ($query) use ($memberId) {
            $query->where('join_pay_member_id', $memberId)
                ->where('pay_prepayid', 'like', '%VIP%')
                ->where('pay_status', 'SUCCESS');
        })->orderByDesc('pay_addtimes')
            ->get()
            ->toArray();
        $data = [];
        foreach ($info as $key => $item) {
            $categoryIds = [];
            if (!empty($item['join_pay_order_id'])) {
                $orderIds = Order::where('order_groupby', $item['join_pay_order_id'])->pluck('order_id');
                $goodsIds = OrderSheet::whereIn('join_sheet_order_id', $orderIds)->pluck('join_sheet_goods_id');
                $categoryIds = Goods::whereIn('goods_id', $goodsIds)->pluck('join_goods_category_id');
            }


            $categoryName = [];
            foreach ($categoryIds as $id) {
                $category = SysCategory::where('category_id', $id)->first();
                if (!empty($category->category_super_path)) {
                    $path = explode('#', $category->category_super_path);
                    $ids = array_filter($path);
                    $categoryName[] = SysCategory::where('category_id', current($ids))->value('category_name');
                } else {
                    $categoryName[] = $category->category_name;
                }
            }
            $item['goods_category'] = implode(',', array_unique($categoryName));
            if (mb_substr($item['goods_category'], 0, 7) == '自定义金额充值') {
                $item['goods_category'] = '自定义金额充值';
            }

            if (in_array($item['pay_category'], ['VIP', 'REFUND'])) {
                $item['balance_type'] = 'add';
            } else {
                $item['balance_type'] = 'cut';
            }
            if ($key == 0 && in_array($item['pay_category'], ['VIP', 'REFUND'])) {
                if (!empty($item['pay_extend_json'])) {
                    $extendJson = json_decode($item['pay_extend_json'], true);
                    if (!isset($extendJson['added_amount'])) {
                        $extendJson['added_amount'] = 0;
                    }
                    if (isset($extendJson['added_amount']) && floatval($extendJson['added_amount']) > 0) {
                        $data[] = [
                            'join_pay_member_id' => $item['join_pay_member_id'],
                            'pay_addtimes' => $item['pay_addtimes'],
                            'pay_amount' => $extendJson['added_amount'],
                            'pay_category' => 'ADDED',
                            'balance' => number_format($balance, 2, '.', ''),
                            'balance_type' => 'add',
                        ];
                    }
                    $balance = $balance - $extendJson['added_amount'];
                }

                $item['balance'] = number_format($balance, 2, '.', '');
                $balance = $balance - $item['pay_amount'];
                $data[] = $item;
                continue;
            }

            if (in_array($item['pay_category'], ['VIP', 'REFUND'])) {
                if (!empty($item['pay_extend_json'])) {
                    $extendJson = json_decode($item['pay_extend_json'], true);
                    if (isset($extendJson['added_amount']) && floatval($extendJson['added_amount']) > 0) {
                        $data[] = [
                            'join_pay_member_id' => $item['join_pay_member_id'],
                            'pay_addtimes' => $item['pay_addtimes'],
                            'pay_amount' => $extendJson['added_amount'],
                            'pay_category' => 'ADDED',
                            'balance' => number_format($balance, 2, '.', ''),
                            'balance_type' => 'add',
                        ];
                        $balance = $balance - $extendJson['added_amount'];
                    }
                }
                $item['balance'] = number_format($balance, 2, '.', '');
                $balance = $balance - $item['pay_amount'];
            } else {
                $item['balance'] = number_format($balance, 2, '.', '');
                $balance = $balance + $item['pay_amount'];
            }

            $data[] = $item;
        }

        $total = count($data);
        $start = ($page - 1) * $pageSize;
        $rows = array_slice($data, $start, $pageSize);

        return json_success('', compact('rows', 'page', 'pageSize', 'total'));
    }

    public static function welfareInfo(Request $request)
    {
        $memberId = $request->get('member_id', '');
        $page = $request->get('page', 1);
        $pageSize = $request->get('pageSize', 20);

        $account = MemberAccount::where('join_account_member_id', $memberId)
            ->where('member_account_classify', 'WELFARE')
            ->first();
        if (!$account) {
            return json_fail('暂无数据');
        }
        $balance = $account->member_account_surplus + $account->member_account_added;

        $info = PayDetail::where(function ($query) use ($memberId) {
            $query->where('join_pay_member_id', $memberId)
                // ->whereIn('pay_prepayid', ['W06', 'A01'])
                ->where('pay_category', 'RECHARGE')
                ->where('pay_prepayid', 'WELFARE')
                ->where('pay_status', 'SUCCESS');
        })->orWhere(function ($query) use ($memberId) {
            $query->where('join_pay_member_id', $memberId)
                ->where('pay_prepayid', 'like', '%WELFARE%')
                ->where('pay_status', 'SUCCESS');
        })->orderByDesc('pay_addtimes')
            ->get()
            ->toArray();
        $data = [];
        foreach ($info as $key => $item) {
            $categoryIds = [];
            if (!empty($item['join_pay_order_id'])) {
                $orderIds = Order::where('order_groupby', $item['join_pay_order_id'])->pluck('order_id');
                $goodsIds = OrderSheet::whereIn('join_sheet_order_id', $orderIds)->pluck('join_sheet_goods_id');
                $categoryIds = Goods::whereIn('goods_id', $goodsIds)->pluck('join_goods_category_id');
            }


            $categoryName = [];
            foreach ($categoryIds as $id) {
                $category = SysCategory::where('category_id', $id)->first();
                if (!empty($category->category_super_path)) {
                    $path = explode('#', $category->category_super_path);
                    $ids = array_filter($path);
                    $categoryName[] = SysCategory::where('category_id', current($ids))->value('category_name');
                } else {
                    $categoryName[] = $category->category_name;
                }
            }
            $item['goods_category'] = implode(',', array_unique($categoryName));
            if (mb_substr($item['goods_category'], 0, 7) == '自定义金额充值') {
                $item['goods_category'] = '自定义金额充值';
            }

            if (in_array($item['pay_category'], ['RECHARGE', 'REFUND'])) {
                $item['balance_type'] = 'add';
            } else {
                $item['balance_type'] = 'cut';
            }
            if ($key == 0 && in_array($item['pay_category'], ['RECHARGE', 'REFUND'])) {
                if (!empty($item['pay_extend_json'])) {
                    $extendJson = json_decode($item['pay_extend_json'], true);
                    if (!isset($extendJson['added_amount'])) {
                        $extendJson['added_amount'] = 0;
                    }
                    if (isset($extendJson['added_amount']) && floatval($extendJson['added_amount']) > 0) {
                        $data[] = [
                            'join_pay_member_id' => $item['join_pay_member_id'],
                            'pay_addtimes' => $item['pay_addtimes'],
                            'pay_amount' => $extendJson['added_amount'],
                            'pay_category' => 'ADDED',
                            'balance' => number_format($balance, 2, '.', ''),
                            'balance_type' => 'add',
                        ];
                    }
                    $balance = $balance - $extendJson['added_amount'];
                }

                $item['balance'] = number_format($balance, 2, '.', '');
                $balance = $balance - $item['pay_amount'];
                $data[] = $item;
                continue;
            }

            if (in_array($item['pay_category'], ['RECHARGE', 'REFUND'])) {
                if (!empty($item['pay_extend_json'])) {
                    $extendJson = json_decode($item['pay_extend_json'], true);
                    if (isset($extendJson['added_amount']) && floatval($extendJson['added_amount']) > 0) {
                        $data[] = [
                            'join_pay_member_id' => $item['join_pay_member_id'],
                            'pay_addtimes' => $item['pay_addtimes'],
                            'pay_amount' => $extendJson['added_amount'],
                            'pay_category' => 'ADDED',
                            'balance' => number_format($balance, 2, '.', ''),
                            'balance_type' => 'add',
                        ];
                        $balance = $balance - $extendJson['added_amount'];
                    }
                }
                $item['balance'] = number_format($balance, 2, '.', '');
                $balance = $balance - $item['pay_amount'];
            } else {
                $item['balance'] = number_format($balance, 2, '.', '');
                $balance = $balance + $item['pay_amount'];
            }

            $data[] = $item;
        }

        $total = count($data);
        $start = ($page - 1) * $pageSize;
        $rows = array_slice($data, $start, $pageSize);

        return json_success('', compact('rows', 'page', 'pageSize', 'total'));
    }

    /**
     * @Desc 我的粉丝列表
     * @Author Gorden
     * @Date 2024/9/20 10:22
     *
     * @param Request $request
     */
    public static function fansList(Request $request)
    {
        $memberId = $request->get('member_id');
        $name = $request->get('name', '');
        $mobile = $request->get('mobile', '');
        $memberIds = [];
        $memberInviteIds = [];
        if (!empty($name)) {
            $memberIds = MemberCert::where('member_cert_name', 'like', '%' . $name . '%')->pluck('join_cert_member_id')->toArray();
            $memberInviteIds = Member::whereIn('member_id', $memberIds)->pluck('join_invite_member_id')->toArray();
        }
        if (!empty($mobile)) {
            $members = Member::where('member_mobile', 'like', '%' . $mobile . '%')
                ->select('member_id', 'join_invite_member_id')
                ->get()
                ->toArray();
            $memberIds2 = array_column($members, 'member_id');
            $memberInviteIds2 = array_column($members, 'join_invite_member_id');
            $memberIds = array_merge($memberIds, $memberIds2);
            $memberInviteIds = array_merge($memberInviteIds, $memberInviteIds2);
        }

        $fans1 = Member::with([
            'info' => function ($query) {
                $query->select('join_info_member_id', 'member_info_nickname', 'member_info_headimg');
            },
            'cert' => function ($query) {
                $query->select('join_cert_member_id', 'member_cert_name');
            },
            'role' => function ($query) {
                $query->select('member_role_id', 'member_role_name');
            }
        ])->where('join_invite_member_id', $memberId)
            ->when(!empty($memberIds), function ($query) use ($memberIds, $memberInviteIds) {
                $allSub = array_merge($memberIds, $memberInviteIds);
                $query->whereIn('member_id', $allSub);
            })
            ->select('member_id', 'join_member_role_id', 'member_id as id', 'join_invite_member_id as pid', 'member_partner_json', 'member_mobile', 'member_addtimes', 'member_is_owner', 'member_is_vip', 'member_is_partner', 'member_is_referrer')
            ->get()
            ->toArray();
        $fans1Ids = array_column($fans1, 'id');
        $fans2 = Member::with([
            'info' => function ($query) {
                $query->select('join_info_member_id', 'member_info_nickname', 'member_info_headimg');
            },
            'cert' => function ($query) {
                $query->select('join_cert_member_id', 'member_cert_name');
            },
            'role' => function ($query) {
                $query->select('member_role_id', 'member_role_name');
            }
        ])->whereIn('join_invite_member_id', $fans1Ids)
            ->when(!empty($memberIds), function ($query) use ($memberIds) {
                $query->whereIn('member_id', $memberIds);
            })
            ->select('member_id', 'join_member_role_id', 'member_id as id', 'join_invite_member_id as pid', 'member_partner_json', 'member_mobile', 'member_addtimes', 'member_is_owner', 'member_is_vip', 'member_is_partner', 'member_is_referrer')
            ->get()
            ->toArray();

        $fans = array_merge($fans1, $fans2);
        foreach ($fans as &$fan) {
            $memberMobile = $fan['member_mobile'] ?? '';
//            $memberNickname = $fan['info'] && $fan['info']['member_info_nickname'] ? $fan['info']['member_info_nickname'] : '';
            $memberCertName = $fan['cert'] && $fan['cert']['member_cert_name'] ? $fan['cert']['member_cert_name'] : '';
            $fan['member_name'] = '';
            if (!empty($memberCertName) && !empty($memberMobile)) {
                $fan['member_name'] = $memberCertName . '-' . $memberMobile;
            } elseif (!empty($memberCertName)) {
                $fan['member_name'] = $memberCertName;
            } elseif (!empty($memberMobile)) {
                $fan['member_name'] = $memberMobile;
            }
            if (!empty($fan['info']) && substr($fan['info']['member_info_headimg'], 0, 4) == 'http') {
                $fan['member_info_headimg'] = $fan['info']['member_info_headimg'];
            } else {
                $fan['member_info_headimg'] = 'https://img.wanyuewellness.com.cn/images/avatar_default.png';
            }
            // 等级名称
            $fan['role_name'] = $fan['role'] && $fan['role']['member_role_name'] ? $fan['role']['member_role_name'] : '';

            unset($fan['info'], $fan['cert'], $fan['role']);
        }

        $tree = new Tree($fans);
        return json_success('success', $tree->getTree());
    }


    /**
     * 格式化树
     * @param $items
     * @return Response
     */
    protected static function formatTree($items): Response
    {
        $format_items = [];
        foreach ($items as $item) {
            $format_items[] = [
                'name' => $item->title ?? $item->name ?? $item->id,
                'value' => (string)$item->id,
                'id' => $item->id,
                'pid' => $item->pid,
            ];
        }
        $tree = new Tree($format_items);
        return json_success('success', $tree->getTree());
    }

    public static function clearWelfare(Request $request)
    {
        $memberId = $request->post('member_id');
        $payTime = $request->post('pay_paytimes', date('Y-m-d H:i:s'));
        $payTime = date('Y-m-d H:i:s', strtotime($payTime));
        $payRemark = $request->post('pay_remark', '');
        $clearPwd = $request->post('clear_password');
        if (!$clearPwd || $clearPwd != '123456') {
            return json_fail('清零密码错误');
        }

        Db::beginTransaction();
        try {
            $account = MemberAccount::where('join_account_member_id', $memberId)
                ->where('member_account_classify', 'WELFARE')
                ->where('member_account_status', 'ACTIVED')
                ->first();
            if (!$account) {
                throw new BusinessException("账户异常");
            }
            if ($account->member_account_surplus <= 0) {
                throw new BusinessException("账户余额为0，无需清零");
            }
            // 创建清除记录
            PayDetail::insert([
                'join_pay_member_id' => $memberId,
                'pay_status' => 'SUCCESS',
                'pay_category' => 'CLEAR',
                'pay_amount' => $account->member_account_surplus,
                'pay_paytimes' => $payTime,
                'pay_prepayid' => $memberId . '-WELFARE',
                'pay_remark' => $payRemark,
                'pay_addtimes' => time()
            ]);

            // 账户归零
            $account->member_account_expend = $account->member_account_expend + $account->member_account_surplus;
            $account->member_account_surplus = 0;
            $account->save();

            Db::commit();

            return json_success('success');
        } catch (BusinessException $e) {
            Db::rollBack();
            return json_fail($e->getMessage());
        } catch (\Exception $e) {
            dump($e->getMessage());
            Db::rollBack();

            return json_fail('清零失败');
        }
    }

    public static function balanceInfo(Request $request)
    {
        $memberId = $request->get('member_id', '');
        $page = $request->get('page', 1);
        $pageSize = $request->get('pageSize', 20);

        $account = MemberAccount::where('join_account_member_id', $memberId)
            ->where('member_account_classify', 'CASH')
            ->first();
        if (!$account) {
            return json_fail('暂无数据');
        }
        $balance = $account->member_account_surplus + $account->member_account_added;

        $info = PayDetail::where(function ($query) use ($memberId) {
            $query->where('join_pay_member_id', $memberId)
                // ->whereIn('pay_prepayid', ['W06', 'A01'])
                ->where('pay_category', 'RECHARGE')
                ->where('pay_prepayid', '<>', $memberId . '-WELFARE')
                ->where('pay_status', 'SUCCESS');
        })->orWhere(function ($query) use ($memberId) {
            $query->where('join_pay_member_id', $memberId)
                ->where('pay_prepayid', 'like', '%CASH%')
                ->where('pay_status', 'SUCCESS');
        })->orderByDesc('pay_addtimes')
            ->get()
            ->toArray();
        $data = [];
        foreach ($info as $key => $item) {
            $categoryIds = [];
            if (!empty($item['join_pay_order_id'])) {
                $orderIds = Order::where('order_groupby', $item['join_pay_order_id'])->pluck('order_id');
                $goodsIds = OrderSheet::whereIn('join_sheet_order_id', $orderIds)->pluck('join_sheet_goods_id');
                $categoryIds = Goods::whereIn('goods_id', $goodsIds)->pluck('join_goods_category_id');
            }


            $categoryName = [];
            foreach ($categoryIds as $id) {
                $category = SysCategory::where('category_id', $id)->first();
                if (!empty($category->category_super_path)) {
                    $path = explode('#', $category->category_super_path);
                    $ids = array_filter($path);
                    $categoryName[] = SysCategory::where('category_id', current($ids))->value('category_name');
                } else {
                    $categoryName[] = $category->category_name;
                }
            }
            $item['goods_category'] = implode(',', array_unique($categoryName));
            if (mb_substr($item['goods_category'], 0, 7) == '自定义金额充值') {
                $item['goods_category'] = '自定义金额充值';
            }
            if (in_array($item['pay_category'], ['RECHARGE', 'REFUND', 'GIVE', 'PARTNER'])) {
                $item['balance_type'] = 'add';
            } else {
                $item['balance_type'] = 'cut';
            }
            if ($key == 0 && in_array($item['pay_category'], ['RECHARGE', 'REFUND', 'GIVE', 'PARTNER'])) {
                if (!empty($item['pay_extend_json'])) {
                    $extendJson = json_decode($item['pay_extend_json'], true);
                    if (!isset($extendJson['added_amount'])) {
                        $extendJson['added_amount'] = 0;
                    }
                    // 计划任务还没跑
                    if ($item['pay_category'] == 'RECHARGE' && (!isset($extendJson['notify']) || $extendJson['notify'] != 'success')) {
                        continue;
                    }
                    if (isset($extendJson['added_amount']) && floatval($extendJson['added_amount']) > 0) {
                        $data[] = [
                            'join_pay_member_id' => $item['join_pay_member_id'],
                            'pay_addtimes' => $item['pay_addtimes'],
                            'pay_amount' => $extendJson['added_amount'],
                            'pay_category' => $item['pay_category'] == 'GIVE' ? 'GIVE' : 'ADDED',
                            'balance' => number_format($balance, 2, '.', ''),
                            'balance_type' => 'add',
                        ];
                    }
                    $balance = $balance - $extendJson['added_amount'];
                }

                $item['balance'] = number_format($balance, 2, '.', '');
                $balance = $balance - $item['pay_amount'];
                if ($item['pay_category'] != 'GIVE') {
                    $data[] = $item;
                }

                continue;
            }

            if (in_array($item['pay_category'], ['RECHARGE', 'REFUND', 'GIVE', 'PARTNER'])) {
                if (!empty($item['pay_extend_json'])) {
                    $extendJson = json_decode($item['pay_extend_json'], true);
                    if (isset($extendJson['added_amount']) && floatval($extendJson['added_amount']) > 0) {
                        $data[] = [
                            'join_pay_member_id' => $item['join_pay_member_id'],
                            'pay_addtimes' => $item['pay_addtimes'],
                            'pay_amount' => $extendJson['added_amount'],
                            'pay_category' => $item['pay_category'] == 'GIVE' ? 'GIVE' : 'ADDED',
                            'balance' => number_format($balance, 2, '.', ''),
                            'balance_type' => 'add',
                        ];
                        $balance = $balance - $extendJson['added_amount'];
                    }
                }
                $item['balance'] = number_format($balance, 2, '.', '');
                $balance = $balance - $item['pay_amount'];
            } else {
                $item['balance'] = number_format($balance, 2, '.', '');
                $balance = $balance + $item['pay_amount'];
            }

            if ($item['pay_category'] != 'GIVE') {
                $data[] = $item;
            }
        }

        $total = count($data);
        $start = ($page - 1) * $pageSize;
        $rows = array_slice($data, $start, $pageSize);

        return json_success('', compact('rows', 'page', 'pageSize', 'total'));
    }

    public static function pointInfo($memberId)
    {
        $account = MemberAccount::where('join_account_member_id', $memberId)
            ->where('member_account_classify', 'POINTS')
            ->first();
        if (!$account) {
            return json_fail('暂无数据');
        }
        $balance = $account->member_account_surplus + $account->member_account_added;
        $points = ClientPoints::where('join_client_points_member_id', $memberId)->get()->toArray();
        $nowPoint = 0;
        foreach ($points as $key => &$point) {
            $point['goods_category'] = '其他';
            if (!empty($point['join_client_points_goods_id'])) {
                $categoryId = Goods::where('goods_id', $point['join_client_points_goods_id'])->pluck('join_goods_category_id');

                $category = SysCategory::where('category_id', $categoryId)->first();
                if (!empty($category->category_super_path)) {
                    $path = explode('#', $category->category_super_path);
                    $ids = array_filter($path);
                    $point['goods_category'] = SysCategory::where('category_id', current($ids))->value('category_name');
                } else {
                    $point['goods_category'] = $category->category_name;
                }
            }

            if (in_array($point['client_points_category'], ["评价", "消费", "活动", "赠送", '登录活动积分'])) {
                $point['balance_type'] = 'add';
            } else {
                $point['balance_type'] = 'cut';
            }

            if ($key == 0) {
                $point['balance'] = number_format($balance, 2, '.', '');
                if (in_array($point['client_points_category'], ["评价", "消费", "活动", "赠送", '登录活动积分'])) {
                    $balance = $balance - $point['client_points_nbr'];
                } else {
                    $balance = $balance + $point['client_points_nbr'];
                }

                continue;
            }
            if (in_array($point['client_points_category'], ["评价", "消费", "活动", "赠送", '登录活动积分'])) {
                $point['balance'] = number_format($balance, 2, '.', '');
                $balance = $balance - $point['client_points_nbr'];
//                $point['balance'] = $balance = $nowPoint = number_format($balance - $point['client_points_nbr'], 2, '.', '');
            } else {
                $point['balance'] = number_format($balance, 2, '.', '');
                $balance = $balance + $point['client_points_nbr'];
//                $point['balance'] = $balance = $nowPoint = number_format($balance + $point['client_points_nbr'], 2, '.', '');
            }
        }

        return json_success('', $points);
    }

    /**
     * @Desc 添加会员
     * @Author Gorden
     * @Date 2024/3/25 13:40
     *
     * @param $params
     * @return \support\Response
     */
    public static function add($params)
    {
        Db::beginTransaction();
        try {
            $memberId = self::generateMemberId();
            // 先写主表，要ID
            $memberData = [
                'member_id' => $memberId,
                'member_is_owner' => $params['is_owner'] ?? 'N',
                'member_classify' => $params['classify'] ?? '',
                'member_status' => $params['status'] ?? 'PENDING',
                'member_mobile' => $params['mobile'] ?? '',
                'member_category' => $params['member_category'] ?? '后台添加',
                'member_invite_code' => self::generateInviteCode(),
//                'member_from' => $params['source'],
                // 'member_extend_json' => json_encode(['come_from' => $params['source']]),
                'member_addtimes' => time(),
            ];

            $extendJson['come_from'] = $params['source'];
            if (!empty($params['user_id'])) {
                $deptId = SysUser::where('user_id', $params['user_id'])->value('join_user_dept_id');
                if (!$deptId) {
                    throw new BusinessException('请先关联业务员和门店');
                }
                $extendJson['belong'] = ['user_id' => $params['user_id'], 'premises' => $deptId];
            }
            $memberData['member_extend_json'] = json_encode($extendJson);

            Member::insert($memberData);

            // 账户表
            $accountData = [
                'join_account_member_id' => $memberId,
                'member_account_classify' => 'POINTS',
                'member_account_status' => 'ACTIVED',
                'member_account_category' => 'NORMAL',
                'member_account_nbr' => $memberId . '-POINTS',
                'member_account_name' => '积分账户',
                'member_account_addtimes' => time()
            ];
            // 积分账户
            MemberAccount::insert($accountData);
            $accountData['member_account_classify'] = 'CASH';
            $accountData['member_account_nbr'] = $memberId . '-CASH';
            $accountData['member_account_name'] = '余额账户';
            // 现金/余额账户
            MemberAccount::insert($accountData);
            // 福利账户
//            if (!empty($params['member_classify']) && $params['member_classify'] == 'EMPLOY'){
//                $welfareData = [
//                    'join_account_member_id'=>$memberId,
//                    'member_account_classify'=>'WELFARE',
//                    'member_account_status'=>'ACTIVED',
//                    'member_account_category'=>'NORMAL',
//                    'member_account_nbr'=>$memberId.'-WELFARE',
//                    'member_account_name'=>'福利账户',
//                    'member_account_addtimes'=>time()
//                ];
//                MemberAccount::insert($welfareData);
//            }

            // member cert
            $certData = [
                'join_cert_member_id' => $memberId,
                'member_cert_nation' => $params['member_cert_nation'] ?? '',
                'member_cert_name' => $params['cert_name'] ?? '',
                'member_cert_nbr' => $params['cert_nbr'] ?? '',
                'member_cert_birth' => !empty($params['birth']) ? date('Y-m-d', strtotime($params['birth'])) : '',
                'member_cert_gender' => $params['gender'] ?? '',
//                'member_cert_province' => !empty($params['province']) ? json_encode($params['province'], JSON_UNESCAPED_UNICODE) : '[]',
//                'member_cert_city' => $params['city'] ?? '',
//                'member_cert_addr' => $params['addr'] ?? '',
//                'member_cert_face' => str_replace(getenv('STORAGE_DOMAIN'), '', $params['member_cert_face']),
//                'member_cert_photo' => str_replace(getenv('STORAGE_DOMAIN'), '', $params['member_cert_photo']),
            ];
            MemberCert::insert($certData);
            // member info
            $infoData = [
                'join_info_member_id' => $memberId,
                'member_info_nickname' => $params['account_name'] ?? '',
                'member_info_headimg' => !empty($params['avatar']) ? getenv('SYSTEM_AVATAR_DOMAIN') . str_replace(getenv('STORAGE_DOMAIN'), '', $params['avatar']) : '',
                'member_info_referee' => $params['member_info_referee'] ?? '',
                'member_info_business' => $params['member_info_business'] ?? '',
                'member_info_service' => $params['member_info_service'] ?? '',
                'member_info_city' => !empty($params['province']) ? implode('-', $params['province']) : '',
                'member_info_address' => $params['member_info_address'] ?? '',
                'member_info_birthday' => !empty($params['birth']) ? date('Y-m-d', strtotime($params['birth'])) : '',
                'member_info_gender' => $params['gender'] ?? '',
            ];
            MemberInfo::insert($infoData);

            Db::commit();
        } catch (BusinessException $e) {
            Db::rollBack();

            return json_fail($e->getMessage());
        } catch (\Exception $e) {
            Db::rollBack();
            dump($e->getMessage() . ';line:' . $e->getLine());
            return json_fail("数据添加失败");
        }

        return json_success('数据添加成功', ['member_id' => $memberId]);
    }

    /**
     * @Desc 编辑会员/ 业主
     * @Author Gorden
     * @Date 2024/4/20 15:18
     *
     * @param $params
     * @return \support\Response
     */
    public static function update($params)
    {
        Db::beginTransaction();
        try {
            $memberId = $params['member_id'];
            $member = Member::where('member_id', $params['member_id'])->first();
            if (!$member) {
                throw new BusinessException('数据异常');
            }
            $extendJson = [];
            if (!empty($member->member_extend_json)) {
                $extendJson = json_decode($member->member_extend_json, true);
            }
            $extendJson['come_from'] = $params['source'];
            // 门店、业务员
            if (!empty($params['user_id'])) {
                $deptId = SysUser::where('user_id', $params['user_id'])->value('join_user_dept_id');
                if (!$deptId) {
                    throw new BusinessException('您选择的业务员没有关联门店');
                }
                $extendJson['belong'] = ['user_id' => $params['user_id'], 'premises' => $deptId];
            }
            // 先写主表，要ID
            $memberData = [
                'member_id' => $memberId,
                'member_is_owner' => $params['is_owner'],
                'member_classify' => $params['member_classify'],
                'member_status' => $params['status'],
                // 'member_mobile' => $params['mobile'],
//                'member_from' => $params['source'],
                'member_extend_json' => json_encode($extendJson)
            ];

            Member::where('member_id', $params['member_id'])->update($memberData);
            // member cert
            $certData = [
                'join_cert_member_id' => $memberId,
                'member_cert_nation' => $params['member_cert_nation'] ?? '',
                'member_cert_name' => $params['cert_name'] ?? '',
                'member_cert_nbr' => $params['cert_nbr'] ?? '',
                'member_cert_birth' => $params['birth'] ? date('Y-m-d', strtotime($params['birth'])) : '',
                'member_cert_gender' => $params['gender'] ?? '',
//                'member_cert_province' => $params['province'] ? json_encode($params['province'], JSON_UNESCAPED_UNICODE) : '[]',
//                'member_cert_city' => $params['city'] ?? '',
//                'member_cert_addr' => $params['addr'] ?? '',
                'member_cert_face' => str_replace(getenv('STORAGE_DOMAIN'), '', $params['member_cert_face']),
                'member_cert_photo' => str_replace(getenv('STORAGE_DOMAIN'), '', $params['member_cert_photo']),
            ];
            if (!MemberCert::where('join_cert_member_id', $memberId)->exists()) {
                MemberCert::insert($certData);
            } else {
                MemberCert::where('join_cert_member_id', $params['member_id'])->update($certData);
            }
            // member info
            $avatar = '';
            if ($params['avatar'] && (strpos($params['avatar'], getenv('APP_IMAGE_DOMAIN')) !== false || strpos($params['avatar'], getenv('SYSTEM_AVATAR_DOMAIN')) !== false)) {
                $avatar = $params['avatar'];
            } else if ($params['avatar']) {
                $avatar = getenv('SYSTEM_AVATAR_DOMAIN') . str_replace(getenv('STORAGE_DOMAIN'), '', $params['avatar']);
            }

            $infoData = [
                'join_info_member_id' => $memberId,
                'member_info_nickname' => $params['account_name'],
                'member_info_headimg' => $avatar,
                'member_info_referee' => $params['member_info_referee'] ?? '',
                'member_info_business' => $params['member_info_business'] ?? '',
                'member_info_service' => $params['member_info_service'] ?? '',
                'member_info_city' => !empty($params['province']) ? implode('-', $params['province']) : '',
                'member_info_address' => $params['member_info_address'] ?? '',
                'member_info_birthday' => $params['birth'] ? date('Y-m-d', strtotime($params['birth'])) : '',
                'member_info_gender' => $params['gender'] ?? '',
            ];
            if (!MemberInfo::where('join_info_member_id', $params['member_id'])->exists()) {
                MemberInfo::insert($infoData);
            } else {
                MemberInfo::where('join_info_member_id', $params['member_id'])->update($infoData);
            }
            // 福利账户
            if (!empty($params['member_classify']) && $params['member_classify'] == 'EMPLOY' && !MemberAccount::where('member_account_nbr', $memberId . '-WELFARE')->exists()) {
                $welfareData = [
                    'join_account_member_id' => $memberId,
                    'member_account_classify' => 'WELFARE',
                    'member_account_status' => 'ACTIVED',
                    'member_account_category' => 'NORMAL',
                    'member_account_nbr' => $memberId . '-WELFARE',
                    'member_account_name' => '福利账户',
                    'member_account_addtimes' => time()
                ];
                MemberAccount::insert($welfareData);
            }

            Db::commit();
        } catch (BusinessException $e) {
            Db::rollBack();
            dump($e->getMessage() . ';line:' . $e->getLine());
            return json_fail($e->getMessage());
        } catch (\Exception $e) {
            Db::rollBack();
            dump($e->getMessage() . ';line:' . $e->getLine());
            return json_fail("数据更新失败");
        }

        return json_success('数据更新成功');
    }

    /**
     * @Desc 会员关联设备列表
     * @Author Gorden
     * @Date 2024/4/7 9:52
     *
     * @param $id
     * @return \support\Response
     */
    public static function deviceList($id)
    {
        $family = Family::where('join_family_creator_member_id', $id)->get()->toArray();
        $familyMemberIds = '';
        if ($family) {
            $familyIds = array_column($family, 'family_id');
            $familyMember = FamilyMember::whereIn('join_family_id', $familyIds)->get()->toArray();
            $familyMemberIds = array_column($familyMember, 'join_family_member_id');
        }

        $rows = MemberDevice::leftJoin('device', 'device.device_id', '=', 'member_device.join_member_device_id')
            ->leftJoin('member', 'member.member_id', '=', 'member_device.join_device_member_id')
            ->leftJoin('family_member', 'family_member.join_family_member_id', '=', 'member_device.join_device_member_id')
            ->select('device.device_category', 'device.device_name', 'device.device_type', 'member.member_mobile', 'member_device.*', 'family_member.family_member_name')
            ->when(!empty($familyMemberIds), function ($query) use ($familyMemberIds) {
                $query->whereIn('member_device.join_device_master_member_id', $familyMemberIds);
            })
            ->when(empty($familyMemberIds), function ($query) use ($id) {
                $query->where('member_device.join_device_master_member_id', $id);
            })->orderBy('member_device_addtimes', 'desc')
            ->get()
            ->toArray();

        return json_success('', ['rows' => $rows]);
    }

    /**
     * @Desc 绑定设备
     * @Author Gorden
     * @Date 2024/4/7 10:27
     *
     * @param Request $request
     * @return \support\Response
     */
    public static function addDevice(Request $request)
    {
        $params = $request->post();
        Db::beginTransaction();
        try {
            $data = [
                'join_device_member_id' => $params['join_device_member_id'] ?? '',
                'join_device_master_member_id' => $params['join_device_master_member_id'],
                'join_member_device_id' => $params['join_member_device_id'],
                'member_device_status' => $params['member_device_status'],
                'member_device_key' => $params['member_device_key'] ?? '',
                'member_device_config_json' => !empty($params['member_device_config_json']) ? $params['member_device_config_json'] : null,
                'member_device_remark' => $params['member_device_remark'] ?? null,
                'member_device_addtimes' => time(),
            ];

            MemberDevice::insert($data);

            $device = Device::where('device_id', $params['join_member_device_id'])->whereIn('device_status', ['PROCESSING', 'PENDING'])->first();
            // 激活设备
            if (!$device) {
                Db::rollBack();
                return json_fail('当前设备不是可激活状态');
            }

            $device->device_status = 'ACTIVED';
            $device->save();

            Device::where('device_id', $params['join_member_device_id'])->update(['device_status' => 'ACTIVED']);

            _syslog("绑定设备", "用户【" . ($params['join_device_member_id'] ?? $params['join_device_master_member_id']) . '】，绑定设备【' . $device->device_name . '】');

            Db::commit();
        } catch (\Exception $e) {
            Db::rollBack();
            dump($e->getMessage());
            return json_fail('绑定失败');
        }

        return json_success('绑定成功');
    }

    public static function updateDevice(Request $request)
    {
        $params = $request->post();
        Db::beginTransaction();
        try {
            $data = [
                'member_device_status' => $params['member_device_status'],
                'member_device_key' => $params['member_device_key'],
                'join_device_member_id' => $params['join_device_member_id'],
                'member_device_config_json' => !empty($params['member_device_config_json']) ? $params['member_device_config_json'] : null,
                'member_device_remark' => $params['member_device_remark'] ?? null,
            ];

            MemberDevice::where('member_device_id', $request->post('member_device_id'))->update($data);

            $device = Device::where('device_id', $params['join_member_device_id'])->first();
            if ($device->device_status != $params['member_device_status']) {
                $device->device_status = $params['member_device_status'];
                $device->save();
            }

//            Device::where('device_id', $params['join_member_device_id'])->update(['device_status' => $params['member_device_status']]);

            Db::commit();

            _syslog("更新绑定设备", "用户【" . ($params['join_device_member_id'] == "" ? $params['join_device_member_id'] : $params['join_device_master_member_id']) . '】，绑定设备【' . $device->device_name . '】');

        } catch (\Exception $e) {
            Db::rollBack();
            dump($e->getMessage());
            return json_fail('绑定更新失败');
        }

        return json_success('绑定更新成功');
    }

    public static function deleteDevice(Request $request)
    {
        try {
            Db::beginTransaction();
            $memberDevice = MemberDevice::where('member_device_id', $request->post('member_device_id'))->first();
            if (!$memberDevice) {
                return json_fail('数据不存在');
            }

            $device = Device::where('device_id', $memberDevice->join_member_device_id)->first();
            $device->device_status = "DISABLED";
            $device->save();
//            Device::where('device_id', $memberDevice->join_member_device_id)->update(['device_status' => 'DISABLED']);

            $memberDevice->delete();

            Db::commit();

            _syslog("解除绑定设备", "用户【" . ($memberDevice->join_device_member_id == "" ? $memberDevice->join_device_member_id : $memberDevice->join_device_master_member_id) . '】，解除绑定设备【' . $device->device_name . '】');

            return json_success('解除绑定成功');
        } catch (\Exception $e) {
            Db::rollBack();

            return json_fail('绑定失败');
        }
    }

    /**
     * 手动创建会员
     */
    public static function createMember($params)
    {
        $memberId = $params['member_id'] ?? '';
        if (!$memberId) {
            throw new BusinessException("参数异常");
        }
        // 主表
        $memberData = [
            'member_id' => $memberId,
            'member_is_owner' => 'N',
            'member_classify' => 'MEMBER',
            'member_status' => 'ACTIVED',
            'member_mobile' => $params['mobile'],
            'member_invite_code' => self::generateInviteCode(),
            'member_extend_json' => json_encode(['come_from' => 'SYSTEM']),
            'member_addtimes' => time()
        ];
        Member::insert($memberData);
        // 账户表
        $accountData = [
            'join_account_member_id' => $memberId,
            'member_account_classify' => 'POINTS',
            'member_account_status' => 'ACTIVED',
            'member_account_category' => 'NORMAL',
            'member_account_nbr' => $memberId . '-POINTS',
            'member_account_name' => '积分账户',
            'member_account_addtimes' => time()
        ];
        // 积分账户
        MemberAccount::insert($accountData);
        $accountData['member_account_classify'] = 'CASH';
        $accountData['member_account_nbr'] = $memberId . '-CASH';
        $accountData['member_account_name'] = '余额账户';
        // 现金/余额账户
        MemberAccount::insert($accountData);
        // info
        $infoData = [
            'join_info_member_id' => $memberId,
            'member_info_nickname' => $params['username']
        ];
        MemberInfo::insert($infoData);
    }

    private static function generateMemberId()
    {
        $id = SysSerial::getSerial();

        return "MR" . date('ymdHi') . random_string(4, 'up');
    }

    public static function getMemberName($mobile, $certName, $nickname)
    {
        $name = '';
        if (!empty($certName)) {
            $name = $certName;
        } elseif (!empty($nickname)) {
            $name = $nickname;
        }
        if (!empty($name) && !empty($mobile)) {
            $name .= '-' . $mobile;
        } elseif (!empty($mobile)) {
            $name = $mobile;
        }

        return $name;
    }

    public static function getMemberNameByMemberId($memberId)
    {
        $member = Member::with([
                'cert' => function ($query) {
                    $query->select('join_cert_member_id', 'member_cert_name');
                },
                'info' => function ($query) {
                    $query->select('join_info_member_id', 'member_info_nickname');
                },
            ]
        )->where('member_id', $memberId)
            ->first();
        if (empty($member)) {
            return;
        }
        $member = $member->toArray();
        $mobile = $certName = $nickname = '';
        if (!empty($member['member_mobile'])) {
            $mobile = $member['member_mobile'];
        }
        if (!empty($member['cert']) && !empty($member['cert']['member_cert_name'])) {
            $certName = $member['cert']['member_cert_name'];
        }
        if (!empty($member['info']) && !empty($member['info']['member_info_nickname'])) {
            $nickname = $member['info']['member_info_nickname'];
        }

        return self::getMemberName($mobile, $certName, $nickname);
    }

    /**
     * @Desc 生成推荐码
     * @Author Gorden
     * @Date 2024/9/6 9:05
     *
     * @return string
     */
    public static function generateInviteCode()
    {
        $code = random_string(8, 'number');
        if (Member::where('member_invite_code', $code)->exists()) {
            return self::generateInviteCode();
        } else {
            return $code;
        }
    }
}