ReferrerController.php 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257
  1. <?php
  2. namespace app\admin\controller\member;
  3. use app\admin\service\member\MemberService;
  4. use app\controller\Curd;
  5. use app\model\CouponDetail;
  6. use app\model\Member;
  7. use app\model\MemberRole;
  8. use app\model\SysDept;
  9. use app\model\SysUser;
  10. use support\Request;
  11. class ReferrerController extends Curd
  12. {
  13. public function list(Request $request)
  14. {
  15. $page = $request->get('page', 1);
  16. $pageSize = $request->get('pageSize', 10);
  17. $name = $request->get('name', '');
  18. $nickname = $request->get('nickname', '');
  19. $mobile = $request->get('mobile', '');
  20. $level = $request->get('level', '');
  21. $isAuth = $request->get('is_auth', '');
  22. $addtime = $request->get('member_addtimes', []);
  23. $type = $request->get('type', '');
  24. $status = $request->get('member_status', '');
  25. $memberIsPartner = $request->get('member_is_partner', '');
  26. $memberIsReferrer = $request->get('member_is_referrer', '');
  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. 'account' => function ($query) {
  37. $query->where('member_account_status', 'ACTIVED');
  38. }
  39. ])->where('member_is_owner', 'N')
  40. ->where('member_mobile', '<>', '0000')
  41. ->leftJoin('member_info', 'member_info.join_info_member_id', 'member.member_id')
  42. ->leftJoin('member_cert', 'member_cert.join_cert_member_id', 'member.member_id')
  43. ->leftJoin('member_role', 'member_role.member_role_id', 'member.join_member_role_id')
  44. ->when($name != '', function ($query) use ($name) {
  45. $query->where('member_cert.member_cert_name', 'like', '%' . $name . '%');
  46. })->when($mobile != '', function ($query) use ($mobile) {
  47. $query->where('member.member_mobile', 'like', '%' . $mobile . '%');
  48. })->when($level != '', function ($query) use ($level, $levelIds) {
  49. if ($level == 'other') {
  50. $query->where('member.join_member_role_id', NULL)->orWhere('member.join_member_role_id', '');
  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('member_role.member_role_id', $level);
  57. }
  58. })->when($isAuth != '', function ($query) use ($isAuth) {
  59. if ($isAuth == 'Y') {
  60. $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', '<>', '');
  61. } else {
  62. $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', '');
  63. }
  64. })->when(!empty($addtime), function ($query) use ($addtime) {
  65. $addtime[0] = strtotime($addtime[0]);
  66. $addtime[1] = strtotime($addtime[1]);
  67. $query->whereRaw("CAST(UNIX_TIMESTAMP(JSON_UNQUOTE(JSON_EXTRACT(member_referrer_json,'$.datetime'))) as SIGNED) >= {$addtime[0]}")
  68. ->whereRaw("CAST(UNIX_TIMESTAMP(JSON_UNQUOTE(JSON_EXTRACT(member_referrer_json,'$.datetime'))) as SIGNED) <= {$addtime[1]}");
  69. })->when(!empty($type), function ($query) use ($type) {
  70. if ($type == 'todayAdd') {
  71. $addtime[0] = strtotime(date('Y-m-d') . ' 00:00:00');
  72. $addtime[1] = strtotime(date('Y-m-d') . ' 23:59:59');
  73. } else if ($type == 'monthAdd') {
  74. $addtime[0] = strtotime(date('Y-m-01') . ' 00:00:00');
  75. $addtime[1] = strtotime(date('Y-m-t') . ' 23:59:59');
  76. }
  77. $query->whereBetween('member.member_addtimes', $addtime);
  78. })->when(!empty($nickname), function ($query) use ($nickname) {
  79. $query->where(function ($query2) use ($nickname) {
  80. $query2->where('member_info.member_info_nickname', 'like', '%' . $nickname . '%')->orWhere('member.member_mobile', 'like', '%' . $nickname . '%');
  81. });
  82. })->when(!empty($status), function ($query) use ($status) {
  83. $query->where('member_status', $status);
  84. })->when(!empty($memberIsPartner), function ($query) use ($memberIsPartner) {
  85. if ($memberIsPartner == 'Y') {
  86. $query->where('member_is_partner', 'Y');
  87. }
  88. })->when(!empty($memberIsReferrer), function ($query) use ($memberIsReferrer) {
  89. if ($memberIsReferrer == 'Y') {
  90. $query->where('member_is_referrer', 'Y');
  91. }
  92. })
  93. ->select('member_id', 'member_is_vip', 'member_is_owner', 'join_invite_member_id', 'member_is_partner', 'member_is_referrer', 'member_classify', 'member_status', 'member_mobile', 'member_from', 'member_addtimes', 'member_extend_json',
  94. 'member_info.member_info_nickname', 'member_info.member_info_headimg', 'member_info.member_info_referee', 'member_info.member_info_business', 'member_info.member_info_service', 'member_info.member_info_city', 'member_info.member_info_address', 'member_info.member_info_gender', 'member_info.member_info_birthday',
  95. 'member_role.member_role_id', 'member_role.member_role_name',
  96. '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'
  97. // 'member_account.member_account_surplus'
  98. )->selectRaw("CAST(UNIX_TIMESTAMP(JSON_UNQUOTE(JSON_EXTRACT(member_referrer_json,'$.datetime'))) as SIGNED) as referrer_add_times");
  99. // 总条数
  100. $total = $rows->count('member_id');
  101. // 已认证条数
  102. $authModel = clone $rows;
  103. $authTotal = $authModel->where('member_cert.member_cert_name','<>','')
  104. ->where('member_cert.member_cert_nbr','<>','')
  105. ->count();
  106. $rows = $rows->orderBy('referrer_add_times', 'DESC')
  107. ->forPage($page, $pageSize)
  108. ->get()
  109. ->toArray();
  110. foreach ($rows as &$row) {
  111. $row['referrer_add_times'] = date('Y-m-d H:i:s',$row['referrer_add_times']);
  112. $row['coupon_count'] = CouponDetail::where('join_coupon_detail_member_id', $row['member_id'])->count();
  113. $row['member_mobile'] = substr($row['member_mobile'], 0, 3) . '****' . substr($row['member_mobile'], 7);
  114. // 推荐人
  115. if (!empty($row['join_invite_member_id'])) {
  116. $row['referrer_name'] = MemberService::getMemberNameByMemberId($row['join_invite_member_id']);
  117. }
  118. $row['info'] = [
  119. 'member_info_nickname' => !empty($row['member_info_nickname']) ? $row['member_info_nickname'] : substr($row['member_mobile'], -4, 4) . '会员',
  120. 'member_info_headimg' => !empty($row['member_info_headimg']) ? $row['member_info_headimg'] : '',
  121. 'member_info_referee' => !empty($row['member_info_referee']) ? $row['member_info_referee'] : '',
  122. 'member_info_business' => !empty($row['member_info_business']) ? $row['member_info_business'] : '',
  123. 'member_info_service' => !empty($row['member_info_service']) ? $row['member_info_service'] : '',
  124. 'member_info_city' => !empty($row['member_info_city']) ? explode('-', $row['member_info_city']) : '',
  125. 'member_info_address' => !empty($row['member_info_address']) ? $row['member_info_address'] : '',
  126. 'member_info_birthday' => !empty($row['member_info_birthday']) ? $row['member_info_birthday'] : '',
  127. 'member_info_gender' => !empty($row['member_info_gender']) ? $row['member_info_gender'] : '',
  128. ];
  129. if (substr($row['info']['member_info_headimg'], 0, 2) == '..') {
  130. $row['info']['member_info_headimg'] = '';
  131. }
  132. if (!empty($row['info']['member_info_address']) && in_array($row['info']['member_info_address'], ['请选择', '省-市-区'])) {
  133. $row['info']['member_info_address'] = '';
  134. }
  135. $row['cert'] = [
  136. 'member_cert_birth' => $row['member_cert_birth'],
  137. 'member_cert_gender' => $row['member_cert_gender'],
  138. 'member_cert_name' => $row['member_cert_name'],
  139. 'member_cert_nbr' => $row['member_cert_nbr'],
  140. 'member_cert_province' => !empty($row['member_cert_province']) && is_json($row['member_cert_province']) ? json_decode($row['member_cert_province']) : '',
  141. 'member_cert_addr' => $row['member_cert_addr'],
  142. 'member_cert_face' => !empty($row['member_cert_face']) ? getenv('STORAGE_DOMAIN') . $row['member_cert_face'] : '',
  143. 'member_cert_photo' => !empty($row['member_cert_photo']) ? getenv('STORAGE_DOMAIN') . $row['member_cert_photo'] : '',
  144. 'member_cert_nation' => $row['member_cert_nation'],
  145. ];
  146. $account = [];
  147. if (!empty($row['account'])) {
  148. foreach ($row['account'] as $item) {
  149. if ($item['member_account_classify'] == 'WELFARE') {
  150. $account['welfare'] = [
  151. 'member_account_name' => $item['member_account_name'],
  152. 'member_account_income' => $item['member_account_income'],
  153. 'member_account_expend' => $item['member_account_expend'],
  154. 'member_account_surplus' => $item['member_account_surplus'],
  155. 'member_account_added' => $item['member_account_added'],
  156. ];
  157. } else if ($item['member_account_classify'] == 'POINTS') {
  158. $account['points'] = [
  159. 'member_account_name' => $item['member_account_name'],
  160. 'member_account_income' => $item['member_account_income'],
  161. 'member_account_expend' => $item['member_account_expend'],
  162. 'member_account_surplus' => $item['member_account_surplus'],
  163. 'member_account_added' => $item['member_account_added'],
  164. ];
  165. } else if ($item['member_account_classify'] == 'CASH') {
  166. $account['cash'] = [
  167. 'member_account_name' => $item['member_account_name'],
  168. 'member_account_income' => $item['member_account_income'],
  169. 'member_account_expend' => $item['member_account_expend'],
  170. 'member_account_surplus' => $item['member_account_surplus'],
  171. 'member_account_added' => $item['member_account_added'],
  172. ];
  173. } else if ($item['member_account_classify'] == 'CARD') {
  174. $account['card'] = [
  175. 'member_account_name' => $item['member_account_name'],
  176. 'member_account_income' => $item['member_account_income'],
  177. 'member_account_expend' => $item['member_account_expend'],
  178. 'member_account_surplus' => $item['member_account_surplus'],
  179. 'member_account_added' => $item['member_account_added'],
  180. ];
  181. } else if ($item['member_account_classify'] == 'VIP') {
  182. $account['vip'] = [
  183. 'member_account_name' => $item['member_account_name'],
  184. 'member_account_income' => $item['member_account_income'],
  185. 'member_account_expend' => $item['member_account_expend'],
  186. 'member_account_surplus' => $item['member_account_surplus'],
  187. 'member_account_added' => $item['member_account_added'],
  188. ];
  189. }
  190. }
  191. }
  192. $row['account'] = $account;
  193. $row['is_auth'] = "未认证";
  194. if (!empty($row['member_cert_nbr']) && !empty($row['member_cert_name'])) {
  195. $row['is_auth'] = "已认证";
  196. }
  197. $row['source'] = "APP";
  198. $row['belong'] = [];
  199. $row['referee'] = [];
  200. if (!empty($row['member_extend_json'])) {
  201. $extendJson = json_decode($row['member_extend_json'], true);
  202. if (isset($extendJson['come_from'])) {
  203. $row['source'] = $extendJson['come_from'] ?? '';
  204. }
  205. if (isset($extendJson['belong'])) {
  206. $user = SysUser::where('user_id', $extendJson['belong']['user_id'])->select('user_name', 'user_mobile')->first();
  207. if ($user) {
  208. $row['belong']['user_id'] = $extendJson['belong']['user_id'];
  209. $row['belong']['username'] = $user->user_name;
  210. $row['belong']['user_mobile'] = $user->user_mobile;
  211. }
  212. $row['belong']['deptName'] = SysDept::where('dept_id', $extendJson['belong']['premises'])->value('dept_name');
  213. }
  214. if (isset($extendJson['referee'])) {
  215. foreach ($extendJson['referee'] as $key => $referee) {
  216. if (preg_match('/^1[3-9]\d{9}$/', $referee)) {
  217. $member = Member::with([
  218. 'cert' => function ($query) {
  219. $query->select('join_cert_member_id', 'member_cert_name');
  220. }
  221. ])->where('member_mobile', $referee)->where('member_status', 'ACTIVED')
  222. ->first();
  223. if ($member->cert && $member->cert->member_cert_name) {
  224. $referee = $member->cert->member_cert_name . '-' . $referee;
  225. }
  226. }
  227. $row['referee'][] = [
  228. 'name' => $referee,
  229. 'time' => $key
  230. ];
  231. }
  232. }
  233. }
  234. // 粉丝
  235. $fans1 = Member::where('join_invite_member_id', $row['member_id'])
  236. ->select('member_id')
  237. ->get()
  238. ->toArray();
  239. $fans1Ids = array_column($fans1, 'member_id');
  240. $fans2Count = Member::whereIn('join_invite_member_id', $fans1Ids)
  241. ->count();
  242. $row['fans_total'] = count($fans1) + $fans2Count;
  243. }
  244. return json_success('', compact('rows', 'page', 'pageSize', 'total','authTotal'));
  245. }
  246. }