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
{
    /**
     * 获取数据统计
     * @param int $storeId 商户ID，0表示平台管理员
     */
    public function getStatistics($storeId = 0)
    {
        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, $storeId);

            // 2. 营业额统计（基于订单表的实付金额）
            $revenueStats = $this->getRevenueStatistics($todayStart, $todayEnd, $monthStart, $monthEnd, $storeId);

            // 3. 退款统计（基于订单表的退款状态）
            $refundStats = $this->getRefundStatistics($todayStart, $todayEnd, $monthStart, $monthEnd, $storeId);

            // 4. 结算统计（基于订单表的商家佣金）
            $settlementStats = $this->getSettlementStatistics($storeId);

            // 5. 会员统计（商户端不显示，返回0）
            $memberStats = $storeId > 0 ? [
                'total' => 0,
                'month' => 0,
                'today' => 0
            ] : $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()
            ];
        }
    }

    /**
     * 订单统计（基于订单表）
     * @param int $storeId 商户ID，0表示平台管理员
     */
    private function getOrderStatistics($todayStart, $todayEnd, $monthStart, $monthEnd, $storeId = 0)
    {
        $orderModel = new OrderModel();

        // 总订单数
        $total = $orderModel->where('mark', 1)
            ->when($storeId > 0, function ($query) use ($storeId) {
                return $query->where('store_id', $storeId);
            })
            ->count();

        // 月订单数
        $month = $orderModel->where('mark', 1)
            ->when($storeId > 0, function ($query) use ($storeId) {
                return $query->where('store_id', $storeId);
            })
            ->whereBetween('create_time', [$monthStart, $monthEnd])
            ->count();

        // 当日订单数
        $today = $orderModel->where('mark', 1)
            ->when($storeId > 0, function ($query) use ($storeId) {
                return $query->where('store_id', $storeId);
            })
            ->whereBetween('create_time', [$todayStart, $todayEnd])
            ->count();

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

    /**
     * 营业额统计（基于订单表的实付金额pay_total）
     * @param int $storeId 商户ID，0表示平台管理员
     */
    private function getRevenueStatistics($todayStart, $todayEnd, $monthStart, $monthEnd, $storeId = 0)
    {
        $orderModel = new OrderModel();

        // 总营业额（已完成订单的实付金额）
        $total = $orderModel->where('mark', 1)
            ->where('status', 4) // 4-确认收货（已完成）
            ->when($storeId > 0, function ($query) use ($storeId) {
                return $query->where('store_id', $storeId);
            })
            ->sum('pay_total');

        // 月营业额
        $month = $orderModel->where('mark', 1)
            ->where('status', 4)
            ->when($storeId > 0, function ($query) use ($storeId) {
                return $query->where('store_id', $storeId);
            })
            ->whereBetween('create_time', [$monthStart, $monthEnd])
            ->sum('pay_total');

        // 当日营业额
        $today = $orderModel->where('mark', 1)
            ->where('status', 4)
            ->when($storeId > 0, function ($query) use ($storeId) {
                return $query->where('store_id', $storeId);
            })
            ->whereBetween('create_time', [$todayStart, $todayEnd])
            ->sum('pay_total');

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

    /**
     * 退款统计（基于订单表的退款状态refund_status=1）
     * @param int $storeId 商户ID，0表示平台管理员
     */
    private function getRefundStatistics($todayStart, $todayEnd, $monthStart, $monthEnd, $storeId = 0)
    {
        $orderModel = new OrderModel();

        // 总退款订单数和金额（refund_status=1表示已退款）
        $totalRefund = $orderModel->where('mark', 1)
            ->where('refund_status', 1) // 1-已退款
            ->when($storeId > 0, function ($query) use ($storeId) {
                return $query->where('store_id', $storeId);
            })
            ->selectRaw('COUNT(*) as count, SUM(pay_total) as amount')
            ->first();

        // 月退款订单数和金额
        $monthRefund = $orderModel->where('mark', 1)
            ->where('refund_status', 1)
            ->when($storeId > 0, function ($query) use ($storeId) {
                return $query->where('store_id', $storeId);
            })
            ->whereBetween('create_time', [$monthStart, $monthEnd])
            ->selectRaw('COUNT(*) as count, SUM(pay_total) as amount')
            ->first();

        // 当日退款订单数和金额
        $todayRefund = $orderModel->where('mark', 1)
            ->where('refund_status', 1)
            ->when($storeId > 0, function ($query) use ($storeId) {
                return $query->where('store_id', $storeId);
            })
            ->whereBetween('create_time', [$todayStart, $todayEnd])
            ->selectRaw('COUNT(*) as count, SUM(pay_total) 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, '.', '')
        ];
    }

    /**
     * 结算统计（基于订单表的商家佣金bonus字段）
     * @param int $storeId 商户ID，0表示平台管理员
     */
    private function getSettlementStatistics($storeId = 0)
    {
        $orderModel = new OrderModel();

        // 已结算金额总数（已完成订单的商家佣金）
        $total = $orderModel->where('mark', 1)
            ->where('status', 4) // 4-确认收货（已完成）
            ->when($storeId > 0, function ($query) use ($storeId) {
                return $query->where('store_id', $storeId);
            })
            ->sum('bonus');

        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;
        $storeId = $params['store_id'] ?? 0; // 商户ID，0表示平台管理员

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

    /**
     * 订单统计列表
     * @param int $storeId 商户ID，0表示平台管理员查看全部数据
     */
    private function getOrderList($groupBy, $startTime, $endTime, $page, $limit, $storeId = 0)
    {
        $dateFormat = $this->getDateFormat($groupBy);

        $query = OrderModel::where('mark', 1)
            ->when($storeId > 0, function ($query) use ($storeId) {
                return $query->where('store_id', $storeId);
            })
            ->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
        ];
    }

    /**
     * 营业额统计列表（基于订单表的实付金额pay_total）
     * @param int $storeId 商户ID，0表示平台管理员查看全部数据
     */
    private function getRevenueList($groupBy, $startTime, $endTime, $page, $limit, $storeId = 0)
    {
        $dateFormat = $this->getDateFormat($groupBy);

        $query = OrderModel::where('mark', 1)
            ->where('status', 4) // 4-确认收货（已完成）
            ->when($storeId > 0, function ($query) use ($storeId) {
                return $query->where('store_id', $storeId);
            })
            ->whereBetween('create_time', [$startTime, $endTime])
            ->selectRaw("FROM_UNIXTIME(create_time, '{$dateFormat}') as date, SUM(pay_total) 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
        ];
    }

    /**
     * 退款统计列表（基于订单表的退款状态refund_status=1）
     * @param int $storeId 商户ID，0表示平台管理员查看全部数据
     */
    private function getRefundList($groupBy, $startTime, $endTime, $page, $limit, $storeId = 0)
    {
        $dateFormat = $this->getDateFormat($groupBy);

        $query = OrderModel::where('mark', 1)
            ->where('refund_status', 1) // 1-已退款
            ->when($storeId > 0, function ($query) use ($storeId) {
                return $query->where('store_id', $storeId);
            })
            ->whereBetween('create_time', [$startTime, $endTime])
            ->selectRaw("FROM_UNIXTIME(create_time, '{$dateFormat}') as date, COUNT(*) as count, SUM(pay_total) 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
        ];
    }

    /**
     * 结算统计列表（基于订单表的商家佣金bonus字段）
     * @param int $storeId 商户ID，0表示平台管理员查看全部数据
     */
    private function getSettlementList($groupBy, $startTime, $endTime, $page, $limit, $storeId = 0)
    {
        $dateFormat = $this->getDateFormat($groupBy);

        $query = OrderModel::where('mark', 1)
            ->where('status', 4) // 4-确认收货（已完成）
            ->when($storeId > 0, function ($query) use ($storeId) {
                return $query->where('store_id', $storeId);
            })
            ->whereBetween('create_time', [$startTime, $endTime])
            ->selectRaw("FROM_UNIXTIME(create_time, '{$dateFormat}') as date, SUM(bonus) 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
        ];
    }

    /**
     * 会员统计列表
     * @param int $storeId 商户ID，0表示平台管理员查看全部数据（会员数据商户端不显示）
     */
    private function getMemberList($groupBy, $startTime, $endTime, $page, $limit, $storeId = 0)
    {
        // 商户端不显示会员数据
        if ($storeId > 0) {
            return [
                'code' => 0,
                'msg' => '获取成功',
                'data' => [],
                'count' => 0
            ];
        }

        $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';
        }
    }
}