Query::__construct() - Code Metrics - Inspection of "Version 0.2.0 2017-04-17" - eclipxe13/engineworks-pivot - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 601eb8...5169a0 )

by Carlos C

created 2017-04-17 20:07 UTC

Query::__construct() A

↳ Parent: Query

Complexity

Conditions	1
Paths	1

Size

Total Lines	5
Code Lines	3

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	4
CRAP Score	1

Importance

Changes

Metric	Value
dl	0
loc	5
ccs	4
cts	4
cp	1
rs	9.4285
c	0
b	0
f	0
cc	1
eloc	3
nc	1
nop	2
crap	1

<?php
declare(strict_types = 1);
namespace EngineWorks\Pivot;

use EngineWorks\DBAL\DBAL;

class Query
{
    /**
     * Database Abstraction Layer Object
     * @var DBAL
     */
    private $db;

    /**
     * @var Pivot
     */
    private $pivot;

    /**
     * Query constructor.
     * @param DBAL $db
     * @param Pivot $pivot
     */
    public function __construct(DBAL $db, Pivot $pivot)
    {
        $this->db = $db;
        $this->pivot = $pivot;
    }

    /**
     * @return DBAL
     */
    public function getDb(): DBAL
    {
        return $this->db;
    }

    /**
     * @return Pivot
     */
    public function getPivot(): Pivot
    {
        return $this->pivot;
    }

    /**
     * Perform a query to obtain the distinct values of a fieldname
     *
     * @param string $fieldname
     * @return array
     * @throws PivotException if the fieldname does not exists in the source fields
     */
    public function queryDistinct(string $fieldname) : array
    {
        if (! $this->pivot->getFieldsCollection()->exists($fieldname)) {
            throw new PivotException("Field $fieldname does not exists in the source fields");
        }
        $sql = 'SELECT DISTINCT ' . $this->db->sqlFieldEscape($fieldname)
            . ' FROM ' . $this->sqlFrom()
            . ' ORDER BY ' . $this->db->sqlFieldEscape($fieldname)
            . ';';
        return $this->db->queryArrayOne($sql) ? : [];
    }

    /**
     * @return QueryResult
     */
    public function query() : QueryResult
    {
        return new QueryResult(
            $this->queryTotalsResult(true),
            ($this->pivot->hasColumns()) ? $this->queryDetailsResult() : null,
            $this->pivot->getCurrentRows(),
            $this->pivot->getCurrentColumns(),
            $this->pivot->getCurrentAggregates()
        );
    }

    /**
     * @return array
     * @throws PivotException
     * @throws PivotExceptionQuery
     */
    private function queryData() : array
    {
        $sqlSelects = [];
        // SQL: Select group fields
        foreach ($this->pivot->getCurrentRows() as $currentRow) {
            $sqlSelects[] = $this->db->sqlFieldEscape($currentRow['fieldname']);
        }
        // SQL: Select aggregator fields
        $sqlAggregates = [];
        foreach ($this->pivot->getAggregatesCollection() as $aggregate) {
            $sqlAggregates[] = $aggregate->getSQL($this->db);
        }
        // SQL: Select group fields and aggregates
        $sqlAllFields = array_merge($sqlSelects, $sqlAggregates);
        $countFields = count($sqlAllFields);
        if (! $countFields) { // nothing to select
            throw new PivotException('Nothing to query');
        }

        // get all elements from filter
        $sqlWheres = [];
        foreach ($this->pivot->getFiltersCollection() as $filter) {
            $sqlWheres[] = '('
                . $filter->getSQL($this->db, $this->pivot->getFieldElement($filter->getFieldname())->toDBAL())
                . ')';
        }
        // SQL Creation
        $sql = 'SELECT ' . implode(', ', $sqlAllFields)
            . ' FROM ' . $this->sqlFrom()
            . ((count($sqlWheres)) ? ' WHERE ' . implode(' AND ', $sqlWheres) : '')
            . ((count($sqlSelects)) ? ' GROUP BY ' . implode(', ', $sqlSelects) . ' WITH ROLLUP' : '')
            . ';';

        // retrieve and return the data
        try {
            $data = $this->db->queryArrayValues($sql);
        } catch (\Throwable $ex) {
            throw new PivotExceptionQuery('Query error: ' . $ex->getMessage());
        }
        if (! is_array($data)) {
            throw new PivotExceptionQuery('Query error:' . $sql);
        }
        return $data;
    }

    /**
     * @param bool $sortValues
     * @return Result
     * @throws PivotException
     */
    private function queryTotalsResult(bool $sortValues) : Result
    {
        // variable to return
        $result = new Result('', '');

        // now all the data including rollup cells are in $data
        // we have to generate the output
        $data = $this->queryData();
        $dataCount = count($data);
        $aggregates = $this->pivot->getAggregatesCollection();
        for ($i = 0; $i < $dataCount; $i++) {
            // create the values content
            $values = [];
            foreach ($aggregates as $aggregate) {
                $aggregateName = $aggregate->getAsname();
                $values[$aggregateName] = $data[$i][$aggregateName];
            }
            // ahora especificar donde va el resultado
            $ch = $result;
            foreach ($this->pivot->getCurrentRows() as $row) {
                $rowvalue = $data[$i][$row['fieldname']];
                if (is_null($rowvalue)) {
                    break;
                } else {
                    if (! $ch->children->exists($rowvalue)) {
                        // insert without value ($row is the $fieldname and $rowvalue is the $caption)
                        $ch->children->addItem(new Result($row['fieldname'], $rowvalue), $rowvalue);
                    }
                    $ch = $ch->children->value($rowvalue);
                }
            }
            // asignar los valores al nodo seleccionado
            if (! is_null($ch->values)) {
                throw new PivotException('Duplicated values when filling the results');
            }
            $ch->values = $values;
        }
        // do sort
        if ($sortValues) {
            $ordering = new ResultOrdering($aggregates->getOrderArray());
            if ($ordering->isRequired()) {
                $result->orderBy($ordering);
            }
        }
        // format aggregate values after sort
        $this->formatResultValues($aggregates, $result);
        return $result;
    }

    private function formatResultValues(Aggregates $aggregates, Result $result)
    {
        foreach ($aggregates as $aggregatorName => $aggregate) {
            if (null !== $value = $result->getCurrentValue($aggregatorName)) {
                $result->setCurrentValue($aggregatorName, number_format($value, $aggregate->getDecimals()));
            }
        }
        foreach ($result->children as $child) {
            $this->formatResultValues($aggregates, $child);
        }
    }

    /**
     * @return Result Null if no columns are set
     * @throws PivotException If no columns are defined
     */
    private function queryDetailsResult() : Result
    {
        $columns = $this->pivot->getCurrentColumns();
        if (! count($columns)) {
            throw new PivotException('Cannot get details if no columns are defined');
        }
        $pivotDetails = clone $this->pivot;
        $pivotDetails->clearSelectorRows();
        $pivotDetails->clearSelectorColumns();
        foreach ($columns as $column) {
            $pivotDetails->addRow($column['fieldname']);
        }
        foreach ($this->pivot->getCurrentRows() as $row) {
            $pivotDetails->addRow($row['fieldname']);
        }
        $queryClone = new self($this->db, $pivotDetails);
        $result = $queryClone->queryTotalsResult(false);
        $result->setAsNotRow(count($columns));
        return $result;
    }

    /**
     * Return the FROM part of the SQL statement
     * @return string
     */
    private function sqlFrom() : string
    {
        // do trim including ';' at the right part
        $sqlSource = rtrim(ltrim($this->pivot->getSource()), "; \t\n\r\x0B\0");
        // check if begins with select, if so then insert into a subquery
        $pos = strpos(strtoupper($sqlSource), 'SELECT');
        if ($pos === 0) {
            $sqlSource = "($sqlSource) AS __pivot__";
        } else {
            $sqlSource = $this->db->sqlTableEscape($sqlSource);
        }
        return $sqlSource;
    }
}


1		<?php
2		declare(strict_types = 1);
3		namespace EngineWorks\Pivot;
4
5		use EngineWorks\DBAL\DBAL;
6
7		class Query
8		{
9		/**
10		* Database Abstraction Layer Object
11		* @var DBAL
12		*/
13		private $db;
14
15		/**
16		* @var Pivot
17		*/
18		private $pivot;
19
20		/**
21		* Query constructor.
22		* @param DBAL $db
23		* @param Pivot $pivot
24		*/
25	10	public function __construct(DBAL $db, Pivot $pivot)
26		{
27	10	$this->db = $db;
28	10	$this->pivot = $pivot;
29	10	}
30
31		/**
32		* @return DBAL
33		*/
34		public function getDb(): DBAL
35		{
36		return $this->db;
37		}
38
39		/**
40		* @return Pivot
41		*/
42		public function getPivot(): Pivot
43		{
44		return $this->pivot;
45		}
46
47		/**
48		* Perform a query to obtain the distinct values of a fieldname
49		*
50		* @param string $fieldname
51		* @return array
52		* @throws PivotException if the fieldname does not exists in the source fields
53		*/
54		public function queryDistinct(string $fieldname) : array
55		{
56		if (! $this->pivot->getFieldsCollection()->exists($fieldname)) {
57		throw new PivotException("Field $fieldname does not exists in the source fields");
58		}
59		$sql = 'SELECT DISTINCT ' . $this->db->sqlFieldEscape($fieldname)
60		. ' FROM ' . $this->sqlFrom()
61		. ' ORDER BY ' . $this->db->sqlFieldEscape($fieldname)
62		. ';';
63		return $this->db->queryArrayOne($sql) ? : [];
64		}
65
66		/**
67		* @return QueryResult
68		*/
69	10	public function query() : QueryResult
70		{
71	10	return new QueryResult(
72	10	$this->queryTotalsResult(true),
73	9	($this->pivot->hasColumns()) ? $this->queryDetailsResult() : null,
74	9	$this->pivot->getCurrentRows(),
75	9	$this->pivot->getCurrentColumns(),
76	9	$this->pivot->getCurrentAggregates()
77		);
78		}
79
80		/**
81		* @return array
82		* @throws PivotException
83		* @throws PivotExceptionQuery
84		*/
85	10	private function queryData() : array
86		{
87	10	$sqlSelects = [];
88		// SQL: Select group fields
89	10	foreach ($this->pivot->getCurrentRows() as $currentRow) {
90	8	$sqlSelects[] = $this->db->sqlFieldEscape($currentRow['fieldname']);
91		}
92		// SQL: Select aggregator fields
93	10	$sqlAggregates = [];
94	10	foreach ($this->pivot->getAggregatesCollection() as $aggregate) {
95	9	$sqlAggregates[] = $aggregate->getSQL($this->db);
96		}
97		// SQL: Select group fields and aggregates
98	10	$sqlAllFields = array_merge($sqlSelects, $sqlAggregates);
99	10	$countFields = count($sqlAllFields);
100	10	if (! $countFields) { // nothing to select
101	1	throw new PivotException('Nothing to query');
102		}
103
104		// get all elements from filter
105	9	$sqlWheres = [];
106	9	foreach ($this->pivot->getFiltersCollection() as $filter) {
107	3	$sqlWheres[] = '('
108	3	. $filter->getSQL($this->db, $this->pivot->getFieldElement($filter->getFieldname())->toDBAL())
109	3	. ')';
110		}
111		// SQL Creation
112	9	$sql = 'SELECT ' . implode(', ', $sqlAllFields)
113	9	. ' FROM ' . $this->sqlFrom()
114	9	. ((count($sqlWheres)) ? ' WHERE ' . implode(' AND ', $sqlWheres) : '')
115	9	. ((count($sqlSelects)) ? ' GROUP BY ' . implode(', ', $sqlSelects) . ' WITH ROLLUP' : '')
116	9	. ';';
117
118		// retrieve and return the data
119		try {
120	9	$data = $this->db->queryArrayValues($sql);
121		} catch (\Throwable $ex) {
122		throw new PivotExceptionQuery('Query error: ' . $ex->getMessage());
123		}
124	9	if (! is_array($data)) {
125		throw new PivotExceptionQuery('Query error:' . $sql);
126		}
127	9	return $data;
128		}
129
130		/**
131		* @param bool $sortValues
132		* @return Result
133		* @throws PivotException
134		*/
135	10	private function queryTotalsResult(bool $sortValues) : Result
136		{
137		// variable to return
138	10	$result = new Result('', '');
139
140		// now all the data including rollup cells are in $data
141		// we have to generate the output
142	10	$data = $this->queryData();
143	9	$dataCount = count($data);
144	9	$aggregates = $this->pivot->getAggregatesCollection();
145	9	for ($i = 0; $i < $dataCount; $i++) {
146		// create the values content
147	9	$values = [];
148	9	foreach ($aggregates as $aggregate) {
149	9	$aggregateName = $aggregate->getAsname();
150	9	$values[$aggregateName] = $data[$i][$aggregateName];
151		}
152		// ahora especificar donde va el resultado
153	9	$ch = $result;
154	9	foreach ($this->pivot->getCurrentRows() as $row) {
155	8	$rowvalue = $data[$i][$row['fieldname']];
156	8	if (is_null($rowvalue)) {
157	8	break;
158		} else {
159	8	if (! $ch->children->exists($rowvalue)) {
160		// insert without value ($row is the $fieldname and $rowvalue is the $caption)
161	8	$ch->children->addItem(new Result($row['fieldname'], $rowvalue), $rowvalue);
162		}
163	8	$ch = $ch->children->value($rowvalue);
164		}
165		}
166		// asignar los valores al nodo seleccionado
167	9	if (! is_null($ch->values)) {
168		throw new PivotException('Duplicated values when filling the results');
169		}
170	9	$ch->values = $values;
171		}
172		// do sort
173	9	if ($sortValues) {
174	9	$ordering = new ResultOrdering($aggregates->getOrderArray());
175	9	if ($ordering->isRequired()) {
176	4	$result->orderBy($ordering);
177		}
178		}
179		// format aggregate values after sort
180	9	$this->formatResultValues($aggregates, $result);
181	9	return $result;
182		}
183
184	9	private function formatResultValues(Aggregates $aggregates, Result $result)
185		{
186	9	foreach ($aggregates as $aggregatorName => $aggregate) {
187	9	if (null !== $value = $result->getCurrentValue($aggregatorName)) {
188	9	$result->setCurrentValue($aggregatorName, number_format($value, $aggregate->getDecimals()));
189		}
190		}
191	9	foreach ($result->children as $child) {
192	8	$this->formatResultValues($aggregates, $child);
193		}
194	9	}
195
196		/**
197		* @return Result Null if no columns are set
198		* @throws PivotException If no columns are defined
199		*/
200	5	private function queryDetailsResult() : Result
201		{
202	5	$columns = $this->pivot->getCurrentColumns();
203	5	if (! count($columns)) {
204		throw new PivotException('Cannot get details if no columns are defined');
205		}
206	5	$pivotDetails = clone $this->pivot;
207	5	$pivotDetails->clearSelectorRows();
208	5	$pivotDetails->clearSelectorColumns();
209	5	foreach ($columns as $column) {
210	5	$pivotDetails->addRow($column['fieldname']);
211		}
212	5	foreach ($this->pivot->getCurrentRows() as $row) {
213	4	$pivotDetails->addRow($row['fieldname']);
214		}
215	5	$queryClone = new self($this->db, $pivotDetails);
216	5	$result = $queryClone->queryTotalsResult(false);
217	5	$result->setAsNotRow(count($columns));
218	5	return $result;
219		}
220
221		/**
222		* Return the FROM part of the SQL statement
223		* @return string
224		*/
225	9	private function sqlFrom() : string
226		{
227		// do trim including ';' at the right part
228	9	$sqlSource = rtrim(ltrim($this->pivot->getSource()), "; \t\n\r\x0B\0");
229		// check if begins with select, if so then insert into a subquery
230	9	$pos = strpos(strtoupper($sqlSource), 'SELECT');
231	9	if ($pos === 0) {
232		$sqlSource = "($sqlSource) AS __pivot__";
233		} else {
234	9	$sqlSource = $this->db->sqlTableEscape($sqlSource);
235		}
236	9	return $sqlSource;
237		}
238		}
239

eclipxe13 / engineworks-pivot

Push — master ( 601eb8...5169a0 )

Query::__construct() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like