yuezi-saas/backend/app/Http/Controllers/Admin/Report/ReportController.php

<?php

namespace App\Http\Controllers\Admin\Report;

use App\Http\Controllers\Controller;
use App\Models\Crm\Contract;
use App\Models\Crm\Customer;
use App\Models\Crm\Lead;
use App\Models\Finance\FinanceRecord;
use App\Models\Room\Room;
use App\Models\Room\RoomType;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\Request;

class ReportController extends Controller
{
    public function overview(Request $request): JsonResponse
    {
        $data = [
            'customer_count' => Customer::count(),
            'contract_amount' => Contract::where('status', 2)->sum('actual_amount'),
            'room_count' => Room::count(),
            'occupied_count' => Room::where('status', 3)->count(),
            'today_revenue' => FinanceRecord::where('type', 1)->where('audit_status', 1)
                ->whereDate('record_date', today())->sum('amount'),
            'month_revenue' => FinanceRecord::where('type', 1)->where('audit_status', 1)
                ->whereMonth('record_date', now()->month)
                ->whereYear('record_date', now()->year)->sum('amount'),
        ];
        return $this->success($data);
    }

    public function revenue(Request $request): JsonResponse
    {
        $year = $request->input('year', now()->year);
        $records = FinanceRecord::where('audit_status', 1)
            ->whereYear('record_date', $year)
            ->selectRaw('MONTH(record_date) as month, type, SUM(amount) as total')
            ->groupBy('month', 'type')
            ->get();
        return $this->success($records);
    }

    public function occupancy(Request $request): JsonResponse
    {
        $data = RoomType::withCount([
            'rooms',
            'rooms as occupied_count' => fn($q) => $q->where('status', 3),
        ])->get();
        return $this->success($data);
    }

    public function crmConversion(Request $request): JsonResponse
    {
        $data = [
            'total_leads' => Lead::count(),
            'following' => Lead::where('status', 2)->count(),
            'contracted' => Lead::where('status', 5)->count(),
            'by_channel' => Lead::selectRaw('channel_id, COUNT(*) as total, SUM(CASE WHEN status=5 THEN 1 ELSE 0 END) as converted')
                ->groupBy('channel_id')
                ->with('channel:id,name')
                ->get(),
        ];
        return $this->success($data);
    }
}