Symfony Profiler

<?php
namespace App\Controller;
use App\Entity\Lesson;
use App\Entity\TutorNoShowIncident;
use App\Repository\LessonChangeLogRepository;
use App\Repository\TutorNoShowIncidentRepository;
use Doctrine\ORM\QueryBuilder;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Security;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;
/**
* @Route("/tutor-no-show-incidents")
*/
class TutorNoShowIncidentController extends AbstractController
{
private const DATE_FORMAT = 'Y-m-d H:i';
private const DELETED_LESSON_LABEL = 'Ištrintas užsiėmimas';
/**
* @Route("/", name="tutor_no_show_incidents_index", methods={"GET"})
* @Security("is_granted('ROLE_ADMIN') and is_granted('IS_AUTHENTICATED_FULLY')")
*/
public function index(): Response
{
return $this->render('tutor_no_show_incidents/index.html.twig');
}
/**
* @Route("/ajax", name="tutor_no_show_incidents_ajax", methods={"POST"})
* @Security("is_granted('ROLE_ADMIN') and is_granted('IS_AUTHENTICATED_FULLY')")
*/
public function ajax(
Request $request,
TutorNoShowIncidentRepository $incidentRepository,
LessonChangeLogRepository $changeLogRepository
): Response {
$page = max(1, (int) $request->request->get('page', 1));
$perPage = max(1, (int) $request->request->get('perPage', 20));
$dateFrom = $request->request->get('dateFrom');
$dateTo = $request->request->get('dateTo');
$filters = $request->request->all('filters');
$qb = $incidentRepository->createFilteredQueryBuilder($dateFrom, $dateTo)
->leftJoin('i.lesson', 'l')
->leftJoin('i.teacher', 't')
->leftJoin('i.child', 'c');
$this->applyTableFilters($qb, $filters);
$qb->orderBy('i.recordedAt', 'DESC');
$totalCount = (clone $qb)
->select('COUNT(i.id)')
->getQuery()
->getSingleScalarResult();
$incidents = (clone $qb)
->select('i, l, t, c')
->setFirstResult(($page - 1) * $perPage)
->setMaxResults($perPage)
->getQuery()
->getResult();
$lessonIds = array_filter(array_map(fn($i) => $i->getLesson()?->getId(), $incidents));
$statusChangeLogs = $this->fetchStatusChangeLogs($lessonIds, $changeLogRepository);
$data = array_map(
fn($incident) => $this->formatIncidentRow($incident, $statusChangeLogs),
$incidents
);
return new JsonResponse([
'last_page' => (int) ceil($totalCount / $perPage),
'data' => $data,
'kpis' => $this->buildKpis($incidentRepository, $dateFrom, $dateTo),
]);
}
/**
* @Route("/csv", name="tutor_no_show_incidents_csv", methods={"GET"})
* @Security("is_granted('ROLE_ADMIN') and is_granted('IS_AUTHENTICATED_FULLY')")
*/
public function csv(
Request $request,
TutorNoShowIncidentRepository $incidentRepository,
LessonChangeLogRepository $changeLogRepository
): Response {
$dateFrom = $request->query->get('dateFrom');
$dateTo = $request->query->get('dateTo');
$incidents = $incidentRepository->createFilteredQueryBuilder($dateFrom, $dateTo)
->leftJoin('i.lesson', 'l')
->select('i, l')
->orderBy('i.recordedAt', 'DESC')
->getQuery()
->getResult();
$lessonIds = array_filter(array_map(fn($i) => $i->getLesson()?->getId(), $incidents));
$statusChangeLogs = $this->fetchStatusChangeLogs($lessonIds, $changeLogRepository);
$f = fopen('php://memory', 'w');
fputcsv($f, [
'Korepetitorius',
'Korepetitoriaus ID',
'Mokinys',
'Pamoka',
'Korepetitoriaus neinformuotai praleistas užsiėmimas',
'Pakeistas statusas',
'Dabartinis statusas',
'Statusas pakeistas',
'Dienos nuo mokinio pirmos pamokos',
], ';');
foreach ($incidents as $incident) {
[$wasChanged, $currentStatus, $statusChangedAt] = $this->resolveIncidentStatus(
$incident,
$statusChangeLogs
);
fputcsv($f, [
$incident->getTeacherName() ?? '',
$incident->getTeacherIndividualNumber() ?? '',
$incident->getChildName() ?? '',
$incident->getLessonStartTime()->format(self::DATE_FORMAT),
$incident->getRecordedAt()->format(self::DATE_FORMAT),
$wasChanged ? 'Taip' : 'Ne',
$currentStatus,
$statusChangedAt ?? '-',
$incident->getDaysSinceFirstLesson() ?? '-',
], ';');
}
fseek($f, 0);
header('Content-Encoding: UTF-8');
header('Content-Type: text/csv; charset=UTF-8');
header('Content-Disposition: attachment; filename="tutor_no_show_incidents.csv";');
echo "\xEF\xBB\xBF";
fpassthru($f);
exit();
}
private function buildKpis(
TutorNoShowIncidentRepository $incidentRepository,
?string $dateFrom,
?string $dateTo
): array {
$qb = $incidentRepository->createFilteredQueryBuilder($dateFrom, $dateTo);
$total = (clone $qb)
->select('COUNT(i.id)')
->getQuery()
->getSingleScalarResult();
$uniqueTutors = (clone $qb)
->select('COUNT(DISTINCT IDENTITY(i.teacher))')
->getQuery()
->getSingleScalarResult();
$tutorCounts = (clone $qb)
->select('IDENTITY(i.teacher) as teacherId, COUNT(i.id) as cnt')
->groupBy('i.teacher')
->getQuery()
->getResult();
$tutorsWith2Plus = 0;
$tutorsWith3Plus = 0;
foreach ($tutorCounts as $row) {
if ($row['cnt'] >= 2) {
$tutorsWith2Plus++;
}
if ($row['cnt'] >= 3) {
$tutorsWith3Plus++;
}
}
$avgDays = (clone $qb)
->select('AVG(i.daysSinceFirstLesson)')
->getQuery()
->getSingleScalarResult();
return [
'total' => (int) $total,
'uniqueTutors' => (int) $uniqueTutors,
'tutorsWith2Plus' => $tutorsWith2Plus,
'tutorsWith3Plus' => $tutorsWith3Plus,
'avgDaysSinceFirstLesson' => $avgDays !== null ? round((float) $avgDays, 1) : null,
];
}
private function applyTableFilters(QueryBuilder $qb, array $filters): void
{
foreach ($filters as $filter) {
$value = $filter['value'] ?? '';
$field = $filter['field'] ?? '';
if ($value === '' || $value === null) {
continue;
}
if ($field === 'teacherFilter') {
$qb->andWhere('(i.teacherName LIKE :teacherFilter OR i.teacherIndividualNumber LIKE :teacherFilter)')
->setParameter('teacherFilter', '%' . $value . '%');
} elseif ($field === 'childName') {
$qb->andWhere('i.childName LIKE :childName')
->setParameter('childName', '%' . $value . '%');
} elseif ($field === 'wasStatusChanged') {
$noShowStatus = Lesson::STATUS_TUTOR_UNINFORMED_MISSED['value'];
if ($value === 'yes') {
$qb->andWhere('i.lesson IS NULL OR l.status != :noShowStatus')
->setParameter('noShowStatus', $noShowStatus);
} elseif ($value === 'no') {
$qb->andWhere('i.lesson IS NOT NULL AND l.status = :noShowStatus')
->setParameter('noShowStatus', $noShowStatus);
}
} elseif ($field === 'daysSinceFirstLesson') {
$qb->andWhere('i.daysSinceFirstLesson = :days')
->setParameter('days', (int) $value);
}
}
}
private function fetchStatusChangeLogs(array $lessonIds, LessonChangeLogRepository $changeLogRepository): array
{
if (!$lessonIds) {
return [];
}
$logs = $changeLogRepository->createQueryBuilder('lcl')
->where('lcl.lesson IN (:lessonIds)')
->andWhere('lcl.changedField = :field')
->andWhere('lcl.changedFrom = :from')
->setParameter('lessonIds', array_values($lessonIds))
->setParameter('field', 'status')
->setParameter('from', Lesson::STATUS_TUTOR_UNINFORMED_MISSED['value'])
->orderBy('lcl.dateAdd', 'ASC')
->getQuery()
->getResult();
$result = [];
foreach ($logs as $log) {
if (!$log->getLesson()) {
continue;
}
$lid = $log->getLesson()->getId();
if (!isset($result[$lid])) {
$result[$lid] = $log;
}
}
return $result;
}
private function resolveIncidentStatus(TutorNoShowIncident $incident, array $statusChangeLogs): array
{
$lesson = $incident->getLesson();
$noShowStatusValue = Lesson::STATUS_TUTOR_UNINFORMED_MISSED['value'];
if (!$lesson) {
return [true, self::DELETED_LESSON_LABEL, null];
}
if ($lesson->getStatusValue() !== $noShowStatusValue) {
$lessonId = $lesson->getId();
$statusChangedAt = isset($statusChangeLogs[$lessonId])
? $statusChangeLogs[$lessonId]->getDateAdd()?->format(self::DATE_FORMAT)
: null;
return [true, $lesson->getStatus(), $statusChangedAt];
}
return [false, Lesson::STATUS_TUTOR_UNINFORMED_MISSED['text'], null];
}
private function formatIncidentRow(TutorNoShowIncident $incident, array $statusChangeLogs): array
{
[$wasChanged, $currentStatus, $statusChangedAt] = $this->resolveIncidentStatus(
$incident,
$statusChangeLogs
);
return [
'id' => $incident->getId(),
'teacherName' => $incident->getTeacherName(),
'teacherIndividualNumber' => $incident->getTeacherIndividualNumber(),
'teacherId' => $incident->getTeacher()?->getId(),
'childName' => $incident->getChildName(),
'childId' => $incident->getChild()?->getId(),
'lessonStartTime' => $incident->getLessonStartTime()->format(self::DATE_FORMAT),
'recordedAt' => $incident->getRecordedAt()->format(self::DATE_FORMAT),
'wasStatusChanged' => $wasChanged,
'currentStatus' => $currentStatus,
'statusChangedAt' => $statusChangedAt,
'daysSinceFirstLesson' => $incident->getDaysSinceFirstLesson(),
];
}
}

src/Controller/TutorNoShowIncidentController.php line 29