www-lib-markdown/phptest/spreadsheet/ExpressionSetTests.php

<?php
declare(strict_types=1);

use PHPUnit\Framework\TestCase;

require_once __DIR__ . '/../../php/markdown.php';
require_once __DIR__ . '/../../php/spreadsheet.php';

final class ExpressionSetTests extends TestCase {
	private function assertSameCellValue(CellValue $expected, CellValue $actual) {
		$this->assertSame($expected->formattedValue, $actual->formattedValue);
		if (gettype($expected->value) === 'double' && gettype($actual->value) === 'double') {
			$this->assertEqualsWithDelta($expected->value, $actual->value, 0.000_001);
		} else {
			$this->assertSame($expected->value, $actual->value);
		}
		$this->assertSame($expected->type, $actual->type);
		$this->assertSame($expected->decimals, $actual->decimals);
	}

	public function test_simpleMath() {
		$grid = new SpreadsheetGrid(1, 1);
		$grid->cells[0][0]->originalValue = CellValue::fromCellString('=7*3');
		$expressionSet = new CellExpressionSet($grid);
		$expressionSet->calculateCells();
		$expected = CellValue::fromValue(21);
		$actual = $grid->cells[0][0]->outputValue;
		$this->assertSameCellValue($expected, $actual);
	}

	public function test_reference() {
		$grid = new SpreadsheetGrid(3, 1);
		$grid->cells[0][0]->originalValue = CellValue::fromValue(123);
		$grid->cells[1][0]->originalValue = CellValue::fromValue(3);
		$grid->cells[2][0]->originalValue = CellValue::fromCellString('=A1*B1');
		$expressionSet = new CellExpressionSet($grid);
		$expressionSet->calculateCells();
		$expected = CellValue::fromValue(369);
		$actual = $grid->cells[2][0]->outputValue;
		$this->assertSameCellValue($expected, $actual);
	}

	public function test_infixPriority() {
		$grid = new SpreadsheetGrid(1, 1);
		$grid->cells[0][0]->originalValue = CellValue::fromCellString('=4*9/3+7-4');
		$expressionSet = new CellExpressionSet($grid);
		$expressionSet->calculateCells();
		$expected = CellValue::fromValue(15);
		$actual = $grid->cells[0][0]->outputValue;
		$this->assertSameCellValue($expected, $actual);
	}

	public function test_filledFormula() {
		$grid = new SpreadsheetGrid(3, 3);
		$grid->cells[0][0]->originalValue = CellValue::fromValue(4);
		$grid->cells[1][0]->originalValue = CellValue::fromValue(5);
		$grid->cells[2][0]->originalValue = CellValue::fromCellString('=A1*B1 FILL');
		$grid->cells[0][1]->originalValue = CellValue::fromValue(6);
		$grid->cells[1][1]->originalValue = CellValue::fromValue(7);
		$grid->cells[0][2]->originalValue = CellValue::fromValue(8);
		$grid->cells[1][2]->originalValue = CellValue::fromValue(9);
		$expressionSet = new CellExpressionSet($grid);
		$expressionSet->calculateCells();
		$this->assertSameCellValue(CellValue::fromValue(20), $grid->cells[2][0]->outputValue);
		$this->assertSameCellValue(CellValue::fromValue(42), $grid->cells[2][1]->outputValue);
		$this->assertSameCellValue(CellValue::fromValue(72), $grid->cells[2][2]->outputValue);
	}

	public function test_dependencies() {
		$grid = new SpreadsheetGrid(2, 4);
		$grid->cells[0][0]->originalValue = CellValue::fromValue(1);
		$grid->cells[1][0]->originalValue = CellValue::fromCellString('=A1+B2');
		$grid->cells[0][1]->originalValue = CellValue::fromValue(2);
		$grid->cells[1][1]->originalValue = CellValue::fromCellString('=A2+B3');
		$grid->cells[0][2]->originalValue = CellValue::fromValue(3);
		$grid->cells[1][2]->originalValue = CellValue::fromCellString('=A3+B4');
		$grid->cells[0][3]->originalValue = CellValue::fromValue(4);
		$grid->cells[1][3]->originalValue = CellValue::fromCellString('=A4');
		$expressionSet = new CellExpressionSet($grid);
		$expressionSet->calculateCells();
		$this->assertSameCellValue(CellValue::fromValue(10), $grid->cells[1][0]->outputValue);
		$this->assertSameCellValue(CellValue::fromValue(9), $grid->cells[1][1]->outputValue);
		$this->assertSameCellValue(CellValue::fromValue(7), $grid->cells[1][2]->outputValue);
		$this->assertSameCellValue(CellValue::fromValue(4), $grid->cells[1][3]->outputValue);
	}

	private function test_simple_formula($expected, $formula) {
		$grid = new SpreadsheetGrid(1, 1);
		$grid->cells[0][0]->originalValue = CellValue::fromCellString($formula);
		$expressionSet = new CellExpressionSet($grid);
		$expressionSet->calculateCells();
		$actual = $grid->cells[0][0]->outputValue;
		$exp = ($expected instanceof CellValue) ? $expected : CellValue::fromValue($expected);
		$this->assertSameCellValue($exp, $actual);
	}

	public function test_func_abs() {
		$this->test_simple_formula(3, '=ABS(-3)');
		$this->test_simple_formula(4, '=ABS(4)');
	}

	public function test_func_and() {
		$this->test_simple_formula(false, '=AND(FALSE, FALSE)');
		$this->test_simple_formula(false, '=AND(FALSE, TRUE)');
		$this->test_simple_formula(false, '=AND(TRUE, FALSE)');
		$this->test_simple_formula(true, '=AND(TRUE, TRUE)');
		$this->test_simple_formula(true, '=AND(TRUE, TRUE, TRUE, TRUE)');
		$this->test_simple_formula(false, '=AND(TRUE, TRUE, TRUE, FALSE)');
	}

	public function test_func_average() {
		$this->test_simple_formula(4, '=AVERAGE(4, 6, 2, 4)');
		$this->test_simple_formula(4, '=AVERAGE(4, 6, 2, "foo", 4)');
	}

	public function test_func_ceiling() {
		$this->test_simple_formula(4.0, '=CEILING(3.1)');
		$this->test_simple_formula(3.0, '=CEILING(3)');
		$this->test_simple_formula(-3.0, '=CEILING(-3.1)');
	}

	public function test_func_exp() {
		$this->test_simple_formula(2.718281828459045, '=EXP(1)');
	}

	public function test_func_floor() {
		$this->test_simple_formula(3.0, '=FLOOR(3.1)');
		$this->test_simple_formula(3.0, '=FLOOR(3)');
		$this->test_simple_formula(-4.0, '=FLOOR(-3.1)');
	}

	public function test_func_if() {
		$this->test_simple_formula(6, '=IF(FALSE, 4, 6)');
		$this->test_simple_formula(4, '=IF(TRUE, 4, 6)');
	}

	public function test_func_ifs() {
		$this->test_simple_formula(1, '=IFS(TRUE, 1, FALSE, 2, FALSE, 3, FALSE, 4, 5)');
		$this->test_simple_formula(2, '=IFS(FALSE, 1, TRUE, 2, FALSE, 3, FALSE, 4, 5)');
		$this->test_simple_formula(3, '=IFS(FALSE, 1, FALSE, 2, TRUE, 3, FALSE, 4, 5)');
		$this->test_simple_formula(4, '=IFS(FALSE, 1, FALSE, 2, FALSE, 3, TRUE, 4, 5)');
		$this->test_simple_formula(5, '=IFS(FALSE, 1, FALSE, 2, FALSE, 3, FALSE, 4, 5)');
	}

	public function test_func_ln() {
		$this->test_simple_formula(1.0, '=LN(2.718281828459045)');
	}

	public function test_func_log() {
		$this->test_simple_formula(3.0, '=LOG(1000, 10)');
		$this->test_simple_formula(6.0, '=LOG(64, 2)');
	}

	public function test_func_lower() {
		$this->test_simple_formula('mixed', '=LOWER("MiXeD")');
	}

	public function test_func_max() {
		$this->test_simple_formula(8, '=MAX(4, 8, 5, 2)');
	}

	public function test_func_min() {
		$this->test_simple_formula(2, '=MIN(4, 8, 5, 2)');
	}

	public function test_func_mod() {
		$this->test_simple_formula(1, '=MOD(37, 4)');
	}

	public function test_func_not() {
		$this->test_simple_formula(false, '=NOT(TRUE)');
		$this->test_simple_formula(true, '=NOT(FALSE)');
	}

	public function test_func_or() {
		$this->test_simple_formula(false, '=OR(FALSE, FALSE)');
		$this->test_simple_formula(true, '=OR(FALSE, TRUE)');
		$this->test_simple_formula(true, '=OR(TRUE, FALSE)');
		$this->test_simple_formula(true, '=OR(TRUE, TRUE)');
		$this->test_simple_formula(true, '=OR(FALSE, FALSE, FALSE, TRUE)');
	}

	public function test_func_power() {
		$this->test_simple_formula(8, '=POWER(2, 3)');
	}

	public function test_func_round() {
		$this->test_simple_formula(3.0, '=ROUND(3.1)');
		$this->test_simple_formula(4.0, '=ROUND(3.5)');
		$this->test_simple_formula(4.0, '=ROUND(4)');
		$this->test_simple_formula(-3.0, '=ROUND(-3.1)');
		$this->test_simple_formula(-3.0, '=ROUND(-3.5)');
		$this->test_simple_formula(-4.0, '=ROUND(-3.9)');
		$this->test_simple_formula(3.1, '=ROUND(3.1415926535, 1)');
		$this->test_simple_formula(3.14, '=ROUND(3.1415926535, 2)');
		$this->test_simple_formula(30.0, '=ROUND(31.415926535, -1)');
	}

	public function test_func_sqrt() {
		$this->test_simple_formula(4.0, '=SQRT(16)');
	}

	public function test_func_substitute() {
		$this->test_simple_formula('cot sot on the mot', '=SUBSTITUTE("cat sat on the mat", "at", "ot")');
		$this->test_simple_formula('cot sot on the mot', '=SUBSTITUTE("cAt saT on the mat", "at", "ot")');
		$this->test_simple_formula('cot sot on the mot', '=SUBSTITUTE("c.*t s.*t on the m.*t", ".*t", "ot")');
	}

	public function test_func_sum() {
		$this->test_simple_formula(15, '=SUM(1, 2, 3, 4, 5)');
	}

	public function test_func_upper() {
		$this->test_simple_formula('MIXED', '=UPPER("mIxEd")');
	}

	public function test_func_xor() {
		$this->test_simple_formula(false, '=XOR(FALSE, FALSE)');
		$this->test_simple_formula(true, '=XOR(FALSE, TRUE)');
		$this->test_simple_formula(true, '=XOR(TRUE, FALSE)');
		$this->test_simple_formula(false, '=XOR(TRUE, TRUE)');
		$this->test_simple_formula(true, '=XOR(FALSE, FALSE, TRUE)');
		$this->test_simple_formula(false, '=XOR(TRUE, FALSE, TRUE)');
	}

	public function test_format() {
		$this->test_simple_formula(new CellValue('2.718', 2.718281828459045, 'number', 3), '=2.718281828459045 ; number 3');
		$this->test_simple_formula(new CellValue('271.83%', 2.718281828459045, 'percent', 2), '=2.718281828459045 ; percent 2');
		$this->test_simple_formula(new CellValue('$2.72', 2.718281828459045, 'currency', 2), '=2.718281828459045 ; currency 2');
	}

	private function iterateCharacters(string $formula, bool $testFinal=false) {
		for ($i = 1; $i < mb_strlen($formula); $i++) {
			$portion = mb_substr($formula, 0, $i);
			$grid = new SpreadsheetGrid(1, 1);
			$grid->cells[0][0]->originalValue = CellValue::fromCellString($portion);
			$expressionSet = new CellExpressionSet($grid);
			$expressionSet->calculateCells();
		}
		if ($testFinal) {
			$grid = new SpreadsheetGrid(1, 1);
			$grid->cells[0][0]->originalValue = CellValue::fromCellString($formula);
			$expressionSet = new CellExpressionSet($grid);
			$expressionSet->calculateCells();
			$actual = $grid->cells[0][0]->outputValue;
			if ($actual === null) {
				$this->fail("Expected \"{$formula}\" to evaluate to a value, got null");
			} elseif ($actual->type === CellValue::TYPE_ERROR) {
				$this->fail("Expected \"{$formula}\" to evaluate to a value, got error {$actual->value->getMessage()}");
			}
		}
	}

	public function test_partialAndBrokenSyntax() {
		// Like `BrokenSyntaxTests`, this tries parsing a bunch of cell values
		// character by character like an author typing them in and makes sure
		// they don't throw exceptions. The `true` flag will do a simple check
		// that the final complete expression evaluates to something other than
		// `null` or an error.
		$this->iterateCharacters("123", true);
		$this->iterateCharacters("-123", true);
		$this->iterateCharacters("true", true);
		$this->iterateCharacters("false", true);
		$this->iterateCharacters("TrUe", true);
		$this->iterateCharacters("fAlSe", true);
		$this->iterateCharacters("$123.45", true);
		$this->iterateCharacters("-$123.45", true);
		$this->iterateCharacters("12.34%", true);
		$this->iterateCharacters("-12.34%", true);
		$this->iterateCharacters("'01234", true);
		$this->iterateCharacters("'  ", true);
		$this->iterateCharacters("Foo bar", true);
		$this->iterateCharacters("=====");
		$this->iterateCharacters("=1*2*3*4", true);
		$this->iterateCharacters("=1**2");
		$this->iterateCharacters("=**123");
		$this->iterateCharacters("=6+*3");
		$this->iterateCharacters("=)))");
		$this->iterateCharacters("=(3*)");
		$this->iterateCharacters("=(*3)");
		$this->iterateCharacters("=(((");
		$this->iterateCharacters("=\")");
		$this->iterateCharacters("=-123", true);
		$this->iterateCharacters("=--123");
		$this->iterateCharacters("=---123");
		$this->iterateCharacters("=-\"str\"");
		$this->iterateCharacters("=\"str\"&3", true);
		$this->iterateCharacters("=1<3", true);
		$this->iterateCharacters("=1<=3", true);
		$this->iterateCharacters("=1>3", true);
		$this->iterateCharacters("=1>=3", true);
		$this->iterateCharacters("=1==3", true);
		$this->iterateCharacters("=1!=3", true);
		$this->iterateCharacters("=>=3");
		$this->iterateCharacters("=<=3");
		$this->iterateCharacters("=!=3");
		$this->iterateCharacters("=MAX)");
		$this->iterateCharacters("=MAX(3,,)");
		$this->iterateCharacters("=MAX(\"a\",\"b\")");
		$this->iterateCharacters("=ZZ999");
		$this->iterateCharacters('=$ZZ999');
		$this->iterateCharacters('=ZZ$999');
		$this->iterateCharacters('=$ZZ$999');
		$this->iterateCharacters("=1+2", true);
		$this->iterateCharacters("=1-2", true);
		$this->iterateCharacters("=1*2", true);
		$this->iterateCharacters("=1/2", true);
		$this->iterateCharacters("=A1(1)");
		$this->iterateCharacters('=$A$1(1)');
		$this->assertTrue(true); // to satisfy PHPUnit
	}
}
?>