FinanceModel.php 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334
  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\finance;
  12. use traits\ModelTrait;
  13. use basic\ModelBasic;
  14. use service\ExportService;
  15. use app\admin\model\user\UserBill;
  16. use app\admin\model\user\User;
  17. use service\PhpSpreadsheetService;
  18. use service\SystemConfigService;
  19. /**数据统计处理
  20. * Class FinanceModel
  21. * @package app\admin\model\finance
  22. */
  23. class FinanceModel extends ModelBasic
  24. {
  25. protected $name = 'user_bill';
  26. use ModelTrait;
  27. const bill = 'user_bill';
  28. /**
  29. * 处理金额
  30. * @param $where
  31. * @return array
  32. */
  33. public static function systemPage($where)
  34. {
  35. $model = new self;
  36. //翻页
  37. $limit = $where['limit'];
  38. $offset = $where['offset'];
  39. $limit = $offset . ',' . $limit;
  40. //排序
  41. $order = '';
  42. if (!empty($where['sort']) && !empty($where['sortOrder'])) {
  43. $order = $where['sort'] . ' ' . $where['sortOrder'];
  44. }
  45. unset($where['limit']);
  46. unset($where['offset']);
  47. unset($where['sort']);
  48. unset($where['sortOrder']);
  49. if (!empty($where['add_time'])) {
  50. list($startTime, $endTime) = explode(' - ', $where['add_time']);
  51. $where['add_time'] = array('between', [strtotime($startTime), strtotime($endTime)]);
  52. } else {
  53. $where['add_time'] = array('between', [strtotime(date('Y/m') . '/01'), strtotime(date('Y/m') . '/' . date('t'))]);
  54. }
  55. if (empty($where['title'])) {
  56. unset($where['title']);
  57. }
  58. $total = $model->where($where)->count();
  59. $rows = $model->where($where)->order($order)->limit($limit)->select()->each(function ($e) {
  60. return $e['add_time'] = date('Y-m-d H:i:s', $e['add_time']);
  61. })->toArray();
  62. return compact('total', 'rows');
  63. }
  64. public static function getBillList($where)
  65. {
  66. $data = ($data = self::setWhereList($where)->page((int)$where['page'], (int)$where['limit'])->select()) && count($data) ? $data->toArray() : [];
  67. if ($data) {
  68. foreach ($data as &$v) {
  69. if ($where['category'] != 'brokerage_price') {
  70. $v['category'] = self::get_category($v['category']);
  71. } else {
  72. $v['category'] = '佣金';
  73. }
  74. }
  75. }
  76. $count = self::setWhereList($where)->count();
  77. return compact('data', 'count');
  78. }
  79. public static function get_category($category)
  80. {
  81. $gold_name = SystemConfigService::get("gold_name");
  82. switch ($category) {
  83. case 'now_money':
  84. $title = '余额';
  85. break;
  86. case 'gold_num':
  87. $title = $gold_name;
  88. break;
  89. case 'weixin':
  90. $title = '微信';
  91. break;
  92. case 'zhifubao':
  93. $title = '支付宝';
  94. break;
  95. }
  96. return $title;
  97. }
  98. public static function SaveExport($where)
  99. {
  100. $data = ($data = self::setWhereList($where)->select()) && count($data) ? $data->toArray() : [];
  101. $export = [];
  102. foreach ($data as $value) {
  103. $category = self::get_category($value['category']);
  104. $export[] = [
  105. $value['uid'],
  106. $value['nickname'],
  107. $category,
  108. $value['pm'] == 0 ? '-' . $value['number'] : $value['number'],
  109. $value['title'],
  110. $value['mark'],
  111. $value['add_time'],
  112. ];
  113. }
  114. if ($where['category'] == 'now_money') {
  115. $type = '余额';
  116. } else {
  117. $type = '金币';
  118. }
  119. $filename = '资金监控' . time() . '.xlsx';
  120. $head = ['用户ID', '昵称', '支付类型', $type, '账单标题', '备注', '创建时间'];
  121. PhpSpreadsheetService::outdata($filename, $export, $head);
  122. }
  123. public static function setWhereList($where)
  124. {
  125. $time['data'] = '';
  126. if ($where['start_time'] != '' && $where['end_time'] != '') {
  127. $time['data'] = $where['start_time'] . ' - ' . $where['end_time'];
  128. }
  129. $model = self::getModelTime($time, self::alias('A')
  130. ->join('user B', 'B.uid=A.uid')
  131. ->order('A.add_time desc'), 'A.add_time');
  132. if ($where['category']) {
  133. $bill_where_op = self::bill_where_op($where['category']);
  134. if ($bill_where_op) {
  135. $model = $model->where('A.category', $bill_where_op['category']['op'], $bill_where_op['category']['condition']);
  136. }
  137. }
  138. if (trim($where['type']) != '') {
  139. $model = $model->where('A.type', $where['type']);
  140. } else {
  141. $model = $model->where('A.type', $bill_where_op['type']['op'], $bill_where_op['type']['condition']);
  142. }
  143. if ($where['nickname'] != '') {
  144. $model = $model->where('B.nickname|B.uid', 'like', "%$where[nickname]%");
  145. }
  146. $model = $model->where('A.number', '<>', 0);
  147. return $model->field(['A.*', 'FROM_UNIXTIME(A.add_time,"%Y-%m-%d %H:%i:%s") as add_time', 'B.uid', 'B.nickname', 'B.name']);
  148. }
  149. /**
  150. * @param $category
  151. * @return array|bool
  152. */
  153. public static function bill_where_op($category)
  154. {
  155. if (!$category || !in_array($category, ['now_money', 'gold_num', 'weixin', 'zhifubao', 'brokerage_price'])) {
  156. return false;
  157. }
  158. switch ($category) {
  159. case "now_money" :
  160. $bill_where_op['category']['op'] = 'in';
  161. $bill_where_op['category']['condition'] = 'now_money';
  162. $bill_where_op['type']['op'] = 'not in';
  163. $bill_where_op['type']['condition'] = 'extract,extract_fail,gain,deduction,sign,pay_vip,extract_success,brokerage,brokerage_return';
  164. break;
  165. case "brokerage_price" :
  166. $bill_where_op['category']['op'] = 'in';
  167. $bill_where_op['category']['condition'] = 'now_money';
  168. $bill_where_op['type']['op'] = 'in';
  169. $bill_where_op['type']['condition'] = 'extract,extract_fail,brokerage,brokerage_return';
  170. break;
  171. case "gold_num" :
  172. $bill_where_op['category']['op'] = 'in';
  173. $bill_where_op['category']['condition'] = 'gold_num';
  174. $bill_where_op['type']['op'] = 'in';
  175. $bill_where_op['type']['condition'] = 'sign,recharge,live_reward,gain,return';
  176. break;
  177. case "weixin" :
  178. $bill_where_op['category']['op'] = 'in';
  179. $bill_where_op['category']['condition'] = 'weixin';
  180. $bill_where_op['type']['op'] = 'in';
  181. $bill_where_op['type']['condition'] = 'pay_product,pay_vip,recharge,pay_sign_up,pay_goods,pay_test_paper,pay_data_download,pay_product_refund,pay_data_download_refund,pay_test_paper_refund,user_recharge_refund,pay_sign_up_refund';
  182. break;
  183. case "zhifubao" :
  184. $bill_where_op['category']['op'] = 'in';
  185. $bill_where_op['category']['condition'] = 'zhifubao';
  186. $bill_where_op['type']['op'] = 'in';
  187. $bill_where_op['type']['condition'] = 'pay_product,pay_vip,recharge,pay_sign_up,pay_goods,pay_test_paper,pay_data_download,pay_product_refund,pay_data_download_refund,pay_test_paper_refund,user_recharge_refund,pay_sign_up_refund';
  188. break;
  189. }
  190. return $bill_where_op;
  191. }
  192. /**
  193. * 获取营业数据
  194. */
  195. public static function getOrderInfo($where)
  196. {
  197. $orderinfo = self::getTimeWhere($where)
  198. ->field('sum(total_price) total_price,sum(cost) cost,sum(pay_postage) pay_postage,sum(pay_price) pay_price,sum(coupon_price) coupon_price,sum(deduction_price) deduction_price,from_unixtime(pay_time,\'%Y-%m-%d\') pay_time')->order('pay_time')->group('from_unixtime(pay_time,\'%Y-%m-%d\')')->select()->toArray();
  199. $price = 0;
  200. $postage = 0;
  201. $deduction = 0;
  202. $coupon = 0;
  203. $cost = 0;
  204. foreach ($orderinfo as $info) {
  205. $price = bcadd($price, $info['total_price'], 2);//应支付
  206. $postage = bcadd($postage, $info['pay_postage'], 2);//邮费
  207. $deduction = bcadd($deduction, $info['deduction_price'], 2);//抵扣
  208. $coupon = bcadd($coupon, $info['coupon_price'], 2);//优惠券
  209. $cost = bcadd($cost, $info['cost'], 2);//成本
  210. }
  211. return compact('orderinfo', 'price', 'postage', 'deduction', 'coupon', 'cost');
  212. }
  213. /**
  214. * 处理where条件
  215. */
  216. public static function statusByWhere($status, $model = null)
  217. {
  218. if ($model == null) $model = new self;
  219. if ('' === $status)
  220. return $model;
  221. else if ($status == 'weixin')//微信支付
  222. return $model->where('pay_type', 'weixin');
  223. else if ($status == 'yue')//余额支付
  224. return $model->where('pay_type', 'yue');
  225. else if ($status == 'offline')//线下支付
  226. return $model->where('pay_type', 'offline');
  227. else
  228. return $model;
  229. }
  230. public static function getTimeWhere($where, $model = null)
  231. {
  232. return self::getTime($where)->where('paid', 1)->where('refund_status', 0);
  233. }
  234. /**
  235. * 获取时间区间
  236. */
  237. public static function getTime($where, $model = null, $prefix = 'add_time')
  238. {
  239. if ($model == null) $model = new self;
  240. if ($where['data'] == '') {
  241. switch ($where['date']) {
  242. case 'today':
  243. case 'week':
  244. case 'month':
  245. case 'year':
  246. $model = $model->whereTime($prefix, $where['date']);
  247. break;
  248. case 'quarter':
  249. list($startTime, $endTime) = User::getMonth('n');
  250. $model = $model->where($prefix, '>', strtotime($startTime));
  251. $model = $model->where($prefix, '<', strtotime($endTime));
  252. break;
  253. }
  254. } else {
  255. list($startTime, $endTime) = explode(' - ', $where['data']);
  256. $model = $model->where($prefix, '>', strtotime($startTime));
  257. $model = $model->where($prefix, '<', strtotime($endTime));
  258. }
  259. return $model;
  260. }
  261. /**
  262. * 获取新增消费
  263. */
  264. public static function getConsumption($where)
  265. {
  266. $consumption = self::getTime($where, new UserBill, 'b.add_time')->alias('a')->join('user b', 'a.uid = b.uid')
  267. ->field('sum(a.number) number')
  268. ->where('a.type', 'pay_product')->find()->toArray();
  269. return $consumption;
  270. }
  271. /**
  272. * 获取普通商品数
  273. */
  274. public static function getOrdinary($where)
  275. {
  276. $ordinary = self::getTimeWhere($where)->where('pink_id', 'eq', 0)->where('seckill_id', 'eq', '0')->sum('pay_price');
  277. return $ordinary;
  278. }
  279. /**
  280. * 获取用户充值
  281. */
  282. public static function getRecharge($where)
  283. {
  284. $Recharge = self::getTime($where, new UserBill)->where('type', 'system_add')->where('category', 'now_money')->sum('number');
  285. return $Recharge;
  286. }
  287. /**
  288. * 获取推广金
  289. */
  290. public static function getExtension($where)
  291. {
  292. $brokerage = self::getTime($where, new UserBill)->where('type', 'brokerage')->where('category', 'now_money')->sum('number');
  293. $brokerage_return = self::getTime($where, new UserBill)->where('type', 'brokerage_return')->where('category', 'now_money')->sum('number');
  294. $extension = bcsub($brokerage, $brokerage_return, 2);
  295. return $extension;
  296. }
  297. /**
  298. * 最近交易
  299. */
  300. public static function trans()
  301. {
  302. $trans = self::alias('a')
  303. ->join('user b', 'a.uid=b.uid')
  304. ->join('store_order_cart_info c', 'a.id=c.oid')
  305. ->join('store_product d', 'c.product_id=d.id')
  306. ->field('b.nickname,a.pay_price,d.store_name')
  307. ->order('a.add_time DESC')
  308. ->limit('6')
  309. ->select()->toArray();
  310. return $trans;
  311. }
  312. }