wanyue_app/app/admin/controller/order/StatisticsController.php

<?php

namespace app\admin\controller\order;

use app\model\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 == 'MEALS') {
            $orderCategory = ['MEALS','DISHES'];
        }  else if ($orderCategory == 'ALL') {
            $orderCategory = ['SERVICE', 'CHNMED', 'CHNNCD', 'GOODS', 'PACKAGE', 'MEALS','DISHES', 'RECHARGE', 'COMBINE', 'PARTNER', 'REFERRER','FRANCHISEE', 'VIP'];
        } else {
            $orderCategory = [$orderCategory];
        }

        $where = '';
        if (in_array('RECHARGE', $orderCategory)) {
            $where = " and join_pay_order_id not in ('COMBINE','PARTNER','REFERRER','FRANCHISEE')";
        }

        $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','FRANCHISEE']);
            })
            ->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','FRANCHISEE']);
            })
            ->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','FRANCHISEE']);
            })
            ->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})
//            ");
        $refundOrderIds = Order::where('order_category','RETURN')
            ->whereIn('order_classify',$orderCategory)
            ->pluck('order_groupby')
            ->toArray();
        $statistics['totalRefund'] = PayDetail::whereIn('join_pay_order_id',$refundOrderIds)->where('pay_status','SUCCESS')->sum('pay_amount');
        $statistics['totalRefundNbr'] = count($refundOrderIds);

        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);
    }
}