get('page', 1); $pageSize = $request->get('pageSize', 20); // $classify = $request->get('classify', 'CASH'); $days = $request->get('member_addtimes', []); $level = $request->get('level', ''); $memberId = $request->get('member_id'); $accountType = $request->get('account_type', 'CASH'); $premisesId = intval($request->get('premises_id', '')); if (!empty($days)) { $days[0] = strtotime($days[0]); $days[1] = strtotime($days[1]); $month = date('Ym', $days[0]); $days[1] = strtotime(date('Y-m-d', $days[1]) . " 23:59:59"); } else { $month = date('Ym'); } // 兼容老等级搜索 $levelIds = []; if (!empty($level)) { $levelName = MemberRole::where('member_role_id', $level)->value('member_role_name'); if (!empty($levelName)) { $levelIds = MemberRole::where('member_role_name', $levelName)->pluck('member_role_id')->toArray(); } } $rows = Member::with([ 'cert' => function ($query) { $query->select('join_cert_member_id', 'member_cert_name'); }, 'info' => function ($query) { $query->select('join_info_member_id', 'member_info_nickname'); }, 'role' => function ($query) { $query->select('member_role_id', 'member_role_name'); } ])->join('member_account as ma', function ($join) use ($accountType) { $join->on('member.member_id', '=', 'ma.join_account_member_id')->where('ma.member_account_classify', '=', $accountType); })->leftJoin('member_role', 'member_role.member_role_id', 'member.join_member_role_id') ->when(!empty($level), function ($query) use ($level, $levelIds) { if ($level == '00') { $query->where('join_member_role_id', '')->orWhere('join_member_role_id', NULL); } else if ($level == 'VIP') { $query->where('member.member_is_vip', 'Y'); } else if (!empty($levelIds)) { $query->whereIn('member_role.member_role_id', $levelIds); } else { $query->where('join_member_role_id', $level); } })->when(!empty($memberId), function ($query) use ($memberId) { $query->where('member.member_id', $memberId); })->when(!empty($days), function ($query) use ($days) { $query->whereBetween('member.member_addtimes', $days); })->when(!empty($premisesId), function ($query) use ($premisesId) { $query->whereJsonContains('member.member_extend_json->belong->premises', $premisesId); })->where('member.member_mobile', '<>', '0000'); $total = $rows->count('member_id'); $statistics['surplus'] = $rows->sum('ma.member_account_surplus'); $statistics['income'] = $rows->sum('ma.member_account_income'); $statistics['expend'] = $rows->sum('member_account_expend'); $statistics['added'] = $rows->sum('member_account_added'); $statistics['memberTotal'] = Member::where('member_mobile', '<>', '0000') ->when(!empty($level), function ($query) use ($level, $levelIds) { if ($level == '00') { $query->where('join_member_role_id', '')->orWhere('join_member_role_id', NULL); } else if ($level == 'VIP') { $query->where('member_is_vip', 'Y'); } else if (!empty($levelIds)) { $query->whereIn('member_role_id', $levelIds); } else { $query->where('join_member_role_id', $level); } })->when(!empty($memberId), function ($query) use ($memberId) { $query->where('member_id', $memberId); })->when(!empty($days), function ($query) use ($days) { $query->whereBetween('member_addtimes', $days); })->when(!empty($premisesId), function ($query) use ($premisesId) { $query->whereJsonContains('member_extend_json->belong->premises', $premisesId); })->count(); $rows = $rows->select('member.member_id', 'member.member_mobile', 'member.member_addtimes', 'member.join_member_role_id', 'ma.member_account_income as member_account_income', 'ma.member_account_expend as member_account_expend', 'ma.member_account_surplus as member_account_surplus', 'ma.member_account_added as member_account_added' ) ->orderBy('member.member_addtimes', 'DESC') ->forPage($page, $pageSize) ->get() ->toArray(); return json_success('', compact('rows', 'page', 'pageSize', 'total', 'statistics')); } }