i have requirement allow end users input formula spreadsheet. have array this:
$table = array( 1=>array( "id"=>1, "name"=>"regulating", "quantity"=>"[2]quantity+[3]value", "value"=>"[2]cost" ), ...) the first level array key same value id key in array.
a tabulated example follows:
id name quantity value 1 regulating [2]quantity+[3]value [2]cost 2 kerbs 3 6 3 bricks 9 7 4 sausages [3]cost 3 5 bamboo [4]quantity [7]cost 6 clams [4]quantity null 7 hardcore [3]quantity*0.5 12 8 beetles [6]quantity*[4]value [2]value the quantity , value keys represent formula reference [id] , either quantity, value or cost.
cost derived multiplying value , quantity.
i using:
preg_match_all("/\[(.*?)\]([a-z]*[a-z]*)/", $string, $matches, preg_set_order); which outputs array for[1][quantity]:
array ( [0] => array ( [0] => [2]quantity [1] => 2 [2] => quantity ) [1] => array ( [0] => [3]value [1] => 3 [2] => value ) ) iterating through table using similar to: $calcstring = $table[1]['quantity'];`
foreach ($matches $match) { $calcstring = str_replace($match[0], $table[$match[1]][$match[2]], $calcstring); } i can string calculated , using matheval class sum.
for example
[1]quantity = [2]quantity + [3]value [2]quantity = 3 [3]value = 7 // [1]quantity = 3 + 7 = 10 [1]value = [2]cost [2]cost = [2]quantity * [2]value // 3 * 6 = 18 basically variables in table refer other [id]key in same table.
but here issue
i need resolve references other parts of table (which may or may not formula) fill in blanks. outside comfort zone , appreciate advice (or better functional code) provides enlightenment on how might able achieve this.
thanks
a dangerously easy, , your-situation-specific well-performable solution!
<?php class solver { private // final output array $arr_evaled, // when cell gains final value, corresponding entry in following array gets marked being done! $arr_done; private $solving_iterations_count; public function solver($array) { $this->arr_done = array(); foreach($array $k => $arr) $this->arr_done[$k] = array('quantity' => false, 'value' => false); // firstly,expand of "[x]cost"s "([x]quantity*[x]value)"s! $this->arr_evaled = array_map( function($v){ return preg_replace('#\[(\d*?)\]cost#', '([$1]quantity*[$1]value)', $v); }, $array ); $this->solving_iterations_count = 0; $this->solve(); } private function isdone() { foreach($this->arr_done $a) if($a['quantity'] == false || $a['value'] == false) return false; return true; } private function iscelldone($id, $fieldname) { return $this->arr_done[$id][$fieldname]; } private function markcellasdone($id, $fieldname, $evaluation) { $this->arr_done[$id][$fieldname] = true; $this->arr_evaled[$id][$fieldname] = $evaluation; } private function isevaluable($str) { return preg_match('#^[0-9*+-\/\(\)\.]*$#', $str) == 1 || strtolower($str)=='null'; } private function replace($from, $to) { foreach($this->arr_evaled &$arr) { $arr['quantity'] = str_replace($from, $to, $arr['quantity']); $arr['value'] = str_replace($from, $to, $arr['value']); } } private function solve() { $issolvable = true; // todo: believe coding part fun!) (e.g: check "reference cycles") if(!$issolvable) return null; while( !$this->isdone() ) { foreach($this->arr_evaled $arr) { foreach(['quantity', 'value'] $fieldname) { if(!$this->iscelldone($arr['id'], $fieldname)) { if($this->isevaluable($arr[$fieldname])) { $evaluation = eval("return {$arr[$fieldname]};"); $this->markcellasdone($arr['id'], $fieldname, $evaluation); $this->replace("[{$arr['id']}]$fieldname", "$evaluation"); } } } } $this->solving_iterations_count++; } foreach($this->arr_evaled &$row) $row['cost'] = $row['quantity'] * $row['value']; return $this->arr_evaled; } public function print_tabulated() { echo "the count of solving iterations: {$this->solving_iterations_count}<br/><br/>"; echo '<table border="1"><tr><th>id</th><th>name</th><th>quantity</th><th>value</th><th>cost</th></tr>'; foreach($this->arr_evaled $arr) echo "<tr><td>{$arr['id']}</td><td>{$arr['name']}</td><td>{$arr['quantity']}</td><td>{$arr['value']}</td><td>{$arr['cost']}</td></tr>"; echo '</table>'; } } // testing $arr = array( 1 => array( 'id' => 1, 'name' => 'regulating', 'quantity' => '[2]quantity+[3]value', 'value' => '[2]cost' ), 2 => array( 'id' => 2, 'name' => 'kerbs', 'quantity' => '3', 'value' => '6' ), 3 => array( 'id' => 3, 'name' => 'bricks', 'quantity' => '9', 'value' => '7' ), 4 => array( 'id' => 4, 'name' => 'sausages', 'quantity' => '[3]cost', 'value' => '3' ), 5 => array( 'id' => 5, 'name' => 'bamboo', 'quantity' => '[4]quantity', 'value' => '[7]cost' ), 6 => array( 'id' => 6, 'name' => 'clams', 'quantity' => '[4]quantity', 'value' => 'null' ), 7 => array( 'id' => 7, 'name' => 'hardcore', 'quantity' => '[3]quantity*0.5', 'value' => '12' ), 8 => array( 'id' => 8, 'name' => 'beetles', 'quantity' => '[6]quantity*[4]value', 'value' => '[2]value' ), ); echo '<pre>'; (new solver($arr))->print_tabulated(); here output:
Comments
Post a Comment