src/Controller/HomeController.php - master - forest-lynx/project

src/Controller/HomeController.php
127 строк6 KB
forest-lynx
init
04 дек 2024, 10:44
04 дек 2024, 10:44f4ceba2
<?php

namespace App\Controller;

use App\Enum\EmployeeStatus;
use App\Enum\Status;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\Query\ResultSetMapping;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Attribute\Route;

#[Route('/', name: 'homepage')]
class HomeController extends AbstractController
{
    public function __construct(private EntityManagerInterface $entityManager)
    {
    }

    public function __invoke(): Response
    {
        return $this->render('home/index.html.twig', [
            'summary_projects' => $this->summaryReportProjects(),
            'detailed_projects' => $this->detailedReportProjects(),
            'summary_developers' => $this->summaryReportDevelopers(),
        ]);
    }

    private function summaryReportProjects(): array
    {
        return $this->entityManager->createQuery(
            'SELECT 
                COUNT(p) AS total_projects,
                SUM(CASE WHEN p.status = :created THEN 1 ELSE 0 END) AS total_created,
                SUM(CASE WHEN p.status = :in_progress THEN 1 ELSE 0 END) AS total_in_progress,
                SUM(CASE WHEN p.status = :completed THEN 1 ELSE 0 END) AS total_completed,
                SUM(CASE WHEN p.status = :cancelled THEN 1 ELSE 0 END) AS total_cancelled,
                AVG(DATE_DIFF(COALESCE(p.dateTo, CURRENT_DATE()), p.dateFrom)) AS avg_duration
            FROM App\Entity\Project p'
        )
        ->setParameters([
            'created' => Status::CREATED,
            'in_progress' => Status::IN_PROGRESS,
            'completed' => Status::COMPLETED,
            'cancelled' => Status::CANCELLED
        ])->getSingleResult();
    }

    private function summaryReportDevelopers(): array
    {
        $rsm = new ResultSetMapping();
        $rsm->addScalarResult('total_developers', 'total_developers', 'integer');
        $rsm->addScalarResult('total_working', 'total_working', 'integer');
        $rsm->addScalarResult('total_dismissed', 'total_dismissed', 'integer');
        $rsm->addScalarResult('total_annual_leave', 'total_annual_leave', 'integer');
        $rsm->addScalarResult('total_remote_work', 'total_remote_work', 'integer');
        $rsm->addScalarResult('total_trainee', 'total_trainee', 'integer');
        $rsm->addScalarResult('avg_age', 'avg_age', 'integer');

        return $this->entityManager->createNativeQuery(
            'SELECT
                COUNT(1) AS total_developers,
                SUM(CASE WHEN status = :working THEN 1 ELSE 0 END) AS total_working,
                SUM(CASE WHEN status = :dismissed THEN 1 ELSE 0 END) AS total_dismissed,
                SUM(CASE WHEN status = :annual_leave THEN 1 ELSE 0 END) AS total_annual_leave,
                SUM(CASE WHEN status = :remote_work THEN 1 ELSE 0 END) AS total_remote_work,
                SUM(CASE WHEN status = :trainee THEN 1 ELSE 0 END) AS total_trainee,
                AVG(TIMESTAMPDIFF(YEAR, birthday, CURRENT_DATE())) AS avg_age
            FROM developer',
            $rsm
        )->setParameters([
            'working' => EmployeeStatus::WORKING,
            'dismissed' => EmployeeStatus::DISMISSED,
            'annual_leave' => EmployeeStatus::ANNUAL_LEAVE,
            'remote_work' => EmployeeStatus::REMOTE_WORK,
            'trainee' => EmployeeStatus::TRAINEE,
        ])->getSingleResult();
    }

    private function detailedReportProjects(): array
    {
        $rsm = new ResultSetMapping();
        $rsm->addScalarResult('project_name', 'project_name', 'string');
        $rsm->addScalarResult('project_status', 'project_status', 'string');
        $rsm->addScalarResult('project_duration_days', 'project_duration_days', 'integer');
        $rsm->addScalarResult('total_developers', 'total_developers', 'integer');
        $rsm->addScalarResult('avg_developer_age', 'avg_developer_age', 'integer');
        $rsm->addScalarResult('developer_positions', 'developer_positions', 'string');

        return $this->entityManager->createNativeQuery(
            'SELECT  
                projects.name AS project_name,
                projects.status AS project_status, 
                DATEDIFF(COALESCE(projects.date_to, CURDATE()), projects.date_from) AS project_duration_days,
                COUNT(project_details.developer_id) AS total_developers,
                ROUND(AVG(project_details.developer_age), 1) AS avg_developer_age,
                GROUP_CONCAT(DISTINCT project_details.developer_position) AS developer_positions
            FROM project projects
            LEFT JOIN (
                SELECT DISTINCT
                    participation.project_id,
                    participation.developer_id,
                    TIMESTAMPDIFF(YEAR, dev.birthday, CURDATE()) AS developer_age,
                    dev_position.name AS developer_position
                FROM participation_project participation
                JOIN developer dev ON participation.developer_id = dev.id
                LEFT JOIN (
                    SELECT 
                        developer_id, 
                        position_id, 
                        date_to
                    FROM employment current_employment
                    WHERE date_to IS NULL 
                    OR date_to = (
                        SELECT MAX(date_to)
                        FROM employment employment_history
                        WHERE employment_history.developer_id = current_employment.developer_id
                    )
                ) current_position ON participation.developer_id = current_position.developer_id
                LEFT JOIN position dev_position ON current_position.position_id = dev_position.id
                GROUP BY participation.project_id, participation.developer_id
            ) project_details ON projects.id = project_details.project_id
            GROUP BY projects.id, projects.name, projects.status',
            $rsm
        )->getResult();
    }
}