<?php
namespace Modules\Consultation\Controller;

use Helpers\Log\LogSysteme;
use Helpers\Outil\Date;
use Helpers\Outil\Outils;
use Helpers\Outil\Sql;
use Helpers\Parametres\Constante;
use Helpers\Phpspreadsheet\Block;
use Helpers\Phpspreadsheet\SpreadsheetHelper;
use Modules\Consultation\Repository\ExportComparatifRepo;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\NamedRange;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use Skeleton\Controller\Controller;
use Helpers\Fichier\Fichier;

/**
 * This controller use an excel file as template
 * An example for this template can be found here :
 * src\Modules\Consultation\Views\ExportComparatif\AOTemplateExport.example.xlsx
 * This template is expected to be found at "{directory "etc"}\AOTemplateExport.xlsx"
 *
 * Excel Named Range and placeholders values are used for all the dynamic rendering.
 */
class ExportComparatifController extends Controller
{
    public function popupExportComparatifAction() :string
    {
        $aoId = $this->request->getAttribute('ao');
        return $this->renderer->render('@Consultation/ExportComparatif/popupExport', [
            'aoId' => $aoId,
        ]);
    }
    public function exportComparatifAction() :string
    {
        $aoId = $this->request->getAttribute('ao');
        $type = $this->request->getParsedBody()['format'] ?? '';


        switch ($type) {
            case 'pdf':
                $consultationWSDL = new \Modules\Consultation\WebService\Consultation();
                $xmlResult = $consultationWSDL->impressionComparatif($aoId);

                $reponse = new \SimpleXMLElement($xmlResult->responseData);
                $filePath = $reponse->ListeDoc->Doc->Fichier;
                $filePath = Fichier::URLDownload($filePath);
                break;
            case 'xlsx':
            default:
                $filePath = $this->exportComparatifExcel($aoId);
        }

        return Fichier::UploadClient($filePath, basename($filePath));
    }

//_____________________________________________________________________________
// Sub-routine

    private function getFillFromNamedRange(NamedRange $range) :Fill
    {
        return $range->getWorksheet()
            ->getCell($range->getCellsInRange()[0])
            ->getStyle()
            ->getFill();
    }

    protected function exportComparatifExcel(string $aoId) :string
    {
        $spreadsheet = IOFactory::load(
            Fichier::getDirEtc() . 'AOTemplateExport.xlsx'
        )->copy();

        $fillFourHeaderOdd = $this->getFillFromNamedRange(
            $spreadsheet->getNamedRange('COLOR_FOUR_HEADER_ODD')
        )->exportArray();
        $fillFourHeaderEven = $this->getFillFromNamedRange(
            $spreadsheet->getNamedRange('COLOR_FOUR_HEADER_EVEN')
        )->exportArray();

        $fillFourOdd = $this->getFillFromNamedRange(
            $spreadsheet->getNamedRange('COLOR_FOUR_ODD')
        )->exportArray();
        
        $fillFourEven = $this->getFillFromNamedRange(
            $spreadsheet->getNamedRange('COLOR_FOUR_EVEN')
        )->exportArray();

        $fillGood = $this->getFillFromNamedRange(
            $spreadsheet->getNamedRange('COLOR_GOOD')
        )->exportArray();
        

        // To prevent a bug in PhpOffice\PhpSpreadsheet\ReferenceHelper::updateCellReference we must remove the "sheet" part of  the formula
        foreach ($spreadsheet->getNamedRanges() as $namedRange) {
            $a = explode('!', $namedRange->getValue());
            $namedRange->setValue(end($a));

            if ($namedRange->getScope() != null) {
                throw new \Exception('!!');
            }

            // Turn named range into it's relative equivalent
            // so that they are automatically updated during an insert/delete
            $spreadsheet->addNamedRange(new NamedRange(
                $namedRange->getName(),
                $namedRange->getWorksheet(),
                str_replace('$', '', $namedRange->getValue())
            ));
        }


        //___________________
        // Render Entete

        $sheet = $spreadsheet->getSheet(0);

        $repo = new ExportComparatifRepo($this->pdo);
        $infoEntet = $repo->getInfoEntet($aoId);
        if (empty($infoEntet)) {
            throw new \Exception('Invalid ao #'.$aoId);
        }

        foreach ($sheet->getRowIterator() as $row) {
            foreach ($row->getCellIterator() as $cell) {
                $value = $cell->getValue();
                if (!is_string($value)) {
                    continue;
                }
                
                $this->processCellPlaceholder(
                    $cell,
                    $infoEntet,
                    [$this, 'renderPlaceholderEntet']
                );
            }
        }

        //___________________
        // Render repeat CS

        $sheet = $spreadsheet->setActiveSheetIndex(1);
        $block = Block::createFromNamedRange(
            $spreadsheet->getNamedRange('REPEAT_COL_CS', $sheet)
        );
        
        foreach ($infoEntet['LigneMapChampSupp'] as $cs) {
            $copy = $block->duplicateRight();

            $copy->walkCell(function (Cell $cell) use ($cs) {
                $value = $cell->getValue();
                if (!is_string($value)) {
                    return;
                }
                $value = str_replace('%CS_NOM%', $cs['NOM'], $value);
                $value = str_replace('{LOOP_ID}', $cs['NUM_FIX'], $value);
                $cell->setValue($value);
            });
        }

        $block->removeColumn();
        unset($block);

        //___________________
        // Repeat block four

        $block = Block::createFromNamedRange(
            $spreadsheet->getNamedRange('REPEAT_COL_FOUR', $sheet)
        );
        foreach ($infoEntet['ListFourRep'] as $index => $repEntet) {
            $copy = $block->duplicateRight();

            $copy->walkCell(function (Cell $cell) use ($infoEntet, $index, $repEntet, $fillFourHeaderOdd, $fillFourHeaderEven, $fillFourOdd, $fillFourEven) {
                $fill = $cell->getStyle()->getFill();

                
                if ($index % 2 == 0) {
                    // Replace fill header
                    if ($fill->exportArray() == $fillFourHeaderOdd) {
                        $fill->applyFromArray($fillFourHeaderEven);
                    }
    
                    // Replace fill row
                    if ($fill->exportArray() == $fillFourOdd) {
                        $fill->applyFromArray($fillFourEven);
                    }
                }
                
                
                
                $value = $cell->getValue();
                if (!is_string($value)) {
                    return;
                }

                $this->processCellPlaceholder(
                    $cell,
                    $repEntet,
                    function (string $code, array $repEntet) use ($infoEntet) {
                        return $this->renderPlaceholderHeaderFour($code, $repEntet, $infoEntet);
                    },
                    function (string $code, array $repEntet) use ($infoEntet) {
                        return $this->renderPlaceholderHeaderFourNumber($code, $repEntet, $infoEntet);
                    }
                );
                
                $value = $cell->getValue();
                if (is_string($value)) {
                    $value = str_replace('{LOOP_ID}', $repEntet['RepId'], $value);
                    $cell->setValue($value);
                }
            });
        }

        $block->removeColumn();
        unset($block);

        //___________________
        // Repeat row

        $listLigne = $repo->getInfoLigne($aoId);

        foreach ($listLigne as $ligne) {
            $blockSousLigne = Block::createFromNamedRange(
                $spreadsheet->getNamedRange('REPEAT_ROW_SL', $sheet)
            );
            $block = Block::createFromNamedRange(
                $spreadsheet->getNamedRange('REPEAT_ROW', $sheet)
            );
            $blockTitre = Block::createFromNamedRange(
                $spreadsheet->getNamedRange('COND_ROW_TITLE', $sheet)
            );
            $blockSousTotal = Block::createFromNamedRange(
                $spreadsheet->getNamedRange('COND_SOUSTOTAL', $sheet)
            );

            $copy = $block->duplicateTop();

            $minCol = null;
            $maxCol = null;
            $copy->walkCell(function (Cell $cell) use ($ligne, &$minCol, &$maxCol) {
                $value = $cell->getValue();
                if (!is_string($value)) {
                    return;
                }

                // Case : best option -> hightlight in green
                if (str_starts_with($value, '%REP.')) {
                    list(,$RepId) = explode('.', $value);
                    if ($RepId == ($ligne['BestRep']['RepId'] ?? null)) {
                        $col = SpreadsheetHelper::getColIndex($cell);
                        
                        $minCol = ($minCol === null ? $col : min($col, $minCol));
                        $maxCol = max($col, $maxCol);
                        // $cell->getStyle()
                        //     ->getFill()
                        //     ->applyFromArray($fillGood);
                    }
                }

                $this->processCellPlaceholder(
                    $cell,
                    $ligne,
                    [$this, 'renderPlaceholderLigne'],
                    [$this, 'replacePlaceholderLigneNumber']
                );
            });
            
            // Change background to fillGood for best rep for this AoLigne
            if ($minCol !== null && $maxCol !== null) {
                Block::createFromList($sheet, [
                    [ $minCol, $copy->getMinRow() ],
                    [ $maxCol, $copy->getMaxRow() ],
                ])->walkCell(function (Cell $cell) use ($fillGood) {
                    $cell->getStyle()
                        ->getFill()
                        ->applyFromArray($fillGood);
                });
            }

            if (!$ligne['ST_APLIG']) {
                // TODO : update block range to allow blockSousTotal located above blockSousLigne
                // Order of operation is important since blockSousLigne is not updated
                // by the blockSousTotal->removeRow
                $blockSousTotal->removeRow();
            }
            

            // Get all possible num sous-ligne for iteration
            $listSousLigneNum = array_unique(array_column(
                array_reduce($ligne['MapLignRep'], function ($carry, $rep) {
                    return array_merge($carry, $rep['MapSousLigne']);
                }, []),
                'NO_SSLIG'
            ));

            foreach ($listSousLigneNum as $numSousLigne) {
                $copy = $blockSousLigne->duplicateTop();

                // Get all SousLigne with num $numSousLigne indexed by their RepId
                $mapSousLigne = array_column(
                    array_reduce($ligne['MapLignRep'], function ($carry, $rep) use ($numSousLigne) {
                        $sousLigne = $rep['MapSousLigne'][ $numSousLigne ] ?? null;
                        if ($sousLigne == null) {
                            return $carry;
                        }
                        $carry[] = $sousLigne;
                        return $carry;
                    }, []),
                    null,
                    'RepId'
                );

                $copy->walkCell(function (Cell $cell) use ($mapSousLigne) {
                    
                    $value = $cell->getValue();
                    if (!is_string($value)) {
                        return;
                    }
            
                    $code = trim($value, '%');
            
                   // Extract %REPSL.{LOOP_ID}.TOTO%
                    if (!str_starts_with($code, 'REPSL.')) {
                        return;
                    }

                    list(, $repId, $code) = explode('.', $code);
                    $sousLigne = $mapSousLigne[ $repId ] ?? [];
            
                    if (empty($sousLigne)) {
                        $cell->setValue('');
                        return;
                    }

                    $cell->setValue('%'.$code.'%'); // Setup a simplier placeholder for processCellPlaceholder
                    $this->processCellPlaceholder(
                        $cell,
                        $sousLigne,
                        [$this,'renderPlaceholderSousLigneRepString'],
                        [$this,'renderPlaceholderSousLigneRepNumber']
                    );
                });
            }

            $blockSousLigne->removeRow();

            if (empty($ligne['Titre'])) {
                $blockTitre->removeRow();
            }
        }

        

        // Remove template for the rows
        $block = Block::createFromNamedRange(
            $spreadsheet->getNamedRange('REPEAT_ROW', $sheet)
        );
        $block->removeRow();
        unset($block);

        // Update range of rows
        //$mapNamedRangeBoundary['REPEAT_ROW']['max'][0] += $colCount;

        //___________________
        // Sheet "prix" global

        foreach ($sheet->getRowIterator() as $row) {
            foreach ($row->getCellIterator() as $cell) {
                $value = $cell->getValue();
                if (!is_string($value)) {
                    continue;
                }

                $this->processCellPlaceholder(
                    $cell,
                    $infoEntet,
                    function ($code, $data) {
                        return '';
                    },
                    [$this, 'renderPlaceholderPrix']
                );
            }
        }

        
        // Clean up
        $spreadsheet->removeDefinedName('COLOR_FOUR_EVEN');
        $spreadsheet->removeDefinedName('COLOR_FOUR_ODD');
        $spreadsheet->removeDefinedName('COLOR_FOUR_HEADER_EVEN');
        $spreadsheet->removeDefinedName('COLOR_FOUR_HEADER_ODD');
        $spreadsheet->removeDefinedName('COLOR_GOOD');
        $spreadsheet->removeDefinedName('REPEAT_COL_CS');
        $spreadsheet->removeDefinedName('REPEAT_COL_FOUR');
        $spreadsheet->removeDefinedName('REPEAT_ROW');

        // Trad titles
        $trad = $this->getTranslationService();
        foreach ($spreadsheet->getAllSheets() as $sheet) {
            $sheet->setTitle($trad->t($sheet->getTitle()));
        }

        // DEBUG
        // $writer = new \PhpOffice\PhpSpreadsheet\Writer\Html($spreadsheet);
        // $writer->setSheetIndex(1);
        // e-cho $writer->generateHtmlAll();
        // e-xit;

        $filePath = Fichier::getDirExport() . '/AO_' .$aoId. '_EXPORT_' . uniqid() . '.xlsx';
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->save($filePath);

        return $filePath;
    }


    /**
     * @param array<mixed> $data
     */
    private function getNumberCellFormat(string $code, array $data) :string
    {
        // Case : prix unitaire
        if (in_array($code, ['PU', 'PRIX_REF']) || str_ends_with($code, '.PU')) {
            return '# ##0.' . str_pad('', Constante::puDecimal, '0');
        }

        // Case : quantite
        if (in_array($code, ['QTE', 'QTE_PROP']) || str_ends_with($code, '.QTE')) {
            return '# ##0.' . str_pad(
                '',
                Outils::getUniteDecimal($this->pdo, $data['UNITE']),
                '0'
            );
        }
        return '# ##0.00';
    }

    /**
     * @param array<mixed> $data
     */
    private function processCellPlaceholder(
        Cell $cell,
        array $data,
        callable $fn,
        callable $fnNumber = null
    ) :void {
        $value = $cell->getValue();

        // Case : cell whole value is a placeholder -> try number with formating
        if ($fnNumber != null && preg_match('@^%([0-9A-z_\.\|]+)%$@', $value)) {
            $code = trim($value, '%');
            $number = $fnNumber($code, $data);
            // Case : number formating successfull
            if ($number !== null) {
                $cell->setValue($number);

                // Force number format
                $cell->setDataType(DataType::TYPE_NUMERIC);
                $cell
                    ->getStyle()
                    ->getNumberFormat()
                    ->setFormatCode($this->getNumberCellFormat($code, $data));

                return;
            }
        }

        $cell->setValueExplicit(
            $this->replacePlaceholder($value, $data, $fn),
            DataType::TYPE_STRING
        );
    }

    /**
     * @param array<mixed> $data
     */
    private function replacePlaceholder(
        string $value,
        array $data,
        callable $fn
    ) :string {

        // "0-9A-z_." for var name; "|" is used by RepId;
        return preg_replace_callback(
            '@%([0-9A-z_\.\|]+)%@',
            function ($listMatch) use ($data, $fn) {
                return $fn($listMatch[1], $data);
            },
            $value
        );
    }

    /**
     * @param array<mixed> $data
     */
    private function renderPlaceholderEntet(string $code, array $data) :string
    {
        if (in_array($code, [
            // Entet
            'NO_AO', 'DEVISE', 'CountFour', 'CountRep',
            'Titre', 'Commentaire',
        ])) {
            return $data[ $code ];
        }

        if (str_starts_with($code, 'CS_')) {
            list(, $id, $field) = explode('_', $code);

            $cs = array_values($data['MapChampSupp'])[ intval($id) - 1 /* convert  id to index */ ] ?? [];
            if ($field == 'VALEUR') {
                return $this->renderChampSupValue($cs);
            }
            return $cs[ $field ] ?? '';
        }

        switch ($code) {
            case 'date_created':
                return Date::dateConverte(date('Ymd'));
            case 'centre_emetteur':
                return $data['CentreEmet_RAIS_SOC'] . ' (' . $data['CentreEmet_CODE_CTR'] . ')';
            case 'list_demandeur':
                return implode(
                    ' - ',
                    array_map(function ($dem) {
                        return $dem['NOM'] . ' (' . $dem['CODE'] . ')';
                    }, $data['ListDemandeur'])
                );
            case 'acheteur':
                return $data['Acheteur_NOM'] . ' (' . $data['Acheteur_CODE'] . ')';
            case 'BUDG_ENT':
                return Outils::formatMontant($data[ $code ]);
        }
        LogSysteme::prefixErrorLog((string)(new \Exception('Invalid placeholder "'.$code.'"')));
        return '';
    }


    /**
     * @param array<mixed> $entetInfo
     */
    private function renderPlaceholderPrix(string $code, array $entetInfo) :?float
    {
        switch ($code) {
            case 'Total.Budget':
                return Outils::formatMontantExcel($entetInfo[ $code ]);
        }
        return null;
    }

    /**
     * @param array<mixed> $data
     * @param array<mixed> $infoEntet
     */
    private function renderPlaceholderHeaderFour(string $code, array $data, array $infoEntet) :string
    {
        
        if (in_array($code, [
            'REFRPFOU', 'OriginLabel', 'CODE_FOU', 'Commentaire'
        ])) {
            return $data[ $code ];
        }

        switch ($code) {
            case 'colon':
                return $this->getTranslationService()->colon();
            case 'DTELIMIT':
            case 'DTE_REP':
                return Date::dateConverte($data[ $code ]);
            case 'TypeRepLabel':
                $trad = $this->getTranslationService();
                return ($data['C_V'] == 'C' ?
                    $trad->t('Type réponse conforme') :
                    $trad->t('Type réponse variante'));
            case 'TypeFourLabel':
                $trad = $this->getTranslationService();
                return ($data['TYPE_FG'] == 'F' ?
                    $trad->t('Fournisseur') : $trad->t('Groupe'));
        }
        LogSysteme::prefixErrorLog((string)(new \Exception('Invalid placeholder "'.$code.'"')));
        return '';
    }

    /**
     * @param array<mixed> $data
     * @param array<mixed> $infoEntet
     */
    private function renderPlaceholderHeaderFourNumber(
        string $code,
        array $data,
        array $infoEntet
    ) :?float {
        switch ($code) {
            case 'Total.Montant':
                return Outils::formatMontantExcel($data['Total.Montant']);
            case 'Total.EcartBest':
                return Outils::formatMontantExcel($data['Total.EcartBest']);
            case 'Total.EcartBestPercent':
                return Outils::formatMontantExcel(
                    $this->getDeltaPercent(
                        $infoEntet['Total.BestRep']['Total.Montant'] ?? 0,
                        $data['Total.Montant'] ?? 0
                    )
                );
            case 'Total.EcartBudget':
                return Outils::formatMontantExcel(
                    $data['Total.Montant'] - $infoEntet['Total.Budget']
                );
            case 'Total.EcartBudgetPercent':
                return Outils::formatMontantExcel(
                    $this->getDeltaPercent(
                        $infoEntet['Total.Budget'],
                        $data['Total.Montant'] ?? 0
                    )
                );
            case 'Total.EcartPrix':
                return Outils::formatMontantExcel(
                    $data['Total.Montant'] - $infoEntet['Total.MontantRef']
                );
            case 'Total.EcartPrixPercent':
                return Outils::formatMontantExcel(
                    $this->getDeltaPercent(
                        $infoEntet['Total.MontantRef'],
                        $data['Total.Montant']
                    )
                );
        }
        return null;
    }

    /**
     * @param array<mixed> $data
     */
    private function renderPlaceholderLigne(string $code, array $data) :string
    {
        // REPSL are handled by renderPlaceholderSousLigneRep -> Ignore
        if (str_starts_with($code, 'REPSL.')) {
            return '%'.$code.'%';
        }

        // Pass through
        if (in_array($code, [
            'NO_PO', 'CODE_ART', 'DES', 'DesComp', 'UNITE', 'TITREST', 'Titre'
        ])) {
            return $data[ $code ];
        }

        if (str_starts_with($code, 'CS_')) {
            list(, $numfix, $field) = explode('_', $code);
            if ($field == 'VALEUR') {
                return $this->renderChampSupValue($data['MapChampSupp'][ $numfix ] ?? []);
            }
            return $data['MapChampSupp'][ $numfix ][ $field ] ?? '';
        }

        if (str_starts_with($code, 'REP.')) {
            list(, $repId, $field) = explode('.', $code);
            return $this->renderPlaceholderLigneRep(
                $field,
                $data['MapLignRep'][ $repId ] ?? [],
                $data
            );
        }

        switch ($code) {
            case 'DELAI':
                return ($data['DELAI'] != '00000000' ?
                    Date::dateConverte($data['DELAI']) : '');
        }
        LogSysteme::prefixErrorLog((string) (new \Exception('Invalid placeholder "'.$code.'"')));
        return '';
    }

    /**
     * @param array<mixed> $data
     */
    private function replacePlaceholderLigneNumber(string $code, array $data) :?float
    {
        if (str_starts_with($code, 'REP.')) {
            list(, $repId, $field) = explode('.', $code);
            return $this->renderPlaceholderLigneRepNumber(
                $field,
                $data['MapLignRep'][ $repId ] ?? [],
                $data
            );
        }

        switch ($code) {
            case 'QTE':
                return floatval(
                    Outils::qteByUnite($this->pdo, $data['QTE'], $data['UNITE'])
                );
            case 'PRIX_REF':
            case 'BUDGET':
            case 'SousTotal.Budget':
                return Outils::formatMontantExcel($data[ $code ] ?? 0);
        }
        
        return null;
    }

    /**
     * @param array<mixed> $data
     * @param array<mixed> $ligne
     */
    private function renderPlaceholderLigneRep(string $code, array $data, array $ligne) :string
    {
        // Pass through
        if (in_array($code, [
            'CODE_ART', 'DES_ART', 'DesCompl', 'COMFOURN', 'UNITE'
        ])) {
            return $data[ $code ] ?? '';
        }
        LogSysteme::prefixErrorLog((string) (new \Exception('Invalid placeholder "'.$code.'"')));
        return '';
    }

    /**
     * @param array<mixed> $data
     * @param array<mixed> $ligne
     */
    private function renderPlaceholderLigneRepNumber(string $code, array $data, array $ligne) :?float
    {
        switch ($code) {
            case 'QTE':
                return floatval(
                    Outils::qteByUnite($this->pdo, $data[ $code ], $data['UNITE'])
                );
            case 'PU':
                return Outils::formatPrixUnitaireExcel($data[ $code ] ?? 0);
            case 'Montant':
                return Outils::formatMontantExcel($data[ $code ] ?? 0);
            case 'EcartBest':
                // Montant valorisé avec le meilleur prix parmi toutes les réponses
                // du même type, pour la ligne
                return Outils::formatMontantExcel($data['EcartBest']);
            case 'EcartBestPercent':
                return Outils::formatMontantExcel(
                    $this->getDeltaPercent(
                        $ligne['BestRep']['Montant'] ?? 0,
                        $data['Montant'] ?? 0
                    )
                );
            case 'EcartBudget':
                return Outils::formatMontantExcel(
                    $data['Montant'] - $ligne['BUDGET']
                );
            case 'EcartBudgetPercent':
                return Outils::formatMontantExcel(
                    $this->getDeltaPercent($ligne['BUDGET'], $data['Montant'] ?? 0)
                );
            case 'EcartPrixRef':
                return Outils::formatMontantExcel(
                    $data['Montant'] - $ligne['MontantPrixRef']
                );
            case 'EcartPrixRefPercent':
                return Outils::formatMontantExcel(
                    $this->getDeltaPercent($ligne['MontantPrixRef'], $data['Montant'] ?? 0)
                );

            // SOUS TOTAUX
            // Note : those placeholder are rendered even for RepLine without SousTotal (a 0 should be returned)
            case 'SousTotalMontant':
            case 'SousTotalEcartBest':
                return Outils::formatMontantExcel(($data[ $code ] ?? 0));
            case 'SousTotalEcartBestPercent':
                return Outils::formatMontantExcel(
                    $this->getDeltaPercent(
                        ($ligne['SousTotal.BestRep']['SousTotalMontant'] ?? 0),
                        $data['SousTotalMontant'] ?? 0
                    )
                );

                
            case 'SousTotalEcartBudget':
                return Outils::formatMontantExcel(
                    ($data['SousTotalMontant'] ?? 0) - ($ligne['SousTotal.Budget'] ?? 0)
                );
            case 'SousTotalEcartBudgetPercent':
                return Outils::formatMontantExcel(
                    $this->getDeltaPercent(
                        $ligne['SousTotal.Budget'] ?? 0,
                        $data['SousTotalMontant'] ?? 0
                    )
                );
            case 'SousTotalEcartPrixRef':
                return Outils::formatMontantExcel(
                    ($data['SousTotalMontant'] ?? 0) - ($ligne['SousTotal.MontantRef'] ?? 0)
                );
            case 'SousTotalEcartPrixRefPercent':
                return Outils::formatMontantExcel(
                    $this->getDeltaPercent(
                        $ligne['SousTotal.MontantRef'] ?? 0,
                        $data['SousTotalMontant'] ?? 0
                    )
                );
        }
        
        return null;
    }

    /**
     * @param array<mixed> $data Entity AoRepSl
     */
    private function renderPlaceholderSousLigneRepString(string $code, array $data) :string
    {
        // Pass through
        if (in_array($code, [
            'CODE_ART', 'DES_ART', 'DCOMP', 'UNITE',
        ])) {
            return $data[ $code ];
        }
        LogSysteme::prefixErrorLog((string) (new \Exception('Invalid placeholder "'.$code.'"')));
        return '';
    }
    /**
     * @param array<mixed> $data Entity AoRepSl
     */
    private function renderPlaceholderSousLigneRepNumber(string $code, array $data) :?float
    {
        switch ($code) {
            case 'QTE_PROP':
                return floatval(Outils::qteByUnite($this->pdo, $data['QTE_PROP'], $data['UNITE']));
            case 'Montant':
            case 'PU':
                return Outils::formatMontantExcel($data[ $code ]);
        }
        
        return null;
    }

    private function getDeltaPercent(float $ref, float $value) :float
    {
        if ($ref == 0) {
            return 0;
        }
        return (($value - $ref) / $ref) * 100;
    }

    /**
     * @param array<mixed> $cs
     */
    private function renderChampSupValue(array $cs) :string
    {
        if (empty($cs)) {
            return '';
        }

        if ($cs['FORMAT'] == 'L') {
            $trad = $this->getTranslationService();
            return Sql::toBool($cs['VALEUR']) ?
                $trad->t('Oui') : $trad->t('Non');
        }
        // TODO : format number ?
        // TODO : format 'A' ?


        return $cs['VALEUR'];
    }
}
