RegionEvolutionProvider.php

<?php

namespace App\State;

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

final class RegionEvolutionProvider 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');
        $startYear = $request?->query->getInt('startYear', 2022);
        $endYear = $request?->query->getInt('endYear', 2022);

        $rateColumn = $this->resolveRateColumn($tax);

        $sql = <<<SQL
            SELECT year, region_code, region_name, AVG(NULLIF($rateColumn, 0)) AS rate
            FROM tax_data
            WHERE year BETWEEN :startYear AND :endYear
              AND $rateColumn > 0
            GROUP BY year, region_code, region_name
            ORDER BY year, region_name
        SQL;

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

        foreach ($rows as $row) {
            $item = new RegionEvolutionPoint();
            $item->id = $row['year'] . '_' . $row['region_code'];
            $item->year = (int) $row['year'];
            $item->regionCode = (string) $row['region_code'];
            $item->regionName = (string) $row['region_name'];
            $item->rate = (float) $row['rate'];
            yield $item;
        }
    }

    private function resolveRateColumn(?string $tax): string
    {
        return match (strtolower((string) $tax)) {
            'tfpnb' => 'rate_tfpnb',
            'tfpb' => 'rate_tfpb',
            'th' => 'rate_th',
            'cfe' => 'rate_cfe',
            default => 'rate_tfpb',
        };
    }
}