BarChartService.php

<?php

namespace App\Service;

use App\Dto\BarChart\BarChartInput;
use App\Dto\BarChart\BarChartOutput;
use Doctrine\ORM\EntityManagerInterface;
use App\Entity\Sale;

class BarChartService
{
    private EntityManagerInterface $entityManager;

    public function __construct(EntityManagerInterface $entityManager)
    {

        $this->entityManager = $entityManager;
        $emConfig = $this->entityManager->getConfiguration();
        $emConfig->addCustomDatetimeFunction('YEAR', 'DoctrineExtensions\Query\Postgresql\Year');
        $emConfig->addCustomDatetimeFunction('MONTH', 'DoctrineExtensions\Query\Postgresql\Month');

    }

    public function getBarChartData(BarChartInput $input): array
    {
        $startDate = new \DateTime($input->start);
        $endDate = new \DateTime($input->end);

        $queryBuilder = $this->entityManager->createQueryBuilder();

        switch ($input->granularity) {
            case 'day':
                $groupByExpression = 's.date';
                break;
            case 'month':
                $groupByExpression = 'MONTH(s.date)';
                break;
            case 'year':
                $groupByExpression = 'YEAR(s.date)';
                break;
            default:
                throw new \InvalidArgumentException('Invalid granularity');
        }

        $result = $queryBuilder
            ->select("{$groupByExpression} as date", 'COUNT(s.id) as occurrences')
            ->from(Sale::class, 's')
            ->where('s.date BETWEEN :start AND :end')
            ->setParameter('start', $startDate->format('Y-m-d'))
            ->setParameter('end', $endDate->format('Y-m-d'))
            ->groupBy("{$groupByExpression}")
            ->orderBy('date')
            ->getQuery()
            ->getResult();

        $output = [];
        foreach ($result as $row) {
            $dateString = $row['date'] instanceof \DateTimeInterface ? $row['date']->format('Y-m-d') : $row['date'];
            $output[] = new BarChartOutput($dateString, (int)$row['occurrences']);
        }

        return $output;
    }

}