AllSetupTeardown::prepareWorksheetWithFormula() - Code Metrics - Inspection of "Refactor Database function Unit Tests" - PHPOffice/PhpSpreadsheet - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — master (#3234)

by Mark

created 2022-12-09 12:36 UTC

AllSetupTeardown::prepareWorksheetWithFormula() B

↳ Parent: AllSetupTeardown

Complexity

Conditions	6
Paths	12

Size

Total Lines	39
Code Lines	32

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
eloc	32
c	0
b	0
f	0
dl	0
loc	39
rs	8.7857
cc	6
nc	12
nop	4

<?php

namespace PhpOffice\PhpSpreadsheetTests\Calculation\Functions\Database;

use PhpOffice\PhpSpreadsheet\Calculation\Exception as CalcException;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PHPUnit\Framework\TestCase;

class AllSetupTeardown extends TestCase
{
    protected const RESULT_CELL = 'Z1';

    /**
     * @var ?Spreadsheet
     */
    private $spreadsheet;

    /**
     * @var ?Worksheet
     */
    private $sheet;

    protected function setUp(): void
    {
    }

    protected function tearDown(): void
    {
        $this->sheet = null;
        if ($this->spreadsheet !== null) {
            $this->spreadsheet->disconnectWorksheets();
            $this->spreadsheet = null;
        }
    }

    /**
     * @param mixed $expectedResult
     */
    protected function mightHaveException($expectedResult): void
    {
        if ($expectedResult === 'exception') {
            $this->expectException(CalcException::class);
        }
    }

    /**
     * @param mixed $value
     */
    protected function setCell(string $cell, $value): void
    {
        if ($value !== null) {
            if (is_string($value) && is_numeric($value)) {
                $this->getSheet()->getCell($cell)->setValueExplicit($value, DataType::TYPE_STRING);
            } else {
                $this->getSheet()->getCell($cell)->setValue($value);
            }
        }
    }

    protected function getSpreadsheet(): Spreadsheet
    {
        if ($this->spreadsheet !== null) {
            return $this->spreadsheet;
        }
        $this->spreadsheet = new Spreadsheet();

        return $this->spreadsheet;
    }

    protected function getSheet(): Worksheet
    {
        if ($this->sheet !== null) {
            return $this->sheet;
        }
        $this->sheet = $this->getSpreadsheet()->getActiveSheet();

        return $this->sheet;
    }

    /**
     * @param int|string $field
     */
    public function prepareWorksheetWithFormula(string $functionName, array $database, $field, array $criteria): void
    {
        $sheet = $this->getSheet();
        $maxCol = '';
        $startCol = 'A';
        $maxRow = 0;
        $startRow = 1;
        $row = $startRow;
        foreach ($database as $dataRow) {
            $col = $startCol;
            foreach ($dataRow as $dataCell) {
                $sheet->getCell("$col$row")->setValue($dataCell);
                $maxCol = max($col, $maxCol);
                ++$col;
            }
            $maxRow = $row;
            ++$row;
        }
        $databaseCells = "$startCol$startRow:$maxCol$maxRow";
        $maxCol = '';
        $startCol = 'P';
        $maxRow = 0;
        $startRow = 1;
        $row = $startRow;
        foreach ($criteria as $dataRow) {
            $col = $startCol;
            foreach ($dataRow as $dataCell) {
                if ($dataCell !== null) {
                    $sheet->getCell("$col$row")->setValueExplicit($dataCell, DataType::TYPE_STRING);
                }
                $maxCol = max($col, $maxCol);
                ++$col;
            }
            $maxRow = $row;
            ++$row;
        }
        $criteriaCells = "$startCol$startRow:$maxCol$maxRow";
        $sheet->getCell('N1')->setValue($field);
        $sheet->getCell(self::RESULT_CELL)->setValue("=$functionName($databaseCells, N1, $criteriaCells)");
    }

    protected function database1(): array
    {
        return [
            ['Tree', 'Height', 'Age', 'Yield', 'Profit'],
            ['Apple', 18, 20, 14, 105],
            ['Pear', 12, 12, 10, 96],
            ['Cherry', 13, 14, 9, 105],
            ['Apple', 14, 15, 10, 75],
            ['Pear', 9, 8, 8, 76.8],
            ['Apple', 8, 9, 6, 45],
        ];
    }

    protected function database2(): array
    {
        return [
            ['Quarter', 'Area', 'Sales Rep.', 'Sales'],
            [1, 'North', 'Jeff', 223000],
            [1, 'North', 'Chris', 125000],
            [1, 'South', 'Carol', 456000],
            [1, 'South', 'Tina', 289000],
            [2, 'North', 'Jeff', 322000],
            [2, 'North', 'Chris', 340000],
            [2, 'South', 'Carol', 198000],
            [2, 'South', 'Tina', 222000],
            [3, 'North', 'Jeff', 310000],
            [3, 'North', 'Chris', 250000],
            [3, 'South', 'Carol', 460000],
            [3, 'South', 'Tina', 395000],
            [4, 'North', 'Jeff', 261000],
            [4, 'North', 'Chris', 389000],
            [4, 'South', 'Carol', 305000],
            [4, 'South', 'Tina', 188000],
        ];
    }

    protected function database3(): array
    {
        return [
            ['Name', 'Gender', 'Age', 'Subject', 'Score'],
            ['Amy', 'Female', 8, 'Math', 0.63],
            ['Amy', 'Female', 8, 'English', 0.78],
            ['Amy', 'Female', 8, 'Science', 0.39],
            ['Bill', 'Male', 8, 'Math', 0.55],
            ['Bill', 'Male', 8, 'English', 0.71],
            ['Bill', 'Male', 8, 'Science', 'awaiting'],
            ['Sue', 'Female', 9, 'Math', null],
            ['Sue', 'Female', 9, 'English', 0.52],
            ['Sue', 'Female', 9, 'Science', 0.48],
            ['Tom', 'Male', 9, 'Math', 0.78],
            ['Tom', 'Male', 9, 'English', 0.69],
            ['Tom', 'Male', 9, 'Science', 0.65],
        ];
    }

    protected function database3FilledIn(): array
    {
        // same as database3 except two omitted scores are filled in
        return [
            ['Name', 'Gender', 'Age', 'Subject', 'Score'],
            ['Amy', 'Female', 10, 'Math', 0.63],
            ['Amy', 'Female', 10, 'English', 0.78],
            ['Amy', 'Female', 10, 'Science', 0.39],
            ['Bill', 'Male', 8, 'Math', 0.55],
            ['Bill', 'Male', 8, 'English', 0.71],
            ['Bill', 'Male', 8, 'Science', 0.51],
            ['Sam', 'Male', 9, 'Math', 0.39],
            ['Sam', 'Male', 9, 'English', 0.52],
            ['Sam', 'Male', 9, 'Science', 0.48],
            ['Tom', 'Male', 9, 'Math', 0.78],
            ['Tom', 'Male', 9, 'English', 0.69],
            ['Tom', 'Male', 9, 'Science', 0.65],
        ];
    }
}


1			<?php
2
3			namespace PhpOffice\PhpSpreadsheetTests\Calculation\Functions\Database;
4
5			use PhpOffice\PhpSpreadsheet\Calculation\Exception as CalcException;
6			use PhpOffice\PhpSpreadsheet\Cell\DataType;
7			use PhpOffice\PhpSpreadsheet\Spreadsheet;
8			use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
9			use PHPUnit\Framework\TestCase;
10
11			class AllSetupTeardown extends TestCase
12			{
13			protected const RESULT_CELL = 'Z1';
14
15			/**
16			* @var ?Spreadsheet
17			*/
18			private $spreadsheet;
19
20			/**
21			* @var ?Worksheet
22			*/
23			private $sheet;
24
25			protected function setUp(): void
26			{
27			}
28
29			protected function tearDown(): void
30			{
31			$this->sheet = null;
32			if ($this->spreadsheet !== null) {
33			$this->spreadsheet->disconnectWorksheets();
34			$this->spreadsheet = null;
35			}
36			}
37
38			/**
39			* @param mixed $expectedResult
40			*/
41			protected function mightHaveException($expectedResult): void
42			{
43			if ($expectedResult === 'exception') {
44			$this->expectException(CalcException::class);
45			}
46			}
47
48			/**
49			* @param mixed $value
50			*/
51			protected function setCell(string $cell, $value): void
52			{
53			if ($value !== null) {
54			if (is_string($value) && is_numeric($value)) {
55			$this->getSheet()->getCell($cell)->setValueExplicit($value, DataType::TYPE_STRING);
56			} else {
57			$this->getSheet()->getCell($cell)->setValue($value);
58			}
59			}
60			}
61
62			protected function getSpreadsheet(): Spreadsheet
63			{
64			if ($this->spreadsheet !== null) {
65			return $this->spreadsheet;
66			}
67			$this->spreadsheet = new Spreadsheet();
68
69			return $this->spreadsheet;
70			}
71
72			protected function getSheet(): Worksheet
73			{
74			if ($this->sheet !== null) {
75			return $this->sheet;
76			}
77			$this->sheet = $this->getSpreadsheet()->getActiveSheet();
78
79			return $this->sheet;
80			}
81
82			/**
83			* @param int\|string $field
84			*/
85			public function prepareWorksheetWithFormula(string $functionName, array $database, $field, array $criteria): void
86			{
87			$sheet = $this->getSheet();
88			$maxCol = '';
89			$startCol = 'A';
90			$maxRow = 0;
91			$startRow = 1;
92			$row = $startRow;
93			foreach ($database as $dataRow) {
94			$col = $startCol;
95			foreach ($dataRow as $dataCell) {
96			$sheet->getCell("$col$row")->setValue($dataCell);
97			$maxCol = max($col, $maxCol);
98			++$col;
99			}
100			$maxRow = $row;
101			++$row;
102			}
103			$databaseCells = "$startCol$startRow:$maxCol$maxRow";
104			$maxCol = '';
105			$startCol = 'P';
106			$maxRow = 0;
107			$startRow = 1;
108			$row = $startRow;
109			foreach ($criteria as $dataRow) {
110			$col = $startCol;
111			foreach ($dataRow as $dataCell) {
112			if ($dataCell !== null) {
113			$sheet->getCell("$col$row")->setValueExplicit($dataCell, DataType::TYPE_STRING);
114			}
115			$maxCol = max($col, $maxCol);
116			++$col;
117			}
118			$maxRow = $row;
119			++$row;
120			}
121			$criteriaCells = "$startCol$startRow:$maxCol$maxRow";
122			$sheet->getCell('N1')->setValue($field);
123			$sheet->getCell(self::RESULT_CELL)->setValue("=$functionName($databaseCells, N1, $criteriaCells)");
124			}
125
126			protected function database1(): array
127			{
128			return [
129			['Tree', 'Height', 'Age', 'Yield', 'Profit'],
130			['Apple', 18, 20, 14, 105],
131			['Pear', 12, 12, 10, 96],
132			['Cherry', 13, 14, 9, 105],
133			['Apple', 14, 15, 10, 75],
134			['Pear', 9, 8, 8, 76.8],
135			['Apple', 8, 9, 6, 45],
136			];
137			}
138
139			protected function database2(): array
140			{
141			return [
142			['Quarter', 'Area', 'Sales Rep.', 'Sales'],
143			[1, 'North', 'Jeff', 223000],
144			[1, 'North', 'Chris', 125000],
145			[1, 'South', 'Carol', 456000],
146			[1, 'South', 'Tina', 289000],
147			[2, 'North', 'Jeff', 322000],
148			[2, 'North', 'Chris', 340000],
149			[2, 'South', 'Carol', 198000],
150			[2, 'South', 'Tina', 222000],
151			[3, 'North', 'Jeff', 310000],
152			[3, 'North', 'Chris', 250000],
153			[3, 'South', 'Carol', 460000],
154			[3, 'South', 'Tina', 395000],
155			[4, 'North', 'Jeff', 261000],
156			[4, 'North', 'Chris', 389000],
157			[4, 'South', 'Carol', 305000],
158			[4, 'South', 'Tina', 188000],
159			];
160			}
161
162			protected function database3(): array
163			{
164			return [
165			['Name', 'Gender', 'Age', 'Subject', 'Score'],
166			['Amy', 'Female', 8, 'Math', 0.63],
167			['Amy', 'Female', 8, 'English', 0.78],
168			['Amy', 'Female', 8, 'Science', 0.39],
169			['Bill', 'Male', 8, 'Math', 0.55],
170			['Bill', 'Male', 8, 'English', 0.71],
171			['Bill', 'Male', 8, 'Science', 'awaiting'],
172			['Sue', 'Female', 9, 'Math', null],
173			['Sue', 'Female', 9, 'English', 0.52],
174			['Sue', 'Female', 9, 'Science', 0.48],
175			['Tom', 'Male', 9, 'Math', 0.78],
176			['Tom', 'Male', 9, 'English', 0.69],
177			['Tom', 'Male', 9, 'Science', 0.65],
178			];
179			}
180
181			protected function database3FilledIn(): array
182			{
183			// same as database3 except two omitted scores are filled in
184			return [
185			['Name', 'Gender', 'Age', 'Subject', 'Score'],
186			['Amy', 'Female', 10, 'Math', 0.63],
187			['Amy', 'Female', 10, 'English', 0.78],
188			['Amy', 'Female', 10, 'Science', 0.39],
189			['Bill', 'Male', 8, 'Math', 0.55],
190			['Bill', 'Male', 8, 'English', 0.71],
191			['Bill', 'Male', 8, 'Science', 0.51],
192			['Sam', 'Male', 9, 'Math', 0.39],
193			['Sam', 'Male', 9, 'English', 0.52],
194			['Sam', 'Male', 9, 'Science', 0.48],
195			['Tom', 'Male', 9, 'Math', 0.78],
196			['Tom', 'Male', 9, 'English', 0.69],
197			['Tom', 'Male', 9, 'Science', 0.65],
198			];
199			}
200			}
201

PHPOffice / PhpSpreadsheet

Pull Request — master (#3234)

AllSetupTeardown::prepareWorksheetWithFormula() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like