<?php

namespace Modules\Consultation\Service;

use Entity\Model\AoEntet;
use Entity\Model\AoRepPijo;
use Helpers\Fichier\Fichier;
use Helpers\Log\LogSysteme;
use Helpers\Outil\ArrayHelper;
use Helpers\Outil\Date;
use Helpers\Outil\Outils;
use Helpers\Outil\Sql;
use Helpers\Trad\ITrad;
use Helpers\Trad\Trad;
use Modules\Consultation\Repository\ExportExcel;
use Modules\Consultation\Repository\GestionConsultation;
use Modules\Consultation\Repository\Reponse;
use PDO;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class ExportAnalyseService
{
    private PDO $pdo;
    private ITrad $trad;

    /**
     * __construct
     *
     * @param  PDO $pdo
     * @param  ITrad $trad
     */
    public function __construct(PDO $pdo, ITrad $trad)
    {
        $this->pdo = $pdo;
        $this->trad = $trad;
    }

    /**
     * exportAnalysis
     *
     * @param  string $idAO
     * @return string
     */
    public function exportAnalysis(string $idAO): string
    {
        $gestionConsu = new GestionConsultation($this->pdo);
        $listeFrs = $gestionConsu->getListeFourniss($idAO);
        $docList = ArrayHelper::indexBy((new AoRepPijo($this->pdo))->findAll(['NO_AO' => $idAO]), 'CODE_FOU');
        foreach ($listeFrs as &$frs) {
            //@phpstan-ignore-next-line
            $frs['docs'] = $docList[$frs['CODE_FOU']] ?? [];
        }
        $ligneConsultation = $gestionConsu->listeLigneConsultation($idAO);
        $calculChoixFrs = (new ConsultationService($this->pdo, $this->trad))->calculeChoixFrs($idAO, $listeFrs);

        $obj = new Spreadsheet();
        $obj->getProperties()->setTitle($this->trad->t('Export résultats'))->setDescription("none");

        $obj->setActiveSheetIndex(0);
        $obj->getActiveSheet()->setTitle($this->trad->t('Réponses fournisseurs'));

        // Récupération des noms des colonnes de l'entête
        $headingColumns = $this->getExportCol('lines');
        $colTitle = '';
        $col = 'A';
        foreach ($headingColumns as $key) {
            $obj->getActiveSheet()->setCellValue($col . '1', $key);
            $colTitle = $col;
            $col++;
        }

        $ligneConsultation = $this->formatValues($ligneConsultation, 'lines');

        // Insertion des données
        // lignes de la consultation
        $line = '3';
        foreach ($ligneConsultation as $data) {
            $col = 'A';
            foreach ($data as $value) {
                $obj->getActiveSheet()->setCellValue($col . $line, trim($value));
                $col++;
            }
            $line++;
        }

        // entêtes fournisseurs
        $colSuppliers = $col;
        foreach ($listeFrs as $value) {
            $cellValue = $value['TYPE_FG'] == 'F'
                ? $this->trad->t('Fournisseur') . ' : ' . $value['RS'] . ' (' . $value['CODE_FOU'] . ')'
                : $this->trad->t('Groupement') . ' : ' . $value['RS_GRP'] . ' (' . $value['CODE_FOU'] . ')';

            $obj->getActiveSheet()->setCellValue($colSuppliers . '1', $cellValue);
            $mergeStart = $colSuppliers;
            $colSuppliers++;
            $colSuppliers++;
            $mergeEnd = $colSuppliers;
            $colSuppliers++;
            $obj->getActiveSheet()->mergeCells($mergeStart . '1' . ':' . $mergeEnd . '1');
        }

        // lignes pour chaque fournisseur
        $colSuppliers = $col;
        $line = '2';
        foreach ($listeFrs as $supplier) {
            $obj->getActiveSheet()->setCellValue($colSuppliers . $line, $this->trad->t('Quantité'));
            $colSuppliers++;
            $obj->getActiveSheet()->setCellValue($colSuppliers . $line, $this->trad->t('Prix'));
            $colSuppliers++;
            $obj->getActiveSheet()->setCellValue($colSuppliers . $line, $this->trad->t('Montant'));
            $colSuppliers++;
        }

        $line++;
        foreach ($ligneConsultation as $data) {
            $colSuppliers = $col;
            foreach ($listeFrs as $supplier) {
                if ($calculChoixFrs['arrayRepFrs'][$supplier['CODE_FOU']][$data['NO_PO']]['COUNT_REPSL']) {
                    $obj->getActiveSheet()->setCellValue($colSuppliers . $line, $this->trad->t('Réponse fournisseur en plusieurs lignes.'));
                } else {
                    if ($calculChoixFrs['arrayRepFrs'][$supplier['CODE_FOU']][$data['NO_PO']]['QTE'] != $data['QTE'] && $calculChoixFrs['arrayRepFrs'][$supplier['CODE_FOU']][$data['NO_PO']]['QTE']) {
                        $obj->getActiveSheet()->setCellValue($colSuppliers . $line, $this->trad->t('Les quantités de la consultation et de la réponse sont différentes.') . ' (' . Outils::qteByUnite($this->pdo, $data['QTE'], $data['UNITE']) . '/' . $calculChoixFrs['arrayRepFrs'][$supplier['CODE_FOU']][$data['NO_PO']]['QTE'] . ')');
                    } else {
                        $obj->getActiveSheet()->setCellValue(
                            $colSuppliers . $line,
                            $calculChoixFrs['arrayRepFrs'][$supplier['CODE_FOU']][$data['NO_PO']]['QTE']
                        );
                    }
                }
                $colSuppliers++;
                if ($calculChoixFrs['arrayRepFrs'][$supplier['CODE_FOU']][$data['NO_PO']]['TOTAL_REPSL']) {
                    $obj->getActiveSheet()->setCellValue(
                        $colSuppliers . $line,
                        Outils::formatMontant($calculChoixFrs['arrayRepFrs'][$supplier['CODE_FOU']][$data['NO_PO']]['TOTAL_REPSL'])
                    );
                } elseif ($calculChoixFrs['arrayRepFrs'][$supplier['CODE_FOU']][$data['NO_PO']]['PU']) {
                    $obj->getActiveSheet()->setCellValue(
                        $colSuppliers . $line,
                        Outils::formatMontant($calculChoixFrs['arrayRepFrs'][$supplier['CODE_FOU']][$data['NO_PO']]['PU'])
                    );
                }
                $colSuppliers++;
                if ($calculChoixFrs['arrayRepFrs'][$supplier['CODE_FOU']][$data['NO_PO']]['TOTAL_REPSL']) {
                    $obj->getActiveSheet()->setCellValue(
                        $colSuppliers . $line,
                        Outils::formatMontant(
                            \intval($calculChoixFrs['arrayRepFrs'][$supplier['CODE_FOU']][$data['NO_PO']]['TOTAL_REPSL'])
                                * \intval($calculChoixFrs['arrayRepFrs'][$supplier['CODE_FOU']][$data['NO_PO']]['QTE'])
                        )
                    );
                } elseif ($calculChoixFrs['arrayRepFrs'][$supplier['CODE_FOU']][$data['NO_PO']]['PU']) {
                    $obj->getActiveSheet()->setCellValue(
                        $colSuppliers . $line,
                        Outils::formatMontant(
                            intval($calculChoixFrs['arrayRepFrs'][$supplier['CODE_FOU']][$data['NO_PO']]['PU'])
                                * \intval($calculChoixFrs['arrayRepFrs'][$supplier['CODE_FOU']][$data['NO_PO']]['QTE'])
                        )
                    );
                }
                $colSuppliers++;
            }
            $line++;
        }

        // note au questionnaire
        $obj->getActiveSheet()->setCellValue($colTitle . $line, $this->trad->t('Note au questionnaire'));
        $colSuppliers = $col;
        foreach ($listeFrs as $supplier) {
            $obj->getActiveSheet()->setCellValue($colSuppliers . $line, Outils::formatPrixUnitaire($supplier['NOTE']));
            $mergeStart = $colSuppliers;
            $colSuppliers++;
            $colSuppliers++;
            $mergeEnd = $colSuppliers;
            $colSuppliers++;
            $obj->getActiveSheet()->mergeCells($mergeStart . $line . ':' . $mergeEnd . $line);
        }
        $line++;
        
        //Total des articles (dans la devise)
        $obj->getActiveSheet()->setCellValue($colTitle . $line, Trad::strBuild(
            $this->trad->t('Total des articles (en @1@)'),
            (new AoEntet($this->pdo))->find(['NO_AO' => $idAO], ['DEVISE'])['DEVISE']
        ));
        $colSuppliers = $col;
        foreach ($listeFrs as $supplier) {
            $cellValue = '';
            if ($supplier['docAbsent']) {
                $cellValue = $this->trad->t('Des documents attendus obligatoires sont absents.');
            }
            if ($supplier['repDepasse']) {
                $cellValue = $this->trad->t('La date limite de l\'offre est dépassée.');
            }
            $cellValue .= $calculChoixFrs['totalFrs'][$supplier['CODE_FOU']];
            $obj->getActiveSheet()->setCellValue($colSuppliers . $line, $cellValue);
            $mergeStart = $colSuppliers;
            $colSuppliers++;
            $colSuppliers++;
            $mergeEnd = $colSuppliers;
            $colSuppliers++;
            $obj->getActiveSheet()->mergeCells($mergeStart . $line . ':' . $mergeEnd . $line);
        }
        $line++;

        // documents de la réponse
        $obj->getActiveSheet()->setCellValue($colTitle . $line, $this->trad->t('Documents de la réponse'));
        $colSuppliers = $col;
        foreach ($listeFrs as $supplier) {
            $i = 0;
            foreach ($supplier['docs'] as $pj) {
                $cellValue = '';
                if (++$i === count($supplier['docs'])) {
                    $cellValue .= $pj['TITRE'];
                } else {
                    $cellValue .= $pj['TITRE'] . ' / ';
                }
            }
            $obj->getActiveSheet()->setCellValue($colSuppliers . $line, $cellValue);

            $mergeStart = $colSuppliers;
            $colSuppliers++;
            $colSuppliers++;
            $mergeEnd = $colSuppliers;
            $colSuppliers++;
            $obj->getActiveSheet()->mergeCells($mergeStart . $line . ':' . $mergeEnd . $line);
        }
        $line++;
        
        foreach ($obj->getWorksheetIterator() as $worksheet) {
            $obj->setActiveSheetIndex($obj->getIndex($worksheet));
            $sheet = $obj->getActiveSheet();
            $cellIterator = $sheet->getRowIterator()->current()->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(true);
            foreach ($cellIterator as $cell) {
                $sheet->getColumnDimension($cell->getColumn())->setAutoSize(true);
            }
        }

        $timerCreationFichier = "_" . date('Ymd') . "_" . date('His');

        $nomFichier = $_SESSION['NomFichier'] = LogSysteme::getServerName() . '_' . $idAO . '_result_export' . $timerCreationFichier . '.xlsx';

        // @phpstan-ignore-next-line
        $urlExcel = Fichier::URLDownload($nomFichier);

        $objWriter = IOFactory::createWriter($obj, 'Xlsx');
        $objWriter->save($urlExcel);

        return $nomFichier;
    }

    /**
     * getExportCol
     *
     * @param  string $sheet
     * @return array<mixed>
     */
    public function getExportCol(string $sheet): array
    {
        switch ($sheet) {
            case 'lines':
                $columns = [
                    'NO_PO' => $this->trad->t('N° de poste'),
                    'CODE_ART' => $this->trad->t('Réf. article'),
                    'DES' => $this->trad->t('Désignation'),
                    'DES_COMP' => $this->trad->t('Désign. compl.'),
                    'QTE' => $this->trad->t('Quantité'),
                    'UNITE' => $this->trad->t('Unité'),
                    'PRIX_REF' => $this->trad->t('Prix réf'),
                    'BUDGET' => $this->trad->t('Budget'),
                ];
                break;

            case 'entete':
                $columns = [
                    'LIBELLE' => $this->trad->t('Libellé'),
                    'LIB_GRILLE' => $this->trad->t('Catégorie'),
                    'DESCRIPT' => $this->trad->t('Description'),
                    'DTECREAT' => $this->trad->t('Date de validation'),
                    'DTEFIN' => $this->trad->t('Date de clôture'),
                    'PERIODE' => $this->trad->t('Période'),
                    'CODE_STE' => $this->trad->t('Sociétés'),
                    'NOM' => $this->trad->t('Créateur'),
                ];
                break;

            case 'questionnaire':
                $columns = [
                    'ARBO' => $this->trad->t('Arborescence des thèmes'),
                    'THEME' => $this->trad->t('Thèmes et sous-thèmes'),
                    'TYPE' => $this->trad->t('Type de question'),
                    'LIBELLE' => $this->trad->t('Libellé question'),
                    'DESCRIPT' => $this->trad->t('Commentaire'),
                    'POIDS' => $this->trad->t('Poids'),
                    'QUEST_FOUR' => $this->trad->t('Pour fournisseur'),
                    'DEP' => $this->trad->t('Question dépendante'),
                    'PARENT' => $this->trad->t('Question parent'),
                    'TBL_REP' => $this->trad->t('Tableau de réponses'),
                    'TYP_NBR' => $this->trad->t('Type de nombre'),
                    'NOTE_VRAI' => $this->trad->t('Note pour oui'),
                    'NOTE_FAUX' => $this->trad->t('Note pour non'),
                ];
                break;

            case 'reponse':
                $columns = [
                    'ARBO' => $this->trad->t('Fournisseur'),
                    'NOTEUR' => $this->trad->t('Evaluateur'),
                    'THEME' => $this->trad->t('Thème'),
                    'LIBELLE' => $this->trad->t('Libellé question'),
                    'PARENT' => $this->trad->t('Question parent'),
                    'NOTE' => $this->trad->t('Note'),
                    'REPONSE' => $this->trad->t('Réponse'),
                    'COMMENTAIRE' => $this->trad->t('Commentaire'),
                    'TYPE' => $this->trad->t('Type de question'),
                    'POIDS' => $this->trad->t('Poids'),
                    'ATTRIBUE_PAR' => $this->trad->t('Note attribuée par'),
                ];
                break;

            case 'commentaire':
                $columns = [
                    'FOURNISS' => $this->trad->t('Fournisseur'),
                    'NOTE' => $this->trad->t('Note'),
                    'COMMENT' => $this->trad->t('Commentaire'),
                    'PLAN' => $this->trad->t('Appréciation globale'),
                ];
                break;

            default:
                $columns = [];
                break;
        }
        return $columns;
    }

    /**
     * formatValues
     *
     * @param  array<mixed> $data
     * @param  string $sheet
     * @return array<mixed>
     */
    public function formatValues(array $data, string $sheet): array
    {
        $questionTypes = [
            'O' => $this->trad->t('Texte'),
            'N' => $this->trad->t('Matrice numérique'),
            'L' => $this->trad->t('Réponse oui/non'),
            'T' => $this->trad->t('Matrice texte'),
            'C' => $this->trad->t('Issue de la base de données')
        ];

        $statusTypes = [
            'T' => $this->trad->t('Terminée'),
            'P' => $this->trad->t('En préparation'),
            'E' => $this->trad->t('en cours'),
        ];

        $listeNoteur = [
            'N' => $this->trad->t('Evaluateur'),
            'D' => $this->trad->t('Demandeur'),
            'C' => $this->trad->t('Dépouilleur'),
        ];

        switch ($sheet) {
            case 'lines':
                foreach ($data as &$value) {
                    $value = [
                        'NO_PO' => $value['NO_PO'],
                        'CODE_ART' => $value['CODE_ART'],
                        'DES' => $value['DES'],
                        'DES_COMP' => $value['DES_COMP'],
                        'QTE' => Outils::qteByUnite($this->pdo, $value['QTE'], $value['UNITE']),
                        'UNITE' => $value['UNITE'],
                        'PRIX_REF' => Outils::formatMontant($value['PRIX_REF']),
                        'BUDGET' => Outils::formatMontant($value['BUDGET']),
                    ];
                }
                $return = $data;
                break;

            case 'gestion':
                foreach ($data as &$value) {
                    $value = [
                        'ID_CAMP' => $value['ID_CAMP'],
                        'LIBELLE' => $value['LIBELLE'],
                        'DESCRIPT' => $value['DESCRIPT'],
                        'STATUT' => $statusTypes[$value['STATUT']],
                        'TOUTESTE' => (Sql::toBool($value['TOUTESTE']))
                            ? $this->trad->t('Oui')
                            : $this->trad->t('Non'),
                        'DTECREAT' => Date::dateConverte($value['DTECREAT']),
                        'DTEFIN' => Date::dateConverte($value['DTEFIN']),
                        'DTEFIN_PREVUE' => Date::dateConverte($value['DTEFIN_PREVUE']),
                        'LIBELLEPER' => $value['LIBELLEPER'],
                        'CREATOR' => $value['CREATOR'] . ' (' . $value['CREEEPAR'] . ')',
                    ];
                }
                $return = $data;
                break;

            case 'reponse':
                foreach ($data as &$value) {
                    unset($value['CODE_FOU']);
                    if ($value['TYPE_REP'] == 'L') {
                        $value['reponse_evaluateur'] = (Sql::toBool($value['reponse_evaluateur']))
                            ? $this->trad->t('Oui')
                            : $this->trad->t('Non');
                    }

                    if ($value['TYPE_REP'] == 'O') {
                        $value['note_attribuee_par'] = $listeNoteur[$value['note_attribuee_par']];
                    }

                    $value['TYPE_REP'] = $questionTypes[$value['TYPE_REP']];
                }
                $return = $data;
                break;

            default:
                $return = [];
                break;
        }
        return $return;
    }
}
