RegionalDistributionProvider.php

<?php

namespace App\State;

use ApiPlatform\Metadata\Operation;
use ApiPlatform\State\ProviderInterface;
use App\ApiResource\RegionalDistribution;
use Doctrine\DBAL\Connection;
use Symfony\Component\HttpFoundation\RequestStack;

final class RegionalDistributionProvider implements ProviderInterface
{
    public function __construct(
        private readonly Connection $connection,
        private readonly RequestStack $requestStack
    ) {}

    public function provide(Operation $operation, array $uriVariables = [], array $context = []): iterable
    {
        $request = $this->requestStack->getCurrentRequest();
        $year = $request?->query->getInt('year', 2023);

        $sql = <<<SQL
            SELECT region_code, region_name, SUM(collected_volume) AS collected_volume
            FROM tax_data
            WHERE year = :year
            GROUP BY region_code, region_name
            ORDER BY collected_volume DESC
        SQL;

        $rows = $this->connection->fetchAllAssociative($sql, ['year' => $year]);

        foreach ($rows as $row) {
            $item = new RegionalDistribution();
            $item->regionCode = (string) $row['region_code'];
            $item->regionName = (string) $row['region_name'];
            $item->collectedVolume = (float) $row['collected_volume'];
            yield $item;
        }
    }
}