UserBill.php 19 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463
  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 service\SystemConfigService;
  13. use traits\ModelTrait;
  14. use basic\ModelBasic;
  15. use app\admin\model\wechat\WechatUser;
  16. use service\PhpSpreadsheetService;
  17. /**
  18. * 用户消费新增金额明细 model
  19. * Class UserBill
  20. * @package app\admin\model\user
  21. */
  22. class UserBill extends ModelBasic
  23. {
  24. use ModelTrait;
  25. protected $insert = ['add_time'];
  26. protected function setAddTimeAttr()
  27. {
  28. return time();
  29. }
  30. public static function income($title, $uid, $category, $type, $number, $link_id = 0, $balance = 0, $mark = '', $status = 1)
  31. {
  32. $pm = 1;
  33. return self::set(compact('title', 'uid', 'link_id', 'category', 'type', 'number', 'balance', 'mark', 'status', 'pm'));
  34. }
  35. public static function expend($title, $uid, $category, $type, $number, $link_id = 0, $balance = 0, $mark = '', $status = 1)
  36. {
  37. $pm = 0;
  38. return self::set(compact('title', 'uid', 'link_id', 'category', 'type', 'number', 'balance', 'mark', 'status', 'pm'));
  39. }
  40. /*
  41. * 获取佣金记录
  42. * */
  43. public static function getBillList($where, $uid)
  44. {
  45. $model = self::where('uid', $uid)->where('category', 'now_money')->where('type', 'in', ['extract', 'extract_fail', 'brokerage', 'brokerage_return'])
  46. ->order('add_time desc');
  47. if ($where['start_date'] && $where['end_date']) $model = $model->where('add_time', 'between', [strtotime($where['start_date']), strtotime($where['end_date'])]);
  48. if ($where['excel'])
  49. $list = $model->select();
  50. else
  51. $list = $model->page((int)$where['page'], (int)$where['limit'])->select();
  52. $list = count($list) ? $list->toArray() : [];
  53. $excel = [];
  54. foreach ($list as &$item) {
  55. $item['add_time'] = date('Y-m-d H:i:s', $item['add_time']);
  56. $item['_type'] = $item['pm'] ? '入账' : '提现';
  57. if ($item['pm']) {
  58. $item['pay_pice'] = self::getDb('store_order')->where('id', $item['link_id'])->value('pay_price');
  59. } else {
  60. $item['pay_pice'] = 0;
  61. }
  62. $excel[] = [$item['add_time'], $item['_type'], $item['title'], $item['number'], $item['mark']];
  63. }
  64. if ($where['excel']) {
  65. $filename = '佣金记录导出' . time() . '.xlsx';
  66. $head = ['时间', '入账/结算', '记录说明', '佣金金额', '备注'];
  67. PhpSpreadsheetService::outdata($filename, $excel, $head);
  68. }
  69. return $list;
  70. }
  71. /**获取用户佣金金额
  72. * @param int $uid
  73. */
  74. public static function getCommissionAmount($uid = 0)
  75. {
  76. $brokerage = self::where('uid', 'in', $uid)->where('category', 'now_money')
  77. ->where('type', 'brokerage')->where('pm', 1)->where('status', 1)->sum('number');
  78. $brokerage_return = self::where('uid', 'in', $uid)->where('category', 'now_money')
  79. ->where('type', 'brokerage_return')->where('pm', 0)->where('status', 1)->sum('number');
  80. $commission = bcsub($brokerage, $brokerage_return, 2);
  81. return $commission;
  82. }
  83. /**
  84. * 获取柱状图和饼状图数据
  85. *
  86. * */
  87. public static function getUserBillChart($where, $category = 'now_money', $type = 'brokerage', $pm = 1, $zoom = 15)
  88. {
  89. $model = self::getModelTime($where, new self());
  90. $list = $model->field(['FROM_UNIXTIME(add_time,"%Y-%c-%d") as un_time', 'sum(number) as sum_number'])
  91. ->order('un_time asc')
  92. ->where(['category' => $category, 'type' => $type, 'pm' => $pm])
  93. ->group('un_time')
  94. ->select();
  95. if (count($list)) $list = $list->toArray();
  96. $legdata = [];
  97. $listdata = [];
  98. $dataZoom = '';
  99. foreach ($list as $item) {
  100. $legdata[] = $item['un_time'];
  101. $listdata[] = $item['sum_number'];
  102. }
  103. if (count($legdata) >= $zoom) $dataZoom = $legdata[$zoom - 1];
  104. //获取用户分布钱数
  105. $fenbulist = self::getModelTime($where, new self(), 'a.add_time')
  106. ->alias('a')
  107. ->join('user r', 'a.uid=r.uid')
  108. ->field(['a.uid', 'sum(a.number) as sum_number', 'r.nickname'])
  109. ->where(['a.category' => $category, 'a.type' => $type, 'a.pm' => $pm])
  110. ->order('sum_number desc')
  111. ->group('a.uid')
  112. ->limit(8)
  113. ->select();
  114. //获取用户当前时间段总钱数
  115. $sum_number = self::getModelTime($where, new self())
  116. ->where(['category' => $category, 'type' => $type, 'pm' => $pm])
  117. ->sum('number');
  118. if (count($fenbulist)) $fenbulist = $fenbulist->toArray();
  119. $fenbudate = [];
  120. $fenbu_legend = [];
  121. $color = ['#ffcccc', '#99cc00', '#fd99cc', '#669966', '#66CDAA', '#ADFF2F', '#00BFFF', '#00CED1', '#66cccc', '#ff9900', '#ffcc00', '#336699', '#cccc00', '#99ccff', '#990066'];
  122. foreach ($fenbulist as $key => $value) {
  123. $fenbu_legend[] = $value['nickname'];
  124. $items['name'] = $value['nickname'];
  125. $items['value'] = bcdiv($value['sum_number'], $sum_number, 2) * 100;
  126. $items['itemStyle']['color'] = $color[$key];
  127. $fenbudate[] = $items;
  128. }
  129. return compact('legdata', 'listdata', 'fenbudate', 'fenbu_legend', 'dataZoom');
  130. }
  131. //获取头部信息
  132. public static function getRebateBadge($where)
  133. {
  134. $datawhere = ['category' => 'now_money', 'type' => 'brokerage', 'pm' => 1];
  135. return [
  136. [
  137. 'name' => '返利数(笔)',
  138. 'field' => '个',
  139. 'count' => self::getModelTime($where, new self())->where($datawhere)->count(),
  140. 'content' => '返利总笔数',
  141. 'background_color' => 'layui-bg-blue',
  142. 'sum' => self::where($datawhere)->count(),
  143. 'class' => 'fa fa-bar-chart',
  144. ],
  145. [
  146. 'name' => '返利金额(元)',
  147. 'field' => '个',
  148. 'count' => self::getModelTime($where, new self())->where($datawhere)->sum('number'),
  149. 'content' => '返利总金额',
  150. 'background_color' => 'layui-bg-cyan',
  151. 'sum' => self::where($datawhere)->sum('number'),
  152. 'class' => 'fa fa-line-chart',
  153. ],
  154. ];
  155. }
  156. //获取返佣用户信息列表
  157. public static function getFanList($where)
  158. {
  159. $datawhere = ['a.category' => 'now_money', 'a.type' => 'brokerage', 'a.pm' => 1];
  160. $list = self::alias('a')->join('user r', 'a.uid=r.uid')
  161. ->where($datawhere)
  162. ->order('a.number desc')
  163. ->join('store_order o', 'o.id=a.link_id')
  164. ->field(['o.order_id', 'FROM_UNIXTIME(a.add_time,"%Y-%c-%d") as add_time', 'a.uid', 'o.uid as down_uid', 'r.nickname', 'r.avatar', 'r.spread_uid', 'r.level', 'a.number'])
  165. ->page((int)$where['page'], (int)$where['limit'])
  166. ->select();
  167. if (count($list)) $list = $list->toArray();
  168. return $list;
  169. }
  170. //获取返佣用户总人数
  171. public static function getFanCount()
  172. {
  173. $datawhere = ['a.category' => 'now_money', 'a.type' => 'brokerage', 'a.pm' => 1];
  174. return self::alias('a')->join('user r', 'a.uid=r.uid')->join('store_order o', 'o.id=a.link_id')->where($datawhere)->count();
  175. }
  176. //获取用户充值数据
  177. public static function getEchartsRecharge($where, $limit = 15)
  178. {
  179. $datawhere = ['category' => 'now_money', 'pm' => 1];
  180. $list = self::getModelTime($where, self::where($datawhere)->where('type', 'in', ['recharge', 'system_add']))
  181. ->field(['sum(number) as sum_money', 'FROM_UNIXTIME(add_time,"%Y-%c-%d") as un_time', 'count(id) as count'])
  182. ->group('un_time')
  183. ->order('un_time asc')
  184. ->select();
  185. if (count($list)) $list = $list->toArray();
  186. $sum_count = self::getModelTime($where, self::where($datawhere)->where('type', 'in', ['recharge', 'system_add']))->count();
  187. $xdata = [];
  188. $seriesdata = [];
  189. $data = [];
  190. $zoom = '';
  191. foreach ($list as $value) {
  192. $xdata[] = $value['un_time'];
  193. $seriesdata[] = $value['sum_money'];
  194. $data[] = $value['count'];
  195. }
  196. if (count($xdata) > $limit) {
  197. $zoom = $xdata[$limit - 5];
  198. }
  199. return compact('xdata', 'seriesdata', 'data', 'zoom');
  200. }
  201. //获取佣金提现列表
  202. public static function getExtrctOneList($where, $uid)
  203. {
  204. $list = self::setOneWhere($where, $uid)->order('add_time desc')->page($where['page'], $where['limit'])
  205. ->field(['number', 'link_id', 'mark', 'FROM_UNIXTIME(add_time,"%Y-%m-%d %H:%i:%s") as _add_time', 'status'])
  206. ->select();
  207. count($list) && $list = $list->toArray();
  208. $count = self::setOneWhere($where, $uid)->count();
  209. foreach ($list as &$value) {
  210. $value['order_id'] = db('store_order')->where(['order_id' => $value['link_id']])->value('order_id');
  211. }
  212. return ['data' => $list, 'count' => $count];
  213. }
  214. //设置单个用户查询
  215. public static function setOneWhere($where, $uid)
  216. {
  217. $model = self::where(['uid' => $uid, 'category' => 'now_money', 'type' => 'brokerage']);
  218. $time['data'] = '';
  219. if ($where['start_time'] != '' && $where['end_time'] != '') {
  220. $time['data'] = $where['start_time'] . ' - ' . $where['end_time'];
  221. $model = self::getModelTime($time, $model);
  222. }
  223. if ($where['nickname'] != '') {
  224. $model = $model->where('link_id|mark', 'like', "%$where[nickname]%");
  225. }
  226. return $model;
  227. }
  228. //查询积分个人明细
  229. public static function getOneIntegralList($where)
  230. {
  231. return self::setWhereList(
  232. $where,
  233. ['deduction', 'system_add'],
  234. ['title', 'number', 'balance', 'mark', 'FROM_UNIXTIME(add_time,"%Y-%m-%d") as add_time']
  235. );
  236. }
  237. //查询个人签到明细
  238. public static function getOneSignList($where)
  239. {
  240. return self::setWhereList(
  241. $where, 'sign',
  242. ['title', 'number', 'mark', 'FROM_UNIXTIME(add_time,"%Y-%m-%d") as add_time']
  243. );
  244. }
  245. //查询个人余额变动记录
  246. public static function getOneBalanceChangList($where)
  247. {
  248. $list = self::setWhereList(
  249. $where,
  250. ['system_add', 'pay_product', 'extract', 'extract_fail', 'pay_goods', 'pay_sign_up', 'pay_sign_up_refund', 'pay_product_refund', 'pay_data_download', 'pay_data_download_refund', 'pay_test_paper', 'pay_test_paper_refund', 'recharge', 'system_sub'],
  251. ['FROM_UNIXTIME(add_time,"%Y-%m-%d") as add_time', 'title', 'type', 'mark', 'number', 'balance', 'pm', 'status'],
  252. 'now_money'
  253. );
  254. foreach ($list as &$item) {
  255. switch ($item['type']) {
  256. case 'system_add':
  257. $item['_type'] = '系统添加';
  258. break;
  259. case 'pay_product':
  260. $item['_type'] = '商品购买';
  261. break;
  262. case 'extract':
  263. $item['_type'] = '提现';
  264. break;
  265. case 'extract_fail':
  266. $item['_type'] = '提现失败';
  267. break;
  268. case 'pay_goods':
  269. $item['_type'] = '购买商品';
  270. break;
  271. case 'pay_sign_up':
  272. $item['_type'] = '活动报名';
  273. break;
  274. case 'pay_product_refund':
  275. $item['_type'] = '退款';
  276. break;
  277. case 'system_sub':
  278. $item['_type'] = '系统减少';
  279. break;
  280. }
  281. $item['_pm'] = $item['pm'] == 1 ? '获得' : '支出';
  282. }
  283. return $list;
  284. }
  285. //设置where条件分页.返回数据
  286. public static function setWhereList($where, $type = '', $field = [], $category = 'gold_num')
  287. {
  288. $models = self::where('uid', $where['uid'])
  289. ->where('category', $category)
  290. ->page((int)$where['page'], (int)$where['limit'])
  291. ->field($field);
  292. if (is_array($type)) {
  293. $models = $models->where('type', 'in', $type);
  294. } else {
  295. $models = $models->where('type', $type);
  296. }
  297. return ($list = $models->order('id desc')->select()) && count($list) ? $list->toArray() : [];
  298. }
  299. //获取积分统计头部信息
  300. public static function getScoreBadgeList($where)
  301. {
  302. return [
  303. [
  304. 'name' => '总积分',
  305. 'field' => '个',
  306. 'count' => self::getModelTime($where, new self())->where('category', 'integral')->where('type', 'in', ['gain', 'system_sub', 'deduction', 'sign'])->sum('number'),
  307. 'background_color' => 'layui-bg-blue',
  308. 'col' => 4,
  309. ],
  310. [
  311. 'name' => '已使用积分',
  312. 'field' => '个',
  313. 'count' => self::getModelTime($where, new self())->where('category', 'integral')->where('type', 'deduction')->sum('number'),
  314. 'background_color' => 'layui-bg-cyan',
  315. 'col' => 4,
  316. ],
  317. [
  318. 'name' => '未使用积分',
  319. 'field' => '个',
  320. 'count' => self::getModelTime($where, db('user'))->sum('integral'),
  321. 'background_color' => 'layui-bg-cyan',
  322. 'col' => 4,
  323. ],
  324. ];
  325. }
  326. //获取积分统计曲线图和柱状图
  327. public static function getScoreCurve($where)
  328. {
  329. //发放积分趋势图
  330. $list = self::getModelTime($where, self::where('category', 'integral')
  331. ->field(['FROM_UNIXTIME(add_time,"%Y-%m-%d") as _add_time', 'sum(number) as sum_number'])
  332. ->group('_add_time')->order('_add_time asc'))->select()->toArray();
  333. $date = [];
  334. $zoom = '';
  335. $seriesdata = [];
  336. foreach ($list as $item) {
  337. $date[] = $item['_add_time'];
  338. $seriesdata[] = $item['sum_number'];
  339. }
  340. unset($item);
  341. if (count($date) > $where['limit']) {
  342. $zoom = $date[$where['limit'] - 5];
  343. }
  344. //使用积分趋势图
  345. $deductionlist = self::getModelTime($where, self::where('category', 'integral')->where('type', 'deduction')
  346. ->field(['FROM_UNIXTIME(add_time,"%Y-%m-%d") as _add_time', 'sum(number) as sum_number'])
  347. ->group('_add_time')->order('_add_time asc'))->select()->toArray();
  348. $deduction_date = [];
  349. $deduction_zoom = '';
  350. $deduction_seriesdata = [];
  351. foreach ($deductionlist as $item) {
  352. $deduction_date[] = $item['_add_time'];
  353. $deduction_seriesdata[] = $item['sum_number'];
  354. }
  355. if (count($deductionlist) > $where['limit']) {
  356. $deduction_zoom = $deductionlist[$where['limit'] - 5];
  357. }
  358. return compact('date', 'seriesdata', 'zoom', 'deduction_date', 'deduction_zoom', 'deduction_seriesdata');
  359. }
  360. public static function setBadgeWhere($model, $where, $alias = '', $like = '', $key = 'add_time')
  361. {
  362. $where['data'] = $where['start_time'] . ' - ' . $where['end_time'];
  363. $alias && $alias .= '.';
  364. $key = $alias ? $alias . $key : $key;
  365. $model = self::getModelTime($where, $model, $key);
  366. if ($where['nickname']) $model = $model->where($like, $where['nickname']);
  367. return $model;
  368. }
  369. public static function getGroupBadge($where)
  370. {
  371. $group_count = self::setBadgeWhere(Member::alias('a')->join('__USER__ u', 'u.uid=a.uid'), $where, 'a', 'u.uid')->count();
  372. $truePrice = bcmul($group_count - 1, 500, 2);
  373. $uids = self::setBadgeWhere(User::where(['a.status' => 1, 'a.is_partner' => 1])->alias('a')->join('group g', 'a.uid=g.uid'), $where, 'a', 'a.uid')->column('a.uid');
  374. $count = Group::where('share_uid', 'in', $uids)->group('share_uid')->count();
  375. $GroupCount = self::setBadgeWhere(new Group, $where, '', 'uid')->count();
  376. $freezing_amount = SystemConfigService::get('freezing_amount');
  377. $partner_money = SystemConfigService::get('partner_money');
  378. return [
  379. [
  380. 'name' => '总组数',
  381. 'field' => '个',
  382. 'count' => $group_count,
  383. 'background_color' => 'layui-bg-cyan',
  384. 'col' => 2,
  385. ],
  386. [
  387. 'name' => '总单数',
  388. 'field' => '单',
  389. 'count' => $GroupCount,
  390. 'background_color' => 'layui-bg-cyan',
  391. 'col' => 2,
  392. ],
  393. [
  394. 'name' => '总收入',
  395. 'field' => '元',
  396. 'count' => bcmul($GroupCount, $partner_money, 2),
  397. 'background_color' => 'layui-bg-cyan',
  398. 'col' => 2,
  399. ],
  400. [
  401. 'name' => '总支出',
  402. 'field' => '元',
  403. 'count' => self::setBadgeWhere(UserExtract::where('a.status', 1)->alias('a')->join('__USER__ u', 'u.uid=a.uid'), $where, 'a', 'u.uid')->sum('a.extract_price'),
  404. 'background_color' => 'layui-bg-cyan',
  405. 'col' => 2,
  406. ],
  407. [
  408. 'name' => '实际提现佣金',
  409. 'field' => '元',
  410. 'count' => self::setBadgeWhere(UserExtract::where('a.status', 1)->alias('a')
  411. ->join('__USER__ u', 'a.uid=u.uid'), $where, 'a', 'u.uid')->sum('a.extract_price'),
  412. 'background_color' => 'layui-bg-cyan',
  413. 'col' => 2,
  414. ],
  415. [
  416. 'name' => '未提现佣金',
  417. 'field' => '元',
  418. 'count' => self::setBadgeWhere(User::where('status', 1), $where, '', 'uid')->sum('now_money'),
  419. 'background_color' => 'layui-bg-cyan',
  420. 'col' => 2,
  421. ],
  422. [
  423. 'name' => '冻结佣金',
  424. 'field' => '元',
  425. 'count' => bcmul($count, $freezing_amount, 2),
  426. 'background_color' => 'layui-bg-cyan',
  427. 'col' => 2,
  428. ],
  429. [
  430. 'name' => '总盈利',
  431. 'field' => '元',
  432. 'count' => bcadd(bcmul($GroupCount, 500, 2), $truePrice, 2),
  433. 'background_color' => 'layui-bg-cyan',
  434. 'col' => 2,
  435. ],
  436. ];
  437. }
  438. }