wanyue_app/app/admin/controller/member/MemberController.php

<?php

namespace app\admin\controller\member;

use app\admin\service\member\MemberService;
use app\admin\service\notify\RechargeService;
use app\admin\service\order\OrderService;
use app\admin\validate\member\MemberValidate;
use app\model\Coupon;
use app\model\CouponDetail;
use app\model\Family;
use app\model\FamilyMember;
use app\model\Member;
use app\model\MemberAccount;
use app\model\MemberBenefit;
use app\model\MemberQuota;
use app\model\MemberRole;
use app\model\Order;
use app\model\OrderReturn;
use app\model\OrderSheet;
use app\model\PayDetail;
use support\Db;
use support\exception\BusinessException;
use support\Log;
use support\Request;
use Tinywan\Jwt\JwtToken;
use function _PHPStan_cc8d35ffb\RingCentral\Psr7\str;

class MemberController
{
    public function selectList(Request $request)
    {
        $keywords = $request->get('keywords');
        $isOwner = $request->get('member_is_owner', 'N');
        if (!$keywords) {
            return json_fail("查询错误");
        }

        $res = Db::table('member')
            ->leftjoin('member_cert', 'member.member_id', '=', 'member_cert.join_cert_member_id')
            ->leftjoin('member_info', 'member.member_id', '=', 'member_info.join_info_member_id')
            ->select('member.member_id', 'member.member_mobile', 'member_cert.member_cert_name', 'member_info.member_info_nickname')
            ->where('member.member_is_owner', $isOwner)
            ->where('member_status', 'ACTIVED')
            // ->where('member_mobile','<>','0000')
            ->where(function ($query) use ($keywords) {
                $query->where('member.member_mobile', 'like', '%' . $keywords . '%')
                    ->orWhere("member.member_id", 'like', '%' . $keywords . '%')
                    ->orWhere("member_cert.member_cert_name", 'like', '%' . $keywords . '%')
                    ->orWhere("member_info.member_info_nickname", 'like', '%' . $keywords . '%');
            })->get();
        $data = [];
        foreach ($res as $item) {
            // $item->member_mobile = substr($item->member_mobile,0,3).'****'.substr($item->member_mobile,7);
            $name = '';
            if (!empty($item->member_cert_name)) {
                $name = $item->member_cert_name;
            } else if (!empty($item->member_info_nickname)) {
                $name = $item->member_info_nickname;
            }
            $value = !empty($name) ? $name . '-' : '';
            if ($item->member_mobile != '0000') {
                $value .= $item->member_mobile;
            }
            if (empty($value)) {
                $value = $item->member_mobile;
            }
            $value = rtrim($value, '-');
            $data[] = [
                'key' => $item->member_id,
                'value' => $value,
            ];
        }

        return json_success('', $data);
    }

    public function exportMember(Request $request)
    {

        return MemberService::exportMember($request);
    }

    public function selectWriteOffMember()
    {
        $members = Member::with([
            'cert' => function ($query) {
                $query->select('join_cert_member_id', 'member_cert_name');
            }
        ])->whereRaw("JSON_EXTRACT(`member_json`, '$.user') IS NOT NULL")
            ->select('member_id', 'member_mobile', 'member_json')
            ->get()
            ->toArray();
        foreach ($members as &$member) {
            // $member['member_mobile'] = substr($member['member_mobile'],0,3).'****'.substr($member['member_mobile'],7);
            $member['member_json'] = json_decode($member['member_json'], true);
            $member['username'] = $member['member_mobile'];
            $member['user_id'] = $member['member_json']['user'] ? $member['member_json']['user']['user_id'] : '';
            if (isset($member['cert']['member_cert_name'])) {
                $member['username'] = $member['cert']['member_cert_name'] . '-' . $member['username'];
            }
        }

        return json_success('', $members);
    }

    public function selectFamilyMemberList(Request $request)
    {
        $keywords = $request->get('keywords');
        if (!$keywords) {
            return json_fail("查询错误");
        }
        // 创建的家庭
        $family = Family::where('join_family_creator_member_id', $request->get('memberId'))->get()->toArray();
        if (!$family) {
            return json_success('暂无数据');
        }
        $familyIds = array_column($family, 'family_id');

        $res = FamilyMember::whereIn('join_family_id', $familyIds)
            ->where("family_member_name", 'like', '%' . $keywords . '%')
            ->get()
            ->toArray();
        $data = [];
        foreach ($res as $item) {
            $data[] = [
                'key' => $item['join_family_member_id'],
                'value' => $item['family_member_name']
            ];
        }

        return json_success('', $data);
    }

    public function selectMemberAllQuota(Request $request)
    {
        $memberId = $request->get('member_id', '');
        $orderId = $request->get('order_id', '');
        $goodsId = $request->get('goods_id', '');
        $person = $request->get('person', '');
        $selectType = $request->get('select_type', 'ALL');

        if (!$memberId) {
            return json_fail('参数异常');
        }
        $data = [];

        $benefit = MemberBenefit::where('join_benefit_member_id', $memberId)
            ->whereColumn('member_benefit_limit_count', '>', 'member_benefit_used_count')
            ->select('member_benefit_id as id', 'member_benefit_name as name', 'member_benefit_limit_count as total', 'member_benefit_used_count as used')
            ->when($orderId != '', function ($query) use ($orderId) {
                $query->where('join_benefit_order_id', $orderId);
            })->when($goodsId != '', function ($query) use ($goodsId) {
                $query->where('join_benefit_goods_id', $goodsId);
            })
            ->get()
            ->toArray();

        foreach ($benefit as $item) {
            if ($item['total'] - $item['used'] < $person) {
                $item['disabled'] = true;
            }
            $item['cut'] = $item['total'] - $item['used'];
            $data[] = $item;
        }
        if ($selectType == 'BENEFIT') {
            return json_success('', $data);
        }

        $quotas = MemberQuota::where('join_quota_member_id', $memberId)
            ->when($goodsId != '', function ($query) use ($goodsId) {
                $query->whereJsonContains('member_quota_json', ['goods_id' => $goodsId]);
            })
            ->selectRaw('join_quota_member_id,join_member_rule_added_component_id,COUNT(*) as total')
            ->groupBy('join_quota_member_id', 'join_member_rule_added_component_id')
            ->get()
            ->toArray();
        foreach ($quotas as $quota) {
            $usedNum = MemberQuota::where('join_quota_member_id', $quota['join_quota_member_id'])
                ->where('join_member_rule_added_component_id', $quota['join_member_rule_added_component_id'])
                ->where('member_quota_status', 'USED')
                ->count('*');
            if ($quota['total'] <= $usedNum) {
                continue;
            }
            $memberQuota = MemberQuota::where('join_quota_member_id', $quota['join_quota_member_id'])
                ->where('join_member_rule_added_component_id', $quota['join_member_rule_added_component_id'])
                ->select('member_quota_id as id', 'member_quota_name as name')
                ->first()
                ->toArray();

            $memberQuota['total'] = $quota['total'];
            $memberQuota['used'] = $usedNum;
            if ($memberQuota['total'] - $memberQuota['used'] < $person) {
                $memberQuota['disabled'] = true;
            }
            $memberQuota['cut'] = $memberQuota['total'] - $memberQuota['used'];
            $data[] = $memberQuota;
        }

        if ($selectType == 'QUOTA') {
            return json_success('', $data);
        }

        return json_success('', $data);
    }

    /**
     * 预约核销用
     */
    public function selectMemberAllQuotaByAppointment(Request $request)
    {
        $memberId = $request->get('member_id', '');
        $orderId = $request->get('order_id', '');
        $goodsId = $request->get('goods_id', '');
        $person = $request->get('person', '');
        $selectType = $request->get('select_type', 'ALL');

        if (!$memberId) {
            return json_fail('参数异常');
        }
        $data = [];

        $benefit = MemberBenefit::where('join_benefit_member_id', $memberId)
            ->whereColumn('member_benefit_limit_count', '>', 'member_benefit_used_count')
            ->select('member_benefit_id as id', 'member_benefit_name as name', 'member_benefit_limit_count as total', 'member_benefit_used_count as used')
            ->when($orderId != '', function ($query) use ($orderId) {
                $query->where('join_benefit_order_id', $orderId);
            })->when($goodsId != '', function ($query) use ($goodsId) {
                $query->where('join_benefit_goods_id', $goodsId);
            })
            ->get()
            ->toArray();
        $data['benefit'] = [];
        foreach ($benefit as $item) {
            $item['benefit']['cut'] = $item['total'] - $item['used'];
            $data['benefit']['name'] = $item['name'];
            $data['benefit']['cut'] += $item['cut'];
            $data['benefit']['id'][] = $item['id'];
        }
        if ($selectType == 'BENEFIT') {
            return json_success('', $data);
        }

        $quotas = MemberQuota::where('join_quota_member_id', $memberId)
            ->when($goodsId != '', function ($query) use ($goodsId) {
                $query->whereJsonContains('member_quota_json', ['goods_id' => $goodsId]);
            })
            ->selectRaw('join_quota_member_id,join_member_rule_added_component_id,COUNT(*) as total')
            ->groupBy('join_quota_member_id', 'join_member_rule_added_component_id')
            ->get()
            ->toArray();

        $data['quota'] = [];
        foreach ($quotas as $quota) {
            $usedNum = MemberQuota::where('join_quota_member_id', $quota['join_quota_member_id'])
                ->where('join_member_rule_added_component_id', $quota['join_member_rule_added_component_id'])
                ->where('member_quota_status', 'USED')
                ->count('*');
            if ($quota['total'] <= $usedNum) {
                continue;
            }
            $memberQuota = MemberQuota::where('join_quota_member_id', $quota['join_quota_member_id'])
                ->where('join_member_rule_added_component_id', $quota['join_member_rule_added_component_id'])
                ->select('member_quota_id as id', 'member_quota_name as name')
                ->first()
                ->toArray();

            $memberQuota['total'] = $quota['total'];
            $memberQuota['used'] = $usedNum;
            if ($memberQuota['total'] - $memberQuota['used'] < $person) {
                $memberQuota['disabled'] = true;
            }
            $memberQuota['cut'] = $memberQuota['total'] - $memberQuota['used'];

            $data['quota']['name'] = $memberQuota['name'];
            $data['quota']['cut'] += $memberQuota['cut'];
            $data['quota']['id'][] = $memberQuota['id'];
            // $data[] = $memberQuota;
        }

        if ($selectType == 'QUOTA') {
            return json_success('', $data);
        }

        return json_success('', $data);
    }

    public function selectMemberAllQuotaByGoods(Request $request)
    {
        $memberId = $request->get('member_id', '');
        $orderId = $request->get('order_id', '');
        $person = $request->get('person', '');
        $selectType = $request->get('select_type', 'ALL');

        if (!$memberId) {
            return json_fail('参数异常');
        }
        $data = [];

        $benefit = MemberBenefit::where('join_benefit_member_id', $memberId)
            // ->whereColumn('member_benefit_limit_count','>','member_benefit_used_count')
            ->select('member_benefit_id as id', 'member_benefit_name as name', 'member_benefit_limit_count as total', 'member_benefit_used_count as used')
            ->when($orderId != '', function ($query) use ($orderId) {
                $query->where('join_benefit_order_id', $orderId);
            })
            ->get()
            ->toArray();


        foreach ($benefit as $item) {
            if ($item['total'] - $item['used'] < $person) {
                $item['disabled'] = true;
            }
            $item['cut'] = $item['total'] - $item['used'];
            $data[] = $item;
        }
        if ($selectType == 'BENEFIT') {
            return json_success('', $data);
        }

        $quotas = MemberQuota::where('join_quota_member_id', $memberId)
            ->selectRaw('join_quota_member_id,join_member_rule_added_component_id,COUNT(*) as total')
            ->groupBy('join_quota_member_id', 'join_member_rule_added_component_id')
            ->get()
            ->toArray();
        foreach ($quotas as $quota) {
            $usedNum = MemberQuota::where('join_quota_member_id', $quota['join_quota_member_id'])
                ->where('join_member_rule_added_component_id', $quota['join_member_rule_added_component_id'])
                ->where('member_quota_status', 'USED')
                ->count('*');
            if ($quota['total'] <= $usedNum) {
                continue;
            }
            $memberQuota = MemberQuota::where('join_quota_member_id', $quota['join_quota_member_id'])
                ->where('join_member_rule_added_component_id', $quota['join_member_rule_added_component_id'])
                ->select('member_quota_id as id', 'member_quota_name as name')
                ->first()
                ->toArray();

            $memberQuota['total'] = $quota['total'];
            $memberQuota['used'] = $usedNum;
            if ($memberQuota['total'] - $memberQuota['used'] < $person) {
                $memberQuota['disabled'] = true;
            }
            $memberQuota['cut'] = $memberQuota['total'] - $memberQuota['used'];
            $data[] = $memberQuota;
        }

        if ($selectType == 'QUOTA') {
            return json_success('', $data);
        }

        return json_success('', $data);
    }

    public function list(Request $request)
    {
        return MemberService::list($request);
    }

    public function info(Request $request)
    {
        $memberId = $request->get('member_id', '');
        if (!$memberId) {
            return json_fail("参数错误");
        }
        return MemberService::info($memberId);
    }

    /**
     * @Desc 会员统计
     * @Author Gorden
     * @Date 2024/10/21 16:31
     *
     * @return \support\Response
     */
    public function statistics()
    {
        $todayStart = strtotime(date('Y-m-d'));
        $todayEnd = strtotime(date('Y-m-d 23:59:59'));
        $todayCount = Member::where('member_addtimes' ,'>=', $todayStart)->where('member_addtimes', '<=', $todayEnd)->count();

        $yesterdayStart = strtotime(date('Y-m-d', strtotime("-1 day")));
        $yesterdayEnd = strtotime(date('Y-m-d 23:59:59', strtotime("-1 day")));
        $yesterdayCount = Member::where('member_addtimes' ,'>=', $yesterdayStart)->where('member_addtimes', '<=', $yesterdayEnd)->count();

        $monthStart = strtotime(date('Y-m-01'));
        $monthEnd = strtotime(date('Y-m-t 23:59:59'));
        $monthCount = Member::where('member_addtimes', '>=', $monthStart)->where('member_addtimes', '<=', $monthEnd)->count();

        $lastMonthStart = strtotime(date('Y-m-01',strtotime("last day of -1 month")));
        $lastMonthEnd = strtotime(date('Y-m-t 23:59:59',strtotime("last day of -1 month")));
        $lastMonthCount = Member::where('member_addtimes', '>=', $lastMonthStart)->where('member_addtimes', '<=', $lastMonthEnd)->count();

        return json_success('',[
            'todayCount'=>$todayCount,
            'yesterdayCount' => $yesterdayCount,
            'monthCount' => $monthCount,
            'lastMonthCount' => $lastMonthCount
        ]);
    }

    public function vipInfo(Request $request)
    {
        $memberId = $request->get('member_id', '');
        if (!$memberId) {
            return json_fail("参数错误");
        }

        return MemberService::vipInfo($request);
    }

    public function welfareInfo(Request $request)
    {
        $memberId = $request->get('member_id');
        if (!$memberId) {
            return json_fail("参数错误");
        }

        return MemberService::welfareInfo($request);
    }

    public function commissionList(Request $request)
    {
        $memberId = $request->get('member_id');
        if (!$memberId) {
            return json_fail("参数错误");
        }

        return MemberService::commissionList($request);
    }

    /**
     * @Desc 我的粉丝列表
     * @Author Gorden
     * @Date 2024/9/20 10:22
     *
     * @param Request $request
     * @return \support\Response
     */
    public function fansList(Request $request)
    {
        $memberId = $request->get('member_id');
        if (!$memberId) {
            return json_fail("参数错误");
        }

        return MemberService::fansList($request);
    }

    public function clearWelfare(Request $request)
    {
        $memberId = $request->post('member_id', '');
        if (!$memberId) {
            return json_fail("参数错误");
        }

        return MemberService::clearWelfare($request);
    }

    public function balanceInfo(Request $request)
    {
        $memberId = $request->get('member_id', '');
        if (!$memberId) {
            return json_fail("参数错误");
        }

        return MemberService::balanceInfo($request);
    }

    public function pointInfo(Request $request)
    {
        $memberId = $request->get('member_id', '');
        if (!$memberId) {
            return json_fail("参数错误");
        }

        return MemberService::pointInfo($memberId);
    }

    /**
     * @Desc 添加会员
     * @Author Gorden
     * @Date 2024/3/25 13:40
     *
     * @param Request $request
     * @return \support\Response
     */
    public function add(Request $request)
    {
        $validate = new MemberValidate();
        if (!$validate->scene('add')->check($request->post())) {
            return json_fail($validate->getError());
        }

        _syslog("添加用户", "用户【" . $request->post('member_cert_name') . '】');

        return MemberService::add($request->post());
    }

    public function update(Request $request)
    {
        $validate = new MemberValidate();
        if (!$validate->scene('update')->check($request->post())) {
            return json_fail($validate->getError());
        }

        _syslog("编辑用户", "用户【" . $request->post('member_cert_name') ?? $request->post('member_id') . '】');

        return MemberService::update($request->post());
    }

    /**
     * @Desc 绑定的设备
     * @Author Gorden
     * @Date 2024/4/7 10:20
     *
     * @param $id
     * @return \support\Response
     */
    public function device($id)
    {
        return MemberService::deviceList($id);
    }

    public function addDevice(Request $request)
    {
        return MemberService::addDevice($request);
    }

    public function updateDevice(Request $request)
    {
        return MemberService::updateDevice($request);
    }

    public function deleteDevice(Request $request)
    {
        return MemberService::deleteDevice($request);
    }

    /**
     * @Desc 卡券数量
     * @Author Gorden
     * @Date 2024/8/22 18:51
     *
     * @param Request $request
     * @return \support\Response
     */
    public function couponCount(Request $request)
    {
        $memberId = $request->get('member_id', '');
        $coupons = CouponDetail::leftJoin('coupon', 'coupon.coupon_id', '=', 'coupon_detail.join_detail_coupon_id')
            ->where('join_coupon_detail_member_id', $memberId)
            ->select('coupon.coupon_name', 'coupon.coupon_classify', 'coupon.coupon_value')
            ->get()
            ->toArray();
        $data = ['manjian' => 0, 'diyong' => 0, 'zhekou' => 0, 'lijian' => 0, 'zengpin' => 0, 'fuli' => 0, 'nianka' => 0, 'jika' => 0, 'yueka' => 0];
        foreach ($coupons as $item) {
            if ($item['coupon_classify'] == '满减券') {
                $data['manjian'] += 1;
            } elseif ($item['coupon_classify'] == '抵用券') {
                $data['diyong'] += 1;
            } elseif ($item['coupon_classify'] == '折扣券') {
                $data['zhekou'] += 1;
            } elseif ($item['coupon_classify'] == '立减券') {
                $data['lijian'] += 1;
            } elseif ($item['coupon_classify'] == '赠品券') {
                $data['zengpin'] += 1;
            } elseif ($item['coupon_classify'] == '福利券') {
                $data['fuli'] += 1;
            } elseif ($item['coupon_classify'] == '年卡') {
                $data['nianka'] += 1;
            } elseif ($item['coupon_classify'] == '季卡') {
                $data['jika'] += 1;
            } elseif ($item['coupon_classify'] == '月卡') {
                $data['yueka'] += 1;
            }
        }
        $data['total'] = $data['manjian'] + $data['diyong'] + $data['zhekou'] + $data['lijian'] + $data['zengpin'] + $data['fuli'] + $data['nianka'] + $data['jika'] + $data['yueka'];

        return json_success('', $data);
    }

    /**
     * @Desc 我的卡券
     * @Author Gorden
     * @Date 2024/8/22 18:45
     *
     * @param Request $request
     * @return \support\Response
     */
    public function coupon(Request $request)
    {
        $memberId = $request->get('member_id', '');
        $classify = $request->get('classify', '');
        $page = $request->get('page', 1);
        $pageSize = $request->get('pageSize', 20);

        $coupons = CouponDetail::with([
            'member' => function ($query) {
                $query->select('member_id', 'member_mobile');
            },
            'cert' => function ($query) {
                $query->select('join_cert_member_id', 'member_cert_name');
            },
            'info' => function ($query) {
                $query->select('join_info_member_id', 'member_info_nickname');
            }
        ])
            ->leftJoin('coupon', 'coupon.coupon_id', '=', 'coupon_detail.join_detail_coupon_id')
            ->where('join_coupon_detail_member_id', $memberId)
            ->where('coupon.coupon_classify', $classify)
            ->groupBy('join_coupon_detail_member_id', 'join_detail_coupon_id');
//        $total = $coupons->count();
        $totalModel = Db::select("select count(a.join_detail_coupon_id) as total from (select join_detail_coupon_id from app_coupon_detail as d left join app_coupon as c ON d.join_detail_coupon_id = c.coupon_id where d.join_coupon_detail_member_id='" . $memberId . "' and c.coupon_classify='" . $classify . "' group by d.join_coupon_detail_member_id,d.join_detail_coupon_id) as a");
        $total = 0;
        if (!empty($totalModel) && !empty($totalModel[0])) {
            $total = $totalModel[0]->total;
        }
        $rows = $coupons->select('coupon_detail.join_coupon_detail_member_id', 'coupon_detail.join_detail_coupon_id'
            , 'coupon.coupon_name', 'coupon.coupon_classify', 'coupon.coupon_value')
            ->selectRaw('COUNT(1) as total, 
                COUNT(IF(app_coupon_detail.coupon_detail_status="USED",1,NULL)) as used_total, 
                COUNT(IF(app_coupon_detail.coupon_detail_status="ACTIVED" or app_coupon_detail.coupon_detail_status="WAITING",1,NULL)) as unused_total,
                MAX(app_coupon_detail.coupon_detail_deadline_datetime) as deadline_datetime')
            ->orderBy('deadline_datetime', 'DESC')
            ->forPage($page, $pageSize)
            ->get()
            ->toArray();

        foreach ($rows as &$item) {
            $mobile = $certName = $nickname = '';
            if (!empty($item['member']) && !empty($item['member']['member_mobile'])) {
                $mobile = $item['member']['member_mobile'];
            }
            if (!empty($item['cert']) && !empty($item['cert']['member_cert_name'])) {
                $certName = $item['cert']['member_cert_name'];
            }
            if (!empty($item['info']) && !empty($item['info']['member_info_nickname'])) {
                $nickname = $item['info']['member_info_nickname'];
            }
            $item['member_name'] = MemberService::getMemberName($mobile, $certName, $nickname);
            unset($item['member'], $item['cert'], $item['info']);

            $item['invalid_total'] = $item['total'] - $item['used_total'] - $item['unused_total'];
            // 优惠券分类
            $coupon = Coupon::with('category')->where('coupon_id', $item['join_detail_coupon_id'])
                ->select('coupon_id', 'join_coupon_category_id')
                ->first();
            if (!empty($coupon) && !empty($coupon->category)) {
                $item['category_name'] = $coupon->category->category_name;
            }
        }

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

    /**
     * @Desc 我的卡券
     * @Author Gorden
     * @Date 2024/8/22 18:45
     *
     * @param Request $request
     * @return \support\Response
     */
    public function allCoupon(Request $request)
    {
        $memberId = $request->get('member_id', '');
        $page = $request->get('page', 1);
        $pageSize = $request->get('pageSize', 20);

        $coupons = CouponDetail::with([
            'member' => function ($query) {
                $query->select('member_id', 'member_mobile');
            },
            'cert' => function ($query) {
                $query->select('join_cert_member_id', 'member_cert_name');
            },
            'info' => function ($query) {
                $query->select('join_info_member_id', 'member_info_nickname');
            }
        ])
            ->leftJoin('coupon', 'coupon.coupon_id', '=', 'coupon_detail.join_detail_coupon_id')
            ->where('join_coupon_detail_member_id', $memberId)
            ->groupBy('join_coupon_detail_member_id', 'join_detail_coupon_id');
//        $total = $coupons->count();
        $totalModel = Db::select("select count(a.join_detail_coupon_id) as total from (select join_detail_coupon_id from app_coupon_detail as d left join app_coupon as c ON d.join_detail_coupon_id = c.coupon_id where d.join_coupon_detail_member_id='" . $memberId . "' group by d.join_coupon_detail_member_id,d.join_detail_coupon_id) as a");
        $total = 0;
        if (!empty($totalModel) && !empty($totalModel[0])) {
            $total = $totalModel[0]->total;
        }
        $rows = $coupons->select('coupon_detail.join_coupon_detail_member_id', 'coupon_detail.join_detail_coupon_id'
            , 'coupon.coupon_name', 'coupon.coupon_classify', 'coupon.coupon_value')
            ->selectRaw('COUNT(1) as total, 
                COUNT(IF(app_coupon_detail.coupon_detail_status="USED",1,NULL)) as used_total, 
                COUNT(IF(app_coupon_detail.coupon_detail_status="ACTIVED" or app_coupon_detail.coupon_detail_status="WAITING",1,NULL)) as unused_total,
                MAX(app_coupon_detail.coupon_detail_deadline_datetime) as deadline_datetime')
            ->orderBy('deadline_datetime', 'DESC')
            ->forPage($page, $pageSize)
            ->get()
            ->toArray();

        foreach ($rows as &$item) {
            $mobile = $certName = $nickname = '';
            if (!empty($item['member']) && !empty($item['member']['member_mobile'])) {
                $mobile = $item['member']['member_mobile'];
            }
            if (!empty($item['cert']) && !empty($item['cert']['member_cert_name'])) {
                $certName = $item['cert']['member_cert_name'];
            }
            if (!empty($item['info']) && !empty($item['info']['member_info_nickname'])) {
                $nickname = $item['info']['member_info_nickname'];
            }
            $item['member_name'] = MemberService::getMemberName($mobile, $certName, $nickname);
            unset($item['member'], $item['cert'], $item['info']);

            $item['invalid_total'] = $item['total'] - $item['used_total'] - $item['unused_total'];
            // 优惠券分类
            $coupon = Coupon::with('category')->where('coupon_id', $item['join_detail_coupon_id'])
                ->select('coupon_id', 'join_coupon_category_id')
                ->first();
            if (!empty($coupon) && !empty($coupon->category)) {
                $item['category_name'] = $coupon->category->category_name;
            }
        }

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

    /**
     * @Desc 我的卡券 领取记录
     * @Author Gorden
     * @Date 2024/8/22 18:45
     *
     * @param Request $request
     * @return \support\Response
     */
    public function couponDetail(Request $request)
    {
        $memberId = $request->get('member_id', '');
        $couponId = $request->get('coupon_id', '');
        $page = $request->get('page', 1);
        $pageSize = $request->get('pageSize', 20);

        $coupons = CouponDetail::with([
            'member' => function ($query) {
                $query->select('member_id', 'member_mobile');
            },
            'cert' => function ($query) {
                $query->select('join_cert_member_id', 'member_cert_name');
            },
            'info' => function ($query) {
                $query->select('join_info_member_id', 'member_info_nickname');
            }
        ])
            ->leftJoin('coupon', 'coupon.coupon_id', '=', 'coupon_detail.join_detail_coupon_id')
            ->where('join_coupon_detail_member_id', $memberId)
            ->where('coupon.coupon_id', $couponId);
        $total = $coupons->count();
        $rows = $coupons->select('coupon_detail.*', 'coupon.coupon_name', 'coupon.coupon_classify', 'coupon.coupon_value')
            ->orderBy('coupon_detail_deadline_datetime', 'desc')
            ->forPage($page, $pageSize)
            ->get()
            ->toArray();

        foreach ($rows as &$item) {
            $mobile = $certName = $nickname = '';
            if (!empty($item['member']) && !empty($item['member']['member_mobile'])) {
                $mobile = $item['member']['member_mobile'];
            }
            if (!empty($item['cert']) && !empty($item['cert']['member_cert_name'])) {
                $certName = $item['cert']['member_cert_name'];
            }
            if (!empty($item['info']) && !empty($item['info']['member_info_nickname'])) {
                $nickname = $item['info']['member_info_nickname'];
            }
            $item['member_name'] = MemberService::getMemberName($mobile, $certName, $nickname);
            unset($item['member'], $item['cert'], $item['info']);
            if (!empty($coupon) && !empty($coupon->category)) {
                $item['category_name'] = $coupon->category->category_name;
            }
        }

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

    /**
     * @Desc 新会员充值备份记录
     * @Author Gorden
     * @Date 2024/10/15 10:27
     *
     * @param Request $request
     * @return \support\Response
     */
    public function rechargeBackupsList(Request $request)
    {
        $page = $request->get('page', 1);
        $pageSize = $request->get('pageSize', 20);
        $memberId = $request->get('member_id');
        if (!$memberId) {
            return json_fail("参数异常");
        }

        $member = Member::where('member_id', $memberId)
            ->select('member_id', 'member_extend_json')
            ->first();
        $data = [];
        if (!empty($member->member_extend_json)) {
            $extendJson = json_decode($member->member_extend_json, true);
            if (isset($extendJson['snap'])) {
                foreach ($extendJson['snap'] as $key => $item) {
                    $data[] = [
                        'recharge_times' => $key,
                        'member_account_surplus' => $item['member_account_surplus'],
                        'member_account_added' => $item['member_account_added'],
                        'pay_amount' => $item['pay_amount'],
                        'added_rate' => floatval($item['added_rate']) * 100,
                        'added_amount' => $item['added_amount']
                    ];
                }
            }
        }

        $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/10/15 11:13
     *
     * @param Request $request
     * @return \support\Response
     */
    public function roleBenefitUsedList(Request $request)
    {
        $page = $request->get('page', 1);
        $pageSize = $request->get('pageSize', 20);
        $memberId = $request->get('member_id');
        if (!$memberId) {
            return json_fail('参数异常');
        }

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

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

    public function consumptionList(Request $request)
    {
        $page = $request->get('page', 1);
        $pageSize = $request->get('pageSize', 20);
        $memberId = $request->get('member_id');
        if (!$memberId) {
            return json_fail('参数异常');
        }

        $member = Member::where('member_id', $memberId)->first();
        $firstRechargeTime = 0;
        if (!empty($member->member_extend_json)) {
            $memberExtendJson = json_decode($member->member_extend_json, true);
            if (isset($memberExtendJson['snap'])) {
                foreach ($memberExtendJson['snap'] as $key => $value) {
                    $firstRechargeTime = strtotime($key);
                    break;
                }
            }
        }

        $payDetailIds = PayDetail::where('pay_addtimes', '>', $firstRechargeTime)
            ->where('pay_prepayid', $memberId . '-CASH')
            ->where('pay_status', 'SUCCESS')
            ->pluck('join_pay_order_id')
            ->toArray();

        $orders = Order::with([
            'payDetail' => function ($query) {
                $query->where('pay_prepayid', 'like', '%CASH%');
            }
        ])->whereIn('order_groupby', $payDetailIds)
            ->where('order_addtimes', '>', $firstRechargeTime)
            ->whereIn('order_classify', ['GOODS', 'SERVICE', 'CHNMED', 'CHNNCD', 'PACKAGE', 'DISHES']);
        $total = $orders->count();
        $rows = $orders->orderBy('order_addtimes', 'DESC')
            ->forPage($page, $pageSize)
            ->get()
            ->toArray();

        $data = [];
        foreach ($rows as $row) {
            $sheets = OrderSheet::with([
                'goods' => function ($query) {
                    $query->select('goods_id', 'goods_name');
                }
            ])->where('join_sheet_order_id', $row['order_id'])
                ->get()
                ->toArray();
            $deng = "";
            if (count($sheets) > 1) {
                $deng = '等';
            }
            foreach ($row['pay_detail'] as $item) {
                if (!empty($item['join_pay_object_json'])) {
                    $payObjectJson = json_decode($item['join_pay_object_json'], true);
                    if ($payObjectJson['order_id'] != $row['order_id']) {
                        continue;
                    }
                }
                $data[] = [
                    'order_classify' => $row['order_classify'],
                    'order_id' => $row['order_id'],
                    'goods_name' => !empty($sheets[0]) && !empty($sheets[0]['goods']) ? ($sheets[0]['goods']['goods_name'] . $deng) : '',
                    'order_amount_total' => $row['order_amount_total'],
                    'order_amount_pay' => $row['order_amount_pay'],
                    'pay_paytimes' => $item['pay_paytimes'] ?? "",
                    'order_addtimes' => $row['order_addtimes'],
                    'pay_amount' => $item['pay_amount']
                ];
            }

        }
        $rows = $data;

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

    /**
     * @Desc 消费导出
     * @Author Gorden
     * @Date 2024/10/15 17:36
     *
     * @param Request $request
     * @return \support\Response
     */
    public function exportConsumptionList(Request $request)
    {
        $memberId = $request->post('member_id');
        $orderIds = $request->post('order_id', []);
        if (!$memberId) {
            return json_fail('参数异常');
        }
        $member = Member::where('member_id', $memberId)->first();
        $firstRechargeTime = 0;
        if (!empty($member->member_extend_json)) {
            $memberExtendJson = json_decode($member->member_extend_json, true);
            if (isset($memberExtendJson['snap'])) {
                foreach ($memberExtendJson['snap'] as $key => $value) {
                    $firstRechargeTime = strtotime($key);
                    break;
                }
            }
        }

        if (empty($orderIds)) {
            $payDetailIds = PayDetail::where('pay_addtimes', '>', $firstRechargeTime)
                ->where('pay_prepayid', $memberId . '-CASH')
                ->where('pay_status', 'SUCCESS')
                ->pluck('join_pay_order_id')
                ->toArray();
        } else {
            $payDetailIds = Order::whereIn('order_id', $orderIds)->pluck('order_groupby')->toArray();
        }

        $rows = Order::with([
            'payDetail' => function ($query) {
                $query->where('pay_prepayid', 'like', '%CASH%');
            }
        ])->whereIn('order_groupby', $payDetailIds)
            ->where('order_addtimes', '>', $firstRechargeTime)
            ->whereIn('order_classify', ['GOODS', 'SERVICE', 'CHNMED', 'CHNNCD', 'PACKAGE', 'DISHES'])
            ->orderBy('order_addtimes', 'DESC')
            ->get()
            ->toArray();

        $data = [];
        $payAmount = 0;
        foreach ($rows as $row) {
            $sheets = OrderSheet::with([
                'goods' => function ($query) {
                    $query->select('goods_id', 'goods_name');
                }
            ])->where('join_sheet_order_id', $row['order_id'])
                ->get()
                ->toArray();
            $deng = "";
            if (count($sheets) > 1) {
                $deng = '等';
            }
            foreach ($row['pay_detail'] as $item) {
                if (!empty($item['join_pay_object_json'])) {
                    $payObjectJson = json_decode($item['join_pay_object_json'], true);
                    if ($payObjectJson['order_id'] != $row['order_id']) {
                        continue;
                    }
                }
                $payAmount += $item['pay_amount'];
                $data[] = [
                    'order_classify' => OrderService::$orderClassify[$row['order_classify']],
                    'order_id' => $row['order_id'],
                    'goods_name' => !empty($sheets[0]) && !empty($sheets[0]['goods']) ? ($sheets[0]['goods']['goods_name'] . $deng) : '',
                    'order_amount_total' => $row['order_amount_total'],
                    'order_amount_pay' => $row['order_amount_pay'],
                    'pay_paytimes' => $item['pay_paytimes'] ?? "",
                    'order_addtimes' => $row['order_addtimes'],
                    'pay_amount' => $item['pay_amount']
                ];
            }
        }

        $data[] = ['pay_amount' => "总计：" . sprintf('%.2f', $payAmount)];

        return json_success('success', $data);
    }

    /**
     * @Desc 获取权益使用记录
     * @Author Gorden
     * @Date 2024/10/15 11:35
     *
     * @param $memberId
     * @param $orderIds
     * @return array
     */
    public function getRoleBenefitUsedList($memberId, $orderIds = [])
    {
        $couponDetails = CouponDetail::where('join_coupon_detail_member_id', $memberId)
            ->where('coupon_detail_status', 'USED')
            ->whereJsonContains('coupon_detail_extend_json->gettype', 'ROLE')
            ->get()
            ->toArray();
        $data = [];
        foreach ($couponDetails as $couponDetail) {
            $orders = Order::where('order_discount_json', 'like', '%' . $couponDetail['coupon_detail_id'] . '%')
                ->whereIn('order_is_complete', ['Y', 'N'])
                ->where('order_status_payment', 'SUCCESS')
                ->when(!empty($orderIds), function ($query) use ($orderIds) {
                    $query->whereIn('order_id', $orderIds);
                })->get()
                ->toArray();
            foreach ($orders as $order) {
                $sheets = OrderSheet::with([
                    'goods' => function ($query) {
                        $query->select('goods_id', 'goods_name');
                    }
                ])->where('join_sheet_order_id', $order['order_id'])
                    ->get()
                    ->toArray();
                $deng = "";
                if (count($sheets) > 1) {
                    $deng = '等';
                }

                $discountJson = json_decode($order['order_discount_json'], true);
                $couponName = '';
                $discountAmount = 0;
                foreach ($discountJson as $item) {
                    $discountAmount = $item['coupon_value'];
                    $couponName = Coupon::where('coupon_id', $item['coupon_id'])->value('coupon_name');
                }

                $payDetailTimes = PayDetail::whereJsonContains('join_pay_object_json->order_id', $order['order_id'])->value('pay_paytimes');
                $data[] = [
                    'order_id' => $order['order_id'],
                    'goods_name' => !empty($sheets[0]) && !empty($sheets[0]['goods']) ? ($sheets[0]['goods']['goods_name'] . $deng) : '',
                    'order_classify' => $order['order_classify'],
                    'order_amount_total' => $order['order_amount_total'],
                    'order_amount_pay' => $order['order_amount_pay'],
                    'coupon_name' => $couponName,
                    'discountAmount' => $discountAmount,
                    'order_status_system' => $order['order_status_system'],
                    'order_addtimes' => $order['order_addtimes'],
                    'pay_paytimes' => $payDetailTimes
                ];
            }
        }

        return $data;
    }

    public function roleBenefitExport(Request $request)
    {
        $orderIds = $request->post('order_id', []);
        $memberId = $request->post('member_id');
        $applyUsername = $request->post('apply_username', '');
        $applyMobile = $request->post('apply_mobile', '');
        $applyAmount = $request->post('apply_amount', '');
        if (!$memberId) {
            return json_fail("参数异常");
        }

        $data = $this->getRoleBenefitUsedList($memberId, $orderIds);
        $dicountAmount = 0;
        foreach ($data as $key => $item) {
            $data[$key]['order_classify'] = OrderService::$orderClassify[$item['order_classify']];
            $dicountAmount += $item['discountAmount'];
        }

        $returnApplayData['data'] = $data;
        $returnApplayData['amount'] = $applyAmount;
        $returnApplayData['apply_username'] = $applyUsername;
        $returnApplayData['apply_mobile'] = $applyMobile;
        $returnApplayData['reason'] = "会员权益退款";


        $data[] = [
            'discountAmount' => '优惠合计：￥' . sprintf('%.2f', $dicountAmount)
        ];
//        $data[] = [
//            'order_classify' => '申请人：' . $applyUsername,
//        ];
//        $data[] = [
//            'order_classify' => '电话：' . $applyMobile,
//        ];
//        $data[] = [
//            'order_classify' => '退款金额：' . $applyAmount,
//        ];

        // 产生退款记录
//        $this->refundOrder($memberId, $returnApplayData);

        return json_success('success', $data);
    }

    public function refundOrder(Request $request)
    {
        $params = $request->post();
        Db::beginTransaction();
        try {
            // 查出所有的充值订单
            $orders = Order::where('join_order_member_id', $params['member_id'])
                ->where('order_classify', 'RECHARGE')
                ->where('order_is_complete', 'Y')
                ->get()
                ->toArray();
            $orderIds = array_column($orders, 'order_id');
            if (empty($orderIds)) {
                throw new BusinessException("未查询到充值订单");
            }

            // 订单改为退款订单
            Order::whereIn('order_id', $orderIds)->update([
                'order_is_complete' => 'R'
            ]);

            OrderReturn::where('join_return_member_id', $params['member_id'])
                ->where('order_return_status', '<>', 'DONE')
                ->where('order_return_category', '会员退款')
                ->delete();

            $returnApplyJson = [
                'reason' => '会员退款',
                'order_id' => $orderIds,
                'classify' => 'ROLE_REFUND',
                'username' => $params['username'],
                'mobile' => $params['mobile'],
                'surplus' => $params['surplus'],
                'added' => $params['added'],
                'amount' => $params['bank_amount'],
                'bank_amount' => $params['bank_amount'],
                'bank_name' => $params['bank_name'],
                'bank_acount' => $params['bank_acount'],
                'bank_cardno' => $params['bank_cardno']
            ];
            // 退款记录
            OrderReturn::insert([
                'join_order_return_user_id' => JwtToken::getCurrentId(),
                'join_return_member_id' => $params['member_id'],
                'join_return_order_id' => $orderIds[0],
                'order_return_status' => 'PENDING',
                'order_return_category' => '会员退款',
                'order_return_apply_datetime' => date('Y-m-d H:i:s'),
                'order_return_apply_json' => json_encode($returnApplyJson),
                'order_return_remark' => $params['remark'] ?? '',
                'order_return_addtimes' => time()
            ]);

            Db::commit();

            return json_success("success");
        } catch (BusinessException $e) {
            Db::rollBack();
            Log::error("创建退款记录失败：" . $e->getMessage());

            return json_fail($e->getMessage());
        } catch (\Exception $e) {
            Db::rollBack();
            Log::error("创建退款记录失败：" . $e->getMessage());

            return json_fail("创建退款记录失败");
        }
    }

    public function roleBenefitRefund(Request $request)
    {
        $memberId = $request->post('member_id');
        if (!$memberId) {
            return json_fail('参数异常');
        }

        $returnOrder = OrderReturn::where('join_return_member_id', $memberId)
            ->where('order_return_status', 'DOING')
            ->where('order_return_category', '会员权益退款')
            ->first();
        if (!$returnOrder) {
            return json_fail("退款数据异常");
        }
        $memberAccount = MemberAccount::where('join_account_member_id', $memberId)
            ->where('member_account_classify', 'CASH')
            ->where('member_account_status', 'ACTIVED')
            ->first();
        if (!$memberAccount) {
            return json_fail('会员账户异常');
        }
        try {
            Db::beginTransaction();
            $refundApplyJson = json_decode($returnOrder->order_return_apply_json, true);
            $amount = $refundApplyJson['amount'];
            $refundApplyJson['user_id'] = JwtToken::getCurrentId();
            $refundApplyJson['datetime'] = date('Y-m-d H:i:s');
            $returnOrder->order_return_refund_json = json_encode($refundApplyJson);
            $returnOrder->order_return_status = 'DONE';
            $returnOrder->save();
            // 账户剩余
            if ($memberAccount->member_account_surplus + $memberAccount->member_account_added < $amount) {
                throw new BusinessException("账户余额不足");
            }
            // 本金
            if ($memberAccount->member_account_surplus >= $amount) {
                $memberAccount->member_account_surplus = $memberAccount->member_account_surplus - $amount;
            } else {
                $memberAccount->member_account_added = $memberAccount->member_account_added - ($amount - $memberAccount->member_account_surplus);
                $memberAccount->member_account_surplus = 0;
            }
            $memberAccount->save();
            // 删除会员身份
            Member::where('member_id', $memberId)
                ->update([
                    'join_member_role_id' => '',
                    'member_role_begintime' => '',
                    'member_role_config_json' => '[]'
                ]);
            // 没用的优惠券失效
            CouponDetail::where('join_coupon_detail_member_id', $memberId)
                ->where('coupon_detail_status', '<>', 'USED')
                ->whereJsonContains('coupon_detail_extend_json->gettype', 'ROLE')
                ->update([
                    'coupon_detail_status' => 'DISABLED'
                ]);

            // 产生一条退款记录 （pay_detail）
            PayDetail::insert([
                'join_pay_member_id' => $memberId,
                'join_pay_order_id' => 'ROLE_REFUND',
                'join_pay_object_json' => '[]',
                'pay_status' => 'SUCCESS',
                'pay_category' => 'ROLE_REFUND',
                'pay_amount' => $amount,
                'pay_paytimes' => date('Y-m-d H:i:s'),
                'pay_prepayid' => $memberId . '-CASH',
                'pay_addtimes' => time()
            ]);

            Db::commit();

            return json_success('success');
        } catch (BusinessException $e) {
            Db::rollBack();

            return json_fail($e->getMessage());
        } catch (\Exception $e) {
            Db::rollBack();

            return json_fail('退款失败');
        }
    }

    public function crownContinue(Request $request)
    {
        $memberId = $request->post('member_id');
        $dataTime = $request->post('datetime', date('Y-m-d'));
        $sendAll = $request->post('send_all', false);
        $orderId = $request->post('order_id');
        if (!$memberId || !$orderId) {
            return json_fail('参数异常');
        }

        $member = Member::where('member_id', $memberId)->first();

        try {
            Db::beginTransaction();
            $roleConfigJson = [];
            if (!empty($member->member_role_config_json)) {
                $roleConfigJson = json_decode($member->member_role_config_json, true);
                if (!empty($roleConfigJson['next'])) {
                    unset($roleConfigJson['next']);
                }
            }

            $roleConfigJson['deadline_datetime'] = date('Y-m-d 23:59:59', strtotime($dataTime . ' + 364 days'));
            $member->member_role_config_json = json_encode($roleConfigJson);
            $member->member_role_begintime = date('Y-m-d 00:00:00', strtotime($dataTime));

            $memberRoleId = MemberRole::where('member_role_category', 'COMBINE')
                ->where('member_role_status', 'ACTIVED')
                ->where('member_role_name', 'like', '%皇冠%')
                ->value('member_role_id');
            $member->join_member_role_id = $memberRoleId;
            $member->save();
            if ($sendAll === true) {
                $payDetail = PayDetail::whereJsonContains('join_pay_object_json->order_id', $orderId)
                    ->first();
                if (!empty($payDetail->pay_extend_json)) {
                    $payExtendJson = json_decode($payDetail->pay_extend_json, true);
                    $account = MemberAccount::where('join_account_member_id', $memberId)
                        ->where('member_account_classify', 'CASH')
                        ->first();
                    $account->member_account_added = $account->member_account_added + $payExtendJson['added_amount'];
                    $account->save();
                }
                // 产生一条支付记录
                PayDetail::insert([
                    'join_pay_member_id' => $memberId,
                    'join_pay_order_id' => 'CROWN_CONTINUE',
                    'join_pay_object_json' => '[]',
                    'pay_status' => 'SUCCESS',
                    'pay_category' => 'CROWN_CONTINUE',
                    'pay_amount' => 0,
                    'pay_paytimes' => date('Y-m-d H:i:s'),
                    'pay_prepayid' => $memberId . '-CASH',
                    'pay_extend_json' => $payDetail->pay_extend_json,
                    'pay_addtimes' => time()
                ]);

                $goodsAttributeJson = RechargeService::getGoodsAttributeJson($orderId);

                // 发券
                $couponParams['order_id'] = $orderId;
                RechargeService::disposeRoleCoupon($goodsAttributeJson, $couponParams, $memberId);
            }
            Db::commit();

            return json_success('success');
        } catch (\Exception $e) {
            dump($e->getTrace());
            Db::rollBack();
            Log::error("续期失败：" . $e->getMessage());
            return json_fail("续期失败");
        }
    }
}