MemberAccountController.php 5.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109
  1. <?php
  2. namespace app\admin\controller\finance;
  3. use app\model\Member;
  4. use app\model\MemberRole;
  5. use support\Db;
  6. use support\Request;
  7. class MemberAccountController
  8. {
  9. public function list(Request $request)
  10. {
  11. $page = $request->get('page', 1);
  12. $pageSize = $request->get('pageSize', 20);
  13. // $classify = $request->get('classify', 'CASH');
  14. $days = $request->get('member_addtimes', []);
  15. $level = $request->get('level', '');
  16. $memberId = $request->get('member_id');
  17. $accountType = $request->get('account_type', 'CASH');
  18. $premisesId = intval($request->get('premises_id', ''));
  19. if (!empty($days)) {
  20. $days[0] = strtotime($days[0]);
  21. $days[1] = strtotime($days[1]);
  22. $month = date('Ym', $days[0]);
  23. $days[1] = strtotime(date('Y-m-d', $days[1]) . " 23:59:59");
  24. } else {
  25. $month = date('Ym');
  26. }
  27. // 兼容老等级搜索
  28. $levelIds = [];
  29. if (!empty($level)) {
  30. $levelName = MemberRole::where('member_role_id', $level)->value('member_role_name');
  31. if (!empty($levelName)) {
  32. $levelIds = MemberRole::where('member_role_name', $levelName)->pluck('member_role_id')->toArray();
  33. }
  34. }
  35. $rows = Member::with([
  36. 'cert' => function ($query) {
  37. $query->select('join_cert_member_id', 'member_cert_name');
  38. },
  39. 'info' => function ($query) {
  40. $query->select('join_info_member_id', 'member_info_nickname');
  41. },
  42. 'role' => function ($query) {
  43. $query->select('member_role_id', 'member_role_name');
  44. }
  45. ])->join('member_account as ma', function ($join) use ($accountType) {
  46. $join->on('member.member_id', '=', 'ma.join_account_member_id')->where('ma.member_account_classify', '=', $accountType);
  47. })->leftJoin('member_role', 'member_role.member_role_id', 'member.join_member_role_id')
  48. ->when(!empty($level), function ($query) use ($level, $levelIds) {
  49. if ($level == '00') {
  50. $query->where('join_member_role_id', '')->orWhere('join_member_role_id', NULL);
  51. } else if ($level == 'VIP') {
  52. $query->where('member.member_is_vip', 'Y');
  53. } else if (!empty($levelIds)) {
  54. $query->whereIn('member_role.member_role_id', $levelIds);
  55. } else {
  56. $query->where('join_member_role_id', $level);
  57. }
  58. })->when(!empty($memberId), function ($query) use ($memberId) {
  59. $query->where('member.member_id', $memberId);
  60. })->when(!empty($days), function ($query) use ($days) {
  61. $query->whereBetween('member.member_addtimes', $days);
  62. })->when(!empty($premisesId), function ($query) use ($premisesId) {
  63. $query->whereJsonContains('member.member_extend_json->belong->premises', $premisesId);
  64. })->where('member.member_mobile', '<>', '0000');
  65. $total = $rows->count('member_id');
  66. $statistics['surplus'] = $rows->sum('ma.member_account_surplus');
  67. $statistics['income'] = $rows->sum('ma.member_account_income');
  68. $statistics['expend'] = $rows->sum('member_account_expend');
  69. $statistics['added'] = $rows->sum('member_account_added');
  70. $statistics['memberTotal'] = Member::where('member_mobile', '<>', '0000')
  71. ->when(!empty($level), function ($query) use ($level, $levelIds) {
  72. if ($level == '00') {
  73. $query->where('join_member_role_id', '')->orWhere('join_member_role_id', NULL);
  74. } else if ($level == 'VIP') {
  75. $query->where('member_is_vip', 'Y');
  76. } else if (!empty($levelIds)) {
  77. $query->whereIn('member_role_id', $levelIds);
  78. } else {
  79. $query->where('join_member_role_id', $level);
  80. }
  81. })->when(!empty($memberId), function ($query) use ($memberId) {
  82. $query->where('member_id', $memberId);
  83. })->when(!empty($days), function ($query) use ($days) {
  84. $query->whereBetween('member_addtimes', $days);
  85. })->when(!empty($premisesId), function ($query) use ($premisesId) {
  86. $query->whereJsonContains('member_extend_json->belong->premises', $premisesId);
  87. })->count();
  88. $rows = $rows->select('member.member_id', 'member.member_mobile', 'member.member_addtimes', 'member.join_member_role_id',
  89. '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'
  90. )
  91. ->orderBy('member.member_addtimes', 'DESC')
  92. ->forPage($page, $pageSize)
  93. ->get()
  94. ->toArray();
  95. return json_success('', compact('rows', 'page', 'pageSize', 'total', 'statistics'));
  96. }
  97. }