User.php 83 KB

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