tablepress / classes / class-evaluate-legacy.php

class-evaluate-legacy.php in TablePress – Tables in WordPress made easy 3.0, at classes/class-evaluate-legacy.php

255 lines 8.6 KB

Raw Download Zip

1	<?php
2	/**
3	* TablePress Formula Evaluation Legacy Class
4	*
5	* @package TablePress
6	* @subpackage Formulas
7	* @author Tobias Bäthge
8	* @since 1.5.0
9	*/
10
11	// Prohibit direct script loading.
12	defined( 'ABSPATH' ) \|\| die( 'No direct script access allowed!' );
13
14	/**
15	* TablePress Formula Evaluation Legacy Class
16	*
17	* Before TablePress 1.5, this was part of the TablePress_Render class.
18	*
19	* @package TablePress
20	* @subpackage Formulas
21	* @author Tobias Bäthge
22	* @since 1.5.0
23	*/
24	class TablePress_Evaluate_Legacy {
25
26	/**
27	* Instance of the EvalMath class.
28	*
29	* @since 1.0.0
30	*/
31	protected \EvalMath $evalmath;
32
33	/**
34	* Table data in which formulas shall be evaluated.
35	*
36	* @since 1.5.0
37	* @var array<int, array<int, string>>
38	*/
39	protected array $table_data;
40
41	/**
42	* Storage for cell ranges that have been replaced in formulas.
43	*
44	* @since 1.0.0
45	* @var array<string, string>
46	*/
47	protected array $known_ranges = array();
48
49	/**
50	* Initialize the Formula Evaluation class, include the EvalMath class.
51	*
52	* @since 1.0.0
53	*/
54	public function __construct() {
55	$this->evalmath = TablePress::load_class( 'EvalMath', 'evalmath.class.php', 'libraries' );
56	// Don't raise PHP warnings.
57	$this->evalmath->suppress_errors = true;
58	}
59
60	/**
61	* Evaluate formulas in the passed table.
62	*
63	* @since 1.0.0
64	*
65	* @param array<int, array<int, string>> $table_data Table data in which formulas shall be evaluated.
66	* @param string $table_id ID of the passed table.
67	* @return array<int, array<int, string>> Table data with evaluated formulas.
68	*/
69	public function evaluate_table_data( array $table_data, string $table_id ): array {
70	$this->table_data = $table_data;
71
72	$num_rows = count( $this->table_data );
73	// Exit early if there's no actual table data (e.g. after using the Row Filter module).
74	if ( 0 === $num_rows ) {
75	return $this->table_data;
76	}
77
78	$num_columns = count( $this->table_data[0] );
79
80	// Make fixed table data available as variables in formulas.
81	$this->evalmath->variables['table_id'] = $table_id;
82	$this->evalmath->variables['num_rows'] = $num_rows;
83	$this->evalmath->variables['num_columns'] = $num_columns;
84
85	// Use two for-loops instead of foreach here to be sure to always work on the "live" table data and not some in-memory copy.
86	for ( $row_idx = 0; $row_idx < $num_rows; $row_idx++ ) {
87	for ( $col_idx = 0; $col_idx < $num_columns; $col_idx++ ) {
88	$this->table_data[ $row_idx ][ $col_idx ] = $this->_evaluate_cell( $this->table_data[ $row_idx ][ $col_idx ], $row_idx, $col_idx );
89	}
90	}
91
92	return $this->table_data;
93	}
94
95	/**
96	* Parse and evaluate the content of a cell.
97	*
98	* @since 1.0.0
99	*
100	* @param string $content Content of a cell.
101	* @param int $row_idx Row index of the cell.
102	* @param int $col_idx Column index of the cell.
103	* @param string[] $parents Optional. List of cells that depend on this cell (to prevent circle references).
104	* @return string Result of the parsing/evaluation.
105	*/
106	protected function _evaluate_cell( string $content, int $row_idx, int $col_idx, array $parents = array() ): string {
107	if ( '' === $content \|\| '=' === $content \|\| '=' !== $content[0] ) {
108	return $content;
109	}
110
111	// Cut off the leading =.
112	$content = substr( $content, 1 );
113
114	// Support putting formulas in strings, like =Total: {A3+A4}.
115	$expressions = array();
116	if ( preg_match_all( '#{(.+?)}#', $content, $expressions, PREG_SET_ORDER ) ) {
117	$formula_in_string = true;
118	} else {
119	$formula_in_string = false;
120	// Fill array so that it has the same structure as if it came from preg_match_all().
121	$expressions[] = array( $content, $content );
122	}
123
124	foreach ( $expressions as $expression ) {
125	$orig_expression = $expression[0];
126	$expression = $expression[1];
127
128	$replaced_references = array();
129	$replaced_ranges = array();
130
131	// Remove all whitespace characters.
132	$expression = str_replace( array( "\n", "\r", "\t", ' ' ), '', $expression );
133
134	// Expand cell ranges (like A3:A6) to a list of single cells (like A3,A4,A5,A6).
135	if ( preg_match_all( '#([A-Z]+)([0-9]+):([A-Z]+)([0-9]+)#', $expression, $referenced_cell_ranges, PREG_SET_ORDER ) ) {
136	foreach ( $referenced_cell_ranges as $cell_range ) {
137	if ( in_array( $cell_range[0], $replaced_ranges, true ) ) {
138	continue;
139	}
140
141	$replaced_ranges[] = $cell_range[0];
142
143	if ( isset( $this->known_ranges[ $cell_range[0] ] ) ) {
144	$expression = (string) preg_replace( '#(?<![A-Z])' . preg_quote( $cell_range[0], '#' ) . '(?![0-9])#', $this->known_ranges[ $cell_range[0] ], $expression );
145	continue;
146	}
147
148	// No -1 necessary for this transformation, as we don't actually access the table.
149	$first_col = TablePress::letter_to_number( $cell_range[1] );
150	$first_row = (int) $cell_range[2];
151	$last_col = TablePress::letter_to_number( $cell_range[3] );
152	$last_row = (int) $cell_range[4];
153
154	$col_start = min( $first_col, $last_col );
155	$col_end = max( $first_col, $last_col ) + 1; // +1 for loop below
156	$row_start = min( $first_row, $last_row );
157	$row_end = max( $first_row, $last_row ) + 1; // +1 for loop below
158
159	$cell_list = array();
160	for ( $col = $col_start; $col < $col_end; $col++ ) {
161	for ( $row = $row_start; $row < $row_end; $row++ ) {
162	$column = TablePress::number_to_letter( $col );
163	$cell_list[] = "{$column}{$row}";
164	}
165	}
166	$cell_list = implode( ',', $cell_list );
167
168	$expression = (string) preg_replace( '#(?<![A-Z])' . preg_quote( $cell_range[0], '#' ) . '(?![0-9])#', $cell_list, $expression );
169	$this->known_ranges[ $cell_range[0] ] = $cell_list;
170	}
171	}
172
173	// Parse and evaluate single cell references (like A3 or XY312), while prohibiting circle references.
174	if ( preg_match_all( '#([A-Z]+)([0-9]+)(?![0-9A-Z\(])#', $expression, $referenced_cells, PREG_SET_ORDER ) ) {
175	foreach ( $referenced_cells as $cell_reference ) {
176	if ( in_array( $cell_reference[0], $parents, true ) ) {
177	return '!ERROR! Circle Reference';
178	}
179
180	if ( in_array( $cell_reference[0], $replaced_references, true ) ) {
181	continue;
182	}
183
184	$replaced_references[] = $cell_reference[0];
185
186	$ref_col = TablePress::letter_to_number( $cell_reference[1] ) - 1;
187	$ref_row = (int) $cell_reference[2] - 1;
188
189	if ( ! isset( $this->table_data[ $ref_row ][ $ref_col ] ) ) {
190	return "!ERROR! Cell {$cell_reference[0]} does not exist";
191	}
192
193	$ref_parents = $parents;
194	$ref_parents[] = $cell_reference[0];
195
196	$result = $this->_evaluate_cell( $this->table_data[ $ref_row ][ $ref_col ], $ref_row, $ref_col, $ref_parents );
197	$this->table_data[ $ref_row ][ $ref_col ] = $result;
198	// Bail if there was an error already.
199	if ( str_contains( $result, '!ERROR!' ) ) {
200	return $result;
201	}
202	// Remove all whitespace characters.
203	$result = str_replace( array( "\n", "\r", "\t", ' ' ), '', $result );
204	// Treat empty cells as 0.
205	if ( '' === $result ) {
206	$result = '0';
207	}
208	// Bail if the cell does not result in a number (meaning it was a number or expression before being evaluated).
209	if ( ! is_numeric( $result ) ) {
210	return "!ERROR! {$cell_reference[0]} does not contain a number or expression";
211	}
212
213	$expression = (string) preg_replace( '#(?<![A-Z])' . $cell_reference[0] . '(?![0-9])#', $result, $expression );
214	}
215	}
216
217	$result = $this->_evaluate_math_expression( $expression, $row_idx, $col_idx );
218	// Support putting formulas in strings, like =Total: {A3+A4}.
219	if ( $formula_in_string ) {
220	$content = str_replace( $orig_expression, $result, $content );
221	} else {
222	$content = $result;
223	}
224	}
225
226	return $content;
227	}
228
229	/**
230	* Evaluate a math expression.
231	*
232	* @since 1.0.0
233	*
234	* @param string $expression Math expression without leading = sign.
235	* @param int $row_idx Row index of the cell with the expression.
236	* @param int $col_idx Column index of the cell with the expression.
237	* @return string Result of the evaluation.
238	*/
239	protected function _evaluate_math_expression( string $expression, int $row_idx, int $col_idx ): string {
240	// Make current cell's name and row and column number available as variables in formulas.
241	$this->evalmath->variables['row'] = $row_idx + 1;
242	$this->evalmath->variables['column'] = $col_idx + 1;
243	$this->evalmath->variables['cell'] = TablePress::number_to_letter( $this->evalmath->variables['column'] ) . $this->evalmath->variables['row'];
244
245	// Straight up evaluation, without parsing of variable or function assignments (which is why we only need one instance of the object).
246	$result = $this->evalmath->evaluate( $expression );
247	if ( false === $result ) {
248	$result = '!ERROR! ' . $this->evalmath->last_error;
249	}
250
251	return (string) $result;
252	}
253
254	} // class TablePress_Evaluate_Legacy
255