*/ class AnnouncementRepository extends ServiceEntityRepository { public function __construct(ManagerRegistry $registry) { parent::__construct($registry, Announcement::class); } public function countPublicByUserAndYear(int $userId, int $year): int { $start = new \DateTimeImmutable(sprintf('%d-01-01 00:00:00', $year)); $end = $start->modify('+1 year'); return (int) $this->createQueryBuilder('a') ->select('COUNT(a.id)') ->andWhere('IDENTITY(a.user) = :userId') ->andWhere('a.locationProvider IS NULL') ->andWhere('a.createdAt >= :start') ->andWhere('a.createdAt < :end') ->setParameter('userId', $userId) ->setParameter('start', $start) ->setParameter('end', $end) ->getQuery() ->getSingleScalarResult(); } public function countByUserAndYear(int $userId, int $year): int { $start = new \DateTimeImmutable(sprintf('%d-01-01 00:00:00', $year)); $end = $start->modify('+1 year'); return (int) $this->createQueryBuilder('a') ->select('COUNT(a.id)') ->andWhere('IDENTITY(a.user) = :userId') ->andWhere('a.createdAt >= :start') ->andWhere('a.createdAt < :end') ->setParameter('userId', $userId) ->setParameter('start', $start) ->setParameter('end', $end) ->getQuery() ->getSingleScalarResult(); } /** * Liste paginée pour le super-admin. * * @param array{ * search?: string|null, * user_id?: int|null, * type?: string|null, * status?: string|null, * date_from?: string|null, * date_to?: string|null, * provider_id?: int|null, * } $filters * * @return array{items: Announcement[], total: int} */ public function adminSearchPaginated(array $filters, int $page, int $limit): array { $page = max(1, $page); $limit = min(100, max(1, $limit)); $offset = ($page - 1) * $limit; $qb = $this->createQueryBuilder('a') ->leftJoin('a.user', 'u')->addSelect('u') ->leftJoin('a.category', 'c')->addSelect('c') ->leftJoin('a.location', 'l')->addSelect('l') ->leftJoin('a.locationProvider', 'lp')->addSelect('lp') ->orderBy('a.createdAt', 'DESC'); // Miroirs messagerie prestataire (FO-CONTACT-*) : hors liste admin « Annonces ». $qb->andWhere('a.referenceCode IS NULL OR a.referenceCode NOT LIKE :foContactPrefix') ->setParameter('foContactPrefix', 'FO-CONTACT-%'); $search = isset($filters['search']) ? trim((string) $filters['search']) : ''; if ($search !== '') { $like = '%' . mb_strtolower($search) . '%'; $or = $qb->expr()->orX( 'LOWER(a.title) LIKE :q', 'LOWER(a.description) LIKE :q', 'LOWER(a.referenceCode) LIKE :q', 'LOWER(u.email) LIKE :q', 'LOWER(u.firstName) LIKE :q', 'LOWER(u.lastName) LIKE :q', ); if (ctype_digit($search)) { $or->add('a.id = :qid'); $qb->setParameter('qid', (int) $search); } $qb->andWhere($or)->setParameter('q', $like); } if (!empty($filters['user_id'])) { $qb->andWhere('u.id = :userId')->setParameter('userId', (int) $filters['user_id']); } if (!empty($filters['type']) && in_array($filters['type'], ['lost', 'found'], true)) { $qb->andWhere('a.type = :type')->setParameter('type', $filters['type']); } if (!empty($filters['status']) && in_array($filters['status'], ['active', 'resolved', 'closed'], true)) { $qb->andWhere('a.status = :status')->setParameter('status', $filters['status']); } if (!empty($filters['provider_id'])) { $qb->andWhere('lp.id = :providerId')->setParameter('providerId', (int) $filters['provider_id']); } if (!empty($filters['only_individual'])) { $qb->andWhere('a.locationProvider IS NULL'); } if (!empty($filters['date_from'])) { $from = \DateTimeImmutable::createFromFormat('Y-m-d', (string) $filters['date_from']); if ($from instanceof \DateTimeImmutable) { $qb->andWhere('a.createdAt >= :dateFrom')->setParameter('dateFrom', $from->setTime(0, 0, 0)); } } if (!empty($filters['date_to'])) { $to = \DateTimeImmutable::createFromFormat('Y-m-d', (string) $filters['date_to']); if ($to instanceof \DateTimeImmutable) { $qb->andWhere('a.createdAt <= :dateTo')->setParameter('dateTo', $to->setTime(23, 59, 59)); } } $countQb = clone $qb; $countQb->select('COUNT(DISTINCT a.id)'); $total = (int) $countQb->getQuery()->getSingleScalarResult(); $items = $qb ->setFirstResult($offset) ->setMaxResults($limit) ->getQuery() ->getResult(); return ['items' => $items, 'total' => $total]; } /** * Créations d'annonces agrégées par jour, semaine (lundi), mois ou heure (0-23). * * @return list */ public function countCreationsGrouped( \DateTimeImmutable $from, \DateTimeImmutable $to, string $granularity, ?string $city = null, ): array { $granularity = match ($granularity) { 'week', 'month', 'hour' => $granularity, default => 'day', }; $periodExpr = match ($granularity) { 'week' => "DATE(DATE_SUB(a.created_at, INTERVAL WEEKDAY(a.created_at) DAY))", 'month' => "DATE_FORMAT(a.created_at, '%Y-%m-01')", 'hour' => 'HOUR(a.created_at)', default => 'DATE(a.created_at)', }; $conn = $this->getEntityManager()->getConnection(); $cityFilter = ''; $params = [ 'from' => $from->format('Y-m-d H:i:s'), 'to' => $to->format('Y-m-d H:i:s'), ]; if ($city !== null && trim($city) !== '') { $cityFilter = ' AND LOWER(TRIM(l.city)) = LOWER(:city)'; $params['city'] = trim($city); } $sql = <<= :from AND a.created_at <= :to{$cityFilter} GROUP BY period ORDER BY period ASC SQL; $rows = $conn->executeQuery($sql, $params)->fetchAllAssociative(); $indexed = []; foreach ($rows as $row) { $period = (string) $row['period']; $indexed[$period] = [ 'period' => $period, 'total' => (int) $row['total'], 'lost' => (int) $row['lost'], 'found' => (int) $row['found'], ]; } if ($granularity === 'hour') { return $this->fillHourSeriesGaps($indexed); } return $this->fillCreationSeriesGaps($indexed, $from, $to, $granularity); } /** * @return list */ public function findCitiesWithAnnouncements(): array { $conn = $this->getEntityManager()->getConnection(); $sql = << '' GROUP BY TRIM(l.city) ORDER BY count DESC, city ASC SQL; $rows = $conn->executeQuery($sql)->fetchAllAssociative(); return array_map( static fn (array $row): array => [ 'city' => (string) $row['city'], 'count' => (int) $row['count'], ], $rows, ); } /** * @param array $indexed * * @return list */ private function fillHourSeriesGaps(array $indexed): array { $result = []; for ($hour = 0; $hour < 24; ++$hour) { $key = (string) $hour; $result[] = $indexed[$key] ?? [ 'period' => $key, 'total' => 0, 'lost' => 0, 'found' => 0, ]; } return $result; } /** * @param array $indexed * * @return list */ private function fillCreationSeriesGaps( array $indexed, \DateTimeImmutable $from, \DateTimeImmutable $to, string $granularity, ): array { $result = []; $cursor = $this->alignToPeriodStart($from, $granularity); $end = $this->alignToPeriodStart($to, $granularity); while ($cursor <= $end) { $key = $cursor->format('Y-m-d'); $result[] = $indexed[$key] ?? [ 'period' => $key, 'total' => 0, 'lost' => 0, 'found' => 0, ]; $cursor = $this->advancePeriod($cursor, $granularity); } return $result; } private function alignToPeriodStart(\DateTimeImmutable $date, string $granularity): \DateTimeImmutable { return match ($granularity) { 'week' => $date->modify('monday this week')->setTime(0, 0, 0), 'month' => $date->modify('first day of this month')->setTime(0, 0, 0), default => $date->setTime(0, 0, 0), }; } private function advancePeriod(\DateTimeImmutable $date, string $granularity): \DateTimeImmutable { return match ($granularity) { 'week' => $date->modify('+1 week'), 'month' => $date->modify('first day of next month'), default => $date->modify('+1 day'), }; } // /** // * @return Announcement[] Returns an array of Announcement objects // */ // public function findByExampleField($value): array // { // return $this->createQueryBuilder('a') // ->andWhere('a.exampleField = :val') // ->setParameter('val', $value) // ->orderBy('a.id', 'ASC') // ->setMaxResults(10) // ->getQuery() // ->getResult() // ; // } // public function findOneBySomeField($value): ?Announcement // { // return $this->createQueryBuilder('a') // ->andWhere('a.exampleField = :val') // ->setParameter('val', $value) // ->getQuery() // ->getOneOrNullResult() // ; // } }