<?php

namespace AppBundle\Export;

use AppBundle\Entity\Availability;
use AppBundle\Entity\OperationITDLC;
use AppBundle\Enum\OperationITDLCState;
use AppBundle\Enum\OperationITDLCType;
use AppBundle\Enum\UserHonorific;
use AppBundle\Enum\UserJobEngineer;
use AppBundle\Enum\UserJobTeacher;
use AppBundle\Enum\UserSchoolGrade;
use AppBundle\Enum\UserType;
use AppBundle\Enum\UserRep;
use Symfony\Component\HttpFoundation\StreamedResponse;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Symfony\Component\HttpFoundation\ResponseHeaderBag;

/**
 *
 * @author Bastien Gatellier <contact@bgatellier.fr>
 */
class OperationItdlcExporter extends AbstractExcelExporter
{
    /**
     *
     * @param  OperationITDLC[] $operations
     * @param  string $filename
     * @return StreamedResponse
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function exportOperations(array $operations, string $filename): StreamedResponse
    {
        $rows = []; // Rows that will be written in the file

        $headers = array(
            'Date rencontre',
            'Heure de début',
            'Heure de fin',
            $this->transchoice('form.engineer.label', 1, [], 'form'),
            "Ingénieur.e - Technicien.ne : Email",
            $this->transchoice('form.engineer.label', 1, [], 'form') . ' : Métier',
            $this->transchoice('form.engineer.label', 1, [], 'form') . ' : Formation',
            $this->transchoice('form.engineer.label', 1, [], 'form') . ' : A propos',
           'Entreprise',
           'Groupement',
            $this->transchoice('form.teacher.label', 1, [], 'form'),
            "Email enseignant",
            $this->trans('form.rep.label', [], 'form'),
            'Classe',
            'Option',
            'Matière',
            'Objectif',
            $this->trans('form.notes.label'),
            $this->trans('form.itdlc.nbStudent.label'),
            'Date envoi mail',
            'Établissement',
            $this->trans('form.address.label'),
            'CP',
            $this->trans('form.city.label'),
            $this->trans('form.academy.label'),
            $this->trans('form.date.creation.label'),
            $this->trans('form.state.label'),
        );

        foreach ($operations as $operation) {
            // TODO: include the related entities into the query that leads to the $operations array, in order to avoid additional requests
            /** @var Availability $availability */
            $availability = $operation->getAvailability()->first();

            $teacher = $operation->getProf();
            $engineer = $operation->getInge();

            $teacherName = null;
            $teacherJob = null;
            if ($teacher) {
                $teacherName = $teacher->getHonorific() ?
                    $teacher->getHonorific() . ' '
                    : null;
                $teacherName .= $teacher->getFullName();

                $teacherEmail = $teacher->getEmail();
                
                $teacherJob = ($teacher->getJob() == "" || $teacher->getJobOther()) ? $teacher->getJobOther() : array_shift($this->constTranslator->trans(UserJobTeacher::class, $teacher->getJob()));

                $teacherRepLevel = $this->constTranslator->trans(UserRep::class, $teacher->getRep());

            }

            $engineerName = null;
            $engineerJob = null;
            $engineerSchoolGrade = null;
            $engineerEstablishment = null;
            $partnerGroup = null;
            if ($engineer) {
                $engineerName = $engineer->getHonorific() ?
                    $engineer->getHonorific() . ' '
                    : null;

                $engineerName .= $engineer->getFullName();

                $engineerEmail = $engineer->getEmail();

                $engineerSchoolGrade = $this->constTranslator->trans(UserSchoolGrade::class, $engineer->getFormation());

                $engineerJob = $this->constTranslator->trans(UserJobEngineer::class, $engineer->getJob());

                $engineerJob = !empty($engineer->getJobOther()) ? $engineer->getJobOther() : $engineerJob;

                $partnerGroup = $engineer->getEstablishment() ? $engineer->getEstablishment()->getPartnerGroup() : '';
            }

            $state = $this->constTranslator->trans(OperationITDLCState::class, $operation->getState());

            $row = array(
                $availability->getDateVisite() instanceof \DateTime ? $availability->getDateVisite()->format('d/m/Y') : '',
                $availability->getStartTime() instanceof \DateTime ? $availability->getStartTime()->format('H:i') : '',
                $availability->getEndTime() instanceof \DateTime ? $availability->getEndTime()->format('H:i') : '',
                $engineerName,
                $engineerEmail,
                $engineerJob,
                $engineer ? array_shift($engineerSchoolGrade) . ' ' . $engineer->getFormationType() : '',
                $engineer ? $engineer->getAboutMe() : '',
                $engineer ? $engineer->getEstablishment() : '',
                $partnerGroup ,
                $teacherName,
                $teacherEmail,
                array_shift($teacherRepLevel),
                $operation->getClassLevel(),
                $operation->getClassOption(),
                $teacherJob,
                $operation->getGoal(),
                $operation->getNote(),
                $operation->getNbStudent(),
                $operation->getDatemail() instanceof \DateTime ? $operation->getDatemail()->format('d/m/Y') : '',
                $teacher->getEstablishmentName(),
                $teacher->getEstablishmentAddress(),
                $teacher->getEstablishmentZipcode(),
                $teacher->getEstablishmentCity(),
                $teacher->getAcademy(),
                $operation->getCreated() instanceof \DateTime ? $operation->getCreated()->format('d/m/Y') : '',
                array_shift($state)
            );

            $rows[] = $row;
        }

        $this->createSpreadsheet($headers, $rows, $filename);

        $this->saveTo('uploads/export/');

        return $this->createStreamedResponse();
    }

    /**
     * @param array  $applications
     * @param string $filename
     * @return StreamedResponse
     * @throws \Doctrine\ORM\NoResultException
     * @throws \Doctrine\ORM\NonUniqueResultException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function exportApplications(array $applications, string $filename): StreamedResponse
    {
        if ($applications[0] instanceof OperationITDLC) {
            [$headers, $rows] = $this->exportTeacherApplications($applications);
        } else {
            [$headers, $rows] = $this->exportEngineerApplications($applications);
        }

        $this->createSpreadsheet($headers, $rows, $filename);

        $this->saveTo('uploads/export/');

        return $this->createStreamedResponse();
    }

    /**
     * @param OperationITDLC[] $applications
     * @return array
     * @throws \Doctrine\ORM\NoResultException
     * @throws \Doctrine\ORM\NonUniqueResultException
     */
    private function exportTeacherApplications(array $applications)
    {
        ini_set("max_execution_time", 60000);
        ini_set("memory_limit", "256M");
        ignore_user_abort(1);

        // Nombre  maximum de disponibilités pour l'ensemble des demandes ITDLC (les disponibilités ne concernent que les professeurs)
        // Cette requête a pour objectif de définir combien de colonnes "Disponibilité" doit contenir le fichier exporté
        $demandesDisponibilitesMax = $this->em
            ->createQuery(
                "SELECT COUNT(av.id) nombreMax
                    FROM AppBundle:Availability av
                    LEFT JOIN av.operationitdlc ope
                    WHERE ope.type = :ope_type
                    GROUP BY ope.id
                    ORDER BY nombreMax DESC"
            )
            ->setParameter('ope_type', OperationITDLCType::DEMANDE)
            ->setMaxResults(1)
            ->getSingleResult();
        $demandesDisponibilitesMax = $demandesDisponibilitesMax['nombreMax'];

        // Nombre maximum de préférences ingénieurs indiquées par les professeurs qui ont effectué une demande ITDLC.
        // Cette requête a pour objectif de définir combien de colonnes "Préférence" doit contenir le fichier exporté
        $demandesPreferencesMax = $this->em
            ->createQuery(
                "SELECT COUNT(pref.id) nombreMax
                    FROM AppBundle:OperationITDLC ope
                    LEFT JOIN ope.prof user
                    LEFT JOIN user.preferencemain pref
                    WHERE ope.type = :ope_type
                    GROUP BY ope.id
                    ORDER BY nombreMax DESC"
            )
            ->setParameter('ope_type', OperationITDLCType::DEMANDE)
            ->setMaxResults(1)
            ->getSingleResult();
        $demandesPreferencesMax = $demandesPreferencesMax['nombreMax'];

        $headers = array(
            'Demande: date de dépôt',
            'Demande: Statut',
            'Demande: objectifs',
            'Demande: nb élèves',
            'Demande: note',
            'Demandeur: ' . lcfirst($this->trans('form.honorific.label')),
            'Demandeur: nom',
            'Demandeur: prénom',
            'Demandeur: statut',
            'Demandeur: matière enseignée',
            'Demandeur: niveaux enseignés',
            'Demandeur: email',
            'Demandeur: tél. fixe',
            'Demandeur: tél. mobile',
            'Établissement: nom',
            'Établissement: adresse',
            'Établissement: code postal',
            'Établissement: ville',
            'Établissement: académie',
        );
        for ($d = 1 ; $d <= $demandesDisponibilitesMax ; $d++) { // Disponibilités des professeurs
            $headers[] = 'Disponibilité '.$d.': date OU jour de la semaine';
            $headers[] = 'Disponibilité '.$d.': heure début';
            $headers[] = 'Disponibilité '.$d.': heure fin';
        }
        for ($p = 1 ; $p <= $demandesPreferencesMax ; $p++) { // Préférences
            $headers[] = 'Préf. ingénieur '.$p.': nom';
            $headers[] = 'Préf. ingénieur '.$p.': prénom';
        }

        $rows = [];

        // Création des données
        foreach($applications as $demande) {
            // Préférences
            $preferences = $this->em
                ->createQuery(
                    "SELECT u FROM AppBundle:Preference u
                            WHERE u.mainUser = :user
                            ORDER BY u.id ASC"
                )
                ->setParameter('user', $demande->getProf()->getId())
                ->getResult();

            // Disponibilités
            $disponibilites = $this->em
                ->createQuery(
                    "SELECT u FROM AppBundle:Availability u
                            WHERE u.operationitdlc = :ope
                            ORDER BY u.id ASC"
                )
                ->setParameter('ope', $demande)
                ->getResult();

            $userHonorifics = $this->constTranslator->trans(UserHonorific::class, $demande->getProf()->getHonorific());
            $userTypes = $this->constTranslator->trans(UserType::class, $demande->getProf()->getType());

            $userJob = $demande->getProf()->getJobOther() ?? $this->constTranslator->trans(UserJobTeacher::class, $demande->getProf()->getJob()); 

            if (is_array($userJob)) {
                $userJob = array_shift($userJob);
            }
            
            $demandeState = $this->constTranslator->trans(OperationITDLCState::class, $demande->getState());

            $row = array(
                $demande->getCreated()->format('d/m/Y'), // Demande: date de dépôt
                array_shift($demandeState), // Demande: Statut
                $demande->getGoal(), // Demande: objectifs
                $demande->getNbStudent(), // Demande: nb élèves
                $demande->getNote(), // Demande: note
                array_shift($userHonorifics), // Demandeur: civilité
                $demande->getProf()->getLastname(), // Demandeur: nom
                $demande->getProf()->getFirstname(), // Demandeur: prénom
                array_shift($userTypes), // Demandeur: type
                $userJob, // Demandeur: matière enseignée
                implode(',',$demande->getProf()->getClassroom()), // Demandeur: niveaux
                $demande->getProf()->getEmail(), // Demandeur: email
                $demande->getProf()->getPhone(), // Demandeur: tél. fixe
                $demande->getProf()->getMobile(), // Demandeur: tél. mobile
                $demande->getProf()->getEstablishmentName(), // Établissement: nom
                $demande->getProf()->getEstablishmentAddress(), // Établissement: adresse
                $demande->getProf()->getEstablishmentZipcode(), // Établissement: code postal
                $demande->getProf()->getEstablishmentCity(), // Établissement: ville
                $demande->getProf()->getAcademy(), // Établissement: académie
            );
            for ($d = 0 ; $d < $demandesDisponibilitesMax ; $d++) {
                if (isset($disponibilites[$d])) {
                    $row[] = $disponibilites[$d]->getDayoftheweek() ?? $disponibilites[$d]->getDateVisite()->format('d/m/Y');

                    $row[] = $disponibilites[$d]->getStartTime()->format('H:i'); // Disponibilité N: heure début
                    $row[] = $disponibilites[$d]->getEndTime()->format('H:i');  // Disponibilité N: heure fin
                } else {
                    $row[] = null;
                    $row[] = null;
                    $row[] = null;
                }
            }
            for ($p = 0 ; $p < $demandesPreferencesMax ; $p++) {
                if (isset($preferences[$p])) {
                    $row[] = $preferences[$p]->getSelectedUser()->getLastname(); // Préf. ingénieur N: nom
                    $row[] = $preferences[$p]->getSelectedUser()->getFirstname(); // Préf. ingénieur N: prénom
                } else {
                    $row[] = null;
                    $row[] = null;
                }
            }

            $rows[] = $row;
        }

        return [$headers, $rows];
    }

    /**
     * @param User[] $applications
     * @return array
     * @throws \Doctrine\ORM\NoResultException
     * @throws \Doctrine\ORM\NonUniqueResultException
     */
    private function exportEngineerApplications(array $applications)
    {
        ini_set("max_execution_time", 60000);
        ini_set("memory_limit", "256M");
        ignore_user_abort(1);

        // Nombre maximum de préférences professeurs indiquées par les ingénieurs qui ont effectué une demande ITDLC.
        // Cette requête a pour objectif de définir combien de colonnes "Préférence" doit contenir le fichier exporté
        $demandesPreferencesMax = $this->em
            ->createQuery(
                "SELECT COUNT(pref.id) nombreMax
                    FROM AppBundle:User user
                    LEFT JOIN user.preferencemain pref
                    WHERE
                    user.nbContribution!=0
                    AND user.nbContribution is not null
                    GROUP BY user.id
                    ORDER BY nombreMax DESC"
            )
            ->setMaxResults(1)
            ->getSingleResult();
        $demandesPreferencesMax = $demandesPreferencesMax['nombreMax'];

        // Création des en-têtes
        $headers = array(
            'Demande: date de dépôt',
            'Demande: objectifs',
            'Demandeur: ' . lcfirst($this->trans('form.honorific.label')),
            'Demandeur: nom',
            'Demandeur: prénom',
            'Demandeur: statut',
            'Demandeur: email',
            'Demandeur: tél. fixe',
            'Demandeur: tél. mobile',
            'Demandeur: temps de dépl. souhaité',
            'Demandeur: distance dépl. souhaitée',
            'Demandeur: dépl. en transport en commun',
            'Demandeur: nb contributions acceptées',
            'Demandeur: niveaux enseignés',
            'Demandeur: métiers',
            'Demandeur: formation',
            'Demandeur: a propos',
            'Entreprise: nom',
            'Entreprise: adresse',
            'Entreprise: code postal',
            'Entreprise: ville',
        );
        for ($p = 1 ; $p <= $demandesPreferencesMax ; $p++) { // Préférences
            $headers[] = 'Préf. professeur '.$p.': nom';
            $headers[] = 'Préf. professeur '.$p.': prénom';
        }

        $rows = [];

        // Création des données
        foreach ($applications as $user) {
            // Préférences
            $preferences = $this->em
                ->createQuery(
                    "SELECT u
                        FROM AppBundle:Preference u
                        WHERE u.mainUser = :user
                        ORDER BY u.id ASC"
                )
                ->setParameter('user', $user->getId())
                ->getResult();

            $userHonorifics = $this->constTranslator->trans(UserHonorific::class, $user->getHonorific());
            $userTypes = $this->constTranslator->trans(UserType::class, $user->getType());
            $userSchoolGrade = $this->constTranslator->trans(UserSchoolGrade::class, $user->getType());
            $userEstablishment = $user->getEstablishment();

            $userJob = $this->constTranslator->trans(UserJobEngineer::class, $user->getJob()); 

            $row = array(
                $user->getCreated()->format('d/m/Y'), // Demande: date de dépôt
                $user->getBriefing(), // Demande: objectifs
                array_shift($userHonorifics), // Demandeur: civilité
                $user->getLastname(), // Demandeur: nom
                $user->getFirstname(), // Demandeur: prénom
                array_shift($userTypes), // Demandeur: type
                $user->getEmail(), // Demandeur: email
                $user->getPhone(), // Demandeur: tél. fixe
                $user->getMobile(), // Demandeur: tél. mobile
                $user->getTransportTime() . ' min', // Demandeur: temps de dépl. souhaité
                $user->getTransportKm() . ' km', // Demandeur: distance dépl. souhaitée
                $user->getPublicTransport(), // Demandeur: dépl. en transport en commun
                $user->getNbContribution(), // Demandeur: nb contributions acceptées
                implode(',', $user->getClassroom()), // Demandeur: niveaux enseignés
                array_shift($userJob) . ' ' . $user->getJobOther(), // Demandeur: Métiers
                array_shift($userSchoolGrade) . ' ' . $user->getFormationType(), // Demandeur: Formation
                $user->getAboutMe(), // Demandeur: A propos
                $userEstablishment ? $userEstablishment->getName() : '', // Entreprise: nom
                $user->getEstablishmentAddress(), // Entreprise: adresse
                $user->getEstablishmentZipcode(), // Entreprise: code postal
                $user->getEstablishmentCity(), // Entreprise: ville
            );
            for ($p = 0 ; $p < $demandesPreferencesMax ; $p++) {
                if (isset($preferences[$p])) {
                    $row[] = $preferences[$p]->getSelectedUser()->getLastname(); // Préf. professeur N: nom
                    $row[] = $preferences[$p]->getSelectedUser()->getFirstname(); // Préf. professeur N: prénom
                } else {
                    $row[] = null;
                    $row[] = null;
                }
            }

            $rows[] = $row;
        }

        return [$headers, $rows];
    }

    /**
    * @param MerITDLC[] $mers
    * @param string $filename
    * @return StreamedResponse
    * @throws \PhpOffice\PhpSpreadsheet\Exception
    * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
    */
    public function exportMERS(array $mers, string $filename): StreamedResponse
    {
        ini_set("max_execution_time", 60000);
        ini_set("memory_limit", "256M");
        ignore_user_abort(1);

        $spreadsheet = new Spreadsheet();

        $spreadsheet->setActiveSheetIndex(0);
        $activeSheet = $spreadsheet->getActiveSheet();

        // Header
        $activeSheet->setCellValue("A1", 'Alerte')->getStyle('A1')->getFont()->setBold(true);
        $activeSheet->setCellValue("B1", 'Académie')->getStyle('B1')->getFont()->setBold(true);
        $activeSheet->setCellValue("C1", 'Enseignant')->getStyle('C1')->getFont()->setBold(true);
        $activeSheet->setCellValue("D1", 'Enseignant Email')->getStyle('D1')->getFont()->setBold(true);
        $activeSheet->setCellValue("E1", 'Ingénieur/Technicien')->getStyle('E1')->getFont()->setBold(true);
        $activeSheet->setCellValue("F1", 'Ingénieur/Technicien Email')->getStyle('F1')->getFont()->setBold(true);
        $activeSheet->setCellValue("G1", 'Nom de l\'entreprise')->getStyle('G1')->getFont()->setBold(true);
        $activeSheet->setCellValue("H1", 'Date de la mise en relation')->getStyle('H1')->getFont()->setBold(true);

        // MERs
        $currentRow = 2;
        $today = new \DateTime();

        foreach ($mers as $mer) {
            if (!is_null($mer->getAnswer())) {
                if ($mer->getAnswer() == 'autre') {
                    $activeSheet->setCellValue("A". $currentRow, 'Autres dates');
                    $activeSheet->getStyle('A'.$currentRow)->getFill()
                        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                        ->getStartColor()->setARGB('FF0000FF');

                    $activeSheet->getStyle('A' . $currentRow)->getFont()
                        ->getColor()->setRGB('FFFFFF');

                } elseif ($mer->getAnswer() == 'non') {
                    $activeSheet->setCellValue("A". $currentRow, 'Refusé');
                    $activeSheet->getStyle('A' . $currentRow)->getFill()
                        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                        ->getStartColor()->setARGB('FFFFFF33');
            
                } elseif ($mer->getAnswer() == 'date1' || $mer->getAnswer() == 'date2' || $mer->getAnswer() == 'date3') {
                    $activeSheet->setCellValue("A". $currentRow, $mer->getCreatedAt());
                    $activeSheet->getStyle('A' . $currentRow)->getFill()
                        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                        ->getStartColor()->setARGB('FF00FF00');
                }
            } elseif ($mer->getCreatedAt()->diff($today)->days > 5 and $mer->getCreatedAt()->diff($today)->days <= 10) {
                $activeSheet->setCellValue("A". $currentRow, 'Pas de réponse après 5 jours');
                $activeSheet->getStyle("A".$currentRow)->getFill()
                    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                    ->getStartColor()->setARGB('FFFF0000');

                $activeSheet->getStyle('A' . $currentRow)->getFont()
                    ->getColor()->setRGB('FFFFFF');
               
            } elseif ($mer->getCreatedAt()->diff($today)->days > 10) {
                $activeSheet->setCellValue("A". $currentRow, 'Pas de réponse après 10 jours');
                $activeSheet->getStyle("A".$currentRow)->getFill()
                    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                    ->getStartColor()->setARGB('FF000000');

                $activeSheet->getStyle('A' . $currentRow)->getFont()
                    ->getColor()->setRGB('FFFFFF');

            } else {
                $activeSheet->setCellValue("A". $currentRow, date('Y-m-d', strtotime('-5days')));
            }

            $activeSheet->setCellValue("B". $currentRow, $mer->getOpe()->getProf()->getAcademy());
            $activeSheet->setCellValue("C". $currentRow, $mer->getOpe()->getProf()->getFullName());
            $activeSheet->setCellValue("D". $currentRow, $mer->getOpe()->getProf()->getEmail());
            $activeSheet->setCellValue("E". $currentRow, $mer->getInge()->getFullName());
            $activeSheet->setCellValue("F". $currentRow, $mer->getInge()->getEmail());
            $activeSheet->setCellValue("G". $currentRow, $mer->getInge()->getEstablishment());
            $activeSheet->setCellValue("H". $currentRow, $mer->getCreatedAt());
            $currentRow++;
        }

        $this->filename = $filename;
        $this->writer = new Xlsx($spreadsheet);
        $this->writer->save('uploads/export/' . $this->filename);
        $writer = $this->writer;

        $response = new StreamedResponse(function() use ($writer) {
            $writer->save('php://output');
        });

        $dispositionHeader = $response->headers->makeDisposition(
            ResponseHeaderBag::DISPOSITION_ATTACHMENT,
            $this->filename
        );

        $response->headers->add([
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8',
            'Pragma' => 'public',
            'Cache-Control' => 'maxage=1',
            'Content-Disposition' => $dispositionHeader,
        ]);


        return $response;
    }
}
