tablepress / classes / class-evaluate-legacy.php

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

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