<?php

namespace LWVendor\PhpOffice\PhpSpreadsheet\Reader\Ods;

use LWVendor\PhpOffice\PhpSpreadsheet\Calculation\Calculation;
class FormulaTranslator
{
    public static function convertToExcelAddressValue(string $openOfficeAddress) : string
    {
        $excelAddress = $openOfficeAddress;
        // Cell range 3-d reference
        // As we don't support 3-d ranges, we're just going to take a quick and dirty approach
        //  and assume that the second worksheet reference is the same as the first
        $excelAddress = (string) \preg_replace([
            '/\\$?([^\\.]+)\\.([^\\.]+):\\$?([^\\.]+)\\.([^\\.]+)/miu',
            '/\\$?([^\\.]+)\\.([^\\.]+):\\.([^\\.]+)/miu',
            // Cell range reference in another sheet
            '/\\$?([^\\.]+)\\.([^\\.]+)/miu',
            // Cell reference in another sheet
            '/\\.([^\\.]+):\\.([^\\.]+)/miu',
            // Cell range reference
            '/\\.([^\\.]+)/miu',
        ], ['$1!$2:$4', '$1!$2:$3', '$1!$2', '$1:$2', '$1'], $excelAddress);
        return $excelAddress;
    }
    public static function convertToExcelFormulaValue(string $openOfficeFormula) : string
    {
        $temp = \explode(Calculation::FORMULA_STRING_QUOTE, $openOfficeFormula);
        $tKey = \false;
        $inMatrixBracesLevel = 0;
        $inFunctionBracesLevel = 0;
        foreach ($temp as &$value) {
            // @var string $value
            // Only replace in alternate array entries (i.e. non-quoted blocks)
            //      so that conversion isn't done in string values
            $tKey = $tKey === \false;
            if ($tKey) {
                $value = (string) \preg_replace([
                    '/\\[\\$?([^\\.]+)\\.([^\\.]+):\\.([^\\.]+)\\]/miu',
                    // Cell range reference in another sheet
                    '/\\[\\$?([^\\.]+)\\.([^\\.]+)\\]/miu',
                    // Cell reference in another sheet
                    '/\\[\\.([^\\.]+):\\.([^\\.]+)\\]/miu',
                    // Cell range reference
                    '/\\[\\.([^\\.]+)\\]/miu',
                ], ['$1!$2:$3', '$1!$2', '$1:$2', '$1'], $value);
                // Convert references to defined names/formulae
                $value = \str_replace('$$', '', $value);
                // Convert ODS function argument separators to Excel function argument separators
                $value = Calculation::translateSeparator(';', ',', $value, $inFunctionBracesLevel);
                // Convert ODS matrix separators to Excel matrix separators
                $value = Calculation::translateSeparator(';', ',', $value, $inMatrixBracesLevel, Calculation::FORMULA_OPEN_MATRIX_BRACE, Calculation::FORMULA_CLOSE_MATRIX_BRACE);
                $value = Calculation::translateSeparator('|', ';', $value, $inMatrixBracesLevel, Calculation::FORMULA_OPEN_MATRIX_BRACE, Calculation::FORMULA_CLOSE_MATRIX_BRACE);
                $value = (string) \preg_replace('/COM\\.MICROSOFT\\./ui', '', $value);
            }
        }
        // Then rebuild the formula string
        $excelFormula = \implode('"', $temp);
        return $excelFormula;
    }
}
