SaleService.php

<?php

namespace App\Service;

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

class SaleService
{
    private EntityManagerInterface $entityManager;

    public function __construct(EntityManagerInterface $entityManager)
    {
        $this->entityManager = $entityManager;
    }

    public function getTimeSeries(): array
    {
        $queryBuilder = $this->entityManager->createQueryBuilder();

        $result = $queryBuilder
            ->select('MONTH(s.date) as month', 'YEAR(s.date) as year', 'AVG(CASE WHEN s.surface <> 0 THEN s.amount / s.surface ELSE 0 END) as average_price')
            ->from(Sale::class, 's')
            ->groupBy('year, month')
            ->orderBy('year, month')
            ->getQuery()
            ->getResult();

        $monthlyAveragePriceEvolutions = [];
        foreach ($result as $row) {
            $monthlyAveragePriceEvolutions[] = new MonthlyAveragePriceEvolution(
                (int)$row['month'],
                (int)$row['year'],
                (float)$row['average_price']
            );
        }

        return $monthlyAveragePriceEvolutions;
    }

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

        $queryBuilder = $this->entityManager->createQueryBuilder();
        $output = [];

        if ($input->granularity == 'month') {
            $result = $queryBuilder
                ->select('YEAR(s.date) as year', 'MONTH(s.date) as month', 'COUNT(s.id) as occurrences')
                ->from(Sale::class, 's')
                ->where('s.date BETWEEN :start AND :end')
                ->setParameter('start', $startDate)
                ->setParameter('end', $endDate)
                ->groupBy('year', 'month')
                ->orderBy('year')
                ->getQuery()
                ->getResult();
        } else {
            switch ($input->granularity) {
                case 'day':
                    $groupByExpression = 's.date';
                    $groupByAlias = 'date';
                    $dateFormat = 'Y-m-d';
                    break;
                case 'year':
                    $groupByExpression = 'YEAR(s.date)';
                    $groupByAlias = 'year';
                    $dateFormat = 'Y';
                    break;
                default:
                    throw new \InvalidArgumentException('Invalid granularity');
            }

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

        foreach ($result as $row) {
            if ($input->granularity == 'month') {
                $month = $row['month'];
                $year = $row['year'];
                $dateString = $year . '-' . str_pad($month, 2, '0', STR_PAD_LEFT);
            } else {
                $dateString = $row[$groupByAlias] instanceof \DateTimeInterface ? $row[$groupByAlias]->format($dateFormat) : $row[$groupByAlias];
            }

            $output[] = new BarChartOutput($dateString, (int)$row['occurrences']);
        }

        return $output;
    }

    public function getDonutChartData(string $year): array
    {
        $repository = $this->entityManager->getRepository(Sale::class);

        $result = $repository->createQueryBuilder('s')
            ->select('s.region', 'COUNT(s.id) as occurrences')
            ->andWhere('YEAR(s.date) = :year')
            ->setParameter('year', $year)
            ->groupBy('s.region')
            ->getQuery()
            ->getResult();

        $data = [];
        foreach ($result as $row) {
            $data[] = [
                'region' => $row['region'],
                'occurrences' => (int)$row['occurrences'],
            ];
        }

        return $data;
    }
}