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

<?php

namespace app\admin\service\member;

use app\model\ClientPoints;
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;

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

        $rows = Member::with([
                'account' => function($query){
                    $query->where('member_account_status','ACTIVED');
                }
            ])
            ->where('member_is_owner', 'N')
            ->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{
                    $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);
            })
            ->select('member_id','member_is_vip', '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_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['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'] : ''
            ];
            $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'] = [];
            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');
                }
            }
        }

        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')
            ->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_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'] : ''
            ];
            $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']
            ];
        }
        return json_success('',$exportData);
    }


    public static function info($memberId)
    {
        $member = Member::with('cert','info')
            ->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['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['is_auth'] = "未认证";
        if (!empty($member['cert']['member_cert_nbr']) && !empty($member['cert']['member_cert_name'])) {
            $member['is_auth'] = "已认证";
        }

        $member['source'] = "APP";
        $member['belong'] = [];
        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');
            }
        }

        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();
        $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();
        $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'));
    }

    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();
        $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'])) {
                $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 ($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' => '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'));
    }

    public static function pointInfo($memberId)
    {
        $account = MemberAccount::where('join_account_member_id', $memberId)
            ->where('member_account_classify', 'POINTS')
            ->first();

        $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'] ?? 'ISABLED',
                'member_mobile' => $params['mobile'] ?? '',
//                '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']) : ''
            ];
            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) {
                return json_fail("暂无数据");
            }
            $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
            ];
            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 (\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_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 "ME" . str_pad($id, 16, "0", STR_PAD_LEFT) . random_string(6, 'up');
    }
}