NN2025112501/app/Services/Common/FinancialService.php

<?php

namespace App\Services\Common;

use App\Models\OrderModel;
use App\Models\MemberModel;
use App\Models\WithdrawModel;
use App\Models\AccountLogModel;
use App\Services\BaseService;
use Illuminate\Support\Facades\DB;

/**
 * 财务服务
 */
class FinancialService extends BaseService
{
    /**
     * 获取数据统计
     */
    public function getStatistics()
    {
        try {
            // 获取当前时间范围
            $today = date('Y-m-d');
            $todayStart = strtotime($today . ' 00:00:00');
            $todayEnd = strtotime($today . ' 23:59:59');
            
            $monthStart = strtotime(date('Y-m-01 00:00:00'));
            $monthEnd = strtotime(date('Y-m-t 23:59:59'));

            // 1. 订单统计
            $orderStats = $this->getOrderStatistics($todayStart, $todayEnd, $monthStart, $monthEnd);

            // 2. 营业额统计
            $revenueStats = $this->getRevenueStatistics($todayStart, $todayEnd, $monthStart, $monthEnd);

            // 3. 退款统计
            $refundStats = $this->getRefundStatistics($todayStart, $todayEnd, $monthStart, $monthEnd);

            // 4. 结算统计
            $settlementStats = $this->getSettlementStatistics();

            // 5. 会员统计
            $memberStats = $this->getMemberStatistics($todayStart, $todayEnd, $monthStart, $monthEnd);

            return [
                'code' => 0,
                'msg' => '获取成功',
                'data' => [
                    'order' => $orderStats,
                    'revenue' => $revenueStats,
                    'refund' => $refundStats,
                    'settlement' => $settlementStats,
                    'member' => $memberStats
                ]
            ];
        } catch (\Exception $e) {
            return [
                'code' => 1,
                'msg' => '获取失败：' . $e->getMessage()
            ];
        }
    }

    /**
     * 订单统计
     */
    private function getOrderStatistics($todayStart, $todayEnd, $monthStart, $monthEnd)
    {
        $orderModel = new OrderModel();

        // 总订单数
        $total = $orderModel->where('mark', 1)->count();

        // 月订单数
        $month = $orderModel->where('mark', 1)
            ->whereBetween('create_time', [$monthStart, $monthEnd])
            ->count();

        // 当日订单数
        $today = $orderModel->where('mark', 1)
            ->whereBetween('create_time', [$todayStart, $todayEnd])
            ->count();

        return [
            'total' => $total,
            'month' => $month,
            'today' => $today
        ];
    }

    /**
     * 营业额统计（使用账户明细表统计商城消费）
     */
    private function getRevenueStatistics($todayStart, $todayEnd, $monthStart, $monthEnd)
    {
        // 使用账户明细表统计商城消费（type=1）和充值缴费（type=2）
        $accountLogModel = new AccountLogModel();

        // 总营业额（商城消费 + 充值缴费）
        $total = $accountLogModel->where('mark', 1)
            ->where('status', 1) // 已完成
            ->whereIn('type', [1, 2]) // 1-商城消费，2-充值缴费
            ->sum('money');

        // 月营业额
        $month = $accountLogModel->where('mark', 1)
            ->where('status', 1)
            ->whereIn('type', [1, 2])
            ->whereBetween('create_time', [$monthStart, $monthEnd])
            ->sum('money');

        // 当日营业额
        $today = $accountLogModel->where('mark', 1)
            ->where('status', 1)
            ->whereIn('type', [1, 2])
            ->whereBetween('create_time', [$todayStart, $todayEnd])
            ->sum('money');

        return [
            'total' => number_format($total, 2, '.', ''),
            'month' => number_format($month, 2, '.', ''),
            'today' => number_format($today, 2, '.', '')
        ];
    }

    /**
     * 退款统计（使用账户明细表统计商城退款）
     */
    private function getRefundStatistics($todayStart, $todayEnd, $monthStart, $monthEnd)
    {
        // 使用账户明细表统计商城退款（type=3）
        $accountLogModel = new AccountLogModel();

        // 总退款订单数和金额
        $totalRefund = $accountLogModel->where('mark', 1)
            ->where('type', 3) // 3-商城退款
            ->where('status', 1) // 已完成
            ->selectRaw('COUNT(*) as count, SUM(money) as amount')
            ->first();

        // 月退款订单数和金额
        $monthRefund = $accountLogModel->where('mark', 1)
            ->where('type', 3)
            ->where('status', 1)
            ->whereBetween('create_time', [$monthStart, $monthEnd])
            ->selectRaw('COUNT(*) as count, SUM(money) as amount')
            ->first();

        // 当日退款订单数和金额
        $todayRefund = $accountLogModel->where('mark', 1)
            ->where('type', 3)
            ->where('status', 1)
            ->whereBetween('create_time', [$todayStart, $todayEnd])
            ->selectRaw('COUNT(*) as count, SUM(money) as amount')
            ->first();

        return [
            'total_count' => $totalRefund->count ?? 0,
            'total_amount' => number_format($totalRefund->amount ?? 0, 2, '.', ''),
            'month_count' => $monthRefund->count ?? 0,
            'month_amount' => number_format($monthRefund->amount ?? 0, 2, '.', ''),
            'today_count' => $todayRefund->count ?? 0,
            'today_amount' => number_format($todayRefund->amount ?? 0, 2, '.', '')
        ];
    }

    /**
     * 结算统计（使用账户明细表统计佣金提现）
     */
    private function getSettlementStatistics()
    {
        // 使用账户明细表统计佣金提现（type=4）
        $accountLogModel = new AccountLogModel();

        // 已结算金额总数（已完成的佣金提现）
        $total = $accountLogModel->where('mark', 1)
            ->where('type', 4) // 4-佣金提现
            ->where('status', 1) // 已完成
            ->sum('money');

        return [
            'total' => number_format($total, 2, '.', '')
        ];
    }

    /**
     * 会员统计
     */
    private function getMemberStatistics($todayStart, $todayEnd, $monthStart, $monthEnd)
    {
        $memberModel = new MemberModel();

        // 总会员数
        $total = $memberModel->where('mark', 1)->count();

        // 月新增会员
        $month = $memberModel->where('mark', 1)
            ->whereBetween('create_time', [$monthStart, $monthEnd])
            ->count();

        // 当日新增会员
        $today = $memberModel->where('mark', 1)
            ->whereBetween('create_time', [$todayStart, $todayEnd])
            ->count();

        return [
            'total' => $total,
            'month' => $month,
            'today' => $today
        ];
    }

    /**
     * 获取统计列表（按年月日分组）
     */
    public function getStatisticsList($params)
    {
        $type = $params['type'] ?? 'order';
        $groupBy = $params['group_by'] ?? 'day';
        $startDate = $params['start_date'] ?? date('Y-m-d', strtotime('-30 days'));
        $endDate = $params['end_date'] ?? date('Y-m-d');
        $page = $params['page'] ?? 1;
        $limit = $params['limit'] ?? 20;

        $startTime = strtotime($startDate . ' 00:00:00');
        $endTime = strtotime($endDate . ' 23:59:59');

        try {
            switch ($type) {
                case 'order':
                    return $this->getOrderList($groupBy, $startTime, $endTime, $page, $limit);
                case 'revenue':
                    return $this->getRevenueList($groupBy, $startTime, $endTime, $page, $limit);
                case 'refund':
                    return $this->getRefundList($groupBy, $startTime, $endTime, $page, $limit);
                case 'settlement':
                    return $this->getSettlementList($groupBy, $startTime, $endTime, $page, $limit);
                case 'member':
                    return $this->getMemberList($groupBy, $startTime, $endTime, $page, $limit);
                default:
                    return ['code' => 1, 'msg' => '未知的统计类型'];
            }
        } catch (\Exception $e) {
            return ['code' => 1, 'msg' => '查询失败：' . $e->getMessage()];
        }
    }

    /**
     * 订单统计列表
     */
    private function getOrderList($groupBy, $startTime, $endTime, $page, $limit)
    {
        $dateFormat = $this->getDateFormat($groupBy);
        
        $query = OrderModel::where('mark', 1)
            ->whereBetween('create_time', [$startTime, $endTime])
            ->selectRaw("FROM_UNIXTIME(create_time, '{$dateFormat}') as date, COUNT(*) as count")
            ->groupBy('date')
            ->orderBy('date', 'desc');

        $total = $query->get()->count();
        $data = $query->offset(($page - 1) * $limit)->limit($limit)->get()->toArray();

        return [
            'code' => 0,
            'msg' => '获取成功',
            'data' => $data,
            'count' => $total
        ];
    }

    /**
     * 营业额统计列表
     */
    private function getRevenueList($groupBy, $startTime, $endTime, $page, $limit)
    {
        $dateFormat = $this->getDateFormat($groupBy);
        
        $query = AccountLogModel::where('mark', 1)
            ->where('status', 1)
            ->whereIn('type', [1, 2])
            ->whereBetween('create_time', [$startTime, $endTime])
            ->selectRaw("FROM_UNIXTIME(create_time, '{$dateFormat}') as date, SUM(money) as amount")
            ->groupBy('date')
            ->orderBy('date', 'desc');

        $allResults = $query->get();
        $total = $allResults->count();
        $list = $allResults->slice(($page - 1) * $limit, $limit)->values()->toArray();
        
        foreach ($list as &$item) {
            $item['amount'] = number_format($item['amount'], 2, '.', '');
        }

        return [
            'code' => 0,
            'msg' => '获取成功',
            'data' => $list,
            'count' => $total
        ];
    }

    /**
     * 退款统计列表
     */
    private function getRefundList($groupBy, $startTime, $endTime, $page, $limit)
    {
        $dateFormat = $this->getDateFormat($groupBy);
        
        $query = AccountLogModel::where('mark', 1)
            ->where('type', 3)
            ->where('status', 1)
            ->whereBetween('create_time', [$startTime, $endTime])
            ->selectRaw("FROM_UNIXTIME(create_time, '{$dateFormat}') as date, COUNT(*) as count, SUM(money) as amount")
            ->groupBy('date')
            ->orderBy('date', 'desc');

        $allResults = $query->get();
        $total = $allResults->count();
        $list = $allResults->slice(($page - 1) * $limit, $limit)->values()->toArray();
        
        foreach ($list as &$item) {
            $item['amount'] = number_format($item['amount'], 2, '.', '');
        }

        return [
            'code' => 0,
            'msg' => '获取成功',
            'data' => $list,
            'count' => $total
        ];
    }

    /**
     * 结算统计列表
     */
    private function getSettlementList($groupBy, $startTime, $endTime, $page, $limit)
    {
        $dateFormat = $this->getDateFormat($groupBy);
        
        $query = AccountLogModel::where('mark', 1)
            ->where('type', 4)
            ->where('status', 1)
            ->whereBetween('create_time', [$startTime, $endTime])
            ->selectRaw("FROM_UNIXTIME(create_time, '{$dateFormat}') as date, SUM(money) as amount")
            ->groupBy('date')
            ->orderBy('date', 'desc');

        $allResults = $query->get();
        $total = $allResults->count();
        $list = $allResults->slice(($page - 1) * $limit, $limit)->values()->toArray();
        
        foreach ($list as &$item) {
            $item['amount'] = number_format($item['amount'], 2, '.', '');
        }

        return [
            'code' => 0,
            'msg' => '获取成功',
            'data' => $list,
            'count' => $total
        ];
    }

    /**
     * 会员统计列表
     */
    private function getMemberList($groupBy, $startTime, $endTime, $page, $limit)
    {
        $dateFormat = $this->getDateFormat($groupBy);
        
        $query = MemberModel::where('mark', 1)
            ->whereBetween('create_time', [$startTime, $endTime])
            ->selectRaw("FROM_UNIXTIME(create_time, '{$dateFormat}') as date, COUNT(*) as count")
            ->groupBy('date')
            ->orderBy('date', 'desc');

        $allResults = $query->get();
        $total = $allResults->count();
        $data = $allResults->slice(($page - 1) * $limit, $limit)->values()->toArray();

        return [
            'code' => 0,
            'msg' => '获取成功',
            'data' => $data,
            'count' => $total
        ];
    }

    /**
     * 获取日期格式
     */
    private function getDateFormat($groupBy)
    {
        switch ($groupBy) {
            case 'year':
                return '%Y';
            case 'month':
                return '%Y-%m';
            case 'day':
            default:
                return '%Y-%m-%d';
        }
    }
}