<?php

namespace AppBundle\Controller\Admin;

use AppBundle\Entity\OperationPEE;
use AppBundle\Entity\User;
use AppBundle\Enum\DemandePEEState;
use AppBundle\Enum\OperationITDLCState;
use AppBundle\Enum\OperationITDLCType;
use AppBundle\Enum\OperationPEEState;
use AppBundle\Enum\UserType;
use AppBundle\Export\OperationItdlcExporter;
use AppBundle\Export\OperationPeeExporter;
use AppBundle\Export\UserExporter;
use AppBundle\Translation\ConstTranslator;
use Cocur\Slugify\Slugify;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\Tools\Pagination\Paginator;
use PhpOffice\PhpSpreadsheet\Exception as PhpSpreadsheetException;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Route;
use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\Translation\TranslatorInterface;
use AppBundle\Entity\OperationITDLC;

class StatisticController extends Controller
{
    /**
     * @var EntityManagerInterface
     */
    private $em;

    /**
     * @var OperationItdlcExporter
     */
    private $operationItdlcExporter;

    /**
     * @var OperationPeeExporter
     */
    private $operationPeeExporter;

    /**
     * @var TranslatorInterface
     */
    private $translator;

    /**
     * @var UserExporter
     */
    private $userExporter;

    /**
     * @var ConstTranslator
     */
    private $utils;
    
    public function __construct(
        EntityManagerInterface $em,
        OperationItdlcExporter $operationItdlcExporter,
        OperationPeeExporter $operationPeeExporter,
        TranslatorInterface $translator,
        UserExporter $userExporter,
        ConstTranslator $utils
    ) {
        $this->em = $em;
        $this->operationItdlcExporter = $operationItdlcExporter;
        $this->operationPeeExporter = $operationPeeExporter;
        $this->translator = $translator;
        $this->userExporter = $userExporter;
        $this->utils = $utils;
    }

    /**
     * Liste des utilisateurs (professeur et ingénieur / technicien)
     *
     * @Route(
     *      "/statistique/user/list/{type}/{page}",
     *      requirements = {"page": "\d+", "type"="teacher|engineer"},
     *      name = "admin_statistics_user"
     * )
     *
     * @param Request $request
     * @param string $type
     * @param int $page
     * @return \Symfony\Component\HttpFoundation\Response
     * @throws \Doctrine\ORM\NonUniqueResultException
     */
    public function userAction(Request $request, string $type = 'teacher', int $page = 1)
    {
        $academies = null;
        $resultsMaxPerPage = $this->getParameter('results.admin.max_per_page');

        $qb = $this->em->getRepository(User::class)->findFromRequest($request, false);
        $qb
            ->setFirstResult(($page - 1) * $resultsMaxPerPage)
            ->setMaxResults($resultsMaxPerPage);

        if (UserType::TEACHER) {
            $academies = $this->em->getRepository(User::class)->findAcademies();
        }

        $users = new Paginator($qb);

        return $this->render('Admin/Stat/user.html.twig', array(
            'academies' => $academies,
            'type' => $type,
            'users' => $users,
            'nbPages' => ceil(count($users) / $resultsMaxPerPage),
        ));
    }

    /**
     * Statistiques pour chaque opération ITDLC
     *
     * @Route(
     *      "/statistique/operation-itdlc/list/{page}",
     *      requirements = {"page": "\d+"},
     *      name = "admin_statistics_itdlc_operation"
     * )
     *
     * @param Request $request
     * @param int $page
     * @return \Symfony\Component\HttpFoundation\Response
     */
    public function itdlcOperationAction(Request $request, int $page = 1)
    {
        $resultsMaxPerPage = $this->getParameter('results.admin.max_per_page');
        $firstresult = ($page - 1) * $resultsMaxPerPage;

        $datemin = $request->get('datemin');
        $datemax = $request->get('datemax');

        // Create query
        $qb = $this->em->createQueryBuilder();
        $qbParams = [];
        $qb
            ->select('o, a, p, i')
            ->from('AppBundle:OperationITDLC', 'o')
            ->leftJoin('o.availability', 'a')
            ->leftJoin('o.inge', 'i')
            ->leftJoin('o.prof', 'p')
            ->where('o.type = :o_type')
            ->orderBy('a.dateVisite', 'desc')
            ->addOrderBy('o.id', 'desc');
        $qbParams['o_type'] = OperationITDLCType::OPERATION;

        if ($datemin) {
            $dateMin = \DateTime::createFromFormat('d/m/Y', $datemin);

            $qb->andWhere('a.dateVisite >= :a_datemin');
            $qbParams['a_datemin'] = $dateMin;
        }

        if ($datemax) {
            $dateMax = \DateTime::createFromFormat('d/m/Y', $datemax);

            $qb->andWhere('a.dateVisite <= :a_datemax');
            $qbParams['a_datemax'] = $dateMax;
        }

        $ope_state = $request->get('state');

        if ($ope_state !== 'all') {
            if (!empty($ope_state)) {
                $qb->andWhere('o.state IN (:o_state)');
                $qbParams['o_state'] = $ope_state;
            }
            else {
                // Default
                $qb->andWhere('o.state = :o_state');
                $qbParams['o_state'] = OperationITDLCState::CLOSED;
            }
        }

        $qb->setParameters($qbParams)
            ->setFirstResult($firstresult)
            ->setMaxResults($resultsMaxPerPage);
        $query = $qb->getQuery();

        // Construct pagination
        $ope = new Paginator($query, $fetchJoinCollection = false);

        return $this->render('Admin/Stat/itdlcOperation.html.twig', array(
            'opes' => $ope,
            'nbPages' => ceil(count($ope) / $resultsMaxPerPage),
            'pageactuelle' => $page,
            'datemin' => $datemin,
            'datemax' => $datemax,
            'states' => $this->utils->trans(OperationITDLCState::class),
            'search_state' => $ope_state ?? OperationITDLCState::CLOSED,
        ));
    }

    /**
     * Statistiques pour chaque opération PEE
     *
     * @Route(
     *      "/statistique/operation-pee/list/{page}",
     *      requirements = {"page": "\d+"},
     *      name = "admin_statistics_pee_operation"
     * )
     *
     * @param Request $request
     * @param int     $page
     * @return \Symfony\Component\HttpFoundation\Response
     */
    public function peeOperationAction(Request $request, int $page = 1)
    {
        $resultsMaxPerPage = $this->getParameter('results.admin.max_per_page');

        if (null !== $ope_datemin = $request->get('ope_datemin')) {
            $ope_datemin = \DateTime::createFromFormat('d/m/Y', $ope_datemin);
        }
        if (null !== $ope_datemax = $request->get('ope_datemax')) {
            $ope_datemax = \DateTime::createFromFormat('d/m/Y', $ope_datemax);
        }
        $ope_company = $request->get('ope_company');
        $ope_academy = $request->get('ope_academy');
        $opeStateSelected = $request->get('ope_state');

        // Statuts que l'on souhaite afficher
        $demandes_states_displayed = array_values(DemandePEEState::getConstants());
        $inscrits = array('total' => array_fill_keys(
            $demandes_states_displayed,
            array(
                "nb" => 0,
                "taux" => 0,
            )
        ));
        $inscrits['total']['participants'] = 0;
        $inscrits['page'] = 0;

        $opeStates = $this->utils->trans(OperationPEEState::class);

        // Récupération de la liste des académies des opérations
        $opeAcademies = $this->em->getRepository(User::class)->findAcademies();

        // Construction des requêtes selon les filtres indiqués: liste des opérations / nombre d'inscrits par statut de demande (Ex: 53 inscrits, 71 désistements...)
        $qb_filtered = $this->em->createQueryBuilder();
        $qb_status_filtered = $this->em->createQueryBuilder();

        $qb_filtered
            ->select('o')
            ->from('AppBundle:OperationPEE', 'o');

        $qb_status_filtered
            ->select('d.state AS name, COUNT(d.state) AS nb')
            ->from('AppBundle:DemandePEE', 'd')
            ->leftJoin('d.operationpee', 'o')
        ;

        if (!empty($ope_company)) {
            $qb_filtered
                ->leftJoin('o.company', 'c')
                ->andWhere($qb_filtered->expr()->orX(
                    $qb_filtered->expr()->like(
                        $qb_filtered->expr()->upper('c.name'),
                        $qb_filtered->expr()->upper(':c_name')
                    ),
                    $qb_filtered->expr()->like(
                        $qb_filtered->expr()->upper('o.site'),
                        $qb_filtered->expr()->upper(':o_site')
                    )
                ))
                ->setParameter('c_name', '%'. $ope_company .'%')
                ->setParameter('o_site', '%'. $ope_company .'%')
            ;

            $qb_status_filtered
                ->leftJoin('o.company', 'c')
                ->andWhere($qb_status_filtered->expr()->orX(
                    $qb_status_filtered->expr()->like(
                        $qb_status_filtered->expr()->upper('c.name'),
                        $qb_status_filtered->expr()->upper(':c_name')
                    ),
                    $qb_status_filtered->expr()->like(
                        $qb_status_filtered->expr()->upper('o.site'),
                        $qb_status_filtered->expr()->upper(':o_site')
                    )
                ))
                ->setParameter('c_name', '%'. $ope_company .'%')
                ->setParameter('o_site', '%'. $ope_company .'%')
            ;
        }
        if ($ope_datemin) {
            $qb_filtered
                ->andWhere('o.dateVisite >= :o_dateVisite_min')
                ->setParameter('o_dateVisite_min', $ope_datemin);

            $qb_status_filtered
                ->andWhere('o.dateVisite >= :o_dateVisite_min')
                ->setParameter('o_dateVisite_min', $ope_datemin);
        }
        if ($ope_datemax) {
            $qb_filtered
                ->andWhere('o.dateVisite <= :o_dateVisite_max')
                ->setParameter('o_dateVisite_max', $ope_datemax);

            $qb_status_filtered
                ->andWhere('o.dateVisite <= :o_dateVisite_max')
                ->setParameter('o_dateVisite_max', $ope_datemax);
        }
        if (!empty($ope_academy)) {
            $qb_filtered
                ->andWhere('o.academy = :o_academy')
                ->setParameter('o_academy', $ope_academy);

            $qb_status_filtered
                ->andWhere('o.academy = :o_academy')
                ->setParameter('o_academy', $ope_academy);
        }
        if (!empty($opeStateSelected)) {
            $qb_filtered
                ->andWhere('o.state = :o_state')
                ->setParameter('o_state', $opeStateSelected);

            $qb_status_filtered
                ->andWhere('o.state = :o_state')
                ->setParameter('o_state', $opeStateSelected);
        }
        $qb_filtered
            ->orderBy('o.academy', 'asc')
            ->addOrderBy('o.dateVisite', 'desc')
            ->addOrderBy('o.created', 'desc');

        $qb_status_filtered
            ->groupBy('d.state');

        /** @var OperationPEE[] $ope_filtered */
        $ope_filtered = $qb_filtered->getQuery()->getResult();

        // Compte le nombre total de participants
        foreach ($ope_filtered as $ope) {
            $inscrits['total']['participants'] += $ope->getNbPeople();
        }

        $status_demandes_filtered = $qb_status_filtered->getQuery()->getArrayResult();

        foreach ($demandes_states_displayed as $state_name) {
            $nb = 0;

            $status_found = array_filter($status_demandes_filtered, function ($status) use ($state_name) {
                return $status['name'] === $state_name;
            });

            if (1 === count($status_found)) {
                $state = array_pop($status_found); // array_pop() because array_filter preserves keys, so we cannot use $status_found[0].
                $nb = $state['nb'];
            }

            $inscrits['total'][$state_name]['nb'] = $nb;

            // Calcul du taux de participation moyen
            $status_participants_nb = $inscrits['total']['participants'];
            $status_participation_rate = 0;
            if ($status_participants_nb > 0) {
                $status_participation_rate = round($nb / $status_participants_nb * 100, 1);
            }
            $inscrits['total'][$state_name]['participation_rate'] = $status_participation_rate;
        }

        // Constructeur pagination
        $qb_filtered
            ->setFirstResult(($page - 1) * $resultsMaxPerPage)
            ->setMaxResults($resultsMaxPerPage);
        $ope_filtered_paginated = new Paginator($qb_filtered->getQuery());


        // Compte le nombre d'inscrits (pour tous les status: inscrit, pré-inscrit...) pour chaque opération
        foreach ($ope_filtered_paginated as $ope_key => $ope) {
            $query_subscribers = $this->em
                ->createQuery(
                    "SELECT
                        d.state AS state_name,
                        COUNT(d.state) AS nb
                    FROM AppBundle:DemandePEE d
                    WHERE d.operationpee = :dOperationpee
                    GROUP BY d.state")
                ->setParameters([
                    'dOperationpee' => $ope->getId(),
                ]);
            $subscribers = $query_subscribers->getResult();

            // Transforme les résultats sous forme de tableau clé=>valeur, clé étant le nom de l'état, valeur le nombre d'inscrits ayant cet état. Ex: 'Pré-inscrit' => 4.
            foreach ($subscribers as $key => $state) {
                $subscribers[$state['state_name']] = $state['nb'];
                unset($subscribers[$key]);
            }

            $inscrits[$ope_key] = [];
            foreach ($demandes_states_displayed as $state_name) {
                $nb = 0;
                if (array_key_exists($state_name, $subscribers)) {
                    $nb = $subscribers[$state_name];
                }

                $inscrits[$ope_key][$state_name] = $nb;
            }

            // Calcul du taux de participation à l'opération
            $ope_participants_nb = $ope->getNbPeople();
            $ope_participation_rate = 0;
            if ($ope_participants_nb > 0) {
                $ope_participation_rate = round($inscrits[$ope_key][DemandePEEState::REGISTERED] / $ope_participants_nb * 100, 1);
            }
            $inscrits[$ope_key]['participation_rate'] = $ope_participation_rate;
        }

        return $this->render('Admin/Stat/peeOperation.html.twig', array(
            'ope_paginated' => $ope_filtered_paginated,
            'ope_states' => $opeStates,
            'inscrits' => $inscrits,
            'nb_pages' => ceil(count($ope_filtered_paginated) / $resultsMaxPerPage),
            'pageactuelle' => $page,
            'ope_datemin' => $ope_datemin,
            'ope_datemax' => $ope_datemax,
            'ope_company' => $ope_company,
            'ope_academies' => $opeAcademies,
            'ope_academy' => $ope_academy,
            'ope_state_selected' => $opeStateSelected,
        ));
    }

    /**
     * Statisques pour les demandes PEE
     *
     * @Route(
     *      "/statistique/user/professeur/demandes/{page}",
     *      requirements = {"page": "\d+"},
     *      name = "admin_statistics_pee_demande"
     * )
     *
     * @param Request $request
     * @param int $page Page courante de la pagination
     * @return \Symfony\Component\HttpFoundation\Response
     */
    public function peeDemandeAction(Request $request, int $page = 1)
    {
        $resultsMaxPerPage = $this->getParameter('results.admin.max_per_page');

        $user_name = $request->get('user_name');
        $user_academy = $request->get('user_academy');
        $demande_state = $request->get('demande_state');
        if ($ope_datemin = $request->get('ope_datemin')) {
            $ope_datemin = \DateTime::createFromFormat('d/m/Y', $ope_datemin);
        }
        if ($ope_datemax = $request->get('ope_datemax')) {
            $ope_datemax = \DateTime::createFromFormat('d/m/Y', $ope_datemax);
        }
        $ope_company = $request->get('ope_company');
        $ope_state = $request->get('ope_state');

        $qbDemandes = $this->em->createQueryBuilder();
        $qbDemandes
            ->select('d, u, o')
            ->from('AppBundle:DemandePEE', 'd')
            ->leftJoin('d.cuser', 'u')
            ->leftJoin('d.operationpee', 'o')
            ->orderBy('u.lastname', 'ASC')
            ->addOrderBy('u.firstname', 'ASC')
            ->addOrderBy('u.id', 'ASC')
            ->setFirstResult(($page - 1) * $resultsMaxPerPage)
            ->setMaxResults($resultsMaxPerPage);
        if (!empty($user_name)) {
            $qbDemandes
                ->andWhere($qbDemandes->expr()->orX(
                    $qbDemandes->expr()->like('LOWER(u.firstname)', ':u_firstname'),
                    $qbDemandes->expr()->like('LOWER(u.lastname)', ':u_lastname')
                ))
                ->setParameter('u_firstname', "%".mb_strtolower($user_name)."%")
                ->setParameter('u_lastname', "%".mb_strtolower($user_name)."%");
        }
        if (!empty($user_academy)) {
            $qbDemandes
                ->andWhere('u.academy = :u_academy')
                ->setParameter('u_academy', $user_academy);
        }
        if (!empty($demande_state)) {
            $qbDemandes
                ->andWhere('d.state = :d_state')
                ->setParameter('d_state', $demande_state);
        }
        if ($ope_datemin) {
            $qbDemandes
                ->andWhere('o.dateVisite >= :o_datemin')
                ->setParameter('o_datemin', $ope_datemin);
        }
        if ($ope_datemax) {
            $qbDemandes
                ->andWhere('o.dateVisite <= :o_datemax')
                ->setParameter('o_datemax', $ope_datemax);
        }
        if (!empty($ope_company)) {
            $qbDemandes
                ->andWhere($qbDemandes->expr()->orX(
                    $qbDemandes->expr()->like('LOWER(o.company)', ':o_company'),
                    $qbDemandes->expr()->like('LOWER(o.site)', ':o_site')
                ))
                ->setParameter('o_company', "%".mb_strtolower($ope_company)."%")
                ->setParameter('o_site', "%".mb_strtolower($ope_company)."%");
        }
        if (!empty($ope_state)) {
            $qbDemandes
                ->andWhere('o.state = :o_state')
                ->setParameter('o_state', $ope_state);
        }

        // Constructeur pagination
        $demandes = new Paginator($qbDemandes);
        $nb_results = count($demandes);
        $nbPage = $nb_results/$resultsMaxPerPage;
        $pagination = [];
        for ($i = 1; $i < $nbPage+1; $i++) {
            array_push($pagination, $i);
        }

        return $this->render('Admin/Stat/peeDemande.html.twig', array(
            'demandes' => $demandes,
            'nb_results' => $nb_results,
            'pagination' => $pagination,
            'pageactuelle' => $page,
            'user_name' => $user_name,
            'u_academies' => $this->em->getRepository(User::class)->findAcademies(),
            'u_academy' => $user_academy,
            'd_states' => $this->utils->trans(DemandePEEState::class),
            'd_state' => $demande_state,
            'ope_datemin' => $ope_datemin,
            'ope_datemax' => $ope_datemax,
            'ope_company' => $ope_company,
            'o_states' => $this->utils->trans(OperationPEEState::class),
            'o_state' => $ope_state,
        ));
    }

    /**
     * Exporte les utilisateurs selon son type (professeurs ou ingénieur) au format Excel 2007
     *
     * @Route(
     *      "/statistique/user/export/{type}",
     *      name="admin_statistics_user_export",
     *      requirements={"type":"teacher|engineer"}
     * )
     *
     * @param Request $request
     * @param string  $type Type de l'utilisateur
     * @return \Symfony\Component\HttpFoundation\StreamedResponse
     * @throws PhpSpreadsheetException
     * @throws \Doctrine\ORM\NonUniqueResultException
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function userExportAction(Request $request, string $type)
    {
        $users = $this->em->getRepository(User::class)->findFromRequest($request);

        $filename = (new Slugify())->slugify($this->translator->transchoice('form.' . $type . '.label', count($users), [], 'form')).'.xlsx';

        return $this->userExporter->export($users, $filename);
    }

    /**
     * Exporte la liste des demandes PEE.
     * Les éventuels filtres utilisés pour restreindre cette liste doivent être passés dans l'URL (GET).
     * Le fichier comprend une ligne par utilisateur, et le format de sortie est Excel 2007.
     *
     * @Route(
     *      "/statistique/professeur/demandes/export",
     *      name="admin_statistics_pee_demande_export"
     * )
     *
     * @param Request $request
     * @return \Symfony\Component\HttpFoundation\StreamedResponse
     * @throws PhpSpreadsheetException
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function peeDemandeExportAction(Request $request)
    {
        ini_set('max_execution_time', 60000);
        ini_set('memory_limit', '256M');
        ignore_user_abort(1);

        $filename = 'DemandesPEE.xlsx';

        // Récupération des paramètres GET
        $user_name = $request->query->get('user_name');
        $user_academy = $request->query->get('user_academy');
        $demande_state = $request->query->get('demande_state');
        if ($ope_datemin = $request->get('ope_datemin')) {
            $ope_datemin = \DateTime::createFromFormat('d/m/Y', $ope_datemin);
        }
        if ($ope_datemax = $request->get('ope_datemax')) {
            $ope_datemax = \DateTime::createFromFormat('d/m/Y', $ope_datemax);
        }
        $ope_company = $request->query->get('ope_company');
        $ope_academy = $request->query->get('ope_academy');
        $ope_state = $request->query->get('ope_state');

        // Création de la requête.
        $qbDemandes = $this->em->createQueryBuilder();
        $qbDemandes
            ->select('d, u, o')
            ->from('AppBundle:DemandePEE', 'd')
            ->leftJoin('d.cuser', 'u')
            ->leftJoin('d.operationpee', 'o')
            ->orderBy('u.lastname', 'ASC')
            ->addOrderBy('u.firstname', 'ASC')
            ->addOrderBy('u.id', 'ASC');
        if (!empty($user_name)) {
            $qbDemandes
                ->andWhere($qbDemandes->expr()->orX(
                    $qbDemandes->expr()->like('LOWER(u.firstname)', ':u_firstname'),
                    $qbDemandes->expr()->like('LOWER(u.lastname)', ':u_lastname')
                ))
                ->setParameter('u_firstname', "%".mb_strtolower($user_name)."%")
                ->setParameter('u_lastname', "%".mb_strtolower($user_name)."%");
        }
        if (!empty($user_academy)) {
            $qbDemandes
                ->andWhere('u.academy = :user_academy')
                ->setParameter('user_academy', $user_academy);
        }
        if (!empty($demande_state)) {
            $qbDemandes
                ->andWhere('d.state = :d_state')
                ->setParameter('d_state', $demande_state);
        }
        if ($ope_datemin) {
            $qbDemandes
                ->andWhere('o.dateVisite >= :ope_datemin')
                ->setParameter('ope_datemin', $ope_datemin);
        }
        if ($ope_datemax) {
            $qbDemandes
                ->andWhere('o.dateVisite <= :ope_datemax')
                ->setParameter('ope_datemax', $ope_datemax);
        }
        if (!empty($ope_company)) {
            $qbDemandes
                ->andWhere($qbDemandes->expr()->orX(
                   $qbDemandes->expr()->like('LOWER(o.company)', ':ope_company'),
                   $qbDemandes->expr()->like('LOWER(o.site)', ':ope_site')
                ))
                ->setParameter('ope_company', "%".mb_strtolower($ope_company)."%")
                ->setParameter('ope_site', "%".mb_strtolower($ope_company)."%");
        }
        if (!empty($ope_state)) {
            $qbDemandes
                ->andWhere('o.state = :ope_state')
                ->setParameter('ope_state', $ope_state);
        }
        if (!empty($ope_academy)) {
            $qbDemandes
                ->andWhere('o.academy = :ope_academy')
                ->setParameter('ope_academy', $ope_academy);
        }

        $demandesPee = $qbDemandes->getQuery()->getResult();

        return $this->operationPeeExporter->exportApplicationsStatistics($demandesPee, $filename);
    }

    /**
     * Exporte la liste des opérations PEE au format Excel 2007
     *
     * @Route(
     *      "/statistique/operation-pee/export",
     *      name="admin_statistics_pee_operation_export"
     * )
     *
     * @param Request $request
     * @return \Symfony\Component\HttpFoundation\StreamedResponse
     * @throws PhpSpreadsheetException
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function peeOperationExportAction(Request $request)
    {
        ini_set('max_execution_time', 60000);
        ini_set('memory_limit', '256M');
        ignore_user_abort(1);

        $filename = 'OperationsPEE.xlsx';

        // Récupération des paramètres GET
        if (null !== $ope_datemin = $request->get('ope_datemin')) {
            $ope_datemin = \DateTime::createFromFormat('d/m/Y', $ope_datemin);
        }
        if (null !== $ope_datemax = $request->get('ope_datemax')) {
            $ope_datemax = \DateTime::createFromFormat('d/m/Y', $ope_datemax);
        }
        $ope_company = $request->get('ope_company');
        $ope_academy = $request->get('ope_academy');
        $ope_state = $request->get('ope_state');

        // Création de la requête.
        $qb = $this->em->createQueryBuilder();
        $qb
            ->select('o')
            ->from('AppBundle:OperationPEE', 'o')
            ->orderBy('o.created', 'DESC');
        if ($ope_datemin) {
            $qb
                ->andWhere('o.dateVisite >= :ope_datemin')
                ->setParameter('ope_datemin', $ope_datemin);
        }
        if ($ope_datemax) {
            $qb
                ->andWhere('o.dateVisite <= :ope_datemax')
                ->setParameter('ope_datemax', $ope_datemax);
        }
        if ($ope_company) {
            $qb
                ->leftJoin('o.company', 'c')
                ->andWhere($qb->expr()->orX(
                    $qb->expr()->like($qb->expr()->lower('c.name'), ':c_name'),
                    $qb->expr()->like($qb->expr()->lower('o.site'), ':o_site')
                ))
                ->setParameter('c_name', "%".mb_strtolower($ope_company)."%")
                ->setParameter('o_site', "%".mb_strtolower($ope_company)."%");
        }
        if ($ope_state) {
            $qb
                ->andWhere('o.state = :ope_state')
                ->setParameter('ope_state', $ope_state);
        }
        if ($ope_academy) {
            $qb
                ->andWhere('o.academy = :ope_academy')
                ->setParameter('ope_academy', $ope_academy);
        }

        $operations_pee = $qb->getQuery()->getResult();

        return $this->operationPeeExporter->exportOperations($operations_pee, $filename);
    }

    /**
     * Exporte la liste des opérations ITDLC au format Excel 2007
     *
     * @Route(
     *      "/statistique/operation-itdlc/export",
     *      name="admin_statistics_itdlc_operation_export"
     * )
     *
     * @param Request $request
     * @return \Symfony\Component\HttpFoundation\StreamedResponse
     * @throws PhpSpreadsheetException
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function itdlcOperationExportAction(Request $request)
    {
        ini_set('max_execution_time', 60000);
        ini_set('memory_limit', '256M');
        ignore_user_abort(1);

        $filename = "OperationsITDLC.xlsx";

        $qb = $this->em->createQueryBuilder();
        $qb
            ->select('o')
            ->from(OperationITDLC::class, 'o')
            ->join('o.availability', 'a')
            ->where('o.type = :o_type')
            ->orderBy('o.created', 'DESC')
            ->addOrderBy('o.id', 'DESC')
            ->setParameters([
                'o_type' => OperationITDLCType::OPERATION,
            ]);

        if ($datemin = $request->get('datemin')) {
            $qb->andWhere('a.dateVisite >= :datemin')
                ->setParameter('datemin', \DateTime::createFromFormat('d/m/Y', $datemin));
        }

        if ($datemax = $request->get('datemax')) {
            $qb->andWhere('a.dateVisite <= :datemax')
                ->setParameter('datemax', \DateTime::createFromFormat('d/m/Y', $datemax));
        }

        $ope_state = $request->get('state');

        if ($ope_state !== 'all') {
            if (!empty($ope_state)) {
                $qb
                    ->andWhere('o.state IN (:o_state)')
                    ->setParameter('o_state', $ope_state);
            }
            else {
                $qb
                    ->andWhere('o.state = :o_state')
                    ->setParameter('o_state', OperationITDLCState::CLOSED);    
            }
        }

        $operationsItdlc = $qb->getQuery()->getResult();

        return $this->operationItdlcExporter->exportOperations($operationsItdlc, $filename);
    }
}
