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

<?php

namespace app\admin\controller\order;

use app\model\PayDetail;
use support\Db;
use support\Request;

class StatisticsController
{
    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 {
            $orderCategory = [$orderCategory];
        }

        $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')
            ->whereIn('pay_category', $orderCategory)
            ->sum('pay_amount');
        $todayRevenueOrderNbrModel = Db::select("select count(1) as total from (select join_pay_order_id,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_category in ({$orderCategoryStr})
                                                                       group by join_pay_order_id,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')
            ->whereIn('pay_category', $orderCategory)
            ->sum('pay_amount');
        $yesterdayRevenueOrderNbrModel = Db::select("select count(1) as total from (select join_pay_order_id,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_category in ({$orderCategoryStr})
                                                                       group by join_pay_order_id,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')
            ->whereIn('pay_category', $orderCategory)
            ->sum('pay_amount');
        $monthRevenueOrderNbrModel = Db::select("select count(1) as total from (select join_pay_order_id,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_category in ({$orderCategoryStr})
                                                                       group by join_pay_order_id,order_id) count");
        $statistics['monthRevenueOrderNbr'] = $monthRevenueOrderNbrModel[0]->total;
        // 累计收入
//        $statistics['totalRevenue'] = PayDetail::where('pay_status', 'SUCCESS')
//            ->whereIn('pay_category', $orderCategory)
//            ->sum('pay_amount');

        // 今日退款
        $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);
    }
}