· 6 years ago · Jul 16, 2019, 07:24 AM
1<?php
2
3namespace T8LB\Spec\Designer\Reports;
4
5use Illuminate\Validation\ValidationException;
6use T8LB\Spec\Designer\Services\Reports\BlockReader;
7use T8LB\Spec\Designer\Services\Reports\DateCompute;
8
9/**
10 * Class Block
11 * @package T8LB\Spec\Designer\Reports
12 */
13class Block
14{
15
16 // TODO: Before giving filteredData, make sure only the finalColumns are sent back -- DONE
17 // TODO: Validate XML Files with filename and top level name attribute -- [Blocks don't have top level names]
18 // TODO: Fix bug of setFinalColumnNames failing when there are no titles -- DONE
19 // TODO: Disallow same names for columns -- DONE
20 // TODO: Compare block and brick columns. Block cannot have more columns than brick -- VERIFY
21 // TODO: Validate - If any column in the Block does not exist in the brick, its a fail -- DONE
22 // TODO: Validate - Check if Brick in Block's relations exist -- DONE
23
24 private $xml = [];
25 private $brickFilenames = [];
26 private $columns,
27 $blockName,
28
29 $visibleColumns,
30 $nonVisibleColumns,
31 $nameToTitleList,
32 $finalColumnNames,
33 $allBrickColumns,
34
35 $virtualColumnsWithInfo,
36 $computedVirtualColumns = [],
37
38 $finalOutput,
39 $leftColumn,
40 $rightColumn,
41 $rightBrick,
42 $leftBrick,
43 $join,
44 $path,
45 $extension;
46
47 /**
48 * Block constructor.
49 * @param $blockName
50 * @throws ValidationException
51 */
52 public function __construct($blockName)
53 {
54 $this->blockName = $blockName;
55
56 try {
57 $this->loadConfiguration($this->blockName);
58 } catch (ValidationException $e) {
59 throw $e;
60 }
61
62 try {
63 $this->validateConfiguration();
64 } catch (ValidationException $e) {
65 throw $e;
66 }
67
68// $dateCompute = new DateCompute();
69//
70// pr($dateCompute->compute("currentmonth(1)"), 1);
71 }
72
73 /**
74 * @param $blockName
75 * @throws ValidationException
76 */
77 private function loadConfiguration($blockName)
78 {
79 # Define Path for Accessing XML
80 $this->path = storage_path('app/reports/');
81
82 # Define Extension for the File
83 $this->extension = '.xml';
84
85 # Read the Given File if XML
86 $data = new BlockReader($this->path . $blockName . $this->extension);
87
88 try {
89 $this->xml = $data->getStructuredData();
90 } catch (ValidationException $e) {
91 throw $e;
92 }
93
94 # Get Bricks Info
95 $bricks = $this->xml['Block']['Bricks'];
96
97 # Relations
98 foreach ($this->xml['Block']['Relations'] as $key => $relation) {
99 $this->leftColumn[$key] = $relation['RelationColumn']['attributes']['BrickLeft'];
100 $this->rightColumn[$key] = $relation['RelationColumn']['attributes']['BrickRight'];
101 $this->rightBrick[$key] = $relation['attributes']['BrickRight'];
102 $this->leftBrick[$key] = $relation['attributes']['BrickLeft'];
103 $this->join[$key] = $relation['attributes']['Type'];
104 }
105
106 # Get Bricks File Names from Block XML
107 foreach ($bricks as $brick)
108 $this->brickFilenames[] = $brick['attributes']['Name'];
109
110 $this->populateColumns($this->xml['Block']['Columns']);
111
112 # Set properties like visibility and titles (instead of names)
113 $this->setColumnProperties();
114
115 # Final columns that will be used for output
116 $this->setFinalColumnNames();
117 }
118
119 /**
120 * @throws ValidationException
121 */
122 private function validateConfiguration()
123 {
124 $errorList = [];
125
126 # Check if names of any column in the block is empty
127 foreach ($this->columns as $column) {
128 if ($column->getName() == "") {
129 $errorList[] = "Name attribute for column does not exist in Block - " . $this->blockName;
130 }
131 }
132
133 # Check if the columns given in block exists in the associated bricks
134 try {
135 $checkColumnsExistInBrick = $this->checkColumnsExistInBrick();
136 } catch (ValidationException $e) {
137 throw $e;
138 }
139
140 if ($checkColumnsExistInBrick !== '') {
141 $errorList[] = $checkColumnsExistInBrick;
142 }
143
144 if (count($errorList) > 0) {
145 throw ValidationException::withMessages($errorList);
146 }
147 }
148
149 /**
150 * @return string
151 * @throws ValidationException
152 */
153 private function checkColumnsExistInBrick()
154 {
155 $brickColumnArrays = [];
156 foreach ($this->brickFilenames as $brickFilename) {
157 try {
158 $brick = new Brick($brickFilename);
159 } catch (ValidationException $e) {
160 throw $e;
161 }
162
163 $brickColumnArrays[] = $brick->getColumns();
164 }
165
166 # Merge columns from different bricks into one
167 $this->allBrickColumns = [];
168 foreach ($brickColumnArrays as $columnArray) {
169 foreach ($columnArray as $column) {
170 $this->allBrickColumns[] = $column;
171 }
172 }
173
174 # Check if the block column exists in the brick
175 foreach ($this->columns as $blockColumn) {
176 // Don't check if it is a virtual column
177 if ($blockColumn->getIsVirtual() == true) {
178 continue;
179 }
180
181 $found = false;
182 foreach ($this->allBrickColumns as $brickColumn) {
183 if ($blockColumn->getName() === $brickColumn->getName()) {
184 # Found column
185 $found = true;
186 break;
187 }
188 }
189
190 if (!$found) {
191 return "The column '" . $blockColumn->getName() . "' was not found in bricks";
192 }
193 }
194
195 return '';
196 }
197
198 public function getData($requiredColumns, $displayFormat, $allColumns = false)
199 {
200 if (count($requiredColumns) == 0) {
201 throw ValidationException::withMessages(["Minimum one column is required."]);
202 }
203
204 $bricks = [];
205 $result = [];
206
207 # Get Columns and Source Data from XML
208 foreach ($this->brickFilenames as $brickFilename) {
209 try {
210 $brick = new Brick($brickFilename);
211 } catch (ValidationException $e) {
212 throw $e;
213 }
214
215 $brickInfo = $brick->getSQL();
216 $bricks[$brickFilename] = [
217 'sqlData' => $brickInfo['sql'],
218 'columns' => $brickInfo['columns']
219 ];
220 }
221
222 # Execute the SQL
223 foreach ($bricks as $key => $value)
224 $result[$key] = $value['sqlData'];
225
226 # Get individual column names
227 foreach ($bricks as $key => $value)
228 $columns[$key] = $value['columns'];
229
230 # Merge All Relations One by One
231 $output = $result[$this->leftBrick[0]];
232 foreach ($this->xml['Block']['Relations'] as $key => $relation) {
233 $alias = range('A', 'Z');
234 # Right Brick Data Collection
235 $rightBrick = ($result[$this->rightBrick[$key]]);
236
237 # Get Left & Right Keys
238 $left = $this->leftColumn[$key];
239 $right = $this->rightColumn[$key];
240 # Get Join Type
241 $joinType = $this->join[$key];
242
243 # Get Left Brick Keys
244 foreach ($columns[$this->leftBrick[$key]] as $column1)
245 $setLeftColumns[$key][] = $column1->Name;
246
247 # Alias Left Brick Keys
248 foreach ($setLeftColumns[$key] as $column1Key => $column1)
249 $setLeftColumns[$key][$column1Key] = $column1 . ' AS ' . $alias[$key] . $alias[$key] . '_' . $column1;
250
251 # Get Right Brick Keys
252 foreach ($columns[$this->rightBrick[$key]] as $column2)
253 $setRightColumns[$key][] = $column2->Name;
254
255 # Alias Right Brick Keys
256 foreach ($setRightColumns[$key] as $column1Key => $column1) {
257 $setRightColumns[$key][$column1Key] = $column1 . ' AS ' . $alias[$key + 1] . $alias[$key + 1] . '_' . $column1;
258 $aliasedRightKeys[$key][$column1] = $alias[$key + 1] . $alias[$key + 1] . '_' . $column1;
259 }
260
261
262 # Get Aliased Right Key
263 $newRightKey = $alias[$key + 1] . $alias[$key + 1] . '_' . $right;
264
265 # Get Aliased Right Columns
266 $rightColumns = implode(',', $setRightColumns[$key]);
267
268
269 # Check if Right DataSet is Empty & Generate Join SQL Statement
270 if (!empty($rightBrick))
271 $output = $this->join($output, $rightBrick, $left, $joinType, $rightColumns, $alias[$key], $alias[$key + 1], $newRightKey);
272 }
273
274 # Get Final Columns
275 foreach ($this->columns as $column) {
276 if ($column->getIsVirtual() != true) {
277 $finalColumnSet[] = $column->Name;
278 }
279 }
280
281
282 # Replace Original Keys with Aliased Keys
283 foreach ($aliasedRightKeys as $formatted) {
284 foreach ($formatted as $key => $format) {
285 $exist = array_search($key, $finalColumnSet);
286 if ($exist >= 0) {
287 $keyToBeReplaced = array_search($key, $finalColumnSet);
288 $finalColumnSet[$keyToBeReplaced] = $format;
289 }
290 }
291 }
292
293 # Get 'Select Column' List for the Final Query
294 $finalColumns = implode(',', $finalColumnSet);
295 $sql = "SELECT " . $finalColumns . " FROM (" . $output . ") AS FinalQuery";
296
297
298 # Load the SQL to the Brick and get SQL executed
299 try {
300 $finalBrick = new Brick(null, $sql);
301 } catch (ValidationException $e) {
302
303 }
304 $dataFromBrick = $finalBrick->getData();
305
306 # Get Executed SQL result
307 $sqlResult = $dataFromBrick['sql'];
308
309 # Replace Alias Keys with Original Keys
310 foreach ($sqlResult as $result) {
311 $finalColumnSet = array_keys((array)$result);
312 foreach ($aliasedRightKeys as $aliasedKey) {
313 foreach ($aliasedKey as $originalKey => $alias) {
314 $exist = array_search($alias, $finalColumnSet);
315 if (!empty($exist) && $exist >= 0) {
316 if (isset($result->$alias)) $result->{$originalKey} = $result->$alias;
317 unset($result->$alias);
318 }
319 }
320 }
321 }
322
323 $dataAfterVirtualColumns = $this->computeVirtualColumns($sqlResult);
324
325 # Final Output
326 $resultSet = $this->filterData($dataAfterVirtualColumns, $requiredColumns, $allColumns);
327
328 # Format Result Set Based on the Request table format by default and JSON is requested.
329 if ($displayFormat !== 'json') {
330 $requiredColumns = (count($requiredColumns) > 0) ? $requiredColumns : $this->finalColumnNames;
331 $this->finalOutput = $this->toTable($requiredColumns, $resultSet);
332 } else
333 $this->finalOutput = $resultSet;
334
335 # Return Final result
336 return $this->finalOutput;
337 }
338
339 private $tmpComputationOperators = [];
340 private function computeVirtualColumns($data) {
341 // Start with $data then start modifying (adding new keys)
342 $modifiedData = $data;
343
344 // Compute object
345 $compute = new DateCompute();
346
347 $x = 0;
348 // Loop through each object in the dataset
349 foreach ($modifiedData as $datum) {
350 // Loop through the virtual columns
351 foreach ($this->virtualColumnsWithInfo as $columnName => $formula) {
352 if (!is_string($formula)) {
353 throw ValidationException::withMessages(['The given formula is invalid']);
354 }
355
356 // rtrim() because of the space created by the getDerivedFormula... function
357 $formulaWithoutOperators = rtrim($this->getDerivedFormulaWithoutOperators($formula, $datum));
358
359 $formulaWithoutOperatorsTerms = explode(" ", $formulaWithoutOperators);
360
361 // Get the without column names formula as a main term (does not consider column names in parameters)
362 $i = 0;
363 $computableFormula = "";
364 foreach ($formulaWithoutOperatorsTerms as $term) {
365 if ($i == count($this->tmpComputationOperators)) {
366 // If its the last term, just concatenate the term and break out of the loop
367 $computableFormula .= $term;
368 break;
369 }
370
371 $computableFormula .= $term . " " . $this->tmpComputationOperators[$i] . " ";
372 $i++;
373 }
374
375 foreach ($this->finalColumnNames as $finalColumnName) {
376 if (strpos($computableFormula, $finalColumnName) !== false) {
377 if (!array_key_exists($finalColumnName, $this->virtualColumnsWithInfo) && !empty($datum->$finalColumnName)) {
378 // Not a virtual column, so just get the value from data and replace
379 $formulaWithoutOperatorsTerms[] = $datum->$finalColumnName;
380 } else if (array_key_exists($finalColumnName, $this->virtualColumnsWithInfo)) {
381 // Column is a virtual column, calculate that value for that
382
383 } else {
384 // No value exists for the column
385 }
386 }
387 }
388
389 $this->tmpComputationOperators = [];
390
391 $performComputation = $compute->compute($computableFormula);
392 $modifiedData[$x]->$columnName = $performComputation;
393 }
394
395 $x++;
396 }
397
398 return $modifiedData;
399 }
400
401 private function getDerivedFormulaWithoutOperators($formula, $datumToModify) {
402 // Get every term into an array
403 $terms = explode(' ', $formula);
404
405 $termsWithoutOperators = array_filter($terms, function($value){
406 $arr = ['+', '-', '*', '/', '%'];
407
408 if (!in_array($value, $arr)) {
409 return $value;
410 } else {
411 $this->tmpComputationOperators[] = $value;
412 }
413 });
414
415 $formulaWithoutOperators = "";
416 // Loop through each term and check if it is a column
417 foreach ($termsWithoutOperators as $term) {
418 if (in_array($term, $this->finalColumnNames)) {
419 // The term is a column
420 if (!array_key_exists($term, $this->virtualColumnsWithInfo)) {
421 // Term is not a virtual column
422 $termValue = $datumToModify->$term;
423 $formulaWithoutOperators .= $termValue . " ";
424 } else {
425 // Term is a virtual column
426 if (!array_key_exists($term, $this->computedVirtualColumns)) {
427 // Term not yet computed
428 $formulaWithoutOperators .= $this->getDerivedFormulaWithoutOperators($this->virtualColumnsWithInfo[$term], $datumToModify);
429// $this->computedVirtualColumns[] = $term;
430 } else {
431 // Term already computed
432 }
433 }
434 } else {
435 $formulaWithoutOperators .= $term . " ";
436 }
437 }
438
439 return $formulaWithoutOperators;
440 }
441
442 /**
443 * @param $input
444 * @return array
445 */
446 private function getKeys($input)
447 {
448 $keys = [];
449 foreach ($input as $object)
450 foreach ($object as $key => $val)
451 if (!in_array($key, $keys)) $keys[] = $key;
452
453 return $keys;
454 }
455
456 /**
457 * @param $leftBrickData
458 * @param $rightBrickData
459 * @param $leftKey
460 * @param $rightKey
461 * @param $joinType
462 * @return array
463 */
464 private function join($leftBrickSQL, $rightBrickSQL, $leftKey, $joinType, $rightColumns, $alias1, $alias2, $newRightKey)
465 {
466 return "SELECT * FROM ({$leftBrickSQL}) AS {$alias1} {$joinType} JOIN ( SELECT $rightColumns FROM ( {$rightBrickSQL}) AS {$alias2}) AS {$alias2} ON {$alias2}.{$newRightKey} = {$alias1}.{$leftKey}";
467
468 }
469
470 /**
471 * @param $columnData
472 */
473 private function populateColumns($columnData)
474 {
475 foreach ($columnData as $column) {
476 $attributes = $column['attributes'];
477 $column = new Column();
478 $properties = get_object_vars($column);
479
480 # Populate column object with values from attributes array
481 foreach ($properties as $property => $value) {
482 if (array_key_exists($property, $attributes)) {
483 $column->$property = isset($attributes[$property]) ? $attributes[$property] : NULL;
484 }
485 }
486
487 $this->columns[] = $column;
488 }
489 }
490
491 /**
492 * @param $columnNames
493 * @param $data
494 * @return array
495 */
496 public function toTable($columnNames, $data)
497 {
498 $output = [];
499
500 $columns = [];
501 foreach ($columnNames as $name) {
502 $columns[] = $name;
503 }
504
505 # Add columns key to the output
506 $output['columns'] = $columns;
507
508 # Create data key to hold the row values
509 $output['data'] = [];
510
511 # Populate rows
512 $i = 0;
513 foreach ($data as $values) {
514 $row = [];
515 foreach ($columnNames as $column) {
516 if (array_key_exists($column, $values)) {
517 $row[] = $values->{$column};
518 } else {
519 $row[] = '';
520 }
521 }
522
523 $output['data'][] = $row;
524
525 $i++;
526 }
527
528 return $output;
529 }
530
531 /**
532 *
533 */
534 private function setColumnProperties()
535 {
536 $count = count($this->columns);
537
538 if ($count === 0) {
539 throw ValidationException::withMessages(["Empty Columns"]);
540 }
541
542 foreach ($this->columns as $column) {
543 if (!empty($column->getName())) {
544 $nameAttr = $column->getName();
545
546 if ($column->getVisible() == 0) {
547 $this->nonVisibleColumns[] = $nameAttr;
548 } else {
549 $this->visibleColumns[] = $nameAttr;
550 }
551
552 if ($column->getIsVirtual() == true) {
553 $this->virtualColumnsWithInfo[$nameAttr] = $column->getComputedAs();
554 }
555
556 if (!empty($column->getTitle())) {
557 $this->nameToTitleList[$nameAttr] = $column->getTitle();
558 }
559 }
560 }
561 }
562
563 /**
564 *
565 */
566 private function setFinalColumnNames()
567 {
568 if (!$this->nameToTitleList) {
569 # No titles in any column
570 $this->nameToTitleList = [];
571 }
572
573 $tmpColumnsList = array_merge($this->visibleColumns, array_values($this->nameToTitleList));
574
575 foreach ($this->nameToTitleList as $name => $title) {
576 if (($key = array_search($name, $this->visibleColumns)) !== false) {
577 unset($tmpColumnsList[$key]);
578 }
579 }
580
581 $this->finalColumnNames = $tmpColumnsList;
582 }
583
584 /**
585 * @param $data
586 * @param array $requiredColumns
587 * @param bool $allColumns
588 * @return mixed
589 */
590 private function filterData($data, $requiredColumns = [], $allColumns = false)
591 {
592 # Start with original data then start filtering
593 $filteredData = $data;
594
595 $i = 0;
596 foreach ($filteredData as $columns) {
597 $columns = (array)$columns;
598
599 # Rename names to titles if present
600 foreach ($this->nameToTitleList as $name => $title) {
601 if ($this->checkKeyValid($name, $columns)) {
602 $filteredData[$i]->{$title} = $filteredData[$i]->{$name};
603 unset($filteredData[$i]->{$name});
604 }
605 }
606
607 if (!$allColumns) {
608 # Remove non visible columns
609 foreach ($columns as $property => $value) {
610 if (!in_array($property, $this->finalColumnNames)) {
611 unset($filteredData[$i]->$property);
612 }
613 }
614 }
615 if (count($requiredColumns) > 0) {
616 foreach ($filteredData as $val) {
617 foreach ($val as $property => $value) {
618 if (!in_array($property, $requiredColumns)) {
619 if (!in_array($property, array_keys($this->virtualColumnsWithInfo))) {
620 unset($filteredData[$i]->$property);
621 }
622 }
623 }
624 }
625 }
626
627 $i++;
628 }
629
630 return $filteredData;
631 }
632
633 /**
634 * @param $key
635 * @param $array
636 * @return bool
637 */
638 private function checkKeyValid($key, $array)
639 {
640 if (array_key_exists($key, $array) && !empty($array[$key])) {
641 return true;
642 }
643
644 return false;
645 }
646
647 private function getColumnTotal($data, $columnName) {
648 $sum = 0;
649
650 foreach ($data as $datum) {
651 $sum += $datum->{$columnName};
652 }
653
654 return $sum;
655 }
656}