User.php 78 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509151015111512151315141515151615171518151915201521152215231524152515261527152815291530153115321533153415351536153715381539154015411542154315441545154615471548154915501551155215531554155515561557155815591560156115621563156415651566156715681569157015711572157315741575157615771578157915801581158215831584158515861587158815891590159115921593159415951596159715981599160016011602160316041605160616071608160916101611161216131614161516161617161816191620162116221623162416251626162716281629163016311632163316341635163616371638163916401641164216431644164516461647164816491650165116521653165416551656165716581659166016611662166316641665166616671668166916701671167216731674167516761677167816791680168116821683168416851686168716881689169016911692169316941695169616971698169917001701170217031704170517061707170817091710171117121713171417151716171717181719172017211722172317241725172617271728172917301731173217331734173517361737173817391740174117421743174417451746174717481749175017511752175317541755175617571758175917601761176217631764176517661767176817691770177117721773177417751776177717781779178017811782178317841785178617871788178917901791179217931794179517961797
  1. <?php
  2. // +----------------------------------------------------------------------
  3. // | CRMEB [ CRMEB赋能开发者,助力企业发展 ]
  4. // +----------------------------------------------------------------------
  5. // | Copyright (c) 2016~2022 https://www.crmeb.com All rights reserved.
  6. // +----------------------------------------------------------------------
  7. // | Licensed CRMEB并不是自由软件,未经许可不能去掉CRMEB相关版权
  8. // +----------------------------------------------------------------------
  9. // | Author: CRMEB Team <admin@crmeb.com>
  10. // +----------------------------------------------------------------------
  11. namespace app\merchant\model\user;
  12. use app\admin\model\order\StoreOrder;
  13. use app\admin\model\user\UserExtract;
  14. use traits\ModelTrait;
  15. use app\wap\model\user\UserBill;
  16. use basic\ModelBasic;
  17. use app\admin\model\wechat\WechatUser;
  18. use service\SystemConfigService;
  19. use app\admin\model\order\StoreOrderStatus;
  20. use app\admin\model\system\SystemMenus;
  21. use service\PhpSpreadsheetService;
  22. /**
  23. * 用户管理 model
  24. * Class User
  25. * @package app\merchant\model\user
  26. */
  27. class User extends ModelBasic
  28. {
  29. use ModelTrait;
  30. /**
  31. * @param $where
  32. * @return array
  33. */
  34. public static function systemPage($where)
  35. {
  36. $model = new self;
  37. if ($where['status'] != '') $model = $model->where('status', $where['status']);
  38. if ($where['is_promoter'] != '') $model = $model->where('is_promoter', $where['is_promoter']);
  39. if ($where['nickname'] != '') $model = $model->where('nickname|uid', 'like', "%$where[nickname]%");
  40. if (isset($where['uids']) && count($where['uids'])) $model->where('uid', 'in', $where['uids']);
  41. $model = $model->order('uid desc');
  42. return self::page($model, function ($item) {
  43. if ($item['spread_uid']) {
  44. $item['spread_uid_nickname'] = self::where('uid', $item['spread_uid'])->value('nickname');
  45. } else {
  46. $item['spread_uid_nickname'] = '无';
  47. }
  48. }, $where);
  49. }
  50. public static function getSpreadUidTwo($uid)
  51. {
  52. if (is_array($uid)) $spread_uid = self::where('spread_uid', 'in', $uid)->column('uid');
  53. else $spread_uid = self::where('spread_uid', $uid)->column('uid');
  54. return self::where('spread_uid', 'in', $spread_uid)->column('uid');
  55. }
  56. /*
  57. * 设置搜索条件
  58. *
  59. */
  60. public static function setWhere($where)
  61. {
  62. if ($where['order'] != '') {
  63. $model = self::order(self::setOrder($where['order']));
  64. } else {
  65. $model = self::order('u.uid desc');
  66. }
  67. if ($where['nickname'] != '') {
  68. $model = $model->where('u.nickname|u.uid|u.phone', 'LIKE', "%$where[nickname]%");
  69. }
  70. if ($where['status'] != '') {
  71. $model = $model->where('status', $where['status']);
  72. }
  73. if ($where['user_time_type'] == 'visitno' && $where['user_time'] != '') {
  74. list($startTime, $endTime) = explode(' - ', $where['user_time']);
  75. $model = $model->where('u.last_time', ['>', strtotime($endTime) + 24 * 3600], ['<', strtotime($startTime)], 'or');
  76. }
  77. if ($where['user_time_type'] == 'visit' && $where['user_time'] != '') {
  78. list($startTime, $endTime) = explode(' - ', $where['user_time']);
  79. $model = $model->where('u.last_time', '>', strtotime($startTime));
  80. $model = $model->where('u.last_time', '<', strtotime($endTime) + 24 * 3600);
  81. }
  82. if ($where['user_time_type'] == 'add_time' && $where['user_time'] != '') {
  83. list($startTime, $endTime) = explode(' - ', $where['user_time']);
  84. $model = $model->where('u.add_time', '>', strtotime($startTime));
  85. $model = $model->where('u.add_time', '<', strtotime($endTime) + 24 * 3600);
  86. }
  87. if ($where['pay_count'] !== '') {
  88. if ($where['pay_count'] == '-1') $model = $model->where('pay_count', 0);
  89. else $model = $model->where('pay_count', '>', $where['pay_count']);
  90. }
  91. switch ($where['is_promoter']) {
  92. case '1':
  93. $model = $model->where('u.is_promoter', 1);
  94. break;
  95. case '2':
  96. $model = $model->where('u.level', 1);
  97. break;
  98. }
  99. return $model;
  100. }
  101. /**
  102. * 异步获取当前用户 信息
  103. * @param $where
  104. * @return array
  105. */
  106. public static function getUserList($where)
  107. {
  108. $model = self::setWhere($where);
  109. $list = $model->alias('u')->field('u.*')->page((int)$where['page'], (int)$where['limit'])
  110. ->select();
  111. $list = count($list) > 0 ? $list->toArray() : [];
  112. $isPhoneMenu = is_phone_menu();
  113. foreach ($list as $k => &$item) {
  114. if ($item['spread_uid']) {
  115. $nickname = self::where('uid', $item['spread_uid'])->value('nickname');
  116. if (check_phone($nickname) && !$isPhoneMenu) {
  117. $nickname = substr_replace($nickname, '****', 3, 4);
  118. }
  119. $item['spread_uid_nickname'] = $nickname . '/' . $item['spread_uid'];
  120. } else {
  121. $item['spread_uid_nickname'] = '无';
  122. }
  123. if ($item['level']) {
  124. $item['levelType'] = '会员';
  125. } else {
  126. $item['levelType'] = '非会员';
  127. }
  128. if ($item['is_h5user'] == 1) {
  129. $item['user_type'] = 'H5';
  130. } else if ($item['is_h5user'] == 2) {
  131. $item['user_type'] = 'PC';
  132. } else if (!$item['is_h5user']) {
  133. $item['user_type'] = '公众号';
  134. }
  135. if ($item['valid_time']) $item['_valid_time'] = date('Y-m-d H:i:s', $item['valid_time']);
  136. if (!$isPhoneMenu && $item['phone']) {
  137. $item['phone'] = substr_replace($item['phone'], '****', 3, 4);
  138. }
  139. if (check_phone($item['nickname']) && !$isPhoneMenu) {
  140. $item['nickname'] = substr_replace($item['nickname'], '****', 3, 4);
  141. }
  142. }
  143. $count = self::setWhere($where)->alias('u')->count();
  144. return ['count' => $count, 'data' => $list];
  145. }
  146. /**
  147. * 异步获取当前用户 信息
  148. * @param $where
  149. * @return array
  150. */
  151. public static function add_get_user_list($where)
  152. {
  153. $model = self::setWhere($where);
  154. $list = $model->alias('u')
  155. ->field('u.*')
  156. ->page((int)$where['page'], (int)$where['limit'])
  157. ->select();
  158. $count = self::setWhere($where)->alias('u')->count();
  159. return ['count' => $count, 'data' => $list];
  160. }
  161. /**
  162. * 修改用户状态
  163. * @param $uids 用户uid
  164. * @param $status 修改状态
  165. * @return array
  166. */
  167. public static function destrSyatus($uids, $status)
  168. {
  169. if (empty($uids) && !is_array($uids)) return false;
  170. if ($status == '') return false;
  171. self::beginTrans();
  172. try {
  173. $res = self::where('uid', 'in', $uids)->update(['status' => $status]);
  174. self::checkTrans($res);
  175. return true;
  176. } catch (\Exception $e) {
  177. self::rollbackTrans();
  178. return Json::fail($e->getMessage());
  179. }
  180. }
  181. /*
  182. * 获取某季度,某年某年后的时间戳
  183. *
  184. * self::getMonth('n',1) 获取当前季度的上个季度的时间戳
  185. * self::getMonth('n') 获取当前季度的时间戳
  186. */
  187. public static function getMonth($time = '', $ceil = 0)
  188. {
  189. if (empty($time)) {
  190. $firstday = date("Y-m-01", time());
  191. $lastday = date("Y-m-d", strtotime("$firstday +1 month -1 day"));
  192. } else if ($time == 'n') {
  193. if ($ceil != 0)
  194. $season = ceil(date('n') / 3) - $ceil;
  195. else
  196. $season = ceil(date('n') / 3);
  197. $firstday = date('Y-m-01', mktime(0, 0, 0, ($season - 1) * 3 + 1, 1, date('Y')));
  198. $lastday = date('Y-m-t', mktime(0, 0, 0, $season * 3, 1, date('Y')));
  199. } else if ($time == 'y') {
  200. $firstday = date('Y-01-01');
  201. $lastday = date('Y-12-31');
  202. } else if ($time == 'h') {
  203. $firstday = date('Y-m-d', strtotime('this week +' . $ceil . ' day')) . ' 00:00:00';
  204. $lastday = date('Y-m-d', strtotime('this week +' . ($ceil + 1) . ' day')) . ' 23:59:59';
  205. }
  206. return array($firstday, $lastday);
  207. }
  208. public static function getcount()
  209. {
  210. return self::alias('a')->join('wechat_user u', 'u.uid=a.uid', 'left')->count();
  211. }
  212. /*
  213. *获取用户某个时间段的消费信息
  214. *
  215. * reutrn Array || number
  216. */
  217. public static function consume($where, $status = '', $keep = '')
  218. {
  219. $model = new self;
  220. $user_id = [];
  221. if (is_array($where)) {
  222. if ($where['is_promoter'] != '') $model = $model->where('is_promoter', $where['is_promoter']);
  223. if ($where['status'] != '') $model = $model->where('status', $where['status']);
  224. switch ($where['date']) {
  225. case null:
  226. case 'today':
  227. case 'week':
  228. case 'year':
  229. if ($where['date'] == null) {
  230. $where['date'] = 'month';
  231. }
  232. if ($keep) {
  233. $model = $model->whereTime('add_time', $where['date'])->whereTime('last_time', $where['date']);
  234. } else {
  235. $model = $model->whereTime('add_time', $where['date']);
  236. }
  237. break;
  238. case 'quarter':
  239. $quarter = self::getMonth('n');
  240. $startTime = strtotime($quarter[0]);
  241. $endTime = strtotime($quarter[1]);
  242. if ($keep) {
  243. $model = $model->where('add_time', '>', $startTime)->where('add_time', '<', $endTime)->where('last_time', '>', $startTime)->where('last_time', '<', $endTime);
  244. } else {
  245. $model = $model->where('add_time', '>', $startTime)->where('add_time', '<', $endTime);
  246. }
  247. break;
  248. default:
  249. //自定义时间
  250. if (strstr($where['date'], '-') !== FALSE) {
  251. list($startTime, $endTime) = explode('-', $where['date']);
  252. $model = $model->where('add_time', '>', strtotime($startTime))->where('add_time', '<', strtotime($endTime));
  253. } else {
  254. $model = $model->whereTime('add_time', 'month');
  255. }
  256. break;
  257. }
  258. } else {
  259. if (is_array($status)) {
  260. $model = $model->where('add_time', '>', $status[0])->where('add_time', '<', $status[1]);
  261. }
  262. }
  263. if ($keep === true) {
  264. return $model->count();
  265. }
  266. if ($status === 'default') {
  267. return $model->group('from_unixtime(add_time,\'%Y-%m-%d\')')->field('count(uid) num,from_unixtime(add_time,\'%Y-%m-%d\') add_time,uid')->select()->toArray();
  268. }
  269. $uid = $model->field('uid')->select()->toArray();
  270. foreach ($uid as $val) {
  271. $user_id[] = $val['uid'];
  272. }
  273. if (empty($user_id)) {
  274. $user_id = [0];
  275. }
  276. if ($status === 'xiaofei') {
  277. $list = UserBill::where('uid', 'in', $user_id)
  278. ->group('type')
  279. ->field('sum(number) as top_number,title')
  280. ->select()
  281. ->toArray();
  282. $series = [
  283. 'name' => isset($list[0]['title']) ? $list[0]['title'] : '',
  284. 'type' => 'pie',
  285. 'radius' => ['40%', '50%'],
  286. 'data' => []
  287. ];
  288. foreach ($list as $key => $val) {
  289. $series['data'][$key]['value'] = $val['top_number'];
  290. $series['data'][$key]['name'] = $val['title'];
  291. }
  292. return $series;
  293. } else if ($status === 'form') {
  294. $list = WechatUser::where('uid', 'in', $user_id)->group('city')->field('count(city) as top_city,city')->limit(0, 10)->select()->toArray();
  295. $count = self::getcount();
  296. $option = [
  297. 'legend_date' => [],
  298. 'series_date' => []
  299. ];
  300. foreach ($list as $key => $val) {
  301. $num = $count != 0 ? (bcdiv($val['top_city'], $count, 2)) * 100 : 0;
  302. $t = ['name' => $num . '% ' . (empty($val['city']) ? '未知' : $val['city']), 'icon' => 'circle'];
  303. $option['legend_date'][$key] = $t;
  304. $option['series_date'][$key] = ['value' => $num, 'name' => $t['name']];
  305. }
  306. return $option;
  307. } else {
  308. $number = UserBill::where('uid', 'in', $user_id)->where('type', 'pay_product')->sum('number');
  309. return $number;
  310. }
  311. }
  312. /*
  313. * 获取 用户某个时间段的钱数或者TOP20排行
  314. *
  315. * return Array || number
  316. */
  317. public static function getUserSpend($date, $status = '')
  318. {
  319. $model = new self();
  320. $model = $model->alias('A');
  321. switch ($date) {
  322. case null:
  323. case 'today':
  324. case 'week':
  325. case 'year':
  326. if ($date == null) $date = 'month';
  327. $model = $model->whereTime('A.add_time', $date);
  328. break;
  329. case 'quarter':
  330. list($startTime, $endTime) = self::getMonth('n');
  331. $model = $model->where('A.add_time', '>', strtotime($startTime));
  332. $model = $model->where('A.add_time', '<', strtotime($endTime));
  333. break;
  334. default:
  335. list($startTime, $endTime) = explode('-', $date);
  336. $model = $model->where('A.add_time', '>', strtotime($startTime));
  337. $model = $model->where('A.add_time', '<', strtotime($endTime));
  338. break;
  339. }
  340. if ($status === true) {
  341. return $model->join('user_bill B', 'B.uid=A.uid')->where('B.type', 'pay_product')->where('B.pm', 0)->sum('B.number');
  342. }
  343. $list = $model->join('user_bill B', 'B.uid=A.uid')
  344. ->where('B.type', 'pay_product')
  345. ->where('B.pm', 0)
  346. ->field('sum(B.number) as totel_number,A.nickname,A.avatar,A.now_money,A.uid,A.add_time')
  347. ->order('totel_number desc')
  348. ->limit(0, 20)
  349. ->select()
  350. ->toArray();
  351. if (!isset($list[0]['totel_number'])) {
  352. $list = [];
  353. }
  354. return $list;
  355. }
  356. /*
  357. * 获取 相对于上月或者其他的数据
  358. *
  359. * return Array
  360. */
  361. public static function getPostNumber($date, $status = false, $field = 'A.add_time', $t = '消费')
  362. {
  363. $model = new self();
  364. if (!$status) $model = $model->alias('A');
  365. switch ($date) {
  366. case null:
  367. case 'today':
  368. case 'week':
  369. case 'year':
  370. if ($date == null) {
  371. $date = 'last month';
  372. $title = '相比上月用户' . $t . '增长';
  373. }
  374. if ($date == 'today') {
  375. $date = 'yesterday';
  376. $title = '相比昨天用户' . $t . '增长';
  377. }
  378. if ($date == 'week') {
  379. $date = 'last week';
  380. $title = '相比上周用户' . $t . '增长';
  381. }
  382. if ($date == 'year') {
  383. $date = 'last year';
  384. $title = '相比去年用户' . $t . '增长';
  385. }
  386. $model = $model->whereTime($field, $date);
  387. break;
  388. case 'quarter':
  389. $title = '相比上季度用户' . $t . '增长';
  390. list($startTime, $endTime) = self::getMonth('n', 1);
  391. $model = $model->where($field, '>', $startTime);
  392. $model = $model->where($field, '<', $endTime);
  393. break;
  394. default:
  395. list($startTime, $endTime) = explode('-', $date);
  396. $title = '相比' . $startTime . '-' . $endTime . '时间段用户' . $t . '增长';
  397. $Time = strtotime($endTime) - strtotime($startTime);
  398. $model = $model->where($field, '>', strtotime($startTime) + $Time);
  399. $model = $model->where($field, '<', strtotime($endTime) + $Time);
  400. break;
  401. }
  402. if ($status) {
  403. return [$model->count(), $title];
  404. }
  405. $number = $model->join('user_bill B', 'B.uid=A.uid')->where('B.type', 'pay_product')->where('B.pm', 0)->sum('B.number');
  406. return [$number, $title];
  407. }
  408. //获取用户新增,头部信息
  409. public static function getBadgeList($where)
  410. {
  411. $user_count = self::setWherePage(self::getModelTime($where, new self), $where, ['is_promoter', 'status'])->count();
  412. $user_count_old = self::getOldDate($where)->count();
  413. $fenxiao = self::setWherePage(self::getModelTime($where, new self), $where, ['is_promoter', 'status'])->where('spread_uid', '<>', 0)->count();
  414. $fenxiao_count = self::getOldDate($where)->where('spread_uid', 'neq', 0)->count();
  415. $newFemxiao_count = bcsub($fenxiao, $fenxiao_count, 0);
  416. $order_count = bcsub($user_count, $user_count_old, 0);
  417. return [
  418. [
  419. 'name' => '会员人数',
  420. 'field' => '个',
  421. 'count' => $user_count,
  422. 'content' => '会员总人数',
  423. 'background_color' => 'layui-bg-blue',
  424. 'sum' => self::count(),
  425. 'class' => 'fa fa-bar-chart',
  426. ],
  427. [
  428. 'name' => '会员增长',
  429. 'field' => '个',
  430. 'count' => $order_count,
  431. 'content' => '会员增长率',
  432. 'background_color' => 'layui-bg-cyan',
  433. 'sum' => $user_count_old ? bcdiv($order_count, $user_count_old, 2) * 100 : 0,
  434. 'class' => 'fa fa-line-chart',
  435. ],
  436. [
  437. 'name' => '分销人数',
  438. 'field' => '个',
  439. 'count' => $fenxiao,
  440. 'content' => '分销总人数',
  441. 'background_color' => 'layui-bg-green',
  442. 'sum' => self::where('spread_uid', 'neq', 0)->count(),
  443. 'class' => 'fa fa-bar-chart',
  444. ],
  445. [
  446. 'name' => '分销增长',
  447. 'field' => '个',
  448. 'count' => $newFemxiao_count,
  449. 'content' => '分销总人数',
  450. 'background_color' => 'layui-bg-orange',
  451. 'sum' => $fenxiao_count ? bcdiv($newFemxiao_count, $fenxiao_count, 2) * 100 : 0,
  452. 'class' => 'fa fa-cube',
  453. ],
  454. ];
  455. }
  456. /*
  457. * 获取会员增长曲线图和分布图
  458. * $where 查询条件
  459. * $limit 显示条数,是否有滚动条
  460. */
  461. public static function getUserChartList($where, $limit = 20)
  462. {
  463. $list = self::setWherePage(self::getModelTime($where, new self), $where, ['is_promoter', 'status'])
  464. ->where('add_time', 'neq', 0)
  465. ->field(['FROM_UNIXTIME(add_time,"%Y-%m-%d") as _add_time', 'count(uid) as num'])
  466. ->order('_add_time asc')
  467. ->group('_add_time')
  468. ->select();
  469. count($list) && $list = $list->toArray();
  470. $seriesdata = [];
  471. $xdata = [];
  472. $Zoom = '';
  473. foreach ($list as $item) {
  474. $seriesdata[] = $item['num'];
  475. $xdata[] = $item['_add_time'];
  476. }
  477. (count($xdata) > $limit) && $Zoom = $xdata[$limit - 5];
  478. //多次购物会员数量饼状图
  479. $count = self::setWherePage(self::getModelTime($where, new self), $where, ['is_promoter'])->count();
  480. $user_count = self::setWherePage(self::getModelTime($where, self::alias('a')->join('store_order r', 'r.uid=a.uid'), 'a.add_time'), $where, ['is_promoter'])
  481. ->where('r.paid', 1)->count('a.uid');
  482. $shop_xdata = ['多次购买数量占比', '无购买数量占比'];
  483. $shop_data = [];
  484. $count > 0 && $shop_data = [
  485. [
  486. 'value' => bcdiv($user_count, $count, 2) * 100,
  487. 'name' => $shop_xdata[0],
  488. 'itemStyle' => [
  489. 'color' => '#D789FF',
  490. ]
  491. ],
  492. [
  493. 'value' => bcdiv($count - $user_count, $count, 2) * 100,
  494. 'name' => $shop_xdata[1],
  495. 'itemStyle' => [
  496. 'color' => '#7EF0FB',
  497. ]
  498. ]
  499. ];
  500. return compact('shop_data', 'shop_xdata', 'fenbu_data', 'fenbu_xdata', 'seriesdata', 'xdata', 'Zoom');
  501. }
  502. //获取$date的前一天或者其他的时间段
  503. public static function getOldDate($where, $moedls = null)
  504. {
  505. $model = $moedls === null ? self::setWherePage(new self(), $where, ['is_promoter', 'status']) : $moedls;
  506. switch ($where['data']) {
  507. case 'today':
  508. $model = $model->whereTime('add_time', 'yesterday');
  509. break;
  510. case 'week':
  511. $model = $model->whereTime('add_time', 'last week');
  512. break;
  513. case 'month':
  514. $model = $model->whereTime('add_time', 'last month');
  515. break;
  516. case 'year':
  517. $model = $model->whereTime('add_time', 'last year');
  518. break;
  519. case 'quarter':
  520. $time = self::getMonth('n', 1);
  521. $model = $model->where('add_time', 'between', $time);
  522. break;
  523. }
  524. return $model;
  525. }
  526. //获取用户属性和性别分布图
  527. public static function getEchartsData($where)
  528. {
  529. $model = self::alias('a');
  530. $data = self::getModelTime($where, $model, 'a.add_time')
  531. ->join('wechat_user r', 'r.uid=a.uid')
  532. ->group('r.province')
  533. ->field('count(r.province) as count,province')
  534. ->order('count desc')
  535. ->limit(15)
  536. ->select();
  537. if (count($data)) $data = $data->toArray();
  538. $legdata = [];
  539. $dataList = [];
  540. foreach ($data as $value) {
  541. $value['province'] == '' && $value['province'] = '未知省份';
  542. $legdata[] = $value['province'];
  543. $dataList[] = $value['count'];
  544. }
  545. $model = self::alias('a');
  546. $sex = self::getModelTime($where, $model, 'a.add_time')
  547. ->join('wechat_user r', 'r.uid=a.uid')
  548. ->group('r.sex')
  549. ->field('count(r.uid) as count,sex')
  550. ->order('count desc')
  551. ->select();
  552. if (count($sex)) $sex = $sex->toArray();
  553. $sexlegdata = ['男', '女', '未知'];
  554. $sexcount = self::getModelTime($where, new self())->count();
  555. $sexList = [];
  556. $color = ['#FB7773', '#81BCFE', '#91F3FE'];
  557. foreach ($sex as $key => $item) {
  558. if ($item['sex'] == 1) {
  559. $item_date['name'] = '男';
  560. } else if ($item['sex'] == 2) {
  561. $item_date['name'] = '女';
  562. } else {
  563. $item_date['name'] = '未知性别';
  564. }
  565. $item_date['value'] = bcdiv($item['count'], $sexcount, 2) * 100;
  566. $item_date['itemStyle']['color'] = $color[$key];
  567. $sexList[] = $item_date;
  568. }
  569. return compact('sexList', 'sexlegdata', 'legdata', 'dataList');
  570. }
  571. //获取佣金记录列表
  572. public static function getCommissionList($where)
  573. {
  574. $list = self::setCommissionWhere($where)
  575. ->page((int)$where['page'], (int)$where['limit'])
  576. ->select();
  577. count($list) && $list = $list->toArray();
  578. foreach ($list as &$value) {
  579. $value['ex_price'] = db('user_extract')->where(['uid' => $value['uid']])->sum('extract_price');
  580. $value['extract_price'] = db('user_extract')->where(['uid' => $value['uid'], 'status' => 1])->sum('extract_price');
  581. }
  582. $count = self::setCommissionWhere($where)->count();
  583. return ['data' => $list, 'count' => $count];
  584. }
  585. //获取佣金记录列表的查询条件
  586. public static function setCommissionWhere($where)
  587. {
  588. $models = self::setWherePage(self::alias('A'), $where, [], ['A.nickname', 'A.uid'])
  589. ->join('user_bill B', 'B.uid=A.uid')
  590. ->group('A.uid')
  591. ->where(['B.category' => 'now_money'])
  592. ->where('B.type', 'in', ['brokerage', 'brokerage_return'])
  593. ->field(['B.number', 'A.nickname', 'A.uid', 'A.now_money', 'A.brokerage_price']);
  594. if ($where['order'] == '') {
  595. $models = $models->order('A.brokerage_price desc');
  596. } else {
  597. $models = $models->order($where['order'] == 1 ? 'A.brokerage_price desc' : 'A.brokerage_price asc');
  598. }
  599. if ($where['price_max'] > 0 && $where['price_min'] > 0) {
  600. $models = $models->where('A.brokerage_price', 'between', [$where['price_min'], $where['price_max']]);
  601. }
  602. return $models;
  603. }
  604. //获取某人用户推广信息
  605. public static function getUserinfo($uid)
  606. {
  607. $userinfo = self::where(['uid' => $uid])->field(['nickname', 'spread_uid', 'now_money', 'add_time'])->find()->toArray();
  608. $userinfo['number'] = UserBill::getCommissionAmount($uid);
  609. $userinfo['spread_name'] = $userinfo['spread_uid'] ? self::where(['uid' => $userinfo['spread_uid']])->value('nickname') : '';
  610. return $userinfo;
  611. }
  612. /**用户信息
  613. * @param $uid
  614. * @return array
  615. * @throws \think\Exception
  616. * @throws \think\db\exception\DataNotFoundException
  617. * @throws \think\db\exception\ModelNotFoundException
  618. * @throws \think\exception\DbException
  619. */
  620. public static function getAllUserinfo($uid)
  621. {
  622. $userinfo = self::where(['uid' => $uid])->find()->toArray();
  623. return $userinfo;
  624. }
  625. public static function getUserinfoV1($uid)
  626. {
  627. $isPhoneMenu = is_phone_menu();
  628. $userinfo = self::where(['uid' => $uid])->find()->toArray();
  629. if (check_phone($userinfo['nickname']) && !$isPhoneMenu) {
  630. $userinfo['nickname'] = substr_replace($userinfo['nickname'], '****', 3, 4);
  631. }
  632. $userinfo['spread_name'] = $userinfo['spread_uid'] ? self::where(['uid' => $userinfo['spread_uid']])->value('nickname') : '';
  633. $spread = self::where(['spread_uid' => $uid])->where('is_promoter', 'neq', 0)->column('uid');
  634. $userinfo['spread_count'] = count($spread);
  635. $userinfo['spread_one'] = UserBill::where(['o.paid' => 1, 'a.uid' => $uid, 'a.category' => 'now_money'])
  636. ->where('a.type', 'in', ['brokerage'])->alias('a')->join('__STORE_ORDER__ o', 'a.link_id=o.id')->sum('o.pay_price');
  637. $userinfo['bill_sum'] = UserBill::where(['category' => 'now_money', 'uid' => $userinfo['uid']])->where('type', 'in', ['brokerage'])->sum('number');
  638. return $userinfo;
  639. }
  640. public static function getPayPrice($uid, $type = ['brokerage'])
  641. {
  642. return UserBill::where(['o.paid' => 1, 'a.uid' => $uid, 'a.category' => 'now_money'])
  643. ->where('a.type', 'in', $type)->alias('a')->join('__STORE_ORDER__ o', 'a.link_id=o.id')->sum('o.pay_price');
  644. }
  645. public static function getLinkCount($uid, $type = ['brokerage'])
  646. {
  647. return UserBill::where(['o.paid' => 1, 'a.uid' => $uid, 'a.category' => 'now_money'])
  648. ->where('a.type', 'in', $type)->alias('a')->join('__STORE_ORDER__ o', 'a.link_id=o.id')->count();
  649. }
  650. public static function getSpreadListV1($where)
  651. {
  652. $spread = self::where(['spread_uid' => $where['uid']])->column('uid') ?: [0];
  653. $list = self::where('uid', 'in', $spread)->order('add_time desc')->page((int)$where['page'], (int)$where['limit'])->select();
  654. $list = count($list) ? $list->toArray() : [];
  655. foreach ($list as &$item) {
  656. $item['order_count'] = self::getLinkCount($item['uid'], ['brokerage']);
  657. $item['sum_pay_price'] = self::getPayPrice($item['uid'], ['brokerage']);
  658. $item['sum_number'] = UserBill::where('uid', $item['uid'])->where('category', 'now_money')->where('type', 'in', ['brokerage'])->sum('number');
  659. }
  660. return $list;
  661. }
  662. //获取某用户的详细信息
  663. public static function getUserDetailed($uid)
  664. {
  665. $isPhoneMenu = is_phone_menu();
  666. $gold_name = SystemConfigService::get('gold_name');//虚拟币名称
  667. $key_field = ['real_name', 'phone', 'province', 'city', 'district', 'detail', 'post_code'];
  668. $Address = ($thisAddress = db('user_address')->where(['uid' => $uid, 'is_default' => 1])->field($key_field)->find()) ?
  669. $thisAddress :
  670. db('user_address')->where(['uid' => $uid])->field($key_field)->find();
  671. $UserInfo = self::get($uid);
  672. if ($UserInfo['last_time']) $UserInfo['last_time'] = date('Y-m-d H:i:s', $UserInfo['last_time']);//最近一次访问日期
  673. else $UserInfo['last_time'] = '无访问';//最近一次访问日期
  674. $UserInfo['add_time'] = date('Y-m-d H:i:s', $UserInfo['add_time']);
  675. $time = '首次:' . $UserInfo['add_time'] . '最近:' . $UserInfo['last_time'];
  676. if (check_phone($UserInfo['nickname']) && !$isPhoneMenu) {
  677. $UserInfo['nickname'] = substr_replace($UserInfo['nickname'], '****', 3, 4);
  678. }
  679. if (check_phone($UserInfo['phone']) && !$isPhoneMenu) {
  680. $UserInfo['phone'] = substr_replace($UserInfo['phone'], '****', 3, 4);
  681. }
  682. return [
  683. ['col' => 12, 'name' => '默认收货地址', 'value' => $thisAddress ? '收货人:' . $thisAddress['real_name'] . ' 收货人电话:' . $thisAddress['phone'] . ' 地址:' . $thisAddress['province'] . ' ' . $thisAddress['city'] . ' ' . $thisAddress['district'] . ' ' . $thisAddress['detail'] : ''],
  684. ['name' => 'ID', 'value' => $uid],
  685. ['name' => '手机号码', 'value' => $UserInfo['phone']],
  686. ['name' => '微信昵称', 'value' => $UserInfo['nickname']],
  687. ['name' => '购买次数', 'value' => StoreOrder::getUserCountPay($uid)],
  688. ['name' => "$gold_name" . '余额', 'value' => $UserInfo['gold_num']],
  689. ['name' => '上级推广人', 'value' => $UserInfo['spread_uid'] ? self::where(['uid' => $UserInfo['spread_uid']])->value('nickname') : ''],
  690. ['name' => '账户余额', 'value' => $UserInfo['now_money']],
  691. ['name' => '佣金总收入', 'value' => UserBill::getCommissionAmount($uid)],
  692. ['name' => '提现总金额', 'value' => db('user_extract')->where(['uid' => $uid, 'status' => 1])->sum('extract_price')],
  693. ['name' => '访问日期', 'value' => $time]
  694. ];
  695. }
  696. //获取某用户的订单个数,消费明细
  697. public static function getHeaderList($uid)
  698. {
  699. return [
  700. [
  701. 'title' => '总计订单',
  702. 'value' => StoreOrder::where(['uid' => $uid, 'paid' => 1])->count(),
  703. 'key' => '笔',
  704. 'class' => '',
  705. ],
  706. [
  707. 'title' => '总消费金额',
  708. 'value' => StoreOrder::where(['uid' => $uid, 'paid' => 1])->sum('pay_price'),
  709. 'key' => '元',
  710. 'class' => '',
  711. ],
  712. [
  713. 'title' => '本月订单',
  714. 'value' => StoreOrder::where(['uid' => $uid, 'paid' => 1])->whereTime('add_time', 'month')->count(),
  715. 'key' => '笔',
  716. 'class' => '',
  717. ],
  718. [
  719. 'title' => '本月消费金额',
  720. 'value' => StoreOrder::where(['uid' => $uid, 'paid' => 1])->whereTime('add_time', 'month')->sum('pay_price'),
  721. 'key' => '元',
  722. 'class' => '',
  723. ]
  724. ];
  725. }
  726. /*
  727. * 获取 会员 订单个数,积分明细,优惠劵明细
  728. *
  729. * $uid 用户id;
  730. *
  731. * return array
  732. */
  733. public static function getCountInfo($uid)
  734. {
  735. $order_count = StoreOrder::where(['uid' => $uid])->count();
  736. $integral_count = UserBill::where(['uid' => $uid, 'category' => 'integral'])->where('type', 'in', ['deduction', 'system_add'])->count();
  737. $sign_count = UserBill::where(['uid' => $uid, 'category' => 'integral', 'type' => 'sign'])->count();
  738. $balanceChang_count = UserBill::where(['uid' => $uid, 'category' => 'now_money'])
  739. ->where('type', 'in', ['system_add', 'pay_product', 'extract', 'extract_fail', 'pay_goods', 'pay_sign_up', 'pay_product_refund', 'system_sub'])
  740. ->count();
  741. $coupon_count = 0;
  742. $spread_count = self::where(['spread_uid' => $uid])->count();
  743. $pay_count = self::getDb('special_buy')->where('uid', $uid)->where('is_del', 0)->count();
  744. return compact('order_count', 'integral_count', 'sign_count', 'balanceChang_count', 'coupon_count', 'spread_count', 'pay_count');
  745. }
  746. /*
  747. * 获取 会员业务的
  748. * 购物会员统计
  749. * 会员访问量
  750. *
  751. * 曲线图
  752. *
  753. * $where 查询条件
  754. *
  755. * return array
  756. */
  757. public static function getUserBusinessChart($where, $limit = 20)
  758. {
  759. //获取购物会员人数趋势图
  760. $list = self::getModelTime($where, self::where('a.status', 1)->alias('a')->join('store_order r', 'r.uid=a.uid'), 'a.add_time')
  761. ->where(['r.paid' => 1, 'a.is_promoter' => 0])
  762. ->where('a.add_time', 'neq', 0)
  763. ->field(['FROM_UNIXTIME(a.add_time,"%Y-%m-%d") as _add_time', 'count(r.uid) as count_user'])
  764. ->group('_add_time')
  765. ->order('_add_time asc')
  766. ->select();
  767. count($list) && $list = $list->toArray();
  768. $seriesdata = [];
  769. $xdata = [];
  770. $zoom = '';
  771. foreach ($list as $item) {
  772. $seriesdata[] = $item['count_user'];
  773. $xdata[] = $item['_add_time'];
  774. }
  775. count($xdata) > $limit && $zoom = $xdata[$limit - 5];
  776. //会员访问量
  777. $visit = self::getModelTime($where, self::alias('a')->join('store_visit t', 't.uid=a.uid'), 't.add_time')
  778. ->where('a.is_promoter', 0)
  779. ->field(['FROM_UNIXTIME(t.add_time,"%Y-%m-%d") as _add_time', 'count(t.uid) as count_user'])
  780. ->group('_add_time')
  781. ->order('_add_time asc')
  782. ->select();
  783. count($visit) && $visit = $visit->toArray();
  784. $visit_data = [];
  785. $visit_xdata = [];
  786. $visit_zoom = '';
  787. foreach ($visit as $item) {
  788. $visit_data[] = $item['count_user'];
  789. $visit_xdata[] = $item['_add_time'];
  790. }
  791. count($visit_xdata) > $limit && $visit_zoom = $visit_xdata[$limit - 5];
  792. //多次购物会员数量饼状图
  793. $count = self::getModelTime($where, self::where('is_promoter', 0))->count();
  794. $user_count = self::getModelTime($where, self::alias('a')->join('store_order r', 'r.uid=a.uid'), 'a.add_time')
  795. ->where('a.is_promoter', 0)
  796. ->where('r.paid', 1)
  797. ->group('a.uid')
  798. ->count();
  799. $shop_xdata = ['多次购买数量占比', '无购买数量占比'];
  800. $shop_data = [];
  801. $count > 0 && $shop_data = [
  802. [
  803. 'value' => bcdiv($user_count, $count, 2) * 100,
  804. 'name' => $shop_xdata[0],
  805. 'itemStyle' => [
  806. 'color' => '#D789FF',
  807. ]
  808. ],
  809. [
  810. 'value' => bcdiv($count - $user_count, $count, 2) * 100,
  811. 'name' => $shop_xdata[1],
  812. 'itemStyle' => [
  813. 'color' => '#7EF0FB',
  814. ]
  815. ]
  816. ];
  817. return compact('seriesdata', 'xdata', 'zoom', 'visit_data', 'visit_xdata', 'visit_zoom', 'shop_data', 'shop_xdata');
  818. }
  819. /*
  820. * 获取用户
  821. * 积分排行
  822. * 会员余额排行榜
  823. * 分销商佣金总额排行榜
  824. * 购物笔数排行榜
  825. * 购物金额排行榜
  826. * 分销商佣金提现排行榜
  827. * 上月消费排行榜
  828. * $limit 查询多少条
  829. * return array
  830. */
  831. public static function getUserTop10List($limit = 10, $is_promoter = 0)
  832. {
  833. //积分排行
  834. $integral = self::where('status', 1)
  835. ->where('is_promoter', $is_promoter)
  836. ->order('integral desc')
  837. ->field(['nickname', 'phone', 'integral', 'FROM_UNIXTIME(add_time,"%Y-%m-%d") as add_time'])
  838. ->limit($limit)
  839. ->select();
  840. count($integral) && $integral = $integral->toArray();
  841. //会员余额排行榜
  842. $now_money = self::where('status', 1)
  843. ->where('is_promoter', $is_promoter)
  844. ->order('now_money desc')
  845. ->field(['nickname', 'phone', 'now_money', 'FROM_UNIXTIME(add_time,"%Y-%m-%d") as add_time'])
  846. ->limit($limit)
  847. ->select();
  848. count($now_money) && $now_money = $now_money->toArray();
  849. //购物笔数排行榜
  850. $shopcount = self::alias('a')
  851. ->join('store_order r', 'r.uid=a.uid')
  852. ->where(['r.paid' => 1, 'a.is_promoter' => $is_promoter])
  853. ->group('r.uid')
  854. ->field(['a.nickname', 'a.phone', 'count(r.uid) as sum_count', 'FROM_UNIXTIME(a.add_time,"%Y-%m-%d") as add_time'])
  855. ->order('sum_count desc')
  856. ->limit($limit)
  857. ->select();
  858. count($shopcount) && $shopcount = $shopcount->toArray();
  859. //购物金额排行榜
  860. $order = self::alias('a')
  861. ->join('store_order r', 'r.uid=a.uid')
  862. ->where(['r.paid' => 1, 'a.is_promoter' => $is_promoter])
  863. ->group('r.uid')
  864. ->field(['a.nickname', 'a.phone', 'sum(r.pay_price) as sum_price', 'FROM_UNIXTIME(a.add_time,"%Y-%m-%d") as add_time', 'r.uid'])
  865. ->order('sum_price desc')
  866. ->limit($limit)
  867. ->select();
  868. count($order) && $order = $order->toArray();
  869. //上月消费排行
  870. $lastorder = self::alias('a')
  871. ->join('store_order r', 'r.uid=a.uid')
  872. ->where(['r.paid' => 1, 'a.is_promoter' => $is_promoter])
  873. ->whereTime('r.pay_time', 'last month')
  874. ->group('r.uid')
  875. ->field(['a.nickname', 'a.phone', 'sum(r.pay_price) as sum_price', 'FROM_UNIXTIME(a.add_time,"%Y-%m-%d") as add_time', 'r.uid'])
  876. ->order('sum_price desc')
  877. ->limit($limit)
  878. ->select();
  879. return compact('integral', 'now_money', 'shopcount', 'order', 'lastorder');
  880. }
  881. /*
  882. * 获取 会员业务
  883. * 会员总余额 会员总积分
  884. * $where 查询条件
  885. *
  886. * return array
  887. */
  888. public static function getUserBusinesHeade($where)
  889. {
  890. return [
  891. [
  892. 'name' => '会员总余额',
  893. 'field' => '元',
  894. 'count' => self::getModelTime($where, self::where('status', 1))->sum('now_money'),
  895. 'background_color' => 'layui-bg-cyan',
  896. 'col' => 6,
  897. ],
  898. [
  899. 'name' => '会员总积分',
  900. 'field' => '分',
  901. 'count' => self::getModelTime($where, self::where('status', 1))->sum('integral'),
  902. 'background_color' => 'layui-bg-cyan',
  903. 'col' => 6
  904. ]
  905. ];
  906. }
  907. /*
  908. * 分销会员头部信息查询获取
  909. *
  910. * 分销商总佣金余额
  911. * 分销商总提现佣金
  912. * 本月分销商业务佣金
  913. * 本月分销商佣金提现金额
  914. * 上月分销商业务佣金
  915. * 上月分销商佣金提现金额
  916. * $where array 时间条件
  917. *
  918. * return array
  919. */
  920. public static function getDistributionBadgeList($where)
  921. {
  922. return [
  923. [
  924. 'name' => '分销商总佣金',
  925. 'field' => '元',
  926. 'count' => self::getModelTime($where, UserBill::where('category', 'now_money')->where('type', 'brokerage'))->where('uid', 'in', function ($query) {
  927. $query->name('user')->where('status', 1)->where('is_promoter', 1)->whereOr('spread_uid', 'neq', 0)->field('uid');
  928. })->sum('number'),
  929. 'background_color' => 'layui-bg-cyan',
  930. 'col' => 3,
  931. ],
  932. [
  933. 'name' => '分销商总佣金余额',
  934. 'field' => '元',
  935. 'count' => self::getModelTime($where, self::where('status', 1)->where('is_promoter', 1))->sum('now_money'),
  936. 'background_color' => 'layui-bg-cyan',
  937. 'col' => 3,
  938. ],
  939. [
  940. 'name' => '分销商总提现佣金',
  941. 'field' => '元',
  942. 'count' => self::getModelTime($where, UserExtract::where('status', 1))->sum('extract_price'),
  943. 'background_color' => 'layui-bg-cyan',
  944. 'col' => 3,
  945. ],
  946. [
  947. 'name' => '本月分销商业务佣金',
  948. 'field' => '元',
  949. 'count' => self::getModelTime(['data' => 'month'], UserBill::where('category', 'now_money')->where('type', 'brokerage'))
  950. ->where('uid', 'in', function ($query) {
  951. $query->name('user')->where('status', 1)->where('is_promoter', 1)->whereOr('spread_uid', 'neq', 0)->field('uid');
  952. })->sum('number'),
  953. 'background_color' => 'layui-bg-cyan',
  954. 'col' => 3,
  955. ],
  956. [
  957. 'name' => '本月分销商佣金提现金额',
  958. 'field' => '元',
  959. 'count' => self::getModelTime(['data' => 'month'], UserExtract::where('status', 1))
  960. ->where('uid', 'in', function ($query) {
  961. $query->name('user')->where('status', 1)->where('is_promoter', 1)->field('uid');
  962. })->sum('extract_price'),
  963. 'background_color' => 'layui-bg-cyan',
  964. 'col' => 4,
  965. ],
  966. [
  967. 'name' => '上月分销商业务佣金',
  968. 'field' => '元',
  969. 'count' => self::getOldDate(['data' => 'year'], UserBill::where('category', 'now_money')->where('uid', 'in', function ($query) {
  970. $query->name('user')->where('status', 1)->where('is_promoter', 1)->whereOr('spread_uid', 'neq', 0)->field('uid');
  971. })->where('type', 'brokerage'))->sum('number'),
  972. 'background_color' => 'layui-bg-cyan',
  973. 'col' => 4,
  974. ],
  975. [
  976. 'name' => '上月分销商佣金提现金额',
  977. 'field' => '元',
  978. 'count' => self::getOldDate(['data' => 'year'], UserBill::where('category', 'now_money')->where('uid', 'in', function ($query) {
  979. $query->name('user')->where('status', 1)->where('is_promoter', 1)->whereOr('spread_uid', 'neq', 0)->field('uid');
  980. })->where('type', 'brokerage'))->sum('number'),
  981. 'background_color' => 'layui-bg-cyan',
  982. 'col' => 4,
  983. ],
  984. ];
  985. }
  986. /*
  987. * 分销会员
  988. * 分销数量 饼状图
  989. * 分销商会员访问量 曲线
  990. * 获取购物会员人数趋势图 曲线
  991. * 多次购物分销会员数量 饼状图
  992. * $where array 条件
  993. * $limit int n条数据后出拖动条
  994. * return array
  995. */
  996. public static function getUserDistributionChart($where, $limit = 20)
  997. {
  998. //分销数量
  999. $fenbu_user = self::getModelTime($where, new self)->field(['count(uid) as num'])->group('is_promoter')->select();
  1000. count($fenbu_user) && $fenbu_user = $fenbu_user->toArray();
  1001. $sum_user = 0;
  1002. $fenbu_data = [];
  1003. $fenbu_xdata = ['分销商', '非分销商'];
  1004. $color = ['#81BCFE', '#91F3FE'];
  1005. foreach ($fenbu_user as $item) {
  1006. $sum_user += $item['num'];
  1007. }
  1008. foreach ($fenbu_user as $key => $item) {
  1009. $value['value'] = bcdiv($item['num'], $sum_user, 2) * 100;
  1010. $value['name'] = isset($fenbu_xdata[$key]) ? $fenbu_xdata[$key] . ' %' . $value['value'] : '';
  1011. $value['itemStyle']['color'] = $color[$key];
  1012. $fenbu_data[] = $value;
  1013. }
  1014. //分销商会员访问量
  1015. $visit = self::getModelTime($where, self::alias('a')->join('store_visit t', 't.uid=a.uid'), 't.add_time')
  1016. ->where('a.is_promoter', 1)
  1017. ->field(['FROM_UNIXTIME(t.add_time,"%Y-%m-%d") as _add_time', 'count(t.uid) as count_user'])
  1018. ->group('_add_time')
  1019. ->order('_add_time asc')
  1020. ->select();
  1021. count($visit) && $visit = $visit->toArray();
  1022. $visit_data = [];
  1023. $visit_xdata = [];
  1024. $visit_zoom = '';
  1025. foreach ($visit as $item) {
  1026. $visit_data[] = $item['count_user'];
  1027. $visit_xdata[] = $item['_add_time'];
  1028. }
  1029. count($visit_xdata) > $limit && $visit_zoom = $visit_xdata[$limit - 5];
  1030. //获取购物会员人数趋势图
  1031. $list = self::getModelTime($where, self::where('a.status', 1)->alias('a')->join('store_order r', 'r.uid=a.uid'), 'a.add_time')
  1032. ->where(['r.paid' => 1, 'a.is_promoter' => 1])
  1033. ->where('a.add_time', 'neq', 0)
  1034. ->field(['FROM_UNIXTIME(a.add_time,"%Y-%m-%d") as _add_time', 'count(r.uid) as count_user'])
  1035. ->group('_add_time')
  1036. ->order('_add_time asc')
  1037. ->select();
  1038. count($list) && $list = $list->toArray();
  1039. $seriesdata = [];
  1040. $xdata = [];
  1041. $zoom = '';
  1042. foreach ($list as $item) {
  1043. $seriesdata[] = $item['count_user'];
  1044. $xdata[] = $item['_add_time'];
  1045. }
  1046. count($xdata) > $limit && $zoom = $xdata[$limit - 5];
  1047. //多次购物分销会员数量饼状图
  1048. $count = self::getModelTime($where, self::where('is_promoter', 1))->count();
  1049. $user_count = self::getModelTime($where, self::alias('a')
  1050. ->join('store_order r', 'r.uid=a.uid'), 'a.add_time')
  1051. ->where('a.is_promoter', 1)
  1052. ->where('r.paid', 1)
  1053. ->group('a.uid')
  1054. ->count();
  1055. $shop_xdata = ['多次购买数量占比', '无购买数量占比'];
  1056. $shop_data = [];
  1057. $count > 0 && $shop_data = [
  1058. [
  1059. 'value' => bcdiv($user_count, $count, 2) * 100,
  1060. 'name' => $shop_xdata[0] . $user_count . '人',
  1061. 'itemStyle' => [
  1062. 'color' => '#D789FF',
  1063. ]
  1064. ],
  1065. [
  1066. 'value' => bcdiv($count - $user_count, $count, 2) * 100,
  1067. 'name' => $shop_xdata[1] . ($count - $user_count) . '人',
  1068. 'itemStyle' => [
  1069. 'color' => '#7EF0FB',
  1070. ]
  1071. ]
  1072. ];
  1073. return compact('fenbu_data', 'fenbu_xdata', 'visit_data', 'visit_xdata', 'visit_zoom', 'seriesdata', 'xdata', 'zoom', 'shop_xdata', 'shop_data');
  1074. }
  1075. /*
  1076. * 分销商佣金提现排行榜
  1077. * 分销商佣金总额排行榜
  1078. * $limit 截取条数
  1079. * return array
  1080. */
  1081. public static function getUserDistributionTop10List($limit)
  1082. {
  1083. //分销商佣金提现排行榜
  1084. $extract = self::alias('a')
  1085. ->join('user_extract t', 'a.uid=t.uid')
  1086. ->where(['t.status' => 1, 'a.is_promoter' => 1])
  1087. ->group('t.uid')
  1088. ->field(['a.nickname', 'a.phone', 'sum(t.extract_price) as sum_price', 'FROM_UNIXTIME(a.add_time,"%Y-%m-%d") as add_time', 't.uid'])
  1089. ->order('sum_price desc')
  1090. ->limit($limit)
  1091. ->select();
  1092. count($extract) && $extract = $extract->toArray();
  1093. //分销商佣金总额排行榜
  1094. $commission = UserBill::alias('l')
  1095. ->join('user a', 'l.uid=a.uid')
  1096. ->where(['l.status' => '1', 'l.category' => 'now_money', 'l.type' => 'brokerage', 'a.is_promoter' => 1])
  1097. ->group('l.uid')
  1098. ->field(['a.nickname', 'a.phone', 'sum(number) as sum_number', 'FROM_UNIXTIME(a.add_time,"%Y-%m-%d") as add_time'])
  1099. ->order('sum_number desc')
  1100. ->limit($limit)
  1101. ->select();
  1102. count($commission) && $commission = $commission->toArray();
  1103. return compact('extract', 'commission');
  1104. }
  1105. public static function getSpreadList($uid, $page, $limit)
  1106. {
  1107. $list = self::where(['spread_uid' => $uid])->field(['uid', 'nickname', 'now_money', 'gold_num', 'add_time'])
  1108. ->order('uid desc')->page((int)$page, (int)$limit)->select();
  1109. count($list) && $list = $list->toArray();
  1110. $isPhoneMenu = is_phone_menu();
  1111. foreach ($list as &$item) {
  1112. if (check_phone($item['nickname']) && !$isPhoneMenu) {
  1113. $item['nickname'] = substr_replace($item['nickname'], '****', 3, 4);
  1114. }
  1115. $item['add_time'] = date('Y-m-d H', $item['add_time']);
  1116. }
  1117. return $list;
  1118. }
  1119. /**
  1120. * 设置推广人查询条件
  1121. * @param $where
  1122. * @param string $alias
  1123. * @param int $spread_type
  1124. * @param null $model
  1125. * @return $this
  1126. */
  1127. public static function setSpreadBadgeWhere($where, $alias = '', $spread_type = 0, $model = null)
  1128. {
  1129. $model = is_null($model) ? new self() : $model;
  1130. $alias = $alias ? $alias . '.' : '';
  1131. if ($spread_type) $where['spread_type'] = $spread_type;
  1132. if ($where['nickname'] && ($uids = self::where('nickname|phone|uid', 'like', "%$where[nickname]%")->column('uid'))) {
  1133. $model = $model->where($alias . 'spread_uid', 'in', $uids);
  1134. }
  1135. if ($where['phone']) $model = $model->where("{$alias}nickname|{$alias}phone", 'like', "%$where[phone]%");
  1136. if ($where['start_time'] && $where['stop_time']) {
  1137. $model = $model->whereTime("{$alias}add_time", 'between', [$where['start_time'], $where['stop_time']]);
  1138. }
  1139. $storeBrokerageStatu = SystemConfigService::get('store_brokerage_statu') ?: 1;//获取后台分销类型
  1140. if ($storeBrokerageStatu == 1) {
  1141. $model = $model->where("{$alias}is_promoter", 1);
  1142. }
  1143. return $model;
  1144. }
  1145. public static function getextractPrice($uid, $where = [])
  1146. {
  1147. if (is_array($uid)) {
  1148. if (!count($uid)) return 0;
  1149. } else
  1150. $uid = [$uid];
  1151. $brokerage = UserBill::getBrokerage($uid, 'now_money', 'brokerage', $where);//获取总佣金
  1152. $return = UserBill::getReturnBrokerage($uid, 'now_money', 'brokerage_return', $where);//获取返还佣金
  1153. $brokerage = bcsub($brokerage, $return, 2);
  1154. $recharge = UserBill::getBrokerage($uid, 'now_money', 'recharge', $where);//累计充值
  1155. $extractTotalPrice = UserExtract::userExtractTotalPrice($uid, 1, $where);//累计提现
  1156. if ($brokerage > $extractTotalPrice) {
  1157. $orderYuePrice = self::getModelTime($where, StoreOrder::where('uid', 'in', $uid)->where(['paid' => 1, 'refund_status' => 0, 'is_del' => 0])->where('type', 'in', '0,1'))->sum('pay_price');//余额累计消费
  1158. $systemAdd = UserBill::getBrokerage($uid, 'now_money', 'system_add', $where);//后台添加余额
  1159. $yueCount = bcadd($recharge, $systemAdd, 2);// 后台添加余额 + 累计充值 = 非佣金的总金额
  1160. $orderYuePrice = $yueCount > $orderYuePrice ? 0 : bcsub($orderYuePrice, $yueCount, 2);// 余额累计消费(使用佣金消费的金额)
  1161. $brokerage = bcsub($brokerage, $extractTotalPrice, 2);//减去已提现金额
  1162. $extract_price = UserExtract::userExtractTotalPrice($uid, 0, $where);
  1163. $brokerage = $extract_price < $brokerage ? bcsub($brokerage, $extract_price, 2) : 0;//减去审核中的提现金额
  1164. $brokerage = $brokerage > $orderYuePrice ? bcsub($brokerage, $orderYuePrice, 2) : 0;//减掉余额支付
  1165. } else {
  1166. $brokerage = 0;
  1167. }
  1168. $num = (float)bcsub($brokerage, $extractTotalPrice, 2);
  1169. return $num > 0 ? $num : 0;//可提现
  1170. }
  1171. /**
  1172. * 获取推广人列表
  1173. * @param array $where 查询条件
  1174. * @return array
  1175. * */
  1176. public static function SpreadList($where)
  1177. {
  1178. $model = self::setSpreadBadgeWhere($where)->field('phone,uid,nickname,add_time,spread_uid,is_promoter,is_senior');
  1179. if ($where['export']) $data = $model->select();
  1180. else $data = $model->page((int)$where['page'], (int)$where['limit'])->select();
  1181. $data = count($data) ? $data->toArray() : [];
  1182. foreach ($data as &$item) {
  1183. $item['spread_nickname'] = self::where('uid', $item['spread_uid'])->value('nickname');
  1184. $item['spread_name'] = '普通';
  1185. //直推订单
  1186. $uids = self::where('spread_uid', $item['uid'])->column('uid');
  1187. if (count($uids)) {
  1188. $item['sum_pay_price'] = StoreOrder::whereIn('uid', $uids)->where(['paid' => 1, 'refund_status' => 0, 'is_del' => 0])->where('type', 'in', '0,1')->sum('pay_price');
  1189. $ids = self::whereIn('spread_uid', $uids)->column('uid');
  1190. if (count($ids)) {
  1191. $item['pay_price'] = StoreOrder::whereIn('uid', $ids)->where(['paid' => 1, 'refund_status' => 0, 'is_del' => 0])->where('type', 'in', '0,1')->sum('pay_price');
  1192. }
  1193. } else {
  1194. $item['sum_pay_price'] = 0;
  1195. $item['pay_price'] = 0;
  1196. }
  1197. unset($ids, $uids);
  1198. $item['rake_back'] = UserBill::getCommissionAmount($item['uid']);
  1199. $item['add_time'] = date('Y-m-d H:i:s', $item['add_time']);
  1200. }
  1201. if ($where['export']) self::SaveExcel($data);
  1202. $count = self::setSpreadBadgeWhere($where)->count();
  1203. return compact('data', 'count');
  1204. }
  1205. /*
  1206. * 保存并下载excel
  1207. * $list array
  1208. * return
  1209. */
  1210. public static function SaveExcel($list)
  1211. {
  1212. $export = [];
  1213. foreach ($list as $index => $item) {
  1214. $export[] = [
  1215. $item['spread_name'],
  1216. $item['nickname'],
  1217. $item['spread_nickname'],
  1218. $item['phone'],
  1219. $item['add_time'],
  1220. $item['sum_pay_price'],
  1221. $item['rake_back']
  1222. ];
  1223. }
  1224. $filename = '推广人列表导出' . time() . '.xlsx';
  1225. $head = ['推广人身份', '昵称', '所属上级', '手机号码', '加入时间', '订单金额', '佣金'];
  1226. PhpSpreadsheetService::outdata($filename, $export, $head);
  1227. }
  1228. public static function guestWhere($where, $guest, $model = null)
  1229. {
  1230. if ($model == null) $model = new self;
  1231. if (isset($where['guest_name']) && $where['guest_name'] != '') $model = $model->where('nickname|uid', 'LIKE', "%$where[guest_name]%");
  1232. $model = $model->where('uid', 'IN', $guest->guest);
  1233. return $model;
  1234. }
  1235. //分销列表
  1236. public static function setSpreadWhere($where = [], $alias = 'u', $model = null)
  1237. {
  1238. $model = is_null($model) ? new self() : $model;
  1239. if ($alias) {
  1240. $model = $model->alias($alias)->order("u.uid desc");
  1241. $alias .= '.';
  1242. }
  1243. $status = (int)SystemConfigService::get('store_brokerage_statu');
  1244. if ($status == 1) {
  1245. $model = $model->where("{$alias}is_promoter", 1);
  1246. }
  1247. if ($where['nickname'] !== '') $model = $model->where("{$alias}nickname|{$alias}uid|{$alias}phone", 'LIKE', "%$where[nickname]%");
  1248. if ((isset($where['start_time']) && isset($where['end_time'])) && $where['start_time'] !== '' && $where['end_time'] !== '') {
  1249. $model = $model->where("{$alias}add_time", 'between', [strtotime($where['start_time']), strtotime($where['end_time'])]);
  1250. }
  1251. if (isset($where['order']) && $where['order'] != '') $model = $model->order($where['order']);
  1252. if (isset($where['is_time']) && isset($where['data']) && $where['data']) $model = self::getModelTime($where, $model, $alias . 'add_time');
  1253. return $model;
  1254. }
  1255. /**
  1256. * 获取推广人数
  1257. * @param $uid //用户的uid
  1258. * @param int $spread
  1259. * $spread 0 一级推广人数 1 二级推广人数
  1260. * @return int|string
  1261. */
  1262. public static function getUserSpreadUidCount($uid, $spread = 1)
  1263. {
  1264. $userStair = self::where('spread_uid', $uid)->column('uid', 'uid');//获取一级推家人
  1265. if ($userStair) {
  1266. if (!$spread) return count($userStair);//返回一级推人人数
  1267. else return self::where('spread_uid', 'IN', implode(',', $userStair))->count();//二级推荐人数
  1268. } else return 0;
  1269. }
  1270. /**
  1271. * 获取推广人的订单
  1272. * @param $uid
  1273. * @param int $spread
  1274. * $spread 0 一级推广总订单 1 所有推广总订单
  1275. * @return int|string
  1276. */
  1277. public static function getUserSpreadOrderCount($uid, $spread = 1)
  1278. {
  1279. $userStair = self::where('spread_uid', $uid)->column('uid', 'uid');//获取一级推家人uid
  1280. if ($userStair) {
  1281. if (!$spread) {
  1282. return StoreOrder::where('uid', 'IN', implode(',', $userStair))->where(['paid' => 1, 'refund_status' => 0, 'is_del' => 0])->where('type', 'in', '0,1')->count();//获取一级推广人订单数
  1283. } else {
  1284. $userSecond = self::where('spread_uid', 'IN', implode(',', $userStair))->column('uid', 'uid');//二级推广人的uid
  1285. if ($userSecond) {
  1286. return StoreOrder::where('uid', 'IN', implode(',', $userSecond))->where(['paid' => 1, 'refund_status' => 0, 'is_del' => 0])->where('type', 'in', '0,1')->count();//获取二级推广人订单数
  1287. } else return 0;
  1288. }
  1289. } else return 0;
  1290. }
  1291. /**
  1292. * 获取分销用户
  1293. * @param array $where
  1294. * @return array
  1295. */
  1296. public static function agentSystemPage($where = array())
  1297. {
  1298. $where['is_time'] = 1;
  1299. $model = self::setSpreadWhere($where, 'u');
  1300. $status = SystemConfigService::get('store_brokerage_statu');
  1301. if (isset($where['excel']) && $where['excel'] == 1) {
  1302. $list = $model->field(['u.uid', 'u.phone', 'u.nickname', 'u.brokerage_price'])->select()->toArray();
  1303. $export = [];
  1304. foreach ($list as $index => $item) {
  1305. $Listuids = self::getModelTime($where, self::where('spread_uid', $item['uid']))->field('uid')->select();
  1306. $newUids = [];
  1307. foreach ($Listuids as $val) {
  1308. $newUids[] = $val['uid'];
  1309. }
  1310. $uids = $newUids;
  1311. unset($uid, $newUids);
  1312. $item['spread_count'] = count($uids);
  1313. if (count($uids)) {
  1314. $ListUidTwo = self::where('spread_uid', 'in', $uids)->field('uid')->select();
  1315. $newUids = [];
  1316. foreach ($ListUidTwo as $val) {
  1317. $newUids[] = $val['uid'];
  1318. }
  1319. $uidTwo = $newUids;
  1320. unset($uid, $newUids);
  1321. $uids = array_merge($uids, $uidTwo);
  1322. $uids = array_unique($uids);
  1323. $uids = array_merge($uids);
  1324. }
  1325. $item['extract_sum_price'] = self::getModelTime($where, UserExtract::where('uid', $item['uid']))->sum('extract_price');
  1326. $item['extract_count_price'] = UserExtract::getUserCountPrice($item['uid']);//累计提现金额
  1327. $item['extract_count_num'] = UserExtract::getUserCountNum($item['uid'], $where);//提现次数
  1328. $item['order_price'] = count($uids) ? StoreOrder::where('uid', 'in', $uids)->where(['paid' => 1, 'refund_status' => 0, 'is_del' => 0])->where('type', 'in', '0,1')->sum('pay_price') : 0;//订单金额
  1329. $item['order_count'] = count($uids) ? StoreOrder::where('uid', 'in', $uids)->where(['paid' => 1, 'refund_status' => 0, 'is_del' => 0])->where('type', 'in', '0,1')->count() : 0;//订单数量
  1330. //可提现佣金
  1331. $item['new_money'] = $item['brokerage_price'];
  1332. //总共佣金
  1333. $income = self::getModelTime($where, UserBill::where(['uid' => $item['uid'], 'category' => 'now_money', 'type' => 'brokerage', 'pm' => 1, 'status' => 1]))->sum('number');
  1334. $return = self::getModelTime($where, UserBill::where(['uid' => $item['uid'], 'category' => 'now_money', 'type' => 'brokerage_return', 'pm' => 0, 'status' => 1]))->sum('number');
  1335. $item['brokerage_money'] = bcsub($income, $return, 2);
  1336. $item['spread_name'] = '暂无';
  1337. if ($spread_uid = self::where('uid', $item['uid'])->value('spread_uid')) {
  1338. if ($user = self::where('uid', $spread_uid)->field(['uid', 'nickname'])->find()) {
  1339. $item['spread_name'] = $user['nickname'] . '/' . $user['uid'];
  1340. }
  1341. }
  1342. $export[] = [
  1343. $item['uid'],
  1344. $item['nickname'],
  1345. $item['phone'],
  1346. $item['spread_count'],
  1347. $item['order_count'],
  1348. $item['order_price'],
  1349. $item['brokerage_money'],
  1350. $item['extract_count_price'],
  1351. $item['extract_count_num'],
  1352. $item['new_money'],
  1353. $item['spread_name']
  1354. ];
  1355. }
  1356. $filename = '推广用户导出' . time() . '.xlsx';
  1357. $head = ['用户编号', '昵称', '电话号码', '推广用户数量', '订单数量', '推广订单金额', '佣金金额', '已提现金额', '提现次数', '未提现金额', '上级推广人'];
  1358. PhpSpreadsheetService::outdata($filename, $export, $head);
  1359. }
  1360. $data = $model->page((int)$where['page'], (int)$where['limit'])->select();
  1361. $data = count($data) ? $data->toArray() : [];
  1362. foreach ($data as &$item) {
  1363. if ((int)$status == 2) $item['is_show'] = false;
  1364. else $item['is_show'] = true;
  1365. $Listuids = self::getModelTime($where, self::where('spread_uid', $item['uid']))->field('uid')->select();
  1366. $newUids = [];
  1367. foreach ($Listuids as $val) {
  1368. $newUids[] = $val['uid'];
  1369. }
  1370. $uids = $newUids;
  1371. unset($uid, $newUids);
  1372. $item['spread_count'] = count($uids);
  1373. if (count($uids)) {
  1374. $ListUidTwo = self::where('spread_uid', 'in', $uids)->field('uid')->select();
  1375. $newUids = [];
  1376. foreach ($ListUidTwo as $val) {
  1377. $newUids[] = $val['uid'];
  1378. }
  1379. $uidTwo = $newUids;
  1380. unset($uid, $newUids);
  1381. $uids = array_merge($uids, $uidTwo);
  1382. $uids = array_unique($uids);
  1383. $uids = array_merge($uids);
  1384. }
  1385. $item['extract_sum_price'] = self::getModelTime($where, UserExtract::where('uid', $item['uid']))->sum('extract_price');
  1386. $item['extract_count_price'] = UserExtract::getUserCountPrice($item['uid']);//累计提现金额
  1387. $item['extract_count_num'] = UserExtract::getUserCountNum($item['uid'], $where);//提现次数
  1388. $item['order_price'] = count($uids) ? StoreOrder::where('uid', 'in', $uids)->where(['paid' => 1, 'refund_status' => 0, 'is_del' => 0])->where('type', 'in', '0,1')->sum('pay_price') : 0;//订单金额
  1389. $item['order_count'] = count($uids) ? StoreOrder::where('uid', 'in', $uids)->where(['paid' => 1, 'refund_status' => 0, 'is_del' => 0])->where('type', 'in', '0,1')->count() : 0;//订单数量
  1390. $item['spread_name'] = '暂无';
  1391. if ($spread_uid = self::where('uid', $item['uid'])->value('spread_uid')) {
  1392. if ($user = self::where('uid', $spread_uid)->field(['uid', 'nickname'])->find()) {
  1393. $item['spread_name'] = $user['nickname'] . '/' . $user['uid'];
  1394. }
  1395. }
  1396. $income = self::getModelTime($where, UserBill::where(['uid' => $item['uid'], 'category' => 'now_money', 'type' => 'brokerage', 'pm' => 1, 'status' => 1]))->sum('number');
  1397. $return = self::getModelTime($where, UserBill::where(['uid' => $item['uid'], 'category' => 'now_money', 'type' => 'brokerage_return', 'pm' => 0, 'status' => 1]))->sum('number');
  1398. //总共佣金
  1399. $item['brokerage_money'] = bcsub($income, $return, 2);
  1400. //可提现佣金
  1401. $item['new_money'] = $item['brokerage_price'];
  1402. }
  1403. $count = self::setSpreadWhere($where)->count();
  1404. return compact('data', 'count');
  1405. }
  1406. public static function getSpreadBadge($where)
  1407. {
  1408. $where['is_time'] = 1;
  1409. $listuids = self::setSpreadWhere($where)->field('u.uid')->select();
  1410. $newUids = [];
  1411. foreach ($listuids as $item) {
  1412. $newUids[] = $item['uid'];
  1413. }
  1414. $uids = $newUids;
  1415. unset($uid, $newUids);
  1416. //分销员人数
  1417. $data['sum_count'] = count($uids);
  1418. $data['spread_sum'] = 0;
  1419. $data['order_count'] = 0;
  1420. $data['pay_price'] = 0;
  1421. $data['number'] = 0;
  1422. $data['extract_count'] = 0;
  1423. $data['extract_price'] = 0;
  1424. if ($data['sum_count']) {
  1425. //发展会员人数
  1426. $data['spread_sum'] = self::where('spread_uid', 'in', $uids)->count();
  1427. //订单总数
  1428. $data['order_count'] = StoreOrder::where('uid', 'in', $uids)->where(['paid' => 1, 'refund_status' => 0, 'is_del' => 0])->where('type', 'in', '0,1')->count();
  1429. //订单金额
  1430. $data['pay_price'] = StoreOrder::where('uid', 'in', $uids)->where(['paid' => 1, 'refund_status' => 0, 'is_del' => 0])->where('type', 'in', '0,1')->sum('pay_price');
  1431. //可提现金额
  1432. $data['number'] = self::where('uid', 'in', $uids)->sum('brokerage_price');
  1433. //提现次数
  1434. $data['extract_count'] = UserExtract::where('uid', 'in', $uids)->count();
  1435. //获取某个用户可提现金额
  1436. $data['extract_price'] = self::getextractPrice($uids, $where);
  1437. }
  1438. return [
  1439. [
  1440. 'name' => '分销员数量',
  1441. 'field' => '人',
  1442. 'count' => $data['sum_count'],
  1443. 'background_color' => 'layui-bg-cyan',
  1444. 'col' => 2,
  1445. ],
  1446. [
  1447. 'name' => '发展人数',
  1448. 'field' => '人',
  1449. 'count' => $data['spread_sum'],
  1450. 'background_color' => 'layui-bg-cyan',
  1451. 'col' => 2,
  1452. ],
  1453. [
  1454. 'name' => '总订单数',
  1455. 'field' => '单',
  1456. 'count' => $data['order_count'],
  1457. 'background_color' => 'layui-bg-cyan',
  1458. 'col' => 2,
  1459. ],
  1460. [
  1461. 'name' => '总订单金额',
  1462. 'field' => '元',
  1463. 'count' => $data['pay_price'],
  1464. 'background_color' => 'layui-bg-cyan',
  1465. 'col' => 2,
  1466. ],
  1467. [
  1468. 'name' => '可提现金额',
  1469. 'field' => '元',
  1470. 'count' => $data['number'],
  1471. 'background_color' => 'layui-bg-cyan',
  1472. 'col' => 2,
  1473. ],
  1474. [
  1475. 'name' => '提现次数',
  1476. 'field' => '次',
  1477. 'count' => $data['extract_count'],
  1478. 'background_color' => 'layui-bg-cyan',
  1479. 'col' => 2,
  1480. ]
  1481. ];
  1482. }
  1483. public static function getStairList($where)
  1484. {
  1485. if (!isset($where['uid'])) return [];
  1486. $data = self::setSairWhere($where, new self())->order('add_time desc')->page((int)$where['page'], (int)$where['limit'])->select();
  1487. $data = count($data) ? $data->toArray() : [];
  1488. foreach ($data as &$item) {
  1489. $item['spread_count'] = self::where('spread_uid', $item['uid'])->count();
  1490. $item['order_count'] = StoreOrder::where('uid', $item['uid'])->where(['paid' => 1, 'refund_status' => 0, 'is_del' => 0])->where('type', 'in', '0,1')->count();
  1491. $item['promoter_name'] = $item['is_promoter'] ? '是' : '否';
  1492. $item['add_time'] = date("Y-m-d H:i:s", $item['add_time']);
  1493. }
  1494. $count = self::setSairWhere($where, new User())->count();
  1495. return compact('data', 'count');
  1496. }
  1497. /**
  1498. * 设置查询条件
  1499. * @param array $where
  1500. * @param object $model
  1501. * @param string $alias
  1502. * */
  1503. public static function setSairWhere($where, $model = null, $alias = '')
  1504. {
  1505. $model = $model === null ? new self() : $model;
  1506. if (!isset($where['uid'])) return $model;
  1507. if ($alias) {
  1508. $model = $model->alias($alias);
  1509. $alias .= '.';
  1510. }
  1511. if (isset($where['type'])) {
  1512. switch ((int)$where['type']) {
  1513. case 1:
  1514. $uids = self::where('spread_uid', $where['uid'])->column('uid');
  1515. if (count($uids))
  1516. $model = $model->where("{$alias}uid", 'in', $uids);
  1517. else
  1518. $model = $model->where("{$alias}uid", 0);
  1519. break;
  1520. case 2:
  1521. $uids = self::where('spread_uid', $where['uid'])->column('uid');
  1522. if (count($uids))
  1523. $spread_uid_two = self::where('spread_uid', 'in', $uids)->column('uid');
  1524. else
  1525. $spread_uid_two = [0];
  1526. if (count($spread_uid_two))
  1527. $model = $model->where("{$alias}uid", 'in', $spread_uid_two);
  1528. else
  1529. $model = $model->where("{$alias}uid", 0);
  1530. break;
  1531. default:
  1532. $uids = self::where('spread_uid', $where['uid'])->column('uid');
  1533. if (count($uids)) {
  1534. if ($spread_uid_two = self::where('spread_uid', 'in', $uids)->column('uid')) {
  1535. $uids = array_merge($uids, $spread_uid_two);
  1536. $uids = array_unique($uids);
  1537. $uids = array_merge($uids);
  1538. }
  1539. $model = $model->where("{$alias}uid", 'in', $uids);
  1540. } else
  1541. $model = $model->where("{$alias}uid", 0);
  1542. break;
  1543. }
  1544. }
  1545. if (isset($where['data']) && $where['data']) $model = self::getModelTime($where, $model, "{$alias}add_time");
  1546. if (isset($where['nickname']) && $where['nickname']) $model = $model->where("{$alias}phone|{$alias}nickname|{$alias}name|{$alias}uid", 'LIKE', "%$where[nickname]%");
  1547. return $model->where($alias . 'status', 1);
  1548. }
  1549. public static function getSairBadge($where)
  1550. {
  1551. $data['number'] = self::setSairWhere($where, new self())->count();
  1552. $where['type'] = 1;
  1553. $data['one_number'] = self::setSairWhere($where, new self())->count();
  1554. $where['type'] = 2;
  1555. $data['two_number'] = self::setSairWhere($where, new self())->count();
  1556. $col = $data['two_number'] > 0 ? 4 : 6;
  1557. return [
  1558. [
  1559. 'name' => '总人数',
  1560. 'field' => '人',
  1561. 'count' => $data['number'],
  1562. 'background_color' => 'layui-bg-cyan',
  1563. 'col' => $col,
  1564. ],
  1565. [
  1566. 'name' => '一级人数',
  1567. 'field' => '人',
  1568. 'count' => $data['one_number'],
  1569. 'background_color' => 'layui-bg-cyan',
  1570. 'col' => $col,
  1571. ],
  1572. [
  1573. 'name' => '二级人数',
  1574. 'field' => '人',
  1575. 'count' => $data['two_number'],
  1576. 'background_color' => 'layui-bg-cyan',
  1577. 'col' => $col,
  1578. ],
  1579. ];
  1580. }
  1581. /**
  1582. * 推广订单
  1583. * @param array $where
  1584. * @return array
  1585. * */
  1586. public static function getStairOrderList($where)
  1587. {
  1588. if (!isset($where['uid'])) return [];
  1589. $data = self::setSairOrderWhere($where, new StoreOrder())->page((int)$where['page'], (int)$where['limit'])->select();
  1590. $data = count($data) ? $data->toArray() : [];
  1591. $Info = self::where('uid', $where['uid'])->find();
  1592. foreach ($data as &$item) {
  1593. $userInfo = self::where('uid', $item['uid'])->find();
  1594. $item['user_info'] = '';
  1595. $item['avatar'] = '';
  1596. if ($userInfo) {
  1597. $item['user_info'] = $userInfo->nickname . '|' . ($userInfo->phone ? $userInfo->phone : '');
  1598. $item['avatar'] = $userInfo->avatar;
  1599. }
  1600. $item['spread_info'] = $Info->nickname . "|" . ($Info->phone ? $Info->phone . "|" : '') . $Info->uid;
  1601. $brokerage = UserBill::where(['category' => 'now_money', 'type' => 'brokerage', 'link_id' => $item['id'], 'uid' => $where['uid']])->value('number');
  1602. $brokerage_return = UserBill::where(['category' => 'now_money', 'type' => 'brokerage_return', 'link_id' => $item['id'], 'uid' => $where['uid']])->value('number');
  1603. $item['number_price'] = bcsub($brokerage, $brokerage_return, 2);
  1604. $item['_pay_time'] = date('Y-m-d H:i:s', $item['pay_time']);
  1605. $item['_add_time'] = date('Y-m-d H:i:s', $item['add_time']);
  1606. $item['take_time'] = ($change_time = StoreOrderStatus::where(['change_type' => 'user_take_delivery', 'oid' => $item['id']])->value('change_time')) ?
  1607. date('Y-m-d H:i:s', $change_time) : '暂无';
  1608. }
  1609. $count = self::setSairOrderWhere($where, new StoreOrder())->count();
  1610. return compact('data', 'count');
  1611. }
  1612. public static function setSairOrderWhere($where, $model = null, $alias = '')
  1613. {
  1614. $model = $model === null ? new self() : $model;
  1615. if (!isset($where['uid'])) return $model;
  1616. if ($alias) {
  1617. $model = $model->alias($alias);
  1618. $alias .= '.';
  1619. }
  1620. if (isset($where['type'])) {
  1621. switch ((int)$where['type']) {
  1622. case 1:
  1623. $uids = self::where('spread_uid', $where['uid'])->column('uid');
  1624. if (count($uids))
  1625. $model = $model->where("{$alias}uid", 'in', $uids);
  1626. else
  1627. $model = $model->where("{$alias}uid", 0);
  1628. break;
  1629. case 2:
  1630. $uids = self::where('spread_uid', $where['uid'])->column('uid');
  1631. if (count($uids))
  1632. $spread_uid_two = self::where('spread_uid', 'in', $uids)->column('uid');
  1633. else
  1634. $spread_uid_two = [0];
  1635. if (count($spread_uid_two))
  1636. $model = $model->where("{$alias}uid", 'in', $spread_uid_two);
  1637. else
  1638. $model = $model->where("{$alias}uid", 0);
  1639. break;
  1640. default:
  1641. $uids = self::where('spread_uid', $where['uid'])->column('uid');
  1642. if (count($uids)) {
  1643. if ($spread_uid_two = self::where('spread_uid', 'in', $uids)->column('uid')) {
  1644. $uids = array_merge($uids, $spread_uid_two);
  1645. $uids = array_unique($uids);
  1646. $uids = array_merge($uids);
  1647. }
  1648. $model = $model->where("{$alias}uid", 'in', $uids);
  1649. } else
  1650. $model = $model->where("{$alias}uid", 0);
  1651. break;
  1652. }
  1653. }
  1654. if (isset($where['data']) && $where['data']) $model = self::getModelTime($where, $model, "{$alias}add_time");
  1655. return $model->where("{$alias}is_del", 0)->where($alias . 'paid', 1)->where($alias . 'refund_status', 0)->where($alias . 'type', 'in', '0,1')->order($alias . 'add_time desc');
  1656. }
  1657. /*
  1658. * 推广订单统计
  1659. * @param array $where
  1660. * @return array
  1661. * */
  1662. public static function getStairOrderBadge($where)
  1663. {
  1664. if (!isset($where['uid'])) return [];
  1665. $data['order_count'] = self::setSairOrderWhere($where, new StoreOrder())->count();
  1666. $data['order_price'] = self::setSairOrderWhere($where, new StoreOrder())->sum('pay_price');
  1667. $ids = self::setSairOrderWhere($where, new StoreOrder())->where(['paid' => 1, 'is_del' => 0, 'refund_status' => 0])->where('type', 'in', '0,1')->column('id');
  1668. $data['number_price'] = 0;
  1669. if (count($ids)) {
  1670. $brokerage = UserBill::where(['category' => 'now_money', 'type' => 'brokerage', 'uid' => $where['uid']])->where('link_id', 'in', $ids)->sum('number');
  1671. $brokerage_return = UserBill::where(['category' => 'now_money', 'type' => 'brokerage_return', 'uid' => $where['uid']])->where('link_id', 'in', $ids)->sum('number');
  1672. $data['number_price'] = bcsub($brokerage, $brokerage_return, 2);
  1673. }
  1674. $where['type'] = 1;
  1675. $data['one_price'] = self::setSairOrderWhere($where, new StoreOrder())->sum('pay_price');
  1676. $data['one_count'] = self::setSairOrderWhere($where, new StoreOrder())->count();
  1677. $where['type'] = 2;
  1678. $data['two_price'] = self::setSairOrderWhere($where, new StoreOrder())->sum('pay_price');
  1679. $data['two_count'] = self::setSairOrderWhere($where, new StoreOrder())->count();
  1680. return [
  1681. [
  1682. 'name' => '总金额',
  1683. 'field' => '元',
  1684. 'count' => $data['order_price'],
  1685. 'background_color' => 'layui-bg-cyan',
  1686. 'col' => 3,
  1687. ],
  1688. [
  1689. 'name' => '订单总数',
  1690. 'field' => '单',
  1691. 'count' => $data['order_count'],
  1692. 'background_color' => 'layui-bg-cyan',
  1693. 'col' => 3,
  1694. ],
  1695. [
  1696. 'name' => '返佣总金额',
  1697. 'field' => '元',
  1698. 'count' => $data['number_price'],
  1699. 'background_color' => 'layui-bg-cyan',
  1700. 'col' => 3,
  1701. ],
  1702. [
  1703. 'name' => '一级总金额',
  1704. 'field' => '元',
  1705. 'count' => $data['one_price'],
  1706. 'background_color' => 'layui-bg-cyan',
  1707. 'col' => 3,
  1708. ],
  1709. [
  1710. 'name' => '一级订单数',
  1711. 'field' => '单',
  1712. 'count' => $data['one_count'],
  1713. 'background_color' => 'layui-bg-cyan',
  1714. 'col' => 3,
  1715. ],
  1716. [
  1717. 'name' => '二级总金额',
  1718. 'field' => '元',
  1719. 'count' => $data['two_price'],
  1720. 'background_color' => 'layui-bg-cyan',
  1721. 'col' => 3,
  1722. ],
  1723. [
  1724. 'name' => '二级订单数',
  1725. 'field' => '单',
  1726. 'count' => $data['two_count'],
  1727. 'background_color' => 'layui-bg-cyan',
  1728. 'col' => 3,
  1729. ],
  1730. ];
  1731. }
  1732. }