StatisticsController.php 8.2 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140
  1. <?php
  2. namespace app\admin\controller\order;
  3. use app\model\PayDetail;
  4. use support\Db;
  5. use support\Request;
  6. class StatisticsController
  7. {
  8. /**
  9. * @Desc 标准订单
  10. * @Author Gorden
  11. * @Date 2024/10/28 9:57
  12. *
  13. * @param Request $request
  14. * @return \support\Response
  15. */
  16. public function order(Request $request)
  17. {
  18. $orderCategory = $request->get('order_category');
  19. $searchTime = $request->get('order_addtimes');
  20. if (!empty($searchTime)) {
  21. $searchTime[0] = strtotime($searchTime[0]);
  22. $searchTime[1] = strtotime($searchTime[1]);
  23. }
  24. if (!$orderCategory) {
  25. return json_fail('参数异常');
  26. }
  27. if ($orderCategory == 'SERVICE') {
  28. $orderCategory = ['SERVICE', 'CHNMED', 'CHNNCD'];
  29. } else if ($orderCategory == 'ALL') {
  30. $orderCategory = ['SERVICE', 'CHNMED', 'CHNNCD', 'GOODS', 'PACKAGE', 'MEALS', 'RECHARGE', 'COMBINE', 'PARTNER', 'REFERRER', 'VIP'];
  31. } else {
  32. $orderCategory = [$orderCategory];
  33. }
  34. $where = '';
  35. if (in_array('RECHARGE', $orderCategory)) {
  36. $where = " and join_pay_order_id not in ('COMBINE','PARTNER','REFERRER')";
  37. }
  38. $orderCategoryStr = "'" . implode("','", $orderCategory) . "'";
  39. $todayTimeUnix = strtotime(date('Y-m-d'));
  40. $yesterdayStart = strtotime(date('Y-m-d', strtotime("-1 days")));
  41. $yesterdayEnd = strtotime(date('Y-m-d 23:59:59', strtotime("-1 days")));
  42. $monthStart = strtotime(date('Y-m-01'));
  43. $monthEnd = strtotime(date('Y-m-t 23:59:59'));
  44. // 今日收入
  45. $statistics['todayRevenue'] = PayDetail::whereRaw('CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) >= ? ', [$todayTimeUnix])
  46. ->where('pay_status', 'SUCCESS')
  47. ->where('pay_prepayid','<>', '')
  48. ->whereIn('pay_category', $orderCategory)
  49. ->when(in_array('RECHARGE', $orderCategory), function ($query) {
  50. $query->whereNotIn('join_pay_order_id', ['COMBINE', 'PARTNER', 'REFERRER']);
  51. })
  52. ->sum('pay_amount');
  53. $todayRevenueOrderNbrModel = Db::select("select count(1) as total from (select JSON_EXTRACT(join_pay_object_json,'$.order_id') as order_id from app_pay_detail where
  54. CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) >= {$todayTimeUnix}
  55. and pay_status = 'SUCCESS'
  56. and pay_prepayid != ''
  57. and pay_category in ({$orderCategoryStr}) {$where}
  58. group by order_id) count");
  59. $statistics['todayRevenueOrderNbr'] = $todayRevenueOrderNbrModel[0]->total;
  60. // 昨日收入
  61. $statistics['yesterdayRevenue'] = PayDetail::whereRaw('CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) >= ? and CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) <= ?', [$yesterdayStart, $yesterdayEnd])
  62. ->where('pay_status', 'SUCCESS')
  63. ->where('pay_prepayid','<>', '')
  64. ->whereIn('pay_category', $orderCategory)
  65. ->when(in_array('RECHARGE', $orderCategory), function ($query) {
  66. $query->whereNotIn('join_pay_order_id', ['COMBINE', 'PARTNER', 'REFERRER']);
  67. })
  68. ->sum('pay_amount');
  69. $yesterdayRevenueOrderNbrModel = Db::select("select count(1) as total from (select JSON_EXTRACT(join_pay_object_json,'$.order_id') as order_id from app_pay_detail where
  70. CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) >= {$yesterdayStart} and CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) <= {$yesterdayEnd}
  71. and pay_status = 'SUCCESS'
  72. and pay_prepayid != ''
  73. and pay_category in ({$orderCategoryStr}) {$where}
  74. group by order_id) count");
  75. $statistics['yesterdayRevenueOrderNbr'] = $yesterdayRevenueOrderNbrModel[0]->total;
  76. // 本月收入
  77. $statistics['monthRevenue'] = PayDetail::whereRaw('CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) >= ? and CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) <= ?', [$monthStart, $monthEnd])
  78. ->where('pay_status', 'SUCCESS')
  79. ->where('pay_prepayid','<>', '')
  80. ->whereIn('pay_category', $orderCategory)
  81. ->when(in_array('RECHARGE', $orderCategory), function ($query) {
  82. $query->whereNotIn('join_pay_order_id', ['COMBINE', 'PARTNER', 'REFERRER']);
  83. })
  84. ->sum('pay_amount');
  85. $monthRevenueOrderNbrModel = Db::select("select count(1) as total from (select JSON_EXTRACT(join_pay_object_json,'$.order_id') as order_id from app_pay_detail where
  86. CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) >= {$monthStart} and CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) <= {$monthEnd}
  87. and pay_status = 'SUCCESS'
  88. and pay_prepayid != ''
  89. and pay_category in ({$orderCategoryStr}) {$where}
  90. group by order_id) count");
  91. $statistics['monthRevenueOrderNbr'] = $monthRevenueOrderNbrModel[0]->total;
  92. // 累计收入
  93. $statistics['totalRevenue'] = PayDetail::where('pay_status', 'SUCCESS')
  94. ->where('pay_prepayid','<>', '')
  95. ->whereIn('pay_category', $orderCategory)
  96. ->sum('pay_amount');
  97. $totalRevenueOrderNbrModel = Db::select("select count(1) as total from (select JSON_EXTRACT(join_pay_object_json,'$.order_id') as order_id from app_pay_detail where
  98. pay_status = 'SUCCESS'
  99. and pay_prepayid != ''
  100. and pay_category in ({$orderCategoryStr}) {$where}
  101. group by order_id) count");
  102. $statistics['totalRevenueOrderNbr'] = $totalRevenueOrderNbrModel[0]->total;
  103. // 今日退款
  104. $todayRefundItem = Db::select("select SUM(d2.pay_amount) as amount,count(1) as total from app_pay_detail d1
  105. inner join app_pay_detail d2 ON d1.join_pay_order_id=d2.join_pay_order_id and d2.pay_category='REFUND'
  106. where CAST(UNIX_TIMESTAMP(d2.pay_paytimes) as SIGNED) >= ? AND d1.pay_category in ({$orderCategoryStr})
  107. ", [$todayTimeUnix]);
  108. $statistics['todayRefund'] = $todayRefundItem[0]->amount;
  109. $statistics['todayRefundNbr'] = $todayRefundItem[0]->total;
  110. // 累计退款
  111. $totalRefundItem = Db::select("select SUM(d2.pay_amount) as amount,count(1) as total from app_pay_detail d1
  112. inner join app_pay_detail d2 ON d1.join_pay_order_id=d2.join_pay_order_id and d2.pay_category='REFUND'
  113. where d1.pay_category in ({$orderCategoryStr})
  114. ");
  115. $statistics['totalRefund'] = $totalRefundItem[0]->amount;
  116. $statistics['totalRefundNbr'] = $totalRefundItem[0]->total;
  117. if (in_array('MEALS', $orderCategory)) {
  118. // 挂账
  119. $sql = "select SUM(order_amount_pay) as amount, count(1) as total from app_order where order_status_payment = 'AWAITING'";
  120. if (!empty($searchTime)) {
  121. $sql .= " and order_addtimes > {$searchTime[0]} and order_addtimes < $searchTime[1]";
  122. }
  123. $awaitingModel = Db::select($sql);
  124. $statistics['awaitingRevenue'] = $awaitingModel[0]->amount;
  125. $statistics['awaitingRevenueOrderNbr'] = $awaitingModel[0]->total;
  126. }
  127. return json_success('success', $statistics);
  128. }
  129. }