tablepress / classes / class-evaluate.php

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

233 lines 7.3 KB

Raw Download Zip

1	<?php
2	/**
3	* TablePress Formula Evaluation 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 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 {
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	* @return array Table data with evaluated formulas.
68	*/
69	public function evaluate_table_data( array $table_data ) {
70	$this->table_data = $table_data;
71
72	$rows = count( $this->table_data );
73	$columns = count( $this->table_data[0] );
74	// 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.
75	for ( $row_idx = 0; $row_idx < $rows; $row_idx++ ) {
76	for ( $col_idx = 0; $col_idx < $columns; $col_idx++ ) {
77	$this->table_data[ $row_idx ][ $col_idx ] = $this->_evaluate_cell( $this->table_data[ $row_idx ][ $col_idx ] );
78	}
79	}
80
81	return $this->table_data;
82	}
83
84	/**
85	* Parse and evaluate the content of a cell.
86	*
87	* @since 1.0.0
88	*
89	* @param string $content Content of a cell.
90	* @param array $parents Optional. List of cells that depend on this cell (to prevent circle references).
91	* @return string Result of the parsing/evaluation.
92	*/
93	protected function _evaluate_cell( $content, array $parents = array() ) {
94	if ( '' === $content \|\| '=' === $content \|\| '=' !== $content[0] ) {
95	return $content;
96	}
97
98	// Cut off the leading =.
99	$content = substr( $content, 1 );
100
101	// Support putting formulas in strings, like =Total: {A3+A4}.
102	$expressions = array();
103	if ( preg_match_all( '#{(.+?)}#', $content, $expressions, PREG_SET_ORDER ) ) {
104	$formula_in_string = true;
105	} else {
106	$formula_in_string = false;
107	// Fill array so that it has the same structure as if it came from preg_match_all().
108	$expressions[] = array( $content, $content );
109	}
110
111	foreach ( $expressions as $expression ) {
112	$orig_expression = $expression[0];
113	$expression = $expression[1];
114
115	$replaced_references = $replaced_ranges = array();
116
117	// Remove all whitespace characters.
118	$expression = str_replace( array( "\n", "\r", "\t", ' ' ), '', $expression );
119
120	// Expand cell ranges (like A3:A6) to a list of single cells (like A3,A4,A5,A6).
121	if ( preg_match_all( '#([A-Z]+)([0-9]+):([A-Z]+)([0-9]+)#', $expression, $referenced_cell_ranges, PREG_SET_ORDER ) ) {
122	foreach ( $referenced_cell_ranges as $cell_range ) {
123	if ( in_array( $cell_range[0], $replaced_ranges, true ) ) {
124	continue;
125	}
126
127	$replaced_ranges[] = $cell_range[0];
128
129	if ( isset( $this->known_ranges[ $cell_range[0] ] ) ) {
130	$expression = preg_replace( '#(?<![A-Z])' . preg_quote( $cell_range[0], '#' ) . '(?![0-9])#', $this->known_ranges[ $cell_range[0] ], $expression );
131	continue;
132	}
133
134	// No -1 necessary for this transformation, as we don't actually access the table.
135	$first_col = TablePress::letter_to_number( $cell_range[1] );
136	$first_row = $cell_range[2];
137	$last_col = TablePress::letter_to_number( $cell_range[3] );
138	$last_row = $cell_range[4];
139
140	$col_start = min( $first_col, $last_col );
141	$col_end = max( $first_col, $last_col ) + 1; // +1 for loop below
142	$row_start = min( $first_row, $last_row );
143	$row_end = max( $first_row, $last_row ) + 1; // +1 for loop below
144
145	$cell_list = array();
146	for ( $col = $col_start; $col < $col_end; $col++ ) {
147	for ( $row = $row_start; $row < $row_end; $row++ ) {
148	$column = TablePress::number_to_letter( $col );
149	$cell_list[] = "{$column}{$row}";
150	}
151	}
152	$cell_list = implode( ',', $cell_list );
153
154	$expression = preg_replace( '#(?<![A-Z])' . preg_quote( $cell_range[0], '#' ) . '(?![0-9])#', $cell_list, $expression );
155	$this->known_ranges[ $cell_range[0] ] = $cell_list;
156	}
157	}
158
159	// Parse and evaluate single cell references (like A3 or XY312), while prohibiting circle references.
160	if ( preg_match_all( '#([A-Z]+)([0-9]+)#', $expression, $referenced_cells, PREG_SET_ORDER ) ) {
161	foreach ( $referenced_cells as $cell_reference ) {
162	if ( in_array( $cell_reference[0], $parents, true ) ) {
163	return '!ERROR! Circle Reference';
164	}
165
166	if ( in_array( $cell_reference[0], $replaced_references, true ) ) {
167	continue;
168	}
169
170	$replaced_references[] = $cell_reference[0];
171
172	$ref_col = TablePress::letter_to_number( $cell_reference[1] ) - 1;
173	$ref_row = $cell_reference[2] - 1;
174
175	if ( ! isset( $this->table_data[ $ref_row ] ) \|\| ! isset( $this->table_data[ $ref_row ][ $ref_col ] ) ) {
176	return "!ERROR! Cell {$cell_reference[0]} does not exist";
177	}
178
179	$ref_parents = $parents;
180	$ref_parents[] = $cell_reference[0];
181
182	$result = $this->table_data[ $ref_row ][ $ref_col ] = $this->_evaluate_cell( $this->table_data[ $ref_row ][ $ref_col ], $ref_parents );
183	// Bail if there was an error already.
184	if ( false !== strpos( $result, '!ERROR!' ) ) {
185	return $result;
186	}
187	// Remove all whitespace characters.
188	$result = str_replace( array( "\n", "\r", "\t", ' ' ), '', $result );
189	// Treat empty cells as 0.
190	if ( '' === $result ) {
191	$result = 0;
192	}
193	// Bail if the cell does not result in a number (meaning it was a number or expression before being evaluated).
194	if ( ! is_numeric( $result ) ) {
195	return "!ERROR! {$cell_reference[0]} does not contain a number or expression";
196	}
197
198	$expression = preg_replace( '#(?<![A-Z])' . $cell_reference[0] . '(?![0-9])#', $result, $expression );
199	}
200	}
201
202	$result = $this->_evaluate_math_expression( $expression );
203	// Support putting formulas in strings, like =Total: {A3+A4}.
204	if ( $formula_in_string ) {
205	$content = str_replace( $orig_expression, $result, $content );
206	} else {
207	$content = $result;
208	}
209	}
210
211	return $content;
212	}
213
214	/**
215	* Evaluate a math expression.
216	*
217	* @since 1.0.0
218	*
219	* @param string $expression without leading = sign.
220	* @return string Result of the evaluation.
221	*/
222	protected function _evaluate_math_expression( $expression ) {
223	// Straight up evaluation, without parsing of variable or function assignments (which is why we only need one instance of the object).
224	$result = $this->evalmath->evaluate( $expression );
225	if ( false === $result ) {
226	return '!ERROR! ' . $this->evalmath->last_error;
227	} else {
228	return (string) $result;
229	}
230	}
231
232	} // class TablePress_Evaluate
233