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');
        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 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) 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 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) 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 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) 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, d1.pay_category as d1_pay_category,d2.pay_category as d2_pay_category 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 ('" . implode("','", $orderCategory) . "') 
            ");
        $statistics['totalRefund'] = $totalRefundItem[0]->amount;
        $statistics['totalRefundNbr'] = $totalRefundItem[0]->total;

        if (in_array('MEALS',$orderCategory)){
            // 挂账
            $awaitingModel = Db::select("select SUM(order_amount_pay) as amount, count(1) as total from app_order where order_status_payment = 'AWAITING'");
            dump($awaitingModel);
        }

        return json_success('success', $statistics);
    }
}