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();
        $tax = $request?->query->get('tax', 'tfpb');
        $year = $request?->query->getInt('year', 2022);
        $volumeColumn = $this->resolveVolumeColumn($tax);

        $sql = <<<SQL
            SELECT region_code, region_name, SUM($volumeColumn) AS collected_volume
            FROM tax_data
            WHERE year = :year
                AND $volumeColumn IS NOT NULL
            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;
        }
    }

    private function resolveVolumeColumn(?string $tax): string
    {
        return match (strtolower((string) $tax)) {
            'tfpnb' => 'volume_tfpnb',
            'tfpb' => 'volume_tfpb',
            'th' => 'volume_th',
            'cfe' => 'volume_cfe',
            default => 'volume_tfpb',
        };
    }
}