123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140 |
- <?php
- namespace app\admin\controller\order;
- use app\model\PayDetail;
- use support\Db;
- use support\Request;
- class StatisticsController
- {
- /**
- * @Desc 标准订单
- * @Author Gorden
- * @Date 2024/10/28 9:57
- *
- * @param Request $request
- * @return \support\Response
- */
- public function order(Request $request)
- {
- $orderCategory = $request->get('order_category');
- $searchTime = $request->get('order_addtimes');
- if (!empty($searchTime)) {
- $searchTime[0] = strtotime($searchTime[0]);
- $searchTime[1] = strtotime($searchTime[1]);
- }
- if (!$orderCategory) {
- return json_fail('参数异常');
- }
- if ($orderCategory == 'SERVICE') {
- $orderCategory = ['SERVICE', 'CHNMED', 'CHNNCD'];
- } else if ($orderCategory == 'ALL') {
- $orderCategory = ['SERVICE', 'CHNMED', 'CHNNCD', 'GOODS', 'PACKAGE', 'MEALS', 'RECHARGE', 'COMBINE', 'PARTNER', 'REFERRER', 'VIP'];
- } else {
- $orderCategory = [$orderCategory];
- }
- $where = '';
- if (in_array('RECHARGE', $orderCategory)) {
- $where = " and join_pay_order_id not in ('COMBINE','PARTNER','REFERRER')";
- }
- $orderCategoryStr = "'" . implode("','", $orderCategory) . "'";
- $todayTimeUnix = strtotime(date('Y-m-d'));
- $yesterdayStart = strtotime(date('Y-m-d', strtotime("-1 days")));
- $yesterdayEnd = strtotime(date('Y-m-d 23:59:59', strtotime("-1 days")));
- $monthStart = strtotime(date('Y-m-01'));
- $monthEnd = strtotime(date('Y-m-t 23:59:59'));
- // 今日收入
- $statistics['todayRevenue'] = PayDetail::whereRaw('CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) >= ? ', [$todayTimeUnix])
- ->where('pay_status', 'SUCCESS')
- ->where('pay_prepayid','<>', '')
- ->whereIn('pay_category', $orderCategory)
- ->when(in_array('RECHARGE', $orderCategory), function ($query) {
- $query->whereNotIn('join_pay_order_id', ['COMBINE', 'PARTNER', 'REFERRER']);
- })
- ->sum('pay_amount');
- $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
- CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) >= {$todayTimeUnix}
- and pay_status = 'SUCCESS'
- and pay_prepayid != ''
- and pay_category in ({$orderCategoryStr}) {$where}
- group by order_id) count");
- $statistics['todayRevenueOrderNbr'] = $todayRevenueOrderNbrModel[0]->total;
- // 昨日收入
- $statistics['yesterdayRevenue'] = PayDetail::whereRaw('CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) >= ? and CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) <= ?', [$yesterdayStart, $yesterdayEnd])
- ->where('pay_status', 'SUCCESS')
- ->where('pay_prepayid','<>', '')
- ->whereIn('pay_category', $orderCategory)
- ->when(in_array('RECHARGE', $orderCategory), function ($query) {
- $query->whereNotIn('join_pay_order_id', ['COMBINE', 'PARTNER', 'REFERRER']);
- })
- ->sum('pay_amount');
- $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
- CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) >= {$yesterdayStart} and CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) <= {$yesterdayEnd}
- and pay_status = 'SUCCESS'
- and pay_prepayid != ''
- and pay_category in ({$orderCategoryStr}) {$where}
- group by order_id) count");
- $statistics['yesterdayRevenueOrderNbr'] = $yesterdayRevenueOrderNbrModel[0]->total;
- // 本月收入
- $statistics['monthRevenue'] = PayDetail::whereRaw('CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) >= ? and CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) <= ?', [$monthStart, $monthEnd])
- ->where('pay_status', 'SUCCESS')
- ->where('pay_prepayid','<>', '')
- ->whereIn('pay_category', $orderCategory)
- ->when(in_array('RECHARGE', $orderCategory), function ($query) {
- $query->whereNotIn('join_pay_order_id', ['COMBINE', 'PARTNER', 'REFERRER']);
- })
- ->sum('pay_amount');
- $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
- CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) >= {$monthStart} and CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) <= {$monthEnd}
- and pay_status = 'SUCCESS'
- and pay_prepayid != ''
- and pay_category in ({$orderCategoryStr}) {$where}
- group by order_id) count");
- $statistics['monthRevenueOrderNbr'] = $monthRevenueOrderNbrModel[0]->total;
- // 累计收入
- $statistics['totalRevenue'] = PayDetail::where('pay_status', 'SUCCESS')
- ->where('pay_prepayid','<>', '')
- ->whereIn('pay_category', $orderCategory)
- ->sum('pay_amount');
- $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
- pay_status = 'SUCCESS'
- and pay_prepayid != ''
- and pay_category in ({$orderCategoryStr}) {$where}
- group by order_id) count");
- $statistics['totalRevenueOrderNbr'] = $totalRevenueOrderNbrModel[0]->total;
- // 今日退款
- $todayRefundItem = Db::select("select SUM(d2.pay_amount) as amount,count(1) as total from app_pay_detail d1
- inner join app_pay_detail d2 ON d1.join_pay_order_id=d2.join_pay_order_id and d2.pay_category='REFUND'
- where CAST(UNIX_TIMESTAMP(d2.pay_paytimes) as SIGNED) >= ? AND d1.pay_category in ({$orderCategoryStr})
- ", [$todayTimeUnix]);
- $statistics['todayRefund'] = $todayRefundItem[0]->amount;
- $statistics['todayRefundNbr'] = $todayRefundItem[0]->total;
- // 累计退款
- $totalRefundItem = Db::select("select SUM(d2.pay_amount) as amount,count(1) as total from app_pay_detail d1
- inner join app_pay_detail d2 ON d1.join_pay_order_id=d2.join_pay_order_id and d2.pay_category='REFUND'
- where d1.pay_category in ({$orderCategoryStr})
- ");
- $statistics['totalRefund'] = $totalRefundItem[0]->amount;
- $statistics['totalRefundNbr'] = $totalRefundItem[0]->total;
- if (in_array('MEALS', $orderCategory)) {
- // 挂账
- $sql = "select SUM(order_amount_pay) as amount, count(1) as total from app_order where order_status_payment = 'AWAITING'";
- if (!empty($searchTime)) {
- $sql .= " and order_addtimes > {$searchTime[0]} and order_addtimes < $searchTime[1]";
- }
- $awaitingModel = Db::select($sql);
- $statistics['awaitingRevenue'] = $awaitingModel[0]->amount;
- $statistics['awaitingRevenueOrderNbr'] = $awaitingModel[0]->total;
- }
- return json_success('success', $statistics);
- }
- }
|